Sample Header Ad - 728x90

Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15

1 vote
1 answer
445 views
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.
Asked by Matteo Tassinari (187 rep)
Aug 28, 2023, 02:17 PM
Last activity: Aug 31, 2023, 06:25 AM