Bug 534069 - Unable to create ACR(AccessControlRuleEntity) entity when changing to MySQL DB
Summary: Unable to create ACR(AccessControlRuleEntity) entity when changing to MySQL DB
Status: UNCONFIRMED
Alias: None
Product: OM2M
Classification: IoT
Component: Platform (show other bugs)
Version: 1.0.0   Edit
Hardware: PC Windows 10
: P3 major
Target Milestone: ---   Edit
Assignee: Project Inbox CLA
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-25 22:09 EDT by teak jin kim CLA
Modified: 2018-04-25 22:09 EDT (History)
0 users

See Also:

Attachments
The sql in the attached file is a file that dumps the DB schema after running om2m. (75.77 KB, application/octet-stream)
2018-04-25 22:09 EDT, teak jin kim CLA 		no flags Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description teak jin kim CLA 2018-04-25 22:09:21 EDT 
Created attachment 273789 [details]
The sql in the attached file is a file that dumps the DB schema after running om2m.

You have changed from the previously used H2DB to MySQL.
You have checked the operation after making changes, but an exception occurs during the initial operation.
We have a problem with creating an initial ACR at ACR and tracking it shows that the ACR entity is not created even though it should be created as a table. Other tables are created normally.
Regarding this, I attach my MySQL server version and screenshot.

The sql in the attached file is a file that dumps the DB schema after running om2m.

OS : Windows 10
MySQL : 5.7.22 Commuity
MySQL JDBC : 5.1.46
Bulid : Maven 3.5.3
JAVA : 1.8.0_172 JDK

Regards.




Here is log trace.

C:\Users\home\Desktop\org.eclipse.om2m\org.eclipse.om2m.site.in-cse\target\products\in-cse\win32\win32\x86_64>java -jar -ea -Declipse.ignoreApp=true -Dosgi.clean=true -Ddebug=true plugins/org.eclipse.equinox.launcher_1.3.0.v20140415-2008.jar -console -noExit
[INFO] - org.eclipse.om2m.datamapping.jaxb.Activator
Starting Data Mapper
[INFO] - org.eclipse.om2m.datamapping.jaxb.Activator
Registering XML Mapper Service..
[INFO] - org.eclipse.om2m.datamapping.jaxb.Activator
XML Mapper service registered.
[INFO] - org.eclipse.om2m.datamapping.jaxb.Activator
Registering JSON Mapper Service..
[INFO] - org.eclipse.om2m.datamapping.jaxb.Activator
JSON Mapper service registered.
[INFO] - org.eclipse.om2m.persistence.eclipselink.Activator
Starting Persistence JPA plugin...
[INFO] - org.eclipse.om2m.persistence.eclipselink.internal.DBServiceJPAImpl
Initializing Database...
[INFO] - org.eclipse.om2m.persistence.eclipselink.internal.DBServiceJPAImpl
=====================DRIVER com.mysql.jdbc.Driver
[INFO] - org.eclipse.om2m.persistence.eclipselink.internal.DBServiceJPAImpl
Creating new EntityManagerFactory...
[INFO] - org.eclipse.om2m.binding.coap.Activator
Register CoAP RestClientService..
[INFO] - org.eclipse.om2m.binding.coap.Activator
Starting CoAP server
4월 26, 2018 10:56:52 오전 ch.ethz.inf.vs.californium.network.config.NetworkConfig createStandardWithFile
정보: Create standard properties with file Californium.properties
4월 26, 2018 10:56:52 오전 ch.ethz.inf.vs.californium.server.Server start
정보: Starting server
4월 26, 2018 10:56:52 오전 ch.ethz.inf.vs.californium.network.CoAPEndpoint start
정보: Starting Endpoint bound to 0.0.0.0/0.0.0.0:5683
[INFO] - org.eclipse.om2m.binding.coap.Activator
CseService opened
[INFO] - org.eclipse.om2m.binding.http.Activator
Register HTTP RestClientService..
[INFO] - org.eclipse.om2m.binding.http.Activator
HTTP RestClientService is registered.
[INFO] - org.eclipse.om2m.core.Activator
Starting CSE...
[INFO] - org.eclipse.om2m.core.Activator
Added Data Mapper Service: application/xml
[INFO] - org.eclipse.om2m.core.Activator
Added Data Mapper Service: application/json
[INFO] - org.eclipse.om2m.core.Activator
Rest client service discovered. Protocol: coap
[INFO] - org.eclipse.om2m.core.Activator
Rest client service discovered. Protocol: http
[INFO] - org.eclipse.om2m.core.Activator
Rest client service discovered. Protocol: mqtt
[INFO] - org.eclipse.om2m.binding.http.Activator
HttpService discovered
[INFO] - org.eclipse.om2m.binding.http.Activator
Register / context
[INFO] - org.eclipse.om2m.webapp.resourcesbrowser.json.Activator
HttpService discovered
[INFO] - org.eclipse.om2m.webapp.resourcesbrowser.json.Activator
Register /webpage http context
osgi> [INFO] - org.eclipse.om2m.persistence.eclipselink.internal.DBServiceJPAImpl
DataBase initialized.
[INFO] - org.eclipse.om2m.persistence.eclipselink.Activator
Registering Database (JPA-EL) Service
[INFO] - org.eclipse.om2m.core.Activator
DataBase persistence service discovered
[INFO] - org.eclipse.om2m.core.thread.CoreExecutor
Creating thread pool with corePoolSize=5 & maximumSize=50
[INFO] - org.eclipse.om2m.core.CSEInitializer
Initializating the cseBase
[INFO] - org.eclipse.om2m.core.CSEInitializer
Create AccessControlPolicy resource
Exception in thread "pool-2-thread-1" javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
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 'CREATE, DELETE, DISCOVERY, NOTIFY, RETRIEVE, UPDATE) VALUES (3, 1, 1, 1, 1, 1, 1' at line 1
Error Code: 1064
Call: INSERT INTO ACR (ACR_ID, CREATE, DELETE, DISCOVERY, NOTIFY, RETRIEVE, UPDATE) VALUES (?, ?, ?, ?, ?, ?, ?)
        bind => [7 parameters bound]
