[MDEV-13149] "Incorrect database name" error by SHOW FUNCTION STATUS in PAD_CHAR_TO_FULL_LENGTH mode Created: 2017-06-22  Updated: 2020-08-25  Resolved: 2017-10-10

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Vesa Pentti (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

all, tested with official docker images

Sprint: 10.2.10

 Description   

If sql-mode PAD_CHAR_TO_FULL_LENGTH is active, show function not work returns

ERROR 1102 (42000): Incorrect database name 'aa '

To Reproduce:

Preparation:

CREATE DATABASE `aa`;
 
 
 
DELIMITER $$
 
 
 
CREATE DEFINER=`root`@`%` FUNCTION `testfunction`()
 
RETURNS varchar(10) CHARSET latin1
 
LANGUAGE SQL
 
NOT DETERMINISTIC
 
NO SQL
 
SQL SECURITY DEFINER
 
COMMENT ''
 
BEGIN
 
 
 
RETURN "test";
 
 
 
END
 
 
 
 
 
$$
 
 
 
 
 
DELIMITER ;
 
 
 
use aa;

Show function in with default sql_mode works well:

mysql> SHOW FUNCTION STATUS WHERE `Db`='aa';
 
+----+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
| Db | Name         | Type     | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
 
+----+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
| aa | testfunction | FUNCTION | root@%  | 2017-06-22 08:26:05 | 2017-06-22 08:26:05 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
 
+----+--------------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
1 row in set (0.00 sec

Set SQL_mode PAD_CHAR_TO_FULL_LENGTH

set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

Show Function throws an error. Search query is filled with spaces up to 64 chars.

mysql> SHOW FUNCTION STATUS WHERE `Db`='aa';    
ERROR 1102 (42000): Incorrect database name 'aa                                                              '

It looks like, that the root cause is the column db in the table mysql.proc.
Columntype is CHAR(64)

Perhaps the sql mode 'PAD_CHAR_TO_FULL_LENGTH' should be ignored
for all SHOW xxxx commands.

 Comments   
Comment by Elena Stepanova [ 2017-06-24 ] 

create function f() returns int return 1;
 
show function status;
 
set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
 
show function status;
 
drop function f;


MariaDB [test]> create function f() returns int return 1;
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [test]> show function status;
 
+------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
| Db   | Name | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
 
+------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
| test | f    | FUNCTION | root@localhost | 2017-06-25 01:35:02 | 2017-06-25 01:35:02 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
+------+------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> show function status;
 
ERROR 1102 (42000): Incorrect database name 'test                                                            '
 
MariaDB [test]> drop function f;

Also reproducible on MySQL 5.5/5.6, but apparently fixed in 5.7 (or maybe it's become so different there that it's incomparable).

Comment by Sergei Golubchik [ 2017-10-09 ]

The approach looks ok. Comments:
1. please fix this bug in 5.5, not in 10.2
2. Something's strange with your test — it's all commented out, while the result is not empty. Did you forget to uncomment it?

Comment by Vesa Pentti (Inactive) [ 2017-10-09 ]

fix in "bb-5.5-pentve" (still struggling with post-commit mail sender so there hasn't been a commit notification)

Comment by Sergei Golubchik [ 2017-10-09 ]

I'd suggest to move

  /* Disable padding temporarily so it doesn't break the query */
 
  ulonglong sql_mode_was = thd->variables.sql_mode;
 
  thd->variables.sql_mode &= ~MODE_PAD_CHAR_TO_FULL_LENGTH;

after tables are opened, then you won't need to restore it twice.

Ok to push after that.

Comment by Vesa Pentti (Inactive) [ 2017-10-09 ]

Good idea. Done. Shall I push the commit directly to "5.5" ?

Comment by Sergei Golubchik [ 2017-10-10 ]

Yes, please

Comment by Vesa Pentti (Inactive) [ 2017-10-10 ]

Pushed to 5.5

Generated at Thu Feb 08 08:03:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.