MySQL Standards Compliance
==========================
find
information about functionality missing from MySQL Server, and how to
work around some differences.
Our goal is to not, without a very good reason, restrict MySQL Server
usability for any usage. Even if we don't have the resources to do
development for every possible use, we are always willing to help and
offer suggestions to people who are trying to use MySQL Server in new
territories.
One of our main goals with the product is to continue to work toward
compliance with the SQL-99 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 big part of our users. (The new `HANDLER' interface in
MySQL Server 4.0 is an example of this strategy. *Note `HANDLER':
HANDLER.)
We will continue to support transactional and non-transactional
databases to satisfy both heavy web/logging usage and mission-critical
24/7 usage.
MySQL Server was designed from the start to work with medium size
databases (10-100 million rows, or about 100 MB per table) on small
computer systems. We will continue to extend MySQL Server to work even
better with terabyte-size databases, as well as to make it possible to
compile a reduced MySQL version that is more suitable for hand-held
devices and embedded usage. The compact design of the MySQL server
makes both of these directions possible without any conflicts in the
source tree.
We are currently not targeting realtime support or clustered databases
(even if you can already do a lot of things with our replication
services).
We are looking at providing XML support in the database server.
What Standards Does MySQL Follow?
---------------------------------
Entry-level SQL-92. ODBC levels 0-3.51.
We are aiming toward supporting the full SQL-99 standard, but without
concessions to speed and quality of the code.
Running MySQL in ANSI Mode
--------------------------
If you start `mysqld' with the `--ansi' or `--sql-mode=ANSI' option,
the following behaviours of MySQL Server change:
* `||' is a string concatenation operator rather than a synonym for
`OR'.
implication of this is that you cannot use double quotes to quote
a literal string, because it will be intepreted as an identifier.
* You can have any number of spaces between a function name and the
`(' character. This forces all function names to be treated as
reserved words. As a result, if you want to access any database,
table, or column name that is a reserved word, you must quote it.
For example, because there is a `USER()' function, the name of the
`user' table in the `mysql' database and the `User' column in that
table become reserved, so you must quote them:
SELECT "User" FROM mysql."user";
* `REAL' is a synonym for `FLOAT' instead of a synonym for `DOUBLE'.
* The default transaction isolation level is `SERIALIZABLE'. *Note
`SET TRANSACTION': SET TRANSACTION.
* You can use a field/expression in `GROUP BY' that is not in the
field list.
Running the server in ANSI mode is the same as starting it with these
options:
--sql-mode=REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY
--transaction-isolation=SERIALIZABLE
In MySQL 4.1, you can achieve the same effect with these two statements:
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL sql_mode =
"REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY";
In MySQL 4.1.1, the `sql_mode' options shown can be also be set with:
SET GLOBAL sql_mode="ansi";
In this case, the value of the `sql_mode' variable will be set to all
options that are relevant for ANSI mode. You can check the result by
doing:
mysql> SET GLOBAL sql_mode="ansi";
mysql> SELECT @@GLOBAL.sql_mode;
-> "REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI"
MySQL Extensions to the SQL-92 Standard
---------------------------------------
can write
code that includes MySQL extensions, but is still portable, by using
comments of the form `/*! ... */'. In this case, MySQL Server will
parse and execute the code within the comment as it would any other
MySQL statement, but other SQL servers will ignore the extensions. For
example:
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
If you add a version number after the `'!'', the syntax will be
executed only if the MySQL version is equal to or newer than the used
version number:
CREATE /*!32302 TEMPORARY */ TABLE t (a INT);
This means that if you have Version 3.23.02 or newer, MySQL Server will
use the `TEMPORARY' keyword.
The following is a list of MySQL extensions:
* The field types `MEDIUMINT', `SET', `ENUM', and the different
`BLOB' and `TEXT' types.
* The field attributes `AUTO_INCREMENT', `BINARY', `NULL',
`UNSIGNED', and `ZEROFILL'.
* All string comparisons are case-insensitive by default, with sort
ordering determined by the current character set (ISO-8859-1
Latin1 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 according to the ASCII order
used on the MySQL server host.
* MySQL Server maps each database to a directory under the MySQL
data directory, and tables within a database to filenames in the
database directory.
This has a few implications:
- Database, table, index, column, or alias names may begin with
a digit (but may not consist solely of digits).
- You can use standard system commands to back up, rename,
move, delete, and copy tables. For example, to rename a
table, rename the `.MYD', `.MYI', and `.frm' files to which
the table corresponds.
* 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 as in: `create table
ralph.my_table...IN my_tablespace'.
* `LIKE' is allowed on numeric columns.
* Use of `INTO OUTFILE' and `STRAIGHT_JOIN' in a `SELECT' statement.
*Note `SELECT': SELECT.
* The `SQL_SMALL_RESULT' option in a `SELECT' statement.
* `EXPLAIN SELECT' to get a description of how tables are joined.
* Use of index names, indexes on a prefix of a field, and use of
`INDEX' or `KEY' in a `CREATE TABLE' statement. *Note `CREATE
TABLE': CREATE TABLE.
* Use of `TEMPORARY' or `IF NOT EXISTS' with `CREATE TABLE'.
* Use of `COUNT(DISTINCT list)' where `list' has more than one
element.
* Use of `CHANGE col_name', `DROP col_name', or `DROP INDEX',
`IGNORE' or `RENAME' in an `ALTER TABLE' statement. *Note `ALTER
TABLE': ALTER TABLE.
* Use of `RENAME TABLE'. *Note `RENAME TABLE': RENAME TABLE.
* Use of multiple `ADD', `ALTER', `DROP', or `CHANGE' clauses in an
`ALTER TABLE' statement.
* Use of `DROP TABLE' with the keywords `IF EXISTS'.
* You can drop multiple tables with a single `DROP TABLE' statement.
* The `ORDER BY' and `LIMIT' clauses of the `UPDATE' and `DELETE'
statements.
* `INSERT INTO ... SET col_name = ...' syntax.
* The `DELAYED' clause of the `INSERT' and `REPLACE' statements.
* The `LOW_PRIORITY' clause of the `INSERT', `REPLACE', `DELETE',
and `UPDATE' statements.
* Use of `LOAD DATA INFILE'. In many cases, this syntax is
compatible with Oracle's `LOAD DATA INFILE'. *Note `LOAD DATA':
LOAD DATA.
* The `ANALYZE TABLE', `CHECK TABLE', `OPTIMIZE TABLE', and `REPAIR
TABLE' statements.
* The `SHOW' statement. *Note `SHOW': SHOW.
* Strings may be enclosed by either `"' or `'', not just by `''.
* Use of the escape `\' character.
* The `SET' statement. *Note `SET': SET OPTION.
* One can specify `ASC' and `DESC' with `GROUP BY'.
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-99 `||' 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.
* `CREATE DATABASE' or `DROP DATABASE'. *Note `CREATE DATABASE':
CREATE DATABASE.
* 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 column comparisons to the
left of the `FROM' in `SELECT' statements. For example:
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
* The `LAST_INSERT_ID()' function. *Note `mysql_insert_id()':
mysql_insert_id.
* The `REGEXP' and `NOT REGEXP' extended regular expression
operators.
* `CONCAT()' or `CHAR()' with one argument or more than two
arguments. (In MySQL Server, these functions can take any number
of arguments.)
* The `BIT_COUNT()', `CASE', `ELT()', `FROM_DAYS()', `FORMAT()',
`IF()', `PASSWORD()', `ENCRYPT()', `MD5()', `ENCODE()', `DECODE()',
`PERIOD_ADD()', `PERIOD_DIFF()', `TO_DAYS()', or `WEEKDAY()'
functions.
* Use of `TRIM()' to trim substrings. SQL-99 supports removal of
single characters only.
* The `GROUP BY' functions `STD()', `BIT_OR()', `BIT_AND()',
`BIT_XOR()', and `GROUP_CONCAT()'. *Note Group by functions::.
* Use of `REPLACE' instead of `DELETE' + `INSERT'. *Note `REPLACE':
REPLACE.
* The `FLUSH', `RESET' and `DO' statements.
* The ability to set variables in a statement with `:=':
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
MySQL Differences Compared to SQL-92
------------------------------------
We try to make MySQL Server follow the ANSI SQL standard
(SQL-92/SQL-99) and the ODBC SQL standard, but in some cases MySQL
Server does things differently:
* For `VARCHAR' columns, trailing spaces are removed when the value
is stored. *Note Bugs::.
* In some cases, `CHAR' columns are silently changed to `VARCHAR'
columns. *Note Silent column changes::.
* Privileges for a table are not automatically revoked when you
delete a table. You must explicitly issue a `REVOKE' to revoke
privileges for a table. *Note `GRANT': GRANT.
For a prioritised list indicating when new extensions will be added to
MySQL Server, you should consult the online MySQL TODO list at
`http://www.mysql.com/doc/en/TODO.html'. That is the latest version of
the TODO list in this manual. *Note TODO::.
Subqueries
..........
Subqueries are supported in MySQL version 4.1. *Note Nutshell 4.1
features::.
Up to version 4.0, only nested queries of the form `INSERT ... SELECT
...' and `REPLACE ... SELECT ...' are supported. You can, however, use
the function `IN()' in other contexts.
You can often rewrite the query without a subquery:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
Can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
Using a `LEFT [OUTER] JOIN' is generally much faster than an equivalent
subquery because the server can optimise it better, a fact that is not
specific to MySQL Server alone. Prior to SQL-92, outer joins did not
exist, so subqueries were the only way to do certain things in those
bygone days. But that is no longer the case, MySQL Server and many
other modern database systems offer a whole range of outer joins types.
For more complicated subqueries you can often create temporary tables
to hold the subquery. In some cases, however, this option will not
work. The most frequently encountered of these cases arises with
`DELETE' statements, for which standard SQL does not support joins
(except in subqueries). For this situation there are three options
available:
* The first option is to upgrade to MySQL version 4.1.
* The second option is to use a procedural programming language
(such as Perl or PHP) to submit a `SELECT' query to obtain the
primary keys for the records to be deleted, and then use these
values to construct the `DELETE' statement (`DELETE FROM ... WHERE
... IN (key1, key2, ...)').
* The third option is to use interactive SQL to construct a set of
`DELETE' statements automatically, using the MySQL extension
`CONCAT()' (in lieu of the standard `||' operator). For example:
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from
it to the `mysql' command-line interpreter, piping its output back
to a second instance of the interpreter:
shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL Server 4.0 supports multi-table `DELETE's that can be used to
efficiently delete rows based on information from one table or even
from many tables at the same time.
`SELECT INTO TABLE'
...................
MySQL Server doesn't yet support the Oracle SQL extension: `SELECT ...
INTO TABLE ...'. Instead, MySQL Server supports the SQL-99 syntax
`INSERT INTO ... SELECT ...', which is basically the same thing. *Note
INSERT SELECT::.
INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;
Alternatively, you can use `SELECT INTO OUTFILE...' or `CREATE TABLE
... SELECT'.
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 _full_ `ACID' compliance. *Note Table
types::.
The other non-transactional table types (such as `MyISAM') in MySQL
Server 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.
With MySQL Server supporting both paradigms, the user is able to decide
if he needs the speed of atomic operations or if he needs to use
transactional features in his applications. This choice can be made on
a per-table basis.
As noted, the trade off for transactional vs. non-transactional table
types lies mostly in performance. Transactional tables have
significantly higher memory and diskspace requirements, and more CPU
overhead. That said, transactional table types such as `InnoDB' do of
course offer many unique features. MySQL Server's modular design allows
the concurrent use of all these different storage engines to suit
different requirements and deliver optimum performance in all
situations.
But how does one 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 table types?
1. In the transactional paradigm, if your applications are written in
a way that is dependent on the calling of `ROLLBACK' instead of
`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.
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, one
can normally fix tables perfectly with no data integrity loss.
2. 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 you never will get an automatic abort from
the server, which is a common problem with transactional database
systems.
3. Even a transactional system can lose data if the server goes down.
The difference between different systems lies in just how small the
time-lap is where they could lose data. No system is 100% secure,
only "secure enough." Even Oracle, reputed to be the safest of
transactional database systems, is reported to sometimes lose data
in such situations.
To be safe with MySQL Server, whether using transactional tables
or not, you only need to have backups and have the binary logging
turned on. With this you can recover from any situation that you
could with any other transactional database system. It is, of
course, always good to have backups, independent of which database
system you use.
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.
updates will stall until any integrity checks are made. If you only
obtain a read lock (as opposed to a write lock), reads and inserts are
still allowed to happen. The new inserted records will not be seen by
any of the clients that have a read lock until they release their read
locks. With `INSERT DELAYED' you can queue inserts into a local queue,
until the locks are released, without having the client wait for the
insert to complete. *Note INSERT DELAYED::.
"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 will never be an
automatic rollback (which can happen with transactional tables if you
are not very careful). MySQL Server also guarantees that there will
not be any 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 when you can update
records on the fly.
* To avoid using `ROLLBACK', you can use the following strategy:
1. Use `LOCK TABLES ...' to lock all the tables you want to
access.
2. Test conditions.
3. Update if everything is okay.
4. Use `UNLOCK TABLES' to release your locks.
This is usually a much faster method than using transactions with
possible `ROLLBACK's, although not always. The only situation this
solution doesn't handle is when someone kills the threads in the
middle of an update. In this case, all locks will be 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 fields relative to their current value.
* Update only those fields that actually have changed.
For example, when we are doing updates to some 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 the user can decide which version of the
customer record he should 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_he_owes_us=money_he_owes_us-125
WHERE
customer_id=id AND address='old address' AND phone='old phone';
As you can see, this is very efficient and works even if another
client has changed the values in the `pay_back' or
`money_he_owes_us' columns.
`AUTO_INCREMENT' column and either the SQL function
`LAST_INSERT_ID()' or the C API function `mysql_insert_id()'.
*Note `mysql_insert_id()': mysql_insert_id.
You can generally code around row-level locking. Some situations
really need it, but they are very few. `InnoDB' tables support
row-level locking. With MyISAM, 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 already 1 in the original row.
You can think of it as though MySQL Server changed the preceding
query to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;
Stored Procedures and Triggers
..............................
Stored procedures are being implemented in our version 5.0 development
tree. *Note Installing source tree::.
procedure.
This provides better overall performance because the query has to be
parsed only once, and less information needs to be sent between the
server and the client. You can also raise the conceptual level by
having libraries of functions in the server. However, stored
procedures of course do increase the load on the database server
system, as more of the work is done on the server side and less on the
client (application) side.
Triggers will also be implemented. A trigger is effectively a type of
stored procedure, one that is invoked when a particular event occurs.
For example, you can install a stored procedure that is triggered each
time a record is deleted from a transactional table and that stored
procedure automatically deletes the corresponding customer from a
customer table when all his transactions are deleted.
Foreign Keys
............
In MySQL Server 3.23.44 and up, `InnoDB' tables support checking of
foreign key constraints, including `CASCADE', `ON DELETE', and `ON
UPDATE'. *Note InnoDB foreign key constraints::.
For other table types, MySQL Server only parses the `FOREIGN KEY'
syntax in `CREATE TABLE' commands, but does not use/store this info.
Note that foreign keys in SQL are not used to join tables, but are used
mostly for checking referential integrity (foreign key constraints). If
you want to get results from multiple tables from a `SELECT' statement,
you do this by joining tables:
SELECT * FROM table1,table2 WHERE table1.id = table2.id;
*Note `JOIN': JOIN. *Note example-Foreign keys::.
When used as a constraint, `FOREIGN KEY's don't need to be used if the
application inserts rows into `MyISAM' tables in the proper order.
For `MyISAM' tables, you can work around the lack of `ON DELETE' by
adding the appropriate `DELETE' statement to an application when you
delete records from a table that has a foreign key. In practice this is
as quick (in some cases quicker) and much more portable than using
foreign keys.
In MySQL Server 4.0 you can use multi-table delete to delete rows from
many tables with one command. *Note DELETE::.
The `FOREIGN KEY' syntax without `ON DELETE ...' is often used by ODBC
applications to produce automatic `WHERE' clauses.
In the near future we will extend the `FOREIGN KEY' implementation so
that the information is stored in the table specification file and may
be retrieved by `mysqldump' and ODBC. At a later stage we will
implement foreign key constraints for `MyISAM' tables as well.
Do keep in mind that foreign keys are often misused, which can cause
severe problems. Even when used properly, it is not a magic solution for
the referential integrity problem, although it can make things easier.
Some advantages of foreign key enforcement:
* Assuming proper design of the relations, foreign key constraints
will make it more difficult for a programmer to introduce an
inconsistency into the database.
* Using cascading updates and deletes can simplify the client code.
* Properly designed foreign key rules aid in documenting relations
between tables.
Disadvantages:
* Mistakes, which are easy to make in designing key relations, can
cause severe problems--for example, circular rules, or the wrong
combination of cascading deletes.
* Additional checking on the database level affects performance, for
this reason some major commercial applications have coded this
logic on the application level.
* It is not uncommon for a DBA to make such a complex topology of
relations that it becomes very difficult, and in some cases
impossible, to back up or restore individual tables.
Views
.....
We plan to implement views in MySQL Server in version 5.1
that an
increasing number of users now regard views as an important aspect.
the
updates on the individual tables.
Views can also be used to restrict access to rows (a subset of a
particular table). One does not need views to restrict access to
columns, as MySQL Server has a sophisticated privilege system. *Note
Privilege system::.
In designing our implementation of views, we aim toward (as fully as
possible within the confines of SQL) compliance with "*Codd's Rule #6*"
for relational database systems: all views that are theoretically
updatable, should in practice also be updatable. This is a complex
issue, and we are taking the time to make sure we get it right.
The implementation itself will be done in stages. Unnamed views
(_derived tables_, a subquery in the `FROM' clause of a `SELECT') are
already implemented in version 4.1.
*Note*: If you are an enterprise level user with an urgent need for
views, please contact to discuss sponsoring options.
Targeted financing of this particular effort by one or more companies
would allow us to allocate additional resources to it. One example of
a feature sponsored in the past is replication.
`--' as the Start of a Comment
..............................
caused many
problems with automatically generated SQL queries that have used
something like the following code, where we automatically insert the
value of the payment for `!payment!':
UPDATE tbl_name SET credit=credit-!payment!
Think about what happens if the value of `payment' is negative.
Because `1--1' is legal in SQL, the consequences of allowing comments
to start with `--' are terrible.
Using our implementation of this method of commenting in MySQL Server
Version 3.23.3 and up, `1-- This is a comment' is actually safe.
Another safe feature is that the `mysql' command-line client removes
all 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 program in a text file that contains `--' comments
you should use:
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
instead of the usual:
shell> mysql database < text-file-with-funny-comments.sql
You can also edit the command file "in place" to change the `--'
comments to `#' comments:
shell> replace " --" " #" -- text-file-with-funny-comments.sql
Change them back with this command:
shell> replace " #" " --" -- text-file-with-funny-comments.sql
How MySQL deals with constraints
--------------------------------
As MySQL allows you to work with both transactional and
non-transactional tables (which don't allow rollback), constraint
handling is a bit different in MySQL than in other databases.
We have to handle the case when you have updated a lot of rows with a
non-transactional table which can't rollback on errors.
TODO
future::.
The basic options MySQL has is to stop the statement in the middle or do
it's best to recover from the problem and continue.
Here follows what happens with the different types of constraints.
Constraint PRIMARY KEY / UNIQUE
...............................
Normally you will get an error when you try to `INSERT' / `UPDATE' a
row that causes a primary key, unique key or foreign key violation. If
you are using a transactional storage engine, like InnoDB, MySQL will
automatically roll back the transaction. If you are using a
non-transactional storage engine MySQL will stop at the wrong row and
leave the rest of the rows unprocessed.
To make life easier MySQL has added support for the `IGNORE' directive
to most commands that can cause a key violation (like `INSERT IGNORE
...'). In this case MySQL will ignore any key violation and continue
with processing the next row. You can get information of what MySQL
did with the `mysql_info()' API function and in later MySQL 4.1 version
with the `SHOW WARNINGS' command. *Note mysql_info::. *Note SHOW
WARNINGS::.
Note that for the moment only `InnoDB' tables support foreign keys.
*Note InnoDB foreign key constraints::. Foreign key support in
`MyISAM' tables is scheduled for inclusion in the MySQL 5.0 source tree.
Constraint `NOT NULL' and `DEFAULT' values
..........................................
To be able to support easy handling of non-transactional tables all
fields in MySQL have default values.
If you insert a 'wrong' value in a column like a `NULL' in a `NOT NULL'
column or a too big numerical value in a numerical column, MySQL will
instead of giving an error instead set the column to the 'best possible
value'. For numerical values this is 0, the smallest possible values
or the largest possible value. For strings this is either the empty
string or the longest possible string that can be in the column.
This means that if you try to store `NULL' into a column that doesn't
take `NULL' values, MySQL Server will store 0 or `''' (empty string) in
it instead. This last behaviour can, for single row inserts, be changed
with the `-DDONT_USE_DEFAULT_FIELDS' compile option.) *Note configure
options::. This causes `INSERT' statements to generate an error unless
you explicitly specify values for all columns that require a non-`NULL'
value.
The reason for the above rules is that we can't check these conditions
before the query starts to execute. If we encounter a problem after
updating a few rows, we can't just rollback as the table type may not
support this. The option to stop is not that good as 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. In MySQL 5.0 we plan to improve this
by providing warnings for automatic field conversions, plus an option
to let you roll back statements that only use transactional tables in
case one such statement does a field assignment that is not allowed.
The above means that one should generally not use MySQL to check field
content, but instead handle this in the application.
Constraint `ENUM' and `SET'
...........................
In MySQL 4.x `ENUM' is not a real constrain but a more efficient way to
store fields that can only contain a given set of values. This is
because of the same reasons `NOT NULL' is not honoured. *Note
constraint NOT NULL::.
If you insert an wrong value in an `ENUM' field, it will be set to the
reserved enum number `0', which will be displayed as an empty string in
string context. *Note ENUM::.
If you insert an wrong option in a `SET' field, the wrong value will be
ignored. *Note SET::.
Known Errors and Design Deficiencies in MySQL
---------------------------------------------
Errors in 3.23 Fixed in a Later MySQL Version
.............................................
The following known errors/bugs are not fixed in MySQL 3.23 because
fixing them would involves changing a lot of code which could introduce
other even worse bugs. The bugs are also classified as 'not fatal' or
'bearable'.
* One can get a deadlock when doing `LOCK TABLE' on multiple tables
and then in the same connection doing a `DROP TABLE' on one of
them while another thread is trying to lock the table. One can
however do a `KILL' on any of the involved threads to resolve this.
Fixed in 4.0.12.
* `SELECT MAX(key_column) FROM t1,t2,t3...' where one of the tables
are empty doesn't return `NULL' but instead the maximum value for
the column. Fixed in 4.0.11.
* `DELETE FROM heap_table' without a `WHERE' doesn't work on a locked
HEAP table.
Open Bugs / Design Deficiencies in MySQL
........................................
The following problems are known and fixing them is a high priority:
* `FLUSH TABLES WITH READ LOCK' does not block `CREATE TABLE' or
`COMMIT', which make cause a problem with the binary log position
when doing a full backup of tables and the binary log.
* `ANALYZE TABLE' on a BDB table may in some cases make the table
unusable until one has restarted `mysqld'. When this happens you
will see errors like the following in the MySQL error file:
001207 22:07:56 bdb: log_flush: LSN past current end-of-log
`FROM'
part even where they are not needed.
* Concatenating many `RIGHT JOINS' or combining `LEFT' and `RIGHT'
join in the same query may not give a correct answer as MySQL only
generates `NULL' rows for the table preceding a `LEFT' or before a
`RIGHT' join. This will be fixed in 5.0 at the same time we add
support for parentheses in the `FROM' part.
* Don't execute `ALTER TABLE' on a `BDB' table on which you are
running multi-statement transactions until all those transactions
complete. (The transaction will probably be ignored.)
* `ANALYZE TABLE', `OPTIMIZE TABLE', and `REPAIR TABLE' may cause
problems on tables for which you are using `INSERT DELAYED'.
* Doing a `LOCK TABLE ...' and `FLUSH TABLES ...' doesn't guarantee
that there isn't a half-finished transaction in progress on the
table.
you are using
`rehash'. This is especially notable when you have a big table
cache.
* Replication uses query-level logging: the master writes the
executed queries to the binary log. This is a very fast, compact,
and efficient logging method that works perfectly in most cases.
Though we have never heard of it actually occurring, it is
theoretically possible for the data on the master and slave to
become different if a query is designed in such a way that the
data modification is non-deterministic, that is, left to the will
of the query optimiser (which generally is not a good practice,
even outside of replication!). For example:
- `CREATE ... SELECT' or `INSERT ... SELECT' which feeds zeros
or `NULL's into an `auto_increment' column.
- `DELETE' if you are deleting rows from a table which has
foreign keys with `ON DELETE CASCADE' properties.
a deterministic order*.
Indeed, for example for `INSERT ... SELECT' with no `ORDER BY',
the `SELECT' may return rows in a different order (which will
result in a row having different ranks, hence getting a different
number in the `auto_increment' column), depending on the choices
made by the optimisers on the master and slave. A query will be
optimised differently on the master and slave only if:
- The files used by the two queries are not exactly the same;
for example `OPTIMIZE TABLE' was run on the master tables and
not on the slave tables (to fix this, since MySQL 4.1.1,
`OPTIMIZE', `ANALYZE' and `REPAIR' are written to the binary
log).
- The table is stored in a different storage engine on the
master than on the slave (one can run with different storage
engines on the slave and master: for example InnoDB on the
master and MyISAM on the slave, if the slave has less
available disk space).
- The MySQL buffers' sizes (`key_buffer_size' etc) are
different on the master and slave.
- The master and slave run different MySQL versions, and the
optimiser code is different between these versions.
This problem may also affect database restoration using
`mysqlbinlog|mysql'.
MySQL versions we will automatically add an `ORDER BY' clause when
needed.
The following problems are known and will be fixed in due time:
* `LIKE' is not `multi-byte character' safe. Comparison is done
character by character.
* When using `RPAD' function, or any other string function that ends
up adding blanks to the right, in a query that has to use temporary
table to be resolved, then all resulting strings will be RTRIM'ed.
This is an example of the query:
`SELECT RPAD(t1.field1, 50, ' ') AS f2, RPAD(t2.field2, 50, '
') AS f1 FROM table1 as t1 LEFT JOIN table2 AS t2 ON
t1.record=t2.joinID ORDER BY t2.record;'
Final result of this bug is that use will not be able to get
blanks on the right side of the resulting field.
The above behaviour exists in all versions of MySQL.
The reason for this is due to the fact that HEAP tables, which are
used first for temporary tables, are not capable of handling
VARCHAR columns.
This behaviour will be fixed in one of the 4.1 series releases.
* Because of how table definitions files are stored one can't use
character 255 (`CHAR(255)') in table names, column names or enums.
This is scheduled to be fixed in version 5.1 when we have new table
definition format files.
* When using `SET CHARACTER SET', one can't use translated
characters in database, table, and column names.
* One can't use `_' or `%' with `ESCAPE' in `LIKE ... ESCAPE'.
* If you have a `DECIMAL' column with a number stored in different
formats (+01.00, 1.00, 01.00), `GROUP BY' may regard each value as
a different value.
* `DELETE FROM merge_table' used without a `WHERE' will only clear
the mapping for the table, not delete everything in the mapped
tables.
* You cannot build the server in another directory when using
MIT-pthreads. Because this requires changes to MIT-pthreads, we
are not likely to fix this. *Note MIT-pthreads::.
can be
changed with the `-O max_sort_length' option to `mysqld'. A
workaround for most cases is to use a substring: `SELECT DISTINCT
LEFT(blob,2048) FROM tbl_name'.
* Calculation is done with `BIGINT' or `DOUBLE' (both are normally
64 bits long). It depends on the function which precision one
gets. The general rule is that bit functions are done with `BIGINT'
precision, `IF', and `ELT()' with `BIGINT' or `DOUBLE' precision
and the rest with `DOUBLE' precision. One should try to avoid
using unsigned long long values if they resolve to be bigger than
63 bits (9223372036854775807) for anything else than bit fields.
MySQL Server 4.0 has better `BIGINT' handling than 3.23.
* All string columns, except `BLOB' and `TEXT' columns, automatically
have all trailing spaces removed when retrieved. For `CHAR' types
this is okay, and may be regarded as a feature according to
SQL-92. The bug is that in MySQL Server, `VARCHAR' columns are
treated the same way.
* You can only have up to 255 `ENUM' and `SET' columns in one table.
* In `MIN()', `MAX()' and other aggregate functions, MySQL currently
compares `ENUM' and `SET' columns by their string value rather
than by the string's relative position in the set.
* `mysqld_safe' redirects all messages from `mysqld' to the `mysqld'
log. One problem with this is that if you execute `mysqladmin
refresh' to close and reopen the log, `stdout' and `stderr' are
still redirected to the old log. If you use `--log' extensively,
you should edit `mysqld_safe' to log to `'hostname'.err' instead
of `'hostname'.log' so you can easily reclaim the space for the
old log by deleting the old one and executing `mysqladmin refresh'.
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
This will update `KEY' with `2' instead of with `1'.
mysql> SELECT * FROM temporary_table, temporary_table AS t2;
* `RENAME' doesn't work with `TEMPORARY' tables or tables used in a
`MERGE' table.
* The optimiser may handle `DISTINCT' differently if you are using
'hidden' columns in a join or not. In a join, hidden columns are
counted as part of the result (even if they are not shown) while in
normal queries hidden columns don't participate in the `DISTINCT'
comparison. We will probably change this in the future to never
compare the hidden columns when executing `DISTINCT'.
An example of this is:
SELECT DISTINCT mp3id FROM band_downloads
WHERE userid = 9 ORDER BY id DESC;
and
ORDER BY band_downloads.id DESC;
In the second case you may in MySQL Server 3.23.x get two
identical rows in the result set (because the hidden `id' column
may differ).
Note that this happens only for queries where you don't have the
ORDER BY columns in the result, something that you are not allowed
to do in SQL-92.
* Because MySQL Server allows you to work with table types that don't
support transactions, and thus can't `rollback' data, some things
behave a little differently in MySQL Server than in other SQL
servers. This is just to ensure that MySQL Server never needs to
do a rollback for an SQL command. This may be a little awkward at
times as column values must be checked in the application, but
this will actually give you a nice speed increase as it allows
MySQL Server to do some optimisations that otherwise would be very
hard to do.
If you set a column to an incorrect value, MySQL Server will,
instead of doing a rollback, store the `best possible value' in
the column:
- If you try to store a value outside the range in a numerical
column, MySQL Server will instead store the smallest or
biggest possible value in the column.
- If you try to store a string that doesn't start with a number
into a numerical column, MySQL Server will store 0 into it.
changed with the -DDONT_USE_DEFAULT_FIELDS compile option.)
- MySQL allows you to store some wrong date values into `DATE'
and `DATETIME' columns (like 2000-02-31 or 2000-02-00). The
idea is that it's not the SQL server job to validate date.
If MySQL can store a date and retrieve exactly the same date,
then MySQL will store the date. If the date is totally wrong
(outside the server's ability to store it), then the special
date value 0000-00-00 will be stored in the column.
- If you set an `ENUM' column to an unsupported value, it will
be set to the error value `empty string', with numeric value
0.
- If you set a `SET' column to an unsupported value, the value
will be ignored.
* If you execute a `PROCEDURE' on a query that returns an empty set,
in some cases the `PROCEDURE' will not transform the columns.
* Creation of a table of type `MERGE' doesn't check if the underlying
tables are of compatible types.
* MySQL Server can't yet handle `NaN', `-Inf', and `Inf' values in
double. Using these will cause problems when trying to export and
import data. We should as an intermediate solution change `NaN' to
`NULL' (if possible) and `-Inf' and `Inf' to the minimum
respective maximum possible `double' value.
* `LIMIT' on negative numbers are treated as big positive numbers.
* If you use `ALTER TABLE' to first add a `UNIQUE' index to a table
used in a `MERGE' table and then use `ALTER TABLE' to add a normal
index on the `MERGE' table, the key order will be different for
the tables if there was an old key that was not unique in the
table. This is because `ALTER TABLE' puts `UNIQUE' keys before
normal keys to be able to detect duplicate keys as early as
possible.
The following are known bugs in earlier versions of MySQL:
* You can get a hung thread if you do a `DROP TABLE' on a table that
is one among many tables that is locked with `LOCK TABLES'.
* In the following case you can get a core dump:
- Delayed insert handler has pending inserts to a table.
- `LOCK table' with `WRITE'.
- `FLUSH TABLES'.
found
multiple times:
UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100;
A workaround is to use:
mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;
This will work because MySQL Server will not use an index on
expressions in the `WHERE' clause.
returned with
the correct number of decimals.
For platform-specific bugs, see the sections about compiling and
porting.
[Назад] [Содержание] [Вперед]
| Главная |