Upgrade stuck at "Update members ip with ipv6 support" (46 of 65)

[simplengmakina]

Hi everyone!

I tried Upgrade, but I'm stuck at "Update members ip with ipv6 support" (46 of 65).

Doesn't really report an error.

[shawnb61]

A few questions about your members table in its current state:
- Do you have a column called "member_ip_old"?
- Do you have a column called "member_ip2_old"?
- Do you have an index called "smf_temp_old_ip"?  (or similar - you may have a prefix other than smf_)
- Do you have an index called "smf_temp_old_ip2"?  (or similar - you may have a prefix other than smf_)
- What does the data look like in member_ip & member_ip2 now?

Trying to figure out where/why it may be stuck.   

[live627]

Also, may be useful: any member IPs that just say unknown?

[simplengmakina]

Also, may be useful: any member IPs that just say unknown?

There were, but I already revised them to actual IPs. Still the same problem.

[simplengmakina]

 

A few questions about your members table in its current state:
- Do you have a column called "member_ip_old"? Yes
- Do you have a column called "member_ip2_old"? Yes
- Do you have an index called "smf_temp_old_ip"?  (or similar - you may have a prefix other than smf_) Yes
- Do you have an index called "smf_temp_old_ip2"?  (or similar - you may have a prefix other than smf_) Yes
- What does the data look like in member_ip & member_ip2 now? It doesn't look like an IP. Weird characters.

Trying to figure out where/why it may be stuck.

[shawnb61]

Can you show an example of the weird characters?

("How weird is it?")

[shawnb61]

The most likely problem here is that somehow you have non-valid IPs in your members table. 

We need to confirm this.   

Background info - the upgrader takes member_ip & member_ip2 and renames them to member_ip_old and member_ip2_old, then creates new columns for member_ip and member_ip2.   If your DB is still in this state, your 2.0.15 member IP data should be in member_ip_old and member_ip2_old. 

We have always understood we may be IPs and blanks in there.  We recently learned there is a lot of data out there with 'unknown' in the IP columns.   RC2 will have a fix for the 'unknown' values:
   https://github.com/SimpleMachines/SMF2.1/pull/5436

So... 

I think the following query will let us know if there is funky data out there that we don't know how to deal with yet - this assumes your upgrade is still halfway thru the IP steps & you still have the _old columns out there:

Code Select Expand

SELECT id_member, member_ip_old, member_ip2_old FROM smf_members
WHERE (member_ip_old not like '%.%.%.%' and member_ip_old != '' and member_ip_old !=  'unknown')
or (member_ip2_old not like '%.%.%.%' and member_ip2_old != '' and member_ip2_old !=  'unknown');

If you could let us know what this finds, it would help. 

[simplengmakina]

So I managed to get through the members and sort of the messages table too. But I made some "manual mods" to the sql. Install finished but posts are not showing at all.

Here's a link to the forum. Everything else works fine.

https://www.autoindustriya.com/talkboard/index.php

[Arantor]

What manual mods did you make, exactly?

[simplengmakina]

What manual mods did you make, exactly?

Since the columns member_ip_old and member_ip2_old were mentioned, I just deleted them. I also removed the indexes.

I followed the same "trick" with the messages table.

Posts are now showing for some strange reason. Well, I uninstalled the SMFPacks SEO mod, it might have been the cause.

Now having:
2: inet_ntop(): Invalid in_addr value
on Subs.php

[Doug Heffernan]

What manual mods did you make, exactly?

Since the columns member_ip_old and member_ip2_old were mentioned, I just deleted them. I also removed the indexes.

I followed the same "trick" with the messages table.

Posts are now showing for some strange reason. Well, I uninstalled the SMFPacks SEO mod, it might have been the cause.

Now having:
2: inet_ntop(): Invalid in_addr value
on Subs.php

You can not delete columns from the db jus like that. Anyways, I just loaded your forum and posts were showing up fine to me.

[simplengmakina]

What manual mods did you make, exactly?

Since the columns member_ip_old and member_ip2_old were mentioned, I just deleted them. I also removed the indexes.

I followed the same "trick" with the messages table.

