Bug #80304 generated columns don't work with foreign key actions

Submitted:	8 Feb 2016 15:32	Modified:	16 May 2016 14:55
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7, 5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
One testcase, by Peter Gulutzan, can be found in
http://ocelot.ca/blog/blog/2016/02/04/generated-columns-in-mariadb-and-mysql/
Search there for
"MySQL lacks some restrictions about foreign keys, eh? That could lead to interesting results. I tried this sequence of statements:" then comes the testcase.

How to repeat:
see above.

Suggested fix:
When fixing, make sure that all foreign key actions work (ON UPDATE/DELETE, CASCADE/RESTRICT/SET NULL/NO ACTION), with stored and virtual generated columns.

Hello Guilhem,

Thank you for the report.

Thanks,
Umesh

Also, when fixing, test a virtual indexed column; I imagine that the index may not be updated, so CHECK TABLE may report a corruption.

Posted by developer:
 
Fixed as of the upcoming 5.7.14 release, and here's the changelog entry:

InnoDB did not block the creation of a foreign key constraint with
referential actions on the base column of a generated stored column.

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.