Bugzilla – Bug 1227109
mysqldump --system=users always fails, reporting a 'Illegal mix of collations'
Last modified: 2024-06-28 15:17:32 UTC
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/115.0 Build Identifier: There seems to be an internal error within mysqldump version 10.11.8 causing it always to fail, reporting not being able to execute a SELECT command due to a 'Illegal mix of collations' Reproducible: Always Steps to Reproduce: 1. zypper update -y; zypper install -y mariadb; systemctl start mariadb 2. /usr/bin/mysql_secure_installation (choose defaults and recommended settings) 3. mysqldump --system=users Actual Results: kostunrix:~ # mysqldump --system=users /*!999999\- enable the sandbox mode */ -- MariaDB dump 10.19 Distrib 10.11.8-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.11.8-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; mysqldump: Couldn't execute 'SELECT CONCAT(QUOTE(u.user), '@', QUOTE(u.Host)) AS u FROM mysql.user u /*!80001 LEFT JOIN mysql.role_edges e ON u.user=e.from_user AND u.host=e.from_host WHERE e.from_user IS NULL */ /*M!100005 WHERE is_role='N' */': Illegal mix of collations (utf8mb4_unicode_520_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '=' (1267) kostunrix:~ # echo $? 2 Expected Results: (taken from openSuse Leap 15.6) /*!999999\- enable the sandbox mode */ -- MariaDB dump 10.19 Distrib 10.6.18-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 10.6.18-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE USER `mariadb.sys`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE; CREATE USER `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket; CREATE USER `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket; CREATE USER `webmaster`@`localhost` IDENTIFIED BY PASSWORD '*8EC378A37494C46344EF5EBF1AD7236E408326BF'; SELECT COALESCE(CURRENT_ROLE(),'NONE') into @current_role; CREATE ROLE IF NOT EXISTS mariadb_dump_import_role; GRANT mariadb_dump_import_role TO CURRENT_USER(); SET ROLE mariadb_dump_import_role; GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT SELECT, DELETE ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; /*M!100005 SET DEFAULT ROLE NONE FOR 'mariadb.sys'@'localhost' */; /*!80001 ALTER USER 'mariadb.sys'@'localhost' DEFAULT ROLE NONE */; GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION; GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; /*M!100005 SET DEFAULT ROLE NONE FOR 'root'@'localhost' */; /*!80001 ALTER USER 'root'@'localhost' DEFAULT ROLE NONE */; GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION; GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION; /*M!100005 SET DEFAULT ROLE NONE FOR 'mysql'@'localhost' */; /*!80001 ALTER USER 'mysql'@'localhost' DEFAULT ROLE NONE */; GRANT ALL PRIVILEGES ON *.* TO `webmaster`@`localhost` IDENTIFIED BY PASSWORD '*8EC378A37494C46344EF5EBF1AD7236E408326BF'; /*M!100005 SET DEFAULT ROLE NONE FOR 'webmaster'@'localhost' */; /*!80001 ALTER USER 'webmaster'@'localhost' DEFAULT ROLE NONE */; SET ROLE NONE; DROP ROLE mariadb_dump_import_role; /*M!100203 EXECUTE IMMEDIATE CONCAT('SET ROLE ', @current_role) */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-06-26 22:34:57 The problem has been reproduced here on 4 different computers, all running a fresh installed openSuse Leap 15.6 (hence, no upgrades) The above entered actual results have been produced on the following system: From KDE InfoCenter ------------------- Operating System: openSUSE Leap 15.6 KDE Plasma Version: 5.27.11 KDE Frameworks Version: 5.115.0 Qt Version: 5.15.12 Kernel Version: 6.4.0-150600.23.7-default (64-bit) Graphics Platform: X11 Processors: 8 × AMD Ryzen 5 3400G with Radeon Vega Graphics Memory: 13.5 GiB of RAM Graphics Processor: AMD Radeon Vega 11 Graphics Manufacturer: ASUS