cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gnumarco
Helper I
Helper I

ODBC Problem with mongoDB BI connector (MySQL)

Hi everybody.

Since 1 month, I am not able to load more than 500,000 (I have 590,000 more or less) lines from MongoDB using the ODBC connector. Let me explain: I use the lastest release of MongoDB 3.6 and I have installed the MongoDB BI connector, that means, I use the ODBC option of PowerBI to get data, as the Mongo BI connector fakes a MySQL ODBC connection.

The last 4 months, all went smoothly and I was able to load my 4,000,000 lines from MongoDB, but this month, the Refresh Data gives me an error (ODBC Driver Error) at the end of the import. That is to say, it reads all the lines, but when it finishes, it just crashes.

 

The Error is:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error[DataSource.Error] ODBC: ERROR [22018][MySQL][ODBC 5.3 (a) Driver][mysqld-5.7.12 mongosqld v2.4.1].'.

 

Digging a bit, the ODBC error 22018 seems to be 2invalid character value for cast specification"...

The very strange thing is that when I try this using Excel: conecting to the exact same DSN, importing the exact same table, it reads the 590,000 lines without problem.

I have tested in PowerBI, it works for 480,000 lines but crashes for 590,000.

I have no idea about what could be the problem, and why it worked for 4,000,000 the last months.

 

Considering the type of the error, I don't really know if the number of lines is the cause of the problem...

 

Thanks!

1 ACCEPTED SOLUTION

Hi Lydia.

Indeed, I found the solution. And it is related to character encoding.

MySQL provides 2 ODBC drivers, ANSI driver and the Unicode driver. In the MongoDB documentiation, they advise to use the ANSI driver, but this is what provoques the error.

It seems to work fine with the Unicode Driver.

 

Thanks for your answer!

View solution in original post

8 REPLIES 8
elflakoconk
Helper I
Helper I

Hello there,

I got this same issue, and also tried with both options (ANSI - unicode), someone have solved in other way?

thanks a lot!

error_mongo.png

 

dvarun
Regular Visitor

I'm facing the exact same error that MuthuLakshmi mentioned in the above comments and I've tried both the drivers still facing the same issue. I've wasted 2 days and found nothing, can somebody help?

 

Muthulakshmi-

"I'm able to connect to MongoDB, but while browsing through the tables in "Navigator" it is throwing the below error message:

 

DataSource.Error: ODBC: ERROR [HY000] [MySQL][ODBC 1.2(w) Driver][mysqld-5.7.12 mongosqld v2.12.0]Unknown database ''

Details:
DataSourceKind=Odbc
DataSourcePath=dsn=B1 Unicode DSN
OdbcErrors=Table

 

Tried with both drivers ANSI and Unicode. Kindly help."

 

TIA

Varun

v-yuezhe-msft
Microsoft
Microsoft

@gnumarco,

The above error is related to collation on MongoDB, what is the collation of your query? Do you have any foreign characters in the MongoDB data source?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia.

Indeed, I found the solution. And it is related to character encoding.

MySQL provides 2 ODBC drivers, ANSI driver and the Unicode driver. In the MongoDB documentiation, they advise to use the ANSI driver, but this is what provoques the error.

It seems to work fine with the Unicode Driver.

 

Thanks for your answer!

View solution in original post

thank you ! this helped me as well. i was pulling my hair out for 3 days straight !

AIW
Frequent Visitor

I had almost the same error and switching to a Unicode DSN also worked for me.

I'm able to connect to MongoDB, but while browsing through the tables in "Navigator" it is throwing the below error message:

 

DataSource.Error: ODBC: ERROR [HY000] [MySQL][ODBC 1.2(w) Driver][mysqld-5.7.12 mongosqld v2.12.0]Unknown database ''

Details:
DataSourceKind=Odbc
DataSourcePath=dsn=B1 Unicode DSN
OdbcErrors=Table

 

Tried with both drivers ANSI and Unicode. Kindly help.

@gnumarco,

Glad to hear the issue is solved, you can accept your reply as answer to close this thread.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors