CODEWITHSUNDEEP
dockermariadbgenerated-columns
qotsa42
qotsa42

Reputation: 317

Adding Generated Columns Crashes MariaDB

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.

Upvotes: 0

Views: 319

Answers (1)

Tangentially Perpendicular
Tangentially Perpendicular

Reputation: 5357

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.

Upvotes: 2

Related Questions