Mysql Create Table error on second CREATE statement [duplicate]

Question

I'm trying to write a script to import a Mysql DB structure. I've exported the database SQL via PhpMyAdmin, and node npm model sqldump. Both produce the same error when trying to create the second table. This doesn't seem to be table-specific - I can mix the tables around (I have 20 tables in this DB) and always hit the same error on the second CREATE TABLE statement.

Can anyone point me towards something stupid I'm missing, please?

The error is:

{ Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS `autoresponses` (
  `id` bigint(20) NOT NULL AUTO_INC' at line 14
    at PromiseConnection.query (D:\dev-mysql-update\node_modules\mysql2\promise.js:75:20)
    at D:\dev-mysql-update\index.js:157:7
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:188:7)
  message: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'CREATE TABLE IF NOT EXISTS `autoresponses` (\n  `id` bigint(20) NOT NULL AUTO_INC\' at line 14',
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '#42000' }

The first two tables are:

CREATE TABLE IF NOT EXISTS `autoresponders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `archived` tinyint(1) NOT NULL DEFAULT '0',
  `title` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `client_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `autoresponders_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `autoresponses` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `archived` tinyint(1) NOT NULL DEFAULT '0',
  `hours_delay` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `autoresponder_id` bigint(20) DEFAULT NULL,
  `notification_id` bigint(20) DEFAULT NULL,
  `client_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `autoresponses_autoresponder_id_notification_id_unique` (`autoresponder_id`,`notification_id`),
  KEY `notification_id` (`notification_id`),
  CONSTRAINT `autoresponses_ibfk_1` FOREIGN KEY (`autoresponder_id`) REFERENCES `autoresponders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `autoresponses_ibfk_2` FOREIGN KEY (`notification_id`) REFERENCES `notifications` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks in advance, Andy

Hi Robert, thanks for getting back to me. I'm using node-mysql2, after reading the content from a text file. I did wonder about that after posting this, so I tried a simple command line export and import into a new DB, and it worked fine. So I suspect it's some sort of character encoding thing. But both the text file and node-mysql are using UTF8, so I'm not sure whether there is something else in this area that I'm missing. — Blakey UK
– Blakey UK, Commented Feb 26, 2018 at 8:51
Actually, I might try to skip the discrete read of the text file, and simply use one of the pre-existing node modules that reads direct from the file (eg, node-mysql-importer or execsql), or maybe even just execute the mysql binary directly via node child_process.execSync - that'll get the intermediate step out of the way. — Blakey UK
– Blakey UK, Commented Feb 26, 2018 at 8:58
Robert, you're the man. That's exactly it. Can't believe I've missed that in reading through things. Thanks so much for helping out. — Blakey UK
– Blakey UK, Commented Feb 26, 2018 at 22:17

Joel Libby · Accepted Answer · 2018-02-25 22:51:22Z

-2

Your table name should be inside your parenthesis.

answered Feb 25, 2018 at 22:51

Joel Libby

1168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Blakey UK Over a year ago

Thanks Joel, but I'm pretty sure that's not right, unless I've misunderstood what you meant? dev.mysql.com/doc/refman/5.7/en/creating-tables.html

Joel Libby Over a year ago

Yeah sorry for leading you the wrong way.

Blakey UK Over a year ago

No problem - I appreciate any support that's intended to help.

Collectives™ on Stack Overflow

Mysql Create Table error on second CREATE statement [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related