Errors with Insert sql with multiple values

[Barbara Deaton]

I've recently implemented code in my application (C MySQL 4.0 API) to generate an insert
statement with multiple values:

Insert into table (co1, col2) values (1, 'a'), (2, 'b'), (3, 'c')...

If an error occurs during insert it appears that depending on the type of storage engine
MySQL won't commit any of the rows or do further processing past the error.  Is there an
option that I can set that would allow processing of the rest of the values to continue?

For example,
mysql> create table instab (col1 int unique, col2 smallint primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> show create table instab;
+--------+---------------------------------------------------------------
| Table  | Create Table
+--------+---------------------------------------------------------------
| instab | CREATE TABLE `instab` (
  `col1` int(11) default NULL,
  `col2` smallint(6) NOT NULL,
  PRIMARY KEY  (`col2`),
  UNIQUE KEY `col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into instab values (1,1), (2,2), (3,3), (4,2), (5,5);
ERROR 1062 (23000): Duplicate entry '2' for key 1
mysql> select * from instab;
Empty set (0.00 sec)


No rows were inserted since it was an InnoDB table.  Where as MyISAM will insert the first
3 rows and then stop processing.

mysql> create table instab (col1 int unique, col2 smallint primary key) engine=m
yisam;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table instab;
+--------+----------------------------------------------------------------------
| Table  | Create Table

                            |
+--------+----------------------------------------------------------------------
| instab | CREATE TABLE `instab` (
  `col1` int(11) default NULL,
  `col2` smallint(6) NOT NULL,
  PRIMARY KEY  (`col2`),
  UNIQUE KEY `col1` (`col1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into instab values (1,1), (2,2), (3,3), (4,2), (5,5);
ERROR 1062 (23000): Duplicate entry '2' for key 1
mysql> select * from instab;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)


Is there an option I can set that basically says ignore errors and keep processing valid
rows?

Thanks.
Barbara