Opened 13 years ago

Closed 13 years ago

#888 closed enhancement (fixed)

Make dynamic table creation code more stable

Reported by: Nicklas Nordborg Owned by: Nicklas Nordborg
Priority: minor Milestone: BASE 2.6
Component: core Version:
Keywords: Cc:

Description

If the timing is unfortunate between two plug-in jobs, the may both try to create the same dynamic database table. One of them will succeed, the second will get an exception similar to:

net.sf.basedb.core.BaseException: Table 'D13Pos' already exists
at net.sf.basedb.core.HibernateUtil.createVirtualTable(HibernateUtil.java:605)
at net.sf.basedb.core.VirtualDb.createTables(VirtualDb.java:290)
at 
net.sf.basedb.core.PositionBatcher.buildInsertSelectSql(PositionBatcher.java:303)
at net.sf.basedb.core.PositionBatcher.insert(PositionBatcher.java:246)
at 
net.sf.basedb.util.IntensityCalculatorUtil.createRootBioAssaySet(IntensityCalculatorUtil.java:204)
at 
net.sf.basedb.plugins.IntensityCalculatorPlugin.run(IntensityCalculatorPlugin.java:249)
at 
net.sf.basedb.core.PluginExecutionRequest.invoke(PluginExecutionRequest.java:89)
at net.sf.basedb.core.InternalJobQueue$JobRunner.run(InternalJobQueue.java:421)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'D13Pos' 
already exists
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1350)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1266)
at 
com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64)
at net.sf.basedb.core.HibernateUtil.createVirtualTable(HibernateUtil.java:581)

The code does check if the table exists before trying to create it, but this is not an atomic operation. There are a couple of possible solutions:

  • Modify the SQL that creates the table to something like: CREATE TABLE IF NOT EXISTS... or whatever the syntax is for the various databases. This has to go into the DbEngine? interface unless we can get Hibernate to help us with it. We may need to have a fallback option if the database doesn't supports this kind of checks.
  • Try to analyze the error message/exception class. SQL exceptions are tricky not standardized between various databases.

Luckily, all table creation goes through HibernateUtil?.createVirtualTable() so there is only a single place to fix.

Change History (3)

comment:1 Changed 13 years ago by Nicklas Nordborg

(In [4099]) References #888: Make dynamic table creation code more stable

Now works for MySQL. Need to investigate if it is possible to solve for Postgres as well.

comment:2 Changed 13 years ago by Nicklas Nordborg

Owner: changed from everyone to Nicklas Nordborg
Status: newassigned

comment:3 Changed 13 years ago by Nicklas Nordborg

Resolution: fixed
Status: assignedclosed

(In [4100]) Fixes #888: Make dynamic table creation code more stable

Removed deboug output. Searching on the net reveals no better solution for Postgres than what is already there (checking and creating the table in two steps).

Note: See TracTickets for help on using tickets.