MySQL Bugs: #30965: Crystal Report 10 can't verify databases with underscore in MySQL-databasename

Bug #30965	Crystal Report 10 can't verify databases with underscore in MySQL-databasename
Submitted:	12 Sep 2007 6:36	Modified:	9 Sep 2008 16:56
Reporter:	Pat Müller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.23	OS:	Windows (XP Sp2)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	crystal, ODBC, tablename, underscore

Description:
If you have to refresh/verify a report definition in Crystal Reports 10 (Build 10.05.1506, SP6) you get "randomly" errors for databases with underscore in its name.

Before using newer versions then "ODBC 3.51.12" everything worked fine. Even in the newest 3.51.19 (and 3.51.20) this problem occurs.

We are not absolutely sure, if databasenames without underscore are never affected from this problem.

How to repeat:
1.) Open a existing (working) Crystal-report
2.) Database, Verify database
3.) Error "Could'n find table XY_Z in database. Remove table from report?"
    (We use the german version of crystal, I'm not sure, if this is the exact english translation)

If the databasename doesn't have a underscore all works fine. If the databasename has a underscore, the error occurs randomly on some of the used tables.

Suggested fix:
None.
Using of an older version like 3.51.12 isn't possible anymore, because of the error with the Long-Blobs who returns no values (which is fixed in newer version).

Thank you for the bug report. Verified on Access 2003 with 3.51.20.

This seems to be actually a bit worst. I have verified the same bug with Crystal Reports XI.

I tried renaming the database name (took out the underscore) and that helped, but then the bug also gets triggered by table names with more than one underscore, not just database names with underscore.

Also, this bug doesn't just affect the verify database functionality from the crystal reports authoring tool (Windows). It also means that the reports can't be run on Crystal Reports Server, which is quite annoying. I have verified this in both a Windows and a Linux installation of Crystal Reports Server.

I need this working soon and I'm trying to find a workaround, but I just have too many tables with many underscores and a too big of an application to be renaming all those tables (it is very common to have several underscore characters on database and table names when using Ruby on Rails framework!).

Hi Sebastian and Pat,

thanks for you reports.

You are both using German Crystal Reports? 
And besides underscores your names don't use äöüß (umlaute)?

You don't change Crystal Report version too?
You only change the MyODBC version?

Also, I need to know, which MySQL server version do you use?

Can somebody of you send us the trace, please?

Is it possible, that you try it with our new MyODBC version 5.1 too?

Sebastian: FYI: there is a native connector for Ruby.

Regards,

Susanne

@Susanne: 
What exactly do you mean with "the trace"? 

I found this in the changelog for ODBC version 3.51.19:
* Removed support for the TRACE and TRACEFILE DSN options. The standard 
ODBC logging should be used. 

Could you please help me with a link to a FAQ or HowTo for the needed "trace"?
I will add the other informations on monday, need to check it at work.

Hi Pat,

look at the DSN configuration. There should be something with trace. I suppose, it's translated with: "Verlaufsprotokoll".

The trace is just the "Verlaufsprotokoll". And we need this, to look, what happened, when you try to do something with databases or tables, where the name has an underscore.

Regards,

Susanne

- Yes, we are using German Crystal Reports (v10)
- We don't use äöü and other vowels (Umlaute) in our database-names, but underscore
- We don't change Crystal version, just re-loading an existing report to use it with the newest MySQL-ODBC-Driver
- Yes, we only changed the ODBC-Driver (and can reproduce that the report works with an older ODBC-Version <= 3.51.12)
- The error occurs with 5.0.18 AND 4.1.15
- I have added a trace and hope, this is what you asked for (let me know, if you need/other informations)

For me, the problem starts at least > 3.51.14 . Using Crystal Reports XI also, and English language.  Noticed that fields are cut off to exactly 21 characters also. Remapping fixes, but is extremely annoying and time consuming. Hope a fix is in the works.

Hi,

I am getting the exact same issue. I just upgraded my ODBC to latest 3.51.21, but problem persists. Any table names with an underscore are not recognised. I am running Crystal Reports XI. My work around for now is to tell Crystal NOT to update the table names / column names in existing reports, then they will still run. This does not help you when creating new reports or adding new tables to existing reports however.
How far away is a fix on this issue?

Regards

Craig Everard

On a whim, I decided to download the Alpha release ODBC connector v 5.1. I installed the connector and created a new ODBC DSN to my MySQL database and then tested this bug using Crystal Reports XI. It appears to be fixed as far as I can tell. I successfully loaded one of my tables that didn't work under 3.51.21 and ran a query against it.
Regards
Craig

I can confirm Craig's newest results!
In the alpha-version of the ODBC 5.1-driver the underscore problem with Crystal Report is fixed.

Is there any chance, that this fix will find the way back to the 3.51.2x driver-version? For us, it would be much less work to update this driver, then change all the existing ODBC-connectionstrings to the newest 5.1-version.

Our error is not solved in the newest available version 3.51.22 (13-Nov-2007) unlike the already reported working version 5.1alpha.

Unfortunately BOTH, the newest available 5.1.1-beta and the 3.51.23 doesn't solve the above problem with the underscores in MySQL-databasenames used by Crystal Report!!

@Jim:
Sorry for asking, but what exactly do you mean with your "Suggested fix: None"?

a) There will be never a solution for this on older version like 3.51.12
b) There will be never a solution for this on version like 3.x.xx
c) There will be never a solution for this neither on 3.x.xx nor on 5.x
Thanks for clarifying in advance!

