4

I ran the following commands from the cli in MacOS Monterey V 12.4:

brew update 
brew upgrade

Upon doing so, MySQL was upgraded from 8.0.30 to 8.0.31. MySQL was running in the background during the upgrade.

MySql would not restart, so I rebooted the Mac as a first attempt to solve the issue. When I run mysqld restart I get the following list of messages:

2022-12-05T18:28:17.528347Z 0 [System] [MY-010116] [Server] /usr/local/Cellar/mysql/8.0.31/bin/mysqld (mysqld 8.0.31) starting as process 19796
2022-12-05T18:28:17.530983Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2022-12-05T18:28:17.536857Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-05T18:28:17.756641Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-05T18:28:18.381834Z 4 [System] [MY-013381] [Server] Server upgrade from '80030' to '80031' started.
2022-12-05T18:28:29.775399Z 4 [Warning] [MY-011836] [InnoDB] sdi_delete failed: Record Doesn't exist: tablespace_id: 1 Key: 1 4049
2022-12-05T18:28:29.796888Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2018, 2022, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- Alters the sys_config table for upgrades -- ALTER TABLE sys_config CHARACTER SET utf8mb4; ' failed with error code = 3958, error message = 'Failed to delete sdi for sys.sys_config in sys/sys_config due to missing record.'.
2022-12-05T18:28:29.798728Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2022-12-05T18:28:29.798742Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-12-05T18:28:30.135378Z 0 [System] [MY-010910] [Server] /usr/local/Cellar/mysql/8.0.31/bin/mysqld: Shutdown complete (mysqld 8.0.31)  Homebrew.
There are a few StackOverflow questions regarding the error sdi_delete failed: Record Doesn't exist: tablespace_id: 1 Key: 1 4049, but they all seem to be related to an attempted SQL command that is performed while the server is actually running.

The data directory /usr/local/var/mysql contains the following files:

#ib_16384_0.dblwr       ca-key.pem              private_key.pem
#ib_16384_1.dblwr       ca.pem                  public_key.pem
#innodb_redo            client-cert.pem         mysql               server-cert.pem
#innodb_temp            client-key.pem          mysql.ibd           server-key.pem
My-Mac.local.err        mysql_upgrade_info      ib_buffer_pool      sys
auto.cnf                ibdata1                 undo_001            undo_002
binlog.index            localhost.err           performance_schema

plus all the database names/data that I did not include in the list above.

I have created a backup of the mysql directory and tried deleting the mysql_upgrade_info file, and the #innodb_redo directory, but that didn't help resolve the problem.

What might I do to get the mysql server past the sdi_delete error so the upgrade will complete and the server will start?

1

1 Answer 1

0

The story in my case was that mysql upgrade was run when the system was lacking disk space. The apt upgrade failed, and the system was restarted, before the space was made available, which stopped mysql during the upgrade process.

It was not possible to start the upgrade because the mysqld was SIGKILLed, not with graceful shutdown and with innodb_fast_shutdown = 0 (instead of 1, the default).

For data recovery:

  • we stopped mysqld service
  • backup /var/lib/mysql
  • removed /var/lib/mysql/ib_logfile{0,1}
  • we configured innodb_force_recovery = 6 Warning: Do not set this more than 3 unless you read and understand the documentation.
  • we patched MySQL so it will continue the upgrade without running ALTER TABLE sys_config CHARACTER SET utf8mb4. See below for details.
  • we run mysqldump, which created a partial dump and mysqld crashed.
  • we run it again only for specific DB schemas and tables, and some of them crashed mysqld
  • we run select a, b, c from ... for remaining tables, choosing the columns which are not crashing mysqld
  • we run select * from ... where id=... for the same tables to squeeze more data
  • we used an just initialized /var/lib/mysql then alter table ... discard tablespace followed by copy of the tablespace file (/var/lib/mysql/myschema/tables.ibd) then alter table ... import tablespace to squeeze even more data

Here is the error, formatted for readability.

2022-12-05T18:28:29.796888Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement'
  -- [Copyright stuff] --
  -- Alters the sys_config table for upgrades --
  ALTER TABLE sys_config CHARACTER SET utf8mb4; '
     failed with error code = 3958,
     error message = 'Failed to delete sdi for sys.sys_config in sys/sys_config due to missing record.'.
 2022-12-05T18:28:29.798728Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
 2022-12-05T18:28:29.798742Z 0 [ERROR] [MY-010119] [Server] Aborting

To skip the failing ALTER TABLE sys_config CHARACTER SET utf8mb4 and upgrade the tables, we temporarily patched the mysql server:

root@myserver:~# diff <(hexdump -C /usr/sbin/mysqld) <(hexdump -C /usr/sbin/mysqld-patched)
2820802c2820802
< 02b36610  64 65 73 0a 2d 2d 0a 41  4c 54 45 52 20 54 41 42  |des.--.ALTER TAB|
---
> 02b36610  64 65 73 0a 2d 2d 0a 2d  2d 20 45 52 20 54 41 42  |des.--.-- ER TAB|
4018508,4018509c4018508,4018509
< *
< 040205f8
---
> 040205f0  00 00 00 00 00 00 00 00  0a                       |.........|
> 040205f9
root@myserver:~# dpkg-query -S /usr/sbin/mysqld
mysql-server-core-8.0: /usr/sbin/mysqld
root@myserver:~# dpkg-query -l mysql-server-core-8.0
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                  Version                 Architecture Description
+++-=====================-=======================-============-=================================
ii  mysql-server-core-8.0 8.0.34-0ubuntu0.20.04.1 amd64        MySQL database server binaries
root@myserver:~# 

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.