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

hair_trigger creates adapter specific code for MySQL #76

Open
erikaxel opened this issue Aug 15, 2018 · 1 comment
Open

hair_trigger creates adapter specific code for MySQL #76

erikaxel opened this issue Aug 15, 2018 · 1 comment

Comments

@erikaxel
Copy link
Contributor

Hi,

First of, thank you for creating such an excellent gem!

We have some issues using MySQL 5.7 and this gem. Unfortunately it creates adapter specific code for our triggers, and since MySQL generates code that only works locally, that is an issue.
The trigger is the something like the following:

  trigger.before(:insert) do
    'SET NEW.value = SELECT max(something) FROM some_table;'
  end

It then generates a migration without issues, however schema.rb is created with an adapter specific code:

  execute(<<-TRIGGERSQL)
CREATE DEFINER = 'db'@'localhost' TRIGGER trigger_name BEFORE INSERT ON `some_table`
FOR EACH ROW
BEGIN
    SET NEW.value = SELECT max(something) FROM some_table;
END
  TRIGGERSQL

The issue for us is CREATE DEFINER = 'db'@'localhost' which can differ from developer to developer and from different prod environments.

I can think of two things causing this: Either a) the CREATE DEFINER part from MySQL, or the b)
'SET NEW.value = SELECT max(something) FROM some_table;' part.

If a) it would be great if it could ignore it and just create a standard trigger call. (which it managed to do in the migration and which works beautifully).
If b) It would be great if it can generate code without the CREATE DEFINER part which I don't think is necessary.

We'll be happy to help out resolving it, but it would be great with some thoughts & pointers to the right place.

@jenseng
Copy link
Owner

jenseng commented Feb 8, 2019

Hi @erikaxel thanks for the detailed bug report! Does this happen for all triggers or just some?

It seems likely that it's related to the different db users depending on the environment. If this is still an issue for you and you don't mind doing a little debugging, I think we could track it down with a breakpoint on this line ... basically, HairTrigger checks that the trigger in the database is an exact match of what the migrations say it should be; if it's not you get the execute(... statement.

So if we can compare the two strings (db_triggers[name] vs definition), that would pinpoint the root cause.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants