Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.5, 10.11, 11.4, 11.8, 12.2, 11.8.3
-
None
-
Debian 13.2.0
Description
When database contains views they are dumped by mariadb-dump in the alphabetical order twice.
First time a placeholders with columns of corresponding types are dumped. Second time an actual views definitions are dumped. That is intended to prevent collisions when view definition is dependent on some other view not loaded yet.
A problem: for columns of UUID type placeholders uses INT. If dependent view's name alphabetically is smaller than name of a view it is depend on, then on dependent view's definition load parser throws an error:
ERROR 4078 (HY000) at line 106: Illegal parameter data types uuid and int for operation '='
|
Workarounds:
For small databases mariadb --force dbname < dump.sql could be performed twice in a row. First run will throw an error, the second run will be ok.
For big databases two dumps should be created - one plain dump with
mariadb-dump -ER dbname > dump.sql
|
and another dump with views only
mariadb -BN -e "
|
SELECT TABLE_NAME
|
FROM information_schema.tables
|
WHERE TABLE_SCHEMA = '$SOURCEDB' |
AND TABLE_TYPE = 'VIEW' |
ORDER BY TABLE_NAME ASC " |
|
while read viewname |
do
|
$mariadb-dump --no-create-db --no-data --triggers --comments ${SOURCEDB} ${viewname} >> views.sql
|
done |
Then the common dump should be loaded with `--force` option and then views dump should be loaded.
mariadb --force dbname < dump.sql
|
mariadb --force dbname < views.sql
|
If views have more that one level of dependency then views.dump should be loaded again and again until error disappear.
Attachments
Issue Links
- is caused by
-
MDEV-26447 mysqldump creates temporary MyISAM table for each VIEW
-
- Closed
-
- relates to
-
MDEV-32770 Mariadb-dump improper view creation
-
- Confirmed
-