MySQL Bugs: #45714: SOURCE command syntax error using DBI

Bug #45714	SOURCE command syntax error using DBI
Submitted:	24 Jun 2009 15:50	Modified:	24 Jun 2009 17:20
Reporter:	Emma Griffiths	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S2 (Serious)
Version:	4.0011-1	OS:	Linux (Ubuntu 9.04 Desktop)
Assigned to:		CPU Architecture:	Any
Tags:	dbd, dbi, MySQL, SOURCE

Description:
Hi

When using the SOURCE command with DBI I get the error given below.

******
N.B.
******
This only occurs using DBI - if I log into the server remotely on the same machine the code is on and execute the commands:

mysql -hxxx.xxx.xxx.xxx -uroot -p

mysql> create database cbts_WV_79;
mysql> use cbts_WV_79;
mysql> SOURCE ./CONFIG/cbts_WV_tables;

It executes perfectly.

Unfortunately I am finding this completely blocking.

Cheers
Emma

***** Error Message *****
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 ./CONFIG/cbts_WV_tables.sql' at line 1 at ./INCLUDE/split.pl line 221.
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 ./CONFIG/cbts_WV_tables.sql' at line 1 at ./INCLUDE/split.pl line 221.
***** End error message *****

How to repeat:
Versions: 
MYSQL client 5.1.30really5.0.75-0ubuntu10
MYSQL server version 5.0.67-0ubuntu6 (Ubuntu)
libdbi-perl 1.608-1
libdbd-mysql-perl 4.0011-1

****** Extracts from code follow ******

use DBI;

my dbh;

# Prepare connection 

my $connectionInfo="DBI:mysql:database=$db;$MYSQL_HOST:$MYSQL_PORT";
$dbh = DBI->connect($connectionInfo,$MYSQL_ROOT_USER,$MYSQL_ROOT_PASSWORD,
  {  'PrintError' => 1,
     'RaiseError' => 1
});
if (!dbh){
  die (sprintf('[FATAL] could not connect to [%s]: %s\n', $db, $DBI::errstr));
}

# Prepare queries

...

my $query4 = qq[ use $newdb ];
my $sth4 = $dbh->prepare($query4);
if (!sth4){
  die (sprintf('[FATAL] could not prepare statement: %s\n\n ERRSTR: %s\n', $query4, $dbh->errstr()));
}
my $query5 = qq[ SOURCE ./CONFIG/$db\_tables.sql ];
my $sth5 = $dbh->prepare($query5);
if (!$sth5){
  die (sprintf('[FATAL] could not prepare statement: %s\n\n ERRSTR: %s\n', $query5, $dbh->errstr()));
}

...

# Creating new db, granting privs and loading table structure

...

print "Creating table structure for database ".$newdb."\n";
print LOG "Creating table structure for database ".$newdb."\n";

my $rv = $sth4->execute();
if (!$rv){
  die )sprintf('[FATAL] Could not execute statement: %s\n\n ERRSTR: %s\n', $query4, $sth4->errstr());
}
my $rv = $sth5->execute();
if (!$rv){
  die )sprintf('[FATAL] Could not execute statement: %s\n\n ERRSTR: %s\n', $query5, $sth5->errstr());
}

...

********* End code extract ******

I forgot to copy them in, but each $sth is finished!

To my best knowledge SOURCE is not a SQL statement (menas that server does not unerstand it).  It is implemented in command line client only. Not in server and not in most other clients.

Peter
(not a MySQL person)

Thanks for that info, Peter!  I wont hold my breath hoping for a solution then, but it would be great to know a work around (I've had problems trying to get things to execute on the command line properly using perl).
Emma

Sorry, but this is not a bug. SOURCE is mysql command line client's command. See http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html for the details.

If you want to implement it in Perl, you'll have to parse the command, extract filename from it, then open file, parse individual SQL statements/commands there and send them to server one by one. Check mysql command line client source code for some ideas on how to do that.