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