Re: ERROR 2013: Lost connection to MySQL server during query

[Michael Gale]

Hello,
	
	Thanks to all who applied but I was able to get access to the log file 
today. The server was shutting down and it seemed to be a performance 
problem.

I increased the RAM in the server and adjusted the Innodb settings to 
take advantage of this.

Now everything is much better.
Michael
Daniel Kasak wrote:
Michael Gale wrote:

Hello,
Earlier today I tried to make some table alterations on a mysql server
(4.0.24) and kept on receiving this error:
ERROR 2013: Lost connection to MySQL server during query
I would login to the server, either through the socket or using the
IP. Then connect to a database and try the following:
CREATE INDEX hostdate ON syslog (machine,date1);
There are about 3 million rows .. about a minute or two into the
command I would get disconnected. Sometimes I could log back but not
others, I tried seaching google and the only answer I found was "This
is fix the in the next release of 5.X.X".
This does not help as I am stuck with 4.0.X right now.
How can I fix this ?
Michael
Is the server crashing? What does your error log say? If you're using
4.0.x, then my first hunch would be table corruption. Have you checked
for that yet?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Mygrating from 3.23 to 4.1

[=?ISO-8859-1?Q?M=E1rio_Gamito?=]

Hi,

For years i've been using MySQL 3.23, but now that i'm about to
reinstall my company's web server, i've decided that it's time to go to 4.1

I've read quite a few things in the web about migrating the databases,
*including the mysql one*, but each article i read, pointed me in a
different direction.

Tryed to do the migration at my home computer, but... no good.

What is indeed the right way to do this *full* databases migration from
3.3.28 to 4.1.11 ?

Any help would be apreciated.

Warm Regards,

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
You don't understand me, I refer that if in a table I use productos.prod_id
and in other table indexes.id if I can use this two fields like the same
index, because when I named the two equal, the index start to work fine.
There should be no problem with joining two tables based on columns with 
different names. "productos.prod_id=indexes.id" should work. Both 
columns could be indexed, (in two separate indexes, of course, as they 
are in two separate tables), but only one index will be used, depending 
on the join order. It does not matter if you write 
"productos.prod_id=indexes.id" or "indexes.id=productos.prod_id", and it 
does not matter if you write "FROM productos,indexes" or "FROM 
indexes,productos" (unless STRAIGHT_JOIN is used).

In this case (se earlier posts in this thread) the table named indexes 
should be read first, then productos. That means an index on 
productos.prod_id will be used, if available. The name of the column in 
the productos table or the name of the related column in the indexes 
table does not matter. The "=" character in the ON clause or in the 
WHERE clause dictates which columns are related, not the name of the 
columns.

I don't know why your index did not work at first.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Writes blocking Reads

[Andy McCurdy]

I forgot to mention:  we're running mysql version 4.0.23-standard-log --
Official MySQL-standard binary.  Here's the innodb status output during a
problematic period.

=
050421 15:29:46 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 26 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1330878, signal count 1241079
Mutex spin waits 16157526, rounds 105045131, OS waits 692467
RW-shared spins 691802, OS waits 328867; RW-excl spins 91394, OS waits 34657

LATEST DETECTED DEADLOCK

050418 14:46:01
*** (1) TRANSACTION:
TRANSACTION 0 52471953, ACTIVE 0 sec, process no 5468, OS thread id
2625171473 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 7832890, query id 51121416 host1 10.15.0.76 username 
Updating
UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of
table `gne/pm_message` trx id 0 52471953 lock_mode X waiting
Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs
FALSE; info bits 32
0: len 4; hex 002234fb; asc  "4 ;; 1: len 6; hex 0320a88f; asc
  ;; 2: len 7; hex 03801315f4; asc;; 3: len 30; hex
3139343432333
33535353937373535313334383739393833323236393236; asc
194423355597755134879983226926;...(truncated); 4: len 30; hex
3230333739383339383930343339303733
30363132343136363636363637; asc
20379839890439073061241667;...(truncated); 5: len 17; hex
417474656e74696f6e204d656d62657273; asc Attention Membe
rs;; 6: len 30; hex
492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I
want you to start posting he;...(truncated); 7: len 13; hex 3
231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc
;; 9: len 1; hex 81; asc  ;; 10: len 8; hex 8000123c5a0d1524; asc
 wrote in message 
news:[EMAIL PROTECTED]
> I've been seeing some weirdness w/ MySQL and InnoDB over the past few 
> days. I have a database that's entirely using InnoDB.  The database is 
> roughly 4.5G (one datafile) and contains a little over 50 tables.  The web 
> applications that hit this database do about 85-90% reads and 10-15% 
> writes. Whenever a write comes in, it seems that subsequent reads get 
> blocked to the _ENTIRE DATABASE_.  What really confuses me is that reads 
> even to other tables that aren't any way involved with the table that's 
> being written to are waiting until the write completes.  I do not use any 
> foreign key relationships which could block a parent row.
>
> Whenever I run "show processlist", everything looks fine until a write 
> comes in.  Reads are answered < 1 sec, and generally don't even appear on 
> the processlist.  However, as soon as a write hits, all of the reads are 
> in a state of "NULL" until the write completes.  This problem is 
> compounded when several different writes happen from different connections 
> at the same time. It leads to the MySQL server blocking long enough that 
> the number of reads in the queue exceed the number of max connections. 
> Once the writes happen, all of the read queries in the queue get answered 
> and the normal processing continues.
>
> I've included two samples from the "show processlist" command.  They were 
> taken approx. 1 second apart and show the growing amount of reads when 
> writes are in the queue.  I've removed the User and Host columns as they 
> don't seem to be relevant as well as trimmed the state column down.  I've 
> also included the my.cnf config file and an output of "show status".
>
> Additionally, I'm running Redhat 7.3 - kernel ver. 2.4.20-28.7bigmem #1 
> SMP
>
> Any help on eliminating this bottleneck would be greatly appreciated!
> Thanks.
> -andy
>
>
> =
> process list 1
> =
>
>
> +--+--+-++-+
> | Id   | db   | Command | Time   | State   | Info
> +--+--+-++-+
> |  2972920 | NULL | Binlog Dump | 463909 | Has sen | NULL
> |  2972930 | NULL | Binlog Dump | 463909 | Has sen | NULL
> | 10954259 | NULL | Query   | 0  | NULL| show processlist
> | 10962986 | gne  | Sleep   | 4  | | NULL
> | 10962987 | gne  | Sleep   | 4  | | NULL
> | 10962997 | gne  | Query   | 3  | NULL| SELECT r.*, 
> ui.username FROM review
> | 10963003 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
> WHERE id='142'
> | 10963018 | gne  | Query   | 3  | NULL| SELECT * FROM clan 
> WHERE id='274'
> | 10963019 | gne  | Query   | 3  | update  | INSERT INTO rating 
> (ref_type_id, re
> | 10963020 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
> WHERE id='55

4.0 -> 4.1 update killed my db!

[m i l e s]

Hi,
I updated my 4.0 install to 4.1 and now NONE of my
databases and tables show up at all.
Im on OS X.3.9.
All the files are there in the data folder, permissions are
set, what did I do wrong ?  Any suggestions as to how to get it
back ?
M i l e s.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Index problem ?

[Keith Ivey]

Michael Gale wrote:
When I run the following:
`Select DISTINCT machine from syslog WHERE date1 > (NOW() - INTERVAL 1
hour);' it takes 9min to complete. 

If I use "Explain" it says the query is using index "hostname", should
it not be using the index "hostdate" which contains fields "machine" and
"date1" since those are the fields I am using in my query ?
Your WHERE clause needs an index on date1, which means an index 
that *starts* with date1.  Your index on (machine, date1) won't 
help in this case.  You could use an index on just date1 or on 
(date1, machine).  The second would allow MySQL to do that query 
from the index alone, without referring to the data file, so it 
should be much faster than what you're doing now.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

little string patch job

[Scott Haneda]

I have a field that has been given to me as all uppercase, they are City
names, some are more than one word.

I need to run a update to:
replace "  " with " "
then lowercase them all, then uppercase the first letter and any first
letter after a space.
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Index problem ?

[Michael Gale]

Hello,

Currently I am using sysklogd-sql to store syslog messages in a MySQL
4.0.24. The current number of rows is around 3,799,700.

The table layout is as follows:

ID
machine
facility
priority
date1
message

Now ID is the primary key and it had one index called hostname
containing (machine).

I do a lot of queries based off information on the machine name and
date1 field. So I created the following index:

`CREATE INDEX hostdate ON syslog (machine,date1);`

This index has a "Cardinality" of 1,899,353 - this number is about half
of the total number of entries.

When I run the following:
`Select DISTINCT machine from syslog WHERE date1 > (NOW() - INTERVAL 1
hour);' it takes 9min to complete. 

If I use "Explain" it says the query is using index "hostname", should
it not be using the index "hostdate" which contains fields "machine" and
"date1" since those are the fields I am using in my query ?

Thanks.

Michael



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unique items from all columns, very slow

[Willie Gnarlson]

On 4/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/21/2005 01:39:15
> PM:
> > On 4/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005
> > 05:46:25  PM:
> > >  > On 4/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > >  > > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005
> > > 01:56:03 PM:
> > >  > >  > On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> wrote:
> > >  > >  > > In the last episode (Apr 20), Willie Gnarlson said:
> > >  > >  > > > I have been attempting to uncover a fast method for
> retrieving
> > > unique
> > >  > >  > > > items from every column in a table. The table has
> approximately
> > > 60
> > >  > >  > > > thousand rows. Each row has an index associated with it,
> and
> > > running
> > >  > >  > > > 'explain select distinct class from dlist' shows that it
> isusing
> > > the
> > >  > >  > > > index.
> > >  > >  > > >
> > >  > >  > > > However, since there are 24 columns, running that select
> query
> > > for
> > >  > >  > > > each column takes about 4 long seconds on a P4 2.4 Ghz.
> > >  > >  > >
> > >  
> > >  > Okay, they aren't creating new values using that form. The form
> I've
> > >  > been talking about is a form to limit results from the database to
> > >  > only specific values for  specific fields without the user having
> to
> > >  > know from memory what the possible values are (plus, they'll change
> > >  > every so often).
> > >  >
> >
> > (...)
> >
> > >
> > > If I read that last part correctly, you have already identified the
> need for
> > > a mechanism for managing the value lists separately from what the user
> > > enters. In this case you should have everything in place (or almost in
> > > place) to put each list into its own table (Someone else suggested
> this
> > > design, too).
> >
> > Right, yes.
> >
> > (...)
> > > Run your queries once to build your lists then use another form (or
> even use
> > > a separate application) to manage the lists. Because it's a one-time
> event,
> > > getting the first set of unique values can take all night if it needs
> to.
> > > What you need to be fast is the building of the select boxes. By
> having
> > > those lists ready-to-go on their own tables, it will be as fast as a
> > > sequential read from the disk (very fast) or a read from the disk
> cache
> > > (faster) or a read from the query cache (fastest). In any case,
> running the
> > > queries will no longer slow you down.
> >
> > It actually seems slower. The separate tables from a previous try look
> > like this:
> >
> > CREATE TABLE `ET` (
> >   `ET` double NOT NULL default '0',
> >   PRIMARY KEY  (`ET`),
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> >
> > That table has 4781 records, but some have more (one has ~18
> > thousand). There are 42 "column" tables.
> >
> > Querying all of them took 5.58 seconds.
> >
> > --
> > Willie Gnarlson
> 
> I am SO confused!!! (and that's hard to do) You have a lookup list with
> eighteen thousand entries in it? Your users must pick 1 of 4781 floating
> point numbers from a select box? What kind of information are you trying
> to work with? I cannot imagine an application that uses data like you are
> describing. Can you help a poor fellow out and let us in on what you are
> working on?

LOL! 

It sounds a little insane, I'll agree. Actually that table example I
provided holds Elapsed Times. Many, many entries can contain the same
ET.

> Here's a question, it may have taken 5.58 seconds for the first pass
> through all 42 tables but how fast was the second pass? Was that 5.58
> measuring just the queries or the time it took to build the select boxes,

No, I singled out the code up until the end of the queries, and only
that is 5.58 seconds. Yipes, the CPU isn't under any real load now,
previously it was. Sorry. It's still too slow I feel.

I thought the caching might have helped, but apparently:

1 pass:  3.35 seconds
2 pass:  3.64 seconds (!?)
3 pass:  3.36 seconds

> too? As far as performance goes, It may be faster to check the user's
> entries during the form validation/processing phase than it will be to
> force them to enter the information the correct way the first time.

I was trying to set it up a series of select  boxes for limiting a
search. I may have to re-think this if I can't get the data from the
tables fast enough.

-- 
Willie Gnarlson

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Writes blocking Reads

[kernel]

Andy McCurdy wrote:
I've been seeing some weirdness w/ MySQL and InnoDB over the past few days. 
I have a database that's entirely using InnoDB.  The database is roughly 
4.5G (one datafile) and contains a little over 50 tables.  The web 
applications that hit this database do about 85-90% reads and 10-15% writes. 
Whenever a write comes in, it seems that subsequent reads get blocked to the 
_ENTIRE DATABASE_.  What really confuses me is that reads even to other 
tables that aren't any way involved with the table that's being written to 
are waiting until the write completes.  I do not use any foreign key 
relationships which could block a parent row.

Whenever I run "show processlist", everything looks fine until a write comes 
in.  Reads are answered < 1 sec, and generally don't even appear on the 
processlist.  However, as soon as a write hits, all of the reads are in a 
state of "NULL" until the write completes.  This problem is compounded when 
several different writes happen from different connections at the same time. 
It leads to the MySQL server blocking long enough that the number of reads 
in the queue exceed the number of max connections.  Once the writes happen, 
all of the read queries in the queue get answered and the normal processing 
continues.

I've included two samples from the "show processlist" command.  They were 
taken approx. 1 second apart and show the growing amount of reads when 
writes are in the queue.  I've removed the User and Host columns as they 
don't seem to be relevant as well as trimmed the state column down.  I've 
also included the my.cnf config file and an output of "show status".

Additionally, I'm running Redhat 7.3 - kernel ver. 2.4.20-28.7bigmem #1 SMP
Any help on eliminating this bottleneck would be greatly appreciated!
Thanks.
-andy
=
process list 1
=
+--+--+-++-+
| Id   | db   | Command | Time   | State   | Info
+--+--+-++-+
|  2972920 | NULL | Binlog Dump | 463909 | Has sen | NULL
|  2972930 | NULL | Binlog Dump | 463909 | Has sen | NULL
| 10954259 | NULL | Query   | 0  | NULL| show processlist
| 10962986 | gne  | Sleep   | 4  | | NULL
| 10962987 | gne  | Sleep   | 4  | | NULL
| 10962997 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963003 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='142'
| 10963018 | gne  | Query   | 3  | NULL| SELECT * FROM clan 
WHERE id='274'
| 10963019 | gne  | Query   | 3  | update  | INSERT INTO rating 
(ref_type_id, re
| 10963020 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='55'
| 10963021 | gne  | Query   | 3  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963022 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963023 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963024 | gne  | Query   | 2  | NULL| SELECT count(*) AS 
count FROM pm_me
| 10963025 | gne  | Query   | 2  | NULL| SELECT * FROM contact 
WHERE user_id
| 10963026 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='729'
| 10963027 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963028 | gne  | Query   | 2  | NULL| select r.review_id from 
review r wh
| 10963029 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963030 | gne  | Query   | 2  | update  | INSERT INTO pm_message 
SET id=NULL,
| 10963031 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963032 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963033 | gne  | Query   | 2  | NULL| select r.review_id from 
review r wh
| 10963034 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963035 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963036 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963037 | gne  | Query   | 2  | NULL| SELECT c.* FROM 
clan_pid cp, clan c
| 10963038 | gne  | Query   | 2  | NULL| SELECT * FROM 
pm_message WHERE id =
| 10963039 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963041 | gne  | Query   | 2  | NULL| SELECT c.* FROM 
clan_pid cp, clan c
| 10963042 | gne  | Query   | 1  | NULL| select r.review_id from 
review r wh
| 10963043 | gne  | Query   | 1  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963045 | gne  | Query   | 1  | NULL| SELECT count(*) AS 
count FROM pm_me
| 10963046 | gne  | Query   | 1  | NULL| SELE

Re: Newbie trying the impossible?

[Frank Bax]

At 03:20 PM 4/21/05, Scott Hamm wrote:
However when I used left join (trying to learn it) I issued this command:
SELECT
QA.OperatorID,
QA.QAID,
QA.BrandID,
QA.Batch,
QA.KeyDate,
Batch.[Order],
Batch.Errors,
Batch.Comments
FROM
QA
Left Join
Batch
ON
(Batch.QAID=QA.ID)
WHERE
ID='77363';
How do I get around to it with 2 different names that uses SAME table?
QA.OperatorID (operator)
QA.QAID (reviewer)
Or am I asking for the impossible?

It's not impossible, but I don't think you've provide enough info to 
provide an example with your data.  The left join is not the problem.  What 
you need is to join the same table twice - to do this properly you need to 
give each reference to that table an alias.  So lets say your names are in 
a table called "names" with fields: id and name.  Then add this to your SQL:
join names QAO on QAO.id = QA.OperatorID
join names QAR on QAR.id = QA.QAID


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: server params

[Brent Baisley]

For starters, compare timings between running a query between the 
machines and one local to the machine. Use the command line client, not 
your web server. See if there is a significant time difference. If 
there is, there is probably something wrong on your network. If not, 
then you should check your web server for bottlenecks.
Of course, network slowdowns could come up if the machines are on 
separate switches, one or both are not running full duplex, the switch 
isn't set to full-duplex, network errors (i.e. due to damaged or poor 
quality cables). And naturally, checking that you are not running at 
the full capacity of your network.

Bottom line, put in timings in your code to find out where the 
bottleneck is. Monitor your machines for cpu, memory and I/O usage 
levels. You need to find where the bottleneck is before you start 
playing with server variables.

On Apr 21, 2005, at 3:47 PM, Rob Brooks wrote:
Hello, we have a web based application where the mysql server runs on a
separate box from the code.  The 2 boxes are on the same subnet so 
there
really shouldn't be any latency issues(emphasis on shouldn't .) but 
we're
trying to track down some timeout glitches in the application.  I was
wondering what things I might look at as far as server variable 
settings
that might be pertinent to having the mysql server on a different box 
from
the code.


server 4.0.2 on darwin6.8

Thx
Rob

--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

server params

[Rob Brooks]

Hello, we have a web based application where the mysql server runs on a
separate box from the code.  The 2 boxes are on the same subnet so there
really shouldn't be any latency issues(emphasis on shouldn't .) but we're
trying to track down some timeout glitches in the application.  I was
wondering what things I might look at as far as server variable settings
that might be pertinent to having the mysql server on a different box from
the code.

 

server 4.0.2 on darwin6.8

 

Thx

Rob

Newbie trying the impossible?

[Scott Hamm]

I could understand this simple SQL statement:

SELECT 
Associates.LastName + ', ' + Associates.FirstName as name,
Production.Hours,
Production.Quantity
FROM 
production,
Associates
WHERE
Associates.ID=Production.OID;

Would produce full name in table.

However when I used left join (trying to learn it) I issued this command:

SELECT 
QA.OperatorID, 
QA.QAID, 
QA.BrandID,
QA.Batch, 
QA.KeyDate,
Batch.[Order],
Batch.Errors,
Batch.Comments
FROM
QA
Left Join
Batch
ON
(Batch.QAID=QA.ID)
WHERE
ID='77363';

How do I get around to it with 2 different names that uses SAME table?

QA.OperatorID (operator)
QA.QAID (reviewer)

Or am I asking for the impossible?



-- 
Power to people, Linux is here.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

InnoDB Writes blocking Reads

[Andy McCurdy]

I've been seeing some weirdness w/ MySQL and InnoDB over the past few days. 
I have a database that's entirely using InnoDB.  The database is roughly 
4.5G (one datafile) and contains a little over 50 tables.  The web 
applications that hit this database do about 85-90% reads and 10-15% writes. 
Whenever a write comes in, it seems that subsequent reads get blocked to the 
_ENTIRE DATABASE_.  What really confuses me is that reads even to other 
tables that aren't any way involved with the table that's being written to 
are waiting until the write completes.  I do not use any foreign key 
relationships which could block a parent row.

Whenever I run "show processlist", everything looks fine until a write comes 
in.  Reads are answered < 1 sec, and generally don't even appear on the 
processlist.  However, as soon as a write hits, all of the reads are in a 
state of "NULL" until the write completes.  This problem is compounded when 
several different writes happen from different connections at the same time. 
It leads to the MySQL server blocking long enough that the number of reads 
in the queue exceed the number of max connections.  Once the writes happen, 
all of the read queries in the queue get answered and the normal processing 
continues.

I've included two samples from the "show processlist" command.  They were 
taken approx. 1 second apart and show the growing amount of reads when 
writes are in the queue.  I've removed the User and Host columns as they 
don't seem to be relevant as well as trimmed the state column down.  I've 
also included the my.cnf config file and an output of "show status".

Additionally, I'm running Redhat 7.3 - kernel ver. 2.4.20-28.7bigmem #1 SMP

Any help on eliminating this bottleneck would be greatly appreciated!
Thanks.
-andy


=
process list 1
=


+--+--+-++-+
| Id   | db   | Command | Time   | State   | Info
+--+--+-++-+
|  2972920 | NULL | Binlog Dump | 463909 | Has sen | NULL
|  2972930 | NULL | Binlog Dump | 463909 | Has sen | NULL
| 10954259 | NULL | Query   | 0  | NULL| show processlist
| 10962986 | gne  | Sleep   | 4  | | NULL
| 10962987 | gne  | Sleep   | 4  | | NULL
| 10962997 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963003 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='142'
| 10963018 | gne  | Query   | 3  | NULL| SELECT * FROM clan 
WHERE id='274'
| 10963019 | gne  | Query   | 3  | update  | INSERT INTO rating 
(ref_type_id, re
| 10963020 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='55'
| 10963021 | gne  | Query   | 3  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963022 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963023 | gne  | Query   | 3  | NULL| SELECT r.*, ui.username 
FROM review
| 10963024 | gne  | Query   | 2  | NULL| SELECT count(*) AS 
count FROM pm_me
| 10963025 | gne  | Query   | 2  | NULL| SELECT * FROM contact 
WHERE user_id
| 10963026 | gne  | Query   | 2  | NULL| SELECT * FROM clan 
WHERE id='729'
| 10963027 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963028 | gne  | Query   | 2  | NULL| select r.review_id from 
review r wh
| 10963029 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963030 | gne  | Query   | 2  | update  | INSERT INTO pm_message 
SET id=NULL,
| 10963031 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963032 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963033 | gne  | Query   | 2  | NULL| select r.review_id from 
review r wh
| 10963034 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963035 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963036 | gne  | Query   | 2  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963037 | gne  | Query   | 2  | NULL| SELECT c.* FROM 
clan_pid cp, clan c
| 10963038 | gne  | Query   | 2  | NULL| SELECT * FROM 
pm_message WHERE id =
| 10963039 | gne  | Query   | 2  | NULL| SELECT r.*, ui.username 
FROM review
| 10963041 | gne  | Query   | 2  | NULL| SELECT c.* FROM 
clan_pid cp, clan c
| 10963042 | gne  | Query   | 1  | NULL| select r.review_id from 
review r wh
| 10963043 | gne  | Query   | 1  | NULL| SELECT urs_id FROM 
user_info_new WH
| 10963045 | gne  | Query   | 1  | NULL| SELECT count(*) AS 
count FROM pm_me
| 10963046 | gne  | Query   | 1  | NULL| SELECT r.*, ui.user

crushed innodb table

[iv]

hi
I've got a problem with a crushed innodb table (as i think)
When I'm trying to make a backup, something like this appears:
mysqldump: Error 2013: Lost connection to MySQL server during query 
when dumping table `adstats` at row 34342;

trying to check table:
mysql> check table adstats extended;
ERROR 2013 (HY000): Lost connection to MySQL server during query
repairing that:
mysql> repair table adstats;
phpads.adstats | repair | note | The handler for the table doesn't 
support repair
the same is going on with "repair table ads_adstats USE_FRM"

I have also tried mysqlchk and myisamchk with -r and -o options. It 
didn't work, sinse that "adstats"  is the innodb  table..
myisamchk: error: 'adstats.frm' is not a MyISAM-table
# mysqlcheck -r phpads adstats
note : The handler for the table doesn't support repair

Any ideas how to deal with the problem? I have read through about a 
hundred threads, but i haven't find a method of repairing innodb tables; 
and only in one place I've found a problem similar to mine, but without 
a solution

Thanks in advance,
iv 
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unique items from all columns, very slow

[SGreen]

Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/21/2005 01:39:15 
PM:

> On 4/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 
> 05:46:25  PM:
> >  > On 4/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >  > > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005
> > 01:56:03 PM:
> >  > >  > On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> wrote:
> >  > >  > > In the last episode (Apr 20), Willie Gnarlson said:
> >  > >  > > > I have been attempting to uncover a fast method for 
retrieving
> > unique
> >  > >  > > > items from every column in a table. The table has 
approximately
> > 60
> >  > >  > > > thousand rows. Each row has an index associated with it, 
and
> > running
> >  > >  > > > 'explain select distinct class from dlist' shows that it 
isusing
> > the
> >  > >  > > > index.
> >  > >  > > >
> >  > >  > > > However, since there are 24 columns, running that select 
query
> > for
> >  > >  > > > each column takes about 4 long seconds on a P4 2.4 Ghz.
> >  > >  > > 
> >  
> >  > Okay, they aren't creating new values using that form. The form 
I've
> >  > been talking about is a form to limit results from the database to
> >  > only specific values for  specific fields without the user having 
to
> >  > know from memory what the possible values are (plus, they'll change
> >  > every so often).
> >  > 
> 
> (...)
> 
> > 
> > If I read that last part correctly, you have already identified the 
need for
> > a mechanism for managing the value lists separately from what the user
> > enters. In this case you should have everything in place (or almost in
> > place) to put each list into its own table (Someone else suggested 
this
> > design, too). 
> 
> Right, yes.
> 
> (...)
> > Run your queries once to build your lists then use another form (or 
even use
> > a separate application) to manage the lists. Because it's a one-time 
event,
> > getting the first set of unique values can take all night if it needs 
to.
> > What you need to be fast is the building of the select boxes. By 
having
> > those lists ready-to-go on their own tables, it will be as fast as a
> > sequential read from the disk (very fast) or a read from the disk 
cache
> > (faster) or a read from the query cache (fastest). In any case, 
running the
> > queries will no longer slow you down. 
> 
> It actually seems slower. The separate tables from a previous try look
> like this:
> 
> CREATE TABLE `ET` (
>   `ET` double NOT NULL default '0',
>   PRIMARY KEY  (`ET`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> That table has 4781 records, but some have more (one has ~18
> thousand). There are 42 "column" tables.
> 
> Querying all of them took 5.58 seconds.
> 
> -- 
> Willie Gnarlson

I am SO confused!!! (and that's hard to do) You have a lookup list with 
eighteen thousand entries in it? Your users must pick 1 of 4781 floating 
point numbers from a select box? What kind of information are you trying 
to work with? I cannot imagine an application that uses data like you are 
describing. Can you help a poor fellow out and let us in on what you are 
working on?

Here's a question, it may have taken 5.58 seconds for the first pass 
through all 42 tables but how fast was the second pass? Was that 5.58 
measuring just the queries or the time it took to build the select boxes, 
too? As far as performance goes, It may be faster to check the user's 
entries during the form validation/processing phase than it will be to 
force them to enter the information the correct way the first time. 

Thanks!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Unique items from all columns, very slow

[Willie Gnarlson]

On 4/21/05, Willie Gnarlson <[EMAIL PROTECTED]> wrote:

(...)

> It actually seems slower. The separate tables from a previous try look
> like this:
> 
> CREATE TABLE `ET` (
>   `ET` double NOT NULL default '0',
>   PRIMARY KEY  (`ET`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> That table has 4781 records, but some have more (one has ~18
> thousand). There are 42 "column" tables.
> 
> Querying all of them took 5.58 seconds.

I forgot to mention that those tables *do* have unique items, too. 

-- 
Willie Gnarlson

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unique items from all columns, very slow

[Willie Gnarlson]

On 4/21/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 05:46:25  PM:
>  > On 4/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>  > > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005
> 01:56:03 PM:
>  > >  > On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> wrote:
>  > >  > > In the last episode (Apr 20), Willie Gnarlson said:
>  > >  > > > I have been attempting to uncover a fast method for retrieving
> unique
>  > >  > > > items from every column in a table. The table has approximately
> 60
>  > >  > > > thousand rows. Each row has an index associated with it, and
> running
>  > >  > > > 'explain select distinct class from dlist' shows that it isusing
> the
>  > >  > > > index.
>  > >  > > >
>  > >  > > > However, since there are 24 columns, running that select query
> for
>  > >  > > > each column takes about 4 long seconds on a P4 2.4 Ghz.
>  > >  > > 
>  
>  > Okay, they aren't creating new values using that form. The form I've
>  > been talking about is a form to limit results from the database to
>  > only specific values for  specific fields without the user having to
>  > know from memory what the possible values are (plus, they'll change
>  > every so often).
>  >   

(...)

>  
> If I read that last part correctly, you have already identified the need for
> a mechanism for managing the value lists separately from what the user
> enters. In this case you should have everything in place (or almost in
> place) to put each list into its own table (Someone else suggested this
> design, too). 

Right, yes.
  
(...)
> Run your queries once to build your lists then use another form (or even use
> a separate application) to manage the lists. Because it's a one-time event,
> getting the first set of unique values can take all night if it needs to.
> What you need to be fast is the building of the select boxes. By having
> those lists ready-to-go on their own tables, it will be as fast as a
> sequential read from the disk (very fast) or a read from the disk cache
> (faster) or a read from the query cache (fastest). In any case, running the
> queries will no longer slow you down. 

It actually seems slower. The separate tables from a previous try look
like this:

CREATE TABLE `ET` (
  `ET` double NOT NULL default '0',
  PRIMARY KEY  (`ET`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

That table has 4781 records, but some have more (one has ~18
thousand). There are 42 "column" tables.

Querying all of them took 5.58 seconds.

-- 
Willie Gnarlson

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ERROR 2005

[Ercilio Almeida]

I´ve installed MYSQL CONTROL CENTER 0.9.4 beta,
when i tried to test the aplication, i get this error "ERROR  2005: Unknow 
MYSQL Server Host 'abc'/(11001)".
 
Thanks, i'm waiting your answer as soon as possible.
 
Ercilio


-
Yahoo! Acesso Grátis: Internet rápida e grátis. Instale o discador agora!

Re: Online documentation

[doug]

Thank you for the clear statement of policy. I had in fact forgotten
about the manual that comes with the installation. It however is
packaged as one rather large html or text file (my system is FreeBSD).
The web site is a far superior reference because of the file format.

Doug

On Thu, 21 Apr 2005, Joerg Bruehe wrote:

> Hi Douglas, all!
>
>
> Am Mi, den 20.04.2005 schrieb [EMAIL PROTECTED] um 17:33:
> > Is the online documentation for 4.0.x available? I was working with
> > dates a while back. As it happens 4.1 added the very function I wanted.
> > But that was not marked as added in 4.1, nor should be be as that would
> > be an unbelieable amount of cruft.

[cut]

> All I can propose is to take the manual that came with the version you
> downloaded and installed. It will at least not contain changes that were
> introduced later.
>
>
> The documentation team currently is changing the way the manual is
> maintained, but I do not know whether this will introduce features (like
> selective extraction) which would help you with your desires.
>
>
> Regards,
> Joerg
>
> --
> Joerg Bruehe, Senior Production Engineer
> MySQL AB, www.mysql.com
>

_
Douglas Denault
http://www.safeport.com
[EMAIL PROTECTED]
Voice: 301-469-8766
  Fax: 301-469-0601

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Problem

[Dto. Sistemas de Unitel]

You don't understand me, I refer that if in a table I use productos.prod_id
and in other table indexes.id if I can use this two fields like the same
index, because when I named the two equal, the index start to work fine.

Thanks

-Mensaje original-
De: Roger Baklund [mailto:[EMAIL PROTECTED] 
Enviado el: jueves, 21 de abril de 2005 18:17
Para: mysql@lists.mysql.com
CC: Dto. Sistemas de Unitel
Asunto: Re: Query Problem

Dto. Sistemas de Unitel wrote:
> Hi Roger,
> 
> You are ok, there was an index problem in one table, they name of the rows
> wasn't equal and MySQL didn't recognize they as the same index. I have
> changed the row name and now is working fine, but I have a little
question,
> How can I use indexes with different names in it's  tables?

I'm not sure if I understand the question, but in general the names of 
the columns and indexes are not case sensitive, which in your case means 
prod_id and PROD_ID should be treated equal. Table names and database 
names are different, it depends on the filesystem the server is using:

http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html >

-- 
Roger



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query Problem

[Roger Baklund]

Dto. Sistemas de Unitel wrote:
Hi Roger,
You are ok, there was an index problem in one table, they name of the rows
wasn't equal and MySQL didn't recognize they as the same index. I have
changed the row name and now is working fine, but I have a little question,
How can I use indexes with different names in it's  tables?
I'm not sure if I understand the question, but in general the names of 
the columns and indexes are not case sensitive, which in your case means 
prod_id and PROD_ID should be treated equal. Table names and database 
names are different, it depends on the filesystem the server is using:

http://dev.mysql.com/doc/mysql/en/name-case-sensitivity.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible bug left join and null

[James Nobis]

Thanks everyone for such quick and thorough responses!
Quoting [EMAIL PROTECTED]:
James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005
10:44:07 AM:
The problem is something fairly simple but yet MySQL seems to make this
complicated.  Essentially, find a list of customers who have not
bought product
X ever.  (Customers have orders, orders have order line items).  All
3 coworkers
independently arrived at the same sql which failed to work.  Then, we
wrote it
as a subquery which has performance issue and finally rewrote it with a
temp
table and a join.  However, it seems like what we had should have
worked.
Borrowing from http://builder.com.com/5100-6388_14-5532304.html about
midway
down the page I set out to create an identical schema and query in
MySQL.
CREATE TABLE `Customer` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `Customer` VALUES (1, 'bob');
INSERT INTO `Customer` VALUES (2, 'nathan');
CREATE TABLE `Order` (
  `id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL default '0',
  `order_date` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00');
INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00');
CREATE TABLE `OrderLines` (
  `order_id` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `OrderLines` VALUES (1, 5, 1);
INSERT INTO `OrderLines` VALUES (1, 9, 1);
INSERT INTO `OrderLines` VALUES (2, 15, 1);
INSERT INTO `OrderLines` VALUES (2, 25, 1);
Then, I run the following query:
SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order` ON Customer.id = Order.customer_id
INNER JOIN OrderLines ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
WHERE Order.customer_id IS NULL
I would expect this to return a single row with Customer.id 2.
Is there something obvious my coworkers and I are missing?
James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com
It's hard to remember where I picked this up but I once read that it's
generally bad form to start with an outer join (LEFT or RIGHT JOIN) and
move into an INNER JOIN like you are doing. Because if the rows from the
Order table are optional to the results of the query, the rows from the
OrderLines are transitively optional as well (if an Order row doesn't
exist then there can't be any OrderLine rows either). So an equivalent
form of your query could have been:
SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order`
   ON Customer.id = Order.customer_id
LEFT JOIN OrderLines
   ON Order.id = OrderLines.order_id
   AND OrderLines.product_id =9
WHERE Order.customer_id IS NULL;
But this won't help you to determine if a Customer had NEVER ordered that
product because you are including Order rows regardless of whether that
order had a product #9 on it or not. I then tried a nested JOIN using
parentheses like this and got no names:
SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN (`Order`
INNER JOIN OrderLines
   ON Order.id = OrderLines.order_id
   AND OrderLines.product_id =9
) ON Customer.id = Order.customer_id
WHERE Order.customer_id IS NULL;
The unfiltered results of that join look like this(sorry if it wraps):
SELECT *
FROM Customer
LEFT JOIN (
   `Order` INNER JOIN OrderLines
   ON Order.id = OrderLines.order_id
   AND OrderLines.product_id =9
) ON Customer.id = Order.customer_id;
++++-+-+--++--+
| id | name   | id | customer_id | order_date  | order_id |
product_id | quantity |
++++-+-+--++--+
|  1 | bob|  1 |   1 | -00-00 00:00:00 |1 |   9 |
1 |
|  2 | nathan |  1 |   1 | -00-00 00:00:00 | NULL | NULL |
   NULL |
|  1 | bob|  2 |   2 | -00-00 00:00:00 | NULL | NULL |
   NULL |
|  2 | nathan |  2 |   2 | -00-00 00:00:00 | NULL | NULL |
   NULL |
++++-+-+--++--+
4 rows in set (0.00 sec)
Each customer has at least one order so the nested JOIN didn't work to
find your answer either (BTW- nested joins are not documented as a valid
syntax so I wasn't sure if it was going to work or not).
However, I thought, why not do exactly what the original question stated:
count how many times product 9 appears as a line item on an order and
return the names of the customers where that count is 0.
SELECT Customer.id
   , Customer.name
   , COUNT(orderlines.product_id) as Line

Re: Slow query: mysql not using correct indexes?

[Dan Nelson]

In the last episode (Apr 21), Scott Gifford said:
> I'm having a problem with query running very slowly.  I run similar
> queries on other tables all the time that perform as expected, and
> this query used to run fine until I removed an explicit LEFT JOIN and
> let the optimizer decide in what order to join two of the tables.
> That fixed some other performance problems I was having, but seems to
> have introduced this new one.
> 
> I'm using "Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)" on an
> older copy of RedHat Linux 7.3 (kernel 2.4.25, libc6 2.2.5).

I get the correct EXPLAIN plan (test_homes first, then
test_homes_supplemental, then test_homes_stats) when I load your data
into Mysql 4.1.11, and the query itself comes back immediately.  Is
upgrading an option?  Even if you can't upgrade to 4.1, try going to
the latest in the 3.23 branch (3.23.58, released way back in Sep 2003). 
3.23.49 is over 3 years old...

++-+-++---+-+-+-+--+---+
| id | select_type | table   | type   | possible_keys | key 
| key_len | ref | rows | Extra |
++-+-++---+-+-+-+--+---+
|  1 | SIMPLE  | test_homes  | index  | PRIMARY   | price   
|   4 | NULL|  100 |   |
|  1 | SIMPLE  | test_homes_supplemental | eq_ref | PRIMARY   | PRIMARY 
|   9 | test.test_homes.mls_num |1 |   |
|  1 | SIMPLE  | test_homes_stats| eq_ref | PRIMARY   | PRIMARY 
|   9 | test.test_homes.mls_num |1 |   |
++-+-++---+-+-+-+--+---+

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible bug left join and null

[Brent Baisley]

There is nothing wrong with what MySQL is doing. Your query is 
incorrect for what you are looking for. Step through your query and 
you'll see your error.

SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order` ON Customer.id = Order.customer_id
You now have a list of the all your Customers with and without orders.
INNER JOIN OrderLines ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
You now joined the Customer/Order list with OrderLines with a product 
id of 9. Here is where your logic fails. You now have a list of all 
customers who ordered product 9. The list does not contain ANY 
customers without an order for product 9.

WHERE Order.customer_id IS NULL
Since you only have a list of customers who ordered product 9, you now 
filter out the entire result set.

Change your inner join to a left join and your query should work. MySQL 
will step through your query in the order you wrote, building or 
filtering as it goes along. You can somewhat alter this order with LEFT 
and/or RIGHT joins.


On Apr 21, 2005, at 10:44 AM, James Nobis wrote:
The problem is something fairly simple but yet MySQL seems to make this
complicated.  Essentially, find a list of customers who have not 
bought product
X ever.  (Customers have orders, orders have order line items).  All 3 
coworkers
independently arrived at the same sql which failed to work.  Then, we 
wrote it
as a subquery which has performance issue and finally rewrote it with 
a temp
table and a join.  However, it seems like what we had should have 
worked.

Borrowing from http://builder.com.com/5100-6388_14-5532304.html about 
midway
down the page I set out to create an identical schema and query in 
MySQL.

CREATE TABLE `Customer` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `Customer` VALUES (1, 'bob');
INSERT INTO `Customer` VALUES (2, 'nathan');
CREATE TABLE `Order` (
  `id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL default '0',
  `order_date` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00');
INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00');
CREATE TABLE `OrderLines` (
  `order_id` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `OrderLines` VALUES (1, 5, 1);
INSERT INTO `OrderLines` VALUES (1, 9, 1);
INSERT INTO `OrderLines` VALUES (2, 15, 1);
INSERT INTO `OrderLines` VALUES (2, 25, 1);
Then, I run the following query:
SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order` ON Customer.id = Order.customer_id
INNER JOIN OrderLines ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
WHERE Order.customer_id IS NULL
I would expect this to return a single row with Customer.id 2.
Is there something obvious my coworkers and I are missing?
James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible bug left join and null

[SGreen]

James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005 
10:44:07 AM:

> The problem is something fairly simple but yet MySQL seems to make this
> complicated.  Essentially, find a list of customers who have not 
> bought product
> X ever.  (Customers have orders, orders have order line items).  All
> 3 coworkers
> independently arrived at the same sql which failed to work.  Then, we 
wrote it
> as a subquery which has performance issue and finally rewrote it with a 
temp
> table and a join.  However, it seems like what we had should have 
worked.
> 
> Borrowing from http://builder.com.com/5100-6388_14-5532304.html about 
midway
> down the page I set out to create an identical schema and query in 
MySQL.
> 
> CREATE TABLE `Customer` (
>   `id` int(11) NOT NULL default '0',
>   `name` varchar(255) NOT NULL default ''
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> INSERT INTO `Customer` VALUES (1, 'bob');
> INSERT INTO `Customer` VALUES (2, 'nathan');
> 
> CREATE TABLE `Order` (
>   `id` int(11) NOT NULL auto_increment,
>   `customer_id` int(11) NOT NULL default '0',
>   `order_date` datetime NOT NULL default '-00-00 00:00:00',
>   PRIMARY KEY  (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
> 
> INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00');
> INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00');
> 
> CREATE TABLE `OrderLines` (
>   `order_id` int(11) NOT NULL default '0',
>   `product_id` int(11) NOT NULL default '0',
>   `quantity` int(11) NOT NULL default '0'
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
> 
> INSERT INTO `OrderLines` VALUES (1, 5, 1);
> INSERT INTO `OrderLines` VALUES (1, 9, 1);
> INSERT INTO `OrderLines` VALUES (2, 15, 1);
> INSERT INTO `OrderLines` VALUES (2, 25, 1);
> 
> Then, I run the following query:
> SELECT DISTINCT Customer.id, Customer.name
> FROM Customer
> LEFT JOIN `Order` ON Customer.id = Order.customer_id
> INNER JOIN OrderLines ON Order.id = OrderLines.order_id
> AND OrderLines.product_id =9
> WHERE Order.customer_id IS NULL
> 
> I would expect this to return a single row with Customer.id 2.
> 
> Is there something obvious my coworkers and I are missing?
> 
> James Nobis
> Web Developer
> Academic Superstore
> 223 W. Anderson Ln. Suite A110, Austin, TX 78752
> Voice: (512) 450-1199 x453 Fax: (512) 450-0263
> http://www.academicsuperstore.com
> 
It's hard to remember where I picked this up but I once read that it's 
generally bad form to start with an outer join (LEFT or RIGHT JOIN) and 
move into an INNER JOIN like you are doing. Because if the rows from the 
Order table are optional to the results of the query, the rows from the 
OrderLines are transitively optional as well (if an Order row doesn't 
exist then there can't be any OrderLine rows either). So an equivalent 
form of your query could have been:

SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order` 
ON Customer.id = Order.customer_id
LEFT JOIN OrderLines 
ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
WHERE Order.customer_id IS NULL;

But this won't help you to determine if a Customer had NEVER ordered that 
product because you are including Order rows regardless of whether that 
order had a product #9 on it or not. I then tried a nested JOIN using 
parentheses like this and got no names:

SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN (`Order` 
INNER JOIN OrderLines 
ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
) ON Customer.id = Order.customer_id
WHERE Order.customer_id IS NULL;

The unfiltered results of that join look like this(sorry if it wraps):

SELECT *
FROM Customer
LEFT JOIN (
`Order` INNER JOIN OrderLines
ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
) ON Customer.id = Order.customer_id;
++++-+-+--++--+
| id | name   | id | customer_id | order_date  | order_id | 
product_id | quantity |
++++-+-+--++--+
|  1 | bob|  1 |   1 | -00-00 00:00:00 |1 |   9 |  
 1 |
|  2 | nathan |  1 |   1 | -00-00 00:00:00 | NULL | NULL | 
NULL |
|  1 | bob|  2 |   2 | -00-00 00:00:00 | NULL | NULL | 
NULL |
|  2 | nathan |  2 |   2 | -00-00 00:00:00 | NULL | NULL | 
NULL |
++++-+-+--++--+
4 rows in set (0.00 sec)

Each customer has at least one order so the nested JOIN didn't work to 
find your answer either (BTW- nested joins are not documented as a valid 
syntax so I wasn't sure if it was going to work or not). 

However, I thought, why not do exactly what the original question stated: 
count how many times product 9 appears as a line item on an order and 
return the names of the customers where tha

Slow query: mysql not using correct indexes?

[Scott Gifford]

Hello,

I'm having a problem with query running very slowly.  I run similar
queries on other tables all the time that perform as expected, and
this query used to run fine until I removed an explicit LEFT JOIN and
let the optimizer decide in what order to join two of the tables.
That fixed some other performance problems I was having, but seems to
have introduced this new one.

I'm using "Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)" on an
older copy of RedHat Linux 7.3 (kernel 2.4.25, libc6 2.2.5).

I've simplified the problem as much as I can and still reproduce it;
the actual tables I'm interested in are much larger, with many more
columns and rows.

The basic problem seems to be when I do a query sorting by price and
joining these three tables together, mysql resorts to "Using
temporary; Using filesort":

mysql> EXPLAIN SELECT test_homes.price, 
  test_homes.mls_num,
  test_homes_supplemental.bathrooms,
  test_homes_stats.detail_views
 FROM test_homes, 
  test_homes_supplemental 
LEFT JOIN test_homes_stats 
  ON test_homes.mls_num = test_homes_stats.mls_num 
WHERE test_homes.mls_num = test_homes_supplemental.mls_num 
 ORDER BY test_homes.price
LIMIT 10;


+-++---+-+-+-+--+-+
| table   | type   | possible_keys | key | key_len | 
ref | rows | Extra   |

+-++---+-+-+-+--+-+
| test_homes_supplemental | ALL| PRIMARY   | NULL|NULL | 
NULL|  100 | Using temporary; Using filesort |
| test_homes  | eq_ref | PRIMARY   | PRIMARY |   9 | 
test_homes_supplemental.mls_num |1 |   |
| test_homes_stats| eq_ref | PRIMARY   | PRIMARY |   9 | 
test_homes.mls_num  |1 |   |

+-++---+-+-+-+--+-+

That's very slow for 22,000 rows.  I don't know why it's doing this,
since the column I'm sorting by is indexed; it seems like it should
get the data from the price index on test_homes, then use eq_ref to
join in test_homes_supplemental and test_homes_stats.  Here are the
table definitions:

CREATE TABLE `test_homes` (
  `mls_num` char(9) NOT NULL default '',
  `price` mediumint(8) unsigned default NULL,
PRIMARY KEY  (`mls_num`),
  KEY `price` (`price`));

CREATE TABLE `test_homes_supplemental` (
  `mls_num` char(9) NOT NULL default '',
  `bathrooms` tinyint(3) unsigned default NULL,
PRIMARY KEY  (`mls_num`));

 CREATE TABLE `test_homes_stats` (
  `mls_num` char(9) NOT NULL default '',
  `detail_views` int(11) NOT NULL default '0',
PRIMARY KEY  (`mls_num`));

Both test_homes and test_homes_supplemental contain one row for each
item; test_homes_stats contain zero or one rows for each item.

If I do explicit LEFT JOINs to tell MySQL what order to join in, I get
the results I expect:

mysql> EXPLAIN SELECT test_homes.price,
  test_homes.mls_num,
  test_homes_supplemental.bathrooms,
  test_homes_stats.detail_views
 FROM test_homes
LEFT JOIN test_homes_supplemental 
  ON test_homes.mls_num = 
test_homes_supplemental.mls_num
LEFT JOIN test_homes_stats 
  ON test_homes.mls_num = test_homes_stats.mls_num 
 ORDER BY test_homes.price
LIMIT 10;

+-++---+-+-++--+---+
| table   | type   | possible_keys | key | key_len | 
ref| rows | Extra |

+-++---+-+-++--+---+
| test_homes  | index  | NULL  | price   |   4 | 
NULL   |  100 |   |
| test_homes_supplemental | eq_ref | PRIMARY   | PRIMARY |   9 | 
test_homes.mls_num |1 |   |
| test_homes_stats| eq_ref | PRIMARY   | PRIMARY |   9 | 
test_homes.mls_num |1 |   |

+-++---+-+-++--+---+

But this query is part of a larger query-generating framework, and I'd
rather not fill the framework will spec

Re: SQL to list table names

[Chris Ramsay]

http://dev.mysql.com/doc/mysql/en/show-tables.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL to list table names

[Frank Bax]

At 11:22 AM 4/21/05, Darryl Hoar wrote:
I am running Mysql 3.23.  Is there an sql statment that will allow me
programmatically to retrieve the names of the tables in a database ?

show tables;
Yes, this is an SQL statement! 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL to list table names

[Paul DuBois]

At 10:22 -0500 4/21/05, Darryl Hoar wrote:
Greetings,
I am running Mysql 3.23.  Is there an sql statment that will allow me
programmatically
to retrieve the names of the tables in a database ?
http://dev.mysql.com/doc/mysql/en/show-tables.html ?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SQL to list table names

[Darryl Hoar]

Greetings,
I am running Mysql 3.23.  Is there an sql statment that will allow me
programmatically
to retrieve the names of the tables in a database ?

thanks,
Darryl



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible bug left join and null

[Jigal van Hemert]

From: "James Nobis"
> SELECT DISTINCT Customer.id, Customer.name
> FROM Customer
> LEFT JOIN `Order` ON Customer.id = Order.customer_id
> INNER JOIN OrderLines ON Order.id = OrderLines.order_id
> AND OrderLines.product_id =9
> WHERE Order.customer_id IS NULL

I expect customers to have placed at least one order, or can one have
customers which have not a single order?
Do you want these "customers" included in the output?

Anyway, I expect that you want the order of all customers checked; this can
be done with a (inner) join:
`Customer` JOIN `Order` ON `Customer`.`id` = `Order`.`customer_id`

Then you left-join this with the order lines to find out all the products
and check for an empty order id:

SELECT DISTINCT Customer.id, Customer.name
FROM Customer
JOIN `Order` ON Customer.id = Order.customer_id
LEFT JOIN OrderLines ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
WHERE OrderLines.order_id IS NULL

This returns:

+-+---+
| Customer.id | Customer.name |
+-+---+
|   2 |nathan |
+-+---+

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Find out who owns database and tables

[Paul DuBois]

At 9:44 -0500 4/21/05, Scott Purcell wrote:
Hello,
I have created some databases and tables, a while back, as a new 
user. I believe when the system was installed it asked me fora root 
username and password. And I set one.

I have some databases, and I would like to find out two things:
1) Who owns what database and its tables: Is there a command to find this out?
In terms of filesystem ownership, the database directories should be set
to be owned by the same login account that you use for running the server.
In terms of ownership by MySQL accounts, there is no such concept.
A given MySQL account can be granted permission to access a database, but
no account "owns" it.
2) If they are owned by root, can I create a new user and update the 
database and its tables to that user?
You can grant access to the database for that user (with the GRANT 
statement).
The user will not "own" the databases.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Find out who owns database and tables

[Scott Purcell]

Hello,

I have created some databases and tables, a while back, as a new user. I 
believe when the system was installed it asked me fora root username and 
password. And I set one.

I have some databases, and I would like to find out two things:

1) Who owns what database and its tables: Is there a command to find this out?
2) If they are owned by root, can I create a new user and update the database 
and its tables to that user?

I am referring to the docs, but I cannot find find the exact syntax for this.


Thanks
Scott



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

possible bug left join and null

[James Nobis]

The problem is something fairly simple but yet MySQL seems to make this
complicated.  Essentially, find a list of customers who have not bought product
X ever.  (Customers have orders, orders have order line items).  All 3 coworkers
independently arrived at the same sql which failed to work.  Then, we wrote it
as a subquery which has performance issue and finally rewrote it with a temp
table and a join.  However, it seems like what we had should have worked.

Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway
down the page I set out to create an identical schema and query in MySQL.

CREATE TABLE `Customer` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `Customer` VALUES (1, 'bob');
INSERT INTO `Customer` VALUES (2, 'nathan');

CREATE TABLE `Order` (
  `id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL default '0',
  `order_date` datetime NOT NULL default '-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00');
INSERT INTO `Order` VALUES (2, 2, '-00-00 00:00:00');

CREATE TABLE `OrderLines` (
  `order_id` int(11) NOT NULL default '0',
  `product_id` int(11) NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `OrderLines` VALUES (1, 5, 1);
INSERT INTO `OrderLines` VALUES (1, 9, 1);
INSERT INTO `OrderLines` VALUES (2, 15, 1);
INSERT INTO `OrderLines` VALUES (2, 25, 1);

Then, I run the following query:
SELECT DISTINCT Customer.id, Customer.name
FROM Customer
LEFT JOIN `Order` ON Customer.id = Order.customer_id
INNER JOIN OrderLines ON Order.id = OrderLines.order_id
AND OrderLines.product_id =9
WHERE Order.customer_id IS NULL

I would expect this to return a single row with Customer.id 2.

Is there something obvious my coworkers and I are missing?

James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

JBoss queries aren't cached by MySQL

[Rafal Kedziorski]

Hi,
we have the problem, that queries generated by JBoss or our code which 
runns under JBoss will be not cached by MySQL. The same query sendet from 
an external application or MySQLFront will be cached by the same MySQL.

I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a.
Any idea why?
Regards,
Rafal
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Unique items from all columns, very slow

[SGreen]

Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 05:46:25 
PM:

> On 4/20/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Willie Gnarlson <[EMAIL PROTECTED]> wrote on 04/20/2005 
01:56:03 PM:
> >  > On 4/20/05, Dan Nelson <[EMAIL PROTECTED]> wrote:
> >  > > In the last episode (Apr 20), Willie Gnarlson said:
> >  > > > I have been attempting to uncover a fast method for retrieving 
unique
> >  > > > items from every column in a table. The table has approximately 
60
> >  > > > thousand rows. Each row has an index associated with it, and 
running
> >  > > > 'explain select distinct class from dlist' shows that it 
isusing the
> >  > > > index.
> >  > > >
> >  > > > However, since there are 24 columns, running that select query 
for
> >  > > > each column takes about 4 long seconds on a P4 2.4 Ghz.
> >  > > 

> Okay, they aren't creating new values using that form. The form I've
> been talking about is a form to limit results from the database to
> only specific values for  specific fields without the user having to
> know from memory what the possible values are (plus, they'll change
> every so often).
> 

> 
> -- 
> Willie Gnarlson

If I read that last part correctly, you have already identified the need 
for a mechanism for managing the value lists separately from what the user 
enters. In this case you should have everything in place (or almost in 
place) to put each list into its own table (Someone else suggested this 
design, too).

This kind of arrangement is very common within databases that support 
robust user interfaces. It takes WAY too long (as you already found out) 
to dynamically regenerate the full list of allowable values every time. 
What happens to your select boxes if you have an empty data table? Does 
that mean that the users can pick from NO values in any column?  By 
putting each pick list in its own table, you separate UI support data from 
your application's "real" data. 

Run your queries once to build your lists then use another form (or even 
use a separate application) to manage the lists. Because it's a one-time 
event, getting the first set of unique values can take all night if it 
needs to. What you need to be fast is the building of the select boxes. By 
having those lists ready-to-go on their own tables, it will be as fast as 
a sequential read from the disk (very fast) or a read from the disk cache 
(faster) or a read from the query cache (fastest). In any case, running 
the queries will no longer slow you down.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: many to many

[Rhino]

I'm not sure if you're a novice to databases in general or just MySQL but if
you aren't quite sure what a join is, I suspect you must be new to
relational databases in general.

In that case, may I recommend that you seriously consider devoting some time
to learn SQL? This is the language used by all of the relational databases -
and many non-relational databases as well - and it will pay you big
dividends to know the language as you continue your work with MySQL.

There are lots of ways to learn SQL:
- online tutorials (just Google on 'SQL tutorial' and you should find a
bunch)
- books
- classroom courses

Also, the product manuals for some databases come with SQL tutorials. The
MySQL manual is not particularly good at teaching SQL but IBM makes a decent
effort with their DB2 manuals and the SQL they use is virtually identical to
the MySQL dialect of SQL because they both try to conform to the same
standards.

Rhino

- Original Message -
From: "Perry Merritt" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, April 20, 2005 11:36 PM
Subject: many to many


> Hi, I'm a novice.
>
> I've designed a database that supports many to many relationships
(actually many to many to many) and I need help creating the query to find
my data. I know things like JOINs exist, but don't have a clue how to use
them.
>
> I have three main tables and two link tables, The main tables are A, B,
and C. Each are defined with id INT and word VARCHAR(32); The link tables
are X and Y. X links A and B with the columns a_id and b_id. Y links the
tables B and C with columns b_id and c_id.
>
> Here's what I want to accomplish:
>
>   Get the A.id where A.word = "some value"
>   Use A.id to search X where X.a_id=A.id (from above)
>Use all occurences of X.a_id = A.id to select word from B using
B.id=X.b_id
>AND finally,
>select C.id where C.Word = "Some other value"
> and given Y.c_id = C.id use the matching Y.b_id to further limit the
select on B.word
>
> Can this convoluted mess be understood and if so, can a single query pull
it off?
>
> I'm implementing this in perl, so I can break the queries into pieces if I
absolutely have to.
>
> Thanks
>
>
>
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Online documentation

[Gleb Paharenko]

Hello.



I'm not sure if it is possible to find on-line documentation for 4.0.x

version, but you can download the old release, documentation is shipped

with it. Archives are at:



  http://downloads.mysql.com/archives.php







[EMAIL PROTECTED] wrote:

> Is the online documentation for 4.0.x available? I was working with

> dates a while back. As it happens 4.1 added the very function I wanted.

> But that was not marked as added in 4.1, nor should be be as that would

> be an unbelieable amount of cruft.

> 

> I would be nice however if maybe one level back was available if even if

> that was as a compressed file.

> 

> _

> Douglas Denault

> http://www.safeport.com

> [EMAIL PROTECTED]

> Voice: 301-469-8766

>  Fax: 301-469-0601

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Build problem

[Gleb Paharenko]

Hello.



Are you sure that there are no official binaries for you platform?

If you have an uncommon OS, process of building MySQL from source could

be hard. I suggest you to use gmake instead of make.







>I am in the process of building MySQL version 4.1.11 as an upgrade from

>4.0.16. This build is so we will be ready for 5.0 once out of beta. We

>are

>using the source distribution and when executing the make step I get the

>following error.

>

>Making all in mysql-test

>/usr/bin/rm -f mysql-test-run mysql-test-run-t

>/usr/bin/sed \

>-e 's!@''MYSQL_NO_DASH_VERSION''@!4.1.11!' \

>/usr/bin/mv install_test_db-t

>install_test_db

>Make: Don't know how to make

>std_data/client-key.pem.  Stop.

>*** Error exit code 1

>Stop.

>*** Error exit code 1

>Stop.

>*** Error exit code 1

>Stop.

>$

>Just for the fun of things, I executed

>the command make test and the process

>completed. However, I can not get

>around this point. Any suggestions on

>how

>to correct the issue and continue.

>Thanks,

>

>>"Jeff Hinds" <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Collecting the primary key using MAX during an insert

[Dan Rossi]

Thanks for your lengthy responses everyone. I ended up using sequences 
which are available with  PEAR MDB2 and DB, so i get the currentID + 1 
while inserting, but as you say another entry could have gone in during 
the process, but this happens during the insert stage. I might have to 
revert the table to auto inc and use concat instead on the current 
primary id ? The prefix's used on the tickets are the issue areas its 
coming from therefore enum is not possible, enum would also limit it to 
that and would have to be updated all the time :)

I had a similar issue with another project where i would have to create 
the issue number using the nextId sequence, but during that time an 
extra sequence nextID may have been placed before ther first insert and 
two entries could be inserting the same number :|, its a flaw i need to 
fix. See the problem with this one is, a ticket number needs to be 
issued before the actual insert because documents get stored within 
directories of timestamp/issue number. so i need to create the 
directory from the issue number, but after they have scanned documents 
(using a twain activex :) ), and ready to submit the form all kinds of 
problems might arise :| I may have to leave the creation of directories 
and moving documents until the insert stage and store the documents in 
a temp directory which reflects the user logged in and possible 
timestamp.

On 21/04/2005, at 8:06 AM, Harald Fuchs wrote:

It sounds like you are generating primary keys based on some letters 
+ an
incrementing value.  That is a very user-friendly method but does not 
lend
itself well to MySQL.  What you CAN do with mysql is to split your 
primary
key into two columns, one text the other an auto_increment-ed numeric.
Then, when you insert the new row of data you can use 
LAST_INSERT_ID() to
get the numeric value assigned to the new row.
Although this trick does what Dan wanted, I would not recommend using
it.  First of all, it's extremely unportable - even within MySQL (it
works only with the MyISAM backend).  Secondly, it's the same kind of
mistake you do when you insist to assign consecutive numbers to your
rows.  This just won't work in the long run - you'll get gaps in the
sequence, either by deletions or by a rollback of a transaction.  You
just have to live with the gaps.
Applied to Dan's problem:
  CREATE TABLE IncidentData (
IncidentType varchar(8) NOT NULL default '',
TypeSerial int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY  (TypeSerial)
  );
  INSERT INTO IncidentData (IncidentType) VALUES ('request');
  INSERT INTO IncidentData (IncidentType) VALUES ('request');
  INSERT INTO IncidentData (IncidentType) VALUES ('warning');
  INSERT INTO IncidentData (IncidentType) VALUES ('fault');
  INSERT INTO IncidentData (IncidentType) VALUES ('request');
  SELECT CONCAT(UCASE(IncidentType),'-',LPAD(TypeSerial,8,'0')) as 
Serial
  , IncidentType
  , TypeSerial
  FROM IncidentData;

This returns:
  +--+--++
  | Serial   | IncidentType | TypeSerial |
  +--+--++
  | REQUEST-0001 | request  |  1 |
  | REQUEST-0002 | request  |  2 |
  | WARNING-0003 | warning  |  3 |
  | FAULT-0004   | fault|  4 |
  | REQUEST-0005 | request  |  5 |
  +--+--++
As you can see, there's a gap between REQUEST-0002 and 
REQUEST-0005,
but what's wrong with that?




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Problem

[Dto. Sistemas de Unitel]

Hi Roger,

You are ok, there was an index problem in one table, they name of the rows
wasn't equal and MySQL didn't recognize they as the same index. I have
changed the row name and now is working fine, but I have a little question,
How can I use indexes with different names in it's  tables?

Thanks for your help, you have been very helpful for me.
Roberto
-Mensaje original-
De: Roger Baklund [mailto:[EMAIL PROTECTED] 
Enviado el: miércoles, 20 de abril de 2005 18:30
Para: mysql@lists.mysql.com
CC: Dto. Sistemas de Unitel
Asunto: Re: Query Problem

Dto. Sistemas de Unitel wrote:
> Hi Roger,
> That was just I need. The order isn’t like you say:
> 
>
++-+---++---+-+-
>
++---+-+
> | id | select_type | table | type   | possible_keys | key |
key_len
> | ref| rows  | Extra
|
>
++-+---++---+-+-
>
++---+-+
> |  1 | SIMPLE  | t1| const  | PRIMARY,uniq  | uniq|
250
> | const  | 1 | Using temporary; Using filesort
|
> |  1 | SIMPLE  | t2| const  | PRIMARY,uniq  | uniq|
250
> | const  | 1 |
|
> |  1 | SIMPLE  | productos | ALL| PRIMARY,dupli | [NULL]  |
[NULL]
> | [NULL] | 16153 |
|
> |  1 | SIMPLE  | i2| eq_ref | PRIMARY,uniq  | PRIMARY |
16
> | unitel.productos.PROD_ID,const | 1 | Using where
|
> |  1 | SIMPLE  | i1| eq_ref | PRIMARY,uniq  | PRIMARY |
16
> | unitel.productos.PROD_ID,const | 1 | Using where
|
>
++-+---++---+-+-
>
++---+-+
>  
> 
> Productos is executed in the middle of the other two ones, the time of the
> query is about 0.44s - 0.75s, it's a little slow, so if we can optimize a
> little more should be perfect.

It seems as there is no index on productos.prod_id?

-- 
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Online documentation

[Joerg Bruehe]

Hi Douglas, all!


Am Mi, den 20.04.2005 schrieb [EMAIL PROTECTED] um 17:33:
> Is the online documentation for 4.0.x available? I was working with
> dates a while back. As it happens 4.1 added the very function I wanted.
> But that was not marked as added in 4.1, nor should be be as that would
> be an unbelieable amount of cruft.

As you know, version 5.0 is the development version currently available,
but some changes are still made on the production version 4.1, and
important fixes also go into 4.0.

There is only one MySQL manual, and it covers all versions. Changes are
(or at least: should be) labeled with the version(s) from which they
become effective, and there are separate sections listing the changes by
version.

This single manual is the source for all formats and publishing forms,
with the released packages as well as online.
(Aside: I am quite happy there are not several manuals, being maintained
in parallel, as this would introduce the risk of divergence, missing
corrections etc.)


> I would be nice however if maybe one level back was available if even if
> that was as a compressed file.

If by "level" you mean release families like 4.0, 4.1, and 5.0, this is
not possible with the approach of "one manual for all".
I understand your situation, that as a 4.0 user / admin you have to skip
over all items that refer to newer versions only, but I cannot offer an
easy solution.

All I can propose is to take the manual that came with the version you
downloaded and installed. It will at least not contain changes that were
introduced later.


The documentation team currently is changing the way the manual is
maintained, but I do not know whether this will introduce features (like
selective extraction) which would help you with your desires.


Regards,
Joerg

-- 
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]