2

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.

  1. 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.
  2. 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.<br />
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.

enter image description here

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.
11
  • 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.
    – Demerit
    Nov 10, 2023 at 0:03
  • 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.
    – Justin657
    Nov 10, 2023 at 4:17
  • 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.
    – Justin657
    Nov 10, 2023 at 4:19
  • 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.
    – Justin657
    Nov 10, 2023 at 21:50
  • 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.
    – Demerit
    Nov 11, 2023 at 19:00

1 Answer 1

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

...and the process finished perfectly!

enter image description here

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

2
  • 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.
    – Demerit
    Nov 12, 2023 at 18:13
  • 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.
    – Justin657
    Nov 12, 2023 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.