4 ms·
1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won'
by einhverfr 14y ago
1) can applications set the SQL_MODE themselves? Can an admin configure the server so applications cannot specify mode? If not, what good is it since it won't guarantee your data?
2) My larger frustration with MySQL is I have run into cases of single transactions deadlocking against themselves. These always happen when the following is true:
* Executing an insert statement in the form of INSERT foo (bar) VALUES (1), (2), (3), (4);
* Only one connection/session active at a time (for example during a data migration to MySQL)
* Frequency goes up when more rows are inserted per statement
* Which inserts trigger the deadlocks are not reproducible
I believe this is an issue with race conditions and threads, perhaps a lock contention that isn't being handled properly between index and table writes or the like. I can reproduce it by inserting a couple million rows into a table, a few thousand at a time, but the statements where this occurs varies from one run to the next.
I have never seen braindead locking behavior on PostgreSQL.
- epimenov 14y agoThere definitely was braindead locking behavior on PostgreSQL at least once: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg157869.html http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg...
- einhverfr 14y agoThat certainly is odd behavior however reading through the whole email thread that seems to be one complicated issue with a two processes obtaining different locks to the same row. Probably not ideal, but maybe I need to recalibrate my definition of braindead because in real-world examples, that would be highly annoying.
- epimenov 14y agoIt happened to us in the real world. And was fixed in 9.2
- einhverfr 14y agoouch. Reminds me of the (one) data corruption bug I found in PostgreSQL 7.3, allowing me to create write-only tables....
- jeltz 14y agoActually it is not fixed yet, the patch was pushed to 9.3 and Alvaro is still working on it. It has happened to us too, though it was very easily fixed in our case due to the excellent deadlock reporting. And I can also confirm that the case for Joel (the original reporter) is also a real world case. Link to talk about it http://www.pgcon.org/2012/schedule/events/483.en.html http://www.pgcon.org/2012/schedule/events/483.en.html Edit: Not sure if I would call it braindead though. Highly annoying, yes. Alvaro has spent a lot of time into solving this, it is much harder than it initially seems.
- AaronBBrown 14y agoWhat version of MySQL? Sounds like the old AUTOINC lock mode behavior. http://insatiabledemand.ideeli.com/post/18850015294/diagnosing-mysql-auto-inc-and-gap-locks http://insatiabledemand.ideeli.com/post/18850015294/diagnosi...
- einhverfr 14y agoI don't think so. First ID's were being assigned manually, so I don't think a table lock would be pulled out, and secondly only one statement was executing at a time. Only one connection issuing sequential statements, and nobody else on the server. I think that this is thread/mutex based because of the fact that I could try to run the same series over and over and get different sets failing. So it seems to me the issue might be something environmental affecting timing. Again the really braindead thing here was that it was only one statement, only one concurrent transaction at any point in time.
- AaronBBrown 14y ago1. Yes, applications can set sql_mode themselves at the session level. An admin cannot prevent this. http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html
- einhverfr 14y agoSo, what good is setting a sensible default as a DBA if the dev guys can override it? I mean it's fine if you only allow one app to write to your db, but once you have more than one, the fact that the option to ignore invalid data even exists ought to make one very nervous if the data stored is worth anything.
- AaronBBrown 14y agoA dev can only override it for their session, not at the global level.