0

We just move to Amazon RDS. Now all the CREATE, UPDATE, and INSERT commands are reverting in the next session. All the tables are InnoDB and query caching is on. What could be the problem?

2 Answers 2

4

Check the autocommit setting. It's possible that it's disabled (set to off) on your RDS instance and now all your uncommitted queries are getting rolled back because you're not explicitly committing.

2
  • 1
    Note that DDL statements are not affected by autocommit. They are statements that implicitly commit. Aug 29, 2021 at 3:34
  • @BillKarwin Fair point, I was just going off what OP said when they mentioned CREATE statements. Thanks for that info. I just updated my answer to correct it.
    – J.D.
    Aug 29, 2021 at 3:37
4

Do not use autocommit=OFF -- it can lead to forgetting to COMMIT, which leads to an automatic ROLLBACK.

Either have autocommit=ON or explicitly have pairs of BEGIN and COMMIT.

(This agrees with J.D.; I just want to add emphasis about this common mistake.)

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.