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".
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".
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.