"Incorrect string value" error when indexing attached files

[tsuchm]

Sorry for my multiple posts.

redmine:/var/lib/redmine# bundle exec rake full_text_search:synchronize RAILS_ENV=production
┌ FullTextSearch::Target:All [===========                                                                                                     ] 245/2463(9%) 40s 64.74/s  4s
└── Journal:New [] 0/0(100%)  0s  0.00/s  0s
└── Journal:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── Journal:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── Issue:New [] 0/0(100%)  0s  0.00/s  0s
└── Issue:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── Issue:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── WikiPage:New [] 0/0(100%)  0s  0.00/s  0s
└── WikiPage:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── WikiPage:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── CustomValue:New [] 0/0(100%)  0s  0.00/s  0s
└── CustomValue:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── CustomValue:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── Project:New [] 0/0(100%)  0s  0.00/s  0s
└── Project:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── Project:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── News:New [] 0/0(100%)  0s  0.00/s  0s
└── News:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── News:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── Document:New [] 0/0(100%)  0s  0.00/s  0s
└── Document:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── Document:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── Message:New [] 0/0(100%)  0s  0.00/s  0s
└── Message:Orphan [] 0/0(100%)  0s  0.00/s  0s
└── Message:Outdated [] 0/0(100%)  0s  0.00/s  0s
└── Attachment:New [============                                                                                                              ] 245/2463(9%) 33s 64.74/s  3s
rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect string value: '\xF2\x82\x9E\xAATv...' for column 'content' at row 1: UPDATE `fts_targets` SET `tag_ids` = '[7,81]', `content` = '%PDF-1.5%10 0 obj<</Filter/FlateDecode/Length 2305>>streamx (snip)%%EOF' WHERE `fts_targets`.`id` = 31481
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:196:in `execute_and_free'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:45:in `exec_delete'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:170:in `update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/query_cache.rb:21:in `update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:197:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:696:in `_update_row'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/locking/optimistic.rb:80:in `_update_row'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:719:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/attribute_methods/dirty.rb:134:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:350:in `block in _update_record'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:98:in `run_callbacks'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:816:in `_run_update_callbacks'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:350:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/timestamp.rb:114:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:705:in `create_or_update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:342:in `block in create_or_update'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:98:in `run_callbacks'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:816:in `_run_save_callbacks'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:342:in `create_or_update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:308:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/validations.rb:52:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:315:in `block in save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:387:in `block in with_transaction_returning_status'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `block in transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:239:in `block in within_new_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:236:in `within_new_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:212:in `transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:385:in `with_transaction_returning_status'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:315:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/suppressor.rb:48:in `save!'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/attachment_mapper.rb:105:in `extract_text'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/mapper.rb:129:in `extract_content'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/attachment_mapper.rb:84:in `upsert_fts_target'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:113:in `block (2 levels) in synchronize_fts_targets_internal'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:213:in `yield'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:213:in `block (2 levels) in iterate'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `block in iterate'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `iterate'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:105:in `block in synchronize_fts_targets_internal'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/resolver.rb:43:in `each'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/resolver.rb:43:in `each'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:100:in `synchronize_fts_targets_internal'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:11:in `synchronize'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:51:in `block (3 levels) in <top (required)>'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:41:in `block (2 levels) in <top (required)>'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:50:in `block (2 levels) in <top (required)>'
/var/lib/gems/2.3.0/gems/rake-13.0.0/exe/rake:27:in `<top (required)>'

