Error running RMAExpress plug-in to create new root bioassay.

[Jari Häkkinen]

I get "Error: Table 'base2dynamic.d1rawparents' doesn't exist" when the RMAExpress plug-in is importing data into BASE. This is correct, there is no d1rawparents table but d1pos and d1spot exists.

trunk version of base with schema #28

I have tried to run updatedb.sh script but it does not help. Not that I expected it to help since I am running on a fresh install of BASE.

Stack trase:

17:58:04,785 WARN DbControl:1039 - Unclosed DbControl during finalize; isLoggedIn = true; clientId = 0; userId = 4 17:58:48,257 ERROR ql:192 - SELECT COUNT(*) AS mapCount FROM base2dynamic.D1RawParents rawmap WHERE rawmap.` cube = 4 GROUP BY rawmap.column, rawmap.position` ORDER BY COUNT(*) DESC limit ? com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'base2dynamic.d1rawparents' doesn't exist

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3176) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:184) at net.sf.basedb.core.DataCube.countSpotMappings(DataCube.java:675) at net.sf.basedb.core.DataCube.onBeforeCommit(DataCube.java:171) at net.sf.basedb.core.DbControl.commit(DbControl.java:357) at se.lu.thep.affymetrix.RMAExpress.run(RMAExpress.java:307) at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:88) at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:420) at java.lang.Thread.run(Thread.java:613)

17:58:51,687 ERROR jobqueue:434 - Exception while executing job: Job[id=6; name=Run plugin: RMAExpress plug-in] net.sf.basedb.core.BaseException: Table 'base2dynamic.d1rawparents' doesn't exist

at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:428) at java.lang.Thread.run(Thread.java:613)

Caused by: net.sf.basedb.core.BaseException: Table 'base2dynamic.d1rawparents' doesn't exist

at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:193) at net.sf.basedb.core.DataCube.countSpotMappings(DataCube.java:675) at net.sf.basedb.core.DataCube.onBeforeCommit(DataCube.java:171) at net.sf.basedb.core.DbControl.commit(DbControl.java:357) at se.lu.thep.affymetrix.RMAExpress.run(RMAExpress.java:307) at net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:88) at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:420) ... 1 more

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'base2dynamic.d1rawparents' doesn't exist

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665) at com.mysql.jdbc.Connection.execSQL(Connection.java:3176) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:184) ... 7 more

[Nicklas Nordborg]

I think this happens because a query to count the number of mappings to raw data is always executed. It shouldn't be executed if the root bioassayset doesn't have any mapping to raw data (as is always the case for Affymetrix data for example). The problematic code is in the DataCube.countSpotMappings method, but I don't know if it has enough information to know if the SQL should be executed or not. It may need to check if a MappingBatcher has been created and check the insert count. The insert count is kept in the batcher. We just need a public method to access it.

[Jari Häkkinen]

Since there is a need to change affy treatment. Is there a possibility to keep the spot count for affymetrix somewhere in the raw tables? Or would this require changes in the data model? I think the spot count should be stored for affymetrix or at least retrieved in someway. This to make the display of affy and genepix the same in raw bioassays listing. Or is this to little gain with too much work?

Maybe na automatic annotation for the cdf (array design) storing the spot count, and the retrieved instead of the failing SQL?

[Nicklas Nordborg]

This problem is not specific to Affymetrix. The same error will happen for all bioassaysets that doesn't use the mapping table to map positions in the bioassayset to positions in the raw data. This could theoretically happen for all types of data since it is not requirement to use the mapping table. Since Affymetrix doesn't put raw data in the database the mapping table is never used and that is why it was discovered for Affymetrix first.

[Nicklas Nordborg]

(In [3075]) Fixes #475: Error running RMAExpress plug-in to create new root bioassay.