Frequently Asked Questions about MySQL. $Revision$
This document contains answers to some of the most frequently asked
questions about MySQL. They're all good questions, but they come
up often enough that substantial net bandwidth can be saved by looking
here first before asking.
The latest version of this FAQ can be found at http://www.tcx.se/
This FAQ is currently available in TeXInfo, ASCII, Info, Postscript and HTML versions.
The primary document is the TeXInfo file. The HTML version is
automatically produced with texi2html. The ASCII and info version is
produced using makeinfo. The Postscript version is produced using
texi2dvi and divps.
If you have any suggested additions or corrections, please send them to
the MySQL mailing list <mysql@tcx.se> with a subject line of the
form FAQ Suggestion: [Insert Topic Here]. See section Subscribing to/un-subscribing from the MySQL mailing list..
This FAQ is written and maintained by David Axmark and Michael (monty) Widenius.
MySQL mailing list.
Requests to be added or dropped from the MySQL list should be sent to
the electronic mail address mdomo@tcx.se. Sending a one line
message saying either subscribe mysql or un-subscribe mysql
will suffice. If your reply address is not valid you may use subscribe
mysql your@address.your-domain or un-subscribe mysql
your@address.your-domain.
Please do not send mail about [un]subscribing to
mysql@tcx.se since any mail sent to this address is
automatically forwarded to hundreds of other users.
Your local site may have many subscribers to MySQL. In that case, it
may have a local mailing list, so that a single message from tcx.se
is sent to the site and propagated to the local list. In this case please
contact your system administrator to be added to or dropped from the local
MySQL list.
Mail to mdomo is handled automatically by majordomo.
Before you ask a question of the mailing list it's a good idea to check
in the manual. If you can't find an answer in the manual, check with your
local MySQL expert. If you don't have any luck there, read through this
FAQ. If you still don't have an answer to your question go ahead and send
mail to mysql@tcx.se.
I think I have found a bug. What information do you need to help me?
If you can use the mysqlbug script that you can find in the
scripts directory in the distribution. Othervise remember to specify (if
relevant):
MySQL. You can find out which version you are
running by typing mysqladmin version. If you know which distribution
file file you used add that (for example mysql-3.20.0.tgz).
uname -a.
When answers are sent to you individually and not to the mailing list, it is considered good etiquette to summarize the answers and mail them to the mailing list.
Try to make your answer broad enough that people other than the original poster may benefit from it. If you consider your answer to have broad interest, you may want to post it to the mailing list instead of replying directly to the individual who asked. In this case, 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, but don't feel obliged to quote the whole question.
MySQL?
MySQL is a SQL (Structured Query Language) database
server. MySQL was written by Michael (monty) Widenius. Se the CREDITS
file in the distribution for more credits for MySQL and related
things.
MySQLOur first intension was to use mSQL to connect to our own fast low level (ISAM) tables. After some testing we came to the conclusion that mSQL wasn't fast or flexible enough. This resulted in a new SQL interface to our database but with almost the same API interface as mSQL. This API was choosed to encourage code sharing.
Where the name MySQL comes from is not perfectly clear. Our base directory and lots of librarys and tools has had the prefix my for well over 10 years. But Monty's daughter (some years younger) are also named My. So there the names comes from is not perfectly clear.
MySQL version?You can always check http://www.tcx.se/ for the latest version of MySQL.
MySQL?On the Internet try using a web browser to http://www.tcx.se/
Or ftp to ftp://ftp.sunet.se/pub/unix/databases/relational/mysql/. That isSunet's (Swedish University Network) FTP archive in Sweden.
MySQL in source form or binary?
If you want to read (and/or modify) the C and C++ code that makes up
MySQL you should always get a source distribution. The code is
always the ultimate manual. The source distribution also contains more tests
and examples.
For most people who want to run MySQL on a platform that has binary
releases, a binary version of MySQL is more convenient. But tcx's binary
release is not compiled in the same way as the source release so some differences
in where support files are located exist.
We use GNU autoconf so it will be possible to port to all modern systems with working Posix threads and a C++ compiler. The client code does require C++ but not threads. We use the software ourselves primarily on Solaris (currently 2.5.1) and some on Linux (currently 2.0.27).
A working Posix thread library is needed for the server. On Solaris 2.5 we use SUN PThreads (the native thread support in 2.4 and before is not good enough) and on Linux we use LinuxThreads by Xavier Leroy (Xavier.Leroy@inria.fr).
There is a patched version of Provenzano's Pthreads from MIT (see http://www.mit.edu:8001/people/proven/pthreads.html) in thedistribution. This can be used for some operating systems that doesn't have posix threads.
We have alose tried to use another user level thread package named FSU Pthreads (see http://www.informatik.hu-berlin.de/~mueller/pthreads.html).
This didn't work with MySQL because of problems with the implementations. See the thr_lock and thr_alarm programs in the mysys directory for some tests/examples of these problems. More information can be found in the `PORTING' file in the distribution.
MySQL support.
mysqld can give error messages in the following languages: English
(default), Germany and Swedish.
To start mysqld with a language use the --language (-L) switch:
mysqld --language swedish
mysqld --language /usr/local/share/swedish
The language files are located (by default) in
`mysql_base_dir/share/LANGUAGE/'
We are going to use the following policy when updating MySQL:
Each minor patch made will increment the last number in the version string. When there is new features or minor incompatibilities with previous versions the second number in the version string will get incremented.
Unireg is our tty interface builder, but it uses a low level connection to our NISAM (with is used by MySQL) and because of this it's very quick. It has existed since 1979 (on Unix in C since ~1986).
It has the following components:
We update most our production databases with the UNIREG interface and serve web pages through MySQL (and in some extreme cases the UNIREG report generator).
Unireg takes about 3M of disk space and works on at least the following platforms: SUN OS 4.x, Solaris, Linux, HPUX, ICL Unix, DNIX, SCO and MSDOS.
Unireg is currently only available in Swedish and Finnish.
The price tag for UNIREG is 10,000 swedish kr (about 1500$ US), but this includes support. UNIREG is distributed as a binary. (But all the isam source can be found in MySQL). Usually we compile the binary for the customer at their site.
New development is concentrated to MySQL.
Basic licensing issues:
For more information se the rest of this chapter and the file `PUBLIC' in the distribution.
MySQL cost?
For normal use MySQL costs nothing. When you sell MySQL
directly or as a part of another product you have to pay for it. See the
file `PUBLIC' in the distribution.
The client access part of MySQL is in the public domain. The command line client inlcudes parts that is under the GNU Public Licence (readline).
The current price is $200 for one licence and $1000 for 10 licences if payed for at the same time.
If you have a low margin high volume product you can always talk to us about other terms.
A licence includes really basic support. This means that we are trying to answer any relevant question. If the answer is in the FAQ, we are going to redirect the answer to the FAQ. If you do not have a licence/support we will probably not answer at all.
If you discover what we consider a real bug, we are likely to fix it in any case. But if you pay for support we will notify you about the fix status instead of just fixing it in a later release.
More comprehensive support will be sold separately. One year of basic email support costs $200. One year of more extensive email/telnet support costs $2000.
Well currently we can take SWIFT payments, cheques or credit cards.
Payment should be made to:
Postgirot Bank AB
105 06 STOCKHOLM, SWEDEN
T.C.X DATAKONSULT AB
BOX 6434
11382 STOCKHOLM
SWIFT address: PGSI SESS
Account number: 96 77 06 - 3
Specify: license and/or support and your name and email address.
In europe and japan you can use EuroGiro (that should be cheaper) to the same account.
If you want to pay by cheque make it payable to "Detron HB". And mail it to the address below.
Detron HB David Axmark Kungsgatan 65 B 753 21 UPPSALA SWEDEN
If you want to pay with credit card over internet you can use https://www.tcx.sw:443/licence.htmy
For commercial licensing or if you have any questions about any of the information in this document, please contact:
Detron HB David Axmark Kungsgatan 65 B 753 21 UPPSALA SWEDEN Voice Phone +46-18-10 22 80 Fax +46-8-729 69 05 (I prefer email if possible) E-Mail: mysql-c@detron.se
MySQL use?
There are four different copyright's on the MySQL distribution.
MySQL specific source needed to make the mysqlclient
library and programs in the `client' directory is in the public
domain. Each file which is in the public domain has a header which clearly
states so. This is everything in `client' directory and some parts of
mysys, mystring and dbug libraries.
MySQL server is AGPL. See the file
PUBLIC for more info.
Our philosophy behind this is:
MySQL
by purchasing a support license.
This is a clarification of the information in the `PUBLIC' file.
MySQL may be *used* freely, including by commercial entities for evaluation or unsupported internal use. However, *distribution* for commercial purposes of MySQL, or anything containing or derived from MySQL in whole or in part, requires a written commercial license from TcX AB, the sole entity authorized by to grant such licenses.
You may not include MySQL "free" in a package containing anything for which a charge is being made exept as noted below.
The intent of the exception provided in the second clause is to allow commercial organizations operating an FTP server or a bulletin board to distribute MySQL freely from it, provided that:
If you want to distribute software in a commercial context that incorporates MySQL and you do *not* want to meet these conditions, you should contact TcX AB to find out about commercial licensing. Commercial licenses involve a payment, and include support and other benefits. These are the only ways you legally can distribute MySQL or anything containing MySQL: either by distributing MySQL under the requirements of the FPL, or by getting a commercial license from TcX AB.
I'm selling a product that can be configured to use MySQL although my customer is responsible for obtaining/installing MySQL (or some other supported alternative). Does one of us owe you money if my customer choses to use MySQL?
If your product REQUIRED MySQL to work you would have to pay a licence. If MySQL just added some new features it should fall inside normal use. For example is using MySQL added logging to a database instead of a text file it should not require a licence. This would of course mean that the user has to fetch and install MySQL by himself. If the program is (almost) useless without MySQL you would have to get a MySQL licence to sell your product.
Do I have to get a licence for my copy?
No you are not selling MySQL itself. But is this case we would like you to purchase MySQL support. That is either your support of MySQL or our support of you (the later is more expensive since our time is limited).
We may choose to distribute older versions of MySQL with the GPL in the future. However these versions will be identified as "GNU MySQL". Also all copyright notices in the relevant files will be changed to the GPL.
CREATE TABLE supports the PRIMARY KEY (columns),
INDEX (columns) and UNIQUE (columns) syntaxes. A PRIMARY
KEY is always UNIQUE. A key may be a prefix of a CHAR field.
isamchk).
select
(column1+column2) from table
sum, max, min, avg
& count).
show tables, show keys from table and
show columns from table
INSERT,UPDATE and DELETE returns how many rows was
affected.
= and <> to constant
NULL is identical as the IS NULL, IS NOT NULL
tests. This is an SQL extension to be compatible with mSQL. (Of course
one should use IS NULL.)
--help or -? as help.
The following column types are supported:
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
TIMESTAMP
DATE
TIME
FIXED LENGTH STRING
VARIABLE LENGTH STRING
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
A BLOB is a binary large object which can hold any amount of data :) There are 4 kinds of blobs See section What column (column) types are available with MySQL?. Normaly one can regard a BLOB as a VARCHAR without specified limit.
There are some constraints because of the message buffer used.
The default size of the buffer is 64K for the server and 512K for the clients.
To change the buffer length for the server, use
mysqld -O max_allowed_packet=max_blob_length. This allows the
message buffer to grow up to this limit when needed.
myODBC defines BLOB:s as LONGVARCHAR.
Restrictions for blobs:
SELECT id,SUBSTR(blob,1,100) GROUP BY 2
The following are useful extensions that MySQL that you probably won't find in other SQL:s. Be warned that if you use this your code won't be portable to other SQL servers.
INTO OUTFILE in a SELECT statement. See section SELECT syntax.
KEY or
INDEX in a CREATE TABLE statement. See section CREATE TABLE syntax..
DROP column or CHANGE column in a ALTER TABLE
statement. See section ALTER TABLE syntax.
LOAD DATA INFILE. This syntax is in many cases compatible with
Oracles LOAD DATA INFILE. See section LOAD DATA INFILE syntax.
" instead of ' to enclose strings.
\ character.
SET OPTION statement.
SELECT part of a GROUP BY statement fields
or functions that doesn't appear in the GROUP BY list.
In MySQL this means 'any matching value'. By using this one can get much
higher performance by avoiding sorting and grouping on unnecessary items.
This is often used in this context:
SELECT order.customerid,customer.name,max(payments) from order,customer WHERE order.customerid = customer.customerid GROUP BY order.customerid;In ANSI SQL you should have to add the customer.name in the GROUP BY clause, but in MySQL you don't have to.
=, <>, <= ,<, >=,>, AND,
OR, or LIKE in a column statement
REGEXP or NOT REGEXP.
CONCAT() with other than 2 arguments. MySQL can take any
number of arguments.
BIT_COUNT(), ELT(), FROM_DAYS(), FORMAT(),
IF(), PASSWORD(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(),
or WEEKDAY().
STD() group function.
MIN() or MAX() as functions, not group functions.
Se the file `INSTALL-SOURCE' in the MySQL distribution.
Check the log file to see if mysqld started up correctly
cd <localstatedir default /usr/local/var> tail <your host name>.log
To verify that MySQL is working run the following tests:
> cd /usr/local/bin > ./mysqlshow +-----------+ | Databases | +-----------+ | mysql | +-----------+ > ./mysqlshow mysql Database: mysql +--------+ | Tables | +--------+ | db | | host | | user | +--------+ > ./mysql -e "select host,db,user from db" mysql +------+--------+------+ | host | db | user | +------+--------+------+ | % | test | | | % | test_% | | +------+--------+------+
You can also run the test in the test subdirectory. To run `auto_increment.tst':
./mysql -vf test < ./tests/auto_increment.tst
Expected results are shown in the file `./tests/auto_increment.res'
To start or stop MySQL you may use the following commands:
scripts/mysql.server stop scripts/mysql.server start
You will probably want to add these start and stop commands to the appropriate places in your /etc/rc* files when you start using MySQL for production applications.
If you are porting MySQL to an new system you should first try to get mysys/thr_lock and mysys/thr_alarm to work. They shouldn't core dump and not print any error (they print a lot of other information thought).
By starting bin/safe_mysqld with --log one will get a log in
install-directory/var/'hostname'.log (the top level database directory).
This log will contain all successfull connections and all commands issued to
the MySQL server.
If you have compiled MySQL with --with-debug=yes you can can also get a very informative log with:
libexec/mysqld --debug
which makes a big log in /tmp/mysql.trace. The default debug option is
d:i:t:o,/tmp/mysql.trace
You can get a smaller log with:
libexec/mysqld --debug=d,info,query,general:o,/tmp/mysql.trace
and even smaller (on stdout):
libexec/mysqld --debug=d,general,query
You can get more information about the debug switches by examining the file
dbug/dbug.c
The following functionality is missing in the current version of MySQL. For the priority of new extensions you should consult: http://www.tcx.se/TODO
WHERE with expressions. The current WHERE can only use operators of type
column = column or column = constant or
constant = constant. This is because the interface to the UNIREG range
optimizer is real simple. In 3.21.0 the range optimizer is totally rewritten
and the WHERE will work as expected. In the mean time one should move
the expressions to the HAVING part. HAVING is like a
WHERE on the result but because HAVING isn't optimized
expressions works. The only problem with HAVING is that all fields
that are used in the HAVING part must also be in the select part.
See section SELECT syntax.
IN.
SELECT. The following will not work in MySQL:
SELECT * from table WHERE id IN (SELECT id from table2)MySQL only supports
INSERT ... SELECT.... Independent sub-SELECTs
will be availably quite soon.
SELECT ... INTO TABLE..... MySQL only supports
SELECT ... INTO OUTFILE....
According to my experience it would would just take a few hours to convert a tool using the mSQL C API to the MySQL C API.
The procedure:
Differences between the MySQL and mSQL C API's.
MYSQL as a connection type (mSQL uses an int)
mSQL are the MySQL client/server communications protocols?Enough different that it's impossible (at least not easy) to support both.
The biggest differences between MySQL and mSQL:
I don't think it makes sense to give a full spec, as we have plans to optimize this in the near future.
MySQL & mSQL 2.0?CREATE TABLE
MySQL
mSQL
MySQL
CREATE TABLE. Indexes can not be
removed without recreating the table. See section ALTER TABLE syntax.
mSQL
CREATE INDEX clause. Indexes
may be removed with DROP INDEX.
MySQL
autoincrement as a column type specifier. The given identifier
may be retrieved after update by the API function mysql_insert_id().
mSQL
group by clause)
MySQL
count(), avg(), min(), max(),
sum() and std(). min() and max() may take string
arguments. count(*) is optimized to return very quickly if no other
column is used.
mSQL
GROUP BY yet.
MySQL
MySQL uses indexes if
the like argument doesn't start with a wild-card.
mSQL
MySQL
mSQL
MySQL
mSQL behavior in MySQL, use braces:
select * from table where a=1 and b=2 or a=3 and b=4 -> select
* from table where (a=1 and (b=2 or (a=3 and (b=4)))).
mSQL
MySQL
mSQL
MySQL
mSQL
MySQL
mSQL
MySQL
mSQL
HAVING clause.
MySQL
SELECT COUNT(*)
AS id_count,id FROM groups GROUP BY id HAVING id_count > 10.
mSQL
MySQL
mSQL
Remember that AND has higher priority in boolean algebra than
OR. It's like 1+2*3 is = 1+ (2*3) = 7.
Always when you are in doubt:
This will make it easier to see what goes wrong. Distinct may also be a lot slower than a normal select.
Entry level SQL92. ODBC level 0-2.
GRANT. See section GRANT syntax. (Compability function).. This always succeedes.
You should use the MySQL privilege tables. See section How does the privilege system work?.
CREATE INDEX. See section CREATE INDEX syntax (Compability function).. This always succeeds.
You should create your index with CREATE TABLE. See section CREATE TABLE syntax..
You can also use ALTER TABLE. See section ALTER TABLE syntax.
DROP INDEX. See section DROP INDEX syntax (Compability function).. This always succeeds.
You can use ALTER TABLE to drop indexes. See section ALTER TABLE syntax.
MySQL use memory ?You can get the currently used buffer sizes with:
> ./mysqld --help
This should result in a all mysqld options and a list of configurable variables like the following
this. See section How can I change the buffer sizes of mysqld ?.
Possibly variables to option --set-variable (-O) are: keybuffer current value: 1048568 max_allowed_packet current value: 65536 net_buffer_length current value: 16384 max_connections current value: 128 table_cache current value: 64 recordbuffer current value: 524280 sortbuffer current value: 2097144
mysqld one can specify a key buffer. This will buffer
all keys in all tables at FIFO basis. (variable keybuffer)
MySQL the easy way: Do a full
join and a sort on two big tables :-(. In the future we will handle this by
after some given memory size limit having the hash heap automatically change to a
file based NISAM table. Now you know how to fill a disk in the future
:-). In many cases mysql can optimize the query so it doesn't need a
temporary table, but one case where this can't be done is when sorting and
grouping on different things.
MySQLadmin reload closes all tables that are not used and marks all
used tables to be closed when the running thread finishes. This will
effectively free most used memory.
NOT NULL if possible. It makes everything quicker (and you save 1
bit per column).
MEDIUMINT is often better than INT.
VARCHAR columns a fixed size record format
will be used. This is much quicker (but of course it may waste some
space). See section What is the different row formats? Or when to use VARCHAR/CHAR?.
isamchk -a on the table
once it is loaded with relevant data. This updates a value for each
index that tells how many rows that have the same value for this key in
average. Of course this is always 1 for unique indexes.
isamchk -Sir1
(if you want so sort on index 1). If you have a unique key that you want
to read all records from in numeric order this is a good way to make
that faster.
To check how you are doing, run isamchk -evi on the .ISM
file.
The time to insert a record consists of:
Where (number) is proportional time. Here is not calculated the initial overhead to open tables (which is done 1 time for every simultaneous running query).
The size of the table slows down the insert of keys with N log N (B-trees).
(Incomplete, MySQL does a lot of optimizations)
((a AND b) AND c OR
(((a AND b) AND (c AND d)))) -> (a AND b) OR (a AND b AND c AND d)
(a<b AND b=c) AND a=5 -> b>5 AND b=c A=5
(b>=5
AND b=5) OR (b=6 and 5=5) or (B=7 and 5=6) -> B=5 or B=6
=, >, >=, <,
<=, BETWEEN and a LIKE with a character prefix like
'something%'.
AND levels.
key = 1 or A = 10 -> NULL (Can't use key)
key = 1 or A = 10 and key=2 -> key = 1 OR key = 2
key_part_1 = const and key_part_3 = const -> key_part_1 =
const
const_table.key = constant
const_table.key_part_1 = const_table2.column and const_table.key_part_2 = constant
ORDER BY or GROUP on columns
from one table and the sort and group are the same, or only one of sort and
group is given, then the sorted table is preferred first in join cue.
mysqld ?
With the mysqld -O variable=size command. Example run:
> mysqld --help (or > mysqld -?)
>mysqld --help
/my/monty/sql/mysqld Ver 3.20.5-beta for SOLARIS 2.5 (SPARCstation)
TCX Datakonsult AB, by Monty. This is FPL, not free software
This software comes with NO WARRANTY: see the file COPYING for details.
Starts the mysql server
Usage: /my/monty/sql/mysqld [OPTIONS]
-b, --basedir=path path to installation directory
-h, --datadir=path path to the database root
-#, --debug=... output debug log. Often this is 'd:t:o,filename`
-T, --debug-info print some debug info at exit
-?, --help display this help and exit
-L, --language=... client error messages in given language
-l, --log[=filename] log connections and queries to file
--log-isam[=filename]
log all isam changes to file
-O, --set-variable var=option
give a variable an value. --help lists variables
--skip-new-routines don't use new possible wrong routines.
--skip-grant-tables start without grant tables. This gives anyone FULL
ACCESS to all tables!
--skip-locking don't use system locking. To use isamchk one has
to shut down the server.
--skip-unsafe-select skipp unsafe select optimizations.
-V, --version output version information and exit
Current base_dir: /my/monty
Current data_dir: data/
Current language: english/
Possibly variables to option --set-variable (-O) are:
keybuffer current value: 1048568
max_allowed_packet current value: 65536
net_buffer_length current value: 16384
max_connections current value: 128
table_cache current value: 64
recordbuffer current value: 524280
sortbuffer current value: 2097144
> mysqld -O keybuffer=512k -O sortbuffer=100k -O recordbuffer=100k
mysqld demon starts with a cd to 'mysql-data-dir'. After this
mysqld-data-dir is changed to './' (current dir). All paths
(databases, pid file, and log file) are prefixed with './'
mysqld to run at full speed?
mysqld -O keybuffer=16m -O sortbuffer=1m -O recordbuffer=512k --skip-locking
--skip-locking disables file locking between SQL requests. If this is
used then the following can happen:
mysqld or the machine crashes a table has a higher risk of
being corrupted. Tables should at least be checked with isamchk
*.ISM after a crash.
mysqladmin reload before
one tries to check/repair tables with isamchk. (isamchk -d
table_name is always allowed).
The --skip-locking is default when compiling with MIT threads.
Actually using no VARCHAR or BLOB types results in a fixed row
size. Otherwise CHAR and VARCHAR are the same. Se the below
for a more exact definition.
You can check the format used for a table with isamchk -d.
MySQL has three different table formats:
isamchk -r should be run now and then to
reorganize the table. This is to get a better layout. Use isamchk -ei
table_name for some statistics.
isamchk
-ed. All links may be removed with isamchk -r.
mysql_query() returns success, mysql_store_result() sometimes returns NULL.It means one of the following:
INSERT
or UPDATE or DELETE).
You can always check if the statement should given a result by checking
that mysql_num_columns(MYSQL *) isn't 0. If this is 0
the last query was a statement that did not return values INSERT,
DELETE....
You have got an error if mysql_error(MYSQL *) isn't empty!
mysql_affected_rows(MYSQL *) returns the number of affected
rows in the last query when doing an INSERT, UPDATE or DELETE.
mysql_insert_id(MYSQL *) returns the given ID of the last
query when inserting a row into a table with a AUTO_INCREMENT key.
Some querries, LOAD DATA INFILE... and
INSERT INTO ... SELECT ..., return additional info.
This result is returned in mysql_info(MYSQL *). mysql_info()
returns a null pointer if there is no additional information.
If you insert a record which have a AUTO_INCREMENT key then you
can get the given id with mysql_insert_id(MYSQL *).
You can check if an auto_increment key is used by the following
code. This also checks if the query was a insert with a
auto_increment key.
if (mysql_error(MYSQL)[0] == 0 &&
mysql_num_fields(MYSQL_RESULT) == 0 &&
mysql_insert_id(MYSQL) != 0)
used_id = mysql_insert_id(MYSQL);
mysql_use_result() and mysql_store_result() modes?
This says to MySQL to use the result directly from the server (no
local store like in mSQL), which is somewhat faster and doesn't
use memory to buffer the hole result.
This shouldn't be used if there is much processing for each row at the
client side, or if the output is sent to a screen on which the user may
do a ^S (stop scroll), because this would tie up the server. One can't
use mysql_data_seek when using mysql_use_result.
When linking with the C API you can get the following errors on some systems:
gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl Undefined first referenced symbol in file floor /usr/local/lib/mysql/libmysqlclient.a(password.o) ld: fatal: Symbol referencing errors. No output written to client
This means that on your system you have to include the math library (-lm) last in the compile/link line.
STRINGS
A string may have ' or " around it.
\ is a escape character. The following escape characters are recognized:
\0
\n
\t
\r
\b
\'
' character.
\"
" character.
\\
\ character.
\%
% character. This is used in wild-card strings to search after
%.
\_
_ character. This is used in wild-card strings to search after
_.
A ' inside a string started with ' may be written as ". A " inside a string started with " may be written as "".
MySQL> select 'hello', "'hello'", '""hello""', "'h"e"l"l"o"', "hel""lo"; 1 rows in set (0.00 sec) +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ | hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo | +-------+---------+-----------+-------------+--------+ mysql> select 'hello', "hello", '""hello""', "'ello", 'e"l"lo', '\'hello'; 1 rows in set (0.00 sec) +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ | hello | hello | ""hello"" | 'ello | e'l'lo | 'hello | +-------+-------+-----------+-------+--------+--------+ mysql> select "This\nIs\nFour\nlines"; 1 rows in set (0.00 sec) +--------------------+ | This Is Four lines | +--------------------+ | This Is Four lines | +--------------------+
If you want to insert binary data into a blob the following characters must be represented by escape sequences:
\0
\
'
"
NUMBERS
Integers are just a sequence of digits.
Floats use . as a decimal separator.
Examples of valid numbers are: 1221, 294.42,
-32032.6809e+10.
NULL
When using the text file export formats, NULL may be represented by \N. See section LOAD DATA INFILE syntax.
CREATE TABLE table_name ( create_definition,... )
create_definition:
column_name type NOT NULL [DEFAULT default_value] [ PRIMARY KEY ]
or column_name type [NULL] [ PRIMARY KEY ]
or PRIMARY KEY ( key_column_name,... )
or KEY [key_name] KEY( key_column_name,...)
or INDEX [key_name] ( key_column_name,...)
or UNIQUE [key_name] ( key_column_name,...)
or FOREIGN KEY key_name ( key_column_name,...) REFERENCES table_name [ ON DELETE { RESTRICT | CASCADE | SET NULL }]
key_column_namne:
column_namn [ (length) ]
To have a DEFAULT the column must be declared as NOT NULL.
TINYINT [(length)] [UNSIGNED] [ZEROFILL]
SMALLINT [(length)] [UNSIGNED] [ZEROFILL]
MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]
INT [(length)] [UNSIGNED] [ZEROFILL]
INTEGER [(length)] [UNSIGNED] [ZEROFILL]
BIGINT [(length)] [UNSIGNED] [ZEROFILL]
FLOAT [(precision)]
FLOAT [(length,decimals)].
DOUBLE [(length,decimals)].
REAL [(length,decimals)].
DOUBLE.
DOUBLE PRECISION [(length,decimals)].
DOUBLE.
DECIMAL [(length,decimals)]
NUMERIC [(length,decimals)]
DECIMAL.
CHAR(NUM)
VARCHAR(NUM)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
DATE
TIME
DATETIME
TIMESTAMP[(LENGTH)]
INT(5) ZEROFILL a value of 5 is retrieved as 00005.
column_namn(length) syntax one can specify a key with is only
a part of a string column. This can make the index file much smaller.
isamchk utility to
reorganize tables.
VARCHAR
columns with a length of 1 or 2 are changed to CHAR. When using one
VARCHAR columns all CHAR columns longer than 2 are changed to
VARCHAR's.
INSERT/UPDATE all strings (CHAR and VARCHAR)
are silently chopped/padded to the maximal length given by CREATE. All end
spaces are also automatically removed. For example VARCHAR(10) means that the
column can contain strings with a length up to 10 characters.
REGEXP and RLIKE) uses
ISO8859-1 (Latin1) when deciding the type of a character.
ALTER [IGNORE] TABLE table_name alter_specification [, alter_specification ...]
alter_specification:
ADD [COLUMN] create_definition
or CHANGE [COLUMN] old_column_name create_definition
or ALTER [COLUMN] column_name { SET default | DROP DEFAULT }
or DROP [COLUMN] column_name
or DROP PRIMARY KEY
DROP INDEX key_name
CHANGE column_name, DROP column_name and DROP
INDEX is MySQL extensions to ANSI SQL92.
COLUMN is pure noiseword and can be omitted.
ADD and CHANGE takes the same create_definition as
CREATE TABLE. See section CREATE TABLE syntax..
ALTER COLUMN sets a new default value or removes the old
default value for a column.
DROP INDEX drops and index. This is an MySQL extension.
DROP FOREIGN KEY is for future compatibility only. It doesn't do
anything.
DROP PRIMARY KEY drops the first UNIQUE key in table.
CHANGE tries to convert data to the new format as good as possibly.
mysql_info(MYSQL*) one can retrive how many records
was copied and how many records was deleted because of multiple keys.
ALTER TABLE one have to have select, insert, delete,
update, create and drop privileges on the table.
DROP TABLE table_name [, table_name....]
Removes one or more tables. All the data and the definition is removed so take it easy with this command!
DELETE FROM table_name WHERE where_definition
Returns records affected.
If one does a delete without a where clause then the table is truncated
(This is much faster). In this case it returns records affected as zero.
This will work even if the datafiles doesn't exists, because it uses
only information from the table definition file, table_name.frm.
where_definition: where_expr or where_expr [ AND | OR ] where_expr where_expr: column_name [> | >= | = | <> | <= | < ] column_name_or_constant or column_name LIKE column_name_or_constant or column_name IS NULL or column_name IS NOT NULL or ( where_definition )
SELECT [DISTINCT | ALL] select_expression,... [ FROM tables... [WHERE
where_definition ] [GROUP BY column,...] [ ORDER BY column [ASC | DESC] ,..]
HAVING full_where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]] [INTO OUTFILE 'file_name' ...]
+
-
*
/
NULL.
|
&
-
(,)
NOT
!
OR
AND
=
<>
<=
<
>=
>
expr LIKE expr
expr NOT LIKE expr
expr REGEXP expr
expr NOT REGEXP expr
ABS()
FLOOR()
CEILING()
ROUND()
EXP()
LOG()
LOG10()
POW()
SQRT()
RAND([integer_expr])
SIGN()
BIT_COUNT()
MOD()
%
MIN()
MAX()
CONCAT()
DATABASE()
LENGTH()
STRCMP()
LOCATE(A,B)
LOCATE(A,B,C)
LEFT(str,length)
RIGHT(str,right)
LTRIM(str)
RTRIM(str)
SOUNDEX(str)
SUBSTRING on the result to get a 'standard' soundex string.
All non alpha characters are ignored in the given string. All characters
outside the A-Z range are treated as vocals.
SUBSTRING(A,B,C)
REPLACE(A,B,C)
INSERT(org,start,length,new)
LCASE(A)
UCASE(A)
PASSWORD()
INTERVAL(A,a,b,c,d)
ELT(N,a,b,c,d,...)
FIELD(A,a,b,c,d,...)
BETWEEN(A,B,C)
PERIOD_ADD(P:N)
PERIOD_DIFF(A,B)
PERIOD_DIFF(199712,9612).
TO_DAYS(DATE)
FROM_DAYS()
WEEKDAY(DATE)
CURDATE()
NOW()
UNIX_TIMESTAMP([DATE])
timestamp column
as argument in which case it returns the timestamp.
DATE may also be a date string, a datetime string, or a number of format
YYMMDD or YYYMMDD in local time.
ISNULL(A)
IFNULL(A,B)
IF(A,B,C)
USER()
FORMAT(nr,NUM)
Functions for GROUP BY clause:
count(expr)avg(expr)min(expr)max(expr)sum(expr)
Sum of expr.
std(expr)
Standard derivation of expression. This is a extension to ANSI SQL.
min() and max() may take string arguments. count(*) is
optimized to return directly if no other column is used.
These can't be used in expression, even if argument may be an expression.
Eg: SUM(value/10) is allowed but SUM(value)/10 is not (yet!).
MySQL has extended the use of GROUP BY. You can use columns or
calculations in the SELECT expressions with doesn't appear in the
GROUP BY part. This stands for 'any possible value for this group'.
By using this one can get much higher performance by avoiding sorting and
grouping on unnecessary items.
For example, in the following query one doesn't need to sort on b.name:
SELECT a.id,b.name,COUNT(*) from a,b WHERE a.id=b.id GROUP BY a.id
Strings are automatically converted to numbers and numbers to strings
when needed (ala perl). If in a compare operation
((=, <>, <= ,<, >=, >)) either if the arguments are a numerical
the arguments are compared as numbers, else the arguments are compared
as strings. All string compares are done case-independent by ISO8859-1.
HAVING clause.
select concat(last_name,' ',first_name) as name from table order by name
IFNULL() and IF() returns number or string value according to
use.
ORDER and GROUP columns may be given as column names,
column alias or column number in SELECT clause.
HAVING clause can take any columns or alias in the
select_expressions. It is applied last, just before items are sent to
the client, without any optimization. Don't use it for items that should
be in the WHERE clause. You can't write (yet):
SELECT user,MAX(salary) FROM users GROUP BY users HAVING max(salary)>10
Change it to:
SELECT user,MAX(salary) AS sum FROM users GROUP BY users HAVING sum > 10
INSERT INTO table [ (column_name,...) ] VALUES (expression,...) or INSERT INTO table [ (column_name,...) ] SELECT ....
An expression make use any previous column in column_name list (or table if no column name list is given).
The following holds for a multi-row INSERT statement:
ORDER BY clause.
INSERT statement cannot appear in the FROM
clause of the query.
INSERT INTO ... SELECT ... then one can get the following
info string with the C API function mysql_info().
@result{Records: 100 Duplicates: 0 Warnings: 0}
Duplicates are number of rows which couldn't be written because some
key would be duplicated. Warnings are columns which was set to NULL,
but has been declared NOT NULL. These will be set to their default value.
LOAD DATA INFILE 'table_interval.text'
[REPLACE | IGNORE]
INTO TABLE table_interval
[FIELDS [TERMINATED BY ',' [OPTIONALLY] ENCLOSED BY '"'
ESCAPED BY '\\' ]] [LINES TERMINATED BY '\n']
[(field list)]
This is used to read rows from a textfile.
To write data to a textfile, use the
SELECT ... INTO OUTFILE 'interval.tab' fields terminated by ','
enclosed by '"' escaped by '\\' lines terminated by '\n'
syntax.
Normally you don't have to specify any of the textfile type options. The
default is a compact textfile with columns sepearated with tab
characters and all rows ends with a newline. Tabs, newlines and \
inside fields are prefixed with a \. NULL's are read and written as
\N.
FIELDS TERMINATED BY has default value of \t.
FIELDS [OPTIONALLY] ENCLOSED BY has default value of "
FIELDS ESCAPED BY has default value of '\\'.
LINES TERMINATED BY has default value of '\n'.
FIELDS TERMINATED BY and LINES TERMINATED BY may be more
than 1 character.
If LINES TERMINATED BY is a empty string and FIELDS
TERMINATED BY is not empty then lines are also terminated by
FIELDS TERMINATED BY.
If FIELDS TERMINATED BY and FIELDS ENCLOSED BY both are
empty strings (") then this gives a fixed row size ("not delimited"
importation format). With a fixed row size NULL values are outputed as a
blank string.
If you specify OPTIONALLY in ENCLOSED BY, then only
strings are enclosed in ENCLOSED BY by the SELECT ... INTO
statement.
When using LOAD duplicated ENCLOSED BY are removed inside
an ENCLOSED BY. For example 'He"llo' is read as 'He'llo'.
If ESCAPED BY is not empty then the following characters will be
prefixed with the escape character: ESCAPED BY, ASCII 0,
and the first character in any of fields terminated by,
FIELDS ENCLOSED BY and LINES TERMINATED BY.
If FIELDS ENCLOSED BY is not empty then NULL is read as a
NULL value. If FIELDS ESCAPED is not empty then \N
is also read as a NULL value.
If REPLACE is used the new row will replace all rows which has a
same unique key. If IGNORE is used the row will be skipped if
there already exist a record with a identical unique key. If no of the
above options is used an error will be issued. The rest of the textfile
will be ignored if one gets a duplicate key error.
Some possible cases that's not supported by LOAD DATA:
FIELDS TERMINATED BY and FIELDS ENCLOSED
BY both are empty) and BLOB fields.
FIELDS ESCAPED BY is empty and the data contains LINES
TERMINATED BY or FIELDS ENCLOSED BY followed by FIELDS
TERMINATED BY.
All rows are read into table. If a row has too few fields the rest fields are set to default values.
For security reasons the textfile must either reside in the database directory or be readable by all.
For more info about the escaped syntax See section Literals. How do you write strings and numbers?.
When the LOAD DATA query is done one can get the following info
string with the C API function mysql_info().
@result{Records: 1 Deleted: 0 Skipped: 0 Warnings: 0}
Warnings is incremented for each column which can't be stored without loss of precision, for each column which didn't get a value from the read text line (happens if the line is too short) and for each line which has more data than can fit into the given columns.
Example that load all fields;
LOAD DATA INFILE 'persondata.text' INTO TABLE persondata;
UPDATE table SET column=expression,... WHERE where_definition
All updates are done from left to right. IF one accesses a column in the expression it uses the current value (a given value or the default value).
SHOW DATABASES [LIKE wild] or SHOW TABLES [FROM database] [LIKE wild] or SHOW COLUMNS FROM table [FROM database] [LIKE wild]
Gives information about databases, tables or columns. The wild is a LIKE
string. FIELDS may be used as an alias for COLUMNS.
(DESCRIBE | DESC) table [column]
Gives information about columns. This command is for Oracle compability. See section SHOW syntax. Get information about names of columns.. Column may be a column name or a string. Strings may contain wildcards.
SET OPTION SQL_VALUE_OPTON=value, ...
The used options remain in effect for the hole current session.
The different options are:
SQL_SELECT_LIMIT=value
GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE,
REFERENSES (column list), USAGE))
ON table TO user,... [WITH GRANT OPTION]
This command doesn't do anything. It's only in mysql for compability reasons. Privileges in MySQL is handled with the mysql grant tables. See section How does the privilege system work?.
CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... )
This function doesn't do anything. It's only in mysql for compability reasons. You can create new index with ALTER TABLE. See section ALTER TABLE syntax.
DROP INDEX index_name
This always succeeds. You can drop an index with ALTER TABLE. See section ALTER TABLE syntax.
MySQL picky about reserved words?
It seems that I can't create a table with column names timestamp or
group.
Functions doesn't clash with table or column names. (For example ABS
is an allowed column name). The only restriction is that space is not
allowed between a function name and the '(' when using functions.
The following are reserved words in MySQL. Most of them (for
example) group, are forbidden by ANSI SQL92 as column and/or and a few
are because MySQL needs them and (currently) is using a Yacc
parser:
add, all, alter, and, as, asc,
auto_increment, between, bigint, blob,by,
cascade, char, character, change,
column, columns create, data, databases,
date, datetime, dec, decimal,
default, delete, desc, describe, distinct,
double,
drop, fields, float, float4, float8,
foreign, from, grant, group,having,
ignore, in, index, infile, insert,
int, int1, int2,
int3, int4, int8, integer, interval,
into, is, key, keys, like, lines,
limit, load, longblob, mediumblob,
mediumint, middleint, not,
null, numeric, on, option, or, order,
precision, primary, procedure, privileges,
real, references, regexp, replace, restrict,
rlike, select, set, show,
smallint, sql_big_tables, sql_big_selects,
sql_select_limit,straight_join, table, tables,
terminated, time, timestamp,
tinyblob, tinyint, unique, unsigned,
update, usage, values, varchar, varying,
where, zerofill.
mysql
mysqladmin
mysqld
mysqldump
mysqlshow
safe_mysqld
mysqlaccess
mysqlbug
mysql_install_db
isamchk
msql2mysql
replace
make_binary_release
If you want to group or sort on a blob, make the BLOB into a fixed length
object with the SUBSTRING or similar functions.
If MySQL crashed (f ex if the computer is turned off) when all data is not written to disk the tables may be crashed. To check a table use
isamchk table_name
isamchk -e table_name
isamchk -ei table_name
Always backup a table before trying to repair it.
To repair a table(REMEMBER TO BACKUP A TABLE BEFORE TRYING TO REPAIR IT!):
isamchk -r table_name
isamchk -rq table_name
To get a description/statistics from a table us the forms below. Sometimes we will explain some of the information in more detail.
isamchk -d table_name
ISAM file: company.ISM
Data records: 1403698 Deleted blocks: 0
Recordlength: 226
Record format: Fixed length
table description:
Key Start Len Index Type
1 2 8 unique double
2 15 10 multip. text packed stripped
3 219 8 multip. double
4 63 10 multip. text packed stripped
5 167 2 multip. unsigned short
6 177 4 multip. unsigned long
7 155 4 multip. text
8 138 4 multip. unsigned long
9 177 4 multip. unsigned long
193 1 text
For explanations se below.
isamchk -d -v table_name
ISAM file: company.ISM
Isam-version: 2
Creation time: 1996-08-28 11:44:22
Recover time: 1997-01-12 18:35:29
Data records: 1403698 Deleted blocks: 0
Datafile: Parts: 1403698 Deleted data: 0
Datafilepointer (bytes): 3 Keyfile pointer (bytes): 3
Max datafile length: 3791650815 Max keyfile length: 4294967294
Recordlength: 226
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 8 unique double 15845376 1024 1
2 15 10 multip. text packed stripped 25062400 1024 2
3 219 8 multip. double 40907776 1024 73
4 63 10 multip. text packed stripped 48097280 1024 5
5 167 2 multip. unsigned short 55200768 1024 4840
6 177 4 multip. unsigned long 65145856 1024 1346
7 155 4 multip. text 75090944 1024 4995
8 138 4 multip. unsigned long 85036032 1024 87
9 177 4 multip. unsigned long 96481280 1024 178
193 1 text
ISAM file
Isam-version
Creation time
Recover time
Data records
Deleted blocks
Datafile: Parts
Data
records.
Deleted data
Datafilepointer
Keyfile pointer
Max datafile length
.ISD) get.
Max keyfile length
.ISM) get.
Recordlength
Record format
Fixed
length.
table description
Key
Start
Len
Index
unique or multip.. If one value can exist multiple times
in this index.
Type
C data-type
with a optional packed or stripped.
Root
Blocksize
Rec/key
isamchk -a. If this is not updated at all a default
value of 30 is given.
isamchk -eis table_name
Checking ISAM file: company.ISM Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 17% Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Recordblocks: 1403698 Deleteblocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1626.51, System time 232.36 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 627, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary contexts switches 639, Involuntary contexts switches 28966
Keyblocks used
Packed
CHAR/VARCHAR/DECIMAL keys. For long string like
names this can reduce the spaced used significantly. In the above example
the 4 key is 10 characters long and gets a 60% reduction in space.
Max levels
Records
M.recordlength
Packed
Recordspace used
Empty space
Blocks/Record
isamchk. Se
See section How do you check/repair/reconstruct an MySQL table?.
Recordblocks
Deleteblocks
Recorddata
Deleted data
Lost space
Linkdata
isamchk -eiv table_name
Checking ISAM file: company.ISM Data records: 1403698 Deleted blocks: 0 - check file-size - check delete-chain index 1: index 2: index 3: index 4: index 5: index 6: index 7: index 8: index 9: No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 - check data record references index: 3 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 - check data record references index: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 - check data record references index: 5 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 6 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 7 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 8 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 - check data record references index: 9 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 9% Packed: 17% - check records and index references [LOTS OF ROW NUMBERS DELETED] Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Recordblocks: 1403698 Deleteblocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1639.63, System time 251.61 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0 Blocks in 4 out 0, Messages in 0 out 0, Signals 0 Voluntary contexts switches 10604, Involuntary contexts switches 122798
Here is the data file sizes of the table used above.
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.ISD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.ISM
The MySQL privilege system makes sure that each user may do exactly the
things that she is supposed to be allowed to. The system decides to
grant different privileges depending on which user connects
from which host to which database. The decision is
based on the contents of the three tables in the MySQL database:
user, host and db.
The grantable privileges on rows are select, insert, update and delete.
The table and database privileges are create and drop. Create and drop are for both tables and databases. Since a user with a drop grant can delete any table, this is the same thing as a drop grant for the database.
Other privileges give the right to use files (for LOAD DATA INFILE and SELECT INTO OUTFILE) and to use the administrative commands shutdown, reload and process (to get the current process list).
The privilege tables are read into mysqld with mysqladmin
reload. If the privilege tables are empty or non-existent or if the
server is started with --skip-grant-tables, full access is
granted to everyone.
You can always test your privileges with the script mysqlaccess,
which Yves Carlier has provided for the MySQL distribution.
See section Why do I get 'Access denied'?.
% and _. Leaving any of these fields empty is equivalent
to setting it to '%'.
localhost, a hostname, an IP number or an SQL
expression. An empty host in the db table means any host in the host
table. An empty host in the host or user table means any host
that can create a TCP connection to your server.
db table.
This means that a superuser only needs to be in the user table with all
privilege-flags set to Y.
Everything granted in the user table is valid for every database
that cannot be found in the db table. For this reason, it might be
wise to grant users (apart from superusers) priviliges on a per-database
basis only.
The host table is mainly there to maintain a list of "secure"
servers. At TCX host contains a list of all machines on the
local network. These are granted all privileges.
The connecting user's privileges are calculated by the following algorithm:
host =
"". Within each host (i.e. very much like GROUP BY Host), sort
by user using the same rules. Finally, in the db table sort by db
using the same rules. In the steps below, we will look through the
sorted tables and always use the first match found.
db table
using the first match found. Call this set of privileges P.
host = "" for the entry found in the db table, AND
P with the privileges for the host in the host table, i.e.
remove all privileges that are not "Y" in both. (If host <> "",
P is not affected. In this case, host must have matched
the connecting host's name at least partially. Therefor it can be
assumed that the privileges found in this row match the connecting
host's profile.)
user
table, i.e. add all privileges that are "Y" in user.
The connecting user gets the set of privileges P.
Let's show an example of the sorting and matching! Suppose that the user table contains this:
+-----------+----------+- | Host | User | ... +-----------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+-
Then the search order will be:
So jeffrey attempting to connect on localhost will be matched by the
localhost/any line, not by the any/jeffrey line. The
first match found is used!
So if you have access problems, print out the user table, sort it by hand, and see where the match is being made.
To add privileges to the MySQL database:
This assumes the current user has insert privileges for the mysql db
table and reload privileges. The server (mysqld) has to be running. If
it is not, start it with safe_mysqld --log &.
> mysql mysql
insert into user values ('%','monty',password('something'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ;
insert into user (host,user,password) values('localhost','dummy',") ;
insert into user values ('%','admin',",'N','N','N','N','N','N','Y','N','Y','Y') ;
quit
> mysqladmin reload
This makes three new users:
monty
MySQL.
admin
mysqladmin reload
and mysqladmin processlist. May be granted individual database
privileges through table db.
dummy
db.
The default privileges (set in `scripts/mysql_install_db') is that
root can do anything. Any user can do anything with any database whose
name is 'test' or start with 'test_'. A normal user can't use
mysqladmin shutdown or mysqladmin processlist. See the
script (`scripts/mysql_install_db') for a example on how to add
other users.
The privilege tables are read into mysqld with 'mysqladmin reload'. If the privilege tables are empty (or non-existent) full access are granted to everyone.
A common mistake is to try something like:
INSERT INTO user VALUES ('%','jeffrey','bLa81m0','Y','Y','Y','N','N','N','N','N', 'N','N');
Then (of course) a mysqladmin reload to make the authentication
change take effect, then trying to connect to the server:
$ ./mysql -h sqlserver -u jeffrey -p bLa81m0 test
Access denied
Try with this instead:
INSERT INTO user VALUES
('%','jeffrey',password('bLa81m0'),'Y','Y','Y','N','N','N','N','N','N','N');
And like before mysqladmin reload to make the authentication change
take effect.
Now things should work.
MySQL without root access?
Yes. You just have to make some user to be able to create databases. The
easiest way to do this is to change the word root to your user
name in the script `scripts/mysql_install_db'. And then run this
script.
When you run mysqladmin status you get something like:
Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12
This can be somewhat perplexing if you only have 6 tables.
Because MySQL is multithread it may have many queries on the same table at once. To minimize the problem with two threads having different states on the same file, I open the table again for each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared for all threads.
If you need it as sql function (like SOUNDEX()), its real easy:
double *Item_func_newname::val()
If you are defining a number function or
String *Item_func_newname::Str(String *str)
If you are defining a string function.
void Item_func_newname::fix_length_and_dec()
This should at least calcutate max_length based on the given
arguments. max_length is the maximal number of chars the function
may return. If the function can't return a NULL, one should set
maybe_null = 0.
About string functions:
Se the file PORTING in the distribution.
MySQL ODBC support?
MySQL ODBC is a 32 bit ODBC (2.50) level 0 for Windows95 and
NT. We hope somebody will port it to Windows 3.x.
MySQL ODBC?We have only tested ODBC with Admndemo, some C programs, Msquery and Excel.
To give some light about any problem we would like to have the log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MYODBC log.
To get a MYODBC log, please put this MYSQL_DEBUG=d:t:O,filename in
your AUTOEXEC.BAT and restart.
The log will be written to file `filename'.
There are three possibilities for sepcifying the server name on windows95:
Example to fill in the 'ODBC setup'.
Data Source Name: test Description: This is my test database Server: 194.216.84.21 User: monty Password: my Port:
These are default values to be given when prompting for a Driver connect. You don't have to give 'server', 'user' or 'password' in this screen. If port is not given the default port (3333) is used.
When connection to a ODBC source you have the option to change the server, user, password and port.
--without-grant-tables option. Now you can change the MySQL
grant tables and use the script mysqlaccess to check if your
grant's should work. mysqladmin reload tells the mysqld demon to
start using the new grant tables.
mysql -u user database or
mysql -uuser -ppassword database.
mysql -u user database then you have a problem with
the 'user' table. Check this by doing mysql -u root mysql and
select * from user. You should have an entry with 'hostname' and
'user' matching your computers hostname and your username. If the client
and the server is running on the same host and you haven't used the
--host option to mysql and you are not using MIT threads,
'localhost' is a synonym for your hostname.
mysql -u root test works but mysql -h your_hostname -u
root test gives 'Access denied' then you don't have the right name for
your host in the user table. For example if you have an entry with host
'tcx' in the 'user' table, but your DNS tells MySQL that your hostname
is 'tcx.subnet.se' then the entry can't work. Test by adding a record
with the IP of your host in the 'user' table. You can of course also add
a host with a wildcard (for example 'tcx%') in the 'user table' (but
using hostnames ending with % is pretty insecure).
mysql -u user database to work on the server machine, but
mysql -u host -u user database doesn't work on another client
machine, then you don't have the client machine in the 'user' or the
'db' table.
mysql -u user test works but mysql -u user
other_database doesn't work, you don't have the other_database in the
'db' table.
SELECT
... INTO OUTFILE or LOAD DATA SQL commands the you probably
don't have the file_priv privilege set for you in the user table.
mysqld demon with:
--debug=d,general,query. This will print info about the host and
user that tries to connect and also information about each command
issued. See section How to debug MySQL.
The format of DATE is 'YYYY-MM-DD'. Actually nothing else is allowed (ANSI SQL). One should use this format to update or in the WHERE clouse. Ie: select * from table_1 where idate >= '1997-05-05';
As a convinience MySQL automaticly converts the date to a number if used in a number context. It's also smart enough to allow a 'relaxed' string form when updating, but only when updating.
This means that the following works:
insert into table_1 (idate) values (19970505) ;
insert into table_1 (idate) values ('19970505') ;
insert into table_1 (idate) values ('1997-05-05');
insert into table_1 (idate) values ('1997.05.05');
insert into table_1 (idate) values ('1997 05 05');
select idate from table_1 where idate >= '1997-05-05';
select idate from table_1 where idate >= 19970505;
select mod(idate,100)1 from table_1 where idate >= 19970505;
The following will not work:
select idate from table_1 where idate >= '19970505';
Because '19970505' is compared as a string to '1997-05-05'.
All keys, PRIMARY, UNIQUE and INDEX(), are stored in B trees. Strings are automaticly prefix- and end-space compressed.
INDEX(col1, col2) creates a multiple index over the two columns. The key can be seen like a concatenation of the given columns. If you use INDEX(col1), INDEX(col2) instead of INDEX(col1,col2) you get two separate keys instead.
SELECT * FROM table WHERE col1=# AND col2=#
In case of INDEX(col1,col2) the right row(s) can be fetched directly. In case of INDEX(col1), INDEX(col2) the optimizer decides which index will find fewer rows and this index will be used to fetch the rows.
If the table have a index INDEX(col1,col2,col3....) and prefix of this can be used by the optimizer to find the rows. This means that above gives you search capabilities on: INDEX(col1) and INDEX(col1,col2) and INDEX(col1,col2,col3)...
MySQL can't use a sub part of a key to locate rows through a key.
With the definition INDEX(col1,col2,col3):
SELECT * FROM table WHERE col1=# SELECT * FROM table WHERE col2=# SELECT * FROM table WHERE col2=# and col3=#
Only the first query will use keys.
This document was generated on 21 December 1999 using the texi2html translator version 1.51.