23

I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through:

An error occurred when reading the file, as it could not be read in the encoding you selected (Autodetect - Unicode (UTF-8)).

Only 1273 queries were executed.

Is this because the database file was originally from a windows machine and it's linebreaks stuffed things up?

I imagine I will get this error even if I tried to send the same database over file transfer to a server so how can I fix it's encoding?

-- MySQL dump 10.13  Distrib 5.5.40-36.1, for Linux (x86_64)
--
-- Host: localhost    Database: *****
-- ------------------------------------------------------
-- Server version   5.5.40-36.1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `******`
--
  • What text caused the problem? – Rick James Aug 19 '15 at 17:37
  • didn't say but my guess is a windows encoded line break – smokerockspunchcops Aug 19 '15 at 22:42
  • Saving the file in notepad does have encoding option at the bottom.open the file in notepad on windows and save it as utf-8 format and then try loading it into MySQL – Nawaz Sohail Aug 20 '15 at 0:33
  • So the file isn't real utf-8? If I am on a mac I can do the same thing with it's default text viewer to fix it? – smokerockspunchcops Aug 20 '15 at 1:11
  • As Nawaz wrote above, open the file in a text editor (i used TextWrangler on Mac), and resave as utf-8 format. – Jojje Feb 4 '16 at 11:41
52

Based on this blog post

http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/

When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.

  • 1
    Does anyone know why this works? I would really like to know what problem existed with UTF-8 (or what problem with Windows), that did not let me import originally. – Douglas.Sesar May 2 '17 at 13:00
  • 1
    Thanks, this also works for MySQL dumps that are generated on CentOS. – Alex Jul 9 '17 at 12:56
  • I confirm this works on MacOS High Sierra 10.13.6. thanks @liangzan – Ahmad Mushtaq Nov 13 '18 at 11:30
  • This works with Sequel Pro (Nightly) on Mojave as well – Urs Jun 19 at 18:27
  • This worked correctly for a MySQL dump that originated on Linux / Mysql and needed to be imported over SSH to a MariaDB 10 database. Thanks. – HongPong Sep 11 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.