MySql 8.0.24:转储/恢复失败，嵌套视图在 DISTINCT 组上具有 ROLLUP [英] MySql 8.0.24: Dump/Restore fails with nested VIEWS having ROLLUP over DISTINCT groups

问题描述

假设有以下 2 个视图

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;

转储，恢复转储的文件并调用显示创建视图 v1;导致以下错误:

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

据我所知，转储文件看起来不错.我认为导入/恢复无法处理它.

当然，上面的例子没有商业意义.归结为我们面临的问题的本质！

• 在 8.0.18 中
• 没有 ROLLUP 修饰符
• 或者没有 DISTINCT 计数
• 或者当 v2 是一个 TABLE 时
• 或者令人惊讶的是，实际上这篇文章的原因......失败取决于视图的名称.更准确地说，是它们在转储文件中的出现！如果我们切换视图的名称 v1<->v2 OK

• 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

我使用的是带有 MySql 8.0.24 的 macOS Catalina.

I'm on macOS Catalina with MySql 8.0.24.

这是一种在终端(在 mac 上)测试事实的便捷方法.(随意打包成脚本)

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 ...

推荐答案

问题已在 8.0.26 中修复.请参阅 MySQL 错误 #103583

Issue is fixed in 8.0.26. See MySQL Bug #103583

这篇关于MySql 8.0.24:转储/恢复失败，嵌套视图在 DISTINCT 组上具有 ROLLUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！