Installation help

Question regarding mysql dump restore (DEFINER)

 
 
Picture of Judy Hsu
Question regarding mysql dump restore (DEFINER)
 

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: -
Picture of Bret Miller
Re: Question regarding mysql dump restore (DEFINER)
Group 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: -
Picture of Judy Hsu
Re: Question regarding mysql dump restore (DEFINER)
 

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: -
Picture of Judy Hsu
Re: Question regarding mysql dump restore (DEFINER)
 

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: -
Picture of Christoph Ruda
Re: Question regarding mysql dump restore (DEFINER)
 

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: -
Picture of Judy Hsu
Re: Question regarding mysql dump restore (DEFINER)
 

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: -