MySQL – Why is Default Width for Signed MEDIUMINT 9 Instead of 8?

The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses. (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)

I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl $userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code.

The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table? The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.

(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)

There's a workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.

User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection. Their value is independent of database transactions, but never visible from one connection to another.

$dbh->do(q[ SET @app_user_id = ? ], undef, $userid);

Until you disconnect from this connection, the variable @app_user_id (which I just made up, it's just a variable name, but the @ designates it as a user defined variable) will contain the value you stored there unless you set it to something else. Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of using CURRENT_USER() in a trigger, use @app_user_id.

This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.

So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html

So, what is supposed to be the best practice if both are not valid because criteria are balanced?

The criteria aren't likely balanced. They're almost never going to be balanced. The cost of the extra join to look up the surrogate key (that's the MEDIUMINT; a surrogate key is an opaque value, typically implemented as an auto-increment in MySQL, which has no intrinsic meaning outside the database and is ideally invisible to the user) is going to be negligible compared to the large storage, larger indexes (which means fewer rows per index page, which means more RAM for the same performance).

And cascading updates across several tables? Yikes. That means unnecessary I/O, bringing with it more row locks, index locks, gap locks, more potential cases for deadlocks...

No, I don't think the two apparent alternatives are likely to be genuinely balanced. Go with the surrogate key.

There are database purists out there who don't like surrogate keys, and if memory serves me, these may be the same people who spend too much time thinking about about theoretical databases and don't like NULL. Don't listen to those people too extensively, brilliant theoreticians they may be. (Dare I add, "caveat lector").