Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Andreas Pardeike (apfsys.se)
Date: Thu Apr 02 2009 - 06:04:05 CDT
Steve & Scott,
Thanks for the suggestions.
The problem with a timestamp is that it's not fine granular. The
consumer application can record last_poll_time and if it is X then
either of the following will not work:
1) select * from table where tstamp >= X
-> this fails because it will receive rows that we already have
2) select * from table where tstamp > X
-> this fails because if the producer application updates rows
faster than 1 second, several rows can get the same timestamp
and in the worst case, the consumer application will run the
select query in the middle of that updating and thus get only
a partial result. The next select will thus skip the remaining
rows with the same timestamp
3) having a 'processed' boolean column
-> this fails with several consumers that will clear that flag
and thus prevent other consumers to see those rows
I once read that if an auto_increment column is set to NULL then it
will become a new number in the sequence but I was not able to get
this to work.
Any other solutions?
On 2 apr 2009, at 10.11, Scott Haneda wrote:
>> Add a column of type timestamp which, by default, will be updated
>> every time a record is inserted or updated. Then the other
>> applications can simply select records with timestamp >
> My same suggestion as well. I go a far as to have at least, three
> standard fields to any table I make:
> CREATE TABLE IF NOT EXISTS `foobar` (
> `id` int(11) NOT NULL auto_increment,
> `udpated` timestamp NOT NULL default '0000-00-00 00:00:00' on update
> `created` timestamp NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
> You never know when you want to know time and date, and this makes
> it brainless. So any new record you add you will set "created =
> NOW();", outside of that, never worry about the updated, field, it
> gets set automatically any time there is any change to the record.
> I am near certain, there is one small thing to note, which is for
> example, if you "update foobar set something = 'test' where id = 1"
> and something was already at "test", since no real update/change
> happened the time-stamp is not going to get updated.
> * There is a version of mysql 4, that going forward, had a change to
> how `timestamp` was defined. If your old data is in version four,
> and you plan to move to a new version, look out for that issue.
> Thanks for "welcome" :)
> Scott * If you contact me off list replace talklists with scott *
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql