MySQL Bugs: #98958: Creating triggers causes mysql.ibd file to grow

Bug #98958	Creating triggers causes mysql.ibd file to grow
Submitted:	16 Mar 2020 10:13	Modified:	26 Mar 2020 14:04
Reporter:	Thomas Kosel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	trigger

Description:
When triggers are created, they might be logged or something like that into the mysql.ibd file.

In my case it happened on a testing environment, where the testsystems database is oftenly emptied for test separation. There I introduced some larger triggers.

How to repeat:
1. create tables
2. repeatedly create multiple large triggers
3. check mysql.ibd file size and existence of triggercode

Testscript, in case the bug does not get visible after first run, rerun the loop.

Attachment: test_mysql.sh (application/x-shellscript, text), 508 bytes.

Initial queries for the test script

Attachment: init_tables.sql (application/sql, text), 338 bytes.

creation of triggers to be executed in a loop

Attachment: create_triggers.sql (application/sql, text), 247.88 KiB.

Hi Mr. Kosel,

Thank you for your bug report.

However, I fail to see what is the problem here. All stored routines and triggers are saved in the data dictionary. And data dictionary is exactly in the tablespace which you have specified.

Hence, what is the problem ????

The problem here is, that the trigger is not just stored once, it seems that it is somehow leaking into the database file.
For example I had the trigger many thousand times in the file.
Please try the script, that I provided.

Hi Mr. Kosel,

I have ran your script. And, indeed, tablespace file size is increasing. However, it is a normal behaviour for InnoDB tablespaces.

Also, tablespace is not leaking, since it is the principal location for all stored routines, triggers, data dictionary, etc, etc ........

Please take a look at the count included in the grep and rerun the loop part some times.
This number grows too, and the database file affected is the mysql file, not the file of the database, for which the trigger is created.
The script should have no effect at all to this database file. (Except of course, that the trigger definition has to be stored somewhere once, not multiple times.)

Hi,

What I get with your script and what you describe is actually the expected behaviour.

This behaviour is partially explained in our Reference Manuals and in Internals Manual.

Those can both be found on http://dev.mysql.com.

Most of it, however, is not described, due to the security reasons.

Hi,

sorry, I noticed, that the script seems to be a little faulty (visible when the 2>/dev/null is removed), when the sql files are executed one by one, it was working.
I will update the script file for this.

fixed error for second trigger

Attachment: create_triggers.sql (application/sql, text), 247.92 KiB.

What happens when I execute this:
➜  ~ docker exec -i trigger_bug sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < init_tables.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
➜  ~ for i in {1..1000}
do
    docker exec -i trigger_bug sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" 2> /dev/null' < create_triggers.sql
done
➜  ~ sudo ls -l /tmp/mysql/mysql.ibd
sudo grep -c 'something_unique' /tmp/mysql/mysql.ibd
-rw-r----- 1 guest-pp3jsp guest-pp3jsp 562036736 Mär 16 17:22 /tmp/mysql/mysql.ibd
4004

Hi,

This is explained in:

* Reference Manual

* Internals Manual

* Source code Manual

You can find them all on dev.myslq.com on "Download" pages.

Hi,

Sorry, but this answer is way too generic to be helpful for this very specific question.
I invested some time to understand it already, please give me a hand on this.
Could you point us to a change log entry that highlights the change in behavior from MySQL 5.7 to 8?
We were not able to find that change in behavior documented anywhere.
Is there a way to workaround the issue?

Kind Regards
Thomas

Hi,

No there is no workaround and this is expected behaviour. 

InnoDB SE is optimised for production environment. In that environment you do not create a drop same trigger thousand times.

There is no changelog entry. Simply, MySQL 8.0 keeps all tables in a single InnoDB tablespace. Unlike earlier versions which kept data in separate MyISAM tables.

And space management of the InnoDB SE has not changed since 2002.

File management for InnoDB SE is mostly explained in the internals manual.

Hi,

thanks, that explains what is happening.
I tried to circumvent the issue by enabling innodb_file_per_table = 1, so I could use optimize table, but it is not possible to do this for the internal mysql database correct?
Btw. of course the test script I uploaded is supposed to make the issue visible.
That's no production use case.

Kind Regards
Thomas

You are welcome .....