Ciphermail and MariaDB

HI,

one short question:

Are thzere any plans 'bout supporting MariaDB? At one installation this
is a "must have"/"killing frequest". Postgres isn't allowed. :confused:

I think that's all for now, maybe much mor questions come next day. :wink:

ttyl
Django

路路路

--
"Bonnie & Clyde der Postmaster-Szene!" approved by Postfix-God
http://wetterstation-pliening.info
http://dokuwiki.nausch.org
http://wiki.piratenpartei.de/Benutzer:Django

Hi Django,

Interesting. Why is Postgres not allowed? I would assume that it would
be the other way around since MariaDB is based on code owned by Oracle
and Postgres is "more" open source. Anyway, perhaps it's not of legal
reasons but some technical reason. That said, Maria DB is not directly
supported but you can see whether you can make it work with Maria DB.
The database layer uses Hibernate (which is an ORM library). Hibernate
supports multiple databases and in theory you might tell it to use a
different database. This however has never tested but it you are willing
to spend some time testing it, I can assist you.

Kind regards,

Martijn Brinkers

路路路

On 06/29/2015 04:17 PM, Django [BOfH] wrote:

one short question:

Are thzere any plans 'bout supporting MariaDB? At one installation this
is a "must have"/"killing frequest". Postgres isn't allowed. :confused:

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

I too would like to try running on MySQL. Just converted my DB from PSQL to MySQL, and will try the actual Hibernate connection later on tomorrow or the next day.

My interest is adding CipherMail to the cluster of MySQL, Dovecot and Postfix servers. Currently, any mail we want to send through CipherMail must travel to a specific server in one site, versus going out (or in) through any site.

MySQL is so much easier to have a reliable master-master configuration. I spent the day trying to get a similar functionality through PostgreSQL, but I really did hit a lot of roadblocks.

I believe MariaDB and MySQL share the same connection libraries, yes? I can share my experiences here.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jun 29, 2015, at 7:31 AM, Martijn Brinkers <martijn(a)djigzo.com> wrote:

On 06/29/2015 04:17 PM, Django [BOfH] wrote:

one short question:

Are thzere any plans 'bout supporting MariaDB? At one installation this
is a "must have"/"killing frequest". Postgres isn't allowed. :confused:

Hi Django,

Interesting. Why is Postgres not allowed? I would assume that it would
be the other way around since MariaDB is based on code owned by Oracle
and Postgres is "more" open source. Anyway, perhaps it's not of legal
reasons but some technical reason. That said, Maria DB is not directly
supported but you can see whether you can make it work with Maria DB.
The database layer uses Hibernate (which is an ORM library). Hibernate
supports multiple databases and in theory you might tell it to use a
different database. This however has never tested but it you are willing
to spend some time testing it, I can assist you.

Kind regards,

Martijn Brinkers

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

Hello!

Currently, any mail we want to send through CipherMail must travel to a
specific server in one site, versus going out (or in) through any site.

Incomming emails to different sites are routed with transport_maps's
hashtable:

domain_1 lmtp:backend_domain_1:24
doamin_2 lmtps:backend_domain_2:24
domain_3 smtp:[10.10.10.10]:25

I can share my experiences here.

Grait, please do it! :wink:

Best regards
Django

路路路

Am 30.06.2015 um 05:03 schrieb Laz C. Peterson:
--
"Bonnie & Clyde der Postmaster-Szene!" approved by Postfix-God
http://wetterstation-pliening.info
http://dokuwiki.nausch.org
http://wiki.piratenpartei.de/Benutzer:Django

Griasde Martijn!

Quoting "Martijn Brinkers" martijn(a)djigzo.com:

Interesting. Why is Postgres not allowed?

It's a political decision - we can use following prioroties ans databeses:

聽聽1st Oracle 12
聽聽2nd MariaDB

Anyway, perhaps it's not of legal reasons but some technical reason.

FULLACK

That said, Maria DB is not directly supported but you can see
whether you can make it work with Maria DB.

That's a very good idea If you can tell me technical details about
changing the backend database, I'll do it, test it and give feedback
to you!

The database layer uses Hibernate (which is an ORM library).

My colleague Klaus, my database specialist, assumed, that your are using
Hibernate.

This however has never tested but it you are willing to spend some
time testing it, I can assist you.

Oh yes, I'll make this tests! So please tell me, what I should do!

Thanks for help!!!

Best regards
Django

路路路

--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

Hello!

Quoting "lst_hoe02 at kwsoft.de" lst_hoe02(a)kwsoft.de:

Maybe they will sponsor the work?

