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

Hi Joe,

Addendum: if that table is mp_moduledefinitions, then I can't find that null value:

mojoportal=# select featurename,sortorder from mp_moduledefinitions;
          featurename           | sortorder
--------------------------------+-----------
 Html Snippet Include           |       500
 Feature Modules (Admin)        |       502
 Roles (Admin)                  |       505
 Pages                          |       500
 Site Settings (Admin)          |       501
 File Manager (Admin)           |       580
 Friendly Url Manager           |       700
 Url Manager (Admin)            |       700
 HtmlContentFeatureName         |       300
 SiteStatisticsFeatureName      |       500
 BlogFeatureName                |       500
 ContactFormFeatureName         |       500
 EventCalendarFeatureName       |       500
 FeatureName                    |       500
 FolderGalleryFeatureName       |       500
 ForumsFeatureName              |       500
 GoogleMapFeatureName           |       500
 HtmlFragmentIncludeFeatureName |       500
 ImageGalleryFeatureName        |       500
 LinksFeatureName               |       500
 PollFeatureName                |       500
 SharedFilesFeatureName         |       500
 SurveyFeatureName              |       500
 XmlXslFeatureName              |       500
(24 rows)
 

The same is true for mp_moduledefinitionsettings, mp_sitesettingsexdef, and mp_htmlcontent (other tables in which I could find a "sortorder" column). Any hints?

best regards,

Rene

Hi Joe,

He, you're already up :)

To answer your questions:

1. mp_moduledefinitions seems to be ok:

mojoportal=# select * from mp_moduledefinitions where sortorder < 300 or guid is null;
 moduledefid | featurename | controlsrc | sortorder | isadmin | icon | defaultcachetime |guid | resourcefile
-------------+-------------+------------+-----------+---------+------+------------------+------+--------------
(0 rows)
 

2. I'm not sure what you mean by orphan rows in mp_modulesettings. Its current definition is

mojoportal=# \d mp_modulesettings
                                               Table "public.mp_modulesettings"
          Column           |          Type          |                                Modifiers
---------------------------+------------------------+--------------------------------------------------------------------------
 id                        | integer                | not null default nextval(('"mp_modulesettings_id_seq"'::text)::regclass)
 moduleid                  | integer                | not null
 settingname               | character varying(50)  | not null
 settingvalue              | character varying(255) | not null
 controltype               | character varying(50)  |
 regexvalidationexpression | text                   |
 moduleguid                | character varying(36)  |
 settingguid               | character varying(36)  |
 controlsrc                | character varying(255) |
 helpkey                   | character varying(255) |
 sortorder                 | integer                | not null default 100
Indexes:
    "pk_modulesettings" PRIMARY KEY, btree (id)
    "ifk_modulesettings_moduleid" btree (moduleid)
    "mp_modulesettings_ix_modul-0" btree (moduleid, settingname)
Foreign-key constraints:
    "fk_modulesettings_modules" FOREIGN KEY (moduleid) REFERENCES mp_modules(moduleid)
    "fk_modulesettings_modules_fk" FOREIGN KEY (moduleid) REFERENCES mp_modules(moduleid)

and it doesn't seem to contain any non-default entries in sortorder:

mojoportal=# select * from mp_modulesettings where sortorder != 100;
 id | moduleid | settingname | settingvalue | controltype | regexvalidationexpression | moduleguid | settingguid | controlsrc | helpkey | sortorder
----+----------+-------------+--------------+-------------+---------------------------+------------+-------------+------------+---------+-----------
(0 rows)
 

3. Concerning guid columns: There are many of them, and it would take quite some time to check them all. Would it be necessary to check all columns with "guid" in the name or only those that are named exactly "guid" (I assume the former are generally foreign keys and should therefore be filled no matter what)?

The root page of the site does indeed start, but with two major problems right now (without checking any of the details):

4. Skinning seems broken. Although I managed to (using Tab a lot ;) ) change from the default skin to one that would not overlay the whole page with my menu und sub-menu entries (I currently selected jsavard-subblue, which is still broken in terms of the menu layout, but at least restricted to the top part), only the menu seems to change with it. That is, the other elements (general page layout, header image, colors, etc.) seem to remain fixed to the styleshout-techmania style (or something else that looks like the mojoportal 2 default). Is there any database setting that needs to be overwritten to make it work again?

5. I have the same problem as on my eariler try of converting to mojoportal 2: All static HTML content blocks are shown in "encoded" (i.e. HTML) form. I assume the reason is that in my mojoportal 1 database, all HTML content is stored in an escaped form, and thus doesn't get interpreted. What is the best way to change the encoding to the way mojoportal 2 handles it now?

best regards,

Rene

Hi Joe,

I'm still impressed by your speed on following up forum posts...

DB stuff

mojoportal=# Delete from mp_modulesettings where moduleid NOT IN (SELECT moduleid from mp_modules);
DELETE 0
mojoportal=# Delete from mp_modulesettings where moduleid  IN (SELECT moduleid from mp_modules WHERE moduledefid NOT IN (SELECT moduledefid FROM mp_moduledefinitions));
DELETE 0
So, seemingly no orphans -good.

