Page MenuHomePhabricator

Tgr unable to login on Horizon
Closed, ResolvedPublic

Description

I cannot login on Horizon; I get "An error occurred authenticating. Please try again later." (This is a different message from what I get with a wrong password, but identical to what I get with a wrong TOTP token. My token works on wikitech though.) I registered to Labs in the short and unfortunate period when it had a realname policy, so my LDAP username is Gergő Tisza; the accent caused problems with other logins in the past, it might be related to that.

Event Timeline

Tgr created this task.Apr 2 2016, 4:57 PM
Restricted Application added a project: Cloud-Services. · View Herald TranscriptApr 2 2016, 4:57 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Clock drift on your phone?

If so, it's a known issue, with a fix proposed, not sure if it was deployed yet as @chasemp was having the same issue yesterday

I've also heard of @chasemp (Rush) and @Smalyshev (Smalyshev) having difficulty getting in

Reedy added a comment.Apr 2 2016, 5:00 PM

https://gerrit.wikimedia.org/r/#/c/281033/ was the fix

It was merged, so I presume it's deployed...

Tgr added a comment.Apr 2 2016, 5:29 PM

Clock drift on your phone?

It's within 5 sec of time.is.

It worked for me finally after realizing phabricator 2FA token doesn't work anymore and I need a different one set up (it used to work before).

Phabricator 2FA has never been linked to Wikitech/Horizon @Smalyshev

chasemp triaged this task as Normal priority.Apr 4 2016, 1:38 PM
Andrew added a subscriber: Andrew.May 17 2016, 7:26 PM

(keystone.identity.core): 2016-05-17 19:22:54,235 DEBUG Local ID: tgr
(keystone.common.wsgi): 2016-05-17 19:22:54,240 ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Traceback (most recent call last):

File "/usr/lib/python2.7/dist-packages/keystone/common/wsgi.py", line 239, in __call__
  result = method(context, **params)
File "/usr/lib/python2.7/dist-packages/keystone/auth/controllers.py", line 377, in authenticate_for_token
  self.authenticate(context, auth_info, auth_context)
File "/usr/lib/python2.7/dist-packages/keystone/auth/controllers.py", line 502, in authenticate
  auth_context)
File "/usr/lib/python2.7/dist-packages/keystone/auth/plugins/wmtotp.py", line 89, in authenticate
  cur.execute(sql, (user_info.user_ref['name'], ))
File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 491, in execute
  self._handle_result(self._connection.cmd_query(stmt))
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 635, in cmd_query
  statement))
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 553, in _handle_result
  raise errors.get_exception(packet)

DatabaseError: 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
(eventlet.wsgi.server): 2016-05-17 19:22:54,243 INFO 208.80.154.147 - - [17/May/2016 19:22:54] "POST /v3/auth/tokens HTTP/1.1" 500 405 0.078332
(keystone.middleware.core): 2016-05-17 19:22:59,773 DEBUG Auth token not in the request header. Will not build auth context.

Andrew renamed this task from Unable to login on Horizon to TGR unable to login on Horizon.May 18 2016, 7:50 PM
Andrew assigned this task to jcrespo.

Chris suggests that something should be done to the 'users' table on silver. So... over to you, Jaime.

jcrespo removed jcrespo as the assignee of this task.May 19 2016, 6:13 AM
jcrespo added a project: DBA.
jcrespo added a subscriber: jcrespo.

"Chris suggests that something should be done to the 'users' table on silver"

What should be done? I have 0 context here.

Please do not assign it to me, apply the DBA tag and someone on the team will take care.

Andrew added a subscriber: csteipp.May 19 2016, 4:17 PM

