TABLES IN INCORRECT ENGINE

PROBLEM:
  CREATE/ALTER TABLE currently can move system tables like
mysql.db, user, host etc, to engines other than MyISAM. This is not
completely supported as of now, by mysqld. When some of system tables
like plugin, servers, event, func, *_priv, time_zone* are moved
to innodb, mysqld restart crashes. Currently system tables
can be moved to BLACKHOLE also!!!.

ANALYSIS:
  The problem is that there is no check before creating or moving
a system table to some particular engine.

  System tables are suppose to be residing in MyISAM. We can think
of restricting system tables to exist only in MyISAM. But, there could
be future needs of these system tables to be part of other engines
by design. For eg, NDB cluster expects some tables to be on innodb
or ndb engine. This calls for a solution, by which system
tables can be supported by any desired engine, with minimal effort.

FIX:
  The solution provides a handlerton interface using which,
mysqld server can query particular storage engine handlerton for
system tables that it supports. This way each storage engine
layer can define their own system database and system tables.

  The check_engine() function uses the new handlerton function
ha_check_if_supported_system_table() to check if db.tablename
provided in the DDL is supported by the SE.

Note: This fix has modified a test in help.test, which was moving
mysql.help_* to innodb. The primary intention of the test was not
to move them between engines.

Problem - The cause of the failure is mainly due to the assert added in
          the code as a result of the fix of the BUG-13333431. When we
          start the server with the --skip-networking option enabled
          we have the mysqld_port explicitly to 0. Since the value of
          report_port is set to mysqld_port, the assertion that
          (report_port!= 0) fails.

Fix - the fix of the problem is to assert the not zero value of
      report_port only in the case the --skip-networking option is not
      used to start the mysqld server.

sql/mysqld.cc:
  Added the code to check for the skip-networking option while starting
  the server.

Problem - this failure occured in the test added for the fix of the 
          bug-13333431. The basic problem of the failure was the 
          value of the report_port which persisted even after the end 
          of the test (ie. rpl_end.inc). So this causes the assertion 
          in the test to fail if it is executed again.

Fix - restarted the server with the default value being passed to the 
      report_port after testing the two expected case so that in the 
      next run of the test we will not encounter the previous value of
      report_port.



mysql-test/suite/rpl/r/rpl_report_port.result:
  Updated the corresponding result file.
mysql-test/suite/rpl/t/rpl_report_port-slave.opt:
  Removed the slave option file.
mysql-test/suite/rpl/t/rpl_report_port.test:
  Added the restart server option before ending the test.

This bug is a duplicate of Bug #31173, which was pushed to the 
mysql-trunk 5.6 on 4th Aug, 2010. This is just a back-port of 
the fix


mysql-test/r/mysqlslap.result:
  A test added as part of the fix for Bug #59107
mysql-test/t/mysqlslap.test:
  A test added as part of the fix for Bug #59107

Merge : 5.1->5.5

Background : 
In mysql-5.1, in a fix for bug#47485, code has been changed for 
mysql client (libmysql/libmysql.c) but corresponding code was not
changed for embedded mysql. In that code change, after execution
of a statement, mysql_stmt_store_result() checks for mysql->state
to be MYSQL_STATUS_STATEMENT_GET_RESULT, instead of
MYSQL_STATUS_GET_RESULT (earlier).

Reason:
In embedded mysql code, after execution, mysql->state was not
set to MYSQL_STATUS_STATEMENT_GET_RESULT, so it was throwing
OUT_OF_SYNC error.

Fix:
Fixed the code in libmysqld/lib_sql.cc to have mysql->state
to be set to MYSQL_STATUS_STATEMENT_GET_RESULT after execution.

                     IPV4/IPV6 ENABLED

Analysis:
----------------------
The problem was that if a hostname resolves to more than one IP-address,
the server (5.5) does not start due to an error. In 5.1 the server used to
take some IP-address and start.

It's a regression and should be fixed.

5.5 supports IPv6, while 5.1 does not. However, that should not 
prevent the server from start -- if a hostname has both IPv4 and IPv6 addresses,
the server should choose some IPv4-address and start.

It's been decided to prefer IPv4-address to be backward compatible with 5.1.

Another problem was that the 5.6 server did not report proper error message
when the specified hostname could not be resolved. So, the code has been 
changed to report proper error message.

