getLong() vs getInt() result differs on unsigned field

[Bill Easton]

Kevin,

What you are overlooking is that 4294967298 is outside the range of Java's
int--it's 0x10002.  So, when it's converted to int, the high order bit
gets discarded, and you are left with a result of 2.  JDBC behaves as would
be expected.

public class TestLong {
final public static void main(String[] args) {
long theLong = 4294967298L;
System.out.println((int) 4294967298L =  + (int) theLong);
}
}

C:\javaboxjava TestLong
(int) 4294967298L = 2

HTH
Bill

== original message follows ==

Date: Mon, 11 Jul 2005 13:54:12 -0400
From: Kevin McAllister [EMAIL PROTECTED]
To: [EMAIL PROTECTED]

Hello,

[...]
The problem comes in with fields of type int(11) unsigned these are
obviously interpreted as Longs on call to resultSet.getObject() which is
fine, the problem I am having is that calls to getLong() and getInt()
return different looking values.

[...]
Code I have runs this query
SELECT title, probabilityID FROM salesprobability

And then iterates the result set, I put the following debugging code in
there:

while (rs.next()) {
  String title = rs.getString(1);
  long id = rs.getLong(2);
  int idInt = rs.getInt(2);
  System.out.println(title+, long Id: +id+ intId: +idInt);
}

And get results like:
10%, long Id: 4294967297 intId: 1
20%, long Id: 4294967298 intId: 2
30%, long Id: 4294967299 intId: 3

I assume I am overlooking something very obvious, But I would expect
that both the long and int interpretations of the value would be the
same, especially for numbers of that magnitude.

Thank you for any help anyone can offer.

Thanks,
Kevin


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

mysqldump, --single-transaction and --flush-logs

[Bill Easton]

If I use the options --single-transaction and --flush-logs with mysqldump
and InnoDB tables, does the new log file start at the same time as the
transaction?

If not, is it known for sure if the log file is switched before the
transaction starts or if it is switched after the transaction starts?

Is there some way to determine the binary log position as of the single
transaction for the dump?


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

Re: global object-id

[Bill Easton]

Well, the answer is no--there's no magic way to have an auto_increment do
what you want.

You could use a GUID--basically a pseudo-random number such the expected
time to pick a duplicate is on the order of the lifetime of the universe.
But GUID's are big and ugly, and it would be nice to just have an integer.

Presumably, one of the things you'd like is to have inserts in different
threads run concurrently--so your increment field should be unique, even in
threads that haven't committed yet.

Here's a scheme that may help.  Yes, it has an extra table.  But it mostly
does what you want.

create table global_increment (
value int not null primary key auto_increment
) type=InnoDB.

You create a new value by

insert into global_increment values (0);
select last_insert_id();

Once you've got a new value, it's yours.  You can commit it immediately or
not, as you like, and still use it in your other tables without
interference.

Unfortunately, the global_increment table grows, and you need to delete old
values.  One way to do this is to estimate how many values are likely to
still belong to current transactions.  You can then do:

commit;
delete from global_increment where value  (your_value -
said_estimate);

(We use something like this as part of a scheme to automatically update
information shown on client screens when the database changes.)

HTH


= original message follows =

From: Martijn Tonies [EMAIL PROTECTED]
To: Konrad Kieling [EMAIL PROTECTED],
 mysql List mysql@lists.mysql.com
Subject: Re: global object-id
Date: Thu, 10 Feb 2005 16:57:49 +0100
 is there a simple way (ie without creating and deleting datasets in an
 extra table) to use an auto_increment id field in several tables. no
 table has to have all ids, so i cannot use the field of a master table
 in childs. it seems one can use a sequence in postgresql in different
 tables. is it possible in a similar way?

sequences are separate objects used to generated, guess what,
sequential numbers. The actual usage of the numbers is undefined.

Auto-inc is auto-inc. Plain and simple. It's something related to
a column in a particular table. Each table can have it's own
instance of 1 (and only 1) auto-incrementing column.

Short answer: no.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server
Upscene Productions
http://www.upscene.com


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

Re: Max connections being used every 10-12 day.

[Bill Easton]

Fredrik,

I haven't read all of the earlier messages, but it looks like your problem
is that a query such as the following takes more than a minute to return 11
rows.

Select A.id, A.parent
  from art A inner join art B using (id)
  where A.id=560685 or B.parent=560685;

Why?  Well, your explain says that no key is used, but the entire table is
scanned (type='ALL').  In particular, MySQL is reading every record of A and
looking to see if either A.id=560685 or B.parent=560685.  Not good.

Your query is equivalent to the following:
Select id
  from art
  where id=560685 or parent=560685;

I'd be surprised if the simpler query weren't slightly faster than the
original--MySQL has more work to do on the original.  It appears that, with
the more complex query, you are trying to fool MySQL into using indexes for
both parts of the OR.  It didn't work.

In an earlier mail, your explain had type='index' and key='id_3'.  In that
case, you evidently had an index, id_3, that contained both id and parent.
So, MySQL could get all of the information form the id_3 index; therefore,
it read the index instead of the table.  It still read the entire index,
looking to see if either A.id=560685 or B.parent=560685.  Better than
reading the full table, but still not good.

What to do?

Well, you can get the information you want in TWO queries:

Select id
  from art
  where id=560685;

Select id
  from art
  where parent=560685;

In each of these, MySQL will surely use the appropriate index and return the
result in a few milliseconds.

You should be able to combine them into one query and get the same behavior:

Select id
  from art
  where id=560685
UNION ALL
Select id
  from art
  where parent=560685;

I'd be surprised if MySQL didn't do fine on this.  (You may have tried this
and failed--as I said, I didn't try to read all of the earlier mail.  But
I'd be astonished if it weren't fast, though I suppose MySQL might try to do
something fancy, knowing that the two queries are on the same table.  In any
event, check the two individual queries.  If necessary, use a temporary
table.)  Then, you get to add your ORDER BY clause; presumably, it will
still be fast.

There was some mention in earlier mail of joins being faster than unions.
That may be, but the difference should be too small to notice, unless
different indexes are used.  In your query above, with the inner join, MySQL
is going to first consider the join and then consider the use of indexes for
the where clause--so it ends up with the full table scan.

HTH,

Bill


= original message follows =

From: Fredrik Carlsson [EMAIL PROTECTED]
To: Donny Simonton [EMAIL PROTECTED]
CC:  mysql@lists.mysql.com
Subject: Re: Max connections being used every 10-12 day.

I really appreciate your help :)

I did some cleanup of my indexes(there are a couple of them left to
clean out but it takes so long time):

mysql show index from
art;
+++--+--+-+-
--+-+--++--++-+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| art |  0 | PRIMARY  |1 | id  | A
|  542437 | NULL | NULL   |  | BTREE  | |
| art |  1 | date |1 | date| A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst |1 | lst| A
| 216 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | batch|1 | batch   | A
| 183 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | batch|2 | lst| A
|1802 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |1 | lst| A
| 216 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |2 | parent  | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | lst_3   |3 | batch   | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | parent   |1 | parent  | A
|   90406 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | mid|1 | mid   | A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | date_2   |1 | date| A
|  542437 | NULL | NULL   | YES  | BTREE  | |
| art |  1 | subc  |1 | subc | A |
54243 | NULL | NULL   | YES  | FULLTEXT   | |
| art |  1 | mf|1 | mf   | A |
54243 | NULL | NULL   | YES  | FULLTEXT   |  

Re: ORDER BY sorting

[Bill Easton]

Mike,

Try select * from foo order by x+0, x;

x+0 converts x to an integer by taking the digits from the beginning of the
string.

== original message follows ==

Date: Sat, 11 Dec 2004 15:36:34 -0600
From: Mike Blezien [EMAIL PROTECTED]
To: MySQL List [EMAIL PROTECTED]
Subject: ORDER BY sorting

Hello,

I'm trying to figure out how sort a column alphabetically, but some of the
values start with a numerical value. So when I do the ORDER BY column_name
all
the values starting alphabeticlly are listed first, then the numerical
values
after that.. can this be done in a single query..

MySQL ver. 4.0.20

TIA,
-- 
Mike(mickalo)Blezien


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

Yet another LEFT JOIN question

[Bill Easton]

Ron,

What's happening is that, when there are clicks and views for an ad, you are
getting the number of clicks TIMES the number of views.

A quick and dirty solution is to put a column, say id, in clicks which is
different for each click, and similarly for views.  Then, you can change
your counts to count(distinct clicks.id) and count(distinct views.id).  Note
that, internally, MySQL will still find all of the (click, view) pairs, then
sort them and remove duplicates--this may or may not be a problem, depending
on usage.

If you are using 4.1 or later, you could do a subquery to count the clicks,
then left join that with the views.

HTH

Bill


From: Ron Gilbert [EMAIL PROTECTED]
Subject: Yet another LEFT JOIN question
Date: Sat, 4 Dec 2004 12:08:43 -0800

I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list
of every time a Ad was clicked on with the Ads ID, and 'Views' is a
simple list of views that ad got, with the Ads ID.

I am trying to SELECT a list of all the ads, with a count for clicks
and a count for views, but my LEFT JOIN is not producing what I
thought.

If the click count is 0, then the view count is OK, but if not, then
the Click count and view count are equal, but a much too large number.

If I just SELECT for views or clicks, then it works OK, it's when they
are combined that it falls apart.

SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views
FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID
 LEFT JOIN Views V ON A1.ID = V.AdID
group by A1.ID

CREATE TABLE `Clicks` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Views` (
   `AdID` int(10) NOT NULL default '0'
   [snip]
)
CREATE TABLE `Ads` (
   `ID` int(10) NOT NULL default '0'
   [snip]
)

I have tried a lot of combinations for LEFT JOIN with no luck.  I've
read all the posts on this list and they don't seem to be doing what I
am, or else I'm not seeing it.


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

Re: Yet another LEFT JOIN question

[Bill Easton]

Try:

select id, clicks, count(views.adId) as views
 from (select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId
group by id) as adsclicks
   left join views on id=views.adid
 group by id;

Explanation:
-- the following gives you a count of clicks for each ad
select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId group by id)
-- if you save it to a temporary table,
create temporary table adsclicks
select ads.id, count(clicks.adId) as clicks
from ads inner join clicks on ads.id=clicks.adId
group by id)
-- you then have a temporary table with a row for each ad and the click
counts
-- you can then left join that with the views table to get the views count,
too.
select id, clicks, count(views.adId) as views
 from adsclicks
   left join views on id=views.adid
 group by id;
-- the query at the beginning of this message uses a subquery instead of
creating and using a temporary table.

Is the subquery better or faster?  Try it and see--depends partly on whether
you have to add a column to identify individual clicks and views.  On the
one hand, the count(distinct) solution looks at more rows; on the other
hand, subqueries may not get as much optimization.  I'd claim that the
subquery describes better what you want, while the count(distinct) is a
kludge to avoid the subquery.

- Original Message - 
From: Ron Gilbert [EMAIL PROTECTED]
To: Bill Easton [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, December 06, 2004 11:05 AM
Subject: Re: Yet another LEFT JOIN question


  If you are using 4.1 or later, you could do a subquery to count the
  clicks,
  then left join that with the views.

 I am using 4.1.  I tried to do a sub-query, but never got it run.  Can
 you give me a quick example?  Is the sub-query a better (faster) way to
 do this?

 Ron

 On Dec 6, 2004, at 6:19 AM, Bill Easton wrote:

  Ron,
 
  What's happening is that, when there are clicks and views for an ad,
  you are
  getting the number of clicks TIMES the number of views.
 
  A quick and dirty solution is to put a column, say id, in clicks which
  is
  different for each click, and similarly for views.  Then, you can
  change
  your counts to count(distinct clicks.id) and count(distinct views.id).
   Note
  that, internally, MySQL will still find all of the (click, view)
  pairs, then
  sort them and remove duplicates--this may or may not be a problem,
  depending
  on usage.
 
  If you are using 4.1 or later, you could do a subquery to count the
  clicks,
  then left join that with the views.
 
  HTH
 
  Bill
 
 
  From: Ron Gilbert [EMAIL PROTECTED]
  Subject: Yet another LEFT JOIN question
  Date: Sat, 4 Dec 2004 12:08:43 -0800
 
  I have three tables, 'Ads' is a list of ads, 'Clicks' is a simple list
  of every time a Ad was clicked on with the Ads ID, and 'Views' is a
  simple list of views that ad got, with the Ads ID.
 
  I am trying to SELECT a list of all the ads, with a count for clicks
  and a count for views, but my LEFT JOIN is not producing what I
  thought.
 
  If the click count is 0, then the view count is OK, but if not, then
  the Click count and view count are equal, but a much too large number.
 
  If I just SELECT for views or clicks, then it works OK, it's when they
  are combined that it falls apart.
 
  SELECT A1.ID, Count(C.AdID) AS Clicks, Count(V.AdID) AS Views
  FROM Ads A1 LEFT JOIN Clicks C ON A1.ID = C.AdID
   LEFT JOIN Views V ON A1.ID = V.AdID
  group by A1.ID
 
  CREATE TABLE `Clicks` (
 `AdID` int(10) NOT NULL default '0'
 [snip]
  )
  CREATE TABLE `Views` (
 `AdID` int(10) NOT NULL default '0'
 [snip]
  )
  CREATE TABLE `Ads` (
 `ID` int(10) NOT NULL default '0'
 [snip]
  )
 
  I have tried a lot of combinations for LEFT JOIN with no luck.  I've
  read all the posts on this list and they don't seem to be doing what I
  am, or else I'm not seeing it.




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

Re: Reorder a table

[Bill Easton]

Martin,

The following will do the resequencing you requested.  Hope it helps.

create table temporary_table select * from the_table;
set @T=0;
update temporary_table set id=(@T:=(@T+1)), the_time=the_time order by
the_time;
delete from the_table;
insert into the_table select * from temporary_table;
drop table temp_table;

== Original message follows ==

Date: Sat, 6 Nov 2004 15:18:50 +0100 (MET)
From: Martin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
MIME-Version: 1.0
Subject: Re: Reorder a table
Message-ID: [EMAIL PROTECTED]
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

 He means that his primary key column has rows with id=1 and
 id=3, but not with id=2.
Thanks, this is exactly the problem. I did not blame mysql for anything - I
like it a lot. I blamed the news application, to which I referred as the
application. I did not develop the application/ don't have the sources and
this is why I am trying to find a workaround by modifying the table
structure.

That is, deletions have left holes in the sequence.  He
 theorizes that renumbering will help.  I agree with you that it won't.
Yes, there are holes in the sequence. But the reason is not due to
deletions, it is because the news application does not seem to use mysql's
auto increment mechanism. The application seems to have an internal
mechanism to increase the primary key and I cannot change this behaviour.
However this behaviour was no problem until I had to restore the news table
with a backup version. Since then I get the error message, which I
described.

So what the application does, is that it looks for the first free primary
key and inserts the dataset with this key. Referring back to my example: I
have datasets with IDs 1 and 3, id=2 is missing. In this case the
application inserts a new dataset with id=2. For my next dataset it would
try to use id=3, this is blocked (good! I want to keep this dataset), but it
prevents the storage of all further news datasets.

I am theorizing that renumbering the IDs will help, because in this case the
application would find id=4 as first free key, could insert it and I could
go on with inserting further datasets. So could someone please tell me how
to do the following with mysql?
1) copy the old news table into a new one and in this new table:
2) order the datasets by timestamp
3) change the primary key (an integer) for all datasets, so that the oldest
datasets gets id 1


Thanks,
Martin



 Michael

 Rhino wrote:

  - Original Message -
  From: Martin [EMAIL PROTECTED]
 
 Hi all,
 
 I have a problem with a news application, which writes news
 items into
 a mysql table. Recently it gets confused, when it inserts new news
 items
 
  into
 
 the table. I get the following exception:
 
 Duplicate entry '25' for key 1. root cause was
 java.sql.SQLException: Invalid argument value, message from server:
 Duplicate entry '25' for key 1
 
 It seems that this application tries to overwrite existing
 news items
 and mysql blocks this (which is very good actually). This
 is a problem
 with
 
  the
 
 application, but I can't change this behaviour. I had a look at the
 table and the primary key of the datasets is not available for all
 datasets, for example I have primary keys 1 and 3, but not 2. So I
 could imagine that it would help to reorder the table.
 
 
  This is a very confusing paragraph: first you express your
 happiness
  that MySQL is preventing the insertion of a duplicate, then
 you call
  it a problem. Why do you think it's a problem? Preventing duplicate
  keys in a table is one of the most important jobs a
 database has to do
  and your database is doing it.
 
  You state that this is an application yet that you can't change the
  behaviour. Why not? If it's your application rather than a
 purchased
  one without source code, you CAN change the application: it's yours.
 
  You state that you had a look at the table and the primary
 key is not
  available for all datasets. What do you mean not available: that
  the *primary key* doesn't exist or that the information
 about what the
  primary key is doesn't exist?
 
  You say that for example I have primary keys 1 and 3, but
 not 2: are
  you aware that a table can only EVER have one primary key?
 That is a
  rule in all relational databases, including MySQL. You
 can't possibly
  have two or three primary keys on a given table. Your
 primary key can
  contain several columns and you can have several UNIQUE keys on a
  table but you can only have ONE primary key on a table,
 regardless of
  how many columns it contains. Why do you think that re-ordering the
  table would help your situation? Also, I'm not clear what
 you mean by
  re-ordering: sorting the rows so that they appear in key
 sequence or
  unloading and reloading the rows into the table. Unless I'm
 completely
  misunderstanding your situation, NEITHER will help you with this
  problem because the database is not at fault here.
 
 
 Could 

Re: Index not used for select distinct?

[Bill Easton]

Matthias,

I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP.  Whatever
you do, it's going to read 10,991,123 of something and select distinct
values.  The use of the index saves a possible sort and allows reading a
smaller record than the full table.

A clever programmer, knowing that there are a handfull of values for IP,
could probably read the index structure and figure out how to avoid reading
all blocks of the index.  However, this is a bit of a special case, and the
MySQL optimizer isn't that smart.  You probably aren't going to do much by
tinkering with the indexes.

If you need to do this query frequently, you could add a table containing
just the IP values.  You would add to it (INSERT IGNORE) when adding to the
larger table.  You would either
(1)  Remember to check for a delete on the smaller table when deleting from
the larger table, or
(2)  Use a LEFT JOIN in your query to select those values in the smaller
table which exist in the larger table; you would then rebuild the smaller
table from time to time when it has too many obsolete entries. (The left
join should be fast, as it only has to look for one of each possible IP in
the larger table.)

HTH, Bill

Matthias Urlichs wrote:

We have a slight opimization problem here.

Given this table:

CREATE TABLE `test` (
  `IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
  `Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci
NOT NULL default 'WARN',
  `epoch` int(10) unsigned NOT NULL default '0',
  KEY `Trap` (`IP`,`Type`,`epoch`),
  KEY `IP` (`IP`)

)

... containing ten million records; the IP column holds only a handful
of distinct values. Given this, I would expect a select distinct ip
to return immediately.

However,



explain select distinct ip from test;


++-+---+---+---+--+-+--
+--+-+
| id | select_type | table | type  | possible_keys | key  | key_len | ref
| rows | Extra   |
++-+---+---+---+--+-+--
+--+-+
|  1 | SIMPLE  | test  | index | NULL  | IP   |  15 | NULL
| 10991123 | Using index |
++-+---+---+---+--+-+--
+--+-+

takes a *long* time and obviously scans the whole table.

Ideas, anybody?

MyISAM vs. InnoDB behave identically.
4.0 or 4.1.5 also didn't make a difference; I didn't test 5.0, as this
is supposed to be a production system.


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

Re: A query to swap the data in two fields

[Bill Easton]

How about:

update table1 set beds1=(@TEMP:=beds1), beds1=beds2, [EMAIL PROTECTED]

Seems to work for me.



= original message follows ==

To: [EMAIL PROTECTED]
From: zzapper [EMAIL PROTECTED]
Subject: Re: A query to swap the data in two fields
Date: Thu, 23 Sep 2004 20:01:09 +0100

On Thu, 23 Sep 2004 13:31:03 -0500,  wrote:

At 10:04 AM 9/23/2004, you wrote:
On Thu, 23 Sep 2004 11:03:08 +0100,  wrote:

 Hi,
 
 I want to swap the data in two fields
 
 eg
   t1.beds to t1.beds2
 and
  t2.beds2 to t1.beds
  do I need to store one of the fields temporarily?

Hey chums you normally get me an answer in minutes, is my question too
easy?
zzapper (vim, cygwin, wiki  zsh)
--

zzapper,
 Gee, have you paid your bill yet?g

The only thing I've been able to come up with is:

update table1 a, table1 b set a.beds1=b.beds2, a.beds2=b.beds1 where
a.rcd_id=b.rcd_id

You need to create a transitory value for one of the variables and this is
done by joining the table onto itself (the b table won't get updated as
table a gets updated). This solution should work on any column type.

There is probably a faster more efficient way of doing it (without a table
join), but only if we knew what the column types were. If they were String
then perhaps subscripting out the strings would be possible.

Mike

P.S. Now about your bill... :)
Mike, cheque in the post!
Sorry about my impertinence, I think I'm going to be a coward and create an
extra column for the
transitory value, as it's a one off operation. Just had a thought (dangerous
I know) but couldn't I
just rename the fields as that would do the same thing?


zzapper (vim, cygwin, wiki  zsh)
--

vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?

http://www.vim.org/tips/tip.php?tip_id=305  Best of Vim Tips



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

Re: Query takes terribly long

[Bill Easton]

Dirk,

If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.

Here's the query you asked about.

SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
  AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
  AND YEAR(putdatetime)  YEAR('2004-09-19')
  AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30

I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.

It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:

  putdatetime  '2003-09-20'

MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.

You can use

  putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)

and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.

Of course, if you really want just the data for the same day last year, you
could use

SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
  AND put=1 AND front=1
  AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30

HTH, Bill

== Dirk Schippers wrote: ==

Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long

No,

I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!

Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.

Thanks for all your help!
Dirk.

Jocelyn Fournier wrote:

Hi,

The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?

Regards,
  Jocelyn Fournier
  www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long




Hello,

Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE but that didn't change things, it might have sped
up the other queries on the table.

Probably this query can't be sped up because of the functions I use.

Is there another thing I can try to speed the query up?

And another question, is it normal that even when not using the cache
(always executing RESET QUERY CACHE; before the query), the query
sometimes only takes 0.33 seconds, and at other times the query takes 22
seconds? Is this something everyone here experiences? Or is there
something wrong with my configuration?

Regards,
Dirk.

Jocelyn Fournier wrote:



Hi,

I would say for this one you need an index on (put, front, putdatetime)


to


get rid of the filesorting.
Note that because you're transforming the putdatetime column with


functions,


MySQL will not be able to use index infos of putdatetime.
So it will use put and front index information to find the result, and
putdatetime to order the result.
If you add topcategory in your query, you will see MySQL will use the


index


(put, front, topcategory, putdatetime) without filesorting.

Regards,
 Jocelyn Fournier
 www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 11:55 AM
Subject: Re: Query takes terribly long






-- 

Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com

Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.


--060102010907050706010607--


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

Re: Query takes terribly long

[Bill Easton]

Good idea.  MySQL appears to use the index and skip between intervals.  (I'm not sure 
it always did...)

On the Feb. 29, I'd be disinclined to rely on MySQL's (or any other DB's) handling of 
invalid dates.  Note that it appears to take '2002-02-29' to mean Mar. 1, 2002, and 
'2002-02-30' to mean Mar 2, 2002.  So your query below, for 2-29, would give the 
counts for Mar. 1 in the prior years.  The original query you asked about gave zero 
for 2-29.  Figure out what you want for a result--and don't forget that in 2005, there 
isn't a 2-29, so you won't ever see the values for 2004-02-29.  Maybe you only care 
about an approximate result, and none of this matters.
  - Original Message - 
  From: Dirk Schippers 
  To: Bill Easton 
  Cc: [EMAIL PROTECTED] 
  Sent: Monday, September 20, 2004 6:06 PM
  Subject: Re: Query takes terribly long


  Hello Bill,

  Your explanation gave me an idea:

  SELECT id 
  FROM story 
  WHERE ((putdatetime='2003-09-20' AND putdatetime'2003-09-21' ) 
   OR (putdatetime='2002-09-20' AND putdatetime'2002-09-21')
   OR (putdatetime='2001-09-20' AND putdatetime'2001-09-21')) 
  AND put=1 AND front=1 
  ORDER BY putdatetime DESC 
  LIMIT 0,30;

  I know there are no stories before 2001, so I know where to stop with building the 
query. As years will pass, I don't think the query will become much slower with the 
extra OR lines added as they are all checked in the index.
  The only thing I'm worried about with this is, what about februari 29th, but Mysql 
seems to handle (putdatetime='2002-02-29' AND putdatetime'2002-02-30') (yes even 
that) quite well), but if it turns out to be a problem, I'll gladly code an exception 
for that day.

  Thanks for your tips, the query now always takes 0.03s in stead of about 0.33s.

  Dirk.

  Bill Easton wrote: 
Dirk,

If you use a function on the column, MySQL will not use the index on that
where clause.  You need to somehow arrange to not use a function
on the column with the index.

Here's the query you asked about.

SELECT id FROM story
WHERE MONTH(putdatetime) = MONTH('2004-09-19')
  AND DAYOFMONTH(putdatetime) = DAYOFMONTH('2004-09-19')
  AND YEAR(putdatetime)  YEAR('2004-09-19')
  AND put=1 AND front=1
ORDER BY putdatetime DESC
LIMIT 0,30

I'm presuming you have an index on (put, front, putdatetime).  MySQL will
read
all of the rows with put=1 and front=1, since it can't make use of the
YEAR(...)  YEAR(...) condition while it is running the query optimizer.

It looks like you are asking for the 30 latest rows for a given month and
day, but
for any year before the current one.  Try replacing the YEAR(...) test with:

  putdatetime  '2003-09-20'

MySQL should use the index to select only 30 records.  If there were 30
records
last year, it will be fast.  If it has to go to the year before, it will
still read all of the
earlier records, until it has found 30, but it will skip over the latest
year's records.

You can use

  putdatetime  ('2004-09-19' + interval 1 DAY - interval 1 YEAR)

and let MySQL do the calculation.  Note that you still get the rignt answer
for '2004-02-29'.

Of course, if you really want just the data for the same day last year, you
could use

SELECT id FROM story
WHERE putdatetime = '2003-09-19' and putdatetime'2003-09-20'
  AND put=1 AND front=1
  AND (MONTH(putdatetime)  2 OR DAY(putdatetime)  29)
ORDER BY putdatetime DESC
LIMIT 0,30

HTH, Bill

== Dirk Schippers wrote: ==

Date: Sun, 19 Sep 2004 18:35:24 +0200
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: Query takes terribly long

No,

I'm quiet sure that isn't the case as I am testing the optimized version
of my website on another database.
Hm, I'll let you know if I find out what's causing it.
If you have any more ideas, please inform me about them!

Storing day, month and year in other columns seems a little overkilling
for what it is used for, so I think my visitors should accept that that
query is a slow one.

Thanks for all your help!
Dirk.

Jocelyn Fournier wrote:

  Hi,

The query is still slow because MySQL can't use the index to search the
right putdatetime.
So unless you store in seperated columns day, month and year, so that you
can query on those fields without a function, this will remain slow.
Are you sure no other long queries are running on this table, which could
lock the table (and thus explain why sometimes it takes up to 22 seconds) ?

Regards,
 Jocelyn Fournier
 www.presence-pc.com
- Original Message - 
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Sunday, September 19, 2004 5:49 PM
Subject: Re: Query takes terribly long




Hello,

Indeed, when I use {put,front,putdatetime}, no filesort is used. But the
query is still very slow.
It takes about 2 seconds. And again, mysql prefers not to use your key,
but I can force that of course.
I did an ANALYZE TABLE

Re: can not find file *.MYI

[Bill Easton]

InnoDB doesn't use any *.MYI, only *.FRM.  The indexes are in the data
files.

You might check the 4th byte of the .FRM file.  x'0C'=InnnoDB, x'09'=MyISAM.

If the InnoDB data files are good, there was a post earlier this year from
Heikki Tuuri about how to get the structure from there.

If the .FRM file is good, I posted a way to recover the structure--it's
worked for a few people.  A copy of the post is at the end of this message.

== original message follows ==

From: Yann Larrivee [EMAIL PROTECTED]
To: V. M. Brasseur [EMAIL PROTECTED]
Subject: Re: can not find file *.MYI
Date: Mon, 6 Sep 2004 20:07:12 -0400
Cc: [EMAIL PROTECTED]

On September 6, 2004 18:33, V. M. Brasseur wrote:
 Your index files appear to have disappeared during your archive.  You
 can rebuild them though.  Have a look at the Table Maintenance and
 Crash Recovery section of the manual:

 http://dev.mysql.com/doc/mysql/en/Table_maintenance.html

 Pay particular attention to the REPAIR TABLE syntax.  That will give
 you want you need.

 Cheers,



As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use it if the=
=20
`.MYI' index file is missing or if its header is corrupted. 

This seems to be my situation, but all my tables used to be innodb plus i a=
m=20
using 4.0.18 at this moment.

I did change the permissions to the proper group but it did not change=20
anything to my situation.

Is there any other way to fix this without upgrading ?

Thanks

Yann Larriv=E9e

== Copy of earlier post on recovering structure from InnoDB .frm
files ==

Try the following

(0) Make sure you have the .frm files backed up

(1) If you have foo.frm, delete foo.* from the data directory, then create
a new MyISAM table.  Doesn't matter what's in it.  E.g., create table foo (x
int).

(2) Copy your saved .frm file back instead of the one just created.

(3) If the table you lost was InnoDB, change the 4th byte of the file
from hex 0C to hex 09.  (This makes it look like a MyISAM .frm)

(4) You should be able to run show create table foo from the MySQL client.



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

Re: please explain why this query isn't optimized

[Bill Easton]

Well, actually, there are 2.878 Meg rows, or 2878k.

What's happening is that it's using the index to find all of the rows where
changed  0, then scanning for the maximum.  If you just look for the
maximum, then discard it if it's not greater than 0, it will be much faster.

The following with InnoDB tables.  (MyISAM has the maximum pre-calculated,
so it should be even faster.)

 mysql SELECT MAX(changed) FROM archived_stats where changed0;
+--+
| MAX(changed) |
+--+
|8 |
+--+
1 row in set (0.21 sec)

mysql SELECT MAX(changed) as maximum FROM archived_stats having maximum 
0;
+-+
| maximum |
+-+
|   8 |
+-+
1 row in set (0.00 sec)

mysql explain SELECT MAX(changed) FROM archived_stats where changed0;
++---+---+-+-+--+---
+--+
| table  | type  | possible_keys | key | key_len | ref  | rows
| Extra|
++---+---+-+-+--+---
+--+
| archived_stats | range | changed   | changed |   4 | NULL | 52802
| Using where; Using index |
++---+---+-+-+--+---
+--+

mysql explain SELECT MAX(changed) as maximum FROM archived_stats having
maximum  0;
+--+
| Comment  |
+--+
| Select tables optimized away |
+--+

You could also use the following.  The idea is to get MySQL to start from
the highest thing in the index, not the lowest.

mysql SELECT changed FROM archived_stats where changed0 order by changed
desc limit 1;
+-+
| changed |
+-+
|   8 |
+-+
1 row in set (0.00 sec)

mysql explain SELECT changed FROM archived_stats where changed0 order by
changed desc limit 1;
++---+---+-+-+--+---
+--+
| table  | type  | possible_keys | key | key_len | ref  | rows
| Extra|
++---+---+-+-+--+---
+--+
| archived_stats | range | changed   | changed |   4 | NULL | 52802
| Using where; Using index |
++---+---+-+-+--+---
+--+
1 row in set (0.00 sec)


== original message follows ==

From: Egor Egorov [EMAIL PROTECTED]
Date: Mon, 06 Sep 2004 13:02:11 +0300
Subject: Re: please explain why this query isn't optimized
To: [EMAIL PROTECTED]

Dave Dyer [EMAIL PROTECTED] wrote:

 Before I post it as a bug, perhaps someone can explain why
 this query is not optimized to use the index (it has to examine all 287k
rows).

 mysql explain SELECT MAX(changed) FROM archived_stats where changed0;
 | table  | type  | possible_keys | key | key_len | ref  | rows
| Extra|
 | archived_stats | range | changed   | changed |   4 | NULL |
2878820 | Using where; Using index |

There are 2.878k rows, not 287k.

And it's optimized as much as possible, the key is used.


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

Re: huge innodb data files

[Bill Easton]

Hmm, well...  It really shouldn't take 2 min to select from an empty table,
no matter what you have in my.cnf.

So, something else is happening.

One way that InnoDB can take forever to read from an empty table is if
there's a transaction still in progress that was started some time ago.
Perhaps there's a process that's looking at the database, having started a
transaction, and has never committed.  Are you sure this isn't the case?
Or, perhaps you have many transactions which get interval locks on your
empty table?  Does the problem go away if you stop and restart the server?
How about if you do a TRUNCATE TABLE on your offending empty table, which
will discard it and recreate it?

If that's not it, perhaps something is wrong with the InnoDB database files.
Can you dump the data with mysqldump, delete the InnoDB database files, and
recreate the database?

If that doesn't help, or if the problem returns, you should post the result
of a SHOW INNODB STATUS when the problem is happening.  You may need to pay
MySQL AB or InnoDB Oy for some help.

= original message follows =

From: Ronan Lucio [EMAIL PROTECTED]
To: Mayuran Yogarajah [EMAIL PROTECTED],
 [EMAIL PROTECTED]
Subject: Re: huge innodb data files
Date: Fri, 27 Aug 2004 09:49:51 -0300

Mayuran,

Well, I´m not a MySQL expert, but I think that a good
configuration in the my.cf file can make it better.

Ronan

