Description:
I get a problem that cause table crash and mysql database server locked.
I have two machine that get replication running. and i create a trigger to log all the changes to a table call "ChangeLog". eg if you update Table A, Customer A's phone number, it will log the previous phone number, and new phone phone.
It is working fine, until you issue a update statement that update thousands of records at once, and in the mean time Master server is inserting to Database in a rate of 4 record per second. eg update customer set state=1 (if you get one thousand customers you will cause the trigger to insert 1000 log changes) that is all working well on the Master server.
But when the slave machine receiving the replication log command, it will casue ChangeLog table crash. and replication will stop. On our slave machine has a application to query the ChangeLog table every 15 second to get the lastest update log, we have try to make the query run as fast as we could, here is our query statemnt
select * from ChangeLog where changelogID>10000 (10000 is the last pos we have read) normally return only 10 records
How to repeat:
Here is my setup
Two windows XP SP2 machines install with mysql-5.1.19-beta-win32.zip
install with setup wizzard
OPTIONS
Server Machine
Non- Transactional Database Only
Online Transaction Processing
using standard my.cnf
The master server is config as follow
server-id=1
skip-name-resolve
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.1.1
master-port=3306
master-user = user
master-password = pass
master-connect-retry = 60
replicate-do-db = testdb
binlog-do-db =testdb
net_buffer_length =1638400
concurrent_insert=2
log-bin = D:\MySQL\MySQLServer51\log\log-bin.log
binlog-do-db = testdb
relay-log=relayB-bin
#Max packetlength to send/receive from to server.
max_allowed_packet=10485760
the slave cnf is setup as follow
server-id=2
skip-name-resolve
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 192.168.1.2
master-port=3306
master-user = user
master-password = pass
master-connect-retry = 60
replicate-do-db = testdb
binlog-do-db =testdb
net_buffer_length =1638400
concurrent_insert=2
log-bin = D:\MySQL\MySQLServer51\log\log-bin.log
binlog-do-db = testdb
relay-log=relayB-bin
#Max packetlength to send/receive from to server.
max_allowed_packet=10485760
Here is my trigger for log the change
CREATE TRIGGER UPDATE_customers BEFORE UPDATE ON customers
FOR EACH ROW BEGIN
DECLARE LogID INTEGER;
DECLARE U CHAR(50);
DECLARE IP CHAR(20);
If ISNULL(New.U) THEN
SET New.U='';
SET New.IP='';
else
SET U=New.U;
SET IP=New.IP;
END IF;
SET New.U='';
SET New.IP='';
INSERT INTO ChangeLog SET TableName = 'customers',changeKeys=CONCAT(New.customersID),Actions='UPDATE',status=0,User=U,IP=IP;
SET LogID = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE
TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ChangeLog')-1 ;
INSERT INTO ChangeLogDetail SET ChangeLogID = LogID,FieldName='PhoneNumber',PreValue=OLD.PhoneNumber,CurrentValue=NEW.PhoneNumber;
END;
Suggested fix:
So far my solution is try to repair the ChangeLog table, and restart replication, sometimes it works, Otherwise i have to shutdown DB server. and using myisamchk.exe to repair the tabel offline, then bring up the DB server again.
Description: I get a problem that cause table crash and mysql database server locked. I have two machine that get replication running. and i create a trigger to log all the changes to a table call "ChangeLog". eg if you update Table A, Customer A's phone number, it will log the previous phone number, and new phone phone. It is working fine, until you issue a update statement that update thousands of records at once, and in the mean time Master server is inserting to Database in a rate of 4 record per second. eg update customer set state=1 (if you get one thousand customers you will cause the trigger to insert 1000 log changes) that is all working well on the Master server. But when the slave machine receiving the replication log command, it will casue ChangeLog table crash. and replication will stop. On our slave machine has a application to query the ChangeLog table every 15 second to get the lastest update log, we have try to make the query run as fast as we could, here is our query statemnt select * from ChangeLog where changelogID>10000 (10000 is the last pos we have read) normally return only 10 records How to repeat: Here is my setup Two windows XP SP2 machines install with mysql-5.1.19-beta-win32.zip install with setup wizzard OPTIONS Server Machine Non- Transactional Database Only Online Transaction Processing using standard my.cnf The master server is config as follow server-id=1 skip-name-resolve replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 192.168.1.1 master-port=3306 master-user = user master-password = pass master-connect-retry = 60 replicate-do-db = testdb binlog-do-db =testdb net_buffer_length =1638400 concurrent_insert=2 log-bin = D:\MySQL\MySQLServer51\log\log-bin.log binlog-do-db = testdb relay-log=relayB-bin #Max packetlength to send/receive from to server. max_allowed_packet=10485760 the slave cnf is setup as follow server-id=2 skip-name-resolve replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = 192.168.1.2 master-port=3306 master-user = user master-password = pass master-connect-retry = 60 replicate-do-db = testdb binlog-do-db =testdb net_buffer_length =1638400 concurrent_insert=2 log-bin = D:\MySQL\MySQLServer51\log\log-bin.log binlog-do-db = testdb relay-log=relayB-bin #Max packetlength to send/receive from to server. max_allowed_packet=10485760 Here is my trigger for log the change CREATE TRIGGER UPDATE_customers BEFORE UPDATE ON customers FOR EACH ROW BEGIN DECLARE LogID INTEGER; DECLARE U CHAR(50); DECLARE IP CHAR(20); If ISNULL(New.U) THEN SET New.U=''; SET New.IP=''; else SET U=New.U; SET IP=New.IP; END IF; SET New.U=''; SET New.IP=''; INSERT INTO ChangeLog SET TableName = 'customers',changeKeys=CONCAT(New.customersID),Actions='UPDATE',status=0,User=U,IP=IP; SET LogID = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ChangeLog')-1 ; INSERT INTO ChangeLogDetail SET ChangeLogID = LogID,FieldName='PhoneNumber',PreValue=OLD.PhoneNumber,CurrentValue=NEW.PhoneNumber; END; Suggested fix: So far my solution is try to repair the ChangeLog table, and restart replication, sometimes it works, Otherwise i have to shutdown DB server. and using myisamchk.exe to repair the tabel offline, then bring up the DB server again.