[Opendnssec-user] Exporting database schema version is not supported for conversion
Arun Natarajan
arun at arunns.com
Wed Mar 22 11:25:33 UTC 2017
Thanks Yuri for the patch.
# diff enforcer/utils/convert_database.pl
enforcer/utils/convert_database.pl.patched
161c161
< if ($from_version == 3) {
---
> if ($from_version == 4) {
175c175
< if ($from_version == 3) {
---
> if ($from_version == 4) {
406c406
< if ($from_version == 3) {
---
> if ($from_version == 4) {
450c450
< insert => 'INSERT INTO dnsseckeys VALUES ( ?, ?, ?, ?, ?, ?,
?, ?, ?, ? )'
---
> insert => 'INSERT INTO dnsseckeys VALUES ( ?, ?, ?, ?, ?,?,
?, ?, ?, ?, ?, ? )'
# ./enforcer/utils/convert_database.pl.patched --from
dbi:SQLite:dbname=/var/opendnssec/kasp.db --to
dbi:mysql:database=kasp;host=localhost --to-username kaspuser
--to-password kasp
Connected to exporting database dbi:SQLite:dbname=/var/opendnssec/kasp.db
Validating existing data
Create the importing database, this will delete existing data? [NO/yes] yes
Enter importing database password (--to-password): kasp
DBI connect('database=kasp;mysql_multi_statements=1','',...) failed: Access
denied for user 'root'@'localhost' (using password: YES) at
./enforcer/utils/convert_database.pl.patched line 318.
./enforcer/utils/convert_database.pl.patched: Unable to connect to
importing database [dbi:mysql:database=kasp;mysql_multi_statements=1]:
Access denied for user 'root'@'localhost' (using password: YES)
bash: --to-username: command not found
--to-username and --to-password may not be valid for Maria db 5.5?
but with root password
# ./enforcer/utils/convert_database.pl.patched --from
dbi:SQLite:dbname=/var/opendnssec/kasp.db --to
dbi:mysql:database=kasp;host=localhost
Connected to exporting database dbi:SQLite:dbname=/var/opendnssec/kasp.db
Validating existing data
Create the importing database, this will delete existing data? [NO/yes] yes
Enter importing database password (--to-password): rootpassword
Connected to importing database
dbi:mysql:database=kasp;mysql_multi_statements=1
Initializing conversion
Deleting existing data in importing database
Converting database
securitymodules
categories
parameters
serialmodes
policies
zones
keypairs
dnsseckeys
parameters_policies
Optimizing database
Done
MariaDB [kasp]> show tables;
+-----------------------------+
| Tables_in_kasp |
+-----------------------------+
| INT_KEYALLOC_VIEW_FOR_MYSQL |
| KEYALLOC_VIEW |
| KEYDATA_VIEW |
| PARAMETER_LIST |
| PARAMETER_VIEW |
| categories |
| dbadmin |
| dnsseckeys |
| keypairs |
| parameters |
| parameters_policies |
| policies |
| securitymodules |
| serialmodes |
| zones |
+-----------------------------+
15 rows in set (0.00 sec)
--
arun
On 22 March 2017 at 13:51, Yuri Schaeffer <yuri at nlnetlabs.nl> wrote:
> Hi Arun,
>
> Can you apply these changes to the convert_database.pl script and let me
> know if that helped?
>
> Regards,
> //Yuri
>
> diff --git a/enforcer/utils/convert_database.pl
> b/enforcer/utils/convert_database.pl
> index 896e14618..d14ce82e0 100755
> --- a/enforcer/utils/convert_database.pl
> +++ b/enforcer/utils/convert_database.pl
> @@ -158,7 +158,7 @@ $sth->finish;
> #
>
> my $from_version_valid = 0;
> -if ($from_version == 3) {
> +if ($from_version == 4) {
> $from_version_valid = 1;
> }
>
> @@ -172,7 +172,7 @@ unless ($from_version_valid) {
> #
>
> if ($to_data_source eq 'mysql') {
> - if ($from_version == 3) {
> + if ($from_version == 4) {
> my $valid = 1;
> print 'Validating existing data', "\n";
>
> @@ -403,7 +403,7 @@ unless ($from_version == $to_version) {
> # Convert the database
> #
>
> -if ($from_version == 3) {
> +if ($from_version == 4) {
> #
> # Schema version 3 does not need any data modifications so just
> dump it out and in
> #
> @@ -447,7 +447,7 @@ if ($from_version == 3) {
> { dnsseckeys => {
> delete => 'DELETE FROM dnsseckeys',
> select => 'SELECT * FROM dnsseckeys',
> - insert => 'INSERT INTO dnsseckeys VALUES ( ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ? )'
> + insert => 'INSERT INTO dnsseckeys VALUES ( ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ? )'
> }},
> { parameters_policies => {
> delete => 'DELETE FROM parameters_policies',
>
>
> On 22-03-17 10:40, Arun Natarajan wrote:
> > Hi,
> >
> > Trying to convert sqlite kasp.db (1.4.8) to MySQL but getting the
> > following error:
> >
> > # ./convert_database.pl <http://convert_database.pl> --from
> > dbi:SQLite:dbname=/var/opendnssec/kasp.db --to
> > dbi:mysql:database=kasp;host=localhost --to-username kasp --to-password
> kasp
> >
> > Connected to exporting database dbi:SQLite:dbname=/var/
> opendnssec/kasp.db
> > ./convert_database.pl <http://convert_database.pl>: Exporting database
> > schema version is not supported for conversion.
> > bash: --to-username: command not found
> >
> > The kasp.db was originally created for 1.4.7 and later converted to
> 1.4.8.
> >
> > $ sqlite3 /var/opendnssec/kasp.db
> > SQLite version 3.7.17 2013-05-20 00:56:22
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> alter table dnsseckeys add column rfc5011 tinyint default 0;
> > sqlite> alter table dnsseckeys add column revoked tinyint default 0;
> > sqlite> drop view if exists KEYDATA_VIEW;
> > sqlite> create view KEYDATA_VIEW as
> > ...> select k.id <http://k.id> as id, d.state as state, k.generate as
> > generate, d.publish as publish,
> > ...> d.ready as ready, d.active as active, d.retire as retire,
> > d.dead as dead,
> > ...> d.keytype as keytype, k.algorithm as algorithm, k.HSMkey_id
> > as location,
> > ...> d.zone_id as zone_id, k.policy_id as policy_id,
> > ...> k.securitymodule_id as securitymodule_id, k.size as size,
> > ...> k.compromisedflag as compromisedflag,
> > ...> k.fixedDate as fixedDate,
> > ...> d.rfc5011 as rfc5011, d.revoked as revoked
> > ...> from keypairs k left outer join dnsseckeys d
> > ...> on k.id <http://k.id> = d.keypair_id;
> > sqlite> insert into parameters (name, description, category_id) select
> > "revoked", "key is revoked?", id from categories where name="ksk";
> > sqlite> update dbadmin set version = 4;
> > sqlite> .exit
> >
> > appreciate your help.
> >
> > thanks,
> > arun
> >
> >
> > _______________________________________________
> > Opendnssec-user mailing list
> > Opendnssec-user at lists.opendnssec.org
> > https://lists.opendnssec.org/mailman/listinfo/opendnssec-user
> >
>
>
> _______________________________________________
> Opendnssec-user mailing list
> Opendnssec-user at lists.opendnssec.org
> https://lists.opendnssec.org/mailman/listinfo/opendnssec-user
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opendnssec.org/pipermail/opendnssec-user/attachments/20170322/03f7d52f/attachment.htm>
More information about the Opendnssec-user
mailing list