Hi all,
I'm trying to upgrade OpenXPKI from 2.0.3 to 3.12 (both installations
with the the Debian packages at openxpki.org).
Regarding schema upgrade the documentation says to look at the upgrade
document in the openxpki-config repository. I suppose this is
https://raw.githubusercontent.com/openxpki/openxpki-config/community/UPGRADEv3.md
The document mentions a few new columns, which I have added or altered.
After that I was still missing the following tables, for which I picked
the necessary DDLs from the schema-mariadb.sql.gz file in the examples
folder.
- backend_session
- frontend_session
- users
What confuses me, is the fact that OpenXPKI is using different queries
depending on the value of (database.yaml).main.type.
With "type: MySQL" (the value I was using on 2.x) openxpkid comes up and
I can log in (although certificate search is still behaving oddly).
With "type: MariaDB" openxpkid doesn't come up, because it trying to
access the sequence seq_audittrail, which is still a table in my schema.
I suppose those `seq_xxx` tables with the `seq_number` and the `dummy`
column were some kind of workaround for old MySQL versions. I have
dropped those tables and recreated them as sequences with the a
startvalue of the old `seq_xxx.seq_number`.
However I'm not sure, if I need to switch the database type to "MariaDB"
after all and if I'm still on track, because it was a lot of trial and
error.
Does it sound reasonable what I did so far?
Thanks in advance,
Dirk
P.S.:
DDLs run so far:
ALTER TABLE application_log MODIFY COLUMN logtimestamp decimal(20,5);
ALTER TABLE crl ADD COLUMN IF NOT EXISTS (`profile` varchar(64) DEFAULT
NULL);
ALTER TABLE datapool ADD COLUMN IF NOT EXISTS (`access_key` VARCHAR(255)
NULL DEFAULT NULL);
ALTER TABLE workflow ADD COLUMN IF NOT EXISTS (`workflow_archive_at`
int(10) unsigned DEFAULT NULL);
ALTER TABLE crl ADD COLUMN IF NOT EXISTS (`max_revocation_id` INT NULL
DEFAULT NULL);
ALTER TABLE certificate ADD COLUMN IF NOT EXISTS (`revocation_id` INT
NULL DEFAULT NULL);
CREATE TABLE IF NOT EXISTS `backend_session` (
`session_id` varchar(255) NOT NULL,
`data` longtext,
`created` int(10) unsigned NOT NULL,
`modified` int(10) unsigned NOT NULL,
`ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `backend_session`
ADD PRIMARY KEY (`session_id`),
ADD INDEX(`modified`);
CREATE TABLE IF NOT EXISTS `frontend_session` (
`session_id` varchar(255) NOT NULL,
`data` longtext,
`created` int(10) unsigned NOT NULL,
`modified` int(10) unsigned NOT NULL,
`ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `frontend_session`
ADD PRIMARY KEY (`session_id`),
ADD INDEX(`modified`);
CREATE TABLE IF NOT EXISTS `users` (
`username` varchar(255) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`pki_realm` varchar(255) DEFAULT NULL,
`mail` varchar(255) NOT NULL,
`realname` varchar(255) DEFAULT NULL,
`role` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Generated DDLs for creating real sequences:
% sudo mysql openxpki -sNe "show tables" |grep "^seq_" |sudo xargs -n1
-I{} mysql openxpki -sNe 'select concat("DROP TABLE {}; CREATE SEQUENCE
{} START WITH ", ifnull(max(seq_number),0), " INCREMENT BY 1 MINVALUE 0
NO MAXVALUE CACHE 1;") from {}'
--
aiticon GmbH
Dirk Heuvels
Stephanstraße 1
60313 Frankfurt am Main
t. +49 69 795 83 83-0
f. +49 69 795 83 83-28
dirk.heuv...@aiticon.com · http://www.aiticon.com
Geschäftsführer: Matthias Herlitzius
Amtsgericht Frankfurt am Main · HRB 79310
USt.-ID-Nr.: DE 218319776
_______________________________________________
OpenXPKI-users mailing list
OpenXPKI-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openxpki-users
