MySQL Bugs: #38173: " Field doesn't have a default value " with row-based replication

Bug #38173	" Field doesn't have a default value " with row-based replication
Submitted:	16 Jul 2008 14:11	Modified:	15 Mar 2010 4:40
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S2 (Serious)
Version:	6.0	OS:	Any
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
When executing a concurrent (2 threads, 20 queries total) workload , involving manual switching between statement and row-based replication, the slave stops as follows:

080716 17:01:51 [ERROR] Slave SQL: Could not execute Write_rows event on table test.C; Field 'int_key' doesn't have a default value, Error_code: 1364; Field 'date_key' doesn't have a default value, Error_code: 1364; Field 'date_nokey' doesn't have a default value, Error_code: 1364; Field 'time_key' doesn't have a default value, Error_code: 1364; Field 'time_nokey' doesn't have a default value, Error_code: 1364; Field 'datetime_key' doesn't have a default value, Error_code: 1364; Field 'datetime_nokey' doesn't have a default value, Error_code: 1364; Field 'varchar_key' doesn't have a default value, Error_code: 1364; Field 'varchar_nokey' doesn't have a default value, Error_code: 1364; handler error HA_ERR_ROWS_EVENT_APPLY; the event's master log master-bin.000001, end_log_pos 77968, Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'int_key' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'date_key' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'date_nokey' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'time_key' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'time_nokey' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'datetime_key' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'datetime_nokey' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'varchar_key' doesn't have a default value Error_code: 1364
080716 17:01:51 [Warning] Slave: Field 'varchar_nokey' doesn't have a default value Error_code: 1364
080716 17:01:51 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000001' position 77806

The master reports a bunch of warnings like:
080716 17:01:51 [Warning] Statement is not safe to log in statement format. Statement: DELETE FROM C WHERE `int_nokey` < 8 LIMIT 1 ; SET BINLOG_FORMAT = ROW

Engine is MyISAM, so this statement must be safe?

How to repeat:
A repeatable test case and the binary log will be attached shortly.

Log files for bug 38173

Attachment: bug38173-logs.zip (application/x-zip-compressed, text), 47.55 KiB.

Grammar file for bug 38173

Attachment: bug38173.yy (application/octet-stream, text), 722 bytes.

To reproduce this issue, please clone the mysql-test-extra-6.0 tree (and pull it to its latest revision) and then execute:

$ cd mysql-test-extra-6.0/mysql-test/gentest
$ perl runall.pl \
  --basedir=/path/to/mysql-6.0 --rpl_mode=row \
  --grammar=/location/of/bug38173.yy --threads=2 --queries=10

5.1 does not appear to be affected.

This bug was just observed without any statement-based replication, a workload of pure row-based inserts is sufficient.

Thank you for the report.

Verified as described.

Here is a simplifed test case:

--source include/master-slave.inc
--disable_abort_on_error
SET BINLOG_FORMAT='ROW';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
        pk INTEGER AUTO_INCREMENT,
        int_nokey INTEGER NOT NULL,
        int_key INTEGER NOT NULL,
        PRIMARY KEY (pk),
        KEY (int_key)
);

INSERT INTO t1 ( `pk` , `int_key` ) VALUES ( '6' , '1' );
INSERT INTO t1 ( `pk` , `int_key` ) VALUES ( '6' , '1' );

--connection master
DROP TABLE t1;
--save_master_pos
--connection slave
--sync_with_master

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71230

2836 Andrei Elkin	2009-04-02
      Bug #38173 Field doesn't have a default value with row-based replication
      
      The reason for the bug was incompatibile with the master side behaviour.
      INSERT query on the master is allowed to insert into a table without specifying
      values of DEFAULT-less fields as well provided that 
      sql_mode is not strict.
      
      Fixed with checking sql_mode by the sql thread to decide how to react.
      Not strict sql_mode should allow Write_rows event to complete.
      
      todo: warnings can be shown via show slave status, still this is a separate rather 
      general issue how to show warnings for the slave threads.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71640

2836 Andrei Elkin	2009-04-08
      Bug #38173 Field doesn't have a default value with row-based replication
      
      The reason of  the bug was incompatibile with the master side behaviour.
      INSERT query on the master is allowed to insert into a table without specifying
      values of DEFAULT-less fields if sql_mode is not strict.
            
      Fixed with checking sql_mode by the sql thread to decide how to react.
      Non-strict sql_mode should allow Write_rows event to complete.
            
      todo: warnings can be shown via show slave status, still this is a 
            separate rather general issue how to show warnings for the slave threads.

Pushed to 6.0-rpl.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71834

2838 Alfranio Correia	2009-04-10
      Post-fix for BUG#38173.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090416165424-s5jp8a50cqy9zghk) (version source revid:alfranio.correia@sun.com-20090410000136-7ic8ajqwc02tr4tz) (merge vers: 6.0.11-alpha) (pib:6)

Documented in the 6.0.11 changelog as follows:

    When using row-based format, replication failed when an INSERT was 
    made on the master without specifying a value for a column with no 
    default, even if strict server SQL mode was not in use and the 
    statement would otherwise have succeeded on the master. Now the SQL 
    mode is checked, and the statement is replicated unless strict mode 
    is in effect. For more information, see "Server SQL Modes".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85024

3116 Alfranio Correia	2009-09-29
      BUG#38173 Field doesn't have a default value with row-based replication
      
      NOTE: Backporting the patch to next-mr.
            
      The reason of  the bug was incompatibile with the master side behaviour.
      INSERT query on the master is allowed to insert into a table without specifying
      values of DEFAULT-less fields if sql_mode is not strict.
                  
      Fixed with checking sql_mode by the sql thread to decide how to react.
      Non-strict sql_mode should allow Write_rows event to complete.
                  
      todo: warnings can be shown via show slave status, still this is a 
      separate rather general issue how to show warnings for the slave threads.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091027094604-9p7kplu1vd2cvcju) (version source revid:zhenxing.he@sun.com-20091026140226-uhnqejkyqx1aeilc) (merge vers: 6.0.14-alpha) (pib:13)

Already documented in 6.0.11.

Closed.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091027095744-rf45u3x3q5d1f5y0) (merge vers: 5.5.0-beta) (pib:13)

Also documented in the 5.5.0 changelog; closed.

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091125130912-d7hrln14ef7y5d7i) (merge vers: 5.1.42) (pib:13)

Also documented in the 5.1.42 changelog; closed.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091124083136-iqm136jm31sfdwg3) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Also documented in the 5.6.0 changelog; closed.

5.6.0 changelog entry unneeded.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No new changelog entries required. Closed.