Posts are now showing for some strange reason. Well, I uninstalled the SMFPacks SEO mod, it might have been the cause.

Now having:
2: inet_ntop(): Invalid in_addr value
on Subs.php

You can not delete columns from the db jus like that. Anyways, I just loaded your forum and posts were showing up fine to me.

Too late for that now

I've traced the error to an IP validation issue.

5732:    * @param string $bin An IP address in IPv4, IPv6 (Either string (postgresql) or binary (other databases))
5733:    * @return string|false The IP address in presentation format or false on error
5734:    */
5735:   function inet_dtop($bin)
5736:   {
5737:      if (empty($bin))
5738:         return '';
5739:   
5740:      global $db_type;
5741:   
5742:      if ($db_type == 'postgresql')
5743:         return $bin;
5744:   
==>5745:      $ip_address = inet_ntop($bin);


Is there a Cloudflare IP fix for 2.1 also as with 2.0.x?

[simplengmakina]

Did some more digging and the errors are caused by messages with blank "poster_ip"

And btw poster_ips look like this: "¬ƒó"

Did a fresh install to another table set and IPs looked like that also. Is this normal?

[shawnb61]

Unfortunately, you deleted the old IP addresses, so I'm not sure we can help you recover that information and convert it properly.   

I have lots of questions at this point... 

Can you redo the 2.1 conversion?  Is this a test environment or a production one?

I'm wondering if the upgrader ever ran to completion...  Did you rerun the upgrader after it froze?   If it froze during the DB steps, it may never have properly completed the utf8 or json conversions, which will cause other issues. 

When you say "And btw poster_ips look like this: "¬ƒó", where are you looking at them?  On a screen in your forum?  In the db using phpmyadmin?

Are all of your ip columns now varbinary(16)?

In my 2.1 environments, IP addresses in the forum look similar to: "79.93.39.179"  And in the DB, via phpmyadmin, they are in this format "0x475d27af" (dummy data...). 

In an ideal world, we should fix your 2.0 data in 2.0 & rerun the upgrader.   

[simplengmakina]

Unfortunately, you deleted the old IP addresses, so I'm not sure we can help you recover that information and convert it properly.   

I have lots of questions at this point... 

Can you redo the 2.1 conversion?  Is this a test environment or a production one?

Unfortunately, I didn't backup.

I'm wondering if the upgrader ever ran to completion...  Did you rerun the upgrader after it froze?   If it froze during the DB steps, it may never have properly completed the utf8 or json conversions, which will cause other issues. 

Yes updated finished. I had to rerun it after each freeze. Only problems I got into were the members and messages folder where I did the daring column deletions.

When you say "And btw poster_ips look like this: "¬ƒó", where are you looking at them?  On a screen in your forum?  In the db using phpmyadmin?

They display okay in the forum. It shows in the db that way via the php client that I'm using. It shows a varbinary on phpmyadmin.

Are all of your ip columns now varbinary(16)?

In my 2.1 environments, IP addresses in the forum look similar to: "79.93.39.179"  And in the DB, via phpmyadmin, they are in this format "0x475d27af" (dummy data...).

In an ideal world, we should fix your 2.0 data in 2.0 & rerun the upgrader.

So I can theoretically run an sql script just to fill those blank fields with whatever ip addresses right? shouldn't really matter right?

There's about 1915 posts affected.

[albertlast]

To full with null works is better,
From pov

[simplengmakina]

To full with null works is better,
From pov

Sorry I don't quite understand this

[albertlast]

The "new" ip fields are not designed to store blank or unvalid ip address,
only valid ip address or null values are allowed to save in the new type of ip fields.

So when you don't know what to input you could insert the binary value of 127.0.0.1 or null.

[simplengmakina]

The "new" ip fields are not designed to store blank or unvalid ip address,
only valid ip address or null values are allowed to save in the new type of ip fields.

So when you don't know what to input you could insert the binary value of 127.0.0.1 or null.

Is there a mysql query I could use to convert the IP into varbinary(16)? The blank IPS are [Null] and are the ones causing an error.

[albertlast]

yes,
but the error message with backtrace is more intresting,
since it should work with null (not blank) values.