diff options
Diffstat (limited to 'Docs')
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/australia.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/australia.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/austria.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/austria.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/canada.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/canada.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/czech-republic.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/czech-republic.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/germany.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/germany.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/great-britain.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/great-britain.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/hungary.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/hungary.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/israel.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/israel.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/italy.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/italy.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/japan.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/japan.txt | 0 | ||||
| -rw-r--r-- | Docs/Flags/latvia.eps | 99 | ||||
| -rw-r--r-- | Docs/Flags/latvia.gif | bin | 0 -> 117 bytes | |||
| -rw-r--r-- | Docs/Flags/latvia.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/russia.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/russia.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/south-korea.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/south-korea.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/sweden.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/sweden.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/taiwan.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/taiwan.txt | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/usa.eps | 0 | ||||
| -rw-r--r--[-rwxr-xr-x] | Docs/Flags/usa.txt | 0 | ||||
| -rw-r--r-- | Docs/internals.texi | 209 | ||||
| -rw-r--r-- | Docs/manual.texi | 3453 |
35 files changed, 3022 insertions, 739 deletions
diff --git a/Docs/Flags/australia.eps b/Docs/Flags/australia.eps index f98c03e2c83..f98c03e2c83 100755..100644 --- a/Docs/Flags/australia.eps +++ b/Docs/Flags/australia.eps diff --git a/Docs/Flags/australia.txt b/Docs/Flags/australia.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/australia.txt +++ b/Docs/Flags/australia.txt diff --git a/Docs/Flags/austria.eps b/Docs/Flags/austria.eps index 7a0b56f3690..7a0b56f3690 100755..100644 --- a/Docs/Flags/austria.eps +++ b/Docs/Flags/austria.eps diff --git a/Docs/Flags/austria.txt b/Docs/Flags/austria.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/austria.txt +++ b/Docs/Flags/austria.txt diff --git a/Docs/Flags/canada.eps b/Docs/Flags/canada.eps index b770266de60..b770266de60 100755..100644 --- a/Docs/Flags/canada.eps +++ b/Docs/Flags/canada.eps diff --git a/Docs/Flags/canada.txt b/Docs/Flags/canada.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/canada.txt +++ b/Docs/Flags/canada.txt diff --git a/Docs/Flags/czech-republic.eps b/Docs/Flags/czech-republic.eps index afa50e9a82d..afa50e9a82d 100755..100644 --- a/Docs/Flags/czech-republic.eps +++ b/Docs/Flags/czech-republic.eps diff --git a/Docs/Flags/czech-republic.txt b/Docs/Flags/czech-republic.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/czech-republic.txt +++ b/Docs/Flags/czech-republic.txt diff --git a/Docs/Flags/germany.eps b/Docs/Flags/germany.eps index 568543e3680..568543e3680 100755..100644 --- a/Docs/Flags/germany.eps +++ b/Docs/Flags/germany.eps diff --git a/Docs/Flags/germany.txt b/Docs/Flags/germany.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/germany.txt +++ b/Docs/Flags/germany.txt diff --git a/Docs/Flags/great-britain.eps b/Docs/Flags/great-britain.eps index 97a7ffc9b57..97a7ffc9b57 100755..100644 --- a/Docs/Flags/great-britain.eps +++ b/Docs/Flags/great-britain.eps diff --git a/Docs/Flags/great-britain.txt b/Docs/Flags/great-britain.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/great-britain.txt +++ b/Docs/Flags/great-britain.txt diff --git a/Docs/Flags/hungary.eps b/Docs/Flags/hungary.eps index e405fc3cffe..e405fc3cffe 100755..100644 --- a/Docs/Flags/hungary.eps +++ b/Docs/Flags/hungary.eps diff --git a/Docs/Flags/hungary.txt b/Docs/Flags/hungary.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/hungary.txt +++ b/Docs/Flags/hungary.txt diff --git a/Docs/Flags/israel.eps b/Docs/Flags/israel.eps index 3d3059a907a..3d3059a907a 100755..100644 --- a/Docs/Flags/israel.eps +++ b/Docs/Flags/israel.eps diff --git a/Docs/Flags/israel.txt b/Docs/Flags/israel.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/israel.txt +++ b/Docs/Flags/israel.txt diff --git a/Docs/Flags/italy.eps b/Docs/Flags/italy.eps index 20c7c7d5da3..20c7c7d5da3 100755..100644 --- a/Docs/Flags/italy.eps +++ b/Docs/Flags/italy.eps diff --git a/Docs/Flags/italy.txt b/Docs/Flags/italy.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/italy.txt +++ b/Docs/Flags/italy.txt diff --git a/Docs/Flags/japan.eps b/Docs/Flags/japan.eps index 8dee6e497ba..8dee6e497ba 100755..100644 --- a/Docs/Flags/japan.eps +++ b/Docs/Flags/japan.eps diff --git a/Docs/Flags/japan.txt b/Docs/Flags/japan.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/japan.txt +++ b/Docs/Flags/japan.txt diff --git a/Docs/Flags/latvia.eps b/Docs/Flags/latvia.eps new file mode 100644 index 00000000000..9c1f81f3ddc --- /dev/null +++ b/Docs/Flags/latvia.eps @@ -0,0 +1,99 @@ +%!PS-Adobe-2.0 EPSF-2.0 +%%Creator: pnmtops +%%Title: latvia.ps +%%Pages: 1 +%%BoundingBox: 295 365 317 396 +%%EndComments +/readstring { + currentfile exch readhexstring pop +} bind def +/rpicstr 32 string def +/gpicstr 32 string def +/bpicstr 32 string def +%%EndProlog +%%Page: 1 1 +gsave +295.44 365.64 translate +21.12 30.72 scale +0.5 0.5 translate 90 rotate -0.5 -0.5 translate +32 22 8 +[ 32 0 0 -22 0 22 ] +{ rpicstr readstring } +{ gpicstr readstring } +{ bpicstr readstring } +true 3 +colorimage +000000000000000000000000000000000000000000000000000000000000 +000000000000000000000000000000000000000000000000000000000000 +000000000000000000000000000000000000000000000000000000000000 +000000000000009494949494949494949494949494949494949494949494 +949494949494940000101010101010101010101010101010101010101010 +101010101010101010000018181818181818181818181818181818181818 +181818181818181818181800009494949494949494949494949494949494 +949494949494949494949494940000101010101010101010101010101010 +101010101010101010101010101010000018181818181818181818181818 +181818181818181818181818181818181800009494949494949494949494 +949494949494949494949494949494949494940000101010101010101010 +101010101010101010101010101010101010101010000018181818181818 +181818181818181818181818181818181818181818181800009494949494 +949494949494949494949494949494949494949494949494940000101010 +101010101010101010101010101010101010101010101010101010000018 +181818181818181818181818181818181818181818181818181818181800 +009494949494949494949494949494949494949494949494949494949494 +940000101010101010101010101010101010101010101010101010101010 +101010000018181818181818181818181818181818181818181818181818 +181818181800009494949494949494949494949494949494949494949494 +949494949494940000101010101010101010101010101010101010101010 +101010101010101010000018181818181818181818181818181818181818 +181818181818181818181800009494949494949494949494949494949494 +949494949494949494949494940000101010101010101010101010101010 +101010101010101010101010101010000018181818181818181818181818 +181818181818181818181818181818181800009494949494949494949494 +949494949494949494949494949494949494940000101010101010101010 +101010101010101010101010101010101010101010000018181818181818 +18181818181818181818181818181818181818181818180000ffffffffff +ffffffffffffffffffffffffffffffffffffffffffffffffff0000ffffff +ffffffffffffffffffffffffffffffffffffffffffffffffffffff0000ff +ffffffffffffffffffffffffffffffffffffffffffffffffffffffffff00 +00ffffffffffffffffffffffffffffffffffffffffffffffffffffffffff +ff0000ffffffffffffffffffffffffffffffffffffffffffffffffffffff +ffffff0000ffffffffffffffffffffffffffffffffffffffffffffffffff +ffffffffff0000ffffffffffffffffffffffffffffffffffffffffffffff +ffffffffffffff0000ffffffffffffffffffffffffffffffffffffffffff +ffffffffffffffffff0000ffffffffffffffffffffffffffffffffffffff +ffffffffffffffffffffff0000ffffffffffffffffffffffffffffffffff +ffffffffffffffffffffffffff0000ffffffffffffffffffffffffffffff +ffffffffffffffffffffffffffffff0000ffffffffffffffffffffffffff +ffffffffffffffffffffffffffffffffff00009494949494949494949494 +949494949494949494949494949494949494940000101010101010101010 +101010101010101010101010101010101010101010000018181818181818 +181818181818181818181818181818181818181818181800009494949494 +949494949494949494949494949494949494949494949494940000101010 +101010101010101010101010101010101010101010101010101010000018 +181818181818181818181818181818181818181818181818181818181800 +009494949494949494949494949494949494949494949494949494949494 +940000101010101010101010101010101010101010101010101010101010 +101010000018181818181818181818181818181818181818181818181818 +181818181800009494949494949494949494949494949494949494949494 +949494949494940000101010101010101010101010101010101010101010 +101010101010101010000018181818181818181818181818181818181818 +181818181818181818181800009494949494949494949494949494949494 +949494949494949494949494940000101010101010101010101010101010 +101010101010101010101010101010000018181818181818181818181818 +181818181818181818181818181818181800009494949494949494949494 +949494949494949494949494949494949494940000101010101010101010 +101010101010101010101010101010101010101010000018181818181818 +181818181818181818181818181818181818181818181800009494949494 +949494949494949494949494949494949494949494949494940000101010 +101010101010101010101010101010101010101010101010101010000018 +181818181818181818181818181818181818181818181818181818181800 +009494949494949494949494949494949494949494949494949494949494 +940000101010101010101010101010101010101010101010101010101010 +101010000018181818181818181818181818181818181818181818181818 +181818181800000000000000000000000000000000000000000000000000 +000000000000000000000000000000000000000000000000000000000000 +000000000000000000000000000000000000000000000000000000000000 +000000000000000000000000 +grestore +showpage +%%Trailer diff --git a/Docs/Flags/latvia.gif b/Docs/Flags/latvia.gif Binary files differnew file mode 100644 index 00000000000..8a898328ebe --- /dev/null +++ b/Docs/Flags/latvia.gif diff --git a/Docs/Flags/latvia.txt b/Docs/Flags/latvia.txt new file mode 100644 index 00000000000..e69de29bb2d --- /dev/null +++ b/Docs/Flags/latvia.txt diff --git a/Docs/Flags/russia.eps b/Docs/Flags/russia.eps index 85c5899d891..85c5899d891 100755..100644 --- a/Docs/Flags/russia.eps +++ b/Docs/Flags/russia.eps diff --git a/Docs/Flags/russia.txt b/Docs/Flags/russia.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/russia.txt +++ b/Docs/Flags/russia.txt diff --git a/Docs/Flags/south-korea.eps b/Docs/Flags/south-korea.eps index a363ab514c4..a363ab514c4 100755..100644 --- a/Docs/Flags/south-korea.eps +++ b/Docs/Flags/south-korea.eps diff --git a/Docs/Flags/south-korea.txt b/Docs/Flags/south-korea.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/south-korea.txt +++ b/Docs/Flags/south-korea.txt diff --git a/Docs/Flags/sweden.eps b/Docs/Flags/sweden.eps index 47cd1fa3e9c..47cd1fa3e9c 100755..100644 --- a/Docs/Flags/sweden.eps +++ b/Docs/Flags/sweden.eps diff --git a/Docs/Flags/sweden.txt b/Docs/Flags/sweden.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/sweden.txt +++ b/Docs/Flags/sweden.txt diff --git a/Docs/Flags/taiwan.eps b/Docs/Flags/taiwan.eps index a514bdf2af4..a514bdf2af4 100755..100644 --- a/Docs/Flags/taiwan.eps +++ b/Docs/Flags/taiwan.eps diff --git a/Docs/Flags/taiwan.txt b/Docs/Flags/taiwan.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/taiwan.txt +++ b/Docs/Flags/taiwan.txt diff --git a/Docs/Flags/usa.eps b/Docs/Flags/usa.eps index 31bd9996d11..31bd9996d11 100755..100644 --- a/Docs/Flags/usa.eps +++ b/Docs/Flags/usa.eps diff --git a/Docs/Flags/usa.txt b/Docs/Flags/usa.txt index e69de29bb2d..e69de29bb2d 100755..100644 --- a/Docs/Flags/usa.txt +++ b/Docs/Flags/usa.txt diff --git a/Docs/internals.texi b/Docs/internals.texi index 9bbd90a7a3a..e0574df550b 100644 --- a/Docs/internals.texi +++ b/Docs/internals.texi @@ -47,7 +47,7 @@ This is a manual about @strong{MySQL} internals. @menu @end menu -@node caching +@node caching,,, @chapter How MySQL handles caching @strong{MySQL} has the following caches: @@ -89,7 +89,7 @@ found rows are cached in a join cache. One SELECT query can use many join caches in the worst case. @end itemize -@node flush tables +@node flush tables,,, @chapter How MySQL handles flush tables @itemize @bullet @@ -134,7 +134,7 @@ After this it will give other threads a chance to open the same tables. @end itemize -@node Filesort +@node Filesort,,, @chapter How MySQL does sorting (filesort) @itemize @bullet @@ -174,7 +174,7 @@ and then we read the rows in the sorted order into a row buffer @end itemize -@node Coding guidelines +@node Coding guidelines,,, @chapter Coding guidelines @itemize @bullet @@ -289,7 +289,7 @@ Use pointers rather than array indexing when operating on strings. @end itemize -@node mysys functions +@node mysys functions,,, @chapter mysys functions Functions i mysys: (For flags se my_sys.h) @@ -433,6 +433,205 @@ Functions i mysys: (For flags se my_sys.h) void end_key_cache _A((void)); - End key-cacheing. +@node protocol,,, +@chapter MySQL client/server protocol + +Raw packet without compression +============================== +------------------------------------------------- +| Packet Length | Packet no | Data | +| 3 Bytes | 1 Byte | n Bytes | +------------------------------------------------- + +3 Byte packet length + The length is calculated with int3store + See include/global.h for details. + The max packetsize can be 16 MB. +1 Byte packet no + +If no compression is used the first 4 bytes of each paket +is the header of the paket. +The packet number is incremented for each sent packet. The first +packet starts with 0 + +n Byte data + +The packet length can be recalculated with: +length = byte1 + (256 * byte2) + (256 * 256 * byte3) + +Raw packet with compression +=========================== +----------------------------------------------------- +| Packet Length | Packet no | Uncomp. Packet Length | +| 3 Bytes | 1 Byte | 3 Bytes | +----------------------------------------------------- + +3 Byte packet length + The length is calculated with int3store + See include/global.h for details. + The max packetsize can be 16 MB. +1 Byte packet no +3 Byte uncompressed packet length + +If compression is used the first 7 bytes of each paket +is the header of the paket. + +Basic packets +============== +OK-packet + For details see sql/net_pkg.cc + function send_ok + ------------------------------------------------- + | Header | No of Rows | Affected Rows | + | | 1 Byte | 1-8 Byte | + ------------------------------------------------- + | ID (last_insert_id) | Status | Length | + | 1-8 Byte | 2 Byte | 1-8 Byte | + ------------------------------------------------- + | Messagetext | + | n Byte | + ------------------------------------------------- + + Header + 1 byte number of rows ? (always 0 ?) + 1-8 bytes affected rows + 1-8 byte id (last_insert_id) + 2 byte Status (usually 0) + If the OK-packege includes a message: + 1-8 bytes length of message + n bytes messagetext + +Error-packet + ------------------------------------------------- + | Header | Statuscode | Error no | + | | 1 Byte | 2 Byte | + ------------------------------------------------- + | Messagetext | 0x00 | + | n Byte | 1 Byte | + ------------------------------------------------- + + Header + 1 byte status code (0xFF = ERROR) + 2 byte error number (is only sent to new 3.23 clients. + n byte errortext + 1 byte 0x00 + + + +The communication +================= + +> Packet from server to client +< Paket from client tor server + + Login + ------ + > 1. packet + Header + 1 byte protocolversion + n byte serverversion + 1 byte 0x00 + 4 byte threadnumber + 8 byte crypt seed + 1 byte 0x00 + 2 byte CLIENT_xxx options (see include/mysql_com.h + that is supported by the server + 1 byte number of current server charset + 2 byte server status variables (SERVER_STATUS_xxx flags) + 13 byte 0x00 (not used yet). + + < 2. packet + Header + 2 byte CLIENT_xxx options + 3 byte max_allowed_packet for the client + n byte username + 1 byte 0x00 + 8 byte crypted password + 1 byte 0x00 + n byte databasename + 1 byte 0x00 + + > 3. packet + OK-packet + + + Command + -------- + < 1. packet + Header + 1 byte command type (e.g.0x03 = query) + n byte query + + Result set (after command) + -------------------------- + > 2. packet + Header + 1-8 byte field_count (packed with net_store_length()) + + If field_count == 0 (command): + 1-8 byte affected rows + 1-8 byte insert id + 2 bytes server_status (SERVER_STATUS_xx) + + If field_count == NULL_LENGTH (251) + LOAD DATA LOCAL INFILE + + If field_count > 0 Result Set: + + > n packets + Header Info + Column description: 5 data object /column + (See code in unpack_fields()) + + Columninfo for each column: + 1 data block table_name + 1 byte length of block + n byte data + 1 data block field_name + 1 byte length of block... + n byte data + 1 data block display length of field + 1 byte length of block + 3 bytes display length of filed + 1 data block type field of type (enum_field_types) + 1 byte length of block + 1 bytexs field of type + 1 data block flags + 1 byte length of block + 2 byte flags for the columns (NOT_NULL_FLAG, ZEROFILL_FLAG....) + 1 byte decimals + + if table definition: + 1 data block default value + + Actual result (one packet per row): + 4 byte header + 1-8 byte length of data + n data + + +Fieldtype Codes: +================ + + display_length |enum_field_type |flags + ---------------------------------------------------- +Blob 03 FF FF 00 |01 FC |03 90 00 00 +Mediumblob 03 FF FF FF |01 FC |03 90 00 00 +Tinyblob 03 FF 00 00 |01 FC |03 90 00 00 +Text 03 FF FF 00 |01 FC |03 10 00 00 +Mediumtext 03 FF FF FF |01 FC |03 10 00 00 +Tinytext 03 FF 00 00 |01 FC |03 10 00 00 +Integer 03 0B 00 00 |01 03 |03 03 42 00 +Mediumint 03 09 00 00 |01 09 |03 00 00 00 +Smallint 03 06 00 00 |01 02 |03 00 00 00 +Tinyint 03 04 00 00 |01 01 |03 00 00 00 +Varchar 03 XX 00 00 |01 FD |03 00 00 00 +Enum 03 05 00 00 |01 FE |03 00 01 00 +Datetime 03 13 00 00 |01 0C |03 00 00 00 +Timestamp 03 0E 00 00 |01 07 |03 61 04 00 +Time 03 08 00 00 |01 0B |03 00 00 00 +Date 03 0A 00 00 |01 0A |03 00 00 00 + @c The Index was empty, and ugly, so I removed it. (jcole, Sep 7, 2000) diff --git a/Docs/manual.texi b/Docs/manual.texi index f63e2c17ff8..3312803f7b6 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -314,7 +314,6 @@ Windows Notes * Windows and SSH:: Connecting to a remote @strong{MySQL} from Windows with SSH * Windows symbolic links:: Splitting data across different disks under Win32 * Windows compiling:: Compiling MySQL clients on Windows. -* Windows and BDB tables.:: Windows and BDB Tables * Windows vs Unix:: @strong{MySQL}-Windows compared to Unix @strong{MySQL} Post-installation Setup and Testing @@ -370,7 +369,7 @@ The MySQL Access Privilege System * Request access:: Access control, stage 2: Request verification * Privilege changes:: When privilege changes take effect * Default privileges:: Setting up the initial @strong{MySQL} privileges -* Adding users:: Adding new user privileges to @strong{MySQL} +* Adding users:: Adding new users to @strong{MySQL} * Passwords:: How to set up passwords * Access denied:: Causes of @code{Access denied} errors @@ -497,9 +496,9 @@ MySQL Table Types * MERGE:: MERGE tables * ISAM:: ISAM tables * HEAP:: HEAP tables +* InnoDB:: InnoDB tables * BDB:: BDB or Berkeley_db tables * GEMINI:: GEMINI tables -* InnoDB:: InnoDB tables MyISAM Tables @@ -518,23 +517,6 @@ MyISAM table problems. * Corrupted MyISAM tables:: * MyISAM table close:: -BDB or Berkeley_DB Tables - -* BDB overview:: Overview of BDB Tables -* BDB install:: Installing BDB -* BDB start:: BDB startup options -* BDB characteristic:: Some characteristic of @code{BDB} tables: -* BDB TODO:: Some things we need to fix for BDB in the near future: -* BDB portability:: Operating systems supported by @strong{BDB} -* BDB errors:: Errors You May Get When Using BDB Tables - -GEMINI Tables - -* GEMINI overview:: -* GEMINI start:: -* GEMINI features:: -* GEMINI TODO:: - InnoDB Tables * InnoDB overview:: InnoDB tables overview @@ -554,7 +536,7 @@ InnoDB Tables Creating InnoDB table space -* Error creating InnoDB:: +* Error creating InnoDB:: Backing up and recovering an InnoDB database @@ -581,6 +563,38 @@ File space management and disk i/o * InnoDB File space:: * InnoDB File Defragmenting:: +BDB or Berkeley_DB Tables + +* BDB overview:: Overview of BDB Tables +* BDB install:: Installing BDB +* BDB start:: BDB startup options +* BDB characteristic:: Some characteristic of @code{BDB} tables: +* BDB TODO:: Some things we need to fix for BDB in the near future: +* BDB portability:: Operating systems supported by @strong{BDB} +* BDB errors:: Errors You May Get When Using BDB Tables + +GEMINI Tables + +* GEMINI Overview:: +* Using GEMINI Tables:: + +GEMINI Overview + +* GEMINI Features:: +* GEMINI Concepts:: +* GEMINI Limitations:: + +Using GEMINI Tables + +* Startup Options:: +* Creating GEMINI Tables:: +* Backing Up GEMINI Tables:: +* Restoring GEMINI Tables:: +* Using Auto_Increment Columns With GEMINI Tables:: +* Performance Considerations:: +* Sample Configurations:: +* When To Use GEMINI Tables:: + MySQL Tutorial * Connecting-disconnecting:: Connecting to and disconnecting from the server @@ -674,6 +688,7 @@ System/Compile Time and Startup Parameter Tuning * Compile and link options:: How compiling and linking affects the speed of MySQL * Disk issues:: Disk issues +* Symbolic links:: Using Symbolic Links * Server parameters:: Tuning server parameters * Table cache:: How MySQL opens and closes tables * Creating many tables:: Drawbacks of creating large numbers of tables in the same database @@ -683,9 +698,10 @@ System/Compile Time and Startup Parameter Tuning * Table locking:: Table locking issues * DNS:: -Disk Issues +Using Symbolic Links -* Symbolic links:: Using symbolic links for databases and tables +* Symbolic links to database:: +* Symbolic links to tables:: Speed of Queries that Access or Update Data @@ -717,6 +733,7 @@ MySQL Utilites Maintaining a MySQL Installation * Table maintenance:: Table maintenance and crash recovery +* Using mysqlcheck:: Using mysqlcheck for maintenance and recovery * Maintenance regimen:: Setting up a table maintenance regimen * Table-info:: Getting information about a table * Crash recovery:: Using @code{myisamchk} for crash recovery @@ -937,6 +954,12 @@ How MySQL Compares to @code{mSQL} * Protocol differences:: How @code{mSQL} and @strong{MySQL} client/server communications protocols differ * Syntax differences:: How @code{mSQL} 2.0 SQL syntax differs from @strong{MySQL} +How MySQL Compares to PostgreSQL + +* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies +* MySQL-PostgreSQL features:: Featurevise Comparison of MySQL and PostgreSQL +* MySQL-PostgreSQL benchmarks:: Benchmarking MySQL and PostgreSQL + MySQL Internals * MySQL threads:: MySQL threads @@ -969,6 +992,7 @@ Changes in release 4.0.x (Development; Alpha) Changes in release 3.23.x (Stable) +* News-3.23.40:: Changes in release 3.23.40 * News-3.23.39:: Changes in release 3.23.39 * News-3.23.38:: Changes in release 3.23.38 * News-3.23.37:: Changes in release 3.23.37 @@ -2120,7 +2144,7 @@ The server can provide error messages to clients in many languages. @item Clients may connect to the @strong{MySQL} server using TCP/IP Sockets, -Unix Sockets (Unixes), or Named Pipes (NT). +Unix Sockets (Unix), or Named Pipes (NT). @item The @strong{MySQL}-specific @code{SHOW} command can be used to retrieve @@ -2267,8 +2291,9 @@ The Berkeley DB code is very stable, but we are still improving the interface between @strong{MySQL} and BDB tables, so it will take some time before this is as tested as the other table types. -@item InnoDB Tables -- Alpha -This is a very recent addition to @code{MySQL} and is not very tested yet. +@item InnoDB Tables -- Beta +This is a recent addition to @code{MySQL}. They appear to work good and +can be used after some initial testing. @item Automatic recovery of MyISAM tables - Beta This only affects the new code that checks if the table was closed properly @@ -2586,6 +2611,10 @@ M2D, a @strong{MySQL} Administration client for Windows. M2D supports administration of @strong{MySQL} databases, creation of new databases and tables, editing, and more. +@item @uref{http://dlabs.4t2.com} +Dexter, a small server written in Perl which can be used as a proxy server for +@strong{MySQL} or as a database extender. + @item @uref{http://www.scibit.com/Products/Software/Utils/Mascon.asp} Mascon is a powerful Win32 GUI for administering MySQL databases. @@ -2835,7 +2864,7 @@ PTS: Project Tracking System. @item @uref{http://tomato.nvgc.vt.edu/~hroberts/mot} Job and software tracking system. -@item @uref{http://www.cynergi.net/non-secure/exportsql/} +@item @uref{http://www.cynergi.net/exportsql/} ExportSQL: A script to export data from Access95+. @item @uref{http://SAL.KachinaTech.COM/H/1/MYSQL.html} @@ -3060,21 +3089,21 @@ from the local @strong{MySQL} list. The following @strong{MySQL} mailing lists exist: @table @code -@item @email{announce-subscribe@@lists.mysql.com, announce} +@item @email{announce-subscribe@@lists.mysql.com} announce This is for announcement of new versions of @strong{MySQL} and related programs. This is a low volume list all @strong{MySQL} users should subscribe to. -@item @email{mysql-subscribe@@lists.mysql.com, mysql} +@item @email{mysql-subscribe@@lists.mysql.com} mysql The main list for general @strong{MySQL} discussion. Please note that some topics are better discussed on the more-specialized lists. If you post to the wrong list, you may not get an answer! -@item @email{mysql-digest-subscribe@@lists.mysql.com, mysql-digest} +@item @email{mysql-digest-subscribe@@lists.mysql.com} mysql-digest The @code{mysql} list in digest form. That means you get all individual messages, sent as one large mail message once a day. -@item @email{bugs-subscribe@@lists.mysql.com, bugs} +@item @email{bugs-subscribe@@lists.mysql.com} bugs On this list you should only post a full, repeatable bug report using the @code{mysqlbug} script (if you are running on Windows, you should include a description of the operating system and the @strong{MySQL} version). @@ -3085,55 +3114,45 @@ bugs posted on this list will be corrected or documented in the next @strong{MySQL} release! If there are only small code changes involved, we will also post a patch that fixes the problem. -@item @email{bugs-digest-subscribe@@lists.mysql.com, bugs-digest} +@item @email{bugs-digest-subscribe@@lists.mysql.com} bugs-digest The @code{bugs} list in digest form. -@item @email{developer-subscribe@@lists.mysql.com, developer} -This list has been depreciated in favor of the -@email{internals-subscribe@@lists.mysql.com, internals} list (below). - -@item @email{developer-digest-subscribe@@lists.mysql.com, developer-digest} -This list has been deprecated in favor of the -@email{internals-digest-subscribe@@lists.mysql.com, internals-digest} -list (below). - -@item @email{internals-subscribe@@lists.mysql.com, internals} +@item @email{internals-subscribe@@lists.mysql.com} internals A list for people who work on the @strong{MySQL} code. On this list one can also discuss @strong{MySQL} development and post patches. -@item @email{internals-digest-subscribe@@lists.mysql.com, internals-digest} -A digest version of the @email{internals-subscribe@@lists.mysql.com, internals} -list. +@item @email{internals-digest-subscribe@@lists.mysql.com} internals-digest +A digest version of the @code{internals} list. -@item @email{java-subscribe@@lists.mysql.com, java} +@item @email{java-subscribe@@lists.mysql.com} java Discussion about @strong{MySQL} and Java. Mostly about the JDBC drivers. -@item @email{java-digest-subscribe@@lists.mysql.com, java-digest} +@item @email{java-digest-subscribe@@lists.mysql.com} java-digest A digest version of the @code{java} list. -@item @email{win32-subscribe@@lists.mysql.com, win32} +@item @email{win32-subscribe@@lists.mysql.com} win32 All things concerning @strong{MySQL} on Microsoft operating systems such as Win95, Win98, NT, and Win2000. -@item @email{win32-digest-subscribe@@lists.mysql.com, win32-digest} +@item @email{win32-digest-subscribe@@lists.mysql.com} win32-digest A digest version of the @code{win32} list. -@item @email{myodbc-subscribe@@lists.mysql.com, myodbc} +@item @email{myodbc-subscribe@@lists.mysql.com} myodbc All things about connecting to @strong{MySQL} with ODBC. -@item @email{myodbc-digest-subscribe@@lists.mysql.com, myodbc-digest} +@item @email{myodbc-digest-subscribe@@lists.mysql.com} myodbc-digest A digest version of the @code{myodbc} list. -@item @email{plusplus-subscribe@@lists.mysql.com, plusplus} +@item @email{plusplus-subscribe@@lists.mysql.com} plusplus All things concerning programming with the C++ API to @strong{MySQL}. -@item @email{plusplus-digest-subscribe@@lists.mysql.com, plusplus-digest} +@item @email{plusplus-digest-subscribe@@lists.mysql.com} plusplus-digest A digest version of the @code{plusplus} list. -@item @email{msql-mysql-modules-subscribe@@lists.mysql.com, msql-mysql-modules} -A list about the Perl support in @strong{MySQL}. +@item @email{msql-mysql-modules-subscribe@@lists.mysql.com} msql-mysql-modules +A list about the Perl support in @strong{MySQL}. msql-mysql-modules -@item @email{msql-mysql-modules-digest-subscribe@@lists.mysql.com, msql-mysql-modules-digest} +@item @email{msql-mysql-modules-digest-subscribe@@lists.mysql.com} msql-mysql-modules-digest A digest version of the @code{msql-mysql-modules} list. @end table @@ -3149,16 +3168,16 @@ English. Note that these are not operated by @strong{MySQL AB}, so we can't guarantee the quality on these. @table @code -@item @email{mysql-france-subscribe@@yahoogroups.com, A French mailing list} -@item @email{list@@tinc.net, A Korean mailing list} +@item @email{mysql-france-subscribe@@yahoogroups.com} A French mailing list +@item @email{list@@tinc.net} A Korean mailing list Email @code{subscribe mysql your@@email.address} to this list. -@item @email{mysql-de-request@@lists.4t2.com, A German mailing list} +@item @email{mysql-de-request@@lists.4t2.com} A German mailing list Email @code{subscribe mysql-de your@@email.address} to this list. You can find information about this mailing list at @uref{http://www.4t2.com/mysql}. -@item @email{mysql-br-request@@listas.linkway.com.br, A Portugese mailing list} +@item @email{mysql-br-request@@listas.linkway.com.br} A Portugese mailing list Email @code{subscribe mysql-br your@@email.address} to this list. -@item @email{mysql-alta@@elistas.net, A Spanish mailing list} +@item @email{mysql-alta@@elistas.net} A Spanish mailing list Email @code{subscribe mysql your@@email.address} to this list. @end table @@ -3564,7 +3583,7 @@ Note that older versions of @strong{MySQL} are still using a more @uref{http://www.mysql.com/support/arrangements/mypl.html, strict license}. See the documentation for that version for more information. If you need a commercial @strong{MySQL} license, because the GPL license doesn't suit your -application, you can buy one at @uref{https://order.mysql.com/license.htmy}. +application, you can buy one at @uref{https://order.mysql.com/}. For normal internal use, @strong{MySQL} costs nothing. You do not have to pay us if you do not want to. @@ -3629,7 +3648,7 @@ contact us. @xref{Contact information}. If you require a @strong{MySQL} license, the easiest way to pay for it is to use the license form on @strong{MySQL}'s secure server at -@uref{https://order.mysql.com/license.htmy}. Other forms of payment are +@uref{https://order.mysql.com/}. Other forms of payment are discussed in @ref{Payment information}. @cindex copyrights @@ -3906,7 +3925,7 @@ BOX 6434, Torsgatan 21 @end example If you want to pay by credit card over the Internet, you can use -@uref{https://order.mysql.com/license.htmy, MySQL AB's secure license form}. +@uref{https://order.mysql.com/, MySQL AB's secure license form}. You can also print a copy of the license form, fill it in, and send it by fax to: @@ -4517,6 +4536,12 @@ Please report bad or out-of-date mirrors to @email{webmaster@@mysql.com}. @uref{ftp://ftp.esat.net/mirrors/download.sourceforge.net/pub/mirrors/mysql/, FTP} @item +@c Added 20010524 +@c EMAIL: arvids@parks.lv (Arvids) +@image{Flags/latvia} Latvia [linux.lv] @ +@uref{ftp://ftp.linux.lv/pub/software/mysql/, FTP} + +@item @c Added 20001125 @c EMAIL: mleicher@silverpoint.nl (Marcel Leicher) @image{Flags/netherlands} Netherlands [Silverpoint] @ @@ -4719,7 +4744,7 @@ Please report bad or out-of-date mirrors to @email{webmaster@@mysql.com}. @c Added 20000925 @image{Flags/usa} USA [ValueClick, Los Angeles CA] @ @uref{http://mysql.valueclick.com/, WWW} -@uref{ftp://mysql.valueclick.com/mysql/, FTP} +@uref{ftp://mysql.valueclick.com/pub/mysql/Downloads/, FTP} @c @item @c Not ok 20000919; Non-existent (Matt) @@ -4796,10 +4821,10 @@ Please report bad or out-of-date mirrors to @email{webmaster@@mysql.com}. @uref{http://mysql.linuxwired.net/, WWW} @uref{ftp://ftp.linuxwired.net/pub/mirrors/mysql/, FTP} -@item +@c @item @c EMAIL: dan@surfsouth.com (Dan Muntz) -@image{Flags/usa} USA [Venoma.Org/Valdosta, GA] @ -@uref{http://mysql.venoma.org/, WWW} +@c @image{Flags/usa} USA [Venoma.Org/Valdosta, GA] @ +@c @uref{http://mysql.venoma.org/, WWW} @item @c EMAIL: hkind@adgrafix.com (Hans Kind) @@ -4999,8 +5024,8 @@ Please report bad or out-of-date mirrors to @email{webmaster@@mysql.com}. @c Added 980610 @c EMAIL: jason@dstc.edu.au (Jason Andrade) @image{Flags/australia} Australia [AARNet/Queensland] @ -@uref{http://mirror.aarnet.edu.au/mysql, WWW} -@uref{ftp://mirror.aarnet.edu.au/pub/mysql, FTP} +@uref{http://mysql.mirror.aarnet.edu.au/, WWW} +@uref{ftp://mysql.mirror.aarnet.edu.au/, FTP} @c @item @c Added 980805. Removed 000102 'no such directory' @@ -5236,7 +5261,7 @@ clients can connect to both @strong{MySQL} versions. The extended @strong{MySQL} binary distribution is marked with the @code{-max} suffix and is configured with the same options as -@code{mysqld-max}. @xref{mysqld-max}. +@code{mysqld-max}. @xref{mysqld-max, , @code{mysqld-max}}. If you want to use the @code{MySQL-Max} RPM, you must first install the standard @code{MySQL} RPM. @@ -5577,8 +5602,8 @@ indicates the type of operating system for which the distribution is intended @item If you see a binary distribution marked with the @code{-max} prefix, this means that the binary has support for transaction-safe tables and other -features. @xref{mysqld-max}. Note that all binaries are built from -the same @strong{MySQL} source distribution. +features. @xref{mysqld-max, , @code{mysqld-max}}. Note that all binaries +are built from the same @strong{MySQL} source distribution. @item Add a user and group for @code{mysqld} to run as: @@ -5590,8 +5615,8 @@ shell> useradd -g mysql mysql These commands add the @code{mysql} group and the @code{mysql} user. The syntax for @code{useradd} and @code{groupadd} may differ slightly on different -Unixes. They may also be called @code{adduser} and @code{addgroup}. You may -wish to call the user and group something else instead of @code{mysql}. +versions of Unix. They may also be called @code{adduser} and @code{addgroup}. +You may wish to call the user and group something else instead of @code{mysql}. @item Change into the intended installation directory: @@ -5634,7 +5659,8 @@ programs properly. @xref{Environment variables}. @item scripts This directory contains the @code{mysql_install_db} script used to initialize -the server access permissions. +the @code{mysql} database containing the grant tables that store the server +access permissions. @end table @item @@ -5700,7 +5726,7 @@ You can start the @strong{MySQL} server with the following command: shell> bin/safe_mysqld --user=mysql & @end example -@xref{safe_mysqld}. +@xref{safe_mysqld, , @code{safe_mysqld}}. @xref{Post-installation}. @@ -5772,7 +5798,7 @@ installation, you may want to make a copy of your previously installed @strong{MySQL} startup file if you made any changes to it, so you don't lose your changes.) -After installing the RPM file(s), the @file{mysqld} daemon should be running +After installing the RPM file(s), the @code{mysqld} daemon should be running and you should now be able to start using @strong{MySQL}. @xref{Post-installation}. @@ -5808,7 +5834,7 @@ files. The following sections indicate some of the issues that have been observed on particular systems when installing @strong{MySQL} from a binary -distribution. +distribution or from RPM files. @cindex binary distributions, on Linux @cindex Linux, binary distribution @@ -6104,8 +6130,8 @@ shell> useradd -g mysql mysql These commands add the @code{mysql} group, and the @code{mysql} user. The syntax for @code{useradd} and @code{groupadd} may differ slightly on different -Unixes. They may also be called @code{adduser} and @code{addgroup}. You may -wish to call the user and group something else instead of @code{mysql}. +versions of Unix. They may also be called @code{adduser} and @code{addgroup}. +You may wish to call the user and group something else instead of @code{mysql}. @item Unpack the distribution into the current directory: @@ -6416,6 +6442,7 @@ shell> CXXFLAGS=-DDONT_USE_DEFAULT_FIELDS ./configure @cindex character sets @findex configure option, --with-charset +@findex configure option, --with-extra-charset @item By default, @strong{MySQL} uses the ISO-8859-1 (Latin1) character set. To change the default set, use the @code{--with-charset} option: @@ -6441,6 +6468,13 @@ indexes may be sorted incorrectly otherwise. (This can happen if you install @strong{MySQL}, create some tables, then reconfigure @strong{MySQL} to use a different character set and reinstall it.) +With the option @code{--with-extra-charset=LIST} you can define +which additional character sets should be incompiled in the server. + +Here @code{LIST} is either a list of character set separated with space, +@code{complex} to include all characters that can't be dynamicly loaded +or @code{all} to include all character sets into the binaries. + @item To configure @strong{MySQL} with debugging code, use the @code{--with-debug} option: @@ -6460,8 +6494,8 @@ applications. @xref{Thread-safe clients}. @item Options that pertain to particular systems can be found in the -system-specific sections later in this chapter. -@xref{Source install system issues}. +system-specific sections later in this chapter. @xref{Source install +system issues}. @end itemize @node Installing source tree, Compilation problems, Installing source, Installing @@ -7304,6 +7338,10 @@ with @code{--static}. If you try to do so, you will get the error: @example ld: fatal: library -ldl: not found + +or + +undefined reference to `dlopen' @end example If too many processes try to connect very rapidly to @code{mysqld}, you will @@ -7449,6 +7487,9 @@ Configure @strong{MySQL} with the @code{--with-named-z-libs=no} option. @node Solaris x86, SunOS, Solaris 2.7, Source install system issues @subsection Solaris x86 Notes +On Solaris 2.8 on x86, @strong{mysqld} will core dump if you run +'strip' in. + If you are using @code{gcc} or @code{egcs} on Solaris x86 and you experience problems with core dumps under load, you should use the following @code{configure} command: @@ -7507,6 +7548,11 @@ Linux version that doesn't have @code{glibc2}, you must install LinuxThreads before trying to compile @strong{MySQL}. You can get LinuxThreads at @uref{http://www.mysql.com/Downloads/Linux}. +@strong{NOTE:} We have seen some strange problems with Linux 2.2.14 and +@strong{MySQL} on SMP systems; If you have a SMP system, we recommend +you to upgrade to Linux 2.4 ASAP! Your system will be faster and more +stable by doing this! + Note that @code{glibc} versions before and including Version 2.1.1 have a fatal bug in @code{pthread_mutex_timedwait} handling, which is used when you do @code{INSERT DELAYED}. We recommend you to not use @@ -7521,6 +7567,40 @@ relative to the root of @code{glibc} Note that @strong{MySQL} will not be stable with around 600-1000 connections if @code{STACK_SIZE} is the default of 2 MB. +If you have a problem with that @strong{MySQL} can't open enough files, +or connections, it may be that you haven't configured Linux to handle +enough files. + +In Linux 2.2 and forwards, you can check the number of allocated +file handlers by doing: + +@example +cat /proc/sys/fs/file-max +cat /proc/sys/fs/dquot-max +cat /proc/sys/fs/super-max +@end example + +If you have more than 16M of memory, you should add something like the +following in your boot script (@file{/etc/rc/boot.local} on SuSE): + +@example +echo 65536 > /proc/sys/fs/file-max +echo 8192 > /proc/sys/fs/dquot-max +echo 1024 > /proc/sys/fs/super-max +@end example + +You can also run the above from the command line as root, but in this case +your old limits will be used next time your computer reboots. + +You should also add /etc/my.cnf: + +@example +[safe_mysqld] +open_files_limit=8192 +@end example + +The above should allow @strong{MySQL} to create up to 8192 connections + files. + The @code{STACK_SIZE} constant in LinuxThreads controls the spacing of thread stacks in the address space. It needs to be large enough so that there will be plenty of room for the stack of each individual thread, but small enough @@ -7659,13 +7739,13 @@ To get a core dump on Linux if @code{mysqld} dies with a SIGSEGV signal, you can start @code{mysqld} with the @code{--core-file} option. Note that you also probably need to raise the @code{core file size} by adding @code{ulimit -c 1000000} to @code{safe_mysqld} or starting @code{safe_mysqld} -with @code{--core-file-sizes=1000000}. @xref{safe_mysqld}. +with @code{--core-file-sizes=1000000}. @xref{safe_mysqld, , @code{safe_mysqld}}. To get a core dump on Linux if @code{mysqld} dies with a SIGSEGV signal, you can start @code{mysqld} with the @code{--core-file} option. Note that you also probably need to raise the @code{core file size} by adding @code{ulimit -c 1000000} to @code{safe_mysqld} or starting @code{safe_mysqld} with -@code{--core-file-sizes=1000000}. @xref{safe_mysqld}. +@code{--core-file-sizes=1000000}. @xref{safe_mysqld, , @code{safe_mysqld}}. If you are linking your own @strong{MySQL} client and get the error: @@ -7993,7 +8073,7 @@ shell> nohup mysqld [options] & @code{nohup} causes the command following it to ignore any @code{SIGHUP} signal sent from the terminal. Alternatively, start the server by running @code{safe_mysqld}, which invokes @code{mysqld} using @code{nohup} for you. -@xref{safe_mysqld}. +@xref{safe_mysqld, , @code{safe_mysqld}}. If you get a problem when compiling mysys/get_opt.c, just remove the line #define _NO_PROTO from the start of that file! @@ -8187,6 +8267,17 @@ CC=gcc CXX=gcc CXXFLAGS=-O3 \ ./configure --prefix=/usr/local/mysql --with-thread-safe-client --with-named-thread-libs=-lpthread @end example +On Irix 6.5.11 with native Irix C and C++ compilers ver. 7.3.1.2, the +following is reported to work + +@example +CC=cc CXX=CC CFLAGS='-O3 -n32 -TARG:platform=IP22 -I/usr/local/include \ +-L/usr/local/lib' CXXFLAGS='-O3 -n32 -TARG:platform=IP22 \ +-I/usr/local/include -L/usr/local/lib' ./configure --prefix=/usr/local/mysql \ +--with-berkeley-db --with-innodb \ +--with-libwrap=/usr/local --with-named-curses-libs=/usr/local/lib/libncurses.a +@end example + @node FreeBSD, NetBSD, SGI-Irix, Source install system issues @subsection FreeBSD Notes @@ -8250,7 +8341,8 @@ FreeBSD is also known to have a very low default file handle limit. safe_mysqld or raise the limits for the @code{mysqld} user in /etc/login.conf (and rebuild it with cap_mkdb /etc/login.conf). Also be sure you set the appropriate class for this user in the password file if you are not -using the default (use: chpass mysqld-user-name). @xref{safe_mysqld}. +using the default (use: chpass mysqld-user-name). @xref{safe_mysqld, , +@code{safe_mysqld}}. If you get problems with the current date in @strong{MySQL}, setting the @code{TZ} variable will probably help. @xref{Environment variables}. @@ -8479,7 +8571,7 @@ The following @code{configure} command should work: @example shell> CFLAGS="-D_XOPEN_XPG4" CXX=gcc CXXFLAGS="-D_XOPEN_XPG4" \ ./configure \ - --with-debug --prefix=/usr/local/mysql \ + --prefix=/usr/local/mysql \ --with-named-thread-libs="-lgthreads -lsocket -lgen -lgthreads" \ --with-named-curses-libs="-lcurses" @end example @@ -8702,10 +8794,10 @@ the DCE libraries while you compile @code{gcc} 2.95! @node HP-UX 11.x, Mac OS X, HP-UX 10.20, Source install system issues @subsection HP-UX Version 11.x Notes -For HPUX Version 11.x we recommend @strong{MySQL} Version 3.23.15 or later. +For HP-UX Version 11.x we recommend @strong{MySQL} Version 3.23.15 or later. -Because of some critical bugs in the standard HPUX libraries, one should -install the following patches before trying to run @strong{MySQL} on HPUX 11.0: +Because of some critical bugs in the standard HP-UX libraries, you should +install the following patches before trying to run @strong{MySQL} on HP-UX 11.0: @example PHKL_22840 Streams cumulative @@ -8715,7 +8807,7 @@ PHNE_22397 ARPA cumulative This will solve a problem that one gets @code{EWOULDBLOCK} from @code{recv()} and @code{EBADF} from @code{accept()} in threaded applications. -If you are using @code{gcc} 2.95.1 on an unpatched HPUX 11.x system, +If you are using @code{gcc} 2.95.1 on an unpatched HP-UX 11.x system, you will get the error: @example @@ -8754,8 +8846,8 @@ After this, the following configure line should work: CFLAGS="-fomit-frame-pointer -O3 -fpic" CXX=gcc CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti -O3" ./configure --prefix=/usr/local/mysql --disable-shared @end example -Here is some information that a HPUX Version 11.x user sent us about compiling -@strong{MySQL} with HPUX:x compiler: +Here is some information that a HP-UX Version 11.x user sent us about compiling +@strong{MySQL} with HP-UX:x compiler: @example Environment: @@ -8865,8 +8957,8 @@ in a while. @section Windows Notes This section describes installation and use of @strong{MySQL} on Windows. -This is also described in the @file{README} file that comes with the -@strong{MySQL} Windows distribution. +This information is also provided in the @file{README} file that comes +with the @strong{MySQL} Windows distribution. @menu * Windows installation:: Installing @strong{MySQL} on Windows @@ -8876,13 +8968,16 @@ This is also described in the @file{README} file that comes with the * Windows and SSH:: Connecting to a remote @strong{MySQL} from Windows with SSH * Windows symbolic links:: Splitting data across different disks under Win32 * Windows compiling:: Compiling MySQL clients on Windows. -* Windows and BDB tables.:: Windows and BDB Tables * Windows vs Unix:: @strong{MySQL}-Windows compared to Unix @strong{MySQL} @end menu @node Windows installation, Win95 start, Windows, Windows @subsection Installing MySQL on Windows +The following instructions apply to precompiled binary distributions. +If you download a source distribution, you will have to compile and install +it yourself. + If you don't have a copy of the @strong{MySQL} distribution, you should first download one from @uref{http://www.mysql.com/downloads/mysql-3.23.html}. @@ -8895,23 +8990,30 @@ To install either distribution, unzip it in some empty directory and run the @code{Setup.exe} program. By default, @strong{MySQL}-Windows is configured to be installed in -@file{C:\mysql}. If you want to install @strong{MySQL} elsewhere, install it -in @file{C:\mysql} first, then move the installation to where you want it. If -you do move @strong{MySQL}, you must tell @code{mysqld} where everything is by -supplying options to @code{mysqld}. Use @code{C:\mysql\bin\mysqld --help} to -display all options! For example, if you have moved the @strong{MySQL} -distribution to @file{D:\programs\mysql}, you must start @code{mysqld} with: -@code{D:\programs\mysql\bin\mysqld --basedir D:\programs\mysql} +@file{C:\mysql}. If you want to install @strong{MySQL} elsewhere, +install it in @file{C:\mysql} first, then move the installation to +where you want it. If you do move @strong{MySQL}, you must indicate +where everything is located by supplying a @code{--basedir} option when +you start the server. For example, if you have moved the @strong{MySQL} +distribution to @file{D:\programs\mysql}, you must start @code{mysqld} +like this: + +@example +C:\> D:\programs\mysql\bin\mysqld --basedir D:\programs\mysql +@end example + +Use @code{mysqld --help} to display all the options that @code{mysqld} +understands! With all newer @strong{MySQL} versions, you can also create a @file{C:\my.cnf} file that holds any default options for the @strong{MySQL} server. Copy the file @file{\mysql\my-xxxxx.cnf} to @file{C:\my.cnf} and edit it to suit your setup. Note that you should specify all paths with @samp{/} instead of @samp{\}. If you use -@samp{\}, you need to specify it twice, as @samp{\} is the escape +@samp{\}, you need to specify it twice, because @samp{\} is the escape character in @strong{MySQL}. @xref{Option files}. -Starting from @strong{MySQL} 3.23.38, the Windows distribution includes +Starting with @strong{MySQL} 3.23.38, the Windows distribution includes both the normal and the @strong{MySQL-Max} binaries. The main benefit of using the normal @code{mysqld.exe} binary is that it's a little faster and uses less resources. @@ -8925,9 +9027,8 @@ symbolic links, BDB and InnoDB tables. @item @code{mysqld-opt} @tab Optimized binary with no support for transactional tables. @item @code{mysqld-nt} @tab -Optimized for a Pentium Pro processor. Has support for -named pipes. You can run this version on Win98, but in -this case no named pipes are created and you must +Optimized binary for NT with support for named pipes. You can run this +version on Win98, but in this case no named pipes are created and you must have TCP/IP installed. @item @code{mysqld-max} @tab Optimized binary with support for symbolic links, BDB and InnoDB tables. @@ -8969,14 +9070,19 @@ You can kill the @strong{MySQL} server by executing: C:\> C:\mysql\bin\mysqladmin -u root shutdown @end example -Note that Win95 and Win98 don't support creation of named pipes. On -Win95 and Win98, you can only use named pipes to connect to a remote -@strong{MySQL} running on an NT server. +Note that Win95 and Win98 don't support creation of named pipes. +On Win95 and Win98, you can only use named pipes to connect to a +remote @strong{MySQL} server running on a Windows NT server host. +(The @strong{MySQL} server must also support named pipes, of +course. For example, using @code{mysqld-opt} under NT will not allow +named pipe connections. You should use either @code{mysqld-nt} or +@code{mysqld-max-nt}.) -If @code{mysqld} doesn't start, please check whether or not the -@file{\mysql\mysql.err} file contains any reason for this. You can also -try to start the server with @code{mysqld --standalone}; In this case, you may -get some useful information on the screen that may help solve the problem. +If @code{mysqld} doesn't start, please check the +@file{\mysql\data\mysql.err} file to see if the server wrote any message +there to indicate the cause of the problem. You can also try to start +the server with @code{mysqld --standalone}; In this case, you may get +some useful information on the screen that may help solve the problem. The last option is to start @code{mysqld} with @code{--standalone --debug}. In this case @code{mysqld} will write a log file @@ -9008,9 +9114,9 @@ or C:\> C:\mysql\bin\mysqld-max-nt --install @end example -(You can also use @code{mysqld} binaries that don't end with -@code{-nt.exe} on NT, but those cannot be started as a service or use -named pipes.) +(Under Windows NT, you can actually install any of the server binaries +as a service, but only those having names that end with @code{-nt.exe} +provide support for named pipes.) You can start and stop the @strong{MySQL} service with these commands: @@ -9028,9 +9134,9 @@ with the default service options. If you have stopped @code{mysqld-nt}, you have to start it with @code{NET START mysql}. The service is installed with the name @code{MySQL}. Once installed, it must -be started using the Services Control Manager (SCM) Utility (found in Control -Panel) or by using the @code{NET START MySQL} command. If any options are -desired, they must be specified as ``Startup parameters'' in the SCM utility +be started using the Services Control Manager (SCM) Utility found in the +Control Panel, or by using the @code{NET START MySQL} command. If any options +are desired, they must be specified as ``Startup parameters'' in the SCM utility before you start the @strong{MySQL} service. Once running, @code{mysqld-nt} can be stopped using @code{mysqladmin}, or from the SCM utility or by using the command @code{NET STOP MySQL}. If you use SCM to stop @code{mysqld-nt}, @@ -9148,14 +9254,12 @@ server, you can do so using this command: C:\> mysqladmin --user=root --password=your_password shutdown @end example -If you are using the old shareware version of @strong{MySQL} Version 3.21 -under Windows, the above command will fail with an error: @code{parse error -near 'SET OPTION password'}. This is because the old shareware version, -which is based on @strong{MySQL} Version 3.21, doesn't have the -@code{SET PASSWORD} command. The fix is in this case to upgrade to -the Version 3.22 shareware. +If you are using the old shareware version of @strong{MySQL} Version +3.21 under Windows, the above command will fail with an error: +@code{parse error near 'SET OPTION password'}. The fix is in to upgrade +to the current @strong{MySQL} version, which is freely available. -With the newer @strong{MySQL} versions you can easily add new users +With the current @strong{MySQL} versions you can easily add new users and change privileges with @code{GRANT} and @code{REVOKE} commands. @xref{GRANT}. @@ -9170,7 +9274,7 @@ Here is a note about how to connect to get a secure connection to remote @itemize @bullet @item -Install an SSH client on your Windows machine --- As a user, the best non-free +Install an SSH client on your Windows machine. As a user, the best non-free one I've found is from @code{SecureCRT} from @uref{http://www.vandyke.com/}. Another option is @code{f-secure} from @uref{http://www.f-secure.com/}. You can also find some free ones on @strong{Google} at @@ -9224,12 +9328,26 @@ Note that the symbolic link will be used only if the directory For example, if the @strong{MySQL} data directory is @file{C:\mysql\data} and you want to have database @code{foo} located at @file{D:\data\foo}, you should create the file @file{C:\mysql\data\foo.sym} that contains the -text @code{D:\data\foo}. After that, all tables created in the database +text @code{D:\data\foo\}. After that, all tables created in the database @code{foo} will be created in @file{D:\data\foo}. +Note that because of the speed penalty you get when opening every table, +we have not enabled this by default even if you have compiled +@strong{MySQL} with support for this. To enable symlinks you should put +in your @code{my.cnf} or @code{my.ini} file the following entry: + +@example +[mysqld] +use-symbolic-links +@end example + +In @strong{MySQL} 4.0 we will enable symlinks by default. Then you +should instead use the @code{skip-symlink} option if you want to +disable this. + @cindex compiling, on Windows @cindex Windows, compiling on -@node Windows compiling, Windows and BDB tables., Windows symbolic links, Windows +@node Windows compiling, Windows vs Unix, Windows symbolic links, Windows @subsection Compiling MySQL Clients on Windows In your source files, you should include @file{windows.h} before you include @@ -9249,19 +9367,9 @@ with the static @file{mysqlclient.lib} library. Note that as the mysqlclient libraries are compiled as threaded libraries, you should also compile your code to be multi-threaded! -@cindex BDB tables -@cindex tables, BDB -@node Windows and BDB tables., Windows vs Unix, Windows compiling, Windows -@subsection Windows and BDB Tables - -We will shortly do a full test on the new BDB interface on Windows. -When this is done we will start to release binary distributions (for -Windows and Unix) of @strong{MySQL} that will include support for BDB -tables. - @cindex Windows, versus Unix @cindex operating systems, Windows versus Unix -@node Windows vs Unix, , Windows and BDB tables., Windows +@node Windows vs Unix, , Windows compiling, Windows @subsection MySQL-Windows Compared to Unix MySQL @strong{MySQL}-Windows has by now proven itself to be very stable. This version @@ -9419,11 +9527,6 @@ For the moment, the workaround is to list the parameters in the @file{C:\my.cnf} file instead. @item -When you suspend a laptop running Win95, the @code{mysqld} daemon doesn't -accept new connections when the laptop is resumed. We don't know if this -is a problem with Win95, TCP/IP, or @strong{MySQL}. - -@item It would be real nice to be able to kill @code{mysqld} from the task manager. For the moment, you must use @code{mysqladmin shutdown}. @@ -9527,20 +9630,17 @@ and are configured with the following compilers and options: @item SunOS 4.1.4 2 sun4c with @code{gcc} 2.7.2.1 @code{CC=gcc CXX=gcc CXXFLAGS="-O3 -felide-constructors" ./configure --prefix=/usr/local/mysql --disable-shared --with-extra-charsets=complex --enable-assembler} -@item SunOS 5.5.1 sun4u with @code{egcs} 1.0.3a -@code{CC=gcc CFLAGS="-O3 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex} - -@item SunOS 5.6 sun4u with @code{egcs} 2.90.27 -@code{CC=gcc CFLAGS="-O3 -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex} +@item SunOS 5.5.1 (and above) sun4u with @code{egcs} 1.0.3a or 2.90.27 or gcc 2.95.2 and newer +@code{CC=gcc CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex --enable-assembler} @item SunOS 5.6 i86pc with @code{gcc} 2.8.1 @code{CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-low-memory --with-extra-charsets=complex} @item Linux 2.0.33 i386 with @code{pgcc} 2.90.29 (@code{egcs} 1.0.3a) -@code{CFLAGS="-O3 -mpentium -mstack-align-double -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O3 -mpentium -mstack-align-double -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --with-extra-charsets=complex} +@code{CFLAGS="-O3 -mpentium -mstack-align-double" CXX=gcc CXXFLAGS="-O3 -mpentium -mstack-align-double -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --with-extra-charsets=complex} @item Linux 2.2.x with x686 with @code{gcc} 2.95.2 -@code{CFLAGS="-O3 -mpentiumpro -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-extra-charset=complex} +@code{CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static --disable-shared --with-extra-charset=complex} @item SCO 3.2v5.0.4 i386 with @code{gcc} 2.7-95q4 @code{CC=gcc CXX=gcc CXXFLAGS=-O3 ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex} @@ -9604,7 +9704,7 @@ shell> cd mysql_installation_directory shell> ./bin/safe_mysqld --user=mysql & @end example -For a binary distribution, do this: +For a binary distribution (not RPM or pkg packages), do this: @example shell> cd mysql_installation_directory @@ -9678,7 +9778,7 @@ mysqld: Can't find file: 'host.frm' The above may also happen with a binary @strong{MySQL} distribution if you don't start @strong{MySQL} by executing exactly @code{./bin/safe_mysqld}! -@xref{safe_mysqld}. +@xref{safe_mysqld, , @code{safe_mysqld}}. You might need to run @code{mysql_install_db} as @code{root}. However, if you prefer, you can run the @strong{MySQL} server as an unprivileged @@ -9979,7 +10079,8 @@ system startup and shutdown, and is described more fully in @item By invoking @code{safe_mysqld}, which tries to determine the proper options -for @code{mysqld} and then runs it with those options. @xref{safe_mysqld}. +for @code{mysqld} and then runs it with those options. @xref{safe_mysqld, , +@code{safe_mysqld}}. @item On NT you should install @code{mysqld} as a service as follows: @@ -10035,7 +10136,7 @@ correctly, check the log file to see if you can find out why. Log files are located in the data directory (typically @file{/usr/local/mysql/data} for a binary distribution, @file{/usr/local/var} for a source distribution, -@file{\mysql\mysql.err} on Windows.) Look in the data directory for +@file{\mysql\data\mysql.err} on Windows.) Look in the data directory for files with names of the form @file{host_name.err} and @file{host_name.log} where @code{host_name} is the name of your server host. Then check the last few lines of these files: @@ -10111,14 +10212,14 @@ library and for which @strong{MySQL} must be configured to use MIT-pthreads. If you can't get @code{mysqld} to start you can try to make a trace file to find the problem. @xref{Making trace files}. +If you are using InnoDB tables, refer to the InnoDB-specific startup +options. @xref{InnoDB start}. + If you are using BDB (Berkeley DB) tables, you should familiarize yourself with the different BDB specific startup options. @xref{BDB start}. If you are using Gemini tables, refer to the Gemini-specific startup options. -@xref{GEMINI start}. - -If you are using InnoDB tables, refer to the InnoDB-specific startup -options. @xref{InnoDB start}. +@xref{Using GEMINI Tables}. @node Automatic start, Command-line options, Starting server, Post-installation @subsection Starting and Stopping MySQL Automatically @@ -10126,6 +10227,10 @@ options. @xref{InnoDB start}. @cindex stopping, the server @cindex server, starting and stopping +The @code{mysql.server} and @code{safe_mysqld} scripts can be used to start +the server automatically at system startup time. @code{mysql.server} can also +be used to stop the server. + The @code{mysql.server} script can be used to start or stop the server by invoking it with @code{start} or @code{stop} arguments: @@ -10143,9 +10248,8 @@ the @strong{MySQL} installation directory, then invokes @code{safe_mysqld}. You might need to edit @code{mysql.server} if you have a binary distribution that you've installed in a non-standard location. Modify it to @code{cd} into the proper directory before it runs @code{safe_mysqld}. If you want the -server to run as some specific user, you can change the -@code{mysql_daemon_user=root} line to use another user. You can also modify -@code{mysql.server} to pass other options to @code{safe_mysqld}. +server to run as some specific user, add an appropriate @code{user} line +to the @file{/etc/my.cnf} file, as shown later in this section. @code{mysql.server stop} brings down the server by sending a signal to it. You can take down the server manually by executing @code{mysqladmin shutdown}. @@ -10171,23 +10275,23 @@ this: datadir=/usr/local/mysql/var socket=/tmp/mysqld.sock port=3306 +user=mysql [mysql.server] -user=mysql basedir=/usr/local/mysql @end example -The @code{mysql.server} script uses the following variables: -@code{user}, @code{datadir}, @code{basedir}, @code{bindir}, and -@code{pid-file}. +The @code{mysql.server} script understands the following options: +@code{datadir}, @code{basedir}, and @code{pid-file}. -The following table shows which option sections each of the startup script -uses: +The following table shows which option groups each of the startup scripts +read from option files: @multitable @columnfractions .20 .80 +@item @strong{Script} @tab @strong{Option groups} @item @code{mysqld} @tab @code{mysqld} and @code{server} -@item @code{mysql.server} @tab @code{mysql.server}, @code{mysqld} and @code{server} -@item @code{safe_mysqld} @tab @code{mysql.server}, @code{mysqld} and @code{server} +@item @code{mysql.server} @tab @code{mysql.server}, @code{mysqld}, and @code{server} +@item @code{safe_mysqld} @tab @code{mysql.server}, @code{mysqld}, and @code{server} @end multitable @xref{Option files}. @@ -10228,7 +10332,8 @@ though. @item --core-file Write a core file if @code{mysqld} dies. For some systems you must also -specify @code{--core-file-size} to @code{safe_mysqld}. @xref{safe_mysqld}. +specify @code{--core-file-size} to @code{safe_mysqld}. @xref{safe_mysqld, , +@code{safe_mysqld}}. @item -h, --datadir=path Path to the database root. @@ -10408,9 +10513,9 @@ recommended for systems where only local requests are allowed. @xref{DNS}. Don't use new, possible wrong routines. Implies @code{--skip-delay-key-write}. This will also set default table type to @code{ISAM}. @xref{ISAM}. -@item --skip-stack-trace -Don't write stack traces. This option is useful when you are running -@code{mysqld} under a debugger. @xref{Debugging server}. +@item --skip-symlink +Don't delete or rename files that a symlinked file in the data directory +points to. @item --skip-safemalloc If @strong{MySQL} is configured with @code{--with-debug=full}, all programs @@ -10422,6 +10527,10 @@ need memory checking, by using this option. Don't allow 'SHOW DATABASE' commands, unless the user has @strong{process} privilege. +@item --skip-stack-trace +Don't write stack traces. This option is useful when you are running +@code{mysqld} under a debugger. @xref{Debugging server}. + @item --skip-thread-priority Disable using thread priorities for faster response time. @@ -10443,6 +10552,9 @@ Run @code{mysqld} daemon as user @code{user_name}. This option is @item -V, --version Output version information and exit. +@item -W, --warnings +Print out warnings like @code{Aborted connection...} to the @code{.err} file. +@xref{Communication errors}. @end table @cindex default options @@ -10564,7 +10676,7 @@ password=my_password no-auto-rehash set-variable = connect_timeout=2 -[mysql-hot-copy] +[mysqlhotcopy] interactive-timeout @end example @@ -10572,7 +10684,7 @@ interactive-timeout @tindex .my.cnf file If you have a source distribution, you will find sample configuration files named @file{my-xxxx.cnf} in the @file{support-files} directory. -If you have a binary distribution, look in the @file{DIR/share/mysql} +If you have a binary distribution, look in the @file{DIR/support-files} directory, where @code{DIR} is the pathname to the @strong{MySQL} installation directory (typically @file{/usr/local/mysql}). Currently there are sample configuration files for small, medium, large, and very @@ -10649,7 +10761,7 @@ The above is the quick and dirty way that one commonly uses for testing. The nice thing with this is that all connections you do in the above shell will automatically be directed to the new running server! -If you need to do this more permanently, you should create an own option +If you need to do this more permanently, you should create an option file for each server. @xref{Option files}. In your startup script that is executed at boot time (mysql.server?) you should specify for both servers: @@ -10743,8 +10855,8 @@ the old @code{ISAM} type. You don't have to convert your old tables to use these with Version 3.23. By default, all new tables will be created with type @code{MyISAM} (unless you start @code{mysqld} with the @code{--default-table-type=isam} option). You can change an @code{ISAM} -table to a @code{MyISAM} table with @code{ALTER TABLE} or the Perl script -@code{mysql_convert_table_format}. +table to a @code{MyISAM} table with @code{ALTER TABLE table_name TYPE=MyISAM} +or the Perl script @code{mysql_convert_table_format}. Version 3.22 and 3.21 clients will work without any problems with a Version 3.23 server. @@ -11918,7 +12030,7 @@ system. This section describes how it works. * Request access:: Access control, stage 2: Request verification * Privilege changes:: When privilege changes take effect * Default privileges:: Setting up the initial @strong{MySQL} privileges -* Adding users:: Adding new user privileges to @strong{MySQL} +* Adding users:: Adding new users to @strong{MySQL} * Passwords:: How to set up passwords * Access denied:: Causes of @code{Access denied} errors @end menu @@ -11947,9 +12059,10 @@ When running @strong{MySQL}, follow these guidelines whenever possible: @itemize @bullet @item DON'T EVER GIVE ANYONE (EXCEPT THE @strong{MySQL} ROOT USER) ACCESS TO THE -mysql.user TABLE! The encrypted password is the real password in -@strong{MySQL}. If you know this for one user, you can easily log in as -him if you have access to his 'host'. +@code{user} TABLE IN THE @code{mysql} DATABASE! The encrypted password +is the real password in @strong{MySQL}. If you know the password listed in +the @code{user} table for a given user, you can easily log in as that +user if you have access to the host listed for that account. @item Learn the @strong{MySQL} access privilege system. The @code{GRANT} and @@ -11978,15 +12091,15 @@ computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use @code{MD5()} or another one-way hashing function. @item -Do not use passwords from dictionaries. There are special programs to +Do not choose passwords from dictionaries. There are special programs to break them. Even passwords like ``xfish98'' are very bad. Much better is ``duag98'' which contains the same word ``fish'' but typed one key to the left on a standard QWERTY keyboard. Another method is to use ``Mhall'' which is taken from the first characters of each word in the sentence ``Mary had -a little lamb.'' This is easy to remember and type, but hard to guess for -someone who does not know it. +a little lamb.'' This is easy to remember and type, but difficult to guess +for someone who does not know it. @item -Invest in a firewall. This protects from at least 50% of all types of +Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put @strong{MySQL} behind the firewall or in a demilitarized zone (DMZ). @@ -11995,11 +12108,16 @@ Checklist: @item Try to scan your ports from the Internet using a tool such as @code{nmap}. @strong{MySQL} uses port 3306 by default. This port should -be inaccessible from untrusted hosts. Another simple way to check whether or -not your @strong{MySQL} port is open is to type @code{telnet -server_host 3306} from some remote machine, where -@code{server_host} is the hostname of your @strong{MySQL} -server. If you get a connection and some garbage characters, the port is +be inaccessible from untrusted hosts. Another simple way to check whether +or not your @strong{MySQL} port is open is to try the following command +from some remote machine, where @code{server_host} is the hostname of +your @strong{MySQL} server: + +@example +shell> telnet server_host 3306 +@end example + +If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If @code{telnet} just hangs or the connection is refused, everything is OK; the port is blocked. @@ -12055,11 +12173,13 @@ not give your applications any more access privileges than they need. Users of PHP: @itemize @bullet @item Check out the @code{addslashes()} function. +As of PHP 4.0.3, a @code{mysql_escape_string()} function is available +that is based on the function of the same name in the @strong{MySQL} C API. @end itemize @item Users of @strong{MySQL} C API: @itemize @bullet -@item Check out the @code{mysql_escape()} API call. +@item Check out the @code{mysql_escape_string()} API call. @end itemize @item Users of @strong{MySQL}++: @@ -12071,6 +12191,11 @@ Users of Perl DBI: @itemize @bullet @item Check out the @code{quote()} method or use placeholders. @end itemize +@item +Users of Java JDBC: +@itemize @bullet +@item Use a @code{PreparedStatement} object and placeholders. +@end itemize @end itemize @item @@ -12106,15 +12231,15 @@ connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted -network, you should use an @strong{SSH} tunnel to encrypt the +network, you should use an SSH tunnel to encrypt the communication. All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in @strong{MySQL} Version 3.22 and above) to make things much harder. To make things even more secure you should use -@code{ssh}. You can find an open source ssh client at -@uref{http://www.openssh.org}, and a commercial ssh client at +@code{ssh}. You can find an open source @code{ssh} client at +@uref{http://www.openssh.org}, and a commercial @code{ssh} client at @uref{http://www.ssh.com}. With this, you can get an encrypted TCP/IP connection between a @strong{MySQL} server and a @strong{MySQL} client. @@ -12139,35 +12264,44 @@ mysql> FLUSH PRIVILEGES; @end example @item -Don't run the @strong{MySQL} daemon as the Unix @code{root} user. -It is very dangerous as any user with @code{FILE} privileges will be able to -create files -as @code{root} (for example, @code{~root/.bashrc}). To prevent this -@code{mysqld} will refuse to run as @code{root} unless it is specified -directly via @code{--user=root} option. +Don't run the @strong{MySQL} daemon as the Unix @code{root} user. This is +very dangerous, because any user with @code{FILE} privileges will be able +to create files as @code{root} (for example, @code{~root/.bashrc}). To +prevent this, @code{mysqld} will refuse to run as @code{root} unless it +is specified directly using a @code{--user=root} option. + +@code{mysqld} can be run as an ordinary unprivileged user instead. +You can also create a new Unix user @code{mysql} to make everything +even more secure. If you run @code{mysqld} as another Unix user, +you don't need to change the @code{root} user name in the @code{user} +table, because @strong{MySQL} user names have nothing to do with Unix +user names. To start @code{mysqld} as another Unix user, add a @code{user} +line that specifies the user name to the @code{[mysqld]} group of the +@file{/etc/my.cnf} option file or the @file{my.cnf} option file in the +server's data directory. For example: + +@example +[mysqld] +user=mysql +@end example -@code{mysqld} can be run as any user instead. You can also create a new -Unix user @code{mysql} to make everything even more secure. If you run -@code{mysqld} as another Unix user, you don't need to change the -@code{root} user name in the @code{user} table, because @strong{MySQL} -user names have nothing to do with Unix user names. You can edit the -@code{mysql.server} script to start @code{mysqld} as another Unix user. -Normally this is done with the @code{su} command. For more details, see -@ref{Changing MySQL user, , Changing @strong{MySQL} user}. +This will cause the server to start as the designated user whether you +start it manually or by using @code{safe_mysqld} or @code{mysql.server}. +For more details, see @ref{Changing MySQL user, , Changing @strong{MySQL} +user}. @item -If you put a password for the Unix @code{root} user in the @code{mysql.server} -script, make sure this script is readable only by @code{root}. +Don't support symlinks to tables (This can be disabled with the +@code{--skip-symlink} option. This is especially important if you run +@code{mysqld} as root as anyone that has write access to the mysqld data +directories could then delete any file in the system! +@xref{Symbolic links to tables}. @item Check that the Unix user that @code{mysqld} runs as is the only user with read/write privileges in the database directories. @item -On Unix platforms, do not run @code{mysqld} as root unless you really -need to. Consider creating a user named @code{mysql} for that purpose. - -@item Don't give the @strong{process} privilege to all users. The output of @code{mysqladmin processlist} shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to @@ -12267,7 +12401,6 @@ DATA INFILE} and administrative operations. @cindex user names, and passwords @cindex passwords, for users - There are several distinctions between the way user names and passwords are used by @strong{MySQL} and the way they are used by Unix or Windows: @@ -12301,6 +12434,42 @@ knowing your 'scrambled' password is enough to be able to connect to the @strong{MySQL} server! @end itemize +@strong{MySQL} users and they privileges are normally created with the +@code{GRANT} command. @xref{GRANT}. + +When you login to a @strong{MySQL} server with a command line client you +should specify the password with @code{--password=your-password}. +@xref{Connecting}. + +@example +mysql --user=monty --password=guess database_name +@end example + +If you want the client to prompt for a password, you should use +@code{--password} without any argument + +@example +mysql --user=monty --password database_name +@end example + +or the short form: + +@example +mysql -u monty -p database_name +@end example + +Note that in the last example the password is @strong{NOT} 'database_name'. + +If you want to use the -p option to supply a password you should do like this: + +@example +mysql -u monty -pguess database_name +@end example + +On some system the library call that @strong{MySQL} uses to prompt for a +password will automaticly cut the password to 8 characters. Internally +@strong{MySQL} doesn't have any limit for the length of the password. + @node Connecting, Password security, User names, Privilege system @section Connecting to the MySQL Server @cindex connecting, to the server @@ -13360,12 +13529,15 @@ running @code{mysql_install_db}. @findex GRANT statement @findex statements, GRANT @node Adding users, Passwords, Default privileges, Privilege system -@section Adding New User Privileges to MySQL +@section Adding New Users to MySQL You can add users two different ways: by using @code{GRANT} statements or by manipulating the @strong{MySQL} grant tables directly. The preferred method is to use @code{GRANT} statements, because they are -more concise and less error-prone. +more concise and less error-prone. @xref{GRANT}. + +There is also a lot of contributed programs like @code{phpmyadmin} that +can be used to create and administrate users. @xref{Contrib}. The examples below show how to use the @code{mysql} client to set up new users. These examples assume that privileges are set up according to the @@ -13476,6 +13648,11 @@ mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP IDENTIFIED BY 'stupid'; @end example +The reason that we do to grant statements for the user 'custom' is that +we want the give the user access to @strong{MySQL} both from the local +machine with Unix sockets and from the remote machine 'whitehouse.gov' +over TCP/IP. + To set up the user's privileges by modifying the grant tables directly, run these commands (note the @code{FLUSH PRIVILEGES} at the end): @@ -16535,6 +16712,16 @@ mysql> select 'David_' LIKE 'David|_' ESCAPE '|'; -> 1 @end example +The following two statements illustrate that string comparisons are +case insensitive unless one of the operands is a binary string: + +@example +mysql> select 'abc' LIKE 'ABC'; + -> 1 +mysql> SELECT 'abc' LIKE BINARY 'ABC'; + -> 0 +@end example + @code{LIKE} is allowed on numeric expressions! (This is a @strong{MySQL} extension to the ANSI SQL @code{LIKE}.) @@ -18811,6 +18998,8 @@ or DELAY_KEY_WRITE = @{0 | 1@} or ROW_FORMAT= @{ default | dynamic | fixed | compressed @} or RAID_TYPE= @{1 | STRIPED | RAID0 @} RAID_CHUNKS=# RAID_CHUNKSIZE=# or UNION = (table_name,[table_name...]) +or DATA DIRECTORY="directory" +or INDEX DIRECTORY="directory" select_statement: [IGNORE | REPLACE] SELECT ... (Some legal select statement) @@ -18865,10 +19054,10 @@ When you insert a value of @code{NULL} (recommended) or @code{0} into an If you delete the row containing the maximum value for an @code{AUTO_INCREMENT} column, the value will be reused with an -@code{ISAM}, @code{BDB} or @code{INNODB} table but not with a -@code{MyISAM} table. If you delete all rows in the table with -@code{DELETE FROM table_name} (without a @code{WHERE}) in -@code{AUTOCOMMIT} mode, the sequence starts over for both table types. +@code{ISAM}, @code{GEMINI} or @code{BDB} table but not with a +@code{MyISAM} or @code{InnoDB} table. If you delete all rows in the table +with @code{DELETE FROM table_name} (without a @code{WHERE}) in +@code{AUTOCOMMIT} mode, the sequence starts over for all table types. @strong{NOTE:} There can be only one @code{AUTO_INCREMENT} column per table, and it must be indexed. @strong{MySQL} Version 3.23 will also only @@ -19142,6 +19331,14 @@ In the created table the @code{PRIMARY} key will be placed first, followed by all @code{UNIQUE} keys and then the normal keys. This helps the @strong{MySQL} optimizer to prioritize which key to use and also more quickly detect duplicated @code{UNIQUE} keys. + +@item +By using @code{DATA DIRECTORY="directory"} or @code{INDEX +DIRECTORY="directory"} you can specify where the table handler should +put it's table and index files. This only works for @code{MyISAM} tables +in @code{MySQL} 4.0, when you are not using the @code{--skip-symlink} +option. @xref{Symbolic links to tables}. + @end itemize @cindex silent column changes @@ -19278,9 +19475,6 @@ INDEX} are @strong{MySQL} extensions to ANSI SQL92. @code{MODIFY} is an Oracle extension to @code{ALTER TABLE}. @item -@code{TRUNCATE} is an Oracle extension. @xref{TRUNCATE}. - -@item The optional word @code{COLUMN} is a pure noise word and can be omitted. @item @@ -19341,8 +19535,7 @@ a table row. The default is to add the column last. or removes the old default value. If the old default is removed and the column can be @code{NULL}, the new default is @code{NULL}. If the column cannot be @code{NULL}, @strong{MySQL} -assigns a default value. -Default value assignment is described in +assigns a default value, as described in @ref{CREATE TABLE, , @code{CREATE TABLE}}. @findex DROP INDEX @@ -19355,6 +19548,10 @@ If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. +@item +If a table contains only one column, the column cannot be dropped. +If what you intend is to remove the table, use @code{DROP TABLE} instead. + @findex DROP PRIMARY KEY @item @code{DROP PRIMARY KEY} drops the primary index. If no such @@ -19461,6 +19658,11 @@ sequence number by executing @code{SET INSERT_ID=#} before @code{ALTER TABLE} or using the @code{AUTO_INCREMENT = #} table option. @xref{SET OPTION}. +With MyISAM tables, if you don't change the @code{AUTO_INCREMENT} +column, the sequence number will not be affected. If you drop an +@code{AUTO_INCREMENT} column and then add another @code{AUTO_INCREMENT} +column, the numbers will start from 1 again. + @xref{ALTER TABLE problems}. @findex RENAME TABLE @@ -19667,6 +19869,8 @@ minimum needed to restore it. Currenlty only works for @code{MyISAM} tables. For @code{MyISAM} table, copies @code{.frm} (definition) and @code{.MYD} (data) files. The index file can be rebuilt from those two. +Before using this command, please see @xref{Backup}. + During the backup, read lock will be held for each table, one at time, as they are being backed up. If you want to backup several tables as a snapshot, you must first issue @code{LOCK TABLES} obtaining a read @@ -19875,6 +20079,8 @@ valid, the table can be re-created this way, even if the data or index files have become corrupted. @end itemize +@code{TRUNCATE} is an Oracle SQL extension. + @findex SELECT @node SELECT, JOIN, TRUNCATE, Reference @section @code{SELECT} Syntax @@ -20495,9 +20701,9 @@ Version 3.22.15. It is a @strong{MySQL} extension to ANSI SQL92. @code{INSERT DELAYED} only works with @code{ISAM} and @code{MyISAM} tables. Note that as @code{MyISAM} tables supports concurrent -@code{SELECT} and @code{INSERT}, if there is no empty blocks in the data -file, you very seldom need to use @code{INSERT DELAYED} with -@code{MyISAM}. +@code{SELECT} and @code{INSERT}, if there is no free blocks in the +middle of the data file, you very seldom need to use @code{INSERT +DELAYED} with @code{MyISAM}. @xref{MyISAM}. When you use @code{INSERT DELAYED}, the client will get an OK at once and the row will be inserted when the table is not in use by any other thread. @@ -20630,6 +20836,13 @@ In other words, you can't access the values of the old row from a @code{REPLACE} statement. In some old @strong{MySQL} version it looked like you could do this, but that was a bug that has been corrected. +When one uses a @code{REPLACE} command, @code{mysql_affected_rows()} +will return 2 if the new row replaced and old row. This is because in +this case one row was inserted and then the duplicate was deleted. + +The above makes it easy to check if @code{REPLACE} added or replaced a +row. + @findex LOAD DATA INFILE @node LOAD DATA, UPDATE, REPLACE, Reference @section @code{LOAD DATA INFILE} Syntax @@ -22646,8 +22859,6 @@ This statement is provided for Oracle compatibility. The @code{SHOW} statement provides similar information. @xref{SHOW, , @code{SHOW}}. - - @findex BEGIN @findex COMMIT @findex ROLLBACK @@ -23005,8 +23216,9 @@ REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] @code{GRANT} is implemented in @strong{MySQL} Version 3.22.11 or later. For earlier @strong{MySQL} versions, the @code{GRANT} statement does nothing. -The @code{GRANT} and @code{REVOKE} commands allow system administrators to -grant and revoke rights to @strong{MySQL} users at four privilege levels: +The @code{GRANT} and @code{REVOKE} commands allow system administrators +to create users and grant and revoke rights to @strong{MySQL} users at +four privilege levels: @table @strong @item Global level @@ -23026,6 +23238,7 @@ Column privileges apply to single columns in a given table. These privileges are stored in the @code{mysql.columns_priv} table. @end table +If you give a grant for a users that doesn't exists, that user is created. For examples of how @code{GRANT} works, see @ref{Adding users}. For the @code{GRANT} and @code{REVOKE} statements, @code{priv_type} may be @@ -23607,9 +23820,9 @@ of both worlds. * MERGE:: MERGE tables * ISAM:: ISAM tables * HEAP:: HEAP tables +* InnoDB:: InnoDB tables * BDB:: BDB or Berkeley_db tables * GEMINI:: GEMINI tables -* InnoDB:: InnoDB tables @end menu @node MyISAM, MERGE, Table types, Table types @@ -23633,8 +23846,12 @@ the table was closed correctly. If @code{mysqld} is started with @code{--myisam-recover}, @code{MyISAM} tables will automatically be checked and/or repaired on open if the table wasn't closed properly. @item -You can @code{INSERT} new rows in a table without deleted rows, -while other threads are reading from the table. +You can @code{INSERT} new rows in a table that doesn't have free blocks +in the middle of the data file, at the same time other threads are +reading from the table (concurrent insert). An free block can come from +an update of a dynamic length row with much data to a row with less data +or when deleting rows. When all free blocks are used up, all future +inserts will be concurrent again. @item Support for big files (63-bit) on filesystems/operating systems that support big files. @@ -23692,6 +23909,10 @@ with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted. @item @code{myisampack} can pack @code{BLOB} and @code{VARCHAR} columns. +@item +You can use put the datafile and index file on different directories +to get more speed (with the @code{DATA/INDEX DIRECTORY="path"} option to +@code{CREATE TABLE}). @xref{CREATE TABLE}. @end itemize @code{MyISAM} also supports the following things, which @strong{MySQL} @@ -24270,6 +24491,14 @@ tables are: @item Tables are compressed with @code{pack_isam} rather than with @code{myisampack}. @end itemize +If you want to convert an @code{ISAM} table to a @code{MyISAM} table so +that you can use utilities such as @code{mysqlcheck}, use an @code{ALTER +TABLE} statement: + +@example +mysql> ALTER TABLE tbl_name TYPE = MYISAM; +@end example + @cindex tables, @code{HEAP} @node HEAP, BDB, ISAM, Table types @section HEAP Tables @@ -24348,375 +24577,6 @@ SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2) @code{sizeof(char*)} is 4 on 32-bit machines and 8 on 64-bit machines. -@cindex tables, @code{BDB} -@cindex tables, @code{Berkeley DB} -@node BDB, GEMINI, HEAP, Table types -@section BDB or Berkeley_DB Tables - -@menu -* BDB overview:: Overview of BDB Tables -* BDB install:: Installing BDB -* BDB start:: BDB startup options -* BDB characteristic:: Some characteristic of @code{BDB} tables: -* BDB TODO:: Some things we need to fix for BDB in the near future: -* BDB portability:: Operating systems supported by @strong{BDB} -* BDB errors:: Errors You May Get When Using BDB Tables -@end menu - -@node BDB overview, BDB install, BDB, BDB -@subsection Overview of BDB Tables - -Support for BDB tables is included in the @strong{MySQL} source distribution -starting from Version 3.23.34 and is activated in the @strong{MySQL}-Max -binary. - -BerkeleyDB, available at @uref{http://www.sleepycat.com/} has provided -@strong{MySQL} with a transactional table handler. By using BerkeleyDB -tables, your tables may have a greater chance of surviving crashes, and also -provides @code{COMMIT} and @code{ROLLBACK} on transactions. The -@strong{MySQL} source distribution comes with a BDB distribution that has a -couple of small patches to make it work more smoothly with @strong{MySQL}. -You can't use a non-patched @code{BDB} version with @strong{MySQL}. - -We at @strong{MySQL AB} are working in close cooperation with Sleepycat to -keep the quality of the @strong{MySQL}/BDB interface high. - -When it comes to supporting BDB tables, we are committed to help our -users to locate the problem and help creating a reproducable test case -for any problems involving BDB tables. Any such test case will be -forwarded to Sleepycat who in turn will help us find and fix the -problem. As this is a two stage operation, any problems with BDB tables -may take a little longer for us to fix than for other table handlers. -However, as the BerkeleyDB code itself has been used by many other -applications than @strong{MySQL}, we don't envision any big problems with -this. @xref{Table handler support}. - -@node BDB install, BDB start, BDB overview, BDB -@subsection Installing BDB - -If you have downloaded a binary version of @strong{MySQL} that includes -support for BerkeleyDB, simply follow the instructions for installing a -binary version of @strong{MySQL}. -@xref{Installing binary}. @xref{mysqld-max}. - -To compile @strong{MySQL} with Berkeley DB support, download @strong{MySQL} -Version 3.23.34 or newer and configure @code{MySQL} with the -@code{--with-berkeley-db} option. @xref{Installing source}. - -@example -cd /path/to/source/of/mysql-3.23.34 -./configure --with-berkeley-db -@end example - -Please refer to the manual provided with the @code{BDB} distribution for -more updated information. - -Even though Berkeley DB is in itself very tested and reliable, -the @strong{MySQL} interface is still considered beta quality. -We are actively improving and optimizing it to get it stable very -soon. - -@node BDB start, BDB characteristic, BDB install, BDB -@subsection BDB startup options - -If you are running with @code{AUTOCOMMIT=0} then your changes in @code{BDB} -tables will not be updated until you execute @code{COMMIT}. Instead of commit -you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}. - -If you are running with @code{AUTOCOMMIT=1} (the default), your changes -will be committed immediately. You can start an extended transaction with -the @code{BEGIN WORK} SQL command, after which your changes will not be -committed until you execute @code{COMMIT} (or decide to @code{ROLLBACK} -the changes). - -The following options to @code{mysqld} can be used to change the behavior of -BDB tables: - -@multitable @columnfractions .30 .70 -@item @strong{Option} @tab @strong{Meaning} -@item @code{--bdb-home=directory} @tab Base directory for BDB tables. This should be the same directory you use for --datadir. -@item @code{--bdb-lock-detect=#} @tab Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM, or YOUNGEST). -@item @code{--bdb-logdir=directory} @tab Berkeley DB log file directory. -@item @code{--bdb-no-sync} @tab Don't synchronously flush logs. -@item @code{--bdb-no-recover} @tab Don't start Berkeley DB in recover mode. -@item @code{--bdb-shared-data} @tab Start Berkeley DB in multi-process mode (Don't use @code{DB_PRIVATE} when initializing Berkeley DB) -@item @code{--bdb-tmpdir=directory} @tab Berkeley DB tempfile name. -@item @code{--skip-bdb} @tab Don't use berkeley db. -@item @code{-O bdb_max_lock=1000} @tab Set the maximum number of locks possible. @xref{SHOW VARIABLES}. -@end multitable - -If you use @code{--skip-bdb}, @strong{MySQL} will not initialize the -Berkeley DB library and this will save a lot of memory. Of course, -you cannot use @code{BDB} tables if you are using this option. - -Normally you should start @code{mysqld} without @code{--bdb-no-recover} if you -intend to use BDB tables. This may, however, give you problems when you -try to start @code{mysqld} if the BDB log files are corrupted. @xref{Starting -server}. - -With @code{bdb_max_lock} you can specify the maximum number of locks -(10000 by default) you can have active on a BDB table. You should -increase this if you get errors of type @code{bdb: Lock table is out of -available locks} or @code{Got error 12 from ...} when you have do long -transactions or when @code{mysqld} has to examine a lot of rows to -calculate the query. - -You may also want to change @code{binlog_cache_size} and -@code{max_binlog_cache_size} if you are using big multi-line transactions. -@xref{COMMIT}. - -@node BDB characteristic, BDB TODO, BDB start, BDB -@subsection Some characteristic of @code{BDB} tables: - -@itemize @bullet -@item -To be able to rollback transactions BDB maintain log files. For maximum -performance you should place these on another disk than your databases -by using the @code{--bdb_log_dir} options. -@item -@strong{MySQL} performs a checkpoint each time a new BDB log -file is started, and removes any log files that are not needed for -current transactions. One can also run @code{FLUSH LOGS} at any time -to checkpoint the Berkeley DB tables. - -For disaster recovery, one should use table backups plus -@strong{MySQL}'s binary log. @xref{Backup}. - -@strong{Warning}: If you delete old log files that are in use, BDB will -not be able to do recovery at all and you may loose data if something -goes wrong. -@item -@strong{MySQL} requires a @code{PRIMARY KEY} in each BDB table to be -able to refer to previously read rows. If you don't create one, -@strong{MySQL} will create an maintain a hidden @code{PRIMARY KEY} for -you. The hidden key has a length of 5 bytes and is incremented for each -insert attempt. -@item -If all columns you access in a @code{BDB} table are part of the same index or -part of the primary key, then @strong{MySQL} can execute the query -without having to access the actual row. In a @code{MyISAM} table the -above holds only if the columns are part of the same index. -@item -The @code{PRIMARY KEY} will be faster than any other key, as the -@code{PRIMARY KEY} is stored together with the row data. As the other keys are -stored as the key data + the @code{PRIMARY KEY}, it's important to keep the -@code{PRIMARY KEY} as short as possible to save disk and get better speed. -@item -@code{LOCK TABLES} works on @code{BDB} tables as with other tables. If -you don't use @code{LOCK TABLE}, @strong{MYSQL} will issue an internal -multiple-write lock on the table to ensure that the table will be -properly locked if another thread issues a table lock. -@item -Internal locking in @code{BDB} tables is done on page level. -@item -@code{SELECT COUNT(*) FROM table_name} is slow as @code{BDB} tables doesn't -maintain a count of the number of rows in the table. -@item -Scanning is slower than with @code{MyISAM} tables as one has data in BDB -tables stored in B-trees and not in a separate data file. -@item -The application must always be prepared to handle cases where -any change of a @code{BDB} table may make an automatic rollback and any -read may fail with a deadlock error. -@item -Keys are not compressed to previous keys as with ISAM or MyISAM -tables. In other words, the key information will take a little more -space in @code{BDB} tables compared to MyISAM tables which don't use -@code{PACK_KEYS=0}. -@item -There is often holes in the BDB table to allow you to insert new rows in -the middle of the key tree. This makes BDB tables somewhat larger than -MyISAM tables. -@item -The optimizer needs to know an approximation of the number of rows in -the table. @strong{MySQL} solves this by counting inserts and -maintaining this in a separate segment in each BDB table. If you don't -do a lot of @code{DELETE} or @code{ROLLBACK}:s this number should be -accurate enough for the @strong{MySQL} optimizer, but as @strong{MySQL} -only store the number on close, it may be wrong if @strong{MySQL} dies -unexpectedly. It should not be fatal even if this number is not 100 % -correct. One can update the number of rows by executing @code{ANALYZE -TABLE} or @code{OPTIMIZE TABLE}. @xref{ANALYZE TABLE} . @xref{OPTIMIZE -TABLE}. -@item -If you get full disk with a @code{BDB} table, you will get an error -(probably error 28) and the transaction should roll back. This is in -contrast with @code{MyISAM} and @code{ISAM} tables where @code{mysqld} will -wait for enough free disk before continuing. -@end itemize - -@node BDB TODO, BDB portability, BDB characteristic, BDB -@subsection Some things we need to fix for BDB in the near future: - -@itemize @bullet -@item -It's very slow to open many BDB tables at the same time. If you are -going to use BDB tables, you should not have a very big table cache (> -256 ?) and you should use @code{--no-auto-rehash} with the @code{mysql} -client. We plan to partly fix this in 4.0. -@item -@code{SHOW TABLE STATUS} doesn't yet provide that much information for BDB -tables. -@item -Optimize performance. -@item -Change to not use page locks at all when we are scanning tables. -@end itemize - -@node BDB portability, BDB errors, BDB TODO, BDB -@subsection Operating systems supported by @strong{BDB} - -If you after having built @strong{MySQL} with support for BDB tables get -the following error in the log file when you start @code{mysqld}: - -@example -bdb: architecture lacks fast mutexes: applications cannot be threaded -Can't init dtabases -@end example - -This means that @code{BDB} tables are not supported for your architecture. -In this case you have to rebuild @strong{MySQL} without BDB table support. - -NOTE: The following list is not complete; We will update this as we get -more information about this. - -Currently we know that BDB tables works with the following operating -system. - -@itemize @bullet -@item -Linux 2.x intel -@item -Solaris sparc -@item -SCO OpenServer -@item -SCO UnixWare 7.0.1 -@end itemize - -It doesn't work with the following operating systems: - -@itemize @bullet -@item -Linux 2.x Alpha -@item -Max OS X -@end itemize - -@node BDB errors, , BDB portability, BDB -@subsection Errors You May Get When Using BDB Tables - -@itemize @bullet -@item -If you get the following error in the @code{hostname.err log} when -starting @code{mysqld}: - -@example -bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version # -@end example -it means that the new @code{BDB} version doesn't support the old log -file format. In this case you have to delete all @code{BDB} log BDB -from your database directory (the files that has the format -@code{log.XXXXXXXXXX} ) and restart @code{mysqld}. We would also -recommend you to do a @code{mysqldump --opt} of your old @code{BDB} -tables, delete the old table and restore the dump. -@item -If you are running in not @code{auto_commit} mode and delete a table you -are using by another thread you may get the following error messages in -the @strong{MySQL} error file: - -@example -001119 23:43:56 bdb: Missing log fileid entry -001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid -@end example - -This is not fatal but we don't recommend that you delete tables if you are -not in @code{auto_commit} mode, until this problem is fixed (the fix is -not trivial). -@end itemize - -@cindex tables, @code{GEMINI} -@node GEMINI, InnoDB, BDB, Table types -@section GEMINI Tables - -@menu -* GEMINI overview:: -* GEMINI start:: -* GEMINI features:: -* GEMINI TODO:: -@end menu - -@node GEMINI overview, GEMINI start, GEMINI, GEMINI -@subsection Overview of GEMINI tables - -The @code{GEMINI} table type is developed and supported by NuSphere Corporation -(@uref{http://www.nusphere.com}). It features row-level locking, transaction -support (@code{COMMIT} and @code{ROLLBACK}), and automatic crash recovery. - -@code{GEMINI} tables will be included in some future @strong{MySQL} 3.23.X -source distribution. - -@node GEMINI start, GEMINI features, GEMINI overview, GEMINI -@subsection GEMINI startup options - -If you are running with @code{AUTOCOMMIT=0} then your changes in @code{GEMINI} -tables will not be updated until you execute @code{COMMIT}. Instead of commit -you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}. - -If you are running with @code{AUTOCOMMIT=1} (the default), your changes -will be committed immediately. You can start an extended transaction with -the @code{BEGIN WORK} SQL command, after which your changes will not be -committed until you execute @code{COMMIT} (or decide to @code{ROLLBACK} -the changes). - -The following options to @code{mysqld} can be used to change the behavior of -GEMINI tables: - -@multitable @columnfractions .30 .70 -@item @strong{Option} @tab @strong{Meaning} -@item @code{--gemini-full-recovery} @tab Default. -@item @code{--gemini-no-recovery} @tab Turn off recovery logging. Not recommended. -@item @code{--gemini-lazy-commit} @tab Relaxes the flush log at commit rule. -@item @code{--gemini-unbuffered-io} @tab All database writes bypass OS cache. -@item @code{--skip-gemini} @tab Don't use Gemini. -@item @code{--O gemini_db_buffers=#} @tab Number of database buffers in database cache. -@item @code{--O gemini_connection_limit=#} @tab Maximum number of connections to Gemini. -@item @code{--O gemini_spin_retries=#} @tab Spin lock retries (optimization). -@item @code{--O gemini_io_threads=#} @tab Number of background I/O threads. -@item @code{--O gemini_lock_table_size=#} @tab Set the maximum number of locks. Default 4096. -@end multitable - -If you use @code{--skip-gemini}, @strong{MySQL} will not initialize the -Gemini table handler, saving memory; you cannot use Gemini tables if you -use @code{--skip-gemini}. - -@node GEMINI features, GEMINI TODO, GEMINI start, GEMINI -@subsection Features of @code{GEMINI} tables: - -@itemize @bullet -@item -If a query result can be resolved solely from the index key, Gemini will -not read the actual row stored in the database. -@item -Locking on Gemini tables is done at row level. -@item -@code{SELECT COUNT(*) FROM table_name} is fast; Gemini maintains a count -of the number of rows in the table. -@end itemize - -@node GEMINI TODO, , GEMINI features, GEMINI -@subsection Current limitations of @code{GEMINI} tables: - -@itemize @bullet -@item -BLOB columns are not supported in @code{GEMINI} tables. -@item -The maximum number of concurrent users accessing @code{GEMINI} tables is -limited by @code{gemini_connection_limit}. The default is 100 users. -@end itemize - -NuSphere is working on removing these limitations. - @node InnoDB, , GEMINI, Table types @section InnoDB Tables @@ -24747,7 +24607,7 @@ binary. If you have downloaded a binary version of @strong{MySQL} that includes support for InnoDB (mysqld-max), simply follow the instructions for installing a binary version of @strong{MySQL}. @xref{Installing binary}. -@xref{mysqld-max}. +@xref{mysqld-max, , @code{mysqld-max}}. To compile @strong{MySQL} with InnoDB support, download MySQL-3.23.37 or newer and configure @code{MySQL} with the @code{--with-innodb} option. @@ -24769,6 +24629,11 @@ InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine. +You can find the latest information about InnoDB at +@uref{http://www.innodb.com}. The most up-to-date version of the +InnoDB manual is always placed there, and you can also order commercial +support for InnoDB. + Technically, InnoDB is a database backend placed under @strong{MySQL}. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which @@ -24918,6 +24783,17 @@ in its own lock table and rolls back the transaction. If you use than InnoDB in the same transaction, then a deadlock may arise which InnoDB cannot notice. In cases like this the timeout is useful to resolve the situation. +@item @code{innodb_flush_method} @tab +(Available from 3.23.40 up.) +The default value for this is @code{fdatasync}. +Another option is @code{O_DSYNC}. +Options @code{littlesync} and @code{nosync} have the +risk that in an operating system crash or a power outage you may easily +end up with a half-written database page, and you have to do a recovery +from a backup. See the section "InnoDB performance tuning", item 6, below +for tips on how to set this parameter. If you are happy with your database +performance it is wisest not to specify this parameter at all, in which +case it will get the default value. @end multitable @node InnoDB init, Using InnoDB tables, InnoDB start, InnoDB @@ -24943,11 +24819,14 @@ InnoDB: Database physically writes the file full: wait... InnoDB: Data file /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... -InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created +InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be c +reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 -InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created +InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be c +reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 -InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be created +InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be c +reated InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections @@ -24979,7 +24858,7 @@ mysqld: ready for connections @end example @menu -* Error creating InnoDB:: +* Error creating InnoDB:: @end menu @node Error creating InnoDB, , InnoDB init, InnoDB init @@ -25035,6 +24914,46 @@ InnoDB has its own internal data dictionary, and you will get problems if the @strong{MySQL} @file{.frm} files are out of 'sync' with the InnoDB internal data dictionary. +@subsubsection Converting MyISAM tables to InnoDB + +InnoDB does not have a special optimization for separate index creation. +Therefore it does not pay to export and import the table and create indexes +afterwards. +The fastest way to alter a table to InnoDB is to do the inserts +directly to an InnoDB table, that is, use @code{ALTER TABLE ... TYPE=INNODB}, +or create an empty InnoDB table with identical definitions and insert +the rows with @code{INSERT INTO ... SELECT * FROM ...}. + +To get better control over the insertion process, it may be good to insert +big tables in pieces: + +@example +INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something + AND yourkey <= somethingelse; +@end example + +After all data has been inserted you can rename the tables. + +During the conversion of big tables you should set the InnoDB +buffer pool size big +to reduce disk i/o. Not bigger than 80 % of the physical memory, though. +You should set InnoDB log files big, and also the log buffer large. + +Make sure you do not run out of tablespace: InnoDB tables take a lot +more space than MyISAM tables. If an @code{ALTER TABLE} runs out +of space, it will start a rollback, and that can take hours if it is +disk-bound. +In inserts InnoDB uses the insert buffer to merge secondary index records +to indexes in batches. That saves a lot of disk i/o. In rollback no such +mechanism is used, and the rollback can take 30 times longer than the +insertion. + +In the case of a runaway rollback, if you do not have valuable data in your +database, +it is better that you kill the database process and delete all InnoDB data +and log files and all InnoDB table @file{.frm} files, and start +your job again, rather than wait for millions of disk i/os to complete. + @node Adding and removing, Backing up, Using InnoDB tables, InnoDB @subsection Adding and removing InnoDB data and log files @@ -25435,6 +25354,103 @@ set by the SQL statement may be preserved. This is because InnoDB stores row locks in a format where it cannot afterwards know which was set by which SQL statement. +@subsection Performance tuning tips + +@strong{1.} +If the Unix @file{top} or the Windows @file{Task Manager} shows that +the CPU usage percentage with your workload is less than 70 %, +your workload is probably +disk-bound. Maybe you are making too many transaction commits, or the +buffer pool is too small. +Making the buffer pool bigger can help, but do not set +it bigger than 80 % of physical memory. + +@strong{2.} +Wrap several modifications into one transaction. InnoDB must +flush the log to disk at each transaction commit, if that transaction +made modifications to the database. Since the rotation speed of a disk +is typically +at most 167 revolutions/second, that constrains the number of commits +to the same 167/second if the disk does not fool the operating system. + +@strong{3.} +If you can afford the loss of some latest committed transactions, you can +set the @file{my.cnf} parameter @code{innodb_flush_log_at_trx_commit} +to zero. InnoDB tries to flush the log anyway once in a second, +though the flush is not guaranteed. + +@strong{4.} +Make your log files big, even as big as the buffer pool. When InnoDB +has written the log files full, it has to write the modified contents +of the buffer pool to disk in a checkpoint. Small log files will cause many +unnecessary disk writes. The drawback in big log files is that recovery +time will be longer. + +@strong{5.} +Also the log buffer should be quite big, say 8 MB. + +@strong{6.} (Relevant from 3.23.39 up.) +In some versions of Linux and Unix, flushing files to disk with the Unix +@code{fdatasync} and other similar methods is surprisingly slow. +The default method InnoDB uses is the @code{fdatasync} function. +If you are not satisfied with the database write performance, you may +try setting @code{innodb_flush_method} in @file{my.cnf} +to @code{O_DSYNC}, though O_DSYNC seems to be slower on most systems. +You can also try setting it to @code{littlesync}, which means that +InnoDB does not call the file flush for every write it does to a +file, but only +in log flush at transaction commits and data file flush at a checkpoint. +The drawback in @code{littlesync} is that if the operating system +crashes, you can easily end up with a half-written database page, +and you have to +do a recovery from a backup. With @code{nosync} you have even less safety: +InnoDB will only flush the database files to disk at database shutdown + +@strong{7.} In importing data to InnoDB, make sure that MySQL does not have +@code{autocommit=1} on. Then every insert requires a log flush to disk. +Put before your plain SQL import file line + +@example +set autocommit=0; +@end example + +and after it + +@example +commit; +@end example + +If you use the @file{mysqldump} option @code{--opt}, you will get dump +files which are fast to import also to an InnoDB table, even without wrapping +them to the above @code{set autocommit=0; ... commit;} wrappers. + +@strong{8.} +Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer +to save disk i/o in inserts, but in a corresponding rollback no such +mechanism is used. A disk-bound rollback can take 30 times the time +of the corresponding insert. Killing the database process will not +help because the rollback will start again at the database startup. The +only way to get rid of a runaway rollback is to increase the buffer pool +so that the rollback becomes CPU-bound and runs fast, or delete the whole +InnoDB database. + +@strong{9.} +Beware also of other big disk-bound operations. +Use @code{DROP TABLE} +or @code{TRUNCATE} (from MySQL-4.0 up) to empty a table, not +@code{DELETE FROM yourtable}. + +@strong{10.} +Use the multi-line @code{INSERT} to reduce +communication overhead between the client and the server if you need +to insert many rows: + +@example +INSERT INTO yourtable VALUES (1, 2), (5, 5); +@end example + +This tip is of course valid for inserts into any table type, not just InnoDB. + @node Implementation, Table and index, InnoDB transaction model, InnoDB @subsection Implementation of multiversioning @@ -25655,11 +25671,11 @@ integer that can be stored in the specified integer type. In disk i/o InnoDB uses asynchronous i/o. On Windows NT it uses the native asynchronous i/o provided by the operating system. -On Unixes InnoDB uses simulated asynchronous i/o built +On Unix, InnoDB uses simulated asynchronous i/o built into InnoDB: InnoDB creates a number of i/o threads to take care of i/o operations, such as read-ahead. In a future version we will add support for simulated aio on Windows NT and native aio on those -Unixes which have one. +versions of Unix which have one. On Windows NT InnoDB uses non-buffered i/o. That means that the disk pages InnoDB reads or writes are not buffered in the operating system @@ -25670,7 +25686,7 @@ just define the raw disk in place of a data file in @file{my.cnf}. You must give the exact size in bytes of the raw disk in @file{my.cnf}, because at startup InnoDB checks that the size of the file is the same as specified in the configuration file. Using a raw disk -you can on some Unixes perform non-buffered i/o. +you can on some versions of Unix perform non-buffered i/o. There are two read-ahead heuristics in InnoDB: sequential read-ahead and random read-ahead. In sequential read-ahead InnoDB notices that @@ -25787,6 +25803,11 @@ they roll back the corresponding SQL statement. @subsection Some restrictions on InnoDB tables @itemize @bullet + +@item @code{SHOW TABLE STATUS} does not give accurate statistics +on InnoDB tables, except for the physical size reserved by the table. +The row count is only a rough estimate used in SQL optimization. + @item If you try to create an unique index on a prefix of a column you will get an error: @@ -25835,17 +25856,17 @@ files your operating system supports. Support for > 4 GB files will be added to InnoDB in a future version. @item The maximum tablespace size is 4 billion database pages. This is also -the maximum size for a table. +the maximum size for a table. The minimum tablespace size is 10 MB. @end itemize @node InnoDB contact information, , InnoDB restrictions, InnoDB @subsection InnoDB contact information -Contact information of Innobase Oy, producer of the InnoDB engine: +Contact information of Innobase Oy, producer of the InnoDB engine. +Website: @uref{http://www.innodb.com}. Email: +@email{Heikki.Tuuri@@innodb.com} @example -Website: www.innobase.fi -Heikki.Tuuri@@innobase.inet.fi phone: 358-9-6969 3250 (office) 358-40-5617367 (mobile) InnoDB Oy Inc. World Trade Center Helsinki @@ -25855,6 +25876,1142 @@ P.O.Box 800 Finland @end example +@cindex tables, @code{BDB} +@cindex tables, @code{Berkeley DB} +@node BDB, GEMINI, HEAP, Table types +@section BDB or Berkeley_DB Tables + +@menu +* BDB overview:: Overview of BDB Tables +* BDB install:: Installing BDB +* BDB start:: BDB startup options +* BDB characteristic:: Some characteristic of @code{BDB} tables: +* BDB TODO:: Some things we need to fix for BDB in the near future: +* BDB portability:: Operating systems supported by @strong{BDB} +* BDB errors:: Errors You May Get When Using BDB Tables +@end menu + +@node BDB overview, BDB install, BDB, BDB +@subsection Overview of BDB Tables + +Support for BDB tables is included in the @strong{MySQL} source distribution +starting from Version 3.23.34 and is activated in the @strong{MySQL}-Max +binary. + +BerkeleyDB, available at @uref{http://www.sleepycat.com/} has provided +@strong{MySQL} with a transactional table handler. By using BerkeleyDB +tables, your tables may have a greater chance of surviving crashes, and also +provides @code{COMMIT} and @code{ROLLBACK} on transactions. The +@strong{MySQL} source distribution comes with a BDB distribution that has a +couple of small patches to make it work more smoothly with @strong{MySQL}. +You can't use a non-patched @code{BDB} version with @strong{MySQL}. + +We at @strong{MySQL AB} are working in close cooperation with Sleepycat to +keep the quality of the @strong{MySQL}/BDB interface high. + +When it comes to supporting BDB tables, we are committed to help our +users to locate the problem and help creating a reproducable test case +for any problems involving BDB tables. Any such test case will be +forwarded to Sleepycat who in turn will help us find and fix the +problem. As this is a two stage operation, any problems with BDB tables +may take a little longer for us to fix than for other table handlers. +However, as the BerkeleyDB code itself has been used by many other +applications than @strong{MySQL}, we don't envision any big problems with +this. @xref{Table handler support}. + +@node BDB install, BDB start, BDB overview, BDB +@subsection Installing BDB + +If you have downloaded a binary version of @strong{MySQL} that includes +support for BerkeleyDB, simply follow the instructions for installing a +binary version of @strong{MySQL}. +@xref{Installing binary}. @xref{mysqld-max, , @code{mysqld-max}}. + +To compile @strong{MySQL} with Berkeley DB support, download @strong{MySQL} +Version 3.23.34 or newer and configure @code{MySQL} with the +@code{--with-berkeley-db} option. @xref{Installing source}. + +@example +cd /path/to/source/of/mysql-3.23.34 +./configure --with-berkeley-db +@end example + +Please refer to the manual provided with the @code{BDB} distribution for +more updated information. + +Even though Berkeley DB is in itself very tested and reliable, +the @strong{MySQL} interface is still considered beta quality. +We are actively improving and optimizing it to get it stable very +soon. + +@node BDB start, BDB characteristic, BDB install, BDB +@subsection BDB startup options + +If you are running with @code{AUTOCOMMIT=0} then your changes in @code{BDB} +tables will not be updated until you execute @code{COMMIT}. Instead of commit +you can execute @code{ROLLBACK} to forget your changes. @xref{COMMIT}. + +If you are running with @code{AUTOCOMMIT=1} (the default), your changes +will be committed immediately. You can start an extended transaction with +the @code{BEGIN WORK} SQL command, after which your changes will not be +committed until you execute @code{COMMIT} (or decide to @code{ROLLBACK} +the changes). + +The following options to @code{mysqld} can be used to change the behavior of +BDB tables: + +@multitable @columnfractions .30 .70 +@item @strong{Option} @tab @strong{Meaning} +@item @code{--bdb-home=directory} @tab Base directory for BDB tables. This should be the same directory you use for --datadir. +@item @code{--bdb-lock-detect=#} @tab Berkeley lock detect. One of (DEFAULT, OLDEST, RANDOM, or YOUNGEST). +@item @code{--bdb-logdir=directory} @tab Berkeley DB log file directory. +@item @code{--bdb-no-sync} @tab Don't synchronously flush logs. +@item @code{--bdb-no-recover} @tab Don't start Berkeley DB in recover mode. +@item @code{--bdb-shared-data} @tab Start Berkeley DB in multi-process mode (Don't use @code{DB_PRIVATE} when initializing Berkeley DB) +@item @code{--bdb-tmpdir=directory} @tab Berkeley DB tempfile name. +@item @code{--skip-bdb} @tab Don't use berkeley db. +@item @code{-O bdb_max_lock=1000} @tab Set the maximum number of locks possible. @xref{SHOW VARIABLES}. +@end multitable + +If you use @code{--skip-bdb}, @strong{MySQL} will not initialize the +Berkeley DB library and this will save a lot of memory. Of course, +you cannot use @code{BDB} tables if you are using this option. + +Normally you should start @code{mysqld} without @code{--bdb-no-recover} if you +intend to use BDB tables. This may, however, give you problems when you +try to start @code{mysqld} if the BDB log files are corrupted. @xref{Starting +server}. + +With @code{bdb_max_lock} you can specify the maximum number of locks +(10000 by default) you can have active on a BDB table. You should +increase this if you get errors of type @code{bdb: Lock table is out of +available locks} or @code{Got error 12 from ...} when you have do long +transactions or when @code{mysqld} has to examine a lot of rows to +calculate the query. + +You may also want to change @code{binlog_cache_size} and +@code{max_binlog_cache_size} if you are using big multi-line transactions. +@xref{COMMIT}. + +@node BDB characteristic, BDB TODO, BDB start, BDB +@subsection Some characteristic of @code{BDB} tables: + +@itemize @bullet +@item +To be able to rollback transactions BDB maintain log files. For maximum +performance you should place these on another disk than your databases +by using the @code{--bdb_log_dir} options. +@item +@strong{MySQL} performs a checkpoint each time a new BDB log +file is started, and removes any log files that are not needed for +current transactions. One can also run @code{FLUSH LOGS} at any time +to checkpoint the Berkeley DB tables. + +For disaster recovery, one should use table backups plus +@strong{MySQL}'s binary log. @xref{Backup}. + +@strong{Warning}: If you delete old log files that are in use, BDB will +not be able to do recovery at all and you may loose data if something +goes wrong. +@item +@strong{MySQL} requires a @code{PRIMARY KEY} in each BDB table to be +able to refer to previously read rows. If you don't create one, +@strong{MySQL} will create an maintain a hidden @code{PRIMARY KEY} for +you. The hidden key has a length of 5 bytes and is incremented for each +insert attempt. +@item +If all columns you access in a @code{BDB} table are part of the same index or +part of the primary key, then @strong{MySQL} can execute the query +without having to access the actual row. In a @code{MyISAM} table the +above holds only if the columns are part of the same index. +@item +The @code{PRIMARY KEY} will be faster than any other key, as the +@code{PRIMARY KEY} is stored together with the row data. As the other keys are +stored as the key data + the @code{PRIMARY KEY}, it's important to keep the +@code{PRIMARY KEY} as short as possible to save disk and get better speed. +@item +@code{LOCK TABLES} works on @code{BDB} tables as with other tables. If +you don't use @code{LOCK TABLE}, @strong{MYSQL} will issue an internal +multiple-write lock on the table to ensure that the table will be +properly locked if another thread issues a table lock. +@item +Internal locking in @code{BDB} tables is done on page level. +@item +@code{SELECT COUNT(*) FROM table_name} is slow as @code{BDB} tables doesn't +maintain a count of the number of rows in the table. +@item +Scanning is slower than with @code{MyISAM} tables as one has data in BDB +tables stored in B-trees and not in a separate data file. +@item +The application must always be prepared to handle cases where +any change of a @code{BDB} table may make an automatic rollback and any +read may fail with a deadlock error. +@item +Keys are not compressed to previous keys as with ISAM or MyISAM +tables. In other words, the key information will take a little more +space in @code{BDB} tables compared to MyISAM tables which don't use +@code{PACK_KEYS=0}. +@item +There is often holes in the BDB table to allow you to insert new rows in +the middle of the key tree. This makes BDB tables somewhat larger than +MyISAM tables. +@item +The optimizer needs to know an approximation of the number of rows in +the table. @strong{MySQL} solves this by counting inserts and +maintaining this in a separate segment in each BDB table. If you don't +do a lot of @code{DELETE} or @code{ROLLBACK}:s this number should be +accurate enough for the @strong{MySQL} optimizer, but as @strong{MySQL} +only store the number on close, it may be wrong if @strong{MySQL} dies +unexpectedly. It should not be fatal even if this number is not 100 % +correct. One can update the number of rows by executing @code{ANALYZE +TABLE} or @code{OPTIMIZE TABLE}. @xref{ANALYZE TABLE} . @xref{OPTIMIZE +TABLE}. +@item +If you get full disk with a @code{BDB} table, you will get an error +(probably error 28) and the transaction should roll back. This is in +contrast with @code{MyISAM} and @code{ISAM} tables where @code{mysqld} will +wait for enough free disk before continuing. +@end itemize + +@node BDB TODO, BDB portability, BDB characteristic, BDB +@subsection Some things we need to fix for BDB in the near future: + +@itemize @bullet +@item +It's very slow to open many BDB tables at the same time. If you are +going to use BDB tables, you should not have a very big table cache (> +256 ?) and you should use @code{--no-auto-rehash} with the @code{mysql} +client. We plan to partly fix this in 4.0. +@item +@code{SHOW TABLE STATUS} doesn't yet provide that much information for BDB +tables. +@item +Optimize performance. +@item +Change to not use page locks at all when we are scanning tables. +@end itemize + +@node BDB portability, BDB errors, BDB TODO, BDB +@subsection Operating systems supported by @strong{BDB} + +If you after having built @strong{MySQL} with support for BDB tables get +the following error in the log file when you start @code{mysqld}: + +@example +bdb: architecture lacks fast mutexes: applications cannot be threaded +Can't init dtabases +@end example + +This means that @code{BDB} tables are not supported for your architecture. +In this case you have to rebuild @strong{MySQL} without BDB table support. + +NOTE: The following list is not complete; We will update this as we get +more information about this. + +Currently we know that BDB tables works with the following operating +system. + +@itemize @bullet +@item +Linux 2.x intel +@item +Solaris sparc +@item +SCO OpenServer +@item +SCO UnixWare 7.0.1 +@end itemize + +It doesn't work with the following operating systems: + +@itemize @bullet +@item +Linux 2.x Alpha +@item +Max OS X +@end itemize + +@node BDB errors, , BDB portability, BDB +@subsection Errors You May Get When Using BDB Tables + +@itemize @bullet +@item +If you get the following error in the @code{hostname.err log} when +starting @code{mysqld}: + +@example +bdb: Ignoring log file: .../log.XXXXXXXXXX: unsupported log version # +@end example +it means that the new @code{BDB} version doesn't support the old log +file format. In this case you have to delete all @code{BDB} log BDB +from your database directory (the files that has the format +@code{log.XXXXXXXXXX} ) and restart @code{mysqld}. We would also +recommend you to do a @code{mysqldump --opt} of your old @code{BDB} +tables, delete the old table and restore the dump. +@item +If you are running in not @code{auto_commit} mode and delete a table you +are using by another thread you may get the following error messages in +the @strong{MySQL} error file: + +@example +001119 23:43:56 bdb: Missing log fileid entry +001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid +@end example + +This is not fatal but we don't recommend that you delete tables if you are +not in @code{auto_commit} mode, until this problem is fixed (the fix is +not trivial). +@end itemize + +@cindex GEMINI tables +@node GEMINI, InnoDB, BDB, Table types +@section GEMINI Tables + +@cindex GEMINI tables, overview +@menu +* GEMINI Overview:: +* Using GEMINI Tables:: +@end menu + +@node GEMINI Overview, Using GEMINI Tables, GEMINI, GEMINI +@subsection GEMINI Overview + +GEMINI is currently not included in the @strong{MySQL} 3.23 distribution +because it's not to our knowledge an open source (GPL) product. + +@code{GEMINI} is a transaction-safe table handler for @strong{MySQL}. It +provides row-level locking, robust transaction support and reliable +crash recovery. It is targeted for databases that need to handle heavy +multi-user updates typical of transaction processing applications while +still providing excellent performance for read-intensive operations. The +@code{GEMINI} table type is developed and supported by NuSphere +Corporation (see @url{http://www.nusphere.com}). + +@code{GEMINI} provides full ACID transaction properties (Atomic, +Consistent, Independent, and Durable) with a programming model that +includes support for statement atomicity and all four standard isolation +levels (Read Uncommitted, Read Committed, Repeatable Read, and +Serializable) defined in the SQL standard. + +The @code{GEMINI} tables support row-level and table-level locking to +increase concurrency in applications and allow reading of tables without +locking for maximum concurrency in a heavy update environment. The +transaction, locking, and recovery mechanisms are tightly integrated to +eliminate unnecessary administration overhead. + +In general, if @code{GEMINI} tables are selected for an application, it +is recommended that all tables updated in the application be +@code{GEMINI} tables to provide well-defined system behavior. If +non-@code{GEMINI} tables are mixed into the application then, ACID +transaction properties cannot be maintained. While there are clearly +cases where mixing table types is appropriate, it should always be done +with careful consideration of the impact on transaction consistency and +recoverability needs of the application and underlying database. + +The @code{GEMINI} table type is derived from a successful commercial +database and uses the storage kernel technology tightly integrated with +@strong{MySQL} server. The basic @code{GEMINI} technology is in use by +millions of users worldwide in production environments today. This +maturity allows @code{GEMINI} tables to provide a solution for those +users who require transaction-based behavior as part of their +applications. + +The @code{GEMINI} table handler supports a configurable data cache that +allows a significant portion of any database to be maintained in memory +while still allowing durable updates. + +@cindex GEMINI tables, features +@menu +* GEMINI Features:: +* GEMINI Concepts:: +* GEMINI Limitations:: +@end menu + +@node GEMINI Features, GEMINI Concepts, GEMINI Overview, GEMINI Overview +@subsubsection GEMINI Features + +The following summarizes the major features provided by @code{GEMINI} +tables. + +@itemize @bullet +@item +Supports all optimization statistics used by the @strong{MySQL} optimizer +including table cardinality, index range estimates and multi-component +selectivity to insure optimal query performance. + +@item +Maintains exact cardinality information for each table so @code{SELECT +COUNT(*) FROM} table-name always returns an answer immediately. + +@item +Supports index-only queries; when index data is sufficient to resolve a +query no record data is read (for non character types). + +@item +@code{GEMINI} uses block based I/O for better performance. There is no +performance penalty for using @code{VARCHAR} fields. The maximum record size is +currently 32K. + +@item +The number of rows in a single @code{GEMINI} table can be 4 quintillion +(full use of 64 bits). + +@item +Individual tables can be as large as 16 petabytes. + +@item +Locking is done at a record or row level rather than at table level +unless table locks are explicitly requested. When a row is inserted into +a table, other rows can be updated, inserted or deleted without waiting +for the inserted row to be committed. + +@item +Provides durable transactions backed by a crash recovery mechanism that +returns the database to a known consistent state in the event of an +unexpected failure. + +@item +Support for all isolation levels and statement atomicity defined in the +SQL standard. + +@item +Reliable Master Replication; the master database can survive system +failure and recover all committed transactions. +@end itemize + +@cindex GEMINI tables, concepts +@node GEMINI Concepts, GEMINI Limitations, GEMINI Features, GEMINI Overview +@subsubsection GEMINI Concepts + +This section highlights some of the important concepts behind +@code{GEMINI} and the @code{GEMINI} programming model, including: + +@itemize @bullet +@item +ACID Transactions +@item +Transaction COMMIT/ROLLBACK +@item +Statement Atomicity +@item +Recovery +@item +Isolation Levels +@item +Row-Level Locking +@end itemize + +These features are described below. + +@cindex GEMINI tables, ACID transactions +@noindent +@strong{ACID Transactions} + +ACID in the context of transactions is an acronym which stands for +@emph{Atomicity}, @emph{Consistency}, @emph{Isolation}, @emph{Durability}. + +@multitable @columnfractions .25 .75 +@item @sc{Attribute} @tab @sc{Description} +@item +@strong{Atomicity} +@tab A transaction allows for the grouping of one or more changes to +tables and rows in the database to form an atomic or indivisible +operation. That is, either all of the changes occur or none of them +do. If for any reason the transaction cannot be completed, everything +this transaction changed can be restored to the state it was in prior to +the start of the transaction via a rollback operation. + +@item +@strong{Consistency} +@tab +Transactions always operate on a consistent view of the data and when +they end always leave the data in a consistent state. Data may be said to +be consistent as long as it conforms to a set of invariants, such as no +two rows in the customer table have the same customer ID and all orders +have an associated customer row. While a transaction executes, these +invariants may be violated, but no other transaction will be allowed to +see these inconsistencies, and all such inconsistencies will have been +eliminated by the time the transaction ends. + +@item +@strong{Isolation} +@tab To a given transaction, it should appear as though it is running +all by itself on the database. The effects of concurrently running +transactions are invisible to this transaction, and the effects of this +transaction are invisible to others until the transaction is committed. + +@item +@strong{Durability} +@tab Once a transaction is committed, its effects are guaranteed to +persist even in the event of subsequent system failures. Until the +transaction commits, not only are any changes made by that transaction +not durable, but are guaranteed not to persist in the face of a system +failures, as crash recovery will rollback their effects. +@end multitable + +@cindex GEMINI tables, COMMIT/ROLLBACK +@noindent +@strong{Transaction COMMIT/ROLLBACK} + +As stated above, a transaction is a group of work being done to +data. Unless otherwise directed, @strong{MySQL} considers each statement +a transaction in itself. Multiple updates can be accomplished by placing +them in a single statement, however they are limited to a single table. + +Applications tend to require more robust use of transaction +concepts. Take, for example, a system that processes an order: A row may +be inserted in an order table, additional rows may be added to an +order-line table, updates may be made to inventory tables, etc. It is +important that if the order completes, all the changes are made to all +the tables involved; likewise if the order fails, none of the changes to +the tables must occur. To facilitate this requirement, @strong{MySQL} +has syntax to start a transaction called @code{BEGIN WORK}. All +statements that occur after the @code{BEGIN WORK} statement are grouped +into a single transaction. The end of this transaction occurs when a +@code{COMMIT} or @code{ROLLBACK} statement is encountered. After the +@code{COMMIT} or @code{ROLLBACK} the system returns back to the behavior +before the @code{BEGIN WORK} statement was encountered where every +statement is a transaction. + +To permanently turn off the behavior where every statement is a +transaction, @strong{MySQL} added a variable called +@code{AUTOCOMMIT}. The @code{AUTOCOMMIT} variable can have two values, +@code{1} and @code{0}. The mode where every statement is a transaction +is when @code{AUTOCOMMIT} is set to @code{1} (@code{AUTOCOMMIT=1}). When +@code{AUTOCOMMIT} is set to @code{0} (@code{AUTOCOMMIT=0}), then every +statement is part of the same transaction until the transaction end by +either @code{COMMIT} or @code{ROLLBACK}. Once a transaction completes, a +new transaction is immediately started and the process repeats. + +Here is an example of the SQL statements that you may find in a typical +order: + +@example +BEGIN WORK; + INSERT INTO order VALUES ...; + INSERT INTO order-lines VALUES ...; + INSERT INTO order-lines VALUES ...; + INSERT INTO order-lines VALUES ...; + UPDATE inventory WHERE ...; +COMMIT; +@end example + +This example shows how to use the @code{BEGIN WORK} statement to start a +transaction. If the variable @code{AUTOCOMMIT} is set to @code{0}, then +a transaction would have been started already. In this case, the +@code{BEGIN WORK} commits the current transaction and starts a new one. + +@cindex GEMINI tables, statement atomicity +@noindent +@strong{Statement Atomicity} + +As mentioned above, when running with @code{AUTOCOMMIT} set to @code{1}, +each statement executes as a single transaction. When a statement has an +error, then all changes make by the statement must be +undone. Transactions support this behavior. Non-transaction safe table +handlers would have a partial statement update where some of the changes +from the statement would be contained in the database and other changes +from the statement would not. Work would need to be done to manually +recover from the error. + +@cindex GEMINI tables, recovery +@noindent +@strong{Recovery} + +Transactions are the basis for database recovery. Recovery is what +supports the Durability attribute of the ACID transaction. + +@code{GEMINI} uses a separate file called the Recovery Log located in +the @code{$DATADIR} directory named @code{gemini.rl}. This file +maintains the integrity of all the @code{GEMINI} tables. @code{GEMINI} +can not recover any data from non-@code{GEMINI} tables. In addition, the +@code{gemini.rl} file is used to rollback transactions in support of the +@code{ROLLBACK} statement. + +In the event of a system failure, the next time the @strong{MySQL} +server is started, @code{GEMINI} will automatically go through its +crash recovery process. The result of crash recovery is that all the +@code{GEMINI} tables will contain the latest changes made to them, and +all transactions that were open at the time of the crash will have been +rolled back. + +The @code{GEMINI} Recovery Log reuses space when it can. Space can be +reused when information in the Recovery Log is no longer needed for +crash recovery or rollback. + +@cindex GEMINI tables, isolation levels +@noindent +@strong{Isolation Levels} + +There are four isolation levels supported by @code{GEMINI}: + +@itemize @bullet +@item +READ UNCOMMITTED +@item +READ COMMITTED +@item +REPEATABLE READ +@item +SERIALIZABLE +@end itemize + +These isolation levels apply only to shared locks obtained by select +statements, excluding select for update. Statements that get exclusive +locks always retain those locks until the transaction commits or rolls +back. + +By default, @code{GEMINI} operates at the @code{READ COMMITTED} +level. You can override the default using the following command: + +@example +SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | +READ COMMITTED | REPEATABLE READ | SERIALIZABLE ] +@end example + +If the @code{SESSION} qualifier used, the specified isolation level +persists for the entire session. If the @code{GLOBAL} qualifier is used, +the specified isolation level is applied to all new connections from +this point forward. Note that the specified isolation level will not +change the behavior for existing connections including the connection +that exectues the @code{SET GLOBAL TRANSACTION ISOLATION LEVEL} +statement. + +@multitable @columnfractions .30 .70 +@item @sc{Isolation Level} @tab @sc{Description} + +@item +@strong{READ UNCOMMITTED} +@tab Does not obtain any locks when reading rows. This means that if a +row is locked by another process in a transaction that has a more strict +isolation level, the @code{READ UNCOMMITTED} query will not wait until +the locks are released before reading the row. You will get an error if +attempt any updates while running at this isolation level. + +@item +@strong{READ COMMITTED} +@tab Locks the requested rows long enough to copy the row from the +database block to the client row buffer. If a @code{READ COMMITTED} +query finds that a row is locked exclusively by another process, it will +wait until either the row has been released, or the lock timeout value +has expired. + +@item +@strong{REPEATABLE READ} +@tab Locks all the rows needed to satisfy the query. These locks are +held until the transaction ends (commits or rolls back). If a +@code{REPEATABLE READ} query finds that a row is locked exclusively by +another process, it will wait until either the row has been released, or +the lock timeout value has expired. + +@item +@strong{SERIALIZABLE} +@tab Locks the table that contains the rows needed to satisfy the +query. This lock is held until the transaction ends (commits or rolls +back). If a @code{SERIALIZABLE} query finds that a row is exclusively +locked by another process, it will wait until either the row has been +released, or the lock timeout value has expired. +@end multitable + +The statements that get exclusive locks are @code{INSERT}, +@code{UPDATE}, @code{DELETE} and @code{SELECT ... FOR UPDATE}. Select +statements without the @code{FOR UPDATE} qualifier get shared locks +which allow other not ''for update'' select statements to read the same +rows but block anyone trying to update the row from accessing it. Rows +or tables with exclusive locks block all access to the row from other +transactions until the transaction ends. + +In general terms, the higher the Isolation level the more likelihood of +having concurrent locks and therefore lock conflicts. In such cases, +adjust the @code{-O gemini_lock_table_size} accordingly. + +@cindex GEMINI tables, row-level locking +@noindent +@strong{Row-Level Locking} + +@code{GEMINI} uses row locks, which allows high concurrency for requests +on the same table. + +In order to avoid lock table overflow, SQL statements that require +applying locks to a large number of rows should either be run at the +serializable isolation level or should be covered by a lock table +statement. + +Memory must be pre-allocated for the lock table. The mysqld server +startup option @code{-0 gemini_lock_table_size} can be used to adjust +the number of concurrent locks. + +@cindex GEMINI tables, limitations +@node GEMINI Limitations, , GEMINI Concepts, GEMINI Overview +@subsubsection GEMINI Limitations + +The following limitations are in effect for the current version of +@code{GEMINI}: + +@itemize @bullet +@item +@code{DROP DATABASE} does not work with @code{GEMINI} tables; instead, +drop all the tables in the database first, then drop the database. + +@item +Maximum number of @code{GEMINI} tables is 1012. + +@item +Maximum number of @code{GEMINI} files a server can manage is 1012. Each +table consumes one file; an additional file is consumed if the table has +any indexes defined on it. + +@item +Maximum size of BLOBs is 16MB. + +@item +@code{FULLTEXT} indexes are not supported with @code{GEMINI} tables. + +@item +There is no support for multi-component @code{AUTO_INCREMENT} fields +that provide alternating values at the component level. If you try to +create such a field, @code{GEMINI} will refuse. + +@item +@code{TEMPORARY TABLES} are not supported by @code{GEMINI}. The +statement @code{CREATE TEMPORARY TABLE ... TYPE=GEMINI} will generate +the response: @code{ERROR 1005: Can't create table '/tmp/#sqlxxxxx' +(errno: 0)}. + +@item +@code{FLUSH TABLES} has not been implemented with @code{GEMINI} tables. +@end itemize + +@cindex GEMINI tables, using +@node Using GEMINI Tables, , GEMINI Overview, GEMINI +@subsection Using GEMINI Tables + +This section explains the various startup options you can use with +@code{GEMINI} tables, how to backup @code{GEMINI} tables, some +performance considerations and sample configurations, and a brief +discussion of when to use @code{GEMINI} tables. + +Specifically, the topics covered in this section are: + +@itemize @bullet +@item +Startup Options +@item +Creating @code{GEMINI} Tables +@item +Backing Up @code{GEMINI} Tables +@item +Using Auto_Increment Columns With @code{GEMINI} Tables +@item +Performance Considerations +@item +Sample Configurations +@item +When To Use @code{GEMINI} Tables +@end itemize + +@cindex GEMINI tables, startup options +@menu +* Startup Options:: +* Creating GEMINI Tables:: +* Backing Up GEMINI Tables:: +* Restoring GEMINI Tables:: +* Using Auto_Increment Columns With GEMINI Tables:: +* Performance Considerations:: +* Sample Configurations:: +* When To Use GEMINI Tables:: +@end menu + +@node Startup Options, Creating GEMINI Tables, Using GEMINI Tables, Using GEMINI Tables +@subsubsection Startup Options + +The table below lists options to mysqld that can be used to change the +behavior of @code{GEMINI} tables. + +@multitable @columnfractions .40 .60 +@item @sc{Option} @tab @sc{Description} + +@item +@code{--default-table-type=gemini} +@tab Sets the default table handler to be @code{GEMINI}. All create +table statements will create @code{GEMINI} tables unless otherwise +specified with @code{TYPE=@var{table-type}}. As noted above, there is +currently a limitation with @code{TEMPORARY} tables using @code{GEMINI}. + +@item +@code{--gemini-flush-log-at-commit} +@tab Forces the recovery log buffers to be flushed after every +commit. This can have a serious performance penalty, so use with +caution. + +@item +@code{--gemini-recovery=FULL | NONE | FORCE} +@tab Sets the recovery mode. Default is @code{FULL}. @code{NONE} is +useful for performing repeatable batch operations because the updates +are not recorded in the recovery log. @code{FORCE} skips crash recovery +upon startup; this corrupts the database, and should be used in +emergencies only. + +@item +@code{--gemini-unbuffered-io} +@tab All database writes bypass the OS cache. This can provide a +performance boost on heavily updated systems where most of the dataset +being worked on is cached in memory with the @code{gemini_buffer_cache} +parameter. + +@item +@code{--O gemini_buffer_cache=size} +@tab Amount of memory to allocate for database buffers, including Index +and Record information. It is recommended that this number be 10% of the +total size of all @code{GEMINI} tables. Do not exceed amount of memory +on the system! + +@item +@code{--O gemini_connection_limit=#} +@tab Maximum number of connections to @code{GEMINI}; default is +@code{100}. Each connection consumes about 1K of memory. + +@item +@code{--O gemini_io_threads=#} +@tab Number of background I/O threads; default is @code{2}. Increase the +number when using @code{--gemini-unbuffered-io} + +@item +@code{--O gemini_lock_table_size=#} +@tab Sets the maximum number of concurrent locks; default is 4096. Using +@code{SET [ GLOBAL | SESSION ] TRANSACTION ISOLATION = ...} will +determine how long a program will hold row locks. + +@item +@code{--O gemini_lock_wait_timeout=seconds} +@tab Number of seconds to wait for record locks when performing queries; +default is 10 seconds. Using @code{SET [ GLOBAL | SESSION ] TRANSACTION +ISOLATION = ...} will determine how long a program will hold row locks. + +@item +@code{--skip-gemini} +@tab Do not use @code{GEMINI}. If you use @code{--skip-gemini}, @strong{MySQL} +will not initialize the @code{GEMINI} table handler, saving memory; you +cannot use @code{GEMINI} tables if you use @code{--skip-gemini}. + +@item +@code{--transaction-isolation=READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE} +@tab Sets the GLOBAL transaction isolation level for all users that +connect to the server; can be overridden with the SET ISOLATION LEVEL +statement. +@end multitable + +@cindex GEMINI tables, creating +@node Creating GEMINI Tables, Backing Up GEMINI Tables, Startup Options, Using GEMINI Tables +@subsubsection Creating GEMINI Tables + +@code{GEMINI} tables can be created by either using the @code{CREATE +TABLE} syntax or the @code{ALTER TABLE} syntax. + +@itemize @bullet +@item +The syntax for creating a @code{GEMINI} table is: + +@example +CREATE TABLE @var{table-name} (....) TYPE=GEMINI; +@end example + +@item +The syntax to convert a table to @code{GEMINI} is: + +@example +ALTER TABLE @var{table-name} TYPE=GEMINI; +@end example +@end itemize + +@xref{Tutorial}, for more information on how to create and use +@code{MySQL} tables. + +@cindex GEMINI tables, backing up +@node Backing Up GEMINI Tables, Restoring GEMINI Tables, Creating GEMINI Tables, Using GEMINI Tables +@subsubsection Backing Up GEMINI Tables + +@code{GEMINI} supports both @code{BACKUP TABLE} and @code{RESTORE TABLE} +syntax. To learn more about how to use @code{BACKUP} and @code{RESTORE}, +see @ref{BACKUP TABLE} and @ref{RESTORE TABLE}. + +To backup @code{GEMINI} tables outside of the @code{MySQL} environment, +you must first shut down the @code{MySQL} server. Once the server is +shut down, you can copy the files associated with @code{GEMINI} to a +different location. The files that make up the @code{GEMINI} table +handler are: + +@itemize @bullet +@item +All files associated with a table with a @code{.gmd} extention below the +@code{$DATADIR} directory. Such files include @code{@var{table}.gmd}, +@code{@var{table}.gmi}, and @code{@var{table}.frm} +@item +@code{gemini.db} in the @code{$DATADIR} directory +@item +@code{gemini.rl} in the @code{$DATADIR} directory +@item +@code{gemini.lg} in the @code{$DATADIR} directory +@end itemize + +All the @code{GEMINI} files must be copied together. You can not copy +just the @code{.gmi} and @code{.gmd} files to a different +@code{$DATADIR} and have them become part of a new database. You can +copy an entire @code{$DATADIR} directory to another location and start a +@strong{MySQL} server using the new @code{$DATADIR}. + +@cindex GEMINI tables, restoring +@node Restoring GEMINI Tables, Using Auto_Increment Columns With GEMINI Tables, Backing Up GEMINI Tables, Using GEMINI Tables +@subsubsection Restoring GEMINI Tables + +To restore @code{GEMINI} tables outside of the @code{MySQL} environment, +you must first shut down the @code{MySQL} server. Once the server is +shut down, you can remove all @code{GEMINI} files in the target +@code{$DATADIR} and then copy the files previously backed up into the +@code{$DATADIR} directory. + +As mentioned above, the files that make up the @code{GEMINI} table +handler are: + +@itemize @bullet +@item +All files associated with a table with a @code{.gmd} extention below the +@code{$DATADIR} directory. Such files include @code{@var{table}.gmd}, +@code{@var{table}.gmi}, and @code{@var{table}.frm} +@item +@code{gemini.db} in the @code{$DATADIR} directory +@item +@code{gemini.rl} in the @code{$DATADIR} directory +@item +@code{gemini.lg} in the @code{$DATADIR} directory +@end itemize + +When restoring a table, all the @code{GEMINI} files must be copied +together. You can not restore just the @code{.gmi} and @code{.gmd} +files. + +@cindex GEMINI tables, auto_increment +@node Using Auto_Increment Columns With GEMINI Tables, Performance Considerations, Restoring GEMINI Tables, Using GEMINI Tables +@subsubsection Using Auto_Increment Columns With GEMINI Tables + +As mentioned previously, @code{GEMINI} tables support row-level and +table-level locking to increase concurrency in applications and to allow +reading of tables without locking for maximum concurrency in heavy +update environments. This feature has several implications when working +with @code{auto_increment} tables. + +In @code{MySQL}, when a column is defined as an @code{auto_increment} +column, and a row is inserted into the table with a @code{NULL} for the +column, the @code{auto_increment} column is updated to be 1 higher than +the highest value in the column. + +With @code{MyISAM} tables, the @code{auto_increment} function is +implemented by looking in the index and finding the highest value and +adding 1 to it. This is possible because the entire @code{ISAM} table is +locked during the update period and the increment value is therefore +guaranteed to not be changing. + +With @code{GEMINI} tables, the @code{auto_increment} function is +implemented by maintaining a counter in a separate location from the +table data. Instead of looking at the highest value in the table index, +@code{GEMINI} tables look at this separately maintained counter. This +means that in a transactional model, unlike the bottleneck inherent in +the @code{MyISAM} approach, @code{GEMINI} users do @b{not} have to wait +until the transaction that added the last value either commits or +rollbacks before looking at the value. + +Two side-effects of the @code{GEMINI} implementation are: + +@itemize @bullet +@item +If an insert is done where the column with the @code{auto_increment} is +specified, and this specified value is the highest value, @code{MyISAM} +uses it as its @code{auto_increment} value, and every subsequent insert +is based on this. By contrast, @code{GEMINI} does not use this value, +but instead uses the value maintained in the separate @code{GEMINI} +counter location. + +@item +To set the counter to a specific value, you can use @code{SET +insert_id=#} and insert a new row in the table. However, as a general +rule, values should not be inserted into an @code{auto_increment} +column; the database manager should be maintaining this field, not the +application. @code{SET insert_id} is a recovery mechanism that should be +used in case of error only. +@end itemize + +Note that if you delete the row containing the maximum value for an +@code{auto_increment} column, the value will be reused with a +@code{GEMINI} table but not with a @code{MyISAM} table. + +See @ref{CREATE TABLE} for more information about creating +@code{auto_increment} columns. + +@cindex GEMINI tables, peformance considerations +@node Performance Considerations, Sample Configurations, Using Auto_Increment Columns With GEMINI Tables, Using GEMINI Tables +@subsubsection Performance Considerations + +In addition to designing the best possible application, configuration of +the data and the server startup parameters need to be considered. How +the hardware is being used can have a dramatic affect on how fast the +system will respond to queries. Disk Drives and Memory must both be +considered. + +@noindent +@strong{Disk Drives} + +For best performance, you want to spread the data out over as many disks +as possible. Using RAID 10 stripes work very well. If there are a lot of +updates then the recovery log (@code{gemini.rl}) should be on a +relatively quiet disk drive. + +To spread the data out without using RAID 10, you can do the following: + +@itemize @bullet +@item +Group all the tables into three categories: Heavy Use, Moderate Use, +Light Use. + +@item +Take the number of disk drives available and use a round-robin approach +to the three categories grouping the tables on a disk drive. The result +will be an equal distribution of Heavy/Moderate/Light tables assigned to +each disk drive. + +@item +Once the tables have been converted to @code{GEMINI} by using the +@code{ALTER TABLE <name> TYPE=GEMINI} statements, move (@code{mv}) the +@code{.gmd} and @code{.gmi} files to a different disk drive and link +(@code{ln -s}) them back to the original directory where the @code{.frm} +file resides. + +@item +Finally, move the @code{gemini.rl} file to its quiet disk location and link +the file back to the @code{$DATADIR} directory. +@end itemize + +@noindent +@strong{Memory} + +The more data that can be placed in memory the faster the access to the +data. Figure out how large the @code{GEMINI} data is by adding up the +@code{.gmd} and @code{.gmi} file sizes. If you can, put at least 10% of +the data into memory. You allocate memory for the rows and indexes by +using the @code{gemini_buffer_cache} startup parameter. For example: + +@example +mysqld -O gemini_buffer_cache=800M +@end example + +@noindent +would allocate 800 MB of memory for the @code{GEMINI} buffer cache. + +@cindex GEMINI tables, sample configurations +@node Sample Configurations, When To Use GEMINI Tables, Performance Considerations, Using GEMINI Tables +@subsubsection Sample Configurations + +Based on the performance considerations above, we can look at some +examples for how to get the best performance out of the system when +using @code{GEMINI} tables. + +@multitable @columnfractions .30 .70 +@item @sc{Hardware} @tab @sc{Configuration} +@item +One CPU, 128MB memory, one disk drive +@tab Allocate 80MB of memory for reading and updating @code{GEMINI} +tables by starting the mysqld server with the following option: + +@example +-O gemini_buffer_cache=80M +@end example + +@item +Two CPUs, 512MB memory, four disk drives +@tab Use RAID 10 to stripe the data across all available disks, or use +the method described in the performance considerations section, +above. Allocate 450MB of memory for reading/updating @code{GEMINI} +tables: + +@example +-O gemini_buffer_cache=450M +@end example +@end multitable + +@cindex GEMINI tables, when to use +@node When To Use GEMINI Tables, , Sample Configurations, Using GEMINI Tables +@subsubsection When To Use GEMINI Tables + +Because the @code{GEMINI} table handler provides crash recovery and +transaction support, there is extra overhead that is not found in other +non-transaction safe table handlers. Here are some general guidelines +for when to employ @code{GEMINI} and when to use other non-transaction +safe tables (@code{NTST}). + +Note that in the following table, you could instead of GEMINI use +InnoDB or BDB tables. + +@multitable @columnfractions .30 .25 .45 +@item +@sc{Access Trends} @tab @sc{Table Type} @tab @sc{Reason} +@item +Read-only +@tab @code{NTST} +@tab Less overhead and faster +@item +Critical data +@tab @code{GEMINI} +@tab Crash recovery protection +@item +High concurrency +@tab @code{GEMINI} +@tab Row-level locking +@item +Heavy update +@tab @code{GEMINI} +@tab Row-level locking +@end multitable + +The table below shows how a typical application schema could be defined. + +@multitable @columnfractions .15 .30 .25 .30 +@item +@sc{Table} @tab @sc{Contents} @tab @sc{Table Type} @tab @sc{Reason} +@item +account +@tab Customer account data +@tab @code{GEMINI} +@tab Critical data, heavy update +@item +order +@tab Orders for a customer +@tab @code{GEMINI} +@tab Critical data, heavy update +@item +orderline +@tab Orderline detail for an order +@tab @code{GEMINI} +@tab Critical data, heavy update +@item +invdesc +@tab Inventory description +@tab @code{NTST} +@tab Read-only, frequent access +@item +salesrep +@tab Sales rep information +@tab @code{NTST} +@tab Infrequent update +@item +inventory +@tab Inventory information +@tab @code{GEMINI} +@tab High concurrency, critical data +@item +config +@tab System configuration +@tab @code{NTST} +@tab Read-only +@end multitable @cindex tutorial @cindex terminal monitor, defined @@ -28272,8 +29429,9 @@ your changes with the new @file{errmsg.txt} file. @node Character sets, Adding character set, Languages, Languages @subsection The Character Set Used for Data and Sorting -By default, @strong{MySQL} uses the ISO-8859-1 (Latin1) character -set. This is the character set used in the USA and western Europe. +By default, @strong{MySQL} uses the ISO-8859-1 (Latin1) character set +with sorting according to Swedish/Finnish. This is the character set suitable +in the USA and western Europe. All standard @strong{MySQL} binaries are compiled with @code{--with-extra-charsets=complex}. This will add code to all @@ -28285,12 +29443,12 @@ The character set determines what characters are allowed in names and how things are sorted by the @code{ORDER BY} and @code{GROUP BY} clauses of the @code{SELECT} statement. -You can change the character set with the -@code{--default-character-set} option when you start the server. -The character sets available depend on the @code{--with-charset=charset} -option to @code{configure}, and the character set configuration files -listed in @file{SHAREDIR/charsets/Index}. -@xref{Quick install}. +You can change the character set with the @code{--default-character-set} +option when you start the server. The character sets available depend +on the @code{--with-charset=charset} and @code{--with-extra-charset= +list-of-charset | complex | all} options to @code{configure}, and the +character set configuration files listed in +@file{SHAREDIR/charsets/Index}. @xref{configure options}. If you change the character set when running @strong{MySQL} (which may also change the sort order), you must run myisamchk -r -q on all @@ -28823,6 +29981,11 @@ Replication will be done correctly with @code{AUTO_INCREMENT}, @code{RAND()}. You can, for example, use @code{UNIX_TIMESTAMP()} for the argument to @code{RAND()}. @item +You have to use the same character set (@code{--default-character-set}) +on the master and the slave. If not, you may get duplicate key errors on +the slave, because a key that is regarded as unique on the master may +not be that in the other character set. +@item @code{LOAD DATA INFILE} will be handled properly as long as the file still resides on the master server at the time of update propagation. @code{LOAD LOCAL DATA INFILE} will be skipped. @@ -29925,6 +31088,7 @@ are using @code{--skip-locking} @menu * Compile and link options:: How compiling and linking affects the speed of MySQL * Disk issues:: Disk issues +* Symbolic links:: Using Symbolic Links * Server parameters:: Tuning server parameters * Table cache:: How MySQL opens and closes tables * Creating many tables:: Drawbacks of creating large numbers of tables in the same database @@ -30037,7 +31201,7 @@ Linux binary is linked statically to get it faster and more portable. @cindex disk issues @cindex performance, disk issues -@node Disk issues, Server parameters, Compile and link options, System +@node Disk issues, Symbolic links, Compile and link options, System @subsection Disk Issues @itemize @bullet @@ -30115,31 +31279,40 @@ really useful on a database server), you can mount your file systems with the noatime flag. @end itemize -@menu -* Symbolic links:: Using symbolic links for databases and tables -@end menu - @cindex symbolic links @cindex links, symbolic -@cindex databases, symbolic links -@cindex tables, symbolic links -@node Symbolic links, , Disk issues, Disk issues -@subsubsection Using Symbolic Links for Databases and Tables +@node Symbolic links, Server parameters, Disk issues, System +@subsection Using Symbolic Links You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file -system with more free space. +system with more free space or increase the speed of your system by +spreading your tables to different disk. + +The recommended may to do this, is to just symlink databases to different +disk and only symlink tables as a last resort. +. + +@cindex databases, symbolic links +@menu +* Symbolic links to database:: +* Symbolic links to tables:: +@end menu + +@node Symbolic links to database, Symbolic links to tables, Symbolic links, Symbolic links +@subsubsection Using Symbolic Links for Databases -If @strong{MySQL} notices that a table is symbolically linked, it will -resolve the symlink and use the table it points to instead. This works -on all systems that support the @code{realpath()} call (at least Linux -and Solaris support @code{realpath()})! On systems that don't support -@code{realpath()}, you should not access the table through the real path -and through the symlink at the same time! If you do, the table will be -inconsistent after any update. +The way to symlink a database is to first create a directory on some +disk where you have free space and then create a symlink to it from +the @strong{MySQL} database directory. + +@example +shell> mkdir /dr1/databases/test +shell> ln -s /dr1/databases/test mysqld-datadir +@end example -@strong{MySQL} doesn't that you link one directory to multiple +@strong{MySQL} doesn't support that you link one directory to multiple databases. Replacing a database directory with a symbolic link will work fine as long as you don't make a symbolic link between databases. Suppose you have a database @code{db1} under the @strong{MySQL} data @@ -30171,11 +31344,82 @@ On Windows you can use internal symbolic links to directories by compiling @strong{MySQL} with @code{-DUSE_SYMDIR}. This allows you to put different databases on different disks. @xref{Windows symbolic links}. +@cindex databases, symbolic links +@node Symbolic links to tables, , Symbolic links to database, Symbolic links +@subsubsection Using Symbolic Links for Tables + +Before @strong{MySQL} 4.0 you should not symlink tables, if you are not +very carefully with them. The problem is that if you run @code{ALTER +TABLE}, @code{REPAIR TABLE} or @code{OPTIMIZE TABLE} on a symlinked +table, the symlinks will be removed and replaced by the original +files. This happens because the above command works by creating a +temporary file in the database directory and when the command is +complete, replace the original file with the temporary file. + +You should not symlink tables on system that doesn't have a fully +working @code{realpath()} call. (At least Linux and Solaris support +@code{realpath()}) + +In @strong{MySQL} 4.0 symlinks is only fully supported for @code{MyISAM} +tables. For other table types you will probably get strange problems +when doing any of the above mentioned commands. + +The handling of symbolic links in @strong{MySQL} 4.0 works the following +way (this is mostly relevant only for @code{MyISAM} tables). + +@itemize @bullet +@item +In the data directory you will always have the table definition file +and the data/index files. +@item +You can symlink the index file and the data file to different directories +independent of the other. +@item +The symlinking can be done from the operating system (if @code{mysqld} is +not running) or with the @code{INDEX/DATA DIRECTORY="path-to-dir"} command +in @code{CREATE TABLE}. @xref{CREATE TABLE}. +@item +@code{myisamchk} will not replace a symlink with the index/file but +work directly on the files the symlinks points to. Any temporary files +will be created in the same directory where the data/index file is. +@item +When you drop a table that is using symlinks, both the symlink and the +file the symlink points to is dropped. This is a good reason to why you +should NOT run @code{mysqld} as root and not allow persons to have write +access to the @strong{MySQL} database directories. +@item +If you rename a table with @code{ALTER TABLE RENAME} and you don't change +database, the symlink in the database directory will be renamed to the new +name and the data/index file will be renamed accordingly. +@item +If you use @code{ALTER TABLE RENAME} to move a table to another database, +then the table will be moved to the other database directory and the old +symlinks and the files they pointed to will be deleted. +@item +If you are not using symlinks you should use the @code{--skip-symlink} +option to @code{mysqld} to ensure that no one can drop or rename a file +outside of the @code{mysqld} data directory. +@end itemize + +Things that are not yet supported: + +@cindex TODO, symlinks +@itemize @bullet +@item +@code{ALTER TABLE} ignores all @code{INDEX/DATA DIRECTORY="path"} options. +@item +@code{CREATE TABLE} doesn't report if the table has symbolic links. +@item +@code{mysqldump} doesn't include the symbolic links information in the output. +@item +@code{BACKUP TABLE} and @code{RESTORE TABLE} doesn't use symbolic links. +@end itemize + @cindex parameters, server @cindex @code{mysqld} server, buffer sizes @cindex buffer sizes, @code{mysqld} server @cindex startup parameters -@node Server parameters, Table cache, Disk issues, System +@node Server parameters, Table cache, Symbolic links, System @subsection Tuning Server Parameters You can get the default buffer sizes used by the @code{mysqld} server @@ -31382,7 +32626,6 @@ Since @strong{MySQL 4.0} you can also use @code{ALTER TABLE tbl_name ENABLE KEYS} instead of @code{myisamchk -r -q /path/to/db/tbl_name}. This way you can also skip @code{FLUSH TABLES} steps. - @item You can speed up insertions by locking your tables: @@ -32151,7 +33394,7 @@ with the @code{-max} prefix. This makes it very easy to test out a another @code{mysqld} binary in an existing installation. Just run @code{configure} with the options you want and then install the new @code{mysqld} binary as @code{mysqld-max} in the same directory -where your old @code{mysqld} binary is. @xref{safe_mysqld}. +where your old @code{mysqld} binary is. @xref{safe_mysqld, , @code{safe_mysqld}}. The @code{mysqld-max} RPM uses the above mentioned @code{safe_mysqld} feature. It just installs the @code{mysqld-max} executable and @@ -32164,7 +33407,7 @@ binaries includes: @multitable @columnfractions .4 .3 .3 @item @strong{System} @tab @strong{BDB} @tab @strong{InnoDB} @item AIX 4.3 @tab N @tab Y -@item HPUX 11.0 @tab N @tab Y +@item HP-UX 11.0 @tab N @tab Y @item Linux-Alpha @tab N @tab Y @item Linux-Intel @tab Y @tab Y @item Linux-Ia64 @tab N @tab Y @@ -32399,7 +33642,7 @@ MY_PWD=`pwd` Check if we are starting this relative (for the binary release) if test -d /data/mysql -a -f ./share/mysql/english/errmsg.sys -a -x ./bin/mysqld -------------------------------------------------------------------------- -@xref{safe_mysqld}. +@xref{safe_mysqld, , @code{safe_mysqld}}. @end example The above test should be successful, or you may encounter problems. @item @@ -32927,7 +34170,7 @@ server). The dump will contain SQL statements to create the table and/or populate the table. If you are doing a backup on the server, you should consider using -the @code{mysqlhotcopy} instead. @xref{mysqlhotcopy}. +the @code{mysqlhotcopy} instead. @xref{mysqlhotcopy, , @code{mysqlhotcopy}}. @example shell> mysqldump [OPTIONS] database [tables] @@ -33760,11 +35003,16 @@ After you have installed the packed table into the @strong{MySQL} database directory you should do @code{mysqladmin flush-tables} to force @code{mysqld} to start using the new table. +If you want to unpack a packed table, you can do this with the +@code{--unpack} option to @code{isamchk} or @code{myisamchk}. + + @cindex installation maintenance @cindex maintaining, tables @cindex tables, maintaining @cindex databases, maintaining -@cindex @code{mysiamchk} +@cindex @code{myisamchk} +@cindex @code{mysqlcheck} @cindex crash, recovery @cindex recovery, from crash @node Maintenance, Adding functions, Tools, Top @@ -33772,6 +35020,7 @@ to start using the new table. @menu * Table maintenance:: Table maintenance and crash recovery +* Using mysqlcheck:: Using mysqlcheck for maintenance and recovery * Maintenance regimen:: Setting up a table maintenance regimen * Table-info:: Getting information about a table * Crash recovery:: Using @code{myisamchk} for crash recovery @@ -33782,7 +35031,7 @@ This chapter covers what you should know about maintaining a @strong{MySQL} distribution. You will learn how to care for your tables on a regular basis, and what to do when disaster strikes. -@node Table maintenance, Maintenance regimen, Maintenance, Maintenance +@node Table maintenance, Using mysqlcheck, Maintenance, Maintenance @section Using @code{myisamchk} for Table Maintenance and Crash Recovery Starting with @strong{MySQL} Version 3.23.13, you can check MyISAM @@ -34044,7 +35293,8 @@ This can be used to get faster inserts! Deactivated indexes can be reactivated by using @code{myisamchk -r}. keys. @item -l or --no-symlinks Do not follow symbolic links. Normally @code{myisamchk} repairs the -table a symlink points at. +table a symlink points at. This option doesn't exist in MySQL 4.0, +as MySQL 4.0 will not remove symlinks during repair. @item -r or --recover Can fix almost anything except unique keys that aren't unique (which is an extremely unlikely error with ISAM/MyISAM tables). @@ -34164,9 +35414,132 @@ This space is allocated on the temporary disk (specified by @code{TMPDIR} or If you have a problem with disk space during repair, you can try to use @code{--safe-recover} instead of @code{--recover}. +@node Using mysqlcheck, Maintenance regimen, Table maintenance, Maintenance +@section Using @code{mysqlcheck} for Table Maintenance and Crash Recovery + +Since @strong{MySQL} version 3.23.38 you will be able to use a new +checking and repairing tool for @code{MyISAM} tables. The difference to +@code{myisamchk} is that @code{mysqlcheck} should be used when the +@code{mysqld} server is running, where as @code{myisamchk} should be used +when it is not. The benefit is that you no longer have to take the +server down for checking or repairing your tables. + +@code{mysqlcheck} uses @strong{MySQL} server commands @code{CHECK}, +@code{REPAIR}, @code{ANALYZE} and @code{OPTIMIZE} in a convenient way +for the user. + +There are three alternative ways to invoke @code{mysqlcheck}: + +@example +shell> mysqlcheck [OPTIONS] database [tables] +shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...] +shell> mysqlcheck [OPTIONS] --all-databases +@end example + +So it can be used in a similar way as @code{mysqldump} when it +comes to what databases and tables you want to choose. + +@code{mysqlcheck} does have a special feature compared to the other +clients; the default behavior, checking tables (-c), can be changed by +renaming the binary. So if you want to have a tool that repairs tables +by default, you should just copy @code{mysqlcheck} to your harddrive +with a new name, @code{mysqlrepair}, or alternatively make a symbolic +link to @code{mysqlrepair} and name the symbolic link as +@code{mysqlrepair}. If you invoke @code{mysqlrepair} now, it will repair +tables by default. + +The names that you can use to change @code{mysqlcheck} default behavior +are here: + +@example +mysqlrepair: The default option will be -r +mysqlanalyze: The default option will be -a +mysqloptimize: The default option will be -o +@end example + +The options available for @code{mysqlcheck} are listed here, please +check what your version supports with @code{mysqlcheck --help}. + +@table @code +@item -A, --all-databases +Check all the databases. This will be same as --databases with all +databases selected +@item -1, --all-in-1 +Instead of making one query for each table, execute all queries in 1 +query separately for each database. Table names will be in a comma +separated list. +@item -a, --analyze +Analyze given tables. +@item --auto-repair +If a checked table is corrupted, automatically fix it. Repairing will be +done after all tables have been checked, if corrupted ones were found. +@item -#, --debug=... +Output debug log. Often this is 'd:t:o,filename' +@item --character-sets-dir=... +Directory where character sets are +@item -c, --check +Check table for errors +@item -C, --check-only-changed +Check only tables that have changed since last check or haven't been +closed properly. +@item --compress +Use compression in server/client protocol. +@item -?, --help +Display this help message and exit. +@item -B, --databases +To check several databases. Note the difference in usage; In this case +no tables are given. All name arguments are regarded as database names. +@item --default-character-set=... +Set the default character set +@item -F, --fast +Check only tables that hasn't been closed properly +@item -f, --force +Continue even if we get an sql-error. +@item -e, --extended +If you are using this option with CHECK TABLE, it will ensure that the +table is 100 percent consistent, but will take a long time. + +If you are using this option with REPAIR TABLE, it will run an extended +repair on the table, which may not only take a long time to execute, but +may produce a lot of garbage rows also! +@item -h, --host=... +Connect to host. +@item -m, --medium-check +Faster than extended-check, but only finds 99.99 percent of all +errors. Should be good enough for most cases. +@item -o, --optimize +Optimize table +@item -p, --password[=...] +Password to use when connecting to server. If password is not given +it's solicited on the tty. +@item -P, --port=... +Port number to use for connection. +@item -q, --quick +If you are using this option with CHECK TABLE, it prevents the check +from scanning the rows to check for wrong links. This is the fastest +check. + +If you are using this option with REPAIR TABLE, it will try to repair +only the index tree. This is the fastest repair method for a table. +@item -r, --repair +Can fix almost anything except unique keys that aren't unique. +@item -s, --silent +Print only error messages. +@item -S, --socket=... +Socket file to use for connection. +@item --tables +Overrides option --databases (-B). +@item -u, --user=# +User for login if not current user. +@item -v, --verbose +Print info about the various stages. +@item -V, --version +Output version information and exit. +@end table + @cindex maintaining, tables @cindex tables, maintenance regimen -@node Maintenance regimen, Table-info, Table maintenance, Maintenance +@node Maintenance regimen, Table-info, Using mysqlcheck, Maintenance @section Setting Up a Table Maintenance Regimen Starting with @strong{MySQL} Version 3.23.13, you can check MyISAM @@ -36966,6 +38339,9 @@ option. @node Communication errors, Full table, Packet too large, Common errors @subsection Communication Errors / Aborted Connection +Starting with @code{MySQL 3.23.40} you only get the @code{Aborted +connection} error of you start @code{mysqld} with @code{--warnings}. + If you find errors like the following in your error log. @example @@ -37355,11 +38731,15 @@ user and use the @code{--user=user_name} option. @code{mysqld} will switch to run as the Unix user @code{user_name} before accepting any connections. @item -If you are using the @code{mysql.server} script to start @code{mysqld} when -the system is rebooted, you should edit @code{mysql.server} to use @code{su} -to run @code{mysqld} as user @code{user_name}, or to invoke @code{mysqld} -with the @code{--user} option. (No changes to @code{safe_mysqld} are -necessary.) +To start the server as the given user name automatically at system +startup time, add a @code{user} line that specifies the user name to +the @code{[mysqld]} group of the @file{/etc/my.cnf} option file or the +@file{my.cnf} option file in the server's data directory. For example: + +@example +[mysqld] +user=user_name +@end example @end enumerate At this point, your @code{mysqld} process should be running fine and dandy as @@ -37486,6 +38866,8 @@ shell> perror 23 File table overflow shell> perror 24 Too many open files +shell> perror 11 +Resource temporarily unavailable @end example The problem here is that @code{mysqld} is trying to keep open too many @@ -37823,6 +39205,12 @@ Post the test file using @code{mysqlbug} to @email{mysql@@lists.mysql.com}. @node ALTER TABLE problems, Change column order, No matching rows, Problems @section Problems with @code{ALTER TABLE}. +@code{ALTER TABLE} changes a table to the current character set. +If you during @code{ALTER TABLE} get a duplicate key error, then the cause +is either that the new character sets maps to keys to the same value +or that the table is corrupted, in which case you should run +@code{REPAIR TABLE} on the table. + If @code{ALTER TABLE} dies with an error like this: @example @@ -37997,11 +39385,12 @@ database directory. The @code{FLUSH TABLE} is needed to ensure that the all active index pages is written to disk before you start the backup. If you want to make a SQL level backup of a table, you can use -@code{SELECT INTO OUTFILE} or @code{BACKUP -TABLE}. @xref{SELECT}. @xref{BACKUP TABLE}. +@code{SELECT INTO OUTFILE} or @code{BACKUP TABLE}. @xref{SELECT}. +@xref{BACKUP TABLE}. Another way to back up a database is to use the @code{mysqldump} program or -the @code{mysqlhotcopy script}. @xref{mysqldump}. @xref{mysqlhotcopy}. +the @code{mysqlhotcopy script}. @xref{mysqldump, , @code{mysqldump}}. +@xref{mysqlhotcopy, , @code{mysqlhotcopy}}. @enumerate @item @@ -38093,7 +39482,8 @@ be an Internet service provider that wants to provide independent If you want to run multiple servers, the easiest way is to compile the servers with different TCP/IP ports and socket files so they are not -both listening to the same TCP/IP port or socket file. @xref{mysqld_multi}. +both listening to the same TCP/IP port or socket file. @xref{mysqld_multi, , +@code{mysqld_multi}}. Assume an existing server is configured for the default port number and socket file. Then configure the new server with a @code{configure} command @@ -38240,7 +39630,7 @@ switch to a new log) by executing @code{FLUSH LOGS}. @xref{FLUSH}. @code{mysqld} writes all errors to the stderr, which the @code{safe_mysqld} script redirects to a file called @code{'hostname'.err}. (On Windows, @code{mysqld} writes this directly -to @file{mysql.err}). +to @file{\mysql\data\mysql.err}). This contains information indicating when @code{mysqld} was started and stopped and also any critical errors found when running. If @code{mysqld} @@ -40463,7 +41853,7 @@ query string.) If you want to know if the query should return a result set or not, you can use @code{mysql_field_count()} to check for this. -@xref{mysql_field_count, @code{mysql_field_count}}. +@xref{mysql_field_count, , @code{mysql_field_count}}. @subsubheading Return Values @@ -40525,7 +41915,7 @@ specified explicitly. @item The @code{passwd} parameter contains the password for @code{user}. If @code{passwd} is @code{NULL}, only entries in the @code{user} table for the -user that have a blank password field will be checked for a match. This +user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the @strong{MySQL} privilege system in such a way that users get different privileges depending on whether or not they have specified a password. @@ -40558,7 +41948,7 @@ of the following flags in very special circumstances: @code{mysqld} to be more ODBC-friendly. @item @code{CLIENT_COMPRESS} @tab Use compression protocol. @item @code{CLIENT_FOUND_ROWS} @tab Return the number of found (matched) rows, not the number of affected rows. -@item @code{CLIENT_IGNORE_SPACE} $tab Allow spaces after function names. Makes all functions names reserved words. +@item @code{CLIENT_IGNORE_SPACE} @tab Allow spaces after function names. Makes all functions names reserved words. @item @code{CLIENT_INTERACTIVE} @tab Allow @code{interactive_timeout} seconds (instead of @code{wait_timeout} seconds) of inactivity before closing the connection. @item @code{CLIENT_NO_SCHEMA} @tab Don't allow the @code{db_name.tbl_name.col_name} syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs. @item @code{CLIENT_ODBC} @tab The client is an ODBC client. This changes @@ -41330,6 +42720,9 @@ For more information on Object Oriented Programming @uref{http://language.perl.com/info/documentation.html} @end example +Note that if you want to use transactions with Perl, you need to have +@code{Msql-Mysql-modules} version 1.2216 or newer. + Installation instructions for @strong{MySQL} Perl support are given in @ref{Perl support}. @@ -42395,53 +43788,518 @@ users. @item @end table -@cindex PostgreSQL, comparison +@cindex PostgreSQL/MySQL, overview @node Compare PostgreSQL, , Compare mSQL, Comparisons @section How MySQL Compares to PostgreSQL +When reading the following, please note that both products are +continually evolving. We at @strong{MySQL AB} and the PostgreSQL +developers are both working on making our respective database as good as +possible, so we are both a serious choice to any commercial database. + +The following comparison is made by us at MySQL AB. We have tried to be +as accurate and fair as possible, but because we don't have a full +knowledge of all PostgreSQL features while we know MySQL througly, we +may have got some things wrong. We will however correct these when they +come to our attention. + We would first like to note that @code{PostgreSQL} and @strong{MySQL} -are both widely used products, but their design goals are completely -different. This means that for some applications @strong{MySQL} is more -suitable and for others @code{PostgreSQL} is more suitable. When -choosing which database to use, you should first check if the database's -feature set is good enough to satisfy your application. If you need -speed, @strong{MySQL} is probably your best choice. If you need some -of the extra features that @code{PostgreSQL} can offer, you should use +are both widely used products, but with different design goals, even if +we are both striving to be ANSI SQL compatible. This means that for +some applications @strong{MySQL} is more suitable and for others +@code{PostgreSQL} is more suitable. When choosing which database to +use, you should first check if the database's feature set satisfies your +application. If you need speed, @strong{MySQL} is probably your best +choice. If you need some of the extra features that only @code{PostgreSQL} +can offer, you should use @code{PostgreSQL}. + +@cindex PostgreSQL/MySQL, strategies +@menu +* MySQL-PostgreSQL goals:: MySQL and PostgreSQL development strategies +* MySQL-PostgreSQL features:: Featurevise Comparison of MySQL and PostgreSQL +* MySQL-PostgreSQL benchmarks:: Benchmarking MySQL and PostgreSQL +@end menu + +@node MySQL-PostgreSQL goals, MySQL-PostgreSQL features, Compare PostgreSQL, Compare PostgreSQL +@subsection MySQL and PostgreSQL development strategies + +When adding things to MySQL we take pride to do an optimal, definite +solution. The code should be so good that we shouldn't have any need to +change it in the foreseeable future. We also do not like to sacrifice +speed for features but instead will do our utmost to find a solution +that will give maximal throughput. This means that development will take +a little longer, but the end result will be well worth this. This kind +of development is only possible because all server code are checked by +one of a few (currently two) persons before it's included in the +@strong{MySQL} server. + +We at MySQL AB believe in frequent releases to be able to push out new +features quickly to our users. Because of this we do a new small release +about every 3 weeks, which a major branch every year. All releases are +throughly tested with our testing tools on a lot of different platforms. + +PostgreSQL is based on a kernel with lots of contributors. In this setup +it makes sense to prioritize adding a lot of new features, instead of +implementing them optimally, because one can always optimize things +later if there arises a need for this. + +Another big difference between @strong{MySQL} and PostgreSQL is that +nearly all of the code in the MySQL server are coded by developers that +are employed by MySQL AB and are still working on the server code. The +exceptions are the transaction engines and the regexp library. + +This is in sharp contrast to the PostgreSQL code where the majority of +the code is coded by a big group of people with different backgrounds. +It was only recently that the PostgreSQL developers announced that they +current developer group had finally had time to take a look at all +the code in the current PostgreSQL release. + +Both of the above development methods has it's own merits and drawbacks. +We here at @strong{MySQL AB} think of course that our model is better +because our model gives better code consistence, more optimal and +reusable code and, in our opinion, fewer bugs. Because we are the +authors of the @strong{MySQL} server code we are better able to +coordinate new features and releases. + +@cindex PostgreSQL/MySQL, features +@node MySQL-PostgreSQL features, MySQL-PostgreSQL benchmarks, MySQL-PostgreSQL goals, Compare PostgreSQL +@subsection Featurevise Comparison of MySQL and PostgreSQL + +On the @uref{http://www.mysql.com/information/crash-me.php, crash-me} +page you can find a list of those database constructs and limits that +one can detect automatically with a program. Note however that a lot of +the numerical limits may be changed with startup options for respective +database. The above web page is however extremely useful when you want to +ensure that your applications works with many different databases or +when you want to convert your application from one datbase to another. + +@strong{MySQL} offers the following advantages over PostgreSQL: + +@itemize @bullet +@item +@code{MySQL} is generally much faster than PostgreSQL. +@xref{MySQL-PostgreSQL benchmarks}. +@item +Because @strong{MySQL} has a much larger user base than PostgreSQL the +code is more tested and has historically been more stable than +PostgreSQL. @strong{MySQL} is the much more used in production +environments than PostgreSQL, mostly thanks to that @strong{MySQL AB}, +former TCX DataKonsult AB, has provided top quality commercial support +for @strong{MySQL} from the day it was released, whereas until recently +PostgreSQL was unsupported. +@item +@strong{MySQL} works on more platforms than PostgreSQL. @xref{Which OS}. +@item +@strong{MySQL} works better on Windows; @strong{MySQL} is running as a +native windows application (a service on NT/Win2000/WinXP), while +PostgreSQL is run under the cygwin emulation. We have heard that +PostgreSQL is not yet that stable on windows but we haven't been able to +verify this ourselves. +@item +@strong{MySQL} has more API to other languages and is supported by more +programs than PostgreSQL. @xref{Contrib}. +@item +@strong{MySQL} works on 24/7 heavy duty systems. In most circumstances +you never have to run any cleanups on @code{MySQL}. PostgreSQL doesn't +yet support 24/7 systems because you have have to run @code{vacuum()} +once in a while to reclaim space from @code{UPDATE} and @code{DELETE} +commands and to perform statistics analyzes that are critical to get +good performance with PostgreSQL. Vacuum is also needed after adding +a lot of new rows to a table. On a busy system with lots of changes +vacuum must be run very frequently, in the worst cases even many times a +day. During the @code{vacuum()} run, which may take hours if the +database is big, the database is from a production standpoint +practically dead. The PostgreSQL team has fixing this on their TODO, +but we assume that this is not an easy thing to fix permanently. +@item +A working, tested replication feature used by sites like +@uref{http://finance.yahoo.com, Yahoo finance}, +@uref{http://www.mobile.de/,mobile.de} and +@uref{http://www.slashdot.org,Slashdot}. +@item +Included in the @strong{MySQL} distribution is included two different +testing suits (@file{mysql-test-run} and +@uref{http://www.mysql.com/information/crash-me.php,crash-me}) and a +benchmark suite. The test system is actively updated with code to test +each new feature and almost all repeatable bugs that comes to our +attention. We test @strong{MySQL} with these on a lot of platforms +before every release. These tests are more sofisticated than anything +have seen from PostgreSQL and ensures that the @strong{MySQL} code keeps +at a high standard. +@item +There are far moore books in print on @strong{MySQL} than on PostgreSQL. +O'Reilly, Sams, Que, and New Riders are all major publishers with books +about MySQL. All @strong{MySQL} features is also documented in the +@strong{MySQL} on-line manual because when a feature is implemented, the +@strong{MySQL} developers are required to document it before it's +included in the source. +@item +@strong{MySQL} has supports more of the standard ODBC functions than @code{PostgreSQL}. +@item +@strong{MySQL} has a much more sophisticated @code{ALTER TABLE}. +@item +@strong{MySQL} has support for tables without transactions for +applications that need all speed they can get. The tables may be memory +based,@code{HEAP} tables or disk based @code{MyISAM}. @xref{Table types}. +@item +@strong{MySQL} has support for 3 different table handles that support +transactions (@code{BDB}, @code{InnoDB} and @code{Gemini}. Because +every transaction engine performs differently under different +conditions, this gives the application writer more options to find an +optimal solution for his/her setup. @xref{Table types}. +@item +@code{MERGE} tables gives you a unique way to instantly make a view over +a set of identical tables and use these as one. This is perfectly for +systems where you have log files that you order for example by month. +@xref{MERGE}. +@item +The option to compress read-only tables, but still have direct access to +the rows in the table, gives you better performance by minimizing disk +reads. This is very useful when you are archiving +things.@xref{myisampack}. +@item +@strong{MySQL} has internal support for text search. @xref{Fulltext Search}. +@item +You can access many databases from the same connection (depending of course +on your privileges). +@item +@strong{MySQL} is coded from the start with multi-threading while +PostgreSQL uses processes. Because context switching and access to +common storage areas is much faster between threads, than are separate +processes, this gives @strong{MySQL} a big speed advantage in multi-user +applications and also makes it easier for @strong{MySQL} to take full +advantage of symmetric multiprocessor systems (SMP). +@item +@strong{MySQL} has a much more sophisticated privilege system than +PostgreSQL. While PostgreSQL only supports @code{INSERT}, +@code{SELECT}, @code{update/delete} grants per user on a database or a +table @strong{MySQL} allows you to define a full set of different +privileges on database, table and columns level. @strong{MySQL} also allows +you to specify the privilege on host+user combinations. @xref{GRANT}. +@item +@strong{MySQL} supports a compressed server/client protocol which +improves performance over slow links. +@item +@strong{MySQL} employs the table handler concept and is the only +relational database we know of built around this concept. This allows +different low level table types to be swapped into the SQL engine, each +table type optimized for a different performance characteristics. +@item +All @code{MySQL} table types (except @strong{InnoDB}) are implemented as +files (ie: one table per file), which makes it really easy to backup, +move, delete and even symlink databases and tables when the server is +down. +@item +Tools to repair and optimize @strong{MyISAM} tables (the most common +@strong{MySQL} table type). A repair tool is only needed when a +physical corruption of a data file happens, usually from a hardware +failure. It allows a majority of the data to be recovered. +@item +Upgrading @strong{MySQL} is painless. When you are upgrading @strong{MySQL}, +you don't need to dump/restore your data, as you have to do with most +PostgreSQL upgrades. +@end itemize + +Drawbacks with @strong{MySQL} compared to PostgreSQL: + +@itemize @bullet +@item +The transaction support in @strong{MySQL} is not yet as well tested as +PostgreSQL's system. +@item +Because @strong{MySQL} uses threads, which are still a moving target on +many OS, one must either use binaries from +@uref{http://www.mysql.com/downloads} or carefully follow our +instructions on +@uref{http://www.mysql.com/doc/I/n/Installing_source.html} to get an +optimal binary that works in all cases. +@item +Table locking, as used by the non-transactional @code{MyISAM} tables, is +in many cases faster than page locks, row locks or versioning. The +drawback however is that if one doesn't take into account how table +locks works, a single long-running query can block a table for updates +for a long time. This can usable be avoided when designing the +application. If not, one can always switch the trouble table to use one +of the transactional table types. @xref{Table locking}. +@item +With UDF (user defined functions) one can extend @strong{MySQL} with +both normal SQL functions and aggregates, but this is not as easy or as +flexible as in PostgreSQL. @xref{Adding functions}. +@item +Updates and deletes that goes over multiple tables is harder to do in +@strong{MySQL}. (Will be fixed in @strong{MySQL} 4.0 with multi-table +@code{DELETE} and multi-table @code{UPDATE} and in @strong{MySQL} 4.1 +with @code{SUB-SELECT}) +@end itemize + +PostgreSQL offers currently the following advantages over @strong{MySQL}: + +Note that because we know the @strong{MySQL} road map, we have included +in the following table the version when @strong{MySQL} should support +this feature. Unfortunately we couldn't do this for previous comparison, +because we don't know the PostgreSQL roadmap. + +@multitable @columnfractions .70 .30 +@item @strong{Feature} @tab @strong{MySQL version} +@item Subselects @tab 4.1 +@item Foreign keys @tab 4.0 and 4.1 +@item Views. @tab 4.2 +@item Stored procedures in multiple languages @tab 4.1 +@item Extensible type system. @tab Not planed +@item Unions @tab 4.0. +@item Full join. @tab 4.0 or 4.1. +@item Triggers. @tab 4.1 +@item Constrainst @tab 4.1 +@item Cursors @tab 4.1 or 4.2 +@item Extensible index types like R-trees @tab R-trees are planned to 4.2 +@item Inherited tables @tab Not planned +@end multitable -@code{PostgreSQL} has some more advanced features like user-defined -types, triggers, rules, and some transaction support (currently it -has about the same semantics as @strong{MySQL}'s transactions in that the -transaction is not 100% atomic). However, PostgreSQL lacks many of the -standard types and functions from ANSI SQL and ODBC. See the @code{crash-me} -Web page (@uref{http://www.mysql.com/information/crash-me.php}) for a complete -list of limits and which types and functions are supported or unsupported. +Other reasons to use PostgreSQL: -Normally, @code{PostgreSQL} is a magnitude slower than @strong{MySQL}. -@xref{Benchmarks}. This is due largely to the fact that they have only -transaction-safe tables and that their transactions system is not as -sophisticated as Berkeley DB's. In @strong{MySQL} you can decide per -table if you want the table to be fast or take the speed penalty of -making it transaction-safe. +@itemize @bullet +@item +Standard usage is in PostgreSQL closer to ANSI SQL in some cases. +@item +One can get speed up PostgreSQL by coding things as stored procedures. +@item +Bigger team of developers that contributes to the server. +@end itemize -The most important things that @code{PostgreSQL} supports that @strong{MySQL} -doesn't yet support: +Drawbacks with PostgreSQL compared to @strong{MySQL}: -@table @code -@item Sub select -@item Foreign keys -@item Stored procedures -@item An extendable type system. -@item A way to extend the SQL to handle new key types (like R-trees) -@end table +@itemize @bullet +@item +@code{Vaccum()} makes PostgreSQL hard to use in a 24/7 environment. +@item +Only transactional tables. +@item +Much slower insert/delete/update. +@end itemize + +For a complete list of drawbacks, you should also examine the first table +in this section. -@strong{MySQL}, on the other hand, supports many ANSI SQL constructs -that @code{PostgreSQL} doesn't support. Most of these can be found at the -@uref{http://www.mysql.com/information/crash-me.php, @code{crash-me} Web page}. +@cindex PostgreSQL/MySQL, benchmarks +@node MySQL-PostgreSQL benchmarks, , MySQL-PostgreSQL features, Compare PostgreSQL +@subsection Benchmarking MySQL and PostgreSQL -If you really need the rich type system @code{PostgreSQL} offers and you -can afford the speed penalty of having to do everything transaction -safe, you should take a look at @code{PostgreSQL}. +The only open source benchmark, that we know of, that can be used to +benchmark @strong{MySQL} and PostgreSQL (and other databases) is our +own. It can be found at: +@uref{http://www.mysql.com/information/benchmarks.html}. + +We have many times asked the PostgreSQL developers and some PostgreSQL +users to help us extend this benchmark to make the definitive benchmark +for databases, but unfortunately we haven't got any feedback for this. + +We, the @strong{MySQL} developers, have because of this spent a lot of +hours to get maximum performance from PostgreSQL for the benchmarks, but +because we don't know PostgreSQL intimately we are sure that there are +things that we have missed. We have on the benchmark page documented +exactly how we did run the benchmark so that it should be easy for +anyone to repeat and verify our results. + +The benchmarks are usually run with and without the @code{--fast} +option. When run with @code{--fast} we are trying to use every trick +the server can do to get the code to execute as fast as possible. +The idea is that the normal run should show how the server would work in +a default setup and the @code{--fast} run shows how the server would do +if the application developer would use extensions in the server to make +his application run faster. + +When running with PostgreSQL and @code{--fast} we do a @code{vacuum()} +between after every major table update and drop table to make the database +in perfect shape for the following selects. The time for vacuum() is +measured separately. + +When running with PostgreSQL 7.1.1 we could however not run with +@code{--fast} because during the insert test, the postmaster (the +PostgreSQL deamon) died and the database was so corrupted that it was +impossible to restart postmaster. (The details about the machine we run +the benchmark can be found on the benchmark page). After this happened +twice, we decided to postpone the @code{--fast} test until next +PostgreSQL release. + +Before going to the other benchmarks we know of, We would like to give +some background to benchmarks: + +It's very easy to write a test that shows ANY database to be best +database in the world, by just restricting the test to something the +database is very good at and not test anything that the database is not +good at; If one after this publish the result with a single figure +things is even easier. + +This would be like we would measure the speed of @strong{MySQL} compared +to PostgreSQL by looking at the summary time of the MySQL benchmarks on +our web page. Based on this @strong{MySQL} would be more than 40 times +faster than PostgreSQL, something that is of course not true. We could +make things even worse by just taking the test where PostgreSQL performs +worst and claim that @strong{MySQL} is more than 2000 times faster than +PostgreSQL. + +The case is that @strong{MySQL} does a lot of optimizations that +PostgreSQL doesn't do and the other way around. An SQL optimizer is a +very complex thing and a company could spend years on just making the +optimizer faster and faster. + +When looking at the benchmark results you should look for things that +you do in your application and just use these results to decide which +database would be best suited for your application. The benchmark +results also shows things a particular database is not good at and should +give you a notion about things to avoid and what you may have to do in +other ways. + +We know of two benchmark tests that claims that PostgreSQL performers +better than @strong{MySQL}. These both where multi-user tests, a test +that we here at @strong{MySQL AB} haven't had time to write and include in +the benchmark suite, mainly because it's a big task to do this in a +manner that is fair against all databases. + +One is the benchmark paid for by +@uref{http://www.greatbridge.com/about/press.php?content_id=4,Great +Bridge}. + +This is the worst benchmark we have ever seen anyone ever conduct. This +was not only tuned to only test what PostgreSQL is absolutely best at, +it was also totally unfair against every other database involved in the +test. + +@strong{NOTE}: We know that not even some of the main PostgreSQL +developers did like the way Great Bridge conducted the benchmark, so we +don't blame them for the way the benchmark was made. + +This benchmark has been condemned in a lot of postings and newsgroups so +we will here just shortly repeat some things that where wrong with it. + +@itemize @bullet +@item +The tests where run with an expensive commercial tool, that makes it +impossible for an open source company like us to verify the benchmarks, +or even check how the benchmark where really done. The tool is not even +a true benchmark tool, but a application/setup testing tool. To refer +this as STANDARD benchmark tool is to stretch the truth a long way. +@item +Great Bridge admitted that they had optimized the PostgreSQL database +(with vacuum() before the test) and tuned the startup for the tests, +something they hadn't done for any of the other databases involved. To +say "This process optimizes indexes and frees up disk space a bit. The +optimized indexes boost performance by some margin". Our benchmarks +clearly indicates that the difference in running a lot of selects on a +database with and without vacuum() can easily differ by a factor of 10. +@item +The test results where also strange; The AS3AP test documentation +mentions that the test does: + +"selections, simple joins, projections, aggregates, one-tuple updates, +and bulk updates" + +PostgreSQL is good at doing selects and joins (especially after a +vacuum()), but doesn't perform as well on inserts/updates; The +benchmarks seem to indicate that only SELECTs where done (or very few +updates) . This could easily explain they good results for PostgreSQL in +this test. The bad results for MySQL will be obvious a bit down in this +document. +@item +They did run the so called benchmark from a Windows machine against a +Linux machine over ODBC, a setup that no normal database user would ever +do when running a heavy multi-user application. This tested more the +ODBC driver and the Windows protocol used between the clients than the +database itself. +@item +When running the database against Oracle and MS-SQL (Great Bridge has +indirectly indicated that the databases they used in the test), +they didn't use the native protocol but instead ODBC. Anyone that has +ever used Oracle, knows that all real application uses the native +interface instead of ODBC. Doing a test through ODBC and claiming that +the results had anything to do with using the database for real can't +be regarded as fair play. They should have done two tests with and +without ODBC to provide the right facts (after having got experts to tune +all involved databases of course). +@item +They refer to the TPC-C tests, but doesn't anywhere mention that the +tests they did where not a true TPC-C test and they where not even +allowed to call it a TPC-C test. A TPC-C test can only be conducted by +the rules approved by the @uref{http://www.tpc.org,TPC-council}. Great +Bridge didn't do that. By doing this they have both violated the TPC +trademark and miscredited their own benchmarks. The rules set by the +TPC-council are very strict to ensure that no one can produce false +results or make unprovable statements. Apparently Great Bridge wasn't +interested in doing this. +@item +After the first test, we contacted Great Bridge and mentioned to them +some of the obvious mistakes they had done with @strong{MySQL}; Running +with a debug version of our ODBC driver, running on a Linux system that +wasn't optimized for threads, using an old MySQL version when there was +a recommended newer one available, not starting @strong{MySQL} with the +right options for heavy multi-user use (the default installation of +MySQL is tuned for minimal resource use). Great Bridge did run a new +test, with our optimized ODBC driver and with better startup options for +MySQL, but refused to either use our updated glibc library or our +standard binary (used by 80% of our users), which was statically linked +with a fixed glibc library. + +According to what we know, Great Bridge did nothing to ensure that the +other databases where setup correctly to run good in their test +environment. We are sure however that they didn't contact Oracle or +Microsoft to ask for their advice in this matter ;) +@item +The benchmark was paid for by Great Bridge, and they decided to publish +only partial chosen results (instead of publishing it all). +@end itemize + +Tim Perdue, a long time PostgreSQL fan and a reluctant MySQL user +published a comparison on +@uref{http://www.phpbuilder.com/columns/tim20001112.php3,phpbuider}. + +When we got aware of the comparison, we phoned Tim Perdue about this +because there was a lot of strange things in his results. For example, +he claimed that MySQL had a problem with five users in his tests, when we +know that there are users with similar machines as his that are using +MySQL with 2000 simultaneous connections doing 400 queries per second (In +this case the limit was the web bandwidth, not the database). + +It sounded like he was using a Linux kernel that either had some +problems with many threads (Linux kernels before 2.4 had a problem with +this but we have documented how to fix this and Tim should be aware of +this problem). The other possible problem could have been an old glibc +library and that Tim didn't use a MySQL binary from our site, which is +linked with a corrected glibc library, but had compiled a version of his +own with. In any of the above cases, the symptom would have been exactly +what Tim had measured. + +We asked Tim if we could get access to his data so that we could repeat +the benchmark and if he could check the MySQL version on the machine to +find out what was wrong and he promised to come back to us about this. +He has not done that yet. + +Because of this we can't put any trust in this benchmark either :( + +Conclusion: + +The only benchmarks that exist today that anyone can download and run +against @strong{MySQL}and PostgreSQL is the MySQL benchmarks. We here +at @strong{MySQL} believe that open source databases should be tested +with open source tools! This is the only way to ensure that no one +does tests that nobody can reproduce and use this to claim that a +database is better than another. Without knowing all the facts it's +impossible to answer the claims of the tester. + +The thing we find strange is that every test we have seen about +PostgreSQL, that is impossible to reproduce, claims that PostgreSQL is +better in most cases while our tests, which anyone can reproduce, +clearly shows otherwise. With this we don't want to say that PostgreSQL +isn't good at many things (It is!) We would just like to see a fair test +where they are very good so that we could get some friendly competition +going! + +For more information about our benchmarks suite see @xref{MySQL +Benchmarks}. + +We are working on an even better benchmark suite, including much better +documentation of what the individual tests really do and how to add more +tests to the suite. @cindex internals @cindex threads @@ -42648,7 +44506,7 @@ attachments, you should ftp all the relevant files to: @end itemize @node Reporting mysqltest bugs, , extending mysqltest, MySQL test suite -@subsection Extending the MySQL Test Suite +@subsection Reporting bugs in the MySQL Test Suite If your @strong{MySQL} version doesn't pass the test suite you should do the following: @@ -42680,6 +44538,10 @@ description of your system, the version of the mysqld binary and how you compiled it. @item +Try also to run @code{mysql-test-run} with the @code{--force} option to +see if there is any other test that fails. + +@item If you have compiled @strong{MySQL} yourself, check our manual for how to compile @strong{MySQL} on your platform or, preferable, use one of the binaries we have compiled for you at @@ -42919,6 +44781,8 @@ An online magazine featuring music, literature, arts, and design content. @item @uref{http://kids.msfc.nasa.gov, NASA KIDS} @item @uref{http://science.nasa.gov, Sience@@NASA} +@item @uref{http://www.handy.de/, handy.de} + @item @uref{http://lindev.jmc.tju.edu/qwor, Qt Widget and Object Repository} @item @uref{http://www.samba-choro.com.br, Brazilian samba site (in Portuguese)} @@ -42933,6 +44797,9 @@ tickets for this event is implemented using @strong{MySQL} and tcl/tk. More than service with millions of users.} @item @uref{http://f1.tauzero.se, Forza Motorsport} + +@item @uref{http://www.dreamhost.com/, DreamHost Web Hosting} + @end itemize @cindex services @@ -43270,16 +45137,16 @@ interface, you should fetch the @code{Data-Dumper}, @code{DBI}, and Perl @code{Data-Dumper} module. Useful with @code{DBI}/@code{DBD} support for older Perl installations. -@item @uref{http://www.mysql.com/Downloads/Contrib/DBI-1.14.tar.gz, DBI-1.14.tar.gz} +@item @uref{http://www.mysql.com/Downloads/Contrib/DBI-1.15.tar.gz, DBI-1.15.tar.gz} Perl @code{DBI} module. -@item @uref{http://www.mysql.com/Downloads/Contrib/KAMXbase1.0.tar.gz,KAMXbase1.0.tar.gz} +@item @uref{http://www.mysql.com/Downloads/Contrib/KAMXbase1.2.tar.gz,KAMXbase1.2.tar.gz} Convert between @file{.dbf} files and @strong{MySQL} tables. Perl module written by Pratap Pereira @email{pereira@@ee.eng.ohio-state.edu}, extended by Kevin A. McGrail @email{kmcgrail@@digital1.peregrinehw.com}. This converter can handle MEMO fields. -@item @uref{http://www.mysql.com/Downloads/Contrib/Msql-Mysql-modules-1.2215.tar.gz, Msql-Mysql-modules-1.2215.tar.gz} +@item @uref{http://www.mysql.com/Downloads/Contrib/Msql-Mysql-modules-1.2216.tar.gz, Msql-Mysql-modules-1.2216.tar.gz} Perl @code{DBD} module to access mSQL and @strong{MySQL} databases. @item @uref{http://www.mysql.com/Downloads/Contrib/Data-ShowTable-3.3.tar.gz, Data-ShowTable-3.3.tar.gz} @@ -43498,8 +45365,8 @@ of several databases simultaneously. By Innovative-IT Development AB. The @strong{MySQL} GUI client homepage. By Sinisa at @strong{MySQL AB}. @item @uref{http://www.mysql.com/Downloads/Contrib/mysql_navigator_0.9.0.tar.gz, MySQL navigator 0.9} -MySQL Navigator is MySQL database server GUI client program. The purpose -of MySQL Navigator is to provide a useful client interface to MySQL +MySQL Navigator is a @strong{MySQL} database server GUI client program. The purpose +of MySQL Navigator is to provide a useful client interface to @strong{MySQL} database servers, whilst supporting multiple operating systems and languages. You can currently import/export database, enter queries, get result sets, edit scripts, run scripts, add, alter, and delete users, @@ -43522,7 +45389,7 @@ You can always find the latest version @uref{http://www.trash.net/~ffischer/admin/index.html, here}. @item @uref{http://www.mysql.com/Downloads/Win32/MySQL-Maker-1.0.zip,MySQL-Maker 1.0}. -Shareware @strong{MySQL} client for Windows. It's WYSIWYG tool which allows +Shareware @strong{MySQL} client for Windows. It's a WYSIWYG tool which allows you to create, change and delete databases and tables. You can change field - structure and add, change and delete data in these tables directly without ODBC-driver. @@ -43532,9 +45399,14 @@ these tables directly without ODBC-driver. Windows GUI (binary only) to administrate a database, by David B. Mansel, @email{david@@zhadum.org}. +@item @uref{http://home.online.no/~runeberg/myqa, MyQA} +is a Linux-based query client for the @strong{MySQL} database server. MyQA +lets you enter SQL queries, execute them, and view the results, all in a +graphical user interface. The GUI is roughly similar to that of the +'Query Analyzer' client that comes with MS SQL Server. @item @uref{http://members.xoom.com/_opex_/mysqlmanager/index.html, MySQL Manager} -a graphical MySQL server manager for MySQL server written in Java, for Windows +a graphical @strong{MySQL} server manager for @strong{MySQL} server written in Java, for Windows @item @uref{http://www.mysql.com/Downloads/Win32/netadmin.zip, netadmin.zip} @@ -43569,6 +45441,11 @@ Some features: @itemize @bullet @item Manage servers, databases, tables, columns, indexes, and users @item Import wizard to import structure and data from MS Access, MS Excel, Dbase, FoxPro, Paradox, and ODBC Databases. + +@item @uref{http://www.mysql.com/Downloads/Contrib/KMYENG113.zip,KMYENG113.zip} +An administrator GUI for @strong{MySQL}. Works only on windows, no source. +Available in English and Japanese. By Mitunobu Kaneko. +Home page: @uref{http://sql.jnts.ne.jp/} @end itemize @item @uref{http://www.mysql.com/Downloads/Contrib/xmysqladmin-1.0.tar.gz, xmysqladmin-1.0.tar.gz} @@ -43610,6 +45487,24 @@ data either by clicking on the table folder or by composing their own SQL statements with our built-in SQL editor. The tool has been tested with Oracle 8 and @strong{MySQL} as the back-end databases. It requires JDK 1.3 from JavaSoft. +@item @uref{http://www.jetools.com/products/databrowser/, DataBrowser} +The DataBrowser is a cross-database, cross-platform data access tool. It is more +user friendly than tools like SQL Plus, psql (command line based tools). It is more +flexible than TOAD, ISQL, PGAccess which are GUI's that are limitied to a single +platform or database. +@item @uref{http://www.intrex.net/amit/software/, SQLC} +The SQL Console is a standalone java application that allows you to connect to a + SQL database system and issue SQL queries and updates. It has an easy-to use +graphical user interface. The SQL Console uses JDBC to connect to the database +systems and, therefore, with proper JDBC drivers, you can use this utility to +connect to some of the most popular database systems. +@item @uref{http://www.mysql.com/Downloads/Contrib/mysql_mmc.zip, MySQL MMC} +MySQL MMC is a GUI Management Tool developed using kdevelop +with a very good interface completely like Microsoft +Enterprise Tool (for SQL Server) or Sybase Central. We +can use it to manage server, database, table, index, +users and to edit table data in grid or execute Sql +by Query Analysis. @end itemize @cindex Web clients @@ -43659,7 +45554,7 @@ html templates. By Alex Krohn. This cgi scripts in Perl enables you to edit content of Mysql database. By Tomas Zeman. @item -@uref{http://futurerealm.com/opensource/futuresql.htm, FutureSQL Web Database Administration Tool}. +@uref{http://worldcommunity.com/opensource/futuresql, FutureSQL Web Database Administration Tool}. FutureSQL by Peter F. Brown, is a free, open source rapid application development Web database administration tool, written in Perl, using @strong{MySQL}. It uses @code{DBI:DBD} and @code{CGI.pm}. @@ -43682,7 +45577,7 @@ and run update queries. Originally written to implement a simple fast low-overhead banner-rotation system. By Sasha Pachev. @item @uref{http://htcheck.sourceforge.net, htCheck} - URL checker with -MySQL backend. Spidered URLs can later be queried using SQL to retrieve +@strong{MySQL} backend. Spidered URLs can later be queried using SQL to retrieve various kinds of information, eg. broken links. Written by Gabriele Bartolini. @item @uref{http://www.odbsoft.com/cook/sources.htm} @@ -43839,7 +45734,7 @@ detection of @code{TIMESTAMP} fields), provides warnings and suggestions while converting, quotes @strong{all} special characters in text and binary data, and so on. It will also convert to @code{mSQL} v1 and v2, and is free of charge for anyone. See -@uref{http://www.cynergi.net/prod/exportsql/} for the latest version. By +@uref{http://www.cynergi.net/exportsql/} for the latest version. By Pedro Freire, @email{support@@cynergi.net}. NOTE: Doesn't work with Access2! @item @uref{http://www.mysql.com/Downloads/Contrib/access_to_mysql.txt, access_to_mysql.txt} @@ -43873,9 +45768,16 @@ table for a different site you are working on, but the table is just a bit different (that is - fields in different order, etc.). By Steve Shreeve. @item @uref{http://www.mysql.com/Downloads/Contrib/oracledump, oracledump} -Perl program to convert Oracle databases to @strong{MySQL}. By Johan Andersson. +Perl program to convert Oracle databases to @strong{MySQL}. Has same +output format as mysqldump. By Johan Andersson. + @item @uref{http://www.mysql.com/Downloads/Contrib/excel2mysql, excel2mysql} Perl program to import Excel spreadsheets into a @strong{MySQL} database. By Stephen Hurd @email{shurd@@sk.sympatico.ca} + +@item @uref{http://www.mysql.com/Downloads/Contrib/T2S_100.ZIP, T2S_100.ZIP}. +Windows program to convert text files to @strong{MySQL} databases. By +Asaf Azulay. + @end itemize @appendixsec Using MySQL with Other Products @@ -43911,6 +45813,10 @@ Patches for @code{radiusd} to make it support @strong{MySQL}. By Wim Bonis, @appendixsec Useful Tools @itemize @bullet +@item @uref{http://worldcommunity.com/opensource/utilities/mysql_backup.html, MySQL Backup}. + +A backup script for MySQL. By Peter F. Brown. + @item @uref{http://www.mysql.com/Downloads/Contrib/mytop, mytop} @item @uref{http://public.yahoo.com/~jzawodn/mytop/, mytop home page} mytop is a Perl program that allows you to monitor @strong{MySQL} servers by @@ -44362,6 +46268,8 @@ Slovak error messages. Romanian error messages. @item Peter Feher Hungarian error messages. +@item Roberto M. Serqueira +Portugise error messages. @item David Sacerdote @email{davids@@secnet.com} Ideas for secure checking of DNS hostnames. @item Wei-Jou Chen @email{jou@@nematic.ieo.nctu.edu.tw} @@ -44376,7 +46284,7 @@ Active mailing list member. Ported (and extended) the benchmark suite to @code{DBI}/@code{DBD}. Have been of great help with @code{crash-me} and running benchmarks. Some new date functions. The mysql_setpermissions script. -@item Jay Flaherty @email{fty@@utk.edu} +@item Jay Flaherty @email{fty@@mediapulse.com} Big parts of the Perl @code{DBI}/@code{DBD} section in the manual. @item Paul Southworth @email{pauls@@etext.org}, Ray Loyzaga @email{yar@@cs.su.oz.au} Proof-reading of the Reference Manual. @@ -44522,11 +46430,30 @@ Our TODO section contains what we plan to have in 4.0. @xref{TODO MySQL 4.0}. Added @code{ALTER TABLE table_name DISABLE KEYS} and @code{ALTER TABLE table_name ENABLE KEYS} commands. @item -Added @code{HANDLER} command. +@code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave. +@item +Renamed @code{safe_mysqld} to @code{mysqld_safe}. +@item +Allow one to use @code{IN} instead of @code{FROM} in @code{SHOW} commands. +@item +@code{SHOW INDEXES} is now a synonym for @code{SHOW INDEX}. @item -Added @code{SQL_CALC_FOUND_ROWS} and @code{FOUND_ROWS()}. This make it -possible to know how many rows a query would have returned if one hadn't -used @code{LIMIT}. +Added support for symbolic links to @code{MyISAM} tables. Symlink handling is +now enabled by default for Windows. +@item +@code{LOAD DATA FROM MASTER} "auto-magically" sets up a slave. +@item +A new @code{HANDLER} interface to @code{MyISAM} tables. +@item +@code{COUNT(DISTINCT)} is about 30% faster. +@item +Creating full text indexes are now much faster. +@item +Searching on packed (@code{CHAR}/@code{VARCHAR}) keys are now much faster. +@item +Added @code{SQL_CALC_FOUND_ROWS} and @code{FOUND_ROWS()}. This makes it +possible to know how many rows a query would have returned +without a @code{LIMIT} clause. @item Changed output format of @code{SHOW OPEN TABLES}. @item @@ -44584,6 +46511,7 @@ users use this code as the rest of the code and because of this we are not yet 100% confident in this code. @menu +* News-3.23.40:: Changes in release 3.23.40 * News-3.23.39:: Changes in release 3.23.39 * News-3.23.38:: Changes in release 3.23.38 * News-3.23.37:: Changes in release 3.23.37 @@ -44627,11 +46555,64 @@ not yet 100% confident in this code. * News-3.23.0:: Changes in release 3.23.0 @end menu -@node News-3.23.39, News-3.23.38, News-3.23.x, News-3.23.x +@node News-3.23.40, News-3.23.39, News-3.23.x, News-3.23.x +@appendixsubsec Changes in release 3.23.40 +@itemize @bullet +@item +Added option @code{--warnings} to @code{mysqld}. Now @code{mysqld} +only prints the error @code{Aborted connection} if this option is used. +@item +Fixed parser to allow floats of type @code{1.0e1} (no sign after @code{e}). +@item +Option @code{--force} to @code{myisamchk} now also updates states. +@item +Added option @code{--warnings} to @code{mysqld}. Now @code{mysqld} +only prints the error @code{Aborted connection} if this option is used. +@item +Fixed problem with @code{SHOW CREATE TABLE} when you didn't have a +@code{PRIMARY KEY}. +@item +Fixed properly the rename of @code{innodb_unix_file_flush_method} to +@code{innodb_flush_method}. +@item +Fixed bug when converting @code{UNSIGNED BIGINT} to @code{DOUBLE}. This caused +a problem when doing comparisons with @code{BIGINT}'s outside of the +signed range. +@item +Fixed bug in @code{BDB} tables when querying empty tables. +@item +Fixed a bug when using @code{COUNT(DISTINCT)} with @code{LEFT JOIN} and +there wasn't any matching rows. +@end itemize + +@node News-3.23.39, News-3.23.38, News-3.23.40, News-3.23.x @appendixsubsec Changes in release 3.23.39 @itemize @bullet @item -Fixed that date-part extract functions works with dates where day +The @code{AUTO_INCREMENT} sequence wasn't reset when dropping +and adding an @code{AUTO_INCREMENT} column. +@item +@code{CREATE ... SELECT} now creates non-unique indexes delayed. +@item +Fixed problem where @code{LOCK TABLES table_name READ} followed by +@code{FLUSH TABLES} put an exclusive lock on the table. +@item +@code{REAL} @@variables with was represented with 2 digits when +converted to strings. +@item +Fixed problem that client 'hung' when @code{LOAD TABLE FROM MASTER} failed. +@item +Running @code{myisamchk --fast --force} will no longer repair tables +that only had the open count wrong. +@item +Added functions to handle symbolic links to make life easier in 4.0. +@item +We are now using the @code{-lcma} thread library on HP-UX 10.20 so +that @strong{MySQL} will be more stable on HP-UX. +@item +Fixed problem with @code{IF()} and number of decimals in the result. +@item +Fixed date-part extraction functions to work with dates where day and/or month is 0. @item Extended argument length in option files from 256 to 512 chars. @@ -44639,7 +46620,7 @@ Extended argument length in option files from 256 to 512 chars. Fixed problem with shutdown when @code{INSERT DELAYED} was waiting for a @code{LOCK TABLE}. @item -Fixed coredump bug buged in InnoDB when tablespace was full. +Fixed coredump bug in InnoDB when tablespace was full. @item Fixed problem with @code{MERGE} tables and big tables (> 4G) when using @code{ORDER BY}. @@ -45052,7 +47033,7 @@ Fixed problem when using @code{DECIMAL()} keys on negative numbers. always returned @code{NULL}. @item Fixed security bug in something (please upgrade if you are using a earlier -MySQL 3.23 version). +@strong{MySQL} 3.23 version). @item Fixed buffer overflow bug when writing a certain error message. @item @@ -45221,7 +47202,7 @@ slave server restart. @item @code{SHOW KEYS} now shows whether or not key is @code{FULLTEXT}. @item -New script @file{mysqld_multi}. @xref{mysqld_multi}. +New script @file{mysqld_multi}. @xref{mysqld_multi, , @code{mysqld_multi}}. @item Added new script, @file{mysql-multi.server.sh}. Thanks to Tim Bunce @email{Tim.Bunce@@ig.co.uk} for modifying @file{mysql.server} to @@ -45274,8 +47255,8 @@ read by @code{mysql_options()}. Added new options @code{--pager[=...]}, @code{--no-pager}, @code{--tee=...} and @code{--no-tee} to the @code{mysql} client. The new corresponding interactive commands are @code{pager}, @code{nopager}, -@code{tee} and @code{notee}. @xref{mysql}, @code{mysql --help} and the -interactive help for more information. +@code{tee} and @code{notee}. @xref{mysql, , @code{mysql}}, @code{mysql --help} +and the interactive help for more information. @item Fixed crash when automatic repair of @code{MyISAM} table failed. @item @@ -45861,7 +47842,7 @@ Added table locks to Berkeley DB. Fixed a bug with @code{LEFT JOIN} and @code{ORDER BY} where the first table had only one matching row. @item -Added 4 sample @code{my.cfg} example files in the @file{support-files} +Added 4 sample @code{my.cnf} example files in the @file{support-files} directory. @item Fixed @code{duplicated key} problem when doing big @code{GROUP BY}'s. @@ -49875,6 +51856,10 @@ Everything in this list is approximately in the order it will be done. If you want to affect the priority order, please register a license or support us and tell us what you want to have done more quickly. @xref{Licensing and Support}. +The plan is that we in the future will support the full ANSI SQL99 +standard, but with a lot of useful extensions. The challenge is to do +this without sacrifying the speed or compromise the code. + @node TODO MySQL 4.0, TODO future, TODO, TODO @appendixsec Things that should be in 4.0 @@ -49964,7 +51949,7 @@ of @code{analyze} is run on all sub tables. @end itemize @node TODO future, TODO sometime, TODO MySQL 4.0, TODO -@appendixsec Things that must done in the real near future +@appendixsec Things that must be done in the real near future @itemize @bullet @item |
