<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Appendix A. MySQL 5.0 Frequently Asked Questions</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="extending-mysql.html" title="Chapter 26. Extending MySQL"><link rel="next" href="error-handling.html" title="Appendix B. Errors, Error Codes, and Common Problems"></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 A. MySQL 5.0 Frequently Asked Questions</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="extending-mysql.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="error-handling.html">Next</a></td></tr></table><hr></div><div class="appendix" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="faqs"></a>Appendix A. MySQL 5.0 Frequently Asked Questions</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="faqs.html#faqs-general">A.1. MySQL 5.0 FAQ — General</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-storage-engines">A.2. MySQL 5.0 FAQ — Storage Engines</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-sql-modes">A.3. MySQL 5.0 FAQ — Server SQL Mode</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-stored-procs">A.4. MySQL 5.0 FAQ — Stored Procedures</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-triggers">A.5. MySQL 5.0 FAQ — Triggers</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-views">A.6. MySQL 5.0 FAQ — Views</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-information-schema">A.7. MySQL 5.0 FAQ — <code class="literal">INFORMATION_SCHEMA</code></a></span></dt><dt><span class="section"><a href="faqs.html#faqs-migration">A.8. MySQL 5.0 FAQ — Migration</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-security">A.9. MySQL 5.0 FAQ — Security</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-mysql-cluster">A.10. MySQL 5.0 FAQ — MySQL Cluster</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-cjk">A.11. MySQL 5.0 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-connectors-apis">A.12. MySQL 5.0 FAQ — Connectors & APIs</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-replication">A.13. MySQL 5.0 FAQ — Replication</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-mysql-drbd-heartbeat">A.14. MySQL 5.0 FAQ — MySQL, DRBD, and Heartbeat</a></span></dt><dd><dl><dt><span class="section"><a href="faqs.html#faqs-drbd">A.14.1. Distributed Replicated Block Device</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-linux-heartbeat">A.14.2. Linux Heartbeat</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-architecture">A.14.3. DRBD Architecture</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-mysql-replication-scale">A.14.4. DRBD and MySQL Replication</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-file-systems">A.14.5. DRBD and File Systems</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-lvm">A.14.6. DRBD and LVM</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-virtualization">A.14.7. DRBD and Virtualization</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-security">A.14.8. DRBD and Security</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-system-requirements">A.14.9. DRBD and System Requirements</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-support-consulting">A.14.10. DBRD and Support and Consulting</a></span></dt></dl></dd></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-general"></a>A.1. MySQL 5.0 FAQ — General</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-1-1">28.1.1: </a>
When did MySQL 5.0 become production-ready
(GA)?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-2">28.1.2: </a>
Can MySQL 5.0 do subqueries?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-3">28.1.3: </a>
Can MySQL 5.0 peform multiple-table inserts,
updates, and deletes?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-4">28.1.4: </a>
Does MySQL 5.0 have a Query Cache? Does it work
on Server, Instance or Database?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-5">28.1.5: </a>
Does MySQL 5.0 have Sequences?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-6">28.1.6: </a>
Does MySQL 5.0 have a
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> function with
fractions of seconds?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-7">28.1.7: </a>
Does MySQL 5.0 work with multi-core processors?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-8">28.1.8: </a>
Is there a hot backup tool for MyISAM like InnoDB Hot
Backup?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-9">28.1.9: </a>
Have there been there any improvements in error reporting
when foreign keys fail? Does MySQL now report which column
and reference failed?
</p></li><li><p><a href="faqs.html#qandaitem-28-1-10">28.1.10: </a>
Can MySQL 5.0 perform ACID transactions?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-1-1"></a><span class="bold"><strong>28.1.1: </strong></span><span class="bold"><strong>
When did MySQL 5.0 become production-ready
(GA)?
</strong></span></p><p>
MySQL 5.0.15 was released for production use on 19 October
2005. We are now working on MySQL 5.1, which is currently in
beta.
</p><p><a name="qandaitem-28-1-2"></a><span class="bold"><strong>28.1.2: </strong></span><span class="bold"><strong>
Can MySQL 5.0 do subqueries?
</strong></span></p><p>
Yes. See <a href="sql-syntax.html#subqueries" title="12.2.8. Subquery Syntax">Section 12.2.8, “Subquery Syntax”</a>.
</p><p><a name="qandaitem-28-1-3"></a><span class="bold"><strong>28.1.3: </strong></span><span class="bold"><strong>
Can MySQL 5.0 peform multiple-table inserts,
updates, and deletes?
</strong></span></p><p>
Yes. For the syntax required to perform multiple-table
updates, see <a href="sql-syntax.html#update" title="12.2.10. UPDATE Syntax">Section 12.2.10, “<code class="literal">UPDATE</code> Syntax”</a>; for that required to
perform multiple-table deletes, see
<a href="sql-syntax.html#delete" title="12.2.1. DELETE Syntax">Section 12.2.1, “<code class="literal">DELETE</code> Syntax”</a>.
</p><p>
A multiple-table insert can be accomplished using a trigger
whose <code class="literal">FOR EACH ROW</code> clause contains
multiple <code class="literal">INSERT</code> statements within a
<code class="literal">BEGIN ... END</code> block. See
<a href="triggers.html#using-triggers" title="19.3. Using Triggers">Section 19.3, “Using Triggers”</a>.
</p><p><a name="qandaitem-28-1-4"></a><span class="bold"><strong>28.1.4: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have a Query Cache? Does it work
on Server, Instance or Database?
</strong></span></p><p>
Yes. The query cache operates on the server level, caching
complete result sets matched with the original query string.
If an exactly identical query is made (which often happens,
particularly in web applications), no parsing or execution
is necessary; the result is sent directly from the cache.
Various tuning options are available. See
<a href="optimization.html#query-cache" title="6.5.4. The MySQL Query Cache">Section 6.5.4, “The MySQL Query Cache”</a>.
</p><p><a name="qandaitem-28-1-5"></a><span class="bold"><strong>28.1.5: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have Sequences?
</strong></span></p><p>
No. However, MySQL has an <code class="literal">AUTO_INCREMENT</code>
system, which in MySQL 5.0 can also handle
inserts in a multi-master replication setup. With the
<code class="option">--auto-increment-increment</code> and
<code class="option">--auto-increment-offset</code> startup options,
you can set each server to generate auto-increment values
that don't conflict with other servers. The
<code class="option">--auto-increment-increment</code> value should be
greater than the number of servers, and each server should
have a unique offset.
</p><p><a name="qandaitem-28-1-6"></a><span class="bold"><strong>28.1.6: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have a
<a href="functions.html#function_now"><code class="literal">NOW()</code></a> function with
fractions of seconds?
</strong></span></p><p>
No. This is on the MySQL roadmap as a “<span class="quote">rolling
feature</span>”. This means that it is not a flagship
feature, but will be implemented, development time
permitting. Specific customer demand may change this
scheduling.
</p><p>
However, MySQL does parse time strings with a fractional
component. See <a href="data-types.html#time" title="10.3.2. The TIME Type">Section 10.3.2, “The <code class="literal">TIME</code> Type”</a>.
</p><p><a name="qandaitem-28-1-7"></a><span class="bold"><strong>28.1.7: </strong></span><span class="bold"><strong>
Does MySQL 5.0 work with multi-core processors?
</strong></span></p><p>
Yes. MySQL is fully multi-threaded, and will make use of
multiple CPUs, provided that the operating system supports
them.
</p><p><a name="qandaitem-28-1-8"></a><span class="bold"><strong>28.1.8: </strong></span><span class="bold"><strong>
Is there a hot backup tool for MyISAM like InnoDB Hot
Backup?
</strong></span></p><p>
This is currently under development for a future MySQL
release.
</p><p><a name="qandaitem-28-1-9"></a><span class="bold"><strong>28.1.9: </strong></span><span class="bold"><strong>
Have there been there any improvements in error reporting
when foreign keys fail? Does MySQL now report which column
and reference failed?
</strong></span></p><p>
The foreign key support in <code class="literal">InnoDB</code> has
seen improvements in each major version of MySQL. Foreign
key support generic to all storage engines is scheduled for
MySQL 6.x; this should resolve any inadequacies in the
current storage engine specific implementation.
</p><p><a name="qandaitem-28-1-10"></a><span class="bold"><strong>28.1.10: </strong></span><span class="bold"><strong>
Can MySQL 5.0 perform ACID transactions?
</strong></span></p><p>
Yes. All current MySQL versions support transactions. The
<code class="literal">InnoDB</code> storage engine offers full ACID
transactions with row-level locking, multi-versioning,
non-locking repeatable reads, and all four SQL standard
isolation levels.
</p><p>
The <code class="literal">NDB</code> storage engine supports the
<code class="literal">READ COMMITTED</code> transaction isolation
level only.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-storage-engines"></a>A.2. MySQL 5.0 FAQ — Storage Engines</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-2-1">28.2.1: </a>
Where can I obtain complete documentation for MySQL storage
engines?
</p></li><li><p><a href="faqs.html#qandaitem-28-2-2">28.2.2: </a>
Are there any new storage engines in MySQL 5.0?
</p></li><li><p><a href="faqs.html#qandaitem-28-2-3">28.2.3: </a>
Have any storage engines been removed in MySQL
5.0?
</p></li><li><p><a href="faqs.html#qandaitem-28-2-4">28.2.4: </a>
What are the unique benefits of the
<code class="literal">ARCHIVE</code> storage engine?
</p></li><li><p><a href="faqs.html#qandaitem-28-2-5">28.2.5: </a>
Do the new features in MySQL 5.0 apply to all
storage engines?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-2-1"></a><span class="bold"><strong>28.2.1: </strong></span><span class="bold"><strong>
Where can I obtain complete documentation for MySQL storage
engines?
</strong></span></p><p>
See <a href="storage-engines.html" title="Chapter 13. Storage Engines">Chapter 13, <i>Storage Engines</i></a>. That chapter contains
information about all MySQL storage engines except for the
<code class="literal">NDB</code> storage engine used for MySQL
Cluster; <code class="literal">NDB</code> is covered in
<a href="mysql-cluster.html" title="Chapter 16. MySQL Cluster">Chapter 16, <i>MySQL Cluster</i></a>.
</p><p><a name="qandaitem-28-2-2"></a><span class="bold"><strong>28.2.2: </strong></span><span class="bold"><strong>
Are there any new storage engines in MySQL 5.0?
</strong></span></p><p>
Yes. The <code class="literal">FEDERATED</code> storage engine, new in
MySQL 5.0, allows the server to access tables
on other (remote) servers. See
<a href="storage-engines.html#federated-storage-engine" title="13.7. The FEDERATED Storage Engine">Section 13.7, “The <code class="literal">FEDERATED</code> Storage Engine”</a>.
</p><p><a name="qandaitem-28-2-3"></a><span class="bold"><strong>28.2.3: </strong></span><span class="bold"><strong>
Have any storage engines been removed in MySQL
5.0?
</strong></span></p><p>
Yes. MySQL 5.0 no longer supports the
<code class="literal">ISAM</code> storage engine. If you have any
existing <code class="literal">ISAM</code> tables from previous
versions of MySQL, you should convert these to
<code class="literal">MyISAM</code> before upgrading to MySQL
5.0.
</p><p><a name="qandaitem-28-2-4"></a><span class="bold"><strong>28.2.4: </strong></span><span class="bold"><strong>
What are the unique benefits of the
<code class="literal">ARCHIVE</code> storage engine?
</strong></span></p><p>
The <code class="literal">ARCHIVE</code> storage engine is ideally
suited for storing large amounts of data without indexes; it
has a very small footprint, and performs selects using table
scans. See <a href="storage-engines.html#archive-storage-engine" title="13.8. The ARCHIVE Storage Engine">Section 13.8, “The <code class="literal">ARCHIVE</code> Storage Engine”</a>, for
details.
</p><p><a name="qandaitem-28-2-5"></a><span class="bold"><strong>28.2.5: </strong></span><span class="bold"><strong>
Do the new features in MySQL 5.0 apply to all
storage engines?
</strong></span></p><p>
The general new features such as views, stored procedures,
triggers, <code class="literal">INFORMATION_SCHEMA</code>, precision
math (<code class="literal">DECIMAL</code> column type), and the
<code class="literal">BIT</code> column type, apply to all storage
engines. There are also additions and changes for specific
storage engines.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-sql-modes"></a>A.3. MySQL 5.0 FAQ — Server SQL Mode</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-3-1">28.3.1: </a>
What are server SQL modes?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-2">28.3.2: </a>
How many server SQL modes are there?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-3">28.3.3: </a>
How do you determine the server SQL mode?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-4">28.3.4: </a>
Is the mode dependent on the database or connection?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-5">28.3.5: </a>
Can the rules for strict mode be extended?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-6">28.3.6: </a>
Does strict mode impact performance?
</p></li><li><p><a href="faqs.html#qandaitem-28-3-7">28.3.7: </a>
What is the default server SQL mode when My SQL
5.0 is installed?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-3-1"></a><span class="bold"><strong>28.3.1: </strong></span><span class="bold"><strong>
What are server SQL modes?
</strong></span></p><p>
Server SQL modes define what SQL syntax MySQL should support
and what kind of data validation checks it should perform.
This makes it easier to use MySQL in different environments
and to use MySQL together with other database servers. The
MySQL Server apply these modes individually to different
clients. For more information, see
<a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>.
</p><p><a name="qandaitem-28-3-2"></a><span class="bold"><strong>28.3.2: </strong></span><span class="bold"><strong>
How many server SQL modes are there?
</strong></span></p><p>
Each mode can be independently switched on and off. See
<a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>, for a complete list of
available modes.
</p><p><a name="qandaitem-28-3-3"></a><span class="bold"><strong>28.3.3: </strong></span><span class="bold"><strong>
How do you determine the server SQL mode?
</strong></span></p><p>
You can set the default SQL mode (for
<span><strong class="command">mysqld</strong></span> startup) with the
<code class="option">--sql-mode</code> option. Using the statement
<code class="literal">SET [SESSION|GLOBAL]
sql_mode='<em class="replaceable"><code>modes</code></em>'</code>, you
can change the settings from within a connection, either
locally to the connection, or to take effect globally. You
can retrieve the current mode by issuing a <code class="literal">SELECT
@@sql_mode</code> statement.
</p><p><a name="qandaitem-28-3-4"></a><span class="bold"><strong>28.3.4: </strong></span><span class="bold"><strong>
Is the mode dependent on the database or connection?
</strong></span></p><p>
A mode is not linked to a particular database. Modes can be
set locally to the session (connection), or globally for the
server. you can change these settings using <code class="literal">SET
[SESSION|GLOBAL]
sql_mode='<em class="replaceable"><code>modes</code></em>'</code>.
</p><p><a name="qandaitem-28-3-5"></a><span class="bold"><strong>28.3.5: </strong></span><span class="bold"><strong>
Can the rules for strict mode be extended?
</strong></span></p><p>
When we refer to <span class="emphasis"><em>strict mode</em></span>, we mean a
mode where at least one of the modes
<code class="literal">TRADITIONAL</code>,
<code class="literal">STRICT_TRANS_TABLES</code>, or
<code class="literal">STRICT_ALL_TABLES</code> is enabled. Options can
be combined, so you can add additional restrictions to a
mode. See <a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>, for more
information.
</p><p><a name="qandaitem-28-3-6"></a><span class="bold"><strong>28.3.6: </strong></span><span class="bold"><strong>
Does strict mode impact performance?
</strong></span></p><p>
The intensive validation of input data that some settings
requires more time than if the validation is not done. While
the performance impact is not that great, if you do not
require such validation (perhaps your application already
handles all of this), then MySQL gives you the option of
leaving strict mode disabled. However — if you do
require it — strict mode can provide such validation.
</p><p><a name="qandaitem-28-3-7"></a><span class="bold"><strong>28.3.7: </strong></span><span class="bold"><strong>
What is the default server SQL mode when My SQL
5.0 is installed?
</strong></span></p><p>
By default, no special modes are enabled. See
<a href="server-administration.html#server-sql-mode" title="5.2.6. SQL Modes">Section 5.2.6, “SQL Modes”</a>, for information about all
available modes and MySQL's default behavior.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-stored-procs"></a>A.4. MySQL 5.0 FAQ — Stored Procedures</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-4-1">28.4.1: </a>
Does MySQL 5.0 support stored procedures?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-2">28.4.2: </a>
Where can I find documentation for MySQL stored procedures
and stored functions?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-3">28.4.3: </a>
Is there a discussion forum for MySQL stored procedures?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-4">28.4.4: </a>
Where can I find the ANSI SQL 2003 specification for stored
procedures?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-5">28.4.5: </a>
How do you manage stored routines?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-6">28.4.6: </a>
Is there a way to view all stored procedures and stored
functions in a given database?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-7">28.4.7: </a>
Where are stored procedures stored?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-8">28.4.8: </a>
Is it possible to group stored procedures or stored
functions into packages?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-9">28.4.9: </a>
Can a stored procedure call another stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-10">28.4.10: </a>
Can a stored procedure call a trigger?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-11">28.4.11: </a>
Can a stored procedure access tables?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-12">28.4.12: </a>
Do stored procedures have a statement for raising
application errors?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-13">28.4.13: </a>
Do stored procedures provide exception handling?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-14">28.4.14: </a>
Can MySQL 5.0 stored routines return result
sets?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-15">28.4.15: </a>
Is <code class="literal">WITH RECOMPILE</code> supported for stored
procedures?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-16">28.4.16: </a>
Is there a MySQL equivalent to using
<code class="literal">mod_plsql</code> as a gateway on Apache to talk
directly to a stored procedure in the database?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-17">28.4.17: </a>
Can I pass an array as input to a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-18">28.4.18: </a>
Can I pass a cursor as an <code class="literal">IN</code> parameter to
a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-19">28.4.19: </a>
Can I return a cursor as an <code class="literal">OUT</code> parameter
from a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-20">28.4.20: </a>
Can I print out a variable's value within a stored routine
for debugging purposes?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-21">28.4.21: </a>
Can I commit or roll back transactions inside a stored
procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-22">28.4.22: </a>
Do MySQL 5.0 stored procedures and functions
work with replication?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-23">28.4.23: </a>
Are stored procedures and functions created on a master
server replicated to a slave?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-24">28.4.24: </a>
How are actions that take place inside stored procedures and
functions replicated?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-25">28.4.25: </a>
Are there special security requirements for using stored
procedures and functions together with replication?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-26">28.4.26: </a>
What limitations exist for replicating stored procedure and
function actions?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-27">28.4.27: </a>
Do the preceding limitations affect MySQL's ability to do
point-in-time recovery?
</p></li><li><p><a href="faqs.html#qandaitem-28-4-28">28.4.28: </a>
What is being done to correct the aforementioned
limitations?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-4-1"></a><span class="bold"><strong>28.4.1: </strong></span><span class="bold"><strong>
Does MySQL 5.0 support stored procedures?
</strong></span></p><p>
Yes. MySQL 5.0 supports two types of stored
routines — stored procedures and stored functions.
</p><p><a name="qandaitem-28-4-2"></a><span class="bold"><strong>28.4.2: </strong></span><span class="bold"><strong>
Where can I find documentation for MySQL stored procedures
and stored functions?
</strong></span></p><p>
See <a href="stored-procedures.html" title="Chapter 18. Stored Procedures and Functions">Chapter 18, <i>Stored Procedures and Functions</i></a>.
</p><p><a name="qandaitem-28-4-3"></a><span class="bold"><strong>28.4.3: </strong></span><span class="bold"><strong>
Is there a discussion forum for MySQL stored procedures?
</strong></span></p><p>
Yes. See
<a href="http://forums.mysql.com/list.php?98" target="_top">http://forums.mysql.com/list.php?98</a>.
</p><p><a name="qandaitem-28-4-4"></a><span class="bold"><strong>28.4.4: </strong></span><span class="bold"><strong>
Where can I find the ANSI SQL 2003 specification for stored
procedures?
</strong></span></p><p>
Unfortunately, the official specifications are not freely
available (ANSI makes them available for purchase). However,
there are books — such as <em class="citetitle">SQL-99 Complete,
Really</em> by Peter Gulutzan and Trudy Pelzer
— which give a comprehensive overview of the standard,
including coverage of stored procedures.
</p><p><a name="qandaitem-28-4-5"></a><span class="bold"><strong>28.4.5: </strong></span><span class="bold"><strong>
How do you manage stored routines?
</strong></span></p><p>
It is always good practice to use a clear naming scheme for
your stored routines. You can manage stored procedures with
<code class="literal">CREATE [FUNCTION|PROCEDURE]</code>,
<code class="literal">ALTER [FUNCTION|PROCEDURE]</code>, <code class="literal">DROP
[FUNCTION|PROCEDURE]</code>, and <code class="literal">SHOW CREATE
[FUNCTION|PROCEDURE]</code>. You can obtain information
about existing stored procedures using the
<code class="literal">ROUTINES</code> table in the
<code class="literal">INFORMATION_SCHEMA</code> database (see
<a href="information-schema.html#routines-table" title="21.14. The INFORMATION_SCHEMA ROUTINES Table">Section 21.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>).
</p><p><a name="qandaitem-28-4-6"></a><span class="bold"><strong>28.4.6: </strong></span><span class="bold"><strong>
Is there a way to view all stored procedures and stored
functions in a given database?
</strong></span></p><p>
Yes. For a database named <em class="replaceable"><code>dbname</code></em>,
use this query on the
<code class="literal">INFORMATION_SCHEMA.ROUTINES</code> table:
</p><pre class="programlisting">SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='<em class="replaceable"><code>dbname</code></em>';
</pre><p>
For more information, see <a href="information-schema.html#routines-table" title="21.14. The INFORMATION_SCHEMA ROUTINES Table">Section 21.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>.
</p><p>
The body of a stored routine can be viewed using
<code class="literal">SHOW CREATE FUNCTION</code> (for a stored
function) or <code class="literal">SHOW CREATE PROCEDURE</code> (for a
stored procedure). See
<a href="sql-syntax.html#show-create-procedure" title="12.5.4.5. SHOW CREATE PROCEDURE and SHOW CREATE
FUNCTION Syntax">Section 12.5.4.5, “<code class="literal">SHOW CREATE PROCEDURE</code> and <code class="literal">SHOW CREATE
FUNCTION</code> Syntax”</a>, for more
information.
</p><p><a name="qandaitem-28-4-7"></a><span class="bold"><strong>28.4.7: </strong></span><span class="bold"><strong>
Where are stored procedures stored?
</strong></span></p><p>
In the <code class="literal">proc</code> table of the
<code class="literal">mysql</code> system database. However, you
should not access the tables in the system database
directly. Instead, use <code class="literal">SHOW CREATE
FUNCTION</code> to obtain information about stored
functions, and <code class="literal">SHOW CREATE PROCEDURE</code> to
obtain information about stored procedures. See
<a href="sql-syntax.html#show-create-procedure" title="12.5.4.5. SHOW CREATE PROCEDURE and SHOW CREATE
FUNCTION Syntax">Section 12.5.4.5, “<code class="literal">SHOW CREATE PROCEDURE</code> and <code class="literal">SHOW CREATE
FUNCTION</code> Syntax”</a>, for more
information about these statements.
</p><p>
You can also query the <code class="literal">ROUTINES</code> table in
the <code class="literal">INFORMATION_SCHEMA</code> database —
see <a href="information-schema.html#routines-table" title="21.14. The INFORMATION_SCHEMA ROUTINES Table">Section 21.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>, for information about
this table.
</p><p><a name="qandaitem-28-4-8"></a><span class="bold"><strong>28.4.8: </strong></span><span class="bold"><strong>
Is it possible to group stored procedures or stored
functions into packages?
</strong></span></p><p>
No. This is not supported in MySQL 5.0.
</p><p><a name="qandaitem-28-4-9"></a><span class="bold"><strong>28.4.9: </strong></span><span class="bold"><strong>
Can a stored procedure call another stored procedure?
</strong></span></p><p>
Yes.
</p><p><a name="qandaitem-28-4-10"></a><span class="bold"><strong>28.4.10: </strong></span><span class="bold"><strong>
Can a stored procedure call a trigger?
</strong></span></p><p>
A stored procedure can execute an SQL statement, such as an
<code class="literal">UPDATE</code>, that causes a trigger to fire.
</p><p><a name="qandaitem-28-4-11"></a><span class="bold"><strong>28.4.11: </strong></span><span class="bold"><strong>
Can a stored procedure access tables?
</strong></span></p><p>
Yes. A stored procedure can access one or more tables as
required.
</p><p><a name="qandaitem-28-4-12"></a><span class="bold"><strong>28.4.12: </strong></span><span class="bold"><strong>
Do stored procedures have a statement for raising
application errors?
</strong></span></p><p>
Not in MySQL 5.0. We intend to implement the
SQL standard <code class="literal">SIGNAL</code> and
<code class="literal">RESIGNAL</code> statements in a future MySQL
release.
</p><p><a name="qandaitem-28-4-13"></a><span class="bold"><strong>28.4.13: </strong></span><span class="bold"><strong>
Do stored procedures provide exception handling?
</strong></span></p><p>
MySQL implements <code class="literal">HANDLER</code> definitions
according to the SQL standard. See
<a href="stored-procedures.html#declare-handlers" title="18.2.8.2. DECLARE Handlers">Section 18.2.8.2, “<code class="literal">DECLARE</code> Handlers”</a>, for details.
</p><p><a name="qandaitem-28-4-14"></a><span class="bold"><strong>28.4.14: </strong></span><span class="bold"><strong>
Can MySQL 5.0 stored routines return result
sets?
</strong></span></p><p>
<span class="emphasis"><em>Stored procedures</em></span> can, but stored
functions cannot. If you perform an ordinary
<code class="literal">SELECT</code> inside a stored procedure, the
result set is returned directly to the client. You need to
use the MySQL 4.1 (or above) client-server protocol for this
to work. This means that — for instance — in
PHP, you need to use the <code class="literal">mysqli</code> extension
rather than the old <code class="literal">mysql</code> extension.
</p><p><a name="qandaitem-28-4-15"></a><span class="bold"><strong>28.4.15: </strong></span><span class="bold"><strong>
Is <code class="literal">WITH RECOMPILE</code> supported for stored
procedures?
</strong></span></p><p>
Not in MySQL 5.0.
</p><p><a name="qandaitem-28-4-16"></a><span class="bold"><strong>28.4.16: </strong></span><span class="bold"><strong>
Is there a MySQL equivalent to using
<code class="literal">mod_plsql</code> as a gateway on Apache to talk
directly to a stored procedure in the database?
</strong></span></p><p>
There is no equivalent in MySQL 5.0.
</p><p><a name="qandaitem-28-4-17"></a><span class="bold"><strong>28.4.17: </strong></span><span class="bold"><strong>
Can I pass an array as input to a stored procedure?
</strong></span></p><p>
Not in MySQL 5.0.
</p><p><a name="qandaitem-28-4-18"></a><span class="bold"><strong>28.4.18: </strong></span><span class="bold"><strong>
Can I pass a cursor as an <code class="literal">IN</code> parameter to
a stored procedure?
</strong></span></p><p>
In MySQL 5.0, cursors are available inside
stored procedures only.
</p><p><a name="qandaitem-28-4-19"></a><span class="bold"><strong>28.4.19: </strong></span><span class="bold"><strong>
Can I return a cursor as an <code class="literal">OUT</code> parameter
from a stored procedure?
</strong></span></p><p>
In MySQL 5.0, cursors are available inside
stored procedures only. However, if you do not open a cursor
on a <code class="literal">SELECT</code>, the result will be sent
directly to the client. You can also <code class="literal">SELECT
INTO</code> variables. See <a href="sql-syntax.html#select" title="12.2.7. SELECT Syntax">Section 12.2.7, “<code class="literal">SELECT</code> Syntax”</a>.
</p><p><a name="qandaitem-28-4-20"></a><span class="bold"><strong>28.4.20: </strong></span><span class="bold"><strong>
Can I print out a variable's value within a stored routine
for debugging purposes?
</strong></span></p><p>
Yes, you can do this in a <span class="emphasis"><em>stored
procedure</em></span>, but not in a stored function. If you
perform an ordinary <code class="literal">SELECT</code> inside a
stored procedure, the result set is returned directly to the
client. You will need to use the MySQL 4.1 (or above)
client-server protocol for this to work. This means that
— for instance — in PHP, you need to use the
<code class="literal">mysqli</code> extension rather than the old
<code class="literal">mysql</code> extension.
</p><p><a name="qandaitem-28-4-21"></a><span class="bold"><strong>28.4.21: </strong></span><span class="bold"><strong>
Can I commit or roll back transactions inside a stored
procedure?
</strong></span></p><p>
Yes. However, you cannot perform transactional operations
within a stored function.
</p><p><a name="qandaitem-28-4-22"></a><span class="bold"><strong>28.4.22: </strong></span><span class="bold"><strong>
Do MySQL 5.0 stored procedures and functions
work with replication?
</strong></span></p><p>
Yes, standard actions carried out in stored procedures and
functions are replicated from a master MySQL server to a
slave server. There are a few limitations that are described
in detail 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><a name="qandaitem-28-4-23"></a><span class="bold"><strong>28.4.23: </strong></span><span class="bold"><strong>
Are stored procedures and functions created on a master
server replicated to a slave?
</strong></span></p><p>
Yes, creation of stored procedures and functions carried out
through normal DDL statements on a master server are
replicated to a slave, so the objects will exist on both
servers. <code class="literal">ALTER</code> and
<code class="literal">DROP</code> statements for stored procedures and
functions are also replicated.
</p><p><a name="qandaitem-28-4-24"></a><span class="bold"><strong>28.4.24: </strong></span><span class="bold"><strong>
How are actions that take place inside stored procedures and
functions replicated?
</strong></span></p><p>
MySQL records each DML event that occurs in a stored
procedure and replicates those individual actions to a slave
server. The actual calls made to execute stored procedures
are not replicated.
</p><p>
Stored functions that change data are logged as function
invocations, not as the DML events that occur inside each
function.
</p><p><a name="qandaitem-28-4-25"></a><span class="bold"><strong>28.4.25: </strong></span><span class="bold"><strong>
Are there special security requirements for using stored
procedures and functions together with replication?
</strong></span></p><p>
Yes. Because a slave server has authority to execute any
statement read from a master's binary log, special security
constraints exist for using stored functions with
replication. If replication or binary logging in general
(for the purpose of point-in-time recovery) is active, then
MySQL DBAs have two security options open to them:
</p><div class="orderedlist"><ol type="1"><li><p>
Any user wishing to create stored functions must be
granted the <code class="literal">SUPER</code> privilege.
</p></li><li><p>
Alternatively, a DBA can set the
<code class="literal">log_bin_trust_function_creators</code>
system variable to 1, which enables anyone with the
standard <code class="literal">CREATE ROUTINE</code> privilege
to create stored functions.
</p></li></ol></div><p>
</p><p><a name="qandaitem-28-4-26"></a><span class="bold"><strong>28.4.26: </strong></span><span class="bold"><strong>
What limitations exist for replicating stored procedure and
function actions?
</strong></span></p><p>
Non-deterministic (random) or time-based actions embedded in
stored procedures may not replicate properly. By their very
nature, randomly produced results are not predictable and
cannot be exactly reproduced, and therefore, random actions
replicated to a slave will not mirror those performed on a
master. Note that declaring stored functions to be
<code class="literal">DETERMINISTIC</code> or setting the
<code class="literal">log_bin_trust_function_creators</code> system
variable to 0 will not allow random-valued operations to be
invoked.
</p><p>
In addition, time-based actions cannot be reproduced on a
slave because the timing of such actions in a stored
procedure is not reproducible through the binary log used
for replication. It records only DML events and does not
factor in timing constraints.
</p><p>
Finally, non-transactional tables for which errors occur
during large DML actions (such as bulk inserts) may
experience replication issues in that a master may be
partially updated from DML activity, but no updates are done
to the slave because of the errors that occurred. A
workaround is for a function's DML actions to be carried out
with the <code class="literal">IGNORE</code> keyword so that updates
on the master that cause errors are ignored and updates that
do not cause errors are replicated to the slave.
</p><p><a name="qandaitem-28-4-27"></a><span class="bold"><strong>28.4.27: </strong></span><span class="bold"><strong>
Do the preceding limitations affect MySQL's ability to do
point-in-time recovery?
</strong></span></p><p>
The same limitations that affect replication do affect
point-in-time recovery.
</p><p><a name="qandaitem-28-4-28"></a><span class="bold"><strong>28.4.28: </strong></span><span class="bold"><strong>
What is being done to correct the aforementioned
limitations?
</strong></span></p><p>
MySQL 5.1 implements <em class="firstterm">row-based
replication</em>, which resolves the limitations
mentioned earlier.
</p><p>
We do not plan to backport row-based replication to MySQL
5.0. For additional information, see
<a href="http://dev.mysql.com/doc/refman" target="_top"><em class="citetitle">Row-Based
Replication</em>, in the <em class="citetitle">MySQL 5.1
Manual</em></a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-triggers"></a>A.5. MySQL 5.0 FAQ — Triggers</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-5-1">28.5.1: </a>
Where can I find the documentation for MySQL
5.0 triggers?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-2">28.5.2: </a>
Is there a discussion forum for MySQL Triggers?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-3">28.5.3: </a>
Does MySQL 5.0 have statement-level or
row-level triggers?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-4">28.5.4: </a>
Are there any default triggers?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-5">28.5.5: </a>
How are triggers managed in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-6">28.5.6: </a>
Is there a way to view all triggers in a given database?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-7">28.5.7: </a>
Where are triggers stored?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-8">28.5.8: </a>
Can a trigger call a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-9">28.5.9: </a>
Can triggers access tables?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-10">28.5.10: </a>
Can triggers call an external application through a UDF?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-11">28.5.11: </a>
Is possible for a trigger to update tables on a remote
server?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-12">28.5.12: </a>
Do triggers work with replication?
</p></li><li><p><a href="faqs.html#qandaitem-28-5-13">28.5.13: </a>
How are actions carried out through triggers on a master
replicated to a slave?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-5-1"></a><span class="bold"><strong>28.5.1: </strong></span><span class="bold"><strong>
Where can I find the documentation for MySQL
5.0 triggers?
</strong></span></p><p>
See <a href="triggers.html" title="Chapter 19. Triggers">Chapter 19, <i>Triggers</i></a>.
</p><p><a name="qandaitem-28-5-2"></a><span class="bold"><strong>28.5.2: </strong></span><span class="bold"><strong>
Is there a discussion forum for MySQL Triggers?
</strong></span></p><p>
Yes. It is available at
<a href="http://forums.mysql.com/list.php?99" target="_top">http://forums.mysql.com/list.php?99</a>.
</p><p><a name="qandaitem-28-5-3"></a><span class="bold"><strong>28.5.3: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have statement-level or
row-level triggers?
</strong></span></p><p>
In MySQL 5.0, all triggers are <code class="literal">FOR
EACH ROW</code> — that is, the trigger is activated
for each row that is inserted, updated, or deleted. MySQL
5.0 does not support triggers using
<code class="literal">FOR EACH STATEMENT</code>.
</p><p><a name="qandaitem-28-5-4"></a><span class="bold"><strong>28.5.4: </strong></span><span class="bold"><strong>
Are there any default triggers?
</strong></span></p><p>
Not explicitly. MySQL does have specific special behavior
for some <code class="literal">TIMESTAMP</code> columns, as well as
for columns which are defined using
<code class="literal">AUTO_INCREMENT</code>.
</p><p><a name="qandaitem-28-5-5"></a><span class="bold"><strong>28.5.5: </strong></span><span class="bold"><strong>
How are triggers managed in MySQL?
</strong></span></p><p>
In MySQL 5.0, triggers can be created using the
<code class="literal">CREATE TRIGGER</code> statement, and dropped
using <code class="literal">DROP TRIGGER</code>. See
<a href="triggers.html#create-trigger" title="19.1. CREATE TRIGGER Syntax">Section 19.1, “<code class="literal">CREATE TRIGGER</code> Syntax”</a>, and
<a href="triggers.html#drop-trigger" title="19.2. DROP TRIGGER Syntax">Section 19.2, “<code class="literal">DROP TRIGGER</code> Syntax”</a>, for more about these
statements.
</p><p>
Information about triggers can be obtained by querying the
<code class="literal">INFORMATION_SCHEMA.TRIGGERS</code> table. See
<a href="information-schema.html#triggers-table" title="21.16. The INFORMATION_SCHEMA TRIGGERS Table">Section 21.16, “The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table”</a>.
</p><p><a name="qandaitem-28-5-6"></a><span class="bold"><strong>28.5.6: </strong></span><span class="bold"><strong>
Is there a way to view all triggers in a given database?
</strong></span></p><p>
Yes. You can obtain a listing of all triggers defined on
database <code class="literal">dbname</code> using a query on the
<code class="literal">INFORMATION_SCHEMA.TRIGGERS</code> table such as
the one shown here:
</p><pre class="programlisting">SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='<em class="replaceable"><code>dbname</code></em>';
</pre><p>
For more information about this table, see
<a href="information-schema.html#triggers-table" title="21.16. The INFORMATION_SCHEMA TRIGGERS Table">Section 21.16, “The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table”</a>.
</p><p>
You can also use the <code class="literal">SHOW TRIGGERS</code>
statement, which is specific to MySQL. See
<a href="sql-syntax.html#show-triggers" title="12.5.4.26. SHOW TRIGGERS Syntax">Section 12.5.4.26, “<code class="literal">SHOW TRIGGERS</code> Syntax”</a>.
</p><p><a name="qandaitem-28-5-7"></a><span class="bold"><strong>28.5.7: </strong></span><span class="bold"><strong>
Where are triggers stored?
</strong></span></p><p>
Triggers for a table are currently stored in
<code class="filename">.TRG</code> files, with one such file one per
table.
</p><p><a name="qandaitem-28-5-8"></a><span class="bold"><strong>28.5.8: </strong></span><span class="bold"><strong>
Can a trigger call a stored procedure?
</strong></span></p><p>
Yes.
</p><p><a name="qandaitem-28-5-9"></a><span class="bold"><strong>28.5.9: </strong></span><span class="bold"><strong>
Can triggers access tables?
</strong></span></p><p>
A trigger can access both old and new data in its own table.
Through a stored procedure, or a multiple-table update or
delete statement, a trigger can also affect other tables.
</p><p><a name="qandaitem-28-5-10"></a><span class="bold"><strong>28.5.10: </strong></span><span class="bold"><strong>
Can triggers call an external application through a UDF?
</strong></span></p><p>
No, not at present.
</p><p><a name="qandaitem-28-5-11"></a><span class="bold"><strong>28.5.11: </strong></span><span class="bold"><strong>
Is possible for a trigger to update tables on a remote
server?
</strong></span></p><p>
Yes. A table on a remote server could be updated using the
<code class="literal">FEDERATED</code> storage engine. (See
<a href="storage-engines.html#federated-storage-engine" title="13.7. The FEDERATED Storage Engine">Section 13.7, “The <code class="literal">FEDERATED</code> Storage Engine”</a>).
</p><p><a name="qandaitem-28-5-12"></a><span class="bold"><strong>28.5.12: </strong></span><span class="bold"><strong>
Do triggers work with replication?
</strong></span></p><p>
Triggers and replication in MySQL 5.0 work in
the same wasy as in most other database engines: Actions
carried out through triggers on a master are not replicated
to a slave server. Instead, triggers that exist on tables
that reside on a MySQL master server need to be created on
the corresponding tables on any MySQL slave servers so that
the triggers activate on the slaves as well as the master.
</p><p><a name="qandaitem-28-5-13"></a><span class="bold"><strong>28.5.13: </strong></span><span class="bold"><strong>
How are actions carried out through triggers on a master
replicated to a slave?
</strong></span></p><p>
First, the triggers that exist on a master must be
re-created on the slave server. Once this is done, the
replication flow works as any other standard DML statement
that participates in replication. For example, consider a
table <code class="literal">EMP</code> that has an
<code class="literal">AFTER</code> insert trigger, which exists on a
master MySQL server. The same <code class="literal">EMP</code> table
and <code class="literal">AFTER</code> insert trigger exist on the
slave server as well. The replication flow would be:
</p><div class="orderedlist"><ol type="1"><li><p>
An <code class="literal">INSERT</code> statement is made to
<code class="literal">EMP</code>.
</p></li><li><p>
The <code class="literal">AFTER</code> trigger on
<code class="literal">EMP</code> activates.
</p></li><li><p>
The <code class="literal">INSERT</code> statement is written to
the binary log.
</p></li><li><p>
The replication slave picks up the
<code class="literal">INSERT</code> statement to
<code class="literal">EMP</code> and executes it.
</p></li><li><p>
The <code class="literal">AFTER</code> trigger on
<code class="literal">EMP</code> that exists on the slave
activates.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-views"></a>A.6. MySQL 5.0 FAQ — Views</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-6-1">28.6.1: </a>
Where can I find documentation covering MySQL Views?
</p></li><li><p><a href="faqs.html#qandaitem-28-6-2">28.6.2: </a>
Is there a discussion forum for MySQL Views?
</p></li><li><p><a href="faqs.html#qandaitem-28-6-3">28.6.3: </a>
What happens to a view if an underlying table is dropped or
renamed?
</p></li><li><p><a href="faqs.html#qandaitem-28-6-4">28.6.4: </a>
Does MySQL 5.0 have table snapshots?
</p></li><li><p><a href="faqs.html#qandaitem-28-6-5">28.6.5: </a>
Does MySQL 5.0 have materialized views?
</p></li><li><p><a href="faqs.html#qandaitem-28-6-6">28.6.6: </a>
Can you insert into views that are based on joins?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-6-1"></a><span class="bold"><strong>28.6.1: </strong></span><span class="bold"><strong>
Where can I find documentation covering MySQL Views?
</strong></span></p><p>
See <a href="views.html" title="Chapter 20. Views">Chapter 20, <i>Views</i></a>.
</p><p><a name="qandaitem-28-6-2"></a><span class="bold"><strong>28.6.2: </strong></span><span class="bold"><strong>
Is there a discussion forum for MySQL Views?
</strong></span></p><p>
Yes. See
<a href="http://forums.mysql.com/list.php?100" target="_top">http://forums.mysql.com/list.php?100</a>
</p><p><a name="qandaitem-28-6-3"></a><span class="bold"><strong>28.6.3: </strong></span><span class="bold"><strong>
What happens to a view if an underlying table is dropped or
renamed?
</strong></span></p><p>
After a view has been created, it is possible to drop or
alter a table or view to which the definition refers. To
check a view definition for problems of this kind, use the
<code class="literal">CHECK TABLE</code> statement. (See
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax">Section 12.5.2.3, “<code class="literal">CHECK TABLE</code> Syntax”</a>.)
</p><p><a name="qandaitem-28-6-4"></a><span class="bold"><strong>28.6.4: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have table snapshots?
</strong></span></p><p>
No.
</p><p><a name="qandaitem-28-6-5"></a><span class="bold"><strong>28.6.5: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have materialized views?
</strong></span></p><p>
No.
</p><p><a name="qandaitem-28-6-6"></a><span class="bold"><strong>28.6.6: </strong></span><span class="bold"><strong>
Can you insert into views that are based on joins?
</strong></span></p><p>
It is possible, provided that your <code class="literal">INSERT</code>
statement has a column list that makes it clear there's only
one table involved.
</p><p>
You <span class="emphasis"><em>cannot</em></span> insert into multiple tables
with a single insert on a view.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-information-schema"></a>A.7. MySQL 5.0 FAQ — <code class="literal">INFORMATION_SCHEMA</code></h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-7-1">28.7.1: </a>
Where can I find documentation for the MySQL
<code class="literal">INFORMATION_SCHEMA</code> database?
</p></li><li><p><a href="faqs.html#qandaitem-28-7-2">28.7.2: </a>
Is there a discussion forum for
<code class="literal">INFORMATION_SCHEMA</code>?
</p></li><li><p><a href="faqs.html#qandaitem-28-7-3">28.7.3: </a>
Where can I find the ANSI SQL 2003 specification for
<code class="literal">INFORMATION_SCHEMA</code>?
</p></li><li><p><a href="faqs.html#qandaitem-28-7-4">28.7.4: </a>
What is the difference between the Oracle Data Dictionary
and MySQL's <code class="literal">INFORMATION_SCHEMA</code>?
</p></li><li><p><a href="faqs.html#qandaitem-28-7-5">28.7.5: </a>
Can I add to or otherwise modify the tables found in the
<code class="literal">INFORMATION_SCHEMA</code> database?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-7-1"></a><span class="bold"><strong>28.7.1: </strong></span><span class="bold"><strong>
Where can I find documentation for the MySQL
<code class="literal">INFORMATION_SCHEMA</code> database?
</strong></span></p><p>
See <a href="information-schema.html" title="Chapter 21. INFORMATION_SCHEMA Tables">Chapter 21, <i><code class="literal">INFORMATION_SCHEMA</code> Tables</i></a>
</p><p><a name="qandaitem-28-7-2"></a><span class="bold"><strong>28.7.2: </strong></span><span class="bold"><strong>
Is there a discussion forum for
<code class="literal">INFORMATION_SCHEMA</code>?
</strong></span></p><p>
See
<a href="http://forums.mysql.com/list.php?101" target="_top">http://forums.mysql.com/list.php?101</a>.
</p><p><a name="qandaitem-28-7-3"></a><span class="bold"><strong>28.7.3: </strong></span><span class="bold"><strong>
Where can I find the ANSI SQL 2003 specification for
<code class="literal">INFORMATION_SCHEMA</code>?
</strong></span></p><p>
Unfortunately, the official specifications are not freely
available. (ANSI makes them available for purchase.)
However, there are books available — such as
<em class="citetitle">SQL-99 Complete, Really</em> by Peter
Gulutzan and Trudy Pelzer — which give a comprehensive
overview of the standard, including
<code class="literal">INFORMATION_SCHEMA</code>.
</p><p><a name="qandaitem-28-7-4"></a><span class="bold"><strong>28.7.4: </strong></span><span class="bold"><strong>
What is the difference between the Oracle Data Dictionary
and MySQL's <code class="literal">INFORMATION_SCHEMA</code>?
</strong></span></p><p>
Both Oracle and MySQL provide metadata in tables. However,
Oracle and MySQL use different table names and column names.
MySQL's implementation is more similar to those found in DB2
and SQL Server, which also support
<code class="literal">INFORMATION_SCHEMA</code> as defined in the SQL
standard.
</p><p><a name="qandaitem-28-7-5"></a><span class="bold"><strong>28.7.5: </strong></span><span class="bold"><strong>
Can I add to or otherwise modify the tables found in the
<code class="literal">INFORMATION_SCHEMA</code> database?
</strong></span></p><p>
No. Since applications may rely on a certain standard
structure, this should not be modified. For this reason,
<span class="emphasis"><em>MySQL AB cannot support bugs or other issues which
result from modifying <code class="literal">INFORMATION_SCHEMA</code>
tables or data</em></span>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-migration"></a>A.8. MySQL 5.0 FAQ — Migration</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-8-1">28.8.1: </a>
Where can I find information on how to migrate from MySQL
4.1 to MySQL 5.0?
</p></li><li><p><a href="faqs.html#qandaitem-28-8-2">28.8.2: </a>
How has storage engine (table type) support changed in MySQL
5.0 from previous versions?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-8-1"></a><span class="bold"><strong>28.8.1: </strong></span><span class="bold"><strong>
Where can I find information on how to migrate from MySQL
4.1 to MySQL 5.0?
</strong></span></p><p>
For detailed upgrade information, see
<a href="installing.html#upgrade" title="2.4.17. Upgrading MySQL">Section 2.4.17, “Upgrading MySQL”</a>. We recommend that you do not skip
a major version when upgrading, but rather complete the
process in steps, upgrading from one major version to the
next in each step. This may seem more complicated, but it
will you save time and trouble — if you encounter
problems during the upgrade, their origin will be easier to
identify, either by you or — if you have a MySQL
Enterprise subscription — by MySQL support.
</p><p><a name="qandaitem-28-8-2"></a><span class="bold"><strong>28.8.2: </strong></span><span class="bold"><strong>
How has storage engine (table type) support changed in MySQL
5.0 from previous versions?
</strong></span></p><p>
Storage engine support has changed as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Support for <code class="literal">ISAM</code> tables was removed
in MySQL 5.0 and you should now use the
<code class="literal">MyISAM</code> storage engine in place of
<code class="literal">ISAM</code>. To convert a table
<em class="replaceable"><code>tblname</code></em> from
<code class="literal">ISAM</code> to <code class="literal">MyISAM</code>,
simply issue a statement such as this one:
</p><pre class="programlisting">ALTER TABLE <em class="replaceable"><code>tblname</code></em> ENGINE=MYISAM;</pre></li><li><p>
Internal <code class="literal">RAID</code> for
<code class="literal">MyISAM</code> tables was also removed in
MySQL 5.0. This was formerly used to allow large
tables in file systems that did not support file sizes
greater than 2GB. All modern file systems allow for
larger tables; in addition, there are now other
solutions such as <code class="literal">MERGE</code> tables and
views.
</p></li><li><p>
The <code class="literal">VARCHAR</code> column type now retains
trailing spaces in all storage engines.
</p></li><li><p>
<code class="literal">MEMORY</code> tables (formerly known as
<code class="literal">HEAP</code> tables) can also contain
<code class="literal">VARCHAR</code> columns.
</p></li></ul></div><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-security"></a>A.9. MySQL 5.0 FAQ — Security</h2></div></div></div><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-9-1">28.9.1: </a>
Where can I find documentation that addresses security
issues for MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-9-2">28.9.2: </a>
Does MySQL 5.0 have native support for SSL?
</p></li><li><p><a href="faqs.html#qandaitem-28-9-3">28.9.3: </a>
Is SSL support be built into MySQL binaries, or must I
recompile the binary myself to enable it?
</p></li><li><p><a href="faqs.html#qandaitem-28-9-4">28.9.4: </a>
Does MySQL 5.0 have built-in authentication
against LDAP directories?
</p></li><li><p><a href="faqs.html#qandaitem-28-9-5">28.9.5: </a>
Does MySQL 5.0 include support for Roles Based
Access Control (RBAC)?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-9-1"></a><span class="bold"><strong>28.9.1: </strong></span><span class="bold"><strong>
Where can I find documentation that addresses security
issues for MySQL?
</strong></span></p><p>
The best place to start is <a href="server-administration.html#security" title="5.6. General Security Issues">Section 5.6, “General Security Issues”</a>.
</p><p>
Other portions of the MySQL Documentation which you may find
useful with regard to specific security concerns include the
following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="server-administration.html#security-guidelines" title="5.6.1. General Security Guidelines">Section 5.6.1, “General Security Guidelines”</a>.
</p></li><li><p>
<a href="server-administration.html#security-against-attack" title="5.6.2. Making MySQL Secure Against Attackers">Section 5.6.2, “Making MySQL Secure Against Attackers”</a>.
</p></li><li><p>
<a href="error-handling.html#resetting-permissions" title="B.1.4.1. How to Reset the Root Password">Section B.1.4.1, “How to Reset the Root Password”</a>.
</p></li><li><p>
<a href="server-administration.html#changing-mysql-user" title="5.6.5. How to Run MySQL as a Normal User">Section 5.6.5, “How to Run MySQL as a Normal User”</a>.
</p></li><li><p>
<a href="extending-mysql.html#udf-security" title="26.2.4.6. User-Defined Function Security Precautions">Section 26.2.4.6, “User-Defined Function Security Precautions”</a>.
</p></li><li><p>
<a href="server-administration.html#privileges-options" title="5.6.3. Security-Related mysqld Options">Section 5.6.3, “Security-Related <span><strong class="command">mysqld</strong></span> Options”</a>.
</p></li><li><p>
<a href="server-administration.html#load-data-local" title="5.6.4. Security Issues with LOAD DATA LOCAL">Section 5.6.4, “Security Issues with <code class="literal">LOAD DATA LOCAL</code>”</a>.
</p></li><li><p>
<a href="installing.html#post-installation" title="2.4.16. Post-Installation Setup and Testing">Section 2.4.16, “Post-Installation Setup and Testing”</a>.
</p></li><li><p>
<a href="installing.html#selinux" title="2.4.19.1.11. SELinux Notes">Section 2.4.19.1.11, “SELinux Notes”</a>.
</p></li><li><p>
<a href="server-administration.html#secure-basics" title="5.8.7.1. Basic SSL Concepts">Section 5.8.7.1, “Basic SSL Concepts”</a>.
</p></li></ul></div><p>
</p><p class="mnmas"><b>MySQL Enterprise</b>
The MySQL Enterprise Monitor enforces best practices for
maximizing the security of your servers. For more
information see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p><a name="qandaitem-28-9-2"></a><span class="bold"><strong>28.9.2: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have native support for SSL?
</strong></span></p><p>
Most 5.0 binaries have support for SSL
connections between the client and server. We can't
currently build with the new YaSSL library everywhere, as
it's still quite new and does not compile on all platforms
yet. See <a href="server-administration.html#secure-connections" title="5.8.7. Using Secure Connections">Section 5.8.7, “Using Secure Connections”</a>.
</p><p>
You can also tunnel a connection via SSH, if (for instance)
if the client application doesn't support SSL connections.
For an example, see <a href="server-administration.html#windows-and-ssh" title="5.8.7.5. Connecting to MySQL Remotely from Windows with SSH">Section 5.8.7.5, “Connecting to MySQL Remotely from Windows with SSH”</a>.
</p><p><a name="qandaitem-28-9-3"></a><span class="bold"><strong>28.9.3: </strong></span><span class="bold"><strong>
Is SSL support be built into MySQL binaries, or must I
recompile the binary myself to enable it?
</strong></span></p><p>
Most 5.0 binaries have SSL enabled for
client-server connections that are secured, authenticated,
or both. However, the YaSSL library currently does not
compile on all platforms. See
<a href="server-administration.html#secure-connections" title="5.8.7. Using Secure Connections">Section 5.8.7, “Using Secure Connections”</a>, for a complete listing
of supported and unsupported platforms.
</p><p><a name="qandaitem-28-9-4"></a><span class="bold"><strong>28.9.4: </strong></span><span class="bold"><strong>
Does MySQL 5.0 have built-in authentication
against LDAP directories?
</strong></span></p><p>
No. Support for external authentication methods is on the
MySQL roadmap as a “<span class="quote">rolling feature</span>”, which
means that we plan to implement it in the future, but we
have not yet determined when this will be done.
</p><p><a name="qandaitem-28-9-5"></a><span class="bold"><strong>28.9.5: </strong></span><span class="bold"><strong>
Does MySQL 5.0 include support for Roles Based
Access Control (RBAC)?
</strong></span></p><p>
No. Support for roles is on the MySQL roadmap as a
“<span class="quote">rolling feature</span>”, which means that we plan to
implement it in the future, but we have not yet determined
when this will be done.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-mysql-cluster"></a>A.10. MySQL 5.0 FAQ — MySQL Cluster</h2></div></div></div><a class="indexterm" name="id3165434"></a><a class="indexterm" name="id3165446"></a><a class="indexterm" name="id3165458"></a><p>
In the following section, we provide answers to questions that are
most frequently asked about MySQL Cluster and the
<code class="literal">NDB</code> storage engine.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-10-1">28.10.1: </a><a class="indexterm" name="id3165510"></a>
What does “<span class="quote">NDB</span>” mean?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-2">28.10.2: </a><a class="indexterm" name="id3165530"></a>
What's the difference in using Cluster
<span class="foreignphrase"><em class="foreignphrase">vs</em></span> using replication?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-3">28.10.3: </a><a class="indexterm" name="id3165556"></a>
Do I need to do any special networking to run Cluster? How
do computers in a cluster communicate?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-4">28.10.4: </a><a class="indexterm" name="id3165578"></a>
How many computers do I need to run a cluster, and why?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-5">28.10.5: </a><a class="indexterm" name="id3165599"></a>
What do the different computers do in a MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-6">28.10.6: </a><a class="indexterm" name="id3165620"></a>
With which operating systems can I use Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-7">28.10.7: </a><a class="indexterm" name="id3165647"></a>
What are the hardware requirements for running MySQL
Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-8">28.10.8: </a><a class="indexterm" name="id3165669"></a>
How much RAM do I need? Is it possible to use disk memory at
all?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-9">28.10.9: </a>
What filesystems can I use with MySQL Cluster? What about
network filesystems or network shares?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-10">28.10.10: </a><a class="indexterm" name="id3165701"></a>
Can I run MySQL Cluster nodes inside virtual machines (such
as those created by VMWare, Parallels, or Xen)?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-11">28.10.11: </a><a class="indexterm" name="id3165723"></a><a class="indexterm" name="id3165735"></a>
I'm trying to populate a Cluster database. The loading
process terminates prematurely and I get an error message
like this one:
</p><pre class="programlisting"><span class="errortext">ERROR 1114: The table 'my_cluster_table' is full</span>
</pre><p>
Why is this happening?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-12">28.10.12: </a><a class="indexterm" name="id3165768"></a>
MySQL Cluster uses TCP/IP. Does this mean that I can run it
over the Internet, with one or more nodes in remote
locations?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-13">28.10.13: </a><a class="indexterm" name="id3165791"></a>
Do I have to learn a new programming or query language to
use MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-14">28.10.14: </a><a class="indexterm" name="id3165813"></a>
How do I find out what an error or warning message means
when using MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-15">28.10.15: </a><a class="indexterm" name="id3165834"></a>
Is MySQL Cluster transaction-safe? What isolation levels are
supported?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-16">28.10.16: </a>
What storage engines are supported by MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-17">28.10.17: </a><a class="indexterm" name="id3165865"></a>
Which versions of the MySQL software support Cluster? Do I
have to compile from source?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-18">28.10.18: </a>
In the event of a catastrophic failure — say, for
instance, the whole city loses power
<span class="emphasis"><em>and</em></span> my UPS fails — would I lose
all my data?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-19">28.10.19: </a>
Is it possible to use <code class="literal">FULLTEXT</code> indexes
with Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-20">28.10.20: </a>
Can I run multiple nodes on a single computer?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-21">28.10.21: </a>
Can I add nodes to a cluster without restarting it?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-22">28.10.22: </a>
Are there any limitations that I should be aware of when
using MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-23">28.10.23: </a><a class="indexterm" name="id3165949"></a>
How do I import an existing MySQL database into a cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-24">28.10.24: </a>
How do cluster nodes communicate with one another?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-25">28.10.25: </a><a class="indexterm" name="id3165985"></a><a class="indexterm" name="id3165997"></a>
What is an <em class="firstterm">arbitrator</em>?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-26">28.10.26: </a><a class="indexterm" name="id3166017"></a>
What data types are supported by MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-27">28.10.27: </a><a class="indexterm" name="id3166038"></a>
How do I start and stop MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-28">28.10.28: </a>
What happens to cluster data when the cluster is shut down?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-29">28.10.29: </a>
Is it helpful to have more than one management node for a
cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-30">28.10.30: </a>
Can I mix different kinds of hardware and operating systems
in one MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-31">28.10.31: </a>
Can I run two data nodes on a single host? Two SQL nodes?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-32">28.10.32: </a>
Can I use hostnames with MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-28-10-33">28.10.33: </a>
How do I handle MySQL users in a Cluster having multiple
MySQL servers?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-10-1"></a><span class="bold"><strong>28.10.1: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166128"></a></strong></span><span class="bold"><strong>
What does “<span class="quote">NDB</span>” mean?
</strong></span></p><p>
This stands for
“<span class="quote"><span class="bold"><strong>N</strong></span>etwork
<span class="bold"><strong>D</strong></span>ata<span class="bold"><strong>b</strong></span>ase</span>”.
<code class="literal">NDB</code> (also known as <code class="literal">NDB
Cluster</code> or <code class="literal">NDBCLUSTER</code>) is the
storage engine that enables clustering in MySQL.
</p><p><a name="qandaitem-28-10-2"></a><span class="bold"><strong>28.10.2: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166199"></a></strong></span><span class="bold"><strong>
What's the difference in using Cluster
<span class="foreignphrase"><em class="foreignphrase">vs</em></span> using replication?
</strong></span></p><p>
In a replication setup, a master MySQL server updates one or
more slaves. Transactions are committed sequentially, and a
slow transaction can cause the slave to lag behind the
master. This means that if the master fails, it is possible
that the slave might not have recorded the last few
transactions. If a transaction-safe engine such as
<code class="literal">InnoDB</code> is being used, a transaction will
either be complete on the slave or not applied at all, but
replication does not guarantee that all data on the master
and the slave will be consistent at all times. In MySQL
Cluster, all data nodes are kept in synchrony, and a
transaction committed by any one data node is committed for
all data nodes. In the event of a data node failure, all
remaining data nodes remain in a consistent state.
</p><p>
In short, whereas standard MySQL replication is
asynchronous, MySQL Cluster is synchronous.
</p><p>
We have implemented (asynchronous) replication for Cluster
in MySQL 5.1. This includes the capability to replicate both
between two clusters, and from a MySQL cluster to a
non-Cluster MySQL server. However, we do not plan to
backport this functionality to MySQL 5.0.
</p><p><a name="qandaitem-28-10-3"></a><span class="bold"><strong>28.10.3: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166274"></a></strong></span><span class="bold"><strong>
Do I need to do any special networking to run Cluster? How
do computers in a cluster communicate?
</strong></span></p><p>
MySQL Cluster is intended to be used in a high-bandwidth
environment, with computers connecting via TCP/IP. Its
performance depends directly upon the connection speed
between the cluster's computers. The minimum connectivity
requirements for Cluster include a typical 100-megabit
Ethernet network or the equivalent. We recommend you use
gigabit Ethernet whenever available.
</p><p>
The faster SCI protocol is also supported, but requires
special hardware. See
<a href="mysql-cluster.html#mysql-cluster-interconnects" title="16.9. Using High-Speed Interconnects with MySQL Cluster">Section 16.9, “Using High-Speed Interconnects with MySQL Cluster”</a>, for more
information about SCI.
</p><p><a name="qandaitem-28-10-4"></a><span class="bold"><strong>28.10.4: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166327"></a></strong></span><span class="bold"><strong>
How many computers do I need to run a cluster, and why?
</strong></span></p><p>
A minimum of three computers is required to run a viable
cluster. However, the minimum
<span class="bold"><strong>recommended</strong></span> number of
computers in a MySQL Cluster is four: one each to run the
management and SQL nodes, and two computers to serve as data
nodes. The purpose of the two data nodes is to provide
redundancy; the management node must run on a separate
machine to guarantee continued arbitration services in the
event that one of the data nodes fails.
</p><p>
To provide increased throughput and high availability, you
should use multiple SQL nodes (MySQL Servers connected to
the cluster). It is also possible (although not strictly
necessary) to run multiple management servers.
</p><p><a name="qandaitem-28-10-5"></a><span class="bold"><strong>28.10.5: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166384"></a></strong></span><span class="bold"><strong>
What do the different computers do in a MySQL Cluster?
</strong></span></p><p>
A MySQL Cluster has both a physical and logical
organization, with computers being the physical elements.
The logical or functional elements of a cluster are referred
to as <em class="firstterm">nodes</em>, and a computer housing a
cluster node is sometimes referred to as a
<em class="firstterm">cluster host</em>. There are three types
of nodes, each corresponding to a specific role within the
cluster. These are:
</p><a class="indexterm" name="id3166420"></a><div class="itemizedlist"><ul type="disc"><li><p>
<span class="bold"><strong>Management node (MGM
node)</strong></span>: Provides management services for the
cluster as a whole, including startup, shutdown,
backups, and configuration data for the other nodes. The
management node server is implemented as the application
<span><strong class="command">ndb_mgmd</strong></span>; the management client used
to control MySQL Cluster via the MGM node is
<span><strong class="command">ndb_mgm</strong></span>.
</p></li><li><p>
<span class="bold"><strong>Data node</strong></span>: Stores and
replicates data. Data node functionality is handled by
an instance of the NDB data node process
<span><strong class="command">ndbd</strong></span>.
</p></li><li><p>
<span class="bold"><strong>SQL node</strong></span>: This is
simply an instance of MySQL Server
(<span><strong class="command">mysqld</strong></span>) that is built with support
for the <code class="literal">NDB Cluster</code> storage engine
and started with the <span><strong class="command">--ndb-cluster</strong></span>
option to enable the engine.
</p></li></ul></div><p><a name="qandaitem-28-10-6"></a><span class="bold"><strong>28.10.6: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166534"></a></strong></span><span class="bold"><strong></strong></span><span class="bold"><strong>
With which operating systems can I use Cluster?
</strong></span></p><p>
MySQL Cluster is supported on most Unix-like operating
systems, including Linux, Mac OS X, Solaris, BSD, HP-UX,
AIX, and IRIX, among others, as well as Novell Netware.
Cluster is not supported for Windows at this time. However,
we are working to add Cluster support for other platforms,
including Windows, and our goal is to offer MySQL Cluster on
all platforms for which MySQL itself is supported.
</p><p>
For more detailed information concerning the level of
support which is offered for MySQL Cluster on various
operating system versions, OS distributions, and hardware
platforms, please refer to
<a href="http://www.mysql.com/support/supportedplatforms/cluster.html" target="_top">http://www.mysql.com/support/supportedplatforms/cluster.html</a>.
</p><p><a name="qandaitem-28-10-7"></a><span class="bold"><strong>28.10.7: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166598"></a></strong></span><span class="bold"><strong>
What are the hardware requirements for running MySQL
Cluster?
</strong></span></p><p>
Cluster should run on any platform for which NDB-enabled
binaries are available. Naturally, faster CPUs and more
memory will improve performance, and 64-bit CPUs will likely
be more effective than 32-bit processors. There must be
sufficient memory on machines used for data nodes to hold
each node's share of the database (see <span class="emphasis"><em>How much
RAM do I Need?</em></span> for more information). Nodes can
communicate via a standard TCP/IP network and hardware. For
SCI support, special networking hardware is required.
</p><p><a name="qandaitem-28-10-8"></a><span class="bold"><strong>28.10.8: </strong></span><span class="bold"><strong><a class="indexterm" name="id3166652"></a></strong></span><span class="bold"><strong>
How much RAM do I need? Is it possible to use disk memory at
all?
</strong></span></p><p>
In MySQL-5.0, Cluster is in-memory only. This
means that all table data (including indexes) is stored in
RAM. Therefore, if your data takes up 1GB of space and you
want to replicate it once in the cluster, you need 2GB of
memory to do so (1 GB per replica). This is in addition to
the memory required by the operating system and any
applications running on the cluster computers.
</p><p>
If a data node's memory usage exceeds what is available in
RAM, then the system will attempt to use swap space up to
the limit set for <code class="literal">DataMemory</code>. However,
this will at best result in severely degraded performance,
and may cause the node to be dropped due to slow response
time (missed heartbeats). We do not recommend on relying on
disk swapping in a production environment for this reason.
In any case, once the <code class="literal">DataMemory</code> limit is
reached, any operations requiring additional memory (such as
inserts) will fail.
</p><p>
(We have implemented disk data storage for MySQL Cluster in
MySQL 5.1, but we have no plans to add this capability in
MySQL 5.0.)
</p><p>
You can use the following formula for obtaining a rough
estimate of how much RAM is needed for each data node in the
cluster:
</p><pre class="programlisting">(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes
</pre><p>
To calculate the memory requirements more exactly requires
determining, for each table in the cluster database, the
storage space required per row (see
<a href="data-types.html#storage-requirements" title="10.5. Data Type Storage Requirements">Section 10.5, “Data Type Storage Requirements”</a>, for details), and
multiplying this by the number of rows. You must also
remember to account for any column indexes as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Each primary key or hash index created for an
<code class="literal">NDBCluster</code> table requires 21–25
bytes per record. These indexes use
<code class="literal">IndexMemory</code>.
</p></li><li><p>
Each ordered index requires 10 bytes storage per record,
using <code class="literal">DataMemory</code>.
</p></li><li><p>
Creating a primary key or unique index also creates an
ordered index, unless this index is created with
<code class="literal">USING HASH</code>. In other words:
</p><div class="itemizedlist"><ul type="circle"><li><p>
A primary key or unique index on a Cluster table
normally takes up 31 to 35 bytes per record.
</p></li><li><p>
However, if the primary key or unique index is
created with <code class="literal">USING HASH</code>, then it
requires only 21 to 25 bytes per record.
</p></li></ul></div></li></ul></div><p>
Note that creating MySQL Cluster tables with <code class="literal">USING
HASH</code> for all primary keys and unique indexes will
generally cause table updates to run more quickly — in
some cases by a much as 20 to 30 percent faster than updates
on tables where <code class="literal">USING HASH</code> was not used
in creating primary and unique keys. This is due to the fact
that less memory is required (because no ordered indexes are
created), and that less CPU must be utilized (because fewer
indexes must be read and possibly updated). However, it also
means that queries that could otherwise use range scans must
be satisfied by other means, which can result in slower
selects.
</p><a class="indexterm" name="id3166858"></a><a class="indexterm" name="id3166871"></a><p>
When calculating Cluster memory requirements, you may find
useful the <code class="filename">ndb_size.pl</code> utility which is
available in recent MySQL 5.0 releases. This
Perl script connects to a current (non-Cluster) MySQL
database and creates a report on how much space that
database would require if it used the
<code class="literal">NDBCluster</code> storage engine. For more
information, see
<a href="mysql-cluster.html#mysql-cluster-utilities-ndb-size" title="16.8.14. ndb_size.pl — NDBCluster Size Requirement Estimator">Section 16.8.14, “<span><strong class="command">ndb_size.pl</strong></span> — NDBCluster Size Requirement Estimator”</a>.
</p><p>
It is especially important to keep in mind that
<span class="emphasis"><em>every MySQL Cluster table must have a primary
key</em></span>. The <code class="literal">NDB</code> storage engine
creates a primary key automatically if none is defined, and
this primary key is created without <code class="literal">USING
HASH</code>.
</p><p>
There is no easy way to determine exactly how much memory is
being used for storage of Cluster indexes at any given time;
however, warnings are written to the Cluster log when 80% of
available <code class="literal">DataMemory</code> or
<code class="literal">IndexMemory</code> is in use, and again when use
reaches 85%, 90%, and so on.
</p><p><a name="qandaitem-28-10-9"></a><span class="bold"><strong>28.10.9: </strong></span><span class="bold"><strong>
What filesystems can I use with MySQL Cluster? What about
network filesystems or network shares?
</strong></span></p><p>
Generally, any filesystem that is native to the host
operating system should work well with MySQL Cluster. If you
find that a given filesystem works particularly well (or not
so especially well) with MySQL Cluster, we invite you to
discuss your findings in the
<a href="http://forums.mysql.com/list.php?25" target="_top">MySQL Cluster
Forums</a>.
</p><p>
We do not test MySQL Cluster with <code class="literal">FAT</code> or
<code class="literal">VFAT</code> filesystems on Linux. Because of
this, and due to the fact that these are not very useful for
any purpose other than sharing disk partitions between Linux
and Windows operating systems on multi-boot computers, we do
not recommend their use with MySQL Cluster.
</p><p>
MySQL Cluster is implemented as a shared-nothing solution;
the idea behind this is that the failure of a single piece
of hardware should not cause the failure of multiple cluster
nodes, or possibly even the failure of the cluster as a
whole. For this reason, the use of network shares or network
filesystems is not supported for MySQL Cluster. This also
applies to shared storage devices such as SANs.
</p><p><a name="qandaitem-28-10-10"></a><span class="bold"><strong>28.10.10: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167028"></a></strong></span><span class="bold"><strong>
Can I run MySQL Cluster nodes inside virtual machines (such
as those created by VMWare, Parallels, or Xen)?
</strong></span></p><p>
This is possible but not recommended for a production
environment.
</p><p>
We have found that running MySQL Cluster processes inside a
virtual machine can give rise to issues with timing and disk
subsystems that have a strong negative impact on the
operation of the cluster. The behavior of the cluster is
often unpredictable in these cases.
</p><p>
If the issue can be reproduced outside the virtual
environment, then we may be able to provide assistance.
Otherwise, we cannot support it at this time.
</p><p><a name="qandaitem-28-10-11"></a><span class="bold"><strong>28.10.11: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167080"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3167096"></a></strong></span><span class="bold"><strong>
I'm trying to populate a Cluster database. The loading
process terminates prematurely and I get an error message
like this one:
<pre class="programlisting"><span class="errortext">ERROR 1114: The table 'my_cluster_table' is full</span>
</pre>
Why is this happening?
</strong></span></p><p>
The cause is very likely to be that your setup does not
provide sufficient RAM for all table data and all indexes,
<span class="emphasis"><em>including the primary key required by the
<code class="literal">NDB</code> storage engine and automatically
created in the event that the table definition does not
include the definition of a primary key</em></span>.
</p><p>
It is also worth noting that all data nodes should have the
same amount of RAM, since no data node in a cluster can use
more memory than the least amount available to any
individual data node. In other words, if there are four
computers hosting Cluster data nodes, and three of these
have 3GB of RAM available to store Cluster data while the
remaining data node has only 1GB RAM, then each data node
can devote only 1GB to clustering.
</p><p><a name="qandaitem-28-10-12"></a><span class="bold"><strong>28.10.12: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167169"></a></strong></span><span class="bold"><strong>
MySQL Cluster uses TCP/IP. Does this mean that I can run it
over the Internet, with one or more nodes in remote
locations?
</strong></span></p><p>
It is <span class="emphasis"><em>very</em></span> unlikely that a cluster
would perform reliably under such conditions, as MySQL
Cluster was designed and implemented with the assumption
that it would be run under conditions guaranteeing dedicated
high-speed connectivity such as that found in a LAN setting
using 100 Mbps or gigabit Ethernet — preferably the
latter. We neither test nor warrant its performance using
anything slower than this.
</p><a class="indexterm" name="id3167212"></a><p>
Also, it is extremely important to keep in mind that
communications between the nodes in a MySQL Cluster are not
secure; they are neither encrypted nor safeguarded by any
other protective mechanism. The most secure configuration
for a cluster is in a private network behind a firewall,
with no direct access to any Cluster data or management
nodes from outside. (For SQL nodes, you should take the same
precautions as you would with any other instance of the
MySQL server.)
</p><p><a name="qandaitem-28-10-13"></a><span class="bold"><strong>28.10.13: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167250"></a></strong></span><span class="bold"><strong>
Do I have to learn a new programming or query language to
use MySQL Cluster?
</strong></span></p><p>
No. Although some specialized commands are used to manage
and configure the cluster itself, only standard (My)SQL
queries and commands are required for the following
operations:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Creating, altering, and dropping tables
</p></li><li><p>
Inserting, updating, and deleting table data
</p></li><li><p>
Creating, changing, and dropping primary and unique
indexes
</p></li></ul></div><p>
Some specialized configuration parameters and files are
required to set up a MySQL Cluster — see
<a href="mysql-cluster.html#mysql-cluster-config-file" title="16.3.4. Configuration File">Section 16.3.4, “Configuration File”</a>, for information
about these.
</p><p>
A few simple commands are used in the MySQL Cluster
management client for tasks such as starting and stopping
cluster nodes. See
<a href="mysql-cluster.html#mysql-cluster-mgm-client-commands" title="16.6.2. Commands in the MySQL Cluster Management Client">Section 16.6.2, “Commands in the MySQL Cluster Management Client”</a>.
</p><p><a name="qandaitem-28-10-14"></a><span class="bold"><strong>28.10.14: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167347"></a></strong></span><span class="bold"><strong>
How do I find out what an error or warning message means
when using MySQL Cluster?
</strong></span></p><p>
There are two ways in which this can be done:
</p><div class="itemizedlist"><ul type="disc"><li><a class="indexterm" name="id3167375"></a><a class="indexterm" name="id3167387"></a><p>
From within the <span><strong class="command">mysql</strong></span> client, use
<span><strong class="command">SHOW ERRORS</strong></span> or <span><strong class="command">SHOW
WARNINGS</strong></span> immediately upon being notified of
the error or warning condition. Errors and warnings also
be displayed in MySQL Query Browser.
</p></li><li><a class="indexterm" name="id3167428"></a><p>
From a system shell prompt, use <span><strong class="command">perror --ndb
<em class="replaceable"><code>error_code</code></em></strong></span>.
</p></li></ul></div><p><a name="qandaitem-28-10-15"></a><span class="bold"><strong>28.10.15: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167468"></a></strong></span><span class="bold"><strong>
Is MySQL Cluster transaction-safe? What isolation levels are
supported?
</strong></span></p><p>
<span class="emphasis"><em>Yes</em></span>: For tables created with the
<code class="literal">NDB</code> storage engine, transactions are
supported. In MySQL 5.0, Cluster supports only
the <code class="literal">READ COMMITTED</code> transaction isolation
level.
</p><p><a name="qandaitem-28-10-16"></a><span class="bold"><strong>28.10.16: </strong></span><span class="bold"><strong>
What storage engines are supported by MySQL Cluster?
</strong></span></p><p>
Clustering in MySQL is supported only by the
<code class="literal">NDB</code> storage engine. That is, in order for
a table to be shared between nodes in a cluster, it must be
created using <code class="literal">ENGINE=NDB</code> (or
<code class="literal">ENGINE=NDBCLUSTER</code>, which is equivalent).
</p><p>
It is possible to create tables using other storage engines
(such as <code class="literal">MyISAM</code> or
<code class="literal">InnoDB</code>) on a MySQL server being used for
clustering, but these non-<code class="literal">NDB</code> tables will
<span class="bold"><strong>not</strong></span> participate in the
cluster; they are local to the individual MySQL server
instance on which they are created.
</p><p><a name="qandaitem-28-10-17"></a><span class="bold"><strong>28.10.17: </strong></span><span class="bold"><strong><a class="indexterm" name="id3167592"></a></strong></span><span class="bold"><strong>
Which versions of the MySQL software support Cluster? Do I
have to compile from source?
</strong></span></p><p>
Cluster is supported in all server binaries in the
5.0 release series for operating systems on
which MySQL Cluster is available. See
<a href="server-administration.html#mysqld" title="5.2. mysqld — The MySQL Server">Section 5.2, “<span><strong class="command">mysqld</strong></span> — The MySQL Server”</a>. You can determine whether your
server has NDB support using either the <code class="literal">SHOW
VARIABLES LIKE 'have_%'</code> or <code class="literal">SHOW
ENGINES</code> statement.
</p><p>
Linux users, please note that <code class="literal">NDB</code> is
<span class="emphasis"><em>not</em></span> included in the standard MySQL
server RPMs. Beginning with MySQL 5.0.4, there are separate
RPM packages for the NDB storage engine and accompanying
management and other tools; see the NDB RPM Downloads
section of the MySQL 5.0 Downloads page for
these. (Prior to 5.0.4, you had to use the
<code class="literal">-max</code> binaries supplied as
<code class="filename">.tar.gz</code> archives. This is still
possible, but is not required, so you can use your Linux
distribution's RPM manager if you prefer.)
</p><p>
You can also obtain NDB support by compiling MySQL from
source, but it is not necessary to do so simply to use MySQL
Cluster. To download the latest binary, RPM, or source
distribution in the MySQL 5.0 series, visit
<a href="http://dev.mysql.com/downloads/mysql/5.0.html" target="_top">http://dev.mysql.com/downloads/mysql/5.0.html</a>.
</p><p><a name="qandaitem-28-10-18"></a><span class="bold"><strong>28.10.18: </strong></span><span class="bold"><strong>
In the event of a catastrophic failure — say, for
instance, the whole city loses power
<span class="emphasis"><em>and</em></span> my UPS fails — would I lose
all my data?
</strong></span></p><p>
All committed transactions are logged. Therefore, although
it is possible that some data could be lost in the event of
a catastrophe, this should be quite limited. Data loss can
be further reduced by minimizing the number of operations
per transaction. (It is not a good idea to perform large
numbers of operations per transaction in any case.)
</p><p><a name="qandaitem-28-10-19"></a><span class="bold"><strong>28.10.19: </strong></span><span class="bold"><strong>
Is it possible to use <code class="literal">FULLTEXT</code> indexes
with Cluster?
</strong></span></p><p>
<code class="literal">FULLTEXT</code> indexing is not supported by the
<code class="literal">NDB</code> storage engine in MySQL
5.0, or by any storage engine other than
<code class="literal">MyISAM</code>. We are working to add this
capability in a future release.
</p><p><a name="qandaitem-28-10-20"></a><span class="bold"><strong>28.10.20: </strong></span><span class="bold"><strong>
Can I run multiple nodes on a single computer?
</strong></span></p><p>
It is possible but not advisable. One of the chief reasons
to run a cluster is to provide redundancy. To enjoy the full
benefits of this redundancy, each node should reside on a
separate machine. If you place multiple nodes on a single
machine and that machine fails, you lose all of those nodes.
Given that MySQL Cluster can be run on commodity hardware
loaded with a low-cost (or even no-cost) operating system,
the expense of an extra machine or two is well worth it to
safeguard mission-critical data. It also worth noting that
the requirements for a cluster host running a management
node are minimal. This task can be accomplished with a 200
MHz Pentium CPU and sufficient RAM for the operating system
plus a small amount of overhead for the
<span><strong class="command">ndb_mgmd</strong></span> and <span><strong class="command">ndb_mgm</strong></span>
processes.
</p><p>
It is acceptable to run multiple cluster data nodes on a
single host for learning about MySQL Cluster, or for testing
purposes; however, this is <span class="emphasis"><em>not</em></span>
supported for production use.
</p><p><a name="qandaitem-28-10-21"></a><span class="bold"><strong>28.10.21: </strong></span><span class="bold"><strong>
Can I add nodes to a cluster without restarting it?
</strong></span></p><p>
Not at present. A simple restart is all that is required for
adding new MGM or SQL nodes to a Cluster. When adding data
nodes the process is more complex, and requires the
following steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Make a complete backup of all Cluster data.
</p></li><li><p>
Completely shut down the cluster and all cluster node
processes.
</p></li><li><p>
Restart the cluster, using the
<code class="option">--initial</code> startup option.
</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
Never use the <code class="option">--initial</code> when starting
<span><strong class="command">ndbd</strong></span> except when necessary to clear
the data node filesystem. See
<a href="mysql-cluster.html#mysql-cluster-ndbd-command-options" title="16.5.5.2. Command Options for ndbd">Section 16.5.5.2, “Command Options for <span><strong class="command">ndbd</strong></span>”</a>,
for information about when this is required.
</p></div></li><li><p>
Restore all cluster data from the backup.
</p></li></ol></div><p>
In a future MySQL Cluster release series, we hope to
implement a “<span class="quote">hot</span>” reconfiguration capability
for MySQL Cluster to minimize (if not eliminate) the
requirement for restarting the cluster when adding new
nodes. However, this is not planned for MySQL
5.0.
</p><p><a name="qandaitem-28-10-22"></a><span class="bold"><strong>28.10.22: </strong></span><span class="bold"><strong>
Are there any limitations that I should be aware of when
using MySQL Cluster?
</strong></span></p><p>
Limitations on <code class="literal">NDB</code> tables in MySQL
5.0 include:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Temporary tables are not supported; a <code class="literal">CREATE
TEMPORARY TABLE</code> statement using
<code class="literal">ENGINE=NDB</code> or
<code class="literal">ENGINE=NDBCLUSTER</code> fails with an
error.
</p></li><li><p>
<code class="literal">FULLTEXT</code> indexes and index prefixes
are not supported. Only complete columns may be indexed.
</p></li><li><p>
Spatial data types are not supported. See
<a href="spatial-extensions.html" title="Chapter 17. Spatial Extensions">Chapter 17, <i>Spatial Extensions</i></a>.
</p></li><li><p>
Only complete rollbacks for transactions are supported.
Partial rollbacks and rollbacks to save points are not
supported.
</p></li><li><p>
The maximum number of attributes allowed per table is
128, and attribute names cannot be any longer than 31
characters. For each table, the maximum combined length
of the table and database names is 122 characters.
</p></li><li><p>
The maximum size for a table row is 8 kilobytes, not
counting <code class="literal">BLOB</code> values. There is no set
limit for the number of rows per table. Table size
limits depend on a number of factors, in particular on
the amount of RAM available to each data node.
</p></li><li><p>
The <code class="literal">NDB</code> engine does not support
foreign key constraints. As with
<code class="literal">MyISAM</code> tables, these are ignored.
</p></li></ul></div><p>
For a complete listing of limitations in MySQL Cluster, see
<a href="mysql-cluster.html#mysql-cluster-limitations" title="16.10. Known Limitations of MySQL Cluster">Section 16.10, “Known Limitations of MySQL Cluster”</a>.
</p><p><a name="qandaitem-28-10-23"></a><span class="bold"><strong>28.10.23: </strong></span><span class="bold"><strong><a class="indexterm" name="id3168088"></a></strong></span><span class="bold"><strong>
How do I import an existing MySQL database into a cluster?
</strong></span></p><p>
You can import databases into MySQL Cluster much as you
would with any other version of MySQL. Other than the
limitations mentioned elsewhere in this FAQ and in
<a href="mysql-cluster.html#mysql-cluster-limitations" title="16.10. Known Limitations of MySQL Cluster">Section 16.10, “Known Limitations of MySQL Cluster”</a>, the only other
special requirement is that any tables to be included in the
cluster must use the <code class="literal">NDB</code> storage engine.
This means that the tables must be created with
<code class="literal">ENGINE=NDB</code> or
<code class="literal">ENGINE=NDBCLUSTER</code>.
</p><p>
It is also possible to convert existing tables using other
storage engines to <code class="literal">NDB Cluster</code> using one
or more <code class="literal">ALTER TABLE</code> statement, but this
requires an additional workaround. See
<a href="mysql-cluster.html#mysql-cluster-limitations" title="16.10. Known Limitations of MySQL Cluster">Section 16.10, “Known Limitations of MySQL Cluster”</a>, for details.
</p><p><a name="qandaitem-28-10-24"></a><span class="bold"><strong>28.10.24: </strong></span><span class="bold"><strong></strong></span><span class="bold"><strong>
How do cluster nodes communicate with one another?
</strong></span></p><p>
Cluster nodes can communicate via any of three different
protocols: TCP/IP, SHM (shared memory), and SCI (Scalable
Coherent Interface). Where available, SHM is used by default
between nodes residing on the same cluster host; however,
this is considered experimental in MySQL 5.0.
SCI is a high-speed (1 gigabit per second and higher),
high-availability protocol used in building scalable
multi-processor systems; it requires special hardware and
drivers. See <a href="mysql-cluster.html#mysql-cluster-interconnects" title="16.9. Using High-Speed Interconnects with MySQL Cluster">Section 16.9, “Using High-Speed Interconnects with MySQL Cluster”</a>,
for more about using SCI as a transport mechanism in MySQL
Cluster.
</p><p><a name="qandaitem-28-10-25"></a><span class="bold"><strong>28.10.25: </strong></span><span class="bold"><strong><a class="indexterm" name="id3168218"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3168233"></a></strong></span><span class="bold"><strong>
What is an <em class="firstterm">arbitrator</em>?
</strong></span></p><p>
If one or more nodes in a cluster fail, it is possible that
not all cluster nodes will be able to “<span class="quote">see</span>” one
another. In fact, it is possible that two sets of nodes
might become isolated from one another in a network
partitioning, also known as a “<span class="quote">split brain</span>”
scenario. This type of situation is undesirable because each
set of nodes tries to behave as though it is the entire
cluster.
</p><p>
When cluster nodes go down, there are two possibilities. If
more than 50% of the remaining nodes can communicate with
each other, we have what is sometimes called a
“<span class="quote">majority rules</span>” situation, and this set of
nodes is considered to be the cluster. The arbitrator comes
into play when there is an even number of nodes: in such
cases, the set of nodes to which the arbitrator belongs is
considered to be the cluster, and nodes not belonging to
this set are shut down.
</p><p>
The preceding information is somewhat simplified. A more
complete explanation taking into account node groups
follows:
</p><p>
When all nodes in at least one node group are alive, network
partitioning is not an issue, because no one portion of the
cluster can form a functional cluster. The real problem
arises when no single node group has all its nodes alive, in
which case network partitioning (the
“<span class="quote">split-brain</span>” scenario) becomes possible. Then
an arbitrator is required. All cluster nodes recognize the
same node as the arbitrator, which is normally the
management server; however, it is possible to configure any
of the MySQL Servers in the cluster to act as the arbitrator
instead. The arbitrator accepts the first set of cluster
nodes to contact it, and tells the remaining set to shut
down. Arbitrator selection is controlled by the
<code class="literal">ArbitrationRank</code> configuration parameter
for MySQL Server and management server nodes. (See
<a href="mysql-cluster.html#mysql-cluster-mgm-definition" title="16.3.4.4. Defining the Management Server">Section 16.3.4.4, “Defining the Management Server”</a>, for
details.) It should also be noted that the role of
arbitrator does not in and of itself impose any heavy
demands upon the host so designated, and thus the arbitrator
host does not need to be particularly fast or to have extra
memory especially for this purpose.
</p><p><a name="qandaitem-28-10-26"></a><span class="bold"><strong>28.10.26: </strong></span><span class="bold"><strong><a class="indexterm" name="id3168343"></a></strong></span><span class="bold"><strong>
What data types are supported by MySQL Cluster?
</strong></span></p><p>
MySQL Cluster supports all of the usual MySQL data types,
with the exception of those associated with MySQL's spatial
extensions. (See <a href="spatial-extensions.html" title="Chapter 17. Spatial Extensions">Chapter 17, <i>Spatial Extensions</i></a>.) In
addition, there are some differences with regard to indexes
when used with <code class="literal">NDB</code> tables.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
MySQL Cluster tables (that is, tables created with
<code class="literal">ENGINE=NDBCLUSTER</code>) have only
fixed-width rows. This means that (for example) each
record containing a <code class="literal">VARCHAR(255)</code> column
will require space for 255 characters (as required for the
character set and collation being used for the table),
regardless of the actual number of characters stored
therein. This issue is expected to be fixed in a future
MySQL release series.
</p></div><p>
See <a href="mysql-cluster.html#mysql-cluster-limitations" title="16.10. Known Limitations of MySQL Cluster">Section 16.10, “Known Limitations of MySQL Cluster”</a>, for more
information about these issues.
</p><p><a name="qandaitem-28-10-27"></a><span class="bold"><strong>28.10.27: </strong></span><span class="bold"><strong><a class="indexterm" name="id3168428"></a></strong></span><span class="bold"><strong>
How do I start and stop MySQL Cluster?
</strong></span></p><p>
It is necessary to start each node in the cluster
separately, in the following order:
</p><div class="orderedlist"><ol type="1"><li><p>
Start the management node with the
<span><strong class="command">ndb_mgmd</strong></span> command.
</p></li><li><p>
Start each data node with the <span><strong class="command">ndbd</strong></span>
command.
</p></li><li><p>
Start each MySQL server (SQL node) using
<span><strong class="command">mysqld_safe --user=mysql &</strong></span>.
</p></li></ol></div><p>
Each of these commands must be run from a system shell on
the machine housing the affected node. (You do not have to
be physically present at the machine — a remote login
shell can be used for this purpose.) You can verify that the
cluster is running by starting the MGM management client
<span><strong class="command">ndb_mgm</strong></span> on the machine housing the MGM
node and issuing the <code class="literal">SHOW</code> or <code class="literal">ALL
STATUS</code> command.
</p><p>
To shut down a running cluster, issue the command
<code class="literal">SHUTDOWN</code> in the MGM client.
Alternatively, you may enter the following command in a
system shell on the machine hosting the MGM node:
</p><pre class="programlisting">shell> <strong class="userinput"><code>ndb_mgm -e "SHUTDOWN"</code></strong>
</pre><p>
(Note that the quotation marks are optional here; the
<code class="literal">SHUTDOWN</code> command itself is not
case-sensitive.)
</p><p>
Either of these commands causes the
<span><strong class="command">ndb_mgm</strong></span>, <span><strong class="command">ndb_mgm</strong></span>, and
any <span><strong class="command">ndbd</strong></span> processes to terminate
gracefully. MySQL servers running as Cluster SQL nodes can
be stopped using <span><strong class="command">mysqladmin shutdown</strong></span>.
</p><p>
For more information, see
<a href="mysql-cluster.html#mysql-cluster-mgm-client-commands" title="16.6.2. Commands in the MySQL Cluster Management Client">Section 16.6.2, “Commands in the MySQL Cluster Management Client”</a>, and
<a href="mysql-cluster.html#mysql-cluster-multi-shutdown-restart" title="16.2.6. Safe Shutdown and Restart">Section 16.2.6, “Safe Shutdown and Restart”</a>.
</p><p><a name="qandaitem-28-10-28"></a><span class="bold"><strong>28.10.28: </strong></span><span class="bold"><strong>
What happens to cluster data when the cluster is shut down?
</strong></span></p><p>
The data that was held in memory by the cluster's data nodes
is written to disk, and is reloaded into memory the next
time that the cluster is started.
</p><p><a name="qandaitem-28-10-29"></a><span class="bold"><strong>28.10.29: </strong></span><span class="bold"><strong>
Is it helpful to have more than one management node for a
cluster?
</strong></span></p><p>
It can be helpful as a fail-safe. Only one MGM node controls
the cluster at any given time, but it is possible to
configure one MGM as primary, and one or more additional
management nodes to take over in the event that the primary
MGM node fails.
</p><p>
See <a href="mysql-cluster.html#mysql-cluster-config-file" title="16.3.4. Configuration File">Section 16.3.4, “Configuration File”</a>, for
information on how to configure MySQL Cluster management
nodes.
</p><p><a name="qandaitem-28-10-30"></a><span class="bold"><strong>28.10.30: </strong></span><span class="bold"><strong>
Can I mix different kinds of hardware and operating systems
in one MySQL Cluster?
</strong></span></p><p>
Yes, so long as all machines and operating systems have the
same “<span class="quote">endianness</span>” (all big-endian or all
little-endian). It is also possible to use different MySQL
Cluster releases on different nodes. However, we recommend
this be done only as part of a rolling upgrade procedure
(see <a href="mysql-cluster.html#mysql-cluster-rolling-restart" title="16.4.1. Performing a Rolling Restart of the Cluster">Section 16.4.1, “Performing a Rolling Restart of the Cluster”</a>).
</p><p><a name="qandaitem-28-10-31"></a><span class="bold"><strong>28.10.31: </strong></span><span class="bold"><strong>
Can I run two data nodes on a single host? Two SQL nodes?
</strong></span></p><p>
Yes, it is possible to do this. In the case of multiple data
nodes, it is advisable (but not required) for each node to
use a different data directory. If you want to run multiple
SQL nodes on one machine, each instance of
<span><strong class="command">mysqld</strong></span> must use a different TCP/IP port.
However, <span class="emphasis"><em>running more than one cluster node of a
given type per machine is not supported for production
use</em></span>.
</p><p><a name="qandaitem-28-10-32"></a><span class="bold"><strong>28.10.32: </strong></span><span class="bold"><strong>
Can I use hostnames with MySQL Cluster?
</strong></span></p><p>
Yes, it is possible to use DNS and DHCP for cluster hosts.
However, if your application requires “<span class="quote">five
nines</span>” availability, we recommend using fixed IP
addresses. Making communication between Cluster hosts
dependent on services such as DNS and DHCP introduces
additional points of failure, and the fewer of these, the
better.
</p><p><a name="qandaitem-28-10-33"></a><span class="bold"><strong>28.10.33: </strong></span><span class="bold"><strong>
How do I handle MySQL users in a Cluster having multiple
MySQL servers?
</strong></span></p><p>
MySQL user accounts and privileges are not automatically
propagated between different MySQL servers accessing the
same MySQL Cluster. Therefore, you must make sure that these
are copied between the SQL nodes yourself.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-cjk"></a>A.11. MySQL 5.0 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets</h2></div></div></div><a class="indexterm" name="id3168808"></a><a class="indexterm" name="id3168820"></a><a class="indexterm" name="id3168833"></a><a class="indexterm" name="id3168846"></a><p>
This set of Frequently Asked Questions derives from the experience
of MySQL's Support and Development groups in handling many
inquiries about CJK (Chinese-Japanese-Korean) issues.
</p><a class="indexterm" name="id3168866"></a><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-11-1">28.11.1: </a>
I have inserted CJK characters into my table. Why does
<code class="literal">SELECT</code> display them as “<span class="quote">?</span>”
characters?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-2">28.11.2: </a><a class="indexterm" name="id3168913"></a><a class="indexterm" name="id3168925"></a><a class="indexterm" name="id3168937"></a>
What GB (Chinese) character sets does MySQL support?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-3">28.11.3: </a><a class="indexterm" name="id3168969"></a><a class="indexterm" name="id3168981"></a><a class="indexterm" name="id3168993"></a>
What problems should I be aware of when working with the
Big5 Chinese character set?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-4">28.11.4: </a><a class="indexterm" name="id3169011"></a><a class="indexterm" name="id3169023"></a><a class="indexterm" name="id3169035"></a>
Why do Japanese character set conversions fail?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-5">28.11.5: </a>
What should I do if I want to convert SJIS
<code class="literal">81CA</code> to <code class="literal">cp932</code>?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-6">28.11.6: </a><a class="indexterm" name="id3169078"></a><a class="indexterm" name="id3169090"></a><a class="indexterm" name="id3169102"></a>
How does MySQL represent the Yen (<code class="literal">¥</code>)
sign?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-7">28.11.7: </a>
Do MySQL plan to make a separate character set where
<code class="literal">5C</code> is the Yen sign, as at least one other
major DBMS does?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-8">28.11.8: </a><a class="indexterm" name="id3169141"></a><a class="indexterm" name="id3169154"></a><a class="indexterm" name="id3169166"></a>
Of what issues should I be aware when working with Korean
character sets in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-9">28.11.9: </a><a class="indexterm" name="id3169184"></a><a class="indexterm" name="id3169196"></a><a class="indexterm" name="id3169208"></a>
Why do I get <span class="errortext">Data truncated</span> error
messages?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-10">28.11.10: </a><a class="indexterm" name="id3169229"></a><a class="indexterm" name="id3169241"></a>
Why does my GUI front end or browser not display CJK
characters correctly in my application using Access, PHP, or
another API?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-11">28.11.11: </a><a class="indexterm" name="id3169264"></a>
I've upgraded to MySQL 5.0. How can I revert to
behavior like that in MySQL 4.0 with regard to character
sets?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-12">28.11.12: </a><a class="indexterm" name="id3169287"></a><a class="indexterm" name="id3169299"></a>
Why do some <code class="literal">LIKE</code> and
<code class="literal">FULLTEXT</code> searches with CJK characters
fail?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-13">28.11.13: </a><a class="indexterm" name="id3169332"></a><a class="indexterm" name="id3169344"></a>
What CJK character sets are available in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-14">28.11.14: </a><a class="indexterm" name="id3169365"></a><a class="indexterm" name="id3169378"></a>
How do I know whether character <em class="replaceable"><code>X</code></em>
is available in all character sets?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-15">28.11.15: </a><a class="indexterm" name="id3169403"></a><a class="indexterm" name="id3169415"></a><a class="indexterm" name="id3169427"></a><a class="indexterm" name="id3169438"></a>
Why don't CJK strings sort correctly in Unicode? (I)
</p></li><li><p><a href="faqs.html#qandaitem-28-11-16">28.11.16: </a><a class="indexterm" name="id3169460"></a><a class="indexterm" name="id3169472"></a><a class="indexterm" name="id3169483"></a><a class="indexterm" name="id3169495"></a><a class="indexterm" name="id3169507"></a>
Why don't CJK strings sort correctly in Unicode? (II)
</p></li><li><p><a href="faqs.html#qandaitem-28-11-17">28.11.17: </a><a class="indexterm" name="id3169528"></a>
Why are my supplementary characters rejected by MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-18">28.11.18: </a><a class="indexterm" name="id3169550"></a><a class="indexterm" name="id3169561"></a><a class="indexterm" name="id3169573"></a>
Shouldn't it be “<span class="quote">CJKV</span>”?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-19">28.11.19: </a><a class="indexterm" name="id3169594"></a>
Does MySQL allow CJK characters to be used in database and
table names?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-20">28.11.20: </a><a class="indexterm" name="id3169615"></a><a class="indexterm" name="id3169627"></a><a class="indexterm" name="id3169639"></a><a class="indexterm" name="id3169651"></a><a class="indexterm" name="id3169663"></a><a class="indexterm" name="id3169674"></a>
Where can I find translations of the MySQL Manual into
Chinese, Japanese, and Korean?
</p></li><li><p><a href="faqs.html#qandaitem-28-11-21">28.11.21: </a>
Where can I get help with CJK and related issues in MySQL?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-11-1"></a><span class="bold"><strong>28.11.1: </strong></span><span class="bold"><strong>
I have inserted CJK characters into my table. Why does
<code class="literal">SELECT</code> display them as “<span class="quote">?</span>”
characters?
</strong></span></p><p>
This problem is usually due to a setting in MySQL that
doesn't match the settings for the application program or
the operating system. Here are some common steps for
correcting these types of issues:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<span class="emphasis"><em>Be certain of what MySQL version you are
using</em></span>.
</p><p>
Use the statement <code class="literal">SELECT VERSION();</code>
to determine this.
</p></li><li><p>
<span class="emphasis"><em>Make sure that the database is actually using
the desired character set</em></span>.
</p><p>
People often think that the client character set is
always the same as either the server character set or
the character set used for display purposes. However,
both of these are false assumptions. You can make sure
by checking the result of <code class="literal">SHOW CREATE TABLE
<em class="replaceable"><code>tablename</code></em></code> or
— better — yet by using this statement:
</p><pre class="programlisting">SELECT character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema = your_database_name
AND table_name = your_table_name
AND column_name = your_column_name;
</pre><p>
</p></li><li><p>
<span class="emphasis"><em>Determine the hexadecimal value of the
character or characters that are not being displayed
correctly</em></span>.
</p><p>
You can obtain this information for a column
<em class="replaceable"><code>column_name</code></em> in the table
<em class="replaceable"><code>table_name</code></em> using the
following query:
</p><pre class="programlisting">SELECT HEX(<em class="replaceable"><code>column_name</code></em>)
FROM <em class="replaceable"><code>table_name</code></em>;
</pre><p>
<code class="literal">3F</code> is the encoding for the
<code class="literal">?</code> character; this means that
<code class="literal">?</code> is the character actually stored in
the column. This most often happens because of a problem
converting a particular character from your client
character set to the target character set.
</p></li><li><p>
<span class="emphasis"><em>Make sure that a round trip possible —
that is, when you select
<em class="replaceable"><code>literal</code></em> (or
<em class="replaceable"><code>_introducer
hexadecimal-value</code></em>), you obtain
<em class="replaceable"><code>literal</code></em> as a
result</em></span>.
</p><p>
For example, the Japanese
<span class="foreignphrase"><em class="foreignphrase">Katakana</em></span> character
<span class="foreignphrase"><em class="foreignphrase">Pe</em></span>
(<code class="literal">ペ'</code>) exists in all CJK character
sets, and has the code point value (hexadecimal coding)
<code class="literal">0x30da</code>. To test a round trip for this
character, use this query:
</p><pre class="programlisting">SELECT 'ペ' AS `ペ`; /* or SELECT _ucs2 0x30da; */
</pre><p>
If the result is not also <code class="literal">ペ</code>, then
the round trip has failed.
</p><p>
For bug reports regarding such failures, we might ask
you to follow up with <code class="literal">SELECT
HEX('ペ');</code>. Then we can determine whether the
client encoding is correct.
</p></li><li><p>
<span class="emphasis"><em>Make sure that the problem is not with the
browser or other application, rather than with
MySQL</em></span>.
</p><p>
Use the <span><strong class="command">mysql</strong></span> client program (on
Windows: <span><strong class="command">mysql.exe</strong></span>) to accomplish
this task. If <span><strong class="command">mysql</strong></span> displays
correctly but your application doesn't, then your
problem is probably due to system settings.
</p><p>
To find out what your settings are, use the
<code class="literal">SHOW VARIABLES</code> statement, whose
output should resemble what is shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.03 sec)
</pre><p>
These are typical character-set settings for an
international-oriented client (notice the use of
<code class="literal">utf8</code> Unicode) connected to a server
in the West (<code class="literal">latin1</code> is a West Europe
character set and a default for MySQL).
</p><p>
Although Unicode (usually the <code class="literal">utf8</code>
variant on Unix, and the <code class="literal">ucs2</code> variant
on Windows) is preferable to Latin, it's often not what
your operating system utilities support best. Many
Windows users find that a Microsoft character set, such
as <code class="literal">cp932</code> for Japanese Windows, is
what's suitable.
</p><p>
If you cannot control the server settings, and you have
no idea what your underlying computer is, then try
changing to a common character set for the country that
you're in (<code class="literal">euckr</code> = Korea;
<code class="literal">gb2312</code> or <code class="literal">gbk</code> =
People's Republic of China; <code class="literal">big5</code> =
Taiwan; <code class="literal">sjis</code>,
<code class="literal">ujis</code>, <code class="literal">cp932</code>, or
<code class="literal">eucjpms</code> = Japan;
<code class="literal">ucs2</code> or <code class="literal">utf8</code> =
anywhere). Usually it is necessary to change only the
client and connection and results settings. There is a
simple statement which changes all three at once:
<code class="literal">SET NAMES</code>. For example:
</p><pre class="programlisting">SET NAMES 'big5';
</pre><p>
Once the setting is correct, you can make it permanent
by editing <code class="filename">my.cnf</code> or
<code class="filename">my.ini</code>. For example you might add
lines looking like these:
</p><pre class="programlisting">[mysqld]
character-set-server=big5
[client]
default-character-set=big5
</pre><p>
</p><p>
It is also possible that there are issues with the API
configuration setting being used in your application;
see <em class="citetitle">Why does my GUI front end or browser not
display CJK characters correctly...?</em> for
more information.
</p></li></ul></div><p><a name="qandaitem-28-11-2"></a><span class="bold"><strong>28.11.2: </strong></span><span class="bold"><strong><a class="indexterm" name="id3170210"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170225"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170240"></a></strong></span><span class="bold"><strong></strong></span><span class="bold"><strong></strong></span><span class="bold"><strong>
What GB (Chinese) character sets does MySQL support?
</strong></span></p><p>
MySQL supports the two common variants of the
<em class="firstterm">GB</em> (<span class="foreignphrase"><em class="foreignphrase">Guojia
Biaozhun</em></span>, or <span class="emphasis"><em>National
Standard</em></span>) character sets which are official in
the People's Republic of China: <code class="literal">gb2312</code>
and <code class="literal">gbk</code>. Sometimes people try to insert
<code class="literal">gbk</code> characters into
<code class="literal">gb2312</code>, and it works most of the time
because <code class="literal">gbk</code> is a superset of
<code class="literal">gb2312</code> — but eventually they try to
insert a rarer Chinese character and it doesn't work. (See
<a href="http://bugs.mysql.com/16072" target="_top">Bug#16072</a> for an example).
</p><p>
Here, we try to clarify exactly what characters are
legitimate in <code class="literal">gb2312</code> or
<code class="literal">gbk</code>, with reference to the official
documents. Please check these references before reporting
<code class="literal">gb2312</code> or <code class="literal">gbk</code> bugs.
</p><div class="itemizedlist"><ul type="disc"><li><p>
For a complete listing of the
<code class="literal">gb2312</code> characters, ordered
according to the <code class="literal">gb2312_chinese_ci</code>
collation:
<a href="http://d.udm.net/bar/~bar/charts/gb2312_chinese_ci.html" target="_top">http://d.udm.net/bar/~bar/charts/gb2312_chinese_ci.html</a>.
</p></li><li><p>
MySQL's <code class="literal">gbk</code> is in reality
“<span class="quote">Microsoft code page 936</span>”. This differs
from the official <code class="literal">gbk</code> for
characters <code class="literal">A1A4</code> (middle dot),
<code class="literal">A1AA</code> (em dash),
<code class="literal">A6E0-A6F5</code>, and
<code class="literal">A8BB-A8C0</code>. For a listing of the
differences, see
<a href="http://recode.progiciels-bpi.ca/showfile.html?name=dist/libiconv/gbk.h" target="_top">http://recode.progiciels-bpi.ca/showfile.html?name=dist/libiconv/gbk.h</a>.
</p></li><li><p>
For a listing of <code class="literal">gbk</code>/Unicode
mappings, see
<a href="http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP936.TXT" target="_top">http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP936.TXT</a>.
</p></li><li><p>
For MySQL's listing of <code class="literal">gbk</code>
characters, see
<a href="http://d.udm.net/bar/~bar/charts/gbk_chinese_ci.html" target="_top">http://d.udm.net/bar/~bar/charts/gbk_chinese_ci.html</a>.
</p></li></ul></div><p>
</p><p><a name="qandaitem-28-11-3"></a><span class="bold"><strong>28.11.3: </strong></span><span class="bold"><strong><a class="indexterm" name="id3170509"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170524"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170539"></a></strong></span><span class="bold"><strong>
What problems should I be aware of when working with the
Big5 Chinese character set?
</strong></span></p><p>
MySQL supports the Big5 character set which is common in
Hong Kong and Taiwan (Republic of China). MySQL's
<code class="literal">big5</code> is in reality Microsoft code page
950, which is very similar to the original
<code class="literal">big5</code> character set. We changed to this
character set starting with MySQL version 4.1.16 / 5.0.16
(as a result of <a href="http://bugs.mysql.com/12476" target="_top">Bug#12476</a>). For example, the following
statements work in current versions of MySQL, but not in old
versions:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE big5 (BIG5 CHAR(1) CHARACTER SET BIG5);</code></strong>
Query OK, 0 rows affected (0.13 sec)
mysql> <strong class="userinput"><code>INSERT INTO big5 VALUES (0xf9dc);</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM big5;</code></strong>
+------+
| big5 |
+------+
| 嫺 |
+------+
1 row in set (0.02 sec)
</pre><p>
A feature request for adding <code class="literal">HKSCS</code>
extensions has been filed. People who need this extension
may find the suggested patch for <a href="http://bugs.mysql.com/13577" target="_top">Bug#13577</a> to be of
interest.
</p><p><a name="qandaitem-28-11-4"></a><span class="bold"><strong>28.11.4: </strong></span><span class="bold"><strong><a class="indexterm" name="id3170649"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170664"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3170680"></a></strong></span><span class="bold"><strong>
Why do Japanese character set conversions fail?
</strong></span></p><p>
MySQL supports the <code class="literal">sjis</code>,
<code class="literal">ujis</code>, <code class="literal">cp932</code>, and
<code class="literal">eucjpms</code> character sets, as well as
Unicode. A common need is to convert between character sets.
For example, there might be a Unix server (typically with
<code class="literal">sjis</code> or <code class="literal">ujis</code>) and a
Windows client (typically with <code class="literal">cp932</code>).
</p><p>
In the following conversion table, the
<code class="literal">ucs2</code> column represents the source, and
the <code class="literal">sjis</code>, <code class="literal">cp932</code>,
<code class="literal">ujis</code>, and <code class="literal">eucjpms</code>
columns represent the destinations — that is, the last
4 columns provide the hexadecimal result when we use
<code class="literal">CONVERT(ucs2)</code> or we assign a
<code class="literal">ucs2</code> column containing the value to an
<code class="literal">sjis</code>, <code class="literal">cp932</code>,
<code class="literal">ujis</code>, or <code class="literal">eucjpms</code>
column.
</p><div class="informaltable"><table border="1"><colgroup><col><col align="left"><col align="left"><col align="left"><col align="left"><col align="left"></colgroup><thead><tr><th>Character Name</th><th align="left">ucs2</th><th align="left">sjis</th><th align="left">cp932</th><th align="left">ujis</th><th align="left">eucjpms</th></tr></thead><tbody><tr><td>BROKEN BAR</td><td align="left">00A6</td><td align="left">3F</td><td align="left">3F</td><td align="left">8FA2C3</td><td align="left">3F</td></tr><tr><td>FULLWIDTH BROKEN BAR</td><td align="left">FFE4</td><td align="left">3F</td><td align="left">FA55</td><td align="left">3F</td><td align="left">8FA2</td></tr><tr><td>YEN SIGN</td><td align="left">00A5</td><td align="left">3F</td><td align="left">3F</td><td align="left">20</td><td align="left">3F</td></tr><tr><td>FULLWIDTH YEN SIGN</td><td align="left">FFE5</td><td align="left">818F</td><td align="left">818F</td><td align="left">A1EF</td><td align="left">3F</td></tr><tr><td>TILDE</td><td align="left">007E</td><td align="left">7E</td><td align="left">7E</td><td align="left">7E</td><td align="left">7E</td></tr><tr><td>OVERLINE</td><td align="left">203E</td><td align="left">3F</td><td align="left">3F</td><td align="left">20</td><td align="left">3F</td></tr><tr><td>HORIZONTAL BAR</td><td align="left">2015</td><td align="left">815C</td><td align="left">815C</td><td align="left">A1BD</td><td align="left">A1BD</td></tr><tr><td>EM DASH</td><td align="left">2014</td><td align="left">3F</td><td align="left">3F</td><td align="left">3F</td><td align="left">3F</td></tr><tr><td>REVERSE SOLIDUS</td><td align="left">005C</td><td align="left">815F</td><td align="left">5C</td><td align="left">5C</td><td align="left">5C</td></tr><tr><td>FULLWIDTH ""</td><td align="left">FF3C</td><td align="left">3F</td><td align="left">815F</td><td align="left">3F</td><td align="left">A1C0</td></tr><tr><td>WAVE DASH</td><td align="left">301C</td><td align="left">8160</td><td align="left">3F</td><td align="left">A1C1</td><td align="left">3F</td></tr><tr><td>FULLWIDTH TILDE</td><td align="left">FF5E</td><td align="left">3F</td><td align="left">8160</td><td align="left">3F</td><td align="left">A1C1</td></tr><tr><td>DOUBLE VERTICAL LINE</td><td align="left">2016</td><td align="left">8161</td><td align="left">3F</td><td align="left">A1C2</td><td align="left">3F</td></tr><tr><td>PARALLEL TO</td><td align="left">2225</td><td align="left">3F</td><td align="left">8161</td><td align="left">3F</td><td align="left">A1C2</td></tr><tr><td>MINUS SIGN</td><td align="left">2212</td><td align="left">817C</td><td align="left">3F</td><td align="left">A1DD</td><td align="left">3F</td></tr><tr><td>FULLWIDTH HYPHEN-MINUS</td><td align="left">FF0D</td><td align="left">3F</td><td align="left">817C</td><td align="left">3F</td><td align="left">A1DD</td></tr><tr><td>CENT SIGN</td><td align="left">00A2</td><td align="left">8191</td><td align="left">3F</td><td align="left">A1F1</td><td align="left">3F</td></tr><tr><td>FULLWIDTH CENT SIGN</td><td align="left">FFE0</td><td align="left">3F</td><td align="left">8191</td><td align="left">3F</td><td align="left">A1F1</td></tr><tr><td>POUND SIGN</td><td align="left">00A3</td><td align="left">8192</td><td align="left">3F</td><td align="left">A1F2</td><td align="left">3F</td></tr><tr><td>FULLWIDTH POUND SIGN</td><td align="left">FFE1</td><td align="left">3F</td><td align="left">8192</td><td align="left">3F</td><td align="left">A1F2</td></tr><tr><td>NOT SIGN</td><td align="left">00AC</td><td align="left">81CA</td><td align="left">3F</td><td align="left">A2CC</td><td align="left">3F</td></tr><tr><td>FULLWIDTH NOT SIGN</td><td align="left">FFE2</td><td align="left">3F</td><td align="left">81CA</td><td align="left">3F</td><td align="left">A2CC</td></tr></tbody></table></div><p>
Now consider this portion of the table:
</p><div class="informaltable"><table border="1"><colgroup><col><col align="right"><col align="right"><col align="right"></colgroup><thead><tr><th> </th><th align="right">ucs2</th><th align="right">sjis</th><th align="right">cp932</th></tr></thead><tbody><tr><td>NOT SIGN</td><td align="right">00AC</td><td align="right">81CA</td><td align="right">3F</td></tr><tr><td>FULLWIDTH NOT SIGN</td><td align="right">FFE2</td><td align="right">3F</td><td align="right">81CA</td></tr></tbody></table></div><p>
This means that MySQL converts the <code class="literal">NOT
SIGN</code> (Unicode <code class="literal">U+00AC</code>) to
<code class="literal">sjis</code> code point <code class="literal">0x81CA</code>
and to <code class="literal">cp932</code> code point
<code class="literal">3F</code>. (<code class="literal">3F</code> is the
question mark (“<span class="quote">?</span>”) — this is what is
always used when the conversion cannot be performed.
</p><p><a name="qandaitem-28-11-5"></a><span class="bold"><strong>28.11.5: </strong></span><span class="bold"><strong>
What should I do if I want to convert SJIS
<code class="literal">81CA</code> to <code class="literal">cp932</code>?
</strong></span></p><p>
Our answer is: “<span class="quote">?</span>”. There are serious
complaints about this: many people would prefer a
“<span class="quote">loose</span>” conversion, so that <code class="literal">81CA (NOT
SIGN)</code> in <code class="literal">sjis</code> becomes
<code class="literal">81CA (FULLWIDTH NOT SIGN)</code> in
<code class="literal">cp932</code>. We are considering a change to
this behavior.
</p><p><a name="qandaitem-28-11-6"></a><span class="bold"><strong>28.11.6: </strong></span><span class="bold"><strong><a class="indexterm" name="id3171633"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171648"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171663"></a></strong></span><span class="bold"><strong>
How does MySQL represent the Yen (<code class="literal">¥</code>)
sign?
</strong></span></p><p>
A problem arises because some versions of Japanese character
sets (both <code class="literal">sjis</code> and
<code class="literal">euc</code>) treat <code class="literal">5C</code> as a
<em class="firstterm">reverse solidus</em> (<code class="literal">\</code>
— also known as a backslash), and others treat it as a
yen sign (<code class="literal">¥</code>).
</p><p>
MySQL follows only one version of the JIS (Japanese
Industrial Standards) standard description. In MySQL,
<span class="emphasis"><em><code class="literal">5C</code> is always the reverse
solidus (<code class="literal">\</code>)</em></span>.
</p><p><a name="qandaitem-28-11-7"></a><span class="bold"><strong>28.11.7: </strong></span><span class="bold"><strong>
Do MySQL plan to make a separate character set where
<code class="literal">5C</code> is the Yen sign, as at least one other
major DBMS does?
</strong></span></p><p>
This is one possible solution to the Yen sign issue;
however, this will not happen in MySQL 5.1 or 5.2.
</p><p><a name="qandaitem-28-11-8"></a><span class="bold"><strong>28.11.8: </strong></span><span class="bold"><strong><a class="indexterm" name="id3171784"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171800"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171815"></a></strong></span><span class="bold"><strong>
Of what issues should I be aware when working with Korean
character sets in MySQL?
</strong></span></p><p>
In theory, while there have been several versions of the
<code class="literal">euckr</code> (<em class="firstterm">Extended Unix Code
Korea</em>) character set, only one problem has been
noted.
</p><p>
We use the “<span class="quote">ASCII</span>” variant of EUC-KR, in which
the code point <code class="literal">0x5c</code> is REVERSE SOLIDUS,
that is <code class="literal">\</code>, instead of the
“<span class="quote">KS-Roman</span>” variant of EUC-KR, in which the code
point <code class="literal">0x5c</code> is <code class="literal">WON
SIGN</code>(<code class="literal">₩</code>). This means that you
cannot convert Unicode <code class="literal">U+20A9</code> to
<code class="literal">euckr</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT</code></strong>
-> <strong class="userinput"><code>CONVERT('₩' USING euckr) AS euckr,</code></strong>
-> <strong class="userinput"><code>HEX(CONVERT('₩' USING euckr)) AS hexeuckr;</code></strong>
+-------+----------+
| euckr | hexeuckr |
+-------+----------+
| ? | 3F |
+-------+----------+
1 row in set (0.00 sec)
</pre><p>
MySQL's graphic Korean chart is here:
<a href="http://d.udm.net/bar/~bar/charts/euckr_korean_ci.html" target="_top">http://d.udm.net/bar/~bar/charts/euckr_korean_ci.html</a>.
</p><p><a name="qandaitem-28-11-9"></a><span class="bold"><strong>28.11.9: </strong></span><span class="bold"><strong><a class="indexterm" name="id3171957"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171973"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3171988"></a></strong></span><span class="bold"><strong>
Why do I get <span class="errortext">Data truncated</span> error
messages?
</strong></span></p><p>
For illustration, we'll create a table with one Unicode
(<code class="literal">ucs2</code>) column and one Chinese
(<code class="literal">gb2312</code>) column.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE ch</code></strong>
-> <strong class="userinput"><code>(ucs2 CHAR(3) CHARACTER SET ucs2,</code></strong>
-> <strong class="userinput"><code>gb2312 CHAR(3) CHARACTER SET gb2312);</code></strong>
Query OK, 0 rows affected (0.05 sec)
</pre><p>
We'll try to place the rare character <code class="literal">汌</code>
in both columns.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO ch VALUES ('A汌B','A汌B');</code></strong>
Query OK, 1 row affected, 1 warning (0.00 sec)
</pre><p>
Ah, there's a warning. Let's see what it is.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW WARNINGS;</code></strong>
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'gb2312' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)
</pre><p>
So it's a warning about the <code class="literal">gb2312</code> column
only.
</p><pre class="programlisting">mysql> SELECT ucs2,HEX(ucs2),gb2312,HEX(gb2312) FROM ch;
+-------+--------------+--------+-------------+
| ucs2 | HEX(ucs2) | gb2312 | HEX(gb2312) |
+-------+--------------+--------+-------------+
| A汌B | 00416C4C0042 | A?B | 413F42 |
+-------+--------------+--------+-------------+
1 row in set (0.00 sec)
</pre><p>
There are several things that need explanation here.
</p><div class="orderedlist"><ol type="1"><li><p>
The fact that it's a “<span class="quote">warning</span>” rather
than an “<span class="quote">error</span>” is characteristic of
MySQL. We like to try to do what we can, to get the
best fit, rather than give up.
</p></li><li><p>
The <code class="literal">汌</code> character isn't in the
<code class="literal">gb2312</code> character set. We described
that problem earlier.
</p></li><li><p>
Admittedly the message is misleading. We didn't
“<span class="quote">truncate</span>” in this case, we replaced with
a question mark. We've had a complaint about this
message (See <a href="http://bugs.mysql.com/9337" target="_top">Bug#9337</a>). But until we come up with
something better, just accept that error/warning code
2165 can mean a variety of things.
</p></li><li><p>
With <code class="literal">SQL_MODE=TRADITIONAL</code>, there
would be an error message, but instead of error 2165
you would see: <code class="literal">ERROR 1406 (22001): Data too
long for column 'gb2312' at row 1</code>.
</p></li></ol></div><p>
</p><p><a name="qandaitem-28-11-10"></a><span class="bold"><strong>28.11.10: </strong></span><span class="bold"><strong><a class="indexterm" name="id3172214"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3172229"></a></strong></span><span class="bold"><strong>
Why does my GUI front end or browser not display CJK
characters correctly in my application using Access, PHP, or
another API?
</strong></span></p><p>
Obtain a direct connection to the server using the
<span><strong class="command">mysql</strong></span> client (Windows:
<span><strong class="command">mysql.exe</strong></span>), and try the same query there.
If <span><strong class="command">mysql</strong></span> responds correctly, then the
trouble may be that your application interface requires
initialization. Use <span><strong class="command">mysql</strong></span> to tell you
what character set or sets it uses with the statement
<code class="literal">SHOW VARIABLES LIKE 'char%';</code>. If you are
using Access, then you are most likely connecting with
MyODBC. In this case, you should check
<a href="connectors.html#myodbc-configuration" title="24.1.3. Connector/ODBC Configuration">Section 24.1.3, “Connector/ODBC Configuration”</a>. If, for instance,
you use <code class="literal">big5</code>, you would enter
<code class="literal">SET NAMES 'big5'</code>. (Note that no
<code class="literal">;</code> is required in this case). If you are
using ASP, you might need to add <code class="literal">SET
NAMES</code> in the code. Here is an example that has
worked in the past:
</p><pre class="programlisting"><%
Session.CodePage=0
Dim strConnection
Dim Conn
strConnection="driver={MySQL ODBC 3.51 Driver};server=<em class="replaceable"><code>server</code></em>;uid=<em class="replaceable"><code>username</code></em>;" \
& "pwd=<em class="replaceable"><code>password</code></em>;database=<em class="replaceable"><code>database</code></em>;stmt=SET NAMES 'big5';"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
%>
</pre><p>
In much the same way, if you are using any character set
other than <code class="literal">latin1</code> with Connector/NET,
then you must specify the character set in the connection
string. See
<a href="connectors.html#connector-net-using-connecting" title="24.2.5.1. Connecting to MySQL Using Connector/NET">Section 24.2.5.1, “Connecting to MySQL Using Connector/NET”</a>, for more
information.
</p><p>
If you are using PHP, try this:
</p><pre class="programlisting"><?php
$link = mysql_connect($host, $usr, $pwd);
mysql_select_db($db);
if( mysql_error() ) { print "Database ERROR: " . mysql_error(); }
mysql_query("SET NAMES 'utf8'", $link);
?>
</pre><p>
In this case, we used <code class="literal">SET NAMES</code> to change
<code class="literal">character_set_client</code> and
<code class="literal">character_set_connection</code> and
<code class="literal">character_set_results</code>.
</p><p>
We encourage the use of the newer <code class="literal">mysqli</code>
extension, rather than <code class="literal">mysql</code>. Using
<code class="literal">mysqli</code>, the previous example could be
rewritten as shown here:
</p><pre class="programlisting">
<?php
$link = new mysqli($host, $usr, $pwd, $db);
if( mysqli_connect_errno() )
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$link->query("SET NAMES 'utf8'");
?>
</pre><p>
Another issue often encountered in PHP applications has to
do with assumptions made by the browser. Sometimes adding or
changing a <code class="literal"><meta></code> tag suffices to
correct the problem: for example, to insure that the user
agent interprets page content as <code class="literal">UTF-8</code>,
you should include <code class="literal"><meta
http-equiv="Content-Type" content="text/html;
charset=utf-8"></code> in the
<code class="literal"><head></code> of the HTML page.
</p><p>
If you are using Connector/J, see
<a href="connectors.html#connector-j-reference-charsets" title="24.4.4.4. Using Character Sets and Unicode">Section 24.4.4.4, “Using Character Sets and Unicode”</a>.
</p><p><a name="qandaitem-28-11-11"></a><span class="bold"><strong>28.11.11: </strong></span><span class="bold"><strong><a class="indexterm" name="id3172501"></a></strong></span><span class="bold"><strong>
I've upgraded to MySQL 5.0. How can I revert to
behavior like that in MySQL 4.0 with regard to character
sets?
</strong></span></p><p>
In MySQL Version 4.0, there was a single
“<span class="quote">global</span>” character set for both server and
client, and the decision as to which character to use was
made by the server administrator. This changed starting with
MySQL Version 4.1. What happens now is a
“<span class="quote">handshake</span>”, as described in
<a href="internationalization-localization.html#charset-connection" title="9.1.4. Connection Character Sets and Collations">Section 9.1.4, “Connection Character Sets and Collations”</a>:
</p><div class="blockquote"><blockquote class="blockquote"><p>
When a client connects, it sends to the server the name
of the character set that it wants to use. The server
uses the name to set the
<code class="literal">character_set_client</code>,
<code class="literal">character_set_results</code>, and
<code class="literal">character_set_connection</code> system
variables. In effect, the server performs a <code class="literal">SET
NAMES</code> operation using the character set name.
</p></blockquote></div><p>
The effect of this is that you cannot control the client
character set by starting <span><strong class="command">mysqld</strong></span> with
<code class="option">--character-set-server=utf8</code>. However, some
of our Asian customers have said that prefer the MySQL 4.0
behavior. To make it possible to retain this behavior, we
added a <span><strong class="command">mysqld</strong></span> switch,
<code class="option">--character-set-client-handshake</code>, which can
be turned off with
<code class="option">--skip-character-set-client-handshake</code>. If
you start <span><strong class="command">mysqld</strong></span> with
<code class="option">--skip-character-set-client-handshake</code>,
then, when a client connects, it sends to the server the
name of the character set that it wants to use —
however, <span class="emphasis"><em>the server ignores this request from the
client</em></span>.
</p><p>
By way of example, suppose that your favorite server
character set is <code class="literal">latin1</code> (unlikely in a
CJK area, but this is the default value). Suppose further
that the client uses <code class="literal">utf8</code> because this is
what the client's operating system supports. Now, start the
server with <code class="literal">latin1</code> as its default
character set:
</p><pre class="programlisting">mysqld --character-set-server=latin1
</pre><p>
And then start the client with the default character set
<code class="literal">utf8</code>:
</p><pre class="programlisting">mysql --default-character-set=utf8
</pre><p>
The current settings can be seen by viewing the output of
<code class="literal">SHOW VARIABLES</code>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.01 sec)
</pre><p>
Now stop the client, and then stop the server using
<span><strong class="command">mysqladmin</strong></span>. Then start the server again,
but this time tell it to skip the handshake like so:
</p><pre class="programlisting">mysqld --character-set-server=utf8 --skip-character-set-client-handshake
</pre><p>
Start the client with <code class="literal">utf8</code> once again as
the default character set, then display the current
settings:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.01 sec)
</pre><p>
As you can see by comparing the differing results from
<code class="literal">SHOW VARIABLES</code>, the server ignores the
client's initial settings if the
<code class="option">--skip-character-set-client-handshake</code> is
used.
</p><p><a name="qandaitem-28-11-12"></a><span class="bold"><strong>28.11.12: </strong></span><span class="bold"><strong><a class="indexterm" name="id3172802"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3172817"></a></strong></span><span class="bold"><strong>
Why do some <code class="literal">LIKE</code> and
<code class="literal">FULLTEXT</code> searches with CJK characters
fail?
</strong></span></p><p>
There is a very simple problem with <code class="literal">LIKE</code>
searches on <code class="literal">BINARY</code> and
<code class="literal">BLOB</code> columns: we need to know the end of
a character. With multi-byte character sets, different
characters might have different octet lengths. For example,
in <code class="literal">utf8</code>, <code class="literal">A</code> requires
one byte but <code class="literal">ペ</code> requires three bytes, as
shown here:
</p><pre class="programlisting">+-------------------------+---------------------------+
| OCTET_LENGTH(_utf8 'A') | OCTET_LENGTH(_utf8 'ペ') |
+-------------------------+---------------------------+
| 1 | 3 |
+-------------------------+---------------------------+
1 row in set (0.00 sec)
</pre><p>
If we don't know where the first character ends, then we
don't know where the second character begins, in which case
even very simple searches such as <code class="literal">LIKE
'_A%'</code> fail. The solution is to use a regular CJK
character set in the first place, or to convert to a CJK
character set before comparing.
</p><p>
This is one reason why MySQL cannot allow encodings of
nonexistent characters. If it is not strict about rejecting
bad input, then it has no way of knowing where characters
end.
</p><p>
For <code class="literal">FULLTEXT</code> searches, we need to know
where words begin and end. With Western languages, this is
rarely a problem because most (if not all) of these use an
easy-to-identify word boundary — the space character.
However, this is not usually the case with Asian writing. We
could use arbitrary halfway measures, like assuming that all
Han characters represent words, or (for Japanese) depending
on changes from Katakana to Hiragana due to grammatical
endings. However, the only sure solution requires a
comprehensive word list, which means that we would have to
include a dictionary in the server for each Asian language
supported. This is simply not feasible.
</p><p><a name="qandaitem-28-11-13"></a><span class="bold"><strong>28.11.13: </strong></span><span class="bold"><strong><a class="indexterm" name="id3172966"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3172981"></a></strong></span><span class="bold"><strong>
What CJK character sets are available in MySQL?
</strong></span></p><p>
The list of CJK character sets may vary depending on your
MySQL version. For example, the <code class="literal">eucjpms</code>
character set was not supported prior to MySQL 5.0.3 (see
<a href="news.html#news-5-0-3" title="E.1.27. Changes in MySQL 5.0.3 (23 March 2005: Beta)">Section E.1.27, “Changes in MySQL 5.0.3 (23 March 2005: Beta)”</a>). However, since the name of
the applicable language appears in the
<code class="literal">DESCRIPTION</code> column for every entry in the
<code class="literal">INFORMATION_SCHEMA.CHARACTER_SETS</code> table,
you can obtain a current list of all the non-Unicode CJK
character sets using this query:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT CHARACTER_SET_NAME, DESCRIPTION</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.CHARACTER_SETS</code></strong>
-> <strong class="userinput"><code>WHERE DESCRIPTION LIKE '%Chinese%'</code></strong>
-> <strong class="userinput"><code>OR DESCRIPTION LIKE '%Japanese%'</code></strong>
-> <strong class="userinput"><code>OR DESCRIPTION LIKE '%Korean%'</code></strong>
-> <strong class="userinput"><code>ORDER BY CHARACTER_SET_NAME;</code></strong>
+--------------------+---------------------------+
| CHARACTER_SET_NAME | DESCRIPTION |
+--------------------+---------------------------+
| big5 | Big5 Traditional Chinese |
| cp932 | SJIS for Windows Japanese |
| eucjpms | UJIS for Windows Japanese |
| euckr | EUC-KR Korean |
| gb2312 | GB2312 Simplified Chinese |
| gbk | GBK Simplified Chinese |
| sjis | Shift-JIS Japanese |
| ujis | EUC-JP Japanese |
+--------------------+---------------------------+
8 rows in set (0.01 sec)
</pre><p>
(See <a href="information-schema.html#character-sets-table" title="21.9. The INFORMATION_SCHEMA CHARACTER_SETS Table">Section 21.9, “The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table”</a>, for more
information.)
</p><p><a name="qandaitem-28-11-14"></a><span class="bold"><strong>28.11.14: </strong></span><span class="bold"><strong><a class="indexterm" name="id3173107"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173122"></a></strong></span><span class="bold"><strong>
How do I know whether character <em class="replaceable"><code>X</code></em>
is available in all character sets?
</strong></span></p><p>
The majority of simplified Chinese and basic non-halfwidth
Japanese <span class="foreignphrase"><em class="foreignphrase">Kana</em></span> characters
appear in all CJK character sets. This stored procedure
accepts a <code class="literal">UCS-2</code> Unicode character,
converts it to all other character sets, and displays the
results in hexadecimal.
</p><pre class="programlisting">DELIMITER //
CREATE PROCEDURE p_convert(ucs2_char CHAR(1) CHARACTER SET ucs2)
BEGIN
CREATE TABLE tj
(ucs2 CHAR(1) character set ucs2,
utf8 CHAR(1) character set utf8,
big5 CHAR(1) character set big5,
cp932 CHAR(1) character set cp932,
eucjpms CHAR(1) character set eucjpms,
euckr CHAR(1) character set euckr,
gb2312 CHAR(1) character set gb2312,
gbk CHAR(1) character set gbk,
sjis CHAR(1) character set sjis,
ujis CHAR(1) character set ujis);
INSERT INTO tj (ucs2) VALUES (ucs2_char);
UPDATE tj SET utf8=ucs2,
big5=ucs2,
cp932=ucs2,
eucjpms=ucs2,
euckr=ucs2,
gb2312=ucs2,
gbk=ucs2,
sjis=ucs2,
ujis=ucs2;
/* If there's a conversion problem, UPDATE will produce a warning. */
SELECT hex(ucs2) AS ucs2,
hex(utf8) AS utf8,
hex(big5) AS big5,
hex(cp932) AS cp932,
hex(eucjpms) AS eucjpms,
hex(euckr) AS euckr,
hex(gb2312) AS gb2312,
hex(gbk) AS gbk,
hex(sjis) AS sjis,
hex(ujis) AS ujis
FROM tj;
DROP TABLE tj;
END//
</pre><p>
The input can be any single <code class="literal">ucs2</code>
character, or it can be the code point value (hexadecimal
representation) of that character. For example, from
Unicode's list of <code class="literal">ucs2</code> encodings and
names
(<a href="http://www.unicode.org/Public/UNIDATA/UnicodeData.txt" target="_top">http://www.unicode.org/Public/UNIDATA/UnicodeData.txt</a>),
we know that the <span class="foreignphrase"><em class="foreignphrase">Katakana</em></span>
character <span class="foreignphrase"><em class="foreignphrase">Pe</em></span> appears in all
CJK character sets, and that its code point value is
<code class="literal">0x30da</code>. If we use this value as the
argument to <code class="literal">p_convert()</code>, the result is as
shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CALL p_convert(0x30da)//</code></strong>
+------+--------+------+-------+---------+-------+--------+------+------+------+
| ucs2 | utf8 | big5 | cp932 | eucjpms | euckr | gb2312 | gbk | sjis | ujis |
+------+--------+------+-------+---------+-------+--------+------+------+------+
| 30DA | E3839A | C772 | 8379 | A5DA | ABDA | A5DA | A5DA | 8379 | A5DA |
+------+--------+------+-------+---------+-------+--------+------+------+------+
1 row in set (0.04 sec)
</pre><p>
Since none of the column values is <code class="literal">3F</code>
— that is, the question mark character
(<code class="literal">?</code>) — we know that every conversion
worked.
</p><p><a name="qandaitem-28-11-15"></a><span class="bold"><strong>28.11.15: </strong></span><span class="bold"><strong><a class="indexterm" name="id3173281"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173296"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173312"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173327"></a></strong></span><span class="bold"><strong>
Why don't CJK strings sort correctly in Unicode? (I)
</strong></span></p><p>
Sometimes people observe that the result of a
<code class="literal">utf8_unicode_ci</code> or
<code class="literal">ucs2_unicode_ci</code> search, or of an
<code class="literal">ORDER BY</code> sort is not what they think a
native would expect. Although we never rule out the
possibility that there is a bug, we have found in the past
that many people do not read correctly the standard table of
weights for the Unicode Collation Algorithm. MySQL uses the
table found at
<a href="http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt" target="_top">http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt</a>.
This is not the first table you will find by navigating from
the <code class="literal">unicode.org</code> home page, because MySQL
uses the older 4.0.0 “<span class="quote">allkeys</span>” table, rather
than the more recent 4.1.0 table. This is because we are
very wary about changing ordering which affects indexes,
lest we bring about situations such as that reported in <a href="http://bugs.mysql.com/16526" target="_top">Bug#16526</a>, illustrated as follows:
</p><pre class="programlisting">mysql< <strong class="userinput"><code>CREATE TABLE tj (s1 CHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci);</code></strong>
Query OK, 0 rows affected (0.05 sec)
mysql> <strong class="userinput"><code>INSERT INTO tj VALUES ('が'),('か');</code></strong>
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> <strong class="userinput"><code>SELECT * FROM tj WHERE s1 = 'か';</code></strong>
+------+
| s1 |
+------+
| が |
| か |
+------+
2 rows in set (0.00 sec)
</pre><p>
The character in the first result row is not the one that we
searched for. Why did MySQL retrieve it? First we look for
the Unicode code point value, which is possible by reading
the hexadecimal number for the <code class="literal">ucs2</code>
version of the characters:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT s1, HEX(CONVERT(s1 USING ucs2)) FROM tj;</code></strong>
+------+-----------------------------+
| s1 | HEX(CONVERT(s1 USING ucs2)) |
+------+-----------------------------+
| が | 304C |
| か | 304B |
+------+-----------------------------+
2 rows in set (0.03 sec)
</pre><p>
Now we search for <code class="literal">304B</code> and
<code class="literal">304C</code> in the <code class="literal">4.0.0
allkeys</code> table, and find these lines:
</p><pre class="programlisting">304B ; [.1E57.0020.000E.304B] # HIRAGANA LETTER KA
304C ; [.1E57.0020.000E.304B][.0000.0140.0002.3099] # HIRAGANA LETTER GA; QQCM
</pre><p>
The official Unicode names (following the “<span class="quote">#</span>”
mark) tell us the Japanese syllabary (Hiragana), the
informal classification (letter, digit, or punctuation
mark), and the Western identifier (<code class="literal">KA</code> or
<code class="literal">GA</code>, which happen to be voiced and
unvoiced components of the same letter pair). More
importantly, the <em class="firstterm">primary weight</em> (the
first hexadecimal number inside the square brackets) is
<code class="literal">1E57</code> on both lines. For comparisons in
both searching and sorting, MySQL pays attention to the
primary weight only, ignoring all the other numbers. This
means that we are sorting <code class="literal">が</code> and
<code class="literal">か</code> correctly according to the Unicode
specification. If we wanted to distinguish them, we'd have
to use a non-UCA (Unicode Collation Algorithm) collation
(<code class="literal">utf8_unicode_bin</code> or
<code class="literal">utf8_general_ci</code>), or to compare the
<code class="literal">HEX()</code> values, or use <code class="literal">ORDER BY
CONVERT(s1 USING sjis)</code>. Being correct
“<span class="quote">according to Unicode</span>” isn't enough, of course:
the person who submitted the bug was equally correct. We
plan to add another collation for Japanese according to the
JIS X 4061 standard, in which voiced/unvoiced letter pairs
like <code class="literal">KA</code>/<code class="literal">GA</code> are
distinguishable for ordering purposes.
</p><p><a name="qandaitem-28-11-16"></a><span class="bold"><strong>28.11.16: </strong></span><span class="bold"><strong><a class="indexterm" name="id3173602"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173617"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173633"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173648"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173663"></a></strong></span><span class="bold"><strong>
Why don't CJK strings sort correctly in Unicode? (II)
</strong></span></p><p>
If you are using Unicode (<code class="literal">ucs2</code> or
<code class="literal">utf8</code>), and you know what the Unicode sort
order is (see <a href="faqs.html#faqs-cjk" title="A.11. MySQL 5.0 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets">Section A.11, “MySQL 5.0 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets”</a>), but MySQL still
seems to sort your table incorrectly, then you should first
verify the table character set:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CREATE TABLE t\G</code></strong>
******************** 1. row ******************
Table: t
Create Table: CREATE TABLE `t` (
`s1` char(1) CHARACTER SET ucs2 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
</pre><p>
Since the character set appears to be correct, let's see
what information the
<code class="literal">INFORMATION_SCHEMA.COLUMNS</code> table can
provide about this column:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME</code></strong>
-> <strong class="userinput"><code>FROM INFORMATION_SCHEMA.COLUMNS</code></strong>
-> <strong class="userinput"><code>WHERE COLUMN_NAME = 's1'</code></strong>
-> <strong class="userinput"><code>AND TABLE_NAME = 't';</code></strong>
+-------------+--------------------+-----------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME |
+-------------+--------------------+-----------------+
| s1 | ucs2 | ucs2_general_ci |
+-------------+--------------------+-----------------+
1 row in set (0.01 sec)
</pre><p>
(See <a href="information-schema.html#columns-table" title="21.3. The INFORMATION_SCHEMA COLUMNS Table">Section 21.3, “The <code class="literal">INFORMATION_SCHEMA COLUMNS</code> Table”</a>, for more information.)
</p><p>
You can see that the collation is
<code class="literal">ucs2_general_ci</code> instead of
<code class="literal">ucs2_unicode_ci</code>. The reason why this is
so can be found using <code class="literal">SHOW CHARSET</code>, as
shown here:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW CHARSET LIKE 'ucs2%';</code></strong>
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)
</pre><p>
For <code class="literal">ucs2</code> and <code class="literal">utf8</code>, the
default collation is “<span class="quote">general</span>”. To specify a
Unicode collation, use <code class="literal">COLLATE
ucs2_unicode_ci</code>.
</p><p><a name="qandaitem-28-11-17"></a><span class="bold"><strong>28.11.17: </strong></span><span class="bold"><strong><a class="indexterm" name="id3173850"></a></strong></span><span class="bold"><strong>
Why are my supplementary characters rejected by MySQL?
</strong></span></p><p>
MySQL does not support supplementary characters — that
is, characters which need more than 3 bytes — for
<code class="literal">UTF-8</code>. We support only what Unicode calls
the <span class="emphasis"><em>Basic Multilingual Plane / Plane 0</em></span>.
Only a few very rare Han characters are supplementary;
support for them is uncommon. This has led to reports such
as that found in <a href="http://bugs.mysql.com/12600" target="_top">Bug#12600</a>, which we rejected as “<span class="quote">not
a bug</span>”. With <code class="literal">utf8</code>, we must
truncate an input string when we encounter bytes that we
don't understand. Otherwise, we wouldn't know how long the
bad multi-byte character is.
</p><p>
One possible workaround is to use <code class="literal">ucs2</code>
instead of <code class="literal">utf8</code>, in which case the
“<span class="quote">bad</span>” characters are changed to question marks;
however, no truncation takes place. You can also change the
data type to <code class="literal">BLOB</code> or
<code class="literal">BINARY</code>, which perform no validity
checking.
</p><p>
We intend at some point in the future to add support for
<code class="literal">UTF-16</code>, which would solve such issues by
allowing 4-byte characters. However, we have as yet set no
definite timetable for doing so.
</p><p><a name="qandaitem-28-11-18"></a><span class="bold"><strong>28.11.18: </strong></span><span class="bold"><strong><a class="indexterm" name="id3173970"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3173985"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174000"></a></strong></span><span class="bold"><strong>
Shouldn't it be “<span class="quote">CJKV</span>”?
</strong></span></p><p>
No. The term “<span class="quote">CJKV</span>” (<em class="firstterm">Chinese
Japanese Korean Vietnamese</em>) refers to Vietnamese
character sets which contain Han (originally Chinese)
characters. MySQL has no plan to support the old Vietnamese
script using Han characters. MySQL does of course support
the modern Vietnamese script with Western characters.
</p><p>
<a href="http://bugs.mysql.com/4745" target="_top">Bug#4745</a> is a request for a specialized Vietnamese
collation, which we might add in the future if there is
sufficient demand for it.
</p><p><a name="qandaitem-28-11-19"></a><span class="bold"><strong>28.11.19: </strong></span><span class="bold"><strong><a class="indexterm" name="id3174058"></a></strong></span><span class="bold"><strong>
Does MySQL allow CJK characters to be used in database and
table names?
</strong></span></p><p>
This issue is fixed in MySQL 5.1, by automatically rewriting
the names of the corresponding directories and files.
</p><p>
For example, if you create a database named
<code class="literal">楮</code> on a server whose operating system
does not support CJK in directory names, MySQL creates a
directory named <code class="literal">@0w@00a5@00ae</code>. which is
just a fancy way of encoding <code class="literal">E6A5AE</code>
— that is, the Unicode hexadecimal representation for
the <code class="literal">楮</code> character. However, if you run a
<code class="literal">SHOW DATABASES</code> statement, you can see
that the database is listed as <code class="literal">楮</code>.
</p><p><a name="qandaitem-28-11-20"></a><span class="bold"><strong>28.11.20: </strong></span><span class="bold"><strong><a class="indexterm" name="id3174143"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174158"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174174"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174189"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174204"></a></strong></span><span class="bold"><strong><a class="indexterm" name="id3174219"></a></strong></span><span class="bold"><strong></strong></span><span class="bold"><strong>
Where can I find translations of the MySQL Manual into
Chinese, Japanese, and Korean?
</strong></span></p><p>
A Simplified Chinese version of the Manual, current for
MySQL 5.1.12, can be found at
<a href="http://dev.mysql.com/doc/#chinese-5.1" target="_top">http://dev.mysql.com/doc/#chinese-5.1</a>. The
Japanese translation of the MySQL 4.1 manual can be
downloaded from
<a href="http://dev.mysql.com/doc/#japanese-4.1" target="_top">http://dev.mysql.com/doc/#japanese-4.1</a>.
</p><p><a name="qandaitem-28-11-21"></a><span class="bold"><strong>28.11.21: </strong></span><span class="bold"><strong>
Where can I get help with CJK and related issues in MySQL?
</strong></span></p><p>
The following resources are available:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A listing of MySQL user groups can be found at
<a href="http://dev.mysql.com/user-groups/" target="_top">http://dev.mysql.com/user-groups/</a>.
</p></li><li><p>
You can contact a sales engineer at the MySQL KK Japan
office using any of the following:
</p><pre class="programlisting">Tel: +81(0)3-5326-3133
Fax: +81(0)3-5326-3001
Email: dsaito@mysql.com
</pre><p>
</p></li><li><p>
View feature requests relating to character set issues
at <a href="http://tinyurl.com/y6xcuf" target="_top">http://tinyurl.com/y6xcuf</a>.
</p></li><li><p>
Visit the MySQL
<a href="http://forums.mysql.com/list.php?103" target="_top">Character
Sets, Collation, Unicode Forum</a>. We are also in
the process of adding foreign-language forums at
<a href="http://forums.mysql.com/" target="_top">http://forums.mysql.com/</a>.
</p></li></ul></div><p>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-connectors-apis"></a>A.12. MySQL 5.0 FAQ — Connectors & APIs</h2></div></div></div><p>
For common questions, issues, and answers relating to the MySQL
Connectors and other APIs, see the following areas of the Manual:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="apis.html#c-api-problems" title="23.2.14. Common Questions and Problems When Using the C API">Section 23.2.14, “Common Questions and Problems When Using the C API”</a>
</p></li><li><p>
<a href="apis.html#php-problems" title="23.3.1. Common Problems with MySQL and PHP">Section 23.3.1, “Common Problems with MySQL and PHP”</a>
</p></li><li><p>
<a href="connectors.html#myodbc-usagenotes" title="24.1.6. Connector/ODBC Notes and Tips">Section 24.1.6, “Connector/ODBC Notes and Tips”</a>
</p></li><li><p>
<a href="connectors.html#connector-net-using" title="24.2.5. Connector/NET Notes and Tips">Section 24.2.5, “Connector/NET Notes and Tips”</a>
</p></li><li><p>
<a href="connectors.html#connector-j-usagenotes" title="24.4.5. Connector/J Notes and Tips">Section 24.4.5, “Connector/J Notes and Tips”</a>
</p></li><li><p>
<a href="connectors.html#connector-mxj-usagenotes" title="24.5.5. Connector/MXJ Notes and Tips">Section 24.5.5, “Connector/MXJ Notes and Tips”</a>
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-replication"></a>A.13. MySQL 5.0 FAQ — Replication</h2></div></div></div><p>
For answers to common queries and question regarding Replication
within MySQL, see <a href="replication.html#replication-faq" title="15.3.4. Replication FAQ">Section 15.3.4, “Replication FAQ”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="faqs-mysql-drbd-heartbeat"></a>A.14. MySQL 5.0 FAQ — MySQL, DRBD, and Heartbeat</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="faqs.html#faqs-drbd">A.14.1. Distributed Replicated Block Device</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-linux-heartbeat">A.14.2. Linux Heartbeat</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-architecture">A.14.3. DRBD Architecture</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-mysql-replication-scale">A.14.4. DRBD and MySQL Replication</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-file-systems">A.14.5. DRBD and File Systems</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-lvm">A.14.6. DRBD and LVM</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-virtualization">A.14.7. DRBD and Virtualization</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-security">A.14.8. DRBD and Security</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-system-requirements">A.14.9. DRBD and System Requirements</a></span></dt><dt><span class="section"><a href="faqs.html#drbd-support-consulting">A.14.10. DBRD and Support and Consulting</a></span></dt></dl></div><a class="indexterm" name="id3174466"></a><a class="indexterm" name="id3174478"></a><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="faqs-drbd"></a>A.14.1. Distributed Replicated Block Device</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about Distributed Replicated Block
Device (DRBD).
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-1-1">28.14.1.1: </a><a class="indexterm" name="id3174515"></a>
What is DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-1-2">28.14.1.2: </a>
What are “<span class="quote">Block Devices</span>”?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-1-3">28.14.1.3: </a><a class="indexterm" name="id3174544"></a>
How is DRBD licensed?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-1-4">28.14.1.4: </a>
Where can I download DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-1-5">28.14.1.5: </a>
If I find a bug in DRBD, to whom do I submit the issue?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-1-6">28.14.1.6: </a>
Where can I get more technical and business information
concerning MySQL and DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-1-1"></a><span class="bold"><strong>28.14.1.1: </strong></span><span class="bold"><strong><a class="indexterm" name="id3174601"></a></strong></span><span class="bold"><strong>
What is DRBD?
</strong></span></p><p>
DRBD is an acronym for Distributed Replicated Block
Device. DRBD is an open source Linux kernel block device
which leverages synchronous replication to achieve a
consistent view of data between two systems, typically an
Active and Passive system. DRBD currently supports all the
major flavors of Linux and comes bundled in several major
Linux distributions. The DRBD project is maintained by
<a href="http://www.drbd.org/" target="_top">LINBIT</a>.
</p><p><a name="qandaitem-28-14-1-2"></a><span class="bold"><strong>28.14.1.2: </strong></span><span class="bold"><strong>
What are “<span class="quote">Block Devices</span>”?
</strong></span></p><p>
Block devices are the type of device used to represent
storage in the Linux Kernel. All physical disk devices
present a “<span class="quote">block device</span>” interface.
Additionally, virtual disk systems like LVM or DRBD
present a “<span class="quote">block device</span>” interface. In this
way, the file system or other software that might want to
access a disk device can be used with any number of real
or virtual devices without having to know anything about
their underlying implementation details.
</p><p><a name="qandaitem-28-14-1-3"></a><span class="bold"><strong>28.14.1.3: </strong></span><span class="bold"><strong><a class="indexterm" name="id3174683"></a></strong></span><span class="bold"><strong>
How is DRBD licensed?
</strong></span></p><p>
DRBD is licensed under the GPL.
</p><p><a name="qandaitem-28-14-1-4"></a><span class="bold"><strong>28.14.1.4: </strong></span><span class="bold"><strong>
Where can I download DRBD?
</strong></span></p><p>
Please see
<a href="http://www.drbd.org/download.html" target="_top">http://www.drbd.org/download.html</a>
</p><p><a name="qandaitem-28-14-1-5"></a><span class="bold"><strong>28.14.1.5: </strong></span><span class="bold"><strong>
If I find a bug in DRBD, to whom do I submit the issue?
</strong></span></p><p>
Bug reports should be submitted to the DRBD mailing list.
Please see: <a href="http://lists.linbit.com/%20" target="_top">http://lists.linbit.com/ </a>.
</p><p><a name="qandaitem-28-14-1-6"></a><span class="bold"><strong>28.14.1.6: </strong></span><span class="bold"><strong>
Where can I get more technical and business information
concerning MySQL and DRBD?
</strong></span></p><p>
Please visit: <a href="http://mysql.com/drbd/" target="_top">http://mysql.com/drbd/</a>
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-linux-heartbeat"></a>A.14.2. Linux Heartbeat</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about Linux Heartbeat.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-2-1">28.14.2.1: </a>
What is Linux Heartbeat?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-2-2">28.14.2.2: </a>
How is Linux Heartbeat licensed?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-2-3">28.14.2.3: </a>
Where can I download Linux Heartbeat?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-2-4">28.14.2.4: </a>
If I find a bug with Linux Heartbeat, to whom do I submit
the issue?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-2-1"></a><span class="bold"><strong>28.14.2.1: </strong></span><span class="bold"><strong>
What is Linux Heartbeat?
</strong></span></p><p>
The Linux-HA project
(<a href="http://www.linux-ha.org/" target="_top">http://www.linux-ha.org/</a>) offers a high
availability solution commonly referred to as Linux
Heartbeat. Linux Heartbeat ships as part of several Linux
distributions, as well as within several embedded high
availability systems. This solution can also be used for
other applications besides databases servers, such as mail
servers, web servers, file servers, and DNS servers.
</p><p>
Linux Heartbeat implements a heartbeat-protocol. A
heartbeat-protocol means that messages are sent at regular
intervals between two or more nodes. If a message is not
received from a node within a given interval, then it is
assumed the node has failed and some type of failover or
recovery action is required. Linux Heartbeat is typically
configured to send these heartbeat messages over standard
Ethernet interfaces, but it does also support other
methods, such as serial-line links.
</p><p><a name="qandaitem-28-14-2-2"></a><span class="bold"><strong>28.14.2.2: </strong></span><span class="bold"><strong>
How is Linux Heartbeat licensed?
</strong></span></p><p>
Linux Heartbeat is licensed under the GPL.
</p><p><a name="qandaitem-28-14-2-3"></a><span class="bold"><strong>28.14.2.3: </strong></span><span class="bold"><strong>
Where can I download Linux Heartbeat?
</strong></span></p><p>
Please see
<a href="http://linux-ha.org/download/index.html" target="_top">http://linux-ha.org/download/index.html</a>.
</p><p><a name="qandaitem-28-14-2-4"></a><span class="bold"><strong>28.14.2.4: </strong></span><span class="bold"><strong>
If I find a bug with Linux Heartbeat, to whom do I submit
the issue?
</strong></span></p><p>
Bug reports should be submitted to
<a href="http://www.linux-ha.org/ClusterResourceManager/BugReports" target="_top">http://www.linux-ha.org/ClusterResourceManager/BugReports</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-architecture"></a>A.14.3. DRBD Architecture</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD Architecture.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-3-1">28.14.3.1: </a>
Is an Active/Active option available for MySQL with DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-3-2">28.14.3.2: </a>
What MySQL storage engines are supported with DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-3-3">28.14.3.3: </a>
How long does a failover take?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-3-4">28.14.3.4: </a>
How long does it take to resynchronize data after a
failure?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-3-1"></a><span class="bold"><strong>28.14.3.1: </strong></span><span class="bold"><strong>
Is an Active/Active option available for MySQL with DRBD?
</strong></span></p><p>
Currently, MySQL does not support Active/Active
configurations using DRBD “<span class="quote">out of the box</span>”.
</p><p><a name="qandaitem-28-14-3-2"></a><span class="bold"><strong>28.14.3.2: </strong></span><span class="bold"><strong>
What MySQL storage engines are supported with DRBD?
</strong></span></p><p>
All of the MySQL transactional storage engines are
supported by DRBD, including InnoDB and Falcon. For
archived or read-only data, MyISAM or Archive can also be
used.
</p><p><a name="qandaitem-28-14-3-3"></a><span class="bold"><strong>28.14.3.3: </strong></span><span class="bold"><strong>
How long does a failover take?
</strong></span></p><p>
Failover time is dependent on many things, some of which
are configurable. After activating the passive host, MySQL
will have to start and run a normal recovery process. If
the InnoDB log files have been configured to a large size
and there was heavy write traffic, this may take a
reasonably long period of time. However, under normal
circumstances, failover tends to take less than a minute.
</p><p><a name="qandaitem-28-14-3-4"></a><span class="bold"><strong>28.14.3.4: </strong></span><span class="bold"><strong>
How long does it take to resynchronize data after a
failure?
</strong></span></p><p>
Resynchronization time depends on how long the two
machines are out of communication and how much data was
written during that period of time. Resynchronization time
is a function of data to be synced, network speed and disk
speed. DRBD maintains a bitmap of changed blocks on the
primary machine, so only those blocks that have changed
will need to be transferred.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-mysql-replication-scale"></a>A.14.4. DRBD and MySQL Replication</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about MySQL Replication Scale-out.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-4-1">28.14.4.1: </a>
What is the difference between MySQL Cluster and DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-4-2">28.14.4.2: </a>
What is the difference between MySQL Replication and DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-4-3">28.14.4.3: </a>
How can I combine MySQL Replication scale-out with DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-4-1"></a><span class="bold"><strong>28.14.4.1: </strong></span><span class="bold"><strong>
What is the difference between MySQL Cluster and DRBD?
</strong></span></p><p>
Both MySQL Cluster and DRBD replicate data synchronously.
MySQL Cluster leverages a shared-nothing storage
architecture in which the cluster can be architected
beyond an Active/Passive configuration. DRBD operates at a
much lower level within the “<span class="quote">stack</span>”, at the
disk I/O level. For a comparison of various high
availability features between these two options, please
refer to <a href="ha-overview.html" title="Chapter 14. High Availability, Scalability, and DRBD">Chapter 14, <i>High Availability, Scalability, and DRBD</i></a>.
</p><p><a name="qandaitem-28-14-4-2"></a><span class="bold"><strong>28.14.4.2: </strong></span><span class="bold"><strong>
What is the difference between MySQL Replication and DRBD?
</strong></span></p><p>
MySQL Replication replicates data asynchronously while
DRBD replicates data synchronously. For a comparison of
various high availability features between these two
options, please refer to the high availability comparison
grid, <a href="ha-overview.html" title="Chapter 14. High Availability, Scalability, and DRBD">Chapter 14, <i>High Availability, Scalability, and DRBD</i></a>.
</p><p><a name="qandaitem-28-14-4-3"></a><span class="bold"><strong>28.14.4.3: </strong></span><span class="bold"><strong>
How can I combine MySQL Replication scale-out with DRBD?
</strong></span></p><p>
MySQL Replication is typically deployed in a Master to
many Slaves configuration. In this configuration, having
many Slaves provides read scalability. DRBD is used to
provide high-availability for the Master MySQL Server in
an Active/Passive configuration. This provides for
automatic failover, safeguards against data loss, and
automatically synchronizes the failed MySQL Master after a
failover.
</p><p>
The most likely scenario in which MySQL Replication
scale-out can be leveraged with DRBD is in the form of
attaching replicated MySQL “<span class="quote">read-slaves</span>” off
of the Active-Master MySQL Server, shown in
<a href="faqs.html#active-master-mysql-server" title="Figure A.1. Active-Master MySQL server">Figure A.1, “Active-Master MySQL server”</a>. Since DRBD
replicates an entire block device, master information such
as the binary logs are also replicated. In this way, all
of the slaves can attach to the Virtual IP Address managed
by Linux Heartbeat. In the event of a failure, the
asynchronous nature of MySQL Replication allows the slaves
to continue with the new Active machine as their master
with no intervention needed.
</p><div class="figure"><a name="active-master-mysql-server"></a><p class="title"><b>Figure A.1. Active-Master MySQL server</b></p><div class="mediaobject"><img src="images/active-master-mysql-server.png" alt="Active-Master MySQL server"></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-file-systems"></a>A.14.5. DRBD and File Systems</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and file systems.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-5-1">28.14.5.1: </a>
Can XFS be used with DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-5-1"></a><span class="bold"><strong>28.14.5.1: </strong></span><span class="bold"><strong>
Can XFS be used with DRBD?
</strong></span></p><p>
Yes. XFS uses dynamic block size, thus DRBD 0.7 or later
is needed.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-lvm"></a>A.14.6. DRBD and LVM</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and LVM.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-6-1">28.14.6.1: </a>
Can I use DRBD on top of LVM?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-6-2">28.14.6.2: </a>
Can I use LVM on top of DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-6-3">28.14.6.3: </a>
Can I use DRBD on top of LVM while at the same time
running LVM on top of that DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-6-1"></a><span class="bold"><strong>28.14.6.1: </strong></span><span class="bold"><strong>
Can I use DRBD on top of LVM?
</strong></span></p><p>
Yes, DRBD supports on-line resizing. If you enlarge your
logical volume that acts as a backing device for DRBD, you
can enlarge DRBD itself too, and of course your file
system if it supports resizing.
</p><p><a name="qandaitem-28-14-6-2"></a><span class="bold"><strong>28.14.6.2: </strong></span><span class="bold"><strong>
Can I use LVM on top of DRBD?
</strong></span></p><p>
Yes, you can use DRBD as a Physical Volume (PV) for LVM.
Depending on the default LVM configuration shipped with
your distribution, you may need to add the
<code class="filename">/dev/drbd*</code> device files to the
<code class="literal">filter</code> option in your
<code class="filename">lvm.conf</code> so LVM scans your DRBDs for
PV signatures.
</p><p><a name="qandaitem-28-14-6-3"></a><span class="bold"><strong>28.14.6.3: </strong></span><span class="bold"><strong>
Can I use DRBD on top of LVM while at the same time
running LVM on top of that DRBD?
</strong></span></p><p>
This requires careful tuning of your LVM configuration to
avoid duplicate PV scans, but yes, it is possible.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-virtualization"></a>A.14.7. DRBD and Virtualization</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and virtualization.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-7-1">28.14.7.1: </a>
Can I use DRBD with OpenVZ?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-7-2">28.14.7.2: </a>
Can I use DRBD with Xen and/or KVM?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-7-1"></a><span class="bold"><strong>28.14.7.1: </strong></span><span class="bold"><strong>
Can I use DRBD with OpenVZ?
</strong></span></p><p>
See
<a href="http://wiki.openvz.org/HA_cluster_with_DRBD_and_Heartbeat" target="_top">http://wiki.openvz.org/HA_cluster_with_DRBD_and_Heartbeat</a>.
</p><p><a name="qandaitem-28-14-7-2"></a><span class="bold"><strong>28.14.7.2: </strong></span><span class="bold"><strong>
Can I use DRBD with Xen and/or KVM?
</strong></span></p><p>
Yes. If you are looking for professional consultancy or
expert commercial support for Xen- or KVM-based
virtualization clusters with DRBD, contact LINBIT
(<a href="http://www.linbit.com" target="_top">http://www.linbit.com</a>).
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-security"></a>A.14.8. DRBD and Security</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and security.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-8-1">28.14.8.1: </a>
Can I encrypt/compress the exchanged data?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-8-2">28.14.8.2: </a>
Does DRBD do mutual node authentication?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-8-1"></a><span class="bold"><strong>28.14.8.1: </strong></span><span class="bold"><strong>
Can I encrypt/compress the exchanged data?
</strong></span></p><p>
Yes. But there is no option within DRBD to allow for this.
You’ll need to leverage a VPN and the network layer
should do the rest.
</p><p><a name="qandaitem-28-14-8-2"></a><span class="bold"><strong>28.14.8.2: </strong></span><span class="bold"><strong>
Does DRBD do mutual node authentication?
</strong></span></p><p>
Yes, starting with DRBD 8 shared-secret mutual node
authentication is supported.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-system-requirements"></a>A.14.9. DRBD and System Requirements</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and System Requirements.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-9-1">28.14.9.1: </a>
What other packages besides DRBD are required?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-9-2">28.14.9.2: </a>
How many machines are required to set up DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-9-3">28.14.9.3: </a>
Does DRBD only run on Linux?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-9-1"></a><span class="bold"><strong>28.14.9.1: </strong></span><span class="bold"><strong>
What other packages besides DRBD are required?
</strong></span></p><p>
When using pre-built binary packages, none except a
matching kernel, plus packages for
<code class="literal">glibc</code> and your favorite shell. When
compiling DRBD from source additional prerequisite
packages may be required. They include but are not limited
to:
</p><div class="itemizedlist"><ul type="disc"><li><p>
glib-devel
</p></li><li><p>
openssl
</p></li><li><p>
devel
</p></li><li><p>
libgcrypt-devel
</p></li><li><p>
glib2-devel
</p></li><li><p>
pkgconfig
</p></li><li><p>
ncurses-devel
</p></li><li><p>
rpm-build
</p></li><li><p>
rpm-devel
</p></li><li><p>
redhat-rpm-config
</p></li><li><p>
gcc
</p></li><li><p>
gcc-c++
</p></li><li><p>
bison
</p></li><li><p>
flex
</p></li><li><p>
gnutls-devel
</p></li><li><p>
lm_sensors-devel
</p></li><li><p>
net-snmp-devel
</p></li><li><p>
python-devel
</p></li><li><p>
bzip2-devel
</p></li><li><p>
libselinux-devel
</p></li><li><p>
perl-DBI
</p></li><li><p>
libnet
</p></li></ul></div><p>
Pre-built x86 and x86_64 packages for specific kernel
versions are available with a support subscription from
LINBIT. Please note that if the kernel is upgraded, DRBD
must be as well.
</p><p><a name="qandaitem-28-14-9-2"></a><span class="bold"><strong>28.14.9.2: </strong></span><span class="bold"><strong>
How many machines are required to set up DRBD?
</strong></span></p><p>
Two machines are required to achieve the minimum degree of
high availability. Although at any one given point in time
one will be primary and one will be secondary, it is
better to consider the machines as part of a mirrored pair
without a “<span class="quote">natural</span>” primary machine.
</p><p><a name="qandaitem-28-14-9-3"></a><span class="bold"><strong>28.14.9.3: </strong></span><span class="bold"><strong>
Does DRBD only run on Linux?
</strong></span></p><p>
DRBD is a Linux Kernel Module, and can work with many
popular Linux distributions. DRBD is currently not
available for non-Linux operating systems.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="drbd-support-consulting"></a>A.14.10. DBRD and Support and Consulting</h3></div></div></div><p>
In the following section, we provide answers to questions that
are most frequently asked about DRBD and resources.
</p><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-28-14-10-1">28.14.10.1: </a>
Does MySQL offer professional consulting to help with
designing a DRBD system?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-10-2">28.14.10.2: </a>
Does MySQL offer support for DRBD and Linux Heartbeat from
MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-10-3">28.14.10.3: </a>
Are pre-built binaries or RPMs available?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-10-4">28.14.10.4: </a>
Does MySQL have documentation to help me with the
installation and configuration of DRBD and Linux
Heartbeat?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-10-5">28.14.10.5: </a>
Is there a dedicated discussion forum for MySQL
High-Availability?
</p></li><li><p><a href="faqs.html#qandaitem-28-14-10-6">28.14.10.6: </a>
Where can I get more information about MySQL for DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-28-14-10-1"></a><span class="bold"><strong>28.14.10.1: </strong></span><span class="bold"><strong>
Does MySQL offer professional consulting to help with
designing a DRBD system?
</strong></span></p><p>
Yes. MySQL offers consulting for the design, installation,
configuration, and monitoring of high availability DRBD.
For more information concerning a High Availability
Jumpstart, please see:
<a href="http://www.mysql.com/consulting/packaged/scaleout.html" target="_top">http://www.mysql.com/consulting/packaged/scaleout.html</a>.
</p><p><a name="qandaitem-28-14-10-2"></a><span class="bold"><strong>28.14.10.2: </strong></span><span class="bold"><strong>
Does MySQL offer support for DRBD and Linux Heartbeat from
MySQL?
</strong></span></p><p>
Yes. Support for DRBD is available with an add-on
subscription to MySQL Enterprise called “DRBD for
MySQL”. For more information about support options for
DRBD see:
<a href="http://mysql.com/products/enterprise/features.html" target="_top">http://mysql.com/products/enterprise/features.html</a>.
</p><p>
For the list of supported Linux distributions, please see:
<a href="http://www.mysql.com/support/supportedplatforms/enterprise.html" target="_top">http://www.mysql.com/support/supportedplatforms/enterprise.html</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
DRBD is only available on Linux. DRBD is not available
on Windows, MacOS, Solaris, HPUX, AIX, FreeBSD, or other
non-Linux platforms.
</p></div><p><a name="qandaitem-28-14-10-3"></a><span class="bold"><strong>28.14.10.3: </strong></span><span class="bold"><strong>
Are pre-built binaries or RPMs available?
</strong></span></p><p>
Yes. “<span class="quote">DRBD for MySQL</span>” is an add-on
subscription to MySQL Enterprise, which provides pre-built
binaries for DRBD. For more information see:
<a href="http://mysql.com/products/enterprise/features.html" target="_top">http://mysql.com/products/enterprise/features.html</a>.
</p><p><a name="qandaitem-28-14-10-4"></a><span class="bold"><strong>28.14.10.4: </strong></span><span class="bold"><strong>
Does MySQL have documentation to help me with the
installation and configuration of DRBD and Linux
Heartbeat?
</strong></span></p><p>
Coming soon.
</p><p><a name="qandaitem-28-14-10-5"></a><span class="bold"><strong>28.14.10.5: </strong></span><span class="bold"><strong>
Is there a dedicated discussion forum for MySQL
High-Availability?
</strong></span></p><p>
Yes, <a href="http://forums.mysql.com/list.php?144" target="_top">http://forums.mysql.com/list.php?144</a>.
</p><p><a name="qandaitem-28-14-10-6"></a><span class="bold"><strong>28.14.10.6: </strong></span><span class="bold"><strong>
Where can I get more information about MySQL for DRBD?
</strong></span></p><p>
For more information about MySQL for DRBD, including a
technical white paper please see:
<a href="http://www.mysql.com/products/enterprise/drbd.html" target="_top">DRBD
for MySQL High Availability</a>.
</p></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="extending-mysql.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="error-handling.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 26. Extending MySQL </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Appendix B. Errors, Error Codes, and Common Problems</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
9
