MySQL Bugs: #30138: mysql-client doesn't want to import database-Dump

Bug #30138	mysql-client doesn't want to import database-Dump
Submitted:	31 Jul 2007 9:53	Modified:	31 Jul 2007 14:38
Reporter:	Hanno Wagner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Solaris (community gpl)
Assigned to:		CPU Architecture:	Any
Tags:	mysql import database dump mysqldump

Description:
I try to migrate one database from a community-server (mysqldump Ver 10.11 Distrib 5.0.46, for pc-solaris2.10 (i386) to our enterprise-Server (mysql Ver 14.12 Distrib 5.0.46, for pc-solrais2.10 (i386) using readline 5.0).
my first attempt was the following command:

bash-3.00# mysqldump -uuser -ppassword -hcommunity-server database|mysql -uuser -ppassword database
ERROR at line 863: Unknown command '\"'.
mysqldump: Got errno 32 on write

Then I put the mysqldump into a file - without any problems or errors. After that I tried to import the same database via file into the local mysql system:

bash-3.00# mysqldump -uroot -pmg_4_ualle -hboerse-web boersenwebb > /tmp/boersenwebb.sql               
bash-3.00#
bash-3.00# mysql -uroot -pmg_4_ualle boersenwebb < /tmp/boersenwebb.sql
ERROR at line 863: Unknown command '\"'.

So, the problem seems to be within the mysql-client.

I have the following entries in the sql-file:

bash-3.00# head -870 /tmp/boersenwebb.sql |tail -20

LOCK TABLES `dax_etc` WRITE;
/*!40000 ALTER TABLE `dax_etc` DISABLE KEYS */;
INSERT INTO `dax_etc` VALUES ('1','17.09.99 17:08:05','Xetra DAX','+5303.94','-0.48','-0.01',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+5304.42',' 16 SEP 1999'),('2','17.09.99 17:08:07','Euro NM Deutschland','+3015.37','-1.71','-0.06',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+3017.08',' 16 SEP 1999'),('3','17.09.99 17:08:08','MDAX','+4166.09','+11.44','+0.28',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+4154.65',' 16 SEP 1999'),('4','17.09.99 17:08:09','Euro Stoxx 50','+3762.25','-12.99','-0.34',' 17 SEP 1999',' 14:44','17.09.99 16:44:00','+3775.24',' 16 SEP 1999'),('5','17.09.99 17:08:11','Dow Jones','+10838.14','+100.68','+0.94',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+10737.46',' 16 SEP 1999'),('6','17.09.99 17:08:13','Nasdaq 100','+2509.43','+42.55','+1.72',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+2466.88',' 16 SEP 1999'),('7','17.09.99 17:08:14','CAC 40','+4644.32','+3.77','+0.08',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+4640.55',' 16 SEP 1999'),('8','17.09.99 17:08:16','Nikkei','+17342.27','+50.68','+0.29',' 17 SEP 1999',' 06:07','17.09.99 08:07:00','+17291.59',' 16 SEP 1999');
/*!40000 ALTER TABLE `dax_etc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dbs_complete_1_1`
--

DROP TABLE IF EXISTS `dbs_complete_1_1`;
CREATE TABLE `dbs_complete_1_1` (
  `status_knz` char(1) NOT NULL default '',
  `chng_wkn` varchar(8) default NULL,
  `wkn` varchar(8) default NULL,
  `chng_edv_kuerzel` varchar(4) default NULL,
  `edv_kuerzel` varchar(4) default NULL,
  `chng_wp_bezeichnung_kurz` varchar(25) default NULL,
  `wp_bezeichnung_kurz` varchar(25) default NULL,

And the mysql-bin-log shows the following:

bash-3.00# mysqlbinlog enterprise-mysql-bin.000003 |tail -40
#070731 11:40:43 server id 1  end_log_pos 341812463     Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1185874843/*!*/;
CREATE TABLE `dax_etc` (
  `i_no` char(3) NOT NULL default '',
  `imp_time` varchar(20) default NULL,
  `index_name` varchar(35) default NULL,
  `value` varchar(15) default NULL,
  `net_change` varchar(15) default NULL,
  `percent_change` varchar(8) default NULL,
  `r_date` varchar(15) default NULL,
  `r_time` varchar(15) default NULL,
  `r_dt_time_offset` varchar(20) default NULL,
  `close` varchar(15) default NULL,
  `close_date` varchar(15) default NULL,
  PRIMARY KEY  (`i_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1/*!*/;
# at 341812463
#070731 11:40:43 server id 1  end_log_pos 341812579     Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1185874843/*!*/;
/*!40000 ALTER TABLE `dax_etc` DISABLE KEYS *//*!*/;
# at 341812579
#070731 11:40:43 server id 1  end_log_pos 341813771     Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1185874843/*!*/;
INSERT INTO `dax_etc` VALUES ('1','17.09.99 17:08:05','Xetra DAX','+5303.94','-0.48','-0.01',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+5304.42',' 16 SEP 1999'),('2','17.09.99 17:08:07','Euro NM Deutschland','+3015.37','-1.71','-0.06',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+3017.08',' 16 SEP 1999'),('3','17.09.99 17:08:08','MDAX','+4166.09','+11.44','+0.28',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+4154.65',' 16 SEP 1999'),('4','17.09.99 17:08:09','Euro Stoxx 50','+3762.25','-12.99','-0.34',' 17 SEP 1999',' 14:44','17.09.99 16:44:00','+3775.24',' 16 SEP 1999'),('5','17.09.99 17:08:11','Dow Jones','+10838.14','+100.68','+0.94',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+10737.46',' 16 SEP 1999'),('6','17.09.99 17:08:13','Nasdaq 100','+2509.43','+42.55','+1.72',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+2466.88',' 16 SEP 1999'),('7','17.09.99 17:08:14','CAC 40','+4644.32','+3.77','+0.08',' 17 SEP 1999',' 15:13','17.09.99 17:13:00','+4640.55',' 16 SEP 1999'),('8','17.09.99 17:08:16','Nikkei','+17342.27','+50.68','+0.29',' 17 SEP 1999',' 06:07','17.09.99 08:07:00','+17291.59',' 16 SEP 1999')/*!*/;
# at 341813771
#070731 11:40:43 server id 1  end_log_pos 341813886     Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1185874843/*!*/;
/*!40000 ALTER TABLE `dax_etc` ENABLE KEYS *//*!*/;
# at 341813886
#070731 11:40:43 server id 1  end_log_pos 341813995     Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1185874843/*!*/;
DROP TABLE IF EXISTS `dbs_complete_1_1`/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

How to repeat:
other databases work fine, so I guess one has to use our source database for this behaviour. I tried two different databases which worked fine..

I pasted the wrong window; could you please anonymize username and Passwords in the command-line-stuff? thanks ;)

Thank you for the report.

I can not repeat described behaviour with provided piece of code. Pleas upload file /tmp/boersenwebb.sql

According to "hide username/password". We can not edit bug reports. All we can do is hide bug report from public area. But in this case you will lose access to it.

Do you need the complete dump? Even zipped these would be ~520 MB. Or are the first 1000 lines okay, since the error already comes around line 850? Then it would be 50MB gzipped...

According to the pw-problem: I switched all passwords so it is not needed anymore, but thanks for looking there.

Thank you for the feedback.

We need part of dump we will get error with which. From the description first 1000 lines should be OK.

Thank you for the provided feedback.

Statement CREATE TABLE `dbs_complete_1_1` contains invalid character:

CREATE TABLE `dbs_complete_1_1` (
  `status_knz` char(1) NOT NULL default '',
<...>

  `mind_schluss_fortl_notierung` varchar(16) default NULL,
  `chng_inkrement_mindestschluá;

--
-- Dumping data for table `dbs_complete_1_1`
--

Please run SHOW CREATE TABLE `dbs_complete_1_1` on community server to we can try with valid definition of `dbs_complete_1_1` and provide output of SHOW VARIABLES LIKE 'collation%' on community server.

Please also indicate accurate version of communityt server: we have not 5.0.46 community, but only 5.0.45, 5.0.41 etc.

The case can get closed.
The problem was the non-ascii-character in the table, where "check table" on the community server (5.0.27) thought it would be still a valid table.
Since we don't use / don't need this table anymore, it was decided to delete it. everything else seems to work fine.

So thanks for the help finding this buggy table!