Browse: Home / 2019 / August / 11 / FIX for CentOS7 MySQL/MariaDB – ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist

Menu

Skip to content
Header image

Justin MerrillLogo

Software Platform Architect | Cloud | DevOps, DevSecOps, Site Reliability | Sys Admin | Security | Full Stack | K8s, CI/CD, IaC, SCM | B.S., M.B.A.

Menu

Skip to content
  • Home
  • Resume (LinkedIn)
  • Blog
CentOS7-FIX-ERROR-1146-(42S02)-at-line-1-Table-mysql.global_priv-doesnt-exist

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

Posted by justinmerrill on August 11, 2019 in Linux | 3659 Views | 1 Response

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:
Userid     : "MariaDB Package Signing Key <[email protected]>"
...
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? &#91;Y/n] Y
ERROR 1146 (42S02) at line 1: Table 'mysql.global_priv' doesn't exist

and/or

1
2
Change the root password? &#91;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? &#91;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.

Posted in Linux | Tagged CentOS, MySQL

About the Author

justinmerrill

Related Posts

Step-by-Step Upgrading MySQL 5.5 to 5.6 for Linux Ubuntu 14Step-by-Step Upgrading MySQL 5.5 to 5.6 for Linux Ubuntu 14→

migrate-linux-server-ubuntu-debian-migration-installed-packages-apt-get-reinstall-install-configureSpiffy Way To Migrate All Installed Packages Between Linux Ubuntu Servers (Upgrades Too!)→

Unity GUI root user Login on Ubuntu 16.04LTSLog In as root from Unity GUI on Ubuntu 16.04 & 16.04.2 LTS Server→

One Response

  1. Anonymous
    Anonymous March 9, 2023 at 10:31 am | Permalink

    Thank you! mysql_upgrade indeed did the trick.

    Reply

Leave a Reply Cancel Reply

©2023 Justin Merrill

Menu