IS EXECUTED TWICE FROM P

This bug is a duplicate of bug 12567331, which was pushed to the
optimizer backporting tree on 2011-06-11. This is just a back-port of
the fix. Both test cases are included as they differ somewhat.

GRACEFUL SHUTDOWN

During startup mysql picks up .frm files from the tmpdir directory and
tries to drop those tables in the storage engine.

The problem is that when tmpdir ends in / then ha_innobase::delete_table()
is passed a string like "/var/tmp//#sql123", then it wrongly normalizes it
to "/#sql123" and calls row_drop_table_for_mysql() which of course fails
to delete the table entry from the InnoDB dictionary cache.
ha_innobase::delete_table() returns an error but nevertheless mysql wipes
away the .frm file and the entry in the InnoDB dictionary cache remains
orphaned with no easy way to remove it.

The "no easy" way to remove it is to create a similar temporary table again,
copy its .frm file to tmpdir under "#sql123.frm" and restart mysqld with
tmpdir=/var/tmp (no trailing slash) - this way mysql will pick the .frm file
after restart and will try to issue drop table for "/var/tmp/#sql123"
(notice do double slash), ha_innobase::delete_table() will normalize it to
"tmp/#sql123" and row_drop_table_for_mysql() will successfully remove the
table entry from the dictionary cache.

The solution is to fix normalize_table_name_low() to normalize things like
"/var/tmp//table" correctly to "tmp/table".

This patch also adds a test function which invokes
normalize_table_name_low() with various inputs to make sure it works
correctly and a mtr test that calls this test function.

Reviewed by:	Marko (http://bur03.no.oracle.com/rb/r/929/)

                      CORRUPTED WHEN RUN CONCURRENTLY WITH

ISSUE: Table corruption due to concurrent queries.
       Different threads running check, repair query
       along with insert. Locks not properly acquired
       in repair query. Rows are inserted inbetween
       repair query.

SOLUTION: Mutex lock is acquired before the
          repair call. Concurrent queries wont
          effect the call to repair.

Recording correct test results.

modified:
  mysql-test/suite/engines/funcs/r/db_alter_collate_ascii.result
  mysql-test/suite/engines/funcs/r/db_alter_collate_utf8.result

row_merge_buf_write(): Relax the bogus assertion.

                      CASES RESETS DATA POINTER TO SMAL

ISSUE: Myisamchk doing sort recover
       on a table reduces data_file_length.
       Maximum size of data file decreases,
       lesser number of rows are stored.

SOLUTION: Size of data_file_length is
          fixed to the original length.

                      CASES RESETS DATA POINTER TO SMAL

ISSUE: Myisamchk doing sort recover
       on a table reduces data_file_length.
       Maximum size of data file decreases,
       lesser number of rows are stored.

SOLUTION: Size of data_file_length is
          fixed to the original length.

rb://914
approved by: Marko Makela

Poll in fil_rename_tablespace() after setting ::stop_ios flag can
result in a hang because the other thread actually dispatching the IO
won't wake IO helper threads or flush the tablespace before starting
wait in fil_mutex_enter_and_prepare_for_io().

No commit message

Fix for #36428/#38364 backported into 5.0.

      KEY HANDLING ON SUBSEQUENT CREATE TABLE IF NOT EXISTS
      
      PROBLEM:
      --------
      Consider a SP routine which does CREATE TABLE
      with REFERENCES clause. The first call to this routine
      invokes parser and the parsed items are cached, so as 
      to avoid parsing for the second execution of the routine.
      
      It is obsevered that valgrind reports a warning
      upon read of thd->lex->alter_info->key_list->Foreign_key object,
      which seem to be pointing to a invalid memory address
      during second time execution of the routine. Accessing this object
      theoretically could cause a crash.
      
      ANALYSIS:
      ---------
      The problem stems from the fact that for some reason
      elements of ref_columns list in thd->lex->alter_info->
      key_list->Foreign_key object are changed to point to
      objects allocated on runtime memory root.
      
      During the first execution of routine we create
      a copy of thd->lex->alter_info object.
      As part of this process we create a clones of objects in
      Alter_info::key_list and of Foreign_key object in particular.
      Then Foreign_key object is cloned for some reason we
      perform shallow copies of both Foreign_key::ref_columns
      and Foreign_key::columns list. So new instance of 
      Foreign_key object starts to SHARE contents of ref_columns
      and columns list with the original instance.
      After that as part of cloning process we call
      list_copy_and_replace_each_value() for elements of
      ref_columns list. As result ref_columns lists in both
      original and cloned Foreign_key object start to contain
      pointers to Key_part_spec objects allocated on runtime
      memory root because of shallow copy.
      
      So when we start copying of thd->lex->alter_info object
      during the second execution of stored routine we indeed
      encounter pointer to the Key_part_spec object allocated
      on runtime mem-root which was cleared during at the end
      of previous execution. This is done in sp_head::execute(), 
      by a call to free_root(&execute_mem_root,MYF(0));
      As result we get valgrind warnings about accessing 
      unreferenced memory.
      
      FIX:
      ----
      The safest solution to this problem is to 
      fix Foreign_key(Foreign_key, MEM_ROOT) constructor to do
      a deep copy of columns lists, similar to Key(Key, MEM_ROOT) 
      constructor.

Bug#13011410 CRASH IN FILESORT CODE WITH GROUP BY/ROLLUP

The assert in 13580775 is visible in 5.6 only, 
but shows that all versions are vulnerable.
13011410 crashes in all versions.

filesort tries to re-use the sort buffer between invocations in order to save
malloc/free overhead.
The fix for Bug 11748783 - 37359: FILESORT CAN BE MORE EFFICIENT.
added an assert that buffer properties (num_records, record_length) are
consistent between invocations. Indeed, they are not necessarily consistent.
  
Fix: re-allocate the sort buffer if properties change.


mysql-test/r/partition.result:
  New tests.
mysql-test/t/partition.test:
  New tests.
sql/filesort.cc:
  If we already have allocated a sort buffer in a previous execution,
  then verify that it is big enough for the current one.
sql/table.h:
  Add sort_keys_size; Number of bytes allocated for the sort_keys buffer.

BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND
WITH/WITHOUT INDEX RANGE SCAN
BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG
RESULTS WITH DECIMAL CONVERSION
BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG
RESULT AFTER MYSQL 5.1.
Those are all cases where the range optimizer got it wrong
with > and >=.

mysql-test/r/range.result:
  Without the code fix for DECIMAL, "select count(val) from t2 where val > 0.1155"
  (which uses a range scan) returned 127 instead of 128);
  Moreover, both
  select * from t1 force  index (primary) where a=1 and c>= 2.9;
  and
  select * from t1 force  index (primary) where a=1 and c> 2.9;
  would miss "1	1	3".
  Without the code fix for strings, both
  SELECT * FROM t1 WHERE F1 >= 'A    ';
  and
  SELECT * FROM t1 WHERE F1 BETWEEN 'A    ' AND 'AAAAA';
  would miss "A	A	A".
