MySQLi does not return error code

Question

In my MySQL database I have a table "table1" with unique constraint set on column "name" - I want to prevent duplicate names.

If there's already name 'John' in table this code:

$db=new mysqli(...);
$sql="INSERT INTO table1 SET id=10,name='John'";
if(!$db->query($sql))
{
 if($db->errno==1062)
 {
 throw new InsertNonUniqueException(...);
 }
 else
 {
 throw new InsertException(...);
 }
}

should throw InsertNonUniqueException() (my own exception). Instead, it throws InsertException().

Execution of query returns false and execution enters the if() loop. Also $db->row_affected is -1 but problem is that $db->errno is always O (it should be 1062)!!! So I can't detect that my insert error was caused by violating unique key constraint on name column!

I don't know why mysqli does not return 1062 code when unique key constraint violation occurs!

• Mihai: Are you sure you have an unique index on your column?
• sbrbot: Yes, I am sure, because if I execute the same sql in SQL command line it returns "Error code 1062, SQL state 23000: Duplicate entry 'John' for key..."
• imtheman: echo $db->errno and see what is says.
• Ryan Vincent: Your sql code works fine here - mysql 5.5.16. I assume that the 'throw InsertNonUniqueException' is a typing error as it should be: 'throw new insertNonUniqueException(...'.
• Simone Nigro: try INSERT INTO table1 (id, name) VALUES (10, 'John')

Answer 1

I can't leave a comment, thus going to ask you here.

Please provide the result of SHOW CREATE TABLE table1;

I can't reproduce your problem using your code and next table:

CREATE TABLE `table1` (
`name` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 2

Am I the only one around here that thinks you have an error in your SQL syntax?.. There is no room for SET in INSERT INTO, because you can only use SET in UPDATE statements (assuming you habe MySQL in version 5.5 or below).

INSERT INTO syntax is like the following (as described in the docs):

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [(col_name,...)]
 SELECT ...
 [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

OR

INSERT INTO tbl_temp2 (fld_id)
 SELECT tbl_temp1.fld_order_id
 FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Try it like this:

<?php
$sql="INSERT INTO table1 (id, name) VALUES ('10', 'John')";
...

• Meeuuuhhhh: You can use SET in INSERT INTO with mysql : doc
• Kleskowy: @Meeuuuhhhh OK, in MySQL 5.6 and above. In 5.5 an below it's not supported.

Answer 3

step 1

make sure that the table has a unique key

SHOW CREATE TABLE table1

expected result

CREATE TABLE `table1` (
 `id` INT(11) default NULL,
 `name` varchar(11) COLLATE utf8_general_ci NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

if there is UNIQUE KEY name (name) we have a unique key

step 2

try to change your code

$db = new mysqli(...);
// first insert
if( !$db->query("INSERT INTO table1 (id, name) VALUES (10, 'John')") ) {
 throw new Exception($db->error);
}
// second insert (for me raise: Duplicate entry 'John' for key 'name')
if( !$db->query("INSERT INTO table1 (id, name) VALUES (11, 'John')") ) {
 throw new Exception($db->error);
}

Please, try these two steps

Side note: if you have name and id as duplicates, only the first duplicate encountered will be returned in the message.

Answer 4

The only issue i have with your code is that:

having setup your table and columns.

I setup a unique index on the table. I did .. stuff on a two column table that ensure it works.

You missed the 'new' keyword when you 'throw exceptions'. this is the only error with your posted code that i could find.

i.e: throw new Exception('Division by zero.'); // example taken from PHP manual.