| Bug #83461 | replication breakage noted from 5.7.10 to 8.0.0 | ||
|---|---|---|---|
| Submitted: | 20 Oct 2016 12:59 | Modified: | 11 Jan 2023 8:15 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 8.0.0, 8.0.18, 5.7.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | conversion, replication, utf8, utf8mb4 | ||
[20 Oct 2016 13:01]
Simon Mudd
master config
Attachment: server5710_etc_my.cnf (application/octet-stream, text), 3.47 KiB.
[20 Oct 2016 13:01]
Simon Mudd
8.0.0 slave config
Attachment: server800_etc_my.cnf (application/octet-stream, text), 3.15 KiB.
[3 Nov 2016 14:00]
Manyi Lu
MySQL does not auto-upgrade any tables to use a new charset. In this case, I believe you have set the default charset on the slave running 8.0.0 to be utf8mb4, and then mysql.db_slaves was created without any charset being specified, so this new table got created with the default charset. The error message looks indeed wrong.
[3 Nov 2016 14:41]
Luis Soares
Hi Simon,
Thanks for taking time to report and provide feedback on this.
Can you answer the following questions that may help validating Manyi's
observation above?
Q1. Was the table mysql.db_slaves created on the slave using regular
replication (issued CREATE TABLE on master and let it propagate)
or was it created directly on master and slave separately (i.e.,
a CREATE TABLE was issued on each server - no replication
involved)?
Q2. Was the table created (through replication or directly) on the
slave after the slave upgrade to 8.0.0 was performed or before
that?
Q3. Was the character set explicitly specified on the create table
statement(s)?
Thanks,
Luís
[4 Nov 2016 7:20]
MySQL Verification Team
This issue can be observed even with 5.7.10(master)->5.7.10(slave) if the table in question has diff charset on master and slave.
- Setup Replication
- On master
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `db_slaves` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `parent_id` int(11) DEFAULT NULL,
-> `dsn` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table db_slaves\G
*************************** 1. row ***************************
Table: db_slaves
Create Table: CREATE TABLE `db_slaves` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
- On slave
mysql> use test
Database changed
mysql> show create table db_slaves\G
*************************** 1. row ***************************
Table: db_slaves
Create Table: CREATE TABLE `db_slaves` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> drop table db_slaves;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `db_slaves` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `parent_id` int(11) DEFAULT NULL,
-> `dsn` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table db_slaves\G
*************************** 1. row ***************************
Table: db_slaves
Create Table: CREATE TABLE `db_slaves` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
-- slave status should be fine
-- Now, provision some data on master
-- On master
mysql> insert into db_slaves(parent_id,dsn) VALUES(1,'mysql.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from db_slaves;
+----+-----------+-----------+
| id | parent_id | dsn |
+----+-----------+-----------+
| 1 | 1 | mysql.com |
+----+-----------+-----------+
1 row in set (0.00 sec)
- Confirm that slave is broken
mysql> 2016-11-04T07:02:08.160122Z 4 [ERROR] Slave SQL for channel '': Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)', Error_code: 1677
2016-11-04T07:02:08.160143Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000002' position 1709
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1988
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 810
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)'
Skip_Counter: 0
Exec_Master_Log_Pos: 1709
Relay_Log_Space: 1289
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 89a28c77-a25a-11e6-8c9d-0010e05f3e06
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 161104 08:02:08
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
[4 Nov 2016 8:28]
MySQL Verification Team
Also, 5.7->5.7(later upgraded slave to 8.0 which has default charset utf8mb4)
On 5.7 Master
=========
CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
After it is replicated, Master/Slave has:
mysql> CREATE TABLE `db_slaves` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `parent_id` int(11) DEFAULT NULL,
-> `dsn` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now, upgrade 5.7 slave to 8.0 which has default charset and table is recreated on slave
CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
Now, Slave has
now, slave has this
mysql> show create table db_slaves\G
*************************** 1. row ***************************
Table: db_slaves
Create Table: CREATE TABLE `db_slaves` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
Any attempt to insert data would trigger the issue:
Master:
mysql> insert into db_slaves(parent_id,dsn) VALUES(3,'mysql2.com');
Query OK, 1 row affected (0.00 sec)
Slave:
mysql> 2016-11-04T08:14:55.189532Z 4 [ERROR] Slave SQL for channel '': Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)', Error_code: 1677
2016-11-04T08:14:55.189572Z 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'master-bin.000002' position 2914
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 3194
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 751
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1677
Last_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)'
Skip_Counter: 0
Exec_Master_Log_Pos: 2914
Relay_Log_Space: 1231
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1677
Last_SQL_Error: Column 2 of table 'test.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 729e083d-a265-11e6-a885-0010e05f3e06
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 161104 09:14:55
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Shane pointed https://dev.mysql.com/doc/refman/8.0/en/replication-features-charset.html so, charset should be same but still error message is confusing.
[4 Nov 2016 8:33]
MySQL Verification Team
Thank you for the report. Observed this with 5.7->5.7, and even with 5.7->5.7(upgraded to 8.). Thanks, Umesh
[4 Nov 2016 9:40]
Simon Mudd
Hi Luis, To respond to your questions. Q1. Yes, the table was created on the slaves using regular replication. Q2. I am just not 100% sure when this happened, but think this happened after the slave was upgraded to 8.0. I tracked down where this came from and it is related to pt-online-schema-change usage to track which slaves need to be monitored for replication delay. Q3. I noticed that the table creation statement did not make an explicit reference to the table or column's character set and this indeed looks like it triggered the table to be created differently on master and slave due to different defaults on both servers. This was missing and has now been corrected in this specific case. To be honest I try to configure /etc/my.cnf to be as minimal as possible as otherwise it can be hard to see what's configured for a specific reason and what is there "just because it is". - This helps to use newer, more appropriate settings as servers get upgraded - However, for cases where the default settings change from one version to another this can lead to issues configuration differences which are not as visible. I believe that this is what has happened here, at least related to the final table definitions on both master and slave being different. The solution to this specific problem is obviously to ensure that settings such as this are configured explicitly on both major versions to be consistent. Documentation should highlight this clearly to ensure that people upgrading are aware of this and adapt their systems if needed.
[4 Nov 2016 9:51]
Simon Mudd
I'll be explicit about 2 Feature requests that come from this behaviour: (1) Fix the error message and make it be explicit about the character sets used. So something like: Column 2 of table 'mysql.db_slaves' cannot be converted from type 'varchar(765)' (character set utf8) to type 'varchar(255)' (character set utf8mb4) (2) Ensure that the conversion can happen in most cases where this makes sense * In some cases such as this one "explicit conversion" should not be necessary: utf8 is a subset of utf8mb4 so I would expect MySQL to be able to pass the "byte stream" straight through. Other similar conversion are also possible. * In other cases the conversion may be possible but will require the server to perform this conversion. If possible allow this to happen for all cases where the server knows how to do this. * Finally there will be cases where the conversion is not be possible: utf8mb4 4-byte characters can not be converted to utf8 3-byte equivalents. In this case the error message indicated would make sense and replication should stop.
[27 Nov 2016 9:21]
Simon Mudd
Related to: bug#83925
[13 Dec 2019 13:40]
Christian Roser
This bug can still be triggered on MySQL 5.7.28, MySQL 5.5.62.
[14 Dec 2019 7:51]
MySQL Verification Team
- Observed that issue is still reproducible on latest builds(used provided schema, conf file) 5.7.28 Master -> 5.7.28 Slave 5.7.28 Master -> 8.0.18 Slave Joining the test details shortly. regards, Umesh
[14 Dec 2019 7:52]
MySQL Verification Team
5.7.28M->5.7.28S test details
Attachment: 83461_5.7.28M_5.7.28S.results (application/octet-stream, text), 22.76 KiB.
[14 Dec 2019 7:52]
MySQL Verification Team
5.7.28M->8.0.18S test details
Attachment: 83461_5.7.28M_8.0.18S.results (application/octet-stream, text), 22.28 KiB.
[30 Dec 2019 5:13]
MySQL Verification Team
Bug #97985 marked as duplicate of this one
[14 Feb 2020 17:11]
Wes Deviers
I'm pretty sure I've tickled this same bug, except it appears I don't have any charset mismatch. I'm replicating from a 5.6 to a 5.7 box. Using Chef, I'm deleting a user across the entire ecosystem that we used to monitor the boxes. EG:
GRANT PROCESS, SUPER ON *.* TO 'cacti'@'10.132.96.61' IDENTIFIED BY PASSWORD '*xxxxx';
I am then removing that user via:
DELETE FROM mysql.user where user = 'cacti';
(we have everything from 5.1 to 8.0, so I can't do anything more fancy than that)
On the slave that generates:
Last_Error: Column 1 of table 'mysql.user' cannot be converted from type 'char(48(bytes))' to type 'char(96(bytes) utf8)'
Except...it's all UTF8.
Master (5.6)
mysql> show create table mysql.user;
| user | CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
<SNIP>
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' |
mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
On the slave:
mysql> show global variables like '%char%';
+---------------------------+----------------------------+
| Variable_name | Value |
+---------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| ft_query_extra_word_chars | OFF |
+---------------------------+----------------------------+
| user | CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
<SNIP>
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
Once again, on the slave:
mysql> select table_name,column_name,character_set_name from columns where table_name='user' and character_set_name IS NOT NULL;
+------------+------------------------+--------------------+
| table_name | column_name | character_set_name |
+------------+------------------------+--------------------+
| user | Host | utf8 |
| user | User | utf8 |
| user | Select_priv | utf8 |
| user | Insert_priv | utf8 |
| user | Update_priv | utf8 |
Which matches with the master. I'm using 5.6 in binlog_format = ROW. If I switch it to MIXED, it works. So perhaps the answer here is that sometimes replication is autodetecting the wrong kind of binlog entry to make?
[14 Feb 2020 17:29]
MySQL Verification Team
Wes, does this help your case: https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_type_c...
[19 Feb 2020 20:02]
Wes Deviers
Nice find. It might, I'll try to experiment with it in the next few weeks. I still wouldn't consider a "conversion" but I suppose that's a difference in language.
[15 Apr 2020 9:19]
Karthik Kamath Koteshwar
Posted by developer: Hi, 1. If a table with character set x was created on Master and the table was explicitly re-created by the user on Slave with character set y, then please be noted that this is a case of Replication between columns using different character sets, which is not supported. https://dev.mysql.com/doc/refman/8.0/en/replication-features-differing-tables.html#replica... 2. If the issue happened because of Upgrade from 5.7 to 8.0, I request to verify if you are still seeing the issue on the latest server versions. On a side note, please consider using the Replication slave option 'slave_type_conversions'. https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_type_c...
[16 May 2020 1:00]
Bugs System
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".
[28 May 2020 6:50]
Karthik Kamath Koteshwar
Posted by developer: Closing the bug. Please re-open in case you are seeing the issue.
[5 Jul 2020 8:12]
Warwick Chapman
I am seeing this on a mysqld 8.0.20-0ubuntu0.20.04.1 slave replicating from a mysqld 5.7.29-0ubuntu0.18.04.1-log master. I saw it on mysql.user first, and tried to fix with: alter table mysql.user charset = utf8; But, after restarting, the replication worker returns the same error: [ERROR] [MY-013146] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.001095, end_log_pos 49318125; Column 0 of table 'mysql.user' cannot be converted from type 'char(180(bytes))' to type 'char(255(bytes) ascii)', Error_code: MY-013146 The slave was running charset = UTF8MB4 as part of us testing for the future in response to this message: [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. I have reverted to: charset = utf8 and restarted, with no luck.
[5 Jul 2020 11:21]
Warwick Chapman
I also wish to add that replication was working until a drive failure and a reboot.
[6 Jan 2023 18:34]
Peter Duffy
I'm seeing almost exactly the same version of this problem as reported by Wes Deviers on 14/02/20. I'm replicating from 5.6 to 5.7. On the 5.6 server, if I change a value for definer in a row in mysql.proc, the replica stops with:
"Column 11 of table 'mysql.proc' cannot be converted from type 'char(231(bytes))' to type 'char(279(bytes) utf8)'"
Both servers are using utf8.
mysql.proc.definer:
on 5.6 master:
COLUMN_NAME: definer
ORDINAL_POSITION: 12
COLUMN_DEFAULT:
IS_NULLABLE: NO
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 77
CHARACTER_OCTET_LENGTH: 231
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_bin
COLUMN_TYPE: char(77)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
on 5.7 slave:
COLUMN_NAME: definer
ORDINAL_POSITION: 12
COLUMN_DEFAULT:
IS_NULLABLE: NO
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 93
CHARACTER_OCTET_LENGTH: 279
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_bin
COLUMN_TYPE: char(93)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
NB - if I replicate the case in Wes Deviers' report (creating a user and then deleting it) - I get the same error that Wes reported.
[6 Jan 2023 19:28]
Peter Duffy
Further to my report above about a similar problem with changing mysql.proc.definer in a setup with 5.6 master and 5.7 replica. Maybe in some cases, the 5.7 replica is not understanding that the 5.6 master is using utf8 - so if it sees that the master column is 231 bytes, it thinks that it needs to multiply this by 3 - and obviously that's incompatible with the replica size of 279 bytes. Maybe something to do with the fact that mysql.proc is a myisam table? Whether or not the above is the case, a possible workaround appeared to be to change the size of the mysql.proc.definer column on the master to match that on the replica (char(93)) - hopefully then, the replica wouldn't see a size mismatch. I've tried this - and it seems to work: once the size of mysql.proc.definer on the 5.6 master is set to char(93), changes to mysql.proc.definer values get accepted on the replica without errors.
[9 Jan 2023 7:24]
Simon Mudd
Peter, as the original bug reporter here I am not sure that Oracle should concern themselves with issues replicating 5.6 to 5.7. MySQL 8.0 is the current/latest major release and has been for 4+ years. 5.7 is already an old version and 5.6 well something that really I think should not be used. If you check the 5.6 release notes: Changes in MySQL 5.6.51 (2021-01-20, General Availability) was the final release of a product first made generally available in 2013 and has the comment: "Note. MySQL 5.6.51 is the last release of the MySQL 5.6 series." MySQL has changed a huge amount since that time (which I remember well) and expecting bug fixes etc to 5.7 to handle 5.6 to 5.7 replication issues is not I think worthwhile. It may be useful to "report the bug" as you've done but expecting any work to resolve that is probably not realistic.
[10 Jan 2023 19:11]
Peter Duffy
Simon, thanks for following up on my comments. I think I've got to the bottom of my own problem (mysql.proc.definer being defined as char(77) in 5.6 and char(93) in 5.7). It seems that the variable 'slave_type_conversions' has to include ALL_NON_LOSSY (== allow non-lossy conversions) before a differently-sized column will be allowed to replicate - even if it's of the same type on both master and replica, and the replica size is bigger than the master size. The manual doesn't make this clear. So - it appears that my problem wasn't a bug after all. But I do think that the coverage of this in the manual could be improved.
[11 Jan 2023 8:15]
Simon Mudd
Documentation can always be improved. There's no doubt about that. I think you can file a bug report with details of a topic that could be improved and changes you think might be appropriate. I've done that on a number of occasions. If the details are clear and make sense I'm sure Oracle would update the appropriate sections if that makes sense.
Description: I have a master - slave setup like this: master: server5710 running mysql-community-server-5.7.10-1.el6.x86_64 slave: server800 running 8.0.0 The 8.0.0 server had been built by stopping the server which had been running 5.7 before, changing the binaries for the 8.0.0 binaries and running mysql_upgrade, followed by a final restart running 8.0.0 with replication running. No other changes were made. configs for 5.7.10 master and 8.0.0 slaves to be included. Replication had been running for some time then it broke. Show slave status shows: *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server5710 Master_User: some_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.006014 Read_Master_Log_Pos: 45984829 Relay_Log_File: relaylog.016933 Relay_Log_Pos: 57627576 Relay_Master_Log_File: binlog.006006 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1677 Last_Error: Column 2 of table 'mysql.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)' Skip_Counter: 0 Exec_Master_Log_Pos: 57627369 Relay_Log_Space: 886062936 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1677 Last_SQL_Error: Column 2 of table 'mysql.db_slaves' cannot be converted from type 'varchar(765)' to type 'varchar(255)' Replicate_Ignore_Server_Ids: Master_Server_Id: 190166014 Master_UUID: 640ab77e-ce64-11e5-ab7d-5cb901da9fb0 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 ... So complaining about a table mysql.db_slaves which is on both master and slave. Table definitions I have for both boxes show: root@server5710 [mysql]> show create table mysql.db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Table definition on the slave: =============================== root@server800 [(none)]> show create table mysql.db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) How to repeat: . Suggested fix: I applied this workaround: root@server800 [mysql]> alter table db_slaves charset = utf8; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 root@server800 [mysql]> alter table db_slave modify `dsn` varchar(255) NOT NULL; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 root@server800 [mysql]> show create table mysql.db_slaves\G *************************** 1. row *************************** Table: db_slaves Create Table: CREATE TABLE `db_slaves` ( `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@server800 [mysql]> start slave; Query OK, 0 rows affected (0.00 sec) and things worked as expected. Thoughts: The upstream master had this column configured as a varchar(255) utf8 (so maximum 3 x 255 bytes) The downstream 8.0.0 slave had this column configured as a varchar(255) utf8mb4 (so maximum 4 x 255 bytes) as a consequence of the mysql_upgrade process moving to 8.0.0. The db_slaves table is not part of MySQL. It came from tooling we were using. While this table is tiny (only a few rows) it may not be good to auto-upgrade non-system tables from utf8 to utf8mb4 as this may trigger table rebuilds which for large tables would be very expensive. For this specific table it did not matter, but the general case of a multi-GB table getting rebuilt as the storage lengths of the column change might be a big concern. Indexing would be affected too. Moving to utf8mb4 as the default setup is good but we need more control here and maybe some setting like avoid_temporal_ugprades to prevent this happening if we don't want it. The error looks odd to me. The type conversion seems to be wrong here. While I've not seen other issues this looks odd and wonder what is going on. I don't have the binlog event that triggered the breakage. I realise the report is not very detailed. Hopefully it provides enough information to recognise something that perhaps is not quite right either in replication, or in the 8.0.0 upgrade procedure which could cause this.