[2.1 RC1] - members pm_ignore_list new data type is too small.

Started by Ben_S, February 18, 2019, 05:51:57 AM

Previous topic - Next topic

Ben_S

---# Updating members pm_ignore_list
ALTER TABLE {$db_prefix}members
MODIFY COLUMN pm_ignore_list VARCHAR(255) NOT NULL DEFAULT '';


I have a lot of members where they have an ignore list that contains sigificantly more that 255 chars,
Liverpool FC Forum with 14 million+ posts.

Gwenwyfar

"It is impossible to communicate with one that does not wish to communicate"

shawnb61

Ben_S -

A few questions -

What is the datatype of that column today?  Text, mediumtext?

Do you know how it was set to something other than varchar(255), did you change it manually?

EDIT:  One more question - are there any other columns too small?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Ben_S

It's currently text, fairly sure that must be the 2.0 default as it doesn't strike me as something I would have changed.

The upgrade script explicitly tries to set it to 255, presumably that was a concious decision as it was previously something else (text)?

Nothing else too small that upgrade picked up on.
Liverpool FC Forum with 14 million+ posts.

albertlast

when you look at the 2.0.15 setup file,
you notice that this field is a 255 varchar.
Which make sense since in mysql text field are blob -> slow.

Arantor

Quote from: Ben_S on February 18, 2019, 04:56:49 PM
It's currently text, fairly sure that must be the 2.0 default as it doesn't strike me as something I would have changed.

The upgrade script explicitly tries to set it to 255, presumably that was a concious decision as it was previously something else (text)?

Nothing else too small that upgrade picked up on.

Fairly sure it was historically a tinytext which were all converted to varchar(255); if it was historically a text, it would have stayed one.

shawnb61

Arantor is correct.  I checked installers for the various versions.  It was tinytext in 1.1, & varchar(255) afterwards. 

I think folks went thru periods of changing these by hand.  When I started helping out on my forum, it was mediumtext...

The question is whether we need to modify the upgrader or not.   My initial thought is to leave it alone. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Sesquipedalian

Well, setting it to a varchar with a larger maximum should be harmless. Standard SMF has always limited that field to a maximum of 255 characters, so the current behaviour in 2.1 isn't a bug and I don't consider it a high priority to change this, but I'm willing to entertain the idea of increasing it to something larger, as long as it is still a reasonable size.

Since you have one of the largest SMF forums out there, Ben_S, I'd be interested to know the largest number of characters any of your users have in that field. If you could run this query and report the result, that'd be lovely.


SELECT MAX(LENGTH(pm_ignore_list)) FROM smf_members;


Obviously, replace "smf_" in that query with whatever the correct prefix is in your database.
I promise you nothing.

Sesqu... Sesqui... what?
Sesquipedalian, the best word in the English language.

Ben_S

Quote from: Arantor on February 18, 2019, 07:00:02 PM
Quote from: Ben_S on February 18, 2019, 04:56:49 PM
It's currently text, fairly sure that must be the 2.0 default as it doesn't strike me as something I would have changed.

The upgrade script explicitly tries to set it to 255, presumably that was a concious decision as it was previously something else (text)?

Nothing else too small that upgrade picked up on.

Fairly sure it was historically a tinytext which were all converted to varchar(255); if it was historically a text, it would have stayed one.

Interesting, not sure how it ended up as text, although it did start out life as YaBB SE and ran many betas and SVN snapshots in the early SMF days so who knows.

I thought I'd already posted how large the largest one is but can't see it, the largest is 6185 - that guy must really hate people!
Liverpool FC Forum with 14 million+ posts.

Arantor

Honestly, that's big enough that I wouldn't encourage making it an in-line value in the row with a varchar (especially as it would consume 24KB to support out of a 64KB row without anything else being considered, unless MySQL has drastically changed how it treats rows and I didn't notice)

albertlast

I checked yesterday the 8.0 guide,
It keep saying the 64kb limit.
Reason why not sure if we want to extend,
The size in general.
Alternative to change the field to text,
Could change the behaviour of performance.

digger

Quote from: Sesquipedalian on February 19, 2019, 01:56:14 AM
Well, setting it to a varchar with a larger maximum should be harmless. Standard SMF has always limited that field to a maximum of 255 characters, so the current behaviour in 2.1 isn't a bug and I don't consider it a high priority to change this, but I'm willing to entertain the idea of increasing it to something larger, as long as it is still a reasonable size.

Since you have one of the largest SMF forums out there, Ben_S, I'd be interested to know the largest number of characters any of your users have in that field. If you could run this query and report the result, that'd be lovely.


SELECT MAX(LENGTH(pm_ignore_list)) FROM smf_members;


Obviously, replace "smf_" in that query with whatever the correct prefix is in your database.
I have some large forums and my largest lenght for pm_ignore_list is 865.

albertlast

a possible "pro" for text would be that the buddy_list field in members is a text datatype

albertlast

with rc2 the field get a text field,
like the buddy_list column

Advertisement: