Question regarding mysql dump restore (DEFINER)

Question regarding mysql dump restore (DEFINER)

by Judy Hsu -
Number of replies: 5

Hi there how are you doing?

Thanks for reading this question. We are in the process to migrate a Moodle 1.9.19+ site to restore it on a local Linux (ubuntu) machine for archive purpose. During the mysql dump restore process:

mysql -u root -p[password] archive < moodle_prod.sql

I ran into this error message:

ERROR 1449 (HY000) at line 29294: The user specified as a definer ('admin'@172.11.1.111') does not exist

How should I resolve this problem? I checked with the Moodle migration doc, but it doesn't seem to mention anything about this.

Basically I download the huge .sql (mysql dump) file from our hosting company, then did the "sed -e" thing to replace all the old domain names with the new archive server ip#. I created a new database named it "archive" and granted moodleuser the required rights for the database, but I guess the original DB (when the mysql dump was made) had different DB admin username and IP#. So should I just go ahead to replace ALL the DEFINER='old_admin@172.11.2.222' SQL SECURITY DEFINER' with the new DB admin username (e.g. root, or moodleuser), with the new IP#?? Is that all needed to be done or should I do something different? Thanks!

Judy

Average of ratings: -
In reply to Judy Hsu

Re: Question regarding mysql dump restore (DEFINER)

by Bret Miller -
Picture of Particularly helpful Moodlers

So... no spaces between -u and the username. And, are you sure "root" is a user on your MySQL database? It's needing a MySQL user, not a linux user.

mysql -udbadmin -ppassword archive < moodle_prod.sql

Average of ratings: -
In reply to Bret Miller

Re: Question regarding mysql dump restore (DEFINER)

by Judy Hsu -

Hi  Bret, thanks for helping out.

I think you might have misunderstood my question. I'm familiar with mysql dump command and the command that I ran was correct. However, I have already created a database called "archive" and this DB has its own admin username (root or moodleuser) and password. The mysql dump file that we received from our hosting company, however, has a different set of DB admin username and password. I think basically the ERROR 1449 was complaining about this, but I'm not sure how I should handle this error. Does this make sense? Should I simply remove all the DEFINER references in the mysql dump file (moodle_prod.sql) and change it to the new DB admin username and IP#, or?? Thanks again!

Average of ratings: -
In reply to Judy Hsu

Re: Question regarding mysql dump restore (DEFINER)

by Judy Hsu -

I guess this discussion seems to be related to my scenario, but I haven't figured out exactly what I should do for my case yet:

http://stackoverflow.com/questions/9446783/remove-definer-clause-from-mysql-dumps

Any help would be really appreciated!

Average of ratings: -
In reply to Judy Hsu

Re: Question regarding mysql dump restore (DEFINER)

by Christoph Ruda -

Hi Judy,

I may be barking up the wrong tree because I'm not a MySQL expert but I wonder if the underscore character in your MySQL user name (old_moodle) is giving you trouble. This is something I found out when I migrated our Moodle 1.9 to 2.5 last summer. My problem related to database names that contained underscore characters, therefore I never saw the error you see. After replacing the _ with - everything was fine.

Perhaps this helps in your case too smile

Christoph

Average of ratings: -
In reply to Christoph Ruda

Re: Question regarding mysql dump restore (DEFINER)

by Judy Hsu -

Hi Christoph, I don't think that's the source of the problem. But thanks again for your help.

OK after I did some research, I did a sed find and replace and I think I was able to restore the database correctly (not 100% sure). I was able to login to mysql, select archive (DB), and do a "select * from mdl_user" and see a lot of data in there. So this is good I think.

Now another problem comes up. I then tried to "merge" with the original config.php that came with the hosting company (when we did the rsync), with a generic Moodle 1.9.19+ config.php.

[The OLD config.php came with the rsync]:

<?php

unset($CFG);

$CFG->dbtype = 'mysqli';

$CFG->dbhost = '172.18.0.xxx';

$CFG->dbname = 'moodle_users';

$CFG->dbuser = 'moodle_admin';

$CFG->dbpass = 'CCCCCCCCC';

$CFG->prefix = 'mdl_';

$CFG->dbpersist = false;

$CFG->wwwroot = 'http://xxx.xxx.xxx.net';

$CFG->dirroot = '/home/moodle/public_html';

$CFG->dataroot = '/home/insight2/moodledata';

$CFG->passwordsaltmain = 'bQ3?RzZuxxxxxxxxxxxxxxxx':

...

?>

[The Generic typical config.php after you install Moodle 1.9.19+]

(skipped, very similar to the one below)


[The "merged" config.php]

<?php

unset($CFG);

$CFG = new stdClass();

$CFG->dbhost = 'localhost';

$CFG->dbname = 'archive';

$CFG->dbuser = 'root';

$CFG->dbpass = 'xxxxxxx';   //the DB root password

$CFG->dbpersist = false;

$CFG->prefix = 'mdl_';

$CFG->wwwroot = 'http://xxx.xxx.xxx.xxx/archive';

$CFG->dirroot = '/var/www/archive';

$CFG->dataroot = '/var/moodledata/archive';

$CFG->admin = 'admin';

$CFG->directorypermissions = 00777;

$CFG->passwordsaltmain = 'xxxxxxxxxxxxxxxxxxxxxxxx';

?>


So, when I go into the web browser and type in the new URL http://xxx.xxx.xxx.xxx/archive, it will NOT display anything, and I have don't know how to further troubleshoot. I guess my questions are:

1. For the "passwordsaltmain" should I be using the "old" one that came with the hosting company rsync, or should I comment it out, or even use my own (that is generated when a blank archive DB was created)??

2. I assume that the default "root" user for MySQL would have access to all databases with all the privileges, right? so when I changed the dbname and dbuser and dbpass I should be using the "new" MySQL DB one (and NOT using the old config.php), right?

3. Anything else that I might have done wrong?

Thanks you!

Average of ratings: -