7

I have extremely strange problem, which drives me crazy all day. I have a simple MySQL table with few columns. One column is int(11) NULL. When I update its value, it works as expected. However, when I update its value second time, it gets assigned "0" value.

I have tested this same behaviour on my MySQL 5.1.58-1ubuntu1 and on other MySQL 5.0.96-community and both behave exactly the same. So it apparently is not problem of one version MySQL.

It is difficult for me to explain, but I have attached 2 screenshots which will tell you much better where is the better.

First screenshot is structure of my table I am updating:

table structure

And here is shown SQL queries beeing executed, where you can see, that first update is correct and the second produces "0" value in column "invoice_number" with no reason:

enter image description here

Am I overlooking something obvious ? It really drives me crazy, because it doesn't make any sense to me...

Thank you for any help in advance...

EDIT: I have tried using only numbers in my queries and this is result (very strange for me as well):

enter image description here

Frodik
  • 14,986
  • 23
  • 90
  • 141
  • +1 for strange problem, which drives me crazy all day..I know this feeling – Mudassir Hasan Nov 20 '12 at 14:49
  • 2
    Why are you specifying strings in your code? `'35'` is a string, and `35` is a number. Why are you not doing `= 35 WHERE id = 55`? – MatBailie Nov 20 '12 at 14:49
  • 4
    Do **not** use character literals `'55` for numeric columns. `'55'` is not a number, it's string. You should use `55` (and I wouldn't be surprised if the weirdness stems from some strange implicit type casting) –  Nov 20 '12 at 14:49
  • You seem to be using phpMyAdmin or similar tools. Did you run all the queries from that textarea at the bottom of your second screenshot? If so it could be the queries are being cached and you get results from cache (which would mean a bug in your tool, not MySQL). Did you try running these queries separately, one at a time? – JScoobyCed Nov 20 '12 at 14:51
  • @Dems I Agree, but isn't it the server automatically tries to parse string into number? – John Woo Nov 20 '12 at 14:51
  • @sailingthoms Good idea, I have tried, but the 3rd try is the same as 2nd – Frodik Nov 20 '12 at 14:52
  • @JScoobyCed I am using Adminer (www.adminer.org), but only so I could show it nicely on SO. Initially I have discovered this problem in my PHP application and was looking for my mistake in my application. However I have easily replicated this problem outside my app as you can se... – Frodik Nov 20 '12 at 14:53
  • did u try using numbers instead of strings, as Dems and a_horse_with_no_name mentioned? – sailingthoms Nov 20 '12 at 14:54
  • Ok. Is there any caching/plugins in MySQL you're using that could result in unsync data? – JScoobyCed Nov 20 '12 at 14:56
  • what if u update only once - does the number reemain unchanged or does it change back to 0 after some time? – sailingthoms Nov 20 '12 at 14:58
  • @sailingthoms I have tried using numbers as you suggested, however even more strange problem has occurred, I will upload picture of it in my question shortly. – Frodik Nov 20 '12 at 14:58
  • by the way, does `NULL` mean your default value or null is allowed for this colum? – sailingthoms Nov 20 '12 at 14:59
  • @sailingthoms NULL means "is allowed" for this column – Frodik Nov 20 '12 at 15:01
  • Are there any triggers activated by your updates? `show triggers` – FoolishSeth Nov 20 '12 at 15:07
  • What happens if you remove the backticks from the update statements around the invoice_number column name? – Jeff Siver Nov 20 '12 at 15:08
  • @FoolishSeth No triggers at all :-( – Frodik Nov 20 '12 at 15:09
  • @JeffSiver Doesn't have effect on anything. Behaves the same with and without backticks – Frodik Nov 20 '12 at 15:10
  • what about table engine? your machine regional setting (encoding, character set)? – JScoobyCed Nov 20 '12 at 15:11
  • Could it be that some other application is accesing your database at the same time, updating the column value to 0?. As sailingthoms asks, what happens if you just execute the first update and wait for a while. Does it reverts back to 0? – richardtz Nov 20 '12 at 15:11
  • one stupid idea: remove the blanks next to `=`. It seems your tool interpretes the value after `=` as column name and not as value. – sailingthoms Nov 20 '12 at 15:12
  • Do you need the columns to be 'unsigned' int. Your data range shouldn't matter but I think you can explore this too – JScoobyCed Nov 20 '12 at 15:14
  • @richardtz I have tried that, I diveded two updates in to different requests with a minute pause between them and it still behaves the same as run as in one batch – Frodik Nov 20 '12 at 15:15
  • @sailingthoms Tried it, doesn't help :-( – Frodik Nov 20 '12 at 15:16
  • @JScoobyCed Tried changing unsigned columns to signed, but no difference. – Frodik Nov 20 '12 at 15:17
  • Stupid behavior... I tried it on a mysql system and it perfectly worked. is your tool (Adminer) working properly? Maybe there is a bug on that layer not in your DB. – sailingthoms Nov 20 '12 at 15:20
  • @sailingthoms As I said earlier in comments, firstly I discovered this error in my PHP application and was blaming it. But then I easily replicated this error also in Adminer. So problem is somewhere else than in Adminer or my app. – Frodik Nov 20 '12 at 15:24
  • @Frodik I read about it, i mean if you tried it now without your tool. It is possible that there are different bugs or errors. U can try it from command line [mysql from cmd](http://stackoverflow.com/questions/5131931/connecting-to-mysql-from-the-command-line) – sailingthoms Nov 20 '12 at 15:27
  • @sailingthoms I have tried it running in Adminer and also outside it as you suggested in mysql sql console, still the same behaviour :-( – Frodik Nov 20 '12 at 15:32
  • Trying to think out of the box, but do you need to `COMMIT` after your `UPDATE`? – BellevueBob Nov 20 '12 at 15:45
  • @BobDuell But I'm not using transactions. Commit is called automatically after each query by MySQL in background. But even though I have tried adding COMMIT command after each UPDATE. But it expectedly did not work :-( – Frodik Nov 20 '12 at 15:49
  • @Frodik, can you try with different values other than 35? I find really weird the SQL error when using 35 and not with 30. – richardtz Nov 20 '12 at 16:25
  • @richardtz Value doesn't matter. The first UPDATE query gets executed and the result is as exptected, however the second UPDATE query puts "0" in column, whatever the updated value is. – Frodik Nov 20 '12 at 16:31
  • @Frodik, I mean executing it as a number, the same way you executed with 35 and got the SQL error. Another test I think may be interesting is executing twice the query with value 30 (with numeric values) and see what happens. – richardtz Nov 20 '12 at 17:43

2 Answers2

2

This isn't an answer, but the comments are getting crowded. The behavior you show does indicate some sort of problem, however more likely in the tool you are using to interface to the database rather than in the database.

The issue with the single quotes is a red herring (i.e. distraction). Even if '55' were interpreted differently from 55, it would be interpreted the same way in the where statement. Also, '35' worked but '30' didn't.

A key insight is the failure when you remove the quotes. The statement:

update i_orders
    set invoice_number = 30
    where id = 55

should not be looking up a column index for 30. The unknown column error you are seeing suggests that the query sent to the database is not the one you see in the tool.

Can you log the queries in the database to see what is actually being sent in?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Logging queries is exactly what I did when I was going crazy earlier today. And those 4 queries shown in second screenshot are exactly copied lines from MySQL log. – Frodik Nov 20 '12 at 15:20
  • The integer problem is more curious. MySQL should not be looking for a column reference in the `set` statement. A field list sounds like it belongs after the table name in an insert. I don't see how to parse your query to get that error. Nor why the string would not cause this problem. – Gordon Linoff Nov 20 '12 at 15:25
0

Ok, I finally figured it out. The problem was somewhere else, not in MySQL itself. I have copied all those SQL queries shown in my question from MySQL query log, so I never suspected there could be something weird. However there was - UTF-8 Byte order mark (EF BB BF) at the beginning of the "35" value and it was, of course, not visible.

The first update query had its origin from different place of my app and its value "30" was only string(2). But when I did var_dump on values in the second query, I noticed that "35" is actually string(5). This value came from jQuery's $.get() call and had this BOM attached to it ...

Two days of life spent on such an "invisible"...

Frodik
  • 14,986
  • 23
  • 90
  • 141