Restore Database With DBI?

[kurt2439]

Hi Monks,

I'm having a problem restoring a database on a remote SQL server with the DBI module. I drop/add my database to clear out the tables but when I try and run this:

my $db_load_file='/home/jchase/testdb.sql';
my $sth=$dbh->prepare("source $db_load_file");
$sth->execute;

I get the following error:

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source /home/jchase/testdb.sql' at line 1 at sync_mumoodle.pl line 31. DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source /home/jchase/testdb.sql' at line 1 at sync_mumoodle.pl line 31.

Not sure why the error gets thrown twice. The db_load_file exists on both the SQL server and the host I am running the perl command from. I wasn't sure how MySQL would interpret the file so I just put it in both places in the same file location to make sure that wasn't the problem.

Does this not work because DBI doesn't really encapsulate all the commands of the mysql command line? That would make sense but then how can I accomplish this without scp'ing the file over and running a system command via ssh?

[Marshall]

This page may be helpful: Using MyQSL in batch mode.

There are arguments to the mysql command line utility like: > mysql -e "source batch_file", where batch_file is a file with SQL batch commands in it. So what you may need to do is something like... system("mysql -e source $db_load_file");?

In other words, there are things that the mysql command line utility can do that are outside of the SQL spec and you might just need to use Perl to execute a shell command rather than using DBI.

Does this not work because DBI doesn't really encapsulate all the commands of the mysql command line? That could be right! If so, just run the shell command from Perl. Once the DB has been created, fire up DBI, connect to it and away you go!

This part: That would make sense but then how can I accomplish this without scp'ing the file over and running a system command via ssh? does confuse me as you say that the file is already in both locations already! But with the mysql command line, you can log-on to a remote host with the -h option and use your local version of "batch_file".

shell> mysql -h host -u user -p < batch_file
Enter password: ********

So I would suggest reading the link I posted above, get what you want to have happen working manually with you typing the shell commands in, then if you can't get Perl to do everything you want in an "automagic" way, check back in and I'm sure you will get lots of help on how to get the last 10% working!

[kurt2439]

Thanks for all the input guys. I ended up running the mysql command on the remote system via the perl system() command as you guys suggested. Getting it all done with native perl code wasn't necessary.

The only trick to getting the system() call to mysql to work as a scheduled task was to use the .my.cnf file in the calling users home directory to specify their sql user, pass and target database without being prompted. Works great!

[Corion]

I would assume that source FILE is not a valid MySQL command.

Why not read each line from the SQL file and $dbh->do(...) it? You might also be interested in DBIx::RunSQL. Personally, I would just run the mysql binary with the source file, either locally or on the server. Maybe you want to talk to your database administrator on how to achieve this?

[kurt2439]

The SQL file is like 6GB, but I guess as long as I go through the file lines with 'while' and not 'foreach' it wouldn't really use up much memory via perl, right?

I could do it with mysql binary, but I need this procedure automated. I guess I can create a mysql profile file for that user so it doesn't prompt for a password. We'll see -- I just wanted to know how to keep it all in perl and it seems like you answered that. Thanks!

[Khen1950fx]

You could run a trace on it to get more info:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( 
          "dbi:AnyData:testdb",
          "username",
          "password",
);

my $db_load_file = '/home/user/testdb.log';
DBI->trace(2);
doPrepare();
exit;

sub doPrepare {
    my $sth;
    $sth = $dbh->prepare( 
    "SELECT source FROM $db_load_file" ), 
    $sth->execute;
}