INSERT ON DUPLICATE KEY UPDATE with Composite key

[NotionCommotion]

Why does the INSERT ON DUPLICATE KEY UPDATE fail?  I read that I also need to add a UNIQUE INDEX (but I have no idea why) and did so, but it still fails.  Thanks

mysql> EXPLAIN points;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | NULL    |       |
| accounts_id | int(11)     | NO   | PRI | NULL    |       |
| name        | varchar(45) | NO   | MUL | NULL    |       |
| value       | varchar(45) | YES  |     | NULL    |       |
| value_old   | varchar(45) | YES  |     | NULL    |       |
| units       | varchar(45) | YES  |     | NULL    |       |
| timestamp   | int(11)     | YES  |     | NULL    |       |
| type        | char(     | NO   | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> SHOW INDEX FROM points;
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| points |          0 | PRIMARY                    |            1 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | PRIMARY                    |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | name_UNIQUE                |            1 | name        | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | name_UNIQUE                |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points_pk           |            1 | accounts_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points_pk           |            2 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points2_pk          |            1 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points2_pk          |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          1 | fk_points_accounts_id_idx  |            1 | accounts_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| points |          1 | fk_points_point_types1_idx |            1 | type        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM points WHERE id=150;
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
| id  | accounts_id | name             | value  | value_old | units | timestamp | type |
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
| 150 |          51 | sample test one  | 102.67 | 102.74    | W     |      NULL | real |
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W') ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=NOW();
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>

 

 

[benanamen]

You have name set to not allow nulls and there is no default value set for that column. In your query you do nothing with the name column. As you have it, you can think of  'name' as a required field.

Edited December 15, 2016 by benanamen

[kicken]

Because your statement is basically an INSERT to start with you have to satisfy all the conditions that a normal insert would have. That includes providing a value for all the NOT NULL columns that do not have a default.

 

The ON DUPLICATE KEY clause is basically just a way to provide some automatic error handling. Think of it as mysql attempting the insert but if it returns an error indicating a duplicate key violation then it re-issues an UPDATE query instead.

[NotionCommotion]

Thanks Benamamen and Kicken.

 

But as seen by the SELECT statement I showed, the record with PK 150-51 already has a value for name.

[kicken]

Yes, but that is irrelevant. Mysql is going to error on the "try to insert" part of the query due to the missing column and never actually get to the update part. In pseudo-code it'd be something like:

if ($query->isValidInsert()){
   if (!$query->insert() && $query->getError() == 'duplicate'){
       $query->update();
   }
}

It's failing on the isValidInsert part because it's missing data.

[NotionCommotion]

Dah...

 

I see several workarounds.

 

1. Get rid of the NOT NULL constraint (which I don't wish to do).
2. Get fancy with triggers or stored procedures (which I also don't with to do).
3. Maybe the following?

INSERT INTO points (id, accounts_id, value, units, name, type) VALUES (150,51,158.6,'W','bogus','bogus')
ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP();

 

     4.  EDIT.  Or probably this.  An extra advantage is INSERTS are never really desired as I am doing this just as a multiple update hack.

 

INSERT IGNORE INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W')
ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP();

Edited December 15, 2016 by NotionCommotion

[benanamen]

What is it that you are actually working on, meaning, the overall task, not your attempted solution to it?

[NotionCommotion]

Ah, Benanamen with his aversion to XY questions

 

I am trying to efficiently perform mass update queries where some of the NOT NULL columns are not updated.  See https://forums.phpfreaks.com/topic/302727-multiple-updates/ for background history.

 

Thanks

 

PS.  As indicated by others responding to this post, a composite key has nothing to do with it.

[kicken]

You could go back to just using an UPDATE statement rather than an INSERT hack. I posted another alternative method in your original thread.

[NotionCommotion]

ThAnks kicked, and while I agree it a hack, you think other solutions are better?

[Shaqattack118]

I think the issue is that the column "value" is a varchar but you're entering it as if it's an integer