vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)
-   -   Database query needed (https://vborg.vbsupport.ru/showthread.php?t=320079)

RichieBoy67 08-31-2015 12:21 PM
Database query needed
 
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 08-31-2015 04:36 PM
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 08-31-2015 07:09 PM
Yes, Exactly. :)

--------------- Added [DATE]1441090577[/DATE] at [TIME]1441090577[/TIME] ---------------

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 09-01-2015 09:30 AM
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 09-01-2015 01:24 PM
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 09-01-2015 02:55 PM
You may want to check out this post - http://www.vbulletin.com/forum/showt...1#post1845274:
Quote:
Originally Posted by Lats
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 09-02-2015 07:29 AM
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 09-02-2015 01:24 PM
You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

Code:
INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))
Code:
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 09-02-2015 03:06 PM
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 09-03-2015 03:06 AM
Quote:
Originally Posted by squidsk (Post 2554112)
You can just grab all threads/posts that don't exist. You can alter the above queries as follows:

Code:
INSERT INTO post
(SELECT *
FROM post7
WHERE NOT threadid IN (SELECT threadid FROM thread))
Code:
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 09-03-2015 01:07 PM
Quote:
Originally Posted by RichieBoy67 (Post 2554163)
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)
Code:
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.
Code:
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 09-04-2015 12:45 AM
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 09-04-2015 12:27 PM
Then just use the first query but take out the NOT in the where clause.

RichieBoy67 09-06-2015 12:27 AM
Quote:
Originally Posted by squidsk (Post 2554255)
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:

PHP Code:
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:

PHP Code:
#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 09-06-2015 04:03 PM
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 09-07-2015 02:39 AM
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 [DATE]1441600859[/DATE] at [TIME]1441600859[/TIME] ---------------

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

--------------- Added [DATE]1441602633[/DATE] at [TIME]1441602633[/TIME] ---------------

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 09-07-2015 06:50 PM
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 09-08-2015 02:31 AM
Yes, I changed the databases and still got the errors.I went by your directions.

PHP Code:
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:

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

RichieBoy67 09-09-2015 10:57 PM
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 09-10-2015 06:04 PM
Try changing the query to insert ignore, as insert ignore will ignore constrains like primary key constraints.

Dave 09-10-2015 07:46 PM
What are the exact errors you are facing?
Also, do not use quotes around the database/table name, that is not valid SQL.

RichieBoy67 09-11-2015 06:21 AM
The error is:
PHP Code:

INSERT INTO "muscle-mecca-BackUp.post" (SELECT * FROM meccaaugust-backup.post WHERE threadid IN (SELECT threadid FROM "muscle-mecca-BackUp.thread"))          MySQL said: [img]https://vborg.vbsupport.ru/[/img] 
  #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:

PHP Code:

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:

PHP Code:

muscle-mecca-BackUp 
Thanks

Dave 09-11-2015 08:10 AM
Yes you get that error because you wrap your database and table name in quotes. Remove the quotes.

RichieBoy67 09-11-2015 08:30 AM
I will rerun it but I got the same type of error before I added those.

--------------- Added [DATE]1441967876[/DATE] at [TIME]1441967876[/TIME] ---------------

Ok,

ran:
PHP Code:

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

Got:
PHP Code:

INSERT INTO muscle-mecca-BackUp.post (SELECT * FROM meccaaugust-backup.post WHERE threadid IN (SELECT threadid FROM muscle-mecca-BackUp.thread))          MySQL said: [IMG]https://74.208.174.189:8443/domains/databases/phpMyAdmin/themes/dot.gif[/IMG] 
  #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 [DATE]1441969060[/DATE] at [TIME]1441969060[/TIME] ---------------

not sure why but cant edit this

Dave 09-11-2015 09:15 AM
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 09-11-2015 01:30 PM
Try this query. The database needs to be backticked seperately from the table name, and I've added IGNORE after INSERT.

Code:

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

RichieBoy67 09-12-2015 12:06 AM
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 [DATE]1442031673[/DATE] at [TIME]1442031673[/TIME] ---------------

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 [DATE]1442049184[/DATE] at [TIME]1442049184[/TIME] ---------------

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!


All times are GMT. The time now is 08:22 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01200 seconds
  • Memory Usage 1,829KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (7)bbcode_code_printable
  • (9)bbcode_php_printable
  • (4)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (27)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete