Error when running Sequel migrations for MySQL DB

[radubutnaru]

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]

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]

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!

[yadavsaroj]

I am having the same issue and this fixed it for me. http://stackoverflow.com/questions/13606469/cannot-change-column-used-in-a-foreign-key-constraint

[bethesque]

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

[yadavsaroj]

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]

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]

• 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]

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]

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]

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]

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]

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