[Opendnssec-user] upgrade debian Jessie to Stretch: database trouble

[Dennis Baaten]

Hi,



I upgraded my server from Debian Jessie (8) to Debian Stretch (9; the newest
Stable release). With this upgrade OpenDNSSEC is upgraded from 1.4.6 to
2.0.3, while Mysql 5.5.58 is upgraded to MariaDB 10.1.26. My current
OpenDNSSEC implementation uses a MySQL backend and follows the principle of
'input and output files'.



During the upgrade I was made aware of the fact that the OpenDNSSEC upgrade
to 2.0.3 requires two steps. Quote:



******

There are two steps in the database migration. If you are running
OpenDNSSEC 1.4.6 from Debian stable, you need to apply the SQL statements
from /usr/share/opendnssec/migrate_1_4_8.{mysql,sqlite3} against your
existing database.



The enforcer does require a full migration, as the internal database has
been completely revised. See the upstream documentation in the
/usr/share/opendnssec/1.4-2.0_db_convert/README.md for a description.




You should also review the documentation on the OpenDNSSEC site. This can
be updated in between releases to provide more help. Especially if you have
tooling around OpenDNSSEC you should be aware that some command line
utilities have changed. A fair amount of backward compatibility has been
respected, but changes are present.

******



In an attempt to migrate the database, I first ran the migrate_1_4_8.mysql.
This threw the error that KEYDATA_VIEW already existed. As this table was
empty, I removed it, and ran the remaining part of the SQL from
migrate_1_4_8.mysql again. This resulted in a view with the name
KEYDATA_VIEW. No further errors.



The next step (migrating to 2.0 db) involves running of convert_mysql in
Bash. Initially this results in "error 1071 specified key was too long max
key length is 767 bytes". I tried to resolve this by setting the InnoDB
variable innodb_file_format to 'Barracuda' and enabling innodb_large_prefix.
This resulted in a new but similar error: "error 1709 index column size too
large. The maximum column size is 767 bytes.". And I've not been able to
resolve this, which basically means that I'm stuck in the migration process
towards OpenDNSSEC 2.0 and cannot start the relevant services on my server.



Any help is much appreciated.



--

Dennis

[Berry A.W. van Halderen]

This post might be inappropriate. Click to display it.

[Casper Gielen]

Post by Berry A.W. van Halderen

Post by Dennis Baaten
The next step (migrating to 2.0 db) involves running of convert_mysql in
Bash. Initially this results in “error 1071 specified key was too long
max key length is 767 bytes”. I tried to resolve this by setting the
InnoDB variable innodb_file_format to ‘Barracuda’ and enabling
innodb_large_prefix. This resulted in a new but similar error: “error
1709 index column size too large. The maximum column size is 767
bytes.”. And I’ve not been able to resolve this, which basically means
that I’m stuck in the migration process towards OpenDNSSEC 2.0 and
cannot start the relevant services on my server.

Contacted off-list for further database dumps. I would expected any
need to really change the file format or prefixes. That seems real
odd. The biggest index we have is (rightfully) on a keylocator column
of 255 bytes. Almost all others are on integers. I suspect some
polluting has occurred in the database and contains some weird
data in certain columns.

The problem is in:

CREATE UNIQUE INDEX zoneName ON zone ( name(255) );

The key is 255 characters, but a character may take multiple bytes,
depending on the enconding.

[Casper Gielen]

Post by Dennis Baaten
Hi,
The next step (migrating to 2.0 db) involves running of convert_mysql in
Bash. Initially this results in “error 1071 specified key was too long
max key length is 767 bytes”. I tried to resolve this by setting the
InnoDB variable innodb_file_format to ‘Barracuda’ and enabling
innodb_large_prefix. This resulted in a new but similar error: “error
1709 index column size too large. The maximum column size is 767
bytes.”. And I’ve not been able to resolve this, which basically means
that I’m stuck in the migration process towards OpenDNSSEC 2.0 and
cannot start the relevant services on my server.

I encountered the same problem, use Barracuda with ROW_FORMAT=DYNAMIC .


Some notes from my upgrade documentation, slightly edited and translated from Dutch.
Sorry for poor language.

# step 0 - halt operation
***@metagross:~# opendnssec-enforcer stop
***@metagross:~# opendnssec-signer stop

# step 1 - DB upgrade
# First upgraden from 1.4.6 to 1.4.8 (from database v3 to v4)
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf opendnssec < /usr/share/opendnssec/migrate_1_4_8.mysql

# step 2 - DB upgrade
# Search zones in state "waiting voor ds-seen"; those are not backwards compatible and should be avoided.
# I just checked that I hd no such zones:
MariaDB [opendnssec]> SELECT zones.name
-> FROM dnsseckeys
-> JOIN zones on zones.id = dnsseckeys.zone_id
-> WHERE dnsseckeys.keytype = 257
-> AND dnsseckeys.active IS NULL
-> AND dnsseckeys.zone_id NOT IN
-> (SELECT dnsseckeys.zone_id
-> FROM dnsseckeys
-> WHERE dnsseckeys.keytype = 257
-> AND dnsseckeys.state = 4)
-> ;

# Inplace upgrade of DB not possible. Create a new one and fill it.

# step 3 - create new DB aanmaken
# First make sure that new databaes are of type Barracuda with ROW_FORMAT=DYNAMIC
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf create odsnew

# load new DB scheme
***@metagross:~# wget https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/src/db/schema.mysql
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf odsnew < schema-2.0.mysql

# step 4 - convert data
# prepare script
***@metagross:~# wget https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/utils/1.4-2.0_db_convert/mysql_convert.sql
***@metagross:~# sed -i "s/REMOTE/opendnssec/g" mysql_convert_1.4.8-2.0.sql
# Manually replace calls to strftime with UNIX_TIMESTAMP()
# I've been told this has been fixed in the scripts for ODS 2.1, you might want to try that version first.
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf odsnew < mysql_convert_1.4.8-2.0.sql

# step 5 - rename database
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop opendnssec
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf create opendnssec
***@metagross:~# mysqldump --defaults-file=/etc/mysql/debian.cnf odsnew | mysql --defaults-file=/etc/mysql/debian.cnf opendnssec
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop odsnew

# step 6 - upgrade SoftHSM
# create new HSM
***@metagross:~# mkdir /var/lib/opendnssec/tokens/
***@metagross:~# softhsm2-util --init-token --slot 0 --label OpenDNSSEC
***@metagross:~# softhsm2-migrate --db /var/lib/softhsm/slot0.db --token OpenDNSSEC

# Migrate OpenDNSSEC
# First update your conf.xml. (hint: "port" in the MySQL section has become case sensitive)
***@metagross:~# ods-migrate

***@metagross:~# opendnssec-enforcer start
***@metagross:~# opendnssec-signer start

[Dennis Baaten]

Thanks for the info! This is helpful. Still getting the error when trying to
load the new DB scheme (step 3). This probably is related to the fact that
I'm having trouble to explicitly create a database using Barracuda with
ROW_FORMAT=DYNAMIC.
--
Dennis

-----Oorspronkelijk bericht-----
Van: Casper Gielen [mailto:***@uvt.nl]
Verzonden: donderdag 2 november 2017 14:05
Aan: opendnssec-***@lists.opendnssec.org
CC: Dennis Baaten <***@baaten.com>
Onderwerp: Re: [Opendnssec-user] upgrade debian Jessie to Stretch: database
trouble

Post by Dennis Baaten
Hi,
The next step (migrating to 2.0 db) involves running of convert_mysql
in Bash. Initially this results in "error 1071 specified key was too
long max key length is 767 bytes". I tried to resolve this by setting
the InnoDB variable innodb_file_format to 'Barracuda' and enabling
innodb_large_prefix. This resulted in a new but similar error: "error
1709 index column size too large. The maximum column size is 767
bytes.". And I've not been able to resolve this, which basically means
that I'm stuck in the migration process towards OpenDNSSEC 2.0 and
cannot start the relevant services on my server.

