timestamps not updated when assigning object.ids=ids

[acesuares]

Maybe this is expected behaviour, but when assigning in this way, timestamps do not get updated or set

object.user_ids=[1,2]

which generates this

(0.0ms)  BEGIN
(0.2ms)  DELETE FROM `roles_users` WHERE `roles_users`.`role_id` = 2 AND `roles_users`.`user_id` IN (3)
(0.1ms)  INSERT INTO `roles_users` (`role_id`, `user_id`) VALUES (2, 1)
(0.1ms)  INSERT INTO `roles_users` (`role_id`, `user_id`) VALUES (2, 2)
(145.4ms)  COMMIT

Here is the migration for roles_users:

# encoding: utf-8
class CreateRolesUsers < ActiveRecord::Migration

  def self.up
    create_table :roles_users, :id => false do |t|
      t.integer :role_id
      t.integer :user_id
      t.timestamps
    end
  end

  def self.down
    drop_table :roles_users
  end

end

Here is how mysql creates the table:

mysql> show create table roles_users;
| Table       | Create Table
| roles_users | CREATE TABLE `roles_users` (
  `role_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

mysql> describe roles_users;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| role_id    | int(11)  | YES  |     | NULL    |       |
| user_id    | int(11)  | YES  |     | NULL    |       |
| created_at | datetime | NO   |     | NULL    |       |
| updated_at | datetime | NO   |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here is how sqlite creates the table:

sqlite> .schema roles_users
CREATE TABLE "roles_users" ("role_id" integer, "user_id" integer, "created_at" datetime NOT NULL, "updated_at" datetime NOT NULL);

Here is how mysql fills the table:

mysql> select * from roles_users;
+---------+---------+---------------------+---------------------+
| role_id | user_id | created_at          | updated_at          |
+---------+---------+---------------------+---------------------+
|       1 |       2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|       3 |       3 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|       2 |       1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|       2 |       2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------+---------+---------------------+---------------------+
4 rows in set (0.00 sec)

Here is what sqlite does:

begin transaction
   (0.2ms)  INSERT INTO "roles_users" ("user_id", "role_id") VALUES (1, 1)
SQLite3::ConstraintException: roles_users.created_at may not be NULL: INSERT INTO "roles_users" ("user_id", "role_id") VALUES (1, 1)
   (0.0ms)  rollback transaction
Completed 500 Internal Server Error in 11ms

ActiveRecord::StatementInvalid (SQLite3::ConstraintException: roles_users.created_at may not be NULL: INSERT INTO "roles_users" ("user_id", "role_id") VALUES (1, 1)):
  sqlite3 (1.3.6) lib/sqlite3/statement.rb:108:in `step'

Rails: rails 3.2.6

So, when assigning ids this way, on a table that has timestamps, mysql will simply fill them with 000000 and sqlite will raise an error. Is the assumption that tables like roles_users don't need timestamps? Is this expected behaviour?

[senny]

can you insert the necessary model code?

[acesuares]

class Role < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :roles
end

Of course there is other stuff like Devise stuff and such, but I believe this is the relevant code. If you need all, just ask.

[senny]

I can reproduce the error, It seems that has_and_belongs_to_many is not aware of the timestamps. I think if you use has_many :through it will work (http://guides.rubyonrails.org/association_basics.html)

@rafaelfranca is this considered a bug or is it intentional? Is has_and_belongs_to_many even recommended to use?

[rafaelfranca]

It is intentional. I remember a discussion about this one in the issue tracker #4653 (comment) #4682 #4696

[senny]

@rafaelfranca thanks for clarifying. @acesuares have a look at the mentioned tickets or you can just use the has_many :through association.

[acesuares]

Thanks! That helped.

Really wasn't aware that the timestamps where set to 0, I always thought they would work as normal mysql timestamps.

The discussion about this is closed, but I think it's important to know when a link between to objects is created and/or updated.

Cheers