Missing query, SET_INTENSITYTRANSFORM_ON_BIOASSAYSETS, in common-queries.xml

[Nicklas Nordborg]

This query is needed to update from BASE 2.11 to BASE 2.12 and was accidentally removed in [5021] (between BASE 2.12.1 and 2.13). This means that trying to upgrade a BASE server from 2.11 or lower to 2.13 or higher will fail since the query is missing.

A typical error message when running ./updatedb.sh is:

[70%]   The adjustment of the existing items failed: Node '<query
id="SET_INTENSITYTRANSFORM_ON_BIOASSAYSETS">' was not found in file
'/common-queries.xml' or in file '/mysql-queries.xml'

net.sf.basedb.core.BaseException: Node '<query id="SET_INTENSITYTRANSFORM_ON_BIOASSAYSETS">' was not found in file '/common-queries.xml' or in file '/mysql-queries.xml'
at net.sf.basedb.core.PredefinedQuery.getQueryString(PredefinedQuery.java:158)
at net.sf.basedb.core.HibernateUtil.getPredefinedQuery(HibernateUtil.java:1685)
at net.sf.basedb.core.HibernateUtil.getPredefinedQuery(HibernateUtil.java:1671)
at net.sf.basedb.core.Update.adjustExistingItems(Update.java:3128)
at net.sf.basedb.install.InitDB.main(InitDB.java:73)

If you get into this situation the simplest workaround is to manually add the missing query to either mysql-queries.xml or postgres-queries.xml. Both files are located in the www/WEB-INF/classes directory. Open the file that matches your database in a text editor and add the following entry to it:

<query id="SET_INTENSITYTRANSFORM_ON_BIOASSAYSETS" type="HQL">
  <sql>
    UPDATE BioAssaySetData bas
    SET bas.intensityTransform = :transform
    WHERE bas.intensityTransform IS NULL
  </sql>
  <description>
    An update query that sets the intensityTransform on all 
    bioassay sets that doesn't have a value.
  </description>
</query>

Save the file and then run the updatedb.sh script again.

Theoretically, it should also be possible to perform a two-step upgrade. Eg. first update your BASE installation to BASE 2.12 and then to the version you really want. But this is a lot more work and requires that you start out with an unchanged and working database. Do not try this approach if your first attempt failed with the above error.