Ubuntu 22.04

Is it the exact statement you extracted from the dump line 512844?
What happens if you execute it manually in the client, apart from the dump?

Yes, it is the exact statement; there are also other similar statements (about 7-8) that failed like this.

If I execute it manually on the client after failure, the behaviour is inconsistent: for some tables it still fails, for other it works.

For example, I just tried executing the following statement from the client:

CREATE TABLE `lin_98_68_persone_TEST` (
`id_persona` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_azienda` int(10) unsigned NOT NULL DEFAULT 1,
`id_sede` int(10) unsigned NOT NULL DEFAULT 1,
`revisione_documento` int(10) unsigned NOT NULL DEFAULT 0,
`cognome_nome` varchar(255) DEFAULT NULL,
`mansione_ruolo` text DEFAULT NULL,
`ragione_sociale` varchar(255) DEFAULT NULL,
`tipo_persona` enum('D','F','E') GENERATED ALWAYS AS (if(`id_dipendente` is not null,'D',if(`id_dipendente_fornitore` is not null,'F','E'))) STORED,
`flag_consenso` tinyint(3) unsigned DEFAULT 2,
`data_consenso` date DEFAULT NULL,
`documento_tipo` enum('N','PAT','PAS','CI') DEFAULT 'N',
`documento_codice` varchar(255) DEFAULT NULL,
`documento_emissione` varchar(255) DEFAULT NULL,
`documento_emissione_data` date DEFAULT NULL,
`documento_scadenza_data` date DEFAULT NULL,
`id_allegato` int(10) unsigned DEFAULT NULL,
`id_dipendente` int(10) unsigned DEFAULT NULL,
`id_dipendente_fornitore` int(10) unsigned DEFAULT NULL,
`id_impresa` int(10) unsigned DEFAULT NULL,
`flag_attivo` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id_persona`,`id_azienda`,`id_sede`,`revisione_documento`),
UNIQUE KEY `unique_id_dipendente_lin_98_68_persone` (`id_dipendente`,`id_azienda`,`id_sede`,`revisione_documento`),
UNIQUE KEY `unique_id_dipendente_fornitore_lin_98_68_persone` (`id_dipendente_fornitore`,`id_azienda`,`id_sede`,`revisione_documento`),
KEY `fk_main_lin_98_68_persone` (`id_azienda`,`id_sede`,`revisione_documento`),
KEY `fk_id_dipendente_lin_98_68_persone` (`cognome_nome`,`id_azienda`,`id_sede`,`revisione_documento`,`id_dipendente`),
KEY `fk_id_dipendente_fornitore_lin_98_68_persone` (`cognome_nome`,`id_azienda`,`id_sede`,`revisione_documento`,`id_dipendente_fornitore`,`id_impresa`),
KEY `fk_id_impresa_lin_98_68_persone` (`id_impresa`,`id_azienda`,`id_sede`,`revisione_documento`,`ragione_sociale`),
KEY `fk_id_allegato_lin_98_68_persone` (`id_allegato`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

and it worked, even though the generated column is marked as "STORED" and refers columns later in the definition

I have also noticed that other statements previously working, now fail, for example:

ALTER TABLE lin_98_67_cartelle_cliniche
CHANGE revisione revisione_cartella INT UNSIGNED DEFAULT 0,
CHANGE data_cartella data_creazione DATE DEFAULT NULL,
ADD data_chiusura DATE DEFAULT NULL AFTER data_creazione,
ADD flag_chiusa BOOLEAN AS (data_chiusura IS NOT NULL) PERSISTENT AFTER data_chiusura,
ADD flag_modello BOOLEAN AS (id_dipendente IS NULL) PERSISTENT AFTER flag_chiusa;

This is a statement taken from our software update procedure; when applied on version 10.6.10 it works correctly, now that I have update to 10.6.15 it no longer works unless I change the computed columns from PERSISTENT to VIRTUAL.

While from a functional perspective I think VIRTUAL and STORED are almost equivalent for our use case, from a performance perspective I think it'd be much better to have the computed value stored instead of it being re-computed each and every time.

for some tables it still fails, for other it works

We cannot comment on statements or on tables which we cannot see. So, back to the statement from the description, do you mean it works for you when you execute it manually, but fails when you run it in the dump?
Can you provide the dump which fails, along with your server options, and the console error from the attempt to execute this dump?
If the dump contains confidential data, you can remove it, we should only need the structures; but if you do so, please try to load the dump after you have modified it and copy-paste the error which you are receiving on this already modified dump.

That SQL dump does not contain sensitive data per-se, however it is a backup with the full database structure of our application, and as such I really cannot upload it to a public bug tracker.

Would it be ok if I sent it to you by direct e-mail instead? I would really need this file not to be available on the public web.

You can upload it to the private section of MariaDB FTP server:
https://mariadb.com/kb/en/meta/mariadb-ftp-server/

If you don't feel comfortable to do it however, please don't send it to me by email either, it's the liability I can't take upon myself.

An even better option would be if you could extract a minimal part of the dump which you can share and which still fails when it's executed as a whole. As we apparently established, the single statement doesn't fail, so there must be something in that dump which causes the error, possibly some system variables (mysqldump sets quite a lot of them), either by themselves or in combination with the configuration of your server.

I am uploading a 7z archive on the FTP server which contains:

• the dump which fails
• the "fixed" dump
• my.cnf file
• my development encryption keys

I have just tested the restore again:

root@TMA-21 ~
$ mysql < MDEV-32030/dump_error.sql
ERROR 1901 (HY000) at line 265772: Function or expression 'id_interfaccia' cannot be used in the GENERATED ALWAYS AS clause of `rimuovi_menu`

root@TMA-21 ~
$ mysql < MDEV-32030/dump_fixed.sql
no errors

The upload should be completed in ~10 minutes.

I would like to add, this is the exact version I have installed:

mysql Ver 15.1 Distrib 10.6.15-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

Finally (and sorry for the many comments), I just downgraded to

mysql Ver 15.1 Distrib 10.6.13-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

and now the dump loads correctly with the exact same settings.

Thank you, the dump was very helpful.
The reason of the error is a conflict between foreign keys with CASCADE action defined on the table in question, and the definition of a virtual column. We couldn't reproduce it with the table from the original description, because the table definition there is abridged, in doesn't have foreign keys (in the dump it does).

The change was made in the scope of MDEV-18114 in 10.6.15 (and corresponding other releases in 10.5+). An example of a problematic structure is t2 here:

--source include/have_innodb.inc

CREATE TABLE t1 (a INT, KEY(a)) ENGINE=InnoDB;

CREATE TABLE t2 (b INT, c INT AS(b) STORED, FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE) ENGINE=InnoDB;

# Cleanup

DROP TABLE t2, t1;

Before the change, the structures were allowed in MariaDB, but as MDEV-18114 describes, didn't work correctly.
Apparently the fix made them forbidden (as they were already, for example, in MySQL), but since I can't see clearly from the commit comment that it was intentional, I'll leave it to serg to confirm.

So, the end result is, if I understand correctly, that you cannot have a STORED computed column based on a field part of a foreign key defined with ON UPDATE CASCADE, right?

I guess that, if this is intended, we'd need to re-define all those virtual columns....

Sorry I pasted a wrong sample before, fixed now.

I do suppose that it was intentional, because the assumption is, if a user defines the virtual column and the foreign key this way, they expect them to work properly; so, if it's not possible, then the definition shouldn't be allowed. It is very unfortunate that it breaks the backward compatibility, we of course try to avoid it, but it's still better than to allow it produce erroneous results silently.

If it's due to foreign keys then yes, indeed, it is intentional. The way MariaDB works, and always did, foreign key cascading actions are applied to child tables on a very low level where the server cannot update STORED generated column values anymore. In other words, if a STORED generated column depends on a column that can be modified via a cascade action, this STORED column can become out of sync with other columns. Basically, it'll have an incorrect value. That's why such a table structure is no longer allowed.

We're planning to lift this restriction. MDEV-31942 (or MDEV-22361) when implemented, will allow us to recalculate STORED generated columns on UPDATE CASCADE and SET NULL actions.

Note that ON DELETE CASCADE is fine, it's allowed, because it doesn't modify individual columns, but deletes the complete row.