Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#338 closed improve feature (fixed)

Show DB queries for debugging

Reported by: matrixpoint Owned by: matrixpoint
Priority: minor Milestone: 0.1.3
Component: BW General Keywords: debugging speed
Cc: jeanyves

Description

Provide a link at the bottom of pages for developers to display DB queries with execution times.

Change History (19)

comment:1 Changed 11 years ago by matrixpoint

Rev. r3765.

I added query execution timing to query_history array that was already part of lib/db/db_statement_mysql.lib.php. This array is available as PVars::get()->query_history if PVars::get()->debug is set. I made use of this in the templates/apps/rox/footer.php. Clicking on the link "DB queries" at the very bottom of any page makes the list of queries visible.

I'm not sure how the Rights system works. Right now, only people with "Words" rights can make use of this feature, unless the PVars::get()->debug variable is only set for certain people (like developers). The latter would make the most sense, omitting the need for a Right. If a Right is needed in addition to PVars::get()->debug, please replace "Words" with the appropriate Right, otherwise, remove the "Words" right.

comment:2 follow-up: Changed 11 years ago by tobixen

Can any private information of other users be exposed through this?

comment:3 in reply to: ↑ 2 Changed 11 years ago by matrixpoint

Replying to tobixen:

Can any private information of other users be exposed through this?

I don't know, so this should be investigated. But my initial thought was that all private information (other than, say, member table ID's of other members) should be about me, when I am logged in.

comment:4 follow-up: Changed 11 years ago by tobixen

I think the changeset should be fast-tracked to the alpha server, and eventually production, since we're having issues with production.

I have reviewed the changeset and it looks good to me, but I don't know the code well enough to "approve" it.

I find the likelihood of showing private information for other users through this code as very low, but that also would have to be commented by someone knowing the code.

Please get in touch with JY and ask for his comments.

comment:5 in reply to: ↑ 4 Changed 11 years ago by matrixpoint

Please get in touch with JY and ask for his comments.

I have him as Cc: on this ticket, but I also directly emailed him.

comment:6 Changed 11 years ago by tobixen

What about the parameters, are they included in the query shown, or will it show up as ... "select * from user where id=?" ?

comment:7 Changed 11 years ago by matrixpoint

I have modified the searchmembers ajax page to produce a query list for debugging. I'll upload it as soon as #338 is approved. On my localhost, this is how a basic query looks:

11: (7.8 ms) select SQL_CALC_FOUND_ROWS count(comments.id) as NbComment?,members.id as IdMember?,members.BirthDate?,members.HideBirthDate?,members.Accomodation,members.Username as Username,date_format(members.LastLogin?,'%Y-%m-%d') as LastLogin?,cities.latitude as Latitude,cities.longitude as Longitude,cities.Name as CityName?,countries.Name as CountryName?,ProfileSummary?,Gender,HideGender? from (members,cities,countries) left join comments on (members.id=comments.IdToMember?) where members.Status='Active' and members.HideBirthDate?='No' and cities.id=members.IdCity? and countries.id=cities.IdCountry? group by members.id order by members.created limit 0,10

I hope this answers your question.

comment:8 Changed 11 years ago by matrixpoint

I uploaded the modification to searchmembers/ajax.php to test in r3770. Using the URL searchmembers/ajax/queries generates a query list for a typical member search.

comment:9 Changed 11 years ago by matrixpoint

Rev. r2775 has improved DB query listing for search members on the test server. Now, the query for an arbitrary search can be seen in the member list area below the map if the link http://test.bewelcome.org/searchmembers/index/queries is used.

comment:10 Changed 11 years ago by tobixen

r3765 and r3770 but not yet r3775 is in alpha now - but I'm pretty sure I don't have this "words" access myself (could probably fix it through the database - but since I know nothing about the database model, it's probably best not to do it) - so PLEASE check and report back if you see any queries that needs to be optimized.

I'm going to look through the "slow queries" logs on goat as well.

comment:11 follow-up: Changed 11 years ago by jeanyves

I like this DQ query list thing

I would like to know how to add in it the queries done by the old BW sql_query function.

some wishes :

  • I think the DB QUERIE access should be under some constraint of the current user having some specific Scope (like "DB_QUERY", in his debug right)

(keep in mind that showing the queries can be a bit dangerous, I recently saw a query with a plaintext password (select from members where username='xxxxx' and password=PASSWORD('yyyyy'))

  • I really whould like the query to contain ALPHA / TEST/ WWW to allow in the slow query log to see where they were built
  • In addition, I would like to have a comment on the query about the context (which $_SESSION[IdMember?], which feature they come from ...), this will help for understanding problem in the slow query log or in other places

JeanYves?

comment:12 in reply to: ↑ 11 Changed 11 years ago by matrixpoint

Replying to jeanyves:

I like this DQ query list thing

I would like to know how to add in it the queries done by the old BW sql_query function.

  • I think the DB QUERIE access should be under some constraint of the current user having some specific Scope (like "DB_QUERY", in his debug right)

Could you create this new right?

(keep in mind that showing the queries can be a bit dangerous, I recently saw a query with a plaintext password (select from members where username='xxxxx' and password=PASSWORD('yyyyy'))

I agree that this is potentially sensitive. But the password should only appear to the one who owns it. We should keep noticing if there is anything more serious that appears in a query.

  • I really whould like the query to contain ALPHA / TEST/ WWW to allow in the slow query log to see where they were built
  • In addition, I would like to have a comment on the query about the context (which $_SESSION[IdMember?], which feature they come from ...), this will help for understanding problem in the slow query log or in other places

JeanYves?

Good ideas.

comment:13 Changed 11 years ago by matrixpoint

  • follow_up changed from test to move to alpha

Changed rights from "Words" to "Debug" (scope = "DB_QUERY") in r3862.

Tests OK with admin login (has right) and henri login (doesn't have debug right), so moving to alpha.

comment:14 Changed 11 years ago by matrixpoint

  • follow_up changed from move to alpha to test on alpha

Remaining files moved to alpha in r3873.

comment:15 Changed 11 years ago by matrixpoint

Jean-Yves: Could you please give me the right to test this on alpha? Thanks!

comment:16 Changed 11 years ago by philipp

  • follow_up changed from test on alpha to release
  • Resolution set to fixed
  • Status changed from new to closed

seems to work fine for me matrixpoint, I gave you access.

comment:17 Changed 11 years ago by matrixpoint

Moved slight formating change in r3766 to alpha in r3889.

comment:18 Changed 11 years ago by matrixpoint

r3893: Fixing error in r3889.

comment:19 Changed 11 years ago by matrixpoint

  • follow_up changed from release to none

Moved to production in r3894. Small error fixed in r3895.

Note: See TracTickets for help on using tickets.