[Pdns-users] Error after upgrade

Mon Oct 19 18:16:46 UTC 2020

Hi Gert:
I repeat every line in schema update and apply without any errors... since 3.5 to 4.3 ( except for the indexes that were deleted in the previous execution of the schema updates)

Here is the output of table records
| records | CREATE TABLE `records` (  `id` bigint NOT NULL AUTO_INCREMENT,
  `domain_id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(10) DEFAULT NULL,
  `content` varchar(64000) DEFAULT NULL,
  `ttl` int DEFAULT NULL,
  `prio` int DEFAULT NULL,
  `disabled` tinyint(1) DEFAULT '0',
  `ordername` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `auth` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nametype_index` (`name`,`type`),
  KEY `domain_id` (`domain_id`),
  KEY `ordername` (`ordername`)
) ENGINE=InnoDB AUTO_INCREMENT=42606 DEFAULT CHARSET=latin1 |

where I found references to 'utf8_general_ci' is in domains table
| domains | CREATE TABLE `domains` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `master` varchar(128) DEFAULT NULL,
  `last_check` int DEFAULT NULL,
  `type` varchar(6) NOT NULL,
  `notified_serial` int unsigned DEFAULT NULL,
  `account` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=latin1 |

Thanks in advance

jorge arenas quezada Aguascalientes, ags mexico jorgeaaq at yahoo.com 

    On Monday, October 19, 2020, 11:22:02 AM CDT, Gert van Dijk via Pdns-users <pdns-users at mailman.powerdns.com> wrote:  

 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

_______________________________________________
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/83479a1c/attachment.htm>