Skip to main content

Entity reference to another entity creates BLOB column in mysql table..why?

12 replies [Last post]
andjarnic
Offline
Joined: 2007-01-08

Hi all,

I am trying to understand why when I have an entity bean reference in another entity bean, it shows up as a BLOB column and not a simple ID pointer (long?)? It makes it impossible to simply insert rows with SQL, and when I do have it running (GlassfishV3, Java6), it appears each row eats up a ton of space... it's as if it is serializing the entire referenced object into the column as a blob.

@Entity
public class User {
...
}

@Entity
public class Group {
@OneToOne
private User user;

...

}

Love to understand how to make it not work like this.

Thank you for any help.

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
andjarnic
Offline
Joined: 2007-01-08

Figured out the problem. Actually two issues.

First thing is that I didn't add the right annotation to one of the entities. I had the @OneToMany, but the @ManyToOne I didn't do right.. or vice versa.

The main issue tho, which on other entities I have that also had the blob column issue, was that I had @Basic(optional=false) above several properties. I don't know why/how this converts a bigint(20) into a blob column, but it does. At least with GlassFish v3 and MySql 5. Soon as I removed that annotation from a few different entities that were all exhibiting this issue, they all now show bigint(20) and all looks normal now.

Thank you for the replies. Wish my book and/or other searches would have revealed this.

mvatkina
Offline
Joined: 2005-04-04

I'm glad you found the cause of the problem. Did you have @Basic on the same fields as @OneToMany or @ManyToOne? If yes, only one annotation is processed on each field, and if @Basic comes first, it denotes a BLOB for anything that is not a primitive, wrapper, or a String.

mvatkina
Offline
Joined: 2005-04-04

Close to impossible... How does the Group class look like? Are there any mapping files that override the annotations?

andjarnic
Offline
Joined: 2007-01-08

Hi,

I am not sure what you mean by override any annotations? The Group entity is very simple.. just an entity with a User entity reference in it. If I remove that User reference... the table is created just fine.. with no BLOB column

mvatkina
Offline
Joined: 2005-04-04

Can you post your complete Group class? Does it have an ID field?

You can use orm.xml (or a similar file) to ovveride mapping annotations.

Regards,
-marina

andjarnic
Offline
Joined: 2007-01-08

Basically it's like this:

@Entity
@Table(name="groups")
public class Groups implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Basic(optional = false)
@Column(name = "id")
private Long id;

private String name;

@JoinColumn(name = "userId", referencedColumnName = "id", updatable = false, insertable = false)
@OneToMany(cascade = CascadeType.PERSIST)
private User user;
}

I left out the getter/setter methods for brevity, but that is the gist of it. The User entity is the same sort of deal.. @Id, String name, that is it. Very simple entities.

The annotations I use, I assumed were ok. I have a book, EJB 3 in Action, and from the examples and the sql output that they show, it seems to me that my groups table should basically be an ID column, a VARCHAR for the name, and a long/integer column that contains the ID from the users table... and the join between the groups.userId column tot he users.id column should be fine.

So it does strike me odd that complete bytes of data for each User object are stored in the groups table for each row, in a BLOB column. I can't fathom why that would be done that way short of some form of serialization for speed... but then if you change the actual user in the users table that any number of rows in the groups table references, does it go back and update all those values in the groups table where the user data is? That seems it would crush performance the more rows you have with the same user data in it.

mvatkina
Offline
Joined: 2005-04-04

@OneToMany (as in your last version) is not the same as @OneToOne (as in the first one). @OneToMany should be placed on a "many" side of the relationship.

andjarnic
Offline
Joined: 2007-01-08

Hi,

I believe I have it set up correctly. My example may have been wrong, but checking my actual code, I have the annotations on the correct objects. For the heck of it I reversed it, the desc in mysql still shows BLOB columns, and I have DDL set to drop-and-create on deploy, so it recreates all the tables each time.

This just seems very strange to me that referenced/foreign keys show up as BLOB columns and store full object data in them instead of simple 64-bit long pointers to an index in another table. I mean, I could do this the old JDBC way.. that is, instead of a reference to User in the Group entity, I simply use a long userId and in the query just use that instead of the object name... although not sure if the named queries on entities will work with that or not.

Is there any reason you can think of that the object ref would be stored in a Blob column type instead of long/integer values that refer to the ID value of the other table it refers to?

I haven't been able to find anything in the properties list for EclipseLink that describes this issue, nor after several google searched I can't seem to find anyone else with this issue.

mvatkina
Offline
Joined: 2005-04-04

Does it work correctly if you use default database (derby)? If yes, it might be a MySQL specific bug somewhere.

Obviously there are plenty of tests that test relationships mapping which expect PK/FK combinations, not BLOBs. That's why I was asking about possible overrides or any mis-mapping. If you have a reproducible test case, please file a bug.

andjarnic
Offline
Joined: 2007-01-08

Interesting.. I've never used Derby, not sure how to replace mysql with it at this point, but I'll look into it.

mvatkina
Offline
Joined: 2005-04-04

comment out/remove element or use jdbc/__default as the resource.

andjarnic
Offline
Joined: 2007-01-08

Bumping.. hoping someone might read this and have some clue as to how to avoid having BLOB columns for entity object references.. so that I can use the mysql command line to insert new rows, as well as reduce how much space is being consumed.