Bug 12635227 - 61188: DROP TABLE EXTREMELY SLOW

This patch fixes a typo in a YaSSL error message.

TO POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED" into
mysql-5.5 tree.

TO POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
 
ALTER TABLE MODIFY/CHANGE ... FIRST did nothing except renaming
columns if new version of the table had exactly the same 
structure as the old one (i.e. as result of such statement, names 
of columns changed their order as specified but data in columns 
didn't). The same thing happened for ALTER TABLE DROP COLUMN/ADD 
COLUMN statements which were supposed to produce new version of
table with exactly the same structure as the old version of table.
I.e. in the latter case the result was the same as if old column 
was renamed instead of being dropped and new column with default
as value being created.
 
Both these problems were caused by the fact that ALTER TABLE
implementation incorrectly interpreted both these situations as 
simple renaming of columns and assumed that in-place ALTER TABLE
algorithm could have been used for them.
 
This patch fixes this problem by ensuring that in cases when some
column is moved to the first position or some column is dropped
the default ALTER TABLE algorithm involving table copying is 
always used. This is achieved by detecting such situations in
mysql_prepare_alter_table() and setting Alter_info::change_level
to ALTER_TABLE_DATA_CHANGED for them.

mysql-test/r/alter_table.result:
  Added test for bug #12652385 - "61493: REORDERING COLUMNS TO
  POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
mysql-test/t/alter_table.test:
  Added test for bug #12652385 - "61493: REORDERING COLUMNS TO
  POSITION FIRST CAN CAUSE DATA TO BE CORRUPTED".
sql/sql_table.cc:
  Changed mysql_prepare_alter_table() to detect situations in 
  which we some column moved to the first position or some column 
  is dropped and ensure that such ALTER TABLE statements won't
  be carried out using in-place algorithm. The latter could have 
  happened before this patch if new version of table had the same 
  structure as the old one (except the column names).

Bug 12430414 - THE TEST PERFSCHEMA.SELECTS.TEST CAN AFFECT SUCCEEDING TESTS
Bug 12430599 - THE TEST PERFSCHEMA.ONE_THREAD_PER_CON. CAN AFFECT SUCCEEDING TESTS
Bug 12431153 - THE TEST PERFSCHEMA.PFS_UPGRADE CAN AFFECT SUCCEEDING TEST

GRADUALLY IF A TRIGGER EXISTS".

This bug manifested itself in two ways:

- Firstly execution of any data-changing statement which
  required prelocking (i.e. involved stored function or
  trigger) as part of transaction slowed down a bit all
  subsequent statements in this transaction. So performance
  in transaction which periodically involved such statements
  gradually degraded over time.
- Secondly execution of any data-changing statement which
  required prelocking as part of transaction prevented
  concurrent FLUSH TABLES WITH READ LOCK from proceeding
  until the end of transaction instead of end of particular
  statement.
  
The problem was caused by incorrect handling of metadata lock
used in FTWRL implementation for statements requiring prelocked 
mode. 
Each statement which changes data acquires global IX lock
with STATEMENT duration. This lock is supposed to block 
concurrent FTWRL from proceeding until the statement ends.

When entering prelocked mode, durations of all metadata locks
acquired so far were changed to EXPLICIT, to prevent 
substatements from releasing these locks. When prelocked mode
was left, durations of metadata locks were changed to
TRANSACTIONAL (with a few exceptions) so they can be properly
released at the end of transaction. 
Unfortunately, this meant that the global IX lock blocking
FTWRL with STATEMENT duration was moved to TRANSACTIONAL
duration after execution of statement requiring prelocking.

Since each subsequent statement that required prelocking and
tried to acquire global IX lock with STATEMENT duration got
a new instance of MDL_ticket, which was later moved to
TRANSACTIONAL duration, this led to unwarranted growth of
number of tickets with TRANSACITONAL duration in this
connection's MDL_context. As result searching for other
tickets in it became slow and acquisition of other metadata
locks by this transaction started to hog CPU.

Moreover, this also meant that after execution of statement
requiring prelocking concurrent FTWRL was blocked
until the end of transaction instead of end of statement.

This patch solves this problem by not moving locks to EXPLICIT
duration when thread enters prelocked mode (unless it is a real 
LOCK TABLES mode). This step turned out to be not really 
necessary as substatements don't try to release metadata locks.
Consequently, the global IX lock blocking FTWRL keeps its
STATEMENT duration and is properly released at the end of
statement and the above issue goes away.

mysql-test/r/flush.result:
  Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
  DEGRADES GRADUALLY IF A TRIGGER EXISTS".
mysql-test/t/flush.test:
  Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
  DEGRADES GRADUALLY IF A TRIGGER EXISTS".
sql/mdl.h:
  Added comment describing various types of metadata lock
  duration.
sql/sql_class.cc:
  Since we no longer change duration of metadata locks to EXPLICIT
  when entering prelocked mode (unless it is a real LOCK TABLES)
  there is no need to restore proper duration of the locks when
  leaving prelocked mode.
sql/sql_class.h:
  Do not change duration of metadata locks to EXPLICIT when
  entering prelocking mode (unless it is a real LOCK TABLES).
  This allows to avoid problems with restoring correct duration
  when leaving this mode. It is possible to do this as
  substatements won't release metadata locks in any case.
sql/sql_parse.cc:
  Added assert checking that we won't release metadata locks
  when in substatement.

Issue:
------
New test case mysql_embedded.test was failing on pb2.

Description:
------------
To run this test case executable libmysqld/examples/mysql_embedded is required.
But as per /libmysqld/examples/cmake_install.cmake this executable doesn't get
copied to <install_dir> when mysql is installed at <install_dir>.That is the
reason it was passing in my local branch and failed on pb2 when pushed.

Solution;
---------
Added code in mysql-test-run.pl, which will try to see if this file exists.If
It doesn't exist, test case will be skipped with a skip message. New code in
mysql-test-run.pl looks only for directory libmysqld/examples/mysql_embedded
because this is the only place where this file could/does exist.

mysql-test/mysql-test-run.pl:
  Added new variable for mysql_embedded executable.
mysql-test/t/disabled.def:
  enabled mysql_embedded.test which was disabled earlier.
mysql-test/t/mysql_embedded.test:
  Modified test case to first verify if mysql_embedded executable exists. If
  it does not, skip the test.

marko.makela@oracle.com-20110616072721-8bo92ctixq6eqavr

The buf_pool->zip_clean list is only needed for debugging, or for
recomputing buf_pool->page_hash when resizing the buffer pool. Buffer
pool resizing was never fully implemented. Remove the resizing code,
and define buf_pool->zip_clean only in debug builds.

buf_pool->zip_clean, buf_LRU_insert_zip_clean(): Enclose in
#if defined UNIV_DEBUG || UNIV_BUF_DEBUG.

buf_chunk_free(), buf_chunk_all_free(), buf_pool_shrink(),
buf_pool_page_hash_rebuild(), buf_pool_resize(): Remove (unreachable code).

rb:671 approved by Inaam Rana

There is an apparent problem with page_zip_clear_rec().
In btr_cur_optimistic_update() we do this:

	page_cur_delete_rec(page_cursor, index, offsets, mtr);
...
	rec = btr_cur_insert_if_possible(cursor, new_entry, 0/*n_ext*/, mtr);
	ut_a(rec); /* <- We calculated above the insert would fit */

The problem is that page_cur_delete_rec() could fill the modification
log while doing page_zip_clear_rec(), requiring recompression for the
btr_cur_insert_if_possible(). In a pathological case, the data could
fail to recompress.

page_zip_clear_rec(): Leave the page modification log alone. Only
clear the necessary fields.

rb:673 approved by Jimmy Yang

Replace UNIV_BLOB_NULL_DEBUG with UNIV_DEBUG||UNIV_BLOB_LIGHT_DEBUG. 
Fix known bogus failures.

btr_cur_optimistic_update(): If rec_offs_any_null_extern(), assert that
the current transaction is an incomplete transaction that is being
rolled back in crash recovery.

row_build(): If rec_offs_any_null_extern(), assert that the transaction
that last updated the record was recovered during crash recovery
(and will soon be rolled back).

btr_cur_compress_if_useful(), btr_compress(): Add the parameter ibool
adjust. If adjust=TRUE, adjust the cursor position after compressing
the page.

btr_lift_page_up(): Return a pointer to the father page.

BTR_KEEP_POS_FLAG: A new flag for btr_cur_pessimistic_update().

btr_cur_pessimistic_update(): If *big_rec != NULL and flags &
BTR_KEEP_POS_FLAG, keep the cursor positioned on the updated record.
Also, do not release the index tree x-lock if *big_rec != NULL.

btr_cur_mtr_commit_and_start(): Commits and restarts a
mini-transaction so that it will retain an x-lock on index->lock and
the page of the cursor. This is invoked when
btr_cur_pessimistic_update() returns *big_rec != NULL.

In all callers of btr_cur_pessimistic_update() that do not pass
BTR_KEEP_POS_FLAG, assert that *big_rec == NULL.

btr_cur_compress(): Unused function [in the built-in MySQL 5.1], remove.

page_rec_get_nth(): Return the nth record on the page (an inverse
function of page_rec_get_n_recs_before()). Refactored from
page_get_middle_rec().

page_get_middle_rec(): Invoke page_rec_get_nth().

page_cur_insert_rec_zip_reorg(): Make use of the page directory
shortcuts in page_rec_get_nth() instead of scanning the whole list of
records.

row_ins_clust_index_entry_by_modify(): Pass BTR_KEEP_POS_FLAG to
btr_cur_pessimistic_update().

row_ins_index_entry_low(): If row_ins_clust_index_entry_by_modify()
returns a big_rec, invoke btr_cur_mtr_commit_and_start() in order to
commit and start the mini-transaction without releasing the x-locks on
index->lock and the cursor page, and write the big_rec. Releasing the
page latch in mtr_commit() caused a race condition.

row_upd_clust_rec(): Pass BTR_KEEP_POS_FLAG to
btr_cur_pessimistic_update(). If it returns a big_rec, invoke
btr_cur_mtr_commit_and_start() in order to commit and start the
mini-transaction without releasing the x-locks on index->lock and the
cursor page, and write the big_rec. Releasing the page latch in
mtr_commit() caused a race condition.

sync_thread_add_level(): Add the parameter ibool relock. When TRUE,
bypass the latching order rules.

rw_lock_add_debug_info(): For nested X-lock requests, pass relock=TRUE
to sync_thread_add_level().

rb:678 approved by Jimmy Yang

              UPDATED TWICE

For multi update it is not allowed to update a column
of a table if that table is accessed through multiple aliases
and either
1) the updated column is used as partitioning key
2) the updated column is part of the primary key 
   and the primary key is clustered

