SAS/MySQL connects to MySQL server db, but I cannot see the tables

[chipican]

Hello all,

I was able to connect to a MySQL server using ssh and a terminal, and was able to see all the tables in my database.

However, when I assign a libname in SAS to access the MySQL tables from within SAS, the libname is correctly assigned but no table is visible in the library.

The libname statement I used is:

libname mydb mysql SERVER='cp-dmz-wps' PORT=23074 DATABASE=ract_RAVE_XXXX_PROD
USER=ract PASSWORD=xxxxxxxxxxxxx
INSERTBUFF=100 DBMAX_TEXT=255 access=readonly;

This statement returns no error, only a note indicating that the library was assigned:

 NOTE: Libref MYDB was successfully assigned as follows:
       Engine:        MYSQL
       Physical Name: cp-dmz-wps

Has anyone already experienced this issue of being able to connect to a MySQL server but not seeing the tables?

Any help would be greatly appreciated.

[JBailey]

Hi @chipican and @oswin_fox

Typically this is due to an issue with SCHEMA= not matching up but...

MySQL has some quirks. Keep in mind, I am no MySQL expert but it seems odd that your ssh and terminal connection works while connecting from SAS does not.

It can also be a case where the USER= is slightly different.

Connect to your database using ssh and terminal. Run the following SQL commands:

select user();
select current_user();
select database();

Now do the same thing from SAS:

libname mydb mysql SERVER='cp-dmz-wps' PORT=23074 DATABASE=ract_RAVE_XXXX_PROD
USER=ract PASSWORD=xxxxxxxxxxxxx
INSERTBUFF=100 DBMAX_TEXT=255 access=readonly;

proc sql;
 
   connect using mydb;
 
   select * from connection to mydb 
      (select user());
   select * from connection to mydb 
      (select current_user());
   select * from connection to mydb
      (select database());
quit;

 Hopefully, they don't match and that will help you solve the problem. Feel free to post the results to this thread and perhaps we can help you sort it out.

Best wishes,

Jeff

[oswin_fox]

Hi @JBailey and @chipican,

I tried as you ask with ssh and in SAS.

From ssh:

mysql> select user();
+----------------+
| user()         |
+----------------+
| ract@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| ract@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select database();
+------------------------------+
| database()                   |
+------------------------------+
|ract_RAVE_XXXX_PROD|
+------------------------------+

From SAS:

As for SSH and SAS it use the user ract. Anything I am missing?

Any help would be greatly appreciated. Thanks

[JBailey]

Hi @oswin_fox

I think we have a clue as to the problem. MySQL accounts can be tied to the location where the connection is coming from. This means that the LIBNAME statement isn't using the same credentials as SAS. You are able to connect from nnnnn@localhost but not from a remote connection nnnnn@some_IP_address.

Show this information to your DBA. Hopefully, they can grant access to the id coming from the SAS machine.

Best wishes,

Jeff 

[oswin_fox]

Hello @JBailey

We checked the rights on MYSQL server:

mysql> show grants for 'ract'@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for ract@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'ract'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

the % mean any. The connection is working, because if we try to show all the tables we see them, the issue is that in the left @chipican cannot see the tables in the libraries menu, for example:

So after use (for example) the code in SAS:

libname mydb mysql SERVER='cp-dmz-wps' PORT=23074 DATABASE=ract_RAVE_XXXX_PROD
USER=ract PASSWORD=xxxxxxxxxxxxx
INSERTBUFF=100 DBMAX_TEXT=255 access=readonly;

we expected to get in libraries -> mydb to see the tables...

@KsharpCan you please inform me about this option? SCHEMA=dbo ? Not familiar with SAS code.

[JBailey · Accepted solution]

Hi @oswin_fox

For grins let's use this LIBNAME statement and see what happens.

libname mydb mysql SERVER='cp-dmz-wps' PORT=23074 DATABASE=ract_RAVE_XXXX_PROD
USER=ract PASSWORD=xxxxxxxxxxxxx
INSERTBUFF=100 DBMAX_TEXT=255 access=readonly
dbconinit="set names utf8";

Update! DBCONINIT= turned out to be the solution here!

dbo is a schema that exists in Microsoft SQL Server. It won't play into MySQL. It is so very easy to look at MySQL and see MSSQL. I do it all the time. All the time. 

@Ksharp is probably saying try setting the SCHEMA= option to something pertinent to your environment and is using dbo (Database Owner) as an example.

Are your table names case-sensitive? The following SAS code will show the SQL that SAS is sending to MySQL. It may be worth trying it to see what is going on. 

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Another thing to try is connecting from the remote machine using a different program - for example: MySQL Workbench.

Best wishes,

Jeff

[oswin_fox]

Hey @JBailey

this option was the solution:

dbconinit="set names utf8";

Thank you so much :-0 and thank you too for this debug option:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Surly one I will use in the future for other SAS problem 🙂

[JBailey]

Hi @oswin_fox and @chipican

This is great news! I may write a communities article on this. There is a lot of goodness in the thread...

@chipican, If you don't mind, can you make my response as the solution so that it will help others?

Thanks for the learning opportunity!

Best wishes,

Jef

[oswin_fox]

Hey @JBailey

this option was the solution:

dbconinit="set names utf8";

 Thank you so much :-0 and thank you too for this debug option:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Surly one I will use in the future for other SAS problem 🙂

[Ksharp]

Add an option ?

SCHEMA=dbo 

[chipican]

Thank you very much all: @Ksharp for your help, @JBailey for finding the final solution, and @oswin_fox for following up on this issue while I was off.

Have a nice day!