1

Since the last days, i have been trying to migrate a MySQL database to PotsgreSQL, but there is a problem that doesn't let me succed: A bit column.

I have tried different methods: First i tried to use pgloader, because it seemed as the easiest, but i don't know how to write a script for this, so i took an example from internet:

load database
     from      mysql://dbu1:password@localhost/openbill
     into postgresql://dbu1:password2@localhost/openbill

 WITH include drop, create tables, no truncate,
      create indexes, reset sequences, foreign keys

  SET maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'openbill'

 CAST type datetime to timestamptz
                drop default drop not null using zero-dates-to-null,
      type date drop not null drop default using zero-dates-to-null

 -- Create the database in postgresql to import into. If your db already exist this can be skipped
 BEFORE LOAD DO
 $$ create schema if not exists openbill; $$

;

The problem with this, is the last line, i didn't know how to change the openbill, and gave me errors, so i tried deleting it, but of course, because of that it doesn't created the tables for the import.

After a lot of headaches, i tried using a dump, for that, i readed that it was necesary to change the sintaxis to be compatible with postgresql, so i found this code that do that: https://github.com/lanyrd/mysql-postgresql-converter but for some reason, it fails when it gets to the first table with a bit column, to be more exact, not when creating it, but when inserting data, because for some reason, the bit seemed like on the sql, and it gave me the error

ERROR:  invalid byte sequence for encoding "UTF8": 0x00

I generated tha dump with:

mysqldump -h ip -u root -p --compatible=postgresql --default-character-set=utf8 database > database.sql

Does anyone knows how to solve this problem? Or if there is a better way to make this migration?

UPDATE

Strings of the sql

mysqldump:

DROP TABLE IF EXISTS "fruto";
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "fruto" (
  "id" int(11) NOT NULL,
  "fruto_nombre" varchar(45) NOT NULL,
  "activo" bit(1) NOT NULL DEFAULT b'1',
  "estatus" bit(1) NOT NULL DEFAULT b'0',
  "idcf" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id")
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "fruto"
--

LOCK TABLES "fruto" WRITE;
/*!40000 ALTER TABLE "fruto" DISABLE KEYS */;
INSERT INTO "fruto" VALUES (1,'CHILE HABANERO MAYAPAN','','\0','HABAMAY'),(2,'PAPAYA MARADOL','','\0','PAYADOL'),(3,'TOMATE CHERRY','','\0','TOMACHER'),(4,'PEPINO INGLES','','\0','PEPING'),(5,'CHILE HABANERO MAYAN KISIN','','\0','HABAKIN'),(6,'CHILE HABANERO JAGUAR','','\0','HABAJAG'),(7,'CHILE HABANERO','','\0','HABA'),(8,'PIMIENTO','','\0','PIMI'),(9,'PIMIENTO NARANJA','','\0','PIMIN'),(10,'PIMIENTO ROJO','','\0','PIMIR'),(11,'PIMIENTO AMARILLO','','\0','PIMIA'),(12,'MINI PEPPER','','\0','MINIPEP'),(13,'MINI PEPPER NARANJA','','\0','MINIPEN'),(14,'MINI PEPPER ROJO','','\0','MINIPER'),(15,'MINI PEPPER AMARILLO','','\0','MINIPEA'),(16,'PEPINO FORMA CORAZON','','\0','PEPICO'),(17,'PEPINO FORMA ESTRELLA','','\0','PEPIES'),(18,'TOMATE','','\0','TOMA'),(19,'MELON','','\0','MELON'),(20,'BERENGENA','','\0','BERE'),(21,'SANDIA','','\0','SANDIA');

PostgreSQL

CREATE TABLE "fruto" (
    "id" integer NOT NULL,
    "fruto_nombre" varchar(90) NOT NULL,
    "activo" bit(1) NOT NULL DEFAULT b'1',
    "estatus" bit(1) NOT NULL DEFAULT b'0',
    "idcf" varchar(40) DEFAULT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "fruto" VALUES (1,'CHILE HABANERO MAYAPAN','','\0','HABAMAY'),(2,'PAPAYA MARADOL','','\0','PAYADOL'),(3,'TOMATE CHERRY','','\0','TOMACHER'),(4,'PEPINO INGLES','','\0','PEPING'),(5,'CHILE HABANERO MAYAN KISIN','','\0','HABAKIN'),(6,'CHILE HABANERO JAGUAR','','\0','HABAJAG'),(7,'CHILE HABANERO','','\0','HABA'),(8,'PIMIENTO','','\0','PIMI'),(9,'PIMIENTO NARANJA','','\0','PIMIN'),(10,'PIMIENTO ROJO','','\0','PIMIR'),(11,'PIMIENTO AMARILLO','','\0','PIMIA'),(12,'MINI PEPPER','','\0','MINIPEP'),(13,'MINI PEPPER NARANJA','','\0','MINIPEN'),(14,'MINI PEPPER ROJO','','\0','MINIPER'),(15,'MINI PEPPER AMARILLO','','\0','MINIPEA'),(16,'PEPINO FORMA CORAZON','','\0','PEPICO'),(17,'PEPINO FORMA ESTRELLA','','\0','PEPIES'),(18,'TOMATE','','\0','TOMA'),(19,'MELON','','\0','MELON'),(20,'BERENGENA','','\0','BERE'),(21,'SANDIA','','\0','SANDIA');

The PostgreSQL one was generated with the python program.

I don't know why, but the '' on values seems like (A square) on my computer, probably is the encoding of the explorer.

  • 1
    Please show us the table declaration involved in 0x00 and the SQL that includes that value. – Rick James May 10 '17 at 0:42
  • Sorry for the wait, i forgot my computer and didn't have a copy of the sql. – Efraín May 10 '17 at 15:05
2

Use --hex-blob on mysqldump.

See also https://bugs.mysql.com/bug.php?id=70404 (and possibly other bugs.)

  • It didn't work just by doing that, but at least gave me the lines with the 0x00 instead of the squares, then i used subime and changed it for the right format(B'0') and worked. Thanks! – Efraín May 10 '17 at 17:57
  • Does 0x00 fail on inserts? (I have not used BIT.) – Rick James May 10 '17 at 20:33

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.