error on Update Query

[Eugenio]

bob wrote:
>
> I have now tried this on every database on my system. The
> exact same error occurs with the update on every one. I have
> made numerous new test databases, the most minimal one only
> has one field. Same problem on the update. Add and delete
> work fine. MySql and phpMyAdmin work flawlessly an every
> database on my system.

Dave, Bob,
your problem is very curious, I can't replicated it.
Can you re-post the information about your systems (OS/PHP version/MySQL version)?

(Latest version of DaDaBIK when this message was posted: 1.6)

 

[dave]

Hi Eugenio!

MySQL 3.22.32

PHP Version 4.0.3pl1

System Linux ns2.he.net 2.2.12 #1 SMP Fri Oct 1 00:58:41 PDT 1999 i686
Build Date Oct 27 2000
Configure Command './configure' '--with-apache=//home/mleber/src.he.net/apache-1.3.6.work/apache_1.3.6' '--enable-track-vars' '--enable-magic-quotes' '--with-mysql' '--with-gd' '--with-ttf' '--enable-ftp' '--with-zlib' '--with-db'
Server API Apache
Virtual Directory Support disabled
Configuration File (php.ini) Path /usr/local/lib
ZEND_DEBUG disabled
Thread Safety disabled

Any luck with the extended error messages?
http://www.dw-graphics.com/dbtest/program_files/index.php

For my test that's bombing on the updates! Same as Bob, runs flawlessly with dbase calls and phpMyAdmin!

I'll be out Thurs-Mon, check back on the progress then!

Regards,
D-

 

[bob]

Eugenio, here you go...

FreeBSD 4.4-STABLE
Apache/1.3.22 (Unix) PHP/4.0.6 mod_ssl/2.8.5 OpenSSL/0.9.6a
PHP Version 4.0.6
MySQL 3.22.27
MySQL 3.22.27
phpMyAdmin 2.2.3

I am in the process of setting you up an account on my server. Hopefully, that will allow you to see what is happening. I will email the login information if you think this will help.

-Bob

 

[bob]

Hi Eugenio,

Per my last post... I have now set up an account for you on our server. I have emailed you the login information. A test database has been set up for you. Dadabik and phpMyAdmin are already installed in your directory. All you need to do is log in.

If there is anything lese I can do, just le me know. Hope this helps.

-Bob

 

[Eugenio]

bob wrote:
>
> Hi Eugenio,
>
> Per my last post... I have now set up an account for you on
> our server. I have emailed you the login information. A test
> database has been set up for you. Dadabik and phpMyAdmin are
> already installed in your directory. All you need to do is
> log in.
>
> If there is anything lese I can do, just le me know. Hope
> this helps.

Thanks a lot Bob!!
I've finally found where the problem is.

Your MySQL don't like the limit clause in the update query,
e.g.
update test set ID = 2 where ID = 2 limit 1
works fine on my PHPMyAdmin (both on a Win box and a Linux server)
but produce this error:
You have an error in your SQL syntax near 'limit 1' at line 1
in your PHPMyAdmin.

DaDaBIK uses limit 1 for additional security during update procedures.

Workaround: you can avoid "limit 1" by changing the line 921 of business_logic.php from:
$sql .= " where $where_field = '$where_value' limit 1";
to
$sql .= " where $where_field = '$where_value'";
of course in this case you'll have less security on update.

I don't know why in some cases MySQL returns error, maybe it depends on the version; Dave, Dug what about you? I guess that the problem is the same.

(Latest version of DaDaBIK when this message was posted: 1.6)

 

[bob]

Hi Eugenio,

That's it. Works perfectly now. Thanks for fixing it and glad I could help.

 

[Dave]

Eugenio!

That's it for me also! Works great eliminating the "limit 1"!

Thanks again for all the help, great interface!

D-

 

[Colin]

Hi all,

just read this thread about the UPDATE problem. Somewhere in the middle I already guessed this LIMIT problem in the UPDATE clause (looked somewhat unfamilar to me ;-).

I just looked into the MySQL manual, a LIMIT # at the end of an UPDATE clause is allowed only with MySQL 3.23.x and as far as I can see Dave and Bob both use 3.22.x so that's the point. I'm using 3.23.49 here, it work's fine.

Anyway, your tool is great, I was just in the mood to begin writing something similar, but a minute before I gave freshmeat.net a quick look and found that one.

Regards,
Colin

 

[Eugenio]

Colin wrote:

> just read this thread about the UPDATE problem. Somewhere in
> the middle I already guessed this LIMIT problem in the UPDATE
> clause (looked somewhat unfamilar to me ;-).
>
> I just looked into the MySQL manual, a LIMIT # at the end of
> an UPDATE clause is allowed only with MySQL 3.23.x and as far
> as I can see Dave and Bob both use 3.22.x so that's the
> point. I'm using 3.23.49 here, it work's fine.
>
> Anyway, your tool is great, I was just in the mood to begin
> writing something similar, but a minute before I gave
> freshmeat.net a quick look and found that one.

Thanks for your appreciation Colin, and thanks a lot to have pointed out exactly the cause of the bug, I'll fix it in the next release of DaDaBIK.

(Latest version of DaDaBIK when this message was posted: 1.7)