MySQL Bugs: #57777: Connector problems between mysql and crystal reports 8.5

Bug #57777	Connector problems between mysql and crystal reports 8.5
Submitted:	27 Oct 2010 16:05	Modified:	4 Jan 2011 23:16
Reporter:	Phil Gardner	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.92	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any

Description:
Reading thro' various other bugs this appears to be an on-going issues between Crystal Reports and mysql, so apologies for raising it again.

I'm trying to create a report using Crystal Reports version 8.5 from data held in mysql.  I've managed to get the mysql odbc connector (v5) working and am able to access the data tables without any issues.  The problems arise when I try and use data from more than one data table.  I've tried all the variations from previous bugs reported on changing the registry key with "PlusEqual"="myodbc5" etc with no success.  Typically the error message I get is:

 ODBC error:[MySQL][ODBC 5.1 Driver][mysqld-5.0.27-standard]you have an error in your sql syntax; check the manual that corresponds to MySQL server verion for the right syntax to use near '}ORDER BY timesheetdetails1.'absWK'ASC' at  line1.  

The SQL in Crystal is as follows:  

SELECT
    timesheetdetails1.`StaffID`, timesheetdetails1.`ProjectID`, timesheetdetails1.`absWk`, timesheetdetails1.`hours`, timesheetdetails1.`tsID`,
    staff1.`Surname`
FROM
    { oj `tmetcmisph3`.`timesheetdetails` timesheetdetails1 INNER JOIN `tmetcmisph3`.`staff` staff1 ON
        timesheetdetails1.`StaffID` = staff1.`staffID`}
ORDER BY
    timesheetdetails1.`absWk` ASC

your advise help would be greatly appreciated

How to repeat:
n/a

Hi Phil and thanks for your report.

What I see in your SQL is inconsistency in quoting:
    timesheetdetails1.`StaffID`
    `tmetcmisph3`.`timesheetdetails` timesheetdetails1 ...

What I'd do is to remove backticks completely, as they are unnecessary in your query, loose table alias 'timesheetdetails1' and use normal table name 'timesheetdetails' (same goes for 'staff1'), loose database prefix (`tmetcmisph3`.) and retest. You also need a space between last } and ORDER BY.

So, my SQL would look like:
SELECT
    timesheetdetails.StaffID, timesheetdetails.ProjectID,
timesheetdetails.absWk, timesheetdetails.hours,
timesheetdetails.tsID, staff.Surname
FROM
{ oj timesheetdetails INNER JOIN staff ON
        timesheetdetails.StaffID = staff.staffID} 
ORDER BY
    timesheetdetails.absWk ASC

There is also a possibility to write this query without {oj} syntax, see http://dev.mysql.com/doc/refman/5.5/en/join.html.

If it still fails, do a mysqldump of tables in question and attach it to the report so I can try. An ODBC trace from driver manager might help too (see our manual on how to obtain one).

As for the "latest and greatest", really don't know.

Hi Tonci
Sorry it’s taken a while to get back to you on this I've been busy with other things.
Firstly many thanks for your suggestions, unfortunately they haven’t resolved the problems.  
I’m not writing the SQL, crystal creates this in the background as you create the report and whilst some parts of it appear to be editable other are not ie the “select” section.  Sadly my knowledge of SQL / MySQL is non-existent, also I’ve only got read access to the data tables as they contain personal and confidential materials, it’s therefore un-likely that I’ll be able to send these to you.  
Looking at past posts on this issue the recommended resolutions all appear to hinge around making changes to registry entries please see http://bugs.mysql.com/bug.php?id=839  many of which I’ve tried, again un-successfully.  Also, and sadly, many of the knowledge base (support.Crystaldecisions.com) articles appear to be no longer available, so if you have any other suggestions I’m happy to give them a try.
Many thanks for help so far
Phil

Hi Phil,

I have no versions of CR older than 11 so I can't run this test specific to that client.  However removing CR entirely from the picture, and simply submitting that SQL syntax directly to Server 5.0.91 using the mysql command line client results in the exact same error.  

According to the 5.0 manual the { OJ ... LEFT OUTER JOIN ...} syntax should work there, but for some reason it isn't.  However it is working in both the 5.1 server and the 5.5 server.  In any event, I don't believe it has anything to do with registry entries.

Tonci,

I think this bug probably needs to be redirected to the server.  Until it is fixed though I think recommending a server upgrade is going to be the only way to resolve this quickly.  Do you concur?

Please also see http://bugs.mysql.com/bug.php?id=28317 for context

Valeriy, please check what's with original {OJ} server bug as it appears not to be working in 5.0...

If you suspect server bug here, please, provide complete test case (to copy/paste, not guess) that demonstrates it. 

Many simple cases do work for me with 5.0.91. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.91-community-nt MySQL Community Edition (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table a(a1 int);
Query OK, 0 rows affected (0.16 sec)

mysql> create table b(a1 int, b1 int);
Query OK, 0 rows affected (0.20 sec)

mysql> create table c(b1 int, c1 int);
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> SELECT * FROM  {OJ {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 } LEFT OUTER JOI
N c ON b.b1 =
    -> c.b1}
    -> ;
Empty set (0.08 sec)

mysql> SELECT * FROM {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 } LEFT OUTER JOIN c O
N b.b1 =
    -> c.b1;
Empty set (0.00 sec)

Phil, I guess Valeriy needs your table structures and not just query that fails.

Hi Valeriy,

Here is the commit comment from bug#28317:
"Parser rejects ODBC's escape sequences for outer joins other than left outer join, yet the escape sequence BNF specifies that this syntax can be used for left, right, and full outer join syntax."

With your tables, this query works:

SELECT * FROM  {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 };

This one does not in 5.0 (but works in later versions):

SELECT * FROM  {OJ  a INNER JOIN b ON a.a1=b.a1 };

This is 5.0-only server bug. Everything works as expected in 5.1+. With current mysql-.50 tree we have the following:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create table a(a1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table b(a1 int, b1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM  {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 };
Empty set (0.00 sec)

mysql> SELECT * FROM  {OJ  a INNER JOIN b ON a.a1=b.a1 };
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}' at line 1
mysql> SELECT * FROM  {OJ  a RIGHT JOIN b ON a.a1=b.a1 };
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}' at line 1
mysql> SELECT * FROM  a RIGHT JOIN b ON a.a1=b.a1;
Empty set (0.00 sec)

Issue is addressed in 5.1+ and will not be fixed in 5.0