mysqldump error: The user specified as a definer does not exist

Discussion in 'Tips/Tricks/Mods' started by bern lehn, Oct 11, 2019 at 3:47 PM.

Tags:
  1. bern lehn

    bern lehn New Member

    posting this in tips and tricks since using options for mysqldump seems to get rid of this problem - maybe it should be moved somewhere else?

    hi fellow ispconfig users and dev´s!

    thanks for maintaining such a great product - this can´t be said often enough!


    Problem:
    mysqldump: Got error: 1449: "The user specified as a definer ('c47xxx'@'localhost') does not exist" when using LOCK TABLES

    c47 refers to the client on the first server (source) on the second server (target where site was moved to) we have c5 and web5 as grou / user


    Description:
    I have moved a site + DB from one ISPC Server to a different one (no multiserver setup but two seperate Servers - running Ubuntu 16.04).

    database was moved by creating a dump and restoring it via terminal / commandline.

    when executing mysqldump plain with no options at all on the second server to create a manual backup I got the mentioned error.

    executing mysqldump with options: --single-transaction --quick --skip-extended-insert --skip-comments did not produce the same error (no other actions where taken) and the dump was created just fine.


    I did this because i noticed that only for this site backups where only created for the web folder, but not the database (for all other sites db backup was fine)

    running php /usr/local/ispconfig/server/cron_debug.php --cronjob=500-backup.inc.php showed the same error after it finished.

    reason for the error seems to be that the definer user is somehow still set the same way it was set on the first server for this database (after it was created from the dump that was created on the first server)

    i found this on SO:
    https://stackoverflow.com/questions/26583103/mysqldump-got-error-1449

    please have a look into this, I am not sure what happens if the definer user does exist by chance on a server that a site + database is migrated to (the user would have an association with another DB and this DB, which is probably bad?)
    it seems to me a bit of a strange behavior from mysql that this persists when dumping and restoring, but maybe I am just ignorant to the fact that creating / restoring under different user context needs some more attention to detail.

    this is another occurance of the same problem: https://www.howtoforge.com/community/threads/backup-issue.79098/

    if the correct answer is to make sure that definer user is set correctly after restoring a DB form a dump, that´s ok. I would really appreaciate to have your opinion on this.
     
  2. bern lehn

    bern lehn New Member

Share This Page