WordPress.org

Make WordPress Core

Opened 16 months ago

Last modified 5 days ago

#44440 reopened defect (bug)

Case sensitive/insensitive bug

Reported by: dingdang Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.9.6
Component: Database Keywords: needs-patch
Focuses: Cc:
PR Number:

Description

This has been discovered while migrating a site from a Windows server to Linux server. The key to the bug is to have a table prefix with capital letters, for example pReFiX.
On the Windows server (however unknown database server/version) this end in LOWER CASE table prefixes while at the same time several table cells still keep the UPPER CASE version of the value "pReFiX":

  • one - in table "prefix_options" called "pReFiX_user_roles"
  • several - in table "prefix_usermeta" like "pReFiX_capabilities", "pReFiX_user_level" etc

In wp-config.php the prefix is lowercase.

  1. This somehow works on the Windows server
  2. When such a site is migrated to a Linux server, a user can log in but then because of the credentials mismatch it loses its role and access is forbidden.

To fix this one must manually replace all occurrences of UPPERCASE prefix to LOWERCASE.

Change History (6)

#1 @pbiron
16 months ago

My envs:

  • windows 10 pro
    • WP 4.9.6
    • MySQL 5.7.10 (engine=InnoDB, db collation utf8mb4_unicode_520_ci)
    • PHP 5.6.36
  • linux (Kernel Version 2.6.32-673.26.1.lve1.4.29.el6.x86_64, I think it's centOS but not positive)
    • WP 4.9.6
    • MySQL 5.6.39 (engine=MyISAM, db collation utf8mb4_unicode_520_ci)
    • PHP 5.6.20

I can confirm:

  1. in windows, I end up with table names like prefix_xxx; while in linux I end up with table names like pReFiX_xxx
  2. the values in the option_name and meta_name columns of the relevant rows of $wpdb->options and $wpdb->usermeta are pReFiX_xxx in both windows and linux

However, I can not replicate the problem of "loses its role and access is forbidden" on linux. I am able to access admin funcs (i.e., create posts, etc) in both windows & linux envs.

This ticket was mentioned in Slack in #core by pbiron. View the logs.


16 months ago

#3 follow-up: @dingdang
16 months ago

In Windows also prefix setting in wp-config.php is lowered case, while in Linux it s mixed ("pReFiX_"). Thus after a migration (copy of the files and database from Windows to Linux) everyhing would work on the frontend as the prefix would match (lower case), the only problem is that in options and usersmeta tables it is in uppercase. After a successful login an error message "You don't have access to that page" appears (even with correct login/password).
Manual fix of the prefix case in these two tables fixes the problem.

The bug is in Windows environment - as the table prefix is converted to lower case (as well in wp-config.php), the values in the two mentioned tables should be lower case as well.

#4 in reply to: ↑ 3 @pbiron
16 months ago

Replying to dingdang:

In Windows also prefix setting in wp-config.php is lowered case, while in Linux it s mixed ("pReFiX_").

Are you saying that wp-config.php on windows contains:

<?php
$table_prefix = 'prefix_';

and on linix it contains:

<?php
$table_prefix = 'pReFiX_';

If so, then I'm lost. If you've got $table_prefix = 'prefix_' on windows and you populated the database on windows, how did you get the row in $wpdb->options to have option_name === 'pReFiX_user_roles'?

Also, if that is what you're doing, why are you doing that? It's kind of like having wp-config.php on windows contain

<?php
define( 'DB_CHARSET', 'utf8' );

and on linix having it contain:

<?php
define( 'DB_CHARSET', 'ascii' );

and then wondering why when you "migrate from windows to linux" you get data corruption.

#5 @pento
3 months ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

Difference in case sensitivity is expected behaviour when migrating between Windows and Linux, this is due to NTFS being case insensitive, while all Linux filesystems are case sensitive.

I recommend only using lower case characters in your table names if you intend to migrate them between different operating systems.

#6 @ReneHermi
5 days ago

  • Resolution invalid deleted
  • Status changed from closed to reopened

Even MySQL recommends lowercases due to the mentioned complications:

" To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use."

Source: https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

A quick fix would be to convert the selected table prefix to lowercase when WordPress is installed. This would be fully compatible for existing sites and would fix the issue for all future installations.

At WP Staging we experienced this serious issue several times when people select a prefix with capitalization and they move their site from Windows to Linux.

With uppercase table prefixes, it's very difficult to move a WP site from one system to another and there is no way to do this automatically.

Note: See TracTickets for help on using tickets.