Beta Branch update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format

update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format

---

- MariaDB 10.1.32 and MariaDB 10.2.2 and higher introduced a new variable for InnoDB which Oracle MySQL already supported for innodb_default_row_format XtraDB/InnoDB Server System Variables. Prior to this MariaDB defaulted to innodb row format = COMPACT but MySQL 5.7.9 and MySQL 8.0 changed the defaults to DYNAMIC Oracle MySQL 8.0 GA Stable Release.
- This update in Centmin Mod 123.09beta01 and newer branches sets in MariaDB 10.1 and 10.2 /etc/my.cnf the new variable which now changes the default row format for InnoDB tables from COMPACT to DYNAMIC, innodb_default_row_format = dynamic. This only applies to new Centmin Mod 123.09beta01 fresh installs so as to avoid any unforeseen changes to existing Centmin Mod user's MariaDB database tables. You can manually convert your InnoDB tables from ROW_FORMAT = COMPACT to ROW_FORMAT = DYNAMIC via ALTER TABLES command if needed. Basic example posted in gist at ROW_FORMAT COMPACT TO DYNAMIC for centmin mod mariadb 10 mysql
- This update doesn't change the base my.cnf templates but instead at initial MariaDB install and MariaDB 10.1 and 10.2 upgrade times does an on the fly addition to existing /etc/my.cnf adding the value to [mariadb-10.1] or [mariadb-10.2] sections of /etc/my.cnf for variable set as, innodb_default_row_format = dynamic. This allows for the variable to only apply to MariaDB 10.1 or 10.2 servers and not MariaDB 10.0 as the variable doesn't exist in version prior to MariaDB 10.1.32 or 10.2.2.

Example in MariaDB 10.1

Code (Text):

[mariadb-10.1]
innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

Example in MariaDB 10.2 - innodb_file_format is commented out as it's deprecated in MariaDB 10.2 as native default in MariaDB 10.2 is Barracuda file format for InnoDB tables

Code (Text):

[mariadb-10.2]
#innodb_file_format = Barracuda
innodb_default_row_format = dynamic
innodb_file_per_table = 1

Continue reading...

123.09beta01 branch

• Branch: centminmod/centminmod
• Commit History: centminmod/centminmod

 

On fresh Centmin Mod 123.09beta01 installs after this update, you can verify if the new innodb_default_row_format MariaDB MySQL server variable is set to dynamic using SSH command below

Code (Text):

mysqladmin var | grep row_format | tr -s ' '

should return dynamic instead of compact. Older Centmin Mod 123.09beta01 installs would return compact instead of dynamic.

Code (Text):

mysqladmin var | grep row_format | tr -s ' '
| innodb_default_row_format | dynamic |

So no more 767 byte limit errors for index length as Centmin Mod MariaDB 10.1 default to Barracuda file format with innodb_large_prefix enabled already. So max InnoDB max index length increases from 767 bytes to 3072 bytes

 

Yes but only applies to database InnoDB tables newly created since. For existing InnoDB tables, you need to do ALTER TABLE command to change it as outlined in gist example link above.

 

Example in gist shows how to generate all alter table commands by querying the
INFORMATION_SCHEMA data and which you can script to run ROW_FORMAT COMPACT TO DYNAMIC for centmin mod mariadb 10 mysql. Google search on how to query MySQL INFORMATION_SCHEMA data on your server

i.e. INFORMATION_SCHEMA databases TABLES table has following columns you can query

Code (Text):

mysql -e "SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES \G;"
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(2048) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8

i.e. to list all InnoDB tables with ROW_FORMAT = Compact and generate ALTER TABLE commands which you can script/run in batches

Code (Text):

mysql -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT = 'Compact' AND TABLE_SCHEMA != 'mysql';" | while read d t e r; do echo "ALTER TABLE $d.$t ROW_FORMAT=DYNAMIC;"; done

will return all InnoDB tables with ROW_FORMAT = Compact excluding mysql system database and generate ALTER TABLE commands. Below server only had one database named test1 with table name t1 with ROW_FORMAT = Compact

Code (Text):

ALTER TABLE test1.t1 ROW_FORMAT=DYNAMIC;

on another server with wordpress install it would return the following

Code (Text):

mysql -N -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT = 'Compact' AND TABLE_SCHEMA != 'mysql';" | while read d t e r; do echo "ALTER TABLE $d.$t ROW_FORMAT=DYNAMIC;"; done
ALTER TABLE wp2872328615dbx_16230.6619_commentmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_comments ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_links ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_options ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_postmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_posts ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_term_relationships ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_term_taxonomy ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_termmeta ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_terms ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_usermeta ROW_FORMAT=DYNAMIC;
ALTER TABLE wp2872328615dbx_16230.6619_users ROW_FORMAT=DYNAMIC;

which is correct as those database tables are currently ROW_FORMAT=Compact

Code (Text):

+-----------------------------------0-----------+----------------+----------------+-----------+------------+--------+------------+--------------------+
| Table Name                       0            | Number of Rows | Storage Engine | Data Size | Index Size | Total  | ROW_FORMAT | TABLE_COLLATION    |
+----------------------------------0------------+----------------+----------------+-----------+------------+--------+------------+--------------------+
| wp2872328615dbx_16230.6619_commentmeta        | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_comments           | 0 Rows         | InnoDB         | 0.02MB    | 0.08MB     | 0.09MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_links              | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_options            | 116 Rows       | InnoDB         | 0.06MB    | 0.03MB     | 0.09MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_postmeta           | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_posts              | 2 Rows         | InnoDB         | 0.02MB    | 0.06MB     | 0.08MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_term_relationships | 0 Rows         | InnoDB         | 0.02MB    | 0.02MB     | 0.03MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_term_taxonomy      | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_termmeta           | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_terms              | 0 Rows         | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_usermeta           | 15 Rows        | InnoDB         | 0.02MB    | 0.03MB     | 0.05MB | Compact    | utf8mb4_unicode_ci |
| wp2872328615dbx_16230.6619_users              | 1 Rows         | InnoDB         | 0.02MB    | 0.05MB     | 0.06MB | Compact    | utf8mb4_unicode_ci |
+----------------------------------------------+----------------+----------------+-----------+------------+--------+------------+--------------------+