MySql > 8.0.18: Dump/Restore problems with VIEWS containing Group By WITH ROLLUP

Question

I have a simple view using a group by with rollup functionality.

Dumping the database with mysqldump produces a file which can't be restored anymore.

Following SQL creates a database with 1 table and 1 view accessing this table:

DROP DATABASE IF EXISTS rollup_test;
CREATE DATABASE rollup_test;
USE rollup_test;

DROP TABLE IF EXISTS tmp_table_rollup;
CREATE TABLE tmp_table_rollup (
 id bigint(20) NOT NULL AUTO_INCREMENT,
 groupKey varchar(250)  DEFAULT NULL,
 groupValue varchar(250) DEFAULT NULL,
 PRIMARY KEY (id)
);

DROP VIEW IF EXISTS tmp_view_rollup;
CREATE VIEW tmp_view_rollup
  AS
    SELECT groupKey,
    COUNT(id) AS groupCount
    FROM tmp_table_rollup
    GROUP BY groupKey
    WITH ROLLUP;

Dumping from terminal using mysqldump rollup_test > /tmp/rollup_test.mysql produces a file with following fragment:

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=root@localhost SQL SECURITY DEFINER */
/*!50001 VIEW tmp_view_rollup AS select rollup_group_item(tmp_table_rollup.groupKey,0) AS groupKey,rollup_sum_switcher(count(tmp_table_rollup.id)) AS groupCount from tmp_table_rollup group by tmp_table_rollup.groupKey with rollup */;

Trying to restore/import this file with mysql rollup_test < /tmp/rollup_test.mysql (new or old db) leads to following error:

ERROR 1305 (42000) at line 70: FUNCTION rollup_test.rollup_group_item does not exist

Why are there internal c functions rollup_group_item and rollup_sum_switcher?

Any help is very appreciated!

I'm on macOS Catalina with MySql 8.0.22.

(Up to MySql version 8.0.18 I did not experience any problems on that issue.)

Answer 1

Update: It appears that removing these internal c function fixes the view. So as a workaround you could pass your backups through a regular expression replacement like this:

mysqldump ... | sed -E 's/(rollup_group_item\()([^,]*)(,[ ]*[0-9]*\))/\2/g' | sed -E 's/rollup_sum_switcher//g'

That appears to work for me on Ubuntu 20.04, but you may need to adjust the replacements to match your particular view(s) and operating system implementation of sed.

---

This doesn't seem to be an issue with mysqldump but rather with MySQL server itself since you get this same odd syntax if you run a SHOW CREATE VIEW statement or even if you SELECT from INFORMATION_SCHEMA.VIEW .

There is a bug filed here.

UPDATE: The bug will be fixed in 8.0.24.