Re: Unique items from all columns, very slow

[Dan Nelson]

In the last episode (Apr 20), Willie Gnarlson said:
> I have been attempting to uncover a fast method for retrieving unique
> items from every column in a table. The table has approximately 60
> thousand rows. Each row has an index associated with it, and running
> 'explain select distinct class from dlist' shows that it is using the
> index.
>
> However, since there are 24 columns, running that select query for
> each column takes about 4 long seconds on a P4 2.4 Ghz.

If there are a significant number of identical values (ages, sizes,
etc), then setting PACK_KEYS=1 on the table may help by making smaller
indexes.  You might also be able to convert some columns to ENUMs,
which will make their indexes even smaller (and let your SELECT be
almost instantaneous if mysql knows how to optimize it right :)

Another alternative would be to build a table for each column, holding
the output of SELECT DISTINCT(column) FROM dlist.  Rebuild it as
frequently as needed.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Unique items from all columns, very slow

[Willie Gnarlson]

Hello,

I have been attempting to uncover a fast method for retrieving unique
items from every column in a table. The table has approximately 60
thousand rows. Each row has an index associated with it, and running
'explain select distinct class from dlist' shows that it is using the
index.

However, since there are 24 columns, running that select query for
each column takes about 4 long seconds on a P4 2.4 Ghz.

I've been unable to find any suitable leads through google or the list
archives.

Any cluebats will be gleefully appreciated. 

Thanks for reading!

-- 
Willie Gnarlson

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

Re: zip code search within x miles

[Jeff Kolber]

On 4/16/05, Scott Gifford <[EMAIL PROTECTED]> wrote:
> Jeff Kolber <[EMAIL PROTECTED]> writes:
> 
> > How are sites doing the search by zip and coming up with results within x
> > miles?  Is there some OSS zip code download that has been created for this?
> 
> The ones I'm familiar with use this:
> 
> http://www.census.gov/geo/www/tiger/zip1999.html
> 
> [...]
> 
> > I would be interested, anyone have insights into doing  with Canadian
> > Zip Codes or otherwise optimizing a database of Canadian Zip Codes?
> 
> I looked into this a few months back (for a very similar project,
> actually) and found many places offering Canadian postal code
> databases, but none for free.  If you already have the database, I
> don't see why optimizing it would be any different than for American
> postal codes.
> 
> ScottG.
> 

Just that there are supposedly 650,000+ canadian codes. Makes you want
to do whatever your doing in an optimal way.

Jeff Kolber

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

Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?

[Rich Carr]

Thank you so much Vivian!  Your first solution was exactly what I was looking 
for!  It works perfectly!
 
Thanks so much!
 
Richard

Vivian Wang <[EMAIL PROTECTED]> wrote:
create table temp select * from viewvisitor order by lastviewtime desc;
select app, itemid, ownerid, visitorid, vusername,lastviewtime, sum(viewcount) 
AS totalcount, itemname from temp where ownerid = 2 GROUP BY concat( app, 
itemid ) ORDER BY totalcount;

or
if you only care about max(lastviewtime), you can do this,
select app, itemid, ownerid, visitorid, vusername, max(lastviewtime), 
sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 
2 GROUP BY concat( app, itemid ) ORDER BY totalcount;

Rich Carr wrote:

>Is there a way to set which rows values are used by the GROUP BY clause for 
>the fields that are not in the GROUP BY clause?
> 
>In this following select statement the group by and order work but the value 
>of the lastviewtime field is not the value of the most recent datetime row. Is 
>there any way to modify the select statement so that the returned lastviewtime 
>field will have the most recent datetime? 
> 
>select app, itemid, ownerid, visitorid, vusername, lastviewtime, 
>sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 
>GROUP BY concat( app, itemid ) ORDER BY totalcount
> 
> 
>create table viewvisitor 
>(
> app char(15), 
> itemid INT UNSIGNED NOT NULL, 
> ownerid INT UNSIGNED NOT NULL, 
> ousername varchar(25), 
> visitorid INT UNSIGNED NOT NULL, 
> vusername varchar(25), 
> vfullname varchar(70), 
> lastviewtime DATETIME NOT NULL, 
> viewcount INT, 
> itemname VARCHAR(40), 
>
>
> PRIMARY KEY master(app, visitorid, itemid),
> INDEX (ownerid),
> INDEX (lastviewtime), 
> INDEX (viewcount)
>);
> 
>Thanks!!!
> 
>Rich
>
>
> 
>-
>Do you Yahoo!?
> Yahoo! Small Business - Try our new resources site! 
> 
>


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: Re: "use mysql" and segmentation fault

[Xu Hao]

Yes, I downloaded the 4.1 rpms from 
http://dev.mysql.com/downloads/mysql/4.1.html 

>Hello.
>
>
>
>You're sure that you are running an official binary? 
>
>
>
>
>
>"Xu Hao" <[EMAIL PROTECTED]> wrote:
>
>> Hi!
>
>> 
>
>> I installed the same rpms on a SuSE 9.2 box and tried to reproduce that 
>> problem, but that problem didn't happen. It seems that this problem has 
>> something to do with RHEL3, right?
>
>> 
>
>> Thanks.





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

Re: Table is full [SOLVED]

[Jason Dixon]

