MariaDB 10.6.16: Auto Increment on primary key is missing on SQLite

[ayalon]

Describe the bug I try to migrate a Drupal schema into SQLite to run tests on it.

Expected behaviour The converter works without an error. All tables and data is converted. Unfortunatly all auto_increment flags are missing and therefore the database is completely broken.

Ï'm using MadiaDB 10.6.16.

An example table from MariaDB looks like this:

CREATE TABLE `watchdog`  (
  `wid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key: Unique watchdog event ID.',
  `uid` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The \"users\".uid of the user who triggered the event.',
  `type` varchar(64) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'Type of log message, for example \"user\" or \"page not found.\"',
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'Text of log message to be passed into the t() function.',
  `variables` longblob NOT NULL COMMENT 'Serialized array of variables that match the message string and that is passed into the t() function.',
  `severity` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'The severity level of the event. ranges from 0 (Emergency) to 7 (Debug)',
  `link` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'Link to view the result of the event.',
  `location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'URL of the origin of the event.',
  `referer` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'URL of referring page.',
  `hostname` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '' COMMENT 'Hostname of the user who triggered the event.',
  `timestamp` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Unix timestamp of when event occurred.',
  PRIMARY KEY (`wid`) USING BTREE,
  INDEX `type`(`type`) USING BTREE,
  INDEX `uid`(`uid`) USING BTREE,
  INDEX `severity`(`severity`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'Table that contains logs of all system events.' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Actual result The resulting schema in sqlite looks like that:

create table main.watchdog
(
    wid       BIGINT                   not null
        primary key,
    uid       INTEGER      default '0' not null,
    type      VARCHAR(64)  default ''  not null,
    message   TEXT                     not null,
    variables BLOB                     not null,
    severity  TINYINT      default '0' not null,
    link      TEXT,
    location  TEXT                     not null,
    referer   TEXT,
    hostname  VARCHAR(128) default ''  not null,
    timestamp BIGINT       default '0' not null
);

create index main.severity
    on main.watchdog (severity);

create index main.type
    on main.watchdog (type);

create index main.uid
    on main.watchdog (uid);

watchdog: table
    + columns
        wid: BIGINT NN
        uid: INTEGER NN default '0'
        type: VARCHAR(64) NN default ''
        message: TEXT NN
        variables: BLOB NN
        severity: TINYINT NN default '0'
        link: TEXT
        location: TEXT NN
        referer: TEXT
        hostname: VARCHAR(128) NN default ''
        timestamp: BIGINT NN default '0'
    + indices
        #1: unique (wid)
        uid: index (uid)
        type: index (type)
        severity: index (severity)
    + keys
        #1: PK (wid) (underlying index #1)

wid is a primary key but has no autoincrement flag.

System Information

| software               | version                                                                                        |
|------------------------|------------------------------------------------------------------------------------------------|
| mysql-to-sqlite3       | 2.1.9                                                                                          |
|                        |                                                                                                |
| Operating System       | Linux 5.15.133.1-microsoft-standard-WSL2                                                       |
| Python                 | CPython 3.10.12                                                                                |
| MySQL                  | mysql  Ver 15.1 Distrib 10.6.16-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper |
| SQLite                 | 3.37.2                                                                                         |
|                        |                                                                                                |
| click                  | 8.1.7                                                                                          |
| mysql-connector-python | 8.3.0                                                                                          |
| python-slugify         | 8.0.4                                                                                          |
| pytimeparse2           | 1.7.1                                                                                          |
| simplejson             | 3.19.2                                                                                         |
| tabulate               | 0.9.0                                                                                          |
| tqdm                   | 4.66.1

I have no clue, why the auto_increment is missing. If I use this tool https://github.com/dumblob/mysql2sqlite the increment is there but other things are broken.

[techouse]

Unfortunatly all auto_increment flags are missing and therefore the database is completely broken.

Hmm, a missing AUTOINCREMENT shouldn't be the end of the world, no? 😂

I probably avoided adding it in the first place because the documentation states

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

I'll make sure to add it now.

[ayalon]

Thanks for your response. Unfortunately, the Drupal database schema relies on the auto_increment values and there is no way around it. If you could add support for it, this would be great.

[techouse]

Looks like this will be nastier than I thought because it can only be used with INTEGER PRIMARY KEY AUTOINCREMENT and would require a substantial rewrite of the code.

Not sure when I'll have time for it. If you have the time, please submit a PR, but make sure to test it properly.

[techouse]

@ayalon I managed to find some time and made it work, the only caveat is that you must only use numeric columns and non-compound primary indices as SQLite limits the use of AUTOINCREMENT only to INTEGER PRIMARY KEY AUTOINCREMENT

I have released the change in v2.1.10.

Please, test it as soon as you can and report back.

[ayalon]

I have tested this and it WORKS! Thanks a lot. Awesome work!

Can you also update the docker image with tag "latest". Currently the latest points to 2.1.9

[ayalon]

We use SQLLite to run tests in a pipeline for Drupal and with the latest release, the dump is fully compatible to it's MySQL equivalent.