[pgsql, installer, schema] statusnet tries to speak mysql when it should speak postgresql

[pgsql, installer, schema] statusnet tries to speak mysql when it should speak postgresql

Issue ID:2284
Issue Category:bug
Component:uncategorized
Priority:normal
Status:active
Assigned:brion
Version:0.9
Milestone:0.9

On Debian Lenny, I used On Debian Lenny, I used the version 0.9.1 of statusnet (http://status.net/statusnet-0.9.1.tar.gz). I copied it in my /var/www directory, installed the php plugins that were missing, then launched install.php.

At this point it appeared to me that I needed to create a user on my postgresql DBS.
{{{
create user "statusnetadmin" with createuser createrole createdb encrypted
password 'statusnetadmin';
}}}
Then the database itself :
{{{
create database statusnet with encoding='UTF8' owner=statusnetadmin;
}}}

I filled the remaining entries of install.php, then applied. At that point I got a first error :
{{{
[...]
* Adding foreign service data to database...
* Writing config file...

Fatal error: Uncaught exception 'Exception' with message 'DB Error: unknown error'
in /var/www/statusnet/lib/pgsqlschema.php:191
Stack trace:
#0 /var/www/statusnet/lib/pgsqlschema.php(387):
PgsqlSchema->createTable('ostatus_source', Array)
#1 /var/www/statusnet/plugins/OStatus/OStatusPlugin.php(411):
PgsqlSchema->ensureTable('ostatus_source', Array)
#2 [internal function]: OStatusPlugin->onCheckSchema()
#3 /var/www/statusnet/lib/event.php(105): call_user_func_array(Array, Array)
#4 /var/www/statusnet/lib/statusnet.php(186): Event::handle('CheckSchema')
#5 /var/www/statusnet/lib/statusnet.php(102): StatusNet::initPlugins()
#6 /var/www/statusnet/lib/common.php(131): StatusNet::init(NULL, NULL, NULL)
#7 /var/www/statusnet/install.php(882): require_once('/var/www/status...')
#8 /var/www/statusnet/install.php(680): registerInitialUser('admini', 'admin',
'', 'true')
#9 /var/www/statusnet/install.php(248): handlePost()
#10 /var/www/statusnet/install.php(948): main()
#11 {main} thrown in /var/www/statusnet/lib/pgsqlschema.php on line 191
}}}
By adding some debug I could see that it was this SQL that was failing :
{{{
CREATE TABLE ostatus_profile ( uri varchar(255) not null , profile_id integer null ,
group_id integer null , feeduri varchar(255) null , salmonuri text null , avatar text
null , created timestamp not null , modified timestamp not null , primary key (uri));
CREATE index ostatus_profile_profile_id_idx ON ostatus_profile (profile_id);
CREATE index ostatus_profile_group_id_idx ON ostatus_profile (group_id);
CREATE index ostatus_profile_feeduri_idx ON ostatus_profile (feeduri);
CREATE TABLE ostatus_source ( notice_id integer not null , profile_uri varchar(255)
not null , method enum('push','salmon') not null , primary key (notice_id));
}}}

Then, when I go back to my localhost/statusnet webpage, almost the same error :
{{{
Fatal error:
Uncaught exception 'Exception' with message 'DB Error: unknown error' in
/var/www/statusnet/lib/pgsqlschema.php:442
Stack trace:
#0 /var/www/statusnet/plugins/OStatus/OStatusPlugin.php(410):
PgsqlSchema->ensureTable('ostatus_profile', Array)
#1 [internal function]: OStatusPlugin->onCheckSchema()
#2 /var/www/statusnet/lib/event.php(105): call_user_func_array(Array, Array)
#3 /var/www/statusnet/lib/statusnet.php(186): Event::handle('CheckSchema')
#4 /var/www/statusnet/lib/statusnet.php(102): StatusNet::initPlugins()
#5 /var/www/statusnet/lib/common.php(131): StatusNet::init(NULL, NULL, NULL)
#6 /var/www/statusnet/index.php(40): require_once('/var/www/status...')
#7 {main} thrown in /var/www/statusnet/lib/pgsqlschema.php on line 441
}}}
when trying to exectute this query :
{{{
ALTER TABLE ostatus_profile MODIFY COLUMN uri varchar(255) not null ,
MODIFY COLUMN profile_id integer null , MODIFY COLUMN group_id integer null ,
MODIFY COLUMN feeduri varchar(255) null , MODIFY COLUMN created timestamp
not null , MODIFY COLUMN modified timestamp not null
}}}

