← Back to team overview

openerp-india team mailing list archive

[Bug 1015995] [NEW] Runaway cpu and temporary table problem when creating view in account_analytic_analysis

 

Public bug reported:

The view  account_analytic_analysis_summary_user contains an unqualified
join in a subselect, leading to a cartesian product of the form
res_users * res_users * account_analytic_account.

When these tabels contain a high number of rows, the attempt to create
the view will result in 100% cpu usage, and/or may crash.

We see this in the log:

Server Traceback (most recent call last):
  File "/home/oerhbrupg61/openerp-web/addons/web/common/http.py", line 592, in send
    result = openerp.netsvc.dispatch_rpc(service_name, method, args)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/netsvc.py", line 360, in dispatch_rpc
    result = ExportService.getService(service_name).dispatch(method, params)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/service/web_services.py", line 586, in dispatch
    res = fn(db, uid, *params)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 167, in execute_kw
    return self.execute(db, uid, obj, method, *args, **kw or {})
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 121, in wrapper
    return f(self, dbname, *args, **kwargs)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 176, in execute
    res = self.execute_cr(cr, uid, obj, method, *args, **kw)
  File "/home/oerhbrupg61/openobject-addons/audittrail/audittrail.py", line 495, in execute_cr
    return fct_src(cr, uid, model, method, *args)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 164, in execute_cr
    return getattr(object, method)(cr, uid, *args, **kw)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3406, in read
    result = self._read_flat(cr, user, select, fields, context, load)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3465, in _read_flat
    cr.execute(query, (tuple(sub_ids),))
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 152, in wrapper
    return f(self, *args, **kwargs)
  File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 212, in execute
    res = self._obj.execute(query, params)
OperationalError: could not write block 1059628 of temporary file: No space left on device
HINT:  Perhaps out of disk space?  

The problem view creation looks like this (in file account-
analytic_analysis.py):

    def init(self, cr):
        tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
        cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
                'SELECT ' \
                    '(u.account_id * u.max_user) + u."user" AS id, ' \
                    'u.account_id AS account_id, ' \
                    'u."user" AS "user", ' \
                    'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
                'FROM ' \
                    '(SELECT ' \
                        'a.id AS account_id, ' \
                        'u1.id AS "user", ' \
                        'MAX(u2.id) AS max_user ' \
                    'FROM ' \
                        'res_users AS u1, ' \
                        'res_users AS u2, ' \
                        'account_analytic_account AS a ' \
                    'GROUP BY u1.id, a.id ' \
                    ') AS u ' \
                'LEFT JOIN ' \
                    '(SELECT ' \
                        'l.account_id AS account_id, ' \
                        'l.user_id AS "user", ' \
                        'SUM(l.unit_amount) AS unit_amount ' \
                    'FROM account_analytic_line AS l, ' \
                        'account_analytic_journal AS j ' \
                    'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
                    'GROUP BY l.account_id, l.user_id ' \
                    ') AS l '
                    'ON (' \
                        'u.account_id = l.account_id ' \
                        'AND u."user" = l."user"' \
                    ') ' \
                'GROUP BY u."user", u.account_id, u.max_user' \
                ')')

The problem might be resolved by rewriting the view to prevent the
cartesian product explosion.

The problematic code is present in 6.0, 6.1 and in trunk.

We will try to submit a fix.

** Affects: openobject-addons
     Importance: Undecided
         Status: New


** Tags: account analysis analytic

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Addons.
https://bugs.launchpad.net/bugs/1015995

Title:
  Runaway cpu and temporary table problem when creating view in
  account_analytic_analysis

Status in OpenERP Addons (modules):
  New

Bug description:
  The view  account_analytic_analysis_summary_user contains an
  unqualified join in a subselect, leading to a cartesian product of the
  form res_users * res_users * account_analytic_account.

  When these tabels contain a high number of rows, the attempt to create
  the view will result in 100% cpu usage, and/or may crash.

  We see this in the log:

  Server Traceback (most recent call last):
    File "/home/oerhbrupg61/openerp-web/addons/web/common/http.py", line 592, in send
      result = openerp.netsvc.dispatch_rpc(service_name, method, args)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/netsvc.py", line 360, in dispatch_rpc
      result = ExportService.getService(service_name).dispatch(method, params)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/service/web_services.py", line 586, in dispatch
      res = fn(db, uid, *params)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 167, in execute_kw
      return self.execute(db, uid, obj, method, *args, **kw or {})
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 121, in wrapper
      return f(self, dbname, *args, **kwargs)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 176, in execute
      res = self.execute_cr(cr, uid, obj, method, *args, **kw)
    File "/home/oerhbrupg61/openobject-addons/audittrail/audittrail.py", line 495, in execute_cr
      return fct_src(cr, uid, model, method, *args)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/osv.py", line 164, in execute_cr
      return getattr(object, method)(cr, uid, *args, **kw)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3406, in read
      result = self._read_flat(cr, user, select, fields, context, load)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/osv/orm.py", line 3465, in _read_flat
      cr.execute(query, (tuple(sub_ids),))
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 152, in wrapper
      return f(self, *args, **kwargs)
    File "/home/oerhbrupg61/bzr/openobject-server/openerp/sql_db.py", line 212, in execute
      res = self._obj.execute(query, params)
  OperationalError: could not write block 1059628 of temporary file: No space left on device
  HINT:  Perhaps out of disk space?  

  The problem view creation looks like this (in file account-
  analytic_analysis.py):

      def init(self, cr):
          tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
          cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
                  'SELECT ' \
                      '(u.account_id * u.max_user) + u."user" AS id, ' \
                      'u.account_id AS account_id, ' \
                      'u."user" AS "user", ' \
                      'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
                  'FROM ' \
                      '(SELECT ' \
                          'a.id AS account_id, ' \
                          'u1.id AS "user", ' \
                          'MAX(u2.id) AS max_user ' \
                      'FROM ' \
                          'res_users AS u1, ' \
                          'res_users AS u2, ' \
                          'account_analytic_account AS a ' \
                      'GROUP BY u1.id, a.id ' \
                      ') AS u ' \
                  'LEFT JOIN ' \
                      '(SELECT ' \
                          'l.account_id AS account_id, ' \
                          'l.user_id AS "user", ' \
                          'SUM(l.unit_amount) AS unit_amount ' \
                      'FROM account_analytic_line AS l, ' \
                          'account_analytic_journal AS j ' \
                      'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
                      'GROUP BY l.account_id, l.user_id ' \
                      ') AS l '
                      'ON (' \
                          'u.account_id = l.account_id ' \
                          'AND u."user" = l."user"' \
                      ') ' \
                  'GROUP BY u."user", u.account_id, u.max_user' \
                  ')')

  The problem might be resolved by rewriting the view to prevent the
  cartesian product explosion.

  The problematic code is present in 6.0, 6.1 and in trunk.

  We will try to submit a fix.

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-addons/+bug/1015995/+subscriptions


Follow ups

References