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.
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
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
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
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
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.
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
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 🙂
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
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 🙂
Add an option ?
SCHEMA=dbo
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.