Uploading Model Data to RM Server fails due to MySQL Primary Key Violation

mschwarzermschwarzer Member Posts: 6 Contributor II
edited December 2018 in Help

Hi,

 

I have a setup with RM Studio + RM Server (based on RM AMI for AWS). But I cannot upload my pre trained model data to the server (server error 500). In the server.log I got the following message:

 

Caused by: java.io.IOException: Cannot store example set: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '?' for key 'PRIMARY'
at de.rapidanalytics.entity.IOObjectVersion.storeStream(IOObjectVersion.java:202) [rapidminer-server-ejb.jar:]
at de.rapidanalytics.entity.Version.storeStream(Version.java:172) [rapidminer-server-ejb.jar:]
at de.rapidanalytics.ejb.RepositoryStorageEJBImpl.storeStream(RepositoryStorageEJBImpl.java:125) [rapidminer-server-ejb.jar:]
... 105 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '?' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_161]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_161]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_161]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [rt.jar:1.8.0_161]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2462)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2379)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2363)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
at com.rapidminer.server.example.db.ExampleSetToDB.storeMetaData(ExampleSetToDB.java:777) [rapidminer-server-ejb.jar:]
at com.rapidminer.server.example.db.ExampleSetToDB.store(ExampleSetToDB.java:523) [rapidminer-server-ejb.jar:]
at de.rapidanalytics.entity.IOObjectVersion.storeStream(IOObjectVersion.java:192) [rapidminer-server-ejb.jar:]
... 107 more

12:05:52,915 INFO [de.rapidanalytics.ejb.RepositoryStorageEJBImpl] (http-/0.0.0.0:8080-11) admin submitted new object to /apps/model-management/data/Prepared Dataset_Excel_Label_v2.
12:05:52,926 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Dropping data tables for es_23
12:05:52,932 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_23_nominal_mapping'. (Ignoring)
12:05:52,937 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_23_data_1'. (Ignoring)
12:06:29,751 INFO [de.rapidanalytics.ejb.RepositoryStorageEJBImpl] (http-/0.0.0.0:8080-11) admin deletes entry at /apps/model-management/data/Prepared Dataset_Excel_Label_v2
12:06:29,755 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Dropping data tables for es_23
12:06:30,289 INFO [de.rapidanalytics.ejb.RepositoryStorageEJBImpl] (http-/0.0.0.0:8080-11) admin submitted new object to /apps/model-management/data/Prepared Dataset_Excel_Label_v2.
12:06:30,297 INFO [de.rapidanalytics.ejb.RepositoryEJBImpl] (http-/0.0.0.0:8080-11) admin created entry '/apps/model-management/data/Prepared Dataset_Excel_Label_v2 of type 'data'.
12:06:30,299 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Dropping data tables for es_24
12:06:30,299 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Cannot determine number of subtables. Probably tables already deleted: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'rapidminerserver.es_24_meta' doesn't exist
12:06:30,300 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_24_meta'. (Ignoring)
12:06:30,300 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_24_nominal_mapping'. (Ignoring)
12:06:30,300 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_24_annotations'. (Ignoring)
12:06:40,608 INFO [de.rapidanalytics.ejb.RepositoryStorageEJBImpl] (http-/0.0.0.0:8080-11) admin deletes entry at /apps/model-management/data/Prepared Dataset_Excel_Label_v2
12:06:40,612 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Dropping data tables for es_24
12:07:38,536 INFO [de.rapidanalytics.ejb.RepositoryStorageEJBImpl] (http-/0.0.0.0:8080-11) admin submitted new object to /apps/model-management/data/Prepared Dataset_Excel_Label_v2.
12:07:38,544 INFO [de.rapidanalytics.ejb.RepositoryEJBImpl] (http-/0.0.0.0:8080-11) admin created entry '/apps/model-management/data/Prepared Dataset_Excel_Label_v2 of type 'data'.
12:07:38,731 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Dropping data tables for es_25
12:07:38,732 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Cannot determine number of subtables. Probably tables already deleted: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'rapidminerserver.es_25_meta' doesn't exist
12:07:38,732 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_25_meta'. (Ignoring)
12:07:38,732 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_25_nominal_mapping'. (Ignoring)
12:07:38,733 INFO [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-11) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table 'rapidminerserver.es_25_annotations'. (Ignoring)

 

