Re: Importing Excel Data in MySql

[Bertrand]

Thanks to all for your suggestions
just have to try all now

Thanks!
 --
 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]

Cloned machine mysql startup problem with innodb

[Paul W]

For backup purposes I have what amounts 
to a complete clone of a live server. 
During the process of copying 
everything, the MySQL data was copied 
over in its entirety. My problem is that 
the mysqld won't start now on this 
second machine. I don't need the 
databases to be preserved here (intend 
to rebuild them later then dump data 
into them). Therefore I can delete the 
database files themselves and start 
again if need be, but I'm not sure what 
would be a correct way to do this with 
the innodb error shown below. Somebody 
know if it's safe to delete most things 
from /var/lib/mysql? Could I just delete 
libdata1?

As to the line saying I should edit 
innodb_data_file_path back to what it 
was - I have no idea what it was  :-(

I'm after the simplest way to get it 
started, the rest is easy.

from the startup errors:
InnoDB: Error: data file 
/var/lib/mysql/ibdata1 is of a different 
size
InnoDB: 6656 pages (rounded down to MB)
InnoDB: than specified in the .cnf file 
128000 pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data 
files, and it failed here,
InnoDB: you should now edit 
innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the 
new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB 
only wrote those files full of
InnoDB: zeros, but did not yet use them 
in any way. But be careful: do not
InnoDB: remove old data files which 
contain your precious data!
041019 13:05:25  Can't init databases
041019 13:05:25  Aborting

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

Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Sergei Golubchik]

Hi!

On Oct 06, Christopher L. Everett wrote:
 I've also found a problem with myisamchk --sort-keys:

I recently fixed one bug in myisamchk --sort-keys where fulltext indexes
are present. Try 4.1.7 release.

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

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

Where is 4.1.6 max 32 bit binary for Solaris?

[Rick Robinson]

 
Is there any plan to release a 32-bit binary 4.1.6 MySQL max for Solaris 9 and
Solaris 8?  I see all the other binaries for Solaris for everything - any reason
to exclude the 32 bit?
 
Thx,
R

Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes

[Sergei Golubchik]

Hi!

On Oct 06, Christopher L. Everett wrote:
 I have an application where I create a faily large table (835MB) with a
 fulltext index.  One of our development workstations and our production
 server will run the script to load the table, but afterwards we have a
 pervasive corruption, with out of range index index pointer errors. 
 Oddly, my development workstation doesn't have those problems.
 
 My box and the ones having the problems have the following differences:
 
  - my box runs ReiserFS, the problem boxes run XFS
  - my box has a nice SCSI HD subsystem, the problem boxes do IDE.
 
 All three boxes run Linux 2.6.x kernels, and my workstation and production
 server share the same mobo.  Come to think of it, I saw similar corruption
 issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the
 show stopper it is now.

Could you try to repeat the problem with the smaller dataset ?
Create a repeatable test case for us ?
 
 Also, on all three boxes, altering the table to drop an index and create
 a new one requires a myisamchk -rq run afterwards when a fulltext index
 either exists or gets added or dropped, which I'd also call a bug.

Sorry, I don't understand. Could you elaborate ?

Regards,
Sergei

-- 
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik [EMAIL PROTECTED]
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
   ___/  www.mysql.com

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

A Complicated DATETIME query using 3.23.54

[shaun thornburgh]

Hi,
I have a table called Bookings which holds information for bookings(!) for 
staff members in my database:

mysql DESCRIBE Bookings;
+---+-+--+-+++
| Field | Type| Null | Key | Default| Extra  
|
+---+-+--+-+++
| Booking_ID| int(11) |  | PRI | NULL   | 
auto_increment |
| User_ID   | int(11) |  | | 0  |
|
| Booking_Start_Date| datetime| YES  | | NULL   |
|
| Booking_End_Date  | datetime| YES  | | NULL   |
|
+---+-+--+-+++

I use the following query to extract how many hours have been worked ona a 
particular day:

SELECT (SUM(((DATE_FORMAT(B.Booking_End_Date, %k) * 60 ) +
 DATE_FORMAT(B.Booking_End_Date, %i)) -
   ((DATE_FORMAT(B.Booking_Start_Date, %k) * 60 ) +
 DATE_FORMAT(B.Booking_Start_Date, %i))) / 60 ) AS 
Available_Hours
FROM Bookings B
WHERE B.User_ID = 1
AND NOT ( 2004-10-25  DATE_FORMAT(Booking_Start_Date, %Y-%m-%d)
OR 2004-10-25  DATE_FORMAT(Booking_End_Date, %Y-%m-%d) )

Is it possible to group this information by day for the next seven days?
Thanks for your help

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

Re: Partial Row Reads?

[Tom Cunningham]

