Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

While importing mysqldump file ERROR 1064 (42000) near ' ■/ ' at line 1

Tags:

Cannot import the below dump file created by mysqldump.exe in command line of windows

/*!40101 SET @saved_cs_client     = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `attachment_types` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `DESCRIPTION` varchar(50) DEFAULT NULL,   `COMMENTS` varchar(256) DEFAULT NULL,   PRIMARY KEY (`ID`),   UNIQUE KEY `UK_ATTACHMENT_TYPES___DESCRIPTION` (`DESCRIPTION`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 

While importing the file in command line

mysql --user=root --password=root < mysqldumpfile.sql 

It throws error

ERROR 1064 (42000) near ' ■/ ' at line 1 

Somebody please help me.

like image 364
Pavan Kumar N Avatar asked Feb 13 '14 11:02

Pavan Kumar N


2 Answers

Finally I got a solution

We need two options

  • --default-character-set=utf8: This insures UTF8 is used for each field
  • --result-file=file.sql: This option prevents the dump data from passing through the Operating System which likely does not use UTF8. Instead it passes the dump data directly to the file specified.

Using these new options your dump command would look something like this:

mysqldump -u root -p --default-character-set=utf8 --result-file=database1.backup.sql database1 

While Importing you can optionally use:

mysql --user=root --password=root --default_character_set utf8 < database1.backup.sql 

Source:http://nathan.rambeck.org/blog/1-preventing-encoding-issues-mysqldump

like image 54
2 revs, 2 users 97% Avatar answered Oct 28 '22 22:10

2 revs, 2 users 97%


It seems that the input file (mysqldumpfile.sql) was created in UTF-8 encoding so these first 3 bytes "at line 1" invisible to you in the .SQL file is the byte order mark (BOM) sequence

So try to change default character set to UTF-8

mysql --user=root --password=root --default_character_set utf8 < mysqldumpfile.sql 
like image 38
valex Avatar answered Oct 28 '22 22:10

valex