I encountered the same problem, use Barracuda with ROW_FORMAT=DYNAMIC .


Some notes from my upgrade documentation, slightly edited and translated
from Dutch.
Sorry for poor language.

# step 0 - halt operation
***@metagross:~# opendnssec-enforcer stop
***@metagross:~# opendnssec-signer stop

# step 1 - DB upgrade
# First upgraden from 1.4.6 to 1.4.8 (from database v3 to v4)
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf opendnssec <
/usr/share/opendnssec/migrate_1_4_8.mysql

# step 2 - DB upgrade
# Search zones in state "waiting voor ds-seen"; those are not backwards
compatible and should be avoided.
# I just checked that I hd no such zones:
MariaDB [opendnssec]> SELECT zones.name
-> FROM dnsseckeys
-> JOIN zones on zones.id = dnsseckeys.zone_id
-> WHERE dnsseckeys.keytype = 257
-> AND dnsseckeys.active IS NULL
-> AND dnsseckeys.zone_id NOT IN
-> (SELECT dnsseckeys.zone_id
-> FROM dnsseckeys
-> WHERE dnsseckeys.keytype = 257
-> AND dnsseckeys.state = 4)
-> ;

# Inplace upgrade of DB not possible. Create a new one and fill it.

# step 3 - create new DB aanmaken
# First make sure that new databaes are of type Barracuda with
ROW_FORMAT=DYNAMIC
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf create
odsnew

# load new DB scheme
***@metagross:~# wget
https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/src/db/schema.my
sql
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf odsnew <
schema-2.0.mysql

# step 4 - convert data
# prepare script
***@metagross:~# wget
https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/utils/1.4-2.0_db
_convert/mysql_convert.sql
***@metagross:~# sed -i "s/REMOTE/opendnssec/g"
mysql_convert_1.4.8-2.0.sql
# Manually replace calls to strftime with UNIX_TIMESTAMP()
# I've been told this has been fixed in the scripts for ODS 2.1, you might
want to try that version first.
***@metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf odsnew <
mysql_convert_1.4.8-2.0.sql

# step 5 - rename database
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop
opendnssec
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf create
opendnssec
***@metagross:~# mysqldump --defaults-file=/etc/mysql/debian.cnf odsnew
| mysql --defaults-file=/etc/mysql/debian.cnf opendnssec
***@metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop
odsnew

# step 6 - upgrade SoftHSM
# create new HSM
***@metagross:~# mkdir /var/lib/opendnssec/tokens/
***@metagross:~# softhsm2-util --init-token --slot 0 --label OpenDNSSEC
***@metagross:~# softhsm2-migrate --db /var/lib/softhsm/slot0.db --token
OpenDNSSEC

# Migrate OpenDNSSEC
# First update your conf.xml. (hint: "port" in the MySQL section has
become case sensitive)
***@metagross:~# ods-migrate

***@metagross:~# opendnssec-enforcer start
***@metagross:~# opendnssec-signer start

--
Casper Gielen <***@uvt.nl> | LIS UNIX PGP fingerprint = 16BD 2C9F 8156
C242 F981 63B8 2214 083C F80E 4AF7

Universiteit van Tilburg | Postbus 90153, 5000 LE Warandelaan 2 | Telefoon
013 466 4100 | G 236 | http://www.uvt.nl

[Casper Gielen]

Post by Dennis Baaten
Thanks for the info! This is helpful. Still getting the error when trying to
load the new DB scheme (step 3). This probably is related to the fact that
I'm having trouble to explicitly create a database using Barracuda with
ROW_FORMAT=DYNAMIC.

That's done with innodb_large_prefix. Put the following in your config:

[mysqld]
innodb_large_prefix = on
innodb_file_format = Barracuda


PS. I'm on MariaDB, not MySQL, but you probably are as well so it
shouldn't matter.

[Dennis Baaten]

Yes same here. Tried this already.

In /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = on

Check variables with phpMyAdmin, and they are set correctly.

Next: mysqladmin --defaults-file=/etc/mysql/debian.cnf create odsnew
Result: new database

Next: mysql --defaults-file=/etc/mysql/debian.cnf odsnew < /usr/share/opendnssec/schema.mysql
Result: ERROR 1709 (HY000) at line 50: Index column size too large. The maximum column size is 767 bytes.
The index column size...

I've installed opendnssec 2.1.3 from testing (rest is still from stable) and I'm using this schema. Seems to be the same as the one from 2.0.
--
Dennis

-----Oorspronkelijk bericht-----
Van: Casper Gielen [mailto:***@uvt.nl]
Verzonden: donderdag 2 november 2017 16:34
Aan: opendnssec-***@lists.opendnssec.org
CC: Dennis Baaten <***@baaten.com>
Onderwerp: Re: [Opendnssec-user] upgrade debian Jessie to Stretch: database trouble

Post by Dennis Baaten
Thanks for the info! This is helpful. Still getting the error when
trying to load the new DB scheme (step 3). This probably is related to
the fact that I'm having trouble to explicitly create a database using
Barracuda with ROW_FORMAT=DYNAMIC.

That's done with innodb_large_prefix. Put the following in your config:

[mysqld]
innodb_large_prefix = on
innodb_file_format = Barracuda


PS. I'm on MariaDB, not MySQL, but you probably are as well so it shouldn't matter.
--
Casper Gielen <***@uvt.nl> | LIS UNIX PGP fingerprint = 16BD 2C9F 8156 C242 F981 63B8 2214 083C F80E 4AF7

Universiteit van Tilburg | Postbus 90153, 5000 LE Warandelaan 2 | Telefoon 013 466 4100 | G 236 | http://www.uvt.nl

[Casper Gielen]

Post by Dennis Baaten
Yes same here. Tried this already.
[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = on
Check variables with phpMyAdmin, and they are set correctly.

I just realized that my documentation is not complete, thanks for
pointing that out before it my production environment :)

I changed schema.mysql, patch attached.

The trick is appending "ROW_FORMAT=DYNAMIC" to the table definitions.

In hindsight I think the first lines of this patch, (SET GLOBAL...)
are redundant with the configuration above, but I'm not sure.

[Dennis Baaten]

Cool! I've now been able to complete step 3. However, I ran into some trouble with step 4.

In the current Debian testing package the strftime calls have not been replaced with UNIX_TIMESTAMP(). So I used this mysql_convert.sql version from github: https://github.com/yschaeff/opendnssec/blob/4c45d7a1a1a4d09b58eb94c0a6a392c71fafd2d0/enforcer/utils/1.4-2.0_db_convert/mysql_convert.sql and stored it as mysql_convert_patch.sql in /usr/share/opendnssec/. Then I replaced 'REMOTE' with 'opendnssec' using your sed command.

Upon execution the resulted in the following error: "ERROR 1093 (HY000) at line 664: Table 'keyState' is specified twice, both as a target for 'UPDATE' and as a separate source for data.". I find this strange because the support ticket https://issues.opendnssec.org/browse/SUPPORT-205 states that this error has been fixed in the version of mysql_convert.sql I downloaded from github (and stored as mysql_convert_patch.sql).

[Yuri Schaeffer]

Please use a more recent version of the script:

https://github.com/opendnssec/opendnssec/blob/develop/enforcer/utils/1.4-2.0_db_convert/mysql_convert.sql

//Yuri

