search

pulsejet / memories

Fast, modern and advanced photo management suite. Runs as a Nextcloud app.
https://memories.gallery
GNU Affero General Public License v3.0
3.22k stars 87 forks source link

memories:places-setup causes MariaDB crash #535

Closed jacotec closed 1 year ago

jacotec commented 1 year ago

Describe the bug Running ./occ memories:places-setup causes MariaDB segfault crash

mariadbd[2464898]: 230331 9:31:57 [ERROR] mysqld got signal 11 ;

Happens on all different 3 instances with MariaDB 10.6 and MariaDB 10.11.

To Reproduce Run memories:places-setup. It runs for a while and crashes MariaDB usually above 500k entries done.

Screenshots

image

Platform (please complete the following information):

Additional context

One cloud server hat places already set up, but as the "places" feature did not work anymore I've tried to do the setup again. That basically means that the setup ran fine in the past and got broken at some point.

Also, I did not intentionally install an alpha version. The NC update to 26 pulled it.

The crash happens randomly at different stages of the import. On one NC server it finished in the 6th attempt after 5 runs with DB crash caused.

On the other servers it worked after stopping php-fpm and apache before running the occ command.

Important: Are all of the following are true?

Yes

vitis586 commented 1 year ago

I was just about to create this issue. I was postponing setting the places up but I am now having the same error as @jacotec.

... Inserted 127500 places, 3863.6/s, ETA: 131s, Last: Coiserette Failed to insert into database An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away In ExceptionConverter.php line 101: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away In Exception.php line 26: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away In Statement.php line 92: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I hit the error a bit earlier at 127500 places. I tried it twice two days ago and I got back to it today and I am getting:

Attempting to set up reverse geocoding

Database table prefix is set to 'oc_' If the planet can be imported, it will not use this prefix The table will be named "memories_planet_geometry" This is necessary for using custom database extensions

MySQL-like support detected!

Database is already set up This will drop and re-download the planet database This is generally not necessary to do frequently

Are you sure you want to download the planet database? This will take a very long time and use some disk space in /tmp Proceed? [y/N] An unhandled exception has been thrown: TypeError: trim(): Argument #1 ($string) must be of type string, bool given in /config/www/nextcloud/apps/memories/lib/Command/PlacesSetup.php:102 Stack trace:

0 /config/www/nextcloud/apps/memories/lib/Command/PlacesSetup.php(102): trim()

1 /config/www/nextcloud/apps/recognize/vendor/symfony/console/Command/Command.php(298): OCA\Memories\Command\PlacesSetup->execute()

2 /config/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(1040): Symfony\Component\Console\Command\Command->run()

3 /config/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(301): Symfony\Component\Console\Application->doRunCommand()

4 /config/www/nextcloud/apps/recognize/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun()

5 /config/www/nextcloud/lib/private/Console/Application.php(214): Symfony\Component\Console\Application->run()

6 /config/www/nextcloud/console.php(100): OC\Console\Application->run()

7 /config/www/nextcloud/occ(11): require_once('...')

I am running Memories 4.12.5 on Nextcloud 25.0.5.

pulsejet commented 1 year ago

@jacotec can you try reducing the 500 here to a lower value (say 50) and see if the error persists? https://github.com/pulsejet/memories/blob/552a6137783219b74a70dea5514bb6b5b315ccbe/lib/Command/PlacesSetup.php#L276

@vitis586 you need an interactive TTY. With docker exec, make sure you have -it

vitis586 commented 1 year ago

Of course I didn't have the interactive TTY. I have it working now!

I tried lowering the count and it worked with up to 250. 300 was too much and failed again.

I am not sure what affects this. Is it tide to performance? I am on MariaDB 10.6.12 (might be the same as one of @jacotec's instances), the database is on SSDs and CPU is Ryzen 5 5650GE.

pulsejet commented 1 year ago

Not sure either, but 500 is probably overkill anyway. 100 should be good enough. The whole point of transactions is faster insertions anyway, which is a one-time process. So we aren't losing much.

romprod commented 1 year ago

I'm having the same issue as this still although I'm using version 5.05 on a new clean install of NextCloud 26.01 in a docker container. I'm using MariaDB 10.11.2

memories/lib/Command/PlacesSetup.php only has 103 lines and there's no mention of if (0 === $count % 500) { or 100

pulsejet commented 1 year ago

@romprod can you try reducing the count at https://github.com/pulsejet/memories/blob/281e431965b79a92718b5050043cbde0a7433bf0/lib/Service/Places.php#L302

ghost commented 1 year ago

@pulsejet I get the same issue on NC 27.0.0 Runing maria DB 10.6.12

root@nextcloud:~# sudo -u www-data php /var/www/nextcloud/occ memories:places-setup Attempting to set up reverse geocoding Database support was detected Download planet data to temporary file... Inserting planet data into database... Inserted 500 / 635189 places (0.1%), Last: Val-d''Oise Inserted 1000 / 635189 places (0.2%), Last: Hyvinkää Inserted 1500 / 635189 places (0.2%), Last: Mandanici Inserted 2000 / 635189 places (0.3%), Last: Sueddi/Suelli Inserted 2500 / 635189 places (0.4%), Last: Orune Inserted 3000 / 635189 places (0.5%), Last: Luras Inserted 3500 / 635189 places (0.6%), Last: Nemi Inserted 4000 / 635189 places (0.6%), Last: Otricoli Inserted 4500 / 635189 places (0.7%), Last: Camerata Picena Inserted 5000 / 635189 places (0.8%), Last: Cogorno Inserted 5500 / 635189 places (0.9%), Last: Cardè Inserted 6000 / 635189 places (0.9%), Last: Solarolo Rainerio Inserted 6500 / 635189 places (1%), Last: Veronella Inserted 7000 / 635189 places (1.1%), Last: Fara Olivana con Sola Inserted 7500 / 635189 places (1.2%), Last: Aicurzio Inserted 8000 / 635189 places (1.3%), Last: Vertova Inserted 8500 / 635189 places (1.3%), Last: Lavis Inserted 9000 / 635189 places (1.4%), Last: Stallhofen Inserted 9500 / 635189 places (1.5%), Last: Briastre Inserted 10500 / 635189 places (1.7%), Last: Landkreis Germersheim Inserted 11000 / 635189 places (1.7%), Last: Reichstett Inserted 11500 / 635189 places (1.8%), Last: Barbentane Inserted 12000 / 635189 places (1.9%), Last: Gemeinde Weiler Inserted 12500 / 635189 places (2%), Last: Gemeinde Tschagguns Inserted 13000 / 635189 places (2%), Last: Gemeinde Oberhofen im Inntal Inserted 13500 / 635189 places (2.1%), Last: Sainte-Croix-aux-Mines Inserted 14000 / 635189 places (2.2%), Last: Loupes Inserted 14500 / 635189 places (2.3%), Last: Bommes Inserted 15500 / 635189 places (2.4%), Last: Ay-sur-Moselle Inserted 16000 / 635189 places (2.5%), Last: Puget Inserted 16500 / 635189 places (2.6%), Last: Margaret Inserted 17000 / 635189 places (2.7%), Last: Longré Inserted 17500 / 635189 places (2.8%), Last: Firestone Inserted 18000 / 635189 places (2.8%), Last: Pedro Vicente Maldonado Inserted 18500 / 635189 places (2.9%), Last: Montmagny Inserted 19000 / 635189 places (3%), Last: Port Richey Inserted 19500 / 635189 places (3.1%), Last: Avalon Inserted 20000 / 635189 places (3.1%), Last: Wadley Inserted 20500 / 635189 places (3.2%), Last: Soda Springs Inserted 21000 / 635189 places (3.3%), Last: Ayat-sur-Sioule Inserted 21500 / 635189 places (3.4%), Last: Aigurande Inserted 22000 / 635189 places (3.5%), Last: Guer Inserted 22500 / 635189 places (3.5%), Last: La Voulte-sur-Rhône Inserted 23000 / 635189 places (3.6%), Last: Prairie City Inserted 23500 / 635189 places (3.7%), Last: Crane Inserted 24000 / 635189 places (3.8%), Last: Augnax Inserted 24500 / 635189 places (3.9%), Last: Lamont Inserted 25000 / 635189 places (3.9%), Last: Nantoux Inserted 25500 / 635189 places (4%), Last: Glasco Inserted 26000 / 635189 places (4.1%), Last: Mount Washington Inserted 26500 / 635189 places (4.2%), Last: Gemeinde Sankt Georgen am Ybbsfelde Inserted 27000 / 635189 places (4.3%), Last: Saint-Antonin Inserted 27500 / 635189 places (4.3%), Last: Canton Inserted 28000 / 635189 places (4.4%), Last: Villers-sur-Mer Inserted 28500 / 635189 places (4.5%), Last: Forada Inserted 29000 / 635189 places (4.6%), Last: Le Roy Inserted 29500 / 635189 places (4.6%), Last: Ghyvelde Inserted 30000 / 635189 places (4.7%), Last: Kingsville Inserted 30500 / 635189 places (4.8%), Last: Owensville Inserted 31000 / 635189 places (4.9%), Last: Plevna Inserted 31500 / 635189 places (5%), Last: Veauville-lès-Quelles Inserted 32000 / 635189 places (5%), Last: Vieux-Vy-sur-Couesnon Inserted 32500 / 635189 places (5.1%), Last: Beverungen Inserted 33000 / 635189 places (5.2%), Last: Tigy Inserted 33500 / 635189 places (5.3%), Last: Franqueville Inserted 34000 / 635189 places (5.4%), Last: Longecourt-en-Plaine Inserted 34500 / 635189 places (5.4%), Last: Cervon Inserted 35000 / 635189 places (5.5%), Last: Varzy Inserted 35500 / 635189 places (5.6%), Last: Reken Inserted 36000 / 635189 places (5.7%), Last: Ercé-en-Lamée Inserted 36500 / 635189 places (5.7%), Last: Çaro Inserted 37000 / 635189 places (5.8%), Last: La Vista Inserted 37500 / 635189 places (5.9%), Last: Laurel Springs Inserted 38000 / 635189 places (6%), Last: Chuzelles Inserted 38500 / 635189 places (6.1%), Last: North Great River Inserted 39000 / 635189 places (6.1%), Last: Myers Corner Inserted 39500 / 635189 places (6.2%), Last: Cervignano del Friuli Inserted 40000 / 635189 places (6.3%), Last: Wallburg Inserted 40500 / 635189 places (6.4%), Last: York Inserted 41000 / 635189 places (6.5%), Last: Clay Center Inserted 41500 / 635189 places (6.5%), Last: Bairdstown Inserted 42500 / 635189 places (6.7%), Last: Mãe do Rio Inserted 43000 / 635189 places (6.8%), Last: Industry Inserted 43500 / 635189 places (6.8%), Last: Connoquenessing Inserted 44000 / 635189 places (6.9%), Last: Casselman Inserted 44500 / 635189 places (7%), Last: Vannes Inserted 45000 / 635189 places (7.1%), Last: Hermosa Inserted 45500 / 635189 places (7.2%), Last: Ducktown Inserted 46000 / 635189 places (7.2%), Last: Bryce Canyon City Inserted 46500 / 635189 places (7.3%), Last: Solms Inserted 47000 / 635189 places (7.4%), Last: Frederickson Inserted 47500 / 635189 places (7.5%), Last: Durbin Inserted 48000 / 635189 places (7.6%), Last: Machadinho Inserted 48500 / 635189 places (7.6%), Last: Pound Inserted 49000 / 635189 places (7.7%), Last: Aasu Inserted 50000 / 635189 places (7.9%), Last: Saint-Sornin-la-Marche Inserted 50500 / 635189 places (8%), Last: Sarıhıdır Köyü Inserted 51000 / 635189 places (8%), Last: Trémentines Inserted 51500 / 635189 places (8.1%), Last: Francisco Alves Inserted 52000 / 635189 places (8.2%), Last: Piacatu Inserted 52500 / 635189 places (8.3%), Last: Embu das Artes Inserted 53000 / 635189 places (8.3%), Last: Poço de José de Moura Inserted 53500 / 635189 places (8.4%), Last: Antônio Almeida Inserted 54000 / 635189 places (8.5%), Last: Užava Inserted 54500 / 635189 places (8.6%), Last: Alto Caparaó Inserted 55000 / 635189 places (8.7%), Last: São José da Barra Inserted 55500 / 635189 places (8.7%), Last: Brasiléia Inserted 56000 / 635189 places (8.8%), Last: Marcelândia Inserted 56500 / 635189 places (8.9%), Last: Chyliczki Inserted 57000 / 635189 places (9%), Last: Peñalsordo Inserted 57500 / 635189 places (9.1%), Last: Chóvar Inserted 58000 / 635189 places (9.1%), Last: Santa Cruz de la Palma Inserted 58500 / 635189 places (9.2%), Last: Ribera d''Ondara Inserted 59000 / 635189 places (9.3%), Last: Villaeles de Valdavia Inserted 59500 / 635189 places (9.4%), Last: Tarazona de la Mancha Inserted 60000 / 635189 places (9.4%), Last: Morella Inserted 60500 / 635189 places (9.5%), Last: San Martín de Oscos Inserted 61000 / 635189 places (9.6%), Last: Camprodon Inserted 61500 / 635189 places (9.7%), Last: Terrinches Inserted 62000 / 635189 places (9.8%), Last: Cernadilla Inserted 62500 / 635189 places (9.8%), Last: La Peza Inserted 63000 / 635189 places (9.9%), Last: Mombeltrán Inserted 63500 / 635189 places (10%), Last: Culleredo Inserted 64000 / 635189 places (10.1%), Last: La Granjuela Inserted 64500 / 635189 places (10.2%), Last: Bielsa Inserted 65000 / 635189 places (10.2%), Last: Vana-Nursi küla Inserted 65500 / 635189 places (10.3%), Last: Suuremõisa küla Inserted 66000 / 635189 places (10.4%), Last: Helbi küla Inserted 66500 / 635189 places (10.5%), Last: Tolli küla Inserted 67000 / 635189 places (10.5%), Last: Kaurutootsi küla Inserted 67500 / 635189 places (10.6%), Last: Lütä küla Inserted 68000 / 635189 places (10.7%), Last: Räitsvere küla Inserted 68500 / 635189 places (10.8%), Last: Mustapali küla Inserted 69000 / 635189 places (10.9%), Last: Lipametsa küla Inserted 69500 / 635189 places (10.9%), Last: Sürgavere küla Inserted 70000 / 635189 places (11%), Last: 門真市 Inserted 70500 / 635189 places (11.1%), Last: Amargosa Inserted 71000 / 635189 places (11.2%), Last: Ettendorf Inserted 71500 / 635189 places (11.3%), Last: Kirchbach Inserted 72000 / 635189 places (11.3%), Last: Saint-Alexandre Inserted 72500 / 635189 places (11.4%), Last: Itapúa Inserted 73000 / 635189 places (11.5%), Last: Ramouzens Inserted 73500 / 635189 places (11.6%), Last: Верхнеуфалейский городской округ Inserted 74000 / 635189 places (11.7%), Last: Bouzais Inserted 74500 / 635189 places (11.7%), Last: Marulanda Inserted 75000 / 635189 places (11.8%), Last: Obersulm Inserted 75500 / 635189 places (11.9%), Last: Zettlitz Inserted 76000 / 635189 places (12%), Last: Montbré Inserted 76500 / 635189 places (12%), Last: Baruth/Mark Inserted 89500 / 635189 places (14.1%), Last: Úvaly Inserted 90000 / 635189 places (14.2%), Last: Prysk Inserted 90500 / 635189 places (14.2%), Last: Huslenky Inserted 91000 / 635189 places (14.3%), Last: Bohuslavice Inserted 91500 / 635189 places (14.4%), Last: Černovice Inserted 92000 / 635189 places (14.5%), Last: Hačky Inserted 92500 / 635189 places (14.6%), Last: Mlékosrby Inserted 93000 / 635189 places (14.6%), Last: Růžová Inserted 93500 / 635189 places (14.7%), Last: Borek Inserted 94000 / 635189 places (14.8%), Last: Dobřany Inserted 94500 / 635189 places (14.9%), Last: Násedlovice Inserted 95000 / 635189 places (15%), Last: Pokojov Inserted 95500 / 635189 places (15%), Last: Cidlina Inserted 96000 / 635189 places (15.1%), Last: Vlachovice Inserted 96500 / 635189 places (15.2%), Last: Argentré Inserted 97000 / 635189 places (15.3%), Last: Jesberg Inserted 97500 / 635189 places (15.3%), Last: Corbeil-Cerf Inserted 98000 / 635189 places (15.4%), Last: Saint-Victor-la-Rivière Inserted 98500 / 635189 places (15.5%), Last: Martainville Inserted 99000 / 635189 places (15.6%), Last: Gammelby Inserted 100000 / 635189 places (15.7%), Last: Motey-Besuche Inserted 100500 / 635189 places (15.8%), Last: Rechtsupweg Inserted 101000 / 635189 places (15.9%), Last: Andrichsfurt Inserted 102000 / 635189 places (16.1%), Last: Mittelneufnach Inserted 102500 / 635189 places (16.1%), Last: Ettaler Forst Inserted 103000 / 635189 places (16.2%), Last: Gron Inserted 103500 / 635189 places (16.3%), Last: Teugn Inserted 104000 / 635189 places (16.4%), Last: Arnbruck Inserted 104500 / 635189 places (16.5%), Last: Medford Township Inserted 105000 / 635189 places (16.5%), Last: Névache Inserted 105500 / 635189 places (16.6%), Last: Rückersdorfer Forst ERROR: Failed to insert polygon 1021748_3 (An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

In ExceptionConverter.php line 106:

An exception occurred while executing a query: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

In Exception.php line 30:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

In Statement.php line 101:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

pulsejet commented 1 year ago

@AndyXheli try changing this line to a lower value (I'm guessing this needs to be configurable at this point)

https://github.com/pulsejet/memories/blob/4e52978fa941c2651b5837f060d4e20ef47386a3/lib/Service/Places.php#L350

ghost commented 1 year ago

@pulsejet thank you for your reply :) any recommendations on what value I should put them at ?

pulsejet commented 1 year ago

Try 20, say

ghost commented 1 year ago

@pulsejet Looks good i ran memories:places-setup after chaning to lime to 20and it completed with no issues.

n0one42 commented 1 year ago

For the people who are not that familiar with php and such... I did the following on my bare metal nextcloud installation but it should be nearly the same also for docker installations. sudo nano /var/www/nextcloud/apps/memories/lib/Service/Places.php Search for f (0 === $count % with Strg + w Then you should finde the block like this:

                // Commit transaction every once in a while
                if (0 === $count % 100) {

For me, I changed it to 80 and it worked.

pulsejet commented 1 year ago

Some new related improvements in https://github.com/pulsejet/memories/commit/7b647dc5c5f361870c22a0240ccf8a1793ea60de

zzyzx-dc commented 1 year ago

I have tried 80 and 20 to no avail. Still crashes with this error message. This time it got as far as 526000 / 635189 places.

Edit: after a reboot, it seems to have worked.

markuman commented 8 months ago

I'm hitting the same error after updating to memories 7.0.2
The changelog sais I should rerun occ memories:places-setup: https://github.com/pulsejet/memories/blob/master/CHANGELOG.md#v702---2024-03-19

I'm running mariadb 10.6.16.

Reducing from 500 to 100 if (0 === $count % 100) { fixes the issue for me.

pulsejet commented 8 months ago

For future reference https://memories.gallery/troubleshooting/#general-error-2006-mysql-server-has-gone-away