Skip foreign keys on TEXT/BLOB fields + use Primary key when not defined in REFERENCES

[DoctorAti]

Describe the bug
MySQL failed adding foreign key to attendance.link_to_member referencing member.None: 1170 (42000): BLOB/TEXT column 'link_to_member' used in key specification without a key length
Traceback (most recent call last)

System Information

$ sqlite3mysql --version

software	version
sqlite3-to-mysql	2.0.2
Operating System	Darwin 21.3.0
Python	CPython 3.9.16
MySQL	mysql Ver 8.0.28 for macos11 on x86_64 (MySQL Community Server - GPL)
SQLite	3.42.0
click	8.1.3
mysql-connector-python	8.0.33
pytimeparse2	1.7.1
simplejson	3.19.1
tabulate	0.9.0
tqdm	4.65.0

For fields which are of type text in sqlite3 and are foreign keys, it should automatically assign some length to key

[techouse]

Good spot.

Can you supply me an example database to test against?

[DoctorAti]

https://drive.google.com/file/d/1TXP_hmsEBPJQZUHxNq19I5yMQ6rAHloO/view?usp=sharing

[techouse]

Looks like the problem is in the setup of your foreign keys as they don't seem to point to anything. If you query for the foreign_key_list

PRAGMA foreign_key_list("attendance");

You'll get

id	seq	table	from	to	on_update	on_delete	match
0	0	member	link_to_member		NO ACTION	NO ACTION	NONE
1	0	event	link_to_event		NO ACTION	NO ACTION	NONE

The to column there is empty, that's why the script errors with

2023-06-27 09:00:33 INFO     Adding foreign key to attendance.link_to_member referencing member.None
2023-06-27 09:00:33 ERROR    MySQL failed adding foreign key to attendance.link_to_member referencing member.None: 1170 (42000): BLOB/TEXT column 'link_to_member' used in key specification without a key length

Do your foreign keys in SQLite even work properly?

[DoctorAti]

in dbeaver, i can see foreign key references to a column. Can you try to use some IDE and check if there is actually an issue with foreign key

[techouse]

I have used DBeaver do inspect it, however, that doesn't help me much in terms of the script which uses

PRAGMA foreign_key_list("attendance");

to get the keys programatically. I'm not aware of a different method. Could you maybe try and make your foreign key definitions more specific so that they have both from and to?

[techouse]

It looks like this is shorthand form foreign key that indirectly references the primary key of the linking table as is described here:

... shorthand form to create the foreign key constraint. Attaching a "REFERENCES " clause to a column definition creates a foreign key constraint that maps the column to the primary key of .

The attendance table indeed uses shorthand form as is visible from the DDL:

CREATE TABLE "attendance"
(
    link_to_event  TEXT
        constraint attendance_event_event_id_fk
            references event,
    link_to_member TEXT
        constraint attendance_member_member_id_fk
            references member,
    constraint attendance_pk
        primary key (link_to_event, link_to_member)
);

[techouse]

Hmm, getting to the bottom of this. Looks like you can't have foreign keys on TEXT / BLOB.

Index prefixes on foreign key columns are not supported. Consequently, BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.