Re-establish file download links from eBooks to WordPress site
Background to issue
Major stuffup, I’d got spam comments on website and I deleted about 2/3 of my post table rows. This lost me all my download links to code files for eBooks I’d published.
I was foolish in that I didn’t have a backup of the website (I do now) so I had to fix the problem from scratch.
I quickly unpublished all the relevant eBooks and then had to recreate the links to the correct location on the post table in the websites database.
The process I took to do that is show below.
Excel Extracting URL from Link
As I had to find all the links in the existing books I ended with the text but had to find the links.
I was using a spreadsheet to itemise all the links and where they are in the books so having a method to extract the actual URL was quite handy.
Some actions on database
Delete rows in MySQL table
This below buggered my Website as I couldn't retrieve the data after it was blown away, best to backup whole website and database first, or copy table first before using
---DO NOT USE UNDER ANY CIRCUMSTANCES UNLESS YOU HAVE COPIED TABLE FIRST--- DELETE FROM Students WHERE ID=2;
I’ve a script for this person posting in my Revit Shortcuts site where I can strip them out of the table with
DELETE FROM wordpress332.wp_posts WHERE post_title like '%Crytorhype%'
So can use that in future without blowing away the database table with all my elements in it.
Stop auto increment on a table and restart it
How to drop auto_increment from a mysql table – Stack Overflow
SET FOREIGN_KEY_CHECKS=0; alter table added_object modify column id_interaction_object int(11) not null; SET FOREIGN_KEY_CHECKS=1;
Insert into a Row
I want to be able to put a non-null entry value to the table at an ID just before I need to put in a Download link, so that, when I go back yo auto increment the next one will be the original download link.
I’ll need to do them consecutively, so will need to go through all the books and have all the downloads ready to install in sequence.
Need to change first Value for the row/ID in script below
SET FOREIGN_KEY_CHECKS=0; INSERT INTO wordpress332.wp_postsold2(ID, post_title, post_content,post_excerpt,to_ping,pinged,post_content_filtered) VALUES (1024, 'dummy','1','1','1','1','1') ; SET FOREIGN_KEY_CHECKS=1;
c# – Add a row to a sql database which has a identity primary key – Stack Overflow
Delete a Table
In SQL use DROP for DELETE for TABLES
DROP TABLE wordpress332.wp_postsold1;
Copy a table
First line creates table with same structure as the LIKE one then following 2 lines copies everything from original to new table
CREATE TABLE IF NOT EXISTS wordpress332.wp_postsOld2 LIKE wordpress332.wp_postsOld; INSERT wordpress332.wp_postsOld2 SELECT * FROM wordpress332.wp_postsOld;
View what’s in a table
SELECT * FROM wordpress332.wp_posts;
Reset Increments-Not realy, it just stops autoincrement, puts the row in and then puts auto-increment back on, so if auto increment set at 3551 then the next auto increent row will be 3552. -SEE BELOW
If over time there are holes in Database you may want to get rid of the gaps. The risk is that with items moved from one row to a lesser row number, if there is a another table using the ID Key to point to that row, it won’t point there anymore.
Don’t use the below code unless you 1/ Backup website & database first, OR 2/ make a copy of the table before doing it, then testing it afterwards.
Otherwise it could be a disaster as things will not point to where they should and so linkages in DB will be broken
SET @count = 0; UPDATE wordpress332.wp_postsOld2 SET wordpress332.wp_postsOld2.id = @count:= @count + 1; ALTER TABLE wordpress332.wp_postsOld2 AUTO_INCREMENT = 1;
Reset Auto Increments
How to change auto increment number in MySQL?
So this below tells auto increment where to start from.
alter table wordpress332.wp_posts auto_increment=1020;
Rename a table
First I rename the existing wp_posts table to wp_postsOrig
Then I rename the modified table wp_postsold1 to wp_posts which has the row ID incrementing from Row ID=1000 onwards. This allows me to look to put original download links into exactly the same location
ALTER TABLE wordpress332.wp_posts RENAME wordpress332.wp_postsOrig; ALTER TABLE wordpress332.wp_postsold1 RENAME wordpress332.wp_posts;
Writing data from one table to another (appending)
See commentary below
INSERT wordpress332.wp_postsOld1 SELECT * FROM wordpress332.wp_postsOld2;
Process to reorganise Table “POST” so that it will be compacted to before my Book download links.
1/ Because I’d originally deleted from Table “POSTS” everything after Row 1000, it started incrementing from after the original table row (pre-Delete) so next row is 3551 , so rows go 999,1000, 3551,3552 etc.
I added some more links to site and so all of those increment after 3551.
- So I copied the “Post” Table twice, “TPost1” & “TPost2”
- in TPost1 I just deleted everything after Row ID=1000
- The reason for this is that there are gaps in the rows between Row ID=1 to Row ID=1000 and I don’t want to mess up any of the existing pointers there.
- in TPost 2 I deleted everything before Row ID =1001, so these rows start at Row ID=3551 and upwards
- I then Reset Increments with SET @count = 1001; so all new fields instead of starting at Row ID = 3551 now start at Row ID =1001, and say there were 50 rows then the last row is 1051
- Although I’ve made rows TO Row ID =1051 it still auto increments from Row ID = 3551
- So I have to use the Reset auto-increment from- Row ID =1051
- So the next item that is added goes to Row ID =1052 which is what I want to happen.
Process to reorganise Table “POST” so that it will accept new Download links from correct Row ID
- Now that I have a Post Table that goes up to Row ID =1052, as long as my links are after ID =1052 the n I can:
- Go to MySQL on Remote Desktop connection and in correct database:
- Set Auto-Increment to the row number that the next download link should be from, lets say it its ID = 1070, so I set
alter table wordpress332.wp_posts auto_increment=1070;
- Then I can go to the Download Monitor Plugin and create a new Download Link and it should show that Row ID= 1070.
- If it doesn’t, and it shows something like Row ID= 1071. then you need to go back to MySQL Workbench and delete a couple of rows and try again.
- I can get a couple of spam comments come in and mess up the sequence so had to delete those Rows, Re-set the auto increment to the row I wanted, delete the last Download Monitor Plugin Download Link and create a new one.
- You have established the Download Link to the correct row, so can come back later to add file that will be added at a much later Row ID, but it’ll have the association with that Download link at the Row ID= 1070 so it works fine
One thing to note, you can just give the new Download Link a Title/Name and PUBLISH, and come back later and add the download link file that you want.
You have established the Download Link to the correct row, so can come back later to add file that will be added at a much later Row ID, but it’ll have the association with that Download link at the Row ID= 1070 so it works fine
Its actually faster to set these up like this, then come back and add files later, especially if you have large gaps between book Row Download Links
then a couple didn't work, these generally were for Last chapter items, WHERE I gave the whole script of the book, so if they wanted it all in one file then they could give me their email. - For this I used the Email Before Download Plugin. This takes a FORM Row ID & a Download Link Row ID and combine them in a shortcode on a POST or PAGE. So the link is not to a Download Link but to a specific page or post on the website (I just used pages for this). Therefore the PAGE has to be created at a specific row number in the 'POST" Table. - As I'd already created Download Links, which were ERRONEOUS I had to go back and mess with the Database. - This is a bit messy but code below, I finally figured out how to do comments for SQL - I'll step through process after code block ```SQL /* */ -- DROP TABLE wordpress332.wp_posts_bac ; /* -- This creates Backup table of posts - in case anything goes wrong CREATE TABLE IF NOT EXISTS wordpress332.wp_posts_bac LIKE wordpress332.wp_posts; INSERT wordpress332.wp_posts_bac SELECT * FROM wordpress332.wp_posts; -- check that posts_bac table is same as orig posts table SELECT * FROM wordpress332.wp_posts_bac ; */ /* -- Create new table for low end - Will add new download later CREATE TABLE IF NOT EXISTS wordpress332.wp_postsx1 LIKE wordpress332.wp_posts; INSERT wordpress332.wp_postsx1 SELECT * FROM wordpress332.wp_posts; -- This deletes all rows above 1940 -- DELETE FROM wordpress332.wp_postsx1 WHERE ID>1940; -- Create new table for low end - Will add new download later CREATE TABLE IF NOT EXISTS wordpress332.wp_postsx2 LIKE wordpress332.wp_posts; INSERT wordpress332.wp_postsx2 SELECT * FROM wordpress332.wp_posts; */ -- DELETE FROM wordpress332.wp_postsx2 WHERE ID<1953; -- SELECT * FROM wordpress332.wp_postsx1 ; -- SELECT * FROM wordpress332.wp_postsx2 ; -- SELECT * FROM wordpress332.wp_postsx2 ; -- Rename posts to a 2nd backup -- ALTER TABLE wordpress332.wp_posts RENAME wordpress332.wp_posts_bac2; -- Rename postsx1 to post table -- ALTER TABLE wordpress332.wp_postsx1 RENAME wordpress332.wp_posts; -- Check the new posts table to make sure its ready -- SELECT * FROM wordpress332.wp_posts ORDER BY ID DESC LIMIT 10; -- Set Auto increment to download point -- alter table wordpress332.wp_posts auto_increment= 1949 ; -- GO DO DOWNLOAD and put in New !!!! -- Add dowwnload as well on next line /* -- This should copy the higher numbers across on top of the other INSERT wordpress332.wp_posts SELECT * FROM wordpress332.wp_postsx2; */- I got caught just creating the download monitor links,
- POST table has Row ID =2027 as highest row
- You want to insert a new Page in at Row ID = 1703,
- there is a gap before this of 2 numbers, sp preceding row number is Row ID = 1701
- After Row ID = 1703, Row ID = 1704 is active so we do not want to mess with that.
- Make a copy of POST table first, so you have a backup in case anything is mogadored
- Make 2 further exact copies of the POST table, x1 & x2.
- Table x1 you then want to delete all rows above Row ID = 1701, so this is the Lower table, we will set it up so we can insert the new page in at Row ID = 1703
- Table x2 you then want to delete all rows below Row ID = 1704, so this is the Higher table
- We then need to Rename the existing POST table
- We then rename Table x1 to be POST table
- We then set AUTO-INCREMENT = 1703
- We then go to Dashboard on website and ADD a NEW PAGE.
- This page should have Row ID = 1703
- TEST TO CHECK THIS IS SO. by putting URL to that page into the browser.
- We can then use:
SELECT * FROM wordpress332.wp_postsx2;
- This adds all of the items in Table x2 and adds them onto the POST table.
- We can now create a new FORM for getting name & email address and set that up, it will have a row ID, say Row ID =2028
- We then create a new Download link to the file that we want to upload, this will have a download link to a row, say Row ID =2029
- We then go back to the new Page we have created and create a short-code block where we will insert the Email Before Download Plugin short-code to put the Form on the Page & When filled out and submitted will return the Download link to the specific file.
A long process , but I got there in the end.
Next time I need to be more careful in seeing if IT’S A download link or a page that the link is pointing to as splitting the Table to add in a Row then merging the table is time consuming, better to have just added them in in an incremental fashion.
It took 3 days from realising what had happened to:
- finding all the download links
- working out a methodology that would work
- finding all the files (actually available on the website in sub-directory of uploads folder)
- working through managing the database and getting the proper SQL commands to do what was required
- Doing the actual re-linking to original link ID
- Finding that there were pages for “Email before upload” for some files, so
- Re-splitting Table to insert new page links instead of Download links
- Re-publishing books on Amazon & Draft 2 Digital
- Writing this article for reference for the future.
That will teach me to make sure I have backups. Still, I did learn something along the way.
Another way to look at it is , 1. I really really stuffed up, BUT 2. I was able to fix the issue.
So I’m quite pleased with being able to resolve the issue. So if it happens another time I can fix it. But I won’t need to, as I’ll have a backup!!!!