Connecting Klipfolio to MySQL

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:

Accessing MySQL pre-built sample data sources

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. 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. 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.

  1. On the Where is your data? page, select MySQL.
  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 or custom metrics.

Adding a MySQL custom-built data source

To add a MySQL custom-built data source:

  1. 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.

  1. On the Where is your data? page, select MySQL.
  2. On the Choose a pre-built or custom connection 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. 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.
  7. Click Continue.
  8. Name, choose refresh settings, and, optionally, share your data source.
  9. 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 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.

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:

Have more questions? Submit a request