Is it possible to fix/repair all Index, Primary Key, Unique (and all other) structure of a Wordpress MS Database

Question

I've backed up my Multisite database (via export from PhpMyAdmin) then after some problems I decided to revert back the original (backed-up database) and I've imported (upload it via PhpMyAdmin) again (and I deleted the file on my pc).

But after some time, I've faced some problem about posts, pages, and other post types on all my sites (I couldn't add new pages, page ids becomes 0 (more than one)). I've read some posts about this issue (database indexes, unique keys and other structural data has been corrupted). I understand that, either import process was broken or my export file have has some problems.

While I think I've resolved the problems, this time same things happens for taxonomies. While I check my taxonomy related tables (of all multi-site databases), I see that there is no any unique keys, indexes, primary keys, Auto Increment fields etc in my database..

As a result, is there any way to fix this issue by a script or SQL command?

Answer 1

Yes the issue is due to the auto increment setting.

All WordPress DB specifics can be found here, which includes details on which ones use auto increment: https://codex.wordpress.org/Database_Description

This isn't going to be an easy task, but here's some sites to help you: https://www.photographerstechsupport.com/tutorials/fixing-wordpress-indexes-foreign-keys-and-auto_increment-fields/

This one is specific to wp_options: https://guides.wp-bullet.com/fixing-wp_options-table-missing-unique-column-primary-key/

Specifically the auto increment: https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql

You need to make sure that whatever you set the auto increment to, that you take the largest number in the ID column, +1 and use that.

Basically that tells the DB next time a new row is inserted, what ID to use when doing the auto incrementation.

SO related post: Wordpress Database lost auto increment