Caused by:
Mysql2::Error: Incorrect string value: '\xF2\x82\x9E\xAATv...' for column 'content' at row 1
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:196:in `execute_and_free'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:45:in `exec_delete'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:170:in `update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/query_cache.rb:21:in `update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:197:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:696:in `_update_row'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/locking/optimistic.rb:80:in `_update_row'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:719:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/attribute_methods/dirty.rb:134:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:350:in `block in _update_record'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:98:in `run_callbacks'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:816:in `_run_update_callbacks'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:350:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/timestamp.rb:114:in `_update_record'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:705:in `create_or_update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:342:in `block in create_or_update'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:98:in `run_callbacks'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/callbacks.rb:816:in `_run_save_callbacks'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/callbacks.rb:342:in `create_or_update'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/persistence.rb:308:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/validations.rb:52:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:315:in `block in save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:387:in `block in with_transaction_returning_status'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `block in transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:239:in `block in within_new_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/transaction.rb:236:in `within_new_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:212:in `transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:385:in `with_transaction_returning_status'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/transactions.rb:315:in `save!'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/suppressor.rb:48:in `save!'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/attachment_mapper.rb:105:in `extract_text'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/mapper.rb:129:in `extract_content'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/attachment_mapper.rb:84:in `upsert_fts_target'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:113:in `block (2 levels) in synchronize_fts_targets_internal'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:213:in `yield'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:213:in `block (2 levels) in iterate'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:211:in `block in iterate'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `each'
/var/lib/gems/2.3.0/gems/tty-progressbar-0.16.0/lib/tty/progressbar.rb:216:in `iterate'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:105:in `block in synchronize_fts_targets_internal'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/resolver.rb:43:in `each'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/resolver.rb:43:in `each'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:100:in `synchronize_fts_targets_internal'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/full_text_search/batch_runner.rb:11:in `synchronize'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:51:in `block (3 levels) in <top (required)>'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:41:in `block (2 levels) in <top (required)>'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/lib/tasks/full_text_search.rake:50:in `block (2 levels) in <top (required)>'
/var/lib/gems/2.3.0/gems/rake-13.0.0/exe/rake:27:in `<top (required)>'
Tasks: TOP => full_text_search:synchronize
(See full trace by running task with --trace)

[kou]

Umm. \xF2\x82\x9E\xAA is a valid UTF-8 character but MySQL rejects the character. (It's a valid UTF-8 character but the character doesn't exist in Unicode. So the rejection is understandable.)

This plugin filters invalid UTF-8 characters by String#scrub but the character is a valid UTF-8 encoding character. So String#scrub accepts the character.

Install ChupaText server, a text extraction server, is the straightforward solution. The character is appeared because raw PDF data is treated as text. If you install ChupaText server, ChupaText extracts text from the PDF. So the invalid character isn't appeared.

See https://github.com/clear-code/redmine_full_text_search#install-chupatext-server-optional and https://github.com/clear-code/redmine_full_text_search#configure-this-plugin how to install ChupaText server.

chupa-text-docker or chupa-text-vargrant will be suitable.

[kou]

I'm using chupa-text-docker.

[tsuchm]

Sorry, my previous comment was confusing. In order to hide my confidential document, I replace almost of them was replaced into "(snip)". Corrupt byte sequence was included in the replaced part.

[kou]

No problem. '%PDF-1.5... shows that text isn't extracted from the PDF. ChupaText server will solve it.

[tsuchm]

The following patch was effective to ease my error.

--- a/lib/full_text_search/attachment_mapper.rb
+++ b/lib/full_text_search/attachment_mapper.rb
@@ -102,7 +102,12 @@ JOIN projects
       set_extracted_content(fts_target,
                             content,
                             [@record.description.presence])
-      fts_target.save!
+      begin
+        fts_target.save!
+      rescue ActiveRecord::StatementInvalid => error
+        $stderr.puts("Failed to save extracted text: #{error.message}")
+        return
+      end
     end

     private

[kou]

If we want to just ignore this error, we can use fts_target.save (no !). But this is not a real fix. It's just a workaround.

[tsuchm]

Yes, I know it is a ugly workaround. As you advised me, ChupaText server will be a right fix. However, it is still useful for our case: we hope to keep the redmine server simple.

[kou]

Using encoding: utf8mb4 in config/database.yml may fix this: https://github.com/clear-code/redmine_full_text_search/issues/76#issuecomment-552624884

[tsuchm]

My config/database.yml has following configuration.

production:
  adapter: mysql2
  database: redmine
  host: localhost
  username: redmine
  password: "********"
  encoding: utf8

I have two questions:

1. encoding: utf8mb4 is different from encoding: utf8?
2. Do you have a safe procedure to migrate the existing database? I think that it will be necessary to convert all tables of Redmine when I will change this setting.

[kou]

utf8mb4 supports 4byte UTF-8 character. utf8 doesn't. \xF2\x82\x9E\xAA is a 4byte UTF-8 character.

In your case, you don't need to migrate the existing database. Because you don't use 4byte UTF-8 characters in issue comment, project description and so on.

You just need to use utf8mb4 in tables only for this plugin at least. Because raw PDF data may have 4byte UTF-8 characters (unintentionally).

You will be able to use utf8mb4 in tables only for this plugin by the followings:

$ sed -i.bak -e 's/encoding: utf8/encoding: utf8mb4/g' config/database.yml
$ bundle exec rake redmine:plugins:migrate RAILS_ENV=production NAME=full_text_search VERSION=0
$ bundle exec rake redmine:plugins:migrate RAILS_ENV=production NAME=full_text_search
$ bundle exec rake full_text_search:synchronize RAILS_ENV=production

FYI: Here is a shell script I used when I convert to utf8mb4 from utf8 on our Redmine database:

sed -r \
  -e 's,^/\*!40101 SET character_set_client = utf8 ,/*!40101 SET character_set_client = utf8mb4 ,g' \
  -e 's,^( .+ CHARACTER SET )utf8 ,\1utf8mb4 ,g' \
  -e 's,^( .+ COLLATE )utf8_unicode_ci,\1utf8mb4_general_ci,g' \
  -e 's,^(\) ENGINE=.+ DEFAULT CHARSET=)utf8 ,\1utf8mb4 ,g' \
  -e 's,^(\) ENGINE=.+ COLLATE=)utf8_unicode_ci,\1utf8mb4_general_ci,g'

I converted mysqldump result by this shell script and restore by the converted dump.

I used this approach because I need to migrate DB server. So I need to dump MySQL data.

You can migrate to utf8mb4 by ALTER TABLE .... It can be done online but you need to run many ALTER TABLE ...s.

[tsuchm]

Thanks for your kind advice.

I have just tried re-construction of table for full_text_search plugin. Re-construction itself suceeded, however, I still saw two warnings produced by my patch.

Failed to save extracted text: Mysql2::Error: Incorrect string value: '\xF2\x82\x9E\xAATv...' for column 'content' at row 1: UPDATE `fts_targets` SET `tag_ids` = '[3,81]', `content` = '%PDF-1.5%10 (snip)'
Failed to save extracted text: Mysql2::Error: Incorrect string value: '\xF0\x9F\x84\xAC U...' for column 'content' at row 1: UPDATE `fts_targets` SET `tag_ids` = '[5,89]', `content` = 'Microsoft Visual Studio (snip)'

I think that the number of these warnings is small enough.

[tsuchm]

Mystery still remains.

MariaDB [redmine]> show create table issue_contents;
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                   |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| issue_contents | CREATE TABLE `issue_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `issue_id` int(11) NOT NULL,
  `subject` text,
  `contents` longtext,
  `status_id` int(11) DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `index_issue_contents_on_contents` (`contents`) COMMENT 'TOKENIZER ''TokenMecab'''
) ENGINE=Mroonga DEFAULT CHARSET=utf8 |
+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL says utf8 is used as the charset of issue_contents table. For confirmation, I do drop table issue_contents and re-execute your procedure, but, the result has not been changed.

Do you have an idea about my mistake?

[kou]

Umm.

Could you show SHOW CREATE DATABASE redmine?

Running ALTER DATABASE redmine DEFAULT CHARACTER SET utf8mb4; then re-migrating this plugin may fix this.

[tsuchm]

The default charset for redmine database was successfully changed as follows:

MariaDB [(none)]> show create database redmine;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| redmine  | CREATE DATABASE `redmine` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> ALTER DATABASE redmine DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show create database redmine;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| redmine  | CREATE DATABASE `redmine` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

However, it causes other problem:

# bundle exec rake redmine:plugins:migrate RAILS_ENV=production NAME=full_text_search VERSION=0
(snip, this command suceeded)
# bundle exec rake redmine:plugins:migrate RAILS_ENV=production NAME=full_text_search
== 20170630063557 EnablePgroonga: migrating ===================================
== 20170630063557 EnablePgroonga: migrated (0.0000s) ==========================

== 20170630063657 CreateSearcherRecords: migrating ============================
-- create_table(:searcher_records, {:options=>"ENGINE=Mroonga", :id=>:integer})
   -> 0.2923s
== 20170630063657 CreateSearcherRecords: migrated (0.2924s) ===================

== 20170630063757 CopyRecordsToSearcherRecords: migrating =====================
== 20170630063757 CopyRecordsToSearcherRecords: migrated (0.0000s) ============

== 20170630075027 LoadCommentsFromChangesets: migrating =======================
== 20170630075027 LoadCommentsFromChangesets: migrated (0.0000s) ==============

== 20170630075028 AddIndexToSearcherRecords: migrating ========================
== 20170630075028 AddIndexToSearcherRecords: migrated (0.0000s) ===============

== 20170810045914 CreateIssueContents: migrating ==============================
-- create_table(:issue_contents, {:options=>"ENGINE=Mroonga", :id=>:integer})
   -> 0.0255s
== 20170810045914 CreateIssueContents: migrated (0.0256s) =====================

== 20170810052427 AddIndexToIssueContents: migrating ==========================
== 20170810052427 AddIndexToIssueContents: migrated (0.0000s) =================

== 20190226093842 ExtendContentSize: migrating ================================
== 20190226093842 ExtendContentSize: migrated (0.0000s) =======================

== 20190226093843 AddMissingIndexesToSearcherRecords: migrating ===============
== 20190226093843 AddMissingIndexesToSearcherRecords: migrated (0.0000s) ======

== 20190603054615 DropSearcherRecords: migrating ==============================
-- drop_table(:searcher_records)
   -> 0.0361s
== 20190603054615 DropSearcherRecords: migrated (0.0362s) =====================

== 20190603060948 CreateFtsTypes: migrating ===================================
-- create_table(:fts_types)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE TABLE `fts_types` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL, UNIQUE INDEX `index_fts_types_on_name`  (`name`))
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:311:in `create_table'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:871:in `block in method_missing'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `block in say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:860:in `method_missing'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/db/migrate/20190603060948_create_fts_types.rb:5:in `change'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:814:in `exec_migration'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:797:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `with_connection'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:796:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:977:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1345:in `ddl_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `each'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:504:in `up'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1011:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:536:in `migrate_plugin'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:477:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:488:in `migrate'
/var/lib/redmine-4.0.5/lib/tasks/redmine.rake:135:in `block (3 levels) in <top (required)>'
/var/lib/gems/2.3.0/gems/rake-13.0.0/exe/rake:27:in `<top (required)>'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE TABLE `fts_types` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL, UNIQUE INDEX `index_fts_types_on_name`  (`name`))
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:311:in `create_table'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:871:in `block in method_missing'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `block in say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:860:in `method_missing'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/db/migrate/20190603060948_create_fts_types.rb:5:in `change'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:814:in `exec_migration'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:797:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `with_connection'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:796:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:977:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1345:in `ddl_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `each'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:504:in `up'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1011:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:536:in `migrate_plugin'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:477:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:488:in `migrate'
/var/lib/redmine-4.0.5/lib/tasks/redmine.rake:135:in `block (3 levels) in <top (required)>'
/var/lib/gems/2.3.0/gems/rake-13.0.0/exe/rake:27:in `<top (required)>'

Caused by:
Mysql2::Error: Specified key was too long; max key length is 767 bytes
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/var/lib/gems/2.3.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
/var/lib/gems/2.3.0/gems/activesupport-5.2.3/lib/active_support/notifications/instrumenter.rb:23:in `instrument'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/schema_statements.rb:311:in `create_table'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:871:in `block in method_missing'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `block in say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:840:in `say_with_time'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:860:in `method_missing'
/var/lib/redmine-4.0.5/plugins/redmine_full_text_search/db/migrate/20190603060948_create_fts_types.rb:5:in `change'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:814:in `exec_migration'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:798:in `block (2 levels) in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:797:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:416:in `with_connection'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:796:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:977:in `migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1292:in `block in execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1345:in `ddl_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1291:in `execute_migration_in_transaction'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1263:in `block in migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `each'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1262:in `migrate_without_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `block in migrate'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1363:in `with_advisory_lock'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1210:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:504:in `up'
/var/lib/gems/2.3.0/gems/activerecord-5.2.3/lib/active_record/migration.rb:1011:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:536:in `migrate_plugin'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:477:in `migrate'
/var/lib/redmine-4.0.5/lib/redmine/plugin.rb:488:in `migrate'
/var/lib/redmine-4.0.5/lib/tasks/redmine.rake:135:in `block (3 levels) in <top (required)>'
/var/lib/gems/2.3.0/gems/rake-13.0.0/exe/rake:27:in `<top (required)>'
Tasks: TOP => redmine:plugins:migrate
(See full trace by running task with --trace)

[kou]

OK. Let's revert the change: ALTER DATABASE redmine DEFAULT CHARACTER SET utf8.

[kou]

Could you re-migrating with master after https://travis-ci.org/clear-code/redmine_full_text_search/builds/611230606 is green?

[kou]

Green.

[tsuchm]

The charset for issue_contents table was successfully changed as follows:

MariaDB [redmine]> show create table issue_contents;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| issue_contents | CREATE TABLE `issue_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) DEFAULT NULL,
  `issue_id` int(11) NOT NULL,
  `subject` text,
  `contents` longtext,
  `status_id` int(11) DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `index_issue_contents_on_contents` (`contents`) COMMENT 'TOKENIZER ''TokenMecab'''
) ENGINE=Mroonga DEFAULT CHARSET=utf8mb4 |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

