Re: JDBC transactions using MySQL / DBCP in Tomcat 5.0.28
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Trond G. Ziarkowski wrote:
Hi all!
In my webapp I do two db inserts into two different tables. If the
second one fails I want to rollback the first one. My code is something
like the following:
Connection con = gotten from jndi DataSource
DBBean db = new DBBean(con);
try {
con.setAutoCommit(false);
Object o = db.getSomething();
Object o2 = db.getSomethingElse();
db.doInsert1(params);
db.doInsert2(params);
con.commit();
} catch (SQLException sqle) {
con.rollback();
throw sqle;
} finally {
if (con != null) {
con.close();
}
}
I thought that this should do the trick, but if the second insert fails
the data inserted in the first insert are still there. I am using MySQL
4.0.21-7 with InnoDB and have tested that I can use transactions with
the MySQL client.
Anyone got any tips?
Trond,
Are your tables in InnoDB storage? (i.e. TYPE=InnoDB used when you
created them)
What does 'SHOW CREAATE TABLE [table-name]' show for both tables?
If it doesn't say 'Type=InnoDB' at the end, transactions won't work :(
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 708 332 0507
www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBokmYtvXNTca6JD8RApHwAKCh/E83lwfvqMOCEmJTAIzrUDWbogCfVQxL
04L03RNzQvpUg+wNtgLFkSo=
=9qCZ
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Tomcat becoming almost completely non-responsive
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kaleb Pederson wrote: Hello, I have an interesting problem. After a while, tomcat (5.0.27) becomes almost completely non-responsive. If I telnet in to port 8009 (I'm using apache and mod_jk2), I get no response, at least not within the default timeout. If a browse to a page, I will generally, after about 4-5 minutes, see a page returned. To narrow down the slowness, I generated a full thread dump, and found the following information: [ see attachment for more info] Total threads: 180 executeQuery: 2 // executing a db query validateConnection: 0 // trying to validate their connection validateObect: 48 // in commons.dbcp.PoolableConnectionFactory.validateObject socketAccept: 3 // accepting a socket socketRead0: 10 // reading a socket ReferenceQueue: 1 ThreadPool$MonitorRunnable: 2 borrowObject and Object.wait: 85 // trying to get an object from the pool Object.wait: 20 // threads just waiting around Remaining: 9 // misc. threads My database connection is setup so that I have 50 allowed connections, which matches my 48 in validateObject and 2 executing queries. However, when I query the database status, I see 2 active threads and the rest are 'sleeping', just waiting around, as they would be if the connection pool hadn't released them yet. So, why would there be 48 connections that seemed locked and weren't querying the DB? And then the other 85 that were seemingly waiting on the 45? Any ideas what might be going on? The DB is ready? I have log abandoned turned and an haven't seen a problem yet. If the load drops sufficiently on the server, everything eventually returns back to normal, otherwise it takes 5-10 minutes to get a response from the server. I have attached an abbreviated form of the thread dump which should provide all the critical information and can provide as much other information as is necessary. Thanks for the help. *All* suggestions welcome ;) --Kaleb Total threads: 180 executeQuery: 2 validateConnection: 0 validateObect: 48 socketAccept: 3 socketRead0: 10 ReferenceQueue: 1 ThreadPool$MonitorRunnable: 2 borrowObject and Object.wait: 85 borrowObject: 0 Object.wait: 20 Remaining: 9 *** 2 like TP-Processor296 daemon prio=1 tid=0x6ea04a90 nid=0x5e87 runnable [738f6000..738f787c] at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:129) at java.io.BufferedInputStream.fill(BufferedInputStream.java:183) at java.io.BufferedInputStream.read1(BufferedInputStream.java:222) at java.io.BufferedInputStream.read(BufferedInputStream.java:277) [snip] Kaleb, Looks like you're using an old version of the JDBC driver that uses BufferedInputStreams by default. There is a 'feature' (many call a bug) in BufferedInputStreams that causes them in some cases to want to read a full buffer's worth of data when you're only asking for some portion of it...That's what's happening here. I'd try downloading something more recent for a JDBC driver (like Connector/J 3.0.15) and see where that gets you, to start with. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBY1AztvXNTca6JD8RAvEIAJ4p5Fi9QIwhNTlNslLMW6cKGhmUpgCeP2JJ RurwXfMfDzSEGTRLqssk4b4= =s3u9 -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: API docs for Mysql JDBC Driver?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mike Curwen wrote: Ok, those docs are way out of date. If you're using connectorj 3, those docs would only confuse the matter. The current docos on the jconnector portion of the mysql.com website contain all the latest info on how much of the spec the jconnector drivers implement. The JavaDoc for the drivers themselves (and it's hard to see when you'd want those, unless you intend to bypass java.sql) you can create from the java source files that you get when you download the binaries + source downloads from mysql.com (or don't bother generating them, and just read them from the *.java files). If you're asking yourself: how do I do SQL with jconnector drivers, then I think what you actually want to look into is how do I use the java.sql classes to do SQL, for which there is a healthy amount of JavaDoc included with the JDK, and a JDBC tutorial on java.sun.com. You should not be directly using the jconnector classes for doing SQL. -Original Message- From: John Najarian [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 10:10 AM To: 'Tomcat Users List' Subject: RE: API docs for Mysql JDBC Driver? I tired looking this up also. The best I could come up with is to compare the java.sql APIs and the mysql limitations. Here is a URL for that: http://www.cs.nott.ac.uk/TSG/manuals/databases/mysql/jdbc/mm.doc/ -Original Message- From: Chuck Carson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 7:49 AM To: LIST: Tomcat User Subject: API docs for Mysql JDBC Driver? Does anyone know where I can find a complete set of API docs for the JConnect Driver 3.x? The docs on the site are lacking and only have simple examples on how to do SELECT's. I am looking for a complete method list or something more detailed. The documentation for the JDBC driver at MySQL.com shows you any MySQL _specific_ information. It is assumed that you either know JDBC or know where to look for the information (I believe it even has some pointers in the docs). There are plenty of documentation artifacts for JDBC (books, tutorials, apidocs, etc), there's no reason for us to re-invent the wheel there. We implement the _standard_, there are very few 'extensions' to the JDBC standard in the Connector/J docs...Where there are, they're documented. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBVy5FtvXNTca6JD8RAqfLAJ9UfemYcooEvsYwHcErMXJ5VXYQaQCgsPEZ kVnbBFcukA92jOUXD0hoi7c= =zss7 -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Retrieveing Unicode stuff from MySQL 4.1.x ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ivan Jouikov wrote: | I tried using those URL parameters. Doesn't change anything. | | |-Original Message- |From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] |Sent: Wednesday, July 14, 2004 2:30 AM |To: Tomcat Users List |Subject: Re: Retrieveing Unicode stuff from MySQL 4.1.x ? | |Ivan Jouikov wrote: | |I tried to use table with CHARACTER SET UTF8, but it didn't change | |anything :( | |Any other suggestions? | |I'm using a line like the one below to connect to the MySQL DB and it is |working quite well here: | |DBUrl=jdbc:mysql://+server+:+databaseport+/+database+?user=+login+ |password=+password+useUnicode=truecharacterEncoding=UTF-8; | |You may want to specify characterSetResults as well. | |http://dev.mysql.com/doc/connector/j/en/index.html Ivan, You're going to have to do a little testing to see _where_ the character encoding is being lost. You should first see if the strings that you are inserting into the database are the same as being retrieved, either in your JSP, or in a little testcase (I prefer JUnit myself), by using String's .equals() method and not your 'eyes'. The reason to do this is to isolate the different stages of encoding/decoding from each other, because there's an encoding/decoding between the JDBC driver and the database, there is one yet again between your JSP and your browser, and there is many times one yet again between your browser and your operating system. You will need to check at _each_ stage to make sure that the characters are encoded the way you think they are. I can state from experience that _usually_ it's not at the database level, however, it's usually a mismatch somewhere else that these problems occur (but they are hard to debug sometimes :( ) Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFA9U22tvXNTca6JD8RAibYAKDHK+QwHHPXuwK8COGp1XhxRM1s7wCdFmIf yxc3AlFTMt9LWvRxYdXwMoM= =6oIc -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Help moving from a ConnectionBroker to using Tomcats DB Connection pool
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Randy wrote:
Hello,
I need a little help
Hopefully I can give the info needed.
My servlets have been using this code I found a long time ago called
DbConnectionBroker
It works very well.
I have been trying to move to the db connection pool of tomcat.
They both use org.gjt.mm.mysql.Driver
But here is where I am having problems. Hopefully this little piece of
code
is enough to help
The thing that is different is the connection I get from the
DbConnectionBroker is different that the one I get from
/
//this is what I do to use jndi
ds = (DataSource)ctx.lookup(JNDICONTEXTLOOKUP);
conn = ds.getConnection();
/
So I guess my question is, is someone using JNDI with mysql and
getting the
LastInsertID..
When I use the connection broker this piece of code below works fine, but
when I use it with jndi the line long newKey =
((org.gjt.mm.mysql.Statement)stmt).getLastInsertID() ;
Gets a class cast exception
Thanks for any help...
try{
Statement stmt = conn.createStatement();
int ret = stmt.executeUpdate(ustr);
rowsProcessed = ret;
if ( ret == 0 ){
writeErrorInfo(userID,executeSql,
executeSql Failed - non fatal
error , no rows processed,
no rows processed,-1);
stmt.close();
return -1;
}
long newKey =
((org.gjt.mm.mysql.Statement)stmt).getLastInsertID()
;
stmt.close();
return (int)newKey;
}catch (SQLException e){
Use MySQL's Connector/J 3.x or 3.1.x (they're what MM.MySQL 'grew up'
to), and use the JDBC-standard Statement.getGeneratedKeys() method to do
the same thing.
-Mark
- --
Mr. Mark Matthews
MySQL AB, Software Development Manager, J2EE and Windows Platforms
Office: +1 708 332 0507
www.mysql.com
MySQL Guide to Lower TCO
http://www.mysql.com/it-resources/white-papers/tco.php
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFA0LlftvXNTca6JD8RAu/YAKCNXcgLQE/mkow0PvrKy/hwFLp+2gCgqhXy
dpr37EvR/tnOfcIvGmWWIic=
=stm+
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: problem with mod_jk 1.2: error in action code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mike Curwen wrote: when you specify the connection to mysql, are you including the 'autoreconnect=true' parameter? If not, the connections will be closed by mysql after (I think) 8 hrs. I wish I could kill the 'autoReconnect' feature. You shouldn't be using it if you're using a connection pool :) Use the correct properties on your connection pool and you should be good to go. For example, setting 'timeBetweenEvictionRunsMillis' (set to some sensible number of milliseconds, I would suggest something around 5 minutes) and 'minEvictableIdleTimeMillis' (set to the number of milliseconds you have 'wait_timeout' set to on the server minus some %) should catch MySQL closing idle connections. Setting 'validationQuery' (set to 'SELECT 1') and 'testOnBorrow' or 'testOnReturn' should catch MySQL server restarts or general network flakiness. In any case, you should also see the new example in the troubleshooting section of the Connector/J docs that shows how your code can also detect whether a SQLException was caused by a lost connection, or some other error (section 4.4 under the following link): http://www.mysql.com/documentation/connector-j/index.html#id2803835 -Mark (the guy who wrote the 'autoReconnect' functionality, and now wishes he could remove it from the JDBC driver) - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAUe8FtvXNTca6JD8RAmpmAJ0Wq/1rT/Fcw40H4fB1/g8UmheonACbBYwU mpRzou4Jb2yXUsGSNooLCKs= =DW3u -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: JNDI Datasource Reference in DD Not Necessary?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David Smith wrote: Despite my posting - and I really hope someone chips in concerning the question I raised about the resource-ref being redundant in the deployment descriptor (!) - the first thing to say is, have faith! Connection pooling works, and it is actually quite simple, so do not give up on it. Cheers for the advice Harry... here's where I'm at I have no choice on the giving up side of things, need to implement it for some client code I'm working on ;-) I've been doing a lot of googling to try and get answers so I may have got crossed wires, I had a version working (or thinking it was working) with the info in $JAKARTA_HOME/conf/server.xml but I wasn't happy with this solution (preferring to have the connection defined in $JAKARTA_HOME/conf/Catalina/localhost/app_name.xml for good reasons... so I'm basically restarting and I now have the following config $JAKARTA_HOME/conf/Catalina/localhost/app_name.xml ?xml version='1.0' encoding='utf-8'? Context docBase=dev-sstl path=/dev-sstl reloadable=true useNaming=false Resource name=jdbc/mysql type=com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource auth=Container/ Dave, You don't want to do this, as Tomcat's DBCP doesn't wrap connection pool data sources currently (or if they do, I can't find it documented anywhere). If you follow the example given in the Tomcat documentation _to_the_letter_ things work out fine. Once you have _that_ working, then start tweaking from there. I've found that trying to plug the right magic values from scratch into server.xml never works as you'd expect. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFATfR1tvXNTca6JD8RAmfvAJ9dkmmyQmwAht6DmAsB/OrZPiiKVgCePuVs Zqkj0tMidrTKUMuAX5b3ekA= =Q5rH -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Another Try at MySQL Connection Pooling
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yiannis Mavroukakis wrote: As far as the listening bit is concerned why not just limit mySQL to listen on localhost then? Do you get better performance out of named pipes? On most versions of Windows, using JDBC and MySQL with named pipes will give you a 50-60% performance improvement. It depends on the particular version of Windows (first, it has to be NT-based, secondly the newer the better). -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAKlT0tvXNTca6JD8RAlLAAJwOqL4QfjEAhc69HSI0VYBerWLT3QCgr+Pt 5JI/y54R11o7k6xyezVJ6Io= =Ay3a -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Another Try at MySQL Connection Pooling
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Parsons Technical Services wrote: Merrill, I may end up eating a little sole, but I have had shoe before. Your name for the jdbc is wrong. It should be: !-- Class name for mm.mysql JDBC driver -- parameter namedriverClassName/name valuecom.mysql.jdbc.Driver/value /parameter Yeah, I know the notes said otherwise. They were written prior to MySQL taking on the drivers officially. Actually, either works (we've left in 'org.gjt.mm.mysql.Driver' for backwards-compatibility). Whenever I've had problems with this, I've found that I'm usually using the wrong strings/formats for the ENV namespace (as a previous poster has talked about), or I messed around with the tomcat configuration too much...Try and use the example they've provided, if you use it out of the box, it works (for most people), and then change one thing at a time. Unfortunately, Tomcat itself doesn't complain much up front when you have something wrong with configuration, and it usually manifests itself as an exception that's not related to what you've misconfigured :( Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAKoOFtvXNTca6JD8RAnj2AKCgLDOhFTqqb/EgUKydHwIBdJc4cwCglp/l oDTq3EIy2zKuLxv+UawytXQ= =b4W4 -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Database Rollback doesn't work
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 George Sexton wrote: I thought transactions were one of those features MySQL decided that real developers didn't need. Are you really certain mySQL Supports transactions in the relatively low version you are using? I assume the poster meant MySQL-3.23, and yes, even MySQL-3.23 has transaction support. You need to use InnoDB tables to have transactional semantics, and it's all covered in the MySQL manual at: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html and http://www.mysql.com/doc/en/ANSI_diff_Transactions.html Also, I hope you don't need to scale. You are essentially locking the table until the mail goes or fails. What if it wedges for 60 seconds doing a DNS lookup? With InnoDB, it would be a row-level, lock, however I agree. SMTP is not a 'transactional' resource. You might be better off dealing with failures after you detect them and rethinking your logic here, instead of waiting for your mailserver to tell you whether it accepted the message or not. Even if your mailserver accepts the message, there is no 'transactional' guarantee that it will be delivered. You'll need to watch for 'bounce' messages to see whether it was delivered or not, and even then, you might not get those. Perhaps a better way to do it might be to have different states, i.e. confirmation email sent, user confirmed, etc? Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/4zYktvXNTca6JD8RApvwAKC3tM1FwsDk0oKbOr/mjGr7czCdUgCgrMcu 3HHtgH4skyOukBRC6RRqMIk= =OxcC -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: HELP!! TOMCAT and MYSQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 engp0510 wrote: Thanks very much! Y, I have tried. But always throw org.xml.sax.SAXParseException said a ; is needed to end the quote of password. Any advice? Tomcat's config file is in XML. The 'XML way' to produce an ampersand () is to use the entity amp; So...Your connection URL would look like: connectionURL=jdbc:mysql://localhost/authority?user=leesonamp;password= Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/LSbhtvXNTca6JD8RAkYKAJ94rLovzLHUodq+n6vYwFBplig+GwCffI+h ESAxHAD4u1/gYfi5MTWvg58= =pqqc -END PGP SIGNATURE- - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
