[MDEV-14882] mysql_upgrade performs unnecessary conversions back and forth - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.0.36, 10.1.35, 10.2.17, 10.3.9
Component/s: Scripts & Clients
Labels:
None

Description

Note: the bug report uses 'comment' column as a known example, but possibly there are others.

On all currently supported versions, table mysql.proc is initially created with column comment of type text:

       Table: proc

Create Table: CREATE TABLE `proc` (

  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',

  `name` char(64) NOT NULL DEFAULT '',

  `type` enum('FUNCTION','PROCEDURE') 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` char(77) CHARACTER SET utf8 COLLATE utf8_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') NOT NULL DEFAULT '',

  `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,

  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

  `body_utf8` longblob,

  PRIMARY KEY (`db`,`name`,`type`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'

However, when mysql_upgrade is run, it converts the column into char(64)

 ALTER TABLE proc  MODIFY db

                          char(64) collate utf8_bin DEFAULT '' NOT NULL,

                   MODIFY definer

                          char(", @definer_name_length, ") collate utf8_bin DEFAULT '' NOT NULL,

                   MODIFY comment

                          char(64) collate utf8_bin DEFAULT '' NOT NULL

")

and then back to text

ALTER TABLE proc MODIFY comment

                         text collate utf8_bin NOT NULL

Naturally, if the column contained a comment longer than 64 symbols, it gets truncated.

It also affects Debian installation, because mysql_upgrade runs there in background, and if other packages which need to use mysql.proc (e.g. Spider) are being installed during this time, they can't create necessary procedures and fail to get configured properly.

Attachments

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2018-01-06 23:48

Elena Stepanova made changes - 2018-01-06 23:48

Field	Original Value	New Value
Assignee	Elena Stepanova [ elenst ]	Sergei Golubchik [ serg ]

Elena Stepanova made changes - 2018-01-06 23:48

Description

_Note: the bug report uses {{'comment'}} column as a known example, but possibly there are others._

On all currently supported versions, table {{mysql.proc}} is initially created with column {{comment}} of type {{text}}:

{code:sql|highlight=18}
       Table: proc
Create Table: CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') 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` char(77) CHARACTER SET utf8 COLLATE utf8_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') NOT NULL DEFAULT '',
  `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
{code}

However, when {{mysql_upgrade}} is run, it converts the column into {{char(64)}}
{code:sql}
ALTER TABLE proc CONVERT TO CHARACTER SET utf8
                    8 Query SET @alter_statement = CONCAT("
ALTER TABLE proc MODIFY db
                          char(64) collate utf8_bin DEFAULT '' NOT NULL,
                   MODIFY definer
                          char(", @definer_name_length, ") collate utf8_bin DEFAULT '' NOT NULL,
                   MODIFY comment
                          char(64) collate utf8_bin DEFAULT '' NOT NULL
")
{code}

and then back to text
{code:sql}
ALTER TABLE proc MODIFY comment
                         text collate utf8_bin NOT NULL
{code}

Naturally, if the column contained a comment longer than 64 symbols, it gets truncated.

It also affects Debian installation, because {{mysql_upgrade}} runs there in background, and if other packages which need to use {{mysql.proc}} (e.g. Spider) are being installed during this time, they can't create necessary procedures and fail to get configured properly.

_Note: the bug report uses {{'comment'}} column as a known example, but possibly there are others._

On all currently supported versions, table {{mysql.proc}} is initially created with column {{comment}} of type {{text}}:

{code:sql|highlight=18}
       Table: proc
Create Table: CREATE TABLE `proc` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `type` enum('FUNCTION','PROCEDURE') 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` char(77) CHARACTER SET utf8 COLLATE utf8_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') NOT NULL DEFAULT '',
  `comment` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`,`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Stored Procedures'
{code}

However, when {{mysql_upgrade}} is run, it converts the column into {{char(64)}}
{code:sql}
ALTER TABLE proc MODIFY db
                          char(64) collate utf8_bin DEFAULT '' NOT NULL,
                   MODIFY definer
                          char(", @definer_name_length, ") collate utf8_bin DEFAULT '' NOT NULL,
                   MODIFY comment
                          char(64) collate utf8_bin DEFAULT '' NOT NULL
")
{code}

and then back to text
{code:sql}
ALTER TABLE proc MODIFY comment
                         text collate utf8_bin NOT NULL
{code}

Naturally, if the column contained a comment longer than 64 symbols, it gets truncated.

It also affects Debian installation, because {{mysql_upgrade}} runs there in background, and if other packages which need to use {{mysql.proc}} (e.g. Spider) are being installed during this time, they can't create necessary procedures and fail to get configured properly.

Elena Stepanova made changes - 2018-01-06 23:50

Fix Version/s		5.5 [ 15800 ]
Fix Version/s		10.0 [ 16000 ]

Sergei Golubchik made changes - 2018-07-14 11:41

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2018-07-14 11:42

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2018-07-14 11:46

Fix Version/s

5.5 [ 15800 ]

Sergei Golubchik made changes - 2018-07-19 10:35

Fix Version/s		10.0.36 [ 22916 ]
Fix Version/s		10.1.35 [ 23116 ]
Fix Version/s		10.2.17 [ 23111 ]
Fix Version/s		10.3.9 [ 23114 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 84758 ]

MariaDB v4 [ 153519 ]

People

Assignee:: Sergei Golubchik

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-01-06 23:48

Updated:: 2018-07-19 10:35

Resolved:: 2018-07-19 10:35

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration