Ticket #571 (new defect)

Opened 2 years ago

Last modified 2 years ago

Reordering categories on Postgres causes data corruption

Reported by: jsheth Assigned to: jsheth
Priority: high Milestone: 0.4.8
Component: SGL - Category Severity: major
Keywords: Cc:

Description

Reordering categories with Seagull 0.4.7 / 0.4.x on Postgres causes data corruption.

How to reproduce: try to move a category up or down using the 'Reorder Categories' screen.

What happens: Multiple 'DB contraint violation' errors occur. An example of an error is as follows:

 MESSAGE: DB Error: constraint violation
TYPE: User Error
FILE: /var/www/seagull_mubarak/lib/pear/PEAR.php
LINE: 879
DEBUG INFO:
874 $format = $options;
875 }
876 printf($format, $this->getMessage());
877 }
878 if ($this->mode & PEAR_ERROR_TRIGGER) {
879 trigger_error($this->getMessage(), $this->level);
880 }
881 if ($this->mode & PEAR_ERROR_DIE) {
882 $msg = $this->getMessage();
883 if (is_null($options) || is_int($options)) {
884 $format = "%s";

Workaround: To avoid problems / data corruption while reordering categories, drop the *fk_category_document* foreign key constraint from the document table and the *fk_parent* constraint from the category table.

Severity: This is a pretty severe bug, because existing category data is corrupted, when the user is least expecting it. This can cause the whole site to malfunction. Many users will not have daily backups, and so there will need to be manual work to get the site working again.

Solution(s):

- Use transactions for all reordering actions, so data is not corrupted if something goes wrong. - Investigate ways to make the SQL queries not fail, even when these DB constraints are present. - Modify the Postgres SQL scripts to not add these constraints by default.


Details:

Actual errors returned by Postgres are as follows. These errors are not shown because PEAR DB provides a generic 'DB constraint violation' error for all such errors. There is a way to enable native error display with PEAR DB, but I am not sure how to enable it.

SQL error:

ERROR: update or delete on "category" violates foreign key constraint "fk_category_document" on "document" DETAIL: Key (category_id)=(76) is still referenced from table "document".

In statement: DELETE FROM category

Solution: Drop the following constraint from the document table: fk_category_document


SQL error:

UPDATE category SET category_id=69 WHERE category_id=110 ERROR: update or delete on "category" violates foreign key constraint "fk_parent" on "category" DETAIL: Key (category_id)=(110) is still referenced from table "category".

Workaround: drop fk_parent constraint

Change History

02/24/06 20:48:24 changed by demian

  • priority changed from normal to high.