MySQL Bugs: #96095: Primary key ignored when updating unless refreshing query

Bug #96095	Primary key ignored when updating unless refreshing query
Submitted:	4 Jul 2019 20:26	Modified:	3 Oct 2019 15:58
Reporter:	Bryce Morrison	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	8.0.16	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	primary key

Description:
I previously reported a bug with Workbench 8.0.14, and it seemed to be resolved when testing on another computer running 8.0.16.

I just now noticed that the problem is occurring again with version 8.0.16.

The update statements are ending with "WHERE", and are not including any primary keys.

How to repeat:
The table I am running the update statement on has more than one primary key.

Run a query on a table with multiple key columns. 
Change some of the values.
Click the execute button.

Do NOT run another query.
Update some of the values that now show automatically (with a re-query supposedly having happened automatically).
Click the execute button.

Notice the failures to update, with an error message such as:
"1064: You have an error in your SQL syntax; check the manual that corresponds...."

Hello Bryce,

Thank you for the report and feedback.
I tried to reproduce at my end with dummy test case on Win 10 with WB 8.0.16 build but not seeing any issues. As requested in Bug #93990, please provide repeatable test case.

- dummy schema for which I'm not seeing any issues:

CREATE DATABASE IF NOT EXISTS test;
use test;
DROP TABLE IF EXISTS t;
CREATE TABLE t (
    id INT,
    id_1 INT,
    some_text varchar(100),
    PRIMARY KEY (id, id_1)
) ;

INSERT INTO `test`.`t` (`id`, `id_1`, `some_text`) VALUES ('1', '1', 'MySQL1');
INSERT INTO `test`.`t` (`id`, `id_1`, `some_text`) VALUES ('2', '2', 'MySQL2');
INSERT INTO `test`.`t` (`id`, `id_1`, `some_text`) VALUES ('3', '3', 'MySQL3');

Then from WB attempted to update as mentioned in the report but it is working fine.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I do not believe that you followed my instructions. Please try to replicate that problem.

To clarify, you only attempted to insert three rows, and forced the key column's value.

I suggest trying to edit an existing table's existing values, in a table which as multiple key columns requiring each to be a unique numeric ID.  Then make a request to modify a value that is not the key column, such as a text field.

After the first attempt is successful, Workbench will appear to refresh the table which is visible.

Repeat the procedure, adjusting another value which is not a key column.  When Workbench tries to update the table, and provides you with a preview of the commands which are to be executed, notice that after "WHERE", when you would expect to see the TWO OR MORE key column values defined in the command, they are blank.

Please read this comment more than once, slowly this time, and do not skip any steps.

I apologize if I'm appearing impatient with this ticket.  You've at least made the two key columns in your example, but there's something missing.  Try making one of them required, but perhaps not auto-increment.

Bryce, Could you provide SQL to create a database and table for which you experience the issue?

I'm sorry, Simon, but I do not have access to that database at this particular moment, but I'm reviewing some of the files that I do have with me that may have been modified at the same time that this ticket (96095) was opened.

From what I recall, one of the primary key columns did require an auto increment unique id.  The second column which did require it to be a primary key might have also been auto increment, and of almost a matching value to the first primary key column.

To repeat this issue we need the complete SQL script test case, with just your description we are not able to repeat it. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".