SoundSoftware Code Site

h1. Setup on new server tasks

# -Install Apache, Postgres, Varnish, etc-

# -Install "Ruby EE":http://www.rubyenterpriseedition.com/ and "Phusion Passenger":http://modrails.org/-

# -Install (via gem) rake, rails version 2.3.5, pg, rmagick, ruby-openid-

# -Clone current soundsoftware code-

# -Set up postgresql user and initialise database-

# -Redmine initialisation-

# -Redmine permissions and ownership-

# -Basic Apache config for site on :81-

# -Varnish config for :80->:81-

# -PHP5 via fcgi-

# -Basic Drupal install-

# -Initial database load for Redmine-

# -Initial database load for Drupal-

# -Pull across initial hg repos-

# -Configure SSL server-

# -Pull across remaining bits of redmine server that are not in hg (fonts, files, etc)-

# Install -hgwebdir-, -reposman-, -soundsoftware Apache auth- (depend on SSL, db, web service key in db etc)

# -Configure- and test the above (cannot test auth until LDAP is working)

# -Cron jobs-

# -Pull across Drupal theme etc-

# -Make sure we can connect to LDAP!-

# Backup regime

# -Ensure machine can be routed to!-

in the future: put old host in admin mode, re-point dns, final hg / database / redmine files update

further in the future: transplant across test servers, Survey service

even further in the future: switch off kakapo's https service, remove old sites from there

h2. Passenger compatibility

The site doesn't work properly with Passenger 3 -- every page is rendered without stylesheet, and the logs fill up with 

<pre>

ActionController::RoutingError (No route matches "/dispatch.cgi" with {:method=>:get}):

  passenger (3.0.0) lib/phusion_passenger/rack/request_handler.rb:96:in `process_request'

  passenger (3.0.0) lib/phusion_passenger/abstract_request_handler.rb:513:in `accept_and_process_next_request'

...

</pre>

(what's it doing in rack/ anyway? This isn't a Rack app)

Reverting to Passenger 2.2 fixes it.  Just noting this here as a reminder to report / investigate...

h2. After import

We have to clear the repositories cache (if we have imported these tables).

<pre>

delete from changesets;

delete from changes;

</pre>

(Do we have to rebuild indexes?  With MySQL it was necessary to run @optimize table@ on these afterwards, followed by @flush tables@.  Probably no harm in at least running @REINDEX TABLE@ on each table)

h2. MySQL to Postgres migration notes

Just to make things more fun, we're migrating the existing databases from MySQL to pgsql.

For Redmine, we don't attempt to dump and reload the schema -- more sensible to use Redmine to set up the schema (in case it has any database-specific set up) and then massage the data as we load it.  We just dump and reload the row data.  That way we only have to fix problems once.

"mysql2pgsql":http://pgfoundry.org/projects/mysql2pgsql/ does a reasonable job of converting the data, as far as it goes.  Some problems remain:

<pre>$ grep ERROR log/dbload.log |sort|uniq

ERROR:  column "admin" is of type boolean but expression is of type integer

ERROR:  column "assignable" is of type boolean but expression is of type integer

ERROR:  column "hide_mail" is of type boolean but expression is of type integer

ERROR:  column "is_closed" is of type boolean but expression is of type integer

ERROR:  column "is_default" is of type boolean but expression is of type integer

ERROR:  column "is_in_chlog" is of type boolean but expression is of type integer

ERROR:  column "is_public" is of type boolean but expression is of type integer

ERROR:  column "mail_notification" is of type boolean but expression is of type integer

ERROR:  column "onthefly_register" is of type boolean but expression is of type integer

ERROR:  column "protected" is of type boolean but expression is of type integer

ERROR:  duplicate key value violates unique constraint "unique_schema_migrations"

ERROR:  INSERT has more expressions than target columns

ERROR:  invalid input syntax for type bytea

$

</pre>

The boolean ones are understandable -- mysqldump writes them out as ones and zeros and you can't tell they were intended to be boolean without the schema.  We can fix them readily enough in a script once we identify which table they come from.

The duplicate key values we don't care about -- in fact we're glad they're rejected, because they come from the Redmine db:migrate which we've already run anyway.

"INSERT has more expressions"... that's a strange one.  Is our schema out of date?

This is from the repositories table.  Columns:

<pre>

 id | project_id | url | login | password | root_url | type 

