Comments on: Replicating from MySQL 8.0 to MySQL 5.7 https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/ Fri, 29 Dec 2023 00:58:13 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Steve https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10970982 Sat, 13 Jul 2019 18:08:22 +0000 https://www.percona.com/blog/?p=51580#comment-10970982 The init-connect option was plausible; but it does not work for users with the super privilege,

]]> By: Vinicius Grippa https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10970981 Sat, 13 Jul 2019 17:48:58 +0000 https://www.percona.com/blog/?p=51580#comment-10970981 Glad that you could figure out a workaround. Another option is setting the utf8 on the init-connect parameter.

]]> By: Steve https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10970976 Fri, 12 Jul 2019 18:29:26 +0000 https://www.percona.com/blog/?p=51580#comment-10970976 The default-character-set=utf8 in [client] works but you have to make sure it is in all instances of my.cnf that client might look at. Adding it to local my.cnf file fixed the issue.

]]> By: Steve https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10970975 Fri, 12 Jul 2019 18:14:29 +0000 https://www.percona.com/blog/?p=51580#comment-10970975 Update:
Using SET NAMES utf8;
Before running statements successfully replicates to 5,7 from 8.0.

]]> By: Steve https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10970974 Fri, 12 Jul 2019 16:43:02 +0000 https://www.percona.com/blog/?p=51580#comment-10970974 I realize it has been awhile; but I am getting the same #255 error with replication from 8.0.13-4 to 5.7.

[ERROR] Slave SQL for channel ”: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/percona-server/charsets/Index.xml’ file’ on query.

The character sets and collations are the same and using UTF8.

master 8.0> show global variables like ‘%character%’;
+————————–+————————————-+
| 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/percona-server/charsets/ |

master 8.0> show global variables like ‘%collation%’;
+——————————-+——————–+
| Variable_name | Value |
+——————————-+——————–+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+——————————-+——————–+

slave 5.7> show global variables like ‘%character%’;
+————————–+————————————-+
| 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/percona-server/charsets/ |
+————————–+————————————-+

slave 5.7> show global variables like ‘%collation%’;
+———————-+—————–+
| Variable_name | Value |
+———————-+—————–+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+———————-+—————–+

New database created on master 8.0:

master 8.0> select * from information_schema.schemata where schema_name = ‘steve_test’;
+————–+————-+—————————-+————————+———-+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+————–+————-+—————————-+————————+———-+
| def | steve_test | utf8 | utf8_unicode_ci | NULL |
+————–+————-+—————————-+————————+———-+

This is an upgrade on a replicated server from 5.7.25-28 to 8.0.13-4 so all users were in place and using the correct plugin.

Any ideas why the replicated statement from 8.0 generates an error in replication to 5.7?

]]> By: Vinicius Grippa https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10969723 Tue, 25 Sep 2018 00:43:12 +0000 https://www.percona.com/blog/?p=51580#comment-10969723 Hi Gauravkumar,

Thanks for reaching! I hope this post help you solve your issue. Analyzing the error that you sent this seems to be a problem of the collation of the MySQL 5.7 that is trying to use one that does not exist. You need to compare both servers and use one collation that exist on MySQL 5.7. To check you can use:

mysql > show global variables like ‘%collation%’;

Compare the master and the slave and check if they are using the same.

]]> By: Gauravkumar Mishra https://www.percona.com/blog/replicating-mysql-8-0-mysql-5-7/#comment-10969720 Mon, 24 Sep 2018 13:17:58 +0000 https://www.percona.com/blog/?p=51580#comment-10969720 Hi Vinicius,
I have been trying to do this for past few days. I then bumped into your post , but still seems to be an issue for me.
I am trying replication only for one particular database.
On slave which is 5.7 has replicate-do-db=”databasename” set in my.cnf
The replication works for almost a day and then we get this error as below:
Last_SQL_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml’ file’ on query. Default database: ‘mysql’. Query: ‘BEGIN’

even though I am using the parameter replicate-do-db it is still throwing an error for query on database ‘mysql’.
I am wondering why? Let me know what do you think of this error.

I will try your solution and will update here.
Wish me good luck!!

]]>