title: MySQL 5.5 innodb_large_prefix configuration setting not taking effect
body: <p>I'm running MySQL 5.5.55 on Debian Linux and I would like to enable the <code>innodb_large_prefix</code> setting so I can have table INDEXes that are larger than 767 bytes.</p>
  
  <p>I have the following configuration in my <code>/etc/mysql/my.cnf</code> file:</p>
  
  <pre><code>[mysqld]
  ...
  innodb_file_per_table=1
  innodb-large-prefix=ON
  innodb_file_format=barracuda
  </code></pre>
  
  <p>(I've also tried <code>innodb_large_prefix=ON</code> as well.)</p>
  
  <p>UPDATE: I double-checked, and no other configuration files exist e.g. <code>/etc/my.cnf</code>, <code>/usr/etc/my.cnf</code>, <code>~root/.my.cnf</code>, <code>~mysql/.my.cnf</code>)</p>
  
  <p>But when I restart MySQL, the active configuration shows as:</p>
  
  <pre><code>| innodb_file_format             | Barracuda              |
  | innodb_large_prefix            | OFF                    |
  | innodb_file_per_table          | ON                     |
  </code></pre>
  
  <p>If I manually change the GLOBAL setting from the MySQL CLI, the setting appears to change:</p>
  
  <pre><code>mysql&gt; SET GLOBAL innodb_large_prefix = ON;
  ...
  | innodb_large_prefix            | ON                     |
  </code></pre>
  
  <p>...but then when I attempt to actually use a "large prefix", I get an error:</p>
  
  <pre><code>mysql&gt; alter table [...]
  ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
  </code></pre>
  
  <p>The documentation for MySQL's <a href="https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix" rel="nofollow noreferrer"><code>innodb_large_prefix</code> setting</a> says that there are several prerequisites to use this setting:</p>
  
  <ol>
  <li>MySQL version 5.5.14 or later ✔</li>
  <li><code>innodb_file_format</code> = <code>Barracuda</code> ✔</li>
  <li><code>innodb_file_per_table</code> = <code>ON</code> ✔</li>
  <li><code>innodb_large_prefix</code> = <code>ON</code> ✔</li>
  </ol>
  
  <p>After playing-around a little bit, I was able to convert my table as desired by doing:</p>
  
  <ol>
  <li><code>SET GLOBAL innodb_large_prefix = ON;</code></li>
  <li><code>ALTER TABLE [...], ROW_FORMAT=DYNAMIC;</code></li>
  </ol>
  
  <p>Great. Then I restarted MySQL again and checked the effective settings:</p>
  
  <pre><code>| innodb_large_prefix            | OFF                    |
  </code></pre>
  
  <p>I have no errors when using that table, etc. so I guess everything is okay, but it's strange that the setting doesn't seem to stick.</p>
  
  <p>I tried re-ALTERing my table with the setting set to <code>OFF</code> and I am unable to build that large index. Does this mean that I will have to manually set the <code>innodb_large_prefix</code> to <code>ON</code> any time I want to ALTER a table in this way? Am I not configuring this setting properly in <code>/etc/mysql/my.cnf</code>?</p>