On Apr 19, 2005, at 1:35 PM, Jason Dixon wrote:
I'm trying to do an update to one of my tables, but I'm getting the 
"table  is full" error.  I've reviewed the manual 
(http://dev.mysql.com/doc/mysql/en/full-table.html), but nothing seems 
to apply.  I looked at the output of myisamchk, and everything looks 
fine.  The table file is only ~100M, and the partition has almost 4G 
of free space.  The proposed update would only add approx 1byte per 
row, so I just don't see how this query can cause a full table.  Can 
someone clue me in here?
This turned out to not be a problem with a full table at all.  I found 
that I was able to update rows at chunks of ~70 rows at a time.  
Anything larger than ~80 would result in the full table error.  I 
found the following bug which apparently was fixed in 4.0.13 (I am 
using 4.0.11):

http://bugs.mysql.com/bug.php?id=230
--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net

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

Moving databases between machines and server versions simultaneously

[Helen Schuilenburg]

I need to move my MySQL databases to another machine.
The current machine the databases are on is running MySQL version 3.23. The new 
machine is running 4.0. The machines have the same floating point format. The databases 
are *very* large (several hundred tables, most around 4MB, a couple over 4GB), so 
using mysqldump will be rather hard on the machine. Can I copy the .frm, .MYI, and 
.MYD files for MyISAM tables between the two machines as described in
  http://dev.mysql.com/doc/mysql/en/upgrading-to-arch.html
or because it is a MySQL version upgrade as well, do I need to move the 
databases by using mysqldump.

Thanks
Helen

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

Re: dependent subquery/correlated subquery

[SGreen]

James Nobis <[EMAIL PROTECTED]> wrote on 04/19/2005 
04:04:24 PM:

> The link below essentially means subquery support is useless for many
> traditional purposes.  You are stuck in a correlated subquery thus 
performance
> is the query being run out-to-in (subquery run for every row?) instead 
of
> in-to-out as desired.  Does anyone know of a work around for this? 
Apparantly
> the MySQL team refuses to even recognize this as a bug.
> 
> http://bugs.mysql.com/bug.php?id=9090
> 
> James Nobis
> Web Developer
> Academic Superstore
> 223 W. Anderson Ln. Suite A110, Austin, TX 78752
> Voice: (512) 450-1199 x453 Fax: (512) 450-0263
> http://www.academicsuperstore.com
> 

No, it just means that you are going to have to do some of the work by 
creating and destroying your own temp table. Here is a query similar to 
the one in the bug report that doesn't use any subqueries.

CREATE TEMPORARY TABLE tmpIDs
SELECT werber, count(1) freq
FROM table_3
GROUP BY werber
HAVING freq > 20;

SELECT *
FROM table_2 t2
INNER JOIN tmpIDs tmp
ON tmp.id = t2.id
LEFT JOIN table_1 t1
on t1.id = t2.id;

DROP TEMPORARY TABLE tmpIDs;

Sure, subqueries may be sub-optimal ways of forming certain queries in 
MySQL. I expect that from newer features. That doesn't mean they are 
broken (bugged). It just means that more work needs to happen to make them 
perform better. I agree with the analysis that this is a feature request 
("please optimize subquery evaluation...here is a test case ") rather 
than a bug. Until they get better, you may have to use the "old" way of 
computing those results (you know, the way we did it BEFORE subqueries 
were ever supported) and go through temp tables in order to get the 
performance you desire.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: mysql syntax

[gerald_clark]

Rich Brant wrote:
I forgot the important part: what I want is to filter on a userID in the
person table such as - 

SELECT u.Username, p.UserID
FROM Users u LEFT OUTER JOIN
 Person p ON u.UserID = p.UserID
WHERE (p.UserID = 5) OR
 (p.UserID IS NULL)
THis will return both the matching recs from the user table and NULLs from
the person table in sql server, but is what I cant get to work in mysql...
 

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

RE: mysql syntax

[SGreen]

OK, I don't see much wrong with this version either. 

Again I ask you:
Which version you are using?
Can you post some sample data and the incorrect results?

Here is a similar query that is working for me. This matches Projects with 
the people who have the resources to complete them (Suppliers). (If this 
data looks familiar to some readers, I also used these tables in an 
earlier thread):

CREATE TABLE people (
  name varchar(11) default NULL,
  rsrc varchar(15) default NULL
);

INSERT INTO people VALUES 
('noah','wood'),('noah','canvas'),('lincoln','wood'),('davinci','canvas'),('davinci','paint');

CREATE TABLE project (
  proj varchar(11) default NULL,
  rsrc varchar(15) default NULL
);

INSERT INTO project VALUES 
('ark','wood'),('ark','canvas'),('cabin','wood'),('monalisa','canvas'),('monalisa','paint'),('jeans','canvas'),('jeans','sewingmachine');

select * from people;

+-++
| name| rsrc   |
+-++
| noah| wood   |
| noah| canvas |
| lincoln | wood   |
| davinci | canvas |
| davinci | paint  |
+-++
5 rows in set (0.06 sec)

select * from project;

+--+---+
| proj | rsrc  |
+--+---+
| ark  | wood  |
| ark  | canvas|
| cabin| wood  |
| monalisa | canvas|
| monalisa | paint |
| jeans| canvas|
| jeans| sewingmachine |
+--+---+
7 rows in set (0.05 sec)

SELECT pr.proj, pr.rsrc,  p.name 
FROM project pr
LEFT JOIN people p
ON p.rsrc = pr.rsrc;

+--+---+-+
| proj | rsrc  | name|
+--+---+-+
| ark  | wood  | noah|
| ark  | wood  | lincoln |
| ark  | canvas| noah|
| ark  | canvas| davinci |
| cabin| wood  | noah|
| cabin| wood  | lincoln |
| monalisa | canvas| noah|
| monalisa | canvas| davinci |
| monalisa | paint | davinci |
| jeans| canvas| noah|
| jeans| canvas| davinci |
| jeans| sewingmachine | NULL|
+--+---+-+
12 rows in set (0.00 sec)

I get a NULL for person on the last line because nobody has a sewing 
machine to share for the "jeans" project.  To match your second query, I 
need to detect either a particular person ( I pick 'davinci') or a NULL.


SELECT pr.proj, pr.rsrc,  p.name 
FROM project pr
LEFT JOIN people p
ON p.rsrc = pr.rsrc
WHERE p.name='davinci'
or p.name is null;

+--+---+-+
| proj | rsrc  | name|
+--+---+-+
| ark  | canvas| davinci |
| monalisa | canvas| davinci |
| monalisa | paint | davinci |
| jeans| canvas| davinci |
| jeans| sewingmachine | NULL|
+--+---+-+
5 rows in set (0.00 sec)

Which is what we expect. Now, can you please show us what is wrong with 
your output?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"Rich Brant" <[EMAIL PROTECTED]> wrote on 04/19/2005 03:46:33 PM:

> I forgot the important part: what I want is to filter on a userID in the
> person table such as - 
> 
> SELECT u.Username, p.UserID
> FROM Users u LEFT OUTER JOIN
>   Person p ON u.UserID = p.UserID
> WHERE (p.UserID = 5) OR
>   (p.UserID IS NULL)
> 
> THis will return both the matching recs from the user table and NULLs 
from
> the person table in sql server, but is what I cant get to work in 
mysql...
> 
> 
> 
> 
> 
> 
> 
>   _ 
> 
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 19, 2005 3:14 PM
> To: Rich Brant
> Cc: mysql@lists.mysql.com
> Subject: Re: mysql syntax
> 
> 
> 
> 
> Rich Brant <[EMAIL PROTECTED]> wrote on 04/19/2005 03:05:51 PM:
> 
> > I'm used to doing something simple such as the following in sql 
server:
> > 
> > SELECT u.Username, p.UserID
> > FROM   Users u LEFT JOIN
> >Person p ON u.UserID = p.UserID
> > 
> > However, I'm not seeing the same results in MySQL.  I don't get all
> > the recs in the users table and NULLs in the userID column from the
> > person table when the users.userID is not in the person table.  I get
> > no recs at all.  What am I missing here?
> > 
> > Thanks!
> > 
> I don't see any obvious problems with your query. I use LEFT JOINs all 
the
> time. 
> 
> Which version MySQL are you using? Can you give us an example of what 
your
> base data looks like (Users table and Person table), and what you 
actually
> got as a response? 
> 
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
> 

dependent subquery/correlated subquery

[James Nobis]

The link below essentially means subquery support is useless for many
traditional purposes.  You are stuck in a correlated subquery thus performance
is the query being run out-to-in (subquery run for every row?) instead of
in-to-out as desired.  Does anyone know of a work around for this?  Apparantly
the MySQL team refuses to even recognize this as a bug.

http://bugs.mysql.com/bug.php?id=9090

James Nobis
Web Developer
Academic Superstore
223 W. Anderson Ln. Suite A110, Austin, TX 78752
Voice: (512) 450-1199 x453 Fax: (512) 450-0263
http://www.academicsuperstore.com

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

Re: zip code search within x miles

[Scott Haneda]

on 4/19/05 9:32 AM, Keith Ivey at [EMAIL PROTECTED] wrote:

> Hank wrote:
> 
>> Talk about over complicating things... here's the above query simplifed.
>> 
>> I can not figure out why they were self joining the table three times:
> 
> Also, the index on zip_code, latitude, and longitude doesn't
> make sense.  Only the zip_code part of it was used, so it should
> have been on zip_code alone.  Latitude and longitude were only
> used in calculations, so indexing them is useless.
> 
> If additional conditions were added to the WHERE to limit the
> search to a square, then latitude and longitude indexes would be
> useful, but they would have to be separate from each other and
> from zip_code.

Anyone willing to put up a final tested and optimized sql query?
-- 
-
Scott HanedaTel: 415.898.2602
 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: mysql syntax

[Rich Brant]

I forgot the important part: what I want is to filter on a userID in the
person table such as - 
 
SELECT u.Username, p.UserID
FROM Users u LEFT OUTER JOIN
  Person p ON u.UserID = p.UserID
WHERE (p.UserID = 5) OR
  (p.UserID IS NULL)
 
THis will return both the matching recs from the user table and NULLs from
the person table in sql server, but is what I cant get to work in mysql...
 
 
 
 
 


  _  

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, April 19, 2005 3:14 PM
To: Rich Brant
Cc: mysql@lists.mysql.com
Subject: Re: mysql syntax




Rich Brant <[EMAIL PROTECTED]> wrote on 04/19/2005 03:05:51 PM:

> I'm used to doing something simple such as the following in sql server:
> 
> SELECT u.Username, p.UserID
> FROM   Users u LEFT JOIN
>Person p ON u.UserID = p.UserID
> 
> However, I'm not seeing the same results in MySQL.  I don't get all
> the recs in the users table and NULLs in the userID column from the
> person table when the users.userID is not in the person table.  I get
> no recs at all.  What am I missing here?
> 
> Thanks!
> 
I don't see any obvious problems with your query. I use LEFT JOINs all the
time. 

Which version MySQL are you using? Can you give us an example of what your
base data looks like (Users table and Person table), and what you actually
got as a response? 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: mysql syntax

[SGreen]

Rich Brant <[EMAIL PROTECTED]> wrote on 04/19/2005 03:05:51 PM:

> I'm used to doing something simple such as the following in sql server:
> 
> SELECT u.Username, p.UserID
> FROM   Users u LEFT JOIN
>Person p ON u.UserID = p.UserID
> 
> However, I'm not seeing the same results in MySQL.  I don't get all
> the recs in the users table and NULLs in the userID column from the
> person table when the users.userID is not in the person table.  I get
> no recs at all.  What am I missing here?
> 
> Thanks!
> 
I don't see any obvious problems with your query. I use LEFT JOINs all the 
time.

Which version MySQL are you using? Can you give us an example of what your 
base data looks like (Users table and Person table), and what you actually 
got as a response?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

mysql syntax

[Rich Brant]

I'm used to doing something simple such as the following in sql server:

SELECT u.Username, p.UserID
FROM   Users u LEFT JOIN
   Person p ON u.UserID = p.UserID

However, I'm not seeing the same results in MySQL.  I don't get all
the recs in the users table and NULLs in the userID column from the
person table when the users.userID is not in the person table.  I get
no recs at all.  What am I missing here?

Thanks!

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

Re: zip code search within x miles

[Hank]

> No, those indexes were intentional.  If you read the section of the manual
> on optimizing queries, you will encounter a page that mentions what are
> known as "covering indexes".  The advantage to a covering index is that if
> your data is numeric and in the index, 

Except that the zip code field is not (and should not be) numeric, so
the qualification test fails.

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

group by problem

[Wakan]

Hi,
I've 2 tables of payments
1: payment emission (P: --> ID,ID_cli,tot)
2: part payments (A: --> ID,P_ID,val)
I'd like to retrieve all payment emissions, with all relative part 
payments(with a left join), for all ID_cli
and I'd like to calculate the rest to pay...like tot-SUM(val).
but I don't know how to obtain the subtotal of all the part payments 
reative to each payment,
so to calculate the remaining payment

this is my real query:
SELECT T.ID ID_tes, importo, tipo_doc, tot_doc,
tot_doc-SUM(importo) saldo,SUM(importo)
FROM mag_movtes T
LEFT JOIN mov_cas C ON T.ID=C.ID_tes AND verso='E' AND C.deleted=0
WHERE T.rif_CF='C' AND T.deleted=0 AND T.ID_CF=426
GROUP BY T.ID,C.ID
HAVING tot_doc-SUM(importo)!=0
these are the tables:
TABLE `mag_movtes`
  `ID` int(11) unsigned NOT NULL auto_increment,
  `data_mov` date default NULL,
  `rif_CF` set('C','F') default NULL,
  `ID_CF` int(11) unsigned default NULL,
  `ID_cau` tinyint(3) unsigned default NULL,
  `data_doc` date default NULL,
  `num_doc` tinytext,
  `tipo_doc` set('B','F','C','I','E') default NULL,
  `imp_doc` decimal(6,2) default NULL,
  `sconto` decimal(4,2) default NULL,
  `tot_doc` decimal(6,2) default NULL,
  `deleted` tinyint(1) unsigned NOT NULL default '0',
TABLE `mov_cas`
  `ID` int(10) unsigned NOT NULL auto_increment,
  `data` date NOT NULL default '-00-00',
  `rif_CF` enum('C','F') NOT NULL default 'C',
  `ID_CF` int(10) unsigned NOT NULL default '0',
  `ID_tes` int(10) unsigned NOT NULL default '0',
  `descr` tinytext,
  `importo` decimal(6,2) default NULL,
  `verso` enum('E','U') NOT NULL default 'E',
  `deleted` tinyint(1) NOT NULL default '0',

Re: zip code search within x miles

[SGreen]

Hank <[EMAIL PROTECTED]> wrote on 04/19/2005 01:33:51 PM:

> On 4/19/05, Keith Ivey <[EMAIL PROTECTED]> wrote:
> > Also, the index on zip_code, latitude, and longitude doesn't
> > make sense. 
> 
> Yeah - I didn't even notice the indexes in the table def (I used my
> own existing zip code table).  That table def and query were obviously
> created by someone pretty green with SQL.
> 
> -Hank
> 
No, those indexes were intentional.  If you read the section of the manual 
on optimizing queries, you will encounter a page that mentions what are 
known as "covering indexes".  The advantage to a covering index is that if 
your data is numeric and in the index, the engine can read the data 
DIRECTLY from the index and completely skip all of the disk operations 
required to read the information from the data file.

