[2.1 RC1] - Upgrade issue where poster_ip_old = unknown

[Ben_S]

If there are values for poster_ip_old that do not translate to a valid IP (in my case there were 5217 set to unknown), the upgrade will never finish as it will keep trying to run the same query resulting in this over and over again (for 3 days before I noticed it):-
   
Wrong value type sent to the database. IPv4 or IPv6 expected.(ip28)<em>(upgrade.php-4947)</em> File: ***/Sources/Subs-Db-mysql.php Line: 921Error: inet_pton(): Unrecognized address unknown File: ***/Sources/Subs-Db-mysql.php Line: 293Error:

update yabbse_messages m
set m.poster_ip_old = '127.0.0.1'
where m.poster_ip_old = 'unknown';
/* Affected rows: 5,217  Found rows: 0  Warnings: 0  Duration for 1 query: 7.594 sec. */

Also noticed this, in upgrade_2-1_mysql.sql

   2183 ---# Create an ip index for old ips
   2184 ---{
   2185 $doChange = true;
   2186 $results = $smcFunc['db_list_columns']('{db_prefix}members');
   2187 if (!in_array('member_ip_old', $results))
   2188         $doChange = false;
   2189
   2190 if ($doChange)
   2191         upgrade_query("CREATE INDEX {$db_prefix}temp_old_poster_ip ON {$db_prefix}messages (poster_ip_old);");

It is checking members rather than messages so the index will never get created.

[albertlast]

This should fix both issues: https://github.com/SimpleMachines/SMF2.1/pull/5436

[Ben_S]

Thanks, they should do it.

If i ever migrate to 2.1, I'll probably strip out a bunch of stuff from upgrade that can be run once the upgrade has been done to avoid being down for so long.

[albertlast]

Personal opinion:
by this size of your setup i wouldn't use mysql/mariadb.
Because the database is very limited in many ways,
reason why i recommand for big setup of 2.1 to use postgres.

i added many optimization to pg to handle this case better than mysql.
Since i got only mid size forum for my test could i maybe miss some places.

[Ben_S]

I'm a MySQL kinda person, the biggest one I work with is pushing 500GB for an EPOS system, 15GB is nothing in comparison .

[albertlast]

I only describe the stat of mysql and pg in relation of smf 2.1.

[Ben_S]

Is there anyone with a reasonable sized forum actually using pgsql? To me it was one of those things someone decided to add that just caused a dev nightmare having to continually support with barely any use.

[albertlast]

i guess not,
but that doesn't change the case that we got more possibility on pg side and
without hurting the mysql performance.

[Arantor]

Is there anyone with a reasonable sized forum actually using pgsql? To me it was one of those things someone decided to add that just caused a dev nightmare having to continually support with barely any use.

More time has been spent on it but I can't imagine many people using it at all.