I'm attempting to run these queries to perform a ROLLBACK, and I'm not too sure what I'm doing wrong, but I get a warning:
Some non-transactional changed tables couldn't be rolled back.
After a bit of research I found that the most likely cause for this message is the false assumption that a table is transactional, but is actually not. How does one determine which tables are transactional?
I have to assume that the database I'm using uses rollback because it's in the assignment that I'm given for the class that requires us to use the database.
Tables that use the InnoDB storage engine, or those using the NDB cluster storage engine, support transactions; the other engines do not. (There's a comparison table somewhere in the documentation, but I can't find it right now.)
To check a specific table, use
SHOW CREATE TABLE <tablename>;
which will show you the complete CREATE TABLE
statement, including the ENGINE
clause.
To check which engines are installed in your database, use
SHOW ENGINES;
If you have InnoDB installed but it is not the default engine, you can either specify ENGINE=InnoDB
in the CREATE TABLE
statement or change it later with
ALTER TABLE <tablename> ENGINE = InnoDB;