Description:
I used mysqldump to create an SQL file containing a dump of all databases on
the server, including all tables and data (using --all-databases). However,
when I tried to restore from this file, the mysql command line tool reported
errors. I guessed that this was due to the --extended-insert option (I was
using --opt); when I turned this off (but kept all of the other --opt
options) the errors disappeared.
How-To-Repeat:
I used mysqldump to create a dump of all databases on the server, as
follows:
mysqldump --host=host --user=user --password=pwd --all-databases
--opt --result-file=backup.sql
However, the problem can be duplicated by running the following SQL alone
against a MySQL database using the mysql command line tool:
****************************************************************
-- MySQL dump 8.21
--
-- Host: pathia.bham.ac.uk Database: test
---------------------------------------------------------
-- Server version 3.23.49-max
--
-- Current Database: test
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ test;
USE test;
--
-- Table structure for table 'up_layout_struct'
--
DROP TABLE IF EXISTS up_layout_struct;
CREATE TABLE up_layout_struct (
USER_ID int(11) NOT NULL default '0',
LAYOUT_ID int(11) NOT NULL default '0',
STRUCT_ID int(11) NOT NULL default '0',
NEXT_STRUCT_ID int(11) default NULL,
CHLD_STRUCT_ID int(11) default NULL,
EXTERNAL_ID int(11) default NULL,
CHAN_ID int(11) default NULL,
NAME varchar(35) default NULL,
TYPE varchar(35) default NULL,
HIDDEN char(1) default NULL,
IMMUTABLE char(1) default NULL,
UNREMOVABLE char(1) default NULL,
PRIMARY KEY (LAYOUT_ID,USER_ID,STRUCT_ID)
) TYPE=MyISAM;
/*!40000 ALTER TABLE up_layout_struct DISABLE KEYS */;
--
-- Dumping data for table 'up_layout_struct'
--
LOCK TABLES up_layout_struct WRITE;
INSERT INTO up_layout_struct VALUES (1,1,1,4,2,NULL,NULL,'Header
folder','header',NULL,'Y','Y'),(1,1,2,3,NULL,NULL,10,NULL,NULL,NULL,NULL,NUL
L),(1,1,3,NULL,NULL,NULL,99,NULL,NULL,NULL,NULL,NULL),(1,1,4,9,5,NULL,NULL,'
Main',NULL,NULL,'Y','Y'),(1,1,5,7,6,NULL,NULL,'Column
1',NULL,NULL,NULL,NULL),(1,1,6,NULL,NULL,NULL,5,NULL,NULL,NULL,NULL,'N'),(1,
1,7,NULL,8,NULL,NULL,'Column
2',NULL,NULL,NULL,NULL),(1,1,8,NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,'N'),(1,
1,9,14,10,NULL,NULL,'Misc',NULL,NULL,'Y','Y'),(1,1,10,12,11,NULL,NULL,'Colum
n
1',NULL,NULL,NULL,NULL),(1,1,11,NULL,NULL,NULL,9,NULL,NULL,NULL,NULL,'N'),(1
,1,12,NULL,13,NULL,NULL,'Column
2',NULL,NULL,NULL,NULL),(1,1,13,NULL,NULL,NULL,11,NULL,NULL,NULL,NULL,'N'),(
1,1,14,22,15,NULL,NULL,'News',NULL,NULL,'Y','Y'),(1,1,15,17,16,NULL,NULL,'Co
lumn
1',NULL,NULL,NULL,NULL),(1,1,16,NULL,NULL,NULL,12,NULL,NULL,NULL,NULL,'N'),(
1,1,17,NULL,18,NULL,NULL,'Column
2',NULL,NULL,NULL,'N'),(1,1,18,19,NULL,NULL,6,NULL,NULL,NULL,NULL,'N'),(1,1,
19,NULL,NULL,NULL,13,NULL,NULL,NULL,NULL,'N'),(1,1,22,25,23,NULL,NULL,'User
Preferences',NULL,'Y','Y','Y'),(1,1,23,24,NULL,NULL,90,NULL,NULL,NULL,NULL,'
Y'),(1,1,24,NULL,NULL,NULL,92,NULL,NULL,NULL,NULL,'Y'),(1,1,25,NULL,26,NULL,
NULL,'Footer
folder','footer',NULL,'Y','Y'),(1,1,26,NULL,NULL,NULL,19,NULL,NULL,NULL,'Y',
'Y'),(2,1,1,4,2,NULL,NULL,'Header
folder','header',NULL,'Y','Y'),(2,1,2,3,NULL,NULL,10,NULL,NULL,NULL,NULL,NUL
L),(2,1,3,NULL,NULL,NULL,99,NULL,NULL,NULL,NULL,NULL),(2,1,4,10,5,NULL,NULL,
'Main',NULL,NULL,NULL,NULL),(2,1,5,8,6,NULL,NULL,'Column
1',NULL,NULL,NULL,NULL),(2,1,6,7,NULL,NULL,11,NULL,NULL,NULL,NULL,NULL),(2,1
,7,NULL,NULL,NULL,14,NULL,NULL,NULL,NULL,NULL),(2,1,8,NULL,9,NULL,NULL,'Colu
mn
2',NULL,NULL,NULL,NULL),(2,1,9,29,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL),(2,1
,29,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL),(2,1,10,17,11,NULL,NULL,'Deve
lopment',NULL,NULL,NULL,NULL),(2,1,11,13,12,NULL,NULL,'Column
1',NULL,NULL,NULL,NULL),(2,1,12,18,NULL,NULL,4,NULL,NULL,NULL,NULL,NULL),(2,
1,18,NULL,NULL,NULL,15,NULL,NULL,NULL,NULL,NULL),(2,1,13,NULL,1
5,NULL,NULL,'Column
2',NULL,NULL,NULL,NULL),(2,1,15,14,NULL,NULL,20,NULL,NULL,NULL,NULL,NULL),(2
,1,14,16,NULL,NULL,7,NULL,NULL,NULL,NULL,NULL),(2,1,16,37,NULL,NULL,16,NULL,
NULL,NULL,NULL,NULL),(2,1,37,NULL,NULL,NULL,22,NULL,NULL,NULL,NULL,NULL),(2,
1,17,21,19,NULL,NULL,'User
Preferences',NULL,'Y','Y','Y'),(2,1,19,20,NULL,NULL,90,NULL,NULL,NULL,NULL,'
Y'),(2,1,20,NULL,NULL,NULL,92,NULL,NULL,NULL,NULL,'Y'),(2,1,21,27,22,NULL,NU
LL,'CWebProxy Examples',NULL,NULL,NULL,NULL),(2,1,22,25,23,NULL,NULL,'Column
1',NULL,NULL,NULL,NULL),(2,1,23,24,NULL,NULL,17,NULL,NULL,NULL,NULL,NULL),(2
,1,24,NULL,NULL,NULL,17,NULL,NULL,NULL,NULL,NULL),(2,1,25,NULL,26,NULL,NULL,
'Column
2',NULL,NULL,NULL,NULL),(2,1,26,NULL,NULL,NULL,18,NULL,NULL,NULL,NULL,NULL),
(2,1,27,NULL,28,NULL,NULL,'Footer
folder','footer',NULL,NULL,NULL),(2,1,28,NULL,NULL,NULL,19,NULL,NULL,NULL,NU
LL,NULL);
/*!40000 ALTER TABLE up_layout_struct ENABLE KEYS */;
UNLOCK TABLES;
****************************************************
I did this by putting it into a text file and running the following from
DOS:
more error_repeat.sql | mysql --host=host --user=user --password=pwd
This gives the error message shown as below (copied from a DOS session):
c:\mysql\bin>more error_repeat.sql | mysql --host=localhost --user=user
--password=pwd
ERROR 1064 at line 44: You have an error in your SQL syntax near
'5,NULL,NULL,'C
olumn 2',NULL,NULL,NULL,NULL),(2,1,15,14,NULL,NULL,20,NULL,NULL,NU' at line
2
c:\mysql\bin>
I get the feeling this might be something to do with line lengths?
Fix:
not known
Synopsis: mysql extended insert problem
Submitter-Id: ?
Originator: Elliot Smith
Organization: University of Birmingham
MySQL support: none
Severity: non-critical
Priority: low
Category: mysqldump/mysql?
Class: sw-bug
Release: mysql-3.23.49
CLIENT
Windows XP machine; 256MB RAM; Pentium processor.
SERVER
Exectutable: mysqld-max
Environment: DELL server
System: Win2000
Compiler: (using precompiled version)
Architecture: i386
Elliot Smith
------------------------------
Web Development Engineer
Corporate Web Team
Information Services
University of Birmingham
email: [EMAIL PROTECTED]
telephone: 0121 414 7108
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