This check is done in unsafe_key_update().

The bug was that for case 2), it was checked whether
updated_column_number == table_share->primary_key 
However, the primary_key variable is the index number of the 
primary key, not a column number.

Prior to this bugfix, the first column was wrongly believed to be
the primary key. The columns covered by an index is found in
table->key_info[idx_number]->key_part. The bugfix is to check if
any of the columns in the keyparts of the primary key are
updated.

The user-visible effect is that for storage engines with
clustered primary key (e.g. InnoDB but not MyISAM) queries
like 
"UPDATE t1 AS A JOIN t2 AS B SET A.primkey=..."
will now error with 
"ERROR HY000: Primary key/partition key update is not allowed 
since the table is updated both as 'A' and 'B'." 
instead of 
"ERROR 1032 (HY000): Can't find record in 't1_tb'"
even if primkey is not the first column in the table. This 
was the intended behavior of bugfix 11764529.


mysql-test/r/multi_update.result:
  Add test for bug#11882110
mysql-test/r/multi_update_innodb.result:
  Add test for bug#11882110
mysql-test/t/multi_update.test:
  Add test for bug#11882110
mysql-test/t/multi_update_innodb.test:
  Add test for bug#11882110
sql/sql_update.cc:
  unsafe_key_update() wrongly checked if the primary key index
  number was the same as updated column number. Now it is checked
  whether any of the columns making up the primary key is updated.
