Database not timezone support enabled

I have the same issue. PHP 5.2.5. Shared hosting on Linux.

In mysql, there's a database called mysql that contains (or needs to contain) some timezone-related tables.

To fix this problem, I ran some commands at the command line.

1. Log into your server through SSH Secure Shell or another shell program. The user you log in with needs to have a MySQL account with access to the mysql database as well. For the following example, I was logged in as root, and I'm not sure if it would work otherwise. If you don't have root access you can always try the following, replacing 'root' with your mysql username.
2. Log into MySQL by typing /usr/local/mysql/bin/mysql -p at the command prompt
3. Enter your password
4. At the mysql prompt, run "SELECT COUNT(*) FROM mysql.time_zone_name;"
5. If you get a result of 0 rows, you need to populate the table.
6. type ./mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | /usr/local/mysql/bin/mysql -p -u root mysql
7. A bunch of errors appear, but you can ignore them. Run "SELECT COUNT(*) FROM mysql.time_zone_name;
   " again. You should have more than 500 rows now.
8. Finally, make sure that your Drupal database user has read permissions on the mysql database.

Jastraat -

Thanks a lot for your help! Unfortunately, I don't have shell access to my host. Can the same be accomplished using the phpmyadmin interface?

Thanks

I'm afraid I don't know... This was the first time I had heard of anything like this as well! The sql checks could certainly be done through phpMyAdmin as long as your login can read the mysql database, but I doubt you'd be able to load the timezone tables.

Thanks again, you are right. I was able to do the SQL checks but not load the tables. Maybe someone else can chime in with a different solution.

Thanks for the information, jatraat. If you head over here, you'll find the time_zone tables that you need if you can't get it through ./mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | /usr/local/mysql/bin/mysql -p -u root mysql.

The attached file is a MySQL dump of the insert rows. This does not hold the create table statements as most MySQL database installations already have the mysql.time_zone tables, just not the rows. You have to run this SQL file on the mysql database that has the time_zone tables.

I had this problem too on my dev server. I look and the time_zone_name table is empty, so I downloaded and installed the timezone info from the link on the status page. Still an error, and after a little digging I remembered that the drupal data user only has permissions for the drupal database. When I changed the 'select' permission for that user and the `mysql` table, the error vanished.

But here's the thing - I have clients that are on managed servers, and there won't be access to that mySQL table, which is essential to the date_api_sql.inc test:

      $tables = db_result(db_query("SELECT COUNT(*) FROM mysql.time_zone_name;"));
      // Make sure the function returns a valid value and the data is current.
      if (empty($tables)) {
        return FALSE;
      }

THIS IS A HUGE SECURITY ISSUE! As a matter of course, even when I have the control of the server, I don't want any limited user to have access to the `mysql` table.

In fact, especially since the username and password of the drupal user are stored in plaintext in the settings file, I would limit the permissions as much as possible.

Now, I'm not at all up to speed on this update (that just what I was starting to do), but I think it's only this test that asks for that direct access.

Why not just have the second test, which tries a conversion?:

      $test = db_result(db_query("SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central')"));
      // This will test that the tables are current.
      elseif ($test == '2007-03-11 01:00:00') {
        return TRUE;
      }
      return FALSE;

I tried this on a managed server just in sql calls - I couldn't access the table in the first test but I was able to run the second convert test successfully.

(these tests actually overlap in the version I have - I separated them for clarity).

Is there any other need for privileges outside of the drupal db?

I've been making an assumption that the timezone tables will be installed on actual web hosts, including inexpensive shared hosts. They are not installed on Windows by default, so anyone using Windows as a local development environment will need to install them, but that is very easy to do in Windows (download the files and drop them in the mysql/data/mysql folder). I don't know what MAMP does, if the tables are populated or not.

The other question is the right way to test whether that capability is there. Maybe I've made the test more difficult than it needs to be.

Anyway the goal is that this would not be something your average end user would have to do anything with. I certainly wouldn't expect anyone to try to do something with the tables on a shared host.

So any help figuring out the following would be helpful:

1) Do common, inexpensive, shared hosts have the timezone tables populated so we can count on having them available and use them?

2) What is the best way to test whether they are there?

I just committed change to cvs to simplify the test to the one that worked in #8. If that returns true, you definitely have timezone support. If it returns false, you may have timezone support with tables that are outdated (which would still work but sometimes be off) or you might not have any timezone support. Let's see how this one works.

