All we need is an easy explanation of the problem, so here it is.
I am trying to run this query on a MySQL database:
alter table table_name alter column_name set default 6160
The console tells me:
Failed to delete sdi for database_name.table_name in
database_name/table_name due to missing record.
error.log file tells me:
[InnoDB] sdi_delete failed: Record Doesn’t exist: tablespace_id: 111111 Key: 1 222222
Note that the ID of 111111 and Key 222222 here are made up. There is a 1 after "Key:" and before 222222 though.
So I figured out I can find out the table it’s referring to, using this query:
select * from information_schema.INNODB_TABLESPACES_BRIEF where space = 111111
And sure enough, the result gives me
database_name/table_name as the first error message says.
But there are no constraints on this table other than the primary key. And there are not 6-figures worth of rows in it, so the key it’s printing out can’t refer to a value in the
id column in my table.
What is Key: 1 222222 referring to? How can I find out which specific record it’s referring to?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The SDI key is not referring to anything in your table; it’s the key of the Serialized Dictionary Information record for the table itself. SDI is new for MySQL 8, so if your table(space) was created by an earlier version, the record would not exist (and would probably be created after a successful DDL statement against the table).
If it existed, you would be able to use the
ibd2sdi utility to retrieve the table information by passing it the
--type (1) and
--id (222222) of the record.
The meaning of fields in the warning message can be gleaned from the source code:
ib::warn(ER_IB_MSG_11) << "sdi_delete failed: Record Doesn't exist:" << " tablespace_id: " << tablespace_id << " Key: " << ib_sdi_key->sdi_key->type << " " << ib_sdi_key->sdi_key->id; // Emit warning, and report missing record error, but do not // assert since this situation can occur when upgrading from a
@mustaccio answered the question as I presented it, but if you are finding this question and wondering what to do to fix the issue so your DDL statement can run, what worked for me is:
check table table_name for upgrade
After I ran that, I was able to successfully run my DDL statement.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