However, the warnings reported in https://github.com/clear-code/redmine_full_text_search/issues/75#issuecomment-553246294 still exist.

[kou]

issue_contents isn't related to this case. Could you show SHOW CREATE TABLE fts_targets?

[tsuchm]

utf8mb4 is used.

MariaDB [redmine]> show create table fts_targets;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| fts_targets | CREATE TABLE `fts_targets` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `source_id` int(11) NOT NULL,
  `source_type_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `container_id` int(11) DEFAULT NULL,
  `container_type_id` int(11) DEFAULT NULL,
  `custom_field_id` int(11) DEFAULT NULL,
  `is_private` tinyint(1) DEFAULT NULL,
  `last_modified_at` timestamp NULL DEFAULT NULL,
  `title` text,
  `content` longtext COMMENT 'FLAGS ''COLUMN_SCALAR|COMPRESS_ZSTD''',
  `tag_ids` text COMMENT 'FLAGS ''COLUMN_VECTOR'', GROONGA_TYPE ''Int32''',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_fts_targets_on_source_id_and_source_type_id` (`source_id`,`source_type_id`),
  KEY `index_fts_targets_on_project_id` (`project_id`),
  KEY `index_fts_targets_on_container_id` (`container_id`),
  KEY `index_fts_targets_on_container_type_id` (`container_type_id`),
  KEY `index_fts_targets_on_custom_field_id` (`custom_field_id`),
  KEY `index_fts_targets_on_is_private` (`is_private`),
  KEY `index_fts_targets_on_last_modified_at` (`last_modified_at`),
  KEY `index_fts_targets_on_source_type_id` (`source_type_id`),
  FULLTEXT KEY `index_fts_targets_on_title` (`title`) COMMENT 'NORMALIZER ''NormalizerNFKC121''',
  FULLTEXT KEY `index_fts_targets_on_content` (`content`) COMMENT 'NORMALIZER ''NormalizerNFKC121'', INDEX_FLAGS ''WITH_POSITION|INDEX_LARGE''',
  FULLTEXT KEY `index_fts_targets_on_tag_ids` (`tag_ids`) COMMENT 'LEXICON ''fts_tags'', INDEX_FLAGS '''''
) ENGINE=Mroonga AUTO_INCREMENT=33804 DEFAULT CHARSET=utf8mb4 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[akahane92]

Hi, Just FYI.

I think this error is common issue during change character set from utf8 to utf8mb4 with existing Mysql database.

Mysql2::Error: Specified key was too long; max key length is 767 bytes

Hints and sample for solving this issue, as follows (Japanese):

- http://zappy.hatenablog.jp/entry/2015/05/19/015044 - https://tmtms.hatenablog.com/entry/2016/09/06/mysql-utf8 ### My sample ``` ＜change /etc/my.cnf for MySQL 5.7＞ [mysqld] character-set-server = utf8mb4 #change sudo service mysql restart use ; alter database `` default character set utf8mb4; alter table `attachments` ROW_FORMAT=DYNAMIC; alter table `attachments` convert to character set utf8mb4; .... alter table `workflows` ROW_FORMAT=DYNAMIC; alter table `workflows` convert to character set utf8mb4; ```

[kou]

@akahane92 Thanks for the information. But we don't need to handle this error for this case. Because this is caused in fts_targets table. It uses Mroonga not InnoDB. So ROW_FORMAT isn't related.

[kou]

@tsuchm I tried the following on clean Debian GNU/Linux stretch. Could you try the following and show the log?

$ RAILS_ENV=production bin/rails redmine:plugins:migrate NAME=full_text_search VERSION=0
== 20190925074645 AddNameIndexToFtsTags: reverting ============================
-- remove_index(:fts_tags, {:column=>:name})
   -> 0.0034s
== 20190925074645 AddNameIndexToFtsTags: reverted (0.0053s) ===================

== 20190807085000 CreateFtsQueryExpansions: reverting =========================
-- drop_table(:fts_query_expansions, {:options=>"ENGINE=Mroonga"})
   -> 0.0051s
== 20190807085000 CreateFtsQueryExpansions: reverted (0.0058s) ================

== 20190728022920 MroongaAddSourceTypeIdIndexToFtsTargets: reverting ==========
-- remove_index(:fts_targets, {:column=>:source_type_id})
   -> 0.0040s
== 20190728022920 MroongaAddSourceTypeIdIndexToFtsTargets: reverted (0.0049s) =

== 20190603061606 CreateFtsTargets: reverting =================================
-- drop_table(:fts_targets, {:options=>"ENGINE=Mroonga DEFAULT CHARSET=utf8mb4"})
   -> 0.0125s
== 20190603061606 CreateFtsTargets: reverted (0.0138s) ========================

== 20190603061445 CreateFtsTags: reverting ====================================
-- drop_table(:fts_tags, {:options=>"ENGINE=Mroonga"})
   -> 0.0036s
== 20190603061445 CreateFtsTags: reverted (0.0044s) ===========================

== 20190603061110 CreateFtsTagTypes: reverting ================================
-- drop_table(:fts_tag_types)
   -> 0.0044s
== 20190603061110 CreateFtsTagTypes: reverted (0.0052s) =======================

== 20190603060948 CreateFtsTypes: reverting ===================================
-- drop_table(:fts_types)
   -> 0.0039s
== 20190603060948 CreateFtsTypes: reverted (0.0047s) ==========================

== 20190603054615 DropSearcherRecords: reverting ==============================
-- create_table(:searcher_records)
   -> 0.2333s
== 20190603054615 DropSearcherRecords: reverted (0.2336s) =====================

== 20190226093843 AddMissingIndexesToSearcherRecords: reverting ===============
== 20190226093843 AddMissingIndexesToSearcherRecords: reverted (0.0000s) ======

== 20190226093842 ExtendContentSize: reverting ================================
== 20190226093842 ExtendContentSize: reverted (0.0000s) =======================

== 20170810052427 AddIndexToIssueContents: reverting ==========================
== 20170810052427 AddIndexToIssueContents: reverted (0.0000s) =================

== 20170810045914 CreateIssueContents: reverting ==============================
-- drop_table(:issue_contents, {:options=>"ENGINE=Mroonga DEFAULT CHARSET=utf8mb4", :id=>:integer})
   -> 0.0031s
== 20170810045914 CreateIssueContents: reverted (0.0038s) =====================

== 20170630075028 AddIndexToSearcherRecords: reverting ========================
== 20170630075028 AddIndexToSearcherRecords: reverted (0.0000s) ===============

== 20170630075027 LoadCommentsFromChangesets: reverting =======================
== 20170630075027 LoadCommentsFromChangesets: reverted (0.0000s) ==============

== 20170630063757 CopyRecordsToSearcherRecords: reverting =====================
== 20170630063757 CopyRecordsToSearcherRecords: reverted (0.0000s) ============

== 20170630063657 CreateSearcherRecords: reverting ============================
-- drop_table(:searcher_records, {:options=>"ENGINE=Mroonga", :id=>:integer})
   -> 0.0213s
== 20170630063657 CreateSearcherRecords: reverted (0.0220s) ===================

== 20170630063557 EnablePgroonga: reverting ===================================
== 20170630063557 EnablePgroonga: reverted (0.0001s) ==========================

$ RAILS_ENV=production bin/rails redmine:plugins:migrate NAME=full_text_search
== 20170630063557 EnablePgroonga: migrating ===================================
== 20170630063557 EnablePgroonga: migrated (0.0001s) ==========================

== 20170630063657 CreateSearcherRecords: migrating ============================
-- create_table(:searcher_records, {:options=>"ENGINE=Mroonga", :id=>:integer})
   -> 0.0762s
== 20170630063657 CreateSearcherRecords: migrated (0.0766s) ===================

== 20170630063757 CopyRecordsToSearcherRecords: migrating =====================
== 20170630063757 CopyRecordsToSearcherRecords: migrated (0.0000s) ============

== 20170630075027 LoadCommentsFromChangesets: migrating =======================
== 20170630075027 LoadCommentsFromChangesets: migrated (0.0000s) ==============

== 20170630075028 AddIndexToSearcherRecords: migrating ========================
== 20170630075028 AddIndexToSearcherRecords: migrated (0.0000s) ===============

== 20170810045914 CreateIssueContents: migrating ==============================
-- create_table(:issue_contents, {:options=>"ENGINE=Mroonga DEFAULT CHARSET=utf8mb4", :id=>:integer})
   -> 0.0109s
== 20170810045914 CreateIssueContents: migrated (0.0114s) =====================

== 20170810052427 AddIndexToIssueContents: migrating ==========================
== 20170810052427 AddIndexToIssueContents: migrated (0.0000s) =================

== 20190226093842 ExtendContentSize: migrating ================================
== 20190226093842 ExtendContentSize: migrated (0.0000s) =======================

== 20190226093843 AddMissingIndexesToSearcherRecords: migrating ===============
== 20190226093843 AddMissingIndexesToSearcherRecords: migrated (0.0000s) ======

== 20190603054615 DropSearcherRecords: migrating ==============================
-- drop_table(:searcher_records)
   -> 0.0260s
== 20190603054615 DropSearcherRecords: migrated (0.0263s) =====================

== 20190603060948 CreateFtsTypes: migrating ===================================
-- create_table(:fts_types)
   -> 0.0070s
== 20190603060948 CreateFtsTypes: migrated (0.0073s) ==========================

== 20190603061110 CreateFtsTagTypes: migrating ================================
-- create_table(:fts_tag_types)
   -> 0.0069s
== 20190603061110 CreateFtsTagTypes: migrated (0.0072s) =======================

== 20190603061445 CreateFtsTags: migrating ====================================
-- create_table(:fts_tags, {:options=>"ENGINE=Mroonga"})
   -> 0.0074s
== 20190603061445 CreateFtsTags: migrated (0.0079s) ===========================

== 20190603061606 CreateFtsTargets: migrating =================================
-- create_table(:fts_targets, {:options=>"ENGINE=Mroonga DEFAULT CHARSET=utf8mb4"})
   -> 0.0375s
== 20190603061606 CreateFtsTargets: migrated (0.0385s) ========================

== 20190728022920 MroongaAddSourceTypeIdIndexToFtsTargets: migrating ==========
-- add_index(:fts_targets, :source_type_id)
   -> 0.0065s
== 20190728022920 MroongaAddSourceTypeIdIndexToFtsTargets: migrated (0.0068s) =

== 20190807085000 CreateFtsQueryExpansions: migrating =========================
-- create_table(:fts_query_expansions, {:options=>"ENGINE=Mroonga"})
   -> 0.0167s
== 20190807085000 CreateFtsQueryExpansions: migrated (0.0170s) ================

== 20190925074645 AddNameIndexToFtsTags: migrating ============================
-- add_index(:fts_tags, :name, {:type=>"fulltext", :comment=>"NORMALIZER 'NormalizerNFKC121'"})
   -> 0.0050s
== 20190925074645 AddNameIndexToFtsTags: migrated (0.0054s) ===================

$ RAILS_ENV=production bin/rails console
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.configurations[Rails.env]["encoding"]
=> "utf8mb4"
irb(main):002:0> FullTextSearch::Target.new(source_id: 1, source_type_id: 1, project_id: 1, content: "\xF2\x82\x9E\xAA").save
   (0.2ms)  SET NAMES utf8mb4,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
   (0.4ms)  SHOW VARIABLES LIKE 'mroonga_libgroonga_version';

   (0.4ms)  SHOW VARIABLES LIKE 'mroonga_libgroonga_version';

   (0.2ms)  BEGIN
  FullTextSearch::Target Create (1.6ms)  INSERT INTO `fts_targets` (`source_id`, `source_type_id`, `project_id`, `content`) VALUES (1, 1, 1, '򂞪')
   (0.1ms)  COMMIT
=> true
irb(main):003:0> 

[kou]

I think that this is no longer a problem for the reporter.