MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Before downgrading from MySQL 5.7, review the information in this section. Some items may require action before downgrading.
In MySQL 5.7.13, system table columns that store user@host
string values were increased in length. Before downgrading to
a previous release, ensure that there are no user@host values
that exceed the previous 77 character length limit, and
perform the following mysql
system table
alterations:
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT ''; ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
The maximum length of MySQL user names was increased from 16
characters to 32 characters in MySQL 5.7.8. Before downgrading
to a previous release, ensure that there are no user names
greater than 16 characters in length, and perform the
following mysql
system table alterations:
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default ''; ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default ''; ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default ''; ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default ''; ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
The Password
column of the
mysql.user
system table was removed in
MySQL 5.7.6. All credentials are stored in the
authentication_string
column, including
those formerly stored in the Password
column. To make the mysql.user
table
compatible with previous releases, perform the following
alterations before downgrading:
ALTER TABLE mysql.user ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER user; UPDATE mysql.user SET password = authentication_string WHERE LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password'; UPDATE mysql.user SET authentication_string = '' WHERE LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
The help_*
and
time_zone*
system tables changed from
MyISAM
to InnoDB
in
MySQL 5.7.5. Before downgrading to a previous release, change
each affected table back to MyISAM
by
running the following statements:
ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.time_zone_transition ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
The mysql.plugin
and
mysql.servers
system tables changed from
MyISAM
to InnoDB
in
MySQL 5.7.6. Before downgrading to a previous release, change
each affected table back to MyISAM
by
running the following statements:
ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT; ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
The definition of the plugin
column in the
mysql.user
system table differs in MySQL
5.7. Before downgrading to a MySQL 5.6 server for versions
5.6.23 and higher, alter the plugin
column
definition using this statement:
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin DEFAULT 'mysql_native_password';
Before downgrading to a MySQL 5.6.22 server or older, alter
the plugin
column definition using this
statement:
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin DEFAULT '';
As of MySQL 5.7.7, the sys
schema is
installed by default during data directory installation.
Before downgrading to a previous version, it is recommended
that you drop the sys
schema:
DROP DATABASE sys;
If you are downgrading to a release that includes the
sys
schema,
mysql_upgrade recreates the
sys
schema in a compatible form. The
sys
schema is not included in MySQL 5.6.
As of MySQL 5.7.5, the FIL_PAGE_FLUSH_LSN
field, written to the first page of each
InnoDB
system tablespace file and to
InnoDB
undo tablespace files, is only
written to the first file of the InnoDB
system tablespace (page number 0:0). As a result, if you have
a multiple-file system tablespace and decide to downgrade from
MySQL 5.7 to MySQL 5.6, you may encounter an invalid message
on MySQL 5.6 startup stating that the log sequence
numbers x
and
y
in ibdata files do not match the
log sequence number y
in the
ib_logfiles. If you encounter this message,
restart MySQL 5.6. The invalid message should no longer
appear.
To simplify InnoDB
tablespace discovery
during crash recovery, new redo log record types were
introduced in MySQL 5.7.5. This enhancement changes the redo
log format. Before performing an in-place downgrade from MySQL
5.7.5 or later, perform a clean shutdown using an
innodb_fast_shutdown
setting
of 0
or 1
. A slow
shutdown using
innodb_fast_shutdown=0
is a
recommended step in
In-Place Downgrade.
MySQL 5.7.8 and 5.7.9 undo logs could contain insufficient
information about spatial columns (Bug #21508582). Before
performing an in-place downgrade from MySQL 5.7.10 or higher
to MySQL 5.7.9 or earlier, perform a slow shutdown using
innodb_fast_shutdown=0
to
clear the undo logs. A slow shutdown using
innodb_fast_shutdown=0
is a
recommended step in
In-Place Downgrade.
MySQL 5.7.8 undo logs could contain insufficient information
about virtual columns and virtual column indexes (Bug
#21869656). Before performing an in-place downgrade from MySQL
5.7.9 or later to MySQL 5.7.8 or earlier, perform a slow
shutdown using
innodb_fast_shutdown=0
to
clear the undo logs. A slow shutdown using
innodb_fast_shutdown=0
is a
recommended step in
In-Place Downgrade.
As of MySQL 5.7.9, the redo log header of the first redo log
file (ib_logfile0
) includes a format
version identifier and a text string that identifies the MySQL
version that created the redo log files. This enhancement
changes the redo log format. To prevent older versions of
MySQL from starting on redo log files created in MySQL 5.7.9
or later, the checksum for redo log checkpoint pages was
changed. As a result, you must perform a slow shutdown of
MySQL (using innodb_fast_shutdown=0) and remove the redo log
files (the ib_logfile*
files) before
performing an in-place downgrade. A slow shutdown using
innodb_fast_shutdown=0
and
removing the redo log files are recommended steps in
In-Place Downgrade.
A new compression version used by the
InnoDB
page compression feature was added
in MySQL 5.7.32. The new compression version is not compatible
with earlier MySQL releases. Creating a page compressed table
in MySQL 5.7.32 or higher and accessing the table after
downgrading to a release earlier than MySQL 5.7.32 causes a
failure. As a workaround, uncompress such tables before
downgrading. To uncompress a table, run
ALTER TABLE
and tbl_name
COMPRESSION='None'OPTIMIZE
TABLE
. For information about the
InnoDB
page compression feature, see
Section 14.9.2, “InnoDB Page Compression”.
Support for sending the server error log to
syslog
in MySQL 5.7.5 and up differs from
older versions. If you use syslog
and
downgrade to a version older than 5.7.5, you must stop using
the relevant mysqld system variables and
use the corresponding mysqld_safe command
options instead. Suppose that you use
syslog
by setting these system variables in
the [mysqld]
group of an option file:
[mysqld] log_syslog=ON log_syslog_tag=mytag
To downgrade, remove those settings and add option settings in
the [mysqld_safe]
option file group:
[mysqld_safe] syslog syslog-tag=mytag
syslog
-related system variables that have
no corresponding mysqld_safe option cannot
be used after a downgrade.
A trigger can have triggers for different combinations of
trigger event (INSERT
,
UPDATE
,
DELETE
) and action time
(BEFORE
, AFTER
), but
before MySQL 5.7.2 cannot have multiple triggers that have the
same trigger event and action time. MySQL 5.7.2 lifts this
limitation and multiple triggers are permitted. This change
has implications for downgrades.
If you downgrade a server that supports multiple triggers to an older version that does not, the downgrade has these effects:
For each table that has triggers, all trigger definitions
remain in the .TRG
file for the
table. However, if there are multiple triggers with the
same trigger event and action time, the server executes
only one of them when the trigger event occurs. For
information about .TRG
files, see
Table
Trigger Storage.
If triggers for the table are added or dropped subsequent
to the downgrade, the server rewrites the table's
.TRG
file. The rewritten file retains
only one trigger per combination of trigger event and
action time; the others are lost.
To avoid these problems, modify your triggers before downgrading. For each table that has multiple triggers per combination of trigger event and action time, convert each such set of triggers to a single trigger as follows:
For each trigger, create a stored routine that contains
all the code in the trigger. Values accessed using
NEW
and OLD
can be
passed to the routine using parameters. If the trigger
needs a single result value from the code, you can put the
code in a stored function and have the function return the
value. If the trigger needs multiple result values from
the code, you can put the code in a stored procedure and
return the values using OUT
parameters.
Drop all triggers for the table.
Create one new trigger for the table that invokes the stored routines just created. The effect for this trigger is thus the same as the multiple triggers it replaces.