Re: [Zope] ZSQL Method Question
Aloha,
I think so, for the moment anyhow!
Thanks all,
John S.
Cliff Ford wrote:
Comment on Peter's suggestion: I am no expert on these things, but it is
my understanding that for MySQL LAST_INSERT_ID() fetches the last
autoincrement value made by the current insert, so the outcome is not
affected by virtually simultaneous requests. And I don't think MySQL
accepts a value for the autoincrement key. I am a bit surprised that the
code you quote is reliable - surely there is a possibility of an insert
after calling GetNextID and before calling SQLInsert in the python code?
Comment on Tino's suggestion: I think that LAST_INSERT_ID() is MySQL
specific and I guess CURRVAL() is Postgres specific. But the principle
is the same: calling within the same Z SQL Method ought to be safe.
Anyway, I hope John has had is question answered.
Cliff
Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec?
What if between the INSERT and the LAST_INSERT_ID() another INSERT is
made?
I use PostgreSQL and with postgres you can always ask the sequence what
the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id
context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier:
one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES
(nextval('foo_foo_id_seq'), ... );
SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards
Tino
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
--
John Schinnerer - MA, Whole Systems Design
--
- Eco-Living -
Whole Systems Design Services
People - Place - Learning - Integration
[EMAIL PROTECTED]
http://eco-living.net
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
Comment on Peter's suggestion: I am no expert on these things, but it is
my understanding that for MySQL LAST_INSERT_ID() fetches the last
autoincrement value made by the current insert, so the outcome is not
affected by virtually simultaneous requests. And I don't think MySQL
accepts a value for the autoincrement key. I am a bit surprised that the
code you quote is reliable - surely there is a possibility of an insert
after calling GetNextID and before calling SQLInsert in the python code?
Comment on Tino's suggestion: I think that LAST_INSERT_ID() is MySQL
specific and I guess CURRVAL() is Postgres specific. But the principle
is the same: calling within the same Z SQL Method ought to be safe.
Anyway, I hope John has had is question answered.
Cliff
Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec?
What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what
the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id
context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier:
one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES
(nextval('foo_foo_id_seq'), ... );
SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards
Tino
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
Aloha,
Is this even easier solution only for postgreSQL, or is it for MySQL?
I am going to have to start working with integrating MySQL into
zope-based stuff soon so I'm trying to get a head start... :-)
thanks,
John S.
Tino Wildenhain wrote:
Peter Bengtsson wrote:
What if you have 1,000,000 requests/sec?
What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what
the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id
context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier:
one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES
(nextval('foo_foo_id_seq'), ... );
SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards
Tino
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
--
John Schinnerer - MA, Whole Systems Design
--
- Eco-Living -
Whole Systems Design Services
People - Place - Learning - Integration
[EMAIL PROTECTED]
http://eco-living.net
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
Peter Bengtsson wrote:
> What if you have 1,000,000 requests/sec?
> What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
>
> I use PostgreSQL and with postgres you can always ask the sequence what
> the next id is going to be. It goes something like this::
>
> next_id = context.GetNextId()[0].next_id
> context.SQLInsertUser(uid=next_id, name='Peter')
>
> where 'GetNextId' is a ZSQL method that looks like this::
>
>
> SELECT NEXTVAL('users_uid_seq') AS next_id
Its even easier:
one ZSQL Method:
INSERT INTO foo (foo_id,blah,bar) VALUES
(nextval('foo_foo_id_seq'), ... );
SELECT CURRVAL('foo_foo_id_seq') as foo_id;
But your above solution is valid too.
Regards
Tino
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
What if you have 1,000,000 requests/sec?
What if between the INSERT and the LAST_INSERT_ID() another INSERT is made?
I use PostgreSQL and with postgres you can always ask the sequence what
the next id is going to be. It goes something like this::
next_id = context.GetNextId()[0].next_id
context.SQLInsertUser(uid=next_id, name='Peter')
where 'GetNextId' is a ZSQL method that looks like this::
SELECT NEXTVAL('users_uid_seq') AS next_id
Cliff Ford wrote:
This is how it works for MySQL:
insert into org (org_name, org_phone) values ('x', 'y')
select LAST_INSERT_ID() as org_id
You have to have the select LAST_INSERT_ID call in the same query as the
insert, and you have to have the sql_delimiter.
I assume you know that the x and y values should be types.
Cliff
Benjamin Menking wrote:
New to python/Zope, old-timer on PHP
I'm using MySQL and a ZSQL method to insert data into the database. ex:
insert into org (org_name, org_phone) values ('x', 'y')
What I'm trying to figure out is that org_id (also part of the org
table, but not specified in the sql statement) is an auto_increment
primary key field and in PHP I can use mysql_insert_id() to find out
what org_id was set to after the mysql_query() call.
Is there a way to retrieve that value with ZSQL method, or must I use
some other mechanism?
Thanks!
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists - http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
--
Peter Bengtsson,
work www.fry-it.com
home www.peterbe.com
hobby www.issuetrackerproduct.com
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
This is how it works for MySQL:
insert into org (org_name, org_phone) values ('x', 'y')
select LAST_INSERT_ID() as org_id
You have to have the select LAST_INSERT_ID call in the same query as the
insert, and you have to have the sql_delimiter.
I assume you know that the x and y values should be types.
Cliff
Benjamin Menking wrote:
New to python/Zope, old-timer on PHP
I'm using MySQL and a ZSQL method to insert data into the database. ex:
insert into org (org_name, org_phone) values ('x', 'y')
What I'm trying to figure out is that org_id (also part of the org
table, but not specified in the sql statement) is an auto_increment
primary key field and in PHP I can use mysql_insert_id() to find out
what org_id was set to after the mysql_query() call.
Is there a way to retrieve that value with ZSQL method, or must I use
some other mechanism?
Thanks!
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Question
when you do an insert with a ZSQL method you do not
get back any info from mysql, you need to do a subsequent search to get
'autogenerated' columns. So do your insert, then do an ZSQL search.
The search returns a 'Results' object which has several methods you can use to
get at the search results.
In a python script try something like:
rstat=context.UserActivity.SQL_SearchEvents(userId='111')
#substitute your own zsql search
print 'rstat= ', rstatprint 'rstat.names=
',rstat.names()print 'rstat.tuples= ',rstat.tuples()print 'rstat.asRDB=
',rstat.asRDB()print 'rstat.data_dictionary=
',rstat.data_dictionary()print 'rstat.dictionaries=
',rstat.dictionaries()
hth
Jonathan
- Original Message -
From:
Benjamin
Menking
To: zope@zope.org
Sent: Monday, July 17, 2006 1:01 PM
Subject: [Zope] ZSQL Method
Question
New to python/Zope, old-timer on
PHPI'm using MySQL and a ZSQL method to insert data into the
database. ex: insert into org (org_name,
org_phone) values ('x', 'y')What I'm trying to figure out is that
org_id (also part of the org table, but not specified in the sql statement) is
an auto_increment primary key field and in PHP I can use mysql_insert_id() to
find out what org_id was set to after the mysql_query() call.Is there
a way to retrieve that value with ZSQL method, or must I use some other
mechanism?Thanks!--
Ben Menking RHCE
IT Security Consulting
(864) 420-5603
(864) 751-1638 fax
IT and Business Solutions
___Zope maillist
-
Zope@zope.orghttp://mail.zope.org/mailman/listinfo/zope**
No cross posts or HTML encoding! **(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev
)
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
[Zope] ZSQL Method Question
New to python/Zope, old-timer on PHP
I'm using MySQL and a ZSQL method to insert data into the database. ex:
insert into org (org_name, org_phone) values ('x', 'y')
What I'm trying to figure out is that org_id (also part of the org
table, but not specified in the sql statement) is an auto_increment
primary key field and in PHP I can use mysql_insert_id() to find out
what org_id was set to after the mysql_query() call.
Is there a way to retrieve that value with ZSQL method, or must I use
some other mechanism?
Thanks!
--
Ben Menking RHCE
IT Security Consulting
(864) 420-5603
(864) 751-1638 fax
IT and Business Solutions
___
Zope maillist - Zope@zope.org
http://mail.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://mail.zope.org/mailman/listinfo/zope-announce
http://mail.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] ZSQL Method & AUTHENTICATED_USER
I have an approach that works for me, but it may be a bit awkward. I have a SQL method (dbGetUserID) which takes AUTHENTICATED_USER as an argument and finds the value from the "userid" field in the database: select userid from users where shortname = When I need to perform database ops on the user, I just call this method and use the resulting id, eg, insert into sometable (userid, otherfield) values (, ) I have AUTHENTICATED_USER in the parameter list for this SQL method as well, though it may not need to be... Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jon Prettyman Sent: Thursday, 19 October 2000 8:18 To: [EMAIL PROTECTED] Subject: Re: [Zope] ZSQL Method & AUTHENTICATED_USER I'm pretty sure that all variables have to be passed in explicitly with SQLMethods. Namespaces are not passed in. Try specifying username as an argument in the ZSQLMethods edit form and pass it in like this: (Untested) -jon Aaron Straup Cope <[EMAIL PROTECTED]> writes: > Hi, > > I'm trying to perform a simple MySQL lookup based on the > AUTHENTICATED_USER. When I test the ZSQL method from the management > interface, everything works fine. However, when I try to call the method > from a DTML document, [it] returns nothing. > > Is this a namespace issue? I'm stumped and hoping that I'm not staring > right past the problem. Can anyone point out what it is I am doing > wrong? Thanks, > > a) DTML Document : foo > > > > > > > > b) ZSQL Method : people.lookup_by_username > > select * from people where username = " "REQUEST.AUTHENTICATED_USER.getUserName()">" > > > > ___ > Zope maillist - [EMAIL PROTECTED] > http://lists.zope.org/mailman/listinfo/zope > ** No cross posts or HTML encoding! ** > (Related lists - > http://lists.zope.org/mailman/listinfo/zope-announce > http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method & AUTHENTICATED_USER
Thanks. It was a syntax issue. I think I tried every single variation on passing variables to the method except that one. Sorry for the bother, folks... :-) On 18 Oct 2000, Jon Prettyman wrote: > I'm pretty sure that all variables have to be passed in explicitly > with SQLMethods. Namespaces are not passed in. > > Try specifying username as an argument in the ZSQLMethods edit form > and pass it in like this: > "lookup_by_username(username=REQUEST.AUTHENTICATED_USER.getUserName()"> > > (Untested) > > -jon > > Aaron Straup Cope <[EMAIL PROTECTED]> writes: > > > Hi, > > > > I'm trying to perform a simple MySQL lookup based on the > > AUTHENTICATED_USER. When I test the ZSQL method from the management > > interface, everything works fine. However, when I try to call the method > > from a DTML document, [it] returns nothing. > > > > Is this a namespace issue? I'm stumped and hoping that I'm not staring > > right past the problem. Can anyone point out what it is I am doing > > wrong? Thanks, > > > > a) DTML Document : foo > > > > > > > > > > > > > > > > b) ZSQL Method : people.lookup_by_username > > > > select * from people where username = " > "REQUEST.AUTHENTICATED_USER.getUserName()">" > > > > > > > > ___ > > Zope maillist - [EMAIL PROTECTED] > > http://lists.zope.org/mailman/listinfo/zope > > ** No cross posts or HTML encoding! ** > > (Related lists - > > http://lists.zope.org/mailman/listinfo/zope-announce > > http://lists.zope.org/mailman/listinfo/zope-dev ) > > ___ > Zope maillist - [EMAIL PROTECTED] > http://lists.zope.org/mailman/listinfo/zope > ** No cross posts or HTML encoding! ** > (Related lists - > http://lists.zope.org/mailman/listinfo/zope-announce > http://lists.zope.org/mailman/listinfo/zope-dev ) > > ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method & AUTHENTICATED_USER
I'm pretty sure that all variables have to be passed in explicitly with SQLMethods. Namespaces are not passed in. Try specifying username as an argument in the ZSQLMethods edit form and pass it in like this: (Untested) -jon Aaron Straup Cope <[EMAIL PROTECTED]> writes: > Hi, > > I'm trying to perform a simple MySQL lookup based on the > AUTHENTICATED_USER. When I test the ZSQL method from the management > interface, everything works fine. However, when I try to call the method > from a DTML document, [it] returns nothing. > > Is this a namespace issue? I'm stumped and hoping that I'm not staring > right past the problem. Can anyone point out what it is I am doing > wrong? Thanks, > > a) DTML Document : foo > > > > > > > > b) ZSQL Method : people.lookup_by_username > > select * from people where username = " "REQUEST.AUTHENTICATED_USER.getUserName()">" > > > > ___ > Zope maillist - [EMAIL PROTECTED] > http://lists.zope.org/mailman/listinfo/zope > ** No cross posts or HTML encoding! ** > (Related lists - > http://lists.zope.org/mailman/listinfo/zope-announce > http://lists.zope.org/mailman/listinfo/zope-dev ) ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] ZSQL Method & AUTHENTICATED_USER
Hi, I'm trying to perform a simple MySQL lookup based on the AUTHENTICATED_USER. When I test the ZSQL method from the management interface, everything works fine. However, when I try to call the method from a DTML document, [it] returns nothing. Is this a namespace issue? I'm stumped and hoping that I'm not staring right past the problem. Can anyone point out what it is I am doing wrong? Thanks, a) DTML Document : foo b) ZSQL Method : people.lookup_by_username select * from people where username = "" ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL Method Timings/Profiling or MySQL SQL ExecutionTiming Statistics
I'm answering myself because I found a solution to my problem, and I
wanted to share the results with the list, in case someone is struggling
to try to do the same thing...
The problem with mysql (go easy, we're not talking about the fact that
mysql is primitive and overall does not match up to the known
transactional rdbms) is that it does not provide any sql statement
profiling or timing statistics. If you use the mysql client to check a
query, you can see a time. You can even write a perl script utilizing the
benchmark module that comes with mysql to get more sql timings.
However, neither of these methods work with Zope (i.e. do you really feel
like interfacing to perl from Zope, and then calling the benchmark module
for EVERY one of your queries?). A more "automated" approach is desirable,
that compares to Oracle's profiling that logs ALL queries to a file with
timing statistics.
I finally decided after searching for a few hours for information, that I
would modify Zope to handle this for me. The logical place was to put it
in ZMySQLDA. I'm attaching a patch of a really _simple_ logging
mechanism. The only activity that really occurs is the time is noted
before and after the query gets processed and fetched, and the difference
is noted. Finally, it gets logged to /tmp/mysql_timing.log --- You need to
turn off MYSQL_TIMING in db.py of ZMySQL after applying the patch should
you not want the timing to occur any longer. And as usual, a Zope restart
is required after patching your ZMySQLDA.
The patch was written for ZMySQLDA 1.2.0 (nothing below).
Knight
[EMAIL PROTECTED]
On Mon, 9 Oct 2000, knight wrote:
> Greetings,
>
> I have a lot of past experience with tuning and timing on Oracle (quite
> simple), but I seem to have hit a hurdle with timing my sql statements in
> MySQL. Getting these timings are _incredibly_ important to finding
> bottlenecks in a web site's performance...
>
> Now, first, is it possible to turn on profiling for ZSQL methods? I've
> turned on my Zope's profiling, and I can see statistics on how many times
> sqlvar was called, but not specific zsql methods. Second, anyone familiar
> with any mysql settings that will dump a log displaying real-time sql
> executions and the corresponding execute time?
>
> I like to turn sql timing on, then run around the site for a while, send
> some people to test it out, or throw up a http load tester script up, and
> then check the timing logs to see how things worked out.
>
> Regards,
>
> Knight
> [EMAIL PROTECTED]
>
>
> ___
> Zope maillist - [EMAIL PROTECTED]
> http://lists.zope.org/mailman/listinfo/zope
> ** No cross posts or HTML encoding! **
> (Related lists -
> http://lists.zope.org/mailman/listinfo/zope-announce
> http://lists.zope.org/mailman/listinfo/zope-dev )
>
>
--- db.py Thu Jun 15 02:10:41 2000
+++ db.py Mon Oct 9 18:06:53 2000
@@ -142,6 +142,12 @@
def __init__(self,connection):
self.connection=connection
+self.MYSQL_TIMING = 1
+if self.MYSQL_TIMING:
+ self.timinglog = open('/tmp/mysql_timing.log', 'w+')
+ if not self.timinglog:
+print "Error: Can't open /tmp/mysql_timing.log for writing"
+self.MYSQL_TIMING = None
info=split(connection)
if info and len(info) < 4:
@@ -174,6 +180,7 @@
select_m=regex.compile('[\0- ]*select[\0- ]+',
regex.casefold).match,
):
+import time
db=self.db
try:
queries=filter(None, map(strip,split(query_string, '\0')))
@@ -181,11 +188,22 @@
result=()
desc=None
for qs in queries:
+if self.MYSQL_TIMING:
+ start_time = time.time()
db.query(qs)
+
c=db.store_result()
try:
desc=c.describe()
r=c.fetch_all_rows()
+if self.MYSQL_TIMING:
+ end_time = time.time()
+ tot_time = end_time - start_time
+ tmpstr = "SQL Statement: " + qs + "\n"
+ self.timinglog.write(tmpstr)
+ tmpstr = "Execution Time: " + str(tot_time*1000) + "
+miliseconds (ms)\n\n"
+ self.timinglog.write(tmpstr)
+ self.timinglog.flush()
except: r=None
if not r: continue
if result:
[Zope] ZSQL Method Timings/Profiling or MySQL SQL Execution Timing Statistics
Greetings, I have a lot of past experience with tuning and timing on Oracle (quite simple), but I seem to have hit a hurdle with timing my sql statements in MySQL. Getting these timings are _incredibly_ important to finding bottlenecks in a web site's performance... Now, first, is it possible to turn on profiling for ZSQL methods? I've turned on my Zope's profiling, and I can see statistics on how many times sqlvar was called, but not specific zsql methods. Second, anyone familiar with any mysql settings that will dump a log displaying real-time sql executions and the corresponding execute time? I like to turn sql timing on, then run around the site for a while, send some people to test it out, or throw up a http load tester script up, and then check the timing logs to see how things worked out. Regards, Knight [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] ZSQL method broken on 2.1.6
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Andreas > Sent: Saturday, June 10, 2000 1:59 AM > To: Umesh Soni > Cc: Phill Hugo; [EMAIL PROTECTED] > Subject: Re: [Zope] ZSQL method broken on 2.1.6 > > > >> > > >> (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) > > >> > As I know so far there is no way to disable this property or to set a > value like 'maximum' This version of ZMySQLDA (and possibly others) doesn't put the LIMIT statement in at all if you set the maximum number of rows to 0 (zero). Like someone else said, it's a "safety net" for queries that may accidently return way too many records. ___ Ron Bickers Logic Etc, Inc. [EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Andreas writes: > ZSQLMethod's "advanced" property 'Maximum number of rows retrieved' > makes no sense to me. It prevents stupid queries to kill Zope. Assume, such a query returns 100.000.000 records. This probably will let Zope require several GB memory. > As I know so far there is no way to disable this property or to set a > value like 'maximum' What about a value of 2.000.000.000. Should be enough for most purposes. Dieter ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Umesh Soni wrote: > > >Umesh Soni wrote: > >> > >> (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) > >> > >> Hi there, > >> > >> I had this ZSQL method (simplified for clarity) working on 2.1.4 > >> > >> select id, nickname from bboard > >> where id<=50 > >> order by id desc > >> limit 30 > > > >Remove either the limit 30 or the LIMIT 1000 Advanced property of the > >ZSQL method (advanced tab). To speed things up, the limit 1000 appended > >to the query to stop MySQL giving Zope lots of lines when it only wants > >1000 anyway. Its not too clever though. There should maybe be a check to > >see if "limit x" exists in the query. > > > >Phill > > > > Thanks Phill, that did the trick --strange thing though the 'Maximum > number of rows retrieved attribute' was set to 100 not 1000 as the > error reported --I changed it to 30. > I`m interested in your thoughts about ZSQLMethod's advanced property 'Maximum number of rows retrieved'. It`s default value is 1000. Some DA's like PyGreSQLDA takes no notice on this value while PoPyDA pays attention of this property. Assumed there is a table foo on my DB with actually 2000 records some DA's return only 1000 on table scans like 'select * from foo' and 10 records on 'select * from foo limit 10 offset 1100' (if offset is supported by the RDBMS). How can I know how many records will match at max to my select statement? ZSQLMethod's "advanced" property 'Maximum number of rows retrieved' makes no sense to me. As I know so far there is no way to disable this property or to set a value like 'maximum' -- _ Andreas Heckel[EMAIL PROTECTED] ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
>Umesh Soni wrote: >> >> (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) >> >> Hi there, >> >> I had this ZSQL method (simplified for clarity) working on 2.1.4 >> >> select id, nickname from bboard >> where id<=50 >> order by id desc >> limit 30 > >Remove either the limit 30 or the LIMIT 1000 Advanced property of the >ZSQL method (advanced tab). To speed things up, the limit 1000 appended >to the query to stop MySQL giving Zope lots of lines when it only wants >1000 anyway. Its not too clever though. There should maybe be a check to >see if "limit x" exists in the query. > >Phill > Thanks Phill, that did the trick --strange thing though the 'Maximum number of rows retrieved attribute' was set to 100 not 1000 as the error reported --I changed it to 30. Anyway it's working now --cheers. Umesh Soni. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] ZSQL method broken on 2.1.6
Umesh Soni wrote: > > (using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) > > Hi there, > > I had this ZSQL method (simplified for clarity) working on 2.1.4 > > select id, nickname from bboard > where id<=50 > order by id desc > limit 30 Remove either the limit 30 or the LIMIT 1000 Advanced property of the ZSQL method (advanced tab). To speed things up, the limit 1000 appended to the query to stop MySQL giving Zope lots of lines when it only wants 1000 anyway. Its not too clever though. There should maybe be a check to see if "limit x" exists in the query. Phill ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] ZSQL method broken on 2.1.6
(using zope 2.1.6 with ZMySQLDA-2.0.0a1 and MySQLdb-0.2.1) Hi there, I had this ZSQL method (simplified for clarity) working on 2.1.4 select id, nickname from bboard where id<=50 order by id desc limit 30 Now, I've upgraded to 2.1.6 and I get the following error; Error, _mysql.OperationalError: (1064, "You have an error in your SQL syntax near 'LIMIT 1000' at line 4") -this is wierd because my ZSQL method says "limit 30" The traceback displayed is; Traceback (innermost last): File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 214, in publish_module File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 179, in publish File /usr/local/Zope/lib/python/Zope/__init__.py, line 202, in zpublisher_exception_hook (Object: simple-test) File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 165, in publish File /usr/local/Zope/lib/python/ZPublisher/mapply.py, line 160, in mapply (Object: manage_test) File /usr/local/Zope/lib/python/ZPublisher/Publish.py, line 102, in call_object (Object: manage_test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 331, in manage_test (Object: simple-test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 312, in manage_test (Object: simple-test) File /usr/local/Zope/lib/python/Shared/DC/ZRDB/DA.py, line 430, in __call__ (Object: simple-test) File /usr/local/Zope/lib/python/Products/ZMySQLDA/db.py, line 221, in query OperationalError: (see above) Note: if I remove the limit constraint as in; select id, nickname from bboard where id<=50 order by id desc the ZSQL method works fine. I do need to limit the number of results returned. Does anyone know a workaround for this? umesh ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] zsql method??/
Jonathan Park wrote: > > Hello everyone! > > I have a dtml method that calls in the sql method like this > > the result I get is what I would expect except in the unique_# field 'L' > is added at the end of the number > ex: > > unique_#blahblah blah > 1L data data data > 2L data data data > > any reason why it is adding the 'L'??? Is this with Sybase by any chance? Search the archives, look for Sybase & long & integers Here is one result (WARNING: long line, mayu be broken up by reader): http://zope.nipltd.com/public/lists/zope-archive.nsf/47ba74c812dbc5dd8025687f0024bb5f/86e528e0223cb6d780256891001fe991?OpenDocument&Highlight=0,sybase,integer,long ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] zsql method??/
Hi Jon,
I had the same trouble when returning values from a user db of mine. I
believe the L indicates that the value returned is of type "Long Integer."
I am using MySQL, which has a function called CONV(). This is the function
I used to fix the problem. Here is an explanation of the function from the
MySQL manual (found at http://www.mysql.com/php/manual.php3):
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string
representation of the number N, converted from base from_base to base
to_base. Returns NULL if any argument is NULL. The argument N is interpreted
as an integer, but may be specified as an integer or a string. The minimum
base is 2 and the maximum base is 36. If to_base is a negative number, N is
regarded as a signed number. Otherwise, N is treated as unsigned. CONV works
with 64-bit precision.
mysql> select CONV("a",16,2);
-> '1010'
mysql> select CONV("6E",18,8);
-> '172'
mysql> select CONV(-17,10,-18);
-> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
-> '40'
In my case I just did something like:
CONV(TheNumber, 10, 10)
If you're not using MySQL, maybe there is a similar function in your db(?)
Hope this helps.
Eric.
// -Original Message-
// From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
// Jonathan Park
// Sent: Thursday, May 18, 2000 1:35 PM
// To: [EMAIL PROTECTED]
// Subject: [Zope] zsql method??/
//
//
// Hello everyone!
//
// I have a dtml method that calls in the sql method like this
//
// the result I get is what I would expect except in the unique_#
// field 'L'
// is added at the end of the number
// ex:
//
// unique_#blahblah blah
// 1L data data data
// 2L data data data
//
// any reason why it is adding the 'L'???
//
// thanks
// -jon
//
// Have a great afternoon!
//
//
// ___
// Zope maillist - [EMAIL PROTECTED]
// http://lists.zope.org/mailman/listinfo/zope
// ** No cross posts or HTML encoding! **
// (Related lists -
// http://lists.zope.org/mailman/listinfo/zope-announce
// http://lists.zope.org/mailman/listinfo/zope-dev )
//
___
Zope maillist - [EMAIL PROTECTED]
http://lists.zope.org/mailman/listinfo/zope
** No cross posts or HTML encoding! **
(Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce
http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] zsql method??/
Oops, I forgot to mention that the CONV function is used in your ZSQL method. Here's the ZSQL Method I use (returns upcoming birthdays falling within this, and the next, month): SELECT FirstName, LastName, MONTH(Birthday) AS Month, DAYOFMONTH(Birthday)AS Day, DATE_FORMAT(Birthday, '%M %D, %Y') AS BDay, CONV(ROUND((TO_DAYS(NOW())-TO_DAYS(Birthday))/365.25), 10, 10) as WillBeAge FROM users WHERE MONTH(Birthday) = MOD(MONTH(NOW()), 12) + 1 OR (MONTH(Birthday) = MONTH(NOW()) AND DAYOFMONTH(Birthday) >= DAYOFMONTH(NOW())) ORDER BY Month, Day // -Original Message- // From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of // Jonathan Park // Sent: Thursday, May 18, 2000 1:35 PM // To: [EMAIL PROTECTED] // Subject: [Zope] zsql method??/ // // // Hello everyone! // // I have a dtml method that calls in the sql method like this // // the result I get is what I would expect except in the unique_# // field 'L' // is added at the end of the number // ex: // // unique_#blahblah blah // 1L data data data // 2L data data data // // any reason why it is adding the 'L'??? // // thanks // -jon // // Have a great afternoon! // // // ___ // Zope maillist - [EMAIL PROTECTED] // http://lists.zope.org/mailman/listinfo/zope // ** No cross posts or HTML encoding! ** // (Related lists - // http://lists.zope.org/mailman/listinfo/zope-announce // http://lists.zope.org/mailman/listinfo/zope-dev ) // ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
RE: [Zope] zsql method??/ (L suffix on unique#)
I suppose this is related to a "bug" that has been "fixed" in Python 1.6a1: http://www.python.org/1.6 >... >Expected Code Breakage >... > >The "nice" string representation of long integers no longer has an 'L' suffix. For >example, print 10L**10 now prints 100 where >it used to print 100L. This affects str(long) too; code breakage is expected for >code that tries to strip off the 'L' without looking, like this: print str(x)[:-1]. You >can use repr(), which returns the 'L' suffix as before. > Just when you thought you understood the world, it changes. ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Re: [Zope] zsql method??/
Sounds like you have to rename your function. so that it does not include spaces. and include () wait till you get to parameters, that's a mind bender. Have Fun! Luis. On Thu, 18 May 2000, you wrote: >> Hello everyone! >> >> I have a dtml method that calls in the sql method like this >> >> the result I get is what I would expect except in the unique_# field 'L' >> is added at the end of the number >> ex: >> >> unique_#blahblah blah >> 1L data data data >> 2L data data data >> >> any reason why it is adding the 'L'??? >> >> thanks >> -jon >> >> Have a great afternoon! >> >> >> ___ >> Zope maillist - [EMAIL PROTECTED] >> http://lists.zope.org/mailman/listinfo/zope >> ** No cross posts or HTML encoding! ** >> (Related lists - >> http://lists.zope.org/mailman/listinfo/zope-announce >> http://lists.zope.org/mailman/listinfo/zope-dev ) -- == Luis Cortes Pollak EPD (915) 621-6113 == ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
[Zope] zsql method??/
Hello everyone! I have a dtml method that calls in the sql method like this the result I get is what I would expect except in the unique_# field 'L' is added at the end of the number ex: unique_#blahblah blah 1L data data data 2L data data data any reason why it is adding the 'L'??? thanks -jon Have a great afternoon! ___ Zope maillist - [EMAIL PROTECTED] http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
