Re: [libdbi-users] libdbi transaction support
On 26/01/13 8:30 PM, markus.hoeni...@mhoenicka.de wrote: Toby Thain writes: Yes, you just found out the hard way that it's wise to include ENGINE specifications in MySQL CREATE TABLE's. True indeed. Actually I think the news is even worse: I seem to recall that MySQL can even *ignore* ENGINE specifications if the engine isn't installed. Leaving you, again, with MyISAM-the-inadequate. More silent fail. I think I've wasted some time on that one in the past. --Toby regards, Markus -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
I've checked in new versions of libdbi/src/dbi_main.c containing the implementations of the transaction functions and libdbi- drivers/tests/test_dbi.c with a first shot at the required test functions. MySQL seems to fail to rollback transactions on my box whereas SQLite3 succeeds. I'm too tired now to track this down, maybe someone else can have a look at this too. I hope I'll get back to this on the weekend. Hi Markus, we have given it a try today and things look pretty good :-). Unfortunately, we can reproduce the problem with MySQL. I barely remember that MySQL by default has implicit commits enabled, what needs to be turned off if you need real ones. At least I have check the tx support in our native mysql driver that Ulrike did and there is this call. Maybe it's useful for you: http://git.adiscon.com/?p=rsyslog.git;a=blob;f=plugins/ommysql/ommysql.c;h=2dfa29de74bd4b172ea6073bb970759753ace491;hb=HEAD#l223 Thanks again for your help! Rainer -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Hello, we have given it a try today and things look pretty good :-). Unfortunately, we can reproduce the problem with MySQL. I barely remember that MySQL by default has implicit commits enabled, what needs to be turned off if you need real ones. By default, MySQL has autocommit enabled. This is disabled automatically when you issue START TRANSACTION statement (or changed default behaviour in MySQL config file). See http://dev.mysql.com/doc/refman/5.5/en/commit.html Olivier -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Rainer Gerhards rgerha...@hq.adiscon.com was heard to say: we have given it a try today and things look pretty good :-). Sounds good :-) Unfortunately, we can reproduce the problem with MySQL. I barely remember that MySQL by default has implicit commits enabled, what needs to be turned off if you need real ones. As Olivier already mentioned, any eplicit START TRANSACTION overrides autocommit, so this shouldn't interfere here. Even if an application using libdbi turns autocommit off or on deliberately, START TRANSACTION should still work as expected. Another possible reason for problems is the table type. If someone uses MyISAM tables, the result I got would be expected. However, MySQL uses InnoDB tables as default these days, and I double-checked that they were used in my tests. I'll try to test the pgsql driver on the weekend to see if that causes problems too. I'll also check the MySQL logs to see if I find something weird, and I'll run some transactions in MySQL's plain ol' command line interface just to make sure it isn't MySQL playing tricks on us. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
On 25/01/13 7:40 PM, markus.hoeni...@mhoenicka.de wrote: Markus Hoenicka writes: Another possible reason for problems is the table type. If someone uses MyISAM tables, the result I got would be expected. However, MySQL uses InnoDB tables as default these days, and I double-checked that they were used in my tests. Turns out that InnoDB is the default engine only in versions 5.5.5 and later. On my box, MyISAM tables were created by default. The test Yes, you just found out the hard way that it's wise to include ENGINE specifications in MySQL CREATE TABLE's. They took far too long to change the default engine to InnoDB. --Toby correctly found out that the latter don't support transactions :-( I've changed the CREATE TABLE statement on my box to ask for InnoDB tables instead. This eliminates the failure of the rollback test. I'll try and finish the test suite asap. The transaction tests require some polish as they should run only if the driver supports transactions. regards, Markus -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Zitat von Rainer Gerhards rgerha...@hq.adiscon.com: sorry for the long silence, we got sidetracked ourselfs. Finally, we yesterday tried to write some test programs to get started. To do so, I did a cvs checkout for both libdbi and libdbi-drivers (according to instructions on the site). I can see the new transaction functions inside the sgml files as well as the headers. However, I do not find any implementation (.c files). Am I overlooking something? No. Apparently my bad. I'll fix that tonight. Thanks a lot! Rainer -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Rainer Gerhards writes: This should be easy to retrofit. I'lls see if I findsome time but feel free to beat me at it. I am quite busy myself at the moment, but I could try and see if I could craft something along that path... Hi all, I've stolen some time from myself to provide a first shot at transaction and savepoint support, see the current cvs revisions of libdbi and libdbi-drivers. The code is entirely untested except that the drivers which I use myself compile and don't crash upon loading. I didn't get round to adding the documentation and the tests, but feel free to test the current code yourself. Usage should be pretty obvious if you look at the diffs. I'm sure some rough edges remain, but then... it's a start. Hi Markus, sorry for the long silence, we got sidetracked ourselfs. Finally, we yesterday tried to write some test programs to get started. To do so, I did a cvs checkout for both libdbi and libdbi-drivers (according to instructions on the site). I can see the new transaction functions inside the sgml files as well as the headers. However, I do not find any implementation (.c files). Am I overlooking something? Rainer -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Rainer Gerhards writes: Zitat von Rainer Gerhards rgerha...@hq.adiscon.com: sorry for the long silence, we got sidetracked ourselfs. Finally, we yesterday tried to write some test programs to get started. To do so, I did a cvs checkout for both libdbi and libdbi-drivers (according to instructions on the site). I can see the new transaction functions inside the sgml files as well as the headers. However, I do not find any implementation (.c files). Am I overlooking something? No. Apparently my bad. I'll fix that tonight. Thanks a lot! Rainer I've checked in new versions of libdbi/src/dbi_main.c containing the implementations of the transaction functions and libdbi-drivers/tests/test_dbi.c with a first shot at the required test functions. MySQL seems to fail to rollback transactions on my box whereas SQLite3 succeeds. I'm too tired now to track this down, maybe someone else can have a look at this too. I hope I'll get back to this on the weekend. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
markus.hoeni...@mhoenicka.de writes: I've stolen some time from myself to provide a first shot at transaction and savepoint support, see the current cvs revisions of libdbi and libdbi-drivers. The code is entirely untested except that the drivers which I use myself compile and don't crash upon loading. I didn't get round to adding the documentation and the tests, but feel free to test the current code yourself. Usage should be pretty obvious if you look at the diffs. I'm sure some rough edges remain, but then... it's a start. FYI I've also updated the docs in cvs to reflect the latest changes. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial Remotely access PCs and mobile devices and provide instant support Improve your efficiency, and focus on delivering more value-add services Discover what IT Professionals Know. Rescue delivers http://p.sf.net/sfu/logmein_12329d2d ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi transaction support
Mike Rylander mrylan...@gmail.com was heard to say: I think I'd add three calls to the proposed API, though, for support of savepoints. They're part of the SQL standard, and supported by several SQL RDBMS' including Postgres (the db my project uses, and we use libdbi to connect to PG). The relevant PG documentation is available at http://www.postgresql.org/docs/9.1/static/sql-savepoint.html . I have wrappers for savepoints in my implementation, and they are very handy for complex DB interactions. Sounds like a no-brainer if it is as simple as you say. I don't keep the SQL standard underneath my pillow, but is it safe to assume that database engines are responsible to deal with any pending savepoints if a transaction is committed (i.e. no extra work for libdbi)? This brings up another question. Applications should of course check the transaction_supported and savepoints_supported driver capabilities and act responsibly. But if a database engine does not support savepoints, or transactions altogether, should libdbi just go ahead when asked, or should it throw an error instead? I recall that MySQL supported transactions in MyISAM tables using no-ops. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Monitor your physical, virtual and cloud infrastructure from a single web console. Get in-depth insight into apps, servers, databases, vmware, SAP, cloud infrastructure, etc. Download 30-day Free Trial. Pricing starts from $795 for 25 servers or applications! http://p.sf.net/sfu/zoho_dev2dev_nov ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
