Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
phpstuff

Ch. 6 - Loading Timezone Data Into Tables - Help Needed

Recommended Posts

Per the sidebar on page 190 (ch. 6) of this book I attempted to populate the tz tables using the sample code provided on the MySQL site:

 

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

 

After that didn't work, I browsed around on this site, Stackoverflow and a few other sites, and cannot figure this out. 

 

Here on this site the closest thing I could find was:

 

http://www.larryullman.com/forums/index.php?/topic/1072-installing-timezones-on-mysql-559-included-with-mamp-on-mac-osx-lion/

 

I used that code and it didn't work - maybe the directory path is off?  I'm not sure...  Xampp is running.  I went to the finder and searched for "zoneinfo" and found nothing... 

 

On Stackoverflow the user said he had to use a Linux box to create the file and then copy it to his Mac, but that doesn't sound right:

 

http://stackoverflow.com/questions/17914284/mysql-convert-tz-command-returns-null

 

Any help or direction here is appreciated...

Share this post


Link to post
Share on other sites

And here are the errors I receive:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near './mysql_tzinfo_to_sql /usr/share/bin/zoneinfo | ./mysql -p -u root mysql' at line 1

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql' at line 1

Share this post


Link to post
Share on other sites

That's the problem...I don't even know where that directory is found. 

 

Where is it found?  Under XAMPP?

 

The only thing I've found remotely close is:

 

Applications / XAMPP / xamppfiles / bin / mysql_tzinfo_to_sql

 

 

I added an empty folder on my desktop called "zoneinfo" and then searched for it with Finder and it came up in the search results..so i'm not sure that anything with that name is on my computer.

 

Supposedly since its Mac OSX it should have it already and should not need that installation package but so far I can't find anything.

Share this post


Link to post
Share on other sites

I just tried to change the timezone and got this below:

#1298 - Unknown or incorrect time zone: 'America/Los_Angeles'
SET time_zone = 'America/Los_Angeles'

and...

 

CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York')

NULL

Share this post


Link to post
Share on other sites

The errors you posted earlier suggest that you're trying to run the time zone importing script while in the mysql client. That's not what you do, you run it from the Terminal, from within the correct directory (where the mysql_tzinfo_to_sql file is found).

Share this post


Link to post
Share on other sites

The file won't be in the /usr/share/zoneinfo folder. That's where the system's time zone information is. The file comes with MySQL. 

Share this post


Link to post
Share on other sites

Do you guys think I should just switch to MAMP?  Is that more common?

 

So, I just went into Terminal, and tried: 

 

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

And got errors:

 

-bash: mysql_tzinfo_to_sql: command not found
-bash: mysql: command not found

 

 

And to Larry's point above, I did navigate into the zoneinfo folder and used "ls" to list the contents and there was nothing except:

 

+VERSION    CST6CDT        Europe        Hongkong    MST        Poland        W-SU
Africa        Canada        Factory        Iceland        MST7MDT        Portugal    WET
America        Chile        GB        Indian        Mexico        ROC        Zulu
Antarctica    Cuba        GB-Eire        Iran        Mideast        ROK        iso3166.tab
Arctic        EET        GMT        Israel        NZ        Singapore    posixrules
Asia        EST        GMT+0        Jamaica        NZ-CHAT        Turkey        zone.tab
Atlantic    EST5EDT        GMT-0        Japan        Navajo        UCT
Australia    Egypt        GMT0        Kwajalein    PRC        US
Brazil        Eire        Greenwich    Libya        PST8PDT        UTC
CET        Etc        HST        MET        Pacific        Universal

 

 

So I can navigate to the zoneinfo folder, but the command "mysql_tzinfo_to_sql" is not working for some reason.

 

Share this post


Link to post
Share on other sites

Ok......so from my post above the file is here:

 

Applications / XAMPP / xamppfiles / bin / mysql_tzinfo_to_sql

 

I navigated via terminal to the bin folder, and attempted to run:

 

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

 

And still got errors:

 

-bash: mysql_tzinfo_to_sql: command not found
-bash: mysql: command not found

unknown001ec2f49bea:/ matt$ cd
unknown001ec2f49bea:~ matt$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
-bash: mysql_tzinfo_to_sql: command not found
-bash: mysql: command not found
unknown001ec2f49bea:~ matt$ pwd
/Users/matt
unknown001ec2f49bea:~ matt$ cd /
unknown001ec2f49bea:/ matt$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
-bash: mysql_tzinfo_to_sql: command not found
-bash: mysql: command not found
unknown001ec2f49bea:/ matt$ cd applications/xampp/bin/
-bash: cd: applications/xampp/bin/: No such file or directory
unknown001ec2f49bea:/ matt$ cd applications
unknown001ec2f49bea:applications matt$ cd xampp
unknown001ec2f49bea:xampp matt$ cd xamppfiles
unknown001ec2f49bea:xamppfiles matt$ cd bin
unknown001ec2f49bea:bin matt$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
-bash: mysql_tzinfo_to_sql: command not found
-bash: mysql: command not found
unknown001ec2f49bea:bin matt$ 

 

Share this post


Link to post
Share on other sites

Ok, so I ran the script slightly different with the ./ and it seemed to work (http://www.larryullman.com/forums/index.php?/topic/1072-installing-timezones-on-mysql-559-included-with-mamp-on-mac-osx-lion/).

 

See below, but after it said "skipping it" terminal froze up.  I'm going to follow a 2 yr old post on here now to remove the +VERSION (http://www.larryullman.com/2011/05/27/utc-and-time-zone-support-in-mysql/), and then go back into terminal and try running it one last time.

 

***Can somebody point me in the right direction as far as how to find the following directory:

 

/usr/share/zoneinfo

 

I can only find "share" which is found here:

 

XAMPP / xamppfiles / share / ( there is no zoneinfo found here )

 

I'm beginning to think I made a very bad decision using XAMPP instead of MAMP.  Can anybody chime in and let me know if I'm now at a point where I need to just start all over and begin again with MAMP?

 

Thanks... 

./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root mysql
Enter password: Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.

Share this post


Link to post
Share on other sites

Neither XAMPP nor MAMP was the problem. It's just a matter of learning how to use the command line interface. In any case, let us know if you have any more questions.

Share this post


Link to post
Share on other sites

Thanks for everyone's help here.

 

Yeah, I think my issue was trying to determine the directory location for certain files as well - appears to be much more support for MAMP vs Xampp for Mac so for a newbie like myself when trying to google for support found little for Xampp.

 

I was breezing along at 100 pages / week and then this loading timezones killed a week of progress...no biggie a learning experience for sure! :)

 

Thanks again.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...