'MySQL 8 - DROP or ALTER Causes ERROR 3664 (HY000): Failed to set SDI

ERROR 3664 (HY000): Failed to set SDI 'MyDatabase.MyTable' in tablespace 'mydatabase/mytable'.

I run into this error whenever trying to DROP a Database or ALTER a Table. I am unable to delete or alter any Table that I've created.

Now, what's really interesting is that these errors only occur after restarting MySQL and a subsequent login (by root user) to MySQL. Here's the pattern:

  1. Login to MySQL as root user.
  2. Create a Database and create Tables.
  3. DROP the Database or ALTER Tables with no issues :)
  4. Exit MySQL
  5. Restart MySQL (stop then start)
  6. Login to MySQL as root user.
  7. Get ERROR 3664 when attempting to DROP the Database or ALTER Tables I previously created, hindering any DROP or ALTER :(

It seems that the restarting of MySQL allows it to recognize the new Database & Table changes and update some kind of SDI (Serialized Dictionary Information) metadata associated with my InnoDB Database Tables and Tablespace I want to change. Somehow this recognition of SDI info is hindering my ALTER and DROP commands. Could this be a bug in MySQL? Or, could my root user not have permission to run commands that modify SDI data? (Although, the documentation says that SDI data is modified by an internal API.)

This happens to me EVERY TIME I create a Database and Tables. So, my very crippling workaround for this error:

  1. Manually delete the data files associated with the Database in the data directory.
  2. Call DROP DATABASE IF EXISTS MyDatabase; (may need to log out/in a couple times, or restart MySQL and try to access tables with SELECT statements for this to finally work)
  3. Recreate the Database and Tables needed.

Any help would be greatly appreciated! Thank you!

I'm running MySQL 8.0.11 Community Server on a Mac.

The same issue with no solution: Unable to drop database mysql: ERROR 3664 (HY000)

mysqlmysql-8.0


Solution 1:[1]

I and several of my students have been seeing this issue crop up more and more.

As a workaround I have found if you go in and do an analyze table on all the tables in the problem database it will fix this error until the next time you restart the server.

Solution 2:[2]

This is a related patch which is in mysql-8.0.11:

https://github.com/mysql/mysql-server/commit/261981bdf42c110f08f98ad2cf84ce6fdef1949e

sdi_debug.result seems to indicate that

SET GLOBAL DEBUG = '-d, sdi_delete_failure';

is needed to overcome the issue.

Solution 3:[3]

If you empty all of the tables, you can then drop the schema.

Interestingly, this statement works if using a PDO in PHP

$conn->exec("DROP DATABASE IF EXISTS $dbname");

Solution 4:[4]

Problem is related to foreign keys. This statement fails:

CREATE TABLE `lottery` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `club_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `details` text,
  `open_date` date DEFAULT NULL,
  `close_date` date DEFAULT NULL,
  `status_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_lottery_club` (`club_id`),
  CONSTRAINT `fk_lottery_club` FOREIGN KEY (`club_id`) REFERENCES `club` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But if I remove the KEY and CONSTRAINT clauses, it works.

CREATE TABLE `lottery` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `club_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `details` text,
  `open_date` date DEFAULT NULL,
  `close_date` date DEFAULT NULL,
  `status_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After the create I then have to add those keys back using ALTER TABLE.

Annoying to say the least.

Solution 5:[5]

FWIW, I was getting the same "Failed to set SDI" error when attempting:

ALTER TABLE 'MyDb.MyTable' ADD COLUMN 'myCol' VARCHAR(255)

In my case the table engines were a mix of InnoDB and MyISAM. They should have all be InnoDB and when I switched them and restarted the MySQL server, things seemed to work.

There were a variety of .sdi files in MySql's data directory (/usr/local/mysql/data/MyDb/ on my machine)... which is just a simple json file with metadata related to a specific table. These SDI files only seem to exist for MyISAM tables.

Before I realized this, I figured the SDI file for the specific table must be missing, or something, and it turns out that mysql has an ibd2sdi utility in its bin directory that can create an SDI file from a tables ibd files (which are also in the data directory).

If you need to stick with MyISAM it might be worth having a look at that. Be careful about modifying the contents of an SDI file, though, as I ran across this quote in the MySql worklogs:

Great care must be taken when doing this - changes which are not compatible with what is stored in the .MYD and .MYI files, like changing the data type of a column, would likely be disastrous and must be avoided.

Don't know if this is helpful, but thought I'd share.

Solution 6:[6]

What worked for me was:

check table table_name for upgrade

After that, I could run my DDL statement without error.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 kyru
Solution 2 Xypron
Solution 3 pbonner
Solution 4 Alec Smythe
Solution 5 TheMadDeveloper
Solution 6 Sammy Larbi

Related Questions