Loris: [SQL] The drop_tables schema causing foreign key constraint errors

[AlexandraLivadas]

The SQL/9999-99-99-drop_tables.sql schema contains foreign key constraints. When it is sourced, foreign key constraint errors occur and some tables are consequently not dropped.

To Reproduce

Note: This will drop some of the tables in your database. Create a backup of your existing database to return to before completing these steps.

1. Go to your LORIS base path, /var/www/loris
2. Log into your MySQL command-line (as the admin user) and select the correct database
3. Source the schema by running the command:
   SOURCE /SQL/9999-99-99-drop_tables.sql
4. See list of foreign key constraint errors and for what tables they are flagged.

Related to PR #6498 and Issue #6495

[ridz1208]

@AlexandraLivadas could you clear your database completely, source RB from scratch
and then run the 9999-99-99 and copy/paste here which tables cause an issue ?

[lingma]

@ridz1208 that 9999-99-99 always has some problem, I had experienced the same problem in 21.0 and 22.0. I just tested, the same problem persists in 23.0. That 9999-99-99 could only work if you have a freshly sourced db without any real data, which is exactly what you just asked @AlexandraLivadas to do. I ignore what the purpose of this 9999-99-99 is.

[lingma]

Anyway, the constraint problem could be handled outside of this script, so the 9999-99-99 itself has NO problem.

[ridz1208]

@lingma what do you mean any real data ??

The ordering of the drop commands is what matters to not encounter foreign key errors and if 9999 works for a freshly source DB then it should work for any other database IF AND ONLY IF the schema has not been altered which is exactly the purpose here. 9999 is specific to the schema on the branch where it exists and would only work on it, if you have modified the schema intentionally then you should be modifying 9999 accordingly otherwise your changes are not complete.

It's the job of the developer issuing a PR to make sure the changes made in the PR are applied to the schemas 000* files, the 9999 drop tables file, the NEW PATCHES directory, and the RB dataset. resolving to using SET FOREIGN_KEY_CHECKS=0 is only bypassing a problem created by a developer somewhere in the commit history...

[lingma]

I use my v23 fake DB, which has only a few PSC added, I should have not modified any structure, at least as I remember, then several tables were left without touching, which proves that the 999 script won't work well. In my experience, it only works well if I have just imported structure via 000?---

[ridz1208]

if the structure is not modified then there is no reason for this to fail unless there is an error in the ordering which needs to be fixed !!

its a bug like any other

please post the error here and we can see the source of it

[lingma]

The first error happens at line 155, tarchive: (ERROR 1217 (23000) at line 155: Cannot delete or update a parent row: a foreign key constraint fails)

Full error, you can count from the bottom to locate:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Query OK, 0 rows affected (0.03 sec)

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

[ridz1208]

if you run SHOW ENGINE INNODB STATUS you canget the exact error under the section "latest foreign key error"

[lingma]

Cannot drop table tarchive because it is referenced by mri_upload1

Where does the mri_upload1 come? I don't think I created this table, the db was up only about 1 month or so, I should have recalled something if I created this strange table.

mri_upload1:
+--------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+------------------+------+-----+---------+----------------+
| UploadID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| TarchiveID | int(11) | YES | MUL | NULL | |
| SessionID | int(10) unsigned | YES | MUL | NULL | |
| IsCandidateInfoValidated | tinyint(1) | YES | | NULL | |
| IsTarchiveValidated | tinyint(1) | NO | | 0 | |
| IsPhantom | enum('N','Y') | NO | | N | |
+--------------------------+------------------+------+-----+---------+----------------+
6 rows

[lingma]

Oh, except mri_upload1, all works fine. I have no idea where comes mri_upload1, thanks. @ridz1208

[ridz1208]

@lingma exactly...

[johnsaigle]

@AlexandraLivadas could you run the same process and see what table is causing your issue?

[AlexandraLivadas]

I have attached a .txt file with the output when I run the command SOURCE SQL/9999-99-99-drop_tables.sql in the MySQL command-line. Based on this output, these are the tables affected. I also checked and when I look for these tables in my DB, they do still exist, whereas other tables were properly deleted.

permissions
permissions_category
session
candidate
caveat_options
language
psc
Project
subproject

drop_tables_output.txt

[johnsaigle]

Can you compare the output you get when running tools/raisinbread_refresh.php? That script should run the DROP TABLES script and then display any tables that are leftover.

[AlexandraLivadas]

@johnsaigle I am assuming I should reset DB with the backup I made before doing this, correct?

[johnsaigle]

Do you have actual data in the backup? If it's just rainsinbread data either way then it shouldn't matter.

[AlexandraLivadas]

No but now since all those tables are deleted, running the tool won't show the full picture.

[AlexandraLivadas]

I have included a .txt file with the
output when I ran ./tools/raisinbread_refresh.php.
The only foreign key constraint "error" listed was for line 37, which is table permissions. However, below this error message, there is a long list of untracked tables that still exist in the database that includes all the tables I listed above (and many more). Then, it prompted me to answer if I wanted to delete those now.

I looked in the tools/raisinbread_refresh.php and the way it goes about deleting all these untracked tables is by setting the foreign key check to 0 and then deleting all the files on that list. However, as @ridz1208 mentioned, we shouldn't need to set the foreign key check to 0 (ideally).

[johnsaigle]

The only foreign key constraint "error" listed was for line 37, which is table permissions.

That's odd. Do you have any idea which table had a foreign key to permissions?

we shouldn't need to set the foreign key check to 0 (ideally).

We shoudn't unless someone has a bunch of custom tables. The drop table script will never know about tables that don't exist in the main repo so it will never be able to drop them. It will also fail to drop core tables if custom tables have foreign keys to the core tables.

[ridz1208]

@AlexandraLivadas if you can, can you try it again? this time, when you get the error, could you run in your database SHOW ENGINE INNODB STATUS in the output there will be a section LATEST FOREIGN KEY ERROR that section will provide much more details on which tables are causing the failures

[christinerogers]

Where are we stuck on this @AlexandraLivadas ?

[AlexandraLivadas]

@christinerogers @ridz1208 I am not able to run SHOW ENGINE INNODB STATUS as I don't have the proper privileges in my DB. I'm trying to backtrack where the foreign keys are. I think my next step will be to test the SQL/9999-99-99-drop_tables.sql without any RB data in my database and see if RB tables are causing any of the errors.

[ridz1208]

@AlexandraLivadas technically, the data itself does not cause the issue, schemas do. So it would be highly unlikely that you get an issue with the RB data and no issue without.

We can make a request for IT to grant you temporarily these permissions to run the SQL command.

[AlexandraLivadas]

When I run the command SHOW ENGINE INNODB STATUS it gives me the following output under LATEST FOREIGN KEY ERROR

2020-05-15 10:03:42 0x7f9339348700 Cannot drop table alivadas_dev.permissions because it is referenced by alivadas_dev.LorisMenuPermissions

I am running this command on a database where I dropped all tables and then sourced everything (including RB) again. However, the table LorisMenuPermissions does not exist in my DB.

[Edit] LorisMenuPermissions is mentioned in some SQL/Archive schemas as well as in release patches up to 22.0. However, I am sourcing my DB from the 23.0 branch and there is no mention of it.

[AlexandraLivadas]

This issue has been resolved. With a fresh re-source of all schemas and RB data, the SQL/9999-99-99-drop_tables.sql schema properly drops all tables.

[johnsaigle]

Just FYI LorisMenuPermissions was removed in #5839. Your DB must have been on a 22.X version of LORIS instead of on 23 or master.

In general using the Raisinbread refresh script should solve this for you if you run it after switching branches.