Prepared statements in stored procedures crash query response time plugin
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Vlad Lesin | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Fix Released
|
High
|
Vlad Lesin |
Bug Description
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 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-
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://
key_buffer_
read_buffer_
max_used_
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_
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_
/home/m_
/lib64/
/home/mysql/
/home/mysql/
/home/mysql/
/home/mysql/
/home/mysql/
/home/mysql/
/lib64/
/lib64/
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.
Connection ID (thread ID): 53
Status: NOT_KILLED
You may download the Percona Server operations manual by visiting
http://
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_
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-
2015-02-26 15:10:13 2332 [Note] Plugin 'FEDERATED' is disabled.
2015-02-26 15:10:13 2b2b6b4f6d00 InnoDB: Warning: Using innodb_
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://
2015-02-26 15:10:14 2332 [Note] Recovering after a crash using /home/mysql/
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/
2015-02-26 15:10:14 2332 [Note] RSA public key file not found: /home/mysql/
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/
Version: '5.6.22-72.0-log' socket: '/home/
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=
begin
declare PROCEDURE_
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_
declare table_cursor cursor for select table_name from information_
declare continue handler for SQLSTATE '02000' begin set p_done = 1; end;
set PROCEDURE_
select PROCEDURE_
set p_done = 0;
set p_cnt_view = 0;
set p_stats_schema = concat(
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_
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_
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_
end
//
delimiter ;
use sample;
call recreate_
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-
The same works properly when i downgraded to Percona-
information type: | Public → Private Security |
information type: | Private Security → Public |
description: | updated |
tags: | added: query-response-time |
summary: |
- mysql 5.6. crashes when dropping and creating view from procedure. + Prepared statements in stored procedures crash query response time + plugin |
Hi,
Couldn't verify your test case with Server version: 5.6.22-72.0-log Percona Server (GPL), Release 72.0, Revision 738
I also tried to verify with 5.6.23 and same, no error. Can you please try to check with latest PS 5.6.23?
nilnandan@ desktop: ~/sandboxes/ rsandbox_ Percona- Server- 5_6_22/ master$ mysql -uroot -p --socket= /tmp/mysql_ sandbox21087. sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22-72.0-log Percona Server (GPL), Release 72.0, Revision 738
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; ------- ------- + ------- ------- + ------- ------- +
+------
| Database |
+------
| information_schema |
| dbtest |
| mysql |
| performance_schema |
| test |
+------
5 rows in set (0.05 sec)
mysql> CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.02 sec)
mysql> use sample
Database changed
mysql>
mysql> create table t1 (id int, name varchar(10));
Query OK, 0 rows affected (0.15 sec)
mysql> create table t2 (id int, name varchar(10), city varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> create table t3 (id int, phone int, email varchar(25));
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE DATABASE `sample_stats` /*!40100 DEFAULT CHARACTER SET utf8 */;
Query OK, 1 row affected (0.00 sec)
mysql> `root`@ `localhost` PROCEDURE `recreate_ views_for_ stats`( ) VERSION_ INFO varchar(200); schema. tables v1 where v1.table_ schema= concat( database( ),'_stats' ) and v1.table_ type='VIEW' ; schema. tables v1 where v1.table_ schema= database( ); VERSION_ INFO = 'v 1.11 '; VERSION_ INFO; database( ),'_stats' ); schema, '.',p_table_ name);
mysql> use sample
Database changed
mysql> delimiter //
mysql>
mysql> CREATE DEFINER=
-> begin
-> declare PROCEDURE_
-> 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_
-> declare table_cursor cursor for select table_name from information_
->
-> declare continue handler for SQLSTATE '02000' begin set p_done = 1; end;
->
-> set PROCEDURE_
->
-> select PROCEDURE_
->
-> set p_done = 0;
-> set p_cnt_view = 0;
-> set p_stats_schema = concat(
-> 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_
-> 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;
...