InnoDB Storage EngineInnoDB Overview
InnoDB provides MySQL with a transaction-safe (ACID compliant)
storage engine with commit, rollback, and crash recovery capabilities.
InnoDB does locking on the row level and also provides an Oracle-style
consistent
non-locking read in SELECT statements. These features increase
multi-user concurrency and performance. There is no need for
lock escalation in InnoDB
because row-level locks in InnoDB fit in very little space.
InnoDB also supports FOREIGN KEY constraints.
In SQL queries you can freely mix InnoDB type tables with other
table types of MySQL, even within the same query.
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.
Fully integrated with MySQL Server, the InnoDB storage engine maintains
its own buffer pool for caching data and indexes in main memory.
InnoDB stores its tables and indexes in a tablespace, which
may consist of several files (or raw disk partitions).
This is different from, for example,
MyISAM tables where each table is stored using separate files.
InnoDB tables can be of any size even on operating
systems where file size is limited to 2GB.
InnoDB is included in binary distributions by default as of MySQL 4.0.
For information about InnoDB support in MySQL 3.23, see
section 15.3 InnoDB in MySQL 3.23. Starting from MySQL 4.1.5, the new
Windows installer makes InnoDB the MySQL default table type on
Windows.
InnoDB is used in production at numerous
large database sites requiring high performance.
The famous Internet news site Slashdot.org runs on InnoDB.
Mytrix, Inc. stores over 1TB of data in InnoDB,
and another site handles an average
load of 800 inserts/updates per second in InnoDB.
InnoDB is published under the same GNU GPL License Version 2
(of June 1991) as MySQL. For more information on MySQL licensing,
see http://www.mysql.com/company/legal/licensing/.
InnoDB Contact Information
Contact information for Innobase Oy, producer of the InnoDB engine:
Web site: http://www.innodb.com/
Email: sales@innodb.com
Phone: +358-9-6969 3250 (office)
+358-40-5617367 (mobile)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
InnoDB in MySQL 3.23
Beginning with MySQL 4.0, InnoDB is enabled by default, so
the following information applies only to MySQL 3.23.
InnoDB tables are included in the MySQL source distribution
starting from 3.23.34a and are activated in the MySQL-Max
binaries of the 3.23 series.
For Windows, the MySQL-Max binaries are included in the
standard distribution.
If you have downloaded a binary version of MySQL that includes support for
InnoDB, simply follow the instructions of the MySQL manual for
installing a binary version of MySQL. If you already have MySQL 3.23
installed, the simplest way to install MySQL-Max is to replace the
executable mysqld server with the corresponding executable from the
MySQL-Max distribution. MySQL and MySQL-Max differ only in the server
executable.
See section 2.7 Installing MySQL on Other Unix-Like Systems and
section 5.1.2 The mysqld-max Extended MySQL Server.
To compile the MySQL source code with InnoDB support,
download MySQL 3.23.34a or newer from
http://www.mysql.com/
and configure MySQL with the
--with-innodb option.
See section 2.8 MySQL Installation Using a Source Distribution.
To use InnoDB tables with MySQL 3.23, you must specify configuration
parameters in the [mysqld] section of the `my.cnf' option file.
On Windows, you can use `my.ini' instead. If you do not configure
InnoDB in the option file, InnoDB will not start. (From MySQL
4.0 on, InnoDB uses default parameters if you do not specify any.
However, to get best performance, it is still recommended that
you use
parameters appropriate for your system, as discussed in
section 15.4 InnoDB Configuration.)
In MySQL 3.23, you must specify at the minimum an innodb_data_file_path
value to configure the InnoDB data files. For example, to configure
InnoDB to use a single 500MB data file, place the
following setting in the [mysqld] section of your option file:
[mysqld] innodb_data_file_path=ibdata1:500M
InnoDB will create the `ibdata1' file in the MySQL data directory
by default. To specify the location explicitly, specify an
innodb_data_home_dir setting.
See section 15.4 InnoDB Configuration.
InnoDB Configuration
To enable InnoDB tables in MySQL 3.23, see
section 15.3 InnoDB in MySQL 3.23.
From MySQL 4.0 on, the InnoDB storage engine is enabled
by default. If you don't want to use InnoDB tables, you can
add the skip-innodb option to your MySQL option file.
Two important disk-based resources managed by the InnoDB storage
engine are its tablespace data files and its log files.
If you specify no InnoDB configuration options, MySQL 4.0 and
above creates an auto-extending 10MB data file named `ibdata1' and
two 5MB log files named `ib_logfile0' and `ib_logfile1' in the MySQL data directory.
(In MySQL 4.0.0 and 4.0.1, the data file is 64MB and not auto-extending.)
In MySQL 3.23, InnoDB will not start if you provide no configuration
options.
Note: To get good performance, you should explicitly provide
InnoDB parameters as discussed in the following examples. Naturally,
you should edit the settings to suit your hardware and requirements.
To set up the InnoDB tablespace files, use the
innodb_data_file_path option in the [mysqld] section of the
`my.cnf' option file. On Windows, you can use `my.ini' instead.
The value of innodb_data_file_path should be a list of one or more
data file specifications. If you name more than one data file, separate them
by semicolon (`;') characters:
innodb_data_file_path=datafile_spec1[;datafile_spec2]...
For example, a setting that explicitly creates a tablespace having the same characteristics as the MySQL 4.0 default is as follows:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend
This setting configures a single 10MB data file named `ibdata1' that is auto-extending. No location for the file is given, so the default is the MySQL data directory.
Sizes are specified using M or G suffix letters to indicate
units of MB or GB.
A tablespace containing a fixed-size 50MB data file named `ibdata1' and
a 50MB auto-extending file named ibdata2 in the data directory can be
configured like this:
[mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The full syntax for a data file specification includes the filename, its size, and several optional attributes:
file_name:file_size[:autoextend[:max:max_file_size]]
The autoextend attribute and those following can be used only for the
last data file in the innodb_data_file_path line. autoextend
is available starting from MySQL 3.23.50 and 4.0.2.
If you specify the autoextend option for the last data file,
InnoDB extends the data file if it runs out of free space in the
tablespace. The increment is 8MB at a time.
If the disk becomes full, you might want to add another data file on
another disk. Instructions for reconfiguring an existing tablespace are
given in section 15.8 Adding and Removing InnoDB Data and Log Files.
InnoDB is not aware of the maximum file size, so
be cautious on filesystems where the maximum file size is 2GB.
To specify a maximum size for an auto-extending data file, use the max
attribute. The following configuration allows `ibdata1' to grow up to a
limit of 500MB:
[mysqld] innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB creates tablespace files in the MySQL data directory by default.
To specify a location explicitly, use the innodb_data_home_dir option.
For example, to use two files named `ibdata1' and `ibdata2' but
create them in the `/ibdata' directory, configure InnoDB like this:
[mysqld] innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Note: InnoDB does not create directories, so make sure that
the `/ibdata' directory exists before you start the server. This is
also true of any log file directories that you configure. Use the Unix or
DOS mkdir command to create any necessary directories.
InnoDB forms the directory path for each data file by textually
concatenating the value of innodb_data_home_dir to the data file name,
adding a slash or backslash between if needed. If the
innodb_data_home_dir option is not mentioned in `my.cnf' at all,
the default value is the ``dot'' directory `./', which means the MySQL
data directory.
If you specify innodb_data_home_dir as an empty string, you can
specify absolute paths for the data files listed in the
innodb_data_file_path value. The following example is equivalent to
the preceding one:
[mysqld] innodb_data_home_dir = innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
A simple `my.cnf' example.
Suppose that you have a computer
with 128MB RAM and one hard disk. The following example shows
possible configuration parameters in `my.cnf' or
`my.ini' for InnoDB. The example assumes the use of
MySQL-Max 3.23.50 or later or MySQL 4.0.2 or later because it uses the
autoextend attribute.
This example suits most users, both on Unix and Windows,
who do not want to distribute InnoDB data files and
log files on several disks. It creates an
auto-extending data file `ibdata1' and two InnoDB log files
`ib_logfile0' and `ib_logfile1' in the
MySQL data directory. Also, the small archived InnoDB log file
`ib_arch_log_0000000000' that InnoDB creates automatically ends
up in the data directory.
[mysqld] # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1
Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files or log files.
Note that data files must be less than 2GB in some filesystems. The combined size of the log files must be less than 4GB. The combined size of data files must be at least 10MB.
When you create an InnoDB tablespace for the first time, it is
best that you start the MySQL server from the command prompt.
InnoDB will then print the information about the database
creation to the screen, so you can see what is happening.
For example, on Windows, if mysqld-max is located in
`C:\mysql\bin', you can start it like this:
C:\> C:\mysql\bin\mysqld-max --console
If you do not send server output to the screen, check the server's error log
to see what InnoDB prints during the startup process.
See section 15.6 Creating the InnoDB Tablespace for an example of what the
information displayed by InnoDB should look like.
Where to specify options on Windows? The rules for option files on Windows are as follows:
WINDIR directory; for
example, `C:\WINDOWS' or `C:\WINNT'. You can use the SET
command at the command prompt in a console window to print the value of
WINDIR:
C:\> SET WINDIR windir=C:\WINNT
Where to specify options on Unix?
On Unix, mysqld reads options from the following files, if they exist,
in the following order:
--defaults-extra-file option.
DATADIR represents the MySQL data directory that was
specified as a configure option when mysqld
was compiled
(typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation).
If you want to make sure that mysqld reads options only from a
specific file, you can use the --defaults-option as the first option
on the command line when starting the server:
mysqld --defaults-file=your_path_to_my_cnf
An advanced `my.cnf' example.
Suppose that you have a Linux computer
with 2GB RAM and three 60GB hard disks
(at directory paths `/', `/dr2' and
`/dr3'). The following example shows possible
configuration parameters in `my.cnf' for InnoDB.
[mysqld] # You can write your other MySQL server options here # ... innodb_data_home_dir = # # Data files must be able to hold your data and indexes innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend # # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # # innodb_log_arch_dir must be the same as innodb_log_group_home_dir # (starting from 4.0.6, you can omit it) innodb_log_arch_dir = /dr3/iblogs set-variable = innodb_log_files_in_group=2 # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=250M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 # # Uncomment the next lines if you want to use them #innodb_flush_method=fdatasync #set-variable = innodb_thread_concurrency=5
Note that the example places the two data files on different disks.
InnoDB will fill the tablespace beginning with the first data file.
In some cases, it will
improve the performance of the database if all data is not placed
on the same physical disk. Putting log files on a different disk from
data is very often beneficial for performance.
You can also use raw disk partitions (raw devices)
as InnoDB data files, which may speed up I/O. See section 15.15.2 Using Raw Devices for the Tablespace.
Warning: On 32-bit GNU/Linux x86, you must be careful not to set
memory usage too high. glibc may allow the process heap to grow over
thread stacks, which will crash your server. It is a risk if the value of
the following expression is close to or exceeds 2GB:
innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Each thread will use a stack (often 2MB, but only 256KB in MySQL AB binaries)
and in the worst case also uses sort_buffer_size + read_buffer_size
additional memory.
Starting from MySQL 4.1, by compiling MySQL yourself,
you can use up to 64GB of physical memory in 32-bit
Windows. See the description for innodb_buffer_pool_awe_mem_mb in
section 15.5 InnoDB Startup Options.
How to tune other mysqld server parameters?
The following values are typical and suit most users:
[mysqld] skip-external-locking set-variable = max_connections=200 set-variable = read_buffer_size=1M set-variable = sort_buffer_size=1M # # Set key_buffer to 5 - 50% of your RAM depending on how much # you use MyISAM tables, but keep key_buffer_size + InnoDB # buffer pool size < 80% of your RAM set-variable = key_buffer_size=...
InnoDB Startup Options
This section describes the InnoDB-related server options. In MySQL
4.0 and up, all of them can be specified in --opt_name=value form on
the command line or in option files. Before MySQL 4.0, numeric options
should be specified using --set-variable=opt_name=value or -O
opt_name=value syntax.
innodb_additional_mem_pool_size
InnoDB uses to store data dictionary
information and other internal data structures. The more tables you have in
your application, the more memory you will need to allocate here. If
InnoDB runs out of memory in this pool, it will start to allocate
memory from the operating system, and write warning messages to the MySQL
error log. The default value is 1MB.
innodb_autoextend_increment
innodb_buffer_pool_awe_mem_mb
InnoDB buffer pool into the
AWE physical memory using this parameter. The maximum possible value for this
is 64000. If this parameter is specified, innodb_buffer_pool_size
is the window in the 32-bit address space of mysqld where InnoDB
maps that AWE memory. A good value for innodb_buffer_pool_size
is 500MB.
innodb_buffer_pool_size
InnoDB uses to cache data and indexes
of its tables. The larger you set this value, the less disk I/O is needed
to access data in tables. On a dedicated database server, you may set this
to up to 80% of the machine physical memory size. However, do not set it
too large because competition for the physical memory might cause paging
in the operating system.
innodb_data_file_path
innodb_data_home_dir
to each path specified here. The file sizes are specified in megabytes
or gigabytes (1024MB) by appending M or G to the size
value. The sum of the sizes of the files must be at least 10MB. On some
operating systems, files must be less than 2GB. If you do not specify
innodb_data_file_path, the default behavior starting from 4.0 is to
create a single 10MB auto-extending data file named `ibdata1'. Starting
from 3.23.44, you can set the file size bigger than 4GB on those operating
systems that support big files. You can also use raw disk partitions as
data files. See section 15.15.2 Using Raw Devices for the Tablespace.
innodb_data_home_dir
InnoDB data files. If
you do not set this value, the default is the MySQL data directory. You can
specify this also as an empty string, in which case you can use absolute
file paths in innodb_data_file_path.
innodb_fast_shutdown
InnoDB does a full purge and an insert buffer merge
before a shutdown. These operations can take minutes, or even hours in extreme
cases. If you set this parameter to 1, InnoDB
skips these operations at shutdown. This option is available starting from
MySQL 3.23.44 and 4.0.1. Its default value is 1 starting from 3.23.50.
innodb_file_io_threads
InnoDB. Normally this should be
left at the default value of 4, but disk I/O on Windows may benefit from
a larger number. On Unix, increasing the number has no effect; InnoDB
always uses the default value.
This option is available as of MySQL 3.23.37.
innodb_file_per_table
innodb_file_per_table
in `my.cnf'! If you shut down mysqld, then records may
disappear from the secondary indexes of a table.
See (Bug #7496) for more information and workarounds. This is fixed in
4.1.9.
This option causes InnoDB to create each new table
using its own `.ibd' file for storing data and indexes, rather than in
the shared tablespace. See section 15.7.6 Using Per-Table Tablespaces.
This option is available as of MySQL 4.1.1.
innodb_locks_unsafe_for_binlog
InnoDB searches
and index scans. Default value for this option is false.
Normally InnoDB uses an algorithm called ``next-key locking.''
InnoDB does the row-level locking in such a way that when it
searches or scans an index of a table, it sets shared or exclusive
locks on the index records it encounters. Thus the row-level locks
are actually index record locks. The locks InnoDB sets on index
records also affect the ``gap'' before that index record.
If a user has a shared or exclusive lock on record R in an index,
another user cannot insert a new index record immediately before
R in the index order. This option causes InnoDB not to
use next-key locking in searches or index scans. Next-key locking is
still used to ensure foreign key constraints and duplicate key checking.
Note that using this option may cause phantom problems: Suppose that you
want to read and lock all children from the child table with an
identifier value larger than 100, with the intent of updating
some column in the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;Suppose that there is an index on the
id column. The query will scan
that index starting from the first record where id is bigger than 100.
Now, if the locks set on the index records do not lock out inserts
made in the gaps, a new row will meanwhile be inserted to the table.
If you now execute the same SELECT within the same transaction,
you will see a new row in the result set returned by the query.
This also means, that if new items are added to the database,
InnoDB does not guarantee serializability instead conflict
serializability is still guaranteed. Therefore, if this option is
used InnoDB guarantees at most isolation level READ COMMITTED.
This option is available as of MySQL 4.1.4.
Starting from MySQL 5.0.2 this option is even more unsafe. InnoDB in
an UPDATE or a DELETE only locks rows that it updates or
deletes. This greatly reduces the probability of deadlocks but they
can happen. Note that this option still does not allow
e.g. UPDATE to overtake other UPDATE even the case
when both updates different rows. Consider following example:
CREATE TABLE A(A INT NOT NULL, B INT); INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;Now if one connection executes a query:
SET AUTOCOMMIT = 0; UPDATE A SET B = 5 WHERE B = 3;and the other connection executes after the first one a query:
SET AUTOCOMMIT = 0; UPDATE A SET B = 4 WHERE B = 2;Then query two has to wait for a commit or rollback of query one, because query one has an exclusive lock to a row (2,3), and query two while scanning rows also tries to take an exclusive lock to the row (2,3) which it cannot have. This is because query two first takes an exclusive lock to a row and then checks does this row belong to the result set and if not then releases the unnecessary lock when option
innodb_locks_unsafe_for_binlog is used.
Therefore, query one is executed as follows:
x-lock(1,2) unlock(1,2) x-lock(2,3) update(2,3) to (2,5) x-lock(3,2) unlock(3,2) x-lock(4,3) update(4,3) to (4,5) x-lock(5,2) unlock(5,2)and then query two is executed as follows:
x-lock(1,2) update(1,2) to (1,4) x-lock(2,3) - wait for query one to commit or rollback
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit is set to 0, once per second
the log buffer is written out to the log file, and the flush to disk
operation is performed on the log file, but nothing is done at a transaction
commit. When this value is 1 (the default), at each transaction commit
the log buffer is written out to the log file, and the flush to disk
operation is performed on the log file. When set to 2, at each commit
the log buffer is written out to the file, but the flush to disk operation
is not performed on it. However, the flushing on the log file
takes place once per second also in the case of 2.
We must note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
You can achieve better performance by setting the value different from
1, but then you can lose at most one second worth of transactions in
a crash. If you set the value to 0, then any mysqld process
crash can erase the last second of transactions. If you set the value
to 2, then only an operating system crash or a power outage can erase
the last second of transactions.
Note that many operating systems and some disk hardware fool in the
flush-to-disk operation. They may tell to mysqld that the flush
has taken place, though it has not. Then the durability of transactions
is not guaranteed even with the setting 1, and in the worst case a
power outage can even corrupt the InnoDB database. Using a battery-backed
disk cache in the SCSI disk controller or in the disk itself speeds up
file flushes, and makes the operation safer. You can also try
using the Unix command hdparm to disable the caching of disk writes
in hardware caches, or use some other command specific to the hardware
vendor.
The default value of this option is 1 (prior to MySQL 4.0.13, the default is 0).
innodb_flush_method
fdatasync,
InnoDB uses fsync() to flush both the data and log
files. If set to O_DSYNC, InnoDB uses O_SYNC
to open and flush the log files, but uses fsync() to flush the
data files. If O_DIRECT is specified (available on some GNU/Linux
versions starting from MySQL 4.0.14), InnoDB uses O_DIRECT
to open the data files, and uses fsync() to flush both the data
and log files. Note that InnoDB does not use fdatasync or
O_DSYNC by default because there have been problems with them on
many Unix flavors. This option is available as of MySQL 3.23.40.
innodb_force_recovery
InnoDB prevents a user from modifying
data when this option is greater than 0. This option is available starting
from MySQL 3.23.44.
innodb_lock_wait_timeout
InnoDB transaction may wait for a lock
before being rolled back. InnoDB automatically detects transaction
deadlocks in its own lock table and rolls back the transaction. Beginning
with MySQL 4.0.20 and 4.1.2, InnoDB notices locks set using the LOCK
TABLES statement. Before that, if you
use the LOCK TABLES statement, or other transaction-safe storage
engines than InnoDB in the same transaction, a deadlock may arise
that InnoDB cannot notice. In cases like this, the timeout is useful
to resolve the situation. The default is 50 seconds.
innodb_log_arch_dir
innodb_log_group_home_dir. Starting from MySQL 4.0.6, you may omit
this option.
innodb_log_archive
InnoDB log files. The default for this option is 0.
innodb_log_buffer_size
InnoDB uses to write to the log files
on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large
log buffer allows large transactions to run without a need to write the log
to disk before the transactions commit. Thus, if you have big transactions,
making the log buffer larger will save disk I/O.
innodb_log_file_size
innodb_log_files_in_group
InnoDB writes to the
files in a circular fashion. The default is 2 (recommended).
innodb_log_group_home_dir
InnoDB log files. It must have the same
value as innodb_log_arch_dir. If you do not specify any InnoDB
log parameters, the default is to create two 5MB files names `ib_logfile0'
and `ib_logfile1' in the MySQL data directory.
innodb_max_dirty_pages_pct
InnoDB tries to flush pages from the buffer pool so that
at most this many percent of pages may not yet flushed been flushed at
any particular time. Available starting from 4.0.13 and 4.1.1. If you have the
SUPER privilege, this percentage can be changed while the server is
running:
SET GLOBAL innodb_max_dirty_pages_pct = value;
innodb_max_purge_lag
INSERT, UPDATE
and DELETE operations when the purge operations are lagging.
The default value of this parameter is zero, meaning that there will
not be any delays. When the value is greater than zero, InnoDB
may delay new row operations, as described in section 15.13 Implementation of Multi-Versioning.
This option can be changed at runtime as a global system variable.
innodb_max_purge_lag is available as of MySQL 4.0.22 and 4.1.6.
innodb_mirrored_log_groups
innodb_open_files
InnoDB. It specifies the maximum number of `.ibd' files that
InnoDB can keep open at one time. The minimum value is 10. The
default is 300. This option is available as of MySQL 4.1.1.
The file descriptors used for `.ibd' files are for InnoDB only.
They are independent of those specified by the --open-files-limit
server option, and do not affect the operation of the table cache.
innodb_thread_concurrency
InnoDB tries to keep the number of operating system threads
concurrently inside InnoDB less than or equal to the limit given
by this parameter. The default value is 8. If you have low performance
and SHOW INNODB STATUS reveals many threads waiting for semaphores,
you may have thread thrashing and should try setting this parameter lower
or higher. If you have a computer with many processors and disks, you
can try setting the value higher to better utilize the resources of you
computer. A recommended value is the sum of the number of processors and
disks your system has. A value of 500 or greater disables the concurrency
checking. This option is available starting from MySQL 3.23.44 and 4.0.1.
innodb_table_locks
InnoDB honors LOCK TABLES;
MySQL will not return from LOCK TABLE .. WRITE until all other
threads have released all their locks to the table. In MySQL 4.0.19
and before, InnoDB ignored table locks, which allowed one to more easily
simulate transactions with a combination of MyISAM and InnoDB tables.
The default value is 1, which means that
LOCK TABLES causes also InnoDB internally to take a table lock.
In applications using AUTOCOMMIT=1, InnoDB's
internal table locks can cause deadlocks. You can set
innodb_table_locks=0 in `my.cnf' to remove that problem.
innodb_status_file
InnoDB to create a file
`<datadir>/innodb_status.<pid>' for periodical
SHOW INNODB STATUS output. This option is available as of MySQL 4.0.21.
InnoDB Tablespace
Suppose that you have installed MySQL and have edited your option file so
that it contains the necessary InnoDB configuration parameters.
Before starting MySQL, you should verify that the directories you have
specified for InnoDB data files and log files exist and that the
MySQL server has access rights to those directories. InnoDB cannot
create directories, only files. Check also that you have enough disk space
for the data and log files.
It is best to run the MySQL server mysqld from the command prompt
when you create an InnoDB database, not from the mysqld_safe
wrapper or as a Windows service. When you run from a command prompt you
see what mysqld prints and what is happening. On Unix, just invoke
mysqld. On Windows, use the --console option.
When you start the MySQL server after initially configuring InnoDB in
your option file, InnoDB creates your
data files and log files. InnoDB will print something like the following:
InnoDB: The first specified datafile /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: datafile /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: 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: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created InnoDB: Started mysqld: ready for connections
A new InnoDB database has now been created. You can connect to the MySQL
server with the usual MySQL client programs like mysql.
When you shut down the MySQL server with mysqladmin shutdown,
the output will be like the following:
010321 18:33:34 mysqld: Normal shutdown 010321 18:33:34 mysqld: Shutdown Complete InnoDB: Starting shutdown... InnoDB: Shutdown completed
You can now look at the data file and log directories and you
will see the files created. The log directory will also contain
a small file named `ib_arch_log_0000000000'. That file
resulted from the database creation, after which InnoDB switched off
log archiving.
When MySQL is started again, the data files and log files will already have
been created, so the output will be much briefer:
InnoDB: Started mysqld: ready for connections
Starting from MySQL 4.1.1, you can add the option innodb_file_per_table
to `my.cnf', and make InnoDB to store each table into its own
`.ibd' file in a database directory of MySQL.
See section 15.7.6 Using Per-Table Tablespaces.
InnoDB Initialization Problems
If InnoDB prints an operating system error in a file operation,
usually the problem is one of the following:
InnoDB data file directory
or the InnoDB log directory.
mysqld does not have access rights to create files in those
directories.
mysqld does not read the proper `my.cnf' or `my.ini'
option file, and consequently does not see the options you specified.
innodb_data_home_dir
or innodb_data_file_path.
If something goes wrong when InnoDB attempts to initialize
its tablespace or its log files, you should delete all files created
by InnoDB. This means all `ibdata' files and all
`ib_logfile's. In case you already created some InnoDB tables,
delete the corresponding `.frm' files for these tables (and any
`.ibd' files if you are using multiple tablespaces) from the
MySQL database directories as well. Then you can try the InnoDB
database creation again. It is best to start the MySQL server from a
command prompt so that you see what is happening.
InnoDB Tables
Suppose that you have started the MySQL client with the command mysql test.
To create an InnoDB table, you must specify and ENGINE = InnoDB
or TYPE = InnoDB option in the table creation SQL statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB; CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
The SQL statement creates a table and an index on column a in the
InnoDB tablespace that consists of the data files you specified in
`my.cnf'. In addition, MySQL creates a file `customers.frm'
in the `test' directory under the MySQL database directory.
Internally, InnoDB adds to its own data dictionary an entry for
table 'test/customers'. This means you can create a table of the same
name customers in some other database, and the table names will
not collide inside InnoDB.
You can query the amount of free space in the InnoDB tablespace
by issuing a SHOW TABLE STATUS statement for any InnoDB table.
The amount of free
space in the tablespace appears in the Comment section in the
output of SHOW TABLE STATUS. An example:
SHOW TABLE STATUS FROM test LIKE 'customers'
Note that the statistics SHOW gives about InnoDB tables
are only approximate. They are used in SQL optimization. Table and index
reserved sizes in bytes are accurate, though.
InnoDB with Different APIs
By default, each client that connects to the MySQL server begins with autocommit
mode enabled, which automatically commits every SQL statement you run.
To use multiple-statement transactions, you can switch autocommit off
with the SQL statement SET AUTOCOMMIT = 0 and use COMMIT
and ROLLBACK to commit or roll back your transaction.
If you want to leave autocommit on, you can enclose your transactions
between START TRANSACTION and COMMIT or ROLLBACK.
Before MySQL 4.0.11, you have to use the keyword BEGIN
instead of START TRANSACTION. The following example shows two
transactions. The first is committed and the second is rolled back.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
In APIs like PHP, Perl DBI/DBD, JDBC, ODBC, or the standard C call
interface of MySQL, you can send transaction control statements such as
COMMIT to the MySQL server as strings just like any other
SQL statements such as SELECT or INSERT.
Some APIs also offer separate special transaction commit and rollback functions
or methods.
MyISAM Tables to InnoDB
Important: You should not convert MySQL system tables in the mysql
database (such as user or host) to the InnoDB type. The
system tables must always be of the MyISAM type.
If you want all your (non-system) tables to be created as InnoDB
tables, you can, starting from the MySQL 3.23.43, add the line
default-table-type=innodb to the [mysqld] section of
your `my.cnf' or `my.ini' file.
InnoDB does not have a special optimization for separate index
creation the way the MyISAM storage engine does. Therefore, it does
not pay to export and import the table and create indexes afterward.
The fastest way to alter a table to InnoDB is to do the inserts
directly to an InnoDB table. That is, use ALTER TABLE
... TYPE=INNODB, or create an empty InnoDB table with identical
definitions and insert the rows with INSERT INTO ... SELECT *
FROM ....
If you have UNIQUE constraints on secondary keys, starting from
MySQL 3.23.52, you can speed up a table import by turning off the uniqueness
checks temporarily during the import session: SET UNIQUE_CHECKS=0;
For big tables, this saves a lot of disk I/O because InnoDB can then
use its insert buffer to write secondary index records in a batch.
To get better control over the insertion process, it might be good to insert big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something AND yourkey <= somethingelse;
After all records have been inserted, you can rename the tables.
During the conversion of big tables, you should increase the size of the
InnoDB buffer pool to reduce disk I/O. Do not use more than 80% of the
physical memory, though. You can also increase the sizes of the InnoDB
log files and the log files.
Make sure that you do not fill up the tablespace: InnoDB tables require
a lot more disk space than MyISAM tables. If an ALTER TABLE
runs out of space, it will start a rollback, and that can take hours if
it is disk-bound. For 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 may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see section 15.9.1 Forcing Recovery.
AUTO_INCREMENT Column Works in InnoDB
If you specify an AUTO_INCREMENT column for a table, the InnoDB
table handle in the data dictionary will contain a special counter called
the auto-increment counter that is used in assigning new values for the
column. The auto-increment counter is stored only in main memory, not
on disk.
InnoDB uses the following algorithm to initialize the auto-increment
counter for a table T that contains an AUTO_INCREMENT column
named ai_col: After a server startup, when a user first does an insert
to a table T, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM T FOR UPDATE;
The value retrieved by the statement is incremented by one and assigned to
the column and the auto-increment counter of the table. If the table is
empty, the value 1 is assigned. If the auto-increment counter is not
initialized and the user invokes a SHOW TABLE STATUS statement that
displays output for the table T, the counter is initialized (but not
incremented) and stored for use by later inserts.
Note that in this initialization
we do a normal exclusive-locking read on the table and the lock lasts to the end
of the transaction.
InnoDB follows the same procedure for initializing the auto-increment
counter for a freshly created table.
Note that if the user specifies NULL or 0 for the
AUTO_INCREMENT column in an INSERT, InnoDB treats the
row as if the value had not been specified and generates a new value for it.
After the auto-increment counter has been initialized, if a user inserts
a row that explicitly specifies the column value, and the value is
bigger than the current counter value, the counter is set to the specified
column value. If the user does not explicitly specify a value, InnoDB
increments the counter by one and assigns the new value to the column.
When accessing the auto-increment counter, InnoDB uses a special table
level AUTO-INC lock that it keeps to the end of the current SQL
statement, not to the end of the transaction.
The special lock release strategy was introduced to improve concurrency
for inserts into a table containing an AUTO_INCREMENT column. Two
transactions cannot have the AUTO-INC lock on the same table
simultaneously.
Note that you may see gaps in the sequence of values assigned to the
AUTO_INCREMENT column if you roll back transactions that have gotten
numbers from the counter.
The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.
FOREIGN KEY Constraints
Starting from MySQL 3.23.44, InnoDB features foreign key constraints.
The syntax of a foreign key constraint definition in InnoDB looks like
this:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
Both tables must be InnoDB type. In the referencing table, there must
be an index where the foreign key columns are listed as the first
columns in the same order. In the referenced table, there must be an index
where the referenced columns are listed as the first columns in
the same order. Index prefixes on foreign key columns are not supported.
InnoDB needs indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. Starting with
MySQL 4.1.2, these indexes are created automatically. In older versions,
the indexes must be created explicitly or the creation of foreign key
constraints will fail.
Corresponding columns in the foreign key and the referenced key must
have similar internal data types inside InnoDB so that they can be
compared without a type conversion.
The size and the signedness of integer types has to be the same.
The length of string types need not be the same.
If you specify a SET NULL action, make sure that you
have not declared the columns in the child table as
NOT NULL.
If MySQL reports an error number 1005 from a CREATE TABLE statement,
and the error message string refers to errno 150, this means that the table
creation failed because a foreign key constraint was not correctly formed.
Similarly, if an ALTER TABLE fails and it refers to errno 150,
that means a foreign key definition would be incorrectly formed for the
altered table. Starting from MySQL 4.0.13, you can use SHOW INNODB
STATUS to display a detailed explanation of the latest InnoDB
foreign key error in the server.
Starting from MySQL 3.23.50, InnoDB does not check foreign key
constraints on those foreign key or referenced key values
that contain a NULL column.
A deviation from SQL standards: If in the parent table
there are several rows that have the same referenced key value,
then InnoDB acts in foreign key checks as if the other parent
rows with the same key value do not exist. For example,
if you have defined a RESTRICT type constraint, and there
is a child row with several parent rows, InnoDB does not allow
the deletion of any of those parent rows.
Starting from MySQL 3.23.50, you can also associate the
ON DELETE CASCADE or ON DELETE SET NULL clause with
the foreign key constraint. Corresponding ON UPDATE options
are available starting from 4.0.8. If ON DELETE CASCADE is
specified, and a row in the parent table is deleted, InnoDB
automatically deletes also all those rows in the child table
whose foreign key values are equal to the referenced key value in
the parent row. If ON DELETE SET NULL is specified, the
child rows are automatically updated so that the columns in the
foreign key are set to the SQL NULL value.
SET DEFAULT is parsed but ignored.
InnoDB performs cascading operations through a depth-first algorithm,
based on records in the indexes corresponding to the foreign key
constraints.
A deviation from SQL standards: If
ON UPDATE CASCADE or ON UPDATE SET NULL recurses to
update the same table it has already updated during the cascade,
it acts like RESTRICT. This means that you cannot use
self-referential ON UPDATE CASCADE or
ON UPDATE SET NULL operations.
This is to prevent infinite loops resulting from cascaded updates.
A self-referential ON DELETE SET NULL, on the other hand,
is possible from 4.0.13.
A self-referential ON DELETE CASCADE has been possible since ON
DELETE was implemented. Since 4.0.21, cascading operations may not be nested
more than 15 levels.
A deviation from SQL standards:
Like MySQL in general, in an SQL statement that inserts, deletes, or
updates many rows, InnoDB checks UNIQUE and FOREIGN KEY
constraints row-by-row. According to the SQL standard, the default behavior
should be that constraints are only checked after the WHOLE SQL statement
has been processed.
A simple example that relates parent and child tables through a
single-column foreign key:
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
A more complex example in which a product_order table has foreign keys
for two other tables. One foreign key references a two-column index in the
product table. The other references a single-column index in the
customer table:
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
Starting from MySQL 3.23.50, InnoDB allows you to add a new
foreign key constraint to a table by using ALTER TABLE:
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
Remember to create the required indexes first.
You can also add a self-referential foreign key constraint to a
table using ALTER TABLE.
Starting from MySQL 4.0.13, InnoDB supports the use of ALTER
TABLE to drop foreign keys:
ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
If the FOREIGN KEY clause included a CONSTRAINT name when you
created the foreign key, you can refer to that name to drop the foreign key.
(A constraint name can be given as of MySQL 4.0.18.) Otherwise, the
fk_symbol value is internally generated by InnoDB when the
foreign key is created. To find out the symbol when you want to drop a foreign
key, use the SHOW CREATE TABLE statement.
An example:
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
Starting from MySQL 3.23.50, the InnoDB parser allows you to use
backticks around table and column names in a
FOREIGN KEY ... REFERENCES ... clause.
Starting from MySQL 4.0.5, the InnoDB parser also takes into account
the lower_case_table_names system variable setting.
Before MySQL 3.23.50, ALTER TABLE or CREATE INDEX
should not be used in connection with tables that have foreign
key constraints or that are referenced in foreign key constraints:
Any ALTER TABLE removes all foreign key
constraints defined for the table. You should not use
ALTER TABLE with the referenced table, either. Instead,
use DROP TABLE and CREATE TABLE to modify the
schema. When MySQL does an ALTER TABLE it may internally
use RENAME TABLE, and that will confuse the
foreign key constraints that refer to the table.
In MySQL, a CREATE INDEX statement is processed as an
ALTER TABLE, so the same considerations apply.
Starting from MySQL 3.23.50, InnoDB returns the foreign key
definitions of a table as part of the output of the SHOW CREATE TABLE
statement:
SHOW CREATE TABLE tbl_name;
From this version, mysqldump also produces correct definitions
of tables to the dump file, and does not forget about the foreign keys.
You can display the foreign key constraints for a table like this:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'
The foreign key constraints are listed in the Comment column of
the output.
When performing foreign key checks, InnoDB sets shared row
level locks on child or parent records it has to look at.
InnoDB checks foreign key constraints immediately; the check
is not deferred to transaction commit.
To make it easier to reload dump files for tables that have foreign key
relationships, mysqldump automatically includes a statement in the
dump output to set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1. This
avoids problems with tables having to be reloaded in a particular order
when the dump is reloaded. For earlier versions, you can disable the
variable manually within mysql when loading the dump file like this:
mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> SOURCE dump_file_name mysql> SET FOREIGN_KEY_CHECKS = 1;
This allows you to import the tables in any order if the dump file contains
tables that are not correctly ordered for foreign keys. It also speeds
up the import operation. FOREIGN_KEY_CHECKS is available starting
from MySQL 3.23.52 and 4.0.3.
Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring
foreign key constraints during LOAD DATA operations.
InnoDB does not allow you to drop a table that is
referenced by a FOREIGN KEY constraint, unless
you do SET FOREIGN_KEY_CHECKS=0.
When you drop a table, the constraints that
were defined in its create statement are also dropped.
If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string.
InnoDB and MySQL Replication
MySQL replication works for InnoDB tables as it does
for MyISAM tables. It is also possible to use replication in
a way where the table type on the slave is not the same as the original
table type on the master. For example, you can replicate modifications to
an InnoDB table on the master to a MyISAM table on the slave.
To set up a new slave for a master, you have to make a copy of the
InnoDB tablespace and the log files, as well as the `.frm' files
of the InnoDB tables, and move the copies to the slave.
For the proper procedure to do this, see section 15.10 Moving an InnoDB Database to Another Machine.
If you can shut down the master or an existing slave, you can take a
cold backup of the InnoDB tablespace and log files and use that to
set up a slave.
To make a new slave without taking down any server you can also use
the non-free (commercial) InnoDB Hot Backup tool.
There are minor limitations in InnoDB replication:
LOAD TABLE FROM MASTER does not work for InnoDB type
tables. There are workarounds: 1) dump the table on the master and import the
dump file into the slave, or 2) use ALTER TABLE tbl_name TYPE=MyISAM
on the master before setting up replication with LOAD TABLE tbl_name
FROM MASTER, and then use ALTER TABLE to alter the master table
back to the InnoDB type afterward.
SLAVE STOP did not
respect the boundary of a multiple-statement transaction.
An incomplete transaction would be rolled back, and the next
SLAVE START would only execute the remaining part of the half
transaction. That would cause replication to fail.
SLAVE STOP.
SET FOREIGN_KEY_CHECKS=0 statement does not work properly.
Most of these limitations can be eliminated by using more recent server versions for which the limitations do not apply.
Transactions that fail on the master do not affect replication at all. MySQL replication is based on the binary log where MySQL writes SQL statements that modify data. A slave reads the binary log of the master and executes the same SQL statements. However, statements that occur within a transaction are not written to the binary log until the transaction commits, at which point all statements in the transaction are written at once. If a statement fails, for example, because of a foreign key violation, or if a transaction is rolled back, no SQL statements are written to the binary log, and the transaction is not executed on the slave at all.
NOTE: CRITICAL BUG in 4.1 if you specify innodb_file_per_table
in `my.cnf'! If you shut down mysqld, then records may
disappear from the secondary indexes of a table. See (Bug #7496) for
more information and workarounds. This is fixed in 4.1.9.
Starting from MySQL 4.1.1, you can store each InnoDB table and its
indexes into its own file. This feature is called ``multiple tablespaces''
because in effect each table has its own tablespace.
If you need to downgrade to 4.0, you have to take table dumps and
re-create the whole InnoDB tablespace. If you have not created new
InnoDB tables under MySQL 4.1.1 or later, and need to downgrade
quickly, you can also do a direct downgrade to the MySQL
4.0.18 or later in the 4.0 series.
Before doing the direct downgrade to 4.0.x, you have to end all client
connections to the mysqld server that is to be downgraded, and let it
run the purge and insert buffer merge operations to completion, so that
SHOW INNODB STATUS shows the main thread in the state
waiting for server activity. Then you can shut down
mysqld and start 4.0.18 or later in the 4.0 series.
You can enable multiple tablespaces by adding a line to the
[mysqld] section of `my.cnf':
[mysqld] innodb_file_per_table
After restarting the server, InnoDB will store each newly created
table into its own file `tbl_name.ibd' in the database directory
where the table belongs. This is similar to what the MyISAM
storage engine does, but MyISAM divides the table into a data
file `tbl_name.MYD' and the index file `tbl_name.MYI'.
For InnoDB, the data and the indexes are stored together in the
`.ibd' file. The `tbl_name.frm' file is still created as usual.
If you remove the innodb_file_per_table line from `my.cnf' and
restart the server, InnoDB creates tables inside the shared tablespace
files again.
innodb_file_per_table affects only table creation. If you start the
server with this option, new tables are created using `.ibd' files, but you can still access tables that exist in the shared
tablespace. If you remove the option, new tables are created in the shared
tablespace, but you can still access any tables that
were created using multiple tablespaces.
InnoDB always needs the shared tablespace. The `.ibd'
files are not sufficient for InnoDB to operate. The shared tablespace
consists of the familiar `ibdata' files where InnoDB puts its
internal data dictionary and undo logs.
You cannot freely move `.ibd' files around between database
directories the way you can with
MyISAM table files. This is because the table definition is stored
in the InnoDB shared tablespace, and also because InnoDB
must preserve the consistency of transaction IDs and log sequence numbers.
Within a given MySQL installation, you can move an `.ibd' file and the
associated table from one database to another with the familiar RENAME
TABLE statement:
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
If you have a ``clean'' backup of an `.ibd' file, you can restore it to the MySQL installation from which it originated as follows:
ALTER TABLE statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;Caution: This deletes the current `.ibd' file.
ALTER TABLE statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;
In this context, a ``clean'' `.ibd' file backup means:
mysqld has flushed all modified pages of the `.ibd' file from
the buffer pool to the file.
You can make such a clean backup `.ibd' file with the following method:
mysqld server and commit all transactions.
SHOW INNODB STATUS shows that there are no active
transactions in the database, and the main thread status of InnoDB is
Waiting for server activity. Then you can make a copy of the
`.ibd' file.
Another method for making a clean copy of an `.ibd' file is to
use the commercial InnoDB Hot Backup tool:
InnoDB Hot Backup to back up the InnoDB installation.
mysqld server on the backup and let it clean up
the `.ibd' files in the backup.
It is in the TODO to also allow moving clean `.ibd' files to another MySQL installation. This requires resetting of transaction IDs and log sequence numbers in the `.ibd' file.
InnoDB Data and Log Files
This section describes what you can do when your InnoDB tablespace runs
out of room or when you want to change the size of the log files.
From MySQL 3.23.50 and 4.0.2, the easiest way to increase the size of the
InnoDB tablespace is to configure it from the beginning to be
auto-extending. Specify the autoextend attribute for the last
data file in the tablespace definition. Then InnoDB will increase the
size of that file automatically in 8MB increments when it runs out of space.
Starting with MySQL 4.1.5, the increment size can be
configured with the option innodb_autoextend_increment, in megabytes.
The default value is 8.
Alternatively, you can increase the size of your tablespace by adding another
data file. To do this, you have to shut down the MySQL server,
edit the `my.cnf' file to add a new data file to the end of
innodb_data_file_path, and start the server again.
If your last data file already was defined with the keyword autoextend,
the procedure to edit `my.cnf' must take into account the size to
which the last data file has grown. You have to
look at the size of the data file, round the size downward to the
closest multiple of 1024 * 1024 bytes (= 1MB), and specify the rounded
size explicitly in innodb_data_file_path. Then you can add
another data file. Remember that only the last data file in the
innodb_data_file_path can be specified as auto-extending.
As an example, assume that the tablespace has just one auto-extending data file `ibdata1':
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this data file, over time, has grown to 988MB. Below is the configuration line after adding another auto-extending data file.
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new file to the tablespace, make sure that it does not
exist. InnoDB will create and initialize it when you restart
the server.
Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:
mysqldump to dump all your InnoDB tables.
If you want to change the number or the size of your InnoDB log
files, you have to stop the MySQL server and make sure that it shuts down
without errors. Then copy the old log files into a safe place just in case
something went wrong in the shutdown and you will need them to recover the
tablespace. Delete the old log files from the log file directory, edit
`my.cnf' to change the log file configuration, and start the MySQL
server again. mysqld will see that the no log files exist at startup
and tell you that it is creating new ones.
InnoDB DatabaseThe key to safe database management is taking regular backups.
InnoDB Hot Backup is an online backup tool you can use to backup
your InnoDB database while it is running. InnoDB Hot Backup does not
require you to shut down your database and it does not set any locks or
disturb your normal database processing. InnoDB Hot Backup
is a non-free (commercial) additional tool whose annual license fee is
390 euros per computer where the MySQL server is run.
See the InnoDB Hot Backup home page
for detailed information and screenshots.
If you are able to shut down your MySQL server, you can make
a ``binary'' backup that consists of all files used by InnoDB to manage
its tables. Use the following procedure:
InnoDB tables to a safe place.
Replication works with InnoDB type tables, so you can use MySQL
replication capabilities to keep a copy of your database at database sites
requiring high availability.
In addition to taking binary backups as just described,
you should also regularly take dumps of your tables with
mysqldump. The reason for this is that a binary file
might be corrupted without you noticing it. Dumped tables are stored
into text files that are human-readable, so spotting table corruption
becomes easier. Also, since the format is simpler, the chance for
serious data corruption is smaller.
mysqldump also has a --single-transaction option that
you can use to take a consistent snapshot without locking out other
clients.
To be able to recover your InnoDB database to the present from the
binary backup described above, you have to run your MySQL server
with binary logging turned on. Then you can apply the binary log to the
backup database to achieve point-in-time recovery:
mysqlbinlog yourhostname-bin.123 | mysql
To recover from a crash of your MySQL server process, the only thing
you have to do is to restart it. InnoDB will automatically check the
logs and perform a roll-forward of the database to the present.
InnoDB will automatically roll back uncommitted transactions that were
present at the time of the crash. During recovery, mysqld will
display output something like this:
InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 13674004 InnoDB: Doing recovery: scanned up to log sequence number 0 13739520 InnoDB: Doing recovery: scanned up to log sequence number 0 13805056 InnoDB: Doing recovery: scanned up to log sequence number 0 13870592 InnoDB: Doing recovery: scanned up to log sequence number 0 13936128 ... InnoDB: Doing recovery: scanned up to log sequence number 0 20555264 InnoDB: Doing recovery: scanned up to log sequence number 0 20620800 InnoDB: Doing recovery: scanned up to log sequence number 0 20664692 InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745 InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections
If your database gets corrupted or your disk fails, you have to do the recovery from a backup. In the case of corruption, you should first find a backup that is not corrupted. After restoring the base backup, do the recovery from the binary log files.
In some cases of database corruption it is enough just to dump, drop,
and re-create one or a few corrupt tables. You can use the
CHECK TABLE SQL statement to check whether a table is corrupt, although
CHECK TABLE naturally cannot detect every possible kind of corruption.
You can use innodb_tablespace_monitor to check the integrity of
the file space management inside the tablespace files.
In some cases, apparent database page corruption is actually due to the operating system corrupting its own file cache, and the data on disk may be okay. It is best first to try restarting your computer. It may eliminate errors that appeared to be database page corruption.
If there is database page corruption, you may want to dump
your tables from the database with SELECT INTO OUTFILE,
and usually most of the data
is intact and correct. But the corruption may cause
SELECT * FROM tbl_name or InnoDB
background operations to crash or assert, or even the InnoDB
roll-forward recovery to crash. Starting from MySQL
3.23.44, there is an InnoDB variable that you can use to
force the InnoDB storage engine to start up, and you can also
prevent background operations from running, so that
you will be able to dump your tables. For example, you can add the following
line to the [mysqld] section of your option file before restarting the
server:
[mysqld] innodb_force_recovery = 4
Before MySQL 4.0, use this syntax instead:
[mysqld] set-variable = innodb_force_recovery=4
The allowable non-zero values for innodb_force_recovery follow.
A larger number includes all precautions of lower numbers. If you are
able to dump your tables with an option value of at most 4, then you are
relatively safe that only some data on corrupt individual pages
is lost. A value of 6 is more dramatic, because database pages are left
in an obsolete state, which in turn may introduce more corruption
into B-trees and other database structures.
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page; try to make
SELECT * FROM tbl_name jump over corrupt index records and pages,
which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur
in the purge operation, this prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent also insert buffer merge operations. If they would cause a
crash, better not do them; do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB will
treat even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any of these options enabled! As a safety
measure, InnoDB prevents users from doing INSERT,
UPDATE, or DELETE when innodb_force_recovery is set to
a value greater than 0.
Starting from MySQL 3.23.53 and 4.0.4, you are allowed to DROP or
CREATE a table even if forced recovery is used. If you know that a
certain table is causing a crash in rollback, you can drop it. You can use
this also to stop a runaway rollback caused by a failing mass import or
ALTER TABLE. You can kill the mysqld process and set
innodb_force_recovery to 3 to bring your database up without
the rollback. Then DROP the table that is causing the runaway
rollback.
InnoDB implements a checkpoint mechanism called a ``fuzzy
checkpoint.'' InnoDB will flush modified database pages from the buffer
pool in small batches. There is no need to flush the buffer pool
in one single batch, which would in practice stop processing
of user SQL statements for a while.
In crash recovery, InnoDB looks for a checkpoint label written
to the log files. It knows that all modifications to the database
before the label are already present in the disk image of the database.
Then InnoDB scans the log files forward from the place of the
checkpoint,
applying the logged modifications to the database.
InnoDB writes to the log files in a circular fashion.
All committed modifications that make the database pages in the buffer
pool different from the images on disk must be available in the log files
in case InnoDB has to do a recovery. This means that when InnoDB starts
to reuse a log file in the circular fashion, it has to make sure that the
database page images on disk already contain the modifications
logged in the log file InnoDB is going to reuse. In other words,
InnoDB
has to make a checkpoint and often this involves flushing of
modified database pages to disk.
The preceding description explains why making your log files very big may save disk I/O in checkpointing. It can make sense to set the total size of the log files as big as the buffer pool or even bigger. The drawback of big log files is that crash recovery can take longer because there will be more logged information to apply to the database.
InnoDB Database to Another Machine
On Windows, InnoDB internally always stores database and
table names in lowercase. To move databases in a binary format
from Unix to Windows or from Windows to Unix, you should have all table
and database names in lowercase. A convenient way to accomplish this
on Unix is to add the following line to the [mysqld] section of
your `my.cnf' before you start creating your databases and tables:
[mysqld] set-variable = lower_case_table_names=1
On Windows, lower_case_table_names is set to 1 by default.
Like MyISAM data files, InnoDB data and log files are
binary-compatible on all platforms if the floating-point number format on
the machines is the same. You can move an InnoDB database simply by
copying all the relevant files, which were listed in section 15.9 Backing Up and Recovering an InnoDB Database. If
the floating-point formats on the machines are different but you have not
used FLOAT or DOUBLE data types in your tables, then the
procedure is the same: Just copy the relevant files. If the formats are
different and your tables contain floating-point data, you have to use
mysqldump to dump your tables on one machine and then import the dump
files on the other machine.
A performance tip is to switch off autocommit mode when you import data into your database, assuming that your tablespace has enough space for the big rollback segment the big import transaction will generate. Do the commit only after importing a whole table or a segment of a table.
InnoDB Transaction Model and Locking
In the InnoDB transaction model, the goal has been to combine the
best properties of a multi-versioning database with traditional two-phase
locking. InnoDB does locking on the row level and runs queries as
non-locking consistent reads by default, in the style of Oracle. The lock
table in InnoDB is stored so space-efficiently that lock escalation
is not needed: Typically several users are allowed to lock every row in the
database, or any random subset of the rows, without InnoDB running
out of memory.
InnoDB and AUTOCOMMIT
In InnoDB, all user activity occurs inside a transaction. If the
autocommit mode is enabled, each SQL statement forms a
single transaction on its own. MySQL always starts a new connection
with autocommit enabled.
If the autocommit mode is switched off with SET AUTOCOMMIT = 0,
then we can consider that a user always has a transaction open. An
SQL COMMIT or ROLLBACK statement ends the
current transaction and a new one starts. Both statements will release
all InnoDB locks that were set during the current transaction.
A COMMIT means that the changes made in the current transaction
are made permanent and become visible to other users.
A ROLLBACK statement, on the other hand, cancels all
modifications made by the current transaction.
If the connection has autocommit enabled, the user can still
perform a multiple-statement transaction by starting it with an
explicit START TRANSACTION or BEGIN statement and ending it with
COMMIT or ROLLBACK.
InnoDB and TRANSACTION ISOLATION LEVEL
In terms of the SQL:1992 transaction isolation levels,
the InnoDB default is REPEATABLE READ.
Starting from MySQL 4.0.5, InnoDB offers all four different
transaction isolation levels described by the SQL standard.
You can set the default isolation level for all connections by using the
--transaction-isolation option on the command line or in option files.
For example, you can set the option
in the [mysqld] section of `my.cnf' like this:
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
A user can change the isolation level of a single session or
all new incoming connections with the SET TRANSACTION
statement. Its syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
Note that there are hyphens in the level names for the
--transaction-isolation option, but not for the SET
TRANSACTION statement.
The default behavior is to set the isolation level for the next (not
started) transaction. If you use the GLOBAL keyword, the statement
sets the default transaction level globally for all new connections
created from that point on (but not existing connections).
You need the SUPER privilege to do this. Using the SESSION
keyword sets the default transaction level for all future transactions
performed on the current connection.
Any client is free to change the session isolation level (even in the middle of a transaction), or the isolation level for the next transaction.
Before MySQL 3.23.50, SET TRANSACTION had no effect
on InnoDB tables. Before 4.0.5, only REPEATABLE READ
and SERIALIZABLE were available.
You can query the global and session transaction isolation levels with these statements:
SELECT @@global.tx_isolation; SELECT @@tx_isolation;
In row-level locking, InnoDB uses so-called ``next-key locking.''
That means that besides index records, InnoDB can also lock
the ``gap'' before an index record to block insertions by other users
immediately before the index record. A next-key lock refers to
a lock that locks an index record and the gap before it.
A gap lock refers to a lock that only locks a gap before some
index record.
A detailed description of each isolation level in InnoDB:
READ UNCOMMITTED
SELECT statements are performed in a non-locking fashion, but a
possible earlier version of a record might be used. Thus, using this
isolation level, such reads are not ``consistent.'' This is also called
``dirty read.'' Other than that, this isolation level works like READ
COMMITTED.
READ COMMITTED
A somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE
and SELECT ... LOCK IN SHARE MODE statements lock only the index
records, not the gaps before them, and thus allow free inserting of new
records next to locked records. UPDATE and DELETE statements
that use a unique index with a unique search condition lock only the index
record found, not the gap before it. In range-type UPDATE
and DELETE statements, InnoDB must set next-key or gap locks
and block insertions by other users to the gaps covered by the range. This
is necessary because ``phantom rows'' must be blocked for MySQL replication
and recovery to work.
Consistent reads behave as in Oracle: Each consistent read,
even within the same transaction, sets and reads its own fresh
snapshot.
See section 15.11.3 Consistent Non-Locking Read.
REPEATABLE READ
This is the default isolation level of InnoDB.
SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE,
UPDATE, and DELETE statements that use a unique index with a
unique search condition lock only the index record found, not the gap
before it.
With other search conditions, these operations employ next-key locking,
locking the index range scanned with next-key or gap locks, and block
new insertions by other users.
In consistent reads, there is an important difference from the
previous isolation level: In this level, all consistent reads within
the same transaction read the same snapshot established by the first
read. This convention means that if you issue several plain
SELECT statements within the same transaction, these
SELECT statements are consistent also with respect to each
other.
See section 15.11.3 Consistent Non-Locking Read.
SERIALIZABLE
This level is like REPEATABLE READ, but all plain
SELECT statements are implicitly converted to
SELECT ... LOCK IN SHARE MODE.
A consistent read means that InnoDB uses its multi-versioning to
present to a query a snapshot of the database at a point in time.
The query will see the changes made by exactly those transactions
that committed before that point of time, and no changes made by
later or uncommitted transactions. The exception to this rule
is that the query will see the changes made by the transaction
itself that issues the query.
If you are running with the default REPEATABLE READ isolation level,
then all consistent reads within the same transaction read the snapshot
established by the first such read in that transaction. You can get a
fresher snapshot for your queries by committing the current transaction
and after that issuing new queries.
Consistent read is the default mode in which InnoDB processes
SELECT statements in READ COMMITTED and
REPEATABLE READ isolation levels. A consistent read
does not set any locks on the tables it accesses, and
therefore other users are free to modify those tables at
the same time a consistent read is being performed on the table.
SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE
In some circumstances, a consistent read is not convenient.
For example, you might want to add a new row into your table child,
and make sure that the child already has a parent in table
parent. The following example shows how to implement referential
integrity in your application code.
Suppose that you use a consistent read to read the table parent
and indeed see the parent of the child in the table. Can you now safely
add the child row to table child? No, because it may
happen that meanwhile some other user deletes the parent row
from the table parent, without you being aware of it.
The solution is to perform the SELECT in a locking
mode using LOCK IN SHARE MODE:
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read.
A shared mode lock prevents others from updating or deleting
the row we have read. Also,
if the latest data belongs to a yet uncommitted transaction of another
client connection, we will wait until that transaction commits.
After we see that the preceding query returns
the parent 'Jones', we can safely add the child record
to the child table and commit our transaction.
Let us look at another example: We have an integer counter field in
a table child_codes that we use to assign
a unique identifier to each child added to table child.
Obviously, using a consistent read or a shared mode read
to read the present value of the counter is not a good idea, since
two users of the database may then see the same value for the
counter, and a duplicate-key error will occur if two users attempt to add
children with the same identifier to the table.
Here, LOCK IN SHARE MODE is not a good solution because if two
users read the counter at the same time, at least one of them
will end up in deadlock when attempting to update the counter.
In this case, there are two good ways to implement the reading and
incrementing of the counter:
(1) update the counter first by incrementing it by 1 and only after
that read it, or
(2) read the counter first with a lock mode FOR UPDATE, and
increment after that. The latter approach can be implemented as follows:
SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE reads the latest
available data, setting exclusive locks on each row it reads.
Thus it sets the same locks a searched SQL UPDATE would set
on the rows.
Please note that the above is merely an example of how SELECT ... FOR
UPDATE works. In MySQL, the specific task of generating a unique identifier
actually can be accomplished using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1); SELECT LAST_INSERT_ID();
The SELECT statement merely retrieves the identifier information
(specific to the current connection). It does not access any table.
In row-level locking, InnoDB uses an algorithm called ``next-key locking.''
InnoDB does the row-level locking in such a way that when it searches
or scans an index of a table, it sets shared or exclusive locks
on the index records it encounters. Thus the row-level locks are
actually index record locks.
The locks InnoDB sets on index records also affect the ``gap'' before
that index record. If a user has a shared or exclusive lock on record
R in an index, another user cannot insert a new index record
immediately before R in the index order. This locking of gaps is
done to prevent the so-called ``phantom problem.'' Suppose that you want to read and
lock all children from the child table with an identifier value
larger than 100, with the intent of updating some column in the selected
rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
Suppose that there is an index on the id column. The query will scan that
index starting from the first record where id is bigger than 100.
Now, if the locks set on the index records would not lock out inserts made
in the gaps, a new row might meanwhile be inserted to the table. If you now
execute the same SELECT within the same transaction, you would see a
new row in the result set returned by the query. This is contrary the
isolation principle of transactions: A transaction should be able to run so
that the data it has read does not change during the transaction. If we
regard a set of rows as a data item, the new ``phantom'' child would violate
this isolation principle.
When InnoDB scans an index, it can also lock the gap
after the last record in the index. Just that happens in the previous
example: The locks set by InnoDB prevent any insert to
the table where id would be bigger than 100.
You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read will prevent anyone meanwhile inserting a duplicate for your row. Thus the next-key locking allows you to ``lock'' the non-existence of something in your table.
InnoDB
Suppose that you are running in the default REPEATABLE READ isolation level.
When you issue a consistent read, that is, an ordinary SELECT
statement, InnoDB will give your transaction a timepoint according
to which your query sees the database. If another transaction deletes
a row and commits after your timepoint was assigned, you will
not see the row as having been deleted. Inserts and updates are treated
similarly.
You can advance your timepoint by committing your transaction
and then doing another SELECT.
This is called ``multi-versioned concurrency control.''
User A User B
SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
1 row in set
In this example, user A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.
If you want to see the ``freshest'' state of the database, you should
use either the READ COMMITTED isolation level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
InnoDB
A locking read, an UPDATE, or a DELETE
generally set record locks on every index record that is scanned in
the processing of the SQL query. It does not matter if there are
WHERE conditions in the query that would exclude the row
from the result set of the query. InnoDB does not remember the exact
WHERE condition, but only knows which index ranges were
scanned. The record locks are normally next-key locks that also block
inserts to the ``gap'' immediately before the record.
If the locks to be set are exclusive,
then InnoDB always retrieves also the clustered index record and sets
a lock on it.
If you do not have indexes suitable for your query and MySQL has to scan the whole table to process the query, every row of the table will become locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily need to scan many rows.
SELECT ... FROM is a consistent read, reading a snapshot of the
database and setting no locks unless the transaction isolation level is set
to SERIALIZABLE. For SERIALIZABLE level, this sets shared
next-key locks on the index records it encounters.
SELECT ... FROM ... LOCK IN SHARE MODE
sets shared next-key locks on all index records the read encounters.
SELECT ... FROM ... FOR UPDATE
sets exclusive next-key locks on all index records the read
encounters.
INSERT INTO ... VALUES (...)
sets an exclusive lock on the inserted row. Note that this lock is not
a next-key lock and does not prevent other users from inserting to the
gap before the inserted row. If a duplicate-key error occurs, a shared
lock on the duplicate index record is set.
AUTO_INCREMENT column
on a table, InnoDB sets an exclusive lock on the end of the index
associated with the AUTO_INCREMENT column.
In accessing the auto-increment counter, InnoDB uses a specific table
lock mode AUTO-INC where the lock lasts only to the end of the
current SQL statement, instead of to the end of the whole transaction.
See section 15.11.1 InnoDB and AUTOCOMMIT.
Before MySQL 3.23.50, SHOW TABLE STATUS applied to a table
with an AUTO_INCREMENT column sets an exclusive row-level lock
to the high end of the AUTO_INCREMENT index. This means
also that SHOW TABLE STATUS could cause a deadlock of transactions,
something that may surprise users. Starting from MySQL 3.23.50,
InnoDB fetches the value of a previously initialized
AUTO_INCREMENT column without setting any locks.
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive (non-next-key) lock on each row inserted into
T. It does the search on S as a consistent read, but
sets shared next-key locks on S if MySQL binary logging is turned
on. InnoDB has to set locks in the latter case: In
roll-forward recovery from a backup, every SQL statement has to be
executed in exactly the same way it was done originally.
CREATE TABLE ... SELECT ...
performs the SELECT as a consistent read or with shared locks,
as in the previous item.
REPLACE
is done like an insert if there is no collision on a unique key.
Otherwise, an exclusive next-key lock is placed on the row that has
to be updated.
UPDATE ... WHERE ...
sets an exclusive next-key lock on every record the search encounters.
DELETE FROM ... WHERE ...
sets an exclusive next-key lock on every record the search encounters.
FOREIGN KEY constraint is defined on a table, any insert,
update, or delete that requires checking of the constraint condition
sets shared record-level locks on the records it looks at to check the
constraint. InnoDB also sets these locks
in the case where the constraint fails.
LOCK TABLES sets table locks, but it is the higher MySQL layer
above the InnoDB layer that sets these locks. Beginning with MySQL
4.0.20 and 4.1.2, InnoDB is aware of table locks if
innodb_table_locks=1 and AUTOCOMMIT=0, and the MySQL
layer above InnoDB knows about row-level locks. Before that,
the automatic deadlock detection of InnoDB cannot detect deadlocks
where such table locks are involved. Also, since the higher MySQL layer
does not know about row-level locks, it is possible to get a table lock
on a table where another user currently has row-level locks. But that does
not put transaction integrity in danger.
See section 15.11.9 Deadlock Detection and Rollback.
See section 15.17 Restrictions on InnoDB Tables.
MySQL begins each client connection with autocommit mode enabled by default. When autocommit is enabled, MySQL does a commit after each SQL statement if that statement did not return an error.
If you have the autocommit mode off and close a connection without calling an explicit commit of your transaction, then MySQL will roll back your transaction.
If an SQL statement returns an error, the commit/rollback behavior depends on the error. See section 15.16 Error Handling.
The following SQL statements (and any synonyms for them) cause an implicit commit of the current transaction in MySQL:
ALTER TABLE, BEGIN, CREATE INDEX, DROP DATABASE,
DROP INDEX, DROP TABLE, LOAD MASTER DATA, LOCK
TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START
TRANSACTION, TRUNCATE, UNLOCK TABLES.
CREATE TABLE (this commits only if before MySQL 4.0.13 and MySQL
binary logging is used).
CREATE TABLE statement in InnoDB is processed as a single
transaction. This means that a ROLLBACK from the user does not
undo CREATE TABLE statements the user made during that
transaction.
InnoDB automatically detects a deadlock of transactions and rolls back a
transaction or transactions to prevent the deadlock. Starting from
MySQL 4.0.5, InnoDB tries to pick small transactions to roll
back. The size of a transaction is determined by the number of rows
it has inserted, updated, or deleted. Prior to 4.0.5, InnoDB
always rolled back the transaction whose lock request was the last
one to build a deadlock, that is, a cycle in the ``waits-for'' graph
of transactions.
Beginning with MySQL 4.0.20 and 4.1.2, InnoDB is aware of
table locks if innodb_table_locks=1 (1 is the default), and the MySQL
layer above InnoDB knows about row-level locks. Before that,
InnoDB cannot detect deadlocks where a table lock set by a MySQL
LOCK TABLES statement is involved, or if a lock set
by another storage engine than InnoDB is involved. You have to resolve
these situations by setting the value of the innodb_lock_wait_timeout
system variable.
When InnoDB performs a complete rollback of a transaction, all the
locks of the transaction are released. However, if just a single SQL
statement is rolled back as a result of an error, some of the locks
set by the SQL statement may be preserved. This is because InnoDB
stores row locks in a format such it cannot know afterward which lock was
set by which SQL statement.
Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.
InnoDB uses automatic row-level locking. You can get deadlocks
even in the case of transactions that just insert or delete a
single row. That is because these operations are not really ``atomic'';
they automatically set locks on the (possibly several) index
records of the row inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their occurrence with the following techniques:
SHOW INNODB STATUS to determine the cause of the latest deadlock.
That can help you to tune your application to avoid deadlocks. This
strategy can be used as of MySQL 3.23.52 and 4.0.3, depending on your MySQL
series.
SELECT ... FOR UPDATE
or ... LOCK IN SHARE MODE), try using a lower isolation
level such as READ COMMITTED.
EXPLAIN SELECT to determine which indexes the MySQL
server regards as the most appropriate for your queries.
SELECT to return data
from an old snapshot, do not add the clause FOR UPDATE
or LOCK IN SHARE MODE to it. Using READ COMMITTED
isolation level is good here, because each consistent read
within the same transaction reads from its own fresh snapshot.
LOCK TABLES with transactional
tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until you commit the transaction explicitly.
For example, if you need to write table t1 and read table t2,
you can do this:
SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;Table-level locks make your transactions queue nicely, and deadlocks are avoided.
InnoDB instant
deadlock detection algorithm also works in this case,
because the serializing lock is a row-level lock.
With MySQL table-level locks, the timeout method must be used to
resolve deadlocks.
AUTOCOMMIT=1 and MySQL's LOCK TABLES
command, InnoDB's internal table locks that were present from 4.0.20
to 4.0.23 can cause deadlocks. Starting from 4.0.22, you can set
innodb_table_locks=0 in `my.cnf' to fall back to the
old behavior and remove the problem.
4.0.24 does not set InnoDB table locks if AUTOCOMMIT=1.
InnoDB Performance Tuning TipsInnoDB 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 167th/second if the disk does not fool the operating system.
innodb_flush_log_at_trx_commit
to 0. InnoDB tries to flush the log once per second anyway,
although the flush is not guaranteed.
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 of big log files is that recovery
time will be longer.
VARCHAR column type instead of CHAR if you are
storing variable-length strings or if the column may contain many
NULL values. A CHAR(N) column always takes N bytes to
store data, even if the string is shorter or its value is
NULL. Smaller tables fit better in the buffer pool and reduce
disk I/O.
fsync() and other similar methods is surprisingly slow.
The default method InnoDB uses is the fsync() function.
If you are not satisfied with the database write performance, you might
try setting innodb_flush_method in `my.cnf' to
O_DSYNC, although O_DSYNC seems to be slower on most
systems.
InnoDB, make sure that MySQL does not have
autocommit mode enabled because that would require a log flush to disk for
every insert.
To disable autocommit during your import operation, surround it with SET
AUTOCOMMIT and COMMIT statements:
SET AUTOCOMMIT=0; /* SQL import statements ... */ COMMIT;If you use the
mysqldump option --opt, you will get dump
files that are fast to import into an InnoDB table, even without
wrapping them with the SET AUTOCOMMIT and COMMIT statements.
InnoDB uses the insert
buffer to save disk I/O in inserts, but no such mechanism is used in a
corresponding rollback. 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 server 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 to use a special procedure.
See section 15.9.1 Forcing Recovery.
DROP TABLE + CREATE TABLE to empty a
table, not DELETE FROM tbl_name.
INSERT syntax to reduce
communication overhead between the client and the server if you need
to insert many rows:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;This tip is valid for inserts into any table type, not just
InnoDB.
UNIQUE constraints on
secondary keys, starting from MySQL 3.23.52 and 4.0.3, you
can speed up table imports by temporarily turning off the uniqueness
checks during the import session:
SET UNIQUE_CHECKS=0;For big tables, this saves a lot of disk I/O because
InnoDB can
use its insert buffer to write secondary index records in a batch.
FOREIGN KEY constraints in your tables, starting
from MySQL 3.23.52 and 4.0.3, you can speed up table imports by turning the
foreign key checks off for a while in the import session:
SET FOREIGN_KEY_CHECKS=0;For big tables, this can save a lot of disk I/O.
[mysqld] query_cache_type = ON query_cache_size = 10MIn MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.
SHOW INNODB STATUS and the InnoDB Monitors
Starting from MySQL 3.23.42, InnoDB includes InnoDB Monitors
that print information about the InnoDB internal state. Starting from
MySQL 3.23.52 and 4.0.3, you can use the SQL statement SHOW INNODB
STATUS to fetch the output of the standard InnoDB Monitor to your
SQL client. The information is useful in performance tuning. If you are
using the mysql interactive SQL client, the output is more readable
if you replace the usual semicolon statement terminator by \G:
mysql> SHOW INNODB STATUS\G
Another way to use InnoDB Monitors is to let them continuously write
data to the standard output of the server mysqld. In this case,
no output is sent to clients. When switched on, InnoDB Monitors
print data about every 15 seconds. Server output usually is directed
to the `.err' log in the MySQL data directory. This data is useful in
performance tuning. On Windows, you must start the server from a command
prompt in a console window with the --console option if you want
to direct the output to the window rather than to the error log.
Monitor output includes information of the following types:
InnoDB thread
To cause the standard InnoDB Monitor to write to the standard
output of mysqld, use the following SQL statement:
CREATE TABLE innodb_monitor(a INT) TYPE=InnoDB;
The monitor can be stopped by issuing the following statement:
DROP TABLE innodb_monitor;
The CREATE TABLE syntax is just a way to pass a command
to the InnoDB engine through the MySQL SQL parser: The only things
that matter are the table name innodb_monitor and that it be an
InnoDB table. The structure of the
table is not relevant at all for the InnoDB Monitor. If you shut down
the server when the monitor is running, and you want to start
the monitor again, you have to drop the
table before you can issue a new CREATE TABLE
statement to start the monitor.
This syntax may change in a future release.
In a similar way, you can start innodb_lock_monitor, which is
otherwise the same as innodb_monitor but also prints a
lot of lock information. A separate innodb_tablespace_monitor
prints a list of created file segments existing in the tablespace
and also validates the tablespace allocation data structures.
Starting from 3.23.44, there is innodb_table_monitor with which
you can print the contents of the InnoDB internal data dictionary.
A sample of InnoDB Monitor output:
mysql> SHOW INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
ting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
ting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
ting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
a35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
serting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
mmitting
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
arting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
ARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
llback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
serting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to 18 1212665295
Last checkpoint at 18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size 3200
Free buffers 110
Database pages 3074
Modified db pages 2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.05 sec)
Some notes on the output:
TRANSACTIONS section reports lock waits, your
application may have lock contention. The output can also help to
trace the reasons for transaction deadlocks.
SEMAPHORES section reports threads waiting for a semaphore and
statistics on how many times threads have needed a spin or a wait on a
mutex or a rw-lock semaphore. A large number of threads waiting for
semaphores may be a result of disk I/O, or contention problems inside
InnoDB. Contention can be due to heavy parallelism of queries, or
problems in operating system thread scheduling. Setting
innodb_thread_concurrency smaller than the default value of 8 can
help in such situations.
BUFFER POOL AND MEMORY section gives you statistics on pages
read and written. You can calculate from these numbers how many data
file I/O operations your queries currently are doing.
ROW OPERATIONS section shows what the main thread is doing.
Beginning with MySQL 4.0.19, InnoDB sends diagnostic output to stderr
or files instead of stdout or fixed-size memory buffers, to avoid potential
buffer overflow errors. As a side effect, the output of SHOW INNODB
STATUS is written to a status file every fifteen seconds. The name of the
file is `innodb_status.pid', where pid is the server
process ID. This file is created in the MySQL data directory. InnoDB
removes the file for a normal shutdown. If abnormal shutdowns have occurred,
instances of these status files may be present and must be removed manually.
Before removing them, you might want to examine them to see if they contain
useful information to the cause of abnormal shutdowns. Beginning with
MySQL 4.0.21, the `innodb_status.pid' file will only be created
if the configuration option innodb_status_file=1 is set.
Because InnoDB is a multi-versioned database, it must keep information
about old versions of rows in the tablespace. This information is stored
in a data structure called a rollback segment after an analogous
data structure in Oracle.
Internally, InnoDB adds two fields to each row stored in the database.
A 6-byte field indicates the transaction identifier for the last
transaction that inserted or updated the row. Also, a deletion
is treated internally as an update where a special bit in the row
is set to mark it as deleted. Each row also contains a 7-byte
field called the roll pointer. The roll pointer points to an
undo log record written to the rollback segment. If the row was
updated, the undo log record contains the information necessary
to rebuild the content of the row before it was updated.
InnoDB uses the information in the rollback segment to perform the
undo operations needed in a transaction rollback. It also uses the
information to build earlier versions of a row for a consistent
read.
Undo logs in the rollback segment are divided into insert and update
undo logs. Insert undo logs are needed only in transaction rollback
and can be discarded as soon as the transaction commits. Update undo logs
are used also in consistent reads, and they can be discarded only after
there is no transaction present for which InnoDB has assigned
a snapshot that in a consistent read could need the information
in the update undo log to build an earlier version of a database
row.
You must remember to commit your transactions regularly, including those
transactions that only issue consistent reads. Otherwise, InnoDB
cannot discard data from the update undo logs, and the rollback segment
may grow too big, filling up your tablespace.
The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space need for your rollback segment.
In the InnoDB multi-versioning scheme, a row is not physically removed from
the database immediately when you delete it with an SQL statement.
Only when InnoDB can discard the update undo log record written for
the deletion can it also physically remove the corresponding row and
its index records from the database. This removal operation is
called a purge, and it is quite fast, usually taking the same order of
time as the SQL statement that did the deletion.
In a scenario where the user inserts and deletes rows in smallish batches at about the same rate in the table, it is possible that the purge thread will start to lag behind, and the table grows bigger and bigger, making everything disk-bound and very slow. Even if the table would carry just 10 MB of useful data, it may grow to occupy 10 GB with all the dead rows. In such a case, it would be good to throttle new row operations, and allocate more resources for the purge thread.
The InnoDB transaction system maintains a list of transactions that
have delete-marked index records by UPDATE or DELETE
operations. Let the length of this list be purge_lag.
Starting with MySQL/InnoDB-4.1.6 and 4.0.22, there is a startup option
and settable global variable innodb_max_purge_lag, which is
zero by default. When this parameter is non-zero, InnoDB may delay new
row operations. When the purge_lag exceeds
innodb_max_purge_lag, each INSERT, UPDATE and
DELETE operation will be delayed by
purge_lag/innodb_max_purge_lag*10-5 milliseconds. The
delay is computed in the beginning of a purge batch, every ten
seconds. The operations will not be delayed if purge cannot run
because of an old consistent read view that could see the rows to be
purged. A typical setting for a problematic workload might be 1
million, assuming that our transactions are small, only 100 bytes in
size, and we can allow 100 MB of unpurged rows in our tables.
MySQL stores its data dictionary information for tables in `.frm'
files in database directories. This is true for all MySQL storage engines.
But every InnoDB table
also has its own entry in InnoDB internal data dictionaries
inside the tablespace. When MySQL drops a table or a database,
it has to delete both an `.frm' file or files, and
the corresponding entries inside the InnoDB data dictionary.
This is the reason why you cannot move InnoDB tables between
databases simply by moving the `.frm' files. It is also the reason why
DROP DATABASE did not work for InnoDB type tables
before MySQL 3.23.44.
Every InnoDB table has a special index called the clustered index
where the data of the rows is stored. If you define a
PRIMARY KEY on your table, the index of the primary key
will be the clustered index.
If you do not define a PRIMARY KEY for
your table, MySQL picks the first UNIQUE index that has only
NOT NULL columns as the primary key and
InnoDB uses it as the clustered index.
If there is no such index in the table,
InnoDB internally generates a clustered index
where the rows are ordered by the row ID that InnoDB assigns
to the rows in such a table. The row ID is a 6-byte field that
increases monotonically as new rows are inserted. Thus the rows
ordered by the row ID will be physically in the insertion order.
Accessing a row through the clustered index is fast because the row data will be on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution. (In many databases, the data is traditionally stored on a different page from the index record.)
In InnoDB, the records in non-clustered indexes (also called secondary
indexes) contain the primary key value for the row. InnoDB uses
this primary key value to search for the row from the clustered index. Note
that if the primary key is long, the secondary indexes use more space.
InnoDB compares CHAR and VARCHAR strings
of different lengths such that the remaining length in the shorter
string is treated as if padded with spaces.
All indexes in InnoDB are B-trees where the index records are
stored in the leaf pages of the tree. The default size of an index
page is 16KB. When new records are inserted, InnoDB tries to
leave 1/16 of the page free for future insertions and updates
of the index records.
If index records are inserted in a sequential order (ascending or descending),
the resulting index pages will be about 15/16 full.
If records are inserted in a random order, the pages will be from
1/2 to 15/16 full. If the fillfactor of an index page drops below 1/2,
InnoDB tries to contract the index tree to free the page.
It is a common situation in a database application that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus the insertions to the clustered index do not require random reads from a disk.
On the other hand, secondary indexes are usually non-unique, and insertions
into secondary indexes happen in a relatively random order. This would
cause a lot of random disk I/O operations without a special mechanism used
in InnoDB.
If an index record should be inserted to a non-unique secondary index,
InnoDB checks whether the secondary index page is already in the buffer
pool. If that is the case, InnoDB does the insertion directly to
the index page. If the index page is not found in the buffer
pool, InnoDB inserts the record to a special insert buffer structure.
The insert buffer is kept so small that it fits entirely in the buffer
pool, and insertions can be done very fast.
Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions to the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.
If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes. InnoDB has an
automatic mechanism that monitors index searches made to the indexes
defined for a table. If InnoDB notices that queries could
benefit from building a hash index, it does so automatically.
Note that the hash index is always built based on an existing
B-tree index on the table. InnoDB can build a hash index on a prefix
of any length of the key defined for the B-tree, depending on the
pattern of searches that InnoDB observes for the B-tree index.
A hash index can be partial: It is not required that the whole
B-tree index is cached in the buffer pool. InnoDB will build
hash indexes on demand for those pages of the index that are
often accessed.
In a sense, InnoDB tailors itself through the adaptive hash index
mechanism to ample main memory, coming closer to the architecture of main
memory databases.
Records in InnoDB tables have the following characteristics:
InnoDB contains a header of six bytes. The header
is used to link consecutive records together, and also in row-level
locking.
InnoDB stores fixed-length character columns such as
CHAR(10) in a fixed-length format.
InnoDB truncates trailing spaces
from VARCHAR columns.
Note that MySQL may internally convert CHAR columns to
VARCHAR. See section 13.2.6.1 Silent Column Specification Changes.
NULL value reserves 1 or 2 bytes in the record directory.
Besides that, an SQL NULL value reserves zero bytes in the data
part of the record if stored in a variable length column.
In a fixed-length column, it reserves the fixed length of the column
in the data part of the record.
The motivation behind reserving the fixed space for NULL values
is that then an update of the column from NULL to a
non-NULL value can be done in place and does not cause
fragmentation of the index page.
InnoDB uses simulated asynchronous disk I/O: InnoDB creates a number of
threads to take care of I/O operations, such as read-ahead.
There are two read-ahead heuristics in InnoDB:
InnoDB notices that the access pattern to a
segment in the tablespace is sequential, it posts in
advance a batch of reads of database pages to the I/O system.
InnoDB notices that some area in a tablespace
seems to be in the process of being fully read into the buffer pool, it
posts the remaining reads to the I/O system.
Starting from MySQL 3.23.40b, InnoDB uses a novel file flush technique
called doublewrite.
It adds safety to crash recovery after an operating system crash
or a power outage, and improves performance on most Unix flavors
by reducing the need for fsync() operations.
Doublewrite means that before writing pages to a data file, InnoDB
first writes them to a contiguous tablespace area called the
doublewrite buffer. Only after the write and the flush to the doublewrite
buffer has completed does InnoDB write the pages to their proper
positions in the data file. If the operating system crashes in the
middle of a page write, InnoDB later will find a good
copy of the page from the doublewrite buffer during recovery.
Starting from MySQL 3.23.41, you can use raw disk partitions as tablespace data files. By using a raw disk, you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which might improve performance.
When you create a new data file, you must put the keyword newraw
immediately after the data file size in innodb_data_file_path.
The partition must be at least as large as the size that you specify.
Note that 1MB in InnoDB is 1024 * 1024 bytes, whereas 1MB usually
means 1,000,000 bytes in disk specifications.
[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
The next time you start the server, InnoDB notices the newraw
keyword and initializes the new partition. However, do not create or change
any InnoDB tables yet. Otherwise, when you next restart the server,
InnoDB will reinitialize the partition and your changes will be lost.
(Starting from 3.23.44, as a safety measure InnoDB prevents users from
modifying data when any partition with newraw is specified.)
After InnoDB has initialized the new partition, stop the server, change
newraw in the data file specification to raw:
[mysqld] innodb_data_home_dir= innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw
Then restart the server and InnoDB will allow changes to be made.
On Windows, starting from 4.1.1, you can allocate a disk partition as a data file like this:
[mysqld] innodb_data_home_dir= innodb_data_file_path=//./D::10Gnewraw
The `//./' corresponds to the Windows syntax of `\\.\' for accessing physical drives.
When you use raw disk partitions, be sure that they have permissions that allow read and write access by the account used for running the MySQL server.
The data files you define in the configuration file form the tablespace of
InnoDB. The files are simply concatenated to form the tablespace.
There is no striping in use. Currently you cannot define where in the
tablespace your tables will be allocated. However, in a newly created
tablespace, InnoDB allocates space starting from the first
data file.
The tablespace consists of database pages with a default size of 16KB.
The pages are grouped into extents of 64 consecutive pages. The ``files''
inside a tablespace are called segments in InnoDB.
The name of the ``rollback segment'' is somewhat confusing because it
actually contains many segments in the tablespace.
Two segments are allocated for each index in InnoDB. One is for
non-leaf nodes of the B-tree, the other is for the leaf nodes. The idea
here is to achieve better sequentiality for the leaf nodes, which contain
the data.
When a segment grows inside the tablespace, InnoDB allocates the
first 32 pages to it individually. After that InnoDB starts
to allocate whole extents to the segment.
InnoDB can add to a large segment up to 4 extents at a time to ensure
good sequentiality of data.
Some pages in the tablespace contain bitmaps of other pages, and
therefore a few extents in an InnoDB tablespace cannot be
allocated to segments as a whole, but only as individual pages.
When you ask for available free space in the tablespace by issuing
a SHOW TABLE STATUS, InnoDB
reports the extents that are definitely free in the tablespace.
InnoDB always reserves some extents for clean-up and other internal
purposes; these reserved extents are not included in the free space.
When you delete data from a table, InnoDB will contract the corresponding
B-tree indexes. It depends on the pattern of deletes whether that frees
individual pages or extents to the tablespace, so that the freed
space becomes available for other users. Dropping a table or deleting
all rows from it is guaranteed to release the space to other users,
but remember that deleted rows will be physically removed only in an
(automatic) purge operation after they are no longer needed in
transaction rollback or consistent read.
If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.
A symptom of fragmentation is that a table takes more space than it 'should take'. How much exactly is that, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fillfactor may vary 50 % - 100 %. Another symptom of fragmentation is that a table scan:
SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;
takes more time than 'it should take'. (Above we are fooling the SQL optimizer to scan the clustered index, not a secondary index.) Most disks can read 10 - 50 MB/s. That can be used to estimate how fast a table scan should run.
It can speed up index scans if you periodically perform a ``null''
ALTER TABLE operation:
ALTER TABLE tbl_name TYPE=InnoDB
That causes MySQL to rebuild the table. Another way to perform a
defragmention operation is to use mysqldump to dump the table to a text
file, drop the table, and reload it from the dump file.
If the insertions to an index are always ascending and records are deleted
only from the end, the InnoDB file space management algorithm
guarantees that fragmentation in the index will not occur.
Error handling in InnoDB is not always the same as specified in the
SQL standard. According to the standard, any error during an SQL statement
should cause the rollback of that statement. InnoDB sometimes rolls
back only part of the statement, or the whole transaction. The following
items describe how InnoDB performs error handling:
Table is full error
and InnoDB rolls back the SQL statement.
InnoDB
to roll back the whole transaction.
INSERT INTO ... SELECT.
This will probably change so that the SQL statement will be rolled
back if you have not specified the IGNORE option in your
statement.
InnoDB storage engine level), and
they roll back the corresponding SQL statement.
During such implicit rollbacks, as well as during the explicit ROLLBACK
SQL command, SHOW PROCESSLIST will display "Rolling back" in the
State column for the connection (starting from MySQL 4.1.8).
InnoDB Error Codes
The following is a non-exhaustive list of common
InnoDB-specific errors that you may encounter, with information
about why they occur and how to resolve the problem.
1005 (ER_CANT_CREATE_TABLE)
errno 150, table creation failed because a foreign key
constraint was not correctly formed.
1016 (ER_CANT_OPEN_FILE)
InnoDB table from the InnoDB data files though
the `.frm' file for the table exists.
See section 15.18.1 Troubleshooting InnoDB Data Dictionary Operations.
1114 (ER_RECORD_FILE_FULL)
InnoDB has run out of free space in the tablespace.
You should reconfigure the tablespace to add a new data file.
1205 (ER_LOCK_WAIT_TIMEOUT)
1213 (ER_LOCK_DEADLOCK)
1216 (ER_NO_REFERENCED_ROW)
1217 (ER_ROW_IS_REFERENCED)
To print the meaning of an operating system error number,
use the perror program that comes with the MySQL distribution.
The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.
1 (EPERM)
2 (ENOENT)
3 (ESRCH)
4 (EINTR)
5 (EIO)
6 (ENXIO)
7 (E2BIG)
8 (ENOEXEC)
9 (EBADF)
10 (ECHILD)
11 (EAGAIN)
12 (ENOMEM)
13 (EACCES)
14 (EFAULT)
15 (ENOTBLK)
16 (EBUSY)
17 (EEXIST)
18 (EXDEV)
19 (ENODEV)
20 (ENOTDIR)
21 (EISDIR)
22 (EINVAL)
23 (ENFILE)
24 (EMFILE)
25 (ENOTTY)
26 (ETXTBSY)
27 (EFBIG)
28 (ENOSPC)
29 (ESPIPE)
30 (EROFS)
31 (EMLINK)
The following table provides a list of some common Windows system error codes. For a complete list see the Microsoft website.
1 (ERROR_INVALID_FUNCTION)
2 (ERROR_FILE_NOT_FOUND)
3 (ERROR_PATH_NOT_FOUND)
4 (ERROR_TOO_MANY_OPEN_FILES)
5 (ERROR_ACCESS_DENIED)
6 (ERROR_INVALID_HANDLE)
7 (ERROR_ARENA_TRASHED)
8 (ERROR_NOT_ENOUGH_MEMORY)
9 (ERROR_INVALID_BLOCK)
10 (ERROR_BAD_ENVIRONMENT)
11 (ERROR_BAD_FORMAT)
12 (ERROR_INVALID_ACCESS)
13 (ERROR_INVALID_DATA)
14 (ERROR_OUTOFMEMORY)
15 (ERROR_INVALID_DRIVE)
16 (ERROR_CURRENT_DIRECTORY)
17 (ERROR_NOT_SAME_DEVICE)
18 (ERROR_NO_MORE_FILES)
19 (ERROR_WRITE_PROTECT)
20 (ERROR_BAD_UNIT)
21 (ERROR_NOT_READY)
22 (ERROR_BAD_COMMAND)
23 (ERROR_CRC)
24 (ERROR_BAD_LENGTH)
25 (ERROR_SEEK)
26 (ERROR_NOT_DOS_DISK)
27 (ERROR_SECTOR_NOT_FOUND)
28 (ERROR_OUT_OF_PAPER)
29 (ERROR_WRITE_FAULT)
30 (ERROR_READ_FAULT)
31 (ERROR_GEN_FAILURE)
32 (ERROR_SHARING_VIOLATION)
33 (ERROR_LOCK_VIOLATION)
34 (ERROR_WRONG_DISK)
36 (ERROR_SHARING_BUFFER_EXCEEDED)
38 (ERROR_HANDLE_EOF)
39 (ERROR_HANDLE_DISK_FULL)
112 (ERROR_DISK_FULL)
123 (ERROR_INVALID_NAME)
1450 (ERROR_NO_SYSTEM_RESOURCES)
InnoDB TablesBLOB and TEXT columns,
is slightly less than half of a database page. That is, the maximum row
length is about 8000 bytes. LONGBLOB and LONGTEXT columns
must be less than 4GB, and the total row length, including also BLOB
and TEXT columns, must be less than 4GB. InnoDB stores
the first 512 bytes of a BLOB or TEXT column in the row,
and the rest into separate pages.
InnoDB log files must be less than 4GB.
InnoDB tables do not support FULLTEXT indexes.
InnoDB tables do not support spatial column types.
ANALYZE TABLE counts cardinality by doing 10 random
dives to each of the index trees and updating index cardinality
estimates accordingly. Note that because these are only estimates,
repeated runs of ANALYZE TABLE may produce different numbers.
This makes ANALYZE TABLE fast on InnoDB tables but not 100%
accurate as it doesn't take all rows into account.
MySQL uses index cardinality estimates only in join optimization. If
some join is not optimized in the right way, you may try using
ANALYZE TABLE. In the few cases that ANALYZE TABLE doesn't
produce values good enough for your particular tables, you can use
FORCE INDEX with your queries to force the usage of a particular
index, or set max_seeks_for_key to ensure that MySQL prefers index
lookups over table scans. See section 5.2.3 Server System Variables. See section A.6 Optimizer-Related Issues.
InnoDB always stores database and table names
internally in lowercase. To move databases in binary format
from Unix to Windows or from Windows to Unix, you should have all database
and table names in lowercase.
mysql database from
MyISAM to InnoDB tables! This is an unsupported operation.
If you do this, MySQL
will not restart until you restore the old system tables from a backup
or re-generate them with the mysql_install_db script.
InnoDB does not keep an internal count of rows in a table. (This
would actually be somewhat complicated because of multi-versioning.)
To process a SELECT COUNT(*) FROM T statement, InnoDB must
scan an index of the table, which will take some time if the table
is not entirely in the buffer pool. To get a fast count, you have to
use a counter table you create yourself and let your application
update it according to the inserts and deletes it does. If your table
does not change often, using the MySQL query cache is a good
solution.
SHOW TABLE STATUS also can be used if an approximate row count is
sufficient.
See section 15.12 InnoDB Performance Tuning Tips.
AUTO_INCREMENT column, you must always define an index for
the table, and that index must contain just the AUTO_INCREMENT
column. In MyISAM tables, the AUTO_INCREMENT column may
be part of a multi-column index.
InnoDB does not support the AUTO_INCREMENT table option for
setting the initial sequence value in a
CREATE TABLE or ALTER TABLE statement.
To set the value with InnoDB, insert a dummy row with a value
one less and delete that dummy row, or insert the first row with an
explicit value specified.
InnoDB may reuse an old
value for an AUTO_INCREMENT column (that is, a value that was
assigned to an old transaction that was rolled back).
AUTO_INCREMENT column runs out of values, InnoDB
wraps a BIGINT to -9223372036854775808 and BIGINT
UNSIGNED to 1. However, BIGINT values have 64 bits, so do
note that if you were to insert one million rows per second, it would still
take nearly three hundred thousand years before BIGINT reached its upper bound.
With all other integer type columns, a duplicate-key error will result.
This is similar to how MyISAM works, because it is mostly general MySQL
behavior and not about any storage engine in particular.
DELETE FROM tbl_name does not regenerate the table but
instead deletes all rows, one by one.
TRUNCATE tbl_name is mapped to DELETE FROM tbl_name
for InnoDB and doesn't reset the AUTO_INCREMENT counter.
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.
CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;If you create a non-unique index on a prefix of a column,
InnoDB will
create an index over the whole column.
LOCK TABLES operation
does not know about InnoDB row-level locks set by already
completed SQL statements. This means that you can get a table lock on
a table even if there still exist transactions by other users who have
row level locks on the same table. Thus your operations on the table
may have to wait if they collide with these locks of other users. Also
a deadlock is possible. However, this does not endanger transaction
integrity, because the row level locks set by InnoDB will
always take care of the integrity. Also, a table lock prevents other
transactions from acquiring more row level locks (in a conflicting
lock mode) on the table.
LOCK
TABLES operation acquires two locks on each table if
innodb_table_locks=1. (1 is the default.)
In addition to a table lock on the MySQL
layer, it will also acquire an InnoDB table lock. Older
versions of MySQL do not acquire InnoDB table locks. Beginning
with MySQL 4.0.22 and 4.1.7, the old behavior can be selected by
setting innodb_table_locks=0. If no InnoDB table lock
is acquired, LOCK TABLES will complete even if some records of
the tables are being locked by other transactions.
InnoDB locks held by a transaction will be released when the
transaction is committed or aborted. Thus, it does not make much
sense to invoke LOCK TABLES on InnoDB tables in
AUTOCOMMIT=1 mode, because the acquired InnoDB table
locks would be released immediately.
LOCK TABLES in MySQL performs an
implicit COMMIT and UNLOCK TABLES. An InnoDB variant of
LOCK TABLES has been planned that can be executed in the middle
of a transaction.
LOAD TABLE FROM MASTER statement for setting up replication
slave servers does not yet work for InnoDB tables. A workaround
is to alter the table to MyISAM on the master, do then the load,
and after that alter the master table back to InnoDB.
InnoDB is 16KB. By recompiling the
code, you can set it to values ranging from 8KB to 64KB.
You have to update the values of UNIV_PAGE_SIZE and
UNIV_PAGE_SIZE_SHIFT in the `univ.i' source file.
InnoDB Troubleshootingmysqld_safe wrapper or as a Windows service.
You will then see what mysqld prints to the command prompt
window, and you have a better grasp of what is going on.
On Windows, you must start the server with the `--console'
option to direct the output to the console window.
InnoDB Monitors to obtain information about a problem.
If the problem is performance-related, or your server appears to be
hung, you should use innodb_monitor to print information
about the internal state of InnoDB. If the problem is with locks, use
innodb_lock_monitor. If the problem is in creation of tables or
other data dictionary operations, use innodb_table_monitor to
print the contents of the InnoDB internal data dictionary.
CHECK TABLE on that
table.
InnoDB Data Dictionary Operations
A specific issue with tables is that the MySQL server keeps data
dictionary information in `.frm' files it stores in the database
directories, while InnoDB also stores the information into its own
data dictionary inside the tablespace files.
If you move `.frm' files around, or use DROP DATABASE in
MySQL versions before 3.23.44, or the server crashes in the middle of a
data dictionary operation, the `.frm' files may end up
out of sync with the InnoDB internal data dictionary.
A symptom of an out-of-sync data dictionary is that a CREATE TABLE
statement fails. If this occurs, you should look in the server's error
log. If the log says that the table already exists inside the InnoDB
internal data dictionary, you have an orphaned table inside the InnoDB
tablespace files that has no corresponding `.frm' file. The error message
looks like this:
InnoDB: Error: table test/parent already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.
You can drop the orphaned table by following the instructions given in the error message.
Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open an `.InnoDB' file:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
In the error log you will find a message like this:
InnoDB: Cannot find table test/child2 from the internal data dictionary InnoDB: of InnoDB though the .frm file for the table exists. Maybe you InnoDB: have deleted and recreated InnoDB data files but have forgotten InnoDB: to delete the corresponding .frm files of InnoDB tables?
This means that there is an orphaned `.frm' file without a
corresponding table inside InnoDB. You can drop the orphaned
`.frm' file by deleting it manually.
If MySQL crashes in the middle of an ALTER TABLE operation, you
may end up with an orphaned temporary table inside the InnoDB
tablespace. With innodb_table_monitor you see a table whose
name is `#sql-...'. Starting from MySQL 4.0.6, you can perform
SQL statements also on tables whose name contains the character `#'
if you enclose the name in backticks.
Thus, you can drop such an orphaned table like any other orphaned
table with the method described above. Note that to copy or rename a file
in the Unix shell, you need to put the file name in double quotes if the
file name contains `#'.
Older MySQL versions did not allow accessing any
table with a name containing `#'. The solution in older MySQL
versions is to use a special InnoDB mechanism available starting from
MySQL 3.23.48. When you have an orphaned table `#sql-id' inside
the tablespace, you can cause InnoDB to rename it to
`rsql-id_recover_innodb_tmp_table' with the following statement:
CREATE TABLE `rsql-id_recover_innodb_tmp_table`(...) TYPE=InnoDB;
Go to the first, previous, next, last section, table of contents.