mixxxdb.sqlite error on startup

[daniel]

working with mixxx1.12 on archlinux on an Lenovo T61
BUT: Bug is reproducible with Mixxx1.11 !

I recently reinstalled dj-system and after installing mixxx i copied the .mixxx directory from backup to home.
When i now start mixxx i get an error saying:

Debug [Main]: Available QtSQL drivers: ("QSQLITE", "QSQLITE3", "QMYSQL3", "QMYSQL", "QODBC3", "QODBC")
Debug [Main]: DB status: "/home/daniel/.mixxx/mixxxdb.sqlite" = true
Debug [Main]: SchemaManager::upgradeToSchemaVersion upgrading 23 versions to version 23
Debug [Main]: Loading schema "/usr/share/mixxx/schema.xml"
Debug [Main]: Applying version 1 : "The base schema for the Mixxx SQLITE database."
Debug [Main]: Failed query: "CREATE TABLE IF NOT EXISTS settings (
        name TEXT UNIQUE NOT NULL,
        value TEXT,
        locked INTEGER DEFAULT 0,
        hidden INTEGER DEFAULT 0)" QSqlError(11, "Der Befehl konnte nicht ausgeführt werden", "database disk image is malformed")
Debug [Main]: Failed to move from version 0 to version 1
Debug [Main]: Rolling back transaction on "qt_sql_default_connection" result: true
exit 255

Mixxx Suggested renaming the mixxxdb and start all over, but that obviously gives me a fresh db with all my crates and playlists lost.

The Error lead me to this
http://techblog.dorogin.com/2011/05/sqliteexception-database-disk-image-is.html

so i did this.
step 3 (.read dumb_all.sql) gives me some errors like this
Error: near line 23478: UNIQUE constraint failed: library.id
Error: near line 23479: UNIQUE constraint failed: library.id
Error: near line 23480: UNIQUE constraint failed: library.id
Error: near line 33891: UNIQUE constraint failed: library.id

AND
resulting mixxxdb is much smaler (from 73849K to 14729k)

Then i started mixxx again

Debug [Main]: Available QtSQL drivers: ("QSQLITE", "QSQLITE3", "QMYSQL3", "QMYSQL", "QODBC3", "QODBC")
Debug [Main]: DB status: "/home/daniel/.mixxx/mixxxdb.sqlite" = true
Debug [Main]: SchemaManager::upgradeToSchemaVersion upgrading 6 versions to version 23

and opening the "select your music library" window.
Mixxx starts, an i get all my crates and Playlists. But almost empty!!!
some crates contain 4 songs, some 10, most 0.

So obviously i lost all my crates, which is…faltan palabras, ich weiß gar nicht was ich dazu sagen soll. ;)

Any chance to look into that?

[RJ Skerry-Ryan (rryan)]

hi daniel! Sorry for this data loss. We aren't careful with our UNIQUE constraints. We can recovery this by hand if you're interested and get it working with the latest Mixxx.

[daniel (mail-daniel)]

Hi Ryan.

So it happend again.
How can i take my crates and playlists from 1.11 to 1.12?

just installing and starting 1.12 goes terribly wrong
http://mixxx.org/forums/viewtopic.php?f=3&t=6472

thanks for your help!

[Max Linke (max-linke)]

HI Daniel

I looked into the db file that you uploaded. Your crates are basically still saved in there. But you have only ~7000 tracks saved in the track_locations table while the library table contains ~50000 entries. The mismatch between track_locations and library table already exists in the text dump of the sql database. So the database still knows about your tracks and crates it just forgot where most of them are saved.

What might work is to invalidate all your library hashes and then rescan your whole library.

"UPDATE LibraryHashes SET hash = 0;"

This will add all the tracks again with a track location. What you have to do now is connecting the track locations to the old library.ids'. We usually do this in 'TrackDAO::detectMovedFiles' but this function uses the filename (stored in the track_locations table) and track duration, so it won't work directly for you. But it should be possible to change the algorithm we use to take 'artist', 'title', 'album' and 'duration' from the library table only. If you have problems writing the sql code please tell us.

best Max

[Daniel Schürmann (daschuer)]

I remove this from the 1.12 target, because by now only one user is effected and no one is working on it. Sorry.

[xerus (xerus2000)]

Why is this bug categorised as "New" and does still exist? It is one of the first things I see when looking at the bug list, even though it is 99% likely that noone will ever get use from it. This Bug database is a mess of old reports...

[Uwe Klotz (uklotzde-deactivatedaccount)]

This bug report is obsolete after many changes on the database subsystem in 2.0/2.1. A fix is not possible without the ability to reproduce the bug.

Please open a new bug report if this should ever happen again with Mixxx 2.1 or newer.

[Swiftb0y (swiftb0y)]

Mixxx now uses GitHub for bug tracking. This bug has been migrated to:
https://github.com/mixxxdj/mixxx/issues/7292