Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0001938Openbravo ERP01. General setuppublic2007-09-12 07:372008-06-12 09:43
Reporteruser71 
Assigned Tocromero 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version2.35 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0001938: Delete/copy client corrupts Postgres database
DescriptionThe delete and copy client function will corrupt the db constraints when run against a Postgresql database.

The stored procedures drop all the primary key/foreign key/unique constaints and do not reconstruct them properly.

E.g. multi column primary keys will be reconstructed with only one column.
Some foreign keys will not be added back because the appropriate primary key does not exist.
etc.

Attached is a script that will fix these problems.

This script:
a) changes the user_constraints view so that it includes all the columns related to a constraint
b) extracts all the drop/recreate logic into separate functions
c) changes the recreate sql so that the constraints are recreated properly.

I have tested these changes by dumping the db schema before a delete and after, then comparing the resultant files. The before/after schemas should be identical.
Steps To Reproduce
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange

Notes
(0005527)
user71   
2005-06-01 00:00   
(edited on: 2008-06-12 09:43)
This bug was originally reported in SourceForge bug tracker and then migrated to Mantis.

You can see the original bug report in:
https://sourceforge.net/support/tracker.php?aid=1792922 [^]
(0002067)
user71   
2007-09-18 09:11   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1737231
Originator: YES

Environment

OS: Linux - Centos 4 (test/prod) & Windows XP (dev)
DB: Postgres 8.1.9 (test/prod) & 8.2.4 (dev)
Release: 2.33
Browser: Firefox 2.0.0.6, IE 7
(0002068)
psarobe   
2007-09-18 09:13   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1500703
Originator: NO

Please follow the guidelines about reporting bugs:

http://wiki.openbravo.com/wiki/index.php/Bug_Reporting_Guidelines [^]

Thank you
(0002069)
villind   
2007-09-18 11:47   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=61737
Originator: NO

Hi,

what is the reason for dropping or disabling constrains during normal operations?

If it is possible to mees up the database structure it also means that the operations are not done within a transaction.

I'm really considering to revoke all DBA permissions from Openbravo user in production environments, but I haven't yet tested how crippled the system will be then. It still may be better than the current inconsistent system.

Regards,
Ville
(0002070)
user71   
2007-09-28 18:03   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1770033
Originator: NO

I think this does completely solve the problem because I still have a wrong primary key for c_greeting_trl. The key does not include the language.

I have created and deleted clients several times. Running the script afterwards did not solve the problem apparently.
(0002071)
user71   
2007-09-30 11:30   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1737231
Originator: YES

The script will not fix the db if you have already deleted or copied a client. You will have to start from a new installation & run the script against the db before deleting any clients.

Also I discovered last week that some errors will still occur if you delete the BigBazaar client. I'll try to a fix for that soon.
(0002072)
user71   
2007-10-04 02:12   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1737231
Originator: YES

I have updated the patch for the 2.34 release and also to fix the problems when deleting BigBazaar.

Note: If you delete BigBazaar before creating any other client, you will not be able to logon until you apply the patch in this bug report
http://sourceforge.net/tracker/index.php?func=detail&aid=1806707&group_id=162271&atid=823129 [^]
File Added: patch_delete_copy_client.sql
(0002073)
user71   
2007-10-11 10:16   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1737231
Originator: YES

Another problem.
With the last patch, when you delete big bazaar it also delete the attribute set instance with id 0. This seems to be required by other parts of the system (e.g. to complete a PO). The instance is deleted because it belongs to an attribute set owned by big baazar.
Switching it to be owned by a system attribute set solves the problem

Run this before deleting big bazaar

-- Fix default? attribute set instance so that it is not deleted when we delete big bazaar
update m_attributesetinstance set m_attributeset_id=0 where m_attributesetinstance_id = 0;
(0002074)
cromero   
2007-10-20 10:26   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1500614
Originator: NO

We have tested an all seems to go well.

This modifications will be included in the next OB-Postgre version.
Thank you EINTELAU for your contribution.

Carlos Romero
Openbravo Team
(0002075)
pjuvara   
2007-10-31 15:21   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1871571
Originator: NO

Assigned to pjuvara for verification.
(0002076)
cromero   
2008-03-03 17:48   
(edited on: 2008-06-12 09:21)
Logged In: YES
user_id=1500614
Originator: NO

I have modified the way in which constraints are disabled/enabled: instead of dropping and recreating all the constraints, I have set an update to the pg_class table in order to disable all the triggers for the tables (what includes the constraints) (look at http://archives.postgresql.org/pgsql-sql/2003-02/msg00624.php [^])

So, now the constraints are not recreated and that is why I believe that the problem of disabling/enabling constraints will not occur again.

Let me know your opinion.

Carlos