Having trouble transferring large databases to a managed database, as the mysql settings seem to be very strict and hard to work round, and when changing the mysql mode via the control panel it doesn’t seem to be reflected when trying to import, at least not straight away.

I have issues with ‘sqlrequireprimary_key’, which i understand there’s no work around for, as well as “zero dates” which the settings do not seem to effect.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
2 answers

answering my own question, seems the zero date setting just took a while to appear

Hi there,

Thanks for reaching out to DigitalOcean Support. I understand you are trying to import large database to managed database and having issues with sqlrequireprimary_key.

We currently have the variable sqlrequireprimary_key turned on to enable user create primary key on tables to avoid replication issue, node replication, etc. This has worked in the past and was not enforced but experience, time, and the information we have gathered from frequent issues ex. time it takes to create a new node for a service from a backup with large tables.

Primary keys are essential for certain management operations also for services that do not have standby or read replica service; any node replacements are performed by first bringing up a standby to which all data from old master is replicated and without primary keys this process may take exceedingly long or fail, Also failed nodes are replaced by restoring backup, which requires playing back binary logs and that may not work if large tables without primary keys have had recent changes.

If you decide you to proceed without Primary Key, you can proceed with

SET SESSION sqlrequireprimary_key = 1;

Note this is for a single session though. Once you log out, you would need to run this again before you could do an import again without it being required.

The below URL has more information

https://www.digitalocean.com/docs/databases/mysql/how-to/create-primary-keys/#how-digitalocean-uses-primary-keys%3Chttps://www.digitalocean.com/docs/databases/mysql/how-to/create-primary-keys/%3E

Please let us know if you have additional questions/concerns. We would be happy to assist you.

Regards
Priyanka
DigitalOcean Support

Submit an Answer