Appendix A MySQL 8.0 Frequently Asked Questions

Table of Contents

A.1 MySQL 8.0 FAQ: General
A.2 MySQL 8.0 FAQ: Storage Engines
A.3 MySQL 8.0 FAQ: Server SQL Mode
A.4 MySQL 8.0 FAQ: Stored Procedures and Functions
A.5 MySQL 8.0 FAQ: Triggers
A.6 MySQL 8.0 FAQ: Views
A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA
A.8 MySQL 8.0 FAQ: Migration
A.9 MySQL 8.0 FAQ: Security
A.10 MySQL 8.0 FAQ: NDB Cluster
A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean Character Sets
A.12 MySQL 8.0 FAQ: Connectors & APIs
A.13 MySQL 8.0 FAQ: Replication
A.14 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool
A.15 MySQL 8.0 FAQ: InnoDB Change Buffer
A.16 MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption
A.17 MySQL 8.0 FAQ: Virtualization Support

A.1 MySQL 8.0 FAQ: General

A.1.1. Which version of MySQL is production-ready (GA)?
A.1.2. Why did MySQL version numbering skip versions 6 and 7 and go straight to 8.0?
A.1.3. Can MySQL 8.0 do subqueries?
A.1.4. Can MySQL 8.0 perform multiple-table inserts, updates, and deletes?
A.1.5. Does MySQL 8.0 have Sequences?
A.1.6. Does MySQL 8.0 have a NOW() function with fractions of seconds?
A.1.7. Does MySQL 8.0 work with multi-core processors?
A.1.8. Why do I see multiple processes for mysqld?
A.1.9. Can MySQL 8.0 perform ACID transactions?

A.1.1.

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

MySQL 8.0, 5.7, and MySQL 5.6 are supported for production use.

MySQL 8.0 achieved General Availability (GA) status with MySQL 8.0.11, which was released for production use on 19 April 2018.

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.

A.1.2.

Why did MySQL version numbering skip versions 6 and 7 and go straight to 8.0?

Due to the many new and important features we were introducing in this MySQL version, we decided to start a fresh new series. As the series numbers 6 and 7 had actually been used before by MySQL, we went to 8.0.

A.1.3.

Can MySQL 8.0 do subqueries?

Yes. See Section 13.2.11, “Subquery Syntax”.

A.1.4.

Can MySQL 8.0 perform multiple-table inserts, updates, and deletes?

Yes. For the syntax required to perform multiple-table updates, see Section 13.2.12, “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 24.3, “Using Triggers”.

A.1.5.

Does MySQL 8.0 have Sequences?

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

A.1.6.

Does MySQL 8.0 have a NOW() function with fractions of seconds?

Yes, see Section 11.3.5, “Fractional Seconds in Time Values”.

A.1.7.

Does MySQL 8.0 work with multi-core processors?

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

A.1.8.

Why do I see multiple processes for mysqld?

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.

A.1.9.

Can MySQL 8.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 8.0 FAQ: Storage Engines

A.2.1. Where can I obtain complete documentation for MySQL storage engines?
A.2.2. Are there any new storage engines in MySQL 8.0?
A.2.3. Have any storage engines been removed in MySQL 8.0?
A.2.4. Can I prevent the use of a particular storage engine?
A.2.5. Is there an advantage to using the InnoDB storage engine exclusively, as opposed to a combination of InnoDB and non-InnoDB storage engines?
A.2.6. What are the unique benefits of the ARCHIVE storage engine?

A.2.1.

Where can I obtain complete documentation for MySQL storage engines?

See Chapter 16, Alternative Storage Engines. That chapter contains information about all MySQL storage engines except for the InnoDB storage engine and the NDB storage engine (used for MySQL Cluster). InnoDB is covered in Chapter 15, The InnoDB Storage Engine. NDB is covered in Chapter 22, MySQL NDB Cluster 8.0.

A.2.2.

Are there any new storage engines in MySQL 8.0?

No. InnoDB is the default storage engine for new tables. See Section 15.1, “Introduction to InnoDB” for details.

A.2.3.

Have any storage engines been removed in MySQL 8.0?

The PARTITION storage engine plugin which provided partitioning support is replaced by a native partitioning handler. As part of this change, the server can no longer be built using -DWITH_PARTITION_STORAGE_ENGINE. partition is also no longer displayed in the output of SHOW PLUGINS, or shown in the INFORMATION_SCHEMA.PLUGINS table.

In order to support partitioning of a given table, the storage engine used for the table must now provide its own (native) partitioning handler. InnoDB is the only storage engine supported in MySQL 8.0 that includes a native partitioning handler. An attempt to create partitioned tables in MySQL 8.0 using any other storage engine fails. (The NDB storage engine used by MySQL Cluster also provides its own partitioning handler, but is currently not supported by MySQL 8.0.)

A.2.4.

Can I prevent the use of a particular storage engine?

Yes. The disabled_storage_engines configuration option defines which storage engines cannot be used to create tables or tablespaces. By default, disabled_storage_engines is empty (no engines disabled), but it can be set to a comma-separated list of one or more engines.

A.2.5.

Is there an advantage to using the InnoDB storage engine exclusively, as opposed to a combination of InnoDB and non-InnoDB storage engines?

Yes. Using InnoDB tables exclusively can simplify backup and recovery operations. MySQL Enterprise Backup does a hot backup of all tables that use the InnoDB storage engine. For tables using MyISAM or other non-InnoDB storage engines, it does a warm backup, where the database continues to run, but those tables cannot be modified while being backed up. See Section 30.2, “MySQL Enterprise Backup Overview”.

A.2.6.

What are the unique benefits of the ARCHIVE storage engine?

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

A.3 MySQL 8.0 FAQ: Server SQL Mode

A.3.1. What are server SQL modes?
A.3.2. How many server SQL modes are there?
A.3.3. How do you determine the server SQL mode?
A.3.4. Is the mode dependent on the database or connection?
A.3.5. Can the rules for strict mode be extended?
A.3.6. Does strict mode impact performance?
A.3.7. What is the default server SQL mode when MySQL 8.0 is installed?

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

A.3.2.

How many server SQL modes are there?

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

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

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

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

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

A.3.7.

What is the default server SQL mode when MySQL 8.0 is installed?

The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

For information about all available modes and default MySQL behavior, see Section 5.1.11, “Server SQL Modes”.

A.4 MySQL 8.0 FAQ: Stored Procedures and Functions

A.4.1. Does MySQL 8.0 support stored procedures and functions?
A.4.2. Where can I find documentation for MySQL stored procedures and stored functions?
A.4.3. Is there a discussion forum for MySQL stored procedures?
A.4.4. Where can I find the ANSI SQL 2003 specification for stored procedures?
A.4.5. How do you manage stored routines?
A.4.6. Is there a way to view all stored procedures and stored functions in a given database?
A.4.7. Where are stored procedures stored?
A.4.8. Is it possible to group stored procedures or stored functions into packages?
A.4.9. Can a stored procedure call another stored procedure?
A.4.10. Can a stored procedure call a trigger?
A.4.11. Can a stored procedure access tables?
A.4.12. Do stored procedures have a statement for raising application errors?
A.4.13. Do stored procedures provide exception handling?
A.4.14. Can MySQL 8.0 stored routines return result sets?
A.4.15. Is WITH RECOMPILE supported for stored procedures?
A.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?
A.4.17. Can I pass an array as input to a stored procedure?
A.4.18. Can I pass a cursor as an IN parameter to a stored procedure?
A.4.19. Can I return a cursor as an OUT parameter from a stored procedure?
A.4.20. Can I print out a variable's value within a stored routine for debugging purposes?
A.4.21. Can I commit or roll back transactions inside a stored procedure?
A.4.22. Do MySQL 8.0 stored procedures and functions work with replication?
A.4.23. Are stored procedures and functions created on a master server replicated to a slave?
A.4.24. How are actions that take place inside stored procedures and functions replicated?
A.4.25. Are there special security requirements for using stored procedures and functions together with replication?
A.4.26. What limitations exist for replicating stored procedure and function actions?
A.4.27. Do the preceding limitations affect the ability of MySQL to do point-in-time recovery?
A.4.28. What is being done to correct the aforementioned limitations?

A.4.1.

Does MySQL 8.0 support stored procedures and functions?

Yes. MySQL 8.0 supports two types of stored routines, stored procedures and stored functions.

A.4.2.

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

See Section 24.2, “Using Stored Routines”.

A.4.3.

Is there a discussion forum for MySQL stored procedures?

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

A.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, that provide a comprehensive overview of the standard, including coverage of stored procedures.

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

A.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 25.23, “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.7.9, “SHOW CREATE PROCEDURE Syntax”, for more information.

A.4.7.

Where are stored procedures stored?

Stored procedures are stored in the mysql.routines and mysql.parameters tables, which are part of the data dictionary. You cannot access these tables directly. Instead, query the INFORMATION_SCHEMA ROUTINES and PARAMETERS tables. See Section 25.23, “The INFORMATION_SCHEMA ROUTINES Table”, and Section 25.16, “The INFORMATION_SCHEMA PARAMETERS Table”.

You can also use SHOW CREATE FUNCTION to obtain information about stored functions, and SHOW CREATE PROCEDURE to obtain information about stored procedures. See Section 13.7.7.9, “SHOW CREATE PROCEDURE Syntax”.

A.4.8.

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

No. This is not supported in MySQL 8.0.

A.4.9.

Can a stored procedure call another stored procedure?

Yes.

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

A.4.11.

Can a stored procedure access tables?

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

A.4.12.

Do stored procedures have a statement for raising application errors?

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

A.4.13.

Do stored procedures provide exception handling?

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

A.4.14.

Can MySQL 8.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 higher) client/server protocol for this to work. This means that, for example, in PHP, you need to use the mysqli extension rather than the old mysql extension.

A.4.15.

Is WITH RECOMPILE supported for stored procedures?

Not in MySQL 8.0.

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

A.4.17.

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

Not in MySQL 8.0.

A.4.18.

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

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

A.4.19.

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

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

A.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 example, in PHP, you need to use the mysqli extension rather than the old mysql extension.

A.4.21.

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

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

A.4.22.

Do MySQL 8.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 24.7, “Stored Program Binary Logging”.

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

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

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

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

A.4.27.

Do the preceding limitations affect the ability of MySQL to do point-in-time recovery?

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

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

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

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

A.5 MySQL 8.0 FAQ: Triggers

A.5.1. Where can I find the documentation for MySQL 8.0 triggers?
A.5.2. Is there a discussion forum for MySQL Triggers?
A.5.3. Does MySQL 8.0 have statement-level or row-level triggers?
A.5.4. Are there any default triggers?
A.5.5. How are triggers managed in MySQL?
A.5.6. Is there a way to view all triggers in a given database?
A.5.7. Where are triggers stored?
A.5.8. Can a trigger call a stored procedure?
A.5.9. Can triggers access tables?
A.5.10. Can a table have multiple triggers with the same trigger event and action time?
A.5.11. Can triggers call an external application through a UDF?
A.5.12. Is it possible for a trigger to update tables on a remote server?
A.5.13. Do triggers work with replication?
A.5.14. How are actions carried out through triggers on a master replicated to a slave?

A.5.1.

Where can I find the documentation for MySQL 8.0 triggers?

See Section 24.3, “Using Triggers”.

A.5.2.

Is there a discussion forum for MySQL Triggers?

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

A.5.3.

Does MySQL 8.0 have statement-level or row-level triggers?

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

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

A.5.5.

How are triggers managed in MySQL?

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

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

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

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

A.5.7.

Where are triggers stored?

Triggers are stored in the mysql.triggers system table, which is part of the data dictionary.

A.5.8.

Can a trigger call a stored procedure?

Yes.

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

A.5.10.

Can a table have multiple triggers with the same trigger event and action time?

In MySQL 8.0, it is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two BEFORE UPDATE triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a clause after FOR EACH ROW that indicates FOLLOWS or PRECEDES and the name of an existing trigger that also has the same trigger event and action time. With FOLLOWS, the new trigger activates after the existing trigger. With PRECEDES, the new trigger activates before the existing trigger.

A.5.11.

Can triggers call an external application through a UDF?

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

A.5.12.

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 16.8, “The FEDERATED Storage Engine”).

A.5.13.

Do triggers work with replication?

Yes. However, the way in which they work depends whether you are using MySQL's classic statement-based or row-based replication format.

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

A.5.14.

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

A.6 MySQL 8.0 FAQ: Views

A.6.1. Where can I find documentation covering MySQL Views?
A.6.2. Is there a discussion forum for MySQL Views?
A.6.3. What happens to a view if an underlying table is dropped or renamed?
A.6.4. Does MySQL 8.0 have table snapshots?
A.6.5. Does MySQL 8.0 have materialized views?
A.6.6. Can you insert into views that are based on joins?

A.6.1.

Where can I find documentation covering MySQL Views?

See Section 24.5, “Using Views”.

A.6.2.

Is there a discussion forum for MySQL Views?

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

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

A.6.4.

Does MySQL 8.0 have table snapshots?

No.

A.6.5.

Does MySQL 8.0 have materialized views?

No.

A.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 8.0 FAQ: INFORMATION_SCHEMA

A.7.1. Where can I find documentation for the MySQL INFORMATION_SCHEMA database?
A.7.2. Is there a discussion forum for INFORMATION_SCHEMA?
A.7.3. Where can I find the ANSI SQL 2003 specification for INFORMATION_SCHEMA?
A.7.4. What is the difference between the Oracle Data Dictionary and MySQL INFORMATION_SCHEMA?
A.7.5. Can I add to or otherwise modify the tables found in the INFORMATION_SCHEMA database?

A.7.1.

Where can I find documentation for the MySQL INFORMATION_SCHEMA database?

See Chapter 25, INFORMATION_SCHEMA Tables

A.7.2.

Is there a discussion forum for INFORMATION_SCHEMA?

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

A.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, that provide a comprehensive overview of the standard, including INFORMATION_SCHEMA.

A.7.4.

What is the difference between the Oracle Data Dictionary and MySQL INFORMATION_SCHEMA?

Both Oracle and MySQL provide metadata in tables. However, Oracle and MySQL use different table names and column names. The MySQL implementation is more similar to those found in DB2 and SQL Server, which also support INFORMATION_SCHEMA as defined in the SQL standard.

A.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 8.0 FAQ: Migration

A.8.1. Where can I find information on how to migrate from MySQL 5.7 to MySQL 8.0?
A.8.2. How has storage engine (table type) support changed in MySQL 8.0 from previous versions?

A.8.1.

Where can I find information on how to migrate from MySQL 5.7 to MySQL 8.0?

For detailed upgrade information, see Section 2.11, “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.

A.8.2.

How has storage engine (table type) support changed in MySQL 8.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 8.0 FAQ: Security

A.9.1. Where can I find documentation that addresses security issues for MySQL?
A.9.2. What is the default authentication plugin in MySQL 8.0?
A.9.3. Does MySQL 8.0 have native support for SSL?
A.9.4. Is SSL support built into MySQL binaries, or must I recompile the binary myself to enable it?
A.9.5. Does MySQL 8.0 have built-in authentication against LDAP directories?
A.9.6. Does MySQL 8.0 include support for Roles Based Access Control (RBAC)?

A.9.1.

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

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:

A.9.2.

What is the default authentication plugin in MySQL 8.0?

The default authentication plugin in MySQL 8.0 is caching_sha2_password. For information about this plugin, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.

The caching_sha2_password plugin provides more secure password encryption than the mysql_native_password plugin (the default plugin in previous MySQL series). For information about the implications of this change of default plugin for server operation and compatibility of the server with clients and connectors, see caching_sha2_password as the Preferred Authentication Plugin.

For general information about pluggable authentication and other available authentication plugins, see Section 6.2.17, “Pluggable Authentication”, and Section 6.4.1, “Authentication Plugins”.

A.9.3.

Does MySQL 8.0 have native support for SSL?

Most 8.0 binaries have support for SSL connections between the client and server. See Section 6.3, “Using Encrypted 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.4, “Connecting to MySQL Remotely from Windows with SSH”.

A.9.4.

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

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

A.9.5.

Does MySQL 8.0 have built-in authentication against LDAP directories?

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

A.9.6.

Does MySQL 8.0 include support for Roles Based Access Control (RBAC)?

Not at this time.

A.10 MySQL 8.0 FAQ: NDB Cluster

In the following section, we answer questions that are frequently asked about MySQL NDB Cluster and the NDB storage engine.

A.10.1. Which versions of the MySQL software support NDB Cluster? Do I have to compile from source?
A.10.2. What do “NDB” and “NDBCLUSTER” mean?
A.10.3. How many computers do I need to run an NDB Cluster, and why?

A.10.1.

Which versions of the MySQL software support NDB Cluster? Do I have to compile from source?

NDB Cluster is not supported in MySQL Server 8.0 releases; it is released as a separate product. NDB Cluster 8.0 is now available as a Developer Preview release for evaluation and testing of new features in the NDBCLUSTER storage engine; for more information, see Chapter 22, MySQL NDB Cluster 8.0. You are strongly advised to use NDB Cluster 7.6 for any new deployments; if you are using an older version of NDB Cluster, we recommend that you upgrade to this version soon as possible. For an overview of improvements made in NDB Cluster 7.6, see What is New in NDB Cluster 7.6.

NDB Cluster 7.6, based on MySQL Server 5.7 and version 7.6 of the NDB storage engine, is is the most recent General Availability (GA) version of NDB Cluster, based on version 7.6 of the NDB storage engine and MySQL Server 5.7. NDB Cluster 7.6 is available for production use; new deployments intended for production should use the latest GA release in this series, which is currently NDB Cluster 7.6.12. You can obtain the most recent NDB Cluster 7.6 release from https://dev.mysql.com/downloads/cluster/. For information about new features and other important changes in this series, see What is New in NDB Cluster 7.6.

For detailed information about deploying and using NDB Cluster, see Chapter 22, MySQL NDB Cluster 8.0.

A.10.2.

What do NDB and NDBCLUSTER mean?

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.

A.10.3.

How many computers do I need to run an NDB Cluster, and why?

A minimum of three computers is required to run a viable cluster. However, the minimum recommended number of computers in an NDB 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.11 MySQL 8.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.

A.11.1. What CJK character sets are available in MySQL?
A.11.2. I have inserted CJK characters into my table. Why does SELECT display them as “?” characters?
A.11.3. What problems should I be aware of when working with the Big5 Chinese character set?
A.11.4. Why do Japanese character set conversions fail?
A.11.5. What should I do if I want to convert SJIS 81CA to cp932?
A.11.6. How does MySQL represent the Yen (¥) sign?
A.11.7. Of what issues should I be aware when working with Korean character sets in MySQL?
A.11.8. Why do I get Incorrect string value error messages?
A.11.9. Why does my GUI front end or browser display CJK characters incorrectly in my application using Access, PHP, or another API?
A.11.10. I've upgraded to MySQL 8.0. How can I revert to behavior like that in MySQL 4.0 with regard to character sets?
A.11.11. Why do some LIKE and FULLTEXT searches with CJK characters fail?
A.11.12. How do I know whether character X is available in all character sets?
A.11.13. Why do CJK strings sort incorrectly in Unicode? (I)
A.11.14. Why do CJK strings sort incorrectly in Unicode? (II)
A.11.15. Why are my supplementary characters rejected by MySQL?
A.11.16. Should “CJK” be “CJKV”?
A.11.17. Does MySQL permit CJK characters to be used in database and table names?
A.11.18. Where can I find translations of the MySQL Manual into Chinese, Japanese, and Korean?
A.11.19. Where can I get help with CJK and related issues in MySQL?

A.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 gb18030 character set is 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                 |
+--------------------+---------------------------------+

(For more information, see Section 25.2, “The INFORMATION_SCHEMA CHARACTER_SETS Table”.)

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 (as of MySQL 5.7.4) gb18030.

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 does not work. (For an example, see Bug #16072).

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:

It is also possible to store CJK characters in Unicode character sets, although the available collations may not sort characters quite as you expect:

  • The utf8 and ucs2 character sets support the characters from Unicode Basic Multilingual Plane (BMP). These characters have code point values between U+0000 and U+FFFF.

  • The utf8mb4, utf16, utf16le, and utf32 character sets support BMP characters, as well as supplementary characters that lie outside the BMP. Supplementary characters have code point values between U+10000 and U+10FFFF.

The collation used for a Unicode character set determines the ability to sort (that is, distinguish) characters in the set:

  • Collations based on Unicode Collation Algorithm (UCA) 4.0.0 distinguish only BMP characters.

  • Collations based on UCA 5.2.0 or 9.0.0 distinguish BMP and supplementary characters.

  • Non-UCA collations may not distinguish all Unicode characters. For example, the utf8mb4 default collation is utf8mb4_general_ci, which distinguishes only BMP characters.

Moreover, distinguishing characters is not the same as ordering them per the conventions of a given CJK language. Currently, MySQL has only one CJK-specific UCA collation, gb18030_unicode_520_ci (which requires use of the non-Unicode gb18030 character set).

For information about Unicode collations and their differentiating properties, including collation properties for supplementary characters, see Section 10.10.1, “Unicode Character Sets”.

A.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 does not 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 is possible. When you select literal (or _introducer hexadecimal-value), do 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 , the round trip 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 to accomplish this task. If mysql displays characters correctly but your application does not, your problem is probably due to system settings.

    To determine your settings, 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/ |
    +--------------------------+----------------------------------------+
    

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

    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 setting your underlying computer uses, try changing to a common character set for the country that you're in (euckr = Korea; gb18030, 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. The SET NAMES. statement changes all three at once. 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.

A.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). The MySQL big5 character set is in reality Microsoft code page 950, which is very similar to the original big5 character set.

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.

A.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 Name ucs2 sjis cp932 ujis eucjpms
BROKEN BAR 00A6 3F 3F 8FA2C3 3F
FULLWIDTH BROKEN BAR FFE4 3F FA55 3F 8FA2
YEN SIGN 00A5 3F 3F 20 3F
FULLWIDTH YEN SIGN FFE5 818F 818F A1EF 3F
TILDE 007E 7E 7E 7E 7E
OVERLINE 203E 3F 3F 20 3F
HORIZONTAL BAR 2015 815C 815C A1BD A1BD
EM DASH 2014 3F 3F 3F 3F
REVERSE SOLIDUS 005C 815F 5C 5C 5C
FULLWIDTH "" FF3C 3F 815F 3F A1C0
WAVE DASH 301C 8160 3F A1C1 3F
FULLWIDTH TILDE FF5E 3F 8160 3F A1C1
DOUBLE VERTICAL LINE 2016 8161 3F A1C2 3F
PARALLEL TO 2225 3F 8161 3F A1C2
MINUS SIGN 2212 817C 3F A1DD 3F
FULLWIDTH HYPHEN-MINUS FF0D 3F 817C 3F A1DD
CENT SIGN 00A2 8191 3F A1F1 3F
FULLWIDTH CENT SIGN FFE0 3F 8191 3F A1F1
POUND SIGN 00A3 8192 3F A1F2 3F
FULLWIDTH POUND SIGN FFE1 3F 8192 3F A1F2
NOT SIGN 00AC 81CA 3F A2CC 3F
FULLWIDTH NOT SIGN FFE2 3F 81CA 3F A2CC

Now consider the following portion of the table.

ucs2 sjis cp932
NOT SIGN 00AC 81CA 3F
FULLWIDTH NOT SIGN FFE2 3F 81CA

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

A.11.5.

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

Our answer is: ?. There are disadvantages to this, and many people would prefer a loose conversion, so that 81CA (NOT SIGN) in sjis becomes 81CA (FULLWIDTH NOT SIGN) in cp932.

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

A.11.7.

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

A.11.8.

Why do I get Incorrect string value error messages?

To see the problem, 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);

In nonstrict SQL mode, try to place the rare character in both columns.

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

The INSERT produces 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

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

Several things need explanation here:

  1. The character is not in the gb2312 character set, as described earlier.

  2. If you are using an old version of MySQL, you may see a different message.

  3. A warning occurs rather than an error because MySQL is not set to use strict SQL mode. In nonstrict mode, MySQL tries to do what it can, to get the best fit, rather than give up. With strict SQL mode, the Incorrect string value message occurs as an error rather than a warning, and the INSERT fails.

A.11.9.

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

Obtain a direct connection to the server using the mysql client, and try the same query there. If mysql responds correctly, 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, you are most likely connecting with Connector/ODBC. In this case, you should check Configuring Connector/ODBC. If, for example, you use big5, you would enter SET NAMES 'big5'. (In this case, no ; character is required.) 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, 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, 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, include <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> in the <head> section of the HTML page.

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

A.11.10.

I've upgraded to MySQL 8.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 10.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 Asian customers 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. Suppose further that the client uses utf8 because this is what the client's operating system supports. 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 resulting 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/ |
+--------------------------+----------------------------------------+

Now stop the client, and 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 resulting 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/ |
+--------------------------+----------------------------------------+

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 option is used.

A.11.11.

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

For LIKE searches, there is a very simple problem with binary string column types such as BINARY and BLOB: we must know where characters end. 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 |
+-------------------------+---------------------------+

If we do not know where the first character in a string ends, we do not know where the second character begins, in which case even very simple searches such as LIKE '_A%' fail. The solution is to use a nonbinary string column type defined to have the proper CJK character set. For example: mycol TEXT CHARACTER SET sjis. Alternatively, convert to a CJK character set before comparing.

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

For FULLTEXT searches, we must 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.

A.11.12.

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. The following stored procedure accepts a UCS-2 Unicode character, converts it to 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 are conversion problems, UPDATE produces warnings. */

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

DELIMITER ;

The input can be any single ucs2 character, or it can be the code 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 value is X'30DA'. If we use this value as the argument to p_convert(), the result is as shown here:

mysql> CALL p_convert(X'30DA');
+------+--------+------+-------+---------+-------+--------+------+------+------+
| ucs2 | utf8   | big5 | cp932 | eucjpms | euckr | gb2312 | gbk  | sjis | ujis |
+------+--------+------+-------+---------+-------+--------+------+------+------+
| 30DA | E3839A | C772 | 8379  | A5DA    | ABDA  | A5DA   | A5DA | 8379 | A5DA |
+------+--------+------+-------+---------+-------+--------+------+------+------+

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

A.11.13.

Why do CJK strings sort incorrectly in Unicode? (I)

CJK sorting problems that occurred in older MySQL versions can be solved as of MySQL 8.0 by using the utf8mb4 character set and the utf8mb4_ja_0900_as_cs collation.

A.11.14.

Why do CJK strings sort incorrectly in Unicode? (II)

CJK sorting problems that occurred in older MySQL versions can be solved as of MySQL 8.0 by using the utf8mb4 character set and the utf8mb4_ja_0900_as_cs collation.

A.11.15.

Why are my supplementary characters rejected by MySQL?

Supplementary characters lie outside the Unicode Basic Multilingual Plane / Plane 0. BMP characters have code point values between U+0000 and U+FFFF. Supplementary characters have code point values between U+10000 and U+10FFFF.

To store supplementary characters, you must use a character set that permits them:

  • The utf8 and ucs2 character sets support BMP characters only.

    The utf8 character set permits only UTF-8 characters that take up to three bytes. This has led to reports such as that found in Bug #12600, which we rejected as not a bug. With utf8, MySQL must truncate an input string when it encounters bytes that it does no understand. Otherwise, it is unknown 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.

  • The utf8mb4, utf16, utf16le, and utf32 character sets support BMP characters, as well as supplementary characters outside the BMP.

A.11.16.

Should CJK be CJKV?

No. The term CJKV (Chinese Japanese Korean Vietnamese) refers to Vietnamese character sets which contain Han (originally Chinese) characters. MySQL supports the modern Vietnamese script with Western characters, but does not support the old Vietnamese script using Han characters.

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

A.11.17.

Does MySQL permit CJK characters to be used in database and table names?

Yes.

A.11.18.

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

The Japanese translation of the MySQL 5.6 manual can be downloaded from https://dev.mysql.com/doc/.

A.11.19.

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

The following resources are available:

A.12 MySQL 8.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 8.0 FAQ: Replication

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

A.13.1. Must the slave be connected to the master all the time?
A.13.2. Must I enable networking on my master and slave to enable replication?
A.13.3. How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?
A.13.4. How do I force the master to block updates until the slave catches up?
A.13.5. What issues should I be aware of when setting up two-way replication?
A.13.6. How can I use replication to improve performance of my system?
A.13.7. What should I do to prepare client code in my own applications to use performance-enhancing replication?
A.13.8. When and how much can MySQL replication improve the performance of my system?
A.13.9. How can I use replication to provide redundancy or high availability?
A.13.10. How do I tell whether a master server is using statement-based or row-based binary logging format?
A.13.11. How do I tell a slave to use row-based replication?
A.13.12. How do I prevent GRANT and REVOKE statements from replicating to slave machines?
A.13.13. Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on OS X and Windows)?
A.13.14. Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?

A.13.1.

Must the slave be connected to the master all the time?

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.

A.13.2.

Must I enable networking on my master and slave to enable replication?

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.

A.13.3.

How do I know how late a slave is compared to the master? In other words, how do I know the date of the last statement replicated by the slave?

Check the Seconds_Behind_Master column in the output from SHOW SLAVE STATUS. See Section 17.1.7.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.2, “Replication Implementation Details”.

A.13.4.

How do I force the master to block updates until the slave catches up?

Use the following procedure:

  1. On the master, execute these statements:

    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;
    

    Record the replication coordinates (the current binary log file name and position) from the output of the SHOW statement.

  2. On the slave, issue the following statement, where the arguments to the MASTER_POS_WAIT() function are the replication coordinate values obtained in the previous step:

    mysql> SELECT MASTER_POS_WAIT('log_name', log_pos);
    

    The SELECT statement blocks until the slave reaches the specified log file and position. At that point, the slave is in synchrony with the master and the statement returns.

  3. On the master, issue the following statement to enable the master to begin processing updates again:

    mysql> UNLOCK TABLES;
    

A.13.5.

What issues should I be aware of when setting up two-way replication?

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.

A.13.6.

How can I use replication to improve performance of my system?

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 option, enable the low_priority_updates system variable, and set the delay_key_write system variable to ALL 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.

A.13.7.

What should I do to prepare client code in my own applications to use performance-enhancing replication?

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

A.13.8.

When and how much can MySQL replication improve the performance of my system?

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:

  • If N = 0 (which means we have no replication), our system can handle about 1200/11 = 109 writes per second.

  • If N = 1, we get up to 184 writes per second.

  • If N = 8, we get up to 400 writes per second.

  • If N = 17, we get up to 480 writes per second.

  • Eventually, as N approaches infinity (and our budget negative infinity), we can get very close to 600 writes per second, increasing system throughput about 5.5 times. However, with only eight servers, we increase it nearly four times.

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:

  • What is the read/write ratio on your system?

  • How much more write load can one server handle if you reduce the reads?

  • For how many slaves do you have bandwidth available on your network?

A.13.9.

How can I use replication to provide redundancy or high availability?

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.4.8, “Switching Masters During Failover”.

A.13.10.

How do I tell whether a master server is using statement-based or row-based binary logging format?

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.4.4.3, “Mixed Binary Logging Format”.

A.13.11.

How do I tell a slave to use row-based replication?

Slaves automatically know which format to use.

A.13.12.

How do I prevent GRANT and REVOKE statements from replicating to slave machines?

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

A.13.13.

Does replication work on mixed operating systems (for example, the master runs on Linux while slaves run on OS X and Windows)?

Yes.

A.13.14.

Does replication work on mixed hardware architectures (for example, the master runs on a 64-bit machine while slaves run on 32-bit machines)?

Yes.

A.14 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool

A.14.1. What is the Thread Pool and what problem does it solve?
A.14.2. How does the Thread Pool limit and manage concurrent sessions and transactions for optimal performance and throughput?
A.14.3. How is the Thread Pool different from the client side Connection Pool?
A.14.4. When should I use the Thread Pool?
A.14.5. Are there recommended Thread Pool configurations?

A.14.1.

What is the Thread Pool and what problem does it solve?

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. For MySQL 8.0, the Thread Pool plugin is included in MySQL Enterprise Edition, a commercial product.

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 5.6.3, “MySQL Enterprise Thread Pool”.

A.14.2.

How does the Thread Pool limit and manage concurrent sessions and transactions for optimal performance and throughput?

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 5.6.3.3, “Thread Pool Operation”.

A.14.3.

How is the Thread Pool different from the client side Connection Pool?

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 back-end 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 back-end 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 28, Connectors and APIs.

A.14.4.

When should I use the Thread Pool?

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 5.6.3.4, “Thread Pool Tuning”.

A.14.5.

Are there recommended Thread Pool configurations?

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 5.6.3.4, “Thread Pool Tuning”.

A.15 MySQL 8.0 FAQ: InnoDB Change Buffer

A.15.1. What types of operations modify secondary indexes and result in change buffering?
A.15.2. What is the benefit of the InnoDB change buffer?
A.15.3. Does the change buffer support other types of indexes?
A.15.4. How much space does InnoDB use for the change buffer?
A.15.5. How do I determine the current size of the change buffer?
A.15.6. When does change buffer merging occur?
A.15.7. When is the change buffer flushed?
A.15.8. When should the change buffer be used?
A.15.9. When should the change buffer not be used?
A.15.10. Where can I find additional information about the change buffer?

A.15.1.

What types of operations modify secondary indexes and result in change buffering?

INSERT, UPDATE, and DELETE operations can modify secondary indexes. If an affected index page is not in the buffer pool, the changes can be buffered in the change buffer.

A.15.2.

What is the benefit of the InnoDB change buffer?

Buffering secondary index changes when secondary index pages are not in the buffer pool avoids expensive random access I/O operations that would be required to immediately read in affected index pages from disk. Buffered changes can be applied later, in batches, as pages are read into the buffer pool by other read operations.

A.15.3.

Does the change buffer support other types of indexes?

No. The change buffer only supports secondary indexes. Clustered indexes, full-text indexes, and spatial indexes are not supported. Full-text indexes have their own caching mechanism.

A.15.4.

How much space does InnoDB use for the change buffer?

Prior to the introduction of the innodb_change_buffer_max_size configuration option in MySQL 5.6, the maximum size of the on-disk change buffer in the system tablespace was 1/3 of the InnoDB buffer pool size.

In MySQL 5.6 and later, the innodb_change_buffer_max_size configuration option defines the maximum size of the change buffer as a percentage of the total buffer pool size. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50.

InnoDB does not buffer an operation if it would cause the on-disk change buffer to exceed the defined limit.

Change buffer pages are not required to persist in the buffer pool and may be evicted by LRU operations.

A.15.5.

How do I determine the current size of the change buffer?

The current size of the change buffer is reported by SHOW ENGINE INNODB STATUS \G, under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading. For example:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges

Relevant data points include:

  • size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.

  • seg size: The size of the change buffer, in pages.

For information about monitoring change buffer status, see Section 15.5.2, “Change Buffer”.

A.15.6.

When does change buffer merging occur?

  • When a page is read into the buffer pool, buffered changes are merged upon completion of the read, before the page is made available.

  • Change buffer merging is performed as a background task. The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by InnoDB background tasks such as merging data from the change buffer.

  • A change buffer merge is performed during crash recovery. Changes are applied from the change buffer (in the system tablespace) to leaf pages of secondary indexes as index pages are read into the buffer pool.

  • The change buffer is fully durable and will survive a system crash. Upon restart, change buffer merge operations resume as part of normal operations.

  • A full merge of the change buffer can be forced as part of a slow server shutdown using --innodb-fast-shutdown=0.

A.15.7.

When is the change buffer flushed?

Updated pages are flushed by the same flushing mechanism that flushes the other pages that occupy the buffer pool.

A.15.8.

When should the change buffer be used?

The change buffer is a feature designed to reduce random I/O to secondary indexes as indexes grow larger and no longer fit in the InnoDB buffer pool. Generally, the change buffer should be used when the entire data set does not fit into the buffer pool, when there is substantial DML activity that modifies secondary index pages, or when there are lots of secondary indexes that are regularly changed by DML activity.

A.15.9.

When should the change buffer not be used?

You might consider disabling the change buffer if the entire data set fits within the InnoDB buffer pool, if you have relatively few secondary indexes, or if you are using solid-state storage, where random reads are about as fast as sequential reads. Before making configuration changes, it is recommended that you run tests using a representative workload to determine if disabling the change buffer provides any benefit.

A.15.10.

Where can I find additional information about the change buffer?

See Section 15.5.2, “Change Buffer”.

A.16 MySQL 8.0 FAQ: InnoDB Data-at-Rest Encryption

A.16.1. Is data decrypted for users who are authorized to see it?
A.16.2. What is the overhead associated with InnoDB data-at-rest encryption?
A.16.3. What are the encryption algorithms used with InnoDB data-at-rest encryption?
A.16.4. Is it possible to use 3rd party encryption algorithms in place of the one provided by the InnoDB data-at-rest encryption feature?
A.16.5. Can indexed columns be encrypted?
A.16.6. What data types and data lengths does InnoDB data-at-rest encryption support?
A.16.7. Does data remain encrypted on the network?
A.16.8. Does database memory contain cleartext or encrypted data?
A.16.9. How do I know which data to encrypt?
A.16.10. How is InnoDB data-at-rest encryption different from encryption functions MySQL already provides?
A.16.11. Does the transportable tablespaces feature work with InnoDB data-at-rest encryption?
A.16.12. Does compression work with InnoDB data-at-rest encryption?
A.16.13. Can I use mysqlpump or mysqldump with encrypted tables?
A.16.14. How do I change (rotate, re-key) the master encryption key?
A.16.15. How do I migrate data from a cleartext InnoDB tablespace to an encrypted InnoDB tablespace?

