Cleaning mysql database

Hi,
I’m using Pro 6.2.9 on Centos 7 with a mariadb database. I never cleaned the database, so my Event table is 300 MB and my UserEvent table is 700 MB in size.
The server manual says, that after deleting entries from the Event table, “the corresponding items in UserEvent will deleted automatically by MariaDB when the foreign keys in Event table are deleted.”
I just deleted a few 100,000 entries from the Event Table, but nothing happened to the UserEvent table. It still has 2,2 million entries.
What can I do?
Thanks.

There is something wrong. Cause UserEvent table has Foreign key on Column eid pointing to Event.uuid and onDelete set to CASCADE in default. Column UserEvent.eid is NULLABLE, so check if there are rows without eid value.

Table Events is universal and if there is row, it doesn’t mean that it have point row in UserEvent table pointing onto it.

Can you check that you have Foreign key setup correctly in UserEvent table? If so, than you delete events which have no pointing UserEvent.

This is from my database backup:

CREATE TABLE `UserEvent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` int(11) DEFAULT NULL,
  `username` varchar(255) NOT NULL,
  `eid` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_UserEvent_username` (`username`),
  KEY `ix_UserEvent_eid` (`eid`),
  CONSTRAINT `UserEvent_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `Event` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9043549 DEFAULT CHARSET=utf8;

hmm it’s not posible to have this table definition and have broken relations. Are you sure you delete all rows from table Event?

No, I only deleted a few 100,000 rows. I called

DELETE FROM Event WHERE to_days(now()) - to_days(timestamp) > 90 LIMIT 100000;

a few times, but the UserEvent table didn’t shrink. Maybe this will happen in the future when the related entries are deleted.

Thank you so far.

Yes, as I said table Event is for all events. So you may not delete user’s events.

Maybe something like this can be(didn’t test it):

DELETE FROM `Events` WHERE `uuid` IN (
    SELECT `u`.`eid` FROM `UserEvents` AS `u`
    JOIN `Events` AS `e` ON `e`.`uuid` = `u`.`eid`
    WHERE to_days(now()) - to_days(`e`.`timestamp`) > 90
    LIMIT 100000
);
1 Like

I just deleted another 100,000 rows from the Event table, and now 300,000 rows from UserEvent have been deleted, too. Seems that the first entries that I deleted didn’t have related UserEvent entries.

Thanks.