search

clear-code / redmine_full_text_search

Full text search for Redmine
MIT License
61 stars 24 forks source link

Duplicate key error in searcher_records table. #62

Closed okojoalg closed 5 years ago

okojoalg commented 5 years ago

When migrating full_text_search, the following error was displayed.

redmine              | Migrating plugins. Please be patient, this could take a while...
redmine              | rake aborted!
redmine              | StandardError: An error has occurred, all later migrations canceled:
redmine              |
redmine              | Mysql2::Error: Duplicate entry '1073-CustomValue' for key 'index_searcher_records_unique':     INSERT INTO searcher_records(original_id, original_type, project_id, project_name, original_created_on, original_updated_on, value, custom_field_id)
redmine              |     SELECT base.id, 'CustomValue', p.id, p.name, NULL, NULL, base.value, base.custom_field_id FROM custom_values AS base
redmine              |      JOIN custom_fields AS f ON (base.custom_field_id = f.id)
redmine              |     JOIN projects AS p ON (base.customized_id = p.id)
redmine              |  WHERE searchable = true;
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `block in query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `handle_interrupt'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `block in execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.11/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/mysql2_adapter.rb:217:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:665:in `block in method_missing'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `block in say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:654:in `method_missing'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:124:in `load_custom_values'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:33:in `block (2 levels) in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:511:in `up'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:4:in `block in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `block in reversible'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:746:in `execute_block'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `reversible'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:3:in `change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:608:in `exec_migration'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:592:in `block (2 levels) in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:591:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:590:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:768:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1023:in `block in execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1071:in `ddl_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1022:in `execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:984:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `each'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:823:in `up'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:801:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:481:in `migrate_plugin'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:453:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:467:in `block in migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `each'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `migrate'
redmine              | /home/redmine/redmine/lib/tasks/redmine.rake:135:in `block (3 levels) in '
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/rake-12.3.2/exe/rake:27:in `'
redmine              |
redmine              | Caused by:
redmine              | ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry '1073-CustomValue' for key 'index_searcher_records_unique':     INSERT INTO searcher_records(original_id, original_type, project_id, project_name, original_created_on, original_updated_on, value, custom_field_id)
redmine              |     SELECT base.id, 'CustomValue', p.id, p.name, NULL, NULL, base.value, base.custom_field_id FROM custom_values AS base
redmine              |      JOIN custom_fields AS f ON (base.custom_field_id = f.id)
redmine              |     JOIN projects AS p ON (base.customized_id = p.id)
redmine              |  WHERE searchable = true;
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `block in query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `handle_interrupt'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `block in execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.11/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/mysql2_adapter.rb:217:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:665:in `block in method_missing'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `block in say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:654:in `method_missing'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:124:in `load_custom_values'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:33:in `block (2 levels) in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:511:in `up'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:4:in `block in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `block in reversible'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:746:in `execute_block'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `reversible'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:3:in `change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:608:in `exec_migration'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:592:in `block (2 levels) in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:591:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:590:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:768:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1023:in `block in execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1071:in `ddl_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1022:in `execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:984:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `each'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:823:in `up'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:801:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:481:in `migrate_plugin'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:453:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:467:in `block in migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `each'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `migrate'
redmine              | /home/redmine/redmine/lib/tasks/redmine.rake:135:in `block (3 levels) in '
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/rake-12.3.2/exe/rake:27:in `'
redmine              |
redmine              | Caused by:
redmine              | Mysql2::Error: Duplicate entry '1073-CustomValue' for key 'index_searcher_records_unique'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `_query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:120:in `block in query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `handle_interrupt'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/mysql2-0.4.10/lib/mysql2/client.rb:119:in `query'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `block in execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:484:in `block in log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.11/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_adapter.rb:478:in `log'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:317:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/mysql2_adapter.rb:217:in `execute'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:665:in `block in method_missing'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `block in say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:634:in `say_with_time'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:654:in `method_missing'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:124:in `load_custom_values'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:33:in `block (2 levels) in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:511:in `up'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:4:in `block in change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `block in reversible'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:746:in `execute_block'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:547:in `reversible'
redmine              | /home/redmine/redmine/plugins/full_text_search/db/migrate/20170630063757_copy_records_to_searcher_records.rb:3:in `change'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:608:in `exec_migration'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:592:in `block (2 levels) in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:591:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:590:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:768:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1023:in `block in execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1071:in `ddl_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:1022:in `execute_migration_in_transaction'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:984:in `block in migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `each'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:980:in `migrate'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:823:in `up'
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.11/lib/active_record/migration.rb:801:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:481:in `migrate_plugin'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:453:in `migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:467:in `block in migrate'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `each'
redmine              | /home/redmine/redmine/lib/redmine/plugin.rb:466:in `migrate'
redmine              | /home/redmine/redmine/lib/tasks/redmine.rake:135:in `block (3 levels) in '
redmine              | /home/redmine/redmine/vendor/bundle/ruby/2.3.0/gems/rake-12.3.2/exe/rake:27:in `'

The result of the subquery of the insert statement is as follows.

mysql>     SELECT base.id, 'CustomValue', p.id, p.name, NULL, NULL, base.value, base.custom_field_id FROM custom_values AS base
    ->      JOIN custom_fields AS f ON (base.custom_field_id = f.id)
    ->     JOIN projects AS p ON (base.customized_id = p.id)
    ->  WHERE searchable = true;
+------+-------------+----+--------------+------+------+-------+-----------------+
| id   | CustomValue | id | name         | NULL | NULL | value | custom_field_id |
+------+-------------+----+--------------+------+------+-------+-----------------+
| 1073 | CustomValue |  5 | XXXXXXXX     | NULL | NULL |       |               1 |
+------+-------------+----+--------------+------+------+-------+-----------------+
1 row in set (2.93 sec)

Is there a solution?

kou commented 5 years ago

Could you show the output of the following SELECT?

select * from custom_fields where id = 1073;
kou commented 5 years ago

I could reproduce this. I've fixed this on mater.

okojoalg commented 5 years ago

Thank you. kou but there was another problem. #63