Reader Level:
Article

MySQL Transactions and Events

By Arjun Panwar on Nov 18, 2011
In this article we will learn MySQL commands without executeing in the events body when autocommit is turnoff.

In this case  MySQL commands without executes in the body of the events and  transaction not also inside in the body.


First off all i have turned off autocommit with the following command :

MySQL> SET GLOBAL init_connect='SET autocommit=0';

Step 1: First of all we have to create tables in our database.

CREATE TABLE info (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(60) DEFAULT NULL,
PRIMARY KEY (`id`)
);


my-sql-info-1.gif

CREATE Table info_1 (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(60) DEFAULT NULL,
`organisation` int(6) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fky_site_org` (`organisation`),
CONSTRAINT `fky_site_org` FOREIGN KEY (`organisation`) REFERENCES info (`id`)
);


my-sql-info.gif


drop table if exists msgs;
CREATE TABLE `msgs` (
`text` varchar(80) DEFAULT NULL
);


my-sql-msgs.gif

Step 2 : In this step i have to turn on the event scheduler do the following syntax:

 set @@global.event_scheduler = on;

  set @@autocommit = 0;


Step 3 : In the this step we have to Create the debugging procedure and table for checking effects is working  properly.
 
delimiter //

drop procedure if exists logix //

create procedure logix (in x varchar(80))
begin
Insert into msgs (text) value (x);
end //

drop event if exists test_scheduler_event;

create definer = current_user event test_scheduler_event
on schedule every 1 minute
on completion preserve
do
begin
call logix (concat(now(),' Test begun ', @@autocommit));

Insert into info (text) values ('The quick brown fox');

Insert into info_1 (text) values ('Hello');

call logix (concat(now(),' Test end'));

end //


commit //

delimiter ;

Output:
Now I find that the contents of the msgs table after running (from a non-administrative user) is:

mysql> select * from msgs;


my-sql-out-put.gif

So  autocommit is staying turned on even though setting it off for everyone (except apparently for event scheduler processes). 

Arjun Panwar

Arjun Panwar is a software developer working on Windows 8 platform and metro style apps.

COMMENT USING