Conversion error if create definitions' right parenthesis not in separate line

[juliniq]

On exporting a database schema, MySQL Workbench (6.3.10) puts the create definitions' right parenthesis on the same line as the last create definition:

-- MySQL Script generated by MySQL Workbench
-- Wed Nov 29 11:49:10 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `test_table`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test_table` ;

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` INT NOT NULL,
  `float_col` FLOAT NULL,
  `text_col` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Apparently mysql2sqlite doesn't expect this:

$ ./mysql2sqlite testdump.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `test_table` (
  `id` integer NOT NULL
,  `float_col` FLOAT NULL
,  `text_col` TEXT NULL
END TRANSACTION;
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".

Leading SQLite3 to output an error message:

$ ./mysql2sqlite testdump.sql | sqlite3 testdump.sqlite3
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".
memory
Error: near line 4: near "END": syntax error

Putting ENGINE = InnoDB on the same line as the right parenthesis and inserting a line break between the two right parentheses helped.

CREATE TABLE IF NOT EXISTS `test_table` (
  `table_id` INT NOT NULL,
  `float_col` FLOAT NULL,
  `text_col` TEXT NULL,
  PRIMARY KEY (`table_id`)
) ENGINE = InnoDB;

mysql2sqlite outputs a correct SQLite3 syntax:

$ ./mysql2sqlite testdump.sql
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `test_table` (
  `table_id` integer NOT NULL
,  `float_col` FLOAT NULL
,  `text_col` TEXT NULL
,  PRIMARY KEY (`table_id`)
);
END TRANSACTION;
WARN Pure sqlite identifiers are case insensitive (even if quoted
     or if ASCII) and doesnt cross-check TABLE and TEMPORARY TABLE
     identifiers. Thus expect errors like "table T has no column named F".

[juliniq]

I found a plugin (mysql-wb-exportsqlite) that converts MySQL Workbench models to SQLite3 directly, so probably I won't come across at this issue with mysql2sqlite anymore.

[dumblob]

Hi @juliniq, thanks for the report!

I've taken a look at the python plugin for the MySQL Workbench and it's definitely a better choice than this conversion script because of the two following reasons.

1. it uses solely internal API of MySQL Workbench and thus ensures semantical correctness of the data, schema, types, etc.
2. because it uses the internal API, it doesn't need to parse any text, nothing - so it's also quite fast (actually mysql2sqlite as well, so this point is unfair :wink:) and extensible

mysql2sqlite is on the other hand compatible only with the standard mysqldump output - which is unfortunately not what MySQL Workbench does. Use the commands in README of mysql2sqlite and you should be fine even with mysql2sqlite.

I can't and will not support any other syntax, because there are so many (each tool has a bit different output) and the point of mysql2sqlite is not to make a full-featured MySQL parser (because that would be extremely difficult, error-prone, and the resulting performance would be very slow).