Change the information_Schema.routine altered or created date in MySQL / MariaDB

Leave a reply

SQL Error [1044] [42000]: (conn=11) Access denied for user 'root'@'localhost' to database 'information_schema'

In MySQL, we have a system table which has information_schema.routines table which keeps a record of procedures when created or updated. In MySQL, we call routines also as procedures, and functions in these databases.

We can change the routine table data by altering another table because routine is only represented / View created on those tables.

We get the error when we try to update the date on the routine table:

update information_schema.routines set CREATED = '2022-12-17 17:57:59.000',CREATED = '2022-12-17 17:57:59.000' 
where name = 'pn' and db = 'new'

Error:
SQL Error [1044] [42000]: (conn=11) Access denied for user 'root'@'localhost' to database 'information_schema'

Note: In this information_Schema.routines is a view.

Lets change the date for this information_Schema view as follows:

  1. Check the date for any specific procedure you want to modified:
select ROUTINE_NAME , routine_schema,CREATED ,CREATED  from information_schema.ROUTINES r 
where ROUTINE_NAME = 'pn' and ROUTINE_SCHEMA = 'new'

ROUTINE_NAME|routine_schema|CREATED                |LAST_ALTERED           |
------------+--------------+-----------------------+-----------------------+
pn          |new           |2022-10-17 17:57:59.000|2022-10-17 17:57:59.000|

2. Check the base table from which it fetch the data i.e mysql.proc

select db,name,created,modified from mysql.proc p where name = 'pn' and db = 'new'

db |name|created                |modified               |
---+----+-----------------------+-----------------------+
new|pn  |2022-10-17 17:57:59.000|2022-10-17 17:57:59.000|

3. update the base table i.e mysql.proc to change the data value:

update mysql.proc set modified = '2022-12-17 17:57:59.000',created = '2022-12-17 17:57:59.000'  where name = 'pn' and db = 'new'

4. Verify the results:

select db,name,created,modified from mysql.proc p where name = 'pn' and db = 'new'

db |name|created                |modified               |
---+----+-----------------------+-----------------------+
new|pn  |2022-12-17 17:57:59.000|2022-12-17 17:57:59.000|

select ROUTINE_NAME , routine_schema,CREATED ,LAST_ALTERED  from information_schema.ROUTINES r where ROUTINE_NAME = 'pn' and ROUTINE_SCHEMA = 'new'

ROUTINE_NAME|routine_schema|CREATED                |LAST_ALTERED           |
------------+--------------+-----------------------+-----------------------+
pn          |new           |2022-12-17 17:57:59.000|2022-12-17 17:57:59.000|

Note: you need to change both dates created and modified otherwise created date pick current date as shown below. If you change only modified date then create date pick current_timestamp as its default value.

update mysql.proc set modified = '2022-11-17 17:57:59.000'
where name = 'pn' and db = 'new'

select ROUTINE_NAME , routine_schema,CREATED ,LAST_ALTERED  from information_schema.ROUTINES r 
where ROUTINE_NAME = 'pn' and ROUTINE_SCHEMA = 'new'

ROUTINE_NAME|routine_schema|CREATED                |LAST_ALTERED           |
------------+--------------+-----------------------+-----------------------+
pn          |new           |2023-09-12 20:16:53.000|2022-11-17 17:57:59.000|

Because its ddl has created like to pick current_timestamp:

-- mysql.proc definition

CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL,
  `specific_name` char(64) NOT NULL DEFAULT '',
  `language` enum('SQL') NOT NULL DEFAULT 'SQL',
  `sql_data_access` enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') NOT NULL DEFAULT 'CONTAINS_SQL',
  `is_deterministic` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `security_type` enum('INVOKER','DEFINER') NOT NULL DEFAULT 'DEFINER',
  `param_list` blob NOT NULL,
  `returns` longblob NOT NULL,
  `body` longblob NOT NULL,
  `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '',
  `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
  `body_utf8` longblob DEFAULT NULL,
  `aggregate` enum('NONE','GROUP') NOT NULL DEFAULT 'NONE',
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=Aria DEFAULT CHARSET=utf8mb3 PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='Stored Procedures';

Leave a Reply