$ ./upgrade/upgrade_6.1_6.2.sh
-------------------------------------------------------------
This script would upgrade your seafile server from 6.1 to 6.2
Press [ENTER] to contiune
-------------------------------------------------------------
Updating seafile/seahub database ...
[INFO] You are using MySQL
[INFO] updating ccnet database...
[INFO] updating seahub database...
[ERROR] Failed to execute sql: (1146, "Table 'seahub-db.FileAudit' doesn't exist")
Failed to upgrade your database
I grep the 6.2.2 for FileAudit, and found no where creating this table.
seafile-pro-server-6.2.2$ grep -rnw ./ -e FileAudit
./upgrade/sql/6.2.0/mysql/seahub.sql:85:ALTER TABLE `FileAudit` ADD INDEX `fileaudit_timestamp` (`timestamp`);
./seahub/seahub/urls.py:60:from seahub.api2.endpoints.admin.file_audit import FileAudit
./seahub/seahub/urls.py:496: url(r'^api/v2.1/admin/logs/file-audit/$', FileAudit.as_view(), name='api-v2.1-admin-logs-file-audit'),
./seahub/seahub/api2/endpoints/admin/file_audit.py:28:class FileAudit(APIView):
./pro/python/seafevents/events/db.py:7:from .models import Event, UserEvent, FileAudit, FileUpdate, PermAudit
./pro/python/seafevents/events/db.py:147: return get_events(session, FileAudit, user, org_id, repo_id, None, start, limit)
./pro/python/seafevents/events/db.py:150: return get_events(session, FileAudit, user, org_id, repo_id, file_path, start, limit)
./pro/python/seafevents/events/db.py:157: file_audit = FileAudit(timestamp, etype, user, ip, device, org_id, \
./pro/python/seafevents/events/db.py:187: obj = FileAudit
./pro/python/seafevents/events/models.py:57:class FileAudit(Base):
./pro/python/seafevents/events/models.py:58: __tablename__ = 'FileAudit'
./pro/python/seafevents/events/models.py:89: return "FileAudit<EventType = %s, User = %s, IP = %s, Device = %s, \
./pro/python/seafevents/events/models.py:94: return "FileAudit<EventType = %s, User = %s, IP = %s, Device = %s, \
./pro/python/seafevents/statistic/file_ops.py:10:from seafevents.events.models import FileAudit
./pro/python/seafevents/statistic/file_ops.py:53: q = self.edb_session.query(func.count(FileAudit.eid)).filter(
./pro/python/seafevents/statistic/file_ops.py:54: FileAudit.timestamp.between(
I looked at the upgrade/sql/6.2.0/mysql/seahub.sql
The error throws from here:
ALTER TABLE `FileAudit` ADD INDEX `fileaudit_timestamp` (`timestamp`);
ALTER TABLE `Event` ADD INDEX `event_timestamp` (`timestamp`);
ALTER TABLE `UserTrafficStat` ADD INDEX `usertrafficstat_timestamp` (`month`);
ALTER TABLE `FileUpdate` ADD INDEX `fileupdate_timestamp` (`timestamp`);
However, it is strange that I also don’t have tables Event, UserTrafficStat, FileUpdate in my seahub database.
Here is the list of tables in my seahub database
admin_log_adminlog
api2_token
api2_tokenv2
avatar_avatar
avatar_groupavatar
base_clientlogintoken
base_commandslastcheck
base_devicetoken
base_filecomment
base_filediscuss
base_groupenabledmodule
base_innerpubmsg
base_innerpubmsgreply
base_userenabledmodule
base_userlastlogin
base_userstarredfiles
base_uuidobjidmap
captcha_captchastore
constance_config
contacts_contact
django_content_type
django_session
group_groupmessage
group_messageattachment
group_messagereply
group_publicgroup
institutions_institution
institutions_institutionadmin
institutions_institutionquota
invitations_invitation
message_usermessage
message_usermsgattachment
message_usermsglastcheck
notifications_notification
notifications_usernotification
options_useroptions
organizations_orgmemberquota
post_office_attachment
post_office_attachment_emails
post_office_email
post_office_emailtemplate
post_office_log
profile_detailedprofile
profile_profile
pubfile_grouppublicfile
registration_registrationprofile
share_anonymousshare
share_fileshare
share_orgfileshare
share_privatefiledirshare
share_uploadlinkshare
sysadmin_extra_userloginlog
termsandconditions_termsandconditions
termsandconditions_usertermsandconditions
two_factor_phonedevice
two_factor_staticdevice
two_factor_statictoken
two_factor_totpdevice
wiki_groupwiki
wiki_personalwiki
The missing tables all have capitalized characters – not quite consistent with existing tables’ naming style.
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.
Hi
Your tutorial is great, thank you…
I just want to add additional information.
I updated my seafile from version 6.0 to 6.2.
When I run upgrade/upgrade_6.0_6.1.sh, it could be finished perfectly.
After that, of course, I run upgrade/upgrade_6.1_6.2.sh which is fail with message FileAudit table not found.
I applied all of your steps successfully and tried to run upgrade/upgrade_6.1_6.2.sh again, unfortunatelly it fail
[root@data seafile-pro-server-6.2.9]# bash upgrade/upgrade_6.1_6.2.sh
-------------------------------------------------------------
This script would upgrade your seafile server from 6.1 to 6.2
Press [ENTER] to contiune
-------------------------------------------------------------
Updating seafile/seahub database ...
[INFO] You are using MySQL
[INFO] updating ccnet database...
[ERROR] Failed to execute sql: Duplicate column name 'reference_id'
Failed to upgrade your database
I search where is reference_id
[root@data seafile-server-latest]# grep -r reference_id upgrade/sql/
upgrade/sql/6.2.0/mysql/ccnet.sql:-- alter table LDAPUsers add column reference_id VARCHAR(255);
upgrade/sql/6.2.0/mysql/ccnet.sql:-- alter table EmailUser add column reference_id VARCHAR(255);
upgrade/sql/6.2.0/mysql/ccnet.sql:-- ALTER TABLE `LDAPUsers` ADD UNIQUE (`reference_id`);
upgrade/sql/6.2.0/mysql/ccnet.sql:-- ALTER TABLE `EmailUser` ADD UNIQUE (`reference_id`);
upgrade/sql/6.2.0/sqlite3/ccnet/usermgr.sql:alter table LDAPUsers add column reference_id VARCHAR(255);
upgrade/sql/6.2.0/sqlite3/ccnet/usermgr.sql:alter table EmailUser add column reference_id VARCHAR(255);
upgrade/sql/6.2.0/sqlite3/ccnet/usermgr.sql:CREATE UNIQUE INDEX IF NOT EXISTS reference_id_index on EmailUser (reference_id);
upgrade/sql/6.2.0/sqlite3/ccnet/usermgr.sql:CREATE UNIQUE INDEX IF NOT EXISTS ldapusers_reference_id_index on LDAPUsers(reference_id);
I looked at my current database with command show create table LDAPUsers;
| LDAPUsers | CREATE TABLE `LDAPUsers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`is_staff` tinyint(1) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`extra_attrs` text DEFAULT NULL,
`reference_id` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `reference_id` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
I see that column reference_id already exists.
We can conclude that we cannot run update script many times with same result.
If we trace sql query for the update process, you can find that all sql script successfully applied except for these
ALTER TABLE `FileAudit` ADD INDEX `fileaudit_timestamp` (`timestamp`);
ALTER TABLE `Event` ADD INDEX `event_timestamp` (`timestamp`);
ALTER TABLE `UserTrafficStat` ADD INDEX `usertrafficstat_timestamp` (`month`);
ALTER TABLE `FileUpdate` ADD INDEX `fileupdate_timestamp` (`timestamp`);
So, to run 6.1 to 6.2 successfully, you can comment out sql script that was already applied to database
upgrade/sql/6.2.0/mysql/ccnet.sql
-- alter table LDAPUsers add column reference_id VARCHAR(255);
-- alter table EmailUser add column reference_id VARCHAR(255);
-- ALTER TABLE `LDAPUsers` ADD UNIQUE (`reference_id`);
-- ALTER TABLE `EmailUser` ADD UNIQUE (`reference_id`);
upgrade/sql/6.2.0/mysql/seahub.sql
-- ALTER TABLE `sysadmin_extra_userloginlog` ADD COLUMN `login_success` tinyint(1) NOT NULL default 1;
-- ALTER TABLE `profile_profile` ADD COLUMN `list_in_address_book` tinyint(1) NOT NULL default 0;
-- ALTER TABLE `profile_profile` ADD INDEX `profile_profile_3d5d3631` (`list_in_address_book`);
If you are not sure what script has been applied to database, you can comment out the sql script one by one and re run upgrade/upgrade_6.1_6.2.sh until success.
Thanks for pointing out this. I omitted it because I always make a backup of the database before any upgrade. When it fails, I will roll back, modify the script, and do the upgrade again. So I can skip your problem. 
I think this is better and cleaner approach. I did my way because I don’t want to reimport seafevent again 
Hi,
I did not have any problem at all but i noticed that :
I don’t know how it happened but then, gracefully to you, the two servers are using mysql for event logging.
thank you