Reader Level:
ARTICLE

MySQL Transactions and Events

Posted by Arjun Panwar Articles | Databases & DBA November 18, 2011
In this article we will learn MySQL commands without executeing in the events body when autocommit is turnoff.
  • 0
  • 0
  • 4869

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). 

COMMENT USING