Bug #18067
closedActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry '36114' for key 'PRIMARY':
Description
Hello,
If I try to Migrate my DB from Postgres to MysQL I get an error.
foreman-rake db:migrate RAILS_ENV=production
foreman-rake db:migrate RAILS_ENV=development
foreman-rake db:convert:prod2dev
database.yml
production:
adapter: postgresql
database: foreman
username: foreman
password: "password"
pool: 5
development:
adapter: mysql2
database: foreman
username: foreman
password: "password"
pool: 5
- Invoke db:convert:prod2dev (first_time)
- Invoke environment (first_time)
- Execute environment
- Execute db:convert:prod2dev
Converting audits...rake aborted!
ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry '36114' for key 'PRIMARY': INSERT INTO `audits` (`id`, `auditable_id`, `auditable_type`, `username`, `action`, `audited_changes`, `version`, `created_at`, `remote_address`, `auditable_name`) VALUES (36114, 152, 'Host', 'API Admin', 'update', '---\nprimary_interface:\n- eth0_sip\n- eth0\n', 526, '2016-06-18 09:51:55', '192.168.0.1', 'test.example')
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `query'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `block in execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/mysql2_adapter.rb:248:in `exec_insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:108:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation.rb:64:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:524:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/counter_cache.rb:139:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/locking/optimistic.rb:75:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods/dirty.rb:133:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:306:in `block in create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:88:in `_run_callbacks__'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:778:in `_run_create_callbacks'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:306:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/timestamp.rb:57:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:504:in `create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:302:in `block in create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:88:in `__run_callbacks__'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:778:in `_run_save_callbacks'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:302:in `create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:120:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/validations.rb:37:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods/dirty.rb:21:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:286:in `block (2 levels) in save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:351:in `block in with_transaction_returning_status'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:211:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:348:in `with_transaction_returning_status'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:286:in `block in save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:301:in `rollback_active_record_state!'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:285:in `save'
/usr/share/foreman/lib/tasks/convert.rake:140:in `block (6 levels) in <top (required)>'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation/delegation.rb:46:in `each'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation/delegation.rb:46:in `each'
/usr/share/foreman/lib/tasks/convert.rake:125:in `block (5 levels) in <top (required)>'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
/usr/share/foreman/lib/tasks/convert.rake:124:in `block (4 levels) in <top (required)>'
/usr/share/foreman/lib/tasks/convert.rake:83:in `each'
/usr/share/foreman/lib/tasks/convert.rake:83:in `block (3 levels) in <top (required)>'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/opt/rh/rh-ruby22/root/usr/share/ruby/monitor.rb:211:in `mon_synchronize'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/opt/rh/rh-ruby22/root/usr/bin/rake:33:in `<main>'
Mysql2::Error: Duplicate entry '36114' for key 'PRIMARY'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `query'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `block in execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_adapter.rb:472:in `block in log'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_adapter.rb:466:in `log'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/mysql2_adapter.rb:248:in `exec_insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:108:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation.rb:64:in `insert'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:524:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/counter_cache.rb:139:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/locking/optimistic.rb:75:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods/dirty.rb:133:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:306:in `block in create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:88:in `_run_callbacks__'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:778:in `_run_create_callbacks'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:306:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/timestamp.rb:57:in `_create_record'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:504:in `create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:302:in `block in create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:88:in `__run_callbacks__'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/callbacks.rb:778:in `_run_save_callbacks'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/callbacks.rb:302:in `create_or_update'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:120:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/validations.rb:37:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods/dirty.rb:21:in `save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:286:in `block (2 levels) in save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:351:in `block in with_transaction_returning_status'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:211:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:348:in `with_transaction_returning_status'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:286:in `block in save'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:301:in `rollback_active_record_state!'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:285:in `save'
/usr/share/foreman/lib/tasks/convert.rake:140:in `block (6 levels) in <top (required)>'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation/delegation.rb:46:in `each'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation/delegation.rb:46:in `each'
/usr/share/foreman/lib/tasks/convert.rake:125:in `block (5 levels) in <top (required)>'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
/usr/share/foreman/lib/tasks/convert.rake:124:in `block (4 levels) in <top (required)>'
/usr/share/foreman/lib/tasks/convert.rake:83:in `each'
/usr/share/foreman/lib/tasks/convert.rake:83:in `block (3 levels) in <top (required)>'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/opt/rh/rh-ruby22/root/usr/share/ruby/monitor.rb:211:in `mon_synchronize'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/opt/rh/rh-ruby22/root/usr/share/gems/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/opt/rh/rh-ruby22/root/usr/bin/rake:33:in `<main>'
Tasks: TOP => db:convert:prod2dev
Regards,
Florian
Updated by Dominic Cleal over 8 years ago
Is the target audits table definitely empty before you run prod2dev?
Are there two entries for key 36114 in the source database's audit table?
It's unlikely that the rake task is importing the same entry twice - more likely one of the above.
Updated by Florian Faltermeier over 8 years ago
Dominic Cleal wrote:
Is the target audits table definitely empty before you run prod2dev?
Are there two entries for key 36114 in the source database's audit table?
It's unlikely that the rake task is importing the same entry twice - more likely one of the above.
Hi Dominic,
thank you for your fast response. I've checked the source database, there is just one entry with this key. The target audits table isn't empty.
Some entries would be created during the foreman-rake db:migrate RAILS_ENV=development task.
Regards,
Florian
Updated by Florian Faltermeier over 8 years ago
Florian Faltermeier wrote:
Dominic Cleal wrote:
Is the target audits table definitely empty before you run prod2dev?
Are there two entries for key 36114 in the source database's audit table?
It's unlikely that the rake task is importing the same entry twice - more likely one of the above.
Hi Dominic,
thank you for your fast response. I've checked the source database, there is just one entry with this key. The target audits table isn't empty.
Some entries would be created during the foreman-rake db:migrate RAILS_ENV=development task.Regards,
Florian
I've cleaned up the whole audits table on the target db but the error still occurs.
Updated by Florian Faltermeier over 8 years ago
Florian Faltermeier wrote:
Florian Faltermeier wrote:
Dominic Cleal wrote:
Is the target audits table definitely empty before you run prod2dev?
Are there two entries for key 36114 in the source database's audit table?
It's unlikely that the rake task is importing the same entry twice - more likely one of the above.
Hi Dominic,
thank you for your fast response. I've checked the source database, there is just one entry with this key. The target audits table isn't empty.
Some entries would be created during the foreman-rake db:migrate RAILS_ENV=development task.Regards,
FlorianI've cleaned up the whole audits table on the target db but the error still occurs.
Hi dominic,
I've checked the rows in the source db audits table. 73329 rows.
After increasing the PAGE_SIZE it's working. (trying to avoid pagination) e.g. PAGE_SIZE = 1000000
Next break:
Converting logs...rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: DELETE FROM `logs`
source database logs table:
select count(*) from logs;
count
---------
3732574
Can I delete the logs table?
Regards,
Florian
Updated by Florian Faltermeier over 7 years ago
- Status changed from New to Resolved
Updated by Ivan Necas over 7 years ago
Florian: could you put some notice on how the issue was resolved for the future users that might potentially run into the issue? Thanks