Pat, you are the one who provided 'Suggested fix: None'. I simply updated the Version on this report to be in the format we expect, so it would show up in the correct reports. I don't have an answer as to when or where this will be fixed.

odbc trace

Attachment: SQL.LOG (application/octet-stream, text), 118.06 KiB.

report that generated the above odbc trace

Attachment: bug30965.rpt (application/x-rpt, text), 43.00 KiB.

mysql>create database foo_bar;
mysql> use foo_bar;
mysql> create table crystaltest (c1 int primary key, c2 char, c3 text);
mysql> create table crystal_test (c1 int primary key, c2 char, c3 text);
mysql> insert into crystaltest values (1, 'a', 'this is a test'));
mysql> insert into crystaltest values (1, 'a', 'this is a test');
mysql> insert into crystal_test values (1, 'a', 'this is a test');

Report bug30965.rpt was run against this data to create the attached odbc trace.  Report was opened many times, refreshed and verified many times.  Problem couldn't be reproduced against 3.51.23

Printscreen-Collection to reproduce the verify-error on databasenames with underscores

Attachment: mysql-odbc-3.51.23-crystal-errors.pdf (application/pdf, text), 65.57 KiB.

Eric
You are right with your new database and your new report. This works nice in Crystal with underscores in databasenames.

BUT if you have TWO (or more) databases with underscores in its name and then try to change one field from the database 'foo_bar_DEVEL'
with the same field from 'foo_bar_PROD' you get the above Error "Could'n find table XY_Z in database. Remove table from report?" (Point 3.)

The same error still occurs, if you try to change the entire database from foo_bar_DEVEL to foo_bar_PROD. We have attached a step-by-step prinscreen collection (from our german crystal reports). Please try to reproduce the error with the help of this PDF.

Hi Pat,
Thanks for the update.  Sorry you're still having difficulties with this.  Could you please take a look at the following scenario I've created and tell me how closely this resembles what you are doing?  Following these exact steps I had no problems using the English version of Crystal Reports XI Developer.

If these steps are correct we'll need to do some further troubleshooting to isolate the problem.  Have you tried cloning your problem table to a database that does NOT have underscores to verify that those characters are definately source of the problem?  

Could you also tell me whether the steps below stimulate the problem in your environment.  If they don't then please include your table create in your next update so we can see whether some individual field in your table may be root of your problem.  

mysql> create database foo_bar_bar;
mysql> create table foo_bar_bar.t (C1 INT, C2 CHAR, C3 Text);
mysql> insert into foo_bar_bar.t values (1, 'a','this is a test');
mysql> create database foo_bar_foo;
mysql> create table foo_bar_foo.t (C1 INT, C2 CHAR, C3 Text);
mysql> insert into foo_bar_foo.t values (1, 'a','this is a test');

1) Created two DSN's, using 3.51.23 driver.  One points to database foo_bar_bar, and the other to foo_bar_foo
2) Created a new blank report in Crystal. 
3) Selected Database/Database Expert
4) Created two new ODBC(RDO) connections, one for each DSN created in (1)
5) select table t from the first of the two current connections and move it to the selected pane
6) select View/Field Explorer if not already open
7) Drag the three fields from the table to the report.
8) select Database/Verify Database -> OK
9) press F5 or the Refresh button to get the data from the server -> OK
10) mysql> update foo_bar_bar.t set c3='this is not a test';
11) select Database/Verify Database -> OK
12) press F5 or the Refresh button to get the data from the server -> OK the change is visible
13) select Database/Set Datasource Location
14) highlight table 't' in the top pane
15) expand the second connection listed (the one that is not currently being used)
16) highlight table 't' in this datasource, and click 'update' -> OK the field has already been changed back to the initial value
17) for completeness, select Database/Verify Database -> OK
18) press F5 or the Refresh button to get the data from the server -> OK
mysql> update foo_bar_foo.t set c3='this is not a test';
19) refresh again -> OK