sql/table.h:
  Fix comment on TABLE_SHARE::primary_key. Incorrect comment
  was introduced by an earlier merge conflict (as per dlenev)

can't parse relative paths "higher" than 3 levels up

When trying to LOAD DATA LOCAL INFILE using a relative path with 3 or
more levels up in the directory hierarchy, mysqld wrongly parses 
the path and as a consequence, can't find the file.

This bug was introduced by patch for bug#58205.
The reason for bug is that implementaiton of function cleanup_dirname()
doesn't take into account the begin of buffer being processed during
handling of path to file.


mysys/mf_pack.c:
  function cleanup_dirname() was modified: fixed wrong comparison
  condition when handling substring "../" at the begining of the buffer.

Some ut_a(!rec_offs_any_null_extern()) assertion failures are indicating
genuine BLOB bugs, others are bogus failures when rolling back incomplete
transactions at crash recovery. This needs more work, and until I get a
chance to work on it, other testing must not be disrupted by this.

revno 2995.37.209
revision id marko.makela@oracle.com-20110518120508-qhn7vz814vn77v5k
parent marko.makela@oracle.com-20110517121555-lmple24qzxqkzep4
timestamp: Wed 2011-05-18 15:05:08 +0300
message:
  Fix a bogus UNIV_SYNC_DEBUG failure in the fix of Bug #59641
  or Oracle Bug #11766513.

  trx_undo_free_prepared(): Do not acquire or release trx->rseg->mutex.
  This code is invoked in the single-threaded part of shutdown, therefore
  a mutex is not needed.

