1198: More Podcast problems BUG#5013

[Bex]

Yes, all those temp tables are from ADFF. I'm very sure that non of my scripts are the cause of your DB-corruption.
Perhaps you have managed to do it yourself by running some bad statements in SQL-Viewer?

But what is the corruption?
Podcast genre having two GenreID's?

[Owyn]

Corruption symptom is lots of junk rows with bad foreign links. For example.

Until I was working on this problem I had never used an UPDATE with the Viewer.
I had used DELETE FROM PodcastEpisodes previously in 3.0 to clean up from moving my Podcasts main folder outside of MM3.

I have also used Sqliteman to dump the schemas and recently took a look at SQLite Spy to confirm a problem Nyn was having.
As far as I know, I have not updated the database using those tools.

At the moment I am working on building a very vanilla database just for testing Podcasts. Also removed all except for bare minimum of scripts (see my sig).

After that I need to work on repairing the original database. First step planned is to implement (or find) an audit script to validate all foreign links.

[Bex]

Hard to say why you're having this. Perhaps some early MM3 alpha did it a long time ago?
Let us know if you find anything that could explain it.

[Owyn]

Just saw a symptom of the problem on a freshly rebuilt database with 1203.

More details to follow. I have just bumped implementation of my audit script to high priority.

[Owyn]

Quick test.

Code: Select all

SELECT * FROM PodcastEpisodes WHERE IDTRACK > 0 AND "Podcast" NOT IN (SELECT Genre FROM Songs WHERE Songs.ID = PodcastEpisodes.IDTrack)

Finds one set of bad episodes.

Possible contributing factor is that I re-added the LastFM Node script (for testing) yesterday evening, i.e. about 8 hours ago. Need to look at the script for UPDATE, etc.

------------
Last.Fm Node script now removed (With difficulty, I had to manually delete the auto script and the .ini section. Also had corruption in Now Playing/Current Player track info(looked like the charset had switched to an Asian set). However, those are other issues.)

[Owyn]

Got a theory about what is happening.

The situation:
I have Podcasts set for hourly updates.
The Last.Fm node script is (for some reason, Auto-DJ not checked, but that is what I was testing) adding a new track to Now Playing on completion of the current track. This function involves reasonably extensive SELECTs.

The mix wasn't bad, so I had just left it running for several hours. (Actually, I crashed for a bit).

The observation:
The erroneous PodcastEpisode.IDTrack all pointed to tracks which were likely to have been included in the mix.

The theory:
Some sort of conflict between Podcast Update and Last.FM Node AutoDJ which causes current Songs.ID to become invalid for Podcast Update.

[Owyn]

Another part of the analysis.

I changed my Podcasts Mask effective this rebuild to "C:\DATA\Podcasts31\<Album>\<Title>" and renamed my existing Podcasts media folder to "C:\DATA\Podcasts30".

This made it easy to track what had happened since re-building the database by going to Locations..Pocasts31.

I saw that the latest Real Deal Podcast track was stil showing as Unplayed but I knew I had listened to it.
All previous episodes in the feed had been either explicitly or automatically removed.
Feed is set to not show removed episodes.
Went to the Subscriptions Node. Saw the current episode was actually a EmmyLou Harris track.

Crap. At that point I started looking and posted the first of this series of messages.

Up to the present. Went to the Subscriptions node, Right clicked the "EmmyLou" episode and selected Find More from Folder(Library). All the tracks in the folder, with the exception of the erroneous track in the episode list, were marked as unplayed (Not surprising, I had just re-imported my music tracks with a Scan).

So, the erroneous track was marked as played. But, the correct episode track was played.

[Ludek]

Are the erroneous tracks in the episode list for any particular subscription or just shown in the main Podcast Subscriptions node? Do you say that they don't have genre = "podcast" and that they have not been downloaded and despite the fact they are under Podcast Subscriptions? Really strange, I wonder what could change PodcastEpisodes.idTrack or Songs.ID to break the right link.

[Owyn]

Several Subscriptions. Total of 12 PodcastEpisodes rows in error.

The episodes exist in Location...Podcasts31 but PodcastEpisodes.IDTrack points to another track.

