Appendix A MySQL 5.5 Frequently Asked Questions

MySQL 5.7 and MySQL 5.6 are supported for production use.

MySQL 5.7 achieved General Availability (GA) status with MySQL 5.7.9, which was released for production use on 21 October 2015.

MySQL 5.6 achieved General Availability (GA) status with MySQL 5.6.10, which was released for production use on 5 February 2013.

MySQL 5.5 achieved General Availability (GA) status with MySQL 5.5.8, which was released for production use on 3 December 2010. The MySQL 5.5 series is no longer current, but still supported in production.

MySQL 5.1 achieved General Availability (GA) status with MySQL 5.1.30, which was released for production use on 14 November 2008. Active development for MySQL 5.1 has ended.

MySQL 5.0 achieved General Availability (GA) status with MySQL 5.0.15, which was released for production use on 19 October 2005. Active development for MySQL 5.0 has ended.

MySQL follows a milestone release model that introduces pre-production-quality features and stabilizes them to release quality (see http://dev.mysql.com/doc/mysql-development-cycle/en/index.html). This process then repeats, so releases cycle between pre-production and release quality status. Please check the change logs to identify the status of a given release.

MySQL 5.4 was a development series. Work on this series has ceased.

A successor to MySQL 5.7 is being actively developed using the milestone release methodology described above.

Yes. See Section 13.2.10, “Subquery Syntax”.

Yes. For the syntax required to perform multiple-table updates, see Section 13.2.11, “UPDATE Syntax”; for that required to perform multiple-table deletes, see Section 13.2.2, “DELETE Syntax”.

A multiple-table insert can be accomplished using a trigger whose FOR EACH ROW clause contains multiple INSERT statements within a BEGIN ... END block. See Section 20.3, “Using Triggers”.

Yes. The query cache operates on the server level, caching complete result sets matched with the original query string. If an exactly identical query is made (which often happens, particularly in web applications), no parsing or execution is necessary; the result is sent directly from the cache. Various tuning options are available. See Section 8.10.3, “The MySQL Query Cache”.

No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 5.5 can also handle inserts in a multi-master replication setup. With the auto_increment_increment and auto_increment_offset system variables, you can set each server to generate auto-increment values that don't conflict with other servers. The auto_increment_increment value should be greater than the number of servers, and each server should have a unique offset.

No, but support was added in 5.6.4.

Also, MySQL does parse time strings with a fractional component. See Section 11.3.2, “The TIME Type”.

Yes. MySQL is fully multi-threaded, and will make use of multiple CPUs, provided that the operating system supports them.

When using LinuxThreads, you should see a minimum of three mysqld processes running. These are in fact threads. There is one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.

Yes. All current MySQL versions support transactions. The InnoDB storage engine offers full ACID transactions with row-level locking, multi-versioning, nonlocking repeatable reads, and all four SQL standard isolation levels.

The NDB storage engine supports the READ COMMITTED transaction isolation level only.

See Chapter 15, Alternative Storage Engines. That chapter contains information about all MySQL storage engines except for the NDB storage engine used for MySQL Cluster; NDB is covered in Chapter 18, MySQL Cluster NDB 7.2.

The features from the optional InnoDB Plugin from MySQL 5.1 are folded into the built-in InnoDB storage engine, so you can take advantage of features such as the Barracuda file format, InnoDB table compression, and the new configuration options for performance. InnoDB also becomes the default storage engine for new tables. See Section 14.1.2, “InnoDB as the Default MySQL Storage Engine” for details.

No.

The ARCHIVE storage engine is ideally suited for storing large amounts of data without indexes; it has a very small footprint, and performs selects using table scans. See Section 15.6, “The ARCHIVE Storage Engine”, for details.

The general new features such as views, stored procedures, triggers, INFORMATION_SCHEMA, precision math (DECIMAL column type), and the BIT column type, apply to all storage engines. There are also additions and changes for specific storage engines.

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 Section 5.1.7, “Server SQL Modes”.

Each mode can be independently switched on and off. See Section 5.1.7, “Server SQL Modes”, for a complete list of available modes.

You can set the default SQL mode (for mysqld startup) with the --sql-mode option. Using the statement SET [GLOBAL|SESSION] sql_mode='modes', 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 SELECT @@sql_mode statement.

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 SET [GLOBAL|SESSION] sql_mode='modes'.

When we refer to strict mode, we mean a mode where at least one of the modes TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES is enabled. Options can be combined, so you can add restrictions to a mode. See Section 5.1.7, “Server SQL Modes”, for more information.

The intensive validation of input data that some settings requires more time than if the validation is not done. While the performance impact is not that great, if you do not require such validation (perhaps your application already handles all of this), then MySQL gives you the option of leaving strict mode disabled. However—if you do require it—strict mode can provide such validation.

By default, no special modes are enabled. For information about all available modes and MySQL's default behavior, see Section 5.1.7, “Server SQL Modes”.

Yes. MySQL 5.5 supports two types of stored routines—stored procedures and stored functions.

See Section 20.2, “Using Stored Routines (Procedures and Functions)”.

Yes. See http://forums.mysql.com/list.php?98.

Unfortunately, the official specifications are not freely available (ANSI makes them available for purchase). However, there are books—such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer—which give a comprehensive overview of the standard, including coverage of stored procedures.

It is always good practice to use a clear naming scheme for your stored routines. You can manage stored procedures with CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE], DROP [FUNCTION|PROCEDURE], and SHOW CREATE [FUNCTION|PROCEDURE]. You can obtain information about existing stored procedures using the ROUTINES table in the INFORMATION_SCHEMA database (see Section 21.17, “The INFORMATION_SCHEMA ROUTINES Table”).

Yes. For a database named dbname, use this query on the INFORMATION_SCHEMA.ROUTINES table:

SELECT ROUTINE_TYPE, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA='dbname';

For more information, see Section 21.17, “The INFORMATION_SCHEMA ROUTINES Table”.

The body of a stored routine can be viewed using SHOW CREATE FUNCTION (for a stored function) or SHOW CREATE PROCEDURE (for a stored procedure). See Section 13.7.5.11, “SHOW CREATE PROCEDURE Syntax”, for more information.

In the proc table of the mysql system database. However, you should not access the tables in the system database directly. Instead, use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.5.11, “SHOW CREATE PROCEDURE Syntax”, for more information about these statements.

You can also query the ROUTINES table in the INFORMATION_SCHEMA database—see Section 21.17, “The INFORMATION_SCHEMA ROUTINES Table”, for information about this table.

No. This is not supported in MySQL 5.5.

Yes.

A stored procedure can execute an SQL statement, such as an UPDATE, that causes a trigger to activate.

Yes. A stored procedure can access one or more tables as required.

Yes. MySQL 5.5 implements the SQL standard SIGNAL and RESIGNAL statements. See Section 13.6.7, “Condition Handling”.

MySQL implements HANDLER definitions according to the SQL standard. See Section 13.6.7.2, “DECLARE ... HANDLER Syntax”, for details.

Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client/server protocol for this to work. This means that—for instance—in PHP, you need to use the mysqli extension rather than the old mysql extension.

Not in MySQL 5.5.

There is no equivalent in MySQL 5.5.

Not in MySQL 5.5.

In MySQL 5.5, cursors are available inside stored procedures only.

In MySQL 5.5, cursors are available inside stored procedures only. However, if you do not open a cursor on a SELECT, the result will be sent directly to the client. You can also SELECT INTO variables. See Section 13.2.9, “SELECT Syntax”.

