{"id":918,"date":"2016-04-05T12:36:03","date_gmt":"2016-04-05T17:36:03","guid":{"rendered":"http:\/\/shawnbeelman.dev\/?p=918"},"modified":"2020-11-24T12:32:32","modified_gmt":"2020-11-24T18:32:32","slug":"manually-changing-wordpress-database-table-prefixes","status":"publish","type":"post","link":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/","title":{"rendered":"Manually Changing WordPress Database Table Prefixes"},"content":{"rendered":"<p>Today it came to my attention that my wife&#8217;s site&#8217;s hosting was running a very old version of PHP, so I thought it was time to update it. It&#8217;s hosted at <a href=\"http:\/\/www.webfaction.com\/signup?affiliate=sbgd\" rel=\"nofollow\">Webfaction<\/a>, which I really like, but I didn&#8217;t see an easy way to update PHP from their control panel. So, I decided it would be fairly easy (ha) to just create a new app, clone the site there, and then point the website to that new app.<\/p>\n<p>After logging into the server via SSH, I successfully copied the entire site using the terminal command:<\/p>\n<pre><code class=\"language-bash\">ssh cp -r ~\/webapps\/oldapp\/* ~\/webapps\/newapp<\/code><\/pre>\n<p>That worked like a charm and was probably about 1000% faster than doing by FTP.<\/p>\n<p>I then went to delete the old app once I confirmed the new one was working. Hold on, there, compadre\u2014it wanted to delete the MySQL DB along with it! Turns out there&#8217;s no way to remove the app without it also removing the associated DB. So, I exported the DB to a gzip, tried to import it into the fresh DB, and got an error:<\/p>\n<blockquote><p>1075 &#8211; Incorrect table definition; there can be only one auto column and it must be defined as a key<\/p><\/blockquote>\n<p>For whatever reason, phpMyAdmin was exporting the tables without the proper &#8220;Primary Key&#8221; definition. I used WP Migrate DB Pro to export the DB instead, and that worked.<\/p>\n<p>Then I realized that the tables were originally created with the default &#8220;wp_&#8221; prefix, which I don&#8217;t like for security reasons. Just add a new prefix to the existing tables in phpMyAdmin, right?<\/p>\n<p>That&#8217;s what I did. Of course I updated the wp-config.php file as well, but when I tried to access the site&#8217;s dashboard, I got a &#8220;permission denied&#8221; error.<\/p>\n<p>Back to Google we go. I found out that there are a few DB entries that use the table prefix in their metakey name. Here&#8217;s the SQL command to update them:<\/p>\n<pre><code class=\"language-sql\">update newprefix_usermeta set meta_key = 'newprefix_capabilities' where meta_key = 'wp_capabilities';\r\nupdate newprefix_usermeta set meta_key = 'newprefix_user_level' where meta_key = 'wp_user_level';\r\nupdate newprefix_usermeta set meta_key = 'newprefix_autosave_draft_ids' where meta_key = 'wp_autosave_draft_ids';\r\nupdate newprefix_options set option_name = 'newprefix_user_roles' where option_name = 'wp_user_roles';<\/code><\/pre>\n<p>&#8230;where &#8220;newprefix&#8221; is the new table prefix defined in wp-config.php.<\/p>\n<p>Phew. That turned out to be a lot of work, but it&#8217;s all working now, running a nice new instance of PHP 7.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today it came to my attention that my wife&#8217;s site&#8217;s hosting was running a very old version of PHP, so I thought it was time to update it. It&#8217;s hosted at Webfaction, which I really like, but I didn&#8217;t see an easy way to update PHP from their control panel. So, I decided it would [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":992,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[116,115,114],"tags":[117],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design\" \/>\n<meta property=\"og:description\" content=\"Today it came to my attention that my wife&#8217;s site&#8217;s hosting was running a very old version of PHP, so I thought it was time to update it. It&#8217;s hosted at Webfaction, which I really like, but I didn&#8217;t see an easy way to update PHP from their control panel. So, I decided it would [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\" \/>\n<meta property=\"og:site_name\" content=\"Shawn Beelman Graphic Design\" \/>\n<meta property=\"article:published_time\" content=\"2016-04-05T17:36:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-24T18:32:32+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"800\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Shawn\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Shawn\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\"},\"author\":{\"name\":\"Shawn\",\"@id\":\"https:\/\/shawnbeelman.com\/#\/schema\/person\/ec795042f645e1468827a7e862015d68\"},\"headline\":\"Manually Changing WordPress Database Table Prefixes\",\"datePublished\":\"2016-04-05T17:36:03+00:00\",\"dateModified\":\"2020-11-24T18:32:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\"},\"wordCount\":364,\"publisher\":{\"@id\":\"https:\/\/shawnbeelman.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg\",\"keywords\":[\"Webfaction\"],\"articleSection\":[\"MySQL\",\"PHP\",\"WordPress\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\",\"url\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\",\"name\":\"Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design\",\"isPartOf\":{\"@id\":\"https:\/\/shawnbeelman.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg\",\"datePublished\":\"2016-04-05T17:36:03+00:00\",\"dateModified\":\"2020-11-24T18:32:32+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage\",\"url\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg\",\"contentUrl\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg\",\"width\":1200,\"height\":800,\"caption\":\"Illustration of computer servers\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/shawnbeelman.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Manually Changing WordPress Database Table Prefixes\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/shawnbeelman.com\/#website\",\"url\":\"https:\/\/shawnbeelman.com\/\",\"name\":\"Shawn Beelman Graphic Design\",\"description\":\"Web Development, Graphic Design and Nature Photography\",\"publisher\":{\"@id\":\"https:\/\/shawnbeelman.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/shawnbeelman.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/shawnbeelman.com\/#organization\",\"name\":\"Shawn Beelman Graphic Design\",\"url\":\"https:\/\/shawnbeelman.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/shawnbeelman.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2019\/04\/SB-logo_6.png\",\"contentUrl\":\"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2019\/04\/SB-logo_6.png\",\"width\":256,\"height\":256,\"caption\":\"Shawn Beelman Graphic Design\"},\"image\":{\"@id\":\"https:\/\/shawnbeelman.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/shawnbeelman.com\/#\/schema\/person\/ec795042f645e1468827a7e862015d68\",\"name\":\"Shawn\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/shawnbeelman.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c11b6955ef4a97a4d6d6fa4014e542a7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c11b6955ef4a97a4d6d6fa4014e542a7?s=96&d=mm&r=g\",\"caption\":\"Shawn\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design","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:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/","og_locale":"en_US","og_type":"article","og_title":"Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design","og_description":"Today it came to my attention that my wife&#8217;s site&#8217;s hosting was running a very old version of PHP, so I thought it was time to update it. It&#8217;s hosted at Webfaction, which I really like, but I didn&#8217;t see an easy way to update PHP from their control panel. So, I decided it would [&hellip;]","og_url":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/","og_site_name":"Shawn Beelman Graphic Design","article_published_time":"2016-04-05T17:36:03+00:00","article_modified_time":"2020-11-24T18:32:32+00:00","og_image":[{"width":1200,"height":800,"url":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg","type":"image\/jpeg"}],"author":"Shawn","twitter_misc":{"Written by":"Shawn","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#article","isPartOf":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/"},"author":{"name":"Shawn","@id":"https:\/\/shawnbeelman.com\/#\/schema\/person\/ec795042f645e1468827a7e862015d68"},"headline":"Manually Changing WordPress Database Table Prefixes","datePublished":"2016-04-05T17:36:03+00:00","dateModified":"2020-11-24T18:32:32+00:00","mainEntityOfPage":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/"},"wordCount":364,"publisher":{"@id":"https:\/\/shawnbeelman.com\/#organization"},"image":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage"},"thumbnailUrl":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg","keywords":["Webfaction"],"articleSection":["MySQL","PHP","WordPress"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/","url":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/","name":"Manually Changing WordPress Database Table Prefixes - Shawn Beelman Graphic Design","isPartOf":{"@id":"https:\/\/shawnbeelman.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage"},"image":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage"},"thumbnailUrl":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg","datePublished":"2016-04-05T17:36:03+00:00","dateModified":"2020-11-24T18:32:32+00:00","breadcrumb":{"@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#primaryimage","url":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg","contentUrl":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2016\/04\/three-blue-servers-mod-web.jpg","width":1200,"height":800,"caption":"Illustration of computer servers"},{"@type":"BreadcrumbList","@id":"https:\/\/shawnbeelman.com\/manually-changing-wordpress-database-table-prefixes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/shawnbeelman.com\/"},{"@type":"ListItem","position":2,"name":"Manually Changing WordPress Database Table Prefixes"}]},{"@type":"WebSite","@id":"https:\/\/shawnbeelman.com\/#website","url":"https:\/\/shawnbeelman.com\/","name":"Shawn Beelman Graphic Design","description":"Web Development, Graphic Design and Nature Photography","publisher":{"@id":"https:\/\/shawnbeelman.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/shawnbeelman.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/shawnbeelman.com\/#organization","name":"Shawn Beelman Graphic Design","url":"https:\/\/shawnbeelman.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/shawnbeelman.com\/#\/schema\/logo\/image\/","url":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2019\/04\/SB-logo_6.png","contentUrl":"https:\/\/shawnbeelman.com\/wp-content\/uploads\/2019\/04\/SB-logo_6.png","width":256,"height":256,"caption":"Shawn Beelman Graphic Design"},"image":{"@id":"https:\/\/shawnbeelman.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/shawnbeelman.com\/#\/schema\/person\/ec795042f645e1468827a7e862015d68","name":"Shawn","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/shawnbeelman.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c11b6955ef4a97a4d6d6fa4014e542a7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c11b6955ef4a97a4d6d6fa4014e542a7?s=96&d=mm&r=g","caption":"Shawn"}}]}},"_links":{"self":[{"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/posts\/918"}],"collection":[{"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/comments?post=918"}],"version-history":[{"count":0,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/posts\/918\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/media\/992"}],"wp:attachment":[{"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/media?parent=918"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/categories?post=918"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shawnbeelman.com\/wp-json\/wp\/v2\/tags?post=918"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}