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 voi
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?
I
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 ha
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 sa
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
) 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]
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
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
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 readi
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
9, 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
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(pu
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 .FR
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 Inn
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 th
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 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
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 membe
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 tha
rting 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
>
> 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:
>
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 wh
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) ord
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_recor
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
e 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
You can suppress writing the delete query to the binary log.
mysql>set sql_log_bin = 0;
mysql>delete ... ;
mysql>set 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 del
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
re
terval 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]
> D
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
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);
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:3
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 t
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_
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 ca
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
patch
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 th
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 <[EMAI
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 retu
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
o
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 w
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
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
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 fof,pub
> 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
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 th
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
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 not
;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
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)
be
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
> an
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
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
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
duplic
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(dis
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> c
"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
> du
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 * f
k" <[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:
>
&
VR>Date: Mon, 28 Oct 2002 12:35:01 +0200
VR>From: Victoria Reznichenko <[EMAIL PROTECTED]>
VR>Subject: re: database corrupted after power switched off
VR>Tom,
VR>Monday, October 28, 2002, 11:59:16 AM, you wrote:
VR>TT> is it a normal behaviour that a sql databases gets corrupted if the
power of
V
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
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]>
> Sub
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
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
;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
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.
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
Al
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]>
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 jo
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 inefficie
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?
> [...
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
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
> 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 &#
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:\Re
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 PROTECT
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
> tra
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
Try these:
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection
("jdbc:mysql://localhost/Testing", , );
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 M
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]>
> T
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
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 tbl
he 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 per
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 J
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 a
88 matches
Mail list logo
