Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Primary keys must not contain null
Primary keys must not contain null [message #815419] Wed, 07 March 2012 16:29 Go to next message
Kresimir Kovac is currently offline Kresimir Kovac
Messages: 2
Registered: March 2012
Junior Member
We have upgraded Glassfish from 3.1.1 to 3.1.2, so also to eclipseLink 3.1.2.

After upgrade, every nativeQuery stopped working with following exception:

Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [ArrayRecord(
 => 1129
 => 2011-09-05 12:50:28.735459)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Wowzalog sql="select logid, created from wowzalog l where xtime >= '2011-05-01'::date and xtime < '2012-03-07'::date + 1 and username = 'bulbTest5' order by xtime, logid ASC offset 0 limit 20")


Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [ArrayRecord(
=> 61)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Broj sql="select count(1) as broj from wowzalog where xtime >= '2011-05-01'::date and xtime < '2012-03-07'::date + 1 and username = 'bulbTest5'")


Is there any reason why this native queries stopped working?
Is there any workaround?
Is this a bug in EclipseLink?
Re: Primary keys must not contain null [message #815450 is a reply to message #815419] Wed, 07 March 2012 17:24 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Can you show how you are executing the queries and what they should return? Can you also verify the provider you were using in Glassfish 3.1.1 and its version - I believe it should have been EclipseLink 2.3.0, but I'm not positive.

I find it strange that your nativeQuery selecting count(1) is being executed as a ReadAllQuery which is expected to return entities, and I cannot think of any changes between the versions that would account for this without additional information.

Best Regards,
Chris
Re: Primary keys must not contain null [message #815586 is a reply to message #815450] Wed, 07 March 2012 20:55 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 5
Registered: March 2012
Junior Member
I have the exact same error with eclipse link since upgrading from GF 3.1.1 to GF 3.1.2

Here is the line of my code it fails on:

Query q = em.createNativeQuery("select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.identity=?", Imsu.class);

And here is a snippet of Imsu.java:

@Entity
@Table(name = "imsu", catalog = "hss_db", schema = "")
@NamedQueries({
@NamedQuery(name = "Imsu.findAll", query = "SELECT i FROM Imsu i")})
public class Imsu implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Column(name = "version")
@Version
private int version;
@Basic(optional = false)
@NotNull
@Column(name = "name")


I have other native queries that work fine in other projects. I think the issue could be related to the fact that this project uses entity classes that specifically reference the catalog as I have some entities in a catalog called hss_db and others in another with a different name.... not sure...
Re: Primary keys must not contain null [message #815997 is a reply to message #815586] Thu, 08 March 2012 09:46 Go to previous messageGo to next message
Kresimir Kovac is currently offline Kresimir Kovac
Messages: 2
Registered: March 2012
Junior Member
Glassfish 3.1.1 is using EclipseLink v2.3.0

Here is the sniplet of the code for 'count(1)' query:
String query = "select count(1) as broj from wowzalog where xtime >= '2011-05-01'::date and xtime < '2012-03-07'::date + 1 and username = 'bulbTest5'"
Broj count = (Broj) em.createNativeQuery(query, Broj.class).getSingleResult();


Broj entity code:
@Entity
public class Broj implements Serializable {
    private static final long serialVersionUID = 786457894567954L;
    
    @Id
    private int broj;

    public int getBroj() {
        return broj;
    }

    public void setBroj(int broj) {
        this.broj = broj;
    }    
}



And the database is PostgreSQL 8.4

[Updated on: Thu, 08 March 2012 09:49]

Report message to a moderator

Re: Primary keys must not contain null [message #815998 is a reply to message #815586] Thu, 08 March 2012 09:49 Go to previous messageGo to next message
Guido Vrankar is currently offline Guido Vrankar
Messages: 2
Registered: March 2012
Junior Member
Hi,
we have the exact same problem since we migrated from eclipselink 2.0.0 to 2.3.2 in an equinox OSGI environment. We are using SQLServer in this particular case and have a table where almost all columns are named with uppercase characters. Only the id column is lowercase (there was a reason for doing so).
Using a specific native ReadAll query yields the following result (exception):
<-- snip -->
Exception Description: The primary key read from the row [ArrayRecord(
... PROCESSED_EMAIL.OBSOLETE_SINCE => null
=> 1331136969211
PROCESSED_EMAIL.RETRY_COUNT => 0)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=ProcessedEmail sql=" SELECT * FROM PROCESSED_EMAIL email WHERE email.STATUS <> 'SQL_DELETED' AND email.OBSOLETE_SINCE is null AND (email.RETRY_COUNT is null or email.RETRY_COUNT < 2) AND email.LAST_CHANGE < DATEADD(hh, -2, GETDATE()) ORDER BY email.RETRY_COUNT asc, email.idscm_to_kasa desc")
...
<-- snap -->
As you can see, the column name for the id column (with value 1331136969211) is omitted in the printed ArrayRecord and this seems to be the problem.

The same query with the exact same Persistent Object definition ran without problems in 2.0.0 .
I could get it running again by renaming the id column to all uppercase characters.

I consider this, however, to be a bug in eclipselink.

Best regards,
Guido
Re: Primary keys must not contain null [message #816263 is a reply to message #815998] Thu, 08 March 2012 16:17 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Guido - I cannot see why this would have worked in EclipseLink 2.0. The problem here is that the field is likely defined with a case that is different than the database is returning, so that it is not returned when the resultset is searched using the field name string. The fix is to use the "eclipselink.jdbc.uppercase-columns" if you have the field names defined in your metadata using uppercase but the database returns lower case. A better solution though might be to use the "eclipselink.jpa.uppercase-column-names" which will uppercase both sides - so it doesn't matter what case you defined the field as in the model.

Best Regards,
Chris
Re: Primary keys must not contain null [message #816268 is a reply to message #816263] Thu, 08 March 2012 16:21 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 5
Registered: March 2012
Junior Member
Hi,

Something has changed in EclipseLink. I upgraded, and my code fails, I roll back to the previous version and by code works. In my case, the fields are all in lowercase, both in the db and in my query. Since GF3.1.2 was released, google is slowly returning more and more hits on this issue. Something must have changed....
Re: Primary keys must not contain null [message #816286 is a reply to message #815997] Thu, 08 March 2012 16:49 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Kresimir:
The "select count(1) as broj from wowzalog" native query is likely suffering from the same problem as Guido's post, since the field is defaulted to "BROJ" and the database query will return it as "broj". Again though, I do not see how this would have worked in EclipseLink 2.3 unless you also had the "eclipselink.jdbc.uppercase-columns" persistence unit property set and don't when you upgraded.

The problem should go away if you set the "eclipselink.jpa.uppercase-column-names" property to true.

The "select count(1) as broj from wowzalog" should probably not be used to return an entity though as it will cause there to be a Broj entity to be managed with count as an ID. You instead might want to use the raw data returned directly rather than an entity.
Re: Primary keys must not contain null [message #816291 is a reply to message #816286] Thu, 08 March 2012 16:56 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Missing name-
The "eclipselink.jpa.uppercase-column-names" should work for you as well, but can you try replacing the EclipseLink jars in glassfish 3.1.2 with the EclipseLink jars from GF 3.1.1? This will rule out a glassfish change, and help narrow down what else might have changed to cause it to have worked previously. My understanding is that this should never have worked by default because EclipseLink has always been case sensitive by default. The only EclipseLink change I am aware of that affects native queries case issues was for bug 299926 in EclipseLink 2.1 which added the uppercase property I mentioned. So it could be some other issue that is manifesting

Best Regards,
Chris
Re: Primary keys must not contain null [message #816423 is a reply to message #816291] Thu, 08 March 2012 21:05 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 5
Registered: March 2012
Junior Member
Hi, I moved the following files from my GF 3.1.1 installation to GF 3.1.2 and immediately the issue was resolved:
./glassfish/modules/org.eclipse.persistence.jpa.jar
./glassfish/modules/org.eclipse.persistence.antlr.jar
./glassfish/modules/org.eclipse.persistence.core.jar
./glassfish/modules/org.eclipse.persistence.oracle.jar
./glassfish/modules/org.eclipse.persistence.moxy.jar
./glassfish/modules/org.eclipse.persistence.asm.jar
./glassfish/modules/org.eclipse.persistence.jpa.modelgen.jar


I'll try the eclipselink.jpa.uppercase-column-names on the new version and see what difference it makes

Paul
Re: Primary keys must not contain null [message #816437 is a reply to message #816423] Thu, 08 March 2012 21:31 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 5
Registered: March 2012
Junior Member
Ok, eclipselink.jpa.uppercase-column-names=true solves the problem....

But Why? The columns and tables in the DB are all lowercase and so is the query and entity column names. I'll run my test with debug on and post the log
Re: Primary keys must not contain null [message #816451 is a reply to message #816437] Thu, 08 March 2012 21:51 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 5
Registered: March 2012
Junior Member
Here is the trace with eclipselink.jpa.uppercase-column-names=false:


2012-03-08T23:39:14.816+0200|TX beginTransaction, status=STATUS_ACTIVE
2012-03-08T23:39:14.817+0200|Execute query ReadAllQuery(referenceClass=Imsu sql="select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.iccid=?")
2012-03-08T23:39:14.817+0200|Connection acquired from connection pool [default].
2012-03-08T23:39:14.817+0200|reconnecting to external connection pool
2012-03-08T23:39:14.819+0200|select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.iccid=?
#]
2012-03-08T23:39:14.867+0200|Local Exception Stack:
Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [ArrayRecord(
=> 39
=>
=>
=>
=> 1
=> 1
=> 1)] during the execution of the query was detected to be null. Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Imsu sql="select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.iccid=?")
at org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:895)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:584)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:560)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:717)
at org.eclipse.persistence.queries.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:769)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:433)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1081)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:844)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1040)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:392)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1128)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2871)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1516)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1498)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:485)
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.getSingleResult(EJBQueryImpl.java:773)




