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
Last activity: Aug 31, 2023, 06:25 AM