FIX for CentOS7 MySQL/MariaDB – ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist
While using an older VM with CentOS7 installed, I attempted to remove the default MySQL v5.5(ish) installation and replace it with a more recent MariaDB v10.5 for a Docker experiment. While trying to line up my DB version types due to a 16-character limit for the db usernames in older versions of MySQL, I ran into: “ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist”.
This was an odd one, as I even removed the original MySQL completely with:
(IMPORTANT: DON’T do this unless you have your existing data backed up with a mysqldump!! Read More here: https://mariadb.com/kb/en/making-backups-with-mysqldump/)
|
1 2 |
sudo service mysql stop; sudo yum remove mysql; |
Then we need to add a recent version of MariaDB that works with CentOS7:
|
1 2 3 4 5 6 7 |
sudo tee /etc/yum.repos.d/mariadb.repo<<EOF [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.5/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 EOF |
Next we update the cache after we added MariaDB to yum:
|
1 |
sudo yum makecache |
Now we install MariaDB from the repo we configured above:
|
1 |
sudo yum install MariaDB-server MariaDB-client |
Be sure to say “yes” to the GPG key, like so:
|
1 2 3 4 5 |
..... Importing GPG key 0x1BB943DB: ... Is this ok [y/N]: y |
Now we need to start the newly installed mariadb.service so we can test it out and secure the MySQL-compatible database:
|
1 |
sudo systemctl start mariadb |
If you want mariadb.service to be started at boot, do this:
|
1 |
sudo systemctl enable mariadb |
Now we need to make sure that we allow connection to port 3306, like so:
|
1 2 |
sudo firewall-cmd --add-service=mysql --permanent sudo firewall-cmd --reload |
Everything seemed to go fine, until the next step…
|
1 |
sudo mysql_secure_installation |
While trying to harden the MariaDB upgrade installation with, the above command, I got the following error(s):
|
1 2 |
Enable unix_socket authentication? [Y/n] Y ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist |
and/or
|
1 2 |
Change the root password? [Y/n] Y ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist |
How to fix “ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist”
I searched in the “usual places” for an obvious fix. This seemed like it might be some kind of local Linux CentOS7 user permissions issue, at first. I went of on a few side quests and peered down the rabbit hole of Linux User permissions for MySQL packages… but I stopped myself. Anytime you see an “error on line 1” it is likely due to some kind of configuration problem, otherwise a “permission denied” or “no such user” error would be more likely.
So after I got my head right, I then tried to completely uninstall all of MariaDB, just to start from scratch (IMPORTANT: DON’T do this unless you have used mysqldump for your existing data as a back up!!)
|
1 2 |
sudo systemctl stop mariadb sudo yum remove mariadb |
After removing MariaDB, completely we need to start over. Just to be sure, we can reboot with:
|
1 |
sudo reboot now |
After going through the entire installation process for a 2nd time, just as before (minus adding the MariaDB repo since that was done once already), I ran into the exact same issue again:
|
1 2 |
Change the root password? [Y/n] Y ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist |
FAIL.
After spending awhile longer trying to understand what went wrong, I attempted the following successful “fix” to the error:
|
1 |
sudo mysql_upgrade |
And I noticed this about 30% through the mysql_upgrade process:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
........... mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Upgrading from a version before MariaDB-10.1 Phase 2/7: Installing used storage engines Checking for tables with unknown storage engine Phase 3/7: Fixing views Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases information_schema ........... |
…. and after that: Ka-ching!
|
1 2 3 4 5 6 |
sudo mysql_secure_installation .......... Enable unix_socket authentication? [Y/n] Y Enabled successfully! Reloading privilege tables.. … Success! |
My MySQL client tools and CLI access to the newly updated and clean installed MariaDB worked flawlessly.
I haven’t had to run the mysql_update command to solve a problem with setting MySQL/MariaDB to start & run in many years, but the error message and missing table issue was pretty obscure. Bonus tip: if you get “error access denied” during
|
1 2 3 4 5 |
</p> <!-- /wp:paragraph --> <!-- wp:urvanov-syntax-highlighter/code-block --> <div class="wp-block-urvanov-syntax-highlighter-code-block"><pre class="lang:default decode:true ">sudo mysql_update |
… then try this instead:
|
1 |
mysql_upgrade --port=3306 --user=root -p |
Change your port number if you need to & enter your root password, accordingly. Also have a look at this post to try and solve your issues with root@localhost -VS- [email protected] having different password requirements.
I hope this helps someone else out! Going through this fix worked well enough for me to do my Docker tests and was still faster than spinning up a shiny new VM or embarking on an entirely new Docker Stack from scratch.
Thank you! mysql_upgrade indeed did the trick.