• Forums
  • Bug Reports
  • Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

This is the place to report bugs and get support. When posting in this forum, please always provide as much detail as possible.

Please do not report problems with a custom build or custom code in this forum. If you are producing your own build from the source code and have problems or questions, ask in the developer forum, do not report it as a bug.

This is the place to report bugs and get support

When posting in this forum, please try to provide as many relevant details as possible. Particularly the following:

  • What operating system were you running when the bug appeared?
  • What database platform is your site using?
  • What version of mojoPortal are you running?
  • What version of .NET do you use?
  • What steps are necessary to reproduce the issue? Compare expected results vs actual results.
Please do not report problems with a custom build or custom code in this forum. If you are producing your own build from the source code and have problems or questions, ask in the developer forum.
This thread is closed to new posts. You must sign in to post in the forums.
12/28/2007 4:01:18 AM
Dean Brettle
Gravatar
Total Posts 148
View Posts

Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

I'm using mojoPortal 2.2.3.6 on Mono 1.2.6 and PostgreSQL 8.2.  I have an existing mojoPortal database that I've upgraded to 2.2.1.5 via the old manual process.  When I visit Setup/Default.aspx, I see:

...
16 Probing system...
20 File system permissions ok.
22 pgsql database connection ok.
39 database permissions are sufficient to alter schema.
31 database initial schema needs to be created.
40 Running script mojoportal-core - 2.2.3.4 - 00:00:00.2274100
391 Npgsql.NpgsqlException: relation "mp_blogcategories" already exists
...

Note that the initial schema does not need to be created.  Browsing the code online, I suspect that problem lies in the following function in mojoPortal.Data.pgsql/dbPortal.cs:

public static bool DatabaseHelper_TableExists(string tableName)
{
  NpgsqlConnection connection = new NpgsqlConnection(GetConnectionString());
  string[] restrictions = new string[4];
   restrictions[2] = tableName;
  connection.Open();
  DataTable table = connection.GetSchema("Tables", restrictions);
  connection.Close();
  if (table != null)
  {
    return (table.Rows.Count > 0);
  }

  return false;
}

I'm not sure if GetSchema() isn't implemented (or there is a bug in the implementation) in the version of npgsql you are referencing (there seem to be both an npgsql.dll and an Npgsql.dll in the bin folder).  Or perhaps accessing table.Rows.Count returns the wrong value because you already called connection.Close().

Anyway, can you reproduce this problem?

 

12/28/2007 8:20:31 AM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

Hey Dean,

It determines whether schema has been created by checking for the existence of the mp_sites table by passing its name into the method you reference.

As I recall .GetSchema works, I have been able to upgrade without errors, it returns true there for me. DataTable is a disconnected recordset so closing the connection is no problem.

I only have one npgsql.dll so I would remove the Npgsql.dll which is most likely an old one. That could be the problem if it wasn't implemented in the old version and its trying to use that.

Joe

12/28/2007 11:21:19 AM
Dean Brettle
Gravatar
Total Posts 148
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

Removing the Npgsql.dll didn't solve the problem.  Any other ideas?  Are you sure you've successfully upgraded a PostgreSQL database?

--Dean

 

12/28/2007 11:46:31 AM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

I upgrade my dev machine quite often.

One idea is to remove the script from the siteroot/Setup/applications/mojoportal-core/SchemaInstallScripts/pgsql folder, then it might move on to the upgrade scripts

Joe

12/28/2007 11:55:49 AM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

You could actually run all the upgrade scripts manually. They are located under siteroot/Setup/applications/mojoportal-core/SchemaUpgradeScripts/pgsql

After running them manually you would need to insert a row in the mp_schemaversion table with

insert into mp_schemaversion (applicationid, applicationname, major, minor, build, revision)
values( '077e4857-f583-488e-836e-34a4b04be855', 'mojoportal-core',2,2,3,6);

or whatever version you upgrade to.

Hope it helps,

Joe

12/28/2007 12:01:31 PM
Joe Audette
Gravatar
Total Posts 18439
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

Another thing you may be ruinning into. Back in the early days the instructions were to run the create tables and procs scripts as postgres user, so postgres user was the owner of the objects. Currently with a clean install the owner is your connection string user because that user creates all the objects.

I'm wondering since your db was originally created back in the day if it doesn't see the mp_sites table because the user in your connection string doesn't own it. You might try upgrading by using the postgres user in your connection string. Then it may see the mp_sites table and know it doesn't need to create the schema.

Joe

12/28/2007 12:24:52 PM
Dean Brettle
Gravatar
Total Posts 148
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

I bet thats it.  I'll see if I can change the ownership of all the tables...  I'd rather not run the upgrade as the postgres user because I don't want any new tables to be owned by that user.

 

12/28/2007 1:16:22 PM
Dean Brettle
Gravatar
Total Posts 148
View Posts

Re: Setup/Default.aspx thinks PostgreSQL schema needs to be created when it doesn't

Yep, that was the problem.  Here's how I worked around it:

Used the manual upgrade scripts to get the database to 2.2.1.5.

  1. Dumped the database with: pg_dump -c mojoportal -f dump2.2.1.5.sql
  2. Changed the owner used in the dump with:
    sed -e 's/OWNER TO [A-Za-z0-9]*/OWNER TO mojo/g' < dump2.2.1.5.sql >ownedbymojo.sql
  3. Became the postgres user with: su postgres
  4. As the postgres user ran: psql -d mojoportal < ownedbymojo.sql
  5. Visited Setup/Default.aspx to complete the upgrade.

I suspect that steps 1-4 do the same thing as the Alter*.sql scripts you emailed me, but I'm more confident that I haven't missed anything by doing it this way.

Thanks for the help!

You must sign in to post in the forums. This thread is closed to new posts.