MySQL Bugs: #10549: Unable to update data using JDBC, getting Can't open file: '<tblname>.ibd' (=1)

Bug #10549	Unable to update data using JDBC, getting Can't open file: '<tblname>.ibd' (=1)
Submitted:	11 May 2005 12:13	Modified:	13 May 2005 16:32
Reporter:	Mohideen Noordeen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.1	OS:	10.3.9
Assigned to:		CPU Architecture:	Any

Description:
When I try to update data in an INNODB table using JDBC I get the following error:

"Can't open file: 'ProjectType.ibd' (errno: 1)" I am using MySQL/J version 3.1.8

my.cnf has the only foll. entry
lower_case_table_names=0

I had to set this up to preserve mixed case in my table names.

my.cnf was changed and dataserver was restarted BEFORE the database was created.

What is the workaround to preserve table name case? I tried to add the foll., still the same issue:

innodb_file_per_table in my.cnf and restarted mysql, dropped and recreated database

The file <tblname>.ibd does exist with the right case under .../mysql/data/<dbname>

I can do all the reads and updates from mysql command line though.
Thanks
Mohideen

How to repeat:
- setup config as in my.cnf
- create database from scratch using innodb tables
- try and insert data using JDBC

Config:
MySQL 4.1.11
OS-X 10.3.9
MySQL/J 3.1.8

My.cnf has the foll:
lower_case_table_names=0
innodb_file_per_table

i've decided to return to lowercase table and column names and thus avoided the problem altogether. This way my.cnf is no longer needed as I am running with default values. The only difficulty was that I had to edit all my ddl to lower case as I like to separate words with underscore or with uppercase which was not possible. e.g. ProjectType => project_type

Thanks
Mohideen

Hi!

If you recreate the database, any of

lower_case_table_names=0 or 1 or 2 (the default)

should work, and you should be able to access your InnoDB tables.

Can you create a repeatable tests case with 4.1.11 where some of those values does NOT work?

Note that once you have chosen a value, you must not change it afterwards. Could this explain why you had the problem originally?

I recommend using value 1 on case-insensitive file systems. Note that you do not need to edit your application, since value 1 puts names in lower case on INTERNALLY.

Regards,

Heikki

When I look at the table list using mysql, some of the table names show up as in the case as I entered and some of them show up in lower case. I also use a mapping generator which picks up these names as returned by mysql which completely messes up the adopted naming convention. I was able to preserve the case if I use the option innodb_file_per_table but then my program crashes saying that it cannot find a .ibd file. As I mentioned, I created a new database after config changes and server restart. Now I have returned to lower case naming convention in my application and everything works.
Mohideen