Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15

Question

I have just updated MariaDB server from 10.6.10 to 10.6.15 on my development machine, and trying to restore a previously working SQL dump now fails.

Here is an example of failing query:

CREATE TABLE `lin_98_41_strumenti_categorie` (
  `id_categoria` 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,
  `id_macchina` int(10) unsigned DEFAULT NULL,
  `id_attrezzo` int(10) unsigned DEFAULT NULL,
  `id_impianto` int(10) unsigned DEFAULT NULL,
  `id_opera_provvisionale` int(10) unsigned DEFAULT NULL,
  `tipo_strumento` char(1) GENERATED ALWAYS AS (concat_ws('',if(`id_macchina` is not null,'M',NULL),if(`id_attrezzo` is not null,'A',NULL),if(`id_impianto` is not null,'I',NULL),if(`id_opera_provvisionale` is not null,'O',NULL))) VIRTUAL,
  PRIMARY KEY (`id_categoria`,`id_azienda`,`id_sede`,`revisione_documento`),
  UNIQUE KEY `unique_id_macchina_lin_98_41_strumenti_categorie` (`id_macchina`,`id_azienda`,`id_sede`,`revisione_documento`),
  UNIQUE KEY `unique_id_attrezzo_lin_98_41_strumenti_categorie` (`id_attrezzo`,`id_azienda`,`id_sede`,`revisione_documento`),
  UNIQUE KEY `unique_id_impianto_lin_98_41_strumenti_categorie` (`id_impianto`,`id_azienda`,`id_sede`,`revisione_documento`),
  UNIQUE KEY `unique_id_opera_provvisionale_lin_98_41_strumenti_categorie` (`id_opera_provvisionale`,`id_azienda`,`id_sede`,`revisione_documento`),
  KEY `fk_main_lin_98_41_strumenti_categorie` (`id_azienda`,`id_sede`,`revisione_documento`),
  CONSTRAINT `fk_id_attrezzo_lin_98_41_strumenti_categorie` FOREIGN KEY (`id_attrezzo`) REFERENCES `agews_attrezzi` (`id_attrezzo`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_id_impianto_lin_98_41_strumenti_categorie` FOREIGN KEY (`id_impianto`) REFERENCES `agews_impianti` (`id_impianto`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_id_macchina_lin_98_41_strumenti_categorie` FOREIGN KEY (`id_macchina`) REFERENCES `agews_macchine` (`id_macchina`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_id_opera_provvisionale_lin_98_41_strumenti_categorie` FOREIGN KEY (`id_opera_provvisionale`) REFERENCES `agews_opere_provvisionali` (`id_opera_provvisionale`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_main_lin_98_41_strumenti_categorie` FOREIGN KEY (`id_azienda`, `id_sede`, `revisione_documento`) REFERENCES `lin_98_41` (`id_azienda`, `id_sede`, `revisione_documento`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

The error is in the last GENERATED ALWAYS field marked as STORED, if I manually change the type to VIRTUAL it works.

The error just says:

ERROR 1901 (HY000) at line 420329: Function or expression 'id_macchina' cannot be used in the GENERATED ALWAYS AS clause of tipo_strumento

but it gives no useful indications as to why now it cannot be used.

However, this SQL dump worked without modifications with version 10.6.10, is it possible there was such a backward-incompatbile change in the latest 10.6 version? I have looked the release notes, but I didn't found anything which should justify suche breaking change.

score 1 · Accepted Answer · answered Aug 31 '23 at 06:25

In the end I found out what happened by creating a ticket on MariaDb's bug tracker, it seems this is intentional:

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.

Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15

1 Answers1