Having been programming for too long now, failing consistency checks definitely make me worry, though ;) I assume the

Ensuring all features are installed in server admin sites... - 00:00:09.9047010

Hi Joe,

Deleting these lines from mp_modulesettings did the trick, the Setup page now ran through successfully. Thanks for helping me debug it this far! Your excellent support is one of the reasons why I like mojoportal so much.

Concerning skinning, it seems that only part of the skin is applied: When I change the skin and then look at the page source, I see that the style files seem to be selected correctly, e.g.

link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/style.css' type='text/css' rel='stylesheet' />
<link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/styletext.css' type='text/css' rel='stylesheet' title='Normaltext' />
<link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/styleprinter.css' type='text/css' rel='stylesheet' media='print' />
<link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/stylemenu.css' type='text/css' rel='stylesheet' media='screen' />
<link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/styletreeview.css' type='text/css' rel='stylesheet' media='screen' />

or


<link href='http://localhost/Data/Sites/1/skins/extjs-viewport1/styletreeview.css' type='text/css' rel='stylesheet' media='screen' />

This also shows in the way the top menu is painted. Although still horribly broken, I see that it changes depending on the selected skin. On the other hand, the whole page "master" layout does not change, as evident e.g. in the footer part:

        <a title="Visit the web site of the designer" href="http://www.styleshout.com/">Design by styleshout</a>

The weird thing is that with the same installation on fht filesystem, skins work with the empty default database, just not with the converted one. So it's (IMHO) unlikely to be a filesystem (permissions, links, whatever) reason.

best regards,

Rene

Hi Joe,

<add key="CombineCSS" value="true"/>

is set in Web.conf and I can't see anything otherwise. I have not modified Web.config besides adding my database connection details. This seems to be acknowledged by the fact that I get a single, long line CSS by the csshandler.aspx link.

At the moment, I can reproducable switch between the two cases:

- Using the empty default database setting (with just 2 pages added and the style changed to styleshout-refresh) gives me the working site with the following header:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="ctl00_Head1"><title>
	Rene Mayrhofer - Home
</title>
<link rel='stylesheet' type='text/css' href='http://ajax.googleapis.com/ajax/libs/yui/2.6.0/build/tabview/assets/skins/sam/tabview.css' />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/csshandler.ashx?skin=styleshout-refresh&amp;config=style.config&amp;add=' type='text/css' rel='stylesheet' />
<!--[if lt IE 7]>
<link rel="stylesheet" href="http://localhost/Data/Sites/1/skins/styleshout-refresh/IESpecific.css" type="text/css" id="IEMenuCSS" />
<![endif]-->
<!--[if gt IE 6]>
<link rel="stylesheet" href="http://localhost/Data/Sites/1/skins/styleshout-refresh/IE7Specific.css" type="text/css" id="IE7MenuCSS" />
<![endif]-->

- Using my converted database (now with the same style set) gives me the broken layout with this header:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="ctl00_Head1"><title>
	Rene Mayrhofer - Einstellungen Website
</title><link rel="stylesheet" type="text/css" href="../App_DesignTimeStyle/style.css" /><link rel="stylesheet" type="text/css" href="../App_DesignTimeStyle/blogmodule.css" /><link rel="stylesheet" type="text/css" href="../App_DesignTimeStyle/aspcalendar.css" /><link rel="stylesheet" type="text/css" href="../App_DesignTimeStyle/stylemenu.css" /><link rel="stylesheet" type="text/css" href="../App_DesignTimeStyle/styletreeview.css" />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/style.css' type='text/css' rel='stylesheet' />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/styletext.css' type='text/css' rel='stylesheet' title='Normaltext' />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/styleprinter.css' type='text/css' rel='stylesheet' media='print' />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/stylemenu.css' type='text/css' rel='stylesheet' media='screen' />
<link href='http://localhost/Data/Sites/1/skins/styleshout-refresh/styletreeview.css' type='text/css' rel='stylesheet' media='screen' />
<!--[if lt IE 7]>
<link rel="stylesheet" href="http://localhost/Data/Sites/1/skins/styleshout-refresh/IESpecific.css" type="text/css" id="IEMenuCSS" />
<![endif]-->
<!--[if gt IE 6]>
<link rel="stylesheet" href="http://localhost/Data/Sites/1/skins/styleshout-refresh/IE7Specific.css" type="text/css" id="IE7MenuCSS" />
<![endif]-->

To switch between the two cases, I only stop apache2 (with mod_mono), drop the mojoportal database, re-create it, and populate it with either of the dumps, and finally start apache2 again. Ctrl-F5 loading in Firefox triggers the switch. As nothing besides the database changes, I'm pretty sure the mojoportal installation is fine otherwise.

Which DB settings are important for the skinning? Is there anything I could check?

best regards,
Rene

Skin name appears to be correct, but I just noticed that the guid in mp_sitesettingsex is all zeros. Could that have something to do with it?

Rene