Query: InsertObjectQuery(org.eclipse.om2m.commons.entities.AccessControlRuleEntity@4367ceba)
        at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:157)
        at org.eclipse.om2m.persistence.eclipselink.internal.DBTransactionJPAImpl.commit(DBTransactionJPAImpl.java:45)
        at org.eclipse.om2m.core.CSEInitializer.initACP(CSEInitializer.java:371)
        at org.eclipse.om2m.core.CSEInitializer.init(CSEInitializer.java:92)
        at org.eclipse.om2m.core.Activator$2$1.run(Activator.java:106)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
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 'CREATE, DELETE, DISCOVERY, NOTIFY, RETRIEVE, UPDATE) VALUES (3, 1, 1, 1, 1, 1, 1' at line 1
Error Code: 1064
Call: INSERT INTO ACR (ACR_ID, CREATE, DELETE, DISCOVERY, NOTIFY, RETRIEVE, UPDATE) VALUES (?, ?, ?, ?, ?, ?, ?)
        bind => [7 parameters bound]
Query: InsertObjectQuery(org.eclipse.om2m.commons.entities.AccessControlRuleEntity@4367ceba)
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:900)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:962)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:631)
        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.ClientSession.executeCall(ClientSession.java:298)
        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.insertObject(DatasourceCallQueryMechanism.java:377)
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:165)
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:180)
        at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:489)
        at org.eclipse.persistence.queries.InsertObjectQuery.executeCommit(InsertObjectQuery.java:80)
        at org.eclipse.persistence.queries.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:90)
        at org.eclipse.persistence.internal.queries.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:301)
        at org.eclipse.persistence.queries.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:58)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:798)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
        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:1737)
        at org.eclipse.persistence.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:226)
        at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:193)
        at org.eclipse.persistence.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:138)
        at org.eclipse.persistence.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:4207)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1441)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1531)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:277)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1169)
        at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:132)
        ... 7 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 'CREATE, DELETE, DISCOVERY, NOTIFY, RETRIEVE, UPDATE) VALUES (3, 1, 1, 1, 1, 1, 1' at line 1
        at sun.reflect.GeneratedConstructorAccessor30.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.Util.getInstance(Util.java:408)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
        at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:890)
        ... 39 more