Answers.Body: <p>Ok, I got the database upgrade to work by <strong>turning OFF hash joins</strong>.</p>
  <p>In my version of mysql 8.0.19, I did this by setting <code>block_nested_loop=off</code> in the <code>optimizer_switch</code> variable. Easy to do in phpmyadmin &gt; server &gt; variables.</p>
  <p>Then I went back to the Civi UI to run the db upgrade link that kept breaking:
  <a href="http://mylocal.lndo.site/civicrm/upgrade?reset=1" rel="nofollow noreferrer">http://mylocal.lndo.site/civicrm/upgrade?reset=1</a></p>
  <p>...and this time it gave me the upgrade screen!</p>
  <p><a href="https://i.sstatic.net/0nrNw.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/0nrNw.png" alt="enter image description here" /></a></p>
  <p>...and the process finished perfectly!</p>
  <p><a href="https://i.sstatic.net/BFPaY.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/BFPaY.png" alt="enter image description here" /></a></p>
  <p><em><strong>Reference 1</strong></em></p>
  <blockquote>
  <p>Block Nested-Loop Flags</p>
  <p><code>block_nested_loop</code> (default on)</p>
  <p>Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this
  also controls use of hash joins, as do the BNL and NO_BNL optimizer
  hints. In MySQL 8.0.20 and later, block nested loop support is removed
  from the MySQL server, and this flag controls the use of hash joins
  only, as do the referenced optimizer hints.</p>
  </blockquote>
  <p>Source: <a href="https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html#optflag_block-nested-loop" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html#optflag_block-nested-loop</a></p>
  <p><strong>Reference 2</strong></p>
  <blockquote>
  <p>By default, MySQL 8.0.18 and later employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL and NO_BNL optimizer hints.</p>
  <p>(MySQL 8.0.18 supported hash_join=on or hash_join=off as part of the setting for the optimizer_switch server system variable as well as the optimizer hints HASH_JOIN or NO_HASH_JOIN. In MySQL 8.0.19 and later, these no longer have any effect.)</p>
  </blockquote>
  <p>Source: <a href="https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-12T18:13:45.877Z
Answers.Text: Good job. There are core unit tests that test the upgrade, among other things, so not sure why it didn't come up there. Most run mysql 5.7 but looks like one variation currently runs 8.0.29 (and at one time was probably 8.0.19), but I don't know what its config is.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-12T18:29:56.607Z
Answers.Text: Thanks, I really appreciate your input to help figure it out. I plan to upgrade mysql to a newer version and see what happens. Could this be an issue experienced more widely by other mysql users? Anecdotally, I noticed another person on the Mattermost Drupal channel a day or two ago experienced WSOD upgrading from 5.66.1 to 5.67. So perhaps it's a wider issue than just my particular configuration.
Answers.UserDisplayName: Justin657
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-12T03:38:20.350Z
Answers.LastActivityDate: 2023-11-12T03:38:20.350Z
Answers.UserDisplayName: Justin657
Body: <p>On my local lando dev environment, I proceeded to <code>composer update</code> to get onto the latest stable/recommended Drupal 10.1.6 and Civi 5.67.0.</p>
  <p>Post-upgrade state:</p>
  <ul>
  <li>CiviCRM UI seems to work fine</li>
  <li>CiviCRM System Status: shows red error &quot;Database Upgrade Required&quot;</li>
  </ul>
  <p><strong>The Civi database upgrade step seems to be broken.</strong></p>
  <ol>
  <li>Following the current <a href="https://docs.civicrm.org/sysadmin/en/latest/upgrade/drupal8/" rel="nofollow noreferrer">upgrade documentation for Drupal 10</a>... after doing <code>composer update</code>, I tried <code>cv upgrade:db</code> in a terminal, but it gave no output.</li>
  <li>I then proceeded to try the UI database upgrade, <code>http://example.org/civicrm/upgrade?reset=1</code>, but this time the site crashed, showing WSOD with this error:</li>
  </ol>
  <pre><code>The website encountered an unexpected error. Please try again later.&lt;br /&gt;
  Warning: Undefined array key 0 in /app/web/core/lib/Drupal/Core/Utility/Error.php on line 142
  
  Warning: Undefined array key &quot;file&quot; in /app/web/core/includes/errors.inc on line 77
  
  Warning: Undefined array key &quot;line&quot; in /app/web/core/includes/errors.inc on line 78
  </code></pre>
  <p>I'm not sure there's much to gather from that backtrace.</p>
  <p>What I found interesting is that it <strong>instantly killed MySQL</strong>!</p>
  <p>It didn't run for a long time or timeout. Boom, it was dead.</p>
  <p>I'm running MySQL 8.0.</p>
  <p>So I restarted MySQL service, turned ON the MySQL <code>general_log</code> to try to capture what's going on there, attempted the Civi Database Upgrade again via the URL (<code>http://example.org/civicrm/upgrade?reset=1</code>)... and it crashed again.</p>
  <p>The last item recorded in the MySQL general_log is this query:</p>
  <pre><code>SELECT
    default_template.id,
    default_template.workflow_id,
    default_template.workflow_name
  FROM
    civicrm_msg_template reserved
    LEFT JOIN civicrm_msg_template default_template ON reserved.workflow_id = default_template.workflow_id
  WHERE
    reserved.is_reserved = 1
    AND default_template.is_default = 1
    AND reserved.id &lt;&gt; default_template.id
    AND reserved.msg_html = default_template.msg_html
  </code></pre>
  <p>When I run this query in PhpMyAdmin, it instantly kills MySQL.</p>
  <p><a href="https://i.sstatic.net/FyIkR.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/FyIkR.png" alt="enter image description here" /></a></p>
  <p>I'm deep in the weeds trying to diagnose if it's my local setup or a more general issue.</p>
  <p>Any ideas?</p>
  <p>Is there anything else I can try to get the CiviCRM Database Upgrade to go through properly?</p>
  <hr />
  <p>UPDATE:</p>
  <p>That query seems to cause some sort of memory issue in mysql.</p>
  <p>Here's what the mysql log says about the crash:</p>
  <pre><code>database_1  | 22:35:22 UTC - mysqld got signal 11 ;
  database_1  | Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
  database_1  | Thread pointer: 0x7ffa840141b0
  database_1  | Attempting backtrace. You can use the following information to find out
  database_1  | where mysqld died. If you see no messages after this, something went
  database_1  | terribly wrong...
  database_1  | stack_bottom = 7ffb18143d60 thread_stack 0x46000
  database_1  | /opt/bitnami/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x55a8fec2f24e]
  database_1  | /opt/bitnami/mysql/bin/mysqld(handle_fatal_signal+0x323) [0x55a8fddad953]
  database_1  | /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7ffb34bfe730]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x2575a88) [0x55a8ff2a5a88]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x11e42b2) [0x55a8fdf142b2]
  database_1  | /opt/bitnami/mysql/bin/mysqld(hash_join_buffer::HashJoinRowBuffer::StoreRow(THD*)+0x62) [0x55a8fdebff52]
  database_1  | /opt/bitnami/mysql/bin/mysqld(HashJoinIterator::BuildHashTable()+0x80) [0x55a8fdec2080]
  database_1  | /opt/bitnami/mysql/bin/mysqld(HashJoinIterator::Init()+0xe0) [0x55a8fdec24e0]
  database_1  | /opt/bitnami/mysql/bin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*)+0x251) [0x55a8fdd3af11]
  database_1  | /opt/bitnami/mysql/bin/mysqld(SELECT_LEX_UNIT::execute(THD*)+0xf9) [0x55a8fdd3d0c9]
  database_1  | /opt/bitnami/mysql/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x20b) [0x55a8fdccccdb]
  database_1  | /opt/bitnami/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x3e8) [0x55a8fdcd6118]
  database_1  | /opt/bitnami/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0x19ad) [0x55a8fdc8884d]
  database_1  | /opt/bitnami/mysql/bin/mysqld(mysql_parse(THD*, Parser_state*)+0x31c) [0x55a8fdc8c43c]
  database_1  | /opt/bitnami/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x156b) [0x55a8fdc8dedb]
  database_1  | /opt/bitnami/mysql/bin/mysqld(do_command(THD*)+0x174) [0x55a8fdc8f474]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x106fa48) [0x55a8fdd9fa48]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x23dfadc) [0x55a8ff10fadc]
  database_1  | /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7ffb34bf3fa3]
  database_1  | /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7ffb343e44cf]
  database_1  | 
  database_1  | Trying to get some variables.
  database_1  | Some pointers may be invalid and cause the dump to abort.
  database_1  | Query (7ffa850531b8): SELECT default_template.id, default_template.workflow_id, default_template.workflow_name FROM civicrm_msg_template reserved         LEFT JOIN civicrm_msg_template default_template           ON reserved.workflow_id = default_template.workflow_id         WHERE reserved.is_reserved = 1 AND default_template.is_default = 1 AND reserved.id &lt;&gt; default_template.id         AND reserved.msg_html = default_template.msg_html
  database_1  | Connection ID (thread ID): 23
  database_1  | Status: NOT_KILLED
  database_1  | 
  database_1  | The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  database_1  | information that should help you find out what is causing the crash.
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-10T00:03:13.293Z
Comments.Text: Is mysql 8 a recent upgrade or has it always been mysql 8? Or have some mysql settings changed? That message_template query isn't the world's most efficient but it's not going to blow anything up, and the most records it could return is the number of system workflow templates which is 30 or so. It _does_ need to compare the text of the templates which is not efficient but these templates aren't huge.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-10T04:17:50.903Z
Comments.Text: It's always been on mysql 8. All my previous attempts to upgrade Civi have ended this same way with a crash. In the past I've just lando destroyed and rebuilt since I've got lots scripted. However this time I wanted to see why it's breaking.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-10T04:19:45.847Z
Comments.Text: Yeah my message template table has only 67 rows. I agree, that query sure doesn't seem like the likely culprit to be crashing mysql.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-10T21:50:14.913Z
Comments.Text: The last line of that query seems to be the problem: `AND reserved.msg_html = default_template.msg_html`. The query runs fine without that line. Since the civicrm_msg_template.msg_html field is of type LONGTEXT, perhaps there's some sort of memory issue when performing the = compare.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T19:00:45.603Z
Comments.Text: That's interesting. It's crashing on hash_join_buffer. If you do `show variables` in mysql does it have anything that looks interesting (in particular the ones related to join buffer - on my local I see `join_buffer_size=262144, join_buffer_space_limit=2097152, max_join_size=18446744073709551615`). Or anything related to buffer or hash or memory.
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T23:20:16.793Z
Comments.Text: Here's what my local says: `join_buffer_size`=262144, `max_join_size`=18446744073709551615
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T23:20:53.550Z
Comments.Text: `optimizer_switch` is set to this very long list of settings, the last of which is `hash_join=on`... index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T23:22:59.197Z
Comments.Text: There's no variable listed as `join_buffer_space_limit` on mine.
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T23:32:01.510Z
Comments.Text: More... `innodb_adaptive_hash_index`=ON, `innodb_adaptive_hash_index_parts`=8, `bulk_insert_buffer_size`=8388608, `innodb_buffer_pool_chunk_size`=134217728, `innodb_buffer_pool_size`=402653184, `innodb_log_buffer_size`=67108864, `read_buffer_size`=2097152, `key_buffer_size`=402653184
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-11T23:32:36.413Z
Comments.Text: Btw this local is on mysql 8.0.19 (current default for a lando installation)
Comments.UserDisplayName: Justin657
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-12T03:04:57.593Z
Comments.Text: Perhaps it's an issue with the LEFT JOIN with `hash_join=ON` in mysql 8.0.19 which goes away in later versions... "In MySQL 8.0.20 and later, hash joins are used for outer joins (including antijoins and semijoins)..."
  https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
Comments.UserDisplayName: Justin657
Tags: <upgrade><mysql><wsod>
Title: Database Upgrade from 5.65.0 to 5.67.0 throws WSOD and kills MySQL
ThreadText: # Database Upgrade from 5.65.0 to 5.67.0 throws WSOD and kills MySQL
  
  (Asked by: username_0 on 2023-11-09)
  
  On my local lando dev environment, I proceeded to composer update to get onto the latest stable/recommended Drupal 10.1.6 and Civi 5.67.0.
  Post-upgrade state:
  
  CiviCRM UI seems to work fine
  CiviCRM System Status: shows red error "Database Upgrade Required"
  
  The Civi database upgrade step seems to be broken.
  
  Following the current upgrade documentation for Drupal 10... after doing composer update, I tried cv upgrade:db in a terminal, but it gave no output.
  I then proceeded to try the UI database upgrade, http://example.org/civicrm/upgrade?reset=1, but this time the site crashed, showing WSOD with this error:
  
  The website encountered an unexpected error. Please try again later.
  Warning: Undefined array key 0 in /app/web/core/lib/Drupal/Core/Utility/Error.php on line 142
  
  Warning: Undefined array key "file" in /app/web/core/includes/errors.inc on line 77
  
  Warning: Undefined array key "line" in /app/web/core/includes/errors.inc on line 78
  
  I'm not sure there's much to gather from that backtrace.
  What I found interesting is that it instantly killed MySQL!
  It didn't run for a long time or timeout. Boom, it was dead.
  I'm running MySQL 8.0.
  So I restarted MySQL service, turned ON the MySQL general_log to try to capture what's going on there, attempted the Civi Database Upgrade again via the URL (http://example.org/civicrm/upgrade?reset=1)... and it crashed again.
  The last item recorded in the MySQL general_log is this query:
  SELECT
    default_template.id,
    default_template.workflow_id,
    default_template.workflow_name
  FROM
    civicrm_msg_template reserved
    LEFT JOIN civicrm_msg_template default_template ON reserved.workflow_id = default_template.workflow_id
  WHERE
    reserved.is_reserved = 1
    AND default_template.is_default = 1
    AND reserved.id <> default_template.id
    AND reserved.msg_html = default_template.msg_html
  
  When I run this query in PhpMyAdmin, it instantly kills MySQL.
  
  I'm deep in the weeds trying to diagnose if it's my local setup or a more general issue.
  Any ideas?
  Is there anything else I can try to get the CiviCRM Database Upgrade to go through properly?
  
  UPDATE:
  That query seems to cause some sort of memory issue in mysql.
  Here's what the mysql log says about the crash:
  database_1  | 22:35:22 UTC - mysqld got signal 11 ;
  database_1  | Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
  database_1  | Thread pointer: 0x7ffa840141b0
  database_1  | Attempting backtrace. You can use the following information to find out
  database_1  | where mysqld died. If you see no messages after this, something went
  database_1  | terribly wrong...
  database_1  | stack_bottom = 7ffb18143d60 thread_stack 0x46000
  database_1  | /opt/bitnami/mysql/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x55a8fec2f24e]
  database_1  | /opt/bitnami/mysql/bin/mysqld(handle_fatal_signal+0x323) [0x55a8fddad953]
  database_1  | /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730) [0x7ffb34bfe730]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x2575a88) [0x55a8ff2a5a88]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x11e42b2) [0x55a8fdf142b2]
  database_1  | /opt/bitnami/mysql/bin/mysqld(hash_join_buffer::HashJoinRowBuffer::StoreRow(THD*)+0x62) [0x55a8fdebff52]
  database_1  | /opt/bitnami/mysql/bin/mysqld(HashJoinIterator::BuildHashTable()+0x80) [0x55a8fdec2080]
  database_1  | /opt/bitnami/mysql/bin/mysqld(HashJoinIterator::Init()+0xe0) [0x55a8fdec24e0]
  database_1  | /opt/bitnami/mysql/bin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*)+0x251) [0x55a8fdd3af11]
  database_1  | /opt/bitnami/mysql/bin/mysqld(SELECT_LEX_UNIT::execute(THD*)+0xf9) [0x55a8fdd3d0c9]
  database_1  | /opt/bitnami/mysql/bin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x20b) [0x55a8fdccccdb]
  database_1  | /opt/bitnami/mysql/bin/mysqld(Sql_cmd_dml::execute(THD*)+0x3e8) [0x55a8fdcd6118]
  database_1  | /opt/bitnami/mysql/bin/mysqld(mysql_execute_command(THD*, bool)+0x19ad) [0x55a8fdc8884d]
  database_1  | /opt/bitnami/mysql/bin/mysqld(mysql_parse(THD*, Parser_state*)+0x31c) [0x55a8fdc8c43c]
  database_1  | /opt/bitnami/mysql/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x156b) [0x55a8fdc8dedb]
  database_1  | /opt/bitnami/mysql/bin/mysqld(do_command(THD*)+0x174) [0x55a8fdc8f474]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x106fa48) [0x55a8fdd9fa48]
  database_1  | /opt/bitnami/mysql/bin/mysqld(+0x23dfadc) [0x55a8ff10fadc]
  database_1  | /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3) [0x7ffb34bf3fa3]
  database_1  | /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f) [0x7ffb343e44cf]
  database_1  | 
  database_1  | Trying to get some variables.
  database_1  | Some pointers may be invalid and cause the dump to abort.
  database_1  | Query (7ffa850531b8): SELECT default_template.id, default_template.workflow_id, default_template.workflow_name FROM civicrm_msg_template reserved         LEFT JOIN civicrm_msg_template default_template           ON reserved.workflow_id = default_template.workflow_id         WHERE reserved.is_reserved = 1 AND default_template.is_default = 1 AND reserved.id <> default_template.id         AND reserved.msg_html = default_template.msg_html
  database_1  | Connection ID (thread ID): 23
  database_1  | Status: NOT_KILLED
  database_1  | 
  database_1  | The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
  database_1  | information that should help you find out what is causing the crash.
  
  **Tags:** upgrade, mysql, wsod
  
  > **Comments:**
  > **username_1**: Is mysql 8 a recent upgrade or has it always been mysql 8? Or have some mysql settings changed? That message_template query isn't the world's most efficient but it's not going to blow anything up, and the most records it could return is the number of system workflow templates which is 30 or so. It _does_ need to compare the text of the templates which is not efficient but these templates aren't huge.  
  > **username_0**: It's always been on mysql 8. All my previous attempts to upgrade Civi have ended this same way with a crash. In the past I've just lando destroyed and rebuilt since I've got lots scripted. However this time I wanted to see why it's breaking.  
  > **username_0**: Yeah my message template table has only 67 rows. I agree, that query sure doesn't seem like the likely culprit to be crashing mysql.  
  > **username_0**: The last line of that query seems to be the problem: `AND reserved.msg_html = default_template.msg_html`. The query runs fine without that line. Since the civicrm_msg_template.msg_html field is of type LONGTEXT, perhaps there's some sort of memory issue when performing the = compare.  
  > **username_1**: That's interesting. It's crashing on hash_join_buffer. If you do `show variables` in mysql does it have anything that looks interesting (in particular the ones related to join buffer - on my local I see `join_buffer_size=262144, join_buffer_space_limit=2097152, max_join_size=18446744073709551615`). Or anything related to buffer or hash or memory.  
  > **username_0**: Here's what my local says: `join_buffer_size`=262144, `max_join_size`=18446744073709551615  
  > **username_0**: `optimizer_switch` is set to this very long list of settings, the last of which is `hash_join=on`... index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on  
  > **username_0**: There's no variable listed as `join_buffer_space_limit` on mine.  
  > **username_0**: More... `innodb_adaptive_hash_index`=ON, `innodb_adaptive_hash_index_parts`=8, `bulk_insert_buffer_size`=8388608, `innodb_buffer_pool_chunk_size`=134217728, `innodb_buffer_pool_size`=402653184, `innodb_log_buffer_size`=67108864, `read_buffer_size`=2097152, `key_buffer_size`=402653184  
  > **username_0**: Btw this local is on mysql 8.0.19 (current default for a lando installation)  
  > **username_0**: Perhaps it's an issue with the LEFT JOIN with `hash_join=ON` in mysql 8.0.19 which goes away in later versions... "In MySQL 8.0.20 and later, hash joins are used for outer joins (including antijoins and semijoins)..." https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2023-11-12. Score: 3
  
  Ok, I got the database upgrade to work by **turning OFF hash joins**.
  
  In my version of mysql 8.0.19, I did this by setting `block_nested_loop=off` in the `optimizer_switch` variable. Easy to do in phpmyadmin > server > variables.
  
  Then I went back to the Civi UI to run the db upgrade link that kept breaking:
  <http://mylocal.lndo.site/civicrm/upgrade?reset=1>
  
  ...and this time it gave me the upgrade screen!
  
  [![enter image description here](https://i.sstatic.net/0nrNw.png)](https://i.sstatic.net/0nrNw.png)
  
  ...and the process finished perfectly!
  
  [![enter image description here](https://i.sstatic.net/BFPaY.png)](https://i.sstatic.net/BFPaY.png)
  
  ***Reference 1***
  
  > Block Nested-Loop Flags
  >
  > `block_nested_loop` (default on)
  >
  > Controls use of BNL join algorithm. In MySQL 8.0.18 and later, this
  > also controls use of hash joins, as do the BNL and NO\_BNL optimizer
  > hints. In MySQL 8.0.20 and later, block nested loop support is removed
  > from the MySQL server, and this flag controls the use of hash joins
  > only, as do the referenced optimizer hints.
  
  Source: <https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html#optflag_block-nested-loop>
  
  **Reference 2**
  
  > By default, MySQL 8.0.18 and later employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL and NO\_BNL optimizer hints.
  >
  > (MySQL 8.0.18 supported hash\_join=on or hash\_join=off as part of the setting for the optimizer\_switch server system variable as well as the optimizer hints HASH\_JOIN or NO\_HASH\_JOIN. In MySQL 8.0.19 and later, these no longer have any effect.)
  
  Source: <https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html>
  
  > **Comments:**
  > **username_1**: Good job. There are core unit tests that test the upgrade, among other things, so not sure why it didn't come up there. Most run mysql 5.7 but looks like one variation currently runs 8.0.29 (and at one time was probably 8.0.19), but I don't know what its config is.  
  > **username_0**: Thanks, I really appreciate your input to help figure it out. I plan to upgrade mysql to a newer version and see what happens. Could this be an issue experienced more widely by other mysql users? Anecdotally, I noticed another person on the Mattermost Drupal channel a day or two ago experienced WSOD upgrading from 5.66.1 to 5.67. So perhaps it's a wider issue than just my particular configuration.  
  
  ---
  
  