[cmsimike]

Hello,

Please help me understand what I'm seeing Kodi do during a music library update.

Before I run a music library update, I do a few things:
- Dump the music database, in case I need to revert
- Create a csv dump of songs where (iTimesPlayed > 0 or userrating > 0) so I can diff the results before and after the music update

I do this for a few reasons, but primarily because I have a lot of smart playlists that rely on those pieces of data. I've had issues historically where playcounts or userratings change and I do the two above to validate my music collection after a music scan.

Today I ran a scan and after comparing the results, I noticed I lost a lot of songs that should otherwise have an iTimesPlayed or userrating. I restored the database and re-ran the scan with logging enabled and right away something jumped out at me, a lot of songs got deleted for (seemingly to me) no reason:

2021-05-28 12:10:41.746 T:23384   DEBUG <general>: Mysql execute: DELETE FROM song WHERE idSong in (171920,171921,171922,171077,171425,171030,171168,171416,171290,171345,171379,171280,171036,171162,171026,170958,170959,171865,171057,171023,171181,171683,171684,171685,171686,171687,171688,171689,171690,171691,171692,171693,171473,171034,171066,170811,170812,170813,170816,170815,170817,170814,170818,170833,173361,170819,170821,170820,170822,170823,170824,170825,170828,170829,170831,170832,170834,170835,170836,170837,170838,170839,170840,170841,170842,170843,170844,170845,170846,170848,170847,170849,170850,170851,170852,170853,173362,170855,170856,170857,170858,170859,170860,170861,170862,170863,170864,170865,170866,170867,170868,170869,170870,170871,170872,170873,170874,170875,170876,170877,170879,170878,170880,170881,170883,170882,170884,170886,170885,170887,170888,170889,170890,170891,170892,170893,170894,170895,170896,170897,170898,170899,170900,170901,170903,170902,170904,173363,170906,170907,170908,170909,170910,170911,170912,170913,170914,170915,170916,170917,170918,170919,170920,170921,170922,170923,170924,170925,170934,170932,170931,170926,170930,170933,170927,170928,170929,170935,170936,170937,170938,170939,170940,170941,170942,170944,170943,170945,170946,170947,170950,173364,170949,170951,170952,170953,170954,170955,170956,170957,170960,170963,170962,170964,170961,170965,170967,170966,170968,170969,170970,170971,170972,170973,170974,170975,170976,170977,170978,170979,170980,170981,170982,170983,170984,170985,170987,170986,170988,170989,170990,170991,170992,170993,170999,170995,170996,170998,170994,170997,171000,171001,171002,171003,171004,171006,171005,171007,171008,171009,171010,171011,171012,171013,171014,171017,171016,171015,171018,171019,171020,171021,171022,171024,171025,171028,171027,171029,171031,171032,171033,171035,171037,171038,173365,171040,171041,171042,173366,171044,171045,171046,171047,171048,171049,171050,171051,171052,171053,171054,171056,171058,171059,171061,171060,171062,171063,171064,171065,173367,171068,171069,171070,171071,171073,171072,171192,171194,171436,171621,171622,171648,171649,171650,171651,171652,171653,171654,171655,171656,171657,171706,172033,171771,171963,171074,171075,171076,171078,171079,171080,171082,171083,171081,171084,171085,171086,171087,171088,171089,171090,171092,171091,171093,171094,171096,171095,173368,171098,171099,171100,171101,171102,171103,171104,171105,171106,171108,171107,171109,171110,171111,171112,171113,171114,171115,171116,169428,171117,171118,171120,171119,171121,171122,173369,171124,171125,171126,171127,171129,171128,171130,171131,171132,171133,171134,171135,171136,171137,171138,171139,171140,171141,171142,171143,171144,171146,171145,171147,171150,171151,171152,171153,171154,171155,171156,171157,171158,171159,171160,171161,171163,171164,171165,171166,171167,171169,171170,171171,171172,171173,171174,171175,171176,171177,171178,171180,171182,171183,171184,171185,171186,171187,171188,171189,171190,171191,171193,171195,171196,171197,171199,171200,171201,171202,171203,171204,171205,171207,171206,171209,171210,171211,171212,171213,171214,171215,171216,171217,171218,171219,171220,171223,171224,171225,171226,171227,171228,171229,171230,171231,171232,171233,171234,171235,171236,171237,171238,171239,171240,171242,171241,171243,171244,171245,171246,171248,171247,171252,171249,171250,171251,171253,171254,171255,171256,171257,171258,171259,171260,171261,171263,173370,171264,171265,171267,173371,171268,171269,171270,171271,171272,171273,171274,171275,171276,171277,171278,171279,171281,171282,171283,171284,171286,171287,171288,171289,171291,171292,171293,171294,171295,171296,171297,171298,171299,171300,171301,171302,171303,171304,171305,171306,171307,171308,171309,171310,171311,171312,171313,171314,171319,171315,171318,171317,171316,171320,171321,171322,171323,171324,171325,171326,171327,171328,171329,171330,171331,171332,171333,171334,171336,171335,171337,171338,171339,171340,171341,171342,171344,171343,171346,171347,171350,171348,171349,171351,171352,171353,171354,171355,171356,171357,171358,171359,171361,171360,171362,171363,173372,171365,171366,171367,171369,171368,171370,171371,171372,171373,171374,171375,171376,171377,171378,171380,171381,171382,171383,171384,171385,171386,171387,171388,171389,171390,171392,171393,171394,171395,171396,171397,171398,171399,171400,171401,171402,171403,171404,171405,171409,171408,171407,171406,171410,171411,171412,171413,171414,171415,171417,171418,171419,171420,171421,171423,171424,171426,171427,171428,171429,171430,171431,171432,171433,171434,171435,171437,171438,171439,171440,171441,171442,171443,171444,171445,171446,171447,171448,171449,171450,171451,171452,171453,171454,171455,171456,171457,171458,171459,171460,171462,171461,171463,171464,171465,171466,171467,171468,171469,171470,171471,171472,171474,171475,171476,171477,171478,171479,171480,171481,171482,171483,171484,171485,171486,171488,171489,171491,171492,171493,171494,171495,171496,171497,171498,171500,171501,171502,171503,171504,171505,171506,171507,171508,171509,171510,171511,171512,171513,171514,171515,171516,171517,171518,171520,171521,171522,171523,171524,171525,171526,171527,171529,171530,171531,171532,171533,171538,171539,171540,171541,171542,171543,171544,171545,171548,171549,171550,171552,171553,171554,171555,171556,171557,171558,171559,171560,171561,171562,171563,171565,171566,171567,171568,171569,171570,171571,171572,171573,171574,171576,171577,171578,171579,171580,171581,171582,171583,171584,171585,171586,171587,171588,171589,171590,171591,171592,171593,171594,171595,171596,171597,171598,171599,171600,171601,171602,171603,171604,171605,171606,171607,171608,171609,171610,171611,171612,171613,171614,171615,171616,171617,171618,171619,171623,171624,171625,171626,171627,171628,171629,171630,171631,171632,171633,171634,171635,171674,171675,171636,171637,171638,171639,171640,171641,171642,171643,171644,171645,171646,171647,171658,171659,171660,171662,171663,171664,171665,171666,171667,171668,171669,171670,171671,171672,171673,171676,171678,171679,171680,171681,171682,171694,171695,171696,171697,171698,171699,171700,171701,171702,171707,171708,171709,171710,171711,171712,171713,171714,171715,171716,171717,171718,171719,171721,171722,171723,171724,171725,171726,171727,171730,171731,171732,171733,171734,171735,171736,171738,171739,171740,171741,171742,171743,171745,171746,171747,171748,171749,171751,171752,171753,171754,171755,171756,171757,171758,171759,171760,171761,171763,171764,171765,171766,171767,171769,171770,171772,171773,171775,171776,171777,171778,171779,171780,171781,171782,171783,171784,171785,171786,171787,171788,171789,171790,171791,171792,171793,171794,171795,171796,171797,171798,171799,171800,171801,171802,171803,171804,171805,171806,171808,171809,171810,171812,171813,171814,171815,171816,171817,171818,171819,171820,171821,171822,171823,171824,171825,171826,171827,171828,171829,171830,171831,171832,171833,171834,171835,171836,171837,171838,171839,171840,171841,171842,171843,171844,171845,171846,171847,171848,171849,171850,171851,171852,171853,171854,171855,171856,171857,171858,171859,171860,171861,171862,171863,171864,171867,171868,171869,171870,171872,171873,171874,171875,171876,171877,171878,171879,171880,171881,171882,171885,171886,171887,171888,171889,171890,171891,171892,171893,171894,171895,171896,171897,171898,171899,171900,171901,171902,171903,171904,171905,171906,171907,171911,171912,171913,171914,171915,171917,171918,171919,171923,171924,171925,171926,171927,171928,171929,171930,171931,171932,171933,171934,171935,171936,171937,171938,171939,171940,171941,171942,171943,171944,171945,171946,171947,171948,171949,171950,171951,171952,171954,171955,171956,171958,171959,171960,171961,171962,171964,171965,171966,171967,171968,171969,171970,171971,171972,171973,171974,171975,171976,171977,171978,171979,171980,171981,171982,171983,171984,171985,171986,171987,171988,171989,171990,171991,171992,171993,171994,171995,171997,171998,171999,172000,172001,172002,172003,172004,172005,172006,172007,172008,172009,172010,172011,172012,172013,172014,172015,172016,172017,172018,172019,172020,172021,172022,172023,172024,172025,172026,172027,172028,172030,172031,172032,172034,172035,172036,172037,172038,172039,172040,172041,172043,172042,172044,172045,172046,172047,172048,172049,172050,172051,172052,172053,172054,172055,172056,172057,172058,172059,172060,172061,172062,172063,172064,171729,171285,171704,171705,171198,171490,171528,171551,171564,171703,171720,171728,171768,171807,171546,171547,171499,171519,171534,171535,171536,171537,171575,171620,171677,171737,171750,171762,171871,171908,171909,171910,171916,171957,171996,172029,171866,171884,171661,171774,171883,171811,171744,171953,171487)
2021-05-28 12:10:42.079 T:23384   DEBUG <general>: Mysql execute: delete from path where strPath='smb://nas.xekm.com/music/Compilations/Singles/'

