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.

SYNTAX TRIGGERS IN ANY WAY

Table with triggers which were using deprecated (5.0-only) syntax became
unavailable for any DML and DDL after upgrade to 5.1 version of server.
Attempt to execute any statement on such a table resulted in parsing
error reported. Since this included DROP TRIGGER and DROP TABLE
statements (actually, the latter was allowed but was not functioning
properly for such tables) it was impossible to fix the problem without
manual operations on .TRG and .TRN files in data directory.

The problem was that failure to parse trigger body (due to 5.0-only
syntax) when opening trigger file for a table prevented the table
from being open. This made all operations on the table impossible
(except DROP TABLE which due to peculiarity in its implementation
dropped the table but left trigger files around).

This patch solves this problem by silencing error which occurs when
we parse trigger body during table open. Error message is preserved
for the future use and table is marked as having a broken trigger.
We also try to analyze parse tree to recover trigger name, which
will be needed in order to drop the broken trigger. DML statements
which invoke triggers on the table marked as having broken trigger
are prohibited and emit saved error message. The same happens for
DDL which change triggers except DROP TRIGGER and DROP TABLE which
try their best to do what was requested. Table becomes no longer
marked as having broken trigger when last such trigger is dropped.

mysql-test/r/trigger-compat.result:
  Add results for test case for bug#45235
mysql-test/t/trigger-compat.test:
  Add test case for bug#45235.
sql/sp_head.cc:
  Added protection against MEM_ROOT double restoring to
  sp_head::restore_thd_mem_root() method. Since this
  method can be sometimes called twice during parsing
  of stored routine (the first time during normal flow
  of parsing, and the second time when a syntax error
  is detected) we need to shortcut execution of the
  method to avoid damaging MEM_ROOT by the second
  consecutive call to this method.
sql/sql_trigger.cc:
  Added error handler Deprecated_trigger_syntax_handler to 
  catch non-OOM errors during parsing of trigger body.
  
  Added handling of parse errors into method 
  Table_triggers_list::check_n_load().
sql/sql_trigger.h:
  Added new members to handle broken triggers and error messages.

row_build(): The record may contain null BLOB pointers when the server
is rolling back an insert that was interrupted by a server crash.

THE EVENT STATUS.

Any ALTER EVENT statement on a disabled event enabled it back
(unless this ALTER EVENT statement explicitly disabled the event).

The problem was that during processing of an ALTER EVENT statement
value of status field was overwritten unconditionally even if new
value was not specified explicitly. As a consequence this field
was set to default value for status which corresponds to ENABLE.

The solution is to check if status field was explicitly specified in
ALTER EVENT statement before assigning new value to status field.

mysql-test/r/events_bugs.result:
  test's result for Bug#11764334 was added.
mysql-test/t/events_bugs.test:
  new test for Bug#11764334 was added.
sql/event_db_repository.cc:
  mysql_event_fill_row() was modified: set value for status field
  in events tables only in case if statement CREATE EVENT
  is being processed or if this value was set in ALTER EVENT
  statement.
  Event_db_repository::create_event was modified: removed redundant
  setting of status field after return from call to mysql_event_fill_row().
sql/event_parse_data.h:
  Event_parse_data structure was modified: added flag
  status_changed that is set to true if status's value
  was changed in ALTER EVENT statement.
sql/sql_yacc.yy:
  Set flag status_changed if status was set in ALTER EVENT
  statement.

STATEMENTS FAIL".

Attempt to execute CREATE TABLE LIKE statement on a MyISAM
table with INDEX or DATA DIRECTORY options specified as a
source resulted in "MyISAM table '...' is in use..." error.
According to our documentation such a statement should create
a copy of source table with DATA/INDEX DIRECTORY options
omitted.

The problem was that new implementation of CREATE TABLE LIKE
statement in 5.5 tried to copy value of INDEX and DATA DIRECTORY
parameters from the source table. Since in description of source
table this parameters also included name of this table, attempt
to create target table with these parameter led to file name
conflict and error.

This fix addresses the problem by preserving documented and
backward-compatible behavior. I.e. by ensuring that contents
of DATA/INDEX DIRECTORY clauses for the source table is
ignored when target table is created.

mysql-test/r/symlink.result:
  Added test for bug #11759990 - "52354: 'CREATE TABLE ..
  LIKE ... ' STATEMENTS FAIL".
mysql-test/t/symlink.test:
  Added test for bug #11759990 - "52354: 'CREATE TABLE ..
  LIKE ... ' STATEMENTS FAIL".
sql/sql_table.cc:
  Changed CREATE TABLE LIKE implementation to ignore contents
  of DATA/INDEX DIRECTORY clauses for source table when target
  table is created. This is documented and backward-compatible
  behavior.

If UNIV_DEBUG or UNIV_BLOB_LIGHT_DEBUG is enabled, add
!rec_offs_any_null_extern() assertions, ensuring that records do not
contain null pointers to externally stored columns in inappropriate
places.

btr_cur_optimistic_update(): Assert !rec_offs_any_null_extern().
Incomplete records must never be updated or deleted. This assertion
will cover also the pessimistic route.

row_build(): Assert !rec_offs_any_null_extern(). Search tuples must
never be built from incomplete index entries.

row_rec_to_index_entry(): Assert !rec_offs_any_null_extern() unless
ROW_COPY_DATA is requested. ROW_COPY_DATA is used for
multi-versioning, and therefore it might be valid to copy the most
recent (uncommitted) version while it contains a null pointer to
off-page columns.

row_vers_build_for_consistent_read(),
row_vers_build_for_semi_consistent_read(): Assert !rec_offs_any_null_extern()
on all versions except the most recent one.

trx_undo_prev_version_build(): Assert !rec_offs_any_null_extern() on
the previous version.

rb:682 approved by Sunny Bains

FUNCTION DOES NOT EXIST IF NOT-PRIV USER RECONNECTS".

The bug itself was fixed by the same patch as bug@11747137
"30977: CONCURRENT STATEMENT USING STORED FUNCTION AND DROP
FUNCTION BREAKS SBR".

Problem: in case of wrong data insert into indexed GEOMETRY fields 
(e.g. NULL value for a not NULL field) MyISAM reported 
"ERROR 126 (HY000): Incorrect key file for table; try to repair it"
due to misuse of the key deletion function.

Fix: always use R-tree key functions for R-tree based indexes
and B-tree key functions for B-tree based indexes.


mysql-test/r/gis-rtree.result:
  Bug#11764487: myisam corruption with insert ignore and invalid spatial data
    - test result.
mysql-test/t/gis-rtree.test:
  Bug#11764487: myisam corruption with insert ignore and invalid spatial data
    - test case.
storage/myisam/mi_update.c:
  Bug#11764487: myisam corruption with insert ignore and invalid spatial data
    - handling update errors check for HA_ERR_NULL_IN_SPATIAL as well to be 
  consistent with mi_write();
    - always use keyinfo->ck_delete()/ck_insert() instead of _mi_ck_delete()/_mi_ck_write()
  to handle index properly, as it may be of B-tree or R-tree type.
storage/myisam/mi_write.c:
  Bug#11764487: myisam corruption with insert ignore and invalid spatial data
    - always use keyinfo->ck_delete() instead of _mi_ck_delete() to handle
  index properly, as it may be of B-tree or R-tree type.