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

sudo service mysql stop;
sudo yum remove mysql;

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:

sudo yum makecache

Now we install MariaDB from the repo we configured above:

sudo yum install MariaDB-server MariaDB-client

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:

sudo systemctl start mariadb

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

sudo systemctl enable mariadb

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

sudo firewall-cmd --add-service=mysql --permanent
sudo firewall-cmd --reload

Everything seemed to go fine, until the next step…

sudo mysql_secure_installation

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

Enable unix_socket authentication? [Y/n] Y
ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist

and/or

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

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:

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:

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:

sudo mysql_upgrade

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

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

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

		

		
			

			

			

			
sudo mysql_update
			

				

					

				

					
1
				
						
sudo mysql_update
					
				
			
		





... then try this instead:





		

		
			

			

			

			
mysql_upgrade  --port=3306 --user=root -p
			

				

					

				

					
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.