MySQL Forums
Forum List  »  Events (Job Scheduler)

Transactions, autocommit and events
Posted by: Glenn Reed
Date: January 19, 2009 04:38PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Transactions, autocommit and events
13888
January 19, 2009 04:38PM
4245
January 26, 2009 07:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.