Besides the issue of whether the tables are there there is the issue of whether the database user has been given select access to them. If one just creates a new user on the MySQL server then this access is not there automatically but has to be explicitly granted.

I just need 'CONVERT_TZ(...)' to work. I don't think any special permission would be needed for that. That is what #8 alluded to above, that I have to get rid of the first test, which might require permissions, which is what I did in my last commit.

Right, exactly my point. The 'CONVERT_TZ(...)' is enough of a test, and requires no special permissions. The first test will always fail just because of permissions on most hosted systems.

And I don't see anything else in your code that should be a problem, at least on first scan.

Thanks.

The changes to the test have been committed, and I also added more explanation to INSTALL.txt about the database timezone.

Please let me know if it turns out that common shared hosts do not have the timezone information loaded in the database, I'm still assuming that they will.

Hi.

Some questions: Just installed the latest Date module and I get the following message on my status report page:

The Date Timezone module requires a database that has timezone support enabled, and your database does not. See MYSQL Timezone Information for more information.

I've read through this whole thread, but there is something fundamental I don't understand. In which database exactly are the timezone tables supposed to be: my drupal database? or some system-wide database that I probably don't have permission for? My site is hosted on bluehost.

The timezone information resides in a system database, not the drupal db.

If you're using the latest date 5.x-2.x-dev version (dated Feb 9), the testing problem described here has been fixed, so the problem is probably in the server config - either the timezone info is outdated or simply not there.

If you're trying to explain things to the host's tech support, just use this info:

The test is a simple SQL call that can be executed at the mySQL command line or in something like phpmyadmin:

SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central');

and the return should be

2007-03-11 01:00:00

Refer them to this:

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

most likely, they will have to run this command with mysql (and perhaps linux) root privileges:

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

or load the timezone data from http://dev.mysql.com/downloads/timezones.html

Just to give a little more info to those who don't know Linux well; checking this on the myriad client systems I manage, I found a Red Hat installation that also had empty timezone tables (mysql 4.1).

There the mysql_tzinfo_to_sql file was not in the path, but was found at:

/usr/local/mysql/bin/mysql_tzinfo_to_sql

and I did NOT need linux root privileges to run it (but still needed mysql rights to the mysql table).

Of course, if you want to test the working of the util BEFORE you pipe it into the mysql system, just run

mysql_tzinfo_to_sql /usr/share/zoneinfo

@Karen: I don't know if Site5 is considered a major host, but I can say that they refused to install the timezone tables when I filed a support ticket with them. I've moved on to a new host who was more than willing to make this change for me though so no problem here for my site. If the new method is faster, then I think it's worth the tradeoff of having to find a host who is willing to configure their database appropriately.

If the new method is faster, then I think it's worth the tradeoff of having to find a host who is willing to configure their database appropriately.

I disagree. And honestly, it's not just because I use site5 for several Drupal sites ;-)

If we want Drupal to grow, we cannot have a feature as important (and basic) as proper date fields depend on finding a specific host. Many potential Drupal users already have hosting. If they hit a wall with Drupal because they cannot use date fields, they'll probably move on to another framework before they go through the trouble and expense of switching hosts.

I'm sorry I don't have the expertise to contribute to fixing this problem, and I appreciate everyone's work on this.

I was hoping that most sites would have the timezone tables installed by default. If that's not the case, I'll have to go back to doing this differently.

Too bad, because it is much better to do this in the database.

I was personally surprised at site5's response since it seems like timezones are considered part of the "default" package. I hate for this module to sacrifice speed for compatibility. Would it be possible to have a module to "emulate" this timezone table (or just fall back to the old-style date handling if the select statement fails)?

P.S. Now that isaac77 mentions it, I do agree that compatibility is very important as well for adoption of Drupal.

@isaac77: Can you double check with one of your Site5 websites to see if your experience is the same as mine? Who knows... maybe I had something misconfigured but I would hate for decisions about this issue to hinge around a single person's report.

I use Dreamhost for several nonprofits (free for 501(c) orgs - see their wiki!), and they support the timezones.

If the speed is much faster, but an alternative could be handled in PHP, maybe the test could set a system variable denoting the preferred method (but will that check eat up the time savings?).

Or, where the function is used, a return error kicks into the slower method (making it even slower for them, I guess).

Hate to slow things down when many will be able able to support this, but of course people should not have to pick a host because of a contributed module, even one as useful as this!

How to solve this problem on XP Pro, running Apache2Triad

For windows, download the timezones table at: http://dev.mysql.com/downloads/timezones.html

