MySQL Bugs: #108234: A Document incorrect regarding Replication with Differing Table Definitions

Bug #108234	A Document incorrect regarding Replication with Differing Table Definitions
Submitted:	23 Aug 2022 7:00	Modified:	3 Oct 2022 12:29
Reporter:	Edwin Wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	mysql 8.0.28	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
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.”
https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

But the statement is incorrect

The following example shows 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. -- see the how to repeat

How to repeat:
On Primary
mysql>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;

On Slave 
mysql>CREATE TABLE `test_replication_differ_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `replicate_col` bigint(20) unsigned DEFAULT NULL,
  `extra_col` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql>set global slave_type_conversions='ALL_NON_LOSSY,ALL_LOSSY,ALL_UNSIGNED';

On Primary:
mysql> insert into test.test_replication_differ_type(id,replicate_col) values(1,2147483651);

On Replica:
mysql>select * from test.test_replication_differ_type;
+----+---------------+-----------+
| id | replicate_col | extra_col |
+----+---------------+-----------+
|  1 |    2147483651 |      NULL |
+----+---------------+-----------+

Suggested fix:
remove the incorrect statement
“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.”
https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

Hi Mr. Wang,

Thank you for your bug report.

However, you are not using our binary, nor have you built MySQL server without additional patches.

Let us know if you can repeat it with our binaries.

Unsupported.

Hello Team,
I did repeat the test on mysql 8.0.28 community version and got the same results as below:
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.

Hi,

Would you please describe all details regarding the replication.

What are the versions on server and slave and what are replication-related configurations on both master and slave ?????

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".