I am working on a JSF-Web-Application with a MySQL-Database and JPA in a Java EE-Context.
I am using the following JPQL to get some data from the Database. The EntityGenerierteAufgabe has the fields createTime and changeTime. CreateTime is set when the database entry is written. In this case changeTime is NULL. When the database entry is updated changeTime will also be set. Now I want to select from this table and order the entries bei changeTime, if set, otherwise by createTime. AND I want to limit the number of results.
String query = "SELECT ga FROM EntityGenerierteAufgabe ga "
+ "WHERE ga.benutzer=:benutzer "
+ "ORDER BY CASE WHEN (ga.changeTime > ga.createTime) THEN ga.changeTime ELSE ga.createTime END DESC";
aufgaben = em.createQuery(query)
.setParameter("benutzer", benutzer)
.setMaxResults(6)
.getResultList();
This JPQL is working fine for one User (BrowserSession). If I do some stress testing with jMeter and use more than one Thread (more than one BrowserSession) and calling the JSF-Page, which calls this JPQL, multiple times causes SOMETIMES the following error:
Call: SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9 FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN DESC LIMIT ?, ?
bind => [3 parameters bound]
Query: ReadAllQuery(referenceClass=EntityGenerierteAufgabe sql="SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9 FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN DESC LIMIT ?, ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002)
at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738)
at org.eclipse.persistence.internal.querie
Information: s.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1751)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
... 124 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 0, 6' at line 1
at sun.reflect.GeneratedConstructorAccessor204.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2030)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1007)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:642)
... 144 more
As you can see the SQL-Query is not build correctly. There is an error inside: ORDER BY CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN DESC LIMIT ?, ?
It is strange because it is working sometimes but sometimes not.
Some extra information: I am using JSF 2.2. The method with the JPQL is called when the relevant ViewController is instanciated:
@javax.faces.view.ViewScoped
@Named
public class ControllerErfolgskontrolle implements Serializable {
@Inject
private SvEnGenerierteAufgabe svEnGenerierteAufgabe;
private List<EntityGenerierteAufgabe> generierteAufgabenBenutzer;
@PostConstruct
private void doInit() {
generierteAufgabenBenutzer = svEnGenerierteAufgabe.getByLimitedSorted(
getControllerSession().getBenutzer());
}
...
}
And the relevant SvEnGenerierteAufgabe-Class with the method creating the JPQL:
@Stateless
public class SvEnGenerierteAufgabe {
@PersistenceContext
EntityManager em;
public List<EntityGenerierteAufgabe> getByLimitedSorted(EntityBenutzer benutzer) {
List<EntityGenerierteAufgabe> aufgaben = new ArrayList<>();
String query = "SELECT ga FROM EntityGenerierteAufgabe ga "
+ "WHERE ga.benutzer=:benutzer "
+ "ORDER BY CASE WHEN (ga.changeTime > ga.createTime) THEN ga.changeTime ELSE ga.createTime END DESC";
aufgaben = em.createQuery(query)
.setParameter("benutzer", benutzer)
.setMaxResults(6)
.getResultList();
return aufgaben;
}
}
I am using the following Verions: - Glassfish 4.1 (build 13) - Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd - MySQL Version: 5.5.27 - mysql-connector-java-5.1.34
I get it working with multiple calls from different jMeter-Test-Threads when I don't set a limit (setMaxResults(...)) OR when I delete the ordering. Both together is not working in some situations like descriped before.
UPDATE 1:
I tested Alex JPQL before, too. I have done it now again. It results in the following generated SQL:
SELECT ID AS a1, ..., CHANGETIME AS a3, CREATETIME AS a4, ...,
CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME ELSE CREATETIME END
FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE
WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME ELSE CREATETIME END DESC LIMIT ?, ?
This is also working fine. But i get same issues when running penetration tests. Sometimes over 1600 Requests (two parallel threads, each sending 800 Requests) work fine and somethimes I get this issue:
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = 2) ORDER BY CASE WHEN (CHANGE' at line 1
and the generated SQL (After the first THEN CHANGETIME a WHEN CREATETIME follows):
SELECT ID AS a1, BEARBEITUNGSZEIT AS a2, CHANGETIME AS a3, CREATETIME AS a4, FORTSCHRITT AS a5, ISTBEENDET AS a6, AUFGABENTYP_ID AS a7, LEVEL_ID AS a8, BENUTZER_ID AS a9, CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN FROM ENTITYGENERIERTEAUFGABE WHERE (BENUTZER_ID = ?) ORDER BY CASE WHEN (CHANGETIME > CREATETIME) THEN CHANGETIME WHEN CREATETIME THEN DESC LIMIT ?, ?
I think there is a synchronazition-problem or a transaction-problem somewhere which results SOMETIMES in a misbuiled SQL, when many requests from different clients will be send. But I don't find the problem. Has anyone an idea? I would test it an update this question.