Post by Dennis Baaten
Cool! I've now been able to complete step 3. However, I ran into some trouble with step 4.
In the current Debian testing package the strftime calls have not been replaced with UNIX_TIMESTAMP(). So I used this mysql_convert.sql version from github: https://github.com/yschaeff/opendnssec/blob/4c45d7a1a1a4d09b58eb94c0a6a392c71fafd2d0/enforcer/utils/1.4-2.0_db_convert/mysql_convert.sql and stored it as mysql_convert_patch.sql in /usr/share/opendnssec/. Then I replaced 'REMOTE' with 'opendnssec' using your sed command.
Upon execution the resulted in the following error: "ERROR 1093 (HY000) at line 664: Table 'keyState' is specified twice, both as a target for 'UPDATE' and as a separate source for data.". I find this strange because the support ticket https://issues.opendnssec.org/browse/SUPPORT-205 states that this error has been fixed in the version of mysql_convert.sql I downloaded from github (and stored as mysql_convert_patch.sql).

[Dennis Baaten]

Thanks, this works. Database successfully migrated.

When performing step 6 described by Casper, I got the following error in syslog: "softhsm2-util: ObjectStore.cpp(59): Failed to enumerate object store in /var/lib/softhsm/tokens/". So instead of "mkdir /var/lib/opendnssec/tokens/", I did " mkdir /var/lib/softhsm/tokens/. After this I was able to migrate to softhsm2 succesfulling, completing step 6 instructions.

Then for the final step. I changed the following in conf.xml:
"port" to "Port"
"<Module>/usr/lib/softhsm/libsofthsm.so</Module>" to "<Module>/usr/lib/softhsm/libsofthsm2.so</Module>"

Then I ran ods-migrate, and got the following error:
Relax-NG validity error : Extra element Signer in interleave
/etc/opendnssec/conf.xml:54: element Signer: Relax-NG validity error : Element Configuration failed to validate content
Segmentation fault

I haven't been able to figure out what is causing this.

[Yuri Schaeffer]

Post by Dennis Baaten
Relax-NG validity error : Extra element Signer in interleave
/etc/opendnssec/conf.xml:54: element Signer: Relax-NG validity error : Element Configuration failed to validate content
Segmentation fault

Can you share your conf.xml? (beware to censor the PIN in the repository
section).

The crash should not happen of course but is probably only related to
the program exit code.

//Yuri

[Dennis Baaten]

<?xml version="1.0" encoding="UTF-8"?>

<Configuration>

<RepositoryList>
<Repository name="SoftHSM">
<Module>/usr/lib/softhsm/libsofthsm2.so</Module>
<TokenLabel>OpenDNSSEC</TokenLabel>
<PIN>*****</PIN>
<RequireBackup/>
</Repository>
</RepositoryList>

<Common>
<Logging>
<!-- Command line verbosity will overwrite configure file -->
<Verbosity>10</Verbosity>
<Syslog><Facility>local0</Facility></Syslog>
</Logging>
<PolicyFile>/etc/opendnssec/kasp.xml</PolicyFile>
<ZoneListFile>/etc/opendnssec/zonelist.xml</ZoneListFile>
</Common>

<Enforcer>
<Privileges>
<User>opendnssec</User>
<Group>opendnssec</Group>
</Privileges>

<!-- NOTE: Enforcer worker threads are not used; this option is ignored -->
<!--
<WorkerThreads>4</WorkerThreads>
-->

<PidFile>/var/run/opendnssec/enforcerd.pid</PidFile>
<Datastore>
<MySQL>
<Host Port="3306">localhost</Host>
<Database>opendnssec</Database>
<Username>*****</Username>
<Password>*****</Password>
</MySQL>
</Datastore>
<Interval>PT3600S</Interval>
<!-- <ManualKeyGeneration/> -->
<RolloverNotification>P14D</RolloverNotification>

<!-- the <DelegationSignerSubmitCommand> will get all current
DNSKEYs (as a RRset) on standard input (with optional CKA_ID)
-->
<DelegationSignerSubmitCommand>/etc/opendnssec/simple-dnskey-mailer.sh</DelegationSignerSubmitCommand>
</Enforcer>

<Signer>
<Privileges>
<User>opendnssec</User>
<Group>opendnssec</Group>
</Privileges>

