Повторное использование Mysql Master-Slave с первичным ключом AUTO_INCREMENT
Проблема заключается в стандартной настройке MysqlMaster-Slave (ведомое устройство доступно только для чтения, двоичное ведение журнала установлено на ROW), когда одна строка вставляется в главную таблицу с автоматическим увеличением первичного ключа, и эта строка, например, получает ID 4288996, когда то же самое вставка реплицируется на ведомом устройстве, одна и та же строка получает другой ID, например 4289032 (поскольку вставка выполняется без значения первичного ключа, которое создается автоматически).
Теперь проблема возникает, когда оператор UPDATE запускается в этой строке на Master:
#180430 18:00:12 server id 1 end_log_pos 429933 CRC32 0xd0d85778
Update_rows: table id 260 flags: STMT_END_F
### UPDATE `cmon`.`simple_alarm`
### WHERE
### @1=4288996
### SET
### @13=1525104012
### @15=1
# at 429933
#180430 18:00:12 server id 1 end_log_pos 429964 CRC32 0xdc9f3fa4 Xid = 452035
Поскольку такая же строка не существует на ведомом устройстве (с другим идентификатором):
'Could not execute Update_rows event on table cmon.simple_alarm; Can't find record in 'simple_alarm', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log xxxxx_bin_log.000003, end_log_pos 429933'
Я знаю, почему это проблема, но не знаю, как ее решить?
Я только что узнал, что каждый ведомый по отдельности решает активировать изменение или нет. Если новый идентификатор уже существует в ведомом устройстве, он не может вызвать изменение
Ответы 1
Известная проблема
Справочное руководство по MySQL 8.0 / ... / Репликация и AUTO_INCREMENT 17.4.1.1
A statement invoking a trigger or function that causes an update to an AUTO_INCREMENT column is not replicated correctly using statement-based replication. These statements are marked as unsafe. (Bug #45677)
Попробуйте ведение журнала на основе строк или смешанного типа. Может, решится.
К сожалению, я уже использую ведение журнала на основе ROW, и возникает такая же проблема: binlog_format = ROW
@Goran Вы пробовали это в комментариях?
Да, я синхронизировал подчиненное устройство с главным устройством несколько раз. И как только новая запись добавляется в таблицу cmon.simple_alarm, происходит рассинхронизация. Я использую следующие шаги: percona.com/doc/percona-xtrabackup/2.4/howtos/…
Можете ли вы вставить новую строку и запустить ее в обеих таблицах и посмотреть, совпадают ли результаты? ВЫБРАТЬ LAST_INSERT_ID ();
Не указывайте идентификатор auto_incremented снова (который, как я предполагаю, является КЛЮЧОМ)
Теперь это результат select alarm_id from cmon.simple_alarm; на ведомом устройстве: "1" "4279416" "4288318" "4288349" "4289023" "4297593" "4297596" "4299500" "4299501" И это на главном устройстве: "1" "4279416" "4288349" "4328650" "4327762" "4288318" "4327760" Как видите, в этой таблице полностью рассинхронизировано.
ALTER TABLE masterTable AUTO_INCREMENT = 9999990; (Тот же оператор для подчиненной таблицы на всякий случай) & Вставить новую строку в главную (не указывать id, как всегда) & SELECT MAX (id) FROM master.alarm; ВЫБРАТЬ МАКС. (Идентификатор) ИЗ slave.alarm; (Если оба одинаковы, все в порядке) & UPDATE master.alarm SET id = 9999996 WHERE id = 9999990 & и посмотреть, не работает ли он по-прежнему?
И ведомое устройство показывает статус ОК: «Ожидание отправки события ведущим устройством», «Ведомое устройство прочитало весь журнал реле; ожидает дополнительных обновлений»
Скорее всего, это вызвано Master и Slave с разными P.Keys.
Я только что выдал delete from cmon.simple_alarm; и опустил всю таблицу. Опять же, та же таблица на ведомом устройстве не пуста (осталась прежней с 9 строками), и репликация остановлена: SQL Thread STOPPED (err: Could not execute Delete_rows event on table cmon.simple_alarm; Can't find record in 'simple_alarm', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log xxxxxx_bin_log.000003, end_log_pos 199526693, errno: 1032) IO Thread RUNNING.
Это нормальное ведомое устройство не имеет этого идентификатора, поэтому оно не может выполнять эту операцию. Эти таблицы не идентичны. Столбцы идентификаторов должны быть одинаковыми для этой операции.
Да это правда. Эта таблица и все операции с ней управляются программой ClusterControl (somenines.com/product/clustercontrol), и я не могу ее изменить (изменить таблицу и / или изменить SQL-запрос).
Я только что это сделал. Также обрежьте главную таблицу simple_alarm перед репликацией. Теперь у меня есть пустые таблицы как на главном, так и на подчиненном устройстве. И следующее значение auto_increment показывает 1 в обеих системах. Теперь я запускаю службу cmon (которая вставляет данные в эту таблицу и посмотрю, что произойдет). Спасибо за вашу помощь.
Как только я запускаю процесс cmon, таблица на главном сервере (simple_alarm) заполняется строками, и ни одна из них не реплицируется на подчиненное устройство. Итак, таблица на главном сервере теперь имеет 6 строк, а на ведомом устройстве все еще пусто :) Я действительно не могу этого понять. Статус ведомого показывает, что все реплицируется: Slave has read all relay log; waiting for more updates - Секунды позади ведущего: 0
Странно, только что выяснилось, что если я вручную вставляю некоторые данные в таблицу, они реплицируются правильно, но когда приложение вставляет строки, которые вообще не реплицируются ... Очень странно ...
Что ж, поздравляю, что вы только что обнаружили ошибку в нескольких девятках автоматизации
По-видимому, вся база данных cmon не реплицирует инструкции вставки, выданные приложением Somenines ClusterControl, а не только указанную таблицу. Другая база данных, используемая в ClusterControl 'dcps', реплицируется без проблем. Я до сих пор не могу понять, как такое возможно.
Можешь вставить перед репликацией. Если это так, вставьте свои собственные строки, затем запустите репликацию, чтобы посмотреть, будет ли автоматическая вставка реплицироваться вместе с вашей или только вашей.
Тиражируются только минные вставки. По-прежнему никаких вставок из приложения.
Я никогда раньше не видел распределенную систему, но эта уже пахнет последней бессмысленной платформой, заполненной обходными путями. Удачи дружище
Ведущий ведомый с другим auto_incremented P.K сам по себе другая проблема. Я думаю, вам следует полностью синхронизировать обе таблицы, включая поле P.K и их значения auto_increment, прежде чем устранять эту проблему. Однако приведенный ниже ответ может решить, я настоятельно рекомендую вам создать новое ведомое устройство, полностью идентичное с той же настройкой репликации, и попробовать свой оператор обновления и посмотреть, не связана ли утечка с рассинхронизацией.