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