Database query needed

[RichieBoy67]

Hey all,

The issue is missing posts. Thread rows, attachments, etc are all there. Normally I would just search the missing post ids and export them as data and just insert them or find the last post id.

In this case though there has been weeks of posting after the data was removed so what I need to do is figure out how to insert just the missing posts without touching the data that is there already.

I have been going by the urls and thread ids and I have fixed a few threads but I need to do this in mass and I do not want to take any chances so can anyone please help me out here??

Thanks,
Rich

[Dave]

Your question is kind of confusing to me.
You are missing posts but want to import them back into the database? Where do you get the missing posts from? From an existing backup?

[RichieBoy67]

Yes, Exactly. :)

--------------- Added 1441090577 at 1441090577 ---------------

Just to elaborate.

Yes I have a back up that contains the missing posts. The threads are there, it is just posts that are missing.

I have been using google webmaster tools, crawl errors to find the missing posts. I have been getting the thread id from the url and pasting it into the post table search of the back up database which is imported into phpadmin.

I then export only the data and insert it into the post table of the original database. This method works but takes too long.

What I want to do if possible is insert all of the back up post table but only insert the missing or empty tables.

Thanks

[Dave]

The easiest way would be to make a PHP script for this.

1. Connect to both databases, the current live one and the backup one.
2. Using basic SELECT queries you check if posts of the backup database exist in the live database.
3. If not, insert it into the table.

[squidsk]

In order to make dave's suggestion work, you'd need to load the backup of your db into a new database on your mysql server and make sure whatever user has appropriate permissions on the new db.

[Lynne]

You may want to check out this post - http://www.vbulletin.com/forum/showthread.php?326281-Recreating-a-deleted-thread-number&p=1845274&viewfull=1#post1845274:

Try this, restore the backup to a different database and using the operations tab in phpmyadmin, rename the thread and post tables to something with a number on the end (post7 and thread7) then copy those 2 tables to the live database.
Run the following queries changing 777 to the threadid of the deleted thread.
Code:

INSERT INTO thread
(SELECT *
FROM thread7
WHERE threadid = 777)

Code:

INSERT INTO post
(SELECT *
FROM post7
WHERE threadid = 777)

[RichieBoy67]

Thanks,

Dave, can you give me an example of the query?

Squid, Yes.. as mentioned I already have the back up database imported into phpadmin. :)

Lynn, that is basically what I am doing but I am trying to automate the process rather than do each post one at a time.

[squidsk]

You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))

INSERT INTO thread
(SELECT *
FROM thread7
WHERE NOT threadid IN (SELECT threadid FROM thread))


EDIT: You have to do the post query first otherwise the deleted thread will already be restored in the thread table.

[Lynne]

Just want to add.... make a database backup before doing this!!!! Even better, make a database backup, set up a test site and then figure out the best method to do this.

[RichieBoy67]

You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))INSERT INTO thread
(SELECT *
FROM thread7
WHERE NOT threadid IN (SELECT threadid FROM thread))
EDIT: You have to do the post query first otherwise the deleted thread will already be restored in the thread table.
I am very confused by these. I do not know where to begin with the script. I have the first query run first on which database?

Thanks Guys

Lynne, You are not kidding! :)

[squidsk]

I am very confused by these. I do not know where to begin with the script. I have the first query run first on which database?

Thanks Guys

Lynne, You are not kidding! :)

Sorry I've reformated the queries to make it more clear which db is which

This should restore all posts removed from the livedb that are in the backupdb that do not belong to threads that still exist on your live db. (i.e. if you deleted a post from a thread but the thread is still on the live db it won't be restored)
INSERT INTO libedb.post
(SELECT *
FROM backupdb.post
WHERE NOT threadid IN (SELECT threadid FROM livedb.thread))

The second query restores all threads that have been deleted.
INSERT INTO livedb.thread
(SELECT *
FROM backupdb.thread
WHERE NOT threadid IN (SELECT threadid FROM livedb.thread))


The results obviously won't include threads/posts deleted before the backup was made.

[RichieBoy67]

Thanks,

The issue though is not missing or deleted threads.. They are there, it is missing posts only. I am not sure what happened to them but it was done using impex.

Bottom line it is only posts that need to be imported.

Thanks so much for your help.

[squidsk]

Then just use the first query but take out the NOT in the where clause.

[RichieBoy67]

Then just use the first query but take out the NOT in the where clause.
Thanks! :)

I will try this today.

--------------- Added 06 Sep 2015 at 05:40 ---------------

Ok, this is what I ran on the live database:


INSERT INTO muscle-mecca-BackUp.post
(SELECT *
FROM muscle-mecca-BackUp.post
WHERE threadid IN (SELECT threadid FROM muscle-mecca-BackUp.thread))
and this is the error I got:


#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-mecca-BackUp.post
(SELECT *
FROM muscle-mecca-BackUp.post
WHERE threadid IN ' at line 1
Did I mess this up?

[squidsk]

Three things: first I think you want to be inserting into your live db not your backup db, second I think you want the threadid to be the ones from the live db (i.e. only grab posts for threads that are still in the live db), and third the reason I think you're getting the error is that you might require backticks or double quotes around your db name for the backupdb.

[RichieBoy67]

yes, those are both back ups.. that was the correct database as it is the back up of my main database. The other one is the back up of the back up. :)

I will try with the quotes. :)

--------------- Added 1441600859 at 1441600859 ---------------

Actually I think you may be right either that or I changed the names incorrectly when I copied it here.

--------------- Added 1441602633 at 1441602633 ---------------

I am not sure what I am missing but have tried everything and nothing works..keep getting the syntax error. I believe we need asterisks.

[squidsk]

If that's the query there's a problem with your tables, both the source and destination post tables are the in the same db. So you're inserting all posts from db1 into db1 if the threads are in db1, which doesn't make any sense, so that's likely what the syntax error is about. If you're restoring posts, then you have to restore from one db into the other based off the threads in the destination db.
(i.e. restore posts into db1 from db2 where the threads are in db1) Your current query is doing restore posts into db1 from db1 where threads are in db1, which won't accomplish anything.

[RichieBoy67]

Yes, I changed the databases and still got the errors.I went by your directions.

INSERT INTO "muscle-mecca-BackUp.post"
(SELECT *
FROM meccaaugust-backup.post
WHERE threadid IN (SELECT threadid FROM "muscle-mecca-BackUp.thread")) The august base is the back up. The other is the live.

Your instructions:

INSERT INTO libedb.post (SELECT * FROM backupdb.post WHERE NOT threadid IN (SELECT threadid FROM livedb.thread))

[RichieBoy67]

If anyone can help me finish this I will glady donate a few $.

Keep in mind these are both back up databases that are live in phpadmin. I did not want to run this on my live site until I get it working.

Don't let the "backup" in the database names confuse you. August is the back up.. the other is the one I am trying to add tyhe posts too.

Thanks

[squidsk]

Try changing the query to insert ignore, as insert ignore will ignore constrains like primary key constraints.

[Dave]

What are the exact errors you are facing?
Also, do not use quotes around the database/table name, that is not valid SQL.

[RichieBoy67]

The error is:


INSERT INTO "muscle-mecca-BackUp.post" (SELECT * FROM meccaaugust-backup.post WHERE threadid IN (SELECT threadid FROM "muscle-mecca-BackUp.thread")) MySQL said: https://vborg.vbsupport.ru/
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"muscle-mecca-BackUp.post"
(SELECT *
FROM meccaaugust-backup.post
WHERE threa' at line 1



When I ran this:



INSERT INTO "muscle-mecca-BackUp.post"
(SELECT *
FROM meccaaugust-backup.post
WHERE threadid IN (SELECT threadid FROM "muscle-mecca-BackUp.thread"))



when I ran on the live database:



muscle-mecca-BackUp



Thanks

[Dave]

Yes you get that error because you wrap your database and table name in quotes. Remove the quotes.

[RichieBoy67]

I will rerun it but I got the same type of error before I added those.

--------------- Added 1441967876 at 1441967876 ---------------

Ok,

ran:


INSERT INTO muscle-mecca-BackUp.post
(SELECT *
FROM meccaaugust-backup.post
WHERE threadid IN (SELECT threadid FROM muscle-mecca-BackUp.thread))




Got:


INSERT INTO muscle-mecca-BackUp.post (SELECT * FROM meccaaugust-backup.post WHERE threadid IN (SELECT threadid FROM muscle-mecca-BackUp.thread)) MySQL said: https://74.208.174.189:8443/domains/databases/phpMyAdmin/themes/dot.gif
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-mecca-BackUp.post
(SELECT *
FROM meccaaugust-backup.post
WHERE threadid IN (' at line 1




--------------- Added 1441969060 at 1441969060 ---------------

not sure why but cant edit this

[Dave]

I have a feeling it's messing up because you have dashes in your database name. Is it possible for you to rename it to something else, alphanumeric characters only? Then try it again.

[squidsk]

Try this query. The database needs to be backticked seperately from the table name, and I've added IGNORE after INSERT.



INSERT IGNORE INTO `muscle-mecca-BackUp`.`post`
(SELECT *
FROM `meccaaugust-backup`.`post`
WHERE threadid IN (SELECT threadid FROM `muscle-mecca-BackUp`.`thread`))

[RichieBoy67]

Error says select command denied for user.. I think I know the issue though. I need both databases to use the same user. Right?

Ok, think I got it.. Checking now!

Thanks

--------------- Added 1442031673 at 1442031673 ---------------

Very strange, this did work on the back ups but now I change the name and am getting an error saying the post table doesn't exist.

######.post table doesn't exist This is for the live database, not the back up. I do not understand it.

--------------- Added 1442049184 at 1442049184 ---------------

Ok, I got it. I believe the last issue had something to do with the log for that table. I ended up copying that entire database to a new one and then running the last query there. Perfect!

This actually leads me to a new topic I will be posting about which is how and why my site actually started ranking better and getting more traffic after almost 400,000 posts were removed.

Thanks!