Select class_name,
GROUP_CONCAT(DISTINCT cl_date
ORDER BY cl_date DESC SEPARATOR ', ')
(select min(cl_date) from CLASS_DATES where item_id = c.item_id and
cl_date > Now())
From CLASSES c
Join CLASS_DATES cd on (c.item_id = cd.item_id)
Group by
I have a solution.
SELECT start_bit_boundary FROM (
SELECT
min(address) as start_bit_boundary,
status, count(*) as CT
FROM MAC_addresses
WHERE status = 0
GROUP BY address >> 2
) AS _INNER
WHERE
_INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;
It returns the first of 4 co
I should have said consecutive addresses, rather than contiguous.
I care about a set of consecutive addresses, and there is no guarantee
of record order.
On 12/17/2015 07:35 AM, John Stile wrote:
> I need help creating a select that returns 4 records that have
> contiguous addresses that start on
Lucio,
>> I have a table where each record is made of one item_number and one
>> data_value.
> You do not have any other column ? In particular you do not have any
> unique key record identifier ? All my tables have a column with a record
> sequence number "seq int NOT NULL AUTO_INCREMENT" whic
Thank you,
> SELECT * FROM test
> WHERE item_number in (SELECT item_number FROM test where data_value=1)
> AND item_number not in (SELECT item_number FROM test where data_value = 2);
That did it.
Olivier
> On Wed, April 29, 2015 07:20, Olivier Nicole wrote:
>>> SELECT * FROM table WHERE item_
On Wed, 29 Apr 2015, Olivier Nicole wrote:
I have a table where each record is made of one item_number and one
data_value.
You do not have any other column ? In particular you do not have any
unique key record identifier ? All my tables have a column with a record
sequence number "seq int NO
Right,
Take a look at this one then:
insert into test(item_number,data_value)
values(1,1),(1,2),(1,3)
,(2,1),(2,3)
,(3,1),(3,2),(3,3)
,(4,1),(4,3);
SELECT * FROM test
WHERE item_number in (SELECT item_number FROM test where data_value=1)
AND item_number not in (SELECT item_number FROM test where
> SELECT * FROM table WHERE item_number=1;
Sorry if my question was not clear: what I am looking for is:
SELECT * FROM table WHERE data_value=1 AND "there is not any reccord with
the same item_number and data_value=2"
Olivier
>
> On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
>> Hi,
>>
>>
SELECT * FROM table WHERE item_number=1;
On Wed, April 29, 2015 06:30, Olivier Nicole wrote:
> Hi,
>
> I am sure that it is feasible with MySQl, and I am sure that's a newbie
> question, but my SQL skills are limited...
>
> I have a table where each record is made of one item_number and one
> data
> You can do:
>
>
> SELECT last_name, first_name, phone, if(pub_email="Y",email,"") as email
> FROM `mydatabasetable` WHERE `current_member` = "Y" AND `pub_name` = "Y"
> ORDER BY last_name ASC
>
Gracias, Carlos. This worked fine!
---Fritz
--
MySQL General Mailing List
For list archiv
Try using a CASE construct in the select. Should work for this.
A
On Tue, Feb 5, 2013 at 3:25 PM, Stefan Kuhn wrote:
> You cannot do this. A sql result alwas has the same number of columns in
> each row. You could have null or "" in the column, though. This could be
> done via the if(,,)-state
You might want to have a look at partitioning that table. It'll be a
considerable downtime, but if done right it'll speed up a lot of your selects.
- Original Message -
> From: "Adarsh Sharma"
> To: "mysql"
> Sent: Tuesday, 15 November, 2011 11:3
More than 20163845 rows are there and my application continuously insert
data in the table.
daily i think there is a increase in 2.5 Gb in that table.
Thanks
Chris Tate-Davies wrote:
How many rows is that???
On Tue, 2011-11-15 at 16:05 +0530, Adarsh Sharma wrote:
Dear all,
I have a doub
2011/09/03 03:40 +0800, Dennis
But it seems that there is no "select ... into LOCAL file" statement. Any
suggestion is appreciated.
Indeed: you can use only standard output.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
Thanks, Tyler,Â
That's very helpful.
Dennis
--- On Sat, 9/3/11, Tyler Poland wrote:
From: Tyler Poland
Subject: Re: select ... into local outfile ... ???
To: mysql@lists.mysql.com
Date: Saturday, September 3, 2011, 3:45 AM
Dennis,
The following closely simulates the default "IN
Dennis,
The following closely simulates the default "INTO OUTFILE", you'll have
to modify it slightly for your purposes:
mysql --delimiter=comma -N < select_statement.sql \
| sed 's/\"/\\\"/g' \
| sed 's/\t/\",\"/g' \
| sed 's/$/\"/g' \
| sed 's/^/\"/g' \
| sed 's/\"NULL\"/\\N/g' \
>-Original Message-
>From: sono...@fannullone.us [mailto:sono...@fannullone.us]
>Sent: Monday, June 20, 2011 6:01 PM
>To: mysql@lists.mysql.com
>Cc: wha...@bfs.de; Jerry Schwartz
>Subject: Re: SELECT records less than 15 minutes old
>
>On Jun 20, 2011, at 10:11
from:http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
SET GLOBAL time_zone = timezone;
from:http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_utc-timestamp
* UTC_TIMESTAMP, UTC_TIMESTAMP()
I have the same results, according to the docs timezone is the offs
On Jun 20, 2011, at 10:11 AM, Jerry Schwartz wrote:
>> You should use UTC time zone or you will run into trouble with DST.
>>
> [JS] If you do that, you can't use an automatic timestamp field. You have to
> set the field yourself.
Thanks Walter and Jerry.
Is there a way to get
>-Original Message-
>From: walter harms [mailto:wha...@bfs.de]
>Sent: Monday, June 20, 2011 7:07 AM
>To: sono...@fannullone.us
>Cc: mysql@lists.mysql.com
>Subject: Re: SELECT records less than 15 minutes old
>
>
>
>Am 19.06.2011 21:06, schrieb sono...@fannull
Am 19.06.2011 21:06, schrieb sono...@fannullone.us:
> On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:
>
>> just a quick debug:
>
> Thanks, Claudio. It turned out to be that NOW() was using the server's
> time and my timestamp was based on my timezone. After fixing that, the
> SELEC
On Jun 19, 2011, at 11:11 AM, Claudio Nanni wrote:
> just a quick debug:
Thanks, Claudio. It turned out to be that NOW() was using the server's
time and my timestamp was based on my timezone. After fixing that, the SELECT
statement works properly.
Marc
--
MySQL General Mailing List
F
just a quick debug:
SELECT time_stamp,DATE_SUB(NOW(), INTERVAL 15 MINUTE) FROM `records` WHERE
`order_id` = $order_id order by time_stamp desc limit 10;
what do you get?
2011/6/19
> Hi,
>
>I'm trying to write a statement that will return all records that
> match a particular order_id
Am 31.03.2011 17:53, schrieb S?ndor Hal?sz:
> 2011/03/23 16:29 -0700, Steffan A. Cline
> So, this is what I have as a basis to work on assuming I am polling
> article #36.
>
> Select *, if(b.articleid=36,1,0) as checked from groups g
> Left join agmap a on g.groupid=a.groupid
> Left joi
2011/03/23 16:29 -0700, Steffan A. Cline
So, this is what I have as a basis to work on assuming I am polling
article #36.
Select *, if(b.articleid=36,1,0) as checked from groups g
Left join agmap a on g.groupid=a.groupid
Left join articles b on a.articleid=b.articleid
Order by g.groupna
Hi Travis,
That query kind of gives me the desired result. However, if is showing
me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6,
2010-12-20 22:17:13, which is when they changed teams. Any thoughts ?
Cheers
Neil
On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard wrote:
> So
Something like this might help you find all of the times where your user_id
switched to a different team_id:
select team_id, user_id, min(last_changed)
from
(select home_team_id as team_id, home_user_id as user_id, last_changed
from data
union all
select away_team_id as team_id, away_user_id as us
--- Original Message -
From: "Aveek Misra"
To: "HaidarPesebe"
Cc: "MySQL Lists"
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re: select data from two tables and SUM qty of the same ID
SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY)
n 'product.product_id' in 'field list'
Note: product_id (product) = bid (sales_report)
haidarpesebe
- Original Message -
From: "Aveek Misra"
To: "HaidarPesebe"
Cc: "MySQL Lists"
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re:
SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) as
Total_Quantity FROM salesreport, product WHERE product.ID = salesreport.ID
GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;
On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:
> please help us look for the data se
Thanks a lot. We will try and inform you.
--Original Message--
From: Aveek Misra
To: Albahar Uya
Cc: MySQL Lists
Subject: Re: select data from two tables and SUM qty of the same ID
Sent: Nov 10, 2010 17:38
SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY) as
At 10:49 AM 9/29/2010, Steve Staples wrote:
Google has not been kind to me on this one, so I figured I would ask
here...
how can I select with NICE options, so that it doesn't KILL my server,
or any other queries...
Do you understand what I am asking?
Steve
Steve,
You might look at http://
>-Original Message-
>From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>Sent: Tuesday, September 21, 2010 11:48 AM
>To: Johan De Meersman
>Cc: [MySQL]
>Subject: Re: SELECT WHERE IN help
>
>Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data
Hi Neil, all!
Tompkins Neil wrote:
> Hi
>
> With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
> return two records for the record_id 3 ? Is it possible ?
This is a case where you may safely use natural language and logic. The
command is
SELECT all fields FROM the rec
On 21/09/2010 16:44, Tompkins Neil wrote:
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
No, that isn't
Thanks for the reply. The search of (3,4,5,6,7,3) is pulling data from a
table. I think in this case I need to change my design .
On Tue, Sep 21, 2010 at 4:46 PM, Johan De Meersman wrote:
> I don't think that'll work, no. Why would you want to return duplicate data
> ? The whole point of an R
I don't think that'll work, no. Why would you want to return duplicate data
? The whole point of an RDBMS is to *avoid* duplicate data :-)
On Tue, Sep 21, 2010 at 5:44 PM, Tompkins Neil wrote:
> Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
> only exists once in the
Thanks for the quick reply. Basically in (3,4,5,6,7,3) the record_id of 3
only exists once in the table my_table. However, because 3 exists twice
within (3,4,5,6,7,3), I want it to return two records for record_id 3. Is
it possible ?
Cheers
Neil
On Tue, Sep 21, 2010 at 4:40 PM, Johan De Meers
If there are two, you will return two.
On Tue, Sep 21, 2010 at 5:33 PM, Tompkins Neil wrote:
> Hi
>
> With a SELECT * FROM my_table WHERE record_id IN (3,4,5,6,7,3), how can I
> return two records for the record_id 3 ? Is it possible ?
>
> Cheers
> Neil
>
--
Bier met grenadyn
Is als mosterd
On 7/19/2010 5:22 PM, Bryan Cantwell wrote:
I have a stored procedure in mysql 5.1.48 that deletes old data from my
tables.
I would like to keep a running count while it does this. Here is what I
try now:
...
DECLARE dropCnt INT DEFAULT 0;
...
SET @sql = CONCAT('DELETE FROM myTable WHERE itemid
Peter
Thanks for the link. I've never run across this page before, but it has
tons of useful informationas well as several answers on how to implement
what I was trying to do.
Ended up going with a solution similar to this example (from the page you
referenced):
SELECT
item,
SUBSTR( MIN
> Date: Wed, 14 Jul 2010 10:25:22 -0400
> Subject: Select w/ group by question
> From: smulle...@gmail.com
> To: mysql@lists.mysql.com
>
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote:
> I'm having trouble formulating a query to gather the following data. I can
> do this via a script, but now it is more or less just bothering me if there
> is an easy/efficient way to gather the following data from a single query.
>
> E
Scott,
I would like to obtain the least cost of each product type and its
associated vendor.
See "Within-group aggregates" at
http://www.artfulsoftware.com/infotree/queries.php.
PB
-
On 7/14/2010 9:25 AM, Scott Mullen wrote:
I'm having trouble formulating a query to gather the follo
Kyong Kim wrote:
I needed to give greater detail.
parent_id isn't unique. The table has a composite primary key (parent_id,
seq_id).
Here's a better schema def
CREATE TABLE sometable (
parent_id INT(10) NOT NULL,
seq_id INT(10) AUTO_INCREMENT,
child_id INT(10) NULL,
PRIMARY KEY(parent_id, seq_i
UPDATE, I think this will solve
> the problem with one statement.
>
> Rodrigo Ferreira
>
> --- On *Wed, 3/24/10, Johnny Withers * wrote:
>
>
> From: Johnny Withers
> Subject: Re: SELECT and INSERT if no row is returned
> To: "Kyong Kim"
> Cc: "my
First, if you want no duplicate parent_id, make it unique key (as JW saids).
Look at INSERT ... ON DUPLICATE KEY UPDATE, I think this will solve the problem
with one statement.
Rodrigo Ferreira
--- On Wed, 3/24/10, Johnny Withers wrote:
From: Johnny Withers
Subject: Re: SELECT and INSERT if
Make parent_id a unique key. Doing a select first and inserting if no
result will work 99.9% of the time; however, consider 2 rows being
inserted at the same time.
JW
On Tuesday, March 23, 2010, Kyong Kim wrote:
> I need to INSERT a row into a table only if it does not exist to
> insure that the
> At 08:59 PM 2/27/2010, you wrote:
>>Hello everyone,
>>
>>How would I select a random row that changes daily?
>>
>>Thanks
>
> The common way would be to do:
>
> select * from table order by rand() limit 1;
>
> You can of course add a Where clause to select only those rows that were
> added today.
At 08:59 PM 2/27/2010, you wrote:
Hello everyone,
How would I select a random row that changes daily?
Thanks
The common way would be to do:
select * from table order by rand() limit 1;
You can of course add a Where clause to select only those rows that were
added today.
select * from tab
...I am using PHP 5.2
> Hello everyone,
>
> How would I select a random row that changes daily?
>
> Thanks
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=ja...@jasoncarson.ca
>
>
--
MySQL General
Steve:
> I suppose maybe making this a slave table
> to the other
> server... nah... lots of work there
Setting your local server to be a slave of the
remote server is not too hard and would
be a MUCH better solution.
The steps are fairly staightforward:
1. Add a slave user to the remote datab
it before, but I guess my searching keywords were
insufficient ;)
Steven Staples
-Original Message-
From: harrison.f...@sun.com [mailto:harrison.f...@sun.com]
Sent: December 9, 2009 2:07 PM
To: Johan De Meersman
Cc: Neil Aggarwal; Steven Staples; mysql@lists.mysql.com
Subject: Re: S
Hello Johan,
On Dec 9, 2009, at 11:22 AM, Johan De Meersman wrote:
Posted this before, but beware: federated tables do NOT use indices.
Every
select is a full table scan, and if you're talking about a logging
table
that could become very expensive very fast.
This is not entirely true. If
Posted this before, but beware: federated tables do NOT use indices. Every
select is a full table scan, and if you're talking about a logging table
that could become very expensive very fast.
On Wed, Dec 9, 2009 at 4:13 PM, Neil Aggarwal wrote:
> > Is this possible to do? To make a connection, i
> Is this possible to do? To make a connection, inside the
> stored procedure
> to a completely different machine and access the mysql there?
The only way I know to access tables from different servers
from a single connection is federated tables:
http://dev.mysql.com/doc/refman/5.0/en/federated
On Sun, Dec 6, 2009 at 2:42 PM, Steve Edberg wrote:
> At 1:26 PM -0500 12/6/09, Victor Subervi wrote:
>
>> Hi;
>> I have the following:
>>
>> mysql> select * from categoriesProducts as c inner join
>> relationshipProducts
>> as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent =
>
At 1:26 PM -0500 12/6/09, Victor Subervi wrote:
Hi;
I have the following:
mysql> select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = prodCat2;
ERROR 1054 (42S22): Unknown column 'pro
I find a solution that works:
SELECT writer_id, writer FROM writer WHERE writer REGEXP '^[A-Z]+' ORDER
BY writer
Thank you all
Nikos
misiaQ wrote:
> Hi,
>
> If the efficiency is the key factor I would suggest to create a trigger on
> insert and update and mark the rows in a separate column inste
Hi,
If the efficiency is the key factor I would suggest to create a trigger on
insert and update and mark the rows in a separate column instead of
executing some fancy string checks during select.
Regards,
m.
-Original Message-
From: nikos [mailto:ni...@qbit.gr]
Sent: 19 November 2009 0
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
SELECT * FROM `table_anw` WHERE WEEKDAY(datum) = 2;
SELECT * FROM `table_anw` WHERE DAYNAME(datum) = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%W') = 'Tuesday';
SELECT * FROM `table_anw` WHERE DATE_FORMAT(datum,'%a') = 'Tue'
Try this
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
Scott Swaim
I.T. Director
Total Care / Joshua Family Medical Care
(817) 297-4455
Website: www.totalcareclinic.com
NOTICE: The information contained in this e-mail is privileged and
confidential and is intended for the exclusive u
Have you tried this?
SELECT * FROM `table_anw` WHERE DAYOFWEEK(datum) = 3;
"Kerstin Finke" escreveu na mensagem
news:20090929130406.9802.qm...@lists.mysql.com...
> Hi,
> I want to search all rows with datum = 'tuesday' for example, something
> like:
>
> SELECT * FROM `table_anw` WHERE datum=DA
Is there anyway the SELECT query can be forced to use the "from" and "to"
rownum parameters?
1st LIMIT arg = OracleFromArg
2nd LIMIT arg = OracleToArg - OracleFromArg + 1
so 'from 11 to 20' becomes LIMIT 11,10.
PB
-
Anoop kumar V wrote:
Hi All,
I am facing a problem in porting an appli
ID FROM Table2 WHERE Source2_Name = 'name'
UNION
SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name'
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re:
¢ndido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, July 27, 2009 1:09 PM
To: mysql@lists.mysql.com
Subject: Re: SELECT query question
select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table
select
*
from
MainTable MT
left join Table1 T1 on MT.Main_ID = T1.MainID
left join Table2 T2 on MT.Main_ID = T2.MainID
left join Table3 T3 on MT.Main_ID = T3.MainID
where
T1.Source1_Name = "anything" or
T2.Source2_Name = "anything" or
T3.Source3_Name = "anything"
Not tested.
-
Thanks all who replied.
After I posted I kept looking and found it... Also had folks point it
out to me.
Your suggestion is what I ended up doing.
Bruce
Gavin Towey wrote:
> Hi Bruce,
>
>
>
> SELECT … INTO OUTFILE always creates the file local to the database
> server. If you want to dum
Hi Bruce,
SELECT ... INTO OUTFILE always creates the file local to the database server.
If you want to dump results where your perl script is running you'll have to
use another method such as receiving the results of the query normally and
writing the file in the perl script.
Regards,
Ga
If I understand your question correctly (and I'm really not sure about
that), you're using a LEFT JOIN where you actually want to use a RIGHT
JOIN.
Or you need to rewrite your query and set the tables in another order.
On Thu, Apr 30, 2009 at 23:01, Nigel Peck wrote:
>
> Can someone please help m
> From: haidarpes...@gmail.com
> To: mysql@lists.mysql.com
> Subject: select data from two table and will sort by price in second table
> Date: Wed, 29 Apr 2009 10:46:48 +0700
>
> dear all,
> please help us mien for select data from two table with details as follows:
>
> primery tabel : bookcat
problem disappeared and the
selects behave as one would expect.
Many thanks to all who offered advice.
Carl
- Original Message -
From: "Perrin Harkins"
To: "Carl"
Cc:
Sent: Friday, March 13, 2009 1:40 PM
Subject: Re: Select query locks tables in Innodb
2009/3/
AFAIK, repeated LIKEs.
On Tue, Mar 24, 2009 at 6:24 AM, Yariv Omer wrote:
> Hi
>
> when I am using a query for several field's values I am using the following
> query:
> Select field from table where in ('11', '22')
>
> I need to do a LIKE search (not exact match but like match)
>
> How can I do
2009/3/12 Carl :
> I am still a little puzzled about how we could have a relatively large set
> of records (100,000+) and yet not cause any table to be locked as the server
> has only 8GB of memory.
What's the relationship you're implying between memory and locking?
Multi-version concurrency doesn
's suggestions also.
>
> Carl
>
>
>
>
>
> - Original Message - From: "Brent Baisley"
> To: "Carl"
> Sent: Thursday, March 05, 2009 1:12 PM
> Subject: Re: Select query locks tables in Innodb
>
>
> Ok, so you have 687 unique or
elp and Baron's suggestions also.
Carl
- Original Message -
From: "Brent Baisley"
To: "Carl"
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
ver
on Schwartz"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STAT
gt; Since the report query is only reading data, I am puzzled why it locks the
> tables. Any ideas?
>
> TIA,
>
> Carl
>
>
> - Original Message - From: "Baron Schwartz"
> To: "Carl"
> Cc:
> Sent: Wednesday, March 04, 2009 2:29 PM
> Subject: Re:
--
From: "Baron Schwartz"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of wh
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of wh
the isolation level but I believe it is whatever was set out of
the box (five years ago.)
Thanks,
Carl
- Original Message -
From: "Perrin Harkins"
To: "Carl"
Cc:
Sent: Wednesday, March 04, 2009 1:49 PM
Subject: Re: Select query locks tables in Innodb
2009
report query. It is a foreign key on one of the files that is used.
TIA,
Carl
- Original Message -
From: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue,
2009/3/4 Carl :
> However, when I had all the pieces in the query
> (copy attached), I could easily see it was locking tables using the Server
> Monitor in Navicat.
I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.
> Explain (copy a
Message -
From: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley
wrote:
A SELECT will/can lock a table. It almost al
om: "Baron Schwartz"
To: "Brent Baisley"
Cc: "Carl" ;
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
inse
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley wrote:
> A SELECT will/can lock a table. It almost always does in MyISAM (no
> insert/updates), almost never does in InnoDB. There is an exception to
> every rule. The problem is most likely in the 107488 rows part of the
> query. That's too many rows
On Tue, Mar 3, 2009 at 10:53 AM, Carl wrote:
> A query that is selecting data for a report locks the files that it accesses
> forcing users who are attempting to enter transactions to wait until the
> select query is finished.
Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you
ver
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just lo
Hello,
On Thu, Feb 12, 2009 at 11:54 PM, Kiran Waje wrote:
> I have two Mysql servers and I want to Read data from one Mysql server to
> another using stored procedure.
>
You may be able to use the Federated engine. Check the output of SHOW ENGINES.
--
Baron Schwartz, Director of Consulting,
thanks, it works fine (on mysql 4 and 5)
> SELECT CONCAT(LEFT(myfield,LENGTH(myfield) - 3), '***') FROM table;
--
Scripts: http://www.spacemarc.it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>-Original Message-
>From: spacemarc [mailto:[EMAIL PROTECTED]
>Sent: Friday, December 05, 2008 8:50 AM
>To: MySQL
>Subject: SELECT with replacement chars
>
>Hi
>how can i replace, in a SELECT query, the last 3 numbers with asterisks?
>from 0123456789 to 0123456***
>
[JS]
SELECT CONCAT(LE
>Hi
>how can i replace, in a SELECT query, the last 3 numbers with
asterisks?
>from 0123456789 to 0123456***
My psudocode for this would be something like:
Select
CONCAT(left$(`field`,(LENGTH(a)-3),'***')
>From `table`
But there might be more efficient ideas out there.
--
Thanks for the improved query.The indexing didn't help much and still the
main problem is it locking all updates to the tables while it executes...
even if I am executing it on a copy of the tables in a different database
--
Dave
2008/11/27 Chandru <[EMAIL PROTECTED]>
> Hi David,
> please create
Hi David,
can you please let me know what is the select query and the update query
along with the explain plan of the same.
can you please let me know if you are using innodb storage engine?
Regards,
Chandru
www.mafiree.com
On Thu, Nov 27, 2008 at 4:45 PM, David Scott
<[EMAIL PROTECTED]>wrot
show full processlist
userX is the user the site is using to connect
databaseX is the database in question
1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
1377562
can u please do "show full processlist" when the update is happening, and
if its innodb
please do "SHOW INNODB STATUS", which will give complete activity on innodb
engine, including lock information.
Please show use the output of these.
regards
anandkl
On 11/27/08, David Scott <[EMAIL PROTEC
008 14:21:39 -0500
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Subject: Re: SELECT through many databases
> CC: mysql@lists.mysql.com
>
> A MERGE table is just a virtual table that is made up of other tables.
> You treat it no differently than any other table, pre
--
From: Andre Matos [mailto:[EMAIL PROTECTED]
Sent: Friday, November 21, 2008 2:11 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: SELECT through many databases
I was trying to avoid both since the SELECT statement is not fixed.
Time to time, users want different information.
Th
I would define a view out of the union query, you will only have to
change that view whenever you add a database to your instance.
Seems a bit strange that you could not manage this scenario, do you have
so many databases added?
If you really do not want to put your hands on code any more the onl
1 - 100 of 1450 matches
Mail list logo
