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
processed
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?
/Andreas Pardeike
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 >
last_poll_time.
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
CURRENT_TIMESTAMP,
`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
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
