Hi All,
How to properly configure Nextcloud on PostgreSQL to support UTF8 4mb characters? Apparently Postgres supports it out of the box without any other mangling yet I’m facing errors with it.
After hours of searching, there are no good/working solutions available (well am sure there are - it’s just an error between keyboard and the screen at my end) - on how to fix it.
Problem shows up if trying to create and assign tag with for example Thai characters. In my case I’ve stumbled on it using Recognize Nextcloud app and have it tested with core Files app - same effect. It must be something around collation, etc. though again reading PostgreSQL related details seems like it supports it without additional configuration.
PostgreSQL side looks like this - please note that all names, etc. so far are in Latin/European language with no issues and only tags happen to have Thai characters requirement.
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
---------+---------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
1583431 | nextcloud | 10 | 6 | en_US.utf8 | en_US.utf8 | f | t | -1 | 13756 | 79398911 | 1 | 1663 |
Below is error when using Files app trying to set tag พระที่นั่งอาภรณ์ภิโมกข์ปราสาท
{
"reqId": "HuNlja3SzVB4G02mYpdp",
"level": 3,
"time": "2023-10-27T02:21:20+02:00",
"remoteAddr": "192.168.0.5",
"user": "dawid",
"app": "webdav",
"method": "POST",
"url": "/remote.php/dav/systemtags",
"message": "An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/118.0",
"version": "27.1.2.1",
"exception": {
"Exception": "Doctrine\\DBAL\\Exception\\DriverException",
"Message": "An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1",
"Code": 7,
"Trace": [
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
"line": 1814,
"function": "convert",
"class": "Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter",
"type": "->",
"args": [
[
"Doctrine\\DBAL\\Driver\\PDO\\Exception"
],
[
"Doctrine\\DBAL\\Query"
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
"line": 1749,
"function": "handleDriverException",
"class": "Doctrine\\DBAL\\Connection",
"type": "->",
"args": [
[
"Doctrine\\DBAL\\Driver\\PDO\\Exception"
],
[
"Doctrine\\DBAL\\Query"
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
"line": 1163,
"function": "convertExceptionDuringQuery",
"class": "Doctrine\\DBAL\\Connection",
"type": "->",
"args": [
[
"Doctrine\\DBAL\\Driver\\PDO\\Exception"
],
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(?, ?, ?)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/lib/private/DB/Connection.php",
"line": 295,
"function": "executeStatement",
"class": "Doctrine\\DBAL\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(?, ?, ?)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
"line": 354,
"function": "executeStatement",
"class": "OC\\DB\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(:dcValue1, :dcValue2, :dcValue3)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
"line": 280,
"function": "execute",
"class": "Doctrine\\DBAL\\Query\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/lib/private/SystemTag/SystemTagManager.php",
"line": 214,
"function": "execute",
"class": "OC\\DB\\QueryBuilder\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 216,
"function": "createTag",
"class": "OC\\SystemTag\\SystemTagManager",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 145,
"function": "createTag",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
"line": 89,
"function": "httpPost",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 472,
"function": "emit",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
"method:POST",
[
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 253,
"function": "invokeMethod",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 321,
"function": "start",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/Server.php",
"line": 365,
"function": "exec",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/appinfo/v2/remote.php",
"line": 35,
"function": "exec",
"class": "OCA\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/remote.php",
"line": 172,
"args": [
"/var/www/html/apps/dav/appinfo/v2/remote.php"
],
"function": "require_once"
}
],
"File": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php",
"Line": 91,
"Previous": {
"Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
"Message": "SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1",
"Code": 7,
"Trace": [
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
"line": 103,
"function": "new",
"class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
"type": "::",
"args": [
[
"PDOException",
[
"22021",
7,
"ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1"
]
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
"line": 1153,
"function": "execute",
"class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
"type": "->",
"args": []
},
{
"file": "/var/www/html/lib/private/DB/Connection.php",
"line": 295,
"function": "executeStatement",
"class": "Doctrine\\DBAL\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(?, ?, ?)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
"line": 354,
"function": "executeStatement",
"class": "OC\\DB\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(:dcValue1, :dcValue2, :dcValue3)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
"line": 280,
"function": "execute",
"class": "Doctrine\\DBAL\\Query\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/lib/private/SystemTag/SystemTagManager.php",
"line": 214,
"function": "execute",
"class": "OC\\DB\\QueryBuilder\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 216,
"function": "createTag",
"class": "OC\\SystemTag\\SystemTagManager",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 145,
"function": "createTag",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
"line": 89,
"function": "httpPost",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 472,
"function": "emit",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
"method:POST",
[
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 253,
"function": "invokeMethod",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 321,
"function": "start",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/Server.php",
"line": 365,
"function": "exec",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/appinfo/v2/remote.php",
"line": 35,
"function": "exec",
"class": "OCA\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/remote.php",
"line": 172,
"args": [
"/var/www/html/apps/dav/appinfo/v2/remote.php"
],
"function": "require_once"
}
],
"File": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
"Line": 30,
"Previous": {
"Exception": "PDOException",
"Message": "SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1",
"Code": "22021",
"Trace": [
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
"line": 101,
"function": "execute",
"class": "PDOStatement",
"type": "->",
"args": [
null
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Connection.php",
"line": 1153,
"function": "execute",
"class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
"type": "->",
"args": []
},
{
"file": "/var/www/html/lib/private/DB/Connection.php",
"line": 295,
"function": "executeStatement",
"class": "Doctrine\\DBAL\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(?, ?, ?)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
"line": 354,
"function": "executeStatement",
"class": "OC\\DB\\Connection",
"type": "->",
"args": [
"INSERT INTO \"oc_systemtag\" (\"name\", \"visibility\", \"editable\") VALUES(:dcValue1, :dcValue2, :dcValue3)",
[
null,
1,
1
],
[
2,
2,
2
]
]
},
{
"file": "/var/www/html/lib/private/DB/QueryBuilder/QueryBuilder.php",
"line": 280,
"function": "execute",
"class": "Doctrine\\DBAL\\Query\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/lib/private/SystemTag/SystemTagManager.php",
"line": 214,
"function": "execute",
"class": "OC\\DB\\QueryBuilder\\QueryBuilder",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 216,
"function": "createTag",
"class": "OC\\SystemTag\\SystemTagManager",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/apps/dav/lib/SystemTag/SystemTagPlugin.php",
"line": 145,
"function": "createTag",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
"*** sensitive parameters replaced ***"
]
},
{
"file": "/var/www/html/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
"line": 89,
"function": "httpPost",
"class": "OCA\\DAV\\SystemTag\\SystemTagPlugin",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 472,
"function": "emit",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
"method:POST",
[
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 253,
"function": "invokeMethod",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": [
[
"Sabre\\HTTP\\Request"
],
[
"Sabre\\HTTP\\Response"
]
]
},
{
"file": "/var/www/html/3rdparty/sabre/dav/lib/DAV/Server.php",
"line": 321,
"function": "start",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/lib/Server.php",
"line": 365,
"function": "exec",
"class": "Sabre\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/apps/dav/appinfo/v2/remote.php",
"line": 35,
"function": "exec",
"class": "OCA\\DAV\\Server",
"type": "->",
"args": []
},
{
"file": "/var/www/html/remote.php",
"line": 172,
"args": [
"/var/www/html/apps/dav/appinfo/v2/remote.php"
],
"function": "require_once"
}
],
"File": "/var/www/html/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
"Line": 101
}
},
"message": "An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1",
"exception": {},
"CustomMessage": "An exception occurred while executing a query: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xe0\nCONTEXT: unnamed portal parameter $1"
}
}