<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Appendix A. MySQL 5.1 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.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="extending-mysql.html" title="Chapter 22. 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.1 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.1 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.1 FAQ — General</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-storage-engines">A.2. MySQL 5.1 FAQ — Storage Engines</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-sql-modes">A.3. MySQL 5.1 FAQ — Server SQL Mode</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-stored-procs">A.4. MySQL 5.1 FAQ — Stored Procedures and Functions</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-triggers">A.5. MySQL 5.1 FAQ — Triggers</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-views">A.6. MySQL 5.1 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.1 FAQ — Migration</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-security">A.9. MySQL 5.1 FAQ — Security</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-mysql-cluster">A.10. MySQL 5.1 FAQ — MySQL Cluster</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-cjk">A.11. MySQL 5.1 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.1 FAQ — Connectors & APIs</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-replication">A.13. MySQL 5.1 FAQ — Replication</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-mysql-drbd-heartbeat">A.14. MySQL 5.1 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 (DRBD)</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.1 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-23-1-1-1">23.1.1: </a>
Which version of MySQL is production-ready (GA)?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-2">23.1.2: </a>
Can MySQL 5.1 do subqueries?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-3">23.1.3: </a>
Can MySQL 5.1 perform multiple-table inserts,
updates, and deletes?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-4">23.1.4: </a>
Does MySQL 5.1 have a Query Cache? Does it work on
Server, Instance or Database?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-5">23.1.5: </a>
Does MySQL 5.1 have Sequences?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-6">23.1.6: </a>
Does MySQL 5.1 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-23-1-1-7">23.1.7: </a>
Does MySQL 5.1 work with multi-core processors?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-8">23.1.8: </a>
Why do I see multiple processes for <code class="literal">mysqld</code>?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-9">23.1.9: </a>
Is there a hot backup tool for MyISAM like InnoDB Hot Backup?
</p></li><li><p><a href="faqs.html#qandaitem-23-1-1-10">23.1.10: </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-23-1-1-11">23.1.11: </a>
Can MySQL 5.1 perform ACID transactions?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-1-1-1"></a><span class="bold"><strong>23.1.1: </strong></span><span class="bold"><strong>
Which version of MySQL is production-ready (GA)?
</strong></span></p><p>
Currently, both MySQL 5.0 and MySQL 5.1 are supported for
production use.
</p><p>
MySQL 5.0 achieved General Availability (GA) status with MySQL
5.0.15, which was released for production use on 19 October
2005.
</p><p>
MySQL 5.1 achieved General Availability (GA) status with MySQL
5.1.30, which was released for production use on 14 November
2008.
</p><p>
Development work on MySQL 5.4 has started; currently, MySQL 5.4
is in beta status.
</p><p>
Development work on MySQL 6.0 has started; currently, MySQL 6.0
is in alpha status.
</p><p><a name="qandaitem-23-1-1-2"></a><span class="bold"><strong>23.1.2: </strong></span><span class="bold"><strong>
Can MySQL 5.1 do subqueries?
</strong></span></p><p>
Yes. See <a href="sql-syntax.html#subqueries" title="12.2.9. Subquery Syntax">Section 12.2.9, “Subquery Syntax”</a>.
</p><p><a name="qandaitem-23-1-1-3"></a><span class="bold"><strong>23.1.3: </strong></span><span class="bold"><strong>
Can MySQL 5.1 perform 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.11. UPDATE Syntax">Section 12.2.11, “<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.2. DELETE Syntax">Section 12.2.2, “<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
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statements within a
<code class="literal">BEGIN ... END</code> block. See
<a href="stored-programs-views.html#triggers" title="19.3. Using Triggers">Section 19.3, “Using Triggers”</a>.
</p><p><a name="qandaitem-23-1-1-4"></a><span class="bold"><strong>23.1.4: </strong></span><span class="bold"><strong>
Does MySQL 5.1 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="7.5.5. The MySQL Query Cache">Section 7.5.5, “The MySQL Query Cache”</a>.
</p><p><a name="qandaitem-23-1-1-5"></a><span class="bold"><strong>23.1.5: </strong></span><span class="bold"><strong>
Does MySQL 5.1 have Sequences?
</strong></span></p><p>
No. However, MySQL has an <code class="literal">AUTO_INCREMENT</code>
system, which in MySQL 5.1 can also handle inserts
in a multi-master replication setup. With the
<a href="replication.html#sysvar_auto_increment_increment"><code class="literal">auto_increment_increment</code></a> and
<a href="replication.html#sysvar_auto_increment_offset"><code class="literal">auto_increment_offset</code></a> system
variables, you can set each server to generate auto-increment
values that don't conflict with other servers. The
<a href="replication.html#sysvar_auto_increment_increment"><code class="literal">auto_increment_increment</code></a> value
should be greater than the number of servers, and each server
should have a unique offset.
</p><p><a name="qandaitem-23-1-1-6"></a><span class="bold"><strong>23.1.6: </strong></span><span class="bold"><strong>
Does MySQL 5.1 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-23-1-1-7"></a><span class="bold"><strong>23.1.7: </strong></span><span class="bold"><strong>
Does MySQL 5.1 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-23-1-1-8"></a><span class="bold"><strong>23.1.8: </strong></span><span class="bold"><strong>
Why do I see multiple processes for <code class="literal">mysqld</code>?
</strong></span></p><p>
When using LinuxThreads, you should see a minimum of three
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> processes running. These are in fact
threads. There is one thread for the LinuxThreads manager, one
thread to handle connections, and one thread to handle alarms
and signals.
</p><p><a name="qandaitem-23-1-1-9"></a><span class="bold"><strong>23.1.9: </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-23-1-1-10"></a><span class="bold"><strong>23.1.10: </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-23-1-1-11"></a><span class="bold"><strong>23.1.11: </strong></span><span class="bold"><strong>
Can MySQL 5.1 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,
nonlocking repeatable reads, and all four SQL standard isolation
levels.
</p><p>
The <code class="literal">NDB</code> storage engine supports the
<a href="sql-syntax.html#isolevel_read-committed"><code class="literal">READ COMMITTED</code></a> 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.1 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-23-2-1-1">23.2.1: </a>
Where can I obtain complete documentation for MySQL storage
engines and the pluggable storage engine architecture?
</p></li><li><p><a href="faqs.html#qandaitem-23-2-1-2">23.2.2: </a>
Do the new features in MySQL 5.1 apply to all
storage engines?
</p></li><li><p><a href="faqs.html#qandaitem-23-2-1-3">23.2.3: </a>
Have any storage engines been removed in MySQL 5.1?
</p></li><li><p><a href="faqs.html#qandaitem-23-2-1-4">23.2.4: </a>
Are there any new storage engines in MySQL 5.1?
</p></li><li><p><a href="faqs.html#qandaitem-23-2-1-5">23.2.5: </a>
What are the unique benefits of the <code class="literal">ARCHIVE</code>
storage engine?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-2-1-1"></a><span class="bold"><strong>23.2.1: </strong></span><span class="bold"><strong>
Where can I obtain complete documentation for MySQL storage
engines and the pluggable storage engine architecture?
</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 17. MySQL Cluster NDB 6.X/7.X">Chapter 17, <i>MySQL Cluster NDB 6.X/7.X</i></a>.
</p><p><a name="qandaitem-23-2-1-2"></a><span class="bold"><strong>23.2.2: </strong></span><span class="bold"><strong>
Do the new features in MySQL 5.1 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
(<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> column type), and the
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">BIT</code></a> column type, apply to all
storage engines. There are also additions and changes for
specific storage engines.
</p><p><a name="qandaitem-23-2-1-3"></a><span class="bold"><strong>23.2.3: </strong></span><span class="bold"><strong>
Have any storage engines been removed in MySQL 5.1?
</strong></span></p><p>
Yes. MySQL 5.1 no longer supports the
<code class="literal">BDB</code> storage engine. Any existing
<code class="literal">BDB</code> tables should be converted to another
storage engine before upgrading to MySQL 5.1.
</p><p><a name="qandaitem-23-2-1-4"></a><span class="bold"><strong>23.2.4: </strong></span><span class="bold"><strong>
Are there any new storage engines in MySQL 5.1?
</strong></span></p><p>
Yes, the <code class="literal">IBMDB2I</code> storage engine is
included in the IBM i5/OS (TAR packages) and IBM i5/OS (SAVF
packages). Also, there have been significant improvements in
existing storage engines, in particular for the
<code class="literal">NDB</code> storage engine that forms the
basis for MySQL Cluster.
</p><p><a name="qandaitem-23-2-1-5"></a><span class="bold"><strong>23.2.5: </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.12. The ARCHIVE Storage Engine">Section 13.12, “The <code class="literal">ARCHIVE</code> Storage Engine”</a>, for details.
</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.1 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-23-3-1-1">23.3.1: </a>
What are server SQL modes?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-2">23.3.2: </a>
Does strict mode impact performance?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-3">23.3.3: </a>
Is the mode dependent on the database or connection?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-4">23.3.4: </a>
Can the rules for strict mode be extended?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-5">23.3.5: </a>
What is the default server SQL mode when My SQL 5.1
is installed?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-6">23.3.6: </a>
How many server SQL modes are there?
</p></li><li><p><a href="faqs.html#qandaitem-23-3-1-7">23.3.7: </a>
How do you determine the server SQL mode?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-3-1-1"></a><span class="bold"><strong>23.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.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p><p><a name="qandaitem-23-3-1-2"></a><span class="bold"><strong>23.3.2: </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-23-3-1-3"></a><span class="bold"><strong>23.3.3: </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
<a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET [GLOBAL|SESSION]
sql_mode='<em class="replaceable"><code>modes</code></em>'</code></a>.
</p><p><a name="qandaitem-23-3-1-4"></a><span class="bold"><strong>23.3.4: </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
<a href="server-administration.html#sqlmode_traditional"><code class="literal">TRADITIONAL</code></a>,
<a href="server-administration.html#sqlmode_strict_trans_tables"><code class="literal">STRICT_TRANS_TABLES</code></a>, or
<a href="server-administration.html#sqlmode_strict_all_tables"><code class="literal">STRICT_ALL_TABLES</code></a> 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.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for more
information.
</p><p><a name="qandaitem-23-3-1-5"></a><span class="bold"><strong>23.3.5: </strong></span><span class="bold"><strong>
What is the default server SQL mode when My SQL 5.1
is installed?
</strong></span></p><p>
By default, no special modes are enabled. See
<a href="server-administration.html#server-sql-mode" title="5.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for information about all
available modes and MySQL's default behavior.
</p><p><a name="qandaitem-23-3-1-6"></a><span class="bold"><strong>23.3.6: </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.1.8. Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for a complete list of
available modes.
</p><p><a name="qandaitem-23-3-1-7"></a><span class="bold"><strong>23.3.7: </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 <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>
startup) with the <a href="server-administration.html#option_mysqld_sql-mode"><code class="option">--sql-mode</code></a>
option. Using the statement
<a href="sql-syntax.html#set-option" title="12.5.4. SET Syntax"><code class="literal">SET [GLOBAL|SESSION]
sql_mode='<em class="replaceable"><code>modes</code></em>'</code></a>, 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></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.1 FAQ — Stored Procedures and Functions</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-23-4-1-1">23.4.1: </a>
Are there special security requirements for using stored
procedures and functions together with replication?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-2">23.4.2: </a>
Where are stored procedures stored?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-3">23.4.3: </a>
Do MySQL 5.1 stored procedures and functions work
with replication?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-4">23.4.4: </a>
Can I commit or roll back transactions inside a stored
procedure?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-5">23.4.5: </a>
Where can I find documentation for MySQL stored procedures and
stored functions?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-6">23.4.6: </a>
Is it possible to group stored procedures or stored functions
into packages?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-7">23.4.7: </a>
Can I pass an array as input to a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-8">23.4.8: </a>
Can a stored procedure call a trigger?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-9">23.4.9: </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-23-4-1-10">23.4.10: </a>
Can a stored procedure call another stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-11">23.4.11: </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-23-4-1-12">23.4.12: </a>
What is being done to correct the aforementioned limitations?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-13">23.4.13: </a>
What limitations exist for replicating stored procedure and
function actions?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-14">23.4.14: </a>
Do stored procedures have a statement for raising application
errors?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-15">23.4.15: </a>
Can a stored procedure access tables?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-16">23.4.16: </a>
Is there a discussion forum for MySQL stored procedures?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-17">23.4.17: </a>
Do the preceding limitations affect MySQL's ability to do
point-in-time recovery?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-18">23.4.18: </a>
How do you manage stored routines?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-19">23.4.19: </a>
Can MySQL 5.1 stored routines return result sets?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-20">23.4.20: </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-23-4-1-21">23.4.21: </a>
Is <code class="literal">WITH RECOMPILE</code> supported for stored
procedures?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-22">23.4.22: </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-23-4-1-23">23.4.23: </a>
Does MySQL 5.1 support stored procedures and
functions?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-24">23.4.24: </a>
Are stored procedures and functions created on a master server
replicated to a slave?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-25">23.4.25: </a>
Where can I find the ANSI SQL 2003 specification for stored
procedures?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-26">23.4.26: </a>
How are actions that take place inside stored procedures and
functions replicated?
</p></li><li><p><a href="faqs.html#qandaitem-23-4-1-27">23.4.27: </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-23-4-1-28">23.4.28: </a>
Do stored procedures provide exception handling?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-4-1-1"></a><span class="bold"><strong>23.4.1: </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 <a href="server-administration.html#priv_super"><code class="literal">SUPER</code></a>
privilege.
</p></li><li><p>
Alternatively, a DBA can set the
<a href="server-administration.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
system variable to 1, which enables anyone with the
standard <a href="server-administration.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a>
privilege to create stored functions.
</p></li></ol></div><p>
</p><p><a name="qandaitem-23-4-1-2"></a><span class="bold"><strong>23.4.2: </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 <a href="sql-syntax.html#show-create-function" title="12.5.5.10. SHOW CREATE FUNCTION Syntax"><code class="literal">SHOW CREATE FUNCTION</code></a> to
obtain information about stored functions, and
<a href="sql-syntax.html#show-create-procedure" title="12.5.5.11. SHOW CREATE PROCEDURE Syntax"><code class="literal">SHOW CREATE PROCEDURE</code></a> to obtain
information about stored procedures. See
<a href="sql-syntax.html#show-create-procedure" title="12.5.5.11. SHOW CREATE PROCEDURE Syntax">Section 12.5.5.11, “<code class="literal">SHOW CREATE PROCEDURE</code> Syntax”</a>, for more information
about these statements.
</p><p>
You can also query the <a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a>
table in the <code class="literal">INFORMATION_SCHEMA</code> database
— see <a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table">Section 20.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>, for information
about this table.
</p><p><a name="qandaitem-23-4-1-3"></a><span class="bold"><strong>23.4.3: </strong></span><span class="bold"><strong>
Do MySQL 5.1 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-programs-views.html#stored-programs-logging" title="19.6. Binary Logging of Stored Programs">Section 19.6, “Binary Logging of Stored Programs”</a>.
</p><p><a name="qandaitem-23-4-1-4"></a><span class="bold"><strong>23.4.4: </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-23-4-1-5"></a><span class="bold"><strong>23.4.5: </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-programs-views.html#stored-routines" title="19.2. Using Stored Routines (Procedures and Functions)">Section 19.2, “Using Stored Routines (Procedures and Functions)”</a>.
</p><p><a name="qandaitem-23-4-1-6"></a><span class="bold"><strong>23.4.6: </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.1.
</p><p><a name="qandaitem-23-4-1-7"></a><span class="bold"><strong>23.4.7: </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.1.
</p><p><a name="qandaitem-23-4-1-8"></a><span class="bold"><strong>23.4.8: </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
<a href="sql-syntax.html#update" title="12.2.11. UPDATE Syntax"><code class="literal">UPDATE</code></a>, that causes a trigger to
activate.
</p><p><a name="qandaitem-23-4-1-9"></a><span class="bold"><strong>23.4.9: </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
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> 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-23-4-1-10"></a><span class="bold"><strong>23.4.10: </strong></span><span class="bold"><strong>
Can a stored procedure call another stored procedure?
</strong></span></p><p>
Yes.
</p><p><a name="qandaitem-23-4-1-11"></a><span class="bold"><strong>23.4.11: </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.1.
</p><p><a name="qandaitem-23-4-1-12"></a><span class="bold"><strong>23.4.12: </strong></span><span class="bold"><strong>
What is being done to correct the aforementioned limitations?
</strong></span></p><p>
As of MySQL 5.1.5, you can choose either statement-based
replication or row-based replication. The original replication
implementation is based on statement-based binary logging.
Row-based binary logging resolves the limitations mentioned
earlier.
</p><p>
Beginning with MySQL 5.1.8, <em class="firstterm">mixed</em>
replication is also available (by starting the server with
<a href="server-administration.html#option_mysqld_binlog-format"><code class="option">--binlog-format=mixed</code></a>). This
hybrid, “<span class="quote">smart</span>” form of replication
“<span class="quote">knows</span>” whether statement-level replication can
safely be used, or row-level replication is required.
</p><p>
For additional information, see
<a href="replication.html#replication-formats" title="16.1.2. Replication Formats">Section 16.1.2, “Replication Formats”</a>.
</p><p><a name="qandaitem-23-4-1-13"></a><span class="bold"><strong>23.4.13: </strong></span><span class="bold"><strong>
What limitations exist for replicating stored procedure and
function actions?
</strong></span></p><p>
Nondeterministic (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
<a href="server-administration.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
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, nontransactional 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-23-4-1-14"></a><span class="bold"><strong>23.4.14: </strong></span><span class="bold"><strong>
Do stored procedures have a statement for raising application
errors?
</strong></span></p><p>
Not in MySQL 5.1. The SQL standard
<a href=""><code class="literal">SIGNAL</code></a> and
<a href=""><code class="literal">RESIGNAL</code></a> statements are
implemented in MySQL 5.5.
</p><p><a name="qandaitem-23-4-1-15"></a><span class="bold"><strong>23.4.15: </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-23-4-1-16"></a><span class="bold"><strong>23.4.16: </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-23-4-1-17"></a><span class="bold"><strong>23.4.17: </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-23-4-1-18"></a><span class="bold"><strong>23.4.18: </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
<a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> table in the
<code class="literal">INFORMATION_SCHEMA</code> database (see
<a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table">Section 20.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>).
</p><p><a name="qandaitem-23-4-1-19"></a><span class="bold"><strong>23.4.19: </strong></span><span class="bold"><strong>
Can MySQL 5.1 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
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> 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-23-4-1-20"></a><span class="bold"><strong>23.4.20: </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.1, cursors are available inside stored
procedures only.
</p><p><a name="qandaitem-23-4-1-21"></a><span class="bold"><strong>23.4.21: </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.1.
</p><p><a name="qandaitem-23-4-1-22"></a><span class="bold"><strong>23.4.22: </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
<a href="information-schema.html#routines-table" title="20.14. The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">INFORMATION_SCHEMA.ROUTINES</code></a> 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="20.14. The INFORMATION_SCHEMA ROUTINES Table">Section 20.14, “The <code class="literal">INFORMATION_SCHEMA ROUTINES</code> Table”</a>.
</p><p>
The body of a stored routine can be viewed using
<a href="sql-syntax.html#show-create-function" title="12.5.5.10. SHOW CREATE FUNCTION Syntax"><code class="literal">SHOW CREATE FUNCTION</code></a> (for a
stored function) or <a href="sql-syntax.html#show-create-procedure" title="12.5.5.11. SHOW CREATE PROCEDURE Syntax"><code class="literal">SHOW CREATE
PROCEDURE</code></a> (for a stored procedure). See
<a href="sql-syntax.html#show-create-procedure" title="12.5.5.11. SHOW CREATE PROCEDURE Syntax">Section 12.5.5.11, “<code class="literal">SHOW CREATE PROCEDURE</code> Syntax”</a>, for more information.
</p><p><a name="qandaitem-23-4-1-23"></a><span class="bold"><strong>23.4.23: </strong></span><span class="bold"><strong>
Does MySQL 5.1 support stored procedures and
functions?
</strong></span></p><p>
Yes. MySQL 5.1 supports two types of stored
routines — stored procedures and stored functions.
</p><p><a name="qandaitem-23-4-1-24"></a><span class="bold"><strong>23.4.24: </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-23-4-1-25"></a><span class="bold"><strong>23.4.25: </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-23-4-1-26"></a><span class="bold"><strong>23.4.26: </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-23-4-1-27"></a><span class="bold"><strong>23.4.27: </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.1, cursors are available inside stored
procedures only. However, if you do not open a cursor on a
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a>, 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.8. SELECT Syntax">Section 12.2.8, “<code class="literal">SELECT</code> Syntax”</a>.
</p><p><a name="qandaitem-23-4-1-28"></a><span class="bold"><strong>23.4.28: </strong></span><span class="bold"><strong>
Do stored procedures provide exception handling?
</strong></span></p><p>
MySQL implements <a href="sql-syntax.html#handler" title="12.2.4. HANDLER Syntax"><code class="literal">HANDLER</code></a>
definitions according to the SQL standard. See
<a href="sql-syntax.html#declare-handler" title="12.8.4.2. DECLARE for Handlers">Section 12.8.4.2, “<code class="literal">DECLARE</code> for Handlers”</a>, for details.
</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.1 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-23-5-1-1">23.5.1: </a>
Is there a discussion forum for MySQL Triggers?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-2">23.5.2: </a>
Where are triggers stored?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-3">23.5.3: </a>
Are there any default triggers?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-4">23.5.4: </a>
Do triggers work with replication?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-5">23.5.5: </a>
Can a trigger call a stored procedure?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-6">23.5.6: </a>
Is it possible for a trigger to update tables on a remote
server?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-7">23.5.7: </a>
Where can I find the documentation for MySQL 5.1
triggers?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-8">23.5.8: </a>
Is there a way to view all triggers in a given database?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-9">23.5.9: </a>
Can triggers call an external application through a UDF?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-10">23.5.10: </a>
Does MySQL 5.1 have statement-level or row-level
triggers?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-11">23.5.11: </a>
How are triggers managed in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-12">23.5.12: </a>
How are actions carried out through triggers on a master
replicated to a slave?
</p></li><li><p><a href="faqs.html#qandaitem-23-5-1-13">23.5.13: </a>
Can triggers access tables?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-5-1-1"></a><span class="bold"><strong>23.5.1: </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-23-5-1-2"></a><span class="bold"><strong>23.5.2: </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-23-5-1-3"></a><span class="bold"><strong>23.5.3: </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 <a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns, as well
as for columns which are defined using
<code class="literal">AUTO_INCREMENT</code>.
</p><p><a name="qandaitem-23-5-1-4"></a><span class="bold"><strong>23.5.4: </strong></span><span class="bold"><strong>
Do triggers work with replication?
</strong></span></p><p>
Yes. However, the way in which they work depends whether you are
using MySQL's “<span class="quote">classic</span>” statement-based
replication available in all versions of MySQL, or the row-based
replication format introduced in MySQL 5.1.
</p><p>
When using statement-based replication, triggers on the slave
are executed by statements that are executed on the master (and
replicated to the slave).
</p><p>
When using row-based replication, triggers are not executed on
the slave due to statements that were run on the master and then
replicated to the slave. Instead, when using row-based
replication, the changes caused by executing the trigger on the
master are applied on the slave.
</p><p>
For more information, see
<a href="replication.html#replication-features-triggers" title="16.3.1.28. Replication and Triggers">Section 16.3.1.28, “Replication and Triggers”</a>.
</p><p><a name="qandaitem-23-5-1-5"></a><span class="bold"><strong>23.5.5: </strong></span><span class="bold"><strong>
Can a trigger call a stored procedure?
</strong></span></p><p>
Yes.
</p><p><a name="qandaitem-23-5-1-6"></a><span class="bold"><strong>23.5.6: </strong></span><span class="bold"><strong>
Is it 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.11. The FEDERATED Storage Engine">Section 13.11, “The <code class="literal">FEDERATED</code> Storage Engine”</a>).
</p><p><a name="qandaitem-23-5-1-7"></a><span class="bold"><strong>23.5.7: </strong></span><span class="bold"><strong>
Where can I find the documentation for MySQL 5.1
triggers?
</strong></span></p><p>
See <a href="stored-programs-views.html#triggers" title="19.3. Using Triggers">Section 19.3, “Using Triggers”</a>.
</p><p><a name="qandaitem-23-5-1-8"></a><span class="bold"><strong>23.5.8: </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
<a href="information-schema.html#triggers-table" title="20.16. The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">INFORMATION_SCHEMA.TRIGGERS</code></a> 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="20.16. The INFORMATION_SCHEMA TRIGGERS Table">Section 20.16, “The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table”</a>.
</p><p>
You can also use the <a href="sql-syntax.html#show-triggers" title="12.5.5.40. SHOW TRIGGERS Syntax"><code class="literal">SHOW
TRIGGERS</code></a> statement, which is specific to MySQL. See
<a href="sql-syntax.html#show-triggers" title="12.5.5.40. SHOW TRIGGERS Syntax">Section 12.5.5.40, “<code class="literal">SHOW TRIGGERS</code> Syntax”</a>.
</p><p><a name="qandaitem-23-5-1-9"></a><span class="bold"><strong>23.5.9: </strong></span><span class="bold"><strong>
Can triggers call an external application through a UDF?
</strong></span></p><p>
Yes. For example, a trigger could invoke the
<code class="literal">sys_exec()</code> UDF available at MySQL Forge here:
<a href="http://forge.mysql.com/projects/project.php?id=211" target="_top">http://forge.mysql.com/projects/project.php?id=211</a>
</p><p><a name="qandaitem-23-5-1-10"></a><span class="bold"><strong>23.5.10: </strong></span><span class="bold"><strong>
Does MySQL 5.1 have statement-level or row-level
triggers?
</strong></span></p><p>
In MySQL 5.1, 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.1 does not support triggers using <code class="literal">FOR
EACH STATEMENT</code>.
</p><p><a name="qandaitem-23-5-1-11"></a><span class="bold"><strong>23.5.11: </strong></span><span class="bold"><strong>
How are triggers managed in MySQL?
</strong></span></p><p>
In MySQL 5.1, triggers can be created using the
<a href="sql-syntax.html#create-trigger" title="12.1.19. CREATE TRIGGER Syntax"><code class="literal">CREATE TRIGGER</code></a> statement, and
dropped using <a href="sql-syntax.html#drop-trigger" title="12.1.30. DROP TRIGGER Syntax"><code class="literal">DROP TRIGGER</code></a>. See
<a href="sql-syntax.html#create-trigger" title="12.1.19. CREATE TRIGGER Syntax">Section 12.1.19, “<code class="literal">CREATE TRIGGER</code> Syntax”</a>, and
<a href="sql-syntax.html#drop-trigger" title="12.1.30. DROP TRIGGER Syntax">Section 12.1.30, “<code class="literal">DROP TRIGGER</code> Syntax”</a>, for more about these statements.
</p><p>
Information about triggers can be obtained by querying the
<a href="information-schema.html#triggers-table" title="20.16. The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">INFORMATION_SCHEMA.TRIGGERS</code></a> table.
See <a href="information-schema.html#triggers-table" title="20.16. The INFORMATION_SCHEMA TRIGGERS Table">Section 20.16, “The <code class="literal">INFORMATION_SCHEMA TRIGGERS</code> Table”</a>.
</p><p><a name="qandaitem-23-5-1-12"></a><span class="bold"><strong>23.5.12: </strong></span><span class="bold"><strong>
How are actions carried out through triggers on a master
replicated to a slave?
</strong></span></p><p>
Again, this depends on whether you are using statement-based or
row-based replication.
</p><p><b>Statement-based replication. </b>
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 <a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> 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 <a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement is
written to the binary log.
</p></li><li><p>
The replication slave picks up the
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> 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><p><b>Row-based replication. </b>
When you use row-based replication, the changes caused by
executing the trigger on the master are applied on the slave.
However, the triggers themselves are not actually executed on
the slave under row-based replication. This is because, if
both the master and the slave applied the changes from the
master and — in addition — the trigger causing
these changes were applied on the slave, the changes would in
effect be applied twice on the slave, leading to different
data on the master and the slave.
</p><p>
In most cases, the outcome is the same for both row-based and
statement-based replication. However, if you use different
triggers on the master and slave, you cannot use row-based
replication. (This is because the row-based format replicates
the changes made by triggers executing on the master to the
slaves, rather than the statements that caused the triggers to
execute, and the corresponding triggers on the slave are not
executed.) Instead, any statements causing such triggers to be
executed must be replicated using statement-based replication.
</p><p>
For more information, see
<a href="replication.html#replication-features-triggers" title="16.3.1.28. Replication and Triggers">Section 16.3.1.28, “Replication and Triggers”</a>.
</p><p><a name="qandaitem-23-5-1-13"></a><span class="bold"><strong>23.5.13: </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. A
trigger can also affect other tables, but it is not permitted to
modify a table that is already being used (for reading or
writing) by the statement that invoked the function or trigger.
</p></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.1 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-23-6-1-1">23.6.1: </a>
Does MySQL 5.1 have materialized views?
</p></li><li><p><a href="faqs.html#qandaitem-23-6-1-2">23.6.2: </a>
What happens to a view if an underlying table is dropped or
renamed?
</p></li><li><p><a href="faqs.html#qandaitem-23-6-1-3">23.6.3: </a>
Is there a discussion forum for MySQL Views?
</p></li><li><p><a href="faqs.html#qandaitem-23-6-1-4">23.6.4: </a>
Where can I find documentation covering MySQL Views?
</p></li><li><p><a href="faqs.html#qandaitem-23-6-1-5">23.6.5: </a>
Does MySQL 5.1 have table snapshots?
</p></li><li><p><a href="faqs.html#qandaitem-23-6-1-6">23.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-23-6-1-1"></a><span class="bold"><strong>23.6.1: </strong></span><span class="bold"><strong>
Does MySQL 5.1 have materialized views?
</strong></span></p><p>
No.
</p><p><a name="qandaitem-23-6-1-2"></a><span class="bold"><strong>23.6.2: </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
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> 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-23-6-1-3"></a><span class="bold"><strong>23.6.3: </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-23-6-1-4"></a><span class="bold"><strong>23.6.4: </strong></span><span class="bold"><strong>
Where can I find documentation covering MySQL Views?
</strong></span></p><p>
See <a href="stored-programs-views.html#views" title="19.5. Using Views">Section 19.5, “Using Views”</a>.
</p><p><a name="qandaitem-23-6-1-5"></a><span class="bold"><strong>23.6.5: </strong></span><span class="bold"><strong>
Does MySQL 5.1 have table snapshots?
</strong></span></p><p>
No.
</p><p><a name="qandaitem-23-6-1-6"></a><span class="bold"><strong>23.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
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a> statement has a column
list that makes it clear there is 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-23-7-1-1">23.7.1: </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-23-7-1-2">23.7.2: </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-23-7-1-3">23.7.3: </a>
Is there a discussion forum for
<code class="literal">INFORMATION_SCHEMA</code>?
</p></li><li><p><a href="faqs.html#qandaitem-23-7-1-4">23.7.4: </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-23-7-1-5">23.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-23-7-1-1"></a><span class="bold"><strong>23.7.1: </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-23-7-1-2"></a><span class="bold"><strong>23.7.2: </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-23-7-1-3"></a><span class="bold"><strong>23.7.3: </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-23-7-1-4"></a><span class="bold"><strong>23.7.4: </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 20. INFORMATION_SCHEMA Tables">Chapter 20, <i><code class="literal">INFORMATION_SCHEMA</code> Tables</i></a>
</p><p><a name="qandaitem-23-7-1-5"></a><span class="bold"><strong>23.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>we
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.1 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-23-8-1-1">23.8.1: </a>
How has storage engine (table type) support changed in MySQL
5.1 from previous versions?
</p></li><li><p><a href="faqs.html#qandaitem-23-8-1-2">23.8.2: </a>
Where can I find information on how to migrate from MySQL
5.0 to MySQL 5.1?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-8-1-1"></a><span class="bold"><strong>23.8.1: </strong></span><span class="bold"><strong>
How has storage engine (table type) support changed in MySQL
5.1 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 <a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> 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
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a> columns.
</p></li></ul></div><p>
</p><p><a name="qandaitem-23-8-1-2"></a><span class="bold"><strong>23.8.2: </strong></span><span class="bold"><strong>
Where can I find information on how to migrate from MySQL
5.0 to MySQL 5.1?
</strong></span></p><p>
For detailed upgrade information, see <a href="installing.html#upgrade" title="2.4.1. Upgrading MySQL">Section 2.4.1, “Upgrading MySQL”</a>.
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></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.1 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-23-9-1-1">23.9.1: </a>
Does MySQL 5.1 have built-in authentication against
LDAP directories?
</p></li><li><p><a href="faqs.html#qandaitem-23-9-1-2">23.9.2: </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-23-9-1-3">23.9.3: </a>
Does MySQL 5.1 include support for Roles Based
Access Control (RBAC)?
</p></li><li><p><a href="faqs.html#qandaitem-23-9-1-4">23.9.4: </a>
Does MySQL 5.1 have native support for SSL?
</p></li><li><p><a href="faqs.html#qandaitem-23-9-1-5">23.9.5: </a>
Where can I find documentation that addresses security issues
for MySQL?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-9-1-1"></a><span class="bold"><strong>23.9.1: </strong></span><span class="bold"><strong>
Does MySQL 5.1 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-23-9-1-2"></a><span class="bold"><strong>23.9.2: </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.1 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.5.7. Using SSL for Secure Connections">Section 5.5.7, “Using SSL for Secure Connections”</a>, for a
complete listing of supported and unsupported platforms.
</p><p><a name="qandaitem-23-9-1-3"></a><span class="bold"><strong>23.9.3: </strong></span><span class="bold"><strong>
Does MySQL 5.1 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><p><a name="qandaitem-23-9-1-4"></a><span class="bold"><strong>23.9.4: </strong></span><span class="bold"><strong>
Does MySQL 5.1 have native support for SSL?
</strong></span></p><p>
Most 5.1 binaries have support for SSL connections
between the client and server. We can't currently build with the
new YaSSL library everywhere, as it is still quite new and does
not compile on all platforms yet. See
<a href="server-administration.html#secure-connections" title="5.5.7. Using SSL for Secure Connections">Section 5.5.7, “Using SSL for 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.5.8. Connecting to MySQL Remotely from Windows with SSH">Section 5.5.8, “Connecting to MySQL Remotely from Windows with SSH”</a>.
</p><p><a name="qandaitem-23-9-1-5"></a><span class="bold"><strong>23.9.5: </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.3. General Security Issues">Section 5.3, “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.3.1. General Security Guidelines">Section 5.3.1, “General Security Guidelines”</a>.
</p></li><li><p>
<a href="server-administration.html#security-against-attack" title="5.3.2. Making MySQL Secure Against Attackers">Section 5.3.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.3.5. How to Run MySQL as a Normal User">Section 5.3.5, “How to Run MySQL as a Normal User”</a>.
</p></li><li><p>
<a href="extending-mysql.html#udf-security" title="22.3.2.6. User-Defined Function Security Precautions">Section 22.3.2.6, “User-Defined Function Security Precautions”</a>.
</p></li><li><p>
<a href="server-administration.html#privileges-options" title="5.3.3. Security-Related mysqld Options">Section 5.3.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.3.4. Security Issues with LOAD
DATA LOCAL">Section 5.3.4, “Security Issues with <code class="literal">LOAD
DATA LOCAL</code>”</a>.
</p></li><li><p>
<a href="installing.html#post-installation" title="2.13. Post-Installation Setup and Testing">Section 2.13, “Post-Installation Setup and Testing”</a>.
</p></li><li><p>
<a href="server-administration.html#secure-basics" title="5.5.7.1. Basic SSL Concepts">Section 5.5.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></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.1 FAQ — MySQL Cluster</h2></div></div></div><a class="indexterm" name="id5063381"></a><a class="indexterm" name="id5063394"></a><a class="indexterm" name="id5063406"></a><a class="indexterm" name="id5063418"></a><a class="indexterm" name="id5063427"></a><a class="indexterm" name="id5063439"></a><a class="indexterm" name="id5063452"></a><a class="indexterm" name="id5063464"></a><a class="indexterm" name="id5063476"></a><a class="indexterm" name="id5063489"></a><a class="indexterm" name="id5063501"></a><a class="indexterm" name="id5063514"></a><a class="indexterm" name="id5063526"></a><a class="indexterm" name="id5063538"></a><a class="indexterm" name="id5063551"></a><a class="indexterm" name="id5063560"></a><a class="indexterm" name="id5063572"></a><a class="indexterm" name="id5063585"></a><a class="indexterm" name="id5063597"></a><a class="indexterm" name="id5063609"></a><a class="indexterm" name="id5063622"></a><a class="indexterm" name="id5063634"></a><a class="indexterm" name="id5063647"></a><a class="indexterm" name="id5063659"></a><a class="indexterm" name="id5063671"></a><a class="indexterm" name="id5063684"></a><a class="indexterm" name="id5063696"></a><a class="indexterm" name="id5063709"></a><a class="indexterm" name="id5063721"></a><a class="indexterm" name="id5063730"></a><a class="indexterm" name="id5063742"></a><a class="indexterm" name="id5063755"></a><p>
In the following section, we answer questions that are frequently
asked about MySQL Cluster and the
<code class="literal">NDBCLUSTER</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-23-10-1-1">23.10.1: </a>
How do I handle MySQL users in a MySQL Cluster having multiple
MySQL servers?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-2">23.10.2: </a>
What do “<span class="quote">NDB</span>” and “<span class="quote">NDBCLUSTER</span>” mean?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-3">23.10.3: </a>
How much RAM do I need to use MySQL Cluster? Is it possible to
use disk memory at all?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-4">23.10.4: </a>
Is MySQL Cluster transaction-safe? What isolation levels are
supported?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-5">23.10.5: </a>
How do I back up and restore a MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-6">23.10.6: </a>
How do I start and stop MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-7">23.10.7: </a>
What is the difference between using MySQL Cluster
<span class="foreignphrase"><em class="foreignphrase">vs</em></span> using MySQL replication?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-8">23.10.8: </a>
What data types are supported by MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-9">23.10.9: </a>
What are the hardware requirements for running MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-10">23.10.10: </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-23-10-1-11">23.10.11: </a>
Can I use host names with MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-12">23.10.12: </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-23-10-1-13">23.10.13: </a>
Can I run two data nodes on a single host? Two SQL nodes?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-14">23.10.14: </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-23-10-1-15">23.10.15: </a>
Can I add data nodes to a MySQL Cluster without restarting it?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-16">23.10.16: </a>
How do I continue to send queries in the event that one of the
SQL nodes fails?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-17">23.10.17: </a>
How do cluster nodes communicate with one another?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-18">23.10.18: </a>
What happened to MySQL Cluster NDB 6.4?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-19">23.10.19: </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-23-10-1-20">23.10.20: </a>
Do I need to do any special networking to run MySQL Cluster? How
do computers in a cluster communicate?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-21">23.10.21: </a>
With which operating systems can I use Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-22">23.10.22: </a>
When I run the <code class="literal">SHOW</code> command in the MySQL
Cluster management client, I see a line of output that looks
like this:
</p><pre class="programlisting">id=2 @10.100.10.32 (Version: 5.1.41, Nodegroup: 0, Master)
</pre><p>
What is a “<span class="quote">master node</span>”, and what does it do? How
do I configure a node so that it is the master?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-23">23.10.23: </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-23-10-1-24">23.10.24: </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-23-10-1-25">23.10.25: </a>
Can I run multiple nodes on a single computer?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-26">23.10.26: </a>
Are there any limitations that I should be aware of when using
MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-27">23.10.27: </a>
What is an “<span class="quote">angel process</span>”?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-28">23.10.28: </a>
What happens to MySQL Cluster data when the cluster is shut
down?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-29">23.10.29: </a>
Is it a good idea to have more than one management node for a
MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-30">23.10.30: </a>
What do the different computers do in a MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-31">23.10.31: </a>
Is it possible to use <code class="literal">FULLTEXT</code> indexes with
MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-32">23.10.32: </a>
I am trying to populate a MySQL Cluster database. The loading
process terminates prematurely and I get an error message like
this one:
<code class="literal"><span class="errortext">ERROR 1114: The table 'my_cluster_table' is
full</span></code>
Why is this happening?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-33">23.10.33: </a>
What storage engines are supported by MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-34">23.10.34: </a>
How do I import an existing MySQL database into a MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-35">23.10.35: </a>
Does MySQL Cluster support IPv6?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-36">23.10.36: </a>
Can I mix different kinds of hardware and operating systems in
one MySQL Cluster?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-37">23.10.37: </a>
How many computers do I need to run a MySQL Cluster, and why?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-38">23.10.38: </a>
What file systems can I use with MySQL Cluster? What about
network file systems or network shares?
</p></li><li><p><a href="faqs.html#qandaitem-23-10-1-39">23.10.39: </a>
What is an <em class="firstterm">arbitrator</em>?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-10-1-1"></a><span class="bold"><strong>23.10.1: </strong></span><span class="bold"><strong>
How do I handle MySQL users in a MySQL Cluster having multiple
MySQL servers?
</strong></span></p><p>
MySQL user accounts and privileges are <span class="emphasis"><em>not</em></span>
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. You can do
this manually, or automate the task with scripts.
</p><div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Warning</h3><p>
Do not attempt to work around this issue by converting the
MySQL system tables to use the
<code class="literal">NDBCLUSTER</code> storage engine. Only
the <code class="literal">MyISAM</code> storage engine is
supported for these tables.
</p></div><p><a name="qandaitem-23-10-1-2"></a><span class="bold"><strong>23.10.2: </strong></span><span class="bold"><strong>
What do “<span class="quote">NDB</span>” and “<span class="quote">NDBCLUSTER</span>” mean?
</strong></span></p><p>
“<span class="quote">NDB</span>” 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> and
<code class="literal">NDBCLUSTER</code> are both names for the
storage engine that enables clustering support in MySQL. Either
name is equally correct; both names appear in our documentation,
and either name can be used in the <code class="literal">ENGINE</code>
option of a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statement for creating a MySQL Cluster table.
</p><p><a name="qandaitem-23-10-1-3"></a><span class="bold"><strong>23.10.3: </strong></span><span class="bold"><strong>
How much RAM do I need to use MySQL Cluster? Is it possible to
use disk memory at all?
</strong></span></p><p>
In MySQL 5.1, Cluster is in-memory only. This means
that all table data (including indexes) is stored in RAM.
Therefore, if your data takes up 1 GB of space and you want to
replicate it once in the cluster, you need 2 GB 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 and later but we have no plans to add this capability in
MySQL 5.1. See
<a href="mysql-cluster.html#mysql-cluster-disk-data" title="17.5.9. MySQL Cluster Disk Data Tables">Section 17.5.9, “MySQL Cluster Disk Data Tables”</a>, for more
information.
</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><p>
When calculating Cluster memory requirements, you may find
useful the <span><strong class="command">ndb_size.pl</strong></span> utility which is
available in recent MySQL 5.1 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-programs-ndb-size-pl" title="17.4.21. ndb_size.pl — NDBCLUSTER Size Requirement Estimator">Section 17.4.21, “<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; 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-23-10-1-4"></a><span class="bold"><strong>23.10.4: </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. Currently, MySQL Cluster supports only the
<a href="sql-syntax.html#isolevel_read-committed"><code class="literal">READ COMMITTED</code></a> transaction
isolation level.
</p><p><a name="qandaitem-23-10-1-5"></a><span class="bold"><strong>23.10.5: </strong></span><span class="bold"><strong>
How do I back up and restore a MySQL Cluster?
</strong></span></p><p>
You can use the NDB native backup and restore functionality in
the MySQL Cluster management client and the
<span><strong class="command">ndb_restore</strong></span> program. See
<a href="mysql-cluster.html#mysql-cluster-backup" title="17.5.3. Online Backup of MySQL Cluster">Section 17.5.3, “Online Backup of MySQL Cluster”</a>, and
<a href="mysql-cluster.html#mysql-cluster-programs-ndb-restore" title="17.4.17. ndb_restore — Restore a MySQL Cluster Backup">Section 17.4.17, “<span><strong class="command">ndb_restore</strong></span> — Restore a MySQL Cluster Backup”</a>.
</p><p>
You can also use the traditional functionality provided for this
purpose in <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> and the MySQL server.
See <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program">Section 4.5.4, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>, for more information.
</p><p><a name="qandaitem-23-10-1-6"></a><span class="bold"><strong>23.10.6: </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, using the
<span><strong class="command">ndb_mgmd</strong></span> command.
</p><p>
You must include the <code class="option">-f</code> or
<a href="mysql-cluster.html#option_ndb_mgmd_config-file"><code class="option">--config-file</code></a> option to
tell the management node where its configuration file can be
found.
</p></li><li><p>
Start each data node with the <span><strong class="command">ndbd</strong></span>
command.
</p><p>
Each data node must be started with the
<a href="mysql-cluster.html#option_ndb_common_connect-string"><code class="option">-c</code></a>
or <a href="mysql-cluster.html#option_ndb_common_connect-string"><code class="option">--connect-string</code></a>
option so that the data node knows how to connect to the
management server.
</p></li><li><p>
Start each MySQL Server (SQL node) using your preferred
startup script, such as <a href="programs.html#mysqld-safe" title="4.3.2. mysqld_safe — MySQL Server Startup Script"><span><strong class="command">mysqld_safe</strong></span></a>.
</p><p>
Each MySQL Server must be started with the
<a href="mysql-cluster.html#option_mysqld_ndbcluster"><code class="option">--ndbcluster</code></a> and
<a href="mysql-cluster.html#option_mysqld_ndb-connectstring"><code class="option">--ndb-connectstring</code></a> options.
These options cause mysqld to enable
<code class="literal">NDBCLUSTER</code> storage engine
support and how to connect to the management server.
</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 <code class="literal">NDB</code>
management client <span><strong class="command">ndb_mgm</strong></span> on the machine
housing the management 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 management client.
Alternatively, you may enter the following command in a system
shell:
</p><pre class="programlisting">shell> <strong class="userinput"><code>ndb_mgm -e "SHUTDOWN"</code></strong>
</pre><p>
(The quotation marks in this example are optional, since there
are no spaces in the command string following the
<code class="option">-e</code> option; in addition, the
<code class="literal">SHUTDOWN</code> command, like other management
client commands, 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 SQL
nodes can be stopped using <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin
shutdown</strong></span></a>.
</p><p>
For more information, see
<a href="mysql-cluster.html#mysql-cluster-mgm-client-commands" title="17.5.2. Commands in the MySQL Cluster Management Client">Section 17.5.2, “Commands in the MySQL Cluster Management Client”</a>, and
<a href="mysql-cluster.html#mysql-cluster-multi-shutdown-restart" title="17.2.5. Safe Shutdown and Restart of MySQL Cluster">Section 17.2.5, “Safe Shutdown and Restart of MySQL Cluster”</a>.
</p><p><a name="qandaitem-23-10-1-7"></a><span class="bold"><strong>23.10.7: </strong></span><span class="bold"><strong>
What is the difference between using MySQL Cluster
<span class="foreignphrase"><em class="foreignphrase">vs</em></span> using MySQL replication?
</strong></span></p><p>
In traditional MySQL replication, 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
<em class="firstterm">asynchronous</em>, MySQL Cluster is
<em class="firstterm">synchronous</em>.
</p><p>
We have implemented (asynchronous) replication for Cluster in
MySQL 5.1 and later. <em class="firstterm">MySQL Cluster
Replication</em> (also sometimes known as
“<span class="quote">geo-replication</span>”) includes the capability to
replicate both between two MySQL Clusters, and from a MySQL
Cluster to a non-Cluster MySQL server. However, we do
<span class="emphasis"><em>not</em></span> plan to backport this functionality to
MySQL 5.1. See
<a href="mysql-cluster.html#mysql-cluster-replication" title="17.6. MySQL Cluster Replication">Section 17.6, “MySQL Cluster Replication”</a>.
</p><p><a name="qandaitem-23-10-1-8"></a><span class="bold"><strong>23.10.8: </strong></span><span class="bold"><strong>
What data types are supported by MySQL Cluster?
</strong></span></p><p><a name="qandaitem-23-10-1-9"></a><span class="bold"><strong>23.10.9: </strong></span><span class="bold"><strong>
What are the hardware requirements for running MySQL Cluster?
</strong></span></p><p>
MySQL Cluster should run on any platform for which
<code class="literal">NDB</code>-enabled binaries are available.
For data nodes and API nodes, faster CPUs and more memory are
likely to improve performance, and 64-bit CPUs are likely to 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). For a computer which is
used only for running the MySQL Cluster management server, the
requirements are minimal; a common desktop PC (or the
equivalent) is generally sufficient for this task. Nodes can
communicate via the standard TCP/IP network and hardware. They
can also use the high-speed SCI protocol; however, special
networking hardware and software are required to use SCI (see
<a href="mysql-cluster.html#mysql-cluster-interconnects" title="17.3.5. Using High-Speed Interconnects with MySQL Cluster">Section 17.3.5, “Using High-Speed Interconnects with MySQL Cluster”</a>).
</p><p><a name="qandaitem-23-10-1-10"></a><span class="bold"><strong>23.10.10: </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 an 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-23-10-1-11"></a><span class="bold"><strong>23.10.11: </strong></span><span class="bold"><strong>
Can I use host names 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, you should use fixed (numeric) IP addresses, since
making communication between Cluster hosts dependent on services
such as DNS and DHCP introduces additional potential points of
failure.
</p><p><a name="qandaitem-23-10-1-12"></a><span class="bold"><strong>23.10.12: </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>
Beginning with MySQL 5.1.24, MySQL Cluster is no longer
supported in standard MySQL Server 5.1 releases. Instead, MySQL
Cluster is now released as a separate product. Currently, three
MySQL Cluster release series are available:
</p><div class="itemizedlist"><ul type="disc"><li><p><b>MySQL Cluster NDB 6.2. </b>
This series is Generally Available (GA) and can be used in
production. However, MySQL Cluster NDB 6.3 and 7.0 are
also now available as GA, so you are encouraged to upgrade
existing deployments and to use one of these for new MySQL
Cluster deployments. The latest MySQL Cluster NDB 6.2
sources and binaries can be obtained from
<a href="http://dev.mysql.com/downloads/cluster6.2.html" target="_top">http://dev.mysql.com/downloads/cluster6.2.html</a>.
</p></li><li><p><b>MySQL Cluster NDB 6.3. </b>
This series is now Generally Available (GA) for use in
production. The latest MySQL Cluster NDB 6.3 sources and
binaries can be obtained from
<a href="http://dev.mysql.com/downloads/cluster" target="_top">http://dev.mysql.com/downloads/cluster</a>.
</p></li><li><p><b>MySQL Cluster NDB 7.0. </b>
This series is now Generally Available (GA) for use in
production. The latest MySQL Cluster NDB 7.0 sources can
be obtained from
<a href="http://dev.mysql.com/downloads/cluster" target="_top">http://dev.mysql.com/downloads/cluster</a>.
</p></li></ul></div><p>
However, you should use MySQL NDB Cluster NDB 6.3 or 7.0 for new
deployments, and if you are already using MySQL 5.1
with clustering support, to upgrade to one of these MySQL
Cluster release series. For an overview of improvements made in
MySQL Cluster NDB 6.2, 6.3, and 7.0, see
<a href="mysql-cluster.html#mysql-cluster-changes-5-1-ndb-6-2" title="17.1.4.3. Features Added in MySQL Cluster NDB 6.2">Section 17.1.4.3, “Features Added in MySQL Cluster NDB 6.2”</a>,
<a href="mysql-cluster.html#mysql-cluster-changes-5-1-ndb-6-3" title="17.1.4.4. Features Added in MySQL Cluster NDB 6.3">Section 17.1.4.4, “Features Added in MySQL Cluster NDB 6.3”</a>, and
<a href="mysql-cluster.html#mysql-cluster-changes-5-1-ndb-7-0" title="17.1.4.5. Features Added in MySQL Cluster NDB 7.0">Section 17.1.4.5, “Features Added in MySQL Cluster NDB 7.0”</a>,
respectively.
</p><p>
You can determine whether your MySQL Server has
<code class="literal">NDBCLUSTER</code> support using either of
the statements <code class="literal">SHOW VARIABLES LIKE 'have_%'</code>
or <a href="sql-syntax.html#show-engines" title="12.5.5.17. SHOW ENGINES Syntax"><code class="literal">SHOW ENGINES</code></a>.
</p><p><a name="qandaitem-23-10-1-13"></a><span class="bold"><strong>23.10.13: </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 <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> must
use a different TCP/IP port. However, in MySQL 5.1,
running more than one cluster node of a given type per machine
is generally not encouraged or supported for production use.
</p><p>
We also advise against running data nodes and SQL nodes together
on the same host, since the <span><strong class="command">ndbd</strong></span> and
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> processes may compete for memory.
</p><p><a name="qandaitem-23-10-1-14"></a><span class="bold"><strong>23.10.14: </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-23-10-1-15"></a><span class="bold"><strong>23.10.15: </strong></span><span class="bold"><strong>
Can I add data nodes to a MySQL Cluster without restarting it?
</strong></span></p><p>
Beginning with MySQL Cluster NDB 6.4, it is possible to add new
data nodes to a running MySQL Cluster without taking it offline.
For more information, see
<a href="mysql-cluster.html#mysql-cluster-online-add-node" title="17.5.10. Adding MySQL Cluster Data Nodes Online">Section 17.5.10, “Adding MySQL Cluster Data Nodes Online”</a>.
</p><p>
For other types of MySQL Cluster nodes, a rolling restart is all
that is required (see
<a href="mysql-cluster.html#mysql-cluster-rolling-restart" title="17.2.6.1. Performing a Rolling Restart of a MySQL Cluster">Section 17.2.6.1, “Performing a Rolling Restart of a MySQL Cluster”</a>).
</p><p>
In MySQL Cluster NDB 6.3 and earlier, it was not possible to add
new data nodes without shutting down and restarting the MySQL
Cluster.
</p><p><a name="qandaitem-23-10-1-16"></a><span class="bold"><strong>23.10.16: </strong></span><span class="bold"><strong>
How do I continue to send queries in the event that one of the
SQL nodes fails?
</strong></span></p><p>
MySQL Cluster does not provide any sort of automatic failover
between SQL nodes. Your application must be prepared to
handlethe loss of SQL nodes and to fail over between them.
</p><p><a name="qandaitem-23-10-1-17"></a><span class="bold"><strong>23.10.17: </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
transport mechanisms: 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. 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="17.3.5. Using High-Speed Interconnects with MySQL Cluster">Section 17.3.5, “Using High-Speed Interconnects with MySQL Cluster”</a>, for more about
using SCI as a transport mechanism for MySQL Cluster.
</p><p><a name="qandaitem-23-10-1-18"></a><span class="bold"><strong>23.10.18: </strong></span><span class="bold"><strong>
What happened to MySQL Cluster NDB 6.4?
</strong></span></p><p>
Because of the number and impact of new features introduced into
MySQL Cluster following the General Availability of MySQL
Cluster NDB 6.3, it was decided that the following release
series represented a “<span class="quote">major</span>” new release series
rather than a “<span class="quote">minor</span>” one, and should be known as
MySQL Cluster 7.0.
</p><p>
The earliest development versions of MySQL Cluster NDB 7.0 were
originally designated “<span class="quote">MySQL Cluster 6.4</span>”, and the
first four releases in this series were identified as MySQL
Cluster NDB 6.4.0 through NDB 6.4.3. MySQL Cluster NDB 7.0.4 is
the fifth MySQL Cluster NDB 7.0 release; it is the successor to
MySQL Cluster NDB 6.4.3.
</p><p>
For more information about MySQL Cluster NDB 7.0, see
<a href="mysql-cluster.html#mysql-cluster-changes-5-1-ndb-7-0" title="17.1.4.5. Features Added in MySQL Cluster NDB 7.0">Section 17.1.4.5, “Features Added in MySQL Cluster NDB 7.0”</a>, and
<a href="mysql-cluster.html#mysql-cluster-news-7-0" title="17.7.1.2. Changes in MySQL Cluster NDB 7.0">Section 17.7.1.2, “Changes in MySQL Cluster NDB 7.0”</a>.
</p><p><a name="qandaitem-23-10-1-19"></a><span class="bold"><strong>23.10.19: </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>
<span class="emphasis"><em>No</em></span>. Although some specialized commands are
used to manage and configure the cluster itself, only standard
(My)SQL statements 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="17.3.2. MySQL Cluster Configuration Files">Section 17.3.2, “MySQL Cluster Configuration Files”</a>, for information
about these.
</p><p>
A few simple commands are used in the MySQL Cluster management
client (<span><strong class="command">ndb_mgm</strong></span>) for tasks such as starting
and stopping cluster nodes. See
<a href="mysql-cluster.html#mysql-cluster-mgm-client-commands" title="17.5.2. Commands in the MySQL Cluster Management Client">Section 17.5.2, “Commands in the MySQL Cluster Management Client”</a>.
</p><p><a name="qandaitem-23-10-1-20"></a><span class="bold"><strong>23.10.20: </strong></span><span class="bold"><strong>
Do I need to do any special networking to run MySQL 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 MySQL 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="17.3.5. Using High-Speed Interconnects with MySQL Cluster">Section 17.3.5, “Using High-Speed Interconnects with MySQL Cluster”</a>, for
more information about SCI.
</p><p><a name="qandaitem-23-10-1-21"></a><span class="bold"><strong>23.10.21: </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, and Solaris. Beginning with MySQL
Cluster NDB 6.4, it is also possible to run MySQL Cluster on
Windows platforms on an experimental basis; we hope to release a
GA version for Windows in MySQL Cluster NDB 7.1.
</p><p>
We are continuing to work on providing MySQL Cluster support for
additional platforms; eventually we intend 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-23-10-1-22"></a><span class="bold"><strong>23.10.22: </strong></span><span class="bold"><strong>
When I run the <code class="literal">SHOW</code> command in the MySQL
Cluster management client, I see a line of output that looks
like this:
</strong></span></p><pre class="programlisting">id=2 @10.100.10.32 (Version: 5.1.41, Nodegroup: 0, Master)
</pre><p><span class="bold"><strong>
What is a “<span class="quote">master node</span>”, and what does it do? How
do I configure a node so that it is the master?
</strong></span></p><p>
The simplest answer is, “<span class="quote">It's not something you can
control, and it's nothing that you need to worry about in
any case, unless you're a software engineer writing or
analyzing the MySQL Cluster source code</span>”.
</p><p>
If you don't find that answer satisfactory, here's a
longer and more technical version:
</p><p>
A number of mechanisms in MySQL Cluster require distributed
coordination among the data nodes. These distributed algorithms
and protocols include global checkpointing, DDL (schema)
changes, and node restart handling. To make this coordination
simpler, the data nodes “<span class="quote">elect</span>” one of their number
to be a “<span class="quote">master</span>”. There is no user-facing mechanism
for influencing this selection, which is is completely
automatic; the fact that it <span class="emphasis"><em>is</em></span> automatic is
a key part of MySQL Cluster's internal architecture.
</p><p>
When a node acts as a master for any of these mechanisms, it is
usually the point of coordination for the activity, and the
other nodes act as “<span class="quote">servants</span>”, carrying out their
parts of the activity as directed by the master. If the node
acting as master fails, then the remaining nodes elect a new
master. Tasks in progress that were being coordinated by the old
master may either fail or be continued by the new master,
depending on the actual mechanism involved.
</p><p>
It is possible for some of these different mechanisms and
protocols to have different master nodes, but in general the
same master is chosen for all of them. The node indicated as the
master in the output of <code class="literal">SHOW</code> in the
management client is actually the <code class="literal">DICT</code> master
(see <a href="http://dev.mysql.com/doc/ndbapi/en/ndb-internals-kernel-blocks-dbdict.html" target="_top">The <code class="literal">DBDICT</code> Block</a>, in
the <em class="citetitle">MySQL Cluster API Developer Guide</em>,
for more information), responsible for coordinating DDL and
metadata activity.
</p><p>
MySQL Cluster is designed in such a way that the choice of
master has no discernable effect outside the cluster itself. For
example, the current master does not have significantly higher
CPU or resource usage than the other data nodes, and failure of
the master should not have a significantly different impact on
the cluster than the failure of any other data node.
</p><p><a name="qandaitem-23-10-1-23"></a><span class="bold"><strong>23.10.23: </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><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.) For more
information, see <a href="mysql-cluster.html#mysql-cluster-security" title="17.5.8. MySQL Cluster Security Issues">Section 17.5.8, “MySQL Cluster Security Issues”</a>.
</p><p><a name="qandaitem-23-10-1-24"></a><span class="bold"><strong>23.10.24: </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><p>
From within the <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> 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.
</p></li><li><p>
From a system shell prompt, use <a href="programs.html#perror" title="4.8.1. perror — Explain Error Codes"><span><strong class="command">perror --ndb
<em class="replaceable"><code>error_code</code></em></strong></span></a>.
</p></li></ul></div><p><a name="qandaitem-23-10-1-25"></a><span class="bold"><strong>23.10.25: </strong></span><span class="bold"><strong>
Can I run multiple nodes on a single computer?
</strong></span></p><p>
It is possible but not always advisable. One of the chief
reasons to run a cluster is to provide redundancy. To obtain 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. For
this reason, if you do run multiple data nodes on a single
machine, it is <span class="emphasis"><em>extremely</em></span> important that
they be set up in such a way that the failure of this machine
does not cause the loss of all the data nodes in a given node
group.
</p><p>
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 300 MHz Pentium or
equivalent 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 that has multiple CPUs, cores, or both. Beginning with
MySQL Cluster NDB 6.4, there is also a special multi-threaded
version of the data node binary intended for use on such
systems. For more information, see
<a href="mysql-cluster.html#mysql-cluster-programs-ndbmtd" title="17.4.3. ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)">Section 17.4.3, “<span><strong class="command">ndbmtd</strong></span> — The MySQL Cluster Data Node Daemon (Multi-Threaded)”</a>.
</p><p>
It is also possible in some cases to run data nodes and SQL
nodes concurrently on the same machine; how well such an
arrangement performs is dependent on a number of factors such as
number of cores and CPUs as well as the amount of disk and
memory available to the data node and SQL node processes, and
you must take these factors into account when planning such a
configuration.
</p><p><a name="qandaitem-23-10-1-26"></a><span class="bold"><strong>23.10.26: </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.1 (including MySQL Cluster NDB 6.x) include the
following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Temporary tables are not supported; a
<a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE
TEMPORARY TABLE</code></a> statement using
<code class="literal">ENGINE=NDB</code> or
<code class="literal">ENGINE=NDBCLUSTER</code> fails with an error.
</p></li><li><p>
The only types of user-defined partitioning supported for
<code class="literal">NDBCLUSTER</code> tables are
<code class="literal">KEY</code> and <code class="literal">LINEAR KEY</code>.
(Beginning with MySQL 5.1.12, attempting to create an
<code class="literal">NDB</code> table using any other
partitioning type fails with an error.)
</p></li><li><p>
<code class="literal">FULLTEXT</code> indexes are not supported.
</p></li><li><p>
Index prefixes are not supported. Only complete columns may
be indexed.
</p></li><li><p>
Spatial indexes are not supported (although spatial columns
can be used). See <a href="functions.html#spatial-extensions" title="11.13. Spatial Extensions">Section 11.13, “Spatial Extensions”</a>.
</p></li><li><p>
Prior to MySQL Cluster NDB 6.3.19, the
<code class="literal">NDBCLUSTER</code> storage engine did
not support partial transactions or partial rollbacks of
transactions. Beginning with MySQL Cluster NDB 6.3.19, this
limitation has been removed, and the behavior of
<code class="literal">NDBCLUSTER</code> is now in line with
that of other transactional storage engines (such as
<code class="literal">InnoDB</code>) that can roll back
individual statements.
</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 <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> 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">NDBCLUSTER</code> engine does not
support foreign key constraints. As with
<code class="literal">MyISAM</code> tables, if these are
specified in a <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
or <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> statement,
they 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="17.1.5. Known Limitations of MySQL Cluster">Section 17.1.5, “Known Limitations of MySQL Cluster”</a>. For information
about limitations in MySQL Cluster 5.0 that are
lifted in MySQL 5.1, MySQL Cluster NDB 6.x, or
MySQL Cluster NDB 7.0, see
<a href="mysql-cluster.html#mysql-cluster-limitations-resolved" title="17.1.5.11. Previous MySQL Cluster Issues Resolved in MySQL 5.1 and MySQL Cluster
NDB 6.x">Section 17.1.5.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1 and MySQL Cluster
NDB 6.x”</a>.
</p><p><a name="qandaitem-23-10-1-27"></a><span class="bold"><strong>23.10.27: </strong></span><span class="bold"><strong>
What is an “<span class="quote">angel process</span>”?
</strong></span></p><p>
This process monitors and, if necessary, attempts to restart the
data node process. If you check the list of active processes on
your system after starting <span><strong class="command">ndbd</strong></span>, you can see
that there are actually 2 processes running by that name, as
shown here (we omit the output from <span><strong class="command">ndb_mgmd</strong></span>
and <span><strong class="command">ndbd</strong></span> for brevity):
</p><pre class="programlisting">shell> <strong class="userinput"><code>./ndb_mgmd</code></strong>
shell> <strong class="userinput"><code>ps aux | grep ndb</code></strong>
me 23002 0.0 0.0 122948 3104 ? Ssl 14:14 0:00 ./ndb_mgmd
me 23025 0.0 0.0 5284 820 pts/2 S+ 14:14 0:00 grep ndb
shell> <strong class="userinput"><code>./ndbd -c 127.0.0.1 --initial</code></strong>
shell> <strong class="userinput"><code>ps aux | grep ndb</code></strong>
me 23002 0.0 0.0 123080 3356 ? Ssl 14:14 0:00 ./ndb_mgmd
me 23096 0.0 0.0 35876 2036 ? Ss 14:14 0:00 ./ndbd -c 127.0.0.1 --initial
me 23097 1.0 2.4 524116 91096 ? Sl 14:14 0:00 ./ndbd -c 127.0.0.1 --initial
me 23168 0.0 0.0 5284 812 pts/2 R+ 14:15 0:00 grep ndb
</pre><p>
The <span><strong class="command">ndbd</strong></span> process showing 0 memory and CPU
usage is the angel process. It actually does use a very small
amount of each, of course. It simply checks to see if the main
<span><strong class="command">ndbd</strong></span> process (the primary data node process
that actually handles the data) is running. If permitted to do
so (for example, if the <code class="literal">StopOnError</code>
configuration parameter is set to false — see
<a href="mysql-cluster.html#mysql-cluster-params-ndbd" title="17.3.3.1. MySQL Cluster Data Node Configuration Parameters">Section 17.3.3.1, “MySQL Cluster Data Node Configuration Parameters”</a>), the angel process
tries to restart the primary data node process.
</p><p><a name="qandaitem-23-10-1-28"></a><span class="bold"><strong>23.10.28: </strong></span><span class="bold"><strong>
What happens to MySQL 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-23-10-1-29"></a><span class="bold"><strong>23.10.29: </strong></span><span class="bold"><strong>
Is it a good idea to have more than one management node for a
MySQL Cluster?
</strong></span></p><p>
It can be helpful as a fail-safe. Only one management node
controls the cluster at any given time, but it is possible to
configure one management node as primary, and one or more
additional management nodes to take over in the event that the
primary management node fails.
</p><p>
See <a href="mysql-cluster.html#mysql-cluster-config-file" title="17.3.2. MySQL Cluster Configuration Files">Section 17.3.2, “MySQL Cluster Configuration Files”</a>, for information
on how to configure MySQL Cluster management nodes.
</p><p><a name="qandaitem-23-10-1-30"></a><span class="bold"><strong>23.10.30: </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><div class="itemizedlist"><ul type="disc"><li><p><b>Management node. </b>
This node 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 is <span><strong class="command">ndb_mgm</strong></span>. See
<a href="mysql-cluster.html#mysql-cluster-programs-ndb-mgmd" title="17.4.4. ndb_mgmd — The MySQL Cluster Management Server Daemon">Section 17.4.4, “<span><strong class="command">ndb_mgmd</strong></span> — The MySQL Cluster Management Server Daemon”</a>, and
<a href="mysql-cluster.html#mysql-cluster-programs-ndb-mgm" title="17.4.5. ndb_mgm — The MySQL Cluster Management Client">Section 17.4.5, “<span><strong class="command">ndb_mgm</strong></span> — The MySQL Cluster Management Client”</a>, for
information about these programs.
</p></li><li><p><b>Data node. </b>
This type of node stores and replicates data. Data node
functionality is handled by instances of the
<code class="literal">NDB</code> data node process
<span><strong class="command">ndbd</strong></span>. For more information, see
<a href="mysql-cluster.html#mysql-cluster-programs-ndbd" title="17.4.2. ndbd — The MySQL Cluster Data Node Daemon">Section 17.4.2, “<span><strong class="command">ndbd</strong></span> — The MySQL Cluster Data Node Daemon”</a>.
</p></li><li><p><b>SQL node. </b>
This is simply an instance of MySQL Server
(<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>) that is built with support for
the <code class="literal">NDBCLUSTER</code> storage engine
and started with the <span><strong class="command">--ndb-cluster</strong></span>
option to enable the engine and the
<code class="literal">--ndb-connectstring</code> option to enable it
to connect to a MySQL Cluster management server. For more
about these options, see
<a href="mysql-cluster.html#mysql-cluster-program-options-mysqld" title="17.3.4.2. mysqld Command Options for MySQL Cluster">Section 17.3.4.2, “<span><strong class="command">mysqld</strong></span> Command Options for MySQL Cluster”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
An <em class="firstterm">API node</em> is any application that
makes direct use of Cluster data nodes for data storage
and retrieval. An SQL node can thus be considered a type
of API node that uses a MySQL Server to provide an SQL
interface to the Cluster. You can write such applications
(that do not depend on a MySQL Server) using the NDB API,
which supplies a direct, object-oriented transaction and
scanning interface to MySQL Cluster data; see
<a href="http://dev.mysql.com/doc/ndbapi/en/overview-ndb-api.html" target="_top">The NDB API</a>, for more information.
</p></div></li></ul></div><p><a name="qandaitem-23-10-1-31"></a><span class="bold"><strong>23.10.31: </strong></span><span class="bold"><strong>
Is it possible to use <code class="literal">FULLTEXT</code> indexes with
MySQL Cluster?
</strong></span></p><p>
<code class="literal">FULLTEXT</code> indexing is not supported by any
storage engine other than <code class="literal">MyISAM</code>.
We are working to add this capability to MySQL Cluster tables in
a future release.
</p><p><a name="qandaitem-23-10-1-32"></a><span class="bold"><strong>23.10.32: </strong></span><span class="bold"><strong>
I am trying to populate a MySQL Cluster database. The loading
process terminates prematurely and I get an error message like
this one:
</strong></span><span class="bold"><strong>
<code class="literal"><span class="errortext">ERROR 1114: The table 'my_cluster_table' is
full</span></code>
</strong></span><span class="bold"><strong>
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. For example, 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 at most 1GB to MySQL Cluster
data and indexes.
</p><p><a name="qandaitem-23-10-1-33"></a><span class="bold"><strong>23.10.33: </strong></span><span class="bold"><strong>
What storage engines are supported by MySQL Cluster?
</strong></span></p><p>
Clustering with 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 MySQL Cluster,
the table must be created using <code class="literal">ENGINE=NDB</code>
(or the equivalent option <code class="literal">ENGINE=NDBCLUSTER</code>).
</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 with a MySQL Cluster, but these
non-<code class="literal">NDB</code> tables do
<span class="emphasis"><em>not</em></span> participate in clustering; each such
table is strictly local to the individual MySQL server instance
on which it is created.
</p><p><a name="qandaitem-23-10-1-34"></a><span class="bold"><strong>23.10.34: </strong></span><span class="bold"><strong>
How do I import an existing MySQL database into a MySQL 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, 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 that use other
storage engines to <code class="literal">NDBCLUSTER</code> using
one or more <a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a>
statement. However, the definition of the table must be
compatible with the <code class="literal">NDBCLUSTER</code>
storage engine prior to making the conversion. In MySQL
5.1, an additional workaround is also required; see
<a href="mysql-cluster.html#mysql-cluster-limitations" title="17.1.5. Known Limitations of MySQL Cluster">Section 17.1.5, “Known Limitations of MySQL Cluster”</a>, for details.
</p><p><a name="qandaitem-23-10-1-35"></a><span class="bold"><strong>23.10.35: </strong></span><span class="bold"><strong>
Does MySQL Cluster support IPv6?
</strong></span></p><p>
Beginning with MySQL Cluster NDB 6.4, IPv6 is supported for
connections between SQL nodes (MySQL servers). However,
connections between all other types of nodes must use IPv4.
</p><p>
In practical terms, this means that you can use IPv6 for
replication between MySQL Clusters, but connections between
nodes in the same MySQL Cluster must use IPv4. For more
information, see
<a href="mysql-cluster.html#mysql-cluster-replication-issues" title="17.6.3. Known Issues in MySQL Cluster Replication">Section 17.6.3, “Known Issues in MySQL Cluster Replication”</a>.
</p><p><a name="qandaitem-23-10-1-36"></a><span class="bold"><strong>23.10.36: </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, as long as all machines and operating systems have the same
“<span class="quote">endianness</span>” (all big-endian or all little-endian).
We are working to overcome this limitation in a future MySQL
Cluster release.
</p><p>
It is also possible to use software from different MySQL Cluster
releases on different nodes. However, we support this only as
part of a rolling upgrade procedure (see
<a href="mysql-cluster.html#mysql-cluster-rolling-restart" title="17.2.6.1. Performing a Rolling Restart of a MySQL Cluster">Section 17.2.6.1, “Performing a Rolling Restart of a MySQL Cluster”</a>).
</p><p><a name="qandaitem-23-10-1-37"></a><span class="bold"><strong>23.10.37: </strong></span><span class="bold"><strong>
How many computers do I need to run a MySQL Cluster, and why?
</strong></span></p><p>
A minimum of three computers is required to run a viable
cluster. However, the minimum <span class="emphasis"><em>recommended</em></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-23-10-1-38"></a><span class="bold"><strong>23.10.38: </strong></span><span class="bold"><strong>
What file systems can I use with MySQL Cluster? What about
network file systems or network shares?
</strong></span></p><p>
Generally, any file system that is native to the host operating
system should work well with MySQL Cluster. If you find that a
given file system 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>
For Windows, we recommend that you use <code class="literal">NTFS</code>
file systems for MySQL Cluster, just as we do for standard
MySQL. We do not test MySQL Cluster with <code class="literal">FAT</code>
or <code class="literal">VFAT</code> file systems. Because of this, we do
not recommend their use with MySQL or 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 file systems is not
supported for MySQL Cluster. This also applies to shared storage
devices such as SANs.
</p><p><a name="qandaitem-23-10-1-39"></a><span class="bold"><strong>23.10.39: </strong></span><span class="bold"><strong>
What is an <em class="firstterm">arbitrator</em>?
</strong></span></p><p>
If one or more data nodes in a cluster fail, it is possible that
not all cluster data nodes will be able to “<span class="quote">see</span>”
one another. In fact, it is possible that two sets of data 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 data nodes tries to behave as though it is the entire
cluster. An arbitrator is required to decide between the
competing sets of data nodes.
</p><p>
When all data nodes in at least one node group are alive,
network partitioning is not an issue, because no single subset
of the cluster can form a functional cluster on its own. 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. In MySQL Cluster NDB 7.0.7 and later, you can also use
the <code class="literal">ArbitrationRank</code> configuration parameter
to control the arbitrator selection process. For more
information about these parameters, see
<a href="mysql-cluster.html#mysql-cluster-mgm-definition" title="17.3.2.5. Defining a MySQL Cluster Management Server">Section 17.3.2.5, “Defining a MySQL Cluster Management Server”</a>.
</p><p>
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></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.1 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets</h2></div></div></div><a class="indexterm" name="id5066862"></a><a class="indexterm" name="id5066875"></a><a class="indexterm" name="id5066887"></a><a class="indexterm" name="id5066900"></a><a class="indexterm" name="id5066913"></a><a class="indexterm" name="id5066926"></a><a class="indexterm" name="id5066938"></a><a class="indexterm" name="id5066951"></a><a class="indexterm" name="id5066964"></a><a class="indexterm" name="id5066973"></a><a class="indexterm" name="id5066985"></a><a class="indexterm" name="id5066998"></a><a class="indexterm" name="id5067011"></a><a class="indexterm" name="id5067020"></a><a class="indexterm" name="id5067032"></a><a class="indexterm" name="id5067045"></a><a class="indexterm" name="id5067057"></a><a class="indexterm" name="id5067070"></a><a class="indexterm" name="id5067082"></a><a class="indexterm" name="id5067091"></a><a class="indexterm" name="id5067104"></a><a class="indexterm" name="id5067117"></a><a class="indexterm" name="id5067126"></a><a class="indexterm" name="id5067138"></a><a class="indexterm" name="id5067151"></a><a class="indexterm" name="id5067160"></a><a class="indexterm" name="id5067173"></a><a class="indexterm" name="id5067185"></a><a class="indexterm" name="id5067198"></a><a class="indexterm" name="id5067210"></a><a class="indexterm" name="id5067223"></a><a class="indexterm" name="id5067236"></a><a class="indexterm" name="id5067248"></a><a class="indexterm" name="id5067261"></a><a class="indexterm" name="id5067273"></a><a class="indexterm" name="id5067286"></a><a class="indexterm" name="id5067298"></a><a class="indexterm" name="id5067311"></a><a class="indexterm" name="id5067323"></a><a class="indexterm" name="id5067336"></a><a class="indexterm" name="id5067348"></a><a class="indexterm" name="id5067361"></a><a class="indexterm" name="id5067374"></a><a class="indexterm" name="id5067386"></a><a class="indexterm" name="id5067399"></a><a class="indexterm" name="id5067408"></a><a class="indexterm" name="id5067420"></a><a class="indexterm" name="id5067433"></a><a class="indexterm" name="id5067446"></a><a class="indexterm" name="id5067458"></a><a class="indexterm" name="id5067470"></a><a class="indexterm" name="id5067483"></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><p><span class="bold"><strong>Questions</strong></span></p><div class="itemizedlist"><ul type="disc"><li><p><a href="faqs.html#qandaitem-23-11-1-1">23.11.1: </a>
I've upgraded to MySQL 5.1. 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-23-11-1-2">23.11.2: </a>
Why do I get <span class="errortext">Data truncated</span> error
messages?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-3">23.11.3: </a>
Why do some <a href="functions.html#operator_like"><code class="literal">LIKE</code></a> and
<code class="literal">FULLTEXT</code> searches with CJK characters fail?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-4">23.11.4: </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-23-11-1-5">23.11.5: </a>
Why do Japanese character set conversions fail?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-6">23.11.6: </a>
Why are my supplementary characters rejected by MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-7">23.11.7: </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-23-11-1-8">23.11.8: </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-23-11-1-9">23.11.9: </a>
I have inserted CJK characters into my table. Why does
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> display them as
“<span class="quote">?</span>” characters?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-10">23.11.10: </a>
How does MySQL represent the Yen (<code class="literal">¥</code>) sign?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-11">23.11.11: </a>
Why don't CJK strings sort correctly in Unicode? (I)
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-12">23.11.12: </a>
Does MySQL allow CJK characters to be used in database and table
names?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-13">23.11.13: </a>
Shouldn't it be “<span class="quote">CJKV</span>”?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-14">23.11.14: </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-23-11-1-15">23.11.15: </a>
What CJK character sets are available in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-16">23.11.16: </a>
Why don't CJK strings sort correctly in Unicode? (II)
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-17">23.11.17: </a>
Where can I get help with CJK and related issues in MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-18">23.11.18: </a>
Where can I find translations of the MySQL Manual into Chinese,
Japanese, and Korean?
</p></li><li><p><a href="faqs.html#qandaitem-23-11-1-19">23.11.19: </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-23-11-1-20">23.11.20: </a>
Of what issues should I be aware when working with Korean
character sets in MySQL?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-11-1-1"></a><span class="bold"><strong>23.11.1: </strong></span><span class="bold"><strong>
I've upgraded to MySQL 5.1. 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
<a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>,
<a href="server-administration.html#sysvar_character_set_results"><code class="literal">character_set_results</code></a>, and
<a href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a>
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 <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with
<a href="server-administration.html#option_mysqld_character-set-server"><code class="option">--character-set-server=utf8</code></a>.
However, some of our Asian customers have said that they prefer
the MySQL 4.0 behavior. To make it possible to retain this
behavior, we added a <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> switch,
<a href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--character-set-client-handshake</code></a>,
which can be turned off with
<a href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>.
If you start <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with
<a href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>,
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
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a>:
</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
<a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin</strong></span></a>. 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
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a>, the server
ignores the client's initial settings if the
<a href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>
is used.
</p><p><a name="qandaitem-23-11-1-2"></a><span class="bold"><strong>23.11.2: </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 is a warning. Use the following statement to 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 is 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 is 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-23-11-1-3"></a><span class="bold"><strong>23.11.3: </strong></span><span class="bold"><strong>
Why do some <a href="functions.html#operator_like"><code class="literal">LIKE</code></a> and
<code class="literal">FULLTEXT</code> searches with CJK characters fail?
</strong></span></p><p>
There is a very simple problem with
<a href="functions.html#operator_like"><code class="literal">LIKE</code></a> searches on
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a> and
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> 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 <a href="functions.html#operator_like"><code class="literal">LIKE
'_A%'</code></a> 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-23-11-1-4"></a><span class="bold"><strong>23.11.4: </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
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> client (Windows:
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql.exe</strong></span></a>), and try the same query there. If
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> responds correctly, then the trouble
may be that your application interface requires initialization.
Use <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> 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-apis.html#connector-odbc-configuration" title="21.1.4. Connector/ODBC Configuration">Section 21.1.4, “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-apis.html#connector-net-programming-connecting" title="21.2.5.1. Connecting to MySQL Using Connector/NET">Section 21.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
<a href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a> and
<a href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a> and
<a href="server-administration.html#sysvar_character_set_results"><code class="literal">character_set_results</code></a>.
</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-apis.html#connector-j-reference-charsets" title="21.3.4.4. Using Character Sets and Unicode">Section 21.3.4.4, “Using Character Sets and Unicode”</a>.
</p><p><a name="qandaitem-23-11-1-5"></a><span class="bold"><strong>23.11.5: </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 <a href="functions.html#function_convert"><code class="literal">CONVERT(ucs2)</code></a>
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 the following 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-23-11-1-6"></a><span class="bold"><strong>23.11.6: </strong></span><span class="bold"><strong>
Why are my supplementary characters rejected by MySQL?
</strong></span></p><p>
Before MySQL 6.0.4, 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 <a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a> or
<a href="data-types.html#binary-varbinary" title="10.4.2. The BINARY and
VARBINARY Types"><code class="literal">BINARY</code></a>, which perform no validity
checking.
</p><p>
As of MySQL 6.0.4, Unicode support is extended to include
supplementary characters by means of additional Unicode
character sets: <code class="literal">utf16</code>,
<code class="literal">utf32</code>, and 4-byte <code class="literal">utf8</code>.
These character sets support supplementary Unicode characters
outside the Basic Multilingual Plane (BMP).
</p><p><a name="qandaitem-23-11-1-7"></a><span class="bold"><strong>23.11.7: </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-23-11-1-8"></a><span class="bold"><strong>23.11.8: </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-23-11-1-9"></a><span class="bold"><strong>23.11.9: </strong></span><span class="bold"><strong>
I have inserted CJK characters into my table. Why does
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> 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 <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> client program (on Windows:
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql.exe</strong></span></a>) to accomplish this task. If
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> 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
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a> 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 is 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 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-23-11-1-10"></a><span class="bold"><strong>23.11.10: </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-23-11-1-11"></a><span class="bold"><strong>23.11.11: </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_bin</code> or
<code class="literal">utf8_general_ci</code>), or to compare the
<a href="functions.html#function_hex"><code class="literal">HEX()</code></a> 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-23-11-1-12"></a><span class="bold"><strong>23.11.12: </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
<a href="sql-syntax.html#show-databases" title="12.5.5.15. SHOW DATABASES Syntax"><code class="literal">SHOW DATABASES</code></a> statement, you can
see that the database is listed as <code class="literal">楮</code>.
</p><p><a name="qandaitem-23-11-1-13"></a><span class="bold"><strong>23.11.13: </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-23-11-1-14"></a><span class="bold"><strong>23.11.14: </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 6.0.
</p><p><a name="qandaitem-23-11-1-15"></a><span class="bold"><strong>23.11.15: </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="http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html#news-5-0-3" target="_top">Changes in MySQL 5.0.3</a>). However, since the name of the
applicable language appears in the
<code class="literal">DESCRIPTION</code> column for every entry in the
<a href="information-schema.html#character-sets-table" title="20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table"><code class="literal">INFORMATION_SCHEMA.CHARACTER_SETS</code></a>
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="20.9. The INFORMATION_SCHEMA CHARACTER_SETS Table">Section 20.9, “The <code class="literal">INFORMATION_SCHEMA CHARACTER_SETS</code> Table”</a>, for more
information.)
</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>, or <span class="emphasis"><em>Simplified Chinese</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://www.collation-charts.org/mysql60/by-charset.html#gb2312" target="_top">gb2312</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://www.collation-charts.org/mysql60/by-charset.html#gbk" target="_top">gbk</a>.
</p></li></ul></div><p>
</p><p><a name="qandaitem-23-11-1-16"></a><span class="bold"><strong>23.11.16: </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.1 FAQ — MySQL Chinese, Japanese, and Korean
Character Sets">Section A.11, “MySQL 5.1 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
<a href="information-schema.html#columns-table" title="20.3. The INFORMATION_SCHEMA COLUMNS Table"><code class="literal">INFORMATION_SCHEMA.COLUMNS</code></a> 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="20.3. The INFORMATION_SCHEMA COLUMNS Table">Section 20.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-23-11-1-17"></a><span class="bold"><strong>23.11.17: </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><p><a name="qandaitem-23-11-1-18"></a><span class="bold"><strong>23.11.18: </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/" target="_top">http://dev.mysql.com/doc/</a>. The Japanese
translation of the MySQL 4.1 manual can be downloaded from
<a href="http://dev.mysql.com/doc/" target="_top">http://dev.mysql.com/doc/</a>.
</p><p><a name="qandaitem-23-11-1-19"></a><span class="bold"><strong>23.11.19: </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 nonhalfwidth
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 is 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-23-11-1-20"></a><span class="bold"><strong>23.11.20: </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://www.collation-charts.org/mysql60/by-charset.html#euckr" target="_top">euckr</a>.
</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.1 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="connectors-apis.html#c-api-problems" title="21.9.10. Common Questions and Problems When Using the C API">Section 21.9.10, “Common Questions and Problems When Using the C API”</a>
</p></li><li><p>
<a href="connectors-apis.html#apis-php-problems" title="21.10.6. Common Problems with MySQL and PHP">Section 21.10.6, “Common Problems with MySQL and PHP”</a>
</p></li><li><p>
<a href="connectors-apis.html#connector-odbc-usagenotes" title="21.1.7. Connector/ODBC Notes and Tips">Section 21.1.7, “Connector/ODBC Notes and Tips”</a>
</p></li><li><p>
<a href="connectors-apis.html#connector-net-programming" title="21.2.5. Connector/NET Programming">Section 21.2.5, “Connector/NET Programming”</a>
</p></li><li><p>
<a href="connectors-apis.html#connector-j-usagenotes" title="21.3.5. Connector/J Notes and Tips">Section 21.3.5, “Connector/J Notes and Tips”</a>
</p></li><li><p>
<a href="connectors-apis.html#connector-mxj-usagenotes" title="21.4.6. Connector/MXJ Notes and Tips">Section 21.4.6, “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.1 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="16.3.4. Replication FAQ">Section 16.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.1 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 (DRBD)</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="id5071437"></a><a class="indexterm" name="id5071450"></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 (DRBD)</h3></div></div></div><a class="indexterm" name="id5071467"></a><a class="indexterm" name="id5071476"></a><a class="indexterm" name="id5071488"></a><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-23-14-1-1-1">23.14.1.1: </a>
How is DRBD licensed?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-1-1-2">23.14.1.2: </a>
Where can I download DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-1-1-3">23.14.1.3: </a>
If I find a bug in DRBD, to whom do I submit the issue?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-1-1-4">23.14.1.4: </a>
What are “<span class="quote">Block Devices</span>”?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-1-1-5">23.14.1.5: </a>
What is DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-1-1-6">23.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-23-14-1-1-1"></a><span class="bold"><strong>23.14.1.1: </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-23-14-1-1-2"></a><span class="bold"><strong>23.14.1.2: </strong></span><span class="bold"><strong>
Where can I download DRBD?
</strong></span></p><p>
Please see
<a href="http://www.drbd.org/download/packages/" target="_top">http://www.drbd.org/download/packages/</a>.
</p><p><a name="qandaitem-23-14-1-1-3"></a><span class="bold"><strong>23.14.1.3: </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/" target="_top">http://lists.linbit.com/</a>.
</p><p><a name="qandaitem-23-14-1-1-4"></a><span class="bold"><strong>23.14.1.4: </strong></span><span class="bold"><strong>
What are “<span class="quote">Block Devices</span>”?
</strong></span></p><p>
A <em class="firstterm">block device</em> is the type of device used
to represent storage in the Linux Kernel. All physical disk
devices present a block device interface. Additionally, virtual
disk systems like LVM or DRBD present a block device 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-23-14-1-1-5"></a><span class="bold"><strong>23.14.1.5: </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-23-14-1-1-6"></a><span class="bold"><strong>23.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-23-14-2-1-1">23.14.2.1: </a>
Where can I download Linux Heartbeat?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-2-1-2">23.14.2.2: </a>
How is Linux Heartbeat licensed?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-2-1-3">23.14.2.3: </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-23-14-2-1-1"></a><span class="bold"><strong>23.14.2.1: </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-23-14-2-1-2"></a><span class="bold"><strong>23.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-23-14-2-1-3"></a><span class="bold"><strong>23.14.2.3: </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-23-14-3-1-1">23.14.3.1: </a>
How long does a failover take?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-2">23.14.3.2: </a>
How long does it take to resynchronize data after a failure?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-3">23.14.3.3: </a>
Are there any situations where you shouldn't use DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-4">23.14.3.4: </a>
Where can I find more information on sample architectures?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-5">23.14.3.5: </a>
Is an Active/Active option available for MySQL with DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-6">23.14.3.6: </a>
Are there any limitations to DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-3-1-7">23.14.3.7: </a>
What MySQL storage engines are supported with DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-14-3-1-1"></a><span class="bold"><strong>23.14.3.1: </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-23-14-3-1-2"></a><span class="bold"><strong>23.14.3.2: </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><p><a name="qandaitem-23-14-3-1-3"></a><span class="bold"><strong>23.14.3.3: </strong></span><span class="bold"><strong>
Are there any situations where you shouldn't use DRBD?
</strong></span></p><p>
See
<a href="http://fghaas.wordpress.com/2007/06/26/when-not-to-use-drbd/" target="_top">When
Not To Use DRBD</a>.
</p><p><a name="qandaitem-23-14-3-1-4"></a><span class="bold"><strong>23.14.3.4: </strong></span><span class="bold"><strong>
Where can I find more information on sample architectures?
</strong></span></p><p>
For an example of a Heartbeat R1-compatible resource
configuration involving a MySQL database backed by DRBD, see
<a href="http://www.drbd.org/users-guide/s-heartbeat-r1.html" target="_top">DRBD
User's Guide</a>.
</p><p>
For an example of the same DRBD-backed configuration for a MySQL
database in a Heartbeat CRM cluster, see
<a href="http://www.drbd.org/users-guide/s-heartbeat-crm.html" target="_top">DRBD
User's Guide</a>.
</p><p><a name="qandaitem-23-14-3-1-5"></a><span class="bold"><strong>23.14.3.5: </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-23-14-3-1-6"></a><span class="bold"><strong>23.14.3.6: </strong></span><span class="bold"><strong>
Are there any limitations to DRBD?
</strong></span></p><p>
See
<a href="http://fghaas.wordpress.com/2007/06/18/drbd-limitations-or-are-they/" target="_top">DRBD
limitations (or are they?)</a>.
</p><p><a name="qandaitem-23-14-3-1-7"></a><span class="bold"><strong>23.14.3.7: </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></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-23-14-4-1-1">23.14.4.1: </a>
What is the difference between MySQL Replication and DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-4-1-2">23.14.4.2: </a>
How can I combine MySQL Replication scale-out with DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-4-1-3">23.14.4.3: </a>
What is the difference between MySQL Cluster and DRBD?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-14-4-1-1"></a><span class="bold"><strong>23.14.4.1: </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. Also, MySQL Replication
replicates MySQL statements, while DRBD replicates the
underlying block device that stores the MySQL data files. 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 and Scalability">Chapter 14, <i>High Availability and Scalability</i></a>.
</p><p><a name="qandaitem-23-14-4-1-2"></a><span class="bold"><strong>23.14.4.2: </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. 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="id5072190"></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" width="550" height="328" alt="Active-Master MySQL server"></div></div><p><a name="qandaitem-23-14-4-1-3"></a><span class="bold"><strong>23.14.4.3: </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 and Scalability">Chapter 14, <i>High Availability and Scalability</i></a>.
</p></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-23-14-5-1-1">23.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-23-14-5-1-1"></a><span class="bold"><strong>23.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-23-14-6-1-1">23.14.6.1: </a>
Can I use DRBD on top of LVM?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-6-1-2">23.14.6.2: </a>
Can I use LVM on top of DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-6-1-3">23.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-23-14-6-1-1"></a><span class="bold"><strong>23.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-23-14-6-1-2"></a><span class="bold"><strong>23.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-23-14-6-1-3"></a><span class="bold"><strong>23.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-23-14-7-1-1">23.14.7.1: </a>
Can I use DRBD with Xen and/or KVM?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-7-1-2">23.14.7.2: </a>
Can I use DRBD with OpenVZ?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-14-7-1-1"></a><span class="bold"><strong>23.14.7.1: </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><p><a name="qandaitem-23-14-7-1-2"></a><span class="bold"><strong>23.14.7.2: </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></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-23-14-8-1-1">23.14.8.1: </a>
Does DRBD do mutual node authentication?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-8-1-2">23.14.8.2: </a>
Can I encrypt/compress the exchanged data?
</p></li></ul></div><p><span class="bold"><strong>Questions and Answers</strong></span></p><p><a name="qandaitem-23-14-8-1-1"></a><span class="bold"><strong>23.14.8.1: </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><p><a name="qandaitem-23-14-8-1-2"></a><span class="bold"><strong>23.14.8.2: </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></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-23-14-9-1-1">23.14.9.1: </a>
What other packages besides DRBD are required?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-9-1-2">23.14.9.2: </a>
How many machines are required to set up DRBD?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-9-1-3">23.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-23-14-9-1-1"></a><span class="bold"><strong>23.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-23-14-9-1-2"></a><span class="bold"><strong>23.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-23-14-9-1-3"></a><span class="bold"><strong>23.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-23-14-10-1-1">23.14.10.1: </a>
Does MySQL offer support for DRBD and Linux Heartbeat from
MySQL?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-10-1-2">23.14.10.2: </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-23-14-10-1-3">23.14.10.3: </a>
Does MySQL offer professional consulting to help with designing
a DRBD system?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-10-1-4">23.14.10.4: </a>
Are pre-built binaries or RPMs available?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-10-1-5">23.14.10.5: </a>
Is there a dedicated discussion forum for MySQL
High-Availability?
</p></li><li><p><a href="faqs.html#qandaitem-23-14-10-1-6">23.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-23-14-10-1-1"></a><span class="bold"><strong>23.14.10.1: </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-23-14-10-1-2"></a><span class="bold"><strong>23.14.10.2: </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>
For MySQL-specific DRBD documentation, see
<a href="ha-overview.html#ha-drbd" title="14.1. Using MySQL with DRBD">Section 14.1, “Using MySQL with DRBD”</a>.
</p><p>
For general DRBD documentation, see
<a href="http://www.drbd.org/users-guide/" target="_top">DRBD User's
Guide</a>.
</p><p><a name="qandaitem-23-14-10-1-3"></a><span class="bold"><strong>23.14.10.3: </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-23-14-10-1-4"></a><span class="bold"><strong>23.14.10.4: </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-23-14-10-1-5"></a><span class="bold"><strong>23.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-23-14-10-1-6"></a><span class="bold"><strong>23.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 22. 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>
