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

Cannot disable FOREIGN_KEY_CHECKS anymore for converting character set

    XMLWordPrintable

Details

    Description

      We had in our migration some tables with a wrong character set created (we didn't set explicit which character set). So we added a while ago a migration to fix this.

      For this we disable first the foreign key checks and then run the ALTER TABLE. It seems now that since the last patch versions this does not work anymore.

      Simplified test case:

      CREATE TABLE `t1` (
        `hash` varchar(32) NOT NULL COLLATE 'utf8_unicode_ci',
        `title` varchar(100) DEFAULT NULL COLLATE 'utf8_unicode_ci',
        PRIMARY KEY (`hash`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;	
       
      CREATE TABLE `t2` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `t1_hash` varchar(32) NOT NULL  COLLATE 'utf8_unicode_ci',
        `name` varchar(100) DEFAULT NULL  COLLATE 'utf8_unicode_ci',
        PRIMARY KEY (`id`),
        KEY `FK_t1_hash` (`t1_hash`),
        CONSTRAINT `FK_t1_hash` FOREIGN KEY (`t1_hash`) REFERENCES `t1` (`hash`) ON DELETE CASCADE ON UPDATE NO ACTION
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
       
       
      SET FOREIGN_KEY_CHECKS = 0;
      ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
      ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
      SET FOREIGN_KEY_CHECKS = 1;
      

      error is:

      SET FOREIGN_KEY_CHECKS = 0
      Query executed OK, 0 rows affected. (0.000 s) Edit
       
      ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      Error in query (1833): Cannot change column 'hash': used in a foreign key constraint 'FK_t1_hash' of table 'test.t2'
       
      ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
      Error in query (1832): Cannot change column 't1_hash': used in a foreign key constraint 'FK_t1_hash'
       
      SET FOREIGN_KEY_CHECKS = 1
      Query executed OK, 0 rows affected. (0.000 s) Edit, Warnings
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              shyim Soner Sayakci
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.