1249 - Genres with No Songs

Beta Testing for Windows Products and plugins

Moderator: Gurus

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 7:42 am

I seem to have run into a problem in my db. I noticed while looking through the genre node for something else that I have three genre's which are not listing any tracks in them. When I open up the db in SQLite one has 15 tracks, the second has 84 tracks and the third has 2 tracks. Yet when I open the GenreSongs table, these tracks have IdSong id's that do not match anything in the Songs table. Can someone help me to get rid of them, they are the Rock, R&B and Hip-Hop genres?

Here they are in MM:

Image

Here they are in SQLite:

Image

Nyn

EDITED to change tonality of message.
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 7:48 am

> IdSong id's that do not match

It sounds to me the DB is not enforcing referential integrity contraints. This is a recipe for disaster in an app like MM so reliant on a DB with complex relations.
Chris

jiri
Posts: 5395
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: 1249 - Genres with No Songs

Post by jiri » Tue Jun 02, 2009 9:17 am

Sorry, but this seems to me to be unnecessarily strong critic. To answer raised questions:

1. We certainly try to do everything in order to keep DB in good shape. SQLite offers mainly triggers for this purpose and as you can see in 'delete_songs' trigger, whenever a song is deleted from Songs table, its GenresSongs records are also deleted.
2. Why doesn't Maintain library delete these junk records? Well, we simply haven't expected such a problem, due to what I write above in 1 and because all other MM operations on DB should be properly handled in transactions, this problem shouldn't normally happen. We could add some code there and maybe we will in next versions, but I'd prefer to find out what has happened and prevent it in the future.
3. So why did it happen? There are several possibilities. First of all, I suppose that you have used several alpha/beta versions in the past and since they certainly had bugs, they could have caused some problems. Then, you definitely use some scripts (apparent from the DB structure) and they can have their own bugs.

Jiri

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 9:37 am

> Sorry, but this seems to me to be unnecessarily strong critic.
> 1. We certainly try to do everything in order to keep DB in good shape.

My apologies if in fact MM is enforcing referential integrity.

> 2. Why doesn't Maintain library delete these junk records?
> Well, we simply haven't expected such a problem ... this problem shouldn't normally happen.

Decades of industry research showed that it does happen, which is why enforcing referential integrity has for years been considered essential. http://en.wikipedia.org/wiki/Referential_integrity.

MM's previous Jet engine makes this easy. MM's current SQLite unfortunately does not.
Chris

jiri
Posts: 5395
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: 1249 - Genres with No Songs

Post by jiri » Tue Jun 02, 2009 9:45 am

MM's previous Jet engine makes this easy. MM's current SQLite unfortunately does not.
Right, but after fixing some bugs of early alpha versions there haven't been any problems related to this. That said, we'll definitely keep this in mind for future updates and will try to find how to make things even safer.

Jiri

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: 1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 9:56 am

You all can debate the why's, who's and how's until the cow jumps over the moon. HOW DO I GET THIS FIXED AND KEEP IT FROM HAPPENING?? Do I need a scripter to fix this because I do not want this sitting around until the next versions to be fixed?

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 10:04 am

> Right, but after fixing some bugs of early alpha versions there haven't been any problems related to this.

I beg to differ: 1190 Device delete leaves orphan records in DB

The absence of RE checking means we'd never hear about such problems (except from rare users like myself and Nyn who stumble upon the junk by accident) EDIT: until a junk-sensitive bug arises.

My guess is that many users have DB junk resulting from occasional MM crash or other interruption during update transactions. It is not a real problem... yet.

> That said, we'll definitely keep this in mind for future updates

I suggest Maintain Library should collect this garbage. Else many a beta tester is going to be saddled with a junk-bloated database, the most serious effect of which will be to give nightmare unreproducable results if any junk-sensitive bug /does/ arise.
Chris

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 10:07 am

> HOW DO I GET THIS FIXED AND KEEP IT FROM HAPPENING??

Not possible with SQLite versions of MM, IMO.

SQLite is fine for light work such as managing INI file replacements, but I've seen no evidence it is capable as the solid RDBMS that heavyweight apps such as MM require.
Chris

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: 1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 10:16 am

