[SOLVED] How do I rebuild the assets table

Need help upgrading your Joomla! website or converting to Joomla! 2.5?
Note: All 1.6 and 1.7 releases have reached end of life and should be updated to 2.5.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
prestonfm
Joomla! Apprentice
Joomla! Apprentice
Posts: 19
Joined: Wed Sep 08, 2010 4:45 am

[SOLVED] How do I rebuild the assets table

Postby prestonfm » Sat Mar 10, 2012 9:09 pm

I used jUpgrade to move from 1.5 to 1.7. At first I thought everything had worked and that the upgrade was successful. Then my publishers started complaining they couldn't edit articles. That ultimately led me to the assets table where I discovered the whole table was screwed up.

I don't seem to be alone in this as I've seen the following related posts:

I even bought ACL Manager to try and sort this out. It did a good job of showing everything was hosed but it's a read only comp.

I've manually fixed all the parent_id and levels. However, I still have articles that can't be edited by the publisher. I suspect the problem is now with the lft and rgt columns (would those cause this if they had the wrong values?).

Basically it looks like the assets table can be completely rebuilt using the content and categories tables. Does anyone know of a way to rebuild assets from scratch?

I've been trying to use Asset Weaver but it doesn't seem to work. I've been trying to fix / modify it, but I have very little PHP experience so this has been a really painful process. Any help is appreciated!!!
Last edited by prestonfm on Sun Mar 11, 2012 6:53 am, edited 2 times in total.

prestonfm
Joomla! Apprentice
Joomla! Apprentice
Posts: 19
Joined: Wed Sep 08, 2010 4:45 am

Re: How do I rebuild the assets table

Postby prestonfm » Sun Mar 11, 2012 6:52 am

After a lot of digging I finally got my problem resolved.

*I need to rant for a minute. I find it very frustrating that Joomla hasn't made the upgrade process easier and more fault tolerant. I know it's open source, but still, if you're going to release a product, it should work, especially the critical aspects.*

I was able to fix the parent_id stuff rather easily just editing the records. The real challenge was the lft and rgt calculations. Almost all of my records had the wrong values.

The real key to fixing this was Joomla Bug 25699. There are 3 solutions in there for recalculating the lft and rgt values. The most promising seems to be assetweaver but I just couldn't get it to work. What finally worked for me was the 2nd solution. It's not very elegant because each level required copying and pasting the same code over and over, but it generated the right values.

Here's the actual script I ran. Hopefully this helps someone else. If you have more than 5 levels you'll have to modify it.

Code: Select all

<?php
$dbname = '';
$user = '';
$pwd = '';

$dbc = mysql_connect('',$user,$pwd);
mysql_select_db($dbname,$dbc);


$i = 0;
#childs level1
$c_childs1= mysql_query("SELECT * from j17_assets WHERE parent_id=1");

while($rowchild1 = mysql_fetch_array($c_childs1)) {
        $i=$i+1;
        $updatelft = "UPDATE j17_assets set j17_assets.lft = ".$i." WHERE id=".$rowchild1['id'];
        echo $updatelft."<br>";
        $update = mysql_query($updatelft);
        $query_child2="SELECT * from j17_assets WHERE parent_id=".$rowchild1['id'];
        $c_childs2= mysql_query($query_child2);
        $num_rows = mysql_num_rows($c_childs2);
        if ($num_rows!=0) {
                while($rowchild2 = mysql_fetch_array($c_childs2)) {
                        $i=$i+1;
                        $updatelft = "UPDATE j17_assets set j17_assets.lft = ".$i." WHERE id=".$rowchild2['id'];
                        echo $updatelft."<br>";
                        $update = mysql_query($updatelft);

                        $query_child3="SELECT * from j17_assets WHERE parent_id=".$rowchild2['id'];
                        $c_childs3= mysql_query($query_child3);
                        $num_rows = mysql_num_rows($c_childs3);
                        if ($num_rows!=0) {
                                while($rowchild3 = mysql_fetch_array($c_childs3)) {
                           $i=$i+1;
                           $updatelft = "UPDATE j17_assets set j17_assets.lft = ".$i." WHERE id=".$rowchild3['id'];
                           echo $updatelft."<br>";
                           $update = mysql_query($updatelft);

                           $query_child4="SELECT * from j17_assets WHERE parent_id=".$rowchild3['id'];
                           $c_childs4= mysql_query($query_child4);
                           $num_rows = mysql_num_rows($c_childs4);
                           if ($num_rows!=0) {
                                 while($rowchild4 = mysql_fetch_array($c_childs4)) {
                                    $i=$i+1;
                                    $updatelft = "UPDATE j17_assets set j17_assets.lft = ".$i." WHERE id=".$rowchild4['id'];
                                    echo $updatelft."<br>";
                                    $update = mysql_query($updatelft);
                                    
                                    $query_child5="SELECT * from j17_assets WHERE parent_id=".$rowchild4['id'];
                                    $c_childs5= mysql_query($query_child5);
                                    $num_rows = mysql_num_rows($c_childs5);
                                    if ($num_rows!=0) {
                                       while($rowchild5 = mysql_fetch_array($c_childs5)) {
                                          $i=$i+1;
                                          $updatelft = "UPDATE j17_assets set j17_assets.lft = ".$i." WHERE id=".$rowchild5['id'];
                                          echo $updatelft."<br>";
                                          $update = mysql_query($updatelft);                                    
                                          
                                          $i=$i+1;
                                          $updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i.", level=4 WHERE id=".$rowchild5['id'];
                                          echo $updatergt."<br>";
                                          $update = mysql_query($updatergt);
                                       }   
                                    }
                                    $i=$i+1;
                                    $updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i.", level=4 WHERE id=".$rowchild4['id'];
                                    echo $updatergt."<br>";
                                    $update = mysql_query($updatergt);
                                 }
                           }
                           $i=$i+1;
                           $updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i.", level=3 WHERE id=".$rowchild3['id'];
                           echo $updatergt."<br>";
                           $update = mysql_query($updatergt);
                                }
                        }
                        $i=$i+1;
                        $updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i.", level=2 WHERE id=".$rowchild2['id'];
                        echo $updatergt."<br>";
                        $update = mysql_query($updatergt);
                }

        }
        $i=$i+1;
        $updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i.", level=1 WHERE id=".$rowchild1['id'];
        echo $updatergt."<br>";
        $update = mysql_query($updatergt);

}
$i=$i+354;
echo "root i: ".$i."<br>";
$updatergt = "UPDATE j17_assets set j17_assets.rgt = ".$i." WHERE id=1";
echo $updatergt."<br>";
$update = mysql_query($updatergt);

?>

User avatar
mcsmom
Joomla! Exemplar
Joomla! Exemplar
Posts: 7985
Joined: Thu Aug 18, 2005 8:43 pm
Location: New York
Contact:

Re: [SOLVED] How do I rebuild the assets table

Postby mcsmom » Sun Mar 11, 2012 12:13 pm

http://docs.joomla.org/Fixing_the_assets_table


Tells you how to do it in the UI of 2.5.
So we must fix our vision not merely on the negative expulsion of war, but upon the positive affirmation of peace. MLK 1964.
http://officialjoomlabook.com Get it at http://www.joomla.org/joomla-press-official-books.html Buy a book, support Joomla!.

prestonfm
Joomla! Apprentice
Joomla! Apprentice
Posts: 19
Joined: Wed Sep 08, 2010 4:45 am

Re: [SOLVED] How do I rebuild the assets table

Postby prestonfm » Sun Mar 11, 2012 4:36 pm

Hi mcsmom,

Thanks for replying.

Notice I put that link in my first post. I followed it several times and unfortunately it does not work. It was not recalculating the lft and rgt values.

HDsportsAT
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 130
Joined: Fri Oct 24, 2014 8:26 am
Contact:

Re: [SOLVED] How do I rebuild the assets table

Postby HDsportsAT » Fri Apr 03, 2015 12:01 pm

have you found a solution to solve the problem?
My Joomla-site: http://www.HDsports.at


Return to “Migrating and Upgrading to Joomla! 2.5”

Who is online

Users browsing this forum: No registered users and 12 guests