#1073 MySQL: Avatar upload with sql backend and mod_pep_plus fails

Reporter Marvin Nimnull
Owner MattJ
Created
Updated
Stars ★ (1)
Tags
  • Priority-Medium
  • Type-Defect
  • Status-Fixed
  • Milestone-0.11
  1. Marvin Nimnull on

    What steps will reproduce the problem? 1. Install prosody, enable sql backend, enable mod_pep_plus module 2. Add necessary users 3. Connect with newly created user, try to change self avatar image What is the expected output? Avatar should change What do you see instead? Client hangs in "publishing" state (server-side errors logs are filled with SQL and lua exceptions) What version of the product are you using? On what operating system? latest version from trunk - changeset: 8499:549361c68f5a on FreeBSD 11.1-RELEASE-p6 Please provide any additional information below. an 27 21:56:55 sql error Error in SQL transaction: /lib/prosody/util/sql.lua:164: Error executing statement parameters: Duplicate entry 'XXXXXX.XXX-ava-pep_urn:xmpp:avatar:-a56c45d89d635a78385d' for key 'prosodyarchive_index' Jan 27 21:56:55 XXXXXX.XXX:pep_plus error Unable to set item: /lib/prosody/util/sql.lua:164: Error executing statement parameters: Duplicate entry 'XXXXXX.XXX-ava-pep_urn:xmpp:avatar:-a56c45d89d635a78385d' for key 'prosodyarchive_index' Jan 27 21:56:55 c2s8040a4d80 error Traceback[c2s]: .../lib/prosody/modules/mod_pubsub/pubsub.lib.lua:31: bad argument #1 to 't_unpack' (table expected, got nil) stack traceback: [C]: in function 't_unpack' .../lib/prosody/modules/mod_pubsub/pubsub.lib.lua:31: in function 'pubsub_error_reply' .../lib/prosody/modules/mod_pubsub/pubsub.lib.lua:308: in function 'handler' .../lib/prosody/modules/mod_pubsub/pubsub.lib.lua:118: in function <.../lib/prosody/modules/mod_pubsub/pubsub.lib.lua:105> (tail call): ? /lib/prosody/util/events.lua:78: in function </lib/prosody/util/events.lua:74> (tail call): ? (tail call): ? ...home/ava/prosod/lib/prosody/modules/mod_iq.lua:38: in function '?' /lib/prosody/util/events.lua:78: in function </lib/prosody/util/events.lua:74> (tail call): ? .../lib/prosody/core/stanza_router.lua:190: in function 'core_post_stanza' .../lib/prosody/core/stanza_router.lua:137: in function 'core_process_stanza' .../lib/prosody/modules/mod_c2s.lua:263: in function 'func' /lib/prosody/util/async.lua:92: in function </lib/prosody/util/async.lua:90>

  2. Marvin Nimnull on

    Table index is defined here: grep -n prosodyarchive_index /prosody-hg/plugins/mod_storage_sql.lua 482: Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" }; I think that "insert" statement should be changed for "replace" .....

  3. Zash on

    REPLACE is not portable SQL and can't be used. It is issuing a DELETE statement in the same transaction, this error should not be possible. Which database engine is used?

    Changes
    • tags Status-NeedInfo
  4. Marvin Nimnull on

    mysql --version mysql Ver 14.14 Distrib 5.5.59, for FreeBSD11.1 (amd64) using readline 7.0 grep -i mysql prosody.cfg.lua sql = { driver = "MySQL", database = "prosody", username = "XXXXX", password = "XXXXX", host = "localhost" } if you will provide me with info on how to obtain additional tracing information I will upload it too. here goes the mysql structure of the database: -- MySQL dump 10.13 Distrib 5.5.59, for FreeBSD11.1 (amd64) -- -- Host: localhost Database: prosody -- ------------------------------------------------------ -- Server version 5.5.59 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `prosody` -- DROP TABLE IF EXISTS `prosody`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `prosody` ( `host` text COLLATE utf8mb4_bin NOT NULL, `user` text COLLATE utf8mb4_bin NOT NULL, `store` text COLLATE utf8mb4_bin NOT NULL, `key` text COLLATE utf8mb4_bin NOT NULL, `type` text COLLATE utf8mb4_bin NOT NULL, `value` mediumtext COLLATE utf8mb4_bin NOT NULL, KEY `prosody_index` (`host`(20),`user`(20),`store`(20),`key`(20)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `prosody` -- LOCK TABLES `prosody` WRITE; /*!40000 ALTER TABLE `prosody` DISABLE KEYS */; ................ /*!40000 ALTER TABLE `prosody` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `prosodyarchive` -- DROP TABLE IF EXISTS `prosodyarchive`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `prosodyarchive` ( `sort_id` int(11) NOT NULL AUTO_INCREMENT, `host` text COLLATE utf8mb4_bin NOT NULL, `user` text COLLATE utf8mb4_bin NOT NULL, `store` text COLLATE utf8mb4_bin NOT NULL, `key` text COLLATE utf8mb4_bin NOT NULL, `when` int(11) NOT NULL, `with` text COLLATE utf8mb4_bin NOT NULL, `type` text COLLATE utf8mb4_bin NOT NULL, `value` mediumtext COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`sort_id`), UNIQUE KEY `prosodyarchive_index` (`host`(20),`user`(20),`store`(20),`key`(20)) ) ENGINE=MyISAM AUTO_INCREMENT=314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `prosodyarchive` -- LOCK TABLES `prosodyarchive` WRITE; /*!40000 ALTER TABLE `prosodyarchive` DISABLE KEYS */; .............. /*!40000 ALTER TABLE `prosodyarchive` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-01-30 23:29:13

  5. Marvin Nimnull on

    ok, I got full sql tracing: general_log = on general_log_file = /var/db/mysql/sql.log log_output = file /usr/local/libexec/mysqld, Version: 5.5.59-log (Source distribution). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument 180131 0:06:56 1 Connect prosodyusr@localhost on prosody 1 Query SET NAMES utf8mb4 1 Query set autocommit=0 1 Query set autocommit=0 1 Prepare SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1 1 Execute SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1 1 Close stmt 1 Query commit 1 Prepare SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin' 1 Execute SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin' 1 Query commit 1 Prepare SHOW SESSION VARIABLES LIKE 'character_set_client' 1 Execute SHOW SESSION VARIABLES LIKE 'character_set_client' 1 Close stmt 1 Query commit 1 Prepare CREATE TABLE `prosody` (`host` TEXT NOT NULL, `user` TEXT NOT NULL, `store` TEXT NOT NULL, `key` TEXT NOT NULL, `type` TEXT NOT NULL, `value` MEDIUMTEXT NOT NULL) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' 1 Query commit 1 Prepare CREATE TABLE `prosodyarchive` (`sort_id` INTEGER PRIMARY KEY AUTO_INCREMENT, `host` TEXT NOT NULL, `user` TEXT NOT NULL, `store` TEXT NOT NULL, `key` TEXT NOT NULL, `when` INTEGER NOT NULL, `with` TEXT NOT NULL, `type` TEXT NOT NULL, `value` MEDIUMTEXT NOT NULL) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_bin' 1 Query commit 1 Prepare SHOW COLUMNS FROM `prosody` WHERE `Field`='value' and `Type`='text' 1 Execute SHOW COLUMNS FROM `prosody` WHERE `Field`='value' and `Type`='text' 1 Query commit 1 Prepare SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` FROM `information_schema`.`columns` WHERE `TABLE_NAME` LIKE 'prosody%%' AND `TABLE_SCHEMA` = ? AND ( `CHARACTER_SET_NAME`!=? OR `COLLATION_NAME`!=?) 1 Execute SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` FROM `information_schema`.`columns` WHERE `TABLE_NAME` LIKE 'prosody%%' AND `TABLE_SCHEMA` = 'prosody' AND ( `CHARACTER_SET_NAME`!='utf8mb4' OR `COLLATION_NAME`!='utf8mb4_bin') 1 Query commit 1 Execute SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` FROM `information_schema`.`columns` WHERE `TABLE_NAME` LIKE 'prosody%%' AND `TABLE_SCHEMA` = 'prosody' AND ( `CHARACTER_SET_NAME`!='utf8mb4' OR `COLLATION_NAME`!='utf8mb4_bin') 1 Query commit 1 Prepare DELETE FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=? 1 Execute DELETE FROM `prosodyarchive` WHERE `host`='XXXXXXXX.XXX' AND `user`='XXX' AND `store`='pep_urn:xmpp:avatar:data' AND `key`='bc592bf4bb5113d733278d4680b0cca4ef4227fe' 1 Prepare INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?) 1 Execute INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES ('XXXXXXXX.XXX','XXX','pep_urn:xmpp:avatar:data',1517346416,'','bc592bf4bb5113d733278d4680b0cca4ef4227fe','xml','<data ... /data>') 1 Query commit 1 Execute DELETE FROM `prosodyarchive` WHERE `host`='XXXXXXXX.XXX' AND `user`='XXX' AND `store`='pep_urn:xmpp:avatar:metadata' AND `key`='bc592bf4bb5113d733278d4680b0cca4ef4227fe' 1 Query rollback

  6. Zash on

    Is this still an issue? I'm not sure what to make of that log. It deletes and inserts as it should. Then it deletes the same item but aborts the transaction. I don't see how that would lead to a duplicated item, as the error in the first post says.

    Changes
    • tags Milestone-0.11
  7. Marvin Nimnull on

    This is the system of my friend, he asked me to help him with the configuration of Prosody. Currently I don't have access to the system. Of course if I was able to solve the problem myself then I been solved it and uploaded "ready to use solution". I think that here can be incompatibility problems or bugs inside libraries or in the FFI layers of Lua and/or sqlite. After all the patches at FreeBSD port targets incompatibility with Posix library and e.t.c. Here can be the similar situation.

  8. Marvin Nimnull on

    typo: "sqlite" should read as "mysql"

  9. MattJ on

    @Marvin Thanks for all the details you've provided so far. I think the only we'll be able to make sense of this is if we can reproduce the issue ourselves. What client do you use to set the avatar?

  10. Marvin Nimnull on

    I used Conversations v1.23.3 under Android 7.1.2

  11. MattJ on

    I can reproduce this, and have a fix that I am testing. Will hopefully have an update shortly. The problem is MySQL only using a fixed-length prefix for the index, which has a uniqueness constraint.

    Changes
    • owner MattJ
    • tags Status-Started
  12. MattJ on

    Updating title for clarity.

    Changes
    • title Avatar upload with sql backend and mod_pep_plus is impossible MySQL: Avatar upload with sql backend and mod_pep_plus fails
  13. Jose on

    not working yet...

  14. MattJ on

    Fixed in https://hg.prosody.im/trunk/rev/55b40f3fa659 Requires a schema upgrade, I posted more details here: https://groups.google.com/d/msg/prosody-users/fjICJLBJ53c/60oUjwV6AwAJ Summary: after upgrade run: prosodyctl mod_storage_sql upgrade

    Changes
    • tags Status-Fixed

New comment

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