6

I have a mysql .sql file, and am trying to use sqlite in-memory database for testing purposes, with the same file.

CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY AUTO_INCREMENT,

When importing I get an error

  • SQLITE_ERROR: near "AUTO_INCREMENT": syntax error

which is documented here:

In SQLite a column declared INTEGER PRIMARY KEY will autoincrement by itself. Remove the AUTOINCREMENT keyword and then try.

I can't remove the AUTOINCREMENT because it's required for mysql. Any advice?

Community
  • 1
  • 1
ripper234
  • 222,824
  • 274
  • 634
  • 905
  • 2
    Trying to create SQL for multiple databases by hand is maddening and you wind up using the lowest common denominator. You're best off using a SQL generator library for this and other reasons, a good one will take care of SQL dialects for you. For example, Javascript has [Knex.js](http://knexjs.org/). – Schwern Dec 07 '16 at 20:49
  • @Schwern we are already using Knew. The one SQL we have is a file that creates the schema the first time the app is started. Perhaps we should mirgate to Knex for that as well. – ripper234 Dec 07 '16 at 21:12
  • http://stackoverflow.com/questions/41027538/how-should-i-initialize-my-project-via-node-npm – ripper234 Dec 07 '16 at 21:17

1 Answers1

9

I have a solution for you.

MySQL supports a special kind of comment syntax, for when you want to use syntax that works in a more recent version of MySQL but not in an earlier version. Maybe you've seen this sort of thing in mysqldump output:

CREATE TABLE `products` (
  `product_id` int NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Notice the character set statement in the comment, with a funny string !40101 which means "if the version of MySQL is less that 4.1.1, then ignore this string as a comment, otherwise execute it as part of an SQL statement."

Whereas in SQLite, the interior of the comment is always just a comment.

So you can define your table like this:

CREATE TABLE IF NOT EXISTS users ( 
  user_id INTEGER PRIMARY KEY /*!40101 AUTO_INCREMENT */,
   . . .

SQLite will ignore the AUTO_INCREMENT, but MySQL will use it, assuming you use version 4.1.1 or later (which is all but certain).

I just tested this with SQLite 3.8.5 and MySQL 8.0.0-dmr and the create table works in both cases.

INTEGER is okay because it's a synonym for INT in MySQL. Don't use INT in SQLite, because the primary key column won't be auto-incrementing unless you literally use the type INTEGER.

Never worry about the argument to INT in MySQL, like INT(11), that number doesn't mean anything important.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This fixed this issue ... and sent me right on to the next issue in our file, `ENUM`. I think the better long-term solution is indeed to migrate from a static SQL file to Knex migrations. – ripper234 Dec 08 '16 at 00:43
  • 1
    @ripper234 see http://stackoverflow.com/questions/362044/mysql-enum-type-vs-join-tables/362058#362058 for my opinions on ENUM. – Bill Karwin Dec 08 '16 at 01:02
  • I concur... currently I'm just trying to migrate the sql statements to knex without changing the entire world around it :) One refactoring at a time. – ripper234 Dec 08 '16 at 01:51