Appendix A. MySQL 6.0 Frequently Asked Questions

Table of Contents

A.1. MySQL 6.0 FAQ — General
A.2. MySQL 6.0 FAQ — Storage Engines
A.3. MySQL 6.0 FAQ — Server SQL Mode
A.4. MySQL 6.0 FAQ — Stored Procedures and Functions
A.5. MySQL 6.0 FAQ — Triggers
A.6. MySQL 6.0 FAQ — Views
A.7. MySQL 5.0 FAQ — INFORMATION_SCHEMA
A.8. MySQL 6.0 FAQ — Migration
A.9. MySQL 6.0 FAQ — Security
A.10. MySQL 6.0 FAQ — MySQL Cluster
A.11. MySQL 6.0 FAQ — MySQL Chinese, Japanese, and Korean Character Sets
A.12. MySQL 6.0 FAQ — Connectors & APIs
A.13. MySQL 6.0 FAQ — Replication
A.14. MySQL 6.0 FAQ — MySQL, DRBD, and Heartbeat
A.14.1. Distributed Replicated Block Device (DRBD) Basics
A.14.2. Linux Heartbeat
A.14.3. DRBD Architecture
A.14.4. DRBD and MySQL Replication
A.14.5. DRBD and File Systems
A.14.6. DRBD and LVM
A.14.7. DRBD and Virtualization
A.14.8. DRBD and Security
A.14.9. DRBD and System Requirements
A.14.10. DBRD and Support and Consulting

A.1. MySQL 6.0 FAQ — General

Questions

  • 22.1.1: Which version of MySQL is production-ready (GA)?

  • 22.1.2: Can MySQL 6.0 do subqueries?

  • 22.1.3: Can MySQL 6.0 perform multiple-table inserts, updates, and deletes?

  • 22.1.4: Does MySQL 6.0 have a Query Cache? Does it work on Server, Instance or Database?

  • 22.1.5: Does MySQL 6.0 have Sequences?

  • 22.1.6: Does MySQL 6.0 have a NOW() function with fractions of seconds?

  • 22.1.7: Does MySQL 6.0 work with multi-core processors?

  • 22.1.8: Is there a hot backup tool for MyISAM like InnoDB Hot Backup?

  • 22.1.9: Have there been there any improvements in error reporting when foreign keys fail? Does MySQL now report which column and reference failed?

  • 22.1.10: Can MySQL 6.0 perform ACID transactions?

Questions and Answers

22.1.1: Which version of MySQL is production-ready (GA)?

Currently, both MySQL 5.0 and MySQL 5.1 are supported for production use.

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

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

Development work on MySQL 5.4 has started; currently, MySQL 5.4 is in beta status.

Development work on MySQL 6.0 has started; currently, MySQL 6.0 is in alpha status.

22.1.2: Can MySQL 6.0 do subqueries?

Yes. See Section 12.2.10, “Subquery Syntax”.

22.1.3: Can MySQL 6.0 perform multiple-table inserts, updates, and deletes?

Yes. For the syntax required to perform multiple-table updates, see Section 12.2.12, “UPDATE Syntax”; for that required to perform multiple-table deletes, see Section 12.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 18.3, “Using Triggers”.

22.1.4: Does MySQL 6.0 have a Query Cache? Does it work on Server, Instance or Database?

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 7.5.5, “The MySQL Query Cache”.

22.1.5: Does MySQL 6.0 have Sequences?

No. However, MySQL has an AUTO_INCREMENT system, which in MySQL 6.0 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.

22.1.6: Does MySQL 6.0 have a NOW() function with fractions of seconds?

No. This is on the MySQL roadmap as a “rolling feature”. This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling.

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

22.1.7: Does MySQL 6.0 work with multi-core processors?

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

22.1.8: Is there a hot backup tool for MyISAM like InnoDB Hot Backup?

This is currently under development for a future MySQL release.

22.1.9: Have there been there any improvements in error reporting when foreign keys fail? Does MySQL now report which column and reference failed?

The foreign key support in InnoDB has seen improvements in each major version of MySQL. Foreign key support generic to all storage engines is scheduled for MySQL 6.x; this should resolve any inadequacies in the current storage engine specific implementation.

22.1.10: Can MySQL 6.0 perform ACID transactions?

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.

A.2. MySQL 6.0 FAQ — Storage Engines

Questions

  • 22.2.1: Where can I obtain complete documentation for MySQL storage engines and the pluggable storage engine architecture?

  • 22.2.2: Are there any new storage engines in MySQL 6.0?

  • 22.2.3: Have any storage engines been removed in MySQL 6.0?

  • 22.2.4: What are the unique benefits of the ARCHIVE storage engine?

  • 22.2.5: Do the new features in MySQL 6.0 apply to all storage engines?

Questions and Answers

22.2.1: Where can I obtain complete documentation for MySQL storage engines and the pluggable storage engine architecture?

See Chapter 13, 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 MySQL Cluster NDB 6.X/7.X.

MySQL Enterprise For expert advice about the storage engine(s) most suitable to your circumstances subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

22.2.2: Are there any new storage engines in MySQL 6.0?

MySQL 6.0 introduces an alpha version of the new Falcon storage engine.

For information about the Falcon storage engine, see Section 13.8, “The Falcon Storage Engine”.

There have also been significant improvements in existing storage engines, in particular for the NDB storage engine that forms the basis for MySQL Cluster.

22.2.3: Have any storage engines been removed in MySQL 6.0?

Yes. MySQL 6.0 no longer supports the BDB storage engine. Any existing BDB tables should be converted to another storage engine before upgrading to MySQL 6.0.

22.2.4: What are the unique benefits of the ARCHIVE storage engine?

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 13.14, “The ARCHIVE Storage Engine”, for details.

22.2.5: Do the new features in MySQL 6.0 apply to all storage engines?

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.

A.3. MySQL 6.0 FAQ — Server SQL Mode

Questions

  • 22.3.1: What are server SQL modes?

  • 22.3.2: How many server SQL modes are there?

  • 22.3.3: How do you determine the server SQL mode?

  • 22.3.4: Is the mode dependent on the database or connection?

  • 22.3.5: Can the rules for strict mode be extended?

  • 22.3.6: Does strict mode impact performance?

  • 22.3.7: What is the default server SQL mode when My SQL 6.0 is installed?

Questions and Answers

22.3.1: What are server SQL modes?

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

22.3.2: How many server SQL modes are there?

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

22.3.3: How do you determine the server SQL mode?

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.

22.3.4: Is the mode dependent on the database or connection?

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

22.3.5: Can the rules for strict mode be extended?

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 additional restrictions to a mode. See Section 5.1.8, “Server SQL Modes”, for more information.

22.3.6: Does strict mode impact performance?

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.

22.3.7: What is the default server SQL mode when My SQL 6.0 is installed?

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

A.4. MySQL 6.0 FAQ — Stored Procedures and Functions

Questions

  • 22.4.1: Does MySQL 6.0 support stored procedures and functions?

  • 22.4.2: Where can I find documentation for MySQL stored procedures and stored functions?

  • 22.4.3: Is there a discussion forum for MySQL stored procedures?

  • 22.4.4: Where can I find the ANSI SQL 2003 specification for stored procedures?

  • 22.4.5: How do you manage stored routines?

  • 22.4.6: Is there a way to view all stored procedures and stored functions in a given database?

  • 22.4.7: Where are stored procedures stored?

  • 22.4.8: Is it possible to group stored procedures or stored functions into packages?

  • 22.4.9: Can a stored procedure call another stored procedure?

  • 22.4.10: Can a stored procedure call a trigger?

  • 22.4.11: Can a stored procedure access tables?

  • 22.4.12: Do stored procedures have a statement for raising application errors?

  • 22.4.13: Do stored procedures provide exception handling?

  • 22.4.14: Can MySQL 6.0 stored routines return result sets?

  • 22.4.15: Is WITH RECOMPILE supported for stored procedures?

  • 22.4.16: Is there a MySQL equivalent to using mod_plsql as a gateway on Apache to talk directly to a stored procedure in the database?

  • 22.4.17: Can I pass an array as input to a stored procedure?

  • 22.4.18: Can I pass a cursor as an IN parameter to a stored procedure?

  • 22.4.19: Can I return a cursor as an OUT parameter from a stored procedure?

  • 22.4.20: Can I print out a variable's value within a stored routine for debugging purposes?

  • 22.4.21: Can I commit or roll back transactions inside a stored procedure?

  • 22.4.22: Do MySQL 6.0 stored procedures and functions work with replication?

  • 22.4.23: Are stored procedures and functions created on a master server replicated to a slave?

  • 22.4.24: How are actions that take place inside stored procedures and functions replicated?

  • 22.4.25: Are there special security requirements for using stored procedures and functions together with replication?

  • 22.4.26: What limitations exist for replicating stored procedure and function actions?

  • 22.4.27: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?

  • 22.4.28: What is being done to correct the aforementioned limitations?

Questions and Answers

22.4.1: Does MySQL 6.0 support stored procedures and functions?

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

22.4.2: Where can I find documentation for MySQL stored procedures and stored functions?

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

22.4.3: Is there a discussion forum for MySQL stored procedures?

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

22.4.4: Where can I find the ANSI SQL 2003 specification for stored procedures?

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.

22.4.5: How do you manage stored routines?

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 19.14, “The INFORMATION_SCHEMA ROUTINES Table”).

22.4.6: Is there a way to view all stored procedures and stored functions in a given database?

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 19.14, “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 12.5.6.11, “SHOW CREATE PROCEDURE Syntax”, for more information.

22.4.7: Where are stored procedures stored?

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 12.5.6.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 19.14, “The INFORMATION_SCHEMA ROUTINES Table”, for information about this table.

22.4.8: Is it possible to group stored procedures or stored functions into packages?

No. This is not supported in MySQL 6.0.

22.4.9: Can a stored procedure call another stored procedure?

Yes.

22.4.10: Can a stored procedure call a trigger?

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

22.4.11: Can a stored procedure access tables?

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

22.4.12: Do stored procedures have a statement for raising application errors?

Yes. As of MySQL 6.0.11, MySQL implements the SQL standard SIGNAL and RESIGNAL statements. See Section 12.8.8, “SIGNAL and RESIGNAL.

22.4.13: Do stored procedures provide exception handling?

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

22.4.14: Can MySQL 6.0 stored routines return result sets?

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.

22.4.15: Is WITH RECOMPILE supported for stored procedures?

Not in MySQL 6.0.

22.4.16: Is there a MySQL equivalent to using mod_plsql as a gateway on Apache to talk directly to a stored procedure in the database?

There is no equivalent in MySQL 6.0.

22.4.17: Can I pass an array as input to a stored procedure?

Not in MySQL 6.0.

22.4.18: Can I pass a cursor as an IN parameter to a stored procedure?

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

22.4.19: Can I return a cursor as an OUT parameter from a stored procedure?

In MySQL 6.0, 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 12.2.9, “SELECT Syntax”.

22.4.20: Can I print out a variable's value within a stored routine for debugging purposes?

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.

22.4.21: Can I commit or roll back transactions inside a stored procedure?

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

22.4.22: Do MySQL 6.0 stored procedures and functions work with replication?

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 18.6, “Binary Logging of Stored Programs”.

22.4.23: Are stored procedures and functions created on a master server replicated to a slave?

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.

22.4.24: How are actions that take place inside stored procedures and functions 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.

22.4.25: Are there special security requirements for using stored procedures and functions together with replication?

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:

  1. Any user wishing to create stored functions must be granted the SUPER privilege.

  2. Alternatively, a DBA can set the log_bin_trust_function_creators system variable to 1, which enables anyone with the standard CREATE ROUTINE privilege to create stored functions.

22.4.26: What limitations exist for replicating stored procedure and function actions?

Nondeterministic (random) or time-based actions embedded in stored procedures may not replicate properly. By their very nature, randomly produced results are not predictable and cannot be exactly reproduced, and therefore, random actions replicated to a slave will not mirror those performed on a master. Note that declaring stored functions to be 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.

22.4.27: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?

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

22.4.28: What is being done to correct the aforementioned limitations?

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.

The default format, 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 16.1.2, “Replication Formats”.

A.5. MySQL 6.0 FAQ — Triggers

Questions

  • 22.5.1: Where can I find the documentation for MySQL 6.0 triggers?

  • 22.5.2: Is there a discussion forum for MySQL Triggers?

  • 22.5.3: Does MySQL 6.0 have statement-level or row-level triggers?

  • 22.5.4: Are there any default triggers?

  • 22.5.5: How are triggers managed in MySQL?

  • 22.5.6: Is there a way to view all triggers in a given database?

  • 22.5.7: Where are triggers stored?

  • 22.5.8: Can a trigger call a stored procedure?

  • 22.5.9: Can triggers access tables?

  • 22.5.10: Can triggers call an external application through a UDF?

  • 22.5.11: Is it possible for a trigger to update tables on a remote server?

  • 22.5.12: Do triggers work with replication?

  • 22.5.13: How are actions carried out through triggers on a master replicated to a slave?

Questions and Answers

22.5.1: Where can I find the documentation for MySQL 6.0 triggers?

See Section 18.3, “Using Triggers”.

22.5.2: Is there a discussion forum for MySQL Triggers?

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

22.5.3: Does MySQL 6.0 have statement-level or row-level triggers?

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

22.5.4: Are there any default triggers?

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

22.5.5: How are triggers managed in MySQL?

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

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

22.5.6: Is there a way to view all triggers in a given database?

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 19.16, “The INFORMATION_SCHEMA TRIGGERS Table”.

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

22.5.7: Where are triggers stored?

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

22.5.8: Can a trigger call a stored procedure?

Yes.

22.5.9: Can triggers access tables?

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.

22.5.10: Can triggers call an external application through a UDF?

Yes. For example, a trigger could invoke the sys_exec() UDF available at MySQL Forge here: http://forge.mysql.com/projects/project.php?id=211

22.5.11: Is it possible for a trigger to update tables on a remote server?

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

22.5.12: Do triggers work with replication?

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 16.3.1.26, “Replication and Triggers”.

22.5.13: How are actions carried out through triggers on a master replicated to a slave?

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:

  1. An INSERT statement is made to EMP.

  2. The AFTER trigger on EMP activates.

  3. The INSERT statement is written to the binary log.

  4. The replication slave picks up the INSERT statement to EMP and executes it.

  5. The AFTER trigger on EMP that exists on the slave activates.

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 16.3.1.26, “Replication and Triggers”.

A.6. MySQL 6.0 FAQ — Views

Questions

  • 22.6.1: Where can I find documentation covering MySQL Views?

  • 22.6.2: Is there a discussion forum for MySQL Views?

  • 22.6.3: What happens to a view if an underlying table is dropped or renamed?

  • 22.6.4: Does MySQL 6.0 have table snapshots?

  • 22.6.5: Does MySQL 6.0 have materialized views?

  • 22.6.6: Can you insert into views that are based on joins?

Questions and Answers

22.6.1: Where can I find documentation covering MySQL Views?

See Section 18.5, “Using Views”.

22.6.2: Is there a discussion forum for MySQL Views?

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

22.6.3: What happens to a view if an underlying table is dropped or renamed?

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 12.5.2.2, “CHECK TABLE Syntax”.)

22.6.4: Does MySQL 6.0 have table snapshots?

No.

22.6.5: Does MySQL 6.0 have materialized views?

No.

22.6.6: Can you insert into views that are based on joins?

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.

A.7. MySQL 5.0 FAQ — INFORMATION_SCHEMA

Questions

  • 22.7.1: Where can I find documentation for the MySQL INFORMATION_SCHEMA database?

  • 22.7.2: Is there a discussion forum for INFORMATION_SCHEMA?

  • 22.7.3: Where can I find the ANSI SQL 2003 specification for INFORMATION_SCHEMA?

  • 22.7.4: What is the difference between the Oracle Data Dictionary and MySQL's INFORMATION_SCHEMA?

  • 22.7.5: Can I add to or otherwise modify the tables found in the INFORMATION_SCHEMA database?

Questions and Answers

22.7.1: Where can I find documentation for the MySQL INFORMATION_SCHEMA database?

See Chapter 19, INFORMATION_SCHEMA Tables

22.7.2: Is there a discussion forum for INFORMATION_SCHEMA?

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

22.7.3: Where can I find the ANSI SQL 2003 specification for INFORMATION_SCHEMA?

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.

22.7.4: What is the difference between the Oracle Data Dictionary and MySQL's 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.

22.7.5: Can I add to or otherwise modify the tables found in the INFORMATION_SCHEMA database?

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.

A.8. MySQL 6.0 FAQ — Migration

Questions

  • 22.8.1: Where can I find information on how to migrate from MySQL 5.1 to MySQL 6.0?

  • 22.8.2: How has storage engine (table type) support changed in MySQL 6.0 from previous versions?

Questions and Answers

22.8.1: Where can I find information on how to migrate from MySQL 5.1 to MySQL 6.0?

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.

22.8.2: How has storage engine (table type) support changed in MySQL 6.0 from previous versions?

Storage engine support has changed as follows:

  • Support for ISAM tables was removed in MySQL 5.0 and you should now use the MyISAM storage engine in place of ISAM. To convert a table tblname from ISAM to MyISAM, simply issue a statement such as this one:

    ALTER TABLE tblname ENGINE=MYISAM;
  • Internal RAID for MyISAM tables was also removed in MySQL 5.0. This was formerly used to allow large tables in file systems that did not support file sizes greater than 2GB. All modern file systems allow for larger tables; in addition, there are now other solutions such as MERGE tables and views.

  • The VARCHAR column type now retains trailing spaces in all storage engines.

  • MEMORY tables (formerly known as HEAP tables) can also contain VARCHAR columns.

A.9. MySQL 6.0 FAQ — Security

Questions

  • 22.9.1: Where can I find documentation that addresses security issues for MySQL?

  • 22.9.2: Does MySQL 6.0 have native support for SSL?

  • 22.9.3: Is SSL support be built into MySQL binaries, or must I recompile the binary myself to enable it?

  • 22.9.4: Does MySQL 6.0 have built-in authentication against LDAP directories?

  • 22.9.5: Does MySQL 6.0 include support for Roles Based Access Control (RBAC)?

Questions and Answers

22.9.1: Where can I find documentation that addresses security issues for MySQL?

The best place to start is Section 5.3, “General Security Issues”.

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

MySQL Enterprise The MySQL Enterprise Monitor enforces best practices for maximizing the security of your servers. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

22.9.2: Does MySQL 6.0 have native support for SSL?

Most 6.0 binaries have support for SSL connections between the client and server. We can't currently build with the new YaSSL library everywhere, as it is still quite new and does not compile on all platforms yet. See Section 5.5.7, “Using SSL for Secure Connections”.

You can also tunnel a connection via SSH, if (for instance) if the client application doesn't support SSL connections. For an example, see Section 5.5.8, “Connecting to MySQL Remotely from Windows with SSH”.

22.9.3: Is SSL support be built into MySQL binaries, or must I recompile the binary myself to enable it?

Most 6.0 binaries have SSL enabled for client-server connections that are secured, authenticated, or both. However, the YaSSL library currently does not compile on all platforms. See Section 5.5.7, “Using SSL for Secure Connections”, for a complete listing of supported and unsupported platforms.

22.9.4: Does MySQL 6.0 have built-in authentication against LDAP directories?

No. Support for external authentication methods is on the MySQL roadmap as a “rolling feature”, which means that we plan to implement it in the future, but we have not yet determined when this will be done.

22.9.5: Does MySQL 6.0 include support for Roles Based Access Control (RBAC)?

No. Support for roles is on the MySQL roadmap as a “rolling feature”, which means that we plan to implement it in the future, but we have not yet determined when this will be done.

A.10. MySQL 6.0 FAQ — MySQL Cluster

Beginning with MySQL 6.0.5, MySQL Cluster is no longer supported in MySQL Server 6.0 releases. Instead, MySQL Cluster is now released as a separate product, available in 2 release series — MySQL Cluster NDB 6.2 and MySQL Cluster NDB 6.3. You should use one of these for new deployments, and plan to upgrade to one of them if you are using a previous version of MySQL with clustering support. For an overview of improvements in MySQL Cluster NDB 6.2 and 6.3, see Features Added in MySQL Cluster NDB 6.2, and Features Added in MySQL Cluster NDB 6.3.

For answers to frequently asked questions about MySQL Cluster, see MySQL 5.1 FAQ — MySQL Cluster. For detailed information about deploying and using MySQL Cluster, see MySQL Cluster NDB 6.X/7.X.

A.11. MySQL 6.0 FAQ — MySQL Chinese, Japanese, and Korean Character Sets

This set of Frequently Asked Questions derives from the experience of MySQL's Support and Development groups in handling many inquiries about CJK (Chinese-Japanese-Korean) issues.

Questions

  • 22.11.1: What CJK character sets are available in MySQL?

  • 22.11.2: I have inserted CJK characters into my table. Why does SELECT display them as “?” characters?

  • 22.11.3: What problems should I be aware of when working with the Big5 Chinese character set?

  • 22.11.4: Why do Japanese character set conversions fail?

  • 22.11.5: What should I do if I want to convert SJIS 81CA to cp932?

  • 22.11.6: How does MySQL represent the Yen (¥) sign?

  • 22.11.7: Do MySQL plan to make a separate character set where 5C is the Yen sign, as at least one other major DBMS does?

  • 22.11.8: Of what issues should I be aware when working with Korean character sets in MySQL?

  • 22.11.9: Why do I get Data truncated error messages?

  • 22.11.10: Why does my GUI front end or browser not display CJK characters correctly in my application using Access, PHP, or another API?

  • 22.11.11: I've upgraded to MySQL 6.0. How can I revert to behavior like that in MySQL 4.0 with regard to character sets?

  • 22.11.12: Why do some LIKE and FULLTEXT searches with CJK characters fail?

  • 22.11.13: How do I know whether character X is available in all character sets?

  • 22.11.14: Why don't CJK strings sort correctly in Unicode? (I)

  • 22.11.15: Why don't CJK strings sort correctly in Unicode? (II)

  • 22.11.16: Why are my supplementary characters rejected by MySQL?

  • 22.11.17: Shouldn't it be “CJKV”?

  • 22.11.18: Does MySQL allow CJK characters to be used in database and table names?

  • 22.11.19: Where can I find translations of the MySQL Manual into Chinese, Japanese, and Korean?

  • 22.11.20: Where can I get help with CJK and related issues in MySQL?

