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/database.mysql.inc 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.

Comments

Anonymous's picture

#1 Anonymous : Thanks for your overview

Just thought I'd leave the obligatory "You're not the only one" post. Same set of problems here in Maryland - need someone on site to manage the database through development, test, and release, work with the regular developers, and also do the production DBA work.


Anonymous's picture

#2 Anonymous : I had the same exact problem

I had the same exact problem except it was a view and not a trigger.


Mikkel Høgh's picture

#3 Mikkel Høgh : Hmm, it might a DNS problem…

That's a real interesting "feature" in MySQL. How did this come about? Incomplete replication?


Kris Buytaert's picture

#4 Kris Buytaert : MySQL Dump

No , no replication .. mysqldump and import again ...