mysqlpump like skip-definer option

[matthewlenz]

mysqlpump supports skipping the definer on triggers and routines. This would be really nice to have in mydumper when snapshotting a db to load up for development in a different environment.

[davidducos]

Hi @matthewlenz, I prefer to dump triggers and routines as it was created. During loading we can parse the CREATE statement and make some changes. We are already doing that on myloader when we have to parse the CREATE TABLE statement to remove the indexes to create the ALTER TABLE statement to add them later.

[matthewlenz]

@davidducos I was actually going to suggest that as an alternative in my original post. I think your way is superior without a doubt. Thanks for considering adding this and thanks for all the advancements in the project you've been spearheading!

[druud]

See also #84, nr 6.

[davidducos]

List of objects:

• triggers
• routines
• views

[kamazee]

Is it closed because --skip-definer is implemented in myloader?
I think having --skip-definer in both mydumper and myloader has its benefits: myloader can load a wider variety of dumps this way but throwing the definer away from the dump upon creation makes the dump itself more portable: this way it can be loaded into any database using any tool.

If indeed it only exists in myloader, could you revisit the decision to not add it into mydumper?

[davidducos]

Hi @kamazee,
Yes, it was closed because we (well it was actually just me) decided that it was simpler to implement it in myloader.
I think that you have some good points in your argument. However, I'm wondering in which databases you will not be able to use myloader? Because MyDumper is for MySQL/Percona/MariaDB, it was not designed to work with other engines.
And, if I accept to implement in mydumper for portability with other database, then other user could ask for other changes. I open to hear any feature request, but they will need to be not just implemented, the will need to be maintain.

I based my decision on that I prefer to dump triggers and routines as it was created the only thing that we are changing is the data for data masking, we are not modifying DDL at dump stage and we are leaving the modifications on myloader. I still think that statement as valid, but I'm open to hear other opinions.

[kamazee]

@davidducos ah, my bad, I didn't mean any other databases (please just discard that part and sorry for the confusion). I meant any other tools, like just piping a dump into mysql in another environment that doesn't have all the users from the environment the data was dumped from.

My points for keeping DEFINER out of a dump are the following:

1. This way dump is portable: it can be just used without any further assumptions (e.g. loading with myloader with a certain option).
2. It's easier to diff dumps from different envs.
3. It plays nicer with version control for the same reason: if the schema and data are the same, the dump won't appear changed if it's from another env that has different set of users.

My case, in the essence, is the following. We have a database with application configuration (it's assumed to be fairly small and portable), and due to a view in the schema, I can't just take a dump from production and load it into a preproduction environment, so I ended up grepping out /*!50013 DEFINER=...*/. This DEFINER difference is the only thing I know that breaks portability of dumps, so I thought that discarding DEFINER would be a nice thing to have.

[davidducos]

Aha! Now I understand! You are using MyDumper as version control mechanism. Initially, I didn't understood your 1. and 2. because, I asked myself: "Why does he want multiple dumps? Why don't just use myloader with the right conf per env?". Then, after reading 3. I totally got it.
Honestly, implement skip-definer in mydumper is not going to be hard. However, doing it for the wrong reasons is what I don't like.
Ok... I will create a new ticket for it... meanwhile, promote MyDumper with a t-shirt for you and your friends: https://geekswag.co/collections/mydumper !! 😄