I've been using PHP PDO with the default setting of
ATTR_EMULATE_PREPARES = true and it was working fine.
However, I wanted to be able to select database values as integers instead of having them converted to strings. When I set
ATTR_EMULATE_PREPARES = false I was able to get integers, but I started getting these errors in the web error log:
Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
The errors are intermittent and I cannot duplicate them at will. I cannot duplicate the problem on my dev system (which has PHP/Apache/MariaDB on the same system). The problem only occurs on my production servers. The production webserver and database server are two separate systems, but are not heavily loaded. I only see errors on the web server, I have not noticed any problems in the database server logs.
I've tried adjusting various timeouts and buffer sizes and nothing seems to make any difference. Most timeouts are set to 60 seconds or more, but the problem occurs on pages with queries that take 15 seconds or less. Sometimes they fail with the above error, but then it will work fine when I refresh the page.
It took me a while to figure out what was going on, but I finally remembered I had changed the
ATTR_EMULATE_PREPARES setting a few days ago. As soon as I changed it back to
true (which is the default), all the problems disappeared.
I tried setting
ATTR_STRINGIFY_FETCHES = false and hoped it would let me select integer values, but that setting does not appear to have any effect.
I'm running the following stack: Apache 2.4.18, PHP 7.3.20-1, MariaDB 10.2.27, Ubuntu 16.04.6
I don't really care about
ATTR_EMULATES_PREPARES, I only changed the setting so I could select integer values. Is there any way to do this without causing errors?