Error 500 while editing taxonomy in 2.6.0

[jesus.s...@gmail.com]

Hi all,

When editing a taxonomy, I get a 500 error from nginx. There is an error referencing a foreign key constraint failure

nginx_1          | 10.150.12.101 - - [13/Aug/2020:15:15:45 +0000] "POST /index.php/user/clipboardStatus HTTP/1.1" 200 77 "http://10.100.40.213:63001/index.php/term/add?taxonomy=%2Findex.php%2F3kah-4sk7-nxfn&linkExisting=true" "Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:79.0) Gecko/20100101 Firefox/79.0" "-"
gearmand_1       |    INFO 2020-08-13 15:16:07.000000 [  main ] Accepted connection from 172.18.0.7:36328
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     3 ] Peer connection has called close()
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     3 ] Disconnected 172.18.0.7:36328
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     3 ] Gear connection disconnected: -:-
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [  main ] Accepted connection from 172.18.0.7:36330
gearmand_1       |  NOTICE 2020-08-13 15:16:08.000000 [  proc ] accepted,a778d6ef985a1f0e80caabccbda99674-arUpdateEsIoDocumentsJob,4af0730ada9187b38bcf3af30f3c3715,0 -> libgearman-server/server.cc:321
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     2 ] Peer connection has called close()
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     2 ] Disconnected 172.18.0.7:36330
gearmand_1       |    INFO 2020-08-13 15:16:08.000000 [     2 ] Gear connection disconnected: -:-
atom_worker_1    | 2020-08-13 17:16:08 > Job started.
nginx_1          | 2020/08/13 15:16:08 [error] 23#23: *2971 FastCGI sent in stderr: "PHP message: Unable to execute UPDATE statement. [wrapped: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`atom`.`term`, CONSTRAINT `term_FK_3` FOREIGN KEY (`parent_id`) REFERENCES `term` (`id`) ON DELETE CASCADE)]" while reading response header from upstream, client: 10.150.12.101, server: _, request: "POST /index.php/art-culos-de-prensa/edit HTTP/1.1", upstream: "fastcgi://172.18.0.7:9000", host: "10.100.40.213:63001", referrer: "http://10.100.40.213:63001/index.php/art-culos-de-prensa/edit"
nginx_1          | 10.150.12.101 - - [13/Aug/2020:15:16:08 +0000] "POST /index.php/art-culos-de-prensa/edit HTTP/1.1" 500 878 "http://10.100.40.213:63001/index.php/art-culos-de-prensa/edit" "Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:79.0) Gecko/20100101 Firefox/79.0" "-"
atom_worker_1    | 2020-08-13 17:16:08 > Updating 64 description(s).
atom_worker_1    | 2020-08-13 17:16:09 > Updated 64 description(s).
atom_worker_1    | 2020-08-13 17:16:09 > Job finished.

It works fine for me on 2.5.1

Any idea?

Best,

Jesus

[José Raddaoui]

Hi Jesus,

It looks like you have a term with an unexpected parent_id in the database. We added some changes in 2.6 to improve the foreign keys declaration (see this ticket for more information) but those changes don't address existing inconsistencies completely. To address this particular issue, I'd suggest to ...

- Create a dump of the database just in case something goes wrong.

- Fix the problematic term/parent relationships: connect to the AtoM database and run the following query:

UPDATE term t

LEFT JOIN term p ON t.parent_id=p.id

SET t.parent_id=110

WHERE p.id IS NULL AND t.id!=110;

- Rebuild the nested set:

php symfony propel:build-nested-set

I hope that solves the issue when you try to edit that term again.

Best regards,

Radda.

[jesus.s...@gmail.com]

No luck this time man:

mysql> use atom;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> UPDATE term t

    -> LEFT JOIN term p ON t.parent_id=p.id

    -> SET t.parent_id=110

    -> WHERE p.id IS NULL AND t.id!=110;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0  Changed: 0  Warnings: 0

[Dan Gillean]

Hi Jesus, 

Have you upgraded recently, with a database dump that was in your previous 2.5.x instance?

