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

"CONVERT TO CHARACTER SET" has no effect if table has no text columns

    XMLWordPrintable

    Details

      Description

      ALTER TABLE table CONVERT TO CHARACTER SET ... has no effect on tables without any textual columns (TEXT, VARCHAR). INFORMATION_SCHEMA still reports the old character set after performing the conversion. If a textual column is added afterwards, it's still created with the old character set and collation. Here's an SQL script to reproduce the issue:

      create database collation_test;
      use collation_test;
       
      drop table if exists `foobar`;
       
      CREATE TABLE `foobar` (
        `foobar_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `version` bigint(20) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
       
      select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
       
      ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
       
      select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
       
      alter table foobar add column txt VARCHAR(255);
       
      select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
       
      ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      

      And the output from MariaDB:

      MariaDB [(none)]> create database collation_test;
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [(none)]> use collation_test;
      Database changed
      MariaDB [collation_test]> 
      MariaDB [collation_test]> drop table if exists `foobar`;
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> CREATE TABLE `foobar` (
          ->   `foobar_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          ->   `version` bigint(20) NOT NULL
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
      +------------+-------------------+
      | table_name | table_collation   |
      +------------+-------------------+
      | foobar     | latin1_swedish_ci |
      +------------+-------------------+
      1 row in set (0.00 sec)
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [collation_test]> /* Next statement should print utf8_unicode_ci but it's still latin1_swedish_ci */
      MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
      +------------+-------------------+
      | table_name | table_collation   |
      +------------+-------------------+
      | foobar     | latin1_swedish_ci |
      +------------+-------------------+
      1 row in set (0.00 sec)
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> alter table foobar add column txt VARCHAR(255);
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> select table_name, table_collation from information_schema.tables where table_schema = 'collation_test' and table_name = 'foobar';
      +------------+-------------------+
      | table_name | table_collation   |
      +------------+-------------------+
      | foobar     | latin1_swedish_ci |
      +------------+-------------------+
      1 row in set (0.00 sec)
       
      MariaDB [collation_test]> 
      MariaDB [collation_test]> ALTER TABLE foobar CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      Query OK, 0 rows affected (0.02 sec)               
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [collation_test]> 
      

      Just FYI, MySQL 5.6 and newer behaves correctly (it runs on one of our CI servers).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              reliefpfeiler Andreas Ahlenstorf
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.