I assume its related to the encoding settings from MySQL (duplicate error for special chars) but as we are using the RM AMI I don't want to the DB settings.

 

Best regards!

 

Tagged:

Best Answer

  • Pavithra_RaoPavithra_Rao Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 123 RM Data Scientist
    Solution Accepted

    Hi @mschwarzer

     

    You can fix this issue by changing the collation of the MySQL database to 'utf8_bin'.

    Example:

    ALTER DATABASE <database/schema name> CHARACTER SET utf8 COLLATE utf8_bin

     

    Let us know if this solves the issue.

     

    Cheers,

     

Answers

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    Hi @mschwarzer,

     

    the error message is not related to the encoding setting you mentioned. Usually tables in databases have PRIMARY Keys which is basically an id column. In addtion, when it comes to missing values, RapidMiner usually displays them as "?".

    So in your case the object you were about to store probably has a missing value in the id column.

     

    The log file you posted is incomplete. The error happens at a previous point and the log file also tells you at which Operator this happens.

    Suggested procedure:

    Identify the error producing Operator from the log file and then check what happens there.

     

    Best,

    Edin

  • mschwarzermschwarzer Member Posts: 6 Contributor II

    Thanks for the reply. Duplicate entry with '?' was just an example, it also it occurs with 'crédit'. That's I assume its a different handling of non-alphanumeric characters in Rapidminer and MySQL. For MySQL 'credit' and 'crédit' are the same but not for Rapidminer. Also, the error does not occur when running a process. The error occurs when copying new data to the server (500 server error).

     

    See screenshot:

    Screenshot from 2018-03-04 19-41-37.png

     

    See error log:

     

    10:39:31,522 INFO  [com.rapidminer.server.example.db.ExampleSetToDB] (http-/0.0.0.0:8080-4) Failed to drop table: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown table
    'rapidminerserver.es_20_data_2'. (Ignoring)
    10:39:59,607 WARNING [de.rapidanalytics.entity.IOObjectVersion] (http-/0.0.0.0:8080-4) Cannot store example set: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationExceptio
    n: Duplicate entry 'crédit' for key 'PRIMARY': com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'crédit' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [rt.jar:1.8.0_161]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) [rt.jar:1.8.0_161]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) [rt.jar:1.8.0_161]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) [rt.jar:1.8.0_161]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2159)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2462)
    ....
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)
    at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_161]

    10:39:59,616 WARNING [de.rapidanalytics.entity.RepositoryException] (http-/0.0.0.0:8080-4) Sending internal server error 500: de.rapidanalytics.entity.RepositoryException: Cannot save data at '/apps/model-management/data/Prepared Dataset_Excel_Label_v2': java.io.IOException: Cannot store example set: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'crédit' for key 'PRIMARY': de.rapidanalytics.entity.RepositoryException: Cannot save data at '/apps/model-management/data/Prepared Dataset_Excel_Label_v2': java.io.IOException: Cannot store example set: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'crédit' for key 'PRIMARY'
    at de.rapidanalytics.ejb.RepositoryStorageEJBImpl.storeStream(RepositoryStorageEJBImpl.java:127) [rapidminer-server-ejb.jar:]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_161]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_161]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_161]
    at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_161]
    at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52) [jboss-as-ee-7.5.0.Final-redhat-15.jar:7.5.0.Final-redhat-15]
    ....
  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    Hi @mschwarzer,

     

    At first I was assuming that we are talking about Example values. But now it seems I was able to reproduce your error when Attribute names are like credit vs. crédit.

     

    If it is Attribute names it might be indeed a collation issue since when it comes to Server the ExampleSets are stored in 3 different database tables named 
    • es_<number>_data_<number>
    • es_<number>_nominal_mapping
    • es_<number>_meta
    The latter one contains the Attribute names as Primary key which would result in the first error you posted.

     

    Regarding your second issue: If the ExampleSet you copied was from your Local Repository to the Server it is the same issue since locally the ExampleSets are stored as file.

     

    Hope we are getting to the point :-)

    Best,

    Edin
Sign In or Register to comment.