Hi,

I've been using SQLyog for several years now. I've just upgraded to version 6.16 and now find a strange problem. I can no longer edit a database table field in place and have the results of that edit remain permanent. Here are the steps I've taken:

1. Open database table.
2. Click Table Data tab.
3. Edit field in place
4. Red message "Data modified but not saved appears"
5. Click save button
6. Message from step #4 disappears.
7. Refresh database table.
8. Data prior to my edit re-appears.

Strange thing is that if I use in SQL query command to UPDATE the data those changes remain permanent. Is there something simple I'm missing?

Thanks,

Clyde

yes .. that is strange!

if "Click save button" means you are clicking the blue disk icon, it works for me! It saves and the *red* message does not appear again!
Alternatively you can use <arrow up> or <arrow down>. That will also save.

You can check in HISTORY what SQL was sent to the server!


What was the version you upgraded from?

I am having the same problem. Had it in 6.x and having it in 7.0 RC as well. Having it in an old 5.x Community version too.

Clicking the save button makes the the red message go away and so does moving the cursor off the record. The problem is the UPDATE command sqlyog issues as a result of that is malformed.

Checking the History tab shows the UPDATE command has the target field values in the WHERE clause as well as the SET class. Since the WHERE clause is searching for field values that don't yet exist it naturally fails - if you manually paste it into the query window and execute it it will match 0 records.

The screen shows the target values but the minute you do a refresh the original values reappear since the UPDATE command never succeeded.

The maddening thing is that the bug will be stubbornly consistent, essentially refusing to let you change a row even after repeated tries. But after taking some other actions like loading another table or changing other fields then returning to try again it will suddenly work.

Please tell us,
1) Which mysql version are you using?
2) How mysql is behaving with update query?

1) I'm using MySQL 5.0.37-community. As I said though by looking at the History tab I can see the UPDATE query is malformed. That tells me its a SQLyog issue because SQLyog forms the UPDATE query string.

2) MySQL responds to the malformed UPDATE query as one might expect:
(0 row(s)returned)
(0 ms taken)

If I manually fix the UPDATE query so the WHERE clause contains the original field values, as it is supposed to, then MySQL responds:
(1 row(s)affected)
(0 ms taken)

Sorry I can't lay out a repeatable scenario so you can recreate the bug. I have one table and set of edits the problem is chronically occurring with while other tables and edits rarely if ever have trouble. It feels like there is some combination of table, data, and/or edits that evokes the bug, I just haven't discovered what that is.

Please send us, The Table schema with some sample data where it is reproducable.

One thing I've noticed is the SELECT COUNT(*) command that SQLyog automatically generates before the UPDATE command is also malformed in the same way, with one or more target field values occurring in the WHERE clause instead of all original field values.

If the coders could put in a debugging trap to detect when this statement returns 0 rows (should never happen if code working right) then that would help detect the bug.

Anyway, I found a way to reproduce it. It is present in SQLyog 5.x, 6.x, and 7.0RC. It occurs when manually entering data, moving to another field, using ctrl-V to paste data, then saving. Only that series of actions seems to cause the bug. To reproduce it do this:

- Create a table "temp" with field1 and field2 both type int. Enter a row of data putting '1' in both fields.
- In the Table Data tab select the field1 cell containing the '1' and type '2' overwriting it. Then highlight the '2' and ctrl-c to copy (or you can use Notepad, et. al. to get a '2' into the clipboard).
- Tab over or click on the field2 cell containing the '1'. Press ctrl-v to paste and the '1' is overwritten with a '2'.
- The "Data modified but not saved" indicator is lit. Eliminate it by either pressing the save button or moving off the row.
- At this point SQLyog is indcating the changes have been saved, but they have not. To confirm the failure, hit Refresh and see the vaules both revert back to '1'. Or switch to the History tab to see the malformed SQL:

select count(*) from `temp` where `field1`='2' and `field2`='1'
update `temp` set `field1`='2',`field2`='2' where `field1`='2' and `field2`='1'

Issue Confirmed
Query generation was wrong here,


Wrong query.

It should be ,

Correct Query.

We will look into this.

Thanks for reporting.

Yes, the SQLyog generated query is wrong. But just to clarify under the specific example I gave above where one is changing the values from '1' to '2' the correct query is not the one you suggested, but should actually be:

select count(*) from `temp` where `field1`='1' and `field2`='1'
update `temp` set `field1`='2',`field2`='2' where `field1`='1' and `field2`='1'