This appendix lists the changes from version to version in the MySQL source code.
We are now working actively on MySQL 4.1 & 5.0 and will only provide critical bug fixes for MySQL 4.0 and MySQL 3.23. We update this section as we add new features, so that everybody can follow the development.
Our TODO section contains what further plans we have for 4.1 & 5.0. See section 1.8 MySQL and The Future (The TODO).
Note that we tend to update the manual at the same time we make changes to MySQL. If you find a version listed here that you can't find on the MySQL download page (http://www.mysql.com/downloads/), this means that the version has not yet been released!
The date mentioned with a release version is the date of the last BitKeeper ChangeSet that this particular release has been based on, not the date when the packages have been made available. The binaries are usually made available a few days after the date of the tagged ChangeSet - building and testing all packages takes some time.
For the time being, version 5.0 is only available in source code. See section 2.3.4 Installing from the Development Source Tree.
The following changelog shows what has already been done in the 5.0 tree:
SELECT INTO list_of_vars
, which can be of mixed,
that is, global and local type.
SET
@a=10;
then SELECT @A;
will now return 10
. Of course,
the content of the variable is still case sensitive; only the name of
this variable is case insensitive.
Version 4.1 of the MySQL server includes many enhancements and new features. Binaries for this version are available for download at http://www.mysql.com/downloads/mysql-4.1.html.
SELECT * FROM t1 WHERE t1.a=(SELECT t2.b FROM t2); SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2);
SELECT t1.a FROM t1, (SELECT * FROM t2) t3 WHERE t1.a=t3.a;
INSERT ... ON DUPLICATE KEY UPDATE ...
syntax. This allows you to
UPDATE
an existing row if the insert would cause a duplicate value
in a PRIMARY
or UNIQUE
key. (REPLACE
allows you to
overwrite an existing row, which is something entirely different.)
See section 6.4.3 INSERT
Syntax.
GROUP_CONCAT()
aggregate function.
See section 6.3.7 Functions and Modifiers for Use with GROUP BY
Clauses.
BTREE
index on HEAP
tables.
SHOW WARNINGS
shows warnings for the last command.
See section 4.5.7.9 SHOW WARNINGS | ERRORS
.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table2 LIKE
table1
.
HELP command
that can be used in the mysql
command line client (and other clients) to get help for SQL commands.
For a full list of changes, please refer to the changelog sections for each individual 4.1.x release.
Functionality added or changed:
--sql-mode=NO_AUTO_VALUE_ON_ZERO
to suppress usual
behaviour of generating the next sequence number when zero is stored in
an AUTO_INCREMENT
column. With this mode enabled, only NULL
generates a sequence number.
user
table, not 45-byte passwords as in 4.1.0.
Any 45-byte passwords created for 4.1.0 must be reset after running the
mysql_fix_privilege_tables
script.
clear
function for each aggregate function.
CONCAT_WS()
no longer skips empty strings.
UTC_DATE()
, UTC_TIME()
, and
UTC_TIMESTAMP()
.
DATE()
, TIME()
, TIMESTAMP()
,
WEEKOFYEAR()
, MICROSECOND()
, ADDTIME()
, SUBTIME()
,
DATEDIFF()
, TIMEDIFF()
, MAKEDATE()
, MAKETIME()
,
and TIMEDIFF()
.
ADDDATE()
and SUBDATE()
.
They now allow a numeric second argument representing the number of days to
be added to or subtracted from the first date argument.
type
values DAY_MICROSECOND
,
HOUR_MICROSECOND
, MINUTE_MICROSECOND
,
SECOND_MICROSECOND
, and MICROSECOND
for DATE_ADD()
, DATE_SUB()
, and EXTRACT()
.
%f
microseconds format specifier for DATE_FORMAT()
and
TIME_FORMAT()
.
SELECT
does not
use indices properly get into slow query log when long log format is
used.
MERGE
tables from MyISAM
tables in
different databases. Formerly, all the MyISAM
tables had to be in the
same database, and the MERGE
table had to be created in that database
as well.
COMPRESS()
, UNCOMPRESS()
, and
UNCOMPRESSED_LENGTH()
.
SET sql_mode='mode'
for a complex mode (like ANSI
), we
now update the sql_mode
variable to include all the individual options
implied by the complex mode.
ROLLUP
, which gives
you summary rows for each GROUP BY
level.
SQLSTATE
codes for all server errors.
mysql_sqlstate()
and
mysql_stmt_sqlstate()
that return the SQLSTATE error code for the
last error.
TIME
columns with hours > 24 (days) were returned incorrectly to the client.
ANALYZE
, OPTIMIZE
, REPAIR
, FLUSH
(and its
equivalents invoked from mysqladmin
) commands
are now stored in the binary log (hence are replicated to the slave),
except FLUSH LOGS
, FLUSH MASTER
, FLUSH SLAVE
,
FLUSH TABLES WITH READ LOCK
, and unless the optional
NO_WRITE_TO_BINLOG
keyword
(or its alias LOCAL
) was used (for a syntax example,
see section 4.5.3 FLUSH
Syntax).
RELAY_LOG_PURGE
to enable/disable automatic
relay log purging.
LOAD DATA
now produces warnings that can be fetched with
SHOW WARNINGS
.
CREATE TABLE table2 (LIKE table1)
that creates an empty table table2
with a definition that is
exactly the same as table1
, including any indexes.
CREATE TABLE table_name (...) TYPE=storage_engine
now generates a
warning if the named storage engine is not available. The table is still
created as a MyISAM
table, as before.
PURGE BINARY LOGS
for PURGE MASTER LOGS
.
PURGE LOGS
syntax is disabled (it had been added in version
4.1.0).
It is now PURGE MASTER LOGS
or PURGE BINARY LOGS
.
SHOW BDB LOGS
for SHOW LOGS
.
SHOW MASTER LOGS
(which had been deleted in version
4.1.0) to SHOW BINARY LOGS
.
Bugs fixed:
HASH
, BTREE
, RTREE
, ERRORS
and
WARNINGS
are not reserved words anymore. (Bug #724)
ROLLUP
when all tables were const
tables.
(Bug #714)
--lower-case-table-names=1
now also makes aliases case
insensitive. (Bug #534)
UNION
which prohibited that NULL
values are
inserted in result set where first SELECT
contains NOT
NULL
columns
WHERE
clause
bigger than outer query WHERE
clause. (Bug #726)
MyISAM
tables with FULLTEXT
indexes
created in 4.0.x to be unreadable in 4.1.x.
REPAIR TABLE ... USE_FRM
when used
with tables
that contained TIMESTAMP
fields and were created in 4.0.x.
ORDER BY
/GROUP BY
clauses. (Bug #442)
INSERT
/REPLACE
statements. (Bug #446)
CREATE FULLTEXT INDEX
syntax illegal.
SELECT
that required a temporary table
(marked by Using temporary
in EXPLAIN
output)
was used as a derived table in EXPLAIN
command. (Bug #251)
DELETE
from a big table with
a new (created by MySQL-4.1) fulltext index.
LAST_INSERT_ID()
now returns 0 if the last INSERT
statement didn't
insert any rows.
BEGIN
, in the
first relay log.) (Bug #53)
CONNECTION_ID()
is now properly replicated. (Bug #177)
PASSWORD()
function in 4.1 is now properly replicated.
(Bug #344)
UNION
operations that involved temporary tables.
DERIVED TABLES
when EXPLAIN
is
used on a DERIVED TABLES
with a join.
DELETE
with ORDER BY
and
LIMIT
caused by an uninitialized array of reference pointers.
USER()
function caused by an error in the size of
the allocated string.
UNION
caused by the empty select list and
a non-existent field being used in some of the sub-selects.
FLUSH LOGS
was
issued on the master. (Bug #254)
REQUIRE SSL
option specified for
their accounts.
Functionality added or changed:
user
table.
CRC32()
function to compute cyclic redundancy check value.
localhost
.
REPAIR
of MyISAM
tables now uses less temporary disk space when
sorting char columns.
DATE
/DATETIME
checking is now a bit stricter to support the
ability to automatically distinguish between date, datetime, and time
with microseconds. For example, dates of type YYYYMMDD HHMMDD
are no
longer supported; you must either have separators between each
DATE
/TIME
part or not at all.
help
week
in the mysql
client and get help for the week()
function.
mysql_get_server_version()
.
libmysqlclient
that fetched field defaults.
record_in_range()
method to MERGE
tables to be
able to choose the right index when there are many to choose from.
RAND()
and user variables @var
.
ANSI_QUOTES
on the fly.
EXPLAIN SELECT
now can be killed. See section 4.5.6 KILL
Syntax.
REPAIR TABLE
now can be killed. See section 4.5.6 KILL
Syntax.
USE|IGNORE|FORCE INDEX
.
DROP TEMPORARY TABLE
now only drops temporary tables and doesn't
end transactions.
UNION
in derived tables.
TIMESTAMP
is now returned as a string of type
'YYYY-MM-DD HH:MM:SS'
and different timestamp lengths are not supported.
This change was necessary for SQL standards compliance. In a future
version, a further change will be made (backward compatible with this
change), allowing the timestamp length to indicate the desired number
of digits of fractions of a second.
MYSQL_FIELD
structure.
CREATE TABLE foo (a INT not null primary key)
the
PRIMARY
word is now optional.
CREATE TABLE
the attribute SERIAL
is now an alias for
BIGINT NOT NULL AUTO_INCREMENT UNIQUE
.
SELECT ... FROM DUAL
is an alias for SELECT ...
.
(To be compatible with some other databases).
CHAR
/VARCHAR
it's now
automatically changed to TEXT
or BLOB
; One will get a
warning in this case.
BLOB
/TEXT
types with the
syntax BLOB(length)
and TEXT(length)
. MySQL will
automatically change it to one of the internal BLOB
/TEXT
types.
CHAR BYTE
is an alias for CHAR BINARY
.
VARCHARACTER
is an alias for VARCHAR
.
integer MOD integer
and integer DIV integer
.
SERIAL DEFAULT VALUE
added as an alias for AUTO_INCREMENT
.
TRUE
and FALSE
added as alias for 1 and 0, respectively.
SELECT .. LIMIT 0
to return proper row count for
SQL_CALC_FOUND_ROWS
.
--tmpdir=dirname1:dirname2:dirname3
.
SELECT * from t1 where t1.a=(SELECT t2.b FROM t2)
.
SELECT a.col1, b.col2 FROM (SELECT MAX(col1) AS col1 FROM root_table) a, other_table b WHERE a.col1=b.col1;
CONVERT(... USING ...)
syntax for converting string values between
character sets.
BTREE
index on HEAP
tables.
CREATE TABLE
.
SHOW FULL COLUMNS FROM table_name
shows column comments.
ALTER DATABASE
.
SHOW [COUNT(*)] WARNINGS
shows warnings from the last command.
CREATE TABLE
... SELECT
by defining the column in the CREATE
part.
CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;
expr SOUNDS LIKE expr
same as SOUNDEX(expr)=SOUNDEX(expr)
.
VARIANCE(expr)
returns the variance of expr
CREATE
[TEMPORARY] TABLE [IF NOT EXISTS] table (LIKE table)
. The table can
be either normal or temporary.
--reconnect
and disable-reconnect
for the
mysql
client, to reconnect automatically or not if the
connection is lost.
START SLAVE
(STOP SLAVE
) no longer returns an error
if the slave is already started (stopped); it returns a
warning instead.
SLAVE START
and SLAVE STOP
are no longer accepted by the query
parser; use START SLAVE
and STOP SLAVE
instead.
Version 4.0 of the MySQL server includes many enhancements and new features:
InnoDB
table type is now included in the standard binaries,
adding transactions, row-level locking, and foreign keys.
See section 7.5 InnoDB
Tables.
MERGE
tables, now supporting INSERT
statements and
AUTO_INCREMENT
.
See section 7.2 MERGE
Tables.
UNION
syntax in SELECT
.
See section 6.4.1.2 UNION
Syntax.
DELETE
statements.
See section 6.4.6 DELETE
Syntax.
libmysqld
, the embedded server library.
See section 9.1.15 libmysqld, the Embedded MySQL Server Library.
GRANT
privilege options for even tighter control and
security.
See section 4.3.1 GRANT
and REVOKE
Syntax.
GRANT
system, particularly
useful for ISPs and other hosting providers.
See section 4.3.6 Limiting user resources.
SET
Syntax.
For a full list of changes, please refer to the changelog sections for each individual 4.0.x release.
Functionality added or changed:
mysql_install_db
to be able to use the local machine's IP
address instead of the host name when building the initial grant tables if
skip-name-resolve
has been specified in my.cnf
. Using this
optione can be helpful on FreeBSD to avoid thread-safety problems with the
FreeBSD resolver libraries. (Thanks to Jeremy Zawodny for the patch)
Bugs fixed:
LOAD DATA FROM
MASTER
dropped the corresponding database on the slave, thus dropping
tables which may not have a counterpart on the master, or tables which
may be excluded from replication using replicate-*-table
rules. Now LOAD DATA FROM MASTER
does not drop the database
anymore, it drops only the tables which have a counterpart on the master
and which match the replicate-*-table
rules. replicate-*-db
rules can still be used to include/exclude a
database as a whole from LOAD DATA FROM MASTER
. A database will
also be included/excluded as a whole if there are some rules like
replicate-wild-do-table=db1.%
or
replicate-wild-ignore-table=db1.%
, like it's already the case for
CREATE DATABASE
and DROP DATABASE
in replication. (Bug
#1248)
mysqlbinlog
crashed (segmentation fault) when used with the
-h, --host
option. (Bug #1258)
Functionality added or changed:
mysqldump
now correctly quotes all identifiers when communicating
with the server. This assures that during the dump process, mysqldump
will never send queries to the server that result in a syntax error.
This problem is not related to the
mysqldump
program's output, which was not changed. (Bug #1148)
MIN()
and MAX()
report that they can return NULL
(this is true because an
empty set will return NULL
). (Bug #324)
mysqld
server is
started on the same TCP/IP port as an already running mysqld
server.
mysqld
server variables wait_timeout
,
net_read_timeout
and net_write_timeout
now work on Windows.
--sql-mode=NO_DIR_IN_CREATE
to make it possible for
slaves to ignore INDEX DIRECTORY
and
DATA DIRECTORY
options given to CREATE TABLE
.
When this is mode is on, SHOW CREATE TABLE
will not show the
given directories.
SHOW CREATE TABLE
now shows the INDEX DIRECTORY
and
DATA DIRECTORY
options, if they were specified when the table was
created.
open_files_limit
server variable now shows the real open files limit.
MATCH ... AGAINST()
in natural language mode
now treats words that are present
in more than 2,000,000 rows as stopwords.
.tar.gz
) has been moved into a subdirectory docs
.
See section 2.2.7 Installation Layouts.
info
file in the binary
distributions. (Bug #1019)
libmysqld
) by default.
See section 1.5.1.2 Embedded MySQL Server.
BETWEEN
with
non-constant limits. (Bug #991)
binlog-do-db
and binlog-ignore-db
options are tested
against the database on the master (see section 4.9.4 The Binary Log), and a
paragraph about how replicate-do-db
, replicate-do-table
and analogous options are tested against the database and tables on the
slave (see section 4.10.6 Replication Options in `my.cnf').
SET PASSWORD
if it is configured to exclude the mysql
database from
replication (using for example
replicate-wild-ignore-table=mysql.%
). This was already the
case for GRANT
and REVOKE
since version 4.0.13 (though
there was Bug #980 in 4.0.13 & 4.0.14, which has been fixed in
4.0.15).
State
column of SHOW
PROCESSLIST
for replication threads and for MASTER_POS_WAIT()
and added the most common states for these threads to the
documentation, see section 4.10.3 Replication Implementation Details.
GRANT
command that creates an anonymous user
(that is, an account with an empty username) no longer requires
FLUSH PRIVILEGES
for the account to be recognized by the server.
(Bug #473)
CHANGE MASTER
now flushes `relay-log.info'. Previously
this was deferred to the next run of START SLAVE
, so if
mysqld
was shutdown on the slave after CHANGE MASTER
without having run START SLAVE
, the relay log's name and
position were lost. At restart they were reloaded from
`relay-log.info', thus reverting to their old (incorrect) values from
before CHANGE MASTER
and leading to error messages
(as the old relay log did not exist any more) and the slave threads
refusing to start. (Bug #858)
Bugs fixed:
ALTER
privilege on the
mysql.user
table to execute random code or to gain shell access with
the UID of the mysqld process (thanks to Jedi/Sector One for spotting and
reporting this bug).
FORCE INDEX
in a query that contained
"Range checked for each record" in the EXPLAIN
output. (Bug #1172)
UPDATE
of split dynamic rows.
The symptom was that the table had a corrupted delete-link if mysqld
was shut down or the table was checked directly after the update.
Can't unlock file
error when running
myisamchk --sort-index
on Windows. (Bug #1119)
key_buffer_size
while
the key cache was actively used. (Bug #1088)
MyISAM
and ISAM
when a row is updated
in a table with a large number of columns and at least one BLOB/TEXT
column.
UNION
and LIMIT #,#
when
one didn't use braces around the SELECT
parts.
UNION
and ORDER BY .. LIMIT #
when one didn't use braces around the SELECT
parts.
SELECT SQL_CALC_FOUND_ROWS ... UNION ALL ... LIMIT #
where FOUND_ROWS()
returned incorrect number of rows.
1+1-1+1-1...
in certain combinations. (Bug #871)
FULLTEXT
index
from being marked as "analyzed".
SHOW CREATE TABLE
is always larger than the data length.
The only known application that was affected by the old behaviour was
Borland dbExpress, which truncated the output from the command.
(Bug #1064)
tis620
character
set. (Bug #1116)
ISAM
bug in MAX()
optimisation.
myisamchk --sort-records=N
no longer marks table as crashed if
sorting failed because of an inappropriate key. (Bug #892)
MyISAM
compressed table handling that sometimes
made it impossible to repair compressed table in "Repair by sort" mode.
"Repair with keycache" (myisamchk --safe-recover
) worked, though.
(Bug #1015)
PRIMARY
key declared for a column
that is not explicitly marked NOT NULL
was sorted after a
UNIQUE
key for a NOT NULL
column).
INTERVAL
when applied to a DATE
value.
(Bug #792)
XOR
evaluation in WHERE
clause. (Bug #992)
LEFT JOIN
)
when ON
condition is always false, and range search in used.
(Bug #926)
MATCH ... AGAINST()
in some
joins. (Bug #942)
MERGE
tables do not ignore "Using index" (from EXPLAIN
output)
anymore.
myisamchk --sort-records
crash when used on compressed table.
ALTER TABLE
and related commands such as CREATE INDEX
.
(Bug #712)
LOAD DATA FROM MASTER
when
the master was running without the --log-bin
option. (Bug #934)
REQUIRE SSL
option specified for
their accounts.
GRANT
or REVOKE
queries even if it was configured to exclude the
mysql
database from replication (for example, using
replicate-wild-ignore-table=mysql.%
). (Bug #980)
Last_errno
and Last_error
fields in the output of
SHOW SLAVE STATUS
are now cleared by CHANGE MASTER
and
when the slave SQL thread starts. (Bug #986)
RESET SLAVE
does not change
connection information (master host, port, user, and
password), whereas it does. The statement resets these to the startup options
(master-host
etc) if there were some. (Bug #985)
SHOW SLAVE STATUS
now shows correct information (master host,
port, user, and password) after RESET SLAVE
(that is, it shows the new values, which are copied from the startup
options if there were some). (Bug #985)
Exec_master_log_pos
and problems
with MASTER_POS_WAIT()
in A->B->C replication setup. (Bug #1086)
mysqlbinlog
when
--position=x
was used with x
being between a
Create_file
event and its fellow Append_block
,
Exec_load
or Delete_file
events. (Bug #1091)
mysqlbinlog
printed superfluous warnings when using
--database
, which caused syntax errors when piped to
mysql
. (Bug #1092)
mysqlbinlog --database
filter LOAD DATA INFILE
too
(previously, it filtered all queries except LOAD DATA
INFILE
). (Bug #1093)
mysqlbinlog
in some cases forgot to put a leading '#'
in
front of the original LOAD DATA INFILE
(this command is
displayed only for information, not to be run; it is later reworked to
LOAD DATA LOCAL
with a different filename, for execution by
mysql
). (Bug #1096)
binlog-do-db
and binlog-ignore-db
incorrectly filtered
LOAD DATA INFILE
(it was half-written to the binary log). This
resulted in a corrupted binary log, which could cause the slave to
stop with an error. (Bug #1100)
InnoDB
table)
was updated, and later in the same transaction a
non-transactional table (such as a MyISAM
table) was
updated using the updated content of the transactional table
(with INSERT ... SELECT
for example), the queries were written
to the binary log in an incorrect order. (Bug #873)
INSERT ... SELECT
updated a
non-transactional table, and ROLLBACK
was issued, no error was
returned to the client. Now the client is warned that some changes
could not be rolled back, as this was already the case for normal
INSERT
. (Bug #1113)
STOP SLAVE
was run while the slave
SQL thread was in the middle of a transaction, and then CHANGE
MASTER
was used to point the slave to some non-transactional
statement, the SQL slave thread could get confused (because it would
still think, from the past, that it was in a transaction).
Functionality added or changed:
InnoDB
now supports indexing a prefix of a column. This means, in
particular, that BLOB
and TEXT
columns can be indexed in
InnoDB
tables, which was not possible before.
INTERVAL(NULL, ...)
returns -1
.
INSERT
from SELECT
when the table into which the records
are inserted is also a table listed in the SELECT
.
CREATE TABLE
and INSERT
from any UNION
.
SQL_CALC_FOUND_ROWS
option now always returns the total number
of rows for any UNION
.
--table
option from mysqlbinlog
to avoid repeating mysqldump
functionality.
max_seeks_for_key
variable that can
be used to force the optimiser to use keys instead of table scans
even if the cardinality of the index is low.
LEFT JOIN
to normal join in
some cases.
CHANGE MASTER
command. See section 4.10.7.9 CHANGE MASTER TO master_def_list
(slave).
--nice
option to mysqld_safe
to allow setting the
niceness of the mysqld
process. (Thanks to Christian Hammers for
providing the initial patch.) (Bug #627)
--read-only
option to cause mysqld
to allow no updates
except from slave threads or from users with the SUPER
privilege.
(Original patch from Markus Benning).
SHOW BINLOG EVENTS FROM x
where x
is strictly less than 4 now
silently converts x
to 4 instead of printing an error. The same change
was done for CHANGE MASTER TO MASTER_LOG_POS=x
and
CHANGE MASTER TO RELAY_LOG_POS=x
.
mysqld
now only adds an interrupt handler for the SIGINT
signal
if you start it with the new --gdb
option. This is because some
MySQL users encountered strange problems when they accidentally sent SIGINT
to
mysqld
threads.
RESET SLAVE
now clears the Last_errno
and
Last_error
fields in the output of SHOW SLAVE STATUS
.
max_relay_log_size
variable; the relay log will be rotated
automatically when its size exceeds max_relay_log_size
. But if
max_relay_log_size
is 0 (the default), max_binlog_size
will be used (as in older versions). max_binlog_size
still
applies to binary logs in any case.
FLUSH LOGS
now rotates relay logs in addition to the other types of
logs it already rotated.
Bugs fixed:
latin1_de
character set was rewritten.
The old algorithm could not handle cases like "sä" > "ßa"
.
See section 4.6.1.1 German character set. In rare cases it resulted in table
corruption.
ALTER TABLE ... UNION=(...)
for MERGE
table is now allowed
even if some underlying MyISAM
tables are read-only. (Bug #702)
CREATE TABLE t1 SELECT x'41'
. (Bug #801)
REPAIR
on a table with a multi-part
auto_increment key where one part was a packed CHAR
.
INSERT
statements not being replicated in the
event of a FLUSH LOGS
command or when the binary log exceeds
max_binlog_size
. (Bug #791)
INTERVAL
and GROUP BY
or
DISTINCT
. (Bug #807)
mysqlhotcopy
so it actually aborts for unsuccessful
table copying operations. Fixed another bug so that it succeeds
when there are thousands of tables to copy. (Bug #812)
mysqlhotcopy
failing to read options from option
files. (Bug #808)
FULLTEXT
indexes even though it was possible (for example, in
SELECT * FROM t1 WHERE MATCH a,b AGAINST("index") > 0
).
UNION
operations.
SHOW TABLES
and
similar commands.
max_user_connections
.
HANDLER
without an index now works properly when a table has deleted
rows. (Bug #787)
LOAD DATA
in mysqlbinlog
. (Bug #670)
SET CHARACTER SET DEFAULT
works. (Bug #462)
MERGE
table behaviour in ORDER BY ... DESC
queries.
(Bug #515)
PURGE MASTER LOGS
or SHOW MASTER LOGS
when the binary log is off. (Bug #733)
DATETIME
column and an integer
constant. (Bug #504)
mysqlbinlog
. (Bug #672)
ERROR 1105: Unknown error
that occurred for some SELECT
queries, where a column that was declared as NOT NULL
was compared
with an expression that took NULL
value.
mysql_real_connect()
to use poll()
instead of
select()
to work around problem with many open files in the client.
MATCH ... AGAINST
used with a
LEFT JOIN
query.
mysqld
variables
to 4294967295 when they are specified on the command line.
HANDLER ... READ
statements, when a table is referenced
via an alias.
safe_malloc
, which caused MySQL to
give "Freeing wrong aligned pointer" errors on SCO 3.2.
ALTER TABLE ... ENABLE/DISABLE KEYS
could cause a core dump when
done after an INSERT DELAYED
statement on the same table.
mysqld
. (Bug #549)
INSERT ... SELECT
into an AUTO_INCREMENT
column
not replicate well. This bug is in the master, not in the slave.
(Bug #490)
INSERT ... SELECT
statement inserted rows into a
non-transactional table, but failed at some point (for example, due to
a ``Duplicate key'' error), the query was not written to the binlog.
Now it is written to the binlog, with its error code, as all other
queries are. About the slave-skip-errors
option for how to
handle partially completed queries in the slave, see section 4.10.6 Replication Options in `my.cnf'. (Bug #491)
SET FOREIGN_KEY_CHECKS=0
was not replicated properly.
The fix probably will not be backported to 3.23.
LOAD DATA INFILE
which had no IGNORE
or
REPLACE
clause on the master, was replicated with IGNORE
.
While this is not a problem if the master and slave data are identical
(a LOAD
that produces no duplicate conflicts on the master will
produce none on the slave anyway), which is true in normal operation, it
is better for debugging not to silently add the IGNORE
. That way,
you can get an error message on the slave and discover that for some
reason, the data on master and slave are different and investigate why.
(Bug #571)
LOAD DATA INFILE
printed an incomplete ``Duplicate entry
'%-.64s' for key %d''' message (the key name and value were not mentioned)
in case of duplicate conflict (which does not happen in normal operation).
(Bug #573)
--debug
, CHANGE MASTER
TO RELAY_LOG_POS
could cause a debug assertion failure. (Bug #576)
LOCK TABLES WRITE
on an InnoDB
table, commit could
not happen, if the query was not written to the binary log (for example, if
--log-bin
was not used, or binlog-ignore-db
was
used). (Bug #578)
DROP TABLE
statements to its binlog, then the 4.0.13
slave would not notice the temporary tables have to be dropped, until the
slave mysqld
server is restarted. This minor inconvenience is
fixed in 3.23.57 and 4.0.14 (meaning the master must be upgraded to
3.23.57 and the slave to 4.0.14 to remove the inconvenience). (Bug #254)
MASTER_POS_WAIT()
was waiting, and the slave was idle, and
the SQL slave thread terminated, MASTER_POS_WAIT()
would wait
forever. Now when the SQL slave thread terminates,
MASTER_POS_WAIT()
immediately returns NULL
(``slave
stopped''). (Bug #651)
RESET SLAVE; START SLAVE;
, the Relay_log_space
value
displayed by SHOW SLAVE STATUS
was too big by four bytes. (Bug #763)
replicate-ignore-table
and other similar rules), the slave
still checked if the query got the same error code (0, no error) as on
the master. So if the master had an error on the query (for example,
``Duplicate entry'' in a multiple-row insert), then the slave stopped
and warned that the error codes didn't match. (Bug #797)
Functionality added or changed:
PRIMARY KEY
now implies NOT NULL
. (Bug #390)
--enable-local-infile
to match the Unix build configuration.
mysql-test-run
. time
does not
accept all required parameters on many platforms (for example, QNX) and timing
the tests is not really required (it's not a benchmark anyway).
SHOW MASTER STATUS
and SHOW SLAVE STATUS
required the
SUPER
privilege; now they accept REPLICATION CLIENT
as well.
(Bug #343)
myisam_repair_threads
variable to enable it.
See section 4.5.7.4 SHOW VARIABLES
.
innodb_max_dirty_pages_pct
variable which controls amount of
dirty pages allowed in InnoDB
buffer pool.
CURRENT_USER()
and Access denied
error messages now report the
hostname exactly as it was specified in the GRANT
command.
InnoDB
tables now support ANALYZE TABLE
.
--new
now changes binary items (0xFFDF
) to be
treated as binary strings instead of numbers by default. This fixes some
problems with character sets where it's convenient to input the string
as a binary item. After this change you have to convert the binary
string to INTEGER
with a CAST
if you want to compare two
binary items with each other and know which one is bigger than the other.
SELECT CAST(0xfeff AS UNSIGNED) < CAST(0xff AS UNSIGNED)
.
This will be the default behaviour in MySQL 4.1. (Bug #152)
delayed_insert_timeout
on Linux (most modern glibc
libraries have a fixed pthread_cond_timedwait
). (Bug #211)
max_insert_delayed_threads
. (Bug #211)
UPDATE ... LIMIT
to also count accepted, but not changed rows.
BIT_AND()
and BIT_OR()
now return an unsigned 64 bit value.
--log-warnings
).
--skip-symlink
and --use-symbolic-links
and
replaced these with --symbolic-links
.
innodb_flush_log_at_trx_commit
was changed
from 0 to 1 to make InnoDB
tables ACID by default.
See section 7.5.3 InnoDB Startup Options.
SHOW KEYS
to display keys that are disabled by
ALTER TABLE DISABLE KEYS
command.
CREATE TABLE
, first
try if the default table type exists before falling back to MyISAM
.
MEMORY
as an alias for HEAP
.
rnd
to my_rnd
as the name was too generic
and is an exported symbol in libmysqlclient
(thanks to Dennis Haney
for the initial patch).
mysqldump
no longer silently deletes the binlogs when called with
--master-data
or --first-slave
;
while this behaviour was convenient for some
users, others may suffer from it. Now one has to explicitely ask for
this deletion with the new --delete-master-logs
option.
replicate-wild-ignore-table=mysql.%
)
to exclude mysql.user
, mysql.host
, mysql.db
,
mysql.tables_priv
and mysql.columns_priv
from
replication, then GRANT
and REVOKE
will not be replicated.
Bugs fixed:
Access denied
error message had wrong Using password
value. (Bug #398)
NATURAL LEFT JOIN
, NATURAL RIGHT JOIN
and
RIGHT JOIN
when using many joined tables. The problem was that
the JOIN
method was not always associated with the tables
surrounding the JOIN
method. If you have a query that uses many
RIGHT JOIN
or NATURAL ... JOINS
you should check that they
work as you expected after upgrading MySQL to this version. (Bug #291)
mysql
command line client no longer looks for \*
commands
inside backtick-quoted strings.
Unknown error
when using UPDATE ... LIMIT
. (Bug #373)
GROUP BY
with constants. (Bug #387)
UNION
and OUTER JOIN
. (Bug #386)
UPDATE
and the query required a
temporary table bigger than tmp_table_size
. (Bug #286)
mysql_install_db
with the -IN-RPM
option for the Mac OS X
installation to not fail on systems with improperly configured hostname
configurations.
LOAD DATA INFILE
will now read 000000
as a zero date instead as
"2000-00-00"
.
DELETE FROM table WHERE const_expression
always to delete the whole table (even if expression result was false).
(Bug #355)
FORMAT('nan',#)
. (Bug #284)
HAVING ... COUNT(DISTINCT ...)
.
*
) in
MATCH ... AGAINST()
in some complex joins.
REPAIR ... USE_FRM
command, when used on read-only,
nonexisting table or a table with a crashed index file.
--no-defaults
, with a prompt
that contained hostname and connection to non-existing db was requested
LEFT
, RIGHT
and MID
when used with
multi-byte character sets and some GROUP BY
queries. (Bug #314)
ORDER BY
being discarded for some
DISTINCT
queries. (Bug #275)
SET SQL_BIG_SELECTS=1
works as documented (This corrects
a new bug introduced in 4.0)
UPDATE ... ORDER BY
. (Bug #241)
WHERE
clause with constant
expression like in WHERE 1 AND (a=1 AND b=1)
.
SET SQL_BIG_SELECTS=1
works again.
SHOW GRANTS
.
FULLTEXT
index stopped working after ALTER TABLE
that converts TEXT
field to CHAR
. (Bug #283)
SELECT
and wildcarded select list,
when user only had partial column SELECT
privileges on the table.
SET PASSWORD
.
NATURAL JOIN
s in the query.
SUM()
didn't return NULL
when there was no rows in result
or when all values was NULL
.
--open-files-limit
in
mysqld_safe
. (Bug #264)
SHOW PROCESSLIST
.
NAN
in FORMAT(...)
function ...
ALTER TABLE ENABLE / DISABLE KEYS
which failed to
force a refresh of table data in the cache.
LOAD DATA INFILE
for custom parameters
(ENCLOSED
, TERMINATED
and so on) and temporary tables.
(Bug #183, Bug #222)
FLUSH LOGS
was
issued on the master. (Bug #254)
LOAD DATA INFILE IGNORE
: when reading
the binary log, mysqlbinlog
and the replication code read REPLACE
instead of IGNORE
. This could make the slave's table
become different from the master's table. (Bug #218)
relay_log_space_limit
was set to a too
small value. (Bug #79)
MyISAM
when a row is inserted into a table with a
large number of columns and at least one BLOB/TEXT
column. Bug was caused
by incorrect calculation of the needed buffer to pack data.
SELECT
@nonexistent_variable caused the
error in client - server protocol due to net_printf() being sent to
the client twice.
SQL_BIG_SELECTS
option.
SHOW PROCESSLIST
which only displayed a localhost
in the "Host"
column. This was caused by a glitch that only used
current thread information instead of information from the linked list of threads.
InnoDB
tables as well.
mysqldump
when it was called with
--master-data
: the CHANGE MASTER TO
commands appended to
the SQL dump had wrong coordinates. (Bug #159)
USER()
was replicated
on the slave ; this caused segfault on the slave. (Bug #178).
USER()
is still badly replicated on the slave (it is
replicated to ""
).
Functionality added or changed:
mysqld
no longer reads options from world-writeable config files.
SHOW PROCESSLIST
will now include the client TCP port after the
hostname to make it easier to know from which client the request
originated.
Bugs fixed:
mysqld
crash on extremely small values of
sort_buffer
variable.
INSERT INTO u SELECT ... FROM t
was written too late to the
binary log if t was very frequently updated during the execution of
this query. This could cause a problem with mysqlbinlog
or
replication. The master must be upgraded, not the slave. (Bug #136)
WHERE
clause. (Bug #142)
InnoDB
tables. This bug occurred as, in many cases, InnoDB
tables can not
be updated ``on the fly,'' but offsets to the records have to be stored in
a temporary table.
mysql_secure_installation
to the server
RPM subpackage. (Bug #141)
myisamchk
) crash on artificially
corrupted .MYI
files.
BACKUP TABLE
to overwrite existing files.
UPDATE
statements when user had all privileges
on the database where tables are located and there were any entries in
tables_priv
table, that is, grant_option
was true.
TRUNCATE
any table in the same database.
LOCK TABLE
followed by DROP
TABLE
in the same thread. In this case one could still kill the thread
with KILL
.
LOAD DATA LOCAL INFILE
was not properly written to the binary
log (hence not properly replicated). (Bug #82)
RAND()
entries were not read correctly by mysqlbinlog
from
the binary log which caused problems when restoring a table that was
inserted with RAND()
. INSERT INTO t1 VALUES(RAND())
. In
replication this worked ok.
SET SQL_LOG_BIN=0
was ignored for INSERT DELAYED
queries. (Bug #104)
SHOW SLAVE STATUS
reported too old positions
(columns Relay_Master_Log_File
and Exec_master_log_pos
)
for the last executed statement from the master, if this statement
was the COMMIT
of a transaction. The master must be upgraded for that,
not the slave. (Bug #52)
LOAD DATA INFILE
was not replicated by the slave if
replicate_*_table
was set on the slave. (Bug #86)
RESET SLAVE
, the coordinates displayed by SHOW
SLAVE STATUS
looked un-reset (though they were, but only
internally). (Bug #70)
LOAD DATA
.
ANALYZE
procedure with error.
CHAR(0)
columns that could cause wrong
results from the query.
AUTO_INCREMENT
column,
as a secondary column in a multi-column key (see section 3.6.9 Using AUTO_INCREMENT
), when
data was inserted with INSERT ... SELECT
or LOAD DATA
into
an empty table.
STOP SLAVE
didn't stop the slave until the slave
got one new command from the master (this bug has been fixed for MySQL 4.0.11
by releasing updated 4.0.11a Windows packages, which include this individual
fix on top of the 4.0.11 sources). (Bug #69)
LOAD DATA
command
was issued with full table name specified, including database prefix.
pthread_attr_getstacksize
on
HP-UX 10.20 (Patch was also included in 4.0.11a sources).
bigint
test to not fail on some platforms (for example, HP-UX and
Tru64) due to different return values of the atof()
function.
rpl_rotate_logs
test to not fail on certain platforms (e.g.
Mac OS X) due to a too long file name (changed slave-master-info.opt
to .slave-mi
).
Functionality added or changed:
NULL
is now sorted LAST if you use ORDER BY ... DESC
(as it was before MySQL 4.0.2). This change was required to comply
with the SQL-99 standard. (The original change was made because we thought
that SQL-99 required NULL
to be always sorted at the same position,
but this was incorrect).
START TRANSACTION
(SQL-99 syntax) as alias for BEGIN
.
This is recommended to use instead of BEGIN
to start a transaction.
OLD_PASSWORD()
as a synonym for PASSWORD()
.
ALL
in group functions.
INNER JOIN
and JOIN
syntaxes.
For example, SELECT * FROM t1 INNER JOIN t2
didn't work before.
Bugs fixed:
InnoDB
tables.
BLOB NOT NULL
columns used with IS NULL
.
InnoDB
storage engine.
BLOB
columns with long strings.
CREATE TABLE (...)
AUTO_INCREMENT=#
.
MIN(key_column)
could in some cases return NULL
on a column
with NULL
and other values.
MIN(key_column)
and MAX(key_column)
could in some cases
return wrong values when used in OUTER JOIN
.
MIN(key_column)
and MAX(key_column)
could return wrong
values if one of the tables was empty.
INTERVAL
,
CASE
, FIELD
, CONCAT_WS
, ELT
and
MAKE_SET
functions.
--lower-case-table-names
(default on Windows)
and you had tables or databases with mixed case on disk, then
executing SHOW TABLE STATUS
followed with DROP DATABASE
or DROP TABLE
could fail with Errcode 13
.
Functionality added or changed:
--log-error[=file_name]
to mysqld_safe
and
mysqld
. This option will force all error messages to be put in a
log file if the option --console
is not given. On Windows
--log-error
is enabled as default, with a default name of
host_name.err
if the name is not specified.
Warning:
to Note:
in the log files.
GROUP BY ... ORDER BY NULL
then result is not sorted.
--ft-stopword-file
command-line option for mysqld
to
replace/disable the built-in stopword list that is used in full-text searches.
See section 4.5.7.4 SHOW VARIABLES
.
glibc
that requires a
stack size larger than 128K
for gethostbyaddr()
to resolve a hostname. You can fix
this for earlier MySQL versions by starting mysqld
with
--thread-stack=192K
.
mysql_waitpid
to the binary distribution and the
MySQL-client
RPM subpackage (required for mysql-test-run
).
MySQL
RPM package to MySQL-server
. When
updating from an older version, MySQL-server.rpm
will simply replace
MySQL.rpm
.
replicate_wild_do_table=db.%
or
replicate_wild_ignore_table=db.%
, these rules will be applied to
CREATE/DROP DATABASE
too.
MASTER_POS_WAIT()
.
Bugs fixed:
rand()
distribution from the first call.
mysqld
to hang when a
table was opened with the HANDLER
command and then
dropped without being closed.
NULL
in an auto_increment
field and also
uses LAST_INSERT_ID()
.
ORDER BY constant_expression
.
myisamchk
's parallel repair mode.
mysqladmin --relative
.
show status
reported a strange number for
Open_files
and Open_streams
.
EXPLAIN
on empty table.
LEFT JOIN
that caused zero rows to be returned in
the case the WHERE
condition was evaluated as FALSE
after
reading const tables. (Unlikely condition).
FLUSH PRIVILEGES
didn't correctly flush table/column privileges
when mysql.tables_priv
is empty.
LOAD DATA INFILE
one a file
that updated and auto_increment field with NULL
or 0
. This
bug only affected MySQL 4.0 masters (not slaves or MySQL 3.23 masters).
NOTE: If you have a slave that has replicated a file with
generated auto_increment fields then the slave data is corrupted and you
should reinitialise the affected tables from the master.
NOT NULL
field to an
expression that returned NULL
.
str LIKE "%other_str%"
where str
or
other_str
contained characters >= 128.
LOAD DATA
and InnoDB
failed
with table full
error the binary log was corrupted.
Functionality added or changed:
OPTIMIZE TABLE
will for MyISAM tables treat all NULL
values as different when calculating cardinality. This helps in
optimising joins between tables where one of the tables has a lot of
NULL
values in a indexed column:
SELECT * from t1,t2 where t1.a=t2.key_with_a_lot_of_null;
FORCE INDEX (key_list)
. This acts likes
USE INDEX (key_list)
but with the addition that a table scan is
assumed to be VERY expensive. One bad thing with this is that it makes
FORCE
a reserved word.
Bugs fixed:
LOAD DATA INFILE
statement that
caused log rotation.
Functionality added or changed:
max_packet_length
for libmysqld.c is now 1024*1024*1024.
max_allowed_packet
in a file ready by
mysql_options(MYSQL_READ_DEFAULT_FILE)
.
for clients.
InnoDB
tables now also support ON UPDATE CASCADE
in
FOREIGN KEY
constraints. See the InnoDB
section in the manual
for the InnoDB
changelog.
Bugs fixed:
ALTER TABLE
with BDB tables.
QUOTE()
function.
GROUP BY
when used on BLOB column with NULL
values.
NULL
values in CASE
... WHEN ...
Functionality added or changed:
mysqlbug
now also reports the compiler version used for building
the binaries (if the compiler supports the option --version
).
Bugs fixed:
-DBIG_TABLES
on a 32 bit system.
mysql_drop_db()
didn't check permissions properly so anyone could
drop another users database. DROP DATABASE
is checked properly.
Functionality added or changed:
CHARACTER SET xxx
and CHARSET=xxx
table options (to be able to read table dumps from 4.1).
IFNULL(A,B)
is now set to be the
more 'general' of the types of A
and B
. (The order is
STRING
, REAL
or INTEGER
).
mysql.server
startup script in the RPM packages from
`/etc/rc.d/init.d/mysql' to `/etc/init.d/mysql'
(which almost all current Linux distributions support for LSB compliance).
Qcache_lowmem_prunes
status variable (number of queries that were
deleted from cache because of low memory).
mysqlcheck
so it can deal with table names containing dashes.
SHOW VARIABLES
)
is no longer used when inserting small (less than 100) number of rows.
SELECT ... FROM merge_table WHERE indexed_column=constant_expr
.
LOCALTIME
and LOCALTIMESTAMP
as synonyms for
NOW()
.
CEIL
is now an alias for CEILING
.
CURRENT_USER()
function can be used to get a user@host
value as it was matched in the GRANT
system.
See section 6.3.6.2 Miscellaneous Functions.
CHECK
constraints to be compatible with SQL-99. This made
CHECK
a reserved word. (Checking of CHECK
constraints is
still not implemented).
CAST(... as CHAR)
.
LIMIT
syntax:
SELECT ... LIMIT row_count OFFSET offset
mysql_change_user()
will now reset the connection to the state
of a fresh connect (Ie, ROLLBACK
any active transaction, close
all temporary tables, reset all user variables etc..)
CHANGE MASTER
and RESET SLAVE
now require that slave
threads be both already stopped; these commands will return an error
if at least one of these two threads is running.
Bugs fixed:
multi table updates
--lower-case-table-names
default on Mac OS X as the default
file system (HFS+) is case insensitive.
See section 6.1.3 Case Sensitivity in Names.
AUTOCOMMIT=0
mode didn't rotate binary log.
SELECT
with joined tables with
ORDER BY
and LIMIT
clause when filesort had to be used.
In that case LIMIT
was applied to filesort of one of the tables,
although it could not be.
This fix solved problems with LEFT JOIN
too.
mysql_server_init()
now makes a copy of all arguments. This fixes
a problem when using the embedded server in C# program.
libmysqlclient
library
that allowed a malicious MySQL
server to crash the client
application.
mysql_change_user()
handling.
All users are strongly recommended to upgrade to version 4.0.6.
--chroot
command-line option of
mysqld
from working.
"..."
in boolean full-text search.
OPTIMIZE TABLE
to corrupt the table
under some rare circumstances.
LOCK TABLES
now works together with multi-table-update and
multi-table-delete.
--replicate-do=xxx
didn't work for UPDATE
commands.
(Bug introduced in 4.0.0)
InnoDB
bugs in REPLACE, AUTO_INCREMENT,
INSERT INTO ... SELECT ...
were fixed. See the InnoDB
changelog
in the InnoDB
section of the manual.
RESET SLAVE
caused a crash if the slave threads were running.
Functionality added or changed:
SHOW PROCESSLIST
command
WEEK()
so that one can get
week number according to the ISO 8601 specification.
(Old code should still work).
INSERT DELAYED
threads doesn't hang on Waiting for
INSERT
when one sends a SIGHUP
to mysqld.
AND
works according to SQL-99 when it comes to
NULL
handling. In practice, this only affects queries where you
do something like WHERE ... NOT (NULL AND 0)
.
mysqld
will now resolve basedir
to its full path (with
realpath()
). This enables one to use relative symlinks to the
MySQL installation directory. This will however cause show
variables
to report different directories on systems where there is
a symbolic link in the path.
IGNORE INDEX
or USE INDEX
.
to be ignored.
--use-frm
option to mysqlcheck
. When used with
REPAIR
, it gets the table structure from the .frm
file, so the
table can be repaired even if the .MYI
header is corrupted.
MAX()
optimisation when used with JOIN
and
ON
expressions.
BETWEEN
behaviour changed (see section 6.3.1.2 Comparison Operators).
Now datetime_col BETWEEN timestamp AND timestamp
should work
as expected.
TEMPORARY
MERGE
tables now.
DELETE FROM myisam_table
now shrinks not only the `.MYD' file but
also the `.MYI' file.
--open-files-limit=#
option to mysqld_safe
it's now passed on to mysqld
.
EXPLAIN
from 'where used'
to
'Using where'
to make it more in line with other output.
safe_show_database
as it was no longer used.
automake 1.5
and
libtool 1.4
.
--ignore-space
) back to the
original --ignore-spaces
in mysqlclient
. (Both syntaxes will
work).
UPDATE
privilege when using REPLACE
.
DROP TEMPORARY TABLE ...
, to be used to make
replication safer.
BEGIN/COMMIT
are now stored in the binary log on
COMMIT
and not stored if one does ROLLBACK
. This fixes
some problems with non-transactional temporary tables used inside
transactions.
SELECT * FROM (t2 LEFT JOIN t3 USING (a)), t1
worked, but
not SELECT * FROM t1, (t2 LEFT JOIN t3 USING (a))
. Note that
braces are simply removed, they do not change the way the join is
executed.
InnoDB
now supports also isolation levels
READ UNCOMMITTED
and READ COMMITTED
.
For a detailed InnoDB
changelog, see section 7.5.16 InnoDB Change History
in this manual.
Bugs fixed:
MAX()
optimisation when used with JOIN
and
ON
expressions.
INSERT DELAY
threads don't hang on Waiting for
INSERT
when one sends a SIGHUP
to mysqld
.
IGNORE INDEX
or USE INDEX
.
root
user in mysqld_safe
.
CHECK
or REPAIR
.
GROUP BY
queries that
didn't return any result.
mysqlshow
to work properly with wildcarded database names and
with database names that contain underscores.
MyISAM
crash when using dynamic-row tables with huge numbers of
packed fields.
BDB
transactions.
MATCH
relevance calculations.
IN BOOLEAN MODE
that made MATCH
to return incorrect relevance value in some complex joins.
MyISAM
key length to a value
slightly less that 500. It is exactly 500 now.
GROUP BY
on columns that may have a NULL
value
doesn't always use disk based temporary tables.
--des-key-file
argument to mysqld
is interpreted relative to the data directory if given as a relative pathname.
NULL
has to be MyISAM
. This was okay for 3.23, but not
needed in 4.*. This resulted in slowdown in many queries since 4.0.2.
ORDER BY ... LIMIT row_count
to not return all rows.
REPAIR TABLE
and myisamchk
to corrupt FULLTEXT
indexes.
mysql
grant table database. Now queries
in this database are not cached in the query cache.
mysqld_safe
for some shells.
MyISAM
MERGE
table has more than 2 ^ 32 rows and
MySQL was not compiled with -DBIG_TABLES
.
ORDER BY ... DESC
problems with InnoDB
tables.
GRANT
/REVOKE
failed if hostname was given in
non-matching case.
LOAD DATA INFILE
when setting a
timestamp
to a string value of '0'
.
myisamchk -R
mode.
mysqld
to crash on REVOKE
.
ORDER BY
when there is a constant in the SELECT
statement.
mysqld
couldn't open the
privilege tables.
SET PASSWORD FOR ...
closed the connection in case of errors (bug
from 4.0.3).
max_allowed_packet
in mysqld
to 1 GB.
INSERT
on a table with an
AUTO_INCREMENT
key which was not in the first part of the key.
LOAD DATA INFILE
to not recreate index if the table had
rows from before.
AES_DECRYPT()
with incorrect arguments.
--skip-ssl
can now be used to disable SSL in the MySQL clients,
even if one is using other SSL options in an option file or previously
on the command line.
MATCH ... AGAINST( ... IN BOOLEAN MODE)
used with ORDER BY
.
LOCK TABLES
and CREATE TEMPORARY TABLES
privilege on
the database level. One must run the mysql_fix_privilege_tables
script on old installations to activate these.
SHOW TABLE ... STATUS
, compressed tables sometimes showed up as
dynamic
.
SELECT @@[global|session].var_name
didn't report
global | session
in the result column name.
FLUSH LOGS
in a circular
replication setup created an infinite number of binary log files.
Now a rotate-binary-log
command in the binary log will not cause slaves
to rotate logs.
STOP EVENT
from binary log when doing FLUSH LOGS
.
SHOW NEW MASTER FOR SLAVE
as this needs to be
completely reworked in a future release.
UNIQUE
key) appeared in ORDER BY
part of SELECT DISTINCT
.
--log-binary=a.b.c
now properly strips off .b.c
.
FLUSH LOGS
removed numerical extension for all future update logs.
GRANT ... REQUIRE
didn't store the SSL information in the
mysql.user
table if SSL was not enabled in the server.
GRANT ... REQUIRE NONE
can now be used to remove SSL information.
AND
is now optional between REQUIRE
options.
REQUIRE
option was not properly saved, which could cause strange
output in SHOW GRANTS
.
mysqld --help
reports correct values for --datadir
and --bind-address
.
mysqld
was started.
SHOW VARIABLES
on some 64 bit systems
(like Solaris sparc).
my_getopt()
; --set-variable
syntax didn't work for
those options that didn't have a valid variable in the my_option
struct.
This affected at least the default-table-type
option.
REPAIR TABLE
and
myisamchk --recover
to fail on tables with duplicates in a unique
key.
CREATE TABLE table_name
SELECT expression(),...
SELECT * FROM table-list GROUP BY ...
and
SELECT DISTINCT * FROM ...
.
--slow-log
when logging an administrator command
(like FLUSH TABLES
).
OPTIMIZE
of locked and modified table,
reported table corruption.
my_getopt()
in handling of special prefixes
(--skip-
, --enable-
). --skip-external-locking
didn't
work and the bug may have affected other similar options.
tee
option.
SELECT ... FROM many_tables .. ORDER BY key limit #
SHOW OPEN TABLES
when a user didn't have access
permissions to one of the opened tables.
configure ... --localstatedir=...
.
mysql.server
script.
mysqladmin shutdown
when pid file was modified
while mysqladmin
was still waiting for the previous one to
disappear. This could happen during a very quick restart and caused
mysqladmin
to hang until shutdown_timeout
seconds had
passed.
AUTO_INCREMENT
columns to
NULL
in LOAD DATA INFILE
.
--lower-case-table-names
,
--lower-case-table-names=1
,
-O lower-case-table-names=1
,
--set-variable=lower-case-table-names=1
SHOW MASTER STATUS
now returns an empty set if binary log is not
enabled.
SHOW SLAVE STATUS
now returns an empty set if slave is not initialised.
SELECT DISTINCT ... FROM many_tables ORDER BY
not-used-column
.
BIGINT
values and quoted strings.
QUOTE()
function that performs SQL quoting to produce values
that can be used as data values in queries.
DELAY_KEY_WRITE
to an enum to allow one set
DELAY_KEY_WRITE
for all tables without taking down the server.
IF(condition,column,NULL)
so that it returns
the value of the column type.
safe_mysqld
a symlink to mysqld_safe
in binary distribution.
user.db
table.
CREATE TABLE ... SELECT function()
.
mysqld
now has the option --temp-pool
enabled by default as this
gives better performance with some operating systems.
CHANGE MASTER TO
if the slave thread died very quickly.
--code-file
option is specified, the server calls
setrlimit()
to set the maximum allowed core file size to unlimited,
so core files can be generated.
--count=N
(-c
) option to mysqladmin
, to make the
program do only N
iterations. To be used with --sleep
(-i
).
Useful in scripts.
UPDATE
: when updating a table,
do_select()
became confused about reading records from a cache.
UPDATE
when several fields were referenced
from a single table
REVOKE
that caused user resources to be randomly set.
GRANT
for the new CREATE TEMPORARY TABLE
privilege.
DELETE
when tables are re-ordered in the
table initialisation method and ref_lengths are of different sizes.
SELECT DISTINCT
with large tables.
DEFAULT
with INSERT
statement.
myisam_max_sort_file_size
and
myisam_max_extra_sort_file_size
are now given in bytes, not megabytes.
MyISAM
/ISAM
files is now turned
off by default. One can turn this on with --external-locking
.
(For most users this is never needed).
INSERT ... SET db_name.table_name.colname=''
.
DROP DATABASE
SET [GLOBAL | SESSION]
syntax to change thread-specific and global
server variables at runtime.
slave_compressed_protocol
.
query_cache_startup_type
to query_cache_type
,
myisam_bulk_insert_tree_size
to bulk_insert_buffer_size
,
record_buffer
to read_buffer_size
and
record_rnd_buffer
to read_rnd_buffer_size
.
--skip-locking
to --skip-external-locking
.
query_buffer_size
.
mysql
client
non-functional.
AUTO_INCREMENT
support to MERGE
tables.
LOG()
function to accept an optional arbitrary base
parameter.
See section 6.3.3.2 Mathematical Functions.
LOG2()
function (useful for finding out how many bits
a number would require for storage).
LN()
natural logarithm function for compatibility with
other databases. It is synonymous with LOG(X)
.
NULL
handling for default values in DESCRIBE
table_name
.
truncate()
to round up negative values to the nearest integer.
--chroot=path
option to execute chroot()
immediately
after all options have been parsed.
lower_case_table_names
now also affects database names.
XOR
operator (logical and bitwise XOR
) with ^
as a synonym for bitwise XOR
.
IS_FREE_LOCK("lock_name")
.
Based on code contributed by Hartmut Holzgraefe hartmut@six.de.
mysql_ssl_clear()
from C API, as it was not needed.
DECIMAL
and NUMERIC
types can now read exponential numbers.
SHA1()
function to calculate 160 bit hash value as described
in RFC 3174 (Secure Hash Algorithm). This function can be considered a
cryptographically more secure equivalent of MD5()
.
See section 6.3.6.2 Miscellaneous Functions.
AES_ENCRYPT()
and AES_DECRYPT()
functions to perform
encryption according to AES standard (Rijndael).
See section 6.3.6.2 Miscellaneous Functions.
--single-transaction
option to mysqldump
, allowing a
consistent dump of InnoDB
tables.
See section 4.8.6 mysqldump
, Dumping Table Structure and Data.
innodb_log_group_home_dir
in SHOW VARIABLES
.
FULLTEXT
index is present and no tables are used.
CREATE TEMPORARY TABLES
, EXECUTE
,
LOCK TABLES
, REPLICATION CLIENT
, REPLICATION SLAVE
,
SHOW DATABASES
and SUPER
. To use these, you must have
run the mysql_fix_privilege_tables
script after upgrading.
TRUNCATE TABLE
; This fixes some core
dump/hangup problems when using TRUNCATE TABLE
.
DELETE
when optimiser uses only indices.
ALTER TABLE table_name RENAME new_table_name
is as fast
as RENAME TABLE
.
GROUP BY
with two or more fields, where at least one
field can contain NULL
values.
Turbo Boyer-Moore
algorithm to speed up LIKE "%keyword%"
searches.
DROP DATABASE
with symlink.
REPAIR ... USE_FRM
.
EXPLAIN
with LIMIT offset != 0
.
"..."
in boolean full-text search.
*
in boolean full-text search.
+word*
s in the query).
MATCH
expression that did not use an index appeared twice.
mysqldump
.
ft_min_word_len
characters.
--without-query-cache
.
INET_NTOA()
now returns NULL
if you give it an argument that
is too large (greater than the value corresponding to 255.255.255.255
).
SQL_CALC_FOUND_ROWS
to work with UNION
s. It will work only
if the first SELECT
has this option and if there is global LIMIT
for the entire statement. For the moment, this requires using parentheses for
individual SELECT
queries within the statement.
SQL_CALC_FOUND_ROWS
and LIMIT
.
CREATE TABLE ...(... VARCHAR(0))
.
SIGINT
and SIGQUIT
problems in `mysql.cc' on Linux
with some glibc
versions.
net_store_length()
linked in the CONVERT::store()
method.
DOUBLE
and FLOAT
columns now honor the UNSIGNED
flag
on storage.
InnoDB
now retains foreign key constraints through ALTER TABLE
and CREATE/DROP INDEX
.
InnoDB
now allows foreign key constraints to be added through the
ALTER TABLE
syntax.
InnoDB
tables can now be set to automatically grow in size (autoextend).
--ignore-lines=n
option to mysqlimport
. This has the
same effect as the IGNORE n LINES
clause for LOAD DATA
.
UNION
with last offset being transposed to total result
set.
REPAIR ... USE_FRM
added.
DEFAULT_SELECT_LIMIT
is always imposed on UNION
result set.
SELECT
options can appear only in the first
SELECT
.
LIMIT
with UNION
, where last select is in
the braces.
UNION
operations.
SELECT
with an empty
HEAP
table.
ORDER BY column DESC
now sorts NULL
values first.
(In other words, NULL
values sort first in all cases, whether or
not DESC
is specified.) This is changed back in 4.0.10.
WHERE key_name='constant' ORDER BY key_name DESC
.
SELECT DISTINCT ... ORDER BY DESC
optimisation.
... HAVING 'GROUP_FUNCTION'(xxx) IS [NOT] NULL
.
--user=#
option for mysqld
to be specified
as a numeric user ID.
SQL_CALC_ROWS
returned an incorrect value when used
with one table and ORDER BY
and with InnoDB
tables.
SELECT 0 LIMIT 0
doesn't hang thread.
USE/IGNORE INDEX
when using
many keys with the same start column.
BerkeleyDB
and InnoDB
tables when
we can use an index that covers the whole row.
InnoDB
sort-buffer handling to take less memory.
DELETE
and InnoDB
tables.
TRUNCATE
and InnoDB
tables that produced the
error Can't execute the given command because you have active locked
tables or an active transaction
.
NO_UNSIGNED_SUBTRACTION
to the set of flags that may be
specified with the --sql-mode
option for mysqld
. It disables
unsigned arithmetic rules when it comes to subtraction. (This will make
MySQL 4.0 behave more like 3.23 with UNSIGNED
columns).
|
, <<
, ...) is now of
type unsigned integer
.
nan
values in MyISAM
to make it possible to
repair tables with nan
in float or double columns.
myisamchk
where it didn't correctly update number of
``parts'' in the MyISAM
index file.
autoconf
2.52 (from autoconf
2.13).
const
tables. This fix also
improves performance a bit when referring to another table from a
const
table.
UPDATE
statement.
DELETE
.
SELECT CONCAT(argument_list) ... GROUP BY 1
.
INSERT ... SELECT
did a full rollback in case of an error. Fixed
so that we only roll back the last statement in the current transaction.
NULL
.
BIT_LENGTH()
function.
GROUP BY BINARY column
.
NULL
keys in HEAP
tables.
ORDER BY
in queries of type:
SELECT * FROM t WHERE key_part1=1 ORDER BY key_part1 DESC,key_part2 DESC
FLUSH QUERY CACHE
.
CAST()
and CONVERT()
functions. The CAST
and
CONVERT
functions are nearly identical and mainly useful when you
want to create a column with a specific type in a CREATE ... SELECT
statement. For more information, read section 6.3.5 Cast Functions.
CREATE ... SELECT
on DATE
and TIME
functions now
create columns of the expected type.
Null
and Index_type
to SHOW INDEX
output.
--no-beep
and --prompt
options to mysql
command-line client.
GRANT ... WITH MAX_QUERIES_PER_HOUR N1 MAX_UPDATES_PER_HOUR N2 MAX_CONNECTIONS_PER_HOUR N3;See section 4.3.6 Limiting user resources.
mysql_secure_installation
to the `scripts/' directory.
system
command to mysql
.
HANDLER
was used with some unsupported table type.
mysqldump
now puts ALTER TABLE tbl_name DISABLE KEYS
and
ALTER TABLE tbl_name ENABLE KEYS
in the sql dump.
mysql_fix_extensions
script.
LOAD DATA FROM MASTER
on OSF/1.
DES_ENCRYPT()
and DES_DECRYPT()
functions.
FLUSH DES_KEY_FILE
statement.
--des-key-file
option to mysqld
.
HEX(string)
now returns the characters in string
converted to
hexadecimal.
GRANT
when using lower_case_table_names=1
.
SELECT ... IN SHARE MODE
to
SELECT ... LOCK IN SHARE MODE
(as in MySQL 3.23).
SELECT
queries.
MATCH ... AGAINST(... IN BOOLEAN MODE)
can now work
without FULLTEXT
index.
myisam/ft_dump
utility for low-level inspection
of FULLTEXT
indexes.
DELETE ... WHERE ... MATCH ...
.
MATCH ... AGAINST(... IN BOOLEAN MODE)
.
Note: you must rebuild your tables with
ALTER TABLE tablename TYPE=MyISAM
to be
able to use boolean full-text search.
LOCATE()
and INSTR()
are now case-sensitive if either
argument is a binary string.
RAND()
initialisation so that RAND(N)
and
RAND(N+1)
are more distinct.
UPDATE ... ORDER BY
.
INSERT INTO ... SELECT
always had IGNORE
enabled.
Now MySQL will stop (and possibly roll back) by default in case of an error
unless you specify IGNORE
.
DATA DIRECTORY
and INDEX DIRECTORY
directives on Windows.
MODIFY
and CHANGE
in ALTER TABLE
to accept
the FIRST
and AFTER
keywords.
ORDER BY
on a whole InnoDB
table.
--xml
option to mysql
for producing XML output.
ft_min_word_len
, ft_max_word_len
, and
ft_max_word_len_for_sort
.
libmysqld
, the embedded MySQL server
library. Also added example programs (a mysql
client and
mysqltest
test program) which use libmysqld
.
my_thread_init()
and my_thread_end()
from `mysql_com.h', and added mysql_thread_init()
and
mysql_thread_end()
to `mysql.h'.
MyISAM
to be able to handle these.
BIGINT
constants now work. MIN()
and MAX()
now handle signed and unsigned BIGINT
numbers correctly.
latin1_de
which provides correct German sorting.
STRCMP()
now uses the current character set when doing comparisons,
which means that the default comparison behaviour now is case-insensitive.
TRUNCATE TABLE
and DELETE FROM tbl_name
are now separate
functions. One bonus is that DELETE FROM tbl_name
now returns
the number of deleted rows, rather than zero.
DROP DATABASE
now executes a DROP TABLE
on all tables in
the database, which fixes a problem with InnoDB
tables.
UNION
.
DELETE
operations.
HANDLER
interface to MyISAM
tables.
INSERT
on MERGE
tables. Patch from
Benjamin Pflugmann.
WEEK(date,0)
to match the calendar in the USA.
COUNT(DISTINCT)
is about 30% faster.
IS NULL
, ISNULL()
and some other internal primitives.
myisam_bulk_insert_tree_size
variable.
CHAR
/VARCHAR
) keys is now much faster.
SELECT DISTINCT * from tbl_name ORDER by key_part1 LIMIT row_count
.
SHOW CREATE TABLE
now shows all table attributes.
ORDER BY ... DESC
can now use keys.
LOAD DATA FROM MASTER
``automatically'' sets up a slave.
safe_mysqld
to mysqld_safe
to make this name more
in line with other MySQL scripts/commands.
MyISAM
tables. Symlink handling is
now enabled by default for Windows.
SQL_CALC_FOUND_ROWS
and FOUND_ROWS()
. This makes it
possible to know how many rows a query would have returned
without a LIMIT
clause.
SHOW OPEN TABLES
.
SELECT expression LIMIT ...
.
ORDER BY
syntax to UPDATE
and DELETE
.
SHOW INDEXES
is now a synonym for SHOW INDEX
.
ALTER TABLE tbl_name DISABLE KEYS
and
ALTER TABLE tbl_name ENABLE KEYS
commands.
IN
as a synonym for FROM
in SHOW
commands.
FULLTEXT
indexes.
REPAIR TABLE
, ALTER TABLE
, and OPTIMIZE TABLE
for tables with FULLTEXT
indexes are now up to 100 times faster.
X'hexadecimal-number'
.
FLUSH TABLES WITH READ LOCK
.
DATETIME = constant
in WHERE
optimisation.
--master-data
and --no-autocommit
options to
mysqldump
. (Thanks to Brian Aker for this.)
mysql_explain_log.sh
to distribution.
(Thanks to mobile.de).
Please note that since release 4.0 is now production level, only critical fixes are done in the 3.23 release series. You are recommended to upgrade when possible, to take advantage of all speed and feature improvements in 4.0. See section 2.5.2 Upgrading From Version 3.23 to 4.0.
The 3.23 release has several major features that are not present in previous versions. We have added three new table types:
MyISAM
InnoDB
BerkeleyDB
or BDB
Note that only MyISAM
is available in the standard binary distribution.
The 3.23 release also includes support for database replication between a master and many slaves, full-text indexing, and much more.
All new features are being developed in the 4.x version. Only bug fixes and minor enhancements to existing features will be added to 3.23.
The replication code and BerkeleyDB code is still not as tested and as the rest of the code, so we will probably need to do a couple of future releases of 3.23 with small fixes for this part of the code. As long as you don't use these features, you should be quite safe with MySQL 3.23!
Note that the above doesn't mean that replication or Berkeley DB don't
work. We have done a lot of testing of all code, including replication
and BDB
without finding any problems. It only means that not as many
users use this code as the rest of the code and because of this we are
not yet 100% confident in this code.
replicate-ignore-table
and other similar rules), the slave
still checked if the query got the same error code (0, no error) as on
the master. So if the master had an error on the query (for example,
``Duplicate entry'' in a multiple-row insert), then the slave stopped
and warned that the error codes didn't match. This is a backport of the
fix for MySQL 4.0. (Bug #797)
ALTER
privilege on the
mysql.user
table to execute random code or to gain shell access with
the UID of the mysqld process (thanks to Jedi/Sector One for spotting and
reporting this bug).
mysqldump
now correctly quotes all identifiers when communicating
with the server. This assures that during the dump process, mysqldump
will never send queries to the server that result in a syntax error. This
problem is not related to the mysqldump
program's output,
which was not changed. (Bug #1148)
MyISAM
and ISAM
when a row is updated
in a table with a large number of columns and at least one BLOB/TEXT
column.
SHOW CREATE TABLE
is always larger than the data length.
The only known application that was affected by the old behaviour was
Borland dbExpress, which truncated the output from the command.
(Bug #1064)
ISAM
bug in MAX()
optimisation.
Unknown error
when doing ORDER BY
on reference table which
was used with NULL
value on NOT NULL
column. (Bug #479)
mysqld
.
kill pid-of-mysqld
to work on Mac OS X.
SHOW TABLE STATUS
displayed incorrect Row_format
value for
tables that have been compressed with myisampack
.
(Bug #427)
SHOW VARIABLES LIKE 'innodb_data_file_path'
displayed only the
name of the first datafile. (Bug #468)
mysqld
didn't allow one to UPDATE
rows in a table even if one had a global UPDATE
privilege and a
database SELECT
privilege.
SELECT
and wildcarded select list,
when user only had partial column SELECT
privileges on the table.
WHERE
clause with a constant
expression such as in WHERE 1 AND (a=1 AND b=1)
.
mysqlbinlog
to fail.
innodb_flush_log_at_trx_commit
was changed
from 0 to 1 to make InnoDB
tables ACID by default.
See section 7.5.3 InnoDB Startup Options.
LOAD DATA INFILE IGNORE
: When reading
the binary log, mysqlbinlog
and the replication code read REPLACE
instead of IGNORE
. This could make the slave's table
become different from the master's table. (Bug #218)
MyISAM
when a row is inserted into a table with a
large number of columns and at least one BLOB/TEXT
column. Bug was caused
by incorrect calculation of the needed buffer to pack data.
TRUNCATE table_name
or
DELETE FROM table_name
statements, which could cause an INSERT
to
table_name
to be written to the log before the
TRUNCATE
or DELETE
statements.
UPDATE
of InnoDB
tables where one row could be
updated multiple times.
PROCEDURE ANALYSE()
to report DATE
instead of
NEWDATE
.
PROCEDURE ANALYSE(#)
to restrict the number of values in an
ENUM
column to #
also for string values.
mysqldump
no longer silently deletes the binary logs when invoked with
the --master-data
or --first-slave
option;
while this behaviour was convenient for some
users, others may suffer from it. Now one has to explicitly ask for binary
logs to be deleted by using the new --delete-master-logs
option.
mysqldump
when it was invoked with the
--master-data
option: The CHANGE MASTER TO
statements that were
appended to the SQL dump had incorrect coordinates. (Bug #159)
mysqld
crash on extremely small values of
sort_buffer
variable.
GRANT UPDATE
on column level.
HAVING
with GROUP BY
.
WHERE
clause. (Bug #142)
myisamchk
) crash on artificially
corrupted `.MYI' files.
mysqld
no longer reads options from world-writeable config files.
mysqld
and safe_mysqld
now only use the first --user
option specified on the command line. (Normally this comes from
`/etc/my.cnf')
BACKUP TABLE
to overwrite existing files.
LOCK TABLE
and
another thread did a DROP TABLE
. In this case one could do
a KILL
on one of the threads to resolve the deadlock.
LOAD DATA INFILE
was not replicated by slave if
replicate_*_table
was set on the slave.
CHAR(0)
columns that could cause wrong
results from the query.
SHOW VARIABLES
on 64-bit platforms. The bug was
caused by wrong declaration of variable server_id
.
SHOW TABLE STATUS
now reports that it can
contain NULL
values (which is the case for a crashed `.frm' file).
rpl_rotate_logs
test to not fail on certain platforms (e.g.
Mac OS X) due to a too long file name (changed slave-master-info.opt
to .slave-mi
).
BLOB NOT NULL
columns used with IS NULL
.
MAX()
optimisation in MERGE
tables.
RAND()
initialisation for new connections.
poll()
system call, which resulted in timeout the value specified as
it was executed in both select()
and poll()
.
SELECT * FROM table WHERE datetime1 IS NULL OR datetime2 IS NULL
.
INTERVAL
,
CASE
, FIELD
, CONCAT_WS
, ELT
and
MAKE_SET
functions.
--lower-case-table-names
(default on Windows)
and you had tables or databases with mixed case on disk, then
executing SHOW TABLE STATUS
followed with DROP DATABASE
or DROP TABLE
could fail with Errcode 13
.
NULL
in an auto_increment
field and also
uses LAST_INSERT_ID()
.
mysqladmin --relative
.
show status
reported a strange number for
Open_files
and Open_streams
.
free
'd pointer bug in mysql_change_user()
handling, that enabled a specially hacked version of MySQL client
to crash mysqld
. Note, that one needs to login to the server
by using a valid user account to be able to exploit this bug.
--slow-log
when logging an administrator command
(like FLUSH TABLES
).
GROUP BY
when used on BLOB column with NULL
values.
NULL
values in CASE ... WHEN ...
.
--chroot
(see section D.4.6 Changes in release 3.23.54 (05 Dec 2002))
is reverted. Unfortunately, there is no way to make it to work, without
introducing backward-incompatible changes in `my.cnf'.
Those who need --chroot
functionality, should upgrade to MySQL 4.0.
(The fix in the 4.0 branch did not break backward-compatibility).
--lower-case-table-names
default on Mac OS X as the default
file system (HFS+) is case insensitive.
NOHUP_NICENESS
testing.
AUTOCOMMIT=0
mode didn't rotate binary log.
scripts/make_binary_distribution
that resulted in
a remaining @HOSTNAME@
variable instead of replacing it with the
correct path to the hostname
binary.
SHOW PROCESSLIST
to core
dump in pthread_mutex_unlock() if a new thread was connecting.
SLAVE STOP
if the thread executing the query has locked
tables. This removes a possible deadlock situation.
mysqld
with a specially crafted packet.
free
'd pointer) when altering a
temporary table.
libmysqlclient
library
that allowed malicious MySQL server to crash the client
application.
mysql_change_user()
handling.
All users are strongly recommended to upgrade to the version 3.23.54.
--chroot
command-line option of mysqld
from working.
OPTIMIZE TABLE
to corrupt the table
under some rare circumstances.
mysqlcheck
so it can deal with table names containing dashes.
NULL
field with <=> NULL
.
IGNORE INDEX
and USE INDEX
sometimes
to be ignored.
GROUP BY
queries that
didn't return any result.
MATCH ... AGAINST () >=0
was treated as if it was
>
.
SHOW PROCESSLIST
when running with an
active slave (unlikely timing bug).
TEMPORARY
MERGE
tables now.
--core-file
works on Linux (at least on kernel 2.4.18).
BDB
and ALTER TABLE
.
GROUP BY
... ORDER BY
queries. Symptom was that mysqld
died in function
send_fields
.
BLOB
values in internal
temporary tables used for some (unlikely) GROUP BY
queries.
WHERE column_name = key_column_name
was calculated as true
for NULL
values.
LEFT JOIN ... WHERE key_column=NULL
.
MyISAM
crash when using dynamic-row tables with huge numbers of
packed fields.
automake 1.5
and
libtool 1.4
.
SHOW INNODB STATUS
was used and skip-innodb
was defined.
LOCK TABLES
on Windows when one connects to a
database that contains upper case letters.
--skip-show-databases
doesn't reset the --port
option.
safe_mysqld
for some shells.
FLUSH STATUS
doesn't reset delayed_insert_threads
.
BINARY
cast on a NULL
value.
GRANT
at the same time a new
user logged in or did a USE database
.
ALTER TABLE
and RENAME TABLE
when running with
-O lower_case_table_names=1
(typically on Windows) when giving the
table name in uppercase.
-O lower_case_table_names=1
also converts database
names to lower case.
SELECT ... ORDER BY ... LIMIT
.
AND/OR
to report that they can return NULL. This fixes a
bug in GROUP BY
on AND/OR
expressions that return
NULL
.
OPTIMIZE
of locked and modified MyISAM table,
reported table corruption.
BDB
-related ALTER TABLE
bug with dropping a column
and shutting down immediately thereafter.
configure ... --localstatedir=...
.
UNSIGNED BIGINT
on AIX (again).
InnoDB
.
BEGIN
/COMMIT
around transaction in the binary log.
This makes replication honour transactions.
user.db
table.
RND()
to make it less predicatable.
GROUP BY
on result with expression that created a
BLOB
field.
GROUP BY
on columns that have NULL
values.
To solve this we now create an MyISAM
temporary table when doing a
GROUP BY
on a possible NULL
item.
From MySQL 4.0.5 we can use in memory HEAP
tables for this case.
SLAVE START
, SLAVE STOP
and automatic repair
of MyISAM tables that could cause table cache to be corrupted.
OPTIMIZE TABLE
and REPAIR TABLE
.
UNIQUE()
key where first part could contain NULL
values.
MERGE
tables and MAX()
function.
ALTER TABLE
with BDB
tables.
LOAD DATA INFILE
to binary log with no
active database.
DROP DATABASE
on a
database with InnoDB
tables.
mysql_info()
returns 0 for 'Duplicates' when using
INSERT DELAYED IGNORE
.
-DHAVE_BROKEN_REALPATH
to the Mac OS X (darwin) compile
options in `configure.in' to fix a failure under high load.
mysqldump
XML output.
ENUM
values. (This fixed a problem with
SHOW CREATE TABLE
.)
CONCAT_WS()
that cut the result.
Com_show_master_stat
to
Com_show_master_status
and Com_show_slave_stat
to
Com_show_slave_status
.
gethostbyname()
to make the client library
thread-safe even if gethostbyname_r
doesn't exist.
GRANT
.
DROP DATABASE
with symlinked directory.
DATETIME
and value outside
DATETIME
range.
BDB
doc files from the source tree, as they're not
needed (MySQL covers BDB
in its own documentation).
glibc
2.2 (needed for
make dist
).
FLOAT(X+1,X)
is not converted to FLOAT(X+2,X)
.
(This also affected DECIMAL
, DOUBLE
and REAL
types)
IF()
is case in-sensitive if the second and
third arguments are case sensitive.
gethostbyname_r
.
'+11111'
for
DECIMAL(5,0) UNSIGNED
columns, we will just drop the sign.
ISNULL(expression_which_cannot_be_null)
and
ISNULL(constant_expression)
.
glibc
library that we used with the 3.23.50
Linux-x86 binaries.
<row>
tags for mysqldump
XML output.
crash-me
and gcc
3.0.4.
@@unknown_variable
doesn't hang server.
@@VERSION
as a synonym for VERSION()
.
SHOW VARIABLES LIKE 'xxx'
is now case-insensitive.
GET_LOCK()
on HP-UX with DCE threads.
SIGINT
and SIGQUIT
problems in mysql
.
InnoDB
now retains foreign key constraints through ALTER TABLE
and CREATE/DROP INDEX
.
InnoDB
now allows foreign key constraints to be added through the
ALTER TABLE
syntax.
InnoDB
tables can now be set to automatically grow in size (autoextend).
gcc
3.0.4, which
should make them a bit faster.
--enable-named-pipe
.
WHERE key_column = 'J' or key_column='j'
.
--log-bin
with LOAD DATA
INFILE
without an active database.
RENAME TABLE
when used with
lower_case_table_names=1
(default on Windows).
DROP TABLE
on a table
that was in use by a thread that also used queries on only temporary tables.
SHOW CREATE TABLE
and PRIMARY KEY
when using
32 indexes.
SET PASSWORD
for the anonymous user.
mysql_options()
.
--enable-local-infile
.
bison
.
DATE_FORMAT()
returned empty string when used
with GROUP BY
.
mysqldump --disable-keys
to work.
NULL
.
LOAD DATA LOCAL INFILE
more secure.
glibc
library,
which has serious problems under high load and Red Hat 7.2. The 3.23.49 binary
release doesn't have this problem.
--xml
option to mysqldump
for producing XML output.
autoconf
2.52 (from autoconf
2.13)
const
tables.
InnoDB
.
InnoDB
variables were always shown in SHOW VARIABLES
as
OFF
on high-byte-first systems (like SPARC).
InnoDB
table and another
thread doing an ALTER TABLE
on the same table. Before that,
mysqld
could crash with an assertion failure in `row0row.c',
line 474.
InnoDB
SQL optimiser to favor index searches more often
over table scans.
InnoDB
tables when several large
SELECT
queries are run concurrently on a multiprocessor Linux
computer. Large CPU-bound SELECT
queries will now also generally
run faster on all platforms.
InnoDB
now prints after crash recovery the
latest MySQL binlog name and the offset InnoDB
was able to recover
to. This is useful, for example, when resynchronising a master and a
slave database in replication.
InnoDB
tables.
InnoDB
tablespace.
InnoDB
now prevents a FOREIGN KEY
declaration where the
signedness is not the same in the referencing and referenced integer columns.
SHOW CREATE TABLE
or SHOW TABLE STATUS
could cause
memory corruption and make mysqld
crash. Especially at risk was
mysqldump
, because it frequently calls SHOW CREATE TABLE
.
AUTO_INCREMENT
column were
wrapped inside one LOCK TABLES
, InnoDB
asserted in
`lock0lock.c'.
NULL
values in a UNIQUE
secondary
index for an InnoDB
table. But CHECK TABLE
was not relaxed: it
reports the table as corrupt. CHECK TABLE
no longer complains in
this situation.
SHOW GRANTS
now shows REFERENCES
instead of REFERENCE
.
SELECT ... WHERE key=@var_name OR key=@var_name2
InnoDB
keys to 500 bytes.
InnoDB
now supports NULL
in keys.
SELECT RELEASE_LOCK()
.
DO expression,[expression]
slave-skip-errors
option.
SHOW STATUS
is
now much longer.)
InnoDB
tables.
GROUP BY expr DESC
works.
t1 LEFT JOIN t2 ON t2.key=constant
.
mysql_config
now also works with binary (relocated) distributions.
InnoDB
and BDB
tables will now use index when doing an
ORDER BY
on the whole table.
BDB
tables.
ANALYZE
, REPAIR
, and OPTIMIZE TABLE
when
the thread is waiting to get a lock on the table.
ANALYZE TABLE
.
INSERT DELAYED
which could cause the binary log to have rows that were not yet written
to MyISAM
tables.
(UPDATE|DELETE) ...WHERE MATCH
bugfix.
MyISAM
files.
--core-file
now works on Solaris.
InnoDB
to complain if it cannot find
free blocks from the buffer cache during recovery.
InnoDB
insert buffer B-tree handling that could cause
crashes.
InnoDB
lock timeout handling.
ALTER TABLE
on a TEMPORARY
InnoDB
table.
OPTIMIZE TABLE
that reset index cardinality if it
was up to date.
t1 LEFT_JOIN t2 ... WHERE t2.date_column IS NULL
when
date_column was declared as NOT NULL
.
BDB
tables and keys on BLOB
columns.
MERGE
tables on OS with 32-bit file pointers.
TIME_TO_SEC()
when using negative values.
Rows_examined
count in slow query log.
AVG()
column in HAVING
.
DAYOFYEAR(column)
, will return NULL
for 0000-00-00
dates.
SELECT * FROM date_col="2001-01-01" and date_col=time_col
)
Can't write, because of unique
constraint
with some GROUP BY
queries.
sjis
character strings used within quoted table
names.
CREATE ... FULLTEXT
keys with other
storage engines than MyISAM
.
signal()
on Windows because this appears to not be
100% reliable.
WHERE col_name=NULL
on an indexed column
that had NULL
values.
LEFT JOIN ... ON (col_name = constant) WHERE col_name = constant
.
%
could cause
a core dump.
TCP_NODELAY
was not used on some systems. (Speed problem.)
The following changes are for InnoDB
tables:
InnoDB
variables to SHOW VARIABLES
.
InnoDB
tables.
DROP DATABASE
now works also for InnoDB
tables.
InnoDB
now supports datafiles and raw disk partitions bigger
than 4 GB on those operating systems that have big files.
InnoDB
calculates better table cardinality estimates for the
MySQL optimiser.
latin1
are ordered
according to the MySQL ordering.
Note: if you are using latin1
and have inserted characters whose
code is greater than 127 into an indexed CHAR
column, you should
run CHECK TABLE
on your table when you upgrade to 3.23.44, and
drop and reimport the table if CHECK TABLE
reports an error!
innodb_thread_concurrency
, helps in
performance tuning in heavily concurrent environments.
innodb_fast_shutdown
, speeds up
server shutdown.
innodb_force_recovery
, helps to save
your data in case the disk image of the database becomes corrupt.
innodb_monitor
has been improved and a new
innodb_table_monitor
added.
AUTO_INCREMENT
columns with
multiple-line inserts.
MAX(col)
is selected from an empty table, and
col
is not the first column in a multi-column index.
INSERT DELAYED
and FLUSH TABLES
introduced
in 3.23.42.
SELECT
with
many tables and multi-column indexes and 'range' type.
EXPLAIN SELECT
when using
many tables and ORDER BY
.
LOAD DATA FROM MASTER
when using table with
CHECKSUM=1
.
BDB
tables.
BDB
tables and UNIQUE
columns defined
as NULL
.
myisampack
when using pre-space filled CHAR
columns.
--safe-user-create
.
LOCK TABLES
and BDB
tables.
REPAIR TABLE
on MyISAM
tables with row
lengths in the range from 65517 to 65520 bytes.
mysqladmin shutdown
when there was
a lot of activity in other threads.
INSERT DELAYED
where delay thread could be
hanging on upgrading locks
with no apparent reason.
myisampack
and BLOB
.
MERGE
table come from the same
database.
LOAD DATA INFILE
and transactional tables.
INSERT DELAYED
with wrong column definition.
REPAIR
of some particularly broken tables.
InnoDB
and AUTO_INCREMENT
columns.
InnoDB
and RENAME TABLE
columns.
InnoDB
and BLOB
columns. If you have
used BLOB
columns larger than 8000 bytes in an InnoDB
table, it is necessary to dump the table with mysqldump
, drop it and
restore it from the dump.
InnoDB
when one could get the error Can't
execute the given command...
even when no transaction was active.
ALTER TABLE
). Now --lower_case_names
also works on Unix.
--sql-mode=value[,value[,value]]
option to mysqld
.
See section 4.1.1 mysqld
Command-line Options.
shutdown
on Solaris where the
`.pid' file wasn't deleted.
InnoDB
now supports < 4 GB rows. The former limit was 8000 bytes.
doublewrite
file flush method is used in InnoDB
.
It reduces the need for Unix fsync()
calls to a fraction and
improves performance on most Unix flavors.
InnoDB
Monitor to print a lot of InnoDB
state
information, including locks, to the standard output. This is useful in
performance tuning.
InnoDB
have been fixed.
record_buffer
to record_buffer
and
record_rnd_buffer
. To make things compatible to previous MySQL
versions, if record_rnd_buffer
is not set, then it takes the
value of record_buffer
.
ORDER BY
where some ORDER BY
parts
where wrongly removed.
ALTER TABLE
and MERGE
tables.
my_thread_init()
and my_thread_end()
to
`mysql_com.h'
--safe-user-create
option to mysqld
.
SELECT DISTINCT ... HAVING
that caused error message
Can't find record in #...
--low-priority-updates
and INSERT
statements.
slave_net_timeout
for replication.
UPDATE
and BDB
tables.
BDB
tables when using key parts.
GRANT FILE ON database.* ...
; previously
we added the DROP
privilege for the database.
DELETE FROM tbl_name ... LIMIT 0
and
UPDATE FROM tbl_name ... LIMIT 0
, which acted as though the
LIMIT
clause was not present (they deleted or updated all selected
rows).
CHECK TABLE
now checks if an AUTO_INCREMENT
column contains
the value 0.
SIGHUP
to mysqld
will now only flush the logs,
not reset the replication.
1.0e1
(no sign after e
).
--force
to myisamchk
now also updates states.
--warnings
to mysqld
. Now mysqld
prints the error Aborted connection
only if this option is used.
SHOW CREATE TABLE
when you didn't have a
PRIMARY KEY
.
innodb_unix_file_flush_method
variable to
innodb_flush_method
.
BIGINT UNSIGNED
to DOUBLE
. This caused
a problem when doing comparisons with BIGINT
values outside of the
signed range.
BDB
tables when querying empty tables.
COUNT(DISTINCT)
with LEFT JOIN
and
there weren't any matching rows.
GEMINI
table
type. GEMINI
is not released under an Open Source
license.
AUTO_INCREMENT
sequence wasn't reset when dropping
and adding an AUTO_INCREMENT
column.
CREATE ... SELECT
now creates non-unique indexes delayed.
LOCK TABLES tbl_name READ
followed by
FLUSH TABLES
put an exclusive lock on the table.
REAL @variable
values were represented with only 2 digits when
converted to strings.
LOAD TABLE FROM MASTER
failed.
myisamchk --fast --force
will no longer repair tables
that only had the open count wrong.
-lcma
thread library on HP-UX 10.20 so
that MySQL will be more stable on HP-UX.
IF()
and number of decimals in the result.
INSERT DELAYED
was waiting for
a LOCK TABLE
.
InnoDB
when tablespace was full.
MERGE
tables and big tables (> 4G) when using
ORDER BY
.
SELECT
from MERGE
table
sometimes results in incorrectly ordered rows.
REPLACE()
when using the ujis
character set.
BDB
patches 3.2.9.1 and 3.2.9.2.
--skip-stack-trace
option to mysqld
.
CREATE TEMPORARY
now works with InnoDB
tables.
InnoDB
now promotes sub keys to whole keys.
CONCURRENT
to LOAD DATA
.
max_allowed_packet
is too low to
read a very long log event from the master.
SELECT DISTINCT ... HAVING
.
SHOW CREATE TABLE
now returns TEMPORARY
for temporary tables.
Rows_examined
to slow query log.
WHERE
that didn't match any rows.
mysqlcheck
.
CHECK
,
REPAIR
, OPTIMIZE
.
InnoDB
.
SELECT * FROM tbl_name,tbl_name2 ... ORDER BY key_part1 LIMIT row_count
will use index on key_part1
instead of filesort
.
LOCK TABLE to_table WRITE,...; INSERT INTO to_table... SELECT ...
when to_table
was empty.
LOCK TABLE
and BDB
tables.
MATCH()
in HAVING
clause.
HEAP
tables with LIKE
.
--mysql-version
option to safe_mysqld
INNOBASE
to InnoDB
(because the INNOBASE
name was already used). All configure
options and mysqld
start options now use innodb
instead of innobase
. This
means that before upgrading to this version, you have to change any
configuration files where you have used innobase
options!
CHAR(255) NULL
columns.
master-host
is not set, as
long as server-id
is set and valid `master.info' is present.
SET SQL_SLAVE_SKIP_COUNTER=1; SLAVE START
after a manual sanity
check/correction of data integrity.
REGEXP
on 64-bit machines.
UPDATE
and DELETE
with WHERE unique_key_part IS NULL
didn't update/delete all rows.
INSERT DELAYED
for tables that support transactions.
TEXT
/BLOB
column
with wrong date format.
ALTER TABLE
and LOAD DATA INFILE
that disabled
key-sorting. These commands should now be faster in most cases.
FLUSH
or REPAIR
) would not use indexes for the
next query.
ALTER TABLE
to InnoDB
tables on FreeBSD.
mysqld
variables myisam_max_sort_file_size
and
myisam_max_extra_sort_file_size
.
InnoDB
.
tis620
character set to make comparisons
case-independent and to fix a bug in LIKE
for this character set.
Note: All tables that uses the tis620
character set must be
fixed with myisamchk -r
or REPAIR TABLE
!
--skip-safemalloc
option to mysqld
.
mysqld
is run
as root.
FLUSH TABLES
and TEMPORARY
tables.
(Problem with freeing the key cache and error Can't reopen table...
.)
InnoDB
with other character sets than latin1
and another problem when using many columns.
DISTINCT
and summary functions.
SET TRANSACTION ISOLATION LEVEL ...
SELECT ... FOR UPDATE
.
UPDATE
where keys weren't always used to find the
rows to be updated.
CONCAT_WS()
where it returned incorrect results.
CREATE ... SELECT
and INSERT ... SELECT
to not
allow concurrent inserts as this could make the binary log hard to repeat.
(Concurrent inserts are enabled if you are not using the binary or update log.)
glibc
2.2.
ORDER BY
.
CLIENT_TRANSACTIONS
.
SHOW VARIABLES
when using INNOBASE
tables.
SELECT DISTINCT
didn't work.
SHOW ANALYZE
for small tables.
run-all-tests
.
INNOBASE
support
to be compiled.
INNOBASE
storage engine and the BDB
storage engine
to the MySQL source distribution.
GEMINI
tables.
INSERT DELAYED
that caused threads to hang when
inserting NULL
into an AUTO_INCREMENT
column.
CHECK TABLE
/ REPAIR TABLE
that could cause
a thread to hang.
REPLACE
will not replace a row that conflicts with an
AUTO_INCREMENT
generated key.
mysqld
now only sets CLIENT_TRANSACTIONS
in
mysql->server_capabilities
if the server supports a
transaction-safe storage engine.
LOAD DATA INFILE
to allow numeric values to be read into
ENUM
and SET
columns.
ALTER TABLE ... ORDER BY
.
max_user_connections
variable to mysqld
.
max_allowed_packet
, not the
arbitrary limit of 4 MB.
=
in argument to --set-variable
.
Waiting for table
.
SHOW CREATE TABLE
now displays the UNION=()
for MERGE
tables.
ALTER TABLE
now remembers the old UNION=()
definition.
BDB
storage engine that occurred when using an index
on multi-part key where a key part may be NULL
.
MAX()
optimisation on sub-key for BDB
tables.
BDB
tables and BLOB
or TEXT
fields when joining many tables.
BDB
tables and TEXT
columns.
BLOB
key where a const row wasn't found.
mysqlbinlog
writes the timestamp value for each query.
This ensures that one gets same values for date functions like NOW()
when using mysqlbinlog
to pipe the queries to another server.
--skip-gemini
, --skip-bdb
, and --skip-innodb
options to be specified when invoking mysqld
, even if these storage
engines are not compiled in to mysqld
.
GROUP BY ... DESC
.
SET
code, when one ran SET @foo=bar
,
where bar
is a column reference, an error was not properly generated.
--character-sets-dir
option to myisampack
.
REPAIR TABLE ... EXTENDED
.
GROUP BY
on an alias,
where the alias was the same as an existing column name.
SEQUENCE()
as an example UDF function.
mysql_install_db
to use BINARY
for CHAR
columns in the privilege tables.
TRUNCATE tbl_name
to TRUNCATE TABLE tbl_name
to use the same syntax as Oracle. Until 4.0 we will also allow
TRUNCATE tbl_name
to not crash old code.
MyISAM
tables when a BLOB
was
first part of a multi-part key.
CASE
didn't work with GROUP BY
.
--sort-recover
option to myisamchk
.
myisamchk -S
and OPTIMIZE TABLE
now work on Windows.
DISTINCT
on results from functions that referred
to a group function, like:
SELECT a, DISTINCT SEC_TO_TIME(SUM(a)) FROM tbl_name GROUP BY a, b;
libmysqlclient
library.
Fixed bug in handling STOP
event after ROTATE
event in
replication.
DROP DATABASE
.
Table_locks_immediate
and Table_locks_waited
status
variables.
SET SQL_SLAVE_SKIP_COUNTER=n
command to recover from
replication glitches without a full database copy.
max_binlog_size
variable; the binary log will be rotated
automatically when the size crosses the limit.
Last_error
, Last_errno
, and Slave_skip_counter
variables to SHOW SLAVE STATUS
.
MASTER_POS_WAIT()
function.
SIGILL
, and SIGBUS
in addition to
SIGSEGV
.
mysqltest
to take care of the timing issues in the test
suite.
ALTER TABLE
can now be used to change the definition for a
MERGE
table.
MERGE
tables on Windows.
--temp-pool
option to mysqld
. Using this option
will cause most temporary files created to use a small set of names,
rather than a unique name for each new file. This is to work around a
problem in the Linux kernel dealing with creating a bunch of new files
with different names. With the old behaviour, Linux seems to "leak"
memory, as it's being allocated to the directory entry cache instead of
the disk cache.
BACKUP
, RESTORE
, CHECK
, REPAIR
, and
ANALYZE TABLE
.
FULL
to SHOW COLUMNS
. Now we show the
privilege list for the columns only if this option is given.
SHOW LOGS
when there weren't any BDB
logs.
mysql_list_fields()
. This is
to keep this code compatible with SHOW FIELDS
.
MERGE
tables didn't work on Windows.
SET PASSWORD=...
on Windows.
TRIM("foo" from "foo")
didn't return an empty string.
--with-version-suffix
option to configure
.
mysql_close()
.
RESTORE TABLE
when trying to restore from a non-existent
directory.
SET PASSWORD
.
MASTER_POS_WAIT()
.
BDB
interface code. During
testing we found and fixed many errors in the interface code.
HAVING
on an empty table could produce one result row when
it shouldn't.
HEAP
tables on Windows.
SHOW TABLE STATUS
didn't show correct average row length for tables
larger than 4G.
CHECK TABLE ... EXTENDED
didn't check row links for fixed size tables.
MEDIUM
to CHECK TABLE
.
DECIMAL()
keys on negative numbers.
HOUR()
(and some other TIME
functions) on a CHAR
column
always returned NULL
.
setrlimit()
on Linux to get
-O --open-files-limit=#
to work on Linux.
bdb_version
variable to mysqld
.
SELECT ... FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=t2.aIn this case the test in the
WHERE
clause was wrongly optimised away.
MyISAM
when deleting keys with possible NULL
values, but the first key-column was not a prefix-compressed text column.
mysql.server
to read the [mysql.server]
option file group
rather than the [mysql_server]
group.
safe_mysqld
and mysql.server
to also read the
server
option section.
Threads_created
status variable to mysqld
.
SHOW OPEN TABLES
command.
myisamdump
works against old mysqld
servers.
myisamchk -k#
so that it works again.
LOCK TABLES
will now automatically start a new transaction.
BDB
tables to not use internal subtransactions and reuse
open files to get more speed.
--mysqld=#
option to safe_mysqld
.
--fields-*-by
and
--lines-terminated-by
options to mysqldump
and
mysqlimport
. By Paul DuBois.
--safe-show-database
option to mysqld
.
have_bdb
, have_gemini
, have_innobase
,
have_raid
and have_openssl
to SHOW VARIABLES
to make it
easy to test for supported extensions.
--open-files-limit
option to mysqld
.
--open-files
option to --open-files-limit
in
safe_mysqld
.
HEAP
tables
that had many keys.
--bdb-no-sync
works.
--bdb-recover
to --bdb-no-recover
as recover should
be on by default.
BDB
locks to 10000.
BDB
tables.
mysqld_multi.sh
to use configure variables. Patch by
Christopher McCrory.
--skip-networking
on Debian Linux.
UNOPENED
in error messages.
SHOW LOGS
queries.
<=>
operator.
REPLACE
with BDB
tables.
LPAD()
and RPAD()
will shorten the result string if it's longer
than the length argument.
SHOW LOGS
command.
BDB
logs on shutdown.
PRIMARY
keys first, followed by
UNIQUE
keys.
UPDATE
involving multi-part keys where one
specified all key parts both in the update and the WHERE
part. In
this case MySQL could try to update a record that didn't match
the whole WHERE
part.
mysqld
to report the
hostname as ''
in some error messages.
HEAP
type tables; the variable
max_heap_table_size
wasn't used. Now either MAX_ROWS
or
max_heap_table_size
can be used to limit the size of a HEAP
type table.
bdb_lock_max
variable to bdb_max_lock
.
AUTO_INCREMENT
on sub-fields for BDB
tables.
ANALYZE
of BDB
tables.
BDB
tables, we now store the number of rows; this helps to optimise
queries when we need an approximation of the number of rows.
ROLLBACK
when you have updated a non-transactional table
you will get an error as a warning.
--bdb-shared-data
option to mysqld
.
Slave_open_temp_tables
status variable to mysqld
binlog_cache_size
and max_binlog_cache_size
variables to
mysqld
.
DROP TABLE
, RENAME TABLE
, CREATE INDEX
and
DROP INDEX
are now transaction endpoints.
DROP DATABASE
on a symbolically linked database, both
the link and the original database is deleted.
DROP DATABASE
to work on OS/2.
SELECT DISTINCT ... table1 LEFT JOIN
table2 ...
when table2
was empty.
--abort-slave-event-count
and
--disconnect-slave-event-count
options to mysqld
for
debugging and testing of replication.
SHOW KEYS
now shows whether key is FULLTEXT
.
mysqld_multi
. See section 4.7.3 mysqld_multi
, A Program for Managing Multiple MySQL Servers.
mysql-multi.server.sh
. Thanks to
Tim Bunce Tim.Bunce@ig.co.uk for modifying mysql.server
to
easily handle hosts running many mysqld
processes.
safe_mysqld
, mysql.server
, and mysql_install_db
have
been modified to use mysql_print_defaults
instead of various hacks
to read the `my.cnf' files. In addition, the handling of various
paths has been made more consistent with how mysqld
handles them
by default.
FULLTEXT
indexes in one table.
REPAIR
/OPTIMIZE
.
Yuri Dario
.
FLUSH TABLES tbl_name
didn't always flush the index tree
to disk properly.
--bootstrap
is now run in a separate thread. This fixes a problem
that caused mysql_install_db
to core dump on some Linux machines.
mi_create()
to use less stack space.
MATCH()
when used
with UNIQUE
key.
crash-me
and the MySQL benchmarks to also work
with FrontBase.
RESTRICT
and CASCADE
after DROP TABLE
to make
porting easier.
--slow-log
.
connect_timeout
variable to mysql
and mysqladmin
.
connect-timeout
as an alias for timeout
for option files
read by mysql_options()
.
--pager[=...]
, --no-pager
,
--tee=...
and --no-tee
to the mysql
client. The
new corresponding interactive commands are pager
, nopager
,
tee
and notee
. See section 4.8.2 mysql
, The Command-line Tool, mysql --help
and the interactive help for more information.
MyISAM
table failed.
SELECT
, UPDATE
and INSERT
statements running. The symptom was that the UPDATE
and
INSERT
queries were locked for a long time while new SELECT
statements were executed before the updates.
options_files
with mysql_options()
the
return-found-rows
option was ignored.
interactive-timeout
in the option file that
is read by mysql_options()
. This makes it possible to force
programs that run for a long time (like mysqlhotcopy
) to use the
interactive_timeout
time instead of the wait_timeout
time.
--log-long-format
then also queries that
do not use an index are logged, even if the query takes less than
long_query_time
seconds.
LEFT JOIN
which caused all columns in a reference
table to be NULL
.
NATURAL JOIN
without keys.
TEXT
or BLOB
.
DROP
of temporary tables wasn't stored in the update/binary log.
SELECT DISTINCT * ... LIMIT row_count
only returned one row.
strstr()
for SPARC and cleaned up
the `global.h' header file to avoid a problem with bad aliasing with
the compiler submitted with Red Hat 7.0. (Reported by Trond Eivind Glomsrød)
--skip-networking
option now works properly on NT.
ISAM
tables when a row with a length
of more than 65K was shortened by a single byte.
MyISAM
when running multiple updating processes on
the same table.
FLUSH TABLE tbl_name
.
--replicate-ignore-table
, --replicate-do-table
,
--replicate-wild-ignore-table
, and --replicate-wild-do-table
options to mysqld
.
IO_CACHE
mechanism instead of
FILE
to avoid OS problems when there are many files open.
--open-files
and --timezone
options to safe_mysqld
.
CREATE TEMPORARY TABLE ... SELECT ...
.
CREATE TABLE ... SELECT NULL
.
large_file_support
,net_read_timeout
,
net_write_timeout
and query_buffer_size
to SHOW VARIABLES
.
created_tmp_files
and sort_merge_passes
to SHOW STATUS
.
FOREIGN KEY
definition.
TRUNCATE table_name
as a synonym for
DELETE FROM table_name
.
BDB
key compare function when comparing part keys.
bdb_lock_max
variable to mysqld
.
mysql_connect()
now aborts on Linux if the server doesn't answer in
timeout
seconds.
SLAVE START
did not work if you started with
--skip-slave-start
and had not explicitly run CHANGE MASTER TO
.
SHOW MASTER STATUS
to be consistent with
SHOW SLAVE STATUS
. (It now has no directory in the log name.)
PURGE MASTER LOGS TO
.
SHOW MASTER LOGS
.
--safemalloc-mem-limit
option to mysqld
to simulate memory
shortage when compiled with the --with-debug=full
option.
SHOW SLAVE STATUS
was using an uninitialised mutex if the slave had
not been started yet.
ELT()
and MAKE_SET()
when the query used
a temporary table.
CHANGE MASTER TO
without specifying MASTER_LOG_POS
would
set it to 0 instead of 4 and hit the magic number in the master binlog.
ALTER TABLE ... ORDER BY ...
syntax added. This will create the
new table with the rows in a specific order.
MyISAM
tables sometimes failed
when the datafile was corrupt.
SHOW CREATE
when using AUTO_INCREMENT
columns.
BDB
tables to use new compare function in Berkeley DB 3.2.3.
latin5
(turkish) character set.
FLUSH MASTER
and FLUSH SLAVE
to RESET MASTER
and RESET SLAVE
.
<>
to work properly with NULL
.
SUBSTRING_INDEX()
and REPLACE()
.
(Patch by Alexander Igonitchev)
CREATE TEMPORARY TABLE IF NOT EXISTS
not to produce an error
if the table exists.
PRIMARY KEY
in a BDB
table, a hidden
PRIMARY KEY
will be created.
BDB
tables.
LEFT JOIN
in some cases preferred a full table scan when there was
no WHERE
clause.
--log-slow-queries
, don't count the time waiting for a lock.
MyISAM
tables if you start mysqld
with
--myisam-recover
.
TYPE=
keyword from CHECK
and
REPAIR
. Allow CHECK
options to be combined. (You can still
use TYPE=
, but this usage is deprecated.)
--replicate-rewrite-db
option to mysqld
.
--skip-slave-start
option to mysqld
.
INSERT INTO foo(some_key)
values (1),(1)
) erroneously terminated the slave thread.
DISTINCT
is only used on columns
from some of the tables.
1e1
).
SHOW GRANTS
didn't always show all column grants.
--default-extra-file=#
option to all MySQL clients.
INSERT
statements now are initialised properly.
UPDATE
didn't always work when used with a range on a timestamp that
was part of the key that was used to find rows.
FULLTEXT
index when inserting a NULL
column.
mkstemp()
instead of tempnam()
. Based
on a patch from John Jones.
databasename
works as second argument to mysqlhotcopy
.
UMASK
and UMASK_DIR
environment variables
now can be specified in octal by beginning the value with a zero.
RIGHT JOIN
. This makes RIGHT
a reserved word.
@@IDENTITY
as a synonym for LAST_INSERT_ID()
.
(This is for MSSQL compatibility.)
myisamchk
and REPAIR
when using FULLTEXT
index.
LOAD DATA INFILE
now works with FIFOs.
(Patch by Toni L. Harbaugh-Blackford.)
FLUSH LOGS
broke replication if you specified a log name with an
explicit extension as the value of the log-bin
option.
MyISAM
with packed multi-part keys.
CHECK TABLE
on Windows.
FULLTEXT
index always used the koi8_ukr
character set.
CHECK TABLE
.
MyISAM
repair/reindex code didn't use the --tmpdir
option for its temporary files.
BACKUP TABLE
and RESTORE TABLE
.
CHANGE MASTER TO
when the slave did not have
the master to start with.
Time
in the processlist for Connect
of
the slave thread.
FLUSH MASTER
if you didn't specify
a filename argument to --log-bin
.
--memlock
option to mysqld
to lock mysqld
in memory on systems with the mlockall()
call (as in Solaris).
HEAP
tables didn't use keys properly. (Bug from 3.23.23.)
MERGE
tables (keys, mapping, creation,
documentation...). See section 7.2 MERGE
Tables.
mysqldump
from 3.23 which caused some CHAR
columns
not to be quoted.
analyze
, check
, optimize
and repair code.
OPTIMIZE TABLE
is now mapped to REPAIR
with statistics and
sorting of the index tree. This means that for the moment it only
works on MyISAM
tables.
ORDER BY
bug with BDB
tables.
mysqld
couldn't remove the `.pid' file
under Windows.
--log-isam
to log MyISAM
tables instead of isam
tables.
CHECK TABLE
to work on Windows.
pwrite()
safe on Windows.
created_tmp_disk_tables
variable to mysqld
.
TIMESTAMP(X)
columns, MySQL now reports columns with X
other than 14 or 8 to be strings.
latin1
as it was before MySQL Version 3.23.23.
Any table that was created or modified with 3.23.22 must be repaired if it has
CHAR
columns that may contain characters with ASCII values greater than
128!
BDB
tables and reading on a unique (not primary) key.
win1251
character set (it's now only marked deprecated).
REPAIR TABLE
or myisamchk
before use!
--core-file
option to mysqld
to get a core file on
Linux if mysqld
dies on the SIGSEGV
signal.
mysql
now starts with option
--no-named-commands
(-g
) by default. This option can be
disabled with --enable-named-commands
(-G
). This may cause
incompatibility problems in some cases, for example, in SQL scripts that
use named commands without a semicolon, etc.! Long format commands
still work from the first line.
DROP TABLE
statements at
the same time.
LEFT JOIN
on an
empty table.
mysqld
with incorrect options.
free()
bug in mysqlimport
.
MyISAM
index handling of
DECIMAL
/NUMERIC
keys.
MyISAM
tables. In some contexts,
usage of MIN(key_part)
or MAX(key_part)
returned an empty set.
mysqlhotcopy
to use the new FLUSH TABLES table_list
syntax. Only tables which are being backed up are flushed now.
--enable-thread-safe-client
so
that both non-threaded (-lmysqlclient
) and threaded
(-lmysqlclient_r
) libraries are built. Users who linked
against a threaded -lmysqlclient
will need to link against
-lmysqlclient_r
now.
RENAME TABLE
command.
NULL
values in COUNT(DISTINCT ...)
.
ALTER TABLE
, LOAD DATA INFILE
on empty tables and
INSERT ... SELECT ...
on empty tables to create non-unique indexes
in a separate batch with sorting. This will make the above calls much
faster when you have many indexes.
ALTER TABLE
now logs the first used insert_id correctly.
BLOB
column.
DATE_ADD/DATE_SUB
where it returned a datetime instead
of a date.
***DEAD***
in SHOW PROCESSLIST
.
pthread_rwlock_rdlock
code.
HEAP
table, all rows
weren't always deleted.
HEAP
tables for searches on a part
index.
SELECT
on part keys to work with BDB
tables.
INSERT INTO bdb_table ... SELECT
to work with BDB
tables.
CHECK TABLE
now updates key statistics for the table.
ANALYZE TABLE
will now only update tables that have been changed
since the last ANALYZE
. Note that this is a new feature and tables
will not be marked to be analysed until they are updated in any way with
3.23.23 or newer. For older tables, you have to do CHECK TABLE
to update the key distribution.
CHECK
, ANALYZE
,
REPAIR
and SHOW CREATE
commands.
CHANGE MASTER TO
statement.
FAST
, QUICK
EXTENDED
check types to
CHECK TABLES
.
myisamchk
so that --fast
and
--check-only-changed
are also honored with --sort-index
and
--analyze
.
LOAD TABLE FROM MASTER
that did not lock the
table during index re-build.
LOAD DATA INFILE
broke replication if the database was excluded
from replication.
SHOW SLAVE STATUS
and SHOW MASTER STATUS
.
SLAVE STOP
now will not return until the slave thread actually exits.
MATCH()
function and FULLTEXT
index type
(for MyISAM
files). This makes FULLTEXT
a reserved word.
lex_hash.h
is created properly for each MySQL
distribution.
MASTER
and COLLECTION
are not reserved words.
--slow-query-log
didn't contain the whole queries.
BDB
tables are rolled back if the
connection is closed unexpectedly.
gcc
2.96 (intel) and gcc
2.9
(IA-64) in gen_lex_hash.c
.
host=
in the
`my.cnf' file.
DATE_ADD()
/DATE_SUB()
against a number.
-F, --fast
for myisamchk
. Added
-C, --check-only-changed
option to myisamchk
.
ANALYZE tbl_name
to update key statistics for tables.
0x...
to be regarded as integers by default.
SHOW PROCESSLIST
.
auto-rehash
on reconnect for the mysql
client.
MyISAM
, where the index file couldn't
get bigger than 64M.
SHOW MASTER STATUS
and SHOW SLAVE STATUS
.
mysql_character_set_name()
function to the
MySQL C API.
mysql_config
script.
<
or >
with a char column that was only
partly indexed.
mysqladmin
to use CREATE DATABASE
and DROP
DATABASE
statements instead of the old deprecated API calls.
chown
warning in safe_mysqld
.
ORDER BY
that was introduced in 3.23.19.
DELETE FROM tbl_name
to do a drop+create of
the table if we are in AUTOCOMMIT
mode (needed for BDB
tables).
ISAM
/MyISAM
index files get full during an INSERT
/UPDATE
.
myisamchk
didn't correctly update row checksum when used with
-ro
(this only gave a warning in subsequent runs).
REPAIR TABLE
so that it works with tables without indexes.
DROP DATABASE
.
LOAD TABLE FROM MASTER
is sufficiently bug-free to announce it as
a feature.
MATCH
and AGAINST
are now reserved words.
DELETE FROM tbl_name
removed the `.frm' file.
SHOW CREATE TABLE
.
GPL
for the server code and utilities and
to LGPL for the client libraries.
MyISAM
table
when doing update based on key on a table with many keys and some key changed
values.
ORDER BY
can now use REF
keys to find subsets of the rows
that need to be sorted.
print_defaults
program to my_print_defaults
to avoid name confusion.
NULLIF()
to work as required by SQL-99.
net_read_timeout
and net_write_timeout
as startup
parameters to mysqld
.
myisamchk --sort-records
on a table with prefix compressed index.
pack_isam
and myisampack
to the standard MySQL
distribution.
BEGIN WORK
(the same as BEGIN
).
ORDER BY
on a CONV()
expression.
LOAD TABLE FROM MASTER
.
FLUSH MASTER
and FLUSH SLAVE
.
FLUSH TABLES WITH READ LOCK
to make a global lock suitable for
making a copy of MySQL datafiles.
CREATE TABLE ... SELECT ... PROCEDURE
now works.
GROUP BY
on VARCHAR/CHAR
columns.
READ
and a
WRITE
lock.
myisamchk
and RAID
tables.
FIND_IN_SET()
when the first argument was NULL
.
LEFT JOIN
and ORDER BY
where the first
table had only one matching row.
duplicated key
problem when doing big GROUP BY
operations.
(This bug was probably introduced in 3.23.15.)
INNER JOIN
to match SQL-99.
NATURAL JOIN
syntax.
BDB
interface.
--no-defaults
and --defaults-file
to
safe_mysqld.sh
and mysql_install_db.sh
.
USE INDEX
works with PRIMARY
keys.
BEGIN
statement to start a transaction in AUTOCOMMIT
mode.
AUTOCOMMIT
mode
and if there is a pending transaction. If there is a pending transaction,
the client library will give an error before reconnecting to the server to
let the client know that the server did a rollback.
The protocol is still backward-compatible with old clients.
KILL
now works on a thread that is locked on a 'write' to a dead client.
log-slave-updates
option to mysqld
, to allow
daisy-chaining the slaves.
pthread_t
is not the same as int
.
INSERT DELAYED
code when doing
ALTER TABLE
.
INSERT DELAYED
.
SLAVE START
and SLAVE STOP
statements.
TYPE=QUICK
option to CHECK
and to REPAIR
.
REPAIR TABLE
when the table was in use by other threads.
gdb
when one does a lot of reconnects. This will also improve
systems where you can't use persistent connections.
UPDATE IGNORE
will not abort if an update results in a
DUPLICATE_KEY
error.
CREATE TEMPORARY TABLE
commands in the update log.
delay_key_write
tables and CHECK TABLE
.
replicate-do-db
and replicate-ignore-db
options to
mysqld
, to restrict which databases get replicated.
SQL_LOG_BIN
option.
mysqld
as root
, you must now use the --user=root
option.
FLUSH TABLES
command.
slow_launch_time
variable and the Slow_launch_threads
status variable to mysqld
. These can be examined with
mysqladmin variables
and mysqladmin extended-status
.
INET_NTOA()
and INET_ATON()
.
IF()
now depends on the second and
third arguments and not only on the second argument.
myisamchk
could go into a loop when trying to
repair a crashed table.
INSERT DELAYED
to update log if SQL_LOG_UPDATE=0
.
REPLACE
on HEAP
tables.
SHOW VARIABLES
output.
DELETE
of many rows on a table with
compressed keys where MySQL scanned the index to find the rows.
CHECK
on table with deleted keyblocks.
LAST_INSERT_ID()
to update
a table with an AUTO_INCREMENT
key.
NULLIF()
function.
LOAD DATA INFILE
on a table with
BLOB/TEXT
columns.
MyISAM
to be faster when inserting keys in sorted order.
EXPLAIN SELECT ...
now also prints out whether MySQL needs to
create a temporary table or use file sorting when resolving the SELECT
.
ORDER BY
parts where the part is a
constant expression in the WHERE
part. Indexes can now be used
even if the ORDER BY
doesn't match the index exactly, as long as
all the unused index parts and all the extra ORDER BY
columns are constants in the WHERE
clause. See section 5.4.3 How MySQL Uses Indexes.
UPDATE
and DELETE
on a whole unique key in the WHERE
part
are now faster than before.
RAID_CHUNKSIZE
to be in 1024-byte increments.
LOAD_FILE(NULL)
.
mysql_real_escape_string()
function to the MySQL C API.
CONCAT()
where one of the arguments was a function
that returned a modified argument.
myisamchk
, where it updated the header in
the index file when one only checked the table. This confused the
mysqld
daemon if it updated the same table at the same time. Now
the status in the index file is only updated if one uses
--update-state
. With older myisamchk
versions you should
use --read-only
when only checking tables, if there is the
slightest chance that the mysqld
server is working on the table at the
same time!
DROP TABLE
is logged in the update log.
DECIMAL()
key field
where the column data contained leading zeros.
myisamchk
when the AUTO_INCREMENT
column isn't
the first key.
DATETIME
in ISO8601 format: 2000-03-12T12:00:00
mysqld
binary can now handle many different
character sets (you can choose which when starting mysqld
).
REPAIR TABLE
.
mysql_thread_safe()
function to the MySQL C API.
UMASK_DIR
environment variable.
CONNECTION_ID()
function to return the client connection thread
ID.
=
on BLOB
or VARCHAR BINARY
keys, where
only a part of the column was indexed, the whole column of the result
row wasn't compared.
sjis
character set and ORDER BY
.
GROUP BY
part.
LOCK TABLE
command; this fixed the problem one got when running
the test-ATIS test with --fast
or --check-only-changed
.
SQL_BUFFER_RESULT
option to SELECT
.
CHECK TABLE
command.
MyISAM
in 3.23.12 that didn't get into the source
distribution because of CVS problems.
mysqladmin shutdown
will wait for the local server
to close down.
print_defaults
program to the `.rpm' files. Removed
mysqlbug
from the client `.rpm' file.
MyISAM
involving REPLACE ... SELECT ...
which could
give a corrupted table.
myisamchk
where it incorrectly reset the
AUTO_INCREMENT
value.
DISTINCT
on HEAP
temporary tables to use hashed
keys to quickly find duplicated rows. This mostly concerns queries of
type SELECT DISTINCT ... GROUP BY ...
. This fixes a problem where
not all duplicates were removed in queries of the above type. In
addition, the new code is MUCH faster.
IF NOT EXISTS
clause to CREATE DATABASE
.
--all-databases
and --databases
options to mysqldump
to allow dumping of many databases at the same time.
DECIMAL()
index in MyISAM
tables.
mysqladmin shutdown
on a local connection, mysqladmin
now waits until the PID file is gone before terminating.
COUNT(DISTINCT ...)
queries.
myisamchk
works properly with RAID tables.
LEFT JOIN
and key_field IS NULL
.
net_clear()
which could give the error Aborted
connection
in the MySQL clients.
USE INDEX (key_list)
and IGNORE INDEX (key_list)
as
parameters in SELECT
.
DELETE
and RENAME
should now work on RAID
tables.
ALTER TABLE tbl_name ADD (field_list)
syntax.
GRANT/REVOKE ALL PRIVILEGES
doesn't affect
GRANT OPTION
.
SHOW GRANTS
.
UNIQUE INDEX
in CREATE
statements.
mysqlhotcopy
- fast online hot-backup utility for local
MySQL databases. By Tim Bunce.
mysqlaccess
. Thanks to Steve Harvey for this.
--i-am-a-dummy
and --safe-updates
options to mysql
.
select_limit
and max_join_size
variables to mysql
.
SQL_MAX_JOIN_SIZE
and SQL_SAFE_UPDATES
options.
READ LOCAL
lock that doesn't lock the table for concurrent
inserts. (This is used by mysqldump
.)
LOCK TABLES ... READ
doesn't anymore allow concurrent
inserts.
--skip-delay-key-write
option to mysqld
.
_rowid
can now be used as an alias for an integer type unique indexed
column.
SIGPIPE
when compiling with --thread-safe-clients
to make things safe for old clients.
LOCK TABLES
.
INSERT DELAYED
.
date_col BETWEEN const_date AND const_date
works.
NULL
in a table with
BLOB/TEXT
columns.
WHERE K1=1 and K3=2 and (K2=2 and K4=4 or K2=3 and K4=5)
source
command to mysql
to allow reading of batch files
inside the mysql
client. Original patch by Matthew Vanecek.
WITH GRANT OPTION
option.
GRANT
error when using tables from many
databases in the same query.
SELECT
when using many overlapping indexes.
MySQL should now be able to choose keys even better when there
are many keys to choose from.
=
). For example, the following type of queries should now
be faster: SELECT * from key_part_1=const and key_part_2 > const2
VARCHAR
columns to CHAR
columns
didn't change row type from dynamic to fixed.
SELECT FLOOR(POW(2,63))
.
mysqld
startup option from --delay-key-write
to
--delay-key-write-for-all-tables
.
read-next-on-key
to HEAP
tables. This should fix all
problems with HEAP
tables when using non-UNIQUE
keys.
--log-slow-queries
option to mysqld
to log all queries
that take a long time to a separate log file with a time indicating how
long the query took.
WHERE key_col=RAND(...)
.
SELECT ... LEFT JOIN ... key_col IS NULL
,
when key_col
could contain NULL
values.
LOAD DATA INFILE
.
NISAM
.
ISAM
when doing some ORDER BY ... DESC
queries.
--delay-key-write
didn't enable delayed key writing.
TEXT
column which involved only case changes.
INSERT DELAYED
doesn't update timestamps that are given.
YEARWEEK()
and options x
, X
, v
and
V
to DATE_FORMAT()
.
MAX(indexed_column)
and HEAP
tables.
BLOB NULL
keys and LIKE "prefix%"
.
MyISAM
and fixed-length rows < 5 bytes.
GROUP BY
queries.
ENUM
field value
was too big.
pthread_mutex_timedwait
,
which is used with INSERT DELAYED
. See section 2.6.2 Linux Notes (All Linux Versions).
MyISAM
with keys > 250 characters.
MyISAM
one can now do an INSERT
at the same time as other
threads are reading from the table.
max_write_lock_count
variable to mysqld
to force a
READ
lock after a certain number of WRITE
locks.
delay_key_write
on show variables
.
concurrency
variable to thread_concurrency
.
LOCATE(substr,str)
, POSITION(substr IN str)
,
LOCATE(substr,str,pos)
, INSTR(str,substr)
,
LEFT(str,len)
, RIGHT(str,len)
,
SUBSTRING(str,pos,len)
, SUBSTRING(str FROM pos FOR len)
,
MID(str,pos,len)
, SUBSTRING(str,pos)
, SUBSTRING(str
FROM pos)
, SUBSTRING_INDEX(str,delim,count)
, RTRIM(str)
,
TRIM([[BOTH | TRAILING] [remstr] FROM] str)
,
REPLACE(str,from_str,to_str)
, REVERSE(str)
,
INSERT(str,pos,len,newstr)
, LCASE(str)
, LOWER(str)
,
UCASE(str)
and UPPER(str)
; patch by Wei He.
FULL
to SHOW PROCESSLIST
.
--verbose
to mysqladmin
.
HEAP
to MyISAM
.
HEAP
tables when doing insert + delete + insert + scan the
table.
REPLACE()
and LOAD DATA INFILE
.
interactive_timeout
variable to mysqld
.
mysql_data_seek()
from ulong
to
ulonglong
.
-O lower_case_table_names={0|1}
option to mysqld
to allow
users to force table names to lowercase.
SELECT ... INTO DUMPFILE
.
--ansi
option to mysqld
to make some functions
SQL-99 compatible.
#sql
.
`
("
in --ansi
mode).
snprintf()
when printing floats to avoid some buffer
overflows on FreeBSD.
FLOOR()
overflow safe on FreeBSD.
--quote-names
option to mysqldump
.
PRIMARY KEY NOT NULL
.
encrypt()
to be thread-safe and not reuse buffer.
mysql_odbc_escape_string()
function to support big5 characters in
MyODBC.
FLOAT
and DOUBLE
(without any length modifiers)
no longer are fixed decimal point numbers.
FLOAT(X)
: Now this is the same as FLOAT
if
X
<= 24 and a DOUBLE
if 24 < X
<= 53.
DECIMAL(X)
is now an alias for DECIMAL(X,0)
and DECIMAL
is now an alias for DECIMAL(10,0)
. The same goes for NUMERIC
.
ROW_FORMAT={default | dynamic | fixed | compressed}
to
CREATE_TABLE
.
DELETE FROM table_name
didn't work on temporary tables.
CHAR_LENGTH()
to be multi-byte character safe.
ORD(string)
.
SELECT DISTINCT ... ORDER BY RAND()
.
MyISAM
level.
ALTER TABLE
didn't work.
AUTO_INCREMENT
column in two keys
MyISAM
, you now can have an AUTO_INCREMENT
column as a key
sub part:
CREATE TABLE foo (a INT NOT NULL AUTO_INCREMENT, b CHAR(5), PRIMARY KEY (b,a))
MyISAM
with packed char keys that could be NULL
.
AS
on field name with CREATE TABLE table_name SELECT ...
didn't
work.
NATIONAL
and NCHAR
when defining character columns.
This is the same as not using BINARY
.
NULL
columns in a PRIMARY KEY
(only in UNIQUE
keys).
LAST_INSERT_ID()
if one uses this in ODBC:
WHERE auto_increment_column IS NULL
. This seems to fix some problems
with Access.
SET SQL_AUTO_IS_NULL=0|1
now turns on/off the handling of
searching after the last inserted row with WHERE
auto_increment_column IS NULL
.
concurrency
to mysqld
for Solaris.
--relative
option to mysqladmin
to make
extended-status
more useful to monitor changes.
COUNT(DISTINCT ...)
on an empty table.
LOAD DATA INFILE
and BLOB
columns.
~
(negation).
UDF
functions.
DATETIME
into a TIME
column no longer will
try to store 'days' in it.
SUM()
.)
LIKE "%"
on an index that may have NULL
values.
REVOKE ALL PRIVILEGES
didn't revoke all privileges.
GRANT
option for a database, he couldn't grant
privileges to other users.
SHOW GRANTS FOR user
(by Sinisa).
date_add
syntax: date/datetime + INTERVAL # interval_type
.
By Joshua Chamas.
LOAD DATA REPLACE
.
REGEXP
is now case-insensitive if you use non-binary strings.
MyISAM
.
ASC
is now the default again for ORDER BY
.
LIMIT
to UPDATE
.
mysql_change_user()
function to the MySQL C API.
SHOW VARIABLES
.
--[whitespace]
comments.
INSERT into tbl_name VALUES ()
, that is, you may now specify
an empty value list to insert a row in which each column is set to its
default value.
SUBSTRING(text FROM pos)
to conform to SQL-99. (Before this
construct returned the rightmost pos
characters.)
SUM()
with GROUP BY
returned 0 on some systems.
SHOW TABLE STATUS
.
DELAY_KEY_WRITE
option to CREATE TABLE
.
AUTO_INCREMENT
on any key part.
YEAR(NOW())
and YEAR(CURDATE())
.
CASE
construct.
COALESCE()
.
SELECT * FROM table_name WHERE
key_part1 >= const AND (key_part2 = const OR key_part2 = const)
. The
bug was that some rows could be duplicated in the result.
myisamchk
without -a
updated the index
distribution incorrectly.
SET SQL_LOW_PRIORITY_UPDATES=1
was causing a parse error.
WHERE
clause.
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100
'1999-01-00'
.
SELECT ... WHERE key_part1=const1 AND
key_part_2=const2 AND key_part1=const4 AND key_part2=const4
; indextype
should be range
instead of ref
.
egcs
1.1.2 optimiser bug (when using BLOB
values) on Linux Alpha.
LOCK TABLES
combined with DELETE FROM table
.
MyISAM
tables now allow keys on NULL
and BLOB/TEXT
columns.
SELECT ... FROM t1 LEFT JOIN t2 ON ... WHERE t2.not_null_column IS NULL
.
ORDER BY
and GROUP BY
can be done on functions.
ORDER BY RAND()
.
WHERE key_column = function
.
WHERE key_column = col_name
even if
the columns are not identically packed.
WHERE col_name IS NULL
.
MyISAM
tables)
HEAP
temporary tables to MyISAM
tables
in case of ``table is full'' errors.
--init-file=file_name
option to mysqld
.
COUNT(DISTINCT value, [value, ...])
.
CREATE TEMPORARY TABLE
now creates a temporary table, in its own
namespace, that is automatically deleted if connection is dropped.
CASE
): CASE, THEN, WHEN, ELSE and END
.
EXPORT_SET()
and MD5()
.
MyISAM
) with a lot of new features.
See section 7.1 MyISAM
Tables.
HEAP
tables which are extremely fast for
lookups.
LOAD_FILE(filename)
to get the contents of a file as a
string value.
<=>
which will act as =
but will return TRUE
if both arguments are NULL
. This is useful for comparing changes
between tables.
EXTRACT(interval FROM datetime)
function.
FLOAT(X)
are not rounded on storage and may be
in scientific notation (1.0 E+10) when retrieved.
REPLACE
is now faster than before.
LIKE
character comparison to behave as =
;
This means that 'e' LIKE 'é'
is now true. (If the line doesn't
display correctly, the latter 'e' is a French 'e' with a dot above.)
SHOW TABLE STATUS
returns a lot of information about the tables.
LIKE
to the SHOW STATUS
command.
Privileges
column to SHOW COLUMNS
.
Packed
and Comment
columns to SHOW INDEX
.
CREATE TABLE ... COMMENT "xxx"
).
UNIQUE
, as in
CREATE TABLE table_name (col INT not null UNIQUE)
CREATE TABLE table_name SELECT ...
CREATE TABLE IF NOT EXISTS ...
CHAR(0)
columns.
DATE_FORMAT()
now requires `%' before any format character.
DELAYED
is now a reserved word (sorry about that :( ).
analyse
, file: `sql_analyse.c'.
This will describe the data in your query. Try the following:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])This procedure is extremely useful when you want to check the data in your table!
BINARY
cast to force a string to be compared in case-sensitive fashion.
--skip-show-database
option to mysqld
.
UPDATE
now also works
with BLOB
/TEXT
columns.
INNER
join syntax. NOTE: This made INNER
a reserved word!
IP/NETMASK
syntax.
NOT NULL DATE/DATETIME
column with IS
NULL
, this is changed to a compare against 0
to satisfy some ODBC
applications. (By shreeve@uci.edu.)
NULL IN (...)
now returns NULL
instead of 0
. This will
ensure that null_column NOT IN (...)
doesn't match
NULL
values.
TIME
columns.
TIME
strings to be more strict. Now the
fractional second part is detected (and currently skipped). The
following formats are supported:
DATETIME
.
LOW_PRIORITY
attribute to LOAD DATA INFILE
.
LOAD DATA INFILE
.
DECIMAL(x,y)
now works according to SQL-99.
LAST_INSERT_ID()
is now updated for INSERT INTO ... SELECT
.
SELECT DISTINCT
is much faster; it uses the new UNIQUE
functionality in MyISAM
. One difference compared to MySQL Version 3.22
is that the output of DISTINCT
is no longer sorted.
mysql_num_fields()
on
a MYSQL
object, you must use mysql_field_count()
instead.
LIBWRAP
; patch by Henning P. Schmiedehausen.
AUTO_INCREMENT
for other than numerical columns.
AUTO_INCREMENT
will now automatically make the column
NOT NULL
.
NULL
as the default value for AUTO_INCREMENT
columns.
SQL_BIG_RESULT
; SQL_SMALL_RESULT
is now default.
--enable-large-files
and --disable-large-files
switches
to configure
. See `configure.in' for some systems where this is
automatically turned off because of broken implementations.
readline
to 4.0.
CREATE TABLE
options: PACK_KEYS
and CHECKSUM
.
--default-table-type
option to mysqld
.
The 3.22 version has faster and safer connect code than version 3.21, as well as a lot of new nice enhancements. As there aren't really any major changes, upgrading from 3.21 to 3.22 should be very easy and painless. See section 2.5.4 Upgrading from Version 3.21 to 3.22.
STD()
.
ISAM
library from 3.23.
INSERT DELAYED
.
LEFT JOIN
/STRAIGHT_JOIN
on a table with only one row.
GROUP BY
on TINYBLOB
columns; this
caused bugzilla to not show rows in some queries.
LOCK TABLE
.
SELECT DISTINCT
queries.
mysqlhotcopy
, a fast online hot-backup utility for local MySQL
databases. By Tim Bunce.
mysqlaccess
. Thanks to Steve Harvey for this.
GROUP
functions.
ISAM
code when deleting rows on tables with
packed indexes.
SELECT
when using many overlapping indexes.
SELECT FLOOR(POW(2,63))
.
WITH GRANT OPTION
option.
GROUP BY
queries.
ENUM
field value
was too big.
mysqlshutdown.exe
and mysqlwatch.exe
to the Windows
distribution.
ORDER BY
on a reference key.
INSERT DELAYED
doesn't update timestamps that are given.
LEFT JOIN
and COUNT()
on a column which
was declared NULL
+ and it had a DEFAULT
value.
CONCAT()
in a WHERE
clause.
AVG()
and STD()
with NULL
values.
BLOB
columns.
ROUND()
will now work on Windows.
BLOB/TEXT
column argument to
REVERSE()
.
/*! */
with version numbers.
SUBSTRING(text FROM pos)
to conform to SQL-99. (Before this
construct returned the rightmost 'pos' characters.)
LOCK TABLES
combined with DELETE FROM table
INSERT ... SELECT
didn't use BIG_TABLES
.
SET SQL_LOW_PRIORITY_UPDATES=#
didn't work.
GRANT ... IDENTIFIED BY
SELECT * FROM table_name WHERE key_part1 >= const AND (key_part2 = const
OR key_part2 = const)
.
ISAM
.
DATA
is no longer a reserved word.
LOCK TABLES table_name READ; FLUSH TABLES;
isamchk
should now work on Windows.
libtool
1.3.2.
configure
.
--defaults-file=file_name
to option file handling to force
use of only one specific option file.
CREATE
syntax to ignore MySQL Version 3.23 keywords.
INSERT DELAYED
on a table locked with
LOCK TABLES
.
DROP TABLE
on a table that was
locked by another thread.
GRANT/REVOKE
commands in the update log.
isamchk
to detect a new error condition.
NATURAL LEFT JOIN
.
mysql_close()
directly after
mysql_init()
.
delayed_insert_thread
counting when you couldn't create a new
delayed_insert thread.
CONCAT()
with many arguments.
DELETE FROM TABLE
when table was locked by
another thread.
LEFT JOIN
involving empty tables.
mysql.db
column from CHAR(32)
to CHAR(60)
.
MODIFY
and DELAYED
are no longer reserved words.
TIME
column.
Host '...' is not allowed to connect to this MySQL
server
after one had inserted a new MySQL user with a GRANT
command.
TCP_NODELAY
also on Linux (should give faster TCP/IP
connections).
STD()
for big tables when result should be 0.
INSERT DELAYED
had some garbage at end in the update log.
mysql_install_db
(from 3.22.17).
BLOB
columns.
shutdown
not all threads died properly.
-O flush_time=#
to mysqld
. This is mostly
useful on Windows and tells how often MySQL should close all
unused tables and flush all updated tables to disk.
VARCHAR
column compared with CHAR
column
didn't use keys efficiently.
--log-update
and connecting
without a default database.
configure
and portability problems.
LEFT JOIN
on tables that had circular dependencies caused
mysqld
to hang forever.
mysqladmin processlist
could kill the server if a new user logged in.
DELETE FROM tbl_name WHERE key_column=col_name
didn't find any matching
rows. Fixed.
DATE_ADD(column, ...)
didn't work.
INSERT DELAYED
could deadlock with status 'upgrading lock'
ENCRYPT()
to take longer salt strings than 2 characters.
longlong2str
is now much faster than before. For Intel x86
platforms, this function is written in optimised assembler.
MODIFY
keyword to ALTER TABLE
.
GRANT
used with IDENTIFIED BY
didn't take effect until privileges
were flushed.
SHOW STATUS
.
ORDER BY
with 'only index' optimisation when there
were multiple key definitions for a used column.
DATE
and DATETIME
columns are now up to 5 times faster than
before.
INSERT DELAYED
can be used to let the client do other things while the
server inserts rows into a table.
LEFT JOIN USING (col1,col2)
didn't work if one used it with tables
from 2 different databases.
LOAD DATA LOCAL INFILE
didn't work in the Unix version because of
a missing file.
VARCHAR
/BLOB
on very short rows (< 4 bytes);
error 127 could occur when deleting rows.
BLOB/TEXT
through formulas didn't work for short (< 256 char)
strings.
GRANT
on a new host, mysqld
could die on the first
connect from this host.
ORDER BY
on column name that was the same
name as an alias.
BENCHMARK(loop_count,expression)
function to time expressions.
mysqld
to make it easier to start from shell
scripts.
TIMESTAMP
column to NULL
didn't record the timestamp
value in the update log.
INSERT INTO TABLE ... SELECT ... GROUP BY
.
localtime_r()
on Windows so that it will no lonher crash
if your date is > 2039, but instead will return a time of all zero.
^Z
(ASCII 26) to \Z
as ^Z
doesn't
work with pipes on Windows.
mysql_fix_privileges
adds a new column to the mysql.func
to
support aggregate UDF functions in future MySQL releases.
NOW()
, CURDATE()
or CURTIME()
directly in a
column didn't work.
SELECT COUNT(*) ... LEFT JOIN ...
didn't work with no WHERE
part.
pthread_cond()
on the Windows version.
get_lock()
now correctly times out on Windows!
DATE_ADD()
and DATE_SUB()
in a
WHERE
clause.
GRANT ... TO user
IDENTIFIED BY 'password'
syntax.
GRANT
checking with SELECT
on many tables.
mysql_fix_privilege_tables
to the RPM
distribution. This is not run by default because it relies on the client
package.
SQL_SMALL_RESULT
to SELECT
to force use of
fast temporary tables when you know that the result set will be small.
DATE_ADD
/DATE_SUB()
doesn't have enough days.
GRANT
compares columns in case-insensitive fashion.
ALTER TABLE
dump core in
some contexts.
user@hostname
can now include `.' and `-'
without quotes in the context of the GRANT
, REVOKE
and
SET PASSWORD FOR ...
statements.
isamchk
for tables which need big temporary files.
mysql_fix_privilege_tables
script
when you upgrade to this version! This is needed because of the new
GRANT
system. If you don't do this, you will get Access
denied
when you try to use ALTER TABLE
, CREATE INDEX
, or
DROP INDEX
.
GRANT
to allow/deny users table and column access.
USER()
to return a value in user@host
format.
Formerly it returned only user
.
PASSWORD
for another user.
FLUSH STATUS
that resets most status variables to zero.
aborted_threads
, aborted_connects
.
connection_timeout
.
SET SQL_WARNINGS=1
to get a warning count also for simple
inserts.
SIGTERM
instead of SIGQUIT
with
shutdown to work better on FreeBSD.
\G
(print vertically) to mysql
.
SELECT HIGH_PRIORITY ...
killed mysqld
.
IS NULL
on a AUTO_INCREMENT
column in a LEFT JOIN
didn't
work as expected.
MAKE_SET()
.
mysql_install_db
no longer starts the MySQL server! You
should start mysqld
with safe_mysqld
after installing it! The
MySQL RPM will, however, start the server as before.
--bootstrap
option to mysqld
and recoded
mysql_install_db
to use it. This will make it easier to install
MySQL with RPMs.
+
, -
(sign and minus), *
, /
, %
,
ABS()
and MOD()
to be BIGINT
aware (64-bit safe).
ALTER TABLE
that caused mysqld
to crash.
INSERT
.)
INSERT INTO tbl_name SET col_name=value, col_name=value, ...
MYSQL_INIT_COMMAND
to mysql_options()
to make
a query on connect or reconnect.
MYSQL_READ_DEFAULT_FILE
and
MYSQL_READ_DEFAULT_GROUP
to mysql_options()
to read the
following parameters from the MySQL option files: port
,
socket
, compress
, password
, pipe
, timeout
,
user
, init-command
, host
and database
.
maybe_null
to the UDF structure.
IGNORE
to INSERT
statements with many rows.
koi8
character sets; users of
koi8
must run isamchk -rq
on each table that has an
index on a CHAR
or VARCHAR
column.
mysql_setpermission
, by Luuk de Boer. It allows easy
creation of new users with permissions for specific databases.
LOAD DATA INFILE
).
SHOW STATUS
and changed format of output to
be like SHOW VARIABLES
.
extended-status
command to mysqladmin
which will show the
new status variables.
SET SQL_LOG_UPDATE=0
caused a lockup of the server.
FLUSH [ TABLES | HOSTS | LOGS | PRIVILEGES ] [, ...]
KILL
thread_id
.
ALTER TABLE
from a INT
to a short CHAR()
column.
SELECT HIGH_PRIORITY
; this will get a lock for the
SELECT
even if there is a thread waiting for another
SELECT
to get a WRITE LOCK
.
wild_compare()
to string class to be able to use LIKE
on
BLOB
/TEXT
columns with \0
.
ESCAPE
option to LIKE
.
mysqladmin debug
.
mysqld
on Windows with the --flush
option.
This will flush all tables to disk after each update. This makes things
much safer on the Windows platforms but also much slower.
my_strcoll()
! The patch should always be safe to install (for any
system), but as this patch changes ISAM
internals it's not yet in the
default distribution.
DATE_ADD()
and DATE_SUB()
didn't work with group functions.
mysql
will now also try to reconnect on USE database
commands.
ORDER BY
and LEFT JOIN
and const
tables.
ORDER BY
if the first ORDER BY
column
was a key and the rest of the ORDER BY
columns wasn't part of the key.
OPTIMIZE TABLE
.
DROP TABLE
and mysqladmin shutdown
on Windows
(a fatal bug from 3.22.6).
TIME columns
and negative strings.
LIMIT
clause for the DELETE
statement.
/*! ... */
syntax to hide MySQL-specific
keywords when you write portable code. MySQL will parse the code
inside the comments as if the surrounding /*!
and */
comment
characters didn't exist.
OPTIMIZE TABLE tbl_name
can now be used to reclaim disk space
after many deletes. Currently, this uses ALTER TABLE
to
regenerate the table, but in the future it will use an integrated
isamchk
for more speed.
libtool
to get the configure more portable.
UPDATE
and DELETE
operations when using
DATETIME
or DATE
keys.
mysqladmin proc
to display information about your own
threads. Only users with the PROCESS
privilege can get
information about all threads.
(In 4.0.2 one needs the SUPER
privilege for this.)
YYMMDD
, YYYYMMDD
,
YYMMDDHHMMSS
for numbers when using DATETIME
and
TIMESTAMP
types. (Formerly these formats only worked with strings.)
CLIENT_IGNORE_SPACE
to allow use of spaces
after function names and before `(' (Powerbuilder requires this).
This will make all function names reserved words.
--log-long-format
option to mysqld
to enable timestamps
and INSERT_IDs in the update log.
--where
option to mysqldump
(patch by Jim Faucette).
mysqldump
.
LOAD DATA INFILE
statement, you can now use the new LOCAL
keyword to read the file from the client. mysqlimport
will
automatically use LOCAL
when importing with the TCP/IP protocol.
DROP TABLE
, ALTER TABLE
, DELETE FROM
TABLE
and mysqladmin flush-tables
under heavy usage.
Changed locking code to get better handling of locks of different types.
DBI
to 1.00 and DBD
to 1.2.0.
mysqld
. (To avoid errors if you accidentally
try to use an old error message file.)
affected_rows()
,
insert_id()
, ...) are now of type BIGINT
to allow 64-bit values
to be used.
This required a minor change in the MySQL protocol which should affect
only old clients when using tables with AUTO_INCREMENT
values > 16M.
mysql_fetch_lengths()
has changed from uint *
to ulong *
. This may give a warning for old clients but should work
on most machines.
mysys
and dbug
libraries to allocate all thread variables
in one struct. This makes it easier to make a threaded `libmysql.dll'
library.
gethostname()
(instead of uname()
) when
constructing `.pid' file names.
COUNT()
, STD()
and AVG()
are extended to handle more than
4G rows.
-838:59:59
<= x <=
838:59:59
in a TIME
column.
TIME
column to too short a value, MySQL now
assumes the value is given as: [[[D ]HH:]MM:]SS
instead of
HH[:MM[:SS]]
.
TIME_TO_SEC()
and SEC_TO_TIME()
can now handle negative times
and hours up to 32767.
SET SQL_LOG_UPDATE={0|1}
to allow users with
the PROCESS
privilege to bypass the update log.
(Modified patch from Sergey A Mukhin violet@rosnet.net.)
LPAD()
.
BLOB
reading from
pipes safer.
-O max_connect_errors=#
option to mysqld
.
Connect errors are now reset for each correct connection.
max_allowed_packet
to 1M
in
mysqld
.
--low-priority-updates
option to mysqld
, to give
table-modifying operations (INSERT
, REPLACE
, UPDATE
,
DELETE
) lower priority than retrievals. You can now use
{INSERT | REPLACE | UPDATE | DELETE} LOW_PRIORITY ...
You can
also use SET SQL_LOW_PRIORITY_UPDATES={0|1}
to change
the priority for one thread. One side effect is that LOW_PRIORITY
is now a reserved word. :(
INSERT INTO table ... VALUES(...),(...),(...)
,
to allow inserting multiple rows with a single statement.
INSERT INTO tbl_name
is now also cached when used with LOCK TABLES
.
(Previously only INSERT ... SELECT
and LOAD DATA INFILE
were
cached.)
GROUP BY
functions with HAVING
:
mysql> SELECT col FROM table GROUP BY col HAVING COUNT(*)>0;
mysqld
will now ignore trailing `;' characters in queries. This
is to make it easier to migrate from some other SQL servers that require the
trailing `;'.
SELECT INTO OUTFILE
.
GREATEST()
and LEAST()
functions. You must now use
these instead of the MAX()
and MIN()
functions to get the
largest/smallest value from a list of values. These can now handle REAL
,
BIGINT
and string (CHAR
or VARCHAR
) values.
DAYOFWEEK()
had offset 0 for Sunday. Changed the offset to 1.
GROUP BY
columns and fields when
there is no GROUP BY
specification.
--vertical
option to mysql
, for printing results in
vertical mode.
--tmpdir
option to mysqld
, for specifying the location
of the temporary file directory.
SELECT ... FROM table WHERE auto_increment_column IS NULLto:
SELECT ... FROM table WHERE auto_increment_column == LAST_INSERT_ID()This allows some ODBC programs (Delphi, Access) to retrieve the newly inserted row to fetch the
AUTO_INCREMENT
id.
DROP TABLE
now waits for all users to free a table before deleting it.
BIN()
, OCT()
, HEX()
and CONV()
for
converting between different number bases.
SUBSTRING()
with 2 arguments.
ORDER BY
and
GROUP BY
.
mysqld
now automatically disables system locking on Linux and Windows,
and for systems that use MIT-pthreads. You can force the use of locking
with the --enable-external-locking
option.
--console
option to mysqld
, to force a console window
(for error messages) when using Windows.
DATE_ADD()
and DATE_SUB()
functions.
mysql_ping()
to the client library.
--compress
option to all MySQL clients.
byte
to char
in `mysql.h' and `mysql_com.h'.
<<
, >>
, RPAD()
and LPAD()
.
ORDER BY
to work when no records are found
when using fields that are not in GROUP BY
(MySQL extension).
--chroot
option to mysqld
, to start mysqld
in
a chroot environment (by Nikki Chumakov nikkic@cityline.ru).
--one-thread
option to mysqld
, for debugging with
LinuxThreads (or glibc
). (This replaces the -T32
flag)
DROP TABLE IF EXISTS
to prevent an error from occurring if the
table doesn't exist.
IF
and EXISTS
are now reserved words (they would have to
be sooner or later).
mysqldump
.
mysql_ping()
.
mysql_init()
and mysql_options()
.
You now MUST call mysql_init()
before you call
mysql_real_connect()
.
You don't have to call mysql_init()
if you only use
mysql_connect()
.
mysql_options(...,MYSQL_OPT_CONNECT_TIMEOUT,...)
so you can set a
timeout for connecting to a server.
--timeout
option to mysqladmin
, as a test of
mysql_options()
.
AFTER column
and FIRST
options to
ALTER TABLE ... ADD columns
.
This makes it possible to add a new column at some specific location
within a row in an existing table.
WEEK()
now takes an optional argument to allow handling of weeks when
the week starts on Monday (some European countries). By default,
WEEK()
assumes the week starts on Sunday.
TIME
columns weren't stored properly (bug in MySQL Version 3.22.0).
UPDATE
now returns information about how many rows were
matched and updated, and how many ``warnings'' occurred when doing the update.
FORMAT(-100,2)
.
ENUM
and SET
columns were compared in binary (case-sensitive)
fashion; changed to be case-insensitive.
mysql_real_connect()
call is changed to:
mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, uint port, const char *unix_socket, uint client_flag)
accept()
thread. This fixes permanently the telnet bug
that was a topic on the mail list some time ago.
mysqld
now has a local hostname
resolver cache so connections should actually be faster than before,
even with this feature.
tbl_name@db_name
or db_name.tbl_name
. This makes it possible to
give a user read access to some tables and write access to others simply by
keeping them in different databases!
--user
option to mysqld
, to allow it to run
as another Unix user (if it is started as the Unix root
user).
mysqladmin password 'new_password'
. This uses encrypted passwords
that are not logged in the normal MySQL log!
SELECT
code to handle some very specific queries
involving group functions (like COUNT(*)
) without a GROUP BY
but
with HAVING
. The following now works:
mysql> SELECT COUNT(*) as C FROM table HAVING C > 1;
malloc()
.
-T32
option to mysqld
, for running all queries under the
main thread. This makes it possible to debug mysqld
under Linux with
gdb
!
not_null_column IS NULL
(needed for some Access
queries).
STRAIGHT_JOIN
to be used between two tables to force the optimiser
to join them in a specific order.
VARCHAR
rather than CHAR
and
the column type is now VARCHAR
for fields saved as VARCHAR
.
This should make the MyODBC
driver better, but may break some old
MySQL clients that don't handle FIELD_TYPE_VARCHAR
the same
way as FIELD_TYPE_CHAR
.
CREATE INDEX
and DROP INDEX
are now implemented through
ALTER TABLE
.
CREATE TABLE
is still the recommended (fast) way to create indexes.
--set-variable
option wait_timeout
to mysqld
.
mysqladmin processlist
to show how long a query
has taken or how long a thread has slept.
show variables
and some new to
show status
.
YEAR
. YEAR
is stored in 1 byte with allowable
values of 0, and 1901 to 2155.
DATE
type that is stored in 3 bytes rather than 4 bytes.
All new tables are created with the new date type if you don't use the
--old-protocol
option to mysqld
.
Error from table handler: #
on some operating systems.
--enable-assembler
option to configure
, for x86 machines
(tested on Linux + gcc
). This will enable assembler functions for the
most important string functions for more speed!
Version 3.21 is quite old now, and should be avoided if possible. This information is kept here for historical purposes only.
SIGHUP
to mysqld
;
mysqld
core dumped when starting from boot on some systems.
DELETE FROM tbl_name
without a WHERE
condition is now done the
long way when you use LOCK TABLES
or if the table is in use, to
avoid race conditions.
INSERT INTO TABLE (timestamp_column) VALUES (NULL);
didn't set timestamp.
mysqladmin
refresh
often. This could in some very rare cases corrupt the header of the
index file and cause error 126 or 138.
refresh()
when running with the
--skip-external-locking
option. There was a ``very small'' time gap after
a mysqladmin refresh
when a table could be corrupted if one
thread updated a table while another thread did mysqladmin
refresh
and another thread started a new update ont the same table
before the first thread had finished. A refresh (or
--flush-tables
) will now not return until all used tables are
closed!
SELECT DISTINCT
with a WHERE
clause that didn't match any rows
returned a row in some contexts (bug only in 3.21.31).
GROUP BY
+ ORDER BY
returned one empty row when no rows where
found.
Use_count: Wrong count for ...
in the error log file.
TINYINT
type on Irix.
LEFT("constant_string",function)
.
FIND_IN_SET()
.
LEFT JOIN
core dumped if the second table is used with a constant
WHERE/ON
expression that uniquely identifies one record.
DATE_FORMAT()
and incorrect dates.
DATE_FORMAT()
now ignores '%'
to make it possible to extend
it more easily in the future.
mysql
now returns an exit code > 0 if the query returned an error.
mysql
client.
By Tommy Larsen tommy@mix.hive.no.
safe_mysqld
to redirect startup messages to
'hostname'.err
instead
of 'hostname'.log
to reclaim file space on mysqladmin refresh
.
ENUM
always had the first entry as default value.
ALTER TABLE
wrote two entries to the update log.
sql_acc()
now closes the mysql
grant tables after a reload to
save table space and memory.
LOAD DATA
to use less memory with tables and BLOB
columns.
SELECT
problem with LEFT()
when using the czech
character set.
isamchk
; it couldn't repair a packed table in a very
unusual case.
SELECT
statements with &
or |
(bit functions) failed on
columns with NULL
values.
LOCK TABLES
+ DELETE from tbl_name
never removed locks properly.
OR
function.
umask()
and creating new databases.
SELECT ... INTO OUTFILE ...
MIN(integer)
or MAX(integer)
in
GROUP BY
.
WEEK("XXXX-xx-01")
.
Error from table handler: #
on some operating systems.
GET_LOCK(string,timeout)
,
RELEASE_LOCK(string)
.
Opened_tables
to show status
.
mysqld
through telnet + TCP/IP.
WHERE key_part_1 >= something AND key_part_2 <= something_else
.
configure
for detection of FreeBSD 3.0 9803xx and above
WHERE
with string_col_key = constant_string
didn't always
find all rows if the column had many values differing only with
characters of the same sort value (like e and e with an accent).
umask()
to make log files non-readable for normal users.
--old-protocol
option to mysqld
.
SELECT
which matched all key fields returned the values in the
case of the matched values, not of the found values. (Minor problem.)
FROM_DAYS(0)
now returns "0000-00-00".
DATE_FORMAT()
, PM and AM were swapped for hours 00 and 12.
BLOB
/TEXT
in GROUP BY
with many
tables.
ENUM
field that is not declared NOT NULL
has NULL
as
the default value.
(Previously, the default value was the first enumeration value.)
INDEX (Organisation,Surname(35),Initials(35))
.
SELECT ... FROM many_tables
much faster.
accept()
to possibly fix some problems on some
Linux machines.
typedef 'string'
to typedef 'my_string'
for better
portability.
isamchk
. Try isamchk --help
.
filesort()
didn't work.
Affects DISTINCT
, ORDER BY
and GROUP BY
on 64-bit
processors.
SELECT
on the
table.
OR
operators on key parts
inside each other.
MIN()
and MAX()
to work properly with strings and
HAVING
.
0664
to 0660
.
LEFT JOIN
and constant expressions in the ON
part.
configure
now works better on OSF/1 (tested on 4.0D).
LIKE
optimisation with international character
support.
DBI
to 0.93.
TIME
, DATE
, TIMESTAMP
, TEXT
, BIT
,
ENUM
, NO
, ACTION
, CHECK
, YEAR
,
MONTH
, DAY
, HOUR
, MINUTE
, SECOND
,
STATUS
, VARIABLES
.
TIMESTAMP
to NULL
in LOAD DATA INFILE ...
didn't
set the current time for the TIMESTAMP
.
BETWEEN
to recognise binary strings. Now BETWEEN
is
case-sensitive.
--skip-thread-priority
option to mysqld
, for systems
where mysqld
's thread scheduling doesn't work properly (BSDI 3.1).
DAYNAME()
and MONTHNAME()
.
TIME_FORMAT()
. This works like DATE_FORMAT()
,
but takes a time string ('HH:MM:SS'
) as argument.
OR
operators of key parts
inside AND
expressions.
variables
command to mysqladmin
.
ALTER TABLE
to work with Windows (Windows can't rename
open files). Also fixed a couple of small bugs in the Windows version.
crash-me
and the benchmarks on
the following platforms: SunOS 5.6 sun4u, SunOS 5.5.1 sun4u, SunOS 4.14 sun4c,
SunOS 5.6 i86pc, Irix 6.3 mips5k, HP-UX 10.20 hppa, AIX 4.2.1 ppc,
OSF/1 V4.0 alpha, FreeBSD 2.2.2 i86pc and BSDI 3.1 i386.
COUNT(*)
problems when the WHERE
clause didn't match any
records. (Bug from 3.21.17.)
NULL = NULL
is true. Now you must use IS NULL
or IS NOT NULL
to test whether a value is NULL
.
(This is according to SQL-99 but may break
old applications that are ported from mSQL
.)
You can get the old behaviour by compiling with -DmSQL_COMPLIANT
.
LEFT OUTER JOIN
clauses.
ORDER BY
on string formula with possible NULL
values.
<=
on sub index.
DAYOFYEAR()
, DAYOFMONTH()
, MONTH()
,
YEAR()
, WEEK()
, QUARTER()
, HOUR()
, MINUTE()
,
SECOND()
and FIND_IN_SET()
.
SHOW VARIABLES
command.
mysql> SELECT 'first ' 'second'; -> 'first second'
mysqlaccess
to 2.02.
LIKE
.
WHERE data_field = date_field2 AND date_field2 = constant
.
SHOW STATUS
command.
mysqladmin stat
to return the right number of queries.
AUTO_INCREMENT
attribute or is a TIMESTAMP
. This is needed for
the new Java driver.
configure
bugs and increased maximum table size
from 2G to 4G.
DBD
to 1.1823. This version implements mysql_use_result
in DBD-Mysql
.
REVERSE()
(by Zeev Suraski).
DBI
to 0.91.
LEFT OUTER JOIN
.
CROSS JOIN
syntax. CROSS
is now a reserved word.
yacc
/bison
stack allocation to be even safer and to allow
MySQL to handle even bigger expressions.
ORDER BY
was slow when used with key ranges.
--with-unix-socket-path
to avoid
confusion.
LEFT OUTER JOIN
.
LEFT
, NATURAL
,
USING
.
MYSQL_HOST
as the default host if it's defined.
SELECT col_name, SUM(expr)
now returns NULL
for col_name
when there are matching rows.
BLOB
values with ASCII
characters over 127.
mysqld
restart if one thread was reading data that another thread modified.
LIMIT offset,count
didn't work in INSERT ... SELECT
.
POWER()
, SPACE()
,
COT()
, DEGREES()
, RADIANS()
, ROUND(2 arg)
and TRUNCATE()
.
LOCATE()
parameters were
swapped according to ODBC standard. Fixed.
TIME_TO_SEC()
.
NOT NULL
fields.
UPDATE SET ...
statements.
BLOB
and TEXT
, to
be compatible with mysqldump
.
mysqlperl
is now from
Msql-Mysql-modules. This means that connect()
now takes
host
, database
, user
, password
arguments! The old
version took host
, database
, password
, user
.
DATE '1997-01-01'
, TIME '12:10:10'
and
TIMESTAMP '1997-01-01 12:10:10'
formats required by SQL-99.
Warning: Incompatible change! This has the unfortunate
side-effect that you no longer can have columns named DATE
, TIME
or TIMESTAMP
. :( Old columns can still be accessed through
tablename.columnname
!)
make
programs trying to rebuild it.
readline
library upgraded to version 2.1.
DBI
/DBD
is now included in the distribution. DBI
is now the recommended way to connect to MySQL from Perl.
DBD
, with test results from
mSQL
2.0.3, MySQL, PostgreSQL 6.2.1 and Solid server 2.2.
crash-me
is now included with the benchmarks; this is a Perl program
designed to find as many limits as possible in an SQL server. Tested with
mSQL
, PostgreSQL, Solid and MySQL.
mysql
command-line tool, by Zeev
Suraski and Andi Gutmans.
REPLACE
that works like INSERT
but
replaces conflicting records with the new record. REPLACE INTO
TABLE ... SELECT ...
works also.
CREATE DATABASE db_name
and DROP
DATABASE db_name
.
RENAME
option to ALTER TABLE
: ALTER TABLE name
RENAME TO new_name
.
make_binary_distribution
now includes `libgcc.a' in
`libmysqlclient.a'. This should make linking work for people who don't
have gcc
.
net_write()
to my_net_write()
because of a name
conflict with Sybase.
DAYOFWEEK()
compatible with ODBC.
bison
memory overrun checking to make MySQL
safer with weird queries.
configure
problems on some platforms.
DATE_FORMAT()
.
NOT IN
.
{fn now() }
DATE
and TIME
values with NULL
.
FLOAT
. Previously, the
values were converted to INT
values before sorting.
key_column=constant
.
DOUBLE
values sorted on integer results instead.
mysql
no longer requires a database argument.
HAVING
should be. According to the SQL standards, it should
be after GROUP BY
but before ORDER BY
. MySQL Version 3.20
incorrectly had it last.
USE database
to start using another database.
mysqld
doesn't crash even if you haven't done a
ulimit -n 256
before starting mysqld
.
errno
.
This makes Linux systems much safer!
SELECT
.
LIKE
on number key.
--table
option to mysql
to print in table format.
Moved time and row information after query result.
Added automatic reconnect of lost connections.
!=
as a synonym for <>
.
VERSION()
to make easier logs.
ftruncate()
call in MIT-pthreads. This made isamchk
destroy the `.ISM' files on (Free)BSD 2.x systems.
__P_
patch in MIT-pthreads.
NULL
if the returned string should be longer than max_allowed_packet
bytes.
INTERVAL
type to ENUM
, because
INTERVAL
is used in SQL-99.
JOIN
+ GROUP
+ INTO OUTFILE
,
the result wasn't grouped.
LIKE
with '_'
as last character didn't work. Fixed.
TRIM()
function.
CURTIME()
.
ENCRYPT()
function by Zeev Suraski.
FOREIGN KEY
syntax skipping. New reserved words:
MATCH
, FULL
, PARTIAL
.
mysqld
now allows IP number and hostname for the --bind-address
option.
SET CHARACTER SET cp1251_koi8
to enable conversions of
data to and from the cp1251_koi8
character set.
CREATE COLUMN
syntax of NOT NULL
columns to be after
the DEFAULT
value, as specified in the SQL-99 standard. This will
make mysqldump
with NOT NULL
and default values incompatible with
MySQL Version 3.20.
ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT
NULL
.
CHAR
and BIT
as synonyms for CHAR(1)
.
SELECT
privilege.
INSERT ... SELECT ... GROUP BY
didn't work in some cases. An
Invalid use of group function
error occurred.
LIMIT
, SELECT
now always uses keys instead of record
scan. This will give better performance on SELECT
and a WHERE
that matches many rows.
BIT_OR()
and BIT_AND()
.
CHECK
and REFERENCES
.
CHECK
is now a reserved word.
ALL
option to GRANT
for better compatibility. (GRANT
is still a dummy function.)
ORDER BY
and GROUP BY
with NULL
columns.
LAST_INSERT_ID()
SQL function to retrieve last
AUTO_INCREMENT
value. This is intended for clients to ODBC that can't use the
mysql_insert_id()
API function, but can be used by any client.
--flush-logs
option to mysqladmin
.
STATUS
to mysql
.
ORDER BY
/GROUP BY
because of bug in gcc
.
INSERT ... SELECT ... GROUP BY
.
mysqlaccess
.
CREATE
now supports all ODBC types and the mSQL
TEXT
type.
All ODBC 2.5 functions are also supported (added REPEAT
). This provides
better portability.
TINYTEXT
, TEXT
, MEDIUMTEXT
and
LONGTEXT
. These are actually BLOB
types, but all searching is
done in case-insensitive fashion.
BLOB
fields are now TEXT
fields. This only
changes that all searching on strings is done in case-sensitive fashion.
You must do an ALTER TABLE
and change the datatype to BLOB
if you want to have tests done in case-sensitive fashion.
configure
issues.
test-select
works.
--enable-unix-socket=pathname
option to configure
.
SUM()
functions.
For example, you can now use SUM(column)/COUNT(column)
.
PI()
, ACOS()
, ASIN()
, ATAN()
, COS()
,
SIN()
and TAN()
.
net_print()
in `procedure.cc'.
SELECT ... INTO OUTFILE
syntax.
GROUP BY
and SELECT
on key with many values.
mysql_fetch_lengths()
sometimes returned incorrect lengths when you used
mysql_use_result()
. This affected at least some cases of
mysqldump --quick
.
WHERE const op field
.
NULL
fields.
--pid-file=#
option to mysqld
.
FROM_UNIXTIME()
, originally by Zeev Suraski.
BETWEEN
in range optimiser (did only test = of the first
argument).
mysql_errno()
, to get the error number of
the error message. This makes error checking in the client much easier.
This makes the new server incompatible with the 3.20.x server when running
without --old-protocol
. The client code is backward-compatible.
More information can be found in the `README' file!
sigwait
and sigset
defines).
configure
should now be able to detect the last argument to
accept()
.
-O tmp_table_size=#
option to mysqld
.
FROM_UNIXTIME(timestamp)
which returns a date string in
'YYYY-MM-DD HH:MM:SS'
format.
SEC_TO_TIME(seconds)
which returns a string in
'HH:MM:SS'
format.
SUBSTRING_INDEX()
, originally by Zeev Suraski.
mysqld
doesn't work on it yet.
pthread_create
to work.
mysqld
doesn't accept hostnames that start with digits followed by a
'.'
, because the hostname may look like an IP number.
--skip-networking
option to mysqld
, to allow only socket
connections. (This will not work with MIT-pthreads!)
free()
that killed the server on
CREATE DATABASE
or DROP DATABASE
.
mysqld
-O
options to better names.
-O join_cache_size=#
option to mysqld
.
-O max_join_size=#
option to mysqld
, to be able to set a
limit how big queries (in this case big = slow) one should be able to handle
without specifying SET SQL_BIG_SELECTS=1
. A # = is about 10
examined records. The default is ``unlimited''.
TIME
, DATE
, DATETIME
or TIMESTAMP
column to a constant, the constant is converted to a time value before
performing the comparison.
This will make it easier to get ODBC (particularly Access97) to work with
the above types. It should also make dates easier to use and the comparisons
should be quicker than before.
query()
in
mysqlperl
to take a query with \0
in it.
YYMMDD
) didn't work.
UPDATE
clause.
SELECT * INTO OUTFILE
, which didn't correctly if the outfile already
existed.
mysql
now shows the thread ID when starting or doing a reconnect.
--new
, but it crashes core a lot yet...
isam
library should be relatively 64-bit clean.
isamchk
which can detect and fix more problems.
isamlog
.
mysqladmin
: you can now do mysqladmin kill 5,6,7,8
to kill
multiple threads.
-O backlog=#
option to mysqld
.
ALTER TABLE
now returns warnings from field conversions.
ASCII()
.
BETWEEN(a,b,c)
. Use the standard SQL
syntax instead: expr BETWEEN expr AND expr
.
SUM()
functions.
tbl_name.field_name
in UPDATE
.
SELECT DISTINCT
when using 'hidden group'. For example:
mysql> SELECT DISTINCT MOD(some_field,10) FROM test -> GROUP BY some_field;Note:
some_field
is normally in the SELECT
part. Standard SQL should
require it.
INTERVAL
, EXPLAIN
, READ
,
WRITE
, BINARY
.
CHAR(num,...)
.
IN
. This uses a binary search to find a match.
LOCK TABLES tbl_name [AS alias] {READ|WRITE} ...
--log-update
option to mysqld
, to get a log suitable for
incremental updates.
EXPLAIN SELECT ...
to get information about how the
optimiser will do the join.
FIELD_TYPE_TINY_BLOB
, FIELD_TYPE_MEDIUM_BLOB
,
FIELD_TYPE_LONG_BLOB
or FIELD_TYPE_VAR_STRING
(as
previously returned by mysql_list_fields
). You should instead only use
FIELD_TYPE_BLOB
or FIELD_TYPE_STRING
. If you want exact
types, you should use the command SHOW FIELDS
.
0x######
which can be used as a string
(default) or a number.
FIELD_TYPE_CHAR
is renamed to FIELD_TYPE_TINY
.
DEFAULT
values no longer need to be NOT NULL
.
ENUM
SET
double
or long long
.
This will provide the full 64-bit range with bit functions and fix some
conversions that previously could result in precision losses. One should
avoid using unsigned long long
columns with full 64-bit range
(numbers bigger than 9223372036854775807) because calculations are done
with signed long long
.
ORDER BY
will now put NULL
field values first. GROUP BY
will also work with NULL
values.
WHERE
with expressions.
mysql> SELECT * FROM tbl_name -> WHERE key_part_1="customer" -> AND key_part_2>=10 AND key_part_2<=10;
Version 3.20 is quite old now, and should be avoided if possible. This information is kept here for historical purposes only.
Changes from 3.20.18 to 3.20.32b are not documented here because the 3.21 release branched here. And the relevant changes are also documented as changes to the 3.21 version.
-p#
(remove #
directories from path) to isamlog
.
All files are written with a relative path from the database directory
Now mysqld
shouldn't crash on shutdown when using the
--log-isam
option.
mysqlperl
version. It is now compatible with msqlperl-0.63
.
DBD
module available.
STD()
(standard deviation).
mysqld
server is now compiled by default without debugging
information. This will make the daemon smaller and faster.
--basedir
option to
mysqld
. All other paths are relative in a normal installation.
BLOB
columns sometimes contained garbage when used with a SELECT
on more than one table and ORDER BY
.
GROUP BY
work as expected
(SQL-99 extension).
Example:
mysql> SELECT id,id+1 FROM table GROUP BY id;
MYSQL_PWD
was reversed. Now MYSQL_PWD
is
enabled as default in the default release.
mysqld
to core dump with
Arithmetic error on SPARC-386.
--unbuffered
option to mysql
, for new mysqlaccess
.
BLOB
columns and the functions IS NULL
and
IS NOT NULL
in the WHERE
clause.
max_allowed_packet
is now 64K for
the server and 512K for the client. This is mainly used to catch
incorrect packets that could trash all memory. The server limit may be
changed when it is started.
safe_mysqld
to check for running daemon.
ELT()
function is renamed to FIELD()
. The new
ELT()
function returns a value based on an index: FIELD()
is the inverse of ELT()
Example: ELT(2,"A","B","C")
returns
"B"
. FIELD("B","A","B","C")
returns 2
.
COUNT(field)
, where field
could have a NULL
value, now
works.
SELECT ... GROUP BY
.
WHERE
with many unoptimisable brace levels.
get_hostname
, only the IP is checked.
Previously, you got Access denied
.
INSERT INTO ... SELECT ... WHERE
could give the error
Duplicated field
.
safe_mysqld
to make it ``safer''.
LIKE
was case-sensitive in some places and case-insensitive in others.
Now LIKE
is always case-insensitive.
'#'
anywhere on the line.
SET SQL_SELECT_LIMIT=#
. See the FAQ for more details.
mysqlaccess
script.
FROM_DAYS()
and WEEKDAY()
to also take a full
TIMESTAMP
or DATETIME
as argument. Before they only took a
number of type YYYYMMDD
or YYMMDD
.
UNIX_TIMESTAMP(timestamp_column)
.
mysqld
to work around a bug in MIT-pthreads. This makes multiple
small SELECT
operations 20 times faster. Now lock_test.pl
should
work.
mysql_FetchHash(handle)
to mysqlperl
.
mysqlbug
script is now distributed built to allow for reporting
bugs that appear during the build with it.
getpwuid()
instead of
cuserid()
.
SELECT
optimiser when using many tables with the same
column used as key to different tables.
latin2
and Russian KOI8
character tables.
GRANT
command to satisfy Powerbuilder.
packets out of order
when using MIT-pthreads.
fcntl()
fails. Thanks to Mike Bretz for finding this bug.
termbits
from `mysql.cc'. This conflicted with
glibc
2.0.
SELECT
as superuser without a database.
SELECT
with group calculation to outfile.
-p
or --password
option to mysql
without
an argument, the user is solicited for the password from the tty.
MYSQL_PWD
(by Elmar Haneke).
kill
to mysqladmin
to kill a specific
MySQL thread.
AUTO_INCREMENT
key with ALTER_TABLE
.
AVG()
gave too small value on some SELECT
statements with
GROUP BY
and ORDER BY
.
DATETIME
type (by Giovanni Maruzzelli
maruzz@matrice.it).
DONT_USE_DEFAULT_FIELDS
works.
CREATE INDEX
.
DATE
, TIME
and
TIMESTAMP
.
OR
of multiple tables (gave empty set).
DATE
and TIME
types.
SELECT
with AND
-OR
levels.
LIMIT
and ORDER BY
.
ORDER BY
and GROUP BY
on items that aren't in the
SELECT
list.
(Thanks to Wim Bonis bonis@kiss.de, for pointing this out.)
INSERT
.
SELECT ... WHERE ... = NULL
.
glibc
2.0. To get glibc
to work, you should
add the `gibc-2.0-sigwait-patch' before compiling glibc
.
ALTER TABLE
when changing a NOT NULL
field to
allow NULL
values.
CREATE TABLE
.
CREATE TABLE
now allows FLOAT(4)
and FLOAT(8)
to mean
FLOAT
and DOUBLE
.
mysqlaccess
by Yves.Carlier@rug.ac.be.
This program shows the access rights for a specific user and the grant
rows that determine this grant.
WHERE const op field
(by bonis@kiss.de).
SELECT ... INTO OUTFILE
, all temporary tables are ISAM
instead of HEAP to allow big dumps.
ALTER TABLE
for SQL-92 compliance.
--port
and --socket
options to all utility programs and
mysqld
.
readdir_r()
. Now mysqladmin create database
and mysqladmin drop database
should work.
tempnam()
. This should fix the ``sort
aborted'' bug.
sql_update
. This fixed slow updates
on first connection. (Thanks to Vaclav Bittner for the test.)
INSERT INTO ... SELECT ...
MEDIUMBLOB
fixed.
ALTER TABLE
and BLOB
values.
SELECT ... INTO OUTFILE
now creates the file in the current
database directory.
DROP TABLE
now can take a list of tables.
DESCRIBE
(DESC
).
make_binary_distribution
.
configure
's
C++ link test.
--without-perl
option to configure
.
ALTER TABLE
didn't copy null bit. As a result, fields that were allowed
to have NULL
values were always NULL
.
CREATE
didn't take numbers as DEFAULT
.
ALTER TABLE
and multi-part keys.
ALTER TABLE
, SELECT ... INTO OUTFILE
and
LOAD DATA INFILE
.
NOW()
.
File_priv
to mysql/user
table.
add_file_priv
which adds the new field File_priv
to the user
table. This script must be executed if you want to
use the new SELECT ... INTO
and LOAD DATA INFILE ...
commands
with a version of MySQL earlier than 3.20.7.
lock_test.pl
test fail.
status
command to mysqladmin
for short logging.
-k
option to mysqlshow
, to get key information for a table.
mysqldump
.
configure
cannot find a -lpthreads
library.
program --help
.
RAND([init])
.
sql_lex
to handle \0
unquoted, but the client can't send
the query through the C API, because it takes a str pointer.
You must use mysql_real_query()
to send the query.
mysql_get_client_info()
.
mysqld
now uses the N_MAX_KEY_LENGTH
from `nisam.h' as
the maximum allowable key length.
mysql> SELECT filter_nr,filter_nr FROM filter ORDER BY filter_nr;Previously, this resulted in the error:
Column: 'filter_nr' in order clause is ambiguous
.
mysql
now outputs '\0'
, '\t'
, '\n'
and '\\'
when encountering ASCII 0, tab, newline or '\'
while writing
tab-separated output.
This is to allow printing of binary data in a portable format.
To get the old behaviour, use -r
(or --raw
).
mysql_fetch_lengths(MYSQL_RES *)
, which
returns an array of column lengths (of type uint
).
IS NULL
in WHERE
clause.
SELECT
option STRAIGHT_JOIN
to tell the optimiser that
it should join tables in the given order.
'--'
in `mysql.cc'
(Postgres syntax).
SELECT
expressions and table columns in a SELECT
which are not used in the group part. This makes it efficient to implement
lookups. The column that is used should be a constant for each group because
the value is calculated only once for the first row that is found for a group.
mysql> SELECT id,lookup.text,SUM(*) FROM test,lookup -> WHERE test.id=lookup.id GROUP BY id;
SUM(function)
(could cause a core dump).
AUTO_INCREMENT
placement in the SQL query:
INSERT INTO table (auto_field) VALUES (0);inserted 0, but it should insert an
AUTO_INCREMENT
value.
mysql
now allows doubled ''
or ""
within strings for
embedded '
or "
.
EXP()
, LOG()
, SQRT()
, ROUND()
, CEILING()
.
configure
source now compiles a thread-free client library
-lmysqlclient
. This is the only library that needs to be linked
with client applications. When using the binary releases, you must
link with -lmysql -lmysys -ldbug -lmystrings
as before.
readline
library from bash-2.0
.
configure
and makefiles (and related source).
VPATH
. Tested with GNU Make 3.75.
safe_mysqld
and mysql.server
changed to be more compatible
between the source and the binary releases.
LIMIT
now takes one or two numeric arguments.
If one argument is given, it indicates the maximum number of rows in
a result. If two arguments are given, the first argument indicates the offset
of the first row to return, the second is the maximum number of rows.
With this it's easy to do a poor man's next page/previous page WWW
application.
FIELDS()
to ELT()
.
Changed SQL function INTERVALL()
to INTERVAL()
.
SHOW COLUMNS
a synonym for SHOW FIELDS
.
Added compatibility syntax FRIEND KEY
to CREATE TABLE
. In
MySQL, this creates a non-unique key on the given columns.
CREATE INDEX
and DROP INDEX
as compatibility functions.
In MySQL, CREATE INDEX
only checks if the index exists and
issues an error if it doesn't exist. DROP INDEX
always succeeds.
sql_acl
(core on new connection).
host
, user
and db
tables from database test
in the distribution.
FIELD_TYPE_CHAR
can now be signed (-128 to 127) or unsigned (0 to 255)
Previously, it was always unsigned.
CONCAT()
and WEEKDAY()
.
mysqld
to be compiled with SunPro
compiler.
'('
immediately after the function name
(no intervening space).
For example, 'USER('
is regarded as beginning a function call, and
'USER ('
is regarded as an identifier USER
followed by a
'('
, not as a function call.
configure
and Automake.
It will make porting much easier. The readline
library is included
in the distribution.
DBD
will follow when the new DBD
code
is ported.
mysqld
can now be started with Swedish
or English (default) error messages.
INSERT()
, RTRIM()
, LTRIM()
and
FORMAT()
.
mysqldump
now works correctly for all field types (even
AUTO_INCREMENT
). The format for SHOW FIELDS FROM tbl_name
is changed so the Type
column contains information suitable for
CREATE TABLE
. In previous releases, some CREATE TABLE
information had to be patched when re-creating tables.
BLOB
and TIMESTAMP
) are corrected.
TIMESTAMP
now returns different date information depending on its
create length.
'_'
.
Version 3.19 is quite old now, and should be avoided if possible. This information is kept here for historical purposes only.
DATABASE()
, USER()
, POW()
,
LOG10()
(needed for ODBC).
WHERE
with an ORDER BY
on fields from only one table,
the table is now preferred as first table in a multi-join.
HAVING
and IS NULL
or IS NOT NULL
now works.
SUM()
,
AVG()
...) didn't work together. Fixed.
mysqldump
: Didn't send password to server.
'Locked'
to process list as information if a query is
locked by another query.
IF(arg,syntax_error,syntax_error)
crashed.
CEILING()
, ROUND()
, EXP()
, LOG()
and SQRT()
.
BETWEEN
to handle strings.
SELECT
with grouping on BLOB
columns not to return
incorrect BLOB
info. Grouping, sorting and distinct on BLOB
columns will not yet work as
expected (probably it will group/sort by the first 7 characters in the
BLOB
). Grouping on formulas with a fixed string size (use MID()
on a BLOB
) should work.
BLOB
fields, the BLOB
was garbage on output.
DISTINCT
with calculated columns.
Go to the first, previous, next, last section, table of contents.