Problem solved!
The root cause is because seafevents
is using sqlite as database instead of mysql.
I am not sure why this happens, since my other databases, e.g., ccnet
, seafile
, seahub
, are all using mysql.
I found the new installation of pro version will put the seafevents
related tables into seahub
database.
Thus, I think this must be some issue during the historical upgrades, since I started using seafile since version 5.0, from CE version later to Pro.
So, to solve this problem, my main idea is to migrate all data in sqlite (under /pro-data/seafevents.db) to the mysql (database seahub-db
).
a. Create the mysql tables in seahub-db
CREATE TABLE `Event` (
`uuid` varchar(36) NOT NULL,
`etype` varchar(128) NOT NULL,
`timestamp` datetime NOT NULL,
`detail` text NOT NULL,
PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `FileAudit` (
`eid` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL,
`etype` varchar(128) NOT NULL,
`user` varchar(255) NOT NULL,
`ip` varchar(45) NOT NULL,
`device` text NOT NULL,
`org_id` int(11) NOT NULL,
`repo_id` varchar(36) NOT NULL,
`file_path` text NOT NULL,
PRIMARY KEY (`eid`),
KEY `idx_file_audit_orgid_eid` (`org_id`,`eid`),
KEY `idx_file_audit_repo_org_eid` (`repo_id`,`org_id`,`eid`),
KEY `idx_file_audit_user_orgid_eid` (`user`,`org_id`,`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `FileUpdate` (
`eid` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL,
`user` varchar(255) NOT NULL,
`org_id` int(11) NOT NULL,
`repo_id` varchar(36) NOT NULL,
`commit_id` varchar(40) NOT NULL,
`file_oper` text NOT NULL,
PRIMARY KEY (`eid`),
KEY `idx_file_update_repo_org_eid` (`repo_id`,`org_id`,`eid`),
KEY `idx_file_update_orgid_eid` (`org_id`,`eid`),
KEY `idx_file_update_user_orgid_eid` (`user`,`org_id`,`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `PermAudit` (
`eid` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL,
`etype` varchar(128) NOT NULL,
`from_user` varchar(255) NOT NULL,
`to` varchar(255) NOT NULL,
`org_id` int(11) NOT NULL,
`repo_id` varchar(36) NOT NULL,
`file_path` text NOT NULL,
`permission` varchar(15) NOT NULL,
PRIMARY KEY (`eid`),
KEY `idx_perm_audit_repo_org_eid` (`repo_id`,`org_id`,`eid`),
KEY `idx_perm_audit_orgid_eid` (`org_id`,`eid`),
KEY `idx_perm_audit_user_orgid_eid` (`from_user`,`org_id`,`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `UserEvent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_id` int(11) DEFAULT NULL,
`username` varchar(255) NOT NULL,
`eid` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_UserEvent_username` (`username`),
KEY `ix_UserEvent_eid` (`eid`),
CONSTRAINT `UserEvent_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `Event` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `UserTrafficStat` (
`email` varchar(255) NOT NULL,
`month` varchar(6) NOT NULL,
`block_download` bigint(20) NOT NULL,
`file_view` bigint(20) NOT NULL,
`file_download` bigint(20) NOT NULL,
`dir_download` bigint(20) NOT NULL,
PRIMARY KEY (`email`,`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `VirusFile` (
`vid` int(11) NOT NULL AUTO_INCREMENT,
`repo_id` varchar(36) NOT NULL,
`commit_id` varchar(40) NOT NULL,
`file_path` text NOT NULL,
`has_handle` tinyint(1) NOT NULL,
PRIMARY KEY (`vid`),
KEY `ix_VirusFile_repo_id` (`repo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `VirusScanRecord` (
`repo_id` varchar(36) NOT NULL,
`scan_commit_id` varchar(40) NOT NULL,
PRIMARY KEY (`repo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
b. Dump seafevents.db
into SQL data
#!/bin/bash
Sqlite2MysqlData () {
echo "SET FOREIGN_KEY_CHECKS=0;"
for TABLE in $(sqlite3 $1 .tables)
do
#get columns and insert ``, around every one
COLS=$(sqlite3 $1 "PRAGMA table_info('${TABLE}');" | cut -d'|' -f2 | sed 's/^/`/' | sed 's/$/`, /' )
#remove comma from last one
COLS_PURE=$(echo $COLS | sed 's/.$//')
#generate insertstatemets (via echoMultipleCommands support by sqlite3), does not include column names
echo -e ".mode insert\nselect * from '${TABLE}';" | sqlite3 $1 |
#replace 3rd word with columns from above: TableName(`col1`, `col2`, `col3`, ...)
sed "s/[^ ]*[^ ]/${TABLE}(${COLS_PURE})/3"
done
echo "SET FOREIGN_KEY_CHECKS=1;"
}
Sqlite2MysqlData seafevents.db >> seafevents.sql
c. Import data into mysql
mysql> use seahub_db;
mysql> source seafevents.sql;
d. Modify seafevents.conf
configure file.
[DATABASE]
#type = sqlite3
#path = /home/seafile/seafile/pro-data/seafevents.db
type = mysql
host = 127.0.0.1
username = root
password = [db password]
name=seahub_db
Afterwards, I can successfully upgrade to 6.2.2.