MySQL Reference Manual for version 4.1.0-alpha.

7 MySQL Table Types

As of MySQL Version 3.23.6, you can choose between three basic table formats (ISAM, HEAP and MyISAM). Newer versions of MySQL support additional table types (InnoDB, or BDB), depending on how you compile it.

When you create a new table, you can tell MySQL what type of table to create. The default table type is usually MyISAM.

MySQL will always create a `.frm' file to hold the table and column definitions. The table's index and data will be stored in one or more other files, depending on the table type.

If you try to use a table type that is not compiled-in or activated, MySQL will instead create a table of type MyISAM. This behaviour is convenient when you want to copy tables between MySQL servers that support different table types. (Perhaps your master server supports transactional storage engines for increased safety, while the slave servers use only non-transactional storage engines for greater speed.)

This automatic change of table types can be confusing for new MySQL users. We plan to fix this by introducing warnings in the new client-server protocol in version 4.1 and generating a warning when a table type is automatically changed.

You can convert tables between different types with the ALTER TABLE statement. See section 6.5.4 ALTER TABLE Syntax.

Note that MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM).

Advantages of transaction-safe tables (TST):

• Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
• You can combine many statements and accept these all in one go with the COMMIT command.
• You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
• If an update fails, all your changes will be restored. (With NTST tables all changes that have taken place are permanent)
• Can provide better concurrency if the table gets many updates concurrently with reads.

Note that to use InnoDB tables you have to use at least the innodb_data_file_path startup option. See section 7.5.2 InnoDB Startup Options.

Advantages of not transaction-safe tables (NTST):

• Much faster as there is no transaction overhead.
• Will use less disk space as there is no overhead of transactions.
• Will use less memory to do updates.

You can combine TST and NTST tables in the same statements to get the best of both worlds.

7.1 MyISAM Tables

MyISAM is the default table type in MySQL Version 3.23. It's based on the ISAM code and has a lot of useful extensions.

The index is stored in a file with the `.MYI' (MYIndex) extension, and the data is stored in a file with the `.MYD' (MYData) extension. You can check/repair MyISAM tables with the myisamchk utility. See section 4.4.6.7 Using myisamchk for Crash Recovery. You can compress MyISAM tables with myisampack to take up much less space. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.

The following is new in MyISAM:

• There is a flag in the MyISAM file that indicates whether the table was closed correctly. If mysqld is started with --myisam-recover, MyISAM tables will automatically be checked and/or repaired on open if the table wasn't closed properly.
• You can INSERT new rows in a table that doesn't have free blocks in the middle of the datafile, at the same time other threads are reading from the table (concurrent insert). An free block can come from an update of a dynamic length row with much data to a row with less data or when deleting rows. When all free blocks are used up, all future inserts will be concurrent again.
• Support for big files (63-bit) on filesystems/operating systems that support big files.
• All data is stored with the low byte first. This makes the data machine and OS independent. The only requirement is that the machine uses two's-complement signed integers (as every machine for the last 20 years has) and IEEE floating-point format (also totally dominant among mainstream machines). The only area of machines that may not support binary compatibility are embedded systems (because they sometimes have peculiar processors). There is no big speed penalty in storing data low byte first; the bytes in a table row is normally unaligned and it doesn't take that much more power to read an unaligned byte in order than in reverse order. The actual fetch-column-value code is also not time critical compared to other code.
• All number keys are stored with high byte first to give better index compression.
• Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster (at least 10%) and old numbers will not be reused as with the old ISAM. Note that when an AUTO_INCREMENT is defined on the end of a multi-part-key the old behaviour is still present.
• When inserted in sorted order (as when you are using an AUTO_INCREMENT column) the key tree will be split so that the high node only contains one key. This will improve the space utilisation in the key tree.
• BLOB and TEXT columns can be indexed.
• NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
• Maximum key length is 500 bytes by default (can be changed by recompiling). In cases of keys longer than 250 bytes, a bigger key block size than the default of 1024 bytes is used for this key.
• Maximum number of keys/table is 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
• myisamchk will mark tables as checked if one runs it with --update-state. myisamchk --fast will only check those tables that don't have this mark.
• myisamchk -a stores statistics for key parts (and not only for whole keys as in ISAM).
• Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
• myisampack can pack BLOB and VARCHAR columns.
• You can use put the datafile and index file on different directories to get more speed (with the DATA/INDEX DIRECTORY="path" option to CREATE TABLE). See section 6.5.3 CREATE TABLE Syntax.

MyISAM also supports the following things, which MySQL will be able to use in the near future:

• Support for a true VARCHAR type; a VARCHAR column starts with a length stored in 2 bytes.
• Tables with VARCHAR may have fixed or dynamic record length.
• VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
• A hashed computed index can be used for UNIQUE. This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)

Note that index files are usually much smaller with MyISAM than with ISAM. This means that MyISAM will normally use less system resources than ISAM, but will need more CPU time when inserting data into a compressed index.

The following options to mysqld can be used to change the behaviour of MyISAM tables. See section 4.5.6.4 SHOW VARIABLES.

The automatic recovery is activated if you start mysqld with --myisam-recover=#. See section 4.1.1 mysqld Command-line Options. On open, the table is checked if it's marked as crashed or if the open count variable for the table is not 0 and you are running with --skip-external-locking. If either of the above is true the following happens.

• The table is checked for errors.
• If we found an error, try to do a fast repair (with sorting and without re-creating the datafile) of the table.
• If the repair fails because of an error in the datafile (for example a duplicate key error), we try again, but this time we re-create the datafile.
• If the repair fails, retry once more with the old repair option method (write row by row without sorting) which should be able to repair any type of error with little disk requirements..

If the recover wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE as an option to myisam-recover, then the automatic repair will abort with an error message in the error file:

Error: Couldn't repair table: test.g00pages

If you in this case had used the FORCE option you would instead have got a warning in the error file:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if you run automatic recover with the BACKUP option, you should have a cron script that automatically moves file with names like `tablename-datetime.BAK' from the database directories to a backup media.

See section 4.1.1 mysqld Command-line Options.

7.1.1 Space Needed for Keys

MySQL can support different index types, but the normal type is ISAM or MyISAM. These use a B-tree index, and you can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order and we don't have any compressed keys.)

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the above figures if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.

7.1.2 MyISAM Table Formats

MyISAM supports 3 different table types. Two of them are chosen automatically depending on the type of columns you are using. The third, compressed tables, can only be created with the myisampack tool.

When you CREATE or ALTER a table you can for tables that doesn't have BLOBs force the table format to DYNAMIC or FIXED with the ROW_FORMAT=# table option. In the future you will be able to compress/decompress tables by specifying ROW_FORMAT=compressed | default to ALTER TABLE. See section 6.5.3 CREATE TABLE Syntax.

7.1.2.1 Static (Fixed-length) Table Characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB, or TEXT columns.

This format is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with an index and static format it is very simple. Just multiply the row number by the row length.

Also, when scanning a table it is very easy to read a constant number of records with each disk read.

The security is evidenced if your computer crashes when writing to a fixed-size MyISAM file, in which case myisamchk can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in MySQL all indexes can always be reconstructed:

• All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
• Very quick.
• Easy to cache.
• Easy to reconstruct after a crash, because records are located in fixed positions.
• Doesn't have to be reorganised (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
• Usually requires more disk space than dynamic tables.

7.1.2.2 Dynamic Table Characteristics

This format is used if the table contains any VARCHAR, BLOB, or TEXT columns or if the table was created with ROW_FORMAT=dynamic.

This format is a little more complex because each row has to have a header that says how long it is. One record can also end up at more than one location when it is made longer at an update.

You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation:

• All string columns are dynamic (except those with a length less than 4).
• Each record is preceded by a bitmap indicating which columns are empty ('') for string columns, or zero for numeric columns. (This isn't the same as columns containing NULL values.) If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
• Usually takes much less disk space than fixed-length tables.
• Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as are required. This results in record fragmentation.
• If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run myisamchk -r from time to time to get better performance. Use myisamchk -ei tbl_name for some statistics.
• Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
• The expected row length for dynamic sized records is:

  3
  + (number of columns + 7) / 8
  + (number of char columns)
  + packed size of numeric columns
  + length of strings
  + (number of NULL columns + 7) / 8
  

  There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with myisamchk -ed. All links may be removed with myisamchk -r.

7.1.2.3 Compressed Table Characteristics

This is a read-only type that is generated with the optional myisampack tool (pack_isam for ISAM tables):

• All MySQL distributions, even those that existed before MySQL went GPL, can read tables that were compressed with myisampack.
• Compressed tables take very little disk space. This minimises disk usage, which is very nice when using slow disks (like CD-ROMs).
• Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
  • There is usually a different Huffman table for each column.
  • Suffix space compression.
  • Prefix space compression.
  • Numbers with value 0 are stored using 1 bit.
  • If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
  • If a column has only a small set of possible values, the column type is converted to ENUM.
  • A column may use a combination of the above compressions.
• Can handle fixed- or dynamic-length records.
• Can be uncompressed with myisamchk.

7.1.3 MyISAM Table Problems

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.

7.1.3.1 Corrupted MyISAM Tables

Even if the MyISAM table format is very reliable (all changes to a table is written before the SQL statements returns) , you can still get corrupted tables if some of the following things happens:

• The mysqld process being killed in the middle of a write.
• Unexpected shutdown of the computer (for example, if the computer is turned off).
• A hardware error.
• You are using an external program (like myisamchk) on a live table.
• A software bug in the MySQL or MyISAM code.

Typial typical symptoms for a corrupt table is:

• You get the error Incorrect key file for table: '...'. Try to repair it while selecting data from the table.
• Queries doesn't find rows in the table or returns incomplete data.

You can check if a table is ok with the command CHECK TABLE. See section 4.4.4 CHECK TABLE Syntax.

You can repair a corrupted table with REPAIR TABLE. See section 4.4.5 REPAIR TABLE Syntax. You can also repair a table, when mysqld is not running with the myisamchk command. myisamchk syntax.

If your tables get corrupted a lot you should try to find the reason for this! See section A.4.1 What To Do If MySQL Keeps Crashing.

In this case the most important thing to know is if the table got corrupted if the mysqld died (one can easily verify this by checking if there is a recent row restarted mysqld in the mysqld error file). If this isn't the case, then you should try to make a test case of this. See section E.1.6 Making a Test Case If You Experience Table Corruption.

7.1.3.2 Clients is using or hasn't closed the table properly

Each MyISAM `.MYI' file has in the header a counter that can be used to check if a table has been closed properly.

If you get the following warning from CHECK TABLE or myisamchk:

# clients is using or hasn't closed the table properly

this means that this counter has come out of sync. This doesn't mean that the table is corrupted, but means that you should at least do a check on the table to verify that it's okay.

The counter works as follows:

• The first time a table is updated in MySQL, a counter in the header of the index files is incremented.
• The counter is not changed during further updates.
• When the last instance of a table is closed (because of a FLUSH or because there isn't room in the table cache) the counter is decremented if the table has been updated at any point.
• When you repair the table or check the table and it was okay, the counter is reset to 0.
• To avoid problems with interaction with other processes that may do a check on the table, the counter is not decremented on close if it was 0.

In other words, the only ways this can go out of sync are:

• The MyISAM tables are copied without a LOCK and FLUSH TABLES.
• MySQL has crashed between an update and the final close. (Note that the table may still be okay, as MySQL always issues writes for everything between each statement.)
• Someone has done a myisamchk --recover or myisamchk --update-stateon a table that was in use by mysqld.
• Many mysqld servers are using the table and one has done a REPAIR or CHECK of the table while it was in use by another server. In this setup the CHECK is safe to do (even if you will get the warning from other servers), but REPAIR should be avoided as it currently replaces the datafile with a new one, which is not signaled to the other servers.

7.2 MERGE Tables

MERGE tables are new in MySQL Version 3.23.25. The code is still in gamma, but should be reasonable stable.

A MERGE table (also known as a MRG_MyISAM table) is a collection of identical MyISAM tables that can be used as one. You can only SELECT, DELETE, and UPDATE from the collection of tables. If you DROP the MERGE table, you are only dropping the MERGE specification.

Note that DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables. (We plan to fix this in 4.1).

With identical tables we mean that all tables are created with identical column and key information. You can't merge tables in which the columns are packed differently, doesn't have exactly the same columns, or have the keys in different order. However, some of the tables can be compressed with myisampack. See section 4.7.4 myisampack, The MySQL Compressed Read-only Table Generator.

When you create a MERGE table, you will get a `.frm' table definition file and a `.MRG' table list file. The `.MRG' just contains a list of the index files (`.MYI' files) that should be used as one. All used tables must be in the same database as the MERGE table itself.

For the moment, you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table.

MERGE tables can help you solve the following problems:

• Easily manage a set of log tables. For example, you can put data from different months into separate files, compress some of them with myisampack, and then create a MERGE to use these as one.
• Give you more speed. You can split a big read-only table based on some criteria and then put the different table part on different disks. A MERGE table on this could be much faster than using the big table. (You can, of course, also use a RAID to get the same kind of benefits.)
• Do more efficient searches. If you know exactly what you are looking after, you can search in just one of the split tables for some queries and use a MERGE table for others. You can even have many different MERGE tables active, with possible overlapping files.
• More efficient repairs. It's easier to repair the individual files that are mapped to a MERGE file than trying to repair a really big file.
• Instant mapping of many files as one. A MERGE table uses the index of the individual tables. It doesn't need to maintain an index of its one. This makes MERGE table collections VERY fast to make or remap. Note that you must specify the key definitions when you create a MERGE table!.
• If you have a set of tables that you join to a big table on demand or batch, you should instead create a MERGE table on them on demand. This is much faster and will save a lot of disk space.
• Go around the file-size limit for the operating system.
• You can create an alias/synonym for a table by just using MERGE over one table. There shouldn't be any really notable performance impacts of doing this (only a couple of indirect calls and memcpy() calls for each read).

The disadvantages with MERGE tables are:

• You can only use identical MyISAM tables for a MERGE table.
• REPLACE doesn't work.
• MERGE tables uses more file descriptors. If you are using a MERGE table that maps over 10 tables and 10 users are using this, you are using 10*10 + 10 file descriptors. (10 datafiles for 10 users and 10 shared index files.)
• Key reads are slower. When you do a read on a key, the MERGE storage engine will need to issue a read on all underlying tables to check which one most closely matches the given key. If you then do a "read-next" then the MERGE storage engine will need to search the read buffers to find the next key. Only when one key buffer is used up, the storage engine will need to read the next key block. This makes MERGE keys much slower on eq_ref searches, but not much slower on ref searches. See section 5.2.1 EXPLAIN Syntax (Get Information About a SELECT).
• You can't do DROP TABLE, ALTER TABLE, DELETE FROM table_name without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the table that is mapped by a MERGE table that is "open". If you do this, the MERGE table may still refer to the original table and you will get unexpected results. The easiest way to get around this deficiency is to issue the FLUSH TABLES command, ensuring no MERGE tables remain "open".

When you create a MERGE table you have to specify with UNION(list-of-tables) which tables you want to use as one. Optionally you can specify with INSERT_METHOD if you want insert for the MERGE table to happen in the first or last table in the UNION list. If you don't specify INSERT_METHOD or specify NO, then all INSERT commands on the MERGE table will return an error.

The following example shows you how to use MERGE tables:

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20))
             TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Note that you can also manipulate the `.MRG' file directly from the outside of the MySQL server:

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1.MYI t2.MYI > total.MRG
shell> mysqladmin flush-tables

Now you can do things like:

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Note that the a column, though declared as PRIMARY KEY, is not really unique, as MERGE table cannot enforce uniqueness over a set of underlying MyISAM tables.

To remap a MERGE table you can do one of the following:

• DROP the table and re-create it
• Use ALTER TABLE table_name UNION(...)
• Change the `.MRG' file and issue a FLUSH TABLE on the MERGE table and all underlying tables to force the storage engine to read the new definition file.

7.2.1 MERGE Table Problems

The following are the known problems with MERGE tables:

• A MERGE table cannot maintain UNIQUE constraints over the whole table. When you do INSERT, the data goes into the first or last table (according to INSERT_METHOD=xxx) and this MyISAM table ensures that the data are unique, but it knows nothing about others MyISAM tables.
• DELETE FROM merge_table used without a WHERE will only clear the mapping for the table, not delete everything in the mapped tables.
• RENAME TABLE on a table used in an active MERGE table may corrupt the table. This will be fixed in MySQL 4.0.x.
• Creation of a table of type MERGE doesn't check if the underlying tables are of compatible types. If you use MERGE tables in this fashion, you are very likely to run into strange problems.
• If you use ALTER TABLE to first add an UNIQUE index to a table used in a MERGE table and then use ALTER TABLE to add a normal index on the MERGE table, the key order will be different for the tables if there was an old non-unique key in the table. This is because ALTER TABLE puts UNIQUE keys before normal keys to be able to detect duplicate keys as early as possible.
• The range optimiser can't yet use MERGE table efficiently and may sometimes produce non-optimal joins. This will be fixed in MySQL 4.0.x.
• DROP TABLE on a table that is in use by a MERGE table will not work on Windows because the MERGE storage engine does the table mapping hidden from the upper layer of MySQL. Because Windows doesn't allow you to drop files that are open, you first must flush all MERGE tables (with FLUSH TABLES) or drop the MERGE table before dropping the table. We will fix this at the same time we introduce VIEWs.

7.3 ISAM Tables

You can also use the deprecated ISAM table type. This will disappear rather soon (probably in MySQL 5.0) because MyISAM is a better implementation of the same thing. ISAM uses a B-tree index. The index is stored in a file with the `.ISM' extension, and the data is stored in a file with the `.ISD' extension. You can check/repair ISAM tables with the isamchk utility. See section 4.4.6.7 Using myisamchk for Crash Recovery.

ISAM has the following features/properties:

• Compressed and fixed-length keys
• Fixed and dynamic record length
• 16 keys with 16 key parts/key
• Max key length 256 (default)
• Data is stored in machine format; this is fast, but is machine/OS dependent.

Most of the things true for MyISAM tables are also true for ISAM tables. See section 7.1 MyISAM Tables. The major differences compared to MyISAM tables are:

• ISAM tables are not binary portable across OS/Platforms.
• Can't handle tables > 4G.
• Only support prefix compression on strings.
• Smaller key limits.
• Dynamic tables get more fragmented.
• Tables are compressed with pack_isam rather than with myisampack.

If you want to convert an ISAM table to a MyISAM table so that you can use utilities such as mysqlcheck, use an ALTER TABLE statement:

mysql> ALTER TABLE tbl_name TYPE = MYISAM;

The embedded MySQL versions doesn't support ISAM tables.

7.4 HEAP Tables

HEAP tables use hashed indexes and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very useful for temporary tables!

The MySQL internal HEAP tables use 100% dynamic hashing without overflow areas. There is no extra space needed for free lists. HEAP tables also don't have problems with delete + inserts, which normally is common with hashed tables:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
    ->                   FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Here are some things you should consider when you use HEAP tables:

• You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidentally do not use all memory.
• Indexes will only be used with = and <=> (but are VERY fast).
• HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows.
• HEAP tables use a fixed record length format.
• HEAP doesn't support BLOB/TEXT columns.
• HEAP doesn't support AUTO_INCREMENT columns.
• Prior to MySQL 4.0.2, HEAP doesn't support an index on a NULL column.
• You can have non-unique keys in a HEAP table (this isn't common for hashed tables).
• HEAP tables are shared between all clients (just like any other table).
• You can't search for the next entry in order (that is, to use the index to do an ORDER BY).
• Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space are needed. Deleted rows are put in a linked list and are reused when you insert new data into the table.
• You need enough extra memory for all HEAP tables that you want to use at the same time.
• To free memory, you should execute DELETE FROM heap_table, TRUNCATE heap_table or DROP TABLE heap_table.
• MySQL cannot find out approximately how many rows there are between two values (this is used by the range optimiser to decide which index to use). This may affect some queries if you change a MyISAM table to a HEAP table.
• To ensure that you accidentally don't do anything foolish, you can't create HEAP tables bigger than max_heap_table_size.

The memory needed for one row in a HEAP table is:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

7.5 InnoDB Tables

7.5.1 InnoDB Tables Overview

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL.

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.

Technically, InnoDB is a complete database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, MyISAM tables where each table is stored as a separate file. InnoDB tables can be of any size also on those operating systems where file-size is limited to 2 GB.

You can find the latest information about InnoDB at http://www.innodb.com/. The most up-to-date version of the InnoDB manual is always placed there, and you can also order commercial licenses and support for InnoDB.

InnoDB is currently (October 2001) used in production at several large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1 TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB.

InnoDB tables are included in the MySQL source distribution starting from 3.23.34a and are activated in the MySQL -Max binary. For Windows the -Max binaries are contained 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, then the simplest way to install MySQL -Max is to replace the server executable `mysqld' with the corresponding executable in the -Max distribution. MySQL and MySQL -Max differ only in the server executable. See section 2.2.11 Installing a MySQL Binary Distribution. See section 4.7.5 mysqld-max, An Extended mysqld Server.

To compile MySQL with InnoDB support, download MySQL-3.23.34a or newer version from http://www.mysql.com/ and configure MySQL with the --with-innodb option. See the MySQL manual about installing a MySQL source distribution. See section 2.3 Installing a MySQL Source Distribution.

cd /path/to/source/of/mysql-3.23.37
./configure --with-innodb

To use InnoDB you have to specify InnoDB startup options in your `my.cnf' or `my.ini' file. The minimal way to modify it is to add to the [mysqld] section the line

innodb_data_file_path=ibdata:30M

but to get good performance it is best that you specify options as recommended. See section 7.5.2 InnoDB Startup Options.

InnoDB is distributed under the GNU GPL License Version 2 (of June 1991). In the source distribution of MySQL, InnoDB appears as a subdirectory.

7.5.2 InnoDB Startup Options

To use InnoDB tables in MySQL-Max-3.23 you MUST specify configuration parameters in the [mysqld] section of the configuration file `my.cnf', or on Windows optionally in `my.ini'.

At the minimum, in 3.23 you must specify innodb_data_file_path where you specify the names and the sizes of datafiles. If you do not mention innodb_data_home_dir in `my.cnf' the default is to create these files to the datadir of MySQL. If you specify innodb_data_home_dir as an empty string, then you can give absolute paths to your data files in innodb_data_file_path. In MySQL-4.0 you do not need to specify even innodb_data_file_path: the default for it is to create an auto-extending 10 MB file `ibdata1' to the datadir of MySQL. (In MySQL-4.0.0 and 4.0.1 the datafile is 64 MB and not auto-extending.)

If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file.

But to get good performance you MUST explicitly set the InnoDB parameters listed in the following examples.

Starting from versions 3.23.50 and 4.0.2 InnoDB allows the last datafile on the innodb_data_file_path line to be specified as auto-extending. The syntax for innodb_data_file_path is then the following:

pathtodatafile:sizespecification;pathtodatafile:sizespecification;...
...  ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]

If you specify the last datafile with the autoextend option, InnoDB will extend the last datafile if it runs out of free space in the tablespace. The increment is 8 MB at a time. An example:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instructs InnoDB to create just a single datafile whose initial size is 100 MB and which is extended in 8 MB blocks when space runs out. If the disk becomes full you may want to add another data file to another disk, for example. Then you have to look the size of `ibdata1', round the size downward to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the rounded size of `ibdata1' explicitly in innodb_data_file_path. After that you can add another datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Be cautious on filesystems where the maximum file-size is 2 GB! InnoDB is not aware of the OS maximum file-size. On those filesystems you might want to specify the max size for the datafile:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

A simple `my.cnf' example. Suppose you have a computer with 128 MB RAM and one hard disk. Below is an example of possible configuration parameters in `my.cnf' or `my.ini' for InnoDB. We assume you are running MySQL-Max-3.23.50 or later, or MySQL-4.0.2 or later. This example suits most users, both on Unix and Windows, who do not want to distribute InnoDB datafiles and log files on several disks. This creates an auto-extending data file `ibdata1' and two InnoDB log files `ib_logfile0' and `ib_logfile1' to the datadir of MySQL (typically `/mysql/data'). Also the small archived InnoDB log file `ib_arch_log_0000000000' ends up in the datadir.

[mysqld]
# You can write your other MySQL server options here
# ...
#                                  Data file(s) must be able to
#                                  hold your data and indexes.
#                                  Make sure 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
#                                  Set ..flush_log_at_trx_commit
#                                  to 0 if you can afford losing
#                                  some last transactions 
innodb_flush_log_at_trx_commit=1

Check that the MySQL server has the rights to create files in datadir.

Note that datafiles must be < 2G in some file systems! The combined size of the log files must be < 4G. The combined size of datafiles must be >= 10 MB.

When you for the first time create an InnoDB database, it is best that you start the MySQL server from the command prompt. Then InnoDB will print the information about the database creation to the screen, and you see what is happening. See below next section what the printout should look like. For example, in Windows you can start `mysqld-max.exe' with:

your-path-to-mysqld>mysqld-max --console

Where to put `my.cnf' or `my.ini' in Windows? The rules for Windows are the following:

• Only one of `my.cnf' or `my.ini' should be created.
• The `my.cnf' file should be placed in the root directory of the drive `C:'.
• The `my.ini' file should be placed in the WINDIR directory, e.g, `C:\WINDOWS' or `C:\WINNT'. You can use the SET command of MS-DOS to print the value of WINDIR.
• If your PC uses a boot loader where the `C:' drive is not the boot drive, then your only option is to use the `my.ini' file.

Where to specify options in Unix? On Unix `mysqld' reads options from the following files, if they exist, in the following order:

• `/etc/my.cnf' Global options.
• `COMPILATION_DATADIR/my.cnf' Server-specific options.
• `defaults-extra-file' The file specified with --defaults-extra-file=....
• `~/.my.cnf' User-specific options.

`COMPILATION_DATADIR' is the MySQL data directory which 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 are not sure from where `mysqld' reads its `my.cnf' or `my.ini', you can give the path as the first command-line option to the server: mysqld --defaults-file=your_path_to_my_cnf.

InnoDB forms the directory path to a datafile by textually catenating innodb_data_home_dir to a datafile name or path in innodb_data_file_path, adding a possible slash or backslash in between if needed. If the keyword innodb_data_home_dir is not mentioned in `my.cnf' at all, the default for it is the 'dot' directory `./' which means the datadir of MySQL.

An advanced `my.cnf' example. Suppose you have a Linux computer with 2 GB RAM and three 60 GB hard disks (at directory paths `/', `/dr2' and `/dr3'). Below is an example of possible configuration parameters in `my.cnf' for InnoDB.

Note that InnoDB does not create directories: you have to create them yourself. Use the Unix or MS-DOS mkdir command to create the data and log group home directories.

[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
#                                  < 2 GB
set-variable = innodb_buffer_pool_size=1G
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#                                  .._log_arch_dir must be the same
#                                  as .._log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
set-variable = innodb_log_files_in_group=3
#                                  Set the log file size to about
#                                  15 % of the buffer pool size
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M
#                                  Set ..flush_log_at_trx_commit to
#                                  0 if you can afford losing
#                                  some last transactions 
innodb_flush_log_at_trx_commit=1
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#set-variable = innodb_thread_concurrency=5

Note that we have placed the two datafiles on different disks. InnoDB will fill the tablespace formed by the datafiles from bottom up. 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 datafiles. In some Unixes they speed up I/O. See the manual section on InnoDB file space management about how to specify them in `my.cnf'.

Warning: on Linux x86 you must be careful you do not set memory usage too high. glibc will allow the process heap to grow over thread stacks, which will crash your server. It is a risk if the value of

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB

is close to 2 GB or exceeds 2 GB. Each thread will use a stack (often 2 MB, but in MySQL AB binaries only 256 kB) and in the worst case also sort_buffer + read_buffer_size additional memory.

How to tune other `mysqld' server parameters? Typical values which suit most users are:

skip-locking
set-variable = max_connections=200
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
#                                  Set key_buffer to 5 - 50%
#                                  of your RAM depending on how
#                                  much you use MyISAM tables, but
#                                  keep key_buffer + InnoDB
#                                  buffer pool size < 80% of
#                                  your RAM
set-variable = key_buffer=...

Note that some parameters are given using the numeric `my.cnf' parameter format: set-variable = innodb... = 123, others (string and boolean parameters) with another format: innodb_... = ... .

The meanings of the configuration parameters are the following:

7.5.3 Creating InnoDB Tablespace

Suppose you have installed MySQL and have edited `my.cnf' so that it contains the necessary InnoDB configuration parameters. Before starting MySQL you should check that the directories you have specified for InnoDB datafiles and log files exist and that you have access rights to those directories. InnoDB cannot create directories, only files. Check also you have enough disk space for the data and log files.

When you now start MySQL, InnoDB will start creating your datafiles and log files. InnoDB will print something like the following:

~/mysqlm/sql > mysqld
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: Log file /home/heikki/data/logs/ib_logfile2 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880
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', InnoDB 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 datafiles and logs 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 again started, the output will be like the following:

~/mysqlm/sql > mysqld
InnoDB: Started
mysqld: ready for connections

7.5.3.1 If Something Goes Wrong in Database Creation

If InnoDB prints an operating system error in a file operation, usually the problem is one of the following:

• You did not create InnoDB data or log directories.
• `mysqld' does not have the rights to create files in those directories.
• `mysqld' does not read the right `my.cnf' or `my.ini' file, and consequently does not see the options you specified.
• The disk is full or a disk quota is exceeded.
• You have created a subdirectory whose name is equal to a datafile you specified.
• There is a syntax error in innodb_data_home_dir or innodb_data_file_path.

If something goes wrong in an InnoDB database creation, you should delete all files created by InnoDB. This means all datafiles, all log files, the small archived log file, and in the case you already did create some InnoDB tables, delete also the corresponding `.frm' files for these tables from the MySQL database directories. Then you can try the InnoDB database creation again.

7.5.4 Creating InnoDB Tables

Suppose you have started the MySQL client with the command mysql test. To create a table in the InnoDB format you must specify TYPE = InnoDB in the table creation SQL command:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

This SQL command will create a table and an index on column A into the InnoDB tablespace consisting of the datafiles you specified in `my.cnf'. In addition MySQL will create a file `CUSTOMER.frm' to the MySQL database directory `test'. Internally, InnoDB will add to its own data dictionary an entry for table 'test/CUSTOMER'. Thus you can create a table of the same name CUSTOMER in another database of MySQL, and the table names will not collide inside InnoDB.

You can query the amount of free space in the InnoDB tablespace by issuing the table status command of MySQL for any table you have created with TYPE = InnoDB. Then the amount of free space in the tablespace appears in the table comment section in the output of SHOW. An example:

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Note that the statistics SHOW gives about InnoDB tables are only approximate: they are used in SQL optimisation. Table and index reserved sizes in bytes are accurate, though.

7.5.4.1 Converting MyISAM Tables to InnoDB

InnoDB does not have a special optimisation for separate index creation. Therefore it does not pay to export and import the table and create indexes afterwards. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table, that is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

To get better control over the insertion process, it may be good to insert big tables in pieces:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

After all data has been inserted you can rename the tables.

During the conversion of big tables you should set the InnoDB buffer pool size big to reduce disk I/O. Not bigger than 80% of the physical memory, though. You should set InnoDB log files big, and also the log buffer large.

Make sure you do not run out of tablespace: InnoDB tables take a lot more space than MyISAM tables. If an ALTER TABLE runs out of space, it will start a rollback, and that can take hours if it is disk-bound. In inserts InnoDB uses the insert buffer to merge secondary index records to indexes in batches. That saves a lot of disk I/O. In rollback no such mechanism is used, and the rollback can take 30 times longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data in your database, it is better that you kill the database process and delete all InnoDB data and log files and all InnoDB table `.frm' files, and start your job again, rather than wait for millions of disk I/Os to complete.

7.5.4.2 Foreign Key Constraints

Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data.

The syntax of a foreign key constraint definition in InnoDB:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]
                  [ON UPDATE {CASCADE | SET NULL | NO ACTION
                              | RESTRICT}]

Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly.

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 you have not declared the columns in the child table NOT NULL.

If MySQL gives the error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, then 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 version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Starting from version 4.0.8 you can use also similar ON UPDATE actions.

If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then 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.

Starting from version 3.23.50, InnoDB does not check foreign key constraints on those foreign key or referenced key values which contain a NULL column.

Starting from version 3.23.50 the InnoDB parser allows you to use backquotes (`) around table and column names in the FOREIGN KEY ... REFERENCES ... clause but the InnoDB parser is not yet aware of the option lower_case_table_names you can specify in `my.cnf'.

An example:

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 SET NULL
) TYPE=INNODB;

Starting from version 3.23.50 InnoDB allows you to add a new foreign key constraint to a table through

ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)
[on_delete_and_on_update_actions]

Remember to create the required indexes first, though. In InnoDB versions < 3.23.50 ALTER TABLE or CREATE INDEX should not be used in connection with tables which have foreign key constraints or which are referenced in foreign key constraints: Any ALTER TABLE removes all foreign key constrainst defined for the table. You should not use ALTER TABLE to the referenced table either, but 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 costraints which refer to the table. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it.

When doing 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.

InnoDB allows you to drop any table even though that would break the foreign key constraints which reference the table. When you drop a table the constraints which were defined in its create statement are also dropped.

If you re-create a table which was dropped, it has to have a definition which 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 above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string.

Starting from version 3.23.50 InnoDB returns the foreign key definitions of a table when you call

SHOW CREATE TABLE yourtablename

Then also `mysqldump' produces correct definitions of tables to the dump file, and does not forget about the foreign keys.

You can also list the foreign key constraints for a table T with

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

The foreign key constraints are listed in the table comment of the output.

7.5.5 Adding and Removing InnoDB Data and Log Files

From version 3.23.50 and 4.0.2 you can specify the last InnoDB datafile to autoextend. Alternatively, you can increase to your tablespace by specifying an additional datafile. To do this you have to shut down the MySQL server, edit the `my.cnf' file adding a new datafile to innodb_data_file_path, and then start the MySQL server again.

Currently you cannot remove a datafile from InnoDB. To decrease the size of your database you have to use `mysqldump' to dump all your tables, create a new database, and import your tables to the new database.

If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL 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 database. Delete then the old log files from the log file directory, edit `my.cnf', and start MySQL again. InnoDB will tell you at the startup that it is creating new log files.

7.5.6 Backing up and Recovering an InnoDB Database

The 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 additional tool which is not included in the standard MySQL distribution. See the InnoDB Hot Backup homepage http://www.innodb.com/hotbackup.html for detailed information and screenshots.

If you are able to shut down your MySQL server, then to take a 'binary' backup of your database you have to do the following:

• Shut down your MySQL database and make sure it shuts down without errors.
• Copy all your datafiles into a safe place.
• Copy all your InnoDB log files to a safe place.
• Copy your `my.cnf' configuration file(s) to a safe place.
• Copy all the `.frm' files for your InnoDB tables into a safe place.

In addition to taking the binary backups described above, you should also regularly take dumps of your tables with `mysqldump'. The reason to this is that a binary file may be corrupted without you noticing it. Dumped tables are stored into text files which are human-readable and much simpler than database binary files. Seeing table corruption from dumped files is easier, and since their format is simpler, the chance for serious data corruption in them is smaller.

A good idea is to take the dumps at the same time you take a binary backup of your database. You have to shut out all clients from your database to get a consistent snapshot of all your tables into your dumps. Then you can take the binary backup, and you will then have a consistent snapshot of your database in two formats.

To be able to recover your InnoDB database to the present from the binary backup described above, you have to run your MySQL database with the general logging and log archiving of MySQL switched on. Here by the general logging we mean the logging mechanism of the MySQL server which is independent of InnoDB logs.

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 which were present at the time of the crash. During recovery, InnoDB will print out something like the following:

~/mysqlm/sql > mysqld
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 which is not corrupted. From a backup do the recovery from the general log files of MySQL according to instructions in the MySQL manual.

7.5.6.1 Checkpoints

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 on 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 which 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 above 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 in big log files is that crash recovery can last longer because there will be more log to apply to the database.

7.5.7 Moving an InnoDB Database to Another Machine

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 we already listed in the previous section on backing up a 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' and `mysqlimport' to move those tables.

A performance tip is to switch off auto-commit mode when you import data into your database, assuming 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.

7.5.8 InnoDB Transaction Model

In the InnoDB transaction model the goal has been to combine the best properties of a multi-versioning database to traditional two-phase locking. InnoDB does locking on row level and runs queries by default as non-locking consistent reads, 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.

In InnoDB all user activity happens inside transactions. If the autocommit mode is used in MySQL, then each SQL statement will form a single transaction. MySQL always starts a new connection with the autocommit mode switched on.

If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can think that a user always has a transaction open. If he issues the SQL COMMIT or ROLLBACK statement, it ends the current transaction, and a new 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, on the other hand, cancels all modifications made by the current transaction.

If the connection has AUTOCOMMIT = 1, then the user can still perform a multi-statement transaction by starting it with BEGIN and ending it with COMMIT or ROLLBACK.

In terms of the SQL-1992 transaction isolation levels, the InnoDB default is REPEATABLE READ. Starting from version 4.0.5, InnoDB offers all 4 different transaction isolation levels described by the SQL-1992 standard. You can set the default isolation level for all connections in the [mysqld] section of `my.cnf':

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 [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                       {READ UNCOMMITTED | READ COMMITTED
                        | REPEATABLE READ | SERIALIZABLE}

SQL statement. Note that there are no hyphens in level names in the SQL syntax. If you specify the keyword GLOBAL in the above statement, it will determine the initial isolation level of new incoming connections, but will not change the isolation level of old connections. Any user is free to change the isolation level of his session, even in the middle of a transaction. In versions < 3.23.50 SET TRANSACTION had no effect on InnoDB tables. In versions < 4.0.5 only REPEATABLE READ and SERIALIZABLE were available.

You can query the global and session transaction isolation levels with:

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 means a lock which locks an index record and the gap before it. A gap lock means a lock which only locks a gap before some index record.

A detailed description of each isolation level in InnoDB:

• READ UNCOMMITTED This is also called 'dirty read': non-locking SELECTs are performed so that we do not look at a possible earlier version of a record; thus they are not 'consistent' reads under this isolation level; otherwise this level works like READ COMMITTED.
• READ COMMITTED Somewhat Oracle-like isolation level. All SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE statements only lock the index records, NOT the gaps before them, and thus allow free inserting of new records next to locked records. UPDATE and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. But still in range type UPDATE and DELETE InnoDB must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary since 'phantom rows' have to be blocked for MySQL replication and recovery to work. Consistent reads behave like in Oracle: each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
• REPEATABLE READ This is the default isolation level of InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE which use a unique index with a unique search condition, only lock the index record found, not the gap before it. Otherwise 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 SELECTs within the same transaction, these SELECTs are consistent also with respect to each other.
• SERIALIZABLE This level is like the previous one, but all plain SELECTs are implicitly converted to SELECT ... LOCK IN SHARE MODE.

7.5.8.1 Consistent Read

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 which 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.

7.5.8.2 Locking Reads

A consistent read is not convenient in some circumstances. Suppose you want to add a new row into your table CHILD, and make sure that the child already has a parent in table PARENT.

Suppose 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 has deleted the parent row from the table PARENT, and you are not aware of that.

The solution is to perform the SELECT in a locking mode, 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. If the latest data belongs to a yet uncommitted transaction of another user, we will wait until that transaction commits. A shared mode lock prevents others from updating or deleting the row we have read. After we see that the above query returns the parent 'Jones', we can safely add his child to table CHILD, and commit our transaction. This example shows how to implement referential integrity in your application code.

Let us look at another example: we have an integer counter field in a table CHILD_CODES which we use to assign a unique identifier to each child we add 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 then two users of the database may see the same value for the counter, and we will get a duplicate key error when we add the two children with the same identifier to the table.

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:

SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE;
UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;

A SELECT ... FOR UPDATE will read 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.

7.5.8.3 Next-key Locking: Avoiding the Phantom Problem

In row level locking InnoDB uses an algorithm called next-key locking. InnoDB does the row level locking so that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records in encounters. Thus the row level locks are more precisely called 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, then 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 I want to read and lock all children with identifier bigger than 100 from table CHILD, and update some field in the selected rows.

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

Suppose there is an index on table CHILD on column ID. Our 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 child might meanwhile be inserted to the table. If now I in my transaction execute

SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;

again, I will see a new child in the result set the query returns. This is against 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, then the new 'phantom' child would break 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 will 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.

7.5.8.4 Locks Set by Different SQL Statements in InnoDB

• SELECT ... FROM ... : this is a consistent read, reading a snapshot of the database and setting no locks.
• 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, sets a shared lock on the duplicate index record.
• INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. Does the search on S as a consistent read, but sets shared next-key locks on S if the MySQL logging is on. InnoDB has to set locks in the latter case because in roll-forward recovery from a backup every SQL statement has to be executed in exactly the same way as it was done originally.
• CREATE TABLE ... SELECT ... performs the SELECT as a consistent read or with shared locks, like 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 which has to be updated.
• UPDATE ... SET ... 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.
• If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete which requires checking of the constraint condition sets shared record level locks on the records it looks at to check the constraint. Also in the case where the constraint fails, InnoDB sets these locks.
• LOCK TABLES ... : sets table locks. In the implementation the MySQL layer of code sets these locks. The automatic deadlock detection of InnoDB cannot detect deadlocks where such table locks are involved: see the following section. Also, since MySQL does know about row level locks, it is possible that you get a table lock on a table where another user currently has row level locks. But that does not put transaction integerity into danger. See section 7.5.13 Restrictions on InnoDB Tables.

7.5.8.5 Deadlock Detection and Rollback

InnoDB automatically detects a deadlock of transactions and rolls back a transaction or transactions to prevent the deadlock. Starting from version 4.0.5, InnoDB will try 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. Previous 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.

InnoDB cannot detect deadlocks where a lock set by a MySQL LOCK TABLES statement is involved, or if a lock set in another storage engine than InnoDB is involved. You have to resolve these situations using innodb_lock_wait_timeout set in `my.cnf'.

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 where it cannot afterwards know which was set by which SQL statement.

7.5.8.6 An Example of How the Consistent Read Works in InnoDB

Suppose you are running on 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. Thus, if transaction B deletes a row and commits after your timepoint was assigned, then you will not see the row deleted. Similarly with inserts and updates.

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    |
              ---------------------

Thus user A sees the row inserted by B only when B has committed the insert, and A has committed his own transaction 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 a locking read:

SELECT * FROM t LOCK IN SHARE MODE;

7.5.8.7 How to cope with deadlocks?

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 have to 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 which 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/deleted.

You can cope with deadlocks and reduce the number of them with the following tricks:

• Use SHOW INNODB STATUS in MySQL versions >= 3.23.52 and >= 4.0.3 to determine the cause of the latest deadlock. That can help you to tune your application to avoid deadlocks.
• Always be prepared to re-issue a transaction if it fails in a deadlock. Deadlocks are not dangerous. Just try again.
• Commit your transactions often. Small transactions are less prone to collide.
• If you are using locking reads SELECT ... FOR UPDATE or ... LOCK IN SHARE MODE, try using a lower isolation level READ COMMITTED.
• Access your tables and rows in a fixed order. Then transactions will form nice queues, and do not deadlock.
• Add good indexes to your tables. Then your queries need to scan less index records and consequently set less locks. Use EXPLAIN SELECT to determine that MySQL picks appropriate indexes for your queries.
• Use less locking: if you can afford a 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.
• If nothing helps, serialize your transactions with table level locks: LOCK TABLES t1 WRITE, t2 READ, ... ; [do something with tables t1 and t2 here]; UNLOCK TABLES. Table level locks make you transactions to queue nicely, and deadlocks are avoided. Note that LOCK TABLES implicitly starts a transaction, just like the command BEGIN, and UNLOCK TABLES implicitly ends the transaction in a COMMIT.
• Another solution to serialize transactions is to create an auxiliary 'semaphore' table where there is just a single row. Each transaction updates that row before accessing other tables. In that way all transactions happen in a serial fashion. Note that then also the InnoDB instant deadlock detection algorithm works, because the serializing lock is a row level lock. In MySQL table level locks we have to resort to the timeout method to resolve a deadlock.

7.5.8.8 Performance Tuning Tips

1. If the Unix `top' or the Windows `Task Manager' shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it bigger than 80% of physical memory.

2. Wrap several modifications into one transaction. InnoDB must flush the log to disk at each transaction commit, if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167/second if the disk does not fool the operating system.

3. If you can afford the loss of some latest committed transactions, you can set the `my.cnf' parameter innodb_flush_log_at_trx_commit to 0. InnoDB tries to flush the log once per second anyway, though the flush is not guaranteed.

4. Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files will cause many unnecessary disk writes. The drawback in big log files is that recovery time will be longer.

5. Also the log buffer should be quite big, say 8 MB.

6. (Relevant from 3.23.39 up.) In some versions of Linux and Unix, flushing files to disk with the Unix fdatasync and other similar methods is surprisingly slow. The default method InnoDB uses is the fdatasync function. If you are not satisfied with the database write performance, you may try setting innodb_flush_method in `my.cnf' to O_DSYNC, though O_DSYNC seems to be slower on most systems.

7. In importing data to InnoDB, make sure that MySQL does not have autocommit=1 on. Then every insert requires a log flush to disk. Put before your plain SQL import file line

SET AUTOCOMMIT=0;

and after it

COMMIT;

If you use the `mysqldump' option --opt, you will get dump files which are fast to import also to an InnoDB table, even without wrapping them to the above SET AUTOCOMMIT=0; ... COMMIT; wrappers.

8. Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but in a corresponding rollback no such mechanism is used. A disk-bound rollback can take 30 times the time of the corresponding insert. Killing the database process will not help because the rollback will start again at the database startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or delete the whole InnoDB database.

9. Beware also of other big disk-bound operations. Use DROP TABLE or TRUNCATE (from MySQL-4.0 up) to empty a table, not DELETE FROM yourtable.

10. Use the multi-line INSERT 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 of course valid for inserts into any table type, not just InnoDB.

7.5.8.9 The InnoDB Monitor

Starting from version 3.23.41 InnoDB includes the InnoDB Monitor which prints information on the InnoDB internal state. When switched on, InnoDB Monitor will make the MySQL server `mysqld' to print data (note: the MySQL client will not print anything) to the standard output about once every 15 seconds. This data is useful in performance tuning. On Windows you must start mysqld-max from a MS-DOS prompt with the --standalone --console options to direct the output to the MS-DOS prompt window.

There is a separate innodb_lock_monitor which prints the same information as innodb_monitor plus information on locks set by each transaction.

The printed information includes data on:

• lock waits of a transactions,
• semaphore waits of threads,
• pending file I/O requests,
• buffer pool statistics, and
• purge and insert buffer merge activity of the main thread of InnoDB.

You can start InnoDB Monitor through the following SQL command:

CREATE TABLE innodb_monitor(a int) type = innodb;

and stop it by

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 created table is not relevant at all for InnoDB Monitor. If you shut down the database 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 to start the monitor. This syntax may change in a future release.

A sample output of the InnoDB Monitor:

================================
010809 18:45:06 INNODB MONITOR OUTPUT
================================
--------------------------
LOCKS HELD BY TRANSACTIONS
--------------------------
LOCK INFO:
Number of locks in the record hash table 1294
LOCKS FOR TRANSACTION ID 0 579342744
TABLE LOCK table test/mytable trx id 0 582333343 lock_mode IX

RECORD LOCKS space id 0 page no 12758 n bits 104 table test/mytable index
PRIMARY trx id 0 582333343 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 74; 1-byte offs FALSE;
info bits 0
 0: len 4; hex 0001a801; asc ;; 1: len 6; hex 000022b5b39f; asc ";;
 2: len 7; hex 000002001e03ec; asc ;; 3: len 4; hex 00000001;
...
-----------------------------------------------
CURRENT SEMAPHORES RESERVED AND SEMAPHORE WAITS
-----------------------------------------------
SYNC INFO:
Sorry, cannot give mutex list info in non-debug version!
Sorry, cannot give rw-lock list info in non-debug version!
-----------------------------------------------------
SYNC ARRAY INFO: reservation count 6041054, signal count 2913432
4a239430 waited for by thread 49627477 op. S-LOCK file NOT KNOWN line 0
Mut ex 0 sp 5530989 r 62038708 sys 2155035;
rws 0 8257574 8025336; rwx 0 1121090 1848344
-----------------------------------------------------
CURRENT PENDING FILE I/O'S
--------------------------
Pending normal aio reads:
Reserved slot, messages 40157658 4a4a40b8
Reserved slot, messages 40157658 4a477e28
...
Reserved slot, messages 40157658 4a4424a8
Reserved slot, messages 40157658 4a39ea38
Total of 36 reserved aio slots
Pending aio writes:
Total of 0 reserved aio slots
Pending insert buffer aio reads:
Total of 0 reserved aio slots
Pending log writes or reads:
Reserved slot, messages 40158c98 40157f98
Total of 1 reserved aio slots
Pending synchronous reads or writes:
Total of 0 reserved aio slots
-----------
BUFFER POOL
-----------
LRU list length 8034
Free list length 0
Flush list length 999
Buffer pool size in pages 8192
Pending reads 39
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31383918, created 51310, written 2985115
----------------------------
END OF INNODB MONITOR OUTPUT
============================
010809 18:45:22 InnoDB starts purge
010809 18:45:22 InnoDB purged 0 pages

Some notes on the output:

• If the section LOCKS HELD BY TRANSACTIONS reports lock waits, then your application may have lock contention. The output can also help to trace reasons for transaction deadlocks.
• Section SYNC INFO will report reserved semaphores if you compile InnoDB with UNIV_SYNC_DEBUG defined in `univ.i'.
• Section SYNC ARRAY INFO 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 big 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.
• Section CURRENT PENDING FILE I/O'S lists pending file I/O requests. A large number of these indicates that the workload is disk I/O-bound.
• Section BUFFER POOL gives you statistics on pages read and written. You can calculate from these numbers how many datafile I/Os your queries are currently doing.

7.5.9 Implementation of Multi-versioning

Since InnoDB is a multi-versioned database, it must keep information of old versions of rows in the tablespace. This information is stored in a data structure we call a rollback segment after an analogous data structure in Oracle.

InnoDB internally adds two fields to each row stored in the database. A 6-byte field tells the transaction identifier for the last transaction which inserted or updated the row. Also a deletion is internally treated 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, then 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 only needed 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, also those transactions which 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 our 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, it can 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 which did the deletion.

7.5.10 Table and Index Structures

MySQL stores its data dictionary information of tables in `.frm' files in database directories. But every InnoDB type 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 a `.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, and why DROP DATABASE did not work for InnoDB type tables in MySQL versions <= 3.23.43.

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, then the index of the primary key will be the clustered index.

If you do not define a primary key for your table, InnoDB will internally generate a clustered index where the rows are ordered by the row id InnoDB assigns to the rows in such a table. The row id is a 6-byte field which monotonically increases 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 us. In many databases the data is traditionally stored on a different page from the index record. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution.

The records in non-clustered indexes (we also call them secondary indexes), in InnoDB 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 will use more space.

7.5.10.1 Physical Structure of an Index

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 16 kB. 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 (ascending or descending) order, the resulting index pages will be about 15/16 full. If records are inserted in a random order, then the pages will be 1/2 - 15/16 full. If the fillfactor of an index page drops below 1/2, InnoDB will try to contract the index tree to free the page.

7.5.10.2 Insert Buffering

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 happen in a relatively random order into secondary indexes. This would cause a lot of random disk I/Os without a special mechanism used in InnoDB.

If an index record should be inserted to a non-unique secondary index, InnoDB checks if the secondary index page is already in the buffer pool. If that is the case, InnoDB will do the insertion directly to the index page. But, if the index page is not found from the buffer pool, InnoDB inserts the record to a special insert buffer structure. The insert buffer is kept so small that it entirely fits in the buffer pool, and insertions can be made to it very fast.

The insert buffer is periodically merged to the secondary index trees in the database. Often we can merge several insertions on the same page in of the index tree, and hence save disk I/Os. It has been measured that the insert buffer can speed up insertions to a table up to 15 times.

7.5.10.3 Adaptive Hash Indexes

If a database fits almost entirely in main memory, then the fastest way to perform queries on it is to use hash indexes. InnoDB has an automatic mechanism which monitors index searches made to the indexes defined for a table, and if InnoDB notices that queries could benefit from building of a hash index, such an index is automatically built.

But 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 what search pattern InnoDB observes on 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 to those pages of the index which are often accessed.

In a sense, through the adaptive hash index mechanism InnoDB adapts itself to ample main memory, coming closer to the architecture of main memory databases.

7.5.10.4 Physical Record Structure

• Each index record in InnoDB contains a header of 6 bytes. The header is used to link consecutive records together, and also in the row level locking.
• Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte field for the transaction id and a 7-byte field for the roll pointer.
• If the user has not defined a primary key for a table, then each clustered index record contains also a 6-byte row id field.
• Each secondary index record contains also all the fields defined for the clustered index key.
• A record contains also a pointer to each field of the record. If the total length of the fields in a record is < 128 bytes, then the pointer is 1 byte, else 2 bytes.

7.5.10.5 How an Auto-increment Column Works in InnoDB

After a database startup, when a user first does an insert to a table T where an auto-increment column has been defined, and the user does not provide an explicit value for the column, then InnoDB executes SELECT MAX(auto-inc-column) FROM T, and assigns that value incremented by one to the column and the auto-increment counter of the table. We say that the auto-increment counter for table T has been initialised.

InnoDB follows the same procedure in initializing the auto-increment counter for a freshly created table.

Note that if the user specifies in an insert the value 0 to the auto-increment column, then InnoDB treats the row like the value would not have been specified.

After the auto-increment counter has been initialised, if a user inserts a row where he explicitly specifies the column value, and the value is bigger than the current counter value, then the counter is set to the specified column value. If the user does not explicitly specify a value, then InnoDB increments the counter by one and assigns its new value to the column.

The auto-increment mechanism, when assigning values from the counter, bypasses locking and transaction handling. Therefore you may also get gaps in the number sequence if you roll back transactions which have got numbers from the counter.

The behaviour of auto-increment is not defined if a user gives 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.

7.5.11 File Space Management and Disk I/O

7.5.11.1 Disk I/O

In disk I/O InnoDB uses asynchronous I/O. On Windows NT it uses the native asynchronous I/O provided by the operating system. On Unix, InnoDB uses simulated asynchronous I/O built into InnoDB: InnoDB creates a number of I/O threads to take care of I/O operations, such as read-ahead. In a future version we will add support for simulated aio on Windows NT and native aio on those versions of Unix which have one.

On Windows NT InnoDB uses non-buffered I/O. That means that the disk pages InnoDB reads or writes are not buffered in the operating system file cache. This saves some memory bandwidth.

Starting from 3.23.41 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 InnoDB before writing pages to a datafile 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, InnoDB writes the pages to their proper positions in the datafile. If the operating system crashes in the middle of a page write, InnoDB will in recovery find a good copy of the page from the doublewrite buffer.

Starting from 3.23.41 you can also use a raw disk partition as a datafile, though this has not been tested yet. When you create a new datafile you have to put the keyword newraw immediately after the data file-size in innodb_data_file_path. The partition must be >= than you specify as the size. Note that 1M in InnoDB is 1024 x 1024 bytes, while in disk specifications 1 MB usually means 1000 000 bytes.

innodb_data_file_path=hdd1:5Gnewraw;hdd2:2Gnewraw

When you start the database again you must change the keyword to raw. Otherwise, InnoDB will write over your partition!

innodb_data_file_path=hdd1:5Graw;hdd2:2Graw

By using a raw disk you can on some Unixes perform unbuffered I/O.

There are two read-ahead heuristics in InnoDB: sequential read-ahead and random read-ahead. In sequential read-ahead InnoDB notices that the access pattern to a segment in the tablespace is sequential. Then InnoDB will post in advance a batch of reads of database pages to the I/O system. In random read-ahead InnoDB notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool. Then InnoDB posts the remaining reads to the I/O system.

7.5.11.2 File Space Management

The datafiles you define in the configuration file form the tablespace of InnoDB. The files are simply catenated to form the tablespace, there is no striping in use. Currently you cannot directly instruct where the space is allocated for your tables, except by using the following fact: from a newly created tablespace InnoDB will allocate space starting from the low end.

The tablespace consists of database pages whose default size is 16 kB. 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 misleading because it actually contains many segments in the tablespace.

For each index in InnoDB we allocate two segments: 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 issue a query SHOW TABLE STATUS FROM ... LIKE ... to ask for available free space in the tablespace, InnoDB will report the extents which 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 if that frees individual pages or extents to the tablespace, so that the freed space is 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 can be physically removed only in a purge operation after they are no longer needed in transaction rollback or consistent read.

7.5.11.3 Defragmenting a Table

If there are random insertions or deletions in the indexes of a table, the indexes may become fragmented. By fragmentation we mean that the physical ordering of the index pages on the disk is not close to the alphabetical ordering of the records on the pages, or that there are many unused pages in the 64-page blocks which were allocated to the index.

It can speed up index scans if you periodically use mysqldump to dump the table to a text file, drop the table, and reload it from the dump. Another way to do the defragmenting is to ALTER the table type to MyISAM and back to InnoDB again. Note that a MyISAM table must fit in a single file on your operating system.

If the insertions to and index are always ascending and records are deleted only from the end, then the file space management algorithm of InnoDB guarantees that fragmentation in the index will not occur.

7.5.12 Error Handling

The error handling in InnoDB is not always the same as specified in the ANSI SQL standards. According to the ANSI 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 list specifies the error handling of InnoDB.

• If you run out of file space in the tablespace, you will get the MySQL 'Table is full' error and InnoDB rolls back the SQL statement.
• A transaction deadlock or a timeout in a lock wait make InnoDB to roll back the whole transaction.
• A duplicate key error only rolls back the insert of that particular row, even in a statement like 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.
• A 'row too long' error rolls back the SQL statement.
• Other errors are mostly detected by the MySQL layer of code, and they roll back the corresponding SQL statement.

7.5.13 Restrictions on InnoDB Tables

• Warning: do NOT convert MySQL system tables from MyISAM TO InnoDB tables! This is not supported; 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.
• 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 optimisation.
• If you try to create a unique index on a prefix of a column you will get an error:

  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.
• INSERT DELAYED is not supported for InnoDB tables.
• The MySQL LOCK TABLES operation does not know of InnoDB row level locks set in already completed SQL statements: this means that you can get a table lock on a table even if there still exist transactions of other users which 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.
• You cannot have a key on a BLOB or TEXT column.
• A table cannot contain more than 1000 columns.
• DELETE FROM TABLE does not regenerate the table but instead deletes all rows, one by one, which is not that fast. In future versions of MySQL you can use TRUNCATE which is fast.
• The default database page size in InnoDB is 16 kB. By recompiling the code one can set it from 8 kB to 64 kB. The maximun row length is slightly less than half of a database page in versions <= 3.23.40 of InnoDB. Starting from source release 3.23.41 BLOB and TEXT columns are allowed to be < 4 GB, the total row length must also be < 4 GB. InnoDB does not store fields whose size is <= 128 bytes on separate pages. After InnoDB has modified the row by storing long fields on separate pages, the remaining length of the row must be less than half a database page. The maximun key length is 7000 bytes.
• On some operating systems datafiles must be < 2 GB. The combined size of log files must be < 4 GB.
• The maximum tablespace size is 4 billion database pages. This is also the maximum size for a table. The minimum tablespace size is 10 MB.

7.5.14 InnoDB Change History

7.5.14.1 MySQL/InnoDB-4.1.0, April x, 2003

• InnoDB now supports up to 64 GB of buffer pool memory in a Windows 32-bit Intel computer. This is possible because InnoDB can use the AWE extension of Windows to address memory over the 4 GB limit of a 32-bit process. A new startup variable innodb_buffer_pool_awe_mem_mb enables AWE and sets the size of the buffer pool in megabytes.
• Reduced the size of buffer headers and the lock table. InnoDB uses 2 % less memory.

7.5.14.2 MySQL/InnoDB-3.23.56, March xx, 2003

• Fixed a major bug in InnoDB query optimization: queries of type SELECT ... WHERE indexcolumn < x and SELECT ... WHERE indexcolumn > x could cause a table scan even if the selectivity would have been very good.

7.5.14.3 MySQL/InnoDB-4.0.12, March xx, 2003

• In crash recovery InnoDB now prints the progress in percents of a transaction rollback.

7.5.14.4 MySQL/InnoDB-4.0.11, February 25, 2003

• Fixed a bug introduced in 4.0.10: SELECT ... FROM ... ORDER BY ... DESC could hang in an infinite loop.
• An outstanding bug: SET FOREIGN_KEY_CHECKS=0 is not replicated properly in the MySQL replication.

7.5.14.5 MySQL/InnoDB-4.0.10, February 4, 2003

• In INSERT INTO t1 SELECT ... FROM t2 WHERE ... MySQL previously set a table level read lock on t2. This lock is now removed.
• Increased SHOW INNODB STATUS max printed length to 200 kB.
• Fixed a major bug in InnoDB query optimization: queries of type SELECT ... WHERE indexcolumn < x and SELECT ... WHERE indexcolumn > x could cause a table scan even if the selectivity would have been very good.
• Fixed a bug: purge could cause a hang in a BLOB table where the primary key index tree was of height 1. Symptom: semaphore waits caused by an X-latch set in btr_free_externally_stored_field().
• Fixed a bug: using InnoDB HANDLER commands on a fresh handle crashed mysqld in ha_innobase::change_active_index().
• Fixed a bug: if MySQL estimated a query in the middle of a SELECT statement, InnoDB could hang on the adaptive hash index latch in btr0sea.c.
• Fixed a bug: InnoDB could report table corruption and assert in page_dir_find_owner_slot() if an adaptive hash index search coincided with purge or an insert.
• Fixed a bug: some file system snapshot tool in Windows 2000 could cause an InnoDB file write to fail with error 33 ERROR_LOCK_VIOLATION. In synchronous writes InnoDB now retries the write 100 times at 1 second intervals.
• Fixed a bug: REPLACE INTO t1 SELECT ... did not work if t1 has an auto-inc column.
• An outstanding bug: SET FOREIGN_KEY_CHECKS=0 is not replicated properly in the MySQL replication.

7.5.14.6 MySQL/InnoDB-3.23.55, January 24, 2003

• In INSERT INTO t1 SELECT ... FROM t2 WHERE ... MySQL previously set a table level read lock on t2. This lock is now removed.
• Fixed a bug: if the combined size of InnoDB log files was >= 2 GB in a 32-bit computer, InnoDB would write log in a wrong position. That could make crash recovery and InnoDB Hot Backup to fail in log scan.
• Fixed a bug: index cursor restoration could theoretically fail.
• Fixed a bug: an assertion in btr0sea.c, in function btr_search_info_update_slow could theoretically fail in a race of 3 threads.
• Fixed a bug: purge could cause a hang in a BLOB table where the primary key index tree was of height 1. Symptom: semaphore waits caused by an X-latch set in btr_free_externally_stored_field().
• Fixed a bug: if MySQL estimated a query in the middle of a SELECT statement, InnoDB could hang on the adaptive hash index latch in btr0sea.c.
• Fixed a bug: InnoDB could report table corruption and assert in page_dir_find_owner_slot() if an adaptive hash index search coincided with purge or an insert.
• Fixed a bug: some file system snapshot tool in Windows 2000 could cause an InnoDB file write to fail with error 33 ERROR_LOCK_VIOLATION. In synchronous writes InnoDB now retries the write 100 times at 1 second intervals.
• An outstanding bug: SET FOREIGN_KEY_CHECKS=0 is not replicated properly in the MySQL replication. The fix will appear in 4.0.11 and will probably not be backported to 3.23.
• Fixed bug in InnoDB page0cur.c in function page_cur_search_with_match which caused InnoDB to remain on the same page forever. This bug is evident only in tables with more than one page.

7.5.14.7 MySQL/InnoDB-4.0.9, January 14, 2003

• Removed the warning message: 'InnoDB: Out of memory in additional memory pool.'
• Fixed a bug: if the combined size of InnoDB log files was >= 2 GB in a 32-bit computer, InnoDB would write log in a wrong position. That could make crash recovery and InnoDB Hot Backup to fail.
• Fixed a bug: index cursor restoration could theoretically fail.

7.5.14.8 MySQL/InnoDB-4.0.8, January 7, 2003

• InnoDB now supports also FOREIGN KEY (...) REFERENCES ...(...) [ON UPDATE CASCADE | ON UPDATE SET NULL | ON UPDATE RESTRICT | ON UPDATE NO ACTION].
• Tables and indexes now reserve 4 % less space in the tablespace. Also existing tables reserve less space. By upgrading to 4.0.8 you will see more free space in "InnoDB free" in SHOW TABLE STATUS.
• Fixed bugs: updating the PRIMARY KEY of a row would generate a foreign key error on all FOREIGN KEYs which referenced secondary keys of the row to be updated. Also, if a referencing FOREIGN KEY constraint only referenced the first columns in an index, and there were more columns in that index, updating the additional columns generated a foreign key error.
• Fixed a bug: if an index contains some column twice, and that column is updated, the table will become corrupt. From now on InnoDB prevents creation of such indexes.
• Fixed a bug: removed superfluous error 149 and 150 printouts from the .err log when a locking SELECT caused a deadlock or a lock wait timeout.
• Fixed a bug: an assertion in btr0sea.c, in function btr_search_info_update_slow could theoretically fail in a race of 3 threads.
• Fixed a bug: one could not switch a session transaction isolation level back to REPEATABLE READ after setting it to something else.

7.5.14.9 MySQL/InnoDB-4.0.7, December 26, 2002

• InnoDB in 4.0.7 is essentially the same as in 4.0.6.

7.5.14.10 MySQL/InnoDB-4.0.6, December 19, 2002

• Since innodb_log_arch_dir has no relevance under MySQL, there is no need to specify it any more in my.cnf.
• LOAD DATA INFILE in AUTOCOMMIT=1 mode no longer does implicit commits for each 1 MB of written binlog.
• Fixed a bug introduced in 4.0.4: LOCK TABLES ... READ LOCAL should not set row locks on the rows read. This caused deadlocks and lock wait timeouts in mysqldump.
• Fixed two bugs introduced in 4.0.4: in AUTO_INCREMENT, REPLACE could cause the counter to be left 1 too low. A deadlock or a lock wait timeout could cause the same problem.
• Fixed a bug: TRUNCATE on a TEMPORARY table crashed InnoDB.
• Fixed a bug introduced in 4.0.5: if binlogging was not switched on, INSERT INTO ... SELECT ... or CREATE TABLE ... SELECT ... could cause InnoDB to hang on a semaphore created in btr0sea.c, line 128. Workaround: switch binlogging on.
• Fixed a bug: in replication issuing SLAVE STOP in the middle of a multi-statement transaction could cause that SLAVE START would only perform a part of the transaction. A similar error could occur if the slave crashed and was restarted.

7.5.14.11 MySQL/InnoDB-3.23.54, December 12, 2002

• Fixed a bug: the InnoDB range estimator greatly exaggerated the size of a short index range if the paths to the endpoints of the range in the index tree happened to branch already in the root. This could cause unnecessary table scans in SQL queries.
• Fixed a bug: ORDER BY could fail if you had not created a primary key to a table, but had defined several indexes of which at least one was a UNIQUE index with all its columns declared as NOT NULL.
• Fixed a bug: a lock wait timeout in connection with ON DELETE CASCADE could cause corruption in indexes.
• Fixed a bug: if a SELECT was done with a unique key from a primary index, and the search matched to a delete-marked record, InnoDB could erroneously return the NEXT record.
• Fixed a bug introduced in 3.23.53: LOCK TABLES ... READ LOCAL should not set row locks on the rows read. This caused deadlocks and lock wait timeouts in mysqldump.
• Fixed a bug: if an index contains some column twice, and that column is updated, the table will become corrupt. From now on InnoDB prevents creation of such indexes.

7.5.14.12 MySQL/InnoDB-4.0.5, November 18, 2002

• InnoDB now supports also transaction isolation levels READ COMMITTED and READ UNCOMMITTED. READ COMMITTED more closely emulates Oracle and makes porting of applications from Oracle to MySQL easier.
• Deadlock resolution is now selective: we try to pick as victims transactions with less modified or inserted rows.
• FOREIGN KEY definitions are now aware of the lower_case_table_names setting in my.cnf.
• SHOW CREATE TABLE does not output the database name to a FOREIGN KEY definition if the referred table is in the same database as the table.
• InnoDB does a consistency check to most index pages before writing them to a data file.
• If you set innodb_force_recovery > 0, InnoDB tries to jump over corrupt index records and pages when doing SELECT * FROM table. This helps in dumping.
• InnoDB now again uses asynchronous unbuffered i/o in Windows 2000 and XP; only unbuffered simulated async i/o in NT, 95/98/ME.
• Fixed a bug: the InnoDB range estimator greatly exaggerated the size of a short index range if the paths to the endpoints of the range in the index tree happened to branch already in the root. This could cause unnecessary table scans in SQL queries. The fix will also be backported to 3.23.54.
• Fixed a bug present in 3.23.52, 4.0.3, 4.0.4: InnoDB startup could take very long or even crash on some Win 95/98/ME computers.
• Fixed a bug: the AUTO-INC lock was held to the end of the transaction if it was granted after a lock wait. This could cause unnecessary deadlocks.
• Fixed a bug: if SHOW INNODB STATUS, innodb_monitor, or innodb_lock_monitor had to print several hundred transactions in one report, and the output became truncated, InnoDB would hang, printing to the error log many waits for a mutex created at srv0srv.c, line 1621.
• Fixed a bug: SHOW INNODB STATUS on Unix always reported average file read size as 0 bytes.
• Fixed a potential bug in 4.0.4: InnoDB now does ORDER BY ... DESC like MyISAM.
• Fixed a bug: DROP TABLE could cause crash or a hang if there was a rollback concurrently running on the table. The fix will only be backported to 3.23 if this appears a real problem for users.
• Fixed a bug: ORDER BY could fail if you had not created a primary key to a table, but had defined several indexes of which at least one was a UNIQUE index with all its columns declared as NOT NULL.
• Fixed a bug: a lock wait timeout in connection with ON DELETE CASCADE could cause corruption in indexes.
• Fixed a bug: if a SELECT was done with a unique key from a primary index, and the search matched to a delete-marked record, InnoDB could return the NEXT record.
• Outstanding bugs: in 4.0.4 two bugs were introduced to AUTO_INCREMENT. REPLACE can cause the counter to be left 1 too low. A deadlock or a lock wait timeout can cause the same problem. These will be fixed in 4.0.6.

7.5.14.13 MySQL/InnoDB-3.23.53, October 9, 2002

• We again use unbuffered disk i/o to data files in Windows. Win XP and Win 2000 read performance seems to be very poor with normal i/o.
• Tuned range estimator so that index range scans are preferred over full index scans.
• Allow dropping and creating a table even if innodb_force_recovery is set. One can use this to drop a table which would cause a crash in rollback or purge, or if a failed table import causes a runaway rollback in recovery.
• Fixed a bug present in 3.23.52, 4.0.3, 4.0.4: InnoDB startup could take very long or even crash on some Win 95/98/ME computers.
• Fixed a bug: fast shutdown (which is the default) sometimes was slowed down by purge and insert buffer merge.
• Fixed a bug: doing a big SELECT from a table where no rows were visible in a consistent read could cause a very long (> 600 seconds) semaphore wait in btr0cur.c line 310.
• Fixed a bug: the AUTO-INC lock was held to the end of the transaction if it was granted after a lock wait. This could cause unnecessary deadlocks.
• Fixed a bug: if you created a temporary table inside LOCK TABLES, and used that temporary table, that caused an assertion failure in ha_innobase.cc.
• Fixed a bug: if SHOW INNODB STATUS, innodb_monitor, or innodb_lock_monitor had to print several hundred transactions in one report, and the output became truncated, InnoDB would hang, printing to the error log many waits for a mutex created at srv0srv.c, line 1621.
• Fixed a bug: SHOW INNODB STATUS on Unix always reported average file read size as 0 bytes.

7.5.14.14 MySQL/InnoDB-4.0.4, October 2, 2002

• We again use unbuffered disk i/o in Windows. Win XP and Win 2000 read performance seems to be very poor with normal i/o.
• Increased the max key length of InnoDB tables from 500 to 1024 bytes.
• Increased the table comment field in SHOW TABLE STATUS so that up to 16000 characters of foreign key definitions can be printed there.
• The auto-increment counter is no longer incremented if an insert of a row immediately fails in an error.
• Allow dropping and creating a table even if innodb_force_recovery is set. One can use this to drop a table which would cause a crash in rollback or purge, or if a failed table import causes a runaway rollback in recovery.
• Fixed a bug: Using ORDER BY primarykey DESC in 4.0.3 causes an assertion failure in btr0pcur.c, line 203.
• Fixed a bug: fast shutdown (which is the default) sometimes was slowed down by purge and insert buffer merge.
• Fixed a bug: doing a big SELECT from a table where no rows were visible in a consistent read could cause a very long (> 600 seconds) semaphore wait in btr0cur.c line 310.
• Fixed a bug: if the MySQL query cache was used, it did not get invalidated by a modification done by ON DELETE CASCADE or ...SET NULL.
• Fixed a bug: if you created a temporary table inside LOCK TABLES, and used that temporary table, that caused an assertion failure in ha_innodb.cc.
• Fixed a bug: if you set innodb_flush_log_at_trx_commit to 1, SHOW VARIABLES would show its value as 16 million.

7.5.14.15 MySQL/InnoDB-4.0.3, August 28, 2002

• Removed unnecessary deadlocks when inserts have to wait for a locking read, update, or delete to release its next-key lock.
• The MySQL HANDLER SQL commands now work also for InnoDB type tables. InnoDB does the HANDLER reads always as consistent reads. HANDLER is a direct access path to read individual indexes of tables. In some cases HANDLER can be used as a substitute of server-side cursors.
• Fixed a bug in 4.0.2: even a simple insert could crash the AIX version.
• Fixed a bug: if you used in a table name characters whose code is > 127, in DROP TABLE InnoDB could assert on line 155 of pars0sym.c.
• Compilation from source now provides a working version both on HP-UX-11 and HP-UX-10.20. The source of 4.0.2 worked only on 11, and the source of 3.23.52 only on 10.20.
• Fixed a bug: if compiled on 64-bit Solaris, InnoDB produced a bus error at startup.

7.5.14.16 MySQL/InnoDB-3.23.52, August 16, 2002

• The feature set of 3.23 will be frozen from this version on. New features will go the 4.0 branch, and only bug fixes will be made to the 3.23 branch.
• Many CPU-bound join queries now run faster. On Windows also many other CPU-bound queries run faster.
• A new SQL command SHOW INNODB STATUS returns the output of the InnoDB Monitor to the client. The InnoDB Monitor now prints detailed info on the latest detected deadlock.
• InnoDB made the SQL query optimizer to avoid too much index-only range scans and choose full table scans instead. This is now fixed.
• "BEGIN" and "COMMIT" are now added in the binlog around transactions. The MySQL replication now respects transaction borders: a user will no longer see half transactions in replication slaves.
• A replication slave now prints in crash recovery the last master binlog position it was able to recover to.
• A new setting innodb_flush_log_at_trx_commit=2 makes InnoDB to write the log to the operating system file cache at each commit. This is almost as fast as the setting innodb_flush_log_at_trx_commit=0, and the setting 2 also has the nice feature that in a crash where the operating system does not crash, no committed transaction is lost. If the operating system crashes or there is a power outage, then the setting 2 is no safer than the setting 0.
• Added checksum fields to log blocks.
• SET FOREIGN_KEY_CHECKS=0 helps in importing tables in an arbitrary order which does not respect the foreign key rules.
• SET UNIQUE_CHECKS=0 speeds up table imports into InnoDB if you have UNIQUE constraints on secondary indexes.
• SHOW TABLE STATUS now lists also possible ON DELETE CASCADE or ON DELETE SET NULL in the comment field of the table.
• When CHECK TABLE is run on any InnoDB type table, it now checks also the adaptive hash index for all tables.
• If you defined ON DELETE CASCADE or SET NULL and updated the referenced key in the parent row, InnoDB deleted or updated the child row. This is now changed to conform to SQL-92: you get the error 'Cannot delete parent row'.
• Improved the auto-increment algorithm: now the first insert or SHOW TABLE STATUS initializes the auto-inc counter for the table. This removes almost all surprising deadlocks caused by SHOW TABLE STATUS.
• Aligned some buffers used in reading and writing to data files. This allows using unbuffered raw devices as data files in Linux.
• Fixed a bug: If you updated the primary key of a table so that only the case of characters changed, that could cause assertion failures, mostly in page0page.ic line 515.
• Fixed a bug: If you delete or update a row referenced in a foreign key constraint and the foreign key check has to wait for a lock, then the check may report an erroneous result. This affects also the ON DELETE... operation.
• Fixed a bug: A deadlock or a lock wait timeout error in InnoDB causes InnoDB to roll back the whole transaction, but MySQL could still write the earlier SQL statements to the binlog, even though InnoDB rolled them back. This could, for example, cause replicated databases to get out-of-sync.
• Fixed a bug: If the database happened to crash in the middle of a commit, then the recovery might leak tablespace pages.
• Fixed a bug: If you specified a non-latin1 character set in my.cnf, then, in contrary to what is stated in the manual, in a foreign key constraint a string type column had to have the same length specification in the referencing table and the referenced table.
• Fixed a bug: DROP TABLE or DROP DATABASE could fail if there simultaneously was a CREATE TABLE running.
• Fixed a bug: If you configured the buffer pool bigger than 2 GB in a 32-bit computer, InnoDB would assert in buf0buf.ic line 214.
• Fixed a bug: on 64-bit computers updating rows which contained the SQL NULL in some column could cause the undo log and the ordinary log to become corrupt.
• Fixed a bug: innodb_log_monitor caused a hang if it suppressed lock prints for a page.
• Fixed a bug: in the HP-UX-10.20 version mutexes would leak and cause race conditions and crashes in any part of InnoDB code.
• Fixed a bug: if you ran in the AUTOCOMMIT mode, executed a SELECT, and immediately after that a RENAME TABLE, then RENAME would fail and MySQL would complain about error 1192.
• Fixed a bug: if compiled on 64-bit Solaris, InnoDB produced a bus error at startup.

7.5.14.17 MySQL/InnoDB-4.0.2, July 10, 2002

• InnoDB is essentially the same as InnoDB-3.23.51.
• If no innodb_data_file_path is specified, InnoDB at the database creation now creates a 10 MB auto-extending data file ibdata1 to the datadir of MySQL. In 4.0.1 the file was 64 MB and not auto-extending.

7.5.14.18 MySQL/InnoDB-3.23.51, June 12, 2002

• Fixed a bug: a join could result in a seg fault in copying of a BLOB or TEXT column if some of the BLOB or TEXT columns in the table contained SQL NULL values.
• Fixed a bug: if you added self-referential foreign key constraints with ON DELETE CASCADE to tables and a row deletion caused InnoDB to attempt the deletion of the same row twice because of a cascading delete, then you got an assertion failure.
• Fixed a bug: if you use MySQL 'user level locks' and close a connection, then InnoDB may assert in ha_innobase.cc, line 302.

7.5.14.19 MySQL/InnoDB-3.23.50, April 23, 2002

• InnoDB now supports an auto-extending last data file. You do not need to preallocate the whole data file at the database startup.
• Made several changes to facilitate the use of the InnoDB Hot Backup tool. It is a separate non-free tool you can use to take online backups of your database without shutting down the server or setting any locks.
• If you want to run the InnoDB Hot Backup tool on an auto-extending data file you have to upgrade it to version ibbackup-0.35.
• The log scan phase in crash recovery will now run much faster.
• Starting from this server version, the hot backup tool truncates unused ends in the backup InnoDB data files.
• To allow the hot backup tool to work, on Windows we no longer use unbuffered i/o or native async i/o; instead we use the same simulated async i/o as on Unix.
• You can now define the ON DELETE CASCADE or ON DELETE SET NULL clause on foreign keys.
• FOREIGN KEY constraints now survive ALTER TABLE and CREATE INDEX.
• We suppress the FOREIGN KEY check if any of the column values in the foreign key or referenced key to be checked is the SQL NULL. This is compatible with Oracle, for example.
• SHOW CREATE TABLE now lists also foreign key constraints. Also mysqldump no longer forgets about foreign keys in table definitions.
• You can now add a new foreign key constraint with ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...).
• FOREIGN KEY definitions now allow backquotes around table and column names.
• MySQL command SET TRANSACTION ISOLATION LEVEL ... has now the following effect on InnoDB tables: if a transaction is defined as SERIALIZABLE then InnoDB conceptually adds LOCK IN SHARE MODE to all consistent reads. If a transaction is defined to have any other isolation level, then InnoDB obeys its default locking strategy which is REPEATABLE READ.
• SHOW TABLE STATUS no longer sets an x-lock at the end of an auto-increment index if the auto-increment counter has already been initialized. This removes in almost all cases the surprising deadlocks caused by SHOW TABLE STATUS.
• Fixed a bug: in a CREATE TABLE statement the string 'foreign' followed by a non-space character confused the FOREIGN KEY parser and caused table creation to fail with errno 150.

7.5.14.20 MySQL/InnoDB-3.23.49, February 17, 2002

• Fixed a bug: if you called DROP DATABASE for a database on which there simultaneously were running queries, the MySQL server could crash or hang. Crashes fixed, but a full fix has to wait some changes in the MySQL layer of code.
• Fixed a bug: on Windows one had to put the database name in lower case for DROP DATABASE to work. Fixed in 3.23.49: case no longer matters on Windows. On Unix the database name remains case-sensitive.
• Fixed a bug: if one defined a non-latin1 character set as the default character set, then definition of foreign key constraints could fail in an assertion failure in dict0crea.c, reporting an internal error 17.

7.5.14.21 MySQL/InnoDB-3.23.48, February 9, 2002

• Tuned the SQL optimizer to favor more often index searches over table scans.
• Fixed a performance problem when several large SELECT queries are run concurrently on a multiprocessor Linux computer. Large CPU-bound SELECT queries will now also generally run faster on all platforms.
• If MySQL binlogging is used, InnoDB now prints after crash recovery the latest MySQL binlog file name and the position in that file (= byte offset) InnoDB was able to recover to. This is useful, for example, when resynchronizing a master and a slave database in replication.
• Added better error messages to help in installation problems.
• One can now recover also MySQL temporary tables which have become orphaned inside the InnoDB tablespace.
• InnoDB now prevents a FOREIGN KEY declaration where the signedness is not the same in the referencing and referenced integer columns.
• Fixed a bug: calling SHOW CREATE TABLE or SHOW TABLE STATUS could cause memory corruption and make mysqld to crash. Especially at risk was mysqldump, because it calls frequently SHOW CREATE TABLE.
• Fixed a bug: if on Unix you did an ALTER TABLE to an InnoDB table and simultaneously did queries to it, mysqld could crash with an assertion failure in row0row.c, line 474.
• Fixed a bug: if inserts to several tables containing an auto-inc column were wrapped inside one LOCK TABLES, InnoDB asserted in lock0lock.c.
• In 3.23.47 we allowed several NULLS in a UNIQUE secondary index. But CHECK TABLE was not relaxed: it reports the table as corrupt. CHECK TABLE no longer complains in this situation.
• Fixed a bug: on Sparc and other high-endian processors SHOW VARIABLES showed innodb_flush_log_at_trx_commit and other boolean-valued startup parameters always OFF even if they were switched on.
• Fixed a bug: if you ran mysqld-max-nt as a service on Windows NT/2000, the service shutdown did not always wait long enough for the InnoDB shutdown to finish.

7.5.14.22 MySQL/InnoDB-3.23.47, December 28, 2001

• Recovery happens now faster, especially in a lightly loaded system, because background checkpointing has been made more frequent.
• InnoDB allows now several similar key values in a UNIQUE secondary index if those values contain SQL NULLs. Thus the convention is now the same as in MyISAM tables.
• InnoDB gives a better row count estimate for a table which contains BLOBs.
• In a FOREIGN KEY constraint InnoDB is now case-insensitive to column names, and in Windows also to table names.
• InnoDB allows a FOREIGN KEY column of CHAR type to refer to a column of VARCHAR type, and vice versa. MySQL silently changes the type of some columns between CHAR and VARCHAR, and these silent changes do not hinder FOREIGN KEY declaration any more.
• Recovery has been made more resilient to corruption of log files.
• Unnecessary statistics calculation has been removed from queries which generate a temporary table. Some ORDER BY and DISTINCT queries will now run much faster.
• MySQL now knows that the table scan of an InnoDB table is done through the primary key. This will save a sort in some ORDER BY queries.
• The maximum key length of InnoDB tables is again restricted to 500 bytes. The MySQL interpreter is not able to handle longer keys.
• The default value of innodb_lock_wait_timeout was changed from infinite to 50 seconds, the default value of innodb_file_io_threads from 9 to 4.

7.5.14.23 MySQL/InnoDB-4.0.1, December 23, 2001

• InnoDB is the same as in 3.23.47.
• In 4.0.0 the MySQL interpreter did not know the syntax LOCK IN SHARE MODE. This has been fixed.
• In 4.0.0 multi-table delete did not work for transactional tables. This has been fixed.

7.5.14.24 MySQL/InnoDB-3.23.46, November 30, 2001

• This is the same as 3.23.45.

7.5.14.25 MySQL/InnoDB-3.23.45, November 23, 2001

• This is a bugfix release.
• In versions 3.23.42-.44 when creating a table on Windows you have to use lower case letters in the database name to be able to access the table. Fixed in 3.23.45.
• InnoDB now flushes stdout and stderr every 10 seconds: if these are redirected to files, the file contents can be better viewed with an editor.
• Fixed an assertion failure in .44, in trx0trx.c, line 178 when you drop a table which has the .frm file but does not exist inside InnoDB.
• Fixed a bug in the insert buffer. The insert buffer tree could get into an inconsistent state, causing a crash, and also crashing the recovery. This bug could appear especially in large table imports or alterations.
• Fixed a bug in recovery: InnoDB could go into an infinite loop constantly printing a warning message that it cannot find free blocks from the buffer pool.
• Fixed a bug: when you created a temporary table of the InnoDB type, and then used ALTER TABLE to it, the MySQL server could crash.
• Prevented creation of MySQL system tables 'mysql.user', 'mysql.host', or 'mysql.db', in the InnoDB type.
• Fixed a bug which can cause an assertion failure in 3.23.44 in srv0srv.c, line 1728.

7.5.14.26 MySQL/InnoDB-3.23.44, November 2, 2001

• You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2).
• You can create > 4 GB data files in those file systems that allow it.
• Improved InnoDB monitors, including a new innodb_table_monitor which allows you to print the contents of the InnoDB internal data dictionary.
• DROP DATABASE will now work also for InnoDB tables.
• Accent characters in the default character set latin1 will be ordered according to the MySQL ordering.<br> NOTE: if you are using latin1 and have inserted characters whose code is > 127 to an indexed CHAR column, you should run CHECK TABLE on your table when you upgrade to 3.23.43, and drop and reimport the table if CHECK TABLE reports an error!
• InnoDB will calculate better table cardinality estimates.
• Change in deadlock resolution: in .43 a deadlock rolls back only the SQL statement, in .44 it will roll back the whole transaction.
• Deadlock, lock wait timeout, and foreign key constraint violations (no parent row, child rows exist) now return native MySQL error codes 1213, 1205, 1216, 1217, respectively.
• A new my.cnf parameter innodb_thread_concurrency helps in performance tuning in high concurrency environments.
• A new my.cnf option innodb_force_recovery will help you in dumping tables from a corrupted database.
• A new my.cnf option innodb_fast_shutdown will speed up shutdown. Normally InnoDB does a full purge and an insert buffer merge at shutdown.
• Raised maximum key length to 7000 bytes from a previous limit of 500 bytes.
• Fixed a bug in replication of auto-inc columns with multiline inserts.
• Fixed a bug when the case of letters changes in an update of an indexed secondary column.
• Fixed a hang when there are > 24 data files.
• Fixed a crash when MAX(col) is selected from an empty table, and col is a not the first column in a multi-column index.
• Fixed a bug in purge which could cause crashes.

7.5.14.27 MySQL/InnoDB-3.23.43, October 4, 2001

• This is essentially the same as InnoDB-3.23.42.

7.5.14.28 MySQL/InnoDB-3.23.42, September 9, 2001

• Fixed a bug which corrupted the table if the primary key of a > 8000-byte row was updated.
• There are now 3 types of InnoDB Monitors: innodb_monitor, innodb_lock_monitor, and innodb_tablespace_monitor. innodb_monitor now prints also buffer pool hit rate and the total number of rows inserted, updated, deleted, read.
• Fixed a bug in RENAME TABLE.
• Fixed a bug in replication with an auto-increment column.

7.5.14.29 MySQL/InnoDB-3.23.41, August 13, 2001

• Support for < 4 GB rows. The previous limit was 8000 bytes.
• Use the doublewrite file flush method.
• Raw disk partitions supported as data files.
• InnoDB Monitor.
• Several hang bugs fixed and an ORDER BY bug ('Sort aborted') fixed.

7.5.14.30 MySQL/InnoDB-3.23.40, July 16, 2001

• Only a few rare bugs fixed.

7.5.14.31 MySQL/InnoDB-3.23.39, June 13, 2001

• CHECK TABLE now works for InnoDB tables.
• A new my.cnf parameter innodb_unix_file_flush_method introduced. It can be used to tune disk write performance.
• An auto-increment column now gets new values past the transaction mechanism. This saves CPU time and eliminates transaction deadlocks in new value assignment.
• Several bug fixes, most notably the rollback bug in 3.23.38.

7.5.14.32 MySQL/InnoDB-3.23.38, May 12, 2001

• The new syntax SELECT ... LOCK IN SHARE MODE is introduced.
• InnoDB now calls fsync after every disk write and calculates a checksum for every database page it writes or reads, which will reveal disk defects.
• Several bug fixes.

7.5.15 InnoDB Contact Information

Contact information of Innobase Oy, producer of the InnoDB engine. Web site: http://www.innodb.com/. E-mail: Heikki.Tuuri@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

7.6 BDB or BerkeleyDB Tables

7.6.1 Overview of BDB Tables

BerkeleyDB, available at http://www.sleepycat.com/ has provided MySQL with a transactional storage engine. Support for this storage engine is included in the MySQL source distribution starting from version 3.23.34 and is activated in the MySQL-Max binary. This storage engine is typically called BDB for short.

BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions. The MySQL source distribution comes with a BDB distribution that has a couple of small patches to make it work more smoothly with MySQL. You can't use a non-patched BDB version with MySQL.

We at MySQL AB are working in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high.

When it comes to supporting BDB tables, we are committed to help our users to locate the problem and help creating a reproducible test case for any problems involving BDB tables. Any such test case will be forwarded to Sleepycat who in turn will help us find and fix the problem. As this is a two-stage operation, any problems with BDB tables may take a little longer for us to fix than for other storage engines. However, as the BerkeleyDB code itself has been used by many other applications than MySQL, we don't envision any big problems with this. See section 1.4.1 Support Offered by MySQL AB.

7.6.2 Installing BDB

If you have downloaded a binary version of MySQL that includes support for BerkeleyDB, simply follow the instructions for installing a binary version of MySQL. See section 2.2.11 Installing a MySQL Binary Distribution. See section 4.7.5 mysqld-max, An Extended mysqld Server.

To compile MySQL with Berkeley DB support, download MySQL Version 3.23.34 or newer and configure MySQL with the --with-berkeley-db option. See section 2.3 Installing a MySQL Source Distribution.

cd /path/to/source/of/mysql-3.23.34
./configure --with-berkeley-db

Please refer to the manual provided with the BDB distribution for more updated information.

Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We are actively improving and optimising it to get it stable very soon.

7.6.3 BDB startup options

If you are running with AUTOCOMMIT=0 then your changes in BDB tables will not be updated until you execute COMMIT. Instead of commit you can execute ROLLBACK to forget your changes. See section 6.7.1 BEGIN/COMMIT/ROLLBACK Syntax.

If you are running with AUTOCOMMIT=1 (the default), your changes will be committed immediately. You can start an extended transaction with the BEGIN WORK SQL command, after which your changes will not be committed until you execute COMMIT (or decide to ROLLBACK the changes).

The following options to mysqld can be used to change the behaviour of BDB tables:

If you use --skip-bdb, MySQL will not initialise the Berkeley DB library and this will save a lot of memory. Of course, you cannot use BDB tables if you are using this option. If you try to create a BDB table, MySQL will instead create a MyISAM table.

Normally you should start mysqld without --bdb-no-recover if you intend to use BDB tables. This may, however, give you problems when you try to start mysqld if the BDB log files are corrupted. See section 2.4.2 Problems Starting the MySQL Server.

With bdb_max_lock you can specify the maximum number of locks (10000 by default) you can have active on a BDB table. You should increase this if you get errors of type bdb: Lock table is out of available locks or Got error 12 from ... when you have do long transactions or when mysqld has to examine a lot of rows to calculate the query.

You may also want to change binlog_cache_size and max_binlog_cache_size if you are using big multi-line transactions. See section 6.7.1 BEGIN/COMMIT/ROLLBACK Syntax.

7.6.4 Characteristics of BDB tables:

• To be able to rollback transactions, the BDB storage engine maintains log files. For maximum performance you should place these on another disk than your databases by using the --bdb-logdir option.
• MySQL performs a checkpoint each time a new BDB log file is started, and removes any log files that are not needed for current transactions. One can also run FLUSH LOGS at any time to checkpoint the Berkeley DB tables. For disaster recovery, one should use table backups plus MySQL's binary log. See section 4.4.1 Database Backups. Warning: If you delete old log files that are in use, BDB will not be able to do recovery at all and you may lose data if something goes wrong.
• MySQL requires a PRIMARY KEY in each BDB table to be able to refer to previously read rows. If you don't create one, MySQL will create an maintain a hidden PRIMARY KEY for you. The hidden key has a length of 5 bytes and is incremented for each insert attempt.
• If all columns you access in a BDB table are part of the same index or part of the primary key, then MySQL can execute the query without having to access the actual row. In a MyISAM table the above holds only if the columns are part of the same index.
• The PRIMARY KEY will be faster than any other key, as the PRIMARY KEY is stored together with the row data. As the other keys are stored as the key data + the PRIMARY KEY, it's important to keep the PRIMARY KEY as short as possible to save disk and get better speed.
• LOCK TABLES works on BDB tables as with other tables. If you don't use LOCK TABLE, MySQL will issue an internal multiple-write lock on the table to ensure that the table will be properly locked if another thread issues a table lock.
• Internal locking in BDB tables is done on page level.
• SELECT COUNT(*) FROM table_name is slow as BDB tables doesn't maintain a count of the number of rows in the table.
• Sequential scanning is slower than with MyISAM tables as the data in BDB tables stored in B-trees and not in a separate datafile.
• The application must always be prepared to handle cases where any change of a BDB table may make an automatic rollback and any read may fail with a deadlock error.
• Keys are not prefix or suffix-compressed like keys in MyISAM tables. In other words, the key information will take a little more space in BDB tables compared to MyISAM tables.
• There are often holes in the BDB table to allow you to insert new rows in the middle of the key tree. This makes BDB tables somewhat larger than MyISAM tables.
• The optimiser needs to know an approximation of the number of rows in the table. MySQL solves this by counting inserts and maintaining this in a separate segment in each BDB table. If you don't issue a lot of DELETE or ROLLBACK statements, this number should be accurate enough for the MySQL optimiser, but as MySQL only stores the number on close, it may be incorrect if MySQL dies unexpectedly. It should not be fatal even if this number is not 100% correct. One can update the number of rows by executing ANALYZE TABLE or OPTIMIZE TABLE. See section 4.5.2 ANALYZE TABLE Syntax . See section 4.5.1 OPTIMIZE TABLE Syntax.
• If you get full disk with a BDB table, you will get an error (probably error 28) and the transaction should roll back. This is in contrast with MyISAM and ISAM tables where mysqld will wait for enough free disk before continuing.

7.6.5 Things we need to fix for BDB in the near future:

• It's very slow to open many BDB tables at the same time. If you are going to use BDB tables, you should not have a very big table cache (like >256) and you should use --no-auto-rehash with the mysql client. We plan to partly fix this in 4.0.
• SHOW TABLE STATUS doesn't yet provide that much information for BDB tables.
• Optimise performance.
• Change to not use page locks at all when we are scanning tables.

7.6.6 Operating systems supported by BDB

Currently we know that the BDB storage engine works with the following operating systems:

• Linux 2.x Intel
• Solaris SPARC
• Caldera (SCO) OpenServer
• Caldera (SCO) UnixWare 7.0.1

It doesn't work with the following operating systems:

• Linux 2.x Alpha
• Max OS X

Note: The above list is not complete; we will update it as we receive more information.

If you build MySQL with support for BDB tables and get the following error in the log file when you start mysqld:

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init dtabases

This means that BDB tables are not supported for your architecture. In this case you must rebuild MySQL without BDB table support.

7.6.7 Restrictions on BDB Tables

Here follows the restrictions you have when using BDB tables:

• BDB tables store in the `.db' file the path to the file as it was created. (This was done to be able to detect locks in a multi-user environment that supports symlinks). The effect of this is that BDB tables are not movable between directories!
• When taking backups of BDB tables, you have to either use mysqldump or take a backup of all table_name.db files and the BDB log files. The BDB log files are the files in the base data directory named log.XXXXXXXXXX (ten digits); The BDB storage engine stores unfinished transactions in the log files and requires these logs to be present when mysqld starts.

7.6.8 Errors That May Occur When Using BDB Tables

• If you get the following error in the hostname.err log when starting mysqld:

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

  it means that the new BDB version doesn't support the old log file format. In this case you have to delete all BDB logs from your database directory (the files with names that have the format log.XXXXXXXXXX) and restart mysqld. We would also recommend you to do a mysqldump --opt of your old BDB tables, delete the old tables, and restore the dump.
• If you are not running in auto-commit mode and delete a table that is referenced in another transaction, you may get the following error messages in your MySQL error log:

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

  This is not fatal but we don't recommend that you delete tables if you are not in auto-commit mode, until this problem is fixed (the fix is not trivial).

8 MySQL APIs

This chapter describes the APIs available for MySQL, where to get them, and how to use them. The C API is the most extensively covered, as it was developed by the MySQL team, and is the basis for most of the other APIs.

8.1 MySQL C API

The C API code is distributed with MySQL. It is included in the mysqlclient library and allows C programs to access a database.

Many of the clients in the MySQL source distribution are written in C. If you are looking for examples that demonstrate how to use the C API, take a look at these clients. You can find these in the clients directory in the MySQL source distribution.

Most of the other client APIs (all except Connector/J) use the mysqlclient library to communicate with the MySQL server. This means that, for example, you can take advantage of many of the same environment variables that are used by other client programs, because they are referenced from the library. See section 4.8 MySQL Client-Side Scripts and Utilities, for a list of these variables.

The client has a maximum communication buffer size. The size of the buffer that is allocated initially (16K bytes) is automatically increased up to the maximum size (the maximum is 16M). Because buffer sizes are increased only as demand warrants, simply increasing the default maximum limit does not in itself cause more resources to be used. This size check is mostly a check for erroneous queries and communication packets.

The communication buffer must be large enough to contain a single SQL statement (for client-to-server traffic) and one row of returned data (for server-to-client traffic). Each thread's communication buffer is dynamically enlarged to handle any query or row up to the maximum limit. For example, if you have BLOB values that contain up to 16M of data, you must have a communication buffer limit of at least 16M (in both server and client). The client's default maximum is 16M, but the default maximum in the server is 1M. You can increase this by changing the value of the max_allowed_packet parameter when the server is started. See section 5.5.2 Tuning Server Parameters.

The MySQL server shrinks each communication buffer to net_buffer_length bytes after each query. For clients, the size of the buffer associated with a connection is not decreased until the connection is closed, at which time client memory is reclaimed.

For programming with threads, see section 8.1.14 How to Make a Threaded Client. For creating a stand-alone application which includes the "server" and "client" in the same program (and does not communicate with an external MySQL server), see section 8.1.15 libmysqld, the Embedded MySQL Server Library.

8.1.1 C API Datatypes

MYSQL

This structure represents a handle to one database connection. It is used for almost all MySQL functions.

MYSQL_RES

This structure represents the result of a query that returns rows (SELECT, SHOW, DESCRIBE, EXPLAIN). The information returned from a query is called the result set in the remainder of this section.

MYSQL_ROW

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.) Rows are obtained by calling mysql_fetch_row().

MYSQL_FIELD

This structure contains information about a field, such as the field's name, type, and size. Its members are described in more detail here. You may obtain the MYSQL_FIELD structures for each field by calling mysql_fetch_field() repeatedly. Field values are not part of this structure; they are contained in a MYSQL_ROW structure.

MYSQL_FIELD_OFFSET

This is a type-safe representation of an offset into a MySQL field list. (Used by mysql_field_seek().) Offsets are field numbers within a row, beginning at zero.

my_ulonglong

The type used for the number of rows and for mysql_affected_rows(), mysql_num_rows(), and mysql_insert_id(). This type provides a range of 0 to 1.84e19. On some systems, attempting to print a value of type my_ulonglong will not work. To print such a value, convert it to unsigned long and use a %lu print format. Example:

printf (Number of rows: %lu\n", (unsigned long) mysql_num_rows(result));

The MYSQL_FIELD structure contains the members listed here:

char * name

The name of the field, as a null-terminated string.

char * table

The name of the table containing this field, if it isn't a calculated field. For calculated fields, the table value is an empty string.

char * def

The default value of this field, as a null-terminated string. This is set only if you use mysql_list_fields().

enum enum_field_types type

The type of the field. The type value may be one of the following:

Type value	Type description
FIELD_TYPE_TINY	TINYINT field
FIELD_TYPE_SHORT	SMALLINT field
FIELD_TYPE_LONG	INTEGER field
FIELD_TYPE_INT24	MEDIUMINT field
FIELD_TYPE_LONGLONG	BIGINT field
FIELD_TYPE_DECIMAL	DECIMAL or NUMERIC field
FIELD_TYPE_FLOAT	FLOAT field
FIELD_TYPE_DOUBLE	DOUBLE or REAL field
FIELD_TYPE_TIMESTAMP	TIMESTAMP field
FIELD_TYPE_DATE	DATE field
FIELD_TYPE_TIME	TIME field
FIELD_TYPE_DATETIME	DATETIME field
FIELD_TYPE_YEAR	YEAR field
FIELD_TYPE_STRING	String (CHAR or VARCHAR) field
FIELD_TYPE_BLOB	BLOB or TEXT field (use max_length to determine the maximum length)
FIELD_TYPE_SET	SET field
FIELD_TYPE_ENUM	ENUM field
FIELD_TYPE_NULL	NULL-type field
FIELD_TYPE_CHAR	Deprecated; use FIELD_TYPE_TINY instead

You can use the IS_NUM() macro to test whether a field has a numeric type. Pass the type value to IS_NUM() and it will evaluate to TRUE if the field is numeric:

if (IS_NUM(field->type))
    printf("Field is numeric\n");

unsigned int length

The width of the field, as specified in the table definition.

unsigned int max_length

The maximum width of the field for the result set (the length of the longest field value for the rows actually in the result set). If you use mysql_store_result() or mysql_list_fields(), this contains the maximum length for the field. If you use mysql_use_result(), the value of this variable is zero.

unsigned int flags

Different bit-flags for the field. The flags value may have zero or more of the following bits set:

Flag value	Flag description
NOT_NULL_FLAG	Field can't be NULL
PRI_KEY_FLAG	Field is part of a primary key
UNIQUE_KEY_FLAG	Field is part of a unique key
MULTIPLE_KEY_FLAG	Field is part of a non-unique key
UNSIGNED_FLAG	Field has the UNSIGNED attribute
ZEROFILL_FLAG	Field has the ZEROFILL attribute
BINARY_FLAG	Field has the BINARY attribute
AUTO_INCREMENT_FLAG	Field has the AUTO_INCREMENT attribute
ENUM_FLAG	Field is an ENUM (deprecated)
SET_FLAG	Field is a SET (deprecated)
BLOB_FLAG	Field is a BLOB or TEXT (deprecated)
TIMESTAMP_FLAG	Field is a TIMESTAMP (deprecated)

Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to test field->type against FIELD_TYPE_BLOB, FIELD_TYPE_ENUM, FIELD_TYPE_SET, or FIELD_TYPE_TIMESTAMP instead. The following example illustrates a typical use of the flags value:

if (field->flags & NOT_NULL_FLAG)
    printf("Field can't be null\n");

You may use the following convenience macros to determine the boolean status of the flags value:

Flag status	Description
IS_NOT_NULL(flags)	True if this field is defined as NOT NULL
IS_PRI_KEY(flags)	True if this field is a primary key
IS_BLOB(flags)	True if this field is a BLOB or TEXT (deprecated; test field->type instead)

unsigned int decimals

The number of decimals for numeric fields.

8.1.2 C API Function Overview

The functions available in the C API are listed here and are described in greater detail in a later section. See section 8.1.3 C API Function Descriptions.

To connect to the server, call mysql_init() to initialise a connection handler, then call mysql_real_connect() with that handler (along with other information such as the hostname, user name, and password). Upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up. When you are done with the connection, call mysql_close() to terminate it.

While a connection is active, the client may send SQL queries to the server using mysql_query() or mysql_real_query(). The difference between the two is that mysql_query() expects the query to be specified as a null-terminated string whereas mysql_real_query() expects a counted string. If the string contains binary data (which may include null bytes), you must use mysql_real_query().

For each non-SELECT query (for example, INSERT, UPDATE, DELETE), you can find out how many rows were changed (affected) by calling mysql_affected_rows().

For SELECT queries, you retrieve the selected rows as a result set. (Note that some statements are SELECT-like in that they return rows. These include SHOW, DESCRIBE, and EXPLAIN. They should be treated the same way as SELECT statements.)

There are two ways for a client to process result sets. One way is to retrieve the entire result set all at once by calling mysql_store_result(). This function acquires from the server all the rows returned by the query and stores them in the client. The second way is for the client to initiate a row-by-row result set retrieval by calling mysql_use_result(). This function initialises the retrieval, but does not actually get any rows from the server.

In both cases, you access rows by calling mysql_fetch_row(). With mysql_store_result(), mysql_fetch_row() accesses rows that have already been fetched from the server. With mysql_use_result(), mysql_fetch_row() actually retrieves the row from the server. Information about the size of the data in each row is available by calling mysql_fetch_lengths().

After you are done with a result set, call mysql_free_result() to free the memory used for it.

The two retrieval mechanisms are complementary. Client programs should choose the approach that is most appropriate for their requirements. In practice, clients tend to use mysql_store_result() more commonly.

An advantage of mysql_store_result() is that because the rows have all been fetched to the client, you not only can access rows sequentially, you can move back and forth in the result set using mysql_data_seek() or mysql_row_seek() to change the current row position within the result set. You can also find out how many rows there are by calling mysql_num_rows(). On the other hand, the memory requirements for mysql_store_result() may be very high for large result sets and you are more likely to encounter out-of-memory conditions.

An advantage of mysql_use_result() is that the client requires less memory for the result set because it maintains only one row at a time (and because there is less allocation overhead, mysql_use_result() can be faster). Disadvantages are that you must process each row quickly to avoid tying up the server, you don't have random access to rows within the result set (you can only access rows sequentially), and you don't know how many rows are in the result set until you have retrieved them all. Furthermore, you must retrieve all the rows even if you determine in mid-retrieval that you've found the information you were looking for.

The API makes it possible for clients to respond appropriately to queries (retrieving rows only as necessary) without knowing whether or not the query is a SELECT. You can do this by calling mysql_store_result() after each mysql_query() (or mysql_real_query()). If the result set call succeeds, the query was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determine whether a result was actually to be expected. If mysql_field_count() returns zero, the query returned no data (indicating that it was an INSERT, UPDATE, DELETE, etc.), and was not expected to return rows. If mysql_field_count() is non-zero, the query should have returned rows, but didn't. This indicates that the query was a SELECT that failed. See the description for mysql_field_count() for an example of how this can be done.

Both mysql_store_result() and mysql_use_result() allow you to obtain information about the fields that make up the result set (the number of fields, their names and types, etc.). You can access field information sequentially within the row by calling mysql_fetch_field() repeatedly, or by field number within the row by calling mysql_fetch_field_direct(). The current field cursor position may be changed by calling mysql_field_seek(). Setting the field cursor affects subsequent calls to mysql_fetch_field(). You can also get information for fields all at once by calling mysql_fetch_fields().

For detecting and reporting errors, MySQL provides access to error information by means of the mysql_errno() and mysql_error() functions. These return the error code or error message for the most recently invoked function that can succeed or fail, allowing you to determine when an error occurred and what it was.

8.1.3 C API Function Descriptions

In the descriptions here, a parameter or return value of NULL means NULL in the sense of the C programming language, not a MySQL NULL value.

Functions that return a value generally return a pointer or an integer. Unless specified otherwise, functions returning a pointer return a non-NULL value to indicate success or a NULL value to indicate an error, and functions returning an integer return zero to indicate success or non-zero to indicate an error. Note that ``non-zero'' means just that. Unless the function description says otherwise, do not test against a value other than zero:

if (result)                   /* correct */
    ... error ...

if (result < 0)               /* incorrect */
    ... error ...

if (result == -1)             /* incorrect */
    ... error ...

When a function returns an error, the Errors subsection of the function description lists the possible types of errors. You can find out which of these occurred by calling mysql_errno(). A string representation of the error may be obtained by calling mysql_error().

8.1.3.1 mysql_affected_rows()

my_ulonglong mysql_affected_rows(MYSQL *mysql)

8.1.3.2 Description

Returns the number of rows changed by the last UPDATE, deleted by the last DELETE or inserted by the last INSERT statement. May be called immediately after mysql_query() for UPDATE, DELETE, or INSERT statements. For SELECT statements, mysql_affected_rows() works like mysql_num_rows().

8.1.3.3 Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows() was called prior to calling mysql_store_result().

8.1.3.4 Errors

None.

8.1.3.5 Example

mysql_query(&mysql,"UPDATE products SET cost=cost*1.25 WHERE group=10");
printf("%ld products updated",(long) mysql_affected_rows(&mysql));

If one specifies the flag CLIENT_FOUND_ROWS when connecting to mysqld, mysql_affected_rows() will return the number of rows matched by the WHERE statement for UPDATE statements.

Note that when one uses a REPLACE command, mysql_affected_rows() will return 2 if the new row replaced and old row. This is because in this case one row was inserted after the duplicate was deleted.

8.1.3.6 mysql_change_user()

my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

8.1.3.7 Description

Changes the user and causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.

This function was introduced in MySQL Version 3.23.3.

mysql_change_user() fails unless the connected user can be authenticated or if he doesn't have permission to use the database. In this case the user and database are not changed

The db parameter may be set to NULL if you don't want to have a default database.

Starting from MySQL 4.0.6 this command will always ROLLBACK any active transactions, close all temporary tables, unlock all locked tables and reset the state as if one had done a new connect. This will happen even if the user didn't change.

8.1.3.8 Return Values

Zero for success. Non-zero if an error occurred.

8.1.3.9 Errors

The same that you can get from mysql_real_connect().

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

ER_UNKNOWN_COM_ERROR

The MySQL server doesn't implement this command (probably an old server)

ER_ACCESS_DENIED_ERROR

The user or password was wrong.

ER_BAD_DB_ERROR

The database didn't exist.

ER_DBACCESS_DENIED_ERROR

The user did not have access rights to the database.

ER_WRONG_DB_NAME

The database name was too long.

8.1.3.10 Example

if (mysql_change_user(&mysql, "user", "password", "new_database"))
{
   fprintf(stderr, "Failed to change user.  Error: %s\n",
           mysql_error(&mysql));
}

8.1.3.11 mysql_character_set_name()

const char *mysql_character_set_name(MYSQL *mysql)

8.1.3.12 Description

Returns the default character set for the current connection.

8.1.3.13 Return Values

The default character set

8.1.3.14 Errors

None.

8.1.3.15 mysql_close()

void mysql_close(MYSQL *mysql)

8.1.3.16 Description

Closes a previously opened connection. mysql_close() also deallocates the connection handle pointed to by mysql if the handle was allocated automatically by mysql_init() or mysql_connect().

8.1.3.17 Return Values

None.

8.1.3.18 Errors

None.

8.1.3.19 mysql_connect()

MYSQL *mysql_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd)

8.1.3.20 Description

This function is deprecated. It is preferable to use mysql_real_connect() instead.

mysql_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info().

The meanings of the parameters are the same as for the corresponding parameters for mysql_real_connect() with the difference that the connection parameter may be NULL. In this case the C API allocates memory for the connection structure automatically and frees it when you call mysql_close(). The disadvantage of this approach is that you can't retrieve an error message if the connection fails. (To get error information from mysql_errno() or mysql_error(), you must provide a valid MYSQL pointer.)

8.1.3.21 Return Values

Same as for mysql_real_connect().

8.1.3.22 Errors

Same as for mysql_real_connect().

8.1.3.23 mysql_create_db()

int mysql_create_db(MYSQL *mysql, const char *db)

8.1.3.24 Description

Creates the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue a SQL CREATE DATABASE statement instead.

8.1.3.25 Return Values

Zero if the database was created successfully. Non-zero if an error occurred.

8.1.3.26 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.27 Example

if(mysql_create_db(&mysql, "my_database"))
{
   fprintf(stderr, "Failed to create new database.  Error: %s\n",
           mysql_error(&mysql));
}

8.1.3.28 mysql_data_seek()

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset)

8.1.3.29 Description

Seeks to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_data_seek() may be used in conjunction only with mysql_store_result(), not with mysql_use_result().

The offset should be a value in the range from 0 to mysql_num_rows(result)-1.

8.1.3.30 Return Values

None.

8.1.3.31 Errors

None.

8.1.3.32 mysql_debug()

void mysql_debug(const char *debug)

8.1.3.33 Description

Does a DBUG_PUSH with the given string. mysql_debug() uses the Fred Fish debug library. To use this function, you must compile the client library to support debugging. See section E.1 Debugging a MySQL server. See section E.2 Debugging a MySQL client.

8.1.3.34 Return Values

None.

8.1.3.35 Errors

None.

8.1.3.36 Example

The call shown here causes the client library to generate a trace file in `/tmp/client.trace' on the client machine:

mysql_debug("d:t:O,/tmp/client.trace");

8.1.3.37 mysql_drop_db()

int mysql_drop_db(MYSQL *mysql, const char *db)

8.1.3.38 Description

Drops the database named by the db parameter.

This function is deprecated. It is preferable to use mysql_query() to issue a SQL DROP DATABASE statement instead.

8.1.3.39 Return Values

Zero if the database was dropped successfully. Non-zero if an error occurred.

8.1.3.40 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.41 Example

if(mysql_drop_db(&mysql, "my_database"))
  fprintf(stderr, "Failed to drop the database: Error: %s\n",
          mysql_error(&mysql));

8.1.3.42 mysql_dump_debug_info()

int mysql_dump_debug_info(MYSQL *mysql)

8.1.3.43 Description

Instructs the server to write some debug information to the log. For this to work, the connected user must have the SUPER privilege.

8.1.3.44 Return Values

Zero if the command was successful. Non-zero if an error occurred.

8.1.3.45 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.46 mysql_eof()

my_bool mysql_eof(MYSQL_RES *result)

8.1.3.47 Description

This function is deprecated. mysql_errno() or mysql_error() may be used instead.

mysql_eof() determines whether the last row of a result set has been read.

If you acquire a result set from a successful call to mysql_store_result(), the client receives the entire set in one operation. In this case, a NULL return from mysql_fetch_row() always means the end of the result set has been reached and it is unnecessary to call mysql_eof(). When used with mysql_store_result(), mysql_eof() will always return true.

On the other hand, if you use mysql_use_result() to initiate a result set retrieval, the rows of the set are obtained from the server one by one as you call mysql_fetch_row() repeatedly. Because an error may occur on the connection during this process, a NULL return value from mysql_fetch_row() does not necessarily mean the end of the result set was reached normally. In this case, you can use mysql_eof() to determine what happened. mysql_eof() returns a non-zero value if the end of the result set was reached and zero if an error occurred.

Historically, mysql_eof() predates the standard MySQL error functions mysql_errno() and mysql_error(). Because those error functions provide the same information, their use is preferred over mysql_eof(), which is now deprecated. (In fact, they provide more information, because mysql_eof() returns only a boolean value whereas the error functions indicate a reason for the error when one occurs.)

8.1.3.48 Return Values

Zero if no error occurred. Non-zero if the end of the result set has been reached.

8.1.3.49 Errors

None.

8.1.3.50 Example

The following example shows how you might use mysql_eof():

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(!mysql_eof(result))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}

