Drop wb_entity_per_page views in Wiki Replicas
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Ladsgroup
	Oct 20 2017, 9:17 AM

Description

We've got https://gerrit.wikimedia.org/r/382694 merged, and DBAs will take care of dropping it everywhere, including Wiki Replicas, but we need someone from cloud team to run maintain-views so the views stop referencing an eventually non-existent table. Thanks

Related Objects
Search...

Status	Assigned	Task
Open	None	T30599 Deadlock tracking bug (tracking)
Resolved	hoo	T111535 Wikibase\Repo\Store\SQL\EntityPerPageTable::{closure} creating high number of deadlocks
Declined	None	T51982 Add missing wb_entity_per_page entries on LinksUpdate
Invalid	Lydia_Pintscher	T70176 EntityPerPageTable class should be usable from the client
Resolved	Ladsgroup	T67333 Wikibase\EntityPerPageTable::getItemsWithoutSitelinks slow query with large LIMIT offset
Resolved	Addshore	T114902 Remove numeric entity IDs from database schema
Resolved	Ladsgroup	T95685 Drop wb_entity_per_page table
Resolved	• Marostegui	T177601 Deploy dropping wb_entity_per_page table
Resolved	Andrew	T178661 Drop wb_entity_per_page views in Wiki Replicas

Event Timeline

Ladsgroup created this task.Oct 20 2017, 9:17 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 20 2017, 9:17 AM

• jcrespo renamed this task from Drop replication of wb_entity_per_page in labs to Drop wb_entity_per_page views in labs.Oct 20 2017, 9:23 AM

• jcrespo updated the task description. (Show Details)

• Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.Oct 20 2017, 9:26 AM

bd808 renamed this task from Drop wb_entity_per_page views in labs to Drop wb_entity_per_page views in Wiki Replicas.Oct 20 2017, 2:57 PM

bd808 updated the task description. (Show Details)

bd808 edited projects, added Data-Services, cloud-services-team (Kanban); removed cloud-services-team.

bd808 moved this task from Inbox to Clinic Duty on the cloud-services-team (Kanban) board.

Ladsgroup moved this task from Proposed to Monitoring on the Wikidata-Former-Sprint-Board board.Oct 22 2017, 12:44 PM

I will update this task once T177601 if finished so it can be done.
Stalling it for now until the above task is resolved.

Thanks gentlemen :)

bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Oct 26 2017, 3:21 PM

I have dropped the table everywhere (T177601#3718698) so this ticket can moved forward by our cloud-services-team

The view was removed by rOPUPb787dc9b4410: labs: do not replicate wb_entity_per_page table.

(u3518@wikidatawiki.labsdb) [wikidatawiki_p]> describe wb_entity_per_page;
ERROR 1356 (HY000): View 'wikidatawiki_p.wb_entity_per_page' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I think this means we need to run maintain-views --all-databases --clean to drop views that are no longer defined in the config. The script currently does not support dropping a single view.

Actually we probably just need maintain-views --databases wikidatawiki --clean rather than all wikis.

In T178661#3725222, @bd808 wrote:

Actually we probably just need maintain-views --databases wikidatawiki --clean rather than all wikis.

And also for testwikidatawiki

Assigning to @chasemp as current on-call. This fell through the cracks.

In T178661#3725222, @bd808 wrote:

Actually we probably just need maintain-views --databases wikidatawiki --clean rather than all wikis.

labsdb1010:~# maintain-views --databases wikidatawiki --clean --replace-all
2017-11-15 20:30:05,262 INFO Full views for wikidatawiki:
2017-11-15 20:30:05,264 INFO [abuse_filter_action]
2017-11-15 20:30:05,285 INFO [babel]
2017-11-15 20:30:05,289 INFO [category]
2017-11-15 20:30:05,292 INFO [categorylinks]
2017-11-15 20:30:05,296 INFO [change_tag]
2017-11-15 20:30:05,342 INFO [externallinks]
2017-11-15 20:30:05,371 INFO [geo_tags]
2017-11-15 20:30:05,379 INFO [global_block_whitelist]
2017-11-15 20:30:05,384 INFO [image]
2017-11-15 20:30:05,387 INFO [imagelinks]
2017-11-15 20:30:05,393 INFO [interwiki]
2017-11-15 20:30:05,396 INFO [iwlinks]
2017-11-15 20:30:05,399 INFO [l10n_cache]
2017-11-15 20:30:05,402 INFO [langlinks]
2017-11-15 20:30:05,409 INFO [linter]
2017-11-15 20:30:05,416 INFO [math]
2017-11-15 20:30:05,419 INFO [module_deps]
2017-11-15 20:30:05,422 INFO [msg_resource_links]
2017-11-15 20:30:05,428 INFO [ores_classification]
2017-11-15 20:30:05,431 INFO [ores_model]
2017-11-15 20:30:05,434 INFO [page]
2017-11-15 20:30:05,441 INFO [pagelinks]
2017-11-15 20:30:05,451 INFO [page_props]
2017-11-15 20:30:05,454 INFO [page_restrictions]
2017-11-15 20:30:05,469 INFO [protected_titles]
2017-11-15 20:30:05,475 INFO [redirect]
2017-11-15 20:30:05,480 INFO [site_identifiers]
2017-11-15 20:30:05,484 INFO [sites]
2017-11-15 20:30:05,488 INFO [site_stats]
2017-11-15 20:30:05,492 INFO [tag_summary]
2017-11-15 20:30:05,496 INFO [templatelinks]
2017-11-15 20:30:05,499 INFO [transcode]
2017-11-15 20:30:05,503 INFO [updatelog]
2017-11-15 20:30:05,509 INFO [user_former_groups]
2017-11-15 20:30:05,513 INFO [user_groups]
2017-11-15 20:30:05,518 INFO [valid_tag]
2017-11-15 20:30:05,533 INFO [globalblocks]
2017-11-15 20:30:05,542 INFO [wb_changes]
2017-11-15 20:30:05,555 INFO [wb_changes_dispatch]
2017-11-15 20:30:05,559 INFO [wb_changes_subscription]
2017-11-15 20:30:05,562 INFO [wb_id_counters]
2017-11-15 20:30:05,566 INFO [wb_items_per_site]
Traceback (most recent call last):
  File "/usr/local/sbin/maintain-views", line 452, in <module>
    ops.execute(fullviews, customviews)
  File "/usr/local/sbin/maintain-views", line 272, in execute
    self.do_fullview(view)
  File "/usr/local/sbin/maintain-views", line 125, in do_fullview
    """.format(self.definer, self.db_p, view, self.db))
  File "/usr/local/sbin/maintain-views", line 53, in write_execute
    self.cursor.execute(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 166, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 852, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1053, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1336, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1010, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

This is the direct cause of T180564.

New wikireplica's:

maintain-views --databases wikidatawiki --clean --replace-all
maintain-views --databases testwikidatawiki --clean --replace-all

1003:

pymysql.err.OperationalError: (1142, "CREATE VIEW command denied to user 'maintainviews'@'localhost' for table 'abuse_filter_action'")

In T178661#3774316, @chasemp wrote:
New wikireplica's:
maintain-views --databases wikidatawiki --clean --replace-all
maintain-views --databases testwikidatawiki --clean --replace-all
1003:

pymysql.err.OperationalError: (1142, "CREATE VIEW command denied to user 'maintainviews'@'localhost' for table 'abuse_filter_action'")

I have fixed it and it ran fine on labsdb1003.
There was one GRANT missing there (that was not missing on the new replicas)

[root@labsdb1003 13:56 /root]
# maintain-views --databases testwikidatawiki --clean --replace-all --mysql-socket /tmp/mysql.sock
2017-11-20 13:56:14,035 INFO Full views for testwikidatawiki:
2017-11-20 13:56:14,037 INFO [abuse_filter_action]
2017-11-20 13:56:14,072 INFO [babel]
2017-11-20 13:56:14,088 INFO [category]
2017-11-20 13:56:14,093 INFO [categorylinks]
2017-11-20 13:56:14,110 INFO [change_tag]
2017-11-20 13:56:14,165 INFO [externallinks]
2017-11-20 13:56:14,194 INFO [geo_tags]
2017-11-20 13:56:14,199 INFO [global_block_whitelist]
2017-11-20 13:56:14,215 INFO [image]
2017-11-20 13:56:14,221 INFO [imagelinks]
2017-11-20 13:56:14,236 INFO [interwiki]
2017-11-20 13:56:14,239 INFO [iwlinks]
2017-11-20 13:56:14,260 INFO [l10n_cache]
2017-11-20 13:56:14,266 INFO [langlinks]
2017-11-20 13:56:14,281 INFO [linter]
2017-11-20 13:56:14,302 INFO [math]
2017-11-20 13:56:14,322 INFO [module_deps]
2017-11-20 13:56:14,325 INFO [msg_resource_links]
2017-11-20 13:56:14,347 INFO [page]
2017-11-20 13:56:14,352 INFO [pagelinks]
2017-11-20 13:56:14,361 INFO [page_props]
2017-11-20 13:56:14,377 INFO [page_restrictions]
2017-11-20 13:56:14,392 INFO [protected_titles]
2017-11-20 13:56:14,410 INFO [redirect]
2017-11-20 13:56:14,416 INFO [site_identifiers]
2017-11-20 13:56:14,433 INFO [sites]
2017-11-20 13:56:14,439 INFO [site_stats]
2017-11-20 13:56:14,444 INFO [tag_summary]
2017-11-20 13:56:14,465 INFO [templatelinks]
2017-11-20 13:56:14,486 INFO [transcode]
2017-11-20 13:56:14,491 INFO [updatelog]
2017-11-20 13:56:14,513 INFO [user_former_groups]
2017-11-20 13:56:14,519 INFO [user_groups]
2017-11-20 13:56:14,523 INFO [valid_tag]
2017-11-20 13:56:14,553 INFO [globalblocks]
2017-11-20 13:56:14,579 INFO [wb_changes]
2017-11-20 13:56:14,596 INFO [wb_changes_dispatch]
2017-11-20 13:56:14,600 INFO [wb_changes_subscription]
2017-11-20 13:56:14,605 INFO [wb_id_counters]
2017-11-20 13:56:14,621 INFO [wb_items_per_site]
2017-11-20 13:56:14,626 INFO [wb_property_info]
2017-11-20 13:56:14,644 INFO [wb_terms]
2017-11-20 13:56:14,648 INFO [wbc_entity_usage]
2017-11-20 13:56:14,663 INFO [wbqc_constraints]
2017-11-20 13:56:14,685 INFO [wbs_propertypairs]
2017-11-20 13:56:14,694 INFO [watchlist_count]
2017-11-20 13:56:14,708 INFO Custom views for testwikidatawiki:
2017-11-20 13:56:14,710 INFO [recentchanges]
2017-11-20 13:56:14,728 INFO [ip_changes]
2017-11-20 13:56:14,731 INFO [user]
2017-11-20 13:56:14,734 INFO [logging]
2017-11-20 13:56:14,736 INFO [ipblocks]
2017-11-20 13:56:14,739 INFO [archive_userindex]
2017-11-20 13:56:14,742 INFO [logging_userindex]
2017-11-20 13:56:14,746 INFO [abuse_filter]
2017-11-20 13:56:14,762 INFO [recentchanges_userindex]
2017-11-20 13:56:14,765 INFO [abuse_filter_log]
2017-11-20 13:56:14,769 INFO [user_properties]
2017-11-20 13:56:14,783 INFO [oldimage_userindex]
2017-11-20 13:56:14,794 INFO [user_properties_anon]
2017-11-20 13:56:14,813 INFO [archive]
2017-11-20 13:56:14,816 INFO [revision_userindex]
2017-11-20 13:56:14,819 INFO [logging_logindex]
2017-11-20 13:56:14,823 INFO [filearchive]
2017-11-20 13:56:14,828 INFO [oldimage]
2017-11-20 13:56:14,831 INFO [filearchive_userindex]
2017-11-20 13:56:14,834 INFO [revision]
2017-11-20 13:56:14,837 INFO [ipblocks_ipindex]
2017-11-20 13:56:14,839 INFO cleanup is enabled
2017-11-20 13:56:14,840 INFO cleaning 1 tables
2017-11-20 13:56:14,841 INFO Dropping view wb_entity_per_page

However, wikidatawiki keeps failing for the transaction time

[root@labsdb1003 13:56 /root]
# maintain-views --databases wikidatawiki --clean --replace-all --mysql-socket /tmp/mysql.sock
2017-11-20 13:56:30,327 INFO Full views for wikidatawiki:
2017-11-20 13:56:30,328 INFO [abuse_filter_action]
2017-11-20 13:56:30,363 INFO [babel]
2017-11-20 13:56:30,367 INFO [category]
2017-11-20 13:56:30,386 INFO [categorylinks]
2017-11-20 13:56:30,393 INFO [change_tag]
2017-11-20 13:56:30,455 INFO [externallinks]
2017-11-20 13:56:30,481 INFO [geo_tags]
2017-11-20 13:56:30,500 INFO [global_block_whitelist]
2017-11-20 13:56:30,509 INFO [image]
2017-11-20 13:56:30,524 INFO [imagelinks]
2017-11-20 13:56:30,531 INFO [interwiki]
2017-11-20 13:56:30,536 INFO [iwlinks]
2017-11-20 13:56:30,541 INFO [l10n_cache]
2017-11-20 13:56:30,572 INFO [langlinks]
2017-11-20 13:56:30,580 INFO [linter]
2017-11-20 13:56:30,586 INFO [math]
2017-11-20 13:56:30,602 INFO [module_deps]
2017-11-20 13:56:30,604 INFO [msg_resource_links]
2017-11-20 13:56:30,611 INFO [ores_classification]
2017-11-20 13:56:30,614 INFO [ores_model]
2017-11-20 13:56:30,639 INFO [page]



 Traceback (most recent call last):
  File "/usr/local/sbin/maintain-views", line 452, in <module>
    ops.execute(fullviews, customviews)
  File "/usr/local/sbin/maintain-views", line 272, in execute
    self.do_fullview(view)
  File "/usr/local/sbin/maintain-views", line 125, in do_fullview
    """.format(self.definer, self.db_p, view, self.db))
  File "/usr/local/sbin/maintain-views", line 53, in write_execute
    self.cursor.execute(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 158, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 308, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 820, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1002, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 966, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 394, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 120, in raise_mysql_exception
    _check_mysql_exception(errinfo)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 115, in _check_mysql_exception
    raise InternalError(errno, errorvalue)
pymysql.err.InternalError: (1205, 'Lock wait timeout exceeded; try restarting transaction')

Thank you @Marostegui

Closing this thing out now :)

