[SOLVED] How do I rebuild the assets table

[prestonfm]

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:

• Fixing the asset table
  Asset weaver
  Slow queries on large website

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!!!

[prestonfm]

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);

?>

[mcsmom]

http://docs.joomla.org/Fixing_the_assets_table


Tells you how to do it in the UI of 2.5.

[prestonfm]

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]

have you found a solution to solve the problem?