29

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 `******`
--
5
  • What text caused the problem?
    – Rick James
    Aug 19, 2015 at 17:37
  • didn't say but my guess is a windows encoded line break Aug 19, 2015 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 Aug 20, 2015 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? Aug 20, 2015 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, 2016 at 11:41

1 Answer 1

67

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.

6
  • 2
    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. May 2, 2017 at 13:00
  • 1
    Thanks, this also works for MySQL dumps that are generated on CentOS.
    – Alex
    Jul 9, 2017 at 12:56
  • I confirm this works on MacOS High Sierra 10.13.6. thanks @liangzan Nov 13, 2018 at 11:30
  • This works with Sequel Pro (Nightly) on Mojave as well
    – Urs
    Jun 19, 2019 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, 2019 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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