duplicate entry (same time every day)

[Ronan McGlue]

i have a little quirck with my current Mysql setup. I log all messages 
from my mtas into Mysql and then use a simple query to generate stats 
from them... as below:


mysql select exim.hour,sum(exim.count),stats.spam,stats.ham from 
servers,exim,stats where exim.date=stats.day and exim.date =curdate() 
and exim.hour=stats.hour and exim.machine=servers.id and 
servers.type='MX'  group by exim.hour;

+--+-+--+--+
| hour | sum(exim.count) | spam | ham  |
+--+-+--+--+
|0 |1636 |  826 |  775 |
|1 |1243 |  708 |  515 |
|2 |1565 | 1018 |  536 |
|3 |2274 |  637 |  492 |
|4 |1325 |  760 |  547 |
|5 |1177 |  768 |  398 |
|6 |1266 |  718 |  530 |
|7 |1382 |  883 |  477 |
|8 |2026 |  899 | 1020 |
|9 |5856 |  838 | 1879 |
|   10 |  80 |   29 |   50 |
+--+-+--+--+
11 rows in set (0.01 sec)


however i have noticed that at hour 3 every day the DB duplicates the 
entries for the 3 MTAs... as below



mysql select servers.id,exim.hour,exim.count from servers,exim,stats 
where exim.hour=3 and exim.date=stats.day and exim.date =curdate() and 
exim.hour=stats.hour and exim.machine=servers.id and servers.type='MX';

++--+---+
| id | hour | count |
++--+---+
|  0 |3 |   819 |
|  0 |3 |   819 |
|  1 |3 |   189 |
|  1 |3 |   189 |
|  2 |3 |   129 |
|  2 |3 |   129 |
++--+---+
6 rows in set (0.00 sec)

hours 0-2 and 4-23 are fine and report everything as expected...


Now the data must be getting in somehow I just dont know how or why?
The inserts are done by an exim acl entry as follows..
 warn condition = ${lookup \
mysql {insert into exim SET \
   machine='MACHINE', \
   date='${substr_0_10:$tod_log}',\
   hour='${substr_11_2:$tod_log}'\
   ON DUPLICATE KEY UPDATE count = count + 1\
}{0}{0}}


anythoughts on the matter?

thanks
Ronan

--
Ronan McGlue
Analyst / Programmer
CMC Systems Group

Queens University Belfast

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

time stapstamp problem, when updating records.

[Gregory Machin]

Hi
I  have a problem I need to create recordes with a time stamp that is not
updated when the record is updated. Is this posible or will I have to
manualy create the timestamp using php and insert it when the record is
created ?

Thank you for your time.

--
Gregory Machin
[EMAIL PROTECTED]
[EMAIL PROTECTED]
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)

+27 72 524 8096

RE: Signal 11 crashes on MySQL V5

[Dave Pullin]

Ady,
INSERT into .. SELECT  .. would be the largest part of the workload. My
workload is a relatively small number of relatively large operations. Your
reported bug appears to be the other way around.

I am using MyISAM - very large quantities of data - can't afford, and don't
need, transactions.

For me, MySQL 5 on 2.4 seems to be less unstable than on 2.6, although I'm
getting TomCat hangs on 2.4, but that's another story and may be hiding
MySQL instability.

Dave

