[MDEV-23170] Characterset missing in routine definition in information_schema.routines Created: 2020-07-14  Updated: 2020-08-05  Resolved: 2020-08-05

Status: Closed
Project: MariaDB Server
Component/s: Information Schema, Stored routines
Affects Version/s: 10.1, 10.5.4, 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Konstantin Schmidt Assignee: Alexander Barkov
Resolution: Won't Fix Votes: 0
Labels: upstream
Environment:

Windows 10 1909
intel i7

Issue Links:
Relates
relates to MDEV-23189 Double backslashes reduced to one in ... Open

 Description   

Characterset for a literal is missing in the routine's definition in information_schema.routines.

1. create routine with explicit characterset _utf8mb4 for default varchar value

CREATE PROCEDURE `testprocedure`()
 
LANGUAGE SQL
 
NOT DETERMINISTIC
 
CONTAINS SQL
 
SQL SECURITY DEFINER
 
COMMENT ''
 
BEGIN
 
DECLARE cLastPushKey varchar(20) DEFAULT _utf8mb4 'ADJECTIVES' COLLATE utf8mb4_german2_ci;
 
END

2. select definition record in information_schema.ROUTINES

SELECT * FROM information_schema.ROUTINES
 
WHERE routine_name = 'testprocedure'

Column ROUTINE_DEFINITION shows routine definition as 

BEGIN
 
	DECLARE cLastPushKey varchar(20) DEFAULT  'ADJECTIVES' COLLATE utf8mb4_german2_ci;
 
END

Characterset _utf8mb4 is missing.

This makes it impossible to write a custom export for specific routines.
No workaround found so far.

I'm working with 5.4.11
10.5.4 shows same behavior.

 Comments   
Comment by Elena Stepanova [ 2020-08-04 ]

mysql.proc table has two columns for a procedure body, body and body_utf8. For the example above, body contains the charset introducer, while body_utf8 doesn't:

                  db: test
 
                name: testprocedure
 
                type: PROCEDURE
 
       specific_name: testprocedure
 
            language: SQL
 
     sql_data_access: CONTAINS_SQL
 
    is_deterministic: NO
 
       security_type: DEFINER
 
          param_list: 
             returns: 
                body: BEGIN
 
DECLARE cLastPushKey varchar(20) DEFAULT _utf8mb4 'ADJECTIVES' COLLATE utf8mb4_german2_ci;
 
END
 
             definer: root@localhost
 
             created: 2020-08-05 01:54:35
 
            modified: 2020-08-05 01:54:35
 
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
             comment: 
character_set_client: latin1
 
collation_connection: latin1_swedish_ci
 
        db_collation: latin1_swedish_ci
 
           body_utf8: BEGIN
 
DECLARE cLastPushKey varchar(20) DEFAULT  'ADJECTIVES' COLLATE utf8mb4_german2_ci;
 
END
 
           aggregate: NONE

Possibly the information schema presents the body_utf8 variant. Assigning to bar to determine whether it's expected.

Comment by Alexander Barkov [ 2020-08-05 ]

These columns:

  • information_schema.routines.ROUTINE_DEFINITION
  • mysql.proc.body_utf8

are not intended for export. They exist only for information purposes.

Please use mysql.proc.body instead.

This behaviour is intentional (but not perfect).

I agree that this behaviour is somewhat inconvenient. It would be better if the mentioned columns preserved the character set introducer as well. But we cannot fix it easily at this point. The problem is that introduces in MySQL/MariaDB unfortunately work in a different way than it is supposed by the SQL standard. Adding introducers in the output would generate a correct statement if MariaDB supported introducers in the standard way. But adding introducers with the current implementations would make the output only misleading.

Generated at Thu Feb 08 09:20:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.