GitLab

Bug 18914 (Calling certain SPs from triggers fail)
Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
Bug 21825 (Incorrect message error deleting records in a table with a
  trigger for inserting)
Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
  error)
Bug 25345 (Cursors from Functions)


This fix resolves a long standing issue originally reported with bug 8407,
which affect the behavior of Stored Procedures, Stored Functions and Trigger
in many different ways, causing symptoms reported by all the bugs listed.
In all cases, the root cause of the problem traces back to 8407 and how the
server locks tables involved with sub statements.

Prior to this fix, the implementation of stored routines would:
- compute the transitive closure of all the tables referenced by a top level
statement
- open and lock all the tables involved
- execute the top level statement
"transitive closure of tables" means collecting:
- all the tables,
- all the stored functions,
- all the views,
- all the table triggers
- all the stored procedures
involved, and recursively inspect these objects definition to find more
references to more objects, until the list of every object referenced does
not grow any more.
This mechanism is known as "pre-locking" tables before execution.
The motivation for locking all the tables (possibly) used at once is to
prevent dead locks.

One problem with this approach is that, if the execution path the code
really takes during runtime does not use a given table, and if the table is
missing, the server would not execute the statement.
This in particular has a major impact on triggers, since a missing table
referenced by an update/delete trigger would prevent an insert trigger to run.

Another problem is that stored routines might define SQL exception handlers
to deal with missing tables, but the server implementation would never give
user code a chance to execute this logic, since the routine is never
executed when a missing table cause the pre-locking code to fail.

With this fix, the internal implementation of the pre-locking code has been
relaxed of some constraints, so that failure to open a table does not
necessarily prevent execution of a stored routine.

In particular, the pre-locking mechanism is now behaving as follows:

1) the first step, to compute the transitive closure of all the tables
possibly referenced by a statement, is unchanged.

2) the next step, which is to open all the tables involved, only attempts
to open the tables added by the pre-locking code, but silently fails without
reporting any error or invoking any exception handler is the table is not
present. This is achieved by trapping internal errors with
Prelock_error_handler

3) the locking step only locks tables that were successfully opened.

4) when executing sub statements, the list of tables used by each statements
is evaluated as before. The tables needed by the sub statement are expected
to be already opened and locked. Statement referencing tables that were not
opened in step 2) will fail to find the table in the open list, and only at
this point will execution of the user code fail.

5) when a runtime exception is raised at 4), the instruction continuation
destination (the next instruction to execute in case of SQL continue
handlers) is evaluated.
This is achieved with sp_instr::exec_open_and_lock_tables()

6) if a user exception handler is present in the stored routine, that
handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
trapped by stored routines. If no handler exists, then the runtime execution
will fail as expected.

With all these changes, a side effect is that view security is impacted, in
two different ways.

First, a view defined as "select stored_function()", where the stored
function references a table that may not exist, is considered valid.
The rationale is that, because the stored function might trap exceptions
during execution and still return a valid result, there is no way to decide
when the view is created if a missing table really cause the view to be invalid.

Secondly, testing for existence of tables is now done later during
execution. View security, which consist of trapping errors and return a
generic ER_VIEW_INVALID (to prevent disclosing information) was only
implemented at very specific phases covering *opening* tables, but not
covering the runtime execution. Because of this existing limitation,
errors that were previously trapped and converted into ER_VIEW_INVALID are
not trapped, causing table names to be reported to the user.
This change is exposing an existing problem, which is independent and will
be resolved separately.


mysql-test/r/information_schema_db.result:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/r/sp-error.result:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/r/sp.result:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/r/trigger.result:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/r/view.result:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/t/sp-error.test:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/t/sp.test:
  Revised the pre-locking code implementation, aligned the tests.
mysql-test/t/trigger.test:
  Revised the pre-locking code implementation, aligned the tests.
sql/lock.cc:
  table->placeholder now checks for schema_table
sql/mysqld.cc:
  my_message_sql(): invoke internal exception handlers
sql/sp_head.cc:
  exec_open_and_lock_tables(): open and lock tables, or return the
  continuation destination of this instruction
sql/sp_head.h:
  exec_open_and_lock_tables(): open and lock tables, or return the
  continuation destination of this instruction
sql/sql_base.cc:
  Prelock_error_handler: delay open table errors until execution
sql/sql_class.cc:
  THD: add internal error handler, as an exception mechanism.
sql/sql_class.h:
  THD: add internal error handler, as an exception mechanism.
sql/sql_update.cc:
  table->placeholder now checks for schema_table
sql/table.cc:
  st_table_list::hide_view_error(): masked more errors for view security
sql/table.h:
  table->placeholder now checks for schema_table, and unopened tables

mysql-test/extra/rpl_tests/rpl_trig004.test:
  Fixed bug in last push
mysql-test/r/create.result:
  Fixed error number
mysql-test/r/rpl_sp.result:
  Fixed error number
mysql-test/r/sp.result:
  Fixed error number
mysql-test/r/view.result:
  Better fix for now() handling
mysql-test/t/ndb_basic.test:
  Use error names instead of numbers
mysql-test/t/ndb_index_unique.test:
  Use error names instead of numbers
mysql-test/t/rpl_skip_error-slave.opt:
  Change to new error number
mysql-test/t/sp.test:
  Change to new error number
mysql-test/t/view.test:
  Better fix for now() handling

mysql-test/r/create.result:
  Fixed error number
mysql-test/r/rpl_sp.result:
  Fixed error number
mysql-test/r/sp.result:
  Fixed error number
mysql-test/r/view.result:
  Better fix for now() handling
mysql-test/t/ndb_basic.test:
  Use error names instead of numbers
mysql-test/t/ndb_index_unique.test:
  Use error names instead of numbers
mysql-test/t/rpl_skip_error-slave.opt:
  Change to new error number
mysql-test/t/sp.test:
  Change to new error number
mysql-test/t/view.test:
  Better fix for now() handling

Changed error message to be compatible with old error file
Added new error message for new DUP_ENTRY syntax


BUILD/SETUP.sh:
  Give warnings for unused objects
mysql-test/extra/binlog_tests/insert_select-binlog.test:
  Changed to use new error message
mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_auto_increment.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_foreign_key.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_insert_id.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_insert_id_pk.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_loaddata.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_row_basic.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_stm_EE_err2.test:
  Changed to use new error message
mysql-test/extra/rpl_tests/rpl_trig004.test:
  Changed to use new error message
mysql-test/include/mix1.inc:
  Changed to use new error message
mysql-test/include/mix2.inc:
  Changed to use new error message
mysql-test/include/ps_modify.inc:
  Changed to use new error message
mysql-test/include/query_cache.inc:
  Changed to use new error message
mysql-test/include/varchar.inc:
  Changed to use new error message
mysql-test/r/create.result:
  Changed to use new error message
mysql-test/r/rpl_sp.result:
  Changed to use new error message
mysql-test/r/sp.result:
  Changed to use new error message
mysql-test/r/view.result:
  Changed to use new error message
mysql-test/t/auto_increment.test:
  Changed to use new error message
mysql-test/t/create.test:
  Changed to use new error message
mysql-test/t/create_select_tmp.test:
  Changed to use new error message
mysql-test/t/ctype_utf8.test:
  Changed to use new error message
mysql-test/t/delayed.test:
  Changed to use new error message
mysql-test/t/heap.test:
  Changed to use new error message
mysql-test/t/heap_btree.test:
  Changed to use new error message
mysql-test/t/heap_hash.test:
  Changed to use new error message
mysql-test/t/innodb.test:
  Changed to use new error message
mysql-test/t/insert_select.test:
  Changed to use new error message
mysql-test/t/insert_update.test:
  Changed to use new error message
mysql-test/t/join_outer.test:
  Changed to use new error message
mysql-test/t/key.test:
  Changed to use new error message
mysql-test/t/merge.test:
  Changed to use new error message
mysql-test/t/myisam.test:
  Changed to use new error message
mysql-test/t/ndb_charset.test:
  Changed to use new error message
mysql-test/t/ndb_index_unique.test:
  Changed to use new error message
mysql-test/t/ndb_insert.test:
  Changed to use new error message
mysql-test/t/ndb_replace.test:
  Changed to use new error message
mysql-test/t/ndb_update.test:
  Changed to use new error message
mysql-test/t/replace.test:
  Changed to use new error message
mysql-test/t/rpl_err_ignoredtable.test:
  Changed to use new error message
mysql-test/t/rpl_row_create_table.test:
  Changed to use new error message
mysql-test/t/rpl_skip_error-slave.opt:
  Changed to use new error message
mysql-test/t/rpl_sp.test:
  Changed to use new error message
mysql-test/t/show_check.test:
  Changed to use new error message
mysql-test/t/sp-error.test:
  Changed to use new error message
mysql-test/t/sp.test:
  Changed to use new error message
mysql-test/t/sp_trans.test:
  Changed to use new error message
mysql-test/t/temp_table.test:
  Changed to use new error message
mysql-test/t/type_binary.test:
  Changed to use new error message
mysql-test/t/type_bit.test:
  Changed to use new error message
mysql-test/t/type_bit_innodb.test:
  Changed to use new error message
mysql-test/t/type_blob.test:
  Changed to use new error message
mysql-test/t/type_varchar.test:
  Changed to use new error message
mysql-test/t/view.test:
  Changed to use new error message
sql/handler.cc:
  ER_DUP_ENTRY -> ER_DUP_ENTRY_WITH_KEY_NAME
sql/share/errmsg.txt:
  Changed error message to be compatible with old error file
  Added new error message for new DUP_ENTRY syntax
sql/sql_table.cc:
  ER_DUP_ENTRY -> ER_DUP_ENTRY_WITH_KEY_NAME
sql-bench/example:
  Example file for how to run tests

Fix for bug #24117 "server crash on a FETCH with a cursor on a table which is not in the table cache"

Problem:
When creating a temporary field for a temporary table in create_tmp_field_from_field(), a resulting field is created as an exact copy of an original one (in Field::new_field()). However, Field_enum and Field_set contain a pointer (typelib) to memory allocated in the parent table's MEM_ROOT, which under some circumstances may be deallocated later by the time a temporary table is used.

Solution:
Override the new_field() method for Field_enum and Field_set and create a separate copy of the typelib structure in there.


include/typelib.h:
  Added copy_typelib() declaration
mysql-test/r/sp.result:
  Added a testcase for bug #24117 "server crash on a FETCH with a cursor on a table which is not in the table cache"
mysql-test/t/sp.test:
  Added a testcase for bug #24117 "server crash on a FETCH with a cursor on a table which is not in the table cache"
mysys/typelib.c:
  Added copy_typelib() definition
sql/field.cc:
  Create a copy of the internal 'typelib' structure when copying Field_enum of Field_set objects.
sql/field.h:
  Override new_field method in Field_enum (and Field_set) to copy the typelib structure.

on altered table" and Bug#19733 "Repeated alter, or repeated 
create/drop, fails"


mysql-test/r/ps.result:
  Post-merge fixes: update results with new tests.
mysql-test/r/sp.result:
  Post-merge fixes: update results.
mysql-test/t/ps.test:
  Add more test cases for Bug#4968 and related.
mysql-test/t/sp.test:
  A post-merge fix: add more testcases for Bug#4968 and related.
sql/sql_insert.cc:
  Post-merge fixes: update comments, fix errors of the manual merge.
sql/sql_lex.cc:
  Fix a manual merge error.
sql/sql_parse.cc:
  Fix a few errors of the manual merge, style.
sql/sql_table.cc:
  Post-merge fixes, fix a few errors of the manual merge, fix style.
sql/sql_yacc.yy:
  A post-merge fix.

* New result files due to new error message/error numbers
* Fixed system_mysql_db tests to work with servers table
* Added UTF8 charset to table defs


mysql-test/include/system_db_struct.inc:
  WL# 3031
  
  Added servers table to inc file used in system_mysql_db* tests
mysql-test/lib/init_db.sql:
  WL# 3031
  
  Added UTF charset to table def
mysql-test/r/mysql.result:
  WL# 3031
  
  New result files, new error messages shifted error #s up by two
mysql-test/r/rpl_sp.result:
  WL# 3031
  
  New result files, new error messages shifted error #s up by two
mysql-test/r/sp.result:
  WL# 3031
  
  New result files, new error messages shifted error #s up by two
mysql-test/r/sp_gis.result:
  WL# 3031
  
  New result files, new error messages shifted error #s up by two
mysql-test/r/system_mysql_db.result:
  WL #3031
  
  New system_mysql_db tests required adding servers table creation. Some more
  comments in these tests would have been nice to explain what they do ;)
mysql-test/t/system_mysql_db_fix30020.test:
  WL #3031
  
  New system_mysql_db tests required adding servers table creation. Some more
  comments in these tests would have been nice to explain what they do ;)
mysql-test/t/system_mysql_db_fix40123.test:
  WL #3031
  
  New system_mysql_db tests required adding servers table creation. Some more
  comments in these tests would have been nice to explain what they do ;)
mysql-test/t/system_mysql_db_fix50030.test:
  WL #3031
  
  New system_mysql_db tests required adding servers table creation. Some more
  comments in these tests would have been nice to explain what they do ;)
scripts/mysql_create_system_tables.sh:
  WL# 3031
  
  Added utf8 charset to table def
scripts/mysql_fix_privilege_tables.sql:
  WL# 3031
  
  Added servers table to mysql_fix_privilege_tables

Post-commit issues fixed
* Test results for other tests fixed due to added error #s
* Memory allocation/free issues found with running with valgrind
* Fix to mysql-test-run shell script to run federated_server test (installs
mysql.servers table properly)


mysql-test/r/1st.result:
  WL #3031
  
  New result for 1st test
mysql-test/r/backup.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/connect.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/information_schema.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/mysql.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/mysqlcheck.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/ndb_dd_basic.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/ndb_dd_ddl.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/select.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/show_check.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/sp.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/sp_gis.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/sp_trans.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/system_mysql_db.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/type_timestamp.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/warnings.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
mysql-test/r/xml.result:
  WL #3031
  
  Error codes differ due to addition of error codes for federated server errors (2)
  so all test results with hard-coded error #s will be off by two. New results generated.
scripts/mysql_create_system_tables.sh:
  WL #3031
  
  Fixed old mysql-test-run.sh script to work with new mysql.servers table as reported
  by Cisco
sql/sql_yacc.yy:
  WL# 3031
  
  OPTIONS/options must be usable as table or column name.
