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

[Jeff Mitchell]

Colin Guthrie wrote:
> 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!).
>   
One thing I haven't asked is your computer specs.  But depending on your 
specs, and how much you have your console set to buffer in memory, etc., 
maybe you're swapping or some other such thing.  Remember: console 
output is *slow*.  Have you seen this problem if you don't launch Amarok 
from a console?

I could believe that copious amounts of console output could make your 
system unhappy.  But the kind of wrench in the gears is that MySQL also 
doesn't allow temporary table sharing across threads, and you said the 
problem goes away with MySQL.  Do you still see the console errors in 
MySQL but it just doesn't kill your system?  It does output one line of 
output per error instead of three...maybe try changing it to also output 
three lines of output per error and see if that makes MySQL behave badly.

> 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.
>   

Okay, I can accept that you have a unique setup  :-)  I don't have 
35,000 entries in craploads of m3us...
>> 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.
>   
Okay.  Please do try this.
>> 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.
>   
Oh, ok.  So you've already tried against newer sqlite.

> 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.
>   
Yep, crack on.

I could do what you suggested and try to put in checks to see if it's 
sqlite and avoid pinging temp tables if so, or some other maybe more 
robust solution, but I'd really like to understand *why* this is a 
problem.  At the moment, no good answers to that question...so your 
debugging should help that, when you get the time to do it.

Another thing for you to try:  comment out the three lines 6131, 6132, 
and 6133 (from stock SVN) in collectiondb.cpp, which will suppress 
outputting sqlite compile errors to console (if your errors are not 
sqlite3_compile errors, comment out the appropriate section).  See if 
that changes things...if it does, it probably means that either your 
console output is tripping you up, or the sqlite3_errmsg function is 
causing an issue.

Thanks,
Jeff