alter table … change column… NOT NULL DEFAULT 2 — weird behaviour

>> EDIT:

So, thanks to @morgo and @cmptrwizard I now know what was going on :)

Turns out, old decisions do sometimes come back and bite later on hehe… who knew self-deprecating-sarcasm.

We turned STRICT_TRANS_TABLES off way back when we upgraded to MySQL 5.6 – meaning that the error MySQL was supposed to through due to the alter not going to do what I was hoping (my mistake), was not thrown. This resulted it the failure being silent – which is what bugged me for days :)

Thanks guys for the assiste.


Original post follows…

This is just weird, and it’s bugging me. It appears when MySQL does the alter table operation on a previously nullable column it does run through it changing the current null values to the new default – but instead of using the default specified it uses the base default value of the core.

Have a look at this to reproduce it:

Ok, let’s add some rows, so we have a nice view of things:

Looking at the data, we find it looks like this:

Ok so far so good. Now, let’s alter the table’s “some_value” column to be a default of “2”.

Now, when doing this on a large table it’s pretty clear that MySQL actually runs through the table entries and changes their values (as it takes minutes to run on a big enough table). So we know it’s not just changing the metadata of the table.

Right, let’s look at the data again:

Do you see what I see? The “some_value” of row with id 101 was changed to “0” – zero. But the alter table statement said the default should be “2” – two.

Ok, did I break the table? Let’s test inserting a row without the “some_value” filled in – which in the past would’ve made it null… but now that I altered the definition it should make it “2”.

Ok cool, so let’s retrieve the data again:

Anybody know why the heck this is?

Leave a Reply

    • That’s amazing, months when upgrading to 5.6 we had to disable strict-mode due to a date constraint we first need to fix in our code. Therefore we didn’t get the error thrown back to us – I’ve tested this and it is indeed the case. Thanks for the comment Morgan, appreciate it! Been bugging me for days :)

      Note to self: Need to get that date constraint sorted out so we can go back to strict-mode 😉