Once your database or data warehouse has been connected to Chartio, you can manage or move the connection and track connection or query errors directly from the Data Sources page. This section highlights how to do this along with ways to customize how your data can be queried within Chartio.
Each database has its own set of potential connection issues. Instead of documenting them all into a large tree of potential issues, Chartio has built error reporting and advice for fixing the errors directly into the setup wizard and direct connection forms, where they’re more directly helpful to you. If these tools aren’t enough to solve your problem, please don’t hesitate to email us at firstname.lastname@example.org.
If you’ve lost your connection to a previously working data source, Chartio has built a nice interface to help you debug the issue. Choose Data Sources from the top navigation, select your data source from the list, and switch to the Debug tab.
Chartio will run a series of tests on the different aspects of the connection that could go wrong. If your connection is healthy, the interface should look like the following:
If something is wrong, the interface will tell you where it is wrong and provide a description of what might be happening and the steps you can take to fix it.
There are times when it may be helpful to view the actual queries that are sent to your database. Chartio allows you to view and download the query log for each connected data source.
View query log
Select Data Sources from the top navigation and choose your data source from the list. Switch to the Query Log tab.
You’ll be able to view the Start Time, Query SQL, Errors (if any), and Query Duration. Query Duration includes only the time it takes your database to run the query—it does not include chart rendering time. You can also filter by dashboard or chart in the dropdown menus if you’re troubleshooting for a particular chart. The Query column also contains links back to the chart and dashboard.
You can download the query log as a CSV, and even upload it back to Chartio to analyze your query data.
Canceling running queries
Another neat feature of the query log is that it allows you to monitor the duration of any queries that are currently running, and cancel them if desired.
Occasionally a database does not respond to a cancel query request from Chartio. MySQL, in particular, requires an open connection to send a cancellation request; if there is no open connection available (because many queries are running), the request will be unsuccessful.
Switch to the Stats tab in your data source’s settings to view statistics about the queries ran on your data source. An easy way to improve database performance is to take a look at the Slowest Charts and begin optimization there. You can also easily see what time of day your database processes the most queries and which charts have the most errors.
Maximum Query Duration
Chartio allows you to set a Maximum Query Duration for your data source. Any queries that take longer than the amount of time specified will be cancelled. Set this to a low value to prevent long-running queries from tying up resources on your database server and slowing down other queries.
Chart caching is handled at the dashboard level. Refer to our “How does caching work?” page for more details.
You can manually get fresh data for all the charts on a Dashboard by selecting “Refresh all data” from your dashboard sidebar menu.
By default, Chartio sets data sources to read-only at the connection level. This provides an additional layer of security against malicious queries.
Some databases, such as Redshift, disallow the creation of variables and temporary tables when connections are set to read-only. For this reason, we allow Redshift users to disable the enforcement of read-only in their data source settings if needed.
If you are seeing an error message like “Error: Transaction is read-only”, disabling the read-only setting should resolve this issue.
Even with read-only disabled, your database is still protected from malicious queries in two ways:
- a user-provided read-only login Chartio uses to connect to your database
- Chartio’s disallowed keywords list that blocks queries with malicious keywords
If you would like chart builders to use only Interactive Mode or SQL Mode, adjust this data source setting.
For existing data sources, any charts previously created in the disallowed mode will be viewable but not editable.
Moving your database connection
If you’ve moved your database from one server to another, follow the steps below to maintain your Chartio connection.
- Make sure that the Chartio user has been added to your new database with the same credentials
- Visit your data source settings (Data sources > your data source) and switch to the Connection tab
- Enter the new hostname for your database and the current password for your Chartio user. Click Save to test the connection and save the new connection information.