[#MDEV-30555] The server does not detect changes in NULL-ability of system table columns

If I change NULL-ability of the column mysql.proc.definer, it's still possible to create stored routins, however it's not possible to execute them.

This MTR test demonstrates the problem. The test is for 10.6. To reproduce the problem in other versions, the data type varchar(384) in the ALTER statement should be modified according to the expected data type in this version.

# Modify the column 'definer' mostly preserving the data type,

# changing only NOT NULL DEFAULT '' to DEFAULT NULL.

ALTER TABLE mysql.proc MODIFY definer

  varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL;

# The procedure gets created without problems.

# However, it's not later possible to execute this procedure.

# The CREATE should rather fail with ER_CANNOT_LOAD_FROM_TABLE_V2.

DELIMITER $$;

CREATE PROCEDURE p1()

BEGIN

  SELECT 'p1';

END;

$$

DELIMITER ;$$

# Notice, its definer is set to NULL instead of 'root@localhost'.

SELECT definer FROM mysql.proc WHERE db='test' AND name='p1';

# The CALL statement with a very confusing error:

# query 'CALL p1' failed: ER_NO_SUCH_USER (1449):

#   The user specified as a definer (''@'') does not exist

CALL p1;

# Clean-up

DROP PROCEDURE p1;

ALTER TABLE mysql.proc MODIFY definer

  varchar(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

 store_failed= store_failed ||

      table->field[MYSQL_PROC_FIELD_DEFINER]->

        store(definer, system_charset_info);

there is no a Field::set_notnull() call. After changing the column NULL-ability, the value of the Field stays NULL.

As the code RELIES on the fact that this Field is not NULL-able, it seems Table_check_intact::check() should catch not only data type difference, but also NULL-ability difference.

The C++ structure TABLE_FIELD_TYPE which stores definitions of the system table columns could be extended by a new field responsible for NULL-ability:

[MDEV-30555] The server does not detect changes in NULL-ability of system table columns Created: 2023-02-03 Updated: 2023-02-03
Status:	Open
Project:	MariaDB Server
Component/s:	Authentication and Privilege System, Data types
Affects Version/s:	10.6
Fix Version/s:	10.6

[MDEV-30555] The server does not detect changes in NULL-ability of system table columns Created: 2023-02-03 Updated: 2023-02-03