@kostajh what is your preferred time horizon for this DB to be available?

@LSobanski is next week a possibility? What options would work for your team?

In T267214#6605223, @Marostegui wrote:

@kostajh thanks for the task.
However, can we get some more details from your side? The clearer everything else, the faster we can try to get it accommodated on our backlog without much back and forth.

The QPS was discussed during the meeting so not a big deal.
However, we'd like to the following before proceeding:

username: Can we get the desired username you'll need to access the database?

How about linkrecommendation since it will be accessed from a service called linkrecommendation in the operations/deployment-charts repo?

Accessed from server (s): from a kubernetes pod: Can we get the list of IPs (although I would assume they'll not be fixed forever, so a range is fine: ie: 10.64.%). We need this to be able to add the grants.

@Joe could you please provide this information to @Marostegui?

Grants needed: SELECT is ok, DROP and TRUNCATE are also OK, do you need CREATE? . Do you need to INSERT data? What's the workflow here.
I remember during the meeting we discussed that I can create the initial tables for you, but once those are created, do you need to drop+create them or truncate + INSERT for the new data is ok?.

I think we need SELECT, TRUNCATE, CREATE and INSERT. When we add a new wiki to our Add-Link project, we will need to create 5 tables:

• lr_{lang}_redirects
• lr_{lang}_nav2vec
• lr_{lang}_word2vec
• lr_{lang}_anchors
• lr_{lang}_pageids

And when we refresh datasets for wikis already in the database, we'll need to TRUNCATE and INSERT. We probably don't need DROP, but if we make changes to the table format in the future we would need ALTER.

Regarding the backup policy, we'll add it to our weekly backups.

sounds good, thank you!

Thanks @kostajh - adding those to the original template.

@kostajh - reminder we are still waiting on knowing from where this database will be accessed.
I could grant 10.64.% or whatever, but if there's something more concrete, that'd be useful.

In T267214#6651446, @Marostegui wrote:

@kostajh - reminder we are still waiting on knowing from where this database will be accessed.
I could grant 10.64.% or whatever, but if there's something more concrete, that'd be useful.

I unfortunately cannot answer that, but perhaps @jijiki / @JMeybohm / @akosiaris know?

In T267214#6652689, @kostajh wrote:

In T267214#6651446, @Marostegui wrote:

@kostajh - reminder we are still waiting on knowing from where this database will be accessed.
I could grant 10.64.% or whatever, but if there's something more concrete, that'd be useful.

I unfortunately cannot answer that, but perhaps @jijiki / @JMeybohm / @akosiaris know?

For the kubernetes side, the database will be accessed from the kubernetes pod IP prefixes.

Mentioned in SAL (#wikimedia-operations) [2020-12-01T08:05:20Z] <marostegui> Create database mwaddlink on m2 - T267214

Change 644456 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m2: Add grants for mwaddlink new database

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

The database has been created under m2 (so it lives together with recommendationapi).
As agreed, two users were created:

• RW one to do the imports: adminlinkrecommendation
• RO for the application: linkrecommendation:

Grants for adminlinkrecommendation@10.192.16.9
GRANT USAGE ON *.* TO `adminlinkrecommendation`@`10.192.16.9` IDENTIFIED BY PASSWORD '*x'

Remember that you need to use m2-master.eqiad.wmnet as a connection entry point.
Credentials work:

# mysql --ssl-verify-server-cert=false -h m2-master.eqiad.wmnet -u adminlinkrecommendation -p mwaddlink
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32268783
Server version: 10.4.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

adminlinkrecommendation@m2-master.eqiad.wmnet[mwaddlink]> show tables;
Empty set (0.001 sec)

adminlinkrecommendation@m2-master.eqiad.wmnet[mwaddlink]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for adminlinkrecommendation@10.64.0.135                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `adminlinkrecommendation`@`10.64.0.135` IDENTIFIED BY PASSWORD '*x' |
| GRANT INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.64.0.135`                                           |
+----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

# mysql --ssl-verify-server-cert=false -h m2-master.eqiad.wmnet -u linkrecommendation -p mwaddlink
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32268933
Server version: 10.4.13-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

linkrecommendation@m2-master.eqiad.wmnet[mwaddlink]> show tables;
Empty set (0.001 sec)

linkrecommendation@m2-master.eqiad.wmnet[mwaddlink]> show grants;
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for linkrecommendation@10.64.0.135                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `linkrecommendation`@`10.64.0.135` IDENTIFIED BY PASSWORD '*x' |
| GRANT SELECT ON `mwaddlink`.* TO `linkrecommendation`@`10.64.0.135`                                                         |
+-----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

And these are all the users and grants:

GRANT INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.192.16.9`
Grants for linkrecommendation@10.192.16.9
GRANT USAGE ON *.* TO `linkrecommendation`@`10.192.16.9` IDENTIFIED BY PASSWORD '*x'
GRANT SELECT ON `mwaddlink`.* TO `linkrecommendation`@`10.192.16.9`
Grants for adminlinkrecommendation@10.64.0.135
GRANT USAGE ON *.* TO `adminlinkrecommendation`@`10.64.0.135` IDENTIFIED BY PASSWORD '*x'
GRANT INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.64.0.135`
Grants for linkrecommendation@10.64.0.135
GRANT USAGE ON *.* TO `linkrecommendation`@`10.64.0.135` IDENTIFIED BY PASSWORD '*x'
GRANT SELECT ON `mwaddlink`.* TO `linkrecommendation`@`10.64.0.135`
Grants for adminlinkrecommendation@10.64.16.19
GRANT USAGE ON *.* TO `adminlinkrecommendation`@`10.64.16.19` IDENTIFIED BY PASSWORD '*x'
GRANT INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.64.16.19`
Grants for linkrecommendation@10.64.16.19
GRANT USAGE ON *.* TO `linkrecommendation`@`10.64.16.19` IDENTIFIED BY PASSWORD '*x'
GRANT SELECT ON `mwaddlink`.* TO `linkrecommendation`@`10.64.16.19`

As I mentioned on the patchset: https://gerrit.wikimedia.org/r/c/operations/puppet/+/644456 I would guess this needs @jijiki or @JMeybohm to push the passwords (left at: mwmaint1002:/home/jayme/mwaddlink to the puppet private repo and help @kostajh puppetizing them on their service.

@kostajh if you need the credentials also to start the initial imports and all that, let me know on which server I can left them.

@jcrespo can you add this database to the backups?
Thanks.

@jcrespo can you add this database to the backups?

roger and willco. Doing right now.

In T267214#6658399, @Marostegui wrote:

@jcrespo can you add this database to the backups?

Backups of m2 added for the mwaddlink, on both datacenters. I also cleaned up old backup grants for the deleted reviewdb, and gave it a general check.

Thank you!

Added the new database to our wikitech page: https://wikitech.wikimedia.org/w/index.php?title=MariaDB%2Fmisc&type=revision&diff=1890045&oldid=1889656

Thank you @Marostegui and @jcrespo!

@kostajh if you need the credentials also to start the initial imports and all that, let me know on which server I can left them.

Are those credentials different from what is left in /home/jayme/mwaddlink on mwmaint1002? I already have access to that.

No, they are the same ones

Change 644456 merged by Marostegui:
[operations/puppet@production] production-m2: Add grants for mwaddlink new database

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

Thank you @Marostegui!

In T267214#6662762, @Marostegui wrote:

I have tested the connection from kubernetes1017 which is on 10.64.0, and it works fine, it can reach m2-master.eqiad.wmnet  thru port 3306 just fine.

For what is worth, that test would NOT catch a problem if there was one. Kubernetes pods do not utilize the IPs of their nodes, but rather each have their own.

However, I did test too and it's fine.

For posterity's sake (and this is probably worthy of a wikitech page), the process was (needs sudo on deploy1001, aka root, but this is being revisited due to the helm3 migration)

$ ssh deploy1001
$ sudo -i
$ kube_env admin codfw
$ kubectl -n default run testing --rm -it --image=docker-registry.discovery.wmnet/wmfdebug
wait for the prompt

$ nmap -p 3306 m2-master.eqiad.wmnet
[snip]

rDNS record for 10.64.0.135: dbproxy1013.eqiad.wmnet
PORT     STATE SERVICE
3306/tcp open  mysql

In T267214#6662790, @akosiaris wrote:

In T267214#6662762, @Marostegui wrote:

I have tested the connection from kubernetes1017 which is on 10.64.0, and it works fine, it can reach m2-master.eqiad.wmnet  thru port 3306 just fine.

For what is worth, that test would NOT catch a problem if there was one. Kubernetes pods do not utilize the IPs of their nodes, but rather each have their own.

However, I did test too and it's fine.

For posterity's sake (and this is probably worthy of a wikitech page), the process was (needs sudo on deploy1001, aka root, but this is being revisited due to the helm3 migration)

$ ssh deploy1001
$ sudo -i
$ kube_env admin codfw
$ kubectl -n default run testing --rm -it --image=docker-registry.discovery.wmnet/wmfdebug
wait for the prompt

$ nmap -p 3306 m2-master.eqiad.wmnet
[snip]

rDNS record for 10.64.0.135: dbproxy1013.eqiad.wmnet
PORT     STATE SERVICE
3306/tcp open  mysql

Thanks Alex: for my own understanding, by the comment at T267214#6652778 - any those pods would be using their own IPs, but always belonging to 10.64.0 range no? (which was the intention of the test with the node)

In T267214#6662807, @Marostegui wrote:

In T267214#6662790, @akosiaris wrote:

In T267214#6662762, @Marostegui wrote:

I have tested the connection from kubernetes1017 which is on 10.64.0, and it works fine, it can reach m2-master.eqiad.wmnet  thru port 3306 just fine.

For what is worth, that test would NOT catch a problem if there was one. Kubernetes pods do not utilize the IPs of their nodes, but rather each have their own.

However, I did test too and it's fine.

For posterity's sake (and this is probably worthy of a wikitech page), the process was (needs sudo on deploy1001, aka root, but this is being revisited due to the helm3 migration)

$ ssh deploy1001
$ sudo -i
$ kube_env admin codfw
$ kubectl -n default run testing --rm -it --image=docker-registry.discovery.wmnet/wmfdebug
wait for the prompt

$ nmap -p 3306 m2-master.eqiad.wmnet
[snip]

rDNS record for 10.64.0.135: dbproxy1013.eqiad.wmnet
PORT     STATE SERVICE
3306/tcp open  mysql

Thanks Alex: for my own understanding, by the comment at T267214#6652778 - any those pods would be using their own IPs, but always belonging to 10.64.0 range no? (which was the intention of the test with the node)

10.64.xx.yy actually, not 10.64.0.x, but overall yes, it's in the 10.64. But that's just for eqiad. codfw pods of course will originate from 10.192.xx.yy ips respectively. Also note they all got IPv6 addresses as well (again in the respective DC address spaces).

Thanks - that makes sense!

Change 646658 had a related patch set uploaded (by Kosta Harlan; owner: Kosta Harlan):
[operations/deployment-charts@master] linkrecommendation: Add private config for DB write user

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

@kostajh backups ran yesterday on both datacenters, but the database backups seem to be empty. Please reopen/ping me if that is unexpected.

In T267214#6678535, @jcrespo wrote:

@kostajh backups ran yesterday on both datacenters, but the database backups seem to be empty. Please reopen/ping me if that is unexpected.

Thanks, it's expected for now as we haven't yet done an initial import.

Thanks for the confirmation. Then backups were setup correctly. Feel free to recheck with me after first import.

Change 646658 merged by jenkins-bot:
[operations/deployment-charts@master] linkrecommendation: Add private config for DB admin user

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

Change 664416 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m2.sql: Add SELECT to adminlinkrecommendation

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

Change 664416 merged by Marostegui:
[operations/puppet@production] production-m2.sql: Add SELECT to adminlinkrecommendation

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

Done!

| GRANT SELECT, INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.64.0.135`                                   |
2 rows in set (0.001 sec)

| GRANT SELECT, INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.192.16.9`                                   |
2 rows in set (0.001 sec)

| GRANT SELECT, INSERT, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.192.16.9`                                   |
2 rows in set (0.000 sec)

@kostajh please test it and if it works fine - feel free to close this task!

In T267214#6832151, @Marostegui wrote:

@kostajh please test it and if it works fine - feel free to close this task!

That works, but I missed one more. Could you please grant DELETE to adminlinkrecommendation? Sorry for the back and forth!

Change 664501 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m2.sql: Add DELETE grant to adminlinkrecommendation

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

Change 664501 merged by Marostegui:
[operations/puppet@production] production-m2.sql: Add DELETE grant to adminlinkrecommendation

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

Done:

root@db1107.eqiad.wmnet[(none)]> pager grep DELETE ; show grants for `adminlinkrecommendation`@`10.64.0.135`; show grants for `adminlinkrecommendation`@`10.192.16.9`; show grants for `adminlinkrecommendation`@`10.192.16.9`;
PAGER set to 'grep DELETE'
| GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.64.0.135`                           |
2 rows in set (0.001 sec)

| GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.192.16.9`                           |
2 rows in set (0.001 sec)

| GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `mwaddlink`.* TO `adminlinkrecommendation`@`10.192.16.9`                           |
2 rows in set (0.001 sec)