Hi everybody,

I currently run two sites with mojoportal 1 (one featuring an open source toolkit for security research, the other one being my personal homepage - I think one of the earliest pages having used mojoportal under Linux/mono) and have been quite happy with it so far. Now that mojoportal 2 runs smoothly under Linux/mono (that is, with sufficiently recent mono packages being available in Debian proper, albeit still with some experimental package mix-in), I would like to update.

Prior tries with importing my (PostgreSQL) database into a test system and then updating the database schema using mojoportal db-scripts went horribly wrong, with mojoportal 2 unable to start properly and not being able to display even the main entry page. Thus, my current try is a fresh mojoportal 2 install with "empty" database (that is, automatically created by the setup), which works nicely on its own (again, on a Debian system).

Now the problem: How can I import the old site data into the new installation without doing it manually by copy&paste using a browser? I already tried dumping the old database in form of SQL inserts and selectively (only those tables that are not for configuration but seem to contain pure data) importing them in the new database, which obvioiusly doesn't work in a lot of cases because the schema changed (and, btw, is a nice way to break the new installation).

Is there a way to import this data, or do I need to try and convert the old site using the schema update scripts? The latter approach seems even more problematic than the first one. I don't use too many mojoportal modules right now, mostly static HTML data and one blog/news block for each of the sites. If I could import just the page structure and static HTML fragments, I could rather easily adapt the rest.

Any hints would be highly appreciated,

Rene

Hi Joe,

Wow, you're as quick in responding as ever!

I'll try to follow your advice and go the upgrade route again. Would you recommend running the db-update scripts manually to change the schema and then see what new tables are missing?

PS: The only reason why I haven't create a new mojoportal Debian package yet is simply lack of time. But if anybody else would like to spend some time towards that aim, I would be more than happy to assist (and also sponsor package uploads!).

best regards,

Rene

Hi Joe,

My current version is 1.0.4 with a few updates taken from SVN to fix mono problems. I could successfully dump the current database, and then run the update scripts in this order (on a test server): 3CreateStoredProcedures.sql, upgradefrom1-0to1-01-or-2-01.sql, upgradefrom2-01to2-1.sql, upgradefrom2-1to2-2.sql, upgradefrom2-2-0to2-2-1.sql, and upgradefrom2-2-1to2-2-1-5.sql (this one is in fact empty for PostgreSQL it seems). Then I ran 3CreateStoredProcedures.sql to update/create those functions that required new tables.

For the record, I initially ran those as the database superuser (postgres) to avoid permission problems. This in turn created permissions problems for the Setup/Default.aspx routine because it created new tables and functions as user "postgres". The tables were not many and easily fixed manually, but for the functions I scripted the owner change:

    echo "\df" | sudo sudo -u postgres psql mojoportal | grep " mp_" | cut -d'|' -f2,4 | while read func sep rem; do echo "alter function $func($rem) owner to mojo;" | sudo sudo -u postgres psql mojoportal; done

This did not appear to change all permissions (although it didn't give me any errors), so I dropped functions and types altogether:

    echo "\df" | sudo sudo -u postgres psql mojoportal | grep " mp_" | cut -d'|' -f2,4 | while read func sep rem; do echo "drop function $func($rem);" | sudo sudo -u postgres psql mojoportal; done

    echo "\dT" | sudo sudo -u postgres psql mojoportal | grep " mp_" | cut -d'|' -f2 | while read type; do echo "drop type $type;" | sudo sudo -u postgres psql mojoportal; done

and re-created them with 3CreateStoredProcedures.sql, this time executed as user "mojo". This got rid of the permissions problem.

However, there's still something wrong. Setup/Default.aspx now gives me:

Probing system...
File system permissions ok.
pgsql database connection ok.
database permissions are sufficient to alter schema.
database initial schema already exists.
database core schema needs upgrade.
2 site(s) found.
Running script mojoportal-core - 2.2.1.5 - 00:00:00.3359250
Npgsql.NpgsqlException: operator does not exist: integer = character varying Severity: ERROR Code: 42883 Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. at Npgsql.NpgsqlState+ c__Iterator1.MoveNext () [0x00000] at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] - 00:00:00.9667060
Note: This page shows some information that is helpful during setup and upgrades but for security it would be best not to show any information when the system is up to date. You can disable setup and suppress all information on this page by setting DisableSetup=true in Web.config. When you need to upgrade, you can set this back to false. If you are logged in as Administrator setup will run and you will be able to see this page even if it is disabled in Web.config so you can easily leave it disabled and just login before upgrading. But if you are not logged in you will need to enable setup in Web.config.

