Eliminate unnecessary table records

Hello everyone,

I have ojs 2.4.8.2 that I am upgrading to version 3.x
Analyzing the database, I could see some tables taking up a lot of space.
The notifications table is 537MB in size with 1774422 rows. Analyzing the records I could see through the SQL statement below that the vast majority of lines refer to non-existent users.

select count(user_id) from notifications where user_id  not in (select user_id from users);
+----------------+
| count(user_id) |
+----------------+
|        1692040 |
+----------------+

Can I delete these records directly in the table since they refer to users that do not exist?

Regards,

Renato L. Sousa

Hi @rensousa,

How many records do you get when you also exclude user_id=0? This is a valid entry in some cases.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

Only 23 records have the field user_id = 0.

select count(*) from notifications where user_id = 0;
+----------+
| count(*) |
+----------+
|       23 |
+----------+

Can manually deleting records that are related to non-existent users (except user_id = 0) cause any serious inconsistencies?

Regards,

Renato L. Sousa

Hi @rensousa,

Removing notifications that relate to a user_id that does not match anything in users, and is not 0, will not affect anything. But please do take a back-up of your database before you begin.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

I’m thinking about the relationships that this table can have.
Should I search for all tables that have the notification_id field and remove those records?
Would it be better to manually insert a register with the user_id in the user table and then use MergeUsers to delete it safely?

Regards,

Renato L. Sousa

Hi @rensousa,

The only table that refers to notifications is notification_settings; you can similarly remove content from notification_settings that refers to non-existent entries in notifications.

I suspect you’re dealing with a lot of garbage data in notifications, and it’ll be a lot cleaner/faster if you remove it. You could go the “merge users” route, but that sounds pretty labour-intensive to me.

Regards,
Alec Smecher
Public Knowledge Project Team