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