storage/federated/ha_federated.cc:
  WL# 3031
  
  * Fixed allocation and free issues (warnings found with --valgrind)
  * Fixed cast issues
  * Made sure port was set accordingly

mysql-test/r/sp.result:
  offset error number according to changed error messages file
mysql-test/r/sp_gis.result:
  offset error number according to changed error messages file

Fix tests for new behaviour: an error is thrown if a NON DETERMINISTIC
stored function (SF) is called during statement-based replication (SBR).


mysql-test/r/func_time.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/gis.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/grant2.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/innodb_notembedded.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/ps.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/query_cache.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/query_cache_notembedded.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/rpl_sp.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Move test for SF-calls in different replication modes to its own file, rpl_sf.
mysql-test/r/rpl_sp_effects.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/r/sp.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/r/timezone2.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/func_time.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/gis.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/t/grant2.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/innodb_notembedded.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/ps.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/query_cache.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/query_cache_notembedded.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/rpl_sp.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/rpl_sp_effects.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
mysql-test/t/sp.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Mark function as DETERMINISTIC so it can be called with no error while
  doing statement-based replication (SBR).
mysql-test/t/timezone2.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Modify test that contains non-deterministic functions so it can still be
  called with no error while doing statement-based replication (SBR).
sql/item_func.cc:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  If 'log_bin_trust_function_creators' variable is set, don't throw an error
  on calling a non-deterministc function in statement-based replication (SBR).
sql/sql_parse.cc:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Do not throw an error on calling a NON DETERMINISTIC stored procedure (SP)
  while doing statement-based replication (SBR), as the routine body is
  executed statement-by-statement.
mysql-test/r/rpl_sf.result:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Test for stored function (SF) calls in different replication modes.
  NON DETERMINISTIC SFs are not allowed while doing
  statement-based replication (SBR).
mysql-test/t/rpl_sf.test:
  Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR
  
  Test for stored function (SF) calls in different replication modes.
  NON DETERMINISTIC SFs are not allowed while doing
  statement-based replication (SBR).

mysql-test/r/sp.result:
  update result

Bug#21025 (misleading error message when creating functions named 'x', or 'y')
Bug#22619 (Spaces considered harmful)

This change contains a fix to report warnings or errors, and multiple tests
cases.

Before this fix, name collisions between:
- Native functions
- User Defined Functions
- Stored Functions
were not systematically reported, leading to confusing behavior.

I) Native / User Defined Function

Before this fix, is was possible to create a UDF named "foo", with the same
name as a native function "foo", but it was impossible to invoke the UDF,
since the syntax "foo()" always refer to the native function.
After this fix, creating a UDF fails with an error if there is a name
collision with a native function.

II) Native / Stored Function

Before this fix, is was possible to create a SF named "db.foo", with the same
name as a native function "foo", but this was confusing since the syntax
"foo()" would refer to the native function. To refer to the Stored Function,
the user had to use the "db.foo()" syntax.
After this fix, creating a Stored Function reports a warning if there is a
name collision with a native function.

III) User Defined Function / Stored Function

Before this fix, creating a User Defined Function "foo" and a Stored Function
"db.foo" are mutually exclusive operations. Whenever the second function is
created, an error is reported. However, the test suite did not cover this
behavior.
After this fix, the  behavior is unchanged, and is now covered by test cases.

Note that the code change in this patch depends on the fix for Bug 21114.


mysql-test/r/sp.result:
  New test cases.
mysql-test/r/udf.result:
  New test cases.
mysql-test/t/sp.test:
  New test cases.
mysql-test/t/udf.test:
  New test cases.
sql/sql_lex.cc:
  Name collisions with native functions.
sql/sql_lex.h:
  Name collisions with native functions.
sql/sql_yacc.yy:
  Name collisions with native functions.
sql/share/errmsg.txt:
  Name collisions with native functions.
mysql-test/r/sp_gis.result:
  New test cases.
mysql-test/t/sp_gis.test:
  New test cases.

The problem was that THD::row_count_func was zeroed too. It was zeroed
as a fix for bug 4905 "Stored procedure doesn't clear for "Rows affected"
However, the proper solution is not to zero, because THD::row_count_func has
been set to -1 already in mysql_execute_command(), a later fix, which obsoletes
the incorrect fix of #4095


mysql-test/r/sp.result:
  update result
mysql-test/t/sp.test:
  test for bug#23760 ROW_COUNT() and store procedure not owrking together
sql/sql_parse.cc:
  Remove zeroing for thd->row_count_func
  The fix for #4905 wasn't right. Now, it's ok without this zeroing
  because if there was an error THD::

mysql-test/r/sp.result:
  Update results.
mysql-test/r/view.result:
  Update results.
server-tools/instance-manager/guardian.cc:
  Post-merge fixes (instance_name is a LEX_STRING).
server-tools/instance-manager/instance.cc:
  Post-merge fixes (instance_name is a LEX_STRING).

