Limitations of MySQL 8

A coin has two sides; similarly, benefits of also using MySQL 8 would come along with a few limitations. Let us walk through a few areas of MySQL 8 now.

Number of tables or databases

The number of databases or tables are not a limitation for MySQL 8; however, the operating system file limit can be a limitation for MySQL 8. Storage Engine InnoDB is allowed to scale up to four billion tables as its peak number.

Table size

You may hit maximum table size limit, which is not restricted from MySQL 8; however, it may be because of operating system filesystem limits.

Joins

In a single join, one can use 61 tables, which can be referred. It is also applicable to the tables that are referenced in view definition. Joins that are part of subqueries and views are also considered to be part of the limitation.

Windows platform

There are few limitations when you have MySQL 8 used on the Windows platform:

• Memory: 32-bit architecture has limitation to use only 2 GB of RAM for a process.
• Ports: In case you have a high number of concurrency you might come across Windows platform limitation of having 4000 ports available for client connections in total.
• Case-insensitivity: The Windows platform doesn't have case sensitivity, which is why tables and databases need to be deliberately managed for case-insensitivity.
• Pipes: |, generally referred as pipe signs, they are not fully supported in Windows. You might come across them in a few scenarios while doing database administration activities.
• Pathname separator: MySQL 8 escape character is \, which is the pathname separator for Windows. Hence while using path separator you can double slash as "\\" as an alternative for a pathname separator.

Table column count

The table column for each table in MySQL 8 has a limit of 4096 columns. It might vary based on a few other factors for columns count limit, as stated in the following section.

Row size

MySQL tables have a limit of 65,535 bytes for a row, although storage engines such as InnoDB are capable of supporting larger chunks.

InnoDB storage engine

Limitations on InnoDB storage engine are what we will talk about a bit more specifically as InnoDB now with MySQL 8 will play a prominent role.

Limitations of InnoDB storage engine

We will have a quick glance at a few of the limitations of InnoDB storage engine:

• The number of indexes supported can be maximum 64 for a table
• For tables that use compressed or dynamic row format; 3072 is the index key prefix length limit
• For tables that use compact or redundant row format; 767 is the index key prefix length limit
• Total columns in a table, which includes virtual generated columns, are limited to a maximum of 1,017
• 16 columns is the maximum permitted for multi-column indexes
• The combined InnoDB log file size cannot exceed 512 GB
• Maximum table size supported by InnoDB is 256 TB
• AdminAPI is not supported while using unix socket connections
• Multi-byte characters might give you unreliable aligned columns while formatting of results in InnoDB clusters

Restrictions

We will now have a quick glance at a few of the restrictions of the InnoDB storage engine:

• Delete from tablename: It doesn't actually delete the complete table, instead it deletes each row of the table one after another.
• Show table status: It wouldn't provide you accurate data all the time; it provides estimates.
• When counting rows, the number of rows provided by count(*) is not accurate because of concurrency; it would count only those counts visible to transactions currently available.
• If there is multiple analyze table queries executed, later one will be blocked until the first one gets completed.
• InnoDB keeps an exclusive lock on the index at the end associated with the auto_increment column.
• In a case the auto_increment integer runs out of the value; the following insert operations would show us duplicate-key errors.
• Foreign keys that are cascaded cannot activate triggers.
• There are a few column names reserved by MySQL that InnoDB uses for internal purposes. The following are a few such column names:
  • DB_ROW_ID
  • DB_TRX_ID
  • DB_ROLL_PTR
  • DB_MIX_ID

We might come across output shown in the following example in case of such reserved column names used:

        mysql> CREATE TABLE chintan (c1 INT, db_row_id INT) 
         ENGINE=INNODB;
        ERROR 1166 (42000): Incorrect column name 'db_row_id'

• InnoDB locks are released immediately after the transaction is aborted or committed, which is held by a transaction.
• The addition of table locks are not supported, as locks are implicit to commit and unlock tables

Data dictionary

Let us have a look at a few known limitations of data dictionary:

• Individual MyISAM tables for backup and restore are not supported by merely copying the files.
• Manually created directories for databases are not supported by MySQL 8. For instance, using mkdir would have no impact on MySQL server data dictionary.
• DDL operations would take more time than expected because such operations are written to storage, undo logs and redo instead of .frm files as what we would have seen in prior versions of MySQL.

Limitations of group replication in MySQL8

It's now time to discuss a few limitations of group replication in MySQL 8:

• Large transactions: Transactions that result to GTID contents cannot be replicated between the rest of the members of the group if they're too large. It is suggested to use smaller chunks of data that cannot be replicated in around five seconds to group members to avoid failures.
• Cluster from a group: If you try to create clusters from an existing group replication setup it will result in an error as the instance would already be part of a replication group. This is noticed currently only in MySQL's wizard mode only; an alternative solution for the issue is to disable wizard mode.
• Serializable isolation level: Serializable isolation level is not supported when multi-primary groups are used, which is the default configuration.
• DDL and DML operations: If there is concurrent DDL and DML operations executed against the same data object but on different servers is not supported when multi-primary group mode is used.
• Replication checksum: Currently MySQL design limitations create restrictions of having replication event checksums.

Limitations of partitioning

We will be discussing limitations of partitioning in this section.

Constructs prohibition

The following are the constructs that are not allowed in expressions of partitions:

• Declared variables
• User variables
• Stored procedures
• Stored functions
• UDFs
• Plugins

Operators

There are a few operators that are not permitted in partition expressions such as << , >> , | , & , ~ and ^ . Results for arithmetic operators such as +, -, and * must have an integer value or NULL.

Tables

The following are a few specific areas that show us limitations of partitioning on tables:

• The maximum number of partitions supported by MySQL 8 for a table is 8192. This limit also considers sub-partitions.
• Fulltext index and search is not supported on partitioned tables.
• Tables that are temporary cannot be partitioned.
• Log tables can't be partitioned.
• Foreign keys are not supported on partitioned InnoDB storage engine.
• The data type of partition keys should be an integer column or can be an expression to an integer. Expression or column values may be NULL; however, expressions that include ENUM are not supported.
• Upgrading partitioned tables that have been partitioned by KEY would have to be reloaded, which stands true other than the InnoDB storage engine.

We have so far discussed overview, features, benefits, and a few limitations of MySQL. Let us now walk through the wonderful use cases of MySQL.