Testing
================================
5.5
=============================
invalid hostname (localhos):
  => Following error message reported.
     120308 15:52:09 [ERROR] Can't start server: cannot resolve hostname!
     120308 15:52:09 [ERROR] Aborting

invalid ip_address:
  => Following error message reported.
      120308 15:56:06 [Note] Server hostname (bind-address): '123.123.123.123'; port: 3306
      120308 15:56:06 [Note]   - '123.123.123.123' resolves to '123.123.123.123';
      120308 15:56:06 [Note] Server socket created on IP: '123.123.123.123'.
      120308 15:56:06 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address

Only ipv4 host configured:
  => Following message logged 
    120308 16:02:50 [Note] Server hostname (bind-address): 'localhost'; port: 3306
    120308 16:02:50 [Note]   - 'localhost' resolves to '127.0.0.1';
    120308 16:02:50 [Note] Server socket created on IP: '127.0.0.1'

Only ipv6 host configured:    
  => Following message logged 
    120308 16:04:03 [Note] Server hostname (bind-address): 'localhost'; port: 3306
    120308 16:04:03 [Note]   - 'localhost' resolves to '::1';
    120308 16:04:03 [Note] Server socket created on IP: '::1'.

ipv4 and ipv6 host configured:
  => Following message logged
    120308 16:05:02 [Note] Server hostname (bind-address): 'localhost'; port: 3306
    120308 16:05:02 [Note]   - 'localhost' resolves to '::1';
    120308 16:05:02 [Note]   - 'localhost' resolves to '127.0.0.1';
    120308 16:05:02 [Note] Server socket created on IP: '127.0.0.1'.
  => Non localhost address  
    120308 16:08:20 [Note] Server hostname (bind-address): 'mysql_addr'; port: 3306
    120308 16:08:20 [Note]   - 'mysql_addr' resolves to '10.178.58.216';
    120308 16:08:20 [Note]   - 'mysql_addr' resolves to 'fe80::120b:a9ff:fe69:59ec';
    120308 16:08:20 [Note] Server socket created on IP: '10.178.58.216'.

More than one entry for ipv4 and ipv6 address:
  => Following message logged
    120308 16:06:19 [Note] Server hostname (bind-address): 'localhost'; port: 3306
    120308 16:06:19 [Note]   - 'localhost' resolves to '::1';
    120308 16:06:19 [Note]   - 'localhost' resolves to '::1';
    120308 16:06:19 [Note]   - 'localhost' resolves to '127.0.0.1';
    120308 16:06:19 [Note]   - 'localhost' resolves to '127.0.0.1';
    120308 16:06:19 [Note] Server socket created on IP: '127.0.0.1'.

rev_id: inaam.rana@oracle.com-20120326134501-23554a8vhjg0wqbm

      
 Description: When the table has more than one unique or primary key, 
 INSERT... ON DUP KEY UPDATE statement is sensitive to the order in which
 the storage engines checks the keys. Depending on this order, the storage
 engine may determine different rows to mysql, and hence mysql can update
 different rows on master and slave.
      
 Solution: We mark INSERT...ON DUP KEY UPDATE on a table with more than on unique
 key as unsafe therefore the event will be logged in row format if it is available
 (ROW/MIXED). If only STATEMENT format is available, a warning will be thrown. 

mysql-test/suite/binlog/r/binlog_unsafe.result:
  Updated result file
mysql-test/suite/binlog/t/binlog_unsafe.test:
  Added test to check  for warning being thrown when the unsafe statement is executed
mysql-test/suite/rpl/r/rpl_known_bugs_detection.result:
  Updated result file
sql/share/errmsg-utf8.txt:
  Added new warning message
sql/sql_base.cc:
  check for tables in the query with more than one UNIQUE KEY and INSERT ON DUPLICATE KEY UPDATE, and mark such statements unsafe.

