| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | Date: | October 23 2006 6:29am |
| Subject: | svn commit - mysqldoc@docsrva: r3699 - trunk/refman-5.1 | ||
| View as plain text | |||
Author: jstephens Date: 2006-10-23 06:29:21 +0200 (Mon, 23 Oct 2006) New Revision: 3699 Log: Adding 5.1 FAQ chapter file. (Uses 5.1 version of existing Cluster FAQ.) Added: trunk/refman-5.1/faqs.xml Added: trunk/refman-5.1/faqs.xml =================================================================== --- trunk/refman-5.1/faqs.xml (rev 0) +++ trunk/refman-5.1/faqs.xml 2006-10-23 04:29:21 UTC (rev 3699) Changed blocks: 1, Lines Added: 5322, Lines Deleted: 0; 172957 bytes @@ -0,0 +1,5322 @@ +<?xml version="1.0" encoding="utf-8"?> +<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN" +"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" +[ + <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent"> + %fixedchars.entities; + <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent"> + %urls.entities; + <!ENTITY % versions.entities SYSTEM "versions.ent"> + %versions.entities; +]> +<chapter id="faqs"> + + <title>MySQL ¤t-series; Technical FAQ</title> + + <section id="faqs-general"> + + <title>MySQL ¤t-series; FAQ — General</title> + + <qandaset defaultlabel="qanda"> + + <qandaentry id="faqs-general-production"> + + <question> + + <para> + When did MySQL ¤t-series; become production-ready + (GA)? + </para> + + </question> + + <answer> + + <para> + MySQL 5.0.15 was released for production use on 19 October + 2005. We are now working on MySQL 5.1, which is currently in + beta. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-subqueries"> + + <question> + + <para> + Can MySQL ¤t-series; do subqueries? + </para> + + </question> + + <answer> + + <para> + Yes. MySQL supports subqueries since version 4.1 See + <xref linkend="subqueries"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-multi-table"> + + <question> + + <para> + Can MySQL ¤t-series; do multi-table inserts and + updates? + </para> + + </question> + + <answer> + + <para> + Yes. Multi-table <literal>UPDATE</literal> and + <literal>DELETE</literal> were actually implemented in MySQL + 4.0, with enhancements added in MySQL 4.1. See + <xref + linkend="update"/>, and + <xref linkend="delete"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-query-cache"> + + <question> + + <para> + Does MySQL ¤t-series; have a Query Cache? Does it work + on Server, Instance or Database? + </para> + + </question> + + <answer> + + <para> + Yes. The Query Cache was introduced in MySQL 4.0, and + operates on the server level. It caches 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 <xref linkend="query-cache"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-sequences"> + + <question> + + <para> + Does MySQL ¤t-series; have Sequences? + </para> + + </question> + + <answer> + + <para> + No. However, MySQL has an <literal>AUTO_INCREMENT</literal> + system, which in MySQL ¤t-series; can also handle + inserts in a multi-master replication setup. With the + <option>--auto-increment-increment</option> and + <option>--auto-increment-offset</option> startup options, + you can set each server to generate auto-increment values + that don't conflict with other servers. The + <option>--auto-increment-increment</option> value should be + greater than the number of servers, and each server should + have a unique offset. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-now-fractional-seconds"> + + <question> + + <para> + Does MySQL 5.0 have a <literal>NOW()</literal> function with + fractions of seconds? + </para> + + </question> + + <answer> + + <para> + No. This is on the MySQL roadmap as a <quote>rolling + feature</quote>. This means that it is not a flagship + feature, but will be implemented, development time + permitting. Specific customer demand may change this + scheduling. + </para> + + <para> + However, MySQL does parse time strings with a fractional + component. See <xref linkend="time"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-multi-cores"> + + <question> + + <para> + Does MySQL ¤t-series; work with mutli-core + processorsd? + </para> + + </question> + + <answer> + + <para> + Yes. MySQL is fully multi-threaded, and will make use of + multiple CPUs, provided that the operating system supports + them. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-myisam-hot-backup"> + + <question> + + <para> + Is there a hot backup tool for MyISAM like InnoDB Hot + Backup? + </para> + + </question> + + <answer> + + <para> + This is currently under development for a future MySQL + release. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-fk-error-reporting"> + + <question> + + <para> + Is there any improvement in error reporting when Foreign Key + fails (i.e. currently does not report which column and + reference failed)? + </para> + + </question> + + <answer> + + <para> + The foreign key support in InnoDB has seen improvements in + each major version of MySQL. However, global foreign key + support (i.e., generically for all storage engines) is + scheduled for MySQL 5.2 and should resolve any inadequacies + in the current storage engine specific implementation. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-general-acid"> + + <question> + + <para> + Can MySQL ¤t-series; perform ACID transactions? + </para> + + </question> + + <answer> + + <para> + Yes. MySQL has supported transactions for years now, since + 3.23-Max and all versions 4.0 and above. The InnoDB storage + engine offers full ACID transactions with row-level locking, + multi-versioning, non-locking repeatable reads, and all four + SQL standard isolation levels. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-storage-engines"> + + <title>MySQL ¤t-series; FAQ — Storage Engines</title> + + <qandaset defaultlabel="qanda"> + + <qandaentry id="faqs-storage-engines-new-engines"> + + <question> + + <para> + What are the new Storage Engines in MySQL ¤t-series;? + </para> + + </question> + + <answer> + + <para> + The <literal>FEDERATED</literal> storage engine, new in My + SQL ¤t-series;, allows the server to access tables in + other (remote) servers. See + <xref + linkend="federated-storage-engine"/>. + </para> + + <para> + MySQL 4.1 already added other new storage engines, such as + ARCHIVE, CSV and NDB (Cluster). + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-storage-engines-archive"> + + <question> + + <para> + What are the unique benefits of the + <literal>ARCHIVE</literal> storage engine? + </para> + + </question> + + <answer> + + <para> + The <literal>ARCHIVE</literal> Storage Engine, added in + MySQL 4.1, is ideally suited for storing large amounts of + data (without indexes) in a very small footprint, and + selects using table scans. See + <xref + linkend="archive-storage-engine"/>, for + details. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-storage-engines-new-features"> + + <question> + + <para> + Do the new features in MySQL ¤t-series; apply to all + storage engines? + </para> + + </question> + + <answer> + + <para> + The general new features such as views, stored procedures, + triggers, <literal>INFORMATION_SCHEMA</literal>, precision + math (<literal>DECIMAL</literal> column type), and the + <literal>BIT</literal> column type, apply to all storage + engines. There are also additions and changes for specific + storage engines. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-sql-modes"> + + <title>MySQL ¤t-series; FAQ — Server SQL Mode</title> + + <qandaset defaultlabel="qanda"> + + <qandaentry id="faqs-sql-modes-what"> + + <question> + + <para> + What are server SQL modes? + </para> + + </question> + + <answer> + + <para> + 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 + <xref + linkend="server-sql-mode"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faq-sql-modes-how-many"> + + <question> + + <para> + How many server SQL modes are there? + </para> + + </question> + + <answer> + + <para> + Quite a few, as each mode can be individually switched on + and off. See <xref linkend="server-sql-mode"/>, for a + complete list of available modes. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-sql-modes-determining"> + + <question> + + <para> + How do you determine the server SQL mode? + </para> + + </question> + + <answer> + + <para> + You can set the default SQL mode (for + <command>mysqld</command> startup) with the + <option>--sql-mode</option> option. Using the statement + <literal>SET [SESSION|GLOBAL] + sql_mode='<replaceable>modes</replaceable>'</literal>, 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 <literal>SELECT + @@sql_mode</literal> statement. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-sql-modes-scope"> + + <question> + + <para> + Is the mode dependent on the database or connection? + </para> + + </question> + + <answer> + + <para> + 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 <literal>SET + [SESSION|GLOBAL] + sql_mode='<replaceable>modes</replaceable>'</literal>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-sql-modes-extending"> + + <question> + + <para> + Can the rules for strict mode be extended? + </para> + + </question> + + <answer> + + <para> + When we refer to <emphasis>strict mode</emphasis>, we mean a + mode where at least one of the modes + <literal>TRADITIONAL</literal>, + <literal>STRICT_TRANS_TABLES</literal>, or + <literal>STRICT_ALL_TABLES</literal> is enabled. Options can + be combined, so you can add additional restrictions to a + mode. See <xref linkend="server-sql-mode"/>, for more + information. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-sql-modes-performance"> + + <question> + + <para> + Does strict mode impact performance? + </para> + + </question> + + <answer> + + <para> + Of course, a setting that requires intensive validation of + input data requires some additional time. 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. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-sql-modes-default"> + + <question> + + <para> + What is the default server SQL mode when My SQL + ¤t-series; is installed? + </para> + + </question> + + <answer> + + <para> + By default, no special modes are enabled. See + <xref + linkend="server-sql-mode"/>, for + information about all available modes and the default + behaviour. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-stored-procs"> + + <title>MySQL ¤t-series; FAQ — Stored Procedures</title> + + <qandaset defaultlabel="qanda"> + + <qandaentry id="faqs-stored-procs-support"> + + <question> + + <para> + Does MySQL ¤t-series; support stored procedures? + </para> + + </question> + + <answer> + + <para> + Yes. MySQL ¤t-series; supports two types of stored + routines — stored procedures and stored functions. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-docs"> + + <question> + + <para> + Where can I find documentation for MySQL stored procedures + and stored functions? + </para> + + </question> + + <answer> + + <para> + See <xref linkend="stored-procedures"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-forum"> + + <question> + + <para> + Is there a discussion forum for MySQL stored procedures? + </para> + + </question> + + <answer> + + <para> + Yes. See + <ulink + url="http://forums.mysql.com/list.php?98" + >http://forums.mysql.com/list.php?98</ulink>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-spec"> + + <question> + + <para> + Where can I find the ANSI SQL 2003 specification for stored + procedures? + </para> + + </question> + + <answer> + + <para> + Unfortunately, the official specifications are not freely + available (ANSI makes them available for purchase). However, + there are books — such as <citetitle>SQL-99 Complete, + Really</citetitle> by Peter Gulutzan and Trudy Pelzer + — which give a comprehensive overview of the standard, + including coverage of stored procedures. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-management"> + + <question> + + <para> + How do you manage stored routines? + </para> + + </question> + + <answer> + + <para> + It is always good practice to use a clear naming scheme for + your stored routines. You can manage stored procedures with + <literal>CREATE [FUNCTION|PROCEDURE]</literal>, + <literal>ALTER [FUNCTION|PROCEDURE]</literal>, <literal>DROP + [FUNCTION|PROCEDURE]</literal>, and <literal>SHOW CREATE + [FUNCTION|PROCEDURE]</literal>. You can obtain information + about existing stored procedures using the + <literal>ROUTINES</literal> table in the + <literal>INFORMATION_SCHEMA</literal> database (see + <xref + linkend="routines-table"/>). + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-view-all"> + + <question> + + <para> + Is there a way to view all stored procedures and stored + functions in a given database? + </para> + + </question> + + <answer> + + <para> + Yes. For a database named <replaceable>dbname</replaceable>, + use this query on the + <literal>INFORMATION_SCHEMA.ROUTINES</literal> table: + </para> + +<programlisting> +SELECT ROUTINE_TYPE, ROUTINE_NAME + FROM INFORMATION_SCHEMA.ROUTINES + WHERE ROUTINE_SCHEMA='<replaceable>dbname</replaceable>'; +</programlisting> + + <para> + For more information, see + <xref + linkend="routines-table"/>. + </para> + + <para> + The body of a stored routine can be viewed using + <literal>SHOW CREATE FUNCTION</literal> (for a stored + function) or <literal>SHOW CREATE PROCEDURE</literal> (for a + stored procedure). See + <xref linkend="show-create-procedure" + />, for + more information. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-storage"> + + <question> + + <para> + Where are stored procedures stored? + </para> + + </question> + + <answer> + + <para> + In the <literal>proc</literal> table of the + <literal>mysql</literal> system database. However, you + should not access the tables in the system database + directly. Instead, use <literal>SHOW CREATE + FUNCTION</literal> to obtain information about stored + functions, and <literal>SHOW CREATE PROCEDURE</literal> to + obtain information about stored procedures. See + <xref + linkend="show-create-procedure"/>, for + more information about these statements. + </para> + + <para> + You can also query the <literal>ROUTINES</literal> table in + the <literal>INFORMATION_SCHEMA</literal> database — + see <xref linkend="routines-table"/>, for information about + this table. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-packages"> + + <question> + + <para> + Is it possible to group stored procedures or stored + functions into packages? + </para> + + </question> + + <answer> + + <para> + No. This is not supported in MySQL ¤t-series;. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-call-other-sps"> + + <question> + + <para> + Can a stored procedure call another stored procedure? + </para> + + </question> + + <answer> + + <para> + Yes. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-call-triggers"> + + <question> + + <para> + Can a stored procedure call a trigger? + </para> + + </question> + + <answer> + + <para> + A stored procedure can execute an SQL statement, such as an + <literal>UPDATE</literal>, that causes a trigger to fire. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-access-tables"> + + <question> + + <para> + Can a stored procedure access tables? + </para> + + </question> + + <answer> + + <para> + Yes. A stored procedure can access one or more tables as + required. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-raising-errors"> + + <question> + + <para> + Do stored procedures have a statement for raising + application errors? + </para> + + </question> + + <answer> + + <para> + Not in MySQL ¤t-series;. The SQL standard + <literal>SIGNAL</literal> and <literal>RESIGNAL</literal> + statements are on the TODO. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-exception-handling"> + + <question> + + <para> + Do stored procedures provide exception handling? + </para> + + </question> + + <answer> + + <para> + MySQL implements <literal>HANDLER</literal> definitions + according to the SQL standard. See + <xref + linkend="declare-handlers"/>, for + details. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-return-result-sets"> + + <question> + + <para> + Can MySQL ¤t-series; stored routines return result + sets? + </para> + + </question> + + <answer> + + <para> + Yes. If you perform an ordinary <literal>SELECT</literal> + inside a stored procedure or stored function, the result set + is returned directly to the client. You need to use the + MySQL 4.1 client-server protocol for this to work. This + means that — for instance — in PHP, you need to + use the <literal>mysqli</literal> extension rather than the + old <literal>mysql</literal> extension. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-with-recompile"> + + <question> + + <para> + Is + +<literallayout>WITH RECOMPILE</literallayout> + + supported for stored procedures? + </para> + + </question> + + <answer> + + <para> + Not in MySQL ¤t-series;. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-mod-plsql"> + + <question> + + <para> + Is there a MySQL equivalent to using + <literal>mod_plsql</literal> as a gateway on Apache to talk + directly to a stored procedure in the database? + </para> + + </question> + + <answer> + + <para> + There is no equivalent in MySQL ¤t-series;. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-arrays"> + + <question> + + <para> + Can I pass an array as input to a stored procedure? + </para> + + </question> + + <answer> + + <para> + Not in MySQL ¤t-series;. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-cursors-in"> + + <question> + + <para> + Can I pass a cursor as an <literal>IN</literal> parameter to + a stored procedure? + </para> + + </question> + + <answer> + + <para> + In MySQL ¤t-series;, cursors are available inside + stored procedures only. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-cursors-out"> + + <question> + + <para> + Can I return a cursor as an <literal>OUT</literal> parameter + from a stored procedure? + </para> + + </question> + + <answer> + + <para> + In MySQL ¤t-series;, cursors are available inside + stored procedures only. However, if you do not open a cursor + on a <literal>SELECT</literal>, the result will be sent + directly to the client. You can also <literal>SELECT + INTO</literal> variables. See <xref linkend="select"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-return-value"> + + <question> + + <para> + Can I print out a variable's value within a stored procedure + for debugging purposes? + </para> + + </question> + + <answer> + + <para> + Yes. If you perform an ordinary <literal>SELECT</literal> + inside a stored procedure or stored function, the result set + is returned directly to the client. You will need to use the + MySQL 4.1 client-server protocol for this to work. This + means that — for instance — in PHP, you need to + use the <literal>mysqli</literal> extension rather than the + old <literal>mysql</literal> extension. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-stored-procs-transaction-support"> + + <question> + + <para> + Can I commit or roll back transactions inside a stored + procedure? + </para> + + </question> + + <answer> + + <para> + Yes. However, you cannot perform transactional operations + within a stored function. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-triggers"> + + <title>MySQL ¤t-series; FAQ — Triggers</title> + + <qandaset defaultlabel="qanda"> + + <qandaentry id="faqs-triggers-docs"> + + <question> + + <para> + Where can I find the documentation for MySQL + ¤t-series; triggers? + </para> + + </question> + + <answer> + + <para> + See <xref linkend="triggers"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry> + + <question> + + <para> + Is there a discussion forum for MySQL Triggers? + </para> + + </question> + + <answer> + + <para> + Yes. It is available at + <ulink + url="http://forums.mysql.com/list.php?99" + >http://forums.mysql.com/list.php?99</ulink>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-statement-or-row-level"> + + <question> + + <para> + Does MySQL ¤t-series; have statement-level or + row-level triggers? + </para> + + </question> + + <answer> + + <para> + In MySQL ¤t-series;, all triggers are <literal>FOR + EACH ROW</literal> — that is, the trigger is activated + for each row that is inserted, updated, or deleted. MySQL + ¤t-series; does not support triggers using + <literal>FOR EACH STATEMENT</literal>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-default"> + + <question> + + <para> + Are there any default triggers? + </para> + + </question> + + <answer> + + <para> + Not explicitly. MySQL does have specific special behavior + for some <literal>TIMESTAMP</literal> columns, as well as + for columns which are defined using + <literal>AUTO_INCREMENT</literal>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-management"> + + <question> + + <para> + How are triggers managed in MySQL? + </para> + + </question> + + <answer> + + <para> + In MySQL ¤t-series;, triggers can be created using the + <literal>CREATE TRIGGER</literal> statement, and dropped + using <literal>DROP TRIGGER</literal>. See + <xref + linkend="create-trigger"/>, and + <xref + linkend="drop-trigger"/>, for more about + these statements. + </para> + + <para> + Information about triggers can be obtained by querying the + <literal>INFORMATION_SCHEMA.TRIGGERS</literal> table. See + <xref linkend="triggers-table"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-view-all"> + + <question> + + <para> + Is there a way to view all triggers in a given database? + </para> + + </question> + + <answer> + + <para> + Yes. You can obtain a listing of all triggers defined on + database <literal>dbname</literal> using a query on the + INFORMATION_SCHEMA.TRIGGERS table such as the one shown + here: + </para> + +<programlisting> +SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT + FROM INFORMATION_SCHEMA.TRIGGERS + WHERE TRIGGER_SCHEMA='<replaceable>dbname</replaceable>'; +</programlisting> + + <para> + For more information about this table, see + <xref + linkend="triggers-table"/>. + </para> + + <para> + You can also use the <literal>SHOW TRIGGERS</literal> + statement, which is specific to MySQL. See + <xref + linkend="show-triggers"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-storage"> + + <question> + + <para> + Where are triggers stored? + </para> + + </question> + + <answer> + + <para> + Triggers are currently stored in <filename>.TRG</filename> + files, with one such file one per table. In other words, a + trigger belongs to a table. + </para> + + <para> + In the future, we plan to change this so that trigger + information will be included in the + <filename>.FRM</filename> file that defines the structure of + the table. We also plan to make triggers database-level + objects — rather than table-level objects as they are + now — to bring them into compliance with the SQL + standard. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-stored-procs"> + + <question> + + <para> + Can a trigger call a stored procedure? + </para> + + </question> + + <answer> + + <para> + Yes. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-table-access"> + + <question> + + <para> + Can triggers access tables? + </para> + + </question> + + <answer> + + <para> + A trigger can access both old and new data in its own table. + Through a stored procedure, or a multi-table update or + delete statement, a trigger can also affect other tables. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-udfs"> + + <question> + + <para> + Can triggers call an external application through a UDF? + </para> + + </question> + + <answer> + + <para> + No, not at present. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-triggers-update-remote-tables"> + + <question> + + <para> + Is possible for a trigger to update tables on a remote + server? + </para> + + </question> + + <answer> + + <para> + Yes. A table on a remote server could be updated using the + <literal>FEDERATED</literal> storage engine. (See + <xref + linkend="federated-storage-engine"/>). + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-views"> + + <title>MySQL ¤t-series; FAQ — Views</title> + + <qandaset> + + <qandaentry id="faqs-views-docs"> + + <question> + + <para> + Where can I find documentation for MySQL Views? + </para> + + </question> + + <answer> + + <para> + See <xref linkend="views"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-views-forum"> + + <question> + + <para> + Is there a discussion forum for MySQL Views? + </para> + + </question> + + <answer> + + <para> + Yes. See + <ulink + url="http://forums.mysql.com/list.php?100" + >http://forums.mysql.com/list.php?100</ulink> + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-views-alter-underlying-table"> + + <question> + + <para> + What happens to a view if an underlying table is dropped or + renamed? + </para> + + </question> + + <answer> + + <para> + 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 + <literal>CHECK TABLE</literal> statement. (See + <xref + linkend="check-table"/>.) + </para> + + </answer> + + </qandaentry> + + <qandaentry> + + <question> + + <para> + Does MySQL ¤t-series; have table snapshots? + </para> + + </question> + + <answer> + + <para> + No. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-views-materialized"> + + <question> + + <para> + Does MySQL ¤t-series; have materialized views? + </para> + + </question> + + <answer> + + <para> + No. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-views-insert-based-joins"> + + <question> + + <para> + Can you insert into views that are based on joins? + </para> + + </question> + + <answer> + + <para> + It is possible, provided that your <literal>INSERT</literal> + statement has a column list that makes it clear there's only + one table involved. + </para> + + <para> + You <emphasis>cannot</emphasis> insert into multiple tables + with a single insert on a view. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-information-schema"> + + <title>MySQL 5.0 FAQ — <literal>INFORMATION_SCHEMA</literal></title> + + <qandaset> + + <qandaentry id="faqs-information-schema-docs"> + + <question> + + <para> + Where can I find documentation for the MySQL + <literal>INFORMATION_SCHEMA</literal> database? + </para> + + </question> + + <answer> + + <para> + See <xref linkend="information-schema"/> + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-information-schema-forum"> + + <question> + + <para> + Is there a discussion forum for + <literal>INFORMATION_SCHEMA</literal>? + </para> + + </question> + + <answer> + + <para> + See + <ulink url="http://forums.mysql.com/list.php?101" + >http://forums.mysql.com/list.php?101</ulink>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-information-schema-spec"> + + <question> + + <para> + Where can I find the ANSI SQL 2003 specification for + <literal>INFORMATION_SCHEMA</literal>? + </para> + + </question> + + <answer> + + <para> + Unfortunately, the official specifications are not freely + available. (ANSI makes them available for purchase.) + However, there are books available — such as + <citetitle>SQL-99 Complete, Really</citetitle> by Peter + Gulutzan and Trudy Pelzer — which give a comprehensive + overview of the standard, including + <literal>INFORMATION_SCHEMA</literal>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-information-schema-data-dictionary"> + + <question> + + <para> + What is the difference between the Oracle Data Dictionary + and MySQL's <literal>INFORMATION_SCHEMA</literal>? + </para> + + </question> + + <answer> + + <para> + 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 + <literal>INFORMATION_SCHEMA</literal> as defined in the SQL + standard. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-information-schema-modify-tables"> + + <question> + + <para> + Can I add to or otherwise modify the tables found in the + <literal>INFORMATION_SCHEMA</literal> database? + </para> + + </question> + + <answer> + + <para> + No. Since applications may rely on a certain standard + structure, this should not be modified.For this reason, + <emphasis>MySQL AB cannot support bugs or other issues which + result from modifying <literal>INFORMATION_SCHEMA</literal> + tables or data</emphasis>. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-migration"> + + <title>MySQL ¤t-series; FAQ — Migration</title> + + <qandaset> + + <qandaentry id="faqs-migration-docs"> + + <question> + + <para> + Where can I find information on how to migrate from MySQL + &previous-series; to MySQL ¤t-series;? + </para> + + </question> + + <answer> + + <para> + For detailed upgrade information, see + <xref linkend="upgrade"/>. We recommend that you do not skip + a major version when upgrading, but rather complete the + process in steps, upgrading from one major version to the + next in each step. This may seem more complicated, but it + will you save time and trouble — if you encounter + problems during the upgrade, their origin will be easier to + identify, either by you or — if you have a MySQL + Network subscription — by MySQL support. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faqs-migration-storage-engines"> + + <question> + + <para> + How has storage engine (table type) support changed in MySQL + ¤t-series; from previous versions? + </para> + + </question> + + <answer> + + <para> + Storage engine support has changed as follows: + + <itemizedlist> + + <listitem> + <para> + Support for <literal>ISAM</literal> tables was removed + in MySQL 5.0 and you should now use the + <literal>MyISAM</literal> storage engine in place of + <literal>ISAM</literal>. To convert a table + <replaceable>tblname</replaceable> from + <literal>ISAM</literal> to <literal>MyISAM</literal>, + simply issue a statement such as this one: + </para> + +<programlisting>ALTER TABLE <replaceable>tblname</replaceable> ENGINE=MYISAM;</programlisting> + </listitem> + + <listitem> + <para> + Internal <literal>RAID</literal> for + <literal>MyISAM</literal> tables was also removed in + MySQL 5.0. This was formerly used to allow large + tables in filesystems that did not support filesizes + greater than 2GB. All modern filesystems allow for + larger tables; in addition, there are now other + solutions such as <literal>MERGE</literal> tables and + views. + </para> + </listitem> + + <listitem> + <para> + The <literal>VARCHAR</literal> column type now retains + trailing spaces in all storage engines. + </para> + </listitem> + + <listitem> + <para> + <literal>MEMORY</literal> tables (formerly known as + <literal>HEAP</literal> tables) can also contain + <literal>VARCHAR</literal> columns. + </para> + </listitem> + + </itemizedlist> + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faq-security"> + + <title>MySQL ¤t-series; FAQ — Security</title> + + <qandaset> + + <qandaentry id="faq-security-ssl-support"> + + <question> + + <para> + Does MySQL ¤t-series; have native support for SSL? + </para> + + </question> + + <answer> + + <para> + Most ¤t-series; binaries have support for SSL + connections between the client and server. We can't + currently build with the new YaSSL library everywhere, as + it's still quite new and does not compile on all platforms + yet. See <xref linkend="secure-connections"/>. + </para> + + <para> + You can also tunnel a connection via SSH, if (for instance) + if the client application doesn't support SSL connections. + For an example, see <xref linkend="windows-and-ssh"/>. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faq-security-built-in-ssl"> + + <question> + + <para> + Is SSL support be built into MySQL binaries, or must I + recompile the binary myself to enable it? + </para> + + </question> + + <answer> + + <para> + Most ¤t-series; binaries have SSL enabled for + client-server connections thsat are secured, authenticated, + or both. However, the YaSSL library currently does not + compile on all platforms. See + <xref linkend="secure-connections"/>, for a complete listing + of supported and unsupported platforms. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faq-security-ldap"> + + <question> + + <para> + Does MySQL ¤t-series; have built-in authentication + against LDAP directories? + </para> + + </question> + + <answer> + + <para> + No. Support for external authentication methods is on the + MySQL roadmap as a <quote>rolling feature</quote>, which + means that we plan to implement it in the future, but we + have not yet determined when this will be done. + </para> + + </answer> + + </qandaentry> + + <qandaentry id="faq-security-roles"> + + <question> + + <para> + Does MySQL ¤t-series; include support for Roles Based + Access Control (RBAC)? + </para> + + </question> + + <answer> + + <para> + No. Support for roles is on the MySQL roadmap as a + <quote>rolling feature</quote>, which means that we plan to + implement it in the future, but we have not yet determined + when this will be done. + </para> + + </answer> + + </qandaentry> + + </qandaset> + + </section> + + <section id="faqs-replication"> + + <title>MySQL ¤t-series; FAQ — Replication</title> + + <para> + <emphasis role="bold">Q</emphasis>: How do I configure a slave if + the master is running and I do not want to stop it? + </para> + + <para> + <emphasis role="bold">A</emphasis>: There are several + possibilities. If you have taken a snapshot backup of the master + at some point and recorded the binary log filename and offset + (from the output of <literal>SHOW MASTER STATUS</literal>) + corresponding to the snapshot, use the following procedure: + </para> + + <orderedlist> + + <listitem> + <para> + Make sure that the slave is assigned a unique server ID. + </para> + </listitem> + + <listitem> + <para> + Execute the following statement on the slave, filling in + appropriate values for each option: + </para> + +<programlisting> +mysql> <userinput>CHANGE MASTER TO</userinput> + -> <userinput>MASTER_HOST='<replaceable>master_host_name</replaceable>',</userinput> + -> <userinput>MASTER_USER='<replaceable>master_user_name</replaceable>',</userinput> + -> <userinput>MASTER_PASSWORD='<replaceable>master_pass</replaceable>',</userinput> + -> <userinput>MASTER_LOG_FILE='<replaceable>recorded_log_file_name</replaceable>',</userinput> + -> <userinput>MASTER_LOG_POS=<replaceable>recorded_log_position</replaceable>;</userinput> +</programlisting> + </listitem> + + <listitem> + <para> + Execute <literal>START SLAVE</literal> on the slave. + </para> + </listitem> + + </orderedlist> + + <para> + If you do not have a backup of the master server, here is a quick + procedure for creating one. All steps should be performed on the + master host. + </para> + + <orderedlist> + + <listitem> + <para> + Issue this statement to acquire a global read lock: + </para> + +<programlisting> +mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput> +</programlisting> + </listitem> + + <listitem> + <para> + With the lock still in place, execute this command (or a + variation of it): + </para> + +<programlisting> +shell> <userinput>tar zcf /tmp/backup.tar.gz /var/lib/mysql</userinput> +</programlisting> + </listitem> + + <listitem> + <para> + Issue this statement and record the output, which you will + need later: + </para> + +<programlisting> +mysql> <userinput>SHOW MASTER STATUS;</userinput> +</programlisting> + </listitem> + + <listitem> + <para> + Release the lock: + </para> + +<programlisting> +mysql> <userinput>UNLOCK TABLES;</userinput> +</programlisting> + </listitem> + + </orderedlist> + + <para> + An alternative to using the preceding procedure to make a binary + copy is to make an SQL dump of the master. To do this, you can use + <command>mysqldump --master-data</command> on your master and + later load the SQL dump into your slave. However, this is slower + than making a binary copy. + </para> + + <remark role="todo"> + [pd] Following para isn't so clear... + </remark> + + <para> + Regardless of which of the two methods you use, afterward follow + the instructions for the case when you have a snapshot and have + recorded the log filename and offset. You can use the same + snapshot to set up several slaves. Once you have the snapshot of + the master, you can wait to set up a slave as long as the binary + logs of the master are left intact. The two practical limitations + on the length of time you can wait are the amount of disk space + available to retain binary logs on the master and the length of + time it takes the slave to catch up. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: Does the slave need to be + connected to the master all the time? + </para> + + <remark role="todo"> + [pd] Is this a promise we should make? + </remark> + + <para> + <emphasis role="bold">A</emphasis>: No, it does not. The slave can + go down or stay disconnected for hours or even days, and then + reconnect and catch up on updates. For example, you can set up a + master/slave relationship over a dial-up link where the link is up + only sporadically and for short periods of time. The implication + of this is that, at any given time, the slave is not guaranteed to + be in synchrony with the master unless you take some special + measures. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: How do I know how late a slave + is compared to the master? In other words, how do I know the date + of the last statement replicated by the slave? + </para> + + <para> + <emphasis role="bold">A</emphasis>: You can read the + <literal>Seconds_Behind_Master</literal> column in <literal>SHOW + SLAVE STATUS</literal>. See + <xref linkend="replication-implementation-details"/>. + </para> + + <remark role="todo"> + Check following in light of changes to TIMESTAMP in recent + versions. /JS + </remark> + + <para> + When the slave SQL thread executes an event read from the master, + it modifies its own time to the event timestamp. (This is why + <literal>TIMESTAMP</literal> is well replicated.) In the + <literal>Time</literal> column in the output of <literal>SHOW + PROCESSLIST</literal>, the number of seconds displayed for the + slave SQL thread is the number of seconds between the timestamp of + the last replicated event and the real time of the slave machine. + You can use this to determine the date of the last replicated + event. Note that if your slave has been disconnected from the + master for one hour, and then reconnects, you may immediately see + <literal>Time</literal> values like 3600 for the slave SQL thread + in <literal>SHOW PROCESSLIST</literal>. This is because the slave + is executing statements that are one hour old. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: How do I force the master to + block updates until the slave catches up? + </para> + + <para> + <emphasis role="bold">A</emphasis>: Use the following procedure: + </para> + + <orderedlist> + + <listitem> + <para> + On the master, execute these statements: + </para> + +<programlisting> +mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput> +mysql> <userinput>SHOW MASTER STATUS;</userinput> +</programlisting> + + <para> + Record the replication cooredinates (the log filename and + offset) from the output of the <literal>SHOW</literal> + statement. + </para> + </listitem> + + <listitem> + <para> + On the slave, issue the following statement, where the + arguments to the <literal>MASTER_POS_WAIT()</literal> function + are the replication coordinate values obtained in the previous + step: + </para> + +<programlisting> +mysql> <userinput>SELECT MASTER_POS_WAIT('<replaceable>log_name</replaceable>', <replaceable>log_offset</replaceable>);</userinput> +</programlisting> + + <para> + The <literal>SELECT</literal> statement blocks until the slave + reaches the specified log file and offset. At that point, the + slave is in synchrony with the master and the statement + returns. + </para> + </listitem> + + <listitem> + <para> + On the master, issue the following statement to allow the + master to begin processing updates again: + </para> + +<programlisting> +mysql> <userinput>UNLOCK TABLES;</userinput> +</programlisting> + </listitem> + + </orderedlist> + + <para> + <emphasis role="bold">Q</emphasis>: What issues should I be aware + of when setting up two-way replication? + </para> + + <para> + <emphasis role="bold">A</emphasis>: MySQL replication currently + does not support any locking protocol between master and slave to + guarantee the atomicity of a distributed (cross-server) update. In + other words, it is possible for client A to make an update to + co-master 1, and in the meantime, before it propagates to + co-master 2, client B could make an update to co-master 2 that + makes the update of client A work differently than it did on + co-master 1. Thus, when the update of client A makes it to + co-master 2, it produces tables that are different from what you + have on co-master 1, even after all the updates from co-master 2 + have also propagated. This means that you should not chain two + servers together in a two-way replication relationship unless you + are sure that your updates can safely happen in any order, or + unless you take care of mis-ordered updates somehow in the client + code. + </para> + + <para> + You should also realize that two-way replication actually does not + improve performance very much (if at all) as far as updates are + concerned. Each server must do the same number of updates, just as + you would have a single server do. The only difference is that + there is a little less lock contention, because the updates + originating on another server are serialized in one slave thread. + Even this benefit might be offset by network delays. + </para> + + <indexterm> + <primary>performance</primary> + <secondary>improving</secondary> + </indexterm> + + <indexterm> + <primary>increasing</primary> + <secondary>performance</secondary> + </indexterm> + + <para> + <emphasis role="bold">Q</emphasis>: How can I use replication to + improve performance of my system? + </para> + + <para> + <emphasis role="bold">A</emphasis>: You should set up one server + as the master and direct all writes to it. Then configure as many + slaves as you have the budget and rackspace for, and distribute + the reads among the master and the slaves. You can also start the + slaves with the <option>--skip-innodb</option>, + <option>--skip-bdb</option>, + <option>--low-priority-updates</option>, and + <option>--delay-key-write=ALL</option> options to get speed + improvements on the slave end. In this case, the slave uses + non-transactional <literal>MyISAM</literal> tables instead of + <literal>InnoDB</literal> and <literal>BDB</literal> tables to get + more speed by eliminating transactional overhead. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: What should I do to prepare + client code in my own applications to use performance-enhancing + replication? + </para> + + <para> + <emphasis role="bold">A</emphasis>: If the part of your code that + is responsible for database access has been properly + abstracted/modularized, converting it to run with a replicated + setup should be very smooth and easy. Change the implementation of + your database access to send all writes to the master, and to send + reads to either the master or a slave. If your code does not have + this level of abstraction, setting up a replicated system gives + you the opportunity and motivation to it clean up. Start by + creating a wrapper library or module that implements the following + functions: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>safe_writer_connect()</literal> + </para> + </listitem> + + <listitem> + <para> + <literal>safe_reader_connect()</literal> + </para> + </listitem> + + <listitem> + <para> + <literal>safe_reader_statement()</literal> + </para> + </listitem> + + <listitem> + <para> + <literal>safe_writer_statement()</literal> + </para> + </listitem> + + </itemizedlist> + + <para> + <literal>safe_</literal> in each function name means that the + function takes care of handling all error conditions. You can use + different names for the functions. The important thing is to have + a unified interface for connecting for reads, connecting for + writes, doing a read, and doing a write. + </para> + + <para> + Then convert your client code to use the wrapper library. This may + be a painful and scary process at first, but it pays off in the + long run. All applications that use the approach just described + are able to take advantage of a master/slave configuration, even + one involving multiple slaves. The code is much easier to + maintain, and adding troubleshooting options is trivial. You need + modify only one or two functions; for example, to log how long + each statement took, or which statement among those issued gave + you an error. + </para> + + <para> + If you have written a lot of code, you may want to automate the + conversion task by using the <command>replace</command> utility + that comes with standard MySQL distributions, or write your own + conversion script. Ideally, your code uses consistent programming + style conventions. If not, then you are probably better off + rewriting it anyway, or at least going through and manually + regularizing it to use a consistent style. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: When and how much can MySQL + replication improve the performance of my system? + </para> + + <para> + <emphasis role="bold">A</emphasis>: MySQL replication is most + beneficial for a system that processes frequent reads and + infrequent writes. In theory, by using a + single-master/multiple-slave setup, you can scale the system by + adding more slaves until you either run out of network bandwidth, + or your update load grows to the point that the master cannot + handle it. + </para> + + <para> + To determine how many slaves you can use before the added benefits + begin to level out, and how much you can improve performance of + your site, you need to know your query patterns, and to determine + empirically by benchmarking the relationship between the + throughput for reads (reads per second, or + <literal>reads</literal>) and for writes + (<literal>writes</literal>) on a typical master and a typical + slave. The example here shows a rather simplified calculation of + what you can get with replication for a hypothetical system. + </para> + + <para> + Let's say that system load consists of 10% writes and 90% reads, + and we have determined by benchmarking that + <literal>reads</literal> is 1200 − 2 × + <literal>writes</literal>. In other words, the system can do 1,200 + reads per second with no writes, the average write is twice as + slow as the average read, and the relationship is linear. Let us + suppose that the master and each slave have the same capacity, and + that we have one master and <replaceable>N</replaceable> slaves. + Then we have for each server (master or slave): + </para> + + <para> + <literal>reads = 1200 − 2 × writes</literal> + </para> + + <para> + <literal>reads = 9 × writes / (<replaceable>N</replaceable> + + 1)</literal> (reads are split, but writes go to all servers) + </para> + + <para> + <literal>9 × writes / (<replaceable>N</replaceable> + 1) + 2 + × writes = 1200</literal> + </para> + + <para> + <literal>writes = 1200 / (2 + + 9/(<replaceable>N</replaceable>+1))</literal> + </para> + + <para> + The last equation indicates the maximum number of writes for + <replaceable>N</replaceable> slaves, given a maximum possible read + rate of 1,200 per minute and a ratio of nine reads per write. + </para> + + <para> + This analysis yields the following conclusions: + </para> + + <itemizedlist> + + <listitem> + <para> + If <replaceable>N</replaceable> = 0 (which means we have no + replication), our system can handle about 1200/11 = 109 writes + per second. + </para> + </listitem> + + <listitem> + <para> + If <replaceable>N</replaceable> = 1, we get up to 184 writes + per second. + </para> + </listitem> + + <listitem> + <para> + If <replaceable>N</replaceable> = 8, we get up to 400 writes + per second. + </para> + </listitem> + + <listitem> + <para> + If <replaceable>N</replaceable> = 17, we get up to 480 writes + per second. + </para> + </listitem> + + <listitem> + <para> + Eventually, as <replaceable>N</replaceable> approaches + infinity (and our budget negative infinity), we can get very + close to 600 writes per second, increasing system throughput + about 5.5 times. However, with only eight servers, we increase + it nearly four times. + </para> + </listitem> + + </itemizedlist> + + <para> + Note that these computations assume infinite network bandwidth and + neglect several other factors that could be significant on your + system. In many cases, you may not be able to perform a + computation similar to the one just shown that accurately predicts + what will happen on your system if you add + <replaceable>N</replaceable> replication slaves. However, + answering the following questions should help you decide whether + and by how much replication will improve the performance of your + system: + </para> + + <itemizedlist> + + <listitem> + <para> + What is the read/write ratio on your system? + </para> + </listitem> + + <listitem> + <para> + How much more write load can one server handle if you reduce + the reads? + </para> + </listitem> + + <listitem> + <para> + For how many slaves do you have bandwidth available on your + network? + </para> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Q</emphasis>: How can I use replication to + provide redundancy or high availability? + </para> + + <para> + <emphasis role="bold">A</emphasis>: With the currently available + features, you would have to set up a master and a slave (or + several slaves), and to write a script that monitors the master to + check whether it is up. Then instruct your applications and the + slaves to change master in case of failure. Some suggestions: + </para> + + <itemizedlist> + + <listitem> + <para> + To tell a slave to change its master, use the <literal>CHANGE + MASTER TO</literal> statement. + </para> + </listitem> + + <listitem> + <para> + A good way to keep your applications informed as to the + location of the master is by having a dynamic DNS entry for + the master. With <literal>bind</literal> you can use + <filename>nsupdate</filename> to dynamically update your DNS. + </para> + </listitem> + + <listitem> + <para> + Run your slaves with the <option>--log-bin</option> option and + without <option>--log-slave-updates</option>. In this way, the + slave is ready to become a master as soon as you issue + <literal>STOP SLAVE</literal>; <literal>RESET + MASTER</literal>, and <literal>CHANGE MASTER TO</literal> + statement on the other slaves. For example, assume that you + have the following setup: + </para> + + <remark role="todo"> + Replace this with an actual diagram. + </remark> + +<programlisting> + WC + \ + v + WC----> M + / | \ + / | \ + v v v + S1 S2 S3 +</programlisting> + + <para> + In this diagram, <literal>M</literal> means the master, + <literal>S</literal> the slaves, <literal>WC</literal> the + clients issuing database writes and reads; clients that issue + only database reads are not represented, because they need not + switch. <literal>S1</literal>, <literal>S2</literal>, and + <literal>S3</literal> are slaves running with + <option>--log-bin</option> and without + <option>--log-slave-updates</option>. Because updates received + by a slave from the master are not logged in the binary log + unless <option>--log-slave-updates</option> is specified, the + binary log on each slave is empty initially. If for some + reason <literal>M</literal> becomes unavailable, you can pick + one of the slaves to become the new master. For example, if + you pick <literal>S1</literal>, all <literal>WC</literal> + should be redirected to <literal>S1</literal>, which will log + updates to its binary log. <literal>S2</literal> and + <literal>S3</literal> should then replicate from + <literal>S1</literal>. + </para> + + <para> + The reason for running the slave without + <option>--log-slave-updates</option> is to prevent slaves from + receiving updates twice in case you cause one of the slaves to + become the new master. Suppose that <literal>S1</literal> has + <option>--log-slave-updates</option> enabled. Then it will + write updates that it receives from <literal>M</literal> to + its own binary log. When <literal>S2</literal> changes from + <literal>M</literal> to <literal>S1</literal> as its master, + it may receive updates from <literal>S1</literal> that it has + already received from <literal>M</literal> + </para> + + <para> + Make sure that all slaves have processed any statements in + their relay log. On each slave, issue <literal>STOP SLAVE + IO_THREAD</literal>, then check the output of <literal>SHOW + PROCESSLIST</literal> until you see <literal>Has read all + relay log</literal>. When this is true for all slaves, they + can be reconfigured to the new setup. On the slave + <literal>S1</literal> being promoted to become the master, + issue <literal>STOP SLAVE</literal> and <literal>RESET + MASTER</literal>. + </para> + + <para> + On the other slaves <literal>S2</literal> and + <literal>S3</literal>, use <literal>STOP SLAVE</literal> and + <literal>CHANGE MASTER TO MASTER_HOST='S1'</literal> (where + <literal>'S1'</literal> represents the real hostname of + <literal>S1</literal>). To <literal>CHANGE MASTER</literal>, + add all information about how to connect to + <literal>S1</literal> from <literal>S2</literal> or + <literal>S3</literal> (<replaceable>user</replaceable>, + <replaceable>password</replaceable>, + <replaceable>port</replaceable>). In <literal>CHANGE + MASTER</literal>, there is no need to specify the name of + <literal>S1</literal>'s binary log or binary log position to + read from: We know it is the first binary log and position 4, + which are the defaults for <literal>CHANGE MASTER</literal>. + Finally, use <literal>START SLAVE</literal> on + <literal>S2</literal> and <literal>S3</literal>. + </para> + + <para> + Then instruct all <literal>WC</literal> to direct their + statements to <literal>S1</literal>. From that point on, all + updates statements sent by <literal>WC</literal> to + <literal>S1</literal> are written to the binary log of + <literal>S1</literal>, which then contains every update + statement sent to <literal>S1</literal> since + <literal>M</literal> died. + </para> + + <para> + The result is this configuration: + </para> + + <remark role="todo"> + Replace with actual diagram (graphic). + </remark> + +<programlisting> + WC + / + | + WC | M(unavailable) + \ | + \ | + v v + S1<--S2 S3 + ^ | + +-------+ +</programlisting> + + <para> + When <literal>M</literal> is up again, you must issue on it + the same <literal>CHANGE MASTER</literal> as that issued on + <literal>S2</literal> and <literal>S3</literal>, so that + <literal>M</literal> becomes a slave of <literal>S1</literal> + and picks up all the <literal>WC</literal> writes that it + missed while it was down. To make <literal>M</literal> a + master again (because it is the most powerful machine, for + example), use the preceding procedure as if + <literal>S1</literal> was unavailable and <literal>M</literal> + was to be the new master. During this procedure, do not forget + to run <literal>RESET MASTER</literal> on <literal>M</literal> + before making <literal>S1</literal>, <literal>S2</literal>, + and <literal>S3</literal> slaves of <literal>M</literal>. + Otherwise, they may pick up old <literal>WC</literal> writes + from before the point at which <literal>M</literal> became + unavailable. + </para> + + <para> + Note that there is no synchronization between the different + slaves to a master. Some slaves might be ahead of others. This + means that the concept outlined in the previous example might + not work. In practice, however, the relay logs of different + slaves will most likely not be far behind the master, so it + would work, anyway (but there is no guarantee). + </para> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Q</emphasis>: How do I prevent GRANT and + REVOKE statements from replicating to slave machines? + </para> + + <para> + <emphasis role="bold">A</emphasis>: Start the server with the + <option>--replicate-wild-ignore-table=mysql.%</option> option. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: Does replication work on mixed + operating systems (for example, the master runs on Linux while + slaves run on Mac OS X and Windows)? + </para> + + <para> + <emphasis role="bold">A</emphasis>: Yes. + </para> + + <para> + <emphasis role="bold">Q</emphasis>: Does replication work on mixed + hardware architectures (for example, the master runs on a 64-bit + machine while slaves run on 32-bit machines)? + </para> + + <para> + <emphasis role="bold">A</emphasis>: Yes. + </para> + + </section> + + <section id="faqs-mysql-cluster"> + + <title>MySQL Cluster FAQ</title> + + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>FAQ</secondary> + </indexterm> + + <indexterm> + <primary>frequently-asked questions about MySQL Cluster</primary> + </indexterm> + + <remark> + Cluster FAQ, version 1.0 (2005-01-29) Author: Jon Stephens, with + the assistance of Tomas Ulin, Mikael Ronström, Harrison Fisk, + Stewart Smith, Pekka Nousiainen, Jeb Miller, Jonas Oreland. + </remark> + + <remark role="todo"> + [js] Add cross-references. + </remark> + + <para> + This section answers questions that are often asked about MySQL + Cluster. + </para> + + <itemizedlist> + + <listitem> + <indexterm> + <primary><literal>NDB</literal></primary> +<!-- <seealso>MySQL Cluster</seealso> --> + </indexterm> + + <para> + <emphasis>What does <quote>NDB</quote> mean?</emphasis> + </para> + + <para> + This stands for + <quote><emphasis role="bold">N</emphasis>etwork + <emphasis role="bold">D</emphasis>ata<emphasis role="bold">b</emphasis>ase.</quote> + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary><foreignphrase>vs</foreignphrase> replication</secondary> + </indexterm> + + <para> + <emphasis>What's the difference in using Cluster + <foreignphrase>vs</foreignphrase> using + replication?</emphasis> + </para> + + <para> + In a replication setup, a master MySQL server updates one or + more slaves. Transactions are committed sequentially, and a + slow transaction can cause the slave to lag behind the master. + This means that if the master fails, it is possible that the + slave might not have recorded the last few transactions. If a + transaction-safe engine such as <literal>InnoDB</literal> 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. + </para> + + <para> + In short, whereas standard MySQL replication is asynchronous, + MySQL Cluster is synchronous. + </para> + + <para> + We have implemented (asynchronous) replication for Cluster in + MySQL 5.1. This includes the capability to replicate both + between two clusters, and from a MySQL cluster to a + non-Cluster MySQL server. + </para> + + <para> + See <xref linkend="mysql-cluster-replication"/>. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>networking requirements</secondary> + </indexterm> + + <para> + <emphasis>Do I need to do any special networking to run + Cluster? (How do computers in a cluster + communicate?)</emphasis> + </para> + + <para> + MySQL Cluster is intended to be used in a high-bandwidth + environment, with computers connecting via TCP/IP. Its + performance depends directly upon the connection speed between + the cluster's computers. The minimum connectivity requirements + for Cluster include a typical 100-megabit Ethernet network or + the equivalent. We recommend you use gigabit Ethernet whenever + available. + </para> + + <para> + The faster SCI protocol is also supported, but requires + special hardware. See + <xref linkend="mysql-cluster-interconnects"/>, for more + information about SCI. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>number of computers required</secondary> + </indexterm> + + <para> + <emphasis>How many computers do I need to run a cluster, and + why?</emphasis> + </para> + + <para> + A minimum of three computers is required to run a viable + cluster. However, the minimum + <emphasis role="bold">recommended</emphasis> 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. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>roles of computers</secondary> + </indexterm> + + <para> + <emphasis>What do the different computers do in a + cluster?</emphasis> + </para> + + <para> + 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 + <firstterm>nodes</firstterm>, and a computer housing a cluster + node is sometimes referred to as a <firstterm>cluster + host</firstterm>. There are three types of nodes, each + corresponding to a specific role within the cluster. These + are: + </para> + + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>node types</secondary> + </indexterm> + + <itemizedlist> + + <listitem> + <para> + <emphasis role="bold">Management node (MGM + node)</emphasis>: 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 + <command>ndb_mgmd</command>; the management client used to + control MySQL Cluster via the MGM node is + <command>ndb_mgm</command>. + </para> + </listitem> + + <listitem> + <para> + <emphasis role="bold">Data node</emphasis>: Stores and + replicates data. Data node functionality is handled by an + instance of the NDB data node process + <command>ndbd</command>. + </para> + </listitem> + + <listitem> + <para> + <emphasis role="bold">SQL node</emphasis>: This is simply + an instance of MySQL Server (<command>mysqld</command>) + that is built with support for the <literal>NDB + Cluster</literal> storage engine and started with the + <command>--ndb-cluster</command> option to enable the + engine. + </para> + </listitem> + + </itemizedlist> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>platforms supported</secondary> + </indexterm> + + <remark role="todo"> + Reference NDB client apps - ndb_mgmd, ndb_mgm, ndbd, mysqld + </remark> + + <para> + <emphasis>With which operating systems can I use + Cluster?</emphasis> + </para> + + <para> + MySQL Cluster is supported on most Unix-like operating + systems, including Linux, Mac OS X, Solaris, BSD, HP-UX, AIX, + and IRIX, among others, as well as Novell Netware. Cluster is + not supported for Windows at this time. However, we are + working to add Cluster support for other platforms, including + Windows, and our goal is to offer MySQL Cluster on all + platforms for which MySQL itself is supported. + </para> + + <para> + 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 + <ulink url="http://www.mysql.com/support/supportedplatforms.html"/>. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>hardware requirements</secondary> + </indexterm> + + <para> + <emphasis>What are the hardware requirements for running MySQL + Cluster?</emphasis> + </para> + + <remark role="todo"> + [js] Add pointers to SCI, Fragments, Replicas. + </remark> + + <para> + Cluster should run on any platform for which NDB-enabled + binaries are available. Naturally, faster CPUs and more memory + will improve performance, and 64-bit CPUs will likely be more + effective than 32-bit processors. There must be sufficient + memory on machines used for data nodes to hold each node's + share of the database (see <emphasis>How much RAM do I + Need?</emphasis> for more information). Nodes can communicate + via a standard TCP/IP network and hardware. For SCI support, + special networking hardware is required. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>memory requirements</secondary> + </indexterm> + + <para> + <emphasis>How much RAM do I need? Is it possible to use disk + memory at all?</emphasis> + </para> + + <para> + Previous to MySQL 5.1, Cluster was in-memory only. This meant + that all table data (including indexes) was stored in RAM. If + your data took up 1GB of space and you wanted to replicate it + once in the cluster, you needed 2GB of memory to do so (1 GB + per replica). This was in addition to the memory required by + the operating system and any applications running on the + cluster computers. This is still true of in-memory tables. + </para> + + <para> + 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 <literal>DataMemory</literal>. However, this + will at best result in severely degraded performance, and may + cuase the node to be dropped due to slow response time (missed + hearbeats). We do not recommend on relying on disk swapping in + a production environment for this reason. In any case, once + the <literal>DataMemory</literal> limit is reached, any + operations requiring additional memory (such as inserts) will + fail. + </para> + + <para> + <literal>NDB Cluster</literal> in MySQL ¤t-series; + includes support for Disk Data, which helps to alleviate these + issues. See <xref linkend="mysql-cluster-disk-data"/>, for + more information. + </para> + + <para> + You can use the following formula for obtaining a rough + estimate of how much RAM is needed for each data node in the + cluster: + </para> + +<programlisting> +(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes +</programlisting> + + <para> + To calculate the memory requirements more exactly requires + determining, for each table in the cluster database, the + storage space required per row (see + <xref linkend="storage-requirements"/>, for details), and + multiplying this by the number of rows. You must also remember + to account for any column indexes as follows: + </para> + + <itemizedlist> + + <listitem> + <para> + Each primary key or hash index created for an + <literal>NDBCluster</literal> table requires 21−25 + bytes per record. These indexes use + <literal>IndexMemory</literal>. + </para> + </listitem> + + <listitem> + <para> + Each ordered index requires 10 bytes storage per record, + using <literal>DataMemory</literal>. + </para> + </listitem> + + <listitem> + <para> + Creating a primary key or unique index also creates an + ordered index, unless this index is created with + <literal>USING HASH</literal>. In other words: + </para> + + <itemizedlist> + + <listitem> + <para> + A primary key or unique index on a Cluster table + normally takes up 31 to 35 bytes per record. + </para> + </listitem> + + <listitem> + <para> + However, if the primary key or unique index is created + with <literal>USING HASH</literal>, then it requires + only 21 to 25 bytes per record. + </para> + </listitem> + + </itemizedlist> + + <para> + Note that creating MySQL Cluster tables with + <literal>USING HASH</literal> 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 <literal>USING + HASH</literal> 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. + </para> + </listitem> + + </itemizedlist> + + <indexterm> + <primary>MySQL Cluster</primary> + <secondary><filename>ndb_size.pl</filename> (utility)</secondary> + </indexterm> + + <indexterm> + <primary><literal>ndb_size.pl</literal> (utility)</primary> + </indexterm> + + <para> + When calculating Cluster memory requirements, you may find + useful the <filename>ndb_size.pl</filename> utility which is + available on + <ulink url="http://forge.mysql.com/projects/view.php?id=88">MySQLForge</ulink>. + This Perl script connects to a current MySQL (non-Cluster) + database and creates a report on how much space that database + would require if it used the <literal>NDBCluster</literal> + storage engine. For more information, see + <xref linkend="mysql-cluster-utilities-ndb-size"/>. + </para> + + <para> + It is especially important to keep in mind that + <emphasis>every MySQL Cluster table must have a primary + key</emphasis>. The <literal>NDB</literal> storage engine + creates a primary key automatically if none is defined, and + this primary key is created without <literal>USING + HASH</literal>. + </para> + + <para> + 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 <literal>DataMemory</literal> or + <literal>IndexMemory</literal> is in use, and again when use + reaches 85%, 90%, and so on. + </para> + + <para> + We often see questions from users who report that, when they + are trying to populate a Cluster database, the loading process + terminates prematurely and an error message like this one is + observed: + </para> + +<programlisting> +ERROR 1114: The table 'my_cluster_table' is full +</programlisting> + + <para> + When this occurs, the cause is very likely to be that your + setup does not provide sufficient RAM for all table data and + all indexes, <emphasis>including the primary key required by + the <literal>NDB</literal> storage engine and automatically + created in the event that the table definition does not + include the definition of a primary key</emphasis>. + </para> + + <para> + It is also worth noting that all data nodes should have the + same amount of RAM, as no data node in a cluster can use more + memory than the least amount available to any individual data + node. In other words, if there are three computers hosting + Cluster data nodes, with two of these having 3GB of RAM + available to store Cluster data, and one having only 1GB RAM, + then each data node can devote only 1GB to clustering. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>networking requirements</secondary> + </indexterm> + + <para> + <emphasis>Because MySQL Cluster uses TCP/IP, does that mean I + can run it over the Internet, with one or more nodes in a + remote location?</emphasis> + </para> + + <para> + It is very doubtful in any case 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. + </para> + + <para> + 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.) + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>SQL statements</secondary> + </indexterm> + + <para> + <emphasis>Do I have to learn a new programming or query + language to use Cluster?</emphasis> + </para> + + <para> + No. Although some specialized commands are used to manage and + configure the cluster itself, only standard (My)SQL queries + and commands are required for the following operations: + </para> + + <itemizedlist> + + <remark role="todo"> + [js] Add pointers to NDB client commands/options. + </remark> + + <listitem> + <para> + Creating, altering, and dropping tables (including Disk + Data tables and related objects) + </para> + </listitem> + + <listitem> + <para> + Inserting, updating, and deleting table data + </para> + </listitem> + + <listitem> + <para> + Creating, changing, and dropping primary and unique + indexes + </para> + </listitem> + + <listitem> + <para> + Configuring and managing SQL nodes (MySQL servers) + </para> + </listitem> + + </itemizedlist> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>error messages</secondary> + </indexterm> + + <para> + <emphasis>How do I find out what an error or warning message + means when using Cluster?</emphasis> + </para> + + <para> + There are two ways in which this can be done: + </para> + + <itemizedlist> + + <listitem> + <para> + From within the <command>mysql</command> client, use + <command>SHOW ERRORS</command> or <command>SHOW + WARNINGS</command> immediately upon being notified of the + error or warning condition. Errors and warnings also be + displayed in MySQL Query Browser. + </para> + </listitem> + + <listitem> + <para> + From a system shell prompt, use <command>perror --ndb + <replaceable>error_code</replaceable></command>. + </para> + </listitem> + + </itemizedlist> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>transactions</secondary> + </indexterm> + + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>transaction isolation levels supported</secondary> + </indexterm> + + <para> + <emphasis>Is MySQL Cluster transaction-safe? What isolation + levels are supported?</emphasis> + </para> + + <para> + <emphasis>Yes</emphasis>: For tables created with the + <literal>NDB</literal> storage engine, transactions are + supported. In MySQL ¤t-series;, Cluster supports only + the <literal>READ COMMITTED</literal> transaction isolation + level. + </para> + </listitem> + + <listitem> + <para> + <emphasis>What storage engines are supported by MySQL + Cluster?</emphasis> + </para> + + <para> + Clustering in MySQL is supported only by the + <literal>NDB</literal> storage engine. That is, in order for a + table to be shared between nodes in a cluster, it must be + created using <literal>ENGINE=NDB</literal> (or + <literal>ENGINE=NDBCLUSTER</literal>, which is equivalent). + </para> + + <para> + It is possible to create tables using other storage engines + (such as <literal>MyISAM</literal> or + <literal>InnoDB</literal>) on a MySQL server being used for + clustering, but these non-<literal>NDB</literal> tables will + <emphasis role="bold">not</emphasis> participate in the + cluster; they are local to the individual MySQL server + instance on which they are created. + </para> + </listitem> + +<!-- + <listitem> + <remark role="todo"> + [js] This question needs to remain commented out until + answered. + </remark> + + <para> + <emphasis>How do I continue to send queries in the event that + one of the SQL nodes fails?</emphasis> + </para> + + <para> + ... + </para> + </listitem> +--> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>how to obtain</secondary> + </indexterm> + + <para> + <emphasis>Which versions of the MySQL software support + Cluster? Do I have to compile from source?</emphasis> + </para> + + <para> + Cluster is supported in all server binaries in the + ¤t-series; release series for operating systems on which + MySQL Cluster is available. See <xref linkend="mysqld"/>. You + can determine whether your server has NDB support using either + the <literal>SHOW VARIABLES LIKE 'have_%'</literal> or + <literal>SHOW ENGINES</literal> statement. + </para> + + <para> + You can also obtain NDB support by compiling MySQL from + source, but it is not necessary to do so simply to use MySQL + Cluster. To download the latest binary, RPM, or source + distibution in the MySQL ¤t-series; series, visit + <ulink url="&base-url-downloads;mysql/¤t-series;.html"/>. + </para> + </listitem> + + <listitem> + <para> + <emphasis>In the event of a catastrophic failure — say, + for instance, the whole city loses power + <emphasis role="bold">and</emphasis> my UPS fails — + would I lose all my data?</emphasis> + </para> + + <para> + 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.) + </para> + </listitem> + + <listitem> + <para> + <emphasis>Is it possible to use <literal>FULLTEXT</literal> + indexes with Cluster?</emphasis> + </para> + + <para> + <literal>FULLTEXT</literal> indexing is not currently + supported by the <literal>NDB</literal> storage engine, or by + any storage engine other than <literal>MyISAM</literal>. We + are working to add this capability in a future release. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Can I run multiple nodes on a single + computer?</emphasis> + </para> + + <para> + It is possible but not advisable. One of the chief reasons to + run a cluster is to provide redundancy. To enjoy the full + benefits of this redundancy, each node should reside on a + separate machine. If you place multiple nodes on a single + machine and that machine fails, you lose all of those nodes. + Given that MySQL Cluster can be run on commodity hardware + loaded with a low-cost (or even no-cost) operating system, the + expense of an extra machine or two is well worth it to + safeguard mission-critical data. It also worth noting that the + requirements for a cluster host running a management node are + minimal. This task can be accomplished with a 200 MHz Pentium + CPU and sufficient RAM for the operating system plus a small + amount of overhead for the <command>ndb_mgmd</command> and + <command>ndb_mgm</command> processes. + </para> + + <para> + It is acceptable to run multiple cluster data nodes on a + single host for learning about MySQL Cluster, or for testing + purposes; howver, this is not supported for production use. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Can I add nodes to a cluster without restarting + it?</emphasis> + </para> + + <para> + Not at present. A simple restart is all that is required for + adding new MGM or SQL nodes to a Cluster. When adding data + nodes the process is more complex, and requires the following + steps: + </para> + + <orderedlist> + + <listitem> + <para> + Make a complete backup of all Cluster data. + </para> + </listitem> + + <listitem> + <para> + Completely shut down the cluster and all cluster node + processes. + </para> + </listitem> + + <listitem> + <para> + Restart the cluster, using the <option>--initial</option> + startup option. + </para> + </listitem> + + <listitem> + <para> + Restore all cluster data from the backup. + </para> + </listitem> + + </orderedlist> + + <para> + In a future MySQL Cluster release series, we hope to implement + a <quote>hot</quote> reconfiguration capability for MySQL + Cluster to minimize (if not eliminate) the requirement for + restarting the cluster when adding new nodes. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Are there any limitations that I should be aware of + when using Cluster?</emphasis> + </para> + + <para> + <literal>NDB</literal> tables in MySQL ¤t-series; are + subject to the following limitations: + </para> + + <itemizedlist> + + <listitem> + <para> + Temporary tables are not supported; a <literal>CREATE + TEMPORARY TABLE</literal> statement using + <literal>ENGINE=NDB</literal> or + <literal>ENGINE=NDBCLUSTER</literal> fails with an error. + </para> + </listitem> + + <listitem> + <para> + The only types of user-defined partitioning supported for + <literal>NDB</literal> tables are <literal>KEY</literal> + and <literal>LINEAR KEY</literal>. (Beginning with MySQL + 5.1.12, attempting to create an <literal>NDB</literal> + table using any other partitioning type fails with an + error.) + </para> + </listitem> + + <listitem> + <para> + <literal>FULLTEXT</literal> indexes and index prefixes are + not supported. Only complete columns may be indexed. + </para> + </listitem> + + <listitem> + <para> + Spatial data types are not supported. See + <xref linkend="spatial-extensions"/>. + </para> + </listitem> + + <listitem> + <para> + Only complete rollbacks for transactions are supported. + Partial rollbacks and rollbacks to savepoints are not + supported. + </para> + </listitem> + + <listitem> + <para> + 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. + </para> + </listitem> + + <listitem> + <para> + The maximum size for a table row is 8 kilobytes, not + counting <literal>BLOB</literal> 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. + </para> + </listitem> + + <listitem> + <para> + The <literal>NDB</literal> engine does not support foreign + key constraints. As with <literal>MyISAM</literal> tables, + these are ignored. + </para> + </listitem> + + </itemizedlist> + + <para> + For additional information on Cluster limitations, see + <xref linkend="mysql-cluster-limitations"/>. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>importing existing tables</secondary> + </indexterm> + + <para> + <emphasis>How do I import an existing MySQL database into a + cluster?</emphasis> + </para> + + <para> + You can import databases into MySQL Cluster much as you would + with any other version of MySQL. Other than the limitation + mentioned in the previous question, the only other special + requirement is that any tables to be included in the cluster + must use the <literal>NDB</literal> storage engine. This means + that the tables must be created with + <literal>ENGINE=NDB</literal> or + <literal>ENGINE=NDBCLUSTER</literal>. It is also possible to + convert existing tables using other storage engines to + <literal>NDB Cluster</literal> using <literal>ALTER + TABLE</literal>, but requires an additional workaround. See + <xref linkend="mysql-cluster-limitations"/>, for details. + </para> + </listitem> + + <listitem> + <para> + <emphasis>How do cluster nodes communicate with one + another?</emphasis> + </para> + + <para> + Cluster nodes can communicate via any of three different + protocols: TCP/IP, SHM (shared memory), and SCI (Scalable + Coherent Interface). Where available, SHM is used by default + between nodes residing on the same cluster host. 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 <xref linkend="mysql-cluster-interconnects"/>, + for more about using SCI as a transport mechanism in MySQL + Cluster. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>arbitrator</secondary> + </indexterm> + + <indexterm> + <primary>arbitrator</primary> +<!-- <see>MySQL Cluster</see> --> + </indexterm> + + <para> + <emphasis>What is an <quote>arbitrator</quote>?</emphasis> + </para> + + <para> + If one or more nodes in a cluster fail, it is possible that + not all cluster nodes will be able to <quote>see</quote> one + another. In fact, it is possible that two sets of nodes might + become isolated from one another in a network partitioning, + also known as a <quote>split brain</quote> scenario. This type + of situation is undesirable because each set of nodes tries to + behave as though it is the entire cluster. + </para> + + <para> + When cluster nodes go down, there are two possibilities. If + more than 50% of the remaining nodes can communicate with each + other, we have what is sometimes called a <quote>majority + rules</quote> situation, and this set of nodes is considered + to be the cluster. The arbitrator comes into play when there + is an even number of nodes: in such cases, the set of nodes to + which the arbitrator belongs is considered to be the cluster, + and nodes not belonging to this set are shut down. + </para> + + <para> + The preceding information is somewhat simplified. A more + complete explanation taking into account node groups follows: + </para> + + <para> + When all nodes in at least one node group are alive, network + partitioning is not an issue, because no one portion of the + cluster can form a functional cluster. The real problem arises + when no single node group has all its nodes alive, in which + case network partitioning (the <quote>split-brain</quote> + 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 <literal>ArbitrationRank</literal> configuration + parameter for MySQL Server and management server nodes. (See + <xref linkend="mysql-cluster-mgm-definition"/>, for details.) + It should also be noted that the role of arbitrator does not + in and of itself impose any heavy demands upon the host so + designated, and thus the arbitrator host does not need to be + particularly fast or to have extra memory especially for this + purpose. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>data types supported</secondary> + </indexterm> + + <para> + <emphasis>What data types are supported by MySQL + Cluster?</emphasis> + </para> + + <para> + MySQL Cluster supports all of the usual MySQL data types, with + the exception of those associated with MySQL's spatial + extensions. (See <xref linkend="spatial-extensions"/>.) In + addition, there are some differences with regard to indexes + when used with <literal>NDB</literal> tables. + <emphasis role="bold">Note</emphasis>: MySQL Cluster Disk Data + tables (that is, tables created with <literal>TABLESPACE ... + STORAGE DISK ENGINE=NDBCLUSTER</literal>) have only + fixed-width rows. This means that (for example) each Disk Data + table record containing a <literal>VARCHAR(255)</literal> + column requires space for 255 characters (as required for the + character set and collation being used for the table), + regardless of the actual number of characters stored therein. + </para> + + <para> + See <xref linkend="mysql-cluster-limitations"/>, for more + information about these issues. + </para> + </listitem> + + <listitem> + <indexterm> + <primary>MySQL Cluster</primary> + <secondary>starting and stopping</secondary> + </indexterm> + + <para> + <emphasis>How do I start and stop MySQL Cluster?</emphasis> + </para> + + <para> + It is necessary to start each node in the cluster separately, + in the following order: + </para> + + <orderedlist> + + <listitem> + <para> + Start the management node with the + <command>ndb_mgmd</command> command. + </para> + </listitem> + + <listitem> + <para> + Start each data node with the <command>ndbd</command> + command. + </para> + </listitem> + + <listitem> + <para> + Start each MySQL server (SQL node) using + <command>mysqld_safe --user=mysql &</command>. + </para> + </listitem> + + </orderedlist> + + <para> + Each of these commands must be run from a system shell on the + machine housing the affected node. You can verify the cluster + is running by starting the MGM management client + <command>ndb_mgm</command> on the machine housing the MGM + node. + </para> + </listitem> + + <listitem> + <para> + <emphasis>What happens to cluster data when the cluster is + shut down?</emphasis> + </para> + + <para> + The data held in memory by the cluster's data nodes is written + to disk, and is reloaded in memory the next time that the + cluster is started. + </para> + + <para> + To shut down the cluster, enter the following command in a + shell on the machine hosting the MGM node: + </para> + +<programlisting> +shell> <userinput>ndb_mgm -e shutdown</userinput> +</programlisting> + + <para> + This causes the <command>ndb_mgm</command>, + <command>ndb_mgm</command>, and any <command>ndbd</command> + processes to terminate gracefully. MySQL servers running as + Cluster SQL nodes can be stopped using <command>mysqladmin + shutdown</command>. + </para> + + <para> + For more information, see + <xref linkend="mysql-cluster-mgm-client-commands"/>, and + <xref linkend="mysql-cluster-multi-shutdown-restart"/>. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Is it helpful to have more than one management node + for a cluster?</emphasis> + </para> + + <para> + It can be helpful as a fail-safe. Only one MGM node controls + the cluster at any given time, but it is possible to configure + one MGM as primary, and one or more additional management + nodes to take over in the event that the primary MGM node + fails. + </para> + + <remark role="todo"> + [js] Add pointers to cluster configuration material. + </remark> + </listitem> + + <listitem> + <remark role="todo"> + Reference Cluster Configuration + </remark> + + <para> + <emphasis>Can I mix different kinds of hardware and operating + systems in a Cluster?</emphasis> + </para> + + <para> + Yes, so long as all machines and operating systems have the + same endianness (all big-endian or all little-endian). It is + also possible to use different MySQL Cluster releases on + different nodes. However, we recommend this be done only as + part of a rolling upgrade procedure. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Can I run two data nodes on a single host? Two SQL + nodes?</emphasis> + </para> + + <para> + Yes, it is possible to do this. In the case of multiple data + nodes, each node must use a different data directory. If you + want to run multiple SQL nodes on one machine, each instance + of <command>mysqld</command> must use a different TCP/IP port. + However, running more than one node of a given type per + machine is not supported for production use. + </para> + </listitem> + + <listitem> + <para> + <emphasis>Can I use hostnames with MySQL Cluster?</emphasis> + </para> + + <para> + Yes, it is possible to use DNS and DHCP for cluster hosts. + However, if your application requires <quote>five + nines</quote> availability, we recommend using fixed IP + addresses. Making communication between Cluster hosts + dependent on services such as DNS and DHCP introduces + additional points of failure, and the fewer of these, the + better. + </para> + </listitem> + +<!-- + <listitem> + <remark role="todo"> + Need to answer this question: + </remark> + + <para> + <emphasis>How do I handle MySQL users in a Cluster having + multiple MySQL servers?</emphasis> + </para> + </listitem> +--> + + </itemizedlist> + + </section> + + <section id="faqs-cjk"> + + <title>MySQL ¤t-series; FAQ — MySQL Chinese, Japanese, and Korean + Character Sets</title> + + <indexterm type="concept"> + <primary>CJK</primary> + <secondary>FAQ</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Chinese, Japanese, Korean character sets</primary> + <secondary>frequently asked questions</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Japanese, Korean, Chinese character sets</primary> + <secondary>frequently asked questions</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Korean, Chinese, Japanese character sets</primary> + <secondary>frequently asked questions</secondary> + </indexterm> + + <para> + This Frequently-Asked-Questions section comes from the experiences + of MySQL's Support and Development groups, after handling many + enquiries about CJK (Chinese Japanese Korean) issues. + </para> + + <section id="cjk-faq-question-marks"> + + <title>SELECT shows non-Latin characters as "?"s. Why?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>characters displayed as question marks</secondary> + </indexterm> + + <para> + You inserted CJK characters with <literal>INSERT</literal>, but + when you do a <literal>SELECT</literal>, they all look like + <quote>?</quote>. It usually is a setting in MySQL that doesn't + match the settings for the application program or the operating + system. These are common troubleshooting steps: + + <itemizedlist> + + <listitem> + <para> + Find out: what version do you have? The statement + <literal>SELECT VERSION();</literal> will tell you. This + FAQ is for MySQL version 5, so some of the answers here + will not apply to you if you have version 4.0 or 4.1. + </para> + </listitem> + + <listitem> + <para> + Find out: what character set is the database column really + in? Too frequently, people think that the character set + will be the same as the server's set (false), or the set + used for display purposes (false). Make sure, by saying + <literal>SHOW CREATE TABLE tablename</literal>, or better + yet by saying this: + +<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; +</programlisting> + </para> + </listitem> + + <listitem> + <para> + Find out: what is the hexadecimal value? + +<programlisting> +SELECT HEX(your_column_name) +FROM your_table_name; +</programlisting> + + If you see <literal>3F</literal>, then that really is the + encoding for <literal>?</literal>, so no wonder you see + <quote>?</quote>. Probably this happened because of a + problem converting a particular character from your client + character set to the target character set. + </para> + </listitem> + + <listitem> + <para> + Find out: is a literal round trip possible, that is, if + you select <quote>literal</quote> (or <quote>_introducer + hexadecimal-value</quote>) do you get + <quote>literal</quote> as a result? For example, with the + Japanese Katakana Letter Pe, which looks like + <literal>ペ'</literal>, and which exists in all CJK + character sets, and which has the code point value + (hexadecimal coding) <literal>0x30da</literal>, enter: + +<programlisting> +SELECT 'ペ' AS `ペ`; /* or SELECT _ucs2 0x30da; */ +</programlisting> + + If the result doesn't look like <literal>ペ</literal>, a + round trip failed. For bug reports, we might ask people to + follow up with <literal>SELECT hex('ペ');</literal>. Then + we can see whether the client encoding is right. + </para> + </listitem> + + <listitem> + <para> + Find out: is it the browser or application? Just use + <command>mysql</command> (the MySQL client program, which + on Windows will be <command>mysql.exe</command>). If + <command>mysql</command> displays correctly but your + application doesn't, then your problem is probably + <quote>Settings</quote>, but consult also the question + about <quote>Troubles with Access (or Perl) (or PHP) + (etc.)</quote> much later in this FAQ. + </para> + + <para> + To find your settings, the statement you need here is + <literal>SHOW VARIABLES</literal>. For example: + +<programlisting> +mysql> <userinput>SHOW VARIABLES LIKE 'char%';</userinput> ++--------------------------+----------------------------------------+ +| 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) +</programlisting> + + The above are typical character-set settings for an + international-oriented client (notice the use of + <literal>utf8</literal> Unicode) connected to a server in + the West (<literal>latin1</literal> is a West Europe + character set and a default for MySQL). + </para> + + <para> + Although Unicode (usually the <literal>utf8</literal> + variant on Unix, usually the <literal>ucs2</literal> + variant on Windows) is better than <quote>latin</quote>, + it's often not what your operating system utilities + support best. Many Windows users find that a Microsoft + character set, such as <literal>cp932</literal> for + Japanese Windows, is what's suitable. + </para> + + <para> + If you can't control the server settings, and you have no + idea what your underlying computer is about, then try + changing to a common character set for the country that + you're in (<literal>euckr</literal> = Korea, + <literal>gb2312</literal> or <literal>gbk</literal> = + People's Republic of China, <literal>big5</literal> = + other China, <literal>sjis</literal> or + <literal>ujis</literal> or <literal>cp932</literal> or + <literal>eucjpms</literal> = Japan, + <literal>ucs2</literal> or <literal>utf8</literal> = + anywhere). Usually it is only necessary to change the + client and connection and results settings, and there is a + simple statement which changes all three at once, namely + <literal>SET NAMES</literal>. For example: + +<programlisting> +SET NAMES 'big5'; +</programlisting> + + Once you get the correct setting, you can make it + permanent by editing <filename>my.cnf</filename> or + <filename>my.ini</filename>. For example you might add + lines looking like this: + +<programlisting> +[mysqld] +character-set-server=big5 +[client] +default-character-set=big5 +</programlisting> + </para> + </listitem> + + </itemizedlist> + </para> + + </section> + + <section id="cjk-faq-gb-charset-problems"> + + <title>Troubles with GB character sets (Chinese)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with GB character sets (Chinese)</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>gb2312, gbk</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>gb2312, gbk</primary> + </indexterm> + + <para> + <remark role="update"> + [SH] References to d.udm.net (Bar's pages) need to be changed + once we've moved those pages to the Reference Manual. + </remark> + + MySQL supports the two common variants of the GB (<quote>Guojia + Biaozhun</quote> or <quote>National Standard</quote>) character + sets which are official in the People's Republic of China: + <literal>gb2312</literal> and <literal>gbk</literal>. Sometimes + people try to insert <literal>gbk</literal> characters into + <literal>gb2312</literal>, and it works most of the time because + <literal>gbk</literal> is a superset of + <literal>gb2312</literal>. But eventually they try to insert a + rarer Chinese character and it doesn't work. (Example: bug + #16072 in our bugs database, + <ulink url="http://bugs.mysql.com/bug.php?id=16072"/>). So we'll + try to clarify here exactly what characters are legitimate in + <literal>gb2312</literal> or <literal>gbk</literal>, with + reference to the official documents. Please check these + references before reporting <literal>gb2312</literal> or + <literal>gbk</literal> bugs. We now have a graphic listing of + the <literal>gbk</literal> characters, currently on the site of + Mr Alexander Barkov (MySQL's principal programmer for character + set issues). The chart is in order according to the + <literal>gb2312_chinese_ci</literal> collation: + <ulink url="http://d.udm.net/bar/~bar/charts/gb2312_chinese_ci.html"/>. + MySQL's <literal>gbk</literal> is in reality <quote>Microsoft + code page 936</quote>. This differs from the official + <literal>gbk</literal> for characters <literal>A1A4</literal> + (middle dot), <literal>A1AA</literal> (em dash), + <literal>A6E0-A6F5</literal>, and <literal>A8BB-A8C0</literal>. + For a listing of the differences, see + <ulink url="http://recode.progiciels-bpi.ca/showfile.html?name=dist/libiconv/gbk.h"/>. + For a listing of gbk/Unicode mappings, see + <ulink url="http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP936.TXT"/>. + For MySQL's listing of gbk characters, see + <ulink url="http://d.udm.net/bar/~bar/charts/gbk_chinese_ci.html"/>. + </para> + + </section> + + <section id="cjk-faq-big5-charset-problems"> + + <title>Troubles with big5 character set (Chinese)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with Big5 character sets (Chinese)</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>big5</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>big5</primary> + </indexterm> + + <para> + MySQL supports the Big5 character set which is common in Hong + Kong and the Republic of China (Taiwan). MySQL's + <literal>big5</literal> is in reality <quote>Microsoft code page + 950</quote>, which is very similar to the original + <literal>big5</literal> character set. This is a recent change, + starting with MySQL version 4.1.16 / 5.0.16. We made the change + as a result of a bug report, bug #12476 in our bugs database, + <ulink url="http://bugs.mysql.com/bug.php?id=12476"/> (title: + <quote>Some big5 codes are still missing ...</quote>). For + example, the following statements work in the current version of + MySQL, but not in old versions: + +<programlisting> +mysql> <userinput>create table big5 (big5 char(1) character set big5);</userinput> +Query OK, 0 rows affected (0.13 sec) + +mysql> <userinput>insert into big5 values (0xf9dc);</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>select * from big5;</userinput> ++------+ +| big5 | ++------+ +| 嫺 | ++------+ +1 row in set (0.02 sec) +</programlisting> + + There is a feature request for adding HKSCS extensions (bug + #13577 in our bugs database, + <ulink url="http://bugs.mysql.com/bug.php?id=13577)"/>. People + who need the extension may find the suggested patch for bug + #13577 is of interest. + </para> + + </section> + + <section id="cjk-faq-charset-conversion-problems"> + + <title>Troubles with character-set conversions (Japanese)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>conversion problems with Japanese character sets</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Japanese character sets</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Japanese character sets</primary> + <secondary>conversion</secondary> + </indexterm> + + <para> + MySQL supports the <literal>sjis</literal>, + <literal>ujis</literal>, <literal>cp932</literal>, and eucjpms + 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 <literal>sjis</literal> or + <literal>ujis</literal>) and a Windows client (typically with + <literal>cp932</literal>). But conversions can seem to fail. + Here's why. In this conversion table, the + <literal>ucs2</literal> column is the source, and the + <literal>sjis</literal>/<literal>cp932</literal>/<literal>ujis</literal>/<literal>eucjpms</literal> + columns are the destination, that is, what the hexadecimal + result would be if we used <literal>CONVERT(ucs2)</literal> or + if we assigned a <literal>ucs2</literal> column containing the + value to an + <literal>sjis</literal>/<literal>cp932</literal>/<literal>ujis</literal>/<literal>eucjpms</literal> + column. + +<programlisting> +character name ucs2 sjis cp932 ujis eucjpms +-------------- ---- ---- ---- ---- ------- + +BROKEN BAR 00A6 3F 3F 8FA2C3 3F +FULLWIDTH BROKEN BAR FFE4 3F FA55 3F 8FA2 + +YEN SIGN 00A5 3F 3F 20 3F +FULLWIDTH YEN SIGN FFE5 818F 818F A1EF 3F + +TILDE 007E 7E 7E 7E 7E +OVERLINE 203E 3F 3F 20 3F + +HORIZONTAL BAR 2015 815C 815C A1BD A1BD +EM DASH 2014 3F 3F 3F 3F + +REVERSE SOLIDUS 005C 815F 5C 5C 5C +FULLWIDTH "" FF3C 3F 815F 3F A1C0 + +WAVE DASH 301C 8160 3F A1C1 3F +FULLWIDTH TILDE FF5E 3F 8160 3F A1C1 + +DOUBLE VERTICAL LINE 2016 8161 3F A1C2 3F +PARALLEL TO 2225 3F 8161 3F A1C2 + +MINUS SIGN 2212 817C 3F A1DD 3F +FULLWIDTH HYPHEN-MINUS FF0D 3F 817C 3F A1DD + +CENT SIGN 00A2 8191 3F A1F1 3F +FULLWIDTH CENT SIGN FFE0 3F 8191 3F A1F1 + +POUND SIGN 00A3 8192 3F A1F2 3F +FULLWIDTH POUND SIGN FFE1 3F 8192 3F A1F2 + +NOT SIGN 00AC 81CA 3F A2CC 3F +FULLWIDTH NOT SIGN FFE2 3F 81CA 3F A2CC +</programlisting> + + For example, consider this extract from the table: + +<programlisting> + ucs2 sjis cp932 + ---- ---- ----- +NOT SIGN 00AC 81CA 3F +FULLWIDTH NOT SIGN FFE2 3F 81CA +</programlisting> + + It means <quote>for NOT SIGN which is Unicode U+00AC, MySQL + converts to sjis code point 0x81CA and to cp932 code point + 3F</quote>. (<literal>3F</literal> is question mark + (<quote>?</quote>) and is what we always use when we can't + convert.) Now, what should we do if we want to convert + <literal>sjis 81CA</literal> to <literal>cp932</literal>? Our + answer is: <quote>?</quote>. There are serious complaints about + this, many people would prefer a <quote>loose</quote> + conversion, so that <literal>81CA (NOT SIGN)</literal> in + <literal>sjis</literal> becomes <literal>81CA (FULLWIDTH NOT + SIGN)</literal> in <literal>cp932</literal>. We are considering + changing. + </para> + + </section> + + <section id="cjk-faq-great-yen-sign-problem"> + + <title>The Great Yen Sign problem (Japanese)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with great Yen sign (Japanese)</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Yen sign</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Yen sign (Japanese)</primary> + </indexterm> + + <para> + In SJIS the code for Yen Sign (<literal>¥</literal>) is + <literal>5C</literal>. In SJIS the code for Reverse Solidus + (<literal>\</literal>) is <literal>5C</literal>. Since the above + statements are contradictory, confusion often results. Well, to + put it more seriously, some versions of Japanese character sets + (both <literal>sjis</literal> and <literal>euc</literal>) have + treated <literal>5C</literal> as a reverse solidus, also known + as a backslash, and others have treated it as a yen sign. + There's nothing we can do, except take sides: MySQL follows only + one version of the JIS (Japanese Industrial Standards) standard + description, and <emphasis>5C is Reverse Solidus</emphasis>, + always. Should we make a separate character set where + <literal>5C</literal> is Yen Sign, as another DBMS (Oracle) + does? We haven't decided. Certainly not in version 5.1 or 5.2. + But if people keep complaining about The Great Yen Sign Problem, + that's one possible solution. + </para> + + </section> + + <section id="cjk-faq-euckr-charset-problems"> + + <title>Troubles with euckr character set (Korean)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with euckr character set (Korean)</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Korean character set</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Korean</primary> + </indexterm> + + <para> + MySQL supports the <literal>euckr</literal> (Extended Unix Code + Korea) character set which is common in South Korea. In theory, + problems could arise because there have been several versions of + this character set. So far, only one problem has been noted, for + Korea's currency symbol. We use the <quote>ASCII</quote> variant + of EUC-KR, in which the code point <literal>0x5c</literal> is + REVERSE SOLIDUS, that is <literal>\</literal>, instead of the + <quote>KS-Roman</quote> variant of EUC-KR, in which the code + point <literal>0x5c</literal> is WON SIGN, that is + <quote>₩</quote>. You can't convert Unicode + <literal>U+20A9</literal> WON SIGN to <literal>euckr</literal>: + +<programlisting> +mysql> <userinput>SELECT CONVERT('₩' USING euckr) AS euckr,</userinput> +-> <userinput>HEX(CONVERT('₩' USING euckr)) AS hexeuckr;</userinput> ++-------+----------+ +| euckr | hexeuckr | ++-------+----------+ +| ? | 3F | ++-------+----------+ +1 row in set (0.00 sec) +</programlisting> + + MySQL's graphic Korean chart is here: + <ulink url="http://d.udm.net/bar/~bar/charts/euckr_korean_ci.html"/>. + </para> + + </section> + + <section id="cjk-faq-data-truncated"> + + <title>The <quote>Data truncated</quote> message</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with data truncation</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>data truncation</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Data truncation with CJK characters</primary> + </indexterm> + + <para> + For illustration, we'll make a table with one Unicode + (<literal>ucs2</literal>) column and one Chinese + (<literal>gb2312</literal>) column. + +<programlisting> +mysql> <userinput>CREATE TABLE ch</userinput> + -> <userinput>(ucs2 CHAR(3) CHARACTER SET ucs2,</userinput> + -> <userinput>gb2312 CHAR(3) CHARACTER SET gb2312);</userinput> +Query OK, 0 rows affected (0.05 sec) +</programlisting> + + We'll try to place the rare character <literal>汌</literal> in + both columns. + +<programlisting> +mysql> <userinput>INSERT INTO ch VALUES ('A汌B','A汌B');</userinput> +Query OK, 1 row affected, 1 warning (0.00 sec) +</programlisting> + + Ah, there's a warning. Let's see what it is. + +<programlisting> +mysql> <userinput>SHOW WARNINGS;</userinput> ++---------+------+---------------------------------------------+ +| Level | Code | Message | ++---------+------+---------------------------------------------+ +| Warning | 1265 | Data truncated for column 'gb2312' at row 1 | ++---------+------+---------------------------------------------+ +1 row in set (0.00 sec) +</programlisting> + + So it's a warning about the gb2312 column only. + +<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) +</programlisting> + + There are several things that need explanation here. + + <orderedlist> + + <listitem> + <para> + The fact that it's a <quote>warning</quote> rather than an + <quote>error</quote> is characteristic of MySQL. We like + to try to do what we can, to get the best fit, rather than + give up. + </para> + </listitem> + + <listitem> + <para> + The <literal>汌</literal> character isn't in the + <literal>gb2312</literal> character set. We described that + problem earlier. + </para> + </listitem> + + <listitem> + <para> + Admittedly the message is misleading. We didn't + <quote>truncate</quote> in this case, we replaced with a + question mark. We've had a complaint about this message + (bug #9337). But until we come up with something better, + just accept that error/warning code 2165 can mean a + variety of things. + </para> + </listitem> + + <listitem> + <para> + With <literal>SQL_MODE=TRADITIONAL</literal>, there would + be an error message, but instead of error 2165 you would + see: <literal>ERROR 1406 (22001): Data too long for column + 'gb2312' at row 1</literal>. + </para> + </listitem> + + </orderedlist> + </para> + + </section> + + <section id="cjk-faq-access-perl-php-troubles"> + + <title>Troubles with Access, Perl, PHP, etc.</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with Access, Perl, PHP, etc.</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Access, Perl, PHP, etc.</secondary> + </indexterm> + + <para> + You can't get things to look right with your special program for + a GUI front end or browser? Get a direct connection to the + server (with <command>mysql</command> on Unix or with + <command>mysql.exe</command> on Windows) and try the same query + there. If mysql is okay, then the trouble is probably that your + application interface needs some initializing. Use + <command>mysql</command> to tell you what character set(s) it + uses, by saying <literal>SHOW VARIABLES LIKE 'char%';</literal>. + If it's Access, you're probably connecting with MyODBC. So + you'll want to check out the Reference Manual page for + configuring an ODBC DSN, and pay attention particularly to the + illustrations for <quote>SQL command on connect</quote>. You + should enter <literal>SET NAMES 'big5'</literal> (supposing that + you use <literal>big5</literal>) (you don't need a + <literal>;</literal> here). If it's ASP, you might need to add + <literal>SET NAMES</literal> in the code. Here is an example + that has worked in the past: + +<programlisting> +<% +Session.CodePage=0 +Dim strConnection +Dim Conn +strConnection="driver={MySQL ODBC 3.51 Driver};server=yourserver;uid=yourusername;" \ + & "pwd=yourpassword;database=yourdatabase;stmt=SET NAMES 'big5';" +Set Conn = Server.CreateObject(<quote>ADODB.Connection</quote>) +Conn.Open strConnection +%> +</programlisting> + + If it's PHP, here's a slightly different user suggestion: + +<programlisting> +<?php + $link = mysql_connect($host,$usr,$pwd); + mysql_select_db($db); + if (mysql_error()) { print "Database ERROR: " . mysql_error(); } + mysql_query("SET CHARACTER SET utf8", $link); + mysql_query("SET NAMES 'utf8'", $link); +?> +</programlisting> + + In this case, the tipper used <literal>SET CHARACTER + SET</literal> statement to change + <literal>character_set_client</literal> and + <literal>character_set_result</literal>, and used <literal>SET + NAMES</literal> to change + <literal>character_set_client</literal> and + <literal>character_set_connection</literal> and + <literal>character_set_results</literal>. So actually the + <literal>SET CHARACTER SET</literal> statement is redundant. + (Incidentally, MySQL people encourage the use of the + <literal>mysqli</literal> extension, rather than the + <literal>mysql</literal> example that this example uses.) + Another thing to check with PHP is the browser assumptions. + Sometimes a meta tag change in the heading area suffices, for + example: <literal><meta http-equiv="Content-Type" + content="text/html; charset=utf-8"></literal> + </para> + + <para> + For Connector/J tips, see the manual section in the Connectors + chapter titled <quote>Using Character Sets and Unicode</quote>. + </para> + + </section> + + <section id="cjk-faq-restore-mysql40-behavior"> + + <title>How can I get old MySQL 4.0 behaviour back?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>MySQL 4.0 behaviour</secondary> + </indexterm> + + <para> + In the old days, with MySQL Version 4.0, there was a single + <quote>global</quote> character set for both server and client + sides, and the decision was made by the server administrator. We + changed that starting with MySQL Version 4.1. What happens now + is a <quote>handshake</quote>. The MySQL Reference Manual + describes it thus: + + <blockquote> + + <para> + 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 <literal>character_set_client</literal>, + <literal>character_set_results</literal>, and + <literal>character_set_connection</literal> system + variables. In effect, the server performs a <literal>SET + NAMES</literal> operation using the character set name. + </para> + + </blockquote> + + The effect of this is: you can't control the client character + set by saying <literal>mysqld + --character-set-server=utf8</literal>. But some Asian customers + said that they don't like that, they want the MySQL 4.0 + behaviour. So we added a <command>mysqld</command> switch, + <option>--character-set-client-handshake</option>, which (and + this is the interesting part) can be turned off with + <option>--skip-character-set-client-handshake</option>. If you + start mysqld with + <option>--skip-character-set-client-handshake</option>, then the + behaviour is like this: When a client connects, it sends to the + server the name of the character set that it wants to use. The + server ignores it! Here is an illustration with the handshake + switch on or off. Pretend that your favourite server character + set is <literal>latin1</literal> (of course that's unlikely in a + CJK area but it's MySQL's default if there's no + <filename>my.ini</filename> or <filename>my.cnf</filename> + file). Pretend that the client operates with + <literal>utf8</literal> because that's what the client's + operating system supports. Start the server with a default + character set, <literal>latin1</literal>: + +<programlisting> +mysqld --character-set-server=latin1 +</programlisting> + + Start the client with a default character set, + <literal>utf8</literal>: + +<programlisting> +mysql --default-character-set=utf8 +</programlisting> + + Show what the current settings are: + +<programlisting> +mysql> <userinput>SHOW VARIABLES LIKE 'char%';</userinput> ++--------------------------+----------------------------------------+ +| 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) +</programlisting> + + Stop the client. Stop the server with + <command>mysqladmin</command>. Start the server again but this + time say <quote>skip the handshake</quote>: + +<programlisting> +mysqld --character-set-server=utf8 --skip-character-set-client-handshake +</programlisting> + + Start the client with a default character set, + <literal>utf8</literal>, again. Show what the current settings + are, again: + +<programlisting> +mysql> <userinput>SHOW VARIABLES LIKE 'char%';</userinput> ++--------------------------+----------------------------------------+ +| 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) +</programlisting> + + As you can see by comparing the <literal>SHOW + VARIABLES</literal> results, the server ignores the client's + initial settings if the + <option>--skip-character-set-client-handshake</option> is used. + </para> + + </section> + + <section id="cjk-faq-fulltext-searches"> + + <title>Why do some LIKE and FULLTEXT searches fail?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>problems with LIKE and FULLTEXT</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>LIKE and FULLTEXT</secondary> + </indexterm> + + <para> + There is a simple problem with <literal>LIKE</literal> searches + on <literal>BINARY</literal> and <literal>BLOB</literal> + 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 <literal>utf8</literal>, + <literal>A</literal> requires one byte but + <literal>ペ</literal> requires three bytes. Illustration: + +<programlisting> + +-------------------------+---------------------------+ + | octet_length(_utf8 'A') | octet_length(_utf8 'ペ') | + +-------------------------+---------------------------+ + | 1 | 3 | + +-------------------------+---------------------------+ + 1 row in set (0.00 sec) + </programlisting> + + If we don't know where the first character ends, then we don't + know where the second character begins, and even simple-looking + searches like <literal>LIKE '_A%'</literal> will fail. The + solution is to use a regular CJK character set in the first + place, or convert to a CJK character character set before + comparing. Incidentally, this is one reason why MySQL cannot + allow encodings of nonexistent characters: It must be strict + about rejecting bad input, or it won't know where characters + end. There is a simple problem with <literal>FULLTEXT</literal>: + we need to know the end of a word. With Western writing this is + rarely a problem because there are spaces between words. With + Asian writing this is not the case. We could use half-good + solutions, like saying that all Han characters represent words, + or depending on (Japanese) changes from Katakana to Hiragana + which are due to grammatical endings. But the only good solution + requires a dictionary, and we haven't found a good open-source + dictionary. + </para> + + </section> + + <section id="cjk-faq-available-cjk-charsets"> + + <title>What CJK character sets are available?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>available character sets</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>character sets available</secondary> + </indexterm> + + <para> + The list of CJK character sets may vary depending on version. + For example, the <literal>eucjpms</literal> character set is a + recent addition. But the language name appears in the + <literal>DESCRIPTION</literal> column for every entry in + <literal>information_schema.character_sets</literal>. Therefore, + to get a current list of all the non-Unicode CJK character sets, + say: + +<programlisting> +mysql> <userinput>SELECT character_set_name, description</userinput> + -> <userinput>FROM information_schema.character_sets</userinput> + -> <userinput>WHERE description LIKE '%Chinese%'</userinput> + -> <userinput>OR description LIKE '%Japanese%'</userinput> + -> <userinput>OR description LIKE '%Korean%'</userinput> + -> <userinput>ORDER BY character_set_name;</userinput> ++--------------------+---------------------------+ +| 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) +</programlisting> + </para> + + </section> + + <section id="cjk-faq-character-x-availability"> + + <title>Is character X available in all character sets?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>availability of specific characters</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>testing if specific characters are available</secondary> + </indexterm> + + <para> + The majority of everyday-use Chinese/Japanese characters + (simplified Chinese and basic non-halfwidth Kana Japanese) + appear in all CJK character sets. Here is a stored procedure + which accepts a UCS-2 Unicode character, converts it to all + other character sets, and displays the results in hexadecimal. + +<programlisting> +DELIMITER // + +CREATE PROCEDURE p_convert (ucs2_char CHAR(1) CHARACTER SET ucs2) +BEGIN + +CREATE TABLE tj + (ucs2 CHAR(1) character set ucs2, + utf8 CHAR(1) character set utf8, + big5 CHAR(1) character set big5, + cp932 CHAR(1) character set cp932, + eucjpms CHAR(1) character set eucjpms, + euckr CHAR(1) character set euckr, + gb2312 CHAR(1) character set gb2312, + gbk CHAR(1) character set gbk, + sjis CHAR(1) character set sjis, + ujis CHAR(1) character set ujis); + +INSERT INTO tj (ucs2) VALUES (ucs2_char); + +UPDATE tj SET utf8=ucs2, + big5=ucs2, + cp932=ucs2, + eucjpms=ucs2, + euckr=ucs2, + gb2312=ucs2, + gbk=ucs2, + sjis=ucs2, + ujis=ucs2; + +/* If there's a conversion problem, UPDATE will produce a warning. */ + +SELECT hex(ucs2) AS ucs2, + hex(utf8) AS utf8, + hex(big5) AS big5, + hex(cp932) AS cp932, + hex(eucjpms) AS eucjpms, + hex(euckr) AS euckr, + hex(gb2312) AS gb2312, + hex(gbk) AS gbk, + hex(sjis) AS sjis, + hex(ujis) AS ujis +FROM tj; + +DROP TABLE tj; + +END// +</programlisting> + + The input can be any single <literal>ucs2</literal> character, + or it can be the code point value (hexadecimal representation) + of that character. Here's an example of what + <function>P_CONVERT()</function> can do. An earlier answer said + that the character <quote>Katakana Letter Pe</quote> appears in + all CJK character sets. We know that the code point value of + Katakana Letter Pe is <literal>0x30da</literal>. (By the way, we + got the name from Unicode's list of ucs2 encodings and names: + <ulink url="http://www.unicode.org/Public/UNIDATA/UnicodeData.txt"/>.) + So we'll say: + +<programlisting> +mysql> <userinput>CALL P_CONVERT(0x30da)//</userinput> ++------+--------+------+-------+---------+-------+--------+------+------+------+ +| 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) +</programlisting> + + Since none of the column values is <literal>3F</literal>, we + know that every conversion worked. + </para> + + </section> + + <section id="cjk-faq-sorting-problems-unicode-1"> + + <title>Strings don't sort correctly in Unicode (I)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>sorting problems</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>sort order problems</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>ORDER BY treatment</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>collations</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>using the right collation</secondary> + </indexterm> + + <para> + Sometimes people observe that the result of a + <literal>utf8_unicode_ci</literal> or + <literal>ucs2_unicode_ci</literal> search or <literal>ORDER + BY</literal> sort is not what they think a native would expect. + Although we never rule out the chance that there is a bug, we + have found in the past that people are not correctly reading the + standard table of weights for the Unicode Collation Algorithm. + So, here's how to check whether we're using the right collation. + The correct table for MySQL is this one: + <ulink url="http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt"/>. + This is different from the first table you will find by + navigating from the <literal>unicode.org</literal> home page. + MySQL deliberately uses the older 4.0.0 <quote>allkeys</quote> + table, instead of the current 4.1.0 table. We are very wary + about changing ordering which affects indexes. Here is an + example of a problem that we handled recently, for a complaint + in our bugs database, + <ulink url="http://bugs.mysql.com/bug.php?id=16526"/>: + +<programlisting> +mysql> <userinput>CREATE TABLE tj (s1 CHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci);</userinput> +Query OK, 0 rows affected (0.05 sec) + +mysql> <userinput>INSERT INTO tj VALUES ('が'),('か');</userinput> +Query OK, 2 rows affected (0.00 sec) +Records: 2 Duplicates: 0 Warnings: 0 + +mysql> <userinput>SELECT * FROM tj WHERE s1 = 'か';</userinput> ++------+ +| s1 | ++------+ +| が | +| か | ++------+ +2 rows in set (0.00 sec) +</programlisting> + + If your eyes are sharp, you'll see that the character in the + first result row isn't 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 <literal>ucs2</literal> version of the characters: + +<programlisting> +mysql> <userinput>SELECT s1,HEX(CONVERT(s1 USING ucs2)) FROM tj;</userinput> ++------+-----------------------------+ +| s1 | HEX(CONVERT(s1 USING ucs2)) | ++------+-----------------------------+ +| が | 304C | +| か | 304B | ++------+-----------------------------+ +2 rows in set (0.03 sec) +</programlisting> + + Now let's search for <literal>304B</literal> and + <literal>304C</literal> in the 4.0.0 allkeys table. We'll find + these lines: + +<programlisting> +304B ; [.1E57.0020.000E.304B] # HIRAGANA LETTER KA +304C ; [.1E57.0020.000E.304B][.0000.0140.0002.3099] # HIRAGANA LETTER GA; QQCM +</programlisting> + + The official Unicode names (following the <quote>#</quote> mark) + are informative; they tell us the Japanese syllabary (Hiragana), + the informal classification (letter instead of digit or + punctuation), and the Western identifier (<literal>KA</literal> + or <literal>GA</literal>, which happen to be voiced/unvoiced + components of the same letter pair). More importantly, the + Primary Weight (the first hexadecimal number inside the square + brackets) is <literal>1E57</literal> on both lines. For + comparisons in both searching and sorting, MySQL pays attention + only to the Primary Weight, it ignores all the other numbers. So + now we know that we're sorting <literal>が</literal> and + <literal>か</literal> correctly according to the Unicode + specification. If we wanted to distinguish them, we'd have to + use a non-Unicode-Collation-Algorithm collation + (<literal>utf8_unicode_bin</literal> or + <literal>utf8_general_ci</literal>), or compare the + <function>HEX()</function> values, or say <literal>ORDER BY + CONVERT(s1 USING sjis)</literal>. Being correct <quote>according + to Unicode</quote> 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, + where voiced/unvoiced letters like KA/GA are distinguishable for + ordering purposes. + </para> + + </section> + + <section id="cjk-faq-sorting-problems-unicode-2"> + + <title>Strings don't sort correctly in Unicode (II)</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>sorting problems</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>sort order problems</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>ORDER BY treatment</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>collations</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Unicode collations</secondary> + </indexterm> + + <para> + You're using Unicode (<literal>ucs2</literal> or + <literal>utf8</literal>), and you know what the Unicode sort + order is (see the previous question and answer), but MySQL still + seems to sort your table wrong? This might be easy. + +<programlisting> +mysql> <userinput>SHOW CREATE TABLE t\G</userinput> +******************** 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) +</programlisting> + + Hmm, the character set looks okay. Let's look at the + <literal>information_schema</literal> for this column. + +<programlisting> +mysql> <userinput>SELECT column_name, character_set_name, collation_name</userinput> + -> <userinput>FROM information_schema.columns</userinput> + -> <userinput>WHERE column_name = 's1'</userinput> + -> <userinput>AND table_name = 't';</userinput> ++-------------+--------------------+-----------------+ +| column_name | character_set_name | collation_name | ++-------------+--------------------+-----------------+ +| s1 | ucs2 | ucs2_general_ci | ++-------------+--------------------+-----------------+ +1 row in set (0.01 sec) +</programlisting> + + Oops, the collation is <literal>ucs2_general_ci</literal> + instead of <literal>ucs2_unicode_ci</literal>! Here's why: + +<programlisting> +mysql> <userinput>SHOW CHARSET LIKE 'ucs2%';</userinput> ++---------+---------------+-------------------+--------+ +| Charset | Description | Default collation | Maxlen | ++---------+---------------+-------------------+--------+ +| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | ++---------+---------------+-------------------+--------+ +1 row in set (0.00 sec) +</programlisting> + + For <literal>ucs2</literal> and <literal>utf8</literal>, the + <quote>general</quote> collation is the default. To specify that + you wanted a <quote>unicode</quote> collation, you should have + specified <literal>COLLATE ucs2_unicode_ci</literal>. + </para> + + </section> + + <section id="cjk-faq-supplementary-chars-rejected"> + + <title>My supplementary characters get rejected</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>rejected characters</secondary> + </indexterm> + + <para> + Right. MySQL doesn't support supplementary characters + (characters which need more than 3 bytes with UTF-8). We support + only what Unicode calls the <emphasis>Basic Multilingual Plane / + Plane 0</emphasis>. Only a few very rare Han characters are + supplementary; support for them is uncommon. This has led to bug + #12600 (<ulink url="http://bugs.mysql.com/bug.php?id=12600"/>) + which we rejected as <quote>not a bug</quote>. With + <literal>utf8</literal>, 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. A + workaround is: if you use <literal>ucs2</literal> instead of + <literal>utf8</literal>, then the bad characters will change to + question marks, but there will be no truncation. Or change the + data type to <literal>BLOB</literal> or + <literal>BINARY</literal>, which have no validity checking. In + our bugs database, bug #14052 + (<ulink url="http://bugs.mysql.com/bug.php?id=14052"/>) is a + feature request for Wikipedia, asking us to support + supplementary characters extending <literal>ucs2</literal> as + well as <literal>utf8</literal>. + </para> + + </section> + + <section id="cjk-faq-cjkv"> + + <title>Shouldn't it be CJKV (V for Vietnamese)?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>CJKV</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>Vietnamese</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Vietnamese</primary> + </indexterm> + + <para> + No. The term CJKV (Chinese Japanese Korean Vietnamese) refers to + 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. Another + question that has come up (once) is a request for specialized + Vietnamese collation, see + <ulink url="http://bugs.mysql.com/bug.php?id=4745"/>. We might + do something about it someday, if many more requests arise. + </para> + + </section> + + <section id="cjk-faq-fixing-cjk-problems"> + + <title>Will MySQL fix any CJK problems in version 5.1?</title> + + <remark role="update"> + [SH] Remove (or rewrite) whole section once the fixes it talks + about are implemented. + </remark> + + <para> + Yes. We're changing the names of files and directories. Here's + an example, using mysql as <literal>root</literal> under Linux: + + <orderedlist> + + <listitem> + <para> + Create a table with a name containing a Han character: + +<programlisting> +mysql> <userinput>CREATE TABLE tab_楮 (s1 INT);</userinput> +Query OK, 0 rows affected (0.07 sec) +</programlisting> + </para> + </listitem> + + <listitem> + <para> + Find out where MySQL stores database files: + +<programlisting> +mysql> <userinput>SHOW VARIABLES LIKE 'datadir';</userinput> ++---------------+-----------------------+ +| Variable_name | Value | ++---------------+-----------------------+ +| datadir | /usr/local/mysql/var/ | ++---------------+-----------------------+ +1 row in set (0.00 sec) +</programlisting> + </para> + </listitem> + + <listitem> + <para> + Look at the directory to see the MyISAM table files: + +<programlisting> +# cd /usr/local/mysql/var/dba +# dir tab_* +-rw-rw---- 1 root root 0 2006-05-16 10:22 tab_@stripped +-rw-rw---- 1 root root 1024 2006-05-16 10:22 tab_@stripped +-rw-rw---- 1 root root 8556 2006-05-16 10:22 tab_@stripped +</programlisting> + </para> + </listitem> + + </orderedlist> + + Notice that MySQL has converted the Han character to + <literal>@</literal> + (Unicode value of Han character), that + is, to a purely ASCII representation. This solves an old + problem, that database files weren't portable, because some + computers wouldn't allow <literal>楮</literal> in a file name. + Conversion to the new file names will be automatic when you + upgrade to version 5.1. This should take care of bug #6313 in + our bugs database, + <ulink url="http://bugs.mysql.com/bug.php?id=6313"/>. + </para> + + </section> + + <section id="cjk-faq-manual-translation"> + + <title>When will MySQL translate the manual again?</title> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>documentation in Chinese</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>documentation in Japanese</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>CJK (Chinese, Japanese, Korean)</primary> + <secondary>documentation in Korean</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Documentation</primary> + <secondary>in Chinese</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Documentation</primary> + <secondary>in Japanese</secondary> + </indexterm> + + <indexterm type="concept"> + <primary>Documentation</primary> + <secondary>in Korean</secondary> + </indexterm> + + <remark role="update"> + [SH] Update as CJK translations of manuals are updated. + </remark> + + <para> + A Beijing-based group has produced a Simplified Chinese version + for us under contract. It's complete and can be found on + <ulink url="http://dev.mysql.com/doc/#chinese-5.1"/>. It's up to + date as of version 5.1.2. The Japanese manual can be downloaded + from <ulink url="http://dev.mysql.com/doc/#japanese-4.1"/>. It + is still for version 4.1. + </para> + + </section> + + <section id="cjk-faq-contact"> + + <title>Whom can I talk to?</title> + + <remark role="update"> + [SH] Update if things change. + </remark> + + <para> + Check <ulink url="http://dev.mysql.com/user-groups/"/> to see if + there is a MySQL user group near you. If there isn't: why not + start one yourself? To contact a sales engineer in MySQL KK's + Japan office: + +<programlisting> +Tel: +81(0)3-5326-3133 +Fax: +81(0)3-5326-3001 +Email: dsaito@stripped +</programlisting> + + To see feature requests about language issues: + + <itemizedlist> + + <listitem> + <para> + Go to <ulink url="http://bugs.mysql.com"/>. + </para> + </listitem> + + <listitem> + <para> + Click <guimenu>Advanced Search</guimenu>. + </para> + </listitem> + + <listitem> + <para> + In the <guilabel>Severity</guilabel> dropdown box, click + <literal>S4 (Feature Request)</literal>. + </para> + </listitem> + + <listitem> + <para> + In the list box beside <guilabel>Category</guilabel>, + click <literal>Character Sets</literal>. + </para> + </listitem> + + <listitem> + <para> + Click the <guibutton>Search</guibutton> button. + </para> + </listitem> + + </itemizedlist> + + You can post CJK questions, or see previous answers, on MySQL's + <quote>Character Sets, Collation, Unicode</quote> forum: + <ulink url="http://forums.mysql.com/list.php?103"/>. MySQL plans + to add native-language forums on + <ulink url="http://forums.mysql.com/"/> very soon. + </para> + + </section> + + </section> + + <section id="faqs-connectors"> + + <title>MySQL ¤t-series; FAQ — Connectors</title> + + <para> + ¤t-series; Connectors FAQ. + </para> + + </section> + + <section id="faqs-tools"> + + <title>MySQL ¤t-series; FAQ — Tools</title> + + <para> + ¤t-series; Tools FAQ. + </para> + + </section> + +</chapter>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r3699 - trunk/refman-5.1 | jon | 23 Oct |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 2.143 sec. using MySQL 5.1.14-beta-log