Rollback not reverting DB state after using INSERT INTO

UPDATE: I just found the solution - in Edit > Preferences > SQL Execution, un-check the 'New connections use auto commit mode' option. This seems to force a commit after every executed query.

Hi Rhys!

Thanks for reaching out.

Thank you for sharing this with the Community!
An alternative solution may be to make sure you have not clicked the following buttons in Workbench and that they appear like this:

Good luck and please feel free to post another question should you encounter any difficulties. Thank you.

Hope this helps.
Best,
Martin

Yeah I found that I had to execute something like

SET autocommit=0;

Valuable lesson, and luckily it was only with a toy dataset and on a duplicate table!

Hi Andrew!

Thanks for reaching out.

This is indeed an alternative solution, thank you very much for sharing it with the Community.
Also, yes, it is very important to gain a significant amount of practice on fabricated or test datasets prior to start working with real datasets. Particularly when this regards database administrative tasks. Nevertheless, you may encounter similar autocommit issues in future coding in SQL, so it is great to have learnt how things work in this matter.

Hope this helps.
Kind regards,
Martin

None of these suggestions worked for me.  I had to just go back and drop the departments_dup table and then re-create it by inserting the data from the departments table.  I seriously would not suggest any SQL beginners to rely on ROLLBACK to correct their mistakes.  Just imagine if this happened to you on a live database where you couldn't just quickly and easily get everything back the way it was with a couple of short scripts.

A problem I faced Samantha was placing the COMMIT after the UPDATE instead of right after the INSERT. This committed the updated values instead of the original values.

I've tried to work with ROLLBACK several times, but it didn't work fine... and in the same session without exiting Workbench.
I've changed the departments_dup more than one time with a new name and number, and I tried to run ROLLBACK to get things a step backward.... not any of them worked with me!
Check out these pics plz.

My execution history:

The result still:

Its supposed to undo the HR step, but it didn't work as expected.

Hi Samantha, Duncan and Meqdad!

Thank you for contributing to this thread!

Yes, indeed, one must be very, very careful when inserting and removing data from a database. Therefore, one must be very careful when using COMMIT, ROLLBACK, or even SAVEPOINT (as statements).

@Meqdad:
While doing the operations you described, did you press the autocommit toggle button, as shown above? Thank you.

Looking forward to your answer.
Kind regards,
Martin

Hi Martin,

I don't... but when I executed this command:
SET autocommit=0;

It worked well.

Thanks

Hi Meqdad!

Thank you for your answer.
Sharing a screenshot of the autocommit button on your end would have probably clarified the situation better, but what you've done is an alternative solution. Please excuse me for not providing it earlier (while thinking that solving the issue with the autocommit button was a clearer and more secure one).

Kind regards,
Martin

Hi Martin,
Neither Commit or Rollback are working. For the MySQL Workbench, Edit>>Preferences>>SQL Editor>>I unchecked the check box "New connections use auto commit mode".
You can scroll to the bottom of the print and notice the ouput for the times that I pressed the Commit(check) or Rollback(x) buttons the rows affected are always zero rows.
When I perform "SELECT" or "UPDATE" it lists rows affected.

Regards,

Fred Reiss

Hi Frederick!

Thanks for reaching out.

The box you need to untick from the SQL Editor is the one about the Safe Updates.

Please do so, restart the connection and retry using COMMIT and ROLLBACK again.
Basically, it seems you are in control of all the tools that may affect the way COMMIT and ROLLBACK will be working on your end - just make sure you've set up the correct combination!
Hope this helps but please feel free to get back to us should you need further assistance. Thank you.
Best,
Martin