Ch. 6 (Pg. 190) - Step By Step Instructions For How To Load Time Zone Table Info

[phpstuff]

I had a lot of issues getting this to work, so I figured I'd try and save others the same headaches and create simple instructions on how to get this done quickly and easily.  I'm using a Mac so please note in my signature to make sure what I'm running (MAMP) is the same or compatible to what you are running.

 

1. Open Terminal in Mac

 

2. Get to the root by typing:  cd /

 

3. List all the files/folders you have in that directory by typing:  ls

 

4. In the files/folders that appear, you should see the "usr" folder

 

5. Go down to the next folder called share by typing: cd share

 

6. Go down to the final folder called zoneinfo by typing: cd zoneinfo

 

7. Now take a look inside the zoneinfo folder by typing: ls

 

8. You should see all the system time zone info files - you will see something like:

 

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

 

9. All of this is the time zone info for areas around the world, and comes loaded with your Mac's operating system files OSX.  You will notice, if you have the +VERSION file (in bold above) that it isn't a type of time zone.  When you later try to run the script to take all of these time zone info files and load them into the mysql time zone tables, that terminal will catch at least that +VERSION file and say that it is "unable to load .... skipping it."  I'm just showing you now what it will be referring to later on in these steps so that it is clear where everything is coming from.  It may be possible that you don't have this file or any non-time zone files, in which case you may run the script and have no issues.

 

10. Next get back to the root by typing: cd /

 

11. From here you will want to navigate into the proper folder within mysql so that you can run the script from the proper location.  Now jump to the applications folder by typing: cd applications

 

12. Jump to the MAMP folder by typing (directories are usually case sensitive so if it doesn't work check caps): cd MAMP

13. Jump to the Library folder by typing: cd Library

 

14. Jump to the bin folder by typing: cd bin

 

15. Once you are in the bin folder, you will be able to run the script.  Please note that instead of jumping folders/directories one at a time that you could just instead jump directly to the final destination by typing the full path: cd applications/MAMP/Library/bin   I just listed it this way for practice.

 

16.  Now assuming you are still in the bin directory, to run the script type (or copy/paste):

 

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

 

17. Terminal may ask you to type your password, so type it in and hit enter (as you type it, it will be invisible).

 

18. The result should be something like:

 

unknown001ec2f49bea:bin matt$  ./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.

 

19. My terminal actually froze once it listed this as the result.  The +VERSION file can't be moved into the mysql time zone table because it isn't the proper type of file.  When it tries to move the file it gets hung up.  So, you need to either delete the file or move the file to get it out of the way so the process can run its course. 

 

20. To have access to actually move/delete the file, I did some research and it appears you need to give yourself single/super user mode ability (in most cases).  If you do not do this, and you do not have access to move/delete files, you will see the results like I experienced below in red. Notice when you do this without the super user method terminal told me "Permission denied." You can perform the superuser method instead by typing sudo - the full command you'll need to type is below after the stuff in red.  When you type "sudo" it gives you the access to do what you need to do, when you type "mv" that is the command in terminal to move a file, next is the file you want to move, and finally type the path of the destination (where you want the file to be moved to).  I'm not sure how important this +VERSION file is so you can move it back after running the script if you want so that nothing is disturbed.

 

unknown001ec2f49bea:zoneinfo matt$ mv +VERSION ~/Desktop
mv: rename +VERSION to /Users/matt/Desktop/+VERSION: Permission denied

 

unknown001ec2f49bea:zoneinfo matt$ sudo mv +VERSION ~/Desktop/

WARNING: Improper use of the sudo command could lead to data loss
or the deletion of important system files. Please double-check your
typing when using sudo. Type "man sudo" for more information.

To proceed, enter your password, or type Ctrl-C to abort.

Password:

***AT THIS POINT, ONCE I ENTERED MY PASSWORD AND HIT ENTER, THE FILE HAS BEEN MOVED TO THE DESKTOP (IT IS NO LONGER IN THE ZONEINFO FOLDER).
 

 

21. At this point, the +VERSION file is no longer in the zoneinfo folder (its on the desktop or wherever you decided to put it above), and so running the script again with the +VERSION file moved (the file causing the issue and blocking the movement of all the time zone info files to the tables) should finally work.

 

22. Re-run the same script as you did above, making sure to run it in the same directory/folder bin:

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

 

This time the results should be different since that culprit +VERSION file is gone.  You will have to enter your password once again (remember as you type the password it stays invisible for security sake).  See below how the script is cycling through many more files now.  This should be it and everything should work at this point.  I'm not sure how to confirm 100% that all the data has been populated as it should into the tables - feel free to ask some of the others on here how to do that if you feel you need to.

 

unknown001ec2f49bea:bin matt$  ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root 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.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
 

Hopefully this helps you out.  This was a major headache and I'm glad its over!!

[Larry]

Congrats for all the learning and thanks for sharing. Being comfortable in the command line is an important quality in a web developer.

 

Just so you know, you can cd directly where you want to be. You don't have to do it in steps: cd /usr/local/share or cd /Applications/MAMP/Library/bin 

 

You should also get familiar with using ls -al, which lists all the files, including hidden ones, along with some properties. 

 

And, on a Mac, if you use open ., it opens the current directory in a Finder window. You can also get to hidden folders using the Finder's Go > Go to Folder command. 

[phpstuff]

Thanks for your help and suggestions above Larry!  I'm glad I ran into this issue now since it forced me to go from never using terminal to starting to get a little more comfortable with it.  I'll try to give back to the forums as much as I can as I really appreciate everyone's help on here.  Thanks again Larry and everyone else who provides help and feedback..

[Larry]

You're quite welcome and thanks again for sharing. Being comfortable in the command line interface is pretty important, so it's good that you've started down that path. Cheers!