SQL Encoding Read Error

[GoogleCodeExporter]

What steps will reproduce the problem?
1. I connected via SSH to a database and wanted to import a database.
2. The database is utf8 and Sequel Pro also shows UTF8 as encoding.
3. When I import the database I only get the error message SQL Encoding Read 
Error.

This is not a usability error. Even if it was, the app should recognize the 
encoding by itself.

Original issue reported on code.google.com by bwill...@gmx.de on 6 Jun 2010 at 11:29

[GoogleCodeExporter]

Also getting this error. Exporting a database via sqldump -- then importing 
through sequel-pro.

Original comment by kris.wh...@eightounce.com on 17 Jun 2010 at 5:21

[GoogleCodeExporter]

@bwillius and @christopherbc9 are you using a nightly build when receiving this 
error?

I can reproduce this error on a recent nightly build (2345). But the same file 
imports fine on 0.9.8. If any other devs want to take a look I can provide a 
test file (Just hit me up on IRC).

Original comment by mattlangtree on 23 Jun 2010 at 1:07

[GoogleCodeExporter]

No, this was the current download.

Original comment by bwill...@gmail.com on 23 Jun 2010 at 6:02

[GoogleCodeExporter]

I am also getting this error, what is the solution?

Original comment by kaku...@gmail.com on 2 Jul 2010 at 4:21

[GoogleCodeExporter]

The SQL import should always use UTF8 when reading in files; if it gets a file 
read error when using UTF8, it then falls back to using the current connection 
encoding.

I haven't been able to reproduce this, but it may be a general problem reading 
the file.  Anyone running into this, it'd be much appreciated if you can 
produce a file showing the problem!  I appreciate this may be difficult to do 
due to confidential data - but you may find that it's a specific block of text 
causing the problem...

Original comment by rowanb@gmail.com on 2 Jul 2010 at 4:33

[GoogleCodeExporter]

I've done some more testing, but have still been unable to reproduce this.  
Does anyone still have a file causing this problem available?

(I'm particularly interested in the approximate mix of characters in the file, 
and the file size.  matt - track me down on IRC at some point!)

Original comment by rowanb@gmail.com on 18 Jul 2010 at 12:28

[GoogleCodeExporter]

Hi rowanb,

I don't want to post my complete website here. Please contact me directly at 
bwillius@gmx.de for the file. The database is from Drupal and the export was 
done with phpMyAdmin 2.11.9.4. In zip format the export is 1,5 MB.

Original comment by bwill...@gmail.com on 18 Jul 2010 at 5:09

[GoogleCodeExporter]

bwillius kindly sent me a file which shows the problem.

It appears that phpMyAdmin has exported a number of utf8 tables using MacRoman 
encoding.  When I try to import using UTF8 it errors as mentioned above; if I 
set the connection encoding to MacRoman, the file is read correctly.

However, the file still isn't imported correctly - looking into this, it 
appears to be because phpMyAdmin is exorting table headers in the following 
format:

--
-- Comment about the table
--

CREATE TABLE …

In MySQL, "--" followed by whitespace - *including newlines* is treated as a 
comment, so the CREATE TABLE is seen as part of the previous comment and isn't 
run.

So there's multiple issues processing phpMyAdmin dumps here:
 - Encoding not UTF8.  This is obviously something that can be worked around by the user, but isn't easy to spot.  The best thing is probably for us to build in automatic encoding detection - this is a rather tricky task, but fortunately a couple of good libraries exist for it.
 - phpMyAdmin using "--" comments incorrectly.  @bwillius, is this something that you configured, or is this the default setting?

@christopherbc9 - your issue sounds slightly different, because sqldump exports 
using UTF8 by default.  Any chance you could provide a test file as well?

Original comment by rowanb@gmail.com on 19 Jul 2010 at 10:28

• Changed state: Accepted
• Added labels: Component-Logic, Milestone-Release0.9, Priority-Medium, Type-Defect, Usability

[GoogleCodeExporter]

This is the default setting of phpMyAdmin.

Original comment by bwill...@gmail.com on 19 Jul 2010 at 10:56

[GoogleCodeExporter]

@rowanb - I was getting ready to send you the file, but I thought i might test 
it out using the same build (2345) on a different machine. I cant seem to 
recreate this with my original file either. Sorry.

If it's any conciliation, I was switching between UTF8 and mac roman encoding 
types just before I tried my import first time around.  

