[#CMP-335] JDBC index store does not work with MySQL JDBC driver 3.0.14

Compass

JDBC index store does not work with MySQL JDBC driver 3.0.14

Created: 16/Dec/06 06:14 PM Updated: 17/Mar/08 03:25 AM

Component/s:

Compass::Core

Affects Version/s:

1.1 M3

Fix Version/s:

1.1 RC1

File Attachments:	1. patch.txt (3 kB)
Environment:	JDK 5.0, MySQL 5.0, MySQL Connector Java 3.0.14

Description

« Hide

There are several problems with using JDBC index store with the software listed above.
1. setBlob(position, null) throws NPE. setNull(position, Types.BLOB) should be used instead
2. openBlobSelectQuote() and closeBlobSelectQuote() in MySQLDialect should return ` (0x60) instead of ' (0x27)
3. TINYINT(1) should be used instead of BIT for boolean fields

All

Comments

Change History

Sort Order:

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 16/Dec/06 06:17 PM

A patch that fixes the problems listed in the bug report.

[ Permlink | « Hide ]

Shay Banon added a comment - 20/Dec/06 07:33 AM

1. I just ran my tests again, and I don't get the NPE using setBlob with null value (using teh same versions you mention).
2. The select quote mentioned are used due to a bug in MySQL driver when using emulateLocators flag, which you should use when using MySQL.
3. What is wrong with BIT? It works for me.

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 22/Dec/06 08:49 AM

What are the exact versions of software you are using? I have MySQL 5.0.27 and MySQL Connector/J 3.0.14 (not 3.1!).

The problem with NPE may be fixed in 3.1.x, but it's quite common with older JDBC drivers. Using setNull() is generally safer than using setBlob(position, null), setString(position, null), etc.
The problem with BIT is that getBoolean() fails for this type, probably due to a bad combination of the server version and the driver version. The meaning of BIT data type was changed in MySQL 5.0.3 and the old driver probably fails because of this.

Looks like I will have to upgrade the JDBC driver anyway, because it turned out that versions 3.0.x don't support emulateLocators. I will post another comment to let you know if that resolves the issues.

[ Permlink | « Hide ]

Shay Banon added a comment - 22/Dec/06 08:57 AM

Sorry mate, I saw the 3 and 14 and did not notice the 0/1. I tested the system with 3.1.14 and as you said, not 3.0.14 . Things seem to work on 3.1.14, but I agree with you that setting the null value using the proper API should be the case.

Regarding the BIT type, it seems to work on the latest driver versions (both 5 and 3.1.14), and it is more appropriate for boolean values, so I think that we can keep it.

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 23/Dec/06 03:08 AM

OK, I upgraded the JDBC driver to version 5.0 and it works fine with unmodified version of Compass. The upgrade was painful, however, because the new driver started throwing data truncation exceptions. It took me a while to figure out that I had to add characterEncoding=UTF-8 to connection options to get rid of them.

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 23/Dec/06 08:59 AM

Just to be clear: the problem with data truncation exceptions is not related to compass. But the fact that compass requires connector/j 3.1 or later with emulateLocators=true is worth mentioning in the documentation.

[ Permlink | « Hide ]

Shay Banon added a comment - 03/Jan/07 01:47 PM

Partly fixed. Changes the setBlob with null value to use setNull.

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 03/Jan/07 02:16 PM

This fix is ok with me, but I still think the main part of the fix should be an update to documentation. Sorry for being a pest

[ Permlink | « Hide ]

Shay Banon added a comment - 03/Jan/07 02:31 PM

No problemo mate, what do you think need to be amended in the documentation? Have a look at the latest one: http://www.opensymphony.com/compass/versions/1.1RC1-SNAPSHOT/html/jdbcdirectory.html .

[ Permlink | « Hide ]

Constantine Bozhkov added a comment - 03/Jan/07 02:56 PM

Right now the table describing available dialects specifies that for MySQL dialect BLOB locators are supported with Connector/J 3.1 or later and emulateLocators=true in connection string. It should say that in order to work with Compass, Connector/J version must be 3.1 or later and that emulateLocators=true must be specified in the connection string. It would be great if there was a section called "MySQL Note" below the table describing this requirement and the table referred to this section.

[ Permlink | « Hide ]

John Latham added a comment - 01/Mar/07 07:42 PM

Shay,

FYI, I got the error "Data truncation: Out of range value adjusted for column 'deleted_' at row 1" during JDBC index rebuild running Compass 1.1 with MySQL 5.0.27 and mysql-connector-java-3.1.11, characterEncoding=utf-8 and emulateLocators=true. Interestingly, it's only a problem on my production Linux servers - using the same drivers on my Windows server (MySQL 5.0.22) is fine.

In any case, the problem went away when I upgraded to mysql-connector-java-5.0.4.

Cheers,
John