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