Random MySQL Syntax Error
14 October, 2013
Greetings Yellowfin Team!
I've created a database connection to a MySQL database on a remote web host. I have no apparent troubles connecting to the database. I can pull data from it via command line, and via MySQL Workbench just fine.
I then hook a datasource up to Yellowfin, create a view on a single table, and create a report on it.
Randomly, the report works just fine, and other times returns a SQL Syntax Error!
Here is the SQL that was automatically generated by the report:
[code]
SELECT DISTINCT
`piwik_log_visit`.`config_browser_name`,
`piwik_log_visit`.`config_browser_version`,
COUNT(`piwik_log_visit`.`idvisit`)
FROM `piwik_log_visit`
GROUP BY
`piwik_log_visit`.`config_browser_version`,
`piwik_log_visit`.`config_browser_name`
[/code]
Obviously, using Piwik in this case as the data source.
I've created a database connection to a MySQL database on a remote web host. I have no apparent troubles connecting to the database. I can pull data from it via command line, and via MySQL Workbench just fine.
I then hook a datasource up to Yellowfin, create a view on a single table, and create a report on it.
Randomly, the report works just fine, and other times returns a SQL Syntax Error!
Here is the SQL that was automatically generated by the report:
[code]
SELECT DISTINCT
`piwik_log_visit`.`config_browser_name`,
`piwik_log_visit`.`config_browser_version`,
COUNT(`piwik_log_visit`.`idvisit`)
FROM `piwik_log_visit`
GROUP BY
`piwik_log_visit`.`config_browser_version`,
`piwik_log_visit`.`config_browser_name`
[/code]
Obviously, using Piwik in this case as the data source.
Hi,
That does sound very strange, I would expect connection issues, but not SQL Syntax errors.
It would be interesting to find out the running SQL query that is used when you get this message.
Unfortunately the only way to see this from within YF, is to enable debug logging. The problem with this, is that it logs EVERYTHING so your log files can get quite big, and if they are limited by size (which is set by default), the logs will recycle quite quickly.
So essentially you need to enabled debug logging, then when you run into this issue, wait 1min and then stop Yellowfin and grab all the logs from the Yellowfinappserverlogs folder.
You can then email these across.
Out of curiosity, if you refresh the report, does it work? Or do you have to wait 10 or so mins before refreshing etc..?
When you get the SQL Syntax, running the query in workbench works?
Regards,
David
That does sound very strange, I would expect connection issues, but not SQL Syntax errors.
It would be interesting to find out the running SQL query that is used when you get this message.
Unfortunately the only way to see this from within YF, is to enable debug logging. The problem with this, is that it logs EVERYTHING so your log files can get quite big, and if they are limited by size (which is set by default), the logs will recycle quite quickly.
So essentially you need to enabled debug logging, then when you run into this issue, wait 1min and then stop Yellowfin and grab all the logs from the Yellowfinappserverlogs folder.
You can then email these across.
Out of curiosity, if you refresh the report, does it work? Or do you have to wait 10 or so mins before refreshing etc..?
When you get the SQL Syntax, running the query in workbench works?
Regards,
David
I was able to track this down to our provider: HostGator. By default, HostGator has new MySQL databases set to interactive_timeout=10, where normally it is 28800. This means an idle connection to the database is kicked after 10 seconds. The reports will then not work until Yellowfin reconnects to the MySQL database.
I was able to temporarily resolve the issue by caching the view with a 1 minute setting.
I was able to temporarily resolve the issue by caching the view with a 1 minute setting.