Mysql
Percona XtraDB 集群中的完整性約束違規
我們有一個 PHP 應用程序，它使用連接到 3 個 Percona 伺服器的 AWS 負載均衡器。
在我們使用單個標準 Percona 伺服器之前，這從未發生過。
集群開始拋出主鍵錯誤，如下所示。
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所有列都是自動遞增的。我們有多個來自 Web 伺服器通過負載均衡器的非持久連接。有幾個 cron 任務也一直連接到負載均衡器。
我不確定是什麼原因造成的。我做了一些研究，“wsrep_auto_increment_control”設置為 ON。
這是我的配置文件：
[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
這不是 Galera 的錯誤，而是應用程序級別的“邏輯錯誤”。感謝 Galera 發生了錯誤並且它沒有破壞您的拓撲或破壞一致性，因為它會發生在標準複製中。
發生這種情況是因為您試圖在兩個節點上幾乎同時插入 auto-inc 記錄。出於性能原因，Galera 具有樂觀鎖定，這就是您收到錯誤的原因。
您可以通過為兩個節點設置不同的自動增量系列來輕鬆解決此問題。
這可以通過配置輕鬆完成（假設設置為 3 節點）：
Node 1 ====== auto_increment_increment = 3 auto_increment_offset = 0 Node 2 ====== auto_increment_increment = 3 auto_increment_offset = 1 Node 3 ====== auto_increment_increment = 3 auto_increment_offset = 2然後重新啟動。這將告訴 MySQL 在節點 1 上僅插入 1、4、7 等；2, 5, 8,… 在節點 2 和 3, 6, 9,… 在 3。小心不要用完 ids！
正如我所說，發生這種情況實際上很酷——錯誤會被清楚地顯示并快速修復，而不是無聲的問題。這在標準複製上效果不佳（更新將繼續成為問題），但在 Galera 上卻不行——預設情況下，同一行上的更新在回滾之前至少重試一次。
請參閱以下文件，了解為什麼不應如上所述設置 auto_increment_offset：http: //dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_increment
0 -> 1 1 -> 1 2 -> 2