I can e-mail you a zipped backup I took when I first noticed the problem.

[Owyn]

Just set Podcast Update to Manual. Don't want auto-delete to kick in and remove Music tracks. Should also freeze Podcast status (other than Played status) for the time being.

Going back to my fallback of subscribing daily podcasts in Google Reader and direct downloading.

[Ludek]

re: zipped backup - do you think your zipped DB?
The DB you previously send me was corrupted, did you check whether this is not the same case?
i.e. Did you try the

Code: Select all

SELECT *  FROM GenresSongs WHERE IDSong NOT IN (SELECT ID FROM Songs)

and the AS rules:
1. Genre doesn't equal 'Podcast'
1. Any text field contains 'Podcast'
whether there are a files with genre = 'Podcast' in results?

Is the LastFM Node script the only script you have installed? I haven't reviewed the code of LastFM node, but I reviewed MM internal code and there doesn't seem to be anything that could change/replace PodcastEpisodes.idTrack.

[Owyn]

re: zipped backup - do you think your zipped DB?
The DB you previously send me was corrupted, did you check whether this is not the same case?

At least some mislinks. Not sure how badly yet. Working on a script.
Hmmm. Going to trim the script back to just create a report. It's all the display stuff that's taking the time.
Should have a full report available in 1-2 hours.
Will be pretty easy to check the database status when the script is done.

i.e. Did you try the

Code: Select all

SELECT *  FROM GenresSongs WHERE IDSong NOT IN (SELECT ID FROM Songs)

Yep. Ok this time.
Can add in as many fancy tests as I can figure out a SELECT for as soon as I have the base script done.

and the AS rules:
1. Genre doesn't equal 'Podcast'
1. Any text field contains 'Podcast'
whether there are a files with genre = 'Podcast' in results?

See prev comment.

Is the LastFM Node script the only script you have installed? I haven't reviewed the code of LastFM node, but I reviewed MM internal code and there doesn't seem to be anything that could change/replace PodcastEpisodes.idTrack.

I have two scripts installed at all times in beta. SQL Viewer and RegExp Find & Replace.
The Last.Fm Node script was the only additional script, and, that was in test only.

Previously I also had installed the scripts shown in my signature for "Stable".

[Owyn]

That was a long 2 hours.
Anyway. First cut on script based test on current database. Script is very raw, but, the basics are there to add in any specific tests.

The Script:

Code: Select all

' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' This file can be replaced  in one of the future versions,
' so please if you want to modify it, make  a copy, do your
' modifications  in that copy and  change Scripts.ini  file 
' appropriately. 
' If you do not do this, you will lose all  your changes in
' this script when you install a new version of MediaMonkey
' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Option Explicit     ' report undefined variables, ...
Dim fout
' SDB variable is connected to MediaMonkey application object

Sub AuditDB
  ' Open inifile and get last used directory
  Dim iniF
  Set iniF = SDB.IniFile

  ' Let user select the output path
  Dim path
  path = iniF.StringValue( "Scripts", "AuditDBDir")

  path = SDB.SelectFolder( path, SDB.Localize( "Select where to create the Audit Report."))

  If path="" Then
    Exit Sub
  End If

  If Right( path, 1)<>"\" Then
    path = path & "\"
  End If

  ' Write selected directory to the ini file
  iniF.StringValue( "Scripts", "AuditDBDir") = path
  Set iniF = Nothing

  ' Connect to the FileSystemObject
  Dim fso
  Set fso = SDB.Tools.FileSystem

  ' Use progress to notify user about the current action
  Dim Progress, ExpText
  Set Progress = SDB.Progress
  ExpText = SDB.Localize("Creating Audit Report...")
  Progress.Text = ExpText

  Dim title
  title = "DB Audit Report"

  ' Create the Report File
  Set fout = fso.CreateTextFile( path & fso.CorrectFilename( title) & ".txt", True)
  fout.WriteLine "MediaMonkey3 Database Audit Report"
 
  '  Check the Database
  Call CheckDB()

  ' Finish the Report
  fout.WriteLine "" 
  fout.WriteLine "----End of Report--" 
  fout.Close
