MySQL dump gives an error with illegal collation mix
Posted by: Nilesh Salunke
Date: February 15, 2021 10:11PM

Hello,

I have MySQL 8.0.21 installation on my server. When I try to take a dump of the database, I get an error "mysqldump: Error: 'Illegal mix of collations (utf8_general_ci, COERCIBLE) and (utf8_unicode_ci, COERCIBLE) for operation '='' when trying to dump tablespaces."

I have added custom settings in the mysqld section in /etc/my.cnf file as below.

lower_case_table_names=1
innodb_buffer_pool_size = 1536M
log_bin_trust_function_creators = 1
optimizer_search_depth=0
character-set-server=utf8
#Collation setting:
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
lower_case_table_names=1


Why would MySQL dump give an error with illegal collation mix? Does MySQL 8 change the collation of the table by itself, As the same thing works well with MySQL 5.7?

Thanks,
Nilesh.

Options: ReplyQuote


Subject
Written By
Posted
MySQL dump gives an error with illegal collation mix
February 15, 2021 10:11PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.