MySql do not start causing an ERROR: PleskMainDBException


Recently we had an issue with Plesk, we were not allowed access and the following error message was displayed to us

ERROR: PleskMainDBException

MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’

0: common_func.php3:168

              db_query(string ‘select param, val from misc’)

1: common_func.php3:587

              get_param(string ‘mysql41_compatible’)

2: common_func.php3:484

              db_set_names()

3: common_func.php3:463

              db_connect_real(string ‘localhost’, string ‘admin’, string ‘*************’, string ‘psa’)

4: common_func.php3:443

              db_connect()

5: auth.php3:90

Narrowing down the problem, we did the following tasks.

 

[[email protected] etc]# /etc/init.d/psa restart

ERROR 1033 (HY000) at line 1: Incorrect information in file: ‘./psa/sessions.frm’

[[email protected] etc]# service mysqld restart

Stopping MySQL:                                                                                       [   OK   ]

Starting MySQL:                                                                                       [   OK   ]

[[email protected] etc]# /etc/init.d/psa restart

The problem turned out to be that we had been tuning the MySql by adding chnaging values and adding new options in the  /etc/my.cnf file  
that we use, you’d think that a reload of mysqld would do. Normally, that’s all you need to have mysqld read off the new settings. If you’ve grown so dependent on plesk, you would not notice that mysqld didn’t actually start, and you will get an error when you load plesk, thinking that it’s a plesk error:
 

ERROR: PleskMainDBException

MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’

0: common_func.php3:168

              db_query(string ‘select param, val from misc’)

1: common_func.php3:587

              get_param(string ‘mysql41_compatible’)

2: common_func.php3:484

              db_set_names()

3: common_func.php3:463

              db_connect_real(string ‘localhost’, string ‘admin’, string ‘*************’, string ‘psa’)

4: common_func.php3:443

              db_connect()

5: auth.php3:90

 
So this is a mysqld failing to start properly problem. To fix this, go to your mysql datadir and see if the sizes of your innodb log files match those in your .cnf. we usually set innodb_log_file_size = 256M and innodb_log_buffer_size = 256M in /etc/my.cnf.
 
This is the content of the /etc/mysql/my.cnf:
[mysqld]
set-variable=local-infile=0

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M

log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days = 10
max_binlog_size = 100M

skip-bdb
#
# * InnoDB
#

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

If they don’t match, stop mysqld, remove the ib_logfile0 and ib_logfile1 files, and start mysqld. be careful not to delete the ibdata1 or ibdata2 files or you lose your data. Avoid using rm -r ib*.  innodb will recreate the log files to match your .cnf and mysqld now becomes available for plesk.

 

Menu