--myisam-recover=mode

Set the mode for automatic recovery of crashed MyISAM tables.

--delay-key-write=ALL

Don't flush key buffers between writes for any MyISAM table.

Note: If you do this, you should not use MyISAM tables from another program (such as from another MySQL server or with myisamchk) when the table is in use. Doing so leads to index corruption.

Using --external-locking does not help for tables that use --delay-key-write.

bulk_insert_buffer_size

The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!

myisam_max_extra_sort_file_size

Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter was given in bytes before MySQL 5.0.6, when it was removed.

myisam_max_sort_file_size

Don't use the fast sort index method to create an index if the temporary file would become larger than this. Note: In MySQL 5.0, this parameter is given in bytes.

myisam_sort_buffer_size

Set the size of the buffer used when recovering tables.

The table is checked for errors.

If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

If you use ALTER TABLE to change a MERGE table to another table type, the mapping to the underlying tables is lost. Instead, the rows from the underlying MyISAM tables are copied into the altered table, which is then assigned the new type.

REPLACE does not work.

You cannot use DROP TABLE, ALTER TABLE, DELETE FROM without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the tables that are mapped into an open MERGE table. If you do so, the MERGE table may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to issue a FLUSH TABLES statement prior to performing any of these operations to ensure that no MERGE tables remain open.

A MERGE table cannot maintain UNIQUE constraints over the whole table. When you perform an INSERT, the data goes into the first or last MyISAM table (depending on the value of the INSERT_METHOD option). MySQL ensures that unique key values remain unique within that MyISAM table, but not across all the tables in the collection.

When you create a MERGE table, there is no check to insure that the underlying tables exist and have identical structures. When the MERGE table is used, MySQL checks that the record length for all mapped tables is equal, but this is not foolproof. If you create a MERGE table from dissimilar MyISAM tables, you are very likely to run into strange problems.

The order of indexes in the MERGE table and its underlying tables should be the same. If you use ALTER TABLE to add a UNIQUE index to a table used in a MERGE table, and then use ALTER TABLE to add a non-unique index on the MERGE table, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This is because ALTER TABLE puts UNIQUE indexes before non-unique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results.

DROP TABLE on a table that is in use by a MERGE table does not work on Windows because the MERGE storage engine's table mapping is hidden from the upper layer of MySQL. Since Windows does not allow the deletion of open files, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table.

Linux 2.x Intel

Sun Solaris (SPARC and x86)

FreeBSD 4.x/5.x (x86, sparc64)

IBM AIX 4.3.x

SCO OpenServer

SCO UnixWare 7.1.x

Windows NT/2000/XP

Linux 2.x Alpha

Linux 2.x AMD64

Linux 2.x IA-64

Linux 2.x s390

Mac OS X

--bdb-home=path

The base directory for BDB tables. This should be the same directory you use for --datadir.

--bdb-lock-detect=method

The BDB lock detection method. The option value should be DEFAULT, OLDEST, RANDOM, or YOUNGEST.

--bdb-logdir=path

The BDB log file directory.

--bdb-no-recover

Do not start Berkeley DB in recover mode.

--bdb-no-sync

Don't synchronously flush the BDB logs. This option is deprecated; use --skip-sync-bdb-logs instead (see the description for --sync-bdb-logs).

--bdb-shared-data

Start Berkeley DB in multi-process mode. (Do not use DB_PRIVATE when initializing Berkeley DB.)

--bdb-tmpdir=path

The BDB temporary file directory.

--skip-bdb

Disable the BDB storage engine.

--sync-bdb-logs

Synchronously flush the BDB logs. This option is enabled by default; use --skip-sync-bdb-logs to disable it.

If you are running with autocommit enabled (which is the default), changes to BDB tables are committed immediately and cannot be rolled back.

If you are running with autocommit disabled, changes do not become permanent until you execute a COMMIT statement. Instead of committing, you can execute ROLLBACK to forget the changes.

You can start a transaction with the BEGIN WORK statement to suspend autocommit, or with SET AUTOCOMMIT=0 to disable autocommit explicitly.

In MySQL 5.0, BDB tables can have up to 31 indexes per table, 16 columns per index, and a maximum key size of 1024 bytes.

MySQL requires a PRIMARY KEY in each BDB table so that each row can be uniquely identified. If you don't create one explicitly, MySQL creates and maintains a hidden PRIMARY KEY for you. The hidden key has a length of five bytes and is incremented for each insert attempt. This key does not appear in the output of SHOW CREATE TABLE or DESCRIBE.

The PRIMARY KEY is faster than any other index, because the PRIMARY KEY is stored together with the row data. The other indexes are stored as the key data + the PRIMARY KEY, so it's important to keep the PRIMARY KEY as short as possible to save disk space and get better speed.

This behavior is similar to that of InnoDB, where shorter primary keys save space not only in the primary index but in secondary indexes as well.

If all columns you access in a BDB table are part of the same index or part of the primary key, MySQL can execute the query without having to access the actual row. In a MyISAM table, this can be done only if the columns are part of the same index.

