5 Replies Latest reply on Aug 23, 2012 5:14 PM by Robert Morton

    Unable to connect Tableau with MySQL tables having dot in their name.

      Dear All,

       

      When I try to create data connection in tableau with MySQL table where table name contains “.” e.g. Product.Sales the connection dialog fails with error message “An error occurred validating custom SQL connection.”.

      I can query data from mentioned table using MySQL Shell client and HeidiSQL using following select queries.

           SELECT * FROM `Products.Sales`;
      1.png

       

       

           SELECT * FROM analysisDB.`Products.Sales`;

      2.png

           SELECT * FROM `analysisDB`.`Products.sales`;

      3.png

           SELECT `ProductID`,  `Month`, `Year`, `SaleQty` FROM `analysisDB`.`Products.sales`;

      8.png


           SELECT

      `analysisDB`.`Products.sales`.`ProductID`,

      `analysisDB`.`Products.sales`. `Month`,

      `analysisDB`.`Products.sales`.`Year`,

      `analysisDB`.`Products.sales`.`SaleQty`

           FROM `analysisDB`.`Products.sales`;

      10.png

       

      However in tableau I am unable to perform analysis on tables with `. ` in their names, even if I use Custom SQL feature with valid query.

      Following snapshots of tableau along with error dialog will help to understand the issue.

       

       

      4.png

       

      5.png

       

      6.png

       

      11.png

       

      9.png

       

      Can we have this issue fixed in future release? Or will it remain known issue.

       

      Regards,

      SMA

        • 1. Re: Unable to connect Tableau with MySQL tables having dot in their name.
          Matt Francis

          Its not a fix as such but using "." in a database table name is a really bad idea and shouldn't really be used as it causes just this sort of issue. Are you in a position to rename the tables to use an underscore instead?

          • 2. Re: Unable to connect Tableau with MySQL tables having dot in their name.

            Matt: I  agree with you regarding naming convention, however I am dealing with db  of a a legacy product (and change in table names will compromise integrity of existing  product), so I have to live with this naming convention / schema, I already have done a lot of stuff in tableau (in context of my current project). It is really important for me to deliver this project with tableau (to save my past effort and resources ), so is there any possible solution / workaround to make it happen?

            • 3. Re: Unable to connect Tableau with MySQL tables having dot in their name.
              Matt Francis

              Would you be able to create a view in the database based on the current table but with different table names? ie

               

              mysql> create table `test.test` (id INT,data VARCHAR(200));

              Query OK, 0 rows affected (0.27 sec)

               

               

              mysql> insert into `test.test` values (1,'Hello World');

              Query OK, 1 row affected (0.00 sec)

               

               

              mysql> select * from test.test

                  -> ;

              ERROR 1142 (42000): SELECT command denied to user 'tableau_rw'@'seq1a.internal.sanger.ac.uk' for table 'test'

              mysql> create view test_test as (select * from `test.test`);

              Query OK, 0 rows affected (0.22 sec)

               

               

              mysql> select * from test_test;

              +------+-------------+

              | id   | data        |

              +------+-------------+

              |    1 | Hello World |

              +------+-------------+

              1 row in set (0.00 sec)

               

               

              mysql> insert into `test.test` values (2,'Hello World its nice to see you');

              Query OK, 1 row affected (0.23 sec)

               

               

              mysql> select * from test_test;

              +------+---------------------------------+

              | id   | data                            |

              +------+---------------------------------+

              |    1 | Hello World                     |

              |    2 | Hello World its nice to see you |

              +------+---------------------------------+

              2 rows in set (0.00 sec)

               

              Then you can connect Tableau to the view, which will be always the same data as the test.test table?

              • 4. Re: Unable to connect Tableau with MySQL tables having dot in their name.

                Matt: I am really thankful for your reply and workaround, and still I will like to hear from Tableau official person to confirm if they  will remove this limitation from future version or not? Because this is a valid use case to have names with special characters like "." or "$" or even Unicode and almost every DBMS and related tools support  naming with special characters or equivalent to  reserve words (e,g i can have tables named "select", "indert" or "sum"  under mysql).

                • 5. Re: Unable to connect Tableau with MySQL tables having dot in their name.
                  Robert Morton

                  Hi Shaukat,

                  I can't officially comment on our roadmap or a timeframe for any changes to our product. I also haven't tried to reproduce this issue yet. I will however state that this sounds like the same class of problems we have seen many times with the MySQL driver itself. Your direct connection is not subjected to interference from the driver, so your queries run properly. We are interested in enabling Tableau to work with newer versions of the MySQL driver (which may address this problem along with many others), but I do not know when this may happen.

                  -Robert