This patch reverts a change introduced by Bug 6951, which incorrectly
set thd->abort_on_warning for stored procedures.

As per internal discussions about the SQL_MODE=TRADITIONAL,
the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE
trigger.

Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE
have been included or revised, to reflect the intended behavior.

(reposting approved patch, to work around source control issues, no review needed)


mysql-test/include/sp-vars.inc:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/sp-vars.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/sp.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/trigger.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/sp-vars.test:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/sp.test:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/trigger.test:
  Tests for SQL_MODE='TRADITIONAL'
sql/sp_head.cc:
  For SQL_MODE='TRADITIONAL',
  thd->abort_on_warning should be set only when assigning a *column*

mysql-test/r/sp.result:
  fix merge issues
mysql-test/r/trigger.result:
  fix merge issues

This patch reverts a change introduced by Bug 6951, which incorrectly
set thd->abort_on_warning for stored procedures.

As per internal discussions about the SQL_MODE=TRADITIONAL,
the correct behavior is to *not* abort on warnings even inside an INSERT/UPDATE
trigger.

Tests for Stored Procedures, Stored Functions, Triggers involving SQL_MODE
have been included or revised, to reflect the intended behavior.


mysql-test/include/sp-vars.inc:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/sp-vars.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/sp.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/r/trigger.result:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/sp-vars.test:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/sp.test:
  Tests for SQL_MODE='TRADITIONAL'
mysql-test/t/trigger.test:
  Tests for SQL_MODE='TRADITIONAL'. Revised the expected behavior for triggers
sql/sp_head.cc:
  For SQL_MODE='TRADITIONAL',
  thd->abort_on_warning should be set only when assigning a *column*

The syntax of the CALL statement, to invoke a stored procedure, has been
changed to make the use of parenthesis optional in the argument list.
With this change, "CALL p;" is equivalent to "CALL p();".

While the SQL spec does not explicitely mandate this syntax, supporting it
is needed for practical reasons, for integration with JDBC / ODBC connectors.

Also, warnings in the sql/sql_yacc.yy file, which were not reported by Bison 2.1
but are now reported by Bison 2.2, have been fixed.

The warning found were:
bison -y -p MYSQL  -d --debug --verbose sql_yacc.yy
sql_yacc.yy:653.9-18: warning: symbol UNLOCK_SYM redeclared
sql_yacc.yy:656.9-17: warning: symbol UNTIL_SYM redeclared
sql_yacc.yy:658.9-18: warning: symbol UPDATE_SYM redeclared
sql_yacc.yy:5169.11-5174.11: warning: unused value: $2
sql_yacc.yy:5208.11-5220.11: warning: unused value: $5
sql_yacc.yy:5221.11-5234.11: warning: unused value: $5
conflicts: 249 shift/reduce

"unused value: $2" correspond to the $$=$1 assignment in the 1st {} block
in table_ref -> join_table {} {},
which does not procude a result ($$) for the rule but an intermediate $2
value for the action instead.
"unused value: $5" are similar, with $$ assignments in {} actions blocks
which are not for the final reduce.


mysql-test/r/sp.result:
  New test case for Bug#21462
mysql-test/t/sp.test:
  New test case for Bug#21462
sql/sql_yacc.yy:
  "CALL p;" syntax for calling a stored procedure
  Fixed bison 2.2 warnings.

mysql-test/r/sp.result:
  After merge fix.
mysql-test/t/sp.test:
  After merge fixes. Restored test for bug 21002 which has mysteriously
  disappeared from 5.1 tree.

  
There was possible stack overrun in an edge case which handles invalid body of
a SP in mysql.proc . That should be case when mysql.proc has been changed
manually. Though, due to bug 21513, it can be exploited without having access
to mysql.proc only being able to create a stored routine.


mysql-test/r/sp.result:
  update result
mysql-test/t/sp.test:
  add a test case for the bug
sql/sp.cc:
  Fix stack overrun. This happen mostly when mysql.proc is damaged, though
  it's possible due to another bug which creates invalid SP body in mysql.proc
  (leading quote from a label being cut) to create stack overrun even without
  having direct access to mysql.proc

containing a select statement that uses an aggregating IN subquery.
Added a parameter to the function fix_prepare_information 
to restore correctly the having clause for the second execution.
Saved andor structure of the having conditions at the proper moment
before any calls of split_sum_func2 that could modify the having structure
adding new Item_ref objects. (These additions, are produced not with 
the statement mem_root, but rather with the execution mem_root.)


mysql-test/r/sp.result:
  Added a test case for bug #21493.
mysql-test/t/sp.test:
  Added a test case for bug #21493.
sql/sql_delete.cc:
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.
sql/sql_insert.cc:
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.
sql/sql_lex.cc:
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.
sql/sql_lex.h:
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.
sql/sql_update.cc:
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.

