在我的日志中太多次 - PDOException：SQLSTATE [HY000] [2002]没有这样的文件或目录

Question

in the last few days my log files have recorded hundreds of those errors, the website is working fine and I am not able to replicate this issue. I believe the error occur when there is a lot of traffic on the website (actually in my access logs it looks like the more errors are caused when crawlers are scanning the website).

I've seen a similar error when exporting the database (few hundred of MBs) through Sequel Pro where in some case "the mysql server has gone away" but if I wait few seconds and try to reconnect the export continues.

I was wondering if there is any fix or if there is any log I can check to find out more about this error, I know that probably increasing the server resources could make the error disappear but I want to be sure that's the real cause.

Is there any mysql config I should look at that could cause (or mitigate) that error?

I also have another question, I'm connecting to mysql using localhost as host, do I get any benefit if I change it to 127.0.0.1?

PS: the backend is a Laravel php application, the more errors have been registered in the homepage (there is only one query being executed on that page and it's a simple select * from table, the table has < 1000 records and it's well indexed, two queries if you are logged in). The website doesn't have so much traffic, around 6k pageviews per day - excluding crawlers.

In some case the first error I get is a "connection refused" followed by the "no such file or directory"

2016-11-15 09:01:07 PDOException: SQLSTATE[HY000] [2002] Connection refused
2016-11-15 09:01:07 PDOException: SQLSTATE[HY000] [2002] No such file or directory

This is what the mysql error.log looks like when I get the mentioned error (in both cases, when I first get the "connection refused" and when I just get the "no such file or directory":

2016-11-15T09:01:07.630308Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-11-15T09:01:07.630539Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2016-11-15T09:01:07.867266Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-11-15T09:01:07.872957Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.16-0ubuntu0.16.04.1) starting as process 5603 ...
2016-11-15T09:01:07.897867Z 0 [Note] InnoDB: PUNCH HOLE support available
2016-11-15T09:01:07.897929Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-11-15T09:01:07.897945Z 0 [Note] InnoDB: Uses event mutexes
2016-11-15T09:01:07.897955Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2016-11-15T09:01:07.897964Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-11-15T09:01:07.897973Z 0 [Note] InnoDB: Using Linux native AIO
2016-11-15T09:01:07.899168Z 0 [Note] InnoDB: Number of pools: 1
2016-11-15T09:01:07.900244Z 0 [Note] InnoDB: Using CPU crc32 instructions
2016-11-15T09:01:07.902815Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2016-11-15T09:01:07.915239Z 0 [Note] InnoDB: Completed initialization of buffer pool
2016-11-15T09:01:07.917561Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2016-11-15T09:01:07.933388Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2016-11-15T09:01:07.938259Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 6776343956
2016-11-15T09:01:07.938307Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 6776343965
2016-11-15T09:01:07.938643Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 6776343965
2016-11-15T09:01:07.938665Z 0 [Note] InnoDB: Database was not shutdown normally!
2016-11-15T09:01:07.938678Z 0 [Note] InnoDB: Starting crash recovery.
2016-11-15T09:01:08.434497Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2016-11-15T09:01:08.434545Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2016-11-15T09:01:08.434598Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2016-11-15T09:01:08.464096Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2016-11-15T09:01:08.465115Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2016-11-15T09:01:08.465130Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2016-11-15T09:01:08.465391Z 0 [Note] InnoDB: Waiting for purge to start
2016-11-15T09:01:08.515613Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 6776343965
2016-11-15T09:01:08.516097Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-11-15T09:01:08.516403Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2016-11-15T09:01:08.530024Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2016-11-15T09:01:08.530080Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-11-15T09:01:08.530102Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2016-11-15T09:01:08.530142Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2016-11-15T09:01:08.539042Z 0 [Note] InnoDB: Buffer pool(s) load completed at 161115  9:01:08
2016-11-15T09:01:08.566608Z 0 [Note] Event Scheduler: Loaded 0 events
2016-11-15T09:01:08.566918Z 0 [Note] /usr/sbin/mysqld: ready for connections.

Answer 1

The diference between using '127.0.0.1' and 'localhost' is that mysql will try to connect using standard network mechanism or using a local machine socket (this one is recommended because its faster)

The error appears because Doctrine has some kind of "localhost socket detector" that appears to fail in several cases, as described in here

PDOException SQLSTATE[HY000] [2002] No such file or directory

or

Troubleshooting "No such file or directory" when running `php app/console doctrine:schema:create`

The best solution (using 127.0.0.1 it doesnt look the good one for me) is to discover the socket location

mysql> show variables like 'socket';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

and pass that value to the mysql connector on php (in laravel it seems that its located in database.php)