Bug #15982 Crystal Reports 8.5 doen't work with MyODBC 3.51.12
Submitted: 26 Dec 2005 11:13 Modified: 24 Jul 2006 8:51
Reporter: Renato Gontijo Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Windows (Windows 2000 Pro)
Assigned to: CPU Architecture:Any

[26 Dec 2005 11:13] Renato Gontijo
Description:
I'm testing version 5.0.16 of MySQL using MyODBC 3.51.12. I don't have any problem to access database by ADO but when I try to connect to database through Crystal Report (CR) 8.5 the program crash.

If I use an old release of MyODBC (like 3.51.11-2) the CR works but sometimes the MySQL crash (I not discover why, yet).
I would like that you verify why this happens.

Thanks!

P.S.: I include the following parameter on my.ini
set-variable = lower_case_table_names=2

How to repeat:
Create a database whith some tables (include a primary key with more than one field). For example:

CREATE DATABASE MYTEST;

CREATE TABLE if not exists TABLETEST_A (
	CD1             INT NOT NULL,
	CD2             INT NOT NULL,
	CD3             INT NOT NULL,
	CD4             INT NOT NULL,
	IN               CHAR(1) NOT NULL
) TYPE=INNODB;

ALTER TABLE TABLETEST_A ADD 
	CONSTRAINT TABLETEST_A_PK PRIMARY KEY
	(
              CD1,
              CD2,
              CD3,
              CD4,
              IN
	)   ;

Now, create a System DSN called DbTest on ODBC Data Source on control panel to access the database MYTEST.

Try to connect by crystal report on database using the DSN DbTest. The program crash.
[26 Dec 2005 12:15] Valeriy Kravchuk
Thank you for a problem report. It looks very similat to bug #6539. Please, check.
[26 Dec 2005 17:09] Renato Gontijo
1. The mysql server was started succeded but when I execute myTest.exe and it generate an error.

I execute the test on
C:\Program Files\MySQL\MySQL Server 5.0\examples\libmysqltest\myTest.exe and I had the following message:

sizeof( MySQL ) == 952
Can't connect to mysql server on port 3306 !

==================== my.ini ====================

[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=8M
table_cache=256
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=185K
sort_buffer_size=139K
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=10M
innodb_thread_concurrency=8
set-variable = lower_case_table_names=2
[26 Dec 2005 17:13] Renato Gontijo
This error doesn't happen on myodbc 3.51.11-2 version only with 3.51.12.
[19 Jan 2006 17:11] Valeriy Kravchuk
Have you checked that http://bugs.mysql.com/bug.php?id=6539? I want simply mark this report as a duplicate, if you have no real arguments to thew contrary.

As for your last test (run myText.exe):

1. It has nothing to do with the original bug you reported.
2. It proves nothing at all. Just look at the code. It tries to connect as follows:

mysql_real_connect( myData, NULL, NULL, NULL, NULL, MYSQL_PORT,  NULL, 0 ) )

It just can not connect without expliciut user and password (as you had not set them in your my.ini file).

So, forget about it.
[5 Feb 2006 14:59] Renato Gontijo
echnical report generated by windows

Attachment: 823b_appcompat.txt (text/plain), 20.97 KiB.

[5 Feb 2006 14:59] Renato Gontijo
When I connect to Crystal Reports 8.5 The following error happens.
I attach file generated by windows with techinical information.

Error Signature

AppName: crw32.exe	 AppVer: 8.5.3.975	 ModName: ntdll.dll
ModVer: 5.1.2600.2180	 Offset: 00010f29

This error was generated on windows XP but this happens on Windows 2000 Pro too.
[21 Feb 2006 7:38] Valeriy Kravchuk
I had not found myodbc3.dll or any other MySQL-related .dll mentioned in the file you had uploaded. So, I am still not sure this problem is related to MyODBC. Can you work with any other, non-MySQL DSN?
[21 Feb 2006 13:53] Renato Gontijo
I'm search for this bug in google and I found something with crystal and mysql.
In windows registry:
HKEY_CURRENT_USER\Software\Seagate Software\Crystal Reports\DatabaseOptions\ODBC\OuterJoin

Sql2OuterJoin=libmyodbc3

