Basic SQL Troubleshooting
Learning when something is wrong with your database queries and learning how to resolve the problem is key to getting the best possible use out of the database connection to Ignition. Typically, the biggest problem that users face with queries is the data taking too long to load on the window, but an error in a query can also be difficult to track down. This section details what to look for when the query fails to execute, as well as what to do when facing slow queries and how to optimize them within the project to help return data to the window as quickly as possible.
Error Message Box
If a query fails to execute, chances are there was an error box that popped up. Resist the urge to close it! The Error Box usually contains a lot of useful info that can help troubleshoot why the query is failing. When looking at the error message, there are a few things to look for. First, you will want to click on the Details tab in the upper left corner of the Error Box. This will bring up the details of the error, which contains the information we need to track down the error.
In the image below, you can see the yellow highlighted part shows us what our query is, as well as where it is executing from. This helps to ensure we are looking at the correct error message for the query we are trying to fix. Next, you want to look for the words 'caused by' in the text of the error details, which tell us the reason for the error. You can see in the image below, there are two of 'caused by' messages highlighted in green.The first one is from the Gateway, and the second one is from the database (in this case, a MySQL database).
After locating the 'caused by', the following message will help to pinpoint what the error is. In this example, we have an error with our syntax in our SQL statement. The most helpful portion of the error is at the end of the message highlighted in purple. This lets us know where the syntax error is in the SQL statement. The trick is to look immediately before the quoted query. In the image below, it starts to quote the original query with 'FROM alarm_events WHERE.....', so in my original query, I need to look at what was right before the 'FROM'. You can see in my original query highlighted in yellow, there is a comma right before the 'FROM', which is incorrect, as the last column in the SELECT statement should not have a comma after it.
Other Common SQL Errors
Unknown Column
Typically, the name of the column is wrong because of a misspelling. In this case, the correct spelling was 'eventtime', but there was an accidental 's' added to the name.
Unknown Table
If the table is not found in the database, it may also be because of a misspelling. Here, the correct table name should be 'alarm_events'.
Any Database Error
The previous error examples have all been from a MySQL database, but the same principles apply to any database. Simply locate the 'caused by', and look at the message afterwards. In this MSSQL syntax error, instead of displaying everything after the syntax error, it only displays the part immediately after. The problem in this query is an extra comma before the word 'FROM'.
Checking the Database Connection
Checking the Database Connection in the Gateway can also be useful to ensure there is a valid connection and there is nothing blocking the execution of queries. The Status page of the Gateway can be used to determine if queries are taking too long, while the Database Connection page can be used to alter the settings of the database to better handle the number of queries running from the Gateway to the database. See the Slow Queries page for more information on changing these settings.
Testing Query Results
Using the Database Query Browser is a good way to test out a query before actually running it on a component. This can help you see what results will be returned so that the query can be modified to narrow down how the query should be formatted.
Checking for Slow Queries
There are several ways that slow running queries can cause improper behavior. See Slow Queries for more details.