MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Tim McDaniel Date:March 10 2008 4:39pm
Subject:Re: No database selected error when running mysql_tzinfo_to_sql
View as plain text  
On Mon, 10 Mar 2008, dpgirago@stripped <dpgirago@stripped> wrote: wrote:
> The command worked with this syntax:
>
> root> mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -D mysql -u xxx -p
> xxx

It's easy to know that the command ran without error, but I don't know
how to test it to know that it actually did what was intended.

That is, when I ran
     mysql_tzinfo_to_sql /usr/share/zoneinfo | less
(no "America/") on the archaic version of MySQL we run, the timezone
names were like

     INSERT INTO time_zone_name (Name, Time_zone_id) VALUES
     ('Africa/Abidjan', @time_zone_id);
     ...
     INSERT INTO time_zone_name (Name, Time_zone_id) VALUES
     ('America/Adak', @time_zone_id);
     ...
     INSERT INTO time_zone_name (Name, Time_zone_id) VALUES
     ('US/Central', @time_zone_id);
     ...

But as
     mysql_tzinfo_to_sql /usr/share/zoneinfo/America | less
it used only timezones in America/ and without that leading value,
like
     INSERT INTO time_zone_name (Name, Time_zone_id) VALUES
     ('Adak', @time_zone_id);
Furthermore, the output starts
     TRUNCATE TABLE time_zone;
     TRUNCATE TABLE time_zone_name;
     TRUNCATE TABLE time_zone_transition;
     TRUNCATE TABLE time_zone_transition_type;
so the process wipes out all other time zone information.

So you lose zones like "UTC", "EST5EDT", "US/Central",
"posix/America/Fort_Wayne", and such.
You'll only have city or country names like "Chicago", "Barbados",
"Argentina/Rio_Gallegos", "New_York", and such.

I know little about how timezones are handled in MySQL, but that's why
I personally would stick to using the provided examples.

> Still curious though why the databse needed to be explicitly
> selected now when I don't recall having to do that before.
>
> mysql_tzinfo_to_sql surely must know which db.tables to update...

Run it and look at the output with your favorite pager or text
editor.  In my ancient MySQL, at least, all the table names are
emitted without a database specified, as shown in the output above.

I can see a use for omitting the database name from the output.
Without it, you can load the timezone data into a test database and
examine it there with SQL, without clobbering the system information.

-- 
Tim McDaniel, tmcd@stripped
Thread
No database selected error when running mysql_tzinfo_to_sqldpgirago10 Mar
  • Re: No database selected error when running mysql_tzinfo_to_sqlDaniel Brown10 Mar
    • Re: No database selected error when running mysql_tzinfo_to_sqlTim McDaniel10 Mar
      • Re: No database selected error when running mysql_tzinfo_to_sqlDaniel Brown10 Mar
  • Re: No database selected error when running mysql_tzinfo_to_sqlTim McDaniel10 Mar
Re: No database selected error when running mysql_tzinfo_to_sqldpgirago10 Mar
  • Re: No database selected error when running mysql_tzinfo_to_sqlTim McDaniel10 Mar
Re: No database selected error when running mysql_tzinfo_to_sqldpgirago10 Mar