As Spencer indicated, we have updated all of the URLs that we could, across the network, to the new i.stack.imgur.com.
A little more background on what I did; hopefully, this helps explain why it took nearly a week:
Spencer approached me and asked if I could help with this issue. I said, "Yes, of course," of course.
I downloaded and started looking at the output file (thanks @Glorfindel!).
I manually mapped all of the API site parameters listed in the output file to database names (a few were trial and error, like rus/russian/video did not point to the database names I expected).
Replaced all of the ## {API site parameter} with USE {database name};.
Eliminated all instances of http: and https: in the output file (since I found many cases where the file indicated one but the post contained the other). Removing the protocol prefix prevented me from having to replace both, but would lead to other work.
Built two SQL statements from each row in the output file under ### Successful replacements:
| 15 | https://i.imgur.com/DvpvklR.png
| https://i.sstatic.net/jEIKP.jpg
| 36199898,40503494,41611512,45018676,45635398,45712604,
45880438,48328568,49699797,52905718,53318239,55318799,
55366144,60723134,61174546 |
...became a much more elaborate...
/* 15 */ UPDATE Posts SET Body = REPLACE(Body,
N'//i.imgur.com/DvpvklR.png',
N'//i.sstatic.net/jEIKP.jpg')
WHERE Id IN (
36199898,40503494,41611512,45018676,45635398,45712604,
45880438,48328568,49699797,52905718,53318239,55318799,
55366144,60723134,61174546
);
UPDATE PostHistory SET Text = REPLACE(Text,
N'//i.imgur.com/DvpvklR.png',
N'//i.sstatic.net/jEIKP.jpg')
WHERE PostId IN (
36199898,40503494,41611512,45018676,45635398,45712604,
45880438,48328568,49699797,52905718,53318239,55318799,
55366144,60723134,61174546
);
We had to update both tables because, if we don't also update the history, anyone who edits the post would get the most recent version from history (or, if they revert to an older version), and that would contain the old URL.
I ran those statements against a handful of sites on Thursday. Initially, I just did the following sites in a very controlled way:
- StackOverflow
- ServerFault
- SuperUser
- Meta.StackExchange
- WebApps
- WebApps Meta
- Gaming
Then, I updated all instances of https://i.sstatic.net/ to https://i.sstatic.net/ on those sites. This is not something to do lightly because, on Stack Overflow for example, trying to update thousands of posts/history will block all of you. It would also potentially cause issues downstream (we use availability groups and don't want to jam too much into the pipe at once). So I had to spread this out in batches, like this, and it worked like a charm:
EXEC {databasename}.sys.sp_executesql N'
SET NOCOUNT ON;
DROP TABLE IF EXISTS #p;
SELECT Id = Id + 0 INTO #p FROM Posts
WHERE Body LIKE @src
INSERT INTO #p(Id) SELECT PostId FROM PostHistory
WHERE Text LIKE @src;
DECLARE @Id int, @c cursor;
SET @c = CURSOR FOR SELECT DISTINCT Id FROM #p;
OPEN @c; FETCH NEXT FROM @c INTO @Id;
WHILE @@FETCH_STATUS <> -1
BEGIN
UPDATE Posts SET Body = REPLACE(Body, @src, @dest)
WHERE Id = @Id AND Body LIKE LIKE N''%'' + @src + N''%'';
UPDATE PostHistory SET Text = REPLACE(Text, @src, @dest)
WHERE PostId = @Id AND Text LIKE N''%'' + @src + N''%'';
FETCH NEXT FROM @c INTO @Id;
END',
N'@src nvarchar(255), @dest nvarchar(255)',
@src = N'https://i.sstatic.net/',
@dest = N'https://i.sstatic.net/';
This took a long time, but it was completely non-disruptive from every measure I could take, so it gave me the confidence to run the remainder of the script against the other sites with a lot less hand-holding (and using sp_ineachdb instead of {specific db}.sys.sp_executesql). This finished over the weekend.
All told, across the network, we updated:
- Over 200,000 posts affected by the explicit URLs here.
- Over 1,500,000 URLs with
http:// (most of those not from this scope).
There are still:
These are probably a mix of failures/uncertainties from the original output file, and possibly new images that were uploaded since the original script was run. If @Glorfindel can run their script again, we can probably continue to chip away at this.
Keep in mind that some can't ever be fixed, at least automatically, because:
- they have been removed from imgur and the wayback machine (and in some cases, the user no longer exists or hasn't been seen in years; example)
- they link to missing galleries, not individual images (example)
- they link to videos or very large animated gifs (example) - some of these may have failed on first run of the script, but may work next time; others will continue to exceed size limits
- they link to the imgur website on purpose (example)
