Converting SQLite to MySQL

[Dominik Karall]

Hi Andrew,

thanks for you reply! Do you know if there are any other differences 
between MySQL and SQLite regarding to the usage with Amarok? Because 
if so, it would be great if I could include them as well to have the 
MySQL DB correctly built for Amarok to avoid errors in future.

If everything runs well I would for sure update the wiki too.

Thanks,
dominik

On Sunday, 12. November 2006 15:29, Andrew Turner wrote:
> 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