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

Wrong charset is chosen when opening a pre-4.1 table

    XMLWordPrintable

Details

    Description

      I create a test ctype_utf8_upgrade, with this server options file `ctype_utf8_upgrade-master.opt`:

      --character-set-server=utf8
      

      and this test file `ctype_utf8_upgrade.test`:

      --echo # Test with a saved table from 3.23
      let $MYSQLD_DATADIR= `select @@datadir`;
       
      set @@session.character_set_database="latin1";
      --copy_file std_data/host_old.frm $MYSQLD_DATADIR/test/t1.frm
      --copy_file std_data/host_old.MYD $MYSQLD_DATADIR/test/t1.MYD
      --copy_file std_data/host_old.MYI $MYSQLD_DATADIR/test/t1.MYI
       
      --error ER_GET_ERRNO
      select count(*) from t1;
      check table t1;
      repair table t1;
      repair table t1 use_frm;
      select count(*) from t1;
      check table t1;
      show create table t1;
      drop table t1;
      

      `mtr ctype_utf8_upgrade` produced the following output:

      worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
      # Test with a saved table from 3.23
      set @@session.character_set_database="latin1";
      select count(*) from t1;
      ERROR HY000: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
      check table t1;
      Table	Op	Msg_type	Msg_text
      test.t1	check	Error	Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
      test.t1	check	error	Corrupt
      repair table t1;
      Table	Op	Msg_type	Msg_text
      test.t1	repair	Error	Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM
      test.t1	repair	error	Corrupt
      repair table t1 use_frm;
      Table	Op	Msg_type	Msg_text
      test.t1	repair	status	OK
      select count(*) from t1;
      count(*)
      0
      check table t1;
      Table	Op	Msg_type	Msg_text
      test.t1	check	status	OK
      show create table t1;
      Table	Create Table
      t1	CREATE TABLE `t1` (
        `Host` char(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
        `Db` char(21) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
        `Select_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Insert_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Update_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Delete_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Create_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Drop_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Grant_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `References_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Index_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        `Alter_priv` enum('N','Y') NOT NULL DEFAULT 'N',
        PRIMARY KEY (`Host`,`Db`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Host privileges;  Merged with database privileges'
      drop table t1;
      main.ctype_utf8_upgrade                  [ fail ]  Found warnings/errors in server log file!
              Test ended at 2019-06-03 06:57:44
      line
      2019-06-03  6:57:44 139799115163392 [Warning] './test/t1' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
      2019-06-03  6:57:44 139799115163392 [Warning] './test/t1' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
      2019-06-03  6:57:44 139799115163392 [Warning] './test/t1' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed
      ^ Found warnings in /home/bar/maria-git/server.10.2/mysql-test/var/log/mysqld.1.err
      ok
       
       - saving '/home/bar/maria-git/server.10.2/mysql-test/var/log/main.ctype_utf8_upgrade/' to '/home/bar/maria-git/server.10.2/mysql-test/var/log/main.ctype_utf8_upgrade/'
      --------------------------------------------------------------------------
      The servers were restarted 0 times
      Spent 0.000 of 3 seconds executing testcases
      mysql-test-run: WARNING: Got errors/warnings while running tests, please examine '/home/bar/maria-git/server.10.2/mysql-test/var/log/warnings' for details.
       
      Failure: Failed 1/1 tests, 0.00% were successful.
       
      Failing test(s): main.ctype_utf8_upgrade
      

      This output looks wrong:

      • Notice the table was created with CHARSET=utf8
      • There are warnings in the server log telling that the default character set was multi-byte

      The command:

      set @@session.character_set_database="latin1";
      

      was obviously ignored.

      In this scenario, it is precisely `@@session.character_set_database` which should be respected: this character set value is used when a table is created without the CHARACTER SET table option:

      [DEFAULT] CHARACTER SET/CHARSET

      [DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT is specified, database's default character set will be used.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            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.