I think what Harald is saying ( if he's not, then I say this):

You could have an alternative table structure like this: - it should
make queries much quicker:


create table raddata_2004_10_ONE (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 c1   float not null,
...
 c500float not null,
)

create table raddata_2004_10_TWO (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 c501   float not null,
...
 c1000float not null,
)

But maybe with a more logical way of partitioning the columns among
the different tables.

Or, another option, perhaps you've thought of, have a table like this:
should make indexing much worse, but querying much better.

create table raddata_2004_10_TWO (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 cID   smallint not null,
 cValue   float not null,
  primary key (granID, scanID, fpID, cID)
  index (granID, cID)
)

OLD QUERY:
Select c1 from raddata_2004_10 where granID between 147568 and 15

NEW QUERY:
Select cValue from raddata_2004_10 where granID between 147568 and
15 and cID=1;
(should be v. fast)

--- incidentally: I have a question: when you have a table like this
with a primary key which has a lot of columns, is there any
performance benefit to adding a new primary key, as an auto-increment
column,  keeping the old primary key as a unique index? I thought
maybe there might be some addressing / hashing issues which worked out
quicker?

Tom.

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

Re: Not Unique Error

[SGreen]

You are INNER JOINING the country table to the query twice without giving 
the query separate aliases for each. Also, you are providing only one set 
of join conditions so you are going to make a Cartesian product with one 
of those joins.

Looks to me like you only need the table once. Here's my suggestion:

SELECT users.UserName, users.userID, users.lastip, users.totallogins, 
users.lastbrowser, 
users.lastlogin, users.Password, users.person, users.entryid, 
users.namerep, users.countryid, 
country.countryid, country.image, 
country.countryid, country.country
FROM  Users INNER JOIN Country ON country.countryid = users.countryid 
WHERE UserName  = 'angelica'
AND  Password  = '4598734'

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

EWA Goodson-Wickes [EMAIL PROTECTED] wrote on 10/23/2004 
09:32:24 PM:

 Hi all,
   I am using Navicat to connect to my MySQL database. I get the 
 following error when I try a query even though there is only one 
 table called Country. Why do I get this error? Thank you for any advice
 
 Error: 1066 Not Unique Table/Alias Country 
 
 QUERY:
 SELECT users.UserName, users.userID, users.lastip, users.
 totallogins, users.lastbrowser, 
 users.lastlogin, users.Password, users.person, users.entryid, users.
 namerep, users.countryid, 
 country.countryid, country.image, 
 country.countryid, country.country
 FROM  Users, Country INNER JOIN Country ON country.countryid = 
 users.countryid 
 WHERE UserName  = 'angelica'
 AND  Password  = '4598734'

Re: Partial Row Reads?

[Ken Gieselman]

Quoting Tom Cunningham [EMAIL PROTECTED]:
I think what Harald is saying ( if he's not, then I say this):
You could have an alternative table structure like this: - it should
make queries much quicker:
create table raddata_2004_10_ONE (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 c1   float not null,
...
 c500float not null,
)
create table raddata_2004_10_TWO (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 c501   float not null,
...
 c1000float not null,
)
But maybe with a more logical way of partitioning the columns among
the different tables.
Yeah, we looked at doing something like this.  It would make the tables 
smaller,
though queries would be much more painful.  Unfortunately, there's not really
any common pattern for access to the various detector values.  Each of the
people analyzing the data has a different set that they like to work with,
depending on the information they're trying to extract.  The merge 
tables would
also require MASSIVE numbers of filehandles, since it would add another 5x to
the eventual number of tables (all of the tables are aggregated over time via
merge tables for the users).

Or, another option, perhaps you've thought of, have a table like this:
should make indexing much worse, but querying much better.
create table raddata_2004_10_TWO (
 granID   integer not null,
 scanID   tinyint unsigned not null,
 fpID tinyint not null,
 cID   smallint not null,
 cValue   float not null,
  primary key (granID, scanID, fpID, cID)
  index (granID, cID)
)
OLD QUERY:
Select c1 from raddata_2004_10 where granID between 147568 and 15
NEW QUERY:
Select cValue from raddata_2004_10 where granID between 147568 and
15 and cID=1;
(should be v. fast)
Interesting :)  It would make reads a lot faster, but I see a couple of
downsides.  First, it means that I'm duplicating the 6 bytes worth of 
ID fields
(plus the row/index overhead) a LOT more -- the main problem with this 
setup is
the volume of data that I'm trying to address.  With 2500 columns, I get 12150
rows per granule, or just short of 3 million rows a day.  With the structure
you suggested, I'd get 7.29 billion rows a day... wonder how the mysql engine
would handle 235 Billion rows per month in a table.  Add the extra overhead,
and I don't think we could manage the storage requirements (it works out to
just under 10TB a year as it is now, with the other tables) -- works out to
2.625 TB a month for just the one table this way, unfortunately.

--- incidentally: I have a question: when you have a table like this
with a primary key which has a lot of columns, is there any
performance benefit to adding a new primary key, as an auto-increment
column,  keeping the old primary key as a unique index? I thought
maybe there might be some addressing / hashing issues which worked out
quicker?
Interesting idea.  Not sure what the gain would be, at least in this case,
however.  Most of the queries are based on channel values, or other data like
geolocation or time, and just related to this table by the 3 id fields. 
 I'd be
willing to give it a shot, but not sure what an autoincrement field would gain
when the queries aren't based on insert-order.

Thanks for the insight!  Appreciate all the suggestions that you guys are
throwing into the hat!
ken
===
  Diplomacy is the weapon of the Civilized Warrior
- Hun, A.T.
Ken Gieselman  [EMAIL PROTECTED]
System Administrator   http://www.endlessknot.com/~kgieselm
Endlessknot Communications   http://www.endlessknot.com
===

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

Error connecting to mysql db

[Ferguson, Michael]

G'Day All,

When I try to connect to my mysql/php through Apache I get the following
error:

Warning: mysql_pconnect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/taxes/db_mysql.php on line 99
Database error: pconnect(localhost:3306, root, $DBPassword) failed.
MySQL Error: 0 ()
Session halted.


Can someone please help me to determine what's wrong here.

Thanks very much
  
Ferg

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

RE: Error connecting to mysql db

[J.R. Bullington]

This is usually one of two things...

1) MySQL is not running on that machine
2) Permissions for accessing the socket via PHP.

More likely this first, but try both. Check you [HOST].err file to be sure.

J.R.

-Original Message-
From: Ferguson, Michael [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 11:57 AM
To: [EMAIL PROTECTED]
Subject: Error connecting to mysql db


G'Day All,

When I try to connect to my mysql/php through Apache I get the following
error:

Warning: mysql_pconnect(): Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (13) in /var/www/html/taxes/db_mysql.php on line 99 
Database error:
pconnect(localhost:3306, root, $DBPassword) failed.
MySQL Error: 0 ()
Session halted.


Can someone please help me to determine what's wrong here.

Thanks very much
  
Ferg

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



smime.p7s
Description: S/MIME cryptographic signature

RE: Error connecting to mysql db

[Ferguson, Michael]

J.R. Bullington replied but when I click on the message I get a popup
box that says:
Can't open this item. Your Digital ID name can not be found by the
underlying security system.

J.R. please send me another reply. Thanks

-Original Message-
From: Ferguson, Michael 
Sent: Monday, October 25, 2004 11:57 AM
To: [EMAIL PROTECTED]
Subject: Error connecting to mysql db



G'Day All,

When I try to connect to my mysql/php through Apache I get the following
error:

Warning: mysql_pconnect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/taxes/db_mysql.php on line 99 Database error:
pconnect(localhost:3306, root, $DBPassword) failed. MySQL Error: 0 ()
Session halted.


Can someone please help me to determine what's wrong here.

Thanks very much
  
Ferg

-- 
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]

