Bug #21208 | time_zone_name.`Name` column is not wide enough | ||
---|---|---|---|
Submitted: | 21 Jul 2006 9:01 | Modified: | 5 Oct 2006 14:48 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Packaging | Severity: | S2 (Serious) |
Version: | 5.0.22-nt | OS: | Windows (Windows) |
Assigned to: | Timothy Smith | CPU Architecture: | Any |
[21 Jul 2006 9:01]
Valeriy Kravchuk
[23 Aug 2006 17:19]
Timothy Smith
Hi. I can't repeat bug #21208, which was verified. When I unzip the file, I see Name is varchar(64) for MySQL 4.1, 5.0 and 5.1: 11:10 ~/m/bk$ for d in 41 50 51; do mm $d/run; mysql tz2006 -Ee 'show create table time_zone_name'; done Setting MySQL environment to '/home/tim/m/bk/41/run' mysqld is alive *************************** 1. row *************************** Table: time_zone_name Create Table: CREATE TABLE `time_zone_name` ( `Name` char(64) NOT NULL default '', `Time_zone_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Time zone names' Setting MySQL environment to '/home/tim/m/bk/50/run' mysqld is alive *************************** 1. row *************************** Table: time_zone_name Create Table: CREATE TABLE `time_zone_name` ( `Name` char(64) NOT NULL default '', `Time_zone_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Time zone names' Setting MySQL environment to '/home/tim/m/bk/51/run' mysqld is alive *************************** 1. row *************************** Table: time_zone_name Create Table: CREATE TABLE `time_zone_name` ( `Name` char(64) NOT NULL DEFAULT '', `Time_zone_id` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Time zone names' Can you please let me know what's different in your case? I suspect it has to do with character sets; however, when I examine the timezone2004e file, it has the identical CREATE TABLE statement, with latin1 charset. Please provide a full example of how to repeat this bug. Thank you. Timothy
[24 Aug 2006 6:17]
Valeriy Kravchuk
I've just downloaded timezone-2006g.zip again, extracted time_zone_name.* files into test subdirectory of data directory, and executed: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.22-community-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show create table time_zone_name\G *************************** 1. row *************************** Table: time_zone_name Create Table: CREATE TABLE `time_zone_name` ( `Name` char(21) NOT NULL default '', `Time_zone_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names' 1 row in set (0.05 sec) All these were done on almost default installation (wizard-based) of MySQL 5.0.22 on Windows XP. utf8 was used as character set.
[24 Aug 2006 23:16]
Iggy Galarza
I tested this on Windows 5.0.25 $ ./mysql.exe --port 9306 -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.25-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> show tables; Empty set (0.04 sec) mysql> show tables; +---------------------------+ | Tables_in_test | +---------------------------+ | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | +---------------------------+ 5 rows in set (0.00 sec) mysql> show create table time_zone_name; +----------------+-------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------------+ | Table | Create Table | +----------------+-------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------------+ | time_zone_name | CREATE TABLE `time_zone_name` ( `Name` char(64) NOT NULL default '', `Time_zone_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Time zone names' | +----------------+-------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------------------+ 1 row in set (0.02 sec)
[31 Aug 2006 0:19]
Timothy Smith
This problem shows up when the server is started with --character-set-server=utf8. It doesn't show up in the default latin1 setup. I need to do some investigation to see the best way to deal with this. It may be necessary to change the format in which the information is distributed, from binary MyISAM files to an SQL dump which will be imported into the server. Regards, Timothy
[28 Sep 2006 20:31]
Timothy Smith
New timezone-2006g.zip file available at ftp://ftp.mysql.com/pub/mysql/download/timezone-2006g.zip Will be updated on the web site page by the web team. The problem was that the tables were created w/o an explicit character set declaration, so when the server was started with a non-default system character set, the table's definition was "altered". Tables are not created with the UTF-8 character set, like all other mysql.* tables.
[2 Oct 2006 18:05]
Timothy Smith
The timezone-2006g.zip file was updated on Thu, 28 Sep 2006. No changes were made to MySQL server code. The pre-packaged time zone database files were created with an unspecified character set. This caused problems when using the files on a MySQL server with a non-default character set, such as UTF-8. (UTF-8 is used by default with the Windows installer, so this bug affected primarily Windows users.) The new timezone-2006g.zip file's tables explicitly use the UTF-8 character set, as with all other MySQL system tables. These time zone tables will work the same, regardless of what system character set the MySQL server is using.
[5 Oct 2006 14:48]
Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs. In the package of pre-built time zone tables that is available for download at http://dev.mysql.com/downloads/timezones.html, the tables now explicitly use the utf8 character set so that they work the same way regardless of the system character set value.