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

[Jeff Mitchell]

Colin Guthrie wrote:
> Hmmm, I'm not 100% sure that's the case to be honest. You know way more
> about the code than me so I'm apprehensive to question you but from what
> I can tell the _temp tables are only accessed when it's appropriate to
> do so:
>   
Yeah, I realized, I'm not correct.  Temp tables are always created.  
This was a dynamic collection change, IIRC, after AFT was already in 
there, because we don't want to drop entries for devices that are not 
plugged in (this is why temp tables are checked first to see if the 
unique ID exists in there).  So as far as I can remember/can tell from 
looking at the code right now, here's the process...sorta:

1) Temp tables are created
2) Entries from the permanent tables are copied over to the temp tables.
3) Items are scanned into the temp tables.
4) If it was an incremental scan, folders that changed are dropped from 
the permanent tables, otherwise all folders are dropped from the 
permanent changes.
5) The items are copied back over from the temp tables to the permanent 
tables.

> The error I see is definitely from the CollectionDB::uniqueidFromUrl()
> method. See below for a patch which "masks" the error (I wouldn't say
> "fix" as there may be other issues).
>   
So, if you want to know why that method is getting called, it's in 
ScanController::startElement().  There's a line:

bundle.setPath ( attrs.value( "path" ) );

When a MetaBundle gets a new path set, it checks to find out if there is 
an existing unique id for that path, and if so, fetches it.  In 
metabundle.cpp you can see that setPath calls setUniqueId (with no 
arguments) which is basically a two-line job that checks if it's a local 
file and if so it calls that function in collectiondb (you saw this when 
creating your patch).

That right there is the *why* of the function getting called during the 
scan.

Now, your patch is essentially making the issue, so let's look at what 
could be the problem here.

1) ScanController::instance()->tablesCreated() is returning true when it 
shouldn't.  The ScanController runs in its own thread, so it's possible 
that the tables are deleted during accessing of m_tablesCreated by that 
function, which then returns.  In that case you could theoretically 
attempt to access uniqueid_temp when you shouldn't, but it certainly 
shouldn't be for every entry in the database.  But maybe on your system, 
for some reason, this function is locking up (as it's a simple accessor 
I don't see how this would be the case).

As for testing this out, change your code to the following:
 
    bool throwawaybool = ScanController::instance() && 
ScanController::instance()->tablesCreated();
    bool scanning = false;
    if (!ignorescanning)
        scanning = ( ScanController::instance() && 
ScanController::instance()->tablesCreated() );

That way you'll always run tablesCreated(), and can see if that is 
causing the lockup.

2) sqlite doesn't like accessing tables that don't exist.  For fun, I 
opened up my collection.db with sqlite3 and attempted, using the up 
arrow and enter, to enter "select * from uniqueid_temp;" as fast as I 
possibly could.  You may not be surprised to learn that I didn't have 
any luck locking up my system, but maybe it'd be worth you trying it 
too.  What I would do is try building Amarok with external sqlite, and 
see if you see the same issue.  If you do, close Amarok and open up the 
collection.db with sqlite and try that select command on the 
non-existent table, and see if sqlite is hanging for some reason.

> For what it's worth, I am not touching the GUI at all when doing a full
> scan. The GUI is sitting with an active playlist (not playing, just
> populated) and the Colletion tab open (but empty if starting with a
> blank db)
>   
I've also tested with a populated playlist.

> There is a single call to uniqueIdFromUrl() in metabundle.cpp. It's in
> the MetaBundle::setUniqueId() overloaded method which in turn is called
> in that format from MetaBundle::setUrl( const KURL &url ) or
> MetaBundle::setPath( const QString &path ).
>   
Yep.
> CollectionDB::updateURL() will call MetaBundle::setPath() and
> subsequently go on to call doAFTStuff with a flag of false hard coded
> with regards to accessing the temporary tables. But in calling
> MetaBundle::setPath() before hand, CollectionDB::uniqueIdFromUrl() will
> be called and due to the way the calculation of whether to check the
> temporary tables is checked, it *will* check the temp tables...
>
> This is me just looking at the code now, but I think this could be the
> codepath I'm hitting. I'll verify this later.
>   
Yep.  Didn't see your analysis here before I wrote my text above :-)

>> Have you tried replicating this problem on MySQL or PostgreSQL?  For the 
>> record, I'm currently using Amarok's internal sqlite, and I've tried things 
>> like adding files to the playlist while a scan is running, and I've not been 
>> able to run into issues.
>>     
>
> Sorry, I thought I'd mentioned this already, but using MySQL neatly
> avoids all this, which is why I think it's a thread related issue with
> sqlite.
>
> As I clarified above, I am not manupilating playlists manually when it's
> scanning but I do have a lot of m3u/pls files in the collection that are
> being interpreted.
>
> I'll get some more debug output for you later as I think I've probably
> missed out some of the critical debug text that highlights where the
> problem is, some of which (from memory) relates to the
> PlaylistReader::doJob() and PlaylistReader::completeJob() methods one of
> which has the QDeepCopy of the MetaBundles object and one which
> doesn't..... I think this is the main hub of this threading issue!!
>   
At this point, barring your experiences from my two suggestions above, 
I'm pretty confident saying that either: you are looking at the wrong 
place (reading the playlists in your collection are in totally different 
code from here and have nothing to do with AFT) and are mistaking your 
freezing issue with AFT because of the sqlite errors you see; have some 
weird sqlite issue that is being exposed both in uniqueIdFromUrl as well 
as somewhere in the playlist reading code; or both.

I'd start looking into more esoteric things.  What version of gcc are 
you using, what optimization flags are you using, etc.  What version of 
gcc/optimization flags were used for your sqlite build.  Etc.

--Jeff