1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Memory limit when delete a row

Discussion in 'Community' started by mirceat, Jul 17, 2019.

  1. mirceat

    mirceat Member

    Level: Community
    Hello,

    I have a large form that include several groups and elements. Works fine for insert/update but when i try to delete a row, i'm getting the message "PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)"

    I tried to increase the memory_limit value from 128M to 1GB, same problem.

    When Fabrik debug is activated and i tried to edit an existing row and press the delete button, the debug shows:

    Code (Text):

    ....

    table:mergeJoinedData get ids

    SELECT distinct `contracts_tool_servicii`.`id` AS __pk_val0, `contracts_tool_servicii_1291_repeat`.`id` AS __pk_val1, `contracts_tool_servicii_1251_repeat`.`id` AS __pk_val2,
    `contracts_tool_servicii_1250_repeat`.`id` AS __pk_val3, `contracts_tool_servicii_1265_repeat`.`id` AS __pk_val4 FROM `contracts_tool_servicii`
    LEFT JOIN  `contracte_setari_repeat_activeaza_abonament` AS `contracte_setari_repeat_activeaza_abonament` ON `contracte_setari_repeat_activeaza_abonament`.`activeaza_abonament` = `contracts_tool_servicii`.`abonament_bundle`
    LEFT JOIN  `contracte_setari_753_repeat` AS `contracte_setari_753_repeat` ON `contracte_setari_753_repeat`.`id` = `contracts_tool_servicii`.`optiuni_clasa`
    LEFT JOIN  `contracte_setari_739_repeat` AS `contracte_setari_739_repeat` ON `contracte_setari_739_repeat`.`parent_id` = `contracts_tool_servicii`.`abonament`
    LEFT JOIN  `contracte_setari_739_repeat` AS `contracte_setari_739_repeat_0` ON `contracte_setari_739_repeat_0`.`parent_id` = `contracts_tool_servicii`.`clasa`
    LEFT JOIN  `contracte_setari_739_repeat` AS `contracte_setari_739_repeat_1` ON `contracte_setari_739_repeat_1`.`parent_id` = `contracts_tool_servicii`.`serviciu`
    LEFT JOIN  `contracts_tool_servicii_1291_repeat` AS `contracts_tool_servicii_1291_repeat` ON `contracts_tool_servicii_1291_repeat`.`parent_id` = `contracts_tool_servicii`.`id`
    LEFT JOIN  `contracts_tool_servicii_1251_repeat` AS `contracts_tool_servicii_1251_repeat` ON `contracts_tool_servicii_1251_repeat`.`parent_id` = `contracts_tool_servicii`.`id`
    LEFT JOIN  `contracts_tool_servicii_1250_repeat` AS `contracts_tool_servicii_1250_repeat` ON `contracts_tool_servicii_1250_repeat`.`parent_id` = `contracts_tool_servicii`.`id`
    LEFT JOIN  `contracte_lista_terminale` AS `contracte_lista_terminale` ON `contracte_lista_terminale`.`terminal` = `contracts_tool_servicii_1250_repeat`.`accesoriu_optiune`
    LEFT JOIN  `contracte_setari_sectiuni` AS `contracte_setari_sectiuni` ON `contracte_setari_sectiuni`.`id_sectiune` = `contracts_tool_servicii_1250_repeat`.`tip_produs`
    LEFT JOIN  `contracts_tool_servicii_1265_repeat` AS `contracts_tool_servicii_1265_repeat` ON `contracts_tool_servicii_1265_repeat`.`parent_id` = `contracts_tool_servicii`.`id`
    LEFT JOIN  `contracte_setari_745_repeat` AS `contracte_setari_745_repeat` ON `contracte_setari_745_repeat`.`id` = `contracts_tool_servicii`.`optiuni_abonament_trei`
    LEFT JOIN  `contracte_setari_745_repeat` AS `contracte_setari_745_repeat_0` ON `contracte_setari_745_repeat_0`.`id` = `contracts_tool_servicii`.`optiuni_abonament_doi`
    LEFT JOIN  `contracte_setari_745_repeat` AS `contracte_setari_745_repeat_1` ON `contracte_setari_745_repeat_1`.`id` = `contracts_tool_servicii`.`optiuni_abonament_unu`
    running this query in database will return over a 1 milion rows..hence the memory limit. Adding a where condition like "where contracts_tool_servicii.id = 123456" will decrease the result to 10 rows.

    My question is: where is the "where" condition?

    Thank you
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Good question. I dunno.

    -- hugh
     
  3. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Out of interest, does your list display OK?

    What is the 'display' number set to for the list (in list nav settings, where you define how many rows to paginate).

    -- hugh
     
  4. mirceat

    mirceat Member

    Level: Community
    Yes, list view is displaying ok but is kinda slow (almost 3-4 seconds to load 636 pages with 10 rows per page)..so 10 is the value set for the display number
     
  5. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Hmmm. OK, I'll try and replicate the issue.

    So this is when you delete from within a form view with the Delete button? Does it work OK if you delete from list view?

    -- hugh
     
  6. mirceat

    mirceat Member

    Level: Community
    Yes, it happen only when i press the delete button from form view; works fine (and quick) if i delete the row from list view.

    Thank you
     
  7. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    OK, what's odd here is that the 'table:mergeJoinedData get ids' query is always going to be the same, regardless of whether it's happening in a list view, delete from list or delete from form. The 'where' part is only going to include whatever filters are currently in effect (pre-filters, element list filters, etc).

    (the following isn't necessary to understand, just background stuff, and me working through the issue in my head)

    What that query does is figures out the pagination details where you have enabled 'merge' (or 'merge and reduce') for your joined data, such that we are only displaying the parent table rows, with the joined data merged down into the parent row.

    Visually, for the list display, it's like this ... without merge ...

    parent PK, child 1 PK, child 2 PK
    1, 1, 1
    1, 1, 2
    1, 1, 3
    1, 2, 1
    1, 2, 2
    1, 2, 3

    ... simple, six rows, as returned by a query with joins. But when merging, that becomes ...

    parent PK, child 1 PK, child 2 PK
    1, (1,2), (1,2,3)

    ... a single row, with 6 rows from the data query condensed (merged) into a single display row.

    So we can't do the normal pagination approach, of just applying a limit to the query, because we don't know how many parent rows will actually be shown. When not merging, then it's simple ... each row the query returns is a List display row ... with multiple rows displaying for each parent that has multiple joined data rows. But with merging, each parent row could have any number of joined data rows, so we have to select all the PKs for the entire table + all joins (that's the __pk_valX stuff in the query debug), then figure out how it all boils down (so 50 of those returned rows might boil down to a single display row, because it's one main table row with a bunch of joined rows being merged). Then we have to build a set of all the main table PKs we need to get our pagination size, and specifically load those ids.

    And the reason we have to do this in a delete operation is we have to know how many (merged) rows got deleted, so we can tweak the session data for the current pagination state. Otherwise you wind up with things like being returned to a list page with 0 records, because the pagination page you are on no longer exists because the table has less rows.

    (ok, deep dive over)

    Anyway ...

    As I said, what puzzles me (and why I asked about your list display) is that it is the same query. It's never going to be constrained to a single row id, it'll only ever have the WHERE clauses for currently active filters that would effect which rows are displayed. It's a pagination query.

    So I'm really at a loss to understand why it works fine in list display or list deletion, but not in form deletion.

    I've stepped through the code for all of those possibilities, and there is no reason for it to act differently.

    So at this point, I'm pretty much stuck. The only way I could proceed would be to get an Akeeba copy of your site, install it locally, and step through the code here to figure out what's going on. But that's at least an hour of billable work, just to get to the point of knowing what's going on ... how long it then takes to fix is obviously unknowable.

    I've already sunk about an hour into this, which is about as far as I can go without billing.

    -- hugh
     
  8. mirceat

    mirceat Member

    Level: Community
    Wow, this is an entire lesson about how Fabrik works :)

    Although I don't have a problem subscribing for a membership plan, I'm not sure i can backup it up and send the website to you (confidentiality issues between me and my client), so for now i will try to play more with filters in list, maybe this will solve the issue.

    Many thanks for help
     
  9. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    We don't have membership / subs any more, we just bill for hours.

    -- hugh
     
  10. mirceat

    mirceat Member

    Level: Community
    Good to know ;)

    fyi, i had to make a minor change in fabrik_elements/databasejoin/databasejoin.php; on line 4131 there is a return that give errors when a multiselect dbjoin element have no value selected and user want to delete the row; the $keys aren't populated and then the delete will fail.

    So i've added a checking before the delete query. I'm not sure is the best way to do it, but it solved my problem.

    Code (Text):

    if ($key > 0) {
            $query = $db->getQuery(true);
            $query->delete($db->qn($join->table_join))->where('id IN (' . implode(',', $keys) .')');

            return $db->setQuery($query)->execute();
    }
     
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community

Share This Page