[Opendnssec-user] mysql migrate duplicate entry

[Emil Natan]

Hello,

Testing migration from 1.4.13 to 2.0.4, convert_mysql fails with:

Creating database TEST1 (as user kaspuser)
Creating tables in TEST1 (as user kaspuser)
Converting database
ERROR 1062 (23000) at line 474: Duplicate entry '81' for key 'PRIMARY'

other than that no problems with the DB running 1.4.13.

I can provide the DB offlist if needed.
Thank you in advance.

Emil

[Berry A.W. van Halderen]

Post by Emil Natan
Creating database TEST1 (as user kaspuser)
Creating tables in TEST1 (as user kaspuser)
Converting database
ERROR 1062 (23000) at line 474: Duplicate entry '81' for key 'PRIMARY'
other than that no problems with the DB running 1.4.13.
I can provide the DB offlist if needed.
Thank you in advance.

Thanks for bringing this, yes please provide the original database
state if possible as a full dump (mysql --opt) as I'd also like to look
at the current schema.

Although this isn't causing the problem as far as I can tell now, please
also use version 2.1.0 for upgrading. It contains important
improvements as 2.0.4 is the last release of that branch and support
is provided on the 2.1 branch.

The error you get is puzzling, as it concerns just the transfer of one
unique/primary key to another which shouldn't lead to duplicate entries.

With kind regards,
Berry van Halderen

[Emil Natan]

Since I had to provide database and configuration files most of the
correspondence went offlist, so here is an update and additional issue I
got into.

One of the issues that made the migration fail was that I'm using shared
keys ( <ShareKeys/> ) for one of my policies and the migration script did
not cope well with it. Since then I was provided with updated version which
worked well.

Another issue was that took us some time to figure out was that I did not
pay attention the configure options when building ODS with mysql support
changed, from --with-database-backend=mysql to
--with-enforcer-database=mysql. I was using the former when building 2.1.0
and as a result the enforcer was failing to connect to the DB.

The issue I now got into after switching to 2.1.0 is that the signconf xml
files for zones which use NSEC3 state that NSEC is in use.

Originally it was:
<Denial>
<NSEC3>
<OptOut />
<Hash>
<Algorithm>1</Algorithm>
<Iterations>10</Iterations>
<Salt>2807e01b1cb8b6d1</Salt>
</Hash>
</NSEC3>
</Denial>

Now it is:
<Denial>
<NSEC/>
</Denial>

The zonefiles are still signed with NSEC3 though. Till now the signer
daemon was using the signconf files to find how to sign a zone, but it
seems this has changed now. For me it's bad because I use the data from the
signconf files for the post-signing checks. Do you consider this a bug and
is it possible to fix it? Thank you.

Emil

Post by Berry A.W. van Halderen

Post by Emil Natan
Creating database TEST1 (as user kaspuser)
Creating tables in TEST1 (as user kaspuser)
Converting database
ERROR 1062 (23000) at line 474: Duplicate entry '81' for key 'PRIMARY'
other than that no problems with the DB running 1.4.13.
I can provide the DB offlist if needed.
Thank you in advance.

Thanks for bringing this, yes please provide the original database
state if possible as a full dump (mysql --opt) as I'd also like to look
at the current schema.
Although this isn't causing the problem as far as I can tell now, please
also use version 2.1.0 for upgrading. It contains important
improvements as 2.0.4 is the last release of that branch and support
is provided on the 2.1 branch.
The error you get is puzzling, as it concerns just the transfer of one
unique/primary key to another which shouldn't lead to duplicate entries.
With kind regards,
Berry van Halderen

[Berry A.W. van Halderen]

On 03/06/2017 10:19 AM, Emil Natan wrote:
[deleted summary]

Thanks for sharing.

Post by Emil Natan
The issue I now got into after switching to 2.1.0 is that the signconf
xml files for zones which use NSEC3 state that NSEC is in use.

[deleted]

Post by Emil Natan
The zonefiles are still signed with NSEC3 though. Till now the signer
daemon was using the signconf files to find how to sign a zone, but it
seems this has changed now. For me it's bad because I use the data from
the signconf files for the post-signing checks. Do you consider this a
bug and is it possible to fix it? Thank you.

Yes, that's a bug thats migration related.

\Berry

[Yuri Schaeffer]

Hi Emil

Post by Emil Natan
ERROR 1062 (23000) at line 474: Duplicate entry '81' for key 'PRIMARY'

As Berry said, it has to to with the shared keys option. The fix is
pretty straight forward. I think a DISTINCT keyword in the right place
solves the issue. However with your database in hand I came across
multiple issues with the script.

So I'll work on those and evaluate the fix. I expect to have it finished
tomorrow.

Regards,
Yuri

[Yuri Schaeffer]

Hi Emil,

If you can please test:
https://raw.githubusercontent.com/yschaeff/opendnssec/test/migrate_fix/enforcer/utils/1.4-2.0_db_convert/mysql_convert.sql

//Yuri

Post by Yuri Schaeffer
Hi Emil

Post by Emil Natan
ERROR 1062 (23000) at line 474: Duplicate entry '81' for key 'PRIMARY'

As Berry said, it has to to with the shared keys option. The fix is
pretty straight forward. I think a DISTINCT keyword in the right place
solves the issue. However with your database in hand I came across
multiple issues with the script.
So I'll work on those and evaluate the fix. I expect to have it finished
tomorrow.
Regards,
Yuri
_______________________________________________
Opendnssec-user mailing list
https://lists.opendnssec.org/mailman/listinfo/opendnssec-user