1

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

RST
  • 13
  • 5

2 Answers2

0

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.

Code Commander
  • 16,771
  • 8
  • 64
  • 65
  • Thank you so much @Code Commander. I'm kind of happy that it's a 'real' issue and not just some stupid wrongdoing on my part. Your suggested workaround 'would' work, but I'm looking forward to the reaction of Oracle/MySql. – RST Nov 25 '20 at 07:19
  • It looks like this bug will be fixed in the upcoming [MySQL 8.0.24](https://bugs.mysql.com/bug.php?id=101740). – Code Commander Dec 08 '20 at 16:22
0

8.0.24 indeed fixes above problem ... BUT ... I noticed an other problem regarding ROLLUP nested views ... please check this out ...

MySql 8.0.24: Dump/Restore fails with nested VIEWS having ROLLUP over DISTINCT groups

RST
  • 13
  • 5