Comment 0 for bug 1426345

Hi

The procedure we have has been working fine since mysql 5.0. What it does simply is gather the table names from a schema (lets say Schema A) and drop the view if they exists and re-create them again in another schema (Schema A_stats) .

when call it crashes with mysqld 11 signal.

Env : Rhel 5.11

uname -a
Linux sula0-1109 2.6.18-400.1.1.el5 #1 SMP Sun Dec 14 06:01:17 EST 2014 x86_64 x86_64 x86_64 GNU/Linux
:~ > cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.11 (Tikanga)
mysql : Percona-Server-5.6.22-rel72.0-738.Linux.x86_64

Crash log:

14:10:12 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

key_buffer_size=209715200
read_buffer_size=4194304
max_used_connections=49
max_threads=252
thread_count=49
connection_count=49
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2272884 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

hread pointer: 0x2b25743b6d90
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 2b2573932080 thread_stack 0x40000
/home/m_game_s1/mysql/bin/mysqld(my_print_stacktrace+0x2c)[0x8f0d5c]
/home/m_game_s1/mysql/bin/mysqld(handle_fatal_signal+0x461)[0x67a7d1]
/lib64/libpthread.so.0[0x382a40eca0]
/home/mysql/lib/mysql/plugin/query_response_time.so[0x2b2555f98b22]
/home/mysql/bin/mysqld[0x6aa205]
/home/mysql/bin/mysqld(_Z18mysql_audit_notifyP3THDjjz+0xa8)[0x6aa3d8]
/home/mysql/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x9c8)[0x7021d8]
/home/mysql/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x162)[0x6d0172]
/home/mysql/bin/mysqld(handle_one_connection+0x40)[0x6d0260]
/lib64/libpthread.so.0[0x382a40683d]
/lib64/libc.so.6(clone+0x6d)[0x3829cd4fcd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (2b425854dba0): drop view if exists hh_s1_stats.access_status_groups_last
Connection ID (thread ID): 53
Status: NOT_KILLED

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
Writing a core file
150226 15:10:12 mysqld_safe Number of processes running now: 0
150226 15:10:12 mysqld_safe mysqld restarted
2015-02-26 15:10:12 0 [Warning] Using unique option prefix thread-cache instead of thread_cache_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-26 15:10:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-26 15:10:12 2332 [Warning] One can only use the --user switch if running as root

2015-02-26 15:10:13 2332 [Warning] Using unique option prefix innodb-additional-mem-pool instead of innodb-additional-mem-pool-size is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-26 15:10:13 2332 [Note] Plugin 'FEDERATED' is disabled.
2015-02-26 15:10:13 2b2b6b4f6d00 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2015-02-26 15:10:13 2332 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-02-26 15:10:13 2332 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-26 15:10:13 2332 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-02-26 15:10:13 2332 [Note] InnoDB: Memory barrier is not used
2015-02-26 15:10:13 2332 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-02-26 15:10:13 2332 [Note] InnoDB: Using Linux native AIO
2015-02-26 15:10:13 2332 [Note] InnoDB: Not using CPU crc32 instructions
2015-02-26 15:10:13 2332 [Note] InnoDB: Initializing buffer pool, size = 200.0M
2015-02-26 15:10:13 2332 [Note] InnoDB: Completed initialization of buffer pool
2015-02-26 15:10:13 2332 [Note] InnoDB: Highest supported file format is Barracuda.
2015-02-26 15:10:13 2332 [Note] InnoDB: The log sequence numbers 10061582051 and 10061582051 in ibdata files do not match the log sequence number 10084984124 in the ib_logfiles!
2015-02-26 15:10:13 2332 [Note] InnoDB: Database was not shutdown normally!
2015-02-26 15:10:13 2332 [Note] InnoDB: Starting crash recovery.
2015-02-26 15:10:13 2332 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-02-26 15:10:13 2332 [Note] InnoDB: Restoring possible half-written data pages
2015-02-26 15:10:13 2332 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 54282, file name logbin.000027
2015-02-26 15:10:14 2332 [Note] InnoDB: 128 rollback segment(s) are active.
2015-02-26 15:10:14 2332 [Note] InnoDB: Waiting for purge to start
2015-02-26 15:10:14 2332 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.22-rel72.0 started; log sequence number 10084984124
2015-02-26 15:10:14 2332 [Note] Recovering after a crash using /home/mysql/data/logbin/logbin
2015-02-26 15:10:14 2332 [Note] Starting crash recovery...
2015-02-26 15:10:14 2332 [Note] Crash recovery finished.
2015-02-26 15:10:14 2332 [Note] RSA private key file not found: /home/mysql/data/dbs//private_key.pem. Some authentication plugins will not work.
2015-02-26 15:10:14 2332 [Note] RSA public key file not found: /home/mysql/data/dbs//public_key.pem. Some authentication plugins will not work.
2015-02-26 15:10:14 2332 [Note] Server hostname (bind-address): '*'; port: 3305
2015-02-26 15:10:14 2332 [Note] IPv6 is available.
2015-02-26 15:10:14 2332 [Note] - '::' resolves to '::';
2015-02-26 15:10:14 2332 [Note] Server socket created on IP: '::'.
2015-02-26 15:10:14 2332 [Note] Event Scheduler: Loaded 0 events
2015-02-26 15:10:14 2332 [Note] /home/mysql/bin/mysqld: ready for connections.
Version: '5.6.22-72.0-log' socket: '/home/mysql/data/run/mysqld.sock' port: 3305 Percona Server (GPL), Release 72.0, Revision 738

How to reproduce

Create 2 databases

CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET utf8 */
-- create some tables

CREATE DATABASE `sample_stats` /*!40100 DEFAULT CHARACTER SET utf8 */
empty database where we will create the view

use sample;

delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE `recreate_views_for_stats`()
begin
  declare PROCEDURE_VERSION_INFO varchar(200);
  declare p_done int;
  declare p_table_name varchar(64);
  declare p_stats_schema varchar(64);
  declare p_cnt int;
  declare p_cnt_view int;
  declare p_stmt varchar(400);

  declare view_cursor cursor for select table_name from information_schema.tables v1 where v1.table_schema=concat(database(),'_stats') and v1.table_type='VIEW';
  declare table_cursor cursor for select table_name from information_schema.tables v1 where v1.table_schema=database();

  declare continue handler for SQLSTATE '02000' begin set p_done = 1; end;

  set PROCEDURE_VERSION_INFO = 'v 1.11 ';

  select PROCEDURE_VERSION_INFO;

  set p_done = 0;
  set p_cnt_view = 0;
  set p_stats_schema = concat(database(),'_stats');
  open view_cursor;
  repeat
    fetch view_cursor into p_table_name;
    if not p_done then
      set @sql = concat('drop view if exists ',p_stats_schema,'.',p_table_name);
      prepare p_stmt from @sql;
      execute p_stmt;
      deallocate prepare p_stmt;
      set p_cnt_view = p_cnt_view + 1;

    end if;
  until p_done end repeat;

  set p_done = 0;
  set p_cnt = 0;
  open table_cursor;
  repeat
    fetch table_cursor into p_table_name;
    if not p_done then
      set @sql = concat('create view ',p_stats_schema,'.',p_table_name,' as select * from ',p_table_name);
      prepare p_stmt from @sql;
      execute p_stmt;
      deallocate prepare p_stmt;
      set p_cnt = p_cnt + 1;
    end if;
  until p_done end repeat;

  select p_cnt_view as views_dropped, p_cnt as views_created, count(*) as views_total from information_schema.views where table_schema=p_stats_schema;
end
//

delimiter ;

use sample;
call recreate_views_for_stats();

The above call should create views in the sample_stats for all the tables in the schema sample.
may be not the first time as it might not have any views to drop . run it first might create views and on the second run it will crash.

Something has changed in the Percona-Server-5.6.22-rel72.0-738.Linux.x86_64
The same works properly when i downgraded to Percona-Server-5.6.22-rel71.0-726.Linux.x86_64 and Percona-Server-5.6.21-rel69.0-675.Linux.x86_64