Hello, i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers dovecot internally uses sql: "ON DUPLICATE KEY UPDATE" which was introduced in mysql 4.1 Is there a chance to make this statement mysql 4.0 compatible or should i update mysql? i want to avoid updating mysql because some old software is running which is not compatible to higher mysql versions. Thanks, Andre
On Thu, 25 Mar 2010 10:24:37 +0100, Andre H?bner <andre.huebner at gmx.de> articulated:> i use successfully mysql-quotasystem with dovecot 1.2.11 on different > servers except for mysql 4.0 servers > dovecot internally uses sql: "ON DUPLICATE KEY UPDATE" which was > introduced in mysql 4.1 > Is there a chance to make this statement mysql 4.0 compatible or > should i update mysql? i want to avoid updating mysql because some > old software is running which is not compatible to higher mysql > versions.In my opinion, continued use of depreciated software is a practice that should be avoided whenever possible. Inevitably, it causes problems as you are now experiencing. I am somewhat surprised that the older software is not compatible with the newer version of MySQL. Usually, it is the other way around. Have you checked to see if newer versions of the incompatible software exist? I would be willing to wager that they have been updated to accommodate the newer MySQL versions. -- Jerry Dovecot.user at seibercom.net Disclaimer: off-list followups get on-list replies or get ignored. Please do not ignore the Reply-To header. __________________________________________________________________ There's a fine line between courage and foolishness. Too bad it's not a fence.
Hello,>> I am somewhat surprised that the older software is not compatible with >> the newer version of MySQL. Usually, it is the other way around. Have >> you checked to see if newer versions of the incompatible software >> exist? I would be willing to wager that they have been updated to >> accommodate the newer MySQL versions.Syntax changed a litte bit and also there are some new protected words which are used by older software in sql without backticks etc. I agree updating would be the better way but we talking about a big number of servers and customers still using it. this is not changed over night.... I looked into dovecot-1.2.11/src/lib-dict/dict-sql.c, there seems to be a check for "ON DUPLICATE KEY UPDATE" on line 588. I recompiled dovecot on mysql 4.0 machine and tried again but there was no change. SQL is successful when dataset in quotatable is existing. in this case dovecot triggers an update of the data. But if no line for user is existing in quotatable and an insert is needed there is still the error: sql dict: commit failed: 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 'ON DUPLICATE KEY UPDATE bytes='12525'' at line why is the mysql 4.0-recompiled dovecot thinking my mysql is able to do "ON DUPLICATE KEY UPDATE"? How to avoid this? Thanks, Andre
(sorry, i only read the digest version of list and always create i new thread by "fake-Re") Hello,>> Perhaps you can implement some updating procedure that >> would only involve a percentage of your users at one time.no way, i want to fix this! but i need help because i have no idea of c-programming. i can read the code a little bit, but never lerned it. solution is to edit dovecot-1.2.11/src/lib-dict/dict-sql.c i can avoid using ON DUPLICATE KEY UPDATE by changing line 91 to: orig: dict->has_on_duplicate_key = strcmp(driver->name, "mysql") == 0; changed: dict->has_on_duplicate_key = strcmp(driver->name, "mysqlXXX") == 0; this works only 50%. if a user has no entry in quotatable, dovecot is updating his quota with 2 queries: INSERT INTO quota (bytes,username) VALUES ('2456','myuser') INSERT INTO quota (messages,username) VALUES ('2','myuser') The first query works, the second throws an error because username-field is uniqe. Duplicate entry 'myuser' for key 1 So i have to find a way to make this query in one step: INSERT INTO quota (bytes,messages, username) VALUES ('2456','2','myuser') or turn the second insert into an update update quota SET messages='2' where username='myuser' But iam afraid this is too much for my c-skills. I need help of an C-Crack or timo as developer. if timo could create a switch for mysql 4.0 would surely be the best. Thanks, Andre
On 25.3.2010, at 11.24, Andre H?bner wrote:> i use successfully mysql-quotasystem with dovecot 1.2.11 on different servers except for mysql 4.0 servers > dovecot internally uses sql: "ON DUPLICATE KEY UPDATE" which was introduced in mysql 4.1The original code supported dict-sql only if ON DUPLICATE KEY UPDATE was supported by the server. Later I added support for PostgreSQL and SQLite by having them use INSERT triggers that update the data if it already exists. I suppose MySQL 4.0 doesn't support these kind of triggers either? Writing code manually to first try to UPDATE and if it fails then INSERT would be possible.. But too much trouble for me to spend time on.