migration error on DrupalUsers update

[jywarren]

In trying to publish #2257 to production, i got this error:

Caused by:
Mysql2::Error: Incorrect string value: '\xE3\x83\x88\xE3\x83\xAA...' for column 'username' at row 1
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `_query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:80:in `block in query'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `handle_interrupt'
/usr/local/bundle/gems/mysql2-0.3.21/lib/mysql2/client.rb:79:in `query'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:218:in `block in execute'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_adapter.rb:590:in `block in log'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:218:in `execute'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/mysql/database_statements.rb:31:in `execute'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:225:in `execute_and_free'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/mysql/database_statements.rb:36:in `exec_query'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/database_statements.rb:92:in `exec_insert'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/database_statements.rb:124:in `insert'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/query_cache.rb:17:in `insert'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/relation.rb:65:in `insert'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/persistence.rb:572:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/counter_cache.rb:128:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/locking/optimistic.rb:75:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/attribute_methods/dirty.rb:123:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/callbacks.rb:302:in `block in _create_record'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:126:in `call'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:506:in `block (2 levels) in compile'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:455:in `call'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:101:in `__run_callbacks__'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:750:in `_run_create_callbacks'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/callbacks.rb:302:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/timestamp.rb:68:in `_create_record'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/persistence.rb:548:in `create_or_update'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/callbacks.rb:298:in `block in create_or_update'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:126:in `call'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:506:in `block (2 levels) in compile'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:455:in `call'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:101:in `__run_callbacks__'
/usr/local/bundle/gems/activesupport-5.0.6/lib/active_support/callbacks.rb:750:in `_run_save_callbacks'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/callbacks.rb:298:in `create_or_update'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/persistence.rb:129:in `save'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/validations.rb:44:in `save'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/attribute_methods/dirty.rb:22:in `save'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:319:in `block (2 levels) in save'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:395:in `block in with_transaction_returning_status'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `block in transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/transaction.rb:189:in `within_new_transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/database_statements.rb:232:in `transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:211:in `transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:392:in `with_transaction_returning_status'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:319:in `block in save'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/transactions.rb:334:in `rollback_active_record_state!'

...

/app/lib/authlogic_openid/authlogic_openid/acts_as_authentic.rb:74:in `save'
/app/app/models/drupal_user.rb:196:in `migrate'
/app/db/migrate/20180614000000_add_user_for_orphaned_drupal_user.rb:5:in `block in change'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/relation/delegation.rb:38:in `each'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/relation/delegation.rb:38:in `each'
/app/db/migrate/20180614000000_add_user_for_orphaned_drupal_user.rb:3:in `change'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:789:in `exec_migration'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:773:in `block (2 levels) in migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:772:in `block in migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `with_connection'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:771:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration/compatibility.rb:121:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:951:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1232:in `block in execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1302:in `ddl_transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1231:in `execute_migration_in_transaction'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1203:in `block in migrate_without_lock'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1202:in `each'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1202:in `migrate_without_lock'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1150:in `block in migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1319:in `with_advisory_lock'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1150:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:1006:in `up'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/migration.rb:984:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/tasks/database_tasks.rb:163:in `migrate'
/usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/railties/databases.rake:58:in `block (2 levels) in <top (required)>'
/usr/local/bundle/gems/rake-12.3.1/exe/rake:27:in `<top (required)>'

[jywarren]

I've left the server as-is after failing make redeploy-container -- not sure what to do next!

[jywarren]

@ViditChitkara @icarito I think i'll leave things as they are -- could we... remove the migration in a new PR and try again? @ViditChitkara can you check the logs at https://jenkins.laboratoriopublico.org/job/Plots-Unstable/ to see if this error came up there at all?

[icarito]

@jywarren I recently saw similar errors with the database of our etherpad instance. The solution in that case was to convert the entire database to utf8mb4 character set because MySQL has a faulty utf8 character set. The question is if this should be done manually or automatically in a migration. A migration would require coding the table alteration from Ruby.

I'd like to have a way to reliably reproduce the issue in staging in order to test a solution.

[icarito]

Just a note for debugging: I just checked and both production and staging have the same database configuration with respect to character sets and collation:
MariaDB [publiclab_production]> show variables like "character_set_database";

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.15 sec)

MariaDB [publiclab_production]> show variables like "collation_database";
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0.01 sec)

[jywarren]

But, to be clear, staging did not show this error? @ViditChitkara can you confirm?

https://jenkins.laboratoriopublico.org/job/Plots-Unstable/

[jywarren]

Is our easiest way forward to revert the migration in a new commit and just pretend we didn't deploy it, and debug further?

[ViditChitkara]

The build went well on unstable, it is odd that we got the error, I'll see something regarding this.

[ViditChitkara]

Yes, the easiest way out would be to revert the migration. According to me.

[jywarren]

OK cool - can you submit a new PR that removes the migration? Were you able to confirm that the unstable build on Jenkins did not have the same error -- can you link to and cite the log output to confirm? Thank you!

…

On Wed, Jun 20, 2018 at 10:09 AM Vidit ***@***.***> wrote: Yes, the easiest way out would be to revert the migration. According to me. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#2846 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJy-JrvGsi-xnlPwUYMQFhj3V-dgvks5t-leSgaJpZM4UsmUE> .

[ViditChitkara]

https://jenkins.laboratoriopublico.org/job/Plots-Unstable/150/

[ViditChitkara]

The link is given above, sure I'll remove the migration.

[ViditChitkara]

@jywarren, Just confirming, should I just remove the migration file concerned and reset the schema.rb.example's version??

[jywarren]

I think that's right. Let's see how Travis reacts.

…

On Wed, Jun 20, 2018, 10:31 AM Vidit ***@***.***> wrote: @jywarren <https://github.com/jywarren>, Just confirming, should I just remove the migration file concerned and reset the schema.rb.example's version?? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#2846 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJ87Kx7CHE6p5Ho4AwxcAOsS2PXVGks5t-lyGgaJpZM4UsmUE> .