Bugzilla – Bug 4387
Upgraded database different than clean installed database
Last modified: 2008-12-18 11:12:53 UTC
If I have installed 6.5.0 and then upgrade to 7.0a1 I get a different database than if I do a clean install of 7.0a1. I am using an external MySQL instance but I suspect that this is not the problem. 6.5.0 release ============== CREATE TABLE `tracks` ( `id` int(10) unsigned NOT NULL auto_increment, `url` text NOT NULL, . . . ) ENGINE=InnoDB DEFAULT CHARSET=utf8 6.5.0 release upgraded to 7.0 ============================= CREATE TABLE `tracks` ( `id` int(10) unsigned NOT NULL auto_increment, `url` text character set utf8 NOT NULL, . . . ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 7.0 release clean install ========================= CREATE TABLE `tracks` ( `id` int(10) unsigned NOT NULL auto_increment, `url` text collate utf8_unicode_ci NOT NULL, . . . ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci The differents is the "url" column which don't thet the collate set correctly when upgraded. I think the problem is in SQL/mysql/schema_3_up.sql where it does: ALTER TABLE tracks collate utf8_unicode_ci; It should probably instead do somehing like: ALTER TABLE tracks convert to character set utf8 collate utf8_unicode_ci;
Do you still see this issue?
(In reply to comment #1) > Do you still see this issue? > The behaviour isn't exactly the same, but I still get different databases. If I install a completely new 7.0 or install a completely new 6.5.4 and upgrade it to 7.0 I get the same databases. If I install a completely new 6.5.0 and upgrade it to 7.0 or install a completely new 6.5.0 and upgrade it to 6.5.4 and then to 7.0 and get a different database compared to a clean 7.0 install. The difference is the columns defined as text, in a new 7.0 install these looks like this: `url` text character set utf8 collate utf8_unicode_ci NOT NULL, But in one of the upgrade scenarios based on a 6.5 release I instead get a 7.0 database like this: `url` text NOT NULL, I'm not sure if this will cause any problems, but it doesn't feel good that we need to support two different database definitions when doing future upgrades. I suppose it might cause problems also since different character sets and collate is used in the database depending on if I did a new install or an upgrade. I've checked the tables by issuing the following SQL command from a SQL client: show create table tracks The problem seems to exist in all tables that have text columns, not just the tracks table.
Andy: Do you think that this is a problem?
I don't think it's a problem. If a column doesn't have a specific charset or collation it will use the one for the whole table, so in this case it's going to be utf8 and utf8_unicode_ci either way.
(In reply to comment #4) > I don't think it's a problem. If a column doesn't have a specific charset or > collation it will use the one for the whole table, so in this case it's going > to be utf8 and utf8_unicode_ci either way. > If I remember correctly, the problem I had earlier was that the charset and collate wasn't set in the SlimServer scripts in 6.5 or an earlier release. The result was that you got different values on these depending on if you run on Windows or Linux. Since the collate/charset are only set on the table during upgrade the previous charset/collate on the column level will remain. You need to do a "convert" as described in the bug report description to really convert the existing charset/collate on the columns. The result is that you definitely got different collate/charset's on the columns depending on if you upgraded or did a clean install. I know this because I got problem with it in the TrackStat plugin when I tried to hardcode the charset/collate values to the same as SC7 and later executed SQL statements with relations between tracks.url with the corresponding TrackStat table. Unfortunately I don't remember exactly how to reproduce this exact behaviour and I don't have the time to investigate it at the moment. If you decide not to do anything regarding this in 7.0, you can probably just close this bug report and we can create a new one if it causes problem for someone in the future. I've already solved it in TrackStat so it checks the collate/charset in the tracks table instead of hardcoding it when it created the TrackStat tables in the database.
Marking this one as works for me.
This bug is being closed since it was resolved for a version which is now released! Please download the new version of SqueezeCenter (formerly SlimServer) at http://www.slimdevices.com/su_downloads.html If you are still seeing this bug, please re-open it and we will consider it for a future release.