sql/item.cc:
  Preamble to the explanations below: opt_range.cc:get_mm_leaf() does
  this (this is not changed by the patch): changes
  column > value
  to
  column OP V
  where:
  * V is what is in "column" after we stored "value" in it
  (such store operation may have done rounding...)
  * OP is > or >=, depending on what's correct.
  For example, if c is an INT column,
  c > 2.9 is changed to
  c OP 3
  where OP is >= ('>' would not be correct).
  The bugs below are cases where we chose OP wrongly.
  Note that such transformations are visible in the optimizer trace.
  
  1) Fix for STRING. In the scenario with CHAR(5) in range.test, this happens,
  in get_mm_tree(), for the condition F1>='A    ':
  * value->save_in_field_no_warnings(field, 1) wants to store the right argument
  (named 'item') into the CHAR(5) field; this stores 'A    ' (the item's value)
  padded with spaces (which changes nothing: still 'A    ')
  * we come to
    case Item_func::GE_FUNC:
      /* Don't use open ranges for partial key_segments */
      if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
          (stored_field_cmp_to_item(param->thd, field, value) < 0))
        tree->min_flag= NEAR_MIN;
      tree->max_flag=NO_MAX_RANGE;
  What this wants to do is: if the field's value is strictly smaller
  than the item's, then ">=" can be changed to ">" (this is an optimization,
  it can help pruning one useless partition).
  * stored_field_cmp_to_item() is called; it compares the field's
  and item's values: the item's value (Item_string::val_str()) is
  'A    ') and the field's value (Field_string::val_str()) is
  'A' (yes val_str() removes end spaces unless sql_mode='PAD_CHAR_TO_FULL_LENGTH');
  and the comparison is done with stringcmp() which considers
  end spaces as relevant; as end spaces differ, function returns a
  negative number, and ">='A    '" becomes ">'A'" (i.e. the NEAR_MIN
  flag is turned on).
  During execution the index range scan code will search for "A", find
  a match, but exclude it (because of ">"), wrongly.
  The badness is the string comparison done by stored_field_cmp_to_item():
  we use the reply of this function to determine where the index search
  should start, so it should do comparison like index search does
  comparisons; index search comparisons are ha_key_cmp() which uses
  a collation-aware comparison (in our case, my_strnncollsp_simple(),
  which ignores end spaces); so stored_field_cmp_to_item()
  needs to do the same. When this is fixed, condition becomes
  ">='A    '".
  
  2) Fix for DECIMAL: just like in other comparisons in stored_field_cmp_to_item(),
  we must first pass the field and then the item; otherwise expectations
  on what <0 and >0 mean (inferiority, superiority) get violated.
  In the test in range.test about c>2.9: c is an INT column, so 2.9
  gets stored as 3, then stored_field_cmp_to_item() compares 3
  and 2.9; because of the wrong order of arguments passed
  to my_decimal_cmp(), range optimizer
  thinks that 3 is < 2.9 and thus changes "c> 2.9" to "c> 3".
  After fixing the order, it changes to the correct "c>= 3".
  In the test in range.inc for val > 0.1155, it was changed to
  val > 0.116, now it is changed to val >= 0.116.

