Upgrading MySQL
152
• Incompatible change: Before MySQL 5.0.8, conversion of
DATETIME
values to numeric form by
adding zero produced a result in
YYYYMMDDHHMMSS
format. The result of
D0
is now in
YYYYMMDDHHMMSS.000000
format.
• Incompatible change: In MySQL 5.0.6, 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 using 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
5.0.6 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 13.2.6, “
LOAD DATA INFILE
Syntax”
.
• Incompatible change: Before MySQL 5.0.2,
SHOW STATUS
returned global status values. The
default as of 5.0.2 is to return session values, which is incompatible with previous versions. To issue
a
SHOW STATUS
statement that will retrieve global status values for all versions of MySQL, write it
like this:
SHOW /*!50002 GLOBAL */ STATUS;
• Incompatible change: User variables are not case sensitive in MySQL 5.0. In MySQL 4.1,
SET @x
= 0; SET @X = 1; SELECT @x;
created two variables and returned
0
. In MySQL 5.0, it creates
one variable and returns
1
. Replication setups that rely on the old behavior may be affected by this
change.
• Some keywords may be reserved in MySQL 5.0 that were not reserved in MySQL 4.1. See
Section 9.3, “Reserved Words”
.
• The
LOAD DATA FROM MASTER
and
LOAD TABLE FROM MASTER
statements are deprecated. See
Section 13.4.2.2, “
LOAD DATA FROM MASTER
Syntax”
, for recommended alternatives.
• As of MySQL 5.0.25,
TIMESTAMP
columns that are
NOT NULL
now are reported that way by
SHOW
COLUMNS
and
INFORMATION_SCHEMA
, rather than as
NULL
.
• Comparisons made between
FLOAT
or
DOUBLE
values that happened to work in MySQL 4.1 may
not do so in 5.0. Values of these types are imprecise in all MySQL versions, and you are strongly
advised to avoid such comparisons as
WHERE col_name=some_double
, regardless of the MySQL
version you are using. See
Section C.5.5.8, “Problems with Floating-Point Values”
.
• As of MySQL 5.0.3,
BIT
is a separate data type, not a synonym for
TINYINT(1)
. See
Section 11.1.1, “Numeric Type Overview”
.
• MySQL 5.0.2 adds several SQL modes that enable stricter control over rejecting records that
have invalid or missing values. See
Section 5.1.7, “Server SQL Modes”
, and
Section 1.8.6.3,
“Constraints on Invalid Data”
. If you want to enable this control but continue to use MySQL's
capability for storing incorrect dates such as
'2004-02-31'
, you should start the server with
--
sql_mode="TRADITIONAL,ALLOW_INVALID_DATES"
[495]
.
• As of MySQL 5.0.2, the
SCHEMA
and
SCHEMAS
keywords are accepted as synonyms for
DATABASE
and
DATABASES
, respectively. (While “schemata” is grammatically correct and even appears in some
MySQL 5.0 system database and table names, it cannot be used as a keyword.)
C API Changes
• Incompatible change: Because the MySQL 5.0 server has a new implementation of the
DECIMAL
data type, a problem may occur if the server is used by older clients that still are linked against
MySQL 4.1 client libraries. If a client uses the binary client/server protocol to execute prepared
statements that generate result sets containing numeric values, an error will be raised:
'Using
unsupported buffer type: 246'
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...