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!

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..." — sbrbot
– sbrbot, Commented Jan 19, 2015 at 20:43
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(...'. — Ryan Vincent
– Ryan Vincent, Commented Feb 13, 2015 at 17:16

vl.lapikov · Accepted Answer · 2015-02-10 19:10:16Z

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

answered Feb 10, 2015 at 19:10

vl.lapikov

7967 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kleskowy · Accepted Answer · 2015-02-13 16:45:28Z

1

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')";
...

edited Feb 13, 2015 at 16:45

answered Feb 13, 2015 at 16:13

Kleskowy

2,6681 gold badge18 silver badges19 bronze badges

2 Comments

Meeuuuhhhh Over a year ago

You can use SET in INSERT INTO with mysql : doc

Kleskowy Over a year ago

@Meeuuuhhhh OK, in MySQL 5.6 and above. In 5.5 an below it's not supported.

Simone Nigro · Accepted Answer · 2015-02-14 11:49:13Z

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.

Ryan Vincent · Accepted Answer · 2015-02-15 10:35:32Z

0

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.

edited Feb 15, 2015 at 10:35

answered Feb 14, 2015 at 21:38

Ryan Vincent

4,5137 gold badges24 silver badges33 bronze badges

Collectives™ on Stack Overflow

MySQLi does not return error code

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related