MobiLink Fall Back

That's not "fallback", but "fall back" (two words), as in that old saying about changing the clocks twice a year: "spring ahead, fall back".

Here's the question that woke me up in the middle of the night:

What happens to MobiLink when the consolidated database server clock is set back one hour in the autumn, when it automatically changes from Daylight Savings Time back to regular time?

Turns out bad things happen, as described in this Help topic:

Dealing with daylight savings time

Daylight savings time can cause problems in a distributed database system if data is synchronized during the hour that the time changes. In fact, you can lose data. This is only an issue in the autumn when the time goes back and there is a one-hour period that can be ambiguous.

To deal with daylight savings time, you have three possible solutions:

• Ensure that the consolidated database server is using UTC time.

• Turn off daylight savings time on the consolidated database server.

• Shut down for an hour when the time changes.

Who writes this stuff? All three suggestions suck:

• The first suggestion "using UTC time" is too glib. A timestamp is a timestamp, there is no "UTC TIMESTAMP" data type, and having to deal with things like the time_zone_adjustment option seems like overkill when it's not your fault MobiLink's getting confused once a year. Go ahead, explain how this suggestion works in 500 words or less.

• The second suggestion "turn off daylight savings time" is just nasty: all your DEFAULT TIMESTAMP and CURRENT TIMESTAMP values will be wrong for two thirds of the year.

• The last suggestion "don't run any synchronizations during the fall back hour" is the least offensive... but the fall back hour in one time zone might fall (pun intended) in the middle of a business day on the other side of the world.

Those suggestions might not be as bad as "you can lose data", but it's worth looking for a solution with less suckage... a solution that deals with the problem directly by forcing MobiLink to download everything it should.

Let's start with Wikipedia's description of what happens each autumn, using the special case of the United States and Canada as an example:

"... the clock jumps backward from 02:00 DST to 01:00 standard time, repeating that hour, and the day has 25 hours. A digital display of local time does not read 02:00 exactly, but instead jumps from 01:59:59.9 ... backward to 01:00:00.0. ... most of the United States and Canada observe DST from the second Sunday in March to the first Sunday in November ..."

Here's how to demonstrate the Daylight Savings Time changes that will happen in 2009:

1. Set your computer time to 2009-03-08 1:59 AM and watch the time spring ahead to 3:00 AM instead of rolling over to 2:00 AM.

2. Then set it to 2009-11-01 1:59 AM and watch it fall back to 1:00 AM instead of changing to 2:00 AM.

Here's what it looks like on Windows XP just before and after the November 1, 2009 "fall back" at 1:59:59 AM:





Here's the database schema for a SQL Anywhere consolidated database that can be used to demonstrate what happens during the "fall back" hour; a trigger is used to automatically set the t.updated_at timestamp column every time a row is inserted or deleted:

CREATE TABLE t (
   pkey        INTEGER NOT NULL PRIMARY KEY,
   updated_at  TIMESTAMP NOT NULL );

CREATE TRIGGER tt BEFORE INSERT, UPDATE ON t
   REFERENCING NEW AS new_t
   FOR EACH ROW
BEGIN
   SET new_t.updated_at = CURRENT TIMESTAMP;
END;

CALL ml_add_table_script  ( 'v1', 't', 'download_cursor', '
SELECT pkey
  FROM t
 WHERE updated_at >= ?' );

Here's the database schema for the corresponding remote database:

CREATE TABLE t (
   pkey   INTEGER NOT NULL PRIMARY KEY );

CREATE PUBLICATION p (
   TABLE t ( pkey ) );

CREATE SYNCHRONIZATION USER "1" TYPE tcpip;

CREATE SYNCHRONIZATION SUBSCRIPTION TO p FOR "1";

Here are the step-by-step instructions for running the demonstration; three rows are inserted on the consolidated database and three MobiLink synchronizations are run, but because of the intervening time changes only two of the rows are downloaded to the remote database:

-- Step 1: Set the consolidated time to 2009-03-08 1:59 AM. 

-- Step 2: Wait for the time to leap forward to 3:00 AM.

-- Step 3: Set the consolidated time to 2009-11-01 1:55 AM.

-- Step 4: Insert row 1 on the consolidated database.
INSERT t ( pkey ) VALUES ( 1 );
COMMIT;
SELECT * FROM t ORDER BY t.pkey;

-- Step 5: Run dbmlsync.

-- Step 6: Wait for the time to fall back to 1:00 AM.

-- Step 7: Insert row 2 on the consolidated database.
INSERT t ( pkey ) VALUES ( 2 );
COMMIT;
SELECT * FROM t ORDER BY t.pkey;

-- Step 8: Run dbmlsync.

-- Step 7: Insert row 3 on the consolidated database.
INSERT t ( pkey ) VALUES ( 3 );
COMMIT;
SELECT * FROM t ORDER BY t.pkey;

-- Step 9: Run dbmlsync

-- Step 10: Show that row 2 is missing from the remote database.
SELECT * FROM t ORDER BY t.pkey;

pkey
1
3

Here's an alternative solution to the problem that, in my opinion, has slightly less suckage than the three solutions documented earlier:

When a MobiLink synchronization runs during the "fall back hour", and the previous download has also run during the same fall back hour, use the modify_last_download_timestamp script to change the last_download timestamp back to the 1:00 AM starting point of the fall back hour. This may cause some rows to downloaded more than once, but it will not lose any changes made during the two hour period between 1 and 2 AM.

Yes, there are two hours between 1 and 2 AM ... when clock is set back one hour.

Anyway, here's the modify_last_download_timestamp script, plus the stored procedure it calls:

CALL ml_add_connection_script ( 'v1', 'modify_last_download_timestamp', '
CALL ml_modify_last_download_timestamp ( ? )
' );

CREATE PROCEDURE ml_modify_last_download_timestamp (
   INOUT @last_download_timestamp TIMESTAMP )
BEGIN
DECLARE @current_timestamp                 TIMESTAMP;
DECLARE @fall_back_hour_starting_timestamp TIMESTAMP;

SET @current_timestamp = CURRENT TIMESTAMP;

-- If the current timestamp and the last download timestamp 
-- are both within the "fall back hour", set the last download 
-- timestamp back to 1:00 AM to catch all changes made during 
-- the two hour period between 1 and 2 AM.

IF  DOW ( @current_timestamp )   =  1  -- Sunday
AND DAY ( @current_timestamp )   <= 7  -- first Sunday
AND MONTH ( @current_timestamp ) =  11 -- first Sunday in November
AND HOUR ( @current_timestamp )  =  1  -- between 1 and 2 AM
THEN 
   SET @fall_back_hour_starting_timestamp 
      = DATETIME ( STRING ( 
         YEAR ( @current_timestamp ),
         '-11-',
         DAY ( @current_timestamp ),
         ' 01:00:00.000' ) );

   IF @last_download_timestamp
   >= @fall_back_hour_starting_timestamp THEN
      SET @last_download_timestamp
         = @fall_back_hour_starting_timestamp;
      MESSAGE STRING ( 
         'SET @last_download_timestamp = ', 
         @last_download_timestamp ) TO CONSOLE;
   END IF;

END IF;

END; -- ml_modify_last_download_timestamp

Now when you run the 10 steps listed earlier, all the rows are downloaded to the remote database:

-- Step 10: Show that row 2 is missing from the remote database.
SELECT * FROM t ORDER BY t.pkey;

pkey
1
2
3

For the record, here are what the three rows look like on the consolidated database, showing the three out-of-order timestamps in the fall back hour:

pkey,updated_at
1,'2009-11-01 01:55:44.468'
2,'2009-11-01 01:00:02.515'
3,'2009-11-01 01:00:51.703'

---

Did I really wake up in the middle of the night, thinking about this problem? Yes.

Did I get up? No... the only thing that will actually get me out of bed at 3AM is a new episode of Robot Chicken.

Why didn't this problem occur to me earlier, like sometime during the ten years I've been working with MobiLink? Good question. Which means I don't have an answer.

Another good question is this: "Why did I use a trigger to set t.updated_at, instead of DEFAULT TIMESTAMP?"

The answer is left as an exercise for the reader.