Loris: showDatabaseQueries does not always display all queries

[johnsaigle]

Describe the bug

The flag showDatabaseQueries can be enabled in project/config.xml and is used by developers to print all MySQL queries to the front-end for use in debugging.

In some situations requiring numerous or very long queries, not all of the queries will be displayed. This reduces its effectiveness as a development tool.

[driusan]

What are "some situations requiring numerous or very long queries"? What queries are not being displayed? showDatabaseQueries doesn't change behaviour based on the query length.

[johnsaigle]

This came up after a discussion on Slack with @h-karim. He had a case where he was debugging a module and received a MySQL error in his error log but it did not show up on the front-end.

I've had similar issues but I can't remember an example at this point.

Karim would you be able to add what you tried when showDatabaseQueries failed to show you the query you wanted?

[christinerogers]

The example complexity was this query fyi @driusan :
https://github.com/aces/Loris/issues/6616#issuecomment-636112892

@h-karim can you confirm what ShowDatabaseQueries actually shows for this, e.g.?

[h-karim]

Yes, so the query that I'm trying to catch is located in https://github.com/aces/Loris/blob/23.0-release/modules/imaging_browser/php/imagingbrowserrowprovisioner.class.inc .

When enabling the showDatabaseQueries, the output that I'm getting can be found here.

I know that none of these are the query I'm looking for because in the error_log, when the ShowDatabaseQueries is disabled, a piece of the incorrect query appears:

PHP Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-defaced_QC_Status,s.CenterID as CenterID,\n c.Entity_type as entity' at line 84 in /var/www/loris/src/Data/Provisioners/DBRowProvisioner.php on line 87, referer: http://localhost:8080/imaging_browser/
[Tue Jun 02 12:03:43.363488 2020] [php7:error] [pid 30296] [client ::1:47922] PHP Fatal error: Uncaught Exception: Invalid SQL statement: SELECT \n p.Name as Site,\n c.PSCID as PSCID,\n c.CandID as DCCID,\n Project.Name as project,\n s.Visit_label as visitLabel,\n \n CASE s.MRIQCStatus\n WHEN 'Fail' THEN\n IF(s.MRIQCPending='Y', 'Pending Fail', 'Fail')\n WHEN 'Pass' THEN\n IF(s.MRIQCPending='Y', 'Pending Pass', 'Pass') \n ELSE s.MRIQCStatus\n END \n as Visit_QC_Status,\n DATE_FORMAT(MIN(pf.Value), "%Y-%m-%d") as First_Acquisition,\n FROM_UNIXTIME(MIN(f.InsertTime)) as First_Insertion,\n FROM_UNIXTIME(MAX(fqc.QCLastChangeTime)) as Last_QC,\n \n CASE \n COALESCE(Max(fqc.QCLastChangeTime), 'new')\n WHEN 'new' THEN IF(FIND_IN_SET(40,GROUP_CONCAT(\n DISTINCT AcquisitionProtocolID))>0 OR FIND_IN_SET(44,GROUP_CONCAT(\n in /var/www/loris/src/Data/Provisioners/DBRowProvisioner.php on line 90, referer: http://localhost:8080/imaging_browser/

I've tried so far at using the element inspector to look at the html, there was nothing that looked similar to the error log query. The response body only included css.

[ridz1208]

@h-karim you've got your error right there.

SQL does not like - in field names. you will need to use dbEscape() function around the field names in the query that represent scan types.

That will transform SELECT scan-type, candId,... into SELECT ``scan-type``, candid,...

[johnsaigle]

But it should still show up in the front-end right?

Or does showDatabaseQueries not print SQL syntax errors? If it doesn't, it really should.

[johnsaigle]

It looks like showDatabaseQueries depends on the _printQuery() function in the database. This is called before each query in the select, insert, replace, and update functions and prints them correctly.

However when some kind of database exception occurs, this does not get printed in the same way. Exceptions are thrown where they may or may not be handled properly by calling code. They will often appear in the error logs but not in the front-end display.

I think we can fix this by creating a similar function _printException within database class that is used to assist in logging DB exceptions. When an exception is caught within the class, we can call _printException within the catch statement which will print the output to the same buffer as the other SQL queries.