Bug #20545 mysql_tzinfo_to_sql Does Not Work With STRICT_ALL_TABLES
Submitted: 19 Jun 2006 17:52 Modified: 6 Jan 2015 16:12
Reporter: imacat . (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.22, 5.1.52 OS:Linux (Linux 2.6.16.14 x86_64)
Assigned to: CPU Architecture:Any

[19 Jun 2006 17:52] imacat .
Description:
Dear all,

    Hi.  This is imacat from Taiwan.  I'm using Debian GNU/Linux Sarge 3.1r2 Linux 2.6.16.14 x86_64, MySQL 5.0.22.  I found that mysql_tzinfo_to_sql does not work with STRICT_ALL_TABLES.

root@rinse:~# mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql
Enter password:
Warning: Unable to load '/usr/share/zoneinfo//Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo//Mideast/Riyadh89' as time zone. Skipping it.
ERROR 1406 (22001) at line 35576: Data too long for column 'Abbreviation' at row 1
root@rinse:~#

    I tried to look into the output of mysql_tzinfo_to_sql, and found entries like:

INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Factory', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;

INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('posix/Factory', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;

INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/Factory', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;

I believe mistakenly putting failed operation into the database (the zic warning) should be the reason.  Even if I turned off STRICT_ALL_TABLES, mysql_tzinfo_to_sql still puts non-sense data into the database.

How to repeat:
In my.cnf:

sql-mode = STRICT_ALL_TABLES

The populate the time zone database:

# mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql
ERROR 1406 (22001) at line 35576: Data too long for column 'Abbreviation' at row 1

Suggested fix:
Fix mysql_tzinfo_to_sql to skip unsuccessfully operation.
[19 Jun 2006 21:15] Sveta Smirnova
I can't repeat it using Linux debx86 2.4.27-1-386 and last 5.0.23 sources
[20 Jun 2006 1:10] imacat .
The zone file that croaks.

Attachment: Factory (application/octet-stream, text), 101 bytes.

[20 Jun 2006 1:24] imacat .
Oh.

    Thank you for looking into this issue.  I have checked again.  It seems that some zone files are themselves malformed.  That message in fact comes from the zone files.  I'll file a bug report on Debian.  But *I suppose that mysql_tzinfo_to_sql can do some sanity check before outputing*.  For example, filtering out time zone abbreviations that contains spaces or are too long.

    I have uploaded the zone file that caused the issue.  This file comes with Debian libc6 package (libc6_2.3.2.ds1-22sarge3_i386.deb).  Copy that file to somewhere:

% mkdir /tmp/zoneinfo
% cp Factory /tmp/zoneinfo/
% mysql_tzinfo_to_sql /tmp/zoneinfo/
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Factory', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;
ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
%
[20 Jun 2006 1:47] imacat .
I have looked into the code again.  It seems that there is a sanity check boundary in sql/tzfile.h:

#define TZ_MAX_CHARS	50	/* Maximum number of abbreviation characters */

    However, in scripts/mysql_create_system_tables.sh:

  c_tztt="$c_tztt CREATE TABLE time_zone_transition_type ("
      ......
  c_tztt="$c_tztt   Abbreviation char(8) DEFAULT '' NOT NULL,"
      ......

    TZ_MAX_CHARS is 50, but the maximum length of Abbreviation is 8.  I guess the sanity check does not work here.
[20 Jun 2006 10:28] Sveta Smirnova
I have same Factory file in my timezone directory and with last 5.0.23 sources I get no error.

If necessary, you can access the source repository and build the latest available version yourself. More information about accessing the source trees is available at http://www.mysql.com/doc/en/Installing_source_tree.html
[20 Jun 2006 18:52] imacat .
Hi.

    After seeing your notes, I have turned for more tests.  I have tested this on the following 3 platforms:

* MySQL 5.0.23, Linux 2.6, Debian Sarge 3.1r2, x86_64, cvs source
* MySQL 5.0.22, Linux 2.6, Debian Sarge 3.1r2, x86_64, source
* MySQL 5.0.22, Linux 2.6, Debian Sarge 3.1r2, x86_64, binary package
* MySQL 5.0.22, Linux 2.6, Debian Sarge 3.1r2, i686, source
* MySQL 5.0.22, Linux 2.6, Fedora Core 2, i686, binary package

    All got the same result.

    The procedure to reproduce this error follows.  

1. In my.cnf

[mysqld]
sql-mode = STRICT_ALL_TABLES

2. Use the zone file Factory from libc6_2.3.2.ds1-22sarge3_i386.deb and run:

% mkdir /tmp/zoneinfo
% cp Factory /tmp/zoneinfo/
% mysql_tzinfo_to_sql /tmp/zoneinfo/
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Factory', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;
ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
% mysql_tzinfo_to_sql /tmp/zoneinfo/ | mysql -u root mysql
Enter password:
ERROR 1406 (22001) at line 8: Data too long for column 'Abbreviation' at row 1

    In fact, if mysql_tzinfo_to_sql produces this line with that zone file "Factory":

INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;

    Then it must fail with STRICT_ALL_TABLES, since Abbreviation is char(8).

    I have also tried to change TZ_MAX_CHARS in sql/tzfile.h from 50 to 8, and the problem is gone.

% mysql_tzinfo_to_sql /tmp/zoneinfo/
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
Warning: Unable to load '/tmp/zoneinfo//Factory' as time zone. Skipping it.
ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
% mysql_tzinfo_to_sql /tmp/zoneinfo/ | mysql -u root mysql
Warning: Unable to load '/tmp/zoneinfo//Factory' as time zone. Skipping it.
Enter password:
%

    Looking into sql/mysql_tzinfo_to_sql.cc I guess this is not the solution since TZ_MAX_CHARS seems to apply to other things than Abbreviation, too.  I may be wrong here.
[20 Jun 2006 20:26] Sveta Smirnova
Sorry for my fault, thank you for the bug report and persistency with which you show me my mistake.
[11 Jun 2009 10:18] Eyal Sorek
Hi,

Does simple alter to the table mysql.time_zone_transition_type to increase the size of the Abbreviation to CHAR(50), before loading the TZ data, will do the trick ?
i.e: alter table mysql.time_zone_transition_type modify Abbreviation CHAR(50);

Thanks,
Eyal Sorek
[11 Jun 2009 10:20] Eyal Sorek
Sorry, don't know if relevant, but I didn't mention my mysql version which is 5.1.32.
[23 Oct 2009 20:36] James Day
If you don't need the Riyadh87, 88 or 89 time zones the easiest workaround for the Debian time zone table is:

mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -uroot --force mysql

You'll still get the warnings and errors but the rest of the time zone information will load.
[30 Mar 2010 11:12] MySQL Verification Team
Widening the column to CHAR(50) to try and load in the long 'Abbreviation' doesn't work, it appears as though the Riyadh* files have other things wrong with them as well. --force is a workaround though.
[7 Nov 2010 12:25] MySQL Verification Team
i doubt strict_all_tables has anything to do with it. the problem is mysql_tzinfo_to_sql fails for some reason in tz_load and it is not telling us why.

[sbester@levovo mysql-advanced-gpl-5.1.52-linux-x86_64-glibc23]$ ./bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > zone.sql
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
[sbester@levovo mysql-advanced-gpl-5.1.52-linux-x86_64-glibc23]$
[7 Nov 2010 12:29] MySQL Verification Team
in valgrind we see a buffer overflow is happening.

--17745-- REDIR: 0x30e1527820 (__strcmp_ssse3) redirected to 0x4a06860 (strcmp)
==17745== Invalid write of size 1
==17745==    at 0x4095DD: tz_load(char const*, st_time_zone_info*, st_mem_root*) (tztime.cc:259)
==17745==    by 0x40A20C: scan_tz_dir(char*) (tztime.cc:2478)
==17745==    by 0x40A19D: scan_tz_dir(char*) (tztime.cc:2469)
==17745==    by 0x40A3C9: main (tztime.cc:2522)
==17745==  Address 0x5652be8 is 0 bytes after a block of size 72 alloc'd
==17745==    at 0x4A0515D: malloc (vg_replace_malloc.c:195)
==17745==    by 0x40B0B7: my_malloc (my_malloc.c:35)
==17745==    by 0x40B86E: alloc_root (my_alloc.c:166)
==17745==    by 0x4092CD: tz_load(char const*, st_time_zone_info*, st_mem_root*) (tztime.cc:210)
==17745==    by 0x40A20C: scan_tz_dir(char*) (tztime.cc:2478)
==17745==    by 0x40A19D: scan_tz_dir(char*) (tztime.cc:2469)
==17745==    by 0x40A3C9: main (tztime.cc:2522)
[18 Nov 2010 11:50] Tatiana Azundris Nuernberg
I don't show the valgrind issue, but the Riyadhs fail the typecnt sanity check at

    if (sp->leapcnt > TZ_MAX_LEAPS ||
        sp->typecnt == 0 || sp->typecnt > TZ_MAX_TYPES ||
        sp->timecnt > TZ_MAX_TIMES ||
        sp->charcnt > TZ_MAX_CHARS ||
        (ttisstdcnt != sp->typecnt && ttisstdcnt != 0) ||
        (ttisgmtcnt != sp->typecnt && ttisgmtcnt != 0))
      return 4;

in tz_load()
[6 Jan 2015 16:12] Paul DuBois
Noted in 5.7.6 changelog.

mysql_tzinfo_to_sql failed in STRICT_ALL_TABLES SQL mode if time zone
tables contained malformed information.