none
Unable to read tables containing Guid from mySQL RRS feed

  • General discussion

  • I'm trying to get data out of a on-premise SugarCRM mySQL database using PowerQuery. I've installed the MySQL Connector Net 6.6.5 from Oracle.

    When connecting, it asks for a servername, database name, username and password - which succeeds. PowerQuery then shows me a list of tables. Some of these tables I can then open, and perform PowerQuery magic on it. Nice.

    But when trying to open other tables, I get the error: "Guid should contain 32 digits with 4 dashes".

    It seems (from various web searches) that this is a common problem when the mySQL database table has columns of type BINARY(16). The commonly suggested fix is to add "old guids=true" to your connections string. How would I do that with PowerQuery?

    The only place I can see that allows me to customize how PowerQuery connects to the mySQL server is the = MySQL.Database("192.168.1.239", "sugarcrm") statement, which does not seem to allow me to specify additional connection string parameters.

    Any suggestions?

    -Louis


    Wednesday, February 26, 2014 2:47 PM

All replies

  • We don't currently allow customizing the connection string, though we're currently looking at adding that ability.

    Meanwhile, I'm trying to reproduce this problem to understand it a little better. Simply creating a table with a "binary(16)" column doesn't seem to trigger the error; do you know how I can create a table that will cause the driver to produce this error?

    Wednesday, February 26, 2014 3:09 PM
  • Hi Louis, I'm currently running into the same problem using excel. I'm trying to import data from the SugarCRM database, but it won't allow me due to the same error.

    Did you find a solution for the connection string? As I'm desperately trying to get the job done for a client. 

    Thanks

    Mark

    Thursday, September 25, 2014 9:51 PM
  • We've added an option to MySQL.Database to accommodate this. You'll need to edit your formula to add an option record. The call should look like this:

    =MySQL.Database("servername", "databasename", [OldGuids=true])

    Friday, September 26, 2014 1:10 PM
  • We've added an option to MySQL.Database to accommodate this. You'll need to edit your formula to add an option record. The call should look like this:

    =MySQL.Database("servername", "databasename", [OldGuids=true])

    Where that OldGuids parameters should be inserted provided that I faced with the issue while using MySQL for Excel add-on?

    Thanks!

    Tuesday, October 21, 2014 11:51 PM
  • This is only relevant to Power Query, not to any other method for loading MYSQL data into Excel. If you're using Power Query, then you can edit the call to MySQL.Database either in the formula bar of the Power Query editor or from the "Advanced Editor" that's available from the ribbon.
    Wednesday, October 22, 2014 4:21 PM