Tuesday, September 4, 2012

MySQL Tricks and Tips

This post aims to provide solution for the common issues which I faced and found other people (on internet posts) also facing. I will keep on updating issues and their resolutions as and when I find them.

MySQL 5.1

Issue 1:
After compilation from source and installation. If you try to start MySQL Server and see following error :

mayank@mayank-pc:~/mysql-bin$ ./libexec/mysqld --vardir=.
120904 14:27:05 [Warning] Can't create test file /home/mayank/mysql-bin/var/mayank-pc.lower-test
120904 14:27:05 [Warning] Can't create test file
/home/mayank/mysql-bin/var/mayank-pc.lower-test
./libexec/mysqld: Can't change dir to '$INSTALL_DIR/var/' (Errcode: 2)
120904 14:27:05 [ERROR] Aborting

120904 14:27:05 [Note] ./libexec/mysqld: Shutdown complete


Resolution :
MySQL is not able to find the data directory as none is specified while running mysqld or in configuration file (if any). Specify datadir explicitely in command or in configuration file.

Command:
mayank@mayank-pc:~/mysql-bin$ ./libexec/mysqld --datadir=./data

Configuration File:
[mysqld]
datadir=/home/mayank/mysql-bin/data


Issue 2:
After compilation from source and installation. If you try to start MySQL Server and see following error :

mayank@mayank-pc:~/mysql-bin$ ./libexec/mysqld
./libexec/mysqld: Table 'mysql.plugin' doesn't exist
120904 14:35:40 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120904 14:35:40 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist



Resolutions:
Same as above.



To be continued ...