Bug#54678: InnoDB, TRUNCATE, ALTER, I_S SELECT, crash or deadlock
- Incompatible change: truncate no longer resorts to a row by
row delete if the storage engine does not support the truncate
method. Consequently, the count of affected rows does not, in
any case, reflect the actual number of rows.
- Incompatible change: it is no longer possible to truncate a
table that participates as a parent in a foreign key constraint,
unless it is a self-referencing constraint (both parent and child
are in the same table). To work around this incompatible change
and still be able to truncate such tables, disable foreign checks
with SET foreign_key_checks=0 before truncate. Alternatively, if
foreign key checks are necessary, please use a DELETE statement
without a WHERE condition.
Problem description:
The problem was that for storage engines that do not support
truncate table via a external drop and recreate, such as InnoDB
which implements truncate via a internal drop and recreate, the
delete_all_rows method could be invoked with a shared metadata
lock, causing problems if the engine needed exclusive access
to some internal metadata. This problem originated with the
fact that there is no truncate specific handler method, which
ended up leading to a abuse of the delete_all_rows method that
is primarily used for delete operations without a condition.
Solution:
The solution is to introduce a truncate handler method that is
invoked when the engine does not support truncation via a table
drop and recreate. This method is invoked under a exclusive
metadata lock, so that there is only a single instance of the
table when the method is invoked.
Also, the method is not invoked and a error is thrown if
the table is a parent in a non-self-referencing foreign key
relationship. This was necessary to avoid inconsistency as
some integrity checks are bypassed. This is inline with the
fact that truncate is primarily a DDL operation that was
designed to quickly remove all data from a table.
mysql-test/suite/innodb/t/innodb-truncate.test:
Add test cases for truncate and foreign key checks.
Also test that InnoDB resets auto-increment on truncate.
mysql-test/suite/innodb/t/innodb.test:
FK is not necessary, test is related to auto-increment.
Update error number, truncate is no longer invoked if
table is parent in a FK relationship.
mysql-test/suite/innodb/t/innodb_mysql.test:
Update error number, truncate is no longer invoked if
table is parent in a FK relationship.
Use delete instead of truncate, test is used to check
the interaction of FKs, triggers and delete.
mysql-test/suite/parts/inc/partition_check.inc:
Fix typo.
mysql-test/suite/sys_vars/t/foreign_key_checks_func.test:
Update error number, truncate is no longer invoked if
table is parent in a FK relationship.
mysql-test/t/mdl_sync.test:
Modify test case to reflect and ensure that truncate takes
a exclusive metadata lock.
mysql-test/t/trigger-trans.test:
Update error number, truncate is no longer invoked if
table is parent in a FK relationship.
sql/ha_partition.cc:
Reorganize the various truncate methods. delete_all_rows is now
passed directly to the underlying engines, so as truncate. The
code responsible for truncating individual partitions is moved
to ha_partition::truncate_partition, which is invoked when a
ALTER TABLE t1 TRUNCATE PARTITION p statement is executed.
Since the partition truncate no longer can be invoked via
delete, the bitmap operations are not necessary anymore. The
explicit reset of the auto-increment value is also removed
as the underlying engines are now responsible for reseting
the value.
sql/handler.cc:
Wire up the handler truncate method.
sql/handler.h:
Introduce and document the truncate handler method. It assumes
certain use cases of delete_all_rows.
Add method to retrieve the list of foreign keys referencing a
table. Method is used to avoid truncating tables that are
parent in a foreign key relationship.
sql/share/errmsg-utf8.txt:
Add error message for truncate and FK.
sql/sql_lex.h:
Introduce a flag so that the partition engine can detect when
a partition is being truncated. Used to give a special error.
sql/sql_parse.cc:
Function mysql_truncate_table no longer exists.
sql/sql_partition_admin.cc:
Implement the TRUNCATE PARTITION statement.
sql/sql_truncate.cc:
Change the truncate table implementation to use the new truncate
handler method and to not rely on row-by-row delete anymore.
The truncate handler method is always invoked with a exclusive
metadata lock. Also, it is no longer possible to truncate a
table that is parent in some non-self-referencing foreign key.
storage/archive/ha_archive.cc:
Rename method as the description indicates that in the future
this could be a truncate operation.
storage/blackhole/ha_blackhole.cc:
Implement truncate as no operation for the blackhole engine in
order to remain compatible with older releases.
storage/federated/ha_federated.cc:
Introduce truncate method that invokes delete_all_rows.
This is required to support partition truncate as this
form of truncate does not implement the drop and recreate
protocol.
storage/heap/ha_heap.cc:
Introduce truncate method that invokes delete_all_rows.
This is required to support partition truncate as this
form of truncate does not implement the drop and recreate
protocol.
storage/ibmdb2i/ha_ibmdb2i.cc:
Introduce truncate method that invokes delete_all_rows.
This is required to support partition truncate as this
form of truncate does not implement the drop and recreate
protocol.
storage/innobase/handler/ha_innodb.cc:
Rename delete_all_rows to truncate. InnoDB now does truncate
under a exclusive metadata lock.
Introduce and reorganize methods used to retrieve the list
of foreign keys referenced by a or referencing a table.
storage/myisammrg/ha_myisammrg.cc:
Introduce truncate method that invokes delete_all_rows.
This is required in order to remain compatible with earlier
releases where truncate would resort to a row-by-row delete.
Bug#54678: InnoDB, TRUNCATE, ALTER, I_S SELECT, crash or deadlock
- Incompatible change: truncate no longer resorts to a row by
row delete if the storage engine does not support the truncate
method. Consequently, the count of affected rows does not, in
any case, reflect the actual number of rows.
- Incompatible change: it is no longer possible to truncate a
table that participates as a parent in a foreign key constraint,
unless it is a self-referencing constraint (both parent and child
are in the same table). To work around this incompatible change
and still be able to truncate such tables, disable foreign checks
with SET foreign_key_checks=0 before truncate. Alternatively, if
foreign key checks are necessary, please use a DELETE statement
without a WHERE condition.
Problem description:
The problem was that for storage engines that do not support
truncate table via a external drop and recreate, such as InnoDB
which implements truncate via a internal drop and recreate, the
delete_all_rows method could be invoked with a shared metadata
lock, causing problems if the engine needed exclusive access
to some internal metadata. This problem originated with the
fact that there is no truncate specific handler method, which
ended up leading to a abuse of the delete_all_rows method that
is primarily used for delete operations without a condition.
Solution:
The solution is to introduce a truncate handler method that is
invoked when the engine does not support truncation via a table
drop and recreate. This method is invoked under a exclusive
metadata lock, so that there is only a single instance of the
table when the method is invoked.
Also, the method is not invoked and a error is thrown if
the table is a parent in a non-self-referencing foreign key
relationship. This was necessary to avoid inconsistency as
some integrity checks are bypassed. This is inline with the
fact that truncate is primarily a DDL operation that was
designed to quickly remove all data from a table.
LOAD DATA into partitioned MyISAM table
Problem was that both partitioning and myisam
used the same table_share->mutex for different protections
(auto inc and repair).
Solved by adding a specific mutex for the partitioning
auto_increment.
Also adding destroying the ha_data structure in
free_table_share (which is to be propagated
into 5.5).
This is a 5.1 ONLY patch, already fixed in 5.5+.
LOAD DATA into partitioned MyISAM table
Problem was that both partitioning and myisam
used the same table_share->mutex for different protections
(auto inc and repair).
Solved by adding a specific mutex for the partitioning
auto_increment.
Also adding destroying the ha_data structure in
free_table_share (which is to be propagated
into 5.5).
This is a 5.1 ONLY patch, already fixed in 5.5+.
Bug#57113: ha_partition::extra(ha_extra_function):
Assertion `m_extra_cache' failed
Fix for bug#55458 included DBUG_ASSERTS causing
debug builds of the server to crash on
another multi-table update.
Removed the asserts since they where wrong.
(updated after testing the patch in 5.5).
mysql-test/r/partition.result:
updated result
mysql-test/t/partition.test:
Added test for bug#57113
sql/ha_partition.cc:
Removed the assert for m_extra_cache when
::extra(HA_PREPARE_FOR_UPDATE) was called.
Bug#57113: ha_partition::extra(ha_extra_function):
Assertion `m_extra_cache' failed
Fix for bug#55458 included DBUG_ASSERTS causing
debug builds of the server to crash on
another multi-table update.
Removed the asserts since they where wrong.
(updated after testing the patch in 5.5).
When having a sub query in partitioned innodb one could
make the partitioning engine to search for a 'index_next_same'
on a partition that had not been initialized.
Problem was that the subselect function looks at table->status
which was not set in the partitioning handler when it skipped
scanning due to no matching partitions found.
Fixed by setting table->status = STATUS_NOT_FOUND when
there was no partitions to scan. (If there are partitions to
scan, it will be set in the partitions handler.)
mysql-test/r/partition_innodb.result:
added result
mysql-test/t/partition_innodb.test:
added test
sql/ha_partition.cc:
set table status to not found, if there ar no partitions to scan.
When having a sub query in partitioned innodb one could
make the partitioning engine to search for a 'index_next_same'
on a partition that had not been initialized.
Problem was that the subselect function looks at table->status
which was not set in the partitioning handler when it skipped
scanning due to no matching partitions found.
Fixed by setting table->status = STATUS_NOT_FOUND when
there was no partitions to scan. (If there are partitions to
scan, it will be set in the partitions handler.)
Fall back to use ALTER TABLE for engines that doesn't support REPAIR when doing repair for upgrade.
Nicer output from mysql_upgrade and mysql_check
Updated all arrays that used NAME_LEN to use SAFE_NAME_LEN to ensure that we don't break things accidently as names can now have a #mysql50# prefix.
client/mysql_upgrade.c:
If we are using verbose, also run mysqlcheck in verbose mode.
client/mysqlcheck.c:
Add more information if running in verbose mode
Print 'Needs upgrade' instead of complex error if table needs to be upgraded
Don't write connect information if verbose is not 2 or above
mysql-test/r/drop.result:
Updated test and results as we now support full table names
mysql-test/r/grant.result:
Now you get a correct error message if using #mysql with paths
mysql-test/r/show_check.result:
Update results as table names can temporarly be bigger than NAME_LEN (during upgrade)
mysql-test/r/upgrade.result:
Test upgrade for long table names.
mysql-test/suite/funcs_1/r/is_tables_is.result:
Updated old test result (had note been updated in a while)
mysql-test/t/drop.test:
Updated test and results as we now support full table names
mysql-test/t/grant.test:
Now you get a correct error message if using #mysql with paths
mysql-test/t/upgrade.test:
Test upgrade for long table names.
sql/ha_partition.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/item.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/log_event.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/mysql_priv.h:
Added SAFE_NAME_LEN
sql/rpl_filter.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sp.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sp_head.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_acl.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_base.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_connect.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_parse.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_prepare.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_select.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_show.cc:
NAME_LEN -> SAFE_NAME_LEN
Enlarge table names for SHOW TABLES to also include optional #mysql50#
sql/sql_table.cc:
Fall back to use ALTER TABLE for engines that doesn't support REPAIR when doing repair for upgrade.
sql/sql_trigger.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_udf.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/sql_view.cc:
NAME_LEN -> SAFE_NAME_LEN
sql/table.cc:
Fixed check_table_name() to not count #mysql50# as part of name
If #mysql50# is part of the name, don't allow path characters in name.
- Changed to still use bcmp() in certain cases becasue
- Faster for short unaligneed strings than memcmp()
- Bettern when using valgrind
- Changed to use my_sprintf() instead of sprintf() to get higher portability for old systems
- Changed code to use MariaDB version of select->skip_record()
- Removed -%::SCCS/s.% from Makefile.am:s to remove automake warnings
As we check for the impossible partitions earlier, it's possible that we don't find any
suitable partitions at all. So this assertion just has to be corrected for this case.
per-file comments:
mysql-test/r/partition_innodb.result
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
test result updated.
mysql-test/t/partition_innodb.test
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
test case added.
sql/ha_partition.cc
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
Assertion changed to '>=' as the prune_partition_set() in the get_partition_set() can
do start_part= end_part+1 if no possible partitions were found.
As we check for the impossible partitions earlier, it's possible that we don't find any
suitable partitions at all. So this assertion just has to be corrected for this case.
per-file comments:
mysql-test/r/partition_innodb.result
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
test result updated.
mysql-test/t/partition_innodb.test
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
test case added.
sql/ha_partition.cc
Bug#55146 Assertion `m_part_spec.start_part == m_part_spec.end_part' in index_read_idx_map
Assertion changed to '>=' as the prune_partition_set() in the get_partition_set() can
do start_part= end_part+1 if no possible partitions were found.
Problem was that the handler call ::extra(HA_EXTRA_CACHE) was cached
but the ::extra(HA_EXTRA_PREPARE_FOR_UPDATE) was not.
Solution was to also cache the other call and forward it when moving
to a new partition to scan.
mysql-test/r/partition.result:
test result
mysql-test/t/partition.test:
New test from bug report.
sql/ha_partition.cc:
cache the HA_EXTRA_PREPARE_FOR_UPDATE just like HA_EXTRA_CACHE.
sql/ha_partition.h:
Added cache flag for HA_EXTRA_PREPARE_FOR_UPDATE
Problem was that the handler call ::extra(HA_EXTRA_CACHE) was cached
but the ::extra(HA_EXTRA_PREPARE_FOR_UPDATE) was not.
Solution was to also cache the other call and forward it when moving
to a new partition to scan.
The issue was that we didn't always check result of ha_rnd_init() which caused a problem for handlers that returned an error in this code.
- Changed prototype of ha_rnd_init() to ensure that we get a compile warning if result is not checked.
- Added ha_rnd_init_with_error() that prints error on failure.
- Checked all usage of ha_rnd_init() and ensure we generate an error message on failures.
- Changed init_read_record() to return 1 on failure.
sql/create_options.cc:
Fixed wrong printf
sql/event_db_repository.cc:
Check result from init_read_record()
sql/events.cc:
Check result from init_read_record()
sql/filesort.cc:
Check result from ha_rnd_init()
sql/ha_partition.cc:
Check result from ha_rnd_init()
sql/ha_partition.h:
Fixed compiler warning
sql/handler.cc:
Added ha_rnd_init_with_error()
Check result from ha_rnd_init()
sql/handler.h:
Added ha_rnd_init_with_error()
Changed prototype of ha_rnd_init() to ensure that we get a compile warning if result is not checked
sql/item_subselect.cc:
Check result from ha_rnd_init()
sql/log.cc:
Check result from ha_rnd_init()
sql/log_event.cc:
Check result from ha_rnd_init()
sql/log_event_old.cc:
Check result from ha_rnd_init()
sql/mysql_priv.h:
init_read_record() now returns error code on failure
sql/opt_range.cc:
Check result from ha_rnd_init()
sql/records.cc:
init_read_record() now returns error code on failure
Check result from ha_rnd_init()
sql/sql_acl.cc:
Check result from init_read_record()
sql/sql_cursor.cc:
Print error if ha_rnd_init() fails
sql/sql_delete.cc:
Check result from init_read_record()
sql/sql_help.cc:
Check result from init_read_record()
sql/sql_plugin.cc:
Check result from init_read_record()
sql/sql_select.cc:
Check result from ha_rnd_init()
Print error if ha_rnd_init() fails.
sql/sql_servers.cc:
Check result from init_read_record()
sql/sql_table.cc:
Check result from init_read_record()
sql/sql_udf.cc:
Check result from init_read_record()
sql/sql_update.cc:
Check result from init_read_record()
storage/example/ha_example.cc:
Don't return error on rnd_init()
storage/ibmdb2i/ha_ibmdb2i.cc:
Removed not relevant comment
Fixed compiler warnings.
queues.h and queues.c are now based on the UNIREG code and thus made BSD.
Fix code to use new queue() interface. This mostly affects how you access elements in the queue.
If USE_NET_CLEAR is not set, don't clear connection from unexpected characters. This should give a speed up when doing a lot of fast queries.
Fixed some code in ma_ft_boolean_search.c that had not made it from myisam/ft_boolean_search.c
include/queues.h:
Use UNIREG code base (BSD)
Changed init_queue() to take all initialization arguments.
New interface to access elements in queue
include/thr_alarm.h:
Changed to use time_t instead of ulong (portability)
Added index_in_queue, to be able to remove random element from queue in O(1)
mysys/queues.c:
Use UNIREG code base (BSD)
init_queue() and reinit_queue() now takes more initialization arguments. (No need for init_queue_ex() anymore)
Now one can tell queue_insert() to store in the element a pointer to where element is in queue. This allows one to remove elements from queue in O(1) instead of O(N)
mysys/thr_alarm.c:
Use new option in queue() to allow fast removal of elements.
Do less inside LOCK_alarm mutex.
This should give a major speed up of thr_alarm usage when there is many threads
sql/create_options.cc:
Fixed wrong printf
sql/event_queue.cc:
Use new queue interface()
sql/filesort.cc:
Use new queue interface()
sql/ha_partition.cc:
Use new queue interface()
sql/ha_partition.h:
Fixed compiler warning
sql/item_cmpfunc.cc:
Fixed compiler warning
sql/item_subselect.cc:
Use new queue interface()
Removed not used variable
sql/net_serv.cc:
If USE_NET_CLEAR is not set, don't clear connection from unexpected characters.
This should give a speed up when doing a lot of fast queries at the disadvantage that if there is a bug in the client protocol the connection will be dropped instead of being unnoticed.
sql/opt_range.cc:
Use new queue interface()
Fixed compiler warnings
sql/uniques.cc:
Use new queue interface()
storage/maria/ma_ft_boolean_search.c:
Copy code from myisam/ft_boolean_search.c
Use new queue interface()
storage/maria/ma_ft_nlq_search.c:
Use new queue interface()
storage/maria/ma_sort.c:
Use new queue interface()
storage/maria/maria_pack.c:
Use new queue interface()
Use queue_fix() instead of own loop to fix queue.
storage/myisam/ft_boolean_search.c:
Use new queue interface()
storage/myisam/ft_nlq_search.c:
Use new queue interface()
storage/myisam/mi_test_all.sh:
Remove temporary file from last run
storage/myisam/myisampack.c:
Use new queue interface()
Use queue_fix() instead of own loop to fix queue.
storage/myisam/sort.c:
Use new queue interface()
storage/myisammrg/myrg_queue.c:
Use new queue interface()
storage/myisammrg/myrg_rnext.c:
Use new queue interface()
storage/myisammrg/myrg_rnext_same.c:
Use new queue interface()
storage/myisammrg/myrg_rprev.c:
Use new queue interface()
The handler function for reading one row from a specific index
was not optimized in the partitioning handler since it
used the default implementation.
No test case since it is performance only, verified by hand.
sql/ha_partition.cc:
Implemented a optimized version of index_read_idx_map
for the case when find flag == HA_READ_KEY_EXACT,
which is the common case.
sql/ha_partition.h:
Declared ha_partition::index_read_idx_map
The handler function for reading one row from a specific index
was not optimized in the partitioning handler since it
used the default implementation.
No test case since it is performance only, verified by hand.
Essentially, the problem is that safemalloc is excruciatingly
slow as it checks all allocated blocks for overrun at each
memory management primitive, yielding a almost exponential
slowdown for the memory management functions (malloc, realloc,
free). The overrun check basically consists of verifying some
bytes of a block for certain magic keys, which catches some
simple forms of overrun. Another minor problem is violation
of aliasing rules and that its own internal list of blocks
is prone to corruption.
Another issue with safemalloc is rather the maintenance cost
as the tool has a significant impact on the server code.
Given the magnitude of memory debuggers available nowadays,
especially those that are provided with the platform malloc
implementation, maintenance of a in-house and largely obsolete
memory debugger becomes a burden that is not worth the effort
due to its slowness and lack of support for detecting more
common forms of heap corruption.
Since there are third-party tools that can provide the same
functionality at a lower or comparable performance cost, the
solution is to simply remove safemalloc. Third-party tools
can provide the same functionality at a lower or comparable
performance cost.
The removal of safemalloc also allows a simplification of the
malloc wrappers, removing quite a bit of kludge: redefinition
of my_malloc, my_free and the removal of the unused second
argument of my_free. Since free() always check whether the
supplied pointer is null, redudant checks are also removed.
Also, this patch adds unit testing for my_malloc and moves
my_realloc implementation into the same file as the other
memory allocation primitives.
client/mysqldump.c:
Pass my_free directly as its signature is compatible with the
callback type -- which wasn't the case for free_table_ent.
