MariaDB Replication Error Number 1366- Incorrect integer value: '\x00' for column 'DB.tablename'.'column' at row 1
Dear Team,
I have replication setup like MySQL (Master of slave1) <-->MariaDB (Slave1 and master of slave2) <-->MariaDB (Slave2).
Database server version details. MySQL 5.6 MariaDB(slave1) 10.3.32 MaraiDB(Slave2) 10.3.32
I am getting error 1366 and Description is below.
Last_SQL_Errno: 1366 Last_SQL_Error: Error 'Incorrect integer value: '\x00' for column `geos`.`cnc_runningtimes`.`UnexpectedWorkStationStatus` at row 1' on query. Default database: 'geos'. Query: 'INSERT INTO cnc_runningtimes( IdWorkStation, StartTime, UnexpectedWorkStationStatus ) VALUES ( NAME_CONST('_IdWorkStation',9), NAME_CONST('_StartTime',TIMESTAMP'2022-02-09 10:24:59'), NAME_CONST('_UnexpectedWorkStationStatus',_binary'\0' COLLATE 'binary') )'
Below some paramter that I have used for troubleshooting purpose.All paramtere and setting same on all 3 server and error still persist SHOW CREATE TABLE cfg_package_extension; SHOW VARIABLES LIKE "%version%"; SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode;
Note: I want to highlight one point that same setup and configuration is working fine for other database server replication
Request to you please help me on that issue.
Answer Answered by Daniel Black in this comment.
Would UnexpectedWorkStationStatus be an enum? Is the column definition identical on all servers?
If you could include a bug report including the exact MySQL-5.6 version, the SHOW CREATE TABLE cnc_runningtimes, the binlog_format this might be repeatable and fixable.
Is the error occurring on slave1?
**** Master *****
mysql> select version(); +-----------------+
| version() |
+-----------------+
| 5.6.51-91.0-log |
+-----------------+ 1 row in set (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'binlog_format'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+ 1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE geos.cnc_runningtimes; +------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cnc_runningtimes | CREATE TABLE `cnc_runningtimes` ( |
`StartTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `EndTime` datetime DEFAULT NULL, `IdWorkStation` smallint(5) unsigned DEFAULT NULL, `UnexpectedWorkStationStatus` tinyint(1) NOT NULL DEFAULT '0', `IdCncRunningTime` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`IdCncRunningTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC | +------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
**** Master *****
** Slave1 ***** MariaDB [(none)]> select version(); +---------------------+
| version() |
+---------------------+
| 10.3.32-MariaDB-log |
+---------------------+ 1 row in set (0.001 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'binlog_format'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+ 1 row in set (0.001 sec)
MariaDB [(none)]> SHOW CREATE TABLE geos.cnc_runningtimes; +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cnc_runningtimes | CREATE TABLE `cnc_runningtimes` ( |
`StartTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `EndTime` datetime DEFAULT NULL, `IdWorkStation` smallint(5) unsigned DEFAULT NULL, `UnexpectedWorkStationStatus` tinyint(1) NOT NULL DEFAULT 0, `IdCncRunningTime` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`IdCncRunningTime`) ) ENGINE=InnoDB AUTO_INCREMENT=262527 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
****slave1*******
***** Slave2 *** MariaDB [(none)]> select version(); +---------------------+
| version() |
+---------------------+
| 10.3.32-MariaDB-log |
+---------------------+ 1 row in set (0.000 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'binlog_format'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+ 1 row in set (0.003 sec)
MariaDB [(none)]> SHOW CREATE TABLE geos.cnc_runningtimes; +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cnc_runningtimes | CREATE TABLE `cnc_runningtimes` ( |
`StartTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `EndTime` datetime DEFAULT NULL, `IdWorkStation` smallint(5) unsigned DEFAULT NULL, `UnexpectedWorkStationStatus` tinyint(1) NOT NULL DEFAULT 0, `IdCncRunningTime` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`IdCncRunningTime`) ) ENGINE=InnoDB AUTO_INCREMENT=261988 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) *****slave2*******
