"Lost connection to MySQL server during query" in Google Cloud SQL

Question

I am having a weird, recurring but not constant, error where I get "2013, 'Lost connection to MySQL server during query'". These are the premises:

• a Python app runs around 15-20minutes every hour and then stops (hourly scheduled by cron)

• the app is on a GCE n1-highcpu-2 instance, the db is on a D1 with a per package pricing plan and the following mysql flags

max_allowed_packet    1073741824
slow_query_log    on
log_output    TABLE
log_queries_not_using_indexes on

• the database is accessed only by this app and this app only so the usage is the same, around 20 consecutive minutes per hour and then nothing at all for the other 40 minutes

• the first query it does is

SELECT users.user_id, users.access_token, users.access_token_secret, users.screen_name, metadata.last_id
FROM users
LEFT OUTER JOIN metadata ON users.user_id = metadata.user_id
WHERE users.enabled = 1

• the above query joins two tables that are each around 700 lines longs and do not have indexes

• after this query (which takes 0.2 seconds when it runs without problems) the app starts without any issues

Looking at the logs I see that each time this error presents itself the interval between the start of the query and the error is 15 minutes.

I've also enabled the slow query log and those query are registered like this:

    start_time: 2014-10-27 13:19:04
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 760
 rows_examined: 1514
            db: foobar
last_insert_id: 0
     insert_id: 0
     server_id: 1234567
      sql_text: ...

Any ideas?

Answer 1

If your connection is idle for the 15 minute gap the you are probably seeing GCE disconnect your idle TCP connection, as described at https://cloud.google.com/compute/docs/troubleshooting#communicatewithinternet. Try the workaround that page suggests:

sudo /sbin/sysctl -w net.ipv4.tcp_keepalive_time=60 net.ipv4.tcp_keepalive_intvl=60 net.ipv4.tcp_keepalive_probes=5

(You may need to put this configuration into /etc/sysctl.conf to make it permanent)