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. 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.
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...
-- Andrey Panin | Linux and UNIX system administrator pazke@donpac.ru | PGP key: wwwkeys.pgp.net