BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chipican
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

View solution in original post

10 REPLIES 10
JBailey
Barite | Level 11

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
Fluorite | Level 6

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:

Screenshot_2018-10-02_09-44-37.png

 

 

 

 

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

 

Any help would be greatly appreciated. Thanks

 

 

JBailey
Barite | Level 11

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
Fluorite | Level 6

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:

 

Screenshot_2018-10-02_14-31-12.png

 

 

 

 

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
Barite | Level 11

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
Fluorite | Level 6

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
Barite | Level 11

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
Fluorite | Level 6

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
Super User

Add an option ?

 

SCHEMA=dbo 

chipican
Fluorite | Level 6

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!

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 3093 views
  • 1 like
  • 4 in conversation