We often see an int column of a table that needs to be changed to unsigned-int and then unsigned-bigint due to the value being out of range. Sometimes, there may even be blockers that prevent us from directly altering the table or applying pt-online-schema-change on the primary, which requires the rotation solution: apply the change on the replica first, switch over the writes to the replica, and then apply the change on the previous primary. In this case, MySQL will have to replicate unsigned-int to unsigned-bigint for a while.
One might think it is obvious and straightforward that MySQL should be able to replicate unsigned-int to unsigned-bigint because unsigned-bigint has a larger size(8 bytes) which covers unsigned-int(4 bytes). It is partly true, but there are some tricks in practice. This blog will show you those tricks through the scenarios.
Let’s understand the scenarios and issues that one may face when replicating from unsigned-int in primary to unsigned-bigint in replica. For scenarios one and two, we will only focus on the binlog_format=ROW, because with binlog_format=STATEMENT, “If the statement run on the source would also execute successfully on the replica, it should also replicate successfully” – MySQL doc. However, for scenario three, we tested for both binlog_format=ROW and binlog_format=STATEMENT.
First, set up the table. Here we have a table test_replication_differ_type with unsigned int in primary and unsigned bigint in replica.
Primary: Server version: MySQL 8.0.28
Table:
1 2 3 4 5 | CREATE TABLE `test_replication_differ_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `replicate_col` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Replica: Server version: MySQL 8.0.28
Table:
1 2 3 4 5 | CREATE TABLE `test_replication_differ_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `replicate_col` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Scenario one: Insert into an unsigned int in primary and replicate to unsigned bigint
on Primary:
1 2 3 4 5 6 7 8 | mysql> insert into test.test_replication_differ_type(id,replicate_col) values(1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | +----+---------------+ |
on Replica:
1 2 3 4 | root@localhost [test]> show replica status\G ... <strong>Last_SQL_Errno: 1677</strong> Last_SQL_Error: Column 1 of table 'test.test_replication_differ_type' cannot be converted from type 'int' to type 'bigint(20)' |
Analysis and solution:
Immediately we see an error stopping us from replicating. Now there’s a configuration option “slave_type_conversions” which controls the type conversion mode used on the replica. The reason for this replication error is because of that, on the replica, the slave_type_conversions variable is NOT set by default.
1 2 3 4 5 6 | root@localhost [test]> show variables like 'slave_type%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | slave_type_conversions | | +------------------------+-------+ |
Setting slave_type_conversions to the mode ALL_LOSSY will not work because: ”requiring either lossy conversions or no conversion at all are permitted; for example, enabling only this mode permits an INT column to be converted to TINYINT (a lossy conversion), but not a TINYINT column to an INT column (non-lossy).” – MySQL doc
1 2 3 4 5 6 7 8 | root@localhost [test]> set global slave_type_conversions='ALL_LOSSY'; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> start slave; Query OK, 0 rows affected (0.01 sec) root@localhost [test]> show slave status\G ... Last_Errno: 1677 Last_Error: Column 1 of table 'test.test_replication_differ_type' cannot be converted from type 'int' to type 'bigint(20)' |
Setting slave_type_conversions to the mode ALL_NON_LOSSY will work because the mode: “This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.” – MySQL doc
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY'; Query OK, 0 rows affected (0.00 sec) root@localhost [test]> start slave; Query OK, 0 rows affected (0.01 sec) root@localhost [test]> show slave status\G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 0root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | +----+---------------+ |
Don’t be confused by the name, ALL_LOSSY and ALL_NON_LOSSY are not exclusive, instead, they can be parallelly added to permit both modes:
On Replica:
1 | root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY, ALL_LOSSY'; |
On Primary:
1 2 3 4 5 6 7 8 9 | mysql> insert into test.test_replication_differ_type(id,replicate_col) values(2,2); Query OK, 1 row affected (0.00 sec) root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | +----+---------------+ |
On Replica:
1 2 3 4 5 6 7 | root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | +----+---------------+ |
Thus, if you want to replicate with different data types, consider using the mode ALL_NON_LOSSY and/or ALL_LOSSY in the global variable slave_type_conversions appropriately.
Scenario two: Insert a value out-of-range of signed int and replicate to unsigned bigint
The range value of a signed int is (-2147483648, 2147483647). Let’s try to insert 2147483647+1 into an unsigned int column on the primary and observe how that replicates. Should be okay right?
On Primary:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> insert into test.test_replication_differ_type(id, replicate_col) values(3, 2147483647); Query OK, 1 row affected (0.00 sec) mysql> insert into test.test_replication_differ_type(id, replicate_col) values(4, 2147483648); Query OK, 1 row affected (0.00 sec) mysql> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | <strong>2147483648</strong> | +----+---------------+ 4 rows in set (0.00 sec) |
On Replica:
1 2 3 4 5 6 7 8 9 10 | root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | <strong> 0</strong> | +----+---------------+ 4 rows in set (0.00 sec) |
Uh-oh, we insert 2147483648 on the primary but got a 0 on the replica.
Analysis and solution:
Checking the binlog file of primary, we can see that the unsigned int value 2147483648 was written as @2=-2147483648 (2147483648) as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | root@db2:/home/vagrant# mysqlbinlog -vvv /var/lib/mysql/mysql-bin.000010 > test_convert.sql root@db2:/home/vagrant# cat test_convert.sql ... # at 2635 #220821 4:55:56 server id 2 end_log_pos 2679 CRC32 0x85dfff8a Write_rows: table id 113 flags: STMT_END_F BINLOG ' 3LoBYxMCAAAASAAAAEsKAAAAAHEAAAAAAAEABHRlc3QAHHRlc3RfcmVwbGljYXRpb25fZGlmZmVy X3R5cGUAAgMDAAKLYMRs 3LoBYx4CAAAALAAAAHcKAAAAAHEAAAAAAAEAAgAC//wEAAAAAAAAgIr/34U= '/*!*/; ### INSERT INTO `test`.`test_replication_differ_type` ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ <strong>### @2=-2147483648 (2147483648) /* INT meta=0 nullable=1 is_null=0 */</strong> # at 2679 #220821 4:55:56 server id 2 end_log_pos 2710 CRC32 0x532d66ec Xid = 89 COMMIT/*!*/; |
However, “When an integer type is promoted, its signedness is not preserved. By default, the replica treats all such values as signed”, and so -2147483648 (2147483648) is treated as signed value -2147483648, and eventually turns into 0 as for the minimum of unsigned bigint is 0.
We need to tell the MySQL replica to treat the value -2147483648 (2147483648) as unsigned int instead of signed int by adding a mode ALL_UNSIGNED to the variable slave_type_conversions as below example:
On Replica
1 2 | root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_UNSIGNED'; Query OK, 0 rows affected (0.00 sec) |
On Primary
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> insert into test.test_replication_differ_type(id,replicate_col) values(5,2147483649); Query OK, 1 row affected (0.00 sec) mysql> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | 2147483648 | | <strong> 5 | 2147483649</strong> | +----+---------------+ 5 rows in set (0.00 sec) |
On Replica
1 2 3 4 5 6 7 8 9 10 11 | root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | 0 | | <strong> 5 | 2147483649</strong> | +----+---------------+ 5 rows in set (0.00 sec) |
We can also add the mode as ALL_SIGNED, but this time, it is not like ALL_LOSSY and ALL_NON_LOSSY which are parallel. ALL_SIGNED has a higher priority than ALL_UNSIGNED, which means MySQL will first treat the value as signed if possible, otherwise treat the value as unsigned.
On Replica
1 2 | root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED,ALL_SIGNED'; Query OK, 0 rows affected (0.00 sec) |
On Primary
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> insert into test.test_replication_differ_type(id,replicate_col) values(6,2147483650); Query OK, 1 row affected (0.01 sec)mysql> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | 2147483648 | | 5 | 2147483649 | | <strong> 6 | 2147483650</strong> | +----+---------------+ 6 rows in set (0.00 sec) |
On Replica
1 2 3 4 5 6 7 8 9 10 11 12 | root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | 0 | | 5 | 2147483649 | | <strong>6 | 0 |</strong> +----+---------------+ 6 rows in set (0.00 sec) |
Scenario three: Replica with an extra column and not all common columns are of the same data type.
The documentation reads: “In addition, when the replica’s copy of the table has more columns than the source’s copy, each column common to the tables must use the same data type in both tables.” – MySQL doc
Let us verify if that’s the behavior.
On Replica
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | root@localhost [test]> set global slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED'; Query OK, 0 rows affected (0.00 sec)root@localhost [test]> alter table test.test_replication_differ_type add column extra_col int unsigned; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [test]> show create table test.test_replication_differ_type\G *************************** 1. row *************************** Table: test_replication_differ_type Create Table: CREATE TABLE `test_replication_differ_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `replicate_col` bigint(20) unsigned DEFAULT NULL, <strong>`extra_col` int(10) unsigned DEFAULT NULL,</strong> PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
On Primary
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> show create table test.test_replication_differ_type\G *************************** 1. row *************************** Table: test_replication_differ_type Create Table: CREATE TABLE `test_replication_differ_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `replicate_col` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)mysql> insert into test.test_replication_differ_type(id,replicate_col) values(7,2147483651); Query OK, 1 row affected (0.00 sec) root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+ | id | replicate_col | +----+---------------+ | 1 | 1 | | 2 | 2 | | 3 | 2147483647 | | 4 | 2147483648 | | 5 | 2147483649 | | 6 | 2147483650 | <strong>| 7 | 2147483651 |</strong> +----+---------------+ 7 rows in set (0.00 sec) |
On Replica
1 2 3 4 5 6 7 8 9 10 11 12 13 | root@localhost [test]> select * from test.test_replication_differ_type; +----+---------------+-----------+ | id | replicate_col | extra_col | +----+---------------+-----------+ | 1 | 1 | NULL | | 2 | 2 | NULL | | 3 | 2147483647 | NULL | | 4 | 0 | NULL | | 5 | 2147483649 | NULL | | 6 | 0 | NULL | <strong>| 7 | 2147483651 | NULL |</strong> +----+---------------+-----------+ 7 rows in set (0.00 sec) |
Now, as our experiment says, the statement in the documentation isn’t correct. The fact is that even if the replicate_col has data type bigint(20) unsigned on the replica other than the int(10) unsigned on the primary, and the replica has an extra column extra_col, it can still replicate well.
I already filed a bug report to Oracle MySQL.
Conclusion
In this blog, I illustrated how to set up the variable slave_type_conversions, the purpose and the difference between ALL_NON_LOSSY and ALL_LOSSY modes, as well as ALL_UNSIGNED and ALL_SIGNED.
Specifically, in order to have MySQL replicate from unsigned-int to unsigned-bigint correctly, we need to set up the variable slave_type_conversions=’ALL_NON_LOSSY,ALL_UNSIGNED’ OR slave_type_conversions=’ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED’
Also please note that when the replica has an extra column than the primary, as long as the common columns are the same name and in the same order and before the extra column, even when a common column data type is different from the primary, the replication can still go well.
Nice post Edwin, just two comments:
1) please use the new terminology and commands (ex: SHOW REPLICA STATUS\G)
2) use html code like pre for a better layout of the post.
Cheers,