Version: 1.4-SVN (using KDE 3.5.5, Gentoo) Compiler: Target: x86_64-pc-linux-gnu OS: Linux (x86_64) release 2.6.17-gentoo-r8 As discussed on IRC today I'm having a problem whereby Amarok tells me in the collection browser that "the file is not in your collection" when clearly it is. The full symptoms are: Started amarok after a clean exit yesterday and the collection was empty. Checked the db tables and the tags table was empty. After a rescan everything came back (covers, stats) except that now the warning shows up in the collection browser that the file isn't in the collection. I've attached a screenshot showing this along with stats that clearly require the file to be in the collection. I tried rescanning a few times. Any help is appreciated. p.s. I just upgraded a few things - mysql, dbus, hal. I'm sure these make the difference but I can't seem to figure it out.
Created attachment 19481 [details] Screenshot of funny behaviour
I think that the version of mysql I have now has filled all the varchar fields with spaces.
I switched on full logging for mysql and had a look at the events that were created. One of particular interest looks like this: SELECT url FROM tags WHERE url = './some/url/path/file.mp3';" It comes from the function CollectionDB::isFileInCollection on line 1435 of database_refactor/collectiondb.cpp I manually ran the command and it found no rows. Changing it to look like this: SELECT url FROM tags WHERE url REGEXP './some/url/path/file.mp3';" produces the one expected row. The problem is that for some reason the url and dir columns are filled. In mysql 5.0.26 this fill is the \0 character, but it shouldn't be used for VARBINARY fields, only BINARY fields. Anyone have any idea why this fill would be there? Here is the dev page on VARBINARY from mysql http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html
Sounds like this mysql bug: http://bugs.mysql.com/bug.php?id=19371
Indeed it is. The problem comes from upgrading 4.1 -> 5.0 Here's how I recovered from the problem: 1. ALTER TABLE tags ENGINE=myisam 2. Repeat 1 for all the tables 3. UPDATE tags SET url = TRIM(TRAILING '\0' FROM url) 4. Repeat 3 for VARBINARY fields url, dir etc. in all tables. (I excluded the uniqueid columns because they didn't seem padded. Also some of the columns were padded with space) 5. Pour a nice single malt scotch and listen to sweet sweet music. I'll attach an sql script that will fix this. I haven't yet fully tested this but I have a strong feeling it'll work just fine.
Created attachment 19489 [details] SQL to fix offending columns after upgrading mysql 4.1 -> 5.0
This has fixed the problem.
Ok, resolving.