0 rows updated when should have been 1

[expired user #8034]'s profile image [expired user #8034] posted 10 years ago in General Permalink
I do not understand the position of HeidiSQL on this issue. Millions of people have been battling this problem for at least a decade. I get this error when I enter a 1 into an integer column where I previously had a 0 and vice versa. What can go wrong between 0 and 1? The table has 11 (!!!) columns and 17 rows. About once every third attempt, this annoying message pops up. I click the message away, then press the F5 key, then enter the same value again and the update works perfectly. So there is absolutely nothing wrong with the table definition if after refreshing HeidiSQL, the update actually works. If I run a PHP update against the same table, there is not even the slightest hint of a problem. This occurs only when I attempt to update the database manually. The problem clearly is with HeidiSQL.
ansgar's profile image ansgar posted 10 years ago Permalink
The position of HeidiSQL on this issue? Please ask what you find inappropriate. I have no position on this thing. But there is no real issue in HeidiSQL here. The message is a notification for the user.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Ok, Ansgar. Here we go. I say there is definitely an issue. It is an ERROR!!!!! If your software fails, then I have to reset it to get to work. I call that a bug. There is definitely a POSITION that HeidiSQL seems to have taken: it is a position of denial! You won't even admit that this is a severe annoyance and inconvenience for the user. You say I should state what I find inappropriate? How about a manual update that fails 33% of the time? Why does that happen? And more appropriately, why are you attempting to evade the issue as has been done for almost ten years? This message SHOULD NOT APPEAR!!! If I am doing something inappropriate with your software, then what is it? Why does your software work only randomly? I am a software developer myself. This is not a "notification for the user", this is a clear error in the HeidiSQL software. You cannot convince me for a moment that a software product that tries to update a simple zero to a value of one, and fails to do so simply provides "a notification to the user" (das ist absoluter Quatsch!).
ansgar's profile image ansgar posted 10 years ago Permalink
Hast du überhaupt verstanden was diese Meldung meint? Bitte erklär malsmileWas meinst du mit einem "manuellem Update"? Heidi versucht zuerst die Primärschlüssel deiner Tabelle für die WHERE Bedingung zu verwenden, dann UNIQUE keys, und wenn davon auch keine vorhanden sind, wird sogar nochmal versucht einfach alle Felder in die WHERE Bedingung einzubauen. Und ERST DANN passiert das manchmal. Aber die Ursache ist meist eine andere, wie auch bei dir, da sind es meist die "silent server changes", die dazu führen - du gibst zum Beispiel einen negativen Integer Wert in einer UNSIGNED Spalte ein, und MySQL (NICHT HEIDISQL!!!) "korrigiert" das automatisch zu einer "0" die bereits vorher dort stand. Damit hast du effektiv 0 betroffene Zeilen anstatt 1.

Anstatt dich hier über kostenlose Software zu beschweren, könntest du konstruktiv sein. Auf solche ätzenden Kommentare kann ich hier verzichten.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Ok, Buckaroo! I understand your technical explanation of unique keys, primary keys, server voo-doo, and everything else. Am Ende ist aber die Tatsache, deine Software soll all diesen Dingen doch ABFANGEN! So let's agree that you are in denial that your software should catch and respond to server anomalies, and leave it at that. By the way, a manual update means exactly that: I call up a table on the screen. Then click on a table row. I type in (manually) a 1 where a 0 is shown. That is a manual change. Got it? Now here is the amusing part: often your software cannot handle this simple change. When I bring your attention to it, you try to convince me that this is not a software error on your part: "...die Ursache ist meist eine Andere". Right! And the issue has nothing whatsoever to do with negative integers. I made it absolutely clear: we are dealing with a simple zero value and a simple one value. Keep on denying that there is a problem with your software. How do you explain the fact that when I execute the very same UPDATE statement from within PHP software that the problem NEVER occurs, but when I try to do the very same thing with your software, it sometimes works and sometimes does not? Anstatt dich zu verteidigen solltest du vielleicht die "ätzende Kommentare" tatsächlich nachschauen. Ich finde deine Software ganz ausgezeichnet! Deswegen benutze ich die überhaupt. Ich bin einfach genervt, wenn ich einen manuellen Eintrag an der Benutzeroberfläche versuche zu machen und der Eintrag scheitert (warum auch immer). Vielleicht können wir gemeinsam einen Weg finden wobei diese Serveranomalien abgefangen werden können. Du wirst eine verbesserte Software haben und ich reisse mir die Haare nicht mehr aus!
kalvaro's profile image kalvaro posted 10 years ago Permalink
I don't have the faintest idea of what all this is about (this thread reminds me of those we used to get some time ago where the author would always assume that all HeidiSQL users are full-time World of Warcraft gamers). I suspect the OP is using a MySQL server that is configured with a legacy SQL Mode, thus his data is being magically fixed. My remarks:

1. If you think you've found a bug in HeidiSQL, please provide a usable bug report that includes a clear explanation of the issue and steps to reproduce it. If you are a MySQL user you probably work in the IT industry: could you do anything with a user report that says "program doesn't work, fix it ASAP!!!!" and nothing else?

2. PHP is just a programming language. The language itself will neither warn or fail to warn about SQL errors: your code will. If you browse Stack Overflow you'll be surprised to discover how many developers omit error checking and even configure PHP to hide error messages. While it isn't entirely the language's fault, you definitively can't praise it for that and put it as model.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Look guys. I outlined the error procedure exactly, so don't come back with more "it's not our fault" evasions. I have all my SQL statements in try-catch blocks, and they never return errors when I change an integer value from a simple zero to a one. I have never had a problem with MySQL in Java, PHP, Visual Basic, Perl, and other languages. All these languages can change a zero to a one successfully, only your software fails often to do so. I have outlined exactly what I did to cause the error and even offered to help in finding the solution. That was the last part of my previous entry (in German). And what I didn't mention is that I have the latest version of MySQL and the problem occurs also with text data. I try to change an "a" to a "b" and it sometimes works, and sometimes fails. Forget it. I tried to make you aware of an issue and you seem to be doing every possible to avoid and evade the issue. Sorry I even mentioned it. So you can relax, you will not hear from me again.
kalvaro's profile image kalvaro posted 10 years ago Permalink
Please accept my excuses for trying to help a fellow user of a program I'm not affiliated with. I mistakenly thought you were trying to get a bugfix or something. I hope you've released all your frustration ranting strangers, which is apparently your only goal.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Sorry Kalvaro. From the tone of your blog entry, I mistakenly thought you work with ansgar. My frustration is with his "explanation". Any software product that can't change a simple 0 to a 1 and back again definitely has a problem and ansgar absolutely will not admit to it. He goes into a long-winded and somewhat insulting explanation in German as to what might or might not be going on in the background. No user needs to be concerned with that. That is where my frustration lies. I have produced scores of websites using MySQL over the years, and never have had a problem doing software database updates. I try to change a table value by hand using the HeidiSQL client, and it is hit or miss as to whether it works or not, and then the software vendor tries to talk his way out of it? Changing a 0 to a 1? Then he writes to me that I am publishing offensive comments about his "free" software? That is an attempt to divert attention away from the core problem of changing a simple zero to a one. Please accept my heartfelt apologies if I have offended you, but this issue is beyond ludicrous!
ansgar's profile image ansgar posted 10 years ago Permalink

No user needs to be concerned with that.


Is that what you intended to say the whole time?
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
genau!
ansgar's profile image ansgar posted 10 years ago Permalink
Das passiert doch total selten eigentlich?
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Nein Ansgar. Eigentlich passiert das relativ oft. Es ist egal, ob am Hauptrechner oder am Notebook. Die Tabelle ist wirklich recht einfach. Die hat nichts besonders an sich. Nur elf Spalten und vierzehn Reihen.
Schau mal nach dem CREATE-Befehl:
CREATE TABLE `xxxxxx_xxxx_xxxx` (
`yyyy_yyyy_yyyy` INT(10) NOT NULL,
`internal_id` INT(11) NOT NULL,
`name` VARCHAR(500) NOT NULL,
`owning_node_id` INT(11) NULL DEFAULT '0',
`owning_node_index` INT(11) NULL DEFAULT '0',
`top_node_indicator` TINYINT(1) NOT NULL DEFAULT '0',
`active_indicator` TINYINT(1) NOT NULL DEFAULT '0',
`version` INT(11) NOT NULL DEFAULT '1',
`modified_by` VARCHAR(50) NOT NULL,
`modification_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modification_description` VARCHAR(500) NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

Eine INT- oder TINYINT-Spalte kann eine Abwechselung zwischen Null und Eins mit umgehen, oder? Wie gesagt, wenn ich eine Eins z.B. in der Spalte top_node_indicator oder owning_node_id eintippe (egal in welcher Reihe), es kommt oft die ärgerliche Meldung. Aber unter normalem Logikfluss, wird niemals ein SQL-Fehler gemeldet.

Sag mir bitte bescheid, wenn ich weiter behilflich sein kann.
ansgar's profile image ansgar posted 10 years ago Permalink
Ein UPDATE auf diese Tabelle schlägt fehl, was die besagte Fehlermeldung erzeugt (0 rows updated...), wenn du die zu aktualisierende Zeile gerade erst eingefügt hast. Das liegt daran, dass HeidiSQL für Tabellen ohne Primärschlüssel sämtliche Spaltenwerte in die WHERE Bedingung übernimmt. Dabei fehlt aber noch der vom Server generierte Wert für die TIMESTAMP Spalte (modification_date). Also macht man ein UPDATE mit einem WHERE mit leerem Wert für "modification_date", der überhaupt nicht existiert, weil der Server längst einen automatischen Wert eingetragen hat.

Lösungen:
* Entweder F5 drücken vorm Editieren, dann holt Heidi alle aktuellen Werte in das Grid
* Oder einfach (und sauber): Primärschlüssel in der Tabelle anlegen. Dann nimmt HeidiSQL den auch für die WHERE Bedingung anstatt alle Spaltenwerte.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Danke Ansgar. Ok. Das macht Sinn. Wie ich verstehe, Kern der Sache ist ein Primärschlüssel anzulegen, richtig?
(Übrigens, leider kommt die Meldung egal, ob die Zeile neu oder alt ist.) Ich probiere's mit dem Primärschlüssel trotzdem aus und melde mich.
ansgar's profile image ansgar posted 10 years ago Permalink
Ja, Primärschlüssel anlegen ist in jeder Tabelle ratsam. Nicht nur für UPDATEs, auch DELETEs werden damit sicherer.

Gut dass wir dass in Ruhe klären konnten. Der Start dieses Threads war wirklich etwas unglücklich.

For the others: Everyting peachy now. His table was lacking a primary key, and due to automatic TIMESTAMP values on the server, HeidiSQL's UPDATE failed to get the right row.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Ansgar is correct. I defined a primary key field for the table, then did over 20 manual updates and not a single one of them failed. Super analysis Ansgar, thanks!

Noch eine Sache die weder Bug noch Beschwerde ist :)
Gibt es eine Möglichkeit die "DEFINER="-Einträge beim Datenbankexport für Views, Funktionen, usw. zu unterbinden? Der Server beim Provider benutzt PHPAdmin zum Datenbank-Upload und der kann nicht imer damit umgehen. D.h. ich kann kein automatisierten Upload machen weil ich manche "DEFINER="-Einträge per Hand entfernen muss. Wenn möglich, wäre es schön. Aber wenn nicht, dann halb so wild. Wenn ich die Zeit habe, schreibe ich selber eine kleine Routine um die Einträge zu entfernen.
ansgar's profile image ansgar posted 10 years ago Permalink
Nein, das macht HeidiSQL bisher nicht. Ich höre das aber nicht zum ersten mal glaube ich... jo, siehe issue #3496.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Ok. Keine Ursache. Trotzdem danke Ansgar.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Hallo Ansgar,

ich habe ein paar "Unebenheiten" in der englischen Grammatik gefunden. Da ich selber Amerikaner (also Native-Speaker) bin, biete ich dir ein paar Vorschläge an. Das würde ich lieber offline erledigen. Meine Emailadresse ist natürlich in deiner Datenbank.
ansgar's profile image ansgar posted 10 years ago Permalink
Ja, gern. Meine Email Adresse findest du auf der "Imprint" Seite, unten im Fußbereich verlinkt.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Hello Ansgar,
I am trying to update an existing table definition, and I am getting a message "Invalid use of NULL" in the new DATE column definition. The "Allow NULL" checkbox is empty, and the default is set to "No default" (to make it mandatory). What am I missing? (The table does have a primary key.)
ansgar's profile image ansgar posted 10 years ago Permalink
If it's an existing column, then you probably have NULL values in it now? Not sure if that's causing that error. Googled for it?
ansgar's profile image ansgar posted 10 years ago Permalink
Oh, do we still talk about MySQL or is it MSSQL?
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
It's MySQL. It is an existing table, but a new column. I was not able to save the table until I checked the "Allow NULL" box for the new column. This is not so bad, because the software validation routines will catch any null or blank screen values. I was wondering why it is necessary to have to check the "Allow NULL" box, when I might not want to do so at some time in the future.
ansgar's profile image ansgar posted 10 years ago Permalink
Google returns a few solution sites, firstly this one, but that one says that the message "Invalid use of NULL" comes in when somebody tries to apply a primary key to a column which has NULL values in it. You said you are just trying to add a date/time column, and there is a primary key elsewhere but not on that new column. So I am stuck here.
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Ok. First of all, thanks for digging into this. There is a workaround, simply by checking the "Allow NULL" checkbox. Nochmal vielen Dank für deine Mühe!! smile
[expired user #8034]'s profile image [expired user #8034] posted 10 years ago Permalink
Hi Ansgar. A quick update: I went back into the table to examine the contents of the new DATE column. I was surprised to see old dates as well as NULL values in the new column. This leads me to believe that the column was defined previously, then removed at some time in the past. When I tried to insert a new column which happened to have exactly the same name, the legacy values reappeared somehow. Among those old values were NULL values. Now the message makes perfect sense. If the "Allow NULL" checkbox is empty, then the legacy NULL values will throw an error. HeidiSQL correctly carries this message up to the presentation layer so I can see it (while I look at the screen totally baffled and scratch various as yet undisclosed body parts.)
ansgar's profile image ansgar posted 10 years ago Permalink
Undisclosed body parts ?? Well, hope you did not hurt yourself too much, man! :)
ansgar's profile image ansgar posted 10 years ago Permalink
To get serious again: I never came across such legacy columns appearing again out of a trash bin or whatever. Good finding, man! Probably that helps others reading this thread.
[expired user #8034]'s profile image [expired user #8034] posted 9 years ago Permalink
Hallo Ansgar,

jetzt, habe ich noch ein Phänomen. Ich versuche ein INSERT durchzuführen. In einer Spalte befindet sich ein Text 15M gross (obwohl die Spalte als MEDIUMTEXT definiert wurde). Leider kriege ich einen SQL-Fehler (1118) Row size too large (>8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.


Hier ist die Tabellendefinition:
CREATE TABLE `help_topics` (
`help_topic_index` INT(10) NOT NULL,
`version` INT(11) NOT NULL DEFAULT '1',
`internal_id` INT(10) NOT NULL,
`active_indicator` TINYINT(1) NOT NULL DEFAULT '1',
`topic` VARCHAR(500) NOT NULL,
`text` MEDIUMTEXT NOT NULL,
`owning_node_id` INT(11) NOT NULL,
`owning_node_index` INT(11) NULL DEFAULT '0',
`pac_indicator` TINYINT(1) NULL DEFAULT '0',
`modified_by` VARCHAR(50) NULL DEFAULT NULL,
`modification_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`modification_description` VARCHAR(500) NOT NULL,
PRIMARY KEY (`help_topic_index`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

Any suggestions?
ansgar's profile image ansgar posted 9 years ago Permalink
MEDIUMTEXT erlaubt eigentlich bis zu ca. 16,7 MB Inhalt. Trotzdem bekommst du diesen "Row size too large" beim INSERT? Na, er sagt ja auch "*row* size too large", also die Zeile ist insgesamt zu groß. Hast du mal versucht auf ROW_FORMAT=DYNAMIC oder ROW_FORMAT=COMPRESSED umzustellen? In HeidiSQL geht das ganz einfach über den "Options" Reiter im Tabelleneditor.
[expired user #8034]'s profile image [expired user #8034] posted 9 years ago Permalink
Hallo Ansgar,
das Problem scheint gar nichts mit der Datenbank zu tun sein. Sondern eine Einschränkung des Servers. Also, an diesem Punkt "nothing to do". Aber, jetzt kriege eine Fehlermeldung vom HeidiSQL Query-Reiter beim einfachen Befehl: LOCK TABLES xxx;

Darf man kein LOCK-Befehl in der Reiteroberfläche abgeben?

Again, this is one of those things you look at, then begin absent-mindedly scratching body parts that you wouldn't dare to touch while sitting in church. The lock-command works perfectly when embedded in the source code. What's going on? Any ideas?
ansgar's profile image ansgar posted 9 years ago Permalink
Welcher Fehler kommt denn beim LOCK TABLES ?
[expired user #8034]'s profile image [expired user #8034] posted 9 years ago Permalink
Ich habe Folgendes eingegeben:
LOCK TABLES projects;

Die Meldung lautet:
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.

Komisch, nicht?
ansgar's profile image ansgar posted 9 years ago Permalink
Da fehlt der lock_type. Aus der Doku:
Syntax:
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE


Versuch mal "LOCK TABLES projects WRITE"
[expired user #8034]'s profile image [expired user #8034] posted 9 years ago Permalink
Ach. Ich Idiot. Natürlich! Danke Ansgar. Der nächste Kaffee geht auf meiner Rechnung.

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