chrisjj wrote:
I suggest Maintain Library should collect this garbage. Else many a beta tester is going to be saddled with a junk-bloated database, the most serious effect of which will be to give nightmare unreproducable results if any junk-sensitive bug /does/ arise.
Not to mention it wil discourage users from being beta-testers. Now I understand users are voluntarily taking on these risks, but what assurances are users given that these things will be taken care of. And I am sorry but it will be added to a possible future version is not a solution. I also understand that you cannot fix what you cannot think of, and I am not a programmer so I will not begin to discuss what checking and double-checking should or could be done. I put alot of effort and time into this db, and so far I have been lucky and have not encountered anything that has destroyed it but it is getting to the point where this is becoming more likely to happen. After this cycle, I am done beta-testing.

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: 1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 10:19 am

chrisjj wrote:> HOW DO I GET THIS FIXED AND KEEP IT FROM HAPPENING??

Not possible with SQLite versions of MM, IMO.

SQLite is fine for light work such as managing INI file replacements, but I've seen no evidence it is capable as the solid RDBMS that heavyweight apps such as MM require.
Wouldn't a delete SQL statement work to clear the junk records out? I know this will not prevent it from happening but at least I can clean them out and know that from this point forward any additional ones added would be the responsiblity of MM, or possibly a script. Though I doubt it is a script since I do not use any scripts which add to the genresongs table.

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 10:29 am

> Wouldn't a delete SQL statement work to clear the junk records out?

Yes, but take care to ensure it is pointed only at junk... and note it is not in general possible to identify what is junk.

> I know this will not prevent it from happening

Good! :)

> but at least I can clean them out and know that from this point forward any additional ones
> added would be the responsiblity of MM, or possibly a script.

No change there, then :)

Good luck!
Chris

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: 1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 10:37 am

chrisjj wrote:> Wouldn't a delete SQL statement work to clear the junk records out?

Yes, but take care to ensure it is pointed only at junk... and note it is not in general possible to identify what is junk.


Good luck!
It this case it should be fairly easy to remove the junk because the songid's start with numbers that are less than my earliest songid in the song table, which once again leads me to believe these were created when I converted to 3.0. And yes I did use the early 3.0 alpha/beta versions so who knows who the culprit is. And personally I could care less who the culprit is unless knowing that information will help in prevention. But I still do not think this is a case of a bug in a script since I do not use any that add or delete genre's.

Let me ask you this, would it be feasible to request a script from a scripter that could possible test for missing links between the referential integrity contraints? Is this something too daunting to accomplish? I am off to look in the WIKI for a table of the relational structure of the db.

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

chrisjj
Posts: 4933
Joined: Wed Feb 14, 2007 5:14 pm
Location: UK

Re: 1249 - Genres with No Songs

Post by chrisjj » Tue Jun 02, 2009 11:04 am

> would it be feasible to request a script from a scripter that could possible test
> for missing links between the referential integrity contraints?

Yes, If he had the relational structure.

> I am off to look in the WIKI for a table of the relational structure of the db.

If that is not current (w.r.t. post-3.0 structure changes), dejunking by it could do more harm than good.

And I know no way of verifying it is.
Chris

fourteen
Posts: 127
Joined: Tue Mar 03, 2009 9:03 pm

Re: 1249 - Genres with No Songs

Post by fourteen » Tue Jun 02, 2009 11:38 am

chrisjj wrote:> IdSong id's that do not match

It sounds to me the DB is not enforcing referential integrity contraints. This is a recipe for disaster in an app like MM so reliant on a DB with complex relations.
This is EXACTLY why I strive to make sure all my important data is in the tracks themselves, and not relying on the database to hold the important stuff. Then, it SHOULD be as simple as delete database+re-scan tracks to recreate the database.
Although MediaMonkey has one facility in place to detect which info is not in the tags/tracks, that facility is not complete nor bug free and I have been trying to encourage improvements in that area.

nynaevelan
Posts: 5559
Joined: Wed Feb 07, 2007 11:07 pm
Location: New Jersey, USA
Contact:

Re: 1249 - Genres with No Songs

Post by nynaevelan » Tue Jun 02, 2009 11:52 am

Unfortunately the three most important things to me are the added date, playlists and the playcounts/history. The added date I can restore because I have my timestamps set to the date the tracks are added to the library, the playlists I can save through the use of Zvezdand's export m3u script but the playhistory is something I must rely on the db for.

Nyn
3.2x - Win7 Ultimate (Zen Touch 2 16 GB/Zen 8GB)
Link to Favorite Scripts/Skins

Join Dropbox, the online site to share your files

Post Reply