| 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: | |
| 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 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.
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.