Exporting a SQLite3 Table from a DB with "Error: no such collation sequence: IUNICODE"

Question

Using DB Browser for SQLite, you can now successfully export a table from databases with IUNICODE collation sequence errors, but it is a manual process using DB Browser's GUI. I used DB Browser because if you try to export the table using sqlite3 from bash or from the sqlite console, you get:

Error: no such collation sequence: IUNICODE

Since there appears to be no command line options for DB Browser (is this correct?), I am looking for another way so I can automate export of the table, preferably using bash script.

Based on CL's response below...did the following:

sqlite3 /home/lpc123/data/MM.DB "SELECT sql FROM sqlite_master WHERE type='table' AND name='Songs';" 

...which generated a long list of table definitions:

CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT COLLATE IUNICODE,IDAlbum INTEGER,Album TEXT COLLATE IUNICODE,AlbumArtist TEXT COLLATE IUNICODE,DiscNumber TEXT COLLATE IUNICODE,TrackNumber TEXT COLLATE IUNICODE,SongTitle TEXT COLLATE IUNICODE,SongPath TEXT COLLATE IUNICODE,Extension TEXT(5),Year INTEGER,Genre TEXT COLLATE IUNICODE,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT COLLATE IUNICODE,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT COLLATE IUNICODE,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT COLLATE IUNICODE,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT COLLATE IUNICODE,Custom2 TEXT COLLATE IUNICODE,Custom3 TEXT COLLATE IUNICODE,Custom4 TEXT COLLATE IUNICODE,Custom5 TEXT COLLATE IUNICODE,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT COLLATE IUNICODE,Publisher TEXT COLLATE IUNICODE,Encoder TEXT COLLATE IUNICODE,Lyricist TEXT COLLATE IUNICODE,Conductor TEXT COLLATE IUNICODE,Remixer TEXT COLLATE IUNICODE,InvolvedPeople TEXT COLLATE IUNICODE,OrigTitle TEXT COLLATE IUNICODE,OrigArtist TEXT COLLATE IUNICODE,OrigLyricist TEXT COLLATE IUNICODE,GroupDesc TEXT COLLATE IUNICODE,SubTitle TEXT COLLATE IUNICODE,ISRC TEXT COLLATE IUNICODE,InitialKey TEXT COLLATE IUNICODE,Language TEXT COLLATE IUNICODE,WebCommercial TEXT COLLATE IUNICODE,WebCopyright TEXT COLLATE IUNICODE,WebFilepage TEXT COLLATE IUNICODE,WebArtist TEXT COLLATE IUNICODE,WebSource TEXT COLLATE IUNICODE,WebRadio TEXT COLLATE IUNICODE,WebPayment TEXT COLLATE IUNICODE,WebPublisher TEXT COLLATE IUNICODE,WebUser TEXT COLLATE IUNICODE,OrigYear INTEGER,Tempo TEXT COLLATE IUNICODE,Mood TEXT COLLATE IUNICODE,Occasion TEXT COLLATE IUNICODE,Quality TEXT COLLATE IUNICODE,Lyrics TEXT COLLATE IUNICODE,Comment TEXT COLLATE IUNICODE, StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT COLLATE IUNICODE, SeasonNumber TEXT COLLATE IUNICODE, Actors TEXT COLLATE IUNICODE, Producer TEXT COLLATE IUNICODE, ParentalRating TEXT COLLATE IUNICODE, ArtworkModified REAL)

Removed all instances of "COLLATE IUNICODE" from that string:

CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)

Enabled write access to sqlite_master with PRAGMA writable_schema=1:

sqlite3 /home/lpc123/data/MM.DB "PRAGMA writable_schema=1;"

Here is where things broke down or the prior command did not take):

write your new table definition(s) into it

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='MonkeyTable'; Which I think means:

sqlite3 /home/lpc123/data/MM.DB "UPDATE sqlite_master SET sql='CREATE TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)' WHERE type='table' AND name='Songs';"

That caused this error:

Error: table sqlite_master may not be modified

I thought maybe the part about creating the table did not belong, so I tried it again this time without the CREATE TABLE part:

sqlite3 /home/lpc123/data/MM.DB "UPDATE sqlite_master SET sql='TABLE Songs (ID INTEGER PRIMARY KEY AUTOINCREMENT,Artist TEXT ,IDAlbum INTEGER,Album TEXT ,AlbumArtist TEXT ,DiscNumber TEXT ,TrackNumber TEXT ,SongTitle TEXT ,SongPath TEXT ,Extension TEXT(5),Year INTEGER,Genre TEXT ,FileLength INTEGER,SongLength INTEGER,IDMedia INTEGER,CacheStatus INTEGER,CacheName TEXT ,Rating INTEGER,Bitrate INTEGER,Seekable INTEGER,Broadcast INTEGER,PreviewState INTEGER,PreviewName TEXT ,PreviewStartTime INTEGER,PreviewLength INTEGER,Author TEXT ,SamplingFrequency INTEGER,Stereo INTEGER,VBR INTEGER,BPM INTEGER,SignType INTEGER,SignPart1 INTEGER,SignPart2 INTEGER,SignPart3 INTEGER,SignPart4 INTEGER,PlayCounter INTEGER,LastTimePlayed REAL,AudioCDTrack INTEGER,FileModified REAL,TrackModified REAL,MaxSample REAL,NormalizeTrack REAL,NormalizeAlbum REAL,Custom1 TEXT ,Custom2 TEXT ,Custom3 TEXT ,Custom4 TEXT ,Custom5 TEXT ,DateAdded REAL,OrigFileLength INTEGER,PreGap INTEGER,PostGap INTEGER,TotalSamples INTEGER,PlaybackPos INTEGER,GaplessBytes INTEGER,IDFolder INTEGER,IDEpisode INTEGER,TrackType INTEGER,Copyright TEXT ,Publisher TEXT ,Encoder TEXT ,Lyricist TEXT ,Conductor TEXT ,Remixer TEXT ,InvolvedPeople TEXT ,OrigTitle TEXT ,OrigArtist TEXT ,OrigLyricist TEXT ,GroupDesc TEXT ,SubTitle TEXT ,ISRC TEXT ,InitialKey TEXT ,Language TEXT ,WebCommercial TEXT ,WebCopyright TEXT ,WebFilepage TEXT ,WebArtist TEXT ,WebSource TEXT ,WebRadio TEXT ,WebPayment TEXT ,WebPublisher TEXT ,WebUser TEXT ,OrigYear INTEGER,Tempo TEXT ,Mood TEXT ,Occasion TEXT ,Quality TEXT ,Lyrics TEXT ,Comment TEXT , StartTime INTEGER, StopTime INTEGER, SkipCount INTEGER, VideoWidth INTEGER, VideoHeight INTEGER, FrameRate INTEGER, ContainerType INTEGER, StreamCount INTEGER, StreamInfo TEXT, EpisodeNumber TEXT , SeasonNumber TEXT , Actors TEXT , Producer TEXT , ParentalRating TEXT , ArtworkModified REAL)' WHERE type='table' AND name='Songs';"

That did not work either.

Answer 1

The answer to this was a script posted here that extracts all tables contained in the database to individual CSV files, and which can be run in bash. In this case, the output needed is one table, and was named songs.csv.

You can then create an SQL file: import.sql which you have to tailor to include all of the columns shown in the extracted table (this example shows four columns):

CREATE TABLE Songs (ID varchar(255) not null, Artist varchar(255) not null, IDAlbum varchar(255) not null, Album varchar(255) not null);
.separator ,
.import songs.csv Songs

Now you can run the following in bash:

sqlite3 currdb.sqlite < import.sql

...which creates an accessible database containing your imported table.

Answer 2

A working solution for MediaMonkey users:

1.) Download 'a bundle of command-line SQLite tools' for windows from here: https://sqlite.org/download.html (the archive is actually called sqlite-tools-win32-x86-3330000.zip and doesn't exist for 64bit, the 32bit works fine.)

2.) Extract the ZIP file, you need at least the file sqlite3.exe

3.) Always create a copy of your MM.DB, never use the original one for the following steps!

4.) Open your CopyOfMM.DB in a CMD window with sqlite3.exe CopyOfMM.DB

5.) On the sqlite-prompt, execute this command:

SELECT sql FROM sqlite_master WHERE type='table' AND name='Songs';

6.) Copy the complete CREATE TABLE output text into an editor like Notepad++

7.) Replace all the COLLATE IUNICODE text occurences with nothing (delete them)

8.) Quit the sqlite-prompt with .quit

9.) Execute the following command as one-liner in the CMD window (replace the CREATE TABLE Songs part completely with your modified text in Step #7. This text varies between MediaMonkey-Versions! Add the suffix ' WHERE type='table' AND name='Songs';")

sqlite3.exe CopyOfMM.DB "PRAGMA writable_schema=1; UPDATE sqlite_master SET sql='CREATE TABLE Songs (......)' WHERE type='table' AND name='Songs';"

This takes only a second.

9a) If you want to be sure if this command changed your table definition, repeat the steps #4 and #5 and double check if there is no IUNICODE COLLATION anymore. Quit the sqlite3.exe prompt.

10.) Reindex your table Songs with the command:

sqlite3.exe CopyOfMM.DB "REINDEX Songs;"

This takes a minute or so, depending on the amount of entries in this table.

Now you can finally execute any sqlite commands for your Songs table without the “Error: no such collation sequence: IUNICODE”

To repeat it: DON'T DO THIS WITH YOUR ORIGINAL MediaMonkey database!

Answer 3

You can modify the database to remove all references to that collation.

Read the table definition(s):

SELECT sql FROM sqlite_master WHERE type='table' AND name='MonkeyTable';

Remove the COLLATE IUNICODE from the string(s), then enable write access to sqlite_master with PRAGMA writable_schema=1; and write your new table definition(s) into it:

UPDATE sqlite_master SET sql='...' WHERE type='table' AND name='MonkeyTable';

If you do want to do this interactively, you must execute both statements in the same session:

sqlite3 mm.db "PRAGMA writable_schema=1; UPDATE sqlite_master ..."

Afterwards, reopen the database, and run REINDEX (because changing the collation made all index contents invalid).