7

Why am I getting this error? Did something change with MySQL versions which would cause this (which worked at one point) to now fail? The INSERT INTO does not specify the user_id value which would be required if an insert was done, but since id 1 already exists, this should become an UPDATE and be valid.

mysql> select * from article;
+----+---------+---------------------+-----------+-----------+
| id | user_id | published_at        | title     | content   |
+----+---------+---------------------+-----------+-----------+
|  1 |       1 | 2011-12-10 12:10:00 | article 1 | content 1 |
|  2 |       2 | 2011-12-20 16:20:00 | article 2 | content 2 |
|  3 |       1 | 2012-01-04 22:00:00 | article 3 | content 3 |
+----+---------+---------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> desc article;
+--------------+------------------+------+-----+-------------------+----------------+
| Field        | Type             | Null | Key | Default           | Extra          |
+--------------+------------------+------+-----+-------------------+----------------+
| id           | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
| user_id      | int(11) unsigned | NO   | MUL | NULL              |                |
| published_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| title        | varchar(100)     | NO   |     | NULL              |                |
| content      | text             | NO   |     | NULL              |                |
+--------------+------------------+------+-----+-------------------+----------------+
5 rows in set (0.01 sec)

mysql> INSERT INTO article (id)
    -> VALUES (1)
    -> ON DUPLICATE KEY UPDATE title = 'article 1b', content = 'content 1b';
ERROR 1364 (HY000): Field 'user_id' doesn't have a default value

MySQL version 5.6.12.

CXJ
  • 4,301
  • 3
  • 32
  • 62

3 Answers3

12

You're getting error because

  1. The user_id column is defined as NOT NULL
  2. The user_id column doesn't have a default value specified
  3. You don't specify its value in your query either

PS: the question is irrelevant to the ON DUPLICATE KEY UPDATE clause - it would be the same error if you didn't use it as well.

PPS: regardless of if the ON DUPLICATE KEY UPDATE triggered - your insert should satisfy all the constraints

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • All true and understood. But this is a test case for an existing library, and I'm just trying to modify the library and make sure all the unit tests still pass. :-( – CXJ Jul 05 '13 at 01:00
  • In theory, this test case worked at some point -- before I cloned the library from Github. Maybe MySQL was more lenient in an older version and allowed this admittedly strange DDL and SQL to work? – CXJ Jul 05 '13 at 03:30
  • 1
    @CXJ: I'm not sure. I would expect this query to fail – zerkms Jul 05 '13 at 03:57
  • 1
    It depends on whether strict mode is enabled. Also I in some cases it allows missing defaults for multi row inserts and throws an error for a single row insert with the same values. – Vatev Jul 05 '13 at 07:00
  • 1
    This used not to be a problem in 5.5, but does seem to be in 5.7 (and presumably 5.6) - presumably a Strict Mode issue as @Vatev says. – fooquency Aug 12 '18 at 16:33
1

Set a default value to the user_id table with: ALTER TABLE article ADD user_id int(11) NOT NULL DEFAULT {DEFAULT_VALUE} or change from NOT NULL to NULL

Gabriel Carvalho
  • 133
  • 1
  • 7
  • 1
    After that - the same should be done with `title` and `content` columns as well (which will lead to a weird structure) – zerkms Jul 05 '13 at 00:49
1

There is a cheat if you know you are only updating, however any row being inserted will cause an error.

INSERT INTO article (id, `user_id`)
VALUES (1, `user_id`)
ON DUPLICATE KEY UPDATE
title = 'article 1b',
content = 'content 1b';
Justin Levene
  • 1,630
  • 19
  • 17