Need help deleting an empty field from a template with 2 million+ pages

[nickie]

The field is empty, the template has over 2 million pages. So of course caution is necessary... I can't try anything too crazy.

When I try to delete the field in admin area, as soon as I click on the confirmation checkbox and submit, it goes straightaway (fraction of a second) to a Bad Gateway 502 (nginx) error page.

I also tried the API solution outlined by kongondo here: https://processwire.com/talk/topic/7480-removing-a-field-from-database/?p=72039 - it does not work, the script just stops running (again very quickly) at the save() part. Also tried deleting from the $fieldgroups api variable instead of from $templates. Same issue.

Here is what I am getting from debug=true:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 72 bytes) in
/www/wire/core/PageFinder.php on line 306

Call Stack:
0.0000 242496 
1. {main}()/www/index.php:0 0.0663 4512544 
2. ProcessPageView->execute()/www/index.php:240 0.0663 4512896 
3. Wire->__call()/www/index.php:240 0.0663 4512896 
4. Wire->runHooks()/www/wire/core/Wire.php:320 0.0663 4515024 
5. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0663 4515360 
6. ProcessPageView->___execute()/www/wire/core/Wire.php:365 0.0721 4647600 
7. Page->render()/www/wire/modules/Process/ProcessPageView.module:172 0.0721 4647784 
8. Wire->__call()/www/wire/modules/Process/ProcessPageView.module:172 0.0721 4647784 
9. Wire->runHooks()/www/wire/core/Wire.php:320 0.0722 4652776 
10. PageRender->renderPage()/www/wire/core/Wire.php:387 0.0722 4653128 
11. Wire->__call()/www/wire/core/Wire.php:387 0.0722 4653128 
12. Wire->runHooks()/www/wire/core/Wire.php:320 0.0722 4655256 
13. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0722 4655592 14. PageRender->___renderPage()/www/wire/core/Wire.php:365 0.0726 4696624 
15. TemplateFile->render()/www/wire/modules/PageRender.module:356 0.0726 4696808 
16. Wire->__call()/www/wire/modules/PageRender.module:356 0.0726 4696808 
17. Wire->runHooks()/www/wire/core/Wire.php:320 0.0726 4698936 
18. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0726 4699104 
19. TemplateFile->___render()/www/wire/core/Wire.php:365 0.0727 4723816 
20. require('/www/site/templates/delete.php')/www/wire/core/TemplateFile.php:169 0.0742 4726296 
21. Fieldgroups->save()/www/site/templates/delete.php:14 0.0742 4726648 
22. Wire->__call()/www/site/templates/delete.php:14 0.0742 4726648 
23. Wire->runHooks()/www/wire/core/Wire.php:320 0.0742 4728464 
24. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0742 4728800 
25. Fieldgroups->___save()/www/wire/core/Wire.php:365 0.0745 4733760 
26. FieldtypeInteger->deleteTemplateField()/www/wire/core/Fieldgroups.php:196 0.0745 4734200 
27. Wire->__call()/www/wire/core/Fieldgroups.php:196 0.0745 4734200 
28. Wire->runHooks()/www/wire/core/Wire.php:320 0.0745 4736336 
29. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0745 4736760 
30. Fieldtype->___deleteTemplateField()/www/wire/core/Wire.php:365 0.0745 4737192 
31. Fields->deleteFieldDataByTemplate()/www/wire/core/Fieldtype.php:878 0.0745 4737632 
32. Wire->__call()/www/wire/core/Fieldtype.php:878 0.0746 4737632 
33. Wire->runHooks()/www/wire/core/Wire.php:320 0.0746 4739464 
34. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0746 4739888 
35. Fields->___deleteFieldDataByTemplate()/www/wire/core/Wire.php:365 0.0754 4743128 
36. Pages->find()/www/wire/core/Fields.php:536 0.0754 4743480 
37. Wire->__call()/www/wire/core/Fields.php:536 0.0754 4743480 
38. Wire->runHooks()/www/wire/core/Wire.php:320 0.0754 4745344 
39. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0754 4745680 
40. Pages->___find()/www/wire/core/Wire.php:365 0.0758 4749608 
41. PageFinder->find()/www/wire/core/Pages.php:199 0.0758 4750048 
42. Wire->__call()/www/wire/core/Pages.php:199 0.0758 4750048 
43. Wire->runHooks()/www/wire/core/Wire.php:320 0.0758 4751864 
44. call_user_func_array:{www/wire/core/Wire.php:365}()/www/wire/core/Wire.php:365 0.0758 4752288 
45. PageFinder->___find()/www/wire/core/Wire.php:365 

