<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Appendix F. Limits and Restrictions</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="news.html" title="Appendix E. MySQL Change History"><link rel="next" href="credits.html" title="Appendix G. Credits"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Appendix F. Limits and Restrictions</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="news.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="credits.html">Next</a></td></tr></table><hr></div><div class="appendix" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="restrictions"></a>Appendix F. Limits and Restrictions</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="restrictions.html#routine-restrictions">F.1. Restrictions on Stored Routines and Triggers</a></span></dt><dt><span class="section"><a href="restrictions.html#cursor-restrictions">F.2. Restrictions on Server-Side Cursors</a></span></dt><dt><span class="section"><a href="restrictions.html#subquery-restrictions">F.3. Restrictions on Subqueries</a></span></dt><dt><span class="section"><a href="restrictions.html#view-restrictions">F.4. Restrictions on Views</a></span></dt><dt><span class="section"><a href="restrictions.html#xa-restrictions">F.5. Restrictions on XA Transactions</a></span></dt><dt><span class="section"><a href="restrictions.html#limits">F.6. Limits in MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="restrictions.html#joins-limits">F.6.1. Limits of Joins</a></span></dt><dt><span class="section"><a href="restrictions.html#column-count-limit">F.6.2. The Maximum Number of Columns Per Table</a></span></dt></dl></dd></dl></div><p>
The discussion here describes restrictions that apply to the use of
MySQL features such as subqueries or views.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="routine-restrictions"></a>F.1. Restrictions on Stored Routines and Triggers</h2></div></div></div><a class="indexterm" name="id3360980"></a><a class="indexterm" name="id3360989"></a><a class="indexterm" name="id3361002"></a><a class="indexterm" name="id3361011"></a><p>
Some of the restrictions noted here apply to all stored routines;
that is, both to stored procedures and stored functions. Some of
restrictions apply only to stored functions, and not to stored
procedures.
</p><p>
All of the restrictions for stored functions also apply to
triggers.
</p><p>
Stored routines cannot contain arbitrary SQL statements. The
following statements are disallowed:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The table-maintenance statements <code class="literal">CHECK
TABLES</code> and <code class="literal">OPTIMIZE TABLES</code>. This
restriction is lifted beginning with MySQL 5.0.17.
</p></li><li><p>
The locking statements <code class="literal">LOCK TABLES</code>,
<code class="literal">UNLOCK TABLES</code>.
</p></li><li><p>
<code class="literal">LOAD DATA</code> and <code class="literal">LOAD
TABLE</code>.
</p></li><li><p>
SQL prepared statements (<code class="literal">PREPARE</code>,
<code class="literal">EXECUTE</code>, <code class="literal">DEALLOCATE
PREPARE</code>). Implication: You cannot use dynamic SQL
within stored routines (where you construct dynamically
statements as strings and then execute them). This restriction
is lifted as of MySQL 5.0.13 for stored procedures; it still
applies to stored functions and triggers.
</p><p>
In addition, SQL statements that are not permitted within
prepared statements are also not permitted in stored routines.
See <a href="sql-syntax.html#sqlps" title="12.7. SQL Syntax for Prepared Statements">Section 12.7, “SQL Syntax for Prepared Statements”</a>, for a list of statements
supported in prepared statements. Statements not listed there
are not supported for SQL prepared statements and thus are
also not supported for stored routines unless noted otherwise
in <a href="stored-procedures.html" title="Chapter 18. Stored Procedures and Functions">Chapter 18, <i>Stored Procedures and Functions</i></a>.
</p></li></ul></div><p>
For stored functions (but not stored procedures), the following
additional statements or operations are disallowed:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Statements that do explicit or implicit commit or rollback.
</p></li><li><p>
Statements that return a result set. This includes
<code class="literal">SELECT</code> statements that do not have an
<code class="literal">INTO <em class="replaceable"><code>var_list</code></em></code>
clause and <code class="literal">SHOW</code> statements. A function can
process a result set either with <code class="literal">SELECT ... INTO
<em class="replaceable"><code>var_list</code></em></code> or by using a
cursor and <code class="literal">FETCH</code> statements. See
<a href="stored-procedures.html#select-into-statement" title="18.2.7.3. SELECT ... INTO Statement">Section 18.2.7.3, “<code class="literal">SELECT ... INTO</code> Statement”</a>.
</p></li><li><p>
<code class="literal">FLUSH</code> statements.
</p></li><li><p>
Before MySQL 5.0.10, stored functions created with
<code class="literal">CREATE FUNCTION</code> must not contain references
to tables, with limited exceptions. They may include some
<code class="literal">SET</code> statements that contain table
references, for example <code class="literal">SET a:= (SELECT MAX(id) FROM
t)</code>, and <code class="literal">SELECT</code> statements that
fetch values directly into variables, for example
<code class="literal">SELECT i INTO var1 FROM t</code>.
</p></li><li><p>
Recursive statements. That is, stored functions cannot be used
recursively.
</p></li><li><p>
Within a stored function or trigger, it is not permitted to
modify a table that is already being used (for reading or
writing) by the statement that invoked the function or
trigger.
</p></li><li><p>
<code class="literal">ALTER VIEW</code>.
</p></li></ul></div><p>
Note that although some restrictions normally apply to stored
functions and triggers but not to stored procedures, those
restrictions do apply to stored procedures if they are invoked
from within a stored function or trigger. For example, although
you can use <code class="literal">FLUSH</code> in a stored procedure, such a
stored procedure cannot be called from a stored function or
trigger.
</p><p>
It is possible for the same identifier to 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:
</p><pre class="programlisting">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;
</pre><p>
In such cases the identifier is ambiguous and the following
precedence rules apply:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A local variable takes precedence over a routine parameter or
table column
</p></li><li><p>
A routine parameter takes precedence over a table column
</p></li><li><p>
A local variable in an inner block takes precedence over a
local variable in an outer block
</p></li></ul></div><p>
The behavior that table columns do not take precedence over
variables is non-standard.
</p><p>
Use of stored routines can cause replication problems. This issue
is discussed further in
<a href="stored-procedures.html#stored-procedure-logging" title="18.4. Binary Logging of Stored Routines and Triggers">Section 18.4, “Binary Logging of Stored Routines and Triggers”</a>.
</p><p>
<code class="literal">INFORMATION_SCHEMA</code> does not have a
<code class="literal">PARAMETERS</code> table until MySQL 6, so applications
that need to acquire routine parameter information at runtime must
use workarounds such as parsing the output of <code class="literal">SHOW
CREATE</code> statements.
</p><p>
There are no stored routine debugging facilities.
</p><p>
Before MySQL 5.0.17, <code class="literal">CALL</code> statements cannot be
prepared. This true both for server-side prepared statements and
for SQL prepared statements.
</p><p>
<code class="literal">UNDO</code> handlers are not supported.
</p><p>
<code class="literal">FOR</code> loops are not supported.
</p><p>
To prevent problems of interaction between server threads, 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. This means that
while the statement executes, it will not see changes to routines
performed by other threads.
</p><p>
For triggers, the following additional statements or operations
are disallowed:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Triggers currently are not activated by foreign key actions.
</p></li><li><p>
The <code class="literal">RETURN</code> statement is disallowed in
triggers, which cannot return a value. To exit a trigger
immediately, use the <code class="literal">LEAVE</code> statement.
</p></li><li><p>
Triggers are not allowed on tables in the
<code class="literal">mysql</code> database.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="cursor-restrictions"></a>F.2. Restrictions on Server-Side Cursors</h2></div></div></div><a class="indexterm" name="id3361518"></a><a class="indexterm" name="id3361527"></a><p>
Server-side cursors are implemented beginning with the C API in
MySQL 5.0.2 via the
<a href="apis.html#mysql-stmt-attr-set" title="23.2.7.3. mysql_stmt_attr_set()"><code class="literal">mysql_stmt_attr_set()</code></a> function. A
server-side cursor allows 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.
</p><p>
In MySQL, a server-side cursor is materialized into a temporary
table. Initially, this is a <code class="literal">MEMORY</code> table, but
is converted to a <code class="literal">MyISAM</code> table if its size
reaches the value of the <code class="literal">max_heap_table_size</code>
system variable. (Beginning with MySQL 5.0.14, the same
temporary-table implementation also is used for cursors in stored
routines.) One limitation of the implementation is that for a
large result set, retrieving its rows through a cursor might be
slow.
</p><p>
Cursors are read only; you cannot use a cursor to update rows.
</p><p>
<code class="literal">UPDATE WHERE CURRENT OF</code> and <code class="literal">DELETE
WHERE CURRENT OF</code> are not implemented, because updatable
cursors are not supported.
</p><p>
Cursors are non-holdable (not held open after a commit).
</p><p>
Cursors are asensitive.
</p><p>
Cursors are non-scrollable.
</p><p>
Cursors are not named. The statement handler acts as the cursor
ID.
</p><p>
You can have open only a single cursor per prepared statement. If
you need several cursors, you must prepare several statements.
</p><p>
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 <code class="literal">CHECK TABLES</code>,
<code class="literal">HANDLER READ</code>, and <code class="literal">SHOW BINLOG
EVENTS</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="subquery-restrictions"></a>F.3. Restrictions on Subqueries</h2></div></div></div><a class="indexterm" name="id3361666"></a><a class="indexterm" name="id3361675"></a><div class="itemizedlist"><ul type="disc"><li><p>
In MySQL 5.0 before 5.0.36, if you compare a
<code class="literal">NULL</code> value to a subquery using
<code class="literal">ALL</code>, <code class="literal">ANY</code>, or
<code class="literal">SOME</code>, and the subquery returns an empty
result, the comparison might evaluate to the non-standard
result of <code class="literal">NULL</code> rather than to
<code class="literal">TRUE</code> or <code class="literal">FALSE</code>.
</p></li><li><p>
A subquery's outer statement can be any one of:
<code class="literal">SELECT</code>, <code class="literal">INSERT</code>,
<code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,
<code class="literal">SET</code>, or <code class="literal">DO</code>.
</p></li><li><p>
Subquery optimization for <code class="literal">IN</code> is not as
effective as for the <code class="literal">=</code> operator or for the
<a href="functions.html#function_in"><code class="literal">IN(<em class="replaceable"><code>value_list</code></em>)</code></a>
operator.
</p><p>
A typical case for poor <code class="literal">IN</code> 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.
</p><p>
The problem is that, for a statement that uses an
<code class="literal">IN</code> subquery, the optimizer rewrites it as a
correlated subquery. Consider the following statement that
uses an uncorrelated subquery:
</p><pre class="programlisting">SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
</pre><p>
The optimizer rewrites the statement to a correlated subquery:
</p><pre class="programlisting">SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
</pre><p>
If the inner and outer queries return
<em class="replaceable"><code>M</code></em> and <em class="replaceable"><code>N</code></em>
rows, respectively, the execution time becomes on the order of
<code class="literal">O(<em class="replaceable"><code>M</code></em>×<em class="replaceable"><code>N</code></em>)</code>,
rather than
<code class="literal">O(<em class="replaceable"><code>M</code></em>+<em class="replaceable"><code>N</code></em>)</code>
as it would be for an uncorrelated subquery.
</p><p>
An implication is that an <code class="literal">IN</code> subquery can
be much slower than a query written using an
<a href="functions.html#function_in"><code class="literal">IN(<em class="replaceable"><code>value_list</code></em>)</code></a>
operator that lists the same values that the subquery would
return.
</p></li><li><p>
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:
</p><pre class="programlisting">DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
</pre><p>
Exception: The preceding prohibition does not apply if you are
using a subquery for the modified table in the
<code class="literal">FROM</code> clause. Example:
</p><pre class="programlisting">UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
</pre><p>
Here the prohibition does not apply because the result from a
subquery in the <code class="literal">FROM</code> clause is stored as a
temporary table, so the relevant rows in <code class="literal">t</code>
have already been selected by the time the update to
<code class="literal">t</code> takes place.
</p></li><li><p>
Row comparison operations are only partially supported:
</p><div class="itemizedlist"><ul type="circle"><li><p>
For <code class="literal"><em class="replaceable"><code>expr</code></em> IN
(<em class="replaceable"><code>subquery</code></em>)</code>,
<em class="replaceable"><code>expr</code></em> can be an
<em class="replaceable"><code>n</code></em>-tuple (specified via row
constructor syntax) and the subquery can return rows of
<em class="replaceable"><code>n</code></em>-tuples.
</p></li><li><p>
For <code class="literal"><em class="replaceable"><code>expr</code></em>
<em class="replaceable"><code>op</code></em> {ALL|ANY|SOME}
(<em class="replaceable"><code>subquery</code></em>)</code>,
<em class="replaceable"><code>expr</code></em> must be a scalar value and
the subquery must be a column subquery; it cannot return
multiple-column rows.
</p></li></ul></div><p>
In other words, for a subquery that returns rows of
<em class="replaceable"><code>n</code></em>-tuples, this is supported:
</p><pre class="programlisting">(<em class="replaceable"><code>val_1</code></em>, ..., <em class="replaceable"><code>val_n</code></em>) IN (<em class="replaceable"><code>subquery</code></em>)
</pre><p>
But this is not supported:
</p><pre class="programlisting">(<em class="replaceable"><code>val_1</code></em>, ..., <em class="replaceable"><code>val_n</code></em>) <em class="replaceable"><code>op</code></em> {ALL|ANY|SOME} (<em class="replaceable"><code>subquery</code></em>)
</pre><p>
The reason for supporting row comparisons for
<code class="literal">IN</code> but not for the others is that
<code class="literal">IN</code> is implemented by rewriting it as a
sequence of
<a href="functions.html#operator_equal"><code class="literal">=</code></a>
comparisons and <a href="functions.html#operator_and"><code class="literal">AND</code></a>
operations. This approach cannot be used for
<code class="literal">ALL</code>, <code class="literal">ANY</code>, or
<code class="literal">SOME</code>.
</p></li><li><p>
Row constructors are not well optimized. The following two
expressions are equivalent, but only the second can be
optimized:
</p><pre class="programlisting">(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
</pre></li><li><p>
Subqueries in the <code class="literal">FROM</code> clause cannot be
correlated subqueries. They are materialized (executed to
produce a result set) before evaluating the outer query, so
they cannot be evaluated per row of the outer query.
</p></li><li><p>
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.
</p><p>
An exception occurs for the case where an
<code class="literal">IN</code> subquery can be rewritten as a
<code class="literal">SELECT DISTINCT</code> join. Example:
</p><pre class="programlisting">SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <em class="replaceable"><code>condition</code></em>);
</pre><p>
That statement can be rewritten as follows:
</p><pre class="programlisting">SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <em class="replaceable"><code>condition</code></em>;
</pre><p>
But in this case, the join requires an extra
<code class="literal">DISTINCT</code> operation and is not more
efficient than the subquery.
</p></li><li><p>
Possible future optimization: MySQL does not rewrite the join
order for subquery evaluation. In some cases, a subquery could
be executed more efficiently if MySQL rewrote it as a join.
This would give the optimizer a chance to choose between more
execution plans. For example, it could decide whether to read
one table or the other first.
</p><p>
Example:
</p><pre class="programlisting">SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
</pre><p>
For that query, MySQL always scans
<code class="literal">outer_table</code> first and then executes the
subquery on <code class="literal">inner_table</code> for each row. If
<code class="literal">outer_table</code> has a lot of rows and
<code class="literal">inner_table</code> has few rows, the query
probably will not be as fast as it could be.
</p><p>
The preceding query could be rewritten like this:
</p><pre class="programlisting">SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;
</pre><p>
In this case, we can scan the small table
(<code class="literal">inner_table</code>) and look up rows in
<code class="literal">outer_table</code>, which will be fast if there is
an index on <code class="literal">(ot.a,ot.b)</code>.
</p></li><li><p>
Possible future optimization: A correlated subquery is
evaluated for each row of the outer query. A better approach
is that if the outer row values do not change from the
previous row, do not evaluate the subquery again. Instead, use
its previous result.
</p></li><li><p>
Possible future optimization: A subquery in the
<code class="literal">FROM</code> clause is evaluated by materializing
the result into a temporary table, and this table does not use
indexes. This does not allow the use of indexes in comparison
with other tables in the query, although that might be useful.
</p></li><li><p>
Possible future optimization: If a subquery in the
<code class="literal">FROM</code> clause resembles a view to which the
merge algorithm can be applied, rewrite the query and apply
the merge algorithm so that indexes can be used. The following
statement contains such a subquery:
</p><pre class="programlisting">SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
</pre><p>
The statement can be rewritten as a join like this:
</p><pre class="programlisting">SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
</pre><p>
This type of rewriting would provide two benefits:
</p><div class="itemizedlist"><ul type="circle"><li><p>
It avoids the use of a temporary table for which no
indexes can be used. In the rewritten query, the optimizer
can use indexes on <code class="literal">t1</code>.
</p></li><li><p>
It gives the optimizer more freedom to choose between
different execution plans. For example, rewriting the
query as a join allows the optimizer to use
<code class="literal">t1</code> or <code class="literal">t2</code> first.
</p></li></ul></div></li><li><p>
Possible future optimization: For <code class="literal">IN</code>,
<code class="literal">= ANY</code>, <code class="literal"><> ANY</code>,
<code class="literal">= ALL</code>, and <code class="literal"><> ALL</code>
with uncorrelated subqueries, use an in-memory hash for a
result or a temporary table with an index for larger results.
Example:
</p><pre class="programlisting">SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM <em class="replaceable"><code>table</code></em> WHERE <em class="replaceable"><code>condition</code></em>)
</pre><p>
In this case, we could create a temporary table:
</p><pre class="programlisting">CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM <em class="replaceable"><code>table</code></em> WHERE <em class="replaceable"><code>condition</code></em>)
</pre><p>
Then, for each row in <code class="literal">big_table</code>, do a key
lookup in <code class="literal">t</code> based on
<code class="literal">bt.non_key_field</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="view-restrictions"></a>F.4. Restrictions on Views</h2></div></div></div><a class="indexterm" name="id3362555"></a><a class="indexterm" name="id3362564"></a><p>
View processing is not optimized:
</p><div class="itemizedlist"><ul type="disc"><li><p>
It is not possible to create an index on a view.
</p></li><li><p>
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).
</p></li></ul></div><p>
Subqueries cannot be used in the <code class="literal">FROM</code> clause of
a view. This limitation will be lifted in the future.
</p><p>
There is a general principle that you cannot modify a table and
select from the same table in a subquery. See
<a href="restrictions.html#subquery-restrictions" title="F.3. Restrictions on Subqueries">Section F.3, “Restrictions on Subqueries”</a>.
</p><p>
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:
</p><pre class="programlisting">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;
</pre><p>
If the view is evaluated using a temporary table, you
<span class="emphasis"><em>can</em></span> 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
“<span class="quote">at the same time.</span>” (This is another reason you might
wish to force MySQL to use the temptable algorithm by specifying
<code class="literal">ALGORITHM = TEMPTABLE</code> in the view definition.)
</p><p>
You can use <code class="literal">DROP TABLE</code> or <code class="literal">ALTER
TABLE</code> to drop or alter a table that is used in a view
definition (which invalidates the view) and no warning results
from the drop or alter operation. An error occurs later when the
view is used.
</p><p>
A view definition is “<span class="quote">frozen</span>” by certain statements:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If a statement prepared by <code class="literal">PREPARE</code> refers
to a view, the view contents seen each time the statement is
executed later will be the contents 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:
</p><pre class="programlisting">CREATE VIEW v AS SELECT 1;
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT 2;
EXECUTE s;
</pre><p>
The result returned by the <code class="literal">EXECUTE</code>
statement is 1, not 2.
</p></li><li><p>
If a statement in a stored routine refers to a view, the view
contents seen by the statement are its contents 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 contents, even if the view
definition is changed later in the loop. Example:
</p><pre class="programlisting">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();
</pre><p>
When the procedure <code class="literal">p()</code> is called, the
<code class="literal">SELECT</code> returns 1 each time through the
loop, even though the view definition is changed within the
loop.
</p></li></ul></div><p>
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
<code class="literal">UNION</code> 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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Updatable views with subqueries anywhere other than in the
<code class="literal">WHERE</code> clause. Some views that have
subqueries in the <code class="literal">SELECT</code> list may be
updatable.
</p></li><li><p>
You cannot use <code class="literal">UPDATE</code> to update more than
one underlying table of a view that is defined as a join.
</p></li><li><p>
You cannot use <code class="literal">DELETE</code> to update a view that
is defined as a join.
</p></li></ul></div><a class="indexterm" name="id3362856"></a><a class="indexterm" name="id3362868"></a><a class="indexterm" name="id3362880"></a><a class="indexterm" name="id3362893"></a><a class="indexterm" name="id3362905"></a><a class="indexterm" name="id3362918"></a><p>
There exists a shortcoming with the current implementation of
views. If a user is granted the basic privileges necessary to
create a view (the <code class="literal">CREATE VIEW</code> and
<code class="literal">SELECT</code> privileges), that user will be unable to
call <code class="literal">SHOW CREATE VIEW</code> on that object unless the
user is also granted the <code class="literal">SHOW VIEW</code> privilege.
</p><p>
That shortcoming can lead to problems backing up a database with
<span><strong class="command">mysqldump</strong></span>, which may fail due to insufficient
privileges. This problem is described in <a href="http://bugs.mysql.com/22062" target="_top">Bug#22062</a>.
</p><p>
The workaround to the problem is for the administrator to manually
grant the <code class="literal">SHOW VIEW</code> privilege to users who are
granted <code class="literal">CREATE VIEW</code>, since MySQL doesn't grant
it implicitly when views are created.
</p><p>
Views do not have indexes, so index hints do not apply. Use of
index hints when selecting from a view is disallowed.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="xa-restrictions"></a>F.5. Restrictions on XA Transactions</h2></div></div></div><p>
XA transaction support is limited to the <code class="literal">InnoDB</code>
storage engine.
</p><p>
The MySQL XA implementation is for “<span class="quote">external XA,</span>”
where a MySQL server acts as a Resource Manager and client
programs act as Transaction Managers. “<span class="quote">Internal XA</span>”
is not implemented. This would allow individual storage engines
within a MySQL server to act as RMs, and the server itself to act
as a TM. Internal XA is required for handling XA transactions that
involve more than one storage engine. The implementation of
internal XA is incomplete because it requires that a storage
engine support two-phase commit at the table handler level, and
currently this is true only for <code class="literal">InnoDB</code>.
</p><p>
For <code class="literal">XA START</code>, the <code class="literal">JOIN</code> and
<code class="literal">RESUME</code> clauses are not supported.
</p><p>
For <code class="literal">XA END</code>, the <code class="literal">SUSPEND [FOR
MIGRATE]</code> clause is not supported.
</p><p>
The requirement that the <em class="replaceable"><code>bqual</code></em> part of
the <em class="replaceable"><code>xid</code></em> 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.
</p><p>
If an XA transaction has reached the <code class="literal">PREPARED</code>
state and the MySQL server is killed (for example, with
<span><strong class="command">kill -9</strong></span> 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.
</p><p>
It is possible that the server will roll back a pending XA
transaction, even one that has reached the
<code class="literal">PREPARED</code> 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 <code class="literal">PREPARED</code> XA
transaction associated with it.) It should be possible to commit
or roll back a <code class="literal">PREPARED</code> XA transaction, but
this cannot be done without changes to the binary logging
mechanism.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="limits"></a>F.6. Limits in MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="restrictions.html#joins-limits">F.6.1. Limits of Joins</a></span></dt><dt><span class="section"><a href="restrictions.html#column-count-limit">F.6.2. The Maximum Number of Columns Per Table</a></span></dt></dl></div><a class="indexterm" name="id3363167"></a><a class="indexterm" name="id3363180"></a><p>
This section lists current limits in MySQL 5.0.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="joins-limits"></a>F.6.1. Limits of Joins</h3></div></div></div><a class="indexterm" name="id3363205"></a><p>
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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="column-count-limit"></a>F.6.2. The Maximum Number of Columns Per Table</h3></div></div></div><a class="indexterm" name="id3363234"></a><p>
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, listed in the following
discussion.
</p><div class="itemizedlist"><ul type="disc"><li><p>
Every table has a maximum row size of 65,535 bytes. This
maximum applies to all storage engines, but a given engine
might have additional constraints that result in a lower
effective maximum row size.
</p><p>
The maximum row size constrains the number of columns
because the total width of all columns cannot exceed this
size. For example, <code class="literal">utf8</code> characters
require up to three bytes per character, so for a
<code class="literal">CHAR(255) CHARACTER SET utf8</code> column, the
server must allocate 255 × 3 = 765 bytes per value.
Consequently, a table cannot contain more than 65,535 / 765
= 85 such columns.
</p><p>
Storage for variable-length columns includes length bytes,
which are assessed against the row size. For example, a
<code class="literal">VARCHAR(255) CHARACTER SET utf8</code> column
takes two bytes to store the length of the value, so each
value can take up to 767 bytes.
</p><p>
<code class="literal">BLOB</code> and <code class="literal">TEXT</code> columns
count from one to four plus eight bytes each toward the
row-size limit because their contents are stored separately.
</p><p>
Declaring columns <code class="literal">NULL</code> can reduce the
maximum number of columns allowed. <code class="literal">NULL</code>
columns require additional space in the row to record
whether or not their values are <code class="literal">NULL</code>.
</p><p>
For <code class="literal">MyISAM</code> tables, each
<code class="literal">NULL</code> column takes one bit extra, rounded
up to the nearest byte. The maximum row length in bytes can
be calculated as follows:
</p><pre class="programlisting">row length = 1
+ (<em class="replaceable"><code>sum of column lengths</code></em>)
+ (<em class="replaceable"><code>number of NULL columns</code></em> + <em class="replaceable"><code>delete_flag</code></em> + 7)/8
+ (<em class="replaceable"><code>number of variable-length columns</code></em>)
</pre><p>
<em class="replaceable"><code>delete_flag</code></em> 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.
<em class="replaceable"><code>delete_flag</code></em> is 0 for dynamic
tables because the flag is stored in the dynamic row header.
</p><p>
These calculations do not apply for
<code class="literal">InnoDB</code> tables, for which storage size is
no different for <code class="literal">NULL</code> columns than for
<code class="literal">NOT NULL</code> columns.
</p><p>
The following statement to create table
<code class="literal">t1</code> 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:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t1</code></strong>
-> <strong class="userinput"><code>(c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
The following statement to create table
<code class="literal">t2</code> fails because the columns are
<code class="literal">NULL</code> and require additional space that
causes the row size to exceed 65,535 bytes:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t2</code></strong>
-> <strong class="userinput"><code>(c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL);</code></strong>
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
</pre></li><li><p>
Each table has an <code class="filename">.frm</code> file that
contains the table definition. The <code class="filename">.frm</code>
file size limit is fixed at 64KB. If a table definition
reaches this size, no more columns can be added. The
expression that checks information to be stored in the
<code class="filename">.frm</code> file against the limit looks like
this:
</p><pre class="programlisting">if (info_length+(ulong) create_fields.elements*FCOMP+288+
n_length+int_length+com_length > 65535L || int_count > 255)
</pre><p>
The relevant factors in this expression are:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">info_length</code> is space needed for
“<span class="quote">screens.</span>” This is related to MySQL's
Unireg heritage.
</p></li><li><p>
<code class="literal">create_fields.elements</code> is the number
of columns.
</p></li><li><p>
<code class="literal">FCOMP</code> is 17.
</p></li><li><p>
<code class="literal">n_length</code> is the total length of all
column names, including one byte per name as a
separator.
</p></li><li><p>
<code class="literal">int_length</code> is related to the list of
values for SET and ENUM columns.
</p></li><li><p>
<code class="literal">com_length</code> is the total length of
column and table comments.
</p></li></ul></div><p>
Thus, using long column names can reduce the maximum number
of columns, as can the inclusion of <code class="literal">ENUM</code>
or <code class="literal">SET</code> columns, or use of column or table
comments.
</p></li><li><p>
Individual storage engines might impose additional
restrictions that limit table column count. Examples:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">InnoDB</code> allows no more than 1000
columns.
</p></li><li><p>
<code class="literal">InnoDB</code> restricts row size to
something less than half a database page (approximately
8000 bytes), not including <code class="literal">VARBINARY</code>,
<code class="literal">VARCHAR</code>, <code class="literal">BLOB</code>, or
<code class="literal">TEXT</code> columns.
</p></li><li><p>
Different <code class="literal">InnoDB</code> storage formats
(<code class="literal">COMPRESSED</code>,
<code class="literal">REDUNDANT</code>) use different amounts of
page header and trailer data, which affects the amount
of storage available for rows.
</p></li></ul></div></li></ul></div></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="news.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="credits.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Appendix E. MySQL Change History </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Appendix G. Credits</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
109
