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.

mysql-to-sqlite3/src/mysql_to_sqlite3/transporter.py

Line 266 in acd0503

if isinstance(column_default, bytes):

[techouse]

That's a duplicate of #75

See #35 (comment) 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 8d3f70a broke it. SQLite does not support composite keys and auto increment.