Yes, you can do this in a stored procedure, but not in a stored function. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You will need to use the MySQL 4.1 (or above) client/server protocol for this to work. This means that—for instance—in PHP, you need to use the mysqli extension rather than the old mysql extension.

Yes. However, you cannot perform transactional operations within a stored function.

Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in Section 20.7, “Binary Logging of Stored Programs”.

Yes, creation of stored procedures and functions carried out through normal DDL statements on a master server are replicated to a slave, so the objects will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.

MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.

Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:

Nondeterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master. Declaring stored functions to be DETERMINISTIC or setting the log_bin_trust_function_creators system variable to 0 will not allow random-valued operations to be invoked.

In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.

Finally, nontransactional tables for which errors occur during large DML actions (such as bulk inserts) may experience replication issues in that a master may be partially updated from DML activity, but no updates are done to the slave because of the errors that occurred. A workaround is for a function's DML actions to be carried out with the IGNORE keyword so that updates on the master that cause errors are ignored and updates that do not cause errors are replicated to the slave.

The same limitations that affect replication do affect point-in-time recovery.

You can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier.

Mixed replication is also available (by starting the server with --binlog-format=mixed). This hybrid, “smart” form of replication “knows” whether statement-level replication can safely be used, or row-level replication is required.

For additional information, see Section 17.1.2, “Replication Formats”.

See Section 20.3, “Using Triggers”.

Yes. It is available at http://forums.mysql.com/list.php?99.

In MySQL 5.5, all triggers are FOR EACH ROW—that is, the trigger is activated for each row that is inserted, updated, or deleted. MySQL 5.5 does not support triggers using FOR EACH STATEMENT.

Not explicitly. MySQL does have specific special behavior for some TIMESTAMP columns, as well as for columns which are defined using AUTO_INCREMENT.

In MySQL 5.5, triggers can be created using the CREATE TRIGGER statement, and dropped using DROP TRIGGER. See Section 13.1.19, “CREATE TRIGGER Syntax”, and Section 13.1.30, “DROP TRIGGER Syntax”, for more about these statements.

Information about triggers can be obtained by querying the INFORMATION_SCHEMA.TRIGGERS table. See Section 21.25, “The INFORMATION_SCHEMA TRIGGERS Table”.

Yes. You can obtain a listing of all triggers defined on database dbname using a query on the INFORMATION_SCHEMA.TRIGGERS table such as the one shown here:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
    FROM INFORMATION_SCHEMA.TRIGGERS
    WHERE TRIGGER_SCHEMA='dbname';

For more information about this table, see Section 21.25, “The INFORMATION_SCHEMA TRIGGERS Table”.

You can also use the SHOW TRIGGERS statement, which is specific to MySQL. See Section 13.7.5.39, “SHOW TRIGGERS Syntax”.

Triggers for a table are currently stored in .TRG files, with one such file one per table.

Yes.

A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

In MySQL 5.5, there cannot be multiple triggers for a given table that have the same trigger event and action time. For example, you cannot have two BEFORE UPDATE triggers for a table. This limitation is lifted in MySQL 5.7.

Yes. For example, a trigger could invoke the sys_exec() UDF.

Yes. A table on a remote server could be updated using the FEDERATED storage engine. (See Section 15.9, “The FEDERATED Storage Engine”).

Yes. However, the way in which they work depends whether you are using MySQL's “classic” statement-based replication available in all versions of MySQL, or the row-based replication format introduced in MySQL 5.1.

When using statement-based replication, triggers on the slave are executed by statements that are executed on the master (and replicated to the slave).

When using row-based replication, triggers are not executed on the slave due to statements that were run on the master and then replicated to the slave. Instead, when using row-based replication, the changes caused by executing the trigger on the master are applied on the slave.

For more information, see Section 17.4.1.36, “Replication and Triggers”.

Again, this depends on whether you are using statement-based or row-based replication.

Statement-based replication.  First, the triggers that exist on a master must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication. For example, consider a table EMP that has an AFTER insert trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave server as well. The replication flow would be:

Row-based replication.  When you use row-based replication, the changes caused by executing the trigger on the master are applied on the slave. However, the triggers themselves are not actually executed on the slave under row-based replication. This is because, if both the master and the slave applied the changes from the master and—in addition—the trigger causing these changes were applied on the slave, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.

In most cases, the outcome is the same for both row-based and statement-based replication. However, if you use different triggers on the master and slave, you cannot use row-based replication. (This is because the row-based format replicates the changes made by triggers executing on the master to the slaves, rather than the statements that caused the triggers to execute, and the corresponding triggers on the slave are not executed.) Instead, any statements causing such triggers to be executed must be replicated using statement-based replication.

For more information, see Section 17.4.1.36, “Replication and Triggers”.

See Section 20.5, “Using Views”.

Yes. See http://forums.mysql.com/list.php?100

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 CHECK TABLE statement. (See Section 13.7.2.2, “CHECK TABLE Syntax”.)

No.

No.

It is possible, provided that your INSERT statement has a column list that makes it clear there is only one table involved.

You cannot insert into multiple tables with a single insert on a view.

See Chapter 21, INFORMATION_SCHEMA Tables

See http://forums.mysql.com/list.php?101.

Unfortunately, the official specifications are not freely available. (ANSI makes them available for purchase.) However, there are books available—such as SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer—which give a comprehensive overview of the standard, including INFORMATION_SCHEMA.

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 INFORMATION_SCHEMA as defined in the SQL standard.

No. Since applications may rely on a certain standard structure, this should not be modified. For this reason, we cannot support bugs or other issues which result from modifying INFORMATION_SCHEMA tables or data.

For detailed upgrade information, see Section 2.11.1, “Upgrading MySQL”. Do not skip a major version when upgrading, but rather complete the process in steps, upgrading from one major version to the next in each step. This may seem more complicated, but it will you save time and trouble—if you encounter problems during the upgrade, their origin will be easier to identify, either by you or—if you have a MySQL Enterprise subscription—by MySQL support.

Storage engine support has changed as follows:

The best place to start is Chapter 6, Security.

Other portions of the MySQL Documentation which you may find useful with regard to specific security concerns include the following:

Most 5.5 binaries have support for SSL connections between the client and server. See Section 6.3.8, “Using Secure Connections”.

You can also tunnel a connection using SSH, if (for example) the client application does not support SSL connections. For an example, see Section 6.3.10, “Connecting to MySQL Remotely from Windows with SSH”.

Most 5.5 binaries have SSL enabled for client/server connections that are secured, authenticated, or both. See Section 6.3.8, “Using Secure Connections”.

The Enterprise edition includes a PAM Authentication Plugin that supports authentication against an LDAP directory.

Not at this time.

MySQL Cluster is not supported in standard MySQL Server 5.5 releases. Instead, MySQL Cluster is provided as a separate product. Currently, the following MySQL Cluster release series are available for production use:

You should use MySQL Cluster NDB 7.3 or MySQL Cluster NDB 7.4 for any new deployments; if you are using an older version of MySQL Cluster, you should upgrade to one of these soon as possible. For an overview of improvements made in MySQL Cluster NDB 7.4, see What is New in MySQL Cluster NDB 7.4; for information about improvements made in MySQL Cluster NDB 7.3, see What is New in MySQL Cluster NDB 7.3.

You can determine whether your MySQL Server has NDB support using one of the statements SHOW VARIABLES LIKE 'have_%', SHOW ENGINES, or SHOW PLUGINS.

“NDB” stands for “Network Database”. NDB and NDBCLUSTER are both names for the storage engine that enables clustering support with MySQL. NDB is preferred, but either name is correct.

In traditional MySQL replication, a master MySQL server updates one or more slaves. Transactions are committed sequentially, and a slow transaction can cause the slave to lag behind the master. This means that if the master fails, it is possible that the slave might not have recorded the last few transactions. If a transaction-safe engine such as InnoDB 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.

In short, whereas standard MySQL replication is asynchronous, MySQL Cluster is synchronous.

Asynchronous replication is also available in MySQL Cluster. MySQL Cluster Replication (also sometimes known as “geo-replication”) includes the capability to replicate both between two MySQL Clusters, and from a MySQL Cluster to a non-Cluster MySQL server. See Section 18.6, “MySQL Cluster Replication”.

MySQL Cluster is intended to be used in a high-bandwidth environment, with computers connecting using TCP/IP. Its performance depends directly upon the connection speed between the cluster's computers. The minimum connectivity requirements for MySQL Cluster include a typical 100-megabit Ethernet network or the equivalent. We recommend you use gigabit Ethernet whenever available.

A minimum of three computers is required to run a viable cluster. However, the minimum recommended 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.

To provide increased throughput and high availability, you should use multiple SQL nodes (MySQL Servers connected to the cluster). It is also possible (although not strictly necessary) to run multiple management servers.

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 nodes, and a computer housing a cluster node is sometimes referred to as a cluster host. There are three types of nodes, each corresponding to a specific role within the cluster. These are:

The simplest answer is, “It's not something you can control, and it's nothing that you need to worry about in any case, unless you're a software engineer writing or analyzing the MySQL Cluster source code”.

If you don't find that answer satisfactory, here's a longer and more technical version:

A number of mechanisms in MySQL Cluster require distributed coordination among the data nodes. These distributed algorithms and protocols include global checkpointing, DDL (schema) changes, and node restart handling. To make this coordination simpler, the data nodes “elect” one of their number to act as leader. (This node was once referred to as a “master”, but this terminology was dropped to avoid confusion with master server in MySQL Replication.) There is no user-facing mechanism for influencing this selection, which is completely automatic; the fact that it is automatic is a key part of MySQL Cluster's internal architecture.

When a node acts as the “leader” for any of these mechanisms, it is usually the point of coordination for the activity, and the other nodes act as “followers”, carrying out their parts of the activity as directed by the leader. If the node acting as leader fails, then the remaining nodes elect a new leader. Tasks in progress that were being coordinated by the old leader may either fail or be continued by the new leader, depending on the actual mechanism involved.

It is possible for some of these different mechanisms and protocols to have different leader nodes, but in general the same leader is chosen for all of them. The node indicated as the leader in the output of SHOW in the management client is known internally as the DICT manager (see The DBDICT Block, in the MySQL Cluster API Developer Guide, for more information), responsible for coordinating DDL and metadata activity.

MySQL Cluster is designed in such a way that the choice of leader has no discernible effect outside the cluster itself. For example, the current leader does not have significantly higher CPU or resource usage than the other data nodes, and failure of the leader should not have a significantly different impact on the cluster than the failure of any other data node.

MySQL Cluster is supported on most Unix-like operating systems. MySQL Cluster is also supported in production settings on Microsoft Windows operating systems.

For more detailed information concerning the level of support which is offered for MySQL Cluster on various operating system versions, operating system distributions, and hardware platforms, please refer to http://www.mysql.com/support/supportedplatforms/cluster.html.

MySQL Cluster should run on any platform for which NDB-enabled binaries are available. For data nodes and API nodes, faster CPUs and more memory are likely to improve performance, and 64-bit CPUs are likely to be more effective than 32-bit processors. There must be sufficient memory on machines used for data nodes to hold each node's share of the database (see How much RAM do I Need? for more information). For a computer which is used only for running the MySQL Cluster management server, the requirements are minimal; a common desktop PC (or the equivalent) is generally sufficient for this task. Nodes can communicate through the standard TCP/IP network and hardware. They can also use the high-speed SCI protocol; however, special networking hardware and software are required to use SCI (see Section 18.3.4, “Using High-Speed Interconnects with MySQL Cluster”).

Formerly MySQL Cluster was in-memory only. MySQL 5.1 and later also provide the ability to store MySQL Cluster on disk. (Note that we have no plans to backport this capability to previous releases.) See Section 18.5.12, “MySQL Cluster Disk Data Tables”, for more information.

For in-memory NDB tables, you can use the following formula for obtaining a rough estimate of how much RAM is needed for each data node in the cluster:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

To calculate the memory requirements more exactly requires determining, for each table in the cluster database, the storage space required per row (see Section 11.7, “Data Type Storage Requirements”, for details), and multiplying this by the number of rows. You must also remember to account for any column indexes as follows:

Creating MySQL Cluster tables with USING HASH 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 USING HASH 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.

When calculating Cluster memory requirements, you may find useful the ndb_size.pl utility which is available in recent MySQL 5.5 releases. This Perl script connects to a current (non-Cluster) MySQL database and creates a report on how much space that database would require if it used the NDBCLUSTER storage engine. For more information, see Section 18.4.24, “ndb_size.pl — NDBCLUSTER Size Requirement Estimator”.

It is especially important to keep in mind that every MySQL Cluster table must have a primary key. The NDB storage engine creates a primary key automatically if none is defined; this primary key is created without USING HASH.

You can determine how much memory is being used for storage of MySQL Cluster data and indexes at any given time using the REPORT MEMORYUSAGE command in the ndb_mgm client; see Section 18.5.2, “Commands in the MySQL Cluster Management Client”, for more information. In addition, warnings are written to the cluster log when 80% of available DataMemory or IndexMemory is in use, and again when usage reaches 85%, 90%, and so on.

Generally, any file system that is native to the host operating system should work well with MySQL Cluster. If you find that a given file system works particularly well (or not so especially well) with MySQL Cluster, we invite you to discuss your findings in the MySQL Cluster Forums.

For Windows, we recommend that you use NTFS file systems for MySQL Cluster, just as we do for standard MySQL. We do not test MySQL Cluster with FAT or VFAT file systems. Because of this, we do not recommend their use with MySQL or MySQL Cluster.

MySQL Cluster is implemented as a shared-nothing solution; the idea behind this is that the failure of a single piece of hardware should not cause the failure of multiple cluster nodes, or possibly even the failure of the cluster as a whole. For this reason, the use of network shares or network file systems is not supported for MySQL Cluster. This also applies to shared storage devices such as SANs.

MySQL Cluster is supported for use in virtual machines beginning with MySQL Cluster NDB 7.2. We currently support and test using Oracle VM.

Some MySQL Cluster users have successfully deployed MySQL Cluster using other virtualization products; in such cases, Oracle can provide MySQL Cluster support, but issues specific to the virtual environment must be referred to that product's vendor.

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.

It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. For example, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote at most 1GB to MySQL Cluster data and indexes.

In some cases it is possible to get Table is full errors in MySQL client applications even when ndb_mgm -e "ALL REPORT MEMORYUSAGE" shows significant free DataMemory. You can force NDB to create extra partitions for MySQL Cluster tables and thus have more memory available for hash indexes by using the MAX_ROWS option for CREATE TABLE. In general, setting MAX_ROWS to twice the number of rows that you expect to store in the table should be sufficient.

For similar reasons, you can also sometimes encounter problems with data node restarts on nodes that are heavily loaded with data. In MySQL Cluster NDB 7.1 and later, the addition of the MinFreePct parameter helps with this issue by reserving a portion (5% by default) of DataMemory and IndexMemory for use in restarts. This reserved memory is not available for storing NDB tables or data.

It is very unlikely that a cluster would perform reliably under such conditions, as MySQL Cluster was designed and implemented with the assumption that it would be run under conditions guaranteeing dedicated high-speed connectivity such as that found in a LAN setting using 100 Mbps or gigabit Ethernet—preferably the latter. We neither test nor warrant its performance using anything slower than this.

Also, it is extremely important to keep in mind that communications between the nodes in a MySQL Cluster are not secure; they are neither encrypted nor safeguarded by any other protective mechanism. The most secure configuration for a cluster is in a private network behind a firewall, with no direct access to any Cluster data or management nodes from outside. (For SQL nodes, you should take the same precautions as you would with any other instance of the MySQL server.) For more information, see Section 18.5.11, “MySQL Cluster Security Issues”.

No. Although some specialized commands are used to manage and configure the cluster itself, only standard (My)SQL statements are required for the following operations:

Some specialized configuration parameters and files are required to set up a MySQL Cluster—see Section 18.3.3, “MySQL Cluster Configuration Files”, for information about these.

A few simple commands are used in the MySQL Cluster management client (ndb_mgm) for tasks such as starting and stopping cluster nodes. See Section 18.5.2, “Commands in the MySQL Cluster Management Client”.

MySQL Cluster supports the same programming APIs and languages as the standard MySQL Server, including ODBC, .Net, the MySQL C API, and numerous drivers for popular scripting languages such as PHP, Perl, and Python. MySQL Cluster applications written using these APIs behave similarly to other MySQL applications; they transmit SQL statements to a MySQL Server (in the case of MySQL Cluster, an SQL node), and receive responses containing rows of data. For more information about these APIs, see Chapter 23, Connectors and APIs.

MySQL Cluster also supports application programming using the NDB API, which provides a low-level C++ interface to MySQL Cluster data without needing to go through a MySQL Server. See The NDB API. In addition, many NDBCLUSTER management functions are exposed by the C-language MGM API; see The MGM API, for more information.

MySQL Cluster NDB 7.1 and later also support Java application programming using ClusterJ, which supports a domain object model of data using sessions and transactions. See Java and MySQL Cluster, for more information.

MySQL Cluster NDB 7.2 adds support for memcached, allowing developers to access data stored in MySQL Cluster using the memcached interface; for more information, see ndbmemcache—Memcache API for MySQL Cluster.

MySQL Cluster includes a command line client for performing basic management functions. See Section 18.4.5, “ndb_mgm — The MySQL Cluster Management Client”, and Section 18.5.2, “Commands in the MySQL Cluster Management Client”.

MySQL Cluster NDB 7.0 and later is also supported by MySQL Cluster Manager, a separate product providing an advanced command line interface that can automate many MySQL Cluster management tasks such as rolling restarts and configuration changes. For more information about MySQL Cluster Manager, see MySQL™ Cluster Manager 1.3.6 User Manual.

There are two ways in which this can be done:

Yes. For tables created with the NDB storage engine, transactions are supported. Currently, MySQL Cluster supports only the READ COMMITTED transaction isolation level.

Clustering with MySQL is supported only by the NDB storage engine. That is, in order for a table to be shared between nodes in a MySQL Cluster, the table must be created using ENGINE=NDB (or the equivalent option ENGINE=NDBCLUSTER).

It is possible to create tables using other storage engines (such as InnoDB or MyISAM) on a MySQL server being used with a MySQL Cluster, but since these tables do not use NDB, they do not participate in clustering; each such table is strictly local to the individual MySQL server instance on which it is created.

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.)

FULLTEXT indexing is currently supported only by the MyISAM storage engine. See Section 12.9, “Full-Text Search Functions”, for more information.

It is possible but not always advisable. One of the chief reasons to run a cluster is to provide redundancy. To obtain the full benefits of this redundancy, each node should reside on a separate machine. If you place multiple nodes on a single machine and that machine fails, you lose all of those nodes. For this reason, if you do run multiple data nodes on a single machine, it is extremely important that they be set up in such a way that the failure of this machine does not cause the loss of all the data nodes in a given node group.

Given that MySQL Cluster can be run on commodity hardware loaded with a low-cost (or even no-cost) operating system, the expense of an extra machine or two is well worth it to safeguard mission-critical data. It also worth noting that the requirements for a cluster host running a management node are minimal. This task can be accomplished with a 300 MHz Pentium or equivalent CPU and sufficient RAM for the operating system, plus a small amount of overhead for the ndb_mgmd and ndb_mgm processes.

It is acceptable to run multiple cluster data nodes on a single host that has multiple CPUs, cores, or both. MySQL Cluster NDB 7.0 and later also provide a multi-threaded version of the data node binary intended for use on such systems. For more information, see Section 18.4.3, “ndbmtd — The MySQL Cluster Data Node Daemon (Multi-Threaded)”.

It is also possible in some cases to run data nodes and SQL nodes concurrently on the same machine; how well such an arrangement performs is dependent on a number of factors such as number of cores and CPUs as well as the amount of disk and memory available to the data node and SQL node processes, and you must take these factors into account when planning such a configuration.

Limitations on NDB tables in MySQL MySQL Cluster NDB 7.2 include the following:

For a complete listing of limitations in MySQL Cluster, see Section 18.1.6, “Known Limitations of MySQL Cluster”. See also Section 18.1.6.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x”.

MySQL Cluster NDB 7.2 does not support foreign key contraints, and ignores foreign keys in CREATE TABLE statements (similarly to how MyISAM treats foreign key syntax).

Foreign key support comparable to that found in the InnoDB storage engine is provided by NDB beginning with MySQL Cluster NDB 7.3. Applications requiring foreign key support should use MySQL Cluster NDB 7.3 or later.

You can import databases into MySQL Cluster much as you would with any other version of MySQL. Other than the limitations mentioned elsewhere in this FAQ, the only other special requirement is that any tables to be included in the cluster must use the NDB storage engine. This means that the tables must be created with ENGINE=NDB or ENGINE=NDBCLUSTER.

It is also possible to convert existing tables that use other storage engines to NDBCLUSTER using one or more ALTER TABLE statement. However, the definition of the table must be compatible with the NDBCLUSTER storage engine prior to making the conversion. In MySQL 5.5, an additional workaround is also required; see Section 18.1.6, “Known Limitations of MySQL Cluster”, for details.

Cluster nodes can communicate through any of three different transport mechanisms: TCP/IP, SHM (shared memory), and SCI (Scalable Coherent Interface). Where available, SHM is used by default between nodes residing on the same cluster host; however, this is considered experimental. SCI is a high-speed (1 gigabit per second and higher), high-availability protocol used in building scalable multi-processor systems; it requires special hardware and drivers. See Section 18.3.4, “Using High-Speed Interconnects with MySQL Cluster”, for more about using SCI as a transport mechanism for MySQL Cluster.

If one or more data nodes in a cluster fail, it is possible that not all cluster data nodes will be able to “see” one another. In fact, it is possible that two sets of data nodes might become isolated from one another in a network partitioning, also known as a “split-brain” scenario. This type of situation is undesirable because each set of data nodes tries to behave as though it is the entire cluster. An arbitrator is required to decide between the competing sets of data nodes.

