Dropping the default value doesn't change the default

Version 10.2.0.5599 (32 Bit)

It seems as if when a column is changed from NULL to No Default Value it runs a query to set it to NULL instead of just removing the default.

Here is what gets executed when trying to change it:

ALTER TABLE `game_store`
    ALTER `game_id` DROP DEFAULT;

ALTER TABLE `game_store`
    CHANGE COLUMN `game_id` `game_id` INT(10) UNSIGNED NULL AFTER `id`;

Yes, that DROP DEFAULT clause is packed into an extra query, as MySQL does not support doing it in an ALTER TABLE ... CHANGE COLUMN ... query. Normally the latter one contains all changes you make on a column. Also, the latter one is fired on all modifications, and HeidiSQL is not able to determine whether there are more modifications or not.

Does that extra query produce some problem?

There isn't a huge problem, just that I cannot drop the default value when modifying the table.

You cannot drop the default value? The first query should exactly do that, and the second one does not contain a new default. That NULL in the second one means: this column allows NULL values.

Here is a gif showing what is happening if this helps

Here are all the commands that were executed:

ALTER TABLE `users`
    ALTER `first` DROP DEFAULT;

ALTER TABLE `users`
    CHANGE COLUMN `first` `first` VARCHAR(50) NULL AFTER `id`;

SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test';

SHOW TABLE STATUS FROM `test`;

SHOW FUNCTION STATUS WHERE `Db`='test';

SHOW PROCEDURE STATUS WHERE `Db`='test';

SHOW TRIGGERS FROM `test`;

SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='test';

SHOW CREATE TABLE `test`.`users`;

/* Entering session "Localhost\Local" */
SHOW CREATE TABLE `test`.`users`;

Also, this used to work in an older version.

Indeed, the server adds that DEFAULT NULL again with the second ALTER query, although it does not contain such a clause.

But the documentation is quite clear here: default column value will be null anyway, even after dropping a default value with such a query:

ALTER TABLE `users` ALTER `first` DROP DEFAULT

https://mariadb.com/kb/en/library/create-table/#default-column-option

Specify a default value using the DEFAULT clause. If you don't specify DEFAULT then the following rules apply:

If the column is not defined with NOT NULL, AUTO_INCREMENT or TIMESTAMP, an explicit DEFAULT NULL will be added. Note that in MySQL and in MariaDB before 10.1.6, you may get an explicit DEFAULT for primary key parts, if not specified with NOT NULL.

Okay, I guess I didn't know that no default is basically the same as NULL:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

Just because DEFAULT NULL is the default behavior, doesn't mean that no default is not possible. You just have to do DROP DEFAULT after you create the column/table and every time you ALTER the column.