However, you can achieve the same effect with the standard MySQL error functions:

mysql_query(&mysql,"SELECT * FROM some_table");
result = mysql_use_result(&mysql);
while((row = mysql_fetch_row(result)))
{
    // do something with data
}
if(mysql_errno(&mysql))  // mysql_fetch_row() failed due to an error
{
    fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
}

8.1.3.51 mysql_errno()

unsigned int mysql_errno(MYSQL *mysql)

8.1.3.52 Description

For the connection specified by mysql, mysql_errno() returns the error code for the most recently invoked API function that can succeed or fail. A return value of zero means that no error occurred. Client error message numbers are listed in the MySQL `errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file `Docs/mysqld_error.txt'.

8.1.3.53 Return Values

An error code value. Zero if no error occurred.

8.1.3.54 Errors

None.

8.1.3.55 mysql_error()

char *mysql_error(MYSQL *mysql)

8.1.3.56 Description

For the connection specified by mysql, mysql_error() returns the error message for the most recently invoked API function that can succeed or fail. An empty string ("") is returned if no error occurred. This means the following two tests are equivalent:

if(mysql_errno(&mysql))
{
    // an error occurred
}

if(mysql_error(&mysql)[0] != '\0')
{
    // an error occurred
}

The language of the client error messages may be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages. See section 4.6.2 Non-English Error Messages.

8.1.3.57 Return Values

A character string that describes the error. An empty string if no error occurred.

8.1.3.58 Errors

None.

8.1.3.59 mysql_escape_string()

You should use mysql_real_escape_string() instead!

This function is identical to mysql_real_escape_string() except that mysql_real_escape_string() takes a connection handler as its first argument and escapes the string according to the current character set. mysql_escape_string() does not take a connection argument and does not respect the current charset setting.

8.1.3.60 mysql_fetch_field()

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result)

8.1.3.61 Description

Returns the definition of one column of a result set as a MYSQL_FIELD structure. Call this function repeatedly to retrieve information about all columns in the result set. mysql_fetch_field() returns NULL when no more fields are left.

mysql_fetch_field() is reset to return information about the first field each time you execute a new SELECT query. The field returned by mysql_fetch_field() is also affected by calls to mysql_field_seek().

If you've called mysql_query() to perform a SELECT on a table but have not called mysql_store_result(), MySQL returns the default blob length (8K bytes) if you call mysql_fetch_field() to ask for the length of a BLOB field. (The 8K size is chosen because MySQL doesn't know the maximum length for the BLOB. This should be made configurable sometime.) Once you've retrieved the result set, field->max_length contains the length of the largest value for this column in the specific query.

8.1.3.62 Return Values

The MYSQL_FIELD structure for the current column. NULL if no columns are left.

8.1.3.63 Errors

None.

8.1.3.64 Example

MYSQL_FIELD *field;

while((field = mysql_fetch_field(result)))
{
    printf("field name %s\n", field->name);
}

8.1.3.65 mysql_fetch_fields()

MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *result)

8.1.3.66 Description

Returns an array of all MYSQL_FIELD structures for a result set. Each structure provides the field definition for one column of the result set.

8.1.3.67 Return Values

An array of MYSQL_FIELD structures for all columns of a result set.

8.1.3.68 Errors

None.

8.1.3.69 Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *fields;

num_fields = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for(i = 0; i < num_fields; i++)
{
   printf("Field %u is %s\n", i, fields[i].name);
}

8.1.3.70 mysql_fetch_field_direct()

MYSQL_FIELD *mysql_fetch_field_direct(MYSQL_RES *result, unsigned int fieldnr)

8.1.3.71 Description

Given a field number fieldnr for a column within a result set, returns that column's field definition as a MYSQL_FIELD structure. You may use this function to retrieve the definition for an arbitrary column. The value of fieldnr should be in the range from 0 to mysql_num_fields(result)-1.

8.1.3.72 Return Values

The MYSQL_FIELD structure for the specified column.

8.1.3.73 Errors

None.

8.1.3.74 Example

unsigned int num_fields;
unsigned int i;
MYSQL_FIELD *field;

num_fields = mysql_num_fields(result);
for(i = 0; i < num_fields; i++)
{
    field = mysql_fetch_field_direct(result, i);
    printf("Field %u is %s\n", i, field->name);
}

8.1.3.75 mysql_fetch_lengths()

unsigned long *mysql_fetch_lengths(MYSQL_RES *result)

8.1.3.76 Description

Returns the lengths of the columns of the current row within a result set. If you plan to copy field values, this length information is also useful for optimisation, because you can avoid calling strlen(). In addition, if the result set contains binary data, you must use this function to determine the size of the data, because strlen() returns incorrect results for any field containing null characters.

The length for empty columns and for columns containing NULL values is zero. To see how to distinguish these two cases, see the description for mysql_fetch_row().

8.1.3.77 Return Values

An array of unsigned long integers representing the size of each column (not including any terminating null characters). NULL if an error occurred.

8.1.3.78 Errors

mysql_fetch_lengths() is valid only for the current row of the result set. It returns NULL if you call it before calling mysql_fetch_row() or after retrieving all rows in the result.

8.1.3.79 Example

MYSQL_ROW row;
unsigned long *lengths;
unsigned int num_fields;
unsigned int i;

row = mysql_fetch_row(result);
if (row)
{
    num_fields = mysql_num_fields(result);
    lengths = mysql_fetch_lengths(result);
    for(i = 0; i < num_fields; i++)
    {
         printf("Column %u is %lu bytes in length.\n", i, lengths[i]);
    }
}

8.1.3.80 mysql_fetch_row()

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)

8.1.3.81 Description

Retrieves the next row of a result set. When used after mysql_store_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve. When used after mysql_use_result(), mysql_fetch_row() returns NULL when there are no more rows to retrieve or if an error occurred.

The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

The lengths of the field values in the row may be obtained by calling mysql_fetch_lengths(). Empty fields and fields containing NULL both have length 0; you can distinguish these by checking the pointer for the field value. If the pointer is NULL, the field is NULL; otherwise, the field is empty.

8.1.3.82 Return Values

A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred.

8.1.3.83 Errors

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.84 Example

MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;

num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
   unsigned long *lengths;
   lengths = mysql_fetch_lengths(result);
   for(i = 0; i < num_fields; i++)
   {
       printf("[%.*s] ", (int) lengths[i], row[i] ? row[i] : "NULL");
   }
   printf("\n");
}

8.1.3.85 mysql_field_count()

unsigned int mysql_field_count(MYSQL *mysql)

If you are using a version of MySQL earlier than Version 3.22.24, you should use unsigned int mysql_num_fields(MYSQL *mysql) instead.

8.1.3.86 Description

Returns the number of columns for the most recent query on the connection.

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

See section 8.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

8.1.3.87 Return Values

An unsigned integer representing the number of fields in a result set.

8.1.3.88 Errors

None.

8.1.3.89 Example

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;

if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if(mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
        else // mysql_store_result() should have returned data
        {
            fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
        }
    }
}

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case, you are checking directly for an error from mysql_store_result() rather than inferring from the value of mysql_field_count() whether the statement was a SELECT.

8.1.3.90 mysql_field_seek()

MYSQL_FIELD_OFFSET mysql_field_seek(MYSQL_RES *result, MYSQL_FIELD_OFFSET offset)

8.1.3.91 Description

Sets the field cursor to the given offset. The next call to mysql_fetch_field() will retrieve the field definition of the column associated with that offset.

To seek to the beginning of a row, pass an offset value of zero.

8.1.3.92 Return Values

The previous value of the field cursor.

8.1.3.93 Errors

None.

8.1.3.94 mysql_field_tell()

MYSQL_FIELD_OFFSET mysql_field_tell(MYSQL_RES *result)

8.1.3.95 Description

Returns the position of the field cursor used for the last mysql_fetch_field(). This value can be used as an argument to mysql_field_seek().

8.1.3.96 Return Values

The current offset of the field cursor.

8.1.3.97 Errors

None.

8.1.3.98 mysql_free_result()

void mysql_free_result(MYSQL_RES *result)

8.1.3.99 Description

Frees the memory allocated for a result set by mysql_store_result(), mysql_use_result(), mysql_list_dbs(), etc. When you are done with a result set, you must free the memory it uses by calling mysql_free_result().

8.1.3.100 Return Values

None.

8.1.3.101 Errors

None.

8.1.3.102 mysql_get_client_info()

char *mysql_get_client_info(void)

8.1.3.103 Description

Returns a string that represents the client library version.

8.1.3.104 Return Values

A character string that represents the MySQL client library version.

8.1.3.105 Errors

None.

8.1.3.106 mysql_get_server_version()

unsigned long mysql_get_server_version(MYSQL *mysql)

8.1.3.107 Description

Returns version number of server as an integer (new in 4.1).

8.1.3.108 Return Values

A number that represents the MySQL server version in format:

main_version*10000 + minor_version *100 + sub_version

For example, 4.1.0 is returned as 40100.

This is useful to quickly determine the version of the server in a client program to know if some capability exits.

8.1.3.109 Errors

None.

8.1.3.110 mysql_get_host_info()

char *mysql_get_host_info(MYSQL *mysql)

8.1.3.111 Description

Returns a string describing the type of connection in use, including the server host name.

8.1.3.112 Return Values

A character string representing the server host name and the connection type.

8.1.3.113 Errors

None.

8.1.3.114 mysql_get_proto_info()

unsigned int mysql_get_proto_info(MYSQL *mysql)

8.1.3.115 Description

Returns the protocol version used by current connection.

8.1.3.116 Return Values

An unsigned integer representing the protocol version used by the current connection.

8.1.3.117 Errors

None.

8.1.3.118 mysql_get_server_info()

char *mysql_get_server_info(MYSQL *mysql)

8.1.3.119 Description

Returns a string that represents the server version number.

8.1.3.120 Return Values

A character string that represents the server version number.

8.1.3.121 Errors

None.

8.1.3.122 mysql_info()

char *mysql_info(MYSQL *mysql)

8.1.3.123 Description

Retrieves a string providing information about the most recently executed query, but only for the statements listed here. For other statements, mysql_info() returns NULL. The format of the string varies depending on the type of query, as described here. The numbers are illustrative only; the string will contain values appropriate for the query.

INSERT INTO ... SELECT ...

String format: Records: 100 Duplicates: 0 Warnings: 0

INSERT INTO ... VALUES (...),(...),(...)...

String format: Records: 3 Duplicates: 0 Warnings: 0

LOAD DATA INFILE ...

String format: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

ALTER TABLE

String format: Records: 3 Duplicates: 0 Warnings: 0

UPDATE

String format: Rows matched: 40 Changed: 40 Warnings: 0

Note that mysql_info() returns a non-NULL value for the INSERT ... VALUES statement only if multiple value lists are specified in the statement.

8.1.3.124 Return Values

A character string representing additional information about the most recently executed query. NULL if no information is available for the query.

8.1.3.125 Errors

None.

8.1.3.126 mysql_init()

MYSQL *mysql_init(MYSQL *mysql)

8.1.3.127 Description

Allocates or initialises a MYSQL object suitable for mysql_real_connect(). If mysql is a NULL pointer, the function allocates, initialises, and returns a new object. Otherwise, the object is initialised and the address of the object is returned. If mysql_init() allocates a new object, it will be freed when mysql_close() is called to close the connection.

8.1.3.128 Return Values

An initialised MYSQL* handle. NULL if there was insufficient memory to allocate a new object.

8.1.3.129 Errors

In case of insufficient memory, NULL is returned.

8.1.3.130 mysql_insert_id()

my_ulonglong mysql_insert_id(MYSQL *mysql)

8.1.3.131 Description

Returns the ID generated for an AUTO_INCREMENT column by the previous query. Use this function after you have performed an INSERT query into a table that contains an AUTO_INCREMENT field.

Note that mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the query that generates the value.

mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column value to LAST_INSERT_ID(expr). See section 6.3.6.2 Miscellaneous Functions.

Also note that the value of the SQL LAST_INSERT_ID() function always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries because the value of that function is maintained in the server.

8.1.3.132 Return Values

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

8.1.3.133 Errors

None.

8.1.3.134 mysql_kill()

int mysql_kill(MYSQL *mysql, unsigned long pid)

8.1.3.135 Description

Asks the server to kill the thread specified by pid.

8.1.3.136 Return Values

Zero for success. Non-zero if an error occurred.

8.1.3.137 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.138 mysql_list_dbs()

MYSQL_RES *mysql_list_dbs(MYSQL *mysql, const char *wild)

8.1.3.139 Description

Returns a result set consisting of database names on the server that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all databases. Calling mysql_list_dbs() is similar to executing the query SHOW databases [LIKE wild].

You must free the result set with mysql_free_result().

8.1.3.140 Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

8.1.3.141 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.142 mysql_list_fields()

MYSQL_RES *mysql_list_fields(MYSQL *mysql, const char *table, const char *wild)

8.1.3.143 Description

Returns a result set consisting of field names in the given table that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all fields. Calling mysql_list_fields() is similar to executing the query SHOW COLUMNS FROM tbl_name [LIKE wild].

Note that it's recommended that you use SHOW COLUMNS FROM tbl_name instead of mysql_list_fields().

You must free the result set with mysql_free_result().

8.1.3.144 Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

8.1.3.145 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.146 mysql_list_processes()

MYSQL_RES *mysql_list_processes(MYSQL *mysql)

8.1.3.147 Description

Returns a result set describing the current server threads. This is the same kind of information as that reported by mysqladmin processlist or a SHOW PROCESSLIST query.

You must free the result set with mysql_free_result().

8.1.3.148 Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

8.1.3.149 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.150 mysql_list_tables()

MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)

8.1.3.151 Description

Returns a result set consisting of table names in the current database that match the simple regular expression specified by the wild parameter. wild may contain the wildcard characters `%' or `_', or may be a NULL pointer to match all tables. Calling mysql_list_tables() is similar to executing the query SHOW tables [LIKE wild].

You must free the result set with mysql_free_result().

8.1.3.152 Return Values

A MYSQL_RES result set for success. NULL if an error occurred.

8.1.3.153 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.154 mysql_num_fields()

unsigned int mysql_num_fields(MYSQL_RES *result)

or

unsigned int mysql_num_fields(MYSQL *mysql)

The second form doesn't work on MySQL Version 3.22.24 or newer. To pass a MYSQL* argument, you must use unsigned int mysql_field_count(MYSQL *mysql) instead.

8.1.3.155 Description

Returns the number of columns in a result set.

Note that you can get the number of columns either from a pointer to a result set or to a connection handle. You would use the connection handle if mysql_store_result() or mysql_use_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a non-empty result. This allows the client program to take proper action without knowing whether or not the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

See section 8.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

8.1.3.156 Return Values

An unsigned integer representing the number of fields in a result set.

8.1.3.157 Errors

None.

8.1.3.158 Example

MYSQL_RES *result;
unsigned int num_fields;
unsigned int num_rows;

if (mysql_query(&mysql,query_string))
{
    // error
}
else // query succeeded, process any data returned by it
{
    result = mysql_store_result(&mysql);
    if (result)  // there are rows
    {
        num_fields = mysql_num_fields(result);
        // retrieve rows, then call mysql_free_result(result)
    }
    else  // mysql_store_result() returned nothing; should it have?
    {
        if (mysql_errno(&mysql))
	{
           fprintf(stderr, "Error: %s\n", mysql_error(&mysql));
	}
        else if (mysql_field_count(&mysql) == 0)
        {
            // query does not return data
            // (it was not a SELECT)
            num_rows = mysql_affected_rows(&mysql);
        }
    }
}

