List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:May 24 2002 2:27pm
Subject:Re: File created by mysqldump cannot be read into MySQL
View as plain text  
Don't use  'more'.  It adds characters to the output.

Instead:

mysql --host=host --user=user --password=pwd < error_repeat.sql



Elliot Smith wrote:

>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: e.smith@stripped
>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 <mysql-thread110085@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
File created by mysqldump cannot be read into MySQLElliot Smith24 May
  • Re: File created by mysqldump cannot be read into MySQLGerald Clark27 May