#1665 SQL statements not compliant with Percona

Reporter Georg
Owner Nobody
Stars ★★ (2)
  • Status-Invalid
  • Priority-Medium
  • Type-Enhancement
  1. Georg on

    What steps will reproduce the problem? 1. Set up MySQL database connection to a Percona XtraDB Cluster 2. Attempt to use `prosodyctl adduser` What is the expected output? Prosody should execute the SQL statements without producing any errors, initialize the database and create the users. What do you see instead? `sql error Error in SQL transaction: /usr/lib64/prosody/util/sql.lua:163: Error preparing statement handle: Percona-XtraDB-Cluster prohib its use of DML command on a table (prosody.prosody) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER /usr/lib64/prosody/util/sql.lua:163: Error preparing statement handle: Percona-XtraDB-Cluster prohibits use of DML command on a table (prosody.prosody) withou t an explicit primary key with pxc_strict_mode = ENFORCING or MASTER` What version of the product are you using? On what operating system? Prosody 0.11.8 on openSUSE 15.3 Please provide any additional information below. I worked around by manually adding an `id` column: `mysql> ALTER TABLE prosody ADD id INT PRIMARY KEY AUTO_INCREMENT;` In my case it seems to work after the manual intervention, however I do not know, whether I am in risk of Prosody possibly getting in conflict with the additional column or if it will overwrite it in the future. A clarification on this would be much appreciated. Furthermore, a user should not be lead to dangerously disabling the ENFORCING mode in Percona XtraDB. This is unfortunately often suggested on the internet as a "solution" to the SQL error produced by Percona.

  2. Zash on

    Thanks for the report. Prosody does not support Percona. Since you found a workaround already, let's leave it at that.

    • tags Status-Invalid
  3. PICCORO Lenz McKAY on

    errr sorry Zash but it seems that a table has no good structure.. this nothign to do with "not supported" product.. this is the way of open collaboration? just a simple conditional at least to use percona?

  4. MattJ on

    Prosody officially supports Postgres, MySQL and SQLite. Just supporting and testing these three is a lot of work for us. We have even considered dropping support for MySQL in the past because of the amount of work it has caused us. It's not a bug in Prosody if it fails to work with an unsupported database. We're not considering adding support for more databases at this time - though such support can be achieved through community modules. Steering an open-source project on a path that enables us to sustainably maintain and test it is not against the "way of open collaboration". Indeed, we ensure Prosody is extremely extensible so that people who need additional features not provided by the core project have an easy path to do so. I hope this helps to explain our perspective :)

New comment

Not published. Used for spam prevention and optional update notifications.