0

I know truncating data means a column has less capacity.

But in my case, all I'm doing is to change the nullability of a numeric column, that has no value at all.

I'm using MariaDB + Adminer. I have not executed an insert query. I just used Adminer to change the nullability of a column named Order.

How is this error even possible? What does it mean in this context?

Hossein Fallah
  • 1,859
  • 2
  • 18
  • 44
  • 1
    please read up on https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query and produce a [mre] – nbk Feb 05 '22 at 13:53
  • 1
    What is the column defenition before the modification? Are you changing it to NULL or to NOT NULL? –  Feb 05 '22 at 13:59
  • What is the column defenition after the modification? – (what are you actually doing) ? (BTW: the proper spelling is 'definition') – Luuk Feb 05 '22 at 13:59
  • @Kendle, your comment helped me a lot. I can't believe that MariaDB gives us such a stupid error when we try to change a nulalbe column to not null, and when all of the values in that column are null. SQL Server at least gives a better message by saying that **null is not permitted**. This was such a misleading message. Where can I report it to MariaDB programmers? – Hossein Fallah Feb 05 '22 at 16:09
  • Glad it helped. MariaDB bug reporting at https://jira.mariadb.org. Please give a one up on the comment if it helped! –  Feb 05 '22 at 16:13

1 Answers1

2

This error usually happens if a column type will be changed and existing values cannot be converted without truncation:

  • e.g Changing INT to TINYINT (and column has values > 0xFF)
  • changing to NOT NULL if column has NULL values
  • shrinking of CHAR/BLOB
  • ..

If you're ok with any truncation (which might end up in loss of data and/or integrity) use ALTER IGNORE TABLE

Georg Richter
  • 5,970
  • 2
  • 9
  • 15
  • I fail to understand how changing `NULL` to `NOT NULL` is related to truncating data. There is no data. – Hossein Fallah Feb 07 '22 at 20:18
  • Truncation means the value cannot fit into changed column and you would loose information. NULL means, that there is no value available, if you change the column to NOT NULL the column would have a value. 0 isn't NULL! – Georg Richter Feb 08 '22 at 20:59
  • Yeah I get that there is no value thus changing to `NOT NULL` is not possible. But this message would be more correct: `Column X has NULL values and can not be changed into NOT NULL`. This message is a million times more correct than `data would be truncated`. That's my point. – Hossein Fallah Feb 09 '22 at 13:20