the sql generated by crystal is
SELECT
    EMPRESA.`CDEMPRESA`, EMPRESA.`NMEMPRESA`,
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1`, PLCONTASNIVEL1.`NMPLCONTASNIVEL1`, PLCONTASNIVEL1.`INACEITAVALOR1`,
    PLCONTASNIVEL2.`CDPLCONTASNIVEL1`, PLCONTASNIVEL2.`CDPLCONTASNIVEL2`, PLCONTASNIVEL2.`NMPLCONTASNIVEL2`, PLCONTASNIVEL2.`INACEITAVALOR2`,
    PLCONTASNIVEL3.`CDPLCONTASNIVEL1`, PLCONTASNIVEL3.`CDPLCONTASNIVEL2`, PLCONTASNIVEL3.`CDPLCONTASNIVEL3`, PLCONTASNIVEL3.`NMPLCONTASNIVEL3`, PLCONTASNIVEL3.`INACEITAVALOR3`,
    PLCONTASNIVEL4.`CDPLCONTASNIVEL1`, PLCONTASNIVEL4.`CDPLCONTASNIVEL2`, PLCONTASNIVEL4.`CDPLCONTASNIVEL3`, PLCONTASNIVEL4.`CDPLCONTASNIVEL4`, PLCONTASNIVEL4.`NMPLCONTASNIVEL4`, PLCONTASNIVEL4.`INACEITAVALOR4`
FROM
oj{ (((`EMPRESA` EMPRESA INNER JOIN `PLCONTASNIVEL1` PLCONTASNIVEL1 ON
        EMPRESA.`CDEMPRESA` = PLCONTASNIVEL1.`CDEMPRESA`)
     LEFT OUTER JOIN `PLCONTASNIVEL2` PLCONTASNIVEL2 ON
        PLCONTASNIVEL1.`CDEMPRESA` = PLCONTASNIVEL2.`CDEMPRESA` AND
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1` = PLCONTASNIVEL2.`CDPLCONTASNIVEL1`)
     LEFT OUTER JOIN `PLCONTASNIVEL3` PLCONTASNIVEL3 ON
        PLCONTASNIVEL2.`CDEMPRESA` = PLCONTASNIVEL3.`CDEMPRESA` AND
    PLCONTASNIVEL2.`CDPLCONTASNIVEL1` = PLCONTASNIVEL3.`CDPLCONTASNIVEL1` AND
    PLCONTASNIVEL2.`CDPLCONTASNIVEL2` = PLCONTASNIVEL3.`CDPLCONTASNIVEL2`)
     LEFT OUTER JOIN `PLCONTASNIVEL4` PLCONTASNIVEL4 ON
        PLCONTASNIVEL3.`CDEMPRESA` = PLCONTASNIVEL4.`CDEMPRESA` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL1` = PLCONTASNIVEL4.`CDPLCONTASNIVEL1` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL2` = PLCONTASNIVEL4.`CDPLCONTASNIVEL2` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL3` = PLCONTASNIVEL4.`CDPLCONTASNIVEL3`}
WHERE
    EMPRESA.`CDEMPRESA` = 1
ORDER BY
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1` ASC,
    PLCONTASNIVEL2.`CDPLCONTASNIVEL2` ASC,
    PLCONTASNIVEL3.`CDPLCONTASNIVEL3` ASC,
    PLCONTASNIVEL4.`CDPLCONTASNIVEL4` ASC

when I alter key in registry the sql is altered to:
SELECT
    EMPRESA.`CDEMPRESA`, EMPRESA.`NMEMPRESA`,
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1`, PLCONTASNIVEL1.`NMPLCONTASNIVEL1`, PLCONTASNIVEL1.`INACEITAVALOR1`,
    PLCONTASNIVEL2.`CDPLCONTASNIVEL1`, PLCONTASNIVEL2.`CDPLCONTASNIVEL2`, PLCONTASNIVEL2.`NMPLCONTASNIVEL2`, PLCONTASNIVEL2.`INACEITAVALOR2`,
    PLCONTASNIVEL3.`CDPLCONTASNIVEL1`, PLCONTASNIVEL3.`CDPLCONTASNIVEL2`, PLCONTASNIVEL3.`CDPLCONTASNIVEL3`, PLCONTASNIVEL3.`NMPLCONTASNIVEL3`, PLCONTASNIVEL3.`INACEITAVALOR3`,
    PLCONTASNIVEL4.`CDPLCONTASNIVEL1`, PLCONTASNIVEL4.`CDPLCONTASNIVEL2`, PLCONTASNIVEL4.`CDPLCONTASNIVEL3`, PLCONTASNIVEL4.`CDPLCONTASNIVEL4`, PLCONTASNIVEL4.`NMPLCONTASNIVEL4`, PLCONTASNIVEL4.`INACEITAVALOR4`
FROM
`EMPRESA` EMPRESA INNER JOIN `PLCONTASNIVEL1` PLCONTASNIVEL1 ON
        EMPRESA.`CDEMPRESA` = PLCONTASNIVEL1.`CDEMPRESA`
     LEFT OUTER JOIN `PLCONTASNIVEL2` PLCONTASNIVEL2 ON
        PLCONTASNIVEL1.`CDEMPRESA` = PLCONTASNIVEL2.`CDEMPRESA` AND
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1` = PLCONTASNIVEL2.`CDPLCONTASNIVEL1`
     LEFT OUTER JOIN `PLCONTASNIVEL3` PLCONTASNIVEL3 ON
        PLCONTASNIVEL2.`CDEMPRESA` = PLCONTASNIVEL3.`CDEMPRESA` AND
    PLCONTASNIVEL2.`CDPLCONTASNIVEL1` = PLCONTASNIVEL3.`CDPLCONTASNIVEL1` AND
    PLCONTASNIVEL2.`CDPLCONTASNIVEL2` = PLCONTASNIVEL3.`CDPLCONTASNIVEL2`
     LEFT OUTER JOIN `PLCONTASNIVEL4` PLCONTASNIVEL4 ON
        PLCONTASNIVEL3.`CDEMPRESA` = PLCONTASNIVEL4.`CDEMPRESA` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL1` = PLCONTASNIVEL4.`CDPLCONTASNIVEL1` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL2` = PLCONTASNIVEL4.`CDPLCONTASNIVEL2` AND
    PLCONTASNIVEL3.`CDPLCONTASNIVEL3` = PLCONTASNIVEL4.`CDPLCONTASNIVEL3`
WHERE
    EMPRESA.`CDEMPRESA` = 1
ORDER BY
    PLCONTASNIVEL1.`CDPLCONTASNIVEL1` ASC,
    PLCONTASNIVEL2.`CDPLCONTASNIVEL2` ASC,
    PLCONTASNIVEL3.`CDPLCONTASNIVEL3` ASC,
    PLCONTASNIVEL4.`CDPLCONTASNIVEL4` ASC

all parenthesis is removed the query and generate the error and close the program.
[27 Feb 2006 21:26] Jorge del Conde
Hi!

I am nuable to reproduce this bug.  Is there any way you can test this out in a different machine ?

Several crystal reports bugs where fixed in 3.51.12 and it all seems to be working fine on my system.
[27 Feb 2006 22:43] Renato Gontijo
example 1 made in crystal

Attachment: my_ex1.jpg (image/jpeg, text), 101.68 KiB.

[27 Feb 2006 22:44] Renato Gontijo
example 2 made in crystal (1 of 2)

Attachment: my_ex2_01.jpg (image/jpeg, text), 94.18 KiB.

[27 Feb 2006 22:44] Renato Gontijo
example 2 made in crystal (2 of 2)

Attachment: my_ex2_02.jpg (image/jpeg, text), 136.35 KiB.

[27 Feb 2006 22:44] Renato Gontijo
First Way... (my_ex1)

ok... it assumes that I have the following table structure.

-- fac --
* cdfac  int  -> pk
° nmfac varchar(50)

-- table 1 --
* cdfac  int   -> pk
* cdtb1  int   -> pk
° nmtb1 varchar(50)

-- table 2 --
* cdfac  int  -> pk
* cdtb1  int  -> pk
* cdtb2  int  -> pk
° nmtb2 varchar(50)

-- table 3 --
* cdfac  int  -> pk
* cdtb1  int  -> pk
* cdtb2  int  -> pk
* cdtb3  int  -> pk
° nmtb3 varchar(50)

the "table3" had "table2" that had "table1". Doesn't create any foreing key, but this tables have a join (logical join ;-) ok).

If I create a report and relate the tables it's work. But if I alter the following key in WindowsRegistry:

HKEY_CURRENT_USER\Software\Seagate Software\Crystal
Reports\DatabaseOptions\ODBC\OuterJoin

Sql2OuterJoin=libmyodbc3

The errors happens!
(see file my_ex1.zip)

Second Way... (my_ex2)

supore that I have the following tables.

-- fac --
* cdfac   -> PK
° nmfac varchar(50)

-- customer
* cdfac   -> PK
* cdcus  -> PK
° nmcus varchar(80)

-- bill --
* cdfac  -> PK
* cdbil  -> PK
* nmbil varchar(50)

create a report with and relate like picture (attached). create an empty formula, and using ocx of crystal pass the following value to formula.
.formula(0)="nm_value={custumer.nmcus}"

When I execute the report the error occurs

Error 20534 - Error detected by database dll.

If I alter the value in Windows registry:

HKEY_CURRENT_USER\Software\Seagate Software\Crystal
Reports\DatabaseOptions\ODBC\OuterJoin

Sql2OuterJoin=libmyodbc3

And after execute the report again its works, but the first report stop and do the same error.

I will attach some pictures. I guess its helps you.
[24 Jul 2006 8:51] Tonci Grgin
Hi Renato. I was not able to repeat the behavior you described.

MySQL server 5.0.25-log BK on Suse 10 host
Win XP Pro SP2 with CR 11 (older version was unavailable)
MyODBC 3.51.12 GA

Probably you should just upgrade your CR.
[24 Jul 2006 8:53] Tonci Grgin
MyODBC DSN

Attachment: 15982-2.jpg (image/jpeg, text), 38.96 KiB.

[24 Jul 2006 8:55] Tonci Grgin
CR report

Attachment: 15982.jpg (image/jpeg, text), 90.41 KiB.