Remote Services for MySQL

Oracle E-Business Suite R12.2 Upgrade Training & R12.2 Consulting

TruTek's Remote Services for MySQL

The MySQL database server is the world's most popular open source database. Its architecture makes it extremely fast and easy to customize. Extensive reuse of code within the software and a minimalistic approach to producing functionally-rich features has resulted in a database management system unmatched in speed, compactness, stability and ease of deployment. The unique separation of the core server from the storage engine makes it possible to run with strict transaction control or with ultra-fast transactionless disk access, whichever is most appropriate for the situation.

MySQL 5.7, the latest certified production-ready release includes numerous enhancements that improve performance, internationalization, and the ability to embed the MySQL Server with other hardware and software solutions. Major enhancements include:

Security improvements. These security enhancements were added:

The server now requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value.  DBAs are advised to also convert accounts that use the mysql_old_password authentication plugin to use mysql_native_password instead, because support for mysql_old_password has been removed.

MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.

Administrators can lock and unlock accounts for better control over who can log in.

To make it easier to support secure connections, MySQL servers compiled using OpenSSL can automatically generate missing SSL and RSA certificate and key files at startup.

All servers (whether compiled using OpenSSL or yaSSL), if not configured for SSL explicitly, attempt to enable SSL automatically at startup if they find the requisite SSL files in the data directory.

In addition, MySQL distributions include a mysql_ssl_rsa_setup utility that can be invoked manually to create SSL and RSA key and certificate files.

MySQL deployments installed using mysqld --initialize are secure by default. The following changes have been implemented as the default deployment characteristics:

The installation process creates only a single root account, 'root'@'localhost', automatically generates a random password for this account, and marks the password expired. The MySQL administrator must connect as root using the random password and assign a new password. (The server writes the random password to the error log.)

Installation creates no anonymous-user accounts.

Installation creates no test database.

SQL mode changes. Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.

Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes are now deprecated but enabled by default. The long term plan is to have them included in strict SQL mode and to remove them as explicit modes in a future MySQL release.

The changes to the default SQL mode result in a default sql_mode system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

Online ALTER TABLE. ALTER TABLE now supports a RENAME INDEX clause that renames an index. The change is made in place without a table-copy operation. It works for all storage engines.

ngram and MeCab full-text parser plugins. As of MySQL 5.7.6, MySQL provides a built-in full-text ngram parser plugin that supports Chinese, Japanese, and Korean (CJK), and an installable MeCab full-text parser plugin for Japanese.

InnoDB enhancements. These InnoDB enhancements were added:

VARCHAR size may be increased using an in-place ALTER TABLE, as in this example:

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

This is true as long as the number of length bytes required by a VARCHAR column remains the same. For VARCHAR values of 0 to 255, one length byte is required to encode the value. For VARCHAR values of 256 bytes or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR size from 0 to 255 bytes or increasing VARCHAR size from a value equal to or greater than 256 bytes.

In-place ALTER TABLE does not support increasing VARCHAR size from less than 256 bytes to a value equal to or greater than 256 bytes. In this case, the number of required length bytes would change from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size from 255 to 256 using in-place ALTER TABLE would return an error:

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

DDL performance for InnoDB temporary tables is improved through optimization of CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements.

InnoDB temporary table metadata is no longer stored to InnoDB system tables. Instead, a new table, INNODB_TEMP_TABLE_INFO, provides users with a snapshot of active temporary tables. The table contains metadata and reports on all user and system-created temporary tables that are active within a given InnoDB instance. The table is created when the first SELECT statement is run against it.

InnoDB now supports MySQL-supported spatial data types. Prior to this release, InnoDB would store spatial data as binary BLOB data. BLOB remains the underlying data type but spatial data types are now mapped to a new InnoDB internal data type, DATA_GEOMETRY.

There is now a separate tablespace for all non-compressed InnoDB temporary tables. The new tablespace is always recreated on server startup and is located in DATADIR by default. A newly added configuration file option, innodb_temp_data_file_path, allows for a user-defined temporary data file path.

In MySQL 5.7.2, innochecksum functionality is enhanced with several new options and extended capabilities.

A new type of non-redo undo log for both normal and compressed temporary tables and related objects now resides in the temporary tablespace.

In MySQL 5.7.2, InnoDB buffer pool dump and load operations are enhanced. A new system variable, innodb_buffer_pool_dump_pct, allows you to specify the percentage of most recently used pages in each buffer pool to read out and dump. When there is other I/O activity being performed by InnoDB background tasks, InnoDB attempts to limit the number of buffer pool load operations per second using the innodb_io_capacity setting.

In MySQL 5.7.3, support is added to InnoDB for full-text parser plugins.

As of MySQL 5.7.4, InnoDB supports multiple page cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page cleaner threads. The default value of 1 maintains the pre-MySQL 5.7.4 configuration in which there is a single page cleaner thread. This enhancement builds on work completed in MySQL 5.6, which introduced a single page cleaner thread to offload buffer pool flushing work from the InnoDB master thread.

As of MySQL 5.7.4, MySQL supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for the following operations:

OPTIMIZE TABLE

ALTER TABLE ... FORCE

ALTER TABLE ... ENGINE=INNODB (when run on an InnoDB table)

Online DDL support reduces table rebuild time and permits concurrent DML, which helps reduce user application downtime. For additional information, see Section 14.10.1, “Overview of Online DDL”.

The Fusion-io Non-Volatile Memory (NVM) file system on Linux provides atomic write capability, which makes the InnoDB doublewrite buffer redundant. In MySQL 5.7.4, the InnoDB doublewrite buffer is automatically disabled for system tablespace files (ibdata files) located on Fusion-io devices that support atomic writes.

As of MySQL 5.7.4, InnoDB supports the Transportable Tablespace feature for partitioned InnoDB tables and individual InnoDB table partitions. This enhancement eases backup procedures for partitioned tables and enables copying of partitioned tables and individual table partitions between MySQL instances.

As of MySQL 5.7.5, the innodb_buffer_pool_size parameter is dynamic, allowing you to resize the buffer pool without restarting the server. The resizing operation, which involves moving pages to a new location in memory, is performed in chunks. Chunk size is configurable using the new innodb_buffer_pool_chunk_size configuration option. You can monitor resizing progress using the new Innodb_buffer_pool_resize_status status variable. For more information, see Section 14.3.3.7, “Resizing the InnoDB Buffer Pool Online”.

Multi-threaded page cleaner support (innodb_page_cleaners) is extended to shutdown and recovery phases in MySQL 5.7.5.

As of MySQL 5.7.5, InnoDB supports indexing of spatial data types using SPATIAL indexes, including use of ALTER TABLE ... ALGORITHM=INPLACE for online operations (ADD SPATIAL INDEX).

As of MySQL 5.7.5, InnoDB performs a bulk load when creating or rebuilding indexes. This method of index creation is known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option, innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth.

As of MySQL 5.7.5, A new log record type (MLOG_FILE_NAME) is used to identify tablespaces that have been modified since the last checkpoint. This enhancement simplifies tablespace discovery during crash recovery and eliminates scans on the file system prior to redo log application.

This enhancement changes the redo log format, requiring that MySQL be shut down cleanly before upgrading to or downgrading from MySQL 5.7.5.

As of MySQL 5.7.5, you can truncate undo logs that reside in undo tablespaces. This feature is enabled using the innodb_undo_log_truncate configuration option.

As of MySQL 5.7.6, InnoDB supports native partitioning. Previously, InnoDB relied on the ha_partition handler, which creates a handler object for each partition. With native partitioning, a partitioned InnoDB table uses a single partition-aware handler object. This enhancement reduces the amount of memory required for partitioned InnoDB tables.

As of MySQL 5.7.9, mysql_upgrade looks for and attempts to upgrade partitioned InnoDB tables that were created using the ha_partition handler. Also in MySQL 5.7.9 and later, you can upgrade such tables by name in the mysql client using ALTER TABLE ... UPGRADE PARTITIONING.

As of MySQL 5.7.6, InnoDB supports the creation of general tablespaces using CREATE TABLESPACE syntax.

CREATE TABLESPACE `tablespace_name` ADD DATAFILE 'file_name.ibd' [FILE_BLOCK_SIZE = n]

General tablespaces can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.

Tables are added to a general tablespace using CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name syntax.

In MySQL 5.7.9, DYNAMIC replaces COMPACT as the implicit default row format for InnoDB tables. A new configuration option, innodb_default_row_format, specifies the default InnoDB row format.

JSON support. Beginning with MySQL 5.7.8, MySQL supports a native JSON type. JSON values are not stored as strings, instead using an internal binary format that permits quick read access to document elements. JSON documents stored in JSON columns are automatically validated whenever they are inserted or updated, with an invalid document producing an error. JSON documents are normalized on creation, and can be compared using most comparison operators such as =, <, <=, >, >=, <>, !=, and <=>.


System and status variables. System and status variable information is now available in Performance Schema tables, in preference to use of INFORMATION_SCHEMA tables to obtain these variable. This also affects the operation of the SHOW VARIABLES and SHOW STATUS statements. The value of the show_compatibility_56 system variable affects the output produced from and privileges required for system and status variable statements and tables. For details, see the description of that variable in Section 5.1.4, “Server System Variables”.

Condition handling. MySQL now supports stacked diagnostics areas. When the diagnostics area stack is pushed, the first (current) diagnostics area becomes the second (stacked) diagnostics area and a new current diagnostics area is created as a copy of it. Within a condition handler, executed statements modify the new current diagnostics area, but GET STACKED DIAGNOSTICS can be used to inspect the stacked diagnostics area to obtain information about the condition that caused the handler to activate, independent of current conditions within the handler itself. (Previously, there was a single diagnostics area. To inspect handler-activating conditions within a handler, it was necessary to check this diagnostics area before executing any statements that could change it.)

Optimizer. These optimizer enhancements were added:

EXPLAIN can be used to obtain the execution plan for an explainable statement executing in a named connection:

EXPLAIN [options] FOR CONNECTION connection_id;

It is possible to provide hints to the optimizer within individual SQL statements, which enables finer control over statement execution plans than can be achieved using the optimizer_switch system variable. Hints are also permitted in statements used with EXPLAIN, enabling you to see how hints affect execution plans.

Triggers. Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted.

Logging. These logging enhancements were added:

Previously, on Unix and Unix-like systems, MySQL support for sending the server error log to syslog was implemented by having mysqld_safe capture server error output and pass it to syslog. The server now includes native syslog support, which has been extended to include Windows.

The mysql client now has a --syslog option that causes interactive statements to be sent to the system syslog facility. Logging is suppressed for statements that match the default “ignore” pattern list ("*IDENTIFIED*:*PASSWORD*"), as well as statements that match any patterns specified using the --histignore option.

Generated Columns. MySQL now supports the specification of generated columns in CREATE TABLE and ALTER TABLE statements. Values of a generated column are computed from an expression specified at column creation time. Generated columns can be virtual (computed “on the fly” when rows are read) or stored (computed when rows are inserted or updated). For more information, see CREATE TABLE and Generated Columns.

mysql client. Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.

Database name rewriting with mysqlbinlog. Renaming of databases by mysqlbinlog when reading from binary logs written using the row-based format is now supported using the --rewrite-db option added in MySQL 5.7.1.

This option uses the format --rewrite-db='dboldname->dbnewname'. You can implement multiple rewrite rules, by specifying the option multiple times.

HANDLER with partitioned tables. The HANDLER statement may now be used with user-partitioned tables. Such tables may use any of the available partitioning types (see Section 18.2, “Partitioning Types”).

Index condition pushdown support for partitioned tables. In MySQL 5.7.3 and later, queries on partitioned tables using the InnoDB or MyISAM storage engine may employ the index condition pushdown optimization that was introduced in MySQL 5.6. See Section 8.2.1.6, “Index Condition Pushdown Optimization”, for more information.

WITHOUT VALIDATION support for ALTER TABLE ... EXCHANGE PARTITION. As of MySQL 5.7.5, ALTER TABLE ... EXCHANGE PARTITION syntax includes an optional {WITH|WITHOUT} VALIDATION clause. When WITHOUT VALIDATION is specified, ALTER TABLE ... EXCHANGE PARTITION does not perform row-by-row validation when exchanging a populated table with the partition, permitting database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition. WITH VALIDATION is the default behavior and need not be specified explicitly. For more information, see Section 18.3.3, “Exchanging Partitions and Subpartitions with Tables”.


Multi-source replication is now possible. MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server.

As part of MySQL Multi-Source Replication, replication channels have been added. Replication channels enable a slave to open multiple connections to replicate from, with each channel being a connection to a master.

Group Replication Performance Schema tables. MySQL 5.7 adds a number of new tables to the Performance Schema to provide information about replication groups and channels. These include the following tables:

replication_applier_configuration

replication_applier_status

replication_applier_status_by_coordinator

replication_applier_status_by_worker

replication_connection_configuration

replication_connection_status

replication_group_members

replication_group_member_stats

All of these tables were added in MySQL 5.7.2, except for replication_group_members and replication_group_member_stats, which were added in MySQL 5.7.6.

Group Replication SQL. The following statements were added in MySQL 5.7.6 for controlling group replication:

START GROUP_REPLICATION

STOP GROUP_REPLICATION