That index is a covering index and will make any query looking for just 
lat and long against a zip code extremely fast because the engine will not 
need to read the data file to get at the lat and long value because they 
are already in the index.

from: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html
++
In some cases, a query can be optimized to retrieve values without 
consulting the data rows. If a query uses only columns from a table that 
are numeric and that form a leftmost prefix for some key, the selected 
values may be retrieved from the index tree for greater speed:

SELECT key_part3 FROM tbl_name WHERE key_part1=1
++

I guess the person who wrote that query wasn't so green after all, eh? 
(BTW, I was not the author of the OP's query although there is a lot of 
irony in this reply ;-D  )

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Trimming down a MySQL installation

[cristopher pierson ewing]

I am installing MySQL on a server with limited space available and I'd 
like to minimize the footprint of the installed database.  is there 
information available on what parts of the installation I can safely 
delete without harming functionality of the database?

I'd love to hear recommendations from the experts out there before I just 
hack-and-slash through the mysql directory.

Thanks in advance,
Cris

Cris Ewing
CME and Telehealth Web Services
University of Washington
School of Medicine
Work Phone: (206) 685-9116
Home Phone: (206) 365-3413
E-mail: [EMAIL PROTECTED]
***
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql import or write my own Perl parser

[Eric Bergen]

Both load data infile and mysqlimport both support the ignore option.
If you are loading a file into a table with a unique index and
duplicate rows in the file any duplicates will be silently ignored.

On 4/19/05, newbie c <[EMAIL PROTECTED]> wrote:
> thanks for the reply!  I am not too concerned about cutting out the columns 
> as I may need to other information for later. 
> I was just wondering does it make a difference if both of the columns that 
> I am interested have entries that are NOT unique? 
> Also,  at what point does one need a parser or use of a hash? 
> thanks!
> 
> Eric Bergen <[EMAIL PROTECTED]> wrote: 
> awk is probably the best tool I can think of for cutting columns out
> of a text file. Something like
> awk -F \\t '{ print $2 "," $3 }' my_file
> could be used to pick the second and third column out of a file prior
> to importing it.
> 
> -Eric
> 
> On 4/18/05, newbie c wrote:
> > Hi,
> > 
> > I am about to create a database and there are a number of files that I
> need
> > to load into the database. They are tab delimited files. One of the files
> contains about 4 or 5 columns. I am only interested in the second and the
> third column right now but I will load the whole table. The values in the
> second column can occur more than once in the file.
> > As well the values in the third column can occur more than once in the
> file.
> > 
> > Another file that I want to load as a table into the databse only contains
> two
> > column and one column will be unique while the second column will have
> > duplicate values in the file.
> > 
> > My question is when should I use mysqlimport, or load data and when should
> I write my own Perl parser to help load the table?
> > What criteria would be needed to decide to read a file into a hash?
> > 
> > Also, if I decide to use mysqlimport is there anything I should watch out
> for?
> > 
> > thanks!
> > 
> > 
> > -
> > Post your free ad now! Yahoo! Canada Personals
> > 
> > 
> 
> 
> -- 
> Eric Bergen
> [EMAIL PROTECTED]
> http://www.ebergen.net
> 
> 
> 
> 
> Post your free ad now! Yahoo! Canada Personals
>  
> 
> 


-- 
Eric Bergen
[EMAIL PROTECTED]
http://www.ebergen.net

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

Table is full

[Jason Dixon]

I'm trying to do an update to one of my tables, but I'm getting the 
"table  is full" error.  I've reviewed the manual 
(http://dev.mysql.com/doc/mysql/en/full-table.html), but nothing seems 
to apply.  I looked at the output of myisamchk, and everything looks 
fine.  The table file is only ~100M, and the partition has almost 4G of 
free space.  The proposed update would only add approx 1byte per row, 
so I just don't see how this query can cause a full table.  Can someone 
clue me in here?

[EMAIL PROTECTED] root]# myisamchk -dv /var/db/mysql/pflabel/statstest.MYI
MyISAM file: /var/db/mysql/pflabel/statstest.MYI
Record format:   Packed
Character set:   latin1 (8)
File-version:1
Creation time:   2005-04-19 12:00:51
Recover time:2005-04-19 12:03:44
Status:  checked,analyzed
Auto increment key:  1  Last value:   2022514
Data records:  1911283  Deleted blocks: 0
Datafile parts:1911283  Deleted data:   0
Datafile pointer (bytes):4  Keyfile pointer (bytes):4
Datafile length: 104946052  Keyfile length:  74199040
Max datafile length:4294967294  Max keyfile length: 4398046510079
Recordlength:  550
table description:
Key Start Len Index   Type Rec/key Root  
Blocksize
1   1 4   unique  unsigned long  1  5110784 
  1024
2   260   255 multip. char packed stripped   12574 27400192 
  2048
3   535   8   multip. ulonglong 16 50799616 
  1024
4   543   8   multip. ulonglong186 74198016 
  1024

Thanks,
--
Jason Dixon
DixonGroup Consulting
http://www.dixongroup.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Segfault 5.0.4 with subquery of the form <> ANY ( inner query )

[James Nobis]

SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: root
To: mysql@lists.mysql.com
Subject: 5.0.4 (and 5.0.3) segfault when presented with a specific query
containing a subquery of the form <> ANY( inner query )

>Description:
0x8097e13 init_signals__Fv + 327
0x833c348 __md5_crypt_r + 312
0x82d729f uf_space_prespace_selected + 219
0x82d3410 _mi_calc_var_pack_key_length + 1044
0x8126676 innobase_mysql_prepare_print_arbitrary_thd + 10
0x8123574 repair__9ha_myisamP3THDR17st_mi_check_paramb + 196
0x81233d1 repair__9ha_myisamP3THDP15st_ha_check_opt + 389
0x81194cd
cost_group_min_max__FP8st_tableP6st_keyUiUiP8SEL_TREEP7SEL_ARGUlbT7PdPUl + 489
0x811db78
merge_buffers__FP13st_sort_paramP11st_io_cacheT1PUcP10st_buffpekN24i + 760
0x80e3754 make_cond_for_table__FP4ItemUxUx + 680
0x80e2839 end_send__FP4JOINP13st_join_tableb + 173
0x80e3754 make_cond_for_table__FP4ItemUxUx + 680
0x80e2839 end_send__FP4JOINP13st_join_tableb + 173
0x80e2584 join_read_last__FP13st_join_table + 128
0x80d3d6f exec__4JOIN + 2591
0x807eb7b exec__30subselect_single_select_engine + 379
0x807bc9e cleanup__21Item_maxmin_subselect + 18
0x807ca39
single_value_transformer__17Item_in_subselectP4JOINP12Comp_creator + 121
0x83aec41 transform__18Item_default_valuePM4ItemFP4ItemPc_P4ItemPc + 81
0x8064eb8 cleanup__17Item_in_optimizer + 0
0x80639c5 convert_constant_item__FP3THDP5FieldPP4Item + 1
0x804c44d val_bool__4Item + 33
0x8068ae4 val_int__12Item_cond_or + 32
0x80e28e5 end_send__FP4JOINP13st_join_tableb + 345
0x80e29a1 end_send_group__FP4JOINP13st_join_tableb + 37
0x80e29a1 end_send_group__FP4JOINP13st_join_tableb + 37
0x80e29a1 end_send_group__FP4JOINP13st_join_tableb + 37
0x80e29a1 end_send_group__FP4JOINP13st_join_tableb + 37
0x80e2584 join_read_last__FP13st_join_table + 128
0x80d3d6f exec__4JOIN + 2591
0x80d55b6
make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 1262
0x80d1c8f
prepare__4JOINPPP4ItemP13st_table_listUiP4ItemUiP8st_orderT6T4T6P13st_select_lexP18st_select_lex_unit
+ 1159
0x80a8a79 mysql_execute_command__FP3THD + 1813
0x80ae6db
add_field_to_list__FP3THDPc16enum_field_typesT1T1UiP4ItemT6P13st_lex_stringT1Pt4List1Z6StringP15charset_info_stUi
+ 435
0x80a7164 dispatch_command__F19enum_server_commandP3THDPcUi + 2216
0x80a6acf dispatch_command__F19enum_server_commandP3THDPcUi + 531
0x80a6084 handle_bootstrap + 180
0x8339afc __pthread_lock + 28
0x836350a _svcauth_des + 730

>How-To-Repeat:
Unfortunately this NDA'd so I cannot provide the table structure, query,
etc.  If the stack trace is unhelpful I will happily spend the time to try to
create a test case.
>Fix:
unknown

>Submitter-Id:  
>Originator:root
>Organization:
 
>MySQL support: none
>Synopsis:  Segfault with subquery
>Severity:  critical
>Priority:  high
>Category:  mysql
>Class: sw-bug
sw-bug
>Release:   mysql-5.0.4-beta-standard (MySQL Community Edition - Standard
(GPL))
>Server: /usr/local/bin/mysqladmin  Ver 8.41 Distrib 5.0.4-beta, for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  5.0.4-beta-standard-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 5 min 59 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 0  Flush tables: 1  Open
tables: 0  Queries per second avg: 0.006
>C compiler:2.95.3
>C++ compiler:  2.95.3
>Environment:

System: Linux dev2 2.4.29-vs1.2.10 #1 Thu Mar 24 23:28:44 CST 2005 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc' 
CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root   13 Apr 14 14:06 /lib/libc.so.6 ->
libc-2.2.5.so
-rwxr-xr-x1 root root  1153784 Jan  7 06:05 /lib/libc-2.2.5.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Community Edition - Standard (GPL)'
'--with-extra-charsets=complex' '--with-server-suffix=-standard'
'--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler'
'--disable-shared' '--with-cli

Re: zip code search within x miles

[Hank]

On 4/19/05, Keith Ivey <[EMAIL PROTECTED]> wrote:
> Also, the index on zip_code, latitude, and longitude doesn't
> make sense.  

Yeah - I didn't even notice the indexes in the table def (I used my
own existing zip code table).  That table def and query were obviously
created by someone pretty green with SQL.

-Hank

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

Re: zip code search within x miles

[Keith Ivey]

Hank wrote:
Talk about over complicating things... here's the above query simplifed.
I can not figure out why they were self joining the table three times:
Also, the index on zip_code, latitude, and longitude doesn't 
make sense.  Only the zip_code part of it was used, so it should 
have been on zip_code alone.  Latitude and longitude were only 
used in calculations, so indexing them is useless.

If additional conditions were added to the WHERE to limit the 
search to a square, then latitude and longitude indexes would be 
useful, but they would have to be separate from each other and 
from zip_code.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Slow performance?!

[Rafal Kedziorski]

Hi,
I wrote some weeks ago to the mailinglist with the same problem. The 
performance isn't still much better.

I try to find the optimal value for tmp_table_size (now set to 67108864), 
cause we have some problems. Sometimes I see, that the result data will be 
copied to tmp table:

Copying to tmp table | select distinct m.media_id from category_tree 
c_tree, media_2_category m2c, medi

This queries are slow. But in MySQLAdministrator 1.0.19 I see:
created_tmp_disk_tables = 0
created_tmp_files = 2315
created_tmp_tables = 98887
Why is MySQL copying the result set to tmp table? THe same query executed 
from command line is faaster than from our JBoss.

This query nees from command line 0,09-0,20 sec:
select
distinct m.media_id
from
category_tree c_tree,
media_2_category m2c,
media m,
media_2_partner m2p,
magix_product mp,
media_type_2_magix_product mt2mp,
media_file mf
where
c_tree.mandant_id = 2
and
c_tree.partner_id = 1
and
c_tree.category_tree_id = m2c.category_tree_id
and
m2c.media_id = m.media_id
and
m2p.media_id = m.media_id
and
m2p.partner_id = 1
and
mp.magix_product_id = 20
and
mp.magix_product_id = mt2mp.magix_product_id
and
m.media_type_id = mt2mp.media_type_id
and
mf.media_id = m.media_id
and
(mf.language_id = 4 or mf.language_id is null)
and
mf.media_file_quality_id = 4
and
(mf.videosignal is null or mf.videosignal = 'ntsc')
and
(c_tree.category_tree_id = 3533 or c_tree.parent_id = 3533 or 
c_tree.path like '3531/3533/%')
order by m2p.priority desc limit 36, 36

From JBoss 0,4-1,6 sec.:
2005-04-19 17:54:41,576 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:56:08,836 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 17:56:11,799 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1320 millis
2005-04-19 17:56:11,860 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1263 millis
2005-04-19 17:56:37,947 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 17:58:20,456 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 450 millis
2005-04-19 18:00:37,389 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 18:01:29,018 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 624 millis
2005-04-19 18:02:08,169 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:12,141 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 456 millis
2005-04-19 18:02:27,084 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 452 millis
2005-04-19 18:02:29,907 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 453 millis
2005-04-19 17:52:21,821 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 660 millis
2005-04-19 17:53:39,256 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 574 millis
2005-04-19 17:54:00,722 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 573 millis
2005-04-19 17:54:03,903 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 655 millis
2005-04-19 17:54:20,486 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 644 millis
2005-04-19 17:59:06,642 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 17:59:24,124 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 657 millis
2005-04-19 18:01:18,357 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 654 millis
2005-04-19 18:01:27,095 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 632 millis
2005-04-19 18:01:28,402 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 915 millis
2005-04-19 18:01:29,468 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 667 millis
2005-04-19 18:02:59,673 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 656 millis
2005-04-19 18:03:02,092 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1479 millis
2005-04-19 18:03:02,689 WARN  [net.mgx.component.database.MySQL] 
getMediaIdsForCategoryNode() slow query needed 1640 millis

In resultset are 34 long values.
The explain of the sql statement looks so (csv separated by |):
table|type|possible_keys|key|key_len|ref|rows|Extra
mp|const|P

Re: zip code search within x miles

[Hank]

Talk about over complicating things... here's the above query simplifed.

I can not figure out why they were self joining the table three times:

SELECT b.zip_code, b.state,
   (3956 * (2 * ASIN(SQRT(
   POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
   COS(a.lat*0.017453293) *
   COS(b.lat*0.017453293) *
   POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2) AS distance
FROM zips a, zips b
WHERE
   a.zip_code = '90210'
GROUP BY distance
having distance <= 5;


-Hank

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

Re: MySQL 5.0.4-beta has been released

[Fredrick Bartlett]

Here are the sequence of queries that executed before the server crashed...

CREATE TEMPORARY TABLE COMETTRACKER.DELIVERY_TRACKING_ETA (
PACKAGEID BIGINT NOT NULL,
RECID_CTASKDATA BIGINT ,
RECTYPE INT(11) ,
USERID INT(11) ,
TIMETAG1 DATETIME ,
TIMETAG2 DATETIME ,
P_DATA1 VARCHAR(20) ,
P_DATA2 VARCHAR(20) ,
SEQVALUE INT(11) ,
SEQUENCE_NUM_TRIP INT(11) ,
COMPANY_CD VARCHAR(6),
PRODUCT_CD CHAR(2),
IO_NUM VARCHAR(4),
BARCODE VARCHAR(25),
SEQCODE VARCHAR(6),
SEQCODE1 VARCHAR(6),
PRIMARY KEY (PACKAGEID),
UNIQUE RECIDTASKDATA(RECID_CTASKDATA),
KEY COCODE (COMPANY_CD, PRODUCT_CD)
)

INSERT INTO COMETTRACKER.DELIVERY_TRACKING_ETA(
PACKAGEID,
RECID_CTASKDATA,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
SEQUENCE_NUM_TRIP,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQCODE1)
SELECT
PACKAGEID,
RECID_CTASKDATA,
RECTYPE,
USERID,
TIMETAG1,
TIMETAG2,
P_DATA1,
P_DATA2,
SEQVALUE,
SEQUENCE_NUM_TRIP,
COMPANY_CD,
PRODUCT_CD,
IO_NUM,
BARCODE,
SEQCODE,
SEQCODE1
FROM COMETTRACKER.DELIVERY_TRACKING
WHERE TRIP_DATE = "20050519"
AND P_DATA1 = "14" 
AND USERID = "21234"
ORDER BY RECID_CTASKDATA

CREATE TEMPORARY TABLE COMETTRACKER.DELIVERY_TRACKING_SORT
SELECT
MIN(SEQVALUE) SEQVALUE,
COMPANY_CD,
PRODUCT_CD,
IO_NUM
FROM COMETTRACKER.DELIVERY_TRACKING_ETA
WHERE P_DATA1 = "14"
AND (P_DATA2 = "" OR P_DATA2 IS NULL)
GROUP BY COMPANY_CD
ORDER BY COMPANY_CD

UPDATE COMETTRACKER.DELIVERY_TRACKING_ETA D, 
COMETTRACKER.DELIVERY_TRACKING_SORT S SET
D.SEQVALUE = S.SEQVALUE
WHERE (D.P_DATA2 = "" OR D.P_DATA2 IS NULL)
AND (S.COMPANY_CD = D.COMPANY_CD
AND S.PRODUCT_CD = D.PRODUCT_CD
AND S.IO_NUM = D.IO_NUM)

This query below did not execute because "Server Had Gone Away"

UPDATE COMETTRACKER.DELIVERY_TRACKING D, COMETTRACKER.DELIVERY_TRACKING_ETA E 
SET
D.SEQVALUE = E.SEQVALUE
WHERE (E.PACKAGEID = D.PACKAGEID)







- Original Message - 
From: "Reggie Burnett" <[EMAIL PROTECTED]>
To: "Fredrick Bartlett" <[EMAIL PROTECTED]>
Cc: "MySQL General List" 
Sent: Tuesday, April 19, 2005 8:00 AM
Subject: Re: MySQL 5.0.4-beta has been released


> What were you doing when it failed?  What type of connections was it 
> handling?
> 
> >Hmmm, it ran for about two hours (Win32) then crashed. I don't receive any
> >error messages to post. Server just stops???
> >
> >- Original Message - 
> >From: "Martijn Tonies" <[EMAIL PROTECTED]>
> >To: "Joerg Bruehe" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> >Cc: "MySQL General List" ;
> ><[EMAIL PROTECTED]>
> >Sent: Tuesday, April 19, 2005 2:42 AM
> >Subject: Re: MySQL 5.0.4-beta has been released
> >
> >
> >  
> >
> >>>A new version of MySQL Community Edition 5.0.4-beta Open Source database
> >>>management system has been released.  This version now includes support
> >>>  
> >>>
> >>for
> >>
> >>
> >>>Stored Procedures, Triggers, Views and many other features.  It is now
> >>>available in source and binary form for a number of platforms from our
> >>>download pages at http://dev.mysql.com/downloads/ and mirror sites.
> >>>  
> >>>
> >>Woohoo, it works on Windows :-)
> >>
> >>One thing that it doesn't do yet though, is saving the view
> >>definition in a human readable way instead of that auto-generated
> >>gibberish :-)
> >>
> >>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: zip code search within x miles

[Eamon Daly]

Interestingly enough, I found another great circle
routine here:
http://www.meridianworlddata.com/Distance-Calculation.asp
and adapted it for MySQL like so:
SELECT
b.zipcode, b.state,
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * 
cos(b.longitude/57.2958 - a.longitude/57.2958)
) AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
3963.0 * acos(
sin(a.latitude/57.2958) * sin(b.latitude/57.2958) +
cos(a.latitude/57.2958) * cos(b.latitude/57.2958) * 
cos(b.longitude/57.2958 - a.longitude/57.2958)
) <= 5 # <-- Your target radius
GROUP BY distance