-Original Message-
From: Ady Wicaksono [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 08, 2006 11:38 PM
To: Dave Pullin
Cc: mysql@lists.mysql.com
Subject: Re: Signal 11 crashes on MySQL V5


Dave

What common transaction you have? INSERT? SELECT? UPDATE? DELETE? Are
you using InnoDB?

I have my personal experience with InnoDB and MySQL 5,
with kernel 2.4 (as you use on RedHat 9) MySQL 5 is unstable on high
load insert/update/delete
with kernel 2.6 (CentOS, Fedora, ...) you'll get more stability but it
still not stable enough.

Please see
http://forums.mysql.com/read.php?22,74279,74279#msg-74279

It has been confirmed as bug on MySQL 5.


Dave Pullin wrote:

I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently
upgraded
to V5 on all servers. Now MySQL is crashing regularly (several times per
day, some days) with 'got signal 11'.

My 3 Linux servers are very different machines running different software
a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386
, a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL
5.0.18-0.glibc23.x86_64
, a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386

The windows machines are not having a problem. All 6 are running
essentially
the same application.

It seems unlikely to be a hardware problem because its on 3 machines at
once. It looks like a MySQL V5 problem but I can't pin it down to anything
specific enough to report a bug.

Anyone had similar experiences with MySQL V5?

Dave







--
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: Aggregate functions in ORDER BY

[Dave Pullin]

I dont think it is possible although I have never searched hard for the
ability. It seems unlikely - since you get to name the columns in the table
and the columns in the query (using 'as'). I suggest you use one of those
abilities.
  -Original Message-
  From: Yasir Assam [mailto:[EMAIL PROTECTED]
  Sent: Wednesday, March 08, 2006 11:10 PM
  To: Dave Pullin
  Cc: mysql@lists.mysql.com
  Subject: Re: Aggregate functions in ORDER BY


  Many thanks for that Dave.

  Do you know whether it's possible for MySQL to return fully qualified
column names by default?

  For example, I'd like

  select * from foo;

  to return

  ++--+
  | foo.a  | foo.b|
  ++--+
  |  1 | Rex  |
  |  2 | Buddy|
  |  3 | Sam  |
  |  4 | Lucky|
  |  5 | Tiny |
  |  6 | Snoopy   |
  |  7 | Lady |
  |  8 | Tiger|
  |  9 | Speedy   |
  ++--+

  instead of

  ++--+
  | a  | b|
  ++--+
  |  1 | Rex  |
  |  2 | Buddy|
  |  3 | Sam  |
  |  4 | Lucky|
  |  5 | Tiny |
  |  6 | Snoopy   |
  |  7 | Lady |
  |  8 | Tiger|
  |  9 | Speedy   |
  ++--+


  Thanks,
  Yasir

All you are doing when you quote the expression like `COUNT(pets.pet_id)`
is referencing the column in the select by its default column name (which is
the same as the expression.)
That's why it only works when the expression is a column.

If you dont want the order by column to appear in the results, make your
select a subselect.

select a,b,c from (
 select a,b,c, COUNT(pets.pet_id) as count
   from etc
  order by count
) as subtable

Dave


-Original Message-
From: Yasir Assam [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 08, 2006 9:10 PM
To: mysql@lists.mysql.com
Subject: Aggregate functions in ORDER BY


Hello,

I noticed something strange in MySQL (I'm using 4.1.15).

If I use an aggregate function in the ORDER BY clause I get an error.

SELECT men.man_name,
   COUNT(pets.pet_id)
FROM men,
 pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY COUNT(pets.pet_id) DESC;

gives me the error:

ERROR  (HY000): Invalid use of group function

To get round this I can quote the ORDER BY column, e.g.

SELECT men.man_name,
   COUNT(pets.pet_id)
FROM men,
 pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `COUNT(pets.pet_id)` DESC;

This isn't an ideal solution though - in order for it to work I have to
put that expression in the SELECT column list. If I want to use an
aggregate function that isn't being displayed it just doesn't work
because it can't find that column:

SELECT men.man_name,
   COUNT(pets.pet_id)
FROM men,
 pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `AVG(pets.pet_age)` DESC;

gives the result:

ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause'

I don't want to have to put the expression I want to order by in the
SELECT column list - is there any way round this?

Below is the SQL code used to create the toy DB I'm using if anyone
wants to experiment:

Thanks,
Yasir


DROP TABLE IF EXISTS men;
CREATE TABLE men (
   man_id INT UNSIGNED PRIMARY KEY,
   man_name VARCHAR(255)
);

INSERT INTO men (man_id, man_name) VALUES
   (1, 'Alan'),
   (2, 'Bob'),
   (3, 'Colin');

DROP TABLE IF EXISTS pets;
CREATE TABLE pets (
   pet_id INT UNSIGNED PRIMARY KEY,
   pet_name VARCHAR(255),
   pet_man_id INT UNSIGNED,
   pet_age INT
);

INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES
   (1, 'Rex', 1, 5),
   (2, 'Buddy', 1, 10),
   (3, 'Sam', 1, 13),
   (4, 'Lucky', 1, 3),
   (5, 'Tiny', 1, 7),
   (6, 'Snoopy', 2, 4),
   (7, 'Lady', 2, 9),
   (8, 'Tiger', 2, 6),
   (9, 'Speedy', 3, 1);

Re: duplicate entry (same time every day)

[SGreen]

Ronan McGlue [EMAIL PROTECTED] wrote on 03/09/2006 05:05:15 AM:

 i have a little quirck with my current Mysql setup. I log all messages 
 from my mtas into Mysql and then use a simple query to generate stats 
 from them... as below:
 
 mysql select exim.hour,sum(exim.count),stats.spam,stats.ham from 
 servers,exim,stats where exim.date=stats.day and exim.date =curdate() 
 and exim.hour=stats.hour and exim.machine=servers.id and 
 servers.type='MX'  group by exim.hour;
 +--+-+--+--+
 | hour | sum(exim.count) | spam | ham  |
 +--+-+--+--+
 |0 |1636 |  826 |  775 |
 |1 |1243 |  708 |  515 |
 |2 |1565 | 1018 |  536 |
 |3 |2274 |  637 |  492 |
 |4 |1325 |  760 |  547 |
 |5 |1177 |  768 |  398 |
 |6 |1266 |  718 |  530 |
 |7 |1382 |  883 |  477 |
 |8 |2026 |  899 | 1020 |
 |9 |5856 |  838 | 1879 |
 |   10 |  80 |   29 |   50 |
 +--+-+--+--+
 11 rows in set (0.01 sec)
 
 
 however i have noticed that at hour 3 every day the DB duplicates the 
 entries for the 3 MTAs... as below
 
 
 mysql select servers.id,exim.hour,exim.count from servers,exim,stats 
 where exim.hour=3 and exim.date=stats.day and exim.date =curdate() and 
 exim.hour=stats.hour and exim.machine=servers.id and servers.type='MX';
 ++--+---+
 | id | hour | count |
 ++--+---+
 |  0 |3 |   819 |
 |  0 |3 |   819 |
 |  1 |3 |   189 |
 |  1 |3 |   189 |
 |  2 |3 |   129 |
 |  2 |3 |   129 |
 ++--+---+
 6 rows in set (0.00 sec)
 
 hours 0-2 and 4-23 are fine and report everything as expected...
 
 
 Now the data must be getting in somehow I just dont know how or why?
 The inserts are done by an exim acl entry as follows..
   warn condition = ${lookup \
  mysql {insert into exim SET \
 machine='MACHINE', \
 date='${substr_0_10:$tod_log}',\
 hour='${substr_11_2:$tod_log}'\
 ON DUPLICATE KEY UPDATE count = count + 1\
  }{0}{0}}
 
 
 anythoughts on the matter?
 
 thanks
 Ronan
 
 -- 
 Ronan McGlue
 Analyst / Programmer
 CMC Systems Group
 
 Queens University Belfast
 

Do you have two entries in `stats` that correspond to hour 3?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: replication after editing bin logs

[Scott Tanner]

Can you post the failed SQL statement from your altered bin log, and
maybe the preceding commands related to that table?


Scott Tanner
AMi Entertainment.net



On Thu, 2006-03-09 at 08:28, Goldblatt, Eric wrote: 
  Scott,
 
 The table already existed before the binlog. The strange thing is that
 there were inserts into this table earlier in the binlog, and they did
 not produce any error messages.
 
 
 Eric 
 -Original Message-
 From: Scott Tanner [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, March 08, 2006 3:46 PM
 To: Goldblatt, Eric
 Subject: Re: replication after editing bin logs
 
 On Wed, 2006-03-08 at 14:51, Goldblatt, Eric wrote:
 snip-
  When I came to step 5, I submitted the SQL text file as a batch job:
  
  mysql -u root -p -e source binlogs_045_052_parsed
  
  After many hours I received the following error message:
  
  ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
  Table 'AB4539p2' is read only
  
  At this point, the mysql batch job terminated.
 
   If its saying the table is read only, it could be a simple permissions
 issue. Is this table being created from the binlog, or is it an existing
 table?
 
  
 Regards,
 
 Scott Tanner
 AMi Entertainment



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

Re: duplicate entry (same time every day)

[Ronan McGlue]

[EMAIL PROTECTED] wrote:



Ronan McGlue [EMAIL PROTECTED] wrote on 03/09/2006 05:05:15 AM:

  i have a little quirck with my current Mysql setup. I log all messages
  from my mtas into Mysql and then use a simple query to generate stats
  from them... as below:
 
  mysql select exim.hour,sum(exim.count),stats.spam,stats.ham from
  servers,exim,stats where exim.date=stats.day and exim.date =curdate()
  and exim.hour=stats.hour and exim.machine=servers.id and
  servers.type='MX'  group by exim.hour;
  +--+-+--+--+
  | hour | sum(exim.count) | spam | ham  |
  +--+-+--+--+
  |0 |1636 |  826 |  775 |
  |1 |1243 |  708 |  515 |
  |2 |1565 | 1018 |  536 |
  |3 |2274 |  637 |  492 |
  |4 |1325 |  760 |  547 |
  |5 |1177 |  768 |  398 |
  |6 |1266 |  718 |  530 |
  |7 |1382 |  883 |  477 |
  |8 |2026 |  899 | 1020 |
  |9 |5856 |  838 | 1879 |
  |   10 |  80 |   29 |   50 |
  +--+-+--+--+
  11 rows in set (0.01 sec)
 
 
  however i have noticed that at hour 3 every day the DB duplicates the
  entries for the 3 MTAs... as below
 
 
  mysql select servers.id,exim.hour,exim.count from servers,exim,stats
  where exim.hour=3 and exim.date=stats.day and exim.date =curdate() and
  exim.hour=stats.hour and exim.machine=servers.id and servers.type='MX';
  ++--+---+
  | id | hour | count |
  ++--+---+
  |  0 |3 |   819 |
  |  0 |3 |   819 |
  |  1 |3 |   189 |
  |  1 |3 |   189 |
  |  2 |3 |   129 |
  |  2 |3 |   129 |
  ++--+---+
  6 rows in set (0.00 sec)
 
  hours 0-2 and 4-23 are fine and report everything as expected...
 
 
  Now the data must be getting in somehow I just dont know how or why?
  The inserts are done by an exim acl entry as follows..
warn condition = ${lookup \
   mysql {insert into exim SET \
  machine='MACHINE', \
  date='${substr_0_10:$tod_log}',\
  hour='${substr_11_2:$tod_log}'\
  ON DUPLICATE KEY UPDATE count = count + 1\
   }{0}{0}}
 
 
  anythoughts on the matter?
 
  thanks
  Ronan
 
  --
  Ronan McGlue
  Analyst / Programmer
  CMC Systems Group
 
  Queens University Belfast
 

Do you have two entries in `stats` that correspond to hour 3?
well yes, thats the problem for hour=3 there are duplicate entries for 
my 3 mail MX machines...


the inserts are done on the fly whenever a mail is accepted to our 
domain as explained, by an exim acl which increments the hours count for 
mail accepted from that specific machine.


I just dont understand why it is being duplicated at the hour of 3 o clock.



Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



--
Ronan McGlue
Analyst / Programmer
CMC Systems Group

Queens University Belfast

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

RE: Charset questions

[Ryan Stille]

Are my emails not coming through?  Or is this question way too 'newbie'
and no one wants to touch it?

-Ryan

Ryan Stille wrote:
 I'm still hoping someone can else can share their input on this.
 
 What do other people usually do as far as the collation setting?
 
 Thanks,
 -Ryan
 
 Ryan Stille wrote:
 When we migrated to MySQL from MS SQL, I left everything set to the
 default as far as collations - latin1_swedish_ci.
 This was based on digging through the manual and google. But now I am
 migrating the application to a newer version of ColdFusion and am
 running into some issues with charsets on some ColdFusion functions.
 I am wondering if my database charset has anything to do with it.
 
 What do other people usually do as far as the collation setting?
 
 We are in the US, but do have a few sites that make use of German
 and Spanish characters. 
 
 Thanks,
 -Ryan



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

Re: Need help with a Basic Query.

[Peter Brawley]

clint lenard wrote:

Hey Guys,

  I was wondering if I could get some assistance with building a
Simple Import Script using PHP and MySQL. Basically I'm trying to pull
info out of one Table and Insert it into the other Table.

Can anyone show me a simple example of this? I can figure out how to
do the rest if I had a simple example.
  

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

PB


  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006


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

RE: Charset questions

[SGreen]

Ryan Stille [EMAIL PROTECTED] wrote on 03/09/2006 09:59:32 AM:

 Are my emails not coming through?  Or is this question way too 'newbie'
 and no one wants to touch it?
 
 -Ryan
 
 Ryan Stille wrote:
  I'm still hoping someone can else can share their input on this.
  
  What do other people usually do as far as the collation setting?
  
  Thanks,
  -Ryan
  
  Ryan Stille wrote:
  When we migrated to MySQL from MS SQL, I left everything set to the
  default as far as collations - latin1_swedish_ci.
  This was based on digging through the manual and google. But now I am
  migrating the application to a newer version of ColdFusion and am
  running into some issues with charsets on some ColdFusion functions.
  I am wondering if my database charset has anything to do with it.
  
  What do other people usually do as far as the collation setting?
  
  We are in the US, but do have a few sites that make use of German
  and Spanish characters. 
  
  Thanks,
  -Ryan
 
 

I don't think that your question was too newbie. I believe that within the 
world of MySQL that changing charsets and collations is still something of 
a dark art. Not many users need something other than the default settings 
and those that do experiment with the various charsets and collations 
until they find one that works for them.

There have been frequent posts on this list looking for help in setting up 
a character set or trying to resolve why certain characters no longer 
appear as they were when they went into the database.  The basic thing 
to remember is that each and every communications channel can have it's 
OWN charset setting. If you are pushing data in using utf-8 and you are 
pulling data out through a connection using UCS-2, you may run into a few 
translation errors (that was just an example, I don't know if they are 
incompatible or not)

I think that the default charset and collation covers most or all of the 
european alphabets so you may not need to use anything but the defaults 
for English, Spanish, Italian, Portuguese, French, German, and the Nordic 
languages (I can't name them all, sorry to those who live there).  I have 
to admit that I haven't had to delve deeply into this subject so I cannot 
speak from deep experience but that's basically what I have gathered by 
lurking on the other posts dealing with this topic.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

InnoDB Indices

[Robert DiFalco]

I have some questions regarding InnoDB indices.
 
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
 
Do queries benefit from an index with this low of a selectivity?
 
If through the query browser I find that they do, should I increase the
selectivity of the index by making it a compound index and adding my
Primary Key to the TYPE index?
 
If I make it a compound index, does it have any positive impact on
INSERT, UPDATE, or maybe just DELETE?
 
R.


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

Re: Database Corruption and MySQL

[Atle Veka]

To restore specific tables (or full databases) I often use this trick (I'm
just writing this down from memory, you would want to run it through a
test environment first). It runs something like this:

1) MASTER: query FLUSH TABLES WITH READ LOCK;
   query SHOW MASTER STATUS\G

2) SLAVE:  query SHOW SLAVE STATUS\G # repeat until caught up with master
   query FLUSH TABLES WITH READ LOCK;

3) MASTER: copy table or database to local disk or directly to slave
   query UNLOCK TABLES; # master is operational again

4) SLAVE:  copy new tables into database
   query UNLOCK TABLES;


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Tue, 7 Mar 2006, Michael Jeung wrote:

 Hi Folks,

 We've got a Single Master/Multiple Slave environment.
 Recently, we had some corruption on one of the slaves and I had to
 repair the affected tables.

 After the repair completed, some of the rows on the slave had been
 deleted - so the Master and the Slave weren't exactly in synch.  The
 slave was missing some records.   I verified this myself with a
 select count(*) on the table.

 Now, I know this is the normal way for MySQL to repair it's database.

 However, what I'd like to do is restore these missing rows to the
 Slave without doing a dump and reload from my backups.  Is this
 possible?

 The way that we normally approach this problem is to take an
 unaffected slave and copy the mysql directory from an unaffected
 slave over to the corrupt slave.  Then we restart MySQL on the
 corrupt slave and things work out without too much difficulty.
 However, this has always seemed like a terrible way to restore a
 corrupt slave to me.  Is there a more elegant way to do it?

 Regards,
 Michael Jeung



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

php and mysql

[Mary Adel]

I have a severe problem that php5 cannot connect to mysql and i don't
know why 
also i am using linux
here is my peice of code
:?php
print hi;
mysql_connect('localhost', '', '') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if their is any configuration please tell me

thanks,
mary 


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

Re: InnoDB Indices

[SGreen]

Robert DiFalco [EMAIL PROTECTED] wrote on 03/09/2006 12:32:44 PM:

 I have some questions regarding InnoDB indices.
 
 Say I have a table with millions of records. On of the fields is a type
 field that has a possible value of 1,2,3, or 4. I sometimes query by the
 type field and may at other times order on it.
 
 Do queries benefit from an index with this low of a selectivity?
 
 If through the query browser I find that they do, should I increase the
 selectivity of the index by making it a compound index and adding my
 Primary Key to the TYPE index?
 
 If I make it a compound index, does it have any positive impact on
 INSERT, UPDATE, or maybe just DELETE?
 
 R.
 
 

Hi Robert,

I assume you have already read through the entire optimization section in 
the manual:
http://dev.mysql.com/doc/refman/4.1/en/optimization.html
-or-
http://dev.mysql.com/doc/refman/5.0/en/optimization.html
(as appropriate)

as this entire chapter deals with the finer details of the questions you 
are asking. So I will try to just answer you in the general sense so that 
perhaps you can make better sense of what you already read.

One of the most frequent bottlenecks to query performance is related to 
physically retrieving data from the hard disks. Indexes, though very 
useful, actually slow down performance if you need to pull more than about 
1/3 of any table's rows off of the disks. Columns of data not already 
included in the indexes themselves must be retrieved from the disk before 
their values can be used as part of a result or a part of a comparison or 
as part of a formula.  What happens to the performance is that it begins 
to take longer (mostly due to the random access disk seek operations) to 
pick lots of individual records (based on an index hit) that it would have 
taken if you had just found the beginning of the table data and streamed 
the whole table through memory in one big burst of data.

Now, because the indexes to a table are loaded into memory before query 
evaluation the optimizer can estimate how many rows of a table it would 
find if it used one index over another (and starting with 5.0 how many it 
would find based on certain combinations of indexes). If all of the data 
you need from a table is actually part of an index then the entire read 
data from disk portion of the query can also be skipped (under the right 
conditions). So having what is known as a covering index (a multi-column 
index where some of the right-most columns are listed mostly to avoid 
actual table reads) can seriously improve the performance of certain 
queries while providing a normal boost to the performance of a bunch of 
others.

How you execute your queries should not matter. By the time the database 
server sees it, one query looks just like every other. So I don't see how 
running a query in the Query Browser would be any faster than if you had 
issued the same query from the CLI or via any of the other connection 
APIs.

Declaring compound indexes make sense if your query patterns frequently 
use those columns or if you are trying to create a covering index. 
Improving the cardinality of any index can only help its performance.

The up-side to indexes (keys) is that they improve the chances of quickly 
finding the data you are looking for thereby improving your overall query 
performance. The down-side is that the addition of every new record has to 
create the appropriate additions to every index on the table. The same 
goes for UPDATES and DELETES. You have to benchmark your performance in 
your environment to figure out when enough indexes becomes too many for 
your comfort. Each new index also takes up space in memory and room on the 
disk so too many indexes can starve your system for resources, too.

Like I said above, these are just some general guidelines. The nitty 
gritty can be found in the manual.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

key_buffer_size and memory used by mysqld

[Denis Solovyov]

Please  explain  to  me how mysqld's key buffer uses memory under Linux
(kernel 2.4, glibc 2.3, mysql 4.1).

I'd  want  to  know  for  sure  if  every  mysqld thread  (forked upon an
incoming connection) uses its own memory for key buffer (key_buffer_size)
or key buffer is common for all mysql threads.

When `ps aux` shows the something like

root  2012  0.0  0.2  2712 1260 ?SMar09   0:00 /bin/sh 
/usr/local/bin/mysqld_safe ...
mysql 2062  0.0  3.4 33836 17756 ?   SMar09   0:02 mysqld ...
mysql 2063  0.0  3.4 33836 17756 ?   SMar09   0:02 mysqld ...
mysql 2064  0.0  3.4 33836 17756 ?   SMar09   0:00 mysqld ...
mysql14596  0.0  3.4 33836 17756 ?   S01:58   0:00 mysqld ...
mysql14598  0.0  3.4 33836 17756 ?   S01:58   0:00 mysqld ...
mysql14599  0.0  3.4 33836 17756 ?   S01:59   0:00 mysqld ...

does  it  mean  that every thread allocates its own megs of memory?  What
will  happen  if there will be VERY many connections?  Will key buffers
eat  all the memory quickly as the number of connections will grow,  or I
misunderstand  `ps`  output and probably anything else?  Do I have to set
key_buffer_size  to  a pretty small value if I expect many simultaneous
connections?

Denis Solovyov



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

Re: InnoDB Indices

[David Turner]

- Original Message 
From: Robert DiFalco [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 9, 2006 9:32:44 AM
Subject: InnoDB Indices

I have some questions regarding InnoDB indices.
 
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
 
Do queries benefit from an index with this low of a selectivity?
 
 ++ For the most part no. I come from Oracle where you can use histograms to 
help. So, someone feel free to correct me if I'm wrong.
 
If through the query browser I find that they do, should I increase the
selectivity of the index by making it a compound index and adding my
Primary Key to the TYPE index?
 
 ++ If your primary key will be included in the where clause then definitely 
include it.
 
If I make it a compound index, does it have any positive impact on
INSERT, UPDATE, or maybe just DELETE?
 
 ++ I can't see it helping with insert, but depending on the where clause on 
your updates and deletes it could.
 
 Dave
 
R.


--
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: replication after editing bin logs

[Atle Veka]

Do you have control over the DELETE queries? If so I would look into the
option of using 'SQL_LOG_BIN':

http://dev.mysql.com/doc/refman/5.0/en/set-option.html
SQL_LOG_BIN = {0 | 1}
If set to 0, no logging is done to the binary log for the client.
The client must have the SUPER privilege to set this option.

This means that you can run the DELETE queries on the master only, leaving
the slave untouched.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Wed, 8 Mar 2006, Goldblatt, Eric wrote:

 Hello,


 Another division in my organization is maintaining a mysql database. For
 various reasons, data is deleted from this database after it has aged
 more than a month. My division has a need for long term storage of the
 same data, so I am trying the following strategy:

 1. Create a snapshot (slave) of the master database.
 2. Enable binary logging on the master database.
 3. Periodically, run the binary logs through the mysqlbinlog utility to
 produce SQL text.
 4. Parse the SQL text to remove DELETE's of aged data.
 5. Apply the SQL text to the snapshot (slave).

 Now data older than one month is being saved in the slave.


 When I came to step 5, I submitted the SQL text file as a batch job:

 mysql -u root -p -e source binlogs_045_052_parsed

 After many hours I received the following error message:

 ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
 Table 'AB4539p2' is read only

 At this point, the mysql batch job terminated.


 Has anyone come across a similar error under similar circumstances?


 Some background:

 I am running mysql 5.0 on a Windows 2003 server. The SQL text file,
 binlogs_045_052_parsed, contains only three kinds of statements:
 millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a few
 CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into about
 5000 tables in the database. Before I received the error, many inserts
 into table 'AB4539p2' succeeded without any error.


 I have a few more basic questions:

 1. Will all the LOAD DATA LOCAL INFILES submitted before the error have
 been committed, or will all the statements from the batch job have
 rolled back once the error occurred? (I want to know if I need to
 resubmit all of the file binlogs_045_052_parsed to mysql, or just the
 portion from the error.)

 2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning
 and end of the SQL text file. I don't understand the purpose of these
 ROLLBACKs.



 Thank you,

 Eric






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

RE: php and mysql

[fbsd_user]

You need a login id and password unless this is test DB added under
ID root
You have to use the same login id as the one you created the
db/table with.

mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())

mysql_connect('localhost', 'root') or die(mysql_error())


-Original Message-
From: Mary Adel [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 3:06 PM
To: mysql@lists.mysql.com
Subject: php and mysql


I have a severe problem that php5 cannot connect to mysql and i
don't
know why
also i am using linux
here is my peice of code
:?php
print hi;
mysql_connect('localhost', '', '') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if their is any configuration please tell me

thanks,
mary


--
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]

InnoiDB Backups

[Alan Fisher]

All,

I would like to knew if anyone knows of a way to automate innoDB Hot Backups
of several databases that can be set to occur at off hours. Also, is it
possible to run several backups at one time. I am using MySQL 4.1.x on a
Solaris system.

Thanks,
Alan Fisher


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

RE: InnoDB Indices

[Robert DiFalco]

 
 ++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.

I guess I was thinking that if an index with otherwise low selectivity
added a rightmost column that was completely unique that it would
improve key distribution and therefore make deletes faster. But every
database engine handles this stuff differently.

R 

-Original Message-
From: David Turner [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 09, 2006 10:13 AM
To: Robert DiFalco; mysql@lists.mysql.com
Subject: Re: InnoDB Indices



- Original Message 
From: Robert DiFalco [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 9, 2006 9:32:44 AM
Subject: InnoDB Indices

I have some questions regarding InnoDB indices.
 
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
 
Do queries benefit from an index with this low of a selectivity?
 
 ++ For the most part no. I come from Oracle where you can use
histograms to help. So, someone feel free to correct me if I'm wrong.
 
If through the query browser I find that they do, should I increase the
selectivity of the index by making it a compound index and adding my
Primary Key to the TYPE index?
 
 ++ If your primary key will be included in the where clause then
definitely include it.
 
If I make it a compound index, does it have any positive impact on
INSERT, UPDATE, or maybe just DELETE?
 
 ++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.
 
 Dave
 
R.


--
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: key_buffer_size and memory used by mysqld

[Kishore Jalleda]

This would clear your doubt about which memory is shared by all threads and
which memory is used by individual threads ..

min_memory_needed_by_mysql  = (global_buffers + ((thread_buffers )*
max_connections)))

where thread_buffers includes the following:

sort_buffer
myisam_sort_buffer
read_buffer
join_buffer
read_rnd_buffer

   and global_buffers includes:

key_buffer
innodb_buffer_pool
innodb_log_buffer
innodb_additional_mem_pool
net_buffer

Also its very easy to set key_buffer_size , just add up the total size of
your indexes in the mysql data dir ( *.MYI files) and set the key buffer
size approx to that, so that all your keys are in memory also you can check
your key efficiency using mytop to make any changes...

Kishore Jalleda


On 3/9/06, Denis Solovyov [EMAIL PROTECTED] wrote:


 Please  explain  to  me how mysqld's key buffer uses memory under Linux
 (kernel 2.4, glibc 2.3, mysql 4.1).

 I'd  want  to  know  for  sure  if  every  mysqld thread  (forked upon an
 incoming connection) uses its own memory for key buffer (key_buffer_size)
 or key buffer is common for all mysql threads.

 When `ps aux` shows the something like

 root  2012  0.0  0.2  2712 1260 ?SMar09   0:00 /bin/sh
 /usr/local/bin/mysqld_safe ...
 mysql 2062  0.0  3.4 33836 17756 ?   SMar09   0:02 mysqld ...
 mysql 2063  0.0  3.4 33836 17756 ?   SMar09   0:02 mysqld ...
 mysql 2064  0.0  3.4 33836 17756 ?   SMar09   0:00 mysqld ...
 mysql14596  0.0  3.4 33836 17756 ?   S01:58   0:00 mysqld ...
 mysql14598  0.0  3.4 33836 17756 ?   S01:58   0:00 mysqld ...
 mysql14599  0.0  3.4 33836 17756 ?   S01:59   0:00 mysqld ...

 does  it  mean  that every thread allocates its own megs of memory?  What
 will  happen  if there will be VERY many connections?  Will key buffers
 eat  all the memory quickly as the number of connections will grow,  or I
 misunderstand  `ps`  output and probably anything else?  Do I have to set
 key_buffer_size  to  a pretty small value if I expect many simultaneous
 connections?

 Denis Solovyov



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

Re: php and mysql

[Peter Brawley]

Mary Adel wrote:

I have a severe problem that php5 cannot connect to mysql and i don't
know why 
also i am using linux

here is my peice of code
:?php
print hi;
mysql_connect('localhost', '', '') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if their is any configuration please tell me
  
PHP wants a username and password. For web pages, it's good practice to 
create

a mysql user just for the db access required by your pages.

PB

-

thanks,
mary 



  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006


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

RE: replication after editing bin logs

[Goldblatt, Eric]

Atle,

No, unfortunately we have no control over the DELETE queries. The master
mysql database is the back end for an application which we cannot touch.


Eric 


-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 09, 2006 1:35 PM
To: Goldblatt, Eric
Cc: mysql@lists.mysql.com
Subject: Re: replication after editing bin logs

Do you have control over the DELETE queries? If so I would look into the
option of using 'SQL_LOG_BIN':

http://dev.mysql.com/doc/refman/5.0/en/set-option.html
SQL_LOG_BIN = {0 | 1}
If set to 0, no logging is done to the binary log for the
client.
The client must have the SUPER privilege to set this option.

This means that you can run the DELETE queries on the master only,
leaving the slave untouched.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Wed, 8 Mar 2006, Goldblatt, Eric wrote:

 Hello,


 Another division in my organization is maintaining a mysql database. 
 For various reasons, data is deleted from this database after it has 
 aged more than a month. My division has a need for long term storage 
 of the same data, so I am trying the following strategy:

 1. Create a snapshot (slave) of the master database.
 2. Enable binary logging on the master database.
 3. Periodically, run the binary logs through the mysqlbinlog utility 
 to produce SQL text.
 4. Parse the SQL text to remove DELETE's of aged data.
 5. Apply the SQL text to the snapshot (slave).

 Now data older than one month is being saved in the slave.


 When I came to step 5, I submitted the SQL text file as a batch job:

 mysql -u root -p -e source binlogs_045_052_parsed

 After many hours I received the following error message:

 ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
 Table 'AB4539p2' is read only

 At this point, the mysql batch job terminated.


 Has anyone come across a similar error under similar circumstances?


 Some background:

 I am running mysql 5.0 on a Windows 2003 server. The SQL text file, 
 binlogs_045_052_parsed, contains only three kinds of statements:
 millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a 
 few CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into 
 about 5000 tables in the database. Before I received the error, many 
 inserts into table 'AB4539p2' succeeded without any error.


 I have a few more basic questions:

 1. Will all the LOAD DATA LOCAL INFILES submitted before the error 
 have been committed, or will all the statements from the batch job 
 have rolled back once the error occurred? (I want to know if I need to

 resubmit all of the file binlogs_045_052_parsed to mysql, or just the 
 portion from the error.)

 2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning

 and end of the SQL text file. I don't understand the purpose of these 
 ROLLBACKs.



 Thank you,

 Eric






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

RE: php and mysql

[Mary Adel]

Thanks for al your help and i di that and now i have another error 

Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)
my code is as follows:

?php
print hi;
mysql_connect('localhost', 'root','') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if u can help in this i ll appreciate that a lot
 

On Thu, 2006-03-09 at 13:37 -0500, fbsd_user wrote:
 You need a login id and password unless this is test DB added under
 ID root
 You have to use the same login id as the one you created the
 db/table with.
 
 mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())
 
 mysql_connect('localhost', 'root') or die(mysql_error())
 
 
 -Original Message-
 From: Mary Adel [mailto:[EMAIL PROTECTED]
 Sent: Thursday, March 09, 2006 3:06 PM
 To: mysql@lists.mysql.com
 Subject: php and mysql
 
 
 I have a severe problem that php5 cannot connect to mysql and i
 don't
 know why
 also i am using linux
 here is my peice of code
 :?php
 print hi;
 mysql_connect('localhost', '', '') or die(mysql_error());
 echo Connected to MySQLbr /;
 print connected;
 ?
 if their is any configuration please tell me
 
 thanks,
 mary
 
 
 --
 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: replication after editing bin logs

[Goldblatt, Eric]

Scott,

There were 257 commands related to the table before the error. They all
look the same.
Below are the last two (256th and 257th), followed by the one that
caused the read-only error.


LOAD DATA LOCAL INFILE
'f:\\Staging\\infiles1\\4539_1140709500_1140709923.in_process-1ccede-0'
INTO TABLE `AB4539p2` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY
'\\' LINES TERMINATED BY '\n' STARTING BY '' (ID,TimeStamp,In,Out);

LOAD DATA LOCAL INFILE
'f:\\Staging\\infiles1\\4539_1140712200_1140713975.in_process-1cebd5-0'
INTO TABLE `AB4539p2` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY
'\\' LINES TERMINATED BY '\n' STARTING BY '' (ID,TimeStamp,In,Out);

LOAD DATA LOCAL INFILE
'f:\\Staging\\infiles1\\4539_1140714000_1140714722.in_process-1d187c-0'
INTO TABLE `AB4539p2` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY
'\\' LINES TERMINATED BY '\n' STARTING BY '' (ID,TimeStamp,In,Out);


If I had to guess what happened, I'd say the database put a lock on the
table during the 257th LOAD DATA, and then didn't release it by the time
the 258th command was executed.


Eric 


-Original Message-
From: Scott Tanner [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 09, 2006 9:38 AM
To: Goldblatt, Eric
Cc: Mysql User List
Subject: Re: replication after editing bin logs

Can you post the failed SQL statement from your altered bin log, and
maybe the preceding commands related to that table?


Scott Tanner
AMi Entertainment.net



On Thu, 2006-03-09 at 08:28, Goldblatt, Eric wrote: 
  Scott,
 
 The table already existed before the binlog. The strange thing is that

 there were inserts into this table earlier in the binlog, and they did

 not produce any error messages.
 
 
 Eric
 -Original Message-
 From: Scott Tanner [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, March 08, 2006 3:46 PM
 To: Goldblatt, Eric
 Subject: Re: replication after editing bin logs
 
 On Wed, 2006-03-08 at 14:51, Goldblatt, Eric wrote:
 snip-
  When I came to step 5, I submitted the SQL text file as a batch job:
  
  mysql -u root -p -e source binlogs_045_052_parsed
  
  After many hours I received the following error message:
  
  ERROR 1036 (HY000) at line 1387471 in file:
'binlogs_045_052_parsed':
  Table 'AB4539p2' is read only
  
  At this point, the mysql batch job terminated.
 
   If its saying the table is read only, it could be a simple 
 permissions issue. Is this table being created from the binlog, or is 
 it an existing table?
 
  
 Regards,
 
 Scott Tanner
 AMi Entertainment



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

RE: replication after editing bin logs

[Burke, Dan]

I'm not sure what version of MySQL you're running, but might it be
possible to put a trigger on the tables in the slave DB to insert
deleted records into another table?  

I've done such things for large tables that need trimmed from time to
time, but I need to save the old data for archival.  But I haven't done
this in a replication situation.

Dan.


-Original Message-
From: Goldblatt, Eric [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 09, 2006 2:45 PM
To: Atle Veka
Cc: mysql@lists.mysql.com
Subject: RE: replication after editing bin logs


Atle,

No, unfortunately we have no control over the DELETE queries. The master
mysql database is the back end for an application which we cannot touch.


Eric 


-Original Message-
From: Atle Veka [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 09, 2006 1:35 PM
To: Goldblatt, Eric
Cc: mysql@lists.mysql.com
Subject: Re: replication after editing bin logs

Do you have control over the DELETE queries? If so I would look into the
option of using 'SQL_LOG_BIN':

http://dev.mysql.com/doc/refman/5.0/en/set-option.html
SQL_LOG_BIN = {0 | 1}
If set to 0, no logging is done to the binary log for the
client.
The client must have the SUPER privilege to set this option.

This means that you can run the DELETE queries on the master only,
leaving the slave untouched.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Wed, 8 Mar 2006, Goldblatt, Eric wrote:

 Hello,


 Another division in my organization is maintaining a mysql database. 
 For various reasons, data is deleted from this database after it has 
 aged more than a month. My division has a need for long term storage 
 of the same data, so I am trying the following strategy:

 1. Create a snapshot (slave) of the master database.
 2. Enable binary logging on the master database.
 3. Periodically, run the binary logs through the mysqlbinlog utility 
 to produce SQL text.
 4. Parse the SQL text to remove DELETE's of aged data.
 5. Apply the SQL text to the snapshot (slave).

 Now data older than one month is being saved in the slave.


 When I came to step 5, I submitted the SQL text file as a batch job:

 mysql -u root -p -e source binlogs_045_052_parsed

 After many hours I received the following error message:

 ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
 Table 'AB4539p2' is read only

 At this point, the mysql batch job terminated.


 Has anyone come across a similar error under similar circumstances?


 Some background:

 I am running mysql 5.0 on a Windows 2003 server. The SQL text file, 
 binlogs_045_052_parsed, contains only three kinds of statements:
 millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a 
 few CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into 
 about 5000 tables in the database. Before I received the error, many 
 inserts into table 'AB4539p2' succeeded without any error.


 I have a few more basic questions:

 1. Will all the LOAD DATA LOCAL INFILES submitted before the error 
 have been committed, or will all the statements from the batch job 
 have rolled back once the error occurred? (I want to know if I need to

 resubmit all of the file binlogs_045_052_parsed to mysql, or just the 
 portion from the error.)

 2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning

 and end of the SQL text file. I don't understand the purpose of these 
 ROLLBACKs.



 Thank you,

 Eric






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


--
This message has been scanned for viruses by TechTeam's email gateway.


--
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).

This message has been scanned for viruses by TechTeam's email gateway.


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

Re: InnoDB Indices

[Heikki Tuuri]

Robert,

actually, InnoDB always internally adds the PRIMARY KEY to every secondary 
index record:


http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html

If a column has just four different values, then in most cases an index on 
that column does not help at all. And every index slows down inserts. That 
is why you normally do not create an index on such a column.


But a query of the following type would get a speedup, assuming that the 
index tree completely fits in the buffer pool (main memory):


SELECT COUNT(*) FROM t WHERE low_selectivity_column = 2;

The speedup would be 4X compared to a table scan.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


- Original Message - 
From: Robert DiFalco [EMAIL PROTECTED]

Newsgroups: mailing.database.myodbc
Sent: Thursday, March 09, 2006 8:41 PM
Subject: RE: InnoDB Indices



=20
++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.

I guess I was thinking that if an index with otherwise low selectivity
added a rightmost column that was completely unique that it would
improve key distribution and therefore make deletes faster. But every
database engine handles this stuff differently.

R=20

-Original Message-
From: David Turner [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 10:13 AM
To: Robert DiFalco; mysql@lists.mysql.com
Subject: Re: InnoDB Indices



- Original Message 
From: Robert DiFalco [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 9, 2006 9:32:44 AM
Subject: InnoDB Indices

I have some questions regarding InnoDB indices.
=20
Say I have a table with millions of records. On of the fields is a type
field that has a possible value of 1,2,3, or 4. I sometimes query by the
type field and may at other times order on it.
=20
Do queries benefit from an index with this low of a selectivity?
=20
++ For the most part no. I come from Oracle where you can use
histograms to help. So, someone feel free to correct me if I'm wrong.
=20
If through the query browser I find that they do, should I increase the
selectivity of the index by making it a compound index and adding my
Primary Key to the TYPE index?
=20
++ If your primary key will be included in the where clause then
definitely include it.
=20
If I make it a compound index, does it have any positive impact on
INSERT, UPDATE, or maybe just DELETE?
=20
++ I can't see it helping with insert, but depending on the where
clause on your updates and deletes it could.
=20
Dave
=20
R.


--
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]





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

Re: InnoiDB Backups

[Heikki Tuuri]

Alan,

- Original Message - 
From: Alan Fisher [EMAIL PROTECTED]

Newsgroups: mailing.database.myodbc
Sent: Thursday, March 09, 2006 8:38 PM
Subject: InnoiDB Backups



All,

I would like to knew if anyone knows of a way to automate innoDB Hot 
Backups

of several databases that can be set to occur at off hours.


I think people create cron jobs to run at night. If I understood right, you 
have several separate  MySQL installations in one computer. Maybe one cron 
job for each installation would work?


InnoDB Hot Backup takes as the input the my.cnf file of the mysqld server, 
and a backup-my.cnf file that specifies where the data is copied. You must 
be very careful not to mix these .cnf files for different mysqld servers. 
And make sure there is enough free disk space for all the backups.



Also, is it
possible to run several backups at one time. I am using MySQL 4.1.x on a
Solaris system.


Yes, you can run several instances of ibbackup at the same time.


Thanks,
Alan Fisher


Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


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

RE: php and mysql

[JC]

On Thu, 9 Mar 2006, Mary Adel wrote:


Thanks for al your help and i di that and now i have another error

Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)
my code is as follows:

?php
print hi;
mysql_connect('localhost', 'root','') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if u can help in this i ll appreciate that a lot


On Thu, 2006-03-09 at 13:37 -0500, fbsd_user wrote:

You need a login id and password unless this is test DB added under
ID root
You have to use the same login id as the one you created the
db/table with.

mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())

mysql_connect('localhost', 'root') or die(mysql_error())


-Original Message-
From: Mary Adel [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 09, 2006 3:06 PM
To: mysql@lists.mysql.com
Subject: php and mysql


I have a severe problem that php5 cannot connect to mysql and i
don't
know why
also i am using linux
here is my peice of code
:?php
print hi;
mysql_connect('localhost', '', '') or die(mysql_error());
echo Connected to MySQLbr /;
print connected;
?
if their is any configuration please tell me

thanks,
mary


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








--

hi mary,

take a look at 
http://www.stanton-finley.net/fedora_core_5_installation_notes.html#MySQL.


basically, you need to make sure that your mysql server accepts local 
connection.


if you login to your mysql server:
mysql -u root (if no password for root) or
mysql -u root -p (if there is pw for root)

and then:
select user,host,password from mysql.user;

if you don't see an entry for root  localhost, then you need to add it 
in.


hope that help.
JC

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

RE: php and mysql

[Logan, David (SST - Adelaide)]

 
If you still have issues after that, then read
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: JC [mailto:[EMAIL PROTECTED] 
Sent: Friday, 10 March 2006 6:42 AM
To: Mary Adel
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: php and mysql

On Thu, 9 Mar 2006, Mary Adel wrote:

 Thanks for al your help and i di that and now i have another error

 Can't connect to local MySQL server through socket
 '/var/lib/mysql/mysql.sock' (2)
 my code is as follows:

 ?php
 print hi;
 mysql_connect('localhost', 'root','') or die(mysql_error());
 echo Connected to MySQLbr /;
 print connected;
 ?
 if u can help in this i ll appreciate that a lot


 On Thu, 2006-03-09 at 13:37 -0500, fbsd_user wrote:
 You need a login id and password unless this is test DB added under
 ID root
 You have to use the same login id as the one you created the
 db/table with.

 mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())

 mysql_connect('localhost', 'root') or die(mysql_error())


 -Original Message-
 From: Mary Adel [mailto:[EMAIL PROTECTED]
 Sent: Thursday, March 09, 2006 3:06 PM
 To: mysql@lists.mysql.com
 Subject: php and mysql


 I have a severe problem that php5 cannot connect to mysql and i
 don't
 know why
 also i am using linux
 here is my peice of code
 :?php
 print hi;
 mysql_connect('localhost', '', '') or die(mysql_error());
 echo Connected to MySQLbr /;
 print connected;
 ?
 if their is any configuration please tell me

 thanks,
 mary


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






-- 

hi mary,

take a look at 
http://www.stanton-finley.net/fedora_core_5_installation_notes.html#MySQ
L.

basically, you need to make sure that your mysql server accepts local 
connection.

if you login to your mysql server:
mysql -u root (if no password for root) or
mysql -u root -p (if there is pw for root)

and then:
select user,host,password from mysql.user;

if you don't see an entry for root  localhost, then you need to add it 
in.

hope that help.
JC

-- 
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: php and mysql

[mysql]

Mary, you need to know 100% that mysql is actually running 
and what port or socket it is listening on for connections, 
before you even try and connect to it.

If you call mysqld directly with a script something like:

#! /bin/sh
# start-mysql-5.0.18
#
# start the MySQL database server

/usr/local/mysql-5.0.18/bin/mysqld \
--defaults-file=/usr/local/mysql-5.0.18/my.cnf \
--port=7000 \
--socket=/var/lib/mysql/mysql.sock \
--pid=/var/lib/mysql/laptop.pid \
--user=mysql \
--datadir=/var/lib/mysql 

Then use a processes monitoring tool such as:

http://www.student.nada.kth.se/~f91-men/qps/

This will then show you if mysqld is running OK.

It will also show you the parameters in the above script 
that you passed to mysqld to get it started.

You then need to try and connect to mysqld with the mysql 
monitor program as already mentioned.

If you just set the client options in /etc/my.cnf config 
file, these will be applied globally to all mysql client 
programs.

EG.

# /etc/my.cnf

# MySQL client program configuration file

# last updated 2006-03-05

# mysqld server configuration file lives in
# /usr/local/mysql-version/my.cnf

#---
# mysql client program configuration options
#---

[mysql]
auto-rehash

# needed for security - to stops multiple deletes/updates
# without a where clause
safe-updates

#---

# The following options will be passed to all MySQL clients

[client]
socket = /var/lib/mysql/mysql.sock
port = 7000 

#snip
#---

# end of mysql client program configurations
# /etc/my.cnf


More options for your mysqld server can be set in the 
--defaults-file=/usr/local/mysql-5.0.18/my.cnf 

These options are additional to the options you pass in the 
above script.

EG

#---
# mysqld server configuration options
#---

[mysqld]
basedir=/usr/local/mysql-5.0.18

## use for testing multiple instances of mysqld
## these parameters are normally passed to mysqld
## from the start-mysql-5.0.18 script
##
##basedir=/usr/local/mysql-5.0.18
##port=7005
##socket=/usr/local/mysql-5.0.18/data/mysql.sock
##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid
##datadir=/usr/local/mysql-5.0.18/data
##user=mysql

server-id=1

#skip-networking
skip-name-resolve
skip-locking

set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M

# logging options
log=5-0-18.log
log-bin=laptop-bin
log-error=5-0-18.error-log
log-queries-not-using-indexes
log-slow-admin-statements
log-slow-queries=5-0-18.slow-log
log-warnings

#---

# end of mysqld server configuration file
# /usr/local/mysql-version/my.cnf

Next you will need to check php is using the same socket to 
connect to mysqld server. Use the phpinfo() function in a 
php script, to verify this. Obviously, you will need the 
mysql or mysqli extension loaded for this.

HTH

Keith Roberts


In theory, theory and practice are the same;
In practice they are not. 

On Fri, 10 Mar 2006, Logan, David (SST - Adelaide) wrote:

 To: Mary Adel [EMAIL PROTECTED]
 From: Logan, David (SST - Adelaide) [EMAIL PROTECTED]
 Subject: RE: php and mysql
 
  
 If you still have issues after that, then read
 http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html
 
 Regards
 
 
 ---
 ** _/ **  David Logan 
 ***   _/ ***  ITO Delivery Specialist - Database
 *_/*  Hewlett-Packard Australia Ltd
 _/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
    _/  _/  _/  _/     Desk:   +618 8408 4273
   _/  _/  _/_/_/  Mobile: 0417 268 665
 *_/   **
 **  _/    Postal: 148 Frome Street,
    _/ **  Adelaide SA 5001
   Australia 
 invent   
 ---
 
 -Original Message-
 From: JC [mailto:[EMAIL PROTECTED] 
 Sent: Friday, 10 March 2006 6:42 AM
 To: Mary Adel
 Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
 Subject: RE: php and mysql
 
 On Thu, 9 Mar 2006, Mary Adel wrote:
 
  Thanks for al your help and i di that and now i have another error
 
  Can't connect to local MySQL server through socket
  '/var/lib/mysql/mysql.sock' (2)
  my code is as follows:
 
  ?php
  print hi;
  mysql_connect('localhost', 

RE: php and mysql

[Ing. Edwin Cruz]

Also be careful with this:

http://dev.mysql.com/doc/refman/5.0/en/old-client.html

(after to establish a connection)


Edwin.


-Mensaje original-
De: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Enviado el: Jueves, 09 de Marzo de 2006 02:17 p.m.
Para: Mary Adel
CC: [EMAIL PROTECTED]; mysql@lists.mysql.com
Asunto: RE: php and mysql


 
If you still have issues after that, then read
http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: JC [mailto:[EMAIL PROTECTED] 
Sent: Friday, 10 March 2006 6:42 AM
To: Mary Adel
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: RE: php and mysql

On Thu, 9 Mar 2006, Mary Adel wrote:

 Thanks for al your help and i di that and now i have another error

 Can't connect to local MySQL server through socket 
 '/var/lib/mysql/mysql.sock' (2) my code is as follows:

 ?php
 print hi;
 mysql_connect('localhost', 'root','') or die(mysql_error()); echo 
 Connected to MySQLbr /; print connected;
 ?
 if u can help in this i ll appreciate that a lot


 On Thu, 2006-03-09 at 13:37 -0500, fbsd_user wrote:
 You need a login id and password unless this is test DB added under 
 ID root You have to use the same login id as the one you created 
 the db/table with.

 mysql_connect('localhost', 'Login id', 'pw') or die(mysql_error())

 mysql_connect('localhost', 'root') or die(mysql_error())


 -Original Message-
 From: Mary Adel [mailto:[EMAIL PROTECTED]
 Sent: Thursday, March 09, 2006 3:06 PM
 To: mysql@lists.mysql.com
 Subject: php and mysql


 I have a severe problem that php5 cannot connect to mysql and i don't
 know why
 also i am using linux
 here is my peice of code
 :?php
 print hi;
 mysql_connect('localhost', '', '') or die(mysql_error());
 echo Connected to MySQLbr /;
 print connected;
 ?
 if their is any configuration please tell me

 thanks,
 mary


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






-- 

hi mary,

take a look at 
http://www.stanton-finley.net/fedora_core_5_installation_notes.html#MySQ
L.

basically, you need to make sure that your mysql server accepts local 
connection.

if you login to your mysql server:
mysql -u root (if no password for root) or
mysql -u root -p (if there is pw for root)

and then:
select user,host,password from mysql.user;

if you don't see an entry for root  localhost, then you need to add it 
in.

hope that help.
JC

-- 
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]




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

Re: Signal 11 crashes on MySQL V5

[Simon Garner]

On 9/03/2006 9:43 a.m., Kishore Jalleda wrote:

could you tell us if these 6 are in a cluster or in a replication set up,
and u also said the 3 linux bixes all crash at once, did u check the logs,
do they crash under load, what about the OS, is it stable when mysql
crashes

Kishore Jalleda



We use cluster and replication. We were seeing random crashes on the 
replication slaves, which are only used for SELECT queries. They 
wouldn't all crash at the same time (then again they aren't all doing 
the same queries at the same time). No OS problems.


This is what we would get in the error log:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help 
diagnose
the problem, but since we have already crashed, something is definitely 
wrong

and this may fail.

key_buffer_size=134217728
read_buffer_size=1044480
max_used_connections=15
max_connections=100
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections 
= 335471 K

bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xaea70058
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x9bb5ac, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81913f0
0xfbc420
0x827a1c1
0x8279d88
0x827a1c1
0x8279f0d
0x827a03a
0x8278554
0x81a6b39
0x81ae100
0x81a5213
0x81a4d4d
0x81a429e
0x960b80
0x6549ce
New value of fp=(nil) failed sanity check, terminating stack trace!

I did run the backtrace but didn't save the results, now I can't resolve 
it without reinstalling mysql 5 because I don't have the right sym file :/


This is using the 5.0.18 Linux RPMs from mysql.com (tried both the 
glibc23 and statically linked ones, no difference - the above trace is 
from the glibc23 one). Running a mix of both Fedora 3 and Fedora 4 on 
Intel P4s.


-Simon


On 3/8/06, Dave Pullin [EMAIL PROTECTED] wrote:

I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently
upgraded
to V5 on all servers. Now MySQL is crashing regularly (several times per
day, some days) with 'got signal 11'.

My 3 Linux servers are very different machines running different software
a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386
, a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL
5.0.18-0.glibc23.x86_64
, a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386

The windows machines are not having a problem. All 6 are running
essentially
the same application.

It seems unlikely to be a hardware problem because its on 3 machines at
once. It looks like a MySQL V5 problem but I can't pin it down to anything
specific enough to report a bug.

Anyone had similar experiences with MySQL V5?

Dave


--
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: Aggregate functions in ORDER BY

[Yasir Assam]

Thanks Dave. I was hoping I could avoid naming the columns myself (all 
my queries are built using an SQL code compiler from data stored in XML 
files). I'll have to write code to add the column alias names (but only 
if they don't use expressions).


Yasir


I dont think it is possible although I have never searched hard for the
ability. It seems unlikely - since you get to name the columns in the table
and the columns in the query (using 'as'). I suggest you use one of those
abilities.
 -Original Message-
 From: Yasir Assam [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, March 08, 2006 11:10 PM
 To: Dave Pullin
 Cc: mysql@lists.mysql.com
 Subject: Re: Aggregate functions in ORDER BY


 Many thanks for that Dave.

 Do you know whether it's possible for MySQL to return fully qualified
column names by default?

 For example, I'd like

 select * from foo;

 to return

 ++--+
 | foo.a  | foo.b|
 ++--+
 |  1 | Rex  |
 |  2 | Buddy|
 |  3 | Sam  |
 |  4 | Lucky|
 |  5 | Tiny |
 |  6 | Snoopy   |
 |  7 | Lady |
 |  8 | Tiger|
 |  9 | Speedy   |
 ++--+

 instead of

 ++--+
 | a  | b|
 ++--+
 |  1 | Rex  |
 |  2 | Buddy|
 |  3 | Sam  |
 |  4 | Lucky|
 |  5 | Tiny |
 |  6 | Snoopy   |
 |  7 | Lady |
 |  8 | Tiger|
 |  9 | Speedy   |
 ++--+


 Thanks,
 Yasir

All you are doing when you quote the expression like `COUNT(pets.pet_id)`
is referencing the column in the select by its default column name (which is
the same as the expression.)
That's why it only works when the expression is a column.

If you dont want the order by column to appear in the results, make your
select a subselect.

select a,b,c from (
select a,b,c, COUNT(pets.pet_id) as count
  from etc
 order by count
) as subtable

Dave


-Original Message-
From: Yasir Assam [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 08, 2006 9:10 PM
To: mysql@lists.mysql.com
Subject: Aggregate functions in ORDER BY


Hello,

I noticed something strange in MySQL (I'm using 4.1.15).

If I use an aggregate function in the ORDER BY clause I get an error.

SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY COUNT(pets.pet_id) DESC;

gives me the error:

ERROR  (HY000): Invalid use of group function

To get round this I can quote the ORDER BY column, e.g.

SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `COUNT(pets.pet_id)` DESC;

This isn't an ideal solution though - in order for it to work I have to
put that expression in the SELECT column list. If I want to use an
aggregate function that isn't being displayed it just doesn't work
because it can't find that column:

SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `AVG(pets.pet_age)` DESC;

gives the result:

ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause'

I don't want to have to put the expression I want to order by in the
SELECT column list - is there any way round this?

Below is the SQL code used to create the toy DB I'm using if anyone
wants to experiment:

Thanks,
Yasir


DROP TABLE IF EXISTS men;
CREATE TABLE men (
  man_id INT UNSIGNED PRIMARY KEY,
  man_name VARCHAR(255)
);

INSERT INTO men (man_id, man_name) VALUES
  (1, 'Alan'),
  (2, 'Bob'),
  (3, 'Colin');

DROP TABLE IF EXISTS pets;
CREATE TABLE pets (
  pet_id INT UNSIGNED PRIMARY KEY,
  pet_name VARCHAR(255),
  pet_man_id INT UNSIGNED,
  pet_age INT
);

INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES
  (1, 'Rex', 1, 5),
  (2, 'Buddy', 1, 10),
  (3, 'Sam', 1, 13),
  (4, 'Lucky', 1, 3),
  (5, 'Tiny', 1, 7),
  (6, 'Snoopy', 2, 4),
  (7, 'Lady', 2, 9),
  (8, 'Tiger', 2, 6),
  (9, 'Speedy', 3, 1);








 




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

Re: Aggregate functions in ORDER BY

[Yasir Assam]

Thanks for that Nicolas.

Yasir


Can you run without the Order By at all? If not, you may need to
properly join the tables.

I could reproduce the issue with MySQL 4.1 as well and I would go around
it myself by creating a temporaty table, populating it with the
aggregate, doing a select on the temp table and then of course dropping
it.

-Original Message-
From: Yasir Assam [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 08, 2006 7:10 PM

To: mysql@lists.mysql.com
Subject: Aggregate functions in ORDER BY


Hello,

I noticed something strange in MySQL (I'm using 4.1.15).

If I use an aggregate function in the ORDER BY clause I get an error.

SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY COUNT(pets.pet_id) DESC;

gives me the error:

ERROR  (HY000): Invalid use of group function

To get round this I can quote the ORDER BY column, e.g.

SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `COUNT(pets.pet_id)` DESC;

This isn't an ideal solution though - in order for it to work I have to 
put that expression in the SELECT column list. If I want to use an 
aggregate function that isn't being displayed it just doesn't work 
because it can't find that column:


SELECT men.man_name,
  COUNT(pets.pet_id)
FROM men,
pets
WHERE men.man_id = pets.pet_man_id
GROUP BY men.man_id
ORDER BY `AVG(pets.pet_age)` DESC;

gives the result:

ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause'

I don't want to have to put the expression I want to order by in the 
SELECT column list - is there any way round this?


Below is the SQL code used to create the toy DB I'm using if anyone 
wants to experiment:


Thanks,
Yasir


DROP TABLE IF EXISTS men;
CREATE TABLE men (
  man_id INT UNSIGNED PRIMARY KEY,
  man_name VARCHAR(255)
);

INSERT INTO men (man_id, man_name) VALUES
  (1, 'Alan'),
  (2, 'Bob'),
  (3, 'Colin');

DROP TABLE IF EXISTS pets;
CREATE TABLE pets (
  pet_id INT UNSIGNED PRIMARY KEY,
  pet_name VARCHAR(255),
  pet_man_id INT UNSIGNED,
  pet_age INT
);

INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES
  (1, 'Rex', 1, 5),
  (2, 'Buddy', 1, 10),
  (3, 'Sam', 1, 13),
  (4, 'Lucky', 1, 3),
  (5, 'Tiny', 1, 7),
  (6, 'Snoopy', 2, 4),
  (7, 'Lady', 2, 9),
  (8, 'Tiger', 2, 6),
  (9, 'Speedy', 3, 1);




 




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

How to Log Warnings and Errors from queries

[ryan lwf]

Hi all,

I understand that the option log-errors and log-warnings only logs server
related internal errors.  How do I enable logging errors from queries
executed, so that I can fix the problematic query statement accordingly?

The statement SHOW WARNINGS and SHOW ERRORS does not work on my server
with mysqld-4.0.25 binary version.

Any inputs are appreciated.

Thanks,
Ryan.

Stored Procedure Problem

[Josh]

I posted this same item on the mysql forum but the
only place that looked remotely appropriate was under
the Newbie section...  I'm not sure if it will be
answer there so I thought I might toss it out here to
see if there were any takers.

I'm baffled as to why this stored procedure is acting
this way.  See the below sample table and examples.

mysql select * from Rates;
+--+--+---+-+---+
| rtID | bdID | empID | rtStartDate | rtBillingRate |
+--+--+---+-+---+
|1 | NULL |  NULL | -00-00  |  0.00 |
|2 | NULL | 1 | 2004-01-01  |  2.00 |
|3 | NULL | 1 | 2004-05-10  |  4.00 |
|4 | NULL | 1 | 2005-01-10  |  6.00 |
|5 | NULL | 1 | 2005-04-12  |  8.00 |
|6 | NULL | 1 | 2006-01-02  | 10.00 |
|8 |   37 | 1 | 2005-10-01  | 25.00 |
+--+--+---+-+---+

DELIMITER $
CREATE PROCEDURE test_rate (EMPID int, BDID int,
CURRENTDATE date)
BEGIN
SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
  FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=EMPID and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID IS NULL
and rtStartDate = CURRENTDATE))
LEFT JOIN Rates rt3 ON (rt3.empID=EMPID and
rt3.chgID IS NULL and rt3.bdID=BDID and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=EMPID and chgID IS NULL and bdID=BDID and
rtStartDate = CURRENTDATE))
 WHERE rt1.empID IS NULL
   and rt1.chgID IS NULL;
 
END$
DELIMITER ;

mysql call test_rate (1,NULL,'2005-09-01');
+---+
| rtBillingRate |
+---+
|  8.00 |
+---+
CORRECT!

mysql call test_rate (1,37,'2005-10-10');
+---+
| rtBillingRate |
+---+
| 25.00 |
+---+
CORRECT!

mysql call test_rate (1,NULL,'2005-10-10');
+---+
| rtBillingRate |
+---+
|  0.00 |
+---+
1 row in set (0.01 sec)
WRONG! This should have returned 8.00.

When I run this query by itself (outside the
procedure) I get the correct result: (notice I'm
plugging in EMPID, BDID, and CURRENTDATE parameters)

SELECT
COALESCE(rt3.rtBillingRate,rt2.rtBillingRate,rt1.rtBillingRate)
AS rtBillingRate
  FROM Rates rt1
LEFT JOIN Rates rt2 ON (rt2.empID=1 and
rt2.chgID IS NULL and rt2.bdID IS NULL and
rt2.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID IS NULL and
rtStartDate = '2005-10-10'))
LEFT JOIN Rates rt3 ON (rt3.empID=1 and
rt3.chgID IS NULL and rt3.bdID=NULL and
rt3.rtStartDate=(SELECT MAX(rtStartDate) FROM Rates
WHERE empID=1 and chgID IS NULL and bdID=NULL and
rtStartDate = '2005-10-10'))
 WHERE rt1.empID IS NULL
   and rt1.chgID IS NULL;

+---+
| rtBillingRate |
+---+
|  8.00 |
+---+
1 row in set (0.00 sec)
CORRECT!

What's going wrong in the stored procedure?

__
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: How to Log Warnings and Errors from queries

[Dan Nelson]

In the last episode (Mar 08), ryan lwf said:
 I understand that the option log-errors and log-warnings only logs
 server related internal errors.  How do I enable logging errors from
 queries executed, so that I can fix the problematic query statement
 accordingly?
 
 The statement SHOW WARNINGS and SHOW ERRORS does not work on my
 server with mysqld-4.0.25 binary version.

Those commands appeared in MySQL 4.1.  Before then, warnings were
simply counted.

-- 
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: mysqldump backup on filters

[Rithish Saralaya]

Your table is missing. Try this:

mysqldump --single-transaction -u root clientdb table
--where=FLD_CLIENT_ID=1  client1_dbbackup.sql

Yes. The tables are missing. That's because I want the backup of all the
tables in the db, and those tables that have the column FLD_CLIENT_ID, they
should be filtered by the where clause.

This is because, if we are to reproduce the system for a client, then all
we
need to do is to run this backup on a new db.

So, it looks like I will have to backup the db in phases.

1. Backup client-specific tables
mysqldump --single-transaction -u root clientdb table_M1 table_M
table_MX --where=FLD_CLIENT_ID=1  client1_dbbackup.sql

2. Then append the other tables to the backup.
mysqldump --single-transaction -u root clientdb table_N1 table_N2 table_NX
 client1_dbbackup.sql

However, this means that I cannot take a backup when the system is up and
running.

Any other ideas, folks?

Regards,
Rithish.


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