Error on - Restoring from full mysqldump

[Adam Smith]

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE /usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[David L]

missing quote marks around the path, and missing table name?

Adam Smith wrote:

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE /usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[Adam Smith]

Thanks David

In doing a full mysqldump there are several hundred tables in the file ?

-- Adam --

David L wrote:

missing quote marks around the path, and missing table name?

Adam Smith wrote:

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE
/usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at
line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[Adam Smith]

Hi David,
I should have mentioned that the dump itself contains 5 or 6 distinct
databases and all their tables ==> the full complement being about 250+
-- Adam --

David L wrote:

missing quote marks around the path, and missing table name?

Adam Smith wrote:

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE
/usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at
line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[David L]

just reading up on the syntax again, and it looks to me you can't doing
all in one go. http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

From looking on mysqldump,
http://dev.mysql.com/doc/mysql/en/mysqldump.html,

I think you'd just play back the dump files in the mysql console. (but
i have no experience in doing that)

Have a look at http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html as
another way to do backup (much quicker).
Adam Smith wrote:

Hi David,
I should have mentioned that the dump itself contains 5 or 6 distinct
databases and all their tables ==> the full complement being about 250+
-- Adam --

David L wrote:

missing quote marks around the path, and missing table name?

Adam Smith wrote:

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE
/usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all'
at line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[Adam Smith]

Hi David,

I was just wondering if the problem was more pervasive than I had
thought, so I began looking at the whole sytem setup, which I only just
acquired, I have a new post Subj: MySQL setup, Another twist - To
mysqldump restoration ?, which I just posted. May want to take a look at
it, if you will.

Thanks

-- Adam --

David L wrote:

just reading up on the syntax again, and it looks to me you can't doing
all in one go. http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

From looking on mysqldump,
http://dev.mysql.com/doc/mysql/en/mysqldump.html,

I think you'd just play back the dump files in the mysql console. (but
i have no experience in doing that)

Have a look at http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html as
another way to do backup (much quicker).
Adam Smith wrote:

Hi David,
I should have mentioned that the dump itself contains 5 or 6 distinct
databases and all their tables ==> the full complement being about 250+
-- Adam --

David L wrote:

missing quote marks around the path, and missing table name?

Adam Smith wrote:

Hope this is the right news group!!
I did a server upgrade and at the same time did a mysql update from
4.0.12 to Ver 12.22 Distrib 4.0.16

mach1# mysql --version
mysql Ver 12.22 Distrib 4.0.16, for portbld-freebsd4.9 (i386)

I attempted to restore the DB with

mysql> LOAD DATA INFILE
/usr/home/adam/Temp_storage/mysqldump/dump_all;

and got the following Error message ==>

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all'
at line 1
mysql>

Inspection of the dumped file ==>

head /usr/home/adam/Temp_storage/mysqldump/dump_all
-- MySQL dump 9.07
--
-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

--
-- Current Database: bpndb
--
Could someone advise

Thanks
-- Adam --

[2metre]

"Adam Smith" <ad*******@econ.com> wrote in message
news:nf********************@speakeasy.net...
[SNIP]

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at line 1
mysql> [SNIP] -- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

[SNIP]

I've also had problems doing this.

Solution.... Delete the line of dashes!

Worked for me!

[Jay Donnell]

I always have to change the -- to # to get it to work. Also, I usually do
$ mysql -u me -p tableName < fileName

I've only used LOAD_DATA... for csv type files. This is what has worked for me.

"2metre" <ne**@hersham.net> wrote in message news:<cc**********@sparta.btinternet.com>...

"Adam Smith" <ad*******@econ.com> wrote in message
news:nf********************@speakeasy.net...
[SNIP]

ERROR 1064: 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 '/usr/home/adam/Temp_storage/mysqldump/dump_all' at line 1
mysql>

[SNIP]

-- Host: localhost Database:
---------------------------------------------------------
-- Server version 4.0.12-log

[SNIP]

I've also had problems doing this.

Solution.... Delete the line of dashes!

Worked for me!