MySql Error: 1175 (updating without a where with primary key), although I do use the PK, and no joins are made inside the query

user_16590932 0
Viewed 49

I have this table (generated by MySql workbench):


CREATE TABLE `system_users` (
  `UserNum` int NOT NULL AUTO_INCREMENT,
  `UserName` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `UserFullName` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `SaveDoc` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `MailOnExpire` char(1) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'N',
  `ExpirationValue` int DEFAULT NULL,
  PRIMARY KEY (`UserName`),
  UNIQUE KEY `UserNum_UNIQUE` (`UserNum`),
  UNIQUE KEY `UserName_UNIQUE` (`UserName`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I also have this stored procedure with an update query:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Set_Preferences`(
    IN xUsername        varchar(45),
    IN xSaveDoc         char(1),
    IN xMailOnExpire    char(1),
    IN xExpirationValue int,
)
BEGIN
    UPDATE system_users
       SET SaveDoc         = xSaveDoc,
           MailOnExpire    = xMailOnExpire,
           ExpirationValue = xExpirationValue
     WHERE Username = xUsername;    
END

I made sure that Username is indeed the PK, then I restarted the workbench in case it's just a temporary error. No luck so far.

In addition, I prefer not to disable safe mode only to get rid of the error, but to find and solve the actual cause.

Thanks

mysql-workbench mysql
1 Answers

In safe updates mode, you can get the error even if your query references the primary key of the table, if the optimizer chooses to do a table-scan or an index-scan.

I tested your query directly in the MySQL client, not the procedure, and the EXPLAIN report showed that it would use the index. Odd that it wants to run the query as type: range when I would have expected eq_ref but I don't have an explanation for that. It counts as using the index enough that safe-updates is satisfied.

mysql> EXPLAIN UPDATE system_users SET SaveDoc = 'Y',            MailOnExpire = 'Y', ExpirationValue = 2 WHERE username='bkarwin';
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | system_users | NULL       | range | PRIMARY       | PRIMARY | 137     | const |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

Anyway, if I change your procedure so it runs EXPLAIN UPDATE instead of running the UPDATE, I can see how the optimizer plan is different when running the same query inside the procedure.

mysql> call Set_Preferences('bkarwin', 'Y', 'Y', 123);
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | UPDATE      | system_users | NULL       | index | NULL          | PRIMARY | 137     | NULL | 2048 |   100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

The documentation mentions that a query that runs a table-scan conflicts with the safe-updates mode, but apparently we see from this case that an index-scan also conflicts. An index-scan on the PRIMARY KEY is similar to a table-scan, in that it will touch every row. In the case of an UPDATE, this means it will lock all the rows, even if it doesn't actually change them.

I don't know why the optimizer chooses to do an index-scan when running the same query inside the procedure, but uses the index when running it directly.

I don't have a workaround to force the UPDATE to use a different optimization plan. All I can suggest is to disable safe-updates mode when you call this procedure.

Bill_Karwin_208600
Related