By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,115 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,115 IT Pros & Developers. It's quick & easy.

Select/Update/Delete by Primary key failing with InnoDB

P: n/a
Hello:

I have just recently converted to using the InnoDB table type so that
I can enforce FOREIGN key constraints. I have been using MyISAM
tables (accessed via JDBC) successfully for some time. However, I
have just come across a problem with the new configuration that
boggles my mind....

First some configuration data:

Server version: 4.1.1-alpha-standard
Protocol version: 10
mysql-connector-java-3.1.1-alpha

I have created the following table:CREATE TABLE PlatformType
(
platformType VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
PRIMARY KEY( platformType ),
INDEX platformType_index ( platformType )
)
TYPE=InnoDB;

mysql> desc PlatformType;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| platformType | varchar(255) | | PRI | | |
| manufacturer | varchar(255) | | | | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I then proceed to build a simple test driver that (via JDBC) inserts a
test record, reads the test record to verify insertion, updates it,
and finally deletes it. The insert seems to work fine. However, when
I attempt to select the record with the following PreparedStatement:

select platformType F1, manufacturer F2 from PlatformType where
platformType = ?
(the '?' has been appropriately populated with the exact column value
used at insertion time - verified multiple times).

The query returns no rows! Also no errors, but definately no rows.
Yet, when I perform the exact same query in the interactive mysql
interpreter, I do see the expected row.

Additonally, if I modify the query to look like this:

select platformType F1, manufacturer F2 from PlatformType where
platformType LIKE ? (with the '?' populated with the exact column
insertion value plus the '%' wildcard)

the select operation then does return the expected row as well.

Please note that there are no other rows in the table.

Finally, please note that the exact same test driver works
successfully (WITHOUT the need for the LIKE clause) on the following
very similar table:

CREATE TABLE OSECRegistration
(
id VARCHAR(255) NOT NULL,
hostName VARCHAR(255) NOT NULL,
platformType VARCHAR(255) NOT NULL,
registrationTime BIGINT NOT NULL,
createdBy VARCHAR(255) NOT NULL,
dateCreated BIGINT NOT NULL,
modifiedBy VARCHAR(255) NOT NULL,
dateModified BIGINT NOT NULL,
PRIMARY KEY( id ),
INDEX id_index ( id )
)
TYPE=InnoDB;

Again, the test for this table is the exact same code base (they are
both generated in exactly the same fashion) and works fine without the
need to change '= ?' to 'LIKE %' in order to get the results. Also, I
have triple verified that the values being sent into the ps.set calls
for the '?' arguments are correct and have no leading and/or trailing
garbage, etc. This has also been verified withing the mysql
interpreter - I use the concat operation to place a '[' char around
the beginning and end of the column in question (platformType) to
ensure that no leading/trailing garbage made it in - and it did not.

I'd appreciate any an all assistance that you can provide of
decipering this mystery as it really has me perplexed (and stressed as
I need to resolve this fairly quickly). I cannot see MATERIAL
difference in the table construction that could even remotely explain
this behavior. Additionally, since the test code works perfectly on
the other table, I cannot explain why it does not for the PlatformType
table (which is really quite similar - just fewer columns).

Thanks again for any and all thoughts, ideas, suggestions!
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Problem solved. It turns out (not suprisingly I guess) to have
nothing to do with InnoDB or the FOREIGN key constratints. After some
checking I realized that the PreparedStatement.setObject() method will
NOT do an implicit conversion to String and hence the comparision to
the VARCHAR column was never satisifed - however, whenver I would
print out the value being set, the System.out.println() was doing the
implicit conversion to String and hence yielding the necessary and
aparently correct value.

So, the fix was to make sure I add the .toString() call inside of the
PreparedStatement.setObject() call and all works as desired.

sk****@abilsoft.com (skidvd) wrote in message news:<fd**************************@posting.google. com>...
Hello:

I have just recently converted to using the InnoDB table type so that
I can enforce FOREIGN key constraints. I have been using MyISAM
tables (accessed via JDBC) successfully for some time. However, I
have just come across a problem with the new configuration that
boggles my mind....

First some configuration data:

Server version: 4.1.1-alpha-standard
Protocol version: 10
mysql-connector-java-3.1.1-alpha

I have created the following table:CREATE TABLE PlatformType
(
platformType VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
PRIMARY KEY( platformType ),
INDEX platformType_index ( platformType )
)
TYPE=InnoDB;

mysql> desc PlatformType;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| platformType | varchar(255) | | PRI | | |
| manufacturer | varchar(255) | | | | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I then proceed to build a simple test driver that (via JDBC) inserts a
test record, reads the test record to verify insertion, updates it,
and finally deletes it. The insert seems to work fine. However, when
I attempt to select the record with the following PreparedStatement:

select platformType F1, manufacturer F2 from PlatformType where
platformType = ?
(the '?' has been appropriately populated with the exact column value
used at insertion time - verified multiple times).

The query returns no rows! Also no errors, but definately no rows.
Yet, when I perform the exact same query in the interactive mysql
interpreter, I do see the expected row.

Additonally, if I modify the query to look like this:

select platformType F1, manufacturer F2 from PlatformType where
platformType LIKE ? (with the '?' populated with the exact column
insertion value plus the '%' wildcard)

the select operation then does return the expected row as well.

Please note that there are no other rows in the table.

