D.2 Changes in release 4.1.x (Alpha)
Version 4.1 of the MySQL server includes many enhancements and new features.
Binaries for this version are available for download at
http://www.mysql.com/downloads/mysql-4.1.html.
-
Subqueries:
| | SELECT * FROM t1 WHERE t1.a=(SELECT t2.b FROM t2);
SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2);
|
-
Derived tables:
| | SELECT t1.a FROM t1, (SELECT * FROM t2) t3 WHERE t1.a=t3.a;
|
-
INSERT ... ON DUPLICATE KEY UPDATE ... syntax. This allows you to
UPDATE an existing row if the insert would cause a duplicate value
in a PRIMARY or UNIQUE key. (REPLACE allows you to
overwrite an existing row, which is something entirely different.)
See section INSERT Syntax.
-
A newly designed
GROUP_CONCAT() aggregate function.
See section Functions and Modifiers for Use with GROUP BY Clauses.
-
Extensive Unicode (UTF8) support.
-
Character sets can be defined per column, table and database.
-
BTREE index on HEAP tables.
-
Support for OpenGIS spatial types (geographical data).
See section Spatial Extensions in MySQL.
-
SHOW WARNINGS shows warnings for the last command.
See section SHOW WARNINGS | ERRORS.
-
Faster binary protocol with prepared statements and parameter binding.
See section C API Prepared Statements.
-
Multi-line queries: You can now issue several queries at once and
then read the results in one go.
See section C API Handling of Multiple Query Execution.
-
Create Table:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table2 LIKE
table1.
-
Server based
HELP command that can be used in the mysql
command line client (and other clients) to get help for SQL commands.
For a full list of changes, please refer to the changelog sections for
each individual 4.1.x release.
D.2.1 Changes in release 4.1.1 (not released yet)
Functionality added or changed:
-
Warning: Incompatible change!
Client authentication now is based on 41-byte passwords in the
user table, not 45-byte passwords as in 4.1.0.
Any 45-byte passwords created for 4.1.0 must be reset after running the
mysql_fix_privilege_tables script.
-
The interface to aggregated UDF functions has changed a bit. One must now
declare a
clear function for each aggregate function.
-
CONCAT_WS() no longer skips empty strings.
-
Added new functions
UTC_DATE(), UTC_TIME(), and
UTC_TIMESTAMP().
-
Added new functions
DATE(), TIME(), TIMESTAMP(),
WEEKOFYEAR(), MICROSECOND(), ADDTIME(), SUBTIME(),
DATEDIFF(), TIMEDIFF(), MAKEDATE(), MAKETIME(),
and TIMEDIFF().
-
Added new syntax for
ADDDATE() and SUBDATE().
They now allow a numeric second argument representing the number of days to
be added to or subtracted from the first date argument.
-
Added new
type values DAY_MICROSECOND,
HOUR_MICROSECOND, MINUTE_MICROSECOND,
SECOND_MICROSECOND, and MICROSECOND
for DATE_ADD(), DATE_SUB(), and EXTRACT().
-
Added new
%f microseconds format specifier for DATE_FORMAT() and
TIME_FORMAT().
-
Enabled that all queries in which at least one
SELECT does not
use indices properly get into slow query log when long log format is
used.
-
It is now possible to create
MERGE tables from MyISAM tables in
different databases. Formerly, all the MyISAM tables had to be in the
same database, and the MERGE table had to be created in that database
as well.
-
Added new functions
COMPRESS(), UNCOMPRESS(), and
UNCOMPRESSED_LENGTH().
-
When doing
SET sql_mode='mode' for a complex mode (like ANSI), we
now update the sql_mode variable to include all the individual options
implied by the complex mode.
-
Added OLAP (Online Analytical Processing) function
ROLLUP, which gives
you summary rows for each GROUP BY level.
-
Added
SQLSTATE codes for all server errors.
-
Added client API functions
mysql_sqlstate() and
mysql_stmt_sqlstate() that return the SQLSTATE error code for the
last error.
-
TIME columns with hours > 24 (days) were returned incorrectly to the client.
-
ANALYZE, OPTIMIZE, REPAIR, FLUSH (and its
equivalents invoked from mysqladmin) commands
are now stored in the binary log (hence are replicated to the slave),
except FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE,
FLUSH TABLES WITH READ LOCK, and unless the optional
NO_WRITE_TO_BINLOG keyword
(or its alias LOCAL) was used (for a syntax example,
see section FLUSH).
-
New global variable
RELAY_LOG_PURGE to enable/disable automatic
relay log purging.
-
LOAD DATA now produces warnings that can be fetched with
SHOW WARNINGS.
-
Added support for syntax
CREATE TABLE table2 (LIKE table1)
that creates an empty table table2 with a definition that is
exactly the same as table1, including any indexes.
-
CREATE TABLE table_name (...) TYPE=storage_engine now generates a
warning if the named storage engine is not available. The table is still
created as a MyISAM table, as before.
-
Most subqueries are now much faster than before.
-
Added alias
PURGE BINARY LOGS for PURGE MASTER LOGS.
-
PURGE LOGS syntax is disabled (it had been added in version
4.1.0).
It is now PURGE MASTER LOGS or PURGE BINARY LOGS.
-
Added alias
SHOW BDB LOGS for SHOW LOGS.
-
Added alias
SHOW MASTER LOGS (which had been deleted in version
4.1.0) to SHOW BINARY LOGS.
Bugs fixed:
-
HASH, BTREE, RTREE, ERRORS and
WARNINGS are not reserved words anymore. (Bug #724)
-
Fix for bug in
ROLLUP when all tables were const tables.
(Bug #714)
-
--lower-case-table-names=1 now also makes aliases case
insensitive. (Bug #534)
-
Fixed a bug in
UNION which prohibited that NULL values are
inserted in result set where first SELECT contains NOT
NULL columns
-
Fixed name resolution of fields of reduced subqueries in unions. (Bug #745)
-
Fixed memory overrun in subqueries in select list with
WHERE clause
bigger than outer query WHERE clause. (Bug #726)
-
Fixed a bug that caused
MyISAM tables with FULLTEXT indexes
created in 4.0.x to be unreadable in 4.1.x.
-
Fixed a data loss bug in
REPAIR TABLE ... USE_FRM when used
with tables
that contained TIMESTAMP fields and were created in 4.0.x.
-
Fixed reduced subquery processing in
ORDER BY/GROUP BY
clauses. (Bug #442)
-
Fixed name resolving of outer fields of subquery in
INSERT/REPLACE statements. (Bug #446)
-
Fixed bug in marking fields of reduced subqueries. (Bug #679)
-
Fixed a bug that made
CREATE FULLTEXT INDEX syntax illegal.
-
Fixed a crash when a
SELECT that required a temporary table
(marked by Using temporary in EXPLAIN output)
was used as a derived table in EXPLAIN command. (Bug #251)
-
Fixed a rare table corruption bug in
DELETE
from a big table with
a new (created by MySQL-4.1) fulltext index.
-
LAST_INSERT_ID() now returns 0 if the last INSERT statement didn't
insert any rows.
-
Fixed missing last character in function output. (Bug #447)
-
Fixed a rare replication bug when a transaction spanned two or more
relay logs, and the slave was stopped while executing the part
of the transaction that was in the second or later relay log. Then
replication would resume at the beginning of the second or later
relay log, which was wrong. (It should resume at
BEGIN, in the
first relay log.) (Bug #53)
-
CONNECTION_ID() is now properly replicated. (Bug #177)
-
The new
PASSWORD() function in 4.1 is now properly replicated.
(Bug #344)
-
Fixed bug with doubly freed memory.
-
Fixed crashing bug in
UNION operations that involved temporary tables.
-
Fixed a crashing bug in
DERIVED TABLES when EXPLAIN is
used on a DERIVED TABLES with a join.
-
Fixed a crashing bug in
DELETE with ORDER BY and
LIMIT caused by an uninitialized array of reference pointers.
-
Fixed a bug in
USER() function caused by an error in the size of
the allocated string.
-
Fixed a crashing bug when attempting to create a table with a spatial (GIS)
column type with a storage engine that does not support spatial types.
-
Fixed a crashing bug in
UNION caused by the empty select list and
a non-existent field being used in some of the sub-selects.
-
Fixed a replication bug when the master is 3.23 and the slave 4.0: The
slave lost the replicated temporary tables if
FLUSH LOGS was
issued on the master. (Bug #254)
-
Fixed a security bug: A server compiled without SSL support still allowed
connections by users that had the
REQUIRE SSL option specified for
their accounts.
D.2.2 Changes in release 4.1.0 (03 Apr 2003: Alpha)
Functionality added or changed:
-
New more secure client authentication based on 45-byte passwords in the
user table.
-
New
CRC32() function to compute cyclic redundancy check value.
-
On Windows, we are now using shared memory to communicate between server
and client when they are running on the same machine and you are
connecting to
localhost.
-
REPAIR of MyISAM tables now uses less temporary disk space when
sorting char columns.
-
DATE/DATETIME checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and time
with microseconds. For example, dates of type YYYYMMDD HHMMDD are no
longer supported; you must either have separators between each
DATE/TIME part or not at all.
-
Server side help for all MySQL functions. One can now type
help
week in the mysql client and get help for the week()
function.
-
Added new C API client function:
mysql_get_server_version().
-
Fixed bug in
libmysqlclient that fetched field defaults.
-
Fixed bug in `mysql.cc' client when skipping comments
-
Added
record_in_range() method to MERGE tables to be
able to choose the right index when there are many to choose from.
-
Replication now works with
RAND() and user variables @var.
-
Allow one to change mode for
ANSI_QUOTES on the fly.
-
EXPLAIN SELECT now can be killed. See section KILL Syntax.
-
REPAIR TABLE now can be killed. See section KILL Syntax.
-
Allow one to specify empty key lists for
USE|IGNORE|FORCE INDEX.
-
DROP TEMPORARY TABLE now only drops temporary tables and doesn't
end transactions.
-
Added a support for
UNION in derived tables.
-
TIMESTAMP is now returned as a string of type
'YYYY-MM-DD HH:MM:SS' and different timestamp lengths are not supported.
This change was necessary for SQL standards compliance. In a future
version, a further change will be made (backward compatible with this
change), allowing the timestamp length to indicate the desired number
of digits of fractions of a second.
-
New faster client/server protocol which supports prepared statements,
bound parameters, and bound result columns, binary transfer of data,
warnings.
-
Added database and real table name (in case of alias) to the
MYSQL_FIELD structure.
-
Multi-line queries: You can now issue several queries at once and
then read the results in one go.
-
In
CREATE TABLE foo (a INT not null primary key) the
PRIMARY word is now optional.
-
In
CREATE TABLE the attribute SERIAL is now an alias for
BIGINT NOT NULL AUTO_INCREMENT UNIQUE.
-
SELECT ... FROM DUAL is an alias for SELECT ....
(To be compatible with some other databases).
-
If one creates a too long
CHAR/VARCHAR it's now
automatically changed to TEXT or BLOB; One will get a
warning in this case.
-
One can specify the different
BLOB/TEXT types with the
syntax BLOB(length) and TEXT(length). MySQL will
automatically change it to one of the internal BLOB/TEXT
types.
-
CHAR BYTE is an alias for CHAR BINARY.
-
VARCHARACTER is an alias for VARCHAR.
-
New operators
integer MOD integer and integer DIV integer.
-
SERIAL DEFAULT VALUE added as an alias for AUTO_INCREMENT.
-
TRUE and FALSE added as alias for 1 and 0, respectively.
-
Aliases are now forced in derived tables, as per SQL-99.
-
Fixed
SELECT .. LIMIT 0 to return proper row count for
SQL_CALC_FOUND_ROWS.
-
One can specify many temporary directories to be used in a round-robin
fashion with:
--tmpdir=dirname1:dirname2:dirname3.
-
Subqueries:
SELECT * from t1 where t1.a=(SELECT t2.b FROM t2).
-
Derived tables:
| | SELECT a.col1, b.col2
FROM (SELECT MAX(col1) AS col1 FROM root_table) a,
other_table b
WHERE a.col1=b.col1;
|
-
Character sets to be defined per column, table and database.
-
Unicode (UTF8) support.
-
New
CONVERT(... USING ...) syntax for converting string values between
character sets.
-
BTREE index on HEAP tables.
-
Faster embedded server (new internal communication protocol).
-
One can add a comment per column in
CREATE TABLE.
-
SHOW FULL COLUMNS FROM table_name shows column comments.
-
ALTER DATABASE.
-
Support for GIS (Geometrical data).
See section Spatial Extensions in MySQL.
-
SHOW [COUNT(*)] WARNINGS shows warnings from the last command.
-
One can specify a column type for a colum in
CREATE TABLE
... SELECT by defining the column in the CREATE part.
| | CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;
|
-
expr SOUNDS LIKE expr same as SOUNDEX(expr)=SOUNDEX(expr).
-
VARIANCE(expr) returns the variance of expr
-
One can create a table from the existing table using
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table). The table can
be either normal or temporary.
-
New options
--reconnect and disable-reconnect for the
mysql client, to reconnect automatically or not if the
connection is lost.
-
START SLAVE (STOP SLAVE) no longer returns an error
if the slave is already started (stopped); it returns a
warning instead.
-
SLAVE START and SLAVE STOP are no longer accepted by the query
parser; use START SLAVE and STOP SLAVE instead.
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44