Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

foreign key implementation #507

Open
pedroalb opened this issue Oct 23, 2017 · 10 comments

Comments

Projects
None yet
4 participants
@pedroalb
Copy link

commented Oct 23, 2017

Hi,

I spoke with @shlomi-noach and @jonahberquist during last Percona Europe in Dublin regarding the possibility of this amazing tool start supporting foreign keys. We are facing the same issues that you faced before with pt-online-schema-change but in our case we have foreign keys referenced to our most busiest tables.
I thought of the following implementation to support foreign keys:

  1. At the time of swap, create one session to the master (if applying changes on master) or slave (if testing in a slave)
  2. SET FOREIGN_KEY_CHECKS = 0;
  3. On same session as point 2, generate new foreign keys to point to ghost table. (we don't care about keeping the same name on constraints)
  4. Swap

What do you think of this idea? It would be great if you could give me your feedback on this. If you allow, I'd also come up with a PR with this implementation.

Thanks,
Pedro.

@zmoazeni

This comment has been minimized.

Copy link
Contributor

commented Oct 23, 2017

I've been thinking of sharing our FK process. This is a good prompt.

My team still uses a handful of them but we're not consistent and we haven't made the hard decision to go one way or another. So we're kind of in limbo as far as using FKs in production. Based on my experience dealing with db issues I see both sides of the issue especially what @shlomi-noach iterated in #331.

What we have done is this:

  1. Drop the FKs for the specific tables we're migrating before using gh-ost. This is almost instanteous.
  2. Run gh-ost process.
  3. Set SET FOREIGN_KEY_CHECKS = 0; and re-add FKs. With that flag, re-adding the FKs are very quick because it doesn't have to evaluate if there are any violations.
  4. Run manual queries to make sure we don't have any FK violations.

It's a pragmatic strategy but it does have a flaw in that we open a window where we are FK-less and we could introduce data issues that we would have to deal with after Step 3. We haven't gotten caught with data issues in step 4 but it's assume we would handle the affected rows manually at that point. (We're not running mission critical data like a financial institution where we couldn't deal with a manual violation right now).

I don't know if that could help inform the decision/path forward with gh-ost in regards to FKs.

@pedroalb

This comment has been minimized.

Copy link
Author

commented Oct 23, 2017

Hi @zmoazeni , thanks for sharing this.

We actually want to have that window as short as possible (or even no window at all) and that's why I thought adding the FKs at the time of swap with SET FOREIGN_KEY_CHECKS = 0 to make the FKs creation almost instantaneous.

@shlomi-noach

This comment has been minimized.

Copy link
Collaborator

commented Oct 24, 2017

On same session as point 2, generate new foreign keys to point to ghost table.

Isn't this a full blown blocking ALTER TABLE?

@pedroalb

This comment has been minimized.

Copy link
Author

commented Oct 24, 2017

Hi @shlomi-noach , you're right.. it is a full blown blocking.
could you share your thoughts on how this foreign key implementation could be approached if you have thought about this?
thanks!

@shlomi-noach

This comment has been minimized.

Copy link
Collaborator

commented Oct 24, 2017

It's been a while since I last added a foreign key on a table, so maybe as @zmoazeni , with foreign_key_checks=0 this is an immediate action? At least maybe for 5.6 and above.

If it's not an immediate action, then the way would be:

  • verify the table is not in a parent-side foreign key relationship.
  • create the ghost table with the foreign key, pick a name that won't conflict.
  • make sure any write to the ghost table is protected by foreign_key_checks=0.
  • pizza
@pedroalb

This comment has been minimized.

Copy link
Author

commented Oct 24, 2017

thanks @shlomi-noach for the update. The SET FOREIGN_KEY_CHECKS = 0; from @zmoazeni doesn't avoid the need of an alter table when he's re-adding the FKs after this. This was mentioned on step 2+3 of my plan.
Regarding your idea, that means I'd have to run the same process for all the tables that reference the parent-side table, and that I would have to go through the chain until I get to the tables that are not in a parent-side foreign key relationship.
Pizza and no FKs for me too..

@shlomi-noach

This comment has been minimized.

Copy link
Collaborator

commented Oct 24, 2017

Oh I meant parent side are completely no go. The steps I illustrated were for child side.

@pedroalb

This comment has been minimized.

Copy link
Author

commented Oct 24, 2017

I understood that but in my case is a completely no go because we have those chains in the model :(
thanks again @shlomi-noach and @zmoazeni for your thoughts and inputs!

@ChristopherSchultz

This comment has been minimized.

Copy link

commented Aug 30, 2018

Support for child-only FKs would be great, since it's actually doable. Not everyone runs without FKs :)

@shlomi-noach

This comment has been minimized.

Copy link
Collaborator

commented Aug 30, 2018

@ChristopherSchultz it would, indeed. We don't have the spare cycles for this. If anyone is interested in submitting a PR, we're grateful.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.