Hi Steve, Thanks for your reply. I agree that TEMPORARY TABLES are really great, and I'm using them in a similar way to your description, and I think I understand their purpose.
My problem, that so far no one has been able to answer, is that I'm using connection pooling with the Tomcat server. As far as I understand, this means that you don't create a connection to the server to do some processing then close it. Instead you get a connection from a pool, use it, then return it to the pool, and all connections appear as the same user to MySQL (i.e. same user, password and host). So my worry is this scenario: TransactionA gets connection A from the pool. TransactionA creates a temporary table for some query. TransactionA is done, and returns the connection to the pool. TransactionB gets a connection from the pool, which just so happens to be connection A. TransactionB tries to create a temporary table with the same name as the one that already exists. ** BANG!! ** TransactionA and TransactionB were trying to use the same temporary table on the same connection. This is why I think there should be a DROP TEMPORARY TABLE priveledge so that I can let the Tomcat server drop the temporary tables, but without having to give it DROP TABLE priveledge which is a lot more worrying. Note I'm cross posting this into the Java list too, incase I'm wrong about my understanding of pooling and someone there can correct me :) Many Thanks, Mike > -----Original Message----- > From: Steve Buehler [mailto:[EMAIL PROTECTED] > Sent: 28 October 2003 11:37 > To: Michael McTernan; mysql > Subject: Re: DROP TEMORARY TABLE > > > TEMPORARY TABLES are suppose to work this way. They are only > there during > a connection and then dropped when the connection to the database is > closed. One thing they are great for is when you have temporary > information > that needs to be kept and worked with. One of my programs has to pull > things from other tables and I put them into a temporary table > where I can > do things with this information easier than I could by putting them into > variables. It makes sorts much easier and the information is > only used to > generate the pages that a client is viewing. Instead of having a > table for > every client that has to be deleted afterwards, I use a temporary > table. You also don't have to have the table be a different name > for each > client that is connecting because only the connection that created it can > see it. So if 5 people access my page at the same time and each > have thier > own "population" table, it is not shared so the information can/will be > different for each of them. > Before I started using temporary tables, I would have my script come up > with a random name for the table. Problem was that if the client killed > the connection before the table could be dropped, it would stay in the > database until I manually dropped it. In my opinion, this was > the biggest > benefit of TEMPORARY TABLES. > > Steve > > At 02:58 PM 10/26/2003, you wrote: > >Hi there, > > > >I'm trying to restrict the access that a Tomcat server has to mysql > >4.0.15-standard-log database server. Unfortunately some of the > queries use > >temporary tables, so I've had to GRANT CREATE TEMPORARY TABLES > to the user. > > > >However, since the server is using connection pooling, it also DROPS the > >temporary tables after the queries have completed. > > > >I'd really like to be able to GRANT DROP TEMPORARY TABLES, but > this doesn't > >seem to exist, so I've got to GRANT DROP - which feels a less > safe to me :( > > > >Is there a better way of doing this that someone has found before, or is > >there a way to grant DROPS of the temporary table? > > > >Many Thanks, > > > >Mike > > > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
