Database restore (moving servers)

[fszone]

Hello,

I recently moved servers and I want to take coppermine with me. I have all files and the backed up datavase as a .sql. Is there any easy script that can restore this database for me? I have myphpadmin but when I try to upload it it says:

Error

SQL-query :

--
--

MySQL said:
#1064 - You have an error in your SQL syntax near '--' at line 2

[Joachim Müller]

Then there's something wrong with the dump you created. Post the lines around the line that gives you the error message. Restoring the database on another server using the mySQL dump created with phpMyAdmin is the recommended way, there's no other script dedicated to do this.

Joachim

[itaintrite]

I get the same problem when trying to import the cpg_pictures dump i created using phpmyadmin.

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 '22:39:53, 1110206745, 2, 'tle', 6000, 1, '', '', '', 'YES', '

Here are the codes before and after.

INSERT INTO `cpg132_pictures` (`pid`, `aid`, `filepath`, `filename`, `filesize`, `total_filesize`, `pwidth`, `pheight`, `hits`, `mtime`, `ctime`, `owner_id`, `owner_name`, `pic_rating`, `votes`, `title`, `caption`, `keywords`, `approved`, `user1`, `user2`, `user3`, `user4`, `url_prefix`, `randpos`, `pic_raw_ip`, `pic_hdr_ip`, `lasthit_ip`) VALUES (1, 1, 'userpics/10002/', 'picture1.jpg', 47327, 81831, 732, 548, 63, 2005-03-11 22:39:53, 1110206745, 2, 'tle', 6000, 1, '', '', '', 'YES', '', '', '', '', 0, 1, '68.237.245.51', '68.237.245.51', NULL),
(2, 4, 'userpics/10001/', 'test.JPG', 73339, 142611, 768, 1024, 133, 2005-03-13 06:38:53, 1110208859, 1, 'Itaintrite', 9000, 2, 'Just me', '', '', 'YES', 'nothing...', '', '', '', 0, 1, '151.202.90.15', '151.202.90.15', NULL),

Here's my server info, do you think it might have something to do with it?

Server
Apache/1.3.31 (Unix) DAV/1.0.3 mod_gzip/1.3.26.1a

PHP Version
4.3.10

mySQL
4.0.23a-log

[Joachim Müller]

what is the line

Code Select Expand

(2, 4, 'userpics/10001/', 'test.JPG', 73339, 142611, 768, 1024, 133, 2005-03-13 06:38:53, 1110208859, 1, 'Itaintrite', 9000, 2, 'Just me', '', '', 'YES', 'nothing...', '', '', '', 0, 1, '151.202.90.15', '151.202.90.15', NULL),
meant to do?

Joachim

[itaintrite]

Oh, that's the next row to be inserted into the table.

[kegobeer]

It doesn't work that way.  Each insert must be separate, you can't double up like that.

INSERT INTO table (column1, column2)
VALUES (value1, value2)

INSERT INTO table (column1, column2)
VALUES (value3, value4)

not

INSERT INTO table (column1, column2)
VALUES (value1, value2), (value3, value4)

[itaintrite]

Really? But this is the exact .sql dump I got from phpmyadmin. I have other .sqls and they were all imported successfully, all but this one. cpg132_pictures

[itaintrite]

I think it has something to do with the space between the date and time. Example, 2005-03-13 06:38:53. Cause I just tried inserting just ONE row and it didn't work either. The error was reported to be near the time.

[kegobeer]

What version of phpmyadmin are you using?  I just did a test dump and my timestamp column was formatted correctly ('20050225221506').  Did you apply some sort of template when you did the dump?  And I'm guessing you also did a complete or extended dump to get those multiple inserts.

[itaintrite]

I'm using phpMyAdmin 2.5.2-pl1. And I don't think I used any templates (except for the dump name). And yes, I checked both 'complete inserts' and 'extended inserts.' should I not?

Attached's an image of my phpmyadmin export options. Thanks.

[itaintrite]

Oops, that's the default. When I export my tables, I check all the boxes in the 'SQL options' section.

[kegobeer]

Try exporting with the default values.  Don't save to a file, just copy the output and save it in a .sql file.

You should upgrade to 2.6.1-pl3, if you can.

[itaintrite]

I'm not running my own server so I'll have to email the company. Thanks for the suggestions. I'll check it out now.

edit: same error same place (the time). do you think it's a phpmyadmin prob? If so, is there a way around this. I have ssh telnet access (I think), would that come in handy?

[Joachim Müller]

if you have shell access, you could try this: http://dev.mysql.com/doc/mysql/en/mysqldump.html

Joachim

[jason]

with no shell access but cgi support, use the following (ive used it for databases over 100mb a few times) :

backup (mysqldump)

Code Select Expand

#!/usr/bin/perl
open(STDERR, ">&STDOUT");
$|=1;
print "Content-type: text/html\n\n";
print "<pre>\n";
$t = `date`;
print "$t\n\n";
system("mysqldump -u USERNAME -pPASSWORD --opt DATABSE_NAME > /path/to/database.sql");
$t = `date`;
print "\n\n$t";
print "</pre>";

restore (mysql)

Code Select Expand

#!/usr/bin/perl
open(STDERR, ">&STDOUT");
$|=1;
print "Content-type: text/html\n\n";
print "<pre>\n";
$t = `date`;
print "$t\n\n";
system("mysql -u USERNAME -pPASSWORD DATABASE_NAME < /path/to/database.sql");
$t = `date`;
print "\n\n$t";
print "</pre>";

edit paths and db info for your environment.  place perl scripts inside your cgi-bin directory and call them from your web browser (and let it finish on its own, dont stop it), no need to split or have shell access.  delete perl scripts and database file from your site when you are done.

you could take this further by compressing the file and setup a date archival format that could be utilized from cron jobs.