Over the weekend, one of our customers in the USA suffered a mass de-registration of handsets during the daylight saving change. After one registration interval, they all re-registered, but inbound calls failed for one full registration interval of each handset.
The same customer had this happen last year when they were running OpenSIPS. At that time, it was determined by the OpenSIPS developers that the problem was that the "expires" column in the location table (stored in MySQL, with db_mode 3) was of type "datetime", which does not handle daylight saving changes gracefully. They suggested changing the db_mode, but this isn't an option because other software reads the location table in real time to decide which SIP proxy machine the handset is registered to. We discussed changing this column (and last_modified which is also a datetime) to type "timestamp", but our customer switched to Kamailio before we could make a final decision on this.
My question is therefore: can we safely do a MySQL "alter table" command to change these two columns to timestamps so that this problem won't happen again next year?
On 11/4/13 4:47 PM, Alistair Cunningham wrote:
Over the weekend, one of our customers in the USA suffered a mass de-registration of handsets during the daylight saving change. After one registration interval, they all re-registered, but inbound calls failed for one full registration interval of each handset.
The same customer had this happen last year when they were running OpenSIPS. At that time, it was determined by the OpenSIPS developers that the problem was that the "expires" column in the location table (stored in MySQL, with db_mode 3) was of type "datetime", which does not handle daylight saving changes gracefully. They suggested changing the db_mode, but this isn't an option because other software reads the location table in real time to decide which SIP proxy machine the handset is registered to. We discussed changing this column (and last_modified which is also a datetime) to type "timestamp", but our customer switched to Kamailio before we could make a final decision on this.
My question is therefore: can we safely do a MySQL "alter table" command to change these two columns to timestamps so that this problem won't happen again next year?
Do you know if mysql accepts to set a timestamp column with a datetime value? I will look over the code to see if there are potential side effects and eventually some workarounds.
Cheers, Daniel
On 04/11/13 17:25, Daniel-Constantin Mierla wrote:
Do you know if mysql accepts to set a timestamp column with a datetime value? I will look over the code to see if there are potential side effects and eventually some workarounds.
Correct me if I'm wrong, but the SQL format of a datetime value is the same as a timestamp value. Both are YYYY-MM-DD HH:MM:SS. Therefore in the following it doesn't matter whether the '2013-11-04 12:34:56' is a datetime or a timestamp:
mysql> create table test ( a datetime, b timestamp ); Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ( '2013-11-04 12:34:56', '2013-11-04 12:34:56' ); Query OK, 1 row affected (0.00 sec)
mysql> select * from test; +---------------------+---------------------+ | a | b | +---------------------+---------------------+ | 2013-11-04 12:34:56 | 2013-11-04 12:34:56 | +---------------------+---------------------+ 1 row in set (0.00 sec)
On 11/4/13 9:51 PM, Alistair Cunningham wrote:
On 04/11/13 17:25, Daniel-Constantin Mierla wrote:
Do you know if mysql accepts to set a timestamp column with a datetime value? I will look over the code to see if there are potential side effects and eventually some workarounds.
Correct me if I'm wrong, but the SQL format of a datetime value is the same as a timestamp value. Both are YYYY-MM-DD HH:MM:SS. Therefore in the following it doesn't matter whether the '2013-11-04 12:34:56' is a datetime or a timestamp:
mysql> create table test ( a datetime, b timestamp ); Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values ( '2013-11-04 12:34:56', '2013-11-04 12:34:56' ); Query OK, 1 row affected (0.00 sec)
mysql> select * from test; +---------------------+---------------------+ | a | b | +---------------------+---------------------+ | 2013-11-04 12:34:56 | 2013-11-04 12:34:56 | +---------------------+---------------------+ 1 row in set (0.00 sec)
I thought timestamp storing the unix time stamp as seconds. I see that in mysql module, MYSQL_TYPE_TIMESTAMP is considered as integer value (so expects the seconds) and MYSQL_TYPE_DATETIME is stored over a time_t by converting from date-time string.
I would do some basic tests to see if it works - on 32b looks like being ok. On 64b, the time_t is long int, iirc, so the sizes are different -- speaking of these, this mapping has to be reviewed anyhow, I will look closer at it when I get a chance.
Daniel
On 04/11/13 18:18, Daniel-Constantin Mierla wrote:
I thought timestamp storing the unix time stamp as seconds. I see that in mysql module, MYSQL_TYPE_TIMESTAMP is considered as integer value (so expects the seconds) and MYSQL_TYPE_DATETIME is stored over a time_t by converting from date-time string.
Yes, it appears that datetime and timestamp are stored differently internally, but present the same format in SQL commands. That's good news, and suggests we can convert columns between the two at will.
I would do some basic tests to see if it works - on 32b looks like being ok. On 64b, the time_t is long int, iirc, so the sizes are different -- speaking of these, this mapping has to be reviewed anyhow, I will look closer at it when I get a chance.
Thanks!
-
Alistair Cunningham -
Daniel-Constantin Mierla