FW: Error connecting to mysql db

[J.R. Bullington]

This is usually one of two things...

1) MySQL is not running on that machine
2) Permissions for accessing the socket via PHP.

More likely this first, but try both. Check you [HOST].err file to be sure.

J.R.

-Original Message-
From: Ferguson, Michael [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 11:57 AM
To: [EMAIL PROTECTED]
Subject: Error connecting to mysql db


G'Day All,

When I try to connect to my mysql/php through Apache I get the following
error:

Warning: mysql_pconnect(): Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (13) in /var/www/html/taxes/db_mysql.php on line 99 
Database error:
pconnect(localhost:3306, root, $DBPassword) failed.
MySQL Error: 0 ()
Session halted.


Can someone please help me to determine what's wrong here.

Thanks very much
  
Ferg

--
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: Determining if query will work

[Eve Atley]

I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!

It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?

Final query setup posted below.

Thanks,
Eve

 
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1' 
  AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);

INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value) 
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;

INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;


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

Result returned by LOAD LOCAL...

[Karam Chand]

Hello,

When I execute a LOAD DATA INFILE statment.. mySQL
returns a result with info like:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 


How can I get more information about deletions,
warnings etc?

Karam

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

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

RE: Error connecting to mysql db

[Ferguson, Michael]

Thanks very much. I appreciate it.

-Original Message-
From: J.R. Bullington [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 12:46 PM
To: [EMAIL PROTECTED]
Subject: FW: Error connecting to mysql db


This is usually one of two things...

1) MySQL is not running on that machine
2) Permissions for accessing the socket via PHP.

More likely this first, but try both. Check you [HOST].err file to be
sure.

J.R.

-Original Message-
From: Ferguson, Michael [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 11:57 AM
To: [EMAIL PROTECTED]
Subject: Error connecting to mysql db


G'Day All,

When I try to connect to my mysql/php through Apache I get the following
error:

Warning: mysql_pconnect(): Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (13) in
/var/www/html/taxes/db_mysql.php on line 99 Database error:
pconnect(localhost:3306, root, $DBPassword) failed. MySQL Error: 0 ()
Session halted.


Can someone please help me to determine what's wrong here.

Thanks very much
  
Ferg

--
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]


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

RE: Determining if query will work

[SGreen]

I am so happy to get you working. Hopefully we helped some other along the 
way ,too :-)

It's a good idea when working with data that you should always clean up 
after yourself, regardless of what language you are using. Not only does 
it free up resources faster it helps to make sure that you don't end up 
with a contention issue like this.

I take the blame for this one! I gave you the CREATE TEMPORARY... without 
the corresponding DROP TEMPORARY TABLE.

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

Just put:

DROP TEMPORARY TABLE tmpCandidates

at the end of each pass and you will get rid of the temp table. No more 
error message. Temp tables are connection specific so you don't have to 
worry about more than one user/process sharing the same temp table, unless 
they share the same database connection (connection pooling is one 
example)

Come back to the list if you need any more help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Eve Atley [EMAIL PROTECTED] wrote on 10/25/2004 12:48:43 PM:

 
 I can't tell you how *glad* I am to get this running! Big hugs to you,
 Shawn!
 
 It's running beautifully. My only question is, after I run the queries,
 I notice it won't let me create the temporary table again (saying
 'tmpCandidates' already exists). Do I just need to then log out of my
 client (MySQL Control Center) and back in to get rid of that temp table?
 As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
 Or is there a query I can put in at the end of the queries to destroy
 the temporary table once through with it?
 
 Final query setup posted below.
 
 Thanks,
 Eve
 
 
 CREATE TEMPORARY TABLE wow.tmpCandidates
 SELECT DISTINCT r.Candidate_ID
 FROM wow.resume r
 WHERE r.Section_ID = '1' 
   AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
 MODE);
 
 INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
 Section_Value) 
 SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
 r.Section_Value
 FROM wow.tmpCandidates tc
 INNER JOIN wow.resume r
 on r.Candidate_ID = tc.Candidate_ID;
 
 INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
 Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
 Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
 Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
 Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
 Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
 Interview_Availability, Interview_Contact, US_Experience,
 Location_Country)
 SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
 c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
 c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
 c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
 c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
 c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
 c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
 c.Interview_Availability, c.Interview_Contact, c.US_Experience,
 c.Location_Country
 FROM wow.tmpCandidates tc
 INNER JOIN wow.candidate c
 ON c.Candidate_ID = tc.Candidate_ID;
 

RE: Determining if query will work

[Eve Atley]

I think this will be my last question on the matter.
 
I was reading previous messages to the list regarding the boolean search
function, and its problems when searching with 3-letter search terms.
I'll need to search for sap as an ERP term. Is there an efficient way
to avoid getting something like 'sappy' when what I really want is just
'sap', nothing preceeding and nothing following?
 
Thanks,
Eve

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 12:54 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work



I am so happy to get you working. Hopefully we helped some other along
the way ,too :-) 

It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this. 

I take the blame for this one! I gave you the CREATE TEMPORARY...
without the corresponding DROP TEMPORARY TABLE. 

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

Just put: 

DROP TEMPORARY TABLE tmpCandidates 

at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table,
unless they share the same database connection (connection pooling is
one example) 

Come back to the list if you need any more help.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 


Eve Atley [EMAIL PROTECTED] wrote on 10/25/2004 12:48:43 PM:

 
 I can't tell you how *glad* I am to get this running! Big hugs to you,
 Shawn!
 
 It's running beautifully. My only question is, after I run the
queries,
 I notice it won't let me create the temporary table again (saying
 'tmpCandidates' already exists). Do I just need to then log out of my
 client (MySQL Control Center) and back in to get rid of that temp
table?
 As I'll need to change what it searches for (ie baan, peoplesoft,
