Code

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#14661 closed (fixed)

A couple of MySQL/MyISAM test failures

Reported by: kmtracey Owned by: nobody
Component: Testing framework Version: 1.2
Severity: Keywords: blocker
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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....

Attachments (1)

t14661a.diff (5.6 KB) - added by russellm 3 years ago.
Proposed fix for the initial_data test problem

Download all attachments as: .zip

Change History (15)

comment:1 Changed 3 years ago by russellm

  • milestone set to 1.3
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

This is obviously a blocker for the 1.3 release.

comment:2 Changed 3 years ago by russellm

  • Keywords blocker added

comment:3 Changed 3 years ago by russellm

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`))')

comment:4 Changed 3 years ago by russellm

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.

comment:5 Changed 3 years ago by russellm

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.

comment:6 Changed 3 years ago by russellm

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.

Changed 3 years ago by russellm

Proposed fix for the initial_data test problem

comment:7 Changed 3 years ago by russellm

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.

comment:8 Changed 3 years ago by russellm

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

comment:9 Changed 3 years ago by russellm

(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.

comment:10 Changed 3 years ago by russellm

(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.

comment:11 Changed 3 years ago by russellm

(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.

comment:12 Changed 3 years ago by kmtracey

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

comment:13 Changed 3 years ago by russellm

  • Resolution set to fixed
  • Status changed from new to closed

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.

comment:14 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.