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 ?
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.
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?
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.
My fix is not nearly as intrusive as what 5.7 has. Still, it changes a very common code path, so I'd rather not put it into 10.0 to avoid possible regressions. I'm going to push it into 10.1 only
Sergei, I don't seen your fix, but expected sequence of checks looks for me so:
For INSERT:
1. run before insert trigger (here may be generated correct values)
2. apply autoincrement for null PK and DEFAULT value for TIMESTAMP etc
3. check duplication for PK and unique index, otherwise if used ON DUPLICATE UPDATE goto update duplicate record begin with step 3.
4. check for not null and type
5. check for constraints
6. add new record
7. run after insert trigger
For UPDATE:
1. run before update trigger (here may be generated correct values)
2. apply autoincrement for null PK and DEFAULT value for TIMESTAMP etc
3. check duplication for PK and unique index
4. check for not null and type checking
5. check for constraints
6. update record
7. run after update trigger
mikhail, right. The difficulty is in the fact that NOT NULL columns in MariaDB/MySQL cannot store NULL values. Not even temporarily, not even in memory. There is no place allocated for that. That's why NOT NULL constraint is checked very early, when the value is stored in the field in memory.
To fix it we need to be able to store NULL values in the NOT NULL columns. Temporarily, only in memory, not in the table. But still, this is a big change that can potentially introduce regressions. Especially because it will affect every INSERT and UPDATE statements, not only those that store NULL and later change it in the BEFORE trigger.
I understood that this could trigger a regression. I would like you to pay attention to the moment I placed the check for null after checking DUPLICATE this in order to be able to do mass update without repeating all the columns in the query.
Example from real life:
INSERT INTO `appl_profiles` (
|
`id_profile`,
|
`id_profile_type`,
|
`emp_id_first`,
|
`dynamic_cols`
|
)
|
VALUES
|
(
|
'99659cfc-6bf5-11e5-bcc5-0050563c3a6d',
|
'13',
|
'212516',
|
COLUMN_CREATE ('bayer_part', '11')
|
),
|
(
|
'a270fce5-6bf5-11e5-bcc5-0050563c3a6d',
|
'13',
|
'212516',
|
COLUMN_CREATE ('bayer_part', '100')
|
)
|
ON DUPLICATE KEY UPDATE
|
`dynamic_cols` = VALUES(`dynamic_cols`)
|
I can not remove '13', '212516' because they are NOT NULLable.
if checking for NULL would be after checking on duplication
I could write:
INSERT INTO `appl_profiles` (
|
`id_profile`,
|
`dynamic_cols`
|
)
|
VALUES
|
(
|
'99659cfc-6bf5-11e5-bcc5-0050563c3a6d',
|
COLUMN_CREATE ('bayer_part', '11')
|
),
|
(
|
'a270fce5-6bf5-11e5-bcc5-0050563c3a6d',
|
COLUMN_CREATE ('bayer_part', '100')
|
)
|
ON DUPLICATE KEY UPDATE
|
`dynamic_cols` = VALUES(`dynamic_cols`)
|
this is much cleaner than previous variant.
For the record, the actual change was this:
commit 0686c34d22a5cbf93015012eaf77a4a977b63afb ad5db17e882fea36dcae6f6e61996b5f9bf28962
|
Author: Sergei Golubchik <serg@mariadb.org>
|
Date: Sat Nov 14 22:51:54 2015 +0100
|
|
|
MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column
|
|
NOT NULL constraint must be checked *after* the BEFORE triggers.
|
That is for INSERT and UPDATE statements even NOT NULL fields
|
must be able to store a NULL temporarily at least while
|
BEFORE INSERT/UPDATE triggers are running.
|
It is important to have it here to avoid confusion, because it has very little to do with the initial bug summary.