Fixing SVN Edge Database after failed upgrade to 5.x

问题：Cannot login to console after upgrade to 5.x

 

The 5.x upgrade has not worked for many users due to a failure to properly transform their database schema to the new format. You typically see an HTTP 503 Error when you try to reach the console and the console.log file shows errors like this:

 

升级到5.x后控制界面无法启动，报503，日志中错误大概如下：

org.quartz.SchedulerConfigException: Failure occured during job recovery.
 [See nested exception: org.quartz.JobPersistenceException: Couldn't retrieve trigger: invalid stream header: 00000000
 [See nested exception: java.io.StreamCorruptedException: invalid stream header: 00000000]]
...
[WrapperJarAppMain] ERROR schema.MS024CreateStatsDatabase  - Statistics data was not migrated!
java.sql.SQLDataException: data exception: invalid character value for cast

To recover from these problems you need to manually fix your database. See: 5.xDBUpgradeFix

 

解决办法

 

原文：https://ctf.open.collab.net/sf/wiki/do/viewPage/projects.svnedge/wiki/5.xDBUpgradeFix?showDetails=true

For some users the upgrade to 5.x failed to transform their DB schema from the old to new format. Here are your options to resolve this problem.

一些用户，比如我，自动升级后重启正常，重启系统后无法启动，问题用这篇文章的方法解决。中文部分并非对原文的翻译，只是大概翻译及一些解释。

 

The 5.x upgrade has not worked for many users due to a failure to properly transform their database schema to the new format. You typically see an HTTP 503 Error when you try to reach the console and the console.log file shows errors like this:

org.quartz.SchedulerConfigException: Failure occured during job recovery.
 [See nested exception: org.quartz.JobPersistenceException: Couldn't retrieve trigger: invalid stream header: 00000000
 [See nested exception: java.io.StreamCorruptedException: invalid stream header: 00000000]]
...
[WrapperJarAppMain] ERROR schema.MS024CreateStatsDatabase  - Statistics data was not migrated!
java.sql.SQLDataException: data exception: invalid character value for cast

To recover from these problems you need to manually fix your database. See: 5.xDBUpgradeFix

 

解决问题有两种办法：要么重建数据库，要么手动修正，文章给出了两种方式适用的场景，我是用手动修复解决的。

 

操作前请先备份修改的文件

Create a new database#【重建数据库】

The easiest option is to just allow SVN Edge to create a new database. Most of the data can be trivially recreated. The notable exception is user accounts. If you are using LDAP, then this is likely not an issue, but if you have a lot of local user accounts you probably want to try to manually fix your database. Here are the steps to create a new database:

最简单的办法是重建数据库，只要按照下面的方法做就可以了。但是有一种情况不能选择这种方式。你的svn创建的用户都是在本地保存的。因为Edge SVN用的是hsqldb，所有内容都保存在本地文件，如果用户较多，创建起来非常麻烦。只能选择手动修复，我就是属于这个情况，大概100用户。如果你使用LDAP方式认证用户，不用重建用户，那么可以考虑重建数据库，但重建后需要重新配置系统的各个参数，比如端口，LDAP等。重建非常简单，删除文件后重启，系统会自动重建。

 

1. Stop the Edge console 【停止所有console服务】
2. In the root of the csvn/data folder, move all of the csvn-production-* files to some temp folder. You will ultimately just delete these when things are OK.【先备份，后删除，也可以用mv。删除csvn/data目录下的所有csvn-production-*文件】
3. Start the Edge console ... this will create a new database【启动，恭喜，重建数据库成功】
4. Login using admin / admin credentials【用初始admin用户登录，默认为admin/admin】
5. Re-enter server configuration such as port number, SSL, LDAP Configuration【重新设置系统参数，比如端口，SSL，LDAP之类的】
6. Click Discover Repositories to have existing repositories added back into the UI【点击发现版本库，让系统识别原有的版本库】
7. Recreate any user accounts【重建所有用户】

Manually fix existing database#【手动修复】

The main database file is csvn-production-hsqldb.script in the csvn/data directory. You may also have a csvn-production-hsqldb.log file. If so, you'll need to check both files, but most likely you will just need to edit csvn-production-hsqldb.script. 

 

主要的数据库文件是csvn/data/csvn-production-hsqldb.script ，有可能还有csvn-production-hsqldb.log。这两个文件都需要按照下面方式检查，不过基本上只要修改csvn-production-hsqldb.script ，另一个文件里没有下面说的这些要改的SQL，我也只是修改了一个文件。不过你最好查查，万一你的有呢

 

1. Delete all lines which start with 'INSERT INTO STAT_VALUE'【删除文件里“INSERT INTO STAT_VALUE”开头的所有行，我的大概有将近2000行，不知道干啥的，统统删掉了】
2. Make the following edits. Each SQL statement should be on one line. The form of the current statements may not match exactly, as your table definitions were somehow altered from the initial schema. But you will looking for five (5) create table statements and then the insert statements for those tables. Replace them with the new statements.【在文件中查找下面的语句进行修改，每条语句一行。查找时会发现都是唯一确定的，很容易定位，不过注意他语句里有“eth3”的，貌似只是个名字，不过我看我原来的是“eth0”，也就改成eth0了】，改完在启动就正常了。

---

CREATE MEMORY TABLE PUBLIC.STATISTIC(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,GROUP_ID BIGINT NOT NULL,NAME VARCHAR(255) NOT NULL,TITLE VARCHAR(255) NOT NULL,TYPE VARCHAR(255) NOT NULL,UNIQUE(NAME),CONSTRAINT FK83F2795066CA96B4 FOREIGN KEY(GROUP_ID) REFERENCES PUBLIC.STAT_GROUP(ID))

change to

CREATE MEMORY TABLE STATISTIC(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,TITLE VARCHAR(255) NOT NULL,NAME VARCHAR(255) NOT NULL,TYPE VARCHAR(255) NOT NULL,GROUP_ID BIGINT NOT NULL,CONSTRAINT CTUQ_STAT_NAME UNIQUE(NAME),CONSTRAINT FK_STAT_GROUP_ID FOREIGN KEY(GROUP_ID) REFERENCES STAT_GROUP(ID))

---

INSERT INTO STATISTIC VALUES(1,0,1,'BytesIn','Rate of Data Received (eth3)','GAUGE')
INSERT INTO STATISTIC VALUES(2,0,1,'BytesOut','Rate of Data Transmitted(eth3)','GAUGE')
INSERT INTO STATISTIC VALUES(3,0,2,'sysUsed','Used space on root volume','GAUGE')
INSERT INTO STATISTIC VALUES(4,0,2,'repoFree','Free space on repository volume','GAUGE')
INSERT INTO STATISTIC VALUES(5,0,2,'repoUsed','Diskspace Used by the Repositories','GAUGE')

change to:

INSERT INTO STATISTIC VALUES(1,0,'Rate of Data Received (eth3)','BytesIn','GAUGE',1)
INSERT INTO STATISTIC VALUES(2,0,'Rate of Data Transmitted(eth3)','BytesOut','GAUGE',1)
INSERT INTO STATISTIC VALUES(3,0,'Used space on root volume','sysUsed','GAUGE',2)
INSERT INTO STATISTIC VALUES(4,0,'Free space on repository volume','repoFree','GAUGE',2)
INSERT INTO STATISTIC VALUES(5,0,'Diskspace Used by the Repositories','repoUsed','GAUGE',2)

---

CREATE MEMORY TABLE PUBLIC.INTERVAL(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY, VERSION BIGINT NOT NULL,NAME VARCHAR(255) NOT NULL,SECONDS BIGINT NOT NULL)

change to:

CREATE MEMORY TABLE PUBLIC.INTERVAL(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,SECONDS BIGINT NOT NULL,NAME VARCHAR(255)  NOT NULL)

---

INSERT INTO INTERVAL VALUES(1,0,'Five Minutes',300)
INSERT INTO INTERVAL VALUES(2,0,'One Hour',3600)
INSERT INTO INTERVAL VALUES(3,0,'One Day',86400)
INSERT INTO INTERVAL VALUES(4,0,'One Week',604800)
INSERT INTO INTERVAL VALUES(5,0,'30 Days',2592000)

change to:

INSERT INTO INTERVAL VALUES(1,0,300,'Five Minutes')
INSERT INTO INTERVAL VALUES(2,0,3600,'One Hour')
INSERT INTO INTERVAL VALUES(3,0,86400,'One Day')
INSERT INTO INTERVAL VALUES(4,0,604800,'One Week')
INSERT INTO INTERVAL VALUES(5,0,2592000,'30 Days')

---

CREATE MEMORY TABLE PUBLIC.UNIT(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,FORMATTER VARCHAR(255),MAX_VALUE INTEGER,MIN_VALUE INTEGER,NAME VARCHAR(255) NOT NULL,UNIQUE(NAME))

change to:

CREATE MEMORY TABLE UNIT(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,FORMATTER VARCHAR(255),NAME VARCHAR(255) NOT NULL,MIN_VALUE INTEGER,MAX_VALUE INTEGER,CONSTRAINT CTUQ_UNIT_NAME UNIQUE(NAME))

