Reseting the WordPress Multisite Blog ID
In 2016 I worked on more WordPress Multisite projects then I have throughout my entire career. During that time, one of the most valuable Multisite habits I learned was, keeping a close eye on my database structure.
As I was got more comfortable with Multisite, I found myself creating and deleting network sites quite often. In my experience, when deleting network sites from the WordPress Dashboard, the database tables for each site are not deleted automatically. If not carefully maintained your database structure can turn into a hot mess quickly.
Deleting the tables is easy as long as you pay close attention to a few small details such as the
blog_id. I like to keep my databases tidy. After deleting a network site I noticed the
blog_id values would fall out-of-sync with the number of sites on the network. So, I went in search of a solution. After trying a few options, I found a simple method that works very well.
I like to keep my projects well organized and when I noticed my blog_ids falling out of sync after deleting network sites and their db tables
MySql auto_increment values
WordPress Multisite stores some basic information about each site on your network in the
wp_blogs table. Keep in mind if you are working with a custom database prefix (not wp_) your table names will look slightly different from these examples. When each new site is created a new row is created within the
wp_blogs table and the new site
blog_id value is automatically incremented by 1.
After deleting a site from the network and removed it’s database tables you can reset the
wp_blogs AUTO_INCREMENT value with a simple sql query:
// Example using default db previx ALTER TABLE wp_blogs AUTO_INCREMENT = 3; // Example using custom db prefix ALTER TABLE my_custom_table_prefix_blogs AUTO_INCREMENT = 3;
Now you can easily and confidently keep your database and multisite blog IDs updated and in-sync.