MySQL server has gone away, inconsistent error, caused by a single script

I have a PHP script that is launching an SQL query, which takes a few seconds and returns about 15K+ results, after making a complex calculation based on data found on a DB table on my server, named table.

After getting the results, it is supposed to Insert (using a Replace) these records onto another DB table on my server.

The script used to work flawlessly when table was a simple core table. I later modified the SQL query to use data from a VIEW table, we can call it view_table, which is based on table but has an extra column that is calculated on the fly.

This had made the script to start crashing my whole SQL server, every once in awhile, throwing this error:

PHP Warning: mysqli::query(): MySQL server has gone away in /home/user/script.php on line 109

Below is line 109:

function getRecordsFromDB(){
    logMemoryUtilizationDetails();
    file_put_contents(LOG_DIRECTORY.'service.log', date("d/m:H:i").':'."getRecordsFromDB::Entry".PHP_EOL, FILE_APPEND);
    global $sourceConn;
    $selectQuery = file_get_contents(__DIR__.'/my-query.sql');
    $items = array();
    $result = $sourceConn->query($selectQuery); // LINE 109
    if ($result){
        if ($result->num_rows > 0) {

            while($row = $result->fetch_assoc()) {
                $item = new Item();
                $item->id               = $row['id'];
                $item->itemId               = $row['itemid'];

I tried to log create to see how much memory does script use on start and exit, and when it is successful, it is using just about 37MB of RAM at its peak.

My server has 6GB of RAM on 4 cores.

There is no other script running on my server that is causing SQL server crashes like this, so I’m sure that this script is causing the crash.

Here is the MY.CNF of my server:

[mysqld]
# disable mysql strict mode
sql_mode=""

log-error=/var/lib/mysql/host.myhost.com.err
performance_schema=1

query_cache_type=0
query_cache_size=0
query_cache_limit=0

key_buffer_size=16M
max_connections=200
max_tmp_tables=1
table_open_cache=2000
local-infile=0
thread_cache_size=4
innodb_file_per_table=1
default-storage-engine=MyISAM
innodb_use_native_aio=0
max_allowed_packet=1024M
innodb_buffer_pool_size=800M
open_files_limit=10000
#wait_timeout=500
tmp_table_size=256M
max_heap_table_size=256M
innodb_buffer_pool_instances=1
#general_log=on
#general_log_file = /var/lib/mysql/all-queries.log

slow-query-log=0
slow-query-log-file=/var/lib/mysql/slow_queries.log
long_query_time=1.0
#log_queries_not_using_indexes=1

And this is from my PHP.INI (for PHP 7.2 which I’m using):

max_execution_time = 240

max_input_time = 60

max_input_vars = 1000

memory_limit = 512M


[MySQLi]

mysqli.max_persistent = -1

;mysqli.allow_local_infile = On

mysqli.allow_persistent = On

mysqli.max_links = -1

mysqli.cache_size = 2000

mysqli.default_port = 3306

mysqli.default_socket =

mysqli.default_host =

mysqli.default_user =

mysqli.default_pw =

mysqli.reconnect = Off

I don’t see any mysql.connect_timeout setting in those files.

I have many other scripts and they all work fine, so I wouldn’t want to change something globally as I’m afraid it can cause other issues on my server.

Answer

Looks like a timed-out or failed query. Please paste the sql query you are using. You can also try and see yourself where the query might cause this by pasting the query in your mysql IDE (Navicat is my favorite) and prepend it with ‘explain extended’ (no quotes). So your query would look like ‘explain extended select …(all 300 lines)’ Look for keys higher than 4, no primary keys and rows queried with really high numbers for starters. Also, it looks like instead of a view you may want to consider creating a stored procedure in which you can select everything into a temporary table and then do the on-the-fly calculated value in the next query. Of course, you need to configure my.cnf to recognize temporary table so it can destroy it once the session is complete. Also, if you have any replication or cluster of servers, make sure to stop binlog before creating the temporary table and then start it once your queries are completed and your session is about to close.

If you like, please paste your my.cnf (mysql config file) to make sure your config is optimal for the large query.

Also, for trouble-shooting purposes, you may want to temporarily increase the max execution time in php.ini.

Advertisement

Answer