WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#41240 closed defect (bug) (invalid)

Changing database table prefix confused logins

Reported by: frankieandshadow Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: General Keywords:
Focuses: Cc:

Description

I was moving a site from one server to another and did the following:

  1. install vanilla wordpress and check it worked (it did), using prefix tst_... in wp-config
  2. mysqldump on old server,
  3. edited the dump to replace all the INSERTs etc from wp_... to tst_... and
  4. deleted vanilla tables and uploaded the edited sql to the database.
  5. replaced vanilla wp-content with wp-content from the old server

(I wanted to do this as the new host only has one database, and I wanted to put a staging version and live version in the same database with different prefixes).

I could view the site but when logging in as admin it appeared to recognise the login and gave the usual black bar across the top with "Howdy, ...", but always redirected back to the home page from login, and gave "you are not allowed to access this page" when trying /wp-admin or similar. Either jetpack or wordfence eventually blocked me, so I removed those, but while it no longer then locked me out it still wouldn't completely log in.

I was able to reproduce the problem on a different server using VirtualBox from the same set of files so I don't think it was anything peculiar about the specific host.

It was behaving as if admin were a user with no privileges. I suspect, on very little evidence, that the old prefix is stored somewhere either in the database (or just maybe wp-content), rather than getting it from the config file. But whatever the cause, it doesn't seem to work to just straightforwardly change the prefix in all the obvious places.

Change History (4)

#1 @knutsp
3 years ago

  • Resolution set to invalid
  • Status changed from new to closed

Hello frankieandshadow

You are right. The table prefix is used in keys in both options and usermeta tables to store roles and user capabilities on a per site basis. However, this Trac is for reporting bugs in the software, not for support.

Tutorial on WPBeginner: http://www.wpbeginner.com/wp-tutorials/how-to-change-the-wordpress-database-prefix-to-improve-security/
Support https://wordpress.org/support/

#2 @netweb
3 years ago

  • Milestone Awaiting Review deleted

#3 @frankieandshadow
3 years ago

Thank you for the link, but I wasn't seeking support. I was trying to report a problem when something that seemed like it should have worked didn't. That is still how it feels as a mere user.

May I respectfully suggest that the table prefixes are not stored in the tables themselves, but substituted in the relevant strings from the config setting. This would make this task much easier and more obvious, and remove the need to poke around in tables where it is not entirely clear what the function of the data is.

I often need to move Wordpress systems between servers and to change domain names and URLs and these operations seem very poorly supported in general.

#4 @knutsp
3 years ago

  • Version 4.8 deleted

Related: #20152

There are two tables in question:

  1. options (see the related ticket and my comment 15 months ago, and please add your comment, as this is fixable)
  2. usermeta

Then for usermeta you would have to either reopen and turn this ticket into an enhancement request or, probably better, create a new enhancement request ticket here. But, for it to have any chance of getting some attention and interest, you should provide an alternative, a suggestion on how it could be fixed or made simpler.

Database migration is not supported by WordPress. There are imports and exports for that task. But it's possible, and I do a lot of it myself, and there are tools and plugins that may automate the process, along with human experts. And all things are documented and described on the web, for those who can search, read and understand technical things on this level. Making things less complicated for WP engineers who want to do things like this are very rarely a priority for the core development team, unless there are obvious benefits in it for the end users.

Also remember that the users and usermeta tables are global for a WordPress network or collection of networks (Multisite), or it may be common to several single installations. If the capabilities key is not to be prefixed with the table prefix of the site it's valid for, how could it be stored discrete?

Note: See TracTickets for help on using tickets.