<PidFile>/var/run/opendnssec/signerd.pid</PidFile>
<SocketFile>/var/run/opendnssec/engine.sock</SocketFile>
<WorkingDirectory>/var/lib/opendnssec/tmp</WorkingDirectory>
<WorkerThreads>4</WorkerThreads>
<SignerThreads>4</SignerThreads>

<!--
<Listener>
<Interface><Port>53</Port></Interface>
</Listener>
-->

<!-- the <NotifyCommmand> will expand the following variables:

%zone the name of the zone that was signed
%zonefile the filename of the signed zone
-->
<!--
<NotifyCommand>/usr/local/bin/my_nameserver_reload_command</NotifyCommand>
-->

<NotifyCommand>/usr/sbin/rndc reload %zone</NotifyCommand>
</Signer>

</Configuration>

--
Dennis

[Yuri Schaeffer]

Post by Dennis Baaten
Relax-NG validity error : Extra element Signer in interleave
/etc/opendnssec/conf.xml:54: element Signer: Relax-NG validity error : Element Configuration failed to validate content

There are two issues

- <Interval> element in the enforcer section is depricated. Later
versions will reject a conf with this element.

- In the <Signer> section <PidFile> and <SocketFile> somehow trip the
parser. If I comment those out it works fine. (The signer will use the
default paths instructed compile time, likely the same in this case)

I don't know what caused the latter but I'll look into it because it
looks like a bug.

//Yuri

[Yuri Schaeffer]

Post by Yuri Schaeffer
I don't know what caused the latter but I'll look into it because it
looks like a bug.

I see the bug was fixed in one of our development branches. I'll make
sure it will be included in a next release.

//Yuri

[Dennis Baaten]

Thanks. I was able to execute ods-migrate without errors.
When trying to start opendnssec-enforcer and opendnssec-signer, I encounter some errors:

ods-enforcerd[16404]: [engine] hsm_get_slot_id(): could not find token with the name OpenDNSSEC

The directory /var/lib/softhsm/tokens/6c6007b7-e40a-6c29-b8a9-eefb00f452e3/ does exists and contains a number of files (I believe the contents were created based on the old /var/lib/softhsm/slot0.db).

ods-signerd: ObjectStore.cpp(74): Failed to open token 6c6007b7-e40a-6c29-b8a9-eefb00f452e3
ods-signerd: [hsm] hsm_get_slot_id(): could not find token with the name OpenDNSSEC

Probably related to the enforcerd error. /etc/opendnssec/conf.xml contains: <TokenLabel>OpenDNSSEC</TokenLabel>.

Could this be related to the fact that (based on the instructions of Casper) I did not migrate to "/var/lib/opendnssec/tokens/" but to "/var/lib/softhsm/tokens"? I did this because I previously got the error: "softhsm2-util: ObjectStore.cpp(59): Failed to enumerate object store in /var/lib/softhsm/tokens/".

[Dennis Baaten]

I solved it.

Just to be sure, I first moved the token directory from /var/lib/softhsm/tokens/ to /var/lib/opendnssec/tokens/. I did this by setting "directories.tokendir = /var/lib/opendnssec/tokens/" in /etc/softhsm/softhsm2.conf. Then I executed the migration steps described by Capser again.
Unfortunately this still resulted in the same error, when starting enforcer and signer.

Then I noticed that the permissions of the token directory and files within this directory. The were set to 'root' while opendnssec runs on user 'opendnssec'. After changing the owner of the directory and the files within, I was able to succesfully start enforcer and signer.

So it took me a couple of days, but it seems that opendnssec is running again. Casper, Yuri and Berry, thanks for your help!

[Dennis Baaten]

In addition to my previous mail: when the error occurs, the following entry is logged in my syslog:

kernel: [100777.309694] ods-migrate[6812]: segfault at 94 ip 0000560b51100c36 sp 00007fff090edd70 error 6 in ods-migrate[560b510fd000+1b000]