My Laravel 5.7 website has been experiencing a few problems that I think are related to each other (but happen at different times):
PDO::prepare(): MySQL server has gone away
E_WARNING: Error while sending STMT_PREPARE packet. PID=10
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
(My database often seems to try to write the same record twice in the same second. I've been unable to figure out why or how to reproduce it; it doesn't seem to be related to user behavior.)- Somehow, those first 2 types of errors only ever appear in my Rollbar logs but not on the text logs on the server or in my Slack notifications, as all errors are supposed to (and all others do).
For months, I've continued to see scary log messages like these, and I've been completely unable to reproduce these errors (and have been unable to diagnose and solve them).
I haven't yet found any actual symptoms or heard of any complaints from users, but the error messages seem non-trivial, so I really want to understand and fix the root causes.
I've tried changing my MySQL config to use max_allowed_packet=300M
(instead of the default of 4M) but still get these exceptions frequently on days when I have more than a couple of visitors to my site.
I've also set (changed from 5M and 10M) the following because of this advice:
innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M
As further background:
- My site has a queue worker that runs jobs (
artisan queue:work --sleep=3 --tries=3 --daemon
). - There are a bunch of queued jobs that can be scheduled to happen at the same moment based on the signup time of visitors. But the most I see that have happened simultaneously is 20.
- There are no entries in the MySQL Slow Query Log.
- I have a few cron jobs, but I doubt they're problematic. One runs every minute but is really simple. Another runs every 5 minutes to send certain scheduled emails if any are pending. And another runs every 30 minutes to run a report.
- I've run various
mysqlslap
queries (I'm completely novice though) and haven't found anything slow even when simulating hundreds of concurrent clients. - I'm using Laradock (Docker).
- My server is DigitalOcean 1GB RAM, 1 vCPU, 25GB SSD. I've also tried 2GB RAM with no difference.
- The results from
SHOW VARIABLES;
andSHOW GLOBAL STATUS;
are here.
My my.cnf
is:
[mysql]
[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
character-set-server=utf8
innodb_buffer_pool_chunk_size=218M
innodb_buffer_pool_size = 218M
max_allowed_packet=300M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query_log.log
long_query_time = 10
log_queries_not_using_indexes = 0
Any ideas about what I should explore to diagnose and fix these problems? Thanks.
If you see this message randomly, possible reasons:
Your MySQL is behind a proxy, and they are using different
timeout
config.You are using PHP's persist connection.
You may try to dig into the problem by these steps:
Make sure your connections to MySQL have long enough timeout (eg: proxy setting, MySQL's
wait_timeout
/interactive_timeout
)Disable the persist connection at PHP side.
Do some
tcpdump
if you can to see what happend when you got the error message.