0

I am running mysql 5.1.41 that came bundled with xampp on windows. I have created an "event" for testing purpose but the problem is that the event doesnt run. The scheduler is ON. I created a table named test_table that has 2 columns : id (auto increment) and test_value (timestamp, default null). This column is supposed to store current timestamp whenever event is fired. So i created an event for this purpose. but the problem is , it does not run. I have checked many database parameters and searched for solution on different forums, but to no avail.

My steps are as under:

Step 1. Test_Table creation statements

CREATE TABLE IF NOT EXISTS `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
  `test_value` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Step 2. Execute following statement to turn scheduler on

set global event_scheduler=on

Step 3. Verify Event_scheduler status

show processlist

Scheduler status

Step 4. Created a test function (routine) to insert current time

DROP FUNCTION `test_func`//
CREATE DEFINER=`pacs`@`localhost` FUNCTION `test_func`(`x` int) RETURNS int(11)
BEGIN
    #Routine body goes here...
INSERT into test_table(test_value) VALUE(CURRENT_TIMESTAMP);
    RETURN 0;
END

Step 5. Executed the function from sql statement, and it worked fine.

Step 6. Created a test event that will call Test_func after every 30 seconds

DROP EVENT `test_event`//
CREATE EVENT `test_event` ON SCHEDULE EVERY 30 SECOND 
STARTS '2018-02-24 22:50:00' ON COMPLETION PRESERVE ENABLE 
DO call test_func(1)

Step 7. Checked event table in mysql schema and found out that although as per last_executed column, it was executed according to UTC, but my test_table did not get any data enter image description here

Step 8. Looking for solutions and assistance

regards.

Fahim Ashraf
  • 93
  • 1
  • 10
  • 2
    `call` is used for a procedure (and you should get an error if you try to do it with a function, which is what happens in your event). A function will e.g. be used with `select test_func(1)` (or the code you used in step 5 to "Executed the function from sql statement, and it worked fine."). If that is what your function does, you should probably use a procedure anyway. – Solarflare Feb 24 '18 at 19:01
  • OMG, such a silly mistake on my part. Yes, you are right. I changed it to select and its working now. Thank you @Solarflare – Fahim Ashraf Feb 24 '18 at 19:32

0 Answers0