InnoDB and Compact

[marklcfc]

I keep reading I shouldn't have compact set for innodb row format. But the only options I can see are Compact or Redundant.

All my tables show like this but I've received no warnings about it in the software. Is this not how it should be though?

 

Edited December 1, 2022 by marklcfc

[teraßyte]

COMPACT format has a reduced length for indexes in tables compared to DYNAMIC. If you're not seeing the option in that list you need to contact your hosting about enabling it.

 

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

Quote

The DYNAMIC row format offers the same storage characteristics as the COMPACT row format but adds enhanced storage capabilities for long variable-length columns and supports large index key prefixes.

The Barracuda file format supports the DYNAMIC row format.

 

Edited December 1, 2022 by teraßyte

[marklcfc]

Does it just need to be enabled or would the database need converting to use it as well?

[Mark H]

Each innodb table within the database would need that setting changed, there's no function to change them all at once.

This process is something you would need to ask your host to assist in doing, since the DYNAMIC option is not appearing to you.

[marklcfc]

2 hours ago, Mark H said:

Each innodb table within the database would need that setting changed, there's no function to change them all at once.

This process is something you would need to ask your host to assist in doing, since the DYNAMIC option is not appearing to you.

Do I need it to be Dynamic? I've not had any issues

[teraßyte]

Sometimes you might get errors like this one below if you're not using DYNAMIC row format (especially on upgrades):

Specified key was too long; max key length is XXXX bytes

[Mark H]

Terabyte is correct.

While it may be working for you now, in the future you will very likely hit that error again, and possibly at the most inconvenient time (e.g. in the middle of an upgrade).

[marklcfc]

Is that likely to happen as I've never seen that error before, nor have I ever been warned about it in the Admin CP.

[teraßyte]

The error usually shows up when adding a new index to a table (so upgrades mainly, as Mark said). The code does have some checks in place so automatically adjust the index length, but it can still fail in some circumstances.

[marklcfc]

Is there something in the latest release that’s causing issues with this as I’m seeing a lot of problems since it was released

[marklcfc]

I've checked with my hosts and I'm apparently using Dynamic already, it's just not showing in phpmyadmin as I'm on an older version of Ubuntu. Should I upgrade from 18 to 20 ubuntu? I don't know whether it's needed.

Edited December 5, 2022 by marklcfc