Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Error connecting to MySQL from Visual Analytics

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Error connecting to MySQL from Visual Analytics

Hi

 

I have SAS/ACCESS ODBC licence through which I am trying to connect to MySQL database. name of the database is "repairsdb". I have below entry created in my obbc.ini file:

 

[SOLVUP]
Driver = OracleODBC-10g
Server = 10.1.8.138
DATABASE = repairsdb
Port = 3306
UID = repairsdbuser
Password = repairspw

 

When I try to create a access it using LIBNAME statement with dsn=SOLVUP User= repairsdbuser pwd =repairspw, it throws error "do not have access rights to repairsdbuser@localhost"(PASSWORD USE =YES). 

 

If i remove the Pwd parameter, LIBNAME is successfully assigned but when I try to access any table in data set, I get an error "no database selected" .

 

Could you please suggest how to make a successful connection ?

 

Regards

Avinash


Accepted Solutions
Solution
‎12-29-2016 04:02 AM
Occasional Contributor
Posts: 11

Re: Error connecting to MySQL from Visual Analytics

This is fixed now. I modified the odbcisnt.ini and odbc.ini files at etc and /opt/sas/SASHome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver . Also, added envionment variables in file sasenv_local .

View solution in original post


All Replies
Frequent Contributor
Posts: 96

Re: Error connecting to MySQL from Visual Analytics

first of all,

this line " Driver = OracleODBC-10g"

 

confusing me in odbc.ini file,

are you sure you have configured proper driver information in odbcinst.ini and odbc.ini files ?

 

because for MySQL driver, you have mentioned OracleODBC-10g.

Occasional Contributor
Posts: 11

Re: Error connecting to MySQL from Visual Analytics

@Kalind_Patel Hi.. that was when I was trying different solutions. I also tried keeping Driver = MySQL . it still gavce same errors.

Frequent Contributor
Posts: 96

Re: Error connecting to MySQL from Visual Analytics

thanks for the clarification,

 

it looks like that you need some additional permission from MySQL database,

 

You can try to connect with mysql command in linux,

I'm sure it will give you the same error,

 

 

In MySQL,

try to tun this command :

SHOW GRANTS FOR 'repairsdbuser'@'localhost';

 

and check what permission you are missing,

Sometimes we have to additionaly grant privilages to users like repairsdbuser@localhost

 

Your MySQL DBA can help you on this issue.

Solution
‎12-29-2016 04:02 AM
Occasional Contributor
Posts: 11

Re: Error connecting to MySQL from Visual Analytics

This is fixed now. I modified the odbcisnt.ini and odbc.ini files at etc and /opt/sas/SASHome/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver . Also, added envionment variables in file sasenv_local .

Post a Question
Discussion Stats
  • 4 replies
  • 256 views
  • 0 likes
  • 2 in conversation