Skip to content

Create view gives permission errors. Need super priv #240

@welrachid

Description

@welrachid

When importing on a different system the user might not be the same as from the exporting db.
I came across this when building an auto dump - auto import script.

I found this SO solution valuable
https://stackoverflow.com/questions/17600564/create-algorithm-undefined-definer

DROP TABLE IF EXISTS `table_view`;
/*!50001 DROP VIEW IF EXISTS `table_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `table_view` AS select * from table */

The problem here is DEFINER=root@localhost

Which needs to be CURRENT_USER() or else i will have this error:
ERROR 1227 (42000) at line 3: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

For now i've manually set it with a workaround with my own append_extra_sqls dumpsettings ( #239 )

Hope someone can make a change in the code so that it works out of the box.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions