• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 Delete All Posts By Guests

DRE

Well-known member
#3
My question is related to: http://xenforo.com/community/threads/xenforo-1-2-to-xenforo-1-2-importer-error.56494/
Server Error
Mysqli statement execute error : Column 'last_message_user_id' cannot be null
So I was looking at this thread: http://xenforo.com/community/thread...conversation_recipient-user_id-to-zero.54996/

and it made me realized that maybe all I need to do is create an account that no one will use and just change the user ids from 0 to 1. I would do the same for profile posts and messages. So all I would need to do is search by user id and replace it with the user ID of the new user I made. Once I do that, I can probably continue importing. The new user id will be a user called 'Guest'.

What's the query to change a user id in profile posts, conversations and messages?
 

DRE

Well-known member
#4
This is how I change a user id and username with someone else for both posts and threads.

Got this from another thread:
Code:
UPDATE xf_post SET user_id = 1319 WHERE username ='';
UPDATE xf_thread SET user_id = 1319 WHERE username ='';
I've altered it to look sort've like what I need. Would this work? 0 is the null account of a spammer who was deleted and 599 being the Guest account I created.

Code:
UPDATE xf_post SET user_id = 0 WHERE username ='Guest';
UPDATE xf_thread SET user_id = 0 WHERE username ='Guest';
UPDATE xf_post SET user_id = 0 WHERE user_id = 599;
UPDATE xf_thread SET user_id = 0 WHERE user_id = 599;
Too scared to run this lol.
 

DRE

Well-known member
#5
Been looking at 'Dealing With Forum Spam' by @Jake Bunce http://xenforo.com/community/resources/dealing-with-forum-spam.980/

Sorta close to what I need http://xenforo.com/community/threads/deleted-a-user-but-the-name-still-shows-up.35531

And from this thread: http://xenforo.com/community/threads/deleted-a-user-but-the-name-still-shows-up.35531/

You can run these queries on your database to change the name on the posts from the deleted user:

Code:
UPDATE xf_post
SET username = 'new name'
WHERE username = 'old name'
AND user_id = 0;

UPDATE xf_thread
SET username = 'new name'
WHERE username = 'old name'
AND user_id = 0;
I don't know their user name and just want to change it to Guests and since they are already a guests with 0 as their user id can I change their user id to an account I created?
 

DRE

Well-known member
#6
What if you don't know their user name and just want to change it to Guests and since they are already a guests with 0 as their user id can you change their user id to an account you created?
Yes you can. It's just a simple update query.
What's the query?
To change the user_id?

Code:
UPDATE xf_post
SET user_id = 5
WHERE user_id = 0;
I created a new guest account whose user id is 1688 then ran this query:

Code:
UPDATE xf_post
SET user_id = 1688
WHERE user_id = 0;
Result: 155 rows affected. ( Query took 0.0052 sec )

So next I tried this with threads.

Code:
UPDATE xf_thread
SET user_id = 1688
WHERE user_id = 0;
Result: 14 rows affected. ( Query took 0.0166 sec )

Hmm ran into an error when I tried the following code:

Code:
UPDATE xf_conversation_user
SET user_id = 1688
WHERE user_id = 0;
#1054 - Unknown column 'user_id' in 'where clause'

What's that mean? Same error also occurs in xf_liked_content
Oh wait, the user id is different. It's like_user_id

and I figured out the xf_conversation_user error. It must be:

Code:
UPDATE xf_conversation_user
SET owner_user_id = 1688
WHERE owner_user_id = 0;
and
Code:
UPDATE xf_conversation_user
SET last_message_user_id = 1688
WHERE last_message_user_id = 0;
K I'm getting the hang of this.
 
Last edited:

DRE

Well-known member
#7
Even if you try to run this query:
Code:
UPDATE xf_post
SET user_id = 1688
WHERE user_id = 0;
It doesn't work all the time because even if you deleted the user their account user name AND user id still shows.

WTF. That may have been a bug from an earlier version (buggier version) of Xenforo. Oh wait...no I figured it out.

This is probably because of that Floris delete zero posters addon.
 

DRE

Well-known member
#8
So if a post is made by an account that has been deleted but their user id still shows in the database when it's not supposed to, the only way to delete that account is to do it manually. I think I already have queries for that... which leads me to my next question, how do I find a post in the database just by entering the post ID?
 
Last edited: