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.
- 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:
- ClickHouse and Informix do not support comments on data structures.
The problems with MySQL
As mentioned, MySQL forces us to use
ALTER statements instead.
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
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
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
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
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.
- MDEV-13110 – I added some information to advocate the request.
- Bug #99296 – Reported by me, as I couldn’t fine an existing request for
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.
So… please comment! Your comments help to make Federico-Razzoli.com website more valuable!