Hi,

I missed migrating configuration to the new install and XtraDB engine did not find tables presumably because tablespace is on different filesystem/path with innodb_file_per_table. After I put in correct configuration

So, did you upgrade an existing installation, or did you create a new installation?
What kind of configuration was it that you missed migrating and that you later added?
At which point did you run initial mysql_upgrade?

Please describe step-by-step what exactly you did.

Thanks.

I was upgrading from SuSE SLES 11 SP3 standard install with following relevant configuration:

sql-mode = "TRADITIONAL"
innodb_data_home_dir = /var/lib/innodb/
innodb-file-per-table
innodb-file-format = Barracuda
innodb_buffer_pool_size = 47G
log-error = /var/log/mysqld.log
slow-query-log-file = /var/log/mysqld-slow.log
slow-query-log = on
innodb-flush-method = O_DIRECT
skip-external-locking
skip-name-resolve

After installing MariaDB 10.0.9 I forgot about the custom InnnoDB tablespace path, started MariaDB and ran mysql_upgrade, which informed me it couldn't find shared tablespace for the individual InnoDB files. I replaced the original configuration, restarted MariaDB and re-ran mysql_upgrade with --force. The .ibd files for the missing tables exist, but apparently they're not in the correct shared tablespace showing with "Table 'mysql.innodb_index_stats' doesn't exist in engine" etc. There is a related bug entry at http://bugs.mysql.com/bug.php?id=67179

I'm guessing I have to either delete the missing tables and possibly run mysql_install_db, or possibly run ALTER TABLE ... IMPORT TABLESPACE for them, though I was expecting mysql_upgrade --force to do it as those tables were added in the upgrade.

Oh also I'm using the official MariaDB-10 RHEL5 packages on the SuSE-11.3 machines, MariaDB-10.0.10-rhel5-x86_64-server.rpm currently. On other identical servers that hasn't caused problems, I just forgot that this one had shared tablespace on different RAID, and forgot to move the configuration before upgrading, which is presumably the cause of the problem.

Thanks for the details, it is much clearer now.
I don't think that much can be done here.
Please consider what really happens with the system tables during your scenario:

• your pre-upgrade system tablespace sits in /var/lib/innodb/.
• you upgraded and started the server without pointing at /var/lib/innodb. It could not find the old innodb system tablespace, so it created a new one in the datadir.
• you ran mysql_upgrade, it created system tables; a table information is spread over three files: .frm file (table definition), .ibd file (table data), and a chunk of information in innodb system tablespace. So, for example for gtid_slave_pos table, mysql_upgrade created datadir/mysql/gtid_slave_pos.frm, datadir/mysql/gtid_slave_pos.ibd, and also updated innodb system tablespace which is currently in the datadir.
• then you shut down the server and restarted it with the option pointing at the pre-upgrade system tablespace in /var/lib/innodb. So, now you still have .frm file and .ibd file, but the system tablespace does not have any information about the newly created tables.
• you re-ran mysql_upgrade with --force. If there had not been any trace of the table, it would have re-created it. But since you have the .frm file, the server thinks there is the table (you could see it for example if you ran SHOW TABLES IN mysql via MySQL client. We cannot not make mysql_upgrade re-create an existing table (even when it's technically possible, which is not quite the case here), it would have been a disaster, because important data could have been lost.
  So, mysql_upgrade leaves the table be. But the .frm file points at InnoDB as the owner of the table, while InnoDB has no information about the table since you discarded the system tablespace where it was written into. So, the table cannot be used.

I think in this case it is reasonable to require manual intervention from the user, since manual intervention caused the trouble in the first place. The easiest remedy would be to remove .frm and .ibd files from the file system. Then mysql_upgrade --force would have re-created the tables without a problem.

The MySQL bug report from the link that you provided shows that Oracle also thinks there should be no automated recovery. As you can see, it describes two scenarios, and the solution for the Windows part was not to damage the table space in the first place, and for the Linux part – not to forget to point at the right data location.

I will close the report for now, please comment to re-open if you disagree.