sqlite3-to-mysql: Convertion hang on 'no such collation sequence: unicase' leading some tables crunched

Describe the bug Hello techouse, today I wanted to convert fresh and clean anki database .anki2 (sqlite3 based) into mysql to retrieve the schema on phpmysql and work on it.

I succeed to retrieve the schema (aka ER Diagram) from the fresh sqlite3 database with anki created with:

anki -l en -b ankidir/

using after DBeaver-ce behind on collection.anki2 . As you can see collection.anki2, a sqlite3 database contains 12 tables:

So what I did is to convert to mysql is

first create a mysql anki database for the user anki with phpMyAdmin
launch the conversion

sqlite3mysql -f ankidir/User\ 1/collection.anki2 -d anki -u anki -p

Expected behaviour All table conversion Completed

Actual result

sqlite3mysql -f ankidir/User\ 1/collection.anki2 -d anki -u anki -p 
MySQL password: 
2021-09-14 20:36:30 INFO     Transferring table col
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00, 22.26it/s]
2021-09-14 20:36:31 INFO     Adding index to column "mid" in table notes
2021-09-14 20:36:31 INFO     Adding index to column "csum" in table notes
2021-09-14 20:36:31 INFO     Adding index to column "usn" in table notes
2021-09-14 20:36:32 INFO     Adding index to column "odid" in table cards
2021-09-14 20:36:32 INFO     Adding index to column "did, queue, due" in table cards
2021-09-14 20:36:32 INFO     Adding index to column "nid" in table cards
2021-09-14 20:36:33 INFO     Adding index to column "usn" in table cards
2021-09-14 20:36:34 INFO     Adding index to column "cid" in table revlog
2021-09-14 20:36:34 INFO     Adding index to column "usn" in table revlog
2021-09-14 20:36:34 INFO     Transferring table deck_config
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00, 21.88it/s]
2021-09-14 20:36:35 INFO     Transferring table config
100%|██████████████████████████████████████████| 15/15 [00:00<00:00, 341.32it/s]
no such collation sequence: unicase

System Information

$ sqlite3mysql --version

| software               | version                                                                                   |
|------------------------|-------------------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.5                                                                                     |
|                        |                                                                                           |
| Operating System       | Linux 4.19.0-17-amd64                                                                     |
| Python                 | CPython 3.9.5                                                                             |
| MySQL                  | mysql  Ver 15.1 Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 |
| SQLite                 | 3.27.2                                                                                    |
|                        |                                                                                           |
| click                  | 8.0.1                                                                                     |
| mysql-connector-python | 8.0.26                                                                                    |
| pytimeparse            | 1.1.8                                                                                     |
| simplejson             | 3.17.3                                                                                    |
| six                    | 1.16.0                                                                                    |
| tabulate               | 0.8.9                                                                                     |
| tqdm                   | 4.62.1                                                                                    |

Additional context If you take a look on phpMyAdmin designer… you see whose tables has been crunched (5 disappeared) or even renamed in uppercase like fields.

To help you, here is a zip of ankidir folder

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 15 (7 by maintainers)

Commits related to this issue

:sparkles: Add unicase custom collation #28 — committed to techouse/sqlite3-to-mysql by techouse 3 years ago
:bug: Fix multiple column index length #28 — committed to techouse/sqlite3-to-mysql by techouse 3 years ago
:bug: Handle multiple column FULLTEXT index transfer error #28 — committed to techouse/sqlite3-to-mysql by techouse 3 years ago

Most upvoted comments

Well I am sorry it is indeed FIELDS with sqlite3 collection.anki .dump. No bad duck, it’s good luck. You win the bunny gold prize Big thanks. SoSie

sosie-js on Sep 15, 2021

Ok Mr Techouse, I will say, don’t bother with it unless you have free time. The way I solved is manually remove de COLLATE unicase stuff and going into manual debug mode with phpMyAdmin to remove buggy incompatible parts

sqlite3 collection.anki2 .dump > dump.sql
cat dump.sql | python sqlite3-to-mysql.py > nouveau-sql-pour-mariadb.sql

this includes :

-UNIQUE KEY Stuff, 
-string keys are not possible like "KEY text NOT NULL PRIMARY KEY,"
-without xxx unsupported such as "without rowid"
-extra tables like "sqlite_stat1 , sqlite_stat4"
-remove extra ANALYZE sqlite_master;
-Records seeing having  duplicate because mulitcolumn agregate key is not supported 
 ie PRIMARY KEY (ntid, ord)
-backslash quoting column names that can not be used because using reserved keywords: mod, KEY, left. field.
-washing all lines "CREATE INDEX foo ON bar ;"

Here are: fix.diff.txt sqlite3-to-mysql.py.txt

sosie-js on Sep 15, 2021