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

mschwarzer · March 2018

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!

Pavithra_Rao · March 2018

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,

Edin_Klapic · March 2018

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

mschwarzer · March 2018

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_Klapic · March 2018

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,