Bug #21208 time_zone_name.`Name` column is not wide enough
Submitted: 21 Jul 2006 9:01 Modified: 5 Oct 2006 14:48
Reporter: Valerii Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Packaging Severity:S2 (Serious)
Version:5.0.22-nt OS:Microsoft Windows (Windows)
Assigned to: Timothy Smith

[21 Jul 2006 9:01] Valerii Kravchuk
Description:
Column `Name` of the time_zone_name table (http://downloads.mysql.com/general/timezone-2006g.zip) is declared as char(21). While when it is created as usual (on Linux, for example), it is:

mysql> show create table time_zone_name\G
*************************** 1. row ***************************
       Table: time_zone_name
Create Table: CREATE TABLE `time_zone_name` (
  `Name` char(64) NOT NULL,
  `Time_zone_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names'
1 row in set (0.00 sec)

It can lead to problems with replication, for example. Valid, different time zone names are becominf duplicates with this table definition.

How to repeat:
Download http://downloads.mysql.com/general/timezone-2006g.zip and check table definition.

Suggested fix:
ALTER TABLE time_zone_name MODIFY `Name` char(64) NOT NULL. Then repack that .zip file :)
[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] Valerii 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.