Philip Meyer;343440 Wrote: > > I tried running the SQL to change collation, and still got the issue > reported (twice). After pressing the "delete unused statistic" button > and seeing the error log lines, the web UI seems to hang - would it > actually work and complete the purge anyway (left it half an hour and > then killed it but maybe I should be patient?). > I suppose the MySQL process was using a lot of CPU during this time ? Could indicate that something needs to be optimized.
Philip Meyer;343440 Wrote: > > I hunted around for other tables that had differing collations, and > updated those (track_history and another table I think), but still got > the issue reported twice. > TrackStat modifies the collate/character set settings on its tables (track_statistics/track_history) at startup according to the SqueezeCenter tables (tracks). The problem is when the two standard tables "tracks" and "tracks_persisistent" have different collate values, in this case TrackStat converts its table according to the "tracks" table with the result that it can't join against the "tracks_persistent" table. Philip Meyer;343440 Wrote: > > I notice that all of the text columns in the track table have > utf8_unicode_ci collation - is that the problem? > In a configuration that is affected by bug 9423, it will look like this: - tracks_persistent: Table collate "utf8_unicode_ci", column collate "utf8_unicode_ci" - tracks: No collate shown, which means that is, which means that it uses the database default collate which is set to "utf8_general_ci". You can't join text columns which have different collate settings and TrackStat converts it's tables at startup to match "tracks". The result is that TrackStat can't join with tracks_persistent which causes the error messages. In a clean 7.2 database started from a completely new database, you will have: - tracks_persistent: Table collate "utf8_unicode_ci", column collate "utf8_unicode_ci" - tracks: Table collate none (which means utf8_general_ci), column collate "utf8_unicode_ci". The important stuff is the column collate and in this case the column collate in "tracks" and tracks_persistent" matches and you can join the tables. So, modifying the TrackStat tables (track_statistics, track_history) won't help because TrackStat will re-convert them at next startup. So you need to correct the standard SqueezeCenter tables to make it work. Philip Meyer;343440 Wrote: > > I don't use the bundled MySQL instance - I use my own MySQL instance. > I have a script that I use to create the database: > > -- http://wiki.slimdevices.com/index.cgi?MySQL > > drop database squeezecenter; > create database squeezecenter character set utf8; > grant all on squeezecenter.* to <username> identified by '<password>'; > flush privileges; > > I haven't needed to recreate my DB in some time - perhaps I should > trash it and start again? > I'm pretty sure the problem is the new "tracks_persistent" table. So it should be enough to convert it to the same collate as the "url" column in the "tracks" table, something like this: Code: -------------------- alter table tracks_persistent convert to character set utf8 collate utf8_general_ci; -------------------- or if the url column in the tracks table has collate "utf8_unicode_ci" (which I don't think it have): Code: -------------------- alter table tracks_persistent convert to character set utf8 collate utf8_unicode_ci; -------------------- If you really want a clean start, dropping and re-creating the database will also work. Just remember that if you do this you might loose information that will take some time to re-create. As an example if you use the "LastFM" or "Amazon" scanning modules in the Custom Scan plugin, you will need to perform a rescan with these which will take some time. However, a clean start is really the best solution if you like to avoid future problems. At the moment you have a database that looks different than the database of most SqueezeCenter 7.2 users. Your database will might be different even if you run the convert SQL directives I mentioned above, those directives only make sure the collate matches but doesn't make sure they are the same as a clean installation. Philip Meyer;343440 Wrote: > > I'm not sure why there are three tables with data in - I assume one of > either track_statistics or track_persistent isn't needed any more? > track_persistent is a standard SqueezeCenter table that was introduced in 'enhancment #142' (http://bugs.slimdevices.com/show_bug.cgi?id=142), this will eventually make the TrackStat specific track_statistics table obsolete. TrackStat needs to be modified so all its queries is using track_persistent instead of track_statistics, but I don't like to do it until enhancement #142 has been completely implemented. Hopefully this happens before the SqueezeCenter 7.3 relelase, but it has been postponed a few times before. By the way, I just checked the code and if you want the history in track_history to match your new path's, you will have to apply the same change as in saveTrack also to the corresponding section in the addToHistory function in Storage.pm Also, the "Remove All Data" operation in the web interface won't clear tracks_persistent at the moment. The result is that you will need to perform the "Restore" operation directly afterwards because if TrackStat performs refresh operation in-between it will updates its data according to the contents of the tracks_persistent table (if it has the correct collate setting). -- erland Erland Isaksson 'My homepage' (http://erland.isaksson.info) 'My download page' (http://erland.isaksson.info/download) (Developer of 'TrackStat, SQLPlayList, DynamicPlayList, Custom Browse, Custom Scan, Custom Skip, Multi Library and Database Query plugins' (http://wiki.erland.isaksson.info/index.php/Category:SlimServer)) ------------------------------------------------------------------------ erland's Profile: http://forums.slimdevices.com/member.php?userid=3124 View this thread: http://forums.slimdevices.com/showthread.php?t=52937 _______________________________________________ plugins mailing list [email protected] http://lists.slimdevices.com/lists/listinfo/plugins
