On Thu, 2006-06-22 at 16:01 +0400, Andrey Panin wrote:
On 169, 06 18, 2006 at 07:31:05PM +0300, Timo Sirainen wrote:
On Sun, 2006-06-18 at 19:59 +0400, Andrey Panin wrote:
Here is a big problem with transaction support in MySQL driver: it simpy doesn't work :( sql_transaction_commit_s() function fails and MySQL reports this error: 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 ';UPDATE ...
Looks like mysql_query() chokes if query contains semicolon.
Did you really test with the few days old code? Because I added this which fixed it for me:
#ifdef CLIENT_MULTI_STATEMENTS /* Updates require this because everything is committed in one large SQL statement. */ db->client_flags |= CLIENT_MULTI_STATEMENTS; #endif
Yes, it works with MySQL >= 4.1. Seems like I need to upgrade MySQL on my home computer.
I thought earlier versions of MySQL allowed multi-statements by default.. Or is it maybe earlier versions of the server?
But sql_update() still fails, because mysql_store_result() returns NULL if the query didn't return data. Of course UPDATE queries do not return data. Attached patch solved this problem for me, but I'm not sure how good it is for common use. Please take a look.
Right. I fixed this a while ago also by checking mysql_errno() return values.
IMHO we must just use MySQL BEGIN/COMMIT/ROLLBACK and let users care about transaction safety of their databases.
I don't think that's such a good idea, because if transactions aren't supported (and isn't that how MySQL is most commonly still used?) the rollback command does nothing and that can cause problems (like wrongly updating quota).
Currently users with MySQL < 4.1 will get obscure SQL syntax error message and think "Where this fscking semicolon came from ?!". May be we should somehow notify user that update isn't actually supported...
The good thing is that currently the only place in Dovecot where SQL updates are done is the dict quota backend, which not many people are using. :) Would be nice to get proper error message about it, but I'm not really sure how to do it. Especially if the only broken combination is MySQL client < 4.1 + MySQL server > 4.1.