According to this [http://www.postgresql.org/docs/8.1/static/sql-altertable.html documentation], ALTER TABLE [...] MODIFY is not a valid postgresql statement.

Finally, here is my configuration file (seems to be incomplete) :
{{{
<?php
if (!defined('STATUSNET') && !defined('LACONICA')) { exit(1); }
$config['site']['name'] = 'ewoooh';
$config['site']['server'] = 'localhost';
$config['site']['path'] = 'statusnet';
$config['db']['database'] = 'pgsql://statusnetadmin:statusnetadmin@localhost/statusnet';
$config['db']['quote_identifiers'] = true;
$config['db']['type'] = 'pgsql';
}}}

Legacy Data

This issue was migrated from another tracking system. The legacy data at time of import is provided below as a reference.

Ticket ID: 
2284
Reported by: 
mike_perdide
Owner: 
brion
Status: 
assigned
Type: 
bug
Component: 
uncategorized
Priority: 
3
Version: 
0.9
Milestone: 
0.9

Updates

#1

Sounds like the PG version of the schema setup classes needs updating (used so far for building and updating tables for plugins, in future will be used for core tables as well)

#2

That still happens with status.net 0.9.2

As I can't attach files, some patches on pastebin.com

http://pastebin.com/PRfXEVGk
Fix for auto_increment. It assumes that auto_increments are always integers, so bigserial can be used.
An alternative fix could be to remove the auto_increment field, and handle a ColumnDef type of "auto_increment" in both mysql (int with auto_increment modifier) and pgsql (bigserial type)

http://pastebin.com/Rbj4PmGb
Fix for creating indices. They are added separately, not inside a table definition.

Other issues are:
* "ENUM(...)" ${type}s - on pgsql, those are created explicitely and then used with their name as type.
* The use of "on update" in RSSCloudPlugin (_totally_ different in pgsql, using triggers or rules). This is used for "modified" fields carrying the latest change date of a row. Maybe this should be done in the SQL framework, automatically updating a configurable field on INSERT and UPDATE?
* "ALTER TABLE" works with a very different syntax, too.

There might be more, at this point I gave up for now.

#3

http://pastebin.com/0MYhE30q updates statusnet_pg.sql to match statusnet.sql.
With hacks for the other issues listed in the previous comment, I manage to get through the install routine. Normal operation still doesn't work.

#4

the statusnet_pg.sql change isn't quite correct, as bigserial uses a different naming scheme for sequences than what some extlib code expects.

With http://www.coresystems.de/~patrick/sn092.diff (which includes all the above patches, with some updates) I managed to run install.php, log in and post a message.

No further testing, and there's a known issue related to sequence naming in there (look for "FIXME" in the patch)
Also, a lot of the changes are ugly hacks that shouldn't ever see the light of a release, but hopefully point out what needs to be changed.

#5

i think the varchar –> enum stuff in lib/columndef.php is the wrong approach. There's code in the 0.9.x branch that translates into a text with a CHECK already. This means we do enforce the data integrity in postgres.

I have re-rolled this patch and committed and pushed the changes the affect only postgres (credit in git log to Patrick Georgi).

Thanks for your contribution :)

#6

Thank you. There are more patches at http://mublog.georgi-clan.de/patches/ (against 0.9.2) that I need to get SN to run on pgsql.

Some are really ugly hacks, others should be quite usable.

Usable are (in my opinion):
000- and 002- (schema update)
004- (use pgsql code for pgsql)
006- (fix index creation)
012- (parse database schema to determine updates)
013- (fix storing OpenID tokens)

Others might still give ideas on what's wrong and possible solutions.

#7

Could you have a look at using git? I'll make my job much easier to review patches from you if there are going to be many.

For this project we're using http://gitorious.org/statusnet, which allows you to have your own repo, and then make merge requests.

#8

Pushed a fix that enables the installer to talk pgsql, gets it past the server_encoding check, and adds a full-text search index method:

https://gitorious.org/~dbs/statusnet/dbs-mainline/commit/ce2be41df4cab20...

Combined with the other fixes that I've pushed in the last 6 hours at https://gitorious.org/~dbs/statusnet/dbs-mainline , this gets the installer much closer to working.

#9

Also pushed commit https://gitorious.org/~dbs/statusnet/dbs-mainline/commit/bee7e1bdc4613cf... to correct a problem with the full-text search index definition.

Login or Register to modify this issue, or to receive updates by email.

You can also subscribe to the RSS feed for updates to this issue.