Error: no such collation sequence: IUNICODE (1)

incans · October 11, 2020, 11:18pm

OS: Windows 10 Pro 64 bit

LibreOffice: 6.3.6.2 (x64)

I am trying to use LibreOffice Base to query the MediaMonkey database which is an SQLite database file.

I installed both the Win32 and X64 SQLite drivers, and then set up an ODBC data source pointing at the MM.db file. By experiment I have found that only the x64 SQLite3 driver will open the database, using any of the 32 bit drivers generates an out of memory error when you try to test the ODBC connection.

The SQLite3 x64 ODBC driver can open the db file and see the table structure etc. I can even run a really simple SQL query, created from the Query Design view, such as-

SELECT "SongTitle" FROM "Songs"

However as soon as I enter any qualifying or filtering clauses such a WHERE or GROUP BY, e.g.-

SELECT COUNT("SongTitle") FROM "Songs" GROUP BY "Custom1"

The query fails to run with the error:

no such collation sequence: IUNICODE (1)

Notice the capitalised name here is “IUNICODE” not “UNICODE”. Is this a simple case of a typo in a hard coded string somewhere? I did not need to enter a collation sequence name during the setup, so I can’t tell where the data is coming from.

Any ideas on how to debug or fix this would be welcome.

frofa · October 12, 2020, 4:44am

Have you tried enabling the Run SQL directly option in the edit query window (top right)? The Base query parser might interfere with the syntax of your query if it is not disabled by this setting. As far as the mis-spelt IUNICODE goes, perhaps that was a mis-spelling when the problem table was first created? (a complete guess). There is something about the non-support of UNICODE in sqlite HERE.

incans · October 12, 2020, 11:25pm

Thanks for the link. That info on SQLite relates specifically to “case folding”, but it does imply that the default sqlite3 setup does not include all the data required for Unicodes complex collation rules, so perhaps it’s relevant.

A pain if that’s the case, Unicode is rapidly becoming the de facto standard for character sets beyond ASCII.

frofa · October 13, 2020, 1:18am

@icans: by all means post the outcome of any research you do regarding the issue of Unicode and SQLite.

incans · October 16, 2020, 10:00am

Additional information-

Enabling the Run SQL Directly option makes no difference to the error

Having downloaded and installed a dedicated tool (SQLite Studio 3.2.1) I can confirm-

SQLite Studio can connect to the database and query it. For example the following more complex query runs and produces the expected results:

select distinct
Custom1 as “Owner”, IDAlbum, max(DiscNumber)
from songs
where Custom1 = “User1” or Custom1 = “User2”
group by Owner, IDAlbum
;

(I use MediaMonkey’s custom field 1 to track the owner of a CD within the family)

The collation name “IUNICODE” appears in the Constraints view of tables in the MM database, however the Collations editor is blank (no collations defined).

It looks as if “IUNICODE” is the default collation baked into SQLite. As explained in the link @frofa provided, the std sqlite install doesn’t include explicit collation data. It looks like this is confusing LO Base

frofa · October 12, 2020, 4:43am

See comment above (revised 13 Oct 2020).

Ratslinger · October 29, 2020, 1:32am

Hello,

This may be a situation with the MediaMonkey DB. See:

https://www.mediamonkey.com/forum/viewtopic.php?t=70023

and possible fix:

incans · November 1, 2020, 7:58pm

Interesting. These comments suggest that “IUNICODE” was added by the MediaMonkey devs. As far as I can tell sqlite3 stores text as Unicode by default, but perhaps the IUNICODE collation name was added to make this explicit.

Anyhow, the suggested fixes basically involve rewriting the MM table definitions to remove the IUNICODE collation name. That’s pretty brute-force, and I would be concerned it might have unpredictable downstream effects on the usability of the database.