Sequential scanning is slower than for MyISAM tables because the data in BDB tables is stored in B-trees and not in a separate data file.

Key values are not prefix- or suffix-compressed like key values in MyISAM tables. In other words, key information takes a little more space in BDB tables compared to MyISAM tables.

There are often holes in the BDB table to allow you to insert new rows in the middle of the index tree. This makes BDB tables somewhat larger than MyISAM tables.

SELECT COUNT(*) FROM tbl_name is slow for BDB tables, because no row count is maintained in the table.

The optimizer needs to know the approximate number of rows in the table. MySQL solves this by counting inserts and maintaining this in a separate segment in each BDB table. If you don't issue a lot of DELETE or ROLLBACK statements, this number should be accurate enough for the MySQL optimizer. However, MySQL stores the number only on close, so it may be incorrect if the server terminates unexpectedly. It should not be fatal even if this number is not 100% correct. You can update the row count by using ANALYZE TABLE or OPTIMIZE TABLE. See Section 13.5.2.1, “ANALYZE TABLE Syntax” and Section 13.5.2.5, “OPTIMIZE TABLE Syntax”.

Internal locking in BDB tables is done at the page level.

LOCK TABLES works on BDB tables as with other tables. If you do not use LOCK TABLES, MySQL issues an internal multiple-write lock on the table (a lock that does not block other writers) to ensure that the table is properly locked if another thread issues a table lock.

To be able to roll back transactions, the BDB storage engine maintains log files. For maximum performance, you can use the --bdb-logdir option to place the BDB logs on a different disk than the one where your databases are located.

MySQL performs a checkpoint each time a new BDB log file is started, and removes any BDB log files that are not needed for current transactions. You can also use FLUSH LOGS at any time to checkpoint the Berkeley DB tables.

For disaster recovery, you should use table backups plus MySQL's binary log. See Section 5.9.1, “Database Backups”.

Warning: If you delete old log files that are still in use, BDB is not able to do recovery at all and you may lose data if something goes wrong.

Applications must always be prepared to handle cases where any change of a BDB table may cause an automatic rollback and any read may fail with a deadlock error.

If you get a full disk with a BDB table, you get an error (probably error 28) and the transaction should roll back. This contrasts with MyISAM tables, for which mysqld waits for sufficient free disk space before continuing.

Opening many BDB tables at the same time may be quite slow. If you are going to use BDB tables, you should not have a very large table cache (for example, with a size larger than 256) and you should use the --no-auto-rehash option when you use the mysql client.

SHOW TABLE STATUS does not provide some information for BDB tables:

mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G
*************************** 1. row ***************************
           Name: bdbtest
         Engine: BerkeleyDB
        Version: 10
     Row_format: Dynamic
           Rows: 154
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:

Optimize performance.

Change to use no page locks for table scanning operations.

Each BDB table stores in the .db file the path to the file as it was created. This was done enable detection of locks in a multi-user environment that supports symlinks. As a consequence of this, it is not possible to move BDB table files from one database directory to another.

When making backups of BDB tables, you must either use mysqldump or else make a backup that includes the files for each BDB table (the .frm and .db files) as well as the BDB log files. The BDB storage engine stores unfinished transactions in its log files and requires them to be present when mysqld starts. The BDB logs are the files in the data directory with names of the form log.XXXXXXXXXX (ten digits).

If a column that allows NULL values has a unique index, only a single NULL value is allowed. This differs from other storage engines.

If the following error occurs when you start mysqld after upgrading, it means that the new BDB version doesn't support the old log file format:

bdb:  Ignoring log file: .../log.XXXXXXXXXX:
unsupported log version #

In this case, you must delete all BDB logs from your data directory (the files with names that have the format log.XXXXXXXXXX) and restart mysqld. We also recommend that you then use mysqldump --opt to dump your BDB tables, drop the tables, and restore them from the dump file.

If autocommit mode is disabled and you drop a BDB table that is referenced in another transaction, you may get error messages of the following form in your MySQL error log:

001119 23:43:56  bdb:  Missing log fileid entry
001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN:
                       1 3644744: Invalid

This is not fatal, but until the problem is fixed, we recommend that you not drop BDB tables except while autocommit mode is enabled. (The fix is not trivial.)

In the first version, the remote server must be a MySQL server. Support by FEDERATED for other database engines may be be added in the future.

The remote table that a FEDERATED table points to must exist before you try to access the table through the FEDERATED table.

It is possible for one FEDERATED table to point to another, but you must be careful not to create a loop.

There is no support for transactions.

There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database. The integrity of the data in the local table could be breached if there was any change to the remote database.

The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, and indexes. It does not support ALTER TABLE, DROP TABLE, or any other Data Definition Language statements. The current implementation does not use Prepared statements.

The implementation uses SELECT, INSERT, UPDATE, and DELETE, but not HANDLER.

FEDERATED tables do not work with the query cache.