Bot SQL causes database restore to fail

[cubber]

Please see this thread I created in the linux support section for all the details:

http://www.eqemulator.org/forums/showthread.php?t=30795

I have traced the issue back to the bot tables, but not sure where to go from there with it.

Currently it seems that one cannot use mysqldump to backup a bot enabled server, and expect to be able to restore that backup. It always fails with the following error:

ERROR 1305 (42000) at line 4923: FUNCTION eqtest.GetMobTypeByName does not exist

[cubber]

Steps to take to confirm the issue:

1. create a new db called eqtest
2. source in peq db rev 1265 or any rev for that matter
3. source in load_players.sql
4. source in load_bots.sql
5. mysqldump -uroot -pROOTPASS --opt eqtest > eqtest.sql
6. delete the database eqtest
7. recreate the eqtest database
8. mysql -u root -p -D eqtest < eqtest.sql
9. get error mentioned above

try whole thing over but do not source in load_bots.sql and it will restore fine.

NOTE:

You will need to see this thread in order to source in load_bots.sql in the first step, since the file is wrong and does not work without an edit:

http://www.projecteq.net/phpBB2/viewtopic.php?t=9833

[Congdar]

Your mysqldump command needs the -R switch to pick up the methods.
I think that method is no longer used anyway.
Get the latest sql files as some have been updated.

load_bots.sql should be:

source player_tables/botguildmembers.sql; 
source player_tables/bots.sql; 
source player_tables/botpetstatepersists.sql; 
source player_tables/botinventory.sql; 
source player_tables/botbuffs.sql; 
source player_tables/botadventuring.sql; 
source player_tables/botgroups.sql;

use this:

DROP DATABASE IF EXISTS eqtest;
CREATE DATABASE eqtest;
USE eqtest;
source peqdb_rev1265.sql;
source load_player.sql;
source load_bots.sql;

Then try your test using the -R switch with mysqldump.

[cubber]

That worked perfectly, thanks! Now I just need to fix my other issue over here http://www.eqemulator.org/forums/showthread.php?t=30809 and I will have a working server again!

[Eva Aisling]

Originally Posted by Congdar

Your mysqldump command needs the -R switch to pick up the methods.

Pardon my ignorance, but how do I do this? I am using the most recent sql files and recently had this error on my Windows server. Is there a way to fully restore my database after a failed restore? I have all my characters, but bots work improperly.

[Congdar]

if you didn't use the -R switch then your backup didn't get the methods. If you go back to the original bot sql files you could probably find the commands that create the methods you are missing.

[Grebbin]

I Have a batch file that reads like this timed to run every 24 hours. I also have bots enabled.

@echo off
Echo Please wait while the peq database is backed up as 'L:\peq_backup.sql' ...
mysqldump -uroot -pfiriona peq > l:\peq_backup.sql

You said...

"Your mysqldump command needs the -R switch to pick up the methods."

So what would the full context be? I'd like to have an auto db backup to my memory stick so I can roll back the server in case of emergency. Would it be:

mysqldump -uroot -pfiriona peq > l:\peq_backup.sql -R ???
I'm an SQL NEWB! Thanks!

[lerxst2112]

Try mysqldump -R -uroot -pfiriona peq > l:\peq_backup.sql

[Grebbin]

Thanks! I'll try that!

[initium]

Originally Posted by Grebbin

I Have a batch file that reads like this timed to run every 24 hours. I also have bots enabled.

@echo off
Echo Please wait while the peq database is backed up as 'L:\peq_backup.sql' ...
mysqldump -uroot -p******* peq > l:\peq_backup.sql

You said...

"Your mysqldump command needs the -R switch to pick up the methods."

So what would the full context be? I'd like to have an auto db backup to my memory stick so I can roll back the server in case of emergency. Would it be:

mysqldump -uroot -p****** peq > l:\peq_backup.sql -R ???
I'm an SQL NEWB! Thanks!

It may not matter, but your password is showing. Maybe you already changed it when you posted, but I thought I'd alert you just in case.