CODEWITHSUNDEEP
pythonmysqldjangocsvtransactions
Mike
Mike

Reputation: 2604

Django w/ MySQL non-transactional changed tables couldn't be rolled back

Keep getting this warning using a MySQL database:

Some non-transactional changed tables couldn't be rolled back

I'm not sure what it means or if it is even causing a problem but I was hoping someone would be able to fill me in on what this means.

I am taking a CSV file, reading it line-by-line and creating Django objects using get_or_create. After I get the message, when I try to recreate it, I get further into the CSV file before the warning occurs.

I tried reading about this error online but I really don't understand what it means. It would be ideal to figure out whats causing this but if I can't I am wondering if I can suppress the warning because maybe it isn't effect my database negatively.

Upvotes: 5

Views: 2969

Answers (2)

e.thompsy
e.thompsy

Reputation: 1377

For me this error happened after I imported a table from another Django application. The origin DB had all the table engines set to MyISAM and the destination app had all the engines set as InnoDB. When I imported the existing table the engine was changed from InnoBD to MyISAM to match the source. I resolved this using MySQL on the command line like so:

$ mysql -uroot -pPASSWORD
> use MY_DB;
> show table status;
> alter table TABLE_WITH_MYISAM engine=innodb;
> quit;

I had imported 5 tables so I had to do the alter command for each table. The show command above will print out the table names and engine settings for all tables in MY_DB.

I hope this helps solve your issue! Cheers!

Upvotes: 0

nsfyn55
nsfyn55

Reputation: 15353

This happens when you mix transactional and non-transactional tables. Changes to non- transactional tables are not effected by a ROLLBACK statement.

For some reasons this may have happened to you we can turn to the docs:

if you were not deliberately mixing transactional and nontransactional tables within the transaction, the most likely cause for this message is that a table you thought was transactional actually is not. This can happen if you try to create a table using a transactional storage engine that is not supported by your mysqld server (or that was disabled with a startup option). If mysqld does not support a storage engine, it instead creates the table as a MyISAM table, which is nontransactional.

This will effect things negatively if you say have an HTTP request that kicks of a transaction, you make some changes, and you need to rollback. The transactional tables will rollback but the others will not. If a transactional storage engine is a requirement for your software you should consider taking steps to migrate all the relevant tables to the InnoDB engine.

Upvotes: 4

Related Questions