Page MenuHomePhabricator
Create Task
Maniphest T318047

Collation problems on labsdbaccounts results in newly created Toolforge tools unable to connect to MariaDB databases
Closed, ResolvedPublic
Actions

Description

Hi,

if i try to connect to the Database using the command

mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud

i get an error message as follows

ERROR 1045 (28000): Access denied for user 's55155'@'172.16.6.95' (using password: YES)

Is there anything i have to do differently, or do you need to create an account for the database?

Looking into my replica.my.cnf file, i see no entry for mysql, as in another tool account, just the client entry

[client]
user = s55155
password = ******

Details

SubjectRepoBranchLines +/-
maintain-dbusers: add missing collate to the account tableoperations/puppetproduction+3 -1
wmcs: maintain_dbusers: don't halt account population loop on errorsoperations/puppetproduction+62 -48
Customize query in gerrit

Event Timeline

Tidoni created this task.Sep 18 2022, 10:17 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 18 2022, 10:17 AM
Peachey88 added a project: cloud-services-team (Kanban).Sep 18 2022, 10:25 AM
taavi added a project: Data-Services.Sep 18 2022, 3:37 PM
Legoktm added subscribers: Enterprisey, Legoktm.Sep 19 2022, 12:04 AM

@Enterprisey complained about this yesterday too, for his new tool "fast-ec".

Legoktm added a comment.Sep 26 2022, 3:25 AM

And now it just bit me in my new rfa-voting-history tool...

tools.rfa-voting-history@tools-sgebastion-10:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.wikimedia.cloud
ERROR 1045 (28000): Access denied for user 's55165'@'172.16.6.95' (using password: YES)

wiki replicas don't work either;

tools.rfa-voting-history@tools-sgebastion-10:~$ sql enwiki
ERROR 1045 (28000): Access denied for user 's55165'@'10.64.37.27' (using password: YES)
Legoktm renamed this task from Tools Account (s55155) no acces to Database (tools.db.svc.wikimedia.cloud) to Newly created Toolforge tools unable to connect to MariaDB databases.Sep 26 2022, 3:25 AM
Legoktm added a comment.Sep 26 2022, 3:30 AM

fast-ec is still broken (created at least a week ago):

tools.fast-ec@tools-sgebastion-10:~$ sql enwiki
ERROR 1045 (28000): Access denied for user 's55157'@'10.64.37.27' (using password: YES)
aborrero subscribed.Sep 26 2022, 6:53 AM

I found this:

aborrero@labstore1004:~$ sudo journalctl -u maintain-dbusers
[..]
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]: Traceback (most recent call last):
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/local/sbin/maintain-dbusers", line 933, in <module>
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     main()
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/local/sbin/maintain-dbusers", line 922, in main
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     populate_new_accounts(config, "paws")
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/local/sbin/maintain-dbusers", line 549, in populate_new_accounts
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     mysql_hash(pwd),
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 166, in execute
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     result = self._query(query)
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 322, in _query
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     conn.query(q)
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 852, in query
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1053, in _read_query_result
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     result.read()
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1336, in read
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     first_packet = self.connection._read_packet()
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1010, in _read_packet
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     packet.check_error()
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 393, in check_error
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     err.raise_mysql_exception(self._data)
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:   File "/usr/lib/python3/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]:     raise errorclass(errno, errval)
Sep 26 01:37:27 labstore1004 maintain-dbusers[24271]: pymysql.err.IntegrityError: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")

This goes back to many days ago.

aborrero triaged this task as High priority.Sep 26 2022, 6:53 AM
aborrero moved this task from Inbox to Doing on the cloud-services-team (Kanban) board.
dcaro subscribed.Sep 26 2022, 8:07 AM

It seems that it's trying to add a new entry for user 'Inaki LL' and when updating the index it collides with an existing 'Iñaki LL', looking if this might be a collation issue (!= charset, see https://mariadb.com/kb/en/setting-character-sets-and-collations/).

