enterprise-support team mailing list archive

[Jericho Rivera <jericho.rivera@xxxxxxxxxxx>]

Public bug reported:

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*/

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: Confirmed


** Tags: i146878

** Bug watch added: MySQL Bug System #82599
   http://bugs.mysql.com/bug.php?id=82599

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=82599
   Importance: Unknown
       Status: Unknown

** Changed in: percona-server
       Status: New => Confirmed

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1637076

Title:
  incorrect type conversions for different column types

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1637076/+subscriptions