1

When doing a PITR-Restore of a second generation Google CloudSQL instance, the restore fails with "Failed to Create" error. I cannot manipulate the instance clone, except reading logs and deleting it.

The mysql.err log shows messages like

E  2017-10-05T14:19:39.259084Z 0 [Note] /usr/sbin/mysqld: ready for connections. 
E  Version: '5.7.14-google-log'  socket: '/mysql/mysql.sock'  port: 3306  (Google) 
E  2017-10-05T14:19:43.151623Z 3 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.017364, pos: 601), semi-sync up to file , position 0. 
E  2017-10-05T14:19:43.151666Z 3 [Note] Semi-sync replication switched OFF. 
E  2017-10-05T14:21:46.173674Z 27 [Note] Aborted connection 27 to db: 'unconnected' user: 'root' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes) 
E  2017-10-05T14:21:52.364195Z 2 [Note] Aborted connection 2 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets) 
E  2017-10-05T14:21:52.395075Z 7 [Note] Aborted connection 7 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets) 
E  2017-10-05T14:21:52.668786Z 29 [Note] Aborted connection 29 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets) 
E  2017-10-05T14:21:52.668816Z 28 [Note] Aborted connection 28 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets) 
E  2017-10-05T14:21:52.875975Z 0 [Note] Giving 1 client threads a chance to die gracefully 
E  2017-10-05T14:21:52.876143Z 0 [Note] Shutting down slave threads 
E  2017-10-05T14:21:54.876268Z 0 [Note] Forcefully disconnecting 1 remaining clients 
E  2017-10-05T14:21:54.876451Z 0 [Warning] /usr/sbin/mysqld: Forcing close of thread 3  user: 'root' 
E  2017-10-05T14:21:54.876479Z 0 [Note] Event Scheduler: Purging the queue. 0 events 
E  2017-10-05T14:21:54.876735Z 0 [Note] Binlog end 
E  2017-10-05T14:21:54.880101Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'

My interpretation is that in Log file 17364 is some operation that exceeds max_allowed_package. (My intention is to restore to some point in log file 17454.) Given that this is technically a clone of a database instance that by definition had the same changes applied already, this error condition does not make too much sense to me.

How do I PITR then?

The procedure I've followed is Performing a point-in-time recovery, i.e. I've created a "clone" and chose "Clone from earlier position", then specified mysql-bin.017364 as "Binary log file name".

Edit: Setting max_allowed_packet

After I've set the flag max_allowed_packet to 1073741824 (1 GiB; the max value) on the origin instance, the error message Got a packet bigger than 'max_allowed_packet' bytes does not appear in the error logs anymore while cloning. However the CloudSQL-Instance still "failed to create", except now I have even less of an idea what to look for. The operations log says "an unknown error occured".

Edit 2:

The PITR operation fails not only with the above instance, but also with others. I have created an independent instance for testing and INSERT a few small rows now and then and try to PITR to various points in time.

To recap: independently of max_allowed_packet, independently of the size of actual write operations PITR fails with no expressive error message. The error message Got a packet bigger than 'max_allowed_packet' bytes was a singular coincident.

Johannes Kohnen
  • 111
  • 5
  • Instead of using gcloud or the Console, can you export a copy of your instance locally using [MySQL](https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html) then import that manual backup? By using MySQL commands directly, you can specify options for [row size](https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html#option_mysqlbinlog_binlog-row-event-max-size) to ensure you are within 'max_allowed_packet'. I know specifically with mysqldump you can set 'max_allowed_packer' and 'net_buffer_length' to stay within the limits. – Jordan Oct 06 '17 at 20:04
  • That wouldn't help as I want to do PITR and I consider this a bug of CloudSQL. – Johannes Kohnen Oct 09 '17 at 11:52
  • To clarify, PITR is simply creating a backup of your instance from a specific position in the binary log, then restoring a clone instance with that backup. You can indeed achieve this via the MySQL commands as previously mentioned. As per the 'max_allowed_packet' issue, it is recommended to directly report this in the [Public Issue Tracker](https://cloud.google.com/support/docs/issue-trackers) to notify Google. – Jordan Oct 10 '17 at 18:32
  • I understand I can administrate the database with native tools. If I have to do that, because CloudSQL operations randomly fail and on top of that CloudSQL adds an opaque layer that hinders me to do proper debugging.... Why am I paying a markup for a managed service, then!? – Johannes Kohnen Oct 12 '17 at 12:35
  • To increase the 'max_allowed_packet' for your project and instance it is recommended to open an [Issue Report](https://cloud.google.com/support/docs/issue-trackers) as previously mentioned. The method of performing the backup manually is an interim workaround while the engineering team would ideally fix the actual issue for you. Of course you can always [directly deploy any number of other MySQL databases](https://console.cloud.google.com/launcher/browse?q=mysql) to the cloud which are not managed like Cloud SQL as you have correctly pointed out. – Jordan Oct 12 '17 at 19:09

1 Answers1

0

- Posting the latest comment as the answer for completeness.

To increase the 'max_allowed_packet' for your project and instance it is recommended to open an Issue Report.

An interim workaround while the engineering team fixes the actual issue for you is to use MySQL PITR to save the point-in-time backup locally. You can then restore a clone instance with that manual backup.

By using MySQL commands directly, you can specify a lower option for row size to ensure you are within 'max_allowed_packet'.

If you are just making a normal backup, you can also use the mysqldump command and lower the max_allowed_packer and net_buffer_length options to stay within the 'max_allowed_packet' limit enforced when restoring the clone from the backup.

Of course you can always directly deploy any number of other MySQL databases to the cloud which are not managed like Cloud SQL as you have correctly pointed out.

Jordan
  • 301
  • 2
  • 8