If you’re getting an error in MySQL that reads something like “Duplicate entry ‘1’ for key ‘person.PRIMARY’” when trying to add a primary key to an existing table, it’s probably because the table already contains duplicate values in the column you’re trying to add the primary key to.

When we add a primary key to an existing table, we need to make sure that the column/s that we’re adding the primary key to contains unique values across all of its rows.

One way to fix this issue is to create a composite primary key (one that’s defined across multiple columns). Another way to fix it is to change the values so that they’re all unique. Another option is to apply the primary key to a different column altogether (i.e. one that contains unique values).

Continue reading →

If you’re getting an error that reads “A table must have at least one visible column” in MySQL, it could be that you’re trying to create a table with nothing but invisible columns. Or you could be altering an existing column to be invisible, but it would leave the table with nothing but invisible columns.

To fix this issue, be sure to have at least one visible column in the table.

Continue reading →

If you’ve been using EXPLAIN ANALYZE to analyse your MySQL queries, you may have encountered a little quirk regarding the output format: When we explicitly use FORMAT=TRADITIONAL in our EXPLAIN ANALYZE statement, we get an error. But when our explain_format system variable is set to TRADITIONAL we don’t get an error.

What’s going on with that? Is this a bug?

First up, no it’s not a bug. It can all be explained by the way EXPLAIN ANALYZE handles our explain_format setting.

Continue reading →

If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format’” in MySQL, it’s probably because you’re trying to run EXPLAIN ANALYZE with the wrong output format.

The EXPLAIN ANALYZE statement only supports the TREE output format. However, there are a few things to be aware of regarding this.

Either way, the quickest way to fix the error is to use FORMAT=TREE in your EXPLAIN ANALYZE statement.

Continue reading →

If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION’” in MySQL, it’s probably because you’re trying to use the FOR CONNECTION option with EXPLAIN ANALYZE, but EXPLAIN ANALYZE doesn’t support the FOR CONNECTION option.

It’s true that we can use the FOR CONNECTION option with the EXPLAIN statement, we can’t use it when using the ANALYZE variant of EXPLAIN (we can use EXPLAIN with or without ANALYZE). At least, this is how it works as of this writing (MySQL 8.3).

To fix, either remove the ANALYZE keyword from the statement or don’t use the FOR CONNECTION option.

Continue reading →

If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.

When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.

To fix, make sure both queries select the same number of columns.

Continue reading →