test suite aborts with mysql 4.1

[mir@…]

With the test fixtures, the test suite aborts (not a regular failure!) with these messages:

Doctest: modeltests.transactions.models.__test__.API_TESTS ... ok
Doctest: modeltests.validation.models.__test__.API_TESTS ... ok
Error: Database django_test_db couldn't be flushed. Possible reasons:
  * The database isn't running or isn't configured correctly.
  * At least one of the expected database tables doesn't exist.
  * The SQL was invalid.
Hint: Look at the output of 'django-admin.py sqlflush'. That's the SQL this command wasn't able to run.
The full error: (1192, "Can't execute the given command because you have active locked tables or an active transaction")

If I go to the commit before the test fixtures, only the json tests fail (because of the forward reference problem).

I use mysql 4.1.11a-4sarge7 from debian with InnoDB storage.

[James Bennett]

There's a lively debate on the developers list going on right now about the myriad problems InnoDB tables cause for the test fixtures system.

[mir@…]

Replying to ubernostrum:

There's a lively debate on the developers list going on right now about the myriad problems InnoDB tables cause for the test fixtures system.

This ticket is not about failing forward references, so it's unrelated.

[Michael Radziej <mir@…>]

I found the reason for the problem, and it's not within the test framework but in core.management.flush:

Mysql 4.x with InnoDB treats TRUNCATE exactly like a DELETE and needs a COMMIT after it. If the COMMIT is missing and the next statement is another TRUNCATE that removes dependent rows, you get the error message from the ticket description. Take a look at ​the mysql reference docs.

The appended patch adds a COMMIT after each SQL command. It's been tested with mysql-4.1 and postgresql-8.1.

[Michael Radziej <mir@…>]

patch

[Marc Fargas]

The behaviour of MySQL is standard? I mean, requiring a COMMIT after the TRUNCATE? If not, that would need to be fixed in the mysql backend. Anyway, does this happend with newer mysql's? (I don't have mysql :)

[Russell Keith-Magee]

Closing as an effective duplicate of #3615. This is a problem because MySQL InnoDB doesn't do referential integrity properly. If we applied the patch proposed here, it would actually break PostgreSQL (and any other database that does referential integrity properly) because you *need* to run all the flush SQL in a single transaction.

#3615 is pending on a way to make MySQL defer constraint checks properly; this ticket needs exactly the same fix in a different way.