TrackStat/Custom Scan/LMS Â/¬/URL problem

[prabbit]

This is going to be long as I describe my situation and what I've done to solve it, so bear with me. My actual goal here is to get my TrackStat ratings back into my LMS and then over to iTunes, but the path is going to be littered with challenges.

A few notes first:
1. I use the ¬ character as a separator in my file names. It is not used in the metadata, but it allows me to easily take file names and easily create metadata, which is a good thing in case things go really bad with my metadata (and backups).
2. I had a FLAC files, but recently converted them to ALAC (.m4a).
3. I also have mp3 files. They were unchanged.
4. All (well, most) of my files have a MusicBrainz ID. (The alac files took the same MB ID as the flac files. A small sample test of removing the flac MB ID and getting a new MB ID based on the alac file uses the same MB ID.)
5. I have a TrackStat backup XML file with thousands of files rated.
6. About the ¬ character...I have my files stored as /artist/album/year ¬ disc-track ¬ title. That's how the OS (details at the bottom of this post) sees the files and that is sort of how LMS sees them, too. I say sort of because sometimes LMS says the file is year ¬ disc-track ¬ title and other times it says the file is year ¬ disc-track ¬ title. And this is also how TrackStat sees the URL: year%2520%25C2%25AC%2520disc-track%2520%25C2%25AC%2520title.m4a. Question: Where did the Â/%25C2 come from?! I didn't put it there. I think the the errant  is a part of the problem.

My process so far:
1. After converting the FLAC files to ALAC, I cleared my LMS library and rescanned.
2. I edited the TrackStat backup XML file that I had from before I changed my flac files to alac files and changed the file extensions from .flac to .m4a. I also changed path directories previously called "flac" to "alac".
3. Using Custom Scan, I attempted to restore my TrackStat ratings. The ratings for the unchanged mp3 files appear to have been restored correctly, but not for the alac files. I don't understand why this is just an issue for the alac/m4a files. So I ran CustomScan and TrackStat in debug mode to look at the log files for clues. Sadly, there's nothing there that look suspicious to me. Erland, do you see anything?

[12-04-23 11:34:55.6758] Plugins::CustomScan::Scanner::moduleRescan (732) Performing module rescan
[12-04-23 11:34:55.6761] Plugins::CustomScan::Scanner::moduleRescan (739) Starting scanning with TrackStat Restore
[12-04-23 11:34:55.8266] Plugins::CustomScan::Scanner::initTrackScan (1318) Clearing track data for trackstatrestore
[12-04-23 11:34:56.2448] Plugins::CustomScan::Scanner::initTrackScan (1336) Start clearing track data with delete
[12-04-23 11:34:56.2489] Plugins::CustomScan::Scanner::initTrackScan (1350) Clearing track data...(Elasped time: 0.422237 seconds)
[12-04-23 11:34:56.2493] Plugins::CustomScan::Scanner::initTrackScan (1396) Deleted track data after 0.422739 seconds
[12-04-23 11:34:56.2500] Plugins::CustomScan::Scanner::initScanTrack (1428) Calling: Plugins::TrackStat::Plugin->trackstatrestore::initScanTrack
[12-04-23 11:34:56.2885] Plugins::CustomScan::Scanner::exitScanTrack (1903) Calling: Plugins::TrackStat::Plugin->trackstatrestore::exitScanTrack
[12-04-23 11:34:56.2888] Plugins::TrackStat::Backup::RestoreModule::exitSca nTrack (92) Deleting existing TrackStat statistics
[12-04-23 11:34:56.4006] Plugins::TrackStat::Storage::deleteAllTracks (1900) TrackStat: Clear all data finished at: 1335198896
[12-04-23 11:34:56.4013] Plugins::CustomScan::Scanner::exitScanTrack (1903) Calling: Plugins::TrackStat::Plugin->trackstatrestore::exitScanTrack
[12-04-23 11:34:56.4016] Plugins::TrackStat::Backup::File::initRestore (165) Restore from: /Volumes/MediaFiles/squeezebox/trackstat_backup/trackstat_scheduled_backup_20120417_afi.xml
[12-04-23 11:34:56.4020] Plugins::CustomScan::Scanner::exitScanTrack (1903) Calling: Plugins::TrackStat::Plugin->trackstatrestore::exitScanTrack
[12-04-23 11:34:56.4024] Plugins::TrackStat::Backup::File::resetScanState (238) Resetting scan state.
[12-04-23 11:34:56.4026] Plugins::TrackStat::Backup::File::scanFunction (261) Created a new Non-blocking XML parser.
[12-04-23 11:34:56.4031] Plugins::TrackStat::Backup::File::scanFunction (274) Parsing next bit of XML..
[12-04-23 11:34:56.4062] Plugins::TrackStat::Storage::findTrack (533) findTrack(): URL: file:///Volumes/MediaFiles/alac_mp3/alac/prabbit/AFI/Decemberunderground/2006%20%C2%AC%201-1%20%C2%AC%20Prelude%201221.m4a
[12-04-23 11:34:56.4065] Plugins::TrackStat::Storage::findTrack (534) findTrack(): mbId: 2909837c-5320-4aef-a71f-3e673f72377f
[12-04-23 11:34:56.4072] Plugins::TrackStat::Storage::saveTrack (1039) Saving play count, last played and added time in storage: 5, 1314199392, 1318526768
[12-04-23 11:34:56.4084] Plugins::TrackStat::Storage::findTrack (533) findTrack(): URL: file:///Volumes/MediaFiles/alac_mp3/alac/prabbit/AFI/Decemberunderground/2006%20%C2%AC%201-1%20%C2%AC%20Prelude%201221.m4a
[12-04-23 11:34:56.4087] Plugins::TrackStat::Storage::findTrack (534) findTrack(): mbId: 2909837c-5320-4aef-a71f-3e673f72377f
[12-04-23 11:34:56.4094] Plugins::TrackStat::Storage::saveTrack (1079) Store rating: 70
:
:
:
[12-04-23 11:34:56.6914] Plugins::TrackStat::Backup::File::handleEndElement (356) Finished restoring TrackStat XML file
[12-04-23 11:34:56.6917] Plugins::TrackStat::Backup::File::doneScanning (212) done Scanning: unlocking and closing
[12-04-23 11:34:56.6925] Plugins::TrackStat::Backup::File::doneScanning (230) Restore completed at 2012-04-23 11:34:56
[12-04-23 11:34:56.6929] Plugins::CustomScan::Scanner::exitScan (998) Calling: scanExit on Plugins::TrackStat::Plugin->trackstatrestore
[12-04-23 11:34:56.6933] Plugins::CustomScan::Scanner::exitScan (1014) Optimizing SQLite database
[12-04-23 11:36:44.7240] Plugins::CustomScan::Scanner::exitScan (1041) Rescan finished of Plugins::TrackStat::Plugin->trackstatrestore

