Connecting Klipfolio to MySQL

You can set up a connection between Klipfolio and your MySQL data to display data from your MySQL database files on a dashboard.

This article includes:

What options do I have for visualizing my MySQL data?

Create data visualizations based on pre-built options or build your own. The choice is yours!

Pre-built by Klipfolio

Data sources provide the foundation for Klip building, so we suggest you create a data source as a first step (before building Klips and adding them to your dashboard).

Our MySQL pre-built sample data sources enable you to access sample data so you can see how the connector works before trying it out with your own data.

Custom-built by you

Taking the time to learn how to create your own data sources and Klips unlocks your data and enables you to control, manipulate, and transform it, leading you to unprecedented insights into your business.

  • Custom data sources: Write your own queries from the ground up and build a data source from scratch.
  • Custom Klips: Using our Klip Editor, you can construct and manipulate your data, including integrating multiple data sources, to produce unique, custom-built data visualizations.

How do I access MySQL pre-built sample data sources?

There are several MySQL pre-built sample data sources available. They use data from our sample databases and are a great way to see what kind of data you can retrieve from your MySQL database using Klipfolio.

Tip: You can also use the pre-built sample data sources as a starting point and adjust their settings and parameters to point to your own data.

To access MySQL pre-built sample data sources:

  1. Navigate to the Service Connectors page in Klipfolio and choose MySQL from the list.
  2. Select a pre-built data source from the left section of the Choose a pre-built or custom connection page.
  3. Click Get data.
  4. View the returned sample data at the bottom of the page.
  5. Optionally, follow the instructions to save the sample data source and then use it to build sample Klips.

How do I add a MySQL custom-built data source?

To add a MySQL custom-built data source:

  1. Navigate to the Service Connectors page in Klipfolio and choose MySQL from the list.
  2. On the Configure your data source page, click Create a custom MySQL data source.
  3. Fill in the following fields and select whether you want to Include column headers and enable Connection security (Use SSL/TLS):
    • Host
    • Port
    • Database
    • Driver
    • Username
    • Password
    • SQL Query - Adjust the query information to return the data you need. Replace placeholder information with your own. For example, replace <databaseTable> with your own data.
  4. If accessing a database behind a firewall, click Use an SSH tunnel and enter the required credentials.
  5. Click Get data.
  6. Ensure this is the data you’re looking for and, optionally, select the checkbox to Model your data. Learn more about modelling your data source here.
  7. Click Continue.
  8. Name, choose refresh settings, and, optionally, share your data source.
  9. Click Save.

Troubleshooting MySQL queries in Klipfolio

Here are a few troubleshooting tips you can try if you are 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.

How do I create a MySQL custom Klip?

You create custom Klips from either your list of Klips or your Dashboard. You choose a data visualization format, for example, a pie chart, gauge, or table, and decide whether you want to use an existing data source or create a new one. In the Klip Editor, there are limitless opportunities to modify, relate, and display your data. See the following related articles for more information and get building!

Related articles

Have more questions? Submit a request