Added back faster subquery execution from 4.1.8. This adds also back a bug from 4.1.8 in comparing NULL to the value of a subquery. See Section A.8.4, “Open Issues in MySQL”.

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 <stefano.dipaola@wisec.it> for finding and informing us about this issue. (CVE-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 19.2.4.1, “UDF Calling Sequences for Simple Functions”, Section 19.2.4.2, “UDF Calling Sequences for Aggregate Functions”, and Section 19.2.4.6, “User-Defined Function Security Precautions”. Thanks to Stefano Di Paola <stefano.dipaola@wisec.it> for finding and informing us about this issue. (CVE-2005-0709, CVE-2005-0710)

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)

Thread stack size was increased from 192KB to 256KB on Linux/IA-64 (too small stack size was causing server crashes on some queries). (Bug#8391)

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)

The server now issues a warning when lower_case_table_names=2 and the data directory is on a case-sensitive filesystem, just as when lower_case_table_names=0 on a case-insensitive filesystem. (Bug#7887)

The server now issues a warning to the error log when it encounters older tables that contain character columns that might be interpreted by newer servers to have a different column length. (Bug#6913) See Section 2.11.1, “Upgrading from MySQL 4.0 to 4.1”, for a discussion of this problem and what to do about it.

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: 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.

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)

Fixed a bug in cardinality estimations for HASH indexes of TEMPORARY tables created using MEMORY storage engine. As a result queries that were using this index (as shown by EXPLAIN) could have returned incorrect results. (Bug#8371)

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)

mysqld had problems finding its language files if the basedir value was specified as a very long pathname. (Bug#8015)

Updates were being written to the binary log when there were binlog-do-db or binlog-ignore-db options even when there was no current database, contrary to Section 5.11.4, “The Binary Log”. (Bug#6749)

Fixed conversion of floating-point values to character fields when the absolute value of the float was less than 1, and also fixed calculation of length for negative values. (Bug#7774)

Column headers in query results retrieved from the query cache could be corrupted when a non-4.1 client was served a result originally generated for a 4.1 client. The query cache was not keeping track of which client/server protocol was being used. (Bug#6511)

Fixed LOAD INDEX statement to actually load index in memory. (Bug#8452)

If multiple prepared statements were executed without retrieving their results, executing one of them again would cause the client program to crash. (Bug#8330)

Non-numeric values inserted into a YEAR column were being stored as 2000 rather than as 0000. (Bug#6067)

Fixed a failure of multiple-table updates to replicate properly on slave servers when --replicate-*-table options had been specified. (Bug#7011)

mysql_stmt_close() C API function was not clearing an error indicator when a previous prepare call failed, causing subsequent invocations of error-retrieving calls to indicate spurious error values. (Bug#7990)

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) HAVING that referred to RAND() or a user-defined function in the SELECT part through an alias could cause a crash or wrong value. (Bug#8216)

If one used CONVERT_TZ() function in SELECT, which in its turn was used in CREATE TABLE statements, then system time zone tables were added to list of tables joined in SELECT and thus erroneous result was produced. (Bug#7899)

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().

Fixed problem with SHOW INDEX reporting Sub_part values in bytes rather than characters for columns with a multi-byte character set. (Bug#7943)

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)

Correct a problem with mysql_config, which was failing to produce proper zlib option for linking under some circumstances. (Bug#6273)

Fixed a problem with calculation of number of columns in row comparison against a 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 (referring 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)

Removed a dependence of boolean full-text search on --default-character-set option. (Bug#8159)

Fixed a crash in a boolean full-text search in certain joins. (Bug#8234)

Fixed erroneous comparison where strings that began with CHAR(31) were considered equal to the empty string. (Bug#8134)

Add description of debug command to mysqladmin help output. (Bug#8207)

perror.exe was always returning “Unknown error” on Windows. See Section 8.17, “perror — Explain Error Codes”. (Bug#7390)

Modify SET statements produced by mysqldump to write quoted strings using single quotes rather than double quotes. This avoids problems if the dump file is reloaded while the ANSI_QUOTES SQL mode is in effect. (Bug#8148)

Fixed a bug where ALTER TABLE improperly would accept an index on a TIMESTAMP column that CREATE TABLE would reject. (Bug#7884)

Fixed a bug in multiple-table UPDATE statements that could cause spurious Table '#sql_....' is full errors if the number of rows to update is big enough. (Bug#7788)

Fixed a problem where SHOW INDEX on a MERGE table could crash a debugging version of the server. (Bug#7377)

Fixed a problem where adding an ORDER BY clause for an indexed column would cause a SELECT to return an empty result. (Bug#7331)

Fixed a problem where ALTER TABLE on a TEMPORARY table with a mixed-lettercase name could cause the table to disappear when lower_case_table_names was set to 2. (Bug#7261)

Fixed a problem with key cache statistics being reported incorrectly by the server after receipt of a SIGHUP signal. (Bug#4285)

Fixed a problem that caused mysql_stmt_prepare() to be very slow when used in client programs on Windows. (Bug#5787)

For indexes, SHOW CREATE TABLE now displays the index type even if it is the default, for storage engines that support multiple index types. (Bug#7235)

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 which caused TIMEDIFF() function to return wrong results if one of its arguments had non-zero microsecond part (Bug#7586).

Fixed a bug which caused TIMESTAMP columns with display width specified to be not identical to DATETIME columns when server was run in MAXDB mode (Bug#7418).

Fixed a bug in UNION statements that resulted in the wrong number of the examined rows reported in the slow query log.

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: Fixed a bug introduced in 4.1.9 to the Windows version if you used innodb_file_per_table. mysqld would stop and complain about Windows error number 87 in a file operation. (See the Bugs database or the 4.1.9 change notes about a workaround for that bug in 4.1.9). (Bug#8021)

InnoDB: Corrected the handling of trailing spaces in the ucs2 character set. (Bug#7350)

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.

InnoDB: Fix a race condition that could cause the assertion space->n_pending_flushes == 0 to fail in fil0fil.c, in fil_space_free(), in DROP TABLE or in ALTER TABLE.

InnoDB: ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad foreign key definition. (Bug#7831)

InnoDB: Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs INSERT ... SELECT ... (binlog not enabled), or a multiple-table UPDATE or DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug#7879)

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. (CVE-2005-0004)

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)

Changed mysql client so that including \p as part of a prompt command uses the name of the shared memory connection when the connection is using shared memory. (Bug#7922)

Fixed a problem in the server where executing a multi-statement query more than once with the query cache active could yield incorrect result sets. (Bug#7966)

Fixed that a 4.1.10 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)

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)