Can't connect to server through socket

[Bestrafung]

We're still running a non-optimized mysql setup. I'm in the process of optimizing it now so any suggestions are welcome. Until recently we haven't had any issues but now we are receiving an error regarding socket connections. It seems to occur randomly and not for every script all the time. Some searches suggested the mysql.sock file missing or the hard disks full. I've checked both and there are no issues there. My theory is that the max connections is being exceeded and that's why we get the socket error. That's just a guess and I don't know how to check.

Here's the message I get in the email throughout the day, the PHP scripts differ but the socket message is always the same:

Database error in vBulletin :

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) /home/xxxxxxxx/public_html/includes/class_core.php on line 317

MySQL Error   : 
Error Number  : 
Request Date  : Saturday, March 1st 2014 @ 02:58:27 PM
Error Date    : Saturday, March 1st 2014 @ 02:58:27 PM
Script        : http://www.xxxxxxxx.com/cron.php?rand=1393703903
Referrer      : http://www.xxxxxxxx.com/tournaments.php?t10
IP Address    : x.x.x.x
Username      : 
Classname     : vB_Database
MySQL Version :

Any ideas would be welcome.

Edit: Could a moderator please rename the thread title? I somehow had a typo and can't fix it.

 

[cPanelMichael]

Hello

Could you check and let us know of any specific output in the MySQL error log at the time of the database connection failure?

Thank you.

 

[Bestrafung]

Thank you for the response. I think I may have actually found the issue. After running a chkdsk on the host drive and then fsck with Knoppix and fixing several errors everything seems to be stable again. It's been several hours since we've had an issue. I'll keep this updated.

 

[cPanelMichael]

I am happy to see the issue might be resolved. Thank you for updating us with the outcome.

 

[Bestrafung]

I just wanted to update this to say everything has been more stable but the issue still occurs from time to time. I found some InnoDB issues possibly related to the file system corruption and have resolved most of them by restoring backups. I still have one major InnoDB issue left that I'm working on now so let's hope it fixes everything.

 

[Bestrafung]

I was still getting an average of 5 database error notifications from our forums every day, occurring mostly around 3am, 8-10am, and 4-7pm but everything was still working. To troubleshoot I tried mysqlcheck on all databases but it crashed. So I then ran mysqlcheck on each individual database. When I found one that crashed I dumped and dropped it then restored it as MyISAM instead of InnoDB. So far everything is much better. I can now run a repair and optimize on all of the tables except horde.

When I tried to dump horde I got this error:
mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES

I then read that you can run a forced update of horde to fix/restore the database/tables. So I turned on innodb force recovery in my.cnf and tried to drop the database. It did so but with errors. When I tried to remove the innodb force recovery and restart mysql it failed. I had to re-enable force recovery for it to start. Unfortunately I can't do much more until later, I can't risk anymore serious sql issues during peak hours.

I need to completely drop the horde database and get InnoDB starting without errors then do a forced update on horde. Can anyone provide some pointers on how to do this without killing the server? I'm going to wait until backups run tonight so I have full MyISAM backups before proceeding any further since the InnoDB backups were failing. I'd appreciate any assistance. Also, let me say that Windows Server is terrible. All of this started because of a simple fragmentation and chkdsk issue. Of course I can't be sure but I don't think this wouldn't occurred on dedicated hardware or in a Linux host.

EDIT: I forgot to mention that the /var/lib/mysql/horde directory is empty but it still thinks the database is there. I'm unable to drop it completely.

 

[Bestrafung]

Before I do something stupid and break everything I wanted to post my next planned steps before I proceed. Please stop me if I'm wrong.

Using the below query I believe it's safe to say there are no InnoDB tables remaining:
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

My plans are to stop mysql, backup the ibdata1 file, remove it, remove the force recovery setting in my.cnf, and finally start mysql. Will this break anything further? From what I've found removing the ibdata1 file will resolve all of my stubborn InnoDB errors and get everything back to normal but I want to be sure.

 

[cPanelMichael]

Using the below query I believe it's safe to say there are no InnoDB tables remaining:
SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Using this command will only produce accurate results if InnoDB is actually running. If you have InnoDB disabled then you would not see output from that command.

Thank you.

 

[Bestrafung]

Using this command will only produce accurate results if InnoDB is actually running. If you have InnoDB disabled then you would not see output from that command.

Thank you.

Thanks for the reply. InnoDB was running and I believe everything is working now. Following the steps from my last post seems to have cleared everything up, all I have left is to run the Horde update and see if the database sets up. I cleared the error log and on restart I get the below:

140410 09:08:59 mysqld_safe mysqld from pid file /var/lib/mysql/server1.thehivenetwork.net.pid ended
140410 09:09:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140410  9:09:00 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
140410  9:09:00 [Note] Plugin 'FEDERATED' is disabled.
140410  9:09:00 InnoDB: The InnoDB memory heap is disabled
140410  9:09:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140410  9:09:00 InnoDB: Compressed tables use zlib 1.2.3
140410  9:09:00 InnoDB: Using Linux native AIO
140410  9:09:00 InnoDB: Initializing buffer pool, size = 128.0M
140410  9:09:00 InnoDB: Completed initialization of buffer pool
140410  9:09:00 InnoDB: highest supported file format is Barracuda.
140410  9:09:00  InnoDB: Waiting for the background threads to start
140410  9:09:01 InnoDB: 5.5.36 started; log sequence number 1595675
140410  9:09:01 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140410  9:09:01 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140410  9:09:01 [Note] Server socket created on IP: '0.0.0.0'.
140410  9:09:01 [Note] Event Scheduler: Loaded 0 events
140410  9:09:01 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.36-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

This looks fairly normal to me but I'm no expert so could anyone let me know if this is good or not?

 

[cPanelMichael]

Yes, that output shows MySQL with InnoDB support started successfully.

Thank you.