Hope this is enough information for one of you seasoned folks to help me out.

Thanks in advance!

[pwired]

2 million+ pages ?? Where did those come from ?

Why not write a database script that deletes those empty fields directly in the database.

[nickie]

Hi pwired,

It's related to a question I posted a couple of months ago (https://processwire.com/talk/topic/8604-how-to-build-pw-site-on-top-of-existing-database-that-keeps-changing/)... it's a feed-reading application with social features and whatnots. The people I am working with (and me most of all) might out of our league here, but I kept pushing them to pick Processwire and boy am I glad I did! One of the important features is searching through the 2+million pages by title etc, and it is really fast. There are of course other options (Solr/ Redis/ ?) if it slows down in another few million pages, but right now it is performing admirably.

As for this empty field - after posting the above question I did poke around in the database, and saw that actually the extra field is taking up only 2-3 rows (in different tables) and an empty table for itself, even though theoretically it is 'added' to millions of pages. Very cool, and I figured I won't sweat it at the moment. (I want to create and delete a test field from a different template first, just to make sure... but it's not going to affect performance until I do, which is a relief.)

I guess the above question has more relevance as a memory leak question maybe. I don't really know how to figure those out, so I'll leave this here in hopes someone who knows how to read the trace can help me understand what exactly happened.

[kongondo]

Yes, looks like a memory issue, but hard to tell what exactly is happening without seeing some code...

[nickie]

kongondo, the code (for my delete.php template, which results in the memory exhaustion) is pretty much verbatim from your snippet in the thread I linked to above: https://processwire.com/talk/topic/7480-removing-a-field-from-database/?p=72039

if ($user->isSuperuser()) {
 
    //first remove the fields from 'user' template before deleting them.
    $t = $templates->get('user');
    $fg = $t->fieldgroup;
    $fg->remove($fields->get('your_field'));
    $fg->save();
 
    //delete the fields
    $f = $fields->get('your_field');
    $fields->delete($f);
 
}

My 'your_field' is a plain integer field, and template ('user' in above code snippet) is the one with millions o' pages.

Thanks for any insight you can provide.

[nickie]

Just wanted to add that deleting the field via admin (with debug on) results in the same issue and similar stack trace. I submit at a URL like /admin/setup/template/removeFields?id=50&fields=119 (confirm delete and submit page) and then get stuck at /admin/setup/template/removeFields which shows the following:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 72 bytes) in 
www/wire/core/PageFinder.php on line 307 

