Weird problem : Trigger not executed in background.

------=_Part_14500_30126831.1142336443252
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all.

I am having a wierd problem with triggers and stored procedures.
This is causing database to be in inconsistent state.

1. I have added a trigger to be called AFTER insert on a table (t1), which
adds a row into another table(say t2).
2. Client connects to the table t1 and inserts desired row and closes
connection normally.
3. If client is terminated in between, or more generally : If trigger
operation is not completed before client closes the connection after
inserting row in t1, database goes in inconsistent state : we have a row i=
n
table t1 but not the desired row in t2.

Should not it be atomic: add a row in table t1 and call trigger(which adds
the row to t2), or do nothing.

Please let me know if you need more information to solve the problem.

Can anybody help me out?

Thanks and regards.
Pushya

------=_Part_14500_30126831.1142336443252--

Re: Weird problem : Trigger not executed in background.

Hi,

> 1. I have added a trigger to be called AFTER insert on a table (t1), which
> adds a row into another table(say t2).
> 2. Client connects to the table t1 and inserts desired row and closes
> connection normally.
> 3. If client is terminated in between, or more generally : If trigger
> operation is not completed before client closes the connection after
> inserting row in t1, database goes in inconsistent state : we have a row
> in
> table t1 but not the desired row in t2.

What storage engine is used for tables t1 and t2?

> Should not it be atomic: add a row in table t1 and call trigger(which adds
> the row to t2), or do nothing.

Yes, for InnoDB (or for any storage engines that support transactions, at
least).

> Please let me know if you need more information to solve the problem.

I need a complete test case and version of MySQL server used.

Best regards,
--
Valeriy Kravchuk, Support Engineer
MySQL AB, Kiev, Ukraine, www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Weird problem : Trigger not executed in background.

On Saturday 15 April 2006 01:13, Valeriy Kravchuk wrote:

> What storage engine is used for tables t1 and t2?
The storage engine used was the default one: "MyISAM".

> > 1. I have added a trigger to be called AFTER insert on a table (t1), which
> > adds a row into another table(say t2).
> > 2. Client connects to the table t1 and inserts desired row and closes
> > connection normally.
> > 3. If client is terminated in between, or more generally : If trigger
> >operation is not completed before client closes the connection after
> >inserting row in t1, database goes in inconsistent state : we have a row
> >in
> >table t1 but not the desired row in t2.
> > Should not it be atomic: add a row in table t1 and call trigger(which
> > adds the row to t2), or do nothing.
>

> Yes, for InnoDB (or for any storage engines that support transactions, at
> least).

What does that mean? Is it not atomic for other storage-engines?


> I need a complete test case and version of MySQL server used.
It is : Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 5.0

Test case is this:
1. Add a trigger to be called AFTER insert on a table (t1), which adds a row
into another table(say t2).

( 2. Clients are supposed to connect to the table t1 and inserts desired row )

3. Terminate client, before trigger operation is entirely complete: add wait
or have a very big table which will make sure that trigger operation takes
some time.

Observed behaviour:
If client is terminated in between, or more generally : If trigger
operation is not completed before client closes the connection after
inserting row in t1, database goes in inconsistent state : we have a row
in table t1 but not the desired row in t2.

Best regards,
Pushya

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org