community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Setting up ODBC Connection to MySQL database through the gallery

Fireball

I'm trying to set up a connection to a MySQL database through the gallery so it can be shared with my group. I have the connection functioning on a single machine, but want to share this connection.

 

It seems in the gallery there is only data connection options for MS SQL Server, Oracle or Other. Going with 'Other' I try the connection string I found on https://www.connectionstrings.com/mysql/

 

Driver={MySQL ODBC 5.2 UNICODE Driver};Server=localhost;Database=myDataBase;
User
=myUsername;Password=myPassword;Option=3;

 

I fill in everything there, save the data connection, add my users, and then none of the users can see it in their designer. There's also no way to test the connection?

 

What am I doing wrong?

Inactive User
Not applicable

Try adding odbc: to the front of the connection string to get it to work.

Check this...ODBC Connection

Alteryx Certified Partner
Alteryx Certified Partner

As @Inactive User alloudes to, your connection string isn't quite complete; here is the connection string for MySQL 8.0 ANSI, as you see you need to prefix ODBC

 

ODBC:DRIVER={MySQL ODBC 8.0 ANSI Driver};Server=ServerName;Database=databasename;Uid=Username;Pwd=Password;

 

SOMETHING THAT IS KEY IS BOTH THE SERVER AND YOUR USERS MUST HAVE THIS DRIVER INSTALLED, OR THE CONNECTION WILL FAIL.

 

Once you have saved your connection you need to share it with users so it is 'permissioned' to the right private studios/users.

 

This can be done by editing the data connection and navigating to the 'Users and Studios' tabs.

 

2019-02-05_07-39-51.png

In order to use this connection from within designer, first of all, the users must have permission to access it.

 

They must also have a 'logged in' session to your Alteryx Gallery (which you can do by either 'Open Workflow' > 'My Gallery' > 'Login' or through 'Save As' > 'My Gallery' > 'Login' and then close.

 

Your users can then go to the 'manage data connections' window which is available by going to 'Options' > 'Advanced Options' and then once you navigate to the 'manage data connections' window they should hit sync. If you find this doesn't make the connection available then I would restart Alteryx Designer.

 

Your users should then be able to make use of these connections via the 'Saved Database Connections' option within the input tool.

 

Ben

 

 

 

 

Fireball

Thankfully I have a small amount of licenses to manage so it wasn't a big deal putting the connection on each computer.

 

The connection finally works with the string

odbc:DRIVER={MySQL ODBC 5.3 Unicode Driver};server=MYSERVER;database=MYDB;USER=MYUSER;PASSWORD=MYPASSWORD;

 

It turns out that I had to install the ODBC driver on all the machines anyway (which is why it was working for me but not my colleagues) it was just as easy to add the database to their system while I did the install rather than use Alteryx to distribute the details. We've also since broken up access to the database by user, so each user would need to have their own shared connection, it's just as easy to hard code it for them.

 

I appreciate everyone's help, things like this are obviously not too bad to get 90% of the way, but that last 10% is **bleep** mired in the teeny details.

Labels