SQL error while doing Maintenance

[bonalymac]

When I run Maintenance, I'm getting this message, and the database hangs

Error Executing SQL "CREATE TABLE'PersonsText0_content'(docid INTEGER PRIMARY KEY,'c0Artist')"; table PersonsText0_content already exists (1,1)

Can't re-open the file. Any suggestions as to what might be going on?

Thanks

Colin

[JoePublic]

This is a 2-year old thread (with just one message), but I'm reopening it because no one replied to the original post. I just got the same error as @bonalymac (see his post) when doing Maintain Library with the Optimize (Complete) option.

"Retry" doesn't work... it just repeats the same error, which is
>> Error executing SQL "CREATE TABLE'PersonsText0_content'(docid INTEGER PRIMARY KEY,'c0Artist')" : table PersonsText0_content already exists (1,1)

"Ignore" brings up a new error, which is
>> Error executing SQL "CREATE TABLE 'PersonsText0_segments'(blockid INTEGER PRIMARY KEY, block BLOB)" : table 'PersonsText0_segments' already exists (1, 1)

"Retry" on that just brings up the same error again.

"Ignore" on that brings up this error:
>> Error executing SQL "CREATE TABLE 'PersonsText0_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level,idx))" : table 'PersonsText0_segdir' already exists (1, 1)

"Ignore" then brought up this error:
>> Error executing SQL statement "INSERT INTO PersonsText0_content VALUES (?,?)" : constraint failed (19, 19)

"Retry" brought up a different error:
>> Error executing SQL statement "INSERT INTO PersonsText0_content VALUES (?,?)" : PRIMARY KEY must be unique (19, 19)

"Ignore" on that reverted me back to the "constraint failed" above. Repeated Ignores kept returning the "constraint failed" error, and so I had to Cancel to get out of the loop.

Once I cancelled, the Optimize Database continued to run and still running as I write this. (In fact, it is running considerably longer than it usually does. I am keeping an eye on it and will update this post when it finishes.)

I do Maintain Library with complete optimization once every week or two and have had no problems before this. I'm on the current MMW Gold and have a rather large library: 280,975 files at the moment.

Help please. What can I do to fix the problem? Thanks.

[JoePublic]

Yes, something is now definitely wrong. It has been Optimizing the database for over 40 minutes now and it usually takes only about 3 minutes. I'm getting a lot of disk activity and Process Hacker (Task Manager on steroids) shows that MM is using 2-3% CPU. so it is doing something. Not sure what though. I might have to kill off the process. My mm.db file is on my SSD. I don't want it to keep writing and writing to the drive if it's just stuck, which it appears to be.

Update: I suspended the MM process for now. All the disk activity was on my C drive (the SSD) and none of it was on the D drive (spinning disk) where my media files are located. So I think the Optimize was just thrashing.

Update 2: I resumed the MM task and it did stop with the high level of disk access eventually, but then settled into a low level of activity (0.1% cpu, around 300B/sec disk access)** for hours. It never "completed" the optimization, and I finally terminated the process. Earlier this week I was running Bex's Tagging Inconsistencies functionality in the Files to Edit node. I wonder if that contributed to my problem today with the Library Maintenance? (Tagging Inconsistencies has a section entitled "Persons With..." and my problems seemed to do with the Persons table.

After terminating MMW, I reopened it and ran Maintain Library again, this time using the "Quick" Optimize. It finished in just a few seconds with no errors. Then I redid Maintain Library, this time with "Complete" Optimization. Almost immediately it came up with the "Error executing SQL "CREATE TABLE 'PersonsText0_content'(docid INTEGER PRIMARY KEY, 'c0Artist') : table 'PersonsText0_content' already exists (1, 1)" error again.

So... what do I do?

** Update 3: I discovered the idle state of MMW is to use about 0.1% CPU and access the disk at around 300B/sec on my PC. So the condition I reported in Update 2 was that MMW finished something and then went to an idle status, but the Maintain Library dialog box never went away.

[Peke]

Hi,
Open support ticket, supply us with LOG (http://www.mediamonkey.com/forum/viewtopic.php?f=1&t=69) and also attach MM.DB with LOG.

[JoePublic]

Thank you. I submitted ticket #GFK-438-22454.

[Ludek]

Hi,
I've just run the 'Optimize database (complete)' on the DB provided by you in #GFK-438-22454

1) There is no error
2) There is no 'PersonsText0_content' table, I've opened your MM.DB in the SQLite studio and run this query:
select tbl_name, sql, type from sqlite_master WHERE type='table'
=> no table like 'PersonsText0_content'

Is it possible that you uploaded the uncorrupted DB on both the links by accident?

Note that:
1) 'PersonsText0_content' is not native MM table (probably created by an addon)
2) The 'Optimize database (complete)' actually re-creates database, i.e. it copies all tables, indexes, triggers from the old database to a new one.
This process shouldn't be needed unless the database is corrupted or malformed somehow. Mostly 'Optimize database (quick)' should be enough.
But in any case there is also no reason to fail to copy a third party table. It should work fine anyway.

[Meander]

Ludek,

You need to have the script Tagging Inconsistencies (by Bex) installed to view the Temp tables in question.

Once installed, run the Persons With... subnode. When the queries have finished (keep MM open), connect to MM.db in SQLiteStudio and you will see:

PersonsText0 (virtual)
PersonsText0_content
PersonsText0_segdir
PersonsText0_segment

These tables persist in MM.db for the session.

[Ludek]

I see it now, tracked/fixed as http://www.ventismedia.com/mantis/view.php?id=13452

Thanks!

[JoePublic]

Woo-hoo! Thanks to Meander for advancing the conversation, and thanks to Peke and Ludek for getting this elevated to documented "bug" status with a target for resolution.

I wanted to add a thought to Ludek's comment that the "Optimize database (complete)" shouldn't be needed unless the database is corrupted or malformed somehow, and that mostly "Optimize database (quick)" should be enough.

The problem I've encountered twice now over the years is that (at least in some cases) the user does not know that the database is corrupted UNLESS he/she runs the Complete optimization. Optimize Complete will (sometimes?) throw an error where Optimize Quick will sail right through. I don't want corruption in my mm.db file of course, but if it IS corrupted, I'd like to find out sooner rather than later so I can address it and ultimately lose less data. On my 2014 vintage PC (having an Intel i5 CPU, lots of RAM and an SSD for a C: drive), it takes about 3 minutes to do a Optimize Complete on my library of 280,000 music files, whereas the Optimize Quick takes maybe 10 seconds. For me, the 3-minute operation is time well spent.

[Ludek]

FYI: You can download the new beta with the fix from here: http://www.mediamonkey.com/forum/viewto ... &start=285