End Sub

Sub CheckDB()
	fout.WriteLine " "
	fout.Writeline "----Checking Links---"
	Call CheckKey("PodcastEpisodes","IDTrack","Songs","ID")

	Call CheckKey("ArtistsSongs","IDArtist","Artists","ID")
	Call CheckKey("ArtistsSongs","IDSong","Songs","ID")

	Call CheckKey("ArtistsAlbums","IDArtist","Artists","ID")
	Call CheckKey("ArtistsAlbums","IDAlbum","Albums","ID")
	
	Call CheckKey("GenresSongs","IDGenre","Genres","ID")
	Call CheckKey("GenresSongs","IDSong","Songs","ID")
	
	Call CheckKey("Songs","IDAlbum","Albums","ID")
	Call CheckKey("Songs","IDMedia","Medias","ID")
	Call CheckKey("Songs","IDFolder","Folders","ID")

	' Call CheckKey("Lists","IDListType","ListTypes","ID")
	
	Call CheckKey("ListsSongs","IDList","Lists","ID")
	' Call CheckKey("ListsSongs","IDListType","ListTypes","ID")
	Call CheckKey("ListsSongs","IDSong","Songs","ID")
	
	Call CheckKey("DeviceFilters","IDDevice","Devices","ID")
	
	Call CheckKey("DeviceTracks","IDDevice","Devices","ID")
	Call CheckKey("DeviceTracks","IDTrack","Songs","ID")
	
	Call CheckKey("Covers","IDSong","Songs","ID")
	
	Call CheckKey("PlaylistSongs","IDPlayList","Playlists","IDPlaylist")
	Call CheckKey("PlaylistSongs","IDSong","Songs","ID")
	
	Call CheckKey("SynchAlbum","IDDevice","Devices","ID")
	Call CheckKey("SynchAlbum","IDAlbum","Albums","ID")
	
	Call CheckKey("SynchArtist","IDDevice","Devices","ID")
	Call CheckKey("SynchArtist","IDArtist","Artists","ID")
	
	Call CheckKey("SynchLocation","IDDevice","Devices","ID")
	Call CheckKey("SynchLocation","IDMedia","Medias","ID")
	
	Call CheckKey("SynchPlaylist","IDDevice","Devices","ID")
	Call CheckKey("SynchPlaylist","IDPlaylist","Playlists","ID")
	
	Call CheckKey("SynchPodcast","IDDevice","Devices","ID")
	Call CheckKey("SynchPodcast","IDPodcast","Podcasts","ID")
	
	Call CheckKey("Played","IDSong","Songs","ID")
	
	Call CheckKey("PodcastEpisodes","IDPodcast","Podcasts","ID")
	Call CheckKey("PodcastEpisodes","IDTrack","Songs","ID")
	
	Call CheckKey("PathProcessing","IDMedia","Medias","ID")
	Call CheckKey("PathProcessing","IDSong","Songs","ID")
	
	Call CheckKey("Folders","IDMedia","Medias","ID")	
	Call CheckKey("Folders","IDParentFolder","Folders","ID")
	
	Call CheckKey("FoldersHier","IDFolder","Folders","ID")
	Call CheckKey("FoldersHier","IDChildFolder","Folders","ID")
	
End Sub

Function CheckKey(FromTable,FromField,ToTable,ToField)
	Dim strSQL, qrySQL, strResult, strCount1, strCount2, strCount3
	strResult = FromTable & "." & FromField & " -> " & ToTable & "." & ToField
		
	strSQL = "SELECT COUNT(*) AS COUNT FROM " & FromTable
	Set qrySQL = SDB.Database.OpenSQL(strSQL)
	strCount1 = qrySQL.StringByIndex(0)

	strSQL = "SELECT COUNT(*) AS COUNT FROM " & FromTable & " WHERE " &_
			FromField & " > 0"
	Set qrySQL = SDB.Database.OpenSQL(strSQL)
	strCount2 = qrySQL.StringByIndex(0)

	strSQL = "SELECT COUNT(*) AS COUNT FROM " & FromTable & " WHERE " &_
			FromField & " > 0 AND " &_
			FromField & " NOT IN " & "(SELECT " & ToField & " FROM " & ToTable & ")"
	Set qrySQL = SDB.Database.OpenSQL(strSQL)
	strCount3 = qrySQL.StringByIndex(0)

	fout.WriteLine strResult & "    Count[" & strCount1 & "]" & " Linked[" & strCount2 & "]" & " Errors[" & strCount3 & "]"
	if strCount3 <> "0" Then
		fout.WriteLine "********Error*********"
		fout.WriteLine " "
	End if
	
