Limit in sub-query - when can we expect it?
Hi List, When can we expect limits in sub-queries? I am currently on 4.1.0. 1235 - This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Query: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.15 : Foreign Key - ERROR 1005 (errno: 150)
Hi, I've got 2 tables in a circular dependency as follows CREATE TABLE USERDETAILS ( USERDETAILS_ID BIGINT NOT NULL, FORENAME VARCHAR(30) BINARY NULL, SURNAME VARCHAR(30) BINARY NULL, USER_USER_ID_OID BIGINT NULL, PRIMARY KEY (USERDETAILS_ID) ) TYPE=INNODB CREATE TABLE `USER` ( USER_ID BIGINT NOT NULL, DETAILS_USERDETAILS_ID_OID BIGINT NULL, LOGIN VARCHAR(20) BINARY NULL, PASSWORD VARCHAR(12) BINARY NULL, PRIMARY KEY (USER_ID) ) TYPE=INNODB CREATE INDEX USERDETAILS_N49 ON USERDETAILS (USER_USER_ID_OID) This all works fine. I then try to create one of the foreign keys between the 2 tables ALTER TABLE jpox.USERDETAILS ADD CONSTRAINT USERDETAILS_FK1 FOREIGN KEY (USER_USER_ID_OID) REFERENCES jpox.`USER` (USER_ID) MySQL (4.0.15) responds with ERROR 1005: Can't create table './jpox/#sql-5b3_5a.frm' (errno: 150) This works with 4.0.18, yet I need my system to work also on 4.0.15 and earlier. Is there something that I'm missing here with regards to my FK specification ? TIA -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export query to text file ***[SPAM]*** **SPAM HTMLM**
Yingyos [EMAIL PROTECTED] wrote:
I type this command line.
mysql -uuser -N -eselect date_format('2004-02-29','%X') sample2.txt
When i open in text editor,it show that '2004' only.
But i want format display on text file.
mysql select date_format('2004-02-29','%X');
++
| date_format('2004-02-29','%X') |
++
| 2004 |
++
1 row in set (0.00 sec)
How 's to do?
Use --tee/--no-tee options or tee/notee commands of mysql client:
http://dev.mysql.com/doc/mysql/en/mysql.html
http://dev.mysql.com/doc/mysql/en/mysql_Commands.html
For example:
mysql tee file.txt;
Logging to file 'file.txt'
mysql SELECT DATE_FORMAT('2004-02-29','%X');
++
| DATE_FORMAT('2004-02-29','%X') |
++
| 2004 |
++
1 row in set (0.01 sec)
mysql notee;
Outfile disabled.
mysql
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
bug in Mysql 4.1
We have found a bug in MySQL 4.1 (tested on Win2k machine):
create database testdate;
use testdate;
create table a (id int not null auto_increment, dt date, primary key(id));
insert into a (dt) values ('');
insert into a (dt) values ('2004-05-06');
select * from a where dt = '';
Pre Mysql 4.1
id dt
-- --
1 -00-00
1 Row(s) affected
Mysql 4.1
id dt
-- --
0 Row(s) affected
When is MySQL 4.1 planned for production release?
Regards
Bent Audun Hesvik
Software engineer
--
it:solutions, O. J. Brochs gate 16B, P.B. 2686 Møhlenpris, N-5836 Bergen,
Norway
+47 55 23 60 96 (office)
+47 95 77 03 29 (cellular phone)
+47 55 23 60 71 (fax)
[EMAIL PROTECTED]
Subscribe to the it:solutions newsletter at http://www.itsolutions.no
--
CONFIDENTIALITY NOTICE:
The content of this e-mail is intended solely for the use of the individual
or entity to whom it is addressed. If you have received this communication
in error, be aware that forwarding it, copying it or in any way disclosing
its content to any other person, is strictly prohibited. If you have
received this communication in error, please notify the author by replying
to this e-mail immediately.
http://www.itsolutions.no
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.651 / Virus Database: 417 - Release Date: 05.04.2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Win Linux
Ivan Cukic (Foment) escribio': -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 | Another question how can see the compatibility of authentification? | I don't know. It should be written in docs... Anyway, try the next thing... Try to create a symlink for every db except for mysql. create a symlink /var/lib/mysql/db1 - /mnt/onedisc/Program\ Files/mysql/data/db1 create a symlink /var/lib/mysql/db2 - /mnt/onedisc/Program\ Files/mysql/data/db2 create a symlink /var/lib/mysql/db3 - /mnt/onedisc/Program\ Files/mysql/data/db3 this way you can control which databases will be shared between win and lin. Regards, Ivan -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAlmSuTGB93IULb3YRAmt1AJ4wyI31vgXS/OwdpkxerolyfLdpiQCfZcFW PZ26ss5LaXM9KFosh2PEd8Y= =pdaD -END PGP SIGNATURE- Thanks, it works, but I needed change privileges and append the new databases. Regards, Segismundo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date calculation and displaying the right date
Hi All I've not been able to find a good answer to this or figure out what would be the best approach - bear in mind I am a beginner. I have tried the following USE databas SELECT Item_A AS 'Something 1', Item_B AS 'Something 2', Item_C AS 'Something 3', INTERVAL 6 MONTH + Item_B AS 'Something 4', BEGIN IF CURDATE() = INTERVAL 6 MONTH + Item_C THEN SELECT INTERVAL 12 MONTH + Item_C [ELSEIF CURDATE() = INTERVAL 12 MONTH + Item_C THEN SELECT INTERVAL 18 MONTH + Item_C] ELSE INTERVAL 6 MONTH + Item_C) END IF FROM Table ; What I want to do is; I have a post Item_C which is a date. Six months after Item_C an event occurs (lets call it The Event) , and continues to occur every six months untill the post Item_C recives Status NULL. I need to be able to extract from the database the following info and I will explain it in an example; Say on May 1 I would like to find out what if any instances of The Event are occuring during the month of may and the next month. If the original Item_C has the date 2003-01-01, the The Event has/will occurr on 2003-07-01 and on 2004-01-01 and on 2004-07-01 etc. In order for me to discover on June 1 2004 that the The Event will occur 2004-07-01 I need to do a calculation on the original date 2003-01-01 and present the relevant date 2004-07-01 at the relevant time and at the same not including any Item_C which has status NULL. I tried to do this with the above example - which I am sure all you knowledgeable people can see is kludge at best...I am prepared to figure this out on my own but I am having difficulty grasping the appropriate methodology here. Hence any and all help will be much appreciated. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with simple MySQL query
Hi, I'd be grateful if someone could help me and tell me what I'm doing wrong with my query. I suspect the answer is simple but I'm suffering a mental block right now. In basic terms, I have three tables. Two are reference tables thus: table: locations fields: location_id (primary key), location_name example data: 1, Lisbon; 2, Porto table: teams fields: team_id (primary key), team_name example data: 1, France; 2, England Then I have another table that uses the other two. It holds information about matches played at a location between two teams. table: matches fields: match_id, match_datetime, match_location (foreign key to locations table), team1_id (foreign key to teams table), team1_score, team2_id (foreign key to teams table), team2_score example data: 1, 2004/06/16 19:45:00, 1, 1, null, 2, null So, my problem is when I'm pulling the information into the web page - it's along the lines of: SELECT m.match_id, m.match_datetime, m.match_location, m.team1_id, m.team1_score, m.team2_id, m.team2_score, l.location_name, t.team_name as team1_name, t.team_name as team2_name FROM matches m, teams t, locations l WHERE l.location_id = m.match_location AND (t.team_id = m.team1_id OR t.team_id = m.team2_id) but it doesn't return the information I want which should be something like: 1, 16/6/04 19:45, Lisbon, France, null, England, null Instead I get: 1, 16/6/04 19:45, Lisbon, France, null, France, null 1, 16/6/04 19:45, Lisbon, England, null, England, null Someone mentioned something about a crosstab query to me but the pages I've seen (including one on the mysql site) doesn't appear to help. Thanks in advance, Ken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.15 : Foreign Key - ERROR 1005 (errno: 150)
Andy Jefferson [EMAIL PROTECTED] wrote: I've got 2 tables in a circular dependency as follows CREATE TABLE USERDETAILS ( USERDETAILS_ID BIGINT NOT NULL, FORENAME VARCHAR(30) BINARY NULL, SURNAME VARCHAR(30) BINARY NULL, USER_USER_ID_OID BIGINT NULL, PRIMARY KEY (USERDETAILS_ID) ) TYPE=INNODB CREATE TABLE `USER` ( USER_ID BIGINT NOT NULL, DETAILS_USERDETAILS_ID_OID BIGINT NULL, LOGIN VARCHAR(20) BINARY NULL, PASSWORD VARCHAR(12) BINARY NULL, PRIMARY KEY (USER_ID) ) TYPE=INNODB CREATE INDEX USERDETAILS_N49 ON USERDETAILS (USER_USER_ID_OID) This all works fine. I then try to create one of the foreign keys between the 2 tables ALTER TABLE jpox.USERDETAILS ADD CONSTRAINT USERDETAILS_FK1 FOREIGN KEY (USER_USER_ID_OID) REFERENCES jpox.`USER` (USER_ID) MySQL (4.0.15) responds with ERROR 1005: Can't create table './jpox/#sql-5b3_5a.frm' (errno: 150) This works with 4.0.18, yet I need my system to work also on 4.0.15 and earlier. Is there something that I'm missing here with regards to my FK specification ? Worked fine for me on both 4.0.15 and 4.0.18. Check error message with SHOW INNODB STATUS command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
NetBSD MySQL
Are there any NetBSD users on this list? Has anyone succesfully compiled MySQL 4.1x or 5.x on NetBSD? Why arent they in pkgsrc? Not just done yet or did something happend in the 4.1.x release that stopped MySQL from working on NetBSD? Regards /Jonas
Re: Help with simple MySQL query
table: locations fields: location_id (primary key), location_name example data: 1, Lisbon; 2, Porto table: teams fields: team_id (primary key), team_name example data: 1, France; 2, England table: matches fields: match_id, match_datetime, match_location (foreign key to locations table), team1_id (foreign key to teams table), team1_score, team2_id (foreign key to teams table), team2_score example data: 1, 2004/06/16 19:45:00, 1, 1, null, 2, null SELECT m.match_id, m.match_datetime, m.match_location, m.team1_id, m.team1_score, m.team2_id, m.team2_score, l.location_name, t.team_name as team1_name, t.team_name as team2_name FROM matches m, teams t, locations l WHERE l.location_id = m.match_location AND (t.team_id = m.team1_id OR t.team_id = m.team2_id) You'll want the teams table joined twice with the matches table: once to retrieve the name of team1_id and a second time for the name of team2_id. The OR in the WHERE clause is actually the problem which causes the multiple rows in the output... SELECT m.match_id, m.match_datetime, m.match_location, m.team1_id, m.team1_score, m.team2_id, m.team2_score, l.location_name, t.team_name as team1_name, tt.team_name as team2_name FROM matches AS m JOIN teams AS t ON m.team1_id = t.team_id JOIN teams AS tt ON m.team2_id = tt.team_id JOIN locations AS l ON l.location_id = m.match_location (I always put the JOIN condition near the actual JOIN, just to make sure that I still understand it later) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Html and mysql..
Brad wrote Josh Trutwin writes: Javascript is a client-side language, the code is executed by the user's browser. It has no way to connect to the database server and run queries so you need to use a server-side programming language like Java (JDBC), PhP, Perl, etc. Tomcat is a decent servlet engine with a nice price tag (free) and PhP/Perl are pretty easy to get working with an Apache server. If you've never done this before, I'd advise to use PhP as it has the smallest learning curve in my opnion. It might be possible to build a backend php or cgi script that could act as the backend access for JavaScript. But it would definitely be a kludge and fraught with security issues. Mainly because you'd be sending the requests, authorization, and responses across the net. Rather than just across the connection between the web server and the database server. I have a PHP script that I've been hacking around with that I can send a random SQL statement and get the results displayed in a basic web page column headings and all :) It isn't real pretty, but does allow me to test out SQL and my skills with PHP. Would I allow such access via HTTP, not by a long shot. It's just too fraught with security issues. Brad Eacker ([EMAIL PROTECTED]) /Brad Hello list, For those of you not familiar with developing web-based applications with M$'s IIS, JavaScript **IS** available server-side. It's one of the two languages you can use to write ASP pages. The only connection I have found from M$ scripts (VBScript or JavaScript) to MySQL has been through the ODBC driver but that doesn't mean there aren't others. The most recent driver I have found has been version 3.53 so if you have a MySQL server more recent than 4.1.x you will have to do some kludging to get it to authenticate. See http://dev.mysql.com/doc/mysql/en/Password_hashing.html for more details. Shawn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MySQL and OpenMosix
Hi: I would like to add an identical server to the one I already have: Double Xeon processors, 4 Gb RAM and RAID 5 (Hardware) HD's. I would also like to cluster them using OpenMosix, but I'm told that MySQL 4.0 will not take advantage of the cluster. Is there a way to cluster MySQL so that queries will migrate to the new node when needed? Is there any docs I could dig into to see if this can be done? Books, how-to's? Any help would be appreciated. Thank you. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Last inserted ID
Well, you don't need the distinct. Are you inserting with your PHP script? LAST_INSERT_ID(), as per the manual, only returns the id from the last insert on that connect. You cannot get the LAST_INSERT_ID() for another connection. j- k- I used distinct because otherwise, I was getting 3575 results from the command line - I only wanted one. My PHP script uses an insert method in a class, and then tries to get the last insert id. After reading the manual, I was under the impression that the class would use the same connection, but that doesn't seem to be the case. I suppose my insert could return the last id... -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie data import question
I am new to MySQL but I some basic database experience. I have about 50,000 rows of data in a CSV file. Where I can find some examples of SQL scripts that show how to: a. Create a database X. b. Create a table Y. c. Import the 50,000 rows of CSV data into table Y. I am running MySQL on Red Hat 9 -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with a Select Query
Hi,
I have a problem with a SELECT query. I have a users table and each user can
be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two
Nurses allocated to them and the User_ID of the nurse is held in column
Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that returns the
two nurses details based on the User_ID of the Rep:
SELECT U1.*, U2.*
FROM Users U1, Users U2, Users R
WHERE R.User_ID = 'x'
AND U1.User_ID = R.Rep_Nurse_1
AND U2.User_ID = R.Rep_Nurse_2
However this returns just the first nurse allocated to that rep.
I have listed my User table defination below and would be most grateful for
anyones advice here.
html
head
titlequery data/title
style type=text/css
!--
.normal { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
12px; font-weight: normal; color: #00}
.medium { font-family: Verdana, Arial, Helvetica, sans-serif; font-size:
15px; font-weight: bold; color: #00; text-decoration: none}
--/style
/head
h3query result ( 15 records )/h3table border=1
tr
td bgcolor=silver class='medium'Field/tdtd bgcolor=silver
class='medium'Type/tdtd bgcolor=silver class='medium'Null/tdtd
bgcolor=silver class='medium'Key/tdtd bgcolor=silver
class='medium'Default/tdtd bgcolor=silver class='medium'Extra/td/tr
tr
td class='normal' valign='top'User_ID/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'PRI/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal'
valign='top'auto_increment/td/trtr
td class='normal' valign='top'Client_ID/tdtd class='normal'
valign='top'int(3)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Username/tdtd class='normal'
valign='top'varchar(40)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'nbsp;/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Password/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Name/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'nbsp;/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Type/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'nbsp;/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'Nurse/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Email/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email_2/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Manager_Email_3/tdtd class='normal'
valign='top'varchar(100)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Location/tdtd class='normal'
valign='top'varchar(40)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'User_Administrator/tdtd class='normal'
valign='top'int(2)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Mobile_Number/tdtd class='normal'
valign='top'varchar(20)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Rep_Nurse_1/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd class='normal' valign='top'nbsp;/td/trtr
td class='normal' valign='top'Rep_Nurse_2/tdtd class='normal'
valign='top'int(11)/tdtd class='normal' valign='top'YES/tdtd
class='normal' valign='top'nbsp;/tdtd class='normal'
valign='top'(NULL)/tdtd
Re: Problem with a Select Query
SELECT user_id, rep_nurse_1, rep_nurse_2 FROM Users WHERE User_ID = 'x' I have a problem with a SELECT query. I have a users table and each user can be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two Nurses allocated to them and the User_ID of the nurse is held in column Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that returns the two nurses details based on the User_ID of the Rep: SELECT U1.*, U2.* FROM Users U1, Users U2, Users R WHERE R.User_ID = 'x' AND U1.User_ID = R.Rep_Nurse_1 AND U2.User_ID = R.Rep_Nurse_2 However this returns just the first nurse allocated to that rep. I have listed my User table defination below and would be most grateful for anyones advice here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
On Thu, May 06, 2004 at 06:55:38AM -0600, Alfredo Cole wrote: Hi: I would like to add an identical server to the one I already have: Double Xeon processors, 4 Gb RAM and RAID 5 (Hardware) HD's. I would also like to cluster them using OpenMosix, but I'm told that MySQL 4.0 will not take advantage of the cluster. Is there a way to cluster MySQL so that queries will migrate to the new node when needed? Is there any docs I could dig into to see if this can be done? Books, how-to's? At the time I wrote Chapter 8 of High Performance MySQL, I tried to discuss the available options: http://www.oreilly.com/catalog/hpmysql/toc.html However, some of the commercial information was hard to come by, so if you're looking at those, you may need to discuss with the vendors too. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB size estimation
Hello, I'm trying to estimate which disks to buy for an mySQL DB for my clienst. May biggest table is around 700GB and has following format: TECHLOG table ARDKey 4 Integer TIMESTAMP 8 DATETIME TECHDATA52 CHAR(52) The searches will be done by ARDKEY and TIMESTAMP... Both of them are not UNIQUE. There will be around 10 different valuese for ARDKEY I'll have 12 Millions of these records I need to estimate the storeage requirements for the index files What is the rule of thumb to use? Thanks Vadim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
many updates really slow
Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with a Select Query
shaun thornburgh wrote Hi, I have a problem with a SELECT query. I have a users table and each user can be a Rep, Nurse or Administrator defined by User_Type. Each Rep has two Nurses allocated to them and the User_ID of the nurse is held in column Rep_Nurse_1 and Rep_Nurse_2. I am trying to perform a query that returns the two nurses details based on the User_ID of the Rep: SELECT U1.*, U2.*rse_2 However this returns just the first nurse allocated to that rep. I have listed my User table defination below and would be most grateful for anyones advice here. --8 SNIP 8--- /shaun Shaun, The problem is in your WHERE clause. You need an OR for the last AND (and a set of parentheses, too) SELECT U1.*, U2.* FROM Users U1, Users U2, Users R WHERE R.User_ID = 'x' AND ( U1.User_ID = R.Rep_Nurse_1 OR U2.User_ID = R.Rep_Nurse_2 ) Another way to write this is through JOINS SELECT U1.*, U2.* FROM Users R LEFT JOIN Users U1 ON U1.User_ID = R.Rep_Nurse_1 LEFT JOIN Users U2 ON U2.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' Both of those will give you two sets of columns (2x the same column names in the same query).If you had a field called Name in your Users table, you would have two of them in your results. One way to get it down to a single set of columns would be to use a UNION query (depending on which version of MySQL you are running). The advantage to this is that if a Rep only has 1 nurse assigned to them, you only get one row of data and not a whole set of NULL values in the duplicate columns. SELECT U1.* FROM Users R INNER JOIN Users U1 ON U1.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' UNION SELECT U2.* FROM Users R INNER JOIN Users U2 ON U2.User_ID = R.Rep_Nurse_1 WHERE R.User_ID = 'x' There is still another method that involves a lot of IF statements but since you didn't post the column names for your Users table, I can't work it up for you. Hope this helps! Shawn Green Database Administrator Unimin Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Cluster
I'm trying to find documentation on how to setup MySQL cluster Any ideas? Vadim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question on Modifying MySQL
Jason Weiss wrote: Hello Sir or Madam, As for my class project, I need to implement a function to calculate miss ratio. But I am having a such problem where to start. I appreciate if you can give me any advice how to do modify the codes and where to start. Thank you. Jason: See http://dev.mysql.com/doc/mysql/en/Adding_native_function.html I also cover this subject in the last chapter of my book MySQL Enterprise Solutions. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: many updates really slow
On Thu, May 06, 2004 at 10:12:19AM -0700, William Wang wrote: Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. Network latency is killing you. Send the cmd.sql file to host A and execute it locally. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MySQL and OpenMosix
El Jue 06 May 2004 11:05, escribió: On Thu, May 06, 2004 at 06:55:38AM -0600, Alfredo Cole wrote: Hi: I would like to add an identical server to the one I already have: Double Xeon processors, 4 Gb RAM and RAID 5 (Hardware) HD's. I would also like to cluster them using OpenMosix, but I'm told that MySQL 4.0 will not take advantage of the cluster. Is there a way to cluster MySQL so that queries will migrate to the new node when needed? Is there any docs I could dig into to see if this can be done? Books, how-to's? At the time I wrote Chapter 8 of High Performance MySQL, I tried to discuss the available options: http://www.oreilly.com/catalog/hpmysql/toc.html However, some of the commercial information was hard to come by, so if you're looking at those, you may need to discuss with the vendors too. Jeremy Jeremy: I have ordered your book from Amazon.com. But I am not planning to use a commercial solution. I want to use OpenMosix, which is released under the GPL. Any suggestions would be welcome. Thank you. -- Alfredo J. Cole http://www.acyc.com http://www.clshonduras.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
I'd suggest a shell script - a couple of them, actually. Have a shell script on the Europe machine that runs continually. It checks for the existence of the cmd.sql file. If it finds it, it FTPs it to the US server and then deletes or archives the file locally. If it doesn't find it, it sleeps for 5 minutes, wakes up and looks again - minimum cpu cycles wasted. The second script would be on the US server. It would check every 5 minutes for the cmd.sql file. If it finds it, it would load it into MySQL and then either delete or archive the file. If it doesn't find the file, it goes back to sleep. Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:43 PM To: Jack Coxen; [EMAIL PROTECTED] Subject: RE: many updates really slow Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover
Re: newbie data import question
On Thu, 2004-05-06 at 07:24, luther van dam wrote: I am new to MySQL but I some basic database experience. I have about 50,000 rows of data in a CSV file. Where I can find some examples of SQL scripts that show how to: a. Create a database X. b. Create a table Y. c. Import the 50,000 rows of CSV data into table Y. I am running MySQL on Red Hat 9 A search at 'http://dev.mysql.com/doc/mysql/en/' yields: a. http://dev.mysql.com/doc/mysql/en/CREATE_DATABASE.html b. http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html c. http://dev.mysql.com/doc/mysql/en/mysqlimport.html -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and OpenSSL
We're trying to set up SSL so that we can access our MySQL server from a remote site. I am having a hard time trying to find a HOW-TO that describes the whole process, and am finding instead little snippets scattered all over. Can anyone point me in a direction that might provide more (and better) information in one place? Any help would be really appreciated. Thanks! David Christensen Brokers International, Ltd. 1200 E Main St Panora, IA 50216 (641) 755-2775 [EMAIL PROTECTED]
RE: many updates really slow
Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. If you have ssh installed on both machinesthis could be done with two commandsIf you set up passwdless ssh authentication, this could be automated in a script. even if you don't you'd just have to type your password. scp ./cmd.sql [EMAIL PROTECTED]:/path/to/cmd.sql ssh [EMAIL PROTECTED] '/path/to/mysql -u user -pPassword DBname /path/to/cmd.sql' I use stuff like this all the time and it works wonderfully. HTH...and i didn't miss the point. Jeff William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- 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: Query Log
Well look at that. I didn't look back to the Connect statement that starts the ID. Just when I think I'm getting a handle, I dive back into the quicksand! Ugh. Thanks Lou - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 5:25 AM Subject: Re: Query Log Lou Olsten [EMAIL PROTECTED] wrote: I'm pretty sure that the answer to this is No, you cannot but I figured I'd check anyway... As I go back through my query log, I'd like to know the user that issued the statement. If the user is still connected, I can cross reference it with the SHOW PROCESSLIST ID, but if they have signed off, is there a way to get the user then? If you look in the general query log file you can see Id column where thread id is specified and username and host in the Argument column. For update log and slow query log use --log-long-format option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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: many updates really slow
if it is part of app. use soap to communicate with server and adopt your application. Send all your commands as one string parameter to server, and execute it all there. Such modification to is quite simple to write using gSoap library (if your application is C++ based) or Axis (Java). mirza William Wang wrote: Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie data import question
Luther - I would recommend picking up Paul DuBois' book MySQL - The Definitive Guide. The MySQL.com website has docs which will answer your question, but I think a good text always helps with learning MySQL. If you have any specific questions or issues. Please feel free to ask. Thanks, --bmansell -Original Message- From: luther van dam [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 7:24 AM To: [EMAIL PROTECTED] Subject: newbie data import question I am new to MySQL but I some basic database experience. I have about 50,000 rows of data in a CSV file. Where I can find some examples of SQL scripts that show how to: a. Create a database X. b. Create a table Y. c. Import the 50,000 rows of CSV data into table Y. I am running MySQL on Red Hat 9 -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- 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]
User Conference Presentations
Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem setting default value for column with concat()
Hi all,
When I run the following code the default value isn't being calculated using the concat
and other functions. Instead it is setting the column definition as a string:
CONCAT(TMP
How can I rework this to get a result more like
TMP-T-2
?
USE cro;
CREATE TABLE lpamform (
crofileno VARCHAR(11) NOT NULL DEFAULT 'CONCAT(TMP-T-,ROUND(RAND(NOW())*10))',
docstatus ENUM('C','A') NOT NULL DEFAULT 'C',
PRIMARY KEY (crofileno)
) TYPE=MyISAM;
Thanks,
Tim
Re: Problem setting default value for column with concat()
Tim Russell wrote:
Hi all,
When I run the following code the default value isn't being calculated using the concat
and other functions. Instead it is setting the column definition as a string:
CONCAT(TMP
How can I rework this to get a result more like
TMP-T-2
?
You can't.
Default values can only be constants.
( except for autoincrement and timestamp )
USE cro;
CREATE TABLE lpamform (
crofileno VARCHAR(11) NOT NULL DEFAULT 'CONCAT(TMP-T-,ROUND(RAND(NOW())*10))',
docstatus ENUM('C','A') NOT NULL DEFAULT 'C',
PRIMARY KEY (crofileno)
) TYPE=MyISAM;
Thanks,
Tim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL problem
Matthew Stuart wrote: I have a home page where I need to show three news articles which are specified by the administrator - so it might not be the latest articles and they might not be in any order. I have designed the db to have two tables to fulfill this function the first table 'tbl_press' holds the news articles, and second 'tbl_hompageids' is what governs the three articles to be shown. tbl_homepageids has three columns, fld_article1, fld_article2 and fld_article3 each having a value manually inputted to indicate which article to select from tbl_press tbl_press has many columns, but the key one in this instance is a field called 'fld_id' which is the value I am trying to get the home page to read via use of tbl_homepageids. Basically, I am trying to use the table 'tbl_homepageids' as the engine to select the correct articles from the table 'tbl_press'. The recordset/SQL statement I am trying to create goes like this: SELECT * FROM tbl_homepageids, tbl_press WHERE fld_article1,tbl_homepageids LIKE fld_id,tbl_press WHERE tbl_homepageids.fld_article1 = tbl_press.fld_id I will create another two recordsets/SQL statements for the other two press articles using fld_article2 and fld_article3. Quite obviously the statement here isn't working and I have tried many different variations on the same theme, but haven't managed to achieve the results required, so I have written it in its simplest format hoping somebody can understand what I need and help me through this trying period in my life!! TIA Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL problem
Matthew Stuart wrote I have a home page where I need to show three news articles which are specified by the administrator - so it might not be the latest articles and they might not be in any order. I have designed the db to have two tables to fulfill this function the first table 'tbl_press' holds the news articles, and second 'tbl_hompageids' is what governs the three articles to be shown. tbl_homepageids has three columns, fld_article1, fld_article2 and fld_article3 each having a value manually inputted to indicate which article to select from tbl_press tbl_press has many columns, but the key one in this instance is a field called 'fld_id' which is the value I am trying to get the home page to read via use of tbl_homepageids. Basically, I am trying to use the table 'tbl_homepageids' as the engine to select the correct articles from the table 'tbl_press'. The recordset/SQL statement I am trying to create goes like this: SELECT * FROM tbl_homepageids, tbl_press WHERE fld_article1,tbl_homepageids LIKE fld_id,tbl_press I will create another two recordsets/SQL statements for the other two press articles using fld_article2 and fld_article3. Quite obviously the statement here isn't working and I have tried many different variations on the same theme, but haven't managed to achieve the results required, so I have written it in its simplest format hoping somebody can understand what I need and help me through this trying period in my life!! TIA Mat /Matthew Hi Mat, There are two ways to approach this issue. You chose to make one record with 3 columns as the specifier of which articles become your headlines. To get the 3 articles from tbl_press you will have to check the ID columns against all 3 values. Now there are multiple ways to do this but this one is less version specific: SELECT tp.* FROM tbl_homepageids h, tbl_press tp WHERE tp.fld_ID IN (h.fld_article1, h.fld_article2, h.fld_article3) While this method works there are several things to remember: 1. You will get 3 articles back for every 1 full row in your headlines table. 2. Your headlines table is limited to just 3 articles per row. Changing that limit will require a change to your database. If you redesigned your headlines table to have 1 column in it, say fld_articleID then you could have 1 article or 1000 articles on your front page without needing to change your design. That would also simplify your query to get the articles for the front page to: SELECT tp.* FROM tbl_homepageids h, tbl_press tp WHERE tp.fld_ID = h.fld_articleID -or you could write it this way, too- SELECT tp.* FROM tbl_press tp INNER JOIN tbl_homepageids h ON tp.fld_ID = h.fld_articleID Best wishes! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range query on datetime with index - any optimization?
Should work fine. Quick test from a large sales test table (1.9 million rows): mysql select count(*) from sali; +--+ | count(*) | +--+ | 1983026 | +--+ 1 row in set (0.00 sec) mysql select salidate,sum(saliQtySold) from sali where salidate DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by to_days(salidate); +-+--+ | salidate| sum(saliQtySold) | +-+--+ | 2004-03-17 13:44:37 |2 | | 2004-03-24 14:40:33 |2 | +-+--+ 2 rows in set (0.00 sec) mysql explain select salidate,sum(saliQtySold) from sali where salidate DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) group by to_days(salidate); +---+---+---+--+-+--+--+-- + | table | type | possible_keys | key | key_len | ref | rows | Extra| +---+---+---+--+-+--+--+-- + | sali | range | saliDate | saliDate | 8 | NULL | 70 | Using where; Using temporary; Using filesort | +---+---+---+--+-+--+--+-- + 1 row in set (0.00 sec) mysql - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Pete McNeil [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 3:00 PM Subject: Re: Range query on datetime with index - any optimization? On Wed, May 05, 2004 at 10:53:13AM -0400, Pete McNeil wrote: Hello folks, I'm usinng MySQL 4.0.17. I have a table something like: RuleID int, GMTBase datetime, Credited bigint, ... I have an index built on GMTBase. I have rougly 8 million rows. GMTBase stores a datetime for the top of the hour on a given date. I want to build a summary of the last 2 days without scanning every record. It appears that there is no way to get MySQL to use the index on GMTBase to avoid scanning all 8 million rows. I estimate it should only scan about 267K rows. Explain mentions the GMTBase index but says it will examine about a million rows. That seems to roughly match my estimate of the number of distinct GMTBase values. The query I want to run is: select RuleID, GMTBase, sum(Credited) from RuleHistograms where GMTBase DATE_SUB(CURRENT_DATE, INTERVAL 2 DAY) group by GMTBase Have I done something wrong or is there simply no way to avoid scanning all of those records? *Something* is wrong. I'm virtually certain I've done this sort of thing before without having MySQL perform full table scans. I can't tell what it is off the top of my head, but it should be that bad. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.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: can't load library
Hi Bob, I don't use openBSD, but from the FreeBSD ldconfig man page I have: -m Instead of replacing the contents of the hints file with those found in the directories specified, ``merge'' in new entries. Directories recorded in the hints file by previous runs of ldconfig are also rescanned for new shared libraries. So I would guess you want ldconfig -m, or reboot your system and I am sure it will scan for new shared libraries. Best of luck. Ken - Original Message - From: Bob DeBolt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 8:12 PM Subject: can't load library Greets I have installed mysql 4.0.18 on an OpenBSD 3.4 box using the D/L from the mysql.com site. All compiled / installed well and in fact nearly everything works. The problem I have is when attempting to use mysql I get the following error. mysql: can't load library ../libmysql/.libs/libmysqlclient.so.12.0 I have not come across this error before as the configure, make make installs have worked fine, not that I install mysql on a real regular basis at this point. The library does exist but I am curious as to why mysql tries looking through the installation directory. ldconfig -r doesn't reveal the presence of this library though. I know this is an extremely simple fix, just need to know which buttons to push. Sincerely Bob DeBolt -- 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]
Hash Index
This one is more curiosity than a problem. I have read the docs about HASH indexes and how they are used, but I'm just wholly unfamiliar with WHAT a HASH index is. I'm only familiar with the term 'hash' as it relates to encryption. What exactly IS a hash index? Just curious, Lou
Fulltext searching
Ok having some problems with MySQL's fulltext search. I have the fields that I need fulltext indexed, everything seems to be working correctly, but for some reason when I search for beyond looking for an item called: Beyond Heaven yoga Day Spa It doesn¹t find it.. I am searching in boolean mode by the way. but if I search for heaven it does find it. I have no idea why it will not find that first word... Actually it finds it when using all the words, except when I use beyond Now it just occurred to me.. if there is only one result.. will it not show that there was a result? Or is it something completely different? Thanks! Rick What lies behind us and what lies before us are tiny matters compared to what lies within us. - Oliver Wendell Holmes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
use LIMIT and get total count
Hi, In MySQL, I do: mysql SELECT * FROM my_table WHERE myconditions LIMIT 10; It retrieves 10 rows of data that meets 'myconditions' from my_table. Is it possible to get the total row count that meets 'myconditions' without another query? Thanks. Dexin __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hash Index
Lou Olsten wrote: This one is more curiosity than a problem. I have read the docs about HASH indexes and how they are used, but I'm just wholly unfamiliar with WHAT a HASH index is. I'm only familiar with the term 'hash' as it relates to encryption. What exactly IS a hash index? Just curious, Lou Lou: Are you familiar with a hash data structure? If not, you can read here: http://ciips.ee.uwa.edu.au/~morris/Year2/PLDS210/hash_tables.html or other pages that Google finds for hash data structure. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: use LIMIT and get total count
Yes Use SQL_CALC_ROWS And then SELECT FOUND_ROWS() -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: [EMAIL PROTECTED] Subject: use LIMIT and get total count Hi, In MySQL, I do: mysql SELECT * FROM my_table WHERE myconditions LIMIT 10; It retrieves 10 rows of data that meets 'myconditions' from my_table. Is it possible to get the total row count that meets 'myconditions' without another query? Thanks. Dexin __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- 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: Fulltext searching
Check to see if beyond is in your stopword file. -Original Message- From: Richard Baskett [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: MySQL Subject: Fulltext searching Ok having some problems with MySQL's fulltext search. I have the fields that I need fulltext indexed, everything seems to be working correctly, but for some reason when I search for beyond looking for an item called: Beyond Heaven yoga Day Spa It doesn¹t find it.. I am searching in boolean mode by the way. but if I search for heaven it does find it. I have no idea why it will not find that first word... Actually it finds it when using all the words, except when I use beyond Now it just occurred to me.. if there is only one result.. will it not show that there was a result? Or is it something completely different? Thanks! Rick What lies behind us and what lies before us are tiny matters compared to what lies within us. - Oliver Wendell Holmes -- 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: Newbie question: difference between MySQL shipped w/ Linux distro and downloading from mysql.com
[EMAIL PROTECTED] wrote: Hi folks. I've got MySQL running on a W2K dev box and want to run it on a production Linux box. Being new to Linux, I'm wondering what's the difference between the version of MySQL that ships with a Linux distro and downloading it from the MySQL website. By way of example: The SuSE website states that 9.1 Professional 64bit (will be running on an Opteron box) will ship with MySQL 4.0.18. They list the file path on DVD as /suse/x86_64/mysql-4.0.18-25.x86_64.rpm: it's 17.64MB in size. The MySQL website lists the file as MySQL-server-4.0.18-0.x86_64.rpm: it's 9.6MB in size. What's the difference between them? Should I use the SuSE version or download from the MySQL site? Does MySQL (the company) supply the RPMs to RH, SuSE, etc to include in their distros or do the distro companies build the RPMs themselves? MySQL is an open-source product. This means anybody can build and package a binary. MySQL AB does it, but so do the distributions like SuSE. For a low-cocurrency application, they are pretty much the same. However, a different build might make a huge difference in stability when the concurrency goes up. I have not yet research the AMD 64 binaries very much, so I cannot say which one is better. In the past, the MySQL team has put a lot of effort into making sure their binaries are absolutely the best on x86 Linux, and did a good job for other platforms. Based on what I've seen with the latest x86 binaries that came from MySQL AB, it appears to me that things have been slipping through the cracks a bit. So for x86, you might actually be better off with the SuSE binary. My uneducated guess about AMD 64 is that they would be about the same in both cases. If this is an issue of concern, try both and see which one performs better. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL on MacOSX
Dear folks, Right after installing from mysql-standard-4.0.18.pkg, I came up with the following: sudo /usr/local/mysql/bin/mysqld_safe Password: Starting mysqld daemon with databases from /usr/local/mysql/data 040506 07:13:11 mysqld ended As far as I read the instruction I have no clue if I missed out anything. Could anyone please give me suggestions? Thanx. Best, hAj http://advancedmeta-metaphorofapagan.net ||| ||| || || || || Seij minus aÇ http://seijminusac.net = '(music of) a negative scalar dimension w/in desolate silence' | | | | | | | gene of Cube http://geneofcube.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext searching
In my system variables it shown that the stopword file is (built-in) so where would I find it then if it is built in.. 'Beyond does not seem like a word that would be in a built-in stopword file does it? Could it be reserved? Thanks! Rick on 5/6/04 15:30, Dathan Vance Pattishall at [EMAIL PROTECTED] wrote: Check to see if beyond is in your stopword file. -Original Message- From: Richard Baskett [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: MySQL Subject: Fulltext searching Ok having some problems with MySQL's fulltext search. I have the fields that I need fulltext indexed, everything seems to be working correctly, but for some reason when I search for beyond looking for an item called: Beyond Heaven yoga Day Spa It doesn¹t find it.. I am searching in boolean mode by the way. but if I search for heaven it does find it. I have no idea why it will not find that first word... Actually it finds it when using all the words, except when I use beyond Now it just occurred to me.. if there is only one result.. will it not show that there was a result? Or is it something completely different? Thanks! Rick What lies behind us and what lies before us are tiny matters compared to what lies within us. - Oliver Wendell Holmes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext searching
If you've built from source, the stopwords are in path-to-source/myisam/ft_static.c In my copy of 4.0.18, beyond is in the list. You can create your own stopword list, or turn off stopwords altogether, if you want. See http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html for the details. Michael Richard Baskett wrote: In my system variables it shown that the stopword file is (built-in) so where would I find it then if it is built in.. 'Beyond does not seem like a word that would be in a built-in stopword file does it? Could it be reserved? Thanks! Rick on 5/6/04 15:30, Dathan Vance Pattishall at [EMAIL PROTECTED] wrote: Check to see if beyond is in your stopword file. -Original Message- From: Richard Baskett [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 2:12 PM To: MySQL Subject: Fulltext searching Ok having some problems with MySQL's fulltext search. I have the fields that I need fulltext indexed, everything seems to be working correctly, but for some reason when I search for beyond looking for an item called: Beyond Heaven yoga Day Spa It doesn¹t find it.. I am searching in boolean mode by the way. but if I search for heaven it does find it. I have no idea why it will not find that first word... Actually it finds it when using all the words, except when I use beyond Now it just occurred to me.. if there is only one result.. will it not show that there was a result? Or is it something completely different? Thanks! Rick What lies behind us and what lies before us are tiny matters compared to what lies within us. - Oliver Wendell Holmes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Directory Permissions on files
Greetings, I've recently inherited a FreeBSD server running MySQL 3.23.54. It's good and stable. I have a second server that runs as a slave to the first. Everything goes smoothly until I make changes to a certain table on my master. This will kill the slave with the error that this table is read-only. These are all MyISAM tables. I noticed recently that the various directories have different permissions and access levels on them and wondered what the correct levels should be. And...does this even have an effect on whether the table can be written to? Thanks in Advance = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing MySQL on MacOSX
hAj wrote: Dear folks, Right after installing from mysql-standard-4.0.18.pkg, I came up with the following: sudo /usr/local/mysql/bin/mysqld_safe Password: Starting mysqld daemon with databases from /usr/local/mysql/data 040506 07:13:11 mysqld ended As far as I read the instruction I have no clue if I missed out anything. Could anyone please give me suggestions? Thanx. This is probably one or both of two things: 1. The data directory is owned by root instead of by mysql. The fix: cd /usr/local/mysql sudo chown -R mysql:mysql data 2. At least a couple Mac OS X updates set incorrect permissions on /tmp (/private/tmp, actually) such that mysqld cannot create its socket. The fix: cd /tmp sudo chmod 1777 . Check both of these, then cd /usr/local/mysql sudo -v sudo bin/mysqld_safe If mysql still fails to start up, check /usr/local/mysql/data/hostname.err, where hostname is the name of your Mac, for the reason. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext searching
on 5/6/04 16:53, Michael Stassen at [EMAIL PROTECTED] wrote: If you've built from source, the stopwords are in path-to-source/myisam/ft_static.c In my copy of 4.0.18, beyond is in the list. You can create your own stopword list, or turn off stopwords altogether, if you want. See http://dev.mysql.com/doc/mysql/en/Fulltext_Fine-tuning.html for the details. I just turned it off and it looks like it's working again. I couldn¹t find the source.. so my guess is that it's a binary... and I guess built in? Once I set the variable in the my.cnf file to '' and reindexed the fulltext fields, everything looks to be working the way I needed it to work! So thank you for sending me in the right direction.. you have saved me a LOT of time! :) Cheers! Rick Work like you don't need the money. Dance like no one is watching. And love like you've never been hurt. - Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Conference Presentations
Where's the presentations? They were supposed to be on the website at the end of last week. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Features for Evaluation
Hi, I am setting up a lab to evaluate main features of MySQL so that provide advise for other departments to consider for their choice of database. Do you have a list of such features? Or do you have similar evaluation reports for my reference? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Features for Evaluation
There's a bunch. Check out: http://dev.mysql.com/doc/mysql/en/Introduction.html HTH, Robert J Taylor [EMAIL PROTECTED] On Thu, 2004-05-06 at 20:09, [EMAIL PROTECTED] wrote: Hi, I am setting up a lab to evaluate main features of MySQL so that provide advise for other departments to consider for their choice of database. Do you have a list of such features? Or do you have similar evaluation reports for my reference? Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New DBManager 2.3.0 is Released
We are pleased to announce the release of the new DBManager 2.3.0. This versions is not only a bug fix but it has new features which make the application more powerful. Starting in this version it's possible to manage MySQL servers through webservices (and soon for other engines). If you have a database hosted on an ISP and for security reasons the ISP doesn't allow remote connections, this feature is for you. With the setup we are distributing the webservice scripts in CGI/C (binary and sources) and PHP so it will be possible to choose the one that fits for each case. The connection is made through HTTP protocol, so it will only require a TCP/IP connection between your workstation and the webserver. All the usual operations are available. So you can, - Manage the structure and data of databases, tables, indexes, columns, etc - Create and Execute Queries graphically - Import Data from MSAccess, MSExcel, ODBC, Paradox, Xbase, Text Files and XML files (through disk file, web server or script on a web) - Export data to a lot of formats - etc. What's new in this version? - New XML Import Wizard which can be useful to import XML data from a disk file, webserver, or script on a webserver using parameters. - New MySQL Webservices management (soon for PostgreSQL and SQLite engines) - Filter and Order now remembers the last options used - Insert NULL values to columns in the Datasheet View - The application can be minimized to the System Tray - It now uses User Profiling allowing for users of the same computer to share the same installation without sharing the catalog information - And a lot of improvements and fixes For more details about the DBManager check http://www.dbtools.com.br/EN/dbmanagerpro.php For more details about the changes please check http://www.dbtools.com.br/EN/article.php?id=00035. To download this version http://www.dbtools.com.br/EN/downloads.php Best Regards, Crercio O. Silva DBTools Software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
