MySQL errors of my own making

Trying to break MySQL to give my colleagues some stuff to exercise their skills on. But it’s going… so so.

I tried to reduce the size of the file ibdata1:

innodb_data_file_path = ibdata1:1M

No go. Turns out the minimum size is 5M.

2021-03-26 22:49:37 0 [ERROR] InnoDB: Tablespace size must be at least 5 MB

innodb_data_file_path = ibdata1:5M

Nope:

2021-03-26 22:50:08 0 [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 768 pages than the 320 pages specified in the .cnf file!

Well yeah… I want it to be smaller. Seems I have to enable autoextend again: https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html. Sounds weird. Well, yeah. That only makes MariaDB ignore my 5MB limit and go with the old value of 12M. Nice.

Oh, I also encountered a problem with MariaDB wanting to have 32000 open files with a small tablespace:

22:49:25 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32184)

Sigh… Had to modify the systemd file /etc/systemd/system/mysql.service to say

LimitNOFILE=65536

And then reload systemd:

systemctl daemon-reload
systemctl restart mysql

Then I realized it was innodb_log_file_size that I wanted to change. Good times.