1

I have the ff. sample queries in this sequence:

  1. START TRANSACTION
  2. UPDATE
  3. INSERT
  4. COMMIT

Query #2 is timing out but I noticed that query #3 is not rolled back.

I am also trying to manually rollback by verifying (querying the table) if the updates went through before I commit. But when I do that, I get an explicit: General error: 2006 MySQL server has gone away error.

My questions:

  1. I have innodb_rollback_on_timeout set to ON, isn't query #3 supposed to be rolled back?

  2. Since I can't even manually roll back, what options do I have?

Note: This is on mariadb-10.4.17

Improve this question

1 Answer 1

Reset to default
1

What is the client? Does it have "auto reconnect" turned on? Turn it off.

What I think is happening:

  1. The UPDATE croaks (in any way) and gets rolled back.
  2. The connection is reestablished, perhaps with autocommit.
  3. The INSERT runs successfully and autocommits.
  4. The COMMIT is silently ignored.

Put error checks after every SQL statement.

Improve this answer
1
  • Client is PHP PDO. I trace the code to find if any auto-reconnection is going on, what I found instead was UPDATE and INSERT was coming from different connections. While I may never find why UPDATE is timing out, I think I found the bigger issue --using transaction with different connections.
    – IMB
    Commented Jan 4, 2021 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.