drupal_install_test.ibd: To start over, you must empty your existing database

In prep for DrupalCon Nashville, I was working on our Drupal Commerce demo sites that we'll be showing off. They have been running in silent mode for some time and recently received an overhaul so they use our demo and out of the box theme for Drupal Commerce, Belgrade. For one of the sites I received the following error: To start over, you must empty your existing database and copy <em>default.settings.php</em> over <em>settings.php</em>. Wait, what? When I ran drush si it dropped all the tables. Apparently, not.

Whenever I would use Drush to reinstall the website, I would get the following error message:


<ul>
<li>To start over, you must empty your existing database and copy <em>default.settings.php</em> over <em>settings.php</em>.</li>
<li>To upgrade an existing installation, proceed to the <a href="/update.php">update script</a>.</li>
<li>View your <a href="http://example.com">existing site</a>.</li>
</ul> 

Now, I was getting ready to pull my hair out. I set up our demos on a Drupal multisite via DrupalVM. All the other sites were installing fine, except this one. So I started debugging.

When I ran SHOW TABLES; in the MySQL command line... there were no tables. When I ran DROP DATABASE db_name ... I got an error. Instead of everything be happy, I got an error like the following:

Error Dropping Database (Can't rmdir '.test\', errno: 17)

I have never seen that. So I did what all proper software engineers do: go to Google, search the error, and see what the hell other people have done when they run into this issue. Luckily I came across a Stack Overflow question and answer https://stackoverflow.com/questions/4584458/error-dropping-database-can…. Basically, this error happens when there is crud data in the database directory. So I checked what was in the database directory (since there were no tables.)


[email protected]:/var/www/drupal$ sudo ls -la /var/lib/mysql/db_name/
total 40
drwxr-x---  2 mysql mysql 20480 Mar 30 18:35 .
drwxr-xr-x 10 mysql mysql  4096 Mar 30 18:20 ..
-rw-r-----  1 mysql mysql 65536 Nov 23 03:54 drupal_install_test.ibd

There was an InnoDB table file called drupal_install_test.ibd. From the MySQL reference

InnoDB's file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.

So running a manual removal fixed everything.

sudo rm /var/lib/mysql/db_name/drupal_install_test.ibd

Turns out this table is part of the Drupal installation process to check it has all the required access it needs to the database. I found the following in lib/Drupal/Core/Database/Install/Tasks.php and it's also in Drupal 7, it seems. The problem is, those, that a bad install and then re-attempted install breaks, bad.


  /**
   * Structure that describes each task to run.
   *
   * @var array
   *
   * Each value of the tasks array is an associative array defining the function
   * to call (optional) and any arguments to be passed to the function.
   */
  protected $tasks = [
    [
      'function'    => 'checkEngineVersion',
      'arguments'   => [],
    ],
    [
      'arguments'   => [
        'CREATE TABLE {drupal_install_test} (id int NULL)',
        'Drupal can use CREATE TABLE database commands.',
        'Failed to <strong>CREATE</strong> a test table on your database server with the command %query. The server reports the following message: %error.<p>Are you sure the configured username has the necessary permissions to create tables in the database?</p>',
        TRUE,
      ],
    ],
    [
      'arguments'   => [
        'INSERT INTO {drupal_install_test} (id) VALUES (1)',
        'Drupal can use INSERT database commands.',
        'Failed to <strong>INSERT</strong> a value into a test table on your database server. We tried inserting a value with the command %query and the server reported the following error: %error.',
      ],
    ],
    [
      'arguments'   => [
        'UPDATE {drupal_install_test} SET id = 2',
        'Drupal can use UPDATE database commands.',
        'Failed to <strong>UPDATE</strong> a value in a test table on your database server. We tried updating a value with the command %query and the server reported the following error: %error.',
      ],
    ],
    [
      'arguments'   => [
        'DELETE FROM {drupal_install_test}',
        'Drupal can use DELETE database commands.',
        'Failed to <strong>DELETE</strong> a value from a test table on your database server. We tried deleting a value with the command %query and the server reported the following error: %error.',
      ],
    ],
    [
      'arguments'   => [
        'DROP TABLE {drupal_install_test}',
        'Drupal can use DROP TABLE database commands.',
        'Failed to <strong>DROP</strong> a test table from your database server. We tried dropping a table with the command %query and the server reported the following error %error.',
      ],
    ],
  ];

I have no idea how the site got into such a corrupt state - and installed. The only relevant issue I could find was https://www.drupal.org/project/drupal/issues/1017050.