One song in particular, seems like it was just deleted and re-added, there by losing my iTimesPlayed and userratings: idSong 171693

2021-05-28 12:17:20.925 T:23384   DEBUG <general>: Mysql execute: INSERT INTO song (idSong, dateNew, idAlbum, idPath, strArtistDisp, strTitle, iTrack, iDuration, strReleaseDate, strOrigReleaseDate, iBPM, iBitrate, iSampleRate, iChannels, strDiscSubtitle, strFileName, dateAdded,  strMusicBrainzTrackID, strArtistSort, iTimesPlayed, iStartOffset, iEndOffset, lastplayed, rating, userrating, votes, comment, mood, strReplayGain) VALUES (171693, '2017-04-20 17:29:46', 733, 21350, 'Eric Prydz', 'Pjanoo (Private Edit)', 20, 277, '2011', '2011', 0, 211, 44100, 2,'', '20 Pjanoo (Private Edit).mp3', '2017-04-20 17:29:46' ,NULL,NULL,8,0,0,'2018-09-05 08:41:34', 0.0, 8, 0, 'IndexTrance','', '')
2021-05-28 12:17:20.926 T:23384   DEBUG <general>: Mysql execute: DELETE FROM song_genre WHERE idSong = 171693
2021-05-28 12:17:20.926 T:23384   DEBUG <general>: Mysql execute: INSERT INTO song_genre (idGenre, idSong, iOrder) VALUES(2,171693,0)
2021-05-28 12:17:20.926 T:23384   DEBUG <general>: Mysql execute: UPDATE song SET strGenres = 'Progressive House' WHERE idSong = 171693
2021-05-28 12:17:20.927 T:23384   DEBUG <general>: This query part contains a like, we will double backslash in the next field: SELECT idArtist FROM artist WHERE strArtist LIKE '
2021-05-28 12:17:20.927 T:23384   DEBUG <general>: Mysql execute: replace into song_artist (idArtist, idSong, idRole, strArtist, iOrder) values(1734,171693,1,'Eric Prydz',0)
...
2021-05-28 12:30:03.548 T:23384   DEBUG <general>: Mysql execute: UPDATE song SET idPath = 21728, strArtistDisp = 'Eric Prydz', strGenres = 'Progressive House',  strTitle = 'Pjanoo (Private Edit)', iTrack = 20, iDuration = 277, strReleaseDate = '2011', strOrigReleaseDate = '2011', strDiscSubtitle = '', strFileName = '20 Pjanoo (Private Edit).mp3', iBPM = 0, iBitrate = 211, iSampleRate = 44100, iChannels = 2, dateAdded = '2015-07-16 00:31:45', strMusicBrainzTrackID = NULL, strArtistSort = NULL, iStartOffset = 0, iEndOffset = 0, rating = 0.0, userrating = 0, votes = 0, comment = 'IndexTrance', mood = '', strReplayGain = '' , iTimesPlayed = 0, lastplayed = NULL WHERE idSong = 171693
2021-05-28 12:30:03.548 T:23384   DEBUG <general>: Mysql execute: DELETE FROM song_genre WHERE idSong = 171693
2021-05-28 12:30:03.548 T:23384   DEBUG <general>: Mysql execute: INSERT INTO song_genre (idGenre, idSong, iOrder) VALUES(2,171693,0)
2021-05-28 12:30:03.549 T:23384   DEBUG <general>: Mysql execute: UPDATE song SET strGenres = 'Progressive House' WHERE idSong = 171693
2021-05-28 12:30:03.549 T:23384   DEBUG <general>: This query part contains a like, we will double backslash in the next field: SELECT idArtist FROM artist WHERE strArtist LIKE '
2021-05-28 12:30:03.550 T:23384   DEBUG <general>: Mysql execute: replace into song_artist (idArtist, idSong, idRole, strArtist, iOrder) values(1734,171693,1,'Eric Prydz',0)

