--0016363b9bf6103cd904778d2519
Content-Type: text/plain; charset=ISO-8859-1
Hi,
With regards the CONNECTION_ID() variable in MySQL, is this based on a per
user basis. Or can multiple users use the same connection_id ?
Thanks,
Neil
--0016363b9bf6103cd904778d2519--
--001485f6cff83e086e04778d5ebe
Content-Type: text/plain; charset=ISO-8859-1
Hi
Thanks for your quick response. In our test environment running MySQL
5.1.31 over SSL (hosted external of our network) and IIS 6 on Windows 2003
Server, I've found when opening a test connection to the database from my PC
and one from my colleagues PC on the same internal network, we both had the
same connectionID ? Is this right ?
The reason I need to know about the CONNECTION_ID() is because we have a
issue on our website whereby not all database connections are correctly
closed. As commented on in this URL http://bugs.mysql.com/bug.php?id=12274
Can anyone offer any advice regarding this ?
Regards
Neil
On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
> Typically, a thread works on a single connection exclusively..
> otherwise it is impossible to do anything transactional. However, I
> have seen applications, in auto-commit mode use distinct statements
> concurrently from the same connection to do read-only work.
>
> If you are worried about the latency of creating new connections for a
> many-threaded design, you should be looking into connection pooling
> which manages the problem of maintaining many connections and only
> letting one thread have a given connection at a time.
>
> - michael dykman
>
> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
>
> > Hi,
> >
> > With regards the CONNECTION_ID() variable in MySQL, is this based on a
> per
> > user basis. Or can multiple users use the same connection_id ?
> >
> > Thanks,
> > Neil
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
--001485f6cff83e086e04778d5ebe--
I am certainly no expert in Windows, but that sounds like the work of
whatever is managing the connections on your application server.
Whatever language you are using, if you opened the connection raw,
what you are describing would be impossible. You need to look at how
you are acquiring your connection and then have a closer look at
whatever component is managing that.. apparently, its' doing it
wrong.
- michael dykman
On Wed, Nov 4, 2009 at 10:36 AM, Tompkins Neil
> Hi
>
> Thanks for your quick response.=A0 In our test environment running MySQL
> 5.1.31 over SSL (hosted external of our network)=A0and IIS 6 on Windows 2=
003
> Server, I've found when opening a test connection to the database from my=
PC
> and one from my colleagues PC on the same internal network, we both had t=
he
> same connectionID ?=A0 Is this right ?
>
> The reason I need to know about the CONNECTION_ID() is because we have a
> issue on our website whereby not all database connections are correctly
> closed.=A0 As commented on in this URL http://bugs.mysql.com/bug.php?id=
=3D12274
>
> Can anyone offer any advice regarding this ?
>
> Regards
> Neil
>
> On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
>>
>> Typically, a =A0thread works on a single connection exclusively..
>> otherwise it is impossible to do anything transactional. =A0However, I
>> have seen applications, in auto-commit mode use distinct statements
>> concurrently from the same connection to do read-only work.
>>
>> If you are worried about the latency of creating new connections for a
>> many-threaded design, you should be looking into connection pooling
>> which manages the problem of maintaining many connections and only
>> letting one thread have a given connection at a time.
>>
>> =A0- michael dykman
>>
>> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
>>
>> > Hi,
>> >
>> > With regards the CONNECTION_ID() variable in MySQL, is this based on a
>> > per
>> > user basis. =A0 Or can multiple users use the same connection_id ?
>> >
>> > Thanks,
>> > Neil
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> "May you live every day of your life."
>> =A0 =A0Jonathan Swift
>
>
--=20
- michael dykman
- mdykman@gmail.com
"May you live every day of your life."
Jonathan Swift
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
--001485f270e2d3bef804778d8540
Content-Type: text/plain; charset=ISO-8859-1
Hi Michael,
We are using standard classic ASP code like :
*Function OpenConnection()
OpenConnection = "DSN=MyDSN_SSL"
End Function
DSNTemp = OpenConnection()
Set Conn = Server.CreateObject("ADODB.Connection")
Set RecordsetTest = Server.CreateObject("ADODB.RecordSet")
response.write Now() & " connecting...
Connected at : "
Conn.Open DSNTemp
response.write Now()
if Conn.errors.count = 0 then
response.write " - Connection Successful!"
else
response.write " - ERROR: Couldn't connect to database"
end if*
* RecordsetTest.open "SELECT CONNECTION_ID() As MySQLConnectionID" ,Conn
'query database for data
if not RecordsetTest.eof then
response.write "
" & RecordsetTest.fields("MySQLConnectionID").value &
"
"
end if
RecordsetTest.close
set RecordsetTest = nothing*
**
* Conn.close
set Conn = nothing*
But my question regarding the CONNECTION_ID is why we are connecting using
the same ID from two different computers. I'm using a MySQL ODBC 5.1 Driver
connection.
Thanks
Neil
On Wed, Nov 4, 2009 at 3:41 PM, Michael Dykman
> I am certainly no expert in Windows, but that sounds like the work of
> whatever is managing the connections on your application server.
> Whatever language you are using, if you opened the connection raw,
> what you are describing would be impossible. You need to look at how
> you are acquiring your connection and then have a closer look at
> whatever component is managing that.. apparently, its' doing it
> wrong.
>
>
> - michael dykman
>
> On Wed, Nov 4, 2009 at 10:36 AM, Tompkins Neil
>
> > Hi
> >
> > Thanks for your quick response. In our test environment running MySQL
> > 5.1.31 over SSL (hosted external of our network) and IIS 6 on Windows
> 2003
> > Server, I've found when opening a test connection to the database from my
> PC
> > and one from my colleagues PC on the same internal network, we both had
> the
> > same connectionID ? Is this right ?
> >
> > The reason I need to know about the CONNECTION_ID() is because we have a
> > issue on our website whereby not all database connections are correctly
> > closed. As commented on in this URL
> http://bugs.mysql.com/bug.php?id=12274
> >
> > Can anyone offer any advice regarding this ?
> >
> > Regards
> > Neil
> >
> > On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
> wrote:
> >>
> >> Typically, a thread works on a single connection exclusively..
> >> otherwise it is impossible to do anything transactional. However, I
> >> have seen applications, in auto-commit mode use distinct statements
> >> concurrently from the same connection to do read-only work.
> >>
> >> If you are worried about the latency of creating new connections for a
> >> many-threaded design, you should be looking into connection pooling
> >> which manages the problem of maintaining many connections and only
> >> letting one thread have a given connection at a time.
> >>
> >> - michael dykman
> >>
> >> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
> >>
> >> > Hi,
> >> >
> >> > With regards the CONNECTION_ID() variable in MySQL, is this based on a
> >> > per
> >> > user basis. Or can multiple users use the same connection_id ?
> >> >
> >> > Thanks,
> >> > Neil
> >> >
> >>
> >>
> >>
> >> --
> >> - michael dykman
> >> - mdykman@gmail.com
> >>
> >> "May you live every day of your life."
> >> Jonathan Swift
> >
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
--001485f270e2d3bef804778d8540--
--0014850b6ac4054e6204779d62d9
Content-Type: text/plain; charset=ISO-8859-1
Thanks for your response. Is it recommended we disable connection pooling,
so that all connections are instantly closed ?
On Wed, Nov 4, 2009 at 4:19 PM, Michael Dykman
> Again, I'm no Microsoft guru, but it appears that your server
> configuration for ADODB is the culprit.
>
> If 2 simultaneous processes are getting access to the same connection
> at the same time via this call, there is somwething very wrong with
> this service . Now if 2 distinct processes happening not *quite* at
> the same time are getting the same handle, no problem. Connection
> pooling is doing it's job.
>
> - michael dykman
>
>
> On Wed, Nov 4, 2009 at 10:47 AM, Tompkins Neil
>
> > Hi Michael,
> >
> > We are using standard classic ASP code like :
> >
> > Function OpenConnection()
> > OpenConnection = "DSN=MyDSN_SSL"
> > End Function
> >
> > DSNTemp = OpenConnection()
> >
> > Set Conn = Server.CreateObject("ADODB.Connection")
> > Set RecordsetTest = Server.CreateObject("ADODB.RecordSet")
> >
> > response.write Now() & " connecting...
Connected at : "
> > Conn.Open DSNTemp
> > response.write Now()
> > if Conn.errors.count = 0 then
> > response.write " - Connection Successful!"
> > else
> > response.write " - ERROR: Couldn't connect to database"
> > end if
> >
> > RecordsetTest.open "SELECT CONNECTION_ID() As MySQLConnectionID" ,Conn
> > 'query database for data
> >
> > if not RecordsetTest.eof then
> > response.write "
" &
> RecordsetTest.fields("MySQLConnectionID").value &
> > "
"
> > end if
> >
> > RecordsetTest.close
> > set RecordsetTest = nothing
> >
> > Conn.close
> > set Conn = nothing
> > But my question regarding the CONNECTION_ID is why we are connecting
> using
> > the same ID from two different computers. I'm using a MySQL ODBC 5.1
> Driver
> > connection.
> >
> > Thanks
> > Neil
> > On Wed, Nov 4, 2009 at 3:41 PM, Michael Dykman
> wrote:
> >>
> >> I am certainly no expert in Windows, but that sounds like the work of
> >> whatever is managing the connections on your application server.
> >> Whatever language you are using, if you opened the connection raw,
> >> what you are describing would be impossible. You need to look at how
> >> you are acquiring your connection and then have a closer look at
> >> whatever component is managing that.. apparently, its' doing it
> >> wrong.
> >>
> >>
> >> - michael dykman
> >>
> >> On Wed, Nov 4, 2009 at 10:36 AM, Tompkins Neil
> >>
> >> > Hi
> >> >
> >> > Thanks for your quick response. In our test environment running MySQL
> >> > 5.1.31 over SSL (hosted external of our network) and IIS 6 on Windows
> >> > 2003
> >> > Server, I've found when opening a test connection to the database from
> >> > my PC
> >> > and one from my colleagues PC on the same internal network, we both
> had
> >> > the
> >> > same connectionID ? Is this right ?
> >> >
> >> > The reason I need to know about the CONNECTION_ID() is because we have
> a
> >> > issue on our website whereby not all database connections are
> correctly
> >> > closed. As commented on in this URL
> >> > http://bugs.mysql.com/bug.php?id=12274
> >> >
> >> > Can anyone offer any advice regarding this ?
> >> >
> >> > Regards
> >> > Neil
> >> >
> >> > On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
> >> > wrote:
> >> >>
> >> >> Typically, a thread works on a single connection exclusively..
> >> >> otherwise it is impossible to do anything transactional. However, I
> >> >> have seen applications, in auto-commit mode use distinct statements
> >> >> concurrently from the same connection to do read-only work.
> >> >>
> >> >> If you are worried about the latency of creating new connections for
> a
> >> >> many-threaded design, you should be looking into connection pooling
> >> >> which manages the problem of maintaining many connections and only
> >> >> letting one thread have a given connection at a time.
> >> >>
> >> >> - michael dykman
> >> >>
> >> >> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
> >> >>
> >> >> > Hi,
> >> >> >
> >> >> > With regards the CONNECTION_ID() variable in MySQL, is this based
> on
> >> >> > a
> >> >> > per
> >> >> > user basis. Or can multiple users use the same connection_id ?
> >> >> >
> >> >> > Thanks,
> >> >> > Neil
> >> >> >
> >> >>
> >> >>
> >> >>
> >> >> --
> >> >> - michael dykman
> >> >> - mdykman@gmail.com
> >> >>
> >> >> "May you live every day of your life."
> >> >> Jonathan Swift
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> - michael dykman
> >> - mdykman@gmail.com
> >>
> >> "May you live every day of your life."
> >> Jonathan Swift
> >
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
--0014850b6ac4054e6204779d62d9--
> Is it recommended we disable connection pooling,
> so that all connections are instantly closed ?
Frankly, no. Well-behaved connection pooling is a good thing. It
will reduce the load on your system resources and speed up responses
considerably.
If the only thing at issue is that you have observed various processes
reusing the same connection, then you have no issues at all. As long
as connections aren't going stale and you are keeping a moderate
number of connections, having a few open and idle is not a bad thing
and puts no discernible load on the system.
If there are any behviours of the connection manger that are giving
you trouble, then you will have to look at however that is
configured.. in your case, that is an ASP/.net matter and you should
consult the gurus there.
- michael dykman
On Thu, Nov 5, 2009 at 5:42 AM, Tompkins Neil
> Thanks for your response.=A0 Is it recommended we disable connection pool=
ing,
> so that all connections are instantly closed ?
>
> On Wed, Nov 4, 2009 at 4:19 PM, Michael Dykman
>>
>> Again, I'm no Microsoft guru, but it appears that your server
>> configuration for ADODB is the culprit.
>>
>> If 2 simultaneous processes are getting access to the same connection
>> at the same time via this call, there is somwething very wrong with
>> this service . =A0Now if 2 distinct processes happening not *quite* at
>> the same time are getting the same handle, no problem. =A0Connection
>> pooling is doing it's job.
>>
>> =A0- michael dykman
>>
>>
>> On Wed, Nov 4, 2009 at 10:47 AM, Tompkins Neil
>>
>> > Hi Michael,
>> >
>> > We are using standard classic ASP code like :
>> >
>> > Function OpenConnection()
>> > =A0 OpenConnection =3D "DSN=3DMyDSN_SSL"
>> > =A0 End Function
>> >
>> > =A0 DSNTemp =3D OpenConnection()
>> >
>> > =A0 Set Conn =3D Server.CreateObject("ADODB.Connection")
>> > =A0 Set RecordsetTest =3D Server.CreateObject("ADODB.RecordSet")
>> >
>> > =A0 response.write Now() & " connecting...
Connected at : "
>> > =A0 Conn.Open DSNTemp
>> > =A0 response.write Now()
>> > =A0 if Conn.errors.count =3D 0 then
>> > response.write " - Connection Successful!"
>> > else
>> > =A0 response.write " - ERROR: Couldn't connect to database"
>> > =A0 end if
>> >
>> > =A0 RecordsetTest.open "SELECT CONNECTION_ID() As MySQLConnectionID" ,=
Conn
>> > 'query database for data
>> >
>> > =A0 if not RecordsetTest.eof then
>> > =A0 =A0response.write "
" &
>> > RecordsetTest.fields("MySQLConnectionID").value &
>> > "
"
>> > =A0 end if
>> >
>> > =A0 RecordsetTest.close
>> > =A0 set RecordsetTest =3D nothing
>> >
>> > =A0 Conn.close
>> > =A0 set Conn =3D nothing
>> > But my question regarding the CONNECTION_ID is why we are connecting
>> > using
>> > the same ID from two different computers.=A0 I'm using a MySQL ODBC 5.=
1
>> > Driver
>> > connection.
>> >
>> > Thanks
>> > Neil
>> > On Wed, Nov 4, 2009 at 3:41 PM, Michael Dykman
>> > wrote:
>> >>
>> >> I am certainly no expert in Windows, but that sounds like the work of
>> >> whatever is managing the connections on your application server.
>> >> Whatever language you are using, if you opened the connection raw,
>> >> what you are describing would be impossible. =A0You need to look at h=
ow
>> >> you are acquiring your connection and then have a closer look at
>> >> whatever component is managing that.. =A0apparently, =A0its' doing it
>> >> wrong.
>> >>
>> >>
>> >> =A0- michael dykman
>> >>
>> >> On Wed, Nov 4, 2009 at 10:36 AM, Tompkins Neil
>> >>
>> >> > Hi
>> >> >
>> >> > Thanks for your quick response.=A0 In our test environment running
>> >> > MySQL
>> >> > 5.1.31 over SSL (hosted external of our network)=A0and IIS 6 on Win=
dows
>> >> > 2003
>> >> > Server, I've found when opening a test connection to the database
>> >> > from
>> >> > my PC
>> >> > and one from my colleagues PC on the same internal network, we both
>> >> > had
>> >> > the
>> >> > same connectionID ?=A0 Is this right ?
>> >> >
>> >> > The reason I need to know about the CONNECTION_ID() is because we
>> >> > have a
>> >> > issue on our website whereby not all database connections are
>> >> > correctly
>> >> > closed.=A0 As commented on in this URL
>> >> > http://bugs.mysql.com/bug.php?id=3D12274
>> >> >
>> >> > Can anyone offer any advice regarding this ?
>> >> >
>> >> > Regards
>> >> > Neil
>> >> >
>> >> > On Wed, Nov 4, 2009 at 3:27 PM, Michael Dykman
>> >> > wrote:
>> >> >>
>> >> >> Typically, a =A0thread works on a single connection exclusively..
>> >> >> otherwise it is impossible to do anything transactional. =A0Howeve=
r, I
>> >> >> have seen applications, in auto-commit mode use distinct statement=
s
>> >> >> concurrently from the same connection to do read-only work.
>> >> >>
>> >> >> If you are worried about the latency of creating new connections f=
or
>> >> >> a
>> >> >> many-threaded design, you should be looking into connection poolin=
g
>> >> >> which manages the problem of maintaining many connections and only
>> >> >> letting one thread have a given connection at a time.
>> >> >>
>> >> >> =A0- michael dykman
>> >> >>
>> >> >> On Wed, Nov 4, 2009 at 10:20 AM, Tompkins Neil
>> >> >>
>> >> >> > Hi,
>> >> >> >
>> >> >> > With regards the CONNECTION_ID() variable in MySQL, is this base=
d
>> >> >> > on
>> >> >> > a
>> >> >> > per
>> >> >> > user basis. =A0 Or can multiple users use the same connection_id=
?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Neil
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >> >> --
>> >> >> =A0- michael dykman
>> >> >> =A0- mdykman@gmail.com
>> >> >>
>> >> >> "May you live every day of your life."
>> >> >> =A0 =A0Jonathan Swift
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> =A0- michael dykman
>> >> =A0- mdykman@gmail.com
>> >>
>> >> "May you live every day of your life."
>> >> =A0 =A0Jonathan Swift
>> >
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> "May you live every day of your life."
>> =A0 =A0Jonathan Swift
>
>
--=20
- michael dykman
- mdykman@gmail.com
"May you live every day of your life."
Jonathan Swift
Larry's First Law of Language Redesign: Everyone wants the colon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg