MySQL Bugs: #49873: Replication thread exiting (Error 1364)

Bug #49873	Replication thread exiting (Error 1364) - field doesn't have default value
Submitted:	22 Dec 2009 10:47	Modified:	23 Aug 2012 17:47
Reporter:	zohar aharoni	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.37	OS:	Linux (CentOS release 5.2)
Assigned to:		CPU Architecture:	Any
Tags:	1364, default value, error, HA_ERR_ROWS_EVENT_APPLY

Description:
Hi,
I have mysql in replication form with 1 master and 4 slave (all same version 5.1.37).

All slaves stopped working with the error below accept for 1 slave that still working.
The my.cnf file on all servers is the same.

why 3 slaves stopped and 1 is still working?
How can I overcome this problem?

--------------------------------------------------------------------------------
Last_Errno: 1364
	   Last_Error: Could not execute Write_rows event on table s1.t1; 
	   Field 'f1' doesn't have a default value, Error_code: 1364; 
	   Field 'f2' doesn't have a default value, Error_code: 1364; 
	   Field 'f3' doesn't have a default value, Error_code: 1364; 
	   Field 'f4' doesn't have a default value, Error_code: 1364; 
	   Field 'f5' doesn't have a default value, Error_code: 1364; 
	   Field 'f6' doesn't have a default value, Error_code: 1364; 
	   Field 'f7' doesn't have a default value, Error_code: 1364; 
	   Field 'f8' doesn't have a default value, Error_code: 1364; 
	   handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log mysql-bin.011467, end_log_pos 4155

How to repeat:
Don't have an example

Suggested fix:
Maybe the slave should skip this with warning?

Thank you for the report.

Do you have same table definition of s1.t1 on all slaves and master? Please run SHOW CREATE TABLE s1.t1; on each of them.

Sure - 

On the master:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `al` smallint(5) unsigned NOT NULL,
  `label` int(10) unsigned NOT NULL DEFAULT '0',
  `source_p_id` int(11) NOT NULL,
  `source_id` bigint(20) NOT NULL,
  `source_type` tinyint(3) unsigned NOT NULL,
  `target_p_id` int(11) NOT NULL,
  `target_id` bigint(20) NOT NULL,
  `target_type` tinyint(3) unsigned NOT NULL,
  `weight` double NOT NULL,
  PRIMARY KEY (`id`,`source_p_id`),
  UNIQUE KEY `t1_ix` (`source_id`,`al`,`label`,`source_p_id`,`target_id`,`source_type`,`target_type`),
  KEY `t1_target_ix` (`target_id`,`al`) USING BTREE,
  KEY `t1_al_source_type_ix` (`al`,`label`)
) ENGINE=InnoDB AUTO_INCREMENT=2367134441 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

On one of the slaves where it failed:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `al` smallint(5) unsigned NOT NULL,
  `label` int(10) unsigned NOT NULL DEFAULT '0',
  `source_p_id` int(11) NOT NULL,
  `source_id` bigint(20) NOT NULL,
  `source_type` tinyint(3) unsigned NOT NULL,
  `target_p_id` int(11) NOT NULL,
  `target_id` bigint(20) NOT NULL,
  `target_type` tinyint(3) unsigned NOT NULL,
  `weight` double NOT NULL,
  PRIMARY KEY (`id`,`source_p_id`),
  UNIQUE KEY `t1_ix` (`source_id`,`al`,`label`,`source_p_id`,`target_id`,`source_type`,`target_type`),
  KEY `t1_target_ix` (`target_id`,`al`) USING BTREE,
  KEY `t1_al_source_type_ix` (`al`,`label`)
) ENGINE=InnoDB AUTO_INCREMENT=2366498705 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