I do have a full log, but it's about 85MB (522946 lines) but paste.kodi.tv (rightfully) didn't let me upload it there. Happy to host it elsewhere and post the link here, if you think that will be a help but I can also PM you if you'd like.

*notes*
- This is the first time I've done a music scan since migrating to Kodi v19 from v18.
- This is also the first music scan I've run on Windows. Up until today, I've been using Linux to scan.

Please let me know if there is other info I can give or

[DaveBlake]

Great that you take a backup copy of your db, that means we can dig into this fully. I'm the guy for all things music database related

First thing for you to know is that rather then do a manual csv dump of song data to preserve playcount and rating (or transfer to another fresh library, or transfer when you change location or names of music files) Kodi music library export facility has the option to export "song playback history" - the song data that is transient and changes during Kodi used rather than that which is embedded in the file. This creates an xml file with playcount, lastplayed and userrating plus enough other information to be able to match up the songs on import to a new database. And then, having created a new library, you can just import the xml file of song playback history and it applies the playcount, lastplayed and rating values to the songs.

This is the first time I've done a music scan since migrating to Kodi v19 from v18.

OK, since v19 makes use of more tags than v18 (DISCSUBTITLE, BPM, ORIGINALDATE and RELEASESTATUS) this means that every music file will be rescanned to pick up that data. But that should not result in loss of playcount and user rating, the transient song data should be preserved. Something else is happening here.

