Reset multisite ID’s

Not specifically for reassigning AUTO_INCREMENT related to site IDs, but for other IDs I’ve done so. The suggestion is only valid if there are no other sites with higher IDs that could clash should the auto increment value be lowered.

As always, make a full backup of what you have now. If things go wrong for any reason, you can restore the current state from backup. BTW, you don’t have to execute ALTER queries to do so, it can be done through the UI of phpMyAdmin. Navigate to the blogs table, then pick Operations from the More tab. The setting is in Table Options.

Thanks for replying!
So it should be safe to decrease considering the fact that the 2nd subsite was deleted? There’s a couple of abandoned plugin tables from the 2nd(and probably from the 3rd subsite) after I deleted it, but I’ve confirmed with the plugin devs that it should be safe to delete them(since their respective subsite is gone).

Found the AUTO_INCREMENT option in the Operations tab, that method feels a lot better than running SQL! It’s set to 4 right now, should it be on 1 or 2 when I create a new subsite?

Do you have any backup plugins you could recommend for multisites? Or Should I just make a manual one in phpMyAdmin?
Thanks again!

Set the value to the one you want assigned to the new site — so 2.

I’ve used updraft plus plugin in the past. You only really need the DB backed up, not the entire site. For quick backups prior to manually altering the DB, I usually just manually export a .sql file of the tables affected by my changes, unless the site is in need of a full backup anyway.

Ah, got it!

Unfortunately I lost access to the WP All-in-One plugin when I converted the site into a multisite. Our host has a backup feature, but I’m not too sure how well it works with WP. I’ll do a DB backup, hopefully nothing goes wrong.

Sorry to keep bothering you, but is there anything else I need to do DB-wise? I’ve yet to come around to deleting the old site’s tables, but there shouldn’t be anything else I need to clean up after that and the soon-to-be-deleted third site, right?
Thanks again, really appreciate it!

You want your DB to reflect a multisite installation with only the one primary site in place. This means no wp_2_*, wp_3_* sort of tables. Set the auto increment of wp_blog to 2. You may need to clean out any wp_blogmeta data related to deleted blogs, but that table is likely to be empty anyway.

AFAIK that’s it, but mind you, I’ve not ever actually done this specific operation, hence the importance of backing up the DB.

Alright, I’ll take a backup of the leftover _2 and _3 tables as well just to be safe before I delete them, I’ll check out wp_blogmeta too. But it seems like WP did a good job scrubbing off the old subsite, sans the leftover plugin tables and the uploads folder in /sites/ID/. And apart from this, it should be safe to proceed?

I’ll take a full backup of the entire DB as well, and I’ll see if I can find anyone else who has experience with this just to be on the safe side. Seems like the author of the linked article in the first post deleted it some time after I created this thread..
Thanks again, much appreciated!

I did see the autoincrement suggestion in that article before it went away. If you have good backups, I’d just go ahead and give it a try. Sure, it’d be nice to have confirmation from someone who actually did this before, but I think it’s reasonably safe to proceed regardless. Of course you are the only one who can assess your own comfort level in trying something without confirmation of others success.

Our host offers a separate DB backup as well, I guess if anything really does go irreversibly wrong I’ll just restore that and the file backup. But I think it’s like you mentioned, there’s probably not much that really could do that.

I’ll see if I can find someone else with experience doing this around the web, and then I’ll get back here in case anyone else wants to reset their multisite ID’s.
I’m a little bit surprised there’s not more about this anywhere else, but then again I guess most people really wouldn’t care about a ID in their database and uploads folder.
Thanks again!

Yeah, the assigned ID shouldn’t matter, but I do understand the desire to have a logical progression of IDs. How do you think I knew about autoincrement? ??

I have a multisite installation I use for testing. I went ahead and deleted one of the sites there, then reset autoincrement, then added a new site. All appears to be well AFAICT. The one glitch I ran into is I could not re-use the same site name again, it had to be something new and never used. I could not determine how reuse is restricted. Otherwise I’ve not run into any problems.

Ah, thanks for trying it out!

Do you mean the name of the blog or the URL? I currently have the 3rd subsite using the deleted 2nd one’s name and URL, was planning on re-using it again for the “new” 2nd one after resetting the AUTO_INCREMENT. So that won’t work?
I’ve read some StackOverflow threads about AUTO_INCREMENT, they’re pretty old but most of them say it’s not possible to lower the value from your current highest. But since you were successful I suppose that’s no longer a thing?

I meant the site URL’s slug. I was restricted when creating a new site, but I just realized now that I am able to edit the slug back to what it was after the initial insertion. I did not check reuse of the site’s title, but I would not expect any difficulty. Additionally, I’m confident it could be changed in through phpMyAdmin if WP wouldn’t let you reuse it for some reason.

I think the initial restriction is due to stale rewrite rules that are regenerated after new site insertion, removing the initial restriction. Since the title isn’t involved in rewrites, there would be no restrictions.

There’s an important caveat to the ability to reset IDs: There can be no existing sites with IDs equal to or higher than the new auto_increment value. This could be why others have said it’s not possible. I doubt the situation has changed over the years. There’s no physical difference between a DB cleaned of old subsite tables which has auto-increment reset, and a fresh new DB with only the main site’s tables and an unmolested auto_increment.

I believe you can even work around that restriction to get a specific unused ID assigned to a site even with higher numbered sites. After resetting auto_increment and inserting a new site, restore auto_increment back to what it was before doing anything else with WP. I’ve not specifically tested this scenario, but I don’t see why it wouldn’t work.

Potentially dumb question, but what’s the difference between the subsite URL and slug?

Ah, so I should definitely be in the clear on that end since I have no other subsites apart from the (soon to be deleted)3rd one? And after cleaning out the website_2 and _3’s plugin tables.

Again, thanks for trying everything out!
I feel a lot more confident moving forward with this now. I just want to work out any potential hiccups so I don’t need to deal with a broken installation.

An URL is composed of protocol (http etc.), subdomain, domain, path, and in some cases filename. Example:
https://subdomain.domain.name/path/to/file.name

The site’s slug is just the path portion of an URL that identifies that site. If a sub-site’s home page URL is https://example.info/my-subsite/, its slug is “my-subsite”.

So in one sense there is no difference, but if we want to be pedantic there is a difference. “URL” is a generally accepted term for internet addresses. “Slug” AFAIK is a common term only within the WP community, I’ve not seen it used elsewhere. I may not get out much though ??

Ah, I see.
So I could run into some issue with that on the “Create new subsite” screen after lowering the increment value, but if I do I can just edit it right after? I was planning on using the same https://example.com/subsite URL as the deleted 2nd and current 3rd one.

Not to bother you even more, but was there anything in the website_blogmeta table when you tested it out?
Thanks!

@bcworkz Not to bombard you with more replies, but just to make sure I’ve got the process down(and for future reference) ??

1a. Delete the abandoned tables from the old 2nd subsite
1b. Delete the 3rd subsite, its uploads folder and its abandoned tables
1c. Delete anything in wbesite_blogmeta?
2. Decrease the AUTO_INCREMENT option on the website_blog table(in the operations tab in phpMyAdmin)
3. Create a new site on the network screen
3a. Change the URL/slug post-creation in-case it won’t let me re-use the old one.