0

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.

Sierra
  • 327
  • 4
  • 11

1 Answers1

3

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;
Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28
  • Notice also that the default engine for MySQL is **not** InnoDB if your version is older than 5.5; check here for some options of setting InnoDB as the default engine: http://stackoverflow.com/questions/3050492/how-can-i-set-default-storage-engine-used-by-mysql – andrechalom Apr 06 '16 at 00:44
  • Thank you, this helped me figure out the engine was MyISAM, which kind of sucks. Now I get it. – Sierra Apr 06 '16 at 00:57
  • Oh, by the way your solution was excellent. Both solutions were good to know, of course. :) – Sierra Apr 06 '16 at 01:00