As you observe on rescanning records do get deleted, it is the simplest way to allow for all the suble data changes that may have happened if the music file had been edited.  Rescanning is normally because the file itself has changed, although in the case of a first scan after version update it is because Kodi wants to read the additional metadata. But before records are deleted the song playback history and other data is stored in local memory so that it can be written as part of the new record.

Your log snippet shows that happening for song with idSong 171693, a record is inserted into the song table with that ID, playcount = 8,  last played = 2018-09-05 08:41:34 and rating = 8. Nothing wrong with that song, no data lost.

Today I ran a scan and after comparing the results, I noticed I lost a lot of songs that should otherwise have an iTimesPlayed or userrating.

I think I am going to need to see the two lots of data and do my own comparison. If there is an issue then it isn't related to the log evidence that you provided. Keep the log of the scan too, that could be useful. My initial guess would be that between v18 and v19 the path for your music sources is being stored differently, so Kodi is seeing the scan as new music.

Can you upload full database dumps of both before and after databases to the cloud and let me have a link via PM.

[cmsimike]

@DaveBlake As usual, thank you for the reply and the help.

> First thing for you to know is that rather then do a manual csv dump of song data to preserve playcount and rating
I do this only to diff the CSVs, so that I can get a view of data before and after a Kodi music scan. I run this query:
> select strPath, strFileName, strArtists, strTitle, strAlbum, userrating, iTimesPlayed from songview where (iTimesPlayed > 0 or userrating > 0) order by strpath, strFileName;
then save the full result set so that they can be easily diffed to see if any unexpected changes to my music database happen. That's where I noted this problem.

I've PMed you a link to a zip, which contains
- the database dump before and after the scan ('a' for before scan, 'b' after scan)
- two CSVs of the resulting query from above that you can diff to see files go missing (0058.csv = the result of the query above before the scan, 0059.csv after the scan)
- the kodi log from the scan

A few items that jump out at me from the diff of the CSVs:
 01 Lillo (Original Mix).mp3    
 Pjanoo (Florencio Cruz Sax Remix).mp3
 20 Pjanoo (Private Edit).mp3
Are files that, seem to me, have disappeared from the database after the scan.

Additionally, before the scan I had 12887 files with a iTimesPlayed > 0 and 1772 files with a userrating
and after the scan i have 12860 files with a iTimesPlayed > 0 and 1772 files with a 1757

No song at all seems to exist in my database with a file name of `Florencio`.


Please let me know if there's anything else!

[Karellen]

Do you have Library Watchdog add-on installed?