Call Stack: 0.0000 249512 
1. {main}() www/index.php:0 0.0708 4524720 
2. ProcessPageView->execute() www/index.php:240 0.0708 4525072 
3. Wire->__call() www/index.php:240 0.0708 4525072 
4. Wire->runHooks() www/wire/core/Wire.php:320 0.0708 4527200 
5. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.0708 4527536 
6. ProcessPageView->___execute() www/wire/core/Wire.php:365 0.0841 4898216 
7. Page->render() www/wire/modules/Process/ProcessPageView.module:172 0.0841 4898400 
8. Wire->__call() www/wire/modules/Process/ProcessPageView.module:172 0.0841 4898400 
9. Wire->runHooks() www/wire/core/Wire.php:320 0.0842 4903392 
10. PageRender->renderPage() www/wire/core/Wire.php:387 0.0842 4903744 
11. Wire->__call() www/wire/core/Wire.php:387 0.0842 4903744 
12. Wire->runHooks() www/wire/core/Wire.php:320 0.0842 4905872 
13. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.0842 4906208 
14. PageRender->___renderPage() www/wire/core/Wire.php:365 0.0846 4947200 
15. TemplateFile->render() www/wire/modules/PageRender.module:356 0.0846 4947384 
16. Wire->__call() www/wire/modules/PageRender.module:356 0.0846 4947384 
17. Wire->runHooks() www/wire/core/Wire.php:320 0.0846 4949512 
18. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.0846 4949680 
19. TemplateFile->___render() www/wire/core/Wire.php:365 0.0847 4974624 
20. require('www/site/templates/admin.php') www/wire/core/TemplateFile.php:169 0.0848 4975272 
21. require('www/wire/modules/AdminTheme/AdminThemeReno/controller.php') www/site/templates/admin.php:15 0.0848 4975824 
22. require('www/wire/core/admin.php') www/wire/modules/AdminTheme/AdminThemeReno/controller.php:13 0.0874 5362888 
23. ProcessController->execute() www/wire/core/admin.php:93 0.0874 5363072 
24. Wire->__call() www/wire/core/admin.php:93 0.0874 5363072 
25. Wire->runHooks() www/wire/core/Wire.php:320 0.0874 5365200 
26. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.0875 5365368 
27. ProcessController->___execute() www/wire/core/Wire.php:365 0.8661 5455992 
28. ProcessTemplate->executeRemoveFields() www/wire/core/ProcessController.php:213 0.8667 5495960 
29. Fieldgroup->save() www/wire/modules/Process/ProcessTemplate/ProcessTemplate.module:1977 0.8667 5496176 
30. Fieldgroups->save() www/wire/core/Fieldgroup.php:318 0.8667 5496528 
31. Wire->__call() www/wire/core/Fieldgroup.php:318 0.8667 5496528 
32. Wire->runHooks() www/wire/core/Wire.php:320 0.8667 5498344 
33. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.8667 5498680 
34. Fieldgroups->___save() www/wire/core/Wire.php:365 0.8670 5503608 
35. FieldtypeInteger->deleteTemplateField() www/wire/core/Fieldgroups.php:196 0.8670 5504048 
36. Wire->__call() www/wire/core/Fieldgroups.php:196 0.8670 5504048 
37. Wire->runHooks() www/wire/core/Wire.php:320 0.8670 5506184 
38. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.8670 5506608 
39. Fieldtype->___deleteTemplateField() www/wire/core/Wire.php:365 0.8670 5507040 
40. Fields->deleteFieldDataByTemplate() www/wire/core/Fieldtype.php:878 0.8670 5507480 
41. Wire->__call() www/wire/core/Fieldtype.php:878 0.8671 5507480 
42. Wire->runHooks() www/wire/core/Wire.php:320 0.8671 5509312 
43. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.8671 5509736 
44. Fields->___deleteFieldDataByTemplate() www/wire/core/Wire.php:365 0.8891 5512848 
45. Pages->find() www/wire/core/Fields.php:536 0.8891 5513200 
46. Wire->__call() www/wire/core/Fields.php:536 0.8891 5513200 
47. Wire->runHooks() www/wire/core/Wire.php:320 0.8892 5515064 
48. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.8892 5515400 
49. Pages->___find() www/wire/core/Wire.php:365 0.8895 5519328 
50. PageFinder->find() www/wire/core/Pages.php:199 0.8895 5519768 
51. Wire->__call() www/wire/core/Pages.php:199 0.8895 5519768 
52. Wire->runHooks() www/wire/core/Wire.php:320 0.8895 5521584 
53. call_user_func_array:{www/wire/core/Wire.php:365}() www/wire/core/Wire.php:365 0.8895 5522008 
54. PageFinder->___find() www/wire/core/Wire.php:365

I thought maybe the issue is that there is no page with the data (database table of field I am trying to delete is empty) so maybe there is some circular reference or something (I heard this can cause memory exhaustion)... but setting a few pages with some arbitrary value for the field didn't make any difference.

[bfncs]

I don't know where your memory error comes from, but your application is already stuffing >256MB inside your RAM. You should definitely start searching for the actual cause of that problem, or your application might run into this on other occasions. Are you fetching all your 2m+ records at some place? Install Xdebug and use the profiler to analyze what happens before you run into serious problems.

