Create new tables without primary keys#
If you Aiven for MySQL was created after 2020-06-03, then by default your Aiven for MySQL does not allow creating new tables without primary keys. You can check this by following those steps:
Select the Aiven for MySQL service that you want to check.
On the Overview tab, in the Advanced configuration section, click Change.
Find the
mysql.sql_require_primary_key
parameter.
If you mysql.sql_require_primary_key
is enabled, then your Aiven for MySQL does not allow to create new tables without primary keys. When this mysql.sql_require_primary_key
is enabled, you won’t be able to create tables without primary keys. You will get the following error message:
Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
If creating tables without primary keys is prevented and the table that you’re trying to create is known to be small you may override this setting and create the table anyway.
See also
You can read more about MySQL replication in the Replication Overview article.
You have two options to create the tables:
Setting
sql_require_primary_key
to zero for the current session with the following command:SET SESSION sql_require_primary_key = 0; and then execute the CREATE TABLE or ALTER TABLE statement again in the same session.
Enable
mysql.sql_require_primary_key
parameter. To enable themysql.sql_require_primary_key
parameter, you can follow those steps:Select your Aiven for MySQL service
Go to Overview tab
Scroll down to the Advanced configuration, and click Change
Select the
mysql.sql_require_primary_key
toSynced
.
Warning
It is only recommended to use this approach when the table is created by an external application and using the session variable is not an option. To prevent more problematic tables from being unexpectedly created in the future you should change the setting back to
Not synced
once you finished creating the tables without primary keys.
See also
Learn how to create missing primary keys in your Aiven for MySQL.