2021-04-04, 23:36
[Split from https://forum.kodi.tv/showthread.php?tid=360791 for context]
I tried to reproduce the "kodi hangs at the end of the rescan" problem capturing debug logs, with the Mac build that includes the patch, however I couldn't reproduce the problem; I suspect the original issue I ran into is related to my Mac being a laptop and having gone to sleep at some point during the 2-3 hour long re-scan process and this impacted kodi. I did notice while testing the release that Kodi has all kinds of issues when the mac wakes up and needs to be restarted. So let's assume this is a non-issue until it can be reproduced.
I will test the Linux 20.0.alpha build with the path on a VM in a server where laptop suspend is not supported and report back if I find issues.
@DaveBlake RE the top-100-album list, I captured the debug log and sent you a link via PM. It is clearly a problem with the mysql statement specific to matrix; I use an external MySQL (maria db, hosted in debian); a Leia client that hosts MyMusic72 on the SAME external MySQL server, that uses the same dataset that got converted by Matrix to version 82, has no issues with Top-100-albums (just tested it)
The error that the query returns (which I got from the debug log) is:
ERROR 1406 (22001): Data too long for column 'sortString' at row 1
After some investigation I found the problem in the SELECT statement, in the final part:
and this is the solution that seems to work:
I noticed in Kodi's code for the udfNaturalSortFormat that they expect 256 bytes for the title. However it seems that titlesortname in the MySQL DB accepts 256 chars of Unicode, 2 bytes per character. So using the leftmost 128 chars guarantees that the title will fit, and I could confirm the SELECT query no longer errors out, the SELECT query works. It seems that anyone having long album titles and using external MySQL for the music DB will run into this problem.
Let me know if you prefer that I open a Github issue or another thread there, for the top-100-album issue or we can continue discussing here, it would be nice to have the fix included in 19.1.
I am able to test variations of the SELECT command if needed. Note that posting an entire SELECT query here (I tried) triggered a cloudflare threat alarm, some sort of protection of this forum against sql injection (I guess)
I tried to reproduce the "kodi hangs at the end of the rescan" problem capturing debug logs, with the Mac build that includes the patch, however I couldn't reproduce the problem; I suspect the original issue I ran into is related to my Mac being a laptop and having gone to sleep at some point during the 2-3 hour long re-scan process and this impacted kodi. I did notice while testing the release that Kodi has all kinds of issues when the mac wakes up and needs to be restarted. So let's assume this is a non-issue until it can be reproduced.
I will test the Linux 20.0.alpha build with the path on a VM in a server where laptop suspend is not supported and report back if I find issues.
@DaveBlake RE the top-100-album list, I captured the debug log and sent you a link via PM. It is clearly a problem with the mysql statement specific to matrix; I use an external MySQL (maria db, hosted in debian); a Leia client that hosts MyMusic72 on the SAME external MySQL server, that uses the same dataset that got converted by Matrix to version 82, has no issues with Top-100-albums (just tested it)
The error that the query returns (which I got from the debug log) is:
ERROR 1406 (22001): Data too long for column 'sortString' at row 1
After some investigation I found the problem in the SELECT statement, in the final part:
Code:
... ORDER BY albumview.iTimesPlayed DESC, udfNaturalSortFormat(titlesortname, 8, '.') DESC, albumview.idAlbum DESC LIMIT 100;
and this is the solution that seems to work:
Code:
... ORDER BY albumview.iTimesPlayed DESC, udfNaturalSortFormat(LEFT(titlesortname,128), 8, '.') DESC, albumview.idAlbum DESC LIMIT 100;
I noticed in Kodi's code for the udfNaturalSortFormat that they expect 256 bytes for the title. However it seems that titlesortname in the MySQL DB accepts 256 chars of Unicode, 2 bytes per character. So using the leftmost 128 chars guarantees that the title will fit, and I could confirm the SELECT query no longer errors out, the SELECT query works. It seems that anyone having long album titles and using external MySQL for the music DB will run into this problem.
Let me know if you prefer that I open a Github issue or another thread there, for the top-100-album issue or we can continue discussing here, it would be nice to have the fix included in 19.1.
I am able to test variations of the SELECT command if needed. Note that posting an entire SELECT query here (I tried) triggered a cloudflare threat alarm, some sort of protection of this forum against sql injection (I guess)