PRIMARY KEY is different

[crenelle]

In our mysql db we have a table with four columns comprising the PRIMARY KEY and one column being the UNIQUE key.

PRIMARY KEY (symbol,contract_year,contract_month,date), UNIQUE KEY id_column (id_column),

After using mysql2sqlite, that sqlite version of the table has id_column be the PRIMARY KEY.

, id_column integer NOT NULL PRIMARY KEY AUTOINCREMENT , UNIQUE (id_column)

This seems a little weird. Is it supposed to do that?

Thanks!

-Mike

[dumblob]

Hi Mike, indeed that doesn't sound right. I'm not aware of any such issues. Could you please post the whole "create" clause from mysqldump as currently I'm unable to reproduce it.

[crenelle]

PRIMARY_KEY_Issue.txt

How's this?

[dumblob]

Thanks, that made it clear. I'd call it a bug. Fixing it means to me ignore the autoincrement flag (and issue a warning about this) if it's not a primary key column. I.e. making gsub( /AUTO_INCREMENT|auto_increment/, "PRIMARY KEY AUTOINCREMENT" ) conditional (only if primary key is present) with an else branch issuing the warning. Feel free to make a PR (please coordinate with @vdechef regarding https://github.com/dumblob/mysql2sqlite/pull/71 which I'd like to review & merge in the next couple of weeks after I'm out of this busy phase).

The reason behind is, that sqlite doesn't support the exact autoincrement semantics on any other field than primary key:

Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

(at the bottom of https://www.sqlite.org/autoinc.html )

Usually it's though fine to forget about autoincrement and use rowid which sqlite uses internally for all tables (except special tables which are though so much limited and error-prone I'd discourage everybody to use them in any serious system). rowid is available when querying (despite it being internal/hidden) and is guaranteed to be unique (though not monotonically increasing as autoincrement mandates) which usually suffices.