The MySQL® software delivers a very fast, multi-threaded, multi-user, and
robust SQL (Structured Query Language) database server. MySQL Server is
intended for mission-critical, heavy-load production systems as well as for
embedding into mass-deployed software. MySQL is a registered trademark of
MySQL AB.
The MySQL software is Dual Licensed. Users can choose to use the MySQL
software as an Open Source product under the terms of the GNU General Public
License (http://www.fsf.org/licenses/)
or can purchase a standard commercial license from MySQL AB. See
http://www.mysql.com/company/legal/licensing/ for more information on
our licensing policies.
The following list describes some sections of particular interest in this
manual:
If you have found a sensitive security bug in MySQL Server, please let us
know immediately by sending an email message to
<security@mysql.com>.
1.1. About This Manual
This is the Reference Manual for all releases of the MySQL Database System
from version 3.23 through release 4.1.21. It is also applicable for
versions of the MySQL software previous to 4.1 (such as 3.23 or 4.0)
because functional changes are indicated with reference to version
numbers. For later MySQL releases, see the appropriately-numbered
edition of this manual.
Because this manual serves as a reference, it does not provide general
instruction on SQL or relational database concepts. It also does not
teach you how to use your operating system or command-line interpreter.
The MySQL Database Software is under constant development, and the
Reference Manual is updated frequently as well. The most recent version
of the manual is available online in searchable form at
http://dev.mysql.com/doc/. Other formats also are available there,
including HTML, PDF, and Windows CHM versions.
The Reference Manual source files are written in DocBook XML format. The
HTML version and other formats are produced automatically, primarily
using the DocBook XSL stylesheets. For information about DocBook, see
http://docbook.org/
If you have any suggestions concerning additions or corrections to this
manual, please send them to the documentation team at
<docs@mysql.com>.
This manual was originally written by David Axmark and Michael “Monty”
Widenius. It is maintained by the MySQL Documentation Team, consisting
of Paul DuBois, Stefan Hinz, Mike Hillyer, and Jon Stephens. For the
many other contributors, see
Appendix C, Credits.
The copyright to this manual is owned by the Swedish company MySQL AB.
MySQL® and the MySQL logo are registered trademarks of MySQL AB. Other
trademarks and registered trademarks referred to in this manual are the
property of their respective owners, and are used for identification
purposes only.
1.2. Conventions Used in This Manual
This manual uses certain typographical conventions:
Text in this style is used for SQL
statements; database, table, and column names; program listings and
source code; and environment variables. Example: “To
reload the grant tables, use the FLUSH PRIVILEGES
statement.”
Text in this style
indicates input that you type in examples.
Text in this style
indicates the names of executable programs and scripts, examples being
mysql (the MySQL command
line client program) and mysqld
(the MySQL server executable).
Text in this style is used for
variable input for which you should substitute a value of your own
choosing.
Filenames and directory names are written like this: “The
global my.cnf file is located in the
/etc directory.”
Character sequences are written like this: “To
specify a wildcard, use the ‘%’ character.”
Text in this style is used for
emphasis.
Text in this style is used in
table headings and to convey especially strong emphasis.
When commands are shown that are meant to be executed from within a
particular program, the prompt shown preceding the command indicates which
command to use. For example, shell>
indicates a command that you execute from your login shell, and
mysql> indicates a statement that you execute
from the mysql client program:
shell> type a shell command here
mysql> type a mysql statement here
The “shell” is your command interpreter. On Unix,
this is typically a program such as sh,
csh, or
bash. On Windows, the equivalent program is
command.com or
cmd.exe, typically run in a
console window.
When you enter a command or statement shown in an example, do not type the
prompt shown in the example.
Database, table, and column names must often be substituted into statements.
To indicate that such substitution is necessary, this manual uses
db_name,
tbl_name, and
col_name. For example, you might
see a statement like this:
mysql> SELECT col_name FROM db_name.tbl_name;
This means that if you were to enter a similar statement, you would supply
your own database, table, and column names, perhaps like this:
mysql> SELECT author_name FROM biblio_db.author_list;
SQL keywords are not case sensitive and may be written in any lettercase.
This manual uses uppercase.
In syntax descriptions, square brackets (‘[’
and ‘]’) indicate optional words or clauses.
For example, in the following statement, IF EXISTS
is optional:
DROP TABLE [IF EXISTS] tbl_name
When a syntax element consists of a number of alternatives, the alternatives
are separated by vertical bars (‘|’). When one
member from a set of choices may be
chosen, the alternatives are listed within square brackets (‘[’
and ‘]’):
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices must
be chosen, the alternatives are listed within braces (‘{’
and ‘}’):
{DESCRIBE | DESC} tbl_name [col_name | wild]
An ellipsis (...) indicates the omission of a
section of a statement, typically to provide a shorter version of more
complex syntax. For example, INSERT ... SELECT
is shorthand for the form of
INSERT statement that is followed by a
SELECT statement.
An ellipsis can also indicate that the preceding syntax element of a
statement may be repeated. In the following example, multiple
reset_option values may be given,
with each of those after the first preceded by commas:
RESET reset_option [,reset_option] ...
Commands for setting shell variables are shown using Bourne shell syntax.
For example, the sequence to set the CC
environment variable and run the configure
command looks like this in Bourne shell syntax:
shell> CC=gcc ./configure
If you are using csh or
tcsh, you must issue commands somewhat differently:
shell> setenv CC gcc
shell> ./configure
1.3. Overview of MySQL AB
MySQL AB is the company of the MySQL founders and main developers. MySQL AB
was originally established in Sweden by David Axmark, Allan Larsson, and
Michael “Monty” Widenius.
We are dedicated to developing the MySQL database software and promoting it
to new users. MySQL AB owns the copyright to the MySQL source code, the
MySQL logo and (registered) trademark, and this manual. See
Section 1.4, “Overview of the MySQL Database Management System”.
The MySQL core values show our dedication to MySQL and Open Source.
These core values direct how MySQL AB works with the MySQL server software:
To be the best and the most widely used database in the world
To be available and affordable by all
To be easy to use
To be continuously improved while remaining fast and safe
To be fun to use and improve
To be free from bugs
These are the core values of the company MySQL AB and its employees:
We subscribe to the Open Source philosophy and support the Open Source
community
We aim to be good citizens
We prefer partners that share our values and mindset
We answer email and provide support
We are a virtual company, networking with others
We work against software patents
The MySQL Web site (http://www.mysql.com/)
provides the latest information about MySQL and MySQL AB.
By the way, the “AB” part of the company name is
the acronym for the Swedish “aktiebolag,” or “stock
company.” It translates to “MySQL, Inc.”
In fact, MySQL, Inc. and MySQL GmbH are examples of MySQL AB subsidiaries.
They are located in the United States and Germany, respectively.
1.4. Overview of the MySQL Database Management System
MySQL, the most popular Open Source SQL database management system, is
developed, distributed, and supported by MySQL AB. MySQL AB is a commercial
company, founded by the MySQL developers. It is a second generation Open
Source company that unites Open Source values and methodology with a
successful business model.
The MySQL Web site (http://www.mysql.com/)
provides the latest information about MySQL software and MySQL AB.
MySQL is a database management system.
A database is a structured collection of data. It may be anything from a
simple shopping list to a picture gallery or the vast amounts of
information in a corporate network. To add, access, and process data
stored in a computer database, you need a database management system
such as MySQL Server. Since computers are very good at handling large
amounts of data, database management systems play a central role in
computing, as standalone utilities, or as parts of other applications.
MySQL is a relational database management system.
A relational database stores data in separate tables rather than putting
all the data in one big storeroom. This adds speed and flexibility. The
SQL part of “MySQL” stands for “Structured
Query Language.” SQL is the most common standardized language
used to access databases and is defined by the ANSI/ISO SQL Standard.
The SQL standard has been evolving since 1986 and several versions
exist. In this manual, “SQL-92” refers to the
standard released in 1992, “SQL:1999” refers
to the standard released in 1999, and “SQL:2003”
refers to the current version of the standard. We use the phrase “the
SQL standard” to mean the current version of the SQL Standard at
any time.
MySQL software is Open Source.
Open Source means that it is possible for anyone to use and modify the
software. Anybody can download the MySQL software from the Internet and
use it without paying anything. If you wish, you may study the source
code and change it to suit your needs. The MySQL software uses the GPL
(GNU General Public License),
http://www.fsf.org/licenses/, to define what you may and may not do
with the software in different situations. If you feel uncomfortable
with the GPL or need to embed MySQL code into a commercial application,
you can buy a commercially licensed version from us. See the MySQL
Licensing Overview for more information (http://www.mysql.com/company/legal/licensing/).
The MySQL Database Server is very fast, reliable, and easy to use.
If that is what you are looking for, you should give it a try. MySQL
Server also has a practical set of features developed in close
cooperation with our users. You can find a performance comparison of
MySQL Server with other database managers on our benchmark page. See
Section 7.1.4, “The MySQL Benchmark Suite”.
MySQL Server was originally developed to handle large databases much
faster than existing solutions and has been successfully used in highly
demanding production environments for several years. Although under
constant development, MySQL Server today offers a rich and useful set of
functions. Its connectivity, speed, and security make MySQL Server
highly suited for accessing databases on the Internet.
MySQL Server works in client/server or embedded systems.
The MySQL Database Software is a client/server system that consists of a
multi-threaded SQL server that supports different backends, several
different client programs and libraries, administrative tools, and a
wide range of application programming interfaces (APIs).
We also provide MySQL Server as an embedded multi-threaded library that
you can link into your application to get a smaller, faster,
easier-to-manage standalone product.
A large amount of contributed MySQL software is available.
It is very likely that your favorite application or language supports
the MySQL Database Server.
The official way to pronounce “MySQL” is “My
Ess Que Ell” (not “my sequel”), but we
don't mind if you pronounce it as “my sequel” or
in some other localized way.
1.4.1. History of MySQL
We started out with the intention of using the
mSQL database system to connect to our tables
using our own fast low-level (ISAM) routines. However, after some
testing, we came to the conclusion that mSQL
was not fast enough or flexible enough for our needs. This resulted in a
new SQL interface to our database but with almost the same API interface
as mSQL. This API was designed to allow
third-party code that was written for use with
mSQL to be ported easily for use with MySQL.
The derivation of the name MySQL is not clear. Our base directory and a
large number of our libraries and tools have had the prefix “my”
for well over 10 years. However, co-founder Monty Widenius's daughter is
also named My. Which of the two gave its name to MySQL is still a
mystery, even for us.
The name of the MySQL Dolphin (our logo) is “Sakila,”
which was chosen by the founders of MySQL AB from a huge list of names
suggested by users in our “Name the Dolphin”
contest. The winning name was submitted by Ambrose Twebaze, an Open
Source software developer from Swaziland, Africa. According to Ambrose,
the feminine name Sakila has its roots in SiSwati, the local language of
Swaziland. Sakila is also the name of a town in Arusha, Tanzania, near
Ambrose's country of origin, Uganda.
1.4.2. The Main Features of MySQL
The following list describes some of the important characteristics of the
MySQL Database Software. See also
Section 1.6, “MySQL Development Roadmap”, for more information about
current and upcoming features.
Fully multi-threaded using kernel threads. It can easily use multiple
CPUs if they are available.
Provides transactional and non-transactional storage engines.
Uses very fast B-tree disk tables (MyISAM)
with index compression.
Relatively easy to add other storage engines. This is useful if you
want to add an SQL interface to an in-house database.
A very fast thread-based memory allocation system.
Very fast joins using an optimized one-sweep multi-join.
In-memory hash tables, which are used as temporary tables.
SQL functions are implemented using a highly optimized class library
and should be as fast as possible. Usually there is no memory
allocation at all after query initialization.
The MySQL code is tested with Purify (a commercial memory leakage
detector) as well as with Valgrind, a GPL tool (http://developer.kde.org/~sewardj/).
The server is available as a separate program for use in a
client/server networked environment. It is also available as a
library that can be embedded (linked) into standalone applications.
Such applications can be used in isolation or in environments where
no network is available.
Data Types:
Many data types: signed/unsigned integers 1, 2, 3, 4, and 8 bytes
long, FLOAT,
DOUBLE, CHAR,
VARCHAR, TEXT,
BLOB, DATE,
TIME, DATETIME,
TIMESTAMP, YEAR,
SET, ENUM,
and OpenGIS spatial types. See
Chapter 11, Data Types.
Fixed-length and variable-length records.
Statements and Functions:
Full operator and function support in the
SELECT and WHERE
clauses of queries. For example:
mysql> SELECT CONCAT(first_name, ' ', last_name)
-> FROM citizen
-> WHERE income/dependents > 10000 AND age > 30;
Full support for SQL GROUP BY and
ORDER BY clauses. Support for group
functions (COUNT(),
COUNT(DISTINCT ...), AVG(),
STD(), SUM(),
MAX(), MIN(),
and
GROUP_CONCAT()).
Support for LEFT OUTER JOIN and
RIGHT OUTER JOIN with both standard SQL
and ODBC syntax.
Support for aliases on tables and columns as required by standard SQL.
DELETE, INSERT,
REPLACE, and UPDATE
return the number of rows that were changed (affected). It is possible
to return the number of rows matched instead by setting a flag when
connecting to the server.
The MySQL-specific SHOW statement can be
used to retrieve information about databases, storage engines,
tables, and indexes.
The EXPLAIN statement can be used to
determine how the optimizer resolves a query.
Function names do not clash with table or column names. For example,
ABS is a valid column name. The only restriction is that for
a function call, no spaces are allowed between the function name and
the ‘(’ that follows it. See
Section 9.5, “Treatment of Reserved Words in MySQL”.
You can mix tables from different databases in the same query (as of
MySQL 3.22).
Security:
A privilege and password system that is very flexible and secure, and
that allows host-based verification. Passwords are secure because
all password traffic is encrypted when you connect to a server.
Scalability and Limits:
Handles large databases. We use MySQL Server with databases that
contain 50 million records. We also know of users who use MySQL
Server with 60,000 tables and about 5,000,000,000 rows.
Up to 64 indexes per table are allowed (32 before MySQL 4.1.2). Each
index may consist of 1 to 16 columns or parts of columns. The
maximum index width is 1000 bytes (767 for
InnoDB); before MySQL 4.1.2, the limit is
500 bytes. An index may use a prefix of a column for
CHAR, VARCHAR,
BLOB, or TEXT
column types.
Connectivity:
Clients can connect to the MySQL server using TCP/IP sockets on any
platform. On Windows systems in the NT family (NT, 2000, XP, 2003,
or Vista), clients can connect using named pipes. On Unix systems,
clients can connect using Unix domain socket files.
In MySQL 4.1 and higher, Windows servers also support shared-memory
connections if started with the
--shared-memory option. Clients can
connect through shared memory by using the
--protocol=memory option.
The Connector/ODBC (MyODBC) interface provides MySQL support for
client programs that use ODBC (Open Database Connectivity)
connections. For example, you can use MS Access to connect to your
MySQL server. Clients can be run on Windows or Unix. MyODBC source
is available. All ODBC 2.5 functions are supported, as are many
others. See
Chapter 18,
Connectors.
The Connector/J interface provides MySQL support for Java client
programs that use JDBC connections. Clients can be run on Windows or
Unix. Connector/J source is available. See
Chapter 18,
Connectors.
MySQL Connector/NET enables developers to easily create .NET
applications that require secure, high-performance data connectivity
with MySQL. It implements the required ADO.NET interfaces and
integrates into ADO.NET aware tools. Developers can build
applications using their choice of .NET languages. MySQL
Connector/NET is a fully managed ADO.NET driver written in 100% pure
C#. See
Chapter 18, Connectors.
Full support for several different character sets, including
latin1 (cp1252),
german,
big5, ujis,
and more. For example, the Scandinavian characters ‘å’,
‘ä’ and ‘ö’
are allowed in table and column names. Unicode support is available
as of MySQL 4.1.
All data is saved in the chosen character set. All comparisons for
normal string columns are case-insensitive.
Sorting is done according to the chosen character set (using Swedish
collation by default). It is possible to change this when the MySQL
server is started. To see an example of very advanced sorting, look
at the Czech sorting code. MySQL Server supports many different
character sets that can be specified at compile time and runtime.
Clients and Tools:
MySQL Server has built-in support for SQL statements to check,
optimize, and repair tables. These statements are available from the
command line through the
mysqlcheck client. MySQL
also includes
myisamchk, a very fast
command-line utility for performing these operations on
MyISAM
tables. See
Chapter 5, Database Administration.
All MySQL programs can be invoked with the
--help or -?
options to obtain online assistance.
1.4.3. MySQL Stability
This section addresses the questions, “How
stable is MySQL Server?” and, “Can
I depend on MySQL Server in this project?” We will
try to clarify these issues and answer some important questions that
concern many potential users. The information in this section is based
on data gathered from the mailing lists, which are very active in
identifying problems as well as reporting types of use.
The original code stems back to the early 1980s. It provides a stable code
base, and the ISAM table format used by the
original storage engine remains backward-compatible. At TcX, the
predecessor of MySQL AB, MySQL code has worked in projects since
mid-1996, without any problems. When the MySQL Database Software
initially was released to a wider public, our new users quickly found
some pieces of untested code. Each new release since then has had fewer
portability problems, even though each new release has also had many new
features.
Each release of the MySQL Server has been usable. Problems have occurred
only when users try code from the “gray zones.”
Naturally, new users don't know what the gray zones are; this section
therefore attempts to document those areas that are currently known. The
descriptions mostly deal with Versions 3.23 and later of MySQL Server.
All known and reported bugs are fixed in the latest version, with the
exception of those listed in the bugs section, which are design-related.
See
Section A.8, “Known Issues in MySQL”.
The MySQL Server design is multi-layered with independent modules. Some of
the newer modules are listed here with an indication of how well-tested
each of them is:
Replication (Stable)
Large groups of servers using replication are in production use, with
good results. Work on enhanced replication features is continuing.
InnoDB tables (Stable)
The InnoDB transactional storage engine
has been stable since version 3.23.49. InnoDB
is being used in large, heavy-load production systems.
Full-text searches (Stable)
Full-text searching is widely used. Important feature enhancements
were added in MySQL 4.0 and 4.1.
MyODBC 3.51 (Stable)
MyODBC 3.51 uses ODBC SDK 3.51 and is in
wide production use. Some issues brought up appear to be
application-related and independent of the ODBC driver or underlying
database server.
1.4.4. How Large MySQL Tables Can Be
MySQL 3.22 had a 4GB (4 gigabyte) limit on table size. With the
MyISAM storage engine in MySQL 3.23, the
maximum table size was increased to 65536 terabytes (2567 – 1
bytes). With this larger allowed table size, the maximum effective table
size for MySQL databases is usually determined by operating system
constraints on file sizes, not by MySQL internal limits.
The InnoDB storage engine maintains
InnoDB tables within a tablespace that can be
created from several files. This allows a table to exceed the maximum
individual file size. The tablespace can include raw disk partitions,
which allows extremely large tables. The maximum tablespace size is
64TB.
The following table lists some examples of operating system file-size
limits. This is only a rough guide and is not intended to be definitive.
For the most up-to-date information, be sure to check the documentation
specific to your operating system.
Operating System
File-size Limit
Linux 2.2-Intel
32-bit
2GB (LFS: 4GB)
Linux 2.4+
(using ext3 filesystem) 4TB
Solaris 9/10
16TB
NetWare w/NSS filesystem
8TB
Win32 w/ FAT/FAT32
2GB/4GB
Win32 w/
NTFS
2TB (possibly larger)
MacOS X
w/ HFS+
2TB
On Linux 2.2, you can get MyISAM tables
larger than 2GB in size by using the Large File Support (LFS) patch for
the ext2 filesystem. On Linux 2.4, patches also exist for ReiserFS to
get support for big files (up to 2TB). Most current Linux distributions
are based on kernel 2.4 or higher and include all the required LFS
patches. With JFS and XFS, petabyte and larger files are possible on
Linux. However, the maximum available file size still depends on several
factors, one of them being the filesystem used to store MySQL tables.
Windows users please note: FAT and VFAT (FAT32) are
not considered suitable for
production use with MySQL. Use NTFS instead.
By default, MySQL creates MyISAM tables with
an internal structure that allows a maximum size of about 4GB. You can
check the maximum table size for a MyISAM
table with the SHOW TABLE STATUS statement or
with myisamchk -dv
tbl_name. See
Section 13.5.4,
“SHOW Syntax”.
If you need a MyISAM table that is larger
than 4GB and your operating system supports large files, the
CREATE TABLE statement supports
AVG_ROW_LENGTH and
MAX_ROWS
options. See
Section 13.1.5, “CREATE TABLE Syntax”.
You can also change these options with ALTER TABLE
to increase a table's maximum allowable size after the table has been
created. See
Section 13.1.2, “ALTER TABLE Syntax”.
Other ways to work around file-size limits for
MyISAM tables are as follows:
If your large table is read-only, you can use
myisampack to compress
it.
myisampack usually
compresses a table by at least 50%, so you can have, in effect, much
bigger tables.
myisampack also can
merge multiple tables into a single table. See
Section 8.5, “myisampack
— Generate Compressed, Read-Only MyISAM
Tables”.
MySQL includes a MERGE library that
allows you to handle a collection of MyISAM
tables that have identical structure as a single
MERGE table. See
Section 14.3, “The MERGE Storage
Engine”.
1.4.5. Year 2000 Compliance
The MySQL Server itself has no problems with Year 2000 (Y2K) compliance:
MySQL Server uses Unix time functions that handle dates into the year
2037 for
TIMESTAMP values. For
DATE and DATETIME
values, dates through the year 9999 are
accepted.
All MySQL date functions are implemented in one source file,
sql/time.cc, and are coded very
carefully to be year 2000-safe.
In MySQL, the YEAR data type can store
the years 0 and
1901 to
2155 in one byte and display them using
two or four digits. All two-digit years are considered to be in the
range 1970 to
2069, which means that if you store 01
in a
YEAR column, MySQL Server treats it as
2001.
The following simple demonstration illustrates that MySQL Server has no
problems with DATE or
DATETIME values through the year 9999, and no
problems with TIMESTAMP values until after
the year 2030:
The final two TIMESTAMP column values are
zero because the year values (2040,
9999) exceed the
TIMESTAMP
maximum. The TIMESTAMP data type, which is
used to store the current time, supports values that range from
'1970-01-01 00:00:00' to
'2030-01-01 00:00:00' on 32-bit machines (signed value). On
64-bit machines, TIMESTAMP handles values
up to
2106 (unsigned value).
Although MySQL Server itself is Y2K-safe, you may run into problems if you
use it with applications that are not Y2K-safe. For example, many old
applications store or manipulate years using two-digit values (which are
ambiguous) rather than four-digit values. This problem may be compounded
by applications that use values such as 00
or 99 as “missing”
value indicators. Unfortunately, these problems may be difficult to fix
because different applications may be written by different programmers,
each of whom may use a different set of conventions and date-handling
functions.
Thus, even though MySQL Server has no Y2K problems,
it is the application's responsibility to provide unambiguous input.
See
Section 11.3.4, “Y2K Issues and Date Types”, for MySQL Server's
rules for dealing with ambiguous date input data that contains two-digit
year values.
1.5. Overview of the MaxDB Database Management System
MaxDB is a heavy-duty enterprise database. The database management system is
SAP-certified.
MaxDB is the new name of a database management system formerly called SAP
DB. In 2003 SAP AG and MySQL AB joined a partnership and re-branded the
database system to MaxDB. The development of MaxDB has continued since then
as it was done before—through the SAP developer team.
MySQL AB cooperates closely with the MaxDB team at SAP around delivering
improvements to the MaxDB product. Joint efforts include development of new
native drivers to enable more efficient usage of MaxDB in the Open Source
community, and improvement of documentation to expand the MaxDB user base.
Interoperability features between MySQL and MaxDB database also are seen as
important. For example, the new MaxDB Synchronization Manager supports data
synchronization from MaxDB to MySQL.
The MaxDB database management system does not share a common code-base with
the MySQL database management system. The MaxDB and MySQL database
management systems are independent products provided by MySQL AB.
MySQL AB offers a complete portfolio of Professional Services for MaxDB.
1.5.1. What is MaxDB?
MaxDB is an ANSI SQL-92 (entry level) compliant relational database
management system (RDBMS) from SAP AG, that is delivered by MySQL AB as
well. MaxDB fulfills the needs for enterprise usage: safety,
scalability, high concurrency, and performance. It runs on all major
operating systems. Over the years it has proven able to run SAP R/3 and
terabytes of data in 24×7 operation.
The database development started in 1977 as a research project at the
Technical University of Berlin. In the early 1980s it became a database
product that subsequently was owned by Nixdorf, Siemens Nixdorf,
Software AG, and today by SAP AG. Along the way, it has been named VDN,
Reflex, Supra 2, DDB/4, Entire SQL-DB-Server, and ADABAS D. In 1997, SAP
took over the software from Software AG and renamed it to SAP DB. Since
October 2000, SAP DB sources additionally were released as Open Source
under the GNU General Public License (see
Appendix J, GNU General Public License).
In 2003, SAP AG and MySQL AB formed a partnership and re-branded the
database system to MaxDB.
1.5.2. History of MaxDB
The history of MaxDB goes back to SAP DB, SAP AG's DBMS. That is, MaxDB is
a re-branded and enhanced version of SAP DB. For many years, MaxDB has
been used for small, medium, and large installations of the mySAP
Business Suite and other demanding SQL applications requiring an
enterprise-class DBMS with regard to the number of users, the
transactional workload, and the size of the database.
SAP DB was meant to provide an alternative to third-party database systems
such as Oracle, Microsoft SQL Server, and DB2 by IBM. In October 2000,
SAP AG released SAP DB under the GNU GPL license (see
Appendix J, GNU General Public License), thus making it Open
Source software.
Today, MaxDB is used in about 3,500 SAP customer installations worldwide.
Moreover, the majority of all DBMS installations on Unix and Linux
within SAP’s IT department rely on MaxDB. MaxDB is tuned toward
heavy-duty online transaction processing (OLTP) with several thousand
users and database sizes ranging from several hundred GB to multiple TB.
In 2003, SAP and MySQL concluded a partnership and development cooperation
agreement. As a result, SAP's database system SAP DB has been delivered
under the name of MaxDB by MySQL since the release of version 7.5
(November 2003).
Version 7.5 of MaxDB is a direct advancement of the SAP DB 7.4 code base.
Therefore, the MaxDB software version 7.5 can be used as a direct
upgrade of previous SAP DB versions starting 7.2.04 and higher.
The former SAP DB development team at SAP AG is responsible, now as
before, for developing and supporting MaxDB. MySQL AB cooperates closely
with the MaxDB team at SAP around delivering improvements to the MaxDB
product, see
Section 1.5, “Overview of the MaxDB Database Management System”.
Both SAP AG and MySQL AB handle the sale and distribution of MaxDB. The
advancement of MaxDB and the MySQL Server leverages synergies that
benefit both product lines.
MaxDB is subjected to SAP AG's complete quality assurance process before
it is shipped with SAP solutions or provided as a download from the
MySQL site.
1.5.3. Features of MaxDB
MaxDB is a heavy-duty, SAP-certified Open Source database for OLTP and
OLAP usage which offers high reliability, availability, scalability, and
a very comprehensive feature set. It is targeted for large mySAP
Business Suite environments and other applications that require maximum
enterprise-level database functionality and complements the MySQL
database server.
MaxDB operates as a client/server product. It was developed to meet the
needs of installations in OLTP and Data Warehouse/OLAP/Decision Support
scenarios and offers these benefits:
Easy configuration and administration:
GUI-based Installation Manager and Database Manager as single
administration tools for DBMS operations
Around-the-clock operation, no planned
downtimes, no permanent attendance required:
Automatic space management, no need for reorganizations
Sophisticated backup and restore
capabilities: Online and incremental backups,
recovery wizard to guide you through the recovery scenario
Supports large number of users, database
sizes in the terabytes, and demanding workloads:
Proven reliability, performance, and scalability
High availability: Cluster
support, standby configuration, hot standby configuration
1.5.4. Licensing and Support
MaxDB can be used under the same licenses available for the other products
distributed by MySQL AB. Thus, MaxDB is available under the GNU General
Public License, and a commercial license. For more information on
licensing, see
http://www.mysql.com/company/legal/licensing/.
MySQL AB offers MaxDB technical support to non-SAP customers. MaxDB
support is available on various levels (Basic, Silver, and Gold), which
expand from unlimited email/web-support to 24×7 phone support for
business critical systems.
MySQL AB also offers Licenses and Support for MaxDB when used with SAP
Applications, like SAP NetWeaver and mySAP Business Suite. For more
information on licenses and support for your needs, please contact MySQL
AB. (See
http://www.mysql.com/company/contact/.)
Consulting and training services are available. MySQL gives classes on
MaxDB at regular intervals. See
http://www.mysql.com/training/ for a list of classes.
1.5.5. Feature Differences Between MaxDB and MySQL
MaxDB is MySQL AB's SAP-certified database. The MaxDB database server
complements the MySQL AB product portfolio. Some MaxDB features are not
available on the MySQL database management server and vice versa.
The following list summarizes the main differences between MaxDB and
MySQL; it is not complete.
MaxDB runs as a client/server system. MySQL can run as a client/server
system or as an embedded system.
MaxDB might not run on all platforms supported by MySQL.
MaxDB uses a proprietary network protocol for client/server
communication. MySQL uses either TCP/IP (with or without SSL
encryption), sockets (under Unix-like systems), or named pipes or
shared memory (under Windows NT-family systems).
MaxDB supports stored procedures and functions. MySQL 5.0 and up also
supports stored procedures and functions. MaxDB supports programming
of triggers through an SQL extension. MySQL 5.0 supports triggers.
MaxDB contains a debugger for stored procedure languages, can
cascade nested triggers, and supports multiple triggers per action
and row.
MaxDB is distributed with user interfaces that are text-based,
graphical, or Web-based. MySQL is distributed with text-based user
interfaces only; graphical user interfaces such as MySQL Query
Browser or MySQL Administrator are shipped separately from the main
distributions. Web-based user interfaces for MySQL are offered by
third parties.
MaxDB supports a number of programming interfaces that also are
supported by MySQL. For developing with MaxDB, the MaxDB ODBC
Driver, SQL Database Connectivity (SQLDBC), JDBC Driver, Perl and
Python modules and a MaxDB PHP extension, which provides access to
MySQL MaxDB databases using PHP, are available. Third Party
Programming Interfaces: Support for OLE DB, ADO, DAO, RDO and .NET
through ODBC. MaxDB supports embedded SQL with C/C++.
MaxDB includes administrative features that MySQL does not have: job
scheduling by time (included in MySQL as of 5.1), event, and alert,
and sending messages to a database administrator on alert
thresholds. (MySQL has scheduling support starting with version
5.1.6.)
1.5.6. Interoperability Features Between MaxDB and MySQL
MaxDB and MySQL are independent database management servers. The
interoperation of the systems is possible in a way that the systems can
exchange their data. To exchange data between MaxDB and MySQL, you can
use the import and export tools of the systems or the MaxDB
Synchronization Manager. The import and export tools can be used to
transfer data in an infrequent, manual fashion. The MaxDB
Synchronization Manager offers faster, automatic data transfer
capabilities.
The MaxDB Loader can be used to export data and object definitions. The
Loader can export data using MaxDB internal, binary formats and text
formats (CSV). Data exported from MaxDB in text formats can be imported
into MySQL using the
mysqlimport client program.
To export MySQL data, you can use either
mysqldump to create
INSERT statements or
SELECT ... INTO OUTFILE to create a text file (CSV). Use the
MaxDB Loader to import the data files generated by MySQL.
Object definitions can be exchanged between the systems using MaxDB Loader
and the MySQL tool mysqldump.
As the SQL dialects of both systems differ slightly and MaxDB has
features currently not supported by MySQL like SQL constraints, we
recommend to hand-tune the definition files. The
mysqldump tool offers an
option
--compatible=maxdb to produce output that is
compatible with MaxDB to make porting easier.
The MaxDB Synchronization Manager is available as part of MaxDB 7.6. The
Synchronization Manager supports creation of asynchronous replication
scenarios between several MaxDB instances. However, interoperability
features also are planned, so that the Synchronization Manager supports
replication to and from a MySQL server.
1.5.7. MaxDB-Related Links
The main page for MaxDB information is
http://www.mysql.com/products/maxdb, which provides details about
the features of the MaxDB database management systems and has pointers
to available documentation.
The MySQL Reference Manual does not contain any MaxDB documentation other
than the introduction given in this section. MaxDB has its own
documentation, which is called the MaxDB library and is available at
http://dev.mysql.com/doc/maxdb/index.html.
MySQL AB runs a community mailing list on MaxDB; see
http://lists.mysql.com/maxdb. The list shows a vivid community
discussion. Many of the core developers contribute to it. Product
announcements are sent to the list.
A Web forum on MaxDB is available at
http://forums.mysql.com/.
The forum focuses on MaxDB questions not related to SAP applications.
This section provides a snapshot of the MySQL development roadmap,
including major features implemented in or planned for various MySQL
releases. The following sections provide information for each release
series.
The current production release series is MySQL 5.0, which was declared
stable for production use as of MySQL 5.0.15, released in October 2005.
The previous production release series was MySQL 4.1, which was declared
stable for production use as of MySQL 4.1.7, released in October 2004. “Production
status” means that future 5.0 and 4.1 development is limited only
to bugfixes. For the older MySQL 4.0 and 3.23 series, only critical
bugfixes are made.
Active MySQL development currently is taking place in the MySQL 5.0 and
5.1 release series; and new features are being added only to the latter.
MySQL 4.0 is available for download at
http://dev.mysql.com/
and from our mirrors. MySQL 4.0 has been tested by a large number of
users and is in production use at many large sites.
1.6.1.1. Features Available in MySQL 4.0
Speed enhancements
MySQL 4.0 implemented a query cache that can give a major
speed boost to applications with repetitive queries. See
Section 5.13, “The MySQL Query Cache”.
MySQL 4.0 further increased the speed of MySQL Server in a
number of areas, such as bulk
INSERT statements, searching on
packed indexes, full-text searching (using
FULLTEXT indexes), and
COUNT(DISTINCT).
Introduction of Embedded MySQL Server
The Embedded Server library added in this release can easily
be used to create standalone and embedded applications. The
embedded server provides an alternative to using MySQL in a
client/server environment. See
Section 1.6.1.2, “The Embedded MySQL Server”.
InnoDB storage engine as standard
The InnoDB storage engine began
to be offered as a standard feature of the MySQL server.
This provided full support for ACID transactions, foreign
keys with cascading UPDATE
and DELETE, and row-level locking
as standard features. See
Section 14.2, “The InnoDB
Storage Engine”.
New functionality
The enhanced FULLTEXT search
capabilities of MySQL Server 4.0 enabled
FULLTEXT indexing of large text
masses with both binary and natural-language searching
logic. It became possible to customize minimal word length
and define your own stop word lists in most human languages,
enabling a broader class of applications to be built with
MySQL Server. See
Section 12.7, “Full-Text Search Functions”.
Standards compliance, portability, and migration
MySQL Server added support for the
UNION statement, a standard SQL
feature.
Features to simplify migration from other database systems to
MySQL Server include TRUNCATE TABLE
(as in Oracle) .
Internationalization
German-speaking users should note that MySQL 4.0 added support
for a new character set,
latin1_de, which ensures that
words with umlauts are sorted in the same order as in German
telephone books.
Usability enhancements
As of version 4.0, most mysqld
parameters (startup options) can be set without taking down
the server. This is a convenient feature for database
administrators. See
Section 13.5.3, “SET Syntax”.
Multiple-table DELETE and
UPDATE statements were added.
On Windows, symbolic link handling at the database level was
enabled by default. On Unix, the
MyISAM storage engine added
support for symbolic linking at the table level (and not
just the database level as before).
The addition of the
SQL_CALC_FOUND_ROWS and
FOUND_ROWS() functions made it
possible to find out the number of rows a
SELECT query that includes a
LIMIT clause would have returned
without that clause.
The libmysqld embedded server library
made MySQL Server suitable for a wider range of applications. Using
this library, developers can embed MySQL Server into various
applications and electronics devices, where the end user has no
knowledge of there actually being an underlying database. Embedded
MySQL Server is ideal for use in Internet appliances, public kiosks,
turnkey hardware/software combination units, high performance
Internet servers, self-contained databases distributed on CD-ROM,
and so on.
MySQL Server 4.0 laid the foundation for new features implemented in
MySQL 4.1, such as subqueries and Unicode support, which were desired by
many of our customers.
A “subquery” is a
SELECT statement nested within
another statement. A “derived table”
(an unnamed view) is a subquery in the
FROM clause of another statement.
See
Section 13.2.8, “Subquery Syntax”.
BTREE indexing is supported for
HEAP tables, significantly
improving response time for non-exact searches.
Added functionality:
CREATE TABLE
tbl_name2 LIKE
tbl_name1
allows you to create, with a single statement, a new table
with a structure exactly like that of an existing table.
The MyISAM storage engine added
support for OpenGIS spatial types for storing geographical
data. See
Chapter 16, Spatial Extensions.
Support was added for replication over SSL connections.
Support for a number of additional storage engines was
implemented in the MySQL 4.1 release series:
The EXAMPLE storage engine is
a “stub” engine that serves
as an example in the MySQL source code for writing new
storage engines, and is primarily of interest to
developers. See
Section 14.6, “The EXAMPLE
Storage Engine”.
NDB Cluster is the storage
engine used by MySQL Cluster to implement tables that
are partitioned over many computers. See
Chapter 15, MySQL Cluster.
The BLACKHOLE storage engine
accepts but does not store data, and always returns an
empty result set. It is for use primarily in
replication. See
Section 14.9, “The BLACKHOLE
Storage Engine”.
Note: These engine
were implemented at different points in the development of
MySQL 4.1. Please see the indicated sections for particulars
in each case.
Standards compliance, portability, and
migration:
The enhanced client/server protocol available beginning with
MySQL 4.1.1 provides the ability to pass multiple warnings
to the client, rather than only a single result, making it
much easier to track problems that occur in operations such
as bulk data loading.
To support applications that require the use of local
languages, the MySQL software added extensive Unicode
support through the utf8 and
ucs2 character sets.
Definition of character sets by column, table, and database.
This allows for a high degree of flexibility in application
design, particularly for multi-language Web sites. See
Chapter 10, Character Set Support.
Per-connection time zones support, allowing individual clients
to select their own time zones when necessary.
Usability enhancements:
The addition of a server-based HELP
command that can be used to get help information for SQL
statements. This information is always applicable to the
particular server version being used. Because this
information is available by issuing an SQL statement, any
client can access it. For example, the
help command of the
mysql
command-line client has been modified to have this
capability.
The syntax INSERT ... ON DUPLICATE KEY
UPDATE ... was implemented. This allows you to update
an existing row if the insert would have caused a duplicate
value for a primary or unique index. See
Section 13.2.4, “INSERT Syntax”.
The following features are implemented in MySQL 5.0.
BIT Data
Type: Can be used to store numbers in binary
notation.
Cursors: Elementary
support for server-side cursors.
Data Dictionary (Information Schema):
The introduction of the
INFORMATION_SCHEMA database in MySQL
5.0 provided a standards-compliant means for accessing the MySQL
Server's metadata; that is, data about the databases (schemas) on
the server and the objects which they contain.
Instance Manager: Can be
used to start and stop the MySQL Server, even from a remote host.
Precision Math: MySQL 5.0
introduced stricter criteria for acceptance or rejection of data,
and implemented a new library for fixed-point arithmetic. These
contributed to a much higher degree of accuracy for mathematical
operations and greater control over invalid values.
Storage Engines: Storage
engines added in MySQL 5.0 include
ARCHIVE and
FEDERATED.
Stored Routines: Support
for named stored procedures and stored functions was implemented in
MySQL 5.0.
Strict Mode and Standard Error Handling:
MySQL 5.0 added a strict mode where by it follows standard SQL in a
number of ways in which it did not previously. Support for standard
SQLSTATE error messages was also implemented.
Triggers: MySQL 5.0 added
limited support for triggers.
VARCHAR Data
Type: The maximum effective length of a
VARCHAR column was increased to 65,532
bytes, and stripping of trailing whitespace was eliminated.
Views: MySQL 5.0 added
support for named, updatable views.
This section lists sources of additional information that you may find
helpful, such as the MySQL mailing lists and user forums, and Internet Relay
Chat.
This section introduces the MySQL mailing lists and provides guidelines as
to how the lists should be used. When you subscribe to a mailing list,
you receive all postings to the list as email messages. You can also
send your own questions and answers to the list.
To subscribe to or unsubscribe from any of the mailing lists described in
this section, visit
http://lists.mysql.com/.
For most of them, you can select the regular version of the list where
you get individual messages, or a digest version where you get one large
message per day.
Please do not send messages about
subscribing or unsubscribing to any of the mailing lists, because such
messages are distributed automatically to thousands of other users.
Your local site may have many subscribers to a MySQL mailing list. If so,
the site may have a local mailing list, so that messages sent from
lists.mysql.com to your site are propagated to the local list. In
such cases, please contact your system administrator to be added to or
dropped from the local MySQL list.
If you wish to have traffic for a mailing list go to a separate mailbox in
your mail program, set up a filter based on the message headers. You can
use either the List-ID: or
Delivered-To: headers to identify list
messages.
The MySQL mailing lists are as follows:
announce
This list is for announcements of new versions of MySQL and related
programs. This is a low-volume list to which all MySQL users should
subscribe.
mysql
This is the main list for general MySQL discussion. Please note that
some topics are better discussed on the more-specialized lists. If
you post to the wrong list, you may not get an answer.
bugs
This list is for people who want to stay informed about issues
reported since the last release of MySQL or who want to be actively
involved in the process of bug hunting and fixing. See
Section 1.8, “How to Report Bugs or Problems”.
internals
This list is for people who work on the MySQL code. This is also the
forum for discussions on MySQL development and for posting patches.
mysqldoc
This list is for people who work on the MySQL documentation: people
from MySQL AB, translators, and other community members.
benchmarks
This list is for anyone interested in performance issues. Discussions
concentrate on database performance (not limited to MySQL), but also
include broader categories such as performance of the kernel,
filesystem, disk system, and so on.
packagers
This list is for discussions on packaging and distributing MySQL. This
is the forum used by distribution maintainers to exchange ideas on
packaging MySQL and on ensuring that MySQL looks and feels as
similar as possible on all supported platforms and operating
systems.
java
This list is for discussions about the MySQL server and Java. It is
mostly used to discuss JDBC drivers such as MySQL Connector/J.
win32
This list is for all topics concerning the MySQL software on Microsoft
operating systems, such as Windows 9x, Me, NT, 2000, XP, and 2003.
myodbc
This list is for all topics concerning connecting to the MySQL server
with ODBC.
gui-tools
This list is for all topics concerning MySQL graphical user interface
tools such as MySQL Administrator
and MySQL Query Browser.
cluster
This list is for discussion of MySQL Cluster.
dotnet
This list is for discussion of the MySQL server and the .NET platform.
It is mostly related to MySQL Connector/Net.
plusplus
This list is for all topics concerning programming with the C++ API
for MySQL.
perl
This list is for all topics concerning Perl support for MySQL with
DBD::mysql.
If you're unable to get an answer to your questions from a MySQL mailing
list or forum, one option is to purchase support from MySQL AB. This
puts you in direct contact with MySQL developers.
The following table shows some MySQL mailing lists in languages other than
English. These lists are not operated by MySQL AB.
A German mailing list. To subscribe, email
subscribe mysql-de your@email.address to this list. You can
find information about this mailing list at
http://www.4t2.com/mysql/.
A Spanish mailing list. To subscribe, email
subscribe mysql your@email.address to this list.
1.7.1.1. Guidelines for Using the Mailing Lists
Please don't post mail messages from your browser with HTML mode turned
on. Many users don't read mail with a browser.
When you answer a question sent to a mailing list, if you consider your
answer to have broad interest, you may want to post it to the list
instead of replying directly to the individual who asked. Try to make
your answer general enough that people other than the original poster
may benefit from it. When you post to the list, please make sure that
your answer is not a duplication of a previous answer.
Try to summarize the essential part of the question in your reply. Don't
feel obliged to quote the entire original message.
When answers are sent to you individually and not to the mailing list,
it is considered good etiquette to summarize the answers and send the
summary to the mailing list so that others may have the benefit of
responses you received that helped you solve your problem.
1.7.2. MySQL Community Support at the MySQL Forums
The forums at
http://forums.mysql.com are an important community resource. Many
forums are available, grouped into these general categories:
Migration
MySQL Usage
MySQL Connectors
Programming Languages
Tools
3rd-Party Applications
Storage Engines
MySQL Technology
SQL Standards
Business
1.7.3. MySQL Community Support on Internet Relay Chat (IRC)
In addition to the various MySQL mailing lists and forums, you can find
experienced community people on Internet Relay Chat (IRC). These are the
best networks/channels currently known to us:
#mysql is primarily for MySQL questions,
but other database and general SQL questions are welcome. Questions
about PHP, Perl, or C in combination with MySQL are also common.
If you are looking for IRC client software to connect to an IRC network,
take a look at xChat
(http://www.xchat.org/).
X-Chat (GPL licensed) is available for Unix as well as for Windows
platforms (a free Windows build of X-Chat is available at
http://www.silverex.org/download/).
1.8. How to Report Bugs or Problems
Before posting a bug report about a problem, please try to verify that it is
a bug and that it has not been reported already:
Start by searching the MySQL online manual at
http://dev.mysql.com/doc/. We try to keep the manual up to date by
updating it frequently with solutions to newly found problems. The
change history (http://dev.mysql.com/doc/mysql/en/news.html)
can be particularly useful since it is quite possible that a newer
version contains a solution to your problem.
If you get a parse error for a SQL statement, please check your syntax
closely. If you can't find something wrong with it, it's extremely
likely that your current version of MySQL Server doesn't support the
syntax you are using. If you are using the current version and the
manual doesn't cover the syntax that you are using, MySQL Server doesn't
support your statement. In this case, your options are to implement the
syntax yourself or email
<licensing@mysql.com>
and ask for an offer to implement it.
If the manual covers the syntax you are using, but you have an older
version of MySQL Server, you should check the MySQL change history to
see when the syntax was implemented. In this case, you have the option
of upgrading to a newer version of MySQL Server.
You can also use
http://www.mysql.com/search/ to search all the Web pages (including
the manual) that are located at the MySQL AB Web site.
If you can't find an answer in the manual, the bugs database, or the mailing
list archives, check with your local MySQL expert. If you still can't find
an answer to your question, please use the following guidelines for
reporting the bug.
The normal way to report bugs is to visit
http://bugs.mysql.com/,
which is the address for our bugs database. This database is public and can
be browsed and searched by anyone. If you log in to the system, you can
enter new reports. If you have no Web access, you can generate a bug report
by using the mysqlbug script
described at the end of this section.
Bugs posted in the bugs database at
http://bugs.mysql.com/
that are corrected for a given release are noted in the change history.
If you have found a sensitive security bug in MySQL, you can send email to
<security@mysql.com>.
Writing a good bug report takes patience, but doing it right the first time
saves time both for us and for yourself. A good bug report, containing a
full test case for the bug, makes it very likely that we will fix the bug in
the next release. This section helps you write your report correctly so that
you don't waste your time doing things that may not help us much or at all.
Please read this section carefully and make sure that all the information
described here is included in your report.
Preferably, you should test the problem using the latest production or
development version of MySQL Server before posting. Anyone should be able to
repeat the bug by just using mysql test < script_file
on your test case or by running the shell or Perl script that you include in
the bug report. Any bug that we are able to repeat has a high chance of
being fixed in the next MySQL release.
It is most helpful when a good description of the problem is included in the
bug report. That is, give a good example of everything you did that led to
the problem and describe, in exact detail, the problem itself. The best
reports are those that include a full example showing how to reproduce the
bug or problem. See
Section E.1.6, “Making a Test Case If You Experience Table Corruption”.
Remember that it is possible for us to respond to a report containing too
much information, but not to one containing too little. People often omit
facts because they think they know the cause of a problem and assume that
some details don't matter. A good principle to follow is that if you are in
doubt about stating something, state it. It is faster and less troublesome
to write a couple more lines in your report than to wait longer for the
answer if we must ask you to provide information that was missing from the
initial report.
The most common errors made in bug reports are (a) not including the version
number of the MySQL distribution that you use, and (b) not fully describing
the platform on which the MySQL server is installed (including the platform
type and version number). These are highly relevant pieces of information,
and in 99 cases out of 100, the bug report is useless without them. Very
often we get questions like, “Why doesn't this work for
me?” Then we find that the feature requested wasn't implemented in
that MySQL version, or that a bug described in a report has been fixed in
newer MySQL versions. Errors often are platform-dependent. In such cases, it
is next to impossible for us to fix anything without knowing the operating
system and the version number of the platform.
If you compiled MySQL from source, remember also to provide information
about your compiler if it is related to the problem. Often people find bugs
in compilers and think the problem is MySQL-related. Most compilers are
under development all the time and become better version by version. To
determine whether your problem depends on your compiler, we need to know
what compiler you used. Note that every compiling problem should be regarded
as a bug and reported accordingly.
If a program produces an error message, it is very important to include the
message in your report. If we try to search for something from the archives,
it is better that the error message reported exactly matches the one that
the program produces. (Even the lettercase should be observed.) It is best
to copy and paste the entire error message into your report. You should
never try to reproduce the message from memory.
If you have a problem with Connector/ODBC (MyODBC), please try to generate a
trace file and send it with your report. See the MyODBC section of
Chapter 18, Connectors.
If your report includes long query output lines from test cases that you run
with the mysql command-line
tool, you can make the output more readable by using the
--vertical option or the
\G
statement terminator. The EXPLAIN SELECT
example later in this section demonstrates the use of
\G.
Please include the following information in your report:
The version number of the MySQL distribution you are using (for example,
MySQL 5.0.19). You can find out which version you are running by
executing mysqladmin version.
The
mysqladmin program can be
found in the
bin directory under your MySQL
installation directory.
The manufacturer and model of the machine on which you experience the
problem.
The operating system name and version. If you work with Windows, you can
usually get the name and version number by double-clicking your My
Computer icon and pulling down the “Help/About
Windows” menu. For most Unix-like operating systems, you can get
this information by executing the command uname -a.
Sometimes the amount of memory (real and virtual) is relevant. If in
doubt, include these values.
If you are using a source distribution of the MySQL software, include
the name and version number of the compiler that you used. If you have a
binary distribution, include the distribution name.
If the problem occurs during compilation, include the exact error
messages and also a few lines of context around the offending code in
the file where the error occurs.
If a database table is related to the problem, include the output from
the SHOW CREATE TABLE
db_name.tbl_name
statement in the bug report. This is a very easy way to get the
definition of any table in a database. The information helps us create a
situation matching the one that you have experienced.
For performance-related bugs or problems with
SELECT statements, you should always
include the output of EXPLAIN SELECT ...,
and at least the number of rows that the SELECT
statement produces. You should also include the output from
SHOW CREATE TABLE
tbl_name for each table
that is involved. The more information you provide about your situation,
the more likely it is that someone can help you.
The following is an example of a very good bug report. The statements
are run using the mysql
command-line tool. Note the use of the \G
statement terminator for statements that would otherwise provide very
long output lines that are difficult to read.
mysql> SHOW VARIABLES;
mysql> SHOW COLUMNS FROM ...\G<output from SHOW COLUMNS>
mysql> EXPLAIN SELECT ...\G<output from EXPLAIN>
mysql> FLUSH STATUS;
mysql> SELECT ...;<A short version of the output from SELECT,
including the time taken to run the query>
mysql> SHOW STATUS;<output from SHOW STATUS>
If a bug or problem occurs while running
mysqld, try to provide an
input script that reproduces the anomaly. This script should include any
necessary source files. The more closely the script can reproduce your
situation, the better. If you can make a reproducible test case, you
should upload it to be attached to the bug report.
If you can't provide a script, you should at least include the output
from mysqladmin variables extended-status
processlist in your report to provide some information
on how your system is performing.
If you can't produce a test case with only a few rows, or if the test
table is too big to be included in the bug report (more than 10 rows),
you should dump your tables using
mysqldump and create a
README file that describes your problem.
Create a compressed archive of your files using
tar and
gzip or
zip, and use FTP to
transfer the archive to
ftp://ftp.mysql.com/pub/mysql/upload/. Then enter the problem into
our bugs database at
http://bugs.mysql.com/.
If you believe that the MySQL server produces a strange result from a
statement, include not only the result, but also your opinion of what
the result should be, and an explanation describing the basis for your
opinion.
When you provide an example of the problem, it's better to use the table
names, variable names, and so forth that exist in your actual situation
than to come up with new names. The problem could be related to the name
of a table or variable. These cases are rare, perhaps, but it is better
to be safe than sorry. After all, it should be easier for you to provide
an example that uses your actual situation, and it is by all means
better for us. If you have data that you don't want to be visible to
others in the bug report, you can use FTP to transfer it to
ftp://ftp.mysql.com/pub/mysql/upload/. If the information is really
top secret and you don't want to show it even to us, go ahead and
provide an example using other names, but please regard this as the last
choice.
Include all the options given to the relevant programs, if possible. For
example, indicate the options that you use when you start the
mysqld server, as well as the options that you use to
run any MySQL client programs. The options to programs such as
mysqld and
mysql, and to the
configure script, are
often key to resolving problems and are very relevant. It is never a bad
idea to include them. If your problem involves a program written in a
language such as Perl or PHP, please include the language processor's
version number, as well as the version for any modules that the program
uses. For example, if you have a Perl script that uses the
DBI and
DBD::mysql modules, include the version
numbers for Perl, DBI, and
DBD::mysql.
If your question is related to the privilege system, please include the
output of mysqlaccess, the
output of mysqladmin reload,
and all the error messages you get when trying to connect. When you test
your privileges, you should first run
mysqlaccess. After this, execute
mysqladmin reload version
and try to connect with the program that gives you trouble.
mysqlaccess can be found
in the
bin directory under your MySQL
installation directory.
If you have a patch for a bug, do include it. But don't assume that the
patch is all we need, or that we can use it, if you don't provide some
necessary information such as test cases showing the bug that your patch
fixes. We might find problems with your patch or we might not understand
it at all. If so, we can't use it.
If we can't verify the exact purpose of the patch, we won't use it. Test
cases help us here. Show that the patch handles all the situations that
may occur. If we find a borderline case (even a rare one) where the
patch won't work, it may be useless.
Guesses about what the bug is, why it occurs, or what it depends on are
usually wrong. Even the MySQL team can't guess such things without first
using a debugger to determine the real cause of a bug.
Indicate in your bug report that you have checked the reference manual
and mail archive so that others know you have tried to solve the problem
yourself.
If the problem is that your data appears corrupt or you get errors when
you access a particular table, you should first check your tables and
then try to repair them with
CHECK TABLE and
REPAIR TABLE or with myisamchk.
See
Chapter 5, Database Administration.
If you are running Windows, please verify the value of
lower_case_table_names using the
SHOW VARIABLES LIKE 'lower_case_table_names'
command. This variable affects how the server handles lettercase of
database and table names. Its effect for a given value should be as
described in
Section 9.2.2, “Identifier Case Sensitivity”.
If you often get corrupted tables, you should try to find out when and
why this happens. In this case, the error log in the MySQL data
directory may contain some information about what happened. (This is the
file with the .err
suffix in the name.) See
Section 5.11.1, “The Error Log”. Please include any relevant
information from this file in your bug report. Normally
mysqld should
never crash a table if nothing
killed it in the middle of an update. If you can find the cause of
mysqld dying, it's much
easier for us to provide you with a fix for the problem. See
Section A.1, “How to Determine What Is Causing a Problem”.
If possible, download and install the most recent version of MySQL
Server and check whether it solves your problem. All versions of the
MySQL software are thoroughly tested and should work without problems.
We believe in making everything as backward-compatible as possible, and
you should be able to switch MySQL versions without difficulty. See
Section 2.1.2, “Choosing Which MySQL Distribution to Install”.
If you have no Web access and cannot report a bug by visiting
http://bugs.mysql.com/,
you can use the
mysqlbug script to generate a
bug report (or a report about any problem).
mysqlbug helps you generate a report by determining much of
the following information automatically, but if something important is
missing, please include it with your message.
mysqlbug can be found in the scripts
directory (source distribution) and in the bin
directory under your MySQL installation directory (binary distribution).
This section describes how MySQL relates to the ANSI/ISO SQL standards.
MySQL Server has many extensions to the SQL standard, and here you can
find out what they are and how to use them. You can also find
information about functionality missing from MySQL Server, and how to
work around some of the differences.
The SQL standard has been evolving since 1986 and several versions exist.
In this manual, “SQL-92” refers to the
standard released in 1992, “SQL:1999” refers
to the standard released in 1999, and “SQL:2003”
refers to the current version of the standard. We use the phrase “the
SQL standard” or “standard SQL” to
mean the current version of the SQL Standard at any time.
One of our main goals with the product is to continue to work toward
compliance with the SQL standard, but without sacrificing speed or
reliability. We are not afraid to add extensions to SQL or support for
non-SQL features if this greatly increases the usability of MySQL Server
for a large segment of our user base. The HANDLER
interface is an example of this strategy. See
Section 13.2.3, “HANDLER Syntax”.
We continue to support transactional and non-transactional databases to
satisfy both mission-critical 24/7 usage and heavy Web or logging usage.
MySQL Server was originally designed to work with medium-sized databases
(10-100 million rows, or about 100MB per table) on small computer
systems. Today MySQL Server handles terabyte-sized databases, but the
code can also be compiled in a reduced version suitable for hand-held
and embedded devices. The compact design of the MySQL server makes
development in both directions possible without any conflicts in the
source tree.
Currently, we are not targeting real-time support, although MySQL
replication capabilities offer significant functionality.
In MySQL 4.1.2 in later, high-availability database clustering is
supported by the NDBCluster storage engine.
See
Chapter 15,
MySQL Cluster.
XML support is to be implemented in a future version of the database
server.
1.9.1. What Standards MySQL Follows
Our aim is to support the full ANSI/ISO SQL standard, but without making
concessions to speed and quality of the code.
ODBC levels 0-3.51.
1.9.2. Selecting SQL Modes
The MySQL server can operate in different SQL modes, and can apply these
modes differentially for different clients. This capability enables each
application to tailor the server's operating mode to its own
requirements.
SQL modes control aspects of server operation such as what SQL syntax
MySQL should support and what kind of data validation checks it should
perform. This makes it easier to use MySQL in different environments and
to use MySQL together with other database servers.
You can set the default SQL mode by starting
mysqld with the
--sql-mode="mode_value"
option. Beginning with MySQL 4.1, you can also change the mode at
runtime by setting the sql_mode system
variable with a SET [SESSION|GLOBAL] sql_mode='mode_value'
statement.
You can tell mysqld to run
in ANSI mode with the --ansi startup option.
Running the server in ANSI mode is the same as starting it with the
following options:
As of MySQL 4.1.1, you can achieve the same effect at runtime by
executing these two statements:
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode = 'ANSI';
You can see that setting the sql_mode
system variable to 'ANSI' enables all SQL
mode options that are relevant for ANSI mode as follows:
mysql> SET GLOBAL sql_mode='ANSI';
mysql> SELECT @@global.sql_mode;
-> 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI'
Note that running the server in ANSI mode with
--ansi is not quite the same as setting the
SQL mode to 'ANSI'. The
--ansi
option affects the SQL mode and also sets the transaction isolation
level. Setting the SQL mode to
'ANSI' has no effect on the isolation
level.
MySQL Server supports some extensions that you probably won't find in
other SQL DBMSs. Be warned that if you use them, your code won't be
portable to other SQL servers. In some cases, you can write code that
includes MySQL extensions, but is still portable, by using comments of
the following form:
/*! MySQL-specific code */
In this case, MySQL Server parses and executes the code within the
comment as it would any other SQL statement, but other SQL servers will
ignore the extensions. For example, MySQL Server recognizes the
STRAIGHT_JOIN keyword in the following statement, but other
servers will not:
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
If you add a version number after the ‘!’
character, the syntax within the comment is executed only if the MySQL
version is greater than or equal to the specified version number. The
TEMPORARY keyword in the following comment
is executed only by servers from MySQL 3.23.02 or higher:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
The following descriptions list MySQL extensions, organized by category.
Organization of data on disk
MySQL Server maps each database to a directory under the MySQL data
directory, and maps tables within a database to filenames in the
database directory. This has a few implications:
Database and table names are case sensitive in MySQL Server on
operating systems that have case-sensitive filenames (such as
most Unix systems). See
Section 9.2.2, “Identifier Case Sensitivity”.
You can use standard system commands to back up, rename, move,
delete, and copy tables that are managed by the
MyISAM or
ISAM
storage engines. For example, it is possible to rename a
MyISAM table by renaming the
.MYD, .MYI,
and .frm files to which the table
corresponds. (Nevertheless, it is preferable to use
RENAME TABLE or
ALTER TABLE ... RENAME and let the server rename the
files.)
By default, strings can be enclosed by either ‘"’
or ‘'’, not just by ‘'’.
(If the
ANSI_QUOTES SQL mode is enabled,
strings can be enclosed only by ‘'’
and the server interprets strings enclosed by ‘"’
as identifiers.)
‘\’ is the escape character in
strings.
In SQL statements, you can access tables from different
databases with the
db_name.tbl_name
syntax. Some SQL servers provide the same functionality but call
this
User space. MySQL Server doesn't
support tablespaces such as used in statements like this:
CREATE TABLE ralph.my_table ... IN my_tablespace.
SQL statement syntax
The ANALYZE TABLE,
CHECK TABLE, OPTIMIZE TABLE,
and
REPAIR TABLE statements.
Use of CHANGE
col_name,
DROP
col_name, or
DROP INDEX,
IGNORE
or RENAME in
ALTER TABLE statements. Use of multiple
ADD, ALTER,
DROP, or
CHANGE
clauses in an ALTER TABLE
statement. See
Section 13.1.2, “ALTER TABLE
Syntax”.
You can specify ASC and
DESC with
GROUP BY, not just with ORDER BY.
The ability to set variables in a statement with the
:= assignment operator:
mysql> SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg
-> FROM test_table;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
Data types
The MEDIUMINT,
SET, and ENUM
data types, and the various BLOB
and
TEXT data types.
The AUTO_INCREMENT,
BINARY, NULL,
UNSIGNED, and
ZEROFILL data type attributes.
Functions and operators
To make it easier for users who migrate from other SQL
environments, MySQL Server supports aliases for many functions.
For example, all string functions support both standard SQL
syntax and ODBC syntax.
MySQL Server understands the || and
&& operators to mean logical OR and
AND, as in the C programming language. In MySQL Server,
|| and OR
are synonyms, as are && and
AND. Because of this nice syntax,
MySQL Server doesn't support the standard SQL
|| operator for string
concatenation; use CONCAT()
instead. Because
CONCAT() takes any number of
arguments, it's easy to convert use of the
|| operator to MySQL Server.
Use of COUNT(DISTINCT
value_list)
where
value_list has more
than one element.
String comparisons are case-insensitive by default, with sort
ordering determined by collation of the current character set,
which is latin1
(cp1252 West European) by default. If you don't like this, you
should declare your columns with the
BINARY attribute or use the
BINARY cast, which causes
comparisons to be done using the underlying character code
values rather then a lexical ordering.
The % operator is a synonym for
MOD(). That is,
N
%
M is equivalent
to
MOD(N,M).
% is supported for C programmers
and for compatibility with PostgreSQL.
The =, <>,
<=,<,
>=,>,
<<,
>>,
<=>, AND,
OR, or LIKE
operators may be used in expressions in the output column list
(to the left of the FROM) in
SELECT statements. For example:
There are several differences between the MySQL and standard SQL
privilege systems. For example, in MySQL, privileges for a table are
not automatically revoked when you delete a table. You must
explicitly issue a REVOKE
statement to revoke privileges for a table. For more information,
see
Section 13.5.1.3, “REVOKE Syntax”.
MySQL 4.1 and up supports subqueries and derived tables. A “subquery”
is a SELECT
statement nested within another statement. A “derived
table” (an unnamed view) is a subquery in the
FROM clause of another statement. See
Section 13.2.8, “Subquery Syntax”.
MySQL Server doesn't support the SELECT ... INTO
TABLE Sybase SQL extension. Instead, MySQL Server supports
the INSERT INTO ... SELECT
standard SQL syntax, which is basically the same thing. See
Section 13.2.4.1, “INSERT ... SELECT
Syntax”. For example:
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
Alternatively, you can use SELECT ... INTO
OUTFILE or CREATE TABLE ... SELECT.
1.9.5.3. Transactions and Atomic Operations
MySQL Server (version 3.23-max and all versions 4.0 and above)
supports transactions with the InnoDB
and
BDB transactional storage engines.
InnoDB provides
fullACID compliance. MySQL Cluster is also a
transaction-safe storage engine. See
Chapter 14, Storage Engines and Table Types. For
information about
InnoDB differences from standard SQL with
regard to treatment of transaction errors, see
Section 14.2.16, “InnoDB Error
Handling”.
The other non-transactional storage engines in MySQL Server (such as
MyISAM) follow a different paradigm for data integrity called
“atomic operations.” In transactional
terms,
MyISAM tables effectively always operate
in
AUTOCOMMIT=1 mode. Atomic operations
often offer comparable integrity with higher performance.
Because MySQL Server supports both paradigms, you can decide whether
your applications are best served by the speed of atomic operations
or the use of transactional features. This choice can be made on a
per-table basis.
As noted, the trade-off for transactional versus non-transactional
storage engines lies mostly in performance. Transactional tables
have significantly higher memory and disk space requirements, and
more CPU overhead. On the other hand, transactional storage engines
such as
InnoDB also offer many significant
features. MySQL Server's modular design allows the concurrent use of
different storage engines to suit different requirements and deliver
optimum performance in all situations.
But how do you use the features of MySQL Server to maintain rigorous
integrity even with the non-transactional
MyISAM tables, and how do these features
compare with the transactional storage engines?
If your applications are written in a way that is dependent on
being able to call
ROLLBACK rather than
COMMIT in critical situations,
transactions are more convenient. Transactions also ensure that
unfinished updates or corrupting activities are not committed to
the database; the server is given the opportunity to do an
automatic rollback and your database is saved.
If you use non-transactional tables, MySQL Server in almost all
cases allows you to resolve potential problems by including
simple checks before updates and by running simple scripts that
check the databases for inconsistencies and automatically repair
or warn if such an inconsistency occurs. Note that just by using
the MySQL log or even adding one extra log, you can normally fix
tables perfectly with no data integrity loss.
More often than not, critical transactional updates can be
rewritten to be atomic. Generally speaking, all integrity
problems that transactions solve can be done with
LOCK TABLES or atomic updates,
ensuring that there are no automatic aborts from the server,
which is a common problem with transactional database systems.
To be safe with MySQL Server, regardless of whether you use
transactional tables, you only need to have backups and have
binary logging turned on. When that is true, you can recover
from any situation that you could with any other transactional
database system. It is always good to have backups, regardless
of which database system you use.
The transactional paradigm has its benefits and its drawbacks. Many
users and application developers depend on the ease with which they
can code around problems where an abort appears to be necessary, or
is necessary. However, even if you are new to the atomic operations
paradigm, or more familiar with transactions, do consider the speed
benefit that non-transactional tables can offer on the order of
three to five times the speed of the fastest and most optimally
tuned transactional tables.
In situations where integrity is of highest importance, MySQL Server
offers transaction-level reliability and integrity even for
non-transactional tables. If you lock tables with
LOCK TABLES, all updates stall until
integrity checks are made. If you obtain a
READ LOCAL lock (as opposed to a write lock) for a table that
allows concurrent inserts at the end of the table, reads are
allowed, as are inserts by other clients. The newly inserted records
are not be seen by the client that has the read lock until it
releases the lock. With INSERT DELAYED,
you can write inserts that go into a local queue until the locks are
released, without having the client wait for the insert to complete.
See
Section 7.3.3, “Concurrent Inserts”, and
Section 13.2.4.2, “INSERT DELAYED
Syntax”.
“Atomic,” in the sense that we mean it, is
nothing magical. It only means that you can be sure that while each
specific update is running, no other user can interfere with it, and
there can never be an automatic rollback (which can happen with
transactional tables if you are not very careful). MySQL Server also
guarantees that there are no dirty reads.
Following are some techniques for working with non-transactional
tables:
Loops that need transactions normally can be coded with the help
of LOCK TABLES, and you don't need
cursors to update records on the fly.
To avoid using ROLLBACK, you can
employ the following strategy:
Use LOCK TABLES to lock all the
tables you want to access.
Test the conditions that must be true before performing the
update.
Update if the conditions are satisfied.
Use UNLOCK TABLES to release your
locks.
This is usually a much faster method than using transactions with
possible rollbacks, although not always. The only situation this
solution doesn't handle is when someone kills the threads in the
middle of an update. In that case, all locks are released but
some of the updates may not have been executed.
You can also use functions to update records in a single
operation. You can get a very efficient application by using the
following techniques:
Modify columns relative to their current value.
Update only those columns that actually have changed.
For example, when we are updating customer information, we update
only the customer data that has changed and test only that none
of the changed data, or data that depends on the changed data,
has changed compared to the original row. The test for changed
data is done with the
WHERE clause in the
UPDATE statement. If the record
wasn't updated, we give the client a message: “Some
of the data you have changed has been changed by another user.”
Then we show the old row versus the new row in a window so that
the user can decide which version of the customer record to use.
This gives us something that is similar to column locking but is
actually even better because we only update some of the columns,
using values that are relative to their current values. This
means that typical
UPDATE statements look something like
these:
UPDATE tablename SET pay_back=pay_back+125;
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_owed_to_us=money_owed_to_us-125
WHERE
customer_id=id AND address='old address' AND phone='old phone';
This is very efficient and works even if another client has
changed the values in the pay_back
or money_owed_to_us columns.
In many cases, users have wanted LOCK TABLES
or ROLLBACK for the purpose of
managing unique identifiers. This can be handled much more
efficiently without locking or rolling back by using an
AUTO_INCREMENT column and either the
LAST_INSERT_ID() SQL function or the
mysql_insert_id() C API function. See
Section 12.9.3, “Information Functions”, and
Section 17.2.3.35, “mysql_insert_id()”.
You can generally code around the need for row-level locking. Some
situations really do need it, and
InnoDB tables support row-level
locking. Otherwise, with MyISAM
tables, you can use a flag column in the table and do something
like the following:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of
affected rows if the row was found and
row_flag wasn't
1 in the original row. You can think of this as though
MySQL Server changed the preceding statement to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;
1.9.5.4. Stored Routines and Triggers
Stored procedures and functions are implemented beginning with MySQL
5.0.
Basic trigger functionality is implemented beginning with MySQL 5.0.2,
with further development planned for MySQL 5.1.
1.9.5.5. Foreign Keys
In MySQL Server 3.23.44 and up, the InnoDB
storage engine supports checking of foreign key constraints, including
CASCADE, ON DELETE, and
ON UPDATE. See
Section 14.2.7.4, “FOREIGN KEY
Constraints”.
For storage engines other than InnoDB,
MySQL Server parses the FOREIGN KEY
syntax in CREATE TABLE statements, but
does not use or store it. In the future, the implementation will be
extended to store this information in the table specification file
so that it may be retrieved by
mysqldump and ODBC. At a
later stage, foreign key constraints will be implemented for
MyISAM tables as well.
Foreign key enforcement offers several benefits to database
developers:
Assuming proper design of the relationships, foreign key
constraints make it more difficult for a programmer to introduce
an inconsistency into the database.
Centralized checking of constraints by the database server makes
it unnecessary to perform these checks on the application side.
This eliminates the possibility that different applications may
not all check the constraints in the same way.
Using cascading updates and deletes can simplify the application
code.
Properly designed foreign key rules aid in documenting
relationships between tables.
Do keep in mind that these benefits come at the cost of additional
overhead for the database server to perform the necessary checks.
Additional checking by the server affects performance, which for
some applications may be sufficiently undesirable as to be avoided
if possible. (Some major commercial applications have coded the
foreign key logic at the application level for this reason.)
MySQL gives database developers the choice of which approach to use.
If you don't need foreign keys and want to avoid the overhead
associated with enforcing referential integrity, you can choose
another storage engine instead, such as
MyISAM. (For example, the
MyISAM storage engine offers very fast
performance for applications that perform only
INSERT and SELECT
operations. In this case, the table has no holes in the middle and the
inserts can be performed concurrently with retrievals. See
Section 7.3.3, “Concurrent Inserts”.)
If you choose not to take advantage of referential integrity checks,
keep the following considerations in mind:
In the absence of server-side foreign key relationship checking,
the application itself must handle relationship issues. For
example, it must take care to insert rows into tables in the
proper order, and to avoid creating orphaned child records. It
must also be able to recover from errors that occur in the
middle of multiple-record insert operations.
If ON DELETE is the only referential
integrity capability an application needs, you can achieve a
similar effect as of MySQL Server 4.0 by using multiple-table
DELETE statements to delete rows from many tables with a
single statement. See
Section 13.2.1, “DELETE Syntax”.
A workaround for the lack of ON DELETE
is to add the appropriate DELETE
statements to your application when you delete records from a
table that has a foreign key. In practice, this is often as
quick as using foreign keys and is more portable.
Be aware that the use of foreign keys can sometimes lead to problems:
Foreign key support addresses many referential integrity issues,
but it is still necessary to design key relationships carefully
to avoid circular rules or incorrect combinations of cascading
deletes.
It is not uncommon for a DBA to create a topology of relationships
that makes it difficult to restore individual tables from a
backup. (MySQL alleviates this difficulty by allowing you to
temporarily disable foreign key checks when reloading a table
that depends on other tables. See
Section 14.2.7.4, “FOREIGN KEY
Constraints”. As of MySQL 4.1.1,
mysqldump generates dump files that take
advantage of this capability automatically when they are
reloaded.)
Note that foreign keys in SQL are used to check and enforce
referential integrity, not to join tables. If you want to get
results from multiple tables from a SELECT
statement, you do this by performing a join between them:
The FOREIGN KEY syntax without
ON DELETE ... is often used by ODBC applications to produce
automatic WHERE clauses.
1.9.5.6. Views
Views (including updatable views) are implemented beginning with MySQL
Server 5.0.1.
Views are useful for allowing users to access a set of relations
(tables) as if it were a single table, and limiting their access to
just that. Views can also be used to restrict access to rows (a
subset of a particular table). For access control to columns, you
can also use the sophisticated privilege system in MySQL Server. See
Section 5.7, “The MySQL Access Privilege System”.
In designing an implementation of views, our ambitious goal, as much
as is possible within the confines of SQL, has been full compliance
with “Codd's Rule #6” for relational
database systems: “All views that are
theoretically updatable, should in practice also be updatable.”
1.9.5.7. '--' as the Start of a
Comment
Standard SQL uses the C syntax /* this is a
comment */ for comments, and MySQL Server supports this
syntax as well. MySQL also support extensions to this syntax that
allow MySQL-specific SQL to be embedded in the comment, as described
in
Section 9.4, “Comment Syntax”.
Standard SQL uses ‘--’ as a start-comment
sequence. MySQL Server uses ‘#’ as the
start comment character. MySQL Server 3.23.3 and up also supports a
variant of the ‘--’ comment style. That
is, the ‘--’ start-comment sequence
must be followed by a space (or by a control character such as a
newline). The space is required to prevent problems with
automatically generated SQL queries that use constructs such as the
following, where we automatically insert the value of the payment
for
payment:
UPDATE account SET credit=credit-payment
Consider about what happens if payment
has a negative value such as -1:
UPDATE account SET credit=credit--1
credit--1 is a legal expression in SQL,
but ‘--’ is interpreted as the start of
a comment, part of the expression is discarded. The result is a
statement that has a completely different meaning than intended:
UPDATE account SET credit=credit
The statement produces no change in value at all. This illustrates
that allowing comments to start with ‘--’
can have serious consequences.
Using our implementation requires a space following the ‘--’
in order for it to be recognized as a start-comment sequence in
MySQL Server 3.23.3 and newer. Therefore,
credit--1 is safe to use.
Another safe feature is that the mysql
command-line client ignores lines that start with ‘--’.
The following information is relevant only if you are running a MySQL
version earlier than 3.23.3:
If you have an SQL script in a text file that contains ‘--’
comments, you should use the replace
utility as follows to convert the comments to use ‘#’
characters before executing the script:
MySQL allows you to work both with transactional tables that allow
rollback and with non-transactional tables that do not. Because of this,
constraint handling is a bit different in MySQL than in other DBMSs. We
must handle the case when you have inserted or updated a lot of rows in
a non-transactional table for which changes cannot be rolled back when
an error occurs.
The basic philosophy is that MySQL Server tries to produce an error for
anything that it can detect while parsing a statement to be executed,
and tries to recover from any errors that occur while executing the
statement. We do this in most cases.
The options MySQL has when an error occurs are to stop the statement in
the middle or to recover as well as possible from the problem and
continue. By default, the server follows the latter course. This means,
for example, that the server may coerce illegal values to the closest
legal values.
The following sections describe how MySQL Server handles different types
of constraints.
1.9.6.1. PRIMARY KEY and
UNIQUE Index Constraints
Normally, an error occurs when you try to
INSERT or UPDATE
a row that causes a primary key, unique key, or foreign key
violation. If you are using a transactional storage engine such as
InnoDB, MySQL automatically rolls back the statement. If you
are using a non-transactional storage engine, MySQL stops processing
the statement at the row for which the error occurred and leaves any
remaining rows unprocessed.
If you want to ignore such key violations, MySQL supports an
IGNORE keyword for
INSERT and UPDATE.
In this case, MySQL ignores any key violations and continues
processing with the next row. See
Section 13.2.4, “INSERT Syntax”,
and
Section 13.2.10, “UPDATE Syntax”.
Through version 4.1, MySQL is forgiving of illegal or improper data
values and coerces them to legal values for data entry. When you
insert an “incorrect” value into a
column, such as a NULL into a
NOT NULL column or a too-large numeric value into a numeric
column, MySQL sets the column to the “best
possible value” instead of producing an error. The following
rules describe in more detail how this works:
If you try to store an out of range value into a numeric column,
MySQL Server instead stores zero, the smallest possible value,
or the largest possible value, whichever is closest to the
invalid value.
For strings, MySQL stores either the empty string or as much of
the string as can be stored in the column.
If you try to store a string that doesn't start with a number into
a numeric column, MySQL Server stores 0.
MySQL allows you to store certain incorrect date values into
DATE and
DATETIME columns (such as
'2000-02-31' or
'2000-02-00'). The idea is that it's
not the job of the SQL server to validate dates. If MySQL can
store a date value and retrieve exactly the same value, MySQL
stores it as given. If the date is totally wrong (outside the
server's ability to store it), the special “zero”
date value
'0000-00-00' is stored in the column
instead.
If you try to store NULL into a
column that doesn't take NULL
values, an error occurs for single-row
INSERT
statements. For multiple-row INSERT
statements or for INSERT INTO ... SELECT
statements, MySQL Server stores the implicit default value for
the column data type. In general, this is
0 for numeric types, the empty string ('')
for string types, and the “zero”
value for date and time types. Implicit default values are
discussed in
Section 11.1.4, “Data Type Default Values”.
If an INSERT statement specifies no
value for a column, MySQL inserts its default value if the
column definition includes an explicit
DEFAULT clause. If the definition has
no such DEFAULT clause, MySQL
inserts the implicit default value for the column data type.
The reason for using the preceding rules is that we can't check these
conditions until the statement has begun executing. We can't just
roll back if we encounter a problem after updating a few rows,
because the storage engine may not support rollback. The option of
terminating the statement is not that good; in this case, the update
would be “half done,” which is probably
the worst possible scenario. In this case, it's better to “do
the best you can” and then continue as if nothing happened.
1.9.6.3. ENUM and
SET Constraints
ENUM and SET
columns provide an efficient way to define columns that can contain
only a given set of values. See
Section 11.4.4, “The ENUM Type”,
and
Section 11.4.5, “The SET Type”.
However, in MySQL 4.1 and earlier,
ENUM and SET
columns do not provide true constraints on entry of invalid data:
ENUM columns always have a default
value. If you specify no default value, then it is
NULL for columns that can have
NULL, otherwise it is the first
enumeration value in the column definition.
If you insert an incorrect value into an
ENUM column or if you force a value
into an ENUM column with
IGNORE, it is set to the reserved
enumeration value of 0, which is
displayed as an empty string in string context.
If you insert an incorrect value into a
SET column, the incorrect value is
ignored. For example, if the column can contain the values
'a', 'b',
and
'c', an attempt to assign
'a,x,b,y' results in a value of
'a,b'.