Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0006846
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] 09. Financial managementcriticalalways2009-01-09 22:072009-03-09 23:27
ReporternetworkbView Statuspublic 
Assigned Toshuehner 
PriorityimmediateResolutionfixedFixed in Version2.35MP13
StatusclosedFix in branch2.3xFixed in SCM revision13534
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabaseOracleJava version1.5.0_15
OS VersionGentoo 2.6.24Database version10 gAnt version1.7
Product Version2.35MP4SCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0006846: Having a huge amount of records on fact_Acct (>125.000) blocks the database on deleting process.

DescriptionIf you have for than 125.000 fact_acct records and you try to delete all of them together (supouse all the periods are opened), the database gets blocked with Oracle looks.
Steps To ReproduceRegister more than 125.000 records on fact_acct (20.000 invoices, 20.000 settlements, 20.000 bankstatements, etc.).
Disable background process.
Try to erase the accounting information with the existing process Finantial management->Accounting->Transactions->Erase Accounting
Proposed Solution1-Database fine tunning.
2-Delete all the information in blocks instead of all at once.
TagsNo tags attached.
Attached Filesdiff file icon 23x_fact_acct_reset_with_commits.diff [^] (1,237 bytes) 2009-01-26 17:59 [Show Content]
xml file icon FACT_ACCT_RESET_with_commits.xml [^] (10,577 bytes) 2009-01-26 17:59

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to feature request 0007753 closededuardo_Argal Reset accounting pl-process should be broken down into smaller parts committable individually 
blocks defect 0006836 closedshuehner Having a huge amount of records on fact_Acct (>125.000) blocks the database on deleting process. 

-  Notes
(0012109)
shuehner (administrator)
2009-01-12 14:11

 Note: trying to reproduce on 2.40 (as we have needed amount of data available on this version) was not successful on oracle 10g, latest 2.40 with about 167.000 fact_acct entries generated from sales invoices. Process did finish successfully in about 4min.

Next test is reverting the accounting reset code on this database to the state of r2.3x and trying to reproduce the error.
(0012112)
shuehner (administrator)
2009-01-12 14:55

 I tried to reproduce the issue in r2.40 with the FACT_ACCT_RESET pl-procedure downgraded to the r2.3x version. I did register about 170.000 entries in FACT_ACCT generated from sales invoices. I was not able to reproduce this problem so far.

The following information would help to reproduce this error:
1.) Exact error message which occurs in this case
2.) Total number of rows for fact_acct, fact_acct_cfs + tables for which accounting is enabled, if the number of rows is significant
3.) Dump of the database where this problem shows up would be helpful if this is possible.
(0012360)
networkb (developer)
2009-01-19 16:57

 This information has been asked to the customer. We are waiting for it.
(0012595)
galderromo (reporter)
2009-01-24 18:31

 First, I would like to change the database version from XE to Oracle 10g. As I haven't the needed permission, could you do it for me?

1) There is no error message, the process gets blocked and never finishes (at least not before 3 hours).

2) Fact 138.000 records (Invoices: 12000, Bank statements:18000, Settlements 19000, Cashes 500, Journals 1000). 6 Organizations.

3) Right now, it is not possible
(0012622)
shuehner (administrator)
2009-01-26 13:19

 Thanks for the info. Could you please add the row-count for the following table:
FACT_ACCT_CFS

As there is some part of the code used when this table is populated.
(0012628)
galderromo (reporter)
2009-01-26 15:53

 FACT_ACCT_CFS is empty.
(0012647)
shuehner (administrator)
2009-01-26 18:01

 As discussed with galder on skype. Attached is a patch with does make the fact_acct_reset transaction size smaller by introducing periodic commits. Please do try if applying this change does solve your problem and give feedback if it does / or don't.

The change is attached as a diff against the 2.3x branch pl-procedure and the already modifed xml-file for this procedure as well.

This patch is only intended for testing if this does help in this special case, not for general usage in other instances.
(0012834)
shuehner (administrator)
2009-01-29 16:54

 Reminder:
Was the patch already tested in the clients' installation? If yes did it solve the issue? If not, was is the planned timeframe for testing the change?
(0012876)
galderromo (reporter)
2009-01-30 11:41

 As explained by Stefan on note 0012647, we received a patch in order to see if solves our issue.

Due to our actual environments and project status, we can't asure we'll try it before 2 weeks time from now.

In any case, we will provide detailled information as we advance.
(0013803)
galderromo (reporter)
2009-02-20 09:54
edited on: 2009-02-20 09:56

 We have tested with an important data load, and the fix worked.
Thanks for the solution.

P.D:As I do not know how to proceed with the issue management, please update status.
(0013948)
svnbot (reporter)
2009-02-23 15:31

 Repository: openbravo
Revision: 13534
Author: huehner
Date: 2009-02-23 15:31:44 +0100 (Mon, 23 Feb 2009)

Fixed 6846
Keep transaction size of the accounting reset process small by doing period commits.

---
U branches/r2.3x/database/model/functions/FACT_ACCT_RESET.xml
---

https://dev.openbravo.com/websvn/openbravo/?rev=13534&sc=1 [^]
(0014535)
psarobe (manager)
2009-03-09 23:27

 Thanks Galder

- Issue History
Date Modified Username Field Change
2009-01-12 13:16 rafaroda Type defect => backport
2009-01-12 13:16 rafaroda fix_in_branch => 2.3x
2009-01-12 14:11 shuehner Note Added: 0012109
2009-01-12 14:55 shuehner Note Added: 0012112
2009-01-12 14:55 shuehner Status scheduled => feedback
2009-01-19 16:57 networkb Note Added: 0012360
2009-01-24 18:31 galderromo Note Added: 0012595
2009-01-24 18:31 galderromo Issue Monitored: galderromo
2009-01-26 09:59 networkb Status feedback => new
2009-01-26 13:19 shuehner Note Added: 0012622
2009-01-26 15:53 galderromo Note Added: 0012628
2009-01-26 17:59 shuehner File Added: 23x_fact_acct_reset_with_commits.diff
2009-01-26 17:59 shuehner File Added: FACT_ACCT_RESET_with_commits.xml
2009-01-26 18:01 shuehner Note Added: 0012647
2009-01-26 18:01 shuehner Status new => feedback
2009-01-29 16:54 shuehner Note Added: 0012834
2009-01-30 11:41 galderromo Note Added: 0012876
2009-02-20 09:54 galderromo Note Added: 0013803
2009-02-20 09:55 galderromo Note Edited: 0013803
2009-02-20 09:55 galderromo Note Edited: 0013803
2009-02-20 09:56 galderromo Note Edited: 0013803
2009-02-23 12:45 shuehner Relationship added related to 0007753
2009-02-23 15:31 svnbot Checkin
2009-02-23 15:31 svnbot Note Added: 0013948
2009-02-23 15:31 svnbot Status feedback => resolved
2009-02-23 15:31 svnbot Resolution open => fixed
2009-02-23 15:31 svnbot svn_revision => 13534
2009-03-06 16:09 psarobe Fixed in Version => 2.35MP12
2009-03-09 23:27 psarobe Regression testing => No
2009-03-09 23:27 psarobe Status resolved => closed
2009-03-09 23:27 psarobe Note Added: 0014535

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker