Missing primary key causes errors when MySQL's sql_require_primary_key variable is set

[justinmacleod]

Description

We are using this plugin for an existing website that we are in the process of migrating to DigitalOcean. We plan on using their Managed Database service. While importing the database export, we discovered an issue that prevented the craft_enupaltranslate_message table from being created. This table does not contain a primary key which causes the import to fail if the MySQL variable sql_require_primary_key is set to true. DigitalOcean recently changed their default variables and this is now required.

I have worked around this by adding a composite primary key for id and language. In order to do this, I had remove the ability for language to be null. This appears to be working okay right now but I am unsure of the ramifications of this. It was at least able to allow me to continue with the setup.

Steps to reproduce

1. Export database from Craft
2. Attempt to import database into a MySQL environment where the sql_require_primary_key variable is set to true.

Additional info

• Craft version: 3.5.16
• PHP version: 7.4.13
• Database driver & version: MySQL 8
• Plugin version: 2.0.0

Refs

• https://www.digitalocean.com/docs/databases/mysql/how-to/create-primary-keys/
• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

[andrelopez]

Hi @justinmacleod we just released Enupal Translate v2.2.0 with a fix to this issue. Thanks for reporting

[andrelopez]

@justinmacleod sorry we had to revert the last migration on v2.21, to fix this issue you may need to run the following command on your db:

set sql_require_primary_key = off

[justinmacleod]

Unfortunately, that flag isn't possible on DigitalOcean. They require a primary key for their managed database service and will fail to import a table without it (regardless of the flag).