Bugzilla – Bug 13300
Incorrect collate on playlist_track table
Last modified: 2009-10-05 14:25:43 UTC
The playlist_track table seems to have incorrect collate setting after the upgrade with the schema_10_up.sql script. All other tables have collate set to "utf8_general_ci" after execution of the schema_8_up.sql script, but playlist_track is re-created with "utf8_unicode_ci" in the schema_10_up.sql script. The result is that you can't run a SQL statement like: select tracks.* from tracks,playlist_track where tracks.url=playlist_track.track and playlist=229; It gives the error: Error Code : 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' Remember to use the "CONVERT TO CHARACTER SET" when upgrading or alternatively simply drop and re-create the table. Just altering the collate will not give the right result since it doesn't modify the existing columns, just new ones created in the future.
Erland, what are you trying to do with this feature so that we can prioritize it appropriately?
if this doesn't affect regular use or popular plugins, it may not be targeted for 7.4
(In reply to comment #2) > if this doesn't affect regular use or popular plugins, it may not be targeted > for 7.4 I'm trying to retrieve a list of all tracks in a playlist using SQL, this is not possible to do in an effective way as long as this bug exists. I use it in TrackStat, Custom Browse, SQL Playlist and Multi Library plugins. I suppose I could patch the database from inside my plugins, but it feels very very wrong since I might cause you a support headache in the future when you'll need to handle different upgrade situations of the database. Standard playlists seems to work in Squeezebox Server 7.4 because it uses the more ineffective way and run one SQL statement to retrieve all urls in the playlist and one SQL statement per track of the playlist to play or view it. Playing or viewing a 1000 track playlist results in executing 1001 SQL statements in 7.4, I believe the same thing was done in 1 SQL statement in 7.3. Of course, 7.3 had other problems and executed a lot of INSERT statements since current playlist was handled in the database. Is the limitation developer resources ? If it is, would a patch help ?
== Auto-comment from SVN commit #28394 to the slim repo by andy == == https://svn.slimdevices.com/slim?view=revision&revision=28394 == Fixed bug 13300, wrong collation was used on playlist_track table in upgrade script. I'm not going to bother fixing this with a new migration script as this was internal to 7.4. Anyone running 7.4 who needs this fix can do a wipe/rescan
The implemented solution won't work for everyone. Basically, users that have been running a 7.4 nightly release sometime during august will have a different database than other users when they upgrade to the official 7.4 release. If this was the intention when the bug report was corrected by changing existing SQL scripts instead of doing a schema_11_up.sql with a "CONVERT TO CHARACTER SET" statement, you can just mark this as resolved again. I just wanted to make sure you are aware of this since it might cause you a support headache in the future when a 7.4 database might look differently depending on if the user has used the beta or not.
I don't really care to be honest, they can do a wipe and rescan if they are affected by this bug.
I'm sorry but wipe and rescan doesn't work, it doesn't drop the tables it just delete all rows in them. I tried it myself before I posted my last comment.
Hmm, yeah, that's not good. Wipe should really delete them. :( Well the workaround for 7.4 users who need this fix is just to delete your database I guess.
This bug has been marked as fixed in the 7.4.0 release version of SqueezeBox Server! * SqueezeCenter: 28672 * Squeezebox 2 and 3: 130 * Transporter: 80 * Receiver: 65 * Boom: 50 * Controller: 7790 * Radio: 7790 Please see the Release Notes for all the details: http://wiki.slimdevices.com/index.php/Release_Notes If you haven't already, please download and install the new version from http://www.logitechsqueezebox.com/support/download-squeezebox-server.html If you are still experiencing this problem, feel free to reopen the bug with your new comments and we'll have another look.