SQL
Hello!

Is there any way to inform mysql client application about changing in
the some table.

One process puts periodically record into the table.
Second process waits new records.
It is very expensive way to do SELECT from this table each second.
Is there any other right way to inform second process?

Best regards.
_______________________________________
Andrey Kotrekhov kota@alkar.net
ISP Alkar Teleport
ÔÅÌ. +380 562 34-00-44

Hi Andrey.

Andrey Kotrekhov wrote:
> SQL
> Hello!
>
> Is there any way to inform mysql client application about changing in
> the some table.
>
> One process puts periodically record into the table.
> Second process waits new records.
> It is very expensive way to do SELECT from this table each second.
> Is there any other right way to inform second process?

I was waiting to see if anyone else had any ideas, because I don't think my ideas are
very good. I should say that I think this is generally a hard problem to solve with
generic SQL, but I think some systems do support their own extensions to provide
signalling or notification.

In MySQL, the two things I can think of are:

1) make the polling more efficient. Instead of polling the table you're watching, have
the inserting process set a flag in another, smaller table, and poll that table.
Another thing you can do to make polling more efficient, when it is necessary, is use a
variable wait time. Exponential or Fibonacci has worked well for me. For more on
this, see
http://www.xaprb.com/blog/2006/05/04...ling-interval/

2) Take advantage of things that use semaphores under the covers. The most obvious one
for me is a string lock with GET_LOCK(). The waiting process and the polling process
could get and release the lock to signal each other. Be careful though, as if you use
more than one lock, there is a potential for deadlock, and if you don't there is a
potential for race conditions.

I have not implemented this myself, and think I would probably write something buggy on
my first attempt, so these are just crude ideas.

Cheers
Baron

Hi Andrey,
> Andrey Kotrekhov wrote:
>> SQL
>> Hello!
>>
>> Is there any way to inform mysql client application about changing in
>> the some table.
>>
>> One process puts periodically record into the table.
>> Second process waits new records.
>> It is very expensive way to do SELECT from this table each second.
>> Is there any other right way to inform second process?
>

Have you had a look at streamsql already [ http://www.streamsql.org ] ?
It does precisely what you want, it's just not mysql and therefore of
course not that well suited for a couple of other db aspects.

Cheers,
Ulrich.

On 6/13/07, Ulrich Staudinger <us@activestocks.de> wrote:
> Hi Andrey,
> > Andrey Kotrekhov wrote:
> >> SQL
> >> Hello!
> >>
> >> Is there any way to inform mysql client application about changing in
> >> the some table.

There are triggers of course which could be used to track any specific
DML event. Depending on what the second process is supposed to do,
one might implement that second process as a server-side procedure.
Failing that, the trigger event *could* be made to launch a system
script, not that it is a course I would recommend lightly. None of
these will signal the client directly, as per mysql protocol, but an
external signal could be brought to bear.

--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.

Hi!
Baron Schwartz wrote:

The situation is more complicated.
I have 2 servers and ring replication between them.
On 1 server there is a process which cashes some data from DB (this
decrease number of queries very match and increase performance).
But sometimes there are some modification of data on 2-d server. Changes
go to the 1-st server by replication.
And only then replication of this changes is done the cashes of data
must be updated
That is why I need the condition of changing some tables on 1-st table.

> Hi Andrey.
>
> Andrey Kotrekhov wrote:
>
>> SQL
>> Hello!
>>
>> Is there any way to inform mysql client application about changing in
>> the some table.
>>
>> One process puts periodically record into the table.
>> Second process waits new records.
>> It is very expensive way to do SELECT from this table each second.
>> Is there any other right way to inform second process?
>
>
> I was waiting to see if anyone else had any ideas, because I don't
> think my ideas are very good. I should say that I think this is
> generally a hard problem to solve with generic SQL, but I think some
> systems do support their own extensions to provide signalling or
> notification.
>
> In MySQL, the two things I can think of are:
>
> 1) make the polling more efficient. Instead of polling the table
> you're watching, have the inserting process set a flag in another,
> smaller table, and poll that table. Another thing you can do to make
> polling more efficient, when it is necessary, is use a variable wait
> time. Exponential or Fibonacci has worked well for me. For more on
> this, see
> http://www.xaprb.com/blog/2006/05/04...ling-interval/
>
>
> 2) Take advantage of things that use semaphores under the covers. The
> most obvious one for me is a string lock with GET_LOCK(). The waiting
> process and the polling process could get and release the lock to
> signal each other. Be careful though, as if you use more than one
> lock, there is a potential for deadlock, and if you don't there is a
> potential for race conditions.
>
> I have not implemented this myself, and think I would probably write
> something buggy on my first attempt, so these are just crude ideas.
>
> Cheers
> Baron



--
Andrey Kotrekhov kota@alkar.net