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
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?
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
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
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:
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
) 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
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
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
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
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
. 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
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
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
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
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
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
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
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
.
. . 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
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
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
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)
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
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
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
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
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
, 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
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.
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
, 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
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
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
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
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
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
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
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
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
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
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
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
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 |
+--+
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
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
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
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
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
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
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)
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
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
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
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
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
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
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
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 *
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
]
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
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
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
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]
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
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
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
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
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:
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
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
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
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
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
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
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
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
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?
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
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
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
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.
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:
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
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
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
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
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
88 matches
Mail list logo
