After upgrading a 5.0 installation to 5.0.10 or above, it is necessary to upgrade your grant tables. Otherwise, creating stored procedures and functions might not work. To perform this upgrade, run mysql_upgrade.
It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, you should protect your data by making a backup.
To upgrade to 5.1 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading.
If you perform a binary (in-place) upgrade without dumping and
reloading tables, you cannot upgrade directly from MySQL 4.1
to 5.1. This occurs due to an incompatible change
in the MyISAM
table index format in MySQL
5.0. Upgrade from MySQL 4.1 to 5.0 and repair all
MyISAM
tables (see
Section 2.4.4, “Rebuilding or Repairing Tables or Indexes”). Then upgrade from MySQL
5.0 to 5.1 and check and repair your tables.
In general, you should do the following when upgrading from MySQL 5.0 to 5.1:
Read all the items in the following sections to see whether any of them might affect your applications:
Section 2.4.1, “Upgrading MySQL”, has general update information.
The items in the change lists found later in this section enable you to identify upgrade issues that apply to your current MySQL installation.
The MySQL 5.1 change history describes significant new features you can use in 5.1 or that differ from those found in MySQL 5.0. Some of these changes may result in incompatibilities. See Section C.1, “Changes in Release 5.1.x (Production)”.
Note particularly any changes that are marked
Known issue or
Incompatible change. These
incompatibilities with earlier versions of MySQL may require
your attention before you upgrade. Our
aim is to avoid these changes, but occasionally they are
necessary to correct problems that would be worse than an
incompatibility between releases. If any upgrade issue
applicable to your installation involves an incompatibility
that requires special handling, follow the instructions
given in the incompatibility description. Often this will
involve dumping and reloading tables, or use of a statement
such as CHECK TABLE
or
REPAIR TABLE
.
For dump and reload instructions, see
Section 2.4.4, “Rebuilding or Repairing Tables or Indexes”. Any procedure that
involves REPAIR TABLE
with
the USE_FRM
option
must be done before upgrading. Use of
this statement with a version of MySQL different from the
one used to create the table (that is, using it after
upgrading) may damage the table. See
Section 12.4.2.6, “REPAIR TABLE
Syntax”.
Before upgrading to a new version of MySQL, Section 2.4.3, “Checking Whether Tables or Indexes Must Be Rebuilt”, to see whether changes to table formats or to character sets or collations were made between your current version of MySQL and the version to which you are upgrading. If so and these changes result in an incompatibility between MySQL versions, you will need to upgrade the affected tables using the instructions in Section 2.4.4, “Rebuilding or Repairing Tables or Indexes”.
After upgrading to a new version of MySQL, run mysql_upgrade (see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”). This program checks your tables, and attempts to repair them if necessary. It also updates your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)
If you run MySQL Server on Windows, see Section 2.5.7, “Upgrading MySQL on Windows”.
If you use replication, see Section 16.4.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.
If your MySQL installation contains a large amount of data that
might take a long time to convert after an in-place upgrade, you
might find it useful to create a “dummy” database
instance for assessing what conversions might be needed and the
work involved to perform them. Make a copy of your MySQL
instance that contains a full copy of the
mysql
database, plus all other databases
without data. Run your upgrade procedure on this dummy instance
to see what actions might be needed so that you can better
evaluate the work involved when performing actual data
conversion on your original database instance.
MySQL Enterprise. MySQL Enterprise subscribers will find more information about upgrading in the Knowledge Base articles found at Upgrading. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
The following lists describe changes that may affect applications and that you should watch out for when upgrading from MySQL 5.0 to 5.1.
Configuration Changes:
Before MySQL 5.1.11, to build MySQL from source with SSL
support enabled, you would invoke
configure with either the
--with-openssl
or
--with-yassl
option. In MySQL 5.1.11, those
options both have been replaced by the
--with-ssl
option. By default,
--with-ssl
causes the bundled yaSSL library
to be used. To select OpenSSL instead, give the option as
--with-ssl=
,
where path
path
is the directory where
the OpenSSL header files and libraries are located.
Server Changes:
Known issue: After a binary
upgrade to MySQL 5.1 from a MySQL 5.0 installation that
contains ARCHIVE
tables,
accessing those tables will cause the server to crash, even
if you have run mysql_upgrade or
CHECK TABLE ...
FOR UPGRADE
. To work around this problem, use
mysqldump to dump all
ARCHIVE
tables before
upgrading, and reload them into MySQL 5.1 after upgrading.
Known issue: The fix for
Bug#23491 introduced a problem with
SHOW CREATE VIEW
, which is
used by mysqldump. This causes an
incompatibility when upgrading from versions affected by
that bug fix (MySQL 5.0.40 through 5.0.43, MySQL 5.1.18
through 5.1.19): If you use mysqldump
before upgrading from an affected version and reload the
data after upgrading to a higher version, you must drop and
recreate your views.
Known issue: Dumps performed by using mysqldump to generate a dump file before the upgrade and reloading the file after upgrading are subject to the following problem:
Before MySQL 5.0.40, mysqldump displays
SPATIAL
index definitions using prefix
lengths for the indexed columns. These prefix lengths are
accepted in MySQL 5.0, but not as of MySQL 5.1. If you use
mysqldump from versions of MySQL older
than 5.0.40, any table containing SPATIAL
indexes will cause an error when the dump file is reloaded
into MySQL 5.1 or higher.
For example, a table definition might look like this when dumped in MySQL 5.0:
CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
The SPATIAL
index definition will not be
accepted in MySQL 5.1. To work around this, edit the dump
file to remove the prefix:
CREATE TABLE `t` ( `g` geometry NOT NULL, SPATIAL KEY `g` (`g`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
Dump files can be large, so it may be preferable to dump table definitions and data separately to make it easier to edit the definitions:
shell>mysqldump --no-data
shell>other_args
> definitions.sqlmysqldump --no-create-info
other_args
> data.sql
Then edit definitions.sql
before
reloading definitions.sql
and
data.sql
, in that order.
If you upgrade to a version of MySQL 5.0 higher than 5.0.40 before upgrading to MySQL 5.1, this problem does not occur.
Known issue: Before MySQL
5.1.30, the
CHECK TABLE ...
FOR UPGRADE
statement did not check for
incompatible collation changes made in MySQL 5.1.24. (This
also affects mysqlcheck and
mysql_upgrade, which cause that statement
to be executed.)
Prior to the fix made in 5.1.30, a binary upgrade (performed
without dumping tables with mysqldump
before the upgrade and reloading the dump file after the
upgrade) would corrupt tables. After the fix,
CHECK TABLE ...
FOR UPGRADE
properly detects the problem and warns
about tables that need repair.
However, the fix is not backward compatible and can result in a downgrading problem under these circumstances:
Perform a binary upgrade to a version of MySQL that includes the fix.
Run CHECK
TABLE ... FOR UPGRADE
(or
mysqlcheck or
mysql_upgrade) to upgrade tables.
Perform a binary downgrade to a version of MySQL that does not include the fix.
The solution is to dump tables with mysqldump before the downgrade and reload the dump file after the downgrade. Alternatively, drop and recreate affected indexes.
Known issue: MySQL
introduces encoding for table names that have non-ASCII
characters (see Section 8.2.3, “Mapping of Identifiers to File Names”). After
a binary upgrade from MySQL 5.0 to 5.1 or higher, the server
recognizes names that have non-ASCII characters and adds a
#mysql50#
prefix to them.
As of MySQL 5.1.31, mysql_upgrade encodes these names by executing the following command:
mysqlcheck --all-databases --check-upgrade --fix-db-names --fix-table-names
Prior to MySQL 5.1.31, mysql_upgrade does not execute this command, so you should execute it manually if you have database or table names that contain nonalphanumeric characters.
Prior to MySQL 5.1.23, the mysqlcheck command does not perform the name encoding for views. To work around this problem, drop each affected view and recreate it.
mysqlcheck cannot fix names that contain
literal instances of the @
character that
is used for encoding special characters. If you have
databases or tables that contain this character, use
mysqldump to dump them before upgrading
to MySQL 5.1, and then reload the dump file
after upgrading.
Known issue: When upgrading from MySQL 5.0 to versions of 5.1 prior to 5.1.23, running mysqlcheck (or mysql_upgrade, which runs mysqlcheck) to upgrade tables fails for names that must be written as quoted identifiers. To work around this problem, rename each affected table to a name that does not require quoting:
RENAME TABLE `tab``le_a` TO table_a; RENAME TABLE `table b` TO table_b;
After renaming the tables, run the mysql_upgrade program. Then rename the tables back to their original names:
RENAME TABLE table_a TO `tab``le_a`; RENAME TABLE table_b TO `table b`;
Known issue: In connection
with view creation, the server created
arc
directories inside database
directories and maintained useless copies of
.frm
files there. Creation and renaming
procedures of those copies as well as creation of
arc
directories has been discontinued
in MySQL 5.1.29.
This change does cause a problem when downgrading to older server versions which manifests itself under these circumstances:
Create a view v_orig
in MySQL 5.1.29
or higher.
Rename the view to v_new
and then
back to v_orig
.
Downgrade to an older 5.1.x server and run mysql_upgrade.
Try to rename v_orig
to
v_new
again. This operation fails.
As a workaround to avoid this problem, use either of these approaches:
Dump your data using mysqldump before downgrading and reload the dump file after downgrading.
Instead of renaming a view after the downgrade, drop it and recreate it.
Incompatible change: Character set or collation changes were made in MySQL 5.1.21, 5.1.23, and 5.1.24 that may require table indexes to be rebuilt. For details, see Section 2.4.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.
Incompatible change: MySQL
5.1 implements support for a plugin API that
allows the loading and unloading of components at runtime,
without restarting the server. Section 22.2, “The MySQL Plugin API”.
The plugin API requires the mysql.plugin
table. After upgrading from an older version of MySQL, you
should run the mysql_upgrade command to
create this table. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.
Plugins are installed in the directory named by the
plugin_dir
system variable.
This variable also controls the location from which the
server loads user-defined functions (UDFs), which is a
change from earlier versions of MySQL. That is, all UDF
library files now must be installed in the plugin directory.
When upgrading from an older version of MySQL, you must
migrate your UDF files to the plugin directory.
Incompatible change: The
table_cache
system variable
has been renamed to
table_open_cache
. Any
scripts that refer to
table_cache
must be updated
to use the new name.
Incompatible change: In
MySQL 5.1.36, options for loading plugins such as pluggable
storage engines were changed from boolean to tristate
format. The implementations overlap, but if you previously
used options of the form
--
or
plugin_name
=0--
,
you should instead use
plugin_name
=1--
or
plugin_name
=OFF--
,
respectively. For details, see
Section 5.1.3, “Server Options for Loading Plugins”.
plugin_name
=ON
Incompatible change: From
MySQL 5.1.24 to 5.1.31, the
UPDATE
statement was changed
such that assigning NULL
to a
NOT NULL
column caused an error even when
strict SQL mode was not enabled. The original behavior
before MySQL 5.1.24 was that such assignments caused an
error only in strict SQL mode, and otherwise set the column
to the implicit default value for the column data type and
generated a warning. (For information about implicit default
values, see Section 10.1.4, “Data Type Default Values”.)
The change caused compatibility problems for applications
that relied on the original behavior. It also caused
replication problems between servers that had the original
behavior and those that did not, for applications that
assigned NULL
to NOT
NULL
columns in
UPDATE
statements without
strict SQL mode enabled. The change was reverted in MySQL
5.1.32 so that UPDATE
again
had the original behavior. Problems can still occur if you
replicate between servers that have the modified
UPDATE
behavior and those
that do not.
Incompatible change: As of
MySQL 5.1.29, the default binary logging mode has been
changed from MIXED
to
STATEMENT
for compatibility with MySQL
5.0.
Incompatible change: In
MySQL 5.1.25, a change was made to the way that the server
handles prepared statements. This affects prepared
statements processed at the SQL level (using the
PREPARE
statement) and those
processed using the binary client-server protocol (using the
mysql_stmt_prepare()
C API
function).
Previously, changes to metadata of tables or views referred to in a prepared statement could cause a server crash when the statement was next executed, or perhaps an error at execute time with a crash occurring later. For example, this could happen after dropping a table and recreating it with a different definition.
Now metadata changes to tables or views referred to by
prepared statements are detected and cause automatic
repreparation of the statement when it is next executed.
Metadata changes occur for DDL statements such as those that
create, drop, alter, rename, or truncate tables, or that
analyze, optimize, or repair tables. Repreparation also
occurs after referenced tables or views are flushed from the
table definition cache, either implicitly to make room for
new entries in the cache, or explicitly due to
FLUSH
TABLES
.
Repreparation is automatic, but to the extent that it occurs, performance of prepared statements is diminished.
Table content changes (for example, with
INSERT
or
UPDATE
) do not cause
repreparation, nor do SELECT
statements.
An incompatibility with previous versions of MySQL is that a
prepared statement may now return a different set of columns
or different column types from one execution to the next.
For example, if the prepared statement is SELECT *
FROM t1
, altering t1
to contain
a different number of columns causes the next execution to
return a number of columns different from the previous
execution.
Older versions of the client library cannot handle this change in behavior. For applications that use prepared statements with the new server, an upgrade to the new client library is strongly recommended.
Along with this change to statement repreparation, the
default value of the
table_definition_cache
system variable has been increased from 128 to 256. The
purpose of this increase is to lessen the chance that
prepared statements will need repreparation due to
referred-to tables/views having been flushed from the cache
to make room for new entries.
A new status variable,
Com_stmt_reprepare
, has been introduced
to track the number of repreparations.
Incompatible change: As of
MySQL 5.1.23, within a stored routine, it is no longer
allowable to declare a cursor for a
SHOW
or
DESCRIBE
statement. This
happened to work in some instances, but is no longer
supported. In many cases, a workaround for this change is to
use the cursor with a SELECT
query to read from an INFORMATION_SCHEMA
table that produces the same information as the
SHOW
statement.
Incompatible change:
SHOW CREATE VIEW
displays
view definitions using an AS
clause for
each column. If a column is created from an expression, the
default alias is the expression text, which can be quite
long. As of MySQL 5.1.23, aliases for column names in
alias_name
CREATE VIEW
statements are
checked against the maximum column length of 64 characters
(not the maximum alias length of 256 characters). As a
result, views created from the output of
SHOW CREATE VIEW
fail if any
column alias exceeds 64 characters. This can cause problems
for replication or loading dump files. For additional
information and workarounds, see
Section D.4, “Restrictions on Views”.
Incompatible change: Several issues were identified for stored programs (stored procedures and functions, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format.
To address these problems, the representation for these
objects was changed in MySQL 5.1.21. However, the fixes
affect all stored programs and views.
(For example, you will see warnings about “no creation
context.”) To avoid warnings from the server about
the use of old definitions from any release prior to 5.1.21,
you should dump stored programs and views with
mysqldump after upgrading to 5.1.21 or
higher, and then reload them to recreate them with new
definitions. Invoke mysqldump with a
--default-character-set
option that names
the non-ASCII character set that was used for the
definitions when the objects were originally defined.
Incompatible change: As of
MySQL 5.1.20, mysqld_safe supports error
logging to syslog
on systems that support
the logger command. The new
--syslog
and
--skip-syslog
options can be used instead of the
--log-error
option to
control logging behavior, as described in
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
In 5.1.21 and up, the default is
--skip-syslog
,
which is compatible with the default behavior of writing an
error log file for releases prior to 5.1.20.
In 5.1.20 only,
the following conditions apply: 1) The default is
to use syslog
, which is not compatible
with releases prior to 5.1.20. 2) Logging to
syslog
may fail to operate correctly in
some cases. For these reasons, avoid using MySQL 5.1.20.
Incompatible change: As of
MySQL 5.1.18, the plugin interface and its handling of
system variables was changed. Command-line options such as
--skip-innodb
now cause an error if InnoDB
is not
built-in or plugin-loaded. You should use
--loose-skip-innodb
if you do not want any
error even if InnoDB
is not available.
The --loose
prefix modifier should be used
for all command-line options where you are uncertain whether
the plugin exists and when you want the operation to proceed
even if the option is necessarily ignored due to the absence
of the plugin. (For a desecription of how
--loose
works, see
Section 4.2.3.1, “Using Options on the Command Line”.)
Incompatible change: As of
MySQL 5.1.15, InnoDB
rolls back only the
last statement on a transaction timeout. A new option,
--innodb_rollback_on_timeout
,
causes InnoDB
to abort and roll back the
entire transaction if a transaction timeout occurs (the same
behavior as in MySQL 4.1).
Incompatible change: As of
MySQL 5.1.15, the following conditions apply to enabling the
read_only
system variable:
If you attempt to enable
read_only
while you
have any explicit locks (acquired with
LOCK TABLES
or have a
pending transaction, an error will occur.
If other clients hold explicit table locks or have
pending transactions, the attempt to enable
read_only
blocks until
the locks are released and the transactions end. While
the attempt to enable
read_only
is pending,
requests by other clients for table locks or to begin
transactions also block until
read_only
has been set.
read_only
can be
enabled while you hold a global read lock (acquired with
FLUSH TABLES WITH
READ LOCK
) because that does not involve table
locks.
Previously, the attempt to enable
read_only
would return
immediately even if explicit locks or transactions were
pending, so some data changes could occur for statements
executing in the server at the same time.
Incompatible change: The
number of function names affected by
IGNORE_SPACE
was reduced
significantly in MySQL 5.1.13, from about 200 to about 30.
(For details about
IGNORE_SPACE
, see
Section 8.2.4, “Function Name Parsing and Resolution”.) This change improves
the consistency of parser operation. However, it also
introduces the possibility of incompatibility for old SQL
code that relies on the following conditions:
IGNORE_SPACE
is
disabled.
The presence or absence of whitespace following a
function name is used to distinguish between a built-in
function and stored function that have the same name
(for example, PI()
versus
PI ()
).
For functions that are no longer affected by
IGNORE_SPACE
as of MySQL
5.1.13, that strategy no longer works. Either of the
following approaches can be used if you have code that is
subject to the preceding incompatibility:
If a stored function has a name that conflicts with a
built-in function, refer to the stored function with a
schema name qualifier, regardless of whether whitespace
is present. For example, write
or schema_name
.PI()
.
schema_name
.PI
()
Alternatively, rename the stored function to use a nonconflicting name and change invocations of the function to use the new name.
Incompatible change: For
utf8
columns, the full-text parser
incorrectly considered several nonword punctuation and
whitespace characters as word characters, causing some
searches to return incorrect results. The fix involves a
change to the full-text parser in MySQL 5.1.12, so as of
5.1.12, any tables that have FULLTEXT
indexes on utf8
columns must be repaired
with REPAIR TABLE
:
REPAIR TABLE tbl_name
QUICK;
Incompatible change:
Storage engines can be pluggable at runtime, so the
distinction between disabled and invalid storage engines no
longer applies. As of MySQL 5.1.12, this affects the
NO_ENGINE_SUBSTITUTION
SQL
mode, as described in Section 5.1.8, “Server SQL Modes”.
Incompatible change: The
structure of FULLTEXT
indexes has been
changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or
greater, any tables that have FULLTEXT
indexes must be repaired with REPAIR
TABLE
:
REPAIR TABLE tbl_name
QUICK;
Incompatible change: In
MySQL 5.1.6, when log tables were implemented, the default
log destination for the general query and slow query log was
TABLE
. As of MySQL 5.1.21, this default
has been changed to FILE
, which is
compatible with MySQL 5.0, but incompatible with earlier
releases of MySQL 5.1. If you are upgrading from MySQL 5.0
to 5.1.21 or higher, no logging option changes should be
necessary. However, if you are upgrading from 5.1.6 through
5.1.20 to 5.1.21 or higher and were using
TABLE
logging, use the
--log-output=TABLE
option explicitly to
preserve your server's table-logging behavior.
Incompatible change: For
ENUM
columns that had
enumeration values containing commas, the commas were mapped
to 0xff
internally. However, this
rendered the commas indistinguishable from true
0xff
characters in the values. This no
longer occurs. However, the fix requires that you dump and
reload any tables that have
ENUM
columns containing true
0xff
in their values: Dump the tables
using mysqldump with the current server
before upgrading from a version of MySQL 5.1 older than
5.1.15 to version 5.1.15 or newer.
As of MySQL 5.1.12, the
lc_time_names
system
variable specifies the locale that controls the language
used to display day and month names and abbreviations. This
variable affects the output from the
DATE_FORMAT()
,
DAYNAME()
and
MONTHNAME()
functions. See
Section 9.7, “MySQL Server Locale Support”.
As of MySQL 5.1.9, mysqld_safe no longer
implicitly invokes mysqld-max if it
exists. Instead, it invokes mysqld unless
a --mysqld
or
--mysqld-version
option
is given to specify another server explicitly. If you
previously relied on the implicit invocation of
mysqld-max, you should use an appropriate
option now. As of MySQL 5.1.12, there is no longer any
separate mysqld-max server, so no change
should be necessary.
SQL Changes:
Known issue: Prior to MySQL 5.1.17, the parser accepted invalid code in SQL condition handlers, leading to server crashes or unexpected execution behavior in stored programs. Specifically, the parser allowed a condition handler to refer to labels for blocks that enclose the handler declaration. This was incorrect because block label scope does not include the code for handlers declared within the labeled block.
As of 5.1.17, the parser rejects this invalid construct, but if you perform a binary upgrade (without dumping and reloading your databases), existing handlers that contain the construct still are invalid and should be rewritten even if they appear to function as you expect.
To find affected handlers, use mysqldump to dump all stored procedures and functions, triggers, and events. Then attempt to reload them into an upgraded server. Handlers that contain illegal label references will be rejected.
For more information about condition handlers and writing
them to avoid invalid jumps, see
Section 12.7.4.2, “DECLARE
for Handlers”.
Incompatible change: The
parser accepted statements that contained /* ...
*/
that were not properly closed with
*/
, such as SELECT 1 /* +
2
. As of MySQL 5.1.23, statements that contain
unclosed /*
-comments now are rejected
with a syntax error.
This fix has the potential to cause incompatibilities.
Because of Bug#26302, which caused the trailing
*/
to be truncated from comments in
views, stored routines, triggers, and events, it is possible
that objects of those types may have been stored with
definitions that now will be rejected as syntactically
invalid. Such objects should be dropped and re-created so
that their definitions do not contain truncated comments.
Incompatible change:
Multiple-table DELETE
statements containing ambiguous aliases could have
unintended side effects such as deleting rows from the wrong
table. Example:
DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;
As of MySQL 5.1.23, alias declarations can be declared only
in the table_references
part.
Elsewhere in the statement, alias references are allowed but
not alias declarations. Statements containing aliases that
are no longer allowed must be rewritten.
Incompatible change: As of
MySQL 5.1.8, TYPE =
is still
accepted as a synonym for the engine_name
ENGINE =
table
option but generates a warning. You should note that this
option is not available in MySQL 5.1.7, and
is removed altogether as of MySQL
5.4 and produces a syntax error.
engine_name
TYPE
has been deprecated since MySQL 4.0.
Incompatible change: The
namespace for triggers changed in MySQL 5.0.10. Previously,
trigger names had to be unique per table. Now they must be
unique within the schema (database). An implication of this
change is that DROP TRIGGER
syntax now uses a schema name instead of a table name
(schema name is optional and, if omitted, the current schema
will be used).
When upgrading from a version of MySQL 5 older than 5.0.10
to MySQL 5.0.10 or newer, you must drop all triggers and
re-create them or DROP
TRIGGER
will not work after the upgrade. Here is a
suggested procedure for doing this:
Upgrade to MySQL 5.0.10 or later to be able to access
trigger information in the
INFORMATION_SCHEMA.TRIGGERS
table. (This should work even for pre-5.0.10 triggers.)
Dump all trigger definitions using the following
SELECT
statement:
SELECT CONCAT('CREATE TRIGGER ', t.TRIGGER_SCHEMA, '.', t.TRIGGER_NAME, ' ', t.ACTION_TIMING, ' ', t.EVENT_MANIPULATION, ' ON ', t.EVENT_OBJECT_SCHEMA, '.', t.EVENT_OBJECT_TABLE, ' FOR EACH ROW ', t.ACTION_STATEMENT, '//' ) INTO OUTFILE '/tmp/triggers.sql' FROM INFORMATION_SCHEMA.TRIGGERS AS t;
The statement uses INTO OUTFILE
, so
you must have the FILE
privilege. The file will be created on the server host.
Use a different file name if you like. To be 100% safe,
inspect the trigger definitions in the
triggers.sql
file, and perhaps make
a backup of the file.
Stop the server and drop all triggers by removing all
.TRG
files in your database
directories. Change location to your data directory and
issue this command:
shell> rm */*.TRG
Start the server and re-create all triggers using the
triggers.sql
file:
mysql>delimiter // ;
mysql>source /tmp/triggers.sql //
Check that all triggers were successfully created using
the SHOW TRIGGERS
statement.
Incompatible change: MySQL
5.1.6 introduces the TRIGGER
privilege. Previously, the
SUPER
privilege was needed to
create or drop triggers. Now those operations require the
TRIGGER
privilege. This is a
security improvement because you no longer need to grant
users the SUPER
privilege to
enable them to create triggers. However, the requirement
that the account named in a trigger's
DEFINER
clause must have the
SUPER
privilege has changed
to a requirement for the
TRIGGER
privilege. When
upgrading from a previous version of MySQL 5.0 or 5.1 to
MySQL 5.1.6 or newer, be sure to update your grant tables by
running mysql_upgrade. This will assign
the TRIGGER
privilege to all
accounts that had the SUPER
privilege. If you fail to update the grant tables, triggers
may fail when activated. After updating the grant tables,
you can revoke the SUPER
privilege from those accounts that no longer otherwise
require it.
Some keywords may be reserved in MySQL 5.1 that were not reserved in MySQL 5.0. See Section 8.3, “Reserved Words”.
The BACKUP TABLE
, and
RESTORE TABLE
statements are
deprecated. mysqldump or
mysqlhotcopy can be used as alternatives.
The LOAD DATA FROM MASTER
and
LOAD TABLE FROM MASTER
statements are deprecated. See
Section 12.5.2.2, “LOAD DATA FROM MASTER
Syntax”, for recommended
alternatives.
The INSTALL PLUGIN
and
UNINSTALL PLUGIN
statements
that are used for the plugin API are new. So is the
WITH PARSER
clause for
FULLTEXT
index creation that associates a
parser plugin with a full-text index.
Section 22.2, “The MySQL Plugin API”.
C API Changes:
Incompatible change: As of
MySQL 5.1.7, the
mysql_stmt_attr_get()
C API
function returns a boolean rather than an unsigned int for
STMT_ATTR_UPDATE_MAX_LENGTH
. (Bug#16144)
User Comments
Add your own comment.