|
|
|
21 How MySQL compares to other databases21.1 How MySQL compares to
|
||||||||||||||||||||||||||||||||||||||||||||||
mSQL type | Corresponding MySQL type |
CHAR(len) | CHAR(len)
|
TEXT(len) | TEXT(len). len is the maximal length.
And LIKE works.
|
INT | INT. With many more options!
|
REAL | REAL. Or FLOAT. Both 4- and 8-byte versions are available.
|
UINT | INT UNSIGNED
|
DATE | DATE. Uses ANSI SQL format rather than mSQL's own.
|
TIME | TIME
|
MONEY | DECIMAL(12,2). A fixed-point value with two decimals.
|
Index creation
MySQL
CREATE TABLE
statement.
mSQL
CREATE INDEX statements.
To insert a unique identifier into a table
MySQL
AUTO_INCREMENT as a column type
specifier.
See section 20.4.29 mysql_insert_id().
mSQL
SEQUENCE on a table and select the _seq column.
To obtain a unique identifier for a row
MySQL
PRIMARY KEY or UNIQUE key to the table.
mSQL
_rowid column. Observe that _rowid may change over time
depending on many factors.
To get the time a column was last modified
MySQL
TIMESTAMP column to the table. This column is automatically set
to the current date and time for INSERT or UPDATE statements if
you don't give the column a value or if you give it a NULL value.
mSQL
_timestamp column.
NULL value comparisons
MySQL
NULL is always NULL.
mSQL
mSQL, NULL = NULL is TRUE. You
must change =NULL to IS NULL and <>NULL to
IS NOT NULL when porting old code from mSQL to MySQL.
String comparisons
MySQL
BINARY attribute, which causes comparisons to be done according to the
ASCII order used on the MySQL server host.
mSQL
Case-insensitive searching
MySQL
LIKE is a case-insensitive or case-sensitive operator, depending on
the columns involved. If possible, MySQL uses indexes if the
LIKE argument doesn't start with a wildcard character.
mSQL
CLIKE.
Handling of trailing spaces
MySQL
CHAR and VARCHAR
columns. Use a TEXT column if this behavior is not desired.
mSQL
WHERE clauses
MySQL
AND is evaluated
before OR). To get mSQL behavior in MySQL, use
parentheses (as shown below).
mSQL
mSQL query:
mysql> SELECT * FROM table WHERE a=1 AND b=2 OR a=3 AND b=4;To make MySQL evaluate this the way that
mSQL would,
you must add parentheses:
mysql> SELECT * FROM table WHERE (a=1 AND (b=2 OR (a=3 AND (b=4))));
Access control
MySQL
mSQL
PostgreSQL has some more advanced features like user-defined
types, triggers, rules and some transaction support. However, PostgreSQL lacks
many of the standard types and functions from ANSI SQL and ODBC. See the
crash-me web page
for a complete list of limits and which types and functions are supported
or unsupported.
Normally, PostgreSQL is a magnitude slower than
MySQL. See section 10.8 Using your own benchmarks. This is due largely to their
transactions system. If you really need transactions or the rich type
system PostgreSQL offers and you can afford the speed penalty, you
should take a look at PostgreSQL.
PHP and MySQLSend any additions to this list to webmaster@mysql.com.
Many users of MySQL have contributed very useful support tools and addons.
A list of what is available at http://www.mysql.com/Contrib
(or any mirror) is shown below.
If you want to build MySQL support for the Perl DBI/DBD
interface, you should fetch the Data-Dumper, DBI, and
Msql-Mysql-modules files and install them.
See section 4.10 Perl installation comments.
00-README This listing.
Data-Dumper module. Useful with DBI/DBD support.
DBI module.
DBD module to access mSQL and MySQL databases..
Data-ShowTable module. Useful with DBI/DBD support.
libmysql.dll, by Blestan Tabakov,
libmysql.dll, by bsilva@umesd.k12.or.us
guile that allows guile to interact with SQL
databases. By Hal Roberts.
user, db and
host tables. By Tim Sailer, modified by Atif Ghaffar
DBI 1.06.
BLOB/TEXT columns by Daniel Koch.
mod_auth_mysql. This is a little tool that allows you
to add/change user records storing group and/or password entries in
MySQL tables. By Harry Brueckner, brueckner@respublica.de.
mod_auth_mysql. This is a two-part system for use with
mod_auth_mysql.
pam, using MySQL.
mysqldump output to a C header file. By Harry Brueckner,
access_to_mysql.txt, except that this
one is fully configurable, has better type conversion (including
detection of TIMESTAMP fields), provides warnings and suggestions
while converting, quotes all special characters in text and
binary data, and so on. It will also convert to mSQL v1 and v2,
and is free of charge for anyone. See
http://www.cynergi.net/prod/exportsql/ for latest version. By
Pedro Freire, support@cynergi.net. Note: Doesn't work with
Access2!
exportsql. By Brian Andrews.
Note: Doesn't work with Access2!
exportsql.txt. That is,
it imports data from MySQL into an Access database via
ODBC. This is very handy when combined with exportSQL, since it lets you
use Access for all DB design and administration, and synchronize with
your actual MySQL server either way. Free of charge. See
http://www.netdive.com/freebies/importsql/ for any updates.
Created by Laurent Bossavit of NetDIVE.
Note: Doesn't work with Access2!
mSQL to MySQL. By alfred@sb.net
mysqldump and pipe it to
the sqlconv.pl script and the script will parse through the
mysqldump output and will rearrange the fields so they can be
inserted into a new table. An example is when you want to create a new
table for a different site you are working on, but the table is just a
bit different (ie - fields in different order, etc.).
By Steve Shreeve.
radiusd to make it support MySQL. By Wim Bonis,
hylafax outgoing faxes in a MySQL database. By Sinisa
Milivojevic, sinisa@coresinc.com.
Contributors to the MySQL distribution are listed below, in somewhat random order:
mysqld.
mysys library.
ISAM and MyISAM libraries (B-tree index file
handlers with index compression and different record formats).
heap library. A memory table system with our superior full dynamic
hashing. In use since 1981 and published around 1984.
replace program (look into it, it's COOL!).
mSQL tools like msqlperl, DBD/DBI and
DB2mysql.
texi2html. Also automatic website updating from
this manual.
libtool support.
mysys are left.
mysqlshutdown.exe and
mysqlwatch.exe
mSQL, but found that it couldn't satisfy our purposes so instead we
wrote a SQL interface to our application builder Unireg. mysqladmin
and mysql are programs that were largely influenced by their
mSQL counterparts. We have put a lot of effort into making the
MySQL syntax a superset of mSQL. Many of the APIs ideas are
borrowed from mSQL to make it easy to port free mSQL programs
to MySQL. MySQL doesn't contain any code from mSQL.
Two files in the distribution ('client/insert_test.c' and
'client/select_test.c') are based on the corresponding (non-copyrighted)
files in the mSQL distribution, but are modified as examples showing
the changes necessary to convert code from mSQL to MySQL.
(mSQL is copyrighted David J. Hughes.)
WHERE column REGEXP regexp.
gcc), the libc library
(from which we have borrowed 'strto.c' to get some code working in Linux)
and the readline library (for the mysql client).
mysqldump (previously msqldump, but ported and enhanced by
Monty).
DBD (Perl) interface.
_MB character set macros and the ujis and sjis character sets.
mysqlaccess, a program to show the access rights for a user.
xmysql, a graphical X client for MySQL.
DBD::mysql module.
FROM_UNIXTIME() time formatting, ENCRYPT() functions, and
bison adviser.
Active mailing list member.
DBI/DBD. Have
been of great help with crash-me and running benchmarks. Some new
date functions. The mysql_setpermissions script.
DBI/DBD section in the manual.
CREATE FUNCTION and
DROP FUNCTION.
AGGREGATE extension to UDF functions.
zlib) to the client/server protocol.
Perfect hashing for the lexical analyzer phase.
Other contributors, bugfinders and testers: James H. Thompson, Maurizio Menghini, Wojciech Tryc, Luca Berra, Zarko Mocnik, Wim Bonis, Elmar Haneke, jehamby@lightside, psmith@BayNetworks.COM, Mike Simons, Jaakko Hyv@"atti.
And lots of bug report/patches from the folks on the mailing list.
And a big tribute to those that help us answer questions on the
mysql@lists.mysql.com mailing list:
DBD-mysql questions.
xmysql-releated questions and basic installation questions.
mysqlbug.
DBD, Linux, some SQL syntax questions.
Note that we tend to update the manual at the same time we implement new things to MySQL. If you find a version listed below that you can't find on the MySQL download page, this means that the version has not yet been released!
The major difference between release 3.23 and releases 3.22 and 3.21 is that 3.23 contains a new ISAM library (MyISAM), which is more tuned for SQL than the old ISAM was.
The 3.23 release is under development, and things will be added at a fast pace to it. For the moment we recommend this version only for users that desperately need a new feature that is found only in this release (like big file support and machine-independent tables). (Note that all new functionality in MySQL 3.23 is extensively tested, but as this release involves much new code, it's difficult to test everything). This version should start to stabilize as soon as we get subselects included in it.
NISAM.
ISAM when doing some ORDER BY .. DESC queries.
--delay-key-write didn't enable delayed key writing.
TEXT column which only involved 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 4.11.5 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 to mysqld to force a
READ lock after a certain number of WRITE locks.
delayed_key_write on show variables.
concurrency 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.
REPLACE() and LOAD DATA INFILE.
mysqld variable interactive_timeout.
mysql_data_seek() from ulong to
ulonglong.
mysqld option -O lower_case_table_names={0|1} to allow
users to force table names to lower case.
SELECT ... INTO DUMPFILE.
--ansi to make some functions ANSI SQL
compatible.
#sql.
' (" in --ansi mode).
[floor() overflow safe on FREEBSD.
--quote-names 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
MyOBC.
FLOAT and DOUBLE (without any length modifiers) are
not anymore 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 | static | 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().
ALTER TABLE + adding a column after the last field didn't work.
CREATE TABLE foo (a int not null auto_increment, b char(5), primary key (b,a))
NULL.
AS on fieldname 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 in uses this in ODBC:
WHERE auto_increment_column IS NULL. This seams to fix some problems
with Access.
SET SQL_AUTO_IS_NULL=0|1 now turns off/on the handling of
searching after the last inserted row with WHERE
auto_increment_column IS NULL.
mysqld variable concurrency for Solaris.
--relative 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 will not anymore
try to store 'days' in it.
SUM().)
LIKE "%" on a index that may have NULL values.
REVOKE ALL PRIVILEGES didn't revoke all privileges.
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 not binary strings.
ASC is now the default again for ORDER BY.
LIMIT to UPDATE.
mysql_change_user().
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 ANSI SQL. (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 wrong.
SET SQL_LOW_PRIORITY_UPDATES=1 gave parse error before.
WHERE clause.
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100
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 optimizer bug (when using BLOBs) on Linux Alpha.
LOCK TABLES combined with DELETE FROM table.
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 = column_name even if
the columns are not identically packed.
WHERE column_name IS NULL.
--init-file=file_name 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 9.4 MySQL table types.
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) is 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 "e' is now true.
SHOW TABLE STATUS returns a lot of information about the tables.
LIKE to the SHOW STATUS command.
SHOW COLUMNS.
packed and comment 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 case sensitively.
--skip-show-database to mysqld.
UPDATE now also works with
BLOB/TEXT columns.
INNER join syntax. NOTE: This made INNER
an 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:
[[DAYS] [H]H:]MM:]SS[.fraction]
[[[[[H]H]H]H]MM]SS[.fraction]
DATETIME.
LOW_PRIORITY attribute to LOAD DATA INFILE.
LOAD DATA INFILE.
DECIMAL(x,y) now works according to ANSI SQL.
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 3.22
is that the output of DISTINCT is not sorted anymore.
mysql_num_fields() on
a MYSQL object, you must use mysql_field_count() instead.
LIBEWRAP; 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/--disable-large-files switch 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.
mysqld option --default-table-type.
The 3.22 version has faster and safer connect code and a lot of new nice enhancements. The reason for not including these changes in the 3.21 version is mainly that we are trying to avoid big changes to 3.21 to keep it as stable as possible. As there aren't really any MAJOR changes, upgrading to 3.22 should be very easy and painless. See section 4.16.2 Upgrading from a 3.21 version to 3.22.
3.22 should also be used with the new DBD-mysql (1.20xx) driver
that can use the new connect protocol!
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.
ROUND() will now work on Win32.
BLOB/TEXT column to REVERSE().
/*! */ with version numbers.
SUBSTRING(text FROM pos) to conform to ANSI SQL. (Before this
construct returned the rightmost 'pos' characters).
LOCK TABLES combined with DELETE FROM table
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)
DATA is not a reserved word anymore.
LOCK TABLES table_name READ; FLUSH TABLES;
isamchk should now work on Win32.
libtool 1.3.2.
configure.
--defaults-file=### to option file handling to force use
of only one specific option file.
CREATE syntax to ignore MySQL 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 not reserved words anymore.
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
all threads didn't die properly.
-O flush-time=# to mysqld. This is mostly
useful on Win32 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 optimized 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' optimzation 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 Win32 so that it will not crash
anymore 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 Win32.
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 Win32 version.
get_lock() now correctly times out on Win32!
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 since 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 user@host
PASSWORD for another user.
FLUSH STATUS that sets 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 statemants with many rows.
isamchk -rq on each table that has an index on
a CHAR or VARCHAR column.
mysql_setpermission, by Luuk de Boer, allows one
to easily create 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.
LIKE on
BLOB/TEXT columns with \0.
ESCAPE option to LIKE.
mysqladmin debug.
mysqld on Win32 with the --flush option.
This will flush all tables to disk after each update. This makes things
much safer on NT/Win98 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 Win32
(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
re-generate 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_priv privilege can get
information about all threads.
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_ID's 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 > 24M.
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 OPTION 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 OPTION 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 Win32,
and for systems that use MIT-pthreads. You can force the use of locking
with the --enable-locking option.
--console option to mysqld, to force a console window
(for error messages) when using Win32.
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 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 optimizer
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!
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-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_column_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).
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 (Organization,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 OSF1 (tested on 4.0D).
LIKE optimization 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 recognize 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:DD') as argument.
ORs of key parts
inside ANDs.
variables to mysqladmin.
ALTER TABLE to work with Win32 (Win32 can't rename open files).
Also fixed a couple of small bugs in the Win32 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,
OSF1 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 or not a value is NULL.
(This is according to ANSI SQL but may break
old applications that are ported from mSQL.)
You can get the old behavior by compiling with -DmSQL_COMPLIANT.
LEFT OUTER JOIN clauses.
ORDER BY on string formula with possible NULL values.
DAYOFYEAR(), DAYOFMONTH(), MONTH(),
YEAR(), WEEK(), QUARTER(), HOUR(), MINUTE(),
SECOND() and FIND_IN_SET().
SHOW VARIABLES.
mysql> SELECT 'first ' 'second'; -> 'first second'
mysqlaccess to 2.02.
LIKE.
WHERE data_field = date_field2 AND date_field2 = constant.
SHOW STATUS.
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 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.
BLOBs 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 ANSI SQL.
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 a 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 AS 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 INTs before sorting.
key_column=constant.
DOUBLE values sorted on integer results instead.
mysql no longer needs a database argument.
HAVING should be. According to ANSI, it should
be after GROUP BY but before ORDER BY. MySQL 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 ANSI SQL.
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 to the --bind-address
option.
SET OPTION CHARACTER SET cp1251_koi8 to enable conversions of
data to/from cp1251_koi8.
CREATE COLUMN syntax of NOT NULL columns to be after
the DEFAULT value, as specified in the ANSI SQL standard. This will
make mysqldump with NOT NULL and default values incompatible with
MySQL 3.20.
ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT
NULL.
CHAR and BIT as synonyms for CHAR(1).
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() 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 BLOBtypes, 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 field type 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 optimizer (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=# to mysqld.
FROM_UNIXTIME(timestamp) which returns a date string in
'YYYY-MM-DD HH:MM:DD' 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 only allow 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 OPTION 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 ANSI
synax 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. ANSI 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
optimizer 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;
Changes from 3.20.18 to 3.20.32b are not documented here since 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 at http://www.mysql.com/Contrib
site.
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
(ANSI SQL 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 unoptimizable 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 OPTION 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 optimizer when using many tables with the same
column used as key to different 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 SELECTs with
GROUP BY and ORDER BY.
DATETIME type (by Giovanni Maruzzelli
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 according to SQL92.
--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 BLOBs.
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().
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 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 allowed 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 behavior, use -r (or --raw).
mysql_fetch_lengths(MYSQL_RES *), which
returns an array of of column lengths (of type uint).
IS NULL in WHERE clause.
SELECT option STRAIGHT_JOIN to tell the optimizer 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 -lstrings 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 - 127) or unsigned (0 - 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 recreating tables.
BLOB and TIMESTAMP) are corrected.
TIMESTAMP now returns different date information depending on its
create length.
'_'.
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 info 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.
BLOB values can't "reliably" be used in GROUP BY or
ORDER BY or DISTINCT. Only the first max_sort_length
bytes (default 1024) are used when comparing BLOBbs in these cases.
This can be changed with the -O max_sort_length option to
mysqld. A workaround for most cases is to use a substring:
SELECT DISTINCT LEFT(blob,2048) FROM tbl_name.
BIGINT or DOUBLE (both are
normally 64 bits long). It depends on the function which precision one
gets. The general rule is that bit functions are done with BIGINT
precision, IF, and ELT() with BIGINT or DOUBLE
precision and the rest with DOUBLE precision. One should try to
avoid using bigger unsigned long long values than 63 bits
(9223372036854775807) for anything else than bit fields!
BLOB and TEXT columns, automatically
have all trailing spaces removed when retrieved. For CHAR types this
is okay, and may be regarded as a feature according to ANSI SQL92. The bug is
that in MySQL, VARCHAR columns are treated the same way.
ENUM and SET columns in one table.
safe_mysqld re-directs all messages from mysqld to the
mysqld log. One problem with this is that if you execute
mysqladmin refresh to close and reopen the log,
stdout and stderr are still redirected to the old log.
If you use --log extensively, you should edit safe_mysqld to
log to ''hostname'.err' instead of ''hostname'.log' so you can
easily reclaim the space for the old log by deleting the old one and
executing mysqladmin refresh.
UPDATE statement, columns are updated from left to right.
If you refer to a updated column, you will get the updated value instead of the
original value. For example:
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1will update
KEY with 2 instead of with 1.
select * from temporary_table, temporary_table as t2;
The following is known bugs in earlier versions of MySQL:
UPDATE that updated a key with
a WHERE on the same key may have failed because the key was used to
search for records and the same row may have been found multiple times:
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;A workaround is to use:
mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;This will work because MySQL will not use index on expressions in the
WHERE clause.
For platform-specific bugs, see the sections about compiling and porting.
Everything in this list is in the order it will be done. If you want to affect the priority order, please register a license or support us and tell us what you want to have done more quickly. See section 3 MySQL licensing and support.
select id from t where grp in (select grp from g where u > 100)
mysqld to support many character sets at the same time.
ALTER TABLE on a table that is symlinked to another
disk, create temporary tables on this disk.
RENAME table as table, table as table [,...]
DECRYPT().
FOREIGN key definitions in the '.frm' file.
DEFAULT values to columns. Give an error when using
an INSERT that doesn't contain a column that doesn't have a
DEFAULT.
SELECT CACHED ....
mysql_query() commands in a row
without reading results or give a nice error message when one does this.
BIT type to take 1 bit (now BIT takes 1 char).
ctime() doesn't work on some FreeBSD systems.
ORDER BY to update. This would be handy with functions like:
generate_id(start,step).
IMAGE option to LOAD DATA INFILE to not update
TIMESTAMP and AUTO_INCREMENT fields.
LOAD DATA INFILE understand a syntax like:
LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name TEXT_FIELDS (text_field1, text_field2, text_field3) SET table_field1=concatenate(text_field1, text_field2), table_field3=23 IGNORE text_field3
VARCHAR support (There is already support for this in MyISAM).
mysql to netscape.
LOCK DATABASES. (with various options)
NATURAL JOIN.
DECIMAL and NUMERIC types can't read exponential numbers;
Field_decimal::store(const char *from,uint len) must be recoded
to fix this.
mysql.cc to do fewer malloc() calls when hashing field
names.
t1 JOIN t2 ON ... and t1 JOIN t2 USING ...
Currently, you can only use this syntax with LEFT JOIN.
unsigned long long type.
CASE.
show status. Counts for:
INSERT/DELETE/UPDATE statements. Records reads and
updated. Selects on 1 table and selects with joins. Mean number of
tables in select. Key buffer read/write hits (logical and real).
ORDER BY, GROUP BY, temporary tables created.
mysql in the middle of a query, you should open
another connection and kill the old running query.
Alternatively, an attempt should be made to detect this in the server.
SHOW INFO FROM tbl_name for basic table information
should be implemented.
NATURAL JOIN.
CONNECT BY PRIOR ... to search hierarchy structures.
RENAME DATABASE
mysqladmin copy database new-database.
IGNORE option to the UPDATE statement (this will delete
all rows that gets a dupplicate key error while updating).
DATETIME to store fractions of seconds.
NULL for calculated columns.
get_changed_tables(timeout,table1,table2,...)
LAST_UPDATED(tbl_name)
update items,month set items.price=month.price where items.id=month.id;
SHOW commands.
SET TIMESTAMP=#;
UNION, MINUS, INTERSECT and FULL OUTER JOIN.
(Currently only LEFT OUTER JOIN is supported)
UNIQUE on fields that can be NULL.
SQL_OPTION MAX_SELECT_TIME=# to put a time limit on a query.
LIMIT to retrieve data from the end.
mysqld version which isn't multithreaded (3-5 days).
safe_mysqld: according to FSSTND (which
Debian tries to follow) PID files should go into '/var/run/<progname>.pid'
and log files into '/var/log'. It would be nice if you could put the
"DATADIR" in the first declaration of "pidfile" and "log", so the
placement of these files can be changed with a single statement.
UPDATE SET blob=read_blob_from_file('my_gif') where id=1;
zlib() for gzip-ed files to LOAD DATA INFILE.
BLOB columns (partly solved now).
AUTO_INCREMENT value when one sets a column to 0.
Use NULL instead.
JOIN with parentheses.
Time is given according to amount of work, not real time. TcX's main business is the use of MySQL not the development of it. But since TcX is a very flexible company, we have put a lot of resources into the development of MySQL.
SELECTs, and because we don't
do transactions, we can be much quicker on everything else). We will
support some kind of atomic operations on multiple tables,
though. Currently atomic operations can be done with LOCK
TABLES/UNLOCK TABLES but we will make this more automatic in the
future.
A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the native thread support in 2.4 and earlier versions are not good enough) and on Linux we use LinuxThreads by Xavier Leroy, Xavier.Leroy@inria.fr.
The hard part of porting to a new Unix variant without good native thread support is probably to port MIT-pthreads. See 'mit-pthreads/README' and Programming POSIX Threads.
The MySQL distribution includes a patched version of Provenzano's Pthreads from MIT (see MIT Pthreads web page). This can be used for some operating systems that do not have POSIX threads.
It is also possible to use another user level thread package named FSU Pthreads (see FSU Pthreads home page). This implementation is being used for the SCO port.
See the 'thr_lock.c' and 'thr_alarm.c' programs in the 'mysys' directory for some tests/examples of these problems.
Both the server and the client need a working C++ compiler (we use gcc
and have tried SparcWorks). Another compiler that is known to work is the
Irix cc.
To compile only the client use ./configure --without-server.
There is currently no support for only compiling the server. Nor is it likly to be added unless someone has a good reason for it.
If you want/need to change any 'Makefile' or the configure script you must
get Automake and Autoconf. We have used the automake-1.2 and
autoconf-2.12 distributions.
All steps needed to remake everything from the most basic files.
/bin/rm */.deps/*.P /bin/rm -f config.cache aclocal autoheader aclocal automake autoconf ./configure --with-debug --prefix='your installation directory' # The makefiles generated above need GNU make 3.75 or newer. # (called gmake below) gmake clean all install init-db
If you run into problems with a new port, you may have to do some debugging of MySQL! See section G.1 Debugging a MySQL server.
Note: Before you start debugging mysqld, first get the test
programs mysys/thr_alarm and mysys/thr_lock to work. This
will ensure that your thread installation has even a remote chance to work!
If you are using some functionality that is very new in MySQL,
you can try to run mysqld with the --skip-new (which will disable all
new, potentially unsafe functionality) or with --safe-mode which
disables a lot of optimization that may cause problems.
See section 18.1 What to do if MySQL keeps crashing.
If mysqld doesn't want to start, you should check that you don't have
any my.cnf file that interferes with your setup!
You can check your my.cnf arguments with mysqld --print-defaults
and avoid using them by starting with mysqld --no-defaults ....
If you have some very specific problem, you can always try to debug
MySQL. To do this you must configure MySQL with the
option --with-debug. You can check whether or not
MySQL was compiled with debugging by doing: mysqld
--help. If the --debug flag is listed with the options then you
have debugging enabled. mysqladmin ver also lists the
mysqld version as mysql ... -debug in this case.
If you are using gcc or egcs, the recommended configure line is:
CC=gcc CFLAGS="-O6" CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-debug
This will avoid problems with the libstdc++ library and with C++ exceptions.
If you can cause the mysqld server to crash quickly, you can try to
create a trace file of this:
Start the mysqld server with a trace log in '/tmp/mysql.trace'.
The log file will get very BIG.
mysqld --debug --log
or you can start it with
mysqld --debug=d,info,error,query,general,where:O,/tmp/mysql.trace
which only prints information with the most interesting tags.
When you configure MySQL for debugging you automatically enable a
lot of extra safety check functions that monitor the health of mysqld.
If they find something "unexpected," an entry will be written to
stderr, which safe_mysqld directs to the error log! This also
means that if you are having some unexpected problems with MySQL and
are using a source distribution, the first thing you should do is to
configure MySQL for debugging! (The second thing, of course, is to
send mail to mysql@lists.mysql.com and ask for help. Please use the
mysqlbug script for all bug reports or questions regarding the
MySQL version you are using!
On most system you can also start mysqld from gdb to get
more information if mysqld crashes.
With some gdb versions on Linux you must use run --one-thread if
you want to be able to debug mysqld threads. In this case you
can only have one thread active at a time.
If you are using gdb 4.17.x on Linux, you should install a '.gdb' file, with the following information, in your current directory:
set print sevenbit off handle SIGUSR1 nostop noprint handle SIGUSR2 nostop noprint handle SIGWAITING nostop noprint handle SIGLWP nostop noprint handle SIGPIPE nostop handle SIGALRM nostop handle SIGHUP nostop handle SIGTERM nostop noprint
Here follows an example how to debug mysqld:
shell> gdb /usr/local/libexec/mysqld gdb> run ... back # Do this when mysqld crashes info locals up info locals up ... (until you get some information about local variables) quit
Include the above output in a mail generated with mysqlbug and
mail this to mysql@lists.mysql.com.
If mysqld hangs you can try to use some system tools like
strace or /usr/proc/bin/pstack to examine where
mysqld has hanged.
If mysqld starts to eat up CPU or memory or if it "hangs", you
can use mysqladmin processlist status to find out if someone is
executing some query that takes a long time. It may be a good idea to
run mysqladmin -i10 processlist status in some window if you are
experiencing performance problems or problems when new clients can't connect.
If mysqld dies or hangs, you should start mysqld with
--log. When mysqld dies again, you can check in the log
file for the query that killed mysqld. Note that before starting
mysqld with --log you should check all your tables with
myisamchk. See section 13 Maintaining a MySQL installation.
If you are using a log file, mysqld --log, you should check the
'hostname' log files, that you can find in the database directory, for
any queries that could cause a problem. Try the command EXPLAIN
on all SELECT statements that takes a long time to ensure that
mysqld are using indexes properly. See section 7.22 EXPLAIN syntax (Get information about a SELECT). You
should also test complicated queries that didn't complete within the
mysql command line tool.
If you find the text mysqld restarted in the error log file (normally
named 'hostname.err') you have probably found a query that causes
mysqld to fail. If this happens you should check all your tables with
myisamchk (see section 13 Maintaining a MySQL installation), and test the queries in the
MySQL log files if someone doesn't work. If you find such a query,
try first upgrading to the newest MySQL version. If this doesn't
help and you can't find anything in the mysql mail archive, you should
report the bug to mysql@lists.mysql.com. Links to mail archives are
available at the online MySQL
documentation page.
If you get corrupted tables or if mysqld always fails after some
update commands, you can test if this bug is reproducible by doing the
following:
mysqladmin shutdown)
myisamchk -s database/*.MYI. Repair any
wrong tables with myisamchk -r database/table.MYI.
mysqld with --log-update
mysqld server.
mysqld server without --log-update
mysql < update-log. The update log
is saved in the MySQL database directory with the name
your-hostname.#.
ISAM code! FTP the tables and the update log to
ftp://www.mysql.com/pub/mysql/secret and we will fix this as soon as
possible!
The command mysqladmin debug will dump some information about
locks in use, used memory and query usage to the mysql log file. This
may help solve some problems. This command also provides some useful
information even if you haven't compiled MySQL for debugging!
If the problem is that some tables are getting slower and slower you
should try to optimize the table with OPTIMIZE TABLE or
myisamchk. See section 13 Maintaining a MySQL installation. You should also check the slow
queries with EXPLAIN.
You should also read the OS-specific section in this manual for problems that may be unique to your environment. See section 4.11 System-specific issues.
If you are using the Perl DBI interface, you can turn on
debugging information by using the trace method or by
setting the DBI_TRACE environment variable.
See section 20.5.2 The DBI interface.
To be able to debug a MySQL client with the integrated debug package,
you should configure MySQL with --with-debug.
See section 4.7.3 Typical configure options.
Before running a client, you should set the MYSQL_DEBUG environment
variable:
shell> MYSQL_DEBUG=d:t:O,/tmp/client.trace shell> export MYSQL_DEBUG
This causes clients to generate a trace file in '/tmp/client.trace'.
If you have problems with your own client code, you should attempt to
connect to the server and run your query using a client that is known to
work. Do this by running mysql in debugging mode (assuming you
have compiled MySQL with debugging on):
shell> mysql --debug=d:t:O,/tmp/client.trace
This will provide useful information in case you mail a bug report. See section 2.3 How to report bugs or problems.
If your client crashes at some 'legal' looking code, you should check that your 'mysql.h' include file matches your mysql library file. A very common mistake is to use an old 'mysql.h' file from an old MySQL installation with new MySQL library.
I have tried to use the RTS thread packages with MySQL but stumbled on the following problems:
They use old version of a lot of POSIX calls and it is very tedious to make wrappers for all functions. I am inclined to think that it would be easier to change the thread libraries to the newest POSIX specification.
Some wrappers are already written. See 'mysys/my_pthread.c' for more info.
At least the following should be changed:
pthread_get_specific should use one argument.
sigwait should take two arguments.
A lot of functions (at least pthread_cond_wait,
pthread_cond_timedwait)
should return the error code on error. Now they return -1 and set errno.
Another problem is that user-level threads use the ALRM signal and this
aborts a lot of functions (read, write, open...).
MySQL should do a retry on interrupt on all of these but it is
not that easy to verify it.
The biggest unsolved problem is the following:
To get thread-level alarms I changed 'mysys/thr_alarm.c' to wait between
alarms with pthread_cond_timedwait(), but this aborts with error
EINTR. I tried to debug the thread library as to why this happens,
but couldn't find any easy solution.
If someone wants to try MySQL with RTS threads I suggest the following:
-DHAVE_rts_threads.
thr_alarm.
thr_alarm. If it runs without any "warning", "error" or aborted
messages, you are on the right track. Here follows a successful run on
Solaris:
Main thread: 1 Tread 0 (5) started Thread: 5 Waiting process_alarm Tread 1 (6) started Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 1 (1) sec Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 2 (2) sec Thread: 6 Simulation of no alarm needed Thread: 6 Slept for 0 (3) sec Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 4 (4) sec Thread: 6 Waiting process_alarm thread_alarm Thread: 5 Slept for 10 (10) sec Thread: 5 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 5 (5) sec Thread: 6 Waiting process_alarm process_alarm ... thread_alarm Thread: 5 Slept for 0 (1) sec end
MySQL is very dependent on the thread package used. So when choosing a good platform for MySQL, the thread package is very important.
There are at least three types of thread packages:
ps may show the different threads. If one thread aborts the
whole process aborts. Most system calls are thread-safe and should
require very little overhead. Solaris, HP-UX, AIX and OSF1 have kernel
threads.
In some systems kernel threads are managed by integrating user level threads in the system libraries. In such cases, the thread switching can only be done by the thread library and the kernel isn't really "thread aware".
A regular expression (regex) is a powerful way of specifying a complex search.
MySQL uses regular Henry Spencer's inplementation of regular expressions. And that is aimed to conform to POSIX 1003.2. MySQL uses the extended version.
This is a simplistic reference that skips the details. To get more exact
information, see Henry Spencer's regex(7) manual page that is
included in the source distribution. See section C Contributors to MySQL.
A regular expression describes a set of strings. The simplest regexp is
one that has no special characters in it. For example, the regexp
hello matches hello and nothing else.
Nontrivial regular expressions use certain special constructs so that
they can match more than one string. For example, the regexp
hello|word matches either the string hello or the string
word.
As a more complex example, the regexp B[an]*s matches any of the
strings Bananas, Baaaaas, Bs and any other string
starting with a B, ending with an s, and containing any
number of a or n characters in between.
A regular expression may use any of the following special characters/constructs:
^
mysql> select "fo\nfo" REGEXP "^fo$"; -> 0 mysql> select "fofo" REGEXP "^fo"; -> 1
$
mysql> select "fo\no" REGEXP "^fo\no$"; -> 1 mysql> select "fo\no" REGEXP "^fo$"; -> 0
.
mysql> select "fofo" REGEXP "^f.*"; -> 1 mysql> select "fo\nfo" REGEXP "^f.*"; -> 1
a*
a characters.
mysql> select "Ban" REGEXP "^Ba*n"; -> 1 mysql> select "Baaan" REGEXP "^Ba*n"; -> 1 mysql> select "Bn" REGEXP "^Ba*n"; -> 1
a+
a characters.
mysql> select "Ban" REGEXP "^Ba+n"; -> 1 mysql> select "Bn" REGEXP "^Ba+n"; -> 0
a?
a character.
mysql> select "Bn" REGEXP "^Ba?n"; -> 1 mysql> select "Ban" REGEXP "^Ba?n"; -> 1 mysql> select "Baan" REGEXP "^Ba?n"; -> 0
de|abc
de or abc.
mysql> select "pi" REGEXP "pi|apa"; -> 1 mysql> select "axe" REGEXP "pi|apa"; -> 0 mysql> select "apa" REGEXP "pi|apa"; -> 1 mysql> select "apa" REGEXP "^(pi|apa)$"; -> 1 mysql> select "pi" REGEXP "^(pi|apa)$"; -> 1 mysql> select "pix" REGEXP "^(pi|apa)$"; -> 0
(abc)*
abc.
mysql> select "pi" REGEXP "^(pi)*$"; -> 1 mysql> select "pip" REGEXP "^(pi)*$"; -> 0 mysql> select "pipi" REGEXP "^(pi)*$"; -> 1
{1}
{2,3}
a*
a{0,}.
a+
a{1,}.
a?
a{0,1}.
i and no comma matches a sequence of exactly i matches of
the atom. An atom followed by a bound containing one integer i
and a comma matches a sequence of i or more matches of the atom.
An atom followed by a bound containing two integers i and
j matches a sequence of i through j (inclusive)
matches of the atom.
Both arguments must 0 >= value <= RE_DUP_MAX (default 255).
If there are two arguments, the second must be greater than or equal to the
first.
[a-dX]
[^a-dX]
a, b,
c, d or X. To include a literal ] character,
it must immediately follow the opening bracket [. To include a
literal - character, it must be written first or last. So
[0-9] matches any decimal digit. Any character that does not have
a defined meaning inside a [] pair has no special meaning and
matches only itself.
mysql> select "aXbc" REGEXP "[a-dXYZ]"; -> 1 mysql> select "aXbc" REGEXP "^[a-dXYZ]$"; -> 0 mysql> select "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1 mysql> select "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0 mysql> select "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1 mysql> select "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0
[[.characters.]]
ch
collating element, then the regular expression [[.ch.]]*c matches the
first five characters of chchcc.
[=character_class=]
o and (+) are the members of an
equivalence class, then [[=o=]], [[=(+)=]], and
[o(+)] are all synonymous. An equivalence class may not be an
endpoint of a range.
[:character_class:]
[: and :] stands for the list of all characters belonging
to that class. Standard character class names are:
| alnum | digit | punct |
| alpha | graph | space |
| blank | lower | upper |
| cntrl | xdigit |
ctype(3) manual
page. A locale may provide others. A character class may not be used as an
endpoint of a range.
mysql> select "justalnums" REGEXP "[[:alnum:]]+"; -> 1 mysql> select "!!" REGEXP "[[:alnum:]]+"; -> 0
[[:<:]]
[[:>:]]
ctype(3)) or an underscore
(_).
mysql> select "a word a" REGEXP "[[:<:]]word[[:>:]]"; -> 1 mysql> select "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -> 0
mysql> select "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -> 1
Unireg is our tty interface builder, but it uses a low level connection to our ISAM (which is used by MySQL) and because of this it is very quick. It has existed since 1979 (on Unix in C since ~1986).
Unireg has the following components:
convform utility. Converts '.frm' and text files between
different character sets.
myisampack utility. Packs a ISAM table (makes it 50-80%
smaller). The table can be read by MySQL like an ordinary
table. Only one record has to be decompressed per access. Cannot handle
BLOB or TEXT columns or updates (yet).
We update most of our production databases with the Unireg interface and serve web pages through MySQL (and in some extreme cases the Unireg report generator).
Unireg takes about 3M of disk space and works on at least the following platforms: SunOS 4.x, Solaris, Linux, HP-UX, ICL Unix, DNIX, SCO and MS-DOS.
Unireg is currently only available in Swedish and Finnish.
The price tag for Unireg is 10,000 Swedish kr (about $1500 US), but this includes support. Unireg is distributed as a binary. (But all the ISAM sources can be found in MySQL). Usually we compile the binary for the customer at their site.
All new development is concentrated to MySQL.
MySQL FREE PUBLIC LICENSE (Version 4, March 5, 1995)
Copyright (C) 1995, 1996 TcX AB & Monty Program KB & Detron HB Stockholm SWEDEN, Helsingfors FINLAND and Uppsala SWEDEN All rights reserved.
NOTE: This license is not the same as any of the GNU Licenses published by the Free Software Foundation. Its terms are substantially different from those of the GNU Licenses. If you are familiar with the GNU Licenses, please read this license with extra care.
This License applies to the computer program known as "MySQL". The "Program", below, refers to such program, and a "work based on the Program" means either the Program or any derivative work of the Program, as defined in the United States Copyright Act of 1976, such as a translation or a modification. The Program is a copyrighted work whose copyright is held by TcX Datakonsult AB and Monty Program KB and Detron HB.
This License does not apply when running "MySQL" on any Microsoft operating system. Microsoft operating systems include all versions of Microsoft Windows NT and Microsoft Windows.
BY MODIFYING OR DISTRIBUTING THE PROGRAM (OR ANY WORK BASED ON THE PROGRAM), YOU INDICATE YOUR ACCEPTANCE OF THIS LICENSE TO DO SO, AND ALL ITS TERMS AND CONDITIONS FOR COPYING, DISTRIBUTING OR MODIFYING THE PROGRAM OR WORKS BASED ON IT. NOTHING OTHER THAN THIS LICENSE GRANTS YOU PERMISSION TO MODIFY OR DISTRIBUTE THE PROGRAM OR ITS DERIVATIVE WORKS. THESE ACTIONS ARE PROHIBITED BY LAW. IF YOU DO NOT ACCEPT THESE TERMS AND CONDITIONS, DO NOT MODIFY OR DISTRIBUTE THE PROGRAM.
MySQL shareware license for Microsoft operating systems (Version 1, September 4, 1998)
Copyright (C) 1998 TcX AB & Monty Program KB & Detron HB Stockholm SWEDEN, Helsingfors FINLAND and Uppsala SWEDEN All rights reserved.
This License applies to the computer program known as "MySQL".
This License applies when running MySQL on any Microsoft operating system. Microsoft operating systems include all versions of Microsoft Windows NT and Microsoft Windows.
YOU SHOULD CAREFULLY READ THE FOLLOWING TERMS AND CONDITIONS BEFORE USING, COPYING OR DISTRIBUTING MySQL. BY USING, COPYING AND DISTRIBUTING MySQL, YOU INDICATE YOUR ACCEPTANCE OF THIS LICENSE TO DO SO, AND ALL ITS TERMS AND CONDITIONS FOR USING, COPYING AND DISTRIBUTING MySQL OR WORKS BASED ON IT. NOTHING OTHER THAN THIS LICENSE GRANTS YOU PERMISSION TO USE, COPY OR DISTRIBUTE MySQL OR ITS DERIVATIVE WORKS. THESE ACTIONS ARE PROHIBITED BY LAW. IF YOU DO NOT ACCEPT THESE TERMS AND CONDITIONS, DO NOT USE, COPY OR DISTRIBUTE MySQL.
Postgirot Bank AB 105 06 STOCKHOLM, SWEDEN TCX DataKonsult AB BOX 6434 11382 STOCKHOLM, SWEDEN SWIFT address: PGSI SESS Account number: 96 77 06 - 3Specify: license and/or support and your name and email address. In Europe and Japan, EuroGiro (that should be cheaper) can be used to the same account. If you want to pay by cheque make it payable to "Monty Program KB" and mail it to the address below.
TCX DataKonsult AB BOX 6434 11382 STOCKHOLM, SWEDENFor more information about commercial licensing, please contact:
David Axmark
Kungsgatan 65 B
753 21 UPPSALA
SWEDEN
Voice Phone +46-18-10 22 80 GMT 9-21. Swedish and
English spoken
Fax +46-8-729 69 05 Email *much* preferred.
E-Mail: mysql-licensing@mysql.com
For more about the license prices and commercial support, like email
support, please refer to the MySQL manual. See section 3.5 MySQL licensing and support costs. See section 3.6 Types of commercial support.
The use of MySQL or any work based on MySQL after the 30-day evaluation
period is in violation of international copyright laws.
mysql_affected_rows()
mysql_change_user()
mysql_close()
mysql_connect()
mysql_create_db()
mysql_data_seek()
mysql_debug()
mysql_drop_db()
mysql_dump_debug_info()
mysql_eof()
mysql_errno()
mysql_error()
mysql_escape_string()
mysql_fetch_field()
mysql_fetch_field_direct()
mysql_fetch_fields()
mysql_fetch_lengths()
mysql_fetch_row()
mysql_field_count(), mysql_field_count()
mysql_field_seek()
mysql_field_tell()
mysql_free_result()
mysql_get_client_info()
mysql_get_host_info()
mysql_get_proto_info()
mysql_get_server_info()
mysql_info()
mysql_init()
mysql_insert_id()
mysql_kill()
mysql_list_dbs()
mysql_list_fields()
mysql_list_processes()
mysql_list_tables()
mysql_num_fields()
mysql_num_rows()
mysql_options()
mysql_ping()
mysql_query()
mysql_real_connect()
mysql_real_query()
mysql_reload()
mysql_row_seek()
mysql_row_tell()
mysql_select_db()
mysql_shutdown()
mysql_stat()
mysql_store_result()
mysql_thread_id()
mysql_use_result()
GROUP BY clauses
ORDER BY clauses
AUTO_INCREMENT, and NULL values
BLOB columns, default values
BLOB columns, indexing
BLOB, inserting binary data
cc1plus problems
configure, running after prior invocation
db table, sorting
DBI Perl module
BLOB and TEXT columns
fatal signal 11
SELECT and WHERE clauses
GROUP BY, aliases in
host table, sorting
BLOB columns
IS NULL
LIKE
NULL values
TEXT columns
make_binary_release
msql2mysql
myisamchk, myisamchk
myisampack, myisampack, myisampack, myisampack, myisampack
mysql
mysql_fix_privilege_tables
mysql_install_db
mysqlaccess
mysqladmin, mysqladmin, mysqladmin, mysqladmin, mysqladmin, mysqladmin
mysqlbug
mysqld
mysqldump, mysqldump
mysqlimport, mysqlimport, mysqlimport
mysqlshow
NULL values vs. empty values
NULL values, and indexes
NULL values, and AUTO_INCREMENT columns
NULL values, and TIMESTAMP columns
ORDER BY, aliases in
pack_isam
replace
configure after prior invocation
safe_mysqld
sql_yacc.cc problems
TEXT columns, default values
TEXT columns, indexing
TIMESTAMP, and NULL values
user table, sorting
LIKE
mysql.columns_priv table
mysql.db table
mysql.host table
mysql.tables_priv table
mysql.user table
This document was generated on 1 January 2000 using the texi2html translator version 1.52 (extended by davida@detron.se).