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

[HY000][1553] Changing name of primary key column with foreign key constraint fails.

Details

    Description

      Changing the name of a primary key column with a foreign key constraint fails with the following error since 10.4.13:

      [HY000][1553] (conn=17) Cannot drop index 'PRIMARY': needed in a foreign key constraint

      create schema test collate utf8_unicode_ci;
      		 
      create table test.input_type
      		 
      (
      		 
          input_type_id int(11) unsigned auto_increment
      		 
              primary key
      		 
      );
      		 
       
      		 
      create table test.input
      		 
      (
      		 
          input_id int unsigned auto_increment
      		 
              primary key,
      		 
          input_type_id int unsigned not null,
      		 
          constraint ibfk
      		 
              foreign key (input_type_id) references test.input_type (input_type_id)
      		 
      );
      		 
       
      		 
      create index type_id
      		 
          on test.input (input_type_id);
      		 
       
      		 
      alter table test.input_type change input_type_id id int(11) unsigned auto_increment;

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23950 Renaming a column that is used as a foreign key throws an error

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            karly Alexander Karlstad added a comment -

            Are there, by the way, any known bug reports over at MySQL for the same problems? I tested and had the same issue in MySQL 8, but not in 5.7, so I suspect they have the same issue as well

            karly Alexander Karlstad added a comment - Are there, by the way, any known bug reports over at MySQL for the same problems? I tested and had the same issue in MySQL 8, but not in 5.7, so I suspect they have the same issue as well
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment -

            By running git-bisect, I founded that the bug is introduced by df07e00a810890f6f6eb1334c76ee22133750777.

            The following diff is probably the point. old_part.length and new_part.length are equal but old_cs->mbmaxlen and new_cs->mbmaxlen are not in the Alice's test case.

            -  if (old_part.length != new_part.length)
            		 
            +  if (old_part.length / old_cs->mbmaxlen != new_part.length / new_cs->mbmaxlen)


            (gdb) p old_part.length
            		 
            $1 = 4
            		 
            (gdb) p new_part.length
            		 
            $2 = 4
            		 
            (gdb) p old_cs->mbmaxlen
            		 
            $3 = 1
            		 
            (gdb) p  new_cs->mbmaxlen
            		 
            $4 = 3

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - By running git-bisect, I founded that the bug is introduced by df07e00a810890f6f6eb1334c76ee22133750777 . The following diff is probably the point. old_part.length and new_part.length are equal but old_cs->mbmaxlen and new_cs->mbmaxlen are not in the Alice's test case. - if (old_part.length != new_part.length) + if (old_part.length / old_cs->mbmaxlen != new_part.length / new_cs->mbmaxlen) (gdb) p old_part.length $1 = 4 (gdb) p new_part.length $2 = 4 (gdb) p old_cs->mbmaxlen $3 = 1 (gdb) p new_cs->mbmaxlen $4 = 3
            marko Marko Mäkelä added a comment -

            nayuta-yanagisawa, thank you very much. It looks like the CHARACTER SET is wrongly affecting the check of the INT column.

            bar, can you suggest how we could refine that code to determine the proper length of the column? The intention of the change was to allow an instant conversion from utf8mb3 to utf8mb4. Character sets are not applicable to columns that do not store character data, yet the information appears to be stored for them. How can we detect that old_part and new_part do not store character data, and thus we should ignore any change of character set?

            marko Marko Mäkelä added a comment - nayuta-yanagisawa , thank you very much. It looks like the CHARACTER SET is wrongly affecting the check of the INT column. bar , can you suggest how we could refine that code to determine the proper length of the column? The intention of the change was to allow an instant conversion from utf8mb3 to utf8mb4 . Character sets are not applicable to columns that do not store character data, yet the information appears to be stored for them. How can we detect that old_part and new_part do not store character data, and thus we should ignore any change of character set?
            bar Alexander Barkov added a comment - - edited

            sanja, can you please review?

            The patch for 10.5

            The patch for 10.4:

            bar Alexander Barkov added a comment - - edited sanja , can you please review? The patch for 10.5 Branch: https://github.com/MariaDB/server/tree/bb-10.5-bar-MDEV-22775 A direct link to the patch: https://github.com/MariaDB/server/commit/ff1ee3fdd92ed5d91fa1641d35c2774fdd3fca13 The patch for 10.4: Branch: https://github.com/MariaDB/server/tree/bb-10.4-bar-MDEV-22775 A direct link to the patch: https://github.com/MariaDB/server/commit/bbd216ec520c0c6ee160602062c6589fddd1d7c6
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            People

              bar Alexander Barkov
              mac89 Maarten Manders
              Votes:
              6 Vote for this issue
              Watchers:
              13 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.