Make WordPress Core

Opened 6 years ago

Closed 3 years ago

Last modified 10 months ago

#44440 closed defect (bug) (invalid)

Case sensitive/insensitive bug

Reported by: dingdang's profile dingdang Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.9.6
Component: Database Keywords: needs-patch
Focuses: Cc:

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 (11)

#1 @pbiron
6 years 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.


6 years ago

#3 follow-up: @dingdang
6 years 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
6 years 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 follow-up: @pento
5 years 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
4 years 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.

#7 @desrosj
3 years ago

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

Closing this out again as the expected behavior detailed in comment:5 still stands.

#8 @dingdang
3 years ago

Just to mention that this is not related just to the fact that on Windows filenames are case insensitive.
What I originally described is that if on Windows installation a mixed prefix is used, then the installation ends as one that would work only on Windows, because for some reason the prefix is converted to lowercase (BY WORDPRESS) and ONLY in some cases, thus creating incompatible installation with Linux (if moved).

#9 in reply to: ↑ 5 @theking2
10 months ago

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

MySQL and MariaDB have to take this in account as MYISAM stores tables in files with the same name. In Windows this potentially creates clashing filenames. Thereupon during installation of MySQL it sets system variable lower_case_table_names to 1 on Windows (0 on Linux and 2 on macOS)

I recommend only using lower case characters as table prefix if you intend to migrate them between different operating systems.

#10 @ReneHermi
10 months ago

I recommend only using lower case characters as table prefix if you intend to migrate them between different operating systems.

We could show a warning to always use lower case table names and prefixes even on windows environment. So if user ever wants to migrate to another OS due to changed requirements it will reduce the risk of running into an issue.

So if there is no agreement to fix this issue programmatically, we at least should show a warning in the installer dialogue and recommend the use of lower case prefix names. This can be a tooltip or one liner and will prevent user from experiencing migration issues in the future.

Last edited 10 months ago by ReneHermi (previous) (diff)

#11 @dingdang
10 months ago

If you read what I've mentioned above - the problem is caused by WordPress because it lowers the case of the prefix (not related to the Windows at all).

Note: See TracTickets for help on using tickets.