Help talk:Toolforge/Database/Archives/2018

Please do not post any new comments on this page. This is a discussion archive See current discussion or the archives index.

Identifying lag

right now the example query is

SELECT lag FROM heartbeat_p.heartbeat JOIN meta_p.wiki ON CONCAT(shard, '.analytics.db.svc.eqiad.wmflabs') = slice WHERE dbname = 'fawiki';

shouldn't it be

SELECT lag FROM heartbeat_p.heartbeat JOIN meta_p.wiki ON CONCAT(shard, '.labsdb') = slice WHERE dbname = 'fawiki';

? -- Seth (talk) 17:33, 9 May 2018 (UTC)

The *.labsdb service names are deprecated. They are literally DNS CNAME pointers to the *.analytics.db.svc.eqiad.wmflabs service names which are actively maintained. New code and examples should use *.analytics.db.svc.eqiad.wmflabs and/or *.web.db.svc.eqiad.wmflabs as appropriate rather than the legacy labsdb service names. --BryanDavis (talk) 19:22, 9 May 2018 (UTC)

Hi BryanDavis!

If I login at tools-login.wmflabs.org and start sql de, then the first sql query (with .analytics....) results in an empty set. The second sql query (with labsdb) results in the wanted row as expected.

What am I doing wrong? -- seth (talk) 22:24, 9 May 2018 (UTC)

You're not doing anything wrong -- the situation is a bit confusing. Historically, we used to have 's3.labsdb' hostnames for the database servers. These names then made their way into the `meta_p.wiki` table (that table should contain just 's3', but this is now difficult to change). More recently, the database servers were revamped, including a hostname change from 's3.labsdb' to 's3.analytics.etc'. However, the wiki table still contains the old name, which cannot be used as-is anymore...

So either we have to change the slice column in wiki table (which might break tools that split the shard on '.labsdb', as well as people who use the query which was on the documentation page originally), or we have to add a new column that just contains the 's3' identifier. phab:T186675 might be a good moment to also fix this issue.

I have now rewritten the example query to no longer be dependent on a specific postfix -- instead, the query just splits on the first '.' and only uses the first component. valhallasw (Merlijn van Deen) (talk) 09:05, 10 May 2018 (UTC)

Access to user database from paws or quarry

Magnus Manske has published a database called "s52680__science_source_p", see http://magnusmanske.de/wordpress/?p=559. I can get a hold on the database with

   user@tools-bastion-03:~$ mysql --defaults-file=$HOME/replica.my.cnf -h tools.db.svc.eqiad.wmflabs
   MariaDB [(none)]> USE s52680__science_source_p;
   MariaDB [s52680__science_source_p]> SHOW TABLES;

But how do I get access to that database with Quarry or PAWS? In Quarry, the following command

   USE s52680__science_source_p;

results in "Access denied for user '<user>'@'%' to database 's52680__science_source_p'". In PAWS I tried

   conn = pymysql.connect(
       host="tools.db.svc.eqiad.wmflabs",
       user=os.environ['MYSQL_USERNAME'],
       password=os.environ['MYSQL_PASSWORD'],
       database='s52680__science_source_p',
       charset='utf8'
   )

but get 'OperationalError: (1044, "Access denied for user '<user>'@'%' to database 's52680__science_source_p'")'. --Fnielsen (talk) 21:34, 2 August 2018 (UTC)

The answer for Quarry is to wait for task T151158 to be implemented. Currently there is no mechanism to change database servers from the Wiki Replica server to the ToolsDB server.

For Paws, the feature request task is task T188406. --BryanDavis (talk) 23:11, 2 August 2018 (UTC)

Toolforge dumping sqldump from enwiki_p to toolsDB custom space

Hi! Is this the right place to talk about Toolforge? I am new to toolforge and had some questions regarding accessing the replica databases(especially enwiki) from the toolsDB server. My objective right now is to get the ipblocks and revisions table in enwiki database over to my custom tools database space where i can play with the data and create custom tables as i want. But i am not able to access the enwiki database from my tool's custom toolsDB server(understandably enwiki is on different shard/server). Is there a way i can bring in data from enwiki to my tool's DB space? I assume this must have already been used by other users as well trying to work with Wikipedia metadata.

I am trying playing around with the mysql command as follows to generate a dump-

mysqldump -u s53882 -p --single-transaction --quick --lock-tables=false enwiki_p ipblocks > ipblocks.sql

After running this command i am prompted for password, and after entering the password i get the following message- mysqldump: Got error: 1049: "Unknown database 'enwiki_p'" when selecting the database

Please let me know if I should use something other than enwiki_p here, I am running this command from my tools space on toolforge tools.blocklog@tools-bastion-02:~$ pwd /data/project/blocklog

Thanks in Advance! Arnab777as3uj (talk) 01:19, 16 November 2018 (UTC)