SQL error while doing Maintenance

Get answers about using the current release of MediaMonkey for Windows.

Moderator: Gurus

bonalymac
Posts: 42
Joined: Fri Oct 12, 2007 4:49 am

SQL error while doing Maintenance

Post by bonalymac » Fri Jun 27, 2014 10:59 am

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
Posts: 109
Joined: Tue May 19, 2009 11:43 am
Location: Fenton, MO

Re: SQL error while doing Maintenance

Post by JoePublic » Sun Jul 24, 2016 8:53 am

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
Posts: 109
Joined: Tue May 19, 2009 11:43 am
Location: Fenton, MO

Re: SQL error while doing Maintenance

Post by JoePublic » Sun Jul 24, 2016 9:27 am

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.
Last edited by JoePublic on Mon Jul 25, 2016 10:01 pm, edited 1 time in total.

Peke
Posts: 13180
Joined: Tue Jun 10, 2003 7:21 pm
Location: Serbia
Contact:

Re: SQL error while doing Maintenance

Post by Peke » Mon Jul 25, 2016 8:28 pm

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.
Best regards,
Pavle
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
How to add SCREENSHOTS to forum

JoePublic
Posts: 109
Joined: Tue May 19, 2009 11:43 am
Location: Fenton, MO

Re: SQL error while doing Maintenance

Post by JoePublic » Mon Jul 25, 2016 9:56 pm

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

Ludek
Posts: 3376
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL error while doing Maintenance

Post by Ludek » Sun Aug 07, 2016 9:10 am

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
Posts: 67
Joined: Fri Jun 06, 2014 8:45 am

Re: SQL error while doing Maintenance

Post by Meander » Sun Aug 07, 2016 10:57 am

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
Posts: 3376
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL error while doing Maintenance

Post by Ludek » Mon Aug 08, 2016 1:59 pm

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

Thanks!
Last edited by Ludek on Mon Aug 08, 2016 3:45 pm, edited 1 time in total.

JoePublic
Posts: 109
Joined: Tue May 19, 2009 11:43 am
Location: Fenton, MO

Re: SQL error while doing Maintenance

Post by JoePublic » Mon Aug 08, 2016 3:24 pm

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
Posts: 3376
Joined: Fri Mar 09, 2007 9:00 am

Re: SQL error while doing Maintenance

Post by Ludek » Wed Aug 10, 2016 6:32 am

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

Post Reply