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
  • LinkedIn
  • Links
  • Privacy Policy & Terms of Use
Header image

Justin Merrill, BS, MBA

DevOps|Software Engineer| FullStack|Application Developer| LAMP/LEMP|Javascript|PHP|Perl|SQL/MySQL| Cloud Engineer: SaaS, IaaS, PaaS, IaC|

Menu

Skip to content
  • Home
  • Resume (LinkedIn)
  • Blog
  • Links
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 | 1670 Views | Leave a 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 [email protected] -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.

Share this:

  • Twitter
  • Facebook

Related

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→

Leave a Reply Cancel reply

Recent Posts

  • Optimum “Universal” Nginx1.18.+ Reverse-Proxy to Apache2.4+ Configuration For PHP7+ Web Applications (Including WordPress & WPMU) with Cloudflare SSL, DNS, Caching & More
  • FIX for CentOS7 MySQL/MariaDB – ERROR 1146 (42S02) at line 1: Table ‘mysql.global_priv’ doesn’t exist
  • Spiffy Way To Migrate All Installed Packages Between Linux Ubuntu Servers (Upgrades Too!)
  • A Great Example of BAD Web Design
  • Log In as root from Unity GUI on Ubuntu 16.04 & 16.04.2 LTS Server

Recent Comments

  • Bruce Banner on How Do YOU Fix a Broken Motherboard Header Pin? (USB, CPU, 1394, case fan, IDE, etc)
  • Domo on How Do YOU Fix a Broken Motherboard Header Pin? (USB, CPU, 1394, case fan, IDE, etc)
  • hard core technical advise needed - Techist - Tech Forum on How Do YOU Fix a Broken Motherboard Header Pin? (USB, CPU, 1394, case fan, IDE, etc)
  • René on How Do YOU Fix a Broken Motherboard Header Pin? (USB, CPU, 1394, case fan, IDE, etc)
  • john dixon on How Do YOU Fix a Broken Motherboard Header Pin? (USB, CPU, 1394, case fan, IDE, etc)

Tags

CentOS Computer Computer Repair Database DIY Do It Yourself Fix Mod Modification MySQL Repair Technology Update Upgrade Web Application Developer Web Developer

©2000 - 2022 Justin Merrill

Menu

  • LinkedIn
  • Give Thank$
  • Privacy Policy & Terms of Use