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

[31 Jul 2007 9:53] Hanno Wagner
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..
[31 Jul 2007 9:56] Hanno Wagner
I pasted the wrong window; could you please anonymize username and Passwords in the command-line-stuff? thanks ;)
[31 Jul 2007 10:38] Sveta Smirnova
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.
[31 Jul 2007 13:00] Hanno Wagner
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.
[31 Jul 2007 13:08] Sveta Smirnova
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.
[31 Jul 2007 13:56] Sveta Smirnova
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.
[31 Jul 2007 14:38] Hanno Wagner
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!