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

Question

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)

Answer 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.

Answer 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.

Answer 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");