Temporary tables are per thread.
WHat guarantee do you have that your next page gets the same thread?
Martin Thomas wrote:
Help (apologies for the length of this email),
I am currently using mysql-max-3.23.49a with a process as follows:
Using tomcat/jsp/servlets, I have written a database connection
pooling java class which establishes connections to mysql, stores them
in an array and manages the distribution of these to other classes as
required.
The code has been written such that once a connection is used, it is
owned by that class and cannot be resused until it is close.
Everything works fine except when I use temporary tables (needed for
some complex sql in my application).
One class executes the following:
connect() - gets a connection from the pool
drop table tablea if exists;
create temporary table tablea type=HEAP select * from othertable;
select * from tableb, tablea;
drop table tablea if exists;
close() - returns connection to the pool
Sometimes the select statement will fail with "Table 'db.tablea'
doesn't exist". Sometimes the create temporary table statement fails
reporting that tablea already exists.
I'm convinced that the pool management code is fine and is not
re-issuing the same connections to more than one thread. The
application is supporting approx 150 concurrent users at any one time.
Does anyone have any ideas what may cause this? Are there any easy
ways to debug this - I've tried examining the binary log but can't
identify which statements are from which connection.
Thanks for any help offered.
Server version 3.23.49a-max-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 7 hours 12 min 53 sec
Threads: 86 Questions: 79859 Slow queries: 71 Opens: 2584 Flush
tables: 1 Open tables: 64 Queries per second avg: 3.075
Environment:
<machine, os, target, libraries (multiple lines)>
System: Linux dbsrvr.somewhere.com 2.2.19-6.2.10smp #1 SMP Tue Oct 9
11:09:48 EDT 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from
/usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses
-Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit
-Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
-Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual
-Wextern-inline -Wsign-promo -Wreorder -Wctor-dtor-privacy
-Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti
-mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 29 2001 /lib/libc.so.6
-> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101836 Jan 15 2001
/lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20273324 Jan 15 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 15 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
--enable-assembler --with-extra-charsets=complex
--enable-thread-safe-client --with-innodb --with-berkeley-db
--enable-thread-safe-client --with-other-libc=/usr/local/mysql-glibc
'--with-comment=Official MySQL Binary' --prefix=/usr/local/mysql
--with-extra-charset=complex --enable-thread-safe-client
--enable-local-infile --with-server-suffix=-max 'CFLAGS=-Wimplicit
-Wreturn-type -Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W
-Wchar-subscripts -Wformat -Wimplicit-function-dec -Wimplicit-int
-Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro
-O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type
-Wid-clash-51 -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts
-Wformat -Wimplicit-function-dec -Wimplicit-int -Wparentheses
-Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wextern-inline
-Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor
-felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3
-fno-omit-frame-pointer' CXX=gcc
Perl: This is perl, version 5.005_03 built for i386-linux
_________________________________________________________________
Internet access plans that fit your lifestyle -- join MSN.
http://resourcecenter.msn.com/access/plans/default.asp
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
