Search code examples
mysqldatabase-migration

On ALTER TABLE, MYSQL complains about DATA TRUNCATED (Warning: 1265)

I have a table with some data and the following fields:

visits               double               null,
page_per_visits      double               null,
valid_domain         tinyint(1) default 1 not null,
page_hits            int                  null,

Now I am trying to get rid of NULLs by running:

ALTER TABLE domain_similar_web_v2 
   CHANGE visits visits DOUBLE PRECISION DEFAULT 0 NOT NULL, 
   CHANGE page_per_visits page_per_visits DOUBLE PRECISION DEFAULT 0 NOT NULL, 
   CHANGE page_hits page_hits DOUBLE PRECISION DEFAULT 0 NOT NULL

And get the following error:

Warning: 1265 Data truncated for column page_hits after ALTER TABLE at row 1

I tried different datatypes for page_hits (BIGINT, FLOAT, DOUBLE PRECISION - result is the same).

What could be wrong and how to fix the table?

PS. local server under MariaDB 10.3

Solution

  • NULLS have to be replaced with '0's before the ALTER (thanks to nbk)