Ah, dang. I did so before seeing the last comment.

Sorry! :-)

• chasemp reassigned this task from • chasemp to Andrew.Nov 21 2017, 9:21 PM

@Marostegui this is on my plate but I have no idea how to proceed... is this timeout due to some long-running job that I need to locate and kill?

In T178661#3787554, @Andrew wrote:

@Marostegui this is on my plate but I have no idea how to proceed... is this timeout due to some long-running job that I need to locate and kill?

Indeed.
Although I would try to look for a time where there are no transactions going on on the page table (I just looked up and there are a few queries running) instead of killing transactions. But up to you :-)

Stashbot subscribed.Nov 28 2017, 10:04 PM

This comment was removed by bd808.

labsdb1003 will be shutdown in 2 weeks. The "damage" prior to that is just that there is a view available on the wikidatawiki.labsdb database that points to non-existent tables. In the longer term we should make some changes to maintain-views so that it can drop a single view without needing to rebuild all of the views for the database.

bd808 moved this task from Clinic Duty to Done on the cloud-services-team (Kanban) board.Nov 30 2017, 6:07 AM

In the longer term we should make some changes to maintain-views so that it can drop a single view without needing to rebuild all of the views for the database.

Also, given that the "new" servers can be depooled almost at will, metadata locking can be avoided, as it can be done on the passive server always.

Drop wb_entity_per_page views in Wiki ReplicasClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Drop wb_entity_per_page views in Wiki Replicas
Closed, ResolvedPublic
Actions

Related Objects
Search...