I'm trying to execute a MySQL dump that I created with phpMyAdmin from an existing database. I have the dump in a file called sql.db.

Here's part of the sql.db file so you get an idea of how everything is set up (note: I replaced the --'s with #'s myself to fix one of the errors that was showing up).

# phpMyAdmin SQL Dump
# version 2.6.3-pl1
# http://www.phpmyadmin.net
#
# Host: localhost
# Generation Time: Feb 16, 2006 at 08:05 PM
# Server version: 4.0.25
# PHP Version: 4.3.11
#
# Database: `wwwluna_phpbb`
#

# ############################

#
# Table structure for table `phpbb_auth_access`
#

CREATE TABLE `phpbb_auth_access` (
`group_id` mediumint(8) NOT NULL default '0',
`forum_id` smallint(5) unsigned NOT NULL default '0',
`auth_view` tinyint(1) NOT NULL default '0',
`auth_read` tinyint(1) NOT NULL default '0',
`auth_post` tinyint(1) NOT NULL default '0',
`auth_reply` tinyint(1) NOT NULL default '0',
`auth_edit` tinyint(1) NOT NULL default '0',
`auth_delete` tinyint(1) NOT NULL default '0',
`auth_sticky` tinyint(1) NOT NULL default '0',
`auth_announce` tinyint(1) NOT NULL default '0',
`auth_vote` tinyint(1) NOT NULL default '0',
`auth_pollcreate` tinyint(1) NOT NULL default '0',
`auth_attachments` tinyint(1) NOT NULL default '0',
`auth_mod` tinyint(1) NOT NULL default '0',
KEY `group_id` (`group_id`),
KEY `forum_id` (`forum_id`)
) TYPE=MyISAM;