- Original Message -
From: Mayuran Yogarajah [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 26, 2004 5:37 PM
Subject: huge innodb data files


Our DB in production currently has 2 innodb data files,
the second one (which is marked autoextend) has now
grown past 26 gigs.  We are experiencing weird speed
problems with one of the tables.  Even though there are
no rows in this table, performing any kind of select takes
about 2 minutes to execute.  Has anyone had a similar
problem before ? What can I do to speed up queries to
this table ?

thanks,
M


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

Re: Replication blocked

[Bill Easton]

A few brief comments on this one...

(1)  Multi-threaded would probably cause thousands of problems 
 Well, not necessarily.

Currently, the slave reads transaction A from
the relay log, executes its actions, commits it, then reads transaction
B, executes and commits.  It wouldn't be unreasonable to allow the
two transactions to execute on different threads, provided that
(a) B can't commit until A does and (b) in case of deadlock, B is rolled
back and re-executed, rather than A. 

Small matter of programming, but not necessarily intractable.

(2)  If replication is being used for backup, a transaction on the master
is backed up as soon as it has been copied to the relay log.  A delay
in executing the relay log isn't a problem unless either (a) you are doing
queries against the slave and need timely data or (b) such delays
are so frequent that the slave can never catch up.

(3)  In the original post, the problem was that during 12 minutes to 
create an index on the slave, other transactions can be locked out.
Presumably, on the master, shared locks are set on every row
of the table as the ALTER TABLE is run, so it is certainly possible
that updates to the table can be delayed or that a deadlock can
happen on the master, as well.

= Original message follows =

Date: Fri, 13 Aug 2004 14:42:28 -0700
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Donny Simonton [EMAIL PROTECTED]
Cc: 'Batara Kesuma' [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Replication blocked

On Fri, Aug 13, 2004 at 01:19:14AM -0500, Donny Simonton wrote:

 There is only one thread for replication on the slave.  It does one
 step a time.  If you use mysqlbinlog on one of your binary files on
 your master, you will see exactly how it all works.

No, there are 2 threads: the IO (or relay) thread, and the SQL thread.

 Multi-threaded would probably cause thousands of problems.  Unless
 it was threaded per table, but that would still cause problems
 because of multi-table deletes and updates.

Agreed.

Jeremy

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

Count of two fields

[Bill Easton]

select ListName, count(*), sum(Info is not null)
from ListTable
group by ListName

= Original Message Follows =

From: John Berman [EMAIL PROTECTED]
Subject: Count of two fields

Wonder if you can help

I have a table with a number of fields but the 

List Name

Member

Info

Ever member in the table has a list and some members have something in the
info field, Im after a count by list name

So for instance the result im after would be something like

List1 55 3

So  for list1 there are 55 members associated with is and out of the 55 3
have something in the info field

Help appreciated

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

Count even when empty

[Bill Easton]

SELECT dbo.lists_.Name_, COUNT(dbo.members_.EmailAddr_) AS nojgid
FROM  dbo.lists_
INNER JOIN dbo.topics_ ON dbo.lists_.Topic_ = dbo.topics_.Title_
LEFT JOIN  dbo.members_  ON dbo.members_.List_ = dbo.lists_.Name_
GROUP BY dbo.lists_.Name_,

The first inner join gives you a row for every list.
The left join gives you a row for every (member, list) pair plus a row (with
a null) for every list with no member.
The COUNT will count the non-null things
Note that you want to select/group on dbo.lists_.Name_, not
dbo.members_.List_, which may be null.


HTH

= Original Message Follows =

From: John Berman [EMAIL PROTECTED]
Subject: Count even when empty
Date: Sun, 15 Aug 2004 01:02:52 +0100

Further to my earlier query

Im using this

SELECT dbo.members_.List_, COUNT(dbo.members_.EmailAddr_) AS nojgid
FROM dbo.members_ INNER JOIN
  dbo.lists_ ON dbo.members_.List_ = dbo.lists_.Name_
INNER JOIN
  dbo.topics_ ON dbo.lists_.Topic_ = dbo.topics_.Title_
WHERE (dbo.members_.jgid IS NULL)
GROUP BY dbo.members_.List_

This works but I want to get a 0 when dbo.members_.jgid IS NULL so my output
shows a 0 for a list, currently is does not show the list when its empty
(obviously because I use is null)


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

Last insert id problem/bug

[Bill Easton]

Scott,

The bottom line is that LAST_INSERT_ID() isn't guaranteed to be unchanged by
an INSERT IGNORE that doesn't insert anything, so you have to do something
else.

You need to test that a row was, in fact, inserted by the first INSERT
IGNORE and, if not, do not execute the second INSERT.

If that's difficult, and you really want to use just SQL statements, you
could have the second insert test that there is a row in the first table
with id=LAST_INSERT_ID(), perhaps using INSERT ... SELECT.  I'd worry,
however, that you don't really have a guarantee of the value of
LAST_INSERT_ID().


Here's what's probably happening.  (I haven't looked at the internals, but
it's a close enough model.)  On the second time you do the first INSERT
IGNORE,
 - It reserves id 129 and remembers that as the last insert id
 - It does the INSERT
 - The insert fails, but the error is ignored
However, the last insert id remains 129.  (You have inserted zero records,
the first of which has a key of 129.)

All you are guaranteed is that
(a) right after you insert something, and before you try to insert
anything else, LAST_INSERT_ID() returns the first id inserted by that INSERT
statement, if any.
(b) for any thread, the id's increase
(c) no two threads ever get the same id
However, LAST_INSERT_ID() is not quite transactional, even if you are using
transactional tables.  Once an id has been reserved, it's never given back,
even if the SQL statement is rolled back--so you can get gaps.  The reason
for this is so that two threads can independently insert records with an
auto_increment key without either one having to wait for the other.  This
can make a substantial difference to the concurrency of transactions.

HTH

Bill

- original message follows --

Date: Thu, 22 Jul 2004 22:56:12 -0700
Subject: Last insert id problem/bug
From: Scott Haneda [EMAIL PROTECTED]

I am not sure this is just how it is, my issue, or a bug, I searched the
bugs database, but don't really know what to call this in order to properly
look up the info:

MySQL 4.0.18-standard

CREATE TABLE `addresses` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `first_name` varchar(48) NOT NULL default '',
  `last_name` varchar(48) NOT NULL default '',
  `email_address` varchar(48) NOT NULL default '',
  `updated` timestamp(14) NOT NULL,
  `added` timestamp(14) NOT NULL default '00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `emailuid` (`email_address`,`user_id`)
) TYPE=InnoDB PACK_KEYS=0 COMMENT='foo';

CREATE TABLE addresses_incampaign (
  id int(11) NOT NULL auto_increment,
  user_id int(11) NOT NULL default '0',
  address_id int(11) NOT NULL default '0',
  campaign_id int(11) NOT NULL default '0',
  updated timestamp(14) NOT NULL,
  added timestamp(14) NOT NULL default '00',
  PRIMARY KEY  (id),
  UNIQUE KEY address_id (address_id)
) TYPE=InnoDB PACK_KEYS=0 COMMENT='bar';

As you can see, I have a unique key on addresses.email_address and
addresses.user_id, note the unique key on address_id in the second table.

I insert the following data TWICE:

INSERT IGNORE INTO `addresses`
(`user_id`,`first_name`,`last_name`,`email_address`) VALUES (1, 'Claire',
'Altman', '[EMAIL PROTECTED]');
INSERT IGNORE INTO `addresses_incampaign` (`user_id`, `address_id`,
`campaign_id`) VALUES (1,LAST_INSERT_ID(), 2);

Notice I am picking up the last_insert_id in the second insert, I then
insert that data again, since I am using insert ignore and since there are
unique keys on this, I should only still have one records in each table.

Results are as follows:
mysql select * from addresses;
+-+-++---+---+
| id  | user_id | first_name | last_name | email_address |
+-+-++---+---+
| 148 |   1 | Claire | Altman| [EMAIL PROTECTED] |
+-+-++---+---+
1 row in set (0.00 sec)

mysql select * from addresses_incampaign ;
+-+-++-+
| id  | user_id | address_id | campaign_id |
+-+-++-+
| 128 |   1 |148 |   2 |
| 129 |   1 |149 |   2 | - SOULD NOT HAVE HAPPENED
+-+-++-+
2 rows in set (0.00 sec)

Record 129 should not have been inserted, it should not have ever gotten a
insert ID back from mysql, mysql said back 149, which is in fact the next
record, but no new record was added to addresses so it really is not valid.

Workarounds and suggestions?
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.




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

Re: Sorting Varchar

[Bill Easton]

OK, I'm sorry.  The solution I gave doesn't work.

You need to have some sort of conditional in the sort expression so that
numbers are sorted numerically and other things are sorted alphanumerically.
I'm not aware of a test for numeric vaues in MySql, so you need to use some
trick to differentiate between the two.

This seems to work, provided there are no negative numbers and the text
things start with letters

  ORDER BY IF (cost  ':', LPAD(cost,10,'0'), CONCAT('1',cost))

In this,
  cost  ':' tests if the string starts with a digit (':' is the character
after '9')
  LPAD(cost,10,'0') pads the integer on the left with zeros -- replace the
10
 with a number at least one more than the maximum number of digits
  CONCAT('1',cost) causes the text items to sort to the end (the numbers
 now start with '0')

Pasha's solution is probably cleaner, but you have to change the
table definition.  For Pasha's solution to work, you would need to
have the text_val column be null (or '') when the value is numeric.


 Subject: Re: Sorting Varchar
 From: [EMAIL PROTECTED]
 Date: Wed, 12 May 2004 18:13:17 -0400

 That didn't do it.  I got 0,0,1050,1168,1195, 1975, 150,155,16500,170,178.
 . .  The non-numerics came out last (which I want).  There are two zeros
 but no negative numbers.  Any alternatives?

 Ken

 **
 On Wed, 12 May 2004 07:47:11 -0400, Bill Easton [EMAIL PROTECTED]
 wrote:

  You could also use order by cost+0,cost.  This puts the non-numerics
  first, then the numerics in numerical order.  (You'd need to get fancier
  if there are non-numerics starting with a digit or numerics = 0.)
 
  From: Sasha Pachev [EMAIL PROTECTED]
 
  [EMAIL PROTECTED] wrote:
   How do I set up a column (cost) that contains numbers and text so
that
   the numbers will sort the numbers accurately?  Using varchar results
  in
   a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. .
.
   when the command is order by cost. Almost all of the text is By
  County.
 
  Ken:
 
  Consider having two columns - num_val, and text_val, and then order by
  num_val,
  text_val
 
  --
  Sasha Pachev
  Create online surveys at http://www.surveyz.com/


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

Fw: Sorting Varchar

[Bill Easton]

 You could also use order by cost+0,cost.  This puts the non-numerics
 first, then the numerics in numerical order.  (You'd need to get fancier
 if there are non-numerics starting with a digit or numerics = 0.)

  From: Sasha Pachev [EMAIL PROTECTED]
 
  [EMAIL PROTECTED] wrote:
   How do I set up a column (cost) that contains numbers and text so that
   the numbers will sort the numbers accurately?  Using varchar results
in
   a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. . .
   when the command is order by cost. Almost all of the text is By
 County.
 
  Ken:
 
  Consider having two columns - num_val, and text_val, and then order by
 num_val,
  text_val
 
  -- 
  Sasha Pachev
  Create online surveys at http://www.surveyz.com/



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

Re: first LIMIT then ORDER

[Bill Easton]

Interesting comment.

I find by experiment that
  (select * from FOO order by a desc limit 10) order by a;
removes duplicates, but, if I drop the second order clause,
  (select * from FOO order by a desc limit 10);
duplicates are retained.

Why is the first a union, but not the second?  Just curious.

 From: Keith C. Ivey [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Date: Fri, 23 Apr 2004 11:27:38 -0400
 Subject: Re: first LIMIT then ORDER

 On 23 Apr 2004 at 7:23, Bill Easton wrote:

  The last suggestion is useful when you do care which entries you get,
  as you can use one order for limit and another for presentation. For
  example, if you'd like the LAST 10 rows, but sorted in FORWARD order,
  you can use something like
  
  (select * from HISTORY order by version desc limit 10) order by
  version;
  
  And I thought I'd have to wait for subqueries...

 One small gotcha that Anders Karlsson pointed out to me through Paul 
 DuBois:  This one-query union syntax doesn't allow you to use the ALL 
 keyword after UNION (since the UNION keyword isn't even there).  That 
 means it will always eliminate duplicate rows (like DISTINCT).  That 
 hasn't come up when I've used it, since I've never been selecting 
 result sets that could contain duplicate rows, but it's something to 
 keep in mind.

 -- 
 Keith C. Ivey [EMAIL PROTECTED]
 Tobacco Documents Online
 http://tobaccodocuments.org


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

Re: first LIMIT then ORDER

[Bill Easton]

The last suggestion is useful when you do care which entries you get,
as you can use one order for limit and another for presentation.
For example, if you'd like the LAST 10 rows, but sorted in FORWARD
order, you can use something like

(select * from HISTORY order by version desc limit 10) order by version;

And I thought I'd have to wait for subqueries...

Date: Thu, 22 Apr 2004 10:35:17 -0500
To: Keith C. Ivey [EMAIL PROTECTED], [EMAIL PROTECTED]
From: Paul DuBois [EMAIL PROTECTED]
Subject: Re: first LIMIT then ORDER

At 11:21 -0400 4/22/04, Keith C. Ivey wrote:
On 22 Apr 2004 at 12:31, Johan Hook wrote:

  Assuming you want to order your arbitrary selection you could
  do something like:
  (SELECT t.Id FROM tab t LIMIT 10)
  UNION ALL
  (SELECT t.Id FROM tab t WHERE 1  0)
  ORDER BY t.Id

You don't even need to include the dummy query.  You can do a UNION
of one result set.  This should work:

 (SELECT t.Id FROM tab t LIMIT 10)
 ORDER BY t.Id

I wrote this comment on the mysql.com site:

 It's not documented above, but you can use ORDER BY on a UNION
 that consists of only one SELECT (and thus doesn't actually
 include the word UNION). Suppose you want the last 5 entries
 in a table, but you want them in ascending order. You can use
 this query:

 ( SELECT * FROM table_name ORDER BY ranking DESC
 LIMIT 5 ) ORDER BY ranking;

 Similarly, you could select the top 10 records from a table
 ordered by one column and then sort them alphabetically by
 another column.

Now, the fact that the syntax isn't documented may mean that it will
disappear, but it's reasonable and useful.

I doubt if it will disappear.  I think this is a better suggestion
than using a temporary table.  Thanks.

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com


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

Re: Randomly selecting from table

[Bill Easton]

If you want to guarantee that the selections are different, rand()
doesn't quite do it, as you will get a repeated value with the
appropriate probability.  You will need to keep a record of
what values have already been seen.  Then, use something
like
  select ...
  from my_table left join my_records_used using (my_id)
  where my_records_used.my_id is null
  order by rand()
  limit 1

 Date: Tue, 20 Apr 2004 13:08:01 -0700 (PDT)
 Subject: Re: Randomly selecting from table
 From: Daniel Clark [EMAIL PROTECTED]
 To: Eve Atley [EMAIL PROTECTED]

 Guess you could use the rand() function and look for a matching row id!?!

  Is it possible to randomly select from all entries in a table, but have
it
  be 2 different ones each time? If so, what documentation should I be
  looking
  at?
 
  I am using PHP and MySQL together, if this helps.
 
  Thanks,
  Eve


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

how to rewrite this query without using temporary table

[Bill Easton]

Lorenzo,

Try this:

select P.ssn
from patientrecall_table as P
  left join appointment_table as A
on P.ssn = A.ssn
   and appdate between '2004-04-15' and '2004-04-30'
where P.nrd = current_date
  and A.ssn is null 

Before applying the where clause, the left join will have
  - a row for each appointment in the given range
  - a row for each patient who doesn't have an appointment
Then the where clause throws away the ones with appointments.

regards,

Bill

 From: lorenzo.kh [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: how to rewrite this query without using temporary table
 Date: Thu, 15 Apr 2004 12:03:04 +0800

 I  have 2 tables.
 patientrecall_table and appointment_table.
 The patientrecall_table used to store what is the next
 recall date for the patient while the appointment_table
 will store all the appointments made.
 Now, I'd like to get through this:
 Patients Due for Recall, but No Scheduled Appointment between certain =
 date.
 In other words, I will need to:
 select those patients who their next recall date greater than
 today date AND they don't have appointment made between the
 date from and date to.
 Currently, what I did was using the temporary table approach.And it was =
 working fine.

 create temporary table tmpA(ssn varchar(9) not null);
 insert into tmpA select ssn from appointment_table=20
 where (appdate between '2004-04-15' and '2004-04-30') group by ssn;
 select a.ssn from patientrecall_table as a
 left join tmpA as b
 on a.ssn=3Db.ssn
 where a.nrd=3Dcurrent_date and b.ssn is null;
 drop table tmpA;

 Is there any other ways that I can use instead of using the temporary =
 table? Please advise.

 Thank you.

 //***

 mysql desc patientrecall_table;
 +---++--+-++---+
 | Field | Type   | Null | Key | Default| Extra |
 +---++--+-++---+
 | ssn   | varchar(9) |  | PRI ||   |
 | nrd   | date   |  | | -00-00 |   |
 +---++--+-++---+
 2 rows in set (0.00 sec)


 mysql desc appointment_table;
 +-+-+--+-++---+
 | Field   | Type| Null | Key | Default| Extra |
 +-+-+--+-++---+
 | appdate | date|  | | -00-00 |   |
 | ssn | varchar(9)  |  | ||   |
 | remark  | varchar(50) |  | ||   |
 +-+-+--+-++---+
 3 rows in set (0.00 sec)

 Server version: 4.0.18-nt



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

using old frm files

[Bill Easton]

Dan,

Try the following

(0) Make sure you have the .frm files backed up

(1) If you have foo.frm, delete foo.* from the data directory, then create
a new MyISAM table.  Doesn't matter what's in it.  E.g., create table foo (x
int).

(2) Copy your saved .frm file back instead of the one just created.

(3) If the table you lost was InnoDB, change the 4th byte of the file
from hex 0C to hex 09.  (This makes it look like a MyISAM .frm)

(4) You should be able to run show create table foo from the MySQL client.

HTH

Bill Easton
Lexpar

 Date: Tue, 23 Mar 2004 11:04:49 -0500 (EST)
 From: dan [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: using old frm files

 we had a database blown away (oops) and we still have the old
 .frm files, is there any way to get this structure back?

 is there a way to view the contents of the .frm files to
 see how the tables/indexes were setup?

 I can't find anything in the docs, faqs, etc.

 Dan.


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

Re: Partial Replication ?

[Bill Easton]

You can suppress writing the delete query to the binary log.

mysqlset sql_log_bin = 0;
mysqldelete ... ;
mysqlset sql_log_bin=1; 

Bonnet R?my [EMAIL PROTECTED] wrote:
 Hello,
 
 I have a database which is flushed every four hours,
 and
 I want to replicate it without replicating the delete
 queries . Is this possible ?
 (sorry for my awful english)

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

Re: Setting variables on update

[Bill Easton]

The following works on 4.0.16, for those of us who'd like to stick to the
stable release:

update controltable set nextid = 1 + (@prevval := nextid)

Note.  There was a problem with this in 4.0.13--namely, the value of
@prevval sometimes was garbage.  I reported this via the list, but got no
response--however, the problem seems to have gone away in 4.0.14.

-Original Message-
From: [EMAIL PROTECTED]
Subject: RE: Setting variables on update
Date: Mon, 23 Feb 2004 11:50:38 -0700

Try this...works on 4.1.2

UPDATE  ControlTable SET NextID=3D 1 + (select @prevval :=3D NextID)

Ed

-Original Message-
From: Sasha Pachev [mailto:[EMAIL PROTECTED]
Sent: Monday, February 23, 2004 11:19 AM
To: Matt Fagan
Cc: [EMAIL PROTECTED]
Subject: Re: Setting variables on update


Matt Fagan wrote:
 Hello,
=20
 I'm trying to run an SQL update statement like:
=20
 UPDATE ControlTable SET @prevval :=3D NextID, NextID =3D NextID
 + 1
=20
 I've tried using a range of different syntax, but nothing
 seems to work. This is something that does work in another
 SQL server I used to use.
=20
 Does anybody know if this is something MySQL can do, and if
 so, what is the correct syntax? Note that I can't use an
 autoincrement column because the IDs aren't being put into
 a database.
=20
 I've searched through MySQL docs and mailing lists but
 couldn't find any mention of this. Any help would be
 appreciated.

A UDF or your own static function might work. Worst case, MySQL source
can be=20
modified to make this work.


--=20
Sasha Pachev
Create online surveys at http://www.surveyz.com/


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

Re: Unique IDs

[Bill Easton]

Craig,

Instead of delaying a second, why not just add a second to the timestamp?
The following should work just fine:

create table tst (ts timestamp primary key, other_stuff varchar(127) not
null);
...
insert into tst
  select greatest(now(), max(T.ts) + interval 1 second)
   , value of other_stuff
  from tst T;

I checked that this works in 4.0.16.  It even appears to work for inserting
the first row into an empty table.  (I think it probably didn't work in
3.x.)

Of course, during a burst, the stored timestamps could be a few seconds
later than the actual time, but, at least the timestamps are unique
and ascending.

You could also keep the timestamp in a separate table and increment it,
using a variable:

update timestamp_table set ts = (@T := greatest(now(), ts + interval 1
second));

Then you can use @T as your generated unique ID in a subsequent
statement, within the same connection.  Of course the timestamps
could be out of order, unless you lock your other table or do
everything within a transaction.  Maybe this matters, maybe not.

auto_increment is probably still better.  With innodb, there is
less locking required.

You can use + 1 instead of + interval 1 second, but it may give
different results some day if MySQL changes the precision of timestamp.

HTH

Bill Easton

 Subject: Re: Unique IDs
 From: Craig Jackson [EMAIL PROTECTED]
 To: Keith C. Ivey [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Date: Thu, 12 Feb 2004 11:57:24 -0600

 On Thu, 2004-02-12 at 11:47, Keith C. Ivey wrote:
  Craig Jackson [EMAIL PROTECTED] wrote:
 
   I have a very large web app that uses timestamp for unique IDs.
   Everything was rolling fine until we started getting many users per
   second, causing some of the unique IDs to not be unique -- users were
   being assigned the same timestamp. Since the web app is so large we
   don't want to change the method of assigning IDs as it would create a
   major project.
 
  I don't understand.  If you're getting many users per second, and
  your timestamps have 1-second resolution, how could you possibly
  solve the problem without changing the method of assigning IDs?
  Are the many users per second periods just short bursts, and you're
  really only getting several hundred users per day?  If so, I guess
  you could keep waiting a second and trying the insert again, but that
  could lead to indefinite delays if traffic gets high.  I think you've
  got to bite the bullet and change the unique ID to something that's
  actually unique -- even an AUTO_INCREMENT would work.

 Thanks for the speedy reply and I have already recommended
 auto_increment for the solution. We do need that quick fix until the
 problem is fixed. How would I go about making Mysql wait one second
 between inserts. We only get about 1000 hits per day, but they tend to
 be concentrated in short time intervals.


 
  -- 
  Keith C. Ivey [EMAIL PROTECTED]
  Tobacco Documents Online
  http://tobaccodocuments.org


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

MyISAM Table Corruption

[Bill Easton]

Hassan,

By Murphy's law, they WILL get corrupted if you don't have a
backup.  You need a current backup, or you need an older
backup and a way to redo the updates.

That said, if you do a FLUSH TABLES after your update, then
corruption is unlikely--no more likely than for any other OS file.

After an update to a MyISAM table and before doing a FLUSH 
TABLES, you can easily get corruption on, say, a power
failure.  (This is observed behavior, despite a claim
in the manual that the data are written to disk after
the update statement.  The data file is incompletely
written, so myisamchk doesn't recover it.)


 From: Hassan Shaikh [EMAIL PROTECTED]
 To: MySQL List [EMAIL PROTECTED]
 Subject: MyISAM Table Corruption
 Date: Tue, 3 Feb 2004 12:50:29 +0400

 What are the chances of MyISAM tables corruption when the table is
 update rarely? (Once in a 60+ days). It's basically a lookup table
 used mainly in SELECT statements.

 Thanks.

 Hassan




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

More Left Join problems

[Bill Easton]

Jacque,

Based on your explain, I'd guess that you don't have any indexes.  Probably,
you need (at least) an index on the join column on the second table, as,
otherwise, MySQL will read the second table once for each row of the first
table.

This probably doesn't have anything to do with the fact that it's a left
join.  You'd probably get the same result with an inner join, since the
tables
appear to be about the same size.

Some hints on reading the explain:  The ALL means MySQL is going to
read the whole table instead of using an index.  The [NULL]'s mean there
is no available index.  The filesort means it's going to get the
content of the table and sort it.  (You could get rid of that with
an index, but it's not all bad, unless your data is already stored in
approximately the correct order.)

I haven't tried to decipher your query.  It's a big, unreadable blob,
and I'm not willing to put in the time.  People posting to this list
would do well to reformat and indent example queries so that they are
easy for a human to read.  It would also be good to make the examples
as short as possible.  I probably saw and ignored your earlier
question for that reason; I usually just skip messages where it
would take too long to decipher the question--I'd assume that other
people do the same.

HTH

Bill

 Date: Thu, 29 Jan 2004 08:03:25 -0800
 From: Jacque Scott [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: More Left Join problems

 I had posted a message earlier this week about my 'Left Join' taking too
 long to run.  This seems to be happening on all of my queries that have
 a 'Left Join'.  Does anyone have any suggestions on why this would
 happen?

 Here is one query which took 45.72 sec to run:

 SELECT Products.NSIPartNumber, Products.Cost AS Cost, If(Bom.ProductID
 Is Not Null,x,) AS BOM, Products.lngLaborHrs AS LaborHrs,
 Products.ModelNo, Products.USPrice AS USPrice, Products.VendorPart,
 Products.Description, Products.ProductID, Null AS SumOfQty,
 Products.Obsolete FROM Products LEFT JOIN BOM ON Products.ProductID =
 BOM.ProductID GROUP BY Products.NSIPartNumber, Products.Cost,
 If(Bom.ProductID Is Not Null,x,), Products.lngLaborHrs,
 Products.ModelNo, Products.USPrice, Products.VendorPart,
 Products.Description, Products.ProductID, Products.Obsolete Having
 ((NSIPartNumber Like %02-001%) AND (Obsolete-1))  ORDER BY
 NSIPartNumber;


 Here is the results when I use Explain.  I don't really know how to
 read this.

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

++-+++---++-
++++
 | 1  | SIMPLE  | Products| ALL| [NULL]| [NULL] |
 [NULL]  | [NULL] | 6852   | Using temporary; Using filesort|
 | 1  | SIMPLE  | BOM| ALL| [NULL]| [NULL] |
 [NULL]  | [NULL] | 5995   ||

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

 Any help is appreciated.

 Jacque


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

Re: More Left Join problems

[Bill Easton]

You need an index on BOM.ProductID

try:
alter table BOM add index (ProductID);
then run your query again

Some additional notes on your query:

(1) You have an expression involving BOM.ProductID in your field list.
Since that column is not in the group by clause, you'll get a ProductID from
some record in the group--no way to predict which one.  (Unless, of course,
all rows a given NSIPartNumber has the same ProductID.)  Note that ANSI SQL
would not let you have this column in the field list and not in the group
by.

If you have an NSIPartNumber for which some ProductID's have a corresponding
BOM record and some don't, you'll get a x or not unpredictably, at MySQL's
whim.

If you want to know if there is ANY ProductID for an NSIPartNumber without a
corresponding record, you could use an expression involving something like
MAX(BOM.ProductID IS NULL), which will be 1 if there is one and 0 if there
isn't.

(2) You probably want the stuff in your HAVING clause to be in a WHERE
clause instead.  HAVING means that the entire result set is generated and
then filtered again to remove some records--although MySQL may optimize it
so it doesn't really do that.  (Just change HAVING to WHERE and move it
before the GROUP BY.)

It's good practice to use the where clause unless you really need having.
You might really need having if you are selecting from the result based on
group functions such as max() or count()--then, the values you are testing
aren't available for the where clause to use.
  - Original Message - 
  From: Jacque Scott
  To: [EMAIL PROTECTED] ; [EMAIL PROTECTED]
  Sent: Thursday, January 29, 2004 2:18 PM
  Subject: Re: More Left Join problems


  Thanks for your time.  I didn't think of formatting the query.  Here is
the query in a more readable format.  I have also taken out most of the
columns in the SELECT.  The query still takes 44 seconds.

  SELECT Products.NSIPartNumber, If(Bom.ProductID Is Not Null,x,) AS
BOM, Products.Obsolete
  FROM Products
  LEFT JOIN BOM ON Products.ProductID = BOM.ProductID
  GROUP BY Products.NSIPartNumber,  Products.Obsolete
  HAVING ((NSIPartNumber Like %02-001%) AND (Obsolete-1));


  Hopefully this will be easier to decipher.


   Bill Easton [EMAIL PROTECTED] 1/29/2004 10:34:21 AM 
  Jacque,

  Based on your explain, I'd guess that you don't have any indexes.
Probably,
  you need (at least) an index on the join column on the second table, as,
  otherwise, MySQL will read the second table once for each row of the first
  table.

  This probably doesn't have anything to do with the fact that it's a left
  join. You'd probably get the same result with an inner join, since the
  tables
  appear to be about the same size.

  Some hints on reading the explain: The ALL means MySQL is going to
  read the whole table instead of using an index. The [NULL]'s mean there
  is no available index. The filesort means it's going to get the
  content of the table and sort it. (You could get rid of that with
  an index, but it's not all bad, unless your data is already stored in
  approximately the correct order.)

  I haven't tried to decipher your query. It's a big, unreadable blob,
  and I'm not willing to put in the time. People posting to this list
  would do well to reformat and indent example queries so that they are
  easy for a human to read. It would also be good to make the examples
  as short as possible. I probably saw and ignored your earlier
  question for that reason; I usually just skip messages where it
  would take too long to decipher the question--I'd assume that other
  people do the same.

  HTH

  Bill

   Date: Thu, 29 Jan 2004 08:03:25 -0800
   From: Jacque Scott  [EMAIL PROTECTED] 
   To:  [EMAIL PROTECTED] 
   Subject: More Left Join problems

   I had posted a message earlier this week about my 'Left Join' taking too
   long to run. This seems to be happening on all of my queries that have
   a 'Left Join'. Does anyone have any suggestions on why this would
   happen?
  
   Here is one query which took 45.72 sec to run:
  
   SELECT Products.NSIPartNumber, Products.Cost AS Cost, If(Bom.ProductID
   Is Not Null,x,) AS BOM, Products.lngLaborHrs AS LaborHrs,
   Products.ModelNo, Products.USPrice AS USPrice, Products.VendorPart,
   Products.Description, Products.ProductID, Null AS SumOfQty,
   Products.Obsolete FROM Products LEFT JOIN BOM ON Products.ProductID =
   BOM.ProductID GROUP BY Products.NSIPartNumber, Products.Cost,
   If(Bom.ProductID Is Not Null,x,), Products.lngLaborHrs,
   Products.ModelNo, Products.USPrice, Products.VendorPart,
   Products.Description, Products.ProductID, Products.Obsolete Having
   ((NSIPartNumber Like %02-001%) AND (Obsolete-1)) ORDER BY
   NSIPartNumber;
  
  
   Here is the results when I use Explain. I don't really know how to
   read this.
  

++-+++---++-
  ++++
   | id | select_type | table | type | possible_keys | key

Re: More Left Join problems

[Bill Easton]

Is this the way to set Indexes?  It sound like a good first approximation.  To go 
beyond that, you need to think a bit about what MySQL will do to satisfy the query.  
Access, and any other database manager, would do similar things--just that the 
tradeoffs chosen may be different.  For example, if you were going to run this query 
one once a month, you might live with the 45 seconds.  On the other hand, for a query 
you are going to run a lot, you might want to include extra fields in the index so 
that MySQL can just read the index instead of reading the actual records.  Remember 
that it costs something to maintain the index, too. (Time to look for a book on SQL 
that talks about such things...)
  - Original Message - 
  From: Jacque Scott 
  To: [EMAIL PROTECTED] 
  Cc: [EMAIL PROTECTED] 
  Sent: Thursday, January 29, 2004 6:28 PM
  Subject: Re: More Left Join problems


  Well setting indexes of both tables helped.  The query now runs at 0.38 seconds.  
What a difference.  

  Is this the way to set Indexes?  Set a 'Unique' index for the PK of each table and 
just an 'Index' for the foreign keys in each table.

  Bill,

  You have been a life saver.  Thank you so much.  I have used Access the last few 
years and it is a different way of thinking.

  Jacque

   Bill Easton [EMAIL PROTECTED] 1/29/2004 1:13:00 PM 
  You need an index on BOM.ProductID

  try:
  alter table BOM add index (ProductID);
  then run your query again

  Some additional notes on your query:

  (1) You have an expression involving BOM.ProductID in your field list.
  Since that column is not in the group by clause, you'll get a ProductID from
  some record in the group--no way to predict which one. (Unless, of course,
  all rows a given NSIPartNumber has the same ProductID.) Note that ANSI SQL
  would not let you have this column in the field list and not in the group
  by.

  If you have an NSIPartNumber for which some ProductID's have a corresponding
  BOM record and some don't, you'll get a x or not unpredictably, at MySQL's
  whim.

  If you want to know if there is ANY ProductID for an NSIPartNumber without a
  corresponding record, you could use an expression involving something like
  MAX(BOM.ProductID IS NULL), which will be 1 if there is one and 0 if there
  isn't.

  (2) You probably want the stuff in your HAVING clause to be in a WHERE
  clause instead. HAVING means that the entire result set is generated and
  then filtered again to remove some records--although MySQL may optimize it
  so it doesn't really do that. (Just change HAVING to WHERE and move it
  before the GROUP BY.)

  It's good practice to use the where clause unless you really need having.
  You might really need having if you are selecting from the result based on
  group functions such as max() or count()--then, the values you are testing
  aren't available for the where clause to use.
  - Original Message - 
  From: Jacque Scott
  To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] 
  Sent: Thursday, January 29, 2004 2:18 PM
  Subject: Re: More Left Join problems


  Thanks for your time. I didn't think of formatting the query. Here is
  the query in a more readable format. I have also taken out most of the
  columns in the SELECT. The query still takes 44 seconds.

  SELECT Products.NSIPartNumber, If(Bom.ProductID Is Not Null,x,) AS
  BOM, Products.Obsolete
  FROM Products
  LEFT JOIN BOM ON Products.ProductID = BOM.ProductID
  GROUP BY Products.NSIPartNumber, Products.Obsolete
  HAVING ((NSIPartNumber Like %02-001%) AND (Obsolete-1));


  Hopefully this will be easier to decipher.


   Bill Easton  [EMAIL PROTECTED]  1/29/2004 10:34:21 AM 
  Jacque,

  Based on your explain, I'd guess that you don't have any indexes.
  Probably,
  you need (at least) an index on the join column on the second table, as,
  otherwise, MySQL will read the second table once for each row of the first
  table.

  This probably doesn't have anything to do with the fact that it's a left
  join. You'd probably get the same result with an inner join, since the
  tables
  appear to be about the same size.

  Some hints on reading the explain: The ALL means MySQL is going to
  read the whole table instead of using an index. The [NULL]'s mean there
  is no available index. The filesort means it's going to get the
  content of the table and sort it. (You could get rid of that with
  an index, but it's not all bad, unless your data is already stored in
  approximately the correct order.)

  I haven't tried to decipher your query. It's a big, unreadable blob,
  and I'm not willing to put in the time. People posting to this list
  would do well to reformat and indent example queries so that they are
  easy for a human to read. It would also be good to make the examples
  as short as possible. I probably saw and ignored your earlier
  question for that reason; I usually just skip messages where it
  would take too long to decipher the question--I'd assume that other
  people do

UNION equivilent required for 3.23.37

[Bill Easton]

Andy,

Does this scratch the itch?

  select col1, col2, date
  from root_table
left join table_one on root_table.table_one_id = table_one.table_one_id
left join table_two on root_table.table_two_id = table_two.table_two_id
  where table_one.table_one_id is not null
and table_one.table_one_id is not null
  order by date;

You get what your requested, but the col1 and col2 are in different result
columns.
If you know only one of them is non-null, you can use an IF() operator to
get
the result in one column.

Otherwise, you can't do it by joining those 3 tables.  (For example, if each
of the 3 tables contained exactly one row, there would be no join with more
than one row.)

If you really want to, I think the following will work, using a helper
table,
which you could create once and keep around.  It sure is ugly--union would
be
a lot nicer.  Basically, the cross join on helper and root_table makes two
copies of root_table; you use these to form the two parts of your union

create temporary table helper (int which) type=heap;
insert into helper values (1), (2);

select if(which=1,col1,col2)
  from helper, root_table
left join table_one on root_table.table_one_id = table_one.table_one_id
left join table_two on root_table.table_two_id = table_two.table_two_id
  where which=1 and col1 is not null
 or which=2 and col2 is not null
  order by date, which;


From: Andy Hall [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
 Subject: UNION equivilent required for 3.23.37
 Date: Wed, 28 Jan 2004 16:02:54 -

 Hi,

 I have looked for answers on the net but havent managed to apply the
 suggestions to my example; I would appreciate any help!

 I have the following set up:

 root_table (root_table_id, table_one_id, table_two_id, date)

 table_one (table_one_id, col1)

 table_two (table_two_id, col2)

 I want to use one query to join root_table with both the other tables,
 getting col1 out if root_table.table_one_id is not NULL and col2 out
if
 root_table.table_two_id is not NULL. I need to then ORDER BY
root_table.date

 With a union, I would have:

 (SELECT root_table.col1, date
 FROM root_table INNER JOIN table_one
 ON root_table.table_one_id = table_one.table_one_id)
 UNION
 (SELECT root_table.col2, date
 FROM root_table INNER JOIN table_two
 ON root_table.table_two_id = table_two.table_two_id)
 ORDER BY date

 But I cant do UNION's in MySQL, so how can I do this?

 Any help appreciated,

 Andy Hall.


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

JOIN 10 times quicker than LEFT JOIN on big tables and simple queries?

[Bill Easton]

Benjamin,

When MySQL does a join, it appears that it considers one table as the
primary
table and one table as the dependent table.  It then selects rows from the
primary table and then, for each selected row, it fetches the corresponding
rows from the dependent table.

For an inner join, MySQL can pick either of the two tables as the primary
table.
So, it picks the larger table, sorts it into the order needed for the GROUP
BY, then
fetches rows from the smaller table.  In your case, the smaller table fits
nicely
in main memory, so the query is reasonably fast.

For a left join, MySQL needs to use the first table as the primary table.
In your
case, the smaller table is the primary table, and you are using all of its
rows.
Since the larger table does not fit in main memory, MySQL reads randomly
from
it.  Since there are many records in the child table for each parent row,
and since
they are spread around, MySQL is reading a large fraction of the child table
for
each of the 67 parent rows for which there are children.  That will be slow.

One could imagine the query optimizer sorting the large table for the left
join, as
it does for the inner join.  I seem to recall that DB2, for example,
sometimes does
this.  However, a query optimizer is, by nature, a package of compromises,
and
MySQL doesn't do it that way.

So what can you do?  Here are some thoughts:

(1)  Give MySQL enough main memory to hold all of the child table.

-- This is clearly easiest, if you are able to do it, as it requires no
database or SQL changes.  However, it may not be possible.

(2)  Index the child table on (p_id, c_id).  If you are selecting more
columns
from child in your actual query, include them, too.

-- MySQL should satisfy the query using the index, rather than the data.
The
index is in order by p_id, so it will only need to be read once.

-- However, you need to maintain this index.

(3)  Use a union to do the left join.

  SELECT PARENT.p_id, 0
FROM parent LEFT JOIN child ON (parent.p_id = child.p_id)
WHERE child.p_id is null
  UNION ALL
  SELECT parent.p_id, COUNT(child.c_id)
FROM parent JOIN child ON (parent.p_id = child.p_id)
GROUP BY parent.p_id;

-- The first SELECT should be satisfied using only the index on child.p_id,
so it should be fast.  It will give you the parents with no children.

-- However, it does require rewriting your SQL.  Also, if you want the
result ordered by parent.p_id, you get to do a second ORDER BY on
the result of the UNION.

(4)  Copy the child rows to a temporary table ordered by child.p_id, then
do the left-join query.

(5)  Arrange to maintain the child table so that the rows are approximately
ordered by child.p_id.

-- This takes some work, but it might speed up other queries, if you
frequently
need to select all of the children for a particular parent.


HTH

Bill

 Date: Thu, 22 Jan 2004 20:09:42 +0100
 From: Benjamin PERNOT
 To: [EMAIL PROTECTED]
 Subject: JOIN 10 times quicker than LEFT JOIN on big tables and simple
queries?

 Here is my problem:
 I have 2 tables, a parent table and a child table. The parent table has
got 113
 rows, the child table has got 3 000 000 rows.

 parent:
 ---
 | p_id  |   name  |
 ---
 |  1| A   |
 |  2| B   |
 |  ...  |...  |
 |  112  | C   |
 |  113  | D   |
 ---

 child:
 --
 | c_id  |  p_id  |
 --
 |  1|1   |
 |  2|56  |
 |  ...  |   ...  |
 |299|2   |
 |300|56  |
 --

 I want to get a list of all the parents (even the parents without child)
with
 the number of children they've got. I use a LEFT JOIN in order to retrieve
all
 the parents without exception :

 SELECT parent.p_id, COUNT(child.c_id) FROM parent LEFT JOIN child ON
(parent.
 p_id = child.p_id) GROUP BY parent.p_id;

 This query takes 140 seconds to be executed and I got 70 results.
 Now if I use a basic JOIN like that:

 SELECT parent.p_id, COUNT(child.c_id) FROM parent JOIN child ON
(parent.p_id =
 child.p_id) GROUP BY parent.p_id;

 The query takes now 13 seconds to finish!! But now I got only 67 results
because
 the basic JOIN does not include the parents without children.

 What I don't understand is why the JOIN is far much quicker than the LEFT
JOIN
 whereas the only difference is that the LEFT JOIN includes the parents
without
 children? Any explanations?

 Here are the EXPLAIN for the 2 cases :
 LEFT JOIN case :
 --
-
 table  type   possible_keys key  key_len ref rows  Extra
 parent index  NULL  PRIMARY  4   NULL113   Using index
 child  refp_id  p_id 5   parent.p_id 40694
 --
-

 JOIN case:
 --
-
 table  type   possible_keys  key 

Re: Recreating InnoDB tables -WITHOUT- .frm

[Bill Easton]

Matthew,

Someone asked this question last year.  It turns out that there's only a
one-character difference between the InnoDB and MyISAM .frm files.

See the posting below from last May for a way to recover the InnoDB table
structure, given an InnoDB .frm file but no data files, basically by
patching the InnoDB .frm file to look like a MyISAM .frm file.  I
understand that it worked pretty well.  Good luck.

Adam,

This probably doesn't do anything for you.  Sorry.

Regards,

Bill

 From: Heikki Tuuri [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Re: Recreating InnoDB tables -WITHOUT- .frm
 Date: Tue, 13 Jan 2004 22:02:36 +0200

 Matthew,

 http://www.innodb.com/ibman.php#InnoDB_Monitor

 
 Starting from 3.23.44, there is innodb_table_monitor with which you can
 print the contents of the internal data dictionary of InnoDB.
 
 The output format is not beautiful, and you have to manually reconstruct
the
 MySQL CREATE TABLE statements from it.

 Adam,

 you can try creating a dummy InnoDB table with enough PRIMARY KEY columns.
 Then replace its .frm file with an old one you have, and try to print SHOW
 CREATE TABLE. I do not know if mysqld will crash or assert. This question
 was discussed on this mailing list some 2 years ago.

 Best regards,

 Heikki Tuuri
 Innobase Oy
 http://www.innodb.com
 Foreign keys, transactions, and row level locking for MySQL
 InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM
 tables

 Order MySQL technical support from https://order.mysql.com/

 ...
 List:MySQL General Discussion« Previous MessageNext Message »
 From:Matthew ScottDate:January 13 2004 5:33pm
 Subject:Recreating InnoDB tables -WITHOUT- .frm



 To all the InnoDB gurus out there:

 I have a similar problem to this person's predicament, except my situation
 is that I have all the innodb data and log files, but have absolutely
 no .FRM files.

 Are there any general tools for data recovery from InnoDB databases?  Any
 companies that can do this for a fee?  Anything???   :)

 Thanks.crossing my fingers that myself and Adam can find resolutions
to
 our respective situations!


 [EMAIL PROTECTED] wrote:

  I'm cleaning up a user-error where the innodb data files were deleted
  without a useful backup. I need to reconstruct the tables and still have
  the frm files. Is this possible? A significant amount of time was put
into
  these tables' structures and I hate to lose that effort...
 
  Yes the users are kicking themselves about the backup...
 
  MTIA
  Adam
 


 -- 
 Matthew Scott [EMAIL PROTECTED]

 Posting from 2003-05-31 

Mark,

Here's a brute force and ignorance approach.  Disclaimer:  It has worked
once,
and may work again some day.  In particular, I haven't looked at the MySQL
internals, and I've only tried it on a very small table.

You have foo.frm, which used to be the .frm file for an InnoDB table.

I note that the (only) difference between .frm files for MyISAM and InnoDB
is that
the fourth byte of the file is hex 09 for MyISAM and hex 0C for InnoDB.
(This,
from comparing .frm files for a very small and simple database.)

0.  Make sure you have foo.frm saved somewhere other than your MySQL data
directory.

1.  Create a new MyISAM table foo; it doesn't matter what the layout is.
For example, create table foo (n int);

2.  Copy your foo.frm over the one created in step 1.

3.  Change the fourth byte of foo.frm to hex 09 instead of hex 0C.

4.  From the MySQL client, say show create table foo;

Good luck.  HTH.

Bill


Date: Thu, 29 May 2003 12:47:02 -0700
Subject: RE: Recovering table structures from .frm files?
From: Mark Morley [EMAIL PROTECTED]
To: Mark Morley [EMAIL PROTECTED], [EMAIL PROTECTED]

 I have a couple of .frm files with no corresponding data or index
 files.  Is it possible to recover the table structure (field names,
 types, sizes) from these files?

More info: these appear to have been created under MySQL 4.0.x and they
were
originally InnoDB files.  I can see a list of field names by running
strings
on each .frm file, but I'd really like to get the colum types and sizes as
well.

Is the file format documented anywhere?

Mark



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

transactions and create table (was Questions about MySQL implementation)

[Bill Easton]

What is the official word on doing a CREATE TABLE inside a transaction?  Can
I do one without
causing the transaction to commit?

By experiment, it appears that 4.0.14 allows this, although, even if the
CREATE TABLE is for an InnoDB
table, a ROLLBACK doesn't remove the created table.  It appears that CREATE
TABLE caused
a commit of the current transaction in 3.23.49.

I'd like to create a temporary table during a transaction--mostly to be able
to emulate things like subqueries
and views that will come in some future production version of MySQL.  I
don't have a problem with
the table creation not being rolled back.

I tried to RTFM.  I did find a note on how transactions are treated for
CREATE TABLE SELECT..., but I wasn't
able to find a clear statement that CREATE TABLE will no longer force a
commit.


From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Questions about MySQL implementation
Date: Thu, 18 Dec 2003 01:18:39 +0200

Chris,

- Original Message - 
From: Chris Nolan [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, December 13, 2003 7:24 AM
Subject: Questions about MySQL implementation

[snip]

 2. I've been told on good authority (by persons on this fine list) that
 Sybase and PostgreSQL (and, from personal experience, SQLBase) support
 ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE
 etc. From what I can gather, neither BDB nor InnoDB do this.

 Does anyone know what sort of technical challenges making the above
 statements undoable involve over and above INSERT, DELETE and UPDATE
 statements? Would this functionality be something that MySQL AB /
 Innobase Oy would be interested in developing should it be sponsored?

Not very difficult: we could keep the 'old' table until the transaction
commit. In a rollback we would fall back to the old table. But the demand
for such a feature is so low that most databases do not have a rollback of
DDL statements.

[snip]
 Chris

Best regards,

Heikki Tuuri
Innobase Oy
[snip]


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

How to include count(*) in selection criteria

[Bill Easton]

use having N  10

The having clause takes the results of the query AFTER all of the rows have
been read and aggregated by the group by clause and further reduces the set
of rows that gets returned.

- original message -

Date: Wed, 17 Dec 2003 12:52:08 -0500 (EST)
From: Gaspar Bakos [EMAIL PROTECTED]
To: mysqllist [EMAIL PROTECTED]
Subject: How to include count(*) in selection criteria

Dear all,

I am trying the following mysql statement (probably it will be obvious
that I am a newcomer):

select IMdirectory, IMljd, count(*) as N from Images where IMstid
= 5, N  10 group by IMdirectory order by IMljd;
ERROR 1054: Unknown column 'N' in 'where clause'

My question is: how could I select only those grouped entries that have
a count number greater than e.g. 10?

Cheers
Gaspar


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

ORDER BY DESC order different when using LIMIT

[Bill Easton]

This is not an error.  The results returned by MySQL are correct.

Since each value of CreatedDate in your example occurs twice, ORDER BY
CreatedDate DESC only says that the ImgId's 2 and 3 should precede ImgId's
1 and 4.  The database is free to return ImgId's 2 and 3 in either order and
to return 1 and 4 in either order.  There's no requirement that it return
them in the same order each time, even if you repeat the same query.

If you really want them returned in a consistent order, you have to sort on
a key, for example ORDER BY CreatedDate DESC, ImgId.

From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject:  ORDER BY DESC order different when using LIMIT
Date: Sun, 16 Nov 2003 19:04:31 +0100

Description:

When using ORDER BY column DESC the lines are shown in one order but when
I use LIMIT 0,1 the second line insted of the first line are shown.
And when I use LIMIT 2,1 the same line as if use LIMIT 3,1 are shown.
The table has 4 rows.
How-To-Repeat:

mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC;
+---+-+
| ImgId | CreatedDate |
+---+-+
| 2 | 2002-11-22  |
| 3 | 2002-11-22  |
| 1 | 2002-11-21  |
| 4 | 2002-11-21  |
+---+-+
4 rows in set (0.00 sec)

mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT
0,1;
+---+-+
| ImgId | CreatedDate |
+---+-+
| 3 | 2002-11-22  |
+---+-+
1 row in set (0.00 sec)

mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT
1,1;
+---+-+
| ImgId | CreatedDate |
+---+-+
| 2 | 2002-11-22  |
+---+-+
1 row in set (0.00 sec)

mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT
2,1;
+---+-+
| ImgId | CreatedDate |
+---+-+
| 4 | 2002-11-21  |
+---+-+
1 row in set (0.00 sec)

mysql SELECT ImgId,CreatedDate FROM dogge ORDER BY CreatedDate DESC LIMIT
3,1;
+---+-+
| ImgId | CreatedDate |
+---+-+
| 4 | 2002-11-21  |
+---+-+
1 row in set (0.00 sec)
[...]


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

Sql - Problem with Left Join

[Bill Easton]

You are taking the INNER JOIN with caddrescontactperson.

Presumably, you need something like this:

  Select A.DepartmentName,A.Address,P.Postcode,P.cityname,
  CP.firstname
  from caddress A,cpostinfo P

 left Join   CContactPerson CP
   on CP.ID =1001
 left join   caddresscontactperson CACP
   on CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID
 left join   ccontactinfo CCI
   on  CACP.ID=CCI.AddressContactPersonID
   and CCI.AddressID=-1  and CCI.ContactInfoTypeID=1

  where A.ID=10 and A.PostInfoID=P.ID

 Date: Wed, 12 Nov 2003 23:20:10 +0100 (CET)
 Subject: Sql - Problem with Left Join
 From: Kim G. Pedersen [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]

 Hello

 the little Query1 works until I add the left join :

 Query 1 )
   Select A.DepartmentName,A.Address,P.Postcode,P.cityname
   from caddress A,cpostinfo P
   there A.ID=10 and A.PostInfoID=P.ID


 Query 2)
   Select A.DepartmentName,A.Address,P.Postcode,P.cityname,
   CP.firstname

  left Join   CContactPerson CP , caddresscontactperson CACP ,ccontactinfo
  CCI on  CP.ID =1001  and CACP.ContactpersonID=CP.ID and
 CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and
 CCI.AddressID=-1  and CCI.ContactInfoTypeID=1

   from caddress A,cpostinfo P
   there A.ID=10 and A.PostInfoID=P.ID


 Query1 return :
  -  company , streeet ,2000,copenhagen


 Query2 return :
 - Empty

 I know that the jeft join will give zero result ,
 since the table caddresscontactperson are empty.

 But I do not Understand Why I not get
  -  company , streeet ,2000,copenhagen , NULL

 Normally a left join should not effect ur result.

 Hope Someone can give me clue

 using Mysql 4.1.0
 regards
 Kim G. Pedersen
 macaos/elprint Development




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

InnoDB lock in share mode on union

[Bill Easton]

How do I lock rows in a union query so that I know they won't change during
the rest of my transaction?

I want to do the following query, using LOCK IN SHARE MODE:
(select id from table1 where id  1)
union
(select id from table2 where id  1);

If I try:
(select id from table1 where id  1 LOCK IN SHARE MODE)
union
(select id from table2 where id  1);
it doesn't appear to do the locking.  Another process can delete a record
from table1 which was contained
in the result.  It appears that I see a consistent snapshot, and the record
is gone after I commit.
(See below.)  I get the same result if I use FOR UPDATE.

MySQL doesn't allow me to put LOCK IN SHARE MODE after the second select or
after the whole union.

I'm using MySQL 4.0.13-nt-log.

--  Example follows

In the following, table1 and table2 have the following structure:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB

mysql begin;
Query OK, 0 rows affected (0.00 sec)

mysql (select id from table1 where id  1 LOCK IN SHARE MODE) union (select
id from table2 where id  1);
++
| id |
++
|  3 |
|  2 |
++
2 rows in set (0.00 sec)

*** another process does: delete from table1
where id=3;

mysql (select id from table1 where id  1 lock in share mode) union (select
id from table2 where id  1);
++
| id |
++
|  3 |
|  2 |
++
2 rows in set (0.01 sec)

mysql commit;
Query OK, 0 rows affected (0.00 sec)

mysql (select id from table1 where id  1 lock in share mode) union (select
id from table2 where id  1);
++
| id |
++
|  2 |
++
1 row in set (0.00 sec)


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

More LEFT JOIN Headaches

[Bill Easton]

Dan,

You don't need a LEFT JOIN here.  Left join lets you keep all of the rows in
one table in a
join, even when there are no matching rows in the second table.

You do have to use the group_members table twice, once to find all the
groups to which
Jim belongs, and again to find all of the members of those groups.  Here's a
select
statement that does the trick.

SELECT G.NAME, M.name
FROM group_members A, group_members B, groups G, members M
where A.member_id = 1
  and A.group_id = B.group_id
  and G.id = B.group_id
  and M.id = B.member_id
ORDER BY groups.name;


However, some comments on left joins...

Your proposed example has no WHERE clause, so ALL rows of group_members will
participate in the
join.  The group_members.member_id in the ON clause only limits the rows
of groups which
will be used in the join, rather than perhaps contributing nulls.  (That's
why  you see a row for White.)

A brief description of left joins:

SELECT *
FROM table1 LEFT JOIN table2 ON join_condition;

returns:
  (1) all rows returned by:
 SELECT * from table1, table2 where  join_condition;
  (2) for each row in table1 that does NOT participate in (1), a row
 from table1 with the table2 columns filled in with nulls

Then, you can add a WHERE clause to further restrict the returned rows; in
particular,
you can use table2.column IS NULL to get rows from table1 which do NOT match
table2.

Note that the join_condition in the ON clause does not restrict what rows
from
table1 participate in the result--without a WHERE, you get at least one row
in the result for each
row of table1.  The ON clause DOES affect what rows of table2 get joined to
rows
of table1.

HTH,

Bill



 Date: Wed, 29 Oct 2003 11:57:54 -0600
 To: [EMAIL PROTECTED]
 From: Dan Hansen [EMAIL PROTECTED]
 Subject: More LEFT JOIN Headaches

 Please help -- by brain is fried...

 I have three tables: groups, members, and a link table called
group_members.

 There is a record in group_members for each member that belongs to a given
 group.

 A member may belong to several group.

 I want to get a results set that shows all members where a given member,
 call him Jim, also belongs.

 Thus if Jim is a member of groups 1 and 3, Mary is a member of 3, and Bob
 belongs to 2 and 3, I want a results like the one below.  I know how to
get
 to this using an intermediate temporary table, but I'd like to vaid that
if
 there's a way.  The queries I have tried either give me all members in all
 groups, or only records where Jim is a member.  My closest query follows,
 then a sketch of my tables with a results set at the end.  Can someone
 please tell me where I an blowing it?

 Thanks -

 Dan Hansen


 SELECT groups.name AS group_name, members.username AS username
 FROM group_members
 LEFT JOIN groups ON groups.id = group_members.group_id
 AND group_members.member_id = 1 (This is Jim, it will be replaced by a
 variable in PHP code)
 LEFT JOIN members ON members.id = group_members.member_id
 ORDER BY groups.name



 ===
 members
 ---
 id   name
 ---
 1Jim
 2Mary
 3Bob

 ===
 groups
 ---
 id   name
 ---
 1Red
 2White
 3Blue

 ===
 group_members
 ---
 id   member_id  group_id
 ---
 11  1
 21  3
 32  3
 43  2
 53  3

 
 DESIRED RESULT SET
 
 group_name   username
 
 Red  Jim
 Blue Jim
 Blue Mary
 Blue Bob

 
 DESIRED RESULT SET
 
 group_name   username
 
 Red  Jim
 Blue Jim
 Blue Mary
 Blue Bob (SHOULD NOT GET THIS ONE)
 WhiteBob




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

Re: How to write this query

[Bill Easton]

Sean,

Slight rewriting of Kevin's query--I assume you want to do the joins on
A_ID.

SELECT A_data, B_data, C_data
FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID
WHERE A.A_ID = 4;

This should work.  For your example, the first left join gives a table with
A.* and nulls for B.*.  Then, the second left join gives you C.* for that
A_ID; it doesn't matter that the B.* part contains nulls.

Bill


 From: sean peters [EMAIL PROTECTED]
 To: Kevin Fries [EMAIL PROTECTED], [EMAIL PROTECTED]
 Subject: Re: How to write this query
 Date: Wed, 1 Oct 2003 16:22:46 -0500

 Unfortunately that wont always work either.

 For instance, assume that there is an A record with A_ID = 4
 And that there is a C record where A_ID = 4,
 but NO B record where A_ID = 4

 So, executing the query:
  SELECT A_data, B_data, C_data
  FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
  WHERE A.A_ID = 4;

 When A left joins B, there is no real B record, so any B columns are
populated
 with null, as per left join.
 Then, table B is left joined to C on A_ID, which is null, and no C record
will
 properly match the B.A_ID = NULL, so the C record is filled with nulls.

 If we were to join A to C then to B, a similar problem would occur if
there
 was a cooresponding B record, but no C record.

 Thanks anyway.


 On Wednesday 01 October 2003 14:25, Kevin Fries wrote:
  You're on the right track with LEFT JOIN.  Just continue the thought...
  Try:
  SELECT A_data, B_data, C_data
  FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID
  WHERE A.A_ID = 4;
 
   -Original Message-
   From: sean peters [mailto:[EMAIL PROTECTED]
   Sent: Wednesday, October 01, 2003 12:07 PM
   To: [EMAIL PROTECTED]
   Subject: How to write this query
  
  
   I've run into a situation where i dont know how to best write
   a query. For a
   base example, consider these 3 tables:
  
   CREATE TABLE A (
A_ID INT NOT NULL PRIMARY KEY,
A_data text
   );
  
   CREATE TABLE B (
B_ID INT NOT NULL PRIMARY KEY,
A_ID INT NOT NULL,
B_data text
   );
  
   CREATE TABLE C (
C_ID INT NOT NULL PRIMARY KEY,
A_ID INT NOT NULL,
C_data text
   );
  
   So ive been running a query like:
   SELECT A_data, B_data, C_data FROM A, B, C
   WHERE A.A_ID = B.B_ID
   AND A.A_ID = C.C_ID
   AND A.A_ID = 4;
  
   What i really want is to get the A_data from A, and if there
   are cooresponding
   records in B and/or C, get B_data and/or C_data, respectively.
  
   This works fine if there are cooresponding records in tables
   B and C for each
   record in A, but if not, this returns nothing.
  
   So, short of querying each table, i cant come up with a good
   solution to my
   problem.
  
   If there were only 2 tables, a LEFT JOIN would work fine, but
   both B and C
   want to be left joined to A, which i dont know how to do.
  
   thanks
   sean peters
   [EMAIL PROTECTED]
  
  
   ---
   mysql, query
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
   http://lists.mysql.com/mysql? [EMAIL PROTECTED]


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

Strange behavior -- user variables in 4.0.14b

[Bill Easton]

I get the following strange behavior with a user variable.  @T has the value
0 to start; after adding 1 to @T a few times, it ends up with a clearly
incorrect value.  I'd expect it to have a value of 280 after the second
select.

--
SELECT @T
--

+--+
| @T   |
+--+
| 0|
+--+
1 row in set (0.00 sec)

--
UPDATE contown_svr SET contown_id=(@T:[EMAIL PROTECTED])
--

Query OK, 280 rows affected (0.05 sec)
Rows matched: 280  Changed: 280  Warnings: 280

--
SELECT @T
--

+--+
| @T   |
+--+
| 1.8669204411349e+021 |
+--+
1 row in set (0.00 sec)

---

More data:

I'm using Windows 2000 with MySQL 4.0.14b, and connected through localhost.
It appears to work correctly on MySQL 4.0.13 and MySQL 3.23.57.
I also tried it on Linux with MySQL 4.0.13, and it worked correctly.
It continues to fail with the 3.23.57 client and the 4.0.14b server.

I get various values for @T, sometimes with a negative exponent.
Sometimes it gives the correct value once, then twice the correct value on
the second try, etc., despite @T being reset to zero.
Sometimes, when I select the values in contown_svr, contown_id (which is an
int) prints as something like 561.1.

This happens when I have the mysql client read a file.  When I cut and paste
the content of the file to console, it appears to give the correct result.

Any help would be appreciated.  It sure sounds to me like a bug in thread
synchronization within the server.

Here's the smallest program I've gotten to fail.  It still fails (gives
wrong value to @T) even if the select returns 0 rows, but it doesn't fail if
I remove the insert...select.  I'll try to cut it down some more and post an
example that's not missing the data--but it may take a while to get to it.

-

select @t:=0;

drop table if exists contown_svr;
create table contown_svr select * from contown where 0;

insert  into contown_svr
select -999,pw.owner,pc.contact_id,0
from fundown pw
   inner join fundcont pc using (funding_id)
   left join contown cw on cw.contact_id = pc.contact_id
   left join grouptree on pw.owner=subject and cw.owner=target
where subject is null
   and pc.contact_id  0;

select @t;

update contown_svr set contown_id=(@t:=(@t+1));

select @t;

exit



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

Re: Left Join multiple tables

[Bill Easton]

Petre,

What I think you want is,
  For each main, with at least one fof,pub... in the date range,
 a row with the id and for fof,pub a value within range or null

What you are probably getting is,
  For each main, with at least one fof,pub... in the date range,
 several rows, where fof,pub,... are null of have some value,
   not necessarily in range, but at least one fof,pub,... is
   in range.

I assume that's what you mean by duplication in the result

Consider what left join means:
  select T1 left join T2 on condition
returns the following rows:
  (1) select T1, T2 where condition
  (2) a row for each T1 which was not matched in (1), with nulls for T2

Any where clauses are applied after the inner join, and serve to
further restrict the rows returned.

What you probably want is:


select * from main
  left join fof on main.id = fof.id
and (fof.information_sent  '$date1' and fof.information_sent 
'$date2')
  left join pub on main.id = pub.id
and (pub.information_sent  '$date1' and pub.information_sent 
'$date2')
  ...
where fof.id is not null
   OR pub.id is not null
   ...

The resulting rows will have only matching dates (or null), which
I suspect is what you want.

Notes:
1. If there are 2 matching dates for fof and 2 matching dates for pub,
   you will still get all 4 combinations.
2. The where clause gets rid of the main.id's where none of the other
   tables has a matching date; you may or may not want this.

HTH,

Bill

 Subject: Left Join multiple tables
 From: Petre Agenbag [EMAIL PROTECTED]
 Date: 29 Jul 2003 16:17:05 +0200

 Hi List
 Me again.

 I'm trying to return from multiple tables, the records that have field
 information_sent between two dates.
 The tables are all related by means of the id of the entry in the main
 table, ie..

 main
 id entity_name ...

 fof
 id_fof id information_sent ...

 pub
 id_pub id information_sent ...

 etc.

 So, I tried the following join

 select * from main
   left join fof on main.id = fof.id
   left join pub on main.id = pub.id
   left join gov on main.id = gov.id
   left join med on main.id = med.id
   left join ngo on main.id = ngo.id
   left join own on main.id = own.id
   left join sup on main.id = sup.id
   left join tra on main.id = tra.id
   where (
   (fof.information_sent  '$date1' and fof.information_sent  '$date2')
   OR
   (pub.information_sent  '$date1' and pub.information_sent  '$date2')
   OR
   (gov.information_sent  '$date1' and gov.information_sent  '$date2')
   OR
   (med.information_sent  '$date1' and med.information_sent  '$date2')
   OR
   (ngo.information_sent  '$date1' and ngo.information_sent  '$date2')
   OR
   (own.information_sent  '$date1' and own.information_sent  '$date2')
   OR
   (sup.information_sent  '$date1' and sup.information_sent  '$date2')
   OR
   (tra.information_sent  '$date1' and tra.information_sent  '$date2')
   )
   order by entity_name


 BUT, although it seems to be joining the tables correctly AND only
 returning the ones with the correct date criteria, it does NOT return
 the id or the information_sent fields correctly ( due to duplication
 in the result )

 What am I doing wrong?

 Thanks


 mysql, query



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

Re: replicating FLUSH LOGS

[Bill Easton]

 From: Egor Egorov [EMAIL PROTECTED]
 Date: Thu, 3 Jul 2003 10:51:08 + (UTC)
 Subject: Re: replicating FLUSH LOGS

 Bill Easton [EMAIL PROTECTED] wrote:
  Under MySQL 3.23, FLUSH LOGS was replicated.  Under 4.0.13, this appears
to
  no longer be the case.
 
  Was this intentional?  Could it be put back the way it was?
 
  We do backups by, at a time of low usage, (1) FLUSH LOGS on the master,
(2)
  Dump the master database, (3) repeat 1 and 2 until there were no updates
  during the dump.  (We've only had to repeat once.)  It was useful to
know
  that both the master and the slave had a binlog starting from the time
of
  the dump.

 FLUSH LOGS has never been replicated.

You are correct, in that FLUSH LOGS was never written to the binlog.

However, it used to be that when a FLUSH LOGS command was given on
the master, the slave also started a new binlog.  (At least, assuming
the slave had binlog and log-slave-updates enabled.)

Example follows with master 3.23.49 on Linux and slave 4.0.1 on Windows
2000.  I'm pretty sure it happened with a 3.23.49 slave as well.

ON SLAVE:
C:\dir \mysql\data
...
07/03/2003  10:21p 201 testslave.001
07/03/2003  10:20p  16 testslave.index
...

ON MASTER:
mysql flush logs;
Query OK, 0 rows affected (0.00 sec)

ON SLAVE:
C:\dir \mysql\data
...
07/03/2003  10:21p 260 testslave.001
07/03/2003  10:21p  55 testslave.002
07/03/2003  10:21p  32 testslave.index
...


I note that on 3.23, after FLUSH LOGS, the end of the binary
log looks like this:
# at 138
#030704  2:21:46 server id  1   Rotate to rcapdcs.142
# at 162
#030704  2:21:46 server id  1   Stop

On 4.0.13, the Stop line is not there.  Perhaps that's
what triggers the log change on the slave.



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

replicating FLUSH LOGS

[Bill Easton]

Under MySQL 3.23, FLUSH LOGS was replicated.  Under 4.0.13, this appears to
no longer be the case.

Was this intentional?  Could it be put back the way it was?

We do backups by, at a time of low usage, (1) FLUSH LOGS on the master, (2)
Dump the master database, (3) repeat 1 and 2 until there were no updates
during the dump.  (We've only had to repeat once.)  It was useful to know
that both the master and the slave had a binlog starting from the time of
the dump.



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

RE: MySQL/INNODB speed on large databases

[Bill Easton]

For the first query below--if you really run it often enough
to mess with indexes, and it really has a limit 1 or a small
limit--an index on (VoidStatus, InstNum) ought to
avoid having MySQL create a big temporary table and then sort it.

In addition, you could add to the index any of columns in the other
AND clauses, if doing so would allow a lot of records to be skipped
over during the index scan, rather than read in their entirety.

 From: Wendell Dingus [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED]
 Subject: RE: MySQL/INNODB speed on large databases
 Date: Wed, 2 Jul 2003 11:51:05 -0400

 Thanks to everyone who has helped and/or made suggestions so far. I'll
 try to provide some answers to your further queries and report back on
 some testing I've done.

 Jeremy asked for explains of some of the problem queries:
 Here is a particularly troublesome one that gets ran quite a lot:
 mysql SELECT InstNum FROM TBL_Transactions WHERE ((IndexStatus  '2' OR
 Scanned'Y') OR (MoneyStatus  '1'))  AND ((VoidStatus = 'N') AND
 (IndexType  'CP') AND (Year  '2001')) ORDER BY InstNum ASC LIMIT 1;
 +--+
 | InstNum  |
 +--+
 | 03128665 |
 +--+
 1 row in set (6.59 sec)

 mysql explain SELECT InstNum FROM TBL_Transactions WHERE ((IndexStatus
  '2' OR Scanned'Y') OR (MoneyStatus  '1'))  AND ((VoidStatus = 'N')
 AND (IndexType  'CP') AND (Year  '2001')) ORDER BY InstNum ASC LIMIT
 1;
 +--+--++
 +-+---+++
 | table| type | possible_keys  | key
 | key_len | ref   | rows   | Extra  |
 +--+--++
 +-+---+++
 | TBL_Transactions | ref  | Year,VoidStatus,IndexStatus,Year_2 |
 VoidStatus |   2 | const | 150804 | where used; Using filesort |
 +--+--++
 +-+---+++
 1 row in set (0.00 sec)

 Thanks to Joseph Bueno for suggesting the 4.x query cache:
 I took the above query and on a test server running 4.0.13 I setup a 1MB
 query cache and tried it out. It took 6 seconds first time and 0.00
 seconds on subsequent times. I'm assuming this cache is smart enough to
 re-perform the query if any data pertaining to it changes, yeah
 surely... So on often-executed queries where the data is very cachable
 this will help.

 After a few minutes of monitoring this one floats to the top of a mytop
 output screen as taking the longest to run:
 mysql explain SELECT DISTINCT LastName, FirstName, PAName FROM
 TBL_AllNames WHERE PAName LIKE 'WHITE%' AND NameType'2' ORDER BY
 LastName, FirstName;
 +--+---+-++-+--+
 ---+-+
 | table| type  | possible_keys   | key| key_len | ref  |
 rows  | Extra   |
 +--+---+-++-+--+
 ---+-+
 | TBL_AllNames | range | PAName,NameType | PAName |  81 | NULL |
 41830 | where used; Using temporary |
 +--+---+-++-+--+
 ---+-+
 1 row in set (0.00 sec)

 mysql Running the actual query returned 4000 rows and took (58.20 sec)

 Here's some details of that table:

 mysql describe TBL_AllNames;
 +---+-+--+-+-+---+
 | Field | Type| Null | Key | Default | Extra |
 +---+-+--+-+-+---+
 | InstNum   | varchar(8)  |  | PRI | |   |
 | Year  | varchar(4)  |  | PRI | |   |
 | NameType  | char(2) |  | PRI | |   |
 | NameClass | char(1) | YES  | MUL | NULL|   |
 | NameAP| char(1) | YES  | | NULL|   |
 | Ncount| int(11) |  | PRI | 0   |   |
 | LastName  | varchar(80) | YES  | MUL | NULL|   |
 | FirstName | varchar(60) | YES  | MUL | NULL|   |
 | TypeofName| varchar(20) | YES  | | NULL|   |
 | PAName| varchar(80) | YES  | MUL | NULL|   |
 | SoundKeyFirst | varchar(12) | YES  | MUL | NULL|   |
 | SoundKeyLast  | varchar(12) | YES  | MUL | NULL|   |
 | RecDate   | varchar(8)  |  | MUL | |   |
 | InstCode  | varchar(10) |  | MUL | |   |
 | IndexType | varchar(4)  |  | | |   |
 | XrefGroup | varchar(8)  |  | | |   |
 +---+-+--+-+-+---+
 16 rows in set (0.00 sec)

 mysql select count(*) from TBL_AllNames;
 +--+
 | count(*) |
 +--+
 |  6164129 |
 +--+
 1 row in set (50.17 sec)


 

Recovering table structures from .frm files?

[Bill Easton]

Mark,

Here's a brute force and ignorance approach.  Disclaimer:  It has worked
once,
and may work again some day.  In particular, I haven't looked at the MySQL
internals, and I've only tried it on a very small table.

You have foo.frm, which used to be the .frm file for an InnoDB table.

I note that the (only) difference between .frm files for MyISAM and InnoDB
is that
the fourth byte of the file is hex 09 for MyISAM and hex 0C for InnoDB.
(This,
from comparing .frm files for a very small and simple database.)

0.  Make sure you have foo.frm saved somewhere other than your MySQL data
directory.

1.  Create a new MyISAM table foo; it doesn't matter what the layout is.
For example, create table foo (n int);

2.  Copy your foo.frm over the one created in step 1.

3.  Change the fourth byte of foo.frm to hex 09 instead of hex 0C.

4.  From the MySQL client, say show create table foo;

Good luck.  HTH.

Bill


Date: Thu, 29 May 2003 12:47:02 -0700
Subject: RE: Recovering table structures from .frm files?
From: Mark Morley [EMAIL PROTECTED]
To: Mark Morley [EMAIL PROTECTED], [EMAIL PROTECTED]

 I have a couple of .frm files with no corresponding data or index
 files.  Is it possible to recover the table structure (field names,
 types, sizes) from these files?

More info: these appear to have been created under MySQL 4.0.x and they
were
originally InnoDB files.  I can see a list of field names by running
strings
on each .frm file, but I'd really like to get the colum types and sizes as
well.

Is the file format documented anywhere?

Mark



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

Re: Problem with LEFT JOIN

[Bill Easton]

Lisi,

First, limiting the clicks:  You didn't have any condition on click.date in
your suggested queries.  You need to have one.  Here's my suggestion,
somewhat modified.  Note that the limit on click.date goes in the ON clause,
while the limit on display.date goes in the WHERE clause.  Looks wierd until
you think about the meaning of left join.  The left join gives you all
displays, together with clicks for the day you want and nulls for displays
with no clicks.  The where clause then throws away displays for the wrong
days.

SELECT ...
FROM display
  LEFT JOIN click
ON display.name=click.name
   AND ... AND DAYOFMONTH(click.date) = '19'
WHERE ...
  AND DAYOFMONTH(display.date) = '19';

Now, about the counts. The above will give you a (display, click) pair for
every display and click with the same name, plus a (display, null) for the
displays without clicks.  Summing counts of the (display, click) pairs is
going to give you results that are wrong, as you found out.  Can you do it
in one query?  Probably not, until MySQL gets subqueries.

Why not use a temporary table?

CREATE TEMPORARY TABLE dcounts
  SELECT display.name, SUM(display.count) as dc
  WHERE ...
AND DAYOFMONTH(display.date) = '19'
  GROUP BY display.name;

this gives you a table of displays and counts.  Then use a LEFT JOIN to
combine it with click:

SELECT dcounts.name, dc, SUM(clicks.date IS NOT NULL)
FROM dcounts
  LEFT JOIN click
ON dcounts.name=click.name
   AND ... AND DAYOFMONTH(click.date) = '19'

Note:  If you are replicating, you may want to create the temporary tables
in a second database and use binlog-ignore-db to avoid cluttering up the
logs and to avoid having the slave get confused by having the same table
created in multiple threads on the server.  Subqueries will sure make life
simpler.

About my comment on indenting.  Yes, you cut and pasted from a generated
query.  What I asked is that, before sending the message, you insert some
new lines and some indentation.  You're asking for free help, and you're
more likely to get it if people don't have to spend time figuring out what
the question is.


- Original Message -
From: Lisi [EMAIL PROTECTED]
To: Bill Easton [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Monday, February 03, 2003 3:37 AM
Subject: Re: Problem with LEFT JOIN



 OK, I'm coming back to this problem after not touching it for a while.
 Sorry about the indentation, it's a dynamically generated query and I just
 cut and pasted into the email.

 I tried your suggestion, and it is showing the correct number of displays
 for most ads, but it is not limiting the clicks - it's displaying all
 clicks even if there were none for that day.  Plus, for one ad, it is
 showing the two clicks (on a different day) it got plus it doubled the
 number of displays it had for that day - i.e. it is multiplying the 13
 displays by the 2 clicks!  Huh?

 Is this just too complicated to do with one query? Should I perhaps use
 different queries, and store the info somehow in an array by name, and
 display it that way?

 I'm really stumped here.

 Thanks,

 -Lisi

 At 07:31 AM 1/20/03 -0500, Bill Easton wrote:
 Shalom, Lisi,
 
 (Sure would have been nice if you had indented and simplified your
 SQL statements so they could be read by a mere human instead of
 just by a computer ;-})
 
 You have
SELECT ...
FROM display
  LEFT JOIN click
ON display.name=click.name
AND ...
AND DAYOFMONTH(display.date) = '19';
 
 The result of this select consists of the following:
(1)  The result of the following inner join:
   SELECT ...
   FROM display. click
   WHERE display.name=click.name
 AND ...
 AND DAYOFMONTH(display.date) = '19';
(2)  For each row of display that did not get used in (1),
 that row together with nulls for all columns of click.
 
 In short, you get at least one row for each row of display--the ON
 clause only affects which ones that have data from click.  That's
 how LEFT JOIN works.  So, in particular, you get data for the
 whole month.
 
 You probably want something like:
SELECT ...
FROM display
  LEFT JOIN click
ON display.name=click.name
WHERE ...
  AND DAYOFMONTH(display.date) = '19';
 
 The left join will give you rows with data from both tables and rows
 from display that don't have data in click; the where clause will
 then narrow the selection to the day you want.
 
 Hope this helps
 
   Date: Sun, 19 Jan 2003 19:02:25 +0200
   To: [EMAIL PROTECTED]
   From: Lisi [EMAIL PROTECTED]
   Subject: Problem with LEFT JOIN
 
   I have a page with many ads that stores both the number of times an ad
is
   displayed and how many times it gets clicked.  These are stored in two
   different tables (since different information is stored for each) but
both
   have identical name columns. I am trying to display both # times
displayed
   and # times clicked in the same table in an admin

Re: How to write this query??

[Bill Easton]

If you have the option to change the table structure, just replace
the date and time columns with a timestamp column.

If you must keep the current structure then the following wil
work, but it will not use indexes in the search:

select ...
where concat(today,' ',heure1)
between '2002-01-01 17:00:00' and '2002-01-30 08:00:59';

To allow MySQL to use an index on today, add an extra clause:

select ...
where today between '2002-01-01' and '2002-01-30'
and concat(today,' ',heure1)
between '2002-01-01 17:00:00' and '2002-01-30 08:00:59';

The following would also work, and might allow an index scan of an
index on (today, heure1):

select ...
where today between '2002-01-01' and '2002-01-30'
and (today  '2002-01-01' or heure1 = '17:00:00')
and (today  '2002-01-30' or heure1 = '08:00:59');

 From: Inandjo Taurel [EMAIL PROTECTED]
 Subject: How to write this query??
 Date: Thu, 23 Jan 2003 18:36:40 +

 hi all,
 i have a table trans with this structure:

 today(date) | heure1(time) | amount(double) | client(varchar 30)

 Now i need to write a query that would take the start date and time, end
 date and time, and return all the records between the two.

 So for example, the query should return all records between 01/01/2002 at
 17:00 and 01/30/2002 at 08:00.
 How can i get that query in one query??

 SQL SQL



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: solution for opposite of this join / join from this subselect

[Bill Easton]

See interleaved comments below.

 Subject: solution for opposite of this join / join from this subselect
 To: [EMAIL PROTECTED]
 From: Josh L Bernardini [EMAIL PROTECTED]
 Date: Wed, 22 Jan 2003 11:23:44 -0800

 thanks to brent, bob and M wells for their contributions to this solution
 and to m especially who seems to have put in a lot of time and nailed it.

 This query returns a list of people not attending a particular event,
given
 an events table, a people table, and a many-many epeople table between
 them. You can modify the where clause to show all the people attending a
 particular event, all the events a person isn't/is attending - most of
what
 you might need in most many - many relationships.

 But I still haven't figured out the importance of left joins vs. right
 joins. can anyone explain why this statement requires a right join to
work?
 could it be rewritten to use a left join?

 mysql SELECT lastname, firstname, title, event
 - FROM people p
 - LEFT JOIN epeople ep on p.id = ep.pid
 - right join events e ON e.id = ep.eid
 - WHERE ep.pid IS NULL
 - and ep.eid is null
 - and e.id=2
 - ORDER BY e.id;

It can.  How about:

mysql SELECT lastname, firstname, title, event
- FROM events e
-   LEFT JOIN (people p
-  LEFT JOIN epeople ep on p.id = ep.pid)
-   ON e.id = ep.eid
- WHERE ep.pid IS NULL
- and ep.eid is null
- and e.id=2
- ORDER BY e.id;

This appears to work in MySQL 4.0.9; Mysql 3.23 didn't allow the
parentheses.
In fact, 4.0.9 appears to work without the parentheses, but 3.23 still
doesn't.
The point is, you want all of the people and all of the events, so you have
to have people on the left side of a left join and events on the left side
of a left join.

[...]

 now whats the functional difference between this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and e.id = 2
 ORDER BY ep.eid;

 and this

 SELECT lastname, firstname, title, event
 FROM people p
 LEFT JOIN epeople ep on p.id = ep.pid
 right join events e on ep.eid = e.id
 WHERE ep.pid IS NULL
 and ep.eid=2
 ORDER BY e.id;

 as written the difference is in the and statements but in my result set
 ep.eid == e.id == 2 so why can't you use the second statement
 interchangably with the first?

No, it's not so that ep.eid == e.id.
In the result of the join, before applying the where clause, ep.eid is null
and e.id is not null for a row corresponding to an event which has no
associated people.

 thanks for any insights,
 jb






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: join from this subselect

[Bill Easton]

How about:

  select people.id ...
  from people left join epeople
on  epeople.pid=people.id
and epeople.eid=2
  where epeople.pid is null;

The left join gives you:
  (1)  rows for people who attended event 2, with epeople columns populated
  (2)  rows for people who did not attend event 2, with nulls in epeople
columns
Then the where restricts to (2).

More generally,
  select ... from L left join R on CONDITION
gives you:
  (1)  the result of the inner join,
 select ... from L, R where CONDITION
  (2)  the rows from L that were not used in (1), with nulls for the R
columns
Then, you can apply an additional WHERE clause to that.

hth


 Subject: join from this subselect
 To: [EMAIL PROTECTED]
 From: Josh L Bernardini [EMAIL PROTECTED]
 Date: Tue, 21 Jan 2003 11:51:13 -0800

 I can't come up with the join syntax to mimic this subselect query to list
 people not attending an event (*epeople.eid == event.id):

 select people.id as pid, concat(lastname, , , firstname) as name from
 people where people.id not in
 (select epeople.pid from
 epeople
   left join people on epeople.pid=people.id
 where epeople.eid=2);

 Thought it would be:
[...]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Left join returns records it shouldn't

[Bill Easton]

The outer join part of the silly query should return
  (1)  All pairs a,b for which (a.zone = b.zone ... and b.leftside != '')
  (2)  A row for each a that is not used in (1), with null for the columns
of b
The where clause then narrows these down to elements of (2) with a.type =
'MX'

No reason there shouldn't be any--and there are some.  Your query does not
ask
for records with a.type='A' and a.type='MX', it asks for records of the left
join
with a.type='MX'.

In fact, for your real query, without the MX part, you should get a result
row
at least for each row of dns_rec for which leftside != ''.  Sounds like you
should
get a lot of them, and it should take a while.

More generally,
  select ... from L left join R on CONDITION
gives you:
  (1)  the result of the inner join,
 select ... from L, R where CONDITION
  (2)  the rows from L that were not used in (1), with nulls for the R
columns
Then, you can apply an additional WHERE clause to that.


 Date: Wed, 22 Jan 2003 12:28:48 -0600 (CST)
 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Left join returns records it shouldn't

 Description:

  The following silly query (silly because it should return no
  records since it is impossible for a.type to have two different
  values simultaneously), returns two records when it should
  return none with the enclosed test data.

 select a.*, b.* from dns_rec a left join dns_rec b
 on (a.zone = b.zone and
 a.rightside = b.rightside and
 a.type = 'A' and b.type = 'A' and
 a.leftside = '' and b.leftside != '')
 where b.zone is null  and a.type = 'MX'
 ;

 [...]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Problem with LEFT JOIN

[Bill Easton]

Shalom, Lisi,

(Sure would have been nice if you had indented and simplified your
SQL statements so they could be read by a mere human instead of
just by a computer ;-})

You have
  SELECT ...
  FROM display
LEFT JOIN click
  ON display.name=click.name
  AND ...
  AND DAYOFMONTH(display.date) = '19';

The result of this select consists of the following:
  (1)  The result of the following inner join:
 SELECT ...
 FROM display. click
 WHERE display.name=click.name
   AND ...
   AND DAYOFMONTH(display.date) = '19';
  (2)  For each row of display that did not get used in (1),
   that row together with nulls for all columns of click.

In short, you get at least one row for each row of display--the ON
clause only affects which ones that have data from click.  That's
how LEFT JOIN works.  So, in particular, you get data for the
whole month.

You probably want something like:
  SELECT ...
  FROM display
LEFT JOIN click
  ON display.name=click.name
  WHERE ...
AND DAYOFMONTH(display.date) = '19';

The left join will give you rows with data from both tables and rows
from display that don't have data in click; the where clause will
then narrow the selection to the day you want.

Hope this helps

 Date: Sun, 19 Jan 2003 19:02:25 +0200
 To: [EMAIL PROTECTED]
 From: Lisi [EMAIL PROTECTED]
 Subject: Problem with LEFT JOIN

 I have a page with many ads that stores both the number of times an ad is
 displayed and how many times it gets clicked.  These are stored in two
 different tables (since different information is stored for each) but both
 have identical name columns. I am trying to display both # times displayed
 and # times clicked in the same table in an admin page.

 Here is my query to find ads that were clicked on today:
 SELECT ads_displayrate.name, SUM(ads_displayrate.count) as display, SUM(
 IF( ads_clickrate.date IS NULL, 0, 1 ) ) as click FROM ads_displayrate
LEFT
 JOIN ads_clickrate ON ads_displayrate.name = ads_clickrate.name AND
 YEAR(ads_displayrate.date) = '2003' AND MONTH(ads_displayrate.date) = '01'
 AND DAYOFMONTH(ads_displayrate.date) = '19' GROUP BY ads_displayrate.name
 ORDER BY ads_displayrate.name

 This works for clicks, but no matter what date I put in it only shows
 displays for the whole month - not the selected day. Also, if I use the
 following query to find clicks for the whole month

 SELECT ads_displayrate.name, SUM(ads_displayrate.count) as display, SUM(
 IF( ads_clickrate.date IS NULL, 0, 1 ) ) as click FROM ads_displayrate
LEFT
 JOIN ads_clickrate ON ads_displayrate.name = ads_clickrate.name AND
 YEAR(ads_displayrate.date) = '2003' AND MONTH(ads_displayrate.date) = '01'
 GROUP BY ads_displayrate.name ORDER BY ads_displayrate.name

 it doubles the number from what it should be.

 What am I doing wrong?

 Thanks in advance,

 -Lisi





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

3.23 doesn't report dup key on INSERT ... SELECT

[Bill Easton]

It appears that the stable, production version, 3.23 doesn't give an SQL
error when an INSERT ... SELECT would cause a duplicate primary key.  4.0
does not appear to have the problem.  (I discovered it when replicating from
3.23.49 Linux to a 4.0.1 Windows 2000--the server did not detect the
duplicate key, but replication crashed.)

The short script below creates a table TEMP_READ and inserts a row with key
(123, 123).  If I then insert the same thing with an INSERT ... SELECT, I
don't get an SQL error.  If I do the insert using INSERT ... VALUES, I do
(correctly) get the error.

This problem occurs on 3.23.49 (and Linux 7.3) and 3.23.54 (Win Me).  It
does not occur on 4.0.1 (Win 2k) and 4.0.8 (Linux 7.3).  The problem
occurred on 3.23.49 (Linux 7.3) using JDBC, as well as the MySQL client.

I looked in the list archives, but did not find it.

---

create database if not exists test;
use test;

drop table if exists GROUPS;
drop table if exists TEMP_READ;

CREATE temporary TABLE GROUPS
   (A int, B int);
insert into GROUPS values
   (123, 123);

CREATE TEMPORARY TABLE TEMP_READ
   (
  SUBJECT   INT NOT NULL,
  TARGETINT NOT NULL,
 PRIMARY KEY (SUBJECT, TARGET)
   );

INSERT INTO TEMP_READ
  values (123,123);

/* should fail, but does not */
INSERT INTO TEMP_READ
   SELECT * from GROUPS;

/* fails (correctly) */
INSERT INTO TEMP_READ
  values (123,123);





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Order By or Group By Help Please

[Bill Easton]

SOLUTION 1:

It's probably simplest, conceptually, to build a temporary table which
provides the proper ordering.  The ordering column below is a computed
value which determines the desired orderint of the File's.  Then, you
can join with an ORDER BY clause that gives the desired order.

mysql create temporary table temp
- select substring(FileNumber,1,6) as File,
-min(concat(1-RUSH, PDate, PTime, substring(FileNumber,1,6)))
as ordering
- from mytable
- group by File;
Query OK, 7 rows affected (0.04 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql select * from temp;
++---+
| File   | ordering  |
++---+
| 1-1023 | 012/0408:001-1023 |
| 1- | 012/0408:301- |
| 1- | 012/0406:001- |
| 1-6655 | 112/1108:401-6655 |
| 1-7654 | 012/0508:001-7654 |
| 1-9868 | 112/0514:001-9868 |
| 1- | 012/0408:001- |
++---+
7 rows in set (0.00 sec)

mysql select mytable.* from
- mytable, temp
- where substring(mytable.FileNumber,1,6) = temp.File
- order by ordering,RUSH desc,PDate,PTime;
+--++---+---+
| RUSH | FileNumber | PTime | PDate |
+--++---+---+
|1 | 1--001 | 06:00 | 12/04 |
|1 | 1-1023-001 | 08:00 | 12/04 |
|0 | 1-1023-002 | 14:00 | 12/09 |
|0 | 1-1023-003 | 11:00 | 12/10 |
|1 | 1--123 | 08:00 | 12/04 |
|0 | 1--124 | 09:30 | 12/09 |
|0 | 1--125 | 10:00 | 12/15 |
|1 | 1--000 | 08:30 | 12/04 |
|1 | 1-7654-043 | 08:00 | 12/05 |
|0 | 1-9868-000 | 14:00 | 12/05 |
|0 | 1-6655-021 | 08:40 | 12/11 |
|0 | 1-6655-022 | 13:30 | 12/15 |
+--++---+---+
12 rows in set (0.01 sec)

SOLUTION 2:

Yes, you can do it in one select statement.  You join two copies of your
table,
using one to compute the ordering.  I don't know how to get rid of the
ordering column in the result, as the expression won't work in the ORDER BY
clause.

mysql select A.*,
-min(concat(1-B.RUSH, B.PDate, B.PTime,
substring(B.FileNumber,1,6))) as ordering
- from mytable A, mytable B
- where substring(A.FileNumber,1,6) = substring(B.FileNumber,1,6)
- group by A.FileNumber
- order by ordering, A.RUSH desc, A.PDate, A.PTime;
+--++---+---+---+
| RUSH | FileNumber | PTime | PDate | ordering  |
+--++---+---+---+
|1 | 1--001 | 06:00 | 12/04 | 012/0406:001- |
|1 | 1-1023-001 | 08:00 | 12/04 | 012/0408:001-1023 |
|0 | 1-1023-002 | 14:00 | 12/09 | 012/0408:001-1023 |
|0 | 1-1023-003 | 11:00 | 12/10 | 012/0408:001-1023 |
|1 | 1--123 | 08:00 | 12/04 | 012/0408:001- |
|0 | 1--124 | 09:30 | 12/09 | 012/0408:001- |
|0 | 1--125 | 10:00 | 12/15 | 012/0408:001- |
|1 | 1--000 | 08:30 | 12/04 | 012/0408:301- |
|1 | 1-7654-043 | 08:00 | 12/05 | 012/0508:001-7654 |
|0 | 1-9868-000 | 14:00 | 12/05 | 112/0514:001-9868 |
|0 | 1-6655-021 | 08:40 | 12/11 | 112/1108:401-6655 |
|0 | 1-6655-022 | 13:30 | 12/15 | 112/1108:401-6655 |
+--++---+---+---+
12 rows in set (0.03 sec)

From: Roger Davis [EMAIL PROTECTED]
To: Mysql [EMAIL PROTECTED]
Subject: Order By or Group By Help Please
Date: Thu, 5 Dec 2002 19:54:57 -0500

Ok, I will try this one again.

I need some help on a select if it is possible.  Take for Example the
following data.

--
| RUSH | FileNumber | PTime  | PDate |
--
|  1   | 1-1023-001 | 08:00  | 12/04 |
|  1   | 1--001 | 06:00  | 12/04 |
|  0   | 1-1023-002 | 14:00  | 12/09 |
|  1   | 1--000 | 08:30  | 12/04 |
|  0   | 1-1023-003 | 11:00  | 12/10 |
|  1   | 1--123 | 08:00  | 12/04 |
|  0   | 1--124 | 09:30  | 12/09 |
|  0   | 1-6655-021 | 08:40  | 12/11 |
|  0   | 1--125 | 10:00  | 12/15 |
|  1   | 1-7654-043 | 08:00  | 12/05 |
|  0   | 1-6655-022 | 13:30  | 12/15 |
|  0   | 1-9868-000 | 14:00  | 12/05 |
--

To end up grouped like this.

--
| RUSH | FileNumber | PTime  | PDate |
--
|  1   | 1--001 | 06:00  | 12/04 |
|  1   | 1-1023-001 | 08:00  | 12/04 |
|  0   | 1-1023-002 | 14:00  | 12/09 |
|  0   | 1-1023-003 | 11:00  | 12/10 |
|  1   | 1--123 | 08:00  | 12/04 |
|  0   | 1--124 | 09:30  | 12/09 |
|  0   | 1--125 | 10:00  | 12/15 |
|  1   | 1--000 | 08:30  | 12/04 |
|  1   | 1-7654-043 | 08:00  | 12/05 |
|  0   | 1-9868-000 | 14:00  | 12/05 |
|  0   | 1-6655-021 | 08:40  | 12/11 |
|  0   | 1-6655-022 | 13:30  | 12/15 |
--

Basically, I need this.

If it is a RUSH (1), It needs to be first
If there are duplicate files (Like 1- (it has 2 dups) or 1-1023 (2
dups)) and one of them is a rush
They need to be grouped 

Re: MySQL Left Join Query

[Bill Easton]

Thanks, John (and Mark), for your comments.

I was inspired to write something, because it seems to be hard to find any
explanation of LEFT JOIN.  I looked through the half dozen SQL books on my
shelf and at the reference manuals for MySQL and DB2.  All of them had a
brief example of the straightforward case, A LEFT JOIN B ON
A.SOMETHING=B.SOMETHING, but none had any discussion or example of a LEFT
JOIN with a more complex ON clause or any discussion of what goes in the ON
clause or what goes in the WHERE clause.  I finally slogged through the
definition in the SQL standard.  It's quite long and quite arcane, and it
may not be readable at all unless you have a degree in math.

I'd be happy to write up a short example for inclusion in the MySQL manual,
if there were some indication from MySQL, AB, that it would be included.


- Original Message -
From: John Ragan [EMAIL PROTECTED]
To: Bill Easton [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, November 19, 2002 11:19 AM
Subject: Re: MySQL Left Join Query



 wow!

 that's known as above and beyond the call of
 duty.  hope the newbies appreciate your work.


  Here's a mini-tutorial on left join that should solve your problem.
 
[...]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL Left Join Query

[Bill Easton]

Here's a mini-tutorial on left join that should solve your problem.

First, let's make a smaller example.  (It would have been helpful if
you had done that and formatted your select so it could be read when
you posted the question ;-)  Here are 2 tables:

select * from header;select * from placement;
+--+ +--+---+
| code | | code | product   |
+--+ +--+---+
|6 | |6 | NOSUCH|
|7 | |7 | OVRLCKBDG |
|8 | |7 | FUBAR |
+--+ +--+---+

You want:
  Info for header 6, and, if any, info for placement (6, '1029106')
  Info for header 7, and, if any, info for placement (7, 'OVRLCKBDG')

Let's look at what a left join is.  For a SQL query,
select A.*, B.* from A left join B on condition;
returns two things,
(1)  Matching rows from A and B, as would be returned by
SELECT A.*, B.* from A, B where condition;
(2)  Rows of the form A.*, null, ..., null  where those rows
in A are included that do NOT participate in the match
in (1), followed by nulls representing the columns of B.

So, in our example,

select header.*, placement.*
  from header
left join placement
  on header.code = placement.code;

gives us all of the header codes and the matching placements:
+--+--+---+
| code | code | product   |
+--+--+---+
|6 |6 | NOSUCH|
|7 |7 | OVRLCKBDG |
|7 |7 | FUBAR |
|8 | NULL | NULL  |
+--+--+---+

Well, we don't want all of the matching placements; we just want the
specific matches listed above.  So, let's add these to the left
join condition:

select header.*, placement.*
  from header
left join placement
  on header.code = placement.code
and ( header.code = 6 and placement.product = '1029106'
   or header.code = 7 and placement.product = 'OVRLCKBDG' );

This gives us the placements we want, but there are too many headers:
+--+--+---+
| code | code | product   |
+--+--+---+
|6 | NULL | NULL  |
|7 |7 | OVRLCKBDG |
|8 | NULL | NULL  |
+--+--+---+

So. finally, we get rid of the extra headers by adding a where clause:

select header.*, placement.*
  from header
left join placement
  on header.code = placement.code
and ( header.code = 6 and placement.product = '1029106'
   or header.code = 7 and placement.product = 'OVRLCKBDG' )
  where header.code in (6, 7);

This gives the desired result:
+--+--+---+
| code | code | product   |
+--+--+---+
|6 | NULL | NULL  |
|7 |7 | OVRLCKBDG |
+--+--+---+




This is a bit ugly, in that the header.code's are listed twice, and there
are a lot of literals in the select statement.  It might be better to
use a temporary table to hold the desired (code, product) pairs.  Then,
we join the 3 tables with a select that seems less complex:

Here's the temporary table:

select * from tt;
+--+---+
| code | product   |
+--+---+
|6 | 1029106   |
|7 | OVRLCKBDG |
+--+---+

and here's the 3-table join:

select header.*, placement.*
  from header
inner join tt
  on header.code = tt.code
left join placement
  on tt.code = placement.code
and tt.product = placement.product;

which also gives the desired result:
+--+--+---+
| code | code | product   |
+--+--+---+
|6 | NULL | NULL  |
|7 |7 | OVRLCKBDG |
+--+--+---+

 From: Mark Colvin [EMAIL PROTECTED]
 To: MySQL Mailing List \(E-mail\) [EMAIL PROTECTED]
 Subject: MySQL Left Join Query
 Date: Mon, 18 Nov 2002 08:05:51 -

 I have a two table query where I want to return records from the first
table
 and if they have corresponding details in my second table I wish to return
 those as well. A sample sql statement I am using is as follows:

 select decheader.code, decheader.height, decheader.width,
 decplacement.position, decplacement.product from decheader left join
 decplacement on decheader.code = decplacement.code where (decheader.code =
 '7' and decplacement.product = 'OVRLCKBDG') or (decheader.code =
'6'
 and decplacement.product = '1029106') or (decheader.code = '5' and
 decplacement.product = '1029103') or (decheader.code = '5' and
 decplacement.product = '1029104') or (decheader.code = '5' and
 decplacement.product = '1029105') or (decheader.code = '5' and
 decplacement.product = '1029106') or (decheader.code = '4' and
 decplacement.product = '1029104') or (decheader.code = '4' and
 decplacement.product = '1029105')

re: database corrupted after power switched off

[Bill Easton]

VRDate: Mon, 28 Oct 2002 12:35:01 +0200
VRFrom: Victoria Reznichenko [EMAIL PROTECTED]
VRSubject: re: database corrupted after power switched off

VRTom,
VRMonday, October 28, 2002, 11:59:16 AM, you wrote:

VRTT is it a normal behaviour that a sql databases gets corrupted if the
power of
VRTT the whole system will be switched off while an application is writing
to the
VRTT database ?

VRYes. Other situations when tables may become corrupted are listed
VRhere:
VR http://www.mysql.com/doc/en/Corrupted_MyISAM_tables.html

   ... which says MyISAM table format is very reliable (all changes to a
table is
   written before the SQL statements returns)  Evidently, this is NOT true.
(Perhaps,
   it used to be true for ISAM tables.  The documentation should be
changed.)

VRTT what can i do that this problem does no more appear ?

VRYou have to shutdown MySQL server correctly, buy UPS :-)

There are a couple of other choices (perhaps to be used in addition to UPS).

(1)  Use InnoDB tables.  Evidently, they are designed to survive such
things,
especially with innodb_flush_log_at_trx_commit=1.

We have survived having the server disconnected from the UPS.  I hope to not
find
out if it works twice :-)

(2)  If you have updates in infrequent bursts, such as with a server used
primarily
for queries or a single-user system, do FLUSH TABLES after each burst.  This
writes
the tables to disk.

We have a number of users with single-user local database a which are
synchronized with
the server from time to time, and we have no control over whether or not
they have UPS.
We did have the problem that the databases would be corrupted if power were
lost an
hour after updating.  Adding the FLUSH TABLES after each user interaction
that updates
the database solved this.  (In reality, we only flush the tables that have
been
modified.)









-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: database corrupted after power switched off

[Bill Easton]

Thanks.  You're right, of course.  Nevertheless, I think the suggestions I
made should help.

(Context:  There was a post asking how to avoid table corruption.  There
were several replies suggesting UPS.  I suggested that, in addition to UPS,
there were two other things that could be done.)

(1)  Use InnoDB. Of course, you need to worry that InnoDB does a
physical write when it thinks it does.  My understanding is that it tries to
do so, if it can, when innodb_flush_log_at_trx_commit=1.

(2)  If MyISAM updates are infrequent, flush tables after updates.  This
closes
the files, which generally causes physical writes on most OS's.  Of course,
this doesn't help with loss of power while an update is running, but it
does avoid corruption when there are no updates going on, and MyISAM
is susceptible to the latter.


- Original Message -
From: Michael T. Babcock [EMAIL PROTECTED]
To: Bill Easton [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; Tom Thurnherr [EMAIL PROTECTED]
Sent: Monday, October 28, 2002 10:18 AM
Subject: Re: database corrupted after power switched off


 Bill Easton wrote:

... which says MyISAM table format is very reliable (all changes to a
 table is written before the SQL statements returns)  Evidently, this is
NOT true.
 (Perhaps, it used to be true for ISAM tables.  The documentation should
be
 changed.)
 
 You are misinterpreting the statement; it says that the database will be
 safe after the SQL command returns (all SQL commands active on the
 table, for that matter).  That is to say, if the power were turned off
 while any (update / etc.) SQL command was running, you could expect
 corruption.

 Again, make sure you check how your OS deals with write caching and
 consider turning it off on the partition / disk you're using for DB
storage.

 --
 Michael T. Babcock
 C.T.O., FibreSpeed Ltd.
 http://www.fibrespeed.net/~mbabcock






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Spam

[Bill Easton]

It seems they are using SPEWS (www.spews.org).

A rude way to find out that our ISP has a problem with the IP address they
gave us :-{

--

From: MySQL [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Spam
Reply-To: [EMAIL PROTECTED]
Message-Id: [EMAIL PROTECTED]
Date: Tue, 24 Sep 2002 08:23:23 -0700 (PDT)

   Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
(http://www.ezmlm.org)
   Date: Tue, 24 Sep 2002 12:07:14 +0200 (CEST)
   From: Gabriele Carioli [EMAIL PROTECTED]

   -- mysql, query

   I wonder if you're using any RBL to avoid spam.

   They're quite effective, since most of spam comes from
   open relays or misconfigured proxies.


Only if the wars between the various blacklists have died out.  A
little internet history: open relays were designed to allow people
with unreliable email connections to communicate.  The newbies on the
internet have messed that up, but there are still areas in the world
where the original problem still exists.

Frankly the antispammers are more of a pita than the spammers.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Zombie slave process on my master

[Bill Easton]

Our server seems to think it is a slave.  It has a slave thread rnning,
whose only action is to complain once a minute about not being able to
connect to master .  This just started one day, when we took the server
down and brought it right back up.

I'd like to get rid of the slave thread, but I don't know where to start.
Any ideas appreciated.



Info. that could be of use follows

We're running on Red Hat 7.2, using 3.23.49-max-log.  (We're replicating
from a 4.0.1 on Win 2000)

Here's the slave status.  (In fact, the server is on port 3307; there is
another MySQL running on port 3306.  There's no user named test)

mysql show slave status;
+-+-+-+---+--+-+
---+-+-++---
-+--+
| Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos |
Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno |
Last_error | Skip_counter |
+-+-+-+---+--+-+
---+-+-++---
-+--+
| | test| 3306| 60|  | 4   |
Yes   | | | 0  |
| 0|
+-+-+-+---+--+-+
---+-+-++---
-+--+
1 row in set (0.00 sec)

Here's the processlist.  Id=1 is the zombie.   Id=3 is the legitimate
connection over the net from a slave.

mysql show processlist;
++-++--+-+---+--
---+--+
| Id | User| Host   | db   | Command | Time  |
State   | Info |
++-++--+-+---+--
---+--+
|  1 | system user | none   | NULL | Connect | 50846 |
connecting to master| NULL |
|  3 | repl| id144.megapipe.net | NULL | Binlog Dump | 50771 |
Slave connection: waiting for binlog update | NULL |
| 93 | root| localhost  | RCAP_SVR | Query   | 0 |
NULL| show processlist |
++-++--+-+---+--
---+--+
3 rows in set (0.00 sec)

Here's a fragment of the error log when this appears to have started:

020726 02:02:39  mysqld started
020726  6:02:40  InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
020805 11:28:15  /usr/local/mysql/libexec/mysqld: Normal shutdown

020805 11:28:15  InnoDB: Starting shutdown...
020805 11:28:26  InnoDB: Shutdown completed
020805 11:28:26  /usr/local/mysql/libexec/mysqld: Shutdown Complete

020805 07:28:26  mysqld ended

020805 07:28:45  mysqld started
020805 11:28:45  InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
020805 11:28:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:29:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:30:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:31:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:32:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:33:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:34:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:35:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:36:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:37:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:38:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:39:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:40:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:41:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:42:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:43:45  

Re: Zombie slave process on my master

[Bill Easton]

Thanks, Victoria.  Yes, there is a master.info, and it looks like the
culprit.  Can I just delete it?

Any idea where it came from?  To the best of my knowledge, the server was
never started as a slave.

 Date: Thu, 15 Aug 2002 16:42:01 +0300
 From: Victoria Reznichenko [EMAIL PROTECTED]
 Subject: Re: Zombie slave process on my master

 Bill,
 Thursday, August 15, 2002, 3:43:03 PM, you wrote:

 BE Our server seems to think it is a slave.  It has a slave thread
rnning,
 BE whose only action is to complain once a minute about not being able to
 BE connect to master .  This just started one day, when we took the
server
 BE down and brought it right back up.

 BE I'd like to get rid of the slave thread, but I don't know where to
start.
 BE Any ideas appreciated.

 Check MySQL data dir. Do you have master.info file here? Seems,
 master.info contains wrong replication info.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

zombie slave process on my master

[Bill Easton]

Our server seems to think it is a slave.  It has a slave thread rnning,
whose only action is to complain once a minute about not being able to
connect to master .  This just started one day, when we took the server
down and brought it right back up.

I'd like to get rid of the slave thread, but I don't know where to start.
Any ideas appreciated.



Info. that could be of use follows

We're running on Red Hat 7.2, using 3.23.49-max-log.  (We're replicating
from a 4.0.1 on Win 2000)

Here's the slave status.  (In fact, the server is on port 3307; there is
another MySQL running on port 3306.  There's no user named test)

mysql show slave status;
+-+-+-+---+--+-+
---+-+-++---
-+--+
| Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos |
Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno |
Last_error | Skip_counter |
+-+-+-+---+--+-+
---+-+-++---
-+--+
| | test| 3306| 60|  | 4   |
Yes   | | | 0  |
| 0|
+-+-+-+---+--+-+
---+-+-++---
-+--+
1 row in set (0.00 sec)

Here's the processlist.  Id=1 is the zombie.   Id=3 is the legitimate
connection over the net from a slave.

mysql show processlist;
++-++--+-+---+--
---+--+
| Id | User| Host   | db   | Command | Time  |
State   | Info |
++-++--+-+---+--
---+--+
|  1 | system user | none   | NULL | Connect | 50846 |
connecting to master| NULL |
|  3 | repl| id144.megapipe.net | NULL | Binlog Dump | 50771 |
Slave connection: waiting for binlog update | NULL |
| 93 | root| localhost  | RCAP_SVR | Query   | 0 |
NULL| show processlist |
++-++--+-+---+--
---+--+
3 rows in set (0.00 sec)

Here's a fragment of the error log when this appears to have started:

020726 02:02:39  mysqld started
020726  6:02:40  InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
020805 11:28:15  /usr/local/mysql/libexec/mysqld: Normal shutdown

020805 11:28:15  InnoDB: Starting shutdown...
020805 11:28:26  InnoDB: Shutdown completed
020805 11:28:26  /usr/local/mysql/libexec/mysqld: Shutdown Complete

020805 07:28:26  mysqld ended

020805 07:28:45  mysqld started
020805 11:28:45  InnoDB: Started
/usr/local/mysql/libexec/mysqld: ready for connections
020805 11:28:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:29:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:30:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:31:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:32:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:33:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:34:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:35:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:36:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:37:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:38:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:39:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:40:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:41:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:42:45  Slave thread: error connecting to master: Unknown MySQL
Server Host '' (4) (107), retry in 60 sec
020805 11:43:45  

Windows install hangs (resolution)

[Bill Easton]

Synopsis:  When installing on Win 98, using the InstallShield installer
provided,
the installation of MySQL hangs and the GUI freezes.  This turned out to be
an interaction
with another program (RealPlayer and AOL).  Removing RealPlayer let
us proceed with the install.

I'm sending this to the list, in the hope of helping someone who might hit
the same problem.

Resolution:  When we tried to reboot, there was a program not responding.
When we said to exit same, the MySQL install screen started to come up while
the system was shutting down.  We killed that and rebooted.  Then, we tried
the install again.  When hung, we went to the task manager (click on empty
space on desktop, then ctrl-alt-delete)--RealPlayer was shown as not
responding.  We killed that task, and the MySQL install started to proceed
during the Windows shutdown.
We then uninstalled RealPlayer and rebooted.  We can now run the install.

When we dialed the Internet, using the AOL software, AOL complained that
there were missing files--seems it wanted RealPlayer to play some music.
Well, it seems the computer really belongs to his 15-year-old daughter, and
she likes to play music while she studies...  Anyway, we told the AOL
software to forget about the music.

There is still a problem with the MyODBC install.  When doing the ODBC
setup,
after we passed through all of the ODBC
setup screens, the ODBC setup program crashed with an invalid instruction;
the message Window included something about the installer BOOTSTRP.  I
don't
know if this is related to the other problem--but the machine seems to be a
bit
messed up.

I would conjecture that the install would have worked (except, perhaps, for
the ODBC) if we had tried the install in safe mode.


Bill Easton



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysqldump -A dump.txt

[Bill Easton]

You are loading all databases, but the security database (mysql) is already
there.  Assuming you are starting with nothing and restoring all databases
that the server knows about, one method that works is to start the server
with --skip-grant-tables, then load the dump file, then flush privileges.
This will restore the security settings.  At least, it works for me.  (I
have
--add-drop-tables when I dump; presumably it would work to drop or delete
the mysql database manually.)

There's also a -o switch in the mysql client which says only update one
database.
I haven't succeeded in making it work.

 Date: Sun, 02 Jun 2002 18:49:24 -0400
 To: Benjamin Pflugmann [EMAIL PROTECTED]
 From: Eric Frazier [EMAIL PROTECTED]
 Subject: Re: mysqldump -A dump.txt

 Hi,

 I didn't get that deeply into why exactly, but it seems that when I
removed
 the mysql database from the file, that the rest went fine with
 mysql -u -p dumpfile

 I got an error about the column_prv field already existing I believe.
Sorry,
 I should have been much more specific. I appreciate your answering even
 though I was vague.

 I think mostly I learned my lesson, and I should export one database at a
 time, with --tab=


 Thanks,


 Eric

 At 01:52 AM 6/3/02 +0200, Benjamin Pflugmann wrote:
 Hi.
 
 Which version of mysqldump did you use and what error message do you
 get?
 
 I have never used mysqldump this way but from what I read in the help
 of mysqldump 3.23.31, mysqldump should insert the necessary SQL
 statements regarding changing and creating the databases (if they do
 not already exist).
 
 Bye,
 
  Benjamin.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

InnoDB and temp. tables

[Bill Easton]

I note by experiment (by observing the content of the binary log) that I get
the following actions when trying to use a temporary table during a
transaction:

-- on creating a temporary table, the create (only) is committed
-- on dropping a temporary table, the current transaction is committed

All tables are InnoDB, including the temporary table.  Using Linux:
3.23.49-max-log

What are the rules?  Can I use a temporary table to hold  temporary results
during a transaction, and, if so, will I still be able to use the binary log
for recovery purposes?

From my experiment, it looks as if I could do so, provided I use a different
name each time and I don't drop the temporary tables explicitly, but let
them go away when connections are terminated.  There aren't many, and this
would be acceptable.  I'd like to know if it would be safe to do so.
(Different names are needed since, in principle, two concurrent transactions
could create temporary tables, and the operations on the temporary tables
could be interleaved in the binary log.)

Note.  I get the same actions with a table which is not declared
temporary--though, of course, it doesn't go away when the connection does.

I wasn't able to find anything on this in the manual.  I seem to recall that
some other databases--such as Oracle--automatically commit before any DDL
statement.

sql,query



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: How can I do this SQL query

[Bill Easton]

Here's one way.  Assumes that cust/item pairs are unique.

select cust, sum((item='12a')+2*(item='13a')) as IND from transfile
group by cust having IND=1;

Alternatively, you could build a temporary table with cust's who ordered
13a,
the use a left join.

 From: Smith, Mike [EMAIL PROTECTED]
 To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
 Subject: RE: How can I do this SQL query
 Date: Thu, 23 May 2002 15:33:05 -0400

  How can I do this SQL query

 I have a file(transfile)  that has 2 fields(cust# and item#)

 CUST#  ITEM#
 1  '12a'
 1  '13a'
 2  '12a'
 3  '13a'
 4  '15a'

 If I want to select customers that have ordered item '12a' but not '13a'
 How can I do this?

 I want to end up with a result of   only customer 2 in this case.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Can't join temp table to self?

[Bill Easton]

When I try to join a temporary table to itself, I get an error message, as
follows:

create temporary table TEMP (X int NOT NULL);
select * from TEMP A, TEMP B;

The select gives: ERROR 1137: Can't reopen table: 'A'

It appears to work as expected without the temporary.  Can I not join a
temporary table to itself?

Happens on:
Windows 2000: 3.23.47-nt
Linux 7.2: 3.23.49-max-log
Also happens on Linux with InnoDB instead of MyISAM.

sql,query



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: need SQL assistance with a few selects

[Bill Easton]

See section 3.5.4 of the manual.  The example there can be adapted to give
you
the date of the max or min disk size in one query.  You have it right for
MAX
and MIN; there is also an AVG function.

From the manual:

``Can it be done with a single query?''

Yes, but only by using a quite inefficient trick that I call the
``MAX-CONCAT trick'':

SELECT article,
   SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(  MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

--
From: Robert L. Yelvington [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: need SQL assistance with a few selects
Date: Fri, 3 May 2002 09:24:02 -0500

Greetings folks,

I am able to do all this by pulling out data with PHP or PERL...just need
some straight MySQL command line advise

Specifically, I need some assistance with the following:

Here's my table's structure (table name is 'table'):

ID, CLIENT_NAME, CLIENT_LOGIN, DISK_SIZE, RUNTIME, RUNDATE

Here's example data:

1,ABC INC,abcinc,1.2,00:30:00,2002-01-01
2,Joe's Restaurant,joesrest,0.2,00:30:00,2002-01-01
3,ABC INC,abcinc,3.2,00:30:00,2002-01-02
4,Joe's Restaurant,joesrest,2.2,00:30:00,2002-01-02
5,ABC INC,abcinc,1.0,00:30:00,2002-01-03
6,Joe's Restaurant,joesrest,1.2,00:30:00,2002-01-03

This is what I'd like to find out:

1. An average disksize for each client, date doesn't matter.
The result I'd like to see would be something like:
ABC INC,1.8
Joe's Restaurant,1.2

2. The date of max disksize and the date of min disksize...in one query...if
that's possible.

I can get the min and max disksize as follows(please critique):

SELECT client_name, min(disk_size) from table group by client_name;

and likewise with the max() function.

Thanks so much!

Respectfully yours,

Rob





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: How do I find duplicate records?

[Bill Easton]

Try:

select substring(id,1,8) as pfx from foo group by pfx;

or, if the column is numeric:

 select floor(id/100) as pfx from foo group by pfx;

 Date: Tue, 30 Apr 2002 12:59:05 -0700 (PDT)
 From: James Dellacova [EMAIL PROTECTED]
 Subject: How do I find duplicate records?
 [...]
 I have over 68,000 records in a table with unique ID
 field that has 10 chars.  Need to create a query to
 find all matching or duplicate IDs up to 8 chars. 
 (Eg. 12345678%%)
 [...]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: sub selects

[Bill Easton]

Try the following:

SELECT leads.* FROM leads
LEFT JOIN blacklist ON leads.refnumberid = blacklist.leadid
  WHERE blacklist.leadid IS NULL
AND ...

The LEFT JOIN will give you a row for every row in leads; blacklist columns
will be null if there's no matching blacklist, so the WHERE clause will
filter these out.

Check out LEFT [OUTER] JOIN in the manual...


 From: [EMAIL PROTECTED]
 To: Mysql. Com [EMAIL PROTECTED]
 Subject: sub selects
 Date: Mon, 22 Apr 2002 15:34:22 +0100

 I know that mysql does not support sub selects but I want to be able to do
 the following:

 I have a table with all my leads in called leads. The table has a unique
ref
 for each lead called refnumberid
 I have a table with leads in and products that leads have asked not to be
 mailed on the table has a field called leadid which stores the value of
 refnumberid above.

 I want to select records from the table leads provided they do not have a
 record in the table blacklist. I have used the following select statement
 which produces no results. The intro screen collects the value of adate
and
 acountry

 sql2= SELECT leads.* from leads,blacklist   WHERE  leads.date1stcontact
=
 'adate'   and leads.country = 'acountry' and leads.refnumberid 
 blacklist.leadid and leads.productname  blacklist.productOR
 leads.date1stcontact = 'adate'  and leads.refnumberid 
 blacklist.leadid and leads.productname  blacklist.product   and
 'acountry' = 'ALL'
set rs22=Server.CreateObject(ADODB.Recordset)
rs22.open sql2,db,0,1


 Can anyone help?

 Regards

 Michael Johnson
 Managing Director
 Humphrey Consulting Limited BPEnet
 13 Austin Friars
 London
 EC2N 2JX
 Tel +44(0)1323 438975
 Fax +44(0)1323 738355
 Email [EMAIL PROTECTED]
 URL www.bpenet.net



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: About escape character '\'

[Bill Easton]

Sorry, I guess I answered too quickly.  You have a problem, if you have to
use a
literal SQL statement, and the various DBMS's use different escape syntax.

However, in Java, you can just use a prepared statement

 String sql = INSERT INTO files (filepath) VALUES (?));
 PreparedStatement ps = con.prepareStatement(sql);
 ps.setString(1, c:\\Repository\\Pack\\);
 ps.executeUpdate();

Hope that works for you.

 Subject: RE: About escape character '\'
 Date: Thu, 11 Apr 2002 13:54:46 -0400
 From: Kathy Sung [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]

 sorry, I should say add 3 extra '\' and not just one in my previous
 email, since if I add 3 more and it becomes:
 INSERT INTO files (filepath) VALUES ('c:RepositoryPack')
 which represents the following string in Java:
 INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\')
 (because in Java '\' is also an escape character)

 So, in MySQL 'c:\Repository\Pack\' will be inserted, while in MS SQL and
 Oracle 'c:\\Repository\\Pack\\' will be inserted and that's the problem
 for me...

 -Original Message-
 From: Bill Easton [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, April 11, 2002 7:27 AM
 To: [EMAIL PROTECTED]
 Cc: Kathy Sung
 Subject: Re: About escape character '\'


 Kathy,

 You shouldn't have a problem here--it's Java, not MySQL, that requires
 the
 doubled '\' in a string literal.

 In Java, the string literal:
   INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
 represents the string whose content is
   INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
 so what gets inserted is, in fact,
   c:\Repository\Pack\

  Subject: About escape character '\'
  Date: Wed, 10 Apr 2002 19:44:21 -0400
  From: Kathy Sung [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
 
  Hi all,
 
  I want to insert the string 'c:\Repository\Pack\' into a mysql table
  using java and I did it as follows:
 
  sql =3D3D INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\'
 );
  insertStmt.execute(sql);
 
  I got an error and I know I should add an extra '\' to escape each of
  the '\' in the above sql statement.  But, the problem is MS SQL and
  Oracle do not treat '\' as an escape character in sql statements, and
 I
  want to keep my Java program as database-independent as possible. (and
 I
  don't want the whole string 'c:\\Repository\\Pack\\' to be stored in
 the
  database when I use MS SQL server or Oracle)
 
  Any suggestion to my problem will be greatly appreciated.
 
  Thanks,
  Kathy






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Newbie GROUP-type question

[Bill Easton]

 select date_format(datepurch,'%Y-%m') as MONTH, count(*), sum(cost)
 from cd
 group by MONTH

 Date: Fri, 12 Apr 2002 14:57:55 -0400
 From: Jesse Sheidlower [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Newbie GROUP-type question
 
 I'm relatively new to the construction of complex queries,
 and I'm trying to figure one out that I'm sure is common;
 I wasn't able to find anything especially helpful in the docs.
 
 Suppose I have the canonical database of CDs, with one table,
 which includes fields for title, cost, and date_purchased.
 I'd like to get a result set that shows the number of CDs
 purchased, and their sum cost, for each month. Thus, if I
 bought some CDs that cost exactly $15.00 each month for the
 last few months, I'll get something like
 
 2001-12   2   30.00
 2002-01   3   45.00
 2002-02   2   30.00
 2002-03   1   15.00
 
 (The exact format isn't too important, I can figure that out
 once I get the basic query down.)
 
 What's the best way to construct this SQL query?
 
 Thanks.
 
 Jesse Sheidlower



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: About escape character '\'

[Bill Easton]

Kathy,

You shouldn't have a problem here--it's Java, not MySQL, that requires the
doubled '\' in a string literal.

In Java, the string literal:
  INSERT INTO files (filepath) VALUES ('c:\\Repository\\Pack\\' )
represents the string whose content is
  INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' )
so what gets inserted is, in fact,
  c:\Repository\Pack\

 Subject: About escape character '\'
 Date: Wed, 10 Apr 2002 19:44:21 -0400
 From: Kathy Sung [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]

 Hi all,

 I want to insert the string 'c:\Repository\Pack\' into a mysql table
 using java and I did it as follows:

 sql =3D INSERT INTO files (filepath) VALUES ('c:\Repository\Pack\' );
 insertStmt.execute(sql);

 I got an error and I know I should add an extra '\' to escape each of
 the '\' in the above sql statement.  But, the problem is MS SQL and
 Oracle do not treat '\' as an escape character in sql statements, and I
 want to keep my Java program as database-independent as possible. (and I
 don't want the whole string 'c:\\Repository\\Pack\\' to be stored in the
 database when I use MS SQL server or Oracle)

 Any suggestion to my problem will be greatly appreciated.

 Thanks,
 Kathy



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: accessing MySQL database from Access

[Bill Easton]

On the grounds that someone else may have this problem, and in response to
John's post, below, I am providing a cookbook of how to do what I asked.

The problem:  I have a bunch of users, all with small local MySQL databases
on their Windows boxes (which get synchronized with a large server
database--but they need to be able to work offline.)  They mostly have and
know Microsoft Access, and we have a requirement to support letting them get
to their data in Access.  Turns out you can bring up the MySQL database in
Access just fine.

BTW, we did consider just using Access locally, with the JDBC-ODBC bridge.
It worked, but we weren't very impressed with how robust it was.  Also, it
would have been harder to avoid having the users modify my database.

The cookbook:

(1)  Install ODBC:
 - Download ODBC from the mysql.com page and unzip into a directory.  (Note
there are downloads for 95/98/ME and NT/2000/XP.  These differ by one file:
The file odbc.inf is a copy of odbc.95 or odbc.NT, as appropriate.  Also,
there are both readme and README--which confuses Windows--they are the
same.)
 - Run the installer, SETUP.EXE

(2)  Link to your MySQL database from Access
 - Start Access
 - Use the wizard to create a new Access database
 - Click File | Get External Data | Link Tables
 - Select Files of Type: ODBC Database
 - Select the Machine Data Sources tab
 - Click New
 - Select User or System Data Sources, as desired
 - Click Next
 - Select MySQL and click Next
 - Click Finish
 - You should get an ODBC driver configuration screen.  Fill in:
Windows DSN name:  Name you would like to use for this data source.
MySQL host (name or IP):  localhost
MySQL database name: The name of the database.
 - Click OK
 - Your new data source should be selected; Click OK
 - You will get a Driver connect screen (which looks like the configuration
screen above) - Click OK
 - You will get a list of tables.  Select those you want to see through
Access.
 - Voila!  You can now access the tables through the Access database screen.


 From: john [EMAIL PROTECTED]
 To: MySQL Lists [EMAIL PROTECTED]
 Subject: RE: accessing MySQL database from Access
 Date: Fri, 5 Apr 2002 08:33:56 -0600

 You know this sounds like a good idea, and I know this is what the
utilities
 are for that come provided with mysql(d). (MYODBC and mysqlaccess)I would
 like to inquire, what do you mean. I don't comprehend what you just said.
 Please someone give more specific details...

 thanks,
 John

 -Original Message-
 From: Dan Nelson [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, April 04, 2002 8:42 PM
 To: Bill Easton
 Cc: [EMAIL PROTECTED]
 Subject: Re: accessing MySQL database from Access


 In the last episode (Apr 04), Bill Easton said:
  I have a bunch of users with a MySQL database on their local
  machines.
 
  Life would be simpler if they were able to access the MySQL database
  (read only) from Microsoft Access.
 
  Can this be done through an ODBC connection?  Can somebody tell me
  how to set it up?

 Just install myodbc on the windows machines, set up a DSN pointing to
 the MySQL server, and link the tables in your Access project.

 --
  Dan Nelson
  [EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

accessing MySQL database from Access

[Bill Easton]

I have a bunch of users with a MySQL database on their local machines.

Life would be simpler if they were able to access the MySQL database (read
only) from Microsoft Access.

Can this be done through an ODBC connection?  Can somebody tell me how to
set it up?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Fw: Help with JOIN query

[Bill Easton]

What you need is to somehow get an additional column with the rows numbered.
You can do this by creating a temporary table with an AUTO_INCREMENT column
and inserting the records from the original table.  Of course, you need an
ORDER BY clause on the INSERT ... SELECT, because SQL doesn't know or care
what order the rows are supposed to be in.

Once you have done this, you can join the two temporary tables on the count
row.

Here's an example for one of your tables:

mysql select * from b;
+--+---+
| seq  | name  |
+--+---+
|9 | Kelly |
|3 | Jack  |
|1 | Bob   |
|7 | Janet |
|8 | Gary  |
+--+---+
5 rows in set (0.00 sec)

mysql create temporary table tb (row int auto_increment primary key, seq
int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql insert into tb (seq,name) select * from b order by seq;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql select * from tb;
+-+--+---+
| row | seq  | name  |
+-+--+---+
|   1 |1 | Bob   |
|   2 |3 | Jack  |
|   3 |7 | Janet |
|   4 |8 | Gary  |
|   5 |9 | Kelly |
+-+--+---+
5 rows in set (0.00 sec)

mysql

 From: Jeff Habermann [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Help with JOIN query
 Date: Thu, 28 Mar 2002 17:16:05 -0800

 What i want to do seems simple, but i can not for the life of me figure
out
 how to do it.
 Tables:  These tables are not related in any way.

 A
 --
 1
 2
 3
 4
 5

 B
 --
 1,Bob
 3,Jack
 7,Janet
 8,Gary
 9,Kelly

 I would like to combine tables A and B with a SELECT statement to get
this:

 SELECT
 --
 1,1,Bob
 2,3,Jack
 3,7,Janet
 4,8,Gary
 5,9,Kelly

 So basically i want to join two tables, but i dont have anything to match
 on...Basically, i am trying to take a column from one table and add it to
 another table for a SELECT statement.  I am not sure if JOIN is even what
i
 am looking for here.  To do this, i am assuming both tables have the exact
 same number of records.
 Any ideas on how i can do this?

 Thanks,
 Jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Date Interval Grouping

[Bill Easton]

try GROUP BY (UNIX_TIMESTAMP(tVH.vDateTime) -
MOD(UNIX_TIMESTAMP(tVH.vDateTime, 300)))

 From: [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Date Interval Grouping
 Date: Tue, 26 Mar 2002 19:13:44 -0700

 Ok here's what I trying to do. I am trying to design a little browser/ip
 tracking page and I need an SQL statement that will allow me to group IPs
 that hit the site in 5 minute intervals (well I just want the interval
thing
 to work cuz I'll prolly want to make this like 30 minutes or a full hour).
 The reason I want this kind of grouping is that if a person hits the page
 constantly for 5 minutes I don't want that to push other relevant IPs down
 the listing. Here's what I have to actually select the data from the
 database.

 SELECT tblVisitorHits.vIP, tblVisitorHits.vDateTime, tblBrowser.bVersion
 FROM tblBrowser
 RIGHT JOIN tblVisitorHits ON tblVisitorHits.vBrowser = tblBrowser.bKey
 ORDER BY tblVisitorHits.vDateTime DESC, tblBrowser.bVersion ASC
 LIMIT 0, 50;

 // A simple group by I know will not work. but was wondering if there
 // was a way to create a more complex group by that included expressions.

 I don't know how one would implement the interval grouping. Any help would
 be graciously accepted.

 ~!



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Complex query

[Bill Easton]

The following will do the trick:

select * from book_hold
group by tcc_cn
having updated=min(updated) and max(status='NOTIFIED')=0;

We use the fact that (status='NOTIFIED') will be 0 when false and 1 when
true,
so this gives a way to select rows for which another row with the same
tcc_cn
and updated has status='NOTIFIED'.

(An alternative, perhaps less obscure, way is to select all of the tcc_cn's
that
ARE notified into a temporary table and then use an outer join, selecting
for
a null in the temporary table.  You still have
to do the group by to select the min(updated), however.)

Hope this helps.


+++--+
| tcc_cn | updated| status   |
+++--+
| 990227 | 2002-03-08 | WAITING  |
| 990227 | 2002-03-10 | WAITING  |
| 990228 | 2002-03-08 | WAITING  |
| 990228 | 2002-03-15 | NOTIFIED |
+++--+
4 rows in set (0.00 sec)

mysql select * from book_hold
- group by tcc_cn
- having updated=min(updated) and max(status='NOTIFIED')=0;
+++-+
| tcc_cn | updated| status  |
+++-+
| 990227 | 2002-03-08 | WAITING |
+++-+
1 row in set (0.00 sec)


 Date: Thu, 21 Mar 2002 15:44:42 -0700 (MST)
 From: Jeff Shipman - SysProg [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Complex query

 I've got a mysql table and I need to perform
 a complex query on it, but I'm not sure how
 to formulate it.

 This is what I need to do:

 select id,tcc_cn,entered,updated,enteredby,num_bump,
 status FROM book_hold WHERE

 1) There are no books with the tcc_cn that already
 have a status of 'NOTIFIED'.
 2) Its updated date is the MIN(updated) for that
 tcc_cn.

 So, for example, if I have the following items:

 tcc_cn   updated   status
 -
 990227   03-08-02  WAITING
 990227   03-10-02  WAITING
 990228   03-08-02  WAITING
 990228   03-15-02  NOTIFIED

 I would get information back for
 this item:

 990227   03-08-02  WAITING

 Is there a good way to do this in one or
 two queries?

 Any tips would be most appreciated. Thanks
 in advance!

 Jeff Shipman   E-Mail: [EMAIL PROTECTED]
 Systems Programmer Phone: (505) 835-5748
 NMIMT Computer Center  http://www.nmt.edu/~jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Java to MySQL connection

[Bill Easton]

Try these:

Class.forName(org.gjt.mm.mysql.Driver);

con = DriverManager.getConnection
  (jdbc:mysql://localhost/Testing, my username, my
pass);

Make sure the jar file in the mm.mysql-2.0.11 directory is in your
classpath/
mm.mysql does NOT use the odbc driver.

 Subject: Java to MySQL connection
 Date: Wed, 20 Mar 2002 14:42:43 -0500
 From: Chris Stewart [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]

 I'm trying to make a connection from a java app to a MySQL database I've
 created on my local PC.  How can I tell the app where to look for the
 existing database?

 Code I'm working with:

 Class.forName(sun.jdbc.odbc.JdbcOdbcDriver).newInstance();
 Connection con =
 DriverManager.getConnection(jdbc:odbc:Testing@localhost:3306,  my
 username ,  my pass );
 System.out.println(Connection Made.);

 As you can tell from this, my database is named Testing.  All I'm
 trying to do right now is get a connection.  Right now when I run this
 code I get Error: java.sql.SQLException: [Microsoft][ODBC Driver
 Manager] Data source name not found and no default driver specified.
 I'm not sure I have the odbc driver setup correctly either.  I
 downloaded mm.mysql-2.0.11 but I'm not really sure how to bring it
 into the mix as far as the driver goes.

 Thanks for any direction.

 Chris Stewart
 [EMAIL PROTECTED]




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Sub-select look-alike?

[Bill Easton]

If I understand your question, you just need to join with the languages
table twice, using aliases:

select LF.language, LT.language
from language_pairs P, languages LF, languages LT
where LF.id = P.from and LT.id = P.to;

 From: =?iso-8859-1?Q?Andreas_Fr=F8sting?= [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Sub-select look-alike?
 Date: Wed, 13 Mar 2002 16:12:31 +0100

 Hi,

 I have two tables:

   languages:
 id tinyint(3) unsigned not null,
 language   varchar(30) not null

   language_pairs:
 from   tinyint(3) unsigned not null,
 to tinyint(3) unsigned not null

 language_pairs.from and language_pairs.to are linked with languages.id

 (both tables are simplified in this mail and contains a lot more
 columns, but they are not relevant)

 Now I want to do a query giving me the names of the languages
 (languages.language) instead of language_pairs.from og .to.

 I can only see a solution requiring the use of sub-selects which MySQL
 doesn't (yet :) has support for.
 Anyone smarter than me who can see a solution?

 I want to avoid use of sub-queries if possible, and my emergency plan is
 to extract all records from `languages` into an array in PHP and simply
 use PHP to join .from and .to with the matching language. That's not in
 any way optimal, that's why I'm asking you guys :)

 regards,

 //andreas
 http://phpwizard.dk (in Danish only)



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: foxpro xbase conversion

[Bill Easton]

I have some code I used to convert Foxpro to XML.  I've put it up for
anonymous ftp at ftp://lexpar.com/pub/foxpro_conversion.zip.
There's an executable, which requires only the Foxpro runtime dll's (which
are redistributable).  It does put out memo fields correctly.

I've included the source--all 12 lines.  If you compile, you'll need
wwipstuff available as shareware
download at http://www.west-wind.com/webconnection/

It was then fairly straightforward to convert it to the database with a
little Java program.

If you need help with finding the runtime stuff, or if you'd like the little
Java program to help get started, let me know.

Hope this is of some help.

 From: Neil Davies [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: foxpro xbase conversion
 Date: Mon, 11 Mar 2002 12:40:22 -

 Needing to convert some old foxpro tables into mysql. Have done this
before
 using a long winded xbase-text-mysql through access97, but it through out
Dr
 Watson errors with a couple of the tables, oddly enough a couple of
 important ones that I need. I was wondering if anyone knew of any
apps/perl
 modules that would perform this for me? Btw. I use WindowsNT4 and don't
have
 easy access to a linux machine.

 Thanks in advance,
 Neil


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SELECT this IF that

[Bill Easton]

You can do what you asked for by making a temporary table, as follows.

create temporary table T (this varchar(..), that varchar(..));
insert into T select tbl1.this, no record from tbl1 left join tbl2 on
tbl1.id = tbl2.id where tbl2.id is null;
insert into T select tbl1.this, tbl2.that from tbl1, tbl2 where tbl1.id =
tbl2.id;
insert into T select no record, tbl2.that from tbl2 left join tbl1 on
tbl2.id = tbl1.id where tbl1.id is null;

With a UNION, you can do it all in one SQL statement, but unions are on the
to do someday list for MySQL.

You probably can't do it in MySQL without a temporary table or some
procedural programming.

Hope this helps a little.

Date: Tue, 05 Mar 2002 13:56:34 -0500
From: Forer, Leif [EMAIL PROTECTED]
Subject: SELECT this IF that
To: '[EMAIL PROTECTED]' [EMAIL PROTECTED]
Message-id: [EMAIL PROTECTED]
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1

I want to select and join data from two tables if the same id exists in both
tables.  If the id only exists in one table I want MySQL to return a string
like sorry, no records found.  How can I do this?

I'm guessing it's something like:

mysql SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id =
tbl2.id, return the data, no record);

(Obviously that's not a real query).



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: help me with complicate sql query

[Bill Easton]

BS Wow, never would have come up with that, thanks very much for the help.
I'm half way done with a programming version rather than sql.  I will try
yours first.

BS I guess this would work if person_id 3 was also assigned to org 1  as
well as org 2, they wouldn't show up the final results?

Yes.  That's like person_id 4 in my example.

I don't know if you've got big tables, but I note that EXPLAIN tells me that
my query will generate a temporary table.  Of course, in any event, you want
an index on person_orgs.person_id.

If you are going to have a temporary table, anyway, it might be clearer to
do it explicitly:
First, create a temporary table containing the person_id's you DON'T want --
i.e., those assigned to org_id 1:

mysql create temporary table temp (person_id int not null);
Query OK, 0 rows affected (0.02 sec)

mysql insert into temp
- select p.person_id from persons p, person_orgs po
- where p.person_id = po.person_id and po.org_id = 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql select * from temp;
+---+
| person_id |
+---+
| 2 |
| 4 |
+---+
2 rows in set (0.00 sec)

Then, use an outer join to get the ones you do want:

mysql select p.person_id, p.name
- from persons p left join temp t
- on p.person_id = t.person_id
- where t.person_id is null;
+---++
| person_id | name   |
+---++
| 1 | unassigned |
| 3 | in org 2   |
+---++
2 rows in set (0.00 sec)

You'll want an index on temp.person_id.  You get to figure out which is
better for you.

Anyway, it was an interesting puzzle.  I came up with the first--and most
offbeat--solution, because I was looking for a one-SQL-statement solution.
Hope this helps.

  - Original Message -
  From: Brian Smith
  To: Bill Easton
  Sent: Saturday, March 02, 2002 8:51 AM
  Subject: RE: help me with complicate sql query


  I guess this would work if person_id 3 was also assigned to org 1  as well
as org 2, they wouldn't show up the final results?
  Brian/
-Original Message-
From: Bill Easton [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 01, 2002 7:46 PM
To: [EMAIL PROTECTED]
Cc: Brian Smith
Subject: Re: help me with complicate sql query


Well, it's possible, but it's not pretty.

The right way, of course, is to have subselects.  Ah, well, someday...

You can't do it using just joins (inner or outer) and where clauses.
The reason is that the joins will give you a cross product and the on
clauses and the where clauses will throw away some of the rows.  There
aren't any rows that indicate that a person is NOT a member of org_id = 1.

Here's a way of doing it using a GROUP BY clause.  We need some way of
getting a row for each person and some way to identify whether that row has
a person we want.  The trick--which works for a numeric column here--is to
group multiple rows by person and then look at the minimum absolute value of
(org_id - 1); this is zero precisely when the person is assigned to org_id -
1;

Here are the tables:

mysql select * from persons;
+---++
| person_id | name   |
+---++
| 1 | unassigned |
| 2 | in org 1   |
| 3 | in org 2   |
| 4 | in org 1 and 2 |
+---++
4 rows in set (0.00 sec)

mysql select * from person_orgs;
+---++
| person_id | org_id |
+---++
| 2 |  1 |
| 3 |  2 |
| 4 |  1 |
| 4 |  2 |
+---++
4 rows in set (0.00 sec)

Here's the first try, with just the GROUP BY.  Note that the rows we
want have either null or a non-zero value in mn.

mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn
- from persons p left join person_orgs po on p.person_id =
po.person_id
- group by p.person_id;
+---++--+
| person_id | name   | mn   |
+---++--+
| 1 | unassigned | NULL |
| 2 | in org 1   |0 |
| 3 | in org 2   |1 |
| 4 | in org 1 and 2 |0 |
+---++--+
4 rows in set (0.00 sec)

And here's the second try, where we use a HAVING clause to throw away
the rows we don't want:

mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn
- from persons p left join person_orgs po on p.person_id =
po.person_id
- group by p.person_id
- having mn is null or mn  0;
+---++--+
| person_id | name   | mn   |
+---++--+
| 1 | unassigned | NULL |
| 3 | in org 2   |1

Re: help me with complicate sql query

[Bill Easton]

Well, it's possible, but it's not pretty.

The right way, of course, is to have subselects.  Ah, well, someday...

You can't do it using just joins (inner or outer) and where clauses.  The
reason is that the joins will give you a cross product and the on clauses
and the where clauses will throw away some of the rows.  There aren't any
rows that indicate that a person is NOT a member of org_id = 1.

Here's a way of doing it using a GROUP BY clause.  We need some way of
getting a row for each person and some way to identify whether that row has
a person we want.  The trick--which works for a numeric column here--is to
group multiple rows by person and then look at the minimum absolute value of
(org_id - 1); this is zero precisely when the person is assigned to org_id -
1;

Here are the tables:

mysql select * from persons;
+---++
| person_id | name   |
+---++
| 1 | unassigned |
| 2 | in org 1   |
| 3 | in org 2   |
| 4 | in org 1 and 2 |
+---++
4 rows in set (0.00 sec)

mysql select * from person_orgs;
+---++
| person_id | org_id |
+---++
| 2 |  1 |
| 3 |  2 |
| 4 |  1 |
| 4 |  2 |
+---++
4 rows in set (0.00 sec)

Here's the first try, with just the GROUP BY.  Note that the rows we want
have either null or a non-zero value in mn.

mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn
- from persons p left join person_orgs po on p.person_id = po.person_id
- group by p.person_id;
+---++--+
| person_id | name   | mn   |
+---++--+
| 1 | unassigned | NULL |
| 2 | in org 1   |0 |
| 3 | in org 2   |1 |
| 4 | in org 1 and 2 |0 |
+---++--+
4 rows in set (0.00 sec)

And here's the second try, where we use a HAVING clause to throw away the
rows we don't want:

mysql select p.person_id, p.name, min(abs(org_id - 1)) as mn
- from persons p left join person_orgs po on p.person_id = po.person_id
- group by p.person_id
- having mn is null or mn  0;
+---++--+
| person_id | name   | mn   |
+---++--+
| 1 | unassigned | NULL |
| 3 | in org 2   |1 |
+---++--+
2 rows in set (0.00 sec)


 From: Brian Smith [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: help me with complicate sql query
 Date: Fri, 1 Mar 2002 10:48:20 -0500
 [...]

 Hello sql guru's, I need help find SQL to solve a problem:

 I have two tables, the first is persons:

 person_id int unsigned not null auto_increment
 firstname varchar(25)
 lastname varchar(25)

 The second is person_orgs

 person_id int unsigned not null
 org_id int unsigned not null

 A person may be assigned more than one org by using person_orgs table, but
 they can only be assigned to an org one time by way of application
 rules/logic.  Here is my problem:

 I need to select a list of persons that have not ever been assigned to an
 org OR have not been assigned to a specific org, such as org_id = 1, so I
 tried:

  select p.firstname,p.lastname from persons p left join person_orgs po on
 p.person_id = po.person_id where  (po.org_id  1 OR po.og_id IS NULL)

 I thought this was correct, and it does return persons never assigned
 before, but all it does concerning the specific org_id is to leave out the
 one record where the person is assigned to org_id = 1.  If the person is
 assigned to org_id =1 plus any additional, then the record for the other
 org(s) is/are returned, giving me a person I don't need.

 I have no idea how to do this.  It is almost as if I need to transform:

 person_id org_id
 11
 12
 1  3

 into:

 person_id org1 org2 org3
 1   1  2  3

 but I don't really know how in sql, and the number of orgs will be
different
 for each person.  I know I could build some large associative array in my
 language of choice and sort through it somehow, that just seems slow and
 cumbersome.

 I feel there is some SQL based solution, I just can't grasp it.

 Can anyone else?

 Thanks,
 Brian



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Emulating a sequence in MySQL?

[Bill Easton]

Yes, you can do this in MySql, given that you are doing it from a procedural
language.  You do a compare and swap, adding 1 to the old value, then do
an SQL update with a where clause that checks the old value.  You keep doing
this until you change a row.

Here's some sample code (in SQL and Java/JDBC)

create table XYZ (COUNTER int not null);
insert into XYZ set COUNTER = 0;

Connection con = ...
PreparedStatement doSelect = con.prepareStatement(select COUNTER from
XYZ);
PreparedStatement doUpdate = con.prepareStatement(update XYZ set COUNTER =
? where COUNTER = ?);
...
int newValue;
do {
ResultSet rs = doSelect.executeQuery();
rs.next();
int oldValue = rs.getInt(1);
rs.close();

newValue = oldValue + 1;
doUpdate.setInt(1,newValue);
doUpdate.setInt(2,oldValue);
} while (doUpdate.executeUpdate() == 0);
// -- newValue is the desired sequence number



 Subject: Emulating a sequence in MySQL?
 Date: Fri, 1 Mar 2002 10:37:57 -0500
 From: Richard Bolen [EMAIL PROTECTED]
 To: MySQL Mailing List (E-mail) [EMAIL PROTECTED]

 I need to have unique id's for every data element in my system no matter
 which table it's in.  In Oracle I can create a sequence and with one SQL
 query I can increment the value and retrieve it for use in my next
 insert.

 Can I do this in MySQL?  I know about AUTO INCREMENT but that appears to
 only work on a per table basis.  Another key requirement is being able
 to increment the value and retrieve it with one SQL call.

 I'm thinking that I can create a table with one column to represent my
 sequence.  The question I have is can I increment the value and retrieve
 it with one SQL statement?

 This may sound like a strange set of requirements but we're trying to
 get our app (a Java JDBC thing) to work across Oracle and MySQL without
 code changes.

 Thanks,
 Rich

 
 Rich Bolen
 Senior Software Developer
 GretagMacbeth Advanced Technologies Center
 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250
 PO Box 14026
 Research Triangle Park, North Carolina 27709-4026  USA
 Phone:  919-549-7575 x239,  Fax: 919-549-0421

 http://www.gretagmacbeth.com/
 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php