I followed a webinar by Piotr Kononow, from Dataedo. The company produces a tool to comment databases. Piotr was asked if descriptions written in the tool become comments in the database, and his answer was enlightening: yes, but not with MySQL.
Why? Because with MySQL to change a comment you have to use ALTER TABLE
, and they don’t want to do that. Piotr didn’t provide more details, but as a database consultant I can say that they are absolutely right. Adding or modifying a comment in MySQL/MariaDB is risky, and in this article I’m going to explain the reasons.

It’d be sad if we lost all memory of what it is, right?
The same applies to your tables. Use comments.
How other DBMSs deal with comments
Other DBMSs support the COMMENT ON
statement to set comments on any data structure:
COMMENT ON SCHEMA dwh IS 'Empty star schema template';
This is true at least for PostgreSQL, Firebird, Db2 and SQL Server. Most of these databases allow to use NULL
instead of a string to remove a comment. They allow comments on different sets of data structures; for example, only PostgreSQL currently allows to comment schemas.
Exceptions:
- SQLite allows to enter comments in
CREATE TABLE
. These comments are preserved and can be seen later. Not a good solution for a DBMS, but SQLite is a library. - SQL Server provides a system stored procedure instead:
sp_addextendedproperty()
. - ClickHouse and Informix do not support comments on data structures.
The problems with MySQL
As mentioned, MySQL forces us to use ALTER
statements instead.
Permissions
To run an ALTER TABLE
statement, one needs the ALTER
privilege. This privilege allows to make any change to a table structure, including destructive changes (DROP COLUMN
), changes that may break existing applications, and changes that may break replication.
Similarly, to set a comment for a stored procedure or a stored function, one needs the ALTER ROUTINE
privilege. However, this allows to change the routine security mode from DEFINER
to INVOKER
, or the other way around. This may allow the routine to do something it shouldn’t be able to do, or may prevent it to do something it should do, breaking it.
In the case of Dataedo or similar tools, only the permissions to inspect data structures and comment them should be granted.
Comments on columns
Using ALTER TABLE
to change a table comment is easy:
ALTER TABLE supplier COMMENT 'Includes past suppliers';
But what if we want to set a comment for an existing column?
ALTER TABLE supplier
MODIFY dir VARCHAR(50)
CHARACTER SET ascii COLLATE ascii_bin
NOT NULL DEFAULT 'catchall' CHECK (dir > '')
COMMENT 'Directory containing invoices';
Can you see the problem? To set a comment, we need to repeat the whole column definition. And it’s very risky. Humans can easily make mistakes, programs can have bugs.
Comments on indexes
In the case of indexes, it’s even worse:
ALTER TABLE supplier
DROP INDEX idx_advanced_search,
ADD INDEX idx_advanced_search (dir)
COMMENT 'Used by the old page "advanced search", drop when the page is discontinued';
Or, with MariaDB:
ALTER TABLE supplier
ADD OR REPLACE INDEX idx_advanced_search (dir)
COMMENT 'Used by the old page "advanced search", drop when the page is discontinued';
There are two problems here:
- To set an index comment, it’s necessary to drop and recreate an index. This can be a huge amount of work, depending on the table size.
- Just like setting column comments, setting index comments is an error-prone operation that could lead to create non-optimal indexes.
NULL and NOT NULL
It is worth noting that we also need to repeat a column definition if we only want to change it from NULL
able to NOT NULL
able. Most other databases provide a way to do it more easily. For example, in PostgreSQL:
ALTER TABLE supplier ALTER COLUMN dir SET NOT NULL;
MySQL provides similar “shortcuts” to rename a column/index, to change or drop DEFAULT
values, and to make an index VISIBLE
or INVISIBLE
. MariaDB allows shortcuts to rename a column/index (but only since version 10.5) and change or drop DEFAULT
values, while invisible indexes are not supported.
See also
Related articles
Reference
- ALTER TABLE Statement, from MySQL documentation
- ALTER TABLE, from MariaDB documentation
Feature requests
- MDEV-13110 – I added some information to advocate the request.
- Bug #99296 – Reported by me, as I couldn’t fine an existing request for
COMMENT ON
.
Conclusions
Because MySQL and MariaDB support comments on data structures, the COMMENT ON
syntax may look to them like a checklist feature. A checklist feature (an expression invented by someone else, but I can’t remember who) is a feature that does not solve a real problem, but it’s just written on some internal checklist.
That is not the case. As I showed above, there are great reasons to implement COMMENT ON
. Its absence makes comments unsafe and often undesirable from a user point of view.
I fixed a problem that prevented people from commenting, which was caused by a WordPress plugin called Autoptimize. It seems to do something wrong when it optimises JavaScript code.
So… please comment! Your comments help to make Federico-Razzoli.com website more valuable!
Toodle pip,
Federico Razzoli
Very good points and well put, Federico! I couldn’t agree more. Comments on the schema objects are so important, and it’s the only sensible place for schema documentation. Comments should be encouraged, but the problems you have highlighted obviously have the opposite effect.
For those who follow the MySQL and MariaDB issue trackers, please consider upvoting these tickets!
I totally agree, and thanks for the upvoting request!
This MySQL/MariaDB flaw put me in a funny situation in the past. I used to write a lot of comments to pull reviews, like “consider adding a comment to this table and some its columns, unless you think that their meaning is obvious”. But then I also told people, “please don’t add comments to columns that are already in use in production unless their meaning is obscure”.