They both achieve similar results; anyone have a feel for
which is "better"?

Eamon Daly

- Original Message - 
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Scott Haneda" <[EMAIL PROTECTED]>; "MySql" 
Sent: Tuesday, April 19, 2005 10:20 AM
Subject: Re: zip code search within x miles


I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
from zipcodedownload.com (and note the index on zipcode,
latitude, and longitude:
CREATE TABLE `zipcodes` (
 `zipcode` char(5) NOT NULL default '',
 `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
 `city` char(45) NOT NULL default '',
 `city_type` enum('D','A','N') NOT NULL default 'D',
 `state` char(75) NOT NULL default '',
 `state_code` char(2) NOT NULL default '',
 `area_code` char(3) default NULL,
 `latitude` double(12,6) NOT NULL default '0.00',
 `longitude` double(12,6) NOT NULL default '0.00',
 KEY `city_state_code` (`zipcode`,`city`,`state_code`),
 KEY `position` (`zipcode`,`latitude`,`longitude`)
) TYPE=MyISAM
Here's the calculation to get zipcodes within 5 miles of
02134:
SELECT
b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 <= 5 # <-- Your target radius
GROUP BY distance
Takes about half a second on our box. Here's the explain:
*** 1. row ***
   table: a
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index; Using temporary; Using filesort
*** 2. row ***
   table: c
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index
*** 3. row ***
   table: b
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 70443
   Extra: Using where
In case you're curious about the number of zipcodes:
mysql> select count(*), count(distinct zipcode) from zipcodes;
+--+-+
| count(*) | count(distinct zipcode) |
+--+-+
|70443 |   42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.

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

RE: zip code search within x miles

[Scott Johnson]

Hi,

Just as a speed note! It's faster to calculate a square and find those
records and then drop the corners with the circle calculation.

Scott.

> -Original Message-
> From: Eamon Daly [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, April 19, 2005 11:20 AM
> To: Scott Haneda; MySql
> Subject: Re: zip code search within x miles
>
>
> I don't think anyone's replied with an actual great circle
> calculation. Here's our zipcode table, populated with data
> from zipcodedownload.com (and note the index on zipcode,
> latitude, and longitude:
>
> CREATE TABLE `zipcodes` (
>   `zipcode` char(5) NOT NULL default '',
>   `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
>   `city` char(45) NOT NULL default '',
>   `city_type` enum('D','A','N') NOT NULL default 'D',
>   `state` char(75) NOT NULL default '',
>   `state_code` char(2) NOT NULL default '',
>   `area_code` char(3) default NULL,
>   `latitude` double(12,6) NOT NULL default '0.00',
>   `longitude` double(12,6) NOT NULL default '0.00',
>   KEY `city_state_code` (`zipcode`,`city`,`state_code`),
>   KEY `position` (`zipcode`,`latitude`,`longitude`)
> ) TYPE=MyISAM
>
> Here's the calculation to get zipcodes within 5 miles of
> 02134:
>
> SELECT
> b.zipcode, b.state,
> (3956 * (2 * ASIN(SQRT(
>  POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
>  COS(a.latitude*0.017453293) *
>  COS(b.latitude*0.017453293) *
>  POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
>  AS distance
> FROM zipcodes a, zipcodes b, zipcodes c
> WHERE
> a.zipcode = '02134' AND # <-- Your starting zipcode
> a.zipcode = c.zipcode AND
> (3956 * (2 * ASIN(SQRT(
>  POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
>  COS(a.latitude*0.017453293) *
>  COS(b.latitude*0.017453293) *
>  POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
>  <= 5 # <-- Your target radius
> GROUP BY distance
>
> Takes about half a second on our box. Here's the explain:
>
> *** 1. row ***
> table: a
>  type: ref
> possible_keys: city_state_code,position,default_city
>   key: position
>   key_len: 5
>   ref: const
>  rows: 2
> Extra: Using where; Using index; Using temporary; Using filesort
> *** 2. row ***
> table: c
>  type: ref
> possible_keys: city_state_code,position,default_city
>   key: position
>   key_len: 5
>   ref: const
>  rows: 2
> Extra: Using where; Using index
> *** 3. row ***
> table: b
>  type: ALL
> possible_keys: NULL
>   key: NULL
>   key_len: NULL
>   ref: NULL
>  rows: 70443
> Extra: Using where
>
> In case you're curious about the number of zipcodes:
>
> mysql> select count(*), count(distinct zipcode) from zipcodes;
> +--+-+
> | count(*) | count(distinct zipcode) |
> +--+-+
> |70443 |   42471 |
> +--+-+
>
> Can't take credit for the SQL, by the way; I'm pretty sure I
> found it on Google.
>
> 
> Eamon Daly
>
>
>
> - Original Message -
> From: "Scott Haneda" <[EMAIL PROTECTED]>
> To: "MySql" 
> Sent: Friday, April 15, 2005 5:37 PM
> Subject: zip code search within x miles
>
>
> > How are sites doing the search by zip and coming up with
> results within x
> > miles?  Is there some OSS zip code download that has been created for
> > this?
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>


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

Re: zip code search within x miles

[Eamon Daly]

I don't think anyone's replied with an actual great circle
calculation. Here's our zipcode table, populated with data
from zipcodedownload.com (and note the index on zipcode,
latitude, and longitude:
CREATE TABLE `zipcodes` (
 `zipcode` char(5) NOT NULL default '',
 `zipcode_type` enum('S','P','U','M') NOT NULL default 'S',
 `city` char(45) NOT NULL default '',
 `city_type` enum('D','A','N') NOT NULL default 'D',
 `state` char(75) NOT NULL default '',
 `state_code` char(2) NOT NULL default '',
 `area_code` char(3) default NULL,
 `latitude` double(12,6) NOT NULL default '0.00',
 `longitude` double(12,6) NOT NULL default '0.00',
 KEY `city_state_code` (`zipcode`,`city`,`state_code`),
 KEY `position` (`zipcode`,`latitude`,`longitude`)
) TYPE=MyISAM
Here's the calculation to get zipcodes within 5 miles of
02134:
SELECT
b.zipcode, b.state,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 AS distance
FROM zipcodes a, zipcodes b, zipcodes c
WHERE
a.zipcode = '02134' AND # <-- Your starting zipcode
a.zipcode = c.zipcode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) *
COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2)
 <= 5 # <-- Your target radius
GROUP BY distance
Takes about half a second on our box. Here's the explain:
*** 1. row ***
   table: a
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index; Using temporary; Using filesort
*** 2. row ***
   table: c
type: ref
possible_keys: city_state_code,position,default_city
 key: position
 key_len: 5
 ref: const
rows: 2
   Extra: Using where; Using index
*** 3. row ***
   table: b
type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
rows: 70443
   Extra: Using where
In case you're curious about the number of zipcodes:
mysql> select count(*), count(distinct zipcode) from zipcodes;
+--+-+
| count(*) | count(distinct zipcode) |
+--+-+
|70443 |   42471 |
+--+-+
Can't take credit for the SQL, by the way; I'm pretty sure I
found it on Google.

Eamon Daly

- Original Message - 
From: "Scott Haneda" <[EMAIL PROTECTED]>
To: "MySql" 
Sent: Friday, April 15, 2005 5:37 PM
Subject: zip code search within x miles


How are sites doing the search by zip and coming up with results within x
miles?  Is there some OSS zip code download that has been created for 
this?

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

Re: MySQL 5.0.4-beta has been released

[Reggie Burnett]

What were you doing when it failed?  What type of connections was it 
handling?

Hmmm, it ran for about two hours (Win32) then crashed. I don't receive any
error messages to post. Server just stops???
- Original Message - 
From: "Martijn Tonies" <[EMAIL PROTECTED]>
To: "Joerg Bruehe" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "MySQL General List" ;
<[EMAIL PROTECTED]>
Sent: Tuesday, April 19, 2005 2:42 AM
Subject: Re: MySQL 5.0.4-beta has been released

 

A new version of MySQL Community Edition 5.0.4-beta Open Source database
management system has been released.  This version now includes support
 

for
   

Stored Procedures, Triggers, Views and many other features.  It is now
available in source and binary form for a number of platforms from our
download pages at http://dev.mysql.com/downloads/ and mirror sites.
 

Woohoo, it works on Windows :-)
One thing that it doesn't do yet though, is saving the view
definition in a human readable way instead of that auto-generated
gibberish :-)
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]
 


 


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

Re: MySQL 5.0.4-beta has been released

[Fredrick Bartlett]

Hmmm, it ran for about two hours (Win32) then crashed. I don't receive any
error messages to post. Server just stops???

- Original Message - 
From: "Martijn Tonies" <[EMAIL PROTECTED]>
To: "Joerg Bruehe" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: "MySQL General List" ;
<[EMAIL PROTECTED]>
Sent: Tuesday, April 19, 2005 2:42 AM
Subject: Re: MySQL 5.0.4-beta has been released


> > A new version of MySQL Community Edition 5.0.4-beta Open Source database
> > management system has been released.  This version now includes support
> for
> > Stored Procedures, Triggers, Views and many other features.  It is now
> > available in source and binary form for a number of platforms from our
> > download pages at http://dev.mysql.com/downloads/ and mirror sites.
>
> Woohoo, it works on Windows :-)
>
> One thing that it doesn't do yet though, is saving the view
> definition in a human readable way instead of that auto-generated
> gibberish :-)
>
> 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]
>


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

Second call: How many innodb_log_file and how many groups

[Duhaime Johanne]

Hello

I have sent this e-mail 2 weeks ago and did not get any answers. Please
allow me to give a second try.

I have Mysql 4.1.7 on Solaris / innodb tables
I have 2 innodb_log_files of 100M each. A group of statements (19000
insert) does that both logfiles are stamped with the same time. It might
be because one has just finished filled and the other start or it might
be because they both rotated and my data might be lost in a crash. To
prevent that, I could increase the files size but they are already big
enough and the recovery might take too long as it is said in the
documentation.

What I want to do, is to create 4 files of 50M instead of 2 files of
100M. In the documentation it is said that the number of files
"recommended" is 2. Is there a problem to have more? Just to confirm
that the "recommended" applies to "have more than one". Does more files
decrease the speed?

Second. Since there is a parameter "innodb_log_files_in_group" does it
means that I can have more than one group? If so, is the group concept
for duplicates of innodb_log_files that you would be kept on another
diks to reinforce the safety of the data?

And finally, if you are still there:

The innodb_buffer_log_buffer_pool_size should be +~80% of the memory
available. I have 4 gigs. Let's I fixed it at 3 gigs.
Now the innodb_log_files_size: "sensible values range" from 1MB to 1/n
of the size of the buffer pool (N =3D number of logs size)
If I use 2 logfiles I get 1.5 gigs.
if I use 4 logfiles I get 750 megs
Is not that too big for the recovery.

Thank you in advance

Johanne Duhaime

Re: mysql import or write my own Perl parser

[newbie c]

thanks for the reply!  I am not too concerned about cutting out the columns
as I may need to other information for later.
I was just wondering does it make a difference if both of the columns that 
I am interested have entries that are NOT unique?
Also,  at what point does one need a parser or use of a hash?
thanks!

Eric Bergen <[EMAIL PROTECTED]> wrote:
awk is probably the best tool I can think of for cutting columns out
of a text file. Something like
awk -F \\t '{ print $2 "," $3 }' my_file
could be used to pick the second and third column out of a file prior
to importing it.

-Eric

On 4/18/05, newbie c wrote:
> Hi,
> 
> I am about to create a database and there are a number of files that I need
> to load into the database. They are tab delimited files. One of the files 
> contains about 4 or 5 columns. I am only interested in the second and the 
> third column right now but I will load the whole table. The values in the 
> second column can occur more than once in the file.
> As well the values in the third column can occur more than once in the file.
> 
> Another file that I want to load as a table into the databse only contains two
> column and one column will be unique while the second column will have
> duplicate values in the file.
> 
> My question is when should I use mysqlimport, or load data and when should I 
> write my own Perl parser to help load the table?
> What criteria would be needed to decide to read a file into a hash?
> 
> Also, if I decide to use mysqlimport is there anything I should watch out for?
> 
> thanks!
> 
> 
> -
> Post your free ad now! Yahoo! Canada Personals
> 
> 


-- 
Eric Bergen
[EMAIL PROTECTED]
http://www.ebergen.net



-
Post your free ad now! Yahoo! Canada Personals

Re: table handle error

[gerald_clark]

prathima rao wrote:
hai,
when ever the power goes or the system restarts when im entring data from my
vb application to mysql 4 database
it gets corrupted and i loose some data at the end please can any one help
me in this
regards
prathima rao
 

Get a backup powersupply with shutdown software.
When the power goes out, stop entering data.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql_subscribe

[moemen saad eldeen]

i want to join mysql mail list


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

table handle error

[prathima rao]

hai,

when ever the power goes or the system restarts when im entring data from my
vb application to mysql 4 database
it gets corrupted and i loose some data at the end please can any one help
me in this

regards

prathima rao


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

WG: Xpath Support MySQL (directly stored XML data in MySQL)

[Thomas Nicolai]

Dear All,
 
I'm really happy to let you know that after a whole year of hard work 
the University of St.Gallen has developed a new research 
platform "Alexandria" based on MySQL and PHP.

http://www.alexandria.unisg.ch

I'm really would like to say thanks to MySQL AB who did support our 
work in a beautiful way especially Alexander Barkov. He has given us
the code of the NEW XPATH FUNCTIONS in MySQL even though itâs not 
yet part of the official code. So I'm really glad to tell you that 
we are the first users of his code and that Alexandria is worldwide 
the first platform which is based on Alexanders Xpath 
implementation. If someone is interested in more details donât be 
afraid to contact either Alexander or myself. For further 
information have a look at his presentation at the MySQL User 
Conference that is now being held:

http://mysql.r18.ru/~bar/myxml/

For now you can use two functions to work with directly stored XML 
data in MySQL - EXTRACTVALUE() and UPDATEXML(). For now we're 
running our database (MySQL 5.0.5dev) with more than 15'000 records 
including fulltext search and it works beautiful. So Alexander keep 
on going :-)
 
Kind Regards,
Thomas

Thomas Nicolai
=mcminstitute
 
UniversitÃt St. Gallen
Blumenbergplatz 9
CH-9000 St. Gallen
Switzerland

Re: Connections problem ...

[Rafal Kedziorski]

At 11:07 19.04.2005, Gleb Paharenko wrote:
Hello.
See:
  http://dev.mysql.com/doc/mysql/en/blocked-host.html
thx. I know this. But some user comments are interesting, while we are 
using nagios.

Regards,
Rafal

>  our MySQL server can handle 1000 connections. We have two JBoss
>  instances.
>  Each configured with max. 100 connections in pool. furthermore we use
>  php
>  which make on each call a connection to MySQL. After a while (some
>  days) we
>  get this:
>
>  java.sql.SQLException
>  MESSAGE: Data source rejected establishment of connection,  message
>  from
>  server: "Host 'omaapache2.bln.de' is blocked because of many
>  connection
>  errors.  Unblock with 'mysqladmin flush-hosts'"
>
>  STACKTRACE:
>
>  java.sql.SQLException: Data source rejected establishment of
>  connection,  message from server: "Host 'omaapache2.rz.de
>  ' is blocked because of many connection errors.  Unblock with
>  'mysqladmin
>  flush-hosts'"
>
>  I don't have any idea why this happens. We can connect to mysql from
>  other
>  computer or directly to make 'mysqladmin flush-hosts'.
>
>>  Can somebody give me some tips?

Rafal Kedziorski <[EMAIL PROTECTED]> wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

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

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

Re: Duplication of records within the same table.

[Jigal van Hemert]

From: "Lee Denny"
> site_id, page_id,header_text,main_text..
>
> I just need to duplicate existing records with a new site_id, so :
>
> 1,1,Hello,some text..
> 1,2,Hello Again, some more text...
>
> gets copied to
>
> 2,3,Hello,some text..
> 2,4,Hello Again, some more text...
>
> page_id is already auto_increment.
>
> I'm sure that an INSERT .. SELECT statement should do it, but I'm unsure
of
> the correct syntax.
>

INSERT INTO `` SELECT , NULL, `header_text`,
`main_text` FROM `` WHERE `site_id`=

NULL will let MySQL use the autoincrement...
Tested on 4.0.23-standard

Regards, Jigal.


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

Re: "use mysql" and segmentation fault

[Gleb Paharenko]

Hello.



You're sure that you are running an official binary? 





"Xu Hao" <[EMAIL PROTECTED]> wrote:

> Hi!

> 

> I installed the same rpms on a SuSE 9.2 box and tried to reproduce that 
> problem, but that problem didn't happen. It seems that this problem has 
> something to do with RHEL3, right?

> 

> Thanks.

> 

> Hao Xu

> 

> 

>>Hello.

>>

>>

>>

>>If you are able to reproduce this problem on another box with a similar

>>

>>configuration, I suggest you to report a bug. BTW mysql client has some

>>

>>problems already reported, see:

>>

>>

>>

>>  http://bugs.mysql.com/bug.php?id=9870

>>

> 

> 

> 

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: Access denied for user: 'apache@localhost' (Using password: NO)

[Gleb Paharenko]

Hello.





>  Could be that the connect command fails, then I start trying and trying, 

> and then the server blocks because it receives too many connections? Then 

> the error message would be the one that I'm getting?





I also thought about this and therefore I gave you that advice. What

is in MySQL error log?











"Jorge Cambra Aused" <[EMAIL PROTECTED]> wrote:

> 

>  I haven't tried it yet, but I'll try it next time it happens, to see if 

> the problema disappears.

> 

>  We actually have some connection problems that can be relevant to my 

> problem:

> 

> Warning: Lost connection to MySQL server during query in 

> /home/httpd/...some.php on line 9

> 

>  We solved them by the PHP code: Now we reconnecto to mysql if the 

> mysql_connect() command fails. If the connect command fails, then we try 10 

> more times.

> 

>  Could be that the connect command fails, then I start trying and trying, 

> and then the server blocks because it receives too many connections? Then 

> the error message would be the one that I'm getting?

> 

>  Thanx in advance,

>  Jorge

> 

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: Connections problem ...

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/blocked-host.html





>  our MySQL server can handle 1000 connections. We have two JBoss

>  instances. 

>  Each configured with max. 100 connections in pool. furthermore we use

>  php 

>  which make on each call a connection to MySQL. After a while (some

>  days) we 

>  get this:

>

>  java.sql.SQLException

>  MESSAGE: Data source rejected establishment of connection,  message

>  from 

>  server: "Host 'omaapache2.bln.de' is blocked because of many

>  connection 

>  errors.  Unblock with 'mysqladmin flush-hosts'"

>

>  STACKTRACE:

>

>  java.sql.SQLException: Data source rejected establishment of 

>  connection,  message from server: "Host 'omaapache2.rz.de

>  ' is blocked because of many connection errors.  Unblock with

>  'mysqladmin 

>  flush-hosts'"

>

>  I don't have any idea why this happens. We can connect to mysql from

>  other 

>  computer or directly to make 'mysqladmin flush-hosts'.

>

>>  Can somebody give me some tips?







Rafal Kedziorski <[EMAIL PROTECTED]> wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




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

Re: mysqldump

[Petar Nedyalkov]

On Tuesday 19 April 2005 13:17, Anna Henricson wrote:
> Hi,
> I have a problem with mysqldump when I want to copy a table from a
> database. I have mysql Ver 14.7, Distrib 4.1.9, for pc-linux-gnu.
>
> I use the following command:
>
> mysqldump -h host -u user -ppassword database_name table_name >
> table_name.sql
>
>
> And get the following error message:
>
> mysqldump: mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE="" */':
> Query was empty (1065)
>
> I've gotten some reponse that this may be due to a version mismatch between
> the client part and the database.
>
> The following output is given from the statement below:

Maybe you're using newer mysql client libraries and that's why your MySQL 3.23 
doesn't understand the command.

>
> mysql> SELECT VERSION();
> +---+
>
> | VERSION() |
>
> +---+
>
> | 3.23.45   |
>
> +---+
> 1 row in set (0.00 sec)
>
> Can anyone help me with this problem?
> Thanks!
>
> Regards Anna Henricson
>
>
> ---
> Anna Henricson, MSc, PhD student
> Center for Genomics and Bioinformatics (CGB)
> Karolinska Institutet
> S-171 77 Stockholm
> Sweden
> Phone: +46 (0)8 524 86030
> Fax: +46 (0)8 323950

-- 

Cyberly yours,
Petar Nedyalkov
Devoted Orbitel Fan :-)

