Re: subquery performance

[Michael Stassen]

Jeff Drew wrote:
 I have a query with a subquery which does not throw an error, but does not
 return either.   I've been testing the query using mysql Query Browser and
 the poor dolphin jumps only once a minute or so ;)   I use MYSQL's
 excellent error documentation heavily so if the query doesn't throw an
 error, I'm at a disadvantage.  Is there an optimization or just better
 syntax to use?

 THE DATA
 I have a table that contains an abbreviated identifier and a full
 identifier.  The real data is a bit messy so here's a sanitized example:

Please don't do that.  When you don't show us the real problem, you increase the 
odds of getting the wrong solution.


 Abbreviated Column contents:   TR123, RG456
 Full Identifier Column contents: TR 123 abc, RG 456 def

Do I understand correctly that your table looks something like

  AbbreviatedIdentifier  FullIdentifier Name   Address
  -  --    ---
  TR123  TR 123 abc a name an address
  RG456  RG 456 def another name   another address

with FullIdentifier as the primary key?  If so, that's a poor table design, 
which is almost certainly causing your problems.  See below.


 THE QUERY
 My intent is to:
 1. select some of the Abbreviated Column and convert that to a selection
 for the Full Identifier Column by:
- extracting the first 2 characters
- inserting a space
- selecting the last 3 characters
- appending % so I can match any of the last 3 characters in the
 Full Identifier

...in the subquery.  The % character is the wildcard for LIKE matches.  There is 
no wildcard for IN.  IN is followed by a list of values, one of which must match 
exactly for the row to be selected, so this won't work as intended.


 2. select rows from Full Identifier Column based on #1

That's very convoluted.  How do you expect this to help?

I'm guessing that FullIdentifier is your primary key.  Because it's a long, 
messy string, you are finding it slow to use it to select rows, particularly 
when you need to find rows with a particular substring buried in the key.  You 
hoped that a shorter string might match more quickly, enabling you to narrow 
down the number of rows where the full id has to be examined.  That will never 
work as you've described it.  The subquery might run faster, but then you must 
do the full id comparison anyway to find rows which match the subquery list 
(FullIdentifier IN ...).  You've actually added overhead.  This *might* work in 
some situations if you had an integer primary key to use to do the matching 
between inner and outer query, but there would probably still be a better way.


In any case, the reality is that MySQL doesn't optimize subqueries all that 
well, so they are seldom the best way to speed up a query.  It may be the case 
that your subquery is being treated as DEPENDENT, in which case it is being run 
once for each row found in the outer query.  That is, once for every row in your 
table.  Since your query never seems to return, I'd bet that's the case here.


 PROBLEMS
 I think I have two problems:
 1. in... % syntax  is not present in any examples I've seen.  They are
 all like... %  so in may not work.

Right, it won't.

 2. Here's query that runs, but does not return:
reformatted

 SELECT name, address
 FROM testTable
 WHERE FullIdentifier
   IN ( SELECT CONCAT(SUBSTRING(AbbreviatedIdentifier,1,2),   ,
  SUBSTRING(AbbreviatedIdentifier FROM 3) , % )
FROM testTable
WHERE name LIKE 'Daisy'));

Is this really your query?  As I've already mentioned, the % won't work for IN. 
 Also, neither FullIdentifier nor AbbreviatedIdentifier is mentioned in the 
selection criteria, so the problem you tried to fix with the subquery is not 
present in this query.  Finally, there is no wildcard in the string that name is 
supposed to match, so there is no need for LIKE.  The intent of this query is 
equivalent to


  SELECT name, address
  FROM testTable
  WHERE name = 'Daisy';

which should be as fast as possible if there is an index on name.

 My left join attempt complained  because the data is all in one table. Is
 there a better solution than my  FullIdentifier in(select...  ?

 I am not an SQL expert so I'd appreciate any ideas on how to correct this
 query.

When I see a string primary key that looks like TR 123 abc, I find it is 
usually a safe bet that the TR means something (a code, a manufacturer, ...), 
the 123 means something (a production run, a part number, a plant number, ...) 
and the abc means something (some detail, a team code, ...).  In other words, 
you've crammed the answers to 3 questions into one column.  That's almost never 
a good idea.


I would (for example):

#Create an auto_inc id column and 3 separate columns for each of the id parts
  ALTER TABLE testTable
  DROP PRIMARY KEY,
  ADD id INTEGER UNSIGNED NOT 

Re: Mysql pushing data to client

[Michael Stassen]

David Godsey wrote:
 I am looking for a way to write a client program that will wake up when
 there is new data in the database, much like replication.

 So instead of my client pulling the database on some fixed interval, I
 would like the mysql daemon to push the data to my client when there is
 new data.  I assume this is possible given that it is done with
 replication.  Does anybody have information on how to implement a client
 program like this?

 I'm using PHP, but I'm not opposed to using C or C++ for this kind of
 functionality.

James Neff wrote:
 I'm doing something similar now and I just have my clients (a java app)
 periodically check a small table that indicates theres data ready for
 them.  It's a simple SELECT count(*) ... that is fairly inexpensive
 and the does this once every 5 seconds.

 I don't know if there is a built-in solution for mysql but this gets the
 job done for me.

 Let me know if you find a better way to do this.

David Godsey wrote:

That is a good work around, I appreciate the idea.  The optimal would be a
client that listens on the mysql thread the pushes data to the database. 
But if there is no way to tap into that thread, the select count(*)

would do.

There must be a way to do it though, since it is done with replication.


Except that it isn't.  The Master writes to its local binlog.  The slave pulls 
updates from the master periodically.  See the manual for all the details 
http://dev.mysql.com/doc/refman/4.1/en/replication-intro.html.


You could write a daemon to run on the mysql server, which would watch for local 
changes and push them to your client, but that sounds overly complex to me 
compared to James' solution.


Michael

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

Re: Conditional copy of values

[Michael Stassen]

Ravi Kumar. wrote:

Dear All,
 
I have 2 tables Table1 and Table2. In Table1 rowid is primary key and in

Table2 rowid is primary key as well as foreign key (referring to rowid in
Table1).
 
There are several other columns in Table1, but Table2 has only one col -

rowid.
 
I want to insert rowid into Table2 conditionally:
 
insert into Table2 (rowid) select T1.rowid from Table1 T1, Table2 T2
where T1.somecolumn = 'some condition' 
 
But I want to insert only those rowid values which do not exist in T2.
 
Please help.
 
I am using MySQL version 4.1.14-standard-log. Hence subqueries are not a

solution.


You've already got your solution, but, for the record, subqueries ARE supported 
in mysql 4.1.14.  They aren't supported in version prior to 4.1.


Michael

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

Re: Why can't I delete these records?

[Michael Stassen]

Evert wrote:
 Hi all!

 Who can tell me why the following does not work...?

 When I do:
 SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND
 condition2 IS NULL;

 it returns:
 +-+
 | counter |
 +-+
 |   2 |
 +-+

 Then I do:
 DELETE FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL;

 it returns:
 Query OK, 0 rows affected (0.00 sec)

 But... when I give a
 SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND
 condition2 IS NULL;

 How come the 2 records did not get deleted...?

 Regards,
   Evert

What you have shown us is perfectly valid SQL that should produce the desired 
results.  Your mysql version (4.1.21) should make no difference.  I see only 2 
possibilities:


  1. There is actually a difference between the two queries (some typo) that 
your editing has hidden.  I know you said you've only done a search and replace 
on the table name (really?), so this is unlikely, but we on the list can't 
conclusively rule it out without seeing the unmodified original queries.  Sorry 
for the rant, but it has frequently been the case that the problem has turned 
out to be in the real query but not in the edited-for-the-list version.


  2. Something odd is going on.  One possibility is that an index used by one 
query but not the other is out of sync with the actual data.  Either you have 
two rows which are not found by the delete, or your select is counting two 
nonexistent rows.  It would be nice to know which.


Have you verified that the two rows actually exist by inspecting their contents 
rather than simply counting them?  That is, what is the output of


  SELECT condition1, condition2
  FROM table1
  WHERE condition1 = 'A' AND condition2 IS NULL;

Do you get two rows of output?  What about

  SELECT *
  FROM table1
  WHERE condition1 = 'A' AND condition2 IS NULL;

Do you get the same two rows (including the other columns)?

Have you tried CHECK/REPAIR?
http://dev.mysql.com/doc/refman/4.1/en/check-table.html
http://dev.mysql.com/doc/refman/4.1/en/repair-table.html

If you still need help, include the output of

  SHOW CREATE TABLE table1;

Michael

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

Re: displaying a sing thumbnail

[Michael Stassen]

[EMAIL PROTECTED] wrote:

I have a database of images, http://www.thethistlehouse.com/db.jpg

What I want to do is select ONLY ONE image to display as a the image link for 
that gallery. As you can see galleries are numbered dynamcially but galleries 
can also be added and deleted so the galleries no's I have now (7, 8) will 
change. I have the code to display the thubnail but am stuck with the query.

I want to use mysql and php to

(i) determine how many unique galleries there are.


To list the galleries:

  SELECT DISTINCT gallery FROM yourpicturetable;

To count them:

  SELECT COUNT(DISTINCT(gallery)) FROM yourpicturetable;


(ii) Retrieve  display a single thumbnail from each gallery to act as the link 
to that gallery


That should be easy, but first you must tell us how you determine which picture 
in each gallery is the desired thumbnail.


Michael

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

Re: Trouble with using IN for a sub-query statement

[Michael Stassen]

Chris wrote:

Chris White wrote:

On Monday 07 August 2006 12:13 pm, William DeMasi wrote:

I meant to have it say :

select * from c2iedm_dev2.act where act_id IN (select obj_act_id from
c2iedm_dev2.act_functl_assoc where subj_act_id =24);


What does the output of (the subselect):

select obj_act_id
from c2iedm_dev2.act_functl_assoc
where subj_act_id =24;

show?

Sorry about that, typo on my part, but I have tried it as above and I 
get

the error I mentioned.


What version of mysql are you using?

select version();

subselects don't work for versions before v5.


You are right to inquire about version, but subselects are supported in mysql 
starting with 4.1.


Michael


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

Re: 1 to many relationship

[Michael Stassen]

Chris wrote:
snip
 select *
 from customers c, issues i, customer_issues ci
 where c.customerid=ci.customerid
  AND ci.issueid=i.issueid;

Chris wrote:
snip
 That query should be the same as this one:
 select *
 from customers c
 inner join customer_issues ci on (c.customerid=ci.customerid)
 inner join issues i on (ci.issueid=i.issueid);

 I don't like writing my queries that way because the order of the joins
 needs to be very specific and as you add more tables, getting the order
 wrong can make things slow (and hard to track down).

I think you have that precisely backwards.  Putting the join conditions in the 
WHERE clause makes it easy to accidentally forget one.  Many, many Why isn't 
this query working? questions on this list have been the result of missing join 
conditions that became obvious when the query was rewritten to explicitly give 
the join conditions in ON clauses.  As you add more tables, the likelihood of 
making that mistake and the difficulty of detecting it increase.


Whether you put it in the WHERE or ON clause, the correct join condition is the 
same.  Like you, I used to write all my joins as implicit joins with the join 
conditions in the WHERE clause (largely becuase most of the examples in the 
manual were written that way).  It worked fine for the relatively simple queries 
I was writing at the time, but as my queries became more complex, that format 
became more and more unwieldy.  With join conditions for 4 or 5 tables and 3 or 
4 row restrictions in the WHERE clause, it gets to be a mess.  Faced with that, 
and seeing the problems others have had, I've gradually switched to writing all 
of my joins with explicit join conditions in ON clauses.


I also have no idea what you mean by getting the order wrong can make things 
slow.  The order in which you write inner joins should have no effect on how 
your query is performed.  The optimizer will, in fact, consider each of the 
possible orderings that produce correct results and choose the one it calculates 
to be the most efficient.  That is,


  SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i ON ci.issueid = i.issueid
  WHERE c.customerid = 13;

is identical to

  SELECT *
  FROM issues i
  JOIN customer_issues ci ON ci.issueid = i.issueid
  JOIN customers c ON c.customerid = ci.customerid
  WHERE c.customerid = 13;

in that mysql will do the same thing to satisfy either one.  This can be 
verified through the use of EXPLAIN.


In short, there is absolutely no speed benefit in putting your join conditions 
in the WHERE clause.  In fact, there is a small inefficiency in putting them 
there, as mysql must then parse the WHERE clause into join conditions and row 
restrictions.  This is very fast, of course, but doesn't have to be done if the 
join conditions are explicitly given in ON clauses.


Finally, you will discover that some of your queries which use the comma as 
implicit join operator will break when you upgrade to mysql 5, because starting 
with mysql 5, implicit joins are given lower precedence than explicit joins, as 
specified by the SQL standard.  See the manual for the details:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html
http://dev.mysql.com/doc/refman/5.0/en/join.html.

Michael

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

Re: 1 to many relationship

[Michael Stassen]

Peter Lauri wrote:
 Is there not a better way to do that? What will happen there is that a large
 result set will be created because when you just do select * from
 customers c, issues i, customer_issues ci it will be like the inner product
 from all these tables, and then just choosing the right ones.

 If the table C have 1000 records, issues 5000 and customer_issues 15000 you
 would end up with a 75,000,000,000 rows large results set, that would not be
 so sweet, would it?

Peter Lauri wrote:
 Yes, it cuts it down to that number of records in the end, so the final
 result set will just be a few rows that match the 'WHERE'. But the internal
 process of MySQL do merge all tables and then chooses the records that
 matches the 'WHERE' clause.

No database would work very well if that were the case.  You are essentially 
asserting that mysql has no optimizer.  That just isn't so.  The job of the 
optimizer is to devise a plan to execute the query in a way that will examine 
the fewest possible rows.  Eliminating rows before looking at them is always 
preferable to eliminating them afterwards.


For example, given the query

  SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i on ci.issueid = i.issueid
  WHERE c.customerid = 13;

mysql will use the index on customers.customerid to select the 1 row with 
customerid = 13, it will then use the index on customer_issues.customerid to 
find matching rows in customer_issues, then finally it will use the index on 
issues.issueid to find matching rows in issues.  This is easily verified using 
EXPLAIN:


  EXPLAIN SELECT *
  FROM customers c
  JOIN customer_issues ci ON c.customerid = ci.customerid
  JOIN issues i on ci.issueid = i.issueid
  WHERE c.customerid = 13;

  +-+---++-+-+--+
  | select_type | table | type   | key | ref | rows |
  +-+---++-+-+--+
  | SIMPLE  | c | const  | PRIMARY | const   |1 |
  | SIMPLE  | ci| ref| PRIMARY | const   |4 |
  | SIMPLE  | i | eq_ref | PRIMARY | test.ci.issueid |1 |
  +-+---++-+-+--+
  3 rows in set (0.01 sec)

(Note that I've pared the output of EXPLAIN down to a few relevant columns.)

The rows column tells the story.  Mysql plans to use the primary key to find 
the 1 matching row in customers, then use the primary key to find the 4 matching 
rows in customer_issues for that 1 customer, then use the primary key to find 
the 1 matching row in issues for each row found in customer_issues.  That is, it 
expects to produce 1x4x1 = 4 rows, not 'size of c' x 'size of ci' x 'size of i' 
rows!


You might want to read the optimization section of the manual for more on the 
subject http://dev.mysql.com/doc/refman/4.1/en/optimization.html.


Chris wrote:
 I don't know enough about mysql internals to debate that so I'll take
 your word for it.

 'Explain' doesn't give enough information about what happens behind the
 scenes so I'm not sure how to prove/disprove that and I don't know of
 any tools that would show you that (if there is let me know!).

 Having said all of that I've never had a problem doing it the way I
 mentioned.. ;)

EXPLAIN is documented in the manual 
http://dev.mysql.com/doc/refman/4.1/en/explain.html.


Michael

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

Re: 1 to many relationship

[Michael Stassen]

Chris wrote:
snip
 Hmm. Must be a recent change, I've had problems with that in the past.

 Thanks for the pointers.

You're welcome.  I'm not sure which part you think is a recent change. 
Certainly, mysql 5 with its demotion of the precedence of the implicit join is 
recent.  Perhaps it depends on your definition of recent.  According to the 
manual http://dev.mysql.com/doc/refman/4.1/en/join.html, putting the join 
condition in an ON clause was first supported for INNER JOIN as of 3.23.17, and 
for JOIN starting with 4.0.11.  Is that what you mean?


Chris wrote:


EXPLAIN is documented in the manual 
http://dev.mysql.com/doc/refman/4.1/en/explain.html.


So it is, but it doesn't show me the decisions it makes. It shows me 
whether it will use an index or not, it won't show me how it puts the 
whole thing together.


Well, I think that information is there, unless I'm mistaking what you mean. 
Mysql joins the tables in the order given in the explain output.  It shows 
whether or not an index is being used, how many rows are predicted to be 
examined, and how the rows will be sorted.



Compare to the postgres one:

http://www.designmagick.com/article/23/page/5

(yes I did write that article so I am biased)

which tells me what's going on at each step of the decision process.

Whether you find that useful or not - *shrug* - I however find it very 
useful and wish the mysql explain was as good as that.


I've not used postgresql, so I will make no claim as to the relative merits of 
each system's EXPLAIN.  It appears you're in a better position to make 
comparisons.  I'll just say that it takes some practice/experience to really get 
what mysql's EXPLAIN is telling you, and I gather from your article that the 
same could be said of postgresql's EXPLAIN.  At least, I couldn't tell just from 
your article what useful info postgresql's EXPLAIN gives that mysql's does not. 
 I wonder if it is possible that what you are looking for is hidden in mysql's 
EXPLAIN output in a way that isn't apparent to you.  If you think that might be 
the case, and you told us what you're looking for, someone could say if it's 
there or not.  On the other hand, you may be certain that's not the case.  Then 
I'd be curious to hear about it.


Michael


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

Re: AW: AW: find in list

[Michael Stassen]

Charlie Schaubmair wrote:

Hello Michael,

thx, I know normalisation.

BUT normalisation isn't always the best (fastest) way to store, or select
the datas, this is why I don't use it most time.
Often I'm testing my projects with normalisation and without and my last
very big problem with big select statements is very fast without
normalisation and with normalisation it was very slow.

br
Charlie


The point of properly normalized and indexed tables is that they almost
always produce more efficient queries. There are exceptions, of course, but
they are just that, exceptions, and I assure you, this is not one of those
times. Finding rows with a particular attribute was made difficult by the
lack of normalization. More to the point, there is no chance that

  SELECT *
  FROM MyTable_attributes
  WHERE FIND_IN_SET(1, someFieldInMyTable);

with the denormalized design is close to as fast as

  SELECT m.*
  FROM MyTable m
  JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
  WHERE ma.attribute = 1;

with the normalized design, as long as the proper indexes are present.  The
former is a guaranteed full-table scan, while the latter is a simple index look 
up.

Michael


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

Re: query needed

[Michael Stassen]

VenuGopal Papasani wrote:
 Once again i send the table data:

 Code  Period  Value
  c12004 22
  c12005 10
  c22005 15
  c32005 20
  c42005 15
  c52005  5
  c62005 30
  c72005 25
  c12006  5
  c22006 15
  c32006 40
  c42006 30

  From this I need the sum of values for period 2005 and codes c1+c2-c4-c5
 (this is not constant its just an example there is lot of codes like
 this..)

 For ex:- the result that I want to get is:
 the value for code c1, period 2005 is 10
   for code c2, period 2005 is 15

  sum of c1 and c2 is 10 + 15 = 25

 The value for code c4, period 2005 is 15
   for code c5, period 2005 is 5

   Sum of c4 and c5 is 15 + 5 = 20

 Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5

You mean, (c1+c2) - (c4+c5), right?

Peter Lauri wrote:
 SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4',
 code, IF(code='c5', code, 0))) FROM datavalue;

You can simplify this using IN.  Also, we need to sum the values, not the codes.

  SELECT SUM(IF(code IN ('c1', 'c2'), value, 0))
   - SUM(IF(code IN ('c4', 'c5'), value, 0)) AS total
  FROM datavalue
  WHERE period = 2005;

VenuGopal Papasani wrote:
 if it is static then it works fine.but we have lots of codes in a table
 which should be done similar operation.instead varifying staticly with
 c1,c2
 can we make dynamic.

Well,

  SELECT SUM(IF(code IN (list of + codes), value, 0))
   - SUM(IF(code IN (list of - codes), value, 0)) AS total
  FROM datavalue
  WHERE period = 2005;

seems simple enough, especially if this is to be truly dynamic.

Another method would be to store a coefficient for each code in a (perhaps 
temporary) table.


  CREATE TABLE code_values (code CHAR(2), coeff INT);

Set coeff to 1 for codes which specify addition, and -1 for codes that specify 
subtraction.  For example, to get c1 + c2 - c4 - c5:


  INSERT INTO code_values VALUES ('c1', 1), ('c2', 1), ('c4', -1), ('c5', -1);

then join the tables to get your result:

  SELECT period, SUM(d.value * cv.coeff) AS total
  FROM datavalue d
  JOIN code_values cv ON (d.code = cv.code)
  WHERE d.period = 2005
  GROUP by d.period;
++-+
| period | SUM(d.value * cv.coeff) |
++-+
|   2005 |   5 |
++-+

I would guess that you have a number of standard queries you must run from time 
to time.  In that case, you could store the coefficients of each query in a 
permanent table.  For example, if c1 + c2 - c4 - c5 and c6 + c7 - c3 were 
two standard sums, you could do something like (results are using your sample 
data above):


  CREATE TABLE sum_queries (query_id INT, code CHAR(2), coeff INT);
  INSERT INTO sum_queries VALUES
  (1, 'c1', 1), (1, 'c2', 1), (1, 'c4', -1), (1, 'c5', -1),
  (2, 'c6', 1), (2, 'c7', 1), (2, 'c3', -1);

  SELECT period, SUM(d.value * sq.coeff) AS total
  FROM datavalue d
  JOIN sum_queries sq ON (d.code = sq.code)
  WHERE d.period = 2005
AND sq.query_id = 1
  GROUP by d.period;
++---+
| period | total |
++---+
|   2005 | 5 |
++---+

  SELECT period, SUM(d.value * sq.coeff) AS total
  FROM datavalue d
  JOIN sum_queries sq ON (d.code = sq.code)
  WHERE d.period = 2005
AND sq.query_id = 2
  GROUP by d.period;
++---+
| period | total |
++---+
|   2005 |35 |
++---+

Of course, a query description table linked by query_id would be a good idea in 
this case.


Michael

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

Re: AW: find in list

[Michael Stassen]

Charlie Schaubmair wrote:
  Hello,

 I want to do a query where I only gt the results by a numeric value:

 select * from MyTable where 1 IN someFieldInMyTable
 I know this query doesn't work, but maybe anyone knows what I mean.

 1 can be a value betwenn 1 and 23
 someFieldInMyTable is normaly a list like: 1,2,3,4,5,6,9,21

Mike van Hoof wrote:
 try:

 SELECT * FROM MyTable WHERE someFieldInMyTable='%1%'
 This will also give you results like 21

 What I usally do in these cases is build the values like this:

 [1][2][3][21]

 And then the query:

 SELECT * FROM MyTable WHERE someFieldInMyTable='%[1]%'

 Mike

Charlie Schaubmair wrote:
 Hello Mike,

 thx, but isn't there another way?

Mike van Hoof wrote:
 maybe there is, but i don't know why... because when you do a like query
 it also finds the 21 when you do a like on the 1.
 what you maybe can do (not tested) is:

 SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
 WHERE wherefield='%,1,%'

 Now your someFieldInMyTable is CONCATed to ,1,2,3,4,5,6,9,21, and over
 that value the where clause is done...

Mike van Hoof wrote:
 Sorry, query was wrong... has to be:

 SELECT *, CONCAT(',',someFieldInMyTable,',') as wherefield FROM MyTable
 WHERE wherefield LIKE '%,1,%'

You've just discovered why this is not the way to store a list of attributes. 
Stuffing multiple values in a single cell is a bad idea.  The correct way to 
implement this is to store the attributes in a separate table, one per row. 
That is, instead of


  MyTable
  ===
  MyTable_id  other columns  attributes
  1 ...  1,2,3,4,5,6,9,21
  2 ...  5,7,13

you would do this:

  MyTable_attributes
  ===
  MyTable_id  other columns
  1 ...
  2 ...

  MyTable_attributes
  ==
  MyTable_id  attribute
  1   1
  1   2
  1   3
  1   4
  1   5
  1   6
  1   9
  1  21
  2   5
  2   7
  2  13

Then finding rows in MyTable which have attribute 1 becomes trivial:

  SELECT m.*
  FROM MyTable m
  JOIN MyTable_attributes ma ON m.MyTable_id = ma.MyTable_id
  WHERE ma.attribute = 1;

With a UNIQUE index on (attribute, MyTable_id) in MyTable_attributes, this will 
be very quick.




That said, you can find what you want with your current schema using the 
FIND_IN_SET() function 
http://dev.mysql.com/doc/refman/4.1/en/string-functions.html.


  SELECT *
  FROM MyTable_attributes
  WHERE FIND_IN_SET(1, someFieldInMyTable);

This works so long as the values in someFieldInMyTable are separated by commas. 
 If you switch to some other separator, such as enclosing attributes in 
brackets, it won't work.


Note that no index on someFieldInMyTable can be used for this query, however, so 
it requires a full-table scan.


Michael

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

Re: Query Question

[Michael Stassen]

The story so far, with comments:

Michael DePhillips wrote:
 Hi,

 Does anyone have a clever way of returning; a requested value with  one
 value less than that value, and one value greater than that value with
 one query.

 For example T1 contains

 ID
 1234
 1235
 1236
 1238

 select ID from T1 where ID = 1235 and ID1235 and ID 1235 LIMIT 3
 (obviously this doesn't work)  I would want to return

 1234
 1235
 1236

 or;
 select ID from T1 where ID = 1237 and ID1237 and ID 1237 LIMIT 3 I
 would want

 1236
 1238

 I would be surprised if there was no way of doing this.but then
 again, I'm often surprised

Michael DePhillips also wrote:
snip
 ...the id may not always
 be one(1) value away. So the number one needs, somehow, to be replaced
 with a way to get the next largest value  and the previous less than
 value.

 Sorry for the lack of precision in my prior post.

nigel wood wrote:
 What you want to do isn't possible in the form you want without
 subqueries. Which would you prefer:

Yes, I believe it is.  See below.

 a) A single query returning one row of three columns containing nulls
 b) A single query using sub queries (MySQL 4.1+ only) returning upto
 three rows with one column
 c) multiple queries

 Nigel

Michael DePhillips wrote:
 Hi Nigel,

 A and B...please.

Douglas Sims wrote:

 I think this will do it, although it takes three queries.

 I'm assuming the id values are unique, even if there can be gaps (that's
 what you might get with an AUTO_INCREMENT field).  If the values are not
 guaranteed to be unique then this may not give what you want (if there
 are multiple rows with the same value you're looking for, e.g.
 3,4,5,5,5,6,9, and you ask for 5, then this would give you 3,5,6, not
 three fives.)

 SELECT @id:=5;
 SELECT * FROM t1 WHERE id(SELECT MAX(id) FROM t1 WHERE id@id) ORDER BY
 id DESC LIMIT 1;
 SELECT * FROM t1 WHERE [EMAIL PROTECTED] LIMIT 1;
 SELECT * FROM t WHERE id(SELECT MIN(id) FROM t1 WHERE id@id) ORDER BY
 id ASC LIMIT 1;

I think you're on the right track, but this is unnecessarily complicated, and, 
unfortunately, wrong.  Given rows with the ids 3,4,5,6,9, it selects 3, 5, and 9 
rather than 4, 5, and 6.


Look at the first query:

  SELECT * FROM t1
  WHERE id  (SELECT MAX(id) FROM t1 WHERE id@id)
  ORDER BY id DESC LIMIT 1;

The subquery finds the previous id (isn't that what we want?), then the outer 
query selects all the ids that are *less than* the id we want and limits the 
output to the largest one.  In other words, we get the row prior to the row that 
is prior to the row with the chosen id.  You need an = in there somewhere.


To get the previous id:

  SELECT MAX(id) FROM t1 WHERE id  @id;

To get the entire previous row:

  SELECT * FROM t1 WHERE id  @id ORDER BY id DESC LIMIT 1;

or

  SELECT * FROM t1 WHERE id = (SELECT MAX(id) FROM t1 WHERE id  @id);

Your last query is similar -- it selects the 2nd row after the middle one rather 
than the next one.


 But as to putting that in one statement... it might be better just to do
 it as three.

There's always UNION.  See below.

[EMAIL PROTECTED] wrote:
 Here's a single query version of Douglas's solution:

 select @id:=6;

 select distinct t.testid,
  (select max(testid) from t  where testid  @id) as previousId,
  (select min(testid) from t  where testid  @id) as nextId
 from t
 where [EMAIL PROTECTED]

 Donna

Well, this requires subqueries (mysql 4.1+), and only returns the ids (not the 
other columns in those rows), but probably neither is a big deal.  There is one 
big problem, however: It returns nothing if the chosen id doesn't exist. 
Michael specified that he wanted the rows before and after the chosen id, even 
if no row has the chosen id.


Try this:

  SET @id = 13;

To get just the ids, even when no row has id = @id:

(SELECT MAX(id) FROM t1 WHERE id  @id)
  UNION DISTINCT
(SELECT id FROM t1 WHERE id = @id)
  UNION DISTINCT
(SELECT MIN(id) FROM t1 WHERE id  @id)
  ORDER BY 1;

To get the row contents, as well:

(SELECT * FROM t1 WHERE id  @id ORDER BY id DESC LIMIT 1)
  UNION DISTINCT
(SELECT * FROM t1 WHERE id = @id)
  UNION DISTINCT
(SELECT * FROM t1 WHERE id  @id ORDER BY id ASC LIMIT 1)
  ORDER BY id;

(Whether those are each one query or three depends on how you feel about UNION, 
I suppose.)


Now, that last query isn't very efficient.  The first and last parts may not use 
the index on id very well.  You could improve on this significantly if you knew 
the size of the largest gap in the sequence of ids (or at least an upper bound). 
 For example, if you were sure no 2 ids were farther apart than 12, you could 
take that into account, like so:


  SET @id = 2345;
  SET @maxgap = 12;

(SELECT * FROM inits
  WHERE id  @id AND id  @id - @maxgap
  ORDER BY id DESC LIMIT 1)
  UNION DISTINCT
(SELECT * FROM inits WHERE id = @id)
  UNION DISTINCT
(SELECT * FROM inits
  WHERE id  @id AND id  @id + @maxgap
  ORDER BY id ASC LIMIT 1)
  

Re: Get a Numeric Zero instead of NULL in SELECT

[Michael Stassen]

Asif Lodhi wrote:

Hi,

I have a query:

reformatted


INSERT INTO tmp2 (x)
SELECT  ((t3.m * 100) + b.id) AS x2
FROM tmp3 t3
LEFT JOIN (SELECT (MAX(x) - ((MAX(x) div 100) * 100)) + 1 AS pid
   FROM tmp2
   WHERE (x div 100) = 2147
   HAVING (MAX(x) - ((MAX(x) div 100) * 100))  483648) b
 ON t3.m=b.pid


First, the outer SELECT:

  SELECT  ((t3.m * 100) + b.id) AS x2

Table b has no column `id`, so I expect that should be

  SELECT  ((t3.m * 100) + b.pid) AS x2

Yes?

Now, the inner SELECT:

  SELECT (MAX(x) - ((MAX(x) div 100) * 100)) + 1 AS pid
  FROM tmp2
  WHERE (x div 100) = 2147
  HAVING (((MAX(x)) - ((MAX(x) div 100) * 100))  483648)

The SELECT clause:
As every row examined has x div 100 = 2147 (the WHERE condition), MAX(x) div
100 must be 2147, so (MAX(x) div 100) * 100) must be 214700.
Therefore,

   (MAX(x) - ((MAX(x) div 100) * 100)) + 1
  = MAX(x) - 214700 + 1
  = MAX(x) - 214699

Thus, we can simplify your select to

  SELECT MAX(x) - 214699 AS pid

Next, the WHERE clause:
Because the condition (x div 100) = 2147 compares a function of x to a
constant, no index on column x can be used to find matching rows.  Always
compare the unmodified column to a constant or a range, if possible, so that an
index may be used.  In this case, (x div 100) = 2147 is equivalent to

  x BETWEEN 214700 AND 214799

Written this way, an index on column x could be used to find matching rows.

Finally, the HAVING clause:
Most of the work here has already been done in the SELECT clause, so there's no
need to recompute.  That is,

  MAX(x) - ((MAX(x) div 100) * 100)  483648

is equivalent to

  pid - 1  483648

which, of course, is the same as

  pid  483649

Putting it all together yields

  SELECT MAX(x) - 214699 AS pid
  FROM tmp2
  WHERE x BETWEEN 214700 AND 214799
  HAVING pid  483649


The table t3 has a single INT field m with a single record and
value: 2147.  The table tmp2 has a single INT field x with no
records.


Really?  You seem to be doing a lot of work to accomplish the equivalent of

  INSERT INTO tmp2 (x) VALUES (214700);

I suppose the intent must be to get a single query that works when tmp2 is empty
and later when tmp2 has rows (as a result of previous INSERTs).  How about this?

  INSERT INTO tmp2 (x)
  SELECT  t3.m * 100 + IFNULL(b.pid, 0) AS x2
  FROM tmp3 t3
  LEFT JOIN ( SELECT MAX(x) - 214699 AS pid
  FROM tmp2
  WHERE x BETWEEN 214700 AND 214799
  HAVING pid  483649)
   ON t3.m = b.pid


The above code returns NULL in the 2nd column of the SELECT


What?  There is no second column of either select.


that I can't add to or multiply with another number to get the final
value to insert into the table tmp2.  I am using INNODB tables on
5.0.22 running on a WINDOWS-XP-SP2.  Around 75 Clients connect from
VB6/Windows.  I am STARTing TRANSACTIONs and COMMITing them from VB6
client-code.  Since I am also using SQL STRICT mode with more stricter
parameters in the MY.INI.

I don't want to use functions as that will impair the query speed.

Do you know of any way that I could use to get a numeric ZERO instead
of a null in the 2ND column of the SELECT?

snip

Again, there is no second column, but you can use IFNULL().

Asif Lodhi wrote in a second message:

Hi,

I am replying to my own post to tell you that now I am using
 CASE WHEN {condition} THEN  END
construct to check for NULL and returning numeric ZERO.  On the face of
it, CASE doesn't seem to be function - it's an operator - isn't?


I'd call it a function, but what's the difference?  Why do you believe operators
are faster than functions?  In any case,  MAX() is a function.  Perhaps you
won't mind adding an IFNULL() now that we've removed a MAX().


However, I would now like to ask you whether I can use some kind of an
Oracle-like  USE_INDEX optimization hint in MySQL to get it to use a
specific index in the SELECT?  I used explain on it and it tells me
that it's using index on all except on the the query I am using as the
2nd table.  Is there any way I can speed it up?


Something doesn't make sense here.  If tmp2 is empty, what difference does an
index make?  How would an index be used to speed up a query on an empty table?
And how could it be slow in the first place?

In any case, as I explained above, use of an index on column x in the inner
query was impossible because the WHERE condition compared a _function of the
column_ (a value calculated from the value of x, whether by a function or use of
an operator) to some value.  By rewriting the query to compare the actual value
of the column, use of an index becomes possible.


Any suggestions?


I get the impression you are implementing some sort of serial primary key where
the millions part means something.  Have you considered a multi-part primary 
key?

In any 

Re: select random ids from list

[Michael Stassen]

kalin mintchev wrote:

select id,title from content where id IN(10,20,30);


cool thanks..  what if i want it to be random ordering like:

select id,title from content where id IN(20,10,30);

and i would like the order of the ids and the in() to be kept in the
result array. what 'order by' would i use?!


  SELECT id, title
  FROM content
  WHERE id IN (20,10,30)
  ORDER BY FIELD(id, 20, 10, 30);

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

Michael

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

Re: IN or OR? whats the diff?

[Michael Stassen]

Ratheesh K J wrote:

Hello all,

Just wanted to know if using IN in the where clause is better than OR in terms 
of performance.

that is :

Are these both same in terms of performance

SELECT * FROM TABLE
WHERE  ( COLUMN = 1 OR COLUMN = 2 );

SELECT * FROM TABLE
WHERE COLUMN IN ( 1, 2 );

thanks,

Ratheesh Bhat K J


They are the same.

You can verify that mysql treats them the same by putting EXPLAIN in front of 
each.  See the manual for details 
http://dev.mysql.com/doc/refman/4.1/en/explain.html.


Michael

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

Re: Problems with WHERE clause

[Michael Stassen]

Jørn Dahl-Stamnes wrote:
 I got the following query:

 select s.id,s.name,sum(p.fee) as fee from serie as s inner join race_serie as
 rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner
 join participants as p on (p.race_id=r.id) where s.receipt=1 and
 p.rider_id=236 and fee  0 group by s.id order by s.f_date;

 which gives me the error:

 ERROR 1052 (23000): Column 'fee' in where clause is ambiguous

 Without the 'and fee  0' the query works fine.

 mysql select version();
 ++
 | version()  |
 ++
 | 4.1.8-standard |
 ++

 Is this due to an old version of MySQL?

No (though your version is quite old -- the current 4.1 is 4.1.21.  A lot of 
bugs were fixed in between 
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html.).  As others have 
pointed out, you have at least two things named fee, a column in participants 
and a calculation in your select.  Simply changing the alias of the latter won't 
fix the problem though, as I'll explain below.


Gabriel PREDA wrote:
snip
 If you want the sum to be larger that zero then you would have to do:

 SELECT s.id, s.name, SUM(p.fee) AS fee
FROM serie AS s
INNER JOIN race_serie AS rs ON rs.serie_id = s.id
INNER JOIN races AS r ON r.id = rs.race_id
INNER JOIN participants AS p ON p.race_id = r.id
 WHERE s.receipt = 1
AND p.rider_id = 236
AND SUM(p.fee)  0
 GROUP BY s.id
 ORDER BY s.f_date;

That won't work.

Jørn Dahl-Stamnes wrote:
snip
 What I want is to find out which series a given rider has participated where
 (s)he has paid fee for participating (in some cases a rider my participate
 without haveing paid).

 If the rider has paid fee once in a serie, then the sum will be more than 0.
 But will the query above give me the id and name for a serie where a rider
 has participated but not paid? One way to find out is to test it.

snip
 This gave the following error:
 ERROR  (HY000): Invalid use of group function

Exactly.

Martin Jespersen wrote:
 Do the right thing(TM) and don't use an alias that is the same as a
 field that exists (this was mentioned before by someone else).
 Since there is a field in your join named fee, using fee as an
 alias, is bound to cause your trouble.
 Also, trying to call sum(p.fee) twice is a waste of cputime. I say
 trying here because this is what fails, you are not allowed to call sum
 in your where clause, thus the Invalid use of group function.

 try

  SELECT s.id, s.name, SUM(p.fee) AS fee_total
  FROM serie AS s
  INNER JOIN race_serie AS rs ON rs.serie_id = s.id
  INNER JOIN races AS r ON r.id = rs.race_id
  INNER JOIN participants AS p ON p.race_id = r.id
  WHERE s.receipt = 1
  AND p.rider_id = 236
  AND fee_total  0
  GROUP BY s.id
  ORDER BY s.f_date;

That still won't work.

Whether you repeat the aggregate function in the WHERE clause or use its alias, 
what you are asking for is unresolvable circular logic.  The WHERE clause 
determines which table rows are to be examined.  You cannot determine whether or 
not to look at a row based on the sum that will be produced if it is, or isn't, 
included.  Do you see the problem?  You won't know the sum until after you have 
chosen which rows are included in your results (the job of the WHERE clause), 
but you are trying to exclude rows from the sum based on the eventual sum. 
Until MySQL has been enhanced to accurately read your mind and predict the 
future, that won't work.


What we really want is to sum the fees paid by a particular rider for each 
series, then display only those series for which the total is positive.  In 
other words, we wish to filter the results *after* they have been calculated, 
not before.  That is the job of the HAVING clause.  Try:


  SELECT s.id, s.name, SUM(p.fee) AS fee_total
  FROM serie AS s
  INNER JOIN race_serie AS rs ON rs.serie_id = s.id
  INNER JOIN races AS r ON r.id = rs.race_id
  INNER JOIN participants AS p ON p.race_id = r.id
  WHERE s.receipt = 1
 AND p.rider_id = 236
  GROUP BY s.id
  HAVING fee_total  0
  ORDER BY s.f_date;

Michael

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

Re: WHERE problem, or is it a problem?

[Michael Stassen]

Peter Lauri wrote:

Best group member,

I have this query on MySQL version 4.0.27:

SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans
FROM tblparticipants part
LEFT JOIN tblanswers answer ON (answer.par_num=part.memberid)
LEFT OUTER JOIN profilepdfauth pdfauth ON (part.memberid=pdfauth.memberid)
WHERE pdfauth.id IS NULL
GROUP BY part.memberid
ORDER BY numberofans DESC, part.memberid;

This works fine,

However, I only want the results where COUNT(*)=31. So I tried:

SELECT part.memberid, part.prefname, part.email, COUNT( * ) AS numberofans
FROM tblparticipants part
LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid )
LEFT OUTER JOIN profilepdfauth pdfauth ON ( part.memberid =
pdfauth.memberid)
WHERE pdfauth.id IS NULL
AND COUNT( * ) =31
GROUP BY part.memberid
ORDER BY numberofans DESC , part.memberid

But then MySQL answered with: # - Invalid use of group function

What is the problem here? Why can I not do a WHERE COUNT(*)=31?

Is there any other way to just select the COUNT(*)=31?

Best regards,
Peter Lauri


WHERE conditions determine which rows to select.  You can't count how many rows 
you've selected until after you've selected them.  Use HAVING to filter the 
results after selection.  Try:


  SELECT part.memberid, part.prefname, part.email, COUNT(*) AS numberofans
  FROM tblparticipants part
  LEFT JOIN tblanswers answer ON ( answer.par_num = part.memberid )
  LEFT OUTER JOIN profilepdfauth pdfauth
ON ( part.memberid = pdfauth.memberid)
  WHERE pdfauth.id IS NULL
  GROUP BY part.memberid
  HAVING numberofans =31
  ORDER BY numberofans DESC , part.memberid

Michael



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

Re: Granting all to a user with a db name prefix

[Michael Stassen]

Paco Zarabozo A. wrote:

Hello All,

I'm trying to GRANT ALL to a user only on DBs that math a prefix, but i 
can't find the way to so it on the documentation. Let's assume the username 
is john. I want him to have all privileges only on databases with the 
prefix john, so he can:


- create and drop databases starting ONLY with john (like john_sessions, 
john123, john_mytest, john_mail, etc)

- have any kind of privileges on such databases

According to the documentation, i can use % and _ as wildcards. However, 
mysql gives my an error if i try to use % wildcard. Only _ wildcard is 
accepted, but the following example:


GRANT ALL ON JOHN_.* to 'JOHN'@'%' IDENTIFIED BY 'foo';

..only allows user john to create databases starting with john, followed by 
ONE single character. Using this, i can give 32 different grants in order to 
allow up to 32 characters after 'john', but i'm sure that's not the way.


If i try the wildcard %, i get an error. I've tried the following:

GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
GRANT ALL ON 'JOHN%'.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
GRANT ALL ON 'JOHN%'.'*' to 'JOHN'@'%' IDENTIFIED BY 'foo';
GRANT ALL ON JOHN*.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
GRANT ALL ON 'JOHN*'.* to 'JOHN'@'%' IDENTIFIED BY 'foo';

..and almost all similar ways. Am i missing something? I temporarily fixed 
the problem by directly editing mysql.db to change the wildcard _ for % in 
the respective record, and it works fine. However, i really want to know the 
right way to do it. I hope someone there gives me the answer.


Thanks a lot, have fun.

Francisco


If you look closely, the answer is in the example at the end of the paragraph 
you cite from the manual: GRANT ... ON `foo\_bar`.* TO ...  You need to quote 
with backticks, the one thing you didn't try.  Hence,


  GRANT ALL ON `JOHN%`.* to 'JOHN'@'%' IDENTIFIED BY 'foo';

should work.

And yes, I would agree that's poorly documented.

Michael



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

Re: mysqldump: Got errno 27 on write. file too large

[Michael Stassen]

Dominik Klein wrote:
 This might be a filesystem problem. Some filesystems (in certain
 configurations) cannot hold files larger than a particular size.

 Do you have any files larger than that cut dumpfile on that partition?


Duhaime Johanne wrote:
 Thank you for your answer.

 Yes I have file larger than what mysqldump could manage. Here is an
 example of this. Both files are on the same partition.

 mercure{root}54: du -k  mercure.log.jui2006
 11948544mercure.log.jui2006

Umm, that's only about 1Gb, which makes it smaller than the problem file.  Did 
you mean to show us a different file?


 mercure{root}68: du -k myregendump
 2098184 myregendump
 Which stop at that size.

 Which make me think that mysql is concerned. Or a tmp file but as I
 mention the tmp file has plenty of space.

 Best regards

 Johanne

My first thought is that Dominik is on the right track.  I get

  ~: perror 27
  OS error code  27:  File too large

which suggests there is some OS limitation.  Perhaps the user running mysqldump 
is limited?  Do you have any larger files owned by the same user?  Can that user 
currently create a file larger than that using another means?


The other possibility would be a bug.  You are using version 4.1.7, which is 
nearly 2 years old now (released October 2004).  The current version is 4.1.20. 
   If you have indeed hit a bug, your best bet would be to upgrade and try 
again.  You should probably at least read the *long* list of bug fixes from 
4.1.7 to 4.1.20 in the MySQL change history in the manual 
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html.


Michael

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

Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Michael Stassen]

Jacob, Raymond A Jr wrote:


I ran the following commands:

USE snort;

CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp 
'2006-05-01';
...
SELECT count(*) from sidtemp;
 count(*)
 7501376

DELETE FROM data WHERE data.cid = sidtemp.cid;
ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause

SHOW tables; 


Does not include sidtemp in the list of tables in the snort database nor
would I expect it to.

Question: What database is the table sidtemp in?

r/Raymond


You have the wrong syntax.  You can't mention a table in the WHERE clause that 
wasn't in the FROM clause.  Try


  DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;

or

  DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;

See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html.

Michael

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

Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Michael Stassen]

Jacob, Raymond A Jr wrote:

Thank you,
I was definitely on the wrong track on this one.
I annotated your commands to make sure that I understood what they
were doing. Are my comments correct?
---
You have the wrong syntax.  You can't mention a table in the WHERE
clause that wasn't in the FROM clause.  Try

   DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;
/* deletes all records in data with cid equal cid in sidtemp but leaves
sidtemp unchanged */


Correct.


or

   DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;
/* deletes all records in data and sidtemp where cids are equal */


No, this only deletes from data.  These 2 are supposed to be equivalent.  Using 
this version of the syntax deletes rows from the tables named *before* the 
FROM.  Tables used to determine the matching rows come after the FROM.  The 
first version I gave uses an explicit JOIN, the second uses the implicit, comma 
join.  I prefer explicit joins, but I included the implicit join because it 
seemed to be what you were trying.



--
/* Will the USING clause work also? */


It should.


 or

/* looking that link below: */
DELETE  data FROM data USING data, sidtemp WHERE data.cid =
sidtemp.cid
 or
DELETE  data FROM data USING data, sidtemp JOIN sidtemp ON data.cid
= sidtemp.cid


 Is the above correct also?

Almost.  In the USING form, the tables which should lose rows go after FROM, 
while the tables used to make the selection go after USING.  Hence, the query 
would be


  DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid;

or

  DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid;

In general, the implicit join syntax (comma between tables with join 
condition(s) in the WHERE clause) seems easy to use, but frequently leads to 
trouble.  The explicit join syntax (table JOIN table ON condition) is much 
clearer, which should help avoid mistakes.  You should probably read the manual 
page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html.



Thank you,
raymond 


Michael

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

Re: Records in front of and behind another record

[Michael Stassen]

Scott Haneda wrote:
 Mysql 4

 I have a frustrating case here, a list of numbers, say, 0 through 30,000 or
 so, however, there are gaps in the numbers, so not purely sequential.

 I am faced with selecting one record from the dataset, that's simple,
 however, before that select, I need to make sure the record is there, and if
 it is not, find the one either just before it, or just after it.  Whichever
 is closest.  If they are the same, lean on either one, I don't really care.

 Any suggestion on this one would be appreciated, I can do this with 3
 separate queries, but that is taking too long, since I have to do this with
 two separate datasets and shove this all back out a browser to the user.

It really would be helpful when posting a question like this if you would 
actually show us the three queries and tell us how long too long is.  It would 
also help to know the structure of your table.


Scott Haneda wrote:
 Clearing this up a little, I have data like this:

 3, 4, 5, 8, 9, 10
 If I am looking for 6, it is not there, I want back 5 and 8, in this case, 5
 is closest, so I would like 5 back, but both are ok, as I can use server
 side code to determine the closest.

OK, that's clear.

Scott Haneda wrote:

I am thinking UNION and two SQL queries would do this, how is UNION
optimized, is it more or less the same as running two selects?


Usually, but a UNION of what two queries?  I won't comment on the efficiency of 
a query I haven't seen.


This can be done in one query.  You didn't give any details, so I'll make them 
up.  The table is named scotts_table, the numbers are in the column named val, 
and the target value is 413.  I'll use user variables for clarity, but they 
aren't necessary.


For each row in the table, the distance from that row's val to the target value 
is the absolute value of the difference between val and the target value.  The 
row with the smallest distance is the one you want.  Hence,


  SET @target = 413;

  SELECT *
  FROM scotts_table
  ORDER BY ABS([EMAIL PROTECTED])
  LIMIT 1;

Of course, that's a full-table scan with a filesort, so it's not very efficient. 
 We can improve on this, however, if we know the size of the largest gap.  For 
example, if we know that the largest gap is 26, we can do the following:


  SET @target = 413;
  SET @range=26;

  SELECT *
  FROM scotts_table
  WHERE val BETWEEN (@target - @range) AND (@target + @range)
  ORDER BY ABS([EMAIL PROTECTED])
  LIMIT 1;

In this case, mysql can use the index on val (You do have an index on val, 
right?) to choose the few rows near the target value, before performing the 
filesort on just those few matching rows.


Michael

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

Re: if else statement

[Michael Stassen]

Song Ken Vern-E11804 wrote:
 Hi,

 I'm trying to build a query in using SQL instead of doing it in Perl.

 I am trying to do something like this :

 If ((select col1 from table1 where id = 1) == 3)
 Then
 Select col2 from table2 where table2.id = 1;
 Else
 Select col2 from table3 where table3.id = 1;

 In Perl I would probably have to access the DB twice.

 Select col2 from table1 where if = 1;

 If (col2 == 3) {
  Select col2 from table2 where table2.id = 1;
 } else {
  Select col2 from table3 where table3.id = 1;
 }

 I've read the manual on subqueries but the example don't indicate how I
 can do a conditional test using a subquery?

 Am I on the right track or is there another way to do this?

Maybe.  The first thing to realize, I believe, is that IF() is not a control 
flow function, despite what the manual says.  IF() is a function whose return 
value depends on a condition.  It does not allow you to control flow in the 
traditional programming sense.


Jørn Dahl-Stamnes wrote:
 Maybe:

 (SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
 and table2.id=1)
 UNION
 (SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col13
 and table3.id=1);

 I have not tested it...

That should probably work, though the JOIN conditions make me cringe.  Also, I 
have no idea why you are using a LEFT-JOIN (and backwards?).  I'd have done it 
this way:


  (SELECT t2.col2
   FROM table1 t1
   JOIN table2 t2 ON t1.id = t2.id
   WHERE t1.id = 1 AND t1.col1 = 3
  )
  UNION
  (SELECT t3.col2
   FROM table1 t1
   JOIN table3 t3 ON t1.id = t3.id
   WHERE t1.id = 1 AND t1.col1 != 3
  );

Thomas Lundström wrote:
 Not sure what you're aming for here and how your data is structured but
 why not use a join and alias and fetch all info in one select and then
 solve what you need in your code?

 Something in the line of:

 select t2.col2 from_t2, t3.col2 from_t3
 from table1 t1, table2 t2, table3 t3
 where t1.id = t2.id
   and t1.id = t3.id
   and t1.id = 3

 Maybe you can do something like that?

That may be a start, but you have the wrong condition on t1.id, and you've left 
out any mention of t1.col1.  Also, explicit JOINs are better than implicit 
(using commas) JOINs.


Peter Lauri wrote:
 SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
 col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;

 That should do it.

I don't believe this will work, as the subqueries will return multiple rows 
where one is expected.


Song Ken Vern-E11804 wrote:
 Hi Peter,

 Thanks you for you answers.

 Can I put SELECT statements inside the IF statement?
 Mysql give error
 ERROR 1064: Error in SQL syntax.

 Under Control Flow functions of the manual, it says
 IF(expr1,expr2,expr3)
 If expr1 is TRUE (expr1  0 and expr1  NULL) then IF() returns expr2, 
else it returns expr3. IF() returns a numeric or string value, depending on the 
context in which it is used.


 But doesn't say what expr is. Examples only show numeric and string functions.

You've never mentioned your mysql version.  4.1+ is needed for subqueries.  In 
4.1.15, I find that


  IF(condition, (subquery1), (subquery2))

works so long as the subqueries are surrounded by parentheses and always return 
exactly one value.


In any case, I don't think subqueries are needed or helpful.  If there is at 
most one row in table2 and table3 for each id in table1, the following should work:


  SELECT IF(t1.col1 = 3, t2.col2, t3.col2)
  FROM table1 t1
  LEFT JOIN table2 t2 ON t1.id = t2.id
  LEFT JOIN table3 t3 ON t1.id = t3.id
  WHERE t1.id = 1;

Otherwise, I think the only option is the UNION query above.

Michael

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

Re: Differences between MySQL 4 and 5 for scripts

[Michael Stassen]

Graham Reeds wrote:
I have posted a similar question on the blojsom group but I feel I will 
have better chance of an answer here.


Blojsom 3 was developed using MySQL5 for it's back end.  However the 
host I am with uses 4.0.25 and are unwilling to upgrade - which is fair 
enough.


So I decided to see what I can do to make the database creation scripts 
MySQL4 compliant.  The CHARSET was the easy one - just change it to 
CHARACTER SET but the next one was more tricky:


The problem I am having is that version 4 fails giving a syntax error 
for the single quotation marks. It appears the script (which was written 
on a Mac using MySQL dump 10.9 if that is any help) uses ` and not ', 
but doing a general search and replace doesn't fix it.  I've searched 
the online docs regarding quotes but came up short (too many hits with 
too many unrelated items) so if anyone knows the exact url that would be 
helpful too.


Here's a snippet of the code:
DROP TABLE IF EXISTS `Blog`;
CREATE TABLE `Blog` (
  `blog_id` varchar(50) NOT NULL,
  PRIMARY KEY  (`blog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks, G.


Single quotes (') are for strings.  Backticks (`) are for identifiers.  To the 
best of my knowledge, this hasn't changed from 4.0 to 5.  Hence, using your 
table definition,


  INSERT INTO `Blog` (`blog_id`) VALUES ('This is a blog id');

would be correctly quoted.

Strictly speaking, you only need to quote identifiers which wouldn't otherwise 
be allowed (reserved words), so none of the backticks are needed in your example 
(or in mine).  See the manual for details:


  http://dev.mysql.com/doc/refman/4.1/en/legal-names.html
  http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html

What leads you to believe your error is related to quoting?  I think it is more 
likely to be something else.  If you post the query that gives the error and 
include the exact error message, I'm sure someone will be able to identify the 
problem.


Michael


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

Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)

[Michael Stassen]

murthy gandikota wrote:
snip
   Here are some pertinent facts:
   a) stopped mysqld
   b)  copied files from sfg to sfgnew in the var directory
   c)  set the permissions to 777 (read, write, execute) for all the files in 
sfgnew

   d)  restarted mysqld

Dan Buettner wrote:
 Murthy, do you have appropriate permissions (777 works, or 775/770 if
 owned by mysql user) on your new directory ('sfgnew')?  If you don't
 have execute permission for the mysql user on that dir, MySQL can't list
 the contents ...

murthy gandikota wrote:
 Hi Dan
   I've set the permissions to 777 for all the files and directories.
   Basically I did chmod -R 777 sfgnew. Also changed the ownership to
   mysql using chown -R mysql:mysql sfgnew.

   Thanks!

No, no, no!  That's using a shotgun to kill a gnat.  This idea of fixing a 
problem by setting permissions on mysql's data to wide open keeps coming up. 
It may work, but it's not a good idea.


There is no reason that normal users should have *any* access to mysql's data 
directory and files.  They most certainly should not have write access!  You may 
not even need group access to the data directory, unless you specifically want 
to give a user or users other than mysql the ability to perform specific tasks 
(read the error log, for instance), in which case you should give the group the 
minimum permission needed.


For typical use, permissions should be 750 for the data directory and its 
subdirectories, and 660 for files in the directories.   That's sufficient and 
relatively safe.  Never set your permissions more open than that unless you are 
certain you know what you are doing.


Michael

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

Re: Limiting results from joins

[Michael Stassen]

Kim Christensen wrote:
 Hey list;

 Consider this statement:

 SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
 = m.manufactor_id  p.product_id = i.product_id;

 Now, each unique product_id from products can have more than one
 entry in the items table, but I only want to fetch the one which
 fullfills a certain criteria. In this case, I want the statement only
 to JOIN the row if the column item_updated from the items table
 equals 1.

Adding

  WHERE i.item_updated = 1

is the simplest solution.

 Is this the proper way to solve this:

 SELECT * FROM products p, manufactors m, items i WHERE p.manufactor_id
 = m.manufactor_id  p.product_id = i.product_id  i.item_id =
 (SELECT item_id FROM items i2 WHERE i2.item_updated = 1);

Definitely not, as you've seen.  Your subquery returns a *lot* of unwanted 
item_ids.

 I find the above solution VERY slow, almost as if I have missed out on
 a very fundamental part of the logic - but it does get the work done.
 How could I speed this up, or solve it in another statement?

[EMAIL PROTECTED] wrote:
 How about:

 select * from products p
 inner join manufactors m on p.manufactor_id=m.manufactor_id
 inner join items i on p.product_id=i.product_id and i.item_updated=1

I'd have suggested

  SELECT *
  FROM products p
  JOIN manufactors m ON p.manufactor_id = m.manufactor_id
  JOIN items i ON p.product_id = i.product_id
  WHERE i.item_updated=1

Although they appear different in theory, the optimizer will almost certainly 
treat them the same.  (Check each with EXPLAIN to see.)  In either case, the 
efficient way to execute the query will depend largely on what indexes are in 
the items table.  With an index on items.item_updated, the optimizer may choose 
to use that index to select the appropriate rows from items, then join to the 
other two tables.  On the other hand, if items has a multi-column index on 
(product_id, item_updated), the optimizer would be able to join table items 
last, using the index to match the one matching row for each row of table 
products.  Without the multi-column index, it couldn't do that.


Kim Christensen wrote:
 That worked great, really cut the time on my queries! However, how
 would one filter out the INNER JOIN statement if it returns more than
 one row to JOIN? I have reworked my previous table setup and query so
 that I need the row that has the lowest value of item_updated, not
 particularly 1.

Well, that's different.  This is an example of why you shouldn't try to simplify 
your query for the list.  When your question doesn't include your real query, 
you are quite likely to get answers that don't apply to your real query.


 Here's what it looks like right now:

 SELECT * FROM products p
 INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id
 INNER JOIN items i ON i.product_id = p.product_id

 The problem is, that each entry in products may occur more than once
 in items, and they are identified by product_id in both tables.
 How do I filter out the results from the last INNER JOIN by certain
 criterias? I want the INNER JOIN to only return the row from items
 which has the lowest value in the column item_updated.

This is a FAQ, with solutions given in the manual, 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html. 
 (While the subquery solution looks more elegant, the temporary table solution 
is frequently more efficient.)


 As it is right now, MySQL returns a row for each time the product
 occurs in the items table, which is not what I want :-)

Peter Lauri wrote:
 Just add:

 ORDER BY i.item_updated GROUP BY i.product_id

 (assuming that item_updated and product_id are in table i)

That definitely will NOT work.  You cannot select columns not included in the 
grouped columns and expect to get meaningful results.  See the manual for why, 
http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html.


Kim Christensen wrote:
 That gives me the result that I'm after, the only problem seems to be
 that the sorting is
 made before the matching - on all the rows (huge execution time) - how
 can I prevent this?

I doubt that really gives the result you want, unless you were very lucky, or 
still haven't told us the real query.  In any case, this query will not work 
reliably.


Peter Lauri wrote:
 Take away the GROUP BY thing. And after that you just check if the rows are
 in the order that you want. The upper row would be the one that GROUP BY
 will take. Are you sure that you want the lowest value in the
 item_update? I would like to have the highest value.

 If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP
 BY i.product_id

Again, no.  ORDER BY does not affect which rows are chosen by GROUP BY, which 
is what you are hoping for with this query.  In fact, GROUP BY does not even 
select rows.  It returns group identifiers and aggregate functions.  If you try 
to select a column which is not included in the GROUP BY clause, you get a 

Re: DateTime limits

[Michael Stassen]

Ben Clewett wrote:
snip
 C# has two DateTime constants:

 DateTime.MinValue = '0001-01-01 00:00:00.000'
 DateTime.MaxValue = '-12-31 23:59:59.999'

snip

 MySQL really doesn't like these values, it shows warnings:

 +-+--+-+
 | Level   | Code | Message |
 +-+--+-+
 | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' |
 +-+--+-+

 The real problem with these warning is:

 1. This date is legal, if a little unlikely.
 2. Any warning crash MySql.Data.dll!!!
snip

Ben Clewett wrote:
 Hi Barry,

 This will happen when comparing against a TIMESTAMP field.

 CREATE TABLE a ( t TIMESTAMP );

 SELECT * FROM a WHERE t  '0001-01-01 00:00:00';
snip

Ben Clewett wrote:

Hi Barry,

  Well removing 'explicit' warnings for every user having problems with
  3rd party modules would have mysql without any warnings nowadays ;)
 
  i think that your mono should get more stable.

I completely take this on board.  This is a bug outside MySQL.

Warnings are very useful.  When we test code and get a warning, this 
almost always results in us re-coding.  Eg:


... WHERE '2006-06-06'  '2006-06-06 12:00:00'

This will give a valid warning, this is bad SQL, time for a re-code!


This will not give a warning, as it is perfectly valid sql.  (It will simply 
return no rows, as the WHERE clause condition cannot be satisfied.)  Why do you 
think otherwise?



BUT my warning is a special case:
  - It's not bad SQL.


Yes, it is bad sql.  Your WHERE clause is

  WHERE t  '0001-01-01 00:00:00'

As t is a TIMESTAMP column, mysql must convert the DATETIME constant to a 
TIMESTAMP, but '0001-01-01 00:00:00' is not a valid TIMESTAMP.  Hence the warning.



  - Because of the C# DateTime object, this happens often.

Therefore a candidate for dropping.  Which will also avoid bugs in badly 
written MySQL clients :)


But, I've probably said enough on the point :)

Ben


According to the manual 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html, the 
officially supported range for DATETIME columns is '1000-01-01 00:00:00' to 
'-12-31 23:59:59', though '0001-01-01 00:00:00' seems to work in practice.


From the same manual page, the officially supported range for TIMESTAMP columns 
is '1970-01-01 00:00:00' to partway through the year 2037.  In practice, 
TIMESTAMP columns take timezone into account.  Hence, as my offset from GMT is 
-5, the earliest TIMESTAMP I can get away with is '1969-12-31 19:00:01'.  That is,


  SELECT * FROM a WHERE t  '1969-12-31 19:00:00';

produes a warning, but

  SELECT * FROM a WHERE t  '1969-12-31 19:00:01';

does not.

I believe your problem begins with your assumption that DATETIME and TIMESTAMP 
are interchangeable.  They are not.  You would probably be fine using C#'s


  DateTime.MinValue = '0001-01-01 00:00:00.000'

for actual DATETIME columns, but it just isn't valid for TIMESTAMP columns. 
That said, mysql manages to do the right thing when faced with this query.  As 
'0001-01-01 00:00:00.000' is earlier than the first valid TIMESTAMP, it is 
converted to a TIMESTAMP of 0, one second before the earliest valid TIMESTAMP, 
and the query proceeds, giving the results you expected, I believe.  It also 
issues a warning to let you know what it did.


This is where the second problem comes into play.  Your system crashes on 
warnings!  As you've already admitted, this is a bug in the .NET MySQL 
library.  The solution seems plain.  The library must be fixed to handle 
warnings.  Mysql should not be changed back to the old days of silently changing 
your query without issuing warnings.


Michael







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

Re: Need help querying a database of polynomials

[Michael Stassen]

Lew E. Lefton wrote:
 Hi,

 I hope this is an appropriate place to ask this question, if you think
 it is better suited for another list/forum, please let me know.

 I have a table that looks like this:

 mysql  select polynomial_id, term_id from polynomial;
 +---+-+
 | polynomial_id | term_id |
 +---+-+
 | 1 |   1 |
 | 1 |   2 |
 | 1 |   3 |
 | 1 |   4 |
 | 2 |   1 |
 | 2 |   2 |
 | 2 |   3 |
 | 2 |   4 |
 | 2 |   5 |
 | 3 |   1 |
 | 3 |   2 |
 | 3 |   3 |
 | 3 |   5 |
 +---+-+

 which represents, say, three polynomials,

   the first is a sum of 4 terms (term1 + term2 + term3 + term4),
   the second is a sum of 5 terms (term1 + term2 + term3 + term4 + term5),
   the third is a sum of 4 terms (term1 + term2 + term3 + term5),
   etc.

 I am storing the polynomials in this way because I may need to store
 very large polynomials.  The table may grow to millions of rows before
 I'm done, with potentially many of the same terms appearing in many
 different polynomials.  Thus I have the terms stored in a separate
 table. Also, this method of storage makes the table easily searched
 (e.g. find all polynomials which have term 2).

 If I have another polynomial, say the sum of terms 1,3,4, and 5, how can
 I quickly search this database to see if it's already been stored?
 Actually, I would eventually like to have a function (using appropriate
 API) which, when given a list of terms, returns the polynomial_id
 regardless of whether it is the result of a new insert or a successful
 lookup.

 I tried variations of this
SELECT DISTINCT polynomial_id FROM polynomial
 WHERE term_id in ('1','2','3','4')
 but I get

 +---+
 | polynomial_id |
 +---+
 | 1 |
 | 2 |
 | 3 |
 +---+

 when I really just wanted '1'. I suspect some subquery magic (e.g.
 restricting to only those polynomials with exactly a count of 4 terms)
 would give me a nice efficient solution, but I am not an SQL expert and
 I have not been succesful in crafting the appropriate query.

 Thanks in advance for your help.  I am happy to provide more details if
 necessary, but I have tried to distill the essence of the problem by this
 simple example.

 Cheers,
 Lew Lefton


Peter Brawley wrote:

SELECT DISTINCT polynomial_id
FROM polynomial p1
INNER JOIN polynomial p2 ON p1.term_id=1 AND p2.term_id=3
INNER JOIN polynomial p3 ON p2.term_id=3 AND p3.term_id=4
INNER JOIN polynomial p4 ON p3.term_id=4 AND p4.term_id=5


That won't work, as each join is missing a crucial condition.  Also, as each 
joined table has a polynomial_id, you need to specify which one you want. 
Finally, if we get the joins right, DISTINCT shouldn't be needed.  I think you 
meant to say


#1
  SELECT p1.polynomial_id
  FROM polynomial p1
  JOIN polynomial p2 ON p1.polynomial_id = p2.polynomial_id AND p2.term_id=3
  JOIN polynomial p3 ON p2.polynomial_id = p3.polynomial_id AND p3.term_id=4
  JOIN polynomial p4 ON p3.polynomial_id = p4.polynomial_id AND p4.term_id=5
  WHERE p1.term_id=1;

I expect that this query, and most of the ones given below, would definitely 
benefit from a multi-column index on (polynomial_id, term_id).  Even then, 
however, I don't think this query will scale very well.  Adding a join per term 
rapidly gets expensive.  Here's an equivalent query without joins:


#2
  SELECT polynomial_id
  FROM polynomial
  WHERE term_id IN (1,3,4,5)
  GROUP BY polynomial_id
  HAVING COUNT(*) = 4;

It should scale much better (and it's easier to write).

Unfortunately, neither query is quite right.  Both will return all polynomials 
which *contain* terms 1, 3, 4, and 5, not the one polynomial (if it exists) 
consisting of precisely those terms and no others.  You would have to do a 
subsequent check of the returned polynomials to see if they had any more terms.


In other words, there are actually two conditions: The polynomial must have all 
the required terms, and it must have the right number of terms.  We need to 
modify the query to take both conditions into account.  This means we cannot put 
the matching terms condition into the WHERE clause, as I did above, because then 
we won't see any other terms.  Instead, we must count total terms and matching 
terms, like this:


#3
  SELECT polynomial_id,
 COUNT(*) AS terms,
 SUM(term_id IN (1,3,4,5)) as matched_terms
  FROM polynomial
  GROUP BY polynomial_id
  HAVING terms = 4 AND matched_terms = 4;

I wrote it that way to make it clear what I'm doing.  If you prefer not to see 
the counts, you can do it this way:


#4
  SELECT polynomial_id
  FROM polynomial
  GROUP BY polynomial_id
  HAVING COUNT(*) = 4 AND SUM(term_id IN (1,3,4,5)) = 4;

Now, that's almost certainly a full 

Re: Help with query: Row number / Rank from a query...

[Michael Stassen]

Dan wrote:

I have a regular query lets say:


Better to show the real query, rather than a simplified version.  Simplified 
requests get you simplified answers.  What seems like a simplification to you, 
may in fact hide a crucial piece of information, thus preventing anyone from 
giving you a solution to your real problem.  We need the real query to give a 
real answer.



SELECT user, points FROM results ORDER BY points DESC

so I get:

userpoints
---
john23
steve   17
bill14

 From this I want to get a particular users 'rank' or row number from  
the query.  I was hoping for a single line elegant query for this.


I thought of this:

SET @i = O;
SELECT @i:= @i + 1 as row, SUM(IF(user = 'steve',@i,0)) as rank, user  
FROM points ORDER BY points DESC GROUP BY id;


That's not your real query, as the table name seems to have changed from 
results to points.



but I get:

rankuser
---
0john
2steve
0bill


I don't think so.  Your select has a column named row, but it's missing in your 
output.


From your description of the problem, I cannot tell why you made the second 
query so complicated.  What is wrong with the following, simpler query?


  SET @i = 0;

  SELECT @i:= @i + 1 AS rank, user
  FROM results
  ORDER BY points DESC;

It should give

  rankuser
  ---
  1   john
  2   steve
  3   bill

as the results.  Isn't that what you want?


which does not really help me

Any ideas?  I tried the manual, Google and the list with no sucsess.

Thanks,

Dan T


If you show us what you want, instead of something that isn't what you want, 
someone may be able to tell you how to get it.


Michael


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

Re: grant modify, doc on grant.

[Michael Stassen]

Gilles MISSONNIER wrote:
 Hello
 How to set  FILE privilege enable to an already defined user ?

 It seems that I have to read the all manual for that.
 I cannot find an example in the on line manual.
snip

sheeri kritzer wrote:
 GRANT FILE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd';

 It won't set up a new account, just add the privilege for you.

Gilles MISSONNIER wrote:
 Thank you Sheeri for answering,

 I guess this syntax works for you, but
 for me NO, this DO NOT work ( I run MySQL 4.21 , on Linux Debian sarge )

 mysql GRANT FILE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd';
 ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
snip

Dilipkumar wrote:

Hi,

You can try this option by

grant file  on *.* to [EMAIL PROTECTED] identified by 'db123';
Query OK, 0 rows affected (0.03 sec)

For all the Databases.


The FILE privilege is a global privilege 
http://dev.mysql.com/doc/refman/5.0/en/grant.html, so it can only be granted 
on *.*, as Dilipkumar suggests.  Also, once you've created a user and set a 
password, you only need the IDENTIFIED BY clause in your GRANT statements if 
you are *changing* the password.  Without that clause, the current password 
stays in effect.  Hence, you need


  GRANT FILE ON *.* TO [EMAIL PROTECTED];

Michael

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

Re: grant modify, doc on grant.

[Michael Stassen]

Dilipkumar wrote:

Dude listen,

You can given file privileges to existing users  for all the Db's as

if you specify as
grant file on database.* to [EMAIL PROTECTED] identified by 'passwd'; [ 
*Error* ]


If you mention to all the DB for a user as:
grant file on *.* to [EMAIL PROTECTED] identified by 'passwd';

Try this, correct me if i am worng.This might help you out.


Perhaps I am misreading, but this sounds as if you think I disagreed with you. 
That isn't the case.  I agreed your solution, *.* instead of db.* is 
correct, referenced the manual page which confirms it, and added that the 
IDENTIFIED BY clause is unnecessary for existing users.  That is,


  grant file on *.* to [EMAIL PROTECTED] identified by 'passwd';

will work, but

  grant file on *.* to [EMAIL PROTECTED];

is better (unless you really mean to reset the password).

Michael

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

Re: Get the record with the latest date

[Michael Stassen]

Brian Menke wrote:

Sorry about the last post, I hope this one comes over better.

This is a bit embarrassing because I think this should be a simple WHERE
clause, but it escapes me at the moment.

I have a table with data:


module_id	emailscore date_time 
1		[EMAIL PROTECTED]	8 	5/27/2006 12:07 AM
1		[EMAIL PROTECTED]	9	5/27/2006 12:11 AM
1		[EMAIL PROTECTED]	9	5/27/2006 12:14 AM
1		[EMAIL PROTECTED]	8	5/27/2006 9:46 PM 
2		[EMAIL PROTECTED]	8	5/24/2006 9:46 PM 
1		[EMAIL PROTECTED]	8	5/27/2006 9:46 PM 
2		[EMAIL PROTECTED]	7 	5/27/2006 9:47 PM 
3		[EMAIL PROTECTED]	7 	5/27/2006 9:47 PM 
3		[EMAIL PROTECTED]	8	5/27/2006 9:50 PM 


I'm trying to get a list of each persons completed test (module_id), and the
score, but only for the latest test result (date_time), not all of them. You
can see that Kris and Robert have taken the same test several times. I just
want the latest results (even if the score was worse). I'm not sure how to
grab the latest module_id for each person? Ultimately, I want my result to
look like this

module_id	emailscore date_time 
1		[EMAIL PROTECTED]	9	5/27/2006 12:14 AM
1		[EMAIL PROTECTED]	8	5/27/2006 9:46 PM 
2		[EMAIL PROTECTED]	8	5/24/2006 9:46 PM 
1		[EMAIL PROTECTED]	8	5/27/2006 9:46 PM 
2		[EMAIL PROTECTED]	7 	5/27/2006 9:47 PM 
3		[EMAIL PROTECTED]	8	5/27/2006 9:50 PM 



I looked a few places on google, but they seemed to be suggesting using DESC
in the where clause and I didn't see how that was going to work? I also
looked at DISTNCT, GROUP BY and ORDER BY, which I think is the right
direction, but I can't seem to figure out how to piece them together.

Any ideas are greatly appreciated!

-Brian Menke


For each email-module_id combination, you want the row with the latest (maximum) 
date.  This is actually a FAQ, with solutions in the manual 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html. 
  You should be able to adapt the examples there to your situation, but let us 
know if you need help.


Michael

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

Re: Adding row numbers to a select.

[Michael Stassen]

Chris W wrote:
Is there an easy way to add row numbers to the result of a select 
statement?


With a user variable:

  SET @i = 0;

  SELECT @i:= @i + 1 AS 'Row', ...

Michael

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

Re: Baffled by query error syntax

[Michael Stassen]

Mike Blezien wrote:

Hello,

this is a continued problem we are having from a earlier posting to the 
list regarding a query. We need to calculate the SUM of the column 
'agent_product_time' which is a TIME datatype column and according to 
the manual:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html
this is the way to SUM the total time, which keeps producing a syntax 
error and figure out why


MySQL version 4.1.12
--- 


SELECT c.account_id,a.name,a.company,
SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY)
AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins

ERROR:
#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to

your MySQL server version for the right syntax to use near
'( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins  FROM account 
a LEFT JO' at line 1

--

What would be producing the syntax error here.??


Something is strange here.  The piece of your query quoted in the syntax error 
does not match the query you gave us.  That makes me think you've given us an 
edited version of your query.  It's hard to catch a syntax error if you don't 
give us the actual query.


The piece of the query quoted in the error has a lot of extraneous spaces.  If I 
had to guess, I'd bet that there is a space between SEC_TO_TIME and the opening 
parenthesis in your real query.  That is, you have


  SEC_TO_TIME ( SUM...

instead of

  SEC_TO_TIME(SUM...

The parser distinguishes functions from columns by the presence of a parenthesis 
 attached to the function name.  For example:


  mysql SELECT VERSION();
  +---+
  | VERSION() |
  +---+
  | 4.1.15|
  +---+
  1 row in set (0.00 sec)

but

  mysql SELECT VERSION ();
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
  that corresponds to your MySQL server version for the right syntax to use
  near '()' at line 1

Note that the error message quotes the query starting with the opening 
parenthesis, as is the case for you.


If that isn't it, please copy and paste your actual query into your next 
message.  I'm sure someone will spot the problem.


Michael

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

Re: Baffled by query error syntax

[Michael Stassen]

sheeri kritzer wrote:
snip


MySQL usually gives a syntax error *where* the error happens.  In this
case, it would indicate a problem with SEC_TO_TIME( but there
shouldn't be a problem, both according to the manual AND according to
my example.


The parser reads the query left-to-right and always quotes the first thing it 
doesn't understand.  As often as not, that's the first thing *after* the actual 
error.  Here's a simple example:


  SELECT version ();

SELECT is proper, of course.  Next comes version.  It doesn't have a 
parenthesis attached, so it must be a column name.  Since version is a column, 
it should be followed by a comma, an alias, the word AS, or some operator.  In 
that context, the ( that comes next doesn't make sense, so that is what mysql 
tells you:


  mysql SELECT VERSION ();
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
  that corresponds to your MySQL server version for the right syntax to use near
  '()' at line 1

The actual error, though, is the space right before the quoted part of the 
query.

Michael

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

Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?

[Michael Stassen]

The Nice Spider wrote:
 Hi,

 This query running fine on 4.0.25 but when trying on
 3.23 an error occurs.
 can one help me to find correct command for 3.23?

 DELETE FROM A
 USING A
 RIGHT JOIN B ON B.id = A.sectionid
 WHERE B.id is null

 This task is deleted any row in A that have no items
 on B.

Then that should be a LEFT JOIN, not a RIGHT.

sheeri kritzer wrote:

You are not being honest with us on the list.

Firstly, the error you got:


You have an error in your SQL syntax near 'USING USING
A
RIGHT JOIN B ON B.id = A.sectionid' at line 1
SQL=DELETE FROM A USING A
RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null


indicates that you used the USING keyword twice in your query, which
won't work in any version of MySQL.


I noticed this, too.  As I doubt his tables are named A and B, the join seems 
the reverse of his intention, and he says the query works in 4.0.25, I expect 
this is a pasting/editing error.  Of course, that's what happens when one 
simplifies instead of posting the real query.  In any case, the issue described 
is actually expected behavior.



I tried to replicate what you have:


snip


mysql select * from A USING A RIGHT JOIN B ON B.id=A.sectionid WHERE
B.id is null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'USING A RIGHT JOIN B ON B.id=A.sectionid WHERE
B.id is null' at line 1

As you can see, on MySQL 4.1.12 I'm getting an error.  ( I used select
* from instead of delete from because that's how I test out delete
queries to make sure I don't do something dumb).


That's a good idea, but FROM A USING A RIGHT JOIN B is valid syntax for 
DELETEs, not FOR SELECTs.



I think you don't want the USING A at all:
select * from A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null;

works just fine for me.


Yes, that's the corresponding SELECT statement, but he is trying to DELETE rows.


And it's true that in 3.23 you could not do a multiple-table UPDATE
(that was introduced in 4.0.0).  So I'm guessing that's what you
really meant.


That's the issue.  The query

  DELETE FROM A
  USING A LEFT JOIN B ON B.id = A.sectionid
  WHERE B.id is NULL;

is a perfectly valid multi-table DELETE statement 
http://dev.mysql.com/doc/refman/4.1/en/delete.html.  The problem is that 
multi-table DELETE was added in 4.0.  Hence the syntax error he gets in 3.23.



Why are you even bothering, though?  why not just use

DELETE FROM A WHERE sectionid IS NULL;
DELETE FROM B WHERE id IS NULL;

?  Because that's all you're really doing in those queries.


No, that's not equivalent at all.  First, he doesn't want to delete any rows 
from B.  He only wants to delete rows in A that don't have corresponding rows in 
B.  Second, the rows in A to be deleted have non-NULL sectionids.  The 
sectionids just don't have corresponding rows in B.


I believe the answer is that there isn't a way to fix the syntax for 3.23, 
because 3.23 simply doesn't support multi-table deletes.  An alternative 
solution is needed.


One option would be to do this programmatically.  Collect the sectionids with a 
SELECT, then delete them with a separate query.


Another option would be to select the good rows into a new table, then use this 
new table to recreate table A.  Something like


  CREATE TABLE A2
  SELECT A.*
  FROM A JOIN B ON A.sectionid = B.id;

then

  DELETE FROM A;
  INSERT INTO A SELECT * FROM A2;

or

  RENAME TABLE A TO A_old, A2 TO A;

Michael


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

Re: WHERE doesn't work

[Michael Stassen]

Tom Lobato wrote:



From: Nicolas Verhaeghe [EMAIL PROTECTED]
Echo the UPDATE query itself and then copy/paste it here that way 
we'll see

what is wrong.


   I already tried it. See some msgs before this. No sucess. When I 
paste the exactily same command to the mysql client, all works fine 
(only the right record is updated).


   Tom


Tom,

With all due respect, there is almost certainly an error in your code.  I've 
been on this list for years, and so far as I can recall, every instance of mysql 
appearing to do something strange from php turned out to be a flaw in the php 
code, not a problem with mysql.  Many of us on the list perform updates with 
WHERE clauses via php without problems.  The most likely explanation is that 
there is something wrong with the WHERE clause as sent to mysql by your php app.


So, you can keep repeating that it doesn't work, and we can keep flailing away 
with blind guesses, but I expect you won't get a solution until you actually 
show us the code.


Also, you've told us that your PHP Version is 4.4.2, and your Mysql client API 
version is 3.23.49, by which I expect you mean that your copy of php was built 
with the 3.23.49 version of the mysql client library.  Unless I've missed it 
though, you haven't told us what version of mysql you have, how it was installed 
(official binary, some other binary, compiled yourself?), and what OS it's on. 
That information may help.


Michael

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

Re: How to Find Most Recent Autoincrement Index Assigned???

[Michael Stassen]

David T. Ashley wrote:

I'm using PHP, and I sometimes INSERT new records in a table.  MySQL assigns
a new autoincrement int field on each INSERT ... nothing surprising there.
It goes 1, 2, 3, etc.

What query can I use to find out what value this int autoincrement assigned
field was?  I could of course SELECT based on what was just inserted, but
that seems inefficient.

Thanks for any help,
Dave.


LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/4.1/en/information-functions.html

Michael

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

Re: bug in simple select, what is going on?

[Michael Stassen]

kmh496 wrote:

hi,
i am running a join query between the german_english table and the
user_todo_german_english tables, to figure out which words a user is
studying.
the key i am using is a combination of wordid + pos + posn (that is part
of speech == pos)
however, i am not able to correctly do the join because of improper
results.  please see the following

mysql select * from user_todo_german_english where date_col 
'2006-04-22 07:00:00';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  94122 |1 |0 | 2006-04-22 07:04:26 |
| curious | 327400 |1 |0 | 2006-04-22 07:04:40 |
| curious | 327402 |1 |0 | 2006-04-22 07:40:41 |
| curious |  59553 |1 |0 | 2006-04-22 07:40:55 |
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
5 rows in set (0.00 sec)

mysql select wordid,pos,posn from german_english where wordid in
(86851,59553);
++--+--+
| wordid | pos  | posn |
++--+--+
|  59553 | m|1 |
|  86851 | m|1 |
++--+--+
2 rows in set (0.00 sec)

mysql select * from user_todo_german_english where wordid = '86851' and
posn = '1' and pos = 'm' AND mb_id='curious';
+-++--+--+-+
| mb_id   | wordid | posn | pos  | date_col|
+-++--+--+-+
| curious |  86851 |1 |0 | 2006-04-22 08:14:12 |
+-++--+--+-+
1 row in set, 1 warning (0.00 sec)

clearly, pos is ZERO in the database, and the letter 'm' in the query.
or is my brain farting again?


You didn't provide the table definitions (output of SHOW CREATE TABLE works 
well), but it appears that pos is a numeric column in user_todo_german_english, 
but a string column in german_english.


I notice that every constant in your WHERE clauses is quoted.  You need to 
realize that 86851 is a number, but '86851' is a string.  When mysql sees


  WHERE numeric_col = 'some string'

it must convert 'some string' to a numeric value in order to make the 
comparison.  That is merely an invisible time waster when 'some string' is just 
a quoted number.  In the case of


  pos = 'm'

however, you get correct, but unanticipated behavior.  As pos is a numeric 
column, 'm' must be converted to a number for the comparison.  Because m is not 
a valid numeric value, it gets converted to 0.  Hence the result you see.


You can find the numeric value of any string by adding 0 to it.  For example,

mysql SELECT '86851', '86851' + 0, 'm', 'm' + 0;
+---+-+---+-+
| 86851 | '86851' + 0 | m | 'm' + 0 |
+---+-+---+-+
| 86851 |   86851 | m |   0 |
+---+-+---+-+
1 row in set (0.00 sec)

You should do two things:

1) Stop quoting numbers.

2) If 'm' is a valid value for pos, then pos should be a string column rather 
than numeric.


Michael

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

Re: MySQL 4.1.18-64 bit won't start after JS2E update on OS X

[Michael Stassen]

Geoffrey Sneddon wrote:

On 21 Apr 2006, at 17:18, Geoffrey Sneddon wrote:

After the JS2E 5.0 Release 4 update on Mac OS 10.4.6 on the 19th,  
MySQL 4.1.18 64-bit hasn't started. It begins to start up, before  
ending with ERROR!. Any possible reasons/solutions?


Did you check mysql's error log for details?


Managed to fix it: /tmp was broken.

/tmp should be a symbolic link to /private/tmp, and should be owned  by 
root and the group admin. It should have a CHMOD of 755.


The Fix:

sudo rm -r /tmp
sudo ln -s /private/tmp /tmp

All the best,

Geoffrey Sneddon


Are you saying the update changed the ownership, group, or permissions?  I have 
had OS X updates change the permissions on /tmp 2 or 3 times in the past, but 
there was no change for me when I just installed JS2E 5.0 Release 4 update. 
Also, why was it necessary to remove and recreate the link?  Couldn't you just 
reset whatever changed?


Michael


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

Re: describe table : improvement

[Michael Stassen]

[EMAIL PROTECTED] wrote:

Thankyou Gabriel.

So how does one set a column comment then?

Regards

Keith


As part of the column definition, as documented in the manual 
http://dev.mysql.com/doc/refman/5.0/en/create-table.html.


  column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]

Michael

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

Re: Reply / Return Address of this List

[Michael Stassen]

[EMAIL PROTECTED] wrote:
The battle has been fought before - and the list administrator has given 
his reasons why he has not made the requested change. The way the list 
currently behaves is not an accident or omission, but a deliberate 
decision. I do not recall the grounds for that decision - maybe RFCs or 
the behaviour of certain email clients (which probably does not include 
your own), or maybe the fact that an individual reply is often very 
difficult if the default is group reply but no the other way round. But 
before asking for a change, I suggest you go back into the archives and 
find out why the administrator decided as he did, and prepare a refutation 
for his argument at that time, not a general complaint that it doesn't 
suit your personal needs. 


Alec


It's in the list FAQ http://lists.mysql.com/faq.php.

Michael

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

Re: getting started with mysql

[Michael Stassen]

Michael Friendly wrote:
I've just started trying to use mysql (debian/linux 
4.0.24_Debian-10sarge1-log), which I'm finding quite frustrating.

I have a bunch of .csv files to try to import. They all have a first
line containing field names.

 When I try load data ...
I get errors no matter what I try


Don't just try things in hopes of success.  Look up the correct syntax in the 
manual and use it.  It's usually quicker and less frustrating that way.



mysql use milestone;
Database changed
mmysql load data local infile 'categories/milecodes.csv' into table 
milecodes

- fields terminated by ',' enclosed by '' ignore 1 lines
- columns (key,year,where,content,form,itemform,itemcontent)
- ;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'columns (key,year,where,content,form,itemform,itemcontent)'


You have a syntax error, as the message is telling you.  The word columns is 
not part of the correct syntax, so you should leave it out.  See the manual for 
details http://dev.mysql.com/doc/refman/4.1/en/load-data.html.


You also have a problem with your first column name, which I describe below.

mysql load data local infile 'categories/milecodes.csv' into table 
milecodes

- fields terminated by ',' enclosed by '' ignore 1 lines
- ;
ERROR 1146: Table 'milestone.milecodes' doesn't exist


Does it?


mysql drop table if exists milecodes;
Query OK, 0 rows affected (0.00 sec)

mysql create table milecodes
- (keyprimary key,
- yearint,
- where   enum('Eur', 'NAmer', 'Other'),
- content char,
- formchar,
- itemformchar,
- itemcontent char
- );
ERROR 1064: You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'primary key,


You shouldn't use key as a column name, as it's a reserved word.  It's 
possible (if you quote it with backticks *every* time), but not a good idea. 
Also, every column needs a type, including the primary key.  Typically, you use 
an unsigned, auto_increment INT column for the primary key.  You probably need 
something like


  CREATE TABLE milecodes
  ( id INT UNSIGNED NOT NULL PRIMARY KEY,
year INT,
...

The manual gives the complete CREATE TABLE syntax 
http://dev.mysql.com/doc/refman/4.1/en/create-table.html and full details on 
column types http://dev.mysql.com/doc/refman/4.1/en/data-types.html.



I get no more joy from mysqlimport.  What is wrong?

 % mysqlimport --force --local --ignore-lines=1 --fields-terminated-by 
',' --fields-enclosed-by '' 
--columns=key,year,where,content,form,itemform,itemcontent

mysqlimport  Ver 3.4 Distrib 4.0.24, for pc-linux-gnu (i386)
Copyright (C) 2000 MySQL AB  MySQL Finland AB  TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Loads tables from text files in various formats.  The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open 
and

read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...


Well, there's the syntax.  Your command line, quoted above, has

  mysqlimport [OPTIONS]

It appears that you specified neither the database nor the textfile.

Michael

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

Re: Date Field Reverting to 0000-00-00 Format

[Michael Stassen]

Lola J. Lee Beno wrote:
snip
Here's an example of one of the tables where the date was retained 
successfully, with the string to the right of the decimal point being 
trimmed:


1Cobweb2005-01-13 15:21:50.654149
2Lace Weight2005-01-13 15:21:50.654149
3Sock2005-01-13 15:21:50.654149

And the CREATE query for this table:

CREATE TABLE StandardWeightType (
   standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT,
   standard_wt_desc varchar(50) NULL,
   standard_wt_lud  datetime NULL,
   PRIMARY KEY (standard_wt_type_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

select * from standardweighttype:

+-+--+-+
| standard_wt_type_id | standard_wt_desc | standard_wt_lud |
+-+--+-+
|   1 | Cobweb   | 2005-01-13 15:21:50 |
|   2 | Lace Weight  | 2005-01-13 15:21:50 |
|   3 | Sock | 2005-01-13 15:21:50 |
|   4 | Fingering| 2005-01-13 15:21:50 |
|   5 | Baby | 2005-01-13 15:21:50 |
|   6 | Sport| 2005-01-13 15:21:50 |
|   7 | Double Knitting  | 2005-01-13 15:21:50 |
|   8 | Worsted  | 2005-01-13 15:21:50 |
|   9 | Aran | 2005-01-13 15:21:50 |
+-+--+-+

I'm not sure why it worked for this table and not the Yarn table.

I'll try out the proposed solutions later today.  I do need to be able 
to use the date column since I will be inserting new records into the 
database via a web application that I will be developing; I am moving 
the data over from a PostgreSQL database which I'd developed earlier 
since I've decided to concentrate on MySQL for the time being.


I take it back.  It is true that the all-zero datetime is what you get for 
invalid input, so I jumped to the conclusion (sorry) that mysql was treating 
your datetimes with decimals as invalid.  Your reply prompted me to try it, and 
I found, to my surprise, that mysql simply dropped the decimals.


So, why isn't this happening for the yarn data?  My best guess is that there is 
an extra tab right before the datetimes in yarn_date.txt.  When you do the 
import, does mysql report any warnings?  With an extra tab in each row of your 
3-line sample data, I get


  Query OK, 3 rows affected, 6 warnings (0.01 sec)
  Records: 3  Deleted: 0  Skipped: 0  Warnings: 6

at the end.  If you have mysql 4.1 or higher, you can run

  SHOW WARNINGS;

to get the details.  With one extra tab per row, I got

+-+--+---+
| Level   | Code | Message 
 |

+-+--+---+
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 1 
 |
| Warning | 1262 | Row 1 was truncated; it contained more data than there were 
input columns |
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 2 
 |
| Warning | 1262 | Row 2 was truncated; it contained more data than there were 
input columns |
| Warning | 1264 | Data truncated; out of range for column 'yarn_lud' at row 3 
 |
| Warning | 1262 | Row 3 was truncated; it contained more data than there were 
input columns |

+-+--+---+
6 rows in set (0.00 sec)

So, take a look at yarn_date.txt and let us know.

Michael

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

Re: Client does not support authentication protocol requested by server

[Michael Stassen]

Andrew wrote:

In the last episode (Mar 26), Andrew said:


What could ever be the problem with the failing Perl DBI (C-B)
connection, if everything is OK with a C-A connection (with and
without Perl), and, in addition, a command-line C-B connection
works?


The password on B is a new-style password, and perl on C was built with
old mysqlclient libraries?


That is the most likely explanation, I think.


I'm sorry, this seems to be getting overlooked, each time i attempt to
convey it:

For BOTH A and B, the Operating System, the Mysql server and client
versions  configurations, and (apparently) everything else relevant to
mysql, IS IDENTICAL! (including firewall, etc.etc.)


No.  They are *supposed* to be identical, but clearly they are not.  I think 
you're asking the wrong question.  You are asking why two identical instances 
would behave differently.  Well, they wouldn't.  I think a better question is, 
what's different?



Yet, C connects to A, but not to B (within the SAME script and loop)!


Right.  The exact same client works for A, but not for B.  Thus, A and B are 
different.



If it /is/ a matter of new-style vs. old-style password, PLEASE tell me
where else to look.  On both A and B, the files /etc/my.cnf are identical
(with old_passwords=1 in each).  Where else should I look for a
difference in configurations?


First, the global configuration file, /etc/my.cnf, can be overridden by a 
server-specific option file and/or command line options.  You can verify what 
the server on B is doing with


  SHOW VARIABLES LIKE 'old_passwords';

One possibility is that this will return OFF for server B.  I would *guess* 
that this is not your problem, but it is worth checking.


Second, the difference in authentication is that before 4.1, mysql used a 
16-byte password hash, while 4.1.1 and up use a 41 byte hash.  Hence, the 
password column of the user table is 41 bytes long for both server A and B. 
Now, the old_passwords setting causes mysql to create *new* passwords as 16 byte 
hashes by default, *but it has no effect on pre-existing passwords*.  One likely 
possibility is simply that the user's password was created on A *after* setting 
old_passwords to ON, but was created on B *before* setting old_passwords to ON. 
 That would yield a password hash that is 16 bytes on server A, but 41 bytes on 
server B.  As a result, old-style clients can connect to A, but not to B.  In 
that case, you can fix this by resetting the user's password on server B.



If C (with MySQL 4.0.25-standard-log) connects to A (5.1.7-beta), as it
is now (WITHOUT any upgrading), doesn't that indicate that C is capable
of connecting to B as well (B being identical to A)?  (Something akin to
transitivity here...)


It would, if A and B were identical.  Hence A and B are not identical.


completely stumped.

TIA

andrew


If neither of my suggestions helps, let us know.  Someone will surely come up 
with another suggestion.


Michael

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

Re: deadlock - further information

[Michael Stassen]

Rithish Saralaya wrote:
snip


in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
make InnoDB to use a consistent read in the SELECT tables in CREATE ...
SELECT. Read the caveats about the my.cnf option, though.



Thanks. However, whatever is written in
http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on
innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my
my.cnf :o)

Also I believe this variable is available as of MySQL 4.1.4, whereas ours in
4.1.11. Upgradation is not a viable option for us right  now.


As 11  4, 4.1.11 is newer than 4.1.4, so this variable is available to you.

4.1.11 is almost a year old.  Have you read the list of bugs fixed since then? 
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html


Michael

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

Re: newbee error (1044)

[Michael Stassen]

Shawn Sharp wrote:

I get the following error while trying to create the following database

mysql mysql  zm_cre­ate.sql.in
ERROR 1044 (42000): Access denied for user ''@'­loc­al­host' to database
'mysql'


You are logged into mysql as the anonymous user, ''@'­loc­al­host'.  By default, 
the anonymous user does not have permission to create databases.



I tried to run the following script

/usr/bin/mysql_in­stall_db --user­=mysql

It does not create mysql database in the correct directory I still only see
the 2 databases


2?  I see only 1, test.  That's to be expected, though.  You can only see 
databases you have access to.  The anonymous user is not authorized to access 
the mysql db, so it is not listed.  Only dbs you have access to are listed. 
Typically, only the mysql root user has access to the mysql db, so it is only 
listed when you are logged in as root.



/us­r/bin/mysqlshow
+---+
| Databases |
+---+
| test  |
+---+

Thanks


Michael


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

Re: newbee error (1044)

[Michael Stassen]

Shawn Sharp wrote:

Dilipkumar,

Thanks much for the tipit did the job!

Now we query mysql to see if the new mysql database is seen by mysql and it
still only sees test:


No, you can't see the mysql db, because you don't have permission to access it. 
 I expect you are still logged in as the anonymous user, who can only work with 
the test db.  You have some reading to do.  You've run mysql_install_db, now you 
need to secure the initial accounts:


  http://dev.mysql.com/doc/refman/4.1/en/default-privileges.html.

Next you'll want to read up on how mysql's privilege system works:

  http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html

Then you should read about creating and managing user accounts:

  http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html


[EMAIL PROTECTED]:/usr/bin mysqlshow
+---+
| Databases |
+---+
| test  |
+---+
[EMAIL PROTECTED]:/usr/bin


Michael

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

Re: Date Field Reverting to 0000-00-00 Format

[Michael Stassen]

Lola J. Lee Beno wrote:
I have a bunch of data where one of the columns is a date field.  Here's 
a sample of the data that I have:


141415010001  02005-01-15 10:15:42.41837
281512010002  02005-01-15 10:22:37.756594
361635020004  02005-01-15 10:27:26.559838

When I run this query:

LOAD DATA LOCAL INFILE 
'/users/lolajl/documents/development/knitlib/datafiles/yarn_date.txt'

INTO TABLE yarn
(yarn_id, standard_wt_type_id, brand_id, yarn_yardage, mfr_id, 
yarn_meters, yarn_putup, yarn_wt_g, yarn_wt_oz, yarn_discontinued_flg, 
yarn_lud);


The dates all get set to:

-00-00 00:00:00


-00-00 00:00:00 is what you get when you try to insert an invalid datetime. 
 Valid datetimes don't have decimals.  See the manual for details 
http://dev.mysql.com/doc/refman/5.0/en/datetime.html.



As you can see:

|  1 |  4 |   14 |  150 |  1 |  0 | 0   |  0 |  1 |   0 | -00-00 00:00:00 |
|  2 |  8 |   15 |  120 |  1 |  0 | 0   |  0 |  2 |   0 | -00-00 00:00:00 |
|  3 |  6 |   16 |  350 |  2 |  0 | 0   |  0 |  4 |   0 | -00-00 00:00:00 |

(I've deleted as many spaces as I could so as to make this more readable.)

When I delete the numbers to the left of the decimal point in the date 
field in yarn_date.txt, it still gets set to the above format.  I tried 
setting the yarn_lud column to NULL and still the same thing.


Ummm, if you delete the numbers to the *left* of the decimal point,
2005-01-15 10:15:42.41837 will turn into .41837, which is still not a valid 
datetime.  You need to delete the numbers to the *right* of the decimal point 
(which I expect you meant), *and* you need to delete the decimal point.  Then 
you'll have a valid datetime (e.g. 2005-01-15 10:15:42).


Alternatively, you can import your data into a table with a string column in the 
place of yarn_lud.  Something like


  datestring CHAR(28)

should do.  Then you can set yarn_lud to

  LEFT(datestring, 19)

or, if necessary,

  LEFT(datestring, LOCATE('.', datestring) - 1)

Adding NULL to the definition of yarn_lud only means that NULLs are allowed.  It 
has no bearing on correct datetime format, nor on the default value for invalid 
datetimes.



Here is the query that creates this table:

CREATE TABLE Yarn (
   yarn_id int UNSIGNED NOT NULL AUTO_INCREMENT,
   standard_wt_type_id int UNSIGNED NULL,
   brand_idint UNSIGNED NULL,
   yarn_yardage int NULL,
   mfr_id  int UNSIGNED NULL,
   yarn_meters  int NULL,
   yarn_putup   varchar(35) NULL,
   yarn_wt_gint NULL,
   yarn_wt_oz   int NULL,
   yarn_discontinued_flg tinyint NULL,
   yarn_lud datetime NULL,
   PRIMARY KEY (yarn_id),
   CONSTRAINT `fk_yarn_brandid` FOREIGN KEY (brand_id)
 REFERENCES Brand (brand_id),
   CONSTRAINT `fk_yarn_mfrid` FOREIGN KEY (mfr_id)
 REFERENCES Manufacturer (mfr_id),
   CONSTRAINT `fk_yarn_stwgttypid` FOREIGN KEY (standard_wt_type_id)
 REFERENCES StandardWeightType 
(standard_wt_type_id)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The odd thing is that I have three tables with a column for the date and 
the dates are retained properly.  What could be causing the dates to be 
converted to the -00-00 format automatically?


I'm not sure what you think is odd about datetime columns behaving as expected, 
but I am sure you're getting the zero datetime because of invalid input.


Michael


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

Re: Find records not in many-to-many table?

[Michael Stassen]

barney wrote:

Thanks, Stefan,

But that only works if both tables have the same field name, doesn't it? If I
use select FileKey from dl_files left join dl_merges using (FileID) where
FileID  is null MySQL returns
Unknown column 'articles.dl_files.FileID' in 'on clause'.

Correct me if I'm wrong, but wouldn't I have to modify the column name in
one of the tables in order for that to work?  Or is there a syntax in the
join lexicon that I can use to alias one of the column names within the
query?  I can't modify the existing table structure(s).

Apologies if I'm being dense.

Make a good day ...
 ... barn


Well, that's what happens when you don't include relevant information, such as 
your table definitions, in your question.


No, you don't need to modify your tables.  You just need to use the join syntax 
that fits your situation.  USING works when the join column has the same name in 
each table.  Otherwise, you need to use ON.  Hence, you need something like


  SELECT FileKey
  FROM dl_files
  LEFT JOIN dl_merges ON dl_files.ID = dl_merges.FileID
  WHERE FileID IS NULL;

See the manual for details http://dev.mysql.com/doc/refman/4.1/en/join.html.

Michael

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

Re: Full outer join

[Michael Stassen]

Maurice van Peursem wrote:

Hello,

I like to have a full outer join. if you have the following tables:

t1:
id | val
 1 |  A
 2 |  B

t2:
id | val
 1 |  B
 2 |  C

SELECT t1.id, t2.id, t1.val FROM t1 FULL OUTER JOIN t2 ON t1.val=t2.val
ORDER BY t1.id,t2.id

I want to get the following result (and in this order):

t1.id | t2.id | t1.val
  1   |  NULL |   A
  2   |   1   |   B
 NULL |   2   |   C

As far as I can tell this is not possible in MySQL. Is there a way to 
construct a MySQL statement that produces this result?


Maurice


Yes, you can produce this result using the union of two left joins:

  (SELECT t1.id AS 't1_id', t2.id AS 't2_id', t1.val
   FROM t1 LEFT JOIN t2 ON t1.val=t2.val)
 UNION DISTINCT
  (SELECT t1.id AS 't1.id', t2.id AS 't2.id', t2.val
   FROM t2 LEFT JOIN t1 ON t1.val=t2.val)
 ORDER BY t1_id IS NULL, t1_id, t2_id IS NULL, t2_id;

Note I added checks for NULL in the ORDER BY clause, because NULLs ordinarily 
come first, but you wanted them last.


Michael


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

Re: problem with selecting my max bid ..

[Michael Stassen]

Gregory Machin wrote:

Ok I tried the following
SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT
MAX(bid_amount) FROM bids WHERE auto_dealer_id='3');
which gives
+---+-++
| dealer_id | auto_id | bid_amount |
+---+-++
| 3 |  12 |  9 |
+---+-++
1 row in set (0.00 sec)

wich is the max bid overall, what I want is the max bid for each auto_id ...


No, it's the max bid received by auto_dealer number 3.  It's a coincidence if 
that's also the max bid overall.



How would I go about this ?


By following the example in the link I sent.

  SELECT dealer_id, auto_id, bid_amount
  FROM bids b1
  WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id);

You see?  Rows are selected if they have the max bid of all rows with the same 
auto_id.


You keep saying you want the max bid per auto_id, but your examples always 
include restrictions on auto_dealer_id.  That's fine, but it's a separate issue. 
 You can just add any additional restrictions to the main query's WHERE clause:


  SELECT dealer_id, auto_id, bid_amount
  FROM bids b1
  WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id)
AND auto_dealer_id = '3' AND Bid_Status = '1';

Michael

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

Re: problem with selecting my max bid ..

[Michael Stassen]

Gregory Machin wrote:

Hi.
I have the following table

| bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp |
Bid_Status |
+-+-+--++-++-+
|   1 |3 | 3 |12 | 2
| NULL |  1 |
|   2 |3 | 3 |12 | 3
| NULL |  1 |
|   3 |  24 | 3 |12 | 4
| NULL |  1 |
|   4 |  24 | 3 |12 | 5
| NULL |  1 |
|   5 |  24 | 3 |12 | 6
| NULL |  1 |
|   6 |3 |   24 |14 | 4
| NULL |  1 |
|   7 |3 | 3 |13 | 4
| NULL |  1 |
|   8 |  24 | 3 | 12 |7
| NULL |  1 |
|   9 |  24 | 3 |13 | 59000
| NULL |  1 |
| 10 |  24 | 3 |12 | 8
| NULL |  1 |
| 11 |  24 | 3 |13 | 6
| NULL |  1 |


where auto_dealer_id is the dealer who put the car on auction ,
auto_id is the id of the car on auction,
bid_amount is the amount did on the car but dealer_id


what I want is to get the max bid placed for each car and the dealer_id who
placed it.
I currnetly have the following:
mysql SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount'
FROM bids  WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id;
+---+-+++
| dealer_id | auto_id | bid_id | bid_amount |
+---+-+++
| 3 |  12 |  1 |  8 |
| 3 |  13 |  7 |  6 |
+---+-+++

But this output is wrong because if you refer to the source table above the
dealer_id should be 24 in both cases..
What have I missed ?

Many Thanks
Gregory Machin


GROUP BY does not return rows.  It returns group names and aggregate stats.  You 
are grouping on auto_id, so you get random (actually, the first found) values 
for dealer_id and bid_id for each group.  Many systems wouldn't even allow this 
query.  Mysql does allow you to select columns not present in the GROUP BY 
clause as a convenience, but you are warned only to use columns with unique 
values per group 
http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html.


Yours is a FAQ, however, with 3 solutions in the manual 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html.


Michael

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

Re: Query Optimization Question

[Michael Stassen]

[EMAIL PROTECTED] wrote:
Yes, a ranged query should respond faster than a negation. In some cases 
you can seriously improve query performance for a negation query if you 
split it into two range queries unioned together. Here is a pseudo 
example:


This query should be slow due to the table scan it takes to test the 
condition:


SELECT ...
FROM ...
WHERE indexfield  16


This query will use the index if the number of rows with indexfield != 16 is 
small enough ( about 30%).


This query should be noticeably faster due to using ranged queries 
(partial index ranged matches):


(   SELECT ...
FROM ...
WHERE indexfield  16
) UNION (
SELECT ...
FROM ...
WHERE indexfield  16
)


At the very least, you'll want to make that UNION ALL, rather than just UNION, 
so mysql doesn't waste time looking for duplicates to remove.  But even then, 
this may be slower.


First, these two unioned queries may still not use the index.  If the number of 
rows with indexfield  16 is too large (more than about 30%) the first query 
will be a table scan.  If the number of rows with indexfield  16 is too large 
(more than about 30%) the second query will be a table scan.  In fact, if the 
number of rows with indexfield = 16 is less than about 40% of the table, then at 
least one of the two unioned queries is guaranteed to be a table scan.  Worse 
yet, this query stands a good chance of being twice as long as the single, != 
query, because it may require 2 table scans!


Second, even if both unioned queries use the index, the result still may not be 
faster.  If the combined number of matching rows is too high, the full table 
scan should be faster than the indexed lookups.


For example:

  SELECT cat, COUNT(*) FROM inits GROUP BY cat;
  +--+--+
  | cat  | COUNT(*) |
  +--+--+
  |0 | 5743 |
  |1 | 3792 |
  |2 |30727 |
  |3 | 1926 |
  |4 | 7812 |
  +--+--+

19273 rows (38.55%) match cat != 2, with roughly half (~19%) on either side.

First, the != case:

  EXPLAIN SELECT * FROM inits WHERE cat != 2 \G
  *** 1. row ***
 id: 1
select_type: SIMPLE
  table: inits
   type: ALL
  possible_keys: cat_idx
key: NULL
key_len: NULL
ref: NULL
   rows: 5
  Extra: Using where

As expected, a full table scan.

  SELECT * FROM inits WHERE cat != 2;
  ...
  19273 rows in set (0.37 sec)

Now, the unioned range queries:

  EXPLAIN SELECT * FROM inits WHERE cat  2
  UNION ALL
  SELECT * FROM inits WHERE cat  2 \G
  *** 1. row ***
 id: 1
select_type: PRIMARY
  table: inits
   type: range
  possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: NULL
   rows: 5680
  Extra: Using where
  *** 2. row ***
 id: 2
select_type: UNION
  table: inits
   type: range
  possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: NULL
   rows: 6543
  Extra: Using where
  *** 3. row ***
 id: NULL
select_type: UNION RESULT
  table: union1,2
   type: ALL
  possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
   rows: NULL
  Extra:


As hoped, mysql plans to use the index for each query.

  SELECT * FROM inits WHERE cat  2
  UNION ALL
  SELECT * FROM inits WHERE cat  2;
  ...
  19273 rows in set (0.78 sec)

Despite (because of) using the index, this takes more than twice as long!

Of course, if you have to do a table scan ANYWAY (because a value you have 
in a constraint is not in an index) then this won't help. This 
optimization is all about getting the engine to use an index whenever it 
can instead of performing a table scan. Of course, that is not always 
possible.


Even when using an index is possible, it is not always desirable.  I'd suggest 
not trying to outfox the optimizer until you've first determined it is making 
bad choices, and then test to make sure the solution is actually an improvement.


Michael

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

Re: Checking for good update

[Michael Stassen]

[EMAIL PROTECTED] wrote:

On Sun, 12 Mar 2006, Michael Stassen wrote:


[EMAIL PROTECTED] wrote:


looks a bit strange to me.


$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());


please try something like this:


Why?  There's nothing wrong with the above statement.


I've never seen logic like that before. It looks to me like 
fbsd_user is trying to use the OR operator outside an if 
statement.


Is the mentioned in the php manual somewhere Michael?


I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.


You should try it.  It works just fine, and isn't the problem.  The
problem is that you cannot treat the result of an UPDATE as if it were a
SELECT.


Regards 


Keith Roberts


Yes, this is documented.  It's also standard practice (in perl and C as well).

OR is not part of an if statement, it is a logical operator. 
http://www.php.net/manual/en/language.operators.logical.php  A or B has a 
value, true or false, depending on the values of A and of B.  In fact, if A is 
true, then A or B is certainly true, so there's no need to look at B at all. 
This short-circuit evaluation, combined with the fact that every assignment 
returns the assigned value 
http://www.php.net/manual/en/language.expressions.php, makes a statement like 
this possible.


  $result = mysql_query($query) or die('Query error:'.mysql_error());

First, the function mysql_query() is called.  Its return value is assigned to 
$result, *and* returned as the return value of the assignment operator (=).  Now 
we know A.  If mysql_query succeeded, its return value (A) evaluates as true, so 
the or operation must be true, so no need to look at B.  If, on the other hand, 
A is false (mysql_query failed), we must evaluate B to determine the value of 
the or expression.  Of course, to determine the value of B, we have to call 
the referenced function, die().


Michael

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

Re: update using 'set' keyword

[Michael Stassen]

fbsd_user wrote:

Trying to bump the count_of_logons by 1 using this update.
Phpmyadmin shows the count staying at zero.
I think that this   SET count_of_logons = 'count_of_logons + 1' 
is not coded correctly, but I get no errors so can not tell.


Anybody have any ideas?

The table def has   count_of_logons INT,

$sql = UPDATE members 
	   SET count_of_logons = 'count_of_logons + 1'
 WHERE logon_id  = '$logonid' 
 AND logon_pw= '$logonpw'

 AND email_verified = 'Y';



Why are you quoting 'count_of_logons + 1'?  In any case, that's the problem. 
'count_of_logons + 1' is a string.  You are assigning it to an INT, so it must 
be converted to a number.  Strings which do not start with anything numeric 
convert to 0.  For example:


mysql SELECT 'count_of_logons + 1' + 0;
+---+
| 'count_of_logons + 1' + 0 |
+---+
| 0 |
+---+
1 row in set (0.00 sec)

Leave out the quotes to get the expected result:

  $sql = UPDATE members
   SET count_of_logons = count_of_logons + 1
   WHERE logon_id  = '$logonid'
 AND logon_pw= '$logonpw'
 AND email_verified = 'Y';

Michael

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

Re: Query Optimization Question

[Michael Stassen]

Robert DiFalco wrote:
 In a previous database engine I was using an IN was more optimal than a
 . So, for example:

 SELECT * FROM table WHERE table.type IN (1,2,3);

 Where the possible values of type are 0-3, was appreciably faster than:

 SELECT * FROM table WHERE table.type  0;

 I've been playing with the Query Browser and checking out the
 optimization documents and haven't been able to make a clear call on
 whether or not this is also the case with MySQL/InnoDB.

Mladen Adamovic wrote:
 IN should be faster implemented with both hash tables and BTREE's so
 nowadays it should also be faster than  as all MySQL implementation
 AFAIK use those well known data structures for indexes.

[EMAIL PROTECTED] wrote:
 YES, YES, YES! This is definitely an optimization.

 When you say IN or =, you are asking for matching values. Matches can
 come from indexes. When you say  or NOT IN, you are asking for
 everything BUT matches.  In order to evaluate a negative, the database
 engine (and this is usually true regardless of database server) almost
 always performs a full table scan to test every row to make sure it is
 either  or NOT IN. At the very best, they have to perform a full index
 scan which is still less efficient than  ranged or values-based lookups.

 It's when you get into the situation where you are matching against dozens
 of IN-clause items that you may run into slowdowns again. Until you reach
 2 or 3 dozen terms (depending on your hardware) you should be faster with
 an IN comparison than a  or a NOT IN comparison. An optimization to
 search for BUT a term or two is to create a temporary table of all of your
 terms and delete the exact ones you want to exclude. Put an index on your
 temp table then JOIN that back into your query again (replacing the huge
 IN clause).  The database will match index to index and things will get
 fast again. This technique can scale up to some really big queries.

 Always try to code for the affirmative tests. Your users will thank you.

Implicit in Mladen and Shawn's answers, but never actually mentioned in the 
original post, is the presence of an index on the type column.  This is probably 
obvious to all concerned, but I mention it for completeness: without an index on 
type, there is no difference between type IN (1,2,3) and type != 0.  That 
is, the question is not whether IN is better than !=, but rather which will 
allow the optimizer to make good use of the index on type.


I find mysql's optimizer is pretty good with well-written queries, as long as 
subqueries aren't involved, so my initial reaction was to expect no difference. 
 After all, as the optimizer considers the WHERE conditions and the available 
indexes, it is certainly possible, at least theoretically, for it to notice that 
type IN (1,2,3) and type != 0 are identical conditions.  That is, a clever 
optimizer could treat them identically.  Shawn's and Mladen's answers gave me 
pause, however, and aroused my curiosity, so I decided to test:


  SELECT VERSION();
  +---+
  | VERSION() |
  +---+
  | 4.1.15|
  +---+

  SELECT cat, COUNT(*) FROM inits GROUP BY cat;
  +--+--+
  | type | COUNT(*) |
  +--+--+
  |0 |44224 |
  |1 | 1919 |
  |2 | 1931 |
  |3 | 1926 |
  +--+--+

  mysql EXPLAIN SELECT * FROM inits WHERE cat IN (1,2,3) \G
  *** 1. row ***
 id: 1
select_type: SIMPLE
  table: inits
   type: range
  possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: NULL
   rows: 8117
  Extra: Using where

  mysql EXPLAIN SELECT * FROM inits WHERE cat != 0 \G
  *** 1. row ***
 id: 1
select_type: SIMPLE
  table: inits
   type: range
  possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: NULL
   rows: 8120
  Extra: Using where

As you can see, the optimizer plans to use the index in both cases, examining 
8117 rows in the IN case and 8120 rows in the != case, to get 5776 matching rows 
out of 50,000 (about 11.55%).


On the other hand, it makes a difference how many rows will match.  What is the 
distribution of values of type?  If the number of matching rows is more than 
about 30% of the table, the optimizer won't use an available index in any case. 
 For example,


mysql EXPLAIN SELECT * FROM inits WHERE cat IN (0,1,2) \G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: inits
 type: ALL
possible_keys: cat_idx
  key: NULL
  key_len: NULL
  ref: NULL
 rows: 5
Extra: Using where

mysql EXPLAIN SELECT * FROM inits WHERE cat !=3 \G
*** 1. row ***
   id: 1
  select_type: SIMPLE
table: 

Re: SQL Foreign Key

[Michael Stassen]

Andreas Krüger wrote:

1) Sorry for not giving you the version:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

*mysql SELECT VERSION();
+---+
| VERSION() |
+---+
| 5.0.17-nt |
+---+*
1 row in set (0.00 sec)

snip

InnoDB Status after failing instruction:
*mysql SHOW INNODB STATUS;*
...

LATEST FOREIGN KEY ERROR

060312 13:40:40 Transaction:
TRANSACTION 0 7440, ACTIVE 0 sec, OS thread id 3048 inserting, thread 
declared

nside InnoDB 499
mysql tables in use 2, locked 2
6 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 7, query id 29 localhost 127.0.0.1 root copy to tmp table
ALTER TABLE friends ADD FOREIGN KEY (category) REFERENCES category(id)
Foreign key constraint fails for table `kontakte/#sql-714_7`:
,
 CONSTRAINT `#sql-714_7_ibfk_1` FOREIGN KEY (`category`) REFERENCES 
`category`

(`id`)
*Trying to add in child table, in index `category` tuple:
DATA TUPLE: 2 fields;
0: len 2; hex 0001; asc   ;; 1: len 2; hex 0001; asc   ;;
*
But in parent table `kontakte/category`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 696e66696d756d00; asc infimum ;;

The further error message from InnoDB status is again quite meaningless 
and seems to make no sense. I have not found anything on the forums to 
the question. This is somehow amazing, since it' s a quite simple 
constellation that I am trying to establish here. Does someone really 
understand the InnoDB error message and what's going wrong? It is not a 
name clash either, for I tried giving the `category` column another name.


Andy Krueger


The foreign key cannot be created because there is at least one row in friends 
with a value for category which does not exist as an id in table category.  In 
general, you cannot create a constraint that is already violated.  Try this:


  SELECT f.id, f.category
  FROM friends f
  LEFT JOIN category c ON f.category=c.id
  WHERE c.id IS NULL;

You will get a list of problem rows in table friends.  Either change them to 
point to existing categories, or add matching records to table categories.  Then 
you should be able to add the foreign key.


Michael


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

Re: Checking for good update

[Michael Stassen]

fbsd_user wrote:
 Using this code I get this error message.

 Warning: mysql_num_rows(): supplied argument is not a valid MySQL
 result resource in /usr/local/www/data/mls_verifyemail.php on line 49

 What code should I use to check if the update worked or not?

Your second line does just that.

 $query = UPDATE members SET email_verified='X' WHERE
 logon_id='.$logonid.';

 $result = mysql_query($query) or die('Query couldn\'t
 executed:'.mysql_error());

Right there.  You told php to die and print the error from mysql if the update 
failed.


 if (mysql_num_rows($result) == 1)
   {
 // the user id and password match,
 print(User id on db);
}
else
{
 //$errorMessage = 'Sorry, wrong user id / password';
 print(Sorry, wrong user id / password);
}

The rest of the code only makes sense for a select, but you did an update. 
mysql_num_rows() tells the number of rows of data returned by a select.  No 
select, no returned rows, no mysql_num_rows()  --  hence the error message.


Logan, David (SST - Adelaide) wrote:
 Perhaps reading the manual would be a good start at
 http://us3.php.net/manual/en/function.mysql-num-rows.php

 It clearly states

 Retrieves the number of rows from a result set. This command is only
 valid for SELECT statements. To retrieve the number of rows affected by
 a INSERT, UPDATE, or DELETE query, use mysql_affected_rows().

 You are using a function not valid for an UPDATE

fbsd_user wrote:

 All ready read that and its clear as mud.
 It tells you update is different but gives no example.

 I asked in my post what should I code for checking the result of a
 update.

Please reread David's post, as he gave you the answer in the quote from the 
manual.  Use mysql_affected_rows() to get the number of rows affected by an 
UPDATE.  There are a few caveats, however.  See the manual for details 
http://www.php.net/manual/en/function.mysql-affected-rows.php.


fbsd_user wrote:
 Maybe I have the overall logic wrong.

I think perhaps you do.

 I don't do a select query first to see if that record is there.
 I just try to update it.

Then it will be difficult to know for certain, in that scenario, whether or not 
a row was matched.


 If the logonid key in the update request is on the table, the record
 does get updated. I can verify that using phpmyadmin.

 When the logonid key in the update request is not on the table, the
 results checks still take the record successful updated condition. A
 look at the table using phpmyadmin shows me that there is no record
 matching that logonid key.

That's right.  Your query is

  UPDATE members SET email_verified='X' WHERE logon_id=$logonid;

You are asking mysql to set the email_verified column to 'X' for every row in 
the table which has the given logon_id.  The success of this query is not 
determined by the number of rows matched.  If no rows match, mysql will 
successfully update 0 rows (just as it will successfully update 13 rows, if 
13 rows match).  That is not an error, as it is precisely what you requested.


 My last test I tried this
 if ($results == TRUE)

 and still the update was successful condition is taken even when the
 update key value is not on the table. I would expect the update was
 unsuccessful condition to have been taken.

No.  The query worked.  It successfully updated all 0 matching rows.

 So the basic question boils down to why does the successful
 condition always get taken even when there is no match on the table
 for the key value being used?

Because success of a query does not depend on the existence of rows which match 
its WHERE clause.  Success depends on parsing and executing the query.


 This is testing a new developed script, so there may be a logic
 error in how things are done in the script. But I need to have the
 results of the update to be able to tell difference between a good
 update and one where the key used to target the record is not on the
 table.

 How do you suggest I should code the result condition test?

As David suggested, you can use mysql_affected_rows() to find how many rows were 
affected by your update.  This will certainly be 0 if there is no matching row. 
 You need to be aware, however, that it will also be zero if the matching 
row(s) already has email_verified='X', because mysql will not waste time 
changing a row for which the new value is the same as the old value.  If that's 
a possibility, you could try parsing the outut of mysql_info().  See the manual 
for details http://www.php.net/manual/en/function.mysql-info.php.


Michael

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

Re: Checking for good update

[Michael Stassen]

[EMAIL PROTECTED] wrote:

looks a bit strange to me.


$result = mysql_query($query) or die('Query couldn\'t
executed:'.mysql_error());


please try something like this:


Why?  There's nothing wrong with the above statement.


// build the query - (that's OK)
$query = UPDATE members SET email_verified='X' WHERE
logon_id=' .$logonid. ';
 
// send the query to the server - save the result resource

$res = mysql_query($query);

// test for the result of the above query
if(!$res)
  {
  // stop the script if the result is not valid
  die('Query couldn\'t be executed:'.mysql_error());
  }


Fine so far, but the code below repeats the problem.  There are no rows to 
process, because there was no SELECT.



// process a valid result
$row = mysql_fetch_array($res)

if (mysql_num_rows($res) == 1)
  {
  // the user id and password match,
  print(User id on db);
  }
else
  {
  //$errorMessage = 'Sorry, wrong user id / password';
  print(Sorry, wrong user id / password);
  }

I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.


You should try it.  It works just fine, and isn't the problem.  The problem is 
that you cannot treat the result of an UPDATE as if it were a SELECT.


snip


Using this code I get this error message.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /usr/local/www/data/mls_verifyemail.php on line


Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.


The query was valid, and it worked, but mysql_num_rows() is only for SELECTs.

Michael

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

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one 
is preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has duplicate 
entries, which you don't want.  If you do not want duplicate entries, you should 
not allow them.  That is, you need a UNIQUE constraint on the id column.  You 
won't be able to add one, however, until you remove the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to toss. 
 In your example, you always keep the row with the lowest recno, but your 
description implies that when one of the duplicates has type = 4, you want to 
keep that one, regardless of recno.  Assuming that to be true, you need 
something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno  t2.recno AND t1.type !=4)
 OR (t1.recno  t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for details 
http://dev.mysql.com/doc/refman/4.1/en/delete.html.)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad idea, 
but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it is 
just lack of imagination on my part, but I'm having trouble seeing why you would 
want to do things this way.  Having a column which should be unique, but isn't, 
and wanting to renumber your primary key column are both red flags.  I also find 
it strange that you seem to want to find the unique ids with type = 4, but you 
are changing every other type to NULL in the process.  Why not just select what 
you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us much).  On 
the other hand, if you carefully describe what you are trying to accomplish, one 
of the many experts on the list may well be able to supply you with a better way.


Michael

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

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't grouping 
by type, and CASE is not an aggregate function.  Mysql will use the value for 
type from the first row it finds for each id in the CASE statement.  The 
following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler suggestion

  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael

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

Re: getting COUNT() TO return 0 for null matches in a query, how?

[Michael Stassen]

Ferindo Middleton Jr wrote:
I have the following query which counts the records from a table called 
registration that have an schedule_id that matches a record in another 
table called schedules.  The below query works fine but how can I get it 
to return a COUNT() of 0 each instance where there is  no record in the 
registration table that matches a schedules.id record?

   SELECT schedules.id, schedules.start_date, schedules.end_date,
   COUNT(schedules.id) FROM schedules, 
registration_and_attendanceWHERE 
registration_and_attendance.schedule_id  = schedules.id

   GROUP BY schedules.id ORDER BY start_date



Your query, rewritten to use an explicit join (with the join condition in the ON 
clause, where it belongs, rather than in the WHERE clause) and table aliases:


  SELECT s.id, s.start_date, s.end_date,
 COUNT(s.id)
  FROM schedules s
  JOIN registration_and_attendance ra ON ra.schedule_id  = s.id
  GROUP BY s.id
  ORDER BY s.start_date;

This query finds only rows from schedules that have matching entries in 
registration_and_attendance.  As you have seen, you can't count what isn't there.


If you change the JOIN to a LEFT JOIN, however, you are guaranteed to get an 
output row for every single id in schedules (the table on the left).  For each 
schedules.id that is not present in registration_and_attendance, you get a 
result row with NULLs for each selected column in registration_and_attendance 
(the table on the right).


Next, we need to take advantage of the fact that count(field) only counts 
non-NULL values of field.  The key is to count something in the table on the 
right, say registration_and_attendance.schedule_id, because it will be NULL (and 
have a 0 count) when there are no matches.


Thus, I believe the query you want is

  SELECT s.id, s.start_date, s.end_date,
 COUNT(ra.schedule_id)
  FROM schedules s
  LEFT JOIN registration_and_attendance ra ON ra.schedule_id  = s.id
  GROUP BY s.id
  ORDER BY s.start_date;

Michael

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

Re: Timestamp error

[Michael Stassen]

pedro mpa wrote:

Greetings!

I am building a website using MySQL 5.0.18 and PHP 5.1.2.
When I try to insert in a table a timestamp value from php's mktime() I get
the following error:
1292: Incorrect datetime value: '1139776424' for column 'access_date' at row
1

The sql for the table is:
CREATE TABLE `members_acs` (
  `id` int(17) unsigned NOT NULL auto_increment,
  `member_id ` int(13) unsigned default NULL,
  `access_date` timestamp NOT NULL default '-00-00 00:00:00',
  `ip` varchar(15) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Are timestamps different from php to mysql?

When I first create a timestamp field it defaults to CURRENT_TIMESTAMP then
the following will default to -00-00 00:00:00 . Is this the normal
behaviour? What am I doing wrong?

Thanks in advance.

Pedr.


The mysql TIMESTAMP is not a unix timestamp, so it isn't compatible with the 
output of php's mktime().  See the manual for the details of the TIMESTAMP type:


http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html
http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Michael


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

Re: How to select data if not in both tables?

[Michael Stassen]

Bob Gailer wrote:

Peter Brawley wrote:


Grant,
If I want to select all the products that are in the product_table, 
but not in the sale_table, how to make the query?  The product_table 
has all the products, but the sale table is a subset of the 
product_table.

  SELECT * FROM product_table p
LEFT JOIN sale_table s USING (prod_id)
WHERE s.prod_id IS NULL;


I have not tested that but I don't think it will work. Try:


Why not?  This is the classic LEFT JOIN solution.  It will work in all versions 
of mysql.


SELECT item_name FROM product_table WHERE prod_id not in (select prod_id 
from sale_table);


This will work only in mysql 4.1+, and will almost certainly be slower, because 
mysql's optimizer tends to treat the subquery as dependent, meaning it will be 
rerun for each and every row of the product_table.


Michael


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

Re: Returning values from an INSERT

[Michael Stassen]

bob pilly wrote:

Hi everyone, im new to SQL and have a question that someone can hopefully answer
  
  If i am inserting a new record into a table that has an auto_increment  field in it, is it possible to get the value of that field returned  automatically instead of having to do a SELECT..
  
  For example my user table has:
  
  userid -- auto_increment and primary key

  username
  
  So if i:
  
  Insert into user (username) VALUES ('Test User');
  
  i dont know what userid was assigned to that particular user and are having to:
  
  SELECT userid FROM user where username = 'Test User';
  
  to get the id. 
  
  Is that the only way to do it?
  
  hope it isnt a stupid question and thanks for any help in advance!
  
  Cheers
  
  Bob


LAST_INSERT_ID() 
http://dev.mysql.com/doc/refman/4.1/en/information-functions.html

Michael

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

Re: Column in field list is ambiguous

[Michael Stassen]

Grant Giddens wrote:
 Hi,

 I'm using mysql 4.1.14 and and getting this error:

 Column 'asin' in field list is ambiguous

 This is the query that is giving me the error:

   SELECT pn_pricecompare_searchdata.prod_id,
  MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
   FROM pn_pricecompare_searchdata ps
   LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
   WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
   ORDER BY score DESC LIMIT 0,10

 The query works fine if I take out the LEFT JOIN

   SELECT pn_pricecompare_searchdata.prod_id,
  MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
   FROM pn_pricecompare_searchdata ps
   LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
   WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
   ORDER BY score DESC LIMIT 0,10

 The searchdata table is MyISAM with fulltext enabled on the fields.  The
 product table is an INNODB table.

 I searched all over and can't find a solution.  I did find one possible
 bug report that might be related: http://bugs.mysql.com/bug.php?id=15607

 Is there any workaround I can use?

 Any suggestions would be greatly appreciated.

 Thanks,
 Grant

Is there a column named asin in the pn_pricecompare_product table?  If so, try 
qualifying asin with the correct table name in your query (use the alias).


Michael

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

Re: Can't Create Foreign Key Constraints

[Michael Stassen]

Lola J. Lee Beno wrote:
I'm trying to create foreign key constraints and keep getting an error 
message 1005 (I did look it up, but didn't see an obvious solution to 
fixing this for my database).


The version I'm using is 5.0.17-max.  I used Mysql WorkBench to create 
the database schema and had it generate the sql script.


I created a table as such:

CREATE TABLE `ows`.`FilmsRatings` (
  `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Rating` VARCHAR(50) NULL,
  PRIMARY KEY (`RatingID`)
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

Then, I created another table as such:

CREATE TABLE `ows`.`Films` (
  `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `MovieTitle` TEXT NULL,
  `PitchText` TEXT NULL,
  `AmountBudgeted` DECIMAL(11, 0) NULL,
  `RatingID` INT(11) NULL,
  `Summary` LONGTEXT NULL,
  `ImageName` VARCHAR(50) NULL,
  `DateInTheaters` DATETIME NULL,
  PRIMARY KEY (`FilmID`),
  CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`)
REFERENCES `ows`.`FilmsRatings` (`RatingID`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE = InnoDB
CHARACTER SET utf8 COLLATE utf8_general_ci;

And got this following error message:

ERROR 1072 (42000): Key column '(not null)' doesn't exist in table

I tried again, this time deleting the CONSTRAINTs details.  Then I tried 
to alter the table as such:


mysql alter table films
- add constraint fk_films_ratings
- foreign key (RatingID) references FilmsRatings (RatingID);

Which produced this error message:

ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150)
mysql drop table films;

This database is being run with InnoDB engine, so I should be able to 
create the foreign key constraints.  So why is this happening?


1) I'm not sure what you are intending with (`(not null)`) in the middle of 
your foreign key definition, but that isn't valid mysql syntax.  See the manual 
for the correct syntax 
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html.


2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an 
INT.  The manual says


   Corresponding columns in the foreign key and the referenced key must have
   similar internal data types inside InnoDB so that they can be compared
   without a type conversion. The size and sign of integer types must be the
   same.

3) Again quoting the manual, You can use SHOW ENGINE INNODB  STATUS to display 
a detailed explanation of the most  recent InnoDB foreign key error in the  server.


Michael

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

Re: MySQL says, Ich don't think so (Subquery woes)

[Michael Stassen]

René Fournier wrote:

SELECT MAX(id) FROM history
WHERE account_id = 216 GROUP BY asset_id

...works. Returns, e.g.: 1234, 3456, 5483, 8382.


SELECT history.* FROM history
WHERE history.id IN (1234, 3456, 5483, 8382 )

...works too. But if I try to combine them using a subquery, a la...


SELECT history.* FROM history
WHERE history.id IN ( SELECT MAX(id) FROM history WHERE account_id =  
216 GROUP BY asset_id )


...it pretty much hangs MySQL. CPU goes to 100%, ten minutes later, I  
have to kill the connection. I can't figure out why. Any ideas? Any  
suggestions?


...Rene


Put EXPLAIN in front of your query to see what mysql does 
http://dev.mysql.com/doc/refman/4.1/en/explain.html.  You will almost 
certainly see that the subquery's select_type is DEPENDENT SUBQUERY, which 
means that the subquery is run for each row of the outer query, rather than just 
once!  Unfortunately, mysql's optimizer is not so good at subqueries yet.


The efficient way to do this is to store the results of the first query (the 
subquery) in a temporary table, then join to it to get the right rows.  Assuming 
id is the primary key of table history, the following should do:


  CREATE TEMPORARY TABLE groupmax (id INT UNSIGNED);

  LOCK TABLES history READ;

  INSERT INTO groupmax
  SELECT MAX(id) FROM history WHERE account_id = 216 GROUP BY asset_id;

  SELECT history.*
  FROM history
  JOIN groupmax ON history.id = groupmax.id

  UNLOCK TABLES;

  DROP TABLE groupmax;

(Adapted from the example in the manual 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html)


Michael

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

Re: Creating REGEXP patterns with use of subquery

[Michael Stassen]

Kim Christensen wrote:

Is there any way to build a REGEXP pattern set by using a subquery?

I have a set of rows in table table, with the column value being
target for my query. That column's content is a bracket separated list
of values, like this:

[118][Word][Integer][Stuff]...
[67][Another word][Another integer][More stuff]...

Now, to get all rows which value string starts with [118], this
does the trick:

SELECT * FROM table WHERE value REGEXP '^\\[118'

And further on, to get all rows which value string starts with
either [21], [42] or [999], this works fine:

SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)'

But I need to be able to do this last query without having to specify
the values by hand, but with a subquery. Imagine the following query:

SELECT id FROM items WHERE parent=5

This gives me a result set of rows which parent columns matches 5. I
would like to use all these results in the last REGEXP query example
above, something like this:

SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items
WHERE parent=5)'

Anyone got any clues?

--
Kim Christensen
[EMAIL PROTECTED]


Gleb has already sent a suggestion to do what you asked.  I'd like to add that 
the problem you're having is one perfect example of why this is not the best 
table design.  It is a bad idea to put multiple values, especially different 
types of values, in one column.  If you have control over this, you should 
change your table so that each value is in its own column.


Change this == to something like this

 value  iid   word   num  stuff
 -- ---  --- ---  
 [118][Word1][6][Something] 118  'Word1'   6  'Something'
 [67][Word2][12][Something else] 67  'Word2'  12  'Something else'

Then your query is simple:

  SELECT * FROM table WHERE iid IN (SELECT id FROM items WHERE parent=5);

Better yet (probably faster), use a join:

  SELECT * FROM table
  JOIN items ON table.iid = items.id
  WHERE items.parent=5;

A simpler query is not the only benefit.  Now that your integers are actually 
stored as integers instead of as strings, the query will run much faster, 
because integer comparisons are an order of magnitude faster than string 
comparisons.


Michael

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

Re: Help Understanding Document Syntax

[Michael Stassen]

Rhino wrote:
First and foremost, thank you very much Michael for correcting my 
mistakes; I _was_ a bit sloppy in my reading of the syntax for the 
statements and that caused some unnecessary errors in my reply to Scott.


However, your corrections are not _quite_ right even now. See below 
where I explain this.


Strange.  It was late when I answered, so I actually tested each statement 
before posting in an attempt to prevent that.


snip

Strangely enough, both of those formulations of the UNIQUE clause fail 
for me with the same error as the mistaken version I first proposed in 
my note to Scott.


This is the current version of my DROP/CREATE:

drop table if exists Purcell01;
create table if not exists Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
-- constraint uk unique index ukix (fname, lname)
-- unique ukix (fname, lname)
) Type=INNODB;

If I run it exactly as shown, with both versions of the UNIQUE clause 
commented, it works fine. But if I uncomment either version of the 
UNIQUE clause, it fails with the same error I mentioned in my previous 
note. I've also tried 'unique(fname, lname)' and that also fails on the 
same error.


Did you add the comma after primary key(empno) when uncommenting one of the 
unique constraint lines?  That's the only thing I can see.


Michael

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

Re: Sort before grouping

[Michael Stassen]

David Förster wrote:

Hi,

is there any way to get datasets sorted before they're grouped by GROUP
BY()?

I have a table of events at different locations and want to select the
newest one for each location.

However SELECT * FROM events GROUP BY location ORDER BY date DESC
gives me just some event per location and the result sorted by date.

Thanks in advance
David

ps: please cc, I'm not on the list


You have a common misconception of what GROUP BY does.  GROUP BY does not ever
return rows from a table.  Instead, it returns group names and aggregate
statistics http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
about groups.  That means that location is the only valid column you may select
when using GROUP BY location, because it is the group name.  Many systems
won't even allow you to select columns not named in the GROUP BY clause.  MySQL
allows it as a convenience, but you are warned
http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html not to
select any column that does not have a unique value per group, because any row
from a given group may be used.

What you are trying to do is also a frequently asked question.  I see that Peter
Brawley has already sent you the link to the 5.0 manual page which provides a
solution using a subquery.  I'd suggest the 4.1 version of the page
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html,
however, as it provides an additional solution which does not require a subquery
and is usually more efficient.

Michael



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

Re: Help Understanding Document Syntax

[Michael Stassen]

Rhino wrote:


The 'symbol' you are referring to, in the foreign key clause of the 
CREATE TABLE statement, is simply an opportunity for you to choose a 
name for the foreign key of the table; if you don't choose a name, MySQL 
will generate a default name for you.


Therefore, if you do this:

   CREATE TABLE Foo
   ...
   constraint (bar) foreign key(workdept) references Sample.department 
on delete cascade

   ...


That's not quite right.  There should be no parentheses around the symbol, but 
you do need parentheses around the referenced column. The syntax is


[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) [reference_definition]

reference_definition:
REFERENCES tbl_name [(index_col_name,...)]

so you should have

  CONSTRAINT bar FOREIGN KEY (workdept) REFERENCES Sample (department)
  ON DELETE CASCADE

snip
I _think_ you are saying that you want the combination of values in two 
of the columns of your table to be unique so that no two rows of the 
same table can have that same combination of values in those two 
columns. I know how to do this in DB2, my main database, so I looked up 
the syntax to do the same thing in MySQL and came up with this small 
example:


=
use tmp;

create table Purcell01
(empno smallint not null,
fname char(10) not null,
lname char(10) not null,
primary key(empno)
constraint(uk) unique index ukix btree (fname, lname)) Type=INNODB;


For the record, unique constraints don't require InnoDB.

snip
Unfortunately, I get a syntax error when I try this in my copy of MySQL, 
which is only 4.0.15. I'm guessing that the UNIQUE clause isn't 
recognized in MySQL 4.0.15 and that the statement will work in 5.1.x but 
there may be some problem with my syntax. I can't find an explicit 
example of a multicolumn unique constraint in the manual so maybe 
someone else reading this thread can identify any errors in the syntax 
if this doesn't work for you.


UNIQUE constraints have been in mysql a long time (at least since 3.23, I 
believe).  You have parentheses in the wrong place again.  The syntax is


  [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)

so the correct definition would be

  CONSTRAINT uk UNIQUE INDEX ukix (fname, lname)

or simply

  UNIQUE ukix (fname, lname)


The other thing you wanted was for a bad row, like the last row in my 
Inserts, to simply be ignored if it violates the unique constraint. In 
DB2, that isn't an option: the insert simply fails due to the violation 
of the uniqueness. However, it _appears_ that MySQL has a different 
policy. Apparently, you can add an IGNORE clause to an INSERT or 
UPDATE statement to make it ignore a uniqueness violation. As I read the 
article on the INSERT statement, you would want an INSERT to look like 
this if you wanted a row that violated uniqueness to be ignored:


   INSERT IGNORE INTO PURCELL01 VALUES(5, 'Fred', 'Flintstone');

The UPDATE statement appears to be the same idea;

   UPDATE IGNORE
   set fname = 'Fred', lname = 'Flintstone'
   where empno = 4;


To be clear, attempting to insert a row which violates a unique constraint, or 
to update a row in such a way as to violate a unique constraint, will fail in 
MySQL.  Adding IGNORE means it will fail silently, rather than throwing an 
error, but it will still fail.



---
Rhino


Michael

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

Re: Help on Unknown field error

[Michael Stassen]

pedro mpa wrote:

Greetings.

I need help on the following query.
I get an error like Unknown/Invalid column total_price [...] when I try
filter by total_price.
How can I do this correctly?

SELECT receipts.*, 
	(SELECT SUM(receipt_itens.price) FROM receipt_itens WHERE

receipt_itens.id_receipt=receipts.id) AS total_price
	FROM receipts 
	WHERE 
		total_price = .$minprice.  
		AND total_price = .$maxprice. 

ORDER BY receipts.date DESC

Thanks in advance.

Pedro.


You can't use column aliases in the WHERE clause.  Try changing WHERE to 
HAVING.

Michael

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

Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?

[Michael Stassen]

George Law wrote:
Nicolas,  


Not sure when the replace function was introduced into mysql, but I
think it might do...


REPLACE() exists in 3.23.


Use replace in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by


Easier said than done.


select * from test;
+-+---+
| uid | name  |
+-+---+
|   1 | george099 |
|   2 | george100 |
|   3 | george101 |
|   4 | george001 |
|   5 | 123bill   |
|   6 | 100bill   |
|  13 | george|
|  14 | darren|
|  15 | joe   |
|  16 | bill  |
+-+---+
10 rows in set (0.00 sec)

mysql select uid,name  from test order by replace(name,'[a-z]','');


REPLACE doesn't accept patterns in the search string.  This REPLACE is looking
for a literal occurrence of the string '[a-z]' to be replaced with ''.


+-+---+
| uid | name  |
+-+---+
|   6 | 100bill   |
|   5 | 123bill   |
|  16 | bill  |
|  14 | darren|
|  13 | george|
|   4 | george001 |
|   1 | george099 |
|   2 | george100 |
|   3 | george101 |
|  15 | joe   |
+-+---+


Look again.  Those are in alphabetical order, not numerical.


You might need to convert 'name' to uppercase to work with all your part
numbers.   


select uid,name  from test order by replace(upper(name),'[A-Z]','');


REPLACE is case-sensitive, but this method just won't work.

mysql SELECT REPLACE('123abcd45','[a-z]','');
+-+
| REPLACE('123abcd45','[a-z]','') |
+-+
| 123abcd45   |
+-+
1 row in set (0.11 sec)

Michael


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

Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?

[Michael Stassen]

Nicolas Verhaeghe wrote:

Thanks, but unfortunately the replace function does not want to work on
a regexp in version 3.23...


or 4.0, or 4.1, or ...


I guess I'll have to create a displacement field and populate it from
the admin tool.


Well, that's the right way to go.  You're seeing the problem with the current 
scheme.  Right now, your displacement is hidden inside the model number, so it 
is difficult to look up the displacement.  That is, the model number contains 
the answers to more than one question.  That's usually a bad idea.  It probably 
ought to be broken into separate columns.


If you make a displacement column, you ought to be able to populate it using 
sql.  See below.



Thanks for your help. I will upgrade this server as soon as I can.


Upgrading is a good idea, but it won't help here.

You can do this in sql, but it's a bit ugly.  Here are the keys:

* MySQL will pull out the number if it's at the beginning of the string.
* You can change the beginning of the string with SUBSTRING().
* You can use CASE to handle the different possibilities.

Putting them together gives you something like this:

SELECT model FROM motorcycles
ORDER BY CASE WHEN model  0 THEN model + 0
  WHEN SUBSTRING(model, 2)  0 THEN SUBSTRING(model, 2) + 0
  WHEN SUBSTRING(model, 3)  0 THEN SUBSTRING(model, 3) + 0
  WHEN SUBSTRING(model, 4)  0 THEN SUBSTRING(model, 4) + 0
 END;
++
| model  |
++
| YZ85   |
| YZ125  |
| 125SX  |
| 250EXC |
| WRF450 |
| YZF450 |
++
6 rows in set (0.00 sec)

The first case handles the strings which start with a number.  The second case 
handles the strings which starts with 1 letter before the number.  The third 
case handles the string which start with 2 letters before the number.  And so 
on.  If you can have more than 3 letters before the number, you'll have to add 
the corresponding conditions.


To just add and populate the displacement column, you could

  ALTER TABLE motorcycles
  ADD displacement INT,
  ADD INDEX disp_idx (displacement);

  UPDATE motorcycles
  SET displacement = CASE WHEN model  0 THEN model + 0
  WHEN SUBSTRING(model, 2)  0 THEN SUBSTRING(model, 2)
  WHEN SUBSTRING(model, 3)  0 THEN SUBSTRING(model, 3)
  WHEN SUBSTRING(model, 4)  0 THEN SUBSTRING(model, 4)
  END;

Then your query becomes simply

  SELECT model FROM motorcycles ORDER BY displacement;

Better yet, the index on displacement can be used to speed up the ordering.

Michael

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

Re: How can I isolate the integer part of a varchar field and use it in an ORDER BY?

[Michael Stassen]

Nicolas Verhaeghe wrote:

Because I am currently stuck with 3.23 I have just decided to create a
displacement field to isolate the number.


That's the right way to go, regardless of version.


Besides, some of these bikes
escape from the rules, for instance instead of 600 for 600cc, you only
have a mere 6, 1 stands for 1000. 


It's bad enough having to pull the displacement out of the model number, but 
when sometimes the number isn't really the displacement, what do you do?  Well, 
if you could extend the rules to cover the exceptions, you could extend the CASE 
statement in my previous message to work, but this is really all the more reason 
to put displacement in its own column.



So all in all the displacement fields will work fine.


I think it's the only good solution.

Michael

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

[Michael Stassen]

Mark Phillips wrote:

I am running mysql 4.0.24 on Debian sarge.

I have a table with two columns, team and division, both varchar(255). 
There are some errors in the table where division has a value but team is 
blank. Given that I am getting new data, and the data entry folks may create 
a record with a blank division and a team, I thought I would avoid any issues 
with team or division being blank as follows:


You should change your app to enforce your rules.  That is, your app should 
prevent your data entry folks from entering incomplete records.  Otherwise, it's 
garbage in, garbage out.



SELECT DISTINCT division, team FROM registered WHERE team!='' OR DIVISION!=''

That returns what I expected - all division-team fields have data


I doubt it.  You've joined your two conditions with OR, so your WHERE 
condition will be true for any row with at least one of the two conditions met. 
 Only a row with *both* fields blank would be excluded.  Remember,


  NOT(A OR B) = NOT(A) AND NOT(B)

so you should have used AND.  You see?  A row you don't want has

  team = '' OR DIVISION = ''

so a row you do want has

  NOT(team = '' OR DIVISION = '')

which is equivalent to

  team != '' AND DIVISION != ''

Then I decided to order the output, so I added an ORDER BY clause and some 
parentheses to make the sql more readable:


SELECT DISTINCT division, team FROM registered WHERE (team!='' OR 
DIVISION!='') ORDER BY division, team


The parentheses are irrelevant.  The ORDER BY cannot have changed which rows 
were returned.  Perhaps the ordering facilitated noticing the unwanted results.


But, I still get records with a blank team field (even if I remove the 
parenthesis). I finally found a solution, but I do not understand it:


SELECT DISTINCT division, team FROM registered WHERE (team!='' AND 
DIVISION!='') ORDER BY division, team


As I explained above.

Why does the ORDER BY clause require an AND in the WHERE clause to work 
correctly?? I do not understand the logic.


ORDER BY has nothing to do with it.


Thanks for any insight you can share with me.


Michael


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

Re: count(*) send a wrong value

[Michael Stassen]

fabsk wrote:
 Hi,

 I'm facing a strange problem. I am using a database at my Internet
 provider (Free, France). The type of table is MyISAM (no choice), MySQL
 4.1.15. I can do my tests with my PHP code or phpMyAdmin.

 The definition of my table is:
 - uid, int
 - cid, int
 - response, text
 - points, int (can be null)

 keys:
 - uid, cid
 - cid, response(4)
 - cid

If I'm reading this correctly, the third index is redundant.  The multi-column 
index on (cid, response(4)) will function equally well as an index on cid. 
There's no need for a separate single-column index on cid.


 When I do select * from my_table where cid=123, I get my 10 records.
 But when I do select count(*) from my_table where cid=123 I get 2. I
 also happens with many other values of cid and the bad result is
 always 2.

 I can't understand what's happen. It seems to simple, but there should
 be something. Do you have an idea?

 Thank you for your attention
 Fabien

[EMAIL PROTECTED] wrote:

From the MySQL 4.1 manual

12.10.1. GROUP BY (Aggregate) Functions

COUNT(expr) 

Returns a count of the number of non-NULL values in the rows 
retrieved by a SELECT statement. 

COUNT() returns 0 if there were no matching rows. 


mysql SELECT student.student_name,COUNT(*)
-FROM student,course
-WHERE student.student_id=course.student_id
-GROUP BY student_name;


COUNT(*) is somewhat different in that it returns a count 
of the number of rows retrieved, whether or not they contain 
NULL values. 



COUNT(*) is optimized to return very quickly if the SELECT 
retrieves from one table, no other columns are retrieved, 
and there is no WHERE clause. For example: 


mysql SELECT COUNT(*) FROM student;


 This optimization applies only to MyISAM and ISAM tables 
only, because an exact record count is stored for these 
table types and can be accessed very quickly. For 
transactional storage engines (InnoDB, BDB), storing an 
exact row count is more problematic because multiple 
transactions may be occurring, each of which may affect the 
count. 

COUNT(DISTINCT expr,[expr...]) 

Returns a count of the number of different non-NULL values. 

COUNT(DISTINCT) returns 0 if there were no matching rows. 


mysql SELECT COUNT(DISTINCT results) FROM student;


In MySQL, you can get the number of distinct expression 
combinations that do not contain NULL by giving a list of 
expressions. In standard SQL, you would have to do a 
concatenation of all expressions inside COUNT(DISTINCT ...). 

COUNT(DISTINCT ...) was added in MySQL 3.23.2. 


Keith


Keith, how does any of this explain Fabien's result?  If

  SELECT * FROM my_table WHERE cid=123;

returns 10 rows, then

  SELECT COUNT(*) FROM my_table WHERE cid=123;

must return 10, or something is wrong.


Fabien, if these are your actual queries and results, then there is certainly a 
problem.  One possibility is that your two queries are optimized differently, 
and one of the two indexes starting with cid is broken.  You should probably run 
a CHECK TABLE, then REPAIR TABLE if needed.  See the manual for details 
http://dev.mysql.com/doc/refman/5.0/en/table-maintenance-sql.html.


If that doesn't help, show us your real queries and their results, along with 
the EXPLAIN http://dev.mysql.com/doc/refman/5.0/en/explain.html output for 
each.  The output of SHOW CREATE TABLE 
http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html would also be 
helpful.


Michael

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

Re: Query Question

[Michael Stassen]

Douglas S. Davis wrote:

Hi,

If the following isn't appropriate, please feel free to ignore.  The 
program I'm referring to is written in Perl and uses a MySQL database, 
so I thought perhaps it would be appropriate for this list.


I have a webpage that displays a user's profile by selecting the data in 
the profiles database that corresponds to the user's ID number (in the 
profiles database that number is stored as the id_num). I have another 
table that stores comments that are posted to each user's profile page.


What I need to do is:

#1)  Display a user's profile by looking up the data in the profiles 
database.


#2)  Look in the comments database for any comments that have the user's 
id_num associated with it, and display the comments and the comment 
posters' names as found by querying the profile database on the comment 
posters' ID numbers.


Can this be accomplished with a join.

Basically two tables with data similar to this:

Profiles Table

| id |  name  |

| 1 | Bill Clinton|
---
| 2 | George Bush |
---
| 3 | Gerald Ford   |
---



Comments Table
--
| user_id | poster_id | comment |
--
| 1  |  2   | What a guy. |
--
| 2  |  3   | What a jerk. |
--
| 1  |  3  | Cigar lover.|
--

When I display the profile for Bill Clinton (id #1 in the Profiles 
table), it needs to pull the comments made to Bill's profile (the ones 
in the Comments table with the user_id matching Bill's id from the 
Profiles table: What a guy., and Cigar lover) and also look up the 
name of the person who made those comments by taking the poster_id 
from the Comments table and matching them with a name from the first 
table (for the two comments on Bill's profile, that would be George 
Bush and Gerald Ford).


Can this be done in one SQL query? If so, what would it look like?

Thanks!

Douglas
Douglas S. Davis
Programmer/Analyst
Haverford College
Administrative Computing
370 Lancaster Ave.
Haverford, PA 19041
610-896-4206



This is a How do I write this query? question, rather than a Perl DBI 
question, so it would be better on the general list.  I've copied the general 
list so any further discussion can take place there.


Yes, you can do this with a join.  You end up joining to the Profiles table 
twice, once for the name of the subjec of the quote and once for the source of 
the quote.  Something like this:


  SELECT p1.name AS Subject, c.comment AS Quote, p2.name AS Source
  FROM Profiles p1
  LEFT JOIN Comments c ON p1.id = c.user_id
  LEFT JOIN Profiles p2 ON c.poster_id = p2.id;

Michael

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

Re: Performance Problem on query kind of like a group by

[Michael Stassen]

Jim Tyrrell wrote:

Everyone,

I finally feel let down by mysql after 5 years of
great use.  I break most things in weeks so this is a
heck of a record.  I am sure I am being a dummy on
this, but
am wondering if there is some setting somewhere to
help out a query like this.

Given a table like this:
FeatureID is an autonumber and the group field keeps
things together aka version control.
FeatureID, FeatureGroupID, FeatureName
1, 1,  Version 1
2, 1,  Version 2
3, 1,  Version 3
4, 1,  Version 4
...
999,   1,  Version 999


Over small sets I write a query like this and get
stuff quickly:
Select FeatureName from Feature F where FeatureID =
(Select max(FeatureID) from Feature where
FeatureGroupID = F.FeatureGroupID)
Giving me Verison 999

In MySQL for 1000 versioned records this takes almost
half a second on my machine.  On my machine for MS SQL
this returns right away basically in no time.  I make
sure cache is not in play by inserting a set of
records and then running the query.  Also the
FeatureGroupID is indexed and so is the featureID.

Is there a setting to make this work in mysql
administration somewhere?
Is there a word or set of words that I can search for?
I have looked high and low and have not been able to
make this work fast enough for me today.

Someone I work with suggested using group by and this
seems to be even worse then what I am doing in the
above query like seconds or longer. h

Also note that also can be considered records in the
table such that
1000,2,  Version 2 1
1001,2,  Version 2 2
1002,2,  Version 2 2

The query Select FeatureName from Feature where
FeatureID = (Select max(FeatureID) from Feature)
will not work for me since i want all of the highest
FeatureID'd, FeatureGroupID'd stuff.

I have some settings in my my.cnf like:
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 3m

These have not had any effect.

Also caching is not an option because the first hit in
one of my more complicated use cases takes over 7
seconds.

Someone please help.

karma is yours if you can help me on this.

Thank You
Jim Tyrrell


Speed questions usually come down to having the right indexes on the tables 
involved, and writing queries so the indexes are properly used.  The settings in 
my.cnf are rarely the issue.


First, you need to find the max FeatureID for each FeatureGroupID.  This will go 
fastest with a multi-column index on (FeatureGroupID, FeatureID), in that order. 
 Do you have that?  {We'd know if you had included the output of SHOW CREATE 
TABLE Feature.}  A multi-column index on (FeatureGroupID, FeatureID) will also 
function as a single-column index on (FeatureGroupID), so you don't need a 
separate index on that column.


Next, you want to find the rows in table Feature whose FeatureGroupID and 
FeatureID match the results of step one.  This is a frequently asked question, 
with 3 solutions given in the manual 
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html.


The next question is whether or not mysql is using the index to optimize your 
query.  Your query is the subquery solution from the above-referenced manual 
page.  Unfortunately, mysql doesn't always optimize queries with subqueries 
properly.  To find out what mysql is doing, put EXPLAIN in front of your query 
http://dev.mysql.com/doc/refman/4.1/en/explain.html.  Post the results if you 
need help interpreting them.


If it turns out that the subquery has fooled mysql into not using the index to 
help your query, then the temporary table solution in the manual will almost 
certainly be faster:


  # Replace INT as needed to match the types of these fields in table Feature
  CREATE TEMPORARY TABLE max_fids (FeatureGroupID INT, FeatureID INT);

  # Avoid anyone changing the data in the middle
  LOCK TABLES Feature READ;

  # Step 1: Get the max FeatureID for each FeatureGroupID
  INSERT INTO max_fids SELECT FeatureGroupID, MAX(FeatureID)
  FROM Feature GROUP BY FeatureGroupID;

  #Step 2: Find the rows in Feature which match the results in Step 1 via a JOIN
  SELECT f.FeatureName,
  FROM max_fids m
  JOIN Feature f
ON f.FeatureGroupID = m.FeatureGroupID
   AND f.FeatureID = m.FeatureID;

  #clean up
  UNLOCK TABLES;
  DROP TABLE max_fids;

That looks a lot more complex, but as the index on (FeatureGroupID, FeatureID) 
will almost certainly be used for both steps, it should be very fast.


Michael

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

Re: Sorting with NULL

[Michael Stassen]

Marcus Bointon wrote:
I have a table that contains a foreign key that can be null. When I  do 
a search I want to have the matches that are null appear first,  and 
then all other values sorted by another column. My data looks  like this:


idname
nullabc
1def
2xyz
nullzzz
7aaa

I want to get them in this order:

nullabc
nullzzz
7aaa
1def
2xyz

SELECT id, name FROM mytable ORDER BY ??

doing ORDER BY id, name will make the null values appear first, but  
then the following values will be in the wrong order. I've thought  
about using FIELD() in the order by, but the docs say it doesn't like  
nulls. Is there some other sorting mechanism I could use?


Thanks,

Marcus


id IS NOT NULL will return 0 when id is NULL, 1 when id is anything else, so

  ORDER BY id IS NOT NULL, name

should do it.

Michael

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

Re: Help with a SELECT query

[Michael Stassen]

Jay Paulson (CE CEN) wrote:

This helps a ton! Thanks! I didn' tknow about the INET_NTOA() or the
INET_ATON() functions. That is much quicker to query on them than on a char set
of ips. However, I did notice on the mysql web site that these functions are
only available in 5.x but the way the page is set up I'm a little confused if
this is true or not. I'm running 4.1.x and it would be nice to have those 
functions.

The page below is where I found information about the function.
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html

Thanks!



Change the 5.0 to 4.1 in the URL to see the relevant page:

http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html

You will discoverr that INET_NTOA() and INET_ATON() were added to mysql way back 
in version 3.23.15.


Unfortunately, the 5.0 version of the manual has no mention of when things were 
added to mysql in previous versions.  You have to look at the 4.1 version of the 
manual for that.  I suppose the reasoning is that the 5.0 manual is for only the 
one version of mysql, while the 4.1 manual is for versions 3.23, 4.0, and 4.1 
combined, but I find it a real pain, and it seems to confuse people into 
thinking old things first arrived in 5.0.


Michael

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

Re: SELECT help.

[Michael Stassen]

Richard Reina wrote:

Can someone help me write a query to tell me the customer numbers (C_NO) of
those who've had more than 4 transactions but none in the last 6 months?
  
 transactions_table

  | ID   | C_NO |DATE  | AMOUT |
  | 2901 |  387 | 2003-10-09 | 23.00 |
   
  Obviously my table has many more entries.
  
  Thank you for any help.
  
  Sincerely,
  
  Richard Reina
   
A people that values its privileges above its principles soon loses both.

 -Dwight D. Eisenhower.


Something like:

  SELECT C_NO FROM transactions_table
  GROUP BY C_NO
  HAVING COUNT(*) = 4
 AND COUNT(DATE  CURDATE() - INTERVAL 6 MONTH) = 0;

Michael

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

Re: Getting # of days until expiration

[Michael Stassen]

Brian Dunning wrote:
Thanks Peter, that appears to be exactly what I'm looking for, but it  
still gives an error and I've been through it with a fine-toothed  comb, 
tried different versions, parens, etc. Here is the exact SQL  statement 
I'm using, with your suggestion:


select
accounts.username,
meets.id as meet_id,
DATEDIFF( DATE_ADD( meets.creation, INTERVAL 30 DAY), NOW() ) as  
expire_days

from
meets
left join
accounts on meets.id=accounts.id
blah blah blah lots more stuff.

And here is the exact error I get:

You have an error in your SQL syntax. Check the manual that  corresponds 
to your MySQL server version for the right syntax to use  near '( 
DATE_ADD( meets.creation, INTERVAL 30 DAY), NOW() ) as expire


Can anyone see the problem? I sure can't.



DATEDIFF was added in 4.1.1.  What version of mysql do you have?
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

Michael



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

Re: backslash and Update

[Michael Stassen]

Danny Stolle wrote:

Hi,

What you perhaps could use is the REGEXP usage in your where clause.
Try this:
update name set first_name=replace(first_name, '\\', '') where 
first_name regexp '';


The fun thing is that when you put '\\' instead of the '' after the 
regexp function it doesn't work. But this sure does.


Before you use the update, create a selection first with the regexp 
function; if that works, your update will do fine.


Hope this little info helps you :-)

Danny

Jerry Swanson wrote:


I have 290 records in the database with backslashes. I want to remove the
backslashes.  Why the query below doesn't remove backslashes?

update name set first_name = REPLACE(first_name,'','') where 
first_name

like '%%';


You should be able to do this with either LIKE or REGEXP.  The problem, I think, 
was too many backslashes in the REPLACE part of the UPDATE.  That is,


  REPLACE(first_name,'','')

tells mysql to replace double backslashes (\\) with nothing, but won't have any 
effect on single backslashes (\).  I believe this is because the LIKE or REGEXP 
pattern is parsed twice, while the REPLACE pattern is parsed once, as the 
following example shows:


  CREATE TABLE bst (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
txt CHAR(32));

  INSERT INTO bst (txt)
  VALUES ('Here\'s some text.'), ('Here is some more.'),
 ('This has a \\ in it.'), ('This one has 2  in the middle.');

  SELECT * FROM bst;
  ++--+
  | id | txt  |
  ++--+
  |  1 | Here's some text.|
  |  2 | Here is some more.   |
  |  3 | This has a \ in it.  |
  |  4 | This one has 2 \\ in the middle. |
  ++--+
  4 rows in set (0.00 sec)

  SELECT * FROM bst WHERE txt LIKE '%%';
  ++--+
  | id | txt  |
  ++--+
  |  3 | This has a \ in it.  |
  |  4 | This one has 2 \\ in the middle. |
  ++--+
  2 rows in set (0.00 sec)

  mysql SELECT * FROM bst WHERE txt REGEXP '';
  ++--+
  | id | txt  |
  ++--+
  |  3 | This has a \ in it.  |
  |  4 | This one has 2 \\ in the middle. |
  ++--+
  2 rows in set (0.01 sec)

  UPDATE bst SET txt = REPLACE(txt, '', '');
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 4  Changed: 1  Warnings: 0

  SELECT * FROM bst;
  +++
  | id | txt|
  +++
  |  1 | Here's some text.  |
  |  2 | Here is some more. |
  |  3 | This has a \ in it.|
  |  4 | This one has 2  in the middle. |
  +++
  4 rows in set (0.00 sec)

  UPDATE bst SET txt = REPLACE(txt, '\\', '');
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 4  Changed: 1  Warnings: 0

  SELECT * FROM bst;
  +++
  | id | txt|
  +++
  |  1 | Here's some text.  |
  |  2 | Here is some more. |
  |  3 | This has a  in it. |
  |  4 | This one has 2  in the middle. |
  +++
  4 rows in set (0.00 sec)

Michael



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

Re: Need Help Connecting

[Michael Stassen]

Mark Phillips wrote:

David,

This is what I got:

[EMAIL PROTECTED]:~$ aliases
bash: aliases: command not found


Your shell is bash, so the correct command is `alias`.


[EMAIL PROTECTED]:~$ which mysql
/usr/bin/mysql


Since you are using bash, it's a better idea to use `type` instead of `which`. 
On some systems (Solaris 7, for example), `which` can give bogus results in 
bash.  So, try


  type mysql


[EMAIL PROTECTED]:~$ $PATH
bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or 
directory
[EMAIL PROTECTED]:~$  


Something is wrong with mark's PATH.  See the error at the end?

I don't have a command 'aliases', but the other tests seem to say all I have 
is mysql running as mysql.


When I am logged in as 'emily' I get:

[EMAIL PROTECTED]:/home/mark$ which mysql
/usr/bin/mysql

[EMAIL PROTECTED]:/home/mark$ echo $PATH
/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games
[EMAIL PROTECTED]:/home/mark$   

Any other thoughts? The error message from mysql when I try to log in is 
strange. Why all the spaces?


mysql: unknown option '--user   mark'


I don't believe that error came from mysql.  Indeed, mysql doesn't care which 
unix user runs it, it only cares which mysql user you say you are.  Because it 
works as expected when Emily runs it, I don't believe mysql is the problem.  I 
believe David is correct that there is something in mark's environment that is 
causing the problem.  Check the output of `alias` and `type mysql`, and check 
mark's .my.cnf file, if he has one.



Thanks!

Mark  


Michael

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

Re: User can see all databases...

[Michael Stassen]

William R. Dickson wrote:
OK, I strongly suspect I've just done something stupid here, but I'm  
having trouble figuring it out. I had a disk go bad on a MySQL server  
this past weekend. I did a clean system install (FreeBSD 5.4) on a  new 
disk, installed the MySQL 3.23 port, and restored the mysql data  
directory from backup. Everything is working fine...except now, every  
user is able to get a list of every database on the system. They  can't 
actually use the databases, but I'd rather they couldn't get  the list, 
either.


Following some suggestions I found in the list archives, I did a SHOW  
GRANTS and found the following (database names match usernames):


GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'blablabla'
GRANT ALL PRIVILEGES ON `username`.* TO 'username'@'%'

I suspect that the problem lies with every user having USAGE'  
privileges on every database (although entering use otherusername;  
returns an error indicating the user has no permissions to read the  
database). However, I can't seem to revoke this privileges. I can't  
even find the privilege in any of the tables.


Can someone point my addled brain in the right direction here?

Thanks!

-Bill


I know it is counter-intuitive, but USAGE means no privileges.  In 3.23, 
seeing all databases is the default behavior, turned off by starting mysqld with 
the --skip-show-database option.  This changed to the behavior you are expecting 
in 4.0.2.


See the manual for more 
http://dev.mysql.com/doc/refman/4.1/en/show-databases.html.


Michael

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

Re: bug in MySQL 5?

[Michael Stassen]

Octavian Rasnita wrote:

From: Gleb Paharenko [EMAIL PROTECTED]


Hello.

In my opinion, it is not a bug. REPLACE has returned the sum
of affected rows - one was deleted, and one was inserted. See:
 http://dev.mysql.com/doc/refman/5.1/en/replace.html


Thank you. I have seen that's the true.
Is there any MySQL command that instead of 


if(record exists) {
delete line
insert new line
}
else {
insert line
}

does something like:

if (record exists) {
update record with the specified fields
}
else {
insert record
}

Thank you.

Teddy


Yes.  You want SELECT ... ON DUPLICATE KEY UPDATE ...  See the manual for 
details http://dev.mysql.com/doc/refman/5.0/en/insert.html.


Michael



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

Re: Select Unique?

[Michael Stassen]

Rhino wrote:


- Original Message - From: John Mistler 
[EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Monday, December 12, 2005 12:34 AM
Subject: Select Unique?


I have two tables 'table1', 'table2' with a matching column 
'column1'.  How can I return all rows from table2 where the entry for 
table2.column1 does not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1  table1.column1

returns all the rows, rather than the unique rows in table2 ... Any 
ideas?



SELECT *
FROM table2
where table2.column1 not in
(select distinct column1
from table1
where column1 not null)


That should be where column1 IS NOT NULL).

The 'distinct' in the subquery is not strictly necessary but should help 
performance. The WHERE clause in the subquery is often omitted but 
really shouldn't be.


Rhino


If you're interested in performance, you probably shouldn't use a subquery.  If 
you put EXPLAIN in front, you'll see that mysql labels this a DEPENDENT 
SUBQUERY, meaning it will rerun the subquery for each row in the outer query. 
The optimizer *should* be smart enough to run the inner query once, then compare 
rows to that list using the index, but it isn't.  As a test, I made a 25 row 
table and a copy missing 3 of those rows.  The subquery version took twice as 
long to execute (.12 sec) as the left join version (.06 sec).  The larger the 
tables involved, the larger the difference is likely to be.


Michael

(Test run on my iBook G4, OS X 10.3.9, mysql 4.1.15.)

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

Re: mysqladmin --skip-grant-tables error

[Michael Stassen]

Alfred Vahau wrote:
According to the Mysql online manual, to reset a root password one 
procedure is to issue the command:

./mysqladmin --skip-grant-tables
to have full access to the database and update the root password as per 
the instructions in the manual.

My problem is when I issue the command
./mysqladmin --skip-grant-tables, I get the error message
mysqladmin: ERROR: unknown option '--grant-tables'

I also tried ./mysqladmin --skip-grant-tables --user=root and this 
didn't help either
I'm using Mysql version 4.0.17-standard which came bundled with my OS 
(Ubuntu Linux Warty)


Pointers much appreciated.
Thanks,

Alfred Vahau
IT Services
University of Papua New Guinea


You appear to have misread the manual.  --skip-grant-tables is not a mysqladmin 
option.  It's a mysqld option.


Just follow the directions in the manual 
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html.


Michael


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

Re: from MySQL to MS Excel ...

[Michael Stassen]

C.R.Vegelin wrote:

Thanks JR, Shawn, Scott, ... for your replies.
I choose to make use of the SELECT ... INTO OUTFILE.
This works fine, but I also want a header-line in the CSV file.
So I made the following statement:

SELECT `ID`, `Code`, `Name`
UNION
SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
FROM Units ORDER BY `ID`;

But this makes the header-line a trailer-line in the csv file, like this:
11,kg,KiloGrams
12,g,Grams
13,Ton,Tonne
...
ID,Code,Name

Any idea how to make a sorted csv file with a real header-line ?
TIA, Cor


I suspect your query is treated as

  (SELECT `ID`, `Code`, `Name`)
 UNION
  (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv'
   FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM Units )
 ORDER BY `ID`;

when you want

  (SELECT `ID`, `Code`, `Name`)
 UNION
  (SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv'
   FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
   FROM Units ORDER BY `ID`);

You see the difference?  The former sorts all the rows by id, while the latter 
only sorts the second query's output.


Michael


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

Re: SQL on Mac OS X - Socket Errors

[Michael Stassen]

Nathan Whitington wrote:

Hello there,

I've searched long and hard, and pestered many people for a solution  
and for help however I can not get around this problem.


I have installed MySQL on my computer which is an Apple iBook G4  which 
is running Mac OS X 10.4.2 and I wish to use MySQL so that I  can learn 
something and play with it.


Great, but what version of mysql?  How did you install?  Did you follow the 
post-installation instructions 
http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html?


I have tried to connect and startup the database through the terminal  
and I've even tried to startup MyPHPAdmin, however I'm getting this  
message:


How did you try to start the server?  Did it start?  Is there anything in 
mysql's error log?


#2002 - The server is not responding (or the local MySQL server's  
socket is not correctly configured)


Error 2002 means the client could not find the socket file to connect to the 
server.  This usually means the server is not running, or the client is 
misconfigured.  As you don't mention any attempts at configuration, I'll guess 
the server isn't running.



Does anybody know what it is I can do to start to get around this problem?


* Follow the post-install procedure in the manual.
* Make sure mysql's data directory is owned by the mysql user.

After that, the most likely problem is that mysql cannot create the socket file 
due to permissions problems.  The default location for the socket file is in 
/tmp.  Several versions of / updates to Mac OS X have set incorrect permissions 
on  /tmp.  You should


  cd /tmp
  sudo chmod 1777 .

to set correct permissions on /tmp.  Then try to start mysqld:

  cd /usr/local/mysql
  sudo -v
  sudo bin/mysqld_safe 

If it doesn't start, look for the .err file in the data directory (the error 
log) for the reason.  If you need more help, include the answers to the above 
questions, the exact commands you entered, the exact text of any errors rceived, 
and the contents of the error log in your reply.



Thank you very much in advance,

Nathan Whitington 
[EMAIL PROTECTED]


Michael


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

Re: Select Unique?

[Michael Stassen]

John Mistler wrote:
I have two tables 'table1', 'table2' with a matching column 'column1'.  
How can I return all rows from table2 where the entry for table2.column1 
does not match any entries in table1.column1?


SELECT * FROM table2 WHERE table2.column1  table1.column1

returns all the rows, rather than the unique rows in table2 ... Any ideas?

Thanks,

John


You need a LEFT JOIN:

  SELECT table2.*
  FROM table2
  LEFT JOIN table1 ON table1.column1 = table2.column1
  WHERE table1.column1 IS NULL;

Michael

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

Re: Normalization question

[Michael Stassen]

Rod Heyd wrote:

Hi Everyone,

I've got a little debate that maybe you can all help me settle.

I have three tables that I need to join, but there are performance problems
with the joins due to some misunderstandings of my predecessor about what's
needed to join the tables efficiently.

Here's the setup.

t1 has a unique key defined on it, call it command_id, t1 has a 1 to many
relationship with t2.
t2 has t1's command_id as a foreign key constraint, plus a set of one or
more instrument_id's.
Each command results in one or more instruments taking data.

The commanding elements defined here are then set to our instrument to aquire
the data. When the data comes back it is organized into t3 by command_id and 
instrument_id. So the primary key on t3 looks like this:

command_id_instrument_id.


Yuck!


So, now I need to write a query that looks at what was commanded in t1 and t2
and then look for any missing data that has not yet been received.

So, I've got a query that looks something like this:

SELECT  stuff
FROM t1 JOIN t2
ON t1.command_id = t2.command_id
LEFT JOIN t3
ON t3.data_id = concat(t1.command_id,'_',t2.instrument_id)

Now, I think everyone is going to see immediately that the join on t3 is
going to have absolutely horrible performance,
the join condition here can't take advantage of any indexes since the string
function pretty much destroys any hope of that. To make matters worse, the
left join is a total killer.


I think the LEFT JOIN isn't that big a deal, if the proper index is there and 
usable.  You can easily find out by comparing the speed of your query below 
against the speed of the same query without LEFT.



So my suggestion to solve the performance bottleneck is to add two columns to
t3, command_id and instrument_id, and create a combined index on the
command_id and instrument_id columns.


Yes, exactly.  The combined index should be UNIQUE.


the join condition on t3 then becomes:

LEFT JOIN t3
ON t3.command_id = t1.command_id AND t3.instrument_id = t2.instrument_id

This works beautifully!  The performance of this new join condition is about
480 times faster than the original.


Yes, this is the way this should be done.

Here's the rub. Since there is a unique data_id that already exists which 
combines the information in both command_id and instrument_id keys, I'm being

told by our seasoned software developer that I am violating classic
database design rules against redundant data.


It is true that you now have redundant data.  Clearly, you do not need both the 
two new columns, command_id and instrument_id, and the old column, 
command_id_instrument_id.  Redundant data is a violation of classic database 
design rules, so one or the other has to go if you want to follow the rules.


Which should go?  Well, the old column, command_id_instrument_id, combines the 
answer to two questions, Which command? and Which instrument?, into one 
column.  That is also also a violation of the classic database design rules, 
and a really bad idea.  It leads to precisely the sort of problem you are trying 
to fix.  It also makes it difficult to find the rows in t3 which belong to a 
particular command, or to a particular instrument.  Those queries require string 
matching, and the latter could not use an index.  If you want to follow the 
rules, drop the old column.



In my opinion, this is a minor violation at best.  We have a good reason
for wanting to identify each data segment with our originally defined
data_id, but this is not strictly a requirement on the database, it's more
of an agreed upon convention that we are going to use to identify the data
segments and distribute them to our consumers.  From a database stand point,
the only requirement is that the data_id be unique.  It could be anything as
far as the database is concerned, as long as the data_id remains unique, it
doesn't matter that it may be overloaded with some duplicate information.
Any more experienced DBA's than I have an opinion on this?


The strictly correct solution is to replace the old, broken column with your two 
new columns.  You can either make the combined index on the two new columns the 
PRIMARY KEY, or you can make an AUTO_INCREMENT primary key and define the 
combination of the two columns as UNIQUE.  It is then trivial to


  SELECT CONCAT(command_id, '_', instrument_id) ...

when you want to display the t3 id using the agreed upon convention.

In short, your seasoned software developer is right to want to follow the rules, 
but the rules dictate replacing the old column with the two new columns.



Thanks!


Michael

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

Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=

[Michael Stassen]

Test USER wrote:

Hi again :)

The table contains a column named value and is in the format varchar(255).
This column contains specifications for different computer products.
There is also a id column so i know which product it belongs to.

value(varchar)
80
17
1024x768
USB
DiVX

For example, the first value 80 tells me with som joins that the product maxtor 
diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for 
the last value in this example.


Now i want to select all harddrvies with a capacity greater or equal to 80.
Doing a select value from tbl where value =80 order by value DESC will give 
some unexpected results.


If you have 80, 120, 250 in the database the result will be:
80
250
120

I don't really know how to solve this other than to use CAST(value as SIGNED).
Maybe i could rebuild the database but i don't know how a good databasedesign 
for this would look like :)


Is the ordering your only concern?  Your value column is a string, so your 
results are ordered alphabetically rather than numerically.  If all you want is 
numeric ordering, you need to tell mysql to treat value as a number in the order by:


  SELECT value FROM tbl WHERE value =80 ORDER BY value+0 DESC;

Michael

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

Re: Alter MyISAM table to adjust max_rows and Avg_row_length

[Michael Stassen]

Jeff wrote:

I've got a table that just hit the 4gig limit for MyISAM tables in a 4.x
ver db.  I need to alter the table structure and set the max_rows and
Avg_row_length to override the default of 4 gig.  Problem is I can't
find any reference in the mysql docs that indicates how to decide a
setting for Avg_row_length or even what the measurement is.  Bytes?
Columns? 


Any help is much appreciated.

Jeff


MAX_ROWS and AVG_ROW_LENGTH are defined in the manual page for CREATE TABLE 
http://dev.mysql.com/doc/refman/4.1/en/create-table.html:


MAX_ROWS: The maximum number of rows you plan to store in the table.  This is 
not a hard limit, but rather an indicator that the table must be able to store 
at least this many rows.


AVG_ROW_LENGTH: An approximation of the average row length for your table.  You 
need to set this only for large tables with variable-size records.


The current value of AVG_ROW_LENGTH can be seen in the output of SHOW TABLE 
STATUS http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html, along 
with the maximum_data_length.  They are in bytes.


As for MAX_ROWS, you really just need a value large enough to require a larger 
pointer.  For example, the manual suggests


  ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn;

It goes on to say that you don't need to set AVG_ROW_LENGTH unless your table 
has BLOB or TEXT values http://dev.mysql.com/doc/refman/4.1/en/full-table.html.


Michael

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

Re: CocoaMySQL v0.7b2 Connection Help

[Michael Stassen]

[EMAIL PROTECTED] wrote:

Responses intermixed.  See below...

untz [EMAIL PROTECTED] wrote on 12/02/2005 10:43:41 PM:

snip

3. After logging into mysql, I created the following database:

mysql create database music_development to 'untz'@'localhost' 
identified by 'paintball';


MySQL databases do not have the concept of ownership. They are all global. 
You only need to say:


(@mysql CLI prompt) CREATE DATABASE music_development;

and you can test to see if your create statement worked by running 


(@CLI) SHOW DATABASES;

If your database is in that list, the command worked.

You appear to have mixed a CREATE DATABASE with a GRANT statement. To 
create a MySQL user you need to use a GRANT statement. If you want to 
create a MySQL user account with all normal DB privileges (but still 
cannot grant permission to others), this is how I would create the account 
and grant access the new database:


(@CLI) GRANT usage ON *.* to 'untz'@'localhost' IDENTIFIED BY 'xx';
(@CLI) GRANT ALL ON music_development to 'untz'@'localhost';

snip

The db name is optional with GRANT, but the table name is not, so this statement 
would grant rights to the *table* named music_development in the currently 
selected db.  (Even though I know why this is so, I've always found it a little 
counter-intuitive, which has led me to make the same mistake a few times.)  What 
Shawn meant to say was


  GRANT ALL ON music_development.* to 'untz'@'localhost';

which grants rights on all tables in the music_development to [EMAIL PROTECTED]

Michael

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

Re: About union sql Mysql 4.x

[Michael Stassen]

HALIL DEMIREZEN wrote:
 Hi,

 I am trying to run an sql query such as below to list items=x randomly and
 then items != x randomly..


 mysql (select * from tablea where item=1 order by rand()) union all
 (select  * from tablea where item != 1 order by rand());

 but the result is not as expected. rand() seems not to be working...

 What can be the problem? or what can be the difference between my will and
 the exact result of the query?

Gleb Paharenko wrote:
 Hello.

From http://dev.mysql.com/doc/refman/5.0/en/union.html:

 ORDER BY for individual SELECT statements within parentheses has an
 effect only when combined with LIMIT. Otherwise, the ORDER BY is
 optimized away.

 Therefore you're getting the same results, because ORDER BY doen't
 work for your query.

HALIL DEMIREZEN wrote:

What if i want to list all the records not limiting them to a constant?


How about

  SELECT * FROM tablea
  ORDER BY (item != 1), RAND();

Michael

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