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?
Add a comment
|
2 Answers
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.
-
1Note that DDL statements are not affected by autocommit. They are statements that implicitly commit. Commented Aug 29, 2021 at 3:34
-
@BillKarwin Fair point, I was just going off what OP said when they mentioned
CREATEstatements. Thanks for that info. I just updated my answer to correct it.– J.D.Commented Aug 29, 2021 at 3:37
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.)
answered Aug 27, 2021 at 19:37