An alternative (if you know that your query should have returned a result set) is to replace the mysql_errno(&mysql) call with a check if mysql_field_count(&mysql) is = 0. This will only happen if something went wrong.

8.1.3.159 mysql_num_rows()

my_ulonglong mysql_num_rows(MYSQL_RES *result)

8.1.3.160 Description

Returns the number of rows in the result set.

The use of mysql_num_rows() depends on whether you use mysql_store_result() or mysql_use_result() to return the result set. If you use mysql_store_result(), mysql_num_rows() may be called immediately. If you use mysql_use_result(), mysql_num_rows() will not return the correct value until all the rows in the result set have been retrieved.

8.1.3.161 Return Values

The number of rows in the result set.

8.1.3.162 Errors

None.

8.1.3.163 mysql_options()

int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg)

8.1.3.164 Description

Can be used to set extra connect options and affect behaviour for a connection. This function may be called multiple times to set several options.

mysql_options() should be called after mysql_init() and before mysql_connect() or mysql_real_connect().

The option argument is the option that you want to set; the arg argument is the value for the option. If the option is an integer, then arg should point to the value of the integer.

Possible options values:

Note that the group client is always read if you use MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP.

The specified group in the option file may contain the following options:

Note that timeout has been replaced by connect-timeout, but timeout will still work for a while.

For more information about option files, see section 4.1.2 `my.cnf' Option Files.

8.1.3.165 Return Values

Zero for success. Non-zero if you used an unknown option.

8.1.3.166 Example

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

The above requests the client to use the compressed client/server protocol and read the additional options from the odbc section in the `my.cnf' file.

8.1.3.167 mysql_ping()

int mysql_ping(MYSQL *mysql)

8.1.3.168 Description

Checks whether the connection to the server is working. If it has gone down, an automatic reconnection is attempted.

This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.

8.1.3.169 Return Values

Zero if the server is alive. Non-zero if an error occurred.

8.1.3.170 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.171 mysql_query()

int mysql_query(MYSQL *mysql, const char *query)

8.1.3.172 Description

Executes the SQL query pointed to by the null-terminated string query. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement.

mysql_query() cannot be used for queries that contain binary data; you should use mysql_real_query() instead. (Binary data may contain the `\0' character, which mysql_query() interprets as the end of the query string.)

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 8.1.3.85 mysql_field_count().

8.1.3.173 Return Values

Zero if the query was successful. Non-zero if an error occurred.

8.1.3.174 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.175 mysql_real_connect()

MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned int client_flag)

8.1.3.176 Description

mysql_real_connect() attempts to establish a connection to a MySQL database engine running on host. mysql_real_connect() must complete successfully before you can execute any of the other API functions, with the exception of mysql_get_client_info().

The parameters are specified as follows:

• The first parameter should be the address of an existing MYSQL structure. Before calling mysql_real_connect() you must call mysql_init() to initialise the MYSQL structure. You can change a lot of connect options with the mysql_options() call. See section 8.1.3.163 mysql_options().
• The value of host may be either a hostname or an IP address. If host is NULL or the string "localhost", a connection to the local host is assumed. If the OS supports sockets (Unix) or named pipes (Windows), they are used instead of TCP/IP to connect to the server.
• The user parameter contains the user's MySQL login ID. If user is NULL, the current user is assumed. Under Unix, this is the current login name. Under Windows ODBC, the current user name must be specified explicitly. See section 8.2.2 How to Fill in the Various Fields in the ODBC Administrator Program.
• The passwd parameter contains the password for user. If passwd is NULL, only entries in the user table for the user that have a blank (empty) password field will be checked for a match. This allows the database administrator to set up the MySQL privilege system in such a way that users get different privileges depending on whether or not they have specified a password. Note: Do not attempt to encrypt the password before calling mysql_real_connect(); password encryption is handled automatically by the client API.
• db is the database name. If db is not NULL, the connection will set the default database to this value.
• If port is not 0, the value will be used as the port number for the TCP/IP connection. Note that the host parameter determines the type of the connection.
• If unix_socket is not NULL, the string specifies the socket or named pipe that should be used. Note that the host parameter determines the type of the connection.
• The value of client_flag is usually 0, but can be set to a combination of the following flags in very special circumstances:

  Flag name	Flag description
  CLIENT_COMPRESS	Use compression protocol.
  CLIENT_FOUND_ROWS	Return the number of found (matched) rows, not the number of affected rows.
  CLIENT_IGNORE_SPACE	Allow spaces after function names. Makes all functions names reserved words.
  CLIENT_INTERACTIVE	Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection.
  CLIENT_NO_SCHEMA	Don't allow the db_name.tbl_name.col_name syntax. This is for ODBC. It causes the parser to generate an error if you use that syntax, which is useful for trapping bugs in some ODBC programs.
  CLIENT_ODBC	The client is an ODBC client. This changes mysqld to be more ODBC-friendly.
  CLIENT_SSL	Use SSL (encrypted protocol).

8.1.3.177 Return Values

A MYSQL* connection handle if the connection was successful, NULL if the connection was unsuccessful. For a successful connection, the return value is the same as the value of the first parameter.

8.1.3.178 Errors

CR_CONN_HOST_ERROR

Failed to connect to the MySQL server.

CR_CONNECTION_ERROR

Failed to connect to the local MySQL server.

CR_IPSOCK_ERROR

Failed to create an IP socket.

CR_OUT_OF_MEMORY

Out of memory.

CR_SOCKET_CREATE_ERROR

Failed to create a Unix socket.

CR_UNKNOWN_HOST

Failed to find the IP address for the hostname.

CR_VERSION_ERROR

A protocol mismatch resulted from attempting to connect to a server with a client library that uses a different protocol version. This can happen if you use a very old client library to connect to a new server that wasn't started with the --old-protocol option.

CR_NAMEDPIPEOPEN_ERROR

Failed to create a named pipe on Windows.

CR_NAMEDPIPEWAIT_ERROR

Failed to wait for a named pipe on Windows.

CR_NAMEDPIPESETSTATE_ERROR

Failed to get a pipe handler on Windows.

CR_SERVER_LOST

If connect_timeout > 0 and it took longer then connect_timeout seconds to connect to the server or if the server died while executing the init-command.

8.1.3.179 Example

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

By using mysql_options() the MySQL library will read the [client] and [your_prog_name] sections in the `my.cnf' file which will ensure that your program will work, even if someone has set up MySQL in some non-standard way.

Note that upon connection, mysql_real_connect() sets the reconnect flag (part of the MYSQL structure) to a value of 1. This flag indicates, in the event that a query cannot be performed because of a lost connection, to try reconnecting to the server before giving up.

8.1.3.180 mysql_real_escape_string()

unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length)

8.1.3.181 Description

This function is used to create a legal SQL string that you can use in a SQL statement. See section 6.1.1.1 Strings.

The string in from is encoded to an escaped SQL string, taking into account the current character set of the connection. The result is placed in to and a terminating null byte is appended. Characters encoded are NUL (ASCII 0), `\n', `\r', `\', `'', `"', and Control-Z (see section 6.1.1 Literals: How to Write Strings and Numbers). (Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. This function quotes the other characters to make them easier to read in log files.)

The string pointed to by from must be length bytes long. You must allocate the to buffer to be at least length*2+1 bytes long. (In the worst case, each character may need to be encoded as using two bytes, and you need room for the terminating null byte.) When mysql_real_escape_string() returns, the contents of to will be a null-terminated string. The return value is the length of the encoded string, not including the terminating null character.

8.1.3.182 Example

char query[1000],*end;

end = strmov(query,"INSERT INTO test_table values(");
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"What's this",11);
*end++ = '\'';
*end++ = ',';
*end++ = '\'';
end += mysql_real_escape_string(&mysql, end,"binary data: \0\r\n",16);
*end++ = '\'';
*end++ = ')';

if (mysql_real_query(&mysql,query,(unsigned int) (end - query)))
{
   fprintf(stderr, "Failed to insert row, Error: %s\n",
           mysql_error(&mysql));
}

The strmov() function used in the example is included in the mysqlclient library and works like strcpy() but returns a pointer to the terminating null of the first parameter.

8.1.3.183 Return Values

The length of the value placed into to, not including the terminating null character.

8.1.3.184 Errors

None.

8.1.3.185 mysql_real_query()

int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length)

8.1.3.186 Description

Executes the SQL query pointed to by query, which should be a string length bytes long. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement.

You must use mysql_real_query() rather than mysql_query() for queries that contain binary data, because binary data may contain the `\0' character. In addition, mysql_real_query() is faster than mysql_query() because it does not call strlen() on the query string.

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 8.1.3.85 mysql_field_count().

8.1.3.187 Return Values

Zero if the query was successful. Non-zero if an error occurred.

8.1.3.188 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.189 mysql_reload()

int mysql_reload(MYSQL *mysql)

8.1.3.190 Description

Asks the MySQL server to reload the grant tables. The connected user must have the RELOAD privilege.

This function is deprecated. It is preferable to use mysql_query() to issue a SQL FLUSH PRIVILEGES statement instead.

8.1.3.191 Return Values

Zero for success. Non-zero if an error occurred.

8.1.3.192 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.193 mysql_row_seek()

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset)

8.1.3.194 Description

Sets the row cursor to an arbitrary row in a query result set. This requires that the result set structure contains the entire result of the query, so mysql_row_seek() may be used in conjunction only with mysql_store_result(), not with mysql_use_result().

The offset should be a value returned from a call to mysql_row_tell() or to mysql_row_seek(). This value is not simply a row number; if you want to seek to a row within a result set using a row number, use mysql_data_seek() instead.

8.1.3.195 Return Values

The previous value of the row cursor. This value may be passed to a subsequent call to mysql_row_seek().

8.1.3.196 Errors

None.

8.1.3.197 mysql_row_tell()

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result)

8.1.3.198 Description

Returns the current position of the row cursor for the last mysql_fetch_row(). This value can be used as an argument to mysql_row_seek().

You should use mysql_row_tell() only after mysql_store_result(), not after mysql_use_result().

8.1.3.199 Return Values

The current offset of the row cursor.

8.1.3.200 Errors

None.

8.1.3.201 mysql_select_db()

int mysql_select_db(MYSQL *mysql, const char *db)

8.1.3.202 Description

Causes the database specified by db to become the default (current) database on the connection specified by mysql. In subsequent queries, this database is the default for table references that do not include an explicit database specifier.

mysql_select_db() fails unless the connected user can be authenticated as having permission to use the database.

8.1.3.203 Return Values

Zero for success. Non-zero if an error occurred.

8.1.3.204 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.205 mysql_shutdown()

int mysql_shutdown(MYSQL *mysql)

8.1.3.206 Description

Asks the database server to shut down. The connected user must have SHUTDOWN privileges.

8.1.3.207 Return Values

Zero for success. Non-zero if an error occurred.

8.1.3.208 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.209 mysql_stat()

char *mysql_stat(MYSQL *mysql)

8.1.3.210 Description

Returns a character string containing information similar to that provided by the mysqladmin status command. This includes uptime in seconds and the number of running threads, questions, reloads, and open tables.

8.1.3.211 Return Values

A character string describing the server status. NULL if an error occurred.

8.1.3.212 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.213 mysql_store_result()

MYSQL_RES *mysql_store_result(MYSQL *mysql)

8.1.3.214 Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

You don't have to call mysql_store_result() or mysql_use_result() for other queries, but it will not do any harm or cause any notable performance if you call mysql_store_result() in all cases. You can detect if the query didn't have a result set by checking if mysql_store_result() returns 0 (more about this later one).

If you want to know if the query should return a result set or not, you can use mysql_field_count() to check for this. See section 8.1.3.85 mysql_field_count().

mysql_store_result() reads the entire result of a query to the client, allocates a MYSQL_RES structure, and places the result into this structure.

mysql_store_result() returns a null pointer if the query didn't return a result set (if the query was, for example, an INSERT statement).

mysql_store_result() also returns a null pointer if reading of the result set failed. You can check if you got an error by checking if mysql_error() doesn't return a null pointer, if mysql_errno() returns <> 0, or if mysql_field_count() returns <> 0.

An empty result set is returned if there are no rows returned. (An empty result set differs from a null pointer as a return value.)

Once you have called mysql_store_result() and got a result back that isn't a null pointer, you may call mysql_num_rows() to find out how many rows are in the result set.

You can call mysql_fetch_row() to fetch rows from the result set, or mysql_row_seek() and mysql_row_tell() to obtain or set the current row position within the result set.

You must call mysql_free_result() once you are done with the result set.

See section 8.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?.

8.1.3.215 Return Values

A MYSQL_RES result structure with the results. NULL if an error occurred.

8.1.3.216 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.217 mysql_thread_id()

unsigned long mysql_thread_id(MYSQL *mysql)

8.1.3.218 Description

Returns the thread ID of the current connection. This value can be used as an argument to mysql_kill() to kill the thread.

If the connection is lost and you reconnect with mysql_ping(), the thread ID will change. This means you should not get the thread ID and store it for later. You should get it when you need it.

8.1.3.219 Return Values

The thread ID of the current connection.

8.1.3.220 Errors

None.

8.1.3.221 mysql_use_result()

MYSQL_RES *mysql_use_result(MYSQL *mysql)

8.1.3.222 Description

You must call mysql_store_result() or mysql_use_result() for every query that successfully retrieves data (SELECT, SHOW, DESCRIBE, EXPLAIN).

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result(). The client will only allocate memory for the current row and a communication buffer that may grow up to max_allowed_packet bytes.

On the other hand, you shouldn't use mysql_use_result() if you are doing a lot of processing for each row on the client side, or if the output is sent to a screen on which the user may type a ^S (stop scroll). This will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

When using mysql_use_result(), you must execute mysql_fetch_row() until a NULL value is returned, otherwise, the unfetched rows will be returned as part of the result set for your next query. The C API will give the error Commands out of sync; you can't run this command now if you forget to do this!

You may not use mysql_data_seek(), mysql_row_seek(), mysql_row_tell(), mysql_num_rows(), or mysql_affected_rows() with a result returned from mysql_use_result(), nor may you issue other queries until the mysql_use_result() has finished. (However, after you have fetched all the rows, mysql_num_rows() will accurately return the number of rows fetched.)

You must call mysql_free_result() once you are done with the result set.

8.1.3.223 Return Values

A MYSQL_RES result structure. NULL if an error occurred.

8.1.3.224 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.3.225 mysql_commit()

my_bool mysql_commit(MYSQL *mysql)

8.1.3.226 Description

Commits the current transaction. Available from MySQL 4.1

8.1.3.227 Return Values

Zero if successful. Non-zero if an error occured.

8.1.3.228 Errors

None

8.1.3.229 mysql_rollback()

my_bool mysql_rollback(MYSQL *mysql)

8.1.3.230 Description

Rollbacks the current transaction. Available from MySQL 4.1

8.1.3.231 Return Values

Zero if successful. Non-zero if an error occured.

8.1.3.232 Errors

None.

8.1.3.233 mysql_autocommit()

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode)

8.1.3.234 Description

Sets autocommit mode on/off. If the mode is 1, set autocommit mode to on; in case of 0, set to off. Available from MySQL 4.1

8.1.3.235 Return Values

Zero if successful. Non-zero if an error occured

8.1.3.236 Errors

None.

8.1.3.237 mysql_more_results()

my_bool mysql_more_results(MYSQL *mysql)

8.1.3.238 Description

Returns true if more results exists from the currently executed query, and the application must call mysql_next_result() to fetch the results. Available from MySQL 4.1

8.1.3.239 Return Values

TRUE if more results exists. FALSE if no more results exists.

8.1.3.240 Errors

None.

8.1.3.241 mysql_next_result()

int mysql_next_result(MYSQL *mysql)

8.1.3.242 Description

If more query results exists, then mysql_next_result() reads the next query results and returns the status back to application. Available from MySQL 4.1

8.1.3.243 Return Values

Zero if successful. Non-zero if an error occured

8.1.3.244 Errors

None.

8.1.4 C API Prepared Statements

From MySQL 4.1, you can also make use of the prepared statements using the statement handler 'MYSQL_STMT', which supports multiple query executions along with input and output binding.

Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed, or prepared. This is executed one or more times at a later time using the statement handle that is returned during the prepare.

Another advantage of prepared statements is that it uses a binary protocol which makes the data tranfer between client and server in a more efficient way than the old MySQL protocol.

Prepared execution is faster than direct execution for statements executed more than once, primarly becuase the query is parsed only once; In the case of direct execution, the query is parsed every time. Prepared execution also can provide a reduction in the network traffic becuase during the execute call, it only sends the data for the parameters.

8.1.5 C API Prepared Statements DataTypes

Prepared statements mainly uses the following two MYSQL_STMT and MYSQL_BIND structures:

MYSQL_STMT

This structure represents a statement handle to prepared statements.It is used for all statement related functions. The statement is initialized when the query is prepared using mysql_prepare(). One connection can have 'n' statement handles, and the limit depends up on the system resources.

MYSQL_BIND

This structure is used in order to bind parameter buffers(mysql_bind_param()) inorder to the parameters data to mysql_execute() call; as well as to bind row buffers(mysql_bind_result()) to fetch the result set data using mysql_fetch(). The MYSQL_BIND structure contains the members listed here:

enum enum_field_types buffer_type [input]

The type of the buffer. The type value must be one of the following:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB
• MYSQL_TYPE_BLOB

void *buffer [input/output]

A pointer to a buffer for the parameters data in case if it is used to supply parameters data or pointer to a buffer in which to return the data when the structure is used for result set bind.

unsigned long buffer_length [input]

Length of the *buffer in bytes. For character and binary C data, the buffer_length specifies the length of the *buffer to be used as a parameter data in case if it is used with mysql_bind_param() or to return that many bytes when fetching results when this is used with mysql_bind_result().

long *length [input/output]

Pointer to the buffer for the parameter's length. When the structure is used as a input parameter data binding, then this argument points to a buffer that, when mysql_execute() is called, contains the length of the parameter value stored in *buffer. This is ignored except for character or binary C data. If the length is a null pointer, then the protocol assumes that all character and binary data are null terminated. When this structure is used in output binding, then mysql_fetch() return the the length of the data that is returned.

bool *is_null [input/output]

Indicates if the parameter data is NULL or fetched data is NULL.

MYSQL_TIME

This structure is used to send and receive DATE, TIME and TIMESTAMP data directly to/from server. The MYSQL_TIME structure contains the members listed here:

Member	Type	Description
year	unsigned int	Year.
month	unsigned int	Month of the year.
day	unsigned int	Day of the month.
hour	unsigned int	Hour of the day(TIME).
minute	unsigned int	Minute of the hour.
second	unsigned int	Second of the minute.
neg	my_bool	A boolean flag to indicate if the time is negative.
second_part	unsigned long	Fraction part of the second(not yet used)

8.1.6 C API Prepared Statements Function Overview

The functions available in the prepared statements are listed here and are described in greater detail in the later section. See section 8.1.7 C API Prepared Statement Function Descriptions.

Call mysql_prepare() to prepare and initialize the statement handle, then call mysql_bind_param() to supply the parameters data, and then call mysql_execute() to execute the query. You can repeat the mysql_execute() by changing parameter values from the respective buffer supplied through mysql_bind_param().

In case if the query is a SELECT statement or any other query which results in a resultset, then mysql_prepare() will also return the result set meta data information in the form of MYSQL_RES result set through mysql_prepare_result().

You can supply the result buffers using mysql_bind_result(), so that the mysql_fetch() will automatically returns data to this buffers. This is row by row fetching.

You can also send the text or binary data in chunks to server using mysql_send_long_data(), by specifying the option is_long_data=1 or length=MYSQL_LONG_DATA or -2 in the MYSQL_BIND structure supplied with mysql_bind_param().

Once the statement execution is over, it must be freed using mysql_stmt_close so that it frees all the alloced resources for the statement handle.

8.1.6.1 Execution Steps:

To prepare and execute a statement, the application:

• Calls mysql_prepare() and passes it a string containing the SQL statement. On a successful prepare, mysql_prepare returns the valid statement handle back to the application
• If the query results in a resultset, then mysql_prepare_result returns the result set meta info..
• Sets the values of any parameters using mysql_bind_param. All parameters must be set; else it will return an error or produce un-expected results
• Calls mysql_execute() to execute the statement.
• Repeat steps 2 and 3 as necessary, by changing the parameter values and re-executing the statement.
• Bind the data buffers to return the row values, if it is a result set query; using mysql_bind_result().
• Fetch the data to buffers row by row by calling mysql_fetch() repetedely until no more rows found.
• When mysql_prepare() is called, in the MySQL client/server protocol:
  • Server parses the query and sends the ok status back to client by assinging a statement id. It also sends total number of parameters, columns count and its meta information if it is a result set oriented query. All syntax and symantecs of the query is checked during this call by the server.
  • Client uses this statement id for the further executions, so that server identifies the statement back from the pool of statements. Now, client allocates a statement handle with this id and returns back to application.
• When mysql_execute() is called, in the MySQL client/server protocol:
  • Client uses the statement handle and sends the parameters data to server.
  • Server identifies the statement using the id provided by the client, and replaces the parameter markers with the newly supplied data and executes the query. If it results in a result set, then sends the data back to client, else sends an OK status with total number of rows changes/deleted/inserted.
• When mysql_fetch() is called, in the MySQL client/server protocol:
  • Client reads the data from the packet row by row and places it to application data buffers by doing the necessary conversions. If the application buffer type is same as that of field type, then the conversions are stright forward.

You can get the statement error code and message using mysql_stmt_errno() and mysql_stmt_error() respectively.

8.1.7 C API Prepared Statement Function Descriptions

You need to use the following functions when you want to prepare and execute the queries.

8.1.7.1 mysql_prepare()

MYSQL_STMT * mysql_prepare(MYSQL *mysql, const char *query, unsigned long length)

8.1.7.2 Description

Prepares the SQL query pointed to by the null-terminated string 'query'. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;`) or \g to the statement.

The application can include one or more parameter markers in the SQL statement. To include a parameter marker, the appication embeds a question mark (?) into the SQL string at the appropriate position.

The markers are legal only in certain places in SQL statements. For example, they are not allowed in the select list(the list of columns to be returned by a SELECT statement), nor are they allowed as both operands of a binary operator such as the equal sign (=), becuase it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Languange(DML) statements, and not in Data Defination Language(DDL) statements.

The parameter markers are then bound to application variables using mysql_bind_param().

8.1.7.3 Return Values

MYSQL_STMT if the prepare was successful. NULL if an error occured.

8.1.7.4 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order

CR_OUT_OF_MEMORY

Out of memory

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_SERVER_LOST

The connection to the server was lost during the query

CR_UNKNOWN_ERROR

An unkown error occured

If the prepare is not successful, i.e. when mysql_prepare() returned a NULL statement, errors can be obtained by calling mysql_error().

8.1.7.5 Example

For the usage of mysql_prepare() refer to the Example from section 8.1.7.21 mysql_execute().

8.1.7.6 mysql_param_count()

unsigned int mysql_param_count(MYSQL_STMT *stmt)

8.1.7.7 Description

Returns the number of parameter markers present from the prepared query.

8.1.7.8 Return Values

An unsigned integer representing the number of parameters in a statement.

8.1.7.9 Errors

None

8.1.7.10 Example

For the usage of mysql_param_count() refer to the Example from section 8.1.7.21 mysql_execute().

8.1.7.11 mysql_prepare_result()

MYSQL_RES *mysql_prepare_result(MYSQL_STMT *stmt)

8.1.7.12 Description

If the mysql_prepare() resulted in a result set query, then mysql_prepare_result() returns the result set meta data in the form of MYSQL_RES structure; which can further be used to process the meta information such as total number of fields and individual field information. This resulted result set can be passed as an argument to any of the field based APIs in order to process the result set meta data information such as:

• mysql_num_fields()
• mysql_fetch_field()
• mysql_fetch_field_direct()
• mysql_fetch_fields()
• mysql_field_count()
• mysql_field_seek()
• mysql_field_tell() and
• mysql_free_result()

8.1.7.13 Return Values

A MYSQL_RES result structure. NULL if no meta information exists from the prepared query.

8.1.7.14 Errors

CR_OUT_OF_MEMORY

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

8.1.7.15 Example

For the usage of mysql_prepare_result() refer to the Example from section 8.1.7.40 mysql_fetch()

8.1.7.16 mysql_bind_param()

int mysql_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)

8.1.7.17 Description

mysql_bind_param is used to bind data for the parameter markers in the SQL statement from mysql_prepare. It uses the structure MYSQL_BIND to supply the data. The supported buffer types are:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB

8.1.7.18 Return Values

Zero if the bind was successful. Non-zero if an error occured.

8.1.7.19 Errors

CR_NO_PREPARE_STMT

No prepared statement exists

CR_NO_PARAMETERS_EXISTS

No parameters exists to bind

CR_INVALID_BUFFER_USE

Indicates if the bind is to supply the long data in chunks and if the buffer type is non string or binary

CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported, possibly the buffer_type is illegal or its not from the above list of supported types.

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

8.1.7.20 Example

For the usage of mysql_bind_param() refer to the Example from section 8.1.7.21 mysql_execute().

8.1.7.21 mysql_execute()

