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
Triage: Triaged: D5 (Feature request)

[18 Aug 2008 13:15] Jan Kneschke
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.
[18 Aug 2008 13:17] Mats Kindahl
Incorrect data is also generated for statement-based replication.
[18 Aug 2008 13:21] Jan Kneschke
The initial description is missing "SET SESSION binlog_format=row" in the first case to trigger RBR events to be written to the logs.
[18 Aug 2008 18:42] Sveta Smirnova
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;