Replication fail with “There is no ‘username’@’host’ registered”

This post doesn’t contain a tip, I’m asking the q and I don’t know the answer yet. Could just be a logic error on my part, in which case it was just a personal mystery and I thank you for helping!

MySQL error 1449 “There is no ‘username’@'host’ registered”

You can get a replication fail with this error if you have triggers, views or stored routines that run as their creator (which is usual) but the slave does not have that user. So far so good.

We just encountered a replication fail on multiple slaves (i.e. not a single isolated mishap) with this error on an UPDATE statement accessing a single base table (i.e. no view), and no triggers defined or routines called in the query. Indeed the slave did not have the reported user (intentionally), but that shouldn’t have mattered. Creating the user allowed replication to continue successfully – that’s no surprise either.

The question is, what condition actually caused this error?

For ref, the original error message; obfucated but structurally unchanged:
[ERROR] Slave: Error ‘There is no ’someuser’@'%’ registered’ on query. Default database: ’somedb’. Query: ‘UPDATE `t1` SET `name` = ’some text string’, `description` = ‘another text string’, `date_taken` = ”, `place_taken` = ”, `modified` = ‘2009-06-24 12:06:16′, `updated_by` = 1, `modified_on` = 1245773176 WHERE `t1`.`id` = 361′, Error_code: 1449

UPDATE (2009-06-26) ok it was an Arjen fail, on a recheck of all the possible situations where permissions are checked (views, stored procs, triggers), I found that there’s 1 trigger in the entire system and it happens to be on this table. So, no bug and my mistake. Issue resolved! Thanks to all those helping!

One Response to “Replication fail with “There is no ‘username’@’host’ registered””

  1. ah yes, but it would be very nice to know that the error came because of a trigger firing, and perhaps the name of the trigger!

    In the future, though, I’ve found that it’s a good idea to query the INFORMATION_SCHEMA before changing users just in case this sort of thing comes up. And if/when this sort of thing does come up, query the INFORMATION_SCHEMA to see what triggers and/or stored routines might be using that username@host.

    If you haven’t already, it would be a good idea to check the list of username@host that are in the tables for triggers and stored routines, so that you can ensure the user isn’t used anywhere else, in a more obscure place.

Leave a Comment