[MDEV-13149] "Incorrect database name" error by SHOW FUNCTION STATUS in PAD_CHAR_TO_FULL_LENGTH mode - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10
Component/s: Data types
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.

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-06-24 22:35 - edited

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).

Elena Stepanova added a comment - 2017-06-24 22:35 - edited 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).

Sergei Golubchik added a comment - 2017-10-09 09:30

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?

Sergei Golubchik added a comment - 2017-10-09 09:30 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?

Vesa Pentti (Inactive) added a comment - 2017-10-09 17:30

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

Vesa Pentti (Inactive) added a comment - 2017-10-09 17:30 fix in "bb-5.5-pentve" (still struggling with post-commit mail sender so there hasn't been a commit notification)

Sergei Golubchik added a comment - 2017-10-09 17:51

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.

Sergei Golubchik added a comment - 2017-10-09 17:51 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.

Vesa Pentti (Inactive) added a comment - 2017-10-09 19:19

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

Vesa Pentti (Inactive) added a comment - 2017-10-09 19:19 Good idea. Done. Shall I push the commit directly to "5.5" ?

Sergei Golubchik added a comment - 2017-10-10 12:33

Yes, please

Sergei Golubchik added a comment - 2017-10-10 12:33 Yes, please

Vesa Pentti (Inactive) added a comment - 2017-10-10 13:18

Pushed to 5.5

Vesa Pentti (Inactive) added a comment - 2017-10-10 13:18 Pushed to 5.5

People

Assignee:: Vesa Pentti (Inactive)

Reporter:: Richard Stracke

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2017-06-22 08:39

Updated:: 2024-07-08 00:38

Resolved:: 2017-10-10 15:26

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server