Code

Opened 4 years ago

Closed 17 months ago

#13533 closed Bug (duplicate)

queries test fails under MySQL InnoDB

Reported by: russellm Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2-beta
Severity: Normal Keywords: innodb mysql
Cc: erikr Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

As of 1.2RC1, the queries test fails under MySQL InnoDB fails at the following test:

Failed example:
    Tag.objects.exclude(children=None)
Expected:
    [<Tag: t1>, <Tag: t3>]
Got:
    [<Tag: t1>, <Tag: t3>, <Tag: t4>]

This is at line 1150 (as of r13252).

This test passes as is under SQLite, Postgres, and MySQL MyISAM.

Even more weird - if you dig into the database at the time the test runs, the query is the same, and the contents of the table is the same. The issue appears to be entirely related to a query cache somewhere in the MySQLdb infrastructure, but only under InnoDB.

You can make the test pass by inserting:

>>> connection.close()

just before the failing test. You can also make the test pass by manually issuing the same query twice, and only checking the test result on the second execution -- i.e., if you modify the test to the following:

>>> r = list(Tag.objects.exclude(children=None))
>>> Tag.objects.exclude(children=None)
[<Tag: t1>, <Tag: t3>]

The test will pass.

Attachments (1)

t13533-tcpdump (13.2 KB) - added by erikr 4 years ago.
tcpdump of communications with mysql

Download all attachments as: .zip

Change History (12)

comment:1 Changed 4 years ago by russellm

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

comment:2 Changed 4 years ago by erikr

  • Keywords innodb mysql added

As of r13252? As far as I can see, this was introduced in r12970.

I've played around with transaction isolation levels and query caching, but didn't get any results.

comment:3 Changed 4 years ago by erikr

  • Cc erikr added

comment:4 Changed 4 years ago by erikr

  • Owner changed from nobody to erikr
  • Status changed from new to assigned

comment:5 Changed 4 years ago by erikr

  • Owner changed from erikr to nobody
  • Status changed from assigned to new

This would appear to be a MySQL bug, triggered by the testcases.

This problem is triggered by the creation of the ExtraInfo in test_evaluated_queryset_as_argument(), introduced in r12970. Somehow, after that has been done, the same MySQL query yields different results on it's first run, then on subsequent runs.

The query run for the test is:

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

I have verified this with tcpdump, and MySQL indeed sends back t1, t3 and t4 on the first run, and only t1 and t3 on the second run.
I will attach a log of this.

The insert query for the ExtraInfo looks completely normal:

INSERT INTO `queries_extrainfo` (`info`, `note_id`) VALUES ('good', 1)

Unfortunately a run of the queries suite does over 2000 queries, so it is rather difficult to isolate the problem.
Perhaps someone more experienced can get more out of this.

Changed 4 years ago by erikr

tcpdump of communications with mysql

comment:6 Changed 3 years ago by russellm

The workaround was added to the test case in r15238.

comment:7 Changed 3 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:8 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

comment:11 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:12 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:13 Changed 17 months ago by claudep

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

I think this has been fixed along #16809.

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.