Fix transferring composite primary keys when AUTOINCREMENT present

[tianzhuqiao]

Thanks for the great project. I am trying to convert the mysql database to sqlite. It works great, but it complains for one table that

ERROR    SQLite failed creating table abc: table "abc" has more than one primary key

Is it expected? I checked sqlite, looks like it shall support multiple key ?

The table looks like

mysql> describe abc;
+----------------+--------------------+------+-----+---------+----------------+
| Field          | Type               | Null | Key | Default | Extra          |
+----------------+--------------------+------+-----+---------+----------------+
| field1 | mediumint unsigned | NO   | PRI | NULL    | auto_increment |
| field2 | mediumint unsigned | NO   | PRI | NULL    |                |
| field3           | char(10)           | NO   | PRI | NULL    |                |
| field4           | char(50)           | YES  |     | -       |                |
| field5           | tinyint(1)         | NO   |     | 0       |                |
+----------------+--------------------+------+-----+---------+----------------+

System Information

$ mysql2sqlite --version

| software               | version                                                     |
|------------------------|-------------------------------------------------------------|
| mysql-to-sqlite3       | 2.2.0                                                       |
|                        |                                                             |
| Operating System       | Linux 5.14.0-460.el9.x86_64                                 |
| Python                 | CPython 3.9.19                                              |
| MySQL                  | mysql  Ver 8.0.36 for Linux on x86_64 (Source distribution) |
| SQLite                 | 3.45.3                                                      |
|                        |                                                             |
| click                  | 8.1.7                                                       |
| mysql-connector-python | 8.4.0                                                       |
| python-slugify         | 8.0.4                                                       |
| pytimeparse2           | 1.7.1                                                       |
| simplejson             | 3.19.2                                                      |
| tabulate               | 0.9.0                                                       |
| tqdm                   | 4.66.4     

This command is only available on v1.3.2 and greater. Otherwise, please provide some basic information about your system (Python version, operating system, etc.).

Additional context Add any other context about the problem here.

In case of errors please run the same command with --debug. This option is only available on v1.4.12 or greater.

[techouse]

Hey,

I have never come across 2 primary keys. Is it maybe a composite primary key? Can you paste your DDL?

Also, have you tried simply using a UNIQUE INDEX as described here?

[tianzhuqiao]

Thanks @techouse. The DDL looks like this. I haven't tried the UNIIQUE INDEX as the mysql database was created by someone else.

| abc  | CREATE TABLE `abc` (
  `field1` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `field2` mediumint unsigned NOT NULL,
  `field3` char(10) NOT NULL,
  `field4` char(50) DEFAULT '-',
  `field5` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`field3`,`field1`,`field2`),
  UNIQUE KEY `field1_UNIQUE` (`field1`,`field2`),
  KEY `fk_abc_filed2_idx` (`field2`),
  CONSTRAINT `fk_abc_field` FOREIGN KEY (`field2`) REFERENCES `def` (`field2`)
) ENGINE=InnoDB AUTO_INCREMENT=345 DEFAULT CHARSET=latin1 |

[techouse]

So it's a composite key

PRIMARY KEY (`field3`,`field1`,`field2`),

But I'm not sure this will work cause it has AUTO_INCREMENT in one of the fields and in SQLite AUTO_INCREMENT is kind of an anti-pattern.

Currently, the library does not support composite primary keys, but it should support composite UNIQUE indices.

I'll try and add this feature in the next release, but not sure when I'll have time to work on it. 🤷

[tianzhuqiao]

Thanks @techouse. May be a different issue. I found that for a table, it complains

2024-06-27 23:48:30 ERROR    SQLite failed creating table abc: near "0": syntax error

and looks like the issue comes from this column in the table (when process the default values)

 `fields` bit(1) DEFAULT b'0',

And in the following function, somehow column_default is of type "str" not "bytes". If I manually convert "b'0'" to bytes at the beginning of that function, looks like the issue goes away.

https://github.com/techouse/mysql-to-sqlite3/blob/acd0503cc030906eb013873d681cabbc5683d890/src/mysql_to_sqlite3/transporter.py#L266

[techouse]

That's a duplicate of #75

See https://github.com/techouse/mysql-to-sqlite3/issues/35#issuecomment-1549165589 for suggestions on how to fix this on your end.

[techouse]

Currently, the library does not support composite primary keys, but it should support composite UNIQUE indices.

Hmm, I just had another look at the code and I think that this should already work. Hmmm ... let me investigate.

EDIT: Looks like implementing auto_increment in https://github.com/techouse/mysql-to-sqlite3/commit/8d3f70a40e48117b39965b692ea3112be0309858 broke it. SQLite does not support composite keys and auto increment.

[techouse]

@tianzhuqiao The original issue regarding composite primary keys in conjunction with AUTOINCREMENT has now been fixed in and released in v2.2.1.

The library will now ignore AUTOINCREMENT in case of composite primary keys. This means that the MySQL DDL

CREATE TABLE `abc` (
  `field1` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `field2` mediumint unsigned NOT NULL,
  `field3` char(10) NOT NULL,
  `field4` char(50) DEFAULT '-',
  `field5` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`field3`,`field1`,`field2`),
  UNIQUE KEY `field1_UNIQUE` (`field1`,`field2`),
  KEY `fk_abc_filed2_idx` (`field2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

will get translated to SQLite like this

CREATE TABLE "abc" (
    "field1" MEDIUMINT NOT NULL  ,
    "field2" MEDIUMINT NOT NULL  ,
    "field3" CHARACTER(10) NOT NULL  ,
    "field4" CHARACTER(50) NULL DEFAULT '-' ,
    "field5" TINYINT NOT NULL DEFAULT '0' ,
    PRIMARY KEY ("field3", "field1", "field2")
);

CREATE UNIQUE INDEX "field1_UNIQUE" ON "abc" ("field1", "field2");
CREATE INDEX "fk_abc_filed2_idx" ON "abc" ("field2");

because SQLite does not support composite primary keys with AUTOINCREMENT.