0

I am getting an error which seem to be related to the new line character not being interpreted during import of a backup created with mydumper.

  • MySQL Server version: 8.0.19-10
  • mydumper version: mydumper 0.9.5, built against MySQL 5.7.21-21

I have tried the following:

  • myloader -d .
  • cat console.size-schema.sql | mysql console
ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"size" (\x0A  "id" int NOT NULL AUTO_INCREMENT,\x0A  "name" varchar(255) NOT NUL' at line 1

The file seems normal when I use cat

$ cat console.size-schema.sql 
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE "size" (
  "id" int NOT NULL AUTO_INCREMENT,
  "name" varchar(255) NOT NULL,
  "label" varchar(255) DEFAULT NULL,
  "width" int DEFAULT NULL,
  "height" int DEFAULT NULL,
  "depth" int DEFAULT NULL,
  "deleted_at" int DEFAULT NULL,
  "created_at" int DEFAULT NULL,
  "updated_at" int DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=522 DEFAULT CHARSET=utf8;
cornernote
  • 1,055
  • 1
  • 12
  • 20

1 Answers1

1

mysql uses " for escaping only in ANSI mode, by default it uses back tick

How do I escape reserved words used as column names? MySQL/Create Table

my personal preference is to use back tick everywhere as this setting is widely used, making it easier for mysql import/export via dump files

zainengineer
  • 13,289
  • 6
  • 38
  • 28
  • Turns out old server had `ANSI_QUOTES` and new server didn't. The `\x0A` looked like it was the issue, but was actually the `"`. Thanks for the help. – cornernote Sep 26 '20 at 11:45