From: Andreas Pardeike (apfsys.se)
Date: Thu Apr 02 2009 - 06:04:05 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]