7.5.14 Historique de l'évolution InnoDB

Note : la traduction de cette section va commencer au moment du démarrage de la traduction générale. Nous mettrons en version française les nouvelles entrées. Les anciennes versions seront traduites en fonction du temps disponible.

• InnoDB supporte désormais le niveau d'isolation de transaction transaction READ COMMITTED et READ UNCOMMITTED. READ COMMITTED émule plus excactement Oracle et rend le portage des applications depuis Oracle bien plus faciles.
• La résolution des blocages de verrou est devenue sélective : nous essayons de choisir les transactions qui ont modifié le moins de ligne jusqu'à présent.
• La définition des FOREIGN KEY prend en compte l'option lower_case_table_names de my.cnf .
• La ocmmande SHOW CREATE TABLE n'affiche pas le nom de la base dans une définition de FOREIGN KEY si la table identifié est dans la même base que celle de la table principale.
• InnoDB fait un texte de cohérence pour la plupart des pages d'index avant de les écrire dans le fichier de données.
• Si vous utilisez innodb_force_recovery > 0, InnoDB essaie de passer outre les lignest et pages d'index corrompus, lors d'une commande SELECT * FROM dans une table. Cela aide lors de l'export.
• InnoDB utilise à nouveau les accès disques asynchrones sans buffer avec Windows 2000 et XP; seulement des accès disques avec simulation d'asynchronisme non bufferisé avec NT, 95/98/ME.
• Correction de bogue : l'estimateur de taille InnoDB exagérait grandement la taille d'un intervalle court d'index, si les chemins jusqu'au point de fin dans l'arbre d'index était relié à la racine. Cela conduisait à des scans de tables inutiles. La correction sera reportée à la version 3.23.54.
• Correction d'un bogue présent dans les versions 3.23.52, 4.0.3, 4.0.4 : Le démarrage d'InnoDB pouvait prendre beaucoup de temps, et même crasher sur Windows 95/98/ME.
• Correction d'un bogue : le verrou AUTO-INC était conservé jusqu'à la fin de la transaction si il avait été donné après une attete. Cela conduisait à des blocages inutiles.
• Correction d'un bogue : Si SHOW INNODB STATUS, innodb_monitor, ou innodb_lock_monitor devait afficher plusieurs centaines de transactions en un seul rapport, le résultat était tronqué, InnoDB s'arrêtait, affichait une erreur dans le log d'erreurs : log many waits for a mutex created at srv0srv.c, line 1621.
• Correction d'un bogue : SHOW INNODB STATUS sous Unix annoncait toujours une taille de lectures de fichier moyenne de 0 octets.
• Correction d'un bogue potentiel en 4.0.4: InnoDB fait désormais des clauses ORDER BY ... DESC comme MyISAM .
• Correction d'un bogue : DROP TABLE pour causer un crash ou un arrêt si il y avait des annulations simultanées sur la même table. La correction sera appliquée à la version 3.23 si cela apparaît comme un véritable problème.
• Correction d'un bogue : ORDER BY pouvait échouer, si vous n'aviez pas de clé primaire sur la table, mais que vous aviez défini plusieurs index, parmi lesquels au moins un index UNIQUE, et toutes les colonnes étaient NOT NULL.
• Correction d'un bogue : une attente de verrou expirée sur une connexion avec la commande ON DELETE CASCADE pouvait conduire à la corruption de la base.
• Correction d'un bogue : si une commande SELECT était faite avec une clé unique sur un index primaire, et que la recherche trouvait une ligne marquée pour l'effacement, InnoDB pouvait retourner la ligne suivante.
• Bug identifié : en 4.0.4, deux bogues ont été introduits avec les colonnes de type AUTO_INCREMENT . REPLACE peut faire que le compteur est corrompu, d'une unité sous la vraie valeur. Un blocage de verrous ou une expiration de pose de verrou peut causer le même problème. Cela sera corrigé en 4.0.6.

• 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 bogue 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.
• Correction d'un bogue : fast shutdown (which is the default) sometimes was slowed down by purge and insert buffer merge.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : if you created a temporary table inside LOCK TABLES, and used that temporary table, that caused an assertion failure in ha_innobase.cc.
• Correction d'un bogue : 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.
• Correction d'un bogue : SHOW INNODB STATUS on Unix always reported average file read size as 0 bytes.

• 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.
• Correction d'un bogue : Using ORDER BY primarykey DESC in 4.0.3 causes an assertion failure in btr0pcur.c, line 203.
• Correction d'un bogue : fast shutdown (which is the default) sometimes was slowed down by purge and insert buffer merge.
• Correction d'un bogue : 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.
• Correction d'un bogue : if the MySQL query cache was used, it did not get invalidated by a modification done by ON DELETE CASCADE or ...SET NULL.
• Correction d'un bogue : if you created a temporary table inside LOCK TABLES, and used that temporary table, that caused an assertion failure in ha_innodb.cc.
• Correction d'un bogue : if you set innodb_flush_log_at_trx_commit to 1, SHOW VARIABLES would show its value as 16 million.

• 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 bogue in 4.0.2: even a simple insert could crash the AIX version.
• Correction d'un bogue : 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.
• Correction d'un bogue : if compiled on 64-bit Solaris, InnoDB produced a bus error at startup.

• The feature set of 3.23 will be frozen from this version on. New features will go the the 4.0 branch, and only bogue 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : If the database happened to crash in the middle of a commit, then the recovery might leak tablespace pages.
• Correction d'un bogue : 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.
• Correction d'un bogue : DROP TABLE or DROP DATABASE could fail if there simultaneously was a CREATE TABLE running.
• Correction d'un bogue : If you configured the buffer pool bigger than 2 GB in a 32-bit computer, InnoDB would assert in buf0buf.ic line 214.
• Correction d'un bogue : 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.
• Correction d'un bogue : innodb_log_monitor caused a hang if it suppressed lock prints for a page.
• Correction d'un bogue : in the HP-UX-10.20 version mutexes would leak and cause race conditions and crashes in any part of InnoDB code.
• Correction d'un bogue : 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.
• Correction d'un bogue : if compiled on 64-bit Solaris, InnoDB produced a bus error at startup.

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

• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : if you use MySQL 'user level locks' and close a connection, then InnoDB may assert in ha_innobase.cc, line 302.

• 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.
• Correction d'un bogue : 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.

• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.

• 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.
• Correction d'un bogue : 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.

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

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

• This is the same as 3.23.45.

• This is a boguefix 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 bogue in the insert buffer. The insert buffer tree could get into an inconsistent state, causing a crash, and also crashing the recovery. This bogue could appear especially in large table imports or alterations.
• Fixed a bogue in recovery: InnoDB could go into an infinite loop constantly printing a warning message that it cannot find free blocks from the buffer pool.
• Correction d'un bogue : 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 bogue which can cause an assertion failure in 3.23.44 in srv0srv.c, line 1728.

• 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 bogue in replication of auto-inc columns with multiline inserts.
• Fixed a bogue 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 bogue in purge which could cause crashes.

• This is essentially the same as InnoDB-3.23.42.

• Fixed a bogue 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 bogue in RENAME TABLE.
• Fixed a bogue in replication with an auto-increment column.

• 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 bogues fixed and an ORDER BY bogue ('Sort aborted') fixed.

• Only a few rare bogues fixed.

• 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 bogue fixes, most notably the rollback bogue in 3.23.38.

• 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 bogue fixes.