Questions and Answers

22.11.1: What CJK character sets are available in MySQL?

The list of CJK character sets may vary depending on your MySQL version. For example, the eucjpms character set was not supported prior to MySQL 5.0.3. 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 '%Chinese%'
    -> 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             |
| gb2312             | GB2312 Simplified Chinese |
| gbk                | GBK Simplified Chinese    |
| sjis               | Shift-JIS Japanese        |
| ujis               | EUC-JP Japanese           |
+--------------------+---------------------------+
8 rows in set (0.01 sec)

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

MySQL supports the two common 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 and gbk. 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.

22.11.2: I have inserted CJK characters into my table. Why does SELECT display them as “?” characters?

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:

  • Be certain of what MySQL version you are using.

    Use the statement SELECT VERSION(); to determine this.

  • Make sure that the database is actually using the desired character set.

    People often think that the client character set is always the same as either the server character set or the character set used for display purposes. However, both of these are false assumptions. You can make sure by checking the result of SHOW CREATE TABLE tablename or — better — yet by using this statement:

    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;
    

  • Determine the hexadecimal value of the character or characters that are not being displayed correctly.

    You can obtain this information for a column column_name in the table table_name using the following query:

    SELECT HEX(column_name)
    FROM table_name;
    

    3F is the encoding for the ? character; this means that ? is the character actually stored in the column. This most often happens because of a problem converting a particular character from your client character set to the target character set.

  • Make sure that a round trip possible — that is, when you select literal (or _introducer hexadecimal-value), you obtain literal as a result.

    For example, the Japanese Katakana character Pe (ペ') exists in all CJK character sets, and has the code point value (hexadecimal coding) 0x30da. To test a round trip for this character, use this query:

    SELECT 'ペ' AS `ペ`;         /* or SELECT _ucs2 0x30da; */
    

    If the result is not also , then the round trip has failed.

    For bug reports regarding such failures, we might ask you to follow up with SELECT HEX('ペ');. Then we can determine whether the client encoding is correct.

  • Make sure that the problem is not with the browser or other application, rather than with MySQL.

    Use the mysql client program (on Windows: mysql.exe) to accomplish this task. If mysql displays correctly but your application doesn't, then your problem is probably due to system settings.

    To find out what your settings are, use the SHOW VARIABLES statement, whose output should resemble what is shown here:

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

    These are typical character-set settings for an international-oriented client (notice the use of utf8 Unicode) connected to a server in the West (latin1 is a West Europe character set and a default for MySQL).

    Although Unicode (usually the utf8 variant on Unix, and the ucs2 variant on Windows) is preferable to Latin, it is often not what your operating system utilities support best. Many Windows users find that a Microsoft character set, such as cp932 for Japanese Windows, is suitable.

    If you cannot control the server settings, and you have no idea what your underlying computer is, then try changing to a common character set for the country that you're in (euckr = Korea; gb2312 or gbk = People's Republic of China; big5 = Taiwan; sjis, ujis, cp932, or eucjpms = Japan; ucs2 or utf8 = anywhere). Usually it is necessary to change only the client and connection and results settings. There is a simple statement which changes all three at once: SET NAMES. For example:

    SET NAMES 'big5';
    

    Once the setting is correct, you can make it permanent by editing my.cnf or my.ini. For example you might add lines looking like these:

    [mysqld]
    character-set-server=big5
    [client]
    default-character-set=big5
    

    It is also possible that there are issues with the API configuration setting being used in your application; see Why does my GUI front end or browser not display CJK characters correctly...? for more information.

22.11.3: What problems should I be aware of when working with the Big5 Chinese character set?

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.

22.11.4: Why do Japanese character set conversions fail?

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.

Character Nameucs2sjiscp932ujiseucjpms
BROKEN BAR00A63F3F8FA2C33F
FULLWIDTH BROKEN BARFFE43FFA553F8FA2
YEN SIGN00A53F3F203F
FULLWIDTH YEN SIGNFFE5818F818FA1EF3F
TILDE007E7E7E7E7E
OVERLINE203E3F3F203F
HORIZONTAL BAR2015815C815CA1BDA1BD
EM DASH20143F3F3F3F
REVERSE SOLIDUS005C815F5C5C5C
FULLWIDTH ""FF3C3F815F3FA1C0
WAVE DASH301C81603FA1C13F
FULLWIDTH TILDEFF5E3F81603FA1C1
DOUBLE VERTICAL LINE201681613FA1C23F
PARALLEL TO22253F81613FA1C2
MINUS SIGN2212817C3FA1DD3F
FULLWIDTH HYPHEN-MINUSFF0D3F817C3FA1DD
CENT SIGN00A281913FA1F13F
FULLWIDTH CENT SIGNFFE03F81913FA1F1
POUND SIGN00A381923FA1F23F
FULLWIDTH POUND SIGNFFE13F81923FA1F2
NOT SIGN00AC81CA3FA2CC3F
FULLWIDTH NOT SIGNFFE23F81CA3FA2CC

Now consider the following portion of the table.

 ucs2sjiscp932
NOT SIGN00AC81CA3F
FULLWIDTH NOT SIGNFFE23F81CA

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.

22.11.5: What should I do if I want to convert SJIS 81CA to cp932?

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.

22.11.6: How does MySQL represent the Yen (¥) sign?

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

22.11.7: Do MySQL plan to make a separate character set where 5C is the Yen sign, as at least one other major DBMS does?

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

22.11.8: Of what issues should I be aware when working with Korean character sets in MySQL?

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.

22.11.9: Why do I get Data truncated error messages?

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;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated 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)