On the slave it worked:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `al` smallint(5) unsigned NOT NULL,
  `label` int(10) unsigned NOT NULL DEFAULT '0',
  `source_p_id` int(11) NOT NULL,
  `source_id` bigint(20) NOT NULL,
  `source_type` tinyint(3) unsigned NOT NULL,
  `target_p_id` int(11) NOT NULL,
  `target_id` bigint(20) NOT NULL,
  `target_type` tinyint(3) unsigned NOT NULL,
  `weight` double NOT NULL,
  PRIMARY KEY (`id`,`source_p_id`),
  UNIQUE KEY `t1_ix` (`source_id`,`al`,`label`,`source_p_id`,`target_id`,`source_type`,`target_type`),
  KEY `t1_target_ix` (`target_id`,`al`) USING BTREE,
  KEY `t1_al_source_type_ix` (`al`,`label`)
) ENGINE=InnoDB AUTO_INCREMENT=2367137404 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I suggest that the slaves are running different sql_modes?

The slaves that fail have *strict* mode and the one that did not have *non-strict* mode?

Am I right?

If so the problem is that INSERTING to a table having a column declared NOT NULL with no DEFAULT fails in *Strict* mode if not that particular column is listed in the INSERT statement.

No.
As I wrote - my.cnf is similar in all slaves (1 is OK, 3 isn't)

The following on all servers (including the master) shows:
mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+

Strange then.  The error 'fields doesn't have default value' tells that the server was not able to perform an INSERT as 
* column was not listed in the INSERT statement
* NULL is not allowed
* there is no DEFAULT
* zero/empty string cannot be inserted (what indicates *strict* mode).
(and then nothing is legal and the only thing the server can do is to return an error)

I believe it would solve the issue to set defaults (ZERO for numbers, '' (empty string) for strings). I would always explicitly set defaults for NOT NULL columns. But this should be sorted out of course. Still looks to me like the replication thread has *strict* mode for its session (at least).

(btw: I forgot to tell that I am not a MySQL person)

Thanks Peter for your help!

One question:
The table definition is the same on the master and on the slave.
So - if it failed on the slave it should also had to fail on the master. 
Am I right?

We never worked with sql_mode and it has the default value (which I think is '').
If it did failed on the master I would expect it not to be in the log...

Please, provide the results of SHOW CREATE TABLE from one of the slaves where it failed.

Thank you for the feedback.

This looks like SQL_MODE problem, but if you use same config on each slave this should be not the case.

Do you run slaves on the same OS? Is it possible if 3 failed slaves are running on Windows?

Please send us configuration files for all servers you use (master, working slave and not working slave).

If you can upload problem binary log file this would be good too.

Zohar,

Ignore my last request, please. I missed the result from failing slave. It is the same (with exception of AUTO_INCREMENT=... clause). Sorry.

All my databases are on the same type of machines/os.

I attached my configuration files:

my.master.txt
my.slave1.txt - replication worked
my.slave2.txt - replication failed

Thanks.

Thank you for the feedback.

Only difference is problem slave has options log-bin and binlog-format. But I can not repeat error with dummy data and these options. Do you have triggers on the table or triggers on other tables which update this table?

Thanks for your reply.

No. I don't use triggers at all.
It is not the first time that I have this kind of a problem where some of the slaves recover and some don't...

BTW - 
I forgot to mention that the table is partitioned - 
PARTITION BY LIST (source_p_id). not sure if it relevant...

Could it be related to network troubles?

Thank you for the feedback.

> PARTITION BY LIST (source_p_id). 

Are all tables on all servers partitioned? Could you please send output of SHOW CREATE TABLE for partitioned table.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I ask finally how to solve it? I also encountered the same problem.
My OS version is not the same, master used centos5.4, slave used centos5.3 , and there are not the same hardware configuration , which is the reason ?
(Each machine mysql version, the configuration is the same.)

thank you very much!

My mysql structure is such a chain : A-> B-> C-> D
A is the master. B,C,D are the slave .Exec a same insert query, problem occurs in B -> C ,but A-> B is normal.

error:
Last_SQL_Errno: 1364
Last_SQL_Error: Error 'Field 'strShipAddress' doesn't have a default value' on query......

show create table:
CREATE TABLE `tb_x` (
  `lId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '',
  ......
  `strShipAddress` text NOT NULL COMMENT '',
  ......
  PRIMARY KEY (`lId`)
) ENGINE=InnoDB AUTO_INCREMENT=400000001496 DEFAULT CHARSET=utf8

-----
mysql version : 5.1.50

A,B used centos 5.4 and the same hardware.
C,D used centos 5.3 and the same hardware.

z l,

thank you for the feedback.

To determine if this is MySQL bug we must repeat this problem first and we can not. Please provide repeatable test case: configuration of all servers, CREATE TABLE statement, query you use.

You also use pretty old version of MySQL. Try to upgrade it to latest version 5.1.63 and check if problem is fixed already.

@Sveta .. to me it looks like you should have asked for 'sql_mode's on master an slave as well.

I think this is easily explainable with statement-based replication setup if slave is running 'strict mode' and master is not.

Peter,

thank you for the feedback, but original reporter claims his SQL mode is not strict (see discussion with you in year 2009).

z l,

please check SQL mode anyway: this is cause of 99% of similar issues and not a MySQL bug, of course.

Sveta ,thank you. I also think this is not a BUG, but I hope to get help.

My mysql structure is such a chain : A-> B-> C
The insert statement sync successfully on B, failed on C.

Export B's binlog we can see : SET @ session.sql_mode = 2097152 / * ! * / ; So B can normal sync data from master A.

Of course export C's relay-log we can also see: SET @ session.sql_mode = 2097152 / * / ; but there is a error like: Error 'Field 'strShipAddress' doesn't have a default value' on query ,So C can not normally sync data from B.

Normal circumstances , the statement is sent and executed by JAVA program . The strange thing is that on one occasion I manually exec the insert statement on master A, and found that all slaves are executed successfully .Then Export from all the slave's binlog found : SET @ session.sql_mode = 0 / * / ;

Then, i create a test database on master A, and create the same table in the test database, and run the insert statement several times, it was successful in all slaves, all the slaves can normally sync. Then export all the slave's binlog found : SET @ session.sql_mode = 0 / * ! * / ;

Conclusion: If the sql_mode = 0 , we will certainly be successful. if it is sql_mode = 2097152, B will successful only, C will failed  .

But what impact the sql_mode's value ? I has been asked to our developers , they answer me the JAVA program didn't set up the value of sql_mode.

thank you !

Sveta ,thank you. I also think this is not a BUG, but I hope to get help.

My mysql structure is such a chain : A-> B-> C
The insert statement sync successfully on B, failed on C.

Export B's binlog we can see : SET @ session.sql_mode = 2097152 / * ! * / ; So B can normal sync data from master A.

Of course export C's relay-log we can also see: SET @ session.sql_mode = 2097152 / * / ; but there is a error like: Error 'Field 'strShipAddress' doesn't have a default value' on query ,So C can not normally sync data from B.

Normal circumstances , the statement is sent and executed by JAVA program . The strange thing is that on one occasion I manually exec the insert statement on master A, and found that all slaves are executed successfully .Then Export from all the slave's binlog found : SET @ session.sql_mode = 0 / * / ;

Then, i create a test database on master A, and create the same table in the test database, and run the insert statement several times, it was successful in all slaves, all the slaves can normally sync. Then export all the slave's binlog found : SET @ session.sql_mode = 0 / * ! * / ;

Conclusion: If the sql_mode = 0 , we will certainly be successful. if it is sql_mode = 2097152, B will successful only, C will failed  .

But what impact the sql_mode's value ? I has been asked to our developers , they answer me the JAVA program didn't set up the value of sql_mode.

thank you !

Thank you for the feedback.

SQL mode 2097152 is STRICT_TRANS_TABLES. Looks like tables on your master and slave B use storage engine MyISAM while on slave C storage engine InnoDB. Please fix your application. This is not a bug.

Status of the report set to "Need feedback" since original reporter had not provided answer on my past question.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".