Dangling reports left when bug delete

[zx2c4]

When deleting a bug, it looks like the reports previous associated with the bug are not deleted, remaining in the database, but there is no longer a way to access them, so they're just leftover data. Probably these reports should be deleted when the associated bug is deleted.

[F43nd1r]

Which database do you use? I could not reproduce this issue.
This is implemented on database level with DELETE CASCADE: Bug -> Stacktrace, Stacktrace -> Report

[zx2c4]

MariaDB 10.1.26.

[F43nd1r]

I'm using MariaDB 10.1.34, so it's probably a configuration issue. My first idea would be a wrong dialect: Did you put spring.jpa.database-platform=org.hibernate.dialect.MariaDB10Dialect in your configuration file before the first start?
If no, that's probably the issue.
If yes, please post the output of show create table stacktrace; and show create table report;.

[zx2c4]

Hah, no, I was using spring.jpa.database-platform=org.hibernate.dialect.MySQL57Dialect. I'll switch to using the correct dialect and then see.

(I previously had to set things manually to use utf8 instead of utf8mb4, because of column width errors. I wonder if setting the proper dialect would have fixed that as well.)

[zx2c4]

It still appears to be broken. Steps:

1. Generate a crash on your phone.
2. View the bug. Click admin. Click "delete bug." Click yes.
3. Observe that the bug is gone. Observe that the report is gone.
4. Open up the mysql command line and type select id from report;.
5. BUG Observe that this still shows the UUID of the orphaned report.

[F43nd1r]

Still can't reproduce. Please post the output of show create table stacktrace; and show create table report;

[zx2c4]

MariaDB [acra]> show create table stacktrace;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                             |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stacktrace | CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `stacktrace` longtext NOT NULL,
  `version_code` int(11) NOT NULL,
  `version_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [acra]> show create table report;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| report | CREATE TABLE `report` (
  `id` varchar(255) NOT NULL,
  `android_version` varchar(255) DEFAULT NULL,
  `content` longtext,
  `date` datetime DEFAULT NULL,
  `phone_model` varchar(255) DEFAULT NULL,
  `user_comment` longtext,
  `user_email` varchar(255) DEFAULT NULL,
  `version_name` varchar(255) DEFAULT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `installation_id` varchar(255) DEFAULT NULL,
  `stacktrace_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_report_stacktrace` (`stacktrace_id`),
  CONSTRAINT `FK_report_stacktrace` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[F43nd1r]

Ok, there's the issue. Here is how it should look like:

CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `stacktrace` longtext NOT NULL,
  `version_code` int(11) NOT NULL,
  `version_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_stacktrace_bug` (`bug_id`),
  CONSTRAINT `FK_stacktrace_bug` FOREIGN KEY (`bug_id`) REFERENCES `bug` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

Basically, you're missing the foreign key reference. Now the question is: why? Could you create a new database and initialize it with the correct dialect to check if it works there?

[zx2c4]

Same symptoms as before, after a full wipe and refresh, using the correct dialect.

MariaDB [acra]> show create table stacktrace;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                             |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stacktrace | CREATE TABLE `stacktrace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bug_id` int(11) NOT NULL,
  `stacktrace` longtext NOT NULL,
  `version_code` int(11) NOT NULL,
  `version_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [acra]> show create table report;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| report | CREATE TABLE `report` (
  `id` varchar(255) NOT NULL,
  `android_version` varchar(255) DEFAULT NULL,
  `content` longtext,
  `date` datetime DEFAULT NULL,
  `phone_model` varchar(255) DEFAULT NULL,
  `user_comment` longtext,
  `user_email` varchar(255) DEFAULT NULL,
  `version_name` varchar(255) DEFAULT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `installation_id` varchar(255) DEFAULT NULL,
  `stacktrace_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_report_stacktrace` (`stacktrace_id`),
  CONSTRAINT `FK_report_stacktrace` FOREIGN KEY (`stacktrace_id`) REFERENCES `stacktrace` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[F43nd1r]

So I was finally able to reproduce it, and it was caused by a typo.
I'm currently looking into validation options for the changelog to avoid this in the future, but I can't find much.

[F43nd1r]

I recommend to

1. export your data
2. Destroy the database
3. Let the new war create the structure
4. Import your data

But you could also add the constraint manually.

[zx2c4]

Just did exactly that.