Here is the trace with eclipselink.jpa.uppercase-column-names=true:

2012-03-08T23:50:07.197+0200|TX beginTransaction, status=STATUS_ACTIVE

2012-03-08T23:50:07.197+0200|Execute query ReadAllQuery(referenceClass=Imsu sql="select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.iccid=?")

2012-03-08T23:50:07.198+0200|Connection acquired from connection pool [default].

2012-03-08T23:50:07.198+0200|reconnecting to external connection pool

2012-03-08T23:50:07.199+0200|select imsu.* from imsu, impi where impi.id_imsu=imsu.id and impi.iccid=?
#]

2012-03-08T23:50:07.208+0200|begin unit of work flush

2012-03-08T23:50:07.209+0200|end unit of work flush
Re: Primary keys must not contain null [message #816777 is a reply to message #816263] Fri, 09 March 2012 08:46 Go to previous messageGo to next message
Guido Vrankar is currently offline Guido Vrankar
Messages: 2
Registered: March 2012
Junior Member
Chris,
as to why it worked in version 2.0.0: The database returned the column name in lowercase just like I had it defined. My persistent class was annotated like this:
@Id
@Column(name="idscm_to_kasa")
public Long id;

@Column(name="MESSAGE_ID", length=1000, nullable = false)
public String messageId;
... more stuff omitted


With version 2.3.2 eclipselink seems to ignore my @Column name annotation in this particular context (select with a certain native query). When using the following NamedQueries I got the correct result with 2.3.2.

@NamedQuery(name="processedEmails", query="SELECT email FROM ProcessedEmail email"),
@NamedQuery(name="processedEmailById", query="SELECT email FROM ProcessedEmail email WHERE email.messageId = :id"),

Is it possible that eclipselink 2.3 honours the case of the @Column annotation most of the time, but in some special cases it ignores it when traversing the result set and just uses some default (that seems to be "look for the column name in uppercase" in my case)?
Re: Primary keys must not contain null [message #817092 is a reply to message #816777] Fri, 09 March 2012 16:19 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
I am unsure why this would have worked in a prior version, since the "eclipselink.jpa.uppercase-column-names" was added to resolve the issue that kept cropping up.

The issue (also described in bug 299926) was mostly with native queries that return entities, as when EclipseLink generates JPQL queries it knows the position of each field and so does not need to use the field to look up the value. This is why NamedQuery and createQuery api will work regardless of the database or field names defined in the mappings.
Native queries that return/build entities though must use the field name string as it is defined in the mapping to search for the value returned. The problem is that users had/have the tendency to define their fields using lower or mixed case, and on other fields let the default be used which is uppercase. So while selects still work due to most databases being case insensitive by default, the string they return for the column name isn't. There is no way to look up "ID" using "id" on a hashmap/table structure. And different databases return the column name differently - Oracle always returns them in uppercase, while others may return them as lower (assuming they are not delimited).

EclipseLink early on had the "eclipselink.jdbc.uppercase-column-names" flag, which would take the resultsets and uppercase the column names. This worked in a lot of cases, but required that users define the mappings using the uppercase field name. So it would not help if you used @Column(name="idscm_to_kasa").
"eclipselink.jpa.uppercase-column-names" was added (bug 299926) to uppercase both the field name used for searches, and set the "eclipselink.jdbc.uppercase-column-names" so that both sides would be uppercase so that using a hashmap would work.

That said, EclipseLink 2.3 should honor the column annotation 100% of the time in all versions. The only thing I can think of is you set the eclipselink.jdbc.uppercase-column-names which would cause the id field to be returned as "IDSCM_TO_KASA" instead of the "idscm_to_kasa" that is expected for the native query, and not have had this flag set when using a prior version.


Mising name - your fields are defined in lower case, but your query uses select *, so it is at the mercy of what the database returns. If you are using Oracle for instance, it will always return the field names in upper case (unless they are delimited), while others might return them in lower case. Check what database you are using and what it returns.

Can you also get the exact version and build number for the EclipseLink version in gf 3.1.1 you are using? This can be done by setting the logging property to log at FINEST, which will print the version at login. With this I can try checking what might have changed to affect the issue, such as if the flag was set to true by default in the particular build used.

Bug 294267 exists to have this default changed from false to true - please vote for it.
Re: Primary keys must not contain null [message #839492 is a reply to message #817092] Sun, 08 April 2012 20:23 Go to previous messageGo to next message
FiruzzZ Mising name is currently offline FiruzzZ Mising name
Messages: 19
Registered: October 2011
Junior Member
same problem, I tried all to make it works but the only solution is back to EL 2.2.1
Re: Primary keys must not contain null [message #839981 is a reply to message #839492] Mon, 09 April 2012 14:02 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Can you provide details on what you are experiencing and what you tried? Most of what was described would have been resolved using the "eclipselink.jpa.uppercase-column-names"=true persistence property.
Re: Primary keys must not contain null [message #865614 is a reply to message #839981] Mon, 30 April 2012 13:40 Go to previous messageGo to next message
John Lister is currently offline John Lister
Messages: 3
Registered: April 2010
Junior Member
I have the same problem. All ok in GF 3.1.1, after upgrade to 3.1.2 I get the primary key is null error. Using PG 8.4 and latest jdbc drivers.

I've added
<property name="eclipselink.jpa.uppercase-column-names" value="true"/>
to my peristence.xml file, but still have the same problem. Reverting the eclipse jars fixes the issue so it is definitely an eclipselink problem.

The relevent code is
patterns=em.createNativeQuery("SELECT * FROM django.summary_pattern WHERE retailer_id = ? ORDER BY template DESC, position", SummaryPattern.class).setParameter(1,retailer.getId()).getResultList();


and

@Entity
@Table(name = "summary_pattern", schema = "django")
public class SummaryPattern implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
}

I even changed the above query to explicitly list the fields but that made no difference either. The table is defined with all lowercase names and I can see that postgresql is returning the lowercase names in the response.

Any ideas?

ps. I note from //www.eclipse.org/forums/index.php/mv/msg/299142/808386/ that the array record should have the field names in there, instead I get
[ArrayRecord(
=> 13124
=> 2011-07-27 09:41:02.0
=> 2011-07-27 09:41:02.0
=> 1710
=> 6632
=> 1
=> (.+?)-
=> (.+?)-)]
So I'm guessing somewhere the field names are getting lost??? Odd that only eclipselink has changed

[Updated on: Mon, 30 April 2012 13:46]

Report message to a moderator

Re: Primary keys must not contain null [message #879131 is a reply to message #815419] Wed, 30 May 2012 15:15 Go to previous messageGo to next message
Craig Garrett is currently offline Craig Garrett
Messages: 1
Registered: May 2012
Junior Member
I'm hoping to bump this thread. I have the same problem with EclipseLink 2.3.2. Rolling back to 2.2.1 fixed it. Any word if a bug has been generated for 2.3.2?

[Updated on: Wed, 30 May 2012 15:16]

Report message to a moderator

Re: Primary keys must not contain null [message #879727 is a reply to message #879131] Thu, 31 May 2012 18:23 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1035
Registered: July 2009
Senior Member
Setting the eclipselink.jpa.uppercase-column-names property to true is the solution if the case the fields are defined in does not match the case the database returns them in.

I have not seen a bug filed to investigate why something that worked in 2.2.1 doesn't in 2.3.2, and I have not been able to reproduce the issue locally. I would recommend you file a bug with particular details like:
a) particular build used. This should show up in the EclipseLink exception and logs with logging turned on.
b) The entity and field names used
c) The query itself, and the results returned when it is not returning an entity
d) What the database returns for the metadata. This can be done using DatabaseAccessor getTableInfo(String catalog, String schema, String tableName, String[] types) if you want. Something like:
print(((JpaEntityManager)em.getDelegate()).getSession().getAccessor().getTableInfo(null, null, "tableName", null, ((JpaEntityManager)em.getDelegate()).getSession()))
e) the properties contained in ((JpaEntityManager)em.getDelegate()).getProperties();
f) the results of the above on both versions.


Re: Primary keys must not contain null [message #885235 is a reply to message #879727] Tue, 12 June 2012 16:47 Go to previous message
Mike Weeda is currently offline Mike Weeda
Messages: 1
Registered: June 2012
Junior Member
I am new to JPA. Am using NetBeans 7.1.2 and Jave EE 6. JPA using Eclipselink and Glassfish (3.1.2) are provided by those tools. This problem with case on database column names is happening with MySQL database under Windows 7.

Replacing the 3.1.2 glassfish/modules/org.exlipse.persistence.* components with those components from 3.1.1 resolved the issue.

[Updated on: Tue, 12 June 2012 18:01]

Report message to a moderator

Previous Topic:Understanding internals
Next Topic:Entity lazy loading strategy from remote client
Goto Forum:
  


Current Time: Thu Oct 30 12:58:50 GMT 2014

Powered by FUDForum. Page generated in 0.03903 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software