Trouble connecting to MySQL database

[nscheaffer]

We are on 4.1 SP01 Patch 2.  I am trying to create a universe based on a MySQL database that is version 5.1.61.  I am using an ODBC data source to connect.  My desktop where I am running the Information Design Tool is a 64-bit machine with Windows 7 Professional.  The server where the CMS is installed is also a 64-bit machine and has Windows Server 2008 R2 Enterprise Service Pack 1.

It seems that I could only create a new Relational Connection if I had a 64-bit ODBC data source on the server.  When I clicked the "Test Connection" button as part of the connection creation process it was successful. However, when I opened my newly created connection and clicked the "Test Connection" button it failed.

So I created a 64-bit ODBC data source on my desktop and then when I clicked "Test Connection" it failed again, but for a different reason.

Then I created a 32-bt ODBC data source and clicking the "Test Connection" button results in success.

I also created a Relation Connection Shortcut based on this new relational connection with that same errors along the way and ultimate success.

So things should be good to go, right?.  I created a Data Foundation based on the relational connection shortcut.  That seemed to work fine, but when I attempt to explore the data foundation I get a cryptic error message, "Error on FreeCursor".

I created a 32-bit ODBC data source on the server, but that made no difference.

So I have two basic questions.

1.  Why do I need a 64-bit ODBC data source on the server, but a 32-bit one on my machine?

2.  How do I get past this "Error on FreeCursor" error?

Thanks,

Noel

[Former Member · Accepted solution]

Can you check if the user id has all the access to the schema and tables in mysql database?  Normally when the connection is established there should not be any more issue while connecting  a universe..

[nscheaffer · Accepted solution]

The user id can see the tables and select from them via MySQL Workbench (querying tool).  I am not sure what else to check.  I may try to connect from Crystal Reports 2013 just to see if I can.

Noel

[nscheaffer · Accepted solution]

I am able to connect to the MySQL database from my workstation with Crystal Reports 2013 using the 32-bit ODBC connection. 

Noel

[Former Member · Accepted solution]

So you can see all the tables from Crystal report ?

[nscheaffer · Accepted solution]

Yes.  I can successfully pull the data into a report.

I am exploring the possibility of a JDBC connection rather than an ODBC connection.

Noel

[Former Member · Accepted solution]

Can you check if you can get it working from the Universe Design Tool?

[nscheaffer · Accepted solution]

The connection tests successfully in IDT.  However, when I try to view the tables I get the "Error on FreeCursor" message.

Noel

[former_member188911 · Accepted solution]

Hi Noel,

Durga is right, client tools use the 32bit drivers, whilst the server uses 64bit ones.

The reason is because client tools are compiled for that architecture.

In BI4.1 the supported ODBC middleware is MySql  ODBC 5.1.8, you may wish to try that

thanks

Simone

[nscheaffer · Accepted solution]

You are AMAZING!  I installed the MySQL Connector/ODBC version 5.1.8 from the following link and it works.

http://downloads.mysql.com/archives/c-odbc/

I am able to see the table and query data.

So I have to ask, should I have known to use that version of the MySQL connector?  Where could I have found this information?

This answer is worth1,000 points to me, but alas I can only give you 10! 

Thank you,

Noel

[former_member188911 · Accepted solution]

Hi Noel,

I don't care much about points, I am just happy when I am able to help.

The information you were looking for are in the PAM - Supported Platforms document.

You can access them either from https://service.sap.com/pam or from this document

I will probably create a wiki page on how to consume this document, in the meantime I try to give a general explanation here

Once you open the Supported Platforms document, you need to find the Semantic Layer section, where there is a hyperlink to the document for the supported reporting databases.

This is an excel spreadsheet, there are different tabs depending on the type of connectivity.

As you get familiar with it you will find surprising things... what methods are supported and which are not, helpful additional information and so on.

Thanks!

Simone

[nscheaffer]

Anybody have any thoughts on this?

Noel

[Former Member]

The BI 4.1 is a 64 bit installation so it only uses 64 bit ODBC ... However all the client tools including the IDT are 32 bit in nature so when you are trying to connect from your machine using IDT it will need a 32bit ODBC to connect to the database..

Hope that clarified the first question...