A couple of MySQL/MyISAM test failures

[Karen Tracey]

Two tests fail when the Django test suite is run against MySQL with the MyISAM engine:

======================================================================
FAIL: test_initial_sql (regressiontests.initial_sql_regress.tests.InitialSQLTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/kmtracey/django/trunk/tests/regressiontests/initial_sql_regress/tests.py", line 8, in test_initial_sql
    self.assertEqual(Simple.objects.count(), 7)
AssertionError: 0 != 7

======================================================================
FAIL: test_tickets_8921_9188 (regressiontests.queries.tests.Queries6Tests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/kmtracey/django/trunk/tests/regressiontests/queries/tests.py", line 1275, in test_tickets_8921_9188
    ['<Tag: t1>', '<Tag: t3>']
  File "/home/kmtracey/django/trunk/django/test/testcases.py", line 512, in assertQuerysetEqual
    return self.assertEqual(map(transform, qs), values)
AssertionError: Lists differ: ['<Tag: t1>', '<Tag: t2>', '<T... != ['<Tag: t1>', '<Tag: t3>']

First differing element 1:
<Tag: t2>
<Tag: t3>

First list contains 1 additional elements.
First extra element 2:
<Tag: t3>

- ['<Tag: t1>', '<Tag: t2>', '<Tag: t3>']
?                      -------------

+ ['<Tag: t1>', '<Tag: t3>']

----------------------------------------------------------------------
Ran 2853 tests in 11845.811s

FAILED (failures=2, skipped=63, expected failures=2)
Destroying test database 'default'...
Destroying test database 'other'...

Running the queries and initial_sql_regress tests in isolation fail as well, so it's not necessary to run the full suite to see the errors. Have not looked into the errors at all, running the test suite in this config takes a while and now it is bedtime....

[Russell Keith-Magee]

This is obviously a blocker for the 1.3 release.

[Russell Keith-Magee]

In the same vein; there's at least one failure under InnoDB, too:

======================================================================
ERROR: test_ticket7778 (regressiontests.queries.tests.SubclassFKTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/rkm/projects/django/hg/tests/regressiontests/queries/tests.py", line 1154, in test_ticket7778
    tvc.delete()
  File "/Users/rkm/projects/django/live/django/db/models/base.py", line 579, in delete
    collector.delete()
  File "/Users/rkm/projects/django/live/django/db/models/deletion.py", line 48, in decorated
    func(self, *args, **kwargs)
  File "/Users/rkm/projects/django/live/django/db/models/deletion.py", line 229, in delete
    query.delete_batch(pk_list, self.using)
  File "/Users/rkm/projects/django/live/django/db/models/sql/subqueries.py", line 44, in delete_batch
    self.do_query(self.model._meta.db_table, where, using=using)
  File "/Users/rkm/projects/django/live/django/db/models/sql/subqueries.py", line 29, in do_query
    self.get_compiler(using).execute_sql(None)
  File "/Users/rkm/projects/django/live/django/db/models/sql/compiler.py", line 735, in execute_sql
    cursor.execute(sql, params)
  File "/Users/rkm/projects/django/live/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/Users/rkm/.virtualenvs/django/lib/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "/Users/rkm/.virtualenvs/django/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test_testdb`.`queries_tvchef`, CONSTRAINT `celebrity_ptr_id_refs_id_be1236fb` FOREIGN KEY (`celebrity_ptr_id`) REFERENCES `queries_celebrity` (`id`))')

[Russell Keith-Magee]

Some more clarification -- The inital_sql failure isn't a regression; the same test fails under Django 1.1. However, in the process of migrating to unittests, we've actually made the test do something useful, which has pointed out a problem that has always existed.

[Russell Keith-Magee]

It turns out that the initial_sql failure points to a pretty major problem.

Using our current test setup, a TransactionTestCase calls flush() to clear the database and reinstall initial data.

However, a flush *doesn't* re-run custom SQL. This is a good thing if the custom SQL is table modifications, etc, but if you're using custom SQL to load initial data -- such as the test is doing -- you're out of luck. Once the database has been flushed, you've lost all your initial data.

The test fails reliably under MySQL MyISAM because of the lack of transaction support -- all test cases are transaction test cases. However, you could get the same effect as long as a transaction test case *of any kind* runs before the initial data test.

Essentially, this has *always* been broken; we've just never noticed, and our old test case only validated against syntax errors -- it didn't actually check anything worked.

I can see two solutions:

1) Document, post-facto, the fact that custom SQL can't be used for test data.
2) Introduce a different type of custom SQL that can be used for initial data.

Neither of these is especially backwards compatible.

[Russell Keith-Magee]

I've just attached a proposed fix for the initial_data problem. It follows up on option (1) -- document the fact that custom SQL can't be used for initial test data, but also makes a slight modification to the test startup process so that we can say this with certainty. This is a case of something that has always been partially true (i.e, all TransactionTestCases, and all tests under MyISAM); the patch just makes it true for all databases, all test cases.

[Russell Keith-Magee]

Proposed fix for the initial_data test problem

[Russell Keith-Magee]

I've now diagnosed the other MySQL failure reported by Karen -- the problem is that MySQL is broken :-)

Exhibit A:

DROP TABLE IF EXISTS `testapp_tag`;
CREATE TABLE `testapp_tag` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(10) NOT NULL,
    `parent_id` integer
);
INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t1", NULL);
INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t2", 1);
INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t3", 1);
INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t4", 3);
INSERT INTO `testapp_tag` (`name`, `parent_id`) VALUES ("t5", 3);

SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag`.`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC;

SELECT `testapp_tag`.`id`, `testapp_tag`.`name`, `testapp_tag`.`parent_id` FROM `testapp_tag` WHERE NOT ((`testapp_tag`.`id` IN (SELECT U0.`id` FROM `testapp_tag` U0 LEFT OUTER JOIN `testapp_tag` U1 ON (U0.`id` = U1.`parent_id`) WHERE U1.`id` IS NULL) AND `testapp_tag`.`id` IS NOT NULL)) ORDER BY `testapp_tag`.`name` ASC;

The last two queries are *identical*. But they yield different results. The second query is the "right" result.

So - this is a MySQL problem. The SQL for the query is completely correct, and consistent with the SQL generated for other backends. The same SQL works for every database *except* MySQL/MyISAM. And if you run the query twice, the problem "fixes" itself under MySQL/MyISAM.

There's very little we can do (or that I'm willing to do) to catch or work around this problem in live queries, so I'm going to fix this problem by adding the dummy query -- that will get the test case to pass, and pass the responsibility down to MySQL.

[Russell Keith-Magee]

(In [15238]) Refs #14661 -- Corrected (well... hacked around) a test failure under MySQL/MyISAM with the queries regression test.

[Russell Keith-Magee]

(In [15239]) Refs #14661 -- Clarified the handling of initial data injected via custom SQL.

This is BACKWARDS INCOMPATIBLE CHANGE for anyone relying on SQL-injected initial data in a test case.

[Russell Keith-Magee]

(In [15240]) [1.2.X] Refs #14661 -- Corrected (well... hacked around) a test failure under MySQL/MyISAM with the queries regression test.

Backport of r15238 from trunk.

[Russell Keith-Magee]

(In [15241]) [1.2.X] Refs #14661 -- Clarified the handling of initial data injected via custom SQL.

This is BACKWARDS INCOMPATIBLE CHANGE for anyone relying on SQL-injected initial data in a test case.

Backport of r15239 from trunk.

[Karen Tracey]

Replying to russellm:

I've now diagnosed the other MySQL failure reported by Karen -- the problem is that MySQL is broken :-)

I don't have time right now to look too closely, but the presence of "where id is null" type constructs in the queries above makes me guess this may be related to the MySQL behavior noted in this thread:

​http://groups.google.com/group/django-developers/browse_thread/thread/d4180b8addf5e970/efb0eb962c71bde6

[Russell Keith-Magee]

Following r15238-r15241, the originally reported test failures are fixed, so I'm closing this ticket. I was going to keep it open to fix the InnoDB problem that I found, but it turns out that this isn't a simple test case failure -- it points to a larger problem with deletion. I've opened #15118 to track that problem.

[Jacob]

Milestone 1.3 deleted