Code

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#11209 closed (fixed)

connection.queries reports different SQL query than MySQL log

Reported by: creecode Owned by: kmtracey
Component: Documentation Version: 1.0
Severity: Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I'm using MySQL 5.0.x and Django v1.0.2.

The QuerySet...

Entry.objects.filter ( content__search = 'heat pipes' )

...is producing this SQL...

SELECT my_app_entry.id, my_app_entry.title, my_app_entry.content, my_app_entry.url, my_app_entry.created, my_app_entry.modified, my_app_entry.website_id FROM my_app_entry WHERE MATCH (my_app_entry.content) AGAINST (heat pipes IN BOOLEAN MODE) LIMIT 21

...as reported from connection.queries. I seem to be getting results back with no reports of errors but if you look closely at the above SQL you'll see there is a syntax error. I verified this by putting the above SQL into a client app that tried to run the above SQL and received an error.

At this point I was a bit confused and took a look at the MySQL log and it reported...

SELECT my_app_entry.id, my_app_entry.title, my_app_entry.content, my_app_entry.url, my_app_entry.created, my_app_entry.modified, my_app_entry.website_id FROM my_app_entry WHERE MATCH (my_app_entry.content) AGAINST ('heat pipes' IN BOOLEAN MODE) LIMIT 21

You'll notice that the query string in the SQL here is properly single quoted.

Attachments (0)

Change History (3)

comment:1 Changed 5 years ago by kmtracey

  • Component changed from Database layer (models, ORM) to Documentation
  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to kmtracey
  • Patch needs improvement unset
  • Status changed from new to assigned
  • Triage Stage changed from Unreviewed to Accepted

Yes, the connection.queries log doesn't necessarily include quoting. Quoting is done by the backend, and if the backend does not provide a method to retrieve the exact sql sent to the DB for the last query (psycopg2 seems to be the only one that does), then the queries are logged without quoting. Changing component to doc, as this behavior is known and expected, but should probably be mentioned in the doc as it comes up and confuses people from time to time.

comment:2 Changed 5 years ago by kmtracey

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

(In [10847]) Fixed #11209 -- Added a note to doc on connection.queries that the logged sql may not inlude parameter quoting.

comment:3 Changed 5 years ago by kmtracey

(In [10848]) [1.0.X] Fixed #11209 -- Added a note to doc on connection.queries that the logged sql may not inlude parameter quoting.

r10847 from trunk.

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.