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`)
);
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`)
);
drop table if exists msgs;
CREATE TABLE `msgs` (
`text` varchar(80) DEFAULT NULL
);
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;
So autocommit is staying turned on even though setting it off for everyone
(except apparently for event scheduler processes).