Sibel

Worth knowing this class of problem is not specific to one database. Anything that stores an ordering derived from an external library has the same exposure when that library changes.

The general lesson: an index is a cache of an ordering, and if the function that produced the ordering changes, the cache is stale. It applies to collations, to some full-text configurations, and to any functional index over a function whose definition you later alter.

That last one catches people regularly and produces exactly the same silent wrong-results symptom.

Two shortcuts worth knowing before writing that query.

The command line client's describe commands show access privileges. Describing a table or a set of objects includes an access privileges column, and there are variants that cover functions and schemas. For a quick look at one object or one schema this is far faster than any query.

A schema-only dump contains every grant. Producing a structure-only dump of the database gives you a file with the complete set of GRANT statements in it. It is not a per-role report, and it is a complete and authoritative record you can search — and it is the thing I actually reach for when auditing, because it cannot miss an object type.

Between those two, most people's real question is answered without building anything.

The leftover — the difference between 8192 and 8000 — is page and row overhead, and it is worth knowing what lives there:

• The page header, which is a fixed size and holds the page identity, type, free space pointers and related metadata.
• The row header, per row, with status bits and pointers.
• The null bitmap, one bit per column.
• The variable-length column offset array, which records where each variable-length value starts.
• The slot array at the end of the page, one entry per row.

Add those and the usable space for row data is a bit over 8000 bytes, which is where the round number comes from. The limit is set so that a single maximum-length value plus the necessary overhead still fits.

This is also why you can define a table whose columns sum to more than the row limit and get a warning rather than an error — it is legal to define, and it fails only if you actually insert a row that does not fit.

Yes, that is exactly the reason, and the history makes it clearer.

The engine stores rows in pages, and a fundamental design rule is that a row must fit on a single page — a row cannot span pages. So the maximum size of any single value is bounded by what can fit in a page alongside everything else.

The 8 kilobyte page was introduced at a specific version, and the maximum length for the variable-length character type went from 255 to 8000 at the same time. Before that the page was 2 kilobytes, and a limit of 255 fitted that era's constraints comfortably.

The double-byte type stores two bytes per character, so 4000 characters is the same 8000 bytes. The two limits are one limit expressed in two units.

So the numbers are not arbitrary. They are the page size minus overhead, rounded down to something memorable.