Mysql - Scheduled Event not Executing Properly

CodeMagician
November 8, 2023
306 views
0 votes
2 Answers

I have a mysql event that is scheduled to run nightly defined as follows:

create definer = myuser@`%` event nightly on schedule
    every '1' DAY
        starts '2023-10-21 00:00:00'
    enable
    do
    call mydb.nightly_script;

Although when I look at the tables that would be modified by the proc, the updates aren’t happening. If I copy the line call mydb.nightly_script; from the event and execute it, I see the proc executes correctly however nothing happens as a result of the event.

I have gone through basic troubleshooting steps. I have verified the event scheduler is ON

SELECT @@event_scheduler

returns

event_scheduler
ON

And, when I look at the EVENTS table in information_schema, it shows that the event has run every night.

SELECT *
FROM information_schema.EVENTS
WHERE EVENT_NAME = 'nightly';

EVENT_CATALOG	EVENT_SCHEMA	EVENT_NAME	DEFINER	TIME_ZONE	EVENT_BODY	EVENT_DEFINITION	EVENT_TYPE	EXECUTE_AT	INTERVAL_VALUE	INTERVAL_FIELD	SQL_MODE	STARTS	ENDS	STATUS	ON_COMPLETION	CREATED	LAST_ALTERED	LAST_EXECUTED	EVENT_COMMENT	ORIGINATOR	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
def	mydb	nightly	myuser@%	SYSTEM	SQL	call mydb.nightly_script	RECURRING	null	1	DAY	STRICT_TRANS_TABLES	2021-10-21 18:08:00	null	ENABLED	NOT PRESERVE	2023-10-05 18:07:45	2023-11-05 00:00:45	2023-11-06 00:00:02		2866802262	utf8mb4	utf8mb4_general_ci	latin1_swedish_ci

Although everything indicates the scheduler is running and the event is firing, the stored procedure either isn’t running or is failing somehow and I’m not sure how to investigate further (it is an Azure DBaaS and I’m not sure how to locate the relevant logs, if they exist at all).

I have attempted a few variations of the call command in the body of the event e.g. verified case of the proc name, fully qualifying the proc using dot notation, including or excluding the parens at the end of the proc (it doesn’t take any parameters), etc.

The definer of both the proc and the event are my user e.g.

CREATE
    definer = myuser@`%` procedure nightly()
BEGIN
...

And my user has the following GRANTS:

SHOW GRANTS FOR myuser

Grants for myuser@%
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO ‘myuser’@’%’ WITH GRANT OPTION

The body of the stored procedure itself looks something like this:

CREATE
    definer = myuser@`%` procedure AutoTagAll()
BEGIN

  /*Clean up any tables that shouldn't be there*/
    drop table if exists jobuserlist;
    drop table if exists pl;
    drop table if exists listIds;
    drop table if exists OldList;
    
    /*Create staging tables */
    
    CREATE TEMPORARY TABLE OldList(
    listId CHAR(36),
    contactId CHAR(36),
    PRIMARY KEY(contactId, listID))
    ;
    CREATE TEMPORARY TABLE jobUserList
    (
        user_id CHAR(36) PRIMARY KEY,
        company_id CHAR(36)
    );
    CREATE INDEX IXN__jobUserList__company_id on jobUserList (company_id);

    CREATE TEMPORARY TABLE pl
    (
        ListId CHAR(36),
        contactId CHAR(36),
        primary key (listId, contactId)
    );

    CREATE TEMPORARY TABLE listIds(
         listID CHAR(36) PRIMARY KEY,
         listName VARCHAR(200)
     );
     
    insert into jobUserList ( user_id, company_id)
    ...


    /*clean up*/
    drop table if exists jobuserlist;
    drop table if exists pl;
    drop table if exists listIds;
    drop table if exists OldList;

END;

Given the event is not functioning correctly, I currently have a bash script running on another machine that is connecting and making the call:

...
#Prepare sql query
SQL_Query='call mydb.nightly'

#mysql command to connect to database

mysql -h$MASTER_DB_HOST -u$MASTER_DB_USER -p$MASTER_DB_PASSWD --ssl_mode=REQUIRED -D$MASTER_DB_NAME <<EOF
$SQL_Query
EOF
echo "script complete."

However, I am unhappy with the security and reliability of this workaround and would prefer to integrate this job into the RDBMS scheduler.

At this point, I’m not sure how to use events for my desired purpose. Is there something I’m missing with events? Is this the wrong approach? Is there something verboten in the stored proc itself (creating temporary tables, performing inserts, updates, and deletes within mydb)?

Thank you, SO community, in advance!

Answers

I am not sure if this will work with your Azure DB but you could try querying the performance_schema:

SELECT * FROM performance_schema.error_log WHERE `DATA` LIKE '%scheduler%';

Failing that, you could add a simple log table and add some logging statements to your proc:

CREATE TABLE nightly_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    ts DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

CREATE
    definer = myuser@`%` procedure AutoTagAll()
BEGIN

    DECLARE _RowCount INT;

    INSERT INTO nightly_log (message) VALUES ('Starting AutoTagAll');

  /*Clean up any tables that shouldn't be there*/
    drop table if exists jobuserlist;
    drop table if exists pl;
    drop table if exists listIds;
    drop table if exists OldList;

    INSERT INTO nightly_log (message) VALUES ('Populating jobUserList...');

    insert into jobUserList ( user_id, company_id)
    select ...

    SET _RowCount = ROW_COUNT();
    INSERT INTO nightly_log (message) VALUES (CONCAT(_RowCount, ' rows inserted into jobUserList'));

    ...

- PratikLad
- November 8, 2023 at 4:37 am
- 0 votes
0
Is there something I’m missing with events? Is this the wrong approach?

I checked your scenario with sample store procedure, and it is working correctly. there are two possible scenarios for which you are facing issue.
- Check all names you are using in ither sorted procedure as table names or the stored procedure name you are calling in Event.
here I tried below code with sample store procedure for every minute:
```
--created sample Stored procedure to insert the rows
DELIMITER //
CREATE PROCEDURE Insertstudent()
BEGIN
    --your code
    insert into student (Id, Name) values (1,'Amy');
END //
    
DELIMITER ;

--created event to insert row on every minute by calling above stored procedure
create event minutelycity
on schedule every '1' minute
   starts '2023-07-11 00:00:00'
enable
do
call mydb.Insertstudent();
```
you can see above procedure name is same while creating and using it in event.

To Verify if the event_scheduler is On – execute the following command:
```
SHOW PROCESSLIST;
```
It’ll output a table/entries, you must look for an entry with User event_scheduler, and Command Daemon:

Inserting row per minute for me:

Is there something verboten in the stored proc itself (creating temporary tables, performing inserts, updates, and deletes within mydb)?

As you are saying when you are calling it outside event it is working properly so check the name of SP you are using in event.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – Scheduled Event not Executing Properly

Answers