Backporting BUG#43789 to mysql-5.1-bugteam

Post-fix for BUG#43789.

      
Backporting BUG#43789 to mysql-5.1-bugteam
                              
The replication was generating corrupted data, warning messages on Valgrind
and aborting on debug mode while replicating a "null" to "not null" field.
Specifically the unpack_row routine, was considering the slave's table
definition and trying to retrieve a field value, where there was nothing to be
retrieved, ignoring the fact that the value was defined as "null" by the master.
                              
To fix the problem, we proceed as follows:
                              
1 - If it is not STRICT sql_mode, implicit default values are used, regardless
if it is multi-row or single-row statement.
                              
2 - However, if it is STRICT mode, then a we do what follows:
                              
2.1 If it is a transactional engine, we do a rollback on the first NULL that is
to be set into a NOT NULL column and return an error.
                              
2.2 If it is a non-transactional engine and it is the first row to be inserted
with multi-row, we also return the error. Otherwise, we proceed with the
execution, use implicit default values and print out warning messages.
                        
Unfortunately, the current patch cannot mimic the behavior showed by the master
for updates on multi-tables and multi-row inserts. This happens because such
statements are unfolded in different row events. For instance, considering the
following updates and strict mode:
                        
(master)
create table t1 (a int);
create table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
update t1, t2 SET t1.a=10, t2.a=NULL;
                        
t1 would have (10) and t2 would have (0) as this would be handled as a
multi-row update. On the other hand, if we had the following updates:
                        
(master)
create table t1 (a int);
create table t2 (a int);
                        
(slave)
create table t1 (a int);
create table t2 (a int not null);
                        
(master)
insert into t1 values (1);
insert into t2 values (2);
update t1, t2 SET t1.a=10, t2.a=NULL;
                        
On the master t1 would have (10) and t2 would have (NULL). On
the slave, t1 would have (10) but the update on t1 would fail.

Backporting BUG#38173 to mysql-5.1-bugteam

The reason of  the bug was incompatibile with the master side behaviour.
INSERT query on the master is allowed to insert into a table without specifying
values of DEFAULT-less fields if sql_mode is not strict.
                  
Fixed with checking sql_mode by the sql thread to decide how to react.
Non-strict sql_mode should allow Write_rows event to complete.
                  
todo: warnings can be shown via show slave status, still this is a 
separate rather general issue how to show warnings for the slave threads.

- Initialized caseinfo only if it is NULL

                     grants are reapplied.


After renaming a user and trying to re-apply grants results in additional
grants.

This is because we use username as part of the key for GRANT_TABLE structure.
When the user is renamed, we only change the username stored and the hash key
still contains the old user name and this results in the extra privileges

Fixed by rebuilding the hash key and updating the column_priv_hash structure
when the user is renamed

mysql-test/r/grant3.result:
  Bug #41597 - After rename of user, there are additional grants when 
               grants are reapplied.
  
  Testcase for BUG#41597
mysql-test/t/grant3.test:
  Bug #41597 - After rename of user, there are additional grants when 
               grants are reapplied.
  
  Testcase for BUG#41597
sql/sql_acl.cc:
  Bug #41597 - After rename of user, there are additional grants when 
               grants are reapplied.
  
  Fixed handle_grant_struct() to update the hash key when the user is renamed.
  Added to set_user_details() method to GRANT_NAME class

There are some problems about help text:
- It is stated that "auto" is the default twice. It need be stated only once.
- It is stated that --base64-output is short for --base64-output=always. But that sounds
like the default is "always", not "auto".

Make the help text clear as following:
Determine when the output statements should be base64-encoded BINLOG 
statements: 'never' disables it and works only for binlogs without 
row-based events; 'auto' prints base64 only when necessary (i.e., 
for row-based events and format description events); 'always' prints 
base64 whenever possible. 'always' is for debugging only and should 
not be used in a production system. If this argument is not given, 
the default is 'auto'; if it is given with no argument, 'always' is used.

If a thread is killed in the server, we throw "shutdown" only if one is actually in
progress; otherwise, we throw "query interrupted".

Control-C in the mysql command-line client is "incremental" now.
First Control-C sends KILL QUERY (when connected to 5.0+ server, otherwise, see next)
Next  Control-C sends KILL CONNECTION
Next  Control-C aborts client.

As the first two steps only pertain to an existing query,
Control-C will abort the client right away if no query is running.

client will give more detailed/consistent feedback on Control-C now.


client/mysql.cc:
  Extends Control-C handling; enhances up feedback to user.
  
  On 5.0+ servers, we try to be nice and send KILL QUERY first
  if Control-C is pressed in the command-line client, but if
  that doesn't work, we now give the user the opportunity to
  send KILL CONNECTION with another Control-C (and to kill the
  client with another Control-C if that somehow doesn't work
  either).
mysql-test/t/flush_read_lock_kill.test:
  we're getting correct "thread killed" rather than
  "in shutdown" error now
mysql-test/t/kill.test:
  we're getting correct "thread killed" rather than
  "in shutdown" error now
mysql-test/t/rpl000001.test:
  we're getting correct "thread killed" rather than
  "in shutdown" error now
