Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MySQL bigint auto_increment primary_key is not dumped correct in db/schema.rb #19232

Closed
ChristianStancu opened this issue Mar 6, 2015 · 14 comments

Comments

Projects
None yet
8 participants
@ChristianStancu
Copy link

commented Mar 6, 2015

Hi,

I have tried to create a table with an bigint as primary_key and auto_increment for a MySQL database.

My Migration was

class CreateTests < ActiveRecord::Migration
  def change
    create_table :tests, id: false do |t|
      t.integer :id, limit: 8, auto_increment: true, primary_key: true
      t.string :name
    end
  end
end

Auto_increment and primary_key have been ignored.

CREATE TABLE `tests` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The db/schema.rb looked like

create_table "tests", id: false, force: :cascade do |t|
  t.integer "id",   limit: 8
  t.string  "name", limit: 255
end

A second try

class CreateTests < ActiveRecord::Migration
  def change
    create_table :tests do |t|
      t.string :name
    end

   change_column :tests, :id, :integer, limit: 8, auto_increment: true, primary_key: true
  end
end

The MySQL-Table was build correct

CREATE TABLE `tests` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

But the db/schema.rb looked like

create_table "tests", force: :cascade do |t|
  t.string "name", limit: 255
end

And after a rake db:reset resets the column was changed back to integer instead of bigint.

Best
Chris

@senny senny added the activerecord label Mar 17, 2015

@wallerjake

This comment has been minimized.

Copy link
Contributor

commented Mar 22, 2015

@LtdPayne - I believe this was fixed in #17631. It seems to be working good for me off of the latest version of master too.

@nethad

This comment has been minimized.

Copy link

commented Mar 23, 2015

@LtdPayne @wallerjake Yes, we tested it here with the latest master and it is working:

Running the migration:

def change
  create_table :tests, id: false do |t|
    t.integer :id, limit: 8, auto_increment: true, primary_key: true
    t.string :name
  end
end

results in the schema:

create_table "tests", id: :bigint, force: :cascade do |t|
  t.string "name", limit: 255
end

which is equivalent. You could probably move the "id: :bigint" part to the migration already, although I didn't try that.

/cc @nerdinand @senny

@senny

This comment has been minimized.

Copy link
Member

commented Mar 23, 2015

@nethad I just ran into a similar bug with a string primary key on 4-2-stable. I'm keeping this issue open to remind me that we need to backport the fix from master to get 4.2.x working as well.

@senny senny added this to the 4.2.2 milestone Mar 23, 2015

@senny

This comment has been minimized.

Copy link
Member

commented Mar 23, 2015

@nethad thanks for investigating 💛

@ChristianStancu

This comment has been minimized.

Copy link
Author

commented Mar 23, 2015

Thanks a lot

@andywenk

This comment has been minimized.

Copy link

commented Apr 7, 2015

Hey,

my fellow coworker @LtdPayne asked me to test this again. I am using branch: '4-2-2-stable' in the Gemfile but it does not seem, that this is already included. Am I right? When can we expect taht this is included.

Thanks a lot

Andy

@senny

This comment has been minimized.

Copy link
Member

commented Apr 7, 2015

@andywenk no, it's currently only on master. This has not been backported yet so there is a chance that it is included in the next release. You mention a branch called 4-2-2-stable but I don't think we branched 4-2-2 out already...

@andywenk

This comment has been minimized.

Copy link

commented Apr 7, 2015

iiiks - sry - I ment https://github.com/rails/rails/tree/4-2-stable and that's for sure not 4-2-2 ... Thanks for the info @senny

@senny

This comment has been minimized.

Copy link
Member

commented Apr 24, 2015

I dug a bit deeper and there have been a lot of changes on master, which make it hard to backport the actual fix without any additional features. Up to the current state of master the migration dsl did not really support creating tables with non-integer primary keys.

I suggest using db/structure.sql for now and skip the backports. Everything should start working with Rails 5.

@rafaelfranca what are your thoughts about backporting #18220?

@kamipo

This comment has been minimized.

Copy link
Member

commented May 19, 2015

@LtdPayne @andywenk I hope that this helps you: https://github.com/kamipo/activerecord-mysql-awesome

@andywenk

This comment has been minimized.

Copy link

commented May 19, 2015

@kamipo awesome - thanks a lot ;-)

@LtdPayne could you please checkout activerecord-mysql-awesome mentioned by @kamipo

@rafaelfranca

This comment has been minimized.

Copy link
Member

commented May 19, 2015

@rafaelfranca what are your thoughts about backporting #18220?

I'd not backport it. There is a lot of changes that may break something.

I suggest using db/structure.sql for now and skip the backports. Everything should start working with Rails 5.

Yes. I think this is the best you have with Rails 4. Or use @kamipo's gem. He is the author of the original patch and a bunch of other improvements on the mysql adapter.

@ChristianStancu

This comment has been minimized.

Copy link
Author

commented May 20, 2015

@kamipo Yes, that's a great help, thanks a lot :)

@AmitPatel-BoTreeConsulting

This comment has been minimized.

Copy link

commented May 23, 2016

With Rails(4.2.5) and mysql2(0.4.3), I am not able to set bigint(20) as primary key using following migration without using activerecord-mysql-awesome gem assuming it would have been fixed in mentioned version of rails and mysql2 gems

class CreateContentUnits < ActiveRecord::Migration
  def change
    create_table :content_units, id: false do |t|
      t.integer :guoid, limit: 8, auto_increment: true, primary_key: true
      t.boolean :active, default: false
    end
  end
end

The generated schema entry is

  create_table "content_units", id: false, force: :cascade do |t|
    t.integer "guoid",  limit: 8
    t.boolean "active",           default: false
  end

And generated table definition is

MariaDB [magnificent_development_cr]> describe content_units;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| guoid  | bigint(20) | YES  |     | NULL    |       |
| active | tinyint(1) | YES  |     | 0       |       |
+--------+------------+------+-----+---------+-------+

Though I could fix the issue using activerecord-mysql-awesome gem but I am bit hesitant to use that because I don't have any idea what implications would have with future versions of rails and mysql2 gems.

Also it adds options: "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" to all the eixsting create_table script in schema.rb

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.