int mysql_execute(MYSQL_STMT *stmt.

8.1.7.22 Description

mysql_execute() executes the prepared query associated with the statement handle. The parameter marker values will be sent to server during this call, so that server replaces markers with this newly supplied data.

If the statement is UPDATE,DELETE,or INSERT, the total number of changed/deletd/inserted values can be found by calling mysql_stmt_affected_rows. If this is a result set query, then one must call mysql_fetch() to fetch the data prior to calling any other calls which results in query processing. For more information on how to fetch the statement binary data, refer to section 8.1.7.40 mysql_fetch().

8.1.7.23 Return Values

mysql_execute() returns the following return values:

8.1.7.24 Errors

CR_NO_PREPARE_QUERY

No query prepared prior to execution

CR_ALL_PARAMS_NOT_BOUND

Not all parameters data is supplied

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.7.25 Example

The following example explains the uasage of mysql_prepare, mysql_param_count, mysql_bind_param, mysql_execute and mysql_stmt_affected_rows().

MYSQL_BIND   bind[3];
MYSQL_STMT   *stmt;
ulonglong    affected_rows;
long         length;
unsigned int param_count;
int          int_data;
short        small_data;
char         str_data[50], query[255];
my_bool      is_null;

  /* Set autocommit mode to true */
  mysql_autocommit(mysql, 1);
  
  if (mysql_query(mysql,"DROP TABLE IF EXISTS test_table"))
  {
    fprintf(stderr, "\n drop table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  if (mysql_query(mysql,"CREATE TABLE test_table(col1 int, col2 varchar(50), \
                                                 col3 smallint,\
                                                 col4 timestamp(14))"))
  {
    fprintf(stderr, "\n create table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  
  /* Prepare a insert query with 3 parameters */
  strmov(query, "INSERT INTO test_table(col1,col2,col3) values(?,?,?)");
  if(!(stmt = mysql_prepare(mysql, query, strlen(query))))
  {
    fprintf(stderr, "\n prepare, insert failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  fprintf(stdout, "\n prepare, insert successful");

  /* Get the parameter count from the statement */
  param_count= mysql_param_count(stmt);

  fprintf(stdout, "\n total parameters in insert: %d", param_count);
  if (param_count != 3) /* validate parameter count */
  {
    fprintf(stderr, "\n invalid parameter count returned by MySQL");
    exit(0);
  }

  /* Bind the data for the parameters */

  /* INTEGER PART */
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (char *)&int_data;
  bind[0].is_null= 0;
  bind[0].length= 0;

  /* STRING PART */
  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind[1].buffer= (char *)str_data;
  bind[1].buffer_length= sizeof(str_data);
  bind[1].is_null= 0;
  bind[1].length= 0;

  /* SMALLINT PART */
  bind[2].buffer_type= MYSQL_TYPE_SHORT;
  bind[2].buffer= (char *)&small_data;
  bind[2].is_null= &is_null;
  bind[2].length= 0;
  is_null= 0;

  /* Bind the buffers */
  if (mysql_bind_param(stmt, bind))
  {
    fprintf(stderr, "\n param bind failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

  /* Specify the data */
  int_data= 10;             /* integer */
  strcpy(str_data,"MySQL"); /* string  */

  /* INSERT SMALLINT data as NULL */
  is_null= 1;

  /* Execute the insert statement - 1*/
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute 1 failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    fprintf(stderr, "\n send a bug report to bugs@lists.mysql.com, by asking why this is not working ?");
    exit(0);
  }
    
  /* Get the total rows affected */   
  affected_rows= mysql_stmt_affected_rows(stmt);

  fprintf(stdout, "\n total affected rows: %lld", affected_rows);
  if (affected_rows != 1) /* validate affected rows */
  {
    fprintf(stderr, "\n invalid affected rows by MySQL");
    exit(0);
  }

  /* Re-execute the insert, by changing the values */
  int_data= 1000;             
  strcpy(str_data,"The most popular open source database"); 
  small_data= 1000;         /* smallint */
  is_null= 0;               /* reset NULL */
 
  /* Execute the insert statement - 2*/
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute 2 failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }
    
  /* Get the total rows affected */   
  affected_rows= mysql_stmt_affected_rows(stmt);

  fprintf(stdout, "\n total affected rows: %lld", affected_rows);
  if (affected_rows != 1) /* validate affected rows */
  {
    fprintf(stderr, "\n invalid affected rows by MySQL");
    exit(0);
  }

  /* Close the statement */
  if (mysql_stmt_close(stmt))
  {
    fprintf(stderr, "\n failed while closing the statement");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }
  
  /* DROP THE TABLE */
  if (mysql_query(mysql,"DROP TABLE test_table"))
  {
    fprintf(stderr, "\n drop table failed");
    fprintf(stderr, "\n %s", mysql_error(mysql));
    exit(0);
  }
  fprintf(stdout, "Success, MySQL prepared statements are working!!!");

8.1.7.26 mysql_stmt_affected_rows()

ulonglong mysql_stmt_affected_rows(MYSQL_STMT *stmt)

8.1.7.27 Description

Returns total number of rows changed by the last execute statement. May be called immediatlely after mysql_execute() for UPDATE,DELETE,or INSERT statements.For SELECT statements, mysql_stmt_affected rows works like mysql_num_rows().

8.1.7.28 Return Values

An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT query, mysql_stmt_affected_rows() was called prior to calling mysql_fetch().

8.1.7.29 Errors

None.

8.1.7.30 Example

For the usage of mysql_stmt_affected_rows() refer to the Example from section 8.1.7.21 mysql_execute().

8.1.7.31 mysql_bind_result()

my_bool mysql_bind_result(MYSQL_STMT *stmt, MYSQL_BIND *bind)

8.1.7.32 Description

mysql_bind_result() is used to associate, or bind, columns in the resultset to data buffers and length buffers. When mysql_fetch() is called to fetch data, the MySQL client protocol returns the data for the bound columns in the specified buffers.

Note that all columns must be bound prior to calling mysql_fetch() in case of fetching the data to buffers; else mysql_fetch() simply ignores the data fetch; also the buffers should be sufficient enough to hold the data as the protocol doesn't return the data in chunks.

A column can be bound or rebound at any time, even after data has been fetched from the result set. The new binding takes effect the next time mysql_fetch() is called. For example, suppose an application binds the columns in a result set and calls mysql_fetch(). The mysql protocol returns data in the bound buffers. Now suppose the application binds the columns to a different set of buffers, then the protocol does not place the data for the just fetched row in the newly bound buffers. Instead, it does when the next mysql_fetch() is called.

To bind a column, an application calls mysql_bind_result() and passes the type, address, and the address of the length buffer. The supported buffer types are:

• MYSQL_TYPE_TINY
• MYSQL_TYPE_SHORT
• MYSQL_TYPE_LONG
• MYSQL_TYPE_LONGLONG
• MYSQL_TYPE_FLOAT
• MYSQL_TYPE_DOUBLE
• MYSQL_TYPE_TIME
• MYSQL_TYPE_DATE
• MYSQL_TYPE_DATETIME
• MYSQL_TYPE_TIMESTAMP
• MYSQL_TYPE_STRING
• MYSQL_TYPE_VAR_STRING
• MYSQL_TYPE_BLOB
• MYSQL_TYPE_TINY_BLOB
• MYSQL_TYPE_MEDIUM_BLOB
• MYSQL_TYPE_LONG_BLOB

8.1.7.33 Return Values

Zero if the bind was successful. Non-zero if an error occured.

8.1.7.34 Errors

CR_NO_PREPARE_STMT

No prepared statement exists

CR_UNSUPPORTED_PARAM_TYPE

The conversion is not supported, possibly the buffer_type is illegal or its not from the list of supported types.

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

8.1.7.35 Example

For the usage of mysql_bind_result() refer to the Example from section 8.1.7.40 mysql_fetch()

8.1.7.36 mysql_stmt_store_result()

int mysql_stmt_store_result(MYSQL_STMT *stmt)

8.1.7.37 Description

You must call mysql_stmt_store_result() for every query that successfully retrieves data(SELECT,SHOW,DESCRIBE,EXPLAIN), and only if you want to buffer the complete result set by the client, so that the subsequent mysql_fetch() call returns buffered data.

You don't have to call mysql_stmt_store_result() for other queries, but it will not harm or cause any notable performance in all cases.You can detect if the query didn't have a result set by checking if mysql_prepare_result() returns 0. For more information refer to section 8.1.7.11 mysql_prepare_result().

8.1.7.38 Return Values

Zero if the results are buffered successfully or Non Zero in case of an error.

8.1.7.39 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

8.1.7.40 mysql_fetch()

int mysql_fetch(MYSQL_STMT *stmt)

8.1.7.41 Description

mysql_fetch() returns the next rowset in the result set. It can be called only while the result set exists i.e. after a call to mysql_execute() that creates a result set or after mysql_stmt_store_result(), which is called after mysql_execute() to buffer the entire resultset.

If row buffers are bound using mysql_bind_result(), it returns the data in those buffers for all the columns in the current row set and the lengths are returned to the length pointer.

Note that, all columns must be bound by the application.

If the data fetched is a NULL data, then the is_null value from MYSQL_BIND contains TRUE, 1, else the data and its length is returned to *buffer and *length variables based on the buffer type specified by the application. All numeric, float and double types have the fixed length(in bytes) as listed below:

where *data_length is nothing but the 'Actual length of the data'.

8.1.7.42 Return Values

8.1.7.43 Errors

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_OUT_OF_MEMORY

Out of memory.

CR_SERVER_GONE_ERROR

The MySQL server has gone away.

CR_SERVER_LOST

The connection to the server was lost during the query.

CR_UNKNOWN_ERROR

An unknown error occurred.

CR_UNSUPPORTED_PARAM_TYPE

If the buffer type is MYSQL_TYPE_DATE,DATETIME,TIME,or TIMESTAMP; and if the field type is not DATE, TIME, DATETIME or TIMESTAMP.

All other unsupported conversion errors are returned from mysql_bind_result().

8.1.7.44 Example

The following example explains the usage of mysql_prepare_result, mysql_bind_result(), and mysql_fetch()

  
MYSQL_STMT *stmt;  
MYSQL_BIND bind[2];
MYSQL_RES  *result;
int        int_data;
long       int_length, str_length;
char       str_data[50];
my_bool    is_null[2];

  query= "SELECT col1, col2 FROM test_table WHERE col1= 10)");
  if (!(stmt= mysql_prepare(&mysql, query, strlen(query)))
  {
    fprintf(stderr, "\n prepare failed");
    fprintf(stderr, "\n %s", mysql_error(&stmt));
    exit(0);
  }

  /* Get the fields meta information */
  if (!(result= mysql_prepare_result(stmt)))
  {
    fprintf(stderr, "\n prepare_result failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

  fprintf(stdout, "Total fields: %ld", mysql_num_fields(result));

  if (mysql_num_fields(result) != 2)
  {
    fprintf(stderr, "\n prepare returned invalid field count");
    exit(0);
  }

  /* Execute the SELECT query */
  if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n execute failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));  
    exit(0);
  }

  /* Bind the result data buffers */
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (char *)&int_data;
  bind[0].is_null= &is_null[0];
  bind[0].length= &int_length;

  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind[1].buffer= (void *)str_data;
  bind[1].is_null= &is_null[1];
  bind[1].length= &str_length;

  if (mysql_bind_result(stmt, bind))
  {
    fprintf(stderr, "\n bind_result failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  

  /* Now fetch data to buffers */
  if (mysql_fetch(stmt))
  {
    fprintf(stderr, "\n fetch failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  
  
  if (is_null[0])
    fprintf(stdout, "\n Col1 data is NULL");
  else
    fprintf(stdout, "\n Col1: %d, length: %ld", int_data, int_length);
   
   if (is_null[1])
     fprintf(stdout, "\n Col2 data is NULL");
   else
    fprintf(stdout, "\n Col2: %s, length: %ld", str_data, str_length);

   
  /* call mysql_fetch again */
  if (mysql_fetch(stmt) |= MYSQL_NO_DATA)
  {
    fprintf(stderr, "\n fetch return more than one row);
    exit(0);
  }  

  /* Free the prepare result meta information */
  mysql_free_result(result);

  /* Free the statement handle */
  if (mysql_stmt_free(stmt))
  {
    fprintf(stderr, "\n failed to free the statement handle);
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }  
 

8.1.7.45 mysql_send_long_data()

int mysql_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, ulong length)

8.1.7.46 Description

Allows an application to send the data in pieces or chunks to server. This function can be used to send character or binary data values in parts to a column(it must be a text or blob) with a character or binary data type.

The data is a pointer to buffer containing the actual data for the parameter represendted by parameter_number. The length indicates the amount of data to be sent in bytes.

8.1.7.47 Return Values

Zero if the data is sent successfully to server. Non-zero if an error occured.

8.1.7.48 Errors

CR_INVALID_PARAMETER_NO

Invalid parameter number

CR_COMMANDS_OUT_OF_SYNC

Commands were executed in an improper order.

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_OUT_OF_MEMOR

Out of memory

CR_UNKNOWN_ERROR

An unknown error occured

8.1.7.49 Example

The following example explains how to send the data in chunks to text column:

  
MYSQL_BIND bind[1];
long       length;

  query= "INSERT INTO test_long_data(text_column) VALUES(?)");
  if (!mysql_prepare(&mysql, query, strlen(query))
  {
    fprintf(stderr, "\n prepare failed");
    fprintf(stderr, "\n %s", mysql_error(&stmt));
    exit(0);
  }
   memset(bind, 0, sizeof(bind));
   bind[0].buffer_type= MYSQL_TYPE_STRING;
   bind[0].length= &length;
   bind[0].is_null= 0;

  /* Bind the buffers */
  if (mysql_bind_param(stmt, bind))
  {
    fprintf(stderr, "\n param bind failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Supply data in chunks to server */
   if (!mysql_send_long_data(stmt,1,"MySQL",5))
  {
    fprintf(stderr, "\n send_long_data failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Supply the next piece of data */
   if (mysql_send_long_data(stmt,1," - The most popular open source database",40))
  {
    fprintf(stderr, "\n send_long_data failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   /* Now, execute the query */
   if (mysql_execute(stmt))
  {
    fprintf(stderr, "\n mysql_execute failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
    exit(0);
  }

   This inserts the data, "MySQL - The most popular open source database" 
   to the field 'text_column'.  

8.1.7.50 mysql_stmt_close()

my_bool mysql_stmt_close(MYSQL_STMT *)

8.1.7.51 Description

Closes the prepared statement. mysql_stmt_close() also deallocates the statement handle pointed to by stmt.

If the current query results are pending or un-read; this cancels the query results; so that next call can be executed.

8.1.7.52 Return Values

Zero if the statement was freed successfully. Non-zero if an error occured.

8.1.7.53 Errors

CR_SERVER_GONE_ERROR

The MySQL server has gone away

CR_UNKNOWN_ERROR

An unkown error occured

8.1.7.54 Example

For the usage of mysql_stmt_close() refer to the Example from section 8.1.7.21 mysql_execute().

8.1.7.55 mysql_stmt_errno()

unsigned int mysql_stmt_errno(MYSQL_STMT *stmt)

8.1.7.56 Description

For the statement specified by stmt, mysql_stmt_errno() returns the error code for the most recently invoked statement API function that can succeed or fail. A return value of zero means that no error occured. Client error message numbers are listed in the MySQL errmsg.h header file. Server error message numbers are listed in mysqld_error.h. In the MySQL source distribution you can find a complete list of error messages and error numbers in the file Docs/mysqld_error.txt

8.1.7.57 Return Values

An error code value. Zero if no error occured.

8.1.7.58 Errors

None

8.1.7.59 mysql_stmt_error()

char *mysql_stmt_error(MYSQL_STMT *stmt)

8.1.7.60 Description

For the statement specified by stmt, mysql_stmt_error() returns the error message for the most recently invoked statement API that can succeed or fail. An empty string ("") is returned if no error occured. This means the following two sets are equivalent:

if (mysql_stmt_errno(stmt))
{
  // an error occured
}

if (mysql_stmt_error(stmt))
{
  // an error occured
}

The language of the client error messages many be changed by recompiling the MySQL client library. Currently you can choose error messages in several different languages.

8.1.7.61 Return Values

A character string that describes the error. An empry string if no error occured.

8.1.7.62 Errors

None

8.1.8 C API Handling multiple query executions

From version 4.1, MySQL supports the multi query execution in a single command. In order to do this, you must set the client flag CLIENT_MULTI_QUERIES option when opening the connection.

By default mysql_query() or mysql_real_query() returns only the first query status and the subsequent queries status can be processed using mysql_more_results() and mysql_next_result().

  /* Connect to server with option CLIENT_MULTI_QUERIES */
  mysql_real_query(..., CLIENT_MULTI_QUERIES);

  /* Now execute multiple queries */
  mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\
                     CREATE TABLE test_table(id int);\
                     INSERT INTO test_table VALUES(10);\
                     UPDATE test_table SET id=20 WHERE id=10;\
                     SELECT * FROM test_table;\
                     DROP TABLE test_table";
  while (mysql_more_results(mysql))
  {
    /* Process all results */
    mysql_next_result(mysql);
    ...
    printf("total affected rows: %lld", mysql_affected_rows(mysql));
    ...
    if ((result= mysql_store_result(mysql))
    {
      /* Returned a result set, process it */
    }
  }

8.1.9 C API Handling DATE, TIME and TIMESTAMP

Using the new binary protocol from MySQL 4.1 and above, one can send and receive the DATE, TIME and TIMESTAMP data using the MYSQL_TIME structure.

MYSQL_TIME structure consites of the following members:

• year
• month
• day
• hour
• minute
• second
• second_part

In order to send the data, one must use the prepared statements through mysql_prepare() and mysql_execute(); and must bind the parameter using type as MYSQL_TYPE_DATE inorder to process date value, MYSQL_TYPE_TIME for time and MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP for datetime/timestamp using mysql_bind_param() when sending and mysql_bind_results() while receiving the data.

Here is a simple example; which inserts the DATE, TIME and TIMESTAMP data.

MYSQL_TIME  ts;
MYSQL_BIND  bind[3];
MYSQL_STMT  *stmt;
  
  strmov(query, "INSERT INTO test_table(date_field, time_field,
                                        timestamp_field) VALUES(?,?,?");

  stmt= mysql_prepare(mysql, query, strlen(query))); 

  /* setup input buffers for all 3 parameters */
  bind[0].buffer_type= MYSQL_TYPE_DATE;
  bind[0].buffer= (char *)&ts;
  bind[0].is_null= 0;
  bind[0].length= 0;
  ..
  bind[1]= bind[2]= bind[0];
  ..

  mysql_bind_param(stmt, bind);

  /* supply the data to be sent is the ts structure */
  ts.year= 2002;
  ts.month= 02;
  ts.day= 03;

  ts.hour= 10;
  ts.minute= 45;
  ts.second= 20;

  mysql_execute(stmt);
  .. 

8.1.10 C API Threaded Function Descriptions

You need to use the following functions when you want to create a threaded client. See section 8.1.14 How to Make a Threaded Client.

8.1.10.1 my_init()

void my_init(void)

8.1.10.2 Description

This function needs to be called once in the program before calling any MySQL function. This initialises some global variables that MySQL needs. If you are using a thread-safe client library, this will also call mysql_thread_init() for this thread.

This is automatically called by mysql_init(), mysql_server_init() and mysql_connect().

8.1.10.3 Return Values

None.

8.1.10.4 mysql_thread_init()

my_bool mysql_thread_init(void)

8.1.10.5 Description

This function needs to be called for each created thread to initialise thread-specific variables.

This is automatically called by my_init() and mysql_connect().

8.1.10.6 Return Values

None.

8.1.10.7 mysql_thread_end()

void mysql_thread_end(void)

8.1.10.8 Description

This function needs to be called before calling pthread_exit() to free memory allocated by mysql_thread_init().

Note that this function is not invoked automatically by the client library. It must be called explicitly to avoid a memory leak.

8.1.10.9 Return Values

None.

8.1.10.10 mysql_thread_safe()

unsigned int mysql_thread_safe(void)

8.1.10.11 Description

This function indicates whether the client is compiled as thread-safe.

8.1.10.12 Return Values

1 is the client is thread-safe, 0 otherwise.

8.1.11 C API Embedded Server Function Descriptions

You must use the following functions if you want to allow your application to be linked against the embedded MySQL server library. See section 8.1.15 libmysqld, the Embedded MySQL Server Library.

If the program is linked with -lmysqlclient instead of -lmysqld, these functions do nothing. This makes it possible to choose between using the embedded MySQL server and a stand-alone server without modifying any code.

8.1.11.1 mysql_server_init()

int mysql_server_init(int argc, char **argv, char **groups)

8.1.11.2 Description

This function must be called once in the program using the embedded server before calling any other MySQL function. It starts up the server and initialises any subsystems (mysys, InnoDB, etc.) that the server uses. If this function is not called, the program will crash. If you are using the DBUG package that comes with MySQL, you should call this after you have called MY_INIT().

The argc and argv arguments are analogous to the arguments to main(). The first element of argv is ignored (it typically contains the program name). For convenience, argc may be 0 (zero) if there are no command-line arguments for the server. mysql_server_init() makes a copy of the arguments so it's safe to destroy argv or groups after the call.

The NULL-terminated list of strings in groups selects which groups in the option files will be active. See section 4.1.2 `my.cnf' Option Files. For convenience, groups may be NULL, in which case the [server] and [emedded] groups will be active.

8.1.11.3 Example

#include <mysql.h>
#include <stdlib.h>

static char *server_args[] = {
  "this_program",       /* this string is not used */
  "--datadir=.",
  "--key_buffer_size=32M"
};
static char *server_groups[] = {
  "embedded",
  "server",
  "this_program_SERVER",
  (char *)NULL
};

int main(void) {
  mysql_server_init(sizeof(server_args) / sizeof(char *),
                    server_args, server_groups);

  /* Use any MySQL API functions here */

  mysql_server_end();

  return EXIT_SUCCESS;
}

8.1.11.4 Return Values

0 if okay, 1 if an error occurred.

8.1.11.5 mysql_server_end()

void mysql_server_end(void)

8.1.11.6 Description

This function must be called once in the program after all other MySQL functions. It shuts down the embedded server.

8.1.11.7 Return Values

None.

8.1.12 Common questions and problems when using the C API

8.1.12.1 Why Is It that After mysql_query() Returns Success, mysql_store_result() Sometimes Returns NULL?

It is possible for mysql_store_result() to return NULL following a successful call to mysql_query(). When this happens, it means one of the following conditions occurred:

• There was a malloc() failure (for example, if the result set was too large).
• The data couldn't be read (an error occurred on the connection).
• The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).

You can always check whether the statement should have produced a non-empty result by calling mysql_field_count(). If mysql_field_count() returns zero, the result is empty and the last query was a statement that does not return values (for example, an INSERT or a DELETE). If mysql_field_count() returns a non-zero value, the statement should have produced a non-empty result. See the description of the mysql_field_count() function for an example.

You can test for an error by calling mysql_error() or mysql_errno().

8.1.12.2 What Results Can I Get From a Query?

In addition to the result set returned by a query, you can also get the following information:

• mysql_affected_rows() returns the number of rows affected by the last query when doing an INSERT, UPDATE, or DELETE. An exception is that if DELETE is used without a WHERE clause, the table is re-created empty, which is much faster! In this case, mysql_affected_rows() returns zero for the number of records affected.
• mysql_num_rows() returns the number of rows in a result set. With mysql_store_result(), mysql_num_rows() may be called as soon as mysql_store_result() returns. With mysql_use_result(), mysql_num_rows() may be called only after you have fetched all the rows with mysql_fetch_row().
• mysql_insert_id() returns the ID generated by the last query that inserted a row into a table with an AUTO_INCREMENT index. See section 8.1.3.130 mysql_insert_id().
• Some queries (LOAD DATA INFILE ..., INSERT INTO ... SELECT ..., UPDATE) return additional information. The result is returned by mysql_info(). See the description for mysql_info() for the format of the string that it returns. mysql_info() returns a NULL pointer if there is no additional information.

8.1.12.3 How Can I Get the Unique ID for the Last Inserted Row?

If you insert a record in a table containing a column that has the AUTO_INCREMENT attribute, you can get the most recently generated ID by calling the mysql_insert_id() function.

You can also retrieve the ID by using the LAST_INSERT_ID() function in a query string that you pass to mysql_query().

You can check if an AUTO_INCREMENT index is used by executing the following code. This also checks if the query was an INSERT with an AUTO_INCREMENT index:

if (mysql_error(&mysql)[0] == 0 &&
    mysql_num_fields(result) == 0 &&
    mysql_insert_id(&mysql) != 0)
{
    used_id = mysql_insert_id(&mysql);
}

The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).

If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');              # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table

8.1.12.4 Problems Linking with the C API

When linking with the C API, the following errors may occur on some systems:

gcc -g -o client test.o -L/usr/local/lib/mysql -lmysqlclient -lsocket -lnsl

Undefined        first referenced
 symbol          in file
floor            /usr/local/lib/mysql/libmysqlclient.a(password.o)
ld: fatal: Symbol referencing errors. No output written to client

If this happens on your system, you must include the math library by adding -lm to the end of the compile/link line.

8.1.13 Building Client Programs

If you compile MySQL clients that you've written yourself or that you obtain from a third-party, they must be linked using the -lmysqlclient -lz option on the link command. You may also need to specify a -L option to tell the linker where to find the library. For example, if the library is installed in `/usr/local/mysql/lib', use -L/usr/local/mysql/lib -lmysqlclient -lz on the link command.

For clients that use MySQL header files, you may need to specify a -I option when you compile them (for example, -I/usr/local/mysql/include), so the compiler can find the header files.

To make the above simpler on Unix we have provied the mysql_config script for you. See section 4.8.9 mysql_config, Get compile options for compiling clients.

You can use this to compile a MySQL client by as follows:

CFG=/usr/local/mysql/bin/mysql_config
sh -c "gcc -o progname `$CFG --cflags` progname.c `$CFG --libs`"

The sh -c is need to get the shell to not threat the output from mysql_config as one word.

8.1.14 How to Make a Threaded Client

The client library is almost thread-safe. The biggest problem is that the subroutines in `net.c' that read from sockets are not interrupt safe. This was done with the thought that you might want to have your own alarm that can break a long read to a server. If you install interrupt handlers for the SIGPIPE interrupt, the socket handling should be thread-safe.

In the older binaries we distribute on our web site (http://www.mysql.com/), the client libraries are not normally compiled with the thread-safe option (the Windows binaries are by default compiled to be thread-safe). Newer binary distributions should have both a normal and a thread-safe client library.

To get a threaded client where you can interrupt the client from other threads and set timeouts when talking with the MySQL server, you should use the -lmysys, -lmystrings, and -ldbug libraries and the net_serv.o code that the server uses.

If you don't need interrupts or timeouts, you can just compile a thread-safe client library (mysqlclient_r) and use this. See section 8.1 MySQL C API. In this case you don't have to worry about the net_serv.o object file or the other MySQL libraries.

When using a threaded client and you want to use timeouts and interrupts, you can make great use of the routines in the `thr_alarm.c' file. If you are using routines from the mysys library, the only thing you must remember is to call my_init() first! See section 8.1.10 C API Threaded Function Descriptions.

All functions except mysql_real_connect() are by default thread-safe. The following notes describe how to compile a thread-safe client library and use it in a thread-safe manner. (The notes below for mysql_real_connect() actually apply to mysql_connect() as well, but because mysql_connect() is deprecated, you should be using mysql_real_connect() anyway.)

To make mysql_real_connect() thread-safe, you must recompile the client library with this command:

shell> ./configure --enable-thread-safe-client

This will create a thread-safe client library libmysqlclient_r. (Assuming your OS has a thread-safe gethostbyname_r() function.) This library is thread-safe per connection. You can let two threads share the same connection with the following caveats:

• Two threads can't send a query to the MySQL server at the same time on the same connection. In particular, you have to ensure that between a mysql_query() and mysql_store_result() no other thread is using the same connection.
• Many threads can access different result sets that are retrieved with mysql_store_result().
• If you use mysql_use_result, you have to ensure that no other thread is using the same connection until the result set is closed. However, it really is best for threaded clients that share the same connection to use mysql_store_result().
• If you want to use multiple threads on the same connection, you must have a mutex lock around your mysql_query() and mysql_store_result() call combination. Once mysql_store_result() is ready, the lock can be released and other threads may query the same connection.
• If you program with POSIX threads, you can use pthread_mutex_lock() and pthread_mutex_unlock() to establish and release a mutex lock.

You need to know the following if you have a thread that is calling MySQL functions which did not create the connection to the MySQL database:

When you call mysql_init() or mysql_connect(), MySQL will create a thread-specific variable for the thread that is used by the debug library (among other things).

If you call a MySQL function, before the thread has called mysql_init() or mysql_connect(), the thread will not have the necessary thread-specific variables in place and you are likely to end up with a core dump sooner or later.

The get things to work smoothly you have to do the following:

1. Call my_init() at the start of your program if it calls any other MySQL function before calling mysql_real_connect().
2. Call mysql_thread_init() in the thread handler before calling any MySQL function.
3. In the thread, call mysql_thread_end() before calling pthread_exit(). This will free the memory used by MySQL thread-specific variables.

You may get some errors because of undefined symbols when linking your client with libmysqlclient_r. In most cases this is because you haven't included the thread libraries on the link/compile line.

8.1.15 libmysqld, the Embedded MySQL Server Library

8.1.15.1 Overview of the Embedded MySQL Server Library

The embedded MySQL server library makes it possible to run a full-featured MySQL server inside the client application. The main benefits are increased speed and more simple management for embedded applications.

The API is identical for the embedded MySQL version and the client/server version. To change an old threaded application to use the embedded library, you normally only have to add calls to the following functions:

Then you must link your code with `libmysqld.a' instead of `libmysqlclient.a'.

The above mysql_server_xxx functions are also included in `libmysqlclient.a' to allow you to change between the embedded and the client/server version by just linking your application with the right library. See section 8.1.11.1 mysql_server_init().

8.1.15.2 Compiling Programs with libmysqld

To get a libmysqld library you should configure MySQL with the --with-embedded-server option.

When you link your program with libmysqld, you must also include the system-specific pthread libraries and some libraries that the MySQL server uses. You can get the full list of libraries by executing mysql_config --libmysqld-libs.

The correct flags for compiling and linking a threaded program must be used, even if you do not directly call any thread functions in your code.

8.1.15.3 Restrictions when using the Embedded MySQL Server

The embedded server has the following limitations:

• No support for ISAM tables. (This is mainly done to make the library smaller)
• No UDF functions.
• No stack trace on core dump.
• No internal RAID support. (This is not normally needed as most OS has nowadays support for big files).
• You can set this up as a server or a master (no replication).
• You can't connect to the embedded server from an outside process with sockets or TCP/IP.

Some of these limitations can be changed by editing the `mysql_embed.h' include file and recompiling MySQL.

8.1.15.4 Using Option Files with the Embedded Server

The following is the recommended way to use option files to make it easy to switch between a client/server application and one where MySQL is embedded. See section 4.1.2 `my.cnf' Option Files.

• Put common options in the [server] section. These will be read by both MySQL versions.
• Put client/server-specific options in the [mysqld] section.
• Put embedded MySQL-specific options in the [embedded] section.
• Put application-specific options in a [ApplicationName_SERVER] section.

8.1.15.5 Things left to do in Embedded Server (TODO)

• We are going to provide options to leave out some parts of MySQL to make the library smaller.
• There is still a lot of speed optimisation to do.
• Errors are written to stderr. We will add an option to specify a filename for these.
• We have to change InnoDB to not be so verbose when using in the embedded version.

8.1.15.6 A Simple Embedded Server Example

This example program and makefile should work without any changes on a Linux or FreeBSD system. For other operating systems, minor changes will be needed. This example is designed to give enough details to understand the problem, without the clutter that is a necessary part of a real application.

To try out the example, create an `test_libmysqld' directory at the same level as the mysql-4.0 source directory. Save the `test_libmysqld.c' source and the `GNUmakefile' in the directory, and run GNU `make' from inside the `test_libmysqld' directory.

`test_libmysqld.c'

/*
 * A simple example client, using the embedded MySQL server library
 */

#include <mysql.h>
#include <stdarg.h>
#include <stdio.h>
#include <stdlib.h>

MYSQL *db_connect(const char *dbname);
void db_disconnect(MYSQL *db);
void db_do_query(MYSQL *db, const char *query);

const char *server_groups[] = {
  "test_libmysqld_SERVER", "embedded", "server", NULL
};

int
main(int argc, char **argv)
{
  MYSQL *one, *two;

  /* mysql_server_init() must be called before any other mysql
   * functions.
   *
   * You can use mysql_server_init(0, NULL, NULL), and it will
   * initialise the server using groups = {
   *   "server", "embedded", NULL
   *  }.
   *
   * In your $HOME/.my.cnf file, you probably want to put:

[test_libmysqld_SERVER]
language = /path/to/source/of/mysql/sql/share/english

   * You could, of course, modify argc and argv before passing
   * them to this function.  Or you could create new ones in any
   * way you like.  But all of the arguments in argv (except for
   * argv[0], which is the program name) should be valid options
   * for the MySQL server.
   *
   * If you link this client against the normal mysqlclient
   * library, this function is just a stub that does nothing.
   */
  mysql_server_init(argc, argv, (char **)server_groups);

  one = db_connect("test");
  two = db_connect(NULL);

  db_do_query(one, "SHOW TABLE STATUS");
  db_do_query(two, "SHOW DATABASES");

  mysql_close(two);
  mysql_close(one);

  /* This must be called after all other mysql functions */
  mysql_server_end();

  exit(EXIT_SUCCESS);
}

static void
die(MYSQL *db, char *fmt, ...)
{
  va_list ap;
  va_start(ap, fmt);
  vfprintf(stderr, fmt, ap);
  va_end(ap);
  (void)putc('\n', stderr);
  if (db)
    db_disconnect(db);
  exit(EXIT_FAILURE);
}

MYSQL *
db_connect(const char *dbname)
{
  MYSQL *db = mysql_init(NULL);
  if (!db)
    die(db, "mysql_init failed: no memory");
  /*
   * Notice that the client and server use separate group names.
   * This is critical, because the server will not accept the
   * client's options, and vice versa.
   */
  mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
  if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
    die(db, "mysql_real_connect failed: %s", mysql_error(db));

  return db;
}

void
db_disconnect(MYSQL *db)
{
  mysql_close(db);
}

void
db_do_query(MYSQL *db, const char *query)
{
  if (mysql_query(db, query) != 0)
    goto err;

  if (mysql_field_count(db) > 0)
  {
    MYSQL_RES   *res;
    MYSQL_ROW    row, end_row;
    int num_fields;

    if (!(res = mysql_store_result(db)))
      goto err;
    num_fields = mysql_num_fields(res);
    while ((row = mysql_fetch_row(res)))
    {
      (void)fputs(">> ", stdout);
      for (end_row = row + num_fields; row < end_row; ++row)
        (void)printf("%s\t", row ? (char*)*row : "NULL");
      (void)fputc('\n', stdout);
    }
    (void)fputc('\n', stdout);
  }
  else
    (void)printf("Affected rows: %lld\n", mysql_affected_rows(db));

  mysql_free_result(res);

  return;

err:
  die(db, "db_do_query failed: %s [%s]", mysql_error(db), query);
}

`GNUmakefile'

# This assumes the MySQL software is installed in /usr/local/mysql
inc      := /usr/local/mysql/include/mysql
lib      := /usr/local/mysql/lib

# If you have not installed the MySQL software yet, try this instead
#inc      := $(HOME)/mysql-4.0/include
#lib      := $(HOME)/mysql-4.0/libmysqld

CC       := gcc
CPPFLAGS := -I$(inc) -D_THREAD_SAFE -D_REENTRANT
CFLAGS   := -g -W -Wall
LDFLAGS  := -static
# You can change -lmysqld to -lmysqlclient to use the
# client/server library
LDLIBS    = -L$(lib) -lmysqld -lz -lm -lcrypt

ifneq (,$(shell grep FreeBSD /COPYRIGHT 2>/dev/null))
# FreeBSD
LDFLAGS += -pthread
else
# Assume Linux
LDLIBS += -lpthread
endif

# This works for simple one-file test programs
sources := $(wildcard *.c)
objects := $(patsubst %c,%o,$(sources))
targets := $(basename $(sources))

all: $(targets)

clean:
	rm -f $(targets) $(objects) *.core

8.1.15.7 Licensing the Embedded Server

The MySQL source code is covered by the GNU GPL license (see section H GNU General Public License). One result of this is that any program which includes, by linking with libmysqld, the MySQL source code must be released as free software (under a license compatible with the GPL).

We encourage everyone to promote free software by releasing code under the GPL or a compatible license. For those who are not able to do this, another option is to purchase a commercial licence for the MySQL code from MySQL AB. For details, please see section 1.4.3 MySQL Licenses.

8.2 MySQL ODBC Support

MySQL provides support for ODBC by means of the MyODBC program. This chapter will teach you how to install MyODBC, and how to use it. Here, you will also find a list of common programs that are known to work with MyODBC.

8.2.1 How To Install MyODBC

MyODBC 2.50 is a 32-bit ODBC 2.50 specification level 0 (with level 1 and level 2 features) driver for connecting an ODBC-aware application to MySQL. MyODBC works on Windows 9x/Me/NT/2000/XP and most Unix platforms. MyODBC 3.51 is an enhanced version with ODBC 3.5x specification level 1 (complete core API + level 2 features).

MyODBC is Open Source, and you can find the newest version at http://www.mysql.com/downloads/api-myodbc.html. Please note that the 2.50.x versions are LGPL licensed, whereas the 3.51.x versions are GPL licensed.

If you have problem with MyODBC and your program also works with OLEDB, you should try the OLEDB driver.

Normally you only need to install MyODBC on Windows machines. You only need MyODBC for Unix if you have a program like ColdFusion that is running on the Unix machine and uses ODBC to connect to the databases.

If you want to install MyODBC on a Unix box, you will also need an ODBC manager. MyODBC is known to work with most of the Unix ODBC managers.

To install MyODBC on Windows, you should download the appropriate MyODBC `.zip' file, unpack it with WinZIP or some similar program, and execute the `SETUP.EXE' file.

On Windows/NT/XP you may get the following error when trying to install MyODBC:

An error occurred while copying C:\WINDOWS\SYSTEM\MFC30.DLL. Restart
Windows and try installing again (before running any applications which
use ODBC)

The problem in this case is that some other program is using ODBC and because of how Windows is designed, you may not in this case be able to install a new ODBC drivers with Microsoft's ODBC setup program. In most cases you can continue by just pressing Ignore to copy the rest of the MyODBC files and the final installation should still work. If this doesn't work, the solution is to reboot your computer in ``safe mode`` (Choose this by pressing F8 just before your machine starts Windows during rebooting), install MyODBC, and reboot to normal mode.

• To make a connection to a Unix box from a Windows box, with an ODBC application (one that doesn't support MySQL natively), you must first install MyODBC on the Windows machine.
• The user and Windows machine must have the access privileges to the MySQL server on the Unix machine. This is set up with the GRANT command. See section 4.3.1 GRANT and REVOKE Syntax.
• You must create an ODBC DSN entry as follows:
  • Open the Control Panel on the Windows machine.
  • Double-click the ODBC Data Sources 32-bit icon.
  • Click the tab User DSN.
  • Click the button Add.
  • Select MySQL in the screen Create New Data Source and click the Finish button.
  • The MySQL Driver default configuration screen is shown. See section 8.2.2 How to Fill in the Various Fields in the ODBC Administrator Program.
• Now start your application and select the ODBC driver with the DSN you specified in the ODBC administrator.

Notice that there are other configuration options on the screen of MySQL (trace, don't prompt on connect, etc) that you can try if you run into problems.

8.2.2 How to Fill in the Various Fields in the ODBC Administrator Program

There are three possibilities for specifying the server name on Windows95:

• Use the IP address of the server.
• Add a file `\windows\lmhosts' with the following information:

  ip hostname
  

  For example:

  194.216.84.21 my_hostname
  

• Configure the PC to use DNS.

Example of how to fill in the ODBC setup:

Windows DSN name:   test
Description:        This is my test database
MySql Database:     test
Server:             194.216.84.21
User:               monty
Password:           my_password
Port:

The value for the Windows DSN name field is any name that is unique in your Windows ODBC setup.

You don't have to specify values for the Server, User, Password, or Port fields in the ODBC setup screen. However, if you do, the values will be used as the defaults later when you attempt to make a connection. You have the option of changing the values at that time.

If the port number is not given, the default port (3306) is used.

If you specify the option Read options from C:\my.cnf, the groups client and odbc will be read from the `C:\my.cnf' file. You can use all options that are usable by mysql_options(). See section 8.1.3.163 mysql_options().

8.2.3 Connect parameters for MyODBC

One can specify the following parameters for MyODBC on the [Servername] section of an `ODBC.INI' file or through the InConnectionString argument in the SQLDriverConnect() call.

The option argument is used to tell MyODBC that the client isn't 100% ODBC compliant. On Windows, one normally sets the option flag by toggling the different options on the connection screen but one can also set this in the opton argument. The following options are listed in the same order as they appear in the MyODBC connect screen:

If you want to have many options, you should add the above flags! For example setting option to 12 (4+8) gives you debugging without package limits!

The default `MYODBC.DLL' is compiled for optimal performance. If you want to debug MyODBC (for example to enable tracing), you should instead use `MYODBCD.DLL'. To install this file, copy `MYODBCD.DLL' over the installed `MYODBC.DLL' file.

8.2.4 How to Report Problems with MyODBC

MyODBC has been tested with Access, Admndemo.exe, C++-Builder, Borland Builder 4, Centura Team Developer (formerly Gupta SQL/Windows), ColdFusion (on Solaris and NT with svc pack 5), Crystal Reports, DataJunction, Delphi, ERwin, Excel, iHTML, FileMaker Pro, FoxPro, Notes 4.5/4.6, SBSS, Perl DBD-ODBC, Paradox, Powerbuilder, Powerdesigner 32 bit, VC++, and Visual Basic.

If you know of any other applications that work with MyODBC, please send mail to myodbc@lists.mysql.com about this!

With some programs you may get an error like: Another user has modifies the record that you have modified. In most cases this can be solved by doing one of the following things:

• Add a primary key for the table if there isn't one already.
• Add a timestamp column if there isn't one already.
• Only use double float fields. Some programs may fail when they compare single floats.

If the above doesn't help, you should do a MyODBC trace file and try to figure out why things go wrong.

8.2.5 Programs Known to Work with MyODBC

Most programs should work with MyODBC, but for each of those listed here, we have tested it ourselves or received confirmation from some user that it works:

Program

Comment

Access

To make Access work:

• If you are using Access 2000, you should get and install the newest (version 2.6 or above) Microsoft MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/. This will fix the following bug in Access: when you export data to MySQL, the table and column names aren't specified. Another way to around this bug is to upgrade to MyODBC Version 2.50.33 and MySQL Version 3.23.x, which together provide a workaround for this bug! You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found here http://support.microsoft.com/support/kb/articles/Q 239/1/14.ASP. This will fix some cases where columns are marked as #deleted# in Access. Note that if you are using MySQL Version 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and above to go around this problem.
• For all Access versions, you should enable the MyODBC option flag Return matching rows. For Access 2.0, you should additionally enable Simulate ODBC 1.0.
• You should have a timestamp in all tables you want to be able to update. For maximum portability TIMESTAMP(14) or simple TIMESTAMP is recommended instead of other TIMESTAMP(X) variations.
• You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
• Only use DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can't find or update rows.
• If you are linking a table through MyODBC, which has BIGINT as one of the column, then the results will be displayed as #DELETED. The work around solution is:
  • Have one more dummy column with TIMESTAMP as the data type, preferably TIMESTAMP(14).
  • Check the 'Change BIGINT columns to INT' in connection options dialog in ODBC DSN Administrator
  • Delete the table link from access and re-create it.
  It still displays the previous records as #DELETED#, but newly added/updated records will be displayed properly.
• If you still get the error Another user has changed your data after adding a TIMESTAMP column, the following trick may help you: Don't use table data sheet view. Create instead a form with the fields you want, and use that form data sheet view. You should set the DefaultValue property for the TIMESTAMP column to NOW(). It may be a good idea to hide the TIMESTAMP column from view so your users are not confused.
• In some cases, Access may generate illegal SQL queries that MySQL can't understand. You can fix this by selecting "Query|SQLSpecific|Pass-Through" from the Access menu.
• Access on NT will report BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change the column to TEXT with ALTER TABLE.
• Access can't always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
• If you have in Access a column defined as BYTE, Access will try to export this as TINYINT instead of TINYINT UNSIGNED. This will give you problems if you have values > 127 in the column!

ADO

When you are coding with the ADO API and MyODBC you need to put attention in some default properties that aren't supported by the MySQL server. For example, using the CursorLocation Property as adUseServer will return for the RecordCount Property a result of -1. To have the right value, you need to set this property to adUseClient, like is showing in the VB code here:

Dim myconn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
Dim myrows As Long

myconn.Open "DSN=MyODBCsample"
mySQL = "SELECT * from user"
myrs.Source = mySQL
Set myrs.ActiveConnection = myconn
myrs.CursorLocation = adUseClient
myrs.Open
myrows = myrs.RecordCount

myrs.Close
myconn.Close

Another workaround is to use a SELECT COUNT(*) statement for a similar query to get the correct row count.

Active server pages (ASP)

You should use the option flag Return matching rows.

BDE applications

To get these to work, you should set the option flags Don't optimize column widths and Return matching rows.

Borland Builder 4

When you start a query you can use the property Active or use the method Open. Note that Active will start by automatically issuing a SELECT * FROM ... query that may not be a good thing if your tables are big!

ColdFusion (On Unix)

The following information is taken from the ColdFusion documentation: Use the following information to configure ColdFusion Server for Linux to use the unixODBC driver with MyODBC for MySQL data sources. Allaire has verified that MyODBC Version 2.50.26 works with MySQL Version 3.22.27 and ColdFusion for Linux. (Any newer version should also work.) You can download MyODBC at http://www.mysql.com/downloads/api-myodbc.html ColdFusion Version 4.5.1 allows you to us the ColdFusion Administrator to add the MySQL data source. However, the driver is not included with ColdFusion Version 4.5.1. Before the MySQL driver will appear in the ODBC datasources drop-down list, you must build and copy the MyODBC driver to `/opt/coldfusion/lib/libmyodbc.so'. The Contrib directory contains the program `mydsn-xxx.zip' which allows you to build and remove the DSN registry file for the MyODBC driver on Coldfusion applications.

DataJunction

You have to change it to output VARCHAR rather than ENUM, as it exports the latter in a manner that causes MySQL grief.

Excel

Works. A few tips:

• If you have problems with dates, try to select them as strings using the CONCAT() function. For example:

  select CONCAT(rise_time), CONCAT(set_time)
      from sunrise_sunset;
  

  Values retrieved as strings this way should be correctly recognised as time values by Excel97. The purpose of CONCAT() in this example is to fool ODBC into thinking the column is of ``string type''. Without the CONCAT(), ODBC knows the column is of time type, and Excel does not understand that. Note that this is a bug in Excel, because it automatically converts a string to a time. This would be great if the source was a text file, but is plain stupid when the source is an ODBC connection that reports exact types for each column.

Word

To retrieve data from MySQL to Word/Excel documents, you need to use the MyODBC driver and the Add-in Microsoft Query help. For example, create a db with a table containing 2 columns of text:

• Insert rows using the mysql client command-line tool.
• Create a DSN file using the ODBC manager, for example, `my' for the db above.
• Open the Word application.
• Create a blank new documentation.
• Using the tool bar called Database, press the button insert database.
• Press the button Get Data.
• At the right hand of the screen Get Data, press the button Ms Query.
• In the Ms Query create a New Data Source using the DSN file my.
• Select the new query.
• Select the columns that you want.
• Make a filter if you want.
• Make a Sort if you want.
• Select Return Data to Microsoft Word.
• Click Finish.
• Click Insert data and select the records.
• Click OK and you see the rows in your Word document.

odbcadmin

Test program for ODBC.

Delphi

You must use BDE Version 3.2 or newer. Set the Don't optimize column width option field when connecting to MySQL. Also, here is some potentially useful Delphi code that sets up both an ODBC entry and a BDE entry for MyODBC (the BDE entry requires a BDE Alias Editor that is free at a Delphi Super Page near you. (Thanks to Bryan Brunton bryan@flesherfab.com for this):

fReg:= TRegistry.Create;
  fReg.OpenKey('\Software\ODBC\ODBC.INI\DocumentsFab', True);
  fReg.WriteString('Database', 'Documents');
  fReg.WriteString('Description', ' ');
  fReg.WriteString('Driver', 'C:\WINNT\System32\myodbc.dll');
  fReg.WriteString('Flag', '1');
  fReg.WriteString('Password', '');
  fReg.WriteString('Port', ' ');
  fReg.WriteString('Server', 'xmark');
  fReg.WriteString('User', 'winuser');
  fReg.OpenKey('\Software\ODBC\ODBC.INI\ODBC Data Sources', True);
  fReg.WriteString('DocumentsFab', 'MySQL');
  fReg.CloseKey;
  fReg.Free;

  Memo1.Lines.Add('DATABASE NAME=');
  Memo1.Lines.Add('USER NAME=');
  Memo1.Lines.Add('ODBC DSN=DocumentsFab');
  Memo1.Lines.Add('OPEN MODE=READ/WRITE');
  Memo1.Lines.Add('BATCH COUNT=200');
  Memo1.Lines.Add('LANGDRIVER=');
  Memo1.Lines.Add('MAX ROWS=-1');
  Memo1.Lines.Add('SCHEMA CACHE DIR=');
  Memo1.Lines.Add('SCHEMA CACHE SIZE=8');
  Memo1.Lines.Add('SCHEMA CACHE TIME=-1');
  Memo1.Lines.Add('SQLPASSTHRU MODE=SHARED AUTOCOMMIT');
  Memo1.Lines.Add('SQLQRYMODE=');
  Memo1.Lines.Add('ENABLE SCHEMA CACHE=FALSE');
  Memo1.Lines.Add('ENABLE BCD=FALSE');
  Memo1.Lines.Add('ROWSET SIZE=20');
  Memo1.Lines.Add('BLOBS TO CACHE=64');
  Memo1.Lines.Add('BLOB SIZE=32');

  AliasEditor.Add('DocumentsFab','MySQL',Memo1.Lines);

C++ Builder

Tested with BDE Version 3.0. The only known problem is that when the table schema changes, query fields are not updated. BDE, however, does not seem to recognise primary keys, only the index PRIMARY, though this has not been a problem.

Vision

You should use the option flag Return matching rows.

Visual Basic

To be able to update a table, you must define a primary key for the table. Visual Basic with ADO can't handle big integers. This means that some queries like SHOW PROCESSLIST will not work properly. The fix is to set the option OPTION=16384 in the ODBC connect string or to set the Change BIGINT columns to INT option in the MyODBC connect screen. You may also want to set the Return matching rows option.

VisualInterDev

If you get the error [Microsoft][ODBC Driver Manager] Driver does not support this parameter the reason may be that you have a BIGINT in your result. Try setting the Change BIGINT columns to INT option in the MyODBC connect screen.

Visual Objects

You should use the option flag Don't optimize column widths.

8.2.6 How to Get the Value of an AUTO_INCREMENT Column in ODBC

A common problem is how to get the value of an automatically generated ID from an INSERT. With ODBC, you can do something like this (assuming that auto is an AUTO_INCREMENT field):

INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();

Or, if you are just going to insert the ID into another table, you can do this:

INSERT INTO foo (auto,text) VALUES(NULL,'text');
INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text');

See section 8.1.12.3 How Can I Get the Unique ID for the Last Inserted Row?.

For the benefit of some ODBC applications (at least Delphi and Access), the following query can be used to find a newly inserted row:

SELECT * FROM tbl_name WHERE auto IS NULL;

8.2.7 Reporting Problems with MyODBC

If you encounter difficulties with MyODBC, you should start by making a log file from the ODBC manager (the log you get when requesting logs from ODBCADMIN) and a MyODBC log.

To get a MyODBC log, you need to do the following:

1. Ensure that you are using `myodbcd.dll' and not `myodbc.dll'. The easiest way to do this is to get `myodbcd.dll' from the MyODBC distribution and copy it over the `myodbc.dll', which is probably in your `C:\windows\system32' or `C:\winnt\system32' directory. Note that you probably want to restore the old myodbc.dll file when you have finished testing, as this is a lot faster than `myodbcd.dll'.
2. Tag the `Trace MyODBC' option flag in the MyODBC connect/configure screen. The log will be written to file `C:\myodbc.log'. If the trace option is not remembered when you are going back to the above screen, it means that you are not using the myodbcd.dll driver (see the item above).
3. Start your application and try to get it to fail.

Check the MyODBC trace file, to find out what could be wrong. You should be able to find out the issued queries by searching after the string >mysql_real_query in the `myodbc.log' file.

You should also try duplicating the queries in the mysql monitor or admndemo to find out if the error is MyODBC or MySQL.

If you find out something is wrong, please only send the relevant rows (max 40 rows) to myodbc@lists.mysql.com. Please never send the whole MyODBC or ODBC log file!

If you are unable to find out what's wrong, the last option is to make an archive (tar or zip) that contains a MyODBC trace file, the ODBC log file, and a README file that explains the problem. You can send this to ftp://support.mysql.com/pub/mysql/secret/. Only we at MySQL AB will have access to the files you upload, and we will be very discrete with the data!

If you can create a program that also shows this problem, please upload this too!

If the program works with some other SQL server, you should make an ODBC log file where you do exactly the same thing in the other SQL server.

Remember that the more information you can supply to us, the more likely it is that we can fix the problem!

8.3 MySQL Java Connectivity (JDBC)

There are 2 supported JDBC drivers for MySQL:

• MySQL Connector/J from MySQL AB, implemented in 100% native Java. This product was formerly known as the mm.mysql driver. You can download MySQL Connector/J from http://www.mysql.com/products/connector-j/.
• The Resin JDBC driver, which can be found at http://www.caucho.com/projects/jdbc-mysql/index.xtp.

For documentation, consult any JDBC documentation, plus each driver's own documentation for MySQL-specific features.

8.4 MySQL PHP API

PHP is a server-side, HTML-embedded scripting language that may be used to create dynamic web pages. It contains support for accessing several databases, including MySQL. PHP may be run as a separate program or compiled as a module for use with the Apache web server.

The distribution and documentation are available at the PHP web site (http://www.php.net/).

8.4.1 Common Problems with MySQL and PHP

• Error: "Maximum Execution Time Exceeded" This is a PHP limit; go into the `php3.ini' file and set the maximum execution time up from 30 seconds to something higher, as needed. It is also not a bad idea to double the ram allowed per script to 16MB instead of 8 MB.
• Error: "Fatal error: Call to unsupported or undefined function mysql_connect() in .." This means that your PHP version isn't compiled with MySQL support. You can either compile a dynamic MySQL module and load it into PHP or recompile PHP with built-in MySQL support. This is described in detail in the PHP manual.
• Error: "undefined reference to `uncompress'" This means that the client library is compiled with support for a compressed client/server protocol. The fix is to add -lz last when linking with -lmysqlclient.

8.5 MySQL Perl API

This section documents the Perl DBI interface. The former interface was called mysqlperl. DBI/DBD now is the recommended Perl interface, so mysqlperl is obsolete and is not documented here.

8.5.1 DBI with DBD::mysql

DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql.

For more information on the Perl5 DBI, please visit the DBI web page and read the documentation:

http://dbi.perl.org/

For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page:

http://language.perl.com/info/documentation.html

Note that if you want to use transactions with Perl, you need to have Msql-Mysql-modules version 1.2216 or newer.

The recommended Perl module is DBD-mysql-2.1022 or newer.

Installation instructions for MySQL Perl support are given in section 2.7 Perl Installation Comments.

If you have the MySQL module installed, you can find information about specific MySQL functionallity with one of the following command

shell> perldoc DBD/mysql
shell> perldoc mysql

8.5.2 The DBI Interface

Portable DBI Methods

MySQL-specific Methods

The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:

$dbh

Database handle

$sth

Statement handle

$rc

Return code (often a status)

$rv

Return value (often a row count)

Portable DBI Methods

connect($data_source, $username, $password)

Use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example uses of connect with the DBD::mysql driver:

$dbh = DBI->connect("DBI:mysql:$database", $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname",
                    $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname:$port",
                    $user, $password);

If the user name and/or password are undefined, DBI uses the values of the DBI_USER and DBI_PASS environment variables, respectively. If you don't specify a hostname, it defaults to 'localhost'. If you don't specify a port number, it defaults to the default MySQL port (3306). As of Msql-Mysql-modules Version 1.2009, the $data_source value allows certain modifiers:

mysql_read_default_file=file_name

Read `filename' as an option file. For information on option files, see section 4.1.2 `my.cnf' Option Files.

mysql_read_default_group=group_name

The default group when reading an option file is normally the [client] group. By specifying the mysql_read_default_group option, the default group becomes the [group_name] group.

mysql_compression=1

Use compressed communication between the client and server (MySQL Version 3.22.3 or later).

mysql_socket=/path/to/socket

Specify the pathname of the Unix socket that is used to connect to the server (MySQL Version 3.21.15 or later).

Multiple modifiers may be given; each must be preceded by a semicolon. For example, if you want to avoid hardcoding the user name and password into a DBI script, you can take them from the user's `~/.my.cnf' option file instead by writing your connect call like this:

$dbh = DBI->connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
                $user, $password);

This call will read options defined for the [client] group in the option file. If you wanted to do the same thing but use options specified for the [perl] group as well, you could use this:

$dbh = DBI->connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
                . ";mysql_read_default_group=perl",
                $user, $password);

disconnect

The disconnect method disconnects the database handle from the database. This is typically called right before you exit from the program. Example:

$rc = $dbh->disconnect;

prepare($statement)

Prepares a SQL statement for execution by the database engine and returns a statement handle ($sth), which you can use to invoke the execute method. Typically you handle SELECT statements (and SELECT-like statements such as SHOW, DESCRIBE, and EXPLAIN) by means of prepare and execute. Example:

$sth = $dbh->prepare($statement)
    or die "Can't prepare $statement: $dbh->errstr\n";

If you want to read big results to your client you can tell Perl to use mysql_use_result() with:

my $sth = $dbh->prepare($statement { "mysql_use_result" => 1});

execute

The execute method executes a prepared statement. For non-SELECT statements, execute returns the number of rows affected. If no rows are affected, execute returns "0E0", which Perl treats as zero but regards as true. If an error occurs, execute returns undef. For SELECT statements, execute only starts the SQL query in the database; you need to use one of the fetch_* methods described here to retrieve the data. Example:

$rv = $sth->execute
          or die "can't execute the query: $sth->errstr;

do($statement)

The do method prepares and executes a SQL statement and returns the number of rows affected. If no rows are affected, do returns "0E0", which Perl treats as zero but regards as true. This method is generally used for non-SELECT statements that cannot be prepared in advance (due to driver limitations) or that do not need to be executed more than once (inserts, deletes, etc.). Example:

$rv = $dbh->do($statement)
        or die "Can't execute $statement: $dbh- >errstr\n";

Generally the 'do' statement is much faster (and is preferable) than prepare/execute for statements that don't contain parameters.

quote($string)

The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks. Example:

$sql = $dbh->quote($string)

fetchrow_array

This method fetches the next row of data and returns it as an array of field values. Example:

while(@row = $sth->fetchrow_array) {
        print qw($row[0]\t$row[1]\t$row[2]\n);
}

fetchrow_arrayref

This method fetches the next row of data and returns it as a reference to an array of field values. Example:

while($row_ref = $sth->fetchrow_arrayref) {
        print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n);
}

fetchrow_hashref

This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:

while($hash_ref = $sth->fetchrow_hashref) {
        print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\
                $hash_ref->{title}\n);
}

fetchall_arrayref

This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of references to arrays for each row. You access or print the data by using a nested loop. Example:

my $table = $sth->fetchall_arrayref
                or die "$sth->errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} ) {
        for $j ( 0 .. $#{$table->[$i]} ) {
                print "$table->[$i][$j]\t";
        }
        print "\n";
}

finish

Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources associated with it. Example:

$rc = $sth->finish;

rows

Returns the number of rows changed (updated, deleted, etc.) by the last command. This is usually used after a non-SELECT execute statement. Example:

$rv = $sth->rows;

NULLABLE

Returns a reference to an array of values that indicate whether columns may contain NULL values. The possible values for each array element are 0 or the empty string if the column cannot be NULL, 1 if it can, and 2 if the column's NULL status is unknown. Example:

$null_possible = $sth->{NULLABLE};

NUM_OF_FIELDS

This attribute indicates the number of fields returned by a SELECT or SHOW FIELDS statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE, or UPDATE. Example:

$nr_of_fields = $sth->{NUM_OF_FIELDS};

data_sources($driver_name)

This method returns an array containing names of databases available to the MySQL server on the host 'localhost'. Example:

@dbs = DBI->data_sources("mysql");

ChopBlanks

This attribute determines whether the fetchrow_* methods will chop leading and trailing blanks from the returned values. Example:

$sth->{'ChopBlanks'} =1;

trace($trace_level)

trace($trace_level, $trace_filename)

The trace method enables or disables tracing. When invoked as a DBI class method, it affects tracing for all handles. When invoked as a database or statement handle method, it affects tracing for the given handle (and any future children of the handle). Setting $trace_level to 2 provides detailed trace information. Setting $trace_level to 0 disables tracing. Trace output goes to the standard error output by default. If $trace_filename is specified, the file is opened in append mode and output for all traced handles is written to that file. Example:

DBI->trace(2);                # trace everything
DBI->trace(2,"/tmp/dbi.out"); # trace everything to
                              # /tmp/dbi.out
$dth->trace(2);               # trace this database handle
$sth->trace(2);               # trace this statement handle

You can also enable DBI tracing by setting the DBI_TRACE environment variable. Setting it to a numeric value is equivalent to calling DBI->(value). Setting it to a pathname is equivalent to calling DBI->(2,value).

MySQL-specific Methods

The methods shown here are MySQL-specific and not part of the DBI standard. Several of them are now deprecated: is_blob, is_key, is_num, is_pri_key, is_not_null, length, max_length, and table. Where DBI-standard alternatives exist, they are noted here:

insertid

If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented values will be stored here. Example:

$new_id = $sth->{insertid};

As an alternative, you can use $dbh->{'mysql_insertid'}.

is_blob

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a BLOB. Example:

$keys = $sth->{is_blob};

is_key

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:

$keys = $sth->{is_key};

is_num

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:

$nums = $sth->{is_num};

is_pri_key

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:

$pri_keys = $sth->{is_pri_key};

is_not_null

Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain NULL values. Example:

$not_nulls = $sth->{is_not_null};

is_not_null is deprecated; it is preferable to use the NULLABLE attribute (described above), because that is a DBI standard.

length

max_length

Each of these methods returns a reference to an array of column sizes. The length array indicates the maximum possible sizes that each column may be (as declared in the table description). The max_length array indicates the maximum sizes actually present in the result table. Example:

$lengths = $sth->{length};
$max_lengths = $sth->{max_length};

NAME

Returns a reference to an array of column names. Example:

$names = $sth->{NAME};

table

Returns a reference to an array of table names. Example:

$tables = $sth->{table};

type

Returns a reference to an array of column types. Example:

$types = $sth->{type};

8.5.3 More DBI/DBD Information

You can use the perldoc command to get more information about DBI.

perldoc DBI
perldoc DBI::FAQ
perldoc DBD::mysql

You can also use the pod2man, pod2html, etc., tools to translate to other formats.

You can find the latest DBI information at the DBI web page: http://dbi.perl.org/.

8.6 MySQL C++ API

MySQL Connector/C++ (or MySQL++) is the official MySQL API for C++. More information can be found at http://www.mysql.com/products/mysql++/.

8.6.1 Borland C++

You can compile the MySQL Windows source with Borland C++ 5.02. (The Windows source includes only projects for Microsoft VC++, for Borland C++ you have to do the project files yourself.)

One known problem with Borland C++ is that it uses a different structure alignment than VC++. This means that you will run into problems if you try to use the default libmysql.dll libraries (that was compiled with VC++) with Borland C++. You can do one of the following to avoid this problem.

• You can use the static MySQL libraries for Borland C++ that you can find on http://www.mysql.com/downloads/os-win32.html.
• Only call mysql_init() with NULL as an argument, not a pre-allocated MYSQL struct.

8.7 MySQL Python API

MySQLdb provides MySQL support for Python, compliant with the Python DB API version 2.0. It can be found at http://sourceforge.net/projects/mysql-python/.

8.8 MySQL Tcl API

MySQLtcl is a simple API for accessing a MySQL database server from the Tcl programming language. It can be found at http://www.xdobry.de/mysqltcl/.

8.9 MySQL Eiffel wrapper

Eiffel MySQL is an interface to the MySQL database server using the Eiffel programming language, written by Michael Ravits. It can be found at http://efsa.sourceforge.net/archive/ravits/mysql.htm.

9 Extending MySQL

9.1 MySQL Internals

This chapter describes a lot of things that you need to know when working on the MySQL code. If you plan to contribute to MySQL development, want to have access to the bleeding-edge in-between versions code, or just want to keep track of development, follow the instructions in section 2.3.4 Installing from the Development Source Tree. If you are interested in MySQL internals, you should also subscribe to our internals mailing list. This list is relatively low traffic. For details on how to subscribe, please see section 1.7.1.1 The MySQL Mailing Lists. All developers at MySQL AB are on the internals list and we help other people who are working on the MySQL code. Feel free to use this list both to ask questions about the code and to send patches that you would like to contribute to the MySQL project!

9.1.1 MySQL Threads

The MySQL server creates the following threads:

• The TCP/IP connection thread handles all connection requests and creates a new dedicated thread to handle the authentication and SQL query processing for each connection.
• On Windows NT there is a named pipe handler thread that does the same work as the TCP/IP connection thread on named pipe connect requests.
• The signal thread handles all signals. This thread also normally handles alarms and calls process_alarm() to force timeouts on connections that have been idle too long.
• If mysqld is compiled with -DUSE_ALARM_THREAD, a dedicated thread that handles alarms is created. This is only used on some systems where there are problems with sigwait() or if one wants to use the thr_alarm() code in ones application without a dedicated signal handling thread.
• If one uses the --flush_time=# option, a dedicated thread is created to flush all tables at the given interval.
• Every connection has its own thread.
• Every different table on which one uses INSERT DELAYED gets its own thread.
• If you use --master-host, a slave replication thread will be started to read and apply updates from the master.

mysqladmin processlist only shows the connection, INSERT DELAYED, and replication threads.

9.1.2 MySQL Test Suite

Until recently, our main full-coverage test suite was based on proprietary customer data and for that reason has not been publicly available. The only publicly available part of our testing process consisted of the crash-me test, a Perl DBI/DBD benchmark found in the sql-bench directory, and miscellaneous tests located in tests directory. The lack of a standardised publicly available test suite has made it difficult for our users, as well developers, to do regression tests on the MySQL code. To address this problem, we have created a new test system that is included in the source and binary distributions starting in Version 3.23.29.

The current set of test cases doesn't test everything in MySQL, but it should catch most obvious bugs in the SQL processing code, OS/library issues, and is quite thorough in testing replication. Our eventual goal is to have the tests cover 100% of the code. We welcome contributions to our test suite. You may especially want to contribute tests that examine the functionality critical to your system, as this will ensure that all future MySQL releases will work well with your applications.

9.1.2.1 Running the MySQL Test Suite

The test system consist of a test language interpreter (mysqltest), a shell script to run all tests(mysql-test-run), the actual test cases written in a special test language, and their expected results. To run the test suite on your system after a build, type make test or mysql-test/mysql-test-run from the source root. If you have installed a binary distribution, cd to the install root (eg. /usr/local/mysql), and do scripts/mysql-test-run. All tests should succeed. If not, you should try to find out why and report the problem if this is a bug in MySQL. See section 9.1.2.3 Reporting Bugs in the MySQL Test Suite.

If you have a copy of mysqld running on the machine where you want to run the test suite you do not have to stop it, as long as it is not using ports 9306 and 9307. If one of those ports is taken, you should edit mysql-test-run and change the values of the master and/or slave port to one that is available.

You can run one individual test case with mysql-test/mysql-test-run test_name.

If one test fails, you should test running mysql-test-run with the --force option to check if any other tests fails.

9.1.2.2 Extending the MySQL Test Suite

You can use the mysqltest language to write your own test cases. Unfortunately, we have not yet written full documentation for it - we plan to do this shortly. You can, however, look at our current test cases and use them as an example. The following points should help you get started:

• The tests are located in mysql-test/t/*.test
• A test case consists of ; terminated statements and is similar to the input of mysql command-line client. A statement by default is a query to be sent to MySQL server, unless it is recognised as internal command (eg. sleep).
• All queries that produce results@-e.g., SELECT, SHOW, EXPLAIN, etc., must be preceded with @/path/to/result/file. The file must contain the expected results. An easy way to generate the result file is to run mysqltest -r < t/test-case-name.test from mysql-test directory, and then edit the generated result files, if needed, to adjust them to the expected output. In that case, be very careful about not adding or deleting any invisible characters - make sure to only change the text and/or delete lines. If you have to insert a line, make sure the fields are separated with a hard tab, and there is a hard tab at the end. You may want to use od -c to make sure your text editor has not messed anything up during edit. We, of course, hope that you will never have to edit the output of mysqltest -r as you only have to do it when you find a bug.
• To be consistent with our setup, you should put your result files in mysql-test/r directory and name them test_name.result. If the test produces more than one result, you should use test_name.a.result, test_name.b.result, etc.
• If a statement returns an error, you should on the line before the statement specify with the --error error-number. The error number can be a list of possible error numbers separated with ','.
• If you are writing a replication test case, you should on the first line of the test file, put source include/master-slave.inc;. To switch between master and slave, use connection master; and connection slave;. If you need to do something on an alternate connection, you can do connection master1; for the master, and connection slave1; for the slave.
• If you need to do something in a loop, you can use something like this:

  let $1=1000;
  while ($1)
  {
   # do your queries here
   dec $1;
  }
  

• To sleep between queries, use the sleep command. It supports fractions of a second, so you can do sleep 1.3;, for example, to sleep 1.3 seconds.
• To run the slave with additional options for your test case, put them in the command-line format in mysql-test/t/test_name-slave.opt. For the master, put them in mysql-test/t/test_name-master.opt.
• If you have a question about the test suite, or have a test case to contribute, e-mail to internals@lists.mysql.com. As the list does not accept attachments, you should ftp all the relevant files to: ftp://support.mysql.com/pub/mysql/Incoming/

9.1.2.3 Reporting Bugs in the MySQL Test Suite

If your MySQL version doesn't pass the test suite you should do the following:

• Don't send a bug report before you have found out as much as possible of what when wrong! When you do it, please use the mysqlbug script so that we can get information about your system and MySQL version. See section 1.7.1.3 How to Report Bugs or Problems.
• Make sure to include the output of mysql-test-run, as well as contents of all .reject files in mysql-test/r directory.
• If a test in the test suite fails, check if the test fails also when run by its own:

  cd mysql-test
  mysql-test-run --local test-name
  

  If this fails, then you should configure MySQL with --with-debug and run mysql-test-run with the --debug option. If this also fails send the trace file `var/tmp/master.trace' to ftp://support.mysql.com/pub/mysql/secret so that we can examine it. Please remember to also include a full description of your system, the version of the mysqld binary and how you compiled it.
• Try also to run mysql-test-run with the --force option to see if there is any other test that fails.
• If you have compiled MySQL yourself, check our manual for how to compile MySQL on your platform or, preferable, use one of the binaries we have compiled for you at http://www.mysql.com/downloads/. All our standard binaries should pass the test suite !
• If you get an error, like Result length mismatch or Result content mismatch it means that the output of the test didn't match exactly the expected output. This could be a bug in MySQL or that your mysqld version produces slight different results under some circumstances. Failed test results are put in a file with the same base name as the result file with the .reject extension. If your test case is failing, you should do a diff on the two files. If you cannot see how they are different, examine both with od -c and also check their lengths.
• If a test fails totally, you should check the logs file in the mysql-test/var/log directory for hints of what went wrong.
• If you have compiled MySQL with debugging you can try to debug this by running mysql-test-run with the --gdb and/or --debug options. See section E.1.2 Creating Trace Files. If you have not compiled MySQL for debugging you should probably do that. Just specify the --with-debug options to configure! See section 2.3 Installing a MySQL Source Distribution.

9.2 Adding New Functions to MySQL

There are two ways to add new functions to MySQL:

• You can add the function through the user-definable function (UDF) interface. User-definable functions are added and removed dynamically using the CREATE FUNCTION and DROP FUNCTION statements. See section 9.2.1 CREATE FUNCTION/DROP FUNCTION Syntax.
• You can add the function as a native (built in) MySQL function. Native functions are compiled into the mysqld server and become available on a permanent basis.

Each method has advantages and disadvantages:

• If you write a user-definable function, you must install the object file in addition to the server itself. If you compile your function into the server, you don't need to do that.
• You can add UDFs to a binary MySQL distribution. Native functions require you to modify a source distribution.
• If you upgrade your MySQL distribution, you can continue to use your previously installed UDFs. For native functions, you must repeat your modifications each time you upgrade.

Whichever method you use to add new functions, they may be used just like native functions such as ABS() or SOUNDEX().

9.2.1 CREATE FUNCTION/DROP FUNCTION Syntax

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
       SONAME shared_library_name

DROP FUNCTION function_name

A user-definable function (UDF) is a way to extend MySQL with a new function that works like native (built in) MySQL functions such as ABS() and CONCAT().

AGGREGATE is a new option for MySQL Version 3.23. An AGGREGATE function works exactly like a native MySQL GROUP function like SUM or COUNT().

CREATE FUNCTION saves the function's name, type, and shared library name in the mysql.func system table. You must have the INSERT and DELETE privileges for the mysql database to create and drop functions.

All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialisation is skipped and UDFs are unavailable. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)

For instructions on writing user-definable functions, see section 9.2 Adding New Functions to MySQL. For the UDF mechanism to work, functions must be written in C or C++, your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

Note that to make AGGREGATE work, you must have a mysql.func table that contains the column type. If this is not the case, you should run the script mysql_fix_privilege_tables to get this fixed.

9.2.2 Adding a New User-definable Function

For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. The MySQL source distribution includes a file `sql/udf_example.cc' that defines 5 new functions. Consult this file to see how UDF calling conventions work.

For mysqld to be able to use UDF functions, you should configure MySQL with --with-mysqld-ldflags=-rdynamic The reason is that to on many platforms (including Linux) you can load a dynamic library (with dlopen()) from a static linked program, which you would get if you are using --with-mysqld-ldflags=-all-static If you want to use an UDF that needs to access symbols from mysqld (like the methaphone example in `sql/udf_example.cc' that uses default_charset_info), you must link the program with -rdynamic (see man dlopen).

For each function that you want to use in SQL statements, you should define corresponding C (or C++) functions. In the discussion below, the name ``xxx'' is used for an example function name. To distinquish between SQL and C/C++ usage, XXX() (uppercase) indicates a SQL function call, and xxx() (lowercase) indicates a C/C++ function call.

The C/C++ functions that you write to implement the interface for XXX() are:

xxx() (required)

The main function. This is where the function result is computed. The correspondence between the SQL type and return type of your C/C++ function is shown here:

SQL type	C/C++ type
STRING	char *
INTEGER	long long
REAL	double

xxx_init() (optional)

The initialisation function for xxx(). It can be used to:

• Check the number of arguments to XXX().
• Check that the arguments are of a required type or, alternatively, tell MySQL to coerce arguments to the types you want when the main function is called.
• Allocate any memory required by the main function.
• Specify the maximum length of the result.
• Specify (for REAL functions) the maximum number of decimals.
• Specify whether the result can be NULL.

xxx_deinit() (optional)

The deinitialisation function for xxx(). It should deallocate any memory allocated by the initialisation function.

When a SQL statement invokes XXX(), MySQL calls the initialisation function xxx_init() to let it perform any required setup, such as argument checking or memory allocation. If xxx_init() returns an error, the SQL statement is aborted with an error message and the main and deinitialisation functions are not called. Otherwise, the main function xxx() is called once for each row. After all rows have been processed, the deinitialisation function xxx_deinit() is called so it can perform any required cleanup.

For aggregate functions (like SUM()), you must also provide the following functions:

xxx_reset() (required)

Reset sum and insert the argument as the initial value for a new group.

xxx_add() (required)

Add the argument to the old sum.

When using aggregate UDF functions MySQL works the following way:

1. Call xxx_init() to let the aggregate function allocate the memory it will need to store results.
2. Sort the table according to the GROUP BY expression.
3. For the first row in a new group, call the xxx_reset() function.
4. For each new row that belongs in the same group, call the xxx_add() function.
5. When the group changes or after the last row has been processed, call xxx() to get the result for the aggregate.
6. Repeat 3-5 until all rows has been processed
7. Call xxx_deinit() to let the UDF free any memory it has allocated.

All functions must be thread-safe (not just the main function, but the initialisation and deinitialisation functions as well). This means that you are not allowed to allocate any global or static variables that change! If you need memory, you should allocate it in xxx_init() and free it in xxx_deinit().

9.2.2.1 UDF Calling Sequences for simple functions

The main function should be declared as shown here. Note that the return type and parameters differ, depending on whether you will declare the SQL function XXX() to return STRING, INTEGER, or REAL in the CREATE FUNCTION statement:

For STRING functions:

char *xxx(UDF_INIT *initid, UDF_ARGS *args,
          char *result, unsigned long *length,
          char *is_null, char *error);

For INTEGER functions:

long long xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

For REAL functions:

double xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

The initialisation and deinitialisation functions are declared like this:

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void xxx_deinit(UDF_INIT *initid);

The initid parameter is passed to all three functions. It points to a UDF_INIT structure that is used to communicate information between functions. The UDF_INIT structure members are listed below. The initialisation function should fill in any members that it wishes to change. (To use the default for a member, leave it unchanged.):

my_bool maybe_null

xxx_init() should set maybe_null to 1 if xxx() can return NULL. The default value is 1 if any of the arguments are declared maybe_null.

unsigned int decimals

Number of decimals. The default value is the maximum number of decimals in the arguments passed to the main function. (For example, if the function is passed 1.34, 1.345, and 1.3, the default would be 3, because 1.345 has 3 decimals.

unsigned int max_length

The maximum length of the string result. The default value differs depending on the result type of the function. For string functions, the default is the length of the longest argument. For integer functions, the default is 21 digits. For real functions, the default is 13 plus the number of decimals indicated by initid->decimals. (For numeric functions, the length includes any sign or decimal point characters.) If you want to return a blob, you can set this to 65K or 16M; this memory is not allocated but used to decide which column type to use if there is a need to temporary store the data.

char *ptr

A pointer that the function can use for its own purposes. For example, functions can use initid->ptr to communicate allocated memory between functions. In xxx_init(), allocate the memory and assign it to this pointer:

initid->ptr = allocated_memory;

In xxx() and xxx_deinit(), refer to initid->ptr to use or deallocate the memory.

9.2.2.2 UDF Calling Sequences for aggregate functions

Here follows a description of the different functions you need to define when you want to create an aggregate UDF function.

char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
                char *is_null, char *error);

This function is called when MySQL finds the first row in a new group. In the function you should reset any internal summary variables and then set the given argument as the first argument in the group.

In many cases this is implemented internally by reseting all variables and then calling xxx_add().

char *xxx_add(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

This function is called for all rows that belongs to the same group, except for the first row. In this you should add the value in UDF_ARGS to your internal summary variable.

The xxx() function should be declared identical as when you define a simple UDF function. See section 9.2.2.1 UDF Calling Sequences for simple functions.

This function is called when all rows in the group has been processed. You should normally never access the args variable here but return your value based on your internal summary variables.

All argument processing in xxx_reset() and xxx_add() should be done identically as for normal UDF functions. See section 9.2.2.3 Argument Processing.

The return value handling in xxx() should be done identically as for a normal UDF. See section 9.2.2.4 Return Values and Error Handling.

The pointer argument to is_null and error is the same for all calls to xxx_reset(), xxx_add() and xxx(). You can use this to remember that you got an error or if the xxx() function should return NULL. Note that you should not store a string into *error! This is just a 1 byte flag!

is_null is reset for each group (before calling xxx_reset(). error is never reset.

If isnull or error are set after xxx() then MySQL will return NULL as the result for the group function.

9.2.2.3 Argument Processing

The args parameter points to a UDF_ARGS structure that has the members listed here:

unsigned int arg_count

The number of arguments. Check this value in the initialisation function if you want your function to be called with a particular number of arguments. For example:

if (args->arg_count != 2)
{
    strcpy(message,"XXX() requires two arguments");
    return 1;
}

enum Item_result *arg_type

The types for each argument. The possible type values are STRING_RESULT, INT_RESULT, and REAL_RESULT. To make sure that arguments are of a given type and return an error if they are not, check the arg_type array in the initialisation function. For example:

if (args->arg_type[0] != STRING_RESULT ||
    args->arg_type[1] != INT_RESULT)
{
    strcpy(message,"XXX() requires a string and an integer");
    return 1;
}

As an alternative to requiring your function's arguments to be of particular types, you can use the initialisation function to set the arg_type elements to the types you want. This causes MySQL to coerce arguments to those types for each call to xxx(). For example, to specify coercion of the first two arguments to string and integer, do this in xxx_init():

args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;

char **args

args->args communicates information to the initialisation function about the general nature of the arguments your function was called with. For a constant argument i, args->args[i] points to the argument value. (See below for instructions on how to access the value properly.) For a non-constant argument, args->args[i] is 0. A constant argument is an expression that uses only constants, such as 3 or 4*7-2 or SIN(3.14). A non-constant argument is an expression that refers to values that may change from row to row, such as column names or functions that are called with non-constant arguments. For each invocation of the main function, args->args contains the actual arguments that are passed for the row currently being processed. Functions can refer to an argument i as follows:

• An argument of type STRING_RESULT is given as a string pointer plus a length, to allow handling of binary data or data of arbitrary length. The string contents are available as args->args[i] and the string length is args->lengths[i]. You should not assume that strings are null-terminated.
• For an argument of type INT_RESULT, you must cast args->args[i] to a long long value:

  long long int_val;
  int_val = *((long long*) args->args[i]);
  

• For an argument of type REAL_RESULT, you must cast args->args[i] to a double value:

  double    real_val;
  real_val = *((double*) args->args[i]);
  

unsigned long *lengths

For the initialisation function, the lengths array indicates the maximum string length for each argument. You should not change these. For each invocation of the main function, lengths contains the actual lengths of any string arguments that are passed for the row currently being processed. For arguments of types INT_RESULT or REAL_RESULT, lengths still contains the maximum length of the argument (as for the initialisation function).

9.2.2.4 Return Values and Error Handling

The initialisation function should return 0 if no error occurred and 1 otherwise. If an error occurs, xxx_init() should store a null-terminated error message in the message parameter. The message will be returned to the client. The message buffer is MYSQL_ERRMSG_SIZE characters long, but you should try to keep the message to less than 80 characters so that it fits the width of a standard terminal screen.

The return value of the main function xxx() is the function value, for long long and double functions. A string functions should return a pointer to the result and store the length of the string in the length arguments.

Set these to the contents and length of the return value. For example:

memcpy(result, "result string", 13);
*length = 13;

The result buffer that is passed to the calc function is 255 byte big. If your result fits in this, you don't have to worry about memory allocation for results.

If your string function needs to return a string longer than 255 bytes, you must allocate the space for it with malloc() in your xxx_init() function or your xxx() function and free it in your xxx_deinit() function. You can store the allocated memory in the ptr slot in the UDF_INIT structure for reuse by future xxx() calls. See section 9.2.2.1 UDF Calling Sequences for simple functions.

To indicate a return value of NULL in the main function, set is_null to 1:

*is_null = 1;

To indicate an error return in the main function, set the error parameter to 1:

*error = 1;

If xxx() sets *error to 1 for any row, the function value is NULL for the current row and for any subsequent rows processed by the statement in which XXX() was invoked. (xxx() will not even be called for subsequent rows.) Note: in MySQL versions prior to 3.22.10, you should set both *error and *is_null:

*error = 1;
*is_null = 1;

9.2.2.5 Compiling and Installing User-definable Functions

Files implementing UDFs must be compiled and installed on the host where the server runs. This process is described below for the example UDF file `udf_example.cc' that is included in the MySQL source distribution. This file contains the following functions:

• metaphon() returns a metaphon string of the string argument. This is something like a soundex string, but it's more tuned for English.
• myfunc_double() returns the sum of the ASCII values of the characters in its arguments, divided by the sum of the length of its arguments.
• myfunc_int() returns the sum of the length of its arguments.
• sequence([const int]) returns an sequence starting from the given number or 1 if no number has been given.
• lookup() returns the IP number for a hostname.
• reverse_lookup() returns the hostname for an IP number. The function may be called with a string "xxx.xxx.xxx.xxx" or four numbers.

A dynamically loadable file should be compiled as a sharable object file, using a command something like this:

shell> gcc -shared -o udf_example.so myfunc.cc

You can easily find out the correct compiler options for your system by running this command in the `sql' directory of your MySQL source tree:

shell> make udf_example.o

You should run a compile command similar to the one that make displays, except that you should remove the -c option near the end of the line and add -o udf_example.so to the end of the line. (On some systems, you may need to leave the -c on the command.)

Once you compile a shared object containing UDFs, you must install it and tell MySQL about it. Compiling a shared object from `udf_example.cc' produces a file named something like `udf_example.so' (the exact name may vary from platform to platform). Copy this file to some directory searched by ld, such as `/usr/lib'. On many systems, you can set the LD_LIBRARY or LD_LIBRARY_PATH environment variable to point at the directory where you have your UDF function files. The dlopen manual page tells you which variable you should use on your system. You should set this in mysql.server or safe_mysqld startup scripts and restart mysqld.

After the library is installed, notify mysqld about the new functions with these commands:

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME "udf_example.so";
mysql> CREATE AGGREGATE FUNCTION avgcost
    ->        RETURNS REAL SONAME "udf_example.so";

Functions can be deleted using DROP FUNCTION:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;

The CREATE FUNCTION and DROP FUNCTION statements update the system table func in the mysql database. The function's name, type and shared library name are saved in the table. You must have the INSERT and DELETE privileges for the mysql database to create and drop functions.

You should not use CREATE FUNCTION to add a function that has already been created. If you need to reinstall a function, you should remove it with DROP FUNCTION and then reinstall it with CREATE FUNCTION. You would need to do this, for example, if you recompile a new version of your function, so that mysqld gets the new version. Otherwise, the server will continue to use the old version.

Active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialisation is skipped and UDFs are unavailable. (An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION.)

9.2.3 Adding a New Native Function

The procedure for adding a new native function is described here. Note that you cannot add native functions to a binary distribution because the procedure involves modifying MySQL source code. You must compile MySQL yourself from a source distribution. Also note that if you migrate to another version of MySQL (for example, when a new version is released), you will need to repeat the procedure with the new version.

To add a new native MySQL function, follow these steps:

1. Add one line to `lex.h' that defines the function name in the sql_functions[] array.
2. If the function prototype is simple (just takes zero, one, two or three arguments), you should in lex.h specify SYM(FUNC_ARG#) (where # is the number of arguments) as the second argument in the sql_functions[] array and add a function that creates a function object in `item_create.cc'. Take a look at "ABS" and create_funcs_abs() for an example of this. If the function prototype is complicated (for example takes a variable number of arguments), you should add two lines to `sql_yacc.yy'. One indicates the preprocessor symbol that yacc should define (this should be added at the beginning of the file). Then define the function parameters and add an ``item'' with these parameters to the simple_expr parsing rule. For an example, check all occurrences of ATAN in `sql_yacc.yy' to see how this is done.
3. In `item_func.h', declare a class inheriting from Item_num_func or Item_str_func, depending on whether your function returns a number or a string.
4. In `item_func.cc', add one of the following declarations, depending on whether you are defining a numeric or string function:

   double   Item_func_newname::val()
   longlong Item_func_newname::val_int()
   String  *Item_func_newname::Str(String *str)
   

   If you inherit your object from any of the standard items (like Item_num_func you probably only have to define one of the above functions and let the parent object take care of the other functions. For example, the Item_str_func class defines a val() function that executes atof() on the value returned by ::str().
5. You should probably also define the following object function:

   void Item_func_newname::fix_length_and_dec()
   

   This function should at least calculate max_length based on the given arguments. max_length is the maximum number of characters the function may return. This function should also set maybe_null = 0 if the main function can't return a NULL value. The function can check if any of the function arguments can return NULL by checking the arguments maybe_null variable. You can take a look at Item_func_mod::fix_length_and_dec for a typical example of how to do this.

All functions must be thread-safe (in other words, don't use any global or static variables in the functions without protecting them with mutexes).

If you want to return NULL, from ::val(), ::val_int() or ::str() you should set null_value to 1 and return 0.

For ::str() object functions, there are some additional considerations to be aware of:

• The String *str argument provides a string buffer that may be used to hold the result. (For more information about the String type, take a look at the `sql_string.h' file.)
• The ::str() function should return the string that holds the result or (char*) 0 if the result is NULL.
• All current string functions try to avoid allocating any memory unless absolutely necessary!

9.3 Adding New Procedures to MySQL

In MySQL, you can define a procedure in C++ that can access and modify the data in a query before it is sent to the client. The modification can be done on row-by-row or GROUP BY level.

We have created an example procedure in MySQL Version 3.23 to show you what can be done.

Additionally we recommend you to take a look at mylua. With this you can use the LUA language to load a procedure at runtime into mysqld.

9.3.1 Procedure Analyse

analyse([max elements,[max memory]])

This procedure is defined in the `sql/sql_analyse.cc'. This examines the result from your query and returns an analysis of the results:

• max elements (default 256) is the maximum number of distinct values analyse will notice per column. This is used by analyse to check if the optimal column type should be of type ENUM.
• max memory (default 8192) is the maximum memory analyse should allocate per column while trying to find all distinct values.

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

9.3.2 Writing a Procedure

For the moment, the only documentation for this is the source.

You can find all information about procedures by examining the following files:

• `sql/sql_analyse.cc'
• `sql/procedure.h'
• `sql/procedure.cc'
• `sql/sql_select.cc'

A Problems and Common Errors

This chapter lists some common problems and error messages that users have run into. You will learn how to figure out what the problem is, and what to do to solve it. You will also find proper solutions to some common problems.

A.1 How to Determine What Is Causing Problems

When you run into problems, the first thing you should do is to find out which program / piece of equipment is causing problems:

• If you have one of the following symptoms, then it is probably a hardware (like memory, motherboard, CPU, or hard disk) or kernel problem:
  • The keyboard doesn't work. This can normally be checked by pressing Caps Lock. If the Caps Lock light doesn't change you have to replace your keyboard. (Before doing this, you should try to reboot your computer and check all cables to the keyboard.)
  • The mouse pointer doesn't move.
  • The machine doesn't answer to a remote machine's pings.
  • Different, unrelated programs don't behave correctly.
  • If your system rebooted unexpectedly (a faulty user level program should never be able to take down your system).
  In this case you should start by checking all your cables and run some diagnostic tool to check your hardware! You should also check if there are any patches, updates, or service packs for your operating system that could likely solve your problems. Check also that all your libraries (like glibc) are up to date. It's always good to use a machine with ECC memory to discover memory problems early!
• If your keyboard is locked up, you may be able to fix this by logging into your machine from another machine and execute kbd_mode -a on it.
• Please examine your system log file (/var/log/messages or similar) for reasons for your problems. If you think the problem is in MySQL then you should also examine MySQL's log files. See section 4.9.4 The Binary Log.
• If you don't think you have hardware problems, you should try to find out which program is causing problems. Try using top, ps, taskmanager, or some similar program, to check which program is taking all CPU or is locking the machine.
• Check with top, df, or a similar program if you are out of memory, disk space, open files, or some other critical resource.
• If the problem is some runaway process, you can always try to kill it. If it doesn't want to die, there is probably a bug in the operating system.

If after you have examined all other possibilities and you have concluded that it's the MySQL server or a MySQL client that is causing the problem, it's time to do a bug report for our mailing list or our support team. In the bug report, try to give a very detailed description of how the system is behaving and what you think is happening. You should also state why you think it's MySQL that is causing the problems. Take into consideration all the situations in this chapter. State any problems exactly how they appear when you examine your system. Use the 'cut and paste' method for any output and/or error messages from programs and/or log files!

Try to describe in detail which program is not working and all symptoms you see! We have in the past received many bug reports that just state "the system doesn't work". This doesn't provide us with any information about what could be the problem.

If a program fails, it's always useful to know:

• Has the program in question made a segmentation fault (core dumped)?
• Is the program taking up the whole CPU? Check with top. Let the program run for a while, it may be evaluating something heavy.
• If it's the mysqld server that is causing problems, can you do mysqladmin -u root ping or mysqladmin -u root processlist?
• What does a client program say (try with mysql, for example) when you try to connect to the MySQL server? Does the client jam? Do you get any output from the program?

When sending a bug report, you should of follow the outlines described in this manual. See section 1.7.1.2 Asking Questions or Reporting Bugs.

A.2 Common Errors When Using MySQL

This section lists some errors that users frequently get. You will find descriptions of the errors, and how to solve the problem here.

A.2.1 Access denied Error

See section 4.2.11 Causes of Access denied Errors. See section 4.2.6 How the Privilege System Works.

A.2.2 MySQL server has gone away Error

This section also covers the related Lost connection to server during query error.

The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld.

Another common reason to receive the MySQL server has gone away error is because you have issued a ``close'' on your MySQL connection and then tried to run a query on the closed connection.

If you have a script, you just have to issue the query again for the client to do an automatic reconnection.

You normally can get the following error codes in this case (which one you get is OS-dependent):

You will also get this error if someone has kills the running thread with kill #threadid#.

You can check that the MySQL hasn't died by executing mysqladmin version and examining the uptime. If the problem is that mysqld crashed you should concentrate one finding the reason for the crash. You should in this case start by checking if issuing the query again will kill MySQL again. See section A.4.1 What To Do If MySQL Keeps Crashing.

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by starting mysqld with the -O max_allowed_packet=# option (default 1M). The extra memory is allocated on demand, so mysqld will allocate more memory only when you issue a big query or when mysqld must return a big result row!

You will also get a lost connection if you are sending a packet >= 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around.

If you want to make a bug report regarding this problem, be sure that you include the following information:

• Include information if MySQL died or not. (You can find this in the hostname.err file. See section A.4.1 What To Do If MySQL Keeps Crashing.
• If a specific query kills mysqld and the involved tables where checked with CHECK TABLE before you did the query, can you do a test case for this? See section E.1.6 Making a Test Case If You Experience Table Corruption.
• What is the value of the wait_timeout variable in the MySQL server ? mysqladmin variables gives you the value of this
• Have you tried to run mysqld with --log and check if the issued query appears in the log ?

See section 1.7.1.2 Asking Questions or Reporting Bugs.

A.2.3 Can't connect to [local] MySQL server Error

A MySQL client on Unix can connect to the mysqld server in two different ways: Unix sockets, which connect through a file in the file system (default `/tmp/mysqld.sock') or TCP/IP, which connects through a port number. Unix sockets are faster than TCP/IP but can only be used when connecting to a server on the same computer. Unix sockets are used if you don't specify a hostname or if you specify the special hostname localhost.

On Windows, if the mysqld server is running on 9x/Me, you can connect only via TCP/IP. If the server is running on NT/2000/XP and mysqld is started with --enable-named-pipe, you can also connect with named pipes. The name of the named pipe is MySQL. If you don't give a hostname when connecting to mysqld, a MySQL client will first try to connect to the named pipe, and if this doesn't work it will connect to the TCP/IP port. You can force the use of named pipes on Windows by using . as the hostname.

The error (2002) Can't connect to ... normally means that there isn't a MySQL server running on the system or that you are using a wrong socket file or TCP/IP port when trying to connect to the mysqld server.

Start by checking (using ps or the task manager on Windows) that there is a process running named mysqld on your server! If there isn't any mysqld process, you should start one. See section 2.4.2 Problems Starting the MySQL Server.

If a mysqld process is running, you can check the server by trying these different connections (the port number and socket pathname might be different in your setup, of course):

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h 'ip for your host' version
shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

Note the use of backquotes rather than forward quotes with the hostname command; these cause the output of hostname (that is, the current hostname) to be substituted into the mysqladmin command.

Here are some reasons the Can't connect to local MySQL server error might occur:

• mysqld is not running.
• You are running on a system that uses MIT-pthreads. If you are running on a system that doesn't have native threads, mysqld uses the MIT-pthreads package. See section 2.2.5 Operating Systems Supported by MySQL. However, not all MIT-pthreads versions support Unix sockets. On a system without sockets support you must always specify the hostname explicitly when connecting to the server. Try using this command to check the connection to the server:

  shell> mysqladmin -h `hostname` version
  

• Someone has removed the Unix socket that mysqld uses (default `/tmp/mysqld.sock'). You might have a cron job that removes the MySQL socket (for example, a job that removes old files from the `/tmp' directory). You can always run mysqladmin version and check that the socket mysqladmin is trying to use really exists. The fix in this case is to change the cron job to not remove `mysqld.sock' or to place the socket somewhere else. See section A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
• You have started the mysqld server with the --socket=/path/to/socket option. If you change the socket pathname for the server, you must also notify the MySQL clients about the new path. You can do this by providing the socket path as an argument to the client. See section A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'.
• You are using Linux and one thread has died (core dumped). In this case you must kill the other mysqld threads (for example, with the mysql_zap script before you can start a new MySQL server. See section A.4.1 What To Do If MySQL Keeps Crashing.
• You may not have read and write privilege to either the directory that holds the socket file or privilege to the socket file itself. In this case you have to either change the privilege for the directory / file or restart mysqld so that it uses a directory that you can access.

If you get the error message Can't connect to MySQL server on some_hostname, you can try the following things to find out what the problem is :

• Check if the server is up by doing telnet your-host-name tcp-ip-port-number and press Enter a couple of times. If there is a MySQL server running on this port you should get a responses that includes the version number of the running MySQL server. If you get an error like telnet: Unable to connect to remote host: Connection refused, then there is no server running on the given port.
• Try connecting to the mysqld daemon on the local machine and check the TCP/IP port that mysqld it's configured to use (variable port) with mysqladmin variables.
• Check that your mysqld server is not started with the --skip-networking option.

A.2.4 Host '...' is blocked Error

If you get an error like this:

Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

this means that mysqld has gotten a lot (max_connect_errors) of connect requests from the host 'hostname' that have been interrupted in the middle. After max_connect_errors failed requests, mysqld assumes that something is wrong (like an attack from a cracker), and blocks the site from further connections until someone executes the command mysqladmin flush-hosts.

By default, mysqld blocks a host after 10 connection errors. You can easily adjust this by starting the server like this:

shell> safe_mysqld -O max_connect_errors=10000 &

Note that if you get this error message for a given host, you should first check that there isn't anything wrong with TCP/IP connections from that host. If your TCP/IP connections aren't working, it won't do you any good to increase the value of the max_connect_errors variable!

A.2.5 Too many connections Error

If you get the error Too many connections when you try to connect to MySQL, this means that there is already max_connections clients connected to the mysqld server.

If you need more connections than the default (100), then you should restart mysqld with a bigger value for the max_connections variable.

Note that mysqld actually allows (max_connections+1) clients to connect. The last connection is reserved for a user with the SUPER privilege. By not giving this privilege to normal users (they shouldn't need this), an administrator with this privilege can log in and use SHOW PROCESSLIST to find out what could be wrong. See section 4.5.6.6 SHOW PROCESSLIST.

The maximum number of connects MySQL is depending on how good the thread library is on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing.

A.2.6 Some non-transactional changed tables couldn't be rolled back Error

If you get the error/warning: Warning: Some non-transactional changed tables couldn't be rolled back when trying to do a ROLLBACK, this means that some of the tables you used in the transaction didn't support transactions. These non-transactional tables will not be affected by the ROLLBACK statement.

The most typical case when this happens is when you have tried to create a table of a type that is not supported by your mysqld binary. If mysqld doesn't support a table type (or if the table type is disabled by a startup option) , it will instead create the table type with the table type that is most resembles to the one you requested, probably MyISAM.

You can check the table type for a table by doing:

SHOW TABLE STATUS LIKE 'table_name'. See section 4.5.6.2 SHOW TABLE STATUS.

You can check the extensions your mysqld binary supports by doing:

show variables like 'have_%'. See section 4.5.6.4 SHOW VARIABLES.

A.2.7 Out of memory Error

If you issue a query and get something like the following error:

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

note that the error refers to the MySQL client mysql. The reason for this error is simply that the client does not have enough memory to store the whole result.

To remedy the problem, first check that your query is correct. Is it reasonable that it should return so many rows? If so, you can use mysql --quick, which uses mysql_use_result() to retrieve the result set. This places less of a load on the client (but more on the server).

A.2.8 Packet too large Error

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

In MySQL 3.23 the biggest possible packet is 16M (due to limits in the client/server protocol). In MySQL 4.0.1 and up, this is only limited by the amount on memory you have on your server (up to a theoretical maximum of 2G).

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client.

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. With some clients, you may also get Lost connection to MySQL server during query error if the communication packet is too big.

Note that both the client and the server has it's own max_allowed_packet variable. If you want to handle big packets, you have to increase this variable both in the client and in the server.

It's safe to increase this variable as memory is only allocated when needed; this variable is more a precaution to catch wrong packets between the client/server and also to ensure that you don't accidentally use big packets so that you run out of memory.

If you are using the mysql client, you may specify a bigger buffer by starting the client with mysql --set-variable=max_allowed_packet=8M. Other clients have different methods to set this variable. Please note that --set-variable is deprecated since MySQL 4.0, just use --max-allowed-packet=8M instead. You can use the option file to set max_allowed_packet to a larger size in mysqld. For example, if you are expecting to store the full length of a MEDIUMBLOB into a table, you'll need to start the server with the set-variable=max_allowed_packet=16M option.

You can also get strange problems with large packets if you are using big blobs, but you haven't given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the safe_mysqld script and restart mysqld.

A.2.9 Communication Errors / Aborted Connection

Starting with MySQL 3.23.40 you only get the Aborted connection error of you start mysqld with --warnings.

If you find errors like the following in your error log.

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

See section 4.9.1 The Error Log.

This means that something of the following has happened:

• The client program did not call mysql_close() before exit.
• The client had been sleeping more than wait_timeout or interactive_timeout without doing any requests. See section 4.5.6.4 SHOW VARIABLES. See section 4.5.6.4 SHOW VARIABLES.
• The client program ended abruptly in the middle of the transfer.

When the above happens, the server variable Aborted_clients is incremented.

The server variable Aborted_connects is incremented when:

• When a connection packet doesn't contain the right information.
• When the user didn't have privileges to connect to a database.
• When a user uses a wrong password.
• When it takes more than connect_timeout seconds to get a connect package. See section 4.5.6.4 SHOW VARIABLES.

Note that the above could indicate that someone is trying to break into your database!

Other reasons for problems with Aborted clients / Aborted connections.

• Usage of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via ftp between these two machines. If a transfer goes in burst-pause-burst-pause ... mode then you are experiencing a Linux duplex syndrome. The only solution is switching duplex mode for both your network card and Hub/Switch to either full duplex or to half duplex and testing the results to decide on the best setting.
• Some problem with the thread library that causes interrupts on reads.
• Badly configured TCP/IP.
• Faulty Ethernets or hubs or switches, cables ... This can be diagnosed properly only by replacing hardware.
• max_allowed_packet is too small or queries require more memory than you have alloacated for mysqld. See section A.2.8 Packet too large Error.

A.2.10 The table is full Error

There is a couple of different cases when you can get this error:

• You are using an older MySQL version (before 3.23.0) when an in-memory temporary table becomes larger than tmp_table_size bytes. To avoid this problem, you can use the -O tmp_table_size=# option to make mysqld increase the temporary table size or use the SQL option BIG_TABLES before you issue the problematic query. See section 5.5.6 SET Syntax. You can also start mysqld with the --big-tables option. This is exactly the same as using BIG_TABLES for all queries. In MySQL Version 3.23, in-memory temporary tables will automatically be converted to a disk-based MyISAM table after the table size gets bigger than tmp_table_size.
• You are using InnoDB tables and run out of room in the InnoDB tablespace. In this case the solution is to extend the InnoDB tablespace.
• You are using ISAM or MyISAM tables on an OS that only supports files of 2G in size and you have hit this limit for the data or index file.
• You are using MyISAM tables and the needed data or index size is bigger than what MySQL has allocated pointers for. (If you don't specify MAX_ROWS to CREATE TABLE MySQL will only allocate pointers to hold 4G of data). You can check the maximum data/index sizes by doing

  SHOW TABLE STATUS FROM database LIKE 'table_name';
  

  or using myisamchk -dv database/table_name. If this is the problem, you can fix it by doing something like:

  ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
  

  You only have to specify AVG_ROW_LENGTH for tables with BLOB/TEXT fields as in this case MySQL can't optimise the space required based only on the number of rows.

A.2.11 Can't create/write to file Error

If you get an error for some queries of type:

Can't create/write to file '\\sqla3fe_0.ism'.

this means that MySQL can't create a temporary file for the result set in the given temporary directory. (The above error is a typical error message on Windows, and the Unix error message is similar.) The fix is to start mysqld with --tmpdir=path or to add to your option file:

[mysqld]
tmpdir=C:/temp

assuming that the `c:\\temp' directory exists. See section 4.1.2 `my.cnf' Option Files.

Check also the error code that you get with perror. One reason may also be a disk full error;

shell> perror 28
Error code  28:  No space left on device

A.2.12 Commands out of sync Error in Client

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order!

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without a mysql_use_result() or mysql_store_result() in between.

A.2.13 Ignoring user Error

If you get the following error:

Found wrong password for user: 'some_user@some_host'; ignoring user

this means that when mysqld was started or when it reloaded the permissions tables, it found an entry in the user table with an invalid password. As a result, the entry is simply ignored by the permission system.

Possible causes of and fixes for this problem:

• You may be running a new version of mysqld with an old user table. You can check this by executing mysqlshow mysql user to see if the password field is shorter than 16 characters. If so, you can correct this condition by running the scripts/add_long_password script.
• The user has an old password (8 characters long) and you didn't start mysqld with the --old-protocol option. Update the user in the user table with a new password or restart mysqld with --old-protocol.
• You have specified a password in the user table without using the PASSWORD() function. Use mysql to update the user in the user table with a new password. Make sure to use the PASSWORD() function:

  mysql> UPDATE user SET password=PASSWORD('your password')
      ->             WHERE user='XXX';
  

A.2.14 Table 'xxx' doesn't exist Error

If you get the error Table 'xxx' doesn't exist or Can't find file: 'xxx' (errno: 2), this means that no table exists in the current database with the name xxx.