Page 1 of 1
[SOLVED] How do I rebuild the assets table
Posted: Sat Mar 10, 2012 9:09 pm
by 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:
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!!!
Re: How do I rebuild the assets table
Posted: Sun Mar 11, 2012 6:52 am
by 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);
?>
Re: [SOLVED] How do I rebuild the assets table
Posted: Sun Mar 11, 2012 12:13 pm
by mcsmom
Re: [SOLVED] How do I rebuild the assets table
Posted: Sun Mar 11, 2012 4:36 pm
by 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.
Re: [SOLVED] How do I rebuild the assets table
Posted: Fri Apr 03, 2015 12:01 pm
by HDsportsAT
have you found a solution to solve the problem?