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.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE t1
(
    c1     varchar(20) INVISIBLE,
    c2     varchar(20) INVISIBLE,
    c3     varchar(20) INVISIBLE
);

Result:

ERROR 4028 (HY000): A table must have at least one visible column.

As suspected, we got the error.

As mentioned, we’d also get the same error if we tried to alter an existing table so that all columns were invisible.

Solution

The solution is to make sure at least one column in the table is visible. So using our example from above, we can omit the INVISIBLE keyword from at least one of those columns:

CREATE TABLE t1
(
    c1     varchar(20),
    c2     varchar(20) INVISIBLE,
    c3     varchar(20) INVISIBLE
);

Result:

Query OK, 0 rows affected (0.01 sec)

This time our code worked without error.

We can use the DESCRIBE command to check our table:

DESCRIBE t1;

Result:

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| c1    | varchar(20) | YES  |     | NULL    |           |
| c2    | varchar(20) | YES  |     | NULL    | INVISIBLE |
| c3    | varchar(20) | YES  |     | NULL    | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

We can see that the table has one visible column and two invisible columns. One visible column was all we needed to eliminate the error.