etc.).
 Or is there a query I can put in at the end of the queries to destroy
 the temporary table once through with it?
 
 Final query setup posted below.
 
 Thanks,
 Eve
 
  
 CREATE TEMPORARY TABLE wow.tmpCandidates
 SELECT DISTINCT r.Candidate_ID
 FROM wow.resume r
 WHERE r.Section_ID = '1' 
   AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
 MODE);
 
 INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
 Section_Value) 
 SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
 r.Section_Value
 FROM wow.tmpCandidates tc
 INNER JOIN wow.resume r
 on r.Candidate_ID = tc.Candidate_ID;
 
 INSERT IGNORE INTO wow.candidate_erp (Candidate_ID, Vendor_ID,
 Last_Name, First_Name, Middle_Initial, Condition_Type, Employer,
 Country_ID, Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments,
 Working, Available, Start_Date, Location, HoldOnPeriod, Relocation,
 Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted,
 Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
 Interview_Availability, Interview_Contact, US_Experience,
 Location_Country)
 SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID, c.Vendor_ID, c.Last_Name,
 c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
 c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
 c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
 c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
 c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate,
 c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
 c.Interview_Availability, c.Interview_Contact, c.US_Experience,
 c.Location_Country
 FROM wow.tmpCandidates tc
 INNER JOIN wow.candidate c
 ON c.Candidate_ID = tc.Candidate_ID;
 

rounding problem

[Tom Butterworth]

Hi
I seem to be having problems returning the expected results when using 
the mysql ROUND() function.

Rounding 3.565 to 2 decimal places i would expect to return 3.57 
however using

SELECT ROUND(3.565, 2);
it returns 3.56. While using
SELECT ROUND(3.575, 2);
works as expected returning 3.58.
I am using mysql version 3.23.54. Any help much appreciated.
Cheers
Buttie
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Help with query statement

[Stuart Felenstein]

I'm having a difficult time getting a query to work
correctly.
I'm not sure, if this is proprietary to Dreamweaver or
not (that is what I do my application development in)

Anyway I have created a search page that is dynamic
(meaning up to the user to choose the criteria)
It all works fine provided I am only querying the main
table. Since that table is composed of some reference
integers, joins are the only way to make it effective.
ButI've tried formating as the following:
SELECT 
  `StaIndTypes`.`CareerCategories`,
  `USStates`.`States`,
  `staTaxTerm`.`TaxTerm`,...
FROM
VendorJobs`, `USStates, `staTaxTerm`

INNER JOIN `StaIndTypes` ON (`VendorJobs`.`Industry` =
`StaIndTypes`.`CareerIDs`)
LEFT OUTER JOIN `USStates` ON
(`VendorJobs`.`LocationState` = `USStates`.`StateID`)
  LEFT OUTER JOIN `staTaxTerm` ON
(`VendorJobs`.`TaxTerm` = `staTaxTerm`.`TaxTermID`)
 
I've also tried:

moving the joins into the where statement
where
vendorjob.industry = staindtypes.careerids 
and
VendorJobs.LocationState = USStates.StateID

Neither works inside the page - though the both work
as just a straight query to the database.

I'm not sure, myabe the code is broken somewhere else,
but if I just put that one table in there
select * from vendorjobs (it adds this):
SELECT * FROM VendorJobs $MM_whereConst $whereClause
order by PostStart desc

I am not sure what the $MM_whereConst or %whereClause
is , or what it should contain.  
So , after many hours of trying various things I
thought I'd throw it out here and see if anyone
understands.

Stuart

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

bug or feature, 'blah' does NOT work with null records

[matt_lists]

I cant tell if this is a bug or a feature.
Select from table where col  'blah'
I use this all the time with other databases, works great, gives me 
everything that's not blah

but in mysql, it wont work if there's null records in the table
I have to do this, select from table where ( col  'blah or isnull(col) )
using 4.0.16 (every time I try to upgrade, I get bit by date bugs with 
the odbc drivers)

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

RE: bug or feature, 'blah' does NOT work with null records

[Jay Blanchard]

[snip]
I cant tell if this is a bug or a feature.

Select from table where col  'blah'

I use this all the time with other databases, works great, gives me 
everything that's not blah

but in mysql, it wont work if there's null records in the table

I have to do this, select from table where ( col  'blah or isnull(col)
)
[/snip]

Feaature.

Thematically NULL is not the same as NOT EQUAL TO something. Something
can be NOT NULL, and if it is NOT NULL it could then be EQUAL TO or NOT
EQUAL TO something. NOT NULL is the state of having or being
'something', whereas NULL is that indescribable state of not being
anything...for even zero is 'something'.

MySQL Philosophy 101 :)

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

Re: bug or feature, 'blah' does NOT work with null records

[Keith Ivey]

matt_lists wrote:
I cant tell if this is a bug or a feature.
Select from table where col  'blah'
I use this all the time with other databases, works great, gives me 
everything that's not blah

In SQL (not just MySQL), any comparisons involving NULL return NULL,
so if that was working in some other database, it's a bug in that database.
See these pages about MS SQL Server and PostgreSQL (which does have a 
workaround), for
example:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_02_8pwy.asp
http://www.sql.org/sql-database/postgresql/manual/functions-comparison.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bug or feature, 'blah' does NOT work with null records

[Paul DuBois]

At 14:51 -0400 10/25/04, matt_lists wrote:
I cant tell if this is a bug or a feature.
Select from table where col  'blah'
I use this all the time with other databases, works great, gives me 
everything that's not blah

