Transactions, autocommit and events

[Glenn Reed]

I understand that you cannot use certain commands within triggers and that this includes START TRANSACTION, COMMIT, ROLLBACK, LOCK TABLE, etc but I thought that this must be because the mysql commands to be executed in the body of the event are already inside a transaction. Unfortunately this does not appear to be the case.

I am using version 5.1.30-community on Windows 2000 and using InnoDB tables.

Now I have turned off autocommit by setting the following
MySQL> SET GLOBAL init_connect='SET autocommit=0';

or by setting it in the my.ini file with

init_connect='SET autocommit=0'

I understand that I need to create a non administrative account for this to take effect so I did and then I ran my test script (see below) from this account. There were no obvious errors.


My minimal test case is as follows:


---------------------------------------------------------------------------
drop table if exists test_2;
drop table if exists test_1;

CREATE TABLE test_1 (
`id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(60) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;



CREATE TABLE test_2 (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(60) DEFAULT NULL,
`organisation` smallint(6) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fky_site_org` (`organisation`),
CONSTRAINT `fky_site_org` FOREIGN KEY (`organisation`) REFERENCES test_1 (`id`)
) ENGINE=InnoDB;

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

# To turn on the event scheduler do the following:

-- set @@global.event_scheduler = on;

set @@autocommit = 0;



#------------------------------------------------------------------------
# Create debugging procedure and table for checking stuff is working
# properly
#------------------------------------------------------------------------
delimiter //

drop procedure if exists logit //

create procedure logit (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 logit (concat(now(),' Test begun ', @@autocommit));

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

-- Insert into test_2 (text) values ('Hello');

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

end //


commit //

delimiter ;

---------------------------------------------------------------------------


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

mysql> select * from msgs;
+----------------------------------+
| text |
+----------------------------------+
| 2009-01-20 11:04:59 Test begun 1 |
| 2009-01-20 11:04:59 Test end |
+----------------------------------+

So clearly autocommit is staying turned on even though I am setting it off for everyone (except apparently for event scheduler processes). Does anyone know why this is and how to turn off autocommit for events?

Regards
Glenn.



Edited 1 time(s). Last edit at 01/21/2009 02:12PM by Glenn Reed.