Bug #71397 | Replicated slave stops replicating with Error_code: 1366 | ||
---|---|---|---|
Submitted: | 16 Jan 2014 9:04 | Modified: | 28 Feb 2014 17:21 |
Reporter: | Shaun Rampersad | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.5.34 | OS: | Linux (Red Hat Enterprise Linux Server release 6.4 (Santiago)) |
Assigned to: | CPU Architecture: | Any | |
Tags: | replication |
[16 Jan 2014 9:04]
Shaun Rampersad
[16 Jan 2014 12:08]
Valeriy Kravchuk
I think it can be related to Bug #67800.
[16 Jan 2014 13:55]
Shaun Rampersad
I dont think its related. See this link. This person has the same issue http://dba.stackexchange.com/questions/50449/mysql-1366-error-on-replication-slave I'm trying to locate the SQL statement on the master log to see what was executed on the master server and then try to replicate this issue.
[16 Jan 2014 17:43]
Sveta Smirnova
Thank you for the report. Please provide output of SHOW CREATE TABLE cfg_package, indicate which SQL mode do you use on both slave and master and try to get full query from the problematic binary log file.
[16 Jan 2014 18:30]
MySQL Verification Team
The above code is a warning which is issued whenever a string (or similar) can not be converted to the numerical field of any kind. In your configuration you could have elevated warnings to errors. So, the info that my colleagues have asked for is necessary.
[17 Jan 2014 8:07]
Shaun Rampersad
Hi Thank your for the feedback. HEre is the info you requested. MASTER SERVER: mysql> SHOW CREATE TABLE cfg_package; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | cfg_package | CREATE TABLE `cfg_package` ( `pkg_id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `version` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL, `xml` longtext COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`pkg_id`) ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@GLOBAL.sql_mode; +-------------------+ | @@GLOBAL.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +--------------------+ | @@SESSION.sql_mode | +--------------------+ | | +--------------------+ 1 row in set (0.00 sec) Transaction in binary log thats causing the problem: See uploaded file - master_query.log SLAVE SERVER: mysql> SHOW CREATE TABLE cfg_package; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | cfg_package | CREATE TABLE `cfg_package` ( `pkg_id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `version` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL, `xml` longtext COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`pkg_id`) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@GLOBAL.sql_mode; +-------------------+ | @@GLOBAL.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +--------------------+ | @@SESSION.sql_mode | +--------------------+ | | +--------------------+ 1 row in set (0.00 sec) Transaction in binary log thats causing the problem: See uploaded file - slave_query.log This was logged in the slave binlog. From what I can see, the queries logged look identical, so there should be no reason why it failed. Thanks for the help. Shaun
[17 Jan 2014 8:32]
Shaun Rampersad
Hi I tried to run this query manually on the slave and it works. So I'm not sure why the replication does not work. mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into `cfg_package_extension` (`pkg_id`, `is_class`, `name`, `data`) values ( NAME_CONST('pPkgId',46), NAME_CONST('pIsClass',_binary'^A' COLLATE 'binary'), NAME_CONST('pName',_utf8'com.nimsoft.config.plugin.cdmPlugin' COLLATE 'utf8_unicode_ci'), NAME_CONST('pData',_binary'<CA><FE><BA><BE>\0\0\02\05\n\0^K\0\"^G\0#\n\0^B\0\\0$\n\0^B\0\0&\n\0\'\0\0)\n\0\'\0*^G\0+^G\0,^A\0^F<init>^A\0^C()V^A\0^DCode^A\0^OLineNumberTable^A\0^RLocalVariableTable^A\0^Dthis^A\0%Lcom/nimsoft/config/plugin/cdmPlugin;^A\0^Gencrypt^A\0&(Ljava/lang/String;)Ljava/lang/String;^A\0^Ddata^A\0^RLjava/lang/String;^A\0^FnimSec^A\0 Lcom/nimsoft/nimbus/NimSecurity;^A\0\nExceptions^G\0-^A\0^LreplaceSlash^A\diskName^A\indexPos^A\0^AI^A\0\rStackMapTable^A\0\nSourceFile^A\0^NcdmPlugin.java^L\0^L\0\r^A\0^^com/nimsoft/nimbus/NimSecurity^A\0 #deadbeef^L\0.\0/^A\0^A/^G\00^L\01\02^A\0^A#^L\03\04^A\0#com/nimsoft/config/plugin/cdmPlugin^A\0^Pjava/lang/Object^A\0^_com/nimsoft/nimbus/NimException^A\0\nencryptISO^A\08(Ljava/lang/String;Ljava/lang/String;)Ljava/lang/String;^A\0^Pjava/lang/String^A\0^GindexOf^A\0^U(Ljava/lang/String;)I^A\0^Greplace^A\0D(Ljava/lang/CharSequence;Ljava/lang/CharSequence;)Ljava/lang/String;\0!\0\n\0^K\0\0\0\0\0^C\0^A\0^L\0\r\0^A\0^N\0\0\0/\0^A\0^A\0\0\0^E*<B7>\0^A<B1>\0\0\0^B\0^O\0\0\0^F\0^A\0\0\0^F\0^P\0\0\0^L\0^A\0\0\0^E\0^Q\0^R\0\0\0 \0^S\0^T\0^B\0^N\0\0\0H\0^C\0^B\0\0\0^P<BB>\0^BY<B7>\0^CL+^R ^D*<B6>\0^E<B0>\0\0\0^B\0^O\0\0\0\n\0^B\0\0\\\0 \0^P\0\0\0^V\0^B\0\0\0^P\0^U\0^V\0\0\\\0^W\0^X\0^A\0^Y\0\0\0^D\0^A\0\Z\0 \0ESC\0^T\0^A\0^N\0\0\0c\0^C\0^B\0\0\0^W*^R^F<B6>\0^G<ESC^B<9F>\0^L*^R^F^R^H<B6>\0 <B0>*<B0>\0\0\0^C\0^O\0\0\0^R\0^D\0\0\0\r\0^G\0^N\0^L\0^P\0^U\0^T\0^P\0\0\0^V\0^B\0\0\0^W\0^\\0^V\0\0\0^G\0^P\0^]\0^^\0^A\0^_\0\0\0^F\0^A<FC>\0^U^A\0^A\0 \0\0\0^B\0!' COLLATE 'binary')) /*!*/; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) I then try to start slave and it fails again. The error in the logfile is: 140117 10:31:09 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000037' at position 84014321, relay log './redcemdb01-relay-bin.000022' position: 84014467 140117 10:31:09 [ERROR] Slave SQL: Error 'Incorrect integer value: '\x01' for column 'is_class' at row 1' on query. Default database: 'nimsoftslm'. Query: 'insert into `cfg_package_extension` (`pkg_id`, `is_class`, `name`, `data`) values ( NAME_CONST('pPkgId',46), NAME_CONST('pIsClass',_binary'^A' COLLATE 'binary'), NAME_CONST('pName',_utf8'com.nimsoft.config.plugin.cdmPlugin' COLLATE 'utf8_unicode_ci'), NAME_CONST('pData',_binary'<CA><FE><BA><BE>\0\0\02\05\n\0^K\0\"^G\0#\n\0^B\0\\0$\n\0^B\0\0&\n\0\'\0\0)\n\0\'\0*^G\0+^G\0,^A\0^F<init>^A\0^C()V^A\0^DCode^A\0^OLineNumberTable^A\0^RLocalVariableTable^A\0^Dthis^A\0%Lcom/nimsoft/config/plugin/cdmPlugin;^A\0^Gencrypt^A\0&(Ljava/lang/String;)Ljava/lang/String;^A\0^Ddata^A\0^RLjava/lang/String;^A\0^FnimSec^A\0 Lcom/nimsoft/nimbus/NimSecurity;^A\0\nExceptions^G\0-^A\0^LreplaceSlash^A\diskName^A\indexPos^A\0^AI^A\0\rStackMapTable^A\0\nSourceFile^A\0^NcdmPlugin.java^L\0^L\0\r^A\0^^com/nimsoft/nimbus/NimSecurity^A\0 #deadbeef^L\0.\0/^A\0^A/^G\00^L\01\02^A\0^A#^L\03\04^A\0#com/nimsoft/config/plugin/cdmPlugin^A\0^Pjava/lang/Object^A\0^_com/nimsoft/nimb 140117 10:31:09 [Warning] Slave: Incorrect integer value: '\x01' for column 'is_class' at row 1 Error_code: 1366 140117 10:31:09 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000037' position 84014321 The position entries correspond to the attached files. Regards shaun
[21 Jan 2014 16:41]
Sveta Smirnova
Thank you for the feedback. When you run query manually you see message "Query OK, 1 row affected, 1 warning (0.00 sec)". If you run SHOW WARNINGS I expect you will still see same message "Incorrect integer value: '\x01' for column 'is_class' at row 1 Error_code: 1366". Question is why it errors if you use forgiving SQL mode. Can be related to bug #70346 which is about NAME_CONST too strict behavior.
[21 Jan 2014 17:32]
Sveta Smirnova
Please also provide output of show variables like '%slave%';
[22 Jan 2014 7:15]
Shaun Rampersad
Hi Sveta Yes there is a warning. I missed that. Here is the info requested. mysql> show warnings; +---------+------+--------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------+ | Warning | 1366 | Incorrect integer value: '^A' for column 'is_class' at row 1 | +---------+------+--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show variables like '%slave%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | init_slave | | | log_slave_updates | OFF | | pseudo_slave_mode | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slave_type_conversions | | | sql_slave_skip_counter | 0 | +---------------------------+------------+ 12 rows in set (0.00 sec) The slave variables are identical on master and slave. Regards shaun
[23 Jan 2014 7:53]
Shaun Rampersad
Hi I have resorted to manually running the faulty query, then skipping the next transaction in the binlog. The slave starts and then fails at the next insert with the same problem. I've manually inserted 3 faulty SQL's again and repeated the process. The slave started and ran for a few seconds before encountering the same issue further in the binlog. I'm guessing that this is the only work around in order to get the slave replicating. This is a production setup so I cannot afford for the slave to be out of sync. I will try to manually rung these queries till I can get the slave running automatically. Thanks for all the help thus far. Shaun
[23 Jan 2014 18:27]
Sveta Smirnova
Thank you for the feedback. I suspected you have option init_slave, setting SQL mode, but you have not. You indicated you compiled server yourself. Please provide options which you used. Another workaround is to change original query, so it does not use character string for TINYINT column or change type of the column to CHAR(1) or other more appropriate type.
[24 Jan 2014 8:00]
Shaun Rampersad
Hi I have cleaned up the source and have lost the config file. I've used mysqlbug to get config options. >C compiler: gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3) >C++ compiler: c++ (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3) >Environment: <machine, os, target, libraries (multiple lines)> System: Linux greencemdb01.fnbsp.co.za 2.6.32-358.6.2.el6.x86_64 #1 SMP Tue May 14 15:48:21 EDT 2013 x86_64 x86_64 x86_64 GNU/Linux Architecture: x86_64 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: x86_64-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --with-java-home=/usr/lib/jvm/java-1.5.0-gcj-1.5.0.0/jre --enable-libgcj-multifile --enable-java-maintainer-mode --with-ecj-jar=/usr/share/java/eclipse-ecj.jar --disable-libjava-multilib --with-ppl --with-cloog --with-tune=generic --with-arch_32=i686 --build=x86_64-redhat-linux Thread model: posix gcc version 4.4.7 20120313 (Red Hat 4.4.7-3) (GCC) Compilation info (call): CC='/usr/bin/gcc' CFLAGS=' -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/c++' CXXFLAGS=' -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='/usr/bin/gcc' CFLAGS=' -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/c++' CXXFLAGS=' -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' HTH. I cannot change the query or table structure as this will invalidate support for the application by the software vendor. Regards shaun
[24 Jan 2014 18:36]
Sveta Smirnova
Thank you for the feedback. But I meant how you build MySQL sources? And if cannot find configure options please try to setup spare slave in your environment, but use Oracle MySQL binaries for your platform, available from dev.mysql.com/downloads and check if the issue is repeatable.
[17 Feb 2014 10:57]
Shaun Rampersad
Hi Sveta Sorry for the delayed reply. The configure option is in the last comment. Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-languages=c,c++,objc,obj-c++,java,fortran,ada --enable-java-awt=gtk --disable-dssi --with-java-home=/usr/lib/jvm/java-1.5.0-gcj-1.5.0.0/jre --enable-libgcj-multifile --enable-java-maintainer-mode --with-ecj-jar=/usr/share/java/eclipse-ecj.jar --disable-libjava-multilib --with-ppl --with-cloog --with-tune=generic --with-arch_32=i686 --build=x86_64-redhat-linux I have not had the time too setup another server for replication. Will try to get that done and test. REgards shaun
[25 Feb 2014 15:55]
Sveta Smirnova
Thank you for the feedback. But this configure option is for gcc. And we need cmake options for MySQL server. Either provide it or test with Oracle binaries in your environment.
[27 Feb 2014 12:08]
Shaun Rampersad
Hi I've installed another slave using the 5.5.34 Oracle binaries that were downloaded from this site. I then dumped the master data using mysqldump --flush-privileges --routines --triggers --add-drop-database --all-databases --single-transaction --master-data > /dbmnt/mysql/master_data.sql On the new slave I imported the dump, changed master to the correct host, port, user, and password, then started the slave. I'm now faced with the following error: [ERROR] Slave SQL: Error 'Cannot add or update a child row: a foreign key constraint fails .... Error_code: 1452 I've ensured that auto_increment_increment and auto_increment_offset are the same on master and slave but this problem still persists. I cannot get the slave to start replicating in order to reproduce the original issue. REgards shaun
[28 Feb 2014 17:21]
Sveta Smirnova
Thank you for the feedback. This is new problem and looks like support request for me. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ I could not repeat original problem, so closing report as "Can't repeat". If you will be able to solve foreign key issue and repeat original problem with Oracle binaries feel free to re-open the report. One note. > I've ensured that auto_increment_increment and auto_increment_offset are the same on master and slave but this problem still persists. While auto_increment_increment should be same on master and slave, auto_increment_offset should be different if you want to avoid duplicate key issues while allowing updates not from the replication SQL thread on slave.
[30 Aug 2016 8:42]
Jonathan GENTILHOMME
Hi, I encounter exactly the same issue with a different environment : mysql> SHOW VARIABLES LIKE "%version%"; +-------------------------+------------------------------------------------ | Variable_name | Value | +-------------------------+------------------------------------------------ | innodb_version | 5.6.31 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.31-enterprise-commercial-advanced-log | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------------------------ uname -a Linux isr-dev-cg1-uim-mysql 3.10.0-327.el7.x86_64 #1 SMP Thu Nov 19 22:10:57 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) Issue : 2016-08-24 14:49:27 2826 [ERROR] Slave SQL: Error 'Incorrect integer value: '\x01' for column 'is_class' at row 1' on query. Regards, Jonathan