MySQL

4.1.2

Released on 28 May 2004
MySQL 4.1.2 Changelog

End of Product Lifecycle. Active development and support for MySQL Database Server versions 3.23, 4.0, and 4.1 has ended. For details, see http://www.mysql.com/about/legal/lifecycle/#calendar. Please consider upgrading to a recent version. Further updates to the content of this manual will be minimal. All formats of this manual will continue to be available until 31 Dec 2010.

Functionality Added or Changed

  • Security Fix: The --defaults-file=file_name option now requires that the file name must exist. (Bug #3413)

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

  • Incompatible Change: Renamed prepared statements C API functions:

    Old NameNew Name
    mysql_bind_param()mysql_stmt_bind_param()
    mysql_bind_result()mysql_stmt_bind_result()
    mysql_prepare()mysql_stmt_prepare()
    mysql_execute()mysql_stmt_execute()
    mysql_fetch()mysql_stmt_fetch()
    mysql_fetch_column()mysql_stmt_fetch_column()
    mysql_param_count()mysql_stmt_param_count()
    mysql_param_result()mysql_stmt_param_metadata()
    mysql_get_metadata()mysql_stmt_result_metadata()
    mysql_send_long_data()mysql_stmt_send_long_data()

    Now all functions that operate with a MYSQL_STMT structure begin with the prefix mysql_stmt_.

  • 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: 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: 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: The Type output column for SHOW TABLE STATUS now is labeled Engine.

  • 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: Added option --replicate-same-server-id.

  • Replication: UUID() function implemented. Note that it does not work with replication yet. See Section 11.14, “Miscellaneous Functions”.

  • MySQL now issues a warning when a SET or ENUM column with duplicate values in the list is created. (Bug #1427)

  • The ft_boolean_syntax variable now can be changed while the server is running. See Section 5.1.3, “Server System Variables”.

  • MyISAM tables now support keys up to 1000 bytes long.

  • mysql command-line client now supports multiple -e options. (Bug #591)

  • CHAR BYTE is an alias for the BINARY data type. (Previously, it was an alias for CHAR BINARY.)

  • mysqlhotcopy now works on NetWare.

  • Prepared statements now work with all types of subqueries.

  • Added the --default-storage-engine server option as a synonym for --default-table-type.

  • MyISAM and InnoDB tables now support index prefix lengths up to 1000 bytes long.

  • The mysqld Windows server was renamed to mysqld-debug. See Section 2.3.8, “Selecting a MySQL Server Type”.

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

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

  • Added the mysql_set_local_infile_handler() and mysql_set_local_infile_default() C API functions.

  • Added Handler_discover status variable.

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

  • The FLOAT and DECIMAL types now obey (precision,scale) settings. (Bug #10897)

  • The mysql command-line client no longer stores in the history file multiple copies of identical queries that are run consecutively.

  • 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 improved character set support introduced in MySQL 4.1.0 for the MyISAM and HEAP storage engines is now available for InnoDB as well.

  • Added latin1_spanish_ci (Modern Spanish) collation for the latin1 character set.

  • 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 the storage_engine system variable as a synonym for table_type.

  • Added explanation of hidden SELECT of UNION in output of EXPLAIN SELECT statement.

  • Added the EXAMPLE storage engine.

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

  • The --log-warnings server option now is enabled by default. Disable with --log-warnings=0.

  • Added the ENGINE table option as a synonym for the TYPE option for CREATE TABLE and ALTER TABLE.

  • UNHEX() function implemented. See Section 11.5, “String Functions”.

  • Multi-line statements in the mysql command-line client now are stored in the history file as a single line.

  • ALTER TABLE DROP PRIMARY KEY no longer drops the first UNIQUE index if there is no primary index. (Bug #2361)

  • 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”.

  • MySQL now supports up to 64 indexes per table.

  • New myisam_data_pointer_size system variable. See Section 5.1.3, “Server System Variables”.

  • 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)

  • Now sql_select_limit variable has no influence on subqueries. (Bug #2600)

  • REVOKE ALL PRIVILEGES, GRANT FROM user_list is changed to a more consistent REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_list. (Bug #2642)

  • Added option --to-last-log to mysqlbinlog, for use in conjunction with --read-from-remote-server.

  • 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 support for character set conversion and MYSQL_TYPE_BLOB type code in prepared statement protocol.

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

  • 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)

  • SHOW GRANTS with no FOR clause or with FOR CURRENT_USER() shows the privileges for the current session.

Bugs Fixed

  • Packaging: In the Mac OS DMG postinstall script, mysql_install_db was invoked with an invalid argument.

  • 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: 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: A MySQL slave server built using --with-debug, and replicating itself, crashed. (Bug #3568)

  • Replication: Multiple-table DELETE statements were never replicated by the slave if there were any --replicate-*-table options. (Bug #2527)

  • 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: If a replication slave was unable to create the first relay log, it crashed. (Bug #2145)

  • 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: 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: 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)

  • 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: 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: Corrected the master's binary log position that InnoDB reports when it is doing a crash recovery on a slave server. (Bug #3015)

  • 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: CREATE TABLE ... LIKE ... statements were not always written to the binary log. (Bug #2557)

  • 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: --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)

  • 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)

  • 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)

  • Prepare statements parameter do not cause error message as fields used in select list but not included in ORDER BY list.

  • Attempting to bind a negative value bind to unsigned caused an Unknown error. (Bug #3223)

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

  • 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)

  • Full-text indexing of strings in multi-byte (all besides utf8) charsets could sometimes hang. (Bug #2065)

  • SHOW GRANTS and EXPLAIN SELECT did not always perform character set conversion correctly. (Bug #3403)

  • 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)

  • Processing of RAND() in subqueries with static tables was not always handled correctly. (Bug #2645)

  • 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)

  • Index_length in HEAP table status for BTREE indexes was not calculated correctly. (Bug #2719)

  • 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)

  • 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)

  • 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)

  • 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)

  • Optimization of ALL and SOME subqueries was not performed well (key field present in subquery). (Bug #3646)

  • 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)

  • 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)

  • 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)

  • The output of mysqldump --tab was not correct. (Bug #2705)

  • Added support for unsigned integer types to prepared statement API . (Bug #3035)

  • An issue with the range optimizer caused a segmentation fault on some very rare queries. (Bug #2698)

  • The INTERVAL() function did not work correctly when 8 or more comparison arguments were used. (Bug #1561)

  • Made clearer the error message that one gets when an update is refused because of the --read-only option. (Bug #2757)

  • UTF8 charset breaks joins with mixed column/string constant. (Bug #2959)

  • 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)

  • Running LOAD DATA FROM MASTER after RESET SLAVE caused a segmentation fault. (Bug #2922)

  • EXPLAIN should now work correctly with UNION queries. (Bug #3639)

  • 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)

  • Incorrect error message when wrong table used in multiple-table DELETE statement in prepared statements. (Bug #3411)

  • SHOW CREATE TABLE ... did not properly double quotation marks. (Bug #2593)

  • CREATE ... SELECT sometimes created a string column with a multi-byte character set (such as UTF8) of insufficient length for holding the data.

  • The second execution of a prepared statement using UNION caused the server to crash. (Bug #3577)

  • Removed try to check NULL if index built on column where NULL is impossible in IN subquery optimization. (Bug #2393)

  • 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)

  • 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)

  • Short-form IP addresses used as arguments to INET_ATON() were not parsed correctly. (Bug #2310)

  • Results of aggregate functions used in subqueries with empty result sets were incorrect. (Bug #3505)

  • mysqlbinlog --read-from-remote-server now print the exact positions of events in lines beginning with at # in the log. (Bug #3214)

  • Compiling the server using the --with-pstack options did not work with binutils 2.13.90. (Bug #1661)

  • Changed the column Seconds_Behind_Master in SHOW SLAVE STATUS to never show a value of -1. (Bug #2826)

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

  • A memory leak could occur with INSERT ... ON DUPLICATE KEY UPDATE. (Bug #2438)

  • Privileges were not checked correctly for ALTER TABLE RENAME. (Bug #3270)

  • 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)

  • Max_used_connections was less than the actual maximum number of connections in use simultaneously.

  • Aggregate functions could lead to server crashes when used in prepared statements. (Bug #3360)

  • 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)

  • Full-text indexing of UTF8 data did not work correctly. (Bug #2033)

  • The --local-load option of mysqlbinlog now requires an argument.

  • CONCAT_WS() makes the server die in case of illegal mix of collations. (Bug #3087)

  • The mysql client program crashed when passed a database name that was longer then expected. (Bug #2221)

  • Table names in were quoted in mysqldump when using values for the server SQL mode where this was not appropriate. (Bug #2591)

  • ANALYZE TABLE on a BDB table inside a transaction caused the server to hang. (Bug #2342)

  • DROP DATABASE now reports the number of tables deleted.

  • Using an impossible WHERE with PROCEDURE ANALYSE() caused the server to hang. (Bug #2238)

  • Parallel repair (myisamchk -p, myisam_repair_threads) sometimes failed to repair a table. (Bug #1334)

  • 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)

  • 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)

  • Invalid results were returned when CAST() was applied to NULL to obtain a signed or unsigned integer value. (Bug #2219)

  • FLUSH TABLES sometimes corrupted table resolution for statements which were prepared before the FLUSH TABLES but which were being executed repeatedly afterward. (Bug #3307)

  • GRANT did not handle table-level privileges correctly. (Bug #2178)

  • Invoking mysql_set_server_option() caused client/server communications to be broken. (Bug #2207)

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

  • Subqueries in the FROM clause were not always parsed correctly. (Bug #2421)

  • There was a symlink vulnerability in the mysqlbug script. (Bug #3284)

  • 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)

  • vio_timeout() virtual function was not set for all protocols. This lead to crashes on Windows. (Bug #2025)

  • 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)

  • 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)

  • A query that uses both UNION [DISTINCT] and UNION ALL now works correctly. (Bug #1428)

  • mysql_stmt_send_long_data() misbehaved on the second execution of a prepared statement when long data had zero length. (Bug #1664)

  • Table default character set affects LONGBLOB columns. (Bug #2821)

  • Requiring UPDATE privilege for tables which are not updated in multiple-table UPDATE statement in prepared statements.

  • Segmentation faults could occur when processing malformed prepared statements. (Bug #2795, Bug #2274)

  • mysqldump did not quote names containing backtick characters (`) correctly. (Bug #2592)

  • 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)

  • Compile the MySQL-client RPM package against libreadline instead of libedit. (Bug #2289)

  • A password was not checked for changes in GRANT accounts until FLUSH PRIVILEGES was executed. (Bug #3404)

  • Subqueries with OR and AND did not always work correctly. (Bug #2838)

  • ALTER DATABASE caused the client to hang if the database did not exist. (Bug #2333)

  • ORDER BY did not always work correctly with SMALLINT columns. (Bug #2147)

  • 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)

  • DOUBLE and FLOAT columns could store out of range values. (Bug #2082)

  • A prepared statement using parameters and having a subquery in the FROM clause could cause the server to crash. (Bug #3020)

  • MATCH ... AGAINST() on a phrase search operator with a missing closing double quote caused the server to crash. (Bug #2708)

  • mysql_stmt_affected_rows() now always returns the number of rows affected by a given statement. (Bug #2247)

  • 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)

  • The syntax CONVERT(expr,type) is now supported again.

  • mysqld could crash when a table was altered and used at the same time. This was a 4.1.2-specific bug. . (Bug #3643)

  • Write operations on a column hvaing a FULLTEXT index could under some rare circumstances lead to table file corruption. (Bug #2417)

  • mysqlbinlog failed to print a USE statement under those rare circumstances where the binary log contained a LOAD DATA INFILE statement. (Bug #3415)