MySQL Bugs: #13331: SHOW CREATE VIEW on view without definer crashes server

Bug #13331	SHOW CREATE VIEW on view without definer crashes server
Submitted:	19 Sep 2005 22:43	Modified:	2 Mar 2006 11:00
Reporter:	Evgeny Potemkin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0-bk	OS:
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Bug occurs because when view is created and no user is specified the definer is set as ''(empty string).
When SHOW CREATE VIEW prints definer it calls append_identifier() -> get_quote_char_for_identifier() -> is_keyword() -> get_hash_symbol() and get_hash_symbol() assumes that len > 0 thus crashing.

How to repeat:
start server
connect with 'mysql test' without specifying the user
create any view, run SHOW CREATE VIEW on it.

I was unable to repeat with latest source and with a simple view,
can you show the actual create commands you used?
Thanks.

miguel@hegel:~/dbs/5.0> bin/mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tb (id int);
Query OK, 0 rows affected (0.04 sec)

mysql> create view tb_v as select * from tb;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view tb_v\G
*************************** 1. row ***************************
       View: tb_v
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`localhost` SQL SECURITY DEFINER VIEW `tb_v` AS select `tb`.`id` AS `id` from `tb`
1 row in set (0.01 sec)

mysql>

bk pull ...
BUILD/compile-pentium-debug
cd mysql-test
run and stop ./mysql-test-run it will create test db
../sql/mysqld --no-defaults     --basedir=. --datadir=./var/master-data --skip-ndbcluster --skip-bdb --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/ --skip-networking --skip-innodb 
client/mysql test
>create table t1(f1 int);
>create view v1 as select * from t1;
>show create view v1;
crash

system gentoo x86_64 2.6.11 glibc 2.3.4

I was able to repeat the behaviour described on our nocona machine (using exactly those strange actions listed in the last message).

After build, runiing and stopping mysql-test, I've started the server:

-bash-2.05b$ ../sql/mysqld --no-defaults     --basedir=. --datadir=./var/master
-data \
> --skip-ndbcluster --skip-bdb --language=../sql/share/english/ \
> --character-sets-dir=../sql/share/charsets/ --skip-networking --skip-innodb
050920 19:46:03 [Note] ../sql/mysqld: ready for connections.
Version: '5.0.14-rc-nightly-20050919-debug'  socket: '/tmp/mysql.sock'  port: 0
 Source distribution
...

Then, from the other window:

-bash-2.05b$ cd mysql-5.0.14-rc-nightly-20050919
-bash-2.05b$ client/mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-nightly-20050919-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show create view v1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Just after that, in the window where I started server I've got:

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.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225788 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

So, yes, it is a crash. Platfrom details:

-bash-2.05b$ uname -a
Linux nocona.mysql.com 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:46:36 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

Can't repeat. Tested on 5.0.19. It seems that bug was fixed somewhere between 5.0.14 and 5.0.19.

I hit the same problem on 5.0.77. I was importing a database from a mysqldump file on another server (also version 5.0.77). The SQL produced by mysqldump contains this:

/*!50001 DROP TABLE `v_individual_articles`*/;
/*!50001 DROP VIEW IF EXISTS `v_individual_articles`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`robert`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_individual_articles` AS select ...etc.

The key thing is that the user in the DEFINER line ('robert@localhost') did not exist on the destination server at the time the database was imported.

When I subsequently do SHOW CREATE VIEW v_individual_articles I get the error:

ERROR 2013 (HY000): Lost connection to MySQL server during query

After I created the user robert@localhost, I can then do the SHOW CREATE VIEW again fine.

Same problem, same behaviour reported by Robert Bergs (Mysql 5.0.77 on RHEL 5.4).