java.sql.SQLException: Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'.

Spring JIRA | Matthew Sgarlata | 1 decade ago
  1. 0

    I'm using LocalSessionFactoryBean.updateDatabaseSchema and it's not working very well on SQL Server. Basically, if there are errors in the upgrade scripts generated by Hibernate, the update does not go through. I'm not sure exactly why this is happening but perhaps some log output and a couple fixes to the problem will help illustrate what's going on. Here is some log output: DEBUG (LocalSessionFactoryBean:executeSchemaScript) - Executing schema statement: create table usergrouppermissions (usergroupid int not null, permissionid int not null, primary key (usergroupid, permissionid)) DEBUG (LocalSessionFactoryBean:executeSchemaScript) - Executing schema statement: alter table files add constraint FK5CEBA778EB197A2 foreign key (createdbyuserid) references users WARN (LocalSessionFactoryBean:executeSchemaScript) - Unsuccessful schema statement: alter table files add constraint FK5CEBA778EB197A2 foreign key (createdbyuserid) references users java.sql.SQLException: Cannot alter table 'files' because this table does not exist in database 'tester'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:465) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:427) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1000) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:906) Then, later on in the log file.... WARN (LocalSessionFactoryBean:executeSchemaScript) - Unsuccessful schema statement: alter table usergrouppermissions add constraint FK11713E7042F124D0 foreign key (usergroupid) references usergroups java.sql.SQLException: Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:465) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:427) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1000) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:906) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.executeSchemaScript(LocalSessionFactoryBean.java:888) at org.springframework.orm.hibernate3.LocalSessionFactoryBean$3.doInHibernate(LocalSessionFactoryBean.java:864) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:311) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:288) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.updateDatabaseSchema(LocalSessionFactoryBean.java:857) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:715) I'm not exactly clear why this is happening, but there are two possible solutions to the issue. Ideally, I would like for either of these solutions to be adopted as a chance to LocalSessionFactoryBean. If you don't like either of the changes, it would be at nice to at least introduce a configuration variable so that subclassing LocalSessionFactoryBean isn't needed just to make the updateDatabaseSchema method work on SQL Server. One solution is to set the flush mode on HibernateTemplate to EAGER in the updateDatabaseSchema method. The other solution is to setAutoCommit(true) in executeSchemaScript. I honestly think either or both solution should be OK, because most databases I've worked with (Oracle, MySQL) automatically commit DDL statements anyway, even if the statement appears in a transaction which is later rolled back. It looks like SQL Server doesn't though.

    Spring JIRA | 1 decade ago | Matthew Sgarlata
    java.sql.SQLException: Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'.
  2. 0

    I'm using LocalSessionFactoryBean.updateDatabaseSchema and it's not working very well on SQL Server. Basically, if there are errors in the upgrade scripts generated by Hibernate, the update does not go through. I'm not sure exactly why this is happening but perhaps some log output and a couple fixes to the problem will help illustrate what's going on. Here is some log output: DEBUG (LocalSessionFactoryBean:executeSchemaScript) - Executing schema statement: create table usergrouppermissions (usergroupid int not null, permissionid int not null, primary key (usergroupid, permissionid)) DEBUG (LocalSessionFactoryBean:executeSchemaScript) - Executing schema statement: alter table files add constraint FK5CEBA778EB197A2 foreign key (createdbyuserid) references users WARN (LocalSessionFactoryBean:executeSchemaScript) - Unsuccessful schema statement: alter table files add constraint FK5CEBA778EB197A2 foreign key (createdbyuserid) references users java.sql.SQLException: Cannot alter table 'files' because this table does not exist in database 'tester'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:465) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:427) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1000) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:906) Then, later on in the log file.... WARN (LocalSessionFactoryBean:executeSchemaScript) - Unsuccessful schema statement: alter table usergrouppermissions add constraint FK11713E7042F124D0 foreign key (usergroupid) references usergroups java.sql.SQLException: Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364) at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778) at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214) at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597) at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:465) at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:427) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1000) at net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:906) at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.executeSchemaScript(LocalSessionFactoryBean.java:888) at org.springframework.orm.hibernate3.LocalSessionFactoryBean$3.doInHibernate(LocalSessionFactoryBean.java:864) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:311) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:288) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.updateDatabaseSchema(LocalSessionFactoryBean.java:857) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:715) I'm not exactly clear why this is happening, but there are two possible solutions to the issue. Ideally, I would like for either of these solutions to be adopted as a chance to LocalSessionFactoryBean. If you don't like either of the changes, it would be at nice to at least introduce a configuration variable so that subclassing LocalSessionFactoryBean isn't needed just to make the updateDatabaseSchema method work on SQL Server. One solution is to set the flush mode on HibernateTemplate to EAGER in the updateDatabaseSchema method. The other solution is to setAutoCommit(true) in executeSchemaScript. I honestly think either or both solution should be OK, because most databases I've worked with (Oracle, MySQL) automatically commit DDL statements anyway, even if the statement appears in a transaction which is later rolled back. It looks like SQL Server doesn't though.

    Spring JIRA | 1 decade ago | Matthew Sgarlata
    java.sql.SQLException: Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'.
  3. 0

    fresh install: invalid column name 'scheme' - Atlassian Answers

    atlassian.com | 4 months ago
    java.sql.SQLException: Invalid column name 'scheme'.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Error on create trigger

    symmetricds | 8 years ago | nuttinjeff
    org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [S1000]; error code [311]; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.; nested exception is java.sql.SQLException: Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
  6. 0

    SelectiveDataReplication

    symmetricds | 8 years ago | manmadh
    org.springframework.jdbc.UncategorizedSQLException: ConnectionCallback; uncategorized SQLException for SQL []; SQL state [S1000]; error code [4104]; The multi-part identifier "inserted.store" could not be bound.; nested exception is java.sql. SQLException: The multi-part identifier "inserted.store" could not be bound.

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.SQLException

      Cannot alter table 'usergrouppermissions' because this table does not exist in database 'tester'.

      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic()
    2. jTDS
      JtdsStatement.executeUpdate
      1. net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
      2. net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2778)
      3. net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2214)
      4. net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:597)
      5. net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:465)
      6. net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:427)
      7. net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:1000)
      8. net.sourceforge.jtds.jdbc.JtdsStatement.executeUpdate(JtdsStatement.java:906)
      8 frames
    3. Commons DBCP
      DelegatingStatement.executeUpdate
      1. org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
      1 frame
    4. Hibernate
      LocalSessionFactoryBean.afterPropertiesSet
      1. org.springframework.orm.hibernate3.LocalSessionFactoryBean.executeSchemaScript(LocalSessionFactoryBean.java:888)
      2. org.springframework.orm.hibernate3.LocalSessionFactoryBean$3.doInHibernate(LocalSessionFactoryBean.java:864)
      3. org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:311)
      4. org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:288)
      5. org.springframework.orm.hibernate3.LocalSessionFactoryBean.updateDatabaseSchema(LocalSessionFactoryBean.java:857)
      6. org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:715)
      6 frames