The problem was that if after FLUSH TABLES WITH READ LOCK the user
issued DROP/ALTER PROCEDURE/FUNCTION the operation would fail (as
expected), but after UNLOCK TABLE any attempt to execute the same
operation would lead to the error 1305 "PROCEDURE/FUNCTION does not
exist", and an attempt to execute any stored function will also fail.

This happened because under FLUSH TABLES WITH READ LOCK we couldn't open
and lock mysql.proc table for update, and this fact was erroneously
remembered by setting mysql_proc_table_exists to false, so subsequent
statements believed that mysql.proc doesn't exist, and thus that there
are no functions and procedures in the database.

As a solution, we remove mysql_proc_table_exists flag completely.  The
reason is that this optimization didn't work most of the time anyway.
Even if open of mysql.proc failed for some reason when we were trying to
call a function or a procedure, we were setting mysql_proc_table_exists
back to true to force table reopen for the sake of producing the same
error message (the open can fail for number of reasons).  The solution
could have been to remember the reason why open failed, but that's a lot
of code for optimization of a rare case.  Hence we simply remove this
optimization.


mysql-test/r/sp.result:
  Add result for bug#21414: SP: Procedure undroppable, to some extent.
mysql-test/t/sp.test:
  Remove no longer relevant comment.
  Add test case for bug#21414: SP: Procedure undroppable, to some extent.
sql/mysql_priv.h:
  Remove declaration of mysql_proc_table_exists.
sql/sp.cc:
  Remove references to mysql_proc_table_exists.
sql/sql_acl.cc:
  Remove reference to mysql_proc_table_exists.

mysql-test/lib/mtr_process.pl:
  Apply manually the merge from 5.0
mysql-test/mysql-test-run.pl:
  Apply manually the merge from 5.0
mysql-test/r/grant.result:
  A post-merge fix.
mysql-test/r/im_cmd_line.result:
  A post-merge fix.
mysql-test/r/im_instance_conf.result:
  A post-merge fix.
mysql-test/r/sp.result:
  A post-merge fix.
mysql-test/t/grant.test:
  A post-merge fix.
mysql-test/t/im_cmd_line.imtest:
  A post-merge fix.
mysql-test/t/im_instance_conf.imtest:
  A post-merge fix.
sql/field.cc:
  A post-merge fix.
sql/item_cmpfunc.cc:
  A post-merge fix.
sql/sp_head.cc:
  A post-merge fix.
sql/sp_head.h:
  A post-merge fix.

The following procedure was not possible if max_sp_recursion_depth is 0
create procedure show_proc() show create procedure show_proc;
  
Actually there is no recursive call but the limit is checked.
  
Solved by temporarily increasing the thread's limit just before the fetch from cache
and decreasing after that.


mysql-test/r/sp.result:
  update result
mysql-test/t/sp.test:
  Test for bug #21416 SP: Recursion level higher than zero needed for non-recursive call
sql/sp.cc:
  Increase the max_sp_recursion_depth temporarily for SHOW CREATE PROCEDURE call.
  This call is in fact not recursive but is counted as such. Outcome, it will work
  always but if max_sp_recursion_depth is reached we are going to cache one more
  sp_head instance.

    
User name (host name) has limit on length. The server code relies on these
limits when storing the names. The problem was that sometimes these limits
were not checked properly, so that could lead to buffer overflow.
  
The fix is to check length of user/host name in parser and if string is too
long, throw an error.


mysql-test/r/grant.result:
  Updated result file.
mysql-test/r/sp.result:
  Updated result file.
mysql-test/r/trigger.result:
  Updated result file.
mysql-test/r/view.result:
  Updated result file.
mysql-test/t/grant.test:
  Added test for BUG#16899.
mysql-test/t/sp.test:
  Added test for BUG#16899.
mysql-test/t/trigger.test:
  Added test for BUG#16899.
mysql-test/t/view.test:
  Added test for BUG#16899.
sql/mysql_priv.h:
  Added prototype for new function.
sql/sql_acl.cc:
  Remove outdated checks.
sql/sql_parse.cc:
  Add a new function for checking string length.
sql/share/errmsg.txt:
  Added new resources.
sql/sql_yacc.yy:
  Check length of user/host name.

  
User name (host name) has limit on length. The server code relies on these
limits when storing the names. The problem was that sometimes these limits
were not checked properly, so that could lead to buffer overflow.

The fix is to check length of user/host name in parser and if string is too
long, throw an error.


mysql-test/r/grant.result:
  Updated result file.
mysql-test/r/sp.result:
  Updated result file.
mysql-test/r/trigger.result:
  Updated result file.
mysql-test/r/view.result:
  Updated result file.
mysql-test/t/grant.test:
  Added test for BUG#16899.
mysql-test/t/sp.test:
  Added test for BUG#16899.
mysql-test/t/trigger.test:
  Added test for BUG#16899.
mysql-test/t/view.test:
  Added test for BUG#16899.
sql/mysql_priv.h:
  Added prototype for new function.
sql/share/errmsg.txt:
  Added new resources.