Find the directory where your mysql data is stored, and put the contents that zip into the 'mysql' folder (don't know why they didn't write an SQL statement!)

more info at http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

Following rleigh's suggestion I sent my hosting provider (bluehost) a message asking about the timezone databases. Still haven't had a reply. I will update when I do.

Godaddy apparently does not have the timezone tables populated as I get this error using the latest 5.x-2.x-dev version (post Feb 9). After going back and forth trying to explain how to populate the tables to Godaddy's obviously confused support techs, they have told me I need a dedicated server to change the settings I would like myself.

I'll probably see if I can change my organization over to Dreamhost (if they support it) since its free for nonprofits. In the meantime I guess I will go back to the 5.x-1.8 version. Thanks for your continued support of this great module.

I just received a reply to my support request (after prodding the tech support through their live chat):

My request:

I'm trying to use a drupal module that depends on certain timezone tables being present in the system mysql database, not my own account's drupal database. According to a thread on the drupal site, some web hosts have these tables; some do not. Would it be possible for Bluehost to add these tables? Please refer to the following links for details:

http://drupal.org/node/218479#comment-724538

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

Thanks.

Their reply:

Hello,

I apologize but we are unable to accommodate you for what you are requesting, it requires root access and running as a user other than your own.

I can't get much from this response other than that it is negative. Judging from others' reports it probably won't do much good to pursue it much further (although I might try). Would it be possible to put these timezone tables into the drupal database instead? Perhaps someone could even make them into an easy-to-install package?

Well that last response about requiring root access means it takes root access to populate the tables, which it does, but once they are populated it should not take root access to be able to use the timezone conversion function. If they have people sharing a mysql server (which would probably be the case), they only need to populate the shared mysql database, not every single individual database on the system, so that doesn't seem like that much of a burden, but apparently it is.

It sounds like it is just not reasonable to assume that shared hosts will have this capability and know how to get things set up.

Figuring out a way to use the timezone functions when they're available and doing the timezone conversion differently when they're not is just too complex to create and manage. There are too many places in the code that this affects.

And it won't help to put the tables into the Drupal database because the timezone functions we will be using assume they are in that database you don't have access to, they can't be anywhere else.

So I guess we're back to re-writing to code to eliminate relying on those tables.

Thanks everyone for chiming in on this. I was really hoping it would turn out differently. I'm working on a fix (and looking at some of the code that ChrisRL submitted to see if I can use it).

And I need to change the status because this is *not* fixed.

Perhaps a coordinated email campaign might help get more hosting providers to enable timezone support. The squeaky wheel gets the grease, after all. And in the end, I doubt that hosting providers really want to alienate their paying customers.

Here's the (automated?) response I got from GoDaddy this afternoon:

...

Thank you for contacting Online Support. Thank you for your feedback and suggestion. As we highly value your new ideas, I will forward your information to our developers for consideration. Please feel free to e-mail us at suggestions@godaddy.com any time with any further suggestions you have.

Please let us know if we can assist you in any other way.

Best Regards,

Norisa A.
Online Support Representative
Customer Inquiry
...
If you need further assistance with this matter, please reply to this email or contact customer service at (480) 505-8877
...
Thanks,
GoDaddy.com, Inc.

And here's the email that I originally sent them this morning:

Many organizations who currently pay GoDaddy for shared-hosting of their Drupal-managed MySQL-driven websites will soon be upgrading their Drupal Calendar and Date modules. These essential upgrades will finally allow their members to accurately share calendars, coordinate schedules, and organize complex tasks -- throughout many different timezones. These upgrades, however, require that GoDaddy have timezone-support enabled at root level on its MySQL servers. Currently, GoDaddy does not have this simple, built-in MySQL feature enabled on its MySQL servers.

Enabling timezone-support on your MySQL servers would allow all of your MySQL-driven accounts, not just your Drupal-managed accounts, to take advantage of timezone-support's obvious benefits.

Please enable timezone-support on all GoDaddy MySQL servers for all of your shared-hosting accounts.

For information on how this can be easily accomplished, please refer to:
http://drupal.org/node/218479#comment-724538

Sincerely,
...

Based on this and the problems noted at http://drupal.org/node/220663, it appears we cannot count on being able to do timezone conversion in the database in any reliable manner, so I am rolling the Date and Calendar modules back to the code they used to use that uses offsets instead of timezone names.

I still need to roll forward and re-apply patches that came after this change, and then see what other adjustments are needed.