547999 - "An unexpected error has occurred!" when migrating Live Chat account on staging

Status: RESOLVED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Matthew Middleton (:zzxc)]

Attempting to migrate a valid Live Chat account on https://support-stage.mozilla.com/tiki-livechat_migration.php is resulting in a "An unexpected error has occurred!" error message.  (The most likely reason for this is a missing livechat_id column in the users_users table)

Comment 1

[Derek Moore [:dmoore]]

You're correct, there is no livechat_id in the users_users table on staging. It's also not present on production.

Let us know if there is a required database schema change to support livechat migration.

Comment 2

[Matthew Middleton (:zzxc)]

This was on staging before the branch switch last week, so the column did exist there.  The SQL to add it should be:

ALTER TABLE users_users ADD COLUMN livechat_id varchar(64);

Once this is done, the migration script (bug 541983) needs to be run again, as the column was dropped.

Comment 3

[Derek Moore [:dmoore]]

Ah, this was lost when we synced from the latest production database in Bug 547757.

We've recreated the column in staging:

mysql> ALTER TABLE users_users ADD COLUMN livechat_id varchar(64);
Query OK, 159798 rows affected (3.51 sec)
Records: 159798  Duplicates: 0  Warnings: 0


Let me know if there is anything else required on the IT side.

Comment 4

[Matthew Middleton (:zzxc)]

Since this column was lost, the automatic migration needs to be run again:
1. Import the latest Openfire production database to chat-support-stage
2. Run tiki-livechat_migration.php again, following https://bugzilla.mozilla.org/show_bug.cgi?id=541982#c15

Comment 5

[Derek Moore [:dmoore]]

Database from 02/22 imported (from the same dump as latest SUMO sync)

livechat_id zeroed:

mysql> UPDATE users_users SET livechat_id=NULL;
Query OK, 0 rows affected (2.71 sec)
Rows matched: 159798  Changed: 0  Warnings: 0


migration script run:

1a. Updating Tiki database for same-email users... 330 distinct emails... Done.
1c. Removing them from the Openfire database... Done.
3.  Creating livechat_id for non-conflicting TikiWiki accounts... 159468 rows...

Comment 6

[Matthew Middleton (:zzxc)]

Filling out the migration script on staging now results in:

Success!
Migration has completed successfully. You may now sign in to live chat with your newly created livechat ID.

->Fixed