PGP ID: 7AE45436
PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc
PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436


pgptIyUbjOKyb.pgp
Description: PGP signature

Using libmySQL with C/C++

[Vicente]

Hello,
 
Yesterday I asked for some help to create a program in C++ integrated with
MySQL. Now I have installed MySQL 4.1.11 and I suppose I will need to
include mysql.h in my code. But this header use 'libmysql.dll', where I have
to define the path in Visual C++? Or where I have to copy this file?
 
Thank you,
Vicente
 
 

Help with a DATETIME query please!

[shaun thornburgh]

Hi,
I am trying to create a query that returns a users availability for the next 
ten days using version 4.1.11. A user has 8.5 hours available per day so a 
users availability is equal to 8.5 less any bookings they have on that day. 
I have a table called Bookings (see below), and each 'Booking' has a start 
date and time and an end date and time. Dates for bookings don't have to be 
the same, i.e. a user could be on holiday for a week.

The follwing query produces an empty result:
SELECT
(8.5 - TIMEDIFF(B.Booking_End_Date, B.Booking_Start_Date)) AS 'Availability'
FROM Bookings B
WHERE B.User_ID = '1'
AND NOT (
CURDATE() < DATE_FORMAT(Booking_Start_Date, "%Y-%m-%d")
CURDATE() > DATE_FORMAT(Booking_End_Date, "%Y-%m-%d")
)
GROUP BY Availability
ORDER BY B.Booking_Start_Date;
Here is the Definition for the bookings table:
+-+-+--+-+-++
| Field   | Type| Null | Key | 
Default | Extra  |
+-+-+--+-+-++
| Booking_ID  | int(11) |  | PRI | 
NULL| auto_increment |
| Booking_Type| varchar(15) |  | | 
Unavailability  ||
| User_ID | int(11) |  | | 0 
  ||
| Project_ID  | int(11) | YES  | | 
NULL||
| Booking_Creator_ID  | int(11) | YES  | | 
NULL||
| Booking_Creation_Date   | datetime| YES  | | 
NULL||
| Booking_Start_Date  | datetime|  | | 
-00-00 00:00:00 ||
| Booking_End_Date| datetime|  | | 
-00-00 00:00:00 ||
| Booking_Status  | varchar(15) |  | | 
Other   ||
| Additional_Notes| text| YES  | | 
NULL||
+-+-+--+-+-++

Thanks for any advice offered here

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

mysqldump

[Anna Henricson]

Hi,
I have a problem with mysqldump when I want to copy a table from a database.
I have mysql Ver 14.7, Distrib 4.1.9, for pc-linux-gnu.

I use the following command:

mysqldump -h host -u user -ppassword database_name table_name >
table_name.sql


And get the following error message:

mysqldump: mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE="" */':
Query was empty (1065)

I've gotten some reponse that this may be due to a version mismatch between the 
client part and the database. 

The following output is given from the statement below:

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

Can anyone help me with this problem?
Thanks!

Regards Anna Henricson


---
Anna Henricson, MSc, PhD student
Center for Genomics and Bioinformatics (CGB)
Karolinska Institutet
S-171 77 Stockholm
Sweden
Phone: +46 (0)8 524 86030
Fax: +46 (0)8 323950


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

Re: Temporal databases & MySQL

[Daniel BODEA]

The model I described is indeed tailored more for tracking changes and
archiving historical records because those were the requirements of the
project I was implementing ;-) I just had to figure out the most optimal way
of addressing these requirements using MySQL 4.1.

