MySQL Bugs: #38850: wrong data on NULL definition doesn't match

Bug #38850	wrong data on NULL definition doesn't match
Submitted:	18 Aug 2008 11:52	Modified:	3 Apr 2009 18:23
Reporter:	Jan Kneschke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S1 (Critical)
Version:	5.1.26, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If the NULL/NUT NULL definition on master and slave aren't matching, the slave may apply data incorrectly without throwing an error:

master: CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16), i2 int NOT NULL);
slave:  CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16) not null, i2 int NOT NULL);

master: INSERT INTO t1 VALUES ( 1, NULL, 1);

master: select * from test.t1;
+----+------+----+
| i1 | c1   | i2 |
+----+------+----+
|  1 | NULL |  1 |
+----+------+----+

slave: select * from test.t1;
+----+----+---------+
| i1 | c1 | i2      |
+----+----+---------+
|  1 |    | 3211264 |
+----+----+---------+

This bug is closely related to Bug#38849

How to repeat:
--connect (master,127.0.0.1,root,,test,3306)
--connect (slave,127.0.0.1,root,,test,3307)
--connection master
use test;
set SQL_LOG_BIN=0;
set session binlog_format=row;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16), i2 int NOT NULL);

--connection slave
use test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16) not null, i2 int NOT NULL);

--connection master
use test;
set SQL_LOG_BIN=1;
INSERT INTO t1 VALUES ( 1, NULL, 1);
SELECT * FROM t1;
--sync_slave_with_master
SELECT * FROM t1;

Suggested fix:
Deny non-matching NULL definitions.

Thank you for the report.

I can not repeat described behavior in my environment. Please provide slave configuration file.

Thank you for the report.

Verified on 32-bit Linux with a bit different results and 5.1.26. Debug bzr builds crash.

Modified tes case:

$cat rpl_bug38850.test
--source include/master-slave.inc
--connection master
use test;
set SQL_LOG_BIN=0;
set session binlog_format=row;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16), c2 char(16), i2 int NOT NULL);

--connection slave
use test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 INT NOT NULL, c1 CHAR(16) not null, c2 char(16), i2 int NOT NULL);

--connection master
use test;
set SQL_LOG_BIN=1;
set session binlog_format=row;
INSERT INTO t1 VALUES ( 1, NULL, "a", 1);
INSERT INTO t1 VALUES ( 2, "b", "c", 2);
SELECT * FROM t1;
sleep 1;
connection slave;
SELECT * FROM t1;

Results with 5.1.26:

SELECT * FROM t1;
i1      c1      c2      i2
1       NULL    a       1
2       b       c       2
SELECT * FROM t1;
i1      c1      c2      i2
1       a               -1862270976
2       b       c       2

Trace for 6.0 bzr:
mysqld: log_event.h:3491: int Rows_log_event::unpack_current_row(const Relay_log_info*, const MY_BITMAP*): Assertion `m_curr_row_end <= m_rows_end' failed.
/users/ssmirnova/src/mysql-6.0/sql/mysqld(my_print_stacktrace+0x26)[0x8918105]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(handle_segfault+0x2f2)[0x82f0c3a]
[0x2ce420]
/lib/libc.so.6(abort+0xf8)[0x316678]
/lib/libc.so.6(__assert_fail+0xfd)[0x30e269]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN14Rows_log_event18unpack_current_rowEPK14Relay_log_infoPK9st_bitmap+0x122)[0x83f692c]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN14Rows_log_event9write_rowEPK14Relay_log_infob+0x10f)[0x83f3479]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN20Write_rows_log_event11do_exec_rowEPK14Relay_log_info+0x87)[0x83f3ead]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0x8b0)[0x83ef55c]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN9Log_event11apply_eventEPK14Relay_log_info+0x1f)[0x848fe6d]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_infob+0x200)[0x848a032]
/users/ssmirnova/src/mysql-6.0/sql/mysqld[0x848c45b]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(handle_slave_sql+0x7d5)[0x848cfd1]
/lib/libpthread.so.0[0x45fbd4]
/lib/libc.so.6(__clone+0x5e)[0x3b74fe]
080818 19:39:44 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60037 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x9933f68
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xad186430 thread_stack 0x30000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at (nil) is an invalid pointer
thd->thread_id=4
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file

For 5.1 trace is similar.

Strict mode does not affect results of tests: with 5.1.26 wrong data, with current bzr crash.

Workaround: ensure master & slave have matching table definitions

See BUG#43789.