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.

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 NULLable to NOT NULLable. 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

• The perils of ALTER TABLE in MySQL/MariaDB

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

Image credit