OK, everything working now.
The name of the realm in the database deviated from the name in the
filesystem. That's why I didn't see existing certificates.
Thus the SQLs used for upgrading the schema seem to be correct. Only
migrating the Sequences was needed an additional "+1" for hitting the
correct number like so:
% 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)+1, " INCREMENT BY 1 MINVALUE
0 NO MAXVALUE CACHE 1;") from {}' >migration_ddls_part2.sql
Would it make sense to detail the migration path a little more in
https://raw.githubusercontent.com/openxpki/openxpki-config/community/UPGRADEv3.md?
I could open a Pull Request, if that makes sense.
Regards,
Dirk
Am 31.08.21 um 15:12 schrieb Dirk Heuvels:
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 {}'
Mit freundlichen Grüßen,
Dirk Heuvels
--
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
[email protected] · http://www.aiticon.com
Geschäftsführer: Matthias Herlitzius
Amtsgericht Frankfurt am Main · HRB 79310
USt.-ID-Nr.: DE 218319776
_______________________________________________
OpenXPKI-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openxpki-users
