Timezone offsets >= 10 hours aren't stored properly in MySQL
Submitted by miknight on Wed, 01/16/2008 - 02:18.
| Project: | ProjectPier |
| Version: | 0.8.0.2 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | closed - by issue author |
Description
The timezone columns in the MySQL tables are of the type FLOAT(2,1). However, looking at:
http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html#id3137009
it describes the first parameter to the FLOAT type to be the total number of digits (not the number of digits preceding the decimal point, which I think it was misunderstood to be).
That meant that a timezone offset of 10.0 hours would get stored as 9.9 hours. I have attached a patch changing the data types to FLOAT(3,1).
I'm not sure if we also need to include some kind of migration for people upgrading from 0.8.
| Attachment | Size |
|---|---|
| timezone_fix.patch | 2.94 KB |
I thought we fixed this before releasing 0.8, guess not. Can you also include a patch to the upgrade script to go along with this?
I changed it in db_migration/onion.php but I can't see where else I might need to include it. Is there an upgrade script in the works for 0.8 to 0.9 (or whatever the next version is slated to be) yet?
We've had one person verify it here: http://projectpier.org/node/609#comment-737
Verified on a fresh install. It works fine.
I also tried the SQL request
ALTER TABLE `pp_users` MODIFY `timezone` FLOAT(3, 1) NOT NULL DEFAULT '0.0';ALTER TABLE `pp_companies` MODIFY `timezone` FLOAT(3, 1) NOT NULL DEFAULT '0.0';
after installation done from the repository and it works too.
I suppose that SQL should be included in the update script.
Great, thanks for testing.
Was this committed? what revision?
It doesn't seem to have been included in the 0.8.0.2 release.
Attached is an updated patch and has been committed to the 0.8.x branch in revision 159. Also see r104 for trunk
Automatically closed -- issue fixed for two weeks with no activity.