Bug #88111 | generated column not accept foreign key for column included | ||
---|---|---|---|
Submitted: | 16 Oct 2017 17:19 | Modified: | 17 May 2019 19:22 |
Reporter: | mohamed atef | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
Version: | 5.7, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[16 Oct 2017 17:19]
mohamed atef
[16 Oct 2017 17:39]
mohamed atef
correction table with generated column not accept foreign key for one of the columns assigned in generated column
[19 Oct 2017 14:30]
MySQL Verification Team
Hi! I have been able to repeat the error that you get. Also, I can not find anything in our documentation that would prohibit creation of the foreign key on the non-generated column(s). However, it still does not mean that this bug will not turn out to be a documentation bug. But, so far, this is verified as a code bug.
[20 Oct 2017 5:36]
MySQL Verification Team
Neater testcase: ---------------- set foreign_key_checks=0; drop database if exists d; create database if not exists d; use d; drop table if exists t,s; set foreign_key_checks=1; create table t(a int primary key)engine=innodb; create table s( a int, b int generated always as (a) stored, key(a,b), constraint foreign key(a) references t(a) on update cascade )engine=innodb; show errors; select version();
[20 Oct 2017 11:30]
MySQL Verification Team
There is simply no need for any explanation. It is a bug in the code and this is why this bug is verified. That also means that one day it will get its schedule for fixing.
[18 Dec 2017 15:32]
Jon Stephens
Fixed in mysqldoc rev 55197. Closed.
[18 Dec 2017 16:00]
mohamed atef
Why this bug is converted to documentation bug When it available in server 8 Where you can add foreign key on column included in generated column
[18 Dec 2017 17:21]
mohamed atef
If ihad column3 is generated as concat(column1,column2) What the difference If column2 updated by query Or updated by foreign key
[19 Dec 2017 11:59]
Jon Stephens
This bug needs to be re-verified. Returning to Open/Server and reverting the previous change in the docs until this is resolved.
[19 Dec 2017 12:16]
mohamed atef
If ihad column3 is generated as concat(column1,column2) and column2 have referenced to column4 by foreign key in other table with on update cascade What the difference If column2 updated by query update Or updated by foreign key when i update column4 in other table ??????????????????????????????????????????????????????????????
[19 Dec 2017 15:55]
mohamed atef
what is this bug verified as a code bug. by Sinisa Milivojevic at [19 Oct 14:30]. { [19 Oct 14:30] Sinisa Milivojevic Hi! I have been able to repeat the error that you get. Also, I can not find anything in our documentation that would prohibit creation of the foreign key on the non-generated column(s). However, it still does not mean that this bug will not turn out to be a documentation bug. But, so far, this is verified as a code bug. [19 Oct 22:17] mohamed atef Sorry I didn’t get you I had read documentation of generated column And i get that not allowed to create foriegn key in generated column With on update cascade or on update set null As generated column don’t accept values by insert or update by query As its value is generated and changed according to the value of The included columns But here In my example I tried to create foreign key in one of the columns included in the generated columns So why it didn’t accept foreign key with on update cascade While i can update the the non generated column by query Thanks Waiting for your clarification [20 Oct 11:30] Sinisa Milivojevic There is simply no need for any explanation. It is a bug in the code and this is why this bug is verified. That also means that one day it will get its schedule for fixing. } then converted to doc. bug by Jon Stephens then not a bug by Jon Stephens then verified by Jon Stephens then closed by Jon Stephens { [18 Dec 15:32] Jon Stephens Fixed in mysqldoc rev 55197. Closed. } the i reopened it to ask you one question { If ihad column3 is generated as concat(column1,column2) and column2 have referenced to column4 by foreign key in other table with on update cascade What the difference If column2 updated by query update Or updated by foreign key when column4 in the other table is updated ?????????????????????????????????????????????????????????????? } then verified by Godofredo Miguel Solorazano then reopened by Godofredo Miguel Solorazano what happen is it so difficult for mysql company to answer my question ??????
[20 Dec 2017 13:23]
MySQL Verification Team
I have run this simplified test case: ===================================================== set foreign_key_checks=0; drop database if exists d; create database if not exists d; use d; drop table if exists t,s; set foreign_key_checks=1; create table t(a int primary key)engine=innodb; create table s( a int, b int generated always as (a) stored, key(a,b), constraint foreign key(a) references t(a) on update cascade )engine=innodb; show errors; select version(); drop table if exists t,s; drop database if exists d; ================================================ And I have got this output : ERROR 1215 (HY000) at line 9: Cannot add foreign key constraint Hence, this bug is present in 5.7 and hence, once again, it is verified as a code bug. Will check 8.0 once when I find some time. Thank you for your input.
[22 Dec 2017 17:02]
MySQL Verification Team
I hereby confirm that this bug is also present in 8.0.4. I get the same error message.
[12 Feb 2019 13:16]
Simon Banaan
Is there any progress on this bug? Or a way I can follow the progress?
[18 Apr 2019 15:51]
Dmitry Lenev
Posted by developer: Hello! Let me clarify the situation. The creation of foreign keys with CASCADE/SET NULL/SET DEFAULT as ON UPDATE or ON DELETE referential actions on a base column of stored generated column is not allowed (in both recent 5.7 and 8.0 versions of server). This is due to fix for bug#22687023 / #80304 "GENERATED COLUMNS DON'T WORK WITH FOREIGN KEY ACTIONS" which was introduced in MySQL 5.7.14. This fact is even mentioned in "Changes in MySQL 5.7.14 (2016-07-29, General Availability)" page of our manual (https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-14.html): " * InnoDB: InnoDB did not block the creation of a foreign key constraint with referential actions on the base column of a stored generated column. (Bug #22687023, Bug #80304) " So the observed behavior is not a code bug. However, I agree that it is a documentation issue since this fact is not mentioned clearly at such pages as: "13.1.18.8 CREATE TABLE and Generated Columns" (https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) or "14.6.1.5 InnoDB and FOREIGN KEY Constraints" (https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html#innodb-foreign...) where other limitations related to generated columns and foreign keys are listed. Because of this I am changing category of this bug to Documentation. I also think that, if filed, request to relax this limitation would be a valid feature request. It is also worth noting that similar limitation on base columns of indexed virtual colums was removed in the same MySQL 5.7.14 see (Bug #22469130, Bug #79772). My guess for the reason, why the limitation for base columns of stored generated columns was not relaxed at the same time, is that stored generated columns are mostly processed by SQL-layer and not by storage engine. So SE expects their values be evaluated for them by SQL-layer. While for indexed virtual columns value evaluation also happens on SE level, i.e. on the same level where handling of referential actions happens.
[18 Apr 2019 20:18]
mohamed atef
Dmitry Lenev sorry it is code bug because i tried to create foreign key on non generated column if i have three column col1 col2 col3 col3 is generated as concat(col1,col2) case 1 if i update col2 or col1 by update statement col3 value will change case 2 if i create foreign key on col1 on update cascade and the referred column value changed col1 value will change, so col3 value will change why it accept the first case and don`t accept the second case what is the difference you have problem in your code you should respect our mind
[23 Apr 2019 13:05]
Dmitry Lenev
Posted by developer: Hello! As I have tried to explain above there is a difference in how we implement processing of direct updates to base columns of generated stored columns and updates to the same columns which are caused by cascading actions. In the former case, update is mostly handled by SQL-layer. Specifically, new value for generated column is produced by SQL-layer as well. So storage engine (InnoDB) gets updated value for generated stored column from SQL-layer, similarly to how it happens for non-generated column. In the latter case, cascading actions is initiated by the storage engine after it gets information that parent row needs to be updated. Since, storage engine is not aware about relation between base column of generated stored column and generated stored column itself, and doesn't handle generated stored column re-evaluation it can't update generated column to proper value in this case. Hence, we block scenarios in which such generated stored column re-evaluation can be caused by cascading actions of foreign keys.
[17 May 2019 19:22]
Daniel Price
Posted by developer: The referenced content was updated with the following information: "A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions." "CREATE TABLE and Generated Columns" (https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) "InnoDB and FOREIGN KEY Constraints" (https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html#i nnodb-foreign-key-generated-columns) Changes should appear online soon. Thank you for the bug report.