{"id":2290,"date":"2023-04-13T12:23:39","date_gmt":"2023-04-13T10:23:39","guid":{"rendered":"https:\/\/www.wp-moon.com\/?p=2290"},"modified":"2023-04-13T12:23:41","modified_gmt":"2023-04-13T10:23:41","slug":"index-auto_increment-database-issues","status":"publish","type":"post","link":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/","title":{"rendered":"WordPress issues related to indexes, keys, and auto_increment fields in the database"},"content":{"rendered":"\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<p class=\"wp-block-paragraph\">During a WordPress database migration, a <strong>backup restoration<\/strong>, or any other database change with phpMyAdmin, problems may arise. This can include data corruption, data loss, version incompatibilities, and many more. This article specifically addresses issues encountered during WordPress database migrations and explains how to resolve them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Potential issues<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">During the <a href=\"https:\/\/www.wp-moon.com\/migrate-a-live-wordpress-to-localhost\/\">migration of your database<\/a>, you might encounter problems such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Restored data is incomplete<\/li>\n\n\n\n<li><strong>Indexes<\/strong>, keys, and <strong>auto_increment fields<\/strong> in the database no longer work<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">What are the consequences?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This can result in strange issues in your WordPress back-office:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Posts or pages<\/strong> that do not <strong>update<\/strong> when modified<\/li>\n\n\n\n<li>Posts or pages that you <strong>cannot create<\/strong> and\/or <strong>blank pages<\/strong> in the back-office when trying to create a new post<\/li>\n\n\n\n<li><strong>Creation of new users not working<\/strong><\/li>\n\n\n\n<li>And many <strong>other issues<\/strong> related to the creation and modification of the site in the back-office<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">What are the reasons?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL <em>INSERT<\/em> queries from various plugins insert rows into various tables with an undefined primary key (and therefore default to 0). Since primary keys must be unique, a second insertion into the same table fails, causing various issues.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For an unknown reason, the <strong>auto_increment was not preserved<\/strong> during the re-importation of the SQL dump. However, the rest of the database is in order.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The reasons for the problem&#8217;s occurrence remain rather <strong>unknown<\/strong>. Fortunately, we know how to fix it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Preventing issues<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To prevent these issues during the database migration, you can:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Check and optimize the database before migration<\/li>\n\n\n\n<li>Regularly back up data<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Backup and recovery<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before making any changes, be sure to create a complete backup of the database. In case of issues, you can restore the database from this backup.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Problem resolution<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The author of the managedwphosting.nl site published a post in which <a href=\"https:\/\/wpindexfixer.tools.managedwphosting.nl\/\">he details all the SQL queries<\/a> needed to repair the database, thanks to him!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are the SQL queries needed to resolve the issues. Each query repairs or recreates the missing indexes, keys, and auto_increment fields for each affected table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Starting with wp_users\nDELETE FROM wp_users WHERE ID = 0;\nALTER TABLE wp_users ADD PRIMARY KEY  (ID);\nALTER TABLE wp_users ADD KEY user_login_key (user_login);\nALTER TABLE wp_users ADD KEY user_nicename (user_nicename);\nALTER TABLE wp_users ADD KEY user_email (user_email);\nALTER TABLE wp_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_usermeta\nDELETE FROM wp_usermeta WHERE umeta_id = 0;\nALTER TABLE wp_usermeta ADD PRIMARY KEY  (umeta_id);\nALTER TABLE wp_usermeta ADD KEY user_id (user_id);\nALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191));\nALTER TABLE wp_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_posts\nDELETE FROM wp_posts WHERE ID = 0;\nALTER TABLE wp_posts ADD PRIMARY KEY  (ID);\nALTER TABLE wp_posts ADD KEY post_name (post_name(191));\nALTER TABLE wp_posts ADD KEY type_status_date (post_type,post_status,post_date,ID);\nALTER TABLE wp_posts ADD KEY post_parent (post_parent);\nALTER TABLE wp_posts ADD KEY post_author (post_author);\nALTER TABLE wp_posts MODIFY ID bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_comments\nDELETE FROM wp_comments WHERE comment_ID = 0;\nALTER TABLE wp_comments ADD PRIMARY KEY  (comment_ID);\nALTER TABLE wp_comments ADD KEY comment_post_ID (comment_post_ID);\nALTER TABLE wp_comments ADD KEY comment_approved_date_gmt (comment_approved,comment_date_gmt);\nALTER TABLE wp_comments ADD KEY comment_date_gmt (comment_date_gmt);\nALTER TABLE wp_comments ADD KEY comment_parent (comment_parent);\nALTER TABLE wp_comments ADD KEY comment_author_email (comment_author_email(10));\nALTER TABLE wp_comments MODIFY comment_ID bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_links\nDELETE FROM wp_links WHERE link_id = 0;\nALTER TABLE wp_links ADD PRIMARY KEY  (link_id);\nALTER TABLE wp_links ADD KEY link_visible (link_visible);\nALTER TABLE wp_links MODIFY link_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_options\nDELETE FROM wp_options WHERE option_id = 0;\nALTER TABLE wp_options ADD PRIMARY KEY  (option_id);\nALTER TABLE wp_options ADD UNIQUE KEY option_name (option_name);\nALTER TABLE wp_options ADD KEY autoload (autoload);\nALTER TABLE wp_options MODIFY option_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_postmeta\nDELETE FROM wp_postmeta WHERE meta_id = 0;\nALTER TABLE wp_postmeta ADD PRIMARY KEY  (meta_id);\nALTER TABLE wp_postmeta ADD KEY post_id (post_id);\nALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));\nALTER TABLE wp_postmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_terms\nDELETE FROM wp_terms WHERE term_id = 0;\nALTER TABLE wp_terms ADD PRIMARY KEY  (term_id);\nALTER TABLE wp_terms ADD KEY slug (slug(191));\nALTER TABLE wp_terms ADD KEY name (name(191));\nALTER TABLE wp_terms MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_term_taxonomy\nDELETE FROM wp_term_taxonomy WHERE term_taxonomy_id = 0;\nALTER TABLE wp_term_taxonomy ADD PRIMARY KEY  (term_taxonomy_id);\nALTER TABLE wp_term_taxonomy ADD UNIQUE KEY term_id_taxonomy (term_id,taxonomy);\nALTER TABLE wp_term_taxonomy ADD KEY taxonomy (taxonomy);\nALTER TABLE wp_term_taxonomy MODIFY term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_term_relationships\nDELETE FROM wp_term_relationships WHERE object_id = 0;\nDELETE FROM wp_term_relationships WHERE term_taxonomy_id = 0;\nALTER TABLE wp_term_relationships ADD PRIMARY KEY  (object_id,term_taxonomy_id);\nALTER TABLE wp_term_relationships ADD KEY term_taxonomy_id (term_taxonomy_id);\n-- Starting with wp_termmeta\nDELETE FROM wp_termmeta WHERE meta_id = 0;\nALTER TABLE wp_termmeta ADD PRIMARY KEY  (meta_id);\nALTER TABLE wp_termmeta ADD KEY term_id (term_id);\nALTER TABLE wp_termmeta ADD KEY meta_key (meta_key(191));\nALTER TABLE wp_termmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;\n-- Starting with wp_commentmeta\nDELETE FROM wp_commentmeta WHERE meta_id = 0;\nALTER TABLE wp_commentmeta ADD PRIMARY KEY  (meta_id);\nALTER TABLE wp_commentmeta ADD KEY comment_id (comment_id);\nALTER TABLE wp_commentmeta ADD KEY meta_key (meta_key(191));\nALTER TABLE wp_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>things to do<\/strong> with these SQL queries:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If your tables do not start with &#8220;wp_&#8221;, replace the prefix everywhere with yours, otherwise it will not work.<\/li>\n\n\n\n<li>Copy and paste all the queries, and execute them in your database. For example, using the &#8220;<em>SQL<\/em>&#8221; tab in phpmyadmin.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Then try to create\/update posts and users again, the <strong>problems should have disappeared<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By carefully following the provided instructions, you should be able to resolve the issues related to your WordPress database migration. Remember to <strong>always back up your data<\/strong> before making significant changes and check the compatibility of WordPress versions and <a href=\"https:\/\/www.wp-moon.com\/errors-with-plugins\/\">plugins<\/a>.<\/p>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>During a WordPress database migration, a backup restoration, or any other database change with phpMyAdmin, problems may arise. This can include data corruption, data loss, version incompatibilities, and many more. This article specifically addresses issues encountered during WordPress database migrations and explains how to resolve them. Potential issues During the migration of your database, you &hellip; <a href=\"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">WordPress issues related to indexes, keys, and auto_increment fields in the database<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":2285,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-2290","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-fix-errors"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Resolving WordPress database migration issues: restoring indexes, keys, and auto_increment fields<\/title>\n<meta name=\"description\" content=\"Solve WordPress database migration issues by repairing indexes, keys, and auto_increment fields using SQL queries.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Resolving WordPress database migration issues: restoring indexes, keys, and auto_increment fields\" \/>\n<meta property=\"og:description\" content=\"Solve WordPress database migration issues by repairing indexes, keys, and auto_increment fields using SQL queries.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/\" \/>\n<meta property=\"og:site_name\" content=\"WP Moon\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/wpmoonwebsite\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-13T10:23:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-13T10:23:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1600\" \/>\n\t<meta property=\"og:image:height\" content=\"1067\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Alexandre G\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@alexgaboriau\" \/>\n<meta name=\"twitter:site\" content=\"@WPMoon4\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandre G\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Resolving WordPress database migration issues: restoring indexes, keys, and auto_increment fields","description":"Solve WordPress database migration issues by repairing indexes, keys, and auto_increment fields using SQL queries.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/","og_locale":"en_US","og_type":"article","og_title":"Resolving WordPress database migration issues: restoring indexes, keys, and auto_increment fields","og_description":"Solve WordPress database migration issues by repairing indexes, keys, and auto_increment fields using SQL queries.","og_url":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/","og_site_name":"WP Moon","article_publisher":"https:\/\/www.facebook.com\/wpmoonwebsite","article_published_time":"2023-04-13T10:23:39+00:00","article_modified_time":"2023-04-13T10:23:41+00:00","og_image":[{"width":1600,"height":1067,"url":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg","type":"image\/jpeg"}],"author":"Alexandre G","twitter_card":"summary_large_image","twitter_creator":"@alexgaboriau","twitter_site":"@WPMoon4","twitter_misc":{"Written by":"Alexandre G","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#article","isPartOf":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/"},"author":{"name":"Alexandre G","@id":"https:\/\/www.wp-moon.com\/en\/#\/schema\/person\/72a64e6910977ec9e4b32fd8da54369d"},"headline":"WordPress issues related to indexes, keys, and auto_increment fields in the database","datePublished":"2023-04-13T10:23:39+00:00","dateModified":"2023-04-13T10:23:41+00:00","mainEntityOfPage":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/"},"wordCount":447,"commentCount":0,"publisher":{"@id":"https:\/\/www.wp-moon.com\/en\/#organization"},"image":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#primaryimage"},"thumbnailUrl":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg","articleSection":["Fix Errors"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/","url":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/","name":"Resolving WordPress database migration issues: restoring indexes, keys, and auto_increment fields","isPartOf":{"@id":"https:\/\/www.wp-moon.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#primaryimage"},"image":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#primaryimage"},"thumbnailUrl":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg","datePublished":"2023-04-13T10:23:39+00:00","dateModified":"2023-04-13T10:23:41+00:00","description":"Solve WordPress database migration issues by repairing indexes, keys, and auto_increment fields using SQL queries.","breadcrumb":{"@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#primaryimage","url":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg","contentUrl":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/04\/database.jpg","width":1600,"height":1067,"caption":"database"},{"@type":"BreadcrumbList","@id":"https:\/\/www.wp-moon.com\/en\/index-auto_increment-database-issues\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.wp-moon.com\/en\/"},{"@type":"ListItem","position":2,"name":"Fix Errors","item":"https:\/\/www.wp-moon.com\/en\/category\/fix-errors\/"},{"@type":"ListItem","position":3,"name":"WordPress issues related to indexes, keys, and auto_increment fields in the database"}]},{"@type":"WebSite","@id":"https:\/\/www.wp-moon.com\/en\/#website","url":"https:\/\/www.wp-moon.com\/en\/","name":"WP Moon","description":"","publisher":{"@id":"https:\/\/www.wp-moon.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wp-moon.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.wp-moon.com\/en\/#organization","name":"WP Moon","url":"https:\/\/www.wp-moon.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.wp-moon.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2020\/02\/logo.png","contentUrl":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2020\/02\/logo.png","width":290,"height":95,"caption":"WP Moon"},"image":{"@id":"https:\/\/www.wp-moon.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/wpmoonwebsite","https:\/\/x.com\/WPMoon4"]},{"@type":"Person","@id":"https:\/\/www.wp-moon.com\/en\/#\/schema\/person\/72a64e6910977ec9e4b32fd8da54369d","name":"Alexandre G","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/09\/312419460_10229483707296045_2919774527885824513_n-100x100.jpg","url":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/09\/312419460_10229483707296045_2919774527885824513_n-100x100.jpg","contentUrl":"https:\/\/www.wp-moon.com\/wp-content\/uploads\/2023\/09\/312419460_10229483707296045_2919774527885824513_n-100x100.jpg","caption":"Alexandre G"},"description":"Alexandre, Web Developer &amp; Website Editor. I've been using WordPress for over 10 years in my professional environment and on personal projects. I created WP Moon to teach you how to use this CMS, solve your problems and show you all the tricks.","sameAs":["https:\/\/www.croque-pixel.com\/","https:\/\/x.com\/alexgaboriau","https:\/\/www.youtube.com\/@wp_moon\/videos"],"url":"https:\/\/www.wp-moon.com\/en\/author\/wp-moon\/"}]}},"_links":{"self":[{"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/posts\/2290","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/comments?post=2290"}],"version-history":[{"count":0,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/posts\/2290\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/media\/2285"}],"wp:attachment":[{"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/media?parent=2290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/categories?post=2290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wp-moon.com\/en\/wp-json\/wp\/v2\/tags?post=2290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}