Bug#32633 Can not create any routine if SQL_MODE=no_engine_substitution

The problem is that one can not create a stored routine if sql_mode
contains NO_ENGINE_SUBSTITUTION or PAD_CHAR_TO_FULL_LENGTH. Also when
a event is created, the mode is silently lost if sql_mode contains one
of the aforementioned.  This was happening because the table definitions
which stored sql_mode values weren't being updated to accept new values
of sql_mode.

The solution is to update, in a backwards compatible manner, the various
table definitions (columns) that store the sql_mode value to take into
account the new possible values. One incompatible change is that if a event
that is being created can't be stored to the mysql.event table, an error
will be raised.

The tests case also ensure that new SQL modes will be added to the mysql.proc
and mysql.event tables, otherwise the tests will fail.


mysql-test/r/events_bugs.result:
  Add test case result for Bug#32633
mysql-test/r/information_schema.result:
  Update the sql_mode column definition.
mysql-test/r/sp.result:
  Add test case result for Bug#32633
mysql-test/r/system_mysql_db.result:
  Update the sql_mode column definition.
mysql-test/t/events_bugs.test:
  Add test case for Bug#32633
mysql-test/t/sp.test:
  Add test case for Bug#32633
mysql-test/t/system_mysql_db_fix50117.test:
  Update the sql_mode column definition.
scripts/mysql_system_tables.sql:
  Update the sql_mode column definition.
scripts/mysql_system_tables_fix.sql:
  Update the sql_mode column definition.
sql/event_db_repository.cc:
  Reset and restore SQL modes when storing and loading a
  event from the data dictionary. Also throw out a error
  if a store fails.
sql/mysqld.cc:
  Add warning to avoid this problem in the future.
sql-common/my_user.c:
  Truncate length if user name or host name does not fit
  in the buffer.
sql/sp.cc:
  SQL mode of the thread must not effect data dictionary operations.

1 files changed, 7 insertions, 1 deletions

diff --git a/sql-common/my_user.c b/sql-common/my_user.c
index 16a544387ee..d6f2818ad77 100644
--- a/sql-common/my_user.c
+++ b/sql-common/my_user.c
@@ -15,7 +15,7 @@
 
 #include <my_user.h>
 #include <m_string.h>
-
+#include <mysql_com.h>
 
 /*
   Parse user value to user name and host name parts.
@@ -47,6 +47,12 @@ void parse_user(const char *user_id_str, size_t user_id_len,
     *user_name_len= p - user_id_str;
     *host_name_len= user_id_len - *user_name_len - 1;
 
+    if (*user_name_len > USERNAME_LENGTH)
+      *user_name_len= USERNAME_LENGTH;
+
+    if (*host_name_len > HOSTNAME_LENGTH)
+      *host_name_len= HOSTNAME_LENGTH;
+
     memcpy(user_name_str, user_id_str, *user_name_len);
     memcpy(host_name_str, p + 1, *host_name_len);
   }