Issue 1404: Exception on SQL to drop unused objects when upgrading from 2.3-rc0 to 2.4-rc1
Status:  New
Owner: ----
Reported by OdinG...@gmail.com, May 22, 2012
Affected Version: Upgrade from 2.3-rc0 to 2.4-rc1

See below for command and full output. Note some info is redacted with XXXXX. Have not noticed any resulting issues. I assume not dropping unused values is not a major problem for normal operation.



$ java -jar gerrit-2.4-rc1.war init -d review_site

*** Gerrit Code Review 2.4-rc1
*** 


*** Git Repositories
*** 

Location of Git repositories   [git]: 

*** SQL Database
*** 

Database server type           [H2/?]: 

*** User Authentication
*** 

Authentication method          [OPENID/?]: 

*** Email Delivery
*** 

SMTP server hostname           [XXXXXXXXXXXXXXXXXX]: 
SMTP server port               [465]: 
SMTP encryption                [SSL/?]: 
SMTP username                  [XXXXXXXXXXXXXXXXXXXXX]: 
Change XXXXXXXXXXXXXXXXX's password [y/N]? 

*** Container Process
*** 

Run as                         [gerrit2]: 
Java runtime                   [/usr/lib/jvm/java-6-openjdk/jre]: 
Upgrade /home/gerrit2/review_site/bin/gerrit.war [Y/n]? 
Copying gerrit.war to /home/gerrit2/review_site/bin/gerrit.war

*** SSH Daemon
*** 

Listen on address              [*]: 
Listen on port                 [29418]: 

*** HTTP Daemon
*** 

Behind reverse proxy           [y/N]? 
Use SSL (https://             [Y/n]? 
Listen on address              [*]: 
Listen on port                 [8443]: 
Canonical URL                  [https://XXXXXXXXXXXXXXXX:8443/ 
Create new self-signed SSL certificate [y/N]? 

Upgrading database schema from version 61 to 62 ...
Upgrading database schema from version 62 to 63 ...
Upgrading database schema from version 63 to 64 ...
Execute the following SQL to drop unused objects:

  ALTER TABLE account_groups DROP COLUMN email_only_authors;

Execute now [Y/n]? 
Exception in thread "main" com.google.gwtorm.server.OrmException: Cannot apply SQL
ALTER TABLE account_groups DROP COLUMN email_only_authors
	at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44)
	at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:223)
	at com.google.gerrit.pgm.Init.run(Init.java:79)
	at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:67)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:616)
	at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:167)
	at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:91)
	at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:49)
	at Main.main(Main.java:25)
Caused by: org.h2.jdbc.JdbcSQLException: Column may be referenced by "PUBLIC.EMAIL_ONLY_AUTHORS_CHECK"; SQL statement:
ALTER TABLE account_groups DROP COLUMN email_only_authors [90083-147]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
	at org.h2.message.DbException.get(DbException.java:167)
	at org.h2.message.DbException.get(DbException.java:144)
	at org.h2.table.Table.checkColumnIsNotReferenced(Table.java:478)
	at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:129)
	at org.h2.command.CommandContainer.update(CommandContainer.java:69)
	at org.h2.command.Command.executeUpdate(Command.java:201)
	at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:178)
	at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
	at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42)
	... 11 more




Jun 1, 2012
#1 farshid....@gmail.com
facing the same issue

Upgrading database schema from version 52 to 53 ...
Upgrading database schema from version 53 to 54 ...
Upgrading database schema from version 54 to 55 ...
Renaming "-- All Projects --" to "All-Projects"
Upgrading database schema from version 55 to 56 ...
Upgrading database schema from version 56 to 57 ...
Upgrading database schema from version 57 to 58 ...
Upgrading database schema from version 58 to 59 ...
Upgrading database schema from version 59 to 60 ...
Upgrading database schema from version 60 to 61 ...
Upgrading database schema from version 61 to 62 ...
Upgrading database schema from version 62 to 63 ...
Upgrading database schema from version 63 to 64 ...
Execute the following SQL to drop unused objects:

  DROP SEQUENCE contributor_agreement_id;
  DROP TABLE ref_rights;
  DROP TABLE projects;
  ALTER TABLE account_groups DROP COLUMN email_only_authors;

Execute now [Y/n]? y
Exception in thread "main" com.google.gwtorm.server.OrmException: Cannot apply SQL
ALTER TABLE account_groups DROP COLUMN email_only_authors
	at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44)
Jun 7, 2012
#2 sjakub
Sam problem here (updating from 2.3):

Upgrading database schema from version 61 to 62 ...
Upgrading database schema from version 62 to 63 ...
Upgrading database schema from version 63 to 64 ...
Execute the following SQL to drop unused objects:

  ALTER TABLE account_groups DROP COLUMN email_only_authors;

Execute now [Y/n]? 
Exception in thread "main" com.google.gwtorm.server.OrmException: Cannot apply SQL
ALTER TABLE account_groups DROP COLUMN email_only_authors
        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44)
        at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:223)
        at com.google.gerrit.pgm.Init.run(Init.java:79)
        at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:67)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:167)
        at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:91)
        at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:49)
        at Main.main(Main.java:25)
Caused by: org.h2.jdbc.JdbcSQLException: Column may be referenced by "PUBLIC.EMAIL_ONLY_AUTHORS_CHECK"; SQL statement:
ALTER TABLE account_groups DROP COLUMN email_only_authors [90083-147]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.table.Table.checkColumnIsNotReferenced(Table.java:478)
        at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:129)
        at org.h2.command.CommandContainer.update(CommandContainer.java:69)
        at org.h2.command.Command.executeUpdate(Command.java:201)
        at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:178)
        at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42)
        ... 11 more

Jun 13, 2012
#3 adia2009...@gmail.com
facing the same issue during the upgrade from 2.3 to 2.4
Exception in thread "main" com.google.gwtorm.server.OrmException: Cannot apply SQL
ALTER TABLE account_groups DROP COLUMN email_only_authors
        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:44)
        at com.google.gerrit.pgm.Init$SiteRun.upgradeSchema(Init.java:223)
        at com.google.gerrit.pgm.Init.run(Init.java:79)
        at com.google.gerrit.pgm.util.AbstractProgram.main(AbstractProgram.java:67)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.google.gerrit.launcher.GerritLauncher.invokeProgram(GerritLauncher.java:167)
        at com.google.gerrit.launcher.GerritLauncher.mainImpl(GerritLauncher.java:91)
        at com.google.gerrit.launcher.GerritLauncher.main(GerritLauncher.java:49)
        at Main.main(Main.java:25)
Caused by: org.h2.jdbc.JdbcSQLException: Column may be referenced by "PUBLIC.EMAIL_ONLY_AUTHORS_CHECK"; SQL statement:
ALTER TABLE account_groups DROP COLUMN email_only_authors [90083-147]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.table.Table.checkColumnIsNotReferenced(Table.java:478)
        at org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:129)
        at org.h2.command.CommandContainer.update(CommandContainer.java:69)
        at org.h2.command.Command.executeUpdate(Command.java:201)
        at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:178)
        at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
        at com.google.gwtorm.jdbc.JdbcExecutor.execute(JdbcExecutor.java:42)
        ... 11 more