ERROR 1146 (42S02): Table doesn't exist

So some of you might have run across the following errors when installing MySQL 5.6 :

• ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist
• ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
• ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist
• ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist
• ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist

You are likely amazed that you see this error on a fresh database install. You are not alone. The issue is fixable though.

The safest thing to do is to reinstall the mysql database via the following command: mysql_install_db

• http://dev.mysql.com/doc/refman/5.6/en/mysql-install-db.html

I recently had to do this on every fresh install (yes it happened more than once) of MySQL 5.6 on a Solaris Sparc environment.

You can try to use the following to create the missing tables but I found it best to keep everything clean and ensure all is set up with the mysql_install_db.

• http://bazaar.launchpad.net/~mysql/mysql-server/5.6/view/head:/scripts/mysql_system_tables.sql#L103 

Some do recommend the launchpad fix I mentioned above but I like I said I prefer the mysql_install_db to ensure everything is linked installed correctly.

I have other blog posts that include examples on using this command :

• http://anothermysqldba.blogspot.com/search/label/mysql_install_db

Related posts on this topic:

• http://scriptinside.blogspot.com/2013/05/mysql-5611-error-1146.html
• http://bugs.mysql.com/bug.php?id=67179
• http://bugs.mysql.com/bug.php?id=67012
• http://blog.webyog.com/2012/10/11/bummer/
• http://dba.stackexchange.com/questions/25036/error-cannot-find-or-open-table

 If you run across this from tables outside of the mysql_install_db scope see Peter's blog post to help get you started:

• http://www.mysqlperformanceblog.com/2008/12/17/recovering-create-table-statement-from-frm-file/

[Warning] .....because the user was set to 'mysql' earlier on the command line

shell> scripts/mysql_install_db --basedir=/usr/local/demouser  --datadir=/var/lib/demodb --user=demouser --ldata=/var/lib/demodb 

Installing MySQL system tables...

[Warning] Ignoring user change to 'demouser' because the user was set to 'mysql' earlier on the command line

Installation of system tables failed! 


This is an error that makes you look back to the command you just entered and begin to question yourself. The error is not as it appears. If you are installing MySQL onto a server that already has an installation or once did it is likely that a my.cnf file is currently in place.  This file could be very valid for the active server and it is ok to be left alone in those cases. The fix for this error is to override the defaults and enable the MySQL server to pull from a different file that includes the username you prefer.  You can change the username in the file but it is likely that you are looking to test and do other things, so below is an example of how to get the error above up and running again.




shell> cp support-files/my-small.cnf /etc/demodb.cnf

shell> vi /etc/demodb.cnf

       port             = 3307

       socket         = /tmp/demodb.sock

       user            = demouser 
       pid_file       = /var/lib/demodb/demodb.pid


shell> scripts/mysql_install_db --defaults-file=/etc/demodb.cnf --basedir=/usr/local/demouser  --datadir=/var/lib/demodb --user=demouser --ldata=/var/lib/demodb 


Installing MySQL system tables...
OK
Filling help tables...
OK

Again, if a my.cnf file currently exists the mysql_install_db  will try to use that file. Often in that file a username is set which led to the error "the user was set to 'mysql' earlier on the command line"

shell> chown -R demouser /var/lib/demodb/*

shell> # bin/mysqld_safe --defaults-file=/etc/demouser.cnf --user= demouser  --datadir=/var/lib/demodb/ --port=3307 


# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/demodb.server




shell> # bin/mysql --port=3307 --socket=/tmp/demodb.sock 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.30 Source distribution




Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.