sql/sql_acl.cc:
  Remove outdated checks.
sql/sql_parse.cc:
  Add a new function for checking string length.
sql/sql_yacc.yy:
  Check length of user/host name.

Implemented code review comments
Test cleanup


sql/protocol.cc:
  Bug#8153 (Stored procedure with subquery and continue handler, wrong result)
  
  Implemented code review comments

client/mysqldump.c:
  A post-merge fix - 'sock' was renamed to 'mysql'
mysql-test/r/events_bugs.result:
  A post merge fix: now we strip rear comments from the query before
  it gets into the log.
mysql-test/r/func_group.result:
  A post merge fix: default clause is now printed uppercase.
mysql-test/r/im_life_cycle.result:
  Fix my mistake in manual resolve.
mysql-test/r/mysqlcheck.result:
  use test; - after we drop client_test_db there is no current database.
  This cleanup is present in 5.1 only, but the test that was added in 
  5.0 assumes there is a current database, test.
mysql-test/r/mysqldump.result:
  Ignore results of execution of mysqldump: we can't rely on 
  MASTER_LOG_POS in test results, it's different for statement
  and row level logging.
mysql-test/r/mysqlshow.result:
  A post-merge fix: information schema contains a few more tables
  in 5.1
mysql-test/r/mysqltest.result:
  A post merge fix: add 5.1 test end separator.
mysql-test/r/ndb_basic.result:
  A post-merge fix: add test end separators.
mysql-test/r/rpl_switch_stm_row_mixed.result:
  A post merge fix: length of varbinary column is now 3 times less.
  Assuming a side effect of some other change. Length of any
  field is not relevant in this test.
mysql-test/r/rpl_view.result:
  Add an end of test marker.
mysql-test/r/show_check.result:
  Remove duplicate results. Add results from a merged test case.
mysql-test/r/sp-error.result:
  Add test end separators.
mysql-test/r/sp-security.result:
  Post-merge fix: use test after the current database is dropped.
mysql-test/r/sp.result:
  Remove a duplicate result (bad merge that left a copy of
  the test case for Bug#19862 in the test suite).
mysql-test/r/strict.result:
  An after-merge fix for a new test case: in 5.1 we issue a more accurate
  error message: "Incorrect value" instead of "Truncated value". I reason
  it so that in case of an error nothing is truncated, really.
  Also found similar changes in other test cases.
mysql-test/r/type_datetime.result:
  Fix the text of an error.
mysql-test/r/union.result:
  A post-merge fix: CHARACTER SET is now uppercase.
mysql-test/t/mysqlcheck.test:
  A post-merge fix: use test, after current database is dropped, there
  is no current database.
mysql-test/t/mysqldump.test:
  Disable result log: it's dependent on binlog position.
mysql-test/t/sp-security.test:
  use test
sql/item_sum.cc:
  Adjust the call to the constructor after the merge.
sql/sp_head.cc:
  Add a missing DBUG_VOID_RETURN, move security checks out of
  execute_trigger to Table_triggers_list: in 5.1 we check for
  TRIGGER privilege, not SUPER privilege to execute triggers, so these
  checks lack table context inside execute_trigger and have to be
  performed when we have table object on hand.
sql/sql_db.cc:
  A post-merge fix: adjust load_db_opt_by_name and check_db_dir_existence
  (new functions added in 5.0) to be tablename-to-filename encoding 
  friendly.
sql/sql_lex.cc:
  A post-merge fix: make skip_rear_comments operate on const uchar *s.
sql/sql_lex.h:
  A post-merge fix.
sql/sql_show.cc:
  A post-merge fix: fix a bad merge, rename orig_sql_command -> sql_command.
sql/sql_trigger.cc:
  A post-merge fix: move security checks to process_triggers
  from execute_trigger.
sql/sql_view.cc:
  Adjust to the new signature of skip_rear_comments.
sql/sql_yacc.yy:
  Adjust to the new signature of init_strings.

mysql-test/r/mysqldump.result:
  update result
mysql-test/t/mysqldump.test:
  update test

    
create function func() returns char(10) binary ...
is no more possible. This will be reenabled when 
bug 2676 "DECLARE can't have COLLATE clause in stored procedure"
is fixed.

Fix after 2nd review


mysql-test/r/sp-error.result:
  update result
mysql-test/r/sp.result:
  update result
mysql-test/t/sp-error.test:
  add a test case for bug#20701 BINARY keyword should be forbidden in stored procedures
mysql-test/t/sp.test:
  Fix test case which uses binary for the return value of a function.
  It's no more possible after fix for bug#20701 
  BINARY keyword should be forbidden in SP
  
  Fix few glitches where ; is used instead of | . The delimiter is |
sql/sql_yacc.yy:
  Fix for bug#20701 BINARY keyword should be forbidden in stored routines
  
  create function func() returns char(10) binary ...
  is no more possible. This will be reenabled when 
  bug 2676 "DECLARE can't have COLLATE clause in stored procedure"
  is fixed

The following procedure was not possible if max_sp_recursion_depth is 0
create procedure show_proc() show create procedure show_proc;

Actually there is no recursive call but the limit is checked.

Solved by temporarily increasing the thread's limit just before the fetch from cache
and decreasing after that.


mysql-test/r/sp.result:
  update result
mysql-test/t/sp.test:
  Test for bug #21416 SP: Recursion level higher than zero needed for non-recursive call
sql/sp.cc:
  Increase the max_sp_recursion_depth temporarily for SHOW CREATE PROCEDURE call.
  This call is in fact not recursive but is counted as such. Outcome, it will work
  always but if max_sp_recursion_depth is reached we are going to cache one more
  sp_head instance.

Before this fix,
- a runtime error in a statement in a stored procedure with no error handlers
was properly detected (as expected)
- a runtime error in a statement with an error handler inherited from a non
local runtime context (i.e., proc a with a handler, calling proc b) was
properly detected (as expected)
- a runtime error in a statement with a *local* error handler was executed
as follows :
a) the statement would succeed, regardless of the error condition, (bug)
b) the error handler would be called (as expected).

