title: How do I disable STRICT_TRANS_TABLES for a Homebrew-installed MySQL server?
body: <p>It seems that MySQL recently (5.6?) changed the default SQL mode to be more restrictive. The new mode is "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION". I like the change, but at least one website that I maintain does not. INSERT queries are failing because they don't specify values for columns that don't have defaults. Before, MySQL would infer default values by column type.</p>
  
  <p>For now, I want to disable STRICT_TRANS_TABLES. I have added <code>sql_mode=NO_ENGINE_SUBSTITUTION</code> to <code>my.cnf</code> and restarted the server but the strict setting persists. What am I doing wrong?</p>
  
  <p>MySQL version:</p>
  
  <pre><code>$ mysqld --version
  mysqld  Ver 5.6.15 for osx10.9 on x86_64 (Homebrew)
  </code></pre>
  
  <p>my.cnf:</p>
  
  <pre><code>$ cat /etc/my.cnf
  [mysqld]
  sql_mode=NO_ENGINE_SUBSTITUTION
  
  # not sure if this is needed but it doesn't seem to have an effect either way
  [mysqld_safe]
  sql_mode=NO_ENGINE_SUBSTITUTION
  </code></pre>
  
  <p>Confirm that mysqld would use settings in my.cnf:</p>
  
  <pre><code>$ mysqld --print-defaults
  mysqld would have been started with the following arguments:
  --sql_mode=NO_ENGINE_SUBSTITUTION
  </code></pre>
  
  <p>Confirm that mysqld is not currently running:</p>
  
  <pre><code>$ ps aux | grep mysql
  metaphile        1022   0.0  0.0  2432784    600 s003  S+    3:10PM   0:00.00 grep mysql
  </code></pre>
  
  <p>Property list provided by Homebrew:</p>
  
  <pre><code>$ cat ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist 
  &lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"&gt;
  &lt;plist version="1.0"&gt;
  &lt;dict&gt;
    &lt;key&gt;KeepAlive&lt;/key&gt;
    &lt;true/&gt;
    &lt;key&gt;Label&lt;/key&gt;
    &lt;string&gt;homebrew.mxcl.mysql&lt;/string&gt;
    &lt;key&gt;ProgramArguments&lt;/key&gt;
    &lt;array&gt;
      &lt;string&gt;/usr/local/opt/mysql/bin/mysqld_safe&lt;/string&gt;
      &lt;string&gt;--bind-address=127.0.0.1&lt;/string&gt;
    &lt;/array&gt;
    &lt;key&gt;RunAtLoad&lt;/key&gt;
    &lt;true/&gt;
    &lt;key&gt;WorkingDirectory&lt;/key&gt;
    &lt;string&gt;/usr/local/var&lt;/string&gt;
  &lt;/dict&gt;
  &lt;/plist&gt;
  </code></pre>
  
  <p>Start MySQL and check SQL mode:</p>
  
  <pre><code>$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist
  $ mysql -uroot
  ...
  mysql&gt; SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
  +--------------------------------------------+--------------------------------------------+
  | @@GLOBAL.sql_mode                          | @@SESSION.sql_mode                         |
  +--------------------------------------------+--------------------------------------------+
  | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
  +--------------------------------------------+--------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Argh!</p>