SlackwareFixMySQLCrash

How to fix a brand-new Mysql installation crash

First of all, let me be the first to say: MYSQL SUCKS! There, I feel better now...

Now, a confession: skip to the TL;DR section below, this is going to save you a lot of time. I am stupid, what can I say?

This is not earth-shattering, just a simple reminder that, if you get the following message when trying to start Mysql under Slackware (and probably many other free unix as well):

root@udon:~# /etc/rc.d/rc.mysqld start
root@udon:~# 120613 21:45:07 mysqld_safe Logging to '/var/lib/mysql/udon.err'.            <--- Cool! It starts!
120613 21:45:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120613 21:45:08 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended           <--- Wait, what?

This means you haven't initialized the database yet...

This can be checked in the file named /var/lib/mysql/udon.err, which contains:

root@udon:~# less /var/lib/mysql/udon.err
120613 21:43:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
120613 21:43:15 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
120613 21:43:15  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
120613 21:43:15  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120613 21:43:16  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
120613 21:43:16  InnoDB: Started; log sequence number 0 0
120613 21:43:16 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
120613 21:43:16 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended
120613 21:45:07 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Table 'mysql.plugin' doesn't exist
120613 21:45:07 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: Log scan progressed past the checkpoint lsn 0 37356
120613 21:45:07  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 0 44233
120613 21:45:07  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 (... etc ...) 99
InnoDB: Apply batch completed
120613 21:45:08  InnoDB: Started; log sequence number 0 44233
120613 21:45:08 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
120613 21:45:08 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended

You can see above the same message comes back regularly: Table 'mysql.host' doesn't exist, and that's all you need to know.

Look for the program named mysql_install_db and run it to properly initialize all tables.

This is what it does on my personal machine:

root@udon:~# /usr/bin/mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h udon password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

Mysql should now be ready to start, which can be tested with a simple /etc/rc.d/rc.mysqld start... And... It does not work!

Here is the result:

root@udon:~# grep -i 22:12 /var/lib/mysql/udon.err 
120613 22:12:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120613 22:12:22 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120613 22:12:22  InnoDB: Database was not shut down normally!
120613 22:12:22  InnoDB: Starting an apply batch of log records to the database...
120613 22:12:23  InnoDB: Started; log sequence number 0 44233
120613 22:12:23 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
120613 22:12:23 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)
120613 22:12:23 mysqld_safe mysqld from pid file /var/run/mysql/mysql.pid ended

But wait, there is more! The errno:13 refers to an access right issue, and not to missing file problem.

As a matter of fact, the Mysql error message 120613 22:12:23 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) is very misleading in this respect: the host.frm file is present and has been created correctly.

You can check this by entering the two following commands:

root@udon:~# find / -name host.frm             
/var/lib/mysql/mysql/host.frm

root@udon:~# ll /var/lib/mysql/mysql/host.frm
-rw-rw---- 1 root root 9510 Jun 13 21:49 /var/lib/mysql/mysql/host.frm

And here is your problem: the file has been created by the program mysql_install_db... but, since I launched it as root, it created the file with owner root and group root.

That's easy enough to correct:

root@udon:~# chown -R mysql:mysql /var/lib/mysql/mysql/*

And now, everything should be starting OK:

root@udon:~# /etc/rc.d/rc.mysqld start                    
root@udon:~# 120613 22:20:18 mysqld_safe Logging to '/var/lib/mysql/udon.err'.
120613 22:20:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

root@udon:~# ps faxu | grep -i mysql
root     13387  0.0  0.0   2212   632 pts/1    S+   22:20   0:00  |       \_ grep -i mysql
root     13315  0.2  0.0   3024  1496 pts/1    S    22:20   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql \
--pid-file=/var/run/mysql/mysql.pid --skip-networking
mysql    13374  0.5  0.6 109984 14332 pts/1    Sl   22:20   0:00  \_ /usr/libexec/mysqld --basedir=/usr \
--datadir=/var/lib/mysql --user=mysql --skip-networking --log-error=/var/lib/mysql/udon.err --pid-file=/var/run/mysql/mysql.pid

Now everything works, takes a bit of detective work, but it's up and running... :-)

(Please note that the mysql processes shown above use the option --skip-networking which is a very good thing - another reason to like Slackware!)

Let's be paranoid... Does it REALLY work?

The following was suggested by Mysql in a nutshell - another awesome resource by O'Reilly (highly recommended, as usual!):

root@udon:~# mysql -h localhost -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT user, Host FROM mysql.user;
+------+-----------+
| user | Host      |
+------+-----------+
| root | 127.0.0.1 |
|      | localhost |
| root | localhost |
|      | udon      |
| root | udon      |
+------+-----------+
5 rows in set (0.00 sec)

mysql> quit
Bye

Yep, it works. Whew!

Please note that there is still no password set for root in the above example.

Summary (aka TL;DR)

Run /usr/bin/mysql_install_db and make sure all the files are owned my user mysql. And that's all there is to it!

Also: I am stupid. If you take a look at the /etc/rc.d/rc.mysqld it says there, written in the immortal words of The Man, Patrick Volkerding himself:

# Before you can run MySQL, you must have a database.  To install an initial
# database, do this as root:
#
#   mysql_install_db --user=mysql
#
# Note that the mysql user must exist in /etc/passwd, and the created files
# will be owned by this dedicated user.  This is important, or else mysql
# (which runs as user "mysql") will not be able to write to the database
# later (this can be fixed with 'chown -R mysql.mysql /var/lib/mysql').
#
# To increase system security, consider using "mysql_secure_installation"
# as well.  For more information on this tool, please read:
#   man mysql_secure_installation

So, there you have it: I am stupid, and I should have checked this file first thing. Slackware is very well documented!

Update!

Run /usr/bin/mysql_secure_installation --user=mysql for a much better installation procedure, and one that won't mess up your installation with un-necessary users and databases... ;-)

Fix error 1017

As an update to the update, you will sometime find yourself staring at this type of error:

mysql ERROR 1017 (HY000): Can't find file: <insert file name here>

This error is more likely caused by a wrong permissions on a Mysql file that was imported from a dump.

To fix, give the proper UID:GID to the database files. In my case:

# /etc/init.d/mysql stop && chown -Rfv mysql:mysql /var/lib/mysql/galactusdb/ && /etc/init.d/mysql start

All on one line, the command above stops Mysql, changes the owner and group of all the files of the database galactusdb and restarts Mysql.

This should not take more than a couple of seconds, which is an acceptable downtime.

If all else fails, try the following command to attempt to fix all the local Mysql databases:

# /usr/bin/mysqlcheck -u put_your_admin_name_here -p'put_your_admin_password_here' --auto-repair --all-databases

If you still have errors after this, I am afraid your database is corrupted, and will have to be restored by hand...

See Also: