Hosting guebs

Appendix E. Restrictions and Limits

Table of Contents

E.1. Restrictions on Stored Programs
E.2. Restrictions on Server-Side Cursors
E.3. Restrictions on Subqueries
E.4. Restrictions on Views
E.5. Restrictions on XA Transactions
E.6. Restrictions on Character Sets
E.7. Limits in MySQL
E.7.1. Limits of Joins
E.7.2. Limits on Number of Databases and Tables
E.7.3. Limits on Table Size
E.7.4. Table Column-Count and Row-Size Limits
E.7.5. Windows Platform Limitations

The discussion here describes restrictions that apply to the use of MySQL features such as subqueries or views.

E.1. Restrictions on Stored Programs

These restrictions apply to the features described in Chapter 19, Stored Programs and Views.

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. There are also some restrictions specific to stored functions but not to stored procedures.

The restrictions for stored functions also apply to triggers. There are also some restrictions specific to triggers.

The restrictions for stored procedures also apply to the DO clause of Event Scheduler event definitions. There are also some restrictions specific to events.

SQL Statements Not Permitted in Stored Routines

Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted:

  • The locking statements LOCK TABLES and UNLOCK TABLES.

  • ALTER VIEW. (Before MySQL 5.1.21, this restriction is enforced only for stored functions.)

  • LOAD DATA and LOAD TABLE.

  • SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).

  • Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Section 13.5, “SQL Syntax for Prepared Statements”.

  • Inserts cannot be delayed. INSERT DELAYED syntax is accepted, but the statement is handled as a normal INSERT.

  • Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. To begin a transaction in this context, use START TRANSACTION instead.

Restrictions for Stored Functions

The following additional statements or operations are not permitted within stored functions. They are permitted within stored procedures, except stored procedures that are invoked from within a stored function or trigger. For example, if you use FLUSH in a stored procedure, that stored procedure cannot be called from a stored function or trigger.

  • Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them.

  • Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 13.2.8.1, “SELECT ... INTO Syntax”, and Section 13.6.6, “Cursors”.

  • FLUSH statements.

  • Stored functions cannot be used recursively.

  • A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

  • If you refer to a temporary table multiple times in a stored function under different aliases, a Can't reopen table: 'tbl_name' error occurs, even if the references occur in different statements within the function.

Restrictions for Triggers

For triggers, the following additional restrictions apply:

  • Triggers currently are not activated by foreign key actions.

  • When using row-based replication, triggers on the slave are not activated by statements originating on the master. The triggers on the slave are activated when using statement-based replication. For more information, see Section 16.4.1.32, “Replication and Triggers”.

  • The RETURN statement is not permitted in triggers, which cannot return a value. To exit a trigger immediately, use the LEAVE statement.

  • Triggers are not permitted on tables in the mysql database.

  • The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger uses a table and the table has changed since the trigger was loaded into the cache, the trigger operates using the outdated metadata.

Restrictions on Calling Stored Procedures

Before MySQL 5.1.4, CALL statements cannot be prepared, meaning that stored procedures cannot be called from dynamic SQL. This is true both for server-side prepared statements and for SQL prepared statements.

Name Conflicts within Stored Routines

The same identifier might be used for a routine parameter, a local variable, and a table column. Also, the same local variable name can be used in nested blocks. For example:

CREATE PROCEDURE p (i INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SELECT i FROM t;
  BEGIN
    DECLARE i INT DEFAULT 1;
    SELECT i FROM t;
  END;
END;

In such cases, the identifier is ambiguous and the following precedence rules apply:

  • A local variable takes precedence over a routine parameter or table column.

  • A routine parameter takes precedence over a table column.

  • A local variable in an inner block takes precedence over a local variable in an outer block.

The behavior that variables take precedence over table columns is nonstandard.

Replication Considerations

Use of stored routines can cause replication problems. This issue is discussed further in Section 19.7, “Binary Logging of Stored Programs”.

The --replicate-wild-do-table=db_name.tbl_name option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options.

Introspection Considerations

INFORMATION_SCHEMA does not have a PARAMETERS table until MySQL 5.5; this table enables stored routines or client applications to determine the names, types, and default values of parameters for stored routines. For releases without this INFORMATION_SCHEMA.PARAMETERS table, to examine these types of metadata, you must use workarounds such as parsing the output of SHOW CREATE statements or the param_list column of the mysql.proc table. param_list contents can be processed from within a stored routine, unlike the output from SHOW.

Debugging Considerations

There are no stored routine debugging facilities.

Unsupported Syntax from the SQL:2003 Standard

The MySQL stored routine syntax is based on the SQL:2003 standard. The following items from that standard are not currently supported:

  • UNDO handlers

  • FOR loops

Concurrency Considerations

To prevent problems of interaction between sessions, when a client issues a statement, the server uses a snapshot of routines and triggers available for execution of the statement. That is, the server calculates a list of procedures, functions, and triggers that may be used during execution of the statement, loads them, and then proceeds to execute the statement. While the statement executes, it does not see changes to routines performed by other sessions.

For maximum concurrency, stored functions should minimize their side-effects; in particular, updating a table within a stored function can reduce concurrent operations on that table. A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. When statement-based binary logging is used, statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel. In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log, even for statement-based binary logging. See Section 19.7, “Binary Logging of Stored Programs”.

Event Scheduler Restrictions

The following limitations are specific to the Event Scheduler:

  • In MySQL 5.1.6 only, any table referenced in an event's action statement must be fully qualified with the name of the schema in which it occurs (that is, as schema_name.table_name).

  • Beginning with MySQL 5.1.8, event names are handled in case-insensitive fashion. For example, this means that you cannot have two events in the same database (and—prior to MySQL 5.1.12—with the same definer) with the names anEvent and AnEvent.

    Important

    If you have events created in MySQL 5.1.7 or earlier which are assigned to the same database and have the same definer, and whose names differ only with respect to lettercase, then you must rename these events to respect case-sensitive handling before upgrading to MySQL 5.1.8 or later.

  • An event may not be created, altered, or dropped by a stored routine, trigger, or another event. An event also may not create, alter, or drop stored routines or triggers. (Bug #16409, Bug #18896)

  • Event timings using the intervals YEAR, QUARTER, MONTH, and YEAR_MONTH are resolved in months; those using any other interval are resolved in seconds. There is no way to cause events scheduled to occur at the same second to execute in a given order. In addition—due to rounding, the nature of threaded applications, and the fact that a nonzero length of time is required to create events and to signal their execution—events may be delayed by as much as 1 or 2 seconds. However, the time shown in the INFORMATION_SCHEMA.EVENTS table's LAST_EXECUTED column or the mysql.event table's last_executed column is always accurate to within one second of the actual event execution time. (See also Bug #16522.)

  • Each execution of the statements contained in the body of an event takes place in a new connection; thus, these statements has no effect in a given user session on the server's statement counts such as Com_select and Com_insert that are displayed by SHOW STATUS. However, such counts are updated in the global scope. (Bug #16422)

  • Prior to MySQL 5.1.12, you could not view another user's events in the INFORMATION_SCHEMA.EVENTS table. In other words, any query made against this table was treated as though it contained the condition DEFINER = CURRENT_USER() in the WHERE clause.

  • Events do not support times later than the end of the Unix Epoch; this is approximately the beginning of the year 2038. Prior to MySQL 5.1.8, handling in scheduled events of dates later than this was buggy; starting with MySQL 5.1.8, such dates are specifically not permitted by the Event Scheduler. (Bug #16396)

  • In MySQL 5.1.6, INFORMATION_SCHEMA.EVENTS shows NULL in the SQL_MODE column. Beginning with MySQL 5.1.7, the SQL_MODE displayed is that in effect when the event was created.

  • In MySQL 5.1.6, the only way to drop or alter an event created by a user who was not the definer of that event was by manipulation of the mysql.event system table by the MySQL root user or by another user with privileges on this table. Beginning with MySQL 5.1.7, DROP USER drops all events for which that user was the definer; also beginning with MySQL 5.1.7 DROP SCHEMA drops all events associated with the dropped schema.

  • References to stored functions, user-defined functions, and tables in the ON SCHEDULE clauses of CREATE EVENT and ALTER EVENT statements are not supported. Beginning with MySQL 5.1.13, these sorts of references are not permitted. (See Bug #22830 for more information.)

  • Generally speaking, statements that are not permitted in stored routines or in SQL prepared statements are also not permitted in the body of an event. For more information, see Section 13.5, “SQL Syntax for Prepared Statements”.

  • When upgrading to MySQL 5.1.18 or 5.1.19 from a previous MySQL version where scheduled events were in use, the upgrade utilities mysql_upgrade and mysql_fix_privilege_tables do not accommodate changes in system tables relating to the Event Scheduler. This issue was fixed in MySQL 5.1.20 (see Bug #28521).

Stored routines and triggers in MySQL Cluster.  Stored procedures, stored functions, and triggers are all supported by tables using the NDB storage engine; however, it is important to keep in mind that they do not propagate automatically between MySQL Servers acting as Cluster SQL nodes. This is because of the following:

  • Stored routine definitions are kept in tables in the mysql system database using the MyISAM storage engine, and so do not participate in clustering.

  • The .TRN and .TRG files containing trigger definitions are not read by the NDB storage engine, and are not copied between Cluster nodes.

Any stored routine or trigger that interacts with MySQL Cluster tables must be re-created by running the appropriate CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER statements on each MySQL Server that participates in the cluster where you wish to use the stored routine or trigger. Similarly, any changes to existing stored routines or triggers must be carried out explicitly on all Cluster SQL nodes, using the appropriate ALTER or DROP statements on each MySQL Server accessing the cluster.

Warning

Do not attempt to work around the issue described in the first item mentioned previously by converting any mysql database tables to use the NDB storage engine. Altering the system tables in the mysql database is not supported and is very likely to produce undesirable results.

E.2. Restrictions on Server-Side Cursors

Server-side cursors are implemented in the C API using the mysql_stmt_attr_set() function. The same implementation is used for cursors in stored routines. A server-side cursor enables a result set to be generated on the server side, but not transferred to the client except for those rows that the client requests. For example, if a client executes a query but is only interested in the first row, the remaining rows are not transferred.

In MySQL, a server-side cursor is materialized into an internal temporary table. Initially, this is a MEMORY table, but is converted to a MyISAM table when its size exceeds the minimum value of the max_heap_table_size and tmp_table_size system variables. Note that the same restrictions apply to internal temporary tables created to hold the result set for a cursor as for other uses of internal temporary tables. See Section 8.8.5, “How MySQL Uses Internal Temporary Tables”. One limitation of the implementation is that for a large result set, retrieving its rows through a cursor might be slow.

Cursors are read only; you cannot use a cursor to update rows.

UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF are not implemented, because updatable cursors are not supported.

Cursors are nonholdable (not held open after a commit).

Cursors are asensitive.

Cursors are nonscrollable.

Cursors are not named. The statement handler acts as the cursor ID.

You can have open only a single cursor per prepared statement. If you need several cursors, you must prepare several statements.

You cannot use a cursor for a statement that generates a result set if the statement is not supported in prepared mode. This includes statements such as CHECK TABLE, HANDLER READ, and SHOW BINLOG EVENTS.

E.3. Restrictions on Subqueries

  • In MySQL 5.1 before 5.1.16, if you compare a NULL value to a subquery using ALL, ANY, or SOME, and the subquery returns an empty result, the comparison might evaluate to the nonstandard result of NULL rather than to TRUE or FALSE. As of 5.1.16, the comparison evaluates to TRUE or FALSE except for subqueries inside IS NULL, such as this:

    SELECT ... WHERE NULL IN (SELECT ...) IS NULL

    As of 5.1.32, the IS NULL limitation is removed and the comparison evaluates to TRUE or FALSE.

  • Subquery optimization for IN is not as effective as for the = operator or for the IN(value_list) operator.

    A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

    The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

    SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

    The optimizer rewrites the statement to a correlated subquery:

    SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

    If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

    An implication is that an IN subquery can be much slower than a query written using an IN(value_list) operator that lists the same values that the subquery would return.

  • In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

    DELETE FROM t WHERE ... (SELECT ... FROM t ...);
    UPDATE t ... WHERE col = (SELECT ... FROM t ...);
    {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

    Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

    UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

    Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

  • Row comparison operations are only partially supported:

    • For expr [NOT] IN subquery, expr can be an n-tuple (specified using row constructor syntax) and the subquery can return rows of n-tuples. The permitted syntax is therefore more specifically expressed as row_constructor [NOT] IN table_subquery

    • For expr op {ALL|ANY|SOME} subquery, expr must be a scalar value and the subquery must be a column subquery; it cannot return multiple-column rows.

    In other words, for a subquery that returns rows of n-tuples, this is supported:

    (expr_1, ..., expr_n) [NOT] IN table_subquery
    

    But this is not supported:

    (expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery
    

    The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of = comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

  • Prior to MySQL 5.1.12, row constructors were not well optimized; of the following two equivalent expressions, only the second could be optimized:

    (col1, col2, ...) = (val1, val2, ...)
    col1 = val1 AND col2 = val2 AND ...

    In MySQL 5.1.12 and later, all row equalities are converted into conjunctions of equalities between row elements, and handled by the optimizer in the same way. (Bug #16081)

  • Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

  • MySQL does not support LIMIT in subqueries for certain subquery operators:

    mysql> SELECT * FROM t1
        ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
    ERROR 1235 (42000): This version of MySQL doesn't yet support
     'LIMIT & IN/ALL/ANY/SOME subquery'
    
  • The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

    An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:

    SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);
    

    That statement can be rewritten as follows:

    SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;
    

    But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery.

  • MySQL permits a subquery to refer to a stored function that has data-modifying side effects such as inserting rows into a table. For example, if f() inserts rows, the following query can modify data:

    SELECT ... WHERE x IN (SELECT f() ...);

    This behavior is an extension to the SQL standard. In MySQL, it can produce indeterminate results because f() might be executed a different number of times for different executions of a given query depending on how the optimizer chooses to handle it.

    For statement-based or mixed-format replication, one implication of this indeterminism is that such a query can produce different results on the master and its slaves.

E.4. Restrictions on Views

View processing is not optimized:

  • It is not possible to create an index on a view.

  • Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

There is a general principle that you cannot modify a table and select from the same table in a subquery. See Section E.3, “Restrictions on Subqueries”.

The same principle also applies if you select from a view that selects from the table, if the view selects from the table in a subquery and the view is evaluated using the merge algorithm. Example:

CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);

UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;

If the view is evaluated using a temporary table, you can select from the table in the view subquery and still modify that table in the outer query. In this case the view will be stored in a temporary table and thus you are not really selecting from the table in a subquery and modifying it at the same time. (This is another reason you might wish to force MySQL to use the temptable algorithm by specifying ALGORITHM = TEMPTABLE in the view definition.)

You can use DROP TABLE or ALTER TABLE to drop or alter a table that is used in a view definition. No warning results from the DROP or ALTER operation, even though this invalidates the view. Instead, an error occurs later, when the view is used. CHECK TABLE can be used to check for views that have been invalidated by DROP or ALTER operations.

A view definition is frozen by certain statements:

  • If a statement prepared by PREPARE refers to a view, the view definition seen each time the statement is executed later will be the definition of the view at the time it was prepared. This is true even if the view definition is changed after the statement is prepared and before it is executed. Example:

    CREATE VIEW v AS SELECT RAND();
    PREPARE s FROM 'SELECT * FROM v';
    ALTER VIEW v AS SELECT NOW();
    EXECUTE s;

    The result returned by the EXECUTE statement is a random number, not the current date and time.

  • If a statement in a stored routine refers to a view, the view definition seen by the statement are its definition the first time that statement is executed. For example, this means that if the statement is executed in a loop, further iterations of the statement see the same view definition, even if the definition is changed later in the loop. Example:

    CREATE VIEW v AS SELECT 1;
    delimiter //
    CREATE PROCEDURE p ()
    BEGIN
      DECLARE i INT DEFAULT 0;
      WHILE i < 5 DO
        SELECT * FROM v;
        SET i = i + 1;
        ALTER VIEW v AS SELECT 2;
      END WHILE;
    END;
    //
    delimiter ;
    CALL p();

    When the procedure p() is called, the SELECT returns 1 each time through the loop, even though the view definition is changed within the loop.

    As of MySQL 5.1.21, ALTER VIEW is prohibited within stored routines, so this restriction does not apply.

With regard to view updatability, the overall goal for views is that if any view is theoretically updatable, it should be updatable in practice. This includes views that have UNION in their definition. Currently, not all views that are theoretically updatable can be updated. The initial view implementation was deliberately written this way to get usable, updatable views into MySQL as quickly as possible. Many theoretically updatable views can be updated now, but limitations still exist:

  • Updatable views with subqueries anywhere other than in the WHERE clause. Some views that have subqueries in the SELECT list may be updatable.

  • You cannot use UPDATE to update more than one underlying table of a view that is defined as a join.

  • You cannot use DELETE to update a view that is defined as a join.

There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user will be unable to call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege.

That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.

The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created.

Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.

SHOW CREATE VIEW displays view definitions using an AS alias_name clause for each column. If a column is created from an expression, the default alias is the expression text, which can be quite long. As of MySQL 5.1.23, aliases for column names in CREATE VIEW statements are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters). As a result, views created from the output of SHOW CREATE VIEW fail if any column alias exceeds 64 characters. This can cause problems in the following circumstances for views with too-long aliases:

  • View definitions fail to replicate to newer slaves that enforce the column-length restriction.

  • Dump files created with mysqldump cannot be loaded into servers that enforce the column-length restriction.

A workaround for either problem is the modify each problematic view definition to use aliases that provide shorter column names. Then the view will replicate properly, and can be dumped and reloaded without causing an error. To modify the definition, drop and create the view again with DROP VIEW and CREATE VIEW, or replace the definition with CREATE OR REPLACE VIEW.

For problems that occur when reloading view definitions in dump files, another workaround is to edit the dump file to modify its CREATE VIEW statements. However, this does not change the original view definitions, which may cause problems for subsequent dump operations.

E.5. Restrictions on XA Transactions

XA transaction support is limited to the InnoDB storage engine.

For external XA, a MySQL server acts as a Resource Manager and client programs act as Transaction Managers. For Internal XA, storage engines within a MySQL server act as RMs, and the server itself acts as a TM. Internal XA support is limited by the capabilities of individual storage engines. Internal XA is required for handling XA transactions that involve more than one storage engine. The implementation of internal XA requires that a storage engine support two-phase commit at the table handler level, and currently this is true only for InnoDB.

For XA START, the JOIN and RESUME clauses are not supported.

For XA END, the SUSPEND [FOR MIGRATE] clause is not supported.

The requirement that the bqual part of the xid value be different for each XA transaction within a global transaction is a limitation of the current MySQL XA implementation. It is not part of the XA specification.

If an XA transaction has reached the PREPARED state and the MySQL server is killed (for example, with kill -9 on Unix) or shuts down abnormally, the transaction can be continued after the server restarts. However, if the client reconnects and commits the transaction, the transaction will be absent from the binary log even though it has been committed. This means the data and the binary log have gone out of synchrony. An implication is that XA cannot be used safely together with replication.

It is possible that the server will roll back a pending XA transaction, even one that has reached the PREPARED state. This happens if a client connection terminates and the server continues to run, or if clients are connected and the server shuts down gracefully. (In the latter case, the server marks each connection to be terminated, and then rolls back the PREPARED XA transaction associated with it.) It should be possible to commit or roll back a PREPARED XA transaction, but this cannot be done without changes to the binary logging mechanism.

E.6. Restrictions on Character Sets

  • Identifiers are stored in mysql database tables (user, db, and so forth) using utf8, but identifiers can contain only characters in the Basic Multilingual Plane (BMP). Supplementary characters are not permitted in identifiers.

  • The ucs2 character sets has the following restrictions:

    • It cannot be used as a client character set, which means that it does not work for SET NAMES or SET CHARACTER SET. (See Section 10.1.4, “Connection Character Sets and Collations”.)

    • It is currently not possible to use LOAD DATA INFILE to load data files that use this character set.

    • FULLTEXT indexes cannot be created on a column that this character set. However, you can perform IN BOOLEAN MODE searches on the column without an index.

    • The use of ENCRYPT() with this character set is not recommended because the underlying system call expects a string terminated by a zero byte.

  • The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

E.7. Limits in MySQL

This section lists current limits in MySQL 5.1.

E.7.1. Limits of Joins

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.

E.7.2. Limits on Number of Databases and Tables

MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

E.7.3. Limits on Table Size

The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating SystemFile-size Limit
Win32 w/ FAT/FAT322GB/4GB
Win32 w/ NTFS2TB (possibly larger)
Linux 2.2-Intel 32-bit2GB (LFS: 4GB)
Linux 2.4+(using ext3 file system) 4TB
Solaris 9/1016TB
Mac OS X w/ HFS+2TB
NetWare w/NSS file system8TB

Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the Large File Support (LFS) patch for the ext2 file system. Most current Linux distributions are based on kernel 2.4 or higher and include all the required LFS patches. On Linux 2.4, patches also exist for ReiserFS to get support for big files (up to 2TB). With JFS and XFS, petabyte and larger files are possible on Linux.

For a detailed overview about LFS in Linux, have a look at Andreas Jaeger's Large File Support in Linux page at http://www.suse.de/~aj/linux_lfs.html.

If you do encounter a full-table error, there are several reasons why it might have occurred:

  • The disk might be full.

  • The InnoDB storage engine maintains InnoDB tables within a tablespace that can be created from several files. This enables a table to exceed the maximum individual file size. The tablespace can include raw disk partitions, which permits extremely large tables. The maximum tablespace size is 64TB.

    If you are using InnoDB tables and run out of room in the InnoDB tablespace. In this case, the solution is to extend the InnoDB tablespace. See Section 14.6.4, “Adding, Removing, or Resizing InnoDB Data and Log Files”.

  • You are using MyISAM tables on an operating system that supports files only up to 2GB in size and you have hit this limit for the data file or index file.

  • You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 – 1 bytes).

    If you need a MyISAM table that is larger than the default limit and your operating system supports large files, the CREATE TABLE statement supports AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.1.17, “CREATE TABLE Syntax”. The server uses these options to determine how large a table to permit.

    If the pointer size is too small for an existing table, you can change the options with ALTER TABLE to increase a table's maximum permissible size. See Section 13.1.7, “ALTER TABLE Syntax”.

    ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
    

    You have to specify AVG_ROW_LENGTH only for tables with BLOB or TEXT columns; in this case, MySQL can't optimize the space required based only on the number of rows.

    To change the default size limit for MyISAM tables, set the myisam_data_pointer_size, which sets the number of bytes used for internal row pointers. The value is used to set the pointer size for new tables if you do not specify the MAX_ROWS option. The value of myisam_data_pointer_size can be from 2 to 7. A value of 4 permits tables up to 4GB; a value of 6 permits tables up to 256TB.

    You can check the maximum data and index sizes by using this statement:

    SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
    

    You also can use myisamchk -dv /path/to/table-index-file. See Section 13.7.5, “SHOW Syntax”, or Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

    Other ways to work around file-size limits for MyISAM tables are as follows:

  • You are using the NDB storage engine, in which case you need to increase the values for the DataMemory and IndexMemory configuration parameters in your config.ini file. See Section 17.3.3.1, “MySQL Cluster Data Node Configuration Parameters”.

  • You are using the MEMORY (HEAP) storage engine; in this case you need to increase the value of the max_heap_table_size system variable. See Section 5.1.4, “Server System Variables”.

E.7.4. Table Column-Count and Row-Size Limits

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors.

  • Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on this limit, reducing the effective maximum row size.

    The maximum row size constrains the number (and possibly size) of columns because the total length of all columns cannot exceed this size. For example, utf8 characters require up to three bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. Consequently, a table cannot contain more than 65,535 / 765 = 85 such columns.

    Storage for variable-length columns includes length bytes, which are assessed against the row size. For example, a VARCHAR(255) CHARACTER SET utf8 column takes two bytes to store the length of the value, so each value can take up to 767 bytes.

    BLOB and TEXT columns count from one to four plus eight bytes each toward the row-size limit because their contents are stored separately from the rest of the row.

    Declaring columns NULL can reduce the maximum number of columns permitted. For MyISAM tables, NULL columns require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra, rounded up to the nearest byte. The maximum row length in bytes can be calculated as follows:

    row length = 1
                 + (sum of column lengths)
                 + (number of NULL columns + delete_flag + 7)/8
                 + (number of variable-length columns)
    

    delete_flag is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header. For information about MyISAM table formats, see Section 14.5.3, “MyISAM Table Storage Formats”.

    These calculations do not apply for InnoDB tables. Storage size is the same for NULL and NOT NULL columns.

    The following statement to create table t1 succeeds because the columns require 32,765 + 2 bytes and 32,766 + 2 bytes, which falls within the maximum row size of 65,535 bytes:

    mysql> CREATE TABLE t1
        -> (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
        -> ENGINE = MyISAM CHARACTER SET latin1;
    Query OK, 0 rows affected (0.02 sec)
    

    The following statement to create table t2 fails because the columns are NULL and MyISAM requires additional space that causes the row size to exceed 65,535 bytes:

    mysql> CREATE TABLE t2
        -> (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
        -> ENGINE = MyISAM CHARACTER SET latin1;
    ERROR 1118 (42000): Row size too large. The maximum row size for the
    used table type, not counting BLOBs, is 65535. You have to change some
    columns to TEXT or BLOBs
    

    The following statement to create table t3 fails because although the column length is within the maximum length of 65,535 bytes, two additional bytes are required to record the length, which causes the row size to exceed 65,535 bytes:

    mysql> CREATE TABLE t3
        -> (c1 VARCHAR(65535) NOT NULL)
        -> ENGINE = MyISAM CHARACTER SET latin1;
    ERROR 1118 (42000): Row size too large. The maximum row size for the
    used table type, not counting BLOBs, is 65535. You have to change some
    columns to TEXT or BLOBs
    

    Reducing the column length to 65,533 or less permits the statement to succeed.

  • Each table has an .frm file that contains the table definition. The server uses the following expression to check some of the table information stored in the file against an upper limit of 64KB:

    if (info_length+(ulong) create_fields.elements*FCOMP+288+
        n_length+int_length+com_length > 65535L || int_count > 255)

    The portion of the information stored in the .frm file that is checked against the expression cannot grow beyond the 64KB limit, so if the table definition reaches this size, no more columns can be added.

    The relevant factors in the expression are:

    • info_length is space needed for screens. This is related to MySQL's Unireg heritage.

    • create_fields.elements is the number of columns.

    • FCOMP is 17.

    • n_length is the total length of all column names, including one byte per name as a separator.

    • int_length is related to the list of values for ENUM and SET columns.

    • com_length is the total length of column and table comments.

    Thus, using long column names can reduce the maximum number of columns, as can the inclusion of ENUM or SET columns, or use of column or table comments.

  • Individual storage engines might impose additional restrictions that limit table column count. Examples:

    • InnoDB permits up to 1000 columns.

    • InnoDB restricts row size to something less than half a database page (approximately 8000 bytes), not including VARBINARY, VARCHAR, BLOB, or TEXT columns.

    • Different InnoDB storage formats (COMPRESSED, REDUNDANT) use different amounts of page header and trailer data, which affects the amount of storage available for rows.

E.7.5. Windows Platform Limitations

The following limitations apply to use of MySQL on the Windows platform:

  • Number of file descriptors

    The number of open file descriptors on Windows is limited to a maximum of 2048, which may limit the ability to open a large number of tables simultaneously. This limit is due not to Windows but to C runtime library compatibility functions used to open files on Windows that use the POSIX compatibility layer.

    This limitation will also cause problems if you try to set open_files_limit to a value greater than the 2048 file limit.

  • Process memory

    On Windows 32-bit platforms it is not possible by default to use more than 2GB of RAM within a single process, including MySQL. This is because the physical address limit on Windows 32-bit is 4GB and the default setting within Windows is to split the virtual address space between kernel (2GB) and user/applications (2GB).

    Some versions of Windows have a boot time setting to enable larger applications by reducing the kernel application. Alternatively, to use more than 2GB, use a 64-bit version of Windows.

  • File system aliases

    When using MyISAM tables, you cannot use aliases within Windows link to the data files on another volume and then link back to the main MySQL datadir location.

    This facility is often used to move the data and index files to a RAID or other fast solution, while retaining the main .frm files in the default data directory configured with the datadir option.

  • Limited number of ports

    Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower.

    For more information about this problem, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271.

  • Concurrent reads

    MySQL depends on the pread() and pwrite() system calls to be able to mix INSERT and SELECT. Currently, we use mutexes to emulate pread() and pwrite(). We intend to replace the file level interface with a virtual interface in the future so that we can use the readfile()/writefile() interface to get more speed. The current implementation limits the number of open files that MySQL 5.1 can use to 2,048, which means that you cannot run as many concurrent threads on Windows as on Unix.

    This problem is fixed in MySQL 5.5.

  • Blocking read

    Before MySQL 5.1.41, MySQL uses a blocking read for each connection. That has the following implications if named-pipe connections are enabled:

    • A connection is not disconnected automatically after eight hours, as happens with the Unix version of MySQL.

    • If a connection hangs, it is not possible to break it without killing MySQL.

    • mysqladmin kill does not work on a sleeping connection.

    • mysqladmin shutdown cannot abort as long as there are sleeping connections.

  • DROP DATABASE

    You cannot drop a database that is in use by another session.

  • Case-insensitive names

    File names are not case sensitive on Windows, so MySQL database and table names are also not case sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See Section 9.2.2, “Identifier Case Sensitivity”.

  • Directory and file names

    On Windows, MySQL Server supports only directory and file names that are compatible with the current ANSI code pages. For example, the following Japanese directory name will not work in the Western locale (code page 1252):

    datadir="C:/私たちのプロジェクトのデータ"

    The same limitation applies to directory and file names referred to in SQL statements, such as the data file path name in LOAD DATA INFILE.

  • The \ path name separator character

    Path name components in Windows are separated by the \ character, which is also the escape character in MySQL. If you are using LOAD DATA INFILE or SELECT ... INTO OUTFILE, use Unix-style file names with / characters:

    mysql> LOAD DATA INFILE 'C:/tmp/skr.txt' INTO TABLE skr;
    mysql> SELECT * INTO OUTFILE 'C:/tmp/skr.txt' FROM skr;
    

    Alternatively, you must double the \ character:

    mysql> LOAD DATA INFILE 'C:\\tmp\\skr.txt' INTO TABLE skr;
    mysql> SELECT * INTO OUTFILE 'C:\\tmp\\skr.txt' FROM skr;
    
  • Problems with pipes

    Pipes do not work reliably from the Windows command-line prompt. If the pipe includes the character ^Z / CHAR(24), Windows thinks that it has encountered end-of-file and aborts the program.

    This is mainly a problem when you try to apply a binary log as follows:

    C:\> mysqlbinlog binary_log_file | mysql --user=root
    

    If you have a problem applying the log and suspect that it is because of a ^Z / CHAR(24) character, you can use the following workaround:

    C:\> mysqlbinlog binary_log_file --result-file=/tmp/bin.sql
    C:\> mysql --user=root --execute "source /tmp/bin.sql"
    

    The latter command also can be used to reliably read in any SQL file that may contain binary data.