Re: [OpenXPKI-users] Upgrade from 2.x to 3.12

2021-09-08 Thread Martin Bartosch via OpenXPKI-users 
Hi,

> 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.

Glad to hear you got it working. PRs to improve the documentation are always 
appreciated.

Best regards,

Martin



___
OpenXPKI-users mailing list
OpenXPKI-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openxpki-users

Re: [OpenXPKI-users] Upgrade from 2.x to 3.12

2021-09-08 Thread Dirk Heuvels 

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
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

[OpenXPKI-users] Upgrade from 2.x to 3.12

2021-08-31 Thread 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 {}'


--

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