Large board upgrade error - "Data truncated for column 'poster_ip_old'"

[Gilrod]

So, uploaded RC2 last night to do the upgrade, got this error...

"Data truncated for column 'poster_ip_old' at row"

Ummm... now the upgrade won't continue, what can I do?

Cheers

Stew

[Gilrod]

Forgot to say, it was during this stage...

Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing: "Update messages poster_ip with ipv6 support (May take a while)" (47 of 65)

[Gilrod]

I did a full restore, ran database cleanup and re-did it, dies in the same place again...

[albertlast]

From which version you came?
Which mysql version you use?
Which datatype got the colum poster_ip of you messages table?

[Gilrod]

coming from 2.0.15

Database:
Server: Localhost via UNIX socket
Server type: MariaDB
Server connection: SSL is not being used Documentation
Server version: 10.2.23-MariaDB-log-cll-lve - MariaDB Server
Protocol version: 10
User: cpses_tnfqpdi3zj@localhost
Server charset: UTF-8 Unicode (utf8)

Datatype, what do you mean?

[albertlast]

In the database table smf_message you find poster_ip column,
which datatype it got (use phpmyadmin).

[Gilrod]

Does this help?

[albertlast]

Change your upgrade_2-1_mysql.sql in this way:
replace

Code Select Expand


upgrade_query("ALTER TABLE {$db_prefix}messages CHANGE poster_ip poster_ip_old varchar(200);");


with

Code Select Expand


upgrade_query("ALTER TABLE {$db_prefix}messages CHANGE poster_ip poster_ip_old varchar(255);");


related pr https://github.com/SimpleMachines/SMF2.1/pull/5678/files

[Sesquipedalian]

PR has been merged.

[Gilrod]

Hi,
I've done the change this morning, but it's STILL doing the same issue... I have confirmed that I did indeed upload the modified sql file.

Stewart

[albertlast]

well it make no sense that the error keep the same,
when you apply the correction correctly.

[m4z]

Be sure to actually (re)load the modified file in your browser, not from history/cache etc.

[Gilrod]

Be sure to actually (re)load the modified file in your browser, not from history/cache etc.

well I'm doing a full restore of the site, then re-uploading ALL the upgrade files for 2.1 RC2, then just running the upgrade script, so not sure how it wouldn't be there...

[albertlast]

Could it be that another table is mention?
Please post the error message again please.

[Gilrod]

Rename old ip column on messages...done
Error!
Data truncated for column 'poster_ip_old' at row 169964

[Arantor]

So what's in that row, from the database before upgrade?

[Gilrod]

Sorry for the delay, work etc...

Looking at that row it looks fine... even the rows around it look fine?!?

Code Select Expand

169964
5369
9
1077129984
126
0
Re: UPDATED: Rush 2004 "PARTIAL" Date Li
The_Letter_R
k********@m*******.com
62.8.125.182
0
0
[quote]It's obviously EuCon '04 you daft ninny's
xx
1

[albertlast]

give us the response of this sql code

Code Select Expand

select max(length(poster_ip)) from smf_messages

[Gilrod]

Providing I've done it correctly...

217

[Arantor]

Then: SELECT * FROM smf_messages WHERE LENGTH(poster_ip) = 217

[Gilrod]

Not the same ID...

176217   5525   9   1105461436   4   0   Newcastle Meet - 15 &amp; 16 April   Ashley_Davidson   sales@mar******ts.com   81.134.33.103 81.138.57.64 81.136.244.226 81.133.2...   0   1113178706   Ashley_Davidson   Update - Update<br><br>Friday night 15th.<br><br>W...   xx   1

[Arantor]

OK so that at least answers the question of what the column contains - it contains multiple IP addresses. Quite *why* it contains multiple IP addresses is another story and you should probably just trim the ones that are over-long by hand rather than trying modify the installer.

To find all the ones that are weird like this:

SELECT * FROM smf_messages WHERE poster_ip LIKE '% %';

This will find all the ones with spaces in so you can prune off the IP addresses after the first one.

[Gilrod]

wow, there's 5317 of them

as you can tell SQL isn't my thing... Is there a script/whatever I can run that will delete the extra ones for me?

[albertlast]

since the value is unvalid i would recommand to mark this entries (the ip values) as unkown:

Code Select Expand


update smf_messages set poster_ip = 'unknown' where poster_ip LIKE '% %'


Result would be that the ip address of this post get lost,
but in my eyes is the information already lost,
so nothing wrong here.

[Gilrod]

That did it! all upgraded to 2.1 now.

re: your question about HOW I had more than 1 IP address... I originally moved to SMF years ago, but from a YaBB2 forum, I seem to remember YaBB stored ALL your IP addresses you logged in from, NOT just the one from a specific post. I wonder if when going from YaBB to SMF it took at IP addresses used?!

Anyway, thanks for the help all!