Documentation location/URL

None

Problem/Motivation

I am working on #2733675: Warning when mysql is not set to READ-COMMITTED and I need to add a new documentation page.

Url: https://www.drupal.org/docs/system-requirements/mysql-transaction-isolat...

Proposed resolution

The page title:Setting the MySQL transaction isolation level

The page text:
The default transaction isolation level for MySQL, MariaDB and equivalent databases is "REPEATABLE READ". This setting with Drupal can result in deadlocks on tables, which will result in the site becoming very slow or not responding at all.

The recommended transaction isolation level for Drupal sites is 'READ COMMITTED'. The 'REPEATABLE READ' option is supported but can result in deadlocks, the other 2 options are 'READ UNCOMMITTED' and 'SERIALIZABLE'. They are available but not supported; use them at your own risk.

To enable 'READ COMMITTED' on a Drupal site, the settings.php file needs to be updated with the new setting. The default location for the file is sites/default/settings.php. The file is created during the installation of your Drupal site.

At the end of the settings.php file is usually the database connection array found. Something like:

$databases['default']['default'] = array(
  'database' => 'databasename',
  'username' => 'sqlusername',
  'password' => 'sqlpassword',
  'host' => 'localhost',
  'driver' => 'mysql',
  'prefix' => '',
  'port' => '3306',
);

To change the database transaction isolation level to "READ COMMITTED" add the following to the database connection array:

  'init_commands' => [
    'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
  ],

The database connection array with the added setting will be something like:

$databases['default']['default'] = array(
  'database' => 'databasename',
  'username' => 'sqlusername',
  'password' => 'sqlpassword',
  'host' => 'localhost',
  'driver' => 'mysql',
  'prefix' => '',
  'port' => '3306',
  'init_commands' => [
    'isolation_level' => 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED',
  ],
);

For more information about isolation levels see the MySQL Transaction Isolation Levels documentation.

Remaining tasks

Comments

daffie created an issue. See original summary.

daffie’s picture

daffie commented
Priority: Normal » Critical

Changing the priority to critical as its parent issue is also critical.

daffie’s picture

daffie commented

My idea was to add a bit to the MySQL section and link from there to the new page. Something with "#Recommended configuration" and "The recommended transaction isolation level is 'READ COMMITTED', only this is not the default setting. Follow the documentation for changing the setting." With a link to the new page.

murilohp’s picture

murilohp commented

I think it's a good start @daffie! I would just suggest to mention about READ UNCOMMITTED and SERIALIZABLE levels, just mention that these two levels, drupal does not have support but the user can set both at their own risk. What do you think?

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented

Per #1650930: Use READ COMMITTED by default for MySQL transactions:

"The other 3 options are 'REPEATABLE READ', 'READ UNCOMMITTED' and 'SERIALIZABLE'. They are not supported; use them at your own risk."

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented

Who has the ability to create new core documentation pages?

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented
Issue summary: View changes
Status: Needs review » Reviewed & tested by the community
murilohp’s picture

murilohp commented

The other 3 options are 'REPEATABLE READ', 'READ UNCOMMITTED' and 'SERIALIZABLE'. They are not supported; use them at your own risk.

Do you think it's a good idea to mention that drupal does not support 'REPEATABLE READ'? I mean, we know the ideal would be 'READ COMMITTED', but the majority sites using drupal are probably using 'REPEATABLE READ' since it's the current default isolation level.

Edit: Never mind this, it makes sense to say "they are not supported", I've just changed to say "they are available but not supported"

murilohp’s picture

murilohp commented
Issue summary: View changes
avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

https://www.drupal.org/docs/system-requirements/database-server-requirements is a documentation page; it cannot have sub-pages. Only documentation guides can have documentation pages.

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented

OK, we'd better open a "MySQL transaction isolation" page under the "System requirements" guide.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

Would not adding the text on https://www.drupal.org/docs/system-requirements/database-server-requirements be sufficient? The documentation page already contains the following sentences.

Drupal itself will generally operate with a default MySQL configuration. A more complex site will likely require configuration changes for the database.

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented

I'm concerned that adding the full text from the IS here to that page would make that page unbalanced, and would obstruct understanding of the general database needs.

But it would be a perfectly satisfactory solution to this issue, which is blocking a critical issue.

One thing to be aware of is that whatever URL we use will be hardcoded in a status report warning message, so it would be good to minimise the probability of having to change it as documentation expands or gets reorganised.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
Status: Reviewed & tested by the community » Needs work

So I think we should not say that REPEATABLE READ is not supported. For one thing, that's actually the mode that DrupalCI runs all the tests on as far as I can tell. Also if you're not experiencing deadlocks and want the additional transactional safety it's a completely reasonable choice. It's really 'READ UNCOMMITTED' and 'SERIALIZABLE' that are not supported.

murilohp’s picture

murilohp commented
Issue summary: View changes
Status: Needs work » Needs review

Makes sense @alexpott, and thanks for the review here. I've updated IS, could you take a look again?

jonathanshaw’s picture

jonathanshaw
Primary language English
Location Stroud, UK
commented
Issue summary: View changes
Status: Needs review » Reviewed & tested by the community

Updated per #10.

daffie’s picture

daffie commented

I have created the page https://www.drupal.org/docs/system-requirements/setting-the-mysql-transa.... I have also asked if I could become the maintainer of it. The page has still the warning "This page has not yet been reviewed by System requirements maintainer(s) and added to the menu.", therefor the issue is not ready to be marked as fixed.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

I added the page to the parent guide. Since that is a documentation page, it's not possible to add maintainers.

@daffie You can be added as maintainer for System Requirements, which is a documentation guide.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

I added daffie as maintainer of the System Requirements guide.

daffie’s picture

daffie commented
Status: Reviewed & tested by the community » Fixed

@apaderno: Thanks!

The warning "This page has not yet been reviewed by System requirements maintainer(s) and added to the menu." on the page has been resolved. Marking this is as fixed. Thanks everybody!

hansfn’s picture

hansfn commented

I think this page is a mismatch (directly) under "System requirements". Yes, I'm aware that it can't be a sub page of Database server requirements (because of limitations in the Doc system). Anyway, if it is a requirement, isn't it better if the title is "MySQL transaction isolation level". That seems to fit better with the other requirement page titles.

PS! The non-alias URL - https://www.drupal.org/node/3285800 - is safer to use when linking from Drupal core. Even if redirects are left behind when an alias URL change, it's likely to break at some point. This is a "wiki" page after all ...

daffie’s picture

daffie commented

@hansfn: Maybe, the title "MySQL transaction isolation level" is a little bit better. Only we would like site owners to change the setting and it will become the default for new sites, therefor having the "Setting the" part in the title looks good to me. Also the core comitter @alexpott had no objections to the title. However if more people think we should change the title and #2733675: Warning when mysql is not set to READ-COMMITTED has not landed, we can change it.

For the link: All pages are linked by there full name. Lets do the same for this page.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.