If yes, then it might be good confirm whether or not some steps in the upgrade process were skipped. 

First, when installing MySQL8, did you use the recommended SQL modes outlined at the end of this section of the 2.6 installation docs?

• https://www.accesstomemory.org/docs/2.6/admin-manual/installation/linux/ubuntu-bionic/#mysql  
  

Notice that these have changed from our previous recommendations. We have found some issues with using the STRICT_TRANS_TABLES mode (which could be contributing to your problem), and while we intend to fix these issues long-term (see issue #13331 for example), for now, we recommend not using this mode. 

There's a query provided here in our documentation on how you check and change your current SQL mode settings: 

• https://www.accesstomemory.org/docs/2.6/admin-manual/maintenance/common-atom-queries/#check-server-sql-modes  
  

Remember to restart MySQL if you make changes here. 

• sudo systemctl restart mysql
  

Next, during the upgrade process, did you remember to drop and recreate the database before loading your dump? See step 4 in this section of the upgrading docs: 

• https://www.accesstomemory.org/docs/2.6/admin-manual/installation/upgrading/#copy-your-old-data  
  

Also, did you remember to run the upgrade task? See: 

• https://www.accesstomemory.org/docs/2.6/admin-manual/installation/upgrading/#run-the-upgrade-task  
  

Hopefully one of these will help resolve your issue. If not, at least we will have ruled out some factors - hopefully Radda will have further ideas for you. 

Cheers, 

Dan Gillean, MAS, MLIS
AtoM Program Manager
Artefactual Systems, Inc.
604-527-2056

@accesstomemory

he / him

--
You received this message because you are subscribed to the Google Groups "AtoM Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/636e4562-f09e-454e-88e8-22d648c9e59do%40googlegroups.com.

[jesus san miguel]

Hi Dan,

This is a Docker deployment, and I followed to the letter the "one line" recipe from Radda:

docker cp /host/path/to/dump/atom_2.5.1.sql docker_percona_1:/atom.sql && \

docker-compose exec percona mysql -h localhost -u atom -patom_12345 -e "DROP DATABASE IF EXISTS atom;" && \

docker-compose exec percona mysql -h localhost -u atom -patom_12345 -e "CREATE DATABASE atom CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;" && \

docker-compose exec percona bash -c "mysql -h localhost -u atom -patom_12345 atom < /atom.sql" && \

docker-compose exec atom php -d memory_limit=-1 symfony tools:upgrade-sql --no-confirmation && \

docker-compose exec atom php -d memory_limit=-1 symfony search:populate

Most probably, the issue will be in the taxonomy data, but so far, I haven't been able to pinpoint it. Ours is a dual culture es/en and some of the taxonomies only have es culture, but adding (via mysql) en translation does not seem to make any difference...

Best,

You received this message because you are subscribed to a topic in the Google Groups "AtoM Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ica-atom-users/WqZODEpLDAM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ica-atom-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/CAC1FhZ%2B0NP9Kf2uO0v5g%2B%3DqipFzt-7jonLzqGB0X_R%2BgpWcVRA%40mail.gmail.com.

[José Raddaoui]

Hola de nuevo Jesús,

Maybe it's just the nested set what got corrupted, did you try the task after the query? Even if the query didn't affect any record a nested set rebuild could fix the issue.

Bests,

Radda.

[jesus san miguel]

Hi Radda,

I rebuilt the nested set, but no luck at all...
My DBA looked at the database and couldn't point to any inconsistencies...
He could only see that there is a NULL value on id 30:

mysql> SELECT id, name FROM taxonomy_i18n WHERE culture='en'
    -> ;
+----+---------------------------------------+
| id | name                                  |
+----+---------------------------------------+
| 30 | NULL                                  |
| 31 | Description Detail Levels             |
| 32 | Actor Entity Types                    |
| 33 | Description Statuses                  |

| 34 | Levels of description                 |

And he asked me if there was any way to log que SQL queries on the percona docker, to try to understand precisely what SQL UPDATE was taking place. Is there?

Best,

Jesus

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/8b8db7b4-fb3e-4233-93e6-165a005ba99dn%40googlegroups.com.

[José Raddaoui]

Hi Jesus,

First of all, I'm a bit confused about terms and taxonomies. The initial error is complaining about a term update and, since AtoM doesn't have an edit taxonomy page, I thought the issue was happening updating a term from the GUI. Is that what you're trying to do? Is the error still the same?

About the taxonomy with NULL name, it's the root taxonomy (used for permissions) and that value is expected.

To enable and access MySQL's general log in the Docker env.:

- Modify mysqld.cnf, adding:

general_log=1

general_log_file=/var/log/mysql/general.log

- Create log file and change permissions:

docker-compose exec -u root percona touch /var/log/mysql/general.log

docker-compose exec -u root percona chown mysql /var/log/mysql/general.log

- Restart the Percona service:

docker-compose restart percona

- Check log file while making changes:

docker-compose exec percona tail -f /var/log/mysql/general.log

- Copy log file to the host:

docker cp docker_percona_1:/var/log/mysql/general.log .

Best regards,

Radda.

[jesus san miguel]

Thank you Radda,

Yes, the error happens during while trying to update the term from the GUI. See attachment.

I captured the percona log trying to update the term "premios" (within genre) to "premios maravillosos". Attached too.

Best,

Jesus

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/04bc2d7a-168d-4c06-834f-e46f793bb91en%40googlegroups.com.

[José Raddaoui]

Np Jesus,

That gives some info and it's quite an unusual situation. The problematic query:

UPDATE term SET `TAXONOMY_ID`=78, `CODE`=NULL, `PARENT_ID`=7, `LFT`=1165, `RGT`=1166 WHERE term.ID=1295

It's using 7 as the `parent_id` for that term and that id is normally used for another entity (`static_page`). In AtoM's schema multiple models inherit from the `object` table, including `term` and `static_page`, so it's possible that that term's parent is pointing to another entity. You could select from the `object` table and look at the `class_name` column to verify that's the case.

By default, AtoM creates the root term with id 110 so you shouldn't see a term with an `id` or `parent_id` lower than that, and it's hard to tell how this could have happened using normal workflows. If you have more information about what caused the issue I'd like to hear it, in case there is something we can do to improve the situation.

In order to fix the issue for this particular term, run the following query to make it a child of the root term:

UPDATE term SET parent_id=110 WHERE id=1295;

And rebuild the nested set (in the Docker env. and just for terms):

docker-compose exec atom php symfony propel:build-nested-set --exclude-tables=information_object,menu

I hope that's all but you may want to check that no other term has an unexpected `id`/`parent_id`.

Best regards,

Radda.

[jesus san miguel]

Hi Radda,

From what I understand, the data is OK, but the query is trying to update the parent_id of the term to a value of 7 which doesn't exist. It seems a bug to me...

Here is another example: Look what happens when I try to rename term "Carteles" to "Some Other Thing". There is no static_page relation there:

2020-08-18T08:17:38.633283Z   16 Query UPDATE object SET `CLASS_NAME`='QubitTerm', `UPDATED_AT`='2020-08-18 10:17:38', `SERIAL_NUMBER`=1 WHERE object.ID=7760 AND object.SERIAL_NUMBER=0

2020-08-18T08:17:38.633687Z   16 Query UPDATE term SET `TAXONOMY_ID`=78, `CODE`=NULL, `PARENT_ID`=7, `LFT`=1165, `RGT`=1166 WHERE term.ID=7760

2020-08-18T08:17:38.634387Z   16 Query ROLLBACK

But the data seems ok to me:

mysql> select * from term_i18n where id=7760

    -> ;

+----------+------+---------+

| name     | id   | culture |

+----------+------+---------+

| Carteles | 7760 | es      |

+----------+------+---------+

1 row in set (0.00 sec)

mysql> select * from term where id=7760

    -> ;

+------+-------------+------+-----------+------+------+----------------+

| id   | taxonomy_id | code | parent_id | lft  | rgt  | source_culture |

+------+-------------+------+-----------+------+------+----------------+

| 7760 |          78 | NULL |       110 |  652 |  653 | es             |

+------+-------------+------+-----------+------+------+----------------+

1 row in set (0.01 sec)

mysql> select * from object where id=48756 or id=48760 or id=48764 or id=48873;

+-------------------------+---------------------+---------------------+-------+---------------+

| class_name              | created_at          | updated_at          | id    | serial_number |

+-------------------------+---------------------+---------------------+-------+---------------+

| QubitObjectTermRelation | 2020-07-14 10:25:10 | 2020-07-15 01:38:21 | 48756 |             0 |

| QubitObjectTermRelation | 2020-07-14 10:25:11 | 2020-07-15 01:38:30 | 48760 |             0 |

| QubitObjectTermRelation | 2020-07-14 10:25:11 | 2020-07-15 01:38:41 | 48764 |             0 |

| QubitObjectTermRelation | 2020-07-14 10:25:51 | 2020-07-15 01:39:11 | 48873 |             0 |

+-------------------------+---------------------+---------------------+-------+---------------+

4 rows in set (0.00 sec)

mysql> select * from object_term_relation where term_id=7760;

+-------+-----------+---------+------------+----------+

| id    | object_id | term_id | start_date | end_date |

+-------+-----------+---------+------------+----------+

| 48756 |     48755 |    7760 | NULL       | NULL     |

| 48760 |     48759 |    7760 | NULL       | NULL     |

| 48764 |     48763 |    7760 | NULL       | NULL     |

| 48873 |     48872 |    7760 | NULL       | NULL     |

+-------+-----------+---------+------------+----------+

4 rows in set (0.00 sec)

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/cc9c955e-6264-4b7f-9b99-646402d3c676n%40googlegroups.com.

[José Raddaoui]

Hi Jesus,

It seems like it's assigning the homepage (`object` with id=7) as the parent term when you update. This is the code involved in the generation
of the parent (broad term) field:

https://github.com/artefactual/atom/blob/stable/2.6.x/apps/qubit/modules/term/actions/editAction.class.php#L170-L210

And this part is the one that sets the parent before save:

https://github.com/artefactual/atom/blob/stable/2.6.x/apps/qubit/modules/term/actions/editAction.class.php#L398-L408

This field uses URLs internally to populate and parse the parent id and it looks like, instead of getting the root term or the real parent, it's linking to the homepage (`/` URL). Some suggestions:

- Maybe some slugs are missing and messing with the URL generation, try to fix them with the generate slugs task (which also requires to re-populate the search index):

docker-compose exec atom php symfony propel:generate-slugs

docker-compose exec atom php symfony search:populate

- I see that you have some customization in the code, check that the edit action linked above is not modified.

- Make sure the routing configuration is still the same and that you are not changing something from the web server.

Best,

Radda.

[jesus san miguel]

Hi Radda,

Slug regeneration didn't solve the problem.

And I'm running no code customization, that's for sure...

Could it be due to the fact that our default culture is es and the term does not have an en description?

Best,

Jesus

To view this discussion on the web visit https://groups.google.com/d/msgid/ica-atom-users/5ed15a9f-aed1-4427-a733-f949f6c85a6fn%40googlegroups.com.

[José Raddaoui]

Hi Jesus,

I thought you had some customization but I see it's just the print view. I'm trying to reproduce locally without luck, not even forcing a different source culture in the database for a term and changing the application's default culture. Would you like to send me a dump to my personal email to see if I can reproduce locally? Info about the default_culture from apps/qubit/config/settings.yml and steps to reproduce will help too.

Best regards,

Radda.

[José Raddaoui]

For those that may end up here ...

The issue was caused by a manual deletion of slugs, including the root term's slug. The generate slugs task doesn't consider the root term so it had to be added manually (a different slug value may be needed if the following one already exists): 

INSERT INTO slug (object_id, slug) VALUES (110, 't8ef-8ghu-4dxa');

Best,

Radda.