Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.33
-
None
-
None
Description
In MariaDB 5.5.28 (and merged into 10.0.0) the following change was made by Monty:
http://bazaar.launchpad.net/~maria-captains/maria/10.0-base/revision/3413.16.2#sql/sql_insert.cc
This causes warnings to be emitted by INSERT IGNORE, which differs from MySQL behavior and specifically contradicts the documentation for INSERT IGNORE which says:
http://dev.mysql.com/doc/refman/5.5/en/insert.html
"If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not."
The MariaDB page on IGNORE doesn't mention this difference:
https://mariadb.com/kb/en/ignore/
This new behavior is not actually desired and causes incompatibilities in some cases (e.g. clients who use INSERT IGNORE and actually check warnings). This should have been gated behind a SQL_MODE at least.
Is there any chance this change could be reverted or corrected?
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.0.6 [ 13202 ] | |
| Fix Version/s | 5.5.34 [ 13700 ] | |
| Assignee | Michael Widenius [ monty ] |
Ugh, after examining this change in detail, it has a few more serious problems:
- It exposes an HA_* handler-internal error number (167) to the client. It should have implemented an existing or new ER_* error and sent that instead. (This would also have allowed the test to use an ER_* name instead of the constant 167.)
- It conflates "Duplicate key" and "Out of range" errors for ALL transactions in replication in order to paper over the upgrade issues with changing this error. This is definitely not expected!
- It breaks a test in insert.result by causing the row_count() function to return -1 (due to the hidden SHOW WARNINGS done by mysqltest) instead of 0. The test was just updated with the new invalid result.
For many it has been a problem that IGNORE didn't give warnings for everything it ignored. I added the warnings for duplicate key errors to correct this.
I assume you agree that the more you know the server did, the better?
I did however forget to update the ignore section and the compatibility section of the KB. This are now fixed.
As far we have seen this has not caused many conflicts. For one year, we have only got one bug report (this one).
The reason for merging Duplicate key error and Out of range errors was the best thing I could come up with to not get replication to break when using an old master and new slave or vice versa. As this is only for replication I don't think this is a
serious issue.
From a client point if view, there is not a big difference between giving a handler error than an SQL error (> 1000). There is already many cases where we use handler errors directly. Only common errors that we want to translate are giving SQL errors. When it comes to test cases, we should sometime expand to have also handler errors as key values for the test cases.
That said, I could add a new compatibility 'mysql55' mode where we ignore giving the warnings for duplicate key. (We already have MySQL323 and MYSQL40 so this would be in line with this.
To fix the 167 issue, we could add the handler errors to ./include/mysqld_ername.h.
Jeremy, do you have a strong issue with that the error number is < 1000 ?
(I would prefer to not change an error number that has been in use for > 1 year).
The change of row_count() to -1 was actually correct.
If you call row_count() after a query that was niot insert it will return -1. This has not changed.
The reason for the result change was that the test case issued a 'show warnings' just after the insert, because of the new warning. This cases row_count() to return -1 instead of 0. Sorry for not adding a per-file comment for this.
I have now pushed code into 5.5 that allow one to also use symbols for handler errors.
As a test of this, I removed the error number constant 167 from the test cases.
| Fix Version/s | 10.0.7 [ 14100 ] | |
| Fix Version/s | 10.0.6 [ 13202 ] |
| Fix Version/s | 5.5.35 [ 14000 ] | |
| Fix Version/s | 5.5.34 [ 13700 ] |
| Fix Version/s | 10.0.8 [ 14200 ] | |
| Fix Version/s | 10.0.7 [ 14100 ] |
Documentation updated about difference in behavior.
To make things compatible with old versions, I suggest we add a new flag: 'old_mode'
This would work as 'sql_mode', but should only be used when one wants to emulate some behavior from old MySQL or MariaDB versions.
To handle this case, one could use:
set @@old_mode="NO_DUP_KEY_WARNINGS_WITH_IGNORE";
(or --old-mode from the command line)
We should over time make the old 'old' variable obsolete and instead start using this method, as this is much more flexible.
I am discussing this with Sergei today and if gives ok I will push this into 5.5.
One can now turn of the warnings by doing:
OLD_MODE= "NO_DUP_KEY_WARNINGS_WITH_IGNORE";
| Fix Version/s | 10.0.8 [ 14200 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | defaullt [ 29405 ] | MariaDB v2 [ 42778 ] |
| Workflow | MariaDB v2 [ 42778 ] | MariaDB v3 [ 61787 ] |
| Workflow | MariaDB v3 [ 61787 ] | MariaDB v4 [ 147150 ] |
I remember it being an intentional change, assigning to Monty in case he wants to reconsider.