Hi Eric

Thank you for your help and the detailed step-by-step instructions!
On step 8.) we get the error
"Die Datenbanktabelle "t" wurde nicht gefunden. Möchten Sie diese Tabelle aus dem Bericht entfernen"
--> buest guess-translation: "The databasetable "t" could not be found. Do you want to remove this table from the report"

Important: We use Crystal Report 10, not XI! (And we are sure that this version 10 was working in earlier driver-versions. Unfortunately, we can't update to XI at the moment).

Please let me know step-by-step, how we can generate the needed informations for you to solve this bug (Tracelog? Anything else?)

Just a little addition: 
With the "old" driver 3.51.12 your example works perfect and without any error!

Just a few comments while Eric is sleeping:
 1) It's impossible for us to support every 3rd party SW there is on the market.
 2) License keys we had for CR X are long expired. As a matter of fact so will ones for CR XI in very near future. CR2008 is current version.
 3) Fact that old driver works can mean anything but probable cause is that we worked *around* a bug in CR X and now that it's fixed in proper place we reverted the change. Same happens all the time.

Now let's see what Eric and Jess will come up with.

Thanks for your interest in MySQL.

After many hours of analyzing the trace-logs, we found the following two facts:
a) Crystal reports 10 without SP6 (10.0.0.533) works with MySQL-Driver 3.51.12 and 3.51.23
b) Crystal reports 10 with SP6 (10.0.5.1506) works with 3.51.12 BUT NOT with 3.51.23!

We assume, the reason is the syntax of the database-name.
a) Crystal without SP6 sends the database-name like 
crw32           96c-48c	ENTER SQLTablesW 
		HSTMT               05ED1F30
		WCHAR *             0x02B86E1C [      -3] "foo_bar_bar\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x02B84774 [      -3] "t\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 

a) Crystal with SP6 sends the database-name with escaped underscore, like
crw32           974-c70	EXIT  SQLTablesW  with return code -1 (SQL_ERROR)
		HSTMT               05F12698
		WCHAR *             0x02BC533C [      -3] "foo\_bar\_bar\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 
		WCHAR *             0x02BDC62C [      -3] "t\ 0"
		SWORD                       -3 
		WCHAR *             0x00000000 [      -3] <empty string>
		SWORD                       -3 

		DIAG [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.18-max]Incorrect database name 'foo\_bar\_bar' (1102) 

Remember: 3.51.12 was able to interpret the escaped special char 'underscore' correctly! Since 3.51.13 this doesn't work anymore.

We hope, this can help to solve the problem with database-names including escaped special chars like underscore.

Thank you very much for your effort, we really appreciate it!!

Try re-generating report from scratch on fully patched CR10 and clean WinXP registry with no escape overrides.

The report to create the above traces was both created by scratch with the table from Eric, once with CR10 "naked" and then fully patched (SP6). I already wrote the difference in "foo\_bar\_bar\ 0" above.

What do you mean with "clean WinXP registry with no escape overrides"? What exactly do I have to check? Which key, which values, which files?

Clear..

[HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports\Database]

If the problem persists after you regenerate the report, then I would be interested to see a resolution to this problem.

As an immeadiate work around you could generate the report with 30-day publically available version of CR11.

I have DELETED my entire registry-entry 
[HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports]
installed 3.51.12 and made a test-report with CR10(SP6, 10.0.5.1506) from scratch with Eric's foo_bar_bar database: -> works fine, no errors.

I have DELETED again my entire registry-entry
[HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports]
installed 3.51.23 and made a test-report with CR10(SP6, 10.0.5.1506) from scratch with Eric's foo_bar_bar database: -> doesn't work with the error above:
"Die Datenbanktabelle "t" wurde nicht gefunden. Möchten Sie diese Tabelle aus dem Bericht entfernen"
--> buest guess-translation: 
"The databasetable "t" could not be found. Do you want to remove this table from the report"

I have added the two logfiles for 3.51.12 and 3.51.23 from this test.

