Justin Merrill, MBA, BS .:|:. Platform Engineer

Platform | DevOps | SRE | K8s | GitOps | IaC | CICD | Security | IAM | Cloud | Software | Data | APIs

Linux

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/)


Then we need to add a recent version of MariaDB that works with CentOS7:

Next we update the cache after we added MariaDB to yum:

Now we install MariaDB from the repo we configured above:

Be sure to say “yes” to the GPG key, like so:

Now we need to start the newly installed mariadb.service so we can test it out and secure the MySQL-compatible database:

If you want mariadb.service to be started at boot, do this:

Now we need to make sure that we allow connection to port 3306, like so:

Everything seemed to go fine, until the next step…

While trying to harden the MariaDB upgrade installation with, the above command, I got the following error(s):

and/or

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!!)


After removing MariaDB, completely we need to start over. Just to be sure, we can reboot with:

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:

FAIL.

After spending awhile longer trying to understand what went wrong, I attempted the following successful “fix” to the error:

And I noticed this about 30% through the mysql_upgrade process:

…. and after that: Ka-ching!

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

… then try this instead:

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.

j-merrill

Justin Merrill, MBA, BS .:|:. Platform Engineer Engineer | Software | DevOps | CICD | Cloud | Security | IAM | SRE | K8s | GitOps | IaC | Data | APIs | Platform

One thought on “FIX for CentOS7 MySQL/MariaDB – ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist

  • Anonymous

    Thank you! mysql_upgrade indeed did the trick.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.