DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

Appendix D. MySQL Change History

Table of Contents

D.1. Changes in release 5.0.x (Production)
D.1.1. Changes in release 5.0.20 (Not yet released)
D.1.2. Changes in release 5.0.19 (04 March 2006)
D.1.3. Changes in release 5.0.18 (21 December 2005)
D.1.4. Changes in release 5.0.17 (14 December 2005)
D.1.5. Changes in release 5.0.16 (10 November 2005)
D.1.6. Changes in release 5.0.15 (19 October 2005: Production)
D.1.7. Changes in release 5.0.14 (Not released)
D.1.8. Changes in release 5.0.13 (22 Sept 2005: Release Candidate)
D.1.9. Changes in release 5.0.12 (02 Sept 2005)
D.1.10. Changes in release 5.0.11 (06 Aug 2005)
D.1.11. Changes in release 5.0.10 (27 July 2005)
D.1.12. Changes in release 5.0.9 (15 July 2005)
D.1.13. Changes in release 5.0.8 (Not released)
D.1.14. Changes in release 5.0.7 (10 June 2005)
D.1.15. Changes in release 5.0.6 (26 May 2005)
D.1.16. Changes in release 5.0.5 (Not released)
D.1.17. Changes in release 5.0.4 (16 Apr 2005)
D.1.18. Changes in release 5.0.3 (23 Mar 2005: Beta)
D.1.19. Changes in release 5.0.2 (01 Dec 2004)
D.1.20. Changes in release 5.0.1 (27 Jul 2004)
D.1.21. Changes in release 5.0.0 (22 Dec 2003: Alpha)
D.2. Changes in MySQL Cluster
D.2.1. Changes in MySQL Cluster-5.0.7 (Not yet released)
D.2.2. Changes in MySQL Cluster-5.0.6 (26 May 2005)
D.2.3. Changes in MySQL Cluster-5.0.5 (Not released)
D.2.4. Changes in MySQL Cluster-5.0.4 (16 Apr 2005)
D.2.5. Changes in MySQL Cluster-5.0.3 (23 Mar 2005: Beta)
D.2.6. Changes in MySQL Cluster-5.0.1 (27 Jul 2004)
D.2.7. Changes in MySQL Cluster-4.1.13 (15 Jul 2005)
D.2.8. Changes in MySQL Cluster-4.1.12 (13 May 2005)
D.2.9. Changes in MySQL Cluster-4.1.11 (01 Apr 2005)
D.2.10. Changes in MySQL Cluster-4.1.10 (12 Feb 2005)
D.2.11. Changes in MySQL Cluster-4.1.9 (13 Jan 2005)
D.2.12. Changes in MySQL Cluster-4.1.8 (14 Dec 2004)
D.2.13. Changes in MySQL Cluster-4.1.7 (23 Oct 2004)
D.2.14. Changes in MySQL Cluster-4.1.6 (10 Oct 2004)
D.2.15. Changes in MySQL Cluster-4.1.5 (16 Sep 2004)
D.2.16. Changes in MySQL Cluster-4.1.4 (31 Aug 2004)
D.2.17. Changes in MySQL Cluster-4.1.3 (28 Jun 2004)
D.3. Changes in MyODBC
D.3.1. Changes in MyODBC 3.51.13
D.3.2. Changes in MyODBC 3.51.12
D.3.3. Changes in MyODBC 3.51.11

This appendix lists the changes from version to version in the MySQL source code through the latest version of MySQL 5.0, which is currently MySQL 5.0.19. Starting with MySQL 5.0, we began offering a new version of the Manual for each new series of MySQL releases (5.0, 5.1, and so on). For information about changes in previous release series of the MySQL database software, see the corresponding version of this Manual. For information about legacy versions of the MySQL software through the 4.1 series, see MySQL 3.23, 4.0, 4.1 Reference Manual.

We update this section as we add new features in the 5.0 series, so that everybody can follow the development process.

Note that we tend to update the manual at the same time we make changes to MySQL. If you find a recent version of MySQL listed here that you can't find on our download page (http://dev.mysql.com/downloads/), it means that the version has not yet been released.

The date mentioned with a release version is the date of the last BitKeeper ChangeSet on which the release was based, not the date when the packages were made available. The binaries are usually made available a few days after the date of the tagged ChangeSet, because building and testing all packages takes some time.

The manual included in the source and binary distributions may not be fully accurate when it comes to the release changelog entries, because the integration of the manual happens at build time. For the most up-to-date release changelog, please refer to the online version instead.

D.1. Changes in release 5.0.x (Production)

The following changelog shows what has been done in the 5.0 tree:

  • Basic support for read-only server side cursors. For information about using cursors within stored routines, see Section 17.2.9, “Cursors”. For information about using cursors from within the C API, see Section 22.2.7.3, “mysql_stmt_attr_set().

  • Basic support for (updatable) views. See, for example, Section 19.2, “CREATE VIEW Syntax”.

  • Basic support for stored procedures and functions (SQL:2003 style). See Chapter 17, Stored Procedures and Functions.

  • Initial support for rudimentary triggers.

  • Added SELECT INTO list_of_vars, which can be of mixed (that is, global and local) types. See Section 17.2.7.3, “SELECT ... INTO Statement”.

  • Removed the update log. It is fully replaced by the binary log. If the MySQL server is started with --log-update, it is translated to --log-bin (or ignored if the server is explicitly started with --log-bin), and a warning message is written to the error log. Setting SQL_LOG_UPDATE silently sets SQL_LOG_BIN instead (or do nothing if the server is explicitly started with --log-bin).

  • Support for the ISAM storage engine has been removed. If you have ISAM tables, you should convert them before upgrading. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • Support for RAID options in MyISAM tables has been removed. If you have tables that use these options, you should convert them before upgrading. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • User variable names are now case insensitive: If you do SET @a=10; then SELECT @A; now returns 10. Case sensitivity of a variable's value depends on the collation of the value.

  • Strict mode, which in essence means that you get an error instead of a warning when inserting an incorrect value into a column. See Section 5.2.5, “The Server SQL Mode”.

  • VARCHAR and VARBINARY columns remember end space. A VARCHAR() or VARBINARY column can contain up to 65,535 characters or bytes, respectively.

  • MEMORY (HEAP) tables can have VARCHAR() columns.

  • When using a constant string or a function that generates a string result in CREATE ... SELECT, MySQL creates the result field based on the maximum length of the string or expression:

    Maximum LengthData type
    = 0CHAR(0)
    < 512VARCHAR(max_length)
    >= 512TEXT

For a full list of changes, please refer to the changelog sections for each individual 5.0.x release.

D.1.1. Changes in release 5.0.20 (Not yet released)

Functionality added or changed:

  • Windows builds now have SSL support enabled. (Bug#18195)

  • The syntax for CREATE PROCEDURE and CREATE FUNCTION statements now includes a DEFINER clause. The DEFINER value specifies the security context to be used when checking access privileges at routine invocation time if the routine has the SQL SECURITY DEFINER characteristic. See Section 17.2.1, “CREATE PROCEDURE and CREATE FUNCTION Syntax”, for more information.

    When mysqldump is invoked with the --routines option, it now dumps the DEFINER value for stored routines.

  • Security Improvement: Checks for permissions on database operations were performed in a case-insensitive manner, meaning that a user with permissions on database MYDATABASE also had permissions on database myDataBase. (Bug #17279)

Bugs fixed:

  • If a row was inserted inside a stored procedure using the parameters passed to the procedure in the INSERT statement, the resulting binlog entry was not escaped properly. (Bug #18293)

  • If InnoDB encountered a HA_ERR_LOCK_TABLE_FULL error and rolled-back a transaction, the transaction was still written to the binary log. (Bug #18283)

  • Stored procedures that call UDFs and pass local string variables caused server crashes. (Bug #17261)

  • Connecting to a server with a UCS2 default character set with a client using a non-UCS2 character set crashed the server. (Bug #18004)

  • Loading of UDFs in a statically linked MySQL caused a server crash. UDF loading is now blocked if the MySQL server is statically linked. (Bug #11835)

  • Views that incorporate tables from the INFORMATION_SCHEMA resulted in a server crash when queried. (Bug #18224)

  • A SELECT * query on an INFORMATION_SCHEMA table by a user with limited privileges resulted in a server crash. (Bug #18113)

  • Attempting to access an InnoDB table after starting the server with --skip-innodb caused a server crash. (Bug #14575)

  • InnoDB used table locks (not row locks) within stored functions. (Bug #18077)

  • Replication slaves could not replicate triggers from older servers that included no DEFINER clause in the trigger definition. Now the trigger executes with the privileges of the invoker (which on the slave is the slave SQL thread). (Bug #16266)

  • Character set conversion of string constants for UNION of constant and table column was not done when it was safe to do so. (Bug #15949)

  • The DEFINER value for stored routines was not replicated. (Bug #15963)

  • Use of stored functions with DISTINCT or GROUP BY can produce incorrect results when ORDER BY is also used. (Bug #13575)

  • Use of TRUNCATE TABLE for a TEMPORARY table on a master server was propagated to slaves properly, but slaves did not decrement the Slave_open_temp_tables counter properly. (Bug #17137)

  • SELECT COUNT(*) for a MyISAM table could return different results depending on whether an index was used. (Bug #14980)

  • A LEFT JOIN with a UNION that selects literal values could crash the server. (Bug #17366)

  • Large file support did not work in AIX server binaries. (Bug #10776)

  • Updating a view that filters certain rows to set a filtered out row to be included in the table caused infinite loop. For example, if the view has a WHERE clause of salary > 100 then issuing an UPDATE statement of SET salary = 200 WHERE id = 10, caused an infinite loop. (Bug #17726)

  • Certain combinations of joins with mixed ON and USING clauses caused unknown column errors. (Bug #15229)

  • NDB Cluster: Inserting and deleting BLOB column values while a backup was in process could cause the loss of an ndbd node. (Bug #14028)

  • If the server was started with the --skip-grant-tables option, it was impossible to create a trigger or a view without explicitly specifying a DEFINER clause. (Bug #16777)

  • COUNT(DISTINCT col1, col2) and COUNT(DISTINCT CONCAT(col1, col2)) operations produced different results if one of the columns was an indexed DECIMAL column. (Bug #15745)

  • The server displayed garbage in the error message warning about bad assignments to DECIMAL columns or routine variables. (Bug #15480)

  • The server would execute stored routines that had a non-existent definer. (Bug #13198)

  • For FEDERATED tables, a SELECT statement with an ORDER BY clause did not return rows in the proper order. (Bug #17377)

  • The FORMAT() function returned an incorrect result when the client's character_set_connection value was utf8. (Bug #16678)

  • NDB Cluster: Some query cache statistics were not always correctly reported for Cluster tables. (Bug #16795)

  • Updating the value of a Unicode VARCHAR column with the result returned by a stored function would cause the insertion of ASCII characters into the column instead of Unicode, even where the function's return type was also declared as Unicode. (Bug #17615)

D.1.2. Changes in release 5.0.19 (04 March 2006)

Functionality added or changed:

  • Incompatible change: The InnoDB storage engine no longer ignores trailing spaces when comparing BINARY or VARBINARY column values. This means that (for example) the binary values 'a' and 'a ' are now regarded as unequal any time they are compared, as they are in MyISAM tables. (Bug #14189)

    See Section 11.4.2, “The BINARY and VARBINARY Types” for more information about the BINARY and VARBINARY types.

  • The result type of the GROUP_CONCAT() function is now VARCHAR only if the the value of the group_concat_max_len system variable is less than or equal to 512. Otherwise, this funciton returns a BLOB. (Bug #14169)

  • Added the mysql_upgrade program that checks all tables for incompatibilities with the current version of MySQL Server and repairs them if necessary. This program should be run for each MySQL upgrade (rather than mysql_fix_privilege_tables). See Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.

  • Added the FOR UPGRADE option for the CHECK TABLE statement. This option checks whether tables are incompatible with the current version of MySQL Server. Also added the --check-upgrade to mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.

  • mysql no longer terminates data value display when it encounters a NUL byte. Instead, it displays NUL bytes as spaces. (Bug #16859)

  • Added the --wait-timeout option to mysqlmanager to allow configuration of the timeout for dropping an inactive connection, and increased the default timeout from 30 seconds to 28,800 seconds (8 hours). (Bug #12674)

  • A number of performance issues were resolved that had previously been encountered when using statements that repeatedly invoked stored functions. For example, calling BENCHMARK() using a stored function executed much more slowly than when invoking it with inline code that accomplished the same task. In most cases the two should now execute with approximately the same speed. (Bug #15014, Bug #14946)

  • libmysqlclient now uses versioned symbols with GNU ld. (Bug #3074)

  • NDB Cluster: More descriptive warnings are now issued when inappropriate logging parameters are set in config.ini. (Formerly, the warning issued was simply Could not add logfile destination.) (Bug #11331)

  • Added the --port-open-timeout option to mysqld to control how many seconds the server should wait for the TCP/IP port to become free if it cannot be opened. (Bug #15591)

  • Repeated invocation of my_init() and my_end() caused corruption of character set data and connection failure. (Bug #6536)

  • Two new Hungarian collations are included: utf8_hungarian_ci and ucs2_hungarian_ci. These support the correct sort order for Hungarian vowels. However, they do not support the correct order for sorting Hungarian consonant contractions; this issue will be fixed in a future release.

  • Wording of error 1329 changed to No data - zero rows fetched, selected, or processed. (Bug #15206)

  • The INFORMATION_SCHEMA now skips data contained in unlistable/unreadable directories rather than returning an error. (Bug #15851)

  • InnoDB now caches a list of unflushed files instead of scanning for unflushed files during a table flush operation. This improves performance when --innodb-file-per-table is set on a system with a large number of InnoDB tables. (Bug #15653)

  • The message for error 1109 changed from Unknown table ... in order clause to Unknown table ... in field list. (Bug #15091)

  • The mysqltest utility now converts all CR/LF combinations to LF to allow test cases intended for Windows to work properly on UNIX-like systems. (Bug #13809)

  • The mysql_ping function will now retry if the reconnect flag is set and error CR_SERVER_LOST is encountered during the first attempt to ping the server. (Bug #14057)

  • mysqldump now surrounds the DEFINER, SQL SECURITY DEFINER and WITH CHECK OPTION clauses of a CREATE VIEW statement with "not in version" comments to prevent errors in earlier versions of MySQL. (Bug #14871)

  • New charset command added to mysql command-line client. By typing charset name or \C name (such as \C UTF8), the client character set can be changed without reconnecting. (Bug #16217)

  • Client API will now attempt reconnect on TCP/IP if the reconnect flag is set, as is the case with sockets. (Bug #2845)

Bugs fixed:

  • Cursors in stored routines could cause a server crash. (Bug #16887)

  • Setting the myisam_repair_threads system variable to a value larger than 1 could cause corruption of large MyISAM tables. (Bug #11527)

  • When using the GROUP_CONCAT() function where the group_concat_max_len system variable was greater than 512, the type of the result was BLOB only if the query included an ORDER BY clause; otherwise the result was a VARCHAR. (Bug #14169)

  • The length of a VARCHAR() column that used the utf8 character set would increase each time the table was re-created in a stored procedure or prepared statement, eventually causing the CREATE TABLE statement to fail. (Bug #13134)

  • type_decimal failed with the prepared statement protocol. (Bug #17826)

  • The MySQL server could crash with out of memory errors when performing aggregate functions on a DECIMAL column. (Bug #17602)

  • A stored procedure failed to return data the first time it was called per connection. (Bug #17476)

  • Using DROP FUNCTION IF EXISTS func_name to drop a user-defined function caused a server crash if the server was running with the --skip-grant-tables option. (Bug #17595)

  • Using ALTER TABLE to increase the length of a BINARY(M) column caused column values to be padded with spaces rather than 0x00 bytes. (Bug #16857)

  • A large BIGINT value specified in a WHERE clause could be treated differently depending on whether it is specified as a quoted string. (For example, WHERE bigint_col = 17666000000000000000 versus WHERE bigint_col = '17666000000000000000'). (Bug #9088)

  • A natural join between INFORMATION_SCHEMA tables failed. (Bug #17523)

  • A memory leak caused warnings on slaves for certain statements that executed without warning on the master. (Bug #16175)

  • The embedded server did not allow binding of columns to the MYSQL_TYPE_VAR_STRING data type in prepared statements. (Bug #12070)

  • The embedded server failed various tests in the automated test suite. (Bug #9630, Bug #9631, Bug #9633, Bug #10801, Bug #10911, Bug #10924, Bug #10925, Bug #10926, Bug #10930, Bug #15433)

  • Instance Manager erroneously accepted a list of instance identifiers for the START INSTANCE and STOP INSTANCE commands (should accept only a single identifier). (Bug #12813)

  • For a transaction that used MyISAM and InnoDB tables, interruption of the transaction due to a dropped connection on a master server caused slaves to lose synchrony. (Bug #16559)

  • SELECT with GROUP BY on a view can cause a server crash. (Bug #16382)

  • If the query optimizer transformed a GROUP BY clause in a subquery, it did not also transform the HAVING clause if there was one, producing incorrect results. (Bug #16603)

  • SUBSTRING_INDEX() could yield inconsistent results when applied with the same arguments to consecutive rows in a query. (Bug #14676)

  • The parser allowed CREATE AGGREGATE FUNCTION for creating stored functions, even though AGGREGATE does not apply. (It is used only for CREATE FUNCTION only when creating user-defined functions.) (Bug #16896)

  • Data truncations on non-UNIQUE indexes could crash InnoDB when using multi-byte character sets. (Bug #17530)

  • Triggers created without BEGIN and END clauses could not be properly restored from a mysqldump file. (Bug #16878)

  • The RENAME TABLE statement did not move triggers to the new table. (Bug #13525)

  • Clients compiled from source with the --without-readline did not save command history from session to session. (Bug #16557)

  • Stored routines that contained only a single statement were not written properly to the dumpfile when using mysqldump. (Bug #14857)

  • For certain MERGE tables, the optimizer wrongly assumed that using index_merge/intersection was too expensive. (Bug #17314)

  • Executing a SHOW CREATE VIEW query of an invalid view caused the mysql_next_result function of libMySQL.dll to hang. (Bug #15943)

  • BIT fields were not properly handled when using row-based replication. (Bug #13418)

  • Issuing GRANT EXECUTE on a procedure would display any warnings related to the creation of the procedure. (Bug #7787)

  • NDB Cluster: ndb_delete_all would run out of memory on tables containing BLOB columns. (Bug #16693)

  • NDB Cluster: UNIQUE keys in Cluster tables were limited to 225 bytes in length. (Bug #15918)

  • In a highly concurrent environment, a server crash or deadlock could result from execution of a statement that used stored functions or activated triggers coincident with alteration of the tables used by these functions or triggers. (Bug #16593)

  • Previously, a stored function invocation was written to the binary log as DO func_name() if the invocation changes data and occurs within a non-logged statement, or if the function invokes a stored procedure that produces an error. These invocations now are logged as SELECT func_name() instead for better control over error code checking (slave servers could stop due to detecting a different error than occurred on the master). (Bug #14769)

  • CHECKSUM TABLE returned different values on MyISAM table depending on whether the QUICK or EXTENDED options were used. (Bug #8841)

  • MySQL server dropped client connection for certain SELECT statements against views defined that used MERGE algorithm. (Bug #16260)

  • A call to the IF() function using decimal arguments could return incorrect results. (Bug #16272)

  • A statement containing GROUP BY and HAVING clauses could return incorrect results when the HAVING clause contained logic that returned FALSE for every row. (Bug #14927)

  • Using GROUP BY on column used in WHERE clause could cause empty set to be returned. (Bug #16203)

  • For a MySQL 5.0 server, using MySQL 4.1 tables in queries with a GROUP BY clause could result in buffer overrun or a server crash. (Bug #16752)

  • SET sql_mode = N, where N > 31, did not work properly. (Bug #13897)

  • NDB Cluster: Cluster log file paths were truncated to 128 characters. They may now be as long as MAX_PATH (the maximum path length permitted by the operating system). (Bug #17411)

  • The mysql_stmt_store_result() C API function could not be used for a prepared statement if a cursor had been opened for the statement. (Bug #14013)

  • The mysql_stmt_sqlstate() C API function incorrectly returned an empty string rather than '00000' when no error occurred. (Bug #16143)

  • Using the TRUNCATE() function with a negative number for the second argument on a BIGINT column returned incorrect results. (Bug #8461)

  • Instance Manager searched wrong location for password file on some platforms. (Bug #16499)

  • NDB Cluster: Following multiple forced shutdowns and restarts of data nodes, DROP DATABASE could fail. (Bug #17325)

  • NDB Cluster: An UPDATE with an inner join failed to match any records if both tables in the join did not have a primary key. (Bug #17257)

  • NDB Cluster: A DELETE with a join in the WHERE clause failed to retrieve any records if both tables in the join did not have a primary key. (Bug #17249)

  • The error message returned by perror --ndb was prefixed with OS error code: instead of NDB error code:. (Bug #17235)

  • NDB Cluster: In some cases, LOAD DATA INFILE did not load all data into NDB tables. (Bug #17081)

  • NDB Cluster: The REDO log would become corrupted (and thus unreadable) in some circumstances, due to a failure in the query handler. (Bug #17295)

  • NDB Cluster: No error message was generated for setting NoOfFragmentLogFiles too low. (Bug #13966)

  • NDB Cluster: No error message was generated for setting MaxNoOfAttributes too low. (Bug #13965)

  • Binary distributions for Solaris contained files with group ownership set to the non-existing wheel group. Now the bin group is used. (Bug #15562)

  • The DECIMAL data type was not being handled correctly with prepared statements. (Bug #16511)

  • The SELECT privilege was required for triggers that performed no selects. (Bug #15196)

  • The UPDATE privilege was required for triggers that performed no updates. (Bug #15166)

  • CAST(... AS TIME) operations returned different results when using versus not using prepared-statement protocol. (Bug #15805)

  • Improper memory handling for stored routine variables could cause memory overruns and binary log corruption. (Bug #15588)

  • Killing a long-running query containing a subquery could cause a server crash. (Bug #14851)

  • A FULLTEXT query in a prepared statement could result in unexpected behavior. (Bug #14496)

  • A RETURN statement within a trigger caused a server crash. RETURN now is disallowed within triggers. To exit immediately, use LEAVE. (Bug #16829)

  • STR_TO_DATE(1,NULL) caused a server crash. (Bug #15828)

  • An invalid stored routine could not be dropped. (Bug #16303)

  • When evaluation of the test in a CASE failed in a stored procedure that contained a CONTINUE handler, execution resumed at the beginning of the CASE statement instead of at the end. (Bug #16568)

  • An INSERT statement in a stored procedure corrupted the binary log. (Bug #16621)

  • When MyODBC or any other client called my_init()/my_end() several times, it caused corruption of charset data stored in once_mem_pool. (Bug #11892)

  • When multiple handlers are created for the same MySQL error number within nested blocks, the outermost handler took precedence. (Bug #15011)

  • Certain LEAVE statements in stored procedures were not properly optimized. (Bug #15737)

  • Setting InnoDB path settings to an empty string caused InnoDB storage engine to crash upon server startup. (Bug #16157)

  • InnoDB used full explicit table locks in trigger processing. (Bug #16229)

  • Server crash when dropping InnoDB constraints named TABLENAME_ibfk_0. (Bug #16387)

  • Corrected race condition when dropping the adaptive hash index for a B-tree page in InnoDB. (Bug #16582)

  • MYSQL_OPT_RECONNECT option was modified by calls to the mysql_real_connect() function. (Bug #15719)

  • InnoDB: After upgrading an InnoDB table having a VARCHAR BINARY column created in MySQL 4.0 to MySQL 5.0, update operations on the table would cause the server to crash. (Bug #16298)

  • Trying to compile the server on Windows generated a stack overflow warning due to a recursive definition of the internal Field_date::store() method. (Bug #15634)

  • The use of LOAD INDEX within a stored routine was permitted and caused the server to crash. Note: LOAD INDEX statements within stored routines are not supported, and now yield an error if attempted. This behavior is intended. (Bug #14270)

  • The mysqlbinlog utility did not output DELIMITER statements, causing syntax errors for stored routine creation statements. (Bug #11312)

  • NDB Cluster returned incorrect Can't find file error for OS error 24, changed to Too many open files. (Bug #15020)

  • Performing a RENAME TABLE on an InnoDB table when the server is started with the --innodb-file-per-table and the data directory is a symlink caused a server crash. (Bug #15991)

  • The mysql_stmt_attr_get function returned an unsigned int instead of a boolean for STMT_ATTR_UPDATE_MAX_LENGTH. (Bug #16144)

  • Multi-byte path names for LOAD DATA and SELECT ... INTO OUTFILE caused errors. Added the character_set_filesystem system variable, which controls the interpretation of string literals that refer to filenames. (Bug #12448)

  • Certain subqueries where the inner query is the result of a aggregate function would return different results on MySQL 5.0 than on MySQL 4.1. (Bug #15347)

  • Attempts to create FULLTEXT indexes on VARCHAR columns larger than 1000 bytes resulted in error. (Bug #13835)

  • Characters in the gb2312 and euckr character sets which did not have Unicode mappings were truncated. (Bug #15377)

  • Certain nested LEFT JOIN operations were not properly optimized. (Bug #16393)

  • GRANT statements specifying schema names that included underscore characters (i.e. my_schema) did not match if the underscore was escaped in the GRANT statement (i.e. GRANT ALL ON `my\_schema` ...). (Bug #14834)

  • Running out of diskspace in the location specified by the tmpdir option resulted in incorrect error message. (Bug #14634)

  • Test suite sp test left behind tables when the test failed that could cause future tests to fail. (Bug #15866)

  • UPDATE statement crashed multi-byte character set FULLTEXT index if update value was almost identical to initial value only differing in some spaces being changed to &nbsp;. (Bug #16489)

  • A SELECT query which contained a GROUP_CONCAT() and an ORDER BY clause against the INFORMATION_SCHEMA resulted in an empty result set. (Bug #15307)

  • The --replicate-do and --replicate-ignore options were not being enforced on multiple-table statements. (Bug #15699, Bug #16487)

  • A prepared statement created from a SELECT ... LIKE query (such as PREPARE stmt1 FROM 'SELECT col_1 FROM tedd_test WHERE col_1 LIKE ?';) would begin to produce erratic results after being executed repeatedly numerous (thousands) of times. (Bug #12734)

  • The server would crash when the size of an ARCHIVE table grew beyond 2GB. (Bug #15787)

  • Created a user function with an empty string (that is, CREATE FUNCTION ''()), was accepted by the server. Following this, calling SHOW FUNCTION STATUS would cause the server to crash. (Bug #15658)

  • In some cases the query optimizer did not properly perform multiple joins where inner joins followed left joins, resulting in corrupted result sets. (Bug #15633)

  • The absence of a table in the left part of a left or right join was not checked prior to name resolution, which resulted in a server crash. (Bug #15538)

  • NDBCluster: A bitfield whose offset and length totaled 32 would crash the cluster. (Bug #16125)

  • NDBCluster: Upon the completion of a scan where a key request remained outstanding on the primary replica and a starting node died, the scan did not terminate. This caused incompleted error handling of the failed node. (Bug #15908)

  • NDBCluster: The ndb_autodiscover test failed sporadically due to a node not being permitted to connect to the cluster. (Bug #15619)

  • NDBCluster: When running more than one management process in a cluster:

    • ndb_mgm -c host:port -e "node_id stop" would stop a management process running only on the same system on which the command was issued.

    • ndb_mgm -e "shutdown" failed to shut down any management processes at all.

    (Bug #12045, Bug #12124)

  • The contents of fill_help_tables.sql could not be loaded in strict SQL mode. (Bug #15760)

  • fill_help_tables.sql was not included in binary distributions for several platforms. (Bug #15759)

  • An INSERT ... SELECT statement between tables in a MERGE set can return errors when statement involves insert into child table from merge table or vice-versa. (Bug #5390)

  • Certain permission management statements could create a NULL hostname for a user, resulting in a server crash. (Bug #15598)

  • A COMMIT statement followed by a ALTER TABLE statement on a BDB table caused server crash. (Bug #14212)

  • A DELETE statement involving a LEFT JOIN and an IS NULL test on the right-hand table of the join crashed the server when the innodb_locks_unsafe_for_binlog option was enabled. (Bug #15650)

  • Performing an ORDER BY on an indexed ENUM column returned error. (Bug #15308)

  • The NOT FOUND condition handler for stored procedures did not distinguish between a NOT FOUND condition and an exception or warning. (Bug #15231)

  • A stored procedure with an undefined variable and an exception handler would hang the client when called. (Bug #14498)

  • Subselect could return wrong results when records cache and grouping was involved. (Bug #15347)

  • Temporary table aliasing did not work inside stored functions. (Bug #12198)

  • MIN() and MAX() operations were not optimized for views. (Bug #16016)

  • Using an aggregate function as the argument for a HAVING clause would result in the aggregate function always returning FALSE. (Bug #14274)

  • Parallel builds occasionally failed on Solaris. (Bug #16282)

  • The FORCE INDEX keyword in a query would prevent an index merge from being used where an index merge would normally be chosen by the optimizer. (Bug #16166)

  • The COALESCE() function truncated data in a TINYTEXT column. (Bug #15581)

  • InnoDB: Comparison of indexed VARCHAR CHARACTER SET ucs2 COLLATE ucs2_bin columns using LIKE could fail. (Bug #14583)

  • An attempt to open a table that requires a disabled storage engine could cause a server crash. (Bug #15185)

  • Issuing a DROP USER command could cause some users to encounter a hostname is not allowed to connect to this MySQL server error. (Bug #15775)

  • Setting innodb_log_file_size to a value greater than 4G crashed the server. (Bug #15108)

  • A SELECT of a stored function that references the INFORMATION_SCHEMA could crash the server. (Bug #15533)

  • Tarball install package was missing a proper fill_help_tables.sql file. (Bug #15151)

D.1.3. Changes in release 5.0.18 (21 December 2005)

Functionality added or changed:

  • It is now possible to build the server such that MyISAM tables can support up to 128 keys rather than the standard 64. This can be done by configuring the build using the option --with-max-indexes=N, where N≤128 is the maximum number of indexes to permit per table. (Bug #10932)

  • The server treats stored routine parameters and local variables (and stored function return values) according to standard SQL. Previously, parameters, variables, and return values were treated as items in expressions and were subject to automatic (silent) conversion and truncation. Now the data type is observed. Data type conversion and overflow problems that occur in assignments result in warnings, or errors in strict mode. The CHARACTER SET clause for character data type declarations is used. Parameters, variables, and return values must be scalars; it is no longer possible to assign a row value. Also, stored functions execute using the sql_mode value in force at function creation time rather than ignoring it. For more information, see Section 17.2.1, “CREATE PROCEDURE and CREATE FUNCTION Syntax”. (Bug #8702, Bug #8768, Bug #8769, Bug #9078, Bug #9572, Bug #12903, Bug #13705, Bug #13808, Bug #13909, Bug #14161, Bug #15148)

Bugs fixed:

  • API function mysql_stmt_prepare returned wrong field length for TEXT columns. (Bug #15613)

  • The output of mysqldump --triggers did not contain the DEFINER clause in dumped trigger definitions. (Bug #15110)

  • The output of SHOW TRIGGERS contained extraneous whitespace. (Bug #15103)

  • Creating a trigger caused a server crash if the table or trigger database was not known because no default database had been selected. (Bug #14863)

  • SHOW [FULL] COLUMNS and SHOW INDEX FROM did not function with temporary tables. (Bug #14271, Bug #14387, Bug #15224)

  • The INFORMATION_SCHEMA.COLUMNS table did not report the size of BINARY or VARBINARY columns. (Bug #14271)

  • The server would not compile under Cygwin. (Bug #13640)

  • DESCRIBE did not function with temporary tables. (Bug #12770)

  • Reversing the order of operands in a WHERE clause testing a simple equality (such as WHERE t1.col1 = t2.col2) would produce different output from EXPLAIN. (Bug #15106)

  • Column aliases were displayed incorrectly in a SELECT from a view following an update to a base table of the view. (Bug #14861)

  • Set functions could not be aggregated in outer subqueries. (Bug #12762)

  • When a connection using yaSSL was aborted, the server would continue to try to read the closed socket, and the thread continued to appear in the output of SHOW PROCESSLIST. Note that this issue did not affect secure connection attempts using OpenSSL. (Bug #15772)

  • InnoDB: Having two tables in a parent-child relationship enforced by a foreign key where one table used ROW_FORMAT=COMPACT and the other used ROW_FORMAT=REDUNDANT could result in a MySQL server crash. Note that this problem did not exist prior to MySQL 5.0.3, when the compact row format for InnoDB was introduced. (Bug #15550)

  • BDB: A DELETE, INSERT, or UPDATE of a BDB table could cause the server to crash where the query contained a subquery using an index read. (Bug #15536)

  • A left join on a column that having a NULL value could cause the server to crash. (Bug #15268)

  • A replication slave server could sometimes crash on a BEFORE UPDATE trigger if the UPDATE query was not executed in the same database as the table with the trigger. (Bug #14614)

  • A race condition when creating temporary files caused a deadlock on Windows with threads in Opening tables or Waiting for table states. (Bug #12071)

  • InnoDB: If FOREIGN_KEY_CHECKS was 0, InnoDB allowed inconsistent foreign keys to be created. (Bug #13778)

  • NDB Cluster: Under some circumstances, it was possible for a restarting node to undergo a forced shutdown. (Bug #15632)

  • NDB Cluster: If an abort by the Transaction Coordinator timed out, the abort condition was incorrectly handled, causing the transaction record to be released prematurely. (Bug #15685)

  • NDB Cluster: The ndb_read_multi_range.test script failed to drop a table, causing the test to fail. (Bug #15675) (See also Bug #15401.)

  • NDB Cluster: A node which failed during cluster startup was sometimes not removed from the internal list of active nodes. (Bug #15587)

  • Resolution of the argument to the VALUES() function to a variable inside a stored routine caused a server crash. The argument must be a table column. (Bug #15441)

D.1.4. Changes in release 5.0.17 (14 December 2005)

Functionality added or changed:

  • The original Linux RPM packages (5.0.17-0) had an issue with a zlib dependency that would result in an error during an install or upgrade. They were replaced by new binaries, 5.0.17-1. (Bug #15223) Here is a list of the new RPM binaries:

    • MySQL-{Max,client,devel,server,shared,ndb*}-5.0.17-1.i386.rpm

    • MySQL-*-standard-5.0.17-1.rhel3.i386.rpm, MySQL-*-standard-5.0.17-1.rhel3.ia64.rpm, MySQL-*-standard-5.0.17-1.rhel3.x86_64.rpm

    • MySQL-*-pro-5.0.17-1.rhel3.i386.rpm, MySQL-*-pro-5.0.17-1.rhel3.ia64.rpm, MySQL-*-pro-5.0.17-1.rhel3.x86_64.rpm

    • MySQL-*-pro-gpl-5.0.17-1.rhel3.i386.rpm, MySQL-*-pro-gpl-5.0.17-1.rhel3.ia64.rpm, MySQL-*-pro-gpl-5.0.17-1.rhel3.x86_64.rpm

  • The syntax for CREATE TRIGGER now includes a DEFINER clause for specifying which access privileges to check at trigger invocation time. See Section 18.1, “CREATE TRIGGER Syntax”, for more information.

    Known issue: If you attempt to replicate from a master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of CREATE TRIGGER statements fails on the slave with a Definer not fully qualified error. A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:

    CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;
    

    CREATE TRIGGER statements written this way will replicate to newer slaves, which pick up the DEFINER clause from the comment and execute successfully. (Bug #16266)

  • Added a DEFINER column to the INFORMATION_SCHEMA.TRIGGERS table.

  • Invoking a stored function or trigger creates a new savepoint level. When the function or trigger finishes, the previous savepoint level is restored. (See Bug #13825 for more information.)

  • Recursion is allowed in stored procedures. Recursive stored functions and triggers still are disallowed. (Bug #10100)

  • In the latin5_turkish_ci collation, the order of the characters A WITH CIRCUMFLEX, I WITH CIRCUMLEX, and U WITH CIRCUMFLEX was changed. If you have used these characters in any indexed columns, you should rebuild those indexes. (Bug #13421)

  • Support files for compiling with Visual Studio 6 have been removed. (Bug #15094)

Bugs fixed:

  • RPM packages had an incorrect zlib dependency. (Bug #15223)

  • NDB Cluster: REPLACE failed when attempting to update a primary key value in a Cluster table. (Bug #14007)

  • make failed when attempting to build MySQL in different directory than source. (Bug #11827)

  • Corrected an error-handling problem within stored routines on 64-bit platforms. (Bug #15630)

  • Slave SQL thread cleanup was not handled properly on Mac OS X when a statement was killed, resulting in a slave crash. (Bug #15623, Bug #15668)

  • Symbolic links did not function properly on Windows platforms. (Bug #14960, Bug #14310)

  • mysqld would not start on Windows 9X operating systems including Windows Me. (Bug #15209)

  • InnoDB: During replication, There was a failure to record events in the binary log that still occurred even in the event of a ROLLBACK. For example, this sequence of commands:

    BEGIN;
    CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB;
    ROLLBACK;
    INSERT INTO t1 VALUES (1);
    

    would succeed on the replication master as expected. However, the INSERT would fail on the slave because the ROLLBACK would (erroneously) cause the CREATE TEMPORARY TABLE statement not to be written to the binlog. (Bug #7947)

  • A bug in mysql-test/t/mysqltest.test caused that test to fail. (Bug #15605)

  • The CREATE test case in mysql-test-run.pl failed on AIX and SCO. (Bug #15607)

  • NDB Cluster: Creating a table with packed keys failed silently. NDB now supports the PACK_KEYS option to CREATE TABLE correctly. (Bug #14514)

  • NDB Cluster: Using ORDER BY primary_key_column when selecting from a table having the primary key on a VARCHAR column caused a forced shutdown of the cluster. (Bug #14828, Bug #15240, Bug #15682, Bug #15517)

  • NDB Cluster: Under certain circumstances, when mysqld connects to a cluster management server, the connection would fail before a node ID could be allocated. (Bug #15215)

  • NDB Cluster: There was a small window for a node failure to occur during a backup without an error being reported. (Bug #15425)

  • mysql --help was missing a newline after the version string when the bundled readline library was not used. (Bug #15097)

  • Implicit versus explicit conversion of float to integer (such as inserting a float value into an integer column versus using CAST(... AS UNSIGNED before inserting the value) could produce different results. Implicit and explicit typecasts now are done the same way, with a value equal to the nearest integer according to the prevailing rounding mode. (Bug #12956)

  • GROUP BY on a view column did not correctly account for the possibility that the column could contain NULL values. (Bug #14850)

  • ANALYZE TABLE did not properly update table statistics for a MyISAM table with a FULLTEXT index containing stopwords, so a subsequent ANALYZE TABLE would not recognize the table as having already been analyzed. (Bug #14902)

  • The maximum value of MAX_ROWS was handled incorrectly on 64-bit systems. (Bug #14155)

  • NDB Cluster: A forced cluster shutdown occurred when the management daemon was restarted with a changed config.ini file that added an API/SQL node. (Bug #15512)

  • Multiple-table update operations were counting updates and not updated rows. As a result, if a row had several updates it was counted several times for the “rows matched” value but updated only once. (Bug #15028)

  • A statement that produced a warning, when fetched via mysql_stmt_fetch(), did not produce a warning count according to mysql_warning_count(). (Bug #15510)

  • Manual manipulation of the mysql.proc table could cause a server crash. This should not happen, but it is also not supported that the server will notice such changes. (Bug #14233)

  • Revised table locking to allow proper assessment of view security. (Bug #11555)

  • Within a stored procedure, inserting with INSERT ... SELECT into a table with an AUTO_INCREMENT column did not generate the correct sequence number. (Bug #14304)

  • SELECT queries that began with an opening parenthesis were not being placed in the query cache. (Bug #14652)

  • Space truncation was being ignored when inserting into BINARY or VARBINARY columns. Now space truncation results in a warning, or an error in strict mode. (Bug #14299)

  • The database-changing code for stored routine handling caused an error-handling problem resulting in a server crash. (Bug #15392)

  • Selecting from a view processed with the temptable algorithm caused a server crash if the query cache was enabled. (Bug #15119)

  • REPAIR TABLES, BACKUP TABLES, RESTORE TABLES within a stored procedure caused a server crash. (Bug #13012)

  • Creating a view that referenced a stored function that selected from a view caused a crash upon selection from the view. (Bug #15096)

  • ALTER TABLE ... SET DEFAULT had no effect. (Bug #14693)

  • Creating a view within a stored procedure could result in an out of memory error or a server crash. (Bug #14885)

  • InnoDB: A race condition allowed two threads to drop a hash index simultaneously. (Bug #14747)

  • mysqlhotcopy tried to copy INFORMATION_SCHEMA tables. (Bug #14610)

  • CHAR(... USING ...) and CONVERT(CHAR(...) USING ...), though logically equivalent, could produce different results. (Bug #14146)

  • The value of INFORMATION_SCHEMA.TABLES.TABLE_TYPE sometimes was reported as empty. (Bug #14476)

  • InnoDB: Activity on an InnoDB table caused execution time for SHOW CREATE TABLE for the table to increase. (Bug #13762)

  • DELETE from CSV tables reported an incorrect rows-affected value. (Bug #13406)

  • The server crashed if compiled without any transactional storage engines. (Bug #15047)

  • Declaring a stored routine variable to have a DEFAULT value that referred to a variable of the same name caused a server crash. (For example: DECLARE x INT DEFAULT x) Now the DEFAULT variable is interpreted as referring to a variable in an outer scope, if there is one. (Bug #14376)

  • Perform character set conversion of constant values whenever possible without data loss. (Bug #10446)

  • mysql ignored the MYSQL_TCP_PORT environment variable. (Bug #5792)

  • ROW_COUNT() returned an incorrect result after EXECUTE of a prepared statement. (Bug #14956)

  • A UNION of DECIMAL columns could produce incorrect results. (Bug #14216)

  • Queries that select records based on comparisons to a set of column could crash the server if there was one index covering the columns, and a set of other non-covering indexes that taken together cover the columns. (Bug #15204)

  • When using an aggregate function to select from a table that has a multiple-column primary key, adding ORDER BY to the query could produce an incorrect result. (Bug #14920)

  • SHOW CREATE TABLE for a view could fail if the client had locked the view. (Bug #14726)

  • For binary string data types, mysqldump --hex-blob produced an illegal output value of 0x rather than ''. (Bug #13318)

  • Some comparisons for the IN() operator were inconsistent with equivalent comparisons for the = operator. (Bug #12612)

  • In a stored procedure, continuing (via a condition handler) after a failed variable initialization caused a server crash. (Bug #14643)

  • Within a stored procedure, exception handling for UPDATE statements that caused a duplicate-key error caused a Packets out of order error for the following statement. (Bug #13729)

  • Creating a table containing an ENUM or SET column from within a stored procedure or prepared statement caused a server crash later when executing the procedure or statement. (Bug #14410)

  • Selecting from a view used filesort retrieval when faster retrieval was possible. (Bug #14816)

  • Warnings from a previous command were not being reset when fetching from a cursor. (Bug #13524)

  • RESET MASTER failed to delete log files on Windows. (Bug #13377)

  • Using ORDER BY on a column from a view, when also selecting the column normally, and via an alias, caused a mistaken Column 'x' in order clause is ambiguous error. (Bug #14662)

  • Invoking a stored procedure within another stored procedure caused the server to crash. (Bug #13549)

  • Stored functions making use of cursors were not replicated. (Bug #)

  • CAST(expr AS BINARY(N)) did not pad with 0x00 to a length of N bytes. (Bug #14255)

  • Casting a FLOAT or DOUBLE whose value was less than 1.0E-06 to DECIMAL would yield an inappropriate value. (Bug #14268)

  • In some cases, a left outer join could yield an invalid result or cause the server to crash, due to a MYSQL_DATA_TRUNCATED error. (Bug #13488)

  • For a invalid view definition, selecting from the INFORMATION_SCHEMA.VIEWS table or using SHOW CREATE VIEW failed, making it difficult to determine what part of the definition was invalid. Now the server returns the definition and issues a warning. (Bug #13818)

  • The server could misinterpret old trigger definition files created before MySQL 5.0.17. Now they are interpreted correctly, but this takes more time and the server issues a warning that the trigger should be re-created. (Bug #14090)

  • mysqldump --triggers did not account for the SQL mode and could dump trigger definitions with missing whitespace if the IGNORE_SPACE mode was enabled. (Bug #14554)

  • Within a trigger definition the CURRENT_USER() function evaluated to the user whose actions caused the trigger to be activated. Now that triggers have a DEFINER value, CURRENT_USER() evaluates to the trigger definer. (Bug #5861)

  • CREATE TABLE tbl_name (...) SELECT ... could crash the server and write invalid data into the .frm file if the CREATE TABLE and SELECT both contained a column with the same name. Also, if a default value is specified in the column definition, it is now actually used. (Bug #14480)

  • A newline character in a column alias in a view definition caused an error when selecting from the view later. (Bug #13622)

  • mysql_fix_privilege_tables.sql contained an erroneous comment that resulted in an error when the file contents were processed. (Bug #14469)

  • On Windows, the server could crash during shutdown if both replication threads and normal client connection threads were active. (Re-fix of Bug #11796)

  • The grammar for supporting the DEFINER = CURRENT_USER clause in CREATE VIEW and ALTER VIEW was incorrect. (Bug #14719)

  • Queries on ARCHIVE tables that used the filesort sorting method could result in a server crash. (Bug #14433)

  • The mysql_stmt_fetch() C APP function could return MYSQL_NO_DATA for a SELECT COUNT(*) FROM tbl_name WHERE 1 = 0 statement, which should return 1 row. (Bug #14845)

  • A LIMIT-related optimization failed to take into account that MyISAM table indexes can be disabled, causing Error 124 when it tried to use such an index. (Bug #14616)

  • A server crash resulted from the following sequence of events: 1) With no default database selected, create a stored procedure with the procedure name explicitly qualified with a database name (CREATE PROCEDURE db_name.proc_name ...). 2) Create another stored procedure with no database name qualifier. 3) Execute SHOW PROCEDURE STATUS. (Bug #14569)

  • Complex subqueries could cause improper internal query execution environment initialization and crash the server. (Bug #14342)

  • For a table that had been opened with HANDLER OPEN, issuing OPTIMIZE TABLE, ALTER TABLE, or REPAIR TABLE caused a server crash. (Bug #14397)

  • A server crash could occur if a prepared statement invoked a stored procedure that existed when the statement was prepared but had been dropped and re-created prior to statement execution. (Bug #12329)

  • A server crash could occur if a prepared statement updated a table for which a trigger existed when the statement was prepared but had been dropped prior to statement execution. (Bug #13399)

  • Statements that implicitly commit a transaction are prohibited in stored functions and triggers. An attempt to create a function or trigger containing such a statement produces an error. (Bug #13627) (The originally reported symptom was that a trigger that dropped another trigger could cause a server crash. That problem was fixed by the patch for Bug #13343.)

D.1.5. Changes in release 5.0.16 (10 November 2005)

Functionality added or changed:

  • When trying to run the server with yaSSL enabled, MySQL now tries to open /dev/random automatically if /dev/urandom is not available. (Bug #13164)

  • The read_only system variable no longer applies to TEMPORARY tables. (Bug #4544)

  • Due to changes in binary logging, the restrictions on which stored routine creators can be trusted not to create unsafe routines have been lifted for stored procedures (but not stored functions). Consequently, the log_bin_trust_routine_creators system variable and the corresponding --log-bin-trust-routine-creators server option were renamed to log_bin_trust_function_creators and --log-bin-trust-function-creators. For backward compatibility, the old names are recognized but result in a warning. See Section 17.4, “Binary Logging of Stored Routines and Triggers”.

  • Added the Compression status variable, which indicates whether the client connection uses compression in the client/server protocol.

  • In MySQL 5.0.13, syntax for DEFINER and SQL SECURITY clauses was added to the CREATE VIEW and ALTER VIEW statements, but the clauses had no effect. They now are enabled. They specify the security context to be used when checking access privileges at view invocation time. See Section 19.2, “CREATE VIEW Syntax”, for more information.

  • The InnoDB, NDB, BDB, and ARCHIVE storage engines now support spatial columns. See Chapter 16, Spatial Extensions.

  • The CHECK TABLE statement now works for ARCHIVE tables.

  • You must now declare a prefix for an index on any column of any Geometry class, the only exception being when the column is a POINT. (Bug #12267)

  • Added a --hexdump option to mysqlbinlog that displays a hex dump of the log in comments. This output can be helpful for replication debugging.

  • MySQL 5.0 now supports character set conversion for seven additional cp950 characters into the big5 character set: 0xF9D6, 0xF9D7, 0xF9D8, 0xF9D9, 0xF9DA, 0xF9DB, and 0xF9DC. Note: If you move data containing these additional characters to an older MySQL installation which does not support them, you may encounter errors. (Bug #12476)

  • When a date column is set NOT NULL and contains 0000-00-00, it will be updated for UPDATE statements that contains columnname IS NULL in the WHERE clause. (Bug #14186)

Bugs fixed:

  • When the DATE_FORMAT() function appeared in both the SELECT and ORDER BY clauses of a query but with arguments that differ by case (i.e. %m and %M), incorrect sorting may have occurred. (Bug #14016)

  • For InnoDB tables, using a column prefix for a utf8 column in a primary key caused Cannot find record errors when attempting to locate records. (Bug #14056)

  • NDB Cluster: A memory leak occurred when performing ordered index scans using indexes a columns larger than 32 bytes, which would eventually lead to the forced shutdown of all mysqld server processes used with the cluster. (Bug #13078)

  • InnoDB: Large innobase_buffer_pool_size and innobase_log_file_size values were displayed incorrectly on 64-bit systems. (Bug #12701)

  • InnoDB: When dropping and adding a PRIMARY KEY, if a loose index scan using only the second part of multiple-part index was chosen, incorrect keys were created and an endless loop resulted. (Bug #13293)

  • NDB Cluster: Repeated transactions using unique index lookups could cause a memory leak leading to error 288, Out of index operations in transaction coordinator. (Bug #14199)

  • Selecting from a table in both an outer query and a subquery could cause a server crash. (Bug #14482)

  • SHOW CREATE TABLE did not display the CONNECTION string for FEDERATED tables. (Bug #13724)

  • For some stored functions dumped by mysqldump --routines, the function definition could not be reloaded later due to a parsing error. (Bug #14723)

  • For a MyISAM table originally created in MySQL 4.1, INSERT DELAYED could cause a server crash. (Bug #13707)

  • The --exit-info=65536 option conflicted with --temp-pool and caused problems with the server's use of temporary files. Now --temp-pool is ignored if --exit-info=65536 is specified. (Bug #9551)

  • ORDER BY DESC within the GROUP_CONCAT() function was not honored when used in a view. (Bug #14466)

  • A comparison with an invalid date (such as WHERE col_name > '2005-09-31') caused any index on col_name not to be used and a string comparison for each row, resulting in slow performance. (Bug #14093)

  • Within stored routines, REPLACE() could return an empty string (rather than the original string) when no replacement was done, and IFNULL() could return garbage results. (Bug #13941)

  • Inserts of too-large DECIMAL values were handled inconsistently (sometimes set to the maximum DECIMAL value, sometimes set to 0). (Bug #13573)

  • Executing REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE on a view for which an underlying table had been dropped caused a server crash. (Bug #14540)

  • A prepared statement that selected from a view processed using the merge algorithm could crash on the second execution. (Bug #14026)

  • Deletes from a CSV table could cause table corruption. (Bug #14672)

  • An update of a CSV table could cause a server crash. (Bug #13894)

  • For queries with nested outer joins, the optimizer could choose join orders that query execution could not handle. The fix is that now the optimizer avoids choosing such join orders. (Bug #13126)

  • Starting mysqld with the --skip-innodb and --default-storage-engine=innodb (or --default-table-type=innodb caused a server crash. (Bug #9815, re-fix of bug from 5.0.5)

  • mysqlmanager did not start up correctly on Windows 2003. (Bug #14537)

  • The parser did not correctly recognize wildcards in the host part of the DEFINER user in CREATE VIEW statements. (Bug #14256)

  • Memory corruption and a server crash could be caused by statements that used a cursor and generated a result set larger than max_heap_table_size. (Bug #14210)

  • mysqld_safe did not correctly start the -max version of the server (if it was present) if the --ledir option was given. (Bug #13774)

  • The mysql parser did not properly strip the delimiter from input lines less than nine characters long. For example, this could cause USE abc; to result in an Unknown database: abc; error. (Bug #14358)

  • Statements of the form CREATE TABLE ... SELECT ... that created a column with a multi-byte character set could incorrectly calculate the maximum length of the column, resulting in a Specified key was too long error. (Bug #14139)

  • Some updatable views could not be updated. (Bug #14027)

  • Running OPTIMIZE TABLE and other data-updating statements concurrently on an InnoDB table could cause a crash or the following warnings in the error log: Warning: Found locks from different threads in write: enter write_lock, Warning: Found locks from different threads in write: start of release lock. (Bug #11704)

  • Indexes for BDB tables were being limited incorrectly to 255 bytes. (Bug #14381)

  • Use of col_name = VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause of an INSERT statement failed with an Column 'col_name' in field list is ambiguous error. (Bug #13392)

  • On Windows, the server was not ignoring hidden or system directories that Windows may have created in the data directory, and would treat them as available databases. (Bug #4375)

  • mysqldump could not dump views if the -x option was given. (Bug #12838)

  • mysqlimport now issues a SET @@character_set_database = binary statement before loading data so that a file containing mixed character sets (columns with different character sets) can be loaded properly. (Bug #12123)

  • Use of the deprecated --sql-bin-update-same option caused a server crash. (Bug #12974)

  • Maximum values were handled incorrectly for command-line options of type GET_LL. (Bug #12925)

  • For a user that has the SELECT privilege on a view, the server erroneously was also requiring the user to have the EXECUTE privilege at view execution time for stored functions used in the view definition. (Bug #9505)

  • Use of WITH ROLLUP PROCEDURE ANALYSE() could hang the server. (Bug #14138)

  • TIMEDIFF(), ADDTIME(), and STR_TO_DATE() were not reporting that they could return NULL, so functions that invoked them might misinterpret their results. (Bug #14009)

  • The example configuration files supplied with MySQL distributions listed the thread_cache_size variable as thread_cache. (Bug #13811)

  • Using ALTER TABLE to add an index could fail if the operation ran out of temporary file space. Now it automatically makes a second attempt that uses a slower method but no temporary file. In this case, problems that occurred during the first attempt can be displayed with SHOW WARNINGS. (Bug #12166)

  • The input polling loop for Instance Manager did not sleep properly. Instance Manager used up too much CPU as a result. (Bug #14388)

  • Trying to take the logarithm of a negative value is now handled in the same fashion as division by zero. That is, it produces a warning when ERROR_FOR_DIVISION_BY_ZERO is set, and an error in strict mode. (Bug #13820)

  • LOAD DATA INFILE would not accept the same character for both the ESCAPED BY and the ENCLOSED BY clauses. (Bug #11203)

  • The value of Last_query_cost was not updated for queries served from the query cache. (Bug #10303)

  • TIMESTAMPDIFF() returned an incorrect result if one argument but not the other was a leap year and a date was from March or later. (Bug #13534)

  • The server incorrectly accepted column definitions of the form DECIMAL(0,D) for D less than 11. (Bug #13667)

  • The displayed value for the CHARACTER_MAXIMUM_LENGTH column in the INFORMATION_SCHEMA.COLUMNS table was not adjusted for multi-byte character sets. (Bug #14290)

  • A bugfix in MySQL 5.0.15 caused the displayed values for the CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH columns in the INFORMATION_SCHEMA.COLUMNS table to be reversed. (Bug #14207)

  • On Windows, the value of character_sets_dir in SHOW VARIABLES output was displayed inconsistently (using both ‘/’ and ‘\’ as pathname component separators). (Bug #14137)

  • Subqueries in the FROM clause failed if the current database was INFORMATION_SCHEMA. (Bug #14089)

  • Corrected a parser precedence problem that resulted in an Unknown column ... in 'on clause' error for some joins. (Bug #13832)

  • For LIKE ... ESCAPE, an escape sequence longer than one character was accepted as valid. Now the sequence must be empty or one character long. If the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence must be one character long. (Bug #12595)

  • SELECT DISTINCT CHAR(col_name) returned incorrect results after SET NAMES utf8. (Bug #13233)

  • A prepared statement failed with Illegal mix of collations if the client character set was utf8 and the statement used a table that had a character set of latin1. (Bug #12371)

  • Inserting a new row into an InnoDB table could cause DATETIME values already stored in the table to change. (Bug #13900)

  • The default value of query_prealloc_size was set to 8192, lower than its minimum of 16384. The minimum has been lowered to 8192. (Bug #13334)

  • The server did not take character set into account in checking the width of the mysql.user.Password column. As a result, it could incorrectly generate long password hashes even if the column was not long enough to hold them. (Bug #13064)

  • Inserting cp932 strings into a VARCHAR column caused a server crash rather than string truncation if the string was longer than the column definition. (Bug #12547)

  • Two threads that were creating triggers on an InnoDB table at the same time could deadlock. (Bug #12739)

  • mysqladmin and mysqldump would hang on SCO OpenServer. (Bug #13238)

  • Where one stored procedure called another stored procedure: If the second stored procedure generated an exception, the exception was not caught by the calling stored procedure. For example, if stored procedure A used an EXIT statement to handle an exception, subsequent statements in A would be executed regardless when A was called by another stored procedure B, even if an exception that should have been handled by the EXIT was generated in A. (Bug #7049)

  • Trying to create a stored routine with no database selected would crash the server. (Bug #13514, Bug #13587)

  • Specifying --default-character-set=cp-932 for mysqld would cause SQL scripts containing comments written using that character set to fail with a syntax error. (Bug #13487)

  • Trying to compile the server using the --without-geometry option caused the build to fail. (Bug #12991)

D.1.6. Changes in release 5.0.15 (19 October 2005: Production)

Functionality added or changed:

  • Warning: Incompatible change. For BINARY columns, the pad value and how it is handled has changed. The pad value for inserts now is 0x00 rather than space, and there is no stripping of the pad value for selects. For details, see Section 11.4.2, “The BINARY and VARBINARY Types”.

  • Warning: Incompatible change. The CHAR() function now returns a binary string rather than a string in the connection character set. An optional USING charset clause may be used to produce a result in a specific character set instead. Also, arguments larger than 256 produce multiple characters. They are no longer interpreted modulo 256 to produce a single character each. These changes may cause some incompatibilities, as noted in Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • NDB Cluster: The perror utility included with the MySQL-Server RPM now provides support for the --ndb option, and so can be used to obtain error message text for MySQL Cluster error codes. (Bug #13740)

  • NDB Cluster: The ndb_mgm client now reports node startup phases automatically. (Bug #16197)

  • When executing single-table UPDATE or DELETE queries containing an ORDER BY ... LIMIT N clause, but not having any WHERE clause, MySQL can now take advantage of an index to read the first N rows in the ordering specified in the query. If an index is used, only the first N records will be read, as opposed to scanning the entire table. (Bug #12915)

  • The MySQL-server RPM now explicitly assigns the mysql system user to the mysql user group during the postinstallation process. This corrects an issue with upgrading the server on some Linux distributions whereby a previously existing mysql user was not changed to the mysql group, resulting in wrong groups for files created following the installation. (Bug #12823)

  • Added the --tz-utc option to mysqldump. This option adds SET TIME_ZONE='+00:00' to the dump file so that TIMESTAMP columns can be dumped and reloaded between servers in different time zones and protected from changes due to daylight saving time. (Bug #13052)

  • When declaring a local variable (or parameter) named password or name, and setting it with SET (for example, SET password = ''), the new error message ERROR 42000: Variable 'nnn' must be quoted with `...`, or renamed is returned (where 'nnn' is 'password' or 'names'). This means there is a syntax conflict with special sentences like SET PASSWORD = PASSWORD(...) (for setting a user's password) and set names default (for setting charset and collation).

    This must be resolved either by quoting the variable name: SET `password` = ..., which will set the local variable `password`, or by renaming the variable to something else (if setting the user's password is the desired effect).

  • The following statements now cause an implicit COMMIT:

    • CREATE VIEW

    • ALTER VIEW

    • DROP VIEW

    • CREATE TRIGGER

    • DROP TRIGGER

    • CREATE USER

    • RENAME USER

    • DROP USER

    (Bug #13343)

  • NDBCluster: A number of new or improved error messages have been implemented in this release in order to provide better and more accurate diagnostic information regarding cluster configuration issues and problems. (Bug #11739, Bug #11749, Bug #12044, Bug #12786, Bug #13197)

  • NDBCluster: A new “smart” node allocation algorithm means that it is no longer necessary to use sequential IDs for cluster nodes, and that nodes not explicitly assigned IDs should now have IDs allocated automatically in most cases. In practical terms, this means that it is now possible to assign a set of node IDs such as 1, 2, 4, 5 without an error being generated due to the missing 3. (Bug #13009)

Bugs fixed:

  • Issuing STOP SLAVE after having acquired a global read lock with FLUSH TABLES WITH READ LOCK caused a deadlock. Now STOP SLAVE is generates an error in such circumstances. (Bug #10942)

  • An expression in an ORDER BY clause failed with Unknown column 'col_name' in 'order clause' if the expression referred to a column alias. (Bug #11694)

  • mysqldump could not dump views. (Bug #14061)

  • Using an undefined variable in an IF or SET clause inside a stored routine produced an incorrect unknown column ... in 'order clause' error message. (Bug #13037)

  • Trying to create a view dynamically using a prepared statement within a stored procedure failed with error 1295. (Bug #13095)

  • mysqldump --triggers did not quote identifiers properly if the --compatible option was given, so the dump output could not be reloaded. (Bug #13146)

  • Character set conversion was not being done for FIND_IN_SET(). (Bug #13751)

  • CAST(1E+300 TO SIGNED INT) produced an incorrect result on little-endian machines. (Bug #13344)

  • Corrected a memory-copying problem for big5 values when using icc compiler on Linux IA-64 systems. (Bug #10836)

  • On BSD systems, the system crypt() call could return an error for some salt values. The error was not handled, resulting in a server crash. (Bug #13619)

  • Character set file parsing during mysql_real_connect() read past the end of a memory buffer. (Bug #6413)

  • InnoDB: Queries that were executed using an index_merge union or intersection could produce incorrect results if the underlying table used the InnoDB storage engine and had a primary key containing VARCHAR members. (Bug #13484)

  • CREATE DEFINER=... VIEW ... caused the server to crash when run with --skip-grant-tables. (Bug #13504)

  • The --interactive-timeout and --slave-net-timeout options for mysqld were not being obeyed on Mac OS X and other BSD-based platforms. (Bug #8731)

  • Queries of the form (SELECT ...) ORDER BY ... were being treated as a UNION. This improperly resulted in only distinct values being returned (because UNION by default eliminates duplicate results). Also, references to column aliases in ORDER BY clauses following parenthesized SELECT statements were not resolved properly. (Bug #7672)

  • If special characters such as '_' , '%', or the escape character were included within the prefix of a column index, LIKE pattern matching on the indexed column did not return the correct result. (Bug #13046, Bug #13919)

  • An UPDATE query using a join would be executed incorrectly on a replication slave. (Bug #12618)

  • Server crashed during a SELECT statement, writing a message like this to the error log:

    InnoDB: Error: MySQL is trying to perform a SELECT
    InnoDB: but it has not locked any tables in ::external_lock()!
    

    (Bug #12736)

  • NDBCluster: ndb_mgmd would allow a node to be stopped or restarted while another node was still starting up, which could crash the cluster. It should now not be possible to issue a node stop or restart while a different node is still restarting, and the cluster management client issues an error if an attempt is made to do so. (Bug #13461)

  • NDBCluster: Placing multiple [TCP DEFAULT] sections in the cluster config.ini file crashed ndb_mgmd. (The ndb_mgmd process now exits gracefully with an appropriate error message instead.) (Bug #13611)

  • NDBCluster: Trying to run ndbd as system root when connecting to a mysqld process running as the mysql system user via SHM caused the ndbd process to crash. (ndbd should now exit gracefully with an appropriate error message instead.) (Bug #9249)

  • Server may over-allocate memory when performing a FULLTEXT search for stopwords only. (Bug #13582)

  • Queries that use indexes in normal SELECT statements may cause range scans in VIEWs. (Bug #13327)

  • When calling a stored procedure with the syntax CALL schema.procedurename and no default schema selected, ERROR 1046 was displayed after the procedure returned. (Bug #13616)

  • With --log-slave-updates Exec_master_log_pos of SQL thread lagged IO (Bug #13023)

  • SHOW CREATE TABLE did not display any FOREIGN KEY clauses if a temporary file could not be created. Now SHOW CREATE TABLE displays an error message in an SQL comment if this occurs. (Bug #13002)

  • A column in the ON condition of a join that referenced a table in a nested join could not be resolved if the nested join was a right join. (Bug #13597)

  • A qualified reference to a view column in the HAVING clause could not be resolved. (Bug #13410)

  • comp_err did not detect when multiple error messages for a language were given for an error symbol. (Bug #13071)

  • For XA transaction IDs (gtrid.bqual.formatID), uniqueness is supposed to be assessed based on gtrid and bqual. MySQL was also including formatID in the uniqueness check. (Bug #13143)

  • Local (non-XA) and XA transactions are supposed to be mutually exclusive within a given client connection, but this prohibition was not always enforced. (Bug #12935)

  • mysqlcheck --all-databases --analyze --optimize failed because it also tried to analyze and optimize the INFORMATION_SCHEMA tables which it can't. (Bug #13783)

  • SELECT * INTO OUTFILE ... FROM INFORMATION_SCHEMA.schemata failed with an Access denied error. (Bug #13202)

  • A table or view named Ç (C-cedilla) couldn't be dropped. (Bug #13145)

  • Tests containing SHOW TABLE STATUS or INFORMATION_SCHEMA failed on opnsrv6c. (Bug, #14064, Bug #14065)

D.1.7. Changes in release 5.0.14 (Not released)

Functionality added or changed:

The limit of 255 characters on the input buffer for mysql on Windows has been lifted. The exact limit depends on what the system allows, but can be up to 64K characters. A typical limit is 16K characters. (Bug #12929)

Re-enabled the --delayed-inserts option for mysqldump, which now checks for each table dumped whether its storage engine supports DELAYED inserts. (Bug #7815)

Added the myisam_stats_method, which controls whether NULL values in indexes are considered the same or different when collecting statistics for MyISAM tables. This influences the query optimizer as described in Section 7.4.7, “MyISAM Index Statistics Collection”. (Bug #12232)

  • When an InnoDB foreign key constraint is violated, the error message now indicates which table, column, and constraint names are involved. (Bug #3443)

  • Configure-time checking for the availability of multi-byte macros and functions in the bundled readline library. This improves handling of multi-byte character sets in the mysql client. (Bug #3982)

  • The CHAR() function now takes into account the character set and collation given by the character_set_connection and collation_connection system variables. For an argument n to CHAR(), the result is n mod 256 for single-byte character sets. For multi-byte character sets, n must be a valid code point in the character set. Also, the result string from CHAR() is checked for well-formedness. For invalid arguments, or a result that is not well-formed, MySQL generates a warning (or, in strict SQL mode, an error). (Bug #10504)

  • RENAME TABLE now works for views as well, as long as you do not try to rename a view into a different database. (Bug #5508)

  • Multiple-table UPDATE and DELETE statements that do not affect any rows are now written to the binary log and will replicate. (Bug #13348, Bug #12844)

  • Range scans can now be performed for queries on VIEWs such as column IN (<constants>) and column BETWEEN ConstantA AND ConstantB. (Bug #13317)

Bugs fixed:

  • NDBCluster: A trigger updating the value of an AUTO_INCREMENT column in a Cluster table would insert an error code rather than the expected value into the column. (Bug #13961)

  • NDBCluster: When performing a delete of a great many (tens of thousands of) rows at once from a Cluster table, an improperly dereferenced pointer could cause the mysqld process to crash. (Bug #9282)

  • CHECKSUM TABLE locked InnoDB tables and did not use a consistent read. (Bug #12669)

  • The --skip-innodb-doublewrite option disables use of the InnoDB doublewrite buffer. However, having this option in effect when creating a new MySQL installation prevented the buffer from even being created, resulting in a server crash later. (Bug #13367)

  • MySQL programs in binary distributions for Solaris 8/9/10 x86 systems would not run on Pentium III machines. (Bug #6772)

  • When SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE for an InnoDB table were executed from within a stored function or a trigger, they were converted to a non-locking consistent read. (Bug #11238)

  • NDB Cluster: If ndb_restore could not find a free mysqld process, it crashed. (Bug #13512)

  • NDB Cluster: Receipt of several enter single user mode commands by multiple ndb_mgmd processes within a short period of time resulted in cluster shutdown. (Bug #13053)

  • NDB Cluster: Multiple ndb_mgmd processes in a cluster would not know each other's IP addresses. (Bug #12037)

  • NDB Cluster: With two mgmd processes in a cluster, ndb_mgmd output for SHOW would display the same IP address for both processes, even when they were on different hosts. (Bug #11595)

  • NDB Cluster: Queries on NDB tables that are executed using index_merge/union or index_merge/intersection could produce incorrect results. (Bug #13081)

  • The --replicate-rewrite-db and --replicate-do-table options did not work for statements in which tables were aliased to names other than those listed by the options. (Bug #11139)

  • After running configure with the --with-embedded-privilege-control option, the embedded server failed to build. (Bug #13501)

  • Nested handlers within stored procedures didn't work. (Bug #6127)

  • The optimizer chose a less efficient execution plan for col_name BETWEEN const AND const than for col_name = const, even though the two expressions are logically equivalent. Now the optimizer can use the ref access method for both expressions. (Bug #13455)

  • Incorrect creation of DECIMAL local variables in a stored procedure could cause a server crash. (Bug #12589)

  • Queries against a MERGE table that has a composite index could produce incorrect results. (Bug #9112)

  • The server was not rejecting FLOAT(M,D) or DOUBLE(M,D) columns specifications when M was less than D. (Bug #12694)

  • After running configure with the --without-server option, the distribution failed to build. (Bug #11680, Bug #13550)

  • Joins nested under NATURAL or USING joins were sometimes not initialized properly, causing a server crash. (Bug #13545)

  • Locking a view with the query cache enabled and query_cache_wlock_invalidate enabled could cause a server crash. (Bug #13424)

  • A HAVING clause that references an unqualified view column name could crash the server. (Bug #13411)

  • Comparisons involving row constructors containing constants could cause a server crash. (Bug #13356)

  • NDB Cluster: LOAD DATA INFILE with a large data file failed. (Bug #10694)

  • NDB Cluster: Adding an index to a table with a large number of columns (more then 100) crashed the storage node. (Bug #13316)

  • Calling the FORMAT() function with a DECIMAL column value caused a server crash when the value was NULL. (Bug #13361)

  • Aggregate functions sometimes incorrectly were allowed in the WHERE clause of UPDATE and DELETE statements. (Bug #13180)

  • It was possible to create a view that executed a stored function for which you did not have the EXECUTE privilege. (Bug #12812)

  • BIT columns and following columns in NDB tables were corrupt when dumped by mysqldump. (Bug #13152)

  • NATURAL joins and joins with USING against a view could return NULL rather than the correct value. (Bug #13127)

  • Use of a user-defined function within the HAVING clause of a query resulted in an Unknown column error. (Bug #11553)

  • For queries for which the optimizer determined a join type of “Range checked for each record” (as shown by EXPLAIN, the query sometimes could cause a server crash, depending on the data distribution. (Bug #12291)

  • For queries with DISTINCT and WITH ROLLUP, the DISTINCT should be applied after the rollup operation, but was not always. (Bug #12887)

  • The server crashed when processing a view that invoked the CONVERT_TZ() function. (Bug #11416)

  • Shared-memory connections were not working on Windows. (Bug #12723)

D.1.8. Changes in release 5.0.13 (22 Sept 2005: Release Candidate)

Functionality added or changed:

  • The syntax for CREATE VIEW and ALTER VIEW statements now includes DEFINER and SQL SECURITY clauses for specifying the security context to be used when checking access privileges at view invocation time. (The syntax is present in 5.0.13, but these clauses have no effect until 5.0.16.) See Section 19.2, “CREATE VIEW Syntax”, for more information.

  • The --hex-dump option for mysqldump now also applies to BIT columns.

  • Added a --routines option for mysqldump that enables dumping of stored routines. (Bug #9056)

  • The connection string for FEDERATED tables now is specified using a CONNECTION table option rather than a COMMENT table option.

  • Better detection of connection timeout for replication servers on Windows allows elimination of extraneous Lost connection errors in the error log. (Bug #5588)

  • The counters for the Key_read_requests, Key_reads, Key_write_requests, and Key_writes status variables were changed from unsigned long to unsigned longlong to accommodate larger variables without rollover. (Bug #12920)

  • The restriction on the use of PREPARE, EXECUTE, and DEALLOCATE PREPARE within stored procedures was lifted. The restriction still applies to stored functions and triggers. (Bug #10975, Bug #7115, Bug #10605)

  • A new command line argument was added to mysqld to ignore client character set information sent during handshake, and use server side settings instead, to reproduce 4.0 behavior (Bug #9948):

    mysqld --skip-character-set-client-handshake

  • OPTIMIZE TABLE and HANDLER now are prohibited in stored procedures and functions and in triggers. (Bug #12953, Bug #12995)

  • InnoDB: The TRUNCATE TABLE statement for InnoDB tables always resets the counter for an AUTO_INCREMENT column now, regardless of whether there is a foreign key constraint on the table. (Beginning with 5.0.3, TRUNCATE TABLE reset the counter, but only if there was no such constraint.) (Bug #11946)

  • The LEAST() and GREATEST() functions used to return NULL only if all arguments were NULL. Now they return NULL if any argument is NULL, the same as Oracle. (Bug #12791)

  • Two new collations have been added for Esperanto: utf8_esperanto_ci and ucs2_esperanto_ci.

  • Reorder network startup to come after all other initialization, particularly storage engine startup which can take a long time. This also prevents MySQL from being run on a privileged port (any port under 1024) unless run as the root user. (Bug #11707)

  • The Windows binary packages are now compiled with the Microsoft Visual Studio 2003 compiler instead of Microsoft Visual C++ 6.0.

  • The binaries compiled with the Intel icc compiler are now built using icc 9.0 instead of icc 8.1. You will have to install new versions of the Intel icc runtime libraries, which are available from here: ( http://dev.mysql.com/downloads/os-linux.html)

Bugs fixed:

  • Incompatible change: A lock wait timeout caused InnoDB to roll back the entire current transaction. Now it rolls back only the most recent SQL statement. (Bug #12308)

  • The FEDERATED storage engine does not support ALTER TABLE, but no appropriate error message was issued. (Bug #13108)

  • mysqldump did not dump triggers properly. (Bug #12597)

  • NDBCluster: The average row size for Cluster tables was being calculated incorrectly. This affected the values shown for the Data_length and Avg_row_length columns in the output generated by SHOW TABLE STATUS as well as the values for the data_length and data_length/table_rows columns shown in the TABLES table of the INFORMATION_SCHEMA database with respect to Cluster tables (tables using other storage engines were not affected by this bug). (Bug #9896)

  • Within a stored procedure, fetching a large number of rows in a loop using a cursor could result in a server crash or an out of memory error. Also, values inserted within a stored procedure using a cursor were interpreted as latin1 even if character set variables had been set to a different character set. (Bug #6513, Bug #9819)

  • For a server compiled with yaSSL, clients that used MySQL Connector/J were not able to establish SSH connections. (Bug #13029)

  • When used in view definitions, DAYNAME(expr), DAYOFWEEK(expr), WEEKDAY(expr) were incorrectly treated as though the expression was TO_DAYS(expr) or TO_DAYS(TO_DAYS(expr)). (Bug #13000)

  • Incorrect implicit nesting of joins caused the parser to fail on queries of the form SELECT ... FROM t1 JOIN t2 JOIN t3 ON t1.t1col = t3.t3col with an Unknown column 't1.t1col' in 'on clause' error. (Bug #12943)

  • NDB: A cluster shutdown following the crash of a data node would fail to terminate the remaining node processes, even though ndb_mgm showed the shutdown request as having been completed. (Bug #10938, Bug #9996, Bug #11623)

  • A column that can be NULL was not handled properly for WITH ROLLUP in a subquery or view. (Bug #12885)

  • Within a transaction, the following statements now cause an implicit commit: CREATE FUNCTION, DROP FUNCTION, DROP PROCEDURE, ALTER FUNCTION, ALTER PROCEDURE, CREATE PROCEDURE. This corrects a problem where these statements followed by ROLLBACK might not be replicated properly. (Bug #12870)

  • Simultaneous execution of DML statements and CREATE TRIGGER or DROP TRIGGER statements on the same table could cause server crashes or errors. (Bug #12704)

  • If a stored function invoked from a SELECT failed with an error, it could cause the client connection to be dropped. Now such errors generate warnings instead so as not to interrupt the SELECT. (Bug #12379)

  • A concurrency problem for CREATE ... SELECT could cause a server crash. (Bug #12845)

  • The server incorrectly generated an Unknown table error message when for attempts to drop tables in the INFORMATION_SCHEMA database. Now it issues an Access denied message. (Bug #9846)

  • The server allowed privileges to be granted explicitly for the INFORMATION_SCHEMA database. Such privileges are always implicit and should not be grantable. (Bug #10734)

  • The server allowed TEMPORARY tables and stored procedures to be created in the INFORMATION_SCHEMA database. (Bug #9683, Bug #10708)

  • The server failed to disallow SET AUTOCOMMIT in stored functions and triggers. It is allowed to change the value of AUTOCOMMIT in stored procedures, but a runtime error might occur if the procedure is invoked from a stored function or trigger. (Bug #12712)

  • Using an INOUT parameter with a DECIMAL data type in a stored procedure caused a server crash. (Bug #12979)

  • Performing an IS NULL check on the MIN() or MAX() of an indexed column in a complex query could produce incorrect results. (Bug #12695)

  • The mysql.server script contained incorrect path for the libexec directory. (Bug #12550)

  • The NDB START BACKUP command could be interrupted by a SHOW command. (Bug #13054)

  • The LIKE ... ESCAPE syntax produced invalid results when escape character was larger than one byte. (Bug #12611)

  • A client connection thread cleanup problem caused the server to crash when closing the connection if the binary log was enabled. (Bug #12517)

  • Using AS to rename a column selected from a view in a subquery made it not possible to refer to that column in the outer query. (Bug #12993)

  • The character_set_system system variable could not be selected with SELECT @@character_set_system. (Bug #11775)

  • A view-creation statement of the form CREATE VIEW name AS SELECT ... FROM tbl_name AS name failed with a Not unique table/alias: 'name' error. (Bug #6808)

  • UNION [DISTINCT] was not removing all duplicates for multi-byte character values. (Bug #12891)

  • Multiplying a DECIMAL value within a loop in a stored routine could incorrectly result in a value of NULL. (Bug #12938)

  • mysql and mysqldump were ignoring the --defaults-extra-file option. (Bug #12917)

  • Columns named in the USING() clause of JOIN ... USING() were incorrectly resolved in case-sensitive fashion. (Bug #13067)

  • Local variables in stored routines were not always initialized correctly. (Bug #13133)

  • SHOW FIELDS FROM schemaname.viewname caused error 1046 when no default schema was set. (Bug #12905)

  • The value of character_set_results could be set to NULL, but returned the string "NULL" when retrieved. (Bug #12363)

  • InnoDB: Limit recursion depth to 200 in deadlock detection to avoid running out of stack space. (Bug #12588)

  • GROUP_CONCAT() ignored an empty string if it was the first value to occur in the result. (Bug #12863)

  • Outer join elimination was erroneously applied for some queries that used a NOT BETWEEN condition, an IN(value_list) condition, or an IF() condition. (Bug #12101, Bug #12102)

  • SHOW FIELDS truncated the TYPE column to 40 characters. (Bug #7142)

  • Use of PREPARE and EXECUTE with a statement that selected from a view in a subquery could cause a server crash. (Bug #12651)

  • On HP-UX 11.x (PA-RISC), the -L option caused mysqlimport to crash. (Bug #12958)

  • If the binary log is enabled, execution of a stored procedure that modifies table data and uses user variables could cause a server crash or incorrect information to be written to the binary log. (Bug #12637)

  • Queries with subqueries, where the inner subquery uses the range or index_merge access method, could return incorrect results. (Bug #12720)

  • After changing the character set with SET CHARACTER SET, the result of the GROUP_CONCAT() function was not converted to the proper character set. (Bug #12829)

  • A bug introduced in MySQL 5.0.12 caused SHOW TABLE STATUS to display an Auto_increment value of 0 for InnoDB tables. (Bug #12973)

  • Foreign keys were not properly enforced in TEMPORARY tables. Foreign keys now are disallowed in TEMPORARY tables. (Bug #12084)

  • Replication of LOAD DATA INFILE failed between systems that use different pathname syntax (such as delimiter characters). (Bug #11815)

  • Within a stored procedure, a server crash was caused by assigning to a VARCHAR INOUT parameter the value of an expression that included the variable itself. (For example, SET c = c.) (Bug #12849)

  • SELECT ... JOIN ... ON ... JOIN ... USING caused a server crash. (Bug #12977)

  • Using GROUP BY when selecting from a view in some cases could cause incorrect results to be returned. (Bug #12922)

  • myisampack did not properly pack BLOB values larger than 224 bytes. (Bug #4214)

  • Incorrect results could be returned from a view processed using a temporary table. (Bug #12941)

  • The server crashed when one thread resized the query cache while another thread was using it. (Bug #12848)

  • mysqld_multi now quotes arguments on command lines that it constructs to avoid problems with arguments that contain shell metacharacters. (Bug #11280)

  • InnoDB: A consistent read could return inconsistent results due to a bug introduced in MySQL 5.0.5. (Bug #12947)

  • Deadlock occurred when several account management statements were run (particularly between FLUSH PRIVILEGES/SET PASSWORD and GRANT/REVOKE statements). (Bug #12423)

  • The Windows installer made a change to one of the mysql.proc table files, causing stored routine functionality to be compromised. The Windows installer now never overwrites files in the MySQL data directory. During an upgrade from one version to another, a file in the data directory will not be overwritten even if it has not been modified since it was put there by an older installer.

    If you have already lost access to stored routines because of this problem, you can get them back using the following procedure:

    • Stop the server.

    • In the mysql\data directory under your MySQL installation directory, and replace the proc.frm file with corresponding file from the version of MySQL that you were using before you upgraded.

    • Start the server

    • Start the mysql command-line client (use the root account or another account that has full database privileges) and execute the mysql_fix_privilege_tables.sql script that upgrades the grant tables to the current structure. Instructions for doing this are given in Section 5.6.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

    After this, all stored routine functionality should work. (Bug #12820)

  • On Windows, the server was preventing tables from being created if the table name was a prefix of a forbidden name. For example, nul is a forbidden name because it's the same as a Windows device name, but a table with the name of n or nu was being forbidden as well. (Bug #12325)

  • InnoDB was too permissive with LOCK TABLE ... READ LOCAL and allowed new inserts into the table. Now READ LOCAL is equivalent to READ for InnoDB. This will cause slightly more locking in mysqldump, but makes InnoDB table dumps consistent with MyISAM table dumps. (Bug #12410)

  • Use of the mysql client HELP command from within a stored routine caused a “packets out of order” error and a lost connection. Now HELP is detected and disallowed within stored routines. (Bug #12490)

  • Use of yaSSL for a secure client connection caused LOAD DATA LOCAL INFILE to fail. (Bug #11286)

  • SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION no longer qualify the routine name with the database name, for consistency with the behavior of SHOW CREATE TABLE. (Bug #10362)

  • A UNION of long utf8 VARCHAR columns was sometimes returned as a column with a LONGTEXT data type rather than VARCHAR. This could prevent such queries from working at all if selected into a MEMORY table because the MEMORY storage engine does not support the TEXT data types. (Bug #12537)

  • If a client has opened an InnoDB table for which the .ibd file is missing, InnoDB would not honor a DROP TABLE statement for the table. (Bug #12852)

  • ALTER TABLE ... DISCARD TABLESPACE for non-InnoDB table caused the client to lose the connection. (The server was not returning the error properly.) (Bug #12207)

  • DO IFNULL(NULL, NULL) and SELECT CAST(IFNULL(NULL, NULL) AS DECIMAL) caused a server crash. (Bug #12841)

  • When using a cursor, a SELECT statement that uses a GROUP BY clause could return incorrect results. (Bug #11904)

  • The SYSDATE() function now returns the time at which it was invoked. In particular, within a stored routine or trigger, SYSDATE() returns the time at which it executes, not the time at which the stored routine or triggering statement began to execute. (Bug #12480)

  • CREATE VIEW inside a stored procedure caused a server crash if the table underlying the view had been deleted. (Bug #12468)

  • A memory leak resulting from repeated SELECT ... INTO statements inside a stored procedure could cause the server to crash. (Bug #11333)

D.1.9. Changes in release 5.0.12 (02 Sept 2005)

Functionality added or changed:

  • Incompatible change: Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns for NATURAL joins and joins specified with a USING clause and proper ordering of output columns. (Bug #6136, Bug #6276, Bug #6489, Bug #6495, Bug #6558, Bug #9067, Bug #9978, Bug #10428, Bug #10646, Bug #10972.) The precedence of the comma operator also now is lower compared to JOIN. (Bug #4789, Bug #12065, Bug #13551.)

    These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly prior to 5.0.12 must be rewritten to comply with the standard. For details about the scope of the changes and examples that show what query rewrites are necessary, see Section 13.2.7.1, “JOIN Syntax”.

  • Recursive triggers are detected and disallowed. Also, within a stored function or trigger, it is not allowable to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. (Bug #11896, Bug #12644)

  • SHOW TABLE STATUS for a view now shows VIEW in uppercase, consistent with SHOW TABLES and INFORMATION_SCHEMA. (Bug #5501)

  • An optimizer estimate of zero rows for a non-empty InnoDB table used in a left or right join could cause incomplete rollback for the table. (Bug #12779)

  • Calls to stored procedures were written to the binary log even within transactions that were rolled back, causing them to be executed on replication slaves. (Bug #12334)

  • Interleaved execution of stored procedures and functions could be written to the binary log incorrectly, causing replication slaves to get out of sync. (Bug #12335)

  • A query of the form SHOW TABLE STATUS FROM db_name WHERE name IN (select_query) would crash the server. (Bug #12636)

  • Users created using an IP address or other alias rather than a hostname listed in /etc/hosts could not set their own passwords. (Bug #12302)

  • Using DESCRIBE on a view after renaming a column in one of the view's base tables caused the server to crash. (Bug #12533)

  • SHOW OPEN TABLES now supports FROM and LIKE clauses. (Bug #12183)

  • SHOW TABLE STATUS FROM INFORMATION_SCHEMA now sorts output by table name the same as it does for other databases. (Bug #12315)

  • SHOW ENGINE INNODB STATUS now can display longer query strings. (Bug #7819)

  • Added the SLEEP() function, which pauses for the number of seconds given by its argument. (Bug #6760)

  • Trying to drop the default keycache by setting @@global.key_buffer_size to zero now returns a warning that the default keycache cannot be dropped. (Bug #10473)

  • The stability of cursors when used with InnoDB tables was greatly improved. (Bug #11832, Bug #12243, Bug #11309)

  • It is no longer possible to issue FLUSH commands from within stored functions or triggers. See Section I.1, “Restrictions on Stored Routines and Triggers”, for details. (Bug #12280, Bug #12307)

  • INFORMATION_SCHEMA objects are now reported as a SYSTEM VIEW table type. (Bug #11711)

Bugs fixed:

  • CHECKSUM TABLE command returned incorrect results for tables with deleted rows. After upgrading, users who used stored checksum information to detect table changes should rebuild their checksum data. (Bug #12296)

  • A data type of CHAR BINARY was not recognized as valid for stored routine parameters. (Bug #9048)

  • SET GLOBAL TRANSACTION ISOLATION LEVEL was not working. (Bug #11207)

  • NDB Cluster: Corrected the parsing of the CLUSTERLOG command by ndb_mgm to allow multiple items. (Bug #12833)

  • NDB Cluster: Improved error messages related to filesystem issues. (Bug #11218)

  • NDB Cluster: When a schema was detected to be corrupt, ndb neglected to close it, resulting in a “file already open” error if the schema was opened again later. written. (Bug #12027)

  • NDB Cluster: When it could not copy a fragment, ndbd exited without printing a message about the condition to the error log. Now the message is written. (Bug #12900)

  • NDB Cluster: When a disk full condition occurred, ndbd exited without printing a message about the condition to the error log. Now the message is written. (Bug #12716)

  • mysql_fix_privilege_tables.sql was missing a comma, causing a syntax error when executed. (Bug #12705)

  • STRCMP() was not handled correctly in views. (Bug #12489)

  • NDB Cluster: Bad values in config.ini caused ndb_mdmd to crash. (Bug #12043)

  • TRUNCATE TABLE did not work with TEMPORARY InnoDB tables. (Bug #11816)

  • Built-in commands for the mysql client, such as delimiter and \d are now always parsed within files that are read using the \. and source commands. (Bug #11523)

  • ALTER TABLE db_name.t RENAME t did not move the table to default database unless the new name was qualified with the database name. (Bug #11493)

  • It was not possible to create a stored function with a spatial return value data type. (Bug #10499)

  • The only valid values for the PACK_KEYS table option are 0 and 1, but other values were being accepted. (Bug #10056)

  • If a DROP DATABASE fails on a master server due to the presence of a non-database file in the database directory, the master have the database tables deleted, but not the slaves. To deal with failed database drops, we now write DROP TABLE statements to the binary log for the tables so that they are dropped on slaves. (Bug #4680)

  • Improper use of loose index scan in InnoDB sometimes caused incorrect query results. (Bug #12672)

  • DELETE or UPDATE for an indexed MyISAM table could fail. This was due to a change in end-space comparison behavior from 4.0 to 4.1. (Bug #12565)

  • Joins on VARCHAR columns of different lengths could produce incorrect results. (Bug #11398)

  • A “Duplicate column name” error no longer occurs when selecting from a view defined as SELECT * from a join that uses a USING clause on tables that have a common column name. (Bug #6558)

  • Invocations of the SLEEP() function incorrectly could get optimized away for statements in which it occurs. Statements containing SLEEP() incorrectly could be stored in the query cache. (Bug #12689)

  • NDB Cluster: An ALTER TABLE command caused loss of data stored prior to the issuing of the command. (Bug #12118)

  • Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385)

  • NOW(), CURRENT_TIME and values generated by timestamp columns are now constant for the duration of a stored function or trigger. This prevents the breaking of statements-based replication. (Bug #12480, Bug #12481)

  • Some statements executed on a master server caused the SQL thread on a slave to run out of memory. (Bug #12532)

  • A SELECT DISTINCT query with a constant value for one of the columns would return only a single row. (Bug #12625)

  • NDB Cluster: Cluster failed to take character set data into account when recomputing hashes (and thus could not locate records for updating or deletion) following a configuration change and node restart. (Bug #12220)

  • NDB Cluster: Wrong error message displayed when cluster management server closed port while mysqld was connecting. (Bug #10950)

  • A view was allowed to depend on a function that referred to a temporary table. (Bug #10970)

  • Prepared statement parameters could cause errors in the binary log if the character set was cp932. (Bug #11338)

  • The CREATE_OPTIONS column of INFORMATION_SCHEMA.TABLES showed incorrect options for tables in INFORMATION_SCHEMA. (Bug #12397)

  • MEMORY tables using B-Tree index on 64-bit platforms could produce false table is full errors. (Bug #12460)

  • Issuing FLUSH INSTANCES followed by STOP INSTANCE caused instance manager to crash. (Bug #10957)

  • Duplicate instructions in stored procedures resulted in incorrect execution when the optimizer optimized the duplicate code away. (Bug #12168)

  • SHOW TABLES FROM returned wrong error message if the schema specified did not exist. (Bug #12591)

  • The ROW() function returned an incorrect result when comparison involved NULL values. (Bug #12509)

  • Views with multiple UNION and UNION ALL produced incorrect results. (Bug #10624)

  • Stored procedures with particularly long loops could crash server due to memory leak. (Bug #12297, Bug #11247)

  • Trigger and stored procedure execution could break replication. (Bug #12482)

  • A server crash could result from an update of a view defined as a join, even though the update updated only a single table. (Bug #12569)

  • On Windows when the --innodb_buffer_pool_awe_mem_mb option has been given, the server detects whether AWE support is available and has been compiled into the server, and displays an appropriate error message if not. (Bug #6581)

  • The NUMERIC_SCALE column of the INFORMATION_SCHEMA.COLUMNS table should be returned as 0 for integer columns. It was being returned as NULL. (Bug #12301)

  • The COLUMN_DEFAULT column of the INFORMATION_SCHEMA.COLUMNS table should be returned as NULL if a column has no default value. An empty string was being returned if the column was defined as NOT NULL. (Bug #12518)

  • Slave I/O threads were considered to be in the running state when launched (rather than after successfully connecting to the master server), resulting in incorrect SHOW SLAVE STATUS output. (Bug #10780)

  • Column names in subqueries must be unique, but were not being checked for uniqueness. (Bug #11864)

  • On Windows, the server could crash during shutdown if both replication threads and normal client connection threads were active. (Bug #11796)

  • Some subqueries of the form SELECT ... WHERE ROW(...) IN (subquery) were being handled incorrectly. (Bug #11867)

  • Selecting from a view after INSERT statements for the view's underlying table yielded different results than subsequent selects. (Bug #12382)

  • The mysql_info() C API function could return incorrect data when executed as part of a multi-statement that included a mix of statements that do and do not return information. (Bug #11688)

  • When restoring INFORMATION_SCHEMA as the default database after failing to execute a stored procedure in an inaccessible database, the server returned a spurious ERROR 42000: Unknown database 'information_schema' message. (Bug #12318)

  • Renamed the rest() macro in my_list.h to list_rest() to avoid name clashes with user code. (Bug #12327)

  • DATE_ADD() and DATE_SUB() were converting invalid dates to NULL in TRADITIONAL SQL mode rather than rejecting them with an error. (Bug #10627)

  • A trigger that included a SELECT statement could cause a server crash. (Bug #11587)

  • An incorrect conversion from double to ulonglong caused indexes not to be used for BDB tables on HP-UX. (Bug #10802)

  • myisampack failed to delete .TMD temporary files when run with -T option. (Bug #12235)

  • Added portability check for Intel compiler to address a problem compiling InnoDB code. (Bug #11510)

  • XA allowed two active transactions to be started with the same XID. (Bug #12162)

  • Concatenating USER() or DATEBASE() with a column produced invalid results. (Bug #12351)

  • Creating a view that included the TIMESTAMPDIFF() function resulted in a invalid view. (Bug #12298)

  • Comparison of InnoDB multi-part primary keys that include VARCHAR columns can result in incorrect results. (Bug #12340)

  • For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380)

  • Using cursors and nested queries for the same table, corrupted results were returned for the outer query. (Bug #11909)

  • User variables were not automatically cast for comparisons, causing queries to fail if the column and connection character sets differed. Now when mixing strings with different character sets but the same coercibility, allow conversion if one character set is a superset of the other. (Bug #10892)

  • Selecting from a view defined as a join over many tables could result in a server crash due to miscalculation of the number of conditions in the WHERE clause. (Bug #12470)

  • Pathame values for options such as ---basedir or --datadir didn't work on Japanese Windows machines for directory names containing multi-byte characters having a second byte of 0x5C (‘\’). (Bug #5439)

  • A race condition between server threads could cause a crash if one thread deleted a stored routine while another thread was executing a stored routine. (Bug #12228)

  • Mishandling of comparison for rows containing NULL values against rows produced by an IN subquery could cause a server crash. (Bug #12392)

  • Inserting NULL into a GEOMETRY column for a table that has a trigger could result in a server crash if the table was subsequently dropped. (Bug #12281)

  • A failure to obtain a lock for an IN SHARE MODE query could result in a server crash. (Bug #12082)

  • SELECT ... INTO var_name within a trigger could cause a server crash. (Bug #11973)

  • INSERT ... SELECT ... ON DUPLICATE KEY UPDATE could fail with an erroneous “Column 'col_name' specified twice” error. (Bug #10109)

  • SHOW TABLE STATUS sometimes reported a Row_format value of Dynamic for MEMORY tables, though such tables always have a format of Fixed. (Bug #3094)

  • A query using a LEFT JOIN, an IN subquery on the outer table, and an ORDER BY clause, caused the server to crash when cursors were enabled. (Bug #11901)

  • Using a stored procedure that referenced tables in the INFORMATION_SCHEMA database would return an empty result set. (Bug #10055, Bug #12278)

  • Columns defined as TINYINT(1) were redefined as TINYINT(4) when incorporated into a VIEW. (Bug #11335)

  • ISO-8601 formatted dates were not being parsed correctly. (Bug #7308)

  • FLUSH TABLES WITH READ LOCK combined with LOCK TABLE .. WRITE caused deadlock. (Bug #9459)

  • NULL column definitions read incorrectly for inner tables of nested outer joins. (Bug #12154)

  • GROUP_CONCAT ignores the DISTINCT modifier when used in a query joining multiple tables where one of the tables has a single row. (Bug #12095)

  • UNION query with FULLTEXT could cause server crash. (Bug #11869)

D.1.10. Changes in release 5.0.11 (06 Aug 2005)

Functionality added or changed:

  • Security improvement: Applied a patch that addresses a potential zlib data vulnerability that could result in an application crash. (CAN-2005-1849) This only affects the binaries for platforms that are linked statically against the bundled zlib (most notably Microsoft Windows and HP-UX).

  • SHOW CHARACTER SET and INFORMATION_SCHEMA now properly report the Latin1 character set as cp1252. (Bug #11216)

  • mysqldump now dumps triggers for each dumped table. This can be suppressed with the --skip-triggers option. (Bug #10431)

  • Added new ER_STACK_OVERRUN_NEED_MORE error message to indicate that, while the stack is not completely full, more stack space is required. (Bug #11213)

  • NDB: Improved handling of the configuration variables NoOfPagesToDiskDuringRestartACC, NoOfPagesToDiskAfterRestartACC, NoOfPagesToDiskDuringRestartTUP, and NoOfPagesToDiskAfterRestartTUP should result in noticeably faster startup times for MySQL Cluster. (Bug #12149)

  • Added support of where clause for queries with FROM DUAL. (Bug #11745)

  • Added an optimization that avoids key access with NULL keys for the ref method when used in outer joins. (Bug #12144)

  • Maximum size of stored procedures increased from 64k to 4Gb. (Bug #11602)

  • Added error message for users who attempt CREATE TABLE ... LIKE and specify a non-table in the LIKE clause. (Bug #6859)

Bugs fixed:

  • DDL statements now are allowed in stored procedures if the procedure is not invoked from a stored function or a trigger. Also fixed problems where a TEMPORARY statement created by one stored routine was inaccessible to another routine invoked during the same connection. (Bug #11126)

  • Creation of the mysql group account failed during the RPM installation. (Bug #12348)

  • big5 strings were not being stored in FULLTEXT index. (Bug #12075)

  • When DROP DATABASE was called concurrently with a DROP TABLE of any table the MySQL Server crashed. (Bug #12212)

  • max_connections_per_hour setting was being capped by unrelated max_user_connections setting. (Bug #9947)

  • SELECT @@local... returned @@session... in the column header. (Bug #10724)

  • Multiplying ABS() output by a negative number would return incorrect results. (Bug #11402)

  • Updated dependency list for RPM builds to include missing dependencies such as useradd and groupadd. (Bug #12233)

  • mysql_install_db used static localhost value in GRANT tables even when server hostname is not localhost, such as localhost.localdomain. This change is applied to version 5.0.10b on Windows. (Bug #11822)

  • Multiple SELECT SQL_CACHE queries in a stored procedure causes error and client hang. (Bug #6897)

  • Added checks to prevent error when allocating memory when there was insufficient memory available. (Bug #7003)

  • Character data truncated when GBK characters 0xA3A0 and 0xA1 are present. (Bug #11987)

  • Comparisons like SELECT "A\\" LIKE "A\\"; fail when using SET NAMES utf8;. (Bug #11754)

  • When used in a SELECT query against a view, the GROUP_CONCAT() function returned only a single row. (Bug #11412)

  • Calling the C API function mysql_stmt_fetch() after all rows of a result set were exhausted would return an error instead of MYSQL_NO_DATA. (Bug #11037)

  • Information about a trigger was not displayed in the output of SELECT ... FROM INFORMATION_SCHEMA.TRIGGERS when the selected database was INFORMATION_SCHEMA, prior to the trigger's first invocation. (Bug #12127)

  • Issuing successive FLUSH TABLES WITH READ LOCK would cause the mysql client to hang. (Bug #11934)

  • In stored procedures, a cursor that fetched an empty string into a variable would set the variable to NULL instead. (Bug #8692)

  • A trigger dependent on a feature of one SQL_MODE setting would cause an error when invoked after the SQL_MODE was changed. (Bug #5891)

  • A delayed insert that would duplicate an existing record crashed the server instead. (Bug #12226)

  • ALTER TABLE when SQL_MODE = 'TRADITIONAL' gave rise to an invalid error message. (Bug #11964)

  • Attempting to repair a table having a fulltext index on a column containing words whose length exceeded 21 characters and where myisam_repair_threads was greater than 1 would crash the server. (Bug #11684)

  • The MySQL Cluster backup log was invalid where the number of Cluster nodes was not equal to a power of 2. (Bug #11675)

  • GROUP_CONCAT() sometimes returned a result with a different collation from that of its arguments. (Bug #10201)

  • The LPAD() and RPAD() functions returned the wrong length to mysql_fetch_fields(). (Bug #11311)

  • A UNIQUE VARCHAR column would be mis-identified as MUL in table descriptions. (Bug #11227)

  • Incorrect error message displayed if user attempted to create a table in a non-existing database using CREATE database_name.table_name syntax. (Bug #10407)

  • InnoDB: Do not flush after each write, not even before setting up the doublewrite buffer. Flushing can be extremely slow on some systems. (Bug #12125)

  • InnoDB: True VARCHAR: Return NULL columns in the format expected by MySQL. (Bug #12186)

  • Two threads could potentially initialize different characters sets and overwrite each other. (Bug #12109)

  • Unsigned LONG system variables may return incorrect value when retrieved with a SELECT for certain values. (Bug #10351)

  • Prepared statements were not being written to the Slow Query log. (Bug #9968)

D.1.11. Changes in release 5.0.10 (27 July 2005)

Functionality added or changed:

  • Security improvement: Applied a patch that addresses a zlib data vulnerability that could result in a buffer overflow and code execution. (CAN-2005-2096) (Bug #11844)

  • Incompatible change: The namespace for triggers has changed. 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). (Bug #5892)

    Note: When upgrading from a previous version of MySQL 5 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. A suggested procedure for doing this is given in Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • The viewing of triggers and trigger metadata has been enhanced as follows:

  • Triggers can now reference tables by name. See Section 18.1, “CREATE TRIGGER Syntax”, for more information.

  • The output of perror --help now displays the --ndb option. (Bug #11999)

  • On Windows, the search path used by MySQL applications for my.ini now includes ..\my.ini (that is, the application's parent directory, and hence, the installation directory). (Bug #10419)

  • Added mysql_get_character_set_info() C API function for obtaining information about the default character set of the current connection.

  • The bundled version of the readline library was upgraded to version 5.0.

  • It is no longer necessary to issue an explicit LOCK TABLES for any tables accessed by a trigger prior to executing any statements that might invoke the trigger. (Bug #9581, Bug #8406)

  • MySQL Cluster: A new -P option is available for use with the ndb_mgmd client. When called with this option, ndb_mgmd prints all configuration data to stdout, then exits.

  • Add table_lock_wait_timeout global server system variable.

Bugs fixed:

  • NDB: Trying to use a greater number of tables then specified by the value of MaxNoOfTables caused table corruption such that data nodes could not be restarted. (Bug #9994)

  • NDB: Attempting to create or drop tables during a backup would cause the cluster to shut down. (Bug #11942)

  • When attempting to drop a table with a broken unique index, NDB failed to drop the table and erroneously report that the table was unknown. (Bug #11355)

  • SELECT ... NOT IN() gave unexpected results when only static value present between the (). (Bug #11885)

  • Fixed compile error when using GCC4 on AMD64. (Bug #12040)

  • NDB ignored the Hostname option in the NDBD DEFAULT section of the Cluster configuration file. (Bug #12028)

  • SHOW PROCEDURE/FUNCTION STATUS didn't work for users with limited access. (Bug #11577)

  • MySQL server would crash is a fetch was performed after a ROLLBACK when cursors were involved. (Bug #10760)

  • The temporary tables created by an ALTER TABLE on a cluster table were visible to all MySQL servers. (Bug #12055)

  • NDB_MGMD was leaking file descriptors. (Bug #11898)

  • IP addresses not shown in ndb_mgm SHOW command on second ndb_mgmd (or on ndb_mgmd restart). (Bug #11596)

  • Functions that evaluate to constants (such as NOW() and CURRENT_USER() were being evaluated in the definition of a VIEW rather than included verbatim. (Bug #4663)

  • Execution of SHOW TABLES failed to increment the Com_show_tables status variable. (Bug #11685)

  • For execution of a stored procedure that refers to a view, changes to the view definition were not seen. The procedure continued to see the old contents of the view. (Bug #6120)

  • For prepared statements, the SQL parser did not disallow ‘?’ parameter markers immediately adjacent to other tokens, which could result in malformed statements in the binary log. (For example, SELECT * FROM t WHERE? = 1 could become SELECT * FROM t WHERE0 = 1.) (Bug #11299)

  • When two threads compete for the same table, a deadlock could occur if one thread has also a lock on another table through LOCK TABLES and the thread is attempting to remove the table in some manner and the other thread want locks on both tables. (Bug #10600)

  • Aliasing the column names in a VIEW did not work when executing a SELECT query on the VIEW. (Bug #11399)

  • Performing an ORDER BY on a SELECT from a VIEW produced unexpected results when VIEW and underlying table had the same column name on different columns. Bug #11709)

  • The C API function mysql_statement_reset() did not clear error information. (Bug #11183)

  • When used within a subquery, SUBSTRING() returned an empty string. (Bug #10269)

  • Multiple-table UPDATE queries using CONVERT_TZ() would fail with an error. (Bug #9979)

  • mysql_fetch_fields() returned incorrect length information for MEDIUM and LONG TEXT and BLOB columns. (Bug #9735)

  • mysqlbinlog was failing the test suite on Windows due to BOOL being incorrectly cast to INT. (Bug #11567)

  • NDBCLuster: Server left core files following shutdown if data nodes had failed. (Bug #11516)

  • Creating a trigger in one database that references a table in another database was being allowed without generating errors. (Bug #8751)

  • Duplicate trigger names were allowed within a single schema. (Bug #6182)

  • Server did not accept some fully-qualified trigger names. (Bug #8758)

  • The traditional SQL mode accepted invalid dates if the date value provided was the result of an implicit type conversion. (Bug #5906)

  • The MySQL server had issues with certain combinations of basedir and datadir. (Bug #7249)

  • INFORMATION_SCHEMA.COLUMNS had some inaccurate values for some data types. (Bug #11057)

  • LIKE pattern matching using prefix index didn't return correct result. (Bug #11650)

  • For several character sets, MySQL incorrectly converted the character code for the division sign to the eucjpms character set. (Bug #11717)

  • When invoked within a view, SUBTIME() returned incorrect values. (Bug #11760)

  • SHOW BINARY LOGS displayed a file size of 0 for all log files but the current one if the files were not located in the data directory. (Bug #12004)

  • Server-side prepared statements failed for columns with a character set of ucs2. (Bug #9442)

  • References to system variables in an SQL statement prepared with PREPARE were evaluated during EXECUTE to their values at prepare time, not to their values at execution time. (Bug #9359)

  • For server shutdown on Windows, error messages of the form Forcing close of thread n user: 'name' were being written to the error log. Now connections are closed more gracefully without generating error messages. (Bug #7403)

  • Increased the version number of the libmysqlclient shared library from 14 to 15 because it is binary incompatible with the MySQL 4.1 client library. (Bug #11893)

  • A recent optimizer change caused DELETE ... WHERE ... NOT LIKE and DELETE ... WHERE ... NOT BETWEEN to not properly identify the rows to be deleted. (Bug #11853)

  • Within a stored procedure that selects from a table, invoking another procedure that requires a write lock for the table caused that procedure to fail with a message that the table was read-locked. (Bug #9565)

  • Within a stored procedure, selecting from a table through a view caused subsequent updates to the table to fail with a message that the table was read-locked. (Bug #9597)

  • For a stored procedure defined with SQL SECURITY DEFINER characteristic, CURRENT_USER() incorrectly reported the use invoking the procedure, not the user who defined it. (Bug #7291)

  • Creating a table with a SET or ENUM column with the DEFAULT 0 clause caused a server crash if the table's character set was utf8. (Bug #11819)

  • With strict SQL mode enabled, ALTER TABLE reported spurious “Invalid default value” messages for columns that had no DEFAULT clause. (Bug #9881)

  • In SQL prepared statements, comparisons could fail for values not equally space-padded. For example, SELECT 'a' = 'a '; returns 1, but PREPARE s FROM 'SELECT ?=?'; SET @a = 'a', @b = 'a '; PREPARE s FROM 'SELECT ?=?'; EXECUTE s USING @a, @b; incorrectly returned 0. (Bug #9379)

  • Labels in stored routines did not work if the character set was not latin1. (Bug #7088)

  • Invoking the DES_ENCRYPT() function could cause a server crash if the server was started without the --des-key-file option. (Bug #11643)

  • The server crashed upon execution of a statement that used a stored function indirectly (via a view) if the function was not yet in the connection-specific stored routine cache and the statement would update a Handler_xxx status variable. This fix allows the use of stored routines under LOCK TABLES without explicitly locking the mysql.lock table. However, you cannot use mysql.proc in statements that will combine locking of it with modifications for other tables. (Bug #11554)

  • The server crashed when dropping a trigger that invoked a stored procedure, if the procedure was not yet in the connection-specific stored routine cache. (Bug #11889)

  • Selecting the result of an aggregate function for an ENUM or SET column within a subquery could result in a server crash. (Bug #11821)

  • Incorrect column values could be retrieved from views defined using statements of the form SELECT * FROM tbl_name. (Bug #11771)

  • The mysql.proc table was not being created properly with the proper utf8 character set and collation, causing server crashes for stored procedure operations if the server was using a multi-byte character set. To take advantage of the bug fix, mysql_fix_privilege_tables should be run to correct the structure of the mysql.proc table. (Bug #11365)

    Note that it is necessary to run mysql_fix_privileges_tables when upgrading from a previous installation that contains the mysql.proc table (that is, from a previous 5.0 installation). Otherwise, creating stored procedures might not work.

  • Execution of a prepared statement that invoked a non-existent or dropped stored routine would crash the server. (Bug #11834)

  • Executing a statement that invoked a trigger would cause problems unless a LOCK TABLES was first issued for any tables accessed by the trigger. Note: The exact nature of the problem depended upon the MySQL 5.0 release being used: prior to 5.0.3, this resulted in a crash; from 5.0.3 to 5.0.7, MySQL would issue a warning; in 5.0.9, the server would issue an error. (Bug #8406)

    The same issue caused LOCK TABLES to fail following UNLOCK TABLES if triggers were involved. (Bug #9581)

  • In a shared Windows environment, MySQL could not find its configuration file unless the file was in the C:\ directory. (Bug #5354)

D.1.12. Changes in release 5.0.9 (15 July 2005)

Functionality added or changed:

  • An attempt to create a TIMESTAMP column with a display width (for example, TIMESTAMP(6)) now results in a warning. Display widths have not been supported for TIMESTAMP since MySQL 4.1. (Bug #10466)

  • InnoDB: When creating or extending an InnoDB data file, at most one megabyte at a time is allocated for initializing the file. Previously, InnoDB allocated and initialized 1 or 8 megabytes of memory, even if only a few 16-kilobyte pages were to be written. This improves the performance of CREATE TABLE in innodb_file_per_table mode.

  • InnoDB: Various optimizations. Removed unreachable debug code from non-debug builds. Added hints for the branch predictor in gcc. Made assertions occupy less space.

  • InnoDB: Make innodb_thread_concurrency=20 by default. Bypass the concurrency checking if the setting is greater than or equal to 20.

  • InnoDB: Make CHECK TABLE killable. (Bug #9730)

  • Recursion in stored routines is now disabled because it was crashing the server. We plan to modify stored routines to allow this to operate safely in a future release. (Bug #11394)

  • The handling of BIT columns has been improved, and should now be much more reliable in a number of cases. (Bug #10617, Bug #11091, Bug #11572)

  • mysql_real_escape_string() API function now respects NO_BACKSLASH_ESCAPES SQL mode. (Bug #10214)

Bugs fixed:

  • SHOW CREATE VIEW did not take the ANSI MODE into account when quoting identifiers. (Bug #6903)

  • The mysql_config script did not handle symbolic linking properly. (Bug #10986)

  • Incorrect results when using GROUP BY ... WITH ROLLUP on a VIEW. (Bug #11639)

  • Instances of the VAR_SAMP() function in view definitions were converted to VARIANCE(). This is incorrect because VARIANCE() is the same as VAR_POP(), not VAR_SAMP(). (Bug #10651)

  • mysqldump failed when reloading a view if the view was defined in terms of a different view that had not yet been reloaded. mysqldump now creates a dummy table to handle this case. (Bug #10927)

  • mysqldump could crash for illegal or non-existent table names. (Bug #9358)

  • The --no-data option for mysqldump was being ignored if table names were given after the database name. (Bug #9558)

  • The --master-data option for mysqldump resulted in no error if the binary log was not enabled. Now an error occurs unless the --force option is given. (Bug #11678)

  • DES_ENCRYPT() and DES_DECRYPT() require SSL support to be enabled, but were not checking for it. Checking for incorrect arguments or resource exhaustion was also improved for these functions. (Bug #10589)

  • When used in joins, SUBSTRING() failed to truncate to zero any string values that could not be converted to numbers. (Bug #10124)

  • mysqldump --xml did not format NULL column values correctly. (Bug #9657)

  • There was a compression algorithm issue with myisampack for very large datasets (where the total size of all records in a single column was on the order of 3 GB or more) on 64-bit platforms. (A fix for other platforms was made in MySQL 5.0.6.) (Bug #8321)

  • Temporary tables were created in the data directory instead of tmpdir. (Bug #11440)

  • MySQL would not compile correctly on QNX due to missing rint() function. (Bug #11544)

  • A SELECT DISTINCT col_name would work correctly with a MyISAM table only when there was an index on col_name. (Bug #11484)

  • The server would lose table-level CREATE VIEW and SHOW VIEW privileges following a FLUSH PRIVILEGES or server restart. (Bug #9795)

  • In strict mode, an INSERT into a view that did not include a value for a NOT NULL column but that did include a WHERE test on the same column would succeed, This happened even though the INSERT should have been prevented due to the failure to supply a value for the NOT NULL column. (Bug #6443)

  • Running a CHECK TABLES on multiple views crashed the server. (Bug #11337)

  • When a table had a primary key containing a BLOB column, creation of another index failed with the error BLOB/TEXT column used in key specification without keylength, even when the new index did not contain a BLOB column. (Bug #11657)

  • NDB Cluster: When trying to open a table that could not be discovered or unpacked, cluster would return error codes which the MySQL server falsely interpreted as operating system errors. (Bug #103651)

  • Manually inserting a row with host='' into mysql.tables_priv and performing a FLUSH PRIVILEGES would cause the server to crash. (Bug #11330)

  • A cursor using a query with a filter on a DATE or DATETIME column would cause the server to crash server after the data was fetched. (Bug #11172)

  • Closing a cursor that was already closed would cause MySQL to hang. (Bug #9814)

  • Using CONCAT_WS on a column set NOT NULL caused incorrect results when used in a LEFT JOIN. (Bug #11469)

  • Signed BIGINT would not accept -9223372036854775808 as a DEFAULT value. (Bug #11215)

  • Views did not use indexes on all appropriate queries. (Bug #10031)

  • For MEMORY tables, it was possible for updates to be performed using outdated key statistics when the updates involved only very small changes in a very few rows. This resulted in the random failures of queries such as UPDATE t SET col = col + 1 WHERE col_key = 2; where the same query with no WHERE clause would succeed. (Bug #10178)

  • Optimizer performed range check when comparing unsigned integers to negative constants, could cause errors. (Bug #11185)

  • Wrong comparison method used in VIEW when relaxed date syntax used (for example, 2005.06.10). (Bug #11325)

  • The ENCRYPT() and SUBSTRING_INDEX() functions would cause errors when used with a VIEW. (Bug #7024)

  • Clients would hang following some errors with stored procedures. (Bug #9503)

  • Combining cursors and subqueries could cause server crash or memory leaks. (Bug #10736)

  • If a prepared statement cursor is opened but not completely fetched, attempting to open a cursor for a second prepared statement will fail. (Bug #10794)

D.1.13. Changes in release 5.0.8 (Not released)

Note: Starting with version 5.0.8, changes for MySQL Cluster can be found in the combined Change History.

Functionality added or changed:

  • Warning: Incompatible change: Previously, 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. (Bug#12268)

  • MEMORY tables now support indexes of up to 500 bytes. See Section 14.4, “The MEMORY (HEAP) Storage Engine”. (Bug #10566)

  • New SQL_MODE - NO_ENGINE_SUBSTITUTION Prevents automatic substitution of storage engine when the requested storage engine is disabled or not compiled in. (Bug #6877)

  • The statements CREATE TABLE, TRUNCATE TABLE, DROP DATABASE, and CREATE DATABASE cause an implicit commit. (Bug #6883)

  • Expanded on information provided in general log and slow query log for prepared statements. (Bug #8367, Bug #9334)

  • Where a GROUP BY query uses a grouping column from the query's SELECT clause, MySQL now issues a warning. This is done because the SQL standard states that any grouping column must unambiguously reference a column of the table resulting from the query's FROM clause, and allowing columns from the SELECT clause to be used as grouping columns is a MySQL extension to the standard.

    By way of example, consider the following table:

    CREATE TABLE users (
      userid INT NOT NULL PRIMARY KEY,
      username VARCHAR(25),
      usergroupid INT NOT NULL
    );
    

    MySQL allows you to use the alias in this query:

    SELECT usergroupid AS id, COUNT(userid) AS number_of_users
    FROM users
    GROUP BY id;
    

    However, the SQL standard requires that the column name be used, as shown here:

    SELECT usergroupid AS id, COUNT(userid) AS number_of_users
    FROM users
    GROUP BY usergroupid;
    

    Queries such as the first of the two shown above will continue to be supported in MySQL; however, beginning with MySQL 5.0.8, using a column alias in this fashion will generate a warning. Note that in the event of a collision between column names and/or aliases used in joins, MySQL attempts to resolve the conflict by giving preference to columns arising from tables named in the query's FROM clause. (Bug #11211)

  • The granting or revocation of privileges on a stored routine is no longer performed when running the server with --skip-grant-tables even after the statement SET @@global.automatic_sp_privileges=1; has been executed. (Bug #9993)

  • Added support for B'10' syntax for bit literal. (Bug #10650)

Bugs fixed:

  • Security fix: On Windows systems, a user with any of the following privileges

    • REFERENCES

    • CREATE TEMPORARY TABLES

    • GRANT OPTION

    • CREATE

    • SELECT

    on *.* could crash mysqld by issuing a USE LPT1; or USE PRN; command. In addition, any of the commands USE NUL;, USE CON;, USE COM1;, or USE AUX; would report success even though the database was not in fact changed. Note: Although this bug was thought to be fixed previously, it was later discovered to be present in the MySQL 5.0.7-beta release for Windows. (Bug #9148, CAN-2005-0799

  • A CREATE TABLE db_name.tbl_name LIKE ... statement would crash the server when no database was selected. (Bug #11028)

  • SELECT DISTINCT queries or GROUP BY queries without MIN() or MAX() could return inconsistent results for indexed columns. (Bug #11044)

  • The SHOW INSTANCE OPTIONS command in MySQL Instance Manager displayed option values incorrectly for options for which no value had been given. (Bug #11200)

  • An outer join with an empty derived table (a result from a subquery) returned no result. (Bug #11284)

  • An outer join with an ON condition that evaluated to false could return an incorrect result. (Bug #11285)

  • mysqld_safe would sometimes fail to remove the pid file for the old mysql process after a crash. As a result, the server would fail to start due to a false A mysqld process already exists... error. (Bug #11122)

  • CAST( ... AS DECIMAL) didn't work for strings. (Bug #11283)

  • NULLIF() function could produce incorrect results if first argument is NULL. (Bug #11142)

  • Setting @@SQL_MODE = NULL caused an erroneous error message. (Bug #10732)

  • Converting a VARCHAR column having an index to a different type (such as TINYTEXT) gave rise to an incorrect error message. (Bug #10543)

    Note that this bugfix induces a slight change in the behavior of indexes: If an index is defined to be the same length as a field (or is left to default to that field's length), and the length of the field is later changed, then the index will adopt the new length of the field. Previously, the size of the index did not change for some field types (such as VARCHAR) when the field type was changed.

  • sql_data_access column of routines table of INFORMATION_SCHEMA was empty. (Bug #11055)

  • A CAST() value could not be included in a VIEW. (Bug #11387)

  • Server crashed when using GROUP BY on the result of a DIV operation on a DATETIME value. (Bug #11385)

  • Possible NULL values in BLOB columns could crash the server when a BLOB was used in a GROUP BY query. (Bug #11295)

  • Fixed 64 bit compiler warning for packet length in replication. (Bug #11064)

  • Multiple range accesses in a subquery cause server crash. (Bug #11487)

  • An issue with index merging could cause suboptimal index merge plans to be chosen when searching by indexes created on DATE columns. The same issue caused the InnoDB storage engine to issue the warning using a partial-field key prefix in search. (Bug #8441)

  • The mysqlhotcopy script was not parsing the output of SHOW SLAVE STATUS correctly when called with the --record_log_pos option. (Bug #7967)

  • SELECT * FROM table returned incorrect results when called from a stored procedure, where table had a primary key. (Bug #10136)

  • When used in defining a view, the TIME_FORMAT() function failed with calculated values, for example, when passed the value returned by SEC_TO_TIME(). (Bug #7521)

  • SELECT DISTINCT ... GROUP BY constant returned multiple rows (it should return a single row). (Bug #8614)

  • INSERT INTO SELECT FROM view produced incorrect result when using ORDER BY. (Bug #11298)

  • Fixed hang/crash with Boolean full-text search where a query contained more query terms that one-third of the query length (it could be achieved with truncation operator: 'a*b*c*d*'). (Bug #7858)

  • Fixed column name generation in VIEW creation to ensure there are no duplicate column names. (Bug #7448)

  • An ORDER BY clause sometimes had no effect on the ordering of a result when selecting specific columns (as opposed to using SELECT *) from a view. (Bug #7422)

  • Some data definition statements (CREATE TABLE where the table was not a temporary table, TRUNCATE TABLE, DROP DATABASE, and CREATE DATABASE) were not being written to the binary log after a ROLLBACK. This also caused problems with replication. (Bug #6883)

  • Calling a stored procedure that made use of an INSERT ... SELECT ... UNION SELECT ... query caused a server crash. (Bug #11060)

  • Selecting from a view defined using SELECT SUM(DISTINCT ...) caused an error; attempting to execute a SELECT * FROM INFORMATION_SCHEMA.TABLES query after defining such a view crashed the server. (Bug #7015)

  • The mysql client would output a prompt twice following input of very long strings, because it incorrectly assumed that a call to the _cgets() function would clear the input buffer. (Bug #10840)

  • A three byte buffer overflow in the client functions caused improper exiting of the client when reading a command from the user. (Bug #10841)

  • Fixed a problem where a stored procedure caused a server crash if the query cache was enabled. (Bug #9715)

  • SHOW CREATE DATABASE INFORMATION_SCHEMA returned an “unknown database” error. (Bug #9434)

  • Corrected a problem with IFNULL() returning an incorrect result on 64-bit systems. (Bug #11235)

  • Fixed a problem resolving table names with lower_case_table_names=2 when the table name lettercase differed in the FROM and WHERE clauses. (Bug #9500)

  • Fixed server crash due to some internal functions not taking into account that for multi-byte character sets, CHAR columns could exceed 255 bytes and VARCHAR columns could exceed 65,535 bytes. (Bug #11167)

  • Fixed locking problems for multiple-statement DELETE statements performed within a stored routine, such as incorrectly locking a to-be-modified table with a read lock rather than a write lock. (Bug #11158)

  • Fixed a portability problem testing for crypt() support that caused compilation problems when using OpenSSL/yaSSL on HP-UX and Mac OS X. (Bug #10675, Bug #11150)

  • The hostname cache was not working. (Bug #10931)

  • On Windows, mysqlshow did not interpret wildcard characters properly if they were given in the table name argument. (Bug #10947)

  • The default hostname for MySQL server was always mysql. (Bug #11174)

  • Using PREPARE to prepare a statement that invoked a stored routine that deallocated the prepared statement caused a server crash. This is prevented by disabling dynamic SQL within stored routines. (Bug #10975) (Note: This restriction was lifted in 5.0.13 for stored procedures, but not stored functions or triggers.)

  • Using PREPARE to prepare a statement that invoked a stored routine that executed the prepared statement caused a Packets out of order error the second time the routine was invoked. This is prevented by disabling dynamic SQL within stored routines. (Bug #7115) (Note: This restriction was lifted in 5.0.13 for stored procedures, but not stored functions or triggers.)

  • Using prepared statements within a stored routine (PREPARE, EXECUTE, DEALLOCATE) could cause the client connection to be dropped after the routine returned. This is prevented by disabling dynamic SQL within stored routines. (Bug #10605) (Note: This restriction was lifted in 5.0.13 for stored procedures, but not stored functions or triggers.)

  • When using a cursor with a prepared statement, the first execution returned the correct result but was not cleaned up properly, causing subsequent executions to return incorrect results. (Bug #10729)

  • MySQL Cluster: Connections between data nodes and management nodes were not being closed following shutdown of ndb_mgmd. (Bug #11132)

  • MySQL Cluster: mysqld processes would not reconnect to cluster following restart of ndb_mgmd. (Bug #11221)

  • MySQL Cluster: Fixed problem whereby data nodes would fail to restart on 64-bit Solaris (Bug #9025)

  • MySQL Cluster: Calling ndb_select_count() crashed the cluster when running on Red Hat Enterprise 4/64-bit/Opteron. (Bug #10058)

  • MySQL Cluster: Insert records were incorrectly applied by ndb_restore, thus making restoration from backup inconsistent if the binlog contained inserts. (Bug #11166)

  • MySQL Cluster: Cluster would time out and crash after first query on 64-bit Solaris 9. (Bug #8918)

  • MySQL Cluster: ndb_mgm client show command displayed incorrect output after master data node failure. (Bug #11050)

  • MySQL Cluster: A delete performed as part of a transaction caused an erroneous result. (Bug #11133)

  • MySQL Cluster: Not allowing sufficient parallelism in cluster configuration (for example, NoOfTransactions too small) caused ndb_restore to fail without providing any error messages. (Bug #10294)

  • MySQL Cluster: When using dynamically allocated ports on Linux, cluster would hang on initial startup. (Bug #10893)

  • MySQL Cluster: Setting TransactionInactiveTimeout= 0 did not result in an infinite timeout. (Bug #11290)

  • InnoDB: Enforce maximum CHAR_LENGTH() of UTF-8 data in ON UPDATE CASCADE. (Bug #10409)

  • InnoDB: Pad UTF-8 VARCHAR columns with 0x20. Pad UCS2 CHAR columns with 0x0020. (Bug #10511)

D.1.14. Changes in release 5.0.7 (10 June 2005)

Functionality added or changed:

  • Security improvement: Applied a patch to fix a UDF library-loading vulnerability that could result in a buffer overflow and code execution. (http://www.appsecinc.com/resources/alerts/mysql/2005-002.html)

  • Added mysql_set_character_set() C API function for setting the default character set of the current connection. This allows clients to affect the character set used by mysql_real_escape_string(). (Bug #8317)

  • The behavior of the Last_query_cost system variable has been changed. The default value is now 0 (rather than -1) and it now has session-level scope (rather than being global). See Section 5.2.4, “Server Status Variables”, for additional information.

  • All characters occurring on the same line following the DELIMITER keyword will be set as delimiter. For example, DELIMITER :; will set :; as the delimiter. This behavior is now consistent between MySQL 5.1 and MySQL 5.0. (Bug #9879)

  • The table, type, and rows columns of EXPLAIN output can now be NULL. This is required for using EXPLAIN on SELECT queries that use no tables (for example, EXPLAIN SELECT 1). (Bug #9899)

  • Placeholders now can be used for LIMIT in prepared statements. (Bug #7306)

  • SHOW BINARY LOGS now displays a File_size column that indicates the size of each file.

  • The --delayed-insert option for mysqldump has been disabled to avoid causing problems with storage engines that do not support INSERT DELAYED. (Bug #7815)

  • Improved the optimizer to be able to use indexes for expressions of the form indexed_col NOT IN (val1, val2, ...) and indexed_col NOT BETWEEN val1 AND val2.. (Bug #10561)

  • Removed mysqlshutdown.exe and mysqlwatch.exe from the Windows “No Installer” distribution (they had already been removed from the “With Installer” distribution before). Removed those programs from the source distribution.

  • Removed WinMySQLAdmin from the source distribution and from the “No Installer” Windows distribution (it had already been removed from the “With Installer” distribution before).

  • InnoDB: In stored procedures and functions, InnoDB no longer takes full explicit table locks for every involved table. Only `intention' locks are taken, similar to those in the execution of an ordinary SQL statement. This greatly reduces the number of deadlocks.

Bugs fixed:

  • Security update: A user with limited privileges could obtain information about the privileges of other users by querying objects in the INFORMATION_SCHEMA database for which that user did not have the requisite privileges. (Bug #10964)

  • Triggers with dropped functions caused crashes. (Bug #5893)

  • Failure of a BEFORE trigger did not prevent the triggering statement from performing its operation on the row for which the trigger error occurred. Now the triggering statement fails as described in Section 18.3, “Using Triggers”. (Bug #10902)

  • Issuing a write lock for a table from one client prevented other clients from accessing the table's metadata. For example, if one client issued a LOCK TABLES mydb.mytable WRITE, then a second client attempting to execute a USE mydb; would hang. (Bug #9998)

  • The LAST_DAY() failed to return NULL when supplied with an invalid argument. See Section 12.5, “Date and Time Functions”. (Bug #10568)

  • The functions COALESCE(), IF(), and IFNULL() performed incorrect conversions of their arguments. (Bug #9939)

  • The TIME_FORMAT() function returned incorrect results with some format specifiers. See Section 12.5, “Date and Time Functions”. (Bug #10590)

  • Dropping stored routines when the MySQL server had been started with --skip-grant-tables generated extraneous warnings. (Bug #9993)

  • A problem with the my_global.h file caused compilation of MySQL to fail on single-processor Linux systems running 2.6 kernels. (Bug #10364)

  • The ucs2_turkish_ci collation failed with upper('i'). UPPER/LOWER now can return a string with different length. (Bug #8610)

  • OPTIMIZE of InnoDB table does not return 'Table is full' if out of tablespace. (Bug #8135)

  • GROUP BY queries with ROLLUP returned wrong results for expressions containing group by columns. (Bug #7894)

  • Fixed bug in FIELD() function where value list contains NULL. (Bug #10944)

  • Corrected a problem where an incorrect data type was returned in the result set metadata when using a prepared SELECT DISTINCT statement to select from a view. (Bug #11111)

  • Fixed bug in the MySQL Instance manager that caused the version to always be unknown when SHOW INSTANCE STATUS was issued. (Bug #10229)

  • Using ORDER BY to sort the results of an IF() that contained a FROM_UNIXTIME() expression returned incorrect results due to integer overflow. (Bug #9669)

  • Fixed a server crash resulting from accessing InnoDB tables within stored functions. This is handled by prohibiting statements that do an implicit or explicit commit or rollback within stored functions or triggers. (Bug #10015)

  • Fixed a server crash resulting from the second invocation of a stored procedure that selected from a view defined as a join that used ON in the join conditions. (Bug #6866)

  • Using ALTER TABLE for a table that had a trigger caused a crash when executing a statement that activated the trigger, and also a crash later with USE db_name for the database containing the table. (Bug #5894)

  • Fixed a server crash resulting from an attempt to allocate too much memory when GROUP BY blob_col and COUNT(DISTINCT) were used. (Bug #11088)

  • Fixed a portability problem for compiling on Windows with Visual Studio 6. (Bug #11153)

  • The incorrect sequence of statements HANDLER tbl_name READ index_name NEXT without a preceding HANDLER tbl_name READ index_name = (value_list) for an InnoDB table resulted in a server crash rather than an error. (Bug #5373)

  • On Windows, with lower_case_table_names set to 2, using ALTER TABLE to alter a MEMORY or InnoDB table that had a mixed-case name also improperly changed the name to lowercase. (Bug #9660)

  • The server timed out SSL connections too quickly on Windows. (Bug #8572)

  • Executing LOAD INDEX INTO CACHE for a table while other threads where selecting from the table caused a deadlock. (Bug #10602)

  • Fixed a server crash resulting from CREATE TABLE ... SELECT that selected from a table being altered by ALTER TABLE. (Bug #10224)

  • The FEDERATED storage engine properly handled outer joins, but not inner joins. (Bug #10848)

  • Consistently report INFORMATION_SCHEMA table names in uppercase in SHOW TABLE STATUS output. (Bug #10059)

  • Fixed a failure of WITH ROLLUP to sum values properly. (Bug #10982)

  • Triggers were not being activated for multiple-table UPDATE or DELETE statements. (Bug #5860)

  • INSERT BEFORE triggers were not being activated for INSERT ... SELECT statements. (Bug #6812)

  • INSERT BEFORE triggers were not being activated for implicit inserts (LOAD DATA). (Bug #8755)

  • If a stored function contained a FLUSH statement, the function crashed when invoked. FLUSH now is disallowed within stored functions. (Bug #8409)

  • Multiple-row REPLACE could fail on a duplicate-key error when having one AUTO_INCREMENT key and one unique key. (Bug #11080)

  • Fixed a server crash resulting from invalid string pointer when inserting into the mysql.host table. (Bug #10181)

  • Multiple-table DELETE did always delete on the fly from the first table that was to be deleted from. In some cases, when using many tables and it was necessary to access the same row twice in the first table, we could miss some rows-to-be-deleted from other tables. This is now fixed.

  • The mysql_next_result() function could hang if you were executing many statements in a mysql_real_query() call and one of those statements raised an error. (Bug #9992)

  • The combination of COUNT(), DISTINCT, and CONCAT() sometimes triggered a memory deallocation bug on Windows resulting in a server crash. (Bug #9593)

  • InnoDB: Do very fast shutdown only if innodb_fast_shutdown=2, but wait for threads to exit and release allocated memory if innodb_fast_shutdown=1. Starting with MySQL/InnoDB 5.0.5, InnoDB would do brutal shutdown also when innodb_fast_shutdown=1. (Bug #9673)

  • InnoDB: Fixed InnoDB: Error: stored_select_lock_type is 0 inside ::start_stmt()! in a stored procedure call if innodb_locks_unsafe_for_binlog was set in my.cnf. (Bug #10746)

  • InnoDB: Fixed a duplicate key error that occurred with REPLACE in a table with an AUTO-INC column. (Bug #11005)

  • MySQL would pass an incorrect key length to storage engines for MIN(). This could cause warnings InnoDB: Warning: using a partial-field key prefix in search. in the .err log. (Bug #11039, same as Bug #13218 in MySQL 4.1.15)

  • Fixed a server crash for INSERT or UPDATE when the WHERE clause contained a correlated subquery that referred to a column of the table being modified. (Bug #6384)

  • Fixed a problem causing an incorrect result for columns that include an aggregate function as part of an expression when WITH ROLLUP is added to GROUP BY. (Bug #7914)

  • Fixed a problem with returning an incorrect result from a view that selected a COALESCE() expression from the result of an outer join. (Bug #9938)

  • MySQL was adding a DEFAULT clause to ENUM columns that included no explicit DEFAULT and were defined as NOT NULL. (This is supposed to happen only for columns that are NULL.) (Bug #6267)

  • Corrected inappropriate error messages that were displayed when attempting to set the read-only warning_count and error_count system variables. (Bug #10339)

D.1.15. Changes in release 5.0.6 (26 May 2005)

Functionality added or changed:

  • Incompatible change: MyISAM and InnoDB tables created with DECIMAL columns in MySQL 5.0.3 to 5.0.5 will appear corrupt after an upgrade to MySQL 5.0.6. Dump such tables with mysqldump before upgrading, and then reload them after upgrading. (The same incompatibility will occur for these tables created in MySQL 5.0.6 after a downgrade to MySQL 5.0.3 to 5.0.5.) (Bug #10465, Bug #10625)

  • The precision of the DECIMAL data type has been increased from 64 to 65 decimal digits.

  • Added the div_precision_increment system variable, which indicates the number of digits of precision by which to increase the result of division operations performed with the / operator.

  • Added the log_bin_trust_routine_creators system variable, which applies when binary logging is enabled. It controls whether stored routine creators can be trusted not to create stored routines that will cause unsafe events to be written to the binary log.

  • Added the --log-bin-trust-routine-creators server option for setting the log_bin_trust_routine_creators system variable from the command line.

  • Implemented the STMT_ATTR_PREFETCH_ROWS option for the mysql_stmt_attr_set() C API function. This sets how many rows to fetch at a time when using cursors with prepared statements.

  • The GRANT and REVOKE statements now support an object_type clause to be used for disambiguating whether the grant object is a table, a stored function, or a stored procedure. Use of this clause requires that you upgrade your grant tables. See Section 5.6.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. (Bug #10246)

  • Added REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME columns to the KEY_COLUMN_USAGE table of INFORMATION_SCHEMA. (Bug #9587)

  • Added a --show-warnings option to mysql to cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode. In interactive mode, \w and \W may be used to enable and disable warning display. (Bug #8684)

  • Removed a limitation that prevented use of FIFOs as logging targets (such as for the general query log). This modification does not apply to the binary log and the relay log. (Bug #8271)

  • Added a --debug option to my_print_defaults.

  • When the server cannot read a table because it cannot read the .frm file, print a message that the table was created with a different version of MySQL. (This can happen if you create tables that use new features and then downgrade to an older version of MySQL.) (Bug #10435)

  • SHOW VARIABLES now shows the slave_compressed_protocol, slave_load_tmpdir and slave_skip_errors system variables. (Bug #7800)

  • Removed unused system variable myisam_max_extra_sort_file_size.

  • Changed default value of myisam_data_pointer_size from 4 to 6. This allows us to avoid table is full errors for most cases.

  • The variable concurrent_insert now takes 3 values. Setting this to 2 changes MyISAM to do concurrent inserts to end of table if table is in use by another thread.

  • New /*> prompt for mysql. This prompt indicates that a /* ... */ comment was begun on an earlier line and the closing */ sequence has not yet been seen. (Bug #9186)

  • If strict SQL mode is enabled, VARCHAR and VARBINARY columns with a length greater than 65,535 no longer are silently converted to TEXT or BLOB columns. Instead, an error occurs. (Bug #8295, Bug #8296)

  • The INFORMATION_SCHEMA.SCHEMATA table now has a DEFAULT_COLLATION_NAME column. (Bug #8998)

  • InnoDB: When the maximum length of SHOW INNODB STATUS output would be exceeded, truncate the beginning of the list of active transactions, instead of truncating the end of the output. (Bug #5436)

  • InnoDB: If innodb_locks_unsafe_for_binlog option is set and the isolation level of the transaction is not set to serializable then InnoDB uses a consistent read for select in clauses like INSERT INTO ... SELECT and UPDATE ... (SELECT) that do not specify FOR UPDATE or IN SHARE MODE. Thus no locks are set to rows read from selected table.

  • Updated version of libedit to 2.9. (Bug #2596)

  • Removed mysqlshutdown.exe and mysqlwatch.exe from the Windows “With Installer” distribution.

Bugs fixed:

  • An error in the implementation of the MyISAM compression algorithm caused myisampack to fail with very large sets of data (total size of all the records in a single column needed to be >= 3 GB in order to trigger this issue). (Bug #8321)

  • Statements that create and use stored routines were not being written to the binary log, which affects replication and data recovery options. (Bug #2610) Stored routine-related statements now are logged, subject to the issues and limitations discussed in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

  • Disabled binary logging within stored routines to avoid writing spurious extra statements to the binary log. For example, if a routine p() executes an INSERT statement, then for CALL p(), the CALL statement appears in the binary log, but not the INSERT statement. (Bug #9100)

  • Statements that create and drop triggers were not being written to the binary log, which affects replication and data recovery options. (Bug #10417) Trigger-related statements now are logged, subject to the issues and limitations discussed in Section 17.4, “Binary Logging of Stored Routines and Triggers”.

  • The mysql_stmt_execute() and mysql_stmt_reset() C API functions now close any cursor that is open for the statement, which prevents a server crash. (Bug #9478)

  • The mysql_stmt_attr_set() C API function now returns an error for option values that are defined in mysql.h but not yet implemented, such as CURSOR_TYPE_SCROLLABLE. (Bug #9643)

  • MERGE tables could fail on Windows due to incorrect interpretation of pathname separator characters for filenames in the .MRG file. (Bug #10687)

  • Fixed a server crash for INSERT ... ON DUPLICATE KEY UPDATE with MERGE tables, which do not have unique indexes. (Bug #10400)

  • Fix FORMAT() to do better rounding for double values (for example, FORMAT(4.55,1) returns 4.6, not 4.5). (Bug #9060)

  • Disallow use of SESSION or GLOBAL for user variables or local variables in stored routines. (Bug #9286)

  • Fixed a server crash when using GROUP BY ... WITH ROLLUP on an indexed column in an InnoDB table. (Bug #9798)

  • In strict SQL mode, some assignments to numeric columns that should have been rejected were not (such as the result of an arithmetic expression or an explicit CAST() operation). (Bug #6961)

  • CREATE TABLE t AS SELECT UUID() created a VARCHAR(12) column, which is too small to hold the 36-character result from UUID(). (Bug #9535)

  • Fixed a server crash in the BLACKHOLE storage engine. (Bug #10175)

  • Fixed a server crash resulting from repeated calls to ABS() when the argument evaluated to NULL. (Bug #10599)

  • For a user-defined function invoked from within a prepared statement, the UDF's initialization routine was invoked for each execution of the statement, but the deinitialization routine was not. (It was invoked only when the statement was closed.) Similarly, when invoking a UDF from within a trigger, the initialization routine was invoked but the deinitialization routine was not. For UDFs that have an expensive deinit function (such as myperl, this bugfix will have negative performance consequences. (Bug #9913)

  • Portability fix for Cygwin: Don't use #pragma interface in source files. (Bug #10241)

  • Fix CREATE TABLE ... LIKE to work when lower_case_table_names is set on a case-sensitive filesystem and the source table name is not given in lowercase. (Bug #9761)

  • Fixed a server crash resulting from a CHECK TABLE statement where the arguments were a view name followed by a table name. (Bug #9897)

  • Within a stored procedure, attempting to update a view defined as an inner join failed with a Table 'tbl_name' was locked with a READ lock and can't be updated error. (Bug #9481)

  • Fixed a problem with INFORMATION_SCHEMA tables being inaccessible depending on lettercase used to refer to them. (Bug #10018)

  • my_print_defaults was ignoring the --defaults-extra-file option or crashing when the option was given. (Bug #9136, Bug #9851)

  • The INFORMATION_SCHEMA.COLUMNS table was missing columns of views for which the user has access. (Bug #9838)

  • Fixed a mysqldump crash that occurred with the --complete-insert option when dumping tables with a large number of long column names. (Bug #10286)

  • Corrected a problem where DEFAULT values where not assigned properly to BIT(1) or CHAR(1) columns if certain other columns preceded them in the table definition. (Bug #10179)

  • For MERGE tables, avoid writing absolute pathnames in the .MRG file for the names of the constituent MyISAM tables so that if the data directory is moved, MERGE tables will not break. For mysqld, write just the MyISAM table name if it is in the same database as the MERGE table, and a path relative to the data directory otherwise. For the embedded servers, absolute pathnames may still be used. (Bug #5964)

  • Corrected a problem resolving outer column references in correlated subqueries when using the prepared statements. (Bug #10041)

  • Corrected the error message for exceeding the MAX_CONNECTIONS_PER_HOUR limit to say max_connections_per_hour instead of max_connections. (Bug #9947)

  • Fixed incorrect memory block allocation for the query cache in the embedded server. (Bug #9549)

  • Corrected an inability to select from a view within a stored procedure. (Bug #9758)

  • Fixed a server crash resulting from use of AVG(DISTINCT) with GROUP BY ... WITH ROLLUP. (Bug #9799)

  • Fixed a server crash resulting from use of DISTINCT AVG() with GROUP BY ... WITH ROLLUP. (Bug #9800)

  • Fixed a server crash resulting from use of a CHAR or VARCHAR column with MIN() or MAX() and GROUP BY ... WITH ROLLUP. (Bug #9820)

  • Fixed a server crash resulting from use of SELECT DISTINCT with a prepared statement that uses a cursor. (Bug #9520)

  • Fixed server crash resulting from multiple calls to a stored procedure that assigned the result of a subquery to a variable or compared it to a value with IN. (Bug #5963)

  • Selecting from a single-table view defined on multiple-table views caused a server crash. (Bug #8528)

  • If the file named by a --defaults-extra-file option does not exist or is otherwise inaccessible, an error now occurs. (Bug #5056)

  • net_read_timeout and net_write_timeout were not being respected on Windows. (Bug #9721)

  • SELECT from INFORMATION_SCHEMA tables failed if the statement has a GROUP BY clause and an aggregate function in the select list. (Bug #9404)

  • Corrected some failures of prepared statements for SQL (PREPARE plus EXECUTE) to return all rows for some SELECT statements. (Bug #9096, Bug #9777)

  • Remove extra slashes in --tmpdir value (for example, convert /var//tmp to /var/tmp, because they caused various errors. (Bug #8497)

  • Added Create_routine_priv, Alter_routine_priv, and Execute_priv privileges to the mysql.host privilege table. (They had been added to mysql.db in MySQL 5.0.3 but not to the host table.) (Bug #8166)

  • Fixed configure to properly recognize whether NTPL is available on Linux. (Bug #2173)

  • Incomplete results were returned from INFORMATION_SCHEMA.COLUMNS for INFORMATION_SCHEMA tables for non-root users. (Bug #10261)

  • Fixed a portability problem in compiling mysql.cc with VC++ on Windows. (Bug #10245)

  • SELECT 0/0 returned 0 rather than NULL. (Bug #10404)

  • MAX() for an INT UNSIGNED (unsigned 4-byte integer) column could return negative values if the column contained values larger than 231. (Bug #9298)

  • SHOW CREATE VIEW got confused and could not find the view if there was a temporary table with the same name as the view. (Bug #8921)

  • Fixed a deadlock resulting from use of FLUSH TABLES WITH READ LOCK while an INSERT DELAYED statement is in progress. (Bug #7823)

  • The optimizer was choosing suboptimal execution plans for certain outer joins where the right table of a left join (or left table of a right join) had both ON and WHERE conditions. (Bug #10162)

  • RENAME TABLE for an ARCHIVE table failed if the .arn file was not present. (Bug #9911)

  • Invoking a stored function that executed a SHOW statement resulted in a server crash. (Bug #8408)

  • Fixed problems with static variables and do not link with libsupc++ to allow building on FreeBSD 5.3. (Bug #9714)

  • Fixed some awk script portability problems in cmd-line-utils/libedit/makelist.sh. (Bug #9954)

  • Fixed a problem with mishandling of NULL key parts in hash indexes on VARCHAR columns, resulting in incorrect query results. (Bug #9489, Bug #10176)

  • InnoDB: Fixed a critical bug in InnoDB AUTO_INCREMENT: it could assign the same value for several rows. (Bug #10359) InnoDB: All InnoDB bug fixes from 4.1.12 and earlier versions, and also the fixes to bugs #10335 and #10607 listed in the 4.1.13 change notes.

D.1.16. Changes in release 5.0.5 (Not released)

No public release of MySQL 5.0.5 was made. The changes described in this section are available in MySQL 5.0.6.

Functionality added or changed:

  • Added support for the BIT data type to the MEMORY, InnoDB, and BDB storage engines.

  • SHOW VARIABLES no longer displays the deprecated log_update system variable. (Bug #9738)

  • The behavior controlled by the --innodb-fast-shutdown option now can be changed at runtime by setting the value of the global innodb_fast_shutdown system variable. It now accepts values 0, 1 and 2 (except on Netware where 2 is disabled). If set to 2, then when the MySQL server shuts down, InnoDB will just flush its logs and shut down brutally (and quickly) as if a MySQL crash had occurred; no committed transaction will be lost, but a crash recovery will be done at next startup.

Bugs fixed:

  • Security fix: If mysqld was started with --user=non_existent_user, it would run using the privileges of the account it was invoked from, even if that was root. (Bug #9833)

  • Corrected a failure to resolve a column reference correctly for a LEFT JOIN that compared a join column to an IN subquery. (Bug #9338)

  • Fixed a problem where, after an internal temporary table in memory became too large and had to be converted to an on-disk table, the error indicator was not cleared and the query failed with error 1023 (Can't find record in ''). (Bug #9703)

  • Multiple-table updates could produce spurious data-truncation warnings if they used a join across columns that are indexed using a column prefix. (Bug #9103)

  • Fixed a string-length comparison problem that caused mysql to fail loading dump files containing certain ‘\’-sequences. (Bug #9756)

  • Fixed a failure to resolve a column reference properly when an outer join involving a view contained a subquery and the column was used in the subquery and the outer query. (Bug #6106, Bug #6107)

  • Use of a subquery that used WITH ROLLUP in the FROM clause of the main query sometimes resulted in a Column cannot be null error. (Bug #9681)

  • Fixed a memory leak that occurred when selecting from a view that contained a subquery. (Bug #10107)

  • Fixed an optimizer bug in computing the union of two ranges for the OR operator. (Bug #9348)

  • Fixed a segmentation fault in mysqlcheck that occurred when the last table checked in --auto-repair mode returned an error (such as the table being a MERGE table). (Bug #9492)

  • SET @var= CAST(NULL AS [INTEGER|CHAR]) now sets the result type of the variable to INTEGER/CHAR. (Bug #6598)

  • Incorrect results were returned for queries of the form SELECT ... LEFT JOIN ... WHERE EXISTS (subquery), where the subquery selected rows based on an IS NULL condition. (Bug #9516)

  • Executing LOCK TABLES and then calling a stored procedure caused an error and resulting in the server thinking that no stored procedures exist. (Bug #9566)

  • Selecting from a view containing a subquery caused the server to hang. (Bug #8490)

  • Within a stored procedure, attempting to execute a multiple-table UPDATE failed with a Table 'tbl_name' was locked with a READ lock and can't be updated error. (Bug #9486)

  • Starting mysqld with the --skip-innodb and --default-storage-engine=innodb (or --default-table-type=innodb caused a server crash. (Bug #9815)

  • Queries containing CURRENT_USER() incorrectly were registered in the query cache. (Bug #9796)

  • Setting the storage_engine system variable to MEMORY succeeded, but retrieving the variable resulted in a value of HEAP (the old name for the MEMORY storage engine) rather than MEMORY. (Bug #10039)

  • mysqlshow displayed an incorrect row count for tables. (Bug #9391)

  • The server died with signal 11 if a non-existent location was specified for the location of the binary log. Now the server exits after printing an appropriate error message. (Bug #9542)

  • Fixed a problem in the client/server protocol where the server closed the connection before sending the final error message. The problem could show up as a Lost connection to MySQL server during query when attempting to connect to access a non-existent database. (Bug #6387, Bug #9455)

  • Fixed a readline-related crash in mysql when the user pressed Control-R. (Bug #9568)

  • For stored functions that should return a YEAR value, corrected a failure of the value to be in YEAR format. (Bug #8861)

  • Fixed a server crash resulting from invocation of a stored function that returned a value having an ENUM or SET data type. (Bug #9775)

  • Fixed a server crash resulting from invocation of a stored function that returned a value having a BLOB data type. (Bug #9102)

  • Fixed a server crash resulting from invocation of a stored function that returned a value having a BIT data type. (Bug #7648)

  • TIMEDIFF() with a negative time first argument and positive time second argument produced incorrect results. (Bug #8068)

  • Fixed a problem with OPTIMIZE TABLE for InnoDB tables being written twice to the binary log. (Bug #9149)

  • InnoDB: Prevent ALTER TABLE from changing the storage engine if there are foreign key constraints on the table. (Bug #5574, Bug #5670)

  • InnoDB: Fixed a bug where next-key locking doesn't allow the insert which does not produce a phantom. (Bug #9354) If the range is of type 'a' <= uniquecolumn, InnoDB lock only the RECORD, if the record with the column value 'a' exists in a CLUSTERED index. This allows inserts before a range.

  • InnoDB: When FOREIGN_KEY_CHECKS=0, ALTER TABLE and RENAME TABLE will ignore any type incompatibilities between referencing and referenced columns. Thus, it will be possible to convert the character sets of columns that participate in a foreign key. Be sure to convert all tables before modifying any data! (Bug #9802)

  • Provide more informative error messages in clustered setting when a query is issued against a table that has been modified by another mysqld server. (Bug #6762)

D.1.17. Changes in release 5.0.4 (16 Apr 2005)

Functionality added or changed:

  • Added ENGINE=MyISAM table option when creating mysql.proc table in mysql_create_system_tables script to make sure the table is created as a MyISAM table even if the default storage engine has been changed. (Bug #9496)

  • SHOW CREATE TABLE for an INFORMATION_SCHEMA table no longer prints a MAX_ROWS value because the value has no meaning. (Bug #8941)

  • Invalid DEFAULT values for CREATE TABLE now generate errors. (Bug #5902)

  • Added --show-table-type option to mysqlshow, to display a column indicating the table type, as in SHOW FULL TABLES. (Bug #5036)

  • The way the time zone information is stored in the binary log was changed, so that it is now possible to have a replication master and slave running with different global time zones. A drawback is that replication from 5.0.4 masters to pre-5.0.4 slaves is impossible.

  • Added --with-big-tables compilation option to configure. (Previously it was necessary to pass -DBIG_TABLES to the compiler manually in order to enable large table support.) See Section 2.8.2, “Typical configure Options”, for details.

  • New configuration directives !include and !includedir implemented for including option files and searching directories for option files. See Section 4.3.2, “Using Option Files”, for usage.

Bugs fixed:

  • The use of XOR together with NOT ISNULL() erroneously resulted in some outer joins being converted to inner joins by the optimizer. (Bug #9017)

  • Fixed an optimizer problem where extraneous comparisons between NULL values in indexed columns were being done for operators such as = that are never true for NULL. (Bug #8877)

  • Fixed the client/server protocol for prepared statements so that reconnection works properly when the connection is killed while reconnect is enabled. (Bug #8866)

  • A server installed as a Windows service and started with --shared-memory could not be stopped. (Bug #9665)

  • Fixed a server crash resulting from multiple executions of a prepared statement involving a join of an INFORMATION_SCHEMA table with another table. (Bug #9383)

  • Fixed utf8_spanish2_ci and ucs2_spanish2_ci collations to not consider ‘r’ equal to ‘rr’. If you upgrade to this version from an earlier version, you should rebuild the indexes of affected tables. (Bug #9269)

  • mysqldump dumped core when invoked with --tmp and --single-transaction options and a non-existent table name. (Bug #9175)

  • Allow extra HKSCS and cp950 characters (big5 extension characters) to be accepted in big5 columns. (Bug #9357)

  • mysql.server no longer uses non-portable alias command or LSB functions. (Bug #9852)

  • Fixed a server crash resulting from GROUP BY on a decimal expression. (Bug #9210)

  • In prepared statements, subqueries containing parameters were erroneously treated as const tables during preparation, resulting in a server crash. (Bug #8807)

  • InnoDB: ENUM and SET columns were treated incorrectly as character strings. This bug did not manifest itself with latin1 collations if there were less than about 100 elements in an ENUM, but it caused malfunction with UTF-8. Old tables will continue to work. In new tables, ENUM and SET will be internally stored as unsigned integers. (Bug #9526)

  • InnoDB: Avoid test suite failures caused by a locking conflict between two server instances at server shutdown/startup. This conflict on advisory locks appears to be the result of a bug in the operating system; these locks should be released when the files are closed, but somehow that does not always happen immediately in Linux. (Bug #9381)

  • InnoDB: True VARCHAR: InnoDB stored the 'position' of a row wrong in a column prefix primary key index; this could cause MySQL to complain ERROR 1032: Can't find record … in an update of the primary key, and also some ORDER BY or DISTINCT queries. (Bug #9314)

  • InnoDB: Fix bug in MySQL/InnoDB 5.0.3: SQL statements were not rolled back on error. (Bug #8650)

  • Fixed a Commands out of sync error when two prepared statements for single-row result sets were open simultaneously. (Bug #8880)

  • Fixed a server crash after a call to mysql_stmt_close() for single-row result set. (Bug #9159)

  • Fixed server crashes for CREATE TABLE ... SELECT or INSERT INTO ... SELECT when selecting from multiple-table view. (Bug #8703, Bug #9398)

  • TRADITIONAL SQL mode should prevent inserts where a column with no default value is omitted or set to a value of DEFAULT. Fixed cases where this restriction was not enforced. (Bug #5986)

  • Fixed a server crash when creating a PRIMARY KEY for a table, if the table contained a BIT column. (Bug #9571)

  • Warning message from GROUP_CONCAT() did not always indicate correct number of lines. (Bug #8681)

  • The commit count cache for NDB was not properly invalidated when deleting a record using a cursor. (Bug #8585)

  • Fixed option-parsing code for the embedded server to understand K, M, and G suffixes for the net_buffer_length and max_allowed_packet options. (Bug #9472)

  • Selecting a BIT column failed if the binary client/server protocol was used. (Bug #9608)

  • Fixed a permissions problem whereby information in INFORMATION_SCHEMA could be exposed to a user with insufficient privileges. (Bug #7214)

  • An error now occurs if you try to insert an invalid value via a stored procedure in STRICT mode. (Bug #5907)

  • Link with libsupc++ on Fedora Core 3 to get language support functions. (Bug #6554)

  • The value of the CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH columns of the INFORMATION_SCHEMA.COLUMNS table must be NULL for numeric columns, but were not. (Bug #9344)

  • DROP TABLE did not drop triggers that were defined for the table. DROP DATABASE did not drop triggers in the database. (Bug #5859, Bug #6559)

  • CREATE OR REPLACE VIEW and ALTER VIEW now require the CREATE VIEW and DROP privileges, not CREATE VIEW and DELETE. (DELETE is a row-level privilege, not a table-level privilege.) (Bug #9260)

  • Some user variables were not being handled with “implicit” coercibility. (Bug #9425)

  • Setting the max_error_count system variable to 0 resulted in a setting of 1. (Bug #9072)

  • Fixed a collation coercibility problem that caused a union between binary and non-binary columns to fail. (Bug #6519)

  • Fixed a bug in division of floating point numbers. It could cause nine zeroes (000000000) to be inserted in the middle of the quotient. (Bug #9501)

  • INFORMATION_SCHEMA tables had an implicit upper limit for the number of rows. As a result, not all data could be returned for some queries. (Bug #9317)

  • Fixed a problem with the tee command in mysql that resulted in mysql crashing. (Bug #8499)

  • CAST() now produces warnings when casting incorrect INTEGER and CHAR values. This also applies to implicit string to number casts. (Bug #5912)

  • ALTER TABLE now fails in STRICT mode if the alteration generates warnings.

  • Using CONVERT('0000-00-00',date) or CAST('0000-00-00' as date) with the NO_ZERO_DATE SQL mode enabled now produces a warning. (Bug #6145)

  • Inserting a zero date in a DATE, DATETIME or TIMESTAMP column during TRADITIONAL mode now produces an error. (Bug #5933)

  • Inserting a zero date into a DATETIME column in TRADITIONAL mode now produces an error.

  • STR_TO_DATE() now produces errors in strict mode (and warnings otherwise) when given an illegal argument. (Bug #5902)

  • Fixed a problem with ORDER BY that sometimes caused incorrect sorting of utf8 data. (Bug #9309)

  • Fixed server crash resulting from queries that combined SELECT DISTINCT, SUM(), and ROLLUP. (Bug #8615)

  • Incorrect results were returned from queries that combined SELECT DISTINCT, GROUP BY , and ROLLUP. (Bug #8616)

  • Too many rows were returned from queries that combined ROLLUP and LIMIT if SQL_CALC_FOUND_ROWS was given. (Bug #8617)

  • If on replication master a LOAD DATA INFILE is interrupted in the middle (integrity constraint violation, killed connection...), the slave used to skip this LOAD DATA INFILE entirely, thus missing some changes if this command permanently inserted/updated some table records before being interrupted. This is now fixed. (Bug #3247)

D.1.18. Changes in release 5.0.3 (23 Mar 2005: Beta)

Note: This Beta release, as any other pre-production release, should not be installed on “production” level systems or systems with critical data. It is good practice to back up your data before installing any new version of software. Although MySQL worked very hard to ensure a high level of quality, protect your data by making a backup as you would for any software beta release.

Functionality added or changed:

  • Security improvement: The server creates .frm, .MYD, .MYI, .MRG, .ISD, and .ISM table files only if a file with the same name does not already exist. Thanks to Stefano Di Paola for finding and informing us about this issue. (CAN-2005-0711)

  • Security improvement: User-defined functions should have at least one symbol defined in addition to the xxx symbol that corresponds to the main xxx() function. These auxiliary symbols correspond to the xxx_init(), xxx_deinit(), xxx_reset(), xxx_clear(), and xxx_add() functions. mysqld by default no longer loads UDFs unless they have at least one auxiliary symbol defined in addition to the main symbol. The --allow-suspicious-udfs option controls whether UDFs that have only an xxx symbol can be loaded. By default, the option is off. mysqld also checks UDF filenames when it reads them from the mysql.func table and rejects those that contain directory pathname separator characters. (It already checked names as given in CREATE FUNCTION statements.) See Section 24.2.4.1, “UDF Calling Sequences for Simple Functions”, Section 24.2.4.2, “UDF Calling Sequences for Aggregate Functions”, and Section 24.2.4.6, “User-Defined Function Security Precautions”. Thanks to Stefano Di Paola for finding and informing us about this issue. (CAN-2005-0709, CAN-2005-0710)

  • The DECIMAL and NUMERIC data types now are handled with a fixed-point library that allows for precision math handling that results in more accurate results. See Chapter 21, Precision Math.

    Warning: Incompatible change: A consequence of the change in handling of the DECIMAL and NUMERIC fixed-point data types is that the server is more strict to follow standard SQL. For example, a data type of DECIMAL(3,1) stores a maximum value of 99.9. Previously, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. Now the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 5.0.3 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 DECIMAL(4,1);
    
  • Incompatible change: The C API ER_WARN_DATA_TRUNCATED warning symbol was renamed to WARN_DATA_TRUNCATED.

  • InnoDB: Upgrading from 4.1: The sorting order for end-space in TEXT columns for InnoDB tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE.

  • Implemented support for XA transactions. See Section 13.4.7, “XA Transactions”. The implementation make the innodb_safe_binlog system variable obsolete, so it has been removed.

  • mysqlbinlog now prints a ROLLBACK statement at the end of its output, in case the server crashed while it was in the process of writing the final entry into the last binary log named on the command line. This causes any half-written transaction to be rolled back when the output is executed. The ROLLBACK is harmless if the binary log file was written and closed normally.

  • Added the engine_condition_pushdown system variable. For NDB, setting this variable to 1 allows processing of some WHERE clause conditions to be processed in NDB nodes before rows are sent to the MySQL server, rather than having rows sent to the server for evaluation.

  • Additional control over transaction completion was implemented. The COMMIT and ROLLBACK statements support AND [NO] CHAIN and RELEASE clauses. There is a new RELEASE SAVEPOINT statement. The completion_type system variable was added for setting the global and session default completion type.

  • A new CREATE USER privilege was added.

  • my.cnf in the compile-time datadir (usually /usr/local/mysql/data/ in the binary tarball distributions) is not being read anymore. The value of the environment variable MYSQL_HOME is used instead of the hard-coded path.

  • Support for the ISAM storage engine has been removed. If you have ISAM tables, you should convert them before upgrading. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • Support for RAID options in MyISAM tables has been removed. If you have tables that use these options, you should convert them before upgrading. See Section 2.10.2, “Upgrading from MySQL 4.1 to 5.0”.

  • Added support for AVG(DISTINCT).

  • ONLY_FULL_GROUP_BY no longer is included in the ANSI composite SQL mode. (Bug #8510)

  • mysqld_safe will create the directory where the UNIX socket file is to be located if the directory does not exist. This applies only to the last component of the directory pathname. (Bug #8513)

  • The coercibility for the return value of functions such as USER() or VERSION() now is “system constant” rather than “implicit.” This makes these functions more coercible than column values so that comparisons of the two do not result in Illegal mix of collations errors. COERCIBILITY() was modified to accommodate this new coercibility value. See Section 12.9.3, “Information Functions”.

  • User variable coercibility has been changed from “coercible” to “implicit.” That is, user variables have the same coercibility as column values.

  • Boolean full-text phrase searching now requires only that matches contain exactly the same words as the phrase and in the same order. Non-word characters no longer need match exactly.

  • CHECKSUM TABLE returns a warning for non-existing tables. The checksum value remains NULL as before. (Bug #8256)

  • The server now includes a timestamp in the Ready for connections message that is written to the error log at startup. (Bug #8444)

  • Added SQL_NOTES session variable to cause Note-level warnings not to be recorded. (Bug #6662)

  • Allowed the service-installation command for Windows servers to specify a single option other than --defaults-file following the service name. This is for compatibility with MySQL 4.1. (Bug #7856)

  • InnoDB: Commit after every 10,000 copied rows when executing ALTER TABLE, CREATE INDEX, DROP INDEX or OPTIMIZE TABLE. This makes it much faster to recover from an aborted operation.

  • Added VAR_POP() and STDDEV_POP() as standard SQL aliases for the VARIANCE() and STDDEV() functions that compute population variance and standard deviation. Added new VAR_SAMP() and STDDEV_SAMP() functions to compute sample variance and standard deviation. (Bug #3190)

  • Fixed a problem with out-of-order packets being sent (ERROR after OK or EOF) following a KILL QUERY statement. (Bug #6804)

  • Retrieving from a view defined as a SELECT that mixed UNION ALL and UNION DISTINCT resulted in a different result than retrieving from the original SELECT. (Bug #6565)

  • Fixed a problem with non-optimal index_merge query execution plans being chosen on IRIX. (Bug #8578)

  • BIT in column definitions now is a distinct data type; it no longer is treated as a synonym for TINYINT(1).

  • Bit-field values can be written using b'value' notation. value is a binary value written using 0s and 1s.

  • From the Windows distribution, predefined accounts without passwords for remote users ("root@%", "@%") were removed (other distributions never had them).

  • Added mysql_library_init() and mysql_library_end() as synonyms for the mysql_server_init() and mysql_server_end() C API functions. mysql_library_init() and mysql_library_end() are #define symbols, but the names more clearly indicate that they should be called when beginning and ending use of a MySQL C API library no matter whether the application uses libmysqlclient or libmysqld. (Bug #6149)

  • SHOW COLUMNS now displays NO rather than blank in the Null output column if the corresponding table column cannot be NULL.

  • Changed XML format for mysql from <col_name>col_value</col_name> to <field name="col_name">col_value</field> to allow for proper encoding of column names that are not legal as element names. (Bug #7811)

  • Added --innodb-checksums and --innodb-doublewrite options for mysqld.

  • Added --large-pages option for mysqld.

  • Added multi_read_range system variable.

  • SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, and so forth display information about the INFORMATION_SCHEMA database. Also, several SHOW statements now accept a WHERE clause specifying which output rows to display. See Chapter 20, The INFORMATION_SCHEMA Database.

  • Added the CREATE ROUTINE and ALTER ROUTINE privileges, and made the EXECUTE privilege operational.

  • InnoDB: Corrected a bug in the crash recovery of ROW_FORMAT=COMPACT tables that caused corruption. (Bug #7973) There may still be bugs in the crash recovery, especially in COMPACT tables.

  • When the MyISAM storage engine detects corruption of a MyISAM table, a message describing the problem now is written to the error log.

  • InnoDB: When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the operating system version at run time and use the fcntl() file flush method on Mac OS X versions 10.3 and later. In Mac OS X, fsync() does not flush the write cache in the disk drive, but the special fcntl() does; however, the flush request is ignored by some external devices. Failure to flush the buffers may cause severe database corruption at power outages.

  • InnoDB: Implemented fast TRUNCATE TABLE. The old approach (deleting rows one by one) may be used if the table is being referenced by foreign keys. (Bug #7150)

  • Added cp932 (SJIS for Windows Japanese) and eucjpms (UJIS for Windows Japanese) character sets.

  • Added several InnoDB status variables. See Section 5.2.4, “Server Status Variables”.

  • Added the FEDERATED storage engine. See Section 14.7, “The FEDERATED Storage Engine”.

  • SHOW CREATE TABLE now uses USING index_type rather than TYPE index_type to specify an index type. (Bug #7233)

  • InnoDB now supports a fast TRUNCATE TABLE. One visible change from this is that auto-increment values for this table are reset on TRUNCATE.

  • Added an error member to the MYSQL_BIND data structure that is used in the C API for prepared statements. This member is used for reporting data truncation errors. Truncation reporting is enabled via the new MYSQL_REPORT_DATA_TRUNCATION option for the mysql_options() C API function.

  • API change: the reconnect flag in the MYSQL structure is now set to 0 by mysql_real_connect(). Only those client programs which didn't explicitly set this flag to 0 or 1 after mysql_real_connect() experience a change. Having automatic reconnection enabled by default was considered too dangerous (after reconnection, table locks, temporary tables, user and session variables are lost).

  • FLUSH TABLES WITH READ LOCK is now killable while it's waiting for running COMMIT statements to finish.

  • MEMORY (HEAP) can have VARCHAR() fields.

  • VARCHAR columns now remember end space. A VARCHAR() column can now contain up to 65535 bytes. For more details, see Section D.1, “Changes in release 5.0.x (Production)”. If the table handler doesn't support the new VARCHAR type, then it's converted to a CHAR column. Currently this happens for NDB tables.

  • InnoDB: Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. The new format typically saves 20 % of disk space and memory.

  • InnoDB: Setting the initial AUTO_INCREMENT value for an InnoDB table using CREATE TABLE ... AUTO_INCREMENT = n now works, and ALTER TABLE ... AUTO_INCREMENT = n resets the current value.

  • Seconds_Behind_Master is NULL (which means “unknown”) if the slave SQL thread is not running, or if the slave I/O thread is not running or not connected to master. It is zero if the SQL thread has caught up to the I/O thread. It no longer grows indefinitely if the master is idle.

  • The MySQL server aborts immediately instead of simply issuing a warning if it is started with the --log-bin option but cannot initialize the binary log at startup (that is, an error occurs when writing to the binary log file or binary log index file).

  • The binary log file and binary log index file now are handled the same way as MyISAM tables when there is a “disk full” or “quota exceeded” error. See Section A.4.3, “How MySQL Handles a Full Disk”.

  • The MySQL server now aborts when started with the option --log-bin-index and without --log-bin, and when started with --log-slave-updates and without --log-bin.

  • If the MySQL server is started without an argument to --log-bin and without --log-bin-index, thus not providing a name for the binary log index file, a warning is issued because MySQL falls back to using the hostname for that name, and this is prone to replication issues if the server's hostname's gets changed later. See Section A.8.1, “Open Issues in MySQL”.

  • Added account-specific MAX_USER_CONNECTIONS limit, which allows you to specify the maximum number of concurrent connections for the account. Also, all limited resources now are counted per account (instead of being counted per user + host pair as it was before). Use the --old-style-user-limits option to get the old behavior.

  • InnoDB: A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record in the foreign key check because inserts can be allowed into gaps.

  • InnoDB: Relaxed locking in INSERT…SELECT, single table UPDATE…SELECT and single table DELETE…SELECT clauses when innodb_locks_unsafe_for_binlog is used and isolation level of the transaction is not serializable. InnoDB uses consistent read in these cases for a selected table.

  • Added a new global system variable slave_transaction_retries: if the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or exceeded InnoDB's innodb_lock_wait_timeout or NDBCluster's TransactionDeadlockDetectionTimeout or TransactionInactiveTimeout, it automatically retries slave_transaction_retries times before stopping with an error. The default is 10. (Bug #8325)

  • When a client releases a user-level lock, DO RELEASE_LOCK() will not be written to the binary log anymore (this makes the binary log smaller); as a counterpart, the slave does not actually take the lock when it executes GET_LOCK(). This is mainly an optimization and should not affect existing setups. (Bug #7998)

  • The way the character set information is stored into the binary log was changed, so that it's now possible to have a replication master and slave running with different global character sets. A drawback is that replication from 5.0.3 masters to pre-5.0.3 slaves is impossible.

  • The LOAD DATA statement was extended to support user variables in the target column list, and an optional SET clause. Now one can perform some transformations on data after they have been read and before they are inserted into the table. For example:

    LOAD DATA INFILE 'file.txt'
      INTO TABLE t1
      (column1, @var1)
      SET column2 = @var1/100;
    

    Also, replication of LOAD DATA was changed, so you can't replicate such statements from a 5.0.3 master to pre-5.0.3 slaves.

  • NDB Cluster: When using this storage engine, the output of SHOW TABLE STATUS now displays properly-calculated values in the Avg_row_length and Data_length columns. (Note that BLOB columns are not yet taken into account.) In addition, the number of replicas is now shown in the Comment column (as number_of_replicas).

Bugs fixed:

  • If a MyISAM table on Windows had INDEX DIRECTORY or DATA DIRECTORY table options, mysqldump dumped the directory pathnames with single-backslash pathname separators. This would cause syntax errors when importing the dump file. mysqldump now changes ‘\’ to ‘/’ in the pathnames on Windows. (Bug #6660)

  • mysql_fix_privilege_tables now fixes that the mysql privilege tables can be used in MySQL 4.1. This allows one to easily downgrade to 4.1 or run MySQL 5.0 and 4.1 with the same privilege files for testing purposes.

  • Fixed bug creating user with GRANT fails with password but works without, (Bug #7905)

  • mysqldump misinterpreted ‘_’ and ‘%’ characters in the names of tables to be dumped as wildcard characters. (Bug #9123)

  • The definition of the enumeration-valued sql_mode column of the mysql.proc table was missing some of the current allowable SQL modes, so stored routines would not necessarily execute with the SQL mode in effect at the time of routine definition. (Bug #8902)

  • REPAIR TABLE did not invalidate query results in the query cache that were generated from the table. (Bug #8480)

  • In strict or traditional SQL mode, too-long string values assigned to string columns (CHAR, VARCHAR, BINARY, VARBINARY, TEXT, or BLOB) were correctly truncated, but the server returned an SQLSTATE value of 01000 (should be 22001). (Bug #6999, Bug #9029)

  • Stored functions that used cursors could return incorrect results. (Bug #8386)

  • AES_DECRYPT(col_name,key) could fail to return NULL for invalid values in col_name, if col_name was declared as NOT NULL. (Bug #8669)

  • Ordering by unsigned expression (more complex than a column reference) was treating the value as signed, producing incorrectly sorted results. (Bug #7425)

  • HAVING was treating unsigned columns as signed. (Bug #7425)

  • Fixed a problem with boolean full-text searches on utf8 columns where a double quote in the search string caused a server crash. (Bug #8351)

  • For a query with both GROUP BY and COUNT(DISTINCT) clauses and a FROM clause with a subquery, NULL was returned for any VARCHAR column selected by the subquery. (Bug #8218)

  • Fixed a bug in TRUNCATE, which did not work within stored procedures. A workaround has been made so that within stored procedures, TRUNCATE is executed like DELETE. This was necessary because TRUNCATE is implicitly locking tables. (Bug #8850)

  • Fixed an optimizer bug that caused incorrectly ordered result from a query that used a FULLTEXT index to retrieve rows and there was another index that was usable for ORDER BY. For such a query, EXPLAIN showed fulltext join type, but regular (not FULLTEXT) index in the Key column. (Bug #6635)

  • If SELECT DISTINCT named an index column multiple times in the select list, the server tried to access different key fields for each instance of the column, which could result in a crash. (Bug #8532)

  • For a stored function that refers to a given table, invoking the function while selecting from the same table resulted in a server crash. (Bug #8405)

  • Comparison of a DECIMAL column containing NULL to a subquery that produced DECIMAL values resulted in a server crash. (Bug #8397)

  • The --set-character-set option for myisamchk was changed to --set-collation. The value needed for specifying how to sort indexes is a collation name, not a character set name. (Bug #8349)

  • Hostname matching didn't work if a netmask was specified for table-specific privileges. (Bug #3309)

  • Corruption of MyISAM table indexes could occur with TRUNCATE TABLE if the table had already been opened. For example, this was possible if the table had been opened implicitly by selecting from a MERGE table that mapped to the MyISAM table. The server now issues an error message for TRUNCATE TABLE under these conditions. (Bug #8306)

  • Setting the connection collation to a value different from the server collation followed by a CREATE TABLE statement that included a quoted default value resulted in a server crash. (Bug #8235)

  • Fixed handling of table-name matching in mysqlhotcopy to accommodate DBD::mysql 2.9003 and up (which implement identifier quoting). (Bug #8136)

  • Selecting from a view defined as a join caused a server crash if the query cache was enabled. (Bug #8054)

  • Results in the query cache generated from a view were not properly invalidated after ALTER VIEW or DROP VIEW on that view. (Bug #8050)

  • FOUND_ROWS() returned an incorrect value after a SELECT SQL_CALC_FOUND_ROWS DISTINCT statement that selected constants and included GROUP BY and LIMIT clauses. (Bug #7945)

  • Selecting from an INFORMATION_SCHEMA table combined with a subquery on an INFORMATION_SCHEMA table caused an error with the message Table tbl_name is corrupted. (Bug #8164)

  • Fixed a problem with equality propagation optimization for prepared statements and stored procedures that caused a server crash upon re-execution of the prepared statement or stored procedure. (Bug #8115, Bug #8849)

  • LEFT OUTER JOIN between an empty base table and a view on an empty base table caused a server crash. (Bug #7433)

  • Use of GROUP_CONCAT() in the select list when selecting from a view caused a server crash. (Bug #7116)

  • Use of a view in a correlated subquery that contains HAVING but no GROUP BY caused a server crash. (Bug #6894)

  • Handling by mysql_list_fields() of references to stored functions within views was incorrect and could result in a server crash. (Bug #6814)

  • mysqldump now avoids writing SET NAMES to the dump output if the server is older than version 4.1 and would not understand that statement. (Bug #7997)

  • Fixed problems when selecting from a view that had an EXISTS or NOT EXISTS subquery. Selecting columns by name caused a server crash. With SELECT *, a crash did not occur, but columns in outer query were not resolved properly. (Bug #6394)

  • DDL statements for views were not being written to the binary log (and thus not subject to replication). (Bug #4838)

  • The CHAR() function was not ignoring NULL arguments, contrary to the documentation. (Bug #6317)

  • Creating a table using a name containing a character that is illegal in character_set_client resulted in the character being stripped from the name and no error. The character now is considered an error. (Bug #8041)

  • Fixed a problem with the Cyrillic letters I and SHORT I being treated the same by the utf8_general_ci collation. (Bug #8385)

  • Some INFORMATION_SCHEMA columns that contained catalog identifiers were of type LONGTEXT. These were changed to VARCHAR(N, where N is the appropriate maximum identifier length. (Bug #7215)

  • Some INFORMATION_SCHEMA columns that contained timestamp values were of type VARBINARY. These were changed to TIMESTAMP. (Bug #7217)

  • An expression that tested a case-insensitive character column against string constants that differed in lettercase could fail because the constants were treated as having a binary collation. (For example, WHERE city='London' AND city='london' could fail.) (Bug #7098, Bug #8690)

  • The output of the STATUS (\s) command in mysql had the values for the server and client character sets reversed. (Bug #7571)

  • If the slave was running with --replicate-*-table options which excluded one temporary table and included another, and the two tables were used in a single DROP TEMPORARY TABLE IF EXISTS statement, as the ones the master automatically writes to its binary log upon client's disconnection when client has not explicitly dropped these, the slave could forget to delete the included replicated temporary table. Only the slave needs to be upgraded. (Bug #8055)

  • When setting integer system variables to a negative value with SET VARIABLES, the value was treated as a positive value modulo 232. (Bug #6958)

  • Corrected a problem with references to DUAL where statements such as SELECT 1 AS a FROM DUAL would succeed but statements such as SELECT 1 AS a FROM DUAL LIMIT 1 would fail. (Bug #8023)

  • Fixed a server crash caused by DELETE FROM tbl_name ... WHERE ... ORDER BY tbl_name.col_name when the ORDER BY column was qualified with the table name. (Bug #8392)

  • Fixed a bug in MATCH ... AGAINST in natural language mode that could cause a server crash if the FULLTEXT index was not used in a join (EXPLAIN did not show fulltext join mode) and the search query matched no rows in the table (Bug #8522).

  • InnoDB: Honor the --tmpdir startup option when creating temporary files. Previously, InnoDB temporary files were always created in the temporary directory of the operating system. On Netware, InnoDB will continue to ignore --tmpdir. (Bug #5822)

  • Platform and architecture information in version information produced for --version option on Windows was always Win95/Win98 (i32). More accurately determine platform as Win32 or Win64 for 32-bit or 64-bit Windows, and architecture as ia32 for x86, ia64 for Itanium, and axp for Alpha. (Bug #4445)

  • If multiple semicolon-separated statements were received in a single packet, they were written to the binary log as a single event rather than as separate per-statement events. For a server serving as a replication master, this caused replication to fail when the event was sent to slave servers. (Bug #8436)

  • Fixed LOAD INDEX statement to actually load index in memory. (Bug #8452)

  • Fixed a failure of multiple-table updates to replicate properly on slave servers when --replicate-*-table options had been specified. (Bug #7011)

  • Fixed failure of CREATE TABLE ... LIKE Windows when the source or destination table was located in a symlinked database directory. (Bug #6607)

  • With lower_case_table_names set to 1, mysqldump on Windows could write the same table name in different lettercase for different SQL statements. Fixed so that consistent lettercase is used. (Bug #5185)

  • mysqld_safe now understands the --help option. Previously, it ignored the option and attempted to start the server anyway. (Bug #7931)

  • Fixed problem in NO_BACKSLASH_ESCAPES SQL mode for strings that contained both the string quoting character and backslash. (Bug #6368)

  • Fixed some portability issues with overflow in floating point values.

  • Prepared statements now gives warnings on prepare.

  • Fixed bug in prepared statements with SUM(DISTINCT...).

  • Fixed bug in prepared statements with OUTER JOIN.

  • Fixed a bug in CONV() function returning unsigned BIGINT number (third argument is positive, and return value does not fit in 32 bits). (Bug #7751)

  • Fixed a failure of the IN() operator to return correct result if all values in the list were constants and some of them were using substring functions, for example, LEFT(), RIGHT(), or MID(). (Bug #7716)

  • Fixed a crash in CONVERT_TZ() function when its second or third argument was from a const table (see Section 7.2.1, “Optimizing Queries with EXPLAIN). (Bug #7705)

  • Fixed a problem with calculation of number of columns in row comparison against subquery. (Bug #8020)

  • Fixed erroneous output resulting from SELECT DISTINCT combined with a subquery and GROUP BY. (Bug #7946)

  • Fixed server crash in comparing a nested row expression (for example row(1,(2,3))) with a subquery. (Bug #8022)

  • Fixed server crash resulting from certain correlated subqueries with forward references (references to an alias defined later in the outer query). (Bug #8025)

  • Fixed server crash resulting from re-execution of prepared statements containing subqueries. (Bug #8125)

  • Fixed a bug where ALTER TABLE improperly would accept an index on a TIMESTAMP column that CREATE TABLE would reject. (Bug #7884)

  • SHOW CREATE TABLE now reports ENGINE=MEMORY rather than ENGINE=HEAP for a MEMORY table (unless the MYSQL323 SQL mode is enabled). (Bug #6659)

  • Fixed a bug where the use of GROUP_CONCAT() with HAVING caused a server crash. (Bug #7769)

  • Fixed a bug where comparing the result of a subquery to a non-existent column caused a server crash on Windows. (Bug #7885)

  • Fixed a bug in a combination of -not and trunc* operators of full-text search. Using more than one truncated negative search term, was causing empty result set.

  • InnoDB: Corrected the handling of trailing spaces in the ucs2 character set. (Bug #7350, Bug #8771)

  • InnoDB: Use native tmpfile() function on Netware. All InnoDB temporary files are created under sys:\tmp. Previously, InnoDB temporary files were never deleted on Netware.

  • Fixed a bug in max_heap_table_size handling, that resulted in Table is full error when the table was still smaller than the limit. (Bug #7791).

  • Fixed a symlink vulnerability in the mysqlaccess script. Reported by Javier Fernandez-Sanguino Pena and Debian Security Audit Team. (CAN-2005-0004)

  • Fixed a bug that caused server crash if some error occurred during filling of temporary table created for derived table or view handling. (Bug #7413)

  • Fixed a bug which caused server crash if query containing CONVERT_TZ() function with constant arguments was prepared. (Bug #6849)

  • Prevent adding CREATE TABLE .. SELECT query to the binary log when the insertion of new records partially failed. (Bug #6682)

  • Fixed a bug which caused a crash when only the slave I/O thread was stopped and started. (Bug #6148)

  • Giving mysqld a SIGHUP caused it to crash.

  • Changed semantics of CREATE/ALTER/DROP DATABASE statements so that replication of CREATE DATABASE is possible when using --binlog-do-db and --binlog-ignore-db. (Bug #6391)

  • A sequence of BEGIN (or SET AUTOCOMMIT=0), FLUSH TABLES WITH READ LOCK, transactional update, COMMIT, FLUSH TABLES WITH READ LOCK could hang the connection forever and possibly the MySQL server itself. This happened for example when running the innobackup script several times. (Bug #6732)

  • mysqlbinlog did not print SET PSEUDO_THREAD_ID statements in front of LOAD DATA INFILE statements inserting into temporary tables, thus causing potential problems when rolling forward these statements after restoring a backup. (Bug #6671)

  • InnoDB: Fixed a bug no error message for ALTER with InnoDB and AUTO_INCREMENT (Bug #7061). InnoDB now supports ALTER TABLE...AUTO_INCREMENT = x query to set auto increment value for a table.

  • Made the MySQL server accept executing SHOW CREATE DATABASE even if the connection has an open transaction or locked tables; refusing it made mysqldump --single-transaction sometimes fail to print a complete CREATE DATABASE statement for some dumped databases. (Bug #7358)

  • Fixed that, when encountering a “disk full” or “quota exceeded” write error, MyISAM sometimes didn't sleep and retry the write, thus resulting in a corrupted table. (Bug #7714)

  • Fixed that --expire-log-days was not honored if using only transactions. (Bug #7236)

  • Fixed that a slave could crash after replicating many ANALYZE TABLE, OPTIMIZE TABLE, or REPAIR TABLE statements from the master. (Bug #6461, Bug #7658)

  • mysqlbinlog forgot to add backquotes around the collation of user variables (causing later parsing problems as BINARY is a reserved word). (Bug #7793)

  • Ensured that mysqldump --single-transaction sets its transaction isolation level to REPEATABLE READ before proceeding (otherwise if the MySQL server was configured to run with a default isolation level lower than REPEATABLE READ it could give an inconsistent dump). (Bug #7850)

  • Fixed that when using the RPAD() function (or any function adding spaces to the right) in a query that had to be resolved by using a temporary table, all resulting strings had rightmost spaces removed (that is, RPAD() did not work) (Bug #4048)

  • Fixed that a 5.0.3 slave can connect to a master < 3.23.50 without hanging (the reason for the hang is a bug in these quite old masters -- SELECT @@unknown_var hangs them -- which was fixed in MySQL 3.23.50). (Bug #7965)

  • InnoDB: Fixed a deadlock without any locking, simple select and update (Bug #7975). InnoDB now takes an exclusive lock when INSERT ON DUPLICATE KEY UPDATE is checking duplicate keys.

  • Fixed a bug where MySQL was allowing concurrent updates (inserts, deletes) to a table if binary logging is enabled. Changed to ensure that all updates are executed in a serialized fashion, because they are executed serialized when binlog is replayed. (Bug #7879)

  • Fixed a rare race condition which could lead to FLUSH TABLES WITH READ LOCK hanging. (Bug #8682)

  • Fixed a bug in replication that caused the master to stamp generated statements (such as SET commands) with an error_code intended only for another statement. This could happen, for example, when a statements generates a duplicate key error on the master but must be replicated. (Bug #8412)

D.1.19. Changes in release 5.0.2 (01 Dec 2004)

Functionality added or changed:

  • Warning: Incompatible change! The precedence of NOT operator has changed so that expressions such as NOT a BETWEEN b AND c are parsed correctly as NOT (a BETWEEN b AND c) rather than as (NOT a) BETWEEN b AND c. The pre-5.0 higher-precedence behavior can be obtained by enabling the new HIGH_NOT_PRECEDENCE SQL mode.

  • SHOW STATUS now shows the thread specific status variables and SHOW GLOBAL STATUS shows the status variables for the whole server.

  • Added support for the INFORMATION_SCHEMAinformation database” that provides database metadata. See Chapter 20, The INFORMATION_SCHEMA Database.

  • A HAVING clause in a SELECT statement now can refer to columns in the GROUP BY clause, as required by standard SQL.

  • Added the CREATE USER and RENAME USER statements.

  • Modify DROP USER so that it drops the account, including all its privileges. Formerly, it removed the account record only for an account that had had all privileges revoked.

  • Added IS [NOT] boolean_value syntax, where boolean_value is TRUE, FALSE, or UNKNOWN.

  • Added several InnoDB status variables. See Section 5.2.4, “Server Status Variables”.

  • Implemented the WITH CHECK OPTION clause for CREATE VIEW.

  • CHECK TABLE now works for views.

  • The SCHEMA and SCHEMAS keywords are now accepted as synonyms for DATABASE and DATABASES.

  • Added initial support for rudimentary triggers (the CREATE TRIGGER and DROP TRIGGER statements).

  • Added basic support for read-only server side cursors.

  • mysqldump --single-transaction --master-data is now able to take an online (non-blocking) dump of InnoDB and report the corresponding binary log coordinates, which makes a backup suitable for point-in-time recovery, roll-forward or replication slave creation. See Section 8.10, “mysqldump — A Database Backup Program”.

  • Added --start-datetime, --stop-datetime, --start-position, --stop-position options to mysqlbinlog (makes point-in-time recovery easier).

  • Made the MySQL server not react to signals SIGHUP and SIGQUIT on Mac OS X 10.3. This is needed because under this OS, the MySQL server receives lots of these signals (reported as Bug #2030).

  • New --auto-increment-increment and --auto-increment-offset startup options. These allow you to set up a server to generate auto-increment values that don't conflict with another server.

  • MySQL now by default checks dates and in strict mode allows only fully correct dates. If you want MySQL to behave as before, you should enable the new ALLOW_INVALID_DATES SQL mode.

  • Added STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and TRADITIONAL SQL modes. The TRADITIONAL mode is shorthand for all the preceding modes. When using mode TRADITIONAL, MySQL generates an error if you try to insert a wrong value in a column. It does not adjust the value to the closest possible legal value.

  • MySQL now remembers which columns were declared to have default values. In STRICT_TRANS_TABLES/STRICT_ALL_TABLES mode, you now get an error if you do an INSERT without specifying all columns that don't have a default value. A side effect of this is that when you do SHOW CREATE for a new table, you no longer see a DEFAULT value for a column for which you didn't specify a default value.

  • The compilation flag DONT_USE_DEFAULT_FIELDS was removed because you can get the same behavior by setting the sql_mode system variable to STRICT_TRANS_TABLES.

  • Added NO_AUTO_CREATE_USER SQL mode to prevent GRANT from automatically creating new users if it would otherwise do so, unless a password also is specified.

  • We now detect too-large floating point numbers during statement parsing and generate an error messages for them.

  • Renamed the sql_updatable_view_key system variable to updatable_views_with_limit. This variable now can have only two values:

    • 1 or YES: Don't issue an error message (warning only) if a VIEW without presence of a key in the underlying table is used in queries with a LIMIT clause for updating. (This is the default value.)

    • 0 or NO: Prohibit update of a VIEW, which does not contain a key in the underlying table and the query uses a LIMIT clause (usually get from GUI tools).

  • Reverted output format of SHOW TABLES to old pre-5.0.1 format that did not include a table type column. To get the additional column that lists the table type, use SHOW FULL TABLES now.

  • The mysql_fix_privilege_tables script now initializes the global CREATE VIEW and SHOW VIEW privileges in the user table to the value of the CREATE privilege in that table.

  • If the server finds that the user table has not been upgraded to include the view-related privilege columns, it treats each account as having view privileges that are the same as its CREATE privilege.

  • InnoDB: If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB in an UPDATE or a DELETE only locks the rows that it updates or deletes. This greatly reduces the probability of deadlocks.

  • A connection doing a rollback now displays "Rolling back" in the State column of SHOW PROCESSLIST.

  • mysqlbinlog now prints an informative commented line (thread id, timestamp, server id, and so forth) before each LOAD DATA INFILE, like it does for other queries; unless --short-form is used.

  • Two new server system variables were introduced. auto_increment_increment and auto_increment_offset can be set locally or globally, and are intended for use in controlling the behavior of AUTO_INCREMENT columns in master-to-master replication. Note that these variables are not intended to take the place of sequences. See Section 5.2.2, “Server System Variables”.

Bugs fixed:

  • Fixed that mysqlbinlog --read-from-remote-server sometimes couldn't accept two binary log files on the command line. (Bug #4507)

  • Fixed that mysqlbinlog --position --read-from-remote-server had incorrect # at lines. (Bug #4506)

  • Fixed that CREATE TABLE ... TYPE=HEAP ... AS SELECT... caused replication slave to stop. (Bug #4971)

  • Fixed that mysql_options(...,MYSQL_OPT_LOCAL_INFILE,...) failed to disable LOAD DATA LOCAL INFILE. (Bug #5038)

  • Fixed that disable-local-infile option had no effect if client read it from a configuration file using mysql_options(...,MYSQL_READ_DEFAULT,...). (Bug #5073)

  • Fixed that SET GLOBAL SYNC_BINLOG did not work on some platforms (Mac OS X). (Bug #5064)

  • Fixed that mysql-test-run failed on the rpl_trunc_binlog test if running test from the installed (the target of 'make install') directory. (Bug #5050)

  • Fixed that mysql-test-run failed on the grant_cache test when run as Unix user 'root'. (Bug #4678)

  • Fixed an unlikely deadlock which could happen when using KILL. (Bug #4810)

  • Fixed a crash when one connection got KILLed while it was doing START SLAVE. (Bug #4827)

  • Made FLUSH TABLES WITH READ LOCK block COMMIT if server is running with binary logging; this ensures that the binary log position can be trusted when doing a full backup of tables and the binary log. (Bug #4953)

  • Fixed that the counter of an auto_increment column was not reset by TRUNCATE TABLE is the table was a temporary one. (Bug #5033)

  • Fixed slave SQL thread so that the SET COLLATION_SERVER... statements it replicates don't advance its position (so that if it gets interrupted before the actual update query, it later redoes the SET). (Bug #5705)

  • Fixed that if the slave SQL thread found a syntax error in a query (which should be rare, as the master parsed it successfully), it stops. (Bug #5711)

  • Fixed that if a write to a MyISAM table fails because of a full disk or an exceeded disk quota, it prints a message to the error log every 10 minutes, and waits until disk becomes free. (Bug #3248)

  • Fixed problem introduced in 4.0.21 where a connection starting a transaction, doing updates, then FLUSH TABLES WITH READ LOCK, then COMMIT, would cause replication slaves to stop (complaining about error 1223). Bug surfaced when using the InnoDB innobackup script. (Bug #5949)

  • OPTIMIZE TABLE, REPAIR TABLE, and ANALYZE TABLE are now replicated without any error code in the binary log. (Bug #5551)

  • If a connection had an open transaction but had done no updates to transactional tables (for example if had just done a SELECT FOR UPDATE then executed a non-transactional update, that update automatically committed the transaction (thus releasing InnoDB's row-level locks etc). (Bug #5714)

  • If a connection was interrupted by a network error and did a rollback, the network error code got stored into the BEGIN and ROLLBACK binary log events; that caused superfluous slave stops. (Bug #6522)

  • Fixed a bug which prevented mysqlbinlog from being able to read from stdin, for example, when piping the output from zcat to mysqlbinlog. (Bug #7853)

D.1.20. Changes in release 5.0.1 (27 Jul 2004)

Note: This build passes our test suite and fixes a lot of reported bugs found in the previous 5.0.0 release. However, please be aware that this is not a “standard MySQL build” in the sense that there are still some open critical bugs in our bugs database at http://bugs.mysql.com/ that affect this release as well. We are actively fixing these and will make a new release where these are fixed as soon as possible. However, this binary should be a good candidate for testing new MySQL 5.0 features for future products.

Functionality added or changed:

  • Warning: Incompatible change! C API change: mysql_shutdown() now requires a second argument. This is a source-level incompatibility that affects how you compile client programs; it does not affect the ability of compiled clients to communicate with older servers. See Section 22.2.3.62, “mysql_shutdown().

  • When installing a MySQL server as a Windows service, the installation command can include a --local-service option following the service name to cause the server to run using the LocalService Windows account that has limited privileges. This is in addition to the --defaults-file option that also can be given following the service name.

  • Added support for read-only and updatable views based on a single table or other updatable views. View use requires that you upgrade your grant tables to add the view-related privileges. See Section 5.6.1, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.

  • Implemented a new “greedy search” optimizer that can significantly reduce the time spent on query optimization for some many-table joins. (You are affected if not only some particular SELECT is slow, but even using EXPLAIN for it takes a noticeable amount of time.) Two new system variables, optimizer_search_depth and optimizer_prune_level, can be used to fine-tune optimizer behavior.

  • A stored procedure is no longer “global.” That is, it now belongs to a specific database:

    • When a database is dropped, all routines belonging to that database are also dropped.

    • Procedure names may be qualified, for example, db.p()

    • When executed from another database, an implicit USE db_name is in effect.

    • Explicit USE db_name statements no longer are allowed in a stored procedure.

    See Chapter 17, Stored Procedures and Functions.

  • Fixed SHOW TABLES output field name and values according to standard. Field name changed from Type to table_type, values are BASE TABLE, VIEW and ERROR. (Bug #4603)

  • Added the sql_updatable_view_key system variable.

  • Added the --replicate-same-server-id server option.

  • Added Last_query_cost status variable that reports optimizer cost for last compiled query.

  • Added the --to-last-log option to mysqlbinlog, for use in conjunction with --read-from-remote-server.

  • Added the --innodb-safe-binlog server option, which adds consistency guarantees between the content of InnoDB tables and the binary log. See Section 5.12.3, “The Binary Log”.

  • OPTIMIZE TABLE for InnoDB tables is now mapped to ALTER TABLE instead of ANALYZE TABLE. This rebuilds the table, which updates index statistics and frees space in the clustered index.

  • sync_frm is now a settable global variable (not only a startup option).

  • For replication of MEMORY (HEAP) tables: Made the master automatically write a DELETE FROM statement to its binary log when a MEMORY table is opened for the first time since master's startup. This is for the case where the slave has replicated a non-empty MEMORY table, then the master is shut down and restarted: the table is now empty on master; the DELETE FROM empties it on slave too. Note that even with this fix, between the master's restart and the first use of the table on master, the slave still has out-of-date data in the table. But if you use the --init-file option to populate the MEMORY table on the master at startup, it ensures that the failing time interval is zero. (Bug #2477)

  • When a session having open temporary tables terminates, the statement automatically written to the binary log is now DROP TEMPORARY TABLE IF EXISTS instead of DROP TEMPORARY TABLE, for more robustness.

  • The MySQL server now returns an error if SET SQL_LOG_BIN is issued by a user without the SUPER privilege (in previous versions it just silently ignored the statement in this case).

  • Changed that when the MySQL server has binary logging disabled (that is, no --log-bin option was used), then no transaction binary log cache is allocated for connections. This should save binlog_cache_size bytes of memory (32KB by default) for every connection.

  • Added the sync_binlog=N global variable and startup option, which makes the MySQL server synchronize its binary log to disk (fdatasync()) after every Nth write to the binary log.

  • Changed the slave SQL thread to print less useless error messages (no more message duplication; no more messages when an error is skipped because of slave-skip-errors).

  • DROP DATABASE IF EXISTS, DROP TABLE IF EXISTS, single-table DELETE, and single-table UPDATE now are written to the binary log even if they changed nothing on the master (for example, even if a DELETE matched no rows). The old behavior sometimes caused bad surprises in replication setups.

  • Replication and mysqlbinlog now have better support for the case that the session character set and collation variables are changed within a given session. See Section 6.7, “Replication Features and Known Problems”.

  • Killing a CHECK TABLE statement does not result in the table being marked as “corrupted” any more; the table remains as if CHECK TABLE had not even started. See Section 13.5.5.3, “KILL Syntax”.

Bugs fixed:

  • Strange results with index (x, y) ... WHERE x=val_1 AND y>=val_2 ORDER BY pk; (Bug #3155)

  • Adding ORDER BY to a query that uses a subquery can cause incorrect results. (Bug #3118)

  • ALTER DATABASE caused the client to hang if the database did not exist. (Bug #2333)

  • SLAVE START (which is a deprecated syntax, START SLAVE should be used instead) could crash the slave. (Bug #2516)

  • Multiple-table DELETE statements were never replicated by the slave if there were any --replicate-*-table options. (Bug #2527)

  • The MySQL server did not report any error if a statement (submitted through mysql_real_query() or mysql_stmt_prepare()) was terminated by garbage characters. This can happen if you pass a wrong length parameter to these functions. The result was that the garbage characters were written into the binary log. (Bug #2703)

  • Replication: If a client connects to a slave server and issues an administrative statement for a table (for example, OPTIMIZE TABLE or REPAIR TABLE), this could sometimes stop the slave SQL thread. This does not lead to any corruption, but you must use START SLAVE to get replication going again. (Bug #1858)

  • Made clearer the error message that one gets when an update is refused because of the --read-only option. (Bug #2757)

  • Fixed that --replicate-wild-*-table rules apply to ALTER DATABASE when the table pattern is %, as is the case for CREATE DATABASE and DROP DATABASE. (Bug #3000)

  • Fixed that when a Rotate event is found by the slave SQL thread in the middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE STATUS remains correct. (Bug #3017)

  • Corrected the master's binary log position that InnoDB reports when it is doing a crash recovery on a slave server. (Bug #3015)

  • Changed the column Seconds_Behind_Master in SHOW SLAVE STATUS to never show a value of -1. (Bug #2826)

  • Changed that when a DROP TEMPORARY TABLE statement is automatically written to the binary log when a session ends, the statement is recorded with an error code of value zero (this ensures that killing a SELECT on the master does not result in a superfluous error on the slave). (Bug #3063)

  • Changed that when a thread handling INSERT DELAYED (also known as a delayed_insert thread) is killed, its statements are recorded with an error code of value zero (killing such a thread does not endanger replication, so we thus avoid a superfluous error on the slave). (Bug #3081)

  • Fixed deadlock when two START SLAVE commands were run at the same time. (Bug #2921)

  • Fixed that a statement never triggers a superfluous error on the slave, if it must be excluded given the --replicate-* options. The bug was that if the statement had been killed on the master, the slave would stop. (Bug #2983)

  • The --local-load option of mysqlbinlog now requires an argument.

  • Fixed a segmentation fault when running LOAD DATA FROM MASTER after RESET SLAVE. (Bug #2922)

  • mysqlbinlog --read-from-remote-server read all binary logs following the one that was requested. It now stops at the end of the requested file, the same as it does when reading a local binary log. There is an option --to-last-log to get the old behavior. (Bug #3204)

  • Fixed mysqlbinlog --read-from-remote-server to print the exact positions of events in the "at #" lines. (Bug #3214)

  • Fixed a rare error condition that caused the slave SQL thread spuriously to print the message Binlog has bad magic number and stop when it was not necessary to do so. (Bug #3401)

  • Fixed mysqlbinlog not to forget to print a USE statement under rare circumstances where the binary log contained a LOAD DATA INFILE statement. (Bug #3415)

  • Fixed a memory corruption when replicating a LOAD DATA INFILE when the master had version 3.23. (Bug #3422)

  • Multiple-table DELETE statements were always replicated by the slave if there were some --replicate-*-ignore-table options and no --replicate-*-do-table options. (Bug #3461)

  • Fixed a crash of the MySQL slave server when it was built with --with-debug and replicating itself. (Bug #3568)

  • Fixed that in some replication error messages, a very long query caused the rest of the message to be invisible (truncated), by putting the query last in the message. (Bug #3357)

  • If server-id was not set using startup options but with SET GLOBAL, the replication slave still complained that it was not set. (Bug #3829)

  • mysql_fix_privilege_tables didn't correctly handle the argument of its --password=password_val option. (Bug #4240)

  • Fixed potential memory overrun in mysql_real_connect() (which required a compromised DNS server and certain operating systems). (Bug #4017, CAN-2004-0836)

  • During the installation process of the server RPM on Linux, mysqld was run as the root system user, and if you had --log-bin=somewhere_out_of_var_lib_mysql it created binary log files owned by root in this directory, which remained owned by root after the installation. This is now fixed by starting mysqld as the mysql system user instead. (Bug #4038)

  • Made DROP DATABASE honor the value of lower_case_table_names. (Bug #4066)

  • The slave SQL thread refused to replicate INSERT ... SELECT if it examined more than 4 billion rows. (Bug #3871)

  • mysqlbinlog didn't escape the string content of user variables, and did not deal well when these variables were in non-ASCII character sets; this is now fixed by always printing the string content of user variables in hexadecimal. The character set and collation of the string is now also printed. (Bug #3875)

  • Fixed incorrect destruction of expression that led to a server crash on complex AND/OR expressions if query was ignored (either by a replication server because of --replicate-*-table rules, or by any MySQL server because of a syntax error). (Bug #3969, Bug #4494)

  • If CREATE TEMPORARY TABLE t SELECT failed while loading the data, the temporary table was not dropped. (Bug #4551)

  • Fixed that when a multiple-table DROP TABLE failed to drop a table on the master server, the error code was not written to the binary log. (Bug #4553)

  • When the slave SQL thread was replicating a LOAD DATA INFILE statement, it didn't show the statement in the output of SHOW PROCESSLIST. (Bug #4326)

D.1.21. Changes in release 5.0.0 (22 Dec 2003: Alpha)

Functionality added or changed:

  • The output of the SHOW BINLOG EVENTS statement has been modified. The Orig_log_pos column has been renamed to End_log_pos and now represents the offset of the last byte of the event, plus one.

  • 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.

  • Added support for SUM(DISTINCT), MIN(DISTINCT), and MAX(DISTINCT).

  • The KILL statement now takes CONNECTION and QUERY modifiers. The first is the same as KILL with no modifier (it kills a given connection thread). The second kills only the statement currently being executed by the connection.

  • Added TIMESTAMPADD() and TIMESTAMPDIFF() functions.

  • Added WEEK and QUARTER values as INTERVAL arguments for the DATE_ADD() and DATE_SUB() functions.

  • New binary log format that enables replication of these session variables: sql_mode, SQL_AUTO_IS_NULL, FOREIGN_KEY_CHECKS (which was replicated since 4.0.14, but here it's done more efficiently and takes less space in the binary logs), UNIQUE_CHECKS. Other variables (like character sets, SQL_SELECT_LIMIT, ...) will be replicated in upcoming 5.0.x releases.

  • Implemented Index Merge optimization for OR clauses. See Section 7.2.6, “Index Merge Optimization”.

  • Basic support for stored procedures (SQL:2003 style). See Chapter 17, Stored Procedures and Functions.

  • Added SELECT INTO list_of_vars, which can be of mixed (that is, global and local) types. See Section 17.2.7.3, “SELECT ... INTO Statement”.

  • Easier replication upgrade (5.0.0 masters can read older binary logs and 5.0.0 slaves can read older relay logs). See Section 6.5, “Replication Compatibility Between MySQL Versions”, for more details). The format of the binary log and relay log is changed compared to that of MySQL 4.1 and older.

Bugs fixed:

D.2. Changes in MySQL Cluster

Starting from 4.1.13 and 5.0.7, all Cluster changes are included in the MySQL Change History, and this manual section is no longer separately maintained.

D.2.1. Changes in MySQL Cluster-5.0.7 (Not yet released)

Note: Starting with version 5.0.8, changes for MySQL Cluster can be found in the combined MySQL Change History.

Functionality added or changed:

Bugs fixed:

  • (Bug #11019) mgmapi start backup in some cases returns wrong backupid

  • (Bug #10190) Backup from cluster wih NoOfReplica=1 is corrupt

  • (Bug #9246) Condition pushdown and left join, wrong result

  • (Bug #10956) More than 7 node restarts with --initial caused cluster to fail.

  • (Bug #9945) ALTER TABLE caused server crash. (Linux/390)

  • (Bug #9826) (Bug #10948) Schema change (DROP TABLE, ALTER TABLE) crashed HPUX and PPC32.

  • (Bug #10711) (Bug #9363) (Bug #8918) (Bug #10058) (Bug #9025) Cluster would time out and crash after first query; setting DataMemory to more than 2GB prevented cluster from starting; calling ndb_select_count() crashed the cluster. (64-bit Unix OSes)

D.2.2. Changes in MySQL Cluster-5.0.6 (26 May 2005)

Functionality added or changed:

  • Limit on number of metadata objects (number of tables, indexes and BLOBs) now increased to 20,320

Bugs fixed:

  • The server would hang on successive calls to an INSERT ... ON DUPLICATE KEY UPDATE query. (Bug #9725)

  • (Bug #10193) Invalid DataDir in config causes ndbd segmentation fault

  • (Bug #10813) Build with SCI Transporter fails

  • (Bug #10831) ndb mgmd LogDestination maxfiles does not rotate logs properly

D.2.3. Changes in MySQL Cluster-5.0.5 (Not released)

Functionality added or changed:

  • Decreased IndexMemory Usage

  • Parallel key lookup (read-multi-range) for queries like SELECT * FROM t1 WHERE primary_key IN (1,2,3,4,5,6,7,8,9,10);

Bugs fixed:

Patches merged from versions 4.1.11 and 4.1.12

  • (Bug #8315) NdbScanFilter cmp method only works for strings of exact word boundary length

  • (Bug #8103) Configuration handling error

  • (Bug #8035) mysqld signal 10 when ndbd is shutdown

  • (Bug #7631) NDB$EVENT contains unreadable event and table names

  • (Bug #7628) Filtered event types are ignored

  • (Bug #7627) Drop Event operation fails

  • (Bug #7424) create index on datetime fails

D.2.4. Changes in MySQL Cluster-5.0.4 (16 Apr 2005)

Functionality added or changed:

  • Condition pushdown to storage engine now works for update and delete as well

Bugs fixed:

  • (Bug #9675) Auto-increment not working with INSERT..SELECT and NDB storage

  • (Bug #9517) Condition pushdown to storage engine does not work for update/delete

  • (Bug #9282) API Node Crashes/Reloads on 'DELETE FROM'

  • (Bug #9280) Memory leak in cluster when dependent sub-queries are used

  • (Bug #8585) ndb_cache2 fails on aix52

D.2.5. Changes in MySQL Cluster-5.0.3 (23 Mar 2005: Beta)

Functionality added or changed:

  • Condition pushdown to storage engine

  • Query cache enabled for cluster

Bugs fixed:

  • Patches merged from version 4.1.10

D.2.6. Changes in MySQL Cluster-5.0.1 (27 Jul 2004)

Functionality added or changed:

  • This was the first MySQL Cluster release in the 5.0 series. As nearly all attention was still focused on getting 4.1 stable, it is not recommended to use MySQL 5.0.1 for MySQL Cluster.

Bugs fixed:

  • N/A

D.2.7. Changes in MySQL Cluster-4.1.13 (15 Jul 2005)

Functionality added or changed:

Bugs fixed:

  • (Bug #11132) Connections between data nodes and management nodes were not being closed following shutdown of ndb_mgmd.

  • (Bug #11050) ndb_mgm> show printed incorrectly after master data node failure.

  • (Bug #10956) More than 7 node restarts with --initial caused cluster to fail.

  • (Bug #9826) (Bug #10948) Schema change (DROP TABLE, ALTER TABLE) crashed HPUX and PPC32.

  • (Bug #9025) Data nodes failed to restart on 64-bit Solaris.

  • (Bug #11166) Insert records were incorrectly applied by ndb_restore, thus making restoration from backup inconsistent if the binlog contained inserts.

  • (Bug #8918) (Bug #9363) (Bug #10711) (Bug #10058) (Bug #9025) Cluster would time out and crash after first query; setting DataMemory to more than 2GB prevented cluster from starting; calling ndb_select_count() crashed the cluster. (64-bit Unix OSes)

  • (Bug #10190) When making a backup of a cluster where NumberOfReplicas was equal to 1, the backup's metadata was corrupted. (Linux)

  • (Bug #9945) ALTER TABLE caused server crash. (Linux/390)

  • (Bug #11133) A delete operation performed as part of a transaction caused an erroneous result.

  • (Bug #10294) Not allowing sufficient parallelism in cluster configuration (for example, NoOfTransactions too small) caused ndb_restore to fail without generating any error messages.

  • (Bug #11290) Setting TransactionInactiveTimeout= 0 did not result in an infinite timeout.

D.2.8. Changes in MySQL Cluster-4.1.12 (13 May 2005)

Functionality added or changed:

Bugs fixed:

  • (Bug #10471) Backup can become inconsistent with certain combinations of multiple-row updates

  • (Bug #10287) ndb_select_all "delimiter" option non functional

  • (Bug #10142) Unhandled resource shortage in UNIQUE index code

  • (Bug #10029) crash in ordered index scan after db full

  • (Bug #10001) 2 NDB nodes get signal 6 (abort) in DBTC

  • (Bug #9969) 4012 - has misleading error message

  • (Bug #9960) START BACKUP reports failure albeit succeeding

  • (Bug #9924) ABORT BACKUP 1 crashes 4 node cluster

  • (Bug #9892) Index activation file during node recovery

  • (Bug #9891) Crash in DBACC (line 7004) during commit

  • (Bug #9865) SELECT does not function properly

  • (Bug #9839) Column with AUTOINC contains -1 Value on node stop

  • (Bug #9757) Uncompleted node failure after gracefully stopping node

  • (Bug #9749) Transactions causes deadlock in ACC

  • (Bug #9724) Node fails to start: Message: File has already been opened

  • (Bug #9691) UPDATE fails on attempt to update primary key

  • (Bug #9675) Auto-increment not working with INSERT..SELECT and NDB storage

  • (Bug #9318) drop database does not drop ndb tables

  • (Bug #9280) Memory leak in cluster when dependent sub-queries are used

  • (Bug #8928) create table with keys will shutdown the cluster

  • Creating a table did not work for a cluster with 6 nodes. (Bug #8928) Databases with 1, 2, 4, 8, ... (2n nodes) did not have the problem. After a rolling upgrade, restart each node manually by restarting it with the --initial option. Otherwise, use dump and restore after an upgrade.

D.2.9. Changes in MySQL Cluster-4.1.11 (01 Apr 2005)

Functionality added or changed:

Bugs fixed:

  • (Bug #9916) DbaccMain.cpp / DBACC (Line: 4876) / Pointer too large

  • (Bug #9435) TIMESTAMP columns don't update

  • (Bug #9052) Uninitialized data during unique index build, potential cluster crash

  • (Bug #8876) Timeout when committing aborted transaction after node failure

  • (Bug #8786) ndb_autodiscover, drop index can fail, wait 2 minutes timeout

  • (Bug #8853) Transaction aborted after long time during node failure (4012)

  • (Bug #8753) Invalid schema object version after dropping index (crash fixed, currently retry required)

  • (Bug #8645) Assertion failure with multiple management servers

  • (Bug #8557) ndbd does not get same nodeid on restart

  • (Bug #8556) corrupt ndb_mgm show printout for certain configurations

  • (Bug #8167) cluster shared memory and mysqld signal usage clash

D.2.10. Changes in MySQL Cluster-4.1.10 (12 Feb 2005)

Bugs fixed:

D.2.11. Changes in MySQL Cluster-4.1.9 (13 Jan 2005)

Functionality added or changed:

  • New implementation of shared memory transporter.

  • Cluster automatically configures shared memory transporter if possible.

  • Cluster prioritizes usage of transporters with shared memory and localhost TCP

  • Added switches to control the above functions, ndb-shm and ndb-optimized-node-selection.

Bugs fixed:

  • (Bug #7805) config.ini parsing error

  • (Bug #7798) Running range scan after alter table in different thread causes node failure

  • (Bug #7761) Alter table does not autocommit

  • (Bug #7725) Indexed DATETIME Columns Return Random Results

  • (Bug #7660) START BACKUP does not increment BACKUP-ID (Big Endian machines)

  • (Bug #7593) Cannot Create A Large NDB Data Warehouse

  • (Bug #7480) Mysqld crash in ha_ndbcluster using Query Browser

  • (Bug #7470) shared memory transporter does not connect

  • (Bug #7396) Primary Key not working in NDB Mysql Clustered table (solaris)

  • (Bug #7379) ndb restore fails to handle blobs and multiple databases

  • (Bug #7346) ndb_restore enters infinite loop

  • (Bug #7340) Problem for inserting data into the Text field on utf8

  • (Bug #7124) ndb_mgmd is aborted on startup when using SHM connection

D.2.12. Changes in MySQL Cluster-4.1.8 (14 Dec 2004)

Functionality added or changed:

  • Default port for ndb_mgmd was changed to 1186 (from 2200) as this port number was officially assigned to MySQL Cluster by IANA.

  • New command in ndb_mgm, PURGE STALE SESSIONS, as a workaround for cases where nodes fail to allocate a node id even if it is free to use.

  • New command in ndb_mgm, CONNECT.

  • The ndb executables have been changed to make use of the regular MySQL command line option parsing features. See Section 15.5.5, “Command Options for MySQL Cluster Processes”, for notes on changes.

  • As bonus of the above you can now specify all command line options in my.cnf using the executable names as sections, that is, [ndbd], [ndb_mgmd], [ndb_mgm], [ndb_restore], and so forth.

          [ndbd]
          ndb-connectstring=myhost.domain.com:1234
          [ndb_mgm]
          ndb-connectstring=myhost.domain.com:1234
    
  • Added use of section [mysql_cluster] in my.cnf. All cluster executables, including mysqld, parse this section. For example, this is a convenient place to put ndb-connectstring so that it need be specified only once.

  • Added cluster log info events on allocation and deallocation of nodeid's.

  • Added cluster log info events on connection refuse as a result of version mismatch.

  • Extended connectstring syntax to allow for leaving the port number out. For example, ndb-connectstring|connect-string=myhost1,myhost2,myhost3 is a valid connectstring and connect occurs on default port 1186.

  • Clear text ndb error messages provided also for error codes that are mapped to corresponding mysql error codes, by executing SHOW WARNINGS after an error has occurred which relates to the ndb storage engine.

  • Significant performance improvements done for read performance, especially for blobs.

  • Added some variables for performance tuning, ndb_force_send and ndb_use_exact_count. Do show variables like 'ndb%'; in mysql client for listing. Use set command to alter variables.

  • Added variables to set some options, ndb_use_transactions and ndb_autoincrement_prefetch_sz.

Bugs fixed:

  • (Bug #7303) ndb_mgm: Trying to set CLUSTERLOG for a specific node id core dumps

  • (Bug #7193) start backup gives false error printout

  • (Bug #7153) Cluster nodes don't report error on endianness mismatch

  • (Bug #7152) ndb_mgmd segmentation fault on incorrect HostName in configuration

  • (Bug #7104) clusterlog filtering and level setting broken

  • (Bug #6995) ndb_recover on varchar fields results in changing case of data

  • (Bug #6919) all status only shows 2 nodes on a 8-node cluster

  • (Bug #6871) DBD execute failed: Got error 897 'Unknown error code' from ndbcluster

  • (Bug #6794) Wrong outcome of update operation of ndb table

  • (Bug #6791) Segmentation fault when config.ini is not correctly set

  • (Bug #6775) failure in acc when running many mysql clients

  • (Bug #6696) ndb_mgm command line options inconsistent with behavior

  • (Bug #6684) ndb_restore doesn't give error messages if improper command given

  • (Bug #6677) ndb_mgm can crash on "ALL CLUSTERLOG"

  • (Bug #6538) Error code returned when select max() on empty table with index

  • (Bug #6451) failing create table givers "ghost" tables which are impossible to remove

  • (Bug #6435) strange behavior of left join

  • (Bug #6426) update with long pk fails

  • (Bug #6398) update of primary key fails

  • (Bug #6354) mysql does not complain about --ndbcluster option when NDB is not compiled in

  • (Bug #6331) INSERT IGNORE .. SELECT breaks subsequent inserts

  • (Bug #6288) cluster nodes crash on data import

  • (Bug #6031) To drop database you have to execute DROP DATABASE command twice

  • (Bug #6020) LOCK TABLE + delete returns error 208

  • (Bug #6018) REPLACE does not work for BLOBs + NDB

  • (Bug #6016) Strange crash with blobs + different DATABASES

  • (Bug #5973) ndb table belonging to different database shows up in show tables

  • (Bug #5872) ALTER TABLE with blob from ndb table to myisam fails

  • (Bug #5844) Failing mysql-test-run leaves stray NDB processes behind

  • (Bug #5824) HELP text messed up in ndb_mgm

  • (Bug #5786) Duplicate key error after restore

  • (Bug #5785) lock timeout during concurrent update

  • (Bug #5782) Unknown error when using LIMIT with ndb table

  • (Bug #5756) RESTART node from ndb_mgm fails

  • A few more not reported bugs fixed

D.2.13. Changes in MySQL Cluster-4.1.7 (23 Oct 2004)

Functionality added or changed:

  • Optimization 1: Improved performance on index scans. Measured 30% performance increase on query which do large amounts of index scans.

  • Optimization 2: Improved performance on primary key lookups. Around double performance for autocommitted primary key lookups.

  • Optimization 3: Improved performance when using blobs by avoiding usage of exclusive locks for blobs.

Bugs fixed:

  • A few bugs fixed.

D.2.14. Changes in MySQL Cluster-4.1.6 (10 Oct 2004)

Functionality added or changed:

  • Limited character set support for storage engine NDBCLUSTER:

    Char setCollation
    big5big5_chinese_ci
     big5_bin
    binarybinary
    euckreuckr_korean_ci
     euckr_bin
    gb2312gb2312_chinese_ci
     gb2312_bin
    gbkgbk_chinese_ci
     gbk_bin
    latin1latin1_swedish_ci
     latin1_bin
    sjissjis_japanese_ci
     sjis_bin
    tis620tis620_bin
    ucs2ucs2_general_ci
     ucs2_bin
    ujisujis_japanese_ci
     ujis_bin
    utf8utf8_general_ci
     utf8_bin
  • The SCI Transporter has been brought up-to-date with all changes and now works and has been documented as well.

  • Optimizations when several clients to a MySQL Server access ndb tables.

  • Added more checks and warnings for erroneous and inappropriate cluster configurations.

  • SHOW TABLES now directly shows ndb tables created on a different MySQL server, that is, without a prior table access.

  • Enhanced support for starting MySQL Server independently of ndbd and ndb_mgmd.

  • Clear text ndb error messages provided by executing SHOW WARNINGS after an error has occurred which relates to the ndb storage engine.

Bugs fixed:

  • Quite a few bugs fixed.

D.2.15. Changes in MySQL Cluster-4.1.5 (16 Sep 2004)

Functionality added or changed:

  • Many queries in MySQL Cluster are executed as range scans or full table scans. All queries that don't use a unique hash index or the primary hash index use this access method. In a distributed system it is crucial that batching is properly performed.

    In previous versions, the batch size was fixed to 16 per data node. In this version it is configurable per MySQL Server. So for queries using lots of large scans it is appropriate to set this parameter rather large and for queries using many small scans only fetching a small amount of records it is appropriate to set it low.

    The performance of queries can easily change as much as 40% based on how this variable is set.

    In future versions more logic will be implemented for assessing the batch size on a per-query basis. Thus, the semantics of the new configuration variable ScanBatchSize are likely to change.

  • The fixed size overhead of the ndbd process has been greatly decreased. This is also true for the overhead per operation record as well as overhead per table and index.

    A number of new configuration variables have been introduced to enable configuration of system buffers. Configuration variables for specifying the numbers of tables, unique hash indexes, and ordered indexes have also been introduced.

    New configuration variables: MaxNoOfOrderedIndexes, MaxNoOfUniqueHashIndexes

    Configuration variables no longer used: MaxNoOfIndexes (split into the two above).

  • In previous versions ALTER TABLE, TRUNCATE TABLE, and LOAD DATA were performed as one big transaction. In this version, all of these statements are automatically separated into several distinct transactions.

    This removes the limitation that one could not change very large tables due to the MaxNoOfConcurrentOperations parameter.

  • MySQL CLuster's online backup feature now backs up indexes so that both data and indexes are restored.

  • In previous versions it was not possible to use NULL in indexes. This is now possible for all supported index types.

  • Much work has been put onto making AUTO_INCREMENT features work as for other table handlers. Autoincrements as a partial key is still only supported by MyISAM.

  • In earlier versions, mysqld would crash if the cluster wasn't started with the --ndbcluster option. Now mysqld handles cluster crashes and starts without crashing.

  • The -i option for initial startup of ndbd has been removed. Initial startup still can be specified by using the --initial option. The reason for this is to ensure that it is clear what takes place when using --initial: this option completely removes all data from the disk and should only be used at initial start, in certain software upgrade cases, and in some cases as a workaround when nodes cannot be restarted successfully.

  • The management client (ndb_mgm) now has additional commands and more information is printed for some commands such as show.

  • In previous versions, the files were called ndb_0.. when it wasn't possible to allocate a node ID when starting the node. To ensure that files are not so easily overwritten, these files are now named ndb_pid.., where pid is the process ID assigned by the OS.

  • The default parameters have changed for ndb_mgmd and ndbd. In particular, they are now started as daemons by default. The -n option has been removed since it could cause confusion as to its meaning (nostart or nodaemon).

  • In the configuration file, you can now use [NDBD] as an alias for [DB], [MYSQLD] as an alias for [API], and [NDB_MGMD] as an alias for [MGM]. Note: In fact, [NDBD], [MYSQLD], and [NDB_MGMD] are now the preferred designations, although the older ones will continue to be supported for some time to come in order to maintain backward compatibility.

  • Many more checks for consistency in configuration have been introduced to in order to provide quicker feedback on configuration errors.

  • In the connect string, it is now possible to use both ‘;’ and ‘,’ as the separator between entries. Thus, "nodeid=2,host=localhost:2200" is equivalent to "nodeid=2;host=localhost:2200".

    In the configuration file, it is also possible to use ‘:’ or ‘=’ for assignment values. For example, MaxNoOfOrderedIndexes : 128 and MaxNoOfOrderedIndexes = 128 are equivalent expressions.

  • The configuration variable names are now case insensitive, so MaxNoOfOrderedIndexes: 128 is equivalent to MAXNOOFORDEREDINDEXES = 128.

  • It is possible now to set the backup directory separately from the FileSystemPath by using the BackupDir configuration variable.

    Log files and trace files can now be placed in any directory by setting the DataDir configuration variable.

    FileSystemPath is no longer mandatory and defaults to DataDir.

  • Queries involving tables from different databases are now supported.

  • It is now possible to update the primary key.

  • The performance of ordered indexes has been greatly improved, particularly the maintenance of indexes on updates, inserts and deletes.

Bugs fixed:

  • Quite a few bugs fixed.

D.2.16. Changes in MySQL Cluster-4.1.4 (31 Aug 2004)

Functionality added or changed:

  • The names of the log files and trace files created by the ndbd and ndb_mgmd processes have changed.

  • Support for the many BLOB data types was introduced in this version.

Bugs fixed:

  • Quite a few bugs were fixed in the 4.1.4 release.

D.2.17. Changes in MySQL Cluster-4.1.3 (28 Jun 2004)

Functionality added or changed:

  • This was the first MySQL Cluster release so all functionality was new.

Bugs fixed:

  • Various bugs fixed in the development process leading up to 4.1.3.

D.3. Changes in MyODBC

D.3.1. Changes in MyODBC 3.51.13

Functionality added or changed:

  • N/A

Bugs fixed:

  • The SQLDriverConnect() ODBC method did not work with recent MyODBC releases. (Bug #12393)

D.3.2. Changes in MyODBC 3.51.12

Functionality added or changed:

  • N/A

Bugs fixed:

  • File DSNs could not be saved. (Bug #12019)

  • SQLColumns() returned no information for tables that had a column named using a reserved word. (Bug #9539)

D.3.3. Changes in MyODBC 3.51.11

Functionality added or changed: No changes.

Bugs fixed:

  • mysql_list_dbcolumns() and insert_fields() were retrieving all rows from a table. Fixed the queries generated by these functions to return no rows. (Bug #8198)

  • SQLGetTypoInfo() returned tinyblob for SQL_VARBINARY and nothing for SQL_BINARY. Fixed to return varbinary for SQL_VARBINARY, binary for SQL_BINARY, and longblob for SQL_LONGVARBINARY. (Bug #8138)