https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292016-08-15T08:55:16ZRedmineRedmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=726792016-08-15T08:55:16ZToshi MARUYAMA
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Needs feedback</i></li></ul><p>Try innodb_large_prefix.<br /><a class="external" href="https://github.com/rails/rails/issues/9855#issuecomment-35618750">https://github.com/rails/rails/issues/9855#issuecomment-35618750</a></p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=779652017-04-11T17:48:02ZShe Bytes
<ul></ul><p>I had this problem too, and it turned out I needed to update mariaDB to beta v10.2.5 from (10.0.?) to get the additional setting for innodb_default_row_format</p>
<p>In your mysql cnf file for mariaDB, add these lines:<br />innodb_file_format=Barracuda #default for >= 10.2.2<br />innodb_file_per_table=ON #default for >= 5.5<br />innodb_large_prefix=1 #default for >= 10.2.2<br />innodb_default_row_format=DYNAMIC #default for >= 10.2.2</p>
<p>These lines should all be default at this point, but since it started working, I just left it.</p>
<p>My system: <br />Environment:<br /> Redmine version 3.3.3.stable.16539<br /> Ruby version 2.2.5-p319 (2016-04-26) [x86_64-linux]<br /> Rails version 4.2.7.1<br /> Environment production<br /> Database adapter Mysql2</p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827222017-12-20T10:09:32ZStanislav Tilsh
<ul></ul><p>Sorry for broken English.<br />I install the version of Redmine 3.4.3 on Debian 9.<br />The redmine version was downloaded here: <a class="external" href="https://www.redmine.org/releases/redmine-3.4.3.tar.gz">https://www.redmine.org/releases/redmine-3.4.3.tar.gz</a></p>
<p>An error during the import of the database to the redmine database being installed is repeated exactly as described above:</p>
<pre>
== 28 CreateWikiPages: migrating ============================================ ==
- create_table (: wiki_pages)
-> 0.0206s
- add_index (: wiki_pages, [: wiki_id,: title], {: name =>: wiki_pages_wiki_id_title})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
Mysql2 :: Error: Index column size too large. The maximum column size is 767 bytes .: CREATE INDEX `wiki_pages_wiki_id_title` ON` wiki_pages` (`wiki_id`,` title`)
</pre>
<p>It is not possible to overcome this on my system:</p>
<pre>
Debian 9.
redmine 3.4.3.
ruby 2.3.3
rails 4.2.7.1
Environment production
Database adapter Mysql2
</pre> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827252017-12-20T10:58:50ZStanislav Tilsh
<ul></ul><p>Modifying the /home/sat/Redmine/redmine-3.4.3/db/migrate/028_create_wiki_pages.rb file to the following:</p>
<pre><code class="ruby syntaxhl"> <span class="k">class</span> <span class="nc">CreateWikiPages</span> <span class="o"><</span> <span class="no">ActiveRecord</span><span class="o">::</span><span class="no">Migration</span>
<span class="k">def</span> <span class="nc">self</span><span class="o">.</span><span class="nf">up</span>
<span class="n">create_table</span> <span class="ss">:wiki_pages</span> <span class="p">,</span> <span class="ss">options: </span><span class="s1">' ROW_FORMAT=DYNAMIC '</span> <span class="k">do</span> <span class="o">|</span><span class="n">t</span><span class="o">|</span>
<span class="n">t</span><span class="p">.</span><span class="nf">column</span> <span class="ss">:wiki_id</span><span class="p">,</span> <span class="ss">:integer</span><span class="p">,</span> <span class="ss">:null</span> <span class="o">=></span> <span class="kp">false</span>
<span class="n">t</span><span class="p">.</span><span class="nf">column</span> <span class="ss">:title</span><span class="p">,</span> <span class="ss">:string</span><span class="p">,</span> <span class="ss">:limit</span> <span class="o">=></span> <span class="mi">255</span><span class="p">,</span> <span class="ss">:null</span> <span class="o">=></span> <span class="kp">false</span>
<span class="n">t</span><span class="p">.</span><span class="nf">column</span> <span class="ss">:created_on</span><span class="p">,</span> <span class="ss">:datetime</span><span class="p">,</span> <span class="ss">:null</span> <span class="o">=></span> <span class="kp">false</span>
<span class="k">end</span>
<span class="n">add_index</span> <span class="ss">:wiki_pages</span><span class="p">,</span> <span class="p">[</span><span class="ss">:wiki_id</span><span class="p">,</span> <span class="ss">:title</span><span class="p">],</span> <span class="ss">:name</span> <span class="o">=></span> <span class="ss">:wiki_pages_wiki_id_title</span>
<span class="k">end</span>
<span class="k">def</span> <span class="nc">self</span><span class="o">.</span><span class="nf">down</span>
<span class="n">drop_table</span> <span class="ss">:wiki_pages</span>
<span class="k">end</span>
<span class="k">end</span>
</code></pre>
<p>I skipped migration to a similar problem:</p>
<pre>
67 CreateWikiRedirects: migrating ==========================================
-- create_table(:wiki_redirects)
-> 0.0205s
-- add_index(:wiki_redirects, [:wiki_id, :title], {:name=>:wiki_redirects_wiki_id_title})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Index column size too large. The maximum column size is 767 bytes.: CREATE INDEX `wiki_redirects_wiki_id_title` ON `wiki_redirects` (`wiki_id`, `title`)
</pre> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827262017-12-20T11:15:14ZStanislav Tilsh
<ul></ul><p>Changed the file /home/sat/Redmine/redmine-3.4.3/db/migrate/067_create_wiki_redirects.rb<br />adding to it:</p>
<pre><code>, options: ' ROW_FORMAT=DYNAMIC '</code></pre>
<p>having resulted a line to a kind:</p>
<pre><code>create_table :wiki_redirects , options: ' ROW_FORMAT=DYNAMIC ' do |t|</code></pre>
<p>The database structure was imported, but there was an error importing data into this structure:</p>
<pre><code>-- add_index(:changesets, [:repository_id, :revision], {:unique=>true, :name=>:changesets_repos_rev})<br />rake aborted!<br />StandardError: An error has occurred, all later migrations canceled:<br />Mysql2::Error: Index column size too large. The maximum column size is 767 bytes.: CREATE UNIQUE INDEX `changesets_repos_rev` ON `changesets` (`repository_id`, `revision`)</code></pre> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827282017-12-20T12:17:25ZStanislav Tilsh
<ul></ul><p>ups...</p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827292017-12-20T12:22:12ZStanislav Tilsh
<ul></ul><p>In the course of studying this problem, I found the following solution on the materials of this page: <a class="external" href="https://github.com/rails/rails/issues/9855#issuecomment-35618750">https://github.com/rails/rails/issues/9855#issuecomment-35618750</a></p>
<p>Created the file:</p>
<pre><code>/home/sat/Redmine/redmine-3.4.3/config/initializers/ar_innodb_row_format.rb</code></pre>
<p>With the following content:</p>
<pre><code>ActiveSupport.on_load :active_record do<br /> module ActiveRecord::ConnectionAdapters<br /> class AbstractMysqlAdapter<br /> def create_table_with_innodb_row_format(table_name, options = {})<br /> table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')<br /> create_table_without_innodb_row_format(table_name, table_options) do |td|<br /> yield td if block_given?<br /> end<br /> end<br /> alias_method_chain :create_table, :innodb_row_format<br /> end<br /> end<br />end</code></pre>
<p>As a result, migration is performed completely:</p>
<ul>
<li>Invoke db:_dump (first_time)</li>
<li>Execute db:_dump</li>
<li>Invoke db:schema:dump (first_time)</li>
<li>Invoke environment </li>
<li>Invoke db:load_config </li>
<li>Execute db:schema:dump</li>
</ul> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827302017-12-20T12:40:26ZStanislav Tilsh
<ul></ul><p>In the course of studying this problem, I found the following solution on the materials of this page: <a class="external" href="https://github.com/rails/rails/issues/9855#issuecomment-35618750">https://github.com/rails/rails/issues/9855#issuecomment-35618750</a></p>
<p>Created the file:</p>
<pre><code>/home/sat/Redmine/redmine-3.4.3/config/initializers/ar_innodb_row_format.rb</code></pre>
<p>With the following content:</p>
<pre><code>ActiveSupport.on_load :active_record do<br /> module ActiveRecord::ConnectionAdapters<br /> class AbstractMysqlAdapter<br /> def create_table_with_innodb_row_format(table_name, options = {})<br /> table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')<br /> create_table_without_innodb_row_format(table_name, table_options) do |td|<br /> yield td if block_given?<br /> end<br /> end<br /> alias_method_chain :create_table, :innodb_row_format<br /> end<br /> end<br />end</code></pre>
<p>Changed my.conf:</p>
<p>sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf</p>
<p>adding to it:</p>
<p>innodb_large_prefix = 1<br />innodb_file_format = barracuda<br />innodb_file_per_table = 1</p>
<p>As a result, migration is performed completely:</p>
<ul>
<li>Invoke db:_dump (first_time)</li>
<li>Execute db:_dump</li>
<li>Invoke db:schema:dump (first_time)</li>
<li>Invoke environment </li>
<li>Invoke db:load_config </li>
<li>Execute db:schema:dump</li>
</ul> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827312017-12-21T03:51:01ZToshi MARUYAMA
<ul></ul><p>Stanislav Tilsh wrote:</p>
<blockquote>
<p>Sorry for broken English.<br />I install the version of Redmine 3.4.3 on Debian 9.</p>
</blockquote>
<p>What is version of MySQL or MariaDB?<br />Which do you use utf8 or utf8mb4?</p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=827382017-12-21T11:49:24ZStanislav Tilsh
<ul></ul><p>Toshi MARUYAMA wrote:</p>
<blockquote>
<p>What is version of MySQL or MariaDB?<br />Which do you use utf8 or utf8mb4?</p>
</blockquote>
<p>mysql Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2</p>
<blockquote>
<p>MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';</p>
</blockquote>
<p>character_set_client utf8mb4 <br /> character_set_connection utf8mb4 <br /> character_set_database utf8mb4 <br /> character_set_filesystem binary <br /> character_set_results utf8mb4 <br /> character_set_server utf8mb4 <br /> character_set_system utf8 <br /> collation_connection utf8mb4_general_ci <br /> collation_database utf8mb4_general_ci <br /> collation_server utf8mb4_general_ci</p>
<blockquote>
<p>MariaDB [(none)]></p>
</blockquote> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=830552018-01-11T16:58:30ZAndrey Luzgin
<ul><li><strong>File</strong> <a href="/attachments/19871">key_too_long_fix.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/19871/key_too_long_fix.patch">key_too_long_fix.patch</a> added</li></ul><p>Inserted <code>, options: ' ROW_FORMAT=DYNAMIC '</code> after all create_table <br />For apply patch run: <code>patch -p1 < ../key_too_long_fix.patch</code></p>
<p>You should see:<br /><code>patching file db/migrate/001_setup.rb<br />patching file db/migrate/007_create_journals.rb<br />patching file db/migrate/008_create_user_preferences.rb<br />patching file db/migrate/010_create_comments.rb<br />patching file db/migrate/013_create_queries.rb<br />patching file db/migrate/015_create_repositories.rb<br />patching file db/migrate/017_create_settings.rb<br />patching file db/migrate/027_create_wikis.rb<br />patching file db/migrate/028_create_wiki_pages.rb<br />patching file db/migrate/029_create_wiki_contents.rb<br />patching file db/migrate/032_create_time_entries.rb<br />patching file db/migrate/034_create_changesets.rb<br />patching file db/migrate/035_create_changes.rb<br />patching file db/migrate/039_create_watchers.rb<br />patching file db/migrate/040_create_changesets_issues.rb<br />patching file db/migrate/042_create_issue_relations.rb<br />patching file db/migrate/045_create_boards.rb<br />patching file db/migrate/046_create_messages.rb<br />patching file db/migrate/067_create_wiki_redirects.rb<br />patching file db/migrate/068_create_enabled_modules.rb<br />patching file db/migrate/081_create_projects_trackers.rb<br />patching file db/migrate/107_add_open_id_authentication_tables.rb<br />patching file db/migrate/20090503121501_create_member_roles.rb<br />patching file db/migrate/20090704172355_create_groups_users.rb<br />patching file db/migrate/20110902000000_create_changeset_parents.rb<br />patching file db/migrate/20130602092539_create_queries_roles.rb<br />patching file db/migrate/20130713104233_create_custom_fields_roles.rb<br />patching file db/migrate/20150113194759_create_email_addresses.rb<br />patching file db/migrate/20150528092912_create_roles_managed_roles.rb<br />patching file db/migrate/20150730122707_create_imports.rb<br />patching file db/migrate/20150730122735_create_import_items.rb<br />patching file db/migrate/20151025072118_create_custom_field_enumerations.rb<br /></code></p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=848772018-05-06T21:22:01ZPhilippe Ferrucci
<ul></ul><p>This patch did nothing here (Debian 9 - 4.9.30-2+deb9u5 (2017-09-19), Ruby 2.5.1, MySQL 10.1.26-MariaDB-0+deb9u1).</p>
<p>I had to recreate the database with the correct charset, as explained by Leslie in <a href="http://www.redmine.org/boards/2/topics/54308" class="external">issue 54308</a></p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=872632018-09-14T15:03:25ZLuc Lalonde
<ul></ul><p>This solved the problem... Add this script redmine/config/initializers/mysqlpls.rb:</p>
<p>require 'active_record/connection_adapters/abstract_mysql_adapter'</p>
<p>module ActiveRecord<br /> module ConnectionAdapters<br /> class AbstractMysqlAdapter<br /> NATIVE_DATABASE_TYPES[:string] = { :name => "varchar", :limit => 191 }<br /> end<br /> end<br />end</p>
<p>I would like to give credit to the person who came up with this solution... But I can't remember where I got it from.</p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=893502019-01-11T13:17:57Zmr gosh
<ul></ul><p>Stanislav Tilsh wrote:</p>
<blockquote>
<p>Created the file:</p>
<p>/home/sat/Redmine/redmine-3.4.3/config/initializers/ar_innodb_row_format.rb</p>
<p>With the following content:</p>
<p>ActiveSupport.on_load :active_record do<br />module ActiveRecord::ConnectionAdapters<br />class AbstractMysqlAdapter<br />def create_table_with_innodb_row_format(table_name, options = {})<br />table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')<br />create_table_without_innodb_row_format(table_name, table_options) do |td|<br />yield td if block_given?<br />end<br />end<br />alias_method_chain :create_table, :innodb_row_format<br />end<br />end<br />end</p>
<p>As a result, migration is performed completely:</p>
<ul>
<li>Invoke db:_dump (first_time)</li>
<li>Execute db:_dump</li>
<li>Invoke db:schema:dump (first_time)</li>
<li>Invoke environment </li>
<li>Invoke db:load_config </li>
<li>Execute db:schema:dump</li>
</ul>
</blockquote>
<p>THX! this is still needed on my ubuntu 18.04 installation with mariadb from the repos...!</p> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=931722019-08-17T16:14:39ZMarius BÄ‚LTEANU
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/31921">Feature #31921</a>: Changes to properly support 4 byte characters (emoji) when database is MySQL</i> added</li></ul> Redmine - Defect #23586: Create index on mysql exceed limitshttps://www.redmine.org/issues/23586?journal_id=966312020-03-08T08:13:23ZGo MAEDA
<ul><li><strong>Status</strong> changed from <i>Needs feedback</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>The error does not occur even when you set the encoding to utf8mb4 if you use MySQL 5.7.7 or later.</p>