Are there any custom operators for integer/varchar conversion in mojoportal or does this indicate that some functions/types still don't match the current database schema? Do you happen to have any hint on how I should proceed from here?

PS: I cannot easily change the skin on my in-production sites, so I intend to do that directly in the database. Would you expect a problem with this approach?

best regards,

Rene

Hi Joe,

I re-tried the upgrade without running 3CreateStoredProcedures.sql and get the same error:

Probing system...
File system permissions ok.
pgsql database connection ok.
database permissions are sufficient to alter schema.
database initial schema already exists.
database core schema needs upgrade.
2 site(s) found.
Running script mojoportal-core - 2.2.1.5 - 00:00:01.1931850
Npgsql.NpgsqlException: operator does not exist: integer = character varying Severity: ERROR Code: 42883 Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. at Npgsql.NpgsqlState+ c__Iterator1.MoveNext () [0x00000] at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] - 00:00:01.8532410
Note: This page shows some information that is helpful during setup and upgrades but for security it would be best not to show any information when the system is up to date. You can disable setup and suppress all information on this page by setting DisableSetup=true in Web.config. When you need to upgrade, you can set this back to false. If you are logged in as Administrator setup will run and you will be able to see this page even if it is disabled in Web.config so you can easily leave it disabled and just login before upgrading. But if you are not logged in you will need to enable setup in Web.config.

best regards,

Rene

Hi Joe,

A little web search pointed me towards the same error with other PostgreSQL clients as well - it seems version 8.3 is more picky and some database layers don't cope with that. I now downgraded to PostgreSQL 8.1 and manually fixed some more permissions. Now I get farther:

Probing system...
File system permissions ok.
pgsql database connection ok.
database permissions are sufficient to alter schema.
database initial schema already exists.
database core schema needs upgrade.
2 site(s) found.
Running script mojoportal-core - 2.2.1.6 - 00:00:00.5516070
Running script mojoportal-core - 2.2.1.7 - 00:00:00.6990970
Running script mojoportal-core - 2.2.1.8 - 00:00:00.8414640
Running script mojoportal-core - 2.2.1.9 - 00:00:02.5850800
Running script mojoportal-core - 2.2.2.0 - 00:00:04.3527810
Running script mojoportal-core - 2.2.2.1 - 00:00:05.6415290
Running script mojoportal-core - 2.2.2.2 - 00:00:06.3948860
Running script mojoportal-core - 2.2.2.3 - 00:00:07.3406880
Running script mojoportal-core - 2.2.2.4 - 00:00:09.2846800
Running script mojoportal-core - 2.2.2.5 - 00:00:10.3491630
Running script mojoportal-core - 2.2.2.6 - 00:00:11.4374300
Running script mojoportal-core - 2.2.2.7 - 00:00:13.2176470
Running script mojoportal-core - 2.2.2.8 - 00:00:15.1374400
Running script mojoportal-core - 2.2.2.9 - 00:00:16.3398260
Running script mojoportal-core - 2.2.3.0 - 00:00:17.7880560
Running script mojoportal-core - 2.2.3.1 - 00:00:20.8098910
Running script mojoportal-core - 2.2.3.2 - 00:00:22.6965220
Running script mojoportal-core - 2.2.3.3 - 00:00:24.8435370
Running script mojoportal-core - 2.2.3.4 - 00:00:28.2308670
Running script mojoportal-core - 2.2.3.5 - 00:00:32.4490400
Running script mojoportal-core - 2.2.3.6 - 00:00:33.7729650
Running script mojoportal-core - 2.2.3.7 - 00:00:35.4236180
Running script mojoportal-core - 2.2.3.8 - 00:00:37.0137080
Running script mojoportal-core - 2.2.3.9 - 00:00:38.1221670
Npgsql.NpgsqlException: multiple primary keys for table "mp_modules" are not allowed Severity: ERROR Code: 42P16 at Npgsql.NpgsqlState+c__Iterator1.MoveNext () [0x00000] at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject () [0x00000] - 00:00:38.2734760
Note: This page shows some information that is helpful during setup and upgrades but for security it would be best not to show any information when the system is up to date. You can disable setup and suppress all information on this page by setting DisableSetup=true in Web.config. When you need to upgrade, you can set this back to false. If you are logged in as Administrator setup will run and you will be able to see this page even if it is disabled in Web.config so you can easily leave it disabled and just login before upgrading. But if you are not logged in you will need to enable setup in Web.config.

Does this mean that mp_modules has not been updated correctly?

best regards,

Rene