---

INSERT INTO UNIT VALUES(1,0,NULL,NULL,0,'Bytes per Second')
INSERT INTO UNIT VALUES(2,0,NULL,NULL,0,'Bytes')

change to:

INSERT INTO UNIT VALUES(1,0,NULL,'Bytes per Second',0,NULL)
INSERT INTO UNIT VALUES(2,0,NULL,'Bytes',0,NULL)

---

CREATE MEMORY TABLE PUBLIC.STAT_GROUP(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,CATEGORY_ID BIGINT NOT NULL,IS_REPLICA BOOLEAN NOT NULL,NAME VARCHAR(255) NOT NULL,TITLE VARCHAR(255) NOT NULL,UNIT_ID BIGINT NOT NULL,UNIQUE(NAME),CONSTRAINT FKE8A250D42B447BEC FOREIGN KEY(CATEGORY_ID) REFERENCES PUBLIC.CATEGORY(ID))

change to:

CREATE MEMORY TABLE STAT_GROUP(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,IS_REPLICA BOOLEAN NOT NULL,UNIT_ID BIGINT NOT NULL,CATEGORY_ID BIGINT NOT NULL,TITLE VARCHAR(255) NOT NULL,NAME VARCHAR(255) NOT NULL,CONSTRAINT CTUQ_SG_NAME UNIQUE(NAME),CONSTRAINT FK_SG_CATEGORY_ID FOREIGN KEY(CATEGORY_ID) REFERENCES CATEGORY(ID))

---

INSERT INTO STAT_GROUP VALUES(1,1,1,FALSE,'NetworkThroughput','Network Throughput (eth3)',1)
INSERT INTO STAT_GROUP VALUES(2,1,2,FALSE,'FileSystem','Disk space on the Filesystem',2)

change to:

INSERT INTO STAT_GROUP VALUES(1,1,FALSE,1,1,'Network Throughput (eth3)','NetworkThroughput')
INSERT INTO STAT_GROUP VALUES(2,1,FALSE,2,2,'Disk space on the Filesystem','FileSystem')

---

CREATE MEMORY TABLE PUBLIC.STAT_ACTION(ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,COLLECT_ID BIGINT NOT NULL,CONSOLIDATE_SOURCE_ID BIGINT,DELETE_ID BIGINT,GROUP_ID BIGINT, CONSTRAINT FK20996E81D9377FE6 FOREIGN KEY(DELETE_ID) REFERENCES PUBLIC.INTERVAL(ID),CONSTRAINT FK20996E81D02162A5 FOREIGN KEY(CONSOLIDATE_SOURCE_ID) REFERENCES PUBLIC.STAT_ACTION(ID),CONSTRAINT FK20996E8186CE8007 FOREIGN KEY(COLLECT_ID) REFERENCES PUBLIC.INTERVAL(ID))

change to:

CREATE MEMORY TABLE STAT_ACTION(ID BIGINT GENERATED BY DEFAULT  AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,VERSION BIGINT NOT NULL,DELETE_ID BIGINT,GROUP_ID BIGINT,CONSOLIDATE_SOURCE_ID BIGINT,COLLECT_ID BIGINT NOT NULL,CONSTRAINT FK_SA_DELETE_ID FOREIGN KEY(DELETE_ID) REFERENCES INTERVAL(ID),CONSTRAINT FK_SA_SOURCE_ID FOREIGN KEY(CONSOLIDATE_SOURCE_ID) REFERENCES STAT_ACTION(ID),CONSTRAINT FK_SA_COLLECT_ID FOREIGN KEY(COLLECT_ID) REFERENCES INTERVAL(ID))

---

INSERT INTO STAT_ACTION VALUES(1,0,1,NULL,3,1)
INSERT INTO STAT_ACTION VALUES(2,0,2,1,4,1)
INSERT INTO STAT_ACTION VALUES(3,0,3,2,5,1)
INSERT INTO STAT_ACTION VALUES(4,0,1,NULL,3,2)
INSERT INTO STAT_ACTION VALUES(5,0,2,4,4,2)
INSERT INTO STAT_ACTION VALUES(6,0,3,5,5,2)

change to:

INSERT INTO STAT_ACTION VALUES(1,0,3,1,NULL,1)
INSERT INTO STAT_ACTION VALUES(2,0,4,1,1,2)
INSERT INTO STAT_ACTION VALUES(3,0,5,1,2,3)
INSERT INTO STAT_ACTION VALUES(4,0,3,2,NULL,1)
INSERT INTO STAT_ACTION VALUES(5,0,4,2,4,2)
INSERT INTO STAT_ACTION VALUES(6,0,5,2,5,3)