Bug #49539 non-descriptive error with multi-table update ignore and strict mode
Submitted: 8 Dec 2009 16:55 Modified: 11 May 2012 17:44
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.88, 5.1.41 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[8 Dec 2009 16:55] Shane Bester
Description:
strange error to receive, since 0 usually means no error:

mysql> update ignore (select 128 as col1) x, test.t1 set t1.a=x.col1;
ERROR 1030 (HY000): Got error 0 from storage engine
mysql>

Okay, show warnings shows enough output, but 2nd and 4th error
is non-descriptive and confusing.

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'a' at row 1 |
| Error | 1030 | Got error 0 from storage engine            | <--- ?
| Error | 1105 | An error occured in multi-table update     |
| Error | 1105 | Unknown error                              | <--- ?
+-------+------+--------------------------------------------+
4 rows in set (0.00 sec)

furthermore, innodb table will cause just 2 warnings (assert in debug mode) and myisam table in non-debug will cause the error above.  So there is more inconsistency, maybe caused by bug #49534

How to repeat:
drop table if exists t1;
create table t1(a tinyint) engine=myisam;
insert into t1 values (0);
set session sql_mode='STRICT_ALL_TABLES';
update ignore (select 128 as col1) x, test.t1 set t1.a=x.col1;

Suggested fix:
clients would like to see "Out of range value for column 'a' at row 1" instead of the meaningless error "Got error 0 from storage engine".
[8 Dec 2009 17:45] Miguel Solorzano
Thank you for the bug report. Verified as described.
[11 May 2012 17:44] Paul Dubois
Noted in 5.6.6 changelog.

A multiple-table UPDATE with the IGNORE keyword resulted in an
inappropriate and not meaningful "Got error 0 from storage engine"
message.