There are several things that need explanation here.

  1. The fact that it is a “warning” rather than an “error” is characteristic of MySQL. We like to try to do what we can, to get the best fit, rather than give up.

  2. The character isn't in the gb2312 character set. We described that problem earlier.

  3. Admittedly the message is misleading. We didn't “truncate” in this case, we replaced with a question mark. We've had a complaint about this message (See Bug#9337). But until we come up with something better, just accept that error/warning code 2165 can mean a variety of things.

  4. With SQL_MODE=TRADITIONAL, there would be an error message, but instead of error 2165 you would see: ERROR 1406 (22001): Data too long for column 'gb2312' at row 1.

22.11.10: Why does my GUI front end or browser not display CJK characters correctly in my application using Access, PHP, or another API?

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 MyODBC. In this case, you should check Section 20.1.4, “Connector/ODBC Configuration”. 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 Section 20.2.4.2, “Connecting to MySQL Using Connector/NET”, for more information.

If you are using PHP, try this:

<?php
  $link = mysql_connect($host, $usr, $pwd);

  mysql_select_db($db);

  if( mysql_error() ) { print "Database ERROR: " . mysql_error(); }
  mysql_query("SET NAMES 'utf8'", $link);
?>

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

We encourage the use of the newer mysqli extension, rather than mysql. Using mysqli, the previous example could be rewritten as shown here:

<?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'");
?>

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 Section 20.4.4.4, “Using Character Sets and Unicode”.

22.11.11: I've upgraded to MySQL 6.0. How can I revert to behavior like that in MySQL 4.0 with regard to character sets?

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 9.1.4, “Connection Character Sets and Collations”:

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 character_set_client, character_set_results, and character_set_connection system variables. In effect, the server performs a SET NAMES operation using the character set name.

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.

22.11.12: Why do some LIKE and FULLTEXT searches with CJK characters fail?

There is a very simple problem with LIKE searches on BINARY and BLOB 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 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.

22.11.13: How do I know whether character X is available in all character sets?

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.

22.11.14: Why don't CJK strings sort correctly in Unicode? (I)

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

22.11.15: Why don't CJK strings sort correctly in Unicode? (II)

If you are using Unicode (ucs2 or utf8), and you know what the Unicode sort order is (see Section A.11, “MySQL 6.0 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 19.3, “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.

22.11.16: Why are my supplementary characters rejected by MySQL?

Before MySQL 6.0.4, 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 multi-byte 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 6.0.4, Unicode support is extended to include supplementary characters by means of additional Unicode character sets: utf16, utf32, and 4-byte utf8. These character sets support supplementary Unicode characters outside the Basic Multilingual Plane (BMP).

22.11.17: Shouldn't it be “CJKV”?

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.

Bug#4745 is a request for a specialized Vietnamese collation, which we might add in the future if there is sufficient demand for it.

22.11.18: Does MySQL allow CJK characters to be used in database and table names?

This issue was 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 .

22.11.19: Where can I find translations of the MySQL Manual into Chinese, Japanese, and Korean?

A Simplified Chinese version of the Manual, current for MySQL 5.1.12, can be found at http://dev.mysql.com/doc/. The Japanese translation of the MySQL 4.1 manual can be downloaded from http://dev.mysql.com/doc/.

22.11.20: Where can I get help with CJK and related issues in MySQL?

The following resources are available:

A.12. MySQL 6.0 FAQ — Connectors & APIs

For common questions, issues, and answers relating to the MySQL Connectors and other APIs, see the following areas of the Manual:

A.13. MySQL 6.0 FAQ — Replication

For answers to common queries and question regarding Replication within MySQL, see Section 16.3.4, “Replication FAQ”.

A.14. MySQL 6.0 FAQ — MySQL, DRBD, and Heartbeat

A.14.1. Distributed Replicated Block Device (DRBD) Basics

In the following section, we provide answers to questions that are most frequently asked about Distributed Replicated Block Device (DRBD).

Questions

Questions and Answers

22.14.1.1: What is DRBD?

DRBD is an acronym for Distributed Replicated Block Device. DRBD is an open source Linux kernel block device which leverages synchronous replication to achieve a consistent view of data between two systems, typically an Active and Passive system. DRBD currently supports all the major flavors of Linux and comes bundled in several major Linux distributions. The DRBD project is maintained by LINBIT.

22.14.1.2: What are “Block Devices”?

A block device is the type of device used to represent storage in the Linux Kernel. All physical disk devices present a block device interface. Additionally, virtual disk systems like LVM or DRBD present a block device interface. In this way, the file system or other software that might want to access a disk device can be used with any number of real or virtual devices without having to know anything about their underlying implementation details.

22.14.1.3: How is DRBD licensed?

DRBD is licensed under the GPL.

22.14.1.4: Where can I download DRBD?

Please see http://www.drbd.org/download/packages/.

22.14.1.5: If I find a bug in DRBD, to whom do I submit the issue?

Bug reports should be submitted to the DRBD mailing list. Please see http://lists.linbit.com/.

22.14.1.6: Where can I get more technical and business information concerning MySQL and DRBD?

Please visit http://mysql.com/drbd/.

A.14.2. Linux Heartbeat

In the following section, we provide answers to questions that are most frequently asked about Linux Heartbeat.

Questions

Questions and Answers

22.14.2.1: What is Linux Heartbeat?

The Linux-HA project (http://www.linux-ha.org/) offers a high availability solution commonly referred to as Linux Heartbeat. Linux Heartbeat ships as part of several Linux distributions, as well as within several embedded high availability systems. This solution can also be used for other applications besides databases servers, such as mail servers, web servers, file servers, and DNS servers.

Linux Heartbeat implements a heartbeat-protocol. A heartbeat-protocol means that messages are sent at regular intervals between two or more nodes. If a message is not received from a node within a given interval, then it is assumed the node has failed and some type of failover or recovery action is required. Linux Heartbeat is typically configured to send these heartbeat messages over standard Ethernet interfaces, but it does also support other methods, such as serial-line links.

22.14.2.2: How is Linux Heartbeat licensed?

Linux Heartbeat is licensed under the GPL.

22.14.2.3: Where can I download Linux Heartbeat?

Please see http://linux-ha.org/download/index.html.

22.14.2.4: If I find a bug with Linux Heartbeat, to whom do I submit the issue?

Bug reports should be submitted to http://www.linux-ha.org/ClusterResourceManager/BugReports.

A.14.3. DRBD Architecture

In the following section, we provide answers to questions that are most frequently asked about DRBD Architecture.

Questions

  • 22.14.3.1: Is an Active/Active option available for MySQL with DRBD?

  • 22.14.3.2: What MySQL storage engines are supported with DRBD?

  • 22.14.3.3: How long does a failover take?

  • 22.14.3.4: How long does it take to resynchronize data after a failure?

  • 22.14.3.5: Are there any situations where you shouldn't use DRBD?

  • 22.14.3.6: Are there any limitations to DRBD?

  • 22.14.3.7: Where can I find more information on sample architectures?

Questions and Answers

22.14.3.1: Is an Active/Active option available for MySQL with DRBD?

Currently, MySQL does not support Active/Active configurations using DRBD “out of the box”.

22.14.3.2: What MySQL storage engines are supported with DRBD?

All of the MySQL transactional storage engines are supported by DRBD, including InnoDB and Falcon. For archived or read-only data, MyISAM or Archive can also be used.

22.14.3.3: How long does a failover take?

Failover time is dependent on many things, some of which are configurable. After activating the passive host, MySQL will have to start and run a normal recovery process. If the InnoDB log files have been configured to a large size and there was heavy write traffic, this may take a reasonably long period of time. However, under normal circumstances, failover tends to take less than a minute.

22.14.3.4: How long does it take to resynchronize data after a failure?

Resynchronization time depends on how long the two machines are out of communication and how much data was written during that period of time. Resynchronization time is a function of data to be synced, network speed and disk speed. DRBD maintains a bitmap of changed blocks on the primary machine, so only those blocks that have changed will need to be transferred.

22.14.3.5: Are there any situations where you shouldn't use DRBD?

See When Not To Use DRBD.

22.14.3.6: Are there any limitations to DRBD?

See DRBD limitations (or are they?).

22.14.3.7: Where can I find more information on sample architectures?

For an example of a Heartbeat R1-compatible resource configuration involving a MySQL database backed by DRBD, see DRBD User's Guide.

For an example of the same DRBD-backed configuration for a MySQL database in a Heartbeat CRM cluster, see DRBD User's Guide.

A.14.4. DRBD and MySQL Replication

In the following section, we provide answers to questions that are most frequently asked about MySQL Replication Scale-out.

Questions

  • 22.14.4.1: What is the difference between MySQL Cluster and DRBD?

  • 22.14.4.2: What is the difference between MySQL Replication and DRBD?

  • 22.14.4.3: How can I combine MySQL Replication scale-out with DRBD?

Questions and Answers

22.14.4.1: What is the difference between MySQL Cluster and DRBD?

Both MySQL Cluster and DRBD replicate data synchronously. MySQL Cluster leverages a shared-nothing storage architecture in which the cluster can be architected beyond an Active/Passive configuration. DRBD operates at a much lower level within the “stack”, at the disk I/O level. For a comparison of various high availability features between these two options, please refer to Chapter 14, High Availability and Scalability.

Note

MySQL Cluster is currently not supported in MySQL 6.0. If you are interested in using MySQL Cluster, see MySQL Cluster NDB 6.X/7.X, which provides information about MySQL Cluster NDB 6.2 and 6.3 (based on MySQL 5.1 but containing the latest improvements and fixes for the NDBCLUSTER storage engine).

22.14.4.2: What is the difference between MySQL Replication and DRBD?

MySQL Replication replicates data asynchronously while DRBD replicates data synchronously. Also, MySQL Replication replicates MySQL statements, while DRBD replicates the underlying block device that stores the MySQL data files. For a comparison of various high availability features between these two options, please refer to the high availability comparison grid, Chapter 14, High Availability and Scalability.

22.14.4.3: How can I combine MySQL Replication scale-out with DRBD?

MySQL Replication is typically deployed in a Master to many Slaves configuration. In this configuration, having many Slaves provides read scalability. DRBD is used to provide high-availability for the Master MySQL Server in an Active/Passive configuration. This provides for automatic failover, safeguards against data loss, and automatically synchronizes the failed MySQL Master after a failover.

The most likely scenario in which MySQL Replication scale-out can be leveraged with DRBD is in the form of attaching replicated MySQL “read-slaves” off of the Active-Master MySQL Server, shown in Figure A.1, “Active-Master MySQL Server”. Since DRBD replicates an entire block device, master information such as the binary logs are also replicated. In this way, all of the slaves can attach to the Virtual IP Address managed by Linux Heartbeat. In the event of a failure, the asynchronous nature of MySQL Replication allows the slaves to continue with the new Active machine as their master with no intervention needed.

Figure A.1. Active-Master MySQL Server

Active-Master MySQL server

A.14.5. DRBD and File Systems

In the following section, we provide answers to questions that are most frequently asked about DRBD and file systems.

Questions

Questions and Answers

22.14.5.1: Can XFS be used with DRBD?

Yes. XFS uses dynamic block size, thus DRBD 0.7 or later is needed.

A.14.6. DRBD and LVM

In the following section, we provide answers to questions that are most frequently asked about DRBD and LVM.

Questions

  • 22.14.6.1: Can I use DRBD on top of LVM?

  • 22.14.6.2: Can I use LVM on top of DRBD?

  • 22.14.6.3: Can I use DRBD on top of LVM while at the same time running LVM on top of that DRBD?

Questions and Answers

22.14.6.1: Can I use DRBD on top of LVM?

Yes, DRBD supports on-line resizing. If you enlarge your logical volume that acts as a backing device for DRBD, you can enlarge DRBD itself too, and of course your file system if it supports resizing.

22.14.6.2: Can I use LVM on top of DRBD?

Yes, you can use DRBD as a Physical Volume (PV) for LVM. Depending on the default LVM configuration shipped with your distribution, you may need to add the /dev/drbd* device files to the filter option in your lvm.conf so LVM scans your DRBDs for PV signatures.

22.14.6.3: Can I use DRBD on top of LVM while at the same time running LVM on top of that DRBD?

This requires careful tuning of your LVM configuration to avoid duplicate PV scans, but yes, it is possible.

A.14.7. DRBD and Virtualization

In the following section, we provide answers to questions that are most frequently asked about DRBD and virtualization.

Questions

Questions and Answers

22.14.7.1: Can I use DRBD with OpenVZ?

See http://wiki.openvz.org/HA_cluster_with_DRBD_and_Heartbeat.

22.14.7.2: Can I use DRBD with Xen and/or KVM?

Yes. If you are looking for professional consultancy or expert commercial support for Xen- or KVM-based virtualization clusters with DRBD, contact LINBIT (http://www.linbit.com).

A.14.8. DRBD and Security

In the following section, we provide answers to questions that are most frequently asked about DRBD and security.

Questions

Questions and Answers

22.14.8.1: Can I encrypt/compress the exchanged data?

Yes. But there is no option within DRBD to allow for this. You’ll need to leverage a VPN and the network layer should do the rest.

22.14.8.2: Does DRBD do mutual node authentication?

Yes, starting with DRBD 8 shared-secret mutual node authentication is supported.

A.14.9. DRBD and System Requirements

In the following section, we provide answers to questions that are most frequently asked about DRBD and System Requirements.

Questions

Questions and Answers

22.14.9.1: What other packages besides DRBD are required?

When using pre-built binary packages, none except a matching kernel, plus packages for glibc and your favorite shell. When compiling DRBD from source additional prerequisite packages may be required. They include but are not limited to:

  • glib-devel

  • openssl

  • devel

  • libgcrypt-devel

  • glib2-devel

  • pkgconfig

  • ncurses-devel

  • rpm-build

  • rpm-devel

  • redhat-rpm-config

  • gcc

  • gcc-c++

  • bison

  • flex

  • gnutls-devel

  • lm_sensors-devel

  • net-snmp-devel

  • python-devel

  • bzip2-devel

  • libselinux-devel

  • perl-DBI

  • libnet

Pre-built x86 and x86_64 packages for specific kernel versions are available with a support subscription from LINBIT. Please note that if the kernel is upgraded, DRBD must be as well.

22.14.9.2: How many machines are required to set up DRBD?

Two machines are required to achieve the minimum degree of high availability. Although at any one given point in time one will be primary and one will be secondary, it is better to consider the machines as part of a mirrored pair without a “natural” primary machine.

22.14.9.3: Does DRBD only run on Linux?

DRBD is a Linux Kernel Module, and can work with many popular Linux distributions. DRBD is currently not available for non-Linux operating systems.

A.14.10. DBRD and Support and Consulting

In the following section, we provide answers to questions that are most frequently asked about DRBD and resources.

Questions

  • 22.14.10.1: Does MySQL offer professional consulting to help with designing a DRBD system?

  • 22.14.10.2: Does MySQL offer support for DRBD and Linux Heartbeat from MySQL?

  • 22.14.10.3: Are pre-built binaries or RPMs available?

  • 22.14.10.4: Does MySQL have documentation to help me with the installation and configuration of DRBD and Linux Heartbeat?

  • 22.14.10.5: Is there a dedicated discussion forum for MySQL High-Availability?

  • 22.14.10.6: Where can I get more information about MySQL for DRBD?

Questions and Answers

22.14.10.1: Does MySQL offer professional consulting to help with designing a DRBD system?

Yes. MySQL offers consulting for the design, installation, configuration, and monitoring of high availability DRBD. For more information concerning a High Availability Jumpstart, please see: http://www.mysql.com/consulting/packaged/scaleout.html.

22.14.10.2: Does MySQL offer support for DRBD and Linux Heartbeat from MySQL?

Yes. Support for DRBD and Linux Heartbeat is available with an add-on subscription to MySQL Enterprise called “DRBD for MySQL”. For more information about support options for DRBD see: http://mysql.com/products/enterprise/features.html.

For the list of supported Linux distributions, please see: http://www.mysql.com/support/supportedplatforms/enterprise.html.

Note

DRBD is only available on Linux. DRBD is not available on Windows, MacOS, Solaris, HPUX, AIX, FreeBSD, or other non-Linux platforms.

22.14.10.3: Are pre-built binaries or RPMs available?

Yes. “DRBD for MySQL” is an add-on subscription to MySQL Enterprise, which provides pre-built binaries for DRBD. For more information, see: http://mysql.com/products/enterprise/features.html.

22.14.10.4: Does MySQL have documentation to help me with the installation and configuration of DRBD and Linux Heartbeat?

For MySQL-specific DRBD documentation, see Section 14.1, “Using MySQL with DRBD”.

For general DRBD documentation, see DRBD User's Guide.

22.14.10.5: Is there a dedicated discussion forum for MySQL High-Availability?

Yes, http://forums.mysql.com/list.php?144.

22.14.10.6: Where can I get more information about MySQL for DRBD?

For more information about MySQL for DRBD, including a technical white paper please see: DRBD for MySQL High Availability.