On labswiki, the user table was create at a time when the collation wasn't explicitly set, so it's

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `user_real_name` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `user_password` tinyblob NOT NULL,
  `user_newpassword` tinyblob NOT NULL,
  `user_newpass_time` binary(14) DEFAULT NULL,
  `user_email` tinytext NOT NULL,
  `user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `user_email_authenticated` binary(14) DEFAULT NULL,
  `user_email_token` binary(32) DEFAULT NULL,
  `user_email_token_expires` binary(14) DEFAULT NULL,
  `user_registration` binary(14) DEFAULT NULL,
  `user_editcount` int(11) DEFAULT NULL,
  `user_password_expires` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_email_token` (`user_email_token`),
  KEY `user_email` (`user_email`(50),`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=8054 DEFAULT CHARSET=latin1

On a modern install of mediawiki, that would be

CREATE TABLE `user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_real_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `user_password` tinyblob NOT NULL,
  `user_newpassword` tinyblob NOT NULL,
  `user_newpass_time` binary(14) DEFAULT NULL,
  `user_email` tinytext NOT NULL,
  `user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `user_email_authenticated` binary(14) DEFAULT NULL,
  `user_email_token` binary(32) DEFAULT NULL,
  `user_email_token_expires` binary(14) DEFAULT NULL,
  `user_registration` binary(14) DEFAULT NULL,
  `user_editcount` int(11) DEFAULT NULL,
  `user_password_expires` binary(14) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_email_token` (`user_email_token`),
  KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB AUTO_INCREMENT=239 DEFAULT CHARSET=utf8

So both the default charset and the collation on user_name are utf8 explicitly.

I *think* updating the labswiki table definition (if that's possible) will fix the problem.

I have converted labswiki to CHARSET=utf8 COLLATE=utf8_bin. However, I am skeptical about this working because:

  • The whole database uses latin1 charset/collation, which means this only changed 1 table, and I do not know what problems that can have for other tables
  • utf8 on MySQL means 3-byte UTF-8, which means it doesn't allow all kinds of characters. 4-byte UTF-8 is utf8mb4, but even I am unsure it is up to date with the latest unicode standard.

Gergő should check now and we will see if that helps; also check for other potential breakages on the site.

<tgr> jynus: I still get "An error occurred authenticating. Please try again later
MariaDB WIKITECH localhost labswiki > SELECT * FROM user WHERE user_name = 'Tgr';
Empty set (0.00 sec)
Tgr added a comment.May 26 2016, 10:16 AM

My shell name is tgr but my username should be Gergő Tisza (assuming this is the MediaWiki user table).

oh, I can see it now- I assumed Tgr or tgr was the user_name and Gergő Tisza the real name- I can see now that it is "corrupt":

392 | Gergő Tisza          | Gergő Tisza

Let me fix it manually to see if it works, if not, may need to be deleted completely.

Maybe you can try one more time?

MariaDB WIKITECH localhost labswiki > SELECT * FROM user WHERE user_id=392;
+---------+--------------+----------------+---------------+------------------+-------------------+----------------------+----------------+-----------
| user_id | user_name    | user_real_name 
+---------+--------------+----------------+---------------+------------------+-------------------+----------------------+----------------+-----------
|     392 | Gergő Tisza  | Gergő Tisza    
+---------+--------------+----------------+---------------+------------------+-------------------+----------------------+----------------+-----------

Can we identify all other now-broken users?

Can we identify all other now-broken users?

Difficult, in an unreliable way- maybe running a script that detects typical utf8->latin1 character errors.

I have not yet 1''% clear that this will work for Gergő.

I have created a list of users here with a very simplistic list of non-ASCII characters:

{P3180}

As I am not sure if listing users from the database is private data, I have for now put it as NDA-only.

Tgr added a comment.May 26 2016, 10:51 AM

I can log in now. Thanks @jcrespo!

Can we identify all other now-broken users?

Just need to do something like UPDATE user SET user_name = CONVERT(CONVERT(CONVERT(user_name USING latin1) USING binary) USING utf8) WHERE user_id != 392, right?

Granted, there might be so few affected users (Labs had a "real name for username" policy, but only for a very short time) that it might be easier to do by hand. In that case, just look for names matching the [\x80-\xFF]{2,} regex - that's how the raw UTF8 encoding of non-ASCII charcters look.

jcrespo added a comment.EditedMay 26 2016, 10:56 AM

See my above list, I used ^[a-zA-Z0-9 \-\.\=]*$ and that probably had a low false positive rate, given the wiki doesn't have a lot of users.

I will check your suggestion to see how it works.

This is the result of applying Tgr's convertion back and forth to fix the latinized utf8 characters. I would really want 1 or more reviewers, as it could have a really bad impact for users on this wiki, and I may not know if the non-latin alphabets are correct (the latin ones seem correct to me):
{P3181}

Tgr added a comment.May 26 2016, 12:01 PM

The extended latin ones are correct. The Japanese/Chinese/Arabian/Hindi/Russian ones look sane (I can't read those alphabets, but they don't have obvious garbage characters).

The ones that look strange are 1272 (also, listed as an admin here, but says the account does not exist?) and 916 (with the number jumping to the front - although that's probably just the browser deciding this is an RTL alphabet). But if all the others are OK, it's unlikely that those would not.

Nemo_bis renamed this task from TGR unable to login on Horizon to Tgr unable to login on Horizon.May 26 2016, 12:03 PM
jcrespo added a comment.EditedMay 26 2016, 12:22 PM

As I mentioned, MySQL's utf8 is not true UTF-8, maybe it is safer to use real UTF-8 (utf8mb4) for characters beyond the BMP? Or binary directly, as we do with the main wikis.

jcrespo added a comment.EditedMay 26 2016, 12:28 PM

As 3 people have already said this looks sane "eg better than what we have now", I will do this now, and maybe we can reopen if some issues arise soon.

Given the limited scope of labswiki, serving as a portal and as documentation mainly in English for tech-savy users, maybe it is not worth fixing every potential little thing (which may take a lot of effort), so fix this now for 99% of the users and wait for users to provide feedback is there are more issues in the future.

I have a backup of the old table, just in case.

Mentioned in SAL [2016-05-26T12:31:33Z] <jynus> updating user table on labswiki to fix incorrect encoding T131630

UPDATE user SET user_name = CONVERT(CONVERT(CONVERT(user_name USING latin1) USING binary) USING utf8) WHERE user_id != 392 and user_name NOT RLIKE '^[a-zA-Z0-9 \-\.\=]*$' ORDER BY user_id;
Query OK, 94 rows affected (0.12 sec)
Rows matched: 95  Changed: 94  Warnings: 0

I have added a message to the talk page of all affected users (except the obvious vandal ones).

jcrespo added a comment.EditedMay 26 2016, 1:54 PM

Thank you, @valhallasw. Please report if some user contacts you back saying that they can now log in / they still cannot log in.

jcrespo closed this task as Resolved.May 30 2016, 9:42 AM
jcrespo claimed this task.

I will close this for now, the title task (Tgr unable to login on Horizon) is resolved.

Thank you @jynus