At first point I evaluate, either ciphermail or Z1 will met our
requirements. And I think ciphermail is very good in the race! :wink:
And yes, we will pay4use like a support contract or an licence cost.
But first we have to overcome the first hurdle and actually this is
MariaDB support.

Have a nice day!
Django

路路路

--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

Hello there Django.

Yes that is how we are doing it right now, using transport_map. But our goal is to go independent if one site goes down 鈥 both for high availability and live backup. Would love to replicate the CipherMail database same as we are replicating our mailbox storage and other services.

I tested out converting the PostgreSQL database to MySQL, but found some issues already. Seems that some of the tables (certificates for example) are given a very large size in PostgreSQL, where as in MySQL they are capped at 255. I don鈥檛 know about the restrictions in MariaDB, but I was unable to copy the data from the certificates table.

Other than that, it seems to be a very possible option as Hibernate supports MySQL just the same.

Any thoughts about the data type mismatch?

~ Laz Peterson
Paravis, LLC

路路路

On Jun 30, 2015, at 12:18 AM, Django [BOfH] <django(a)nausch.org> wrote:

Hello!

Am 30.06.2015 um 05:03 schrieb Laz C. Peterson:

Currently, any mail we want to send through CipherMail must travel to a
specific server in one site, versus going out (or in) through any site.

Incomming emails to different sites are routed with transport_maps's
hashtable:

domain_1 lmtp:backend_domain_1:24
doamin_2 lmtps:backend_domain_2:24
domain_3 smtp:[10.10.10.10]:25

I can share my experiences here.

Grait, please do it! :wink:

Best regards
Django
--
"Bonnie & Clyde der Postmaster-Szene!" approved by Postfix-God
http://wetterstation-pliening.info
http://dokuwiki.nausch.org
http://wiki.piratenpartei.de/Benutzer:Django
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

HI Laz!

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I tested out converting the PostgreSQL database to MySQL, but found
some issues already. Seems that some of the tables (certificates
for example) are given a very large size in PostgreSQL, where as in
MySQL they are capped at 255.

really?

CREATE TABLE certificates (
聽聽聽聽聽id bigint NOT NULL,
聽聽聽聽聽notbefore timestamp without time zone,
聽聽聽聽聽notafter timestamp without time zone,
聽聽聽聽聽issuer text,
聽聽聽聽聽issuerfriendly text,
聽聽聽聽聽serial character varying(1024),
聽聽聽聽聽subjectkeyidentifier text,
聽聽聽聽聽subject text,
聽聽聽聽聽subjectfriendly text,
聽聽聽聽聽certificate bytea,
聽聽聽聽聽thumbprint character varying(255),
聽聽聽聽聽certpath bytea,
聽聽聽聽聽certpathtype character varying(255),
聽聽聽聽聽creationdate timestamp without time zone,
聽聽聽聽聽datepathupdated timestamp without time zone,
聽聽聽聽聽keyalias character varying(1024),
聽聽聽聽聽storename character varying(255)
);

How did you made your database dump from your postgresql?

ttyl
Django

路路路

--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

Hello Laz, hello Martijn,

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I believe MariaDB and MySQL share the same connection libraries,
yes? I can share my experiences here.

May someone show me details, how I can set up Ciphermail/Hibernating
using a MariaDB?

Thanx!

ttyl
Django

路路路

--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

Yes, I have same information in the database dump.

I was actually using MySQL Workbench to do the conversion, and my issue was using the wrong ODBC driver (I was using ANSI, should have been using Unicode). After switching that, all is good, data has been moved over.

Regarding the Hibernate config, let me play around with this today and I will get back to you later on. But the switch should be simple, as long as you have the correct ODBC driver in the /usr/share/djigzo/lib folder (that is where mine are on Ubuntu 14.04 for CipherMail) then you can just change the /usr/share/djigzo/conf/hibernate.cfg.xml to reflect these changes. Those folder paths and files are where I have CipherMail installed, your installation may be different.

You can get the MySQL JDBC driver from http://dev.mysql.com/downloads/connector/j/ as well.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jun 30, 2015, at 6:54 AM, django(a)nausch.org wrote:

HI Laz!

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I tested out converting the PostgreSQL database to MySQL, but found some issues already. Seems that some of the tables (certificates for example) are given a very large size in PostgreSQL, where as in MySQL they are capped at 255.

really?

CREATE TABLE certificates (
聽聽聽id bigint NOT NULL,
聽聽聽notbefore timestamp without time zone,
聽聽聽notafter timestamp without time zone,
聽聽聽issuer text,
聽聽聽issuerfriendly text,
聽聽聽serial character varying(1024),
聽聽聽subjectkeyidentifier text,
聽聽聽subject text,
聽聽聽subjectfriendly text,
聽聽聽certificate bytea,
聽聽聽thumbprint character varying(255),
聽聽聽certpath bytea,
聽聽聽certpathtype character varying(255),
聽聽聽creationdate timestamp without time zone,
聽聽聽datepathupdated timestamp without time zone,
聽聽聽keyalias character varying(1024),
聽聽聽storename character varying(255)
);

How did you made your database dump from your postgresql?

ttyl
Django
--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

In theory, you should just be able to drop that MySQL JDBC driver inside /usr/share/djigzo/lib/lib.d/ and then change the hibernate.cfg.xml to reflect these options:

<hibernate-configuration>
聽聽聽聽<session-factory>
<!-- <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:postgresql://127.0.0.1/djigzo/</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>
-->
聽聽聽聽聽聽聽聽<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/djigzo</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>

I have not tried this yet, so I don鈥檛 know how well or if it even works.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jun 30, 2015, at 7:08 AM, django(a)nausch.org wrote:

Hello Laz, hello Martijn,

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I believe MariaDB and MySQL share the same connection libraries, yes? I can share my experiences here.

May someone show me details, how I can set up Ciphermail/Hibernating using a MariaDB?

Thanx!

ttyl
Django
--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

I just tried this and unfortunately it is not so straight forward.

The driver loads but then it is unable to access the tables. Seems like the problem is due to case sensitivity 鈥 CipherMail is looking for the 鈥淎dmin鈥 table, where I have 鈥渁dmin鈥 鈥 I will look into this more later on.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jun 30, 2015, at 8:02 AM, Laz C. Peterson <laz(a)paravis.net> wrote:

In theory, you should just be able to drop that MySQL JDBC driver inside /usr/share/djigzo/lib/lib.d/ and then change the hibernate.cfg.xml to reflect these options:

<hibernate-configuration>
聽聽聽<session-factory>
<!-- <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:postgresql://127.0.0.1/djigzo/</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>
-->
聽聽聽聽聽聽聽<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/djigzo</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>

I have not tried this yet, so I don鈥檛 know how well or if it even works.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

On Jun 30, 2015, at 7:08 AM, django(a)nausch.org wrote:

Hello Laz, hello Martijn,

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I believe MariaDB and MySQL share the same connection libraries, yes? I can share my experiences here.

May someone show me details, how I can set up Ciphermail/Hibernating using a MariaDB?

Thanx!

ttyl
Django
--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

Possibly Martijn can shed some light on this 鈥

I have renamed the tables so that they match the same case as the original PostgreSQL tables. Attached is the error log at that point, seems to have something to do with the XML configuration files, but I am not too familiar with Java so I don鈥檛 exactly know what to be looking for.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jun 30, 2015, at 8:14 AM, Laz C. Peterson <laz(a)paravis.net> wrote:

I just tried this and unfortunately it is not so straight forward.

The driver loads but then it is unable to access the tables. Seems like the problem is due to case sensitivity 鈥 CipherMail is looking for the 鈥淎dmin鈥 table, where I have 鈥渁dmin鈥 鈥 I will look into this more later on.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

On Jun 30, 2015, at 8:02 AM, Laz C. Peterson <laz(a)paravis.net> wrote:

In theory, you should just be able to drop that MySQL JDBC driver inside /usr/share/djigzo/lib/lib.d/ and then change the hibernate.cfg.xml to reflect these options:

<hibernate-configuration>
聽聽<session-factory>
<!-- <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
聽聽聽聽聽聽<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:postgresql://127.0.0.1/djigzo/</property>
聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>
-->
聽聽聽聽聽聽<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
聽聽聽聽聽聽<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
聽聽聽聽聽聽<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/djigzo</property>
聽聽聽聽聽聽<property name="hibernate.connection.username">djigzo</property>
聽聽聽聽聽聽<property name="hibernate.connection.password">djigzo</property>

I have not tried this yet, so I don鈥檛 know how well or if it even works.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

On Jun 30, 2015, at 7:08 AM, django(a)nausch.org wrote:

Hello Laz, hello Martijn,

Quoting "Laz C. Peterson" <laz(a)paravis.net>:

I believe MariaDB and MySQL share the same connection libraries, yes? I can share my experiences here.

May someone show me details, how I can set up Ciphermail/Hibernating using a MariaDB?

Thanx!

ttyl
Django
--
http://dokuwiki.nausch.org
http://wetterstation-pliening.info
http://ebersberger-liedersammlung.de

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

