I couldn't find this behavior described in the official MySQL documentation

A few minutes of looking in the MySQL docs found this:

Column values can be given in several ways:

If you are not running in strict SQL mode, any column
not explicitly given a value is set to its default
(explicit or implicit) value. For example, if you specify a
column list that does not name all the columns in the
table, unnamed columns are set to their default values.
Default value assignment is described in Section 11.6,
“Data Type Default Values”. See also Section 1.8.3.3,
“Constraints on Invalid Data”.

If you want an INSERT statement to generate an error unless
you explicitly specify values for all columns that do not
have a default value, you should use strict mode. See
Section 5.1.7, “Server SQL Modes”.

In mysql you can achieve this as,

ALTER TABLE room MODIFY property_id bigint(20) not null;

The syntax you used is from a SQL Server example, not from MySQL. It would be good to check the MySQL documentation about ALTER TABLE syntax.

In MySQL, the ALTER COLUMN subclause can only be used for setting or dropping the default value of the column (SET DEFAULT literal or DROP DEFAULT).

You need to use either CHANGE COLUMN (note that column name is doubled as you could use this to change its name):

ALTER TABLE MyTable  
    CHANGE COLUMN comment comment BIGINT NOT NULL;

or MODIFY COLUMN:

ALTER TABLE MyTable 
    MODIFY COLUMN comment BIGINT NOT NULL;

Now, there are 2 more problems, beside the syntax:

• Seeing the CREATE TABLE, converting a "comment" column from TEXT to BIGINT does not make much sense. I'll assume that you want to keep it TEXT and only make it NOT NULL.

• There might be NULL values already in the table. If that's the case, the statement will fail with errors. So, you have to first UPDATE those values to some non-null value (say the empty string) and then modify the column:

So our statements become:

UPDATE glpi_ticketsatisfactions 
SET comment = ''
WHERE Comment IS NULL ;

ALTER TABLE glpi_ticketsatisfactions 
    MODIFY COLUMN comment TEXT COLLATE utf8_unicode_ci NOT NULL ;

This is because the table already has a row or more with null value, you might need to update those to 0 before executing ALTER table, e.g.:

UPDATE test SET views = 0 WHERE views IS NULL;
ALTER TABLE test MODIFY COLUMN views int NOT NULL DEFAULT 0;

Here's the SQL Fiddle (commenting out update statement will result in the same error).

The answer is to use the following (slightly odd) syntax:

ALTER TABLE items ALTER ordering DROP DEFAULT;

try this

use NULL instead of NOT NULL

CREATE TABLE `Foo` (
`id`  int NOT NULL ,
`FirstName`  varchar(255) NULL ,
`LastName`  varchar(255) NULL DEFAULT  'NONE' , 
PRIMARY KEY (`id`)
);

and query like this

use DEFAULT instead of NULL

INSERT INTO Foo ( `FirstName`, `LastName` ) VALUES('FOO', DEFAULT);

That's perfectly acceptable, it basically states:

You have to provide a value for this, but I have no clue what that would be.

If you define a default value, you claim that this is a sensible value for any row that doesn't supply a value - which is not really feasible for most attributes.

Think about a column named first_name - you want to make sure a value is supplied but there is no way you can come up with a sensible default value for that. Or a column named salary in an employee table.

As documented under Data Type Default Values:

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

(I think they meant implicit, not explicit).

Moreover, as documented under CREATE TABLE Syntax:

If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

Therefore, in MySQL the following column definitions are all identical:

columnname type
columnname type NULL
columnname type DEFAULT NULL
columnname type NULL DEFAULT NULL

The choice of which to use is a balance between being explicit, and being concise. Depending on the circumstances, I might use any of the above.