Original comment by mattlangtree on 21 Jul 2010 at 9:04

[GoogleCodeExporter]

@mattlangree - I suspect it's because the import falls back to the current 
connection encoding if it can't read using UTF8.  So if your connection was set 
to MacRoman at the time, it would probably have worked.

--

I'm currently working on encoding improvements for the import process - 
specifically, a new dropdown when importing, so the file encoding can be set 
explicitly, and with the default selection being "Autodetect" - which will run 
a set of encoding tests against the file to determine the most likely encoding. 
 That on top of the current fallbacks should fix this issue.

Original comment by rowanb@gmail.com on 23 Jul 2010 at 12:46

• Changed state: Started

[GoogleCodeExporter]

r2475 adds a new "Encoding:" menu in the Import dialogs, including a new 
default "Autodetect" option.  This works with the test files I've been sent, 
and most files I've produced!  Of course it'll never be perfect, encodings 
being what they are, but there is now a manual setting available as well.

The error message -if an error occurs - has also been improved.

(Nightlies are available to test at http://nightly.sequelpro.com/ , but as 
they're not as widely tested we don't recommend their use with production data).

bwillius - the file you sent me still doesn't import correctly, but due to a 
separate issue; the file you sent combines comments in the "--" style and CR 
(\r) line endings, which combine to make many queries "invisible" as they are 
eaten by the comments.  This is Issue #652 , which I'll attempt to address 
tomorrow.

Original comment by rowanb@gmail.com on 25 Jul 2010 at 12:10

• Changed state: Fixed

[GoogleCodeExporter]

I have Sequel Pro version 0.9.9.1.

I created a DB with UTF8 as default encoding.

When I am trying to import the csv file, it is giving same reading error due to 
encoding issue.
I am trying to import the data in the table having UTF8 encoding and have 
selected Autodetect option of Encoding, while import.

Original comment by mitesh.s...@stripes39.com on 24 Oct 2011 at 8:15

[GoogleCodeExporter]

Mitesh,

Could you create a new issue with a little more information?  For example, the 
"autodetect" encoding option is based on scanning the first part of the file, 
up to the first 2.5MB - is the file larger than this?  Also, what was the error 
message? There are a couple of read-type errors, which should give a little 
more information as to the type of error.

Original comment by rowanb@gmail.com on 25 Oct 2011 at 10:04

[GoogleCodeExporter]

I was having this same issue today. I mysqldump'd a 1GB vBulletin database 
using --default-character-set=utf8, no compression or anything. Neither import 
using Auto-select or specifying UTF8 worked. 

Setting it to Mac OS Roman did the trick, though. Import worked fine.

Original comment by lee.pete...@gmail.com on 8 Mar 2012 at 12:56

[GoogleCodeExporter]

Certainly an odd one, I get this error fairly frequently and have to manually 
select "Mac OS Roman" as the encoding for it to work. All our databases are 
UTF-8 and the default encoding in Sequel Pro is set to UTF-8.

This error only seems to happen for me with databases dumped on a Linux server 
and imported into a Mac. The same database can be dumped from a Mac and 
restored without issue.

Original comment by sc...@headfirst.co.nz on 8 Apr 2013 at 11:09

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I am also dealing with the same issue.  Thank you #16 for your solution, this 
worked as a temporary resolution.

Original comment by jno...@getmoxied.net on 27 Feb 2014 at 6:57

[GoogleCodeExporter]

#16's solution works for me as well.

Original comment by fss...@gmail.com on 27 Feb 2014 at 7:18

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

#16 nailed it. how strange. it was "Romanian" for me though as I did not have 
"Roman".

Original comment by christop...@gmail.com on 20 Mar 2014 at 5:32

[GoogleCodeExporter]

Had the same problem this morning, setting the character encoding to 'Western 
(Mac OS Roman)' seemed to work perfectly though.

Original comment by b...@maido.com on 26 Mar 2014 at 12:43

[GoogleCodeExporter]

+1 For setting the encoding to 'Western (Mac OS Roman)' 

How odd!

Original comment by joelbrew...@gmail.com on 19 May 2014 at 3:12

[GoogleCodeExporter]

Yup, "Western (Mac OS Roman)" works, but only for regular .sql files, not 
zipped ones.

Original comment by kris...@kristengrote.com on 31 Jul 2014 at 10:00