Background:

  - as described in MySQL Internals Prepared Stored
    (http://forge.mysql.com/wiki/MySQL_Internals_Prepared_Stored),
    the Optimizer sometimes does destructive changes to the parsed
    LEX-object (Item-tree), which makes it impossible to re-use
    that tree for PS/SP re-execution.

  - in order to be able to re-use the Item-tree, the destructive
    changes are remembered and rolled back after the statement execution.

The problem, discovered by this bug, was that the objects representing
GROUP-BY clause did not restored after query execution. So, the GROUP-BY
part of the statement could not be properly re-initialized for re-execution
after destructive changes.

Those objects do not take part in the Item-tree, so they can not be saved
using the approach for Item-tree.

The fix is as follows:

  - introduce a new array in st_select_lex to store the original
    ORDER pointers, representing the GROUP-BY clause;

  - Initialize this array in fix_prepare_information().

  - restore the list of GROUP-BY items in reinit_stmt_before_use().

        
Fix the calculation of the next autoinc value when offset > 1. Some of the
results have changed due to the changes in the allocation calculation. The
new calculation will result in slightly bigger gaps for bulk inserts.
  
rb://866 Approved by Jimmy Yang.
Backported from mysql-trunk (5.6)

LOCK_THREAD_COUNT

When using the performance schema file io instrumentation in MySQL 5.5,
a thread would loop forever inside lf_pinbox_put_pins, when disconnecting.
It would also hold LOCK_thread_count while doing so, effectively killing the
server.

The root cause of the loop in lf_pinbox_put_pins() is a leak of LF_PINS,
when used with the filename_hash LF_HASH table in the performance schema.

This fix contains the following changes:

1)
Added the missing call to lf_hash_search_unpin(), to prevent the leak.

2)
In mysys/lf_alloc-pin.c, there was some extra debugging code
(MY_LF_EXTRA_DEBUG) written to detect precisely this kind of issues,
but it was never used.
Replaced MY_LF_EXTRA_DEBUG with DBUG_OFF, so that leaks similar to this one
can be always detected in regular debug builds.

3)
Backported the fix for the following bug, from 5.6 to 5.5:
Bug#13417446 - 63339: INCORRECT FILE PATH IN PEFORMANCE_SCHEMA ON WINDOWS

Analysis:
-------------------------------
According to the Manual
(http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html):
"Column, index, stored routine, and event names are not case sensitive on any
platform, nor are column aliases."

In other words, 'lower_case_table_names' does not affect the behaviour of 
those identifiers.

On the other hand, trigger names are case sensitive on some platforms,
and case insensitive on others. 'lower_case_table_names' does not affect
the behaviour of trigger names either.

The bug was that SHOW statements did case sensitive comparison
for stored procedure / stored function / event names.

Fix:
Modified the code so that comparison in case insensitive for routines 
and events for "SHOW" operation.

As part of this commit, only fixing the test failures due to the actual code fix.

Change the type of purge_sys_t::n_pages_handled and purge_sys_t::handle_limit
to ulonglong from ulint. On a 32 bit system doing ~700 deletes per second the
counters can overflow in ~3.5 months, if they are 32 bit.

Approved by Jimmy Yang over IM.

Analysis:
-------------------------------
According to the Manual
(http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html):
"Column, index, stored routine, and event names are not case sensitive on any
platform, nor are column aliases."

In other words, 'lower_case_table_names' does not affect the behaviour of 
those identifiers.

On the other hand, trigger names are case sensitive on some platforms,
and case insensitive on others. 'lower_case_table_names' does not affect
the behaviour of trigger names either.

The bug was that SHOW statements did case sensitive comparison
for stored procedure / stored function / event names.

Fix:
Modified the code so that comparison in case insensitive for routines 
and events for "SHOW" operation.

rb://942
approved by: Marko Makela

We don't need to scan LRU for dropping AHI entries when DROPing a table.
AHI entries are already removed when we free up extents for the btree.

Manually resolved conflicts in ".bzr-mysql/default.conf" (tree name)
and in "storage/innodb_plugin" (does not belong into 5.5).

solve a conflict in ".bzr-mysql/default.conf".

to mysql-5.5

rb://979 approved by Marko

conflict in "sql/filesort.cc" solved manually.

Problem:

Field_set::val_str in case of an empty SET value
returned a String with str_length==0 and Ptr==0,
which is not expected by some pieces of the code.

Fix:
      
Returning an empty string with str_length==0 and Ptr=="",
like Field_enum does.

  Doing this for Sayantan whose VPN is not working ATM

The test case must insert all the records using a single transaction. Otherwise the test 
case takes more than 15 minutes and will time out in pb2 and mtr.  

FROM BUFFER POOL

rb://975
approved by: Marko Makela

There is a race in lock_validate() where we try to access a page
without ensuring that the tablespace stays valid during the operation
i.e.: it is not deleted. This patch tries to fix that by using an
existing flag (the flag is renamed to make it's name more generic
in line with it's new use).