HI Laz!

Those folder paths and files are where I have CipherMail installed,
your installation may be different.

Fine! :wink: I've installed Ciphermail based on RPM, so the path are
exactly the same here. Tomorrow morning my colleague an me are working
on MariaDB support.

You can get the MySQL JDBC driver from
http://dev.mysql.com/downloads/connector/j/

Thanx for your usefull hints!

cul8r
Django

路路路

Am 30.06.2015 um 16:54 schrieb Laz C. Peterson:

Hi,

It turned out that adding support for MySQL (MariaDB) was more work than
just changing the Hibernate dialect. We had to make sure that the
required changes did not require existing users to update the database.
Modifying all the unit tests to support different databases was also
quite a bit of work. That said, now we have it working for MySQL, in
principle adding support for other database (if ever) is now relative easy.

We have uploaded an RPM which does not depend on Postgres and contains
the required configuration files for MySQL.

The RPMs can be downloaded from

Backend;

http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm

Instructions:

1. Install RPMs

Install the RPMs using the quick install guide
(https://www.ciphermail.com/documents/quick-install-guide.txt)

Note: skip the parts about postgres

2. Install and configure MySQL (MariaDB)

Install MySQL

yum -y install mariadb-server mariadb
systemctl start mariadb
systemctl enable mariadb

max_allowed_packet should be set to a high value (in mysql config)

$ vi /etc/my.cnf.d/server.cnf

Add the following line below [server]

max_allowed_packet = 128M

Note: the max size depends on the largest email, CRL you want to support.

systemctl restart mariadb

3. Create database user and database

mysql -u root

CREATE USER 'djigzo'@'localhost' IDENTIFIED BY 'djigzo';
CREATE DATABASE djigzo CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON djigzo.* TO 'djigzo'@'localhost';

Note 1: The database should be set to UTF8
Note 2: ALL permission should probably be changed to a lower privilege
but we did not yet check what the minimal required privileges are.

4. Import database scheme

$ mysql -u djigzo --password=djigzo djigzo <
/usr/share/djigzo/conf/database/sql/djigzo.mysql.sql

5. Tell CipherMail to use MySQL

$ vi /usr/share/djigzo/wrapper/djigzo.wrapper.conf

Uncomment the line:

wrapper.java.additional.15=-Dciphermail.hibernate.database.type=mysql

6. Restart

/etc/init.d/djigzo restart

Because if licensing issues, we use the JDBC driver from MariaDB
(https://downloads.mariadb.org/connector-java/). If you insist on using
the driver from MySQL, you need to remove the MariaDB jdbc driver, add
the MySQL JDBC driver and configure the hibernate config file to use the
MySQL driver.

The RPM is probably not final so it might change. We are thinking of
splitting up the packages (RPM, deb) into a back-end package which does
not automatically configure Postgres and a database specific back-end
package which depends on the main back-end package.

Let me know how it goes.

Kind regards,

Martijn Brinkers

路路路

On 06/30/2015 09:37 AM, django(a)nausch.org wrote:

Griasde Martijn!

Quoting "Martijn Brinkers" martijn(a)djigzo.com:

Interesting. Why is Postgres not allowed?

It's a political decision - we can use following prioroties ans databeses:

1st Oracle 12
2nd MariaDB

Anyway, perhaps it's not of legal reasons but some technical reason.

FULLACK

That said, Maria DB is not directly supported but you can see whether
you can make it work with Maria DB.

That's a very good idea If you can tell me technical details about
changing the backend database, I'll do it, test it and give feedback to
you!

The database layer uses Hibernate (which is an ORM library).

My colleague Klaus, my database specialist, assumed, that your are using
Hibernate.

This however has never tested but it you are willing to spend some
time testing it, I can assist you.

Oh yes, I'll make this tests! So please tell me, what I should do!

Thanks for help!!!

Best regards
Django

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

Martijn, as always, you are awesome!

Running Ubuntu over here, would I be able to 鈥渁lien鈥 these .rpm files into .deb and get the same results?

I am really looking forward to trying this out tomorrow. Thank you for your efforts, this is greatly appreciated!

Cheers.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jul 4, 2015, at 2:27 PM, Martijn Brinkers <martijn(a)djigzo.com> wrote:

Hi,

It turned out that adding support for MySQL (MariaDB) was more work than
just changing the Hibernate dialect. We had to make sure that the
required changes did not require existing users to update the database.
Modifying all the unit tests to support different databases was also
quite a bit of work. That said, now we have it working for MySQL, in
principle adding support for other database (if ever) is now relative easy.

We have uploaded an RPM which does not depend on Postgres and contains
the required configuration files for MySQL.

The RPMs can be downloaded from

Backend;

http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm

Instructions:

1. Install RPMs

Install the RPMs using the quick install guide
(https://www.ciphermail.com/documents/quick-install-guide.txt)

Note: skip the parts about postgres

2. Install and configure MySQL (MariaDB)

Install MySQL

yum -y install mariadb-server mariadb
systemctl start mariadb
systemctl enable mariadb

max_allowed_packet should be set to a high value (in mysql config)

$ vi /etc/my.cnf.d/server.cnf

Add the following line below [server]

max_allowed_packet = 128M

Note: the max size depends on the largest email, CRL you want to support.

systemctl restart mariadb

3. Create database user and database

mysql -u root

CREATE USER 'djigzo'@'localhost' IDENTIFIED BY 'djigzo';
CREATE DATABASE djigzo CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL ON djigzo.* TO 'djigzo'@'localhost';

Note 1: The database should be set to UTF8
Note 2: ALL permission should probably be changed to a lower privilege
but we did not yet check what the minimal required privileges are.

4. Import database scheme

$ mysql -u djigzo --password=djigzo djigzo <
/usr/share/djigzo/conf/database/sql/djigzo.mysql.sql

5. Tell CipherMail to use MySQL

$ vi /usr/share/djigzo/wrapper/djigzo.wrapper.conf

Uncomment the line:

wrapper.java.additional.15=-Dciphermail.hibernate.database.type=mysql

6. Restart

/etc/init.d/djigzo restart

Because if licensing issues, we use the JDBC driver from MariaDB
(https://downloads.mariadb.org/connector-java/). If you insist on using
the driver from MySQL, you need to remove the MariaDB jdbc driver, add
the MySQL JDBC driver and configure the hibernate config file to use the
MySQL driver.

The RPM is probably not final so it might change. We are thinking of
splitting up the packages (RPM, deb) into a back-end package which does
not automatically configure Postgres and a database specific back-end
package which depends on the main back-end package.

Let me know how it goes.

Kind regards,

Martijn Brinkers

On 06/30/2015 09:37 AM, django(a)nausch.org <mailto:django(a)nausch.org> wrote:

Griasde Martijn!

Quoting "Martijn Brinkers" martijn(a)djigzo.com:

Interesting. Why is Postgres not allowed?

It's a political decision - we can use following prioroties ans databeses:

1st Oracle 12
2nd MariaDB

Anyway, perhaps it's not of legal reasons but some technical reason.

FULLACK

That said, Maria DB is not directly supported but you can see whether
you can make it work with Maria DB.

That's a very good idea If you can tell me technical details about
changing the backend database, I'll do it, test it and give feedback to
you!

The database layer uses Hibernate (which is an ORM library).

My colleague Klaus, my database specialist, assumed, that your are using
Hibernate.

This however has never tested but it you are willing to spend some
time testing it, I can assist you.

Oh yes, I'll make this tests! So please tell me, what I should do!

Thanks for help!!!

Best regards
Django

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com/&gt;

Twitter: http://twitter.com/CipherMail
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com <mailto:Users(a)lists.djigzo.com>
https://lists.djigzo.com/lists/listinfo/users

Hi Laz,

The Ubuntu/Debian version can be downloaded from:

Backend:

http://www.ciphermail.com/downloads/other/djigzo_2.10.3-0_all-no-database.deb

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web_2.10.3-0_all.deb

(add .asc for GPG sig)

Kind regards,

Martijn Brinkers

Martijn, as always, you are awesome!

Running Ubuntu over here, would I be able to 鈥渁lien鈥 these .rpm files
into .deb and get the same results?

I am really looking forward to trying this out tomorrow. Thank you
for your efforts, this is greatly appreciated!

Cheers.

~ Laz Peterson Paravis, LLC Ph: 951.319.3240 x201

Hi,

It turned out that adding support for MySQL (MariaDB) was more work
than just changing the Hibernate dialect. We had to make sure that
the required changes did not require existing users to update the
database. Modifying all the unit tests to support different
databases was also quite a bit of work. That said, now we have it
working for MySQL, in principle adding support for other database
(if ever) is now relative easy.

We have uploaded an RPM which does not depend on Postgres and
contains the required configuration files for MySQL.

The RPMs can be downloaded from

Backend;

http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm
<http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm&gt;

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm
<http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm&gt;

Instructions:

1. Install RPMs

Install the RPMs using the quick install guide
(https://www.ciphermail.com/documents/quick-install-guide.txt
<https://www.ciphermail.com/documents/quick-install-guide.txt&gt;\)

Note: skip the parts about postgres

2. Install and configure MySQL (MariaDB)

Install MySQL

yum -y install mariadb-server mariadb systemctl start mariadb
systemctl enable mariadb

max_allowed_packet should be set to a high value (in mysql config)

$ vi /etc/my.cnf.d/server.cnf

Add the following line below [server]

max_allowed_packet = 128M

Note: the max size depends on the largest email, CRL you want to
support.

systemctl restart mariadb

3. Create database user and database

mysql -u root

CREATE USER 'djigzo'@'localhost' IDENTIFIED BY 'djigzo'; CREATE
DATABASE djigzo CHARACTER SET utf8 COLLATE utf8_general_ci; GRANT
ALL ON djigzo.* TO 'djigzo'@'localhost';

Note 1: The database should be set to UTF8 Note 2: ALL permission
should probably be changed to a lower privilege but we did not yet
check what the minimal required privileges are.

4. Import database scheme

$ mysql -u djigzo --password=djigzo djigzo <
/usr/share/djigzo/conf/database/sql/djigzo.mysql.sql

5. Tell CipherMail to use MySQL

$ vi /usr/share/djigzo/wrapper/djigzo.wrapper.conf

Uncomment the line:

wrapper.java.additional.15=-Dciphermail.hibernate.database.type=mysql

6. Restart

路路路

On 07/05/2015 01:44 AM, Laz C. Peterson wrote:

On Jul 4, 2015, at 2:27 PM, Martijn Brinkers <martijn(a)djigzo.com> >> wrote:

/etc/init.d/djigzo restart

Because if licensing issues, we use the JDBC driver from MariaDB
(https://downloads.mariadb.org/connector-java/
<https://downloads.mariadb.org/connector-java/&gt;\). If you insist on
using the driver from MySQL, you need to remove the MariaDB jdbc
driver, add the MySQL JDBC driver and configure the hibernate
config file to use the MySQL driver.

The RPM is probably not final so it might change. We are thinking
of splitting up the packages (RPM, deb) into a back-end package
which does not automatically configure Postgres and a database
specific back-end package which depends on the main back-end
package.

Let me know how it goes.

Kind regards,

Martijn Brinkers

On 06/30/2015 09:37 AM, django(a)nausch.org >> <mailto:django(a)nausch.org> wrote:

Griasde Martijn!

Quoting "Martijn Brinkers" martijn(a)djigzo.com:

Interesting. Why is Postgres not allowed?

It's a political decision - we can use following prioroties ans
databeses:

1st Oracle 12 2nd MariaDB

Anyway, perhaps it's not of legal reasons but some technical
reason.

FULLACK

That said, Maria DB is not directly supported but you can see
whether you can make it work with Maria DB.

That's a very good idea If you can tell me technical details
about changing the backend database, I'll do it, test it and
give feedback to you!

The database layer uses Hibernate (which is an ORM library).

My colleague Klaus, my database specialist, assumed, that your
are using Hibernate.

This however has never tested but it you are willing to spend
some time testing it, I can assist you.

Oh yes, I'll make this tests! So please tell me, what I should
do!

Thanks for help!!!

Best regards Django

-- CipherMail email encryption

Open source email encryption gateway with support for S/MIME,
OpenPGP and PDF messaging.

https://www.ciphermail.com/&gt;

Twitter: http://twitter.com/CipherMail
<http://twitter.com/CipherMail&gt;
_______________________________________________ Users mailing list
Users(a)lists.djigzo.com <mailto:Users(a)lists.djigzo.com>
https://lists.djigzo.com/lists/listinfo/users
<https://lists.djigzo.com/lists/listinfo/users&gt;

_______________________________________________ Users mailing list
Users(a)lists.djigzo.com https://lists.djigzo.com/lists/listinfo/users

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

Hello Martijn, thank you for these .deb files. I have set up the test server and all looks to be running smoothly except for (so far) one thing I have noticed.

When importing certificates or keys under 鈥淐ertificates鈥 menu, we are getting an error that says:

Keys: The private key file could not be imported. Cause: ClassNotFoundException: org.bouncycastle.jcajce.provider.keystore.pkcs12.PKCS12KeyStoreSpi$BCPKCS12KeyStore
Certificates: An unexpected application exception has occurred. org/bouncycastle/asn1/ANS1InputStream

Since I am not tossing this into production just yet, I don鈥檛 know of a way to test the certificates/keys automatically importing from incoming emails. If you have a way to do that without being a live relay, I would be happy to test.

Also, the /etc/djigzo folder no longer contains the symbolic links to the CipherMail configuration files. (/etc/djigzo-web is fine.)

Other than that, looks great! Thanks again Martijn, your help is greatly appreciated. I will let you know if I see anything else that looks interesting.

~ Laz Peterson
Paravis, LLC
Ph: 951.319.3240 x201

路路路

On Jul 5, 2015, at 1:24 AM, Martijn Brinkers <martijn(a)ciphermail.com> wrote:

Hi Laz,

The Ubuntu/Debian version can be downloaded from:

Backend:

http://www.ciphermail.com/downloads/other/djigzo_2.10.3-0_all-no-database.deb

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web_2.10.3-0_all.deb

(add .asc for GPG sig)

Kind regards,

Martijn Brinkers

On 07/05/2015 01:44 AM, Laz C. Peterson wrote:

Martijn, as always, you are awesome!

Running Ubuntu over here, would I be able to 鈥渁lien鈥 these .rpm files
into .deb and get the same results?

I am really looking forward to trying this out tomorrow. Thank you
for your efforts, this is greatly appreciated!

Cheers.

~ Laz Peterson Paravis, LLC Ph: 951.319.3240 x201

On Jul 4, 2015, at 2:27 PM, Martijn Brinkers <martijn(a)djigzo.com> >>> wrote:

Hi,

It turned out that adding support for MySQL (MariaDB) was more work
than just changing the Hibernate dialect. We had to make sure that
the required changes did not require existing users to update the
database. Modifying all the unit tests to support different
databases was also quite a bit of work. That said, now we have it
working for MySQL, in principle adding support for other database
(if ever) is now relative easy.

We have uploaded an RPM which does not depend on Postgres and
contains the required configuration files for MySQL.

The RPMs can be downloaded from

Backend;

http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm
<http://www.ciphermail.com/downloads/other/djigzo-2.10.3-0.noarch-no-database.rpm&gt;

Web GUI:

http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm
<http://www.ciphermail.com/downloads/other/djigzo-web-2.10.3-0.noarch.rpm&gt;

Instructions:

1. Install RPMs

Install the RPMs using the quick install guide
(https://www.ciphermail.com/documents/quick-install-guide.txt
<https://www.ciphermail.com/documents/quick-install-guide.txt&gt;\)

Note: skip the parts about postgres

2. Install and configure MySQL (MariaDB)

Install MySQL

yum -y install mariadb-server mariadb systemctl start mariadb
systemctl enable mariadb

max_allowed_packet should be set to a high value (in mysql config)

$ vi /etc/my.cnf.d/server.cnf

Add the following line below [server]

max_allowed_packet = 128M

Note: the max size depends on the largest email, CRL you want to
support.

systemctl restart mariadb

3. Create database user and database

mysql -u root

CREATE USER 'djigzo'@'localhost' IDENTIFIED BY 'djigzo'; CREATE
DATABASE djigzo CHARACTER SET utf8 COLLATE utf8_general_ci; GRANT
ALL ON djigzo.* TO 'djigzo'@'localhost';

Note 1: The database should be set to UTF8 Note 2: ALL permission
should probably be changed to a lower privilege but we did not yet
check what the minimal required privileges are.

4. Import database scheme

$ mysql -u djigzo --password=djigzo djigzo <
/usr/share/djigzo/conf/database/sql/djigzo.mysql.sql

5. Tell CipherMail to use MySQL

$ vi /usr/share/djigzo/wrapper/djigzo.wrapper.conf

Uncomment the line:

wrapper.java.additional.15=-Dciphermail.hibernate.database.type=mysql

6. Restart

/etc/init.d/djigzo restart

Because if licensing issues, we use the JDBC driver from MariaDB
(https://downloads.mariadb.org/connector-java/
<https://downloads.mariadb.org/connector-java/&gt;\). If you insist on
using the driver from MySQL, you need to remove the MariaDB jdbc
driver, add the MySQL JDBC driver and configure the hibernate
config file to use the MySQL driver.

The RPM is probably not final so it might change. We are thinking
of splitting up the packages (RPM, deb) into a back-end package
which does not automatically configure Postgres and a database
specific back-end package which depends on the main back-end
package.

Let me know how it goes.

Kind regards,

Martijn Brinkers

On 06/30/2015 09:37 AM, django(a)nausch.org >>> <mailto:django(a)nausch.org> wrote:

Griasde Martijn!

Quoting "Martijn Brinkers" martijn(a)djigzo.com:

Interesting. Why is Postgres not allowed?

It's a political decision - we can use following prioroties ans
databeses:

1st Oracle 12 2nd MariaDB

Anyway, perhaps it's not of legal reasons but some technical
reason.

FULLACK

That said, Maria DB is not directly supported but you can see
whether you can make it work with Maria DB.

That's a very good idea If you can tell me technical details
about changing the backend database, I'll do it, test it and
give feedback to you!

The database layer uses Hibernate (which is an ORM library).

My colleague Klaus, my database specialist, assumed, that your
are using Hibernate.

This however has never tested but it you are willing to spend
some time testing it, I can assist you.

Oh yes, I'll make this tests! So please tell me, what I should
do!

Thanks for help!!!

Best regards Django

-- CipherMail email encryption

Open source email encryption gateway with support for S/MIME,
OpenPGP and PDF messaging.

https://www.ciphermail.com/&gt;

Twitter: http://twitter.com/CipherMail
<http://twitter.com/CipherMail&gt;
_______________________________________________ Users mailing list
Users(a)lists.djigzo.com <mailto:Users(a)lists.djigzo.com>
https://lists.djigzo.com/lists/listinfo/users
<https://lists.djigzo.com/lists/listinfo/users&gt;

_______________________________________________ Users mailing list
Users(a)lists.djigzo.com https://lists.djigzo.com/lists/listinfo/users

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users

Hello Martijn, thank you for these .deb files. I have set up the
test server and all looks to be running smoothly except for (so far)
one thing I have noticed.

When importing certificates or keys under 鈥淐ertificates鈥 menu, we are
getting an error that says:

Keys: The private key file could not be imported. Cause:
ClassNotFoundException:
org.bouncycastle.jcajce.provider.keystore.pkcs12.PKCS12KeyStoreSpi$BCPKCS12KeyStore
Certificates: An unexpected application exception has occurred.
org/bouncycastle/asn1/ANS1InputStream

Could it be that you upgraded an existing server and forgot to restart
tomcat? I just tested it with a fresh installation on Ubuntu 14.04 and
was able to import a p12 (pfx) file without any problem.

Since I am not tossing this into production just yet, I don鈥檛 know of
a way to test the certificates/keys automatically importing from
incoming emails. If you have a way to do that without being a live
relay, I would be happy to test.

Also, the /etc/djigzo folder no longer contains the symbolic links to
the CipherMail configuration files. (/etc/djigzo-web is fine.)

This was already changed with the last release (2.10.0-3). I decided
that it was not worth the effort to symlink all config files from /etc/
to the real dir. Some config files were symlinked from /etc/ and some
were not.

Kind regards,

Martijn Brinkers

路路路

On 07/05/2015 07:51 PM, Laz C. Peterson wrote:

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

Ah, let me try restarting Tomcat.

~ Laz Peterson
Paravis, LLC

路路路

On Jul 5, 2015, at 12:49 PM, Martijn Brinkers <martijn(a)ciphermail.com> wrote:

On 07/05/2015 07:51 PM, Laz C. Peterson wrote:

Hello Martijn, thank you for these .deb files. I have set up the
test server and all looks to be running smoothly except for (so far)
one thing I have noticed.

When importing certificates or keys under 鈥淐ertificates鈥 menu, we are
getting an error that says:

Keys: The private key file could not be imported. Cause:
ClassNotFoundException:
org.bouncycastle.jcajce.provider.keystore.pkcs12.PKCS12KeyStoreSpi$BCPKCS12KeyStore
Certificates: An unexpected application exception has occurred.
org/bouncycastle/asn1/ANS1InputStream

Could it be that you upgraded an existing server and forgot to restart
tomcat? I just tested it with a fresh installation on Ubuntu 14.04 and
was able to import a p12 (pfx) file without any problem.

Since I am not tossing this into production just yet, I don鈥檛 know of
a way to test the certificates/keys automatically importing from
incoming emails. If you have a way to do that without being a live
relay, I would be happy to test.

Also, the /etc/djigzo folder no longer contains the symbolic links to
the CipherMail configuration files. (/etc/djigzo-web is fine.)

This was already changed with the last release (2.10.0-3). I decided
that it was not worth the effort to symlink all config files from /etc/
to the real dir. Some config files were symlinked from /etc/ and some
were not.

Kind regards,

Martijn Brinkers

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail

--
CipherMail email encryption

Open source email encryption gateway with support for S/MIME, OpenPGP
and PDF messaging.

https://www.ciphermail.com

Twitter: http://twitter.com/CipherMail
_______________________________________________
Users mailing list
Users(a)lists.djigzo.com
https://lists.djigzo.com/lists/listinfo/users