543115 - Import script erroneously deleted all accounts except Matthew's

Status: VERIFIED FIXED

(support.mozilla.org Graveyard :: Chat, defect)

Description

[Stephen Donner [:stephend] Not actively reading bugmail]

While testing https://wiki.mozilla.org/Support/Live_Chat/SUMO_login/Testing, I noticed that the username I had just created via http://chat-support-stage.mozilla.com:9091/plugins/registration/sign-up.jsp wasn't being recognized.

The reason, Matthew said in IRC, is that "the import script deleted every account, even those that could not be imported."

This is blocking further testing.

(Matthew will clarify.)

Comment 1

[James Socol [:jsocol, :james]]

Which import script? Is this the script from bug 534351?

Comment 2

[Stephen Donner [:stephend] Not actively reading bugmail]

(In reply to comment #1)
> Which import script? Is this the script from bug 534351?

Matthew?  As per our Monday meeting, we were going to try to ship 1.5.1 tomorrow; do you have a status update?  (We're not going to make it, so we should reset our ETA.)

Comment 3

[Matthew Middleton (:zzxc)]

I tested this script in various cases, but the behavior from staging only seems to be reproduced when TikiWiki accounts have empty ("") e-mail fields.  After importing a clean copy of the Openfire and SUMO databases and upgrading to 1.5.1, running the script on sumotools resulted in only migrated accounts being deleted from Openfire.  We should try this again, making sure staging has a clean unmodified copy of the production databases.

I'm updating this script to check for any empty e-mail fields, and refuse to run if that's the case.  I'll be posting that patch in the next few minutes, so that we can test it.

Comment 4

[Matthew Middleton (:zzxc)]

To clarify, empty e-mail accounts only resulted in about 500 additional accounts being removed.  There were still ~1000 accounts left, in all cases, after the script had been run.

The staging problem seems to have been caused by an incomplete database.

Comment 5

[Matthew Middleton (:zzxc)]

Attachment: Match only valid e-mail addresses, with one '@'

This patch reduces the number of deleted rows from ~500 to the apparently correct number of 321.  All of the test accounts I used still existed after doing a test migration on sumotools.  A number of Openfire accounts have whitespace-only e-mail addresses, which seemed to be causing extra migrations.

Comment 6

[Paul Craciunoiu [:paulc]]

Comment on attachment 425054 [details] [diff] [review]
Match only valid e-mail addresses, with one '@'

>+        email IS NOT NULL AND email != '' AND email LIKE '%@%';
While we're at it, we may as well validate that there's only one '@' and a dot. How about adding this:
AND email REGEXP '[^@]+@[^@]+\\.[^@]+';
... instead of:
AND email LIKE '%@%';
On my test db, this excludes another few emails (4 emails, one e.g. is "vsimgak@live,ca")

James or Laura: thoughts? Should we also check if the mail was successfully sent (mail() returns true)

Comment 7

[Laura Thomson :laura]

Sounds good to me.  Re mail(), it can't hurt (but the mail may still not be delivered, true just means it was accepted by the mail server).

Comment 8

[Paul Craciunoiu [:paulc]]

Attachment: match email with mysql regex + output failures

Thanks Matthew for this. Since those accounts are erased, it's good to make sure emails are actually sent.

This patch does that, plus some cleanup. Patch line 43 removes an unnecessary replace. Now, when the mail() function fails, the respective accounts are not deleted.

Comment 9

[Laura Thomson :laura]

Comment on attachment 425066 [details] [diff] [review]
match email with mysql regex + output failures

failmail, lol

Comment 10

[Paul Craciunoiu [:paulc]]

(In reply to comment #9)
> failmail, lol
I spent years looking for a chance to use that name legitimately :)
r61773.

Time to verify remaining livechat bugs, I hope.
Matthew, Stephend: what's next?

Comment 11

[Stephen Donner [:stephend] Not actively reading bugmail]

Next steps are to:

* Have IT re-run the steps listed in the blocking bugs for bug 503215
* QA will then begin testing step-by-step, as per https://wiki.mozilla.org/Support/Live_Chat/SUMO_login/Testing; Matthew, we're going to need your help along the way, most likely.

Comment 12

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED; Vishal and I have tested pretty thorough, and the testing included previously created, migrated accounts just fine.