0

I have an event that puts NULL instead of the proper value in a field while the requests, ran by hand 1 after the other, work just fine.

The event is ran via MySQL's scheduler every 10 seconds.

Here is my event's code:

BEGIN
DECLARE id INT;
DECLARE reqtype varchar(15);
SELECT t1.id into @id from ticket as t1, ticket_request as t2 , ticket_request as t3 WHERE t2.parent_request_id = t1.id and t3.id=t1.id  and t2.request_type<>t3.request_type order by t1.id desc LIMIT 1;
   IF @id > 0
   THEN
        select request_type into @reqtype from ticket_request where id=@id;
        update ticket_request as t set t.request_type=@reqtype where t.parent_request_id=@id;
   END IF;
END

Now, individually, these requests give:

SELECT t1.id from ticket as t1, ticket_request as t2 , ticket_request as t3 WHERE t2.parent_request_id = t1.id and t3.id=t1.id  and t2.request_type<>t3.request_type order by t1.id desc LIMIT 1;

Outputs 3118

select request_type from ticket_request where id=3118;

Outputs service_request. And...

update ticket_request as t set t.request_type='service_request' where t.parent_request_id=3318;

Gives the desired result. The child's request type is updated with the parent.

When ran into an event, everything is working fine but the update puts a NULL in my t.request_type. The correct child is updated, and only that one... So I know the update is working, at least partially.

Here is the data before

id      request_type        parent_request_id
3118    service_request     null
4556    incident            3118

And the one I'd like to see after

id      request_type        parent_request_id
3118    service_request     null
4556    service_request     3118

Anyone has an idea of what is going wrong?

Marc Arbour
  • 57
  • 7
  • 1
    What event? If it is a `BEFORE INSERT` trigger then the row may not have been written to the database yet. – Kenney Oct 26 '15 at 20:07
  • hi, it is an event ran every 10 seconds by an MySQL event scheduler. I will update the question to clarify. Thanks. – Marc Arbour Oct 26 '15 at 20:08
  • 1
    Why are you declaring variables that you don't use? `@id` is not the same as `id`. – Barmar Oct 26 '15 at 20:21
  • Hmmm. Could you check if the variable `reqtype` is `NULL`during the batch execution? Not that it explains anything, but you could retrieve the `regtype` already in the first `SELECT`... maybe try that as well. – trincot Oct 26 '15 at 20:26
  • Hi Barmar. I am declaring it so I can reuse it down the road with `IF @id > 0` and `t.parent_request_id=@id`. Thanks. – Marc Arbour Oct 26 '15 at 20:26
  • Hi trincot, I have tried select buth id and request_type along in the 1st request but MySQL doesn't let me do multiple into's. – Marc Arbour Oct 26 '15 at 20:27
  • You're declaring `id`, but using `@id` below. They're not the same thing. – Barmar Oct 26 '15 at 20:27
  • See [here](http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference) for what @Barmar said. (Side note: you could `SELECT t3.request_type into @reqtype` in your first query to get rid of the second query; that will save you 8640 queries a day). – Kenney Oct 26 '15 at 20:32
  • 1
    This whole thing could be done in a single `UPDATE+JOIN` query. But I'm having trouble understanding the relationship so I can show the correct query. Can you post some example data and the desired results? – Barmar Oct 26 '15 at 20:33
  • Hello Barmar. I have updated the question as per your suggestion. – Marc Arbour Oct 29 '15 at 17:57

0 Answers0