MySQL Bugs: #112131: Failed to insert a row into the "film" table

Bug #112131	Failed to insert a row into the "film" table
Submitted:	22 Aug 2023 8:53	Modified:	6 Nov 2023 9:57
Reporter:	Maksim Gramin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sakila

Description:
While exploring the Sakila schema using a fake data generator, I discovered an interesting effect: an error occurs when inserting some rows into the "film" table.

How to repeat:
Suppose we want to insert a new film into the `film` table with the maximum allowed value for the “film_id” column. The “film_id” column has a data type of “smallint unsigned” with a maximum value of 65535:

insert into sakila.film
(film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating)
values
(65535, 'Oppenheimer', 'The story of American scientist, J. Robert Oppenheimer', '2023', 1, 1, 3, 4.99, 300, 19.99, 'R');

When trying to insert a row into the "film" table, we receive an error message stating:

"Data truncation: Out of range value for column 'film_id' at row 1." 

This error occurs because the "ins_film" trigger attempts to insert a portion of the row, including the "film_id" column, into another table ("film_text" table). However, in the "film_text" table, the "film_id" column has "smallint" data type, which allows for a maximum value of 32767.

Suggested fix:
I suggest modifying the "film_text" table, perhaps in the following way:

alter table sakila.film_text modify column film_id smallint unsigned not null;

Hello Maksim Gramin,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Thank you Maksim for the bug report, this is now fixed in the source.

Thank you.
Please let me know, will this bug be mentioned on the "Sakila Change History" page (https://dev.mysql.com/doc/sakila/en/sakila-news.html)?