in reply to Import dump file into mysql DB using DBI

This last part of the error message: 
... near ';
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 S' at line 7 at resultsDBsource.pl line 34, <FH> chunk 1.

[download]
looks sort of like the dump file has been edited in some way to place C-style commenting marks around some lines. I shouldn't be surprised of mysql rejects this (the normal way to comment out a line of SQL is to put two hyphens at the beginning.)

(UPDATE: I should have checked the mysql manual first; C-style comments are allowed, and comments starting with /*! are "special": not ignored, but used to flag statements that use mysql-specific extensions to SQL, and the digits following /*! indicate what (earliest) version of mysql is needed for the extended statement to work. What do you know about the version of the server you are using?)

Rather than reading the whole file as a into a single scalar, you should use the idea mentioned above, setting $/ to the string that marks the end of an sql statement, and read the file one statement at a time, and -- this is important -- on each statement, check for "/*commented strings*/" and remove them before preparing the statement.

If you are trying to load a database on a server where you do not have login shell access, you might want to have mysql running on your own machine, and try loading the dump file locally via a shell command line: 

  mysql test < dump.file

[download]
will load the file contents into the "test" database. Revise the dump file until that operation produces the desired result, then load the dump file on the remote server.

(If you are able to connect to the target database from anywhere via a login-shell "mysql" command, why not just load the dump file that way?)

Replies are listed 'Best First'.
Re^2: Import dump file into mysql DB using DBI
by siva kumar (Pilgrim) on Oct 11, 2007 at 12:46 UTC
    Hi,
    I don't have access to the target DB machine.
    I have to automate the perl script, the script will search for ".sql" files and those files have to updated to DB.
[reply]
      I still prefer to use mysql command line for this task. If you think you have to use a perl sript, then I assume you have remote access to the DB server, in which case you can also use the mysql client from your host.

      SQL files (.sql) are meant to be executed in batch mode, each statement is terminated with a semicolon (";"). With DBI, you execute query one statement at a time and semicolons are disliked :-)

      Finally, if you still need a perl script to automate DB updating (still assuming you the remote access), this might help (sorry, I don't bother to test it). 

      #!/usr/bin/perl
use strict;
use warnings;

my $sql_file = '/path/to/sql/file.sql';
exit 0 # silently
  unless -r $sql_file;

my $user = 'user';
my $pass = 'pass';
my $db = 'somedb';

system "mysql -u$user -p$pass $somedb < $sql_file"
  and die "can't update $db\n";

      [download]
      Also, you may want to consider about DB backup and/or hotcopy, as both facilities are provided by mysql, AFAIK.

      Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

[reply]
[d/l]
      You can always do a system "mysql $connection_data < $sql_file" (assuming that the mysql client is installed on the machine where the script is running)
[reply]
[d/l]

In Section Seekers of Perl Wisdom