Page MenuHomePhabricator

MediaWiki should assume read-only mode if MySQL says DB is read-only
Closed, ResolvedPublic

Description

Author: justin

Description:
I have a MySQL ecosystem of a Master and a number of slaves, with an ip address that can be brought up on the master or any of the slaves for High Availability.

To reduce problems with re-syncing the master from a slave if a failover is needed (failover=the ip address is brought up on a slave after the master crashes), all the MySQL slaves are marked as read-only in their configuration and only after determining that the master MySQL server will be down for an extended period of time, will a slave be brought up read-write and made the new master until the primary master is fixed.

MediaWiki does not operate on a read-only slave, and this is an enhancement request to allow MediaWiki to operate in a read-only MySQL environment. Currently errors are thrown (usually couldn't update the objectcache), although some pages do occasionally load).

The error thrown is 1290, and if this could be caught and recognised as a MySQL read-only server error, MediaWiki could simply throw away any write queries and make only read queries against the server. Of course, each new page load would represent a new attempt at writing, since at any time the MySQL server could be brought up read-write or failed back to the master server.

The error displayed is as follows for one attempt at a page load:

A database query syntax error has occurred. This may indicate a bug in the software. The last attempted database query was:

DELETE FROM `objectcache` WHERE keyname='wiki_staging:pcache:idhash:19674-0!1!0!!en!2'

from within function "MediaWikiBagOStuff::_doquery". MySQL returned error "1290: The MySQL server is running with the --read-only option so it cannot execute this statement (mysql-server-name)".


Version: 1.14.x
Severity: enhancement

Details

Reference
bz22923

Event Timeline

bzimport raised the priority of this task from to Low.
bzimport set Reference to bz22923.
bzimport added a subscriber: Unknown Object (MLST).
bzimport created this task.Mar 22 2010, 6:19 PM
Reedy added a comment.Mar 23 2010, 8:21 AM

If you are using version 1.14, you should really be upgrading, 1.15.2 is considered stable, and 1.16 is not far away

And then need to see if it still occurs in a newer version

justin wrote:

(In reply to comment #1)

If you are using version 1.14, you should really be upgrading, 1.15.2 is
considered stable, and 1.16 is not far away

And then need to see if it still occurs in a newer version

Downtimes on our wiki are difficult to schedule, and we customize new releases before upgrading. We've been waiting for 1.16 to be released so we can upgrade to that version. When it is released, I'll report back if this is still a valid enhancement request or not, and close if appropriate.

demon added a comment.Mar 23 2010, 6:09 PM

I *believe* this issue was fixed in r55079, so an upgrade *should* fix it. Hopefully :)

mark.ziegler wrote:

Just tried with 1.116.0beta1.
No success.
Still receiving message "1290: The MySQL server is running with the --read-only option so it cannot execute this statement (localhost)" and so I am unable to view self created page. Page "recent changes" works.

mark.ziegler wrote:

Screenshot of Mediawiki 1.16.0 Beta 1 with MySQL 5.0.67 in read_only mode.

Attached:

mark.ziegler wrote:

Another test with MediaWiki 1.17alpha (r64152).
Result is the same as mentioned above.

justin wrote:

I now have a 1.16 Beta2 Version of the wiki running, and have found that viewing pages from a read-only MySQL server works (page is displayed at top, and error message is displayed on the bottom)

I have 2 suggestions:

  1. When viewing a page from a MySQL server marked as read-only, the wiki page is shown, and then below that all the toolbars are shown as if it were a wiki page, and the read-only MySQL error is shown. I suggest showing a simple sentence at the bottom or top of the page indicating the wiki is read-only, instead of showing a large page below the existing page (most aren't going to care about the error, unless they go to edit a page)
  1. If MySQL returns a read-only error, make a more friendly error message such as "Database Error: MySQL is running read-only." or similarly simple message (users don't read error messages, as you may know).
Krinkle set Security to None.
Krinkle removed a subscriber: wikibugs-l-list.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMar 9 2016, 2:07 PM
Meno25 removed a subscriber: Meno25.Mar 11 2016, 2:34 PM
Krinkle renamed this task from MediaWiki should revert to read-only if MySQL error indicates DB in read-only to MediaWiki should assume read-only mode if MySQL says DB is read-only.
jcrespo added subscribers: MZMcBride, Joe, aaron and 2 others.

This limitation causes issues when there is an emergency such as T139346 and increases downtime from seconds to >4 minutes.

aaron claimed this task.Jul 6 2016, 10:00 AM

Change 300493 had a related patch set uploaded (by Aaron Schulz):
Automatically detect READ_ONLY mode for MySQL/MariaDB

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

aaron moved this task from No Resources to Doing on the Performance-Team board.Jul 28 2016, 8:38 PM

Change 300493 merged by jenkins-bot:
Automatically detect READ_ONLY mode for MySQL/MariaDB

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

aaron closed this task as Resolved.Jul 30 2016, 3:39 AM

This is huge! Thank you a lot! If this works properly (we should thoroughly test it, datacenter failovers and master failovers can now be fully automatized and will only take a ~5 seconds).