Cannot connect to MySQL server on ‘localhost’: Cacti Error

I was installing and setting up Cacti recently and faced following error:

“FATAL: Cannot connect to MySQL server on ‘localhost’.”

This post includes debugging steps and solution for the issue.

Along with the Cacti’s web-interface, the poller.php was generating the same error as follows:

root@ubuntu:/var/www/cacti# php /var/www/cacti/poller.php
FATAL: Cannot connect to MySQL server on 'localhost'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

Also the apache error log expressed following errors:

tail -10f /var/log/apache2/error.log
[Mon May 04 02:17:56 2015] [error] [client 127.0.0.1] PHP Warning:  include_once(/var/www/cacti-0.8.8c/lib/adodb/adodb.inc.php): failed to open stream: Permission denied in /var/www/cacti-0.8.8c/include/global.php on line 205
[Mon May 04 02:17:56 2015] [error] [client 127.0.0.1] PHP Warning:  include_once(): Failed opening '/var/www/cacti-0.8.8c/lib/adodb/adodb.inc.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /var/www/cacti-0.8.8c/include/global.php on line 205
[Mon May 04 02:17:56 2015] [error] [client 127.0.0.1] PHP Warning:  include_once(/var/www/cacti-0.8.8c/lib/database.php): failed to open stream: Permission denied in /var/www/cacti-0.8.8c/include/global.php on line 206
[Mon May 04 02:17:56 2015] [error] [client 127.0.0.1] PHP Warning:  include_once(): Failed opening '/var/www/cacti-0.8.8c/lib/database.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /var/www/cacti-0.8.8c/include/global.php on line 206
[Mon May 04 02:17:56 2015] [error] [client 127.0.0.1] PHP Fatal error:  Call to undefined function db_connect_real() in /var/www/cacti-0.8.8c/include/global.php on line 209
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: host in /var/www/cacti-0.8.8c/lib/database.php on line 35
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: user in /var/www/cacti-0.8.8c/lib/database.php on line 35
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: pass in /var/www/cacti-0.8.8c/lib/database.php on line 35
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: db_name in /var/www/cacti-0.8.8c/lib/database.php on line 35
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: db_type in /var/www/cacti-0.8.8c/lib/database.php on line 35
[Mon May 04 02:18:42 2015] [error] [client 127.0.0.1] PHP Notice:  Undefined variable: port in /var/www/cacti-0.8.8c/lib/database.php on line 35

Troubleshooting steps:

• Verified we have correct dir permisssions (Gave permissions: ugo+rx).
• Verified ping to localhost (works). If you’re having other hostname check to see if it’s ping’able.
• Confirmed that connectivity to mysql-server using mysql client using same parameters works.
• There is no bind-address specified in MySQL config that may cause connectivity issues.

 

After checking on above possibilities, the issue was still there. As said the Cacti’s web-interface on web-browser http://localhost/cacti generated following errors:
FATAL: Cannot connect to MySQL server on ‘localhost’. Please make sure you have specified a valid MySQL database name in ‘include/config.php’

I checked the global.php configurations and found db_connect_read function:

# File: include/global.php

/* connect to the database server */
db_connect_real($database_hostname, $database_username, $database_password, $database_default, $database_type, $database_port, $database_ssl);

The configuration in Cacti’s database config file is as follows:

root@ubuntu:/var/www/cacti# cat include/config.php 
$database_type = "mysql";
$database_default = "cacti";
$database_hostname = "localhost";
$database_username = "cacti";
$database_password = "xxxxx";
$database_port = "3306";
$database_ssl = false;

Changing $database_hostname to 127.0.0.1 in include/config.php made the Cacti setup work!!

$database_hostname = "127.0.0.1";

Though I decided to resolve this keeping database_hostname to “localhost”. I looked at possible issues related to db_connect_real and php.ini details.

Added doing following change in php configuration file:

# vi /etc/php5/apache2/php.ini
mysql.default_socket =/var/lib/mysql/mysqld.sock

Restart apache and boom… Connectivity worked with hostname as “localhost”, the Cacti admin page loaded!!

But why did that socket file change worked for hostname “localhost”!!! and why it was not concerned if I use hostname as IP (127.0.0.1)!

I debugged further and created a softlink to MySQL socket-file at /var/run/mysqld/mysqld.sock and removed “mysql.default_socket =/var/lib/mysql/mysqld.sock ” from php.ini

ln -s /var/lib/mysql/mysqld.sock /var/run/mysqld/mysqld.sock

cat php.ini | grep default_socket
## mysql.default_socket=/var/lib/mysql/mysqld.sock

Restarted apache

/etc/init.d/apache2 restart

and the Cacti worked for “localhost” database host!!

So, for localhost as database_hostname, db_connect_real expect /var/run/mysqld/mysqld.sock as socket! I wonder if db_connect_real would like to buy a $database_socket variable in Cacti’s config.php?!!

Anyways, there could be other issues that we checked earlier while “Troubleshooting Steps”, the issue here was MySQL’s socket file.

• If you’re using location other than /var/run/mysqld/mysqld.sock for mysql socket, specify it in “mysql.default_socket” in php.ini
• If you can restart MySQL, change it to use socket=/var/run/mysqld/mysqld.sock.

Do comment if you have more information on this behaviour!