Adding Generated Columns Crashes MariaDB

Question

I am experiencing a strange bug with generated columns and MariaDB running in a Docker container.

The image I'm using is mariadb:10.

I have been trying to add generated columns. The first column I add works fine; the second I add crashes the container and destroys the table.

Here's the first column that is working:

ALTER TABLE program
    ADD is_current tinyint AS (
        IF (
            status IN ('active', 'suspended')
            AND start_date >= NOW(),
            1,
            0
        )
    );

This one works just fine. The following SQL crashes the container:

ALTER TABLE program
    ADD is_covered tinyint AS (
        IF (
            status IN ('active', 'suspended')
            AND start_date <= NOW(),
            1,
            0
        )
    );

After restarting the container, I get the following errors:

SELECT * FROM program;

[42S02][1932] Table 'my_local.program' is marked as crashed and should be repaired

repair table my_local.program;

Table 'my_local.program' doesn't exist in engine / operation failed

Following the directions from this question I checked in the container for the existence of the ibdata1 file. It exists, as do the table's .ibd and .rfm files.

I have not been able to fix this; I had to drop the table and re-create it and re-import the data.

If anyone has any suggestions, I'd love to hear.

From the [mariadb documentation](https://mariadb.com/kb/en/generated-columns/): "*The `ALTER TABLE` statement has limited support for generated columns. ... - It does not support adding a `VIRTUAL` generated column with the `ADD` clause if the same statement is also adding other columns if `ALGORITHM` is not set to `COPY`. See [MDEV-17468](https://jira.mariadb.org/browse/MDEV-17468) for more information.*" - Could this be related? — Turing85, Aug 17 '21 at 00:24
Edited to change MySQL to MariaDB. MariaDB forked from MySQL in 2010, and it has been implementing its own code since then. You should consider MariaDB to be a distinct database product from MySQL. There are more and more incompatibilities between these two products introduced in each release. — Bill Karwin, Aug 17 '21 at 01:28
@Barmar yes, there is actually some more logic I'd like to include but I didn't get that far before the table crashed... — qotsa42, Aug 17 '21 at 04:43

score 2 · Answer 1 · answered Aug 17 '21 at 00:29

Checking the reference for MySQL 8 for generated columns I find that

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

This is also true of MySQL 5.7.

When I attempted to create your generated column with MySQL 8 I got this message:

Error Code: 3763. Expression of generated column 'is_covered' contains a disallowed function: now.

I note, however, that you are using mariadb:10. Although it is derived from MySQL, MariaDB is now effectively a different product.

The MariaDB reference for generated columns says: (for 10.2.1 onwards):

Non-deterministic built-in functions are supported in expressions for not indexed VIRTUAL generated columns. 

Non-deterministic built-in functions are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.

So, If you have MySQL you can't do this at all. If you have MariaDB 10.2.1+ you should be able to do it with certain limitations.

In any case, you should get an error message, not a crashed table. I suggest you check the MariaDB bug reports, and submit one if this is not already there.

Adding Generated Columns Crashes MariaDB

1 Answers1