Cloud SQL instance 2nd generation, MySQL 5.7 InnoDB: Assertion failure, Cannot allocate 18446744073709546192 bytes

[Jacob Hagstedt P Suorra]

Hi!

On Monday (170807) I noticed that we started to get unstable connections to our Cloud SQL instance (2nd generation). Under the "Operations" tab I noticed that a backup had failed.

The database seemed to work on and off, until today Tuesday (170808) when it totally broke down on us. Now it has a yellow warning sign beside it, saying "Under maintenance", and I cannot do anything with it.

Checking the logs, and I found this:

[ERROR] InnoDB: Cannot allocate 18446744073709546192 bytes of memory after 60 retries over 60 seconds. OS error: Cannot allocate memory (12). Check if you should increase the swap file or ulimits of your operating system. Note that on most 32-bit computers the process memory space is limited to 2 GB or 4 GB.

(See the attached mysql_err_log_1.txt for complete log)

and I found this MySQL bug: https://bugs.mysql.com/bug.php?id=70185 which I find very similar to the error message that I got. That person is suggesting two things:

1) add 'innodb_force_recovery=4' to [mysqld] in my.cnf

or....

2) set 'innodb_change_buffering=none' in my.cnf 

But these are flags that gcloud won't allow me to change:

gcloud sql instances patch [INSTANCE-NAME] --database-flags innodb_force_recovery=4

    The following message will be used for the patch API method.

    {"project": [PROJECT-NAME], "name": [INSTANCE-NAME], "settings": {"databaseFlags": [{"name": "innodb_force_recovery", "value": "4"}]}}

    WARNING: This patch modifies a value that requires your instance to be

    restarted. Submitting this patch will immediately restart your

    instance if it's running.

    Do you want to continue (Y/n)?  Y

ERROR: (gcloud.sql.instances.patch) HTTPError 404: Flag requested cannot be set.

And non of those flags can be found here: https://cloud.google.com/sql/docs/mysql/flags 

I have also tried to spin up multiple new SQL instances, one with the same spec as the original, and one with everything doubled (vCPU, RAM, SSD) in case there was any resource limits that hit the ceiling, and tried to restore to a previous point in time using our backups, but without any luck. I've tried to restore to the backup one day before this happened (One backup at 6 Aug 2017, 03:23:17, and one at 4 Aug 2017, 00:55:01) 

All my services that tries to access the database get one of either (2013, 'Lost connection to MySQL server during query') or (2006, 'MySQL server has gone away').

I've also tried to do an export using the Google cloud web UI, but that export fails with the following error message:
mysqldump: Couldn't execute 'show table status like 'email\_meta'': Lost connection to MySQL server during query (2013)

Trying to do an export using Sequel Pro (a mysql client for mac) yields the same problem: The connection fails.

This means I cannot even export my data so that I can manually re-create all my tables and insert data.

I'm running out of ideas of how to find what is wrong, does anyone have any idea and can help me, it will be highly appreciated!

I'll also attach the logs for one of the newly created instances which I used to restore the 170804 backup. There it can be seen that the mysql server restarts multiple times but fails to keep alive. That file is called mysql_err_log_2.txt

[Kenworth (Google Cloud Platform)]

Given your current situation, I would recommend you file a defect report on Public Issue Tracker for better tracking purposes. Provide more information such as your Project-ID, Cloud SQL instance name, and other sanitized details such as config files on top of the information you mentioned above for better assistance to your specific issue. You can also drop the issue link on this thread so other users experiencing the same issue would be able to reference it.

[Jacob Hagstedt P Suorra]

Thank you for your answer. I've filed a defect report and added the information you mentioned: https://issuetracker.google.com/issues/64501064