SEO

I'm getting an error trying to run this command:

root> mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p
xxx

ERROR 1046 (3D000) at line 1: No database selected

Funny thing is I know I've run this on 2 other servers with identical
software without issue, however I see that I've run yum update on this box
but not on the other two.

/var/log> "Nov 13 14:51:58 Updated: tzdata.noarch 2007h-1.el5"

OS: CentOS 5
Mysql Version: 5.0.22

Google not helping much with this.

Kinda stumped...

--David.

On Mon, Mar 10, 2008 at 11:07 AM, <dpgirago@mdanderson.org> wrote:
> I'm getting an error trying to run this command:
>
> root> mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p
> xxx
>
> ERROR 1046 (3D000) at line 1: No database selected

mysql -D mysql -u xxx -p < mysql_tzinfo_to_sql /usr/share/zoneinfo/America/

The -D flag selects the database `mysql`, which is where the time
zone information belongs. The < redirect reads from the "file"
(which, in this case, is actually a redirected STDOUT) into the
database.

--
</Dan>

Daniel P. Brown
Senior Unix Geek
<? while(1) { $me = $mind--; sleep(86400); } ?>

On Mon, 10 Mar 2008, dpgirago@mdanderson.org wrote:
> I'm getting an error trying to run this command:
>
> root> mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p
> xxx
>
> ERROR 1046 (3D000) at line 1: No database selected

The MySQL 5.0 manual explains mysql_tzinfo_to_sql at
<http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html>
6.0 at <http://dev.mysql.com/doc/refman/6.0/en/time-zone-support.html>
looks to be the same.

Its example line is

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

There's a trailing "mysql", which specifies the "mysql" database.
It also specifies all zoneinfo, not "America/".

<http://dev.mysql.com/doc/refman/6.0/en/mysql-tzinfo-to-sql.html>
describes the 6.0 version in a little more detail. Its examples also
specify the mysql database explicitly and use the whole zoneinfo database.
I know little about the whole situation, so you may well know better
than me, but I'd use the examples provided.

A later example, to see whether tables have been loaded, is

SELECT COUNT(*) FROM mysql.time_zone_name;

> Funny thing is I know I've run this on 2 other servers with
> identical software without issue

Have you tried the SELECT command above to verify? I don't know about
MySQL, but in other databases I've used, it's possible to specify a
default database to use on each session unless another database is
explicitly specified. Brief reading says that ~/.my.cnf can be used
as an options file.

--
Tim McDaniel, tmcd@panix.com

On Mon, 10 Mar 2008, Daniel Brown <parasane@gmail.com> wrote:
> mysql -D mysql -u xxx -p < mysql_tzinfo_to_sql /usr/share/zoneinfo/America/
>
> The -D flag selects the database `mysql`, which is where the time
> zone information belongs. The < redirect reads from the "file"
> (which, in this case, is actually a redirected STDOUT) into the
> database.

That is not legal shell syntax on UNIXy systems (or CMD.EXE, for that
matter). "<" is followed by the input file name, so the command above
would read a file named "mysql_tzinfo_to_sql" in the current
directory. It does not run the mysql_tzinfo_to_sql command. The way
to redirect command output into the input of another command is to use
"|" in the proper way. Please see the on-line man pages that I posted
in my other note a minute ago.

--
Tim McDaniel, tmcd@panix.com

On Mon, Mar 10, 2008 at 11:36 AM, Tim McDaniel <tmcd@panix.com> wrote:
> On Mon, 10 Mar 2008, Daniel Brown <parasane@gmail.com> wrote:
> > mysql -D mysql -u xxx -p < mysql_tzinfo_to_sql /usr/share/zoneinfo/America/
> >
> > The -D flag selects the database `mysql`, which is where the time
> > zone information belongs. The < redirect reads from the "file"
> > (which, in this case, is actually a redirected STDOUT) into the
> > database.
>
> That is not legal shell syntax on UNIXy systems (or CMD.EXE, for that
> matter). "<" is followed by the input file name, so the command above
> would read a file named "mysql_tzinfo_to_sql" in the current
> directory. It does not run the mysql_tzinfo_to_sql command. The way
> to redirect command output into the input of another command is to use
> "|" in the proper way. Please see the on-line man pages that I posted
> in my other note a minute ago.

You're right. I can't find the pipe character on my Treo 700wx's
keyboard, so I meant to show an alternative method (directing it to a
file and directing the file into MySQL), but must've screwed up and
lost train of thought halfway through. Odd.

It should've been as follows:

mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ >
/tmp/time_zone_info.sql
mysql -D mysql -u xxx -p < /tmp/time_zone_info.sql

--
</Dan>

Daniel P. Brown
Senior Unix Geek
<? while(1) { $me = $mind--; sleep(86400); } ?>