Save As Copy - not copying all child records

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
G Scott
Posts: 18
Joined: 2018-02-25 00:02

Save As Copy - not copying all child records

Post by G Scott » 2021-06-22 10:08

I have a table [purchase_orders] and a child table [purchase_order_line_items].

When Save As Copy a record in the purchase_orders table, AppGini should also copy the child records in the purchase_order_line_items table.

The problem is, only some records get copied. Others do not. For example, a purchase order which might have 6 line items. When I try to Save As Copy, only 4 of these line items get copied, the other 2 line items are missed.

What would cause this?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Save As Copy - not copying all child records

Post by jsetzer » 2021-06-22 11:15

Maybe missing permissions ("allow view") on child-records? Just an idea.

Don't know if it helps: the code for copying children can be found in TABLENAME_dml.php. There should be a function named TABLENAME_copy_children($destination_id, $source_id) { ... }
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

G Scott
Posts: 18
Joined: 2018-02-25 00:02

Re: Save As Copy - not copying all child records

Post by G Scott » 2021-06-22 20:17

Hi Jan,

It's definitely not permissions as I am logged in as the owner of the records and also have full read/add/edit/delete permissions for both tables.

I have noticed this is an intermittent issue. Sometimes all child records copy for a given parent purchase order, and other times not. This is the case even if I repeatedly try to Save As Copy on the same purchase order record.

So, this leads me to believe it may be a server configuration issue. The app is hosted on a shared hosting platform with some sort of load balancing technology.

Thanks for your advice, I'll take a look at that function and see if any changes can be made to increase reliability.

Best regards
-Grant

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Save As Copy - not copying all child records

Post by rpierce » 2021-06-22 23:30

I'm also having trouble with Save as Copy not working. It just won't copy the child records. Is there something in the database that could break this functionality?

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Save As Copy - not copying all child records

Post by jsetzer » 2021-06-23 06:07

Just in case you have added indexes to the database table manually:

Another reason could be database constraints on the child-table, for example unique constraints on multiple fields like so:

Columns
-id
-parent_id
-position

Index
-UIX_psition on `parent_id` + `position`

Data
id;parent_id;position
1;1;1
2;1;2

When duplicating records #1 and #2, on database-level they can not be inserted due to unique constraint on parent_id + position
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Save As Copy - not copying all child records

Post by rpierce » 2021-06-23 21:02

JS,

You're right on with the indexes causing trouble with the "Save as Copy". I had an issue with the feature once before and found that it was database related. Based on your hint, I looked at my database again and figured out what was causing Save as Copy not to function. I'm posting this just in case someone else cane benefit.

It seems that when the MySQL database is generated by AppGini (at least in my case) on the server, MySQL assigns indexes to most of the fields that I had set in AppGini as lookup (auto fill) fields. These indexes aren't anything I configured or wanted. They were disallowing many of my records from being imported via CSV. I set up a completely new directory and regenerated my app then FTP'd it to the new directory. When I checked the structure of the newly created database, there they were! A bunch of indexes I didn't want. So, I deleted the unwanted indexes, ensuring that I maintained the Primary index as configured in AppGini. Bingo! Everything is working fine now.

I keep learning from you all here in the forum and AppGini. Another puzzle solved for me...!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Save As Copy - not copying all child records

Post by jsetzer » 2021-06-24 06:53

Hi,

good to know. I was only guessing possible reasons.

If you remove those indexes manually they will be re-generated automatically next time, I'm afraid. So, possibly this could not be a permanent solution.

Actually, I'm quite surprised.

Having a look at one of my databases, as you have already said, I can see there are also indexes on foreign key columns for every lookup field (+autofill). For performance reasons this totally makes sense.

So, I think AppGini should generate...
  • PRIMARY KEY index
  • UNIQUE INDEXes for "unique" fields
  • INDEXes for lookup (and autofill) fields
But those normal INDEXes are just plain organizational indexes, not unique constraints. Duplicating a record having those should not break the insert command... theoretically.

If have just checked setupIndexes()-function in updateDB.php. This creates an index per column but no multi-column unique indexes. I think normal INDEX constraints for lookups will not be the reason for this unexpected "copy children" behaviour.

Unique constraints on one or more columns could (=should) break the insert command. But I don't see them being generated somewhere.

So, I'm still wondering if there is another reason and I hope others can contribute because I would like to rely on this functionality working 100% of the time.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

rpierce
Veteran Member
Posts: 255
Joined: 2018-11-26 13:55
Location: Washington State

Re: Save As Copy - not copying all child records

Post by rpierce » 2021-06-24 16:14

Maybe I just needed to to refresh my database?? I've been using it since 2017 and have made many iterations of my app. I'll tell you one thing, the import CSV file is a God send!

G Scott
Posts: 18
Joined: 2018-02-25 00:02

Re: Save As Copy - not copying all child records

Post by G Scott » 2021-06-24 20:30

I think I've made some progress with this.

I've increased the value of CURLOPT_TIMEOUT on line 820 of admin/incFunctions.php. It's part of the curl_insert_handler function. I've increased it from 8 to 16.

Save As Copy seems a lot slower now, but stable in that all child records are being copied consistently.

I'm going to confirm this tomorrow on my office internet connection (slow connection) and then I'll gradually reduce the value until the problem reappears. This should allow me to find an optimum value.

Best regards,
Grant

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Save As Copy - not copying all child records

Post by jsetzer » 2021-06-25 06:35

Thanks @Grant for your contribution and trial.

I have seen the curl_batch call in TABLENAME_copy_children function and I was afraid there might be a timing problem due to usage of curl_multi_exec.

i am curious about the result of your trial!
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

G Scott
Posts: 18
Joined: 2018-02-25 00:02

Re: Save As Copy - not copying all child records

Post by G Scott » 2021-07-03 19:41

Hi Jan,

Despite my best efforts to narrow down the problem, I'm no further forward.

I've increased and decreased the CURLOPT_TIMEOUT to various values from 1 to 100. It has no effect on whether all child records are fully copied.

I've throttled my connection in Chrome to 50Kbit/s and 1000ms latency, although the page loads much slower, it doesn't seem to have any effect on the copying of the child records.

Sometimes all child records copy. Other times, only a small proportion of the child records copy. There doesn't seem to be any obvious pattern to it.

I shall keep experimenting and update if I work this out.

Best regards,
Grant

G Scott
Posts: 18
Joined: 2018-02-25 00:02

Re: Save As Copy - not copying all child records

Post by G Scott » 2021-07-09 10:42

Ok, I have had a little bit more time to investigate this.

It looks like it's a problem with the webhosts server config/firewall.

The server error log shows the following 3 errors for every child record that does not get copied:

XXXX.co.uk [Fri Jul 09 11:20:12 2021] [error] [client 000.000.000.000:0] SoftException in Application.cpp:549: Could not execute script "/home/sites/6a/7/ZZZZZZZZZ/public_html/YYYYYY/purchase_order_line_items_view.php"

XXXX.co.uk [Fri Jul 09 11:20:12 2021] [error] [client 000.000.000.000:0] Caused by SystemException in API_Linux.cpp:444: execve() for program "/usr/php74/usr/bin/php-cgi" failed: Resource temporarily unavailable

XXXX.co.uk [Fri Jul 09 11:20:12 2021] [error] [client 000.000.000.000:0] End of script output before headers: purchase_order_line_items_view.php


I've raised a ticket with the webhost to find out why this is happening.

Post Reply