table exporter says java.io.IOException: Unknown column 'spt.ch2' in 'where clause' when exporting Illumina 1-channel data

[Jari Häkkinen]

I get the following file output when I run the table exporter in a spot data view for a bioassayset. I have 1-channel data so the reference to a second channels is unexpected. I select to export the current page only and use the preset 'default' columns. I have sorted the data in ascending ch 1 values.

Position	Ch 1	
java.io.IOException: Unknown column 'spt.ch2' in 'where clause'
	at net.sf.basedb.clients.web.plugins.SimpleExport.exportProperties(SimpleExport.java:580)
	at net.sf.basedb.clients.web.plugins.SimpleExport.performExport(SimpleExport.java:309)
	at net.sf.basedb.core.plugin.AbstractExporterPlugin.doExport(AbstractExporterPlugin.java:187)
	at net.sf.basedb.core.PluginResponse.downloadImmediately(PluginResponse.java:181)
	at org.apache.jsp.common.plugin.index_jsp._jspService(index_jsp.java:602)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at net.sf.basedb.clients.web.servlet.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)
Caused by: net.sf.basedb.core.BaseException: Unknown column 'spt.ch2' in 'where clause'
	at net.sf.basedb.core.QueryExecutor.getFutureResult(QueryExecutor.java:194)
	at net.sf.basedb.core.QueryExecutor.executeQuery(QueryExecutor.java:91)
	at net.sf.basedb.core.AbstractSqlQuery.iterate(AbstractSqlQuery.java:198)
	at net.sf.basedb.core.DynamicQuery.iterate(DynamicQuery.java:46)
	at net.sf.basedb.clients.web.plugins.DynamicQueryWrapper.iterate(DynamicQueryWrapper.java:62)
	at net.sf.basedb.clients.web.plugins.SimpleExport.exportProperties(SimpleExport.java:525)
	... 28 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'spt.ch2' in 'where clause'
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:224)
	at net.sf.basedb.core.QueryExecutor$FutureResultSet.call(QueryExecutor.java:212)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
	... 1 more

[Nicklas Nordborg]

I assume that the spot listing in the web interface works as expected, so the error is hard to explain since the export *should* use an identical query, but obviously the export query is different. That the complain is about the 'where' part of the query indicates that the problem is an incorrect filter.

Have you tried using the 'view/presests -> clear filter' to see if it helps? If it helps it is a mystery how the filter got there in the first place and why it doesn't make the spot listing fail as well.

Could it be some kind of "leakage" to the Illumina experiment from another (2-channel) experiment which has a filter on the 'ch2' column?

When speaking of "leakage". Do you go through the export sequence from start to failure without navigating to another experiment in the main window? This shouldn't affect the export unless you open a second export window, but that should be detected by the GUI and a warning should be displayed.

[Nicklas Nordborg]

Hmmm.... I just noticed a something strange with a two-channel experiment as well... I think it is related since it seems to be related to filtering on columns that are hidden.

1. Go to the "Spot data" tab of any two-channel expriment
2. Add a filter on the 'Ch 2' column
3. Export the data with the table exporter. It should work as expected.
4. Clear the filter on 'Ch 2' and hide the 'Ch 2' column.
5. Export the data again. The exported data is the same as in step 3 (except that no channel-2 values are exported). The filter on 'Ch 2' that was removed is still applied in the export.
6. If the 'Ch 2' column is made visible again the export works as expected.

I currently have no access to a one-channel experiment, but I think if we go to a one-channel experiment and try to export the data the filter on 'Ch 2' is still remembered somewhere and applied to the export which then fails since there is no ch2 column. If this theory is correct, a workaround is to:

1. Go to a two-channel experiment
2. Display the spot data
3. Clear all filters
4. Display all columns
5. Click on the 'Export' button and then close the popup again.

[Nicklas Nordborg]

I just checked the code and I think the problem lies in the lack of a 'subContext' parameter in the call to Base.getAndSetCurrentContext() in the ExportItems section in the file www/views/experiments/spotdata/index.jsp'. The value of 'subContext' parameter depends on the raw data type used in the experiment. If it is not specified, it means that the returned context holds a mix of context information from previously used experiments with a different raw data type. All other places that calls Base.getAndSetCurrentContext()` use the 'subContext' parameter so it is probably only the export functionality that is affected.

[Nicklas Nordborg]

Here is the correct formatting of the above comment.

I just checked the code and I think the problem lies in the lack of a 'subContext' parameter in the call to Base.getAndSetCurrentContext() in the ExportItems section in the file www/views/experiments/spotdata/index.jsp. The value of 'subContext' parameter depends on the raw data type used in the experiment. If it is not specified, it means that the returned context holds a mix of context information from previously used experiments with a different raw data type. All other places that calls Base.getAndSetCurrentContext() use the 'subContext' parameter so it is probably only the export functionality that is affected.

[Martin Svensson]

(In [4923]) Fixes #1305 table exporter says java.io.IOException: Unknown column 'spt.ch2' in 'where clause' when exporting Illumina 1-channel data