Unable to create or change a table without a primary key on MySQL migration

[luisherranz]

Bug Description

Description

After updating n8n to version 1.61.0, the migration "CreateAnnotationTables1724753530828" is failing due to MySQL's sql_require_primary_key setting. This prevents the successful completion of the update process for users with managed MySQL databases (e.g., DigitalOcean) where they cannot directly modify global MySQL settings.

Error Message

Migration "CreateAnnotationTables1724753530828" failed, error: 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.

Environment

• n8n version: 1.61.0
• Database: MySQL (managed service, DigitalOcean)
• sql_require_primary_key setting: Enabled

Possible Solutions

Modify the migration to include primary keys for all new tables.

Additional Context

This issue particularly affects users with managed database services where they cannot easily modify global MySQL settings. It creates a barrier to updating n8n, potentially leaving instances on older versions.

To Reproduce

1. Have a n8n installation using a MySQL database with sql_require_primary_key enabled (default in many managed database services).
2. Attempt to update n8n to version 1.61.0.
3. Observe the migration failure with the error message above.

Expected behavior

Expected Behavior

The migration should complete successfully without requiring users to modify their database settings.

Actual Behavior

The migration fails due to the sql_require_primary_key setting, preventing users from completing the update to v1.61.0.

Operating System

Ubuntu 20.04.5

n8n Version

1.61.0

Node.js Version

v18.20.2

Database

MySQL

Execution mode

main (default)

[Joffcom]

Hey @luisherranz,

We have created an internal ticket to look into this which we will be tracking as "GHC-292"