Description:
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.
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:
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;"
Description: 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. 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: 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;"