|
|
Upgrading from MySQL 4 to 4.1In general, you should do the following when upgrading from MySQL 4.0 to 4.1:
Several visible behaviors have changed between MySQL 4.0 and MySQL 4.1 to fix
some critical bugs and make MySQL more compatible with standard SQL. These
changes may affect your applications.
Some of the 4.1 behaviors can be tested in 4.0 before performing a full
upgrade to 4.1. We have added to later MySQL 4.0 releases (from 4.0.12 on) a
--new startup option for mysqld. See Section 5.1.2,
“Command Options”.
This option gives you the 4.1 behavior for the most critical changes. You can
also enable these behaviors for a given client connection with the SET @@new=1 command, or turn them off if they are on with
SET @@new=0.
If you believe that some of the 4.1 changes affect you, we recommend that
before upgrading to 4.1, you download the latest MySQL 4.0 version and run it
with the --new option by adding the following to your
config file: [mysqld-4.0]
new
That way you can test the new behaviors in 4.0 to make sure that your
applications work with them. This helps you have a smooth, painless transition
when you perform a full upgrade to 4.1 later. Putting the --new option in the [mysqld-4.0]
option group ensures that you do not accidentally later run the 4.1 version with
the --new option.
The following lists describe changes that may affect applications and that
you should watch out for when upgrading to version 4.1.
Server Changes:
The most notable change is that character set support has been improved. The
server supports multiple character sets, and all tables and non-binary string
columns (CHAR, VARCHAR,
and TEXT) have a character set. See Section 9.1,
“Character Set Support”. Binary string columns (BINARY, VARBINARY, and BLOB) contain strings of bytes and do not have a character
set.
Note
This change in character set support results in the potential for table
damage if you do not upgrade properly, so consider carefully the
incompatibilities noted here.
-
Incompatible change: There are
conditions under which you should rebuild tables. In general, to rebuild a
table, dump it with mysqldump and
reload the dump file. Some items in the following list indicate alternatives
means for rebuilding.
-
Incompatible change: MySQL
interprets length specifications in character column definitions in
characters. (Earlier versions interpret them in bytes.) For example,
CHAR(N) means N characters, not N bytes.
For single-byte character sets, this change makes no difference. However,
if you upgrade to MySQL 4.1 and configure the server to use a multi-byte
character set, the apparent length of character columns changes. Suppose that
a 4.0 table contains a CHAR(8) column used to store
ujis characters. Eight bytes can store from two to
four ujis characters. If you upgrade to 4.1 and
configure the server to use ujis as its default
character set, the server interprets character column lengths based on the
maximum size of a ujis character, which is three
bytes. The number of three-byte characters that fit in eight bytes is two.
Consequently, if you use SHOW CREATE TABLE to view
the table definition, MySQL displays CHAR(2). You
can retrieve existing data from the table, but you can only store new values
containing up to two characters. To correct this issue, use ALTER TABLE to change the column definition. For example:
ALTER TABLE tbl_name MODIFY col_name CHAR(8);
-
Incompatible change: As of MySQL
4.1.2, handling of the
FLOAT and DOUBLE floating-point data types is more strict to follow
standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Before 4.1.2,
the server allowed larger numbers to be stored. That is, it stored a value
such as 100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the maximum
allowable value of 99.9. If you have tables that were created before MySQL
4.1.2 and that contain floating-point data not strictly legal for the data
type, you should alter the data types of those columns. For example: ALTER TABLE tbl_name MODIFY col_name FLOAT(4,1);
-
Incompatible change: In connection
with the support for per-connection time zones in MySQL 4.1.3, the
timezone system variable was renamed to system_time_zone.
-
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 4.1 older than 4.1.23 to version 4.1.23 or
newer.
-
Incompatible change: The interface
to aggregate user-defined functions changed as of MySQL 4.1.1. You must
declare a
xxx_clear() function for each aggregate
function XXX(). xxx_clear() is used instead of xxx_reset(). See Section 19.2.4.2,
“UDF Calling Sequences for Aggregate Functions”.
-
Important note: MySQL 4.1 stores
table names and column names in
utf8. If you have
table names or column names that use characters outside of the standard 7-bit
US-ASCII range, you may have to do a mysqldump of your tables in MySQL 4.0 and
restore them after upgrading to MySQL 4.1. The symptom for this problem is
that you get a table not found error when trying to
access your tables. In this case, you should be able to downgrade back to
MySQL 4.0 and access your data.
-
Important note: If you upgrade to
MySQL 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0. That
is because, for earlier versions,
InnoDB is not
aware of multiple tablespaces.
-
All tables and non-binary string columns (
CHAR,
VARCHAR, and TEXT) have
a character set. See Section 9.1,
“Character Set Support”. Binary string columns (BINARY, VARBINARY, and BLOB) contain strings of bytes and do not have a
character set.
Character set information is displayed by SHOW CREATE
TABLE and mysqldump. (MySQL
versions 4.0.6 and above can read the new dump files; older versions cannot.)
This change should not affect applications that use only one character set.
-
If you were using columns with the
CHAR BINARY
or VARCHAR BINARY data types in MySQL 4.0, these
were treated as binary strings. To have them treated as binary strings in
MySQL 4.1, you should convert them to the BINARY
and VARBINARY data types, respectively.
-
If you have table columns that store character data represented in a
character set that the 4.1 server supports directly, you can convert the
columns to the proper character set using the instructions in Section 9.1.9.2,
“Converting 4.0 Character Columns to 4.1 Format”. Also, database, table,
and column identifiers are stored internally using Unicode (UTF-8) regardless
of the default character set. See Section 8.2,
“Database, Table, Index, Column, and Alias Names”.
-
The table definition format used in
.frm files
has changed slightly in 4.1. MySQL 4.0 versions from 4.0.11 on can read the
new .frm format directly, but older versions
cannot. If you need to move tables from 4.1 to a version earlier than 4.0.11,
you should use mysqldump. See Section 4.5.4,
“mysqldump — A Database Backup
Program”.
-
Windows servers support connections from local clients using shared memory
if run with the
--shared-memory option. If you are
running multiple servers this way on the same Windows machine, you should use
a different --shared-memory-base-name option for
each server.
-
As of MySQL 4.1.21, 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 4.1.10a, the server by default no longer loads user-defined
functions (UDFs) unless they have at least one auxiliary symbol defined in
addition to the main function symbol. This behavior can be overridden with the
--allow-suspicious-udfs option. See Section 19.2.4.6,
“User-Defined Function Security Precautions”.
Client Changes:
-
mysqldump has the
--opt and --quote-names options
enabled by default. You can turn these off using --skip-opt and --skip-quote-names.
SQL Changes:
-
Incompatible change: In MySQL 4.1,
string comparison works according to the SQL standard: Instead of stripping
end spaces before comparison, the shorter string is extended using spaces.
This means that
'a' > 'a\t', which it was not
previously. If you have any tables containing an indexed CHAR, VARCHAR or TEXT column in which the last character in the index may
be less than ASCII(32), you should use REPAIR TABLE or mysqlcheck to ensure that the table is correct.
-
Incompatible change:
TIMESTAMP is returned in MySQL 4.1 as a string in 'YYYY-MM-DD HH:MM:SS' format. (See Section 10.3.1.2,
“TIMESTAMP Properties as of MySQL 4.1”.) From
4.0.12 on, the --new option can be used to make a
4.0 server behave as 4.1 in this respect. The effect of this option is
described in Section 10.3.1.1,
“TIMESTAMP Properties Prior to MySQL 4.1”.
When running the server with --new, if you want
to have a TIMESTAMP column returned as a number (as
MySQL 4.0 does by default), you should add +0 when
you retrieve it: mysql> SELECT ts_col + 0 FROM tbl_name;
Display widths for TIMESTAMP columns are no
longer supported in MySQL 4.1. For example, if you declare a column as TIMESTAMP(10), the (10) is
ignored.
-
Incompatible change: Binary values
such as
0xFFDF are assumed to be strings instead of
numbers. This fixes some problems with character sets where it is convenient
to input a string as a binary value. With this change, you should use CAST() if you want to compare binary values
numerically as integers: mysql> SELECT CAST(0xFEFF AS UNSIGNED INTEGER)
-> < CAST(0xFF AS UNSIGNED INTEGER);
-> 0
If you do not use CAST(), a lexical string comparison is made instead:
mysql> SELECT 0xFEFF < 0xFF;
-> 1
Using binary items in a numeric context or comparing them using the = operator should work as before. (The --new option can be used from 4.0.13 on to make a 4.0
server behave as 4.1 in this respect.)
-
Incompatible change: Before MySQL
4.1.13, conversion of
DATETIME values to numeric
form by adding zero produced a result in YYYYMMDDHHMMSS format. The result of DATETIME+0 is now in YYYYMMDDHHMMSS.000000 format.
-
Incompatible change: In MySQL
4.1.12, the behavior of
LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS
ENCLOSED BY values both are empty. Formerly, a column was read or
written the display width of the column. For example, INT(4) was read or written using a field with a width of
4. Now columns are read and written using a field width wide enough to hold
all values in the field. However, data files written before this change was
made might not be reloaded correctly with LOAD DATA
INFILE for MySQL 4.1.12 and up. This change also affects data files
read by mysqlimport and written by
mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO
OUTFILE. For more information, see Section 12.2.5,
“LOAD DATA INFILE Syntax”.
-
Incompatible change: Before MySQL
4.1.1, the statement parser was less strict and its string-to-date conversion
would ignore everything up to the first digit. As a result, invalid statements
such as the following were accepted:
INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');
As of MySQL 4.1.1, the parser is stricter and treats the string as an
invalid date, so the preceding statement results in a warning.
-
Incompatible change: In MySQL
4.1.2, the
Type column in the output from SHOW TABLE STATUS was renamed to Engine. This affects applications that identify output
columns by name rather than by position.
-
Incompatible change: The syntax
for multiple-table
DELETE statements that use table
aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the
true table name to refer to any table from which rows should be deleted: DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1, you must use the alias: DELETE t1 FROM test AS t1, test2 WHERE ...
We did not make this change in 4.0 to avoid breaking any old 4.0
applications that were using the old syntax. However, if you use such DELETE statements and are using replication, the change
in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.
-
Some keywords are reserved in MySQL 4.1 that were not reserved in MySQL
4.0. See Section 8.3,
“Reserved Words”.
-
The
LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 12.6.2.2,
“LOAD DATA FROM MASTER Syntax”, for recommended
alternatives.
-
When using multiple-table
DELETE statements, you
should use the alias of the tables from which you want to delete, not the
actual table name. For example, instead of doing this: DELETE test FROM test AS t1, test2 WHERE ...
Do this: DELETE t1 FROM test AS t1, test2 WHERE ...
This corrects a problem that was present in MySQL 4.0.
-
For functions that produce a
DATE, DATETIME, or TIME value, the
result returned to the client is fixed up to have a temporal type. For
example, in MySQL 4.1, you obtain the following: mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01 00:00:00'
In MySQL 4.0, the result of the stement is different: mysql> SELECT CAST('2001-1-1' AS DATETIME);
-> '2001-01-01'
-
DEFAULT values no longer can be specified for
AUTO_INCREMENT columns. (In 4.0, a DEFAULT value is silently ignored; in 4.1, an error
occurs.)
-
LIMIT no longer accepts negative arguments. Use
some large number (maximum 18446744073709551615) instead of -1.
-
SERIALIZE is no longer a valid mode value for
the sql_mode variable. You should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead.
SERIALIZE is no longer valid for the --sql-mode option for mysqld, either. Use --transaction-isolation=SERIALIZABLE instead.
-
A new startup option named
innodb_table_locks was
added that causes LOCK TABLE to also acquire InnoDB table locks. This option is enabled by default.
This can cause deadlocks in applications that use AUTOCOMMIT=1 and LOCK TABLES.
If you application encounters deadlocks after upgrading, you may need to add
innodb_table_locks=0 to your my.cnf file.
C API Changes:
-
Incompatible change: The
mysql_shutdown() C API function has an extra
parameter as of MySQL 4.1.3: SHUTDOWN-level. You
should convert any mysql_shutdown(X) call you have in your
application to mysql_shutdown(X,SHUTDOWN_DEFAULT). Any
third-party API that links against the C API library must be modified to
account for this change or it will not compile.
-
Some C API calls such as
mysql_real_query() return 1 on error, not -1. You may
have to change some old applications if they use constructs like this: if (mysql_real_query(mysql_object, query, query_length) == -1)
{
printf("Got error");
}
Change the call to test for a non-zero value instead: if (mysql_real_query(mysql_object, query, query_length) != 0)
{
printf("Got error");
}
Password-Handling Changes:
The password hashing mechanism changed in 4.1 to provide better security;
this may cause compatibility problems if you have clients using the client
library from 4.0 or earlier. (It is very likely that you have 4.0 clients in
situations where clients connect from remote hosts that have not yet upgraded to
4.1.) The following list indicates some possible upgrade strategies. They
represent various tradeoffs between the goals of compatibility with old clients
and security.
-
Only upgrade the client to use 4.1 client libraries (not the server). No
behavior changes (except the return value of some API calls), but you cannot
use any of the new features provided by the 4.1 client/server protocol,
either. (MySQL 4.1 has an extended client/server protocol that offers such
features as prepared statements and multiple result sets.) See Section 16.2.4,
“C API Prepared Statements”.
-
Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the
Password column in the user table so that it can hold long password hashes.
However — to provide backward compatibility allowing pre-4.1 clients to
continue connecting to their short-hash accounts — run the server with the
--old-passwords option. Eventually, when all your
clients are upgraded to 4.1, you can stop using the --old-passwords server option. You can also change the
passwords for your MySQL accounts to use the new more secure format. A 4.1
installation using only the improved authentication protocol is the most
secure one.
Further background on password hashing with respect to client authentication
and password-changing operations may be found in Section 5.5.9,
“Password Hashing as of MySQL 4.1”, and Section A.1.2.4,
“Client does not support authentication
protocol”.
|
