Unable to change table column type in MySQL 5.7

Question

I have this table in MySQL 5.7 database and rest of SQL statements:

create table m_workflow
    (
        id bigint(10) auto_increment primary key,
        course bigint(10) default 0 not null,
        name varchar(255) default '' not null,
        intro longtext not null,
        introformat smallint(4) default 0 not null,
        recommendationstitle varchar(255) null,
        recommendintro longtext null,
        recommendintroformat bigint(10) default 1 null,
        timeopen bigint(10) default 0 not null,
        timeclose bigint(10) default 0 not null,
        timelimit bigint(10) default 0 not null,
        timecreated bigint(10) default 0 not null,
        timemodified bigint(10) default 0 not null
    )
    comment 'The settings for each workflow.';

    create index m_work_cou_ix on m_workflow (course);

    INSERT INTO m_workflow (course, name, intro, introformat) VALUES (1, 'aaa', 'aaaa', 1);
    INSERT INTO m_workflow (course, name, intro, introformat) VALUES (1, 'aaa', 'aaaa', 1);

    ALTER TABLE m_workflow MODIFY COLUMN recommendationstitle LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL after introformat;

The poblem here is that it fails on ALTER column statement with error:

Data truncated for column 'recommendationstitle' at row 1

If I add some text into recommendationstitle fields than conversion goes OK.

Is this to be expected or am I doing something wrong?

Here is the SQL fiddle - http://www.sqlfiddle.com/#!9/10934c/2

Answer 1

You are trying to modify it from NULL to NOT NULL. Currently existing records with NULL values causing the issue.