Can't have 'id' automatically chosen when using CPK?

[Kevin Bullock]

Given a MySQL database with the following schema:

CREATE TABLE `posts` (

  `id` int(11) NOT NULL auto_increment,

  `version` int(11) NOT NULL default '1',

  `title` varchar(255) default NULL,

  `body` text,

  `created_at` datetime NOT NULL,

  `updated_at` datetime NOT NULL,

  PRIMARY KEY  (`id`,`version`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

and the following model:

class Post < ActiveRecord::Base

  set_primary_keys :id, :version

end

...I'd expect the 'id' key to be automatically set on save. And indeed, in the DB, it is:

mysql> select * from posts;

+----+---------+--------+------+---------------------+---------------------+

| id | version | title  | body | created_at          | updated_at          |

+----+---------+--------+------+---------------------+---------------------+

|  1 |       1 | foobar | NULL | 2012-12-09 03:47:35 | 2012-12-09 03:47:35 | 

+----+---------+--------+------+---------------------+---------------------+

But this isn't reflected in the object:

>> p=Post.create(:title => 'foobar')

=> #<Post id: nil, version: 1, title: "foobar", body: nil, created_at: "2012-12-09 03:47:35", updated_at: "2012-12-09 03:47:35">

irb(main):008:0> p.reload

ActiveRecord::RecordNotFound: Couldn't find Post with ID=,1 WHERE `posts`.`id` IS NULL AND `posts`.`version` = 1

[...]

I'd at least expect CPK to make the save fail if it doesn't have all parts of the key. Yes, the DB should also make that fail, but as you can see we have here a case where the DB save succeeds but CPK can't reload the record back.

Tested against Rails 3.0 (CPK 3.1.x), Rails 3.1 (CPK 4.1.x), and Rails 3.2 (CPK 5.0.x).

[brian...@madebymarket.com]

I am seeing the same issue in a Rails 4 app. Has anyone seen this before or have this a solution?

[Elise Wood]

Here's a hack I used in the one model I have that has this setup

table definition:

CREATE TABLE things (id INTEGER NOT NULL, version INTEGER NOT NULL DEFAULT 1);

CREATE SEQUENCE things_id_seq;

ALTER SEQUENCE things_id_seq OWNED BY things.id;

class Thing

  self.primary_keys = :id, :version

  

  def create_record(attribute_names = @attributes.keys)

    Thing.transaction do

      self[:id] = Thing.find_by_sql("SELECT nextval('things_id_seq') AS nextval").first.nextval

      

      attributes_values = arel_attributes_with_values_for_create(attribute_names)

      

      new_id = self.class.unscoped.insert attributes_values

      self.id ||= new_id if self.class.primary_key

      

      @new_record = false

      id

    end

  end

end