Seems that transaction is not enabled on my MySQL DB
When running start transaction -> updates -> rollback in MySQL script having set autocommit = 0 I do not get any errors, but the updates are not rolled back. I gave the MySQL user ALL permissions and still the same.
Here are some details about the test I'm running.
It uses a simple table, created with: "create table a ( id serial primary key);"
Following is the test script:
delete from a;
set autocommit = 0;
start transaction;
insert into a(id) values(1);
rollback work;
select count(*) from a;
So the script makes sure the table is empty, Then it starts a transaction, insert a row and rollback the insert. As the "insert" was rolled back, the "select" should indicate that table still contains 0 rows.
Running this on PostgreSQL:
$ psql < test1
DELETE 0
START TRANSACTION
INSERT 0 1
ROLLBACK
count
0
This is the expected behavior, 0 rows in the table as the insert was rolled back.
Running the same on my MySQL DB:
$ mysql -u < test1
count(*)
1
Having 1 row following the rollback indicate that the "insert" was NOT rolled back, just as in non-transaction mode.