Opened 10 years ago

Closed 10 years ago

#888 closed enhancement (fixed)

Make dynamic table creation code more stable

Reported by: nicklas Owned by: nicklas
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 10 years ago by nicklas

(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 10 years ago by nicklas

  • Owner changed from everyone to nicklas
  • Status changed from new to assigned

comment:3 Changed 10 years ago by nicklas

  • Resolution set to fixed
  • Status changed from assigned to closed

(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.