If you’re getting an error that reads “Failed to generate invisible primary key. Column ‘my_row_id’ already exists“, it’s probably because you’re trying to create a table without a primary key, and you’ve named a column my_row_id.

When your system has generated invisible primary keys (GIPKs) enabled, and you create an InnoDB table without explicitly defining a primary key, MySQL automatically creates an invisible column called my_row_id and creates a primary key constraint against it. This is called a generated invisible primary key.

However, if you name one of your columns my_row_id, then MySQL can’t create the GIPK due to the column already existing.

There are several ways to go about fixing this issue.

Example of Error

First, here’s an example of code that produces the error:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    my_row_id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Result:

ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

The reason I got this error is because I have GIPKs enabled on my system and I named a column my_row_id when I tried to create a table without a primary key.

Below are several options for fixing this issue.

Solution 1: Use a Different Column Name

The most obvious solution is to simply use a different column name (one other than my_row_id).

Example:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    person_id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

This time there was no naming conflict and the table was successfully created.

Solution 2: Make my_row_id the Primary Key

Another option is to explicitly set the my_row_id as the primary key:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    my_row_id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

Again the table was created successfully.

The reason this was successful is because MySQL only creates a GIPK/my_row_id column when we don’t explicitly specify a primary key for the table. When we explicitly specify a primary key, MySQL doesn’t even try to create a GIPK/my_row_id column. Therefore there will be no naming conflict (and no error).

Solution 3: Add Another Column as the Primary Key

Another way to deal with the issue is to add a completely new row the primary key.

Example:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    my_row_id INT,
    person_id INT NOT NULL PRIMARY KEY,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

Once again the table was created successfully.

The reason there was no error in this scenario is because I defined a primary key for the table. So it’s a similar scenario to the previous example. The fact that I explicitly created a primary key meant that MySQL didn’t need to create a GIPK. Therefore there was no naming conflict, even though I still defined a column called my_row_id.

Solution 4: Disable GIPKs

If none of the other solutions are suitable, then you might need to think about disabling GIPKs altogether.

To disable GIPKs, set the sql_generate_invisible_primary_key variable to OFF. This is actually the default setting, and so if your system has GIPKs enabled, then there’s probably a reason for that.

In any case, if you determine that you can safely disable GIPKs, run the following code:

SET sql_generate_invisible_primary_key = OFF;

That’s all that’s needed to disable GIPKs.

Now let’s run the original CREATE TABLE statement again:

DROP TABLE IF EXISTS Person;
CREATE TABLE Person
(
    my_row_id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Result:

Query OK, 0 rows affected (0.01 sec)

This time the table was created successfully, even though we didn’t define any primary keys.

But don’t forget that MySQL didn’t create a generated invisible primary key for the table either.