(BTW: As an immediate work around we still use the old driver 3.51.12 for all our existing reports, because we have many hundreds of them...)

fix + test

Attachment: bug30965.diff (application/octet-stream, text), 3.80 KiB.

Thank you Jess, fantastic news and this on a saturday!!
Could you please help me to apply your *.diff?
(I don't have a compiler to make my "own" windows-binaries)
What is the usual way to integrate such a *.diff and how to test it in a windows-environment? I found this article http://dev.mysql.com/tech-resources/articles/howto_successful_patch.html , but couldn't find the necessary hint for me as end user.
Thanks again for your support!

What a wonderful start in this new week!
The test-myodbc3.dll has passed all the 19 steps from Eric's test-setup without any error. :))
Now we have to make some further tests with our own reports and our own runtime-DSN-changing-method. This needs one or two more days to complete.

I will tell you the results as soon as possible.

@Jess: Thank you for help!

We have finished our tests and have two results:
1.) Our main problem with the underscore in the databasenames is solved!
2.) But with this newest test-version we have a side-effect with german vowels (and maybe other characters) in SELECT-Statements looking for a text.

This problem doesn't occure with 3.51.23 - there must be a relation to the latest fix.

This is the snippet from a trace-log:
1404-1788	ENTER SQLExecDirectW 
		HSTMT               061F7318
		WCHAR *             0x04A62600 [      83] "SELECT * FROM device_hw_ids WHERE dhs_deviceid=776 AND dhs_Beschreibung='Ger\ffte ID'"
		SDWORD                    83

The SQL-Statement should be:
"SELECT * FROM device_hw_ids WHERE dhs_deviceid=776 AND dhs_Beschreibung='Geräte ID'"

Our german vowel "ä" (in the word "Geräte ID") will be changed to "\ff" in the newest test-driver 3.51.14

Please let me know, if we can do some other tests if needed.

Typo:
....to "\ff" in the newest test-driver 3.51.24(!)

Additional informations for side-effect with german vowels:

Using the MySQL-Query-Browser, the command
SELECT * FROM device_hw_ids WHERE dhs_deviceid=776 AND dhs_Beschreibung='Geräte ID'
will return the matching 1 record.
(We assume, that MySQL-Querybrowser doesn't use ODBC).

In "VB.NET and ADO" or "Crystal Report" you will get the error:
File: isctype.c
Line: 68
Expression: (unsigned)(c+1) <= 256

This patch is incorrect. The catalog and table name arguments to SQLTables() are to be treated as wildcards. The catalog and table name arguments to SQLSpecialColumns should be treated as literal identifiers.

In mysql_table_status(), we need to use the correct function for escaping identifiers in the FROM clause, not mysql_real_escape_string. We also need to fix myodbc_escape_wildcard() to escape correctly (it should only escape _, %, and ').

We also need to fix the escaping of identifiers in mysql_list_dbcolumns().

The way that mysql_table_status() is used has a broader problem in that it does not handle wildcards in the catalog name. Functions that call it will need to be fixed to retrieve and iterate over the list of catalogs to get the list of tables.

The escape_input_parameter() function in catalog.c is almost always the indication of a bug. It doesn't actually do what you might think from the function name.

Is there any chance that I can watch the (internal) progress on this issue? Since the last comment from Jim Winstead I'm not sure, if anyone is working on this case.

Any news on this case? 
Could someone please give me some progress information?

Pat, it's triaged and prioritized very high but I can't give you any definite answer.

We have repeated all the tests with your newest version 3.51.25 (11-Apr-2008).
Actually, all our problems seems to be solved:
- Underscores in tablenames: works
- German vowels in tablenames: works
- German vowels in fieldnames: works
- Verify databases in Crystal Report: works
- LONGBLOB: works
- Eric MaLossi's FOO_BAR-tests: works

I'm not sure now, because I can't find any information about this error-fix in the official changeLog.txt

Has someone fixed this and just not added a line in the ChangeLog.txt?

Pat, The only relevant thing I see is bug#32989, but that was fixed in 3.51.24. I'm going to close this bug now.

What about this comment in the description of bug#32989
"[14 Mar 19:40] MC Brown

A note has been added to the 3.51.24 changelog: 

Using tables with a single quote or other non-standard characters in the table or column names through ODBC would fail."

How can we test this and be sure that this is (still) true or not?

Pat,

That's the entry in the release notes that describes the bug that was fixed. As with almost all bugs that are fixed, a regression test has been added to our codebase.

Jess