Nextcloud version : 14.0.4
Operating system and version : Centos 7
Apache or nginx version : 2.4.6
PHP version : 7.2.11
The issue you are facing:
I have 2 web servers running nextcloud with haproxy. We have 3 dbs Mariadb Cluster with transaction isolation READ-COMMITED and i am trying to setup maxscale to load balance write to master and slaves to read. The problem is that the nextcloud client that my users use for uploading the fies get a lot of errors File entry could not be inserted with insertIfNotExist() but could also not be selected with getId() in order to perform an update. Please try again
I understand that this happens because the client write to master and trying to read from slave something that is not written yet. If i wait a little and then try to force resync (from my desktop client) then that error stops.
I am wondering if this is problem on my configuration of maxscale or is it because nextcloud cannot work with maxscale anymore.
Steps to replicate it: Each time i put a new file to the folder that sync with desktop client with the above setup File entry could not be inserted with insertIfNotExist() but could also not be selected with getId() in order to perform an update. Please try again
apperas. After some time it sync it and everything works ok.
Over the last couple of months, there have been a few updates to the version you are using. They fixed also a couple of database stuff but I don’t know if this is for your type of problem as well. There is however a changelog:
Support for certain things can change between major versions. If you stay on a major version, minor updates should not break anything. And it is recommended to update within your major branch regularly.
I try the same setup in nextcloud 16. I have the same problem. The sync client have some errors like File entry could not be inserted with insertIfNotExist() but could also not be selected with getId() in order to perform an update. Please try again
and after i restart the sync of the desktop client then i sync it without problem. I am wondering if i make some wrong in my read write split.
If someone is still using maxscale write read, or sql proxy read write please let me know how he setup it and if he had the same issues.
I just switched from Haproxy to Proxysql and I get “File entry could not be inserted but could also not be selected with getId()” errors randomly too. Especially when using the initial preview generator command.
I also get some “MySQL_Session.cpp:4085:handler(): [WARNING] Error during query on (server_ip,3306,186): 1062, Duplicate entry ‘107389811’ for key ‘PRIMARY’” errors in Proxysql logs
I did setup my cluster as 1 writer and 4 backup writers and reads are routed to the 4 backup readers.
I guess some stale reads happen for no apparent reason.
Replying to myself : Maxscale (readwritesplit) + Galera multimaster + wsrep_sync_wait=3 works just fine.
Couldn’t figure out a way to correct the “File entry could not be inserted but could also not be selected with getId()” with proxysql. So far after 48h hours of intensive testing no errors on my nextcloud instance with maxscale …
The only point remaining to clear is the licensing of Maxscale. It’s not free but you’re allowed 2 free instances for pre-prod purposes.
It’s been running perfectly so far. No cluster failure in 9 monts which we’re occuring once a month with haproxy and no more “File entry could not be inserted but could also not be selected with getId()” error. I now have 2 maxscale running for redundancy
Watch out : the pricing if you go over the basic licence is fearsome if you’re not a big company.
Here is the maxscale config I use. You have to create the maxscale and maxscale-monitor accounts manually and give them rights on your databases.
# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25/
# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-25-mariadb-maxscale-configuration-guide/
[maxscale]
threads=auto
admin_host=0.0.0.0
admin_secure_gui=false
# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#
[MARIADB-1]
type=server
address=XXXXXXX
port=3306
protocol=MariaDBBackend
[MARIADB-5]
type=server
address=XXXXXXX
port=3306
protocol=MariaDBBackend
[MARIADB-2]
type=server
address=XXXXXXX
port=3306
protocol=MariaDBBackend
[MARIADB-3]
type=server
address=XXXXXXX
port=3306
protocol=MariaDBBackend
[MARIADB-4]
type=server
address=XXXXXXX
port=3306
protocol=MariaDBBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/maxscale-25-monitors/
[Galera-Monitor]
type=monitor
module=galeramon
servers=MARIADB-1, MARIADB-2, MARIADB-3, MARIADB-4, MARIADB-5
user=maxscale_monitor
password=XXXXXXX
monitor_interval=2000ms
#ip Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25-readconnroute/
#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave
# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-25-readwritesplit/
[Read-Write-Service]
type=service
router=readwritesplit
servers=MARIADB-1, MARIADB-2, MARIADB-3, MARIADB-4, MARIADB-5
user=maxscale
password=XXXXXXX
master_reconnection=true
master_failure_mode=fail_on_write
retry_failed_reads=true
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3306