Finally, please note that the exact same test driver works
successfully (WITHOUT the need for the LIKE clause) on the following
very similar table:

CREATE TABLE OSECRegistration
(
id VARCHAR(255) NOT NULL,
hostName VARCHAR(255) NOT NULL,
platformType VARCHAR(255) NOT NULL,
registrationTime BIGINT NOT NULL,
createdBy VARCHAR(255) NOT NULL,
dateCreated BIGINT NOT NULL,
modifiedBy VARCHAR(255) NOT NULL,
dateModified BIGINT NOT NULL,
PRIMARY KEY( id ),
INDEX id_index ( id )
)
TYPE=InnoDB;

Again, the test for this table is the exact same code base (they are
both generated in exactly the same fashion) and works fine without the
need to change '= ?' to 'LIKE %' in order to get the results. Also, I
have triple verified that the values being sent into the ps.set calls
for the '?' arguments are correct and have no leading and/or trailing
garbage, etc. This has also been verified withing the mysql
interpreter - I use the concat operation to place a '[' char around
the beginning and end of the column in question (platformType) to
ensure that no leading/trailing garbage made it in - and it did not.

I'd appreciate any an all assistance that you can provide of
decipering this mystery as it really has me perplexed (and stressed as
I need to resolve this fairly quickly). I cannot see MATERIAL
difference in the table construction that could even remotely explain
this behavior. Additionally, since the test code works perfectly on
the other table, I cannot explain why it does not for the PlatformType
table (which is really quite similar - just fewer columns).

Thanks again for any and all thoughts, ideas, suggestions!

Jul 20 '05 #2

P: n/a
Problem solved. It turns out (not suprisingly I guess) to have
nothing to do with InnoDB or the FOREIGN key constratints. After some
checking I realized that the PreparedStatement.setObject() method will
NOT do an implicit conversion to String and hence the comparision to
the VARCHAR column was never satisifed - however, whenver I would
print out the value being set, the System.out.println() was doing the
implicit conversion to String and hence yielding the necessary and
aparently correct value.

So, the fix was to make sure I add the .toString() call inside of the
PreparedStatement.setObject() call and all works as desired.

sk****@abilsoft.com (skidvd) wrote in message news:<fd**************************@posting.google. com>...
Hello:

I have just recently converted to using the InnoDB table type so that
I can enforce FOREIGN key constraints. I have been using MyISAM
tables (accessed via JDBC) successfully for some time. However, I
have just come across a problem with the new configuration that
boggles my mind....

First some configuration data:

Server version: 4.1.1-alpha-standard
Protocol version: 10
mysql-connector-java-3.1.1-alpha

I have created the following table:CREATE TABLE PlatformType
(
platformType VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
PRIMARY KEY( platformType ),
INDEX platformType_index ( platformType )
)
TYPE=InnoDB;

mysql> desc PlatformType;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| platformType | varchar(255) | | PRI | | |
| manufacturer | varchar(255) | | | | |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I then proceed to build a simple test driver that (via JDBC) inserts a
test record, reads the test record to verify insertion, updates it,
and finally deletes it. The insert seems to work fine. However, when
I attempt to select the record with the following PreparedStatement:

select platformType F1, manufacturer F2 from PlatformType where
platformType = ?
(the '?' has been appropriately populated with the exact column value
used at insertion time - verified multiple times).

The query returns no rows! Also no errors, but definately no rows.
Yet, when I perform the exact same query in the interactive mysql
interpreter, I do see the expected row.

Additonally, if I modify the query to look like this:

select platformType F1, manufacturer F2 from PlatformType where
platformType LIKE ? (with the '?' populated with the exact column
insertion value plus the '%' wildcard)

the select operation then does return the expected row as well.

Please note that there are no other rows in the table.

Finally, please note that the exact same test driver works
successfully (WITHOUT the need for the LIKE clause) on the following
very similar table:

CREATE TABLE OSECRegistration
(
id VARCHAR(255) NOT NULL,
hostName VARCHAR(255) NOT NULL,
platformType VARCHAR(255) NOT NULL,
registrationTime BIGINT NOT NULL,
createdBy VARCHAR(255) NOT NULL,
dateCreated BIGINT NOT NULL,
modifiedBy VARCHAR(255) NOT NULL,
dateModified BIGINT NOT NULL,
PRIMARY KEY( id ),
INDEX id_index ( id )
)
TYPE=InnoDB;

Again, the test for this table is the exact same code base (they are
both generated in exactly the same fashion) and works fine without the
need to change '= ?' to 'LIKE %' in order to get the results. Also, I
have triple verified that the values being sent into the ps.set calls
for the '?' arguments are correct and have no leading and/or trailing
garbage, etc. This has also been verified withing the mysql
interpreter - I use the concat operation to place a '[' char around
the beginning and end of the column in question (platformType) to
ensure that no leading/trailing garbage made it in - and it did not.

I'd appreciate any an all assistance that you can provide of
decipering this mystery as it really has me perplexed (and stressed as
I need to resolve this fairly quickly). I cannot see MATERIAL
difference in the table construction that could even remotely explain
this behavior. Additionally, since the test code works perfectly on
the other table, I cannot explain why it does not for the PlatformType
table (which is really quite similar - just fewer columns).

Thanks again for any and all thoughts, ideas, suggestions!

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.