How to turn off MySQL strict mode in Rails

Question

Upgrading to Rails 4, it seems MySQL strict mode is now on by default for Rails connections. I say this because my Rails app is getting "Mysql2::Error: Data too long for column" when saving a string value longer than 255 characters. Yet, I paste the same query into MySQL console (where global strict mode is reported to be off) and it works fine, just with truncation warnings. As further evidence, it says here "Rails 4 both use strict mode by default".

My question is how can I turn strict mode off from the Rails app? I'd rather avoid upgrading everything to support it right now.

score 27 · Accepted Answer · edited Jun 10 '20 at 05:06

27

You can set strict mode in your database.yml using strict: false as follows:

production:
  host: ...
  username: ...
  strict: false

https://api.rubyonrails.org/v4.2.8/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html

edited Jun 10 '20 at 05:06

Deepak Mahakale

22,834
10
68
88

answered Feb 19 '14 at 22:35

nimblegorilla

1,183
2
9
19

Great, this is working for me and documented. Looks like the right choice. – mahemoff Mar 25 '14 at 01:51
1

I agree, tested on my code and seems to be the right choice for rails 4, not for rails 3.2 – Polak Jul 18 '19 at 13:09

score 12 · Answer 2 · answered Sep 09 '17 at 07:35

12

The mysql2 gem exposes an option to execute an initial command on connect and reconnect. You can set the init_command from inside database.yml:

production:
  host: ...
  username: ...
  init_command: "SET @@SESSION.sql_mode = ''"

answered Sep 09 '17 at 07:35

Graeme

970
8
16

1

Thanks! This is the only one that worked for a rails 3.2 app. – RidingTheRails May 27 '18 at 14:10
This solution also works for a Rails 5.2 app, if instead of appending to the current SQL MODE you want to override it entierly – curveorzos Jun 10 '20 at 22:24

score 7 · Answer 3 · answered Jan 29 '14 at 01:34

7

You can add this to your database.yml

variables:
   sql_mode: 'traditional'

or

variables:
   strict_mode: false

See:

https://github.com/rails/rails/pull/8346

answered Jan 29 '14 at 01:34

Arya

2,135
15
13

This didn't work unfortunately. I tried `innodb_strict_mode` (that's the actual variable), but setting it to "OFF" or false still causes this error when saving a record. What's odd is the variable was recognised because if I try any random variable name in the same place, it throws an exception when Rails start. – mahemoff Jan 29 '14 at 18:37
Worked for me. See also https://github.com/rails/rails/issues/25924 for a recent change in rails 5. – Jared Beck Jul 29 '16 at 19:30

score 6 · Answer 4 · answered Feb 06 '14 at 22:12

6

Following @edubriguenti's answer, I found this. Applying the post and the comment there, I added the following to environment.rb and it looks to have solved the problem.

# Set MySQL to clear sql mode for all connections
class ActiveRecord::ConnectionAdapters::Mysql2Adapter 
  alias :connect_no_sql_mode :connect
  def connect
    connect_no_sql_mode
    execute("SET sql_mode = ''")
  end
end

ActiveRecord::Base.connection.reconnect!

answered Feb 06 '14 at 22:12

mahemoff

44,526
36
160
222

FYI for future people seeing this, this sometimes did not work for me on rails 4.0.3 - in production, the sql_mode is somehow still set to strict sometimes. I'll try setting it in database.yml as well, perhaps they fixed that setting. – Kevin Mar 13 '14 at 22:32
Okay. It's working for me afaict on 4.1 beta. Please update with results of database.yml as I didn't try it yet and it's obviously cleaner if it works. – mahemoff Mar 14 '14 at 00:55
I started having a problem with this where it no longer entered strict mode as expected. Finding @nimblegorilla's answer works properly. – mahemoff Mar 25 '14 at 01:52
maybe https://stackoverflow.com/a/34432139/520567 ? – akostadinov Aug 12 '23 at 09:36

score 2 · Answer 5 · answered Jan 31 '14 at 11:37

2

@arya 's answer might not work because 'traditional' sql mode is strict, if you want to set it to non-strict, try this:

sql_mode: ''

Hope that helps

answered Jan 31 '14 at 11:37

Piotr Kruczek

2,384
11
18

Worked for me as of Rails 4.2. – Piers C Feb 20 '16 at 19:14

score 1 · Answer 6 · answered Feb 06 '14 at 11:31

1

Try this:

# Set MySQL to clear sql mode for all connections
class ActiveRecord::ConnectionAdapters::MysqlAdapter 
  alias :connect_no_sql_mode :connect
  def connect
    connect_no_sql_mode
    execute("SET sql_mode = ''")
  end
end

answered Feb 06 '14 at 11:31

Eduardo Briguenti Vieira

4,351
3
37
49

This was a good starting point, but causes an error with modern Rails. Revised in my answer above. – mahemoff Feb 06 '14 at 23:58

How to turn off MySQL strict mode in Rails

6 Answers6

Linked