title: How to make sql-mode="NO_ENGINE_SUBSTITUTION" permanent in MySQL my.cnf
body: <h2>UPDATE FIXED 1/18/15</h2>
  
  <p>After we recently updated to MySQL 5.6.27 (from the Ubuntu repo), this option now works. So this appears to have been a problem with the previous version of MySQL.</p>
  
  <h2>ORIGINAL QUESTION</h2>
  
  <p>With a new upgrade to MySQL (5.6.20), updates and inserts fail unless I set sql-mode to NO_ENGINE_SUBSTITUTION.</p>
  
  <p>Thanks to the <a href="http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html">documentation</a>, I can run the following from mysql terminal and that fixes the problem (temporarily):</p>
  
  <pre><code>SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
  SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';`
  </code></pre>
  
  <p>But the next time MySQL restarts, these settings are lost. </p>
  
  <p>So I have tried to make that permanent by editing /etc/mysql/my.cnf (on my standard server running Ubuntu 12.04.5 LTS), and adding the config settings that the <a href="http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html">documentation</a> says should be added:</p>
  
  <pre><code>[mysqld]
  sql-mode="NO_ENGINE_SUBSTITUTION"
  </code></pre>
  
  <h2>Alternative Syntaxes for Testing</h2>
  
  <p>Just for testing purposes, I have also tried the following formats (which do not cause errors when restarting MySQL, but they do not affect the setting).</p>
  
  <pre><code># dash no quotes
  sql-mode=NO_ENGINE_SUBSTITUTION
  # underscore no quotes
  sql_mode=NO_ENGINE_SUBSTITUTION
  # underscore and quotes
  sql_mode="NO_ENGINE_SUBSTITUTION"
  </code></pre>
  
  <p>Nothing works. After restart this setting is lost and I have to run the commands manually again from mysql terminal to make saving work again. </p>
  
  <h2>Alternative Locations</h2>
  
  <ul>
  <li>I know /etc/mysql/my.cnf is being referenced because we have replication defined in this file, and that is working.</li>
  <li>There is not another identical setting in this file that is overwriting it.</li>
  </ul>
  
  <p>I get a list of the config files that are being referenced by running this from the command line:</p>
  
  <pre><code>mysqld --help --verbose
  </code></pre>
  
  <p>I see a line that reads:</p>
  
  <pre><code>Default options are read from the following files in the given order:
  /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
  </code></pre>
  
  <p>This is the default location it "looks" for files, it doesn't mean that it actually found a file there, e.g. my server doesn't have <code>/etc/my.cnf</code>, <code>/usr/etc/my.cnf</code> or <code>~/.my.cnf</code>.</p>
  
  <p>So it looks like my config in /etc/mysql/my.cnf is the only file mysql is referencing, and therefore this setting is not being overwritten. </p>
  
  <h2>Logical Conclusion of Testing</h2>
  
  <p>Logically then, it seems the syntax is not correct or is being ignored for some other reason. Any other ideas?</p>