Version History of MySQL Server 4.1.2
Functionality added or changed:
- Security Fix: The --defaults-file=file_name option now requires that the file name must exist. (Bug#3413)
- Incompatible Change: String comparison now works according to the SQL standard. Because we have that 'a' = 'a ' then from it must follow that 'a' > 'a\t'. (The latter was not the case before MySQL 4.1.2.) To implement it, we had to change how storage engines compare strings internally. As a side effect, if you have a table where a CHAR or VARCHAR column in some row has a value with the last character less than ASCII(32), you have to repair this table. CHECK TABLES tells you if this problem exists. (Bug#3152)
- Incompatible Change: The Type output column for SHOW TABLE STATUS now is labeled Engine.
- Incompatible Change: Handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Previously, the server permitted 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 permissible value of 99.9. If you have tables that were created before MySQL 4.1.2 and that contain floating-point data not strictly legal for the column type, you should alter the data types of those columns. For example:
- ALTER TABLE tbl_name MODIFY col_name FLOAT(4,1);
- Incompatible Change: Added support for DEFAULT CURRENT_TIMESTAMP and for ON UPDATE CURRENT_TIMESTAMP specifications for TIMESTAMP columns. Now you can explicitly say that a TIMESTAMP column should be set automatically to the current timestamp for INSERT or UPDATE statements, or even prevent the column from updating automatically. Only one column with such an auto-set feature per table is supported. TIMESTAMP columns created with earlier versions of MySQL behave as before. Behavior of TIMESTAMP columns that were created without explicit specification of default/on as earlier depends on its position in table: If it is the first TIMESTAMP column, it be treated as having been specified as TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. In other cases, it would be treated as a TIMESTAMP DEFAULT 0 column. NOW is supported as an alias for CURRENT_TIMESTAMP.
Unlike in previous versions, explicit specification of default values for TIMESTAMP column is never ignored and turns off the auto-set feature (unless you have CURRENT_TIMESTAMP as the default).
- Incompatible Change: Renamed prepared statements C API functions:
- Now all functions that operate with a MYSQL_STMT structure begin with the prefix mysql_stmt_.
- Incompatible Change: The signature of the mysql_stmt_prepare() function was changed to int mysql_stmt_prepare(MYSQL_STMT *stmt, const char *query, unsigned long length). To create a MYSQL_STMT handle, you should use the mysql_stmt_init() function, not mysql_stmt_prepare().
- Replication: 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 the master's startup. This is for the case where the slave has replicated a nonempty MEMORY table, and then the master is shut down and restarted: the table is now empty on the master; the DELETE FROM empties it on the slave as well.
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. However, 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)
- Replication: UUID() function implemented. Note that it does not work with replication yet. See Section 11.14, “Miscellaneous Functions”.
- Replication: Added option --replicate-same-server-id.
- The FLOAT and DECIMAL types now obey (precision,scale) settings. (Bug#10897)
- REVOKE ALL PRIVILEGES, GRANT FROM user_list is changed to a more consistent REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_list. (Bug#2642)
- Now sql_select_limit variable has no influence on subqueries. (Bug#2600)
- ALTER TABLE DROP PRIMARY KEY no longer drops the first UNIQUE index if there is no primary index. (Bug#2361)
- MySQL now issues a warning when a SET or ENUM column with duplicate values in the list is created. (Bug#1427)
- A name of “Primary” no longer can be specified as an index name. (That name is reserved for the PRIMARY KEY if the table has one.) (Bug#856)
- SHOW GRANTS with no FOR clause or with FOR CURRENT_USER() shows the privileges for the current session.
- mysqld_multi now creates the log in the directory named by datadir (from the [mysqld] section in my.cnf or compiled in), not in /tmp. Thanks to Christian Hammers from Debian Security Team for reporting this. (CVE-2004-0388)
- 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 support for character set conversion and MYSQL_TYPE_BLOB type code in prepared statement protocol.
- 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.
- Added option --to-last-log to mysqlbinlog, for use in conjunction with --read-from-remote-server.
- mysql command-line client now supports multiple -e options. (Bug#591)
- New myisam_data_pointer_size system variable. See Section 5.1.3, “Server System Variables”.
- MySQL now supports up to 64 indexes per table.
- The Mac OS X Startup Item has been moved from the directory /Library/StartupItems/MySQL to /Library/StartupItems/MySQLCOM to avoid a file name collision with the MySQL Startup Item installed with Mac OS X Server. See Section 2.12.2, “Mac OS X Notes”.
- Multi-line statements in the mysql command-line client now are stored in the history file as a single line.
- UNHEX() function implemented. See Section 11.5, “String Functions”.
- Added the ENGINE table option as a synonym for the TYPE option for CREATE TABLE and ALTER TABLE.
- The --log-warnings server option now is enabled by default. Disable with --skip-log-warnings.
- Internal string-to-number conversion now supports only SQL:2003 compatible syntax for numbers. In particular, '0x10'+0 does not work anymore. (Actually, it worked only on some systems before, such as Linux. It did not work on others, such as FreeBSD or Solaris. Making these queries OS-independent was the goal of this change.) Use CONV() to convert hexadecimal numbers to decimal. Example: CONV(MID('0x10',3),16,10)+0.
- Added the EXAMPLE storage engine.
- Added explanation of hidden SELECT of UNION in output of EXPLAIN SELECT statement.
- Added the storage_engine system variable as a synonym for table_type.
- Added Binlog_cache_use and Binlog_cache_disk_use status variables that count the number of transactions that used transaction binary log and that had to flush this temporary binary log to disk instead of using only the in-memory buffer. They can be used for tuning the binlog_cache_size system variable.
- Added latin1_spanish_ci (Modern Spanish) collation for the latin1 character set.
- The improved character set support introduced in MySQL 4.1.0 for the MyISAM and HEAP storage engines is now available for InnoDB as well.
- If you try to create a key with a key part that is too long, and it is safe to auto-truncate it to a smaller length, MySQL now does so. A warning is generated, rather than an error.
- The mysql command-line client no longer stores in the history file multiple copies of identical queries that are run consecutively.
- C API enhancement: SERVER_QUERY_NO_INDEX_USED and SERVER_QUERY_NO_GOOD_INDEX_USED flags are now set in the server_status field of the MYSQL structure. It is these flags that make the query to be logged as slow if mysqld was started with --log-slow-queries --log-queries-not-using-indexes.
- Added Handler_discover status variable.
- Added the mysql_set_local_infile_handler() and mysql_set_local_infile_default() C API functions.
- Added init_connect and init_slave system variables. The values should be SQL statements to be executed when each client connects or each time a slave's SQL thread starts, respectively.
- The MySQL server now returns an error if SET sql_log_bin or SET sql_log_update is issued by a user without the SUPER privilege (in previous versions it just silently ignored the statement in this case).
- The mysqld Windows server was renamed to mysqld-debug. See Section 2.3.8, “Selecting a MySQL Server Type”.
- MyISAM and InnoDB tables now support index prefix lengths up to 1000 bytes long.
- Added the --default-storage-engine server option as a synonym for --default-table-type.
- Prepared statements now work with all types of subqueries.
- mysqlhotcopy now works on NetWare.
- CHAR BYTE is an alias for the BINARY data type. (Previously, it was an alias for CHAR BINARY.)
- MyISAM tables now support keys up to 1000 bytes long.
- The ft_boolean_syntax variable now can be changed while the server is running. See Section 5.1.3, “Server System Variables”.
- Packaging: In the Mac OS PKG postinstall script, mysql_install_db was invokrd with an invalid argument.
- Replication: 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)
- Replication: A MySQL slave server built using --with-debug, and replicating itself, crashed. (Bug#3568)
- Replication: 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)
- Replication: Memory could be corrupted by replicating a LOAD DATA INFILE from a MySQL 3.23 master. Some less critical issues remain; see Section 14.7, “Replication Features and Issues”. (Bug#3422)
- Replication: 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)
- Replication: 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)
- Replication: Corrected the master's binary log position that InnoDB reports when it is doing a crash recovery on a slave server. (Bug#3015)
- Replication: --replicate-wild-*-table rules now apply to ALTER DATABASE when the table pattern is %, as is the case for CREATE DATABASE and DROP DATABASE. (Bug#3000)
- Replication: Statements did not raise errors on the slave, if the slave was excluded given the --replicate-* options in use at the time. The effect of this problem was: when a statement was killed on the master, the slave stopped. (Bug#2983)
- Replication: CREATE TABLE ... LIKE ... statements were not always written to the binary log. (Bug#2557)
- Replication: Multiple-table DELETE statements were never replicated by the slave if there were any --replicate-*-table options. (Bug#2527)
- Replication: If a replication slave was unable to create the first relay log, it crashed. (Bug#2145)
- Replication: Removed a misleading "check permissions on master.info" from a replication error message, because the cause of the problem could be different from permissions. (Bug#2121)
- Replication: Following a nonfatal error during the execution of a statement that later succeeded, the master failed to reset the error code to 0, so the error code was written into the binary log. This caused false Did not get the same error as on master errors on the slave. (Bug#2083)
- Replication: Replication: a rare race condition in the slave SQL thread that could lead to an incorrect complaint that the relay log is corrupted. (Bug#2011)
- Replication: 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)
- Replication: Replication: in the slave SQL thread, a multiple-table UPDATE could produce an incorrect complaint that some record was not found in one table, if the UPDATE was preceded by a INSERT ... SELECT. (Bug#1701)
- REPAIR TABLE could corrupt a table containing FULLTEXT indexes and many words of different lengths that are considered equal (which is possible in certain collations, such as latin1_german2_ci or utf8_general_ci). (Bug#3835)
- Starting mysqld with binary logging disabled, but with a nonzero value for the expire_logs_days system variable caused the server to crash. (Bug#3807)
- Using the GROUP_CONCAT() function on an expression with ORDER BY as well as an external ORDER BY in a query caused the server to crash. (Bug#3752)
- The results of a query that used DISTINCT and ORDER BY by a column's real name, while the column had an alias specified in the SELECT clause, were not returned in the correct order. (Bug#3681)
- Optimization of ALL and SOME subqueries was not performed well (key field present in subquery). (Bug#3646)
- mysqld could crash when a table was altered and used at the same time. This was a 4.1.2-specific bug. . (Bug#3643)
- EXPLAIN should now work correctly with UNION queries. (Bug#3639)
- The second execution of a prepared statement using UNION caused the server to crash. (Bug#3577)
- Results of aggregate functions used in subqueries with empty result sets were incorrect. (Bug#3505)
- mysqlbinlog failed to print a USE statement under those rare circumstances where the binary log contained a LOAD DATA INFILE statement. (Bug#3415)
- Incorrect error message when wrong table used in multiple-table DELETE statement in prepared statements. (Bug#3411)
- Prepared statements are supported for INSERT, REPLACE, CREATE, DELETE, SELECT, DO, SET and SHOW statements. All other statements are now prohibited by the prepared statement interface. (Bug#3406, Bug#3398, Bug#2811)
- A password was not checked for changes in GRANT accounts until FLUSH PRIVILEGES was executed. (Bug#3404)
- SHOW GRANTS and EXPLAIN SELECT did not always perform character set conversion correctly. (Bug#3403)
- A rare error condition 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)
- The Exec_master_log_pos column and its disk image in the relay-log.info were not handled correctly if the master had version 3.23. (The value was too big by six bytes.) This bug does not exist in MySQL 5.0. (Bug#3400)
- Aggregate functions could lead to server crashes when used in prepared statements. (Bug#3360)
- FLUSH TABLES sometimes corrupted table resolution for statements which were prepared before the FLUSH TABLES but which were being executed repeatedly afterward. (Bug#3307)
- On Linux platforms, setting the character_set_results variable to NULL and then attempting to retrieve it using SELECT @@character_set_results caused the server to crash. (Bug#3296)
- There was a symlink vulnerability in the mysqlbug script. (Bug#3284)
- Privileges were not checked correctly for ALTER TABLE RENAME. (Bug#3270)
- Attempting to bind a negative value bind to unsigned caused an Unknown error. (Bug#3223)
- mysqlbinlog --read-from-remote-server now print the exact positions of events in lines beginning with at # in the log. (Bug#3214)
- 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)
- CONCAT_WS() makes the server die in case of illegal mix of collations. (Bug#3087)
- A memory leak occurred in the client library when a statement handle was freed on a closed connection (call to mysql_stmt_close() after mysql_close()). (Bug#3073)
- 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)
- Added support for unsigned integer types to prepared statement API . (Bug#3035)
- A prepared statement using parameters and having a subquery in the FROM clause could cause the server to crash. (Bug#3020)
- When a Rotate event was found by the slave SQL thread in the middle of a transaction, the value of Relay_Log_Pos in SHOW SLAVE STATUS was incorrectly altered. (Bug#3017)
- Full-text search on multi-byte character sets (such as UTF8) that appeared when a search word was shorter than a matching word from the index (for example, searching for “Uppsala” when the table contains “Uppsa*la”). (Bug#3011)
- UTF8 charset breaks joins with mixed column/string constant. (Bug#2959)
- When a password was assigned to an account at the global level and then privileges were granted at the database level (without specifying any password), the existing password was replaced temporarily in memory until the next FLUSH PRIVILEGES operation or the server was restarted. (Bug#2953)
- Running LOAD DATA FROM MASTER after RESET SLAVE caused a segmentation fault. (Bug#2922)
- A deadlock ocurred when two START SLAVE statements were run at the same time. (Bug#2921)
- Comparison of table and database names when using the --lower_case_table_names option was not always performed correctly. (Bug#2880)
- Subqueries with OR and AND did not always work correctly. (Bug#2838)
- Changed the column Seconds_Behind_Master in SHOW SLAVE STATUS to never show a value of -1. (Bug#2826)
- Table default character set affects LONGBLOB columns. (Bug#2821)
- Segmentation faults could occur when processing malformed prepared statements. (Bug#2795, Bug#2274)
- Made clearer the error message that one gets when an update is refused because of the --read-only option. (Bug#2757)
- Index_length in HEAP table status for BTREE indexes was not calculated correctly. (Bug#2719)
- MATCH ... AGAINST() on a phrase search operator with a missing closing double quote caused the server to crash. (Bug#2708)
- The output of mysqldump --tab was not correct. (Bug#2705)
- 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)
- An issue with the range optimizer caused a segmentation fault on some very rare queries. (Bug#2698)
- The GROUP_CONCAT() had a number of issues with ORDER BY and DISTINCT, and with GROUP BY in subqueries. (Bug#2695, Bug#3319, Bug#3381)
- Processing of RAND() in subqueries with static tables was not always handled correctly. (Bug#2645)
- When ALTER TABLE RENAME, was used to rename a table with the same name in another database, it silently dropped the destination table if it existed. (Bug#2628)
- SHOW CREATE TABLE ... did not properly double quotation marks. (Bug#2593)
- mysqldump did not quote names containing backtick characters (`) correctly. (Bug#2592)
- Table names in were quoted in mysqldump when using values for the server SQL mode where this was not appropriate. (Bug#2591)
- UNION operations with the InnoDB storage engine, when some columns from one table were used in one SELECT statement and some were used in another SELECT statement, were not handled correctly. (Bug#2552)
- Table and column privileges were not loaded on startup. (Bug#2546)
- UNION operations did not handle NULL columns properly, when a column in the first SELECT node was NOT NULL. (Bug#2508)
- A memory leak could occur with INSERT ... ON DUPLICATE KEY UPDATE. (Bug#2438)
- Subqueries in the FROM clause were not always parsed correctly. (Bug#2421)
- Write operations on a column hvaing a FULLTEXT index could under some rare circumstances lead to table file corruption. (Bug#2417)
- Removed try to check NULL if index built on column where NULL is impossible in IN subquery optimization. (Bug#2393)
- ANALYZE TABLE on a BDB table inside a transaction caused the server to hang. (Bug#2342)
- ALTER DATABASE caused the client to hang if the database did not exist. (Bug#2333)
- Short-form IP addresses used as arguments to INET_ATON() were not parsed correctly. (Bug#2310)
- Compile the MySQL-client RPM package against libreadline instead of libedit. (Bug#2289)
- mysql_stmt_fetch() and mysql_stmt_store_result() could hang if they were called without a prior call to mysql_stmt_execute(). Now they give an error instead. (Bug#2248)
- mysql_stmt_affected_rows() now always returns the number of rows affected by a given statement. (Bug#2247)
- Using an impossible WHERE with PROCEDURE ANALYSE() caused the server to hang. (Bug#2238)
- The mysql client program crashed when passed a database name that was longer then expected. (Bug#2221)
- Invalid results were returned when CAST() was applied to NULL to obtain a signed or unsigned integer value. (Bug#2219)
- Invoking mysql_set_server_option() caused client/server communications to be broken. (Bug#2207)
- CHECK TABLE sometimes produced a spurious error Found key at page ... that points to record outside datafile for a table with a FULLTEXT index. (Bug#2190)
- GRANT did not handle table-level privileges correctly. (Bug#2178)
- ORDER BY did not always work correctly with SMALLINT columns. (Bug#2147)
- Queries with subqueries in the FROM clause now lock all tables at once. EXPLAIN of subqueries in FROM output was also not handled correctly. (Bug#2120)
- DOUBLE and FLOAT columns could store out of range values. (Bug#2082)
- Full-text indexing of strings in multi-byte (all besides utf8) charsets could sometimes hang. (Bug#2065)
- Added optimization that enables prepared statements using a large number of tables or tables with a large number of columns to be re-executed significantly faster. (Bug#2050)
- Full-text indexing of UTF8 data did not work correctly. (Bug#2033)
- vio_timeout() virtual function was not set for all protocols. This lead to crashes on Windows. (Bug#2025)
- myisamchk and CHECK TABLE that sometimes a spurious error Found key at page ... that points to record outside datafile for a table with a FULLTEXT index. (Bug#1977)
- mysql_stmt_send_long_data() misbehaved on the second execution of a prepared statement when long data had zero length. (Bug#1664)
- Compiling the server using the --with-pstack options did not work with binutils 2.13.90. (Bug#1661)
- You can now call mysql_stmt_attr_set(..., STMT_ATTR_UPDATE_MAX_LENGTH) to tell the client library to update MYSQL_FIELD->max_length when doing mysql_stmt_store_result(). . (Bug#1647)
- The INTERVAL() function did not work correctly when 8 or more comparison arguments were used. (Bug#1561)
- A query that uses both UNION [DISTINCT] and UNION ALL now works correctly. (Bug#1428)
- Parallel repair (myisamchk -p, myisam_repair_threads) sometimes failed to repair a table. (Bug#1334)
- The syntax CONVERT(expr,type) is now supported again.
- Requiring UPDATE privilege for tables which are not updated in multiple-table UPDATE statement in prepared statements.
- A multiple-table UPDATE statement resulted in an error when one of the tables was not updated but was used in the nested query contained therein.
- DROP DATABASE now reports the number of tables deleted.
- The --local-load option of mysqlbinlog now requires an argument.
- Max_used_connections was less than the actual maximum number of connections in use simultaneously.
- Packaging: Added missing file mysql_create_system_tables to the server RPM package. This bug was fixed for the 4.1.1 RPMs by updating the MySQL-server RPM from MySQL-server-4.1.1-0 to MySQL-server-4.1.1-1. The other RPMs were not affected by this change.
- CREATE ... SELECT sometimes created a string column with a multi-byte character set (such as UTF8) of insufficient length for holding the data.
- UNION statements did not consult sql_select_limit value when set. This is now fixed properly, which means that this limit is applied to the top level query, unless LIMIT for entire UNION is used.
- Prepare statements parameter do not cause error message as fields used in select list but not included in ORDER BY list.