Reporting Issues

Reporting Issues

by Matt Mackin -
Number of replies: 12

I have been having a problem running one of my reports for the past day or two.  

It includes student info, course name, completion dates, etc.

I have been using the same SQL code all along, but when I go to run the report I am getting an error message that says:  "SQL entered is not supported by this report or is incorrect. Error reading from database"  Strangely enough, all of my other (smaller reports) are working just fine.

Have there been any issues with moodle server/databases recently?  Up until yesterday this report has worked fine, so I just want to try and figure out if this is some sort of a problem on my end.


Using version 2.6.3


Thank you

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't think there's much we can do unless you show us the SQL causing the problem.

Average of ratings: -
In reply to Howard Miller

Re: Reporting Issues

by Matt Mackin -

SELECT distinct    user1.firstname AS Firstname,

        user1.lastname AS Lastname,

        user1.id AS id,

        (select data from mdl_user_info_data a where fieldid = 3 and userid = user1.id ) as company,

        user1.email AS Email,

        (select data from mdl_user_info_data a where fieldid = 5 and userid = user1.id ) as fullshippingaddress,

        user1.country AS country,

        course.fullname AS Course,

        FROM_UNIXTIME (user1.firstaccess,'%m/%d/%y') AS startdate,

        (select code from {certificate_issues} i JOIN mdl_certificate c where course.id = c.course AND user1.id = i.userid AND c.id = i.certificateid) as CertificateCode,

        FROM_UNIXTIME ((select i.timecreated from {certificate_issues} i JOIN mdl_certificate c where course.id = c.course AND user1.id = i.userid AND c.id = i.certificateid), '%m/%d/%y') as CertificateDate,

        (SELECT shortname FROM mdl_role WHERE id=en.roleid)

        AS ROLE FROM mdl_course AS course JOIN mdl_enrol AS en ON en.courseid = course.id

        JOIN mdl_user_enrolments AS ue ON ue.enrolid = en.id JOIN mdl_user AS user1 ON ue.userid = user1.id

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

In the middle there is a subselect...

select code from {certificate_issues} i JOIN mdl_certificate c where course.id = c.course AND user1.id = i.userid AND c.id = i.certificateid

why does that have curly brackets and the others don't?

Average of ratings: -
In reply to Howard Miller

Re: Reporting Issues

by Matt Mackin -

Don't really know...doesn't seem right.  Had someone else write this for us (haven't done much SQL in a while).  Any suggestions?  

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I don't have the Certificate module installed but I expect it means mdl_certificate_issues instead of {certificate_issues}. You would use the curly bracket version insure a Moodle $DB->execute() or similar, where Moodle replaces the table name with the correct name. If you are running this query directly against the database then the curly brackets will be wrong. 

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Luis de Vasconcelos -

Change all the  {certificate_issues} to mdl_certificate_issues. Example:

(select code from {certificate_issues} i...

becomes:

(select code from mdl_certificate_issues i...

 

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Reporting Issues

by Matt Mackin -
A little better, but still not solved.

Is there any way to contact someone at Moodle to find out if there was some sort of change recently?  I can't find any way to contact moodlerooms tech support.   My IT department wants to know about recent changes before they have to reconfigure. 
Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You are contacting "someone at Moodle". As a community project this is how it works. 

"Moodlerooms", on the other hand, is a commercial company. Are you a client of theirs?

Let's stick with it - what error are you getting now? The database layout can change with any upgrade which is why these sort of queries can be quite fragile. However, the only way this would happen if "your IT department" changed something or updated something on your site. 

To take a step back - if it worked before and doesn't work now, what did YOU change?

Average of ratings: -
In reply to Howard Miller

Re: Reporting Issues

by Matt Mackin -

The company I work for is a client of moodlerooms, and we use them for our corporate e-learning.  A few times a month we run various reports to track progress in our company, with our customers, etc.

Up until a few days ago, the query I listed above worked just fine.  Since the end of last week, for whatever reason, this query now longer works and I receive the error message above.

To my knowledge nothing has changed on our end, so I am just trying to determine if there was some sort of moodle update that was made. 

The error message I am receiving is :

"The SQL entered is not supported by this report or is incorrect. Error reading from database"

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Michael Burroughs -

Wow! I thought I was doing something wrong! I just started getting this message as well. I have made no changes to our current configuration. I have checked the schema and nothing appears to have changed. The database is intact and does not appear to have any errors. Where to now?

Average of ratings: -
In reply to Matt Mackin

Re: Reporting Issues

by Floyd Saner -
There is one possibility I can think of.  If your system was upgraded to Moodle 2.7 and your report accesses the Moodle log table, it is possible that is causing the errors.  See the following:


https://docs.moodle.org/27/en/Upgrading#Possible_issues_that_may_affect_you_in_Moodle_2.7

https://docs.moodle.org/dev/Logging_2

You can enable legacy logging in Moodle 2.7 at: Site administration ... Plugins ... Logging  ... Manage log stores

Floyd

Average of ratings: -