End Function  
  

The first report:

Code: Select all

MediaMonkey3 Database Audit Report
 
----Checking Links---
PodcastEpisodes.IDTrack -> Songs.ID    Count[3923] Linked[582] Errors[0]
ArtistsSongs.IDArtist -> Artists.ID    Count[33578] Linked[33578] Errors[0]
ArtistsSongs.IDSong -> Songs.ID    Count[33578] Linked[33578] Errors[0]
ArtistsAlbums.IDArtist -> Artists.ID    Count[2099] Linked[2099] Errors[0]
ArtistsAlbums.IDAlbum -> Albums.ID    Count[2099] Linked[2099] Errors[0]
GenresSongs.IDGenre -> Genres.ID    Count[29309] Linked[29309] Errors[0]
GenresSongs.IDSong -> Songs.ID    Count[29309] Linked[29309] Errors[0]
Songs.IDAlbum -> Albums.ID    Count[20680] Linked[20678] Errors[0]
Songs.IDMedia -> Medias.ID    Count[20680] Linked[20680] Errors[0]
Songs.IDFolder -> Folders.ID    Count[20680] Linked[20680] Errors[0]
ListsSongs.IDList -> Lists.ID    Count[1718] Linked[1718] Errors[0]
ListsSongs.IDSong -> Songs.ID    Count[1718] Linked[1718] Errors[0]
DeviceFilters.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
DeviceTracks.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
DeviceTracks.IDTrack -> Songs.ID    Count[0] Linked[0] Errors[0]
Covers.IDSong -> Songs.ID    Count[25342] Linked[25342] Errors[0]
PlaylistSongs.IDPlayList -> Playlists.IDPlaylist    Count[214] Linked[214] Errors[214]
********Error*********
 
PlaylistSongs.IDSong -> Songs.ID    Count[214] Linked[214] Errors[0]
SynchAlbum.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
SynchAlbum.IDAlbum -> Albums.ID    Count[0] Linked[0] Errors[0]
SynchArtist.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
SynchArtist.IDArtist -> Artists.ID    Count[0] Linked[0] Errors[0]
SynchLocation.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
SynchLocation.IDMedia -> Medias.ID    Count[0] Linked[0] Errors[0]
SynchPlaylist.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
SynchPlaylist.IDPlaylist -> Playlists.ID    Count[0] Linked[0] Errors[0]
SynchPodcast.IDDevice -> Devices.ID    Count[0] Linked[0] Errors[0]
SynchPodcast.IDPodcast -> Podcasts.ID    Count[0] Linked[0] Errors[0]
Played.IDSong -> Songs.ID    Count[206] Linked[206] Errors[44]
********Error*********
 
PodcastEpisodes.IDPodcast -> Podcasts.ID    Count[3923] Linked[3923] Errors[0]
PodcastEpisodes.IDTrack -> Songs.ID    Count[3923] Linked[582] Errors[0]
PathProcessing.IDMedia -> Medias.ID    Count[0] Linked[0] Errors[0]
PathProcessing.IDSong -> Songs.ID    Count[0] Linked[0] Errors[0]
Folders.IDMedia -> Medias.ID    Count[1491] Linked[1491] Errors[0]
Folders.IDParentFolder -> Folders.ID    Count[1491] Linked[1490] Errors[0]
FoldersHier.IDFolder -> Folders.ID    Count[9542] Linked[9542] Errors[0]
FoldersHier.IDChildFolder -> Folders.ID    Count[9542] Linked[9542] Errors[0]

----End of Report--

This should have checked every foreign key in the database.
Not sure what to make of the two errors.

Time for a break.