Updated import into temporary table mod for latest distribution
Hi everyone,
I'm just re-documenting for the latest distributions (11/09A onwards) the modification described at...
http://www.pricetapestry.com/node/2554#comment-10312
...for importing @ALL into a temporary table rather than the live products table; which enables very large sites to be updated with almost no down-time as the final stage of the process to swap the temporary table `products_import` over to become the live `products` table is very quick.
includes/admin.php
Look for the following code on line 366 in 12/10B, 321 in 12/10A, 309 in 11/09A:
$sql = sprintf("INSERT INTO `".$config_databaseTablePrefix."products` SET...and REPLACE this with:
global $importALL;
if ($importALL)
{
$table = "products_import";
}
else
{
$table = "products";
}
$sql = sprintf("INSERT INTO `".$config_databaseTablePrefix.$table."` SETLook for the following code on line 507 in 12/10B, 451 in 12/10A, 441 in 11/09A:
$sql = "DELETE FROM `".$config_databaseTablePrefix."products` WHERE filename='".database_safe($admin_importFeed["filename"])."'";
database_queryModify($sql,$insertId);...and REPLACE this with:
global $importALL;
if ($importALL)
{
$table = "products_import";
}
else
{
$table = "products";
$sql = "DELETE FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";
database_queryModify($sql,$insertId);
}...and finally look for the following code on line 515 in 12/10B, 459 in 12/10A, 449 in 11/09A:
$sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix."products` WHERE filename='".database_safe($admin_importFeed["filename"])."'";...and REPLACE this with:
$sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";Then in scripts/import.php
Look for the following code starting at line 54 in 12/10B,12/10A, 53 in 11/09A:
if ($filename == "@ALL")
{
$sql = "TRUNCATE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "SELECT * FROM `".$config_databaseTablePrefix."feeds`";
if (database_querySelect($sql,$rows))
{
foreach($rows as $feed)
{
if (file_exists("../feeds/".$feed["filename"]))
{
import();
}
}
}
}...and REPLACE this entire block with the following code:
if ($filename == "@ALL")
{
$importALL = TRUE;
$sql = "DROP TABLE IF EXISTS `".$config_databaseTablePrefix."products_import`";
database_queryModify($sql,$result);
$sql = "CREATE TABLE `".$config_databaseTablePrefix."products_import` LIKE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` DISABLE KEYS";
database_queryModify($sql,$result);
$sql = "SELECT * FROM `".$config_databaseTablePrefix."feeds`";
if (database_querySelect($sql,$rows))
{
foreach($rows as $feed)
{
if (file_exists("../feeds/".$feed["filename"]))
{
import();
}
}
}
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";
database_queryModify($sql,$result);
$sql = "DROP TABLE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "RENAME TABLE `".$config_databaseTablePrefix."products_import` TO `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
}Hi David,
Can you tell me if this also works when you run /scripts/cron.php using http invocation? Thanks
Hi Allan,
Here's the corresponding change for scripts/cron.php. Look for the following code at line 77:
$sql = "TRUNCATE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);...and REPLACE with:
$importALL = TRUE;
$sql = "CREATE TABLE `".$config_databaseTablePrefix."products_import` LIKE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` DISABLE KEYS";
database_queryModify($sql,$result);Finally look for the following code at line 94:
print chr(13)."backfilling reviews... ";...and REPLACE with:
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";
database_queryModify($sql,$result);
$sql = "DROP TABLE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "RENAME TABLE `".$config_databaseTablePrefix."products_import` TO `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
print chr(13)."backfilling reviews... ";Cheers,
David.
--
PriceTapestry.com
Thanks for the quick response David, I'll give this a try!
Hi David, I gave this a try and it did work in so far as a temp table is created and then it is renamed once it is finished to pt_products. However, when the table is being created (takes about ~30 min) the website is still down. Do you have any advice on what I might be doing wrong? Thanks
Hi Allan,
Could you email me your modified includes/admin.php and scripts/cron.php and i'll check it out...
Cheers,
David.
--
PriceTapestry.com
Hi David
The products_import table was created but the table wasn't moved to the products table. I think the import stopped before importing all feeds. I think I want to import only modified feed. Will this still work.
Hi Adrian,
That would be a sign of the import process terminating before completion. Prior to implementing this were you able to run an import.php @ALL to completion, and were you using the same method of invocation (e.g. letting import.php run via your cron job / automation script rather than manually)?
Unfortunately the method isn't possible with @MODIFIED as all products have to be imported into the temporary table in order for it to be simply swapped over to become the live table; but if @ALL was working previously we should be able to get to the bottom of why it didn't complete using this mod...
Cheers,
David.
--
PriceTapestry.com
Great Mod David,
Great to have no downtime on a 30,000 product site.
Many thanks
Adrian