Is there a way to rebuild post/profile-post like caches?

Jaxel

Jaxel

Well-known member
I just deleted an alt user who went on a liking rampage... I don't want his username, which was offensive, appearing under every post. Is there a way to rebuild the like caches?
 
Sort by date Sort by votes
You are hitting an internal condition of the MySQL database where you cannot perform a query because the table is locked for too long. Rerun the query. If this constantly happens, it may be worth talking to your host to triage and find a root cause.
 
Upvote 0 Downvote
eagle eyes said:
Getting this.

Code: 
An exception occurred: Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction in /home/xxx/public_html/library/Zend/Db/Statement/Mysqli.php on line 214

Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in /home/xxx/public_html/rebuildlikes.php at line 39
Click to expand...

That's a server limit.

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error. If you don't have root access then you need to ask your host or server person to do this.

For reference:
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
 
Upvote 0 Downvote
Getting this error when running the rebuild file

Rebuilding like totals for users . . . done!

Rebuilding like totals for profile_posts . . . done!
Rebuilding serial cache for profile_posts . . . done!

Rebuilding like totals for posts . . . An unexpected database error occurred. Please try again later.
Click to expand...

This is a test conversion of a vb3.8 forum with the thank you hack, 1.4 million posts around 300,000 likes
User total likes seem ok but no likes that i can find are showing in threads.

EDIT:
checking mysql error log shows

150717 0:02:17 InnoDB: ERROR: the age of the last checkpoint is 9433668,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
Click to expand...

Thanks in advance
 
Last edited:
Upvote 0 Downvote
Jake Bunce said:
Then you can make a small modification to this script for your purposes. Add the red code:

Rich (BB code): 
$contentTypes = array(
    'profile_post',
    'post'
);

echo "Rebuilding like totals for users . . . ";

// REBUILD LIKE TOTALS FOR USERS
$db->query("
    UPDATE xf_user AS u
    SET u.like_count = (
        SELECT COUNT(*)
        FROM xf_liked_content AS lc
        INNER JOIN xf_user_authenticate AS ua ON (ua.user_id = lc.like_user_id)
        WHERE lc.content_user_id = u.user_id
        AND lc.content_type IN (" . $db->quote($contentTypes) . ")
    )
");

That will make it so the user counts only include likes for posts and profile posts.
Click to expand...

Jake, I hate to be a pain but is there an easy way to modify this so that it only rebuilds likes for one user?
 
Upvote 0 Downvote
Upvote 0 Downvote
Jake Bunce said:
That's a server limit.

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error. If you don't have root access then you need to ask your host or server person to do this.

For reference:
MySQL :: MySQL 5.0 Reference Manual :: 14.2.2 InnoDB Startup Options and System Variables
Click to expand...

Hosting company rejected to increase the limit !
Any chance to do it in steps? I used your method to import > 350,000 likes but I cannot rebuild the cache for this huge amount in one step (Request Timeout)

Any suggestion @Jake Bunce ? :)
 
Upvote 0 Downvote
I have a 500.000 postings forum imported from vBulletin 3.8x and rebuild-scrips runs very, very long. I set the browser timeout to one hour, which wasn't enough. But the user thanks seems to be okay.

Question: Do I loose some important stuff when the script runs in a timeout after an hour? Or do I get something like an inconsistent database?
And: Can I run the script again later?
 
Upvote 0 Downvote
vFranky said:
I have a 500.000 postings forum imported from vBulletin 3.8x and rebuild-scrips runs very, very long. I set the browser timeout to one hour, which wasn't enough. But the user thanks seems to be okay.

Question: Do I loose some important stuff when the script runs in a timeout after an hour? Or do I get something like an inconsistent database?
And: Can I run the script again later?
Click to expand...

That script can be intensive, but 1 hour is way longer than I would expect. The process might be crashing.

Yes, it is safe to run the script again any number of times. There is no real danger other than server load. An incomplete run will just leave some like counts not yet rebuilt.
 
Upvote 0 Downvote
How can we achieve a similar thing in XF 2.0? I've imported a number of likes from VB's Thank you Add-on. However, I now need to recalculate the xf_posts.likes and xf_posts.like_users values.

Thanks
 
Upvote 0 Downvote
Jake Bunce said:
Minor fix. I added inner joins so that nonexistent users are excluded. XenForo doesn't delete the like records when you delete a user, so I have now accounted for that.
Click to expand...
when i run the script, for example xenforo.com/rebuildlikes.php it just instantly reload xenforo.com and nothing happens

any help is appreciated

edit: i had another cron running, so i think now its doing something, its been trying to load the php file for the last 5 mins so its working hopefully
 
Upvote 0 Downvote
Hey guys
I wanted to import old likes from phpBB into xenForo. I managed to insert the like data into xf_reaction_content. Sadly, xf_post must be updated manually. I came across this script as a good base for my modifications. Since I only have likes (no other reactions) and only on posts (no profile posts) the import would be even easier.
Maybe its a very stupid question but I did not find any information about that. The questions is: how can I access the xenForo db adapter from within a simple php-script. I tried the script provided but hence I am on xenForo 2 there must be a different autoloader or required script.
I appreaciate any help.
Thanks and have a nice day
 
Upvote 0 Downvote
Hey guys

Just wanted to let you know, that after hours of work I finally managed to get it working. I used a simple mysqli-connection. I thought it was not possible because I serialized like arrays instead of json_encode.

I wrote a small script which is able to rebuild the reactions and reaction users. I wrote it only to rebuild initially (it can't rebuild oder like types or post categroies such as wall posts). Requirement is that the thanks data is already exists in table "xf_reaction_content".
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

Stuart Wright
  • Solved
XF 2.2 Is there a list of CLI commands to run the rebuild caches routines?
Replies
2
Views
301
Stuart Wright
Stuart Wright
Mouth
  • Question Question
XF 2.2 Splitting a thread FROM a post?
Replies
0
Views
94
Mouth
Mouth
L
  • Question Question
XF 2.2 Is there a way to re-order images
Replies
1
Views
97
Paul B
Paul B
BlueGreen91
  • Question Question
XF 2.2 Is there a way to enable comments for the approval queue?
Replies
0
Views
168
BlueGreen91
BlueGreen91
Ariel Schnee
  • Question Question
XF 1.5 Is There A Way To Automatically Restrict The Thread To The Original Poster?
Replies
2
Views
255
Mr Lucky
Mr Lucky
Top Bottom