| Bug #82599 | slave_type_conversions messing with data which still fits within allowed range | ||
|---|---|---|---|
| Submitted: | 16 Aug 2016 14:14 | Modified: | 17 Aug 2016 7:14 |
| Reporter: | Przemyslaw Malkowski | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.7.14,5.6, 5.6.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | replication slave_type_conversions | ||
[17 Aug 2016 7:14]
MySQL Verification Team
Hello Przemyslaw, Thank you for the report. Verified as described with 5.7.14 build. Thanks, Umesh
[2 Jun 2022 20:27]
Fernando Camargos
This bug continues to be observed on MySQL 5.7.38 using the test case previously provided by Przemyslaw Malkowski. Here's a slightly different one, but fundalmentally showcasing the same issue, which renders it impossible to upgrade the schema in a replica first to later promote it as the primary: primary> select @@version,@@version_comment; +------------+------------------------------+ | @@version | @@version_comment | +------------+------------------------------+ | 5.7.38-log | MySQL Community Server (GPL) | +------------+------------------------------+ 1 row in set (0.00 sec) primary > CREATE TABLE test_lossy (id smallint(5) unsigned NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.08 sec) replica > alter table test_lossy modify id int(10) unsigned not null; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 replica > select @@slave_type_conversions; +--------------------------+ | @@slave_type_conversions | +--------------------------+ | ALL_NON_LOSSY | +--------------------------+ 1 row in set (0.00 sec) primary > insert into test_lossy values (36087); Query OK, 1 row affected (0.03 sec) primary > select * from test_lossy; +-------+ | id | +-------+ | 36087 | +-------+ 1 row in set (0.00 sec) replica > select * from test_lossy; +----+ | id | +----+ | 0 | +----+ 1 row in set (0.00 sec)
Description: It may happen that you want to prepare for int column growth and change datatype from int to bigint, so you do it first on the slave. Unfortunately, after passing maximum value for signed int (2147483647), when master has unsigned int, regardless if the slave has singed or unsigned bigint, the values get either truncated to 0 (when unsigned) or stored as negative numbers counting from -2147483647 downwards. How to repeat: master [localhost] {msandbox} (test) > select @@version,@@version_comment; +------------+------------------------------+ | @@version | @@version_comment | +------------+------------------------------+ | 5.7.14-log | MySQL Community Server (GPL) | +------------+------------------------------+ 1 row in set (0.00 sec) master [localhost] {msandbox} (test) > create table c1 (id int unsigned NOT NULL); Query OK, 0 rows affected (0.05 sec) slave1 [localhost] {msandbox} (test) > alter table c1 modify id bigint NOT NULL; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) > insert into c1 values (1),(2147483647),(2147483649),(2147483650); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 master [localhost] {msandbox} (test) > select * from test.c1; +------------+ | id | +------------+ | 1 | | 2147483647 | | 2147483649 | | 2147483650 | +------------+ 4 rows in set (0.00 sec) slave1 [localhost] {msandbox} (test) > select @@slave_type_conversions; +--------------------------+ | @@slave_type_conversions | +--------------------------+ | ALL_NON_LOSSY | +--------------------------+ 1 row in set (0.00 sec) slave1 [localhost] {msandbox} (test) > select * from test.c1; +-------------+ | id | +-------------+ | 1 | | 2147483647 | | -2147483647 | | -2147483646 | +-------------+ 4 rows in set (0.00 sec) Suggested fix: Clearly there is no reason to mess up with values which both fit within int unsigned on master and both signed/unsigned bigint on the slave. The values should be replicated as they were.