1 Reply Latest reply on Mar 2, 2017 2:49 PM by Tom W

    MySQL custom queries returning no data

    Charles Ayotte-Trépanier

      Hi,

       

      I'm not familiar with MySQL, but as a Tableau admin, users sometime come to me to ask to optimize their reports (using MySQL data.)

       

      And as a Tableau admin, I always try to put as much of the work on the database. My first step when optimizing a report is always to modify the query to add the calculated fields, table calculations etc.

       

      Anyway, it's been twice now that I've been asked to optimize reports sourcing their data from MySQL. Both times, my custom sql wouldn't return anything (when a result set was expected, of course.) The first time, I gave up on modifying the query and optimized the workbook. This time, I really have to modify the query (which, right not, is just a 'drag/drop' of 1 table in the Tableau Desktop interface.)

       

      For example, I have a query that looks like the one below, and wouldn't return anything:

       

      select

           a.col1,

           b.col2

      from tableA a

      join tableB b

           on a.colX = b.colY

       

      Yet, the following query would return data (just by changing the selected columns):

       

      select

           a.col1

      from tableA a

      join tableB b

           on a.colX = b.colY

       

       

      I highly doubt that my issue is related to Tableau, but maybe some of you who are more familiar with MySQL could help me figure how I could troubleshoot my issue.

       

      Thanks!

        • 1. Re: MySQL custom queries returning no data
          Tom W

          Seems unusual and most likely unrelated to Tableau. To prove that, use a query tool external to Tableau to create your custom SQL - I think it's called Workbook for MySQL.

          If it's not working in the query tool then it won't work in Tableau either.

           

          Some additional hints;

          > Reduce the scope of the query and see if it works. Run SELECT TOP 100 a.col1, a.col2 ....... to only get 100 rows. Does that work?

          > Do you have a DBA on site? Given that you aren't overly familiar with SQL, I think it's a bad idea to be optimizing things using custom SQL in Tableau. I personally think Custom SQL in Tableau is just a bad practice when collaborating with user users. I'd much prefer to get a DBA who knows what they are doing to create a view in your database which makes the joins correctly.

          > You may need to optimize this query by adding indexes to tables etc. This is DBA sort of stuff, you're going to get over your head quickly here!