Upgrader: Minor DB differences in install vs upgrade

[sbulen]

Description

Some minor differences noticed when doing an install vs an upgrade:

• smf_log_errors, session column, the default value is different (empty string vs multiple spaces)
• smf_members, message_labels column, is not deleted
• smf_pm_recipients, labels column, is not deleted
• smf_package_servers, validation_url column - nullable is different between install & upgrade

Other differences of note: Note that if the upgrader needs to do a utf8 conversion, that conversion changes data types of many text columns, e.g., from text to mediumtext. The same set of differences exists if the utf8 conversion was done in the 2.0 acp, prior to an upgrade.

If no utf8 conversion was ever needed, e.g., the 2.0 forum was created using utf8, after upgrade the data types all match a fresh 2.1 install.

Not sure we need/want to do anything about this, just documenting for awareness.

Steps to reproduce

1. Do an install
2. Do a seprate upgrade from 2.0.19
3. Compare the DB exports; they should be equivalent

Environment (complete as necessary)

• Version/Git revision: Current
• Database Type: MySQL
• Database Version: 8
• PHP Version: 8

[Sesquipedalian]

• smf_log_errors, session column, the default value is different (empty string vs multiple spaces)
• smf_members, message_labels column, is not deleted
• smf_pm_recipients, labels column, is not deleted

We probably should address these ones. I don't think the rest of the differences you describe are a concern.

[sbulen]

I don't think the rest of the differences you describe are a concern.

Agreed. Just documenting for awareness. I hadn't noticed those differences before myself.

[jdarwood007]

Do any of these concern errors such as mysql 8 generates for strict mode? If they don't, I would leave this for the next version.

It isn't safe to edit things like message_labels on the members column for large forums in a update package.

[sbulen]

The thing is that it tries to delete them. But that's failing somehow.

[sbulen]

Added one more.... smf_package_servers validation_url - whether the column is nullable is different between install & upgrade.

[Oldiesmann]

Do any of these concern errors such as mysql 8 generates for strict mode? If they don't, I would leave this for the next version.

It isn't safe to edit things like message_labels on the members column for large forums in a update package.

Why isn't it safe? That column is from back when we stored PM labels as a JSON-encoded list. I rewrote that years ago to put PM labels in a separate table instead.

[sbulen]

Yep. Until recently(-ish), those label columns were being successfully deleted. After a change, the deletion doesn't work.

It's safe to make the delete work again.

[jdarwood007]

Why isn't it safe? That column is from back when we stored PM labels as a JSON-encoded list. I rewrote that years ago to put PM labels in a separate table instead.

I realize I was more thinking of messages table, but the same could still apply to the message_labels. Our package manager is not designed to handle timeouts or long processes.

[sbulen]

Feels like we're overthinking this...

We delete many old unused cols via the upgrader.

In this case, the upgrader used to delete these cols. A somewhat recent change broke that. We should fix the bug & delete the cols again.

[Oldiesmann]

I realize I was more thinking of messages table, but the same could still apply to the message_labels. Our package manager is not designed to handle timeouts or long processes.

Not really something worth dealing with in the package manager in my opinion. We should at least deal with it in the large upgrade.

[jdarwood007]

Thats fine, but these will exist until we take care of the next release.