MERGE tables are an excellent way of segmenting the history views to span
different storage containers and (I haven't tried this yet) packing the
tables that form the view might further speed things up while saving storage
space.

I was wondering though whether an incremental SQL storage engine existed in
open source, MySQL or otherwise. One capable of inserting a row in a table
based on some other row by storing only the fields that differ between the
two, and ideally performing diffs between large text fields. Any clues ?

Thank you,
Daniel

- Original Message - 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; 
Sent: Friday, April 15, 2005 5:23 PM
Subject: Re: Temporal databases & MySQL


What you describe makes sense and would certainly work, I don't know
that I would call it a temporal solution. The ENUM (I, U, D) seams a bit
redundant with time. This model resembles a traditional application log
or trace file, which is highly desirable for a records keeping system,
like for a phone company, or auto dealership. But if your looking for a
dynamic time based model to support systems that might track plants and
animals that have lived or live on earth that show extinction,
reintroduction and evolution...your stuck with start times and end times
marking valid entries and a large "WHERE clause" using "BETEEN st_date
AND en_date". If you stick with the T_HIST model you may want to check
out the MERGE tables, they can help segment your older histories while
still giving you a VIEW like access, assuming you can't go to the MySql
5 release.

Ed

-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Daniel BODEA
Sent: Saturday, April 09, 2005 8:46 PM
To: mysql@lists.mysql.com
Subject: Re: Temporal databases & MySQL

I was thinking about the following model for the application I'm working
on:

Any given table T holds the conventional data associated with instant
NOW,
no temporal data at all. There are tables T_HIST for every table T with
an
identical structure plus one date column which is set to NOW on every
insert
(these tables are only inserted into) and another ENUM column holding an
identifier for the operation (I, U or D). An index would be created over
both (date, operation).

An INSERT in T is duplicated in T_HIST. An UPDATE is first performed in
T
and the resulting row is INSERTed in T_HIST. A DELETE first copies the
column into T_HIST and the row is deleted from T.

As long as all T tables have PKs that are guaranteed not to be recycled,
I
suppose the benefits would be the following:

---
T tables can be kept compact and fast for all mundane operations.

Since the history can only grow and is logically separated, it can use
separate storage strategies better fit for much larger amounts of data
compared to tables T.

Temporal data remains a minimal addition while allowing for all (?)
temporal
queries to be performed. I initially thought there would be
circumstances
where queries would have to perform a join to the corresponding T tables
but
then the ENUM column should fully replace the join.

Index usage for temporal queries in the MySQL context should be optimal
when
using the date column as the main index on a table that is naturally
guaranteed to have this column ordered at all times.
---

Views and triggers would be simulated by the application which should
not be
too incumbent considering that the application needs to provide some
easy
means of changing the "querying instant" anyway.

I'm in no way favoring any temporal model if it's not for its ability to
perform best on a given SQL engine (MySQL in this case). Not being that
familiar with the inner workings of MySQL though, I can only submit the
module above to the attention of MySQL specialists who may have the time
to
post back their thoughts.

Thanks,
Daniel

"Daniel BODEA" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi Shawn,
>
> I really meant temporal and not temporary. Temporal as in TSQL2.
Databases
> that on the one hand accumulate all changes to data over time along
with
> accurate time information and on the other hand provide varying
degrees of
> transparency in querying this data based on the theory of instants and
> aggregated intervals of time.
>
> Most of the resources available online are largely academic though.
>
> Google :
> http://www.google.com/search?hl=en&q=temporal+database
>
> Troels' links has a good temporal databases section :
> http://troels.arvin.dk/db/rdbms/links/
>
> The TAU Project that has some experimental code for several engines of
which
> MySQL :
> http://www.cs.arizona.edu/tau/
>
> I need to use this fully in a project that uses MySQL 4.1.latest and
in a
> way that's independent of the struct

Duplication of records within the same table.

[Lee Denny]

Hello,

I've got a table with a simple structure:

site_id, page_id,header_text,main_text..

I just need to duplicate existing records with a new site_id, so :

1,1,Hello,some text..
1,2,Hello Again, some more text...

gets copied to

2,3,Hello,some text..
2,4,Hello Again, some more text...

page_id is already auto_increment.

I'm sure that an INSERT .. SELECT statement should do it, but I'm unsure of
the correct syntax.

cheers,

Lee



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

Re: MySQL 5.0.4-beta has been released

[Martijn Tonies]

> A new version of MySQL Community Edition 5.0.4-beta Open Source database
> management system has been released.  This version now includes support
for
> Stored Procedures, Triggers, Views and many other features.  It is now
> available in source and binary form for a number of platforms from our
> download pages at http://dev.mysql.com/downloads/ and mirror sites.

Woohoo, it works on Windows :-)

One thing that it doesn't do yet though, is saving the view
definition in a human readable way instead of that auto-generated
gibberish :-)

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 : Connections problem ...

[Rafal Kedziorski]

At 11:14 19.04.2005, prasanna a wrote:
Hi
It means that mysqld has received many connect
requests from that host.If the erro on connecting to
But than we should get to many connections.
mysqld exedes the parameter  max_connect_errors
How can there be an error?
(Default value is 10).
We change this value to 1000 at first.
mysqld assumes that something is wrong .So blocks the
host from further connections until you execute a
mysqladmin flush-hosts.
I'm checking how many connections we had at this time.
regards,
rafal 

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

Table structure not replicating

[Gavin Hamill]

Hi, we're running 4.1.11 on two Debian woody machines and replication on the 
'laterooms' database is working marvellously with the exception of altering 
table structure. If I execute an ALTER TABLE ... ADD... no error is 
generated, but the slave does not add the column.

Of course then INSERTS to the table fail due to the missing column and 
likewise ALTER TABLE DROP fails because the column doesn't exist on the 
slave :)

The permissions for the 'repl' user on the master are all "N" except for 
Repl_slave_priv. (I even tried adding Alter_priv for the repl user but it 
made no difference)

MASTER
server-id   = 1
binlog-do-db= laterooms
mysql> show master status;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.47 | 21195154 | laterooms|  |
+--+--+--+--+
1 row in set (0.00 sec)

SLAVE
server-id   = 2
binlog-do-db= laterooms

mysql> show slave status \G
*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: meg
Master_User: repl
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.47
Read_Master_Log_Pos: 21183485
 Relay_Log_File: liz-relay-bin.05
  Relay_Log_Pos: 76813
  Relay_Master_Log_File: mysql-bin.47
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 21183485
Relay_Log_Space: 76813
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Any ideas would be warmly welcomed!

Cheers,
Gavin.


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

Connections problem ...

[Rafal Kedziorski]

Hi,
our MySQL server can handle 1000 connections. We have two JBoss instances. 
Each configured with max. 100 connections in pool. furthermore we use php 
which make on each call a connection to MySQL. After a while (some days) we 
get this:

java.sql.SQLException
MESSAGE: Data source rejected establishment of connection,  message from 
server: "Host 'omaapache2.bln.de' is blocked because of many connection 
errors.  Unblock with 'mysqladmin flush-hosts'"

STACKTRACE:
java.sql.SQLException: Data source rejected establishment of 
connection,  message from server: "Host 'omaapache2.rz.de
' is blocked because of many connection errors.  Unblock with 'mysqladmin 
flush-hosts'"

I don't have any idea why this happens. We can connect to mysql from other 
computer or directly to make 'mysqladmin flush-hosts'.

Can somebody give me some tips?
regards,
rafal 

Re: InnoDB Performance

[Jigal van Hemert]

From: "David Lloyd"
> journalling file system. It's not always that clear cut. I've just
> switched a number of big customer databases to InnoDB and noone's
> noticed any difference - if anything it's going faster.

For small tables (<50,000 records) MyISAM is usually a lot faster. However,
MyISAM gets slower as the table size increases if it is used in a medium to
high concurrency environment. If you only use selects (low concurrency)
MyISAM will probably be fast, but when it comes to large tables with lots of
select, update and insert queries you will most likely see that the speed of
InnoDB remains roughly constant as the database grows.
You will see a slower performance as :
- InnoDB runs out of table space and has to autoextend the data file(s)
- the conf file is not suitable for the db size anymore
- the hardware is not suitable for the db size anymore (e.g. not enough
memory)
But this is not really InnoDBs fault ;-)

Regards, Jigal.


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

Re: Access denied for user: 'apache@localhost' (Using password: NO)

[Jorge Cambra Aused]

 I haven't tried it yet, but I'll try it next time it happens, to see if 
the problema disappears.

 We actually have some connection problems that can be relevant to my 
problem:

Warning: Lost connection to MySQL server during query in 
/home/httpd/...some.php on line 9

 We solved them by the PHP code: Now we reconnecto to mysql if the 
mysql_connect() command fails. If the connect command fails, then we try 10 
more times.

 Could be that the connect command fails, then I start trying and trying, 
and then the server blocks because it receives too many connections? Then 
the error message would be the one that I'm getting?

 Thanx in advance,
 Jorge

From: Gleb Paharenko <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: 
NO)
Date: Mon, 18 Apr 2005 18:38:54 +0300

Hello.
It is strange that refusing message says you're not using passwords.
Does the problem disappear after the "FLUSH HOSTS" statement, instead of
restarting MySQL server?

"Jorge Cambra Aused" <[EMAIL PROTECTED]> wrote:
>
>  I have a web application running with Apache with PHP 4.1.2 (revision
> 7.2.6) and MySQL 3.23.58.
>
>  The access from the PHP code to the MySQL server using the IP of the
> machine with MySQL, an username and a password, and it works fine.
>
>  But sometimes we get the MySQL error:
>
> Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
>
>  All the connections to the database fails for this reason, and we are 
sure
> that we are passing correctly the username, password and IP address. If 
we
> restart MySQL, then the problems disappears, and it works fine, but we 
don't
> know why it happens.
>
>  The username that we are using to access MySQL is not 'apache', apache, 
as
> you can guess, is the name of the user that is running Apache.
>
>  Thanx in advance,
>  Jorge Cambra
>
> _
> Descarga gratis la Barra de Herramientas de MSN
> 
http://www.msn.es/usuario/busqueda/barra?XAPID=2031&DI=1055&SU=http%3A//www.hotmail.com&HL=LINKTAG1OPENINGTEXT_MSNBH
>
>

--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com

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

_
Descubre la descarga digital con MSN Music. Más de medio millón de 
canciones. http://music.msn.es/

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

Re: Running mysql on a headless Windows CE Device

[Dixie]

I'd hate to use Microsoft's SQL server, but I feel like i've hit a
dead end with mysql.
If anyone can offer some advice on how to get mysql running on windows
ce, or can point me to some programmers who would be willing to port
the source code for me, please contact me.
-LiquidIce
[EMAIL PROTECTED]
 

Hi, that's isn't the solution but if you have a Ipoq HP... you can try 
to install a linux embedded distro (like familiar or OpenZaurus) and the 
mysql binaries for thats OS.
I'm running mysql on Zaurus 6000L sync to Debian & SuSe server and a 
winXp pro trought SQLYog.
Also works fine.

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