Bug #70521 Can't query `information_schema`.`routines` with sqlmode PAD_CHAR_TO_FULL_LENGTH

• Submitted: 4 Oct 2013 12:52
• Modified: 6 Dec 2013 10:28
• Reporter: Maxim Makhotkin
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.6.14-log
• OS: Windows (7 x64)
• CPU Architecture: Any

[4 Oct 2013 12:52] Maxim Makhotkin

Description:
Cannot fetch a list of stored procs or functions when sqlmode contains 'PAD_CHAR_TO_FULL_LENGTH'

SELECT * FROM `INFORMATION_SCHEMA`.`ROUTINES` generates an errror:
ERROR 1102 (42000): Incorrect database name 'demo1012                                                        '
(note the extra spaces after the database name and before the closing quote)

How to repeat:
mysql> use 'demo1012'
Database changed
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------+
| @@sql_mode                                                                             |
+----------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `INFORMATION_SCHEMA`.`ROUTINES`;
ERROR 1102 (42000): Incorrect database name 'demo1012                                                        '

[4 Oct 2013 12:54] Maxim Makhotkin

fix typo

[4 Oct 2013 16:52] MySQL Verification Team

Hello Maxim,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[14 Nov 2013 15:06] Hartmut Holzgraefe

I fail to reproduce this with 5.6.14 on linux? Is it Windows specific? Or are there some "how to reproduce" steps missing?

[29 Nov 2013 11:51] Maxim Makhotkin

I didn't have a chance to test it on other operation systems. 

Before attempting a reproduce make sure your @@sql_mode does contain the mentioned option PAD_CHAR_TO_FULL_LENGTH

[6 Dec 2013 10:28] Maxim Makhotkin

Reproduced this bug on Amazon AWS, they provide a Linux ч64 installation of Community Server edition (5.6.13)

[9 Jul 2015 15:27] Rick James

Still a problem in 5.6.23:

http://forums.mysql.com/read.php?98,632729,632729

[18 Jul 2015 7:53] MySQL Verification Team

mysql> set sql_mode="PAD_CHAR_TO_FULL_LENGTH";
Query OK, 0 rows affected (0.00 sec)

mysql> show function status;
ERROR 1102 (42000): Incorrect database name 'test
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.8.0-m17-log |
+---------------+
1 row in set (0.00 sec)

In the error log we have all the warnings:

[Warning] Stored routine 'sys                                                             '.'ps_is_account_enabled                                           ': invalid value in column mysql.proc.character_set_client.
[Warning] Stored routine 'sys                                                             '.'ps_is_account_enabled                                           ': invalid value in column mysql.proc.collation_connection.
[Warning] Stored routine 'sys                                                             '.'ps_is_account_enabled                                           ': invalid value in column mysql.proc.db_collation.
[Warning] Stored routine 'sys                                                             '.'ps_is_consumer_enabled                                          ': invalid value in column mysql.proc.character_set_client.
[Warning] Stored routine 'sys                                                             '.'ps_is_consumer_enabled                                          ': invalid value in column mysql.proc.collation_connection.
[Warning] Stored routine 'sys                                                             '.'ps_is_consumer_enabled                                          ': invalid value in column mysql.proc.db_collation.