Production Database Protocol

This is a guide to the protocol that should be used when interacting with the production StarExec database from the command line.

You should practice using this protocol on StarDev to make sure you know how to rollback changes before you attempt to interact with the production database.

After starting a connection to the database from the command-line you should ALWAYS run:

SET autocommit=0;

By default any changes you make to the database cannot be reverted because MySQL automatically commits all changes immediately. By setting autocommit=0 you can rollback any mistakes you make. If you make a mistake you can always undo your command with:

ROLLBACK;

Whenever you want to make changes you should use this construct:

START TRANSACTION;
(UPDATE, INSERT, etc.)
COMMIT;

If you make a mistake when you run your (UPDATE, INSERT, etc.) command you can use ROLLBACK to undo changes back to the START TRANSACTION statement.

You have to use the START TRANSACTION...COMMIT construct so that ROLLBACK won't roll back every single change you made during your connection. You should use one START TRANSACTION...COMMIT construct for every SQL statement that will change the database. You will want to check that the correct number of rows were changed in the correct way before running COMMIT. You can use SELECT statements before your commit to make sure that the row(s) were changed in the correct way.