[Pdns-users] Error after upgrade

[Gert van Dijk]

Hi Jorge,

Could you please post the output of "show create table records \G" [1] as a
MySQL query on that database? It should print the schema for the 'records'
table as you have it there right now without any contents.

If that output does *not* include the character set latin1 on the table,
then the following may be helpful.

According to my git-blame'ing skills, the character set change was done in
4.1. [2] [3]
I believe your error can be explained by not having performed the right
schema migration or the ignorance of some error was supposed to warn you
for exactly this issue.

You could repeat that ALTER TABLE statement manually and try to reproduce
the error.
If that works, perhaps check all other details of all tables too, to verify
whether not additional migration steps have been omitted for some reason.

HTH

Gert

[1]: https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html
[2]:
https://github.com/PowerDNS/pdns/commit/273d7599f16a4dd49669e949ba9ef2f3185b8803
[3]:
https://github.com/PowerDNS/pdns/blob/8ab2e30d86e930c3d15285bb85fcc69403e4a428/modules/gmysqlbackend/3.4.0_to_4.1.0_schema.mysql.sql#L5

On Mon, Oct 19, 2020 at 4:09 PM Jorge Arenas via Pdns-users <
pdns-users at mailman.powerdns.com> wrote:

> Thanks Marcus
>
> I did the updates of the database schemas (With some warnings on charset
> modifications, but apparently the changes were made)
>
> make the modifications from 3.4.0 to 4.1.0, 4.1.0 to 4.2.0 and 4.2.0 to
> 4.3.0
>
> but I keep getting the same error
>
> reading some post in internet ( I am not an expert on MySQL) they mention
> that this error: "Is generally caused by comparing two strings of
> incompatible collations or by attempting to select data of different
> collations into a combined column"
>
> But I do not know how correct this
>
>
> Jorge Arenas Quezada
> Aguascalientes, Ags Mexico
> jorgeaaq at yahoo.com
>
>
> On Monday, October 19, 2020, 1:04:07 AM CDT, Markus Ehrlicher <
> markus.ehrlicher at komsa.de> wrote:
>
>
> Hello Jorge,
>
>
>
> I stumbled upon the same problem a few months ago. Here you can find the
> schema-updates:
>
> https://github.com/PowerDNS/pdns/tree/master/modules/gmysqlbackend
>
>
>
> best regards,
>
> Markus
>
>
>
> *Von:* Pdns-users <pdns-users-bounces at mailman.powerdns.com> *Im Auftrag
> von *Jorge Arenas via Pdns-users
> *Gesendet:* Montag, 19. Oktober 2020 05:42
> *An:* pdns-users at mailman.powerdns.com
> *Betreff:* [Pdns-users] Error after upgrade
>
>
>
> I have a Centos 7 running PowerDNS
>
>
>
> after a update to
>
>
>
>  mysql-community-server-8.0.22-1.el7.x86_64
>
>  mysql-community-devel-8.0.22-1.el7.x86_64
>
>  pdns-4.3.1-1pdns.el7.x86_64
>
>  pdns-backend-mysql-4.3.1-1pdns.el7.x86_64
>  pdns-tools-4.3.1-1pdns.el7.x86_64
>
>
>
> I am receiving this messages
>
>
>
>
>
> ct 18 22:38:14 pdns_server: Backend error: GSQLBackend unable to find
> before/after (after) for domain_id 48 and qname 'xxxx': Could not execute
> mysql statement: select ordername from records where ordername > ? and
> domain_id=? and disabled=0 and ordername is not null order by 1 asc limit
> 1: Illegal mix of collations (latin1_bin,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '>'
>
>
>
> Oct 18 22:38:16 pdns_server: Backend reported permanent error which
> prevented lookup (GSQLBackend unable to find before/after (after) for
> domain_id 48 and qname 'xxxx': Could not execute mysql statement: select
> ordername from records where ordername > ? and domain_id=? and disabled=0
> and ordername is not null order by 1 asc limit 1: Illegal mix of collations
> (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '>'),
> aborting
>
>
>
> Oct 18 22:38:16 pdns_server: Backend error: GSQLBackend unable to find
> before/after (after) for domain_id 48 and qname 'xxxxxx': Could not execute
> mysql statement: select ordername from records where ordername > ? and
> domain_id=? and disabled=0 and ordername is not null order by 1 asc limit
> 1: Illegal mix of collations (latin1_bin,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '>'
>
>
>
>
>
> I do not know if there some bug or requirement of the updated software
>
>
>
> I had and old version of pdns and I do not know if i need to update my
> database schema
>
>
>
> Can someone help me or point me in the right direction?
>
>
>
> thanks in advance
>
>
>
> Jorge Arenas
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> https://mailman.powerdns.com/mailman/listinfo/pdns-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20201019/76ba131e/attachment.htm>