Magento 1.7.02 CE custom url rewrites removed after reindexing “Catalog URL Rewrites”

Question

I migrated our store from Xcart to Magento. In the process, product, category, and static page urls changed. (18,000:530:50 respectively)

Using the following code, I create new urlrewrites for the static pages. When I reindex Catalog URL Rewrites, my custom rewrites disappear. Whats the deal?!?!?!?!

Im working on product and category imports next. Ive seen several threads on StackOverflow and MagentoCommerce about this, but the only workaround offered it to modify Magento core files and remove nested category names from the url. Thats not applicable for my static page url rewrites, and not acceptable for product and category url rewrites.

Any help is GREATLY APPRECIATED.

PS. this is my first question on StackOverflow ;)

<?php
if ($import_custom) {

    echo "\n\n\n*********************************\n\n\n";

    $sql = 'select * from url_rewrite_xcart_to_magento_custom';
    $all_url_pairs = mysqli_submit_get($db_param_petdata, $sql, "0");

    foreach ($all_url_pairs as $url_pair) {

        //  echo $url_pair['old_url'] ."\n";
        //  echo $url_pair['new_url'] ."\n";
            var_export($url_pair);
            echo "\n\n";

        $new_urlrewrite_object = Mage::getModel('core/url_rewrite');
        $new_urlrewrite_object
        ->setIsSystem(0)
        ->setStoreId($store_id)
        ->setOptions('RP')
        ->setIdPath($url_pair['old_url'])
        ->setTargetPath( $url_pair['new_url'])
        ->setRequestPath($url_pair['old_url'])
        ->setDescription('imported aeroflow staff url forwarding list')
        ->save()
        ;
        $new_urlrewrite_object
        ->clearInstance
        ;

        //  var_export($new_urlrewrite_object);
        //  echo "\n\n";

        $existing_urlrewrite_object = Mage::getModel('core/url_rewrite')
        ->setStoreId($store_id)
        ->loadByRequestPath($url_pair['old_url'])
        ;
        var_export($existing_urlrewrite_object);
        echo "\n\n";

        $existing_urlrewrite_object
        ->clearInstance
        ;

        echo "\n\n\n*********************************\n\n\n";




        //  Mage::getModel('core/url_rewrite')
        //  ->setIsSystem(0)
        //  ->setOptions('RP')
        //  ->setIdPath('Imported-Old-Website-URL-test1')
        //  ->setTargetPath('alicia-suspender-skirt-32360')
        //  ->setRequestPath('SHOPONLINE.aspx?Product=Alicia Skirt&amp;Brand=Lascivious Test&amp;PID=1975')
        //  ->save()
        //  ;


        //  Mage::getModel('core/url_rewrite')
        //  ->setIsSystem(0)
        //  ->setStoreId($storeId)
        //  ->setOptions('RP')
        //  ->setIdPath('index.php?cat=c' . $categoryId . '_' . $this->strip($data['name']) . '.html')
        //  ->setTargetPath($categoryModel->getUrlPath() . '.html')
        //  ->setRequestPath('index.php?cat=c' . $categoryId . '_' . $this->strip($data['name']) . '.html')
        //  ->save();

    }
}
?>

Benji Benji · Accepted Answer · 2013-12-02T22:51:57

After an exhaustive search, I found magento's url rewrite system to be unreliable in general. Sometimes, the url rewrite reindexing stalls, and I must truncate the "core_url_rewrite" table by hand.

Also, magento urls are all lower case by default, mysql is case insensitive, but apache is case sensitive. These conditions work against each other.

I therefore wrote a php script that pulls url rewrite data from a mysql table and creates an apache rewrite database file.

Here is my site-specific apache config:

#REWRITES FOR TRANSITION FROM XCART TO MAGENTO WITHOUT LOSING SEO PAGE RANK
RewriteMap xcart_magento dbm:/etc/apache2/sites-available/xcart_magento_rewrites.dbm
RewriteCond ${xcart_magento:$1|NOT_FOUND} !NOT_FOUND
RewriteRule ^/(.*) /${xcart_magento:$1} [R=301]