Current collation is 'utf8mb4_general_ci', available ones for the current charset are:

mysql:labsdbaccounts@m5-master.eqiad.wmnet [labsdbaccounts]> show collation like 'utf8mb4%';
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4 |   45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |   46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
| utf8mb4_swedish_ci           | utf8mb4 |  232 |         | Yes      |       8 |
| utf8mb4_turkish_ci           | utf8mb4 |  233 |         | Yes      |       8 |
| utf8mb4_czech_ci             | utf8mb4 |  234 |         | Yes      |       8 |
| utf8mb4_danish_ci            | utf8mb4 |  235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci        | utf8mb4 |  236 |         | Yes      |       8 |
| utf8mb4_slovak_ci            | utf8mb4 |  237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci          | utf8mb4 |  238 |         | Yes      |       8 |
| utf8mb4_roman_ci             | utf8mb4 |  239 |         | Yes      |       8 |
| utf8mb4_persian_ci           | utf8mb4 |  240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci         | utf8mb4 |  241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci         | utf8mb4 |  242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci           | utf8mb4 |  243 |         | Yes      |       8 |
| utf8mb4_german2_ci           | utf8mb4 |  244 |         | Yes      |       8 |
| utf8mb4_croatian_mysql561_ci | utf8mb4 |  245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci       | utf8mb4 |  246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci        | utf8mb4 |  247 |         | Yes      |       8 |
| utf8mb4_croatian_ci          | utf8mb4 |  608 |         | Yes      |       8 |
| utf8mb4_myanmar_ci           | utf8mb4 |  609 |         | Yes      |       8 |
| utf8mb4_thai_520_w2          | utf8mb4 |  610 |         | Yes      |       4 |
| utf8mb4_general_nopad_ci     | utf8mb4 | 1069 |         | Yes      |       1 |
| utf8mb4_nopad_bin            | utf8mb4 | 1070 |         | Yes      |       1 |
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+
33 rows in set (0.00 sec)
dcaro added a comment.Sep 26 2022, 8:38 AM

Testing locally, using a non-case insensitive collation (one not ending in _ci) seems to do the trick, used utf8mb4_bin:

MariaDB [test1]> CREATE TABLE `account` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `mysql_username` varchar(255) NOT NULL,   `type` enum('user','tool','paws') DEFAULT NULL,   `username` varchar(255) NOT NULL,   `password_hash` binary(41) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `account_type` (`type`,`username`) ) ENGINE=InnoDB AUTO_INCREMENT=28887 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.073 sec)

MariaDB [test1]> insert into account values (1, 'user1', 'paws', 'ñ1', 'lolo');
Query OK, 1 row affected (0.013 sec)

MariaDB [test1]> select * from account
    -> ;
+----+----------------+------+----------+-------------------------------------------+
| id | mysql_username | type | username | password_hash                             |
+----+----------------+------+----------+-------------------------------------------+
|  1 | user1          | paws | ñ1       | lolo                                      |
+----+----------------+------+----------+-------------------------------------------+
1 row in set (0.001 sec)

MariaDB [test1]> insert into account values (2, 'user1', 'paws', 'n1', 'lolo');
ERROR 1062 (23000): Duplicate entry 'paws-n1' for key 'account_type'

MariaDB [test1]> alter table account convert to character set 'utf8mb4' collate 'utf8mb4_bin';
Query OK, 1 row affected (0.098 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test1]> insert into account values (2, 'user1', 'paws', 'n1', 'lolo');
Query OK, 1 row affected (0.013 sec)

MariaDB [test1]> select * from account;
+----+----------------+------+----------+-------------------------------------------+
| id | mysql_username | type | username | password_hash                             |
+----+----------------+------+----------+-------------------------------------------+
|  1 | user1          | paws | ñ1       | lolo                                      |
|  2 | user1          | paws | n1       | lolo                                      |
+----+----------------+------+----------+-------------------------------------------+
2 rows in set (0.001 sec)
gerritbot added a comment.Sep 26 2022, 9:07 AM