but in mysql, it wont work if there's null records in the table
That is the correct behavior.
I have to do this, select from table where ( col  'blah or isnull(col) )
You could use the = equality operator, which like like = except that it
also is true for NULL values:
NOT (col = 'blah')
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: bug or feature, 'blah' does NOT work with null records

[SGreen]

MySQL also has a work around. You might try to rephrase your comparison as 


WHERE not col = 'blah'

the = operator is documented as a null-enabled equality check. That 
way if you are comparing null to null, you get a true or false and not 
another null. This comparator is available as of 3.23.0

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

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Keith Ivey [EMAIL PROTECTED] wrote on 10/25/2004 03:11:53 PM:

 matt_lists wrote:
 
  I cant tell if this is a bug or a feature.
 
  Select from table where col  'blah'
 
  I use this all the time with other databases, works great, gives me 
  everything that's not blah
 
 
 In SQL (not just MySQL), any comparisons involving NULL return NULL,
 so if that was working in some other database, it's a bug in that 
database.
 
 See these pages about MS SQL Server and PostgreSQL (which does have a 
 workaround), for
 example:
 
 http://msdn.microsoft.com/library/default.asp?url=/library/en-
 us/acdata/ac_8_qd_02_8pwy.asp
 
http://www.sql.org/sql-database/postgresql/manual/functions-comparison.html
 
 -- 
 Keith Ivey [EMAIL PROTECTED]
 Washington, DC
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

RE: rounding problem

[Edgar Meij]

Hmmm, peculiar... Tried it on 4.1.6-gamma-nt and works fine:

SELECT ROUND(3.575, 2); = 3,57
SELECT ROUND(3.565, 2); = 3.56

The round() function probably cuts off the last bit...

More info: http://lists.mysql.com/myodbc/8 

Regards,

Edgar

-Oorspronkelijk bericht-
Van: Tom Butterworth [mailto:[EMAIL PROTECTED] 
Verzonden: maandag 25 oktober 2004 19:35
Aan: [EMAIL PROTECTED]
Onderwerp: rounding problem

Hi

I seem to be having problems returning the expected results when using 
the mysql ROUND() function.

Rounding 3.565 to 2 decimal places i would expect to return 3.57 
however using

SELECT ROUND(3.565, 2);

it returns 3.56. While using

SELECT ROUND(3.575, 2);

works as expected returning 3.58.

I am using mysql version 3.23.54. Any help much appreciated.

Cheers

Buttie


-- 
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]

OT: table size WAS RE: optimizing database

[Chris W. Parker]

Razor Fish mailto:[EMAIL PROTECTED]
on Thursday, October 21, 2004 3:19 PM said:

 i need consulting help for optimizing a database with
 1.2 million records to handle 3-4 million hits a day.

this is going to be a 'duh' question on my part but i just had to ask
anyway.

my largest table probably has 700 records in it. what the heck kind of
data is being stored where it reaches the millions (or more)?



chris.

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

Re: rounding problem

[V. M. Brasseur]

This must be a bug that was fixed in the 4.1 version.  I see the same 
thing as Tom, using both 3.23.49 and 4.0.20...

Oh, wait.  The ever-helpful manual comes through again:
From the manual documentation on ROUND():
Note that the behavior of ROUND() when the argument is halfway between 
two integers depends on the C library implementation. Different 
implementations round to the nearest even number, always up, always 
down, or always toward zero. If you need one kind of rounding, you 
should use a well-defined function such as TRUNCATE() or FLOOR() instead.
http://dev.mysql.com/doc/mysql/en/Mathematical_functions.html#IDX1363

There are many references to this bug in the MySQL Bug Tracker.  All 
of them reinforce that this is not a bug but rather a variance in C 
library implementation.

http://bugs.mysql.com/search.php?search_for=roundstatus=Allseverity=alllimit=10order_by=cmd=displaydirection=ASCbug_type=Anyassign=php_os=phpver=bug_age=0
Cheers,
--V
Edgar Meij wrote:
Hmmm, peculiar... Tried it on 4.1.6-gamma-nt and works fine:
SELECT ROUND(3.575, 2); = 3,57
SELECT ROUND(3.565, 2); = 3.56
The round() function probably cuts off the last bit...
More info: http://lists.mysql.com/myodbc/8 

Regards,
Edgar
-Oorspronkelijk bericht-
Van: Tom Butterworth [mailto:[EMAIL PROTECTED] 
Verzonden: maandag 25 oktober 2004 19:35
Aan: [EMAIL PROTECTED]
Onderwerp: rounding problem

Hi
I seem to be having problems returning the expected results when using 
the mysql ROUND() function.

Rounding 3.565 to 2 decimal places i would expect to return 3.57 
however using

SELECT ROUND(3.565, 2);
it returns 3.56. While using
SELECT ROUND(3.575, 2);
works as expected returning 3.58.
I am using mysql version 3.23.54. Any help much appreciated.
Cheers
Buttie

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

RE: table size WAS RE: optimizing database

[J.R. Bullington]

I have multiple databases running tables with thousands of records in them. Some of my 
tables have
as many as 130 million records in them. Memberships and patient data can easily run 
from thousands
to tens of thousands of records. If you are looking into things like DNA/Genome 
mapping, you can
easily run into billions of records.

J.R.

-Original Message-
From: Chris W. Parker [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 3:41 PM
To: [EMAIL PROTECTED]
Subject: OT: table size WAS RE: optimizing database

Razor Fish mailto:[EMAIL PROTECTED]
on Thursday, October 21, 2004 3:19 PM said:

 i need consulting help for optimizing a database with
 1.2 million records to handle 3-4 million hits a day.

this is going to be a 'duh' question on my part but i just had to ask anyway.

my largest table probably has 700 records in it. what the heck kind of data is being 
stored where it
reaches the millions (or more)?



chris.



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

Calculating a value based on an aliased column

[Eamon Daly]

Okay, I'm at my wit's end on this one. Suppose I have a
(grossly simplified) table like so:
mysql select * from filter;
++--+---+---+--++---+
| id | name | value | in_method | in_value | out_method | out_value |
++--+---+---+--++---+
|  1 | foo  | 1 | Multiply  |1 | Add| 2 |
|  2 | foo  | 2 | Divide|3 | Subtract   | 4 |
|  3 | foo  | 3 | Add   |5 | Multiply   | 6 |
|  4 | bar  | 4 | Subtract  |7 | Divide | 8 |
|  5 | bar  | 5 | Multiply  |9 | Add|10 |
++--+---+---+--++---+
where in_method and out_method are enums. I want to perform
a transformation on the initial value using in_method, then
perform another calculation of that result using out_method,
like so:
++--+-+-+
| id | name | phase_1 | phase_2 |
++--+-+-+
|  1 | foo  |   1 |   3 |
|  2 | foo  | .67 |   -3.33 |
|  3 | foo  |   8 |  48 |
|  4 | bar  |  -3 |   -0.38 |
|  5 | bar  |  45 |  55 |
++--+-+-+
On paper, it's easy:
SELECT
CASE
 WHEN in_method = 'Add' THEN value + in_value
 WHEN in_method = 'Subtract' THEN value - in_value
 WHEN in_method = 'Multiply' THEN value * in_value
 WHEN in_method = 'Divide' THEN value / in_value
END as phase_1,
CASE
 WHEN out_method = 'Add' THEN phase_1 + in_value
 WHEN out_method = 'Subtract' THEN phase_1 - in_value
 WHEN out_method = 'Multiply' THEN phase_1 * in_value
 WHEN out_method = 'Divide' THEN phase_1 / in_value
END as phase_2
FROM filter
But, of course, that results in ERROR 1054: Unknown column
'phase_1' in 'field list'. Do I need to create a temporary
table just to hold all the phase_1 values? Ultimately, I
want to group by name, so that seems like an awfully
wasteful step. Am I missing something?

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

Problem with JDBC and Unicode

[Barley]

Hi all,

I have been struggling to get MySql to accept Unicode characters for a while
now with no success. I am attaching a small example program as well as a
mySql dump in hopes that someone can help.

The expected behavior is that the program should insert a curly quote and a
'one quarter' symbol. It gets the 'one quarter' symbol, but not the curly
quote (unicode char 201C). Any help much appreciated.

MySql version - 4.1.4-gamma-nt-log
Connecto/J version - 3.0.10

table dump:

use testchar;
DROP TABLE IF EXISTS `testchar`;
CREATE TABLE `testchar` (
  `testchar` varchar(100) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


test code snippet:

try {

  Class.forName(org.gjt.mm.mysql.Driver);
  Connection conn = DriverManager.getConnection(

jdbc:mysql://localhost/testchar?user=rootamp;useUnicode=trueamp;character
Encoding=utf8);
  java.sql.Statement select = conn.createStatement();
  select.executeUpdate(insert into testchar (testchar) values ('problem
characters - \u201C ... \u00BC'));
  ResultSet r = select.executeQuery(select testchar from testchar);
  while (r.next()) {
System.out.println(r.getString(1));
  }
  conn.close();
}
catch (Exception e) {
  e.printStackTrace();
}
-

The output should be:
problem characters -  ... ¼

Instead, it is:
problem characters - ? ... ¼

Many MANY grateful thanks to anyone who can tell me what I am doing wrong.
This has been very frustrating.

Gregg

Two Instances

[Christopher Chamber]

Hi Everyone. My problem is that i want to create 2 instances of a
sub-query. For Example:

SELECT DISTINCT V4.* FROM (SELECT v2.* FROM cell v1,cell v2 WHERE v1.name
= 'reviews' AND v2.sal = v1.sal ) v3 , v4

This query will gives an error as V4 is not allowed, i.e. not more than 1
instance of a sub-query can be created. Is there a way around this
problem. I would not like to make this sub-query again to get another
instance of it. I need it very much, and if anyone can give me a way to do
it, i would be highly grateful.

Thanks in advance.

---
Christopher Chamber
http://gem-hs.org/cc.html

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

Slow ORDER BY query..

[Aaron]

Hi all , 

I am currently experiencing an issue with a query I would have thought to be somewhat 
straightforward. Perhaps someone could shed some light on what might be causing this? 
The below example was running by itself , not waiting for any other queries. It just 
took a bloody long time to run. The system load went to around 7 or so , however the 
CPU's were not taxed at all. 

Of curious note to me , is that it seems to be intermittently taking a long time. Upon 
restarting of the server and flushing the cache , some queries will take  1 second 
, some will take around 5 , and some will take ridiculously long times. I think that 
has to do perhaps with the amount of rows matching before the ORDER BY? 

I've included all the information I can think of below if anyone feels like having a 
look, It would be be greatly appreciated. 

Thanks!
Aaron

~~

MySQL Version:
~~
MySQL 4.0.18 on RedHat Linux

The Query 

mysql SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate DESC 
LIMIT 20 ; 
+-+
| ID  |
+-+
| 1653497 |
| 1653498 |
| 1653506 |
| 1652861 |
| 1652685 |
| 1652784 |
| 1651739 |
| 1650276 |
| 1650323 |
| 1649569 |
| 1649079 |
| 1649228 |
| 1649410 |
| 1649411 |
| 1648444 |
| 1648543 |
| 1648877 |
| 1648897 |
| 1648911 |
| 1648308 |
+-+
20 rows in set (2 min 52.20 sec)

Record Count:
mysql SELECT count(1) FROM Offers_To_Buy ;
+--+
| count(1) |
+--+
|   461216 |
+--+
1 row in set (0.00 sec)

Explain Output:
mysql EXPLAIN SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate 
DESC LIMIT 20 ;
+---+--+--+-+-+---+--+-+
| table | type | possible_keys| key | key_len | ref   | rows | 
Extra   |
+---+--+--+-+-+---+--+-+
| Offers_To_Buy | ref  | subcategory,scdd | subcategory |   4 | const | 8562 | 
Using where; Using filesort |
+---+--+--+-+-+---+--+-+
1 row in set (0.00 sec)

The Table:

mysql describe Offers_To_Buy ;
+--+--+--+-+++
| Field| Type | Null | Key | Default| Extra  |
+--+--+--+-+++
| bid  | mediumtext   |  | MUL |||
| company_name | varchar(50)  | YES  | | NULL   ||
| street_address   | varchar(100) | YES  | | NULL   ||
| city | varchar(25)  | YES  | | NULL   ||
| province | varchar(25)  | YES  | | NULL   ||
| country  | varchar(25)  |  | MUL |||
| postal_code  | varchar(10)  | YES  | | NULL   ||
| phone| varchar(50)  | YES  | | NULL   ||
| fax  | varchar(50)  | YES  | | NULL   ||
| email| varchar(100) |  | |||
| contact_name | varchar(50)  | YES  | | NULL   ||
| keywords | varchar(100) | YES  | MUL | NULL   ||
| URL  | varchar(200) | YES  | | NULL   ||
| obtain_documents | varchar(50)  | YES  | | NULL   ||
| cost | float(10,2)  | YES  | | NULL   ||
| deletedate   | date |  | MUL | -00-00 ||
| bidvalue | float(10,2)  | YES  | | NULL   ||
| country_dest | varchar(25)  | YES  | | NULL   ||
| subcatID | int(10) unsigned |  | MUL | 0  ||
| ID   | int(10) unsigned |  | MUL | NULL   | auto_increment |
| source   | varchar(30)  |  | MUL |||
| approved_by  | varchar(30)  | YES  | | NULL   ||
| oldtitle | varchar(100) |  | MUL |||
| Closed   | tinyint(4)   |  | | 0  ||
| userID   | int(10) unsigned |  | MUL | 0  ||
| image| varchar(30)  | YES  | | NULL   ||
| postDate | date | YES  | | NULL   ||
| blank5   | char(1)  | YES  | | NULL   ||

Importing data from a file

[Manuel J. Contreras Maya]

Hello,
I am new in mysql and I woul like to ask what is the best way to import 
data from a file.

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

RE: Importing data from a file

[Brian Abbott]

There's a tool called mysqlimport. I recommend using that. Or, if you
have to extract data from a larger set, you could write a program. But,
mysqlimport is the easiest way. If you're on a UNIX machine, run 'man
mysqlimport' for more information.

Good luck,

Brian 

-Original Message-
From: Manuel J. Contreras Maya [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 10:00 AM
To: [EMAIL PROTECTED]
Subject: Importing data from a file

Hello,

I am new in mysql and I woul like to ask what is the best way to import 
data from a file.

Cheers,

Manuel

-- 
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: Importing data from a file

[Manuel J. Contreras Maya]

I managed to do it using
mysql load data infile '/home/manuel/databases/import.txt'
   - into table countries
   - fields terminated by ';';
Query OK, 240 rows affected (0.01 sec)
Records: 240  Deleted: 0  Skipped: 0  Warnings: 18
(I will check the warnings...)
I guess my system do not have mysqlimport,
[EMAIL PROTECTED] manuel]$ man mysqlimport
No manual entry for mysqlimport
Thanks a lot!
Manuel


Brian Abbott wrote:
There's a tool called mysqlimport. I recommend using that. Or, if you
have to extract data from a larger set, you could write a program. But,
mysqlimport is the easiest way. If you're on a UNIX machine, run 'man
mysqlimport' for more information.
Good luck,
Brian 

-Original Message-
From: Manuel J. Contreras Maya [mailto:[EMAIL PROTECTED] 
Sent: Monday, October 25, 2004 10:00 AM
To: [EMAIL PROTECTED]
Subject: Importing data from a file

Hello,
I am new in mysql and I woul like to ask what is the best way to import 
data from a file.

Cheers,
Manuel
 


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

Re: Importing data from a file

[Rhino]

I've used the 'load data infile' command to import data from a file and it
worked well for my purposes. See
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html for the full syntax.

Rhino

- Original Message - 
From: Manuel J. Contreras Maya [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, October 25, 2004 1:59 PM
Subject: Importing data from a file


 Hello,

 I am new in mysql and I woul like to ask what is the best way to import
 data from a file.

 Cheers,

 Manuel

 -- 
 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]

Full-Text Search

[Jalil Feghhi]

In the MySQL documentation, it says that: Boolean full-text searches
have these characteristics: 

*   They do not use the 50% threshold. 

*   They do not automatically sort rows in order of decreasing
relevance. You can see this from the preceding query result: The row
with the highest relevance is the one that contains ``MySQL'' twice, but
it is listed last, not first. 

*   They can work even without a FULLTEXT index, although this would
be slow. 

*   The minimum and maximum word length full-text parameters apply. 

*   The stopword list applies. 

I had two questions:
 
1. How are the returned rows sorted in the boolean full-text searches?
 
2. Is there any way to get more information (other than the score) from
MySQL? For example, can we find out the location of matches? 
 
Regards,
 
-Jalil

mysqlbug report

[Louis R. Profit]

From: lrpbfd
To: [EMAIL PROTECTED]
Subject: [50 character or so descriptive subject here (for reference)]
  

Description:
   precise description of the problem (multiple lines)
How-To-Repeat:
   code/input/activities to reproduce the problem (multiple lines)
Fix:
   how to correct or work around the problem, if known (multiple 
lines)
  

Submitter-Id:  submitter ID
Originator:lrpbfd
Organization:
organization of PR author (multiple lines)
MySQL support: [none | licence | email support | extended email support ]
Synopsis:  synopsis of the problem (one line)
Severity:  [ non-critical | serious | critical ] (one line)
Priority:  [ low | medium | high ] (one line)
Category:  mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one 
line)
Release:   mysql-4.0.21-standard (Official MySQL-standard binary)
  

C compiler:
C++ compiler:
Environment:
   machine, os, target, libraries (multiple lines)
System: SunOS hexagon.lrp.com 5.9 Generic_112234-03 i86pc i386 i86pc
Architecture: i86pc
  

Some paths:  /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc
GCC: Reading specs from /usr/local/lib/gcc/i386-pc-solaris2.9/3.4.0/specs
Configured with: ../configure --with-as=/usr/ccs/bin/as 
--with-ld=/usr/ccs/bin/ld --disable-nls
Thread model: posix
gcc version 3.4.0
Compilation info: CC='cc'  CFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none 
-nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native'  CXX='CC'  
CXXFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all 
-xlibmil -xlibmopt -xtarget=native'  LDFLAGS=''  ASFLAGS=''
LIBC:
-rw-r--r--   1 root bin  1886116 Nov  4  2002 /lib/libc.a
lrwxrwxrwx   1 root root  11 May 31 22:37 /lib/libc.so - 
./libc.so.1
-rwxr-xr-x   1 root bin   795704 Nov  4  2002 /lib/libc.so.1
lrwxrwxrwx   1 root other  9 Jul 27 21:46 /lib/libc.so.6 - 
libc.so.1
-rw-r--r--   1 root bin  1886116 Nov  4  2002 /usr/lib/libc.a
lrwxrwxrwx   1 root root  11 May 31 22:37 /usr/lib/libc.so 
- ./libc.so.1
-rwxr-xr-x   1 root bin   795704 Nov  4  2002 /usr/lib/libc.so.1
lrwxrwxrwx   1 root other  9 Jul 27 21:46 /usr/lib/libc.so.6 
- libc.so.1
Configure command: ./configure '--prefix=/usr/local/mysql' 
'--localstatedir=/usr/local/mysql/data' 
'--libexecdir=/usr/local/mysql/bin' '--with-comment=Official
MySQL-standard binary' '--with-extra-charsets=complex' 
'--with-server-suffix=-standard' '--enable-thread-safe-client' 
'--enable-local-infile' 'CC=cc' 'CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none 
-nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' 'CXX=CC' 
'CXXFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all 
-xlibmil -xlibmopt -xtarget=native' '--with-named-curses=-lcurses' 
'--disable-shared' '--with-embedded-server' '--with-innodb'


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

Re: Slow ORDER BY query..

[Dan Sashko]

from mysql:
With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use
indexes to resolve the query. It cannot if you see Using filesort in the
Extra column.
. further :
In MySQL 4.1 and up, a filesort optimization is used that records not only
the sort key value and row position, but also the columns required for the
query. This avoids reading the rows twice.
so suggestion is to upgrade to 4.1+. This is most likely not  a viable
solution :) so further from their website:
If you want to increase ORDER BY speed, first see whether you can get MySQL
to use indexes rather than an extra sorting phase. If this is not possible,
you can try the following strategies:
 a.. Increase the size of the sort_buffer_size variable.
 b.. Increase the size of the read_rnd_buffer_size variable.
 c.. Change tmpdir to point to a dedicated filesystem with lots of empty
space. If you use MySQL 4.1 or later, this option accepts several paths that
are used in round-robin fashion. Paths should be separated by colon
characters (`:') on Unix and semicolon characters (`;') on Windows, NetWare,
and OS/2. You can use this feature to spread the load across several
directories. Note: The paths should be for directories in filesystems that
are located on different physical disks, not different partitions of the
same disk. 
also from their site, if  I am not mistakin, they suggest to use GROUP BY
the colum that will be sorted by (which forces the sort and might eliminate
the rereading of the rows) with ORDER BY NULL at the end to eliminate the
overhead of sorting (since it will be sorted in 'GROUP BY deletedate DESC')
:
SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC
ORDER BY NULL LIMIT 20 ;
if your deletedate is not unique then mesh some already used key into it (ID
or subcatID) to eliminate actual grouping:
SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC,
ID ORDER BY NULL LIMIT 20 ;
(note to that, you ID field, thought autoincremented, is not set to be
unique, so unless you know it is unique for a fact then meshing it with
deletedate might still produce undesirable grouping. In that case you might
have to add some unique number in group by clause liek a current row
counter, or worst case a random number).
--- 
It might not speed up the query depending on the size of the WHERE results,
but might not be as random on completion time.

Curiouse if it does anything, let me know if you try.
- Original Message - 
From: Aaron [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, October 25, 2004 4:19 PM
Subject: Slow ORDER BY query..

Hi all ,
I am currently experiencing an issue with a query I would have thought to be 
somewhat straightforward. Perhaps someone could shed some light on what 
might be causing this? The below example was running by itself , not waiting 
for any other queries. It just took a bloody long time to run. The system 
load went to around 7 or so , however the CPU's were not taxed at all.

Of curious note to me , is that it seems to be intermittently taking a long 
time. Upon restarting of the server and flushing the cache , some queries 
will take  1 second , some will take around 5 , and some will take 
ridiculously long times. I think that has to do perhaps with the amount of 
rows matching before the ORDER BY?

I've included all the information I can think of below if anyone feels like 
having a look, It would be be greatly appreciated.

Thanks!
Aaron
~~
MySQL Version:
~~
MySQL 4.0.18 on RedHat Linux
The Query

mysql SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate 
DESC LIMIT 20 ;
+-+
| ID  |
+-+
| 1653497 |
| 1653498 |
| 1653506 |
| 1652861 |
| 1652685 |
| 1652784 |
| 1651739 |
| 1650276 |
| 1650323 |
| 1649569 |
| 1649079 |
| 1649228 |
| 1649410 |
| 1649411 |
| 1648444 |
| 1648543 |
| 1648877 |
| 1648897 |
| 1648911 |
| 1648308 |
+-+
20 rows in set (2 min 52.20 sec)

Record Count:
mysql SELECT count(1) FROM Offers_To_Buy ;
+--+
| count(1) |
+--+
|   461216 |
+--+
1 row in set (0.00 sec)
Explain Output:
mysql EXPLAIN SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY 
deletedate DESC LIMIT 20 ;
+---+--+--+-+-+---+--+-+
| table | type | possible_keys| key | key_len | ref   | 
rows | Extra   |
+---+--+--+-+-+---+--+-+
| Offers_To_Buy | ref  | subcategory,scdd | subcategory |   4 | const | 
8562 | Using where; Using filesort |
+---+--+--+-+-+---+--+-+
1 row in set (0.00 sec)

The Table:

mysql describe Offers_To_Buy ;

compiler warning (UDF code)

[Sergei Skarupo]

HI everyone,
 
Sorry if this question doesn't belong here...
 
I tried to compile several open-source UDF's (downloaded from 
http://mysql-udf.sourceforge.net/) and got a warning. Could someone please elaborate 
on this:
 
/usr/include/asm/atomic.h:40:2: warning: #warning Using kernel header in userland 
program. BAD!

This is from gcc 3.2.2 on Red Hat 9, kernel version 2.4.20

How bad is it really? What are the implications? 

When I compile the same code with gcc 2.95.2, kernel 2.4.0 on another machine, I do 
not get this warning.

Thanks in advance,
 
Sergei