A.16.1.

Is data decrypted for users who are authorized to see it?

Yes. InnoDB data-at-rest encryption is designed to transparently apply encryption within the database without impacting existing applications. Returning data in encrypted format would break most existing applications. InnoDB data-at-rest encryption provides the benefit of encryption without the overhead associated with traditional database encryption solutions, which would typically require expensive and substantial changes to applications, database triggers, and views.

A.16.2.

What is the overhead associated with InnoDB data-at-rest encryption?

There is no additional storage overhead. According to internal benchmarks, performance overhead amounts to a single digit percentage difference.

A.16.3.

What are the encryption algorithms used with InnoDB data-at-rest encryption?

InnoDB data-at-rest encryption supports the Advanced Encryption Standard (AES256) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption.

A.16.4.

Is it possible to use 3rd party encryption algorithms in place of the one provided by the InnoDB data-at-rest encryption feature?

No, it is not possible to use other encryption algorithms. The provided encryption algorithm is broadly accepted.

A.16.5.

Can indexed columns be encrypted?

InnoDB data-at-rest encryption supports all indexes transparently.

A.16.6.

What data types and data lengths does InnoDB data-at-rest encryption support?

InnoDB data-at-rest encryption supports all supported data types. There is no data length limitation.

A.16.7.

Does data remain encrypted on the network?

Data encrypted by the InnoDB data-at-rest feature is decrypted when it is read from the tablespace file. Thus, if the data is on the network, it is in cleartext form. However, data on the network can be encrypted using MySQL network encryption, which encrypts data traveling to and from a database using SSL/TLS.

A.16.8.

Does database memory contain cleartext or encrypted data?

With InnoDB data-at-rest encryption, in-memory data is decrypted, which provides complete transparency.

A.16.9.

How do I know which data to encrypt?

Compliance with the PCI-DSS standard requires that credit card numbers (Primary Account Number, or 'PAN') be stored in encrypted form. Breach Notification Laws (for example, CA SB 1386, CA AB 1950, and similar laws in 43+ more US states) require encryption of first name, last name, driver license number, and other PII data. In early 2008, CA AB 1298 added medical and health insurance information to PII data. Additionally, industry specific privacy and security standards may require encryption of certain assets. For example, assets such as pharmaceutical research results, oil field exploration results, financial contracts, or personal data of law enforcement informants may require encryption. In the health care industry, the privacy of patient data, health records and X-ray images is of the highest importance.

A.16.10.

How is InnoDB data-at-rest encryption different from encryption functions MySQL already provides?

There are symmetric and asymmetric encryption APIs in MySQL that can be used to manually encrypt data within the database. However, the application must manage encryption keys and perform required encryption and decryption operations by calling API functions. InnoDB data-at-rest encryption requires no application changes, is transparent to end users, and provides automated, built-in key management.

A.16.11.

Does the transportable tablespaces feature work with InnoDB data-at-rest encryption?

Yes. It is supported for encrypted file-per-table tablespaces. For more information, see Exporting Encrypted Tablespaces.

A.16.12.

Does compression work with InnoDB data-at-rest encryption?

Customers using InnoDB data-at-rest encryption receive the full benefit of compression because compression is applied before data blocks are encrypted.

A.16.13.

Can I use mysqlpump or mysqldump with encrypted tables?

Yes. Because these utilities create logical backups, the data dumped from encrypted tables is not encrypted.

A.16.14.

How do I change (rotate, re-key) the master encryption key?

InnoDB data-at-rest encryption uses a two tier key mechanism. When data-at-rest encryption is used, individual tablespace keys are stored in the header of the underlying tablespace data file. Tablespace keys are encrypted using the master encryption key. The master encryption key is generated when tablespace encryption is enabled, and is stored outside the database. The master encryption key is rotated using the ALTER INSTANCE ROTATE INNODB MASTER KEY statement, which generates a new master encryption key, stores the key, and rotates the key into use.

A.16.15.

How do I migrate data from a cleartext InnoDB tablespace to an encrypted InnoDB tablespace?

Transferring data from one tablespace to another is not required. To encrypt data in an InnoDB file-per-table tablespace, run ALTER TABLE tbl_name ENCRYPTION = 'Y'. To encrypt a general tablespace or the mysql tablespace, run ALTER TABLESPACE tablespace_name ENCRYPTION = 'Y'. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption support for the mysql system tablespace is available as of MySQL 8.0.16.

A.17 MySQL 8.0 FAQ: Virtualization Support

A.17.1. Is MySQL supported on virtualized environments such as Oracle VM, VMWare, Docker, Microsoft Hyper-V, or others?

A.17.1.

Is MySQL supported on virtualized environments such as Oracle VM, VMWare, Docker, Microsoft Hyper-V, or others?

MySQL is supported on virtualized environments, but is certified only for Oracle VM. Contact Oracle Support for more information.

Be aware of potential problems when using virtualization software. The usual ones are related to performance, performance degradations, slowness, or unpredictability of disk, I/O, network, and memory.