House Bonita

This posting has nothing to do with SQL Anywhere, unless you're looking for a really nice place to spend a working holiday, complete with high speed internet and a comfy workstation overlooking a canal.



House Bonita is a 2500 square foot, 4 bedroom, 3.5 bathroom house with lanai and heated pool on Woonsocket Canal in Cape Coral, Florida, available for weekly rental via VacationHit.com (click on "Cape Coral", then scroll down to "House Bonita").

It's only been on the rental market for one week, but already the months of February and March 2009 are booked, as well as part of December and most of April. Vacation rental houses in Cape Coral are a great bargain, much nicer and much cheaper than hotel rooms.



House Bonita is the result of a four-year search for a house in south-west Florida. The day Google Maps went live, I was working in Miami and used Google Maps to scroll across Florida from east to west, and then up the Gulf coast until I noticed a town with an unusual feature: hundreds of miles of canals providing water access to the Gulf of Mexico without requiring the purchase of actual shorefront property. That was Cape Coral, and that personal discovery marked the beginning of a search that has resulted in Bonita House.

Four years is a long time, but I had to wait for The Housing Bubble to come and go. Back in February of this year I posted Great Moments In History: Housing Bubble where I wrote that the bubble had finally burst (fifty-percent drops in asking prices kinda gave that away :)

Has the housing market hit bottom in Cape Coral? Maybe, maybe not, but by this fall prices had dropped far enough to qualify as "good deals". Especially the foreclosure properties... one that cost US$1,000,000 to build in 2004 sold for US$485,000 in 2008.

House Bonita wasn't a foreclosure, however, it was move-in ready. Complete with a dock and a 20,000 pound boatlift so you can park your sailboat or yacht... speaking of which, there are no bridges or other obstacles between House Bonita and the Gulf, and yes, manatee do swim in the canals.

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.

Running WordPress With SQL Anywhere

WordPress is a popular open source blog publishing PHP application originally written for use with MySQL. Jason Hinsperger, a product manager at Sybase iAnywhere, used WordPress to implement the iAnywhere Solutions blogs (including his own Rows and Columns) but instead of MySQL he used SQL Anywhere 10.

Now Jason has gone public with the "how to" about using SQL Anywhere instead of MySQL:

How To: Running WordPress with SQL Anywhere

SQL Anywhere Articles

Here's a new article that just appeared on the web:

Creating Windows Mobile Databases with SQL Anywhere

The SQL Anywhere product is an entire suite of database applications and servers. It runs on many platforms, including Windows Mobile. Using Visual Studio, programmers can develop database applications that run on a Windows Mobile device.

Here's another one, not exactly new but maybe you missed it at Techwave:

Top 10 Cool New Features In SQL Anywhere 11

SQL Anywhere 11 has a surprisingly large number of new features, especially when you consider the huge number of features introduced only two years ago in SQL Anywhere 10. This article presents a Top 10 list of new features in SQL Anywhere 11 that developers are likely to find most interesting. Some of the features that didn't make the Top 10 list are described as well, including features that might be important to management but aren't necessarily cool as far as developers are concerned. Also included is a short list of features that are more weird than wonderful.

Once again, it's time for the SQL Anywhere Oscars, when folks tune in to see which of their favorite features got picked, and which ones got left out. And to see some weird stuff... it's all here, the Top 10 list of cool new features in SQL Anywhere 11.