Description:
Hi,
In version 6.0 you introduced the table `INFORMATION_SCHEMA`.`PARAMETERS`.
This table is very useful for retrieving routines' metadata using the information_schema database, without using the mysql database (#33455).
Unfortunately, I am still not able to retrieve all the info I need (or, in other words, all the info I was able to retrieve using the mysql.proc table).
In particular, that's true for attributes of the parameter data types (unsigned, zerofill, binary, ascii, unicode).
If I have a routine with the following parameters:
IN `P1` BIGINT
OUT `P2` BIGINT zerofill
IN `P3` TEXT binary
OUT `P4` CHAR unicode
IN `p5` ENUM('value1', 'value2')
using mysql.proc, I can get exactly the parameters of the routine:
IN `P1` BIGINT,
OUT `P2` BIGINT zerofill
IN `P3` TEXT binary
OUT `P4` CHAR unicode
IN `p5` ENUM('value1', 'value2')
but using information_schema.parameters I get wrong or missing information
PARAMETER_MODE: IN
PARAMETER_NAME: P1
DATA_TYPE: bigint
PARAMETER_MODE: OUT
PARAMETER_NAME: P2
DATA_TYPE: bigint
DTD_IDENTIFIER: bigint(20) unsigned zerofill ### possible wrong info: unsigned?
PARAMETER_MODE: IN
PARAMETER_NAME: P3
DATA_TYPE: text
DTD_IDENTIFIER: text ### missed info: binary?
PARAMETER_MODE: OUT
PARAMETER_NAME: P4
DATA_TYPE: char
DTD_IDENTIFIER: char(1) ### missed info: unicode?
PARAMETER_MODE: IN
PARAMETER_NAME: p5
DATA_TYPE: enum
DTD_IDENTIFIER: enum('value1','value2')
I'd like to ask you if this is a know issue or a bug and if it is possible to extend the parameters table with those info.
So far I have 2 alternatives:
- I can use the parameters table but get "missed" info
- I can use the proc table, get the info, but have a bug with users with limited-privileges (#33455).
Thanks.
How to repeat:
SQL Script:
SELECT VERSION(), CURRENT_USER();
DROP DATABASE IF EXISTS `testbug`;
CREATE DATABASE IF NOT EXISTS `testbug`;
DELIMITER $$
CREATE
DEFINER=CURRENT_USER()
PROCEDURE `testbug`.`test1`(
IN `P1` BIGINT,
OUT `P2` BIGINT zerofill,
IN `P3` TEXT binary,
OUT `P4` CHAR unicode,
IN `p5` ENUM('value1', 'value2')
)
COMMENT 'procedure test1'
BEGIN
# DO nothing
END $$
DELIMITER ;
#using mysql.proc
SELECT `param_list` FROM `mysql`.`proc` WHERE `db` ='testbug' AND `name` ='test1' and `type` ='procedure'\G
#using information_schema.parameters
SELECT * FROM `INFORMATION_SCHEMA`.`PARAMETERS` WHERE `SPECIFIC_SCHEMA` ='testbug' AND `SPECIFIC_NAME` ='test1' AND `ROUTINE_TYPE` ='PROCEDURE'\G
Script Output:
mysql> SELECT VERSION(), CURRENT_USER();
+---------------------------+----------------+
| VERSION() | CURRENT_USER() |
+---------------------------+----------------+
| 6.0.8-alpha-community-log | root@localhost |
+---------------------------+----------------+
1 row in set (0.00 sec)
mysql>
mysql> DROP DATABASE IF EXISTS `testbug`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS `testbug`;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> DELIMITER $$
mysql>
mysql> CREATE
-> DEFINER=CURRENT_USER()
-> PROCEDURE `testbug`.`test1`(
-> IN `P1` BIGINT,
-> OUT `P2` BIGINT zerofill,
-> IN `P3` TEXT binary,
-> OUT `P4` CHAR unicode,
-> IN `p5` ENUM('value1', 'value2')
-> )
-> COMMENT 'procedure test1'
-> BEGIN
-> # DO nothing
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> #using mysql.proc
mysql> SELECT `param_list` FROM `mysql`.`proc` WHERE `db` ='testbug' AND `name`
='test1' and `type` ='procedure'\G
*************************** 1. row ***************************
param_list: IN `P1` BIGINT,OUT `P2` BIGINT zerofill,IN `P3` TEXT binary,OUT `P4`
CHAR unicode,IN `p5` ENUM('value1', 'value2')
1 row in set (0.00 sec)
mysql>
mysql> #using information_schema.parameters
mysql> SELECT * FROM `INFORMATION_SCHEMA`.`PARAMETERS` WHERE `SPECIFIC_SCHEMA` =
'testbug' AND `SPECIFIC_NAME` ='test1' AND `ROUTINE_TYPE` ='PROCEDURE'\G
*************************** 1. row ***************************
SPECIFIC_CATALOG: NULL
SPECIFIC_SCHEMA: testbug
SPECIFIC_NAME: test1
ORDINAL_POSITION: 1
PARAMETER_MODE: IN
PARAMETER_NAME: P1
DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 19
NUMERIC_SCALE: 0
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: bigint(20)
ROUTINE_TYPE: PROCEDURE
*************************** 2. row ***************************
SPECIFIC_CATALOG: NULL
SPECIFIC_SCHEMA: testbug
SPECIFIC_NAME: test1
ORDINAL_POSITION: 2
PARAMETER_MODE: OUT
PARAMETER_NAME: P2
DATA_TYPE: bigint
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 19
NUMERIC_SCALE: 0
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: bigint(20) unsigned zerofill
ROUTINE_TYPE: PROCEDURE
*************************** 3. row ***************************
SPECIFIC_CATALOG: NULL
SPECIFIC_SCHEMA: testbug
SPECIFIC_NAME: test1
ORDINAL_POSITION: 3
PARAMETER_MODE: IN
PARAMETER_NAME: P3
DATA_TYPE: text
CHARACTER_MAXIMUM_LENGTH: 65535
CHARACTER_OCTET_LENGTH: 65535
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
CHARACTER_SET_NAME: latin1
COLLATION_NAME: latin1_swedish_ci
DTD_IDENTIFIER: text
ROUTINE_TYPE: PROCEDURE
*************************** 4. row ***************************
SPECIFIC_CATALOG: NULL
SPECIFIC_SCHEMA: testbug
SPECIFIC_NAME: test1
ORDINAL_POSITION: 4
PARAMETER_MODE: OUT
PARAMETER_NAME: P4
DATA_TYPE: char
CHARACTER_MAXIMUM_LENGTH: 1
CHARACTER_OCTET_LENGTH: 2
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
CHARACTER_SET_NAME: ucs2
COLLATION_NAME: ucs2_general_ci
DTD_IDENTIFIER: char(1)
ROUTINE_TYPE: PROCEDURE
*************************** 5. row ***************************
SPECIFIC_CATALOG: NULL
SPECIFIC_SCHEMA: testbug
SPECIFIC_NAME: test1
ORDINAL_POSITION: 5
PARAMETER_MODE: IN
PARAMETER_NAME: p5
DATA_TYPE: enum
CHARACTER_MAXIMUM_LENGTH: 6
CHARACTER_OCTET_LENGTH: 6
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
CHARACTER_SET_NAME: latin1
COLLATION_NAME: latin1_swedish_ci
DTD_IDENTIFIER: enum('value1','value2')
ROUTINE_TYPE: PROCEDURE
5 rows in set (0.00 sec)
mysql>
Suggested fix:
Unacceptable (or partial) workaround: use mysql.proc