Jun 13 2008

MySQL Trigger Woes

After a period of inactivity I was hacking back on a Drupal project, I had taken a mysql dump from a production platform and imported into my local dev setup, just to have some realistic data.

All of a sudden some forms started failing with the following error:

  1. user warning: There is no 'user'@'nonlocalhost registered query: insert into blah (stuff,morestuff) values (x,y) in /var/vhost/drupal-tree/includes/ on line 172.

My Drupal data connection was correct and working for selects etc.. only a limited set of inserts failed.

After some debugging I realised that the error was not Drupal related, running the same query on my MySQL console gave the same error.

  1. ERROR 1449 (HY000): There is no 'user'@'nonlocalhost' registered

The error came from a trigger on the table I was inserting data into that had been created on the production machine by a user@'host' that didn't exist on my development machine. the user was identical but the host wasn't.

MySQLdump includes that information in a dump and uses it to restore the same values.

So recreating the trigger on the development machine solved the problem.
I should probably look a bit closer into the MySQL bugs to figure out if this is a bug or just expected behaviour.

There might even be a parameter to disable this feature , but I didn't find it yet.