What does a MySQL Support Engineer do during his first hours of vacation? Yes, napping. After that, he goes on the web and tries out something new. Today: SugarCRM .. and struggeling a little with the installation.
The problem? The installation (on MacOS 10.5) was successful, no errors, but:
Sugar CRM 5.1.0c Files May Only Be Used With A Sugar CRM 5.1.0 Database
Logging didn’t reveal anything, but the general query log did! All DML statements were send to MySQL, but apparently not .. committed? After looking in the code I noticed a lack of commit statements.. Putting an explicit commit it worked, for the config table.
What was the real problem?
[mysqld]
init_connect='SET AUTOCOMMIT=0'
Don’t ask me why that was there, sometimes I do crazy stuff testing things, but this setup should work! Each transaction in applications should be committed. Disabling the AUTOCOMMIT shows that SugarCRM is not doing that.
Actually, I filed a bug for it at bugs on the sweet.. euh, SugarCRM website.
Comments
That SugarCRM is presuming AUTOCOMMIT=1 ?
Where they're not using multi-statement transactions, they won't want to issue COMMIT all the time….
I'm sure that when they do have a multi-statement transaction, they wrap it in START TRANSACTION … COMMIT (as that's the only thing that makes that work, so it's not really a gamble to presume that ;-)
What the system could do is issue an AUTOCOMMIT=1 when first connecting to make sure, but being a web app that would have to be done on every page and slow things down. Plus, this is no different from many other settings that can mess with the behaviour. You can't start overriding them all, it's best to simply note the correct/appropriate server config in the docs/setup.
I guess it boils down to “explicit is better then implicit”.
Anyway, I guess the bug is pretty obvious, and workarounds exists..
So no, what you're referring to is not what I said.
Yes, the symptom you saw is as you describe, but calling it a bug is unpractical to the extreme. As I mentioned, there are many other settings that you can change in MySQL server that would make most if not all apps break, you can't make all that explicit in the app, as it becomes unmaintainable (particularly with community/enterprise split having non-linear versioning!) and most importantly SLOW. You'd have to run many more queries on connect, and before/after most queries.
yes - I have seen problems like this many times - virtually every MySQL based application (yeah PHP crowd, looking at you especially) simply rely on a default MySQL configuration.
I have written about these and other problems in relation to Wordpress:
http://rpbouman.blogspot.com/search?q=wordpress
But I have had similar findings for other popular PHP apps.
Personally, I think the server admin should be free to configure the server to their liking. It should be the app's responsibility to establish the right environment upon connect. So if an app really needs autocommit because they didn't bother writing explicit transactions, fine - they should simply issue
SET autocommit := 1
when connecting. Same for sql mode - the app should ensure the right mode is set, not the admin.
It's a small thingy really, but big projects like SugarCRM should really indeed allow transactional engines and honor MySQL configuration. I think this should be addressed.
I commented today on my bug report at SugarCRM. Maybe it's an Enterprise feature! :-)