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

[Casper Gielen]

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