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
Triage: Triaged: D2 (Serious)

[16 Jul 2008 14:11] Philip Stoev
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.
[16 Jul 2008 14:16] Philip Stoev
Log files for bug 38173

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

[16 Jul 2008 14:19] Philip Stoev
Grammar file for bug 38173

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

[16 Jul 2008 14:28] Philip Stoev
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.
[16 Jul 2008 20:00] Philip Stoev
This bug was just observed without any statement-based replication, a workload of pure row-based inserts is sufficient.
[30 Jul 2008 19:38] Sveta Smirnova
Thank you for the report.

Verified as described.
[10 Aug 2008 16:08] Philip Stoev
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
[2 Apr 2009 16:58] Bugs System
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.
[8 Apr 2009 13:10] Bugs System
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.
[9 Apr 2009 16:56] Andrei Elkin
Pushed to 6.0-rpl.
[10 Apr 2009 0:02] Bugs System
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.
[16 Apr 2009 16:57] Bugs System
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)
[30 May 2009 7:39] Jon Stephens
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".
[29 Sep 2009 14:04] Bugs System
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.
[27 Oct 2009 9:49] Bugs System
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)
[27 Oct 2009 19:15] Jon Stephens
Already documented in 6.0.11.

Closed.
[12 Nov 2009 8:18] Bugs System
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)
[12 Nov 2009 12:14] Jon Stephens
Also documented in the 5.5.0 changelog; closed.
[2 Dec 2009 8:05] Bugs System
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)
[3 Dec 2009 11:31] Jon Stephens
Also documented in the 5.1.42 changelog; closed.
[16 Dec 2009 8:38] Bugs System
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)
[16 Dec 2009 8:45] Bugs System
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)
[16 Dec 2009 8:51] Bugs System
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)
[16 Dec 2009 15:47] Jon Stephens
Also documented in the 5.6.0 changelog; closed.
[8 Mar 2010 0:12] Paul Dubois
5.6.0 changelog entry unneeded.
[12 Mar 2010 14:11] Bugs System
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)
[12 Mar 2010 14:27] Bugs System
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)
[12 Mar 2010 14:43] Bugs System
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)
[15 Mar 2010 4:40] Jon Stephens
No new changelog entries required. Closed.