</pre>

Values being inserted (example):

<pre>

INSERT INTO "repositories" VALUES (1,2,E'/var/hg/svcore',E'',E'',E'/var/hg/svcore',E'Mercurial',E'--- {}\n\n');

</pre>

So we are indeed missing something -- the @checkout_settings@ column.  We forgot to run the plugins migration (@rake db:migrate:plugins@).

Fixing that, we have a new one instead:

<pre>

ERROR:  duplicate key value violates unique constraint "settings_pkey"

</pre>

The problem is that the blimmin' redmine-checkout plugin has inserted its own settings row, which appears only later in the imported dump.  We need to @delete from settings@ before we can load.

Later,

<pre>

ERROR:  duplicate key value violates unique constraint "roles_pkey"

ERROR:  duplicate key value violates unique constraint "users_pkey"

</pre>

The roles one doesn't really matter (we're inserting the same stuff anyway) but the users one does, as it has all of admin's details.  So we have to do this after the migrate steps but before the data load:

<pre>

delete from settings

delete from roles

delete from users

</pre>

h2. Problematic Boolean columns

We'll just script a quick hack to convert these based on counting the commas for column numbers.  Hence we need to know which table each belongs to, which column it is (counting from the left), and whether any of the fields to the left can contain commas themselves.

* @admin@: table @users@ col 8 (counting from 1); no

* @assignable@: table @roles@ col 4; no

* @hide_mail@: table @user_preferences@ col 4; no

* @is_closed@: table @issue_statuses@ col 3; no

* @is_default@: table @issue_statuses@ col 4; table @enumerations@ col 4; both no

* @is_in_chlog@: table @trackers@ col 3; no

* @is_in_roadmap@: table @trackers@ col 5; no

* @is_public@: table @projects@ col 5; yes. This is likely to be first occurrence of @,1,@ or @,0,@ however

* @mail_notification@: table @members@ col 5; no (there is also a column of this name in @users@ but it is not boolean)

* @onthefly_register@: table @auth_sources@ col 13; yes. This is likely to be first occurrence of @,1,@ or @,0,@ however and we only have one row in @auth_sources@ at the moment anyway

* @tls@: table @auth_sources@ col 14; yes. Last column, so look for @,0)@ or @,1)@ I guess.  Yummy

* @protected@: table @wiki_pages@ col 5; no

h2. Errors from Drupal database import

All of the foregoing is to do with the Redmine database.  Here's what we get for Drupal.

Our first load failed because of something to do with the @cache@ table -- but we don't want to load the cache anyway (do we?).  After eliminating the cache from the dump and trying again,

<pre>ERROR:  duplicate key value violates unique constraint "actions_pkey"

ERROR:  duplicate key value violates unique constraint "authmap_pkey"

ERROR:  duplicate key value violates unique constraint "blocks_pkey"

ERROR:  duplicate key value violates unique constraint "blocks_roles_pkey"

ERROR:  duplicate key value violates unique constraint "boxes_pkey"

ERROR:  duplicate key value violates unique constraint "cache_menu_pkey"

ERROR:  duplicate key value violates unique constraint "filter_formats_pkey"

ERROR:  duplicate key value violates unique constraint "filters_pkey"

ERROR:  duplicate key value violates unique constraint "menu_custom_pkey"

ERROR:  duplicate key value violates unique constraint "menu_links_pkey"

ERROR:  duplicate key value violates unique constraint "menu_router_pkey"

ERROR:  duplicate key value violates unique constraint "node_access_pkey"

ERROR:  duplicate key value violates unique constraint "node_type_pkey"

ERROR:  duplicate key value violates unique constraint "role_pkey"

ERROR:  duplicate key value violates unique constraint "sessions_pkey"

ERROR:  duplicate key value violates unique constraint "system_pkey"

ERROR:  duplicate key value violates unique constraint "users_pkey"

ERROR:  duplicate key value violates unique constraint "variable_pkey"

ERROR:  relation "cache_views" does not exist

ERROR:  relation "image_attach" does not exist

ERROR:  relation "image" does not exist

ERROR:  relation "upload" does not exist

ERROR:  relation "views_display" does not exist

ERROR:  relation "views_view" does not exist

</pre>

The first few were probably caused by the previous partial load with cache (I didn't clear out in between).

The rest by missing modules?