I’m testing out some new hardware with the latest and greatest software versions prior to migrating a current Percona 5.1 environment across to it. However, I’m running into some real weirdness.
I have a master/slave replication setup, both on identical hardware, and I’m using the sysbench utility’s OLTP benchmark to test things out. I’m running the benchmark on the master for 5 - 10 minutes at a time and watching the slave to see how far out of sync it goes and how fast it catches up.
A few minutes into the test, the slave’s SQL thread dies (Slave_SQL_Running = No) and the Last_SQL_Error shows a clearly invalid SQL statement as follows:
Somehow, a colon has snuck into the primary key value (4:389).
So, thinking there was some corruption in the transfer to the slave, I login to the master, dump the binlog involved, and lo an behold the same invalid syntax is there:
$ mysqlbinlog us-db01-bin.000030|grep INSERT |grep 4:389
INSERT INTO sbtest values(4:389,0,’ ',‘aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy’)
OK, this has got me confused. How could an statement with invalid syntax get into the binlog? Just to be sure, I tried out the statement on the master, and as expected it failed with the same syntax error it did on the slave.
Searching through the same binlog there seem to be three or four more similar offending statements further down amongst the tens of thousands of valid ones so even if I skip this statement, it’s going to fail again very soon.
Ideas anyone?
Environment is:
- CentOS 6.3 x86_64
- Both master and slave running in KVM VMs on our own hardware
- Percona 5.5.25a-rel27.1.277.rhel6 (from the Percona yum repository) as follows:
Percona-Server-client-55.x86_64 5.5.25a-rel27.1.277.rhel6 @percona
Percona-Server-server-55.x86_64 5.5.25a-rel27.1.277.rhel6 @percona
Percona-Server-shared-55.x86_64 5.5.25a-rel27.1.277.rhel6 @percona
Percona-Server-shared-compat.x86_64 5.5.25a-rel27.1.277.rhel6 @percona - SSD for all drives
Thanks,
Toby.
[Edit for more info]
I should probably also say that the table involved (sysbench is using just one) is on InnoDB. Following is the my.cnf - it’s identical on both machines except for the server-id:
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
We are on BBU RAID so this should provide high reliability with little
performance degredation
sync_binlog=1
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=250
default-time-zone=UTC
sql_mode=“NO_AUTO_VALUE_ON_ZERO”
default-storage-engine=innodb
character-set-server=utf8
collation-server=utf8_bin
long_query_time=10
slow_query_log=true
slow_query_log_file=/var/log/mysql/log-slow-queries.log
max_allowed_packet=64M
skip-host-cache
skip-name-resolve
log-bin = /data1/mysql/logs/us-db01-bin
expire_logs_days = 30
binlog_format = MIXED
server-id = 11
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /data1/mysql/logs
innodb_file_per_table
innodb_buffer_pool_size = 8000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 150M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 300
innodb_fast_shutdown = 0
max_heap_table_size = 500M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid