I want to know how to configurate slow-query-log to let it not record the
update sql.
I just want to know how the slow select statement ,not the update or insert.
Anybody's reply is appreciated,thanks.
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
You could use a view:
CREATE VIEW all_scores
SELECT s.tid, s.vid, s.uid, s.highScore
FROM score s
JOIN objects o
ON s.tid = o.tid
JOIN itemtypes it
ON s.vid = it.vid
JOIN users u
ON s.uid = u.uid
WHERE o.shortname = %s /* Should these ANDs really be ORs? */
AND i.itemtype LIKE %s;
SELECT highSco
On 20.12.2007 22:46 CE(S)T, Martijn Tonies wrote:
>> Okay, I got that. So a COMMIT statement after disabling autocommit mode
>> and another START TRANSACTION does not finish my transaction. (But then,
>> what does?) Interesting view, I didn't know that. But now all's clear: I
>> won't touch autocom
Brent Baisley skrev:
You're right that wouldn't work because you can't assign the query
results to a variable. You want to put the variables in the query and
assign them to the value of the field.
...WHERE s2.tid = (SELECT @tid:=tid AS tid FROM objects WHERE shortname = %s)...
That should work b
Brent,
you put me on the right track.. ;) Reading up a bit on syntax for
variables, I came up with the following, which seems to work.
SET @tid:=(SELECT tid FROM objects WHERE shortname = %s);
SET @vid:=(SELECT vid FROM itemtypes WHERE itemtype LIKE %s);
SELECT (count(*)+)/(SELECT COUNT(*) FROM
Brent Baisley skrev:
You might be able to use variables to store the result of the query.
Although I've never tried assigning the result of a query to a
variable, only field values.
SELECT (count(*)+)/(SELECT COUNT(*) FROM score AS s2
WHERE s2.tid = @tid:=(SELECT tid FROM objects WHERE shortname
> >> So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
> >> and "START TRANSACTION" start a new transaction level, is that true?
> >
> > I didn't say it supports nested transactions, I said that if your
> > application
> > starts a single transaction and does not finish it, it
On 20.12.2007 21:34 CE(S)T, Martijn Tonies wrote:
>> So MySQL does support nested transaction and both "SET AUTOCOMMIT = 0"
>> and "START TRANSACTION" start a new transaction level, is that true?
>
> I didn't say it supports nested transactions, I said that if your
> application
> starts a single
On 20.12.2007 22:18 CE(S)T, Baron Schwartz wrote:
> On Dec 20, 2007 3:33 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
>> I wasn't able to find MVCC-related information (I assume it means Multi
>> Version Concurrency Control, not sure whether that's correct) in the
>> MySQL manual. But Martijn's expl
Hi,
On Dec 20, 2007 3:33 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> On 20.12.2007 21:14 CE(S)T, Baron Schwartz wrote:
> > It doesn't support nested transactions. What you're seeing is the
> > effects of MVCC. The InnoDB section of the MySQL manual explains it.
>
> I wasn't able to find MVCC-r
ryan...
mysql has the ability to set access rights to a given database/tables for
given users.
so it's easy to create a database, and to only allow a given set of users
access to the database. in fact, if you lock it down enough, only the user
with permissions will know the db exists... unless th
> On 20.12.2007 20:34 CE(S)T, Martijn Tonies wrote:
> > Are your tables InnoDB? If so, the snapshot transaction is giving you
> > a static view on the data and your own changes, while your PHPMyAdmin
> > commits the NULL write. Your application keeps on seeing your own
> > changes, cause it did
On 20.12.2007 21:14 CE(S)T, Baron Schwartz wrote:
> It doesn't support nested transactions. What you're seeing is the
> effects of MVCC. The InnoDB section of the MySQL manual explains it.
I wasn't able to find MVCC-related information (I assume it means Multi
Version Concurrency Control, not su
On 20.12.2007 20:34 CE(S)T, Martijn Tonies wrote:
> Are your tables InnoDB? If so, the snapshot transaction is giving you
> a static view on the data and your own changes, while your PHPMyAdmin
> commits the NULL write. Your application keeps on seeing your own
> changes, cause it did not end the s
Hi,
On Dec 20, 2007 2:26 PM, Yves Goergen <[EMAIL PROTECTED]> wrote:
> On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> > But when I set that column to NULL
> > with phpMyAdmin, my application still reads the old data from the
> > database. phpMyAdmin keeps telling me that the value is actually N
Yves,
> On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> > But when I set that column to NULL
> > with phpMyAdmin, my application still reads the old data from the
> > database. phpMyAdmin keeps telling me that the value is actually NULL,
> > which I just entered. Whereas the persistent PHP conn
On 20.12.2007 19:42 CE(S)T, Yves Goergen wrote:
> But when I set that column to NULL
> with phpMyAdmin, my application still reads the old data from the
> database. phpMyAdmin keeps telling me that the value is actually NULL,
> which I just entered. Whereas the persistent PHP connection doesn't see
Okay, I've convinced myself that this can't be done. In the http world,
it can be done only because http/1.1 includes the hostname in the request.
If mysql doesn't do that, there's no way to handle all the cases.
So I'll either have to settle for people remembering their own socket
files, or ...
Hi,
today I have noticed a strange bug with MySQL and PHP. I'm developing a
PHP application, using the MySQL database server 5.0.45 on Windows XP
and the PDO connection objects (PHP Data Objects). The PHP application
works on InnoDB tables and uses transactions and persistent connections.
One of t
You're right and I should have thought that through. This is a new build on
a new server with an imported dump, and of course that data was conserved.
However, why doesn't it let me connect to the databases? I've entered as the
root user, and granted the priviledges, as in my previous email, and th
My guess: The user could already be there. Is this a fresh DB, can you check
the server logs for more info?
Regards,
Amit Sharma
E: [EMAIL PROTECTED]
M: +91 9971 490 700
W: www.affle.co.uk
> -Original Message-
> From: Victor Subervi [mailto:[EMAIL PROTECTED]
> Sent: 20 December 2007 22
Hi;
I can create the following just fine:
grant all on data1.* to myuser IDENTIFIED BY 'pass';
grant all on data2.* to myuser IDENTIFIED BY 'pass';
...but when I try this:
CREATE USER myuser IDENTIFIED BY 'pass';
ERROR 1396 (HY000): Operation CREATE USER failed for 'myuser'@'%'
...and this:
> -Original Message-
> From: Jay Pipes [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 20, 2007 11:25 AM
> To: Jerry Schwartz
> Cc: mysql@lists.mysql.com
> Subject: Re: Forbidden subquery
>
> No problem. I hope by now you figured out I made a typo... :) The
> WHERE in the DELETE shou
No problem. I hope by now you figured out I made a typo... :) The
WHERE in the DELETE should be prod_price_chg_flag='O', not ="X" :)
-jay
Jerry Schwartz wrote:
Hi Jerry!
The very last sentence on:
http://dev.mysql.com/doc/refman/5.0/en/delete.html
is "Currently, you cannot delete from a ta
>
> Hi Jerry!
>
> The very last sentence on:
> http://dev.mysql.com/doc/refman/5.0/en/delete.html
>
> is "Currently, you cannot delete from a table and select from the same
> table in a subquery."
>
[JS] Yes, I knew that. I just thought that illegal query was the best way of
expressing what I wante
I did a CHECK TABLE and it reported no errors.
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
From: Rodrigo Marins [
> > What I want to accomplish is expressed best as
> >
> > DELETE FROM prod_price
> > WHERE prod_price.prod_price_chg_flag = "O"
> > AND prod_price.prod_id IN
> >
> >(SELECT prod_price.prod_id FROM prod_price
> >WHERE prod_price.prod_price_chg_flag = "X")
> > ;
> >
> > This is clear, concis
I'm really not sure what to try next. ps -aux shows MySQL as hogging
the CPU, not PHP or Terminal:
When this happens, do a 'SHOW PROCESSLIST' in mysql to see what it's
doing.
I have, and I can't see anything unusual. There are a few scripts
that loop with very slow overhead (with sufficien
Hi Baron,
I know. But that would mean
http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html is in
error, as it says sql_warnings can be set in the option file.
Regards,
Martijn
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Baron S
Yep. File a bug report against the documentation; that table is a
work in progress. On my system,
[EMAIL PROTECTED] $ /usr/sbin/mysqld --verbose --help | grep warn
-W, --log-warnings[=#]
Log some not critical warnings to the log file.
-W, --warnings[=#] Deprecated; use
Hi. I'd like to set up something like apache's name-based virtual
hosting.
I read the docs for mysqlmanager. It told me how to set it up to run
multiple instances of mysql on one machine, where each instance had its
own port number and socket file.
I'd like to have my users connect to their own
That is not a mysqld option, it's a session variable:
mysql> select @@global.sql_warnings;
ERROR 1238 (HY000): Variable 'sql_warnings' is a SESSION variable
On Dec 20, 2007 9:14 AM, Martijn van den Burg
<[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm having issues with replication breaking on one machin
Hi,
I'm having issues with replication breaking on one machine (Linux, x64)
but continuing normal on the other (Solaris).
Both run MySQL 5.0.18-standard.
The only difference betweenn the configuration of teh two is the setting
of sql_notes and sql_warnings.
I want to find out if that causes the
Baron Schwartz wrote:
On Dec 20, 2007 7:16 AM, Sharon <[EMAIL PROTECTED]> wrote:
Baron Schwartz wrote:
Hi,
On Dec 20, 2007 2:15 AM, Sharon <[EMAIL PROTECTED]> wrote:
Hello all,
Given this table:
DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
CREATE TABLE `maprimary`.`tbl_locales_ip2l`
On Dec 20, 2007 7:16 AM, Sharon <[EMAIL PROTECTED]> wrote:
>
> Baron Schwartz wrote:
> > Hi,
> >
> > On Dec 20, 2007 2:15 AM, Sharon <[EMAIL PROTECTED]> wrote:
> >> Hello all,
> >> Given this table:
> >> DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
> >> CREATE TABLE `maprimary`.`tbl_locale
Baron Schwartz wrote:
Hi,
On Dec 20, 2007 2:15 AM, Sharon <[EMAIL PROTECTED]> wrote:
Hello all,
Given this table:
DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
CREATE TABLE `maprimary`.`tbl_locales_ip2l` (
`ipStart` int(10) unsigned zerofill NOT NULL default '00',
`ipEnd`
Hi,
On Dec 20, 2007 2:15 AM, Sharon <[EMAIL PROTECTED]> wrote:
> Hello all,
> Given this table:
> DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
> CREATE TABLE `maprimary`.`tbl_locales_ip2l` (
>`ipStart` int(10) unsigned zerofill NOT NULL default '00',
>`ipEnd` int(10) unsig
Moon's Father skrev:
Just a look at your sql query at first.
Your like key word's right must like this:
like 's%', then it'll use the index you created for your table.
The second try you may use temporary table to replace your own query like "
(SELECT uid FROM users WHERE username = %s)
"
Well
Hi.. I'm struggling with a query that I'm trying to simplify as much as
possible, but I can't seem to get rid of using the very same subqueries
several times.
Would there be a way to optimize the following so I get rid of
subqueries that do the exact same thing more than once?
SELECT (count(*
Hello all,
Given this table:
DROP TABLE IF EXISTS `maprimary`.`tbl_locales_ip2l`;
CREATE TABLE `maprimary`.`tbl_locales_ip2l` (
`ipStart` int(10) unsigned zerofill NOT NULL default '00',
`ipEnd` int(10) unsigned zerofill NOT NULL default '00',
`countryCode` varchar(2) defaul
40 matches
Mail list logo
