incorrect type conversions for different column types

[Jericho Rivera]

This bug is similar to upstream bug: https://bugs.mysql.com/bug.php?id=82599

On RBR formats, converting tinyint to smallint (or for the upstream bug int to bigint) with slave_type_conversions=ALL_NON_LOSSY results in incorrect value on slave.

Affects PS 5.5,5.6,5.7

Running test...
Check MySQL versions
Master
+--------------+---------------------------------------------------+-----------------+
| @@version | @@version_comment | @@binlog_format |
+--------------+---------------------------------------------------+-----------------+
| 5.7.14-7-log | Percona Server (GPL), Release 7, Revision 083e298 | ROW |
+--------------+---------------------------------------------------+-----------------+
Slave
+--------------+---------------------------------------------------+-----------------+
| @@version | @@version_comment | @@binlog_format |
+--------------+---------------------------------------------------+-----------------+
| 5.7.14-7-log | Percona Server (GPL), Release 7, Revision 083e298 | ROW |
+--------------+---------------------------------------------------+-----------------+

Drop table test.type_convert if it exists

Creating table

Table created
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| type_convert | CREATE TABLE `type_convert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `providerid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Insert record with providerid = 128, Slave table is identical
+----+------------+
| id | providerid |
+----+------------+
| 1 | 128 |
+----+------------+

Check Slave data
+----+------------+
| id | providerid |
+----+------------+
| 1 | 128 |
+----+------------+

Set slave_type_conversions to ALL_NON_LOSSY
+---------------------------------+
| @@global.slave_type_conversions |
+---------------------------------+
| ALL_NON_LOSSY |
+---------------------------------+

Running \'ALTER TABLE test.type_convert MODIFY providerid SMALLINT UNSIGNED NOT NULL\'

Show create table on Slave
*************************** 1. row ***************************
      Table: type_convert
Create Table: CREATE TABLE `type_convert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `providerid` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

Insert in Master, Slave table is not identical

Check data in Master
+----+------------+
| id | providerid |
+----+------------+
| 1 | 128 |
| 2 | 128 |
+----+------------+

Check data in Slave
+----+------------+
| id | providerid |
+----+------------+
| 1 | 128 |
| 2 | 0 |
+----+------------+

In MariaDB 5.5.50 at least conversion is done right and slave data is identical with master.

slave1 [localhost] {msandbox} (test) > select * from type_convert;
+--------+------------+
| AutoId | providerid |
+--------+------------+
| 1 | 128 |
| 2 | 128 |
+--------+------------+
2 rows in set (0.00 sec)

binlog_format=ROW
slave_type_conversions=ALL_NON_LOSSY /*on_slave*/

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1026