Please provide ibdata1 file

dcorp, I see that this issue was also filed as MDEV-13556, which is only linking to one issue (MDEV-10977) instead of the promised two.

The MDEV-10977 fix is present in MariaDB 10.2.4. I updated that ticket.

Could it be that the ibdata1 file was originally created with an older version of MySQL than 5.5? InnoDB used to write uninitialized garbage to unused bytes in data pages. I fixed that in some version of the InnoDB Plugin for MySQL 5.1.

I suspect that there are some previously unused bytes in page 7 of the system tablespace are garbage instead of 0. These bytes were repurposed in MariaDB 10.1 to identify encryption.

Could you please attach a copy of the first 8 pages of your ibdata1 file?

dd bs=16384 count=8 if=ibdata1 of=/tmp/ibdata_first_8.bin

These pages should not contain any user data or schema information.

Could you please also try to update to the most recent MariaDB 10.1 instead of 10.2.7? I assume that it should fail as well, but perhaps with different symptoms.

If my suspicion about the uninitialized garbage bytes is correct, perhaps we should introduce a parameter that tells InnoDB that all data is unencrypted, no matter what the previously unused bytes say.

I can reproduce it locally. So far the scenario is as described initially, probably some of the steps are not necessary:

• start new clean 5.5 instance with all defaults;
• run

  create table ti (i int);

  insert into ti values (1),(2);

  create table tm (i int) engine=MyISAM;

  insert into tm values (1),(2);

• shut down the server normally
• start 10.2.7 with all defaults on the same datadir
• run mysql_upgrade
• shut down the server normally
• start the same 10.2.7 with

  --plugin-load-add=file_key_management --file_key_management_filename=`pwd`/mysql-test/std_data/keys.txt --file_key_management_encryption_algorithm=aes_cbc --innodb-encrypt-tables --innodb-encrypt-log --innodb-encryption-threads=4

• run

  ALTER TABLE tm ENGINE=InnoDB;

• shut down the server
• start the server with the same encryption options as before
  => failure

The attached mdev13557_ibdata.tar.gz contains four ibdata1 files:

• ibdata.5.5 - after 5.5 shutdown
• ibdata.10.2.7.after_mysql_upgrade_before_encryption - after first 10.2.7 shutdown (no encryption yet, just mysql_upgrade)
• ibdata.10.2.7.after_encryption_before_restart - after second 10.2.7 shutdown (encrypted and the MyISAM table converted)
• ibdata.10.2.7.after_failure_upon_restart - after failure

Upd: Instead of converting a MyISAM table to InnoDB, a new InnoDB table can be created, same result (no special ALTER magic)

Hi Marko,

The ibdata_first_8.bin contains some identifiable information that I'd rather not be posting here.
I'm happy to pass it along in private however.

Of note, the 5.5.52 release did have some innodb tables that were brought forward.

After the upgrade and enabling encryption, the database does still start/stop without a problem.
However, for this test I created a new database and table as MyISAM, after enabling encryption then stopped the service and on startup it failed again.

Could it be that the ibdata1 file was originally created with an older version of MySQL than 5.5?

Yes, the original database was created on mysql 5.1, moved as a package to 5.5, then via a dump and import again to 5.5 on a newer server.

I'll test 10.1.latest and post the results.

Update: I've attached the startup errors (10.1.26_error.txt) when doing the upgrade+encrypt on version 10.1.26.
Same error message as 10.2

Hi dcorp, thank you for your feedback. Because our QA engineer elenst repeated the problem with a freshly created 5.5 database, we can conclude that the impact is much higher than I initially thought. We can proceed without any copy of your data.

Yes, the original database was created on mysql 5.1, moved as a package to 5.5, then via a dump and import again to 5.5 on a newer server.

SQL dump and import into MySQL 5.5 or later would properly initialize all InnoDB data pages. If all previous upgrades were made in place, using original binary files potentially dating back as far as MySQL 3.23.49, then there could be problems due to previously unused but written-as-garbage bytes that have been repurposed in later versions of InnoDB. I think that we can rule it out in this case. InnoDB stopped writing uninitialized data to files in some version of the InnoDB Plugin for MySQL 5.1. All 5.5 versions should be safe in this respect.

10.1 : https://github.com/MariaDB/server/commit/7049b864bd8eaa0de2b832868ed0735f201c97c0

I wrote some comments to the 10.1 patch. It seems to me that this could also fix
MDEV-13488 InnoDB writes CRYPT_INFO even though encryption is not enabled
If that is the case, please mark this bug report as a duplicate of that one.

commit 352d27ce36bc807f0c3c803e192ad1851f7f86a0
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Aug 29 14:23:34 2017 +0300

MDEV-13557: Startup failure, unable to decrypt ibdata1

Fixes also MDEV-13488: InnoDB writes CRYPT_INFO even though
encryption is not enabled.

Problem was that we created encryption metadata (crypt_data) for
system tablespace even when no encryption was enabled and too early.
System tablespace can be encrypted only using key rotation.

Test innodb-key-rotation-disable, innodb_encryption, innodb_lotoftables
require adjustment because INFORMATION_SCHEMA INNODB_TABLESPACES_ENCRYPTION
contain row only if tablespace really has encryption metadata.

fil_crypt_set_thread_cnt: Send message to background encryption threads
if they exits when they are ready. This is required to find tablespaces
requiring key rotation if no other changes happen.

fil_crypt_find_space_to_rotate: Decrease the amount of time waiting
when nothing happens to better enable key rotation on startup.

fsp_header_init: Write encryption metadata to page 0 only if tablespace is
encrypted or encryption is disabled by table option.

i_s_dict_fill_tablespaces_encryption : Skip tablespaces that do not
contain encryption metadata. This is required to avoid too early
wait condition trigger in encrypted -> unencrypted state transfer.

open_or_create_data_files: Do not create encryption metadata
by default to system tablespace.

10.2 version requesting new review as this is somewhat different compared to 10.1.
https://github.com/MariaDB/server/commit/fc3708c2aebddbdd49948160024b8765c47768d9