and some Valgrind instrumentation.

- Reverting the patch for Bug # 12584302
  The patch will be reverted in 5.1 and 5.5.
  The patch will not be reverted in 5.6, the change will
  be properly documented in 5.6.
- Backporting DBUG_ASSERT not to crash on '0000-01-00'
  (already fixed in mysql-trunk (5.6))

Introducing new collations:
utf8_general_mysql500_ci and ucs2_general_mysql500_ci,
to reproduce behaviour of utf8_general_ci and ucs2_general_ci
from mysql-5.1.23 (and earlier).

The collations are added to simplify upgrade from mysql-5.1.23 and earlier.

Note: The patch does not make new server start over old data automatically.
Some manual upgrade procedures are assumed.

Paul: please get in touch with me to discuss upgrade procedures
when documenting this bug.

modified:
  include/m_ctype.h
  mysql-test/r/ctype_utf8.result
  mysql-test/t/ctype_utf8.test
  mysys/charset-def.c
  strings/ctype-ucs2.c
  strings/ctype-utf8.c

                     DUMP ROUTINES

Minor post-fix to avoid build failure when built with
Werror.

Relax a bogus debug assertion.
Approved by Jimmy Yang on IM.

Test extra/rpl_tests/rpl_extra_col_master.test (used by
rpl_extra_col_master_*) ends with the active connection pointing to the
slave. Thus, the two last tests never succeed in changing the binlog
format of the master away from 'row'. With correct active connection
(master) tests fail for binlog 'statement' and 'mixed' formats.

Tests rpl_extra_col_master_* only run when binary log format is
row.  Statement and mix replication do not make sense in this
tests since it will try to execute statements on columns that do
not exist.  This fix is basically a backport from mysql-5.5, see
changes done as part of BUG 39934.

When mode==BUF_KEEP_OLD, buffered inserts are being merged to the page.
It is possible that a read request for a page was pending while the page
was freed in DROP INDEX or DROP TABLE. In these cases, it is OK (although
useless) to merge the buffered changes to the freed page.

ISSUES WITH COPYING PARTITIONED INNODB TABLES FROM LINUX TO WINDOWS

This problem was already fixed in mysql-trunk as part of bug #11755924.  I am 
backporting the fix to mysql-5.1.  

                     routines.

mysqldump in xml mode did not dump routines, events or
triggers.

This patch fixes this issue by fixing the if conditions
that disallowed the dump of above mentioned objects in
xml mode, and added the required code to enable dump
in xml format.


client/mysqldump.c:
  BUG#11760384 - 52792: mysqldump in XML mode does not dump
                        routines.
  
  Fixed some if conditions to allow execution of dump methods
  for xml and further added the relevant code at places to produce
  the dump in xml format.
mysql-test/r/mysqldump.result:
  Added a test case for Bug#11760384.
mysql-test/t/mysqldump.test:
  Added a test case for Bug#11760384.

If we meet DB_TOO_MANY_CONCURRENT_TRXS during the execution tab_create_graph from row_create_table_for_mysql(), .ibd file for the table should be created already but was not deleted for the error handling.

rb:875 approved by Jimmy Yang

------------------------------------------------------------
revno: 3258
committer: Jon Olav Hauglid <jon.hauglid@oracle.com>
branch nick: mysql-trunk-bug12663165
timestamp: Thu 2011-07-14 10:05:12 +0200
message:
  Bug#12663165 SP DEAD CODE REMOVAL DOESN'T UNDERSTAND CONTINUE HANDLERS
  
  When stored routines are loaded, a simple optimizer tries to locate
  and remove dead code. The problem was that this dead code removal
  did not work correctly with CONTINUE handlers.
  
  If a statement triggers a CONTINUE handler, the following statement
  will be executed after the handler statement has completed. This
  means that the following statement is not dead code even if the
  previous statement unconditionally alters control flow. This fact
  was lost on the dead code removal routine, which ended up with
  removing instructions that could have been executed. This could
  then lead to assertions, crashes and generally bad behavior when
  the stored routine was executed.
  
  This patch fixes the problem by marking as live code all stored
  routine instructions that are in the same scope as a CONTINUE handler.
  
  Test case added to sp.test.

Fixed it to work with "--repeat" option.

Fixed the testcase using timestamp logic while doing grep from the error file.