mysql_query() behavior is different than mysql on command line

Question

I have the following table:

insert_test | CREATE TABLE insert_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  closed int(11) NOT NULL DEFAULT '0',
  user int(11) DEFAULT '-1',
  level int(11) DEFAULT '-1',
  comment text,
  count int(11) DEFAULT '1',
  PRIMARY KEY (id,closed),
  UNIQUE KEY user (user,level,closed)
)

When I run the following commands on the command line:

INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;
INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;

...this is the output I get:

+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
|  9 |      0 |    1 |    50 | First insert  |     1 |
| 10 |      0 |    1 |    75 | Second insert |     1 |
+----+--------+------+-------+---------------+-------+

When I run these commands using mysql_query(), this is what I get.

+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
| 11 |      0 |    1 |    50 | Second insert |     2 |
+----+--------+------+-------+---------------+-------+

So the query is updating instead of inserting a new row when I use the mysql_query() function, when it shouldn't be, because the two inserts have different levels and so they are unique...right? Am I wrong, or is there something going on here?

Edit: The code snippet exactly where I use it is:

char* query = "INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
char* query2 = "INSERT INTO db.insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
mysql_query( link, query );
mysql_query( link, query2 );

I know the link is correct and that this code is being run because it does work (i.e. the queries are run) except for the issue where it is updating instead of inserting.

Answer 1

I cannot reproduce any issue. I just tested this, with your table above (thank you for creating a simple test case), and a quick C program. It worked as expected, inserting two rows. Using MySQL 8.0.0-dmr.

#include <my_global.h>
#include <mysql.h>
#include <string.h>

int main(int argc, char **argv)
{
  MYSQL *con = mysql_init(NULL);

  if (con == NULL)
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }

  mysql_real_connect(con, "localhost", "root", "password", "test", 0, NULL, 0);

  if (strlen(mysql_error(con)))
  {
      fprintf(stderr, "%s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  char* query = "INSERT INTO insert_test (closed, user, level, comment, count) VALUES (0, 1, 50, 'First insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";
  char* query2 = "INSERT INTO insert_test (closed, user, level, comment, count) VALUES (0, 1, 75, 'Second insert', 1) ON DUPLICATE KEY UPDATE comment=VALUES(comment), count=count+1;";

  mysql_query(con, query);

  if (mysql_errno(con))
  {
      fprintf(stderr, "Error: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  printf("Rows: %ld\n", (long) mysql_affected_rows(con));

  if (mysql_warning_count(con))
  {
      fprintf(stderr, "Warnings: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  mysql_query(con, query2);

  if (mysql_errno(con))
  {
      fprintf(stderr, "Error: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  printf("Rows: %ld\n", (long) mysql_affected_rows(con));

  if (mysql_warning_count(con))
  {
      fprintf(stderr, "Warnings: %s\n", mysql_error(con));
      mysql_close(con);
      exit(1);
  }

  mysql_close(con);
  exit(0);
}

Output shows:

Rows: 1
Rows: 1

Data in the table:

mysql> select * from insert_test;
+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
|  5 |      0 |    1 |    50 | First insert  |     1 |
|  6 |      0 |    1 |    75 | Second insert |     1 |
+----+--------+------+-------+---------------+-------+

Running the program multiple times increments the count column for both rows. When this happens, it shows 2 rows affected for each statement, which is normal.

mysql> select * from insert_test;
+----+--------+------+-------+---------------+-------+
| id | closed | user | level | comment       | count |
+----+--------+------+-------+---------------+-------+
| 23 |      0 |    1 |    50 | First insert  |     2 |
| 24 |      0 |    1 |    75 | Second insert |     2 |
+----+--------+------+-------+---------------+-------+