Ready to get the most from your MySQL data? Get started by setting up a connection between Klipfolio and your MySQL database files and adding some data sources.
When you're finished adding MySQL data sources, you'll use them to power your custom metrics or Klips.
This article includes:
- Adding a MySQL custom-built data source
- Troubleshooting MySQL queries in Klipfolio
- I've added MySQL data sources - What's next?
Adding a MySQL custom-built data source
To add a MySQL custom-built data source:
- Click the + beside Data Sources in the left navigation sidebar.
If you don’t see the option above, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources > Create a New Data Source.
- On the Where is your data? page, select MySQL.
- On the Choose a pre-built or custom connection page, click Create a custom MySQL data source.
- Fill in the following fields:
- Host
- Port
- Database
- Driver
- Username
- Password
- At SQL Query, adjust the query information to return the data you need and replace placeholder information with your own. For example, replace <databaseTable> with your own data.
- At Columns, choose to include or exclude column headers.
- At Connection security, choose whether to use SSL/TLS.
- If accessing a database behind a firewall, click Use an SSH tunnel and enter the required credentials.
- Click Get data.
- Ensure this is the data you’re looking for. If you’re using Klips, optionally select the checkbox to Model your data. If you’re using PowerMetrics, which requires modelled data sources, you’ll automatically enter the modeller after clicking Continue. Learn more about modelling data sources.
- Click Continue.
- Name, choose refresh settings, and, optionally, share your data source.
- Click Save.
The data source is added to your account and is ready to use for custom metrics or custom Klips. You can see all your data sources in your Data Source Library, accessed by clicking Data Sources in the left navigation sidebar.
Troubleshooting MySQL queries in Klipfolio
Here are a few troubleshooting tips you can try if you're having issues with your MySQL query.
First steps
Begin by checking out the following:
- Confirm that the SQL connection is working by using a query of Select 1. This should return the result of 1.
- Make sure your query isn’t trying to bring in too much data. Klipfolio supports a maximum file size of 10 MB. If you’re returning more than 10 MB of data, your query may not work. To see if this is the issue, try adding a LIMIT to your query (e.g., LIMIT 1000) to reduce the amount of data being returned.
- Confirm that it isn't taking too long to bring in your data. Klipfolio includes a global setting of 80 seconds within which to bring in data. If you think this might be the issue, try reducing the amount of your data so you stay within this range.
If you are seeing an “error in your SQL syntax” message
Make sure your query does not include a string containing the “;” character. Klipfolio truncates your query at the first encountered “;” and ignores everything after that.
For example, if your query includes SELECT ';', you will get an error because the SQL server receives SELECT '; as a query, which, because it’s an incomplete string, is invalid.
To fix this issue, you can use a hexadecimal literal string instead. If you do so, the previous example would be written as SELECT x'3b'.
If your query isn’t returning all of the data
If your MySQL query in Klipfolio contains records that end in a backslash (/) the data will be truncated at that point, so your query will not return all of the data. To work around this issue, try adding a space after the backslash within the query url to see if this corrects the issue.
Next steps
Great! You've connected to your MySQL data and created one (or more) data sources. Now you're ready to use those data sources in custom metrics or custom Klips.
Want to learn more?
Check out these related articles: