Case-insensitivity causes data loss when renaming fields

[SkyLundy]

Short description of the issue

Renaming a field with a capitalized name that already exists in lowercase destroys the table and all data for the field you are renaming. The field that was being renamed is continues to exist under it's previous name within ProcessWire but now shows an error that its corresponding database table does not exist. First noticed in a site running v3.0.165, confirmed issue still exists in v3.0.200. Possibly affects all versions of ProcessWire.

Expected behavior

Renaming a field with a capitalized name that already exists in lowercase is recognized as unique and saves properly.

Actual behavior

Renaming a field with a capitalized name that already exists in lowercase deletes the table for the field being renamed and SQLSTATE[42S02] Base table or view not found error is shown in the admin. The field retains the original name and remains present in ProcessWire and assigned to templates.

Optional: Screenshots/Links that demonstrate the issue

Optional: Suggestion for a possible fix

Enable case-sensitivity for field names or detect field name similarity and show an error without data loss.

Steps to reproduce the issue

1. Create a field with the name body
2. Create a field with the name wvprofile_body
3. Rename the field wvprofile_body to Body

Setup/Environment

• ProcessWire version: 3.0.165
• (Optional) PHP version: 8.0.27
• (Optional) MySQL version: 5.7.41-log
• (Optional) Any 3rd party modules that are installed and could be related to the issue: Isolated core issue

[SkyLundy]

Discussions in the forum brought up the $config->dbLowercaseTables property and that is is true by default. This creates a conflict with the admin UI where field name input requirements state "Use only ASCII letters (a-z A-Z), numbers (0-9) or underscores."

So the input requirements conflict with the default configuration since it looks like field names and table names are directly correlated, but are where casing is unexpectedly changed on entry. Unfortunately this might break existing installations that already use cased field names.

Trying to figure out a non-breaking change for existing installations, one would be appending the field ID to the table name. There may be better solutions.

[ryancramerdesign]

@SkyLundy Thanks, I've pushed a fix for that. Btw, it shouldn't delete your table, it should just rename it to tmp_field_body. If you want, you can rename it back, but you'll have to use a query:

$query->exec('RENAME TABLE tmp_field_body TO field_wvprofile_body'); 

or rename to "field_body", whatever matches your field name.

[SkyLundy]

@ryancramerdesign Thank you for the quick fix! A member found that as well and posted it to the thread in the PW forums so that info is there as well. I'll test as soon as I am able to or see if someone else is available as well.

[SkyLundy]

That fixed the issue. Thanks again!