Issue:
When libmysqld/example/mysql_embedded is executed, it was getting abort. Its a
regression as it was working in 5.1 and failed in 5.5. Issue is there because 
remaining_argc/remaining_argv were not getting assigned correctly in 
init_embedded_server() which were being used later in init_common_variable().

Solution:
Rectified code to pass correct argc/argv to be used in init_common_variable().

libmysqld/lib_sql.cc:
  Rectified remaining_argc/remaining_argv assignment.
mysql-test/r/mysql_embedded.result:
  Result file for the test case added.
mysql-test/t/mysql_embedded.test:
  Added test case to verify libmysqld/example/mysql_embedded works.

               COLUMNS IN VIEWS

Issue:
charset value for a Column, returned by MYSQL_LIST_FIELDS(), was not same
for Table and View. This was because, for view, field charset was not being
returned.

Solution:
Added definition of function "charset_for_protocol()" in calss 
Item_ident_for_show to return field charset value.

sql/item.h:
  Added definition for charset_for_protocol() function to return field charset.
tests/mysql_client_test.c:
  Added a test case test_bug12337762 for the changes done.

This test case was failing on 5.5 and trunk for two reasons.
1) It waited for the "Waiting for table level lock" process
   state while this state was renamed "Waiting for table
   metadata lock" with the introduction of MDL in 5.5.
2) SET GLOBAL query_cache_size= 100000; gave a warning since
   query_cache_size is supposed to be multiples of 1024.

This patch fixes these two issues and re-enables the test case.

               RESULT CONSISTED OF MORE THAN ONE ROW

MySQL converts incorrect DATEs and DATETIMEs to '0000-00-00' on
insertion by default. This means that this sequence is possible:

CREATE TABLE t1(date_notnull DATE NOT NULL);
INSERT INTO t1 values (NULL);
SELECT * FROM t1;
0000-00-00

At the same time, ODBC drivers do not (or at least did not in the
90's) understand the DATE and DATETIME value '0000-00-00'. Thus,
to be able to query for the value 0000-00-00 it was decided in
MySQL 4.x (or maybe even before that) that for the special case
of DATE/DATETIME NOT NULL columns, the query "SELECT ... WHERE
date_notnull IS NULL" should return rows with date_notnull ==
'0000-00-00'. This is documented misbehavior that we do not want
to change.

The hack used to make MySQL return these rows is to convert 
"date_notnull IS NULL" to "date_notnull = 0". This is, however,
only done if the table date_notnull belongs to is not an inner
table of an outer join. The rationale for this seems to be that
if there is no join match for the row in the outer table,
null-complemented rows would otherwise not be returned because
the null-complemented DATE value is actually NULL. On the other
hand, this means that the "return rows with 0000-00-00 when the
query asks for IS NULL"-hack is not in effect for outer joins.

In this bug, we have a LEFT JOIN that does not misbehave like 
the documentation says it should. The fix is to rewrite

"date_notnull IS NULL" to "date_notnull IS NULL OR 
                           date_notnull = 0"
if dealing with an OUTER JOIN, otherwise 
"date_notnull IS NULL" to "date_notnull = 0"
as was done before.

Note:
The bug was originally reported as different result on first 
and second execution of SP. The reason was that during first
execution the query was correctly rewritten to an inner join
due to a null-rejecting predicate. On second execution the
"IS NULL" -> "= 0" rewrite was done because there was no outer
join. The real problem, though, was incorrect date/datetime 
IS NULL handling for OUTER JOINs.

mysql-test/r/type_datetime.result:
  Add test for BUG#12561818
mysql-test/t/type_datetime.test:
  Add test for BUG#12561818
sql/sql_select.cc:
  Special handling of NULL for DATE/DATETIME NOT NULL columns:
  In the case of outer join,
  "date_notnull IS NULL" 
  is now rewritten to
  "date_notnull IS NULL OR date_notnull = 0"

cmake/make_dist.cmake.in:
  Run 'bzr export' for plugins.
cmake/plugin.cmake:
  Lookup plugins with bzr repos.