Hi guys,
We have a PHP application that uses AWS Load Balancer that connects to 3 Percona Servers.
Before we used a single standard Percona Server and that never happened.
The cluster starts throwing Primary Key errors as shown below.
2014/10/14 18:50:20 [error] 23862#0: *193487 FastCGI sent in stderr: "PHP message: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '839027' for key 'PRIMARY'' in ../LeadNotes.php:423
2014/10/14 18:50:37 [error] 23864#0: *193116 FastCGI sent in stderr: "PHP message: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '226829' for key 'PRIMARY'' in ../Leads.php:588
2014/10/14 18:57:46 [error] 23865#0: *197705 FastCGI sent in stderr: "PHP message: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '438532' for key 'PRIMARY'' in ../EmailQueue.php:122
2014/10/14 19:01:19 [error] 23862#0: *199328 FastCGI sent in stderr: "PHP message: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1414' for key 'PRIMARY'' in ../EnrollModel.php:325
2014/10/14 19:11:31 [error] 23864#0: *203042 FastCGI sent in stderr: "PHP message: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '58078' for key 'PRIMARY'' in ../HelpTicketComments.php:94
All columns are auto-increment.
We have multiple non-persisitent connections coming from the web server trough the load balancer.
There are several cron tasks that connects to the load balancer all the time also.
I’m not sure what can cause this. I did a little bit of research and “wsrep_auto_increment_control” is set to ON.
This is my configuration file:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/data/mysql
tmpdir = /mnt/tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
default_time_zone = America/New_York
# -- Cluster Settings -------------------------- #
# Path to Galera library
wsrep_provider = /usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
# It should be empty during bootstrap
wsrep_cluster_address=gcomm://10.0.200.7,10.0.200.8,10.0.210.7
#wsrep_cluster_address=gcomm://
# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW
# MyISAM storage engine has only experimental support
default_storage_engine = InnoDB
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2
# Node #1 address
wsrep_node_address = 10.0.200.7
# SST method
wsrep_sst_method = xtrabackup-v2
# Authentication for SST method
wsrep_sst_auth="db1:xxxxxxx"
wsrep_slave_threads = 2
# Cluster and node name
wsrep_cluster_name = db1
wsrep_node_name = db1a
# ---------------------------------------------- #
key_buffer_size = 256M
sort_buffer_size = 3M
read_rnd_buffer_size = 3M
join_buffer_size = 3M
max_allowed_packet = 64M
thread_stack = 512K
thread_cache_size = 12
table_open_cache = 2048
max_heap_table_size = 128M
tmp_table_size = 256M
myisam-recover = BACKUP
max_connections = 200
thread_concurrency = 8
#
# * Query Cache Configuration
#
# Cache size needs to be set to 0 before start with XtrabDB cluster
# It can ben changed during runtime
# http://www.percona.com/doc/percona-xtradb-cluster/5.6/limitation.html
query_cache_type = 1
query_cache_limit = 4M
query_cache_size = 0
#
# * Logging and Replication
#
# It has to be logged to FILE to work with XtraDB Cluster
# http://www.percona.com/doc/percona-xtradb-cluster/5.6/limitation.html
log_output = FILE
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 10
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/data/log/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 512M
log-slave-updates
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_thread_concurrency = 0
innodb_file_format = Barracuda
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 32
innodb_write_io_threads = 16
# There is no point in having trx_commit to 1 in Galera Cluster
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table
#
# * Security
#
ssl-ca = /etc/ssl/certs/ca.crt
ssl-cert = /etc/mysql/keys/db1a.crt
ssl-key = /etc/mysql/keys/db1a.key
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M