Error: Invisible Primary Keys Are Enabled

Issue

Syncs associated with MySQL destinations fail. The following error appears:

We couldn't update the primary keys because invisible primary keys are enabled in your destination.

Environment

All MySQL destinations

Resolution

To resolve this issue, you must disable invisible primary key generation by setting sql_generate_invisible_primary_key server system variable to OFF. You can do this at either the database level or the session level.

To disable invisible primary keys for the entire database, run the following MySQL statement:

SET GLOBAL sql_generate_invisible_primary_key = OFF;

If you prefer not to change the global setting, disable invisible primary keys for your current session:

1. Set sql_generate_invisible_primary_key to OFF:

   SET SESSION sql_generate_invisible_primary_key = OFF;
   

2. Drop the existing primary key from the affected table:

   ALTER TABLE . DROP PRIMARY KEY;
   Re-enable the setting for the session if needed:
   SET SESSION sql_generate_invisible_primary_key = ON;
   Cause
   When sql_generate_invisible_primary_key is set to ON, MySQL requires every table to have a primary key and automatically generates an invisible one (my_row_id) if none exists. This prevents Fivetran from dropping and recreating primary keys during syncs, resulting in the above error.
   For more information, see the MySQL Generated Invisible Primary Keys documentation.
   Thanks for your feedback!
   Was this page helpful?
   YesNo
   Submit