#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
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>
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" .....
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
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
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
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
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.
Marvin Nimnull
on
typo: "sqlite" should read as "mysql"
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?
Marvin Nimnull
on
I used Conversations v1.23.3 under Android 7.1.2
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
MattJ
on
Updating title for clarity.
Changes
titleAvatar upload with sql backend and mod_pep_plus is impossible MySQL: Avatar upload with sql backend and mod_pep_plus fails
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>
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" .....
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?
Changesmysql --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
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
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.
ChangesThis 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.
typo: "sqlite" should read as "mysql"
@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?
I used Conversations v1.23.3 under Android 7.1.2
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.
ChangesUpdating title for clarity.
ChangesAvatar upload with sql backend and mod_pep_plus is impossibleMySQL: Avatar upload with sql backend and mod_pep_plus failsnot working yet...
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