0

We recently tried setting innodb_undo_tablespaces=2 for our mysql 5.7 server. But during the restart the server returned with:

[ERROR] InnoDB: Expected to open 2 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

It turns out this is already documented : "The number of innodb_undo_tablespaces must be set prior to initializing InnoDB. Attempting to restart InnoDB with a greater number of undo tablespaces than you specified when you first created the database will result in a failed start and an error stating that InnoDB did not find the expected number of undo tablespaces."

What does "first created the database" mean? Does the documentation mean a fresh installation of mysql where there are no databases within the server? Is there a way to bypass this requirement?

2

Probably refers to "fresh installation".

The workaround is painful...

  1. Dump all the data.
  2. Stop mysqld.
  3. Blow away ibdata1 and iblogs.
  4. Change the setting in my.cnf.
  5. Restart. (This should feel like a 'fresh' install.)
  6. Reload the data.

Do you have some indication that changing that setting will help you significantly? If so, please explain. It is a very new feature, and is there for a reason, but not many users need it.

Also

from the 5.7 documentation, - dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release." and "The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance."

– Thanks to @OutfastSource

  • We keep all innodb tables in their own files but our ibdata1 file reached to about 50GB. I want to give it a fresh start. And I really like the new truncate option for undo tablespace. Thank you for the answer! – Haluk Feb 4 '16 at 23:23
  • Hmmm... Did you run a huge UPDATE on, say, a 50GB table? – Rick James Feb 4 '16 at 23:36
  • Yes definitely... – Haluk Feb 5 '16 at 2:18
  • For anyone stumbling upon this, here are some important notes from the 5.7 documentation, "The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release." and "The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance." dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html – Software Prophets Jan 16 '19 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.