Rails 4.1 - Write to MySQL database without typecasting

Question

I have a column in my MySQL database which is of type TINYINT(1). I need to store actual integers in this column. The problem is, because of the column type, Rails 4.1 assumes this column contains only boolean values, so it typecasts all values besides 0 or 1 to be 0 when it writes to the database.

I don't want to simply disable boolean emulation since we have a number of columns in our database where we use TINYINT(1) to actually represent a boolean value. And I am currently not able to change the column types in MySQL.

How can I force Rails 4.1 to bypass the typecasting step and write directly to the database instead?

(This excerpt from the Rails 4.1 source may be of some use: https://github.com/rails/rails/blob/4-1-stable/activerecord/lib/active_record/attribute_methods/write.rb)

Additional context for other readers, `TINYINT(1)` can store signed integer values -127..+127 http://stackoverflow.com/questions/4401673/mysql-boolean-tinyint1-holds-values-up-to-127 but MySQL [uses `BOOLEAN` as a synonym for it](https://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html). — Michael Berkowski, Jun 23 '15 at 20:36
Would it be the end of the world if you opened up that column to a regular `INT` using a migration? — tadman, Jun 23 '15 at 21:03
@tadman Believe me, the thought crossed my mind, but for reasons outside my control I'm not able to change the structure of this database. — Ben Visness, Jun 23 '15 at 21:55
You could try looking at [this answer](http://stackoverflow.com/questions/11036563/how-to-access-column-value-after-serialization-activerecord) to see if that helps. — tadman, Jun 23 '15 at 22:00
@tadman It's easy to *read* raw values from MySQL since Rails provides a method for that. But *writing* raw values is the issue at hand. — Ben Visness, Jun 23 '15 at 22:09
@BenVisness have you tried overwriting the setter like this: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#class-ActiveRecord%3a%3aBase-label-Overwriting+default+accessors — d34n5, Jun 25 '15 at 21:17

score 3 · Accepted Answer · answered Jun 26 '15 at 23:59

3

Could you use raw SQL to do the insert?

Something like:

sql = "INSERT INTO my_table (smallnumber) VALUES (100)"
ActiveRecord::Base.connection.execute(sql)

answered Jun 26 '15 at 23:59

user1002119

3,692
4
27
30

This was the workaround I found for myself as well. I had hoped to find a more Ruby-like solution, but this is ultimately the most elegant solution I have been able to find. So the bounty goes to you, sir! – Ben Visness Jul 02 '15 at 04:34
Better still, this method works nicely when you override the default setter for the attribute! – Ben Visness Jul 02 '15 at 04:37

d34n5 · Answer 2 · 2015-06-25T22:01:42.810

1

I don't know if it works but you can try to overwrite the setter using the method :raw_write_attribute or :write_attribute. The :raw_write_attribute and :write_attribute methods disable/enable the type casting before writing.

Let's say the attribute/column is called :the_boolean_column_who_wanted_to_be_an_integer, you can probably do something like:

def the_boolean_column_who_wanted_to_be_an_integer=(value)
  raw_write_attribute(:the_boolean_column_who_wanted_to_be_an_integer, value) # or write_attribute(...
end

Does it work?

edited Jun 25 '15 at 22:01

answered Jun 25 '15 at 21:43

d34n5

1,308
10
18

The hilarious problem with that solution is this line in the Rails 4.1 source: `alias_method :raw_type_cast_attribute_for_write, :type_cast_attribute_for_write`. So in Rails 4.1, raw_write_attribute apparently is no different from write_attribute! – Ben Visness Jun 25 '15 at 22:17
I think you may be right, though, that that method would work in newer versions of Rails. I unfortunately don't think I'll be able to change the version in the project I'm working on. – Ben Visness Jun 25 '15 at 22:24

AOG · Answer 3 · 2015-06-26T00:43:22.493

Maybe you should overwrite the setter completely, using rails 4.1 source code:

def the_field=(value)
    attr_name = 'the_field'
    attr_name = self.class.primary_key if attr_name == 'id' && self.class.primary_key
    @attributes_cache.delete(attr_name)
    column = column_for_attribute(attr_name)

    # If we're dealing with a binary column, write the data to the cache
    # so we don't attempt to typecast multiple times.
    if column && column.binary?
      @attributes_cache[attr_name] = value
    end

    if column || @attributes.has_key?(attr_name)
      @attributes[attr_name] = value
    else
      raise ActiveModel::MissingAttributeError, "can't write unknown attribute `#{attr_name}'"
    end
end

Note that @attributes[attr_name] = send(type_cast_method, column, value) has been changed to @attributes[attr_name] = value . You can probably simplify it for your use case. Also note that I haven't tried this, and even if it works, you should be careful whenever you want to upgrade rails.

score 0 · Answer 4 · answered Jun 26 '15 at 22:58

0

Plan A: Change to SMALLINT (2 bytes) as a compromise.

Plan B: See if TINYINT(3) will fool Rails into not thinking it is Boolean.

Plan C: See if TINYINT UNSIGNED will fool Rails into not thinking it is Boolean. (This assumes your number are non-negative: 0..255.)

answered Jun 26 '15 at 22:58

Rick James

135,179
13
127
222

This is a good answer, but as I clarified in the comments, I am *not* able to change the column types in MySQL. (And I know from experience that `TINYINT(2)` works fine.) – Ben Visness Jun 28 '15 at 20:03

Rails 4.1 - Write to MySQL database without typecasting

4 Answers4