bench restore: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

[zongo811]

Issue: Bug report

Please make sure your issue is reproducible on the latest bench version. The currently supported branches are:

• PyPI latest
• master (minor bug fixes)
• v5.x (Merged with develop on every release)
• develop (all updates)

Do the checklist before filing an issue:

• Can you replicate the issue on the supported bench versions?
• Is this something you can debug and fix? Send a pull request! Bug fixes and documentation fixes are welcome

Describe the bug 📉
We use stored functions and views in our database. When we do "bench backup" and "bench restore" the stored functions and dependent views are not recreated, and this error occurs:
Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

Workaround:
Stored functions and views can be recreated manually by running a sql-script.

To Reproduce 📃
Steps to reproduce the behavior:

1. Run sql-script to create stored function and view:

DELIMITER $$
CREATE FUNCTION double_me(p_value INT)
RETURNS INT DETERMINISTIC
BEGIN
	return p_value * 2;
END $$
DELIMITER ;

CREATE OR REPLACE
VIEW `View_double_me` AS
select
    `double_me`(123) AS `double_me(123)`;

2. bench backup
3. bench restore ...
4. See error

Expected behavior 📈
bench restore runs without error, stored function and view are created by bench restore .

Screenshots 🔮
If applicable, add screenshots to help explain your problem.

OS (please complete the following information): 🌀

• Linux: debian:buster
• macOS: version
• Windows version
• Others? haros:distro:version

Version Information

Can be found out by running bench version in your respective bench folder.

• Bench Branch: 5.6.0
• Frappe Version: 13.14.0
• ERPNext Version: 13.14.0

Additional context 📄
Add any other context about the problem here.

Possible Solution 📑
Any idea what might be causing the issue. Or if you have a proposed solution to the problem.

[zongo811]

The failing SQL-statement is CREATE VIEW:

Also a CREATE FUNCTION for the referenced function "json_field_value" is not included in the unpacked sql-script.
"json_field_value" is a function created by me to be used in views.

[zongo811]

Got it. The target DB has a different name than the source DB, hence the DEFINER points to a nonexistent user which leads to the error.
Copying to another DB is not the same as restoring a backup :)

But: can you do something about including the stored functions?

[gavindsouza]

But: can you do something about including the stored functions?

You would have to raise a feature request on frappe/frappe since backup is managed there. However, from what I read in the mariadb docs, it's possible.

mysqldump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). Procedures and functions are however also part of the system tables (for example mysql.proc).

The only bit is that it's going to be time-consuming (and probably require a lot of RnD) for someone (like me) who hasn't used these before to get this done. Seems like you have some experience here though, if you can provide the mysqldump command you'd use to make the backup, we could get a starting point for this.

---

Closing this since it's not covered in this project. Please continue this discussion on an issue thread on frappe/frappe or feel free to raise a PR there for the same.