Can not add Foreign Keys in my Migrations "All of a Sudden"

I have been working on a local VirtualBox for my dev environment, and that mirrors my live production server. Both are CentOS 7.2, Apache. I pulled an all nighter last night (finally went to bed at 7:30a today) as I was trying to get Stripe checkout to work and my subscription system/workflow in place. I did. And I figured I'd get 2-3 hours sleep before the family woke up, and then I'd jump back on to re-test my subscription process and payments with stripe, then push to Live and test there. Devestatingly, when I booted up my VirtualBox environment I was greeted with a "Kernel Panic" error and it won't boot....that sucks.

Luckily I had been pushing my code and migrations to my love cde base last night, so I at least have the code I can work through to get the payment process working on my Live and circle back to my VirtualBox woes later.

But when it rains it pours - and when you are sleep deprived you miss things and make mistakes.

For some reason when I try to run my Migrations - that all worked on my dev environment - I can't run any that include Foreign Keys. All my Foreign Keys through an error:

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'name@'localhost' for table site.subscription_types' (SQL: alter table `subscriptions` add constraint `subscriptions_subscription_type_id_foreign` foreign key (`subscription_type_id`) references `subscription_types` (`id`))

It is ONLY migrations that include a foreign key constraint rule.

I first thought that this might be a permissons issue on my Production Database, but it is not (at least from what I can tell: the user has all the same privs they have had when I have made other migrations on the Production server. Then I thought maybe it was a file permission issue, and there was in fact file permission edits I had to make, but that did not fix the error.

Any thoughts why "All of a Sudden" my I am not be able to migrate with foreign keys on production? I have not had this problem before unless there was a actual typo or mistake in the migration, but then that never worked in EITHER environment of course.

I was really excited to finally have my subscription and payment process with Stripe finally 'working', and now I am just demoralized....and very tired.

Scotalia

3 years ago

Best Answer

Level 7

SOLUTION:

Turns out my D user did NOT have permissions set on a the 'subscription_types' or 'subscriptions' tables to set foreign keys.

I have never run into an issue in 13 years where I have had to set user permissions on a table by table basis - I routinely set them on a Database level and that has always worked.

If anyone sees this, can you advise why I may have had to set the permissions on such a granular level in my case and why giving the user Database privileges was not sufficient? In the meant ime I will add to to the list of "Something new I learned today I need to research after 13+ years of doing this" :)

Please or to participate in this conversation.