NEXTCLOUD : Error : SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock

46 x served & 6 x viewed

Sur NEXTCLOUD j’ai l’erreur : SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

Mes versions :

  • NEXTCLOUD : 20.0.1 ( command : sudo -u www-data php /var/www/html/nextcloud/occ –version )
  • MARIADB : 10.3.25-MariaDB-0+deb10u1 Debian 10
  • PHP : 7.3.19-1~deb10u1
  • OS : Debian 10

Quand j’essaye de voir ou est le problème :

MariaDB [(none)]> SHOW ENGINE INNODB STATUS;
...
| InnoDB |      | 
=====================================
2020-11-17 09:19:30 0x7fa224b77700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 461 srv_active, 0 srv_shutdown, 600286 srv_idle
srv_master_thread log flush and writes: 600676
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 98430
OS WAIT ARRAY INFO: signal count 85332
RW-shared spins 64094, rounds 1791314, OS waits 59076
RW-excl spins 22498, rounds 84684, OS waits 850
RW-sx spins 1924, rounds 35426, OS waits 904
Spin rounds per wait: 27.95 RW-shared, 3.76 RW-excl, 18.41 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-11-14 16:45:09 0x7fa23828d700
*** (1) TRANSACTION:
TRANSACTION 506754, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 3
MySQL thread id 33716, OS thread handle 140334704551680, query id 602553 localhost farias Updating
UPDATE `oc_file_locks` SET `lock` = `lock` - '1' WHERE (`key` IN ('files/f3c7b68359c596992199b90449d7ff8c', 'files/5a2eaa89d932ecbb1dfec1006109244b', 'files/b34c1fc8665aefe70b8e44d4ac09aa67', 'files/994269d0fc31eeda97e0469ed6ab8da3', 'files/d6a2d20758f30740cbecca7cc8b13233', 'files/8853c6e699dd4e7e2655f41918e5a1d2', 'files/3320d1d40cdf4b94707b61cda6ecae0b', 'files/2af4fbefe05ca8c38046394574650bed', 'files/c5f55069bcd203aaa95e4290aabd8c8b', 'files/5aa7bb013dd77e1cdc270e19760a6a23', 'files/d4be38ed80c9b1bf63e3bf45937fb8cd', 'files/ecaafc552486d9c983da23ce46dd72eb', 'files/bbeee26a3ecfeb7ea06dc35abfdff76b', 'files/69f5b0b8e324f92f590b6211eb6c96a1', 'files/5f57699c6ab4f6f04e2c1649ded34919', 'files/c122984832ebf9f0484fd06b33caf6ca', 'files/7c240806d390dc989d67aeb310ce09ac', 'files/d183dc061d795de42af22fbdac8d9ace', 'files/64149897c3515f6526f20690d6cdaa86', 'files/2cd2b02ce9f36f5a067cf8d8e205f34e', 'files/bac5d6b465c
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 53 n bits 232 index lock_key_index of table `nextclouddb`.`oc_file_locks` trx id 506754 lock_mode X locks rec but not gap waiting
Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 66696c65732f356132656161383964393332656362623164666563313030; asc files/5a2eaa89d932ecbb1dfec100; (total 38 bytes);
 1: len 8; hex 0000000000004b43; asc       KC;;

*** (2) TRANSACTION:
TRANSACTION 506747, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 1128, 16 row lock(s), undo log entries 6
MySQL thread id 33717, OS thread handle 140334703630080, query id 602561 localhost farias Update
INSERT INTO `oc_file_locks` (`key`, `lock`, `ttl`) VALUES('files/3320d1d40cdf4b94707b61cda6ecae0b', '1', '1605372309')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 39 page no 53 n bits 232 index lock_key_index of table `nextclouddb`.`oc_file_locks` trx id 506747 lock mode S
Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 66696c65732f356132656161383964393332656362623164666563313030; asc files/5a2eaa89d932ecbb1dfec100; (total 38 bytes);
 1: len 8; hex 0000000000004b43; asc       KC;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 39 page no 39 n bits 352 index lock_key_index of table `nextclouddb`.`oc_file_locks` trx id 506747 lock mode S waiting
Record lock, heap no 111 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 66696c65732f333332306431643430636466346239343730376236316364; asc files/3320d1d40cdf4b94707b61cd; (total 38 bytes);
 1: len 8; hex 0000000000004b4b; asc       KK;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 546878
Purge done for trx's n:o < 546878 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421809682043144, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421809682038928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2613 OS file reads, 488009 OS file writes, 304247 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 41 merges
merged operations:
 insert 43, delete mark 724, delete 8
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 8 buffer(s)
Hash table size 34679, node heap has 7 buffer(s)
Hash table size 34679, node heap has 10 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 536481738
Log flushed up to   536481738
Pages flushed up to 536481738
Last checkpoint at  536481729
0 pending log flushes, 0 pending chkp writes
211673 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 170655744
Dictionary memory allocated 615072
Buffer pool size   8192
Free buffers       4922
Database pages     2746
Old database pages 993
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3994, not young 1406
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2351, created 395, written 247428
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2746, unzip_LRU len: 1013
I/O sum[42]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=645, Main thread ID=140333945968384, state: sleeping
Number of rows inserted 14499, updated 74567, deleted 5768, read 9673725
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 18326
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Je n’arrive pas à trouver le problème.

Misère.

 

NextCloud : Ajouter un certificat SSL sur Apache.

49 x served & 7 x viewed

Ma procédure :

Etape 1 : Ajouter les bons packages :

$ apt-get update && apt-get install certbot python-certbot-apache

Etape 2 : Demande du certificat :

$ certbot --apache -w /var/www/html/nextcloud/ -d my.cyber-neurones.org
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator apache, Installer apache
Obtaining a new certificate
Performing the following challenges:
http-01 challenge for my.cyber-neurones.org
Waiting for verification...
Cleaning up challenges
Created an SSL vhost at /etc/apache2/sites-available/nextcloud-le-ssl.conf
Enabled Apache ssl module
Deploying Certificate to VirtualHost /etc/apache2/sites-available/nextcloud-le-ssl.conf
Enabling available site: /etc/apache2/sites-available/nextcloud-le-ssl.conf

Please choose whether or not to redirect HTTP traffic to HTTPS, removing HTTP access.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1: No redirect - Make no further changes to the webserver configuration.
2: Redirect - Make all requests redirect to secure HTTPS access. Choose this for
new sites, or if you're confident your site works on HTTPS. You can undo this
change by editing your web server's configuration.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Select the appropriate number [1-2] then [enter] (press 'c' to cancel): 1

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Congratulations! You have successfully enabled https://my.cyber-neurones.org

You should test your configuration at:
https://www.ssllabs.com/ssltest/analyze.html?d=my.cyber-neurones.org
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/my.cyber-neurones.org/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/my.cyber-neurones.org/privkey.pem
   Your cert will expire on 2021-01-31. To obtain a new or tweaked
   version of this certificate in the future, simply run certbot again
   with the "certonly" option. To non-interactively renew *all* of
   your certificates, run "certbot renew"
 - If you like Certbot, please consider supporting our work by:

   Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
   Donating to EFF:                    https://eff.org/donate-le


Etape 3 : Restart de apache :

$ /etc/init.d/apache2 restart
[ ok ] Restarting apache2 (via systemctl): apache2.service

Rien de plus simple …

Nextcloud : Les problèmes sur mon installation

141 x served & 56 x viewed

Je viens de voir qu’il y avait quelques problèmes sur mon installation :

1- Manque d’un index :

# sudo -u www-data php /usr/share/nginx/nextcloud/occ db:add-missing-indices
Check indices of the share table.
Check indices of the filecache table.
Check indices of the twofactor_providers table.
Check indices of the login_flow_v2 table.
Check indices of the whats_new table.
Check indices of the cards table.
Check indices of the cards_properties table.
Check indices of the calendarobjects_props table.
Check indices of the schedulingobjects table.
Check indices of the oc_properties table.
Adding properties_path_index index to the oc_properties table, this can take some time...
oc_properties table updated successfully.

2- Manque d’une colonne :

sudo -u www-data php /usr/share/nginx/nextcloud/occ db:add-missing-columns
Check columns of the comments table.
Adding additional reference_id column to the comments table, this can take some time...
Comments table updated successfully.

3- Passage de PHP 7.2 en PHP 7.3 :

# sudo add-apt-repository ppa:ondrej/php
...
# sudo apt-get update
...
# sudo apt install php7.3
....
Les paquets supplémentaires suivants seront installés : 
libapache2-mod-php7.3 libpcre2-8-0 php-common php7.3-cli php7.3-common php7.3-json php7.3-opcache
php7.3-readline
Paquets suggérés :
php-pear
Les NOUVEAUX paquets suivants seront installés :
libapache2-mod-php7.3 libpcre2-8-0 php7.3 php7.3-cli php7.3-common php7.3-json php7.3-opcache
php7.3-readline
...
# sudo apt install php7.3-common php7.3-cli php7.3-bcmath php7.3-bz2 php7.3-curl php7.3-gd php7.3-intl php7.3-json php7.3-mbstring php7.3-readline php7.3-xml php7.3-zip php7.3-fpm
...
NOTICE: Not enabling PHP 7.3 FPM by default.
NOTICE: To enable PHP 7.3 FPM in Apache2 do:
NOTICE: a2enmod proxy_fcgi setenvif
NOTICE: a2enconf php7.3-fpm
NOTICE: You are seeing this message because you have apache2 package installed.

# sudo apt-get install php7.3-mysql php7.3-imagick php7.3-recode php7.3-tidy php7.3-xmlrpc

# sudo a2dismod php7.2
Module php7.2 disabled.
To activate the new configuration, you need to run:
systemctl restart apache2
# sudo a2enmod php7.3
Considering dependency mpm_prefork for php7.3:
Considering conflict mpm_event for mpm_prefork:
Considering conflict mpm_worker for mpm_prefork:
Module mpm_prefork already enabled
Considering conflict php5 for php7.3:
Enabling module php7.3.
To activate the new configuration, you need to run:
systemctl restart apache2
# sudo systemctl restart apache2
# sudo a2enmod proxy_fcgi setenvif
Considering dependency proxy for proxy_fcgi:
Enabling module proxy.
Enabling module proxy_fcgi.
Module setenvif already enabled
To activate the new configuration, you need to run:
systemctl restart apache2
$ sudo a2enconf php7.3-fpm
Enabling conf php7.3-fpm.
To activate the new configuration, you need to run:
systemctl reload apache2
$ php -v
PHP 7.3.21-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Aug 7 2020 14:44:10) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.21, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.21-1+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

4- Ajout du module PHP bcmath :

# sudo apt install php7.3-bcmath

5 – Ajout du module PHP gmp :

# sudo apt install php7.3-gm

6 – Check et start de php7.3-fpm :

# ls -l /run/php/php7.*
-rw-r--r-- 1 root     root     4 août  24 13:07 /run/php/php7.2-fpm.pid
srw-rw---- 1 www-data www-data 0 août  24 13:07 /run/php/php7.2-fpm.sock
-rw-r--r-- 1 root     root     5 août  31 11:26 /run/php/php7.3-fpm.pid
srw-rw---- 1 www-data www-data 0 août  31 11:26 /run/php/php7.3-fpm.sock

#  sudo systemctl start php7.3-fpm

#  sudo systemctl enable php7.3-fpm

# diff /etc/php/7.3/fpm/php.ini /etc/php/7.2/fpm/php.ini  | grep -v "< ;" | grep -v "> ;" | grep -v "\-\-\-"
...
< output_buffering = 4096 > output_buffering = 8192
...
< max_execution_time = 30 > max_execution_time = 600
...
< max_input_time = 60 > max_input_time = 600
...
< memory_limit = 128M > memory_limit = 2048M
...
< display_errors = Off > display_errors = On
...
< display_startup_errors = Off > display_startup_errors = On
...
< log_errors_max_len = 1024 > log_errors_max_len = 4024
...
< ignore_repeated_errors = Off > ignore_repeated_errors = On
...
< post_max_size = 8M > post_max_size = 20M
...
< upload_max_filesize = 2M > upload_max_filesize = 40M
...
< max_file_uploads = 20 > max_file_uploads = 100
...
< default_socket_timeout = 60 > default_socket_timeout = 600
...
< session.cache_expire = 180 > session.cache_expire = 320


7 – Modification dans NextCloud :

Fichier /etc/nginx/conf.d/nextcloud-local.conf et /etc/nginx/conf.d/nextcloud.conf :

       #fastcgi_pass unix:/run/php/php7.2-fpm.sock;
       fastcgi_pass unix:/run/php/php7.3-fpm.sock;

NextCloud & NGinx : server reached pm.max_children setting (5), consider raising it

147 x served & 20 x viewed

J’ai eu l’erreur : « server reached pm.max_children setting (5), consider raising it » dans les fichiers de logs /var/log/php7.2-fpm.log.

Mes fichiers de logs pour Nextcloud ( configuration via NGinx ) :

– /var/log/php7.2-fpm.log : PHP .
– /var/log/nginx/nextcloud.error & /var/log/nginx/nextcloud.access : NGINX .
– /var/log/nextcloud.log : Nextcloud.
– /usr/share/nginx/nextcloud/data/nextcloud.log : Nextcloud.
– /usr/share/nginx/nextcloud/data/audit.log : Nextcloud.
– /usr/share/nginx/nextcloud/data/updater.log : Nextcloud.

Pour supprimer l’erreur j’ai modifié le fichier de configuration :

# cat /etc/php/7.2/fpm/pool.d/www.conf | grep -v "^;" | grep -v "^$"
[www]
user = www-data
group = www-data
listen = /run/php/php7.2-fpm.sock
listen.owner = www-data
listen.group = www-data
pm = dynamic
pm.max_children = 50 
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 10
env[PATH] = /usr/local/bin:/usr/bin:/bin

Je suis donc passé de 5 à 50 … je pense que le problème ne devrait plus apparaitre.

A noter aussi que j’ai modifié le système de cron afin que cela soit plus performant :

$ crontab -u www-data -l
*/5  *  *  *  * php -f /usr/share/nginx/nextcloud/cron.php