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!!!
[SOLVED] How do I rebuild the assets table
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.
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.
-
- Joomla! Apprentice
- Posts: 21
- Joined: Wed Sep 08, 2010 4:45 am
[SOLVED] How do I rebuild the assets table
Last edited by prestonfm on Sun Mar 11, 2012 6:53 am, edited 2 times in total.
-
- Joomla! Apprentice
- Posts: 21
- Joined: Wed Sep 08, 2010 4:45 am
Re: How do I rebuild the assets table
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.
*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
- Joomla! Exemplar
- Posts: 7897
- Joined: Thu Aug 18, 2005 8:43 pm
- Location: New York
- Contact:
Re: [SOLVED] How do I rebuild the assets table
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!.
http://officialjoomlabook.com Get it at http://www.joomla.org/joomla-press-official-books.html Buy a book, support Joomla!.
-
- Joomla! Apprentice
- Posts: 21
- Joined: Wed Sep 08, 2010 4:45 am
Re: [SOLVED] How do I rebuild the assets table
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.
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.
-
- Joomla! Enthusiast
- Posts: 170
- Joined: Fri Oct 24, 2014 8:26 am
- Contact:
Re: [SOLVED] How do I rebuild the assets table
have you found a solution to solve the problem?
My Joomla-site: www.HDsports.at