Hi Randy, thanks for the reply. I made some progress on this. It seems that the query in the temp table text box is run and stored in a table called 'temp'. If the 'number of temporary tables required' is more than 1, it seems to basically create multiple instances of the temp query results as temp, temp1, temp2 etc... I read that this is because the temp table can only be used once in the query. My client has some reports that have up to 3 temp tables. In the end I removed the temp tables altogether and nested the temp query back into the main report query. This resolved/worked-around the issue.
But in answer to your question, it's the moodle db being queried, although the db is on a separate host to the front end. the root cause turned out to be a curly issue with the backend database running on mariadb (database was migrated from a mysql 5.7 db) and for some reason the query that moodle runs to create the temp tables was failing. In the end it turned out to be joins in the temp table query that had data type mismatches (joining a BIGINT to a TEXT column (user.id to feedback.value) and the result was that the temp query wasn't running but the report was returning the 'error writing to database'. I guess it was failing because of how mariadb determines the column types for the temp table creation. Casting the userid to a string resolved the issue but it took a lot of mucking around to find the culprit (basically remarking out each section of the query until I got it to run).
Anyhow, I hope this helps someone else that may have similar issues.
But in answer to your question, it's the moodle db being queried, although the db is on a separate host to the front end. the root cause turned out to be a curly issue with the backend database running on mariadb (database was migrated from a mysql 5.7 db) and for some reason the query that moodle runs to create the temp tables was failing. In the end it turned out to be joins in the temp table query that had data type mismatches (joining a BIGINT to a TEXT column (user.id to feedback.value) and the result was that the temp query wasn't running but the report was returning the 'error writing to database'. I guess it was failing because of how mariadb determines the column types for the temp table creation. Casting the userid to a string resolved the issue but it took a lot of mucking around to find the culprit (basically remarking out each section of the query until I got it to run).
Anyhow, I hope this helps someone else that may have similar issues.