Change 835088 had a related patch set uploaded (by Arturo Borrero Gonzalez; author: Arturo Borrero Gonzalez):

[operations/puppet@production] wmcs: maintain_dbusers: don't halt account population loop on errors

https://gerrit.wikimedia.org/r/835088

gerritbot added a project: Patch-For-Review.Sep 26 2022, 9:07 AM
aborrero renamed this task from Newly created Toolforge tools unable to connect to MariaDB databases to Collation problems on labsdbaccounts results in newly created Toolforge tools unable to connect to MariaDB databases.Sep 26 2022, 9:46 AM
gerritbot added a comment.Sep 26 2022, 10:11 AM

Change 835088 merged by Arturo Borrero Gonzalez:

[operations/puppet@production] wmcs: maintain_dbusers: don't halt account population loop on errors

https://gerrit.wikimedia.org/r/835088

Stashbot added a comment.Sep 26 2022, 10:14 AM

Mentioned in SAL (#wikimedia-cloud) [2022-09-26T10:14:15Z] <arturo> deployed new version of maintain-dbusers (T318047)

Maintenance_bot removed a project: Patch-For-Review.Sep 26 2022, 10:30 AM
aborrero lowered the priority of this task from High to Medium.Sep 26 2022, 11:24 AM

Now the error doesn't crash the daemon:

Sep 26 10:13:05 labstore1004 systemd[1]: Started Maintain labsdb accounts.
Sep 26 10:17:34 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:24:25 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:29:54 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:36:44 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:43:30 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:50:19 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 10:55:49 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 11:01:18 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 11:06:45 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 11:12:18 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Sep 26 11:17:56 labstore1004 /usr/local/sbin/maintain-dbusers[38618]: problem populating new account: (1062, "Duplicate entry 'paws-Inaki LL' for key 'account_type'")
Stashbot added a comment.Sep 26 2022, 12:32 PM

Mentioned in SAL (#wikimedia-cloud) [2022-09-26T12:32:22Z] <dcaro> Changed the collation of labsdbaccount db to utf8mb4_bin (T318047)

dcaro changed the task status from Open to In Progress.Sep 26 2022, 12:35 PM
dcaro claimed this task.
dcaro added a project: User-dcaro.
dcaro moved this task from To refine to Doing on the User-dcaro board.
dcaro added a comment.Sep 26 2022, 12:48 PM

Now the account was created :)

Sep 26 12:39:47 labstore1004 /usr/local/sbin/maintain-dbusers[63140]: Created account in clouddb1015.eqiad.wmnet:3314 for paws Inaki LL

And I was able to login using the wd-flaw-finder credentials.
I'll close this for now, but @Tidoni can you verify that you can login too, and if not, reopen please?

Thanks!

dcaro closed this task as Resolved.Sep 26 2022, 12:48 PM
dcaro moved this task from Doing to Done on the User-dcaro board.
Tidoni added a comment.Sep 26 2022, 4:13 PM

Can confirm, login works fine now. Thanks for looking into it.

Legoktm added a comment.Sep 26 2022, 10:09 PM

Thank you as well!

Enterprisey awarded a token.Sep 27 2022, 12:00 AM
gerritbot added a comment.Sep 29 2022, 2:44 PM

Change 836849 had a related patch set uploaded (by David Caro; author: David Caro):

[operations/puppet@production] maintain-dbusers: add missing collate to the account table

https://gerrit.wikimedia.org/r/836849

gerritbot added a project: Patch-For-Review.Sep 29 2022, 2:44 PM
gerritbot added a comment.Sep 30 2022, 10:16 AM

Change 836849 merged by David Caro:

[operations/puppet@production] maintain-dbusers: add missing collate to the account table

https://gerrit.wikimedia.org/r/836849

Maintenance_bot removed a project: Patch-For-Review.Sep 30 2022, 10:30 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits