Category Archives: MySQL

Installing MySQLnd in your cPanel server !

You might want to install the the MySQL native driver for PHP (MySQLnd) over the custom MySQL driver provided with the default EasyApache.

To enable this manually, you will need to edit the file :

/var/cpanel/easy/apache/rawopts/all_php5 and make sure the following entries are in place there :

–enable-mysqlnd
–with-mysqli=mysqlnd

Save the above file, run EasyApache from WHM and recompile as normal.

 

Fix – “Cannot load from mysql.proc, The table is probably corrupted”

For someone who upgraded MySQL from versions 5.0.x to something like 5.5.x, this error might be a familiar one :

“Cannot load from mysql.proc. The table is probably corrupted”

The database activities would not work as expected and hence you will need to fix this error at the earliest.

The recommended solution from MySQL is to run the mysql_upgrade script which examines all tables in all databases for incompatibilities with the current version of MySQL Server.

mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

You can do this by :

# mysql_upgrade -u root

If you are using a cPanel based system, just run

# mysql_upgrade

To force the check, run

mysql_upgrade --force

Does this result in an error ? “”FATAL ERROR: Upgrade failed””

Look for the logs when this happens and try to spot if some invalid db folders are getting listed during the upgrade.

It might be something like :

mysqlcheck: Got error: 1102: Incorrect database name ‘#dbname#.bk’ when selecting the database

The above error indicates you have an invalid db under the mysql dbs folder, usually – /var/lib/mysql.

Navigate to /var/lib/mysql and find if there are any folders with any extension ( keep only valid db folders and move everything else, like backed up files or so from there )

Once this is complete, re-run the mysql_upgrade script !

 

MySQL databases not showing up space in cPanel

When trying to view the databases from the cPanel interface of an account, you might end up with no values at all. However, if you check the backend, you can find that these database folders consumes space.

To fix this issue, SSH to the server and open the file :  /var/cpanel/cpanel.config and make sure the variable disk_usage_include_sqldbs is set to 1 ( instead of 0 )

Once that change is made, hit the following from the shell :

# /scripts/update_db_cache

This should fix the issue !

 

MySQL server not starting – Part 4 !

When trying to restart the MySQL service, do you happen to find the following error in the logs ?

/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/xx’ (Errcode: 122)
date InnoDB: Error: unable to create temporary file; errno: 122
date [ERROR] Can’t init databases
date [ERROR] Aborting

Under usual circumstances, this error can come up, when the permission of /tmp is inappropriate. The right one should be :

drwxrwxrwt  4 root root size date /tmp

( ie, it should be equivalent to # chmod 1777 /tmp )

You make sure this is the right one set for /tmp, you ensure that disk space aint full, still do you face this issue ?

– If so, check for the number of files and nature of them in /tmp. Delete unnecessary/unwanted hidden/temporary files and restart the service. This should fix it.

 

MySQL server not starting – Part 3

A common issue when MySQL versions are upgraded is the presence of depreciated commands in /etc/my.cnf that would prevent MySQL from restarting.  Other than the presence of depreciated ones, incomplete commands too can cause the issues.

When MySQL is upgraded to 5.5 from 5.1 or so, you might see that the service doesnt get restarted. Looking at the configuration file at /etc/my.cnf do you find something like this ?

slow_query_log_file

Here the path is not referenced, which is the issue confronted. Usually if slow_query_log is enabled in the configuration, then the path to log file should be mentioned, like :

slow_query_log_file=/path-to-file

Either comment off the incorrect line or if you are depending on slow_query_log mention the path to the log file as shown above. Once this is given, your service would restart fine.

 

MySQL server not starting – Part 2

There are ton’s of causes for which MySQL might not start, ranging from disk space full ( highlighted here ) to databases getting corrupt.

First place where you have to check for a clue is the .err log
– ( /var/lib/mysql/hostname.err )

If the err corresponds to something like this :

InnoDB: Page lsn 0 40542, low 4 bytes of lsn at page end 40542
InnoDB: Page number (if stored to page already) 47,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 12
InnoDB: Also the page in the doublewrite buffer is corrupt.
InnoDB: Cannot continue operation.
InnoDB: You can try to recover the database with the my.cnf
InnoDB: innodb_force_recovery=X

One of the reason for this error is the use of multiple storage engines, MyISAM or InnoDB

Check your /etc/my.cnf for any lines which highlight the use
of multiple storage engines.

Following can be an example :

innodb_force_recovery=X
default-storage-engine=MyISAM

The above configuration implies MyISAM is the default storage engine, but another setting related to innoDB is already given, which conflicts.

If your default storage engine is MyISAM, then giving the following option in /etc/my.cnf would fix the issue :

skip-innodb

Once the configuration file is edited, restart the service to save the changes

Note : Mostly applies to MySQL versions < 5.5.x

Memory drain issues on MySQL 5.6 ?

With the recent upgrade of WHM/cPanel, the users get the ability to upgrade their MySQL server to 5.6.x ( x > 6 ).

However, when this upgrade is done, lot of server owners are seeing memory drainage issues. An idle MySQL server tends to consume around 50% of your RAM, which is a very serious concern.  I had to upgrade a personal server of mine and faced serious issues with memory drainage. A more dig on this issue, highlighted a change brought around in the latest versions of MySQL with the parameter ‘performance_schema‘.

Starting from MySQL 5.6.6, this parameter performance_schema is enabled by default and it consumes the server memory even at an idle state. Performance Schema automatically sizes the values of several of its parameters at server start-up if they are not set explicitly, which causes the memory usage to spike up.

The workaround for this issue is to disable performance_schema. This can be done by adding the following value to the configuration file – my.cnf

performance_schema = 0

Add this line and restart MySQL server. Things should be fine from now 🙂

Note : When you try to upgrade MySQL to 5.6.x, from a VPS with 1GB of RAM provisioned you will need to edit the config file and pass the keyword to disable performance_schema ( Yes, before the upgrade ). If not, there are chances for your upgrade to fail partly, due to MySQL upgrade script installing MySQL server components each and it gets killed due to over-usage of RAM as performance_schema is enabled by default.

 

MySQL server not starting !

When trying to start MySQL server, are you facing this error :

“Timeout error occurred trying to start”

Check for the MySQL logs to see if you can track something.

# /var/log/mysqld.log or /var/lib/mysql/hostname.err

( whichever is the log location ) and see if you can trace :

==============

[ERROR] /usr/libexec/mysqld: Disk is full writing ‘ (Errcode: 122). Waiting for someone to free space… Retry in 60 secs
…………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………..

==============

Check for the server disk-space # df -h.

Clear any unwanted logs/files and make sure there is enough free space and restart your service.