mysql-test/t/rpl_error_ignored_table.test:
  we're getting correct "thread killed" rather than
  "in shutdown" error now
sql/records.cc:
  make error messages on KILL uniform for rr_*()
  by folding that handling into rr_handle_error()
sql/sql_class.h:
  Only throw "shutdown" when we have one flagged as being in progress;
  otherwise, throw "query interrupted" as it's likely to be "KILL CONNECTION"
  or related.

Problem: the "caseinfo" member of CHARSET_INFO structure was not
initialized for user-defined Unicode collations, which made the
server crash.
Fix: initializing caseinfo properly.

Adding tests for the bug.

A contributed patch by Andrei Boros (SCA signed).
- Fixing locale definition file
- Adding tests

Adding @@session and @@global prefixes to a
declared variable in a stored procedure the server
would lead to a crash.

The reason was that during the parsing of the
syntactic rule 'option_value' an uninitialized
set_var object was pushed to the parameter stack
of the SET statement. The parent rule
'option_type_value'  interpreted the existence of
variables on the parameter stack as an assignment
and wrapped it in a sp_instr_set object.

As the procedure later was executed an attempt
was made to run the method 'check()' on an
uninitialized member object (NULL value) belonging
to the previously created but uninitialized object.


sql/sql_yacc.yy:
  * Assign the option_type at once since it is needed by the next
    parsing rule (internal_variable_name)
  * Rearranged the if statement to reduce negations and gain more
    clarity of code.
  * Added check for option_type to better detect if current
    variable is a SP local variable or a system variable.

Problem: using null microsecond part in a WHERE condition 
(e.g. WHERE date_time_field <= "YYYY-MM-DD HH:MM:SS.0000") 
may lead to wrong results due to improper DATETIMEs 
comparison in some cases.

Fix: comparing DATETIMEs as strings we must trim trailing 0's
in such cases.


mysql-test/r/innodb_mysql.result:
  Fix for bug#47963: Wrong results when index is used
    - test result.
mysql-test/t/innodb_mysql.test:
  Fix for bug#47963: Wrong results when index is used
    - test case.
sql/item.cc:
  Fix for bug#47963: Wrong results when index is used
    - comparing DATETIMEs as strings we must trim trailing 0's in the 
  microsecond part to ensure
  'YYYY-MM-DD HH:MM:SS.000' == 'YYYY-MM-DD HH:MM:SS'

 
The problem was in incorrect handling of predicates involving 
NULL as a constant value by the range optimizer. 
 
For example, when creating a SEL_ARG node from a condition of 
the form "field < const" (which would normally result in the 
"NULL < field < const" SEL_ARG),  the special case when "const" 
is NULL was not taken into account, so "NULL < field < NULL" 
was produced for the "field < NULL" condition. 
 
As a result, SEL_ARG structures of this form could not be 
further optimized which in turn could lead to incorrectly 
constructed SEL_ARG trees. In particular, code assuming SEL_ARG 
structures to always form a sequence of ordered disjoint 
intervals could enter an infinite loop under some 
circumstances. 
 
Fixed by changing get_mm_leaf() so that for any sargable 
predicate except "<=>" involving NULL as a constant, "empty" 
SEL_ARG is returned, since such a predicate is always false. 

mysql-test/r/partition_pruning.result:
  Fixed a broken test case.
mysql-test/r/range.result:
  Added a test case for bug #47123.
mysql-test/r/subselect.result:
  Fixed a broken test cases.
mysql-test/t/range.test:
  Added a test case for bug #47123.
sql/opt_range.cc:
  Fixed get_mm_leaf() so that for any sargable
  predicate except "<=>" involving NULL as a constant, "empty"
  SEL_ARG is returned, since such a predicate is always false.

Use "#ifdef", not plain "#if".

            view that has Group By
      
When SELECT'ing from a view that mentions another,
materialized, view, access was being denied. The issue was
resolved by lifting a special case which avoided such access
checking in check_single_table_access. In the past, this was
necessary since if such a check were performed, the error
message would be downgraded to a warning in the case of SHOW
CREATE VIEW. The downgrading of errors was meant to handle
only that scenario, but could not distinguish the two as it
read only the error messages.
      
The special case was needed in the fix of bug no 36086.
Before that, views were confused with derived tables.
      
After bug no 35996 was fixed, the manipulation of errors
during SHOW CREATE VIEW execution is not dependent on the
actual error messages in the queue, it rather looks at the
actual cause of the error and takes appropriate
action. Hence the aforementioned special case is now
superfluous and the bug is fixed.


mysql-test/r/view_grant.result:
  Bug#46019: Test result.
mysql-test/t/view_grant.test:
  Bug#46019: Test case.
sql/sql_parse.cc:
  Bug#46019: fix.

      
Implemented the server infrastructure for the fix:

1. Added a function LEX_STRING *thd_query_string(THD) to return
a LEX_STRING structure instead of char *.
This is the function that must be called in innodb instead of 
thd_query()

2. Did some encapsulation in THD : aggregated thd_query and 
thd_query_length into a LEX_STRING and made accessor and mutator 
methods for easy code updating. 

3. Updated the server code to use the new methods where applicable.