Here is my script:

<?php

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////  GETTING STARTED
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

require_once '/usr/local/bin/functions.php';
require_once("/home/magento/app/Mage.php");
Mage::app();


$username="xxx";
$password="xxx";
$database_petdata="xxx";
$database_mag="xxx";
$server="localhost";

$db_param_general["server"] =       $server;
$db_param_general["username"] =     $username;
$db_param_general["password"] =     $password;
$db_param_general["database"] =     '';
$db_param_petdata["server"] =   $server;
$db_param_petdata["username"] =     $username;
$db_param_petdata["password"] =     $password;
$db_param_petdata["database"] =     $database_petdata;
$db_param["server"] =       $server;
$db_param["username"] =     $username;
$db_param["password"] =     $password;
$db_param["database"] =     $database_mag;




////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////  MAKE APACHE REWRITE DATABASE FILE
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

$make_txt_file = 1;
if ($make_txt_file) {

    $base_dir = "/etc/apache2/sites-available/";
    $rewrite_map_file_txt = "xcart_magento_rewrites.txt";
    $rewrite_map_file_dbm = "xcart_magento_rewrites.dbm";
    unlink ($base_dir . $rewrite_map_file_txt);
    unlink ($base_dir . $rewrite_map_file_dbm);

    date_default_timezone_set('America/New_York');
    $dt = date(DATE_RSS);

    $file_header  = "##\n";
    $file_header .= "## $base_dir$rewrite_map_file_txt - product, category, and custom url rewrite map database file\n";
    $file_header .= "##\n";
    $file_header .= "## created by Benji Burrell on $dt \n";
    $file_header .= "##\n";

    file_put_contents($base_dir . $rewrite_map_file_txt, $file_header,FILE_APPEND);

    $sql = 'select xcart_url as "old",mag_url as "new" from url_rewrite_xcart_to_magento_categories where xcart_url != mag_url';
    $cat_url_sets = mysqli_submit_get($db_param_petdata, $sql, "0");
    //  echo "-------------- CATEGORY URL SETS\n";
    //  print_r($cat_url_sets);
    foreach ($cat_url_sets as $set) {
        $str = $set["old"] . " " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
        $str = $set["old"] . "/ " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
    }

    $sql = 'select xcart_url as "old",mag_url as "new" from url_rewrite_xcart_to_magento_products where xcart_url != mag_url';
    $prod_url_sets = mysqli_submit_get($db_param_petdata, $sql, "0");
//  echo "-------------- PRODUCT URL SETS\n";
//  print_r($prod_url_sets);
    foreach ($prod_url_sets as $set) {
        $str = $set["old"] . " " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
        $str = $set["old"] . "/ " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
    }

    $sql = 'select old_url as "old",new_url as "new" from url_rewrite_xcart_to_magento_custom where old_url != new_url';
    $custom_url_sets = mysqli_submit_get($db_param_petdata, $sql, "0");
//  echo "-------------- CUSTOM URL SETS\n";
//  print_r($custom_url_sets);
    foreach ($custom_url_sets as $set) {
        $str = $set["old"] . " " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
        $str = $set["old"] . "/ " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
        $str = $set["old"] . ".html " . $set["new"] . "\n";
        file_put_contents($base_dir . $rewrite_map_file_txt, $str,FILE_APPEND);
    }

}

$cmd = "httxt2dbm -i " . $base_dir . $rewrite_map_file_txt . " -o " . $base_dir . $rewrite_map_file_dbm ;

shell_exec($cmd);


$shell_output = shell_exec('/etc/init.d/apache2 restart');
echo "$shell_output";



?>

Hope this helps someone out there!

Magento 1.7.02 CE custom url rewrites removed after reindexing “Catalog URL Rewrites”

1 Answers