0

As title says I upgraded my mariadb, all fine and no issues, ran mariadb-upgrade and all fine but now every time i start mariadb I see this errors

Mar 27 03:38:42 db3 debian-start[2408864]: SELECT count(*) FROM mysql.user WHERE user='root' and password='' and password_expired='N' and plugin in ('', 'mysql_native_password', 'mysql_old_password')
Mar 27 03:38:42 db3 debian-start[2408864]: --------------
Mar 27 03:38:42 db3 debian-start[2408864]: ERROR 1267 (HY000) at line 1: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation '='

I have tried changing the mysql.global_priv already and even tried to run this

ALTER TABLE mysql.user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

But it gives an error /* SQL Error (1347): 'mysql.user' is not of type 'BASE TABLE' */

This works fine but doesn't fix the issue

ALTER TABLE mysql.global_priv CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Confirmation of the defaults being loaded at start

mysqld would have been started with the following arguments:
--socket=/run/mysqld/mysqld.sock --pid-file=/run/mysqld/mysqld.pid --basedir=/usr --skip-name-resolve --bind-address=0.0.0.0 --port=REDACTED --collation-server=utf8mb4_unicode_520_ci --init-connect=init-connect=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci --character-set-server=utf8mb4 --character-set-client-handshake=FALSE --skip-character-set-client-handshake=1 --key_buffer_size=2G --myisam_sort_buffer_size=16M --max_allowed_packet=256M --myisam-recover-options=BACKUP --max_length_for_sort_data=8192 --expire_logs_days=7 --max_binlog_size=100M --max_connections=39000 --max_user_connections=38990 --wait_timeout=10 --back_log=4096 --max_connect_errors=4096 --table_open_cache=40000 --table_definition_cache=40000 --tmp_table_size=1G --max_heap_table_size=1G --innodb_buffer_pool_size=32G --innodb_log_file_size=8G --innodb_read_io_threads=64 --innodb_write_io_threads=64 --innodb_thread_concurrency=0 --innodb_flush_log_at_trx_commit=0 --innodb_flush_method=O_DIRECT --performance_schema=OFF --innodb-file-per-table=1 --innodb_io_capacity=2000 --innodb_table_locks=0 --innodb_lock_wait_timeout=50 --innodb_deadlock_detect=ON --query_cache_limit=0 --query_cache_size=0 --query_cache_type=0 --sql-mode=NO_ENGINE_SUBSTITUTION --log_error=/var/lib/mysql/mysql_error.log --log_queries_not_using_indexes=0 --long_query_time=5 --slow_query_log=1 --slow_query_log_file=/var/lib/mysql/mysql_slow.log --event_scheduler=ON --performance_schema_max_file_handles=999999

My show create table

CREATE TABLE `global_priv` (
    `Host` CHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `User` CHAR(128) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_520_ci',
    `Priv` LONGTEXT NOT NULL DEFAULT '{}' COLLATE 'utf8mb4_unicode_520_ci',
    PRIMARY KEY (`Host`, `User`) USING BTREE
)
COMMENT='Users and global privileges'
COLLATE='utf8mb4_unicode_520_ci'
ENGINE=Aria
;

I don't know what else to do. This doesn't seem to cause issues when connecting but it's annoying and also seems related to this issue here that had no solution

Mariadb can't enforce charset and collation to clients

Anyone knows how to fix this?

Otto
  • 444
  • 10
Freedo
  • 206
  • 2
  • 10

3 Answers3

1

DISCLAIMER: I have not tested this, so do this at your own risk.

I could be wrong but it sounds as if you somehow have different collations on the table mysql.global_priv and the view mysql.user. To verify:

SHOW CREATE VIEW mysql.user\G

If so, you will need to modify the collation and maybe character set on the mysql.user view. The only way I know how to do that is:

SET collation_connection = 'utf8mb4_unicode_520_ci';
SET character_set_client = 'utf8mb4';
ALTER .....;

The ALTER statement shouldn't actually change the view itself, just make it use the collation_connection and character_set_client you have set for your session in the previous lines.

So, simply replace the dots above with the output shown in the "Create View" line from SHOW CREATE VIEW mysql.user\G, except remove the initial CREATE keyword (so that the statement starts with ALTER).

dbdemon
  • 6,964
  • 4
  • 21
  • 40
1

The mysql.* tables are all system tables, managed by the MariaDB itself. You should probably not be messing with them manually.

This situation sounds like a bug in mariadb-upgrade or some other part that fails to update/convert the character set (or collation) on upgrade. There is a bug in Debian that is clearly related: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1104533. I also found https://jira.mariadb.org/browse/MDEV-36815 at upstream.

Please in the spirit of open source help upstream fix this so that all upgrades going forward handle this automatically.

Otto
  • 444
  • 10
0

As OP tried, I first altered collation on mysql.global_priv. The value that made all the fields in mysql.user have the same collation was

ALTER TABLE mysql.global_priv CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

but I was still getting the error message (with a different pair of illegal collations). To fully get rid of the error message I had to change a line I had in /etc/mysql/mariadb.conf.d/55-custom.cnf from

[mysqld]
collation-server = utf8mb4_unicode_ci

to

[mysqld]
collation-server = utf8mb4_general_ci
timcu
  • 1