[cmsimike]

Do you have Library Watchdog add-on installed?

I do not.

[DaveBlake]

@cmsimike unfortunately the link you sent me keeps timing out, try a different cloud maybe?

[cmsimike]

@cmsimike unfortunately the link you sent me keeps timing out, try a different cloud maybe?

@DaveBlake Resent - sorry about that.

[DaveBlake]

Data received @cmsimike .
An initial look comparing songs in each database the majority of differences relate to changes in file name. Rescan will see these as  new songs, old entry gets deleted and playcount and userrating lost. But I will dig a bit deeper.

[cmsimike]

Data received @cmsimike .
An initial look comparing songs in each database the majority of differences relate to changes in file name. Rescan will see these as  new songs, old entry gets deleted and playcount and userrating lost. But I will dig a bit deeper.

Thanks! As far as I'm aware, no file names should have changed. I've only added a few new files (which is why I'm trying to scan). So if you're seeing file name changes, can you give an example so I can investigate on my end?

[DaveBlake]

A number of files named xxx(1).mp3 are now  xxx.mp3 e.g.
01. Arty, Nadia Ali & BT - Must Be The Love (Original Mix)(1).mp3 ->  01. Arty, Nadia Ali & BT - Must Be The Love (Original Mix).mp3
01. Arty feat. Maty Noyes - Falling Down (Original Mix)(1).mp3 -> 01. Arty feat. Maty Noyes - Falling Down (Original Mix)(1).mp3

But there are some other odd changes too e.g.
a) has 14 - Time To Break Up.mp3 with song title "What Went Wrong"
while
b) has 16 - What Went Wrong.mp3 song title "What Went Wrong"
folder smb://nas.xekm.com/music/Blink 182/Take Off Your Pants And Jacket/

and some others seem to have a song move to a new files.

A full list of added (start +) and removed (start -) music files can be found here

[cmsimike]

A number of files named xxx(1).mp3 are now  xxx.mp3 e.g.
01. Arty, Nadia Ali & BT - Must Be The Love (Original Mix)(1).mp3 ->  01. Arty, Nadia Ali & BT - Must Be The Love (Original Mix).mp3
01. Arty feat. Maty Noyes - Falling Down (Original Mix)(1).mp3 -> 01. Arty feat. Maty Noyes - Falling Down (Original Mix)(1).mp3

But there are some other odd changes too e.g.
a) has 14 - Time To Break Up.mp3 with song title "What Went Wrong"
while
b) has 16 - What Went Wrong.mp3 song title "What Went Wrong"
in folder smb://nas.xekm.com/music/Blink 182/Take Off Your Pants And Jacket/

and some others seem to have a song move to a new files.

Good find!! Looking at the Arty songs, I do have a few in there that are duplicated and thereby I understand why those would swap around during a scan. I'll do a search in my music library to make sure the any duplicate files like this are found and deleted.

Doing a double check for *Florencio* and it seems like I have a duplicate file in there too, but in another path (which might explain why that song was removed and possibly another one scanned in).
So long story short, seems like my problems are primarily around mismanaged music library, with duplicates.

[DaveBlake]

A full list of added (start +) and removed (start -) music files can be found here

There may be one song with a different playcount for me to look closer at, but I'm not seeing an obvious issue.

[cmsimike]

A full list of added (start +) and removed (start -) music files can be found here

There may be one song with a different playcount for me to look closer at, but I'm not seeing an obvious issue.

The link seems to be redirecting me to the homepage. Can you please repost?
I am thinking I need to write a script that compares what files are currently in my kodi database vs what's on disk, and take action for any files not in the kodi database.

[DaveBlake]

The list of file changes is found here https://paste.kodi.tv/soqelufequ

One way to compare is to use the following query on both databases

Code:

SELECT strPath, strFilename, strTitle, iTimesPlayed, userrating, rating
FROM song JOIN path on path.idPath = song.idPath
ORDER BY strPath, strFilename, strTitle;

Export the results set as csv and diff those.


There are 3 songs with a different playcount, by the lastplayed date I think you have simply played them:
Helsinki Scorchin' (Bart Claessen Remix).m4a
Reflect (Jerome Isma-Ae & Daniel Portman Remix).m4a
Paolo Mojo - 1983 (Eric Prydz Remix).mp3

So your problem is duplicate files in your music collection. Remember the music library is NOT a database of files, it is a database of song, album and artists derived from reading the metadata from music files. It does not need the files to be named specially or located in any specific folder layout. It will edit song records if the "same song" (matching mbid) is found later in the scanning but in a different file.