The root cause is that functions like my_messqge_sql would "forget" to set
the thread flag thd->net.report_error to 1, because of the check involving
sp_rcontext::found_handler_here().
Failure to set this flag would cause, later in the call stack,
in Item_func::fix_fields() at line 190, the code to return FALSE and consider
that executing the statement was successful.

With this fix :
- error handling code, that was duplicated in different places in the code,
is now implemented in sp_rcontext::handle_error(),
- handle_error() correctly sets thd->net.report_error when a handler is
present, regardless of the handler location (local, or in the call stack).

A test case, bug8153_subselect, has been written to demonstrate the change
of behavior before and after the fix.

Another test case, bug8153_function_a, as also been writen.
This test has the same behavior before and after the fix.
This test has been written to demonstrate that the previous expected
result of procedure bug18787, was incorrect, since select no_such_function()
should fail and therefore not produce a result.

The incorrect result for bug18787 has the same root cause as Bug#8153,
and the expected result has been adjusted.


sql/mysqld.cc:
  Bug#8153, use sp_rcontext::handle_error() to handle errors.
sql/sql_error.cc:
  Bug#8153, use sp_rcontext::handle_error() to handle errors.
sql/protocol.cc:
  Bug#8153, use sp_rcontext::handle_error() to handle errors.
sql/sp_rcontext.h:
  Bug#8153, created helper sp_rcontext::handle_error() to handle errors.
sql/sp_rcontext.cc:
  Bug#8153, created helper sp_rcontext::handle_error() to handle errors.
mysql-test/t/sp.test:
  Bug#8153, added test cases.
mysql-test/r/sp.result:
  Bug#8153, added test cases, fixed expected result of bug18787.

Fix for BUG#16676: Database CHARSET not used for stored procedures

The problem in BUG#16211 is that CHARSET-clause of the return type for
stored functions is just ignored.

The problem in BUG#16676 is that if character set is not explicitly
specified for sp-variable, the server character set is used instead
of the database one.

The fix has two parts:

  - always store CHARSET-clause of the return type along with the
    type definition in mysql.proc.returns column. "Always" means that
    CHARSET-clause is appended even if it has not been explicitly
    specified in CREATE FUNCTION statement (this affects BUG#16211 only).

    Storing CHARSET-clause if it is not specified is essential to avoid
    changing character set if the database character set is altered in
    the future.

    NOTE: this change is not backward compatible with the previous releases.

  - use database default character set if CHARSET-clause is not explicitly
    specified (this affects both BUG#16211 and BUG#16676).

    NOTE: this also breaks backward compatibility.


mysql-test/r/mysqldump.result:
  Updated result file.
mysql-test/r/sp.result:
  Updated result file.
mysql-test/t/sp.test:
  Provided test cases for BUG#16211, BUG#16676.
sql/mysql_priv.h:
  Added two convenient functions for work with databases.
sql/sp.cc:
  1. Add CHARSET-clause to CREATE-statement if it has been explicitly specified.
  2. Polishing -- provided some comments.
sql/sp_head.cc:
  Use database charset as default charset of sp-variable.
sql/sp_head.h:
  Move init_sp_name() out of init_strings().
sql/sql_db.cc:
  Two new functions created:
    - load_db_opt_by_name();
    - check_db_dir_existence();
sql/sql_show.cc:
  Eliminate duplicated code by using
  check_db_dir_existence() and load_db_opt_by_name()
sql/sql_table.cc:
  Eliminate duplicated code by using
  check_db_dir_existence() and load_db_opt_by_name()
sql/sql_yacc.yy:
  Call sp_head::init_sp_name() to initialize stored routine name.

mysql-test/r/sp.result:
  Exclude
mysql-test/t/sp.test:
  Exclude