Advertisement:

Author Topic: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2  (Read 438 times)

Offline kenk2005

  • Newbie
  • *
  • Posts: 4
Thanks guys - great forum.

I was running SMF 2.0.15 and wanted to ditch MySQL for PostgreSQL and I did the successful conversion using the superb tool pgloader. That was smooth - I just had to rename the sequence names to what SMF likes, ie "smf_topics_seq". The transition was fine but I noticed in 2.0.15, there are not specific PostgreSQL datatypes being used such as boolean (instead varchar(1) was used...)

Anyhow, things mostly worked but I had to alter some of the code for casting with some cols in the database, ie:

Sources/Subs.php, line 589, I had to put the "::int"  as follows:

"'value' => $value === true ? 'value::int + 1' : ($value === false ? 'value::int - 1' : $value)"

And I did in various other places like also in Sources/Search.php ("{string:message_list_in_set}::text"). So I don't know if table columns didn't convert right but.... I thought to upgrade to 2.1 RC2 just to get everything up to date with the SQL and where I don't have to hack anything.

So, I ran the installer - the only change I had to make to the database when running upgrade.php was:

alter table smf_ban_items add ip_low1 int; -- all the way to ip_low4
alter table smf_ban_items add ip_high1 int; -- all the way to ip_high4

The upgrade.php went fine and upgraded what it had to. When I try to load the forum, I get:

array(9) {
  • => array(3) { ["file"]=> string(45) "/var/www/somewhere/Sources/Errors.php" ["line"]=> int(290) ["function"]=> string(9) "log_error" } [1]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(3091) ["function"]=> string(17) "smf_error_handler" } [2]=> array(1) { ["function"]=> string(9) "{closure}" } [3]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(3092) ["function"]=> string(21) "preg_replace_callback" } [4]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(2949) ["function"]=> string(12) "parsesmileys" } [5]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(3424) ["function"]=> string(9) "parse_bbc" } [6]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(3652) ["function"]=> string(17) "setupThemeContext" } [7]=> array(3) { ["file"]=> string(43) "/var/www/somewhere/Sources/Subs.php" ["line"]=> int(3274) ["function"]=> string(15) "template_header" } [8]=> array(3) { ["file"]=> string(36) "/var/www/somewhere/index.php" ["line"]=> int(154) ["function"]=> string(6) "obExit" } } Error loop.


I've put everything back up from the backups, but was wondering if others had this issue with what seems to be the smileys. I see another issue on here regarding smileys but unclear if it's that.

I had a fresh install so I don't think it was a Theme or mod. If it was a theme, which may be set in the database, but is not in the file directory, how do I reset that theme to default? If it is that...

Thank you

Ken

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 580
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #1 on: April 21, 2019, 01:03:29 AM »
To migrate from mysql 2.0.15 to pg 2.0.15 is best way to install an empty 2.0.15 on pg,
with this you get the right table structur and datatypes.

Another way would be to create a second database in you pg env and setup ther an empty 2.0.15 again and
use the tool like https://www.postgrescompare.com/ to check the dif between you migrated version and the offical pg schema.

After the release of rc2 two fixes are created for pg setups:
https://github.com/SimpleMachines/SMF2.1/pull/5596
https://github.com/SimpleMachines/SMF2.1/pull/5593

Offline kenk2005

  • Newbie
  • *
  • Posts: 4
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #2 on: April 21, 2019, 02:31:03 AM »
albertlast, thank you. I would have done the new setup for 2.0.15, but there was no script I could find that would convert with structure already there - I think pgloader creates all from scratch.

I ran your second suggestion for the comparison and changed a few things from the script (like smallserial couldn't be created, etc) but I ran it all successfully. I used a fresh database for the comparison. Now I see the smf_ban_items with all the proper columns. It was mostly setting or removing defaults and creating additional indexes.

Will get the new pulls and try again.

I'm glad for the PostgreSQL support, truly.

Offline kenk2005

  • Newbie
  • *
  • Posts: 4
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #3 on: April 21, 2019, 02:43:11 AM »
The one problem I see is that I still have to cast the following:

'value' => $value === true ? 'value::int + 1' : ($value === false ? 'value::int - 1' : $value),

(Sources/Subs.php line 589)

The tables are the exact same (variable and value being text). Without the casting, I see:

ERROR: operator does not exist: text + integer
LINE 3: SET value = value + 1

But this is version 2.0.15 - don't mean to hijack this forum, otherwise all else seems perfect!

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 580
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #4 on: April 21, 2019, 03:10:21 AM »
When you look into the setup file of pg,
than you notice that smf create some helper function in the first 124 line of code,
they you missing and reason why you need to change the code.
Install this function you should be fine or at least better.

Offline albertlast

  • Development Contributor
  • Full Member
  • *
  • Posts: 580
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #5 on: April 21, 2019, 03:28:09 AM »
2.1 need also the help function,
beside of this 2.1 did a huge jump of supporting pg.

Reason why newer version of pg is needed (min 9.4 recomandet newest),
some little feature exists only on a pg setup.

Offline kenk2005

  • Newbie
  • *
  • Posts: 4
Re: Migrated to PostgreSQL and then errors when upgrading to 2.1 RC2
« Reply #6 on: April 21, 2019, 11:51:46 AM »
Yes, am running PostgreSQL 11. It's good it's being supported; it is a popular and powerful database. I appreciate the developers here took the time to add it in.

Thank you for helping me with this; all seems to be good now - no more casting in the code - all looks good! I put in the other functions and the operators - I think some were in there already not all.

Thank you, albertlast! I converted fully!