That said, your problem of getting rid of a field can easily be solved by directly working with SQL commands on the database. You'll need shell access to the mysql command line tool or a tool like phpMyAdmin or Adminer to execute arbitrary SQL in your database. You probably already know a way to get this done so I'll leave it to you, how you do it.

Before you do anything: do yourself a favor and dump the contents of your database and save that backup somewhere safe for later. Never work on a live instance of your application, especially not without a backup and a tested way to reset your database from it.

Ok, now let's get to the work. Field data is, as you already mentioned, only saved in three places of the database:

• the actual content data of the pages for field is saved in it's own table (called field_fieldname),
• the field settings are saved in the fields table, and
• the association between a field and a fieldgroup and therefore a template is saved in the fieldgroups_fields table.

So, let's get rid of it all. Let's assume that your field is called your_field. To keep things simple (without any joins) we'll get the id of your field in the first step and use it later as your_field_id.

# Get the ID of your field. Note it down somewhere, you'll need it later
SELECT `id`  FROM `fields` WHERE `name` = 'your_field';

# Delete field data from fields table by it's ID
DELETE FROM  `fields` WHERE  `id` =  'your_field_id';

# Delete field associations from fieldgroups_fields table by the field's ID
DELETE FROM `fieldgroups_fields` WHERE `fields_id` = `your_fields_id`;

# Now drop the table created for your field called field_your_field
DROP TABLE  `field_your_field`;

That should have been all that was needed.

With kind regards

Marc

[nickie]

Thanks for the SQL code, Marc. And your warning is well heeded - backup and Xdebug are in order. I haven't run into the error in other places yet, but you are right, it's foolish to wait for that to happen before I deal with it.

[ryan]

If this were a files/images field and you had 2m pages of data, you might have to take extra measures. But from what I can see, you are just removing an integer field. ProcessWire should be able to do this quickly, so long as you are running an up-to-date version. What version are you running?

Have a look in /wire/core/Fields.php at the method ___deleteFieldDataByTemplate(). That's what is executing this. It has two options, the slow one, and the fast one. The slow one cycles through each page with the field and asks the Fieldtype module to handle the deletion. This is required for fields that have other assets that might need to be deleted too, like files or images. But for a field like an integer, it can use the fast method, which goes directly into the database and deletes it all with one query (which should just take a second). 

My guess is that the version of PW you are running is old enough to only have the slow method, or that for some reason or another the slow method is getting used when it shouldn't. The slow method would almost certainly fail in trying to deal with 2m pages, so I'm pretty sure that's what we're seeing here. 

So check your PW version. You may be able to resolve this simply by upgrading. If that's not it, let me know and we can look at other options. 

[nickie]

Ryan, thanks for pointing me in the exact direction I needed to go!

The PW version was not the issue, as I was using 2.5.21 dev. The file you linked to (Fields.php) was the exact source of the memory exhaustion, specifically on a line (caught in the trace too) inside the slow method:

if($numPages <= 200 || $hasDeletePageField) {
	// not many pages to operate on, OR fieldtype has a custom deletePageField method, 
	// so use verbose/slow method to delete the field from pages
			
	$items = $this->wire('pages')->find($selector); // << the problematic line, since I have 2.3million pages!
        
        //iterate through items etc

}

The test for the slower method was proving true because of $numPages, which is defined as

$numPages = $this->getNumPages($field, array('template' => $template)); 

which calculates the number of pages which has the field set. So since the field was empty in all pages, numpages was turning out to be zero.

I set the slow delete condition to 'false' temporarily, and was able to delete the field via admin in a heartbeat.

Since the actual process of page-by-page field deletion doesn't make use of numPages at all, I'm thinking maybe it should be changed to include all pages of the template? Unless I'm missing some other use for it.

In any case, thank you so much for the reply, sorted me right out.

[ryan]

Since the actual process of page-by-page field deletion doesn't make use of numPages at all, I'm thinking maybe it should be changed to include all pages of the template? Unless I'm missing some other use for it.

You are absolutely right. Either that, or the find() that locates those pages should only include the pages that have a value set. I will go in and update this now. Thank you for going into the detail you did here, as I had not considered that issue before so this is a big help.