Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Warning: no row

thescourgex's profile image thescourgex posted 12 years ago in General Permalink
Warning: No row was affected by the last update. This is most likely caused by entering data which the MySQL-server has converted silently. For example when you enter a date like "0000-01-01" (applies only to newer MySQL-versions).
---------------------------------------------------------------
I get this message sometimes when i try to edit something in a row, some rows i can edit while other's i get this message.....mostly it's on the rows that i really need to edit...how can i fix it? or "force" it to edit it anyway?
ansgar's profile image ansgar posted 12 years ago Permalink
The message says it: "... data which the MySQL-server has converted silently". (But maybe we should rephrase that sentence, suggestions?)

Anyway, you cannot force HeidiSQL to apply such a change because it's the server which does this silent conversion, not HeidiSQL. HeidiSQL only informs you that the server did something you would not have expected here.

Please post your CREATE TABLE and the UPDATE statement here so I can give you a more detailed answer.
thescourgex's profile image thescourgex posted 12 years ago Permalink
Sure...if you will exsplain to me how i find the "create table" and "update statement"
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 12 years ago Permalink
The UPDATE can be copied from the Log-tab at the bottom after you changed the values.

The CREATE TABLE statement can be copied from the dialog "Advanced table properties" which you can call from the rightclick menu when a table is selected in the list from the "Database"-tab.
thescourgex's profile image thescourgex posted 12 years ago Permalink
UPDATE `gameobject_spawns` SET `Faction`=40 WHERE `ID`=29 AND `Entry`=2061 AND `Map`=0 AND `x`=-1071.02 AND `y`=-3843.59 AND `z`=136.313 AND `Facing`=2.18166 AND `o1`=0 AND `o2`=0 AND `o3`=-0.819152 AND `o4`=0.573576 AND `State`=1 AND `Flags`=0 AND `Faction`=0 AND `Scale`=1 AND `stateNpcLink`=0


CREATE TABLE `gameobject_spawns` (
`ID` int(30) unsigned NOT NULL default '0',
`Entry` int(10) unsigned NOT NULL default '0',
`Map` int(10) unsigned NOT NULL default '0',
`x` float NOT NULL default '0',
`y` float NOT NULL default '0',
`z` float NOT NULL default '0',
`Facing` float NOT NULL default '0',
`o1` float NOT NULL default '0',
`o2` float NOT NULL default '0',
`o3` float NOT NULL default '0',
`o4` float NOT NULL default '0',
`State` int(10) unsigned NOT NULL default '0',
`Flags` int(10) unsigned NOT NULL default '0',
`Faction` int(10) unsigned NOT NULL default '0',
`Scale` float NOT NULL default '0',
`stateNpcLink` int(30) NOT NULL default '0',
UNIQUE KEY `ID` (`ID`),
KEY `Map` (`Map`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_cs



-------------------------------------------------
I think this should be what you asked for :)
ansgar's profile image ansgar posted 12 years ago Permalink
Ok, the first thing is: this table is missing a primary key. It's not a must have but my guess is that you tried to achieve the uniqueness of the ID field by adding a UNIQUE key. You should definitely use a PRIMARY key instead of the UNIQUE one. You can do so using the index-editor in HeidiSQL.

The second thing is - and that is supposed to be the answer to your question:
There are some FLOAT-fields in that table. Say we have a value of 1.23456 in the field "x" and you want to update it with the value 1.234567 . The last digit (7) exceeds the precision of the FLOAT-typed field so the server cuts that digit. The value to be used for the update is now 1.23456. Which is the original value again. So, the server doesn't do anything and reports "0 affected rows" because it sees that you haven't sent any values which is effectively different from those which are already stored in the row. HeidiSQL again get this info "0 affected rows" from the server and generates the above mentioned warning message.

I don't know which value of the updated row you were editing when you got the message so my explanation could also be totally rubbish.
thescourgex's profile image thescourgex posted 12 years ago Permalink
It's not just rows in a table i get this at, some tables i can edit in, but some i get this all the time no matter what i edit
ansgar's profile image ansgar posted 12 years ago Permalink
Ah... I think I have it: As the table has no primary key, an UPDATE statements has all column names in it, including their values to make most likely that you update the correct row. You're running into a problem we discussed here sometime:
http://www.heidisql.com/forum/viewtopic.php?t=47

However, your simple workaround is to change the UNIQUE key into a primary key:
ALTER TABLE `gameobject_spawns` DROP INDEX `ID`
ALTER TABLE `gameobject_spawns` ADD PRIMARY KEY (`ID`)


After you have done that you can safely edit rows using the datagrid.
thescourgex's profile image thescourgex posted 12 years ago Permalink
Thank you! so much, diden't use the ALTER TABLE `gameobject_spawns` DROP INDEX `ID` part, only the

ALTER TABLE `gameobject_spawns` ADD PRIMARY KEY (`ID`)


And now i can edit the table happy

Your the best!
Rune's profile image Rune posted 1 week ago Permalink

I have the same problem.

However, I do not know where to find the 'create table' info. So I do not know where to change the UNIQUE key to a primary key. Can anyone direct me?

Thank you

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Rune's profile image Rune posted 1 week ago Permalink

I think my database is even telling me which fields are locked. It seems here that "spawnid" and "waypointid" are the ones blocking me, right?

1 attachment(s):
  • Capture
ansgar's profile image ansgar posted 1 week ago Permalink

Man, what's the version of HeidiSQL you were using to create that screenshot? If you like to get support, uninstall that old one and install the current release please.

Please login to leave a reply, or register at first.