<div dir="ltr"><div>Hi Jorge,<br></div><div><br></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></div><div>If that output does *not* include the character set latin1 on the table, then the following may be helpful.<br></div><div><br></div><div>According to my git-blame'ing skills, the character set change was done in 4.1. [2] [3]<br></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></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></div><div><br></div><div>HTH</div><div><br></div><div>Gert</div><div><br></div><div>[1]: <a 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 href="https://github.com/PowerDNS/pdns/commit/273d7599f16a4dd49669e949ba9ef2f3185b8803">https://github.com/PowerDNS/pdns/commit/273d7599f16a4dd49669e949ba9ef2f3185b8803</a></div><div>[3]: <a 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><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Oct 19, 2020 at 4:09 PM Jorge Arenas via Pdns-users <<a href="mailto:pdns-users@mailman.powerdns.com">pdns-users@mailman.powerdns.com</a>> wrote:<br></div><blockquote class="gmail_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></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></span></span></span></div><div dir="ltr"><span><span lang="en"><span title=""><br></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></span></span></span></div><div dir="ltr"><br></div><div dir="ltr"> <span><span lang="en"><span title="">but I keep getting the same error</span></span></span><br></div><div dir="ltr"><br></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></div><div dir="ltr">But I do not know how correct this</div><div><br></div><div><br></div><div dir="ltr">Jorge Arenas Quezada <br></div><div dir="ltr">Aguascalientes, Ags Mexico <br></div><div dir="ltr"><a href="mailto:jorgeaaq@yahoo.com" target="_blank">jorgeaaq@yahoo.com</a></div></div>
<div><br></div><div><br></div>
</div><div id="gmail-m_2864234332669992962yahoo_quoted_3544481061">
<div style="font-family:"Helvetica Neue",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 href="mailto:markus.ehrlicher@komsa.de" target="_blank">markus.ehrlicher@komsa.de</a>> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div id="gmail-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" href="https://github.com/PowerDNS/pdns/tree/master/modules/gmysqlbackend" target="_blank">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 href="mailto:pdns-users-bounces@mailman.powerdns.com" target="_blank">pdns-users-bounces@mailman.powerdns.com</a>>
<b>Im Auftrag von </b>Jorge Arenas via Pdns-users<br>
<b>Gesendet:</b> Montag, 19. Oktober 2020 05:42<br>
<b>An:</b> <a href="mailto:pdns-users@mailman.powerdns.com" target="_blank">pdns-users@mailman.powerdns.com</a><br>
<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>
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>
Pdns-users mailing list<br>
<a href="mailto:Pdns-users@mailman.powerdns.com" target="_blank">Pdns-users@mailman.powerdns.com</a><br>
<a href="https://mailman.powerdns.com/mailman/listinfo/pdns-users" rel="noreferrer" target="_blank">https://mailman.powerdns.com/mailman/listinfo/pdns-users</a><br>
</blockquote></div>