Converting SQLite to MySQL

[Andrew Turner]

Dominik,

Amarok does have quite a lot of DB-specific code, including the types
for the "url" column. For MySQL we use VARBINARY( 255 ) to combat both
the problems you've described - case insensitivity and MySQL4's stupid
maximum column length (for indices and hence primary keys, I believe)

If you change VARCHAR(1024) to VARBINARY( 255 ) it might work for you,
but as the wiki mentions, this whole procedure is unsupported. Unless
you really really value your statistics, it might be worth starting
with a clean MySQL DB, or you just stick with SQLite.

If you do decide to try it and it works, it would be great if you
could let us know and either you or one of us could update the wiki.

Andrew

On 12/11/06, Dominik Karall <dominik.karall at gmx.net> wrote:
> hi,
>
> I tried to convert my sqlite database to mysql and followed the
> instructions on:
> http://amarok.kde.org/wiki/MySQL_HowTo
>
> following errors occured:
> -
> ERROR 1071 (42000) at line 23843: Specified key was too long; max key
> length is 999 bytes
>
> line 23843: CREATE TABLE statistics (url VARCHAR(1024),deviceid
> INTEGER,createdate INTEGER,accessdate INTEGER,percentage FLOAT,rating
> INTEGER DEFAULT 0,playcounter INTEGER,uniqueid VARCHAR(32)
> UNIQUE,deleted BOOL DEFAULT 0,PRIMARY KEY(url, deviceid) );
> -
> there were more such errors, that were caused by UTF-8 which needs
> 3byte per character, so 1024 must be changed to something about 300.
>
> another annoying bug:
> mysql seems to be case insensitive on inserts, so all my songs which
> are unique on my hdd (e.g. FOD.mp3 and fod.mp3) are treated as
> duplicate in mysql.
>
> Conclusion:
> IMHO there is no easy way to convert sqlite dbs to mysql. maybe these
> errors should be mentioned on the wiki too.
> but I'm asking myself, if these create table statements and inserts
> don't work with mysql correctly, how do they work native in amarok?
> because the case insensitivity could cause some problems with
> uniqueness.
>
> cheers,
> dominik
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok
>