| Bug #68861 | mysql_tzinfo_to_sql creates bad data | ||
|---|---|---|---|
| Submitted: | 3 Apr 2013 21:57 | Modified: | 9 Jun 2014 23:48 |
| Reporter: | Nigel Pearson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Installing | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Any (OS X 10.8 zoneinfo) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | BSD | ||
[3 Apr 2013 21:57]
Nigel Pearson
[15 Apr 2013 11:55]
MySQL Verification Team
Thank you for the bug report. Verified as described. Darwin pochita 12.3.0 Darwin Kernel Version 12.3.0: Sun Jan 6 22:37:10 PST 2013; root:xnu-2050.22.13~1/RELEASE_X86_64 x86_64 [08:31:47][pochita:]~ miguel$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. 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 38457: Data too long for column 'Abbreviation' at row 1
[20 May 2013 14:53]
Diane Yarbrough
So what is the fix?
[7 Jun 2013 19:08]
Paulo Avelar
I'm having the same bug on mac os. using the latest .dmg download file. Any resolution on this? thanks Paulo
[10 Jun 2013 23:48]
Nigel Pearson
Paulo, the workaround is to put output in a file, and edit bad lines. (April 4 comment here: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html )
[26 Jun 2013 12:09]
Nigel Pearson
Diane, a hacky fix could be something like:
% diff -u mysql-5.5.24/sql/tztime.cc.orig mysql-5.5.24/sql/tztime.cc
--- mysql-5.5.24/sql/tztime.cc.orig 2013-06-26 21:55:23.000000000 +1000
+++ mysql-5.5.24/sql/tztime.cc 2013-06-26 21:54:26.000000000 +1000
@@ -262,7 +262,10 @@
if (ttisp->tt_abbrind > sp->charcnt)
return 1;
}
- for (i= 0; i < sp->charcnt; i++)
+ uint max_zone_size = sp->charcnt;
+ if (max_zone_size > 8)
+ max_zone_size = 8;
+ for (i= 0; i < max_zone_size; i++)
sp->chars[i]= *p++;
sp->chars[i]= '\0'; /* ensure '\0' at end */
for (i= 0; i < sp->leapcnt; i++)
%
which forces the string to fit in the default Abbreviation field length. Even better would be for it, or prepare_tz_info(), to throw away entries that have bad data in them (like here where each of Transition_type_id, Offset and Is_DST are zero)
[22 Oct 2013 0:16]
Michael Pabst
On my Mac (OSX 10.8.5) and MySQL setup (via Homebrew, "Ver 14.14 Distrib 5.6.13"), it looks like there's one offending line in the output of msyql_tzinfo_to_sql – grep for "Local time zone must be set--see zic manual page", an error message I'm guessing output by zic(1) when whoever first built the zone info files, and the system local timezone had not yet been set. Credit where credit is due, I was tipped off by http://bugs.mysql.com/bug.php?id=20545 , which seems to be more or less the same bug. Anyways, as a fix, either convince your project manager to let you use Postgres, or failing that, save the output of mysql_tzinfo_to_sql to a file, comment the INSERT statements for the offending timezone (in my installation, named "Factory") and pipe the edited file into your mysql database. Larry Ellison, you owe me a coke.
[5 Jun 2014 14:21]
Thierry de Villeneuve
My workaround (no brainer) : Remove the SQL_MODEs for the session and let MySQL drop the extra characters from the offending varchars $MYSQL_HOME/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql $MYSQL_HOME/bin/mysql --defaults-file=/etc/my.cnf --user=root --password=xxx mysql < /tmp/mysql_tzinfo_to.sql
[9 Jun 2014 23:48]
Paul DuBois
Noted in 5.7.5 changelog. mysql_tzinfo_to_sql mishandled some values from the abbreviation list (read from the timezone information file) due to failure to account for the the null character appended to the end of the char array.