CREATE TABLE `phpbb_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) NOT NULL default '',
`ban_email` varchar(255) default NULL,
PRIMARY KEY (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `phpbb_banlist`
#


# ############################

#
# Table structure for table `phpbb_categories`
#

CREATE TABLE `phpbb_categories` (
`cat_id` mediumint(8) unsigned NOT NULL auto_increment,
`cat_title` varchar(100) default NULL,
`cat_order` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`cat_id`),
KEY `cat_order` (`cat_order`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

And so on.. the file is about 906 lines long.

So I'm trying to execute that file (to add the data into an existing database) by doing this in PHP:


$query = file_get_contents("db.sql");

if (!mysql_query($query))
die("Error: " . mysql_error());
mysql_close(db);

Unfortunately, the PHP is returning this error:

Error: 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 '; CREATE TABLE `phpbb_banlist` ( `ban_id` mediumint(8)

Please help as soon as you can, thanks.

Have you tried running it in phpMyAdmin for kicks?

I did just now and it worked perfectly in phpMyAdmin.

So does anyone have the solution.. my last post did NOT mean that the problem is fixed, because it isn't.

You cannot do what you're trying to do...

mysql_query takes ONE query. You're passing it the contents of a file--many, many queries. It does not understand this, hence the error.

The easy way would to drop down to the CLI and use mysqldump. Another easy way would be to use phpMyAdmin--you haven't mentioned why you can't just use phpMyAdmin to import it again?

The difficult way would be to write your script such that it will break up the many individual SQL statements in the file into individual ones, and run mysql_query on each.

The easy way would to drop down to the CLI and use mysqldump. What do you mean by this?

The reason I can't just import using phpMyAdmin is that I need this to be automated in a PHP script.

What do you mean by this?
Login via SSH and run:

mysqldump -u db-username -p db-name < db.sql

Oh, nah that wouldn't work because I have to do this from a PHP script, like I said.

Please post your php file here as an attachment
Thanks

Please post your php file here as an attachment
Thanks

Um,
Do you have access to the exec() or system() php command? If so, execute the mysqldump.

If not,
create a script in PHP which breaks each SQL query seperately and feeds it in a loop til all are done.

OK,
so I have too much free time on my hands and feeling generous.... I should charge you for this! lol..

<?php
// db details
$host= 'host';
$user= 'user';
$pass= 'pass';
$db= 'db';
$loc= getenv('DOCUMENT_ROOT');

// feed into db from a raw sql file
system(sprintf(
'mysql -h $host -u $user -p$pass $db > $loc/db.sql'
));
echo '+DONE';
?>

This should work,
I have no idea why it wouldnt work. Unless system() is barred.

If you need it done from a GZip file (thought Id make it, since I'd use it eventually too, can keep it in my PHP util directory)


<?php
// db details
$host= 'host';
$user= 'user';
$pass= 'pass';
$db= 'db';

// unpack that archive and pump it into mysql.
system(sprintf(
'gunzip -c %s/localhost.sql.gz | mysql -h %s -u %s -p%s %s',
getenv('DOCUMENT_ROOT'),
$host,
$user,
$pass,
$db
));
echo '+DONE';
?>


Like my different way of using the variables? eh? lol...

Excellent! Thank you very much Real-Hosts. I'll try this immediately and I'll post again to let you know how it goes.

Real-Hosts
Mind explaining the code in layman terms!
Thanks

I'ts not working.

Here's what I have.. (with my password replaced with PASSWORD obviously)

$result = system(sprintf("mysql -h localhost -u wwwluna_phpbb -p PASSWORD wwwluna_phpbb > $loc/db2.sql"));
if (!$result)
print "error: result = false"; Of course, it's printing out "error: result = false"...
another note: wwwluna_phpbb is the name of both the mysql user AND the database, in case you're confused.

Change that to:
--pPASSWORD (notice, no space) since I just checked, that appears to be the syntax (sorry, my bad, I forgot)

Also,
Is the db2.sql in the same location as the php script your running?
If not, make it:

$loc = "/path/to/db2/without/trailing/slash";

eg:
$loc = "/home/wwwluna/public_html/sql";
so your db2.sql would be located at /home/www/luna/public_html/sql/db2.sql

failing that,
I will recheck the ' > $loc/db2.sql ' as that may be the problem. The MySQL command is sound.. But try the above two fixes out, and let me know. I'll annotate the code since someone requested it :)

Also, why did you not use the PHP script, but with the variables declared above the bulk of the coding? This makes it so much easier, and syntax is preserved perfectly

OK,
Sorry. scrap the old code.
This is the exact coding you are after:


<?php

// db details
// basically you put the details in, which is required to access the db and perform such a function
// the account MUST have privileges either globally, or relating to this db.
$host= 'host';
$user= 'user';
$pass= 'pass';
$db= 'db';
// this is simply to find out the /path/to/ the PHP script being run, and assumes that the sql is in the same directory
$loc= getenv('DOCUMENT_ROOT');

// feed into db from a raw sql file
// Uses the sprintf() embedded within the system() command. Basically executes it like it was running in shell.
// calls the mysql system using syntax avaliable from 'mysql --help'

// I only just noticed how the -p$pass has no space.
// the " > " at the end, means 'put the last into the first' (in this case)
// although, it could be wrong! which is what I've trying to figure out.

system(sprintf(
'mysql -h $host -u $user -p$pass $db < $loc/db.sql'
));

// since it's a nice script, it will output 'done' when its finished. Just to let you know the script isnt hanging
// and that it's actually completed its job.
echo '+DONE';
?>


Ensuring that the sql file is in the same folder as the PHP script being executed.

And the Gzip code:


<?php
// db details
// basically you put the details in, which is required to access the db and perform such a function
// the account MUST have privileges either globally, or relating to this db.
$host= 'host';
$user= 'user';
$pass= 'pass';
$db= 'db';

// unpack that archive and pump it into mysql.
// basically uses the same principles with system() and sprintf() to un-gzip the file
// but not extracting it to a local dir. But in RAM. Pumping it into MYSQL using the MySQL syntax
// this is basically what we would execute if we had SSH.

// This is how I first learnt to do such tiny functions, by having it called using %s
// but having the variables below it. Problem with this is, the variables (in my opinion)
// cannot be complexly defined, as they are proposed in this fashion to be static, not dynamic.
// so it's really to keep those static variables out of your hair in other parts of your coding
// and with this, you have to be careful, you have to put them in as they are used.
// if it was $user $pass $db ... You could not define them below in the order $pass $user $db
// and expect it to work. Just has to be in order of usage. Its quite pointless really
// I never actually code this way anymore and think it's useless, prone to errors, and just looks awful.

system(sprintf(
'gunzip -c %s/localhost.sql.gz | mysql -h %s -u %s -p%s %s',
getenv('DOCUMENT_ROOT'),
$host,
$user,
$pass,
$db
));
echo '+DONE';
?>

Also,
if you ever need to backup your db. This will dump it into gzip.


<?php
$host= 'host';
$user= 'user';
$pass= 'pass';
$db= 'db';
$loc= getenv('DOCUMENT_ROOT');

system(sprintf(
'mysqldump --opt -u$user -p$pass $db > $loc/db.sql'
));
echo '+DONE';
?>


Which is useful for your own backups.
Stick it on cron, download it when you can by HTTP or FTP. Or if you have another server, get it to wget it 5mins later. wa-la.

Still not working. I didn't copy all of your new code; I just looked for changes to make things faster.

Here's what I have now.. and still the same error/problem.
$loc= getenv('DOCUMENT_ROOT');

$result = system(sprintf("mysql -h localhost -u wwwluna_phpbb -pPASSWORD wwwluna_phpbb < $loc/db2.sql"));
if (!$result)
print "error: result = false";

And yep, db2.sql is in the same directory as the PHP script.

I just created admin_blah with a table 'hi' in it.
I dumped the db into a .sql and then delete the 'hi' using phpmyadmin.

I then executed:
mysql -h localhost -u admin_blah -pblah admin_blah < /root/db.sql

The host was 'localhost', the user was 'admin_blah' the password was 'blah' and the db name was 'admin_blah'

And it worked fine.
I have no idea what is wrong... Could you please stop editing the code, run it AS IS. And then check in PHPmyAdmin.

Ok, now I tested it as is and still it doesn't work.

So something's blocking it, if you copy and paste my coding exactly, and then use it. The only problem could be the $loc.

on the original coding:

a) remove the $loc from the command, and just leave it as " < db.sql " and put them in the same dir.

b) make it: $loc = "/path/to/without/trailing/slash";

Failing those two,
I guess system() or sprintf() is forbidden.

Nope, none of that worked either. I messaged my host about it and he's going to talk to the guy higher up than him about it.

OK,
Show him the coding to see whats wrong with it.

Or get him to SSH in and do it for you, using the command I used above, in bold somewhere.

Why don't you code this with error_reporting(E_ALL); so instead of "it doesn't work" you might get an idea because PHP will output a message or two if it failed.

Possible reasons of failure:

* If you are running in "safe_mode" then most likely calls to system() are blocked.

* mysql is not in the path of the Apache user. Always, always, always, give the direct path to any executable if you are using a script to run it. Do not assume that it is in the path, especially if it works from the command line over SSH for your account. You can find the location of mysql by running which mysql from the command line.

You can avoid all this nonesense in a one-shot sweep if you simply use MySQLi and multi_query (http://www.php.net/manual/en/function.mysqli-multi-query.php). Be careful with that though.

This was taken from teh PHP Manual I have not tested either of them but by first glance do not see why they will not work.

here's a script for parsing a *.sql file (tested only on dumps created with phpMyAdmin) which is short and simple (why do people say "here's a short and simple script" and it has a 100 lines?). the script skips comments and allows ; to be present within the querys

<?php
function parse_mysql_dump($url){
$file_content = file($url);
$query = "";
foreach($file_content as $sql_line){
if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
$query .= $sql_line;
if(preg_match("/;[\040]*\$/", $sql_line)){
$result = mysql_query($query)or die(mysql_error());
$query = "";
}
}
}
}
?>
</SPAN>

A modified version of the above:

This SQL dump parser fixes the check for comments that was present in the old (ie. a '--' located anywhere in the string would make it ignore that line!), and adds the check for the # comment. That had me thinking.

<?php
function parse_mysql_dump($url, $ignoreerrors = false) {
$file_content = file($url);
$query = "";
foreach($file_content as $sql_line) {
$tsl = trim($sql_line);
if (($sql != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
$query .= $sql_line;
if(preg_match("/;\s*$/", $sql_line)) {
$result = mysql_query($query);
if (!$result && !$ignoreerrors) die(mysql_error());
$query = "";
}
}
}
}
?>


If you download the php manual with notes you can find most of the answers to common problems.

Hope this helps.

Regards,
Ray

Thanks, Idealws.com! That solves the problem. The only problem I have now is that phpMyAdmin has formatted the db.sql file to have a bunch of linebreaks and spaces inside of "create table" commands, making them on separate lines. This is making the script give some errors. But I'm working on removing the lines manually.. this is taking a long time with 900+ lines.

You know, I believe you'd have saved a lot of time if you had access to mysqldump and the CLI, rather than trying to think of hackish ways to get this done in PHP.

Prediction: once you get this PHP script working, you are now going to begin hitting PHP's resource limits on script runtime and memory usage, and are going to have to start splitting SQL files...

Tamasrepus...
Yes, I'm not an idiot. I know that. Infact, that's why we all asked clearly whether he could get SSH and whether his host could help out...

It's not hackish ways.
It's simply utilising the PHP system() and sprintf() commands. I'm sorry that you didn't try to help, but turn up and criticise? I was only trying to help him find a solution.

I have managed to use the same principal (system and sprintf in a script, with mysqldump and mysql commands) for a 400mb IPB database...

Oh yes,
but thanks for putting help into the thread. Nicely done.

But I'm working on removing the lines manually.. this is taking a long time with 900+ lines.

Say hello to trim() (http://php.net/trim).