What is the default table storage format for Swisscom MariaDb Ent?

Question

When creating a table like this

CREATE TABLE `dummy` (
  `userid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `providerid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `provideruserid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and defining a PK like the following:

ALTER TABLE dummy ADD PRIMARY KEY (userid,providerid,provideruserid);

I get this error:

Error: Specified key was too long; max key length is 767 bytes

I think the following are set correctly and therefore most probably not the problem:

innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_large_prefix=ON

Most likely the table storage format is causing issues here, but I'm not able to check the defined default. As per the documentation, the default table storage format for MariaDB (since 10.2.2) is DYNAMIC - if it is DYNAMIC then this would be perfect, but this seems not to be the case.

Does anyone know the default table storage format in Swisscom MariaDB Ent. and why it is not DYNAMIC? (Probably :))

Often the `255` is overkill. `191` will fit. Also 10.2 increases the 767 limit. — Rick James, Nov 10 '17 at 16:26

Sybil · Accepted Answer · 2017-11-09T14:58:36.140

Our version of MariaDB in Prd:

select VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.22-MariaDB |
+-----------------+
1 row in set (0.00 sec)

Quote from MariaDB KB XtraDB/InnoDB Storage Formats

Compact

Compact was the default format until MariaDB 10.2.1, and is suitable for general use if the Antelope file format is used. It was introduced in MySQL 5.0.

In the Compact storage format (as in Redundant) BLOB and TEXT columns are partly stored in the row page. At least 767 bytes are stored in the row; values which exceed this value are are stored in dedicated pages. Since Compact and Redundant rows maximum size is about 8000 bytes, this limits the number of BLOB or TEXT columns that can be used in a table. Each BLOB page is 16KB, regardless the size of the data.

Other columns can be stored in different pages too, if they exceed the row page's size limit.

Our version of MariaDB in Lab (soon to be deployed on Prd). This the relevant line in bosh release.

select VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.26-MariaDB |
+-----------------+
1 row in set (0.00 sec)

How to fix the error ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. on Swisscom Application Cloud (have a look at the diff ROW_FORMAT=DYNAMIC):

MariaDB [stackoverflow]> CREATE TABLE `dummy` (
    ->   `userid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `providerid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    ->   `provideruserid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
    -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)

MariaDB [stackoverflow]> ALTER TABLE dummy ADD PRIMARY KEY (userid,providerid,provideruserid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [stackoverflow]> show index from dummy;
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dummy |          0 | PRIMARY  |            1 | userid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dummy |          0 | PRIMARY  |            2 | providerid     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dummy |          0 | PRIMARY  |            3 | provideruserid | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Here the code for copy and paste:

CREATE TABLE `dummy` 
             ( 
                          `userid`         VARCHAR(255) collate utf8mb4_unicode_ci NOT NULL, 
                          `providerid`     VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
                          `provideruserid` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL 
             ) 
             engine=innodb row_format=dynamic DEFAULT charset=utf8mb4 COLLATE=utf8mb4_unicode_ci;

@fyobor-glebov many thanks for the detailed explanation just a comment to your answer: 'is suitable for general use if the Antelope file format is used'...this is not the case, the the default fileformat is 'Barracuda' — domi, Nov 09 '17 at 15:25

What is the default table storage format for Swisscom MariaDb Ent?

1 Answers1