MySQL Bugs: #38854: wrong data if SIGNED/UNSIGNED bit doesn't match

Bug #38854	wrong data if SIGNED/UNSIGNED bit doesn't match
Submitted:	18 Aug 2008 13:15	Modified:	18 Aug 2008 18:42
Reporter:	Jan Kneschke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.26, 5.1, 6.0 bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
master: CREATE TABLE t1 (i1 TINYINT SIGNED);
slave:  CREATE TABLE t1 (i1 TINYINT UNSIGNED);

master: INSERT INTO t1 VALUES ( -1);

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

slave:  select * from test.t1;
+------+
| i1   |
+------+
|  255 |
+------+

In SBR the same INSERT would lead to:

slave:  select * from test.t1;
+------+
| i1   |
+------+
|    0 |
+------+

inserting the smallest possible value into the column

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 TINYINT SIGNED);

--connection slave
use test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 TINYINT UNSIGNED);

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

Suggested fix:
As the protocol doesn't carry a signed/unsigned information the slave's or client's can't interpret the value correctly.

The protocol should be extended to store more meta-information of columns in the table-map event to be able to deny replicating if the column definitions of master and slave don't match.

Incorrect data is also generated for statement-based replication.

The initial description is missing "SET SESSION binlog_format=row" in the first case to trigger RBR events to be written to the logs.

Thank you for the report.

Verified as described.

I think bug here is only inconsistence between statement based and row based repolication.

Verified with a bit modified test case:

--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 TINYINT SIGNED);

--connection slave
use test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 TINYINT UNSIGNED);

--connection master
use test;
set SQL_LOG_BIN=1;
SET SESSION binlog_format=row;
INSERT INTO t1 VALUES ( -1);
SET SESSION binlog_format=statement;
INSERT INTO t1 VALUES ( -1);
SELECT * FROM t1;
--sync_slave_with_master
connection slave;
SELECT * FROM t1;