Issue
Assuming following 2 views
CREATE VIEW v2 AS SELECT 4711 AS XYZ;
CREATE VIEW v1 AS
SELECT
1 AS A
,COUNT(DISTINCT (1)) AS B
FROM v2
GROUP BY A WITH ROLLUP;
Dumping, restoring the dumped file and calling
SHOW CREATE VIEW v1;
leads to following error:
FAILS with ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct count(distinct 4711)) AS `B` from `dbfail`.`v2` group by `A` with rollu' at line 1
In my understanding the dumpfile looks OK. I assume the import/restore can't handle it.
Of course, above example does not make sence in a business manner. It's reduced to the essence of the problem we are facing!
It works fine
- in 8.0.18
- without the ROLLUP modifier
- or without the DISTINCT count
- or when v2 is a TABLE
- OR SURPRISINGLY, and actually the reason for this post ... The failure depends on the names of the views. More precise, their appearance in the dumpfile! If we switch the names of the views v1<->v2 its OK
How to repeat
I'm on macOS Catalina with MySql 8.0.24.
Here's a convenient way of testing the fact in a terminal (on mac). (Feel free to pack it in a script)
The first part is the working one and the second fails due to switched view names.
SCHEMA=dbok
INNERVIEWNAME=v1
OUTERVIEWNAME=v2
mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \
CREATE DATABASE $SCHEMA; \
USE $SCHEMA; \
CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \
CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \
SHOW CREATE VIEW $OUTERVIEWNAME;"
rm -rf ./$SCHEMA.mysql
mysqldump $SCHEMA > ./$SCHEMA.mysql
mysql $SCHEMA < ./$SCHEMA.mysql
mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;"
SCHEMA=dbfail
INNERVIEWNAME=v2
OUTERVIEWNAME=v1
mysql -e "DROP DATABASE IF EXISTS $SCHEMA; \
CREATE DATABASE $SCHEMA; \
USE $SCHEMA; \
CREATE VIEW $INNERVIEWNAME AS SELECT 4711 AS anything; \
CREATE VIEW $OUTERVIEWNAME AS SELECT 4711 AS A, COUNT(DISTINCT(4711)) AS B FROM $INNERVIEWNAME GROUP BY A WITH ROLLUP; \
SHOW CREATE VIEW $OUTERVIEWNAME;"
rm -rf ./$SCHEMA.mysql
mysqldump $SCHEMA > ./$SCHEMA.mysql
mysql $SCHEMA < ./$SCHEMA.mysql
mysql -e "USE $SCHEMA; SHOW CREATE VIEW $OUTERVIEWNAME;"
How can I overcome this problem?
Except for the renaming, of course!. Is anybody else facing this problem too? Is there a proper solution (e.g. dump restore params)? I could not find any comment on this issue yet ...
