Problems with sqlite support with 1.4.8: no such table: uniqueid_temp

[Colin Guthrie]

Jeff Mitchell wrote:
> But that doesn't affect anything.  At least not over here.  I just 
> managed to brute-force it so that I get the "no such table 
> uniqueid_temp" errors, but they in no way cause horrendous CPU usage
> or lockups, and it just falls through to the second case as intended.
> 
I'll do some more tests with and without the issue to try and measure
the speed difference. If I'm honest, it's not too bad with the local
collection I've been testing with and is a lot more apparent with the
NFS mounted one which is possibly due to the huge number of m3u files
there (none of which I really use anyway but then I've known how to work
around this issue by using MySQL from the beginning and I'm really just
trying to see if there really is something wrong now rather than work
around it!).

On my NFS mount, this will cause about 35k failed queries+debug output.
I'll try and see if just masking the debug output allows the scan to
complete in comparable time to MySQL backend which doesn't produce the
errors.... (which is odd.... if simply changing the backend either does
or does not produce the errors, I would say that there is something
wrong in the one that does.... or a misuse of the API etc., but
obviously that doesn't appear to be happening here as it's only me and a
handful of others who are affected).

The thing is I *know* those tables *do* exist at the time the
PlaylistBrowser tries to access them. I've put debug around when the
tables are created and dropped, and they are added at the beginning of
the thread and removed at the end. Therefore, there must be some sort of
weird issue whereby one thread/contexts can access the tables and
another (the playlist) cannot. This explains why MySQL can access it
(due to better thread handling and only one actual connection) whereas
there is an issue with sqlite.

I accept that it's harmless to try and access them but if the debug is
such that it does actually cause this slowdown then this could lead to
something productive... (e.g. a simple check for is sqlite and a check
in the sqlite_temp_master table to make sure the table exists may end up
"fixing" things)

>> Still the query will fail for every file in every playlist found
>> and thus that's a fair few pointless queries in my case which
>> probably accounts for the slowdown.
>> 
> No, it doesn't.  There are many queries being made for every file 
> scanned.  A single extra query isn't causing huge slowdown for you.
> The only thing it would do is cause the scanning to go slower, either
>  imperceptibly or almost imperceptibly (the slowest part of the
> scanning is the file parsing itself).

Well, due to the setup I will be doing about 3x as many *failed* queries
as there are files... (I have about 10k files, with 35k listed in m3us).
Now I know I need to tidy up the m3u's but that's not the point.

Like I say, I'll see if removing the debug output from the failed
queries speeds things up.

>> I really appreciate the time you're spending on this. I hope it
>> does turn out to be a bug and not just user support ;)
>> 
> Unfortunately at this point it's still the latter.  I still can't 
> reproduce this.  We have X number of Amarok installs out there that 
> include AFT, Y percentage of which are using sqlite (both probably 
> fairly large numbers) and you're the only one that seems to be having
>  this issue (not with seeing the uniqueid_temp errors -- that's just
>  normal debug output -- but with the huge long scan times seeming to
> be caused by a simple sqlite query).  So I still tend to think that
> this is something on your system.

Yeah, it is confusing but then I wonder how many users have crap loads
of old and unused m3u files kicking around in said collections. Maybe a
fair few. Also, if this bug is only triggered when the collection
scanner finds a new m3u which is loaded by the Playlistloader, doing
subsequent full scans will not trigger it.

The playlists need to be removed from the playlistbrowser_save.xml file
and a full rebuild done to trigger, thus it's not super likely that this
will be something that would crop up in normal usage after a full scan.



> Have you tried using an external, more recent version of sqlite and 
> seeing if that helps?  The most recent release is 3.5.6...you could
> just grab the source (the "amalgam" source) and overwrite what's in
> the Amarok tree with the sqlite.h and sqlite.c files if you want to
> keep it internal but want to try a newer version.  Make sure if you
> are using an external package outside the tree that sqlite is built
> with threadsafety.  Generally it is enabled by default.
> 
> I know this is frustrating for you, but as I can't reproduce this (on
>  two separate computers...one x86 and one x86_64) I still can't hack
> at it to figure out what's going on.  So I need you to keep trying
> things out on your side.

That's OK, I don't mind trying here and as I say I'm not doing this to
get a workign system either (that I can do with MySQL) I just want to
make sure this doesn't affect anyone else.

> Trying a recent sqlite version would be a help, because if that fixes
>  things, I'll bump it up in our tree.

Unfortunately our default packages are built against 3.5.6 by default
which is where I saw the original problem so I doubt this is gonna help.


I'll be away from debugging for a day or two, so if you have any bright
ideas let me know, otherwise I'll just crack on and report anything that
crops up.

Cheers.

Col