Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14882

mysql_upgrade performs unnecessary conversions back and forth

    XMLWordPrintable

Details

    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

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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