search

My-Little-Forum / mylittleforum

A simple PHP and MySQL based internet forum that displays the messages in classical threaded view (tree structure)
GNU General Public License v3.0
124 stars 48 forks source link

Fix: change charset of column user_email to utf8 … #629

Closed auge8472 closed 2 years ago

auge8472 commented 2 years ago

… to prevent an index size error during the upgrade. This happens (depending on the MySQL version) when adding the index key_user_email, in case of a maximal index size of 767 bytes.

The charset has to be corrected before the index gets added. The code manipulates the upgrade section for version 2.4.99.2. The identical database query is also part of the upgrade to version 20220517.1. I was not able to produce an error when altering the column definition once with the upgrade to 2.4.99.2 and again with and to the identical settings when upgrading to the current master. So I tend to assume, that the script will not break.

auge8472 commented 2 years ago

Found an issue during testing. The keyword UNIQUE in the alter statement will create an additional index when executed again. Let's say, the index of a column is named user_date (like the column) during the creation of the table and the alter statement is

ALTER TABLE users CHANGE user_date user_date VARCHAR(256) CHARACTER SET utf8 NOT NULL UNIQUE;

the result is an additional index user_date_2. Have to inspect it.

auge8472 commented 2 years ago

The keyword UNIQUE in the alter statement will create an additional index when executed again.

I can confirm that altering a column definition with the keyword UNIQUE like in the statement in the comment above, adds an index and executing the statement again adds another index. Altering the column without the keyword changes the definition without altering an already existing index/key. The existence of multipe identical indexes does not break the database but it leads to warnings about these identical indexes (at least in phpMyAdmin).

So I'll try to remove the unnecessary indexes. In the table for the userdata (usually mlf2_userdata) I'll search for indexes that begins with user_ what will find the indexes, that are generated automatically when altering the column user_email. Valid indexes in this table are currently key_user_name, key_user_type and key_user_email. So searching for user_ will not break anything. After finding something, I'll delete the superfluous index(es) in a loop.

The correspondent commit will follow.