pact-foundation/pact_broker

Error when running Sequel migrations for MySQL DB

Closed this issue · 12 comments

radubutnaru commented

Hi,

I'm trying to install pact_broker connecting to a MySQL DB. I'm connecting via the 'mysql2' adapter. DB is empty initially, no tables exist. When I first started pact_broker, I got the error below:

/usr/lib64/ruby/gems/2.1.0/gems/sequel-4.25.0/lib/sequel/adapters/mysql2.rb:78:in `query': Mysql2::Error: Cannot change column 'pact_version_content_sha': used in a foreign key constraint 'fk_pact_version_content' (Sequel::DatabaseError)
    from /usr/lib64/ruby/gems/2.1.0/gems/sequel-4.25.0/lib/sequel/adapters/mysql2.rb:78:in `block in _execute'
    from /usr/lib64/ruby/gems/2.1.0/gems/sequel-4.25.0/lib/sequel/database/logging.rb:37:in `log_yield'
    from /usr/lib64/ruby/gems/2.1.0/gems/sequel-4.25.0/lib/sequel/adapters/mysql2.rb:78:in `_execute'
    ...

Any idea how to get past this?

Thank you,
R.

bethesque commented

Use postgres? I do recommend it, MySQL won't support the functionality we want to add with full text search.

I tried to replicate the problem on my machine (Mac Yosemite) using mysql-5.6.26, a fresh database, ruby 2.2.2 and I could not do it. Here are my credentials. Have you set utf8 for the connection?

DATABASE_CREDENTIALS = {database: "bethtest_pact_broker", adapter: "mysql2", :encoding => 'utf8', username: 'bethtest', password: 'bethtest'}
radubutnaru commented

OK, switched to postgres, and everything seems to be working fine at the moment.

Related to the MySQL config, FYI, I was on CentOS 6.6, Ruby 2.1.6, MySQL client/devel libs 5.1.73, MySQL server 5.6.24 (on a separate host).

Thank you for your help!

bethesque commented

Thanks @yadavsaroj. For future reference, was it the LOCK TABLES solution or the FOREIGN_KEY_CHECKS?

yadavsaroj commented

The accepted answer worked for me. Essentially drop the foreign key and modify primary key definition. - ALTER TABLE favorite_food DROP FOREIGN KEY fk_fav_food_person_id, MODIFY person_id SMALLINT UNSIGNED;

sk1talets commented

The easiest thing to do to fix the installation I think would be:
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE pacts MODIFY pact_version_content_sha varchar(255) NOT NULL;
SET FOREIGN_KEY_CHECKS = 1;
UPDATE schema_info SET version = 19;

(make sure it's the migration number 19_* which failed)
And then accessing the Pact Broker page will apply the rest of the migrations.

sk1talets commented
  • The migrations have been tested on MySQL and PostgreSQL - your milage will vary on other databases.

I guess it's better to remove this from Pact Broker readme if you guys are not going to fix this.

bethesque commented

It seems to work on some MySQL instances, but not others. I'm not sure what the difference is. I originally developed it on MySQL. The problem with adding the SET FOREIGN_KEY_CHECKS in the migration is that it is MySQL specific, and won't run on postgresql. Though we could put a DB type check in there.

bethesque commented

Actually, it looks like I've already had to do a DB check in some other code.

    PactBroker::MigrationHelper.with_mysql do
      # Needed to make FK pact_version_content_sha match encoding of pact_version_content ID
      run("ALTER TABLE pacts CONVERT TO CHARACTER SET 'utf8';")
    end

@sk1talets - if you're interested in submitting a PR to make it work, I'd be happy to accept it. I'm on mat leave with 6 month old twins at the moment, so don't have time to work on it myself.

sk1talets commented

The problem with adding the SET FOREIGN_KEY_CHECKS in the migration is that it is MySQL specific, and won't run on postgresql.

yes, it's just a workaround/hack for installation with MySQL

if you're interested in submitting a PR to make it work, I'd be happy to accept it.

sure, will do that

bethesque commented

Good news, mysql users. I've finally managed to reproduce and fix this migration for mysql, thanks to travis now using the trusty image.

sk1talets commented

Sorry, we decided not to go the contract based testing approach for now and I couldn't find time to fix that.