When all data nodes in at least one node group are alive, network partitioning is not an issue, because no single subset of the cluster can form a functional cluster on its own. The real problem arises when no single node group has all its nodes alive, in which case network partitioning (the “split-brain” 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 ArbitrationRank configuration parameter for MySQL Server and management server nodes. In MySQL Cluster NDB 7.0.7 and later, you can also use the ArbitrationRank configuration parameter to control the arbitrator selection process. For more information about these parameters, see Section 18.3.3.5, “Defining a MySQL Cluster Management Server”.

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.

MySQL Cluster supports all of the usual MySQL data types, including those associated with MySQL's spatial extensions; however, the NDB storage engine does not support spatial indexes. (Spatial indexes are supported only by MyISAM; see Section 11.5, “Extensions for Spatial Data”, for more information.) In addition, there are some differences with regard to indexes when used with NDB tables.

See Section 18.1.6, “Known Limitations of MySQL Cluster”, for more information about these issues.

It is necessary to start each node in the cluster separately, in the following order:

Each of these commands must be run from a system shell on the machine housing the affected node. (You do not have to be physically present at the machine—a remote login shell can be used for this purpose.) You can verify that the cluster is running by starting the NDB management client ndb_mgm on the machine housing the management node and issuing the SHOW or ALL STATUS command.

To shut down a running cluster, issue the command SHUTDOWN in the management client. Alternatively, you may enter the following command in a system shell:

shell> ndb_mgm -e "SHUTDOWN"

(The quotation marks in this example are optional, since there are no spaces in the command string following the -e option; in addition, the SHUTDOWN command, like other management client commands, is not case-sensitive.)

Either of these commands causes the ndb_mgm, ndb_mgm, and any ndbd processes to terminate gracefully. MySQL servers running as SQL nodes can be stopped using mysqladmin shutdown.

For more information, see Section 18.5.2, “Commands in the MySQL Cluster Management Client”, and Section 18.2.6, “Safe Shutdown and Restart of MySQL Cluster”.

The data that was held in memory by the cluster's data nodes is written to disk, and is reloaded into memory the next time that the cluster is started.

It can be helpful as a fail-safe. Only one management node controls the cluster at any given time, but it is possible to configure one management node as primary, and one or more additional management nodes to take over in the event that the primary management node fails.

See Section 18.3.3, “MySQL Cluster Configuration Files”, for information on how to configure MySQL Cluster management nodes.

Yes, as long as all machines and operating systems have the same “endianness” (all big-endian or all little-endian).

It is also possible to use software from different MySQL Cluster releases on different nodes. However, we support this only as part of a rolling upgrade procedure (see Section 18.5.5, “Performing a Rolling Restart of a MySQL Cluster”).

Yes, it is possible to do this. In the case of multiple data nodes, it is advisable (but not required) for each node to use a different data directory. If you want to run multiple SQL nodes on one machine, each instance of mysqld must use a different TCP/IP port. However, in MySQL 5.5, running more than one cluster node of a given type per machine is generally not encouraged or supported for production use.

We also advise against running data nodes and SQL nodes together on the same host, since the ndbd and mysqld processes may compete for memory.

Yes, it is possible to use DNS and DHCP for cluster hosts. However, if your application requires “five nines” availability, you should use fixed (numeric) IP addresses, since making communication between Cluster hosts dependent on services such as DNS and DHCP introduces additional potential points of failure.

IPv6 is supported for connections between SQL nodes (MySQL servers), but connections between all other types of MySQL Cluster nodes must use IPv4.

In practical terms, this means that you can use IPv6 for replication between MySQL Clusters, but connections between nodes in the same MySQL Cluster must use IPv4. For more information, see Section 18.6.3, “Known Issues in MySQL Cluster Replication”.

MySQL user accounts and privileges are normally not automatically propagated between different MySQL servers accessing the same MySQL Cluster. Beginning with MySQL Cluster NDB 7.2, MySQL Cluster provides support for distributed privileges. While privilege distribution is not enabled automatically, you can activate it by following a procedure provided in the MySQL Cluster documentation. See Section 18.5.14, “Distributed MySQL Privileges for MySQL Cluster”, for more information.

MySQL Cluster does not provide any sort of automatic failover between SQL nodes. Your application must be prepared to handle the loss of SQL nodes and to fail over between them.

You can use the NDB native backup and restore functionality in the MySQL Cluster management client and the ndb_restore program. See Section 18.5.3, “Online Backup of MySQL Cluster”, and Section 18.4.20, “ndb_restore — Restore a MySQL Cluster Backup”.

You can also use the traditional functionality provided for this purpose in mysqldump and the MySQL server. See Section 4.5.4, “mysqldump — A Database Backup Program”, for more information.

This process monitors and, if necessary, attempts to restart the data node process. If you check the list of active processes on your system after starting ndbd, you can see that there are actually 2 processes running by that name, as shown here (we omit the output from ndb_mgmd and ndbd for brevity):

shell> ./ndb_mgmd

shell> ps aux | grep ndb
me      23002  0.0  0.0 122948  3104 ?        Ssl  14:14   0:00 ./ndb_mgmd
me      23025  0.0  0.0   5284   820 pts/2    S+   14:14   0:00 grep ndb

shell> ./ndbd -c 127.0.0.1 --initial

shell> ps aux | grep ndb
me      23002  0.0  0.0 123080  3356 ?        Ssl  14:14   0:00 ./ndb_mgmd
me      23096  0.0  0.0  35876  2036 ?        Ss   14:14   0:00 ./ndbd -c 127.0.0.1 --initial
me      23097  1.0  2.4 524116 91096 ?        Sl   14:14   0:00 ./ndbd -c 127.0.0.1 --initial
me      23168  0.0  0.0   5284   812 pts/2    R+   14:15   0:00 grep ndb

The ndbd process showing 0 memory and CPU usage is the angel process. It actually does use a very small amount of each, of course. It simply checks to see if the main ndbd process (the primary data node process that actually handles the data) is running. If permitted to do so (for example, if the StopOnError configuration parameter is set to false—see Section 18.3.2.1, “MySQL Cluster Data Node Configuration Parameters”), the angel process tries to restart the primary data node process.

The list of CJK character sets may vary depending on your MySQL version. For example, the gb18030 character set was not supported prior to MySQL 5.7.4. However, since the name of the applicable language appears in the DESCRIPTION column for every entry in the INFORMATION_SCHEMA.CHARACTER_SETS table, you can obtain a current list of all the non-Unicode CJK character sets using this query:

mysql> SELECT CHARACTER_SET_NAME, DESCRIPTION
    -> FROM INFORMATION_SCHEMA.CHARACTER_SETS
    -> WHERE DESCRIPTION LIKE '%Chin%'
    -> OR DESCRIPTION LIKE '%Japanese%'
    -> OR DESCRIPTION LIKE '%Korean%'
    -> ORDER BY CHARACTER_SET_NAME;
+--------------------+---------------------------------+
| CHARACTER_SET_NAME | DESCRIPTION                     |
+--------------------+---------------------------------+
| big5               | Big5 Traditional Chinese        |
| cp932              | SJIS for Windows Japanese       |
| eucjpms            | UJIS for Windows Japanese       |
| euckr              | EUC-KR Korean                   |
| gb18030            | China National Standard GB18030 |
| gb2312             | GB2312 Simplified Chinese       |
| gbk                | GBK Simplified Chinese          |
| sjis               | Shift-JIS Japanese              |
| ujis               | EUC-JP Japanese                 |
+--------------------+---------------------------------+
9 rows in set (0.01 sec)

(See Section 21.1, “The INFORMATION_SCHEMA CHARACTER_SETS Table”, for more information.)

MySQL supports three variants of the GB (Guojia Biaozhun, or National Standard, or Simplified Chinese) character sets which are official in the People's Republic of China: gb2312, gbk, and gb18030 (added in MySQL 5.7.4).

Sometimes people try to insert gbk characters into gb2312, and it works most of the time because gbk is a superset of gb2312—but eventually they try to insert a rarer Chinese character and it doesn't work. (See Bug #16072 for an example).

Here, we try to clarify exactly what characters are legitimate in gb2312 or gbk, with reference to the official documents. Please check these references before reporting gb2312 or gbk bugs.

This problem is usually due to a setting in MySQL that doesn't match the settings for the application program or the operating system. Here are some common steps for correcting these types of issues:

MySQL supports the Big5 character set which is common in Hong Kong and Taiwan (Republic of China). MySQL's big5 is in reality Microsoft code page 950, which is very similar to the original big5 character set. We changed to this character set starting with MySQL version 4.1.16 / 5.0.16 (as a result of Bug #12476). For example, the following statements work in current versions of MySQL, but not in old versions:

mysql> CREATE TABLE big5 (BIG5 CHAR(1) CHARACTER SET BIG5);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO big5 VALUES (0xf9dc);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM big5;
+------+
| big5 |
+------+
| 嫺  |
+------+
1 row in set (0.02 sec)

A feature request for adding HKSCS extensions has been filed. People who need this extension may find the suggested patch for Bug #13577 to be of interest.

MySQL supports the sjis, ujis, cp932, 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 sjis or ujis) and a Windows client (typically with cp932).

In the following conversion table, the ucs2 column represents the source, and the sjis, cp932, ujis, and eucjpms columns represent the destinations—that is, the last 4 columns provide the hexadecimal result when we use CONVERT(ucs2) or we assign a ucs2 column containing the value to an sjis, cp932, ujis, or eucjpms column.

Now consider the following portion of the table.

This means that MySQL converts the NOT SIGN (Unicode U+00AC) to sjis code point 0x81CA and to cp932 code point 3F. (3F is the question mark (“?”)—this is what is always used when the conversion cannot be performed.

Our answer is: “?”. There are serious complaints about this: many people would prefer a “loose” conversion, so that 81CA (NOT SIGN) in sjis becomes 81CA (FULLWIDTH NOT SIGN) in cp932. We are considering a change to this behavior.

A problem arises because some versions of Japanese character sets (both sjis and euc) treat 5C as a reverse solidus (\—also known as a backslash), and others treat it as a yen sign (¥).

MySQL follows only one version of the JIS (Japanese Industrial Standards) standard description. In MySQL, 5C is always the reverse solidus (\).

This is one possible solution to the Yen sign issue; however, this will not happen in MySQL 5.1 or 6.0.

In theory, while there have been several versions of the euckr (Extended Unix Code Korea) character set, only one problem has been noted.

We use the “ASCII” variant of EUC-KR, in which the code point 0x5c is REVERSE SOLIDUS, that is \, instead of the “KS-Roman” variant of EUC-KR, in which the code point 0x5c is WON SIGN(₩). This means that you cannot convert Unicode U+20A9 to euckr:

mysql> SELECT
    ->     CONVERT('₩' USING euckr) AS euckr,
    ->     HEX(CONVERT('₩' USING euckr)) AS hexeuckr;
+-------+----------+
| euckr | hexeuckr |
+-------+----------+
| ?     | 3F       |
+-------+----------+
1 row in set (0.00 sec)

MySQL's graphic Korean chart is here: euckr.

For illustration, we'll create a table with one Unicode (ucs2) column and one Chinese (gb2312) column.

mysql> CREATE TABLE ch
    -> (ucs2 CHAR(3) CHARACTER SET ucs2,
    -> gb2312 CHAR(3) CHARACTER SET gb2312);
Query OK, 0 rows affected (0.05 sec)

We'll try to place the rare character 汌 in both columns.

mysql> INSERT INTO ch VALUES ('A汌B','A汌B');
Query OK, 1 row affected, 1 warning (0.00 sec)

Ah, there is a warning. Use the following statement to see what it is:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect string value: '\xE6\xB1\x8CB' for column 'gb2312' at row 1
1 row in set (0.00 sec)

So it is a warning about the gb2312 column only.

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)

Several things need explanation here:

Obtain a direct connection to the server using the mysql client (Windows: mysql.exe), and try the same query there. If mysql responds correctly, then the trouble may be that your application interface requires initialization. Use mysql to tell you what character set or sets it uses with the statement SHOW VARIABLES LIKE 'char%';. If you are using Access, then you are most likely connecting with Connector/ODBC. In this case, you should check Configuring Connector/ODBC. If, for instance, you use big5, you would enter SET NAMES 'big5'. (Note that no ; is required in this case). If you are using ASP, you might need to add SET NAMES in the code. Here is an example that has worked in the past:

<%
Session.CodePage=0
Dim strConnection
Dim Conn
strConnection="driver={MySQL ODBC 3.51 Driver};server=server;uid=username;" \
               & "pwd=password;database=database;stmt=SET NAMES 'big5';"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
%>

In much the same way, if you are using any character set other than latin1 with Connector/Net, then you must specify the character set in the connection string. See Connecting to MySQL Using Connector/Net, for more information.

If you are using PHP, try this:

<?php
  $link = new mysqli($host, $usr, $pwd, $db);

  if( mysqli_connect_errno() )
  {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

  $link->query("SET NAMES 'utf8'");
?>

In this case, we used SET NAMES to change character_set_client and character_set_connection and character_set_results.

Another issue often encountered in PHP applications has to do with assumptions made by the browser. Sometimes adding or changing a <meta> tag suffices to correct the problem: for example, to insure that the user agent interprets page content as UTF-8, you should include <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> in the <head> of the HTML page.

If you are using Connector/J, see Using Character Sets and Unicode.

In MySQL Version 4.0, there was a single “global” character set for both server and client, and the decision as to which character to use was made by the server administrator. This changed starting with MySQL Version 4.1. What happens now is a “handshake”, as described in Section 10.1.4, “Connection Character Sets and Collations”:

The effect of this is that you cannot control the client character set by starting mysqld with --character-set-server=utf8. However, some of our Asian customers have said that they prefer the MySQL 4.0 behavior. To make it possible to retain this behavior, we added a mysqld switch, --character-set-client-handshake, which can be turned off with --skip-character-set-client-handshake. If you start mysqld with --skip-character-set-client-handshake, then, when a client connects, it sends to the server the name of the character set that it wants to use—however, the server ignores this request from the client.

By way of example, suppose that your favorite server character set is latin1 (unlikely in a CJK area, but this is the default value). Suppose further that the client uses utf8 because this is what the client's operating system supports. Now, start the server with latin1 as its default character set:

mysqld --character-set-server=latin1

And then start the client with the default character set utf8:

mysql --default-character-set=utf8

The current settings can be seen by viewing the output of SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| 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)

Now stop the client, and then stop the server using mysqladmin. Then start the server again, but this time tell it to skip the handshake like so:

mysqld --character-set-server=utf8 --skip-character-set-client-handshake

Start the client with utf8 once again as the default character set, then display the current settings:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------------------+
| 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)

As you can see by comparing the differing results from SHOW VARIABLES, the server ignores the client's initial settings if the --skip-character-set-client-handshake is used.

There is a very simple problem with LIKE searches on BINARY and BLOB columns: we need to know the end of a character. With multibyte character sets, different characters might have different octet lengths. For example, in utf8, A requires one byte but ペ requires three bytes, as shown here:

+-------------------------+---------------------------+
| OCTET_LENGTH(_utf8 'A') | OCTET_LENGTH(_utf8 'ペ') |
+-------------------------+---------------------------+
|                       1 |                         3 |
+-------------------------+---------------------------+
1 row in set (0.00 sec)

If we don't know where the first character ends, then we don't know where the second character begins, in which case even very simple searches such as LIKE '_A%' fail. The solution is to use a regular CJK character set in the first place, or to convert to a CJK character set before comparing.

This is one reason why MySQL cannot allow encodings of nonexistent characters. If it is not strict about rejecting bad input, then it has no way of knowing where characters end.

For FULLTEXT searches, we need to know where words begin and end. With Western languages, this is rarely a problem because most (if not all) of these use an easy-to-identify word boundary—the space character. However, this is not usually the case with Asian writing. We could use arbitrary halfway measures, like assuming that all Han characters represent words, or (for Japanese) depending on changes from Katakana to Hiragana due to grammatical endings. However, the only sure solution requires a comprehensive word list, which means that we would have to include a dictionary in the server for each Asian language supported. This is simply not feasible.

The majority of simplified Chinese and basic nonhalfwidth Japanese Kana characters appear in all CJK character sets. This stored procedure accepts a UCS-2 Unicode character, converts it to all other character sets, and displays the results in hexadecimal.

DELIMITER //

CREATE PROCEDURE p_convert(ucs2_char CHAR(1) CHARACTER SET ucs2)
BEGIN

CREATE TABLE tj
             (ucs2 CHAR(1) character set ucs2,
              utf8 CHAR(1) character set utf8,
              big5 CHAR(1) character set big5,
              cp932 CHAR(1) character set cp932,
              eucjpms CHAR(1) character set eucjpms,
              euckr CHAR(1) character set euckr,
              gb2312 CHAR(1) character set gb2312,
              gbk CHAR(1) character set gbk,
              sjis CHAR(1) character set sjis,
              ujis CHAR(1) character set ujis);

INSERT INTO tj (ucs2) VALUES (ucs2_char);

UPDATE tj SET utf8=ucs2,
              big5=ucs2,
              cp932=ucs2,
              eucjpms=ucs2,
              euckr=ucs2,
              gb2312=ucs2,
              gbk=ucs2,
              sjis=ucs2,
              ujis=ucs2;

/* If there is a conversion problem, UPDATE will produce a warning. */

SELECT hex(ucs2) AS ucs2,
       hex(utf8) AS utf8,
       hex(big5) AS big5,
       hex(cp932) AS cp932,
       hex(eucjpms) AS eucjpms,
       hex(euckr) AS euckr,
       hex(gb2312) AS gb2312,
       hex(gbk) AS gbk,
       hex(sjis) AS sjis,
       hex(ujis) AS ujis
FROM tj;

DROP TABLE tj;

END//

The input can be any single ucs2 character, or it can be the code point value (hexadecimal representation) of that character. For example, from Unicode's list of ucs2 encodings and names (http://www.unicode.org/Public/UNIDATA/UnicodeData.txt), we know that the Katakana character Pe appears in all CJK character sets, and that its code point value is 0x30da. If we use this value as the argument to p_convert(), the result is as shown here:

mysql> CALL p_convert(0x30da)//
+------+--------+------+-------+---------+-------+--------+------+------+------+
| 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)

Since none of the column values is 3F—that is, the question mark character (?)—we know that every conversion worked.

Sometimes people observe that the result of a utf8_unicode_ci or ucs2_unicode_ci search, or of an ORDER BY sort is not what they think a native would expect. Although we never rule out the possibility that there is a bug, we have found in the past that many people do not read correctly the standard table of weights for the Unicode Collation Algorithm. MySQL uses the table found at http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. This is not the first table you will find by navigating from the unicode.org home page, because MySQL uses the older 4.0.0 “allkeys” table, rather than the more recent 4.1.0 table. (The newer '520' collations in MySQL 5.6 use the 5.2 “allkeys” table.) This is because we are very wary about changing ordering which affects indexes, lest we bring about situations such as that reported in Bug #16526, illustrated as follows:

mysql< CREATE TABLE tj (s1 CHAR(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tj VALUES ('が'),('か');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tj WHERE s1 = 'か';
+------+
| s1   |
+------+
| が  |
| か  |
+------+
2 rows in set (0.00 sec)

The character in the first result row is not the one that we searched for. Why did MySQL retrieve it? First we look for the Unicode code point value, which is possible by reading the hexadecimal number for the ucs2 version of the characters:

mysql> SELECT s1, HEX(CONVERT(s1 USING ucs2)) FROM tj;
+------+-----------------------------+
| s1   | HEX(CONVERT(s1 USING ucs2)) |
+------+-----------------------------+
| が  | 304C                        |
| か  | 304B                        |
+------+-----------------------------+
2 rows in set (0.03 sec)

Now we search for 304B and 304C in the 4.0.0 allkeys table, and find these lines:

304B  ; [.1E57.0020.000E.304B] # HIRAGANA LETTER KA
304C  ; [.1E57.0020.000E.304B][.0000.0140.0002.3099] # HIRAGANA LETTER GA; QQCM

The official Unicode names (following the “#” mark) tell us the Japanese syllabary (Hiragana), the informal classification (letter, digit, or punctuation mark), and the Western identifier (KA or GA, which happen to be voiced and unvoiced components of the same letter pair). More importantly, the primary weight (the first hexadecimal number inside the square brackets) is 1E57 on both lines. For comparisons in both searching and sorting, MySQL pays attention to the primary weight only, ignoring all the other numbers. This means that we are sorting が and か correctly according to the Unicode specification. If we wanted to distinguish them, we'd have to use a non-UCA (Unicode Collation Algorithm) collation (utf8_bin or utf8_general_ci), or to compare the HEX() values, or use ORDER BY CONVERT(s1 USING sjis). Being correct “according to Unicode” isn't enough, of course: the person who submitted the bug was equally correct. We plan to add another collation for Japanese according to the JIS X 4061 standard, in which voiced/unvoiced letter pairs like KA/GA are distinguishable for ordering purposes.

If you are using Unicode (ucs2 or utf8), and you know what the Unicode sort order is (see Section A.11, “MySQL 5.5 FAQ: MySQL Chinese, Japanese, and Korean Character Sets”), but MySQL still seems to sort your table incorrectly, then you should first verify the table character set:

mysql> SHOW CREATE TABLE t\G
******************** 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)

Since the character set appears to be correct, let's see what information the INFORMATION_SCHEMA.COLUMNS table can provide about this column:

mysql> SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE COLUMN_NAME = 's1'
    -> AND TABLE_NAME = 't';
+-------------+--------------------+-----------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+-------------+--------------------+-----------------+
| s1          | ucs2               | ucs2_general_ci |
+-------------+--------------------+-----------------+
1 row in set (0.01 sec)

(See Section 21.4, “The INFORMATION_SCHEMA COLUMNS Table”, for more information.)

You can see that the collation is ucs2_general_ci instead of ucs2_unicode_ci. The reason why this is so can be found using SHOW CHARSET, as shown here:

mysql> SHOW CHARSET LIKE 'ucs2%';
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| ucs2    | UCS-2 Unicode | ucs2_general_ci   |      2 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

For ucs2 and utf8, the default collation is “general”. To specify a Unicode collation, use COLLATE ucs2_unicode_ci.

Before MySQL 5.5.3, MySQL does not support supplementary characters—that is, characters which need more than 3 bytes—for UTF-8. We support only what Unicode calls the Basic Multilingual Plane / Plane 0. Only a few very rare Han characters are supplementary; support for them is uncommon. This has led to reports such as that found in Bug #12600, which we rejected as “not a bug”. With utf8, we must truncate an input string when we encounter bytes that we don't understand. Otherwise, we wouldn't know how long the bad multibyte character is.

One possible workaround is to use ucs2 instead of utf8, in which case the “bad” characters are changed to question marks; however, no truncation takes place. You can also change the data type to BLOB or BINARY, which perform no validity checking.

As of MySQL 5.5.3, Unicode support is extended to include supplementary characters by means of additional Unicode character sets: utf16, utf32, and 4-byte utf8mb4. These character sets support supplementary Unicode characters outside the Basic Multilingual Plane (BMP).

No. The term “CJKV” (Chinese Japanese Korean Vietnamese) refers to Vietnamese character sets which contain Han (originally Chinese) characters. MySQL has no plan to support the old Vietnamese script using Han characters. MySQL does of course support the modern Vietnamese script with Western characters.

As of MySQL 5.6, there are Vietnamese collations for Unicode character sets, as described in Section 10.1.14.1, “Unicode Character Sets”.

This issue is fixed in MySQL 5.1, by automatically rewriting the names of the corresponding directories and files.

For example, if you create a database named 楮 on a server whose operating system does not support CJK in directory names, MySQL creates a directory named @0w@00a5@00ae, which is just a fancy way of encoding E6A5AE—that is, the Unicode hexadecimal representation for the 楮 character. However, if you run a SHOW DATABASES statement, you can see that the database is listed as 楮.

The following resources are available:

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.

To ensure that catchup can occur for a slave that has been disconnected, you must not remove binary log files from the master that contain information that has not yet been replicated to the slaves. Asynchronous replication can work only if the slave is able to continue reading the binary log from the point where it last read events.

Yes, networking must be enabled on the master and slave. If networking is not enabled, the slave cannot connect to the master and transfer the binary log. Check that the skip-networking option has not been enabled in the configuration file for either server.

Check the Seconds_Behind_Master column in the output from SHOW SLAVE STATUS. See Section 17.1.4.1, “Checking Replication Status”.

When the slave SQL thread executes an event read from the master, it modifies its own time to the event timestamp. (This is why TIMESTAMP is well replicated.) In the Time column in the output of SHOW PROCESSLIST, 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 large Time values such as 3600 for the slave SQL thread in SHOW PROCESSLIST. This is because the slave is executing statements that are one hour old. See Section 17.2.1, “Replication Implementation Details”.

Use the following procedure:

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.

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.

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 --skip-innodb, --low-priority-updates, and --delay-key-write=ALL options to get speed improvements on the slave end. In this case, the slave uses nontransactional MyISAM tables instead of InnoDB tables to get more speed by eliminating transactional overhead.

See the guide to using replication as a scale-out solution, Section 17.3.3, “Using Replication for Scale-Out”.

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.

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 must know your query patterns, and determine empirically by benchmarking the relationship between the throughput for reads and writes 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. Let reads and writes denote the number of reads and writes per second, respectively.

Let's say that system load consists of 10% writes and 90% reads, and we have determined by benchmarking that reads is 1200 - 2 * writes. 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. Suppose that the master and each slave have the same capacity, and that we have one master and N slaves. Then we have for each server (master or slave):

reads = 1200 - 2 * writes

reads = 9 * writes / (N + 1) (reads are split, but writes replicated to all slaves)

9 * writes / (N + 1) + 2 * writes = 1200

writes = 1200 / (2 + 9/(N + 1))

The last equation indicates the maximum number of writes for N slaves, given a maximum possible read rate of 1,200 per second and a ratio of nine reads per write.

This analysis yields the following conclusions:

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 N replication slaves. However, answering the following questions should help you decide whether and by how much replication will improve the performance of your system:

How you implement redundancy is entirely dependent on your application and circumstances. High-availability solutions (with automatic failover) require active monitoring and either custom scripts or third party tools to provide the failover support from the original MySQL server to the slave.

To handle the process manually, you should be able to switch from a failed master to a pre-configured slave by altering your application to talk to the new server or by adjusting the DNS for the MySQL server from the failed server to the new server.

For more information and some example solutions, see Section 17.3.6, “Switching Masters During Failover”.

Check the value of the binlog_format system variable:

mysql> SHOW VARIABLES LIKE 'binlog_format';

The value shown will be one of STATEMENT, ROW, or MIXED. For MIXED mode, statement-based logging is used by default but replication switches automatically to row-based logging under certain conditions, such as unsafe statements. For information about when this may occur, see Section 5.2.4.3, “Mixed Binary Logging Format”.

Slaves automatically know which format to use.

Start the server with the --replicate-wild-ignore-table=mysql.% option to ignore replication for tables in the mysql database.

Yes.

Yes.

The MySQL Thread Pool is a MySQL server plugin that extends the default connection-handling capabilities of the MySQL server to limit the number of concurrently executing statements/queries and transactions to ensure that each has sufficient CPU and memory resources to fulfill its task. Commercial distributions of MySQL 5.5 and 5.6 include the Thread Pool plugin.

The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The Thread Pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The Thread Pool plugin increases server performance by efficiently managing statement execution threads for large numbers of client connections, especially on modern multi-CPU/Core systems.

For more information, see Section 8.12.7, “The Thread Pool Plugin”.

The Thread Pool uses a “divide and conquer” approach to limiting and balancing concurrency. Unlike the default connection handling of the MySQL Server, the Thread Pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. The Thread Pool then manages client connections within configurable thread groups, where they are prioritized and queued based on the nature of the work they were submitted to accomplish.

For more information, see Section 8.12.7.2, “Thread Pool Operation”.

The MySQL Connection Pool operates on the client side to ensure that a MySQL client does not constantly connect to and disconnect from the MySQL server. It is designed to cache idle connections in the MySQL client for use by other users as they are needed. This minimizes the overhead and expense of establishing and tearing down connections as queries are submitted to the MySQL server. The MySQL Connection Pool has no visibility as to the query handling capabilities or load of the backend MySQL server. By contrast, the Thread Pool operates on the MySQL server side and is designed to manage the execution of inbound concurrent connections and queries as they are received from the client connections accessing the backend MySQL database. Because of the separation of duties, the MySQL Connection Pool and Thread Pool are orthogonal and can be used independent of each other.

MySQL Connection Pooling via the MySQL Connectors is covered in Chapter 23, Connectors and APIs.

There are a few rules of thumb to consider for optimal Thread Pool use cases:

The MySQL Threads_running variable keeps track of the number of concurrent statements currently executing in the MySQL Server. If this variable consistently exceeds a region where the server won't operate optimally (usually going beyond 40 for InnoDB workloads), the Thread Pool will be beneficial, especially in extreme parallel overload situations.

If you are using the innodb_thread_concurrency to limit the number of concurrently executing statements, you will find the Thread Pool solves the same problem, only better, by assigning connections to thread groups, then queuing executions based on transactional content, user defined designations, and so forth.

Lastly, if your workload comprises mainly short queries, the Thread Pool will be beneficial.

To learn more, see Section 8.12.7.3, “Thread Pool Tuning”.

The Thread Pool has a number of user case driven configuration parameters that affect its performance. To learn about these and tips on tuning, see Section 8.12.7.3, “Thread Pool Tuning”.