<html><head></head><body><div class="ydpa8b5b050yahoo-style-wrap" style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:13px;"><div><div dir="ltr" data-setdir="false"><div><div dir="ltr" data-setdir="false">Hi Gert:</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">I repeat every line in schema update and apply without any errors... since 3.5 to 4.3 ( <span><span class="ydpefea9936tlid-translation ydpefea9936translation" lang="en"><span title="">except for the indexes that were deleted in the previous execution of the schema updates)</span></span></span><br></div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false">Here is the output of table records</div><div dir="ltr" data-setdir="false"><br></div><div>| records | CREATE TABLE `records` (</div> `id` bigint NOT NULL AUTO_INCREMENT,<br> `domain_id` int DEFAULT NULL,<br> `name` varchar(255) DEFAULT NULL,<br> `type` varchar(10) DEFAULT NULL,<br> `content` varchar(64000) DEFAULT NULL,<br> `ttl` int DEFAULT NULL,<br> `prio` int DEFAULT NULL,<br> `disabled` tinyint(1) DEFAULT '0',<br> `ordername` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,<br> `auth` tinyint(1) DEFAULT NULL,<br> PRIMARY KEY (`id`),<br> KEY `nametype_index` (`name`,`type`),<br> KEY `domain_id` (`domain_id`),<br> KEY `ordername` (`ordername`)<br>) ENGINE=InnoDB AUTO_INCREMENT=42606 DEFAULT CHARSET=latin1 |<br><br></div><div><br></div><div dir="ltr" data-setdir="false">where I found references to '<span>utf8_general_ci</span>' is in domains table</div><div dir="ltr" data-setdir="false"><br></div><div dir="ltr" data-setdir="false"><div>| domains | CREATE TABLE `domains` (<br> `id` int NOT NULL AUTO_INCREMENT,<br> `name` varchar(255) NOT NULL,<br> `master` varchar(128) DEFAULT NULL,<br> `last_check` int DEFAULT NULL,<br> `type` varchar(6) NOT NULL,<br> `notified_serial` int unsigned DEFAULT NULL,<br> `account` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,<br> PRIMARY KEY (`id`),<br> UNIQUE KEY `name_index` (`name`)<br>) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=latin1 |<br><br></div><div><br></div></div><div dir="ltr" data-setdir="false"><br><span></span></div><div dir="ltr" data-setdir="false">Thanks in advance</div><div dir="ltr" data-setdir="false"><br></div></div><div><br></div><div class="ydpa8b5b050signature">jorge arenas quezada Aguascalientes, ags mexico jorgeaaq@yahoo.com</div></div>
<div><br></div><div><br></div>
</div><div id="yahoo_quoted_3423263322" class="yahoo_quoted">
<div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;">
<div>
On Monday, October 19, 2020, 11:22:02 AM CDT, Gert van Dijk via Pdns-users <pdns-users@mailman.powerdns.com> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="yiv0582290263"><div><div dir="ltr"><div>Hi Jorge,<br clear="none"></div><div><br clear="none"></div><div>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.</div><div><br clear="none"></div><div>If that output does *not* include the character set latin1 on the table, then the following may be helpful.<br clear="none"></div><div><br clear="none"></div><div>According to my git-blame'ing skills, the character set change was done in 4.1. [2] [3]<br clear="none"></div><div>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.</div><div><br clear="none"></div><div>You could repeat that ALTER TABLE statement manually and try to reproduce the error.</div><div>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.<br clear="none"></div><div><br clear="none"></div><div>HTH</div><div><br clear="none"></div><div>Gert</div><div><br clear="none"></div><div>[1]: <a rel="nofollow" shape="rect" target="_blank" href="https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html">https://dev.mysql.com/doc/refman/8.0/en/show-create-table.html</a></div><div>[2]: <a rel="nofollow" shape="rect" target="_blank" href="https://github.com/PowerDNS/pdns/commit/273d7599f16a4dd49669e949ba9ef2f3185b8803">https://github.com/PowerDNS/pdns/commit/273d7599f16a4dd49669e949ba9ef2f3185b8803</a></div><div>[3]: <a rel="nofollow" shape="rect" target="_blank" href="https://github.com/PowerDNS/pdns/blob/8ab2e30d86e930c3d15285bb85fcc69403e4a428/modules/gmysqlbackend/3.4.0_to_4.1.0_schema.mysql.sql#L5">https://github.com/PowerDNS/pdns/blob/8ab2e30d86e930c3d15285bb85fcc69403e4a428/modules/gmysqlbackend/3.4.0_to_4.1.0_schema.mysql.sql#L5</a></div></div><br clear="none"><div class="yiv0582290263gmail_quote"><div class="yiv0582290263yqt2702079890" id="yiv0582290263yqt18733"><div class="yiv0582290263gmail_attr" dir="ltr">On Mon, Oct 19, 2020 at 4:09 PM Jorge Arenas via Pdns-users <<a rel="nofollow" shape="rect" ymailto="mailto:pdns-users@mailman.powerdns.com" target="_blank" href="mailto:pdns-users@mailman.powerdns.com">pdns-users@mailman.powerdns.com</a>> wrote:<br clear="none"></div><blockquote class="yiv0582290263gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div><div style="font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:13px;"><div><div dir="ltr">Thanks Marcus</div><div dir="ltr"><br clear="none"></div><div dir="ltr"><span><span lang="en"><span title="">I did the updates of the database schemas (With some warnings on charset modifications, <span><span lang="en"><span title="">but apparently the changes were made</span></span></span>)<br clear="none"></span></span></span></div><div dir="ltr"><span><span lang="en"><span title=""><br clear="none"></span></span></span></div><div dir="ltr"><span><span lang="en"><span title="">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 <br clear="none"></span></span></span></div><div dir="ltr"><br clear="none"></div><div dir="ltr"> <span><span lang="en"><span title="">but I keep getting the same error</span></span></span><br clear="none"></div><div dir="ltr"><br clear="none"></div><div dir="ltr">reading some post in internet ( I am not an expert on MySQL) they mention that this error: "I<span>s generally caused by comparing two strings of incompatible
collations or by attempting to select data of different collations into a
combined column"</span></div><div dir="ltr"><br clear="none"></div><div dir="ltr">But I do not know how correct this</div><div><br clear="none"></div><div><br clear="none"></div><div dir="ltr">Jorge Arenas Quezada <br clear="none"></div><div dir="ltr">Aguascalientes, Ags Mexico <br clear="none"></div><div dir="ltr"><a rel="nofollow" shape="rect" ymailto="mailto:jorgeaaq@yahoo.com" target="_blank" href="mailto:jorgeaaq@yahoo.com">jorgeaaq@yahoo.com</a></div></div>
<div><br clear="none"></div><div><br clear="none"></div>
</div><div id="yiv0582290263gmail-m_2864234332669992962yahoo_quoted_3544481061">
<div style="font-family:Helvetica, Arial, sans-serif;font-size:13px;color:rgb(38,40,42);">
<div>
On Monday, October 19, 2020, 1:04:07 AM CDT, Markus Ehrlicher <<a rel="nofollow" shape="rect" ymailto="mailto:markus.ehrlicher@komsa.de" target="_blank" href="mailto:markus.ehrlicher@komsa.de">markus.ehrlicher@komsa.de</a>> wrote:
</div>
<div><br clear="none"></div>
<div><br clear="none"></div>
<div><div id="yiv0582290263gmail-m_2864234332669992962yiv5784906753">
<div>
<div>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);">Hello Jorge,</span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);"> </span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);">I stumbled upon the same problem a few months ago. Here you can find the schema-updates:</span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);"><a rel="nofollow" shape="rect" target="_blank" href="https://github.com/PowerDNS/pdns/tree/master/modules/gmysqlbackend">https://github.com/PowerDNS/pdns/tree/master/modules/gmysqlbackend</a></span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);"> </span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);">best regards,</span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);">Markus</span></p>
<p><span style="font-size:11pt;font-family:sans-serif;color:rgb(31,73,125);"> </span></p>
<div>
<div style="border-color:rgb(225,225,225) currentcolor currentcolor;border-style:solid none none;border-width:1pt medium medium;padding:3pt 0cm 0cm;">
<p><b><span style="font-size:11pt;font-family:sans-serif;">Von:</span></b><span style="font-size:11pt;font-family:sans-serif;"> Pdns-users <<a rel="nofollow" shape="rect" ymailto="mailto:pdns-users-bounces@mailman.powerdns.com" target="_blank" href="mailto:pdns-users-bounces@mailman.powerdns.com">pdns-users-bounces@mailman.powerdns.com</a>>
<b>Im Auftrag von </b>Jorge Arenas via Pdns-users<br clear="none">
<b>Gesendet:</b> Montag, 19. Oktober 2020 05:42<br clear="none">
<b>An:</b> <a rel="nofollow" shape="rect" ymailto="mailto:pdns-users@mailman.powerdns.com" target="_blank" href="mailto:pdns-users@mailman.powerdns.com">pdns-users@mailman.powerdns.com</a><br clear="none">
<b>Betreff:</b> [Pdns-users] Error after upgrade</span></p>
</div>
</div>
<p> </p>
<div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">I have a Centos 7 running PowerDNS</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">after a update to
</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> mysql-community-server-8.0.22-1.el7.x86_64</span></p>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> mysql-community-devel-8.0.22-1.el7.x86_64</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> pdns-4.3.1-1pdns.el7.x86_64</span></p>
</div>
<p><span style="font-size:10pt;font-family:sans-serif;"> pdns-backend-mysql-4.3.1-1pdns.el7.x86_64<br clear="none">
pdns-tools-4.3.1-1pdns.el7.x86_64</span></p>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">I am receiving this messages
</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">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 '>'</span></p>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">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</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">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 '>'</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">I do not know if there some bug or requirement of the updated software
</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">I had and old version of pdns and I do not know if i need to update my database schema</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">Can someone help me or point me in the right direction?</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">thanks in advance</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;">Jorge Arenas</span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
</div>
</div>
<div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
<div>
<p><span style="font-size:10pt;font-family:sans-serif;"> </span></p>
</div>
</div>
</div>
</div>
</div></div>
</div>
</div></div>_______________________________________________<br clear="none">
Pdns-users mailing list<br clear="none">
<a rel="nofollow" shape="rect" ymailto="mailto:Pdns-users@mailman.powerdns.com" target="_blank" href="mailto:Pdns-users@mailman.powerdns.com">Pdns-users@mailman.powerdns.com</a><br clear="none">
<a rel="nofollow" shape="rect" target="_blank" href="https://mailman.powerdns.com/mailman/listinfo/pdns-users">https://mailman.powerdns.com/mailman/listinfo/pdns-users</a><br clear="none">
</blockquote></div></div></div></div><div class="yqt2702079890" id="yqt33267">_______________________________________________<br clear="none">Pdns-users mailing list<br clear="none"><a shape="rect" ymailto="mailto:Pdns-users@mailman.powerdns.com" href="mailto:Pdns-users@mailman.powerdns.com">Pdns-users@mailman.powerdns.com</a><br clear="none"><a shape="rect" href="https://mailman.powerdns.com/mailman/listinfo/pdns-users" target="_blank">https://mailman.powerdns.com/mailman/listinfo/pdns-users</a><br clear="none"></div></div>
</div>
</div></body></html>