Accidentally renamed a field with a name that already exists, lost all data for that field.

[FireWire]

Like the title says.

Accidentally renamed "wvprofile_body" to "Body" where I actually meant to change the label. Didn't notice that I had entered that into the wrong field, hit Save. ProcessWire destroyed the wvprofile_body table then showed me an error saying that the field "body" already exists.

I have no idea what happened. I've never had this happen before, but I've also never accidentally renamed a field to an existing field name. Has this happened to anyone else? Doesn't PW check for a unique name before deleting data? Looked in the database directly, the entire wvprofile_body table is gone.

Keep backups.

Edited May 30, 2023 by FireWire
Added solved tag after reason/solution was found

[cwsoft]

Hi,

damn, data loss sucks. What PW version are you using? Do you have debug on/off? Had some similar issue yesterday but PW showed a warning that some fields/data will get lost and asked if I would like to proceed. Using the latest public release from last week, not the dev version. Have you noticed any warnings before?

Cheers

[FireWire]

9 minutes ago, zx80 said:

Hi,

damn, data loss sucks. What PW version are you using? Do you have debug on/off? Had some similar issue yesterday but PW showed a warning that some fields/data will get lost and asked if I would like to proceed. Using the latest public release from last week, not the dev version. Have you noticed any warnings before?

Cheers

This was on a production server so debug is off, no warning. I am familiar with that data loss warning and didn't see it. I've only seen that when attempting to delete a field or change its type.

v3.0.165, so stable and not the latest.

I was able to restore the data by pulling a table from an SQL backup and running a query to create the table and insert all of the data. Was the main text field on ~120 pages so there was a chance for a lot of catastrophic data loss. ProCache kept the content live while I restored this but it was a real not-cool moment...

ProCache and Cronjob Database Backup can go a long way to save a site with no downtime. Just still really not sure if this is something everyone needs to keep an eye out for or if it was a one time glitch.

[Robin S]

I tested in 3.0.217 and when trying to rename a field to the same name as an existing field PW gave an error notice, did not change the field name, and no data was lost. So maybe it was a strange glitch, or it was an issue that was fixed between 3.0.165 and 3.0.217.

[wbmnfktr]

Tested in a local 3.0.217 without any data loss with the same behaviour I remember before when renaming a field.
If you want, I could downgrade to your version (whatever it is) and go from there. It's local and I could take a look.

[wbmnfktr]

1 hour ago, FireWire said:

Cronjob Database

I use this with/on all major or high profile projects that handle lots of data to create a backup each and every time soneone logs into the site. The space is available. And if not... log in every 4 week to delete a few backups.

[wbmnfktr]

Ok... so I made the move and downgraded everything down to 3.0.165

Still no data loss. Which is fine for me, yet... nothing that help's you a bit.

Is there something special in your setup?

 

[iank]

I think I may have replicated this.   (PW 3.0.200).   My suspicion is that it's related to renaming the field to 'Body' (uppercase 'B') when the field 'body' (lowercase 'b') already exists.  Processwire lets you name fields with upper and lowercase characters.    Here's what I did on my dev system:

1. Create a new field 'test_content'. (The field 'content' already exists).
2. Add 'test_content' to a template and populated several pages with data.   
3. Check the db.  The table field_test_content exists containing the data just added.
4. Attempt to rename 'test_content' to 'Content' (note uppercase C).
5. Receive error as shown below.  
6. Check db again.  The table field_test_content is no longer there.

It looks as if PW correctly checks for an existing field if the case matches, but goes ahead and deletes the table if not.

Subsequently, whenever I try to edit a page with a template containing that field, I now receive the same error.   

[FireWire]

13 hours ago, wbmnfktr said:

Ok... so I made the move and downgraded everything down to 3.0.165

Still no data loss. Which is fine for me, yet... nothing that help's you a bit.

Is there something special in your setup?

 

There was nothing special in the setup, it's a very similar setup to what I usually use on sites. Really appreciate you looking into it and running some tests!

2 hours ago, iank said:

I think I may have replicated this.   (PW 3.0.200).   My suspicion is that it's related to renaming the field to 'Body' (uppercase 'B') when the field 'body' (lowercase 'b') already exists.  Processwire lets you name fields with upper and lowercase characters.    Here's what I did on my dev system:

1. Create a new field 'test_content'. (The field 'content' already exists).
2. Add 'test_content' to a template and populated several pages with data.   
3. Check the db.  The table field_test_content exists containing the data just added.
4. Attempt to rename 'test_content' to 'Content' (note uppercase C).
5. Receive error as shown below.  
6. Check db again.  The table field_test_content is no longer there.

