Follow foreign key constraints

[sebo]

Like you descriped in the section "Also, when you need to analyze problems with data from your production databases, you might want to pull only relevant parts of data and also hide personal data (user names, for example)." the problem often is related to one row which has multiple foreign key constraints which are related to other rows in other tables. Is that already possible with slimdump or do I have to right filter sql statements for every table?

[mpdude]

No, we don't currently have any support for that.

How exactly do you think could this work? Dump a table and all referenced rows in other tables, discovering FK relationships automatically?

What if there are other configuration statements governing these tables?

[sebo]

Yes, thats how it could work. Probably need to have a loop detection.

"What if there are other configuration statements governing these tables?"
What do you mean with that?

[MalteWunsch]

Interesting idea. The FK relationships are stored in the information_schema, easily queryable.

What about adapting the constraint on the master table to the slave table? Generally speaking, the constraint itself is no use for us. Just in the special case where it only contains the referenced primary key (e.g. "id mod 2 == 0") it could be adapted for the slave table constraint directly ("foreign_key mod 2 == 0"), with some parsing and field name lookup in between. More general constraints like "username like 'a%'" cannot be adapted directly.

My feeling is that we need MySQL to dump the master table first and add something like "AND foreign_id IN (SELECT id FROM master)" to the constraint of the slave table. This would introduce an order to the table dumping. I don't know how much of an architectural change this would be.

[digilist]

Hey, I just discovered your project. It's interesting, I got the same idea several years ago: https://github.com/digilist/SnakeDumper

I solved the foreign key problem by collecting all primary keys of dumped rows and then by selecting only those rows which have a "valid" foreign key (I think you could also do this with sub queries, but I did not want to have n nested subqueries - this might take a long time, e.g. if there are filters on non-indexed columns). Each table that is referenced by another table needs to be dumped before that other table. Therefore, my SnakeDumper is not able to handle loops in the foreign key relations and I am not yet sure how to solve this (and you know, there are so many other things to do... ;))