MySQL Catalog (or Schema) Name not being prepended in the EJ

[andrigtmiller]

I have been writting an EJB3 prototype using MySQL 4.1.xx, and I was getting a SQL parse error on the generated insert statement, causing my Order object's persist to fail. After experimenting using the MySQL query browser, I discovered that MySQL requires the catalog or schema name to be prepended to the table name even though you have connected specifically to that catalog in the connection URL. Therefore the following Hibernate (EJB3) generated SQL statement of:

insert into Order (state, customerid, addressline1, addressline2, city, zipcode, zipplusfour) values (?, ?, ?, ?, ?, ?, ?)

fails, and it needs to look like this:

insert into prototype.Order (state, customerid, addressline1, addressline2, city, zipcode, zipplusfour) values (?, ?, ?, ?, ?, ?, ?)

Now, the only way I could get the statement to look like this was to change the entities @Table tag from:

@Table(name="Order)

to:

@Table(name="prototype.Order")

Obviously, this ties the entity directly to the specific schema, making it un-portable to multiple database schema's or catalogs. This cannot be the proper way to fix this issue, even though it did work.

Can anyone shed some light on what I am missing? There must be a property somewhere that I am not setting or not setting correctly.

[martinganserer]

Hi,

I think it depends on the connection settings of your Data Source.
If the owner of a table is different to the user/schema account defined in the Data Source you always need the schema name!

To solve your problem you have to set a different user/schema name in your Data Source config file!
In your case it must be "prototype".

Does it work?

[andrigtmiller]

That is the problem.

Thanks.

[andrigtmiller]

I was pretty sure that you were correct, and I dropped the database, setup a new user, and logging as that user, and created the database again from scratch. I tested using that user to insert to the database through the MySQL Query Browser, and all worked as expected without having to specify the schema name, but it still failed to insert from the actual entity without the schema name. I also double check my data source configuration to make sure I was logging in as the owner of the schema, and I was. From the MySQL Administration tool, I could see all of the application servers connections, and they were all connected with the proper user!

This should have cleared this up, but it didn't. I'm not sure now, what else I might have wrong. Anyone have another idea?

[epbernard]

hibernate.default_schema

[andrigtmiller]

I set the hibernate.default_schema property in the persistence.xml, and now it does prepend the EJB3. in front of the table name, but it also changes the table name to EJB3_Order, when the table name is just Order. So this sort of worked, but it broke the table name mapping, so instead of getting EJB3.Order I am getting EJB3.EJB3_Order! This is still not right. What else am I missing?

[andrigtmiller]

I figured it out! I needed to set the hibernate.default_catalog property instead of the hibernate.default_schema property.

Thanks for all the help on this one!