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

[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:C.Gielen at uvt.nl] 
Verzonden: donderdag 2 november 2017 14:05
Aan: opendnssec-user at lists.opendnssec.org
CC: Dennis Baaten <dennis at baaten.com>
Onderwerp: Re: [Opendnssec-user] upgrade debian Jessie to Stretch: database
trouble

Op 02-11-17 om 10:42 schreef 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
  root at metagross:~# opendnssec-enforcer stop
  root at metagross:~# opendnssec-signer  stop

  # step 1 - DB upgrade
  # First upgraden from 1.4.6 to 1.4.8 (from database v3 to v4)
  root at 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
  root at metagross:~# mysqladmin --defaults-file=/etc/mysql/debian.cnf create
odsnew
 
  # load new DB scheme
  root at metagross:~# wget
https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/src/db/schema.my
sql
  root at metagross:~# mysql --defaults-file=/etc/mysql/debian.cnf odsnew  <
schema-2.0.mysql

  # step 4 - convert data
  # prepare script
  root at metagross:~# wget
https://github.com/opendnssec/opendnssec/raw/2.0.4/enforcer/utils/1.4-2.0_db
_convert/mysql_convert.sql
  root at 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.
  root at metagross:~#  mysql --defaults-file=/etc/mysql/debian.cnf odsnew  <
mysql_convert_1.4.8-2.0.sql

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

  # step 6 - upgrade SoftHSM
  # create new HSM
  root at metagross:~# mkdir /var/lib/opendnssec/tokens/
  root at metagross:~# softhsm2-util --init-token --slot 0 --label OpenDNSSEC
  root at 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)
  root at metagross:~# ods-migrate
  
  root at metagross:~# opendnssec-enforcer start
  root at metagross:~# opendnssec-signer  start

--
Casper Gielen <cgielen at 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