Default specifies a value which should be inserted when no value is provided explicitly. When you attempt to insert NULL, it's not an absence of a value, it's a value NULL. So, naturally the default value is not used. It is so both for MySQL and MariaDB. AUTO_INCREMENT is essentially different in this regard.

Possibly a good solution for your case would have been using virtual columns, only they don't seem to support UUID.

What you describe in the last part, however, is easy to implement via virtual columns:

MariaDB [test]> create table t (i int auto_increment primary key, b varchar(16) as (concat('A-',i)) persistent);
Query OK, 0 rows affected (0.91 sec)

MariaDB [test]> insert into t (i) values (1),(2);
Query OK, 2 rows affected (0.18 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t;
+---+------+
| i | b    |
+---+------+
| 1 | A-1  |
| 2 | A-2  |
+---+------+
2 rows in set (0.00 sec)

I can convert your report into a feature request, but please specify what exactly you are after, as you have several suggestions here:

• use a default value when NULL is attempted to be inserted into a non-nullable column;
• add UUID data type;
• allow functions in DEFAULT clause.

The primary problem why i wrote this because my application server for add new records or update existence uses templates such as
INSERT INTO `{$table_name}`(`id`,`id_type`, `value1`, `value2`,`value3`, [ . . . ])
VALUES (NULLIF('{$id}',''), '{$id_type}', '{$value1}', '{$value2}', '{$value3}', [ . . . ])
ON DUPLICATE KEY UPDATE
`id_type` = '{$id_type}',
`value1` = '{$value1}',
`value2` = '{$value2}',
`value3` = '{$value3}',
[ . . . ] = [ . . . ];
This is fine works with AUTOINCREMENT PRIMARY KEY, problems begins when I want use UUID PRIMARY KEY.
It worked before because previous programmer not use PRIMARY KEY it allow him use NULL and TRIGGER for generation UUID if id value is NULL. But even so it is not entirely correct. Because that is the primary key. I am changed schema and saw that trigger has stopped working, because PRIMARY KEY cannot be NULL. Then I try workaround this by using DEFAULT 0, but saw that it also not works.

The best solution would be to use UUID data type, and database automaticaly generates UUID values ​​for NULL, as this happens with AUTOINCREMENT PRIMARY KEY.

But this feature request already has been written by me. And I look forward to solutions for more than two years.

I am don't know what can be done more quickly, probably due to all the above would be enough to fill in the values ​​of DEFAULT NULL. So working CURRENT_TIMESTAMP.

CREATE TABLE `b` (
`b` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test1`.`b` (`b`) VALUES (NULL) ;

SELECT * FROM b

b
---------------------
2015-08-13 02:58:56

It would suit me if it appeared in the version: 10.0.22

Ideas about the use of functions is a reserve for future developments, which would allow to opt out of the trigger.

Virtual columns do not help in described case
because that idea with the generated ID that was then to a distributed database synchronized with the parent base. And for this ID must be transferred to the parent base with a prefix.

I can give a quick state overview of some of my past experience with sequences :

UUID as primary key already proved to be bad solution

• Good primary key should be incremental to avoid fragmentation
• Good primary key should be incremental to avoid gap locking , gaps between 2 UUID are just huge.
• Good primary key should be numeric values to avoid encoding cost .

Holding a global lock at every inserted row would make the sequence very slow

• Good sequence function like auto increment get special management in chunk to avoid locking contention
• Good sequence function should avoid locking, UUID, micro second timestamp avoid collisions and are good candidates.

But some generic default call to external function would still require to be serialized like triggers

• Not appropriate for sequence generation it would need special non locking processing

Can you investigate into microsecond date time, probably feet all requirement ?

Mikhail Gavrilov,

The best solution would be to use UUID data type, and database automaticaly generates UUID values ​​for NULL, as this happens with AUTOINCREMENT PRIMARY KEY.
But this feature request already has been written by me. And I look forward to solutions for more than two years.

Found it, MDEV-5593, which was a duplicate of MDEV-4958. It cannot go to 10.1, but since there is a demand for it, I've increased the priority, so at least it will be considered for 10.2.

I am don't know what can be done more quickly, probably due to all the above would be enough to fill in the values ​​of DEFAULT NULL. So working CURRENT_TIMESTAMP.
It would suit me if it appeared in the version: 10.0.22

If you want, I can convert it to a feature request like that, but I can say right away that there is no chance at all it would possibly appear in any 10.0 release, it is not a feature that can be added to a post-GA release.
Moreover, chances that it would be implemented at all are very, very slim. The behavior of CURRENT_TIMESTAMP is very non-standard comparing to all other data types, and MySQL is drifting away from it. In 5.6, they introduced a variable which switches off non-standard behavior of CURRENT_TIMESTAMP and started deprecating the old behavior (and among other things, it switches off the feature you are referring to); and in 5.7, they are deprecating the variable itself, saying there is no point to control this non-standard behavior as it will be removed in future versions (see http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp).
So, all in all, I would not rely on this feature in a long run, much less on appearance of other similar features.

VAROQUI Stephane,

We have two tasks related to UUID type: MDEV-4958 (Adding datatype UUID) and MDEV-6445 (UUID column type addition for distributed systems). I think your comment will have more value there. I can copy it, but maybe if you read the tasks (especially the second one), you'll want to add something.

Stephane, I am agree with you that integer's much better for PK than UUID.

I try described how I want solve problem without UUID and why can't solve it.

CREATE TABLE `vvv` (
  `s` INT(11) DEFAULT NULL,
  `id` INT(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE FUNCTION `get_current_schema`()
    RETURNS  INT
    DETERMINISTIC
    READS SQL DATA
    BEGIN
	RETURN 1;
    END

CREATE TRIGGER `vvv_insert` BEFORE INSERT ON `vvv` 
    FOR EACH ROW BEGIN
	IF NEW.s=get_current_schema() AND NEW.id IS NULL THEN
		SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM vvv WHERE s = get_current_schema());
	END IF;
    END

1) For example we have 3 different database schema. 1,2,3 we will store id schema as `s` filed.
2) Each schema must have own incremental id, we would store in `id` field.

Function get_current_schema must return for 1 schema 1, for 2 schema 2 and for 3 schema 3

We consider the example on scheme 1 so I simplified function code (hardcoded return 1)

1) schema 1 receive 3 record from scheme 2:

INSERT INTO `vvv` (`s`, `id`) VALUES (2, 20) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (2, 1) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (2, 10) ;

Ok.

s id
------ --------
2 20
2 1
2 10

Next 3 record from scheme 3:

INSERT INTO `vvv` (`s`, `id`) VALUES (3, 5) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (3, 6) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (3, 7) ;

s id
------ --------
2 20
2 1
2 10
3 5
3 6
3 7

And then create own records:

INSERT INTO `vvv` (`s`, `id`) VALUES (1, NULL) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (1, NULL) ;
INSERT INTO `vvv` (`s`, `id`) VALUES (1, NULL) ;

s id
------ --------
2 20
2 1
2 10
3 5
3 6
3 7
1 1
1 2
1 3

Worked as expected, but:

1) I don't think that it really good solution for get next id:
SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM vvv WHERE s = get_current_schema());
Be better if MariaDB have function get_autoinctement_for_table('vvv') and I can use this function in my trigger.

2) I can't create PK for field's `s` and `id`

CREATE TABLE `vvv` (
  `s` INT(11) NOT NULL,
  `id` INT(11) NOT NULL,
  PRIMARY KEY (`s`,`id`),
  KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Because if I do it I trigger stop work's because I try insert NULL value.

If above problem would be solved I can created distributed system without UUID.

I am afraid that microsecond date time may have collisions.

Elena, if before trigger would be called before NULL checking, I not need set DEFAULT 0 also for UUID generation, because I can handle NULL value inside trigger and replace NULL with correct value.

So, it may be more easy to do?

Mikhail Gavrilov,

Yes, it's very reasonable. Moreover, it is actually an ancient upstream bug http://bugs.mysql.com/bug.php?id=6295 which was fixed in 5.7. We will need to merge the bugfix.

I don't know if it's possible to do it in 10.0 (from the upstream bug history I got an impression that the bugfix is very intrusive); for now, I will set it for 10.0 so it will be considered, but no promises here.