"insert into.. select * from"
When inserting into a partitioned table using 'insert into
<target> select * from <src>', read_buffer_size bytes of memory
are allocated for each partition in the target table.
This resulted in large memory consumption when the number of
partitions are high.
This patch introduces a new method which tries to estimate the
buffer size required for each partition and limits the maximum
buffer size used to maximum of 10 * read_buffer_size,
11 * read_buffer_size in case of monotonic partition functions.
sql/ha_partition.cc:
Introduced a method ha_partition::estimate_read_buffer_size
to estimate buffer size required for each partition.
Method ha_partition::start_part_bulk_insert updated
to update the read_buffer_size before calling bulk upload
in storage engines.
Added thd in ha_partition::start_part_bulk_insert method signature.
sql/ha_partition.h:
Introduced a method ha_partition::estimate_read_buffer_size.
Added thd in ha_partition::start_part_bulk_insert method signature.
"insert into.. select * from"
When inserting into a partitioned table using 'insert into
<target> select * from <src>', read_buffer_size bytes of memory
are allocated for each partition in the target table.
This resulted in large memory consumption when the number of
partitions are high.
This patch introduces a new method which tries to estimate the
buffer size required for each partition and limits the maximum
buffer size used to maximum of 10 * read_buffer_size,
11 * read_buffer_size in case of monotonic partition functions.
* Finished Monty and Jani's merge
* Some InnoDB tests still fail (because it's old xtradb code run against
newer testsuite). They are expected to go after mergning with the latest
xtradb.
(Backport)
Problem is that when insert (ha_start_bulk_insert) in i partitioned table,
it will call ha_start_bulk_insert for every partition, used or not.
Solution is to delay the call to the partitions ha_start_bulk_insert until
the first row is to be inserted into that partition
sql/ha_partition.cc:
Bug#35845: unneccesary call to ha_start_bulk_insert for not used partitions
Using a bitmap for keeping record of which partitions for which
ha_start_bulk_insert has been called, and check against that if one
should call it before continue with the insert/update, or if it has already
been called.
This way it will only call ha_start_bulk_insert for the used partitions.
There is also a little prediction on how many rows that will be inserted into
the current partition, it will guess on equal distribution of the records
across all partitions, accept for the first used partition, which will guess
at 50% of the given estimate, if it is a monotonic partitioning function.
sql/ha_partition.h:
Bug#35845: unneccesary call to ha_start_bulk_insert for not used partitions
Added help variables and function for delaying ha_bulk_insert until it has
to be called.
Fixed a comment.
(Backport)
Problem is that when insert (ha_start_bulk_insert) in i partitioned table,
it will call ha_start_bulk_insert for every partition, used or not.
Solution is to delay the call to the partitions ha_start_bulk_insert until
the first row is to be inserted into that partition
Inserting a negative value in the autoincrement column of a
partitioned innodb table was causing the value of the auto
increment counter to wrap around into a very large positive
value. The consequences are the same as if a very large positive
value was inserted into a column, e.g. reduced autoincrement
range, failure to read autoincrement counter.
The current patch ensures that before calculating the next
auto increment value, the current value is within the positive
maximum allowed limit.
mysql-test/suite/parts/inc/partition_auto_increment.inc:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds tests for performing insert,update and delete on a partition
table with negative auto_increment values.
mysql-test/suite/parts/r/partition_auto_increment_innodb.result:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the innodb engine.
mysql-test/suite/parts/r/partition_auto_increment_memory.result:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the memory engine.
mysql-test/suite/parts/r/partition_auto_increment_myisam.result:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the myisam engine.
mysql-test/suite/parts/r/partition_auto_increment_ndb.result:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the ndb engine.
mysql-test/suite/parts/t/partition_auto_increment_archive.test:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds a variable that allows the Archive engine to skip tests
that involve insertion of negative auto increment values.
mysql-test/suite/parts/t/partition_auto_increment_blackhole.test:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds a variable that allows the Blackhole engine to skip tests
that involve insertion of negative auto increment values.
sql/ha_partition.cc:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Ensures that the current value is lesser than the upper limit
for the type of the field before setting the next auto increment
value to be calculated.
sql/ha_partition.h:
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Modifies the set_auto_increment_if_higher function, to take
into account negative auto increment values when doing a
comparison.
Inserting a negative value in the autoincrement column of a
partitioned innodb table was causing the value of the auto
increment counter to wrap around into a very large positive
value. The consequences are the same as if a very large positive
value was inserted into a column, e.g. reduced autoincrement
range, failure to read autoincrement counter.
The current patch ensures that before calculating the next
auto increment value, the current value is within the positive
maximum allowed limit.
INSERT ... SELECT ...
Problem was that when bulk insert is used on an empty
table/partition, it disables the indexes for better
performance, but in this specific case it also tries
to read from that partition using an index, which is
not possible since it has been disabled.
Solution was to allow index reads on disabled indexes
if there are no records.
Also reverted the patch for bug#38005, since that was a workaround
in the partitioning engine instead of a fix in myisam.
mysql-test/r/partition.result:
Bug#46639: 1030 (HY000): Got error 124 from storage engine on
INSERT ... SELECT ...
updated result file
mysql-test/t/partition.test:
Bug#46639: 1030 (HY000): Got error 124 from storage engine on
INSERT ... SELECT ...
Added testcase
sql/ha_partition.cc:
Bug#46639: 1030 (HY000): Got error 124 from storage engine on
INSERT ... SELECT ...
reverted the patch for bug#38005, since that was a workaround
around this problem, not needed after fixing it in myisam.
storage/myisam/mi_search.c:
Bug#46639: 1030 (HY000): Got error 124 from storage engine on
INSERT ... SELECT ...
Return HA_ERR_END_OF_FILE instead of HA_ERR_WRONG_INDEX
when there are no rows.
INSERT ... SELECT ...
Problem was that when bulk insert is used on an empty
table/partition, it disables the indexes for better
performance, but in this specific case it also tries
to read from that partition using an index, which is
not possible since it has been disabled.
Solution was to allow index reads on disabled indexes
if there are no records.
Also reverted the patch for bug#38005, since that was a workaround
in the partitioning engine instead of a fix in myisam.
column on partitioned table
An assertion 'ASSERT_COULUMN_MARKED_FOR_READ' is failed if the query
is executed with index containing double column on partitioned table.
The problem is that assertion expects all the fields which are read,
to be in the read_set.
In this query only the field 'a' is in the readset as the tables in
the query are joined by the field 'a' and so the assertion fails
expecting other field 'b'.
Since the function cmp() is just comparison of two parameters passed,
the assertion is not required.
Fixed by removing the assertion in the double fields comparision
function and also fixed the index initialization to do ordered
index scan with RW LOCK which ensures all the fields from a key are in
the read_set.
Note: this bug is not reproducible with other datatypes because the
assertion doesn't exist in comparision function for other
datatypes.
mysql-test/r/partition.result:
Testcase for BUG#45816
mysql-test/t/partition.test:
Testcase for BUG#45816
sql/field.cc:
Removed the assertion ASSERT_COLUMN_MARED_FOR_READ in Field_double::cmp()
function
sql/ha_partition.cc:
Fixed index_int() method to make it initialize the read_set properly if
ordered index scan with RW lock is requested.
column on partitioned table
An assertion 'ASSERT_COULUMN_MARKED_FOR_READ' is failed if the query
is executed with index containing double column on partitioned table.
The problem is that assertion expects all the fields which are read,
to be in the read_set.
In this query only the field 'a' is in the readset as the tables in
the query are joined by the field 'a' and so the assertion fails
expecting other field 'b'.
Since the function cmp() is just comparison of two parameters passed,
the assertion is not required.
Fixed by removing the assertion in the double fields comparision
function and also fixed the index initialization to do ordered
index scan with RW LOCK which ensures all the fields from a key are in
the read_set.
Note: this bug is not reproducible with other datatypes because the
assertion doesn't exist in comparision function for other
datatypes.
We disallow the partitioning of a log table. You could however
partition a table first, and then point logging to it. This is
not only against the docs, it also crashes the server.
We catch this case now.
mysql-test/r/partition.result:
results for 40281
mysql-test/t/partition.test:
test for 40281: show that trying to log to partitioned table fails rather
to crash the server
sql/ha_partition.cc:
Signal that we no longer support logging to partitioned tables,
as per the docs.
sql/sql_partition.cc:
Some commands like "USE ..." have no select, yet we may try
to parse partition info after their execution if user set a
partitioned table as log target. This shouldn't lead to a
NULL-deref/crash.
We disallow the partitioning of a log table. You could however
partition a table first, and then point logging to it. This is
not only against the docs, it also crashes the server.
We catch this case now.
Problem was that a failing rename just left the partitions at the state
it was at the failure.
Solution was to try to revert the started rename if a failure occured.
mysql-test/r/partition_not_embedded.result:
Bug#30102: Rename table does corrupt tables with partition files on failure
New result file
mysql-test/t/partition_not_embedded.test:
Bug#30102: Rename table does corrupt tables with partition files on failure
New test file
(list_files does not report the files in embedded)
sql/ha_partition.cc:
Bug#30102: Rename table does corrupt tables with partition files on failure
Better error handling for rename partitions (reverting the started rename
operation)
Different order of files for delete.
sql/handler.cc:
Bug#30102: Rename table does corrupt tables with partition files on failure
Tries to remove as many table files as possible
if the first delete succeeds.
Problem was that a failing rename just left the partitions at the state
it was at the failure.
Solution was to try to revert the started rename if a failure occured.
NOTE: This undoes changes by BUG#42829 in sql_class.cc:binlog_query().
I will revert the change in a post-push fix (the binlog filter should
be checked in sql_base.cc:decide_logging_format()).
NOTE: This undoes changes by BUG#42829 in sql_class.cc:binlog_query().
I will revert the change in a post-push fix (the binlog filter should
be checked in sql_base.cc:decide_logging_format()).
General overview:
The logic for switching to row format when binlog_format=MIXED had
numerous flaws. The underlying problem was the lack of a consistent
architecture.
General purpose of this changeset:
This changeset introduces an architecture for switching to row format
when binlog_format=MIXED. It enforces the architecture where it has
to. It leaves some bugs to be fixed later. It adds extensive tests to
verify that unsafe statements work as expected and that appropriate
errors are produced by problems with the selection of binlog format.
It was not practical to split this into smaller pieces of work.
Problem 1:
To determine the logging mode, the code has to take several parameters
into account (namely: (1) the value of binlog_format; (2) the
capabilities of the engines; (3) the type of the current statement:
normal, unsafe, or row injection). These parameters may conflict in
several ways, namely:
- binlog_format=STATEMENT for a row injection
- binlog_format=STATEMENT for an unsafe statement
- binlog_format=STATEMENT for an engine only supporting row logging
- binlog_format=ROW for an engine only supporting statement logging
- statement is unsafe and engine does not support row logging
- row injection in a table that does not support statement logging
- statement modifies one table that does not support row logging and
one that does not support statement logging
Several of these conflicts were not detected, or were detected with
an inappropriate error message. The problem of BUG#39934 was that no
appropriate error message was written for the case when an engine
only supporting row logging executed a row injection with
binlog_format=ROW. However, all above cases must be handled.
Fix 1:
Introduce new error codes (sql/share/errmsg.txt). Ensure that all
conditions are detected and handled in decide_logging_format()
Problem 2:
The binlog format shall be determined once per statement, in
decide_logging_format(). It shall not be changed before or after that.
Before decide_logging_format() is called, all information necessary to
determine the logging format must be available. This principle ensures
that all unsafe statements are handled in a consistent way.
However, this principle is not followed:
thd->set_current_stmt_binlog_row_based_if_mixed() is called in several
places, including from code executing UPDATE..LIMIT,
INSERT..SELECT..LIMIT, DELETE..LIMIT, INSERT DELAYED, and
SET @@binlog_format. After Problem 1 was fixed, that caused
inconsistencies where these unsafe statements would not print the
appropriate warnings or errors for some of the conflicts.
Fix 2:
Remove calls to THD::set_current_stmt_binlog_row_based_if_mixed() from
code executed after decide_logging_format(). Compensate by calling the
set_current_stmt_unsafe() at parse time. This way, all unsafe statements
are detected by decide_logging_format().
Problem 3:
INSERT DELAYED is not unsafe: it is logged in statement format even if
binlog_format=MIXED, and no warning is printed even if
binlog_format=STATEMENT. This is BUG#45825.
Fix 3:
Made INSERT DELAYED set itself to unsafe at parse time. This allows
decide_logging_format() to detect that a warning should be printed or
the binlog_format changed.
Problem 4:
LIMIT clause were not marked as unsafe when executed inside stored
functions/triggers/views/prepared statements. This is
BUG#45785.
Fix 4:
Make statements containing the LIMIT clause marked as unsafe at
parse time, instead of at execution time. This allows propagating
unsafe-ness to the view.
mysql-test/extra/rpl_tests/create_recursive_construct.inc:
Added auxiliary file used by binlog_unsafe.test to create and
execute recursive constructs
(functions/procedures/triggers/views/prepared statements).
mysql-test/extra/rpl_tests/rpl_foreign_key.test:
removed unnecessary set @@session.binlog_format
mysql-test/extra/rpl_tests/rpl_insert_delayed.test:
Filter out table id from table map events in binlog listing.
Got rid of $binlog_format_statement.
mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test:
disable warnings around call to unsafe procedure
mysql-test/include/rpl_udf.inc:
Disabled warnings for code that generates warnings
for some binlog formats. That would otherwise cause
inconsistencies in the result file.
mysql-test/r/mysqldump.result:
Views are now unsafe if they contain a LIMIT clause.
That fixed BUG#45831. Due to BUG#45832, a warning is
printed for the CREATE VIEW statement.
mysql-test/r/sp_trans.result:
Unsafe statements in stored procedures did not give a warning if
binlog_format=statement. This is BUG#45824. Now they do, so this
result file gets a new warning.
mysql-test/suite/binlog/r/binlog_multi_engine.result:
Error message changed.
mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result:
INSERT DELAYED didn't generate a warning when binlog_format=STATEMENT.
That was BUG#45825. Now there is a warning, so result file needs to be
updated.
mysql-test/suite/binlog/r/binlog_stm_ps.result:
Changed error message.
mysql-test/suite/binlog/r/binlog_unsafe.result:
updated result file:
- error message changed
- added test for most combinations of unsafe constructs invoked
from recursive constructs
- INSERT DELAYED now gives a warning (because BUG#45826 is fixed)
- INSERT..SELECT..LIMIT now gives a warning from inside recursive
constructs (because BUG#45785 was fixed)
- When a recursive construct (e.g., stored proc or function)
contains more than one statement, at least one of which is
unsafe, then all statements in the recursive construct give
warnings. This is a new bug introduced by this changeset.
It will be addressed in a post-push fix.
mysql-test/suite/binlog/t/binlog_innodb.test:
Changed error code for innodb updates with READ COMMITTED or
READ UNCOMMITTED transaction isolation level and
binlog_format=statement.
mysql-test/suite/binlog/t/binlog_multi_engine.test:
The error code has changed for statements where more than one
engine is involved and one of them is self-logging.
mysql-test/suite/binlog/t/binlog_unsafe-master.opt:
Since binlog_unsafe now tests unsafe-ness of UDF's, we need an extra
flag in the .opt file.
mysql-test/suite/binlog/t/binlog_unsafe.test:
- Clarified comment.
- Rewrote first part of test. Now it tests not only unsafe variables
and functions, but also unsafe-ness due to INSERT..SELECT..LIMIT,
INSERT DELAYED, insert into two autoinc columns, use of UDF's, and
access to log tables in the mysql database.
Also, in addition to functions, procedures, triggers, and prepared
statements, it now also tests views; and it constructs recursive
calls in two levels by combining these recursive constructs.
Part of the logic is in extra/rpl_tests/create_recursive_construct.inc.
- added tests for all special system variables that should not be unsafe.
- added specific tests for BUG#45785 and BUG#45825
mysql-test/suite/rpl/r/rpl_events.result:
updated result file
mysql-test/suite/rpl/r/rpl_extraColmaster_innodb.result:
updated result file
mysql-test/suite/rpl/r/rpl_extraColmaster_myisam.result:
updated result file
mysql-test/suite/rpl/r/rpl_foreign_key_innodb.result:
updated result file
mysql-test/suite/rpl/r/rpl_idempotency.result:
updated result file
mysql-test/suite/rpl/r/rpl_mix_found_rows.result:
Split rpl_found_rows.test into rpl_mix_found_rows.test (a new file) and
rpl_stm_found_rows.test (renamed rpl_found_rows.test). This file equals
the second half of the old rpl_found_rows.result, with the following
modifications:
- minor formatting changes
- additional initialization
mysql-test/suite/rpl/r/rpl_mix_insert_delayed.result:
Moved out code operating in mixed mode from rpl_stm_insert_delayed
(into rpl_mix_insert_delayed) and got rid of explicit setting of
binlog format.
mysql-test/suite/rpl/r/rpl_rbr_to_sbr.result:
updated result file
mysql-test/suite/rpl/r/rpl_row_idempotency.result:
Moved the second half of rpl_idempotency.test, which only
executed in row mode, to rpl_row_idempotency.test. This is
the new result file.
mysql-test/suite/rpl/r/rpl_row_insert_delayed.result:
Got rid of unnecessary explicit setting of binlog format.
mysql-test/suite/rpl/r/rpl_stm_found_rows.result:
Split rpl_found_rows.test into rpl_mix_found_rows.test (a new file) and
rpl_stm_found_rows.test (renamed rpl_found_rows.test). Changes in
this file:
- minor formatting changes
- warning is now issued for unsafe statements inside procedures
(since BUG#45824 is fixed)
- second half of file is moved to rpl_mix_found_rows.result
mysql-test/suite/rpl/r/rpl_stm_insert_delayed.result:
Moved out code operating in mixed mode from rpl_stm_insert_delayed
(into rpl_mix_insert_delayed) and got rid of explicit setting of
binlog format.
mysql-test/suite/rpl/r/rpl_stm_loadfile.result:
error message changed
mysql-test/suite/rpl/r/rpl_temporary_errors.result:
updated result file
mysql-test/suite/rpl/r/rpl_udf.result:
Remove explicit set of binlog format (and triplicate test execution)
and rely on test system executing the test in all binlog formats.
mysql-test/suite/rpl/t/rpl_bug31076.test:
Test is only valid in mixed or row mode since it generates row events.
mysql-test/suite/rpl/t/rpl_events.test:
Removed explicit set of binlog_format and removed duplicate testing.
Instead, we rely on the test system to try all binlog formats.
mysql-test/suite/rpl/t/rpl_extraColmaster_innodb.test:
Removed triplicate testing and instead relying on test system.
Test is only relevant for row format since statement-based replication
cannot handle extra columns on master.
mysql-test/suite/rpl/t/rpl_extraColmaster_myisam.test:
Removed triplicate testing and instead relying on test system.
Test is only relevant for row format since statement-based replication
cannot handle extra columns on master.
mysql-test/suite/rpl/t/rpl_idempotency-slave.opt:
Removed .opt file to avoid server restarts.
mysql-test/suite/rpl/t/rpl_idempotency.test:
- Moved out row-only tests to a new test file, rpl_row_idempotency.test.
rpl_idempotency now only contains tests that execute in all
binlog_formats.
- While I was here, also removed .opt file to avoid server restarts.
The slave_exec_mode is now set inside the test instead.
mysql-test/suite/rpl/t/rpl_mix_found_rows.test:
Split rpl_found_rows.test into rpl_mix_found_rows.test (a new file) and
rpl_stm_found_rows.test (renamed rpl_found_rows.test). This file
contains the second half of the original rpl_found_rows.test with the
follwing changes:
- initialization
- removed SET_BINLOG_FORMAT and added have_binlog_format_mixed.inc
- minor formatting changes
mysql-test/suite/rpl/t/rpl_mix_insert_delayed.test:
Moved out code operating in mixed mode from rpl_stm_insert_delayed
(into rpl_mix_insert_delayed) and got rid of explicit setting of
binlog format.
mysql-test/suite/rpl/t/rpl_rbr_to_sbr.test:
Test cannot execute in statement mode, since we no longer
switch to row format when binlog_format=statement.
Enforced mixed mode throughout the test.
mysql-test/suite/rpl/t/rpl_row_idempotency.test:
Moved the second half of rpl_idempotency.test, which only
executed in row mode, to this new file. We now rely on the
test system to set binlog format.
mysql-test/suite/rpl/t/rpl_row_insert_delayed.test:
- Got rid of unnecessary explicit setting of binlog format.
- extra/rpl_tests/rpl_insert_delayed.test does not need the
$binlog_format_statement variable any more, so that was
removed.
mysql-test/suite/rpl/t/rpl_slave_skip.test:
The test switches binlog_format internally and master generates both
row and statement events. Hence, the slave must be able to log in both
statement and row format. Hence test was changed to only execute in
mixed mode.
mysql-test/suite/rpl/t/rpl_stm_found_rows.test:
Split rpl_found_rows.test into rpl_mix_found_rows.test (a new file) and
rpl_stm_found_rows.test (renamed rpl_found_rows.test). Changes in
this file:
- minor formatting changes
- added have_binlog_format_statement and removed SET BINLOG_FORMAT.
- second half of file is moved to rpl_mix_found_rows.test
- added cleanup code
mysql-test/suite/rpl/t/rpl_stm_insert_delayed.test:
Moved out code operating in mixed mode from rpl_stm_insert_delayed
(into rpl_mix_insert_delayed) and got rid of explicit setting of
binlog format.
mysql-test/suite/rpl/t/rpl_switch_stm_row_mixed.test:
The test switches binlog_format internally and master generates both
row and statement events. Hence, the slave must be able to log in both
statement and row format. Hence test was changed to only execute in
mixed mode on slave.
mysql-test/suite/rpl/t/rpl_temporary_errors.test:
Removed explicit set of binlog format. Instead, the test now only
executes in row mode.
mysql-test/suite/rpl/t/rpl_udf.test:
Remove explicit set of binlog format (and triplicate test execution)
and rely on test system executing the test in all binlog formats.
mysql-test/suite/rpl_ndb/combinations:
Added combinations file for rpl_ndb.
mysql-test/suite/rpl_ndb/r/rpl_ndb_binlog_format_errors.result:
new result file
mysql-test/suite/rpl_ndb/r/rpl_ndb_circular_simplex.result:
updated result file
mysql-test/suite/rpl_ndb/t/rpl_ndb_2innodb.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_2myisam.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_basic.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors-master.opt:
new option file
mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors-slave.opt:
new option file
mysql-test/suite/rpl_ndb/t/rpl_ndb_binlog_format_errors.test:
New test case to verify all errors and warnings generated by
decide_logging_format.
mysql-test/suite/rpl_ndb/t/rpl_ndb_blob.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_blob2.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_simplex.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
While I was here, also made the test clean up after itself.
mysql-test/suite/rpl_ndb/t/rpl_ndb_commit_afterflush.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_ctype_ucs2_def.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_delete_nowhere.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_do_db.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_do_table.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_func003.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_innodb_trans.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_insert_ignore.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_engines_transactions.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_multi_update3.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_rep_ignore.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_row_001.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_sp003.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_sp006.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/suite/rpl_ndb/t/rpl_ndb_trig004.test:
The test needs slave to be able to switch to row mode, so the
test was changed to only execute in mixed and row mode.
mysql-test/t/partition_innodb_stmt.test:
Changed error code for innodb updates with READ COMMITTED or
READ UNCOMMITTED transaction isolation level and
binlog_format=statement.
sql/event_db_repository.cc:
Use member function to read current_stmt_binlog_row_based.
sql/events.cc:
Use member function to read current_stmt_binlog_row_based.
sql/ha_ndbcluster_binlog.cc:
reset_current_stmt_binlog_row_based() is not a no-op for the ndb_binlog
thread any more. Instead, the ndb_binlog thread now forces row mode both
initially and just after calling mysql_parse. (mysql_parse() is the only
place where reset_current_stmt_binlog_row_based() may be called from
the ndb_binlog thread, so these are the only two places that need to
change.)
sql/ha_partition.cc:
Use member function to read current_stmt_binlog_row_based.
sql/handler.cc:
Use member function to read current_stmt_binlog_row_based.
sql/item_create.cc:
Added DBUG_ENTER to some functions, to be able to trace when
set_stmt_unsafe is called.
sql/log.cc:
Use member function to read current_stmt_binlog_row_based.
sql/log_event.cc:
- Moved logic for changing to row format out of do_apply_event (and into
decide_logging_format).
- Added @todo comment for post-push cleanup.
sql/log_event_old.cc:
Move logic for changing to row format out of do_apply_event (and into
decide_logging_format).
sql/mysql_priv.h:
Make decide_logging_format() a member of the THD class, for two reasons:
- It is natural from an object-oriented perspective.
- decide_logging_format() needs to access private members of THD
(specifically, the new binlog_warning_flags field).
sql/rpl_injector.cc:
Removed call to set_current_stmt_binlog_row_based().
From now on, only decide_logging_fromat is allowed to modify
current_stmt_binlog_row_based. This call is from the ndb_binlog
thread, mostly executing code in ha_ndbcluster_binlog.cc.
This call can be safely removed, because:
- current_stmt_binlog_row_based is initialized for the ndb_binlog
thread's THD object when the THD object is created. So we're
not going to read uninitialized memory.
- The behavior of ndb_binlog thread does not use the state of the
current_stmt_binlog_row_based. It is conceivable that the
ndb_binlog thread would rely on the current_stmt_binlog_format
in two situations:
(1) when it calls mysql_parse;
(2) when it calls THD::binlog_query.
In case (1), it always clears THD::options&OPTION_BIN_LOG (because
run_query() in ha_ndbcluster_binlog.cc is only called with
disable_binlogging = TRUE).
In case (2), it always uses qtype=STMT_QUERY_TYPE.
sql/set_var.cc:
Added @todo comment for post-push cleanup.
sql/share/errmsg.txt:
Added new error messages and clarified ER_BINLOG_UNSAFE_STATEMENT.
sql/sp.cc:
Added DBUG_ENTER, to be able to trace when set_stmt_unsafe is called.
Got rid of MYSQL_QUERY_TYPE: it was equivalent to STMT_QUERY_TYPE.
sql/sp_head.cc:
Use member function to read current_stmt_binlog_row_based.
sql/sp_head.h:
Added DBUG_ENTER, to be able to trace when set_stmt_unsafe is called.
sql/sql_acl.cc:
Got rid of MYSQL_QUERY_TYPE: it was equivalent to STMT_QUERY_TYPE.
sql/sql_base.cc:
- Made decide_logging_format take care of all logic for deciding the
logging format, and for determining the related warnings and errors.
See comment above decide_logging_format for details.
- Made decide_logging_format a member function of THD, since it needs
to access private members of THD and since its purpose is to update
the state of a THD object.
- Added DBUG_ENTER, to be able to trace when set_stmt_unsafe is called.
sql/sql_class.cc:
- Moved logic for determining unsafe warnings away from THD::binlog_query
(and into decide_logging_format()). Now, it works like this:
1. decide_logging_format detects that the current statement shall
produce a warning, if it ever makes it to the binlog
2. decide_logging_format sets a flag of THD::binlog_warning_flags.
3. THD::binlog_query reads the flag. If the flag is set, it generates
a warning.
- Use member function to read current_stmt_binlog_row_based.
sql/sql_class.h:
- Added THD::binlog_warning_flags (see sql_class.cc for explanation).
- Made decide_logging_format() and reset_for_next_command() member
functions of THD (instead of standalone functions). This was needed
for two reasons: (1) the functions need to access the private member
THD::binlog_warning_flags; (2) the purpose of these functions is to
update the staet of a THD object, so from an object-oriented point
of view they should be member functions.
- Encapsulated current_stmt_binlog_row_based, so it is now private and
can only be accessed from a member function. Also changed the
data type to an enumeration instead of a bool.
- Removed MYSQL_QUERY_TYPE, because it was equivalent to
STMT_QUERY_TYPE anyways.
- When reset_current_stmt_binlog_row_based was called from the
ndb_binlog thread, it would behave as a no-op. This special
case has been removed, and the behavior of
reset_current_stmt_binlog_row_based does not depend on which thread
calls it any more. The special case did not serve any purpose,
since the ndb binlog thread did not take the
current_stmt_binlog_row_based flag into account anyways.
sql/sql_delete.cc:
- Moved logic for setting row format for DELETE..LIMIT away from
mysql_prepare_delete.
(Instead, we mark the statement as unsafe at parse time (sql_yacc.yy)
and rely on decide_logging_format() (sql_class.cc) to set row format.)
This is part of the fix for BUG#45831.
- Use member function to read current_stmt_binlog_row_based.
sql/sql_insert.cc:
- Removed unnecessary calls to thd->lex->set_stmt_unsafe() and
thd->set_current_stmt_binlog_row_based_if_mixed() from
handle_delayed_insert(). The calls are unnecessary because they
have already been made; they were made in the constructor of
the `di' object.
- Since decide_logging_format() is now a member function of THD, code
that calls decide_logging_format() had to be updated.
- Added DBUG_ENTER call, to be able to trace when set_stmt_unsafe is
called.
- Moved call to set_stmt_unsafe() for INSERT..SELECT..LIMIT away from
mysql_insert_select_prepare() (and into decide_logging_format).
This is part of the fix for BUG#45831.
- Use member function to read current_stmt_binlog_row_based.
sql/sql_lex.h:
- Added the flag BINLOG_STMT_FLAG_ROW_INJECTION to enum_binlog_stmt_flag.
This was necessary so that a statement can identify itself as a row
injection.
- Added appropriate setter and getter functions for the new flag.
- Added or clarified some comments.
- Added DBUG_ENTER()
sql/sql_load.cc:
Use member function to read current_stmt_binlog_row_based.
sql/sql_parse.cc:
- Made mysql_reset_thd_for_next_command() clear thd->binlog_warning_flags.
- Since thd->binlog_warning_flags is private, it must be set in a
member function of THD. Hence, moved the body of
mysql_reset_thd_for_next_command() to the new member function
THD::reset_thd_for_next_command(), and made
mysql_reset_thd_for_next_command() call
THD::reset_thd_for_next_command().
- Removed confusing comment.
- Use member function to read current_stmt_binlog_row_based.
sql/sql_repl.cc:
Use member function to read current_stmt_binlog_row_based.
sql/sql_table.cc:
Use member function to read current_stmt_binlog_row_based.
sql/sql_udf.cc:
Use member function to read current_stmt_binlog_row_based.
sql/sql_update.cc:
Moved logic for setting row format for UPDATE..LIMIT away from
mysql_prepare_update.
(Instead, we mark the statement as unsafe at parse time (sql_yacc.yy)
and rely on decide_logging_format() (sql_class.cc) to set row format.)
This is part of the fix for BUG#45831.
sql/sql_yacc.yy:
Made INSERT DELAYED, INSERT..SELECT..LIMIT, UPDATE..LIMIT, and
DELETE..LIMIT mark themselves as unsafe at parse time (instead
of at execution time).
This is part of the fixes BUG#45831 and BUG#45825.
storage/example/ha_example.cc:
Made exampledb accept inserts. This was needed by the new test case
rpl_ndb_binlog_format_errors, because it needs an engine that
is statement-only (and accepts inserts).
storage/example/ha_example.h:
Made exampledb a statement-only engine instead of a row-only engine.
No existing test relied exampledb's row-only capabilities. The new
test case rpl_ndb_binlog_format_errors needs an engine that is
statement-only.
storage/innobase/handler/ha_innodb.cc:
- Changed error error code and message given by innodb when
binlog_format=STATEMENT and transaction isolation level is
READ COMMITTED or READ UNCOMMITTED.
- While I was here, also simplified the condition for
checking when to give the error.
General overview:
The logic for switching to row format when binlog_format=MIXED had
numerous flaws. The underlying problem was the lack of a consistent
architecture.
General purpose of this changeset:
This changeset introduces an architecture for switching to row format
when binlog_format=MIXED. It enforces the architecture where it has
to. It leaves some bugs to be fixed later. It adds extensive tests to
verify that unsafe statements work as expected and that appropriate
errors are produced by problems with the selection of binlog format.
It was not practical to split this into smaller pieces of work.
Problem 1:
To determine the logging mode, the code has to take several parameters
into account (namely: (1) the value of binlog_format; (2) the
capabilities of the engines; (3) the type of the current statement:
normal, unsafe, or row injection). These parameters may conflict in
several ways, namely:
- binlog_format=STATEMENT for a row injection
- binlog_format=STATEMENT for an unsafe statement
- binlog_format=STATEMENT for an engine only supporting row logging
- binlog_format=ROW for an engine only supporting statement logging
- statement is unsafe and engine does not support row logging
- row injection in a table that does not support statement logging
- statement modifies one table that does not support row logging and
one that does not support statement logging
Several of these conflicts were not detected, or were detected with
an inappropriate error message. The problem of BUG#39934 was that no
appropriate error message was written for the case when an engine
only supporting row logging executed a row injection with
binlog_format=ROW. However, all above cases must be handled.
Fix 1:
Introduce new error codes (sql/share/errmsg.txt). Ensure that all
conditions are detected and handled in decide_logging_format()
Problem 2:
The binlog format shall be determined once per statement, in
decide_logging_format(). It shall not be changed before or after that.
Before decide_logging_format() is called, all information necessary to
determine the logging format must be available. This principle ensures
that all unsafe statements are handled in a consistent way.
However, this principle is not followed:
thd->set_current_stmt_binlog_row_based_if_mixed() is called in several
places, including from code executing UPDATE..LIMIT,
INSERT..SELECT..LIMIT, DELETE..LIMIT, INSERT DELAYED, and
SET @@binlog_format. After Problem 1 was fixed, that caused
inconsistencies where these unsafe statements would not print the
appropriate warnings or errors for some of the conflicts.
Fix 2:
Remove calls to THD::set_current_stmt_binlog_row_based_if_mixed() from
code executed after decide_logging_format(). Compensate by calling the
set_current_stmt_unsafe() at parse time. This way, all unsafe statements
are detected by decide_logging_format().
Problem 3:
INSERT DELAYED is not unsafe: it is logged in statement format even if
binlog_format=MIXED, and no warning is printed even if
binlog_format=STATEMENT. This is BUG#45825.
Fix 3:
Made INSERT DELAYED set itself to unsafe at parse time. This allows
decide_logging_format() to detect that a warning should be printed or
the binlog_format changed.
Problem 4:
LIMIT clause were not marked as unsafe when executed inside stored
functions/triggers/views/prepared statements. This is
BUG#45785.
Fix 4:
Make statements containing the LIMIT clause marked as unsafe at
parse time, instead of at execution time. This allows propagating
unsafe-ness to the view.
the auto_increment value
This is an alternative patch that instead of allowing RECREATE TABLE
on TRUNCATE TABLE it implements reset_auto_increment that is called
after delete_all_rows.
Note: this bug was fixed by Mattias Jonsson:
Pusing this patch: http://lists.mysql.com/commits/70370
mysql-test/suite/parts/r/partition_auto_increment_memory.result:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
mysql-test/suite/parts/r/partition_auto_increment_myisam.result:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
sql/ha_partition.cc:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
Added reset_auto_increment, to be used after delete_all_rows
to simulate truncate.
storage/heap/ha_heap.cc:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
Added reset_auto_increment, to be used after delete_all_rows
to simulate truncate
storage/heap/ha_heap.h:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
Added reset_auto_increment, to be used after delete_all_rows
to simulate truncate
storage/myisam/ha_myisam.cc:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
Added reset_auto_increment, to be used after delete_all_rows
to simulate truncate.
storage/myisam/ha_myisam.h:
Bug#35111: Truncate a MyISAM partitioned table does not reset
the auto_increment value
Added reset_auto_increment, to be used after delete_all_rows
to simulate truncate.
the auto_increment value
This is an alternative patch that instead of allowing RECREATE TABLE
on TRUNCATE TABLE it implements reset_auto_increment that is called
after delete_all_rows.
Note: this bug was fixed by Mattias Jonsson:
Pusing this patch: http://lists.mysql.com/commits/70370
mysql-test/r/information_schema.result:
Fixed a result file.
mysql-test/r/innodb-autoinc.result:
Fixed a result file.
mysql-test/t/connect.test:
Fixed a problem with merge, needed to close
connections and use the default
There were so many changes into mtr (this is the new mtr coming) that I rather
copied mtr from 6.0-main here (at least this one knows how to run Maria tests).
I also fixed suite/maria tests to be accepted by the new mtr.
mysys/thr_mutex.c:
adding DBUG_PRINT here, so that we can locate where the warning is issued.
Problem is that DATA_FREE in SHOW TABLE STATUS
is not correct when not using innodb_file_per_table.
The solution is to use I_S.PARTITIONS instead.
This is only a small fix for correcting mean record length and
always return 0 if the table is empty.
sql/ha_partition.cc:
Bug#36312: InnoDB DATA_FREE BUG?
This is only a small fix for correcting mean record length and
always return 0 if the table is empty.
Problem is that DATA_FREE in SHOW TABLE STATUS
is not correct when not using innodb_file_per_table.
The solution is to use I_S.PARTITIONS instead.
This is only a small fix for correcting mean record length and
always return 0 if the table is empty.
