MariaDB switch to socket authentication fails with error 1146 (42S02): Table mysql.global_priv doesnt exist

Written by Claudio Kuenzler - 0 comments

Published on November 15th 2025 - Listed in MariaDB MySQL Databases Linux

---

My older MariaDB setups were created with the classic 'root'@'localhost' user. But in the last couple of years the default behaviour has switched to Socket Authentication. If you're logged in as root on the MariaDB machine, the mysql command uses the MariaDB socket to log in.

ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist

In a past article I wrote how to reset MariaDB to use Socket Authentication. The easiest way to do this (on MariaDB 10.5) is using the mysql_secure_installation command.

However on this particular server I ran into an error:

root@bullseye:~# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): ***************
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] Y
--------------
UPDATE mysql.global_priv SET priv=json_set(priv, '$.password_last_changed', UNIX_TIMESTAMP(), '$.plugin', 'mysql_native_password', '$.authentication_string', 'invalid', '$.auth_or', json_array(json_object(), json_object('plugin', 'unix_socket'))) WHERE User='root'
--------------

ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist
Failed!
Cleaning up...

Huh?! That's a first to me...

MariaDB upgraded (due to distribution release upgrade)

When a (official) MariaDB or MySQL command doesn't find a table, that basically means one thing: There's a discrepancy between the MariaDB client (command) and server (database/table structure).

A verification in the APT logs (/var/log/apt/history.*) checked out; there was indeed a major version upgrade of MariaDB a few weeks ago:

root@bullseye:~# zcat /var/log/apt/history.log.1.gz
[...]
Start-Date: 2025-09-23  19:44:02
Commandline: apt-get dist-upgrade
[...]
Upgrade: libpam0g:amd64 (1.3.1-5, 1.4.0-9+deb11u2), debconf:amd64 (1.5.71+deb10u1, 1.5.77) [...] mariadb-server:amd64 (1:10.3.39-0+deb10u2, 1:10.5.29-0+deb11u1) [...]
[...]

Another look into the audit logs showed that this server was upgraded from Debian 10 to 11. The apt-get dist-upgrade command with the modified APT sources was executed just before, on September 23rd 19:43:12.

This explains the change in the mysql system database. Usually a mysql_upgrade should automatically be triggered after such a major upgrade of MySQL or MariaDB packages. For some reason this didn't happen and needs to be executed manually.

Manual MySQL Upgrade

However executing the mysql_upgrade command, as root user, failed, too: 

root@bullseye:~# mysql_upgrade
Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed

Here we run again into the initial problem: The client (mysql_upgrade command) wants to use the socket authentication - however the mysql user table isn't prepared for this yet (that was the reason to launch the mysql_secure_installation command at the beginning!).

As a temporary workaround we must submit login credentials to the mysql_upgrade command.

root@bullseye:~# mysql_upgrade -u root -h localhost -p
Enter password: ***************
Major version upgrade detected from 10.3.39-MariaDB to 10.5.29-MariaDB. Check required!
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
[...]
information_schema
performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

Looks like the MySQL database was - finally - upgraded to to 10.5 now. 

mysql_secure_installation now working

Now with the version upgrade completed, the previously missing table should have been created and mysql_secure_installation should now work:

root@bullseye:~# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): ***************
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] Y
Enabled successfully!
Reloading privilege tables..
 ... Success!
[...]

Success!

TL;DR

If you run a distribution upgrade on Debian (or on another Linux distribution) and this involves a major version upgrade of MySQL or MariaDB, make sure to execute mysql_upgrade afterwards. The MariaDB package should actually do this automatically (in post installation or post upgrade scripts), but I've seen a few servers where this never happened.

Executing mysql_upgrade after the upgrade makes sure your system tables are "migrated" to the new major version. Afterwards you should switch to socket authentication.

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.