1

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

Blakey UK
  • 53
  • 8
  • How are you executing these statements? – Robert Moskal Feb 25 '18 at 22:56
  • 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 Feb 26 '18 at 08: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 Feb 26 '18 at 08:58
  • 1
    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 Feb 26 '18 at 22:17

1 Answers1

-2

Your table name should be inside your parenthesis.

Joel Libby
  • 106
  • 8