Worker falls over on locked database

[fringley]

I'm frequently getting a locked database:

[osaris]

Same here. Temporary fix is to use mysql rather than sqlite.

[joelcogen]

I don't think this can be fixed with sqlite, so mysql will probably become default in the future. pg is fine too.

[fringley]

Is there an easy way to migrate over now?

Regards, Chris Tingley Technical Director, conjure.co.uk

Sent from my iPhone, apologies for brevity and typos

On 11 Dec 2012, at 08:03, Joel Cogen notifications@github.com wrote:

I don't think this can be fixed with sqlite, so mysql will probably become default in the future. pg is fine too.

— Reply to this email directly or view it on GitHubhttps://github.com/belighted/bennett/issues/48#issuecomment-11234465.

[osaris]

Add

gem 'mysql2' 

after gem 'sqlite3' in the Gemfile (you can comment it)

and run

sudo bundle install --without=test development

then update your config/database.yml to looks like this :

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: bennett_production
  pool: 5
  username: root
  password:
  host: localhost

then create your bennett_production MySQL database and run

bundle exec rake db:migrate RAILS_ENV=production

all done !

[fringley]

Cool, thanks! Will that migrate over the data from my current db installation as well?

Regards, Chris Tingley Technical Director, conjure.co.uk

Sent from my iPhone, apologies for brevity and typos

On 11 Dec 2012, at 08:39, "Raphaël emourgeon" notifications@github.com wrote:

Add

gem 'mysql2'

after gem 'sqlite3' in the Gemfile (you can comment it)

and run

sudo bundle install --without=test development

then update your config/database.yml to looks like this :

production: adapter: mysql2 encoding: utf8 reconnect: false database: bennett_production pool: 5 username: root password: host: localhost

then create your bennett_production MySQL database and run

bundle exec rake db:migrate RAILS_ENV=production

all done !

— Reply to this email directly or view it on GitHubhttps://github.com/belighted/bennett/issues/48#issuecomment-11235238.

[osaris]

No, this will create a new database. But you can export SQLite database data in a dump (SQL format) and import it into MySQL.

[fringley]

Sounds like a plan! Ill give that a go later and let you know how I get on :) thanks very much for your help!

Regards, Chris Tingley Technical Director, conjure.co.uk

Sent from my iPhone, apologies for brevity and typos

On 11 Dec 2012, at 08:45, "Raphaël emourgeon" notifications@github.com wrote:

No, this will create a new database. But you can export SQLite database data in a dump (SQL format) and import it into MySQL.

— Reply to this email directly or view it on GitHubhttps://github.com/belighted/bennett/issues/48#issuecomment-11235379.

[fringley]

So, we have migrated our database. We had issues with MySQL, but seeing as our CI box is running PG, we decided to use that instead. We came across a few issues which i'll cover below in case anyone else wants to do the same. The good news is that it seems to have fixed the issue - we think!

• As mentioned above, update your database.yml with PG connection details
• Add gem 'pg' to your Gemfile
• Run bundle install
• Dump your current sqlite3 production database:
  • sqlite3 db/production.sqlite3 .dump > dump.sql
• Edit the SQL dump and do the following:
  • Remove the first PRAGMA line
  • Remove all CREATE TABLE lines
  • Remove 'sqlite_sequence' table related lines at the bottom of the file
  • Remove the unique index lines at the bottom of the file
• Change the "create_builds" bennett migration:
  • Change t.string :commit_message to t.text :commit_message
• Setup your PG database:
  • bundle exec rake db:drop db:create db:migrate RAILS_ENV=production
• Import your sql dump:
  • psql bennett_production < db/sqlite_data.sql
• Fix the PG sequences as the import doesn't work properly with the autoincrementing sequences:
  • psql bennett_production

    SELECT pg_catalog.setval(pg_get_serial_sequence('builds', 'id'), (SELECT MAX(id) FROM builds));
    SELECT pg_catalog.setval(pg_get_serial_sequence('commands', 'id'), (SELECT MAX(id) FROM commands));
    SELECT pg_catalog.setval(pg_get_serial_sequence('invitations', 'id'), (SELECT MAX(id) FROM invitations));
    SELECT pg_catalog.setval(pg_get_serial_sequence('projects', 'id'), (SELECT MAX(id) FROM projects));
    SELECT pg_catalog.setval(pg_get_serial_sequence('results', 'id'), (SELECT MAX(id) FROM results));
    SELECT pg_catalog.setval(pg_get_serial_sequence('rights', 'id'), (SELECT MAX(id) FROM rights));
    SELECT pg_catalog.setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users));
    

• Alter the lib/tasks/resque.rake rake file, the resque:setup task:

  task "resque:setup" => :environment do
  Resque.before_fork = Proc.new { ActiveRecord::Base.establish_connection }
  end
  

[tobypinder]

I can vouch for the fact that the last bullet point of @fringley's post here is EXTREMELY important (I am using PG). Will it harm other DBs if this got merged in? It seems like this would fix the locked database issue, even on SQLite.

[joelcogen]

Migrating data from MySQL/Sqlite to pg can be extremely painful. If you don't have many projects, it might be easier to just create them again in the new environment. All you will really loose is history, but I don't think it is that important.

[mtrisoline]

Something I did to help get around the database was just force the application to connect to a remote PG server for tests. I noticed it also helped with test performance when running tests for multiple projects concurrently.