It looks as if PW correctly checks for an existing field if the case matches, but goes ahead and deletes the table if not.

Subsequently, whenever I try to edit a page with a template containing that field, I now receive the same error.   

This is it. It was the uppercase B in Body that I accidentally put into Name instead of Label that conflicted with another field named body.

Really appreciate @Robin S @wbmnfktr and @iank working to figure out what happened, I don't have the time to troubleshoot right now (deadlines!) and you really helped out. If I didn't find out what happened I would be kind of uneasy about the website. Ideally I think it would be best if field names were restricted to lowercase, but that would be a big breaking change at this point.

Many situations could cause this:

• Text intended for the Label could mistakenly be put into Name and all data is lost for that field (my case)
• A developer may be familiar with this bug, but not know or have forgotten that a field already exists with a name that will conflict
• Accidentally mistyping with a capital letter where a lowercase letter would have shown the appropriate duplicate field name error

I have opened a Github issue for this bug.

[iank]

I did a little more digging too. It seems as part of the field rename process, the corresponding (old) table is temporarily renamed to tmp_field_yournewname, and then tries to rename this temp table to the correct new name: field_yournewname.   This second part fails because the table already exists, so I believe this throws an Exception and you would expect the admin to re-render with an error notice. (Like it does with the normal duplicate field name error).

However, since the underlying field's DB table has been renamed to its _tmp version, there's another exception, showing the error.  At least I think that's what's happening.

DB table names are lowercase by default ($config->dbLowercaseTables is true unless overridden), so 'Body' will try to create 'field_body'.

@FireWire, you might find there's a tmp_field_body table in your DB, with all your content intact. 

Good to know there's a Github issue opened.

[FireWire]

54 minutes ago, iank said:

I did a little more digging too. It seems as part of the field rename process, the corresponding (old) table is temporarily renamed to tmp_field_yournewname, and then tries to rename this temp table to the correct new name: field_yournewname.   This second part fails because the table already exists, so I believe this throws an Exception and you would expect the admin to re-render with an error notice. (Like it does with the normal duplicate field name error).

However, since the underlying field's DB table has been renamed to its _tmp version, there's another exception, showing the error.  At least I think that's what's happening.

DB table names are lowercase by default ($config->dbLowercaseTables is true unless overridden), so 'Body' will try to create 'field_body'.

@FireWire, you might find there's a tmp_field_body table in your DB, with all your content intact. 

Good to know there's a Github issue opened.

Fantastic work! That table does exist in my DB.

As for DB table names being lowercase by default, that seems to conflict with the admin UI.

If the field lets you enter uppercase names, but table names default to lowercase, I think this issue is pretty much guaranteed to happen. I think that because the UI has shown that uppercase letters are accepted and it's been that way for a long time then the solution would be for $config->dbLowercaseTables to default to false. That would preserve the behavior shown in the admin and fix the issue going forward.

But, I too, have started to dig deeper.

I did a quick search because I was curious about case-handling in MySQL itself and if there might be additional scenarios to consider. Database case sensitivity is dependent on the filesystem of the underlying OS. Windows is not case sensitive, Unix-like systems such as Linux are case sensitive, macOS is almost always the exception to that rule where it's a Unix-like system but HFS+ is not case sensitive and APFS can optionally be (but only at the time of formatting the disk) and is not by default. Even Docker on Mac looks like it struggles with this.

So I'm wondering if this is an environment issue and having $config->dbLowercaseTables is true to mitigate potential issues. Regardless though, the admin UI/name field validation should reflect the current DB casing configuration. The better option would just be to eliminate using uppercase letters for fields in the future if it would act as a sort of protection for all environments.

[FireWire]

1 hour ago, iank said:

I did a little more digging too. It seems as part of the field rename process, the corresponding (old) table is temporarily renamed to tmp_field_yournewname, and then tries to rename this temp table to the correct new name: field_yournewname.   This second part fails because the table already exists, so I believe this throws an Exception and you would expect the admin to re-render with an error notice. (Like it does with the normal duplicate field name error).

However, since the underlying field's DB table has been renamed to its _tmp version, there's another exception, showing the error.  At least I think that's what's happening.

DB table names are lowercase by default ($config->dbLowercaseTables is true unless overridden), so 'Body' will try to create 'field_body'.

@FireWire, you might find there's a tmp_field_body table in your DB, with all your content intact. 

Good to know there's a Github issue opened.

Confirmed renaming the tmp field table to the original table name solved the issue. Many thanks for your work!

[FireWire]

Ryan has already responded to the Github issue and will merge the fix as soon as it can be confirmed. Passing along a PW API method call he shared that can fix this in case anyone that runs into this and wants to skip working in the DB directly..

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

Thanks @ryan!