Answered How can I recalculate "uri" in a large site_content with more than 1.000.000 entries without server timeout for the script?

I have inserted a huge table into site_content via phpmyadmin to create over 1.000.000 resources. I managed it by splitting it in packages a 100.000 entries via "id BETWEEN 0 AND 100000" and so on.



The problem I have now is that neither 'alias' nor 'uri' are generated since it did not went through the modx API.

I allready tried a few things but all failed due to the huge table size and the long script runtime:

1. I place all recources as a child of one resource by setting the default value of 'parent' to the resource id before the sql INSERT INTO. Than I moved the parent in the modx manager to another position in the site tree but this way the API is not able to recalculate all resources and stop after a certain amount of changed URIs

2.I executed a script i found in the forum from OPENGEEK in the root folder of my site

<?php
include 'config.core.php';
include MODX_CORE_PATH . 'model/modx/modx.class.php';
$modx = new modX();

$modx->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');
$modx->initialize('mgr');

$modx->setLogLevel(modX::LOG_LEVEL_INFO);

$modx->exec("UPDATE {$modx->getTableName('modResource')} SET uri = '' WHERE uri_override = 0");

$modx->call('modResource', 'refreshURIs', array(&$modx));

same problem here the table is just to big to run completly through within the given time set by my provider

Has anyone an idea? Maybe a limitation in the php script so I can split it into smaler packages. I am unfortunately not that experienced with SQL or PHP to write it by myself.

[ed. note: sh0ck23 last edited this post 10 years, 10 months ago.]

Do the created resources have something in their "uri" field at all? If not, comment out the $modx->exec() line and see if that helps.

Not sure what you mean whith "created resources".

After I first import the data into site_content the URI has of course no entry or NULL since i didnt import anything via phpmyadmin

When I run the script it is able to create the URI for approx the first 1400 lines, after this I get a time out since the script runtime is limited to something about 90sec from my provider. I deleted the $modx->exec() check testwise anyway but makes no diference.

I meant the imported data into site_content with "created resources".

Maybe adding the below to the top of the script helps to increase the limit, but that's very dependant on your host and I have no idea how long it should take with a million resources:

jsut tried it but doesnt work either. Longer script runtime is prohibited by the provider in general. Also tried something similar before with

how would the correct syntax be if I would tell this script...

<?php
include 'config.core.php';
include MODX_CORE_PATH . 'model/modx/modx.class.php';
$modx = new modX();

$modx->setLogTarget(XPDO_CLI_MODE ? 'ECHO' : 'HTML');
$modx->initialize('mgr');

$modx->setLogLevel(modX::LOG_LEVEL_INFO);

$modx->call('modResource', 'refreshURIs', array(&$modx));

... to execute the script only for the IDs between 1000 and 2000 so something like ( WHERE id BETWEEN 1000 AND 2000 )?

I think i found a dirty but at least a solution that might work but I can not figure out the correct SQL syntax to update it via phpmyadmin

In this SQL the id is not added:




I can only do:


or:


but combining words and the id from the row doesn't work

can anyone tell me the correct syntax? [ed. note: sh0ck23 last edited this post 10 years, 10 months ago.]

You really need to run this from PHP CLI (which does not have execution time restrictions) with that large of a site. Or you could try and find a way to break the original script up into small pieces, perhaps starting with each top-level parent.

Hello.

I've just got similar issue: if you have more than 10000 resources and trying to call 'refreshURIs' the script will reach the max_execution_time limit (120 seconds on my server). Is there any way to speed up this operation? Have somebody found out any workaruond?

There are several suggestions in the posts above. OpenGeek's method would be the most reliable.