MySQL Bugs: #96497: The Auto-extending innodb_system data file './ibdata1' is of a different size

Bug #96497	The Auto-extending innodb_system data file './ibdata1' is of a different size
Submitted:	10 Aug 2019 7:31	Modified:	10 Aug 2019 19:18
Reporter:	Maurice Volaski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I had the following in my my.cnf, which I inherited

innodb_data_file_path = ibdata1:10M:autoextend

I took it out, and mysql freaked out with:

The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

How to repeat:
Put this line
innodb_data_file_path = ibdata1:10M:autoextend in cnf and then launch mysql. Quit and take out the line.

Suggested fix:
There's a ton going on here that's wrong.

First off, let's examine the error message itself. Where does it get 640 from? The message should explain how it's calculated that, and then it lies about what's specified in cnf. Nothing was specified there. So where does 768 come from, from the default 12M size? Second, it doesn't say why this matters. Third, it doesn't say how to address it. If I lose the original line in the cnf, it's not clear how I could generate it back again. I might be stuck having to blow away the whole data infrastructure and restore from backup.

Now the documentation tells us that this line
innodb_data_file_path = ibdata1:10M:autoextend
will create 10M file at the first launch of mysql and will autoextend as necessary by 10M each time. So that's fine. If the line is not present, the default would be to extend by 12M. But somehow if one has this config line set to 10M at the outset, then removes the line on a subsequent launch, that somehow triggers this error. This doesn't make sense to me. The only change would be to extend the file by 12M instead of 10M. Why is that catastrophic?

If there is some hidden reason why 10M can't be summarily changed to 12M, then the error message should reflect this and then tell the user how to resolve it.

This is not a bug,  but completely expected.   
The default is:   ibdata1:12M:autoextend

So when you create a 10MiB file at first.  The server looks for a 12MiB file after you remove the my.cnf option.  Error here is expected.

640 pages is 10MiB. A page is 16KiB in size.

>
>  than specified in the .cnf file: initial 768 pages,
>

Here, you need to put:

768 * 1024 * 16 = 12582912
ibdata1:12582912:autoextend

or 12582912/1024/1024 = 

ibdata1:12M:autoextend

I find it ironic that you would not consider this a bug given your good effort to explain it here, an explanation that is not present in the cryptic error message itself. This bug report will have to serve an antidote to this deficiency.

Furthermore, as I stated the docs don't make it clear this is expected behavior. It ought to say that the initial auto-expansion size is fixed and the engine needs whatever it's initially configured at to remain in the configuration. (Why this is the case is also unexplained).

The "Resizing the System Tablespace" section in the documentation has been updated to cover the failure scenario described above.

https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html

Thank you for the bug report.