4. Edited the TrackStat XML file to remove Â/%25C2 and processed the newly saved TrackStat file. No success.
5. Changed my file names removing the ¬/%25AC character and changed TrackStat to match it and processed that newly saved TrackStat file. No success.
6. I tried removing the MusicBrainz ID from the TrackStat file and processing that newly saved file. No success.

I am defining success as being able to see the rating in TrackStat/LMS in the web UI.

Again, my goal is to restore my ratings. After I get them in LMS, then I can begin the process of getting them to iTunes, but one step at a time.

I'm out of ideas. What else can I do?

-prabbit

LMS details:
Logitech Media Server Version: 7.7.3 - r33913 @ Wed Mar 28 02:01:55 PDT 2012
Operating system: Mac OS X 10.6.8 - EN - utf8
Platform Architecture: x86_64
Perl Version: 5.10.0 - darwin-thread-multi-2level
Database Version: DBD::SQLite 1.34_01 (sqlite 3.7.7.1)
Custom Scan v2.8.3779
TrackStat v3.0.3786

[erland]

After you have restored TrackStat, try to restart LMS before you view the ratings, the reason is that TrackStat has some caching of data for the web interface so there could be some bug so if you first view a specific track, then do the restore and then view exactly the same track/album as before it would show the cached data instead of what's in the database. If you see the message saying "Store rating: 70" in the log, it's an indication that the rating is written to the database and then the cause is probably the above mentioned cache issue.

It is of course a bug, because I should really empty the cache after a restore, but I might have missed this and then just restarting LMS after the restore operation has completely finished should do the same thing as it's cached in memory.

[prabbit]

Restarting LMS didn't help and just to cover my bases, this is what I did:

1. Uninstalled TrackStat and Custom Scan.
2. Deleted the entire Squeezebox cache directory.
3. Restarted LMS.
4. Cleared my entire library and rescanned.
5. Confirmed that no files had any TrackStat ratings.
6. Deleted my server.log and scanner.log files.
7. Restarted LMS.
8. Installed TrackStat (but not Custom Scan).
9. Restarted LMS.
10. With logging for TrackStat on Debug, I restored my ratings.
11. Before viewing any ratings, I restarted LMS.
12. I cleared my web browser's cache (you know, just to be safe).
13. Ratings for my mp3 files are present (just like before); however, ratings for my m4a files are not present even though the log file says the ratings are being written/saved.

Any other ideas?

[erland]

Can you install Database Query plugin and run its "TrackStat Inconsistency/Problems" query and post the result ?

It sounds very strange if the log in point 10 indicates that the m4a ratings are updated but they aren't available in point 13

[prabbit]

Here is the result

Missing entries in TrackStat
No
-3092

Not refreshed entries in TrackStat
Yes
1266

Not refreshed entries in TrackStat history
No
0

Unused entries in TrackStat data
Yes
13904

Unused entries in TrackStat history
No
0

Songs with really long urls (Longer than 511 characters)
No
0

Songs with long urls (Longer than 255 and shorter than 511 characters)
Yes
6

Songs with invalid urlmd5 entries
No
0

History entries with invalid urlmd5 entries
No
0

[erland]

Can you run the "Squeezebox Server Inconsistency/Problems" query also ?
The first number which is a negative number looks strange, I've seen other people with this behavior and that have been caused of them using cue sheets. There is a bug related to cue sheets and musicbrainz tags in LMS, but your issue isn't related to cue sheet tracks, is it ?

Another thing I would try is to try to use the "Remove all data" button in TrackStat settings page in the "Backup" section just before you start the restore, this will make sure the TrackStat database tables are empty before the restore operation starts and make sure previous data can't cause any issues.

Finally, can you check how the "Rating" number is set in the standard "Song Info" page which you get to when clicking on a song in one of the standard browse menus or in the current playlist, is that also empty ?

[haschmich]

I've got a negative number for "Missing entries in Trackstat" as well, and I don't even know what cue sheets are. Also I get the following error when I click on "Unused entries in Trackstat data":

Error: Carp::Clan::__ANON__(): DBI Exception: DBD::SQLite::db prepare failed: no such function: from_unixtime [for Statement " select track_statistics.rating,track_statistics.playcount,from_unixtime(track_statistics.lastplayed) as lastplayed,from_unixtime(track_statistics.added) as added,track_statistics.url from track_statistics left join tracks on tracks.urlmd5=track_statistics.urlmd5 and tracks.audio=1 where tracks.url is null order by track_statistics.url "] at /share/MD0_DATA/.qpkg/LogitechMediaServer/var/home/SqueezeboxServer/Slim/Schema/Storage.pm line 126 , no such function: from_unixtime

The reason why I'm looking into this is that I have one album for which trackstat doesn't seem to find any tracks anymore. Looking into the trackstat exports shows a big number of duplicate entries for the same song of this album, each with the same MusicBrainz id, only differing in the value for "added".

I'm wondering whether there's a way to delete all entries in the trackstat database for a specific MusicBrainz id?

[prabbit]

I've got a negative number for "Missing entries in Trackstat" as well, and I don't even know what cue sheets are. Also I get the following error when I click on "Unused entries in Trackstat data":

^^^ I also get that error when I click Unused entries in TrackStat data. I've prepared most of my reply to Erland, but I've been trying to figure out how to flush the Database Query database in the hopes that doing so would clear up the error.

[prabbit]

Can you run the "Squeezebox Server Inconsistency/Problems" query also ?

Sure. Here it is...

Missing rows in contributor_album table: No : 0
Invalid musicbrainz song tags: No : 0
Invalid musicbrainz album tags: No : 0
Invalid musicbrainz artist tags: No : 0
Duplicate musicbrainz song tags: Yes : 201
Duplicate musicbrainz album tags: Yes : 6
Duplicate musicbrainz artist tags: Yes : 2
Artists without any songs: No : 1
Albums without any songs: No : 0
Genres without any songs: No : 0
Years without any songs: No : 0

The first number which is a negative number looks strange, I've seen other people with this behavior and that have been caused of them using cue sheets. There is a bug related to cue sheets and musicbrainz tags in LMS, but your issue isn't related to cue sheet tracks, is it ?

I figured that negative number might be strange. I don't use or have any cue sheets.

Another thing I would try is to try to use the "Remove all data" button in TrackStat settings page in the "Backup" section just before you start the restore, this will make sure the TrackStat database tables are empty before the restore operation starts and make sure previous data can't cause any issues.

I removed all data and after I did this is what the database query for TrackStat Statistics looks like. Is this what you would expect? There's no entry under "Number of totally played songs." Should there be values under the unplayed albums/artists?

Number of songs: 0
Number of history entries: 0
Number of played unique songs: 0
Number of totally played songs:
Number of unplayed songs: 0
Number of unplayed albums: 2037
Number of unplayed artists: 1556
Number of rated songs: 0
Number of unrated songs: 0

This is what TrackStat Statistics looks like after I have deleted the data and then restored from my backup file. The number of songs is correct, but I would expect several thousand more songs to have a rating.

Number of songs: 20910
Number of history entries: 0
Number of played unique songs: 2332
Number of totally played songs: 6673
Number of unplayed songs: 18578
Number of unplayed albums: 1737
Number of unplayed artists: 1398
Number of rated songs: 2214
Number of 0 star rated songs: 6
Number of 1 star rated songs: 20
Number of 2 star rated songs: 1393
Number of 3 star rated songs: 252
Number of 4 star rated songs: 496
Number of 5 star rated songs: 47
Number of unrated songs: 18696

Finally, can you check how the "Rating" number is set in the standard "Song Info" page which you get to when clicking on a song in one of the standard browse menus or in the current playlist, is that also empty ?

I'm not exactly sure what you mean by "how the 'Rating' is set." Do you mean is the rating set (that is, does it have a value/1-10/stars/asterisks)? When I browse to a song (or search) and look at its rating, there is no rating (in other words, yes, the rating is empty). Of course, the exception is the mp3s where the rating is correctly being restored.

[haschmich]

I've just opened a separate thread for my problem with duplicate TrackStat entries, which may or may not be related:
http://forums.slimdevices.com/showth...n-every-rescan