MySQL Bugs: #23436: MyODBC and Business Objects Designer

Bug #23436	MyODBC and Business Objects Designer
Submitted:	18 Oct 2006 17:33	Modified:	29 May 2013 12:16
Reporter:	Martyn Atkins	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	batch, batched, fix, SQL, workaround

Description:
When trying to design Business Objects Data Universes using Business Objects 6.1b (Designer), if you use MyODBC version >3.51.04 for the Universe connection it will always crash/dump.

Using 3.51.04 cures the problem but is too old a client for authenticating against our new MySQL 5.0 server installation.

In addition, on my machine it seems impossible to remove 3.51.12 and re-install 3.51.04 successfully which is a big problem.

How to repeat:
Try to design a Business Objects Universe using a connection using the generic ODBC Business Objects Network Layer and a MyODBC connection with drivers with a version higher than 3.51.04

It will crash with the following dump. Tthis error does not occur with MyODBC 3.51.04 but this driver does not support the newer authentication method of later versions of MySQL.

------------------------------------------------------------------
Unhandled Exception
  Code: c0000005
  Description: EXCEPTION_ACCESS_VIOLATION

Call stack:
------------------------------------------------------------------
Module  at   0x02cc0000: C:\WINDOWS\system32\myodbc3.dll  v 3.51.12.0 [English (United States)]
    address: 0x02cc51b2
    address: 0x02cc537c
Module  at   0x10000000: C:\Program Files\Business Objects\BusinessObjects Enterprise 6\dataAccess\RDBMS\legacy\sqbodbc.dll  v 61.21.0.226 [English (United States)]
    address: 0x1000a510
    address: 0x100119a0

It seems that this may be related to the MySQL diver not supporting batched sql which has been discussed elsewhere. I have managed to fix the problem by disabling array fecth size for Business Objects connections using ODBC and MyODBC 3.51.12

Business Objects help has this to say about array fetch sizes:

===========
Note the following points when setting Array fetch size:

If you enter 20, and your query retrieves 100 rows, the connection is executes 5 fetches to retrieve your data. Five fetches of 20 rows each equals 100 rows of data.

If you enter 1, the array fetch is deactivated and data is retrieved row by row.

Deactivating array fetch is the safest way of retrieving your data but row-by-row retrieval slows down server performance. The greater the value in the Array fetch size option, the faster your rows are retrieved; you must, however, ensure you have adequate client system memory.
===========

My previous defaults were 10 for all MyODBC connections and they worked fine with MyODBC 3.51.04, but started failing with 3.51.12

As using an array fetch size >1 and retrieving rows in batches speeds up access, it would be great if the MyODBC driver could be fixed to support this feature. I have lost many hours getting to the bottom of this and my fix is no more than a workaround which results in slower performance of my Business Objects connections.

I have uploaded an image file of the Business Objects connection setting that fixes the problem in case anyone elese can benefit from this workaround.

Regards,
Martyn Atkins

Business Objects Array fetch size setting as workaround fix

Attachment: settings.png (image/x-png, text), 5.93 KiB.

Hi Martyin and thanks for your report. It is impossible for me to have and properly use all the SW there is. So was the trouble with one you use. I have a suggestion for you, can you please try with MyODBC from ftp://ftp.mysql.com/pub/mysql/download/connectors/odbc/mysql-connector-odbc-3.51.13-beta-w...
and inform me of the result?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.