RE: Problem with master/slave replication
From: B. Keith Murphy [mailto:[EMAIL PROTECTED] show slave status after it stops copying should give you some type of error information about the failure. What does it show? In addition to the error, what do the following fields show? Slave_IO_Running Slave_SQL_Running Seconds_Behind_Master -- Mike Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL - Replication (Master/Slave) Question
From: Dan Rogart [mailto:[EMAIL PROTECTED] On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote: Correction to a couple of replies I've seen -- a slave server can have more than one master, but not to the same database. That is, Slave reads Database1 and Database3 from Master1 and also reads Database2 from Master2. You may actually be able to get down to the table level, but I'd have to check on that. Not likely, though. As for how to set it all up, don't ask me. I just enjoy the results. :) (apologies if you get a dupe, Baron -- I accidentally hit reply, not reply-to-all) I would be very interested in hearing more about how you set this up, because as far as I know it's impossible for a slave to have more than one master at any given time. Are you using some kind of time based rotation that changes the master info on the slave periodically or something? So, I looked into our my.cnf and it turns out that I was wrong. My apologies. Where I was misled was that we're doing a sort of pass-through replication. That is, Server1 replicates Database1 and Database3 to Server2, and Server2 then replicates Database1, Database2, and Database3 to Server3. Sorry to have spouted misinformation! -- Mike Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL - Replication (Master/Slave) Question
Correction to a couple of replies I've seen -- a slave server can have more than one master, but not to the same database. That is, Slave reads Database1 and Database3 from Master1 and also reads Database2 from Master2. You may actually be able to get down to the table level, but I'd have to check on that. Not likely, though. As for how to set it all up, don't ask me. I just enjoy the results. :) (apologies if you get a dupe, Baron -- I accidentally hit reply, not reply-to-all) -- Mike Johnson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recursion or something recursion-esque
This one may end up dead in the water, but I figured I'd run it past the group as I've seen some pretty creative solutions in my time here. Let's say I have a table like this: ++---+ | id | parent_id | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 2 | | 4 | 0 | | 5 | 1 | | 6 | 2 | | 7 | 1 | | 8 | 3 | | 9 | 8 | | 10 | 5 | ++---+ id is the primary key and parent_id refers to this table's id. That is, 3 is a child of 2 and 8 is a child of 3. What I'd love to do is pull all children (and grandchildren, etc) per each, such that I'd end up with the following result set or something like it: +++ | id | children | +++ | 1 | 5, 7 | | 2 | 3, 6, 8, 9 | | 3 | 8, 9 | | 4 || | 5 | 10 | | 6 || | 7 || | 8 | 9 | | 9 || | 10 || +++ Say there's more to this table than what you see, and say it's a lookup table to a larger table. If I'm querying on everything in that larger table that's 2 here, I'd like it to be able to actually pull anything that's 2, 3, 6, 8, or 9. The obvious solution is to parse out an array of that ahead of time and use it (1 = (5, 7), 2 = (3, 6, 8, 9), etc), but let's pretend this is an annoyingly complex Perl suite and if I can just manipulate queries I'll be a whole lot happier. Any thoughts? I feel like the solution is either remarkably simple or frustratingly difficult. Thanks in advance if you can help! -- Mike Johnson Smarter Travel Media LLC Applications Developer http://www.smartertravel.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 and unions
Hi all, Is there anyone using version 4.1 that can verify or refute the claim posted at the bottom of the upgrade notes here? http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html The user claims that UNION statements with parentheses are broken in 4.1. That is, 'SELECT * FROM table1 UNION (SELECT * FROM table2)' or something similar. We're looking to upgrade but find te claim a bit dubious as there's no follow-up, no other mention, and I can't recall seeing anything on the list about it. Can anyone perform a quick test or verify one way or the other? I'd be much obliged. Thanks! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1 and unions
From: Scott Noyes [mailto:[EMAIL PROTECTED] UNION is not broken; the syntax in the given comment is incorrect. You can do SELECT * FROM table UNION SELECT * FROM table or (SELECT * FROM table) UNION (SELECT * FROM table) but not SELECT * FROM table UNION (SELECT * FROM table) Oh, interesting. My next question was going to be if the second syntax is correct (all subqueries enclosed in parens), but looks like it's good. Thanks, Scott. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? It's good DB design, but you need to not `select *' but the specific fields you'd like. An example of might be: SELECT s.name, s.age, a.street_name, n.num, n.extension FROM students s JOIN address a ON a.student_id = s.student_id JOIN phone_num n ON n.student_id = s.student_id HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
From: Mike Johnson [mailto:[EMAIL PROTECTED] From: Koon Yue Lam [mailto:[EMAIL PROTECTED] the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? It's good DB design, but you need to not `select *' but the specific fields you'd like. An example of might be: SELECT s.name, s.age, a.street_name, n.num, n.extension FROM students s JOIN address a ON a.student_id = s.student_id JOIN phone_num n ON n.student_id = s.student_id I just realized I sort of misread your question (or, rather, only read the first half of it). I guess my question is whether or not you're using some sort of front-end scripting language to retrieve results or using the MySQL client straight. If the former, you can definitely work with the data in the way you'd like, but as for the latter, the MySQL client itself wasn't actually meant to be used as any sort of reporting tool. It certainly isn't made to make data look nice. :) Might you be using PHP, Perl, or something else like that? -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
same-table join
I think I'm just having a brain fart here, but if someone could help me out I'd appreciate. Maybe it's just too late in the day for thinking... Say I have a table `actions` with a structure and data as such: ++++ | id | userid | action | ++++ | 1 | a | foo| | 2 | a | bar| | 3 | a | baz| | 4 | b | foo| | 5 | c | foo| | 6 | c | bar| | 7 | d | foo| | 8 | d | bar| | 9 | d | baz| ++++ What I want to do is return all values of `userid` that have a 'foo' action but not a 'baz' action (those being b and c). I know I know the answer to this, but I'm drawing a blank at the moment. Thanks in advance if someone could give me a hand. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Performance
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED] Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! The first thing I'd do is index the `result` field, as you're checking against it in the WHERE clause. However, if I remember indexing behavior correctly, that won't help if you only have a a few unique values in that column. Give it a shot, though, I imagine it'd definitely help. If that doesn't drastically improve it, I'd also look into a way around performing the date and time functions in the query. I don't know if that's possible, but depending on what this is feeding to (most likely PHP or Perl), it may be quicker to do those calculations in the wrapping script (if there is one, that is). HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Performance
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED] Hi Folks! Here, I have the following table: CREATE TABLE `accesses` ( `time` varchar(15) NOT NULL default '', `duration` int(10) default NULL, `user` varchar(25) NOT NULL default '', `ipaddr` varchar(15) NOT NULL default '', `result` varchar(30) default NULL, `bytes` int(10) default NULL, `reqmethod` varchar(10) default NULL, `urlparent` varchar(100) NOT NULL default '', KEY `usuario` (`usuario`), KEY `time_result` (`time`, `result`) ); If my table has a great number of rows (something like 5 millions), the result time is too much longer. select user, count(distinct concat(date_format(from_unixtime(time), %d/%m/%Y), - , time_format(from_unixtime(time), %H:%i)), ipaddr, urlparent) as qtd, sec_to_time(sum(duration)/1000) as duration, sum(bytes) as bytes from acessos where time = 1109646000 and time = 1112324399 and result TCP_DENIED/403 group by user order by user; PS: explaining this select, the time_result key is a possible_key, but, in the key field I have the NULL value (the NULL persists even if I force with use index()). Can anybody help me what can I do to make this query faster (indexes, tuning, or, change the table structure or the query). Thank you ! Oh, I'm sorry. I read your CREATE statement too quickly the first time and didn't notice that the `time_result` index was across both `time` and `result`. In that case, indexing `result` separately may not help at all. Might be worth a shot, though, if you have the disk space and time to play around with it. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Passing Arrays between pages
From: PartyPosters [mailto:[EMAIL PROTECTED] Hello, Please can someone tell me how you pass arrays between PHP pages. I have tried something like this and have tried many variations of this but nothing working yet ; Page 1 $testArray[0] =Apple; $testArray[1] =Banana; $testArray[2] =Peach; echo INPUT NAME = \kcompany[]\ TYPE = \hidden\ VALUE=\$testArray\; Page2 echo $testArray[1]; This is more of a PHP question, and I'm sure you'll be admonished for it, but I'll answer it anyway. Essentially, you need to construct the array elements as their own hidden values. echo INPUT NAME = \kcompany[]\ TYPE = \hidden\ VALUE=\Apple\; echo INPUT NAME = \kcompany[]\ TYPE = \hidden\ VALUE=\Banana\; echo INPUT NAME = \kcompany[]\ TYPE = \hidden\ VALUE=\Peach\; At that point, if you call: echo $kcompany[1]; after the form submit, you'll get Banana. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE Opposite
From: shaun thornburgh [mailto:[EMAIL PROTECTED] Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help SELECT ... INTO OUTFILE is what you want. It's sort of covered in the SELECT syntax documentation: http://dev.mysql.com/doc/mysql/en/select.html Pretty handy at times. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATE problem
From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql =SELECT COUNT(table.id) FROM table WHERE table.created = NOW(); $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's MYSQL equivalent to Oracle's TRUNC(date_time_var)?
From: sol beach [mailto:[EMAIL PROTECTED] In Oracle TRUNC(data_time_var) returns only the date portion of a date_time data type. What's the easiest way in MYSQL to accomplish the same thing? I tried to RTFM (Paul DuBios' MYSQL tome; 2nd Ed.), but no clean solution lept out at me. It is a GREAT book, but it did not help me in this case. TIA! Try this: SELECT DATE_FORMAT(date_time_var, '%Y-%m-%d') You can customize what fields you'd like in the second arg. Above will return -MM-DD. More here: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Also, as of 4.1.1, you can use DATE(date_time_var) to return the date portion. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Suppress the 0 value
From: Martin Rytz [mailto:[EMAIL PROTECTED] Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? SELECT IF(id = 1, NULL, id) AS id ... The usage of IF() is IF(expression, true value, false value). -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Suppress the 0 value
From: Mike Johnson [mailto:[EMAIL PROTECTED] From: Martin Rytz [mailto:[EMAIL PROTECTED] Hi MySQL-Users I have a simple select statement like 'select id from table'. The result is 0, becaues the id field is 0 (int-field). My problem is now how to suppress the 0 and give NULL instead of 0 as the result (i.E. everytime the result from the select is 0 it should be NULL). How can this be done within the select-statement? SELECT IF(id = 1, NULL, id) AS id ... The usage of IF() is IF(expression, true value, false value). Oops, my mistake. Should have been IF(id = 0, NULL, id). I was testing on a table without `0' values. Sorry for the mix-up. -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a DATETIME Query Please Using 3.23.54
From: shaun thornburgh [mailto:[EMAIL PROTECTED] Hi, The following query works out the amount of hours a user has been booked for on 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 Hours_Booked FROM Bookings B WHERE B.User_ID = 1 AND NOT ( 2004-11-01 DATE_FORMAT(B.Booking_Start_Date, %Y-%m-%d) OR 2004-11-01 DATE_FORMAT(B.Booking_End_Date, %Y-%m-%d) ) ; However it fails if the booking spans more than one day. For example I have a booking that starts at 9am on 2004-10-31 and ends at 10am on 2004-11-02, and for this the query returns one hour, when it should be 24! Any pointers regarding this would be most apprecited %k returns, simply, the hour value of the date in question. In this case, you're subtracting 9 from 10, which is where the 1 comes from. While there may be an easier way, I'd start with converting both dates to Unix format, subtracting, and then doing the division to get # of days. Something like... SELECT (UNIX_TIMESTAMP(B.Booking_End_Date) - UNIX_TIMESTAMP(B.Booking_Start_Date)) / (60 * 60); Given values of '2004-11-01 10:00:00' and '2004-10-31 09:00:00', you end up with 25 (which I think is what you were looking for, not 24). As I said, there may be a more elegant way to do that, but this is the brute-force method that should work. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smarterliving.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Converting date in MySQL
From: Jeff Smelser [mailto:[EMAIL PROTECTED]
On Thursday 14 October 2004 03:35 pm, Stuart Felenstein wrote:
No :),. cause it seems that those formats are for
outbound, db -.
I was looking for the other direction.
Huh? It really doesnt matter does it? They work either way..
I use those functions all the time for inbound..
It does matter, though. You can't use DATE_FORMAT() to translate
'10/14/2004' into '2004-10-14.'
It looks like what the poster wants is STR_TO_DATE() (a la
STR_TO_DATE('10/14/2004', '%m/%d/%Y'), but that's not available until
MySQL 4.1.1.
Out of curiosity, how /would/ you do this? I'm assuming you're not using
STR_TO_DATE() (as I didn't even know it existed until I just checked),
though I may be incorrect.
As the last poster said, if you're not using 4.1.1, you're better off
setting the format in the calling script. You'd assumedly need to do
some error-checking, anyway.
--
Mike Johnson Smarter Living, Inc.
Web Developerwww.smarterliving.com
[EMAIL PROTECTED] (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sanity Check : Error in sql
From: Stuart Felenstein [mailto:[EMAIL PROTECTED] I'm running into a problem here where mySQL is saying unknown column. Education_table TypeID Type HS High School CO College MA Masters SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' I tried adding where typeid = type but that makes not sense. I'm now wondering and running (after I post) if type is a no-no-word. Try wrapping Value in single- or double-quotes in your code. It sounds like it's essentially generating this query: Select * from Education_table where TypeID = HS; instead of... Select * from Education_table where TypeID = 'HS'; HTH! -- Mike Johnson Smarter Living, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: distinct based on two fields--please help
From: Claire Lee [mailto:[EMAIL PROTECTED] I have a table like this name price type A10 1 B30 2 A20 1 B20 2 would like to distinct based on name and type, do a sum and get the following result name price type A 30 1 B 50 2 How do I do this with one sql query? Thanks. SELECT name, SUM(price), type FROM tablename GROUP BY name, type; -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create table syntax question
From: eifion herbert (IAH-C) [mailto:[EMAIL PROTECTED]
Hi,
Probably a very basic question.
I'm trying to a create a table in mySQL 4.0.15 thus:
create table VACANCIES(
VACREF char(6) NOT NULL PRIMARY KEY,
TITLE varchar(60),
LOC varchar(9),
DESC text,
STARTDATE date,
GROUP varchar(25),
CONSTRAINT fk_grp FOREIGN KEY (GROUP) REFERENCES GROUPS(GPNAME),
CONSTRAINT chk_loc CHECK (LOC in ('Loc1', 'Loc2', 'Loc3'));
And it says I have a syntax error. I've been through the manual and
can't spot what I've done wrong. Anyone?
I'm willing to bet that it's because DESC is a reserved word (an abbreviation of
DESCRIBE, used to show a table structure).
Either change the name of that column or wrap it in backticks (`DESC`) whenever it's
referenced.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL -- SQL syntax error.....
From: Scott Fletcher [mailto:[EMAIL PROTECTED]
When I use this SQL statement, ...
--snip--
UPDATE BUSINESS_CATEGORY SET
(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) =
('JUNKKK','JUNK123KK') WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--snip--
I get the SQL syntax error saying,
--snip--
You have an error in your SQL syntax. Check the manual that
corresponds
to your MySQL server version for the right syntax to use near
'(BUSINESS_CATEGORY.BUS_CAT,BUSINESS_CATEGORY.BUS_DESC) = ('JUNK
--snip--
So, I looked up in MySQL's documentation at
http://dev.mysql.com/doc/mysql/en/UPDATE.html, it didn't say anything
helpful about the SQL syntax. So, what did I do wrong??
I don't think MySQL supports using parens in that regard.
Try this:
UPDATE BUSINESS_CATEGORY SET
BUSINESS_CATEGORY.BUS_CAT = 'JUNKKK',
BUSINESS_CATEGORY.BUS_DESC = 'JUNK123KK'
WHERE BUSINESS_CATEGORY.BUS_CAT_ID = '733788'
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very Strange data corruption
From: Jeff McKeon [mailto:[EMAIL PROTECTED]
Query:
insert into
MIS.simcard(ID,ShipID,Service_Provider,SN,v1,v2,f1,d1,puk1,puk
2,pin1,pin
2,TwoStage,Status,DateAssigned,DateDisabled,UserID)
VALUES('NULL', '6889927707', '1', '8988169214000421398',
'881621456175',
'', '', '881693156175', '62982149', '', '', '', '1307', '1',
'1085508771', 'NULL', 'jsm');
Always results in a ShipID field value of 2147483647 instead of
6889927707
Even if I just do a simple:
insert into MIS.simcard (ShipID) values ('6889927707');
It does the same darn thing.
ShipID is an Int(11) field
Version 4.0.15
If I change the first digit of the input from a 6 to any
other digit, it
gets entered correctly. Any idea what is going on here!?
Version 4.0.15
The max value of INT is 2147483647, lower than the value you're inserting (even when
unsigned, which is 4294967295). That's why that's what's getting inserted.
Manual page is here:
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
Try converting the column to a BIGINT, the signed max alone is 9223372036854775807.
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very Strange data corruption
From: David Griffiths [mailto:[EMAIL PROTECTED] MySQL really should throw an exception/error rather than just quietly trim your data and accept it. When your data is critical, and your business depends on it, you can't have bad data quietly going into the database. Someone correct me if I'm wrong, but isn't it considered best practice to validate data before it gets to the database? I can't seem to find a source for this after a quick search on Google, though... -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very Strange data corruption
From: Steve Meyers [mailto:[EMAIL PROTECTED] David Griffiths wrote: I'm not sure what the sql standard says on the matter, but Oracle, DB2 and Postgres would through an exception. In fact, there is a page on MySQL gotachs to document MySQL behaviour when it differs significnatly from other databases (like the first datetime field in a table getting a value if none is provided during insert). I assume you mean timestamp, not datetime. Not trying to be picky, I just don't want to confuse anyone out there. While we're being picky, then, the first timestamp field is actually updated on any INSERT or UPDATE, not just given a value on the initial INSERT. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php mysql problem
From: Jianping Zhu [mailto:[EMAIL PROTECTED] but i got error message with: http://coopunit.forestry.uga.edu:8080/testdb.php the error is: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/testdb.php on line 13 How can Fix this problem? Thanks Sounds as though PHP was installed without the MySQL library. I haven't done it myself in a while, but I believe it requires the --with-mysql flag. HTH -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need correct 'order by' syntax where field does not contain NULL
From: Eve Atley [mailto:[EMAIL PROTECTED]
Hi. I had a MySQL DB set up and recently added a field
'order' to allow for exceptions in a web site menu
heirarchy. Fields should be ordered by 'order' field
first where it does not contain 'NULL', and then by
field 'title'. I had this previously:
select * from navigation WHERE id = '.$category.' AND
active='y' ORDER BY title
...and now, when I put in:
select * from navigation WHERE id = '.$category.' AND
active='y' ORDER BY order, title
...my menu shows nothing. How can I write my statement to
allow for ORDER by order where 'order' does not contain
'NULL', and then title?
I'm curious about what you're using for a MySQL client. Are you using a web-based tool
such as phpMyAdmin? If so, it probably added this new column using backticks, which
allowed a reserved word (order) to be used as the name. I'm fairly certain that's why
your menu is not displaying anything -- your query is dying when it gets to the ORDER
BY clause.
So first step, in your query, add backticks around order:
ORDER BY `order`, title
That'll allow MySQL to see it as a column name and not a reserved word.
My second point is that I saw two replies to this, both of which said something along
the lines of:
SELECT * FROM navigation
WHERE id = '.$category.'
AND active='y'
AND order IS NOT NULL
ORDER BY order, title
...which is blatantly wrong. You asked for all records matching your original WHERE
clauses, order first by the 'order' column if not null, and then by 'title' if 'order'
is null. This query above will not return records for which 'order' is null.
While it may not be the best way to go about it, give this a shot:
SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord
FROM navigation
WHERE id = '.$category.'
AND active='y'
ORDER BY sort_ord ASC, title
This generates a temporary column in your result set that is used as the sort order
based on the values of 'order.' Note that if you use values higher than 99 in 'order,'
you'll want to set the 99 higher.
For an example, try this out:
===
mysql CREATE TABLE test (title VARCHAR(10), `order` INT);
Query OK, 0 rows affected (0.01 sec)
mysql INSERT INTO test (title, `order`) VALUES ('a', NULL);
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO test (title, `order`) VALUES ('b', NULL);
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO test (title, `order`) VALUES ('c', 1);
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO test (title, `order`) VALUES ('d', NULL);
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO test (title, `order`) VALUES ('e', 3);
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO test (title, `order`) VALUES ('f', 2);
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM test;
+---+---+
| title | order |
+---+---+
| a | NULL |
| b | NULL |
| c | 1 |
| d | NULL |
| e | 3 |
| f | 2 |
+---+---+
6 rows in set (0.00 sec)
mysql SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM test ORDER BY
sort_ord ASC, title ASC;
+---+---+--+
| title | order | sort_ord |
+---+---+--+
| c | 1 |1 |
| f | 2 |2 |
| e | 3 |3 |
| a | NULL | 99 |
| b | NULL | 99 |
| d | NULL | 99 |
+---+---+--+
6 rows in set (0.00 sec)
===
If someone knows a better way to do this, I'd be curious to hear it. I imagine there's
a more efficient way to do it, but I can't seem to stumble upon it.
Anyway, hope this helps. :)
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WHERE clause problem
From: mayuran [mailto:[EMAIL PROTECTED] Here is my question: The following query returns incorrect rows and I dont understand why. mysql SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) The first one, while it may look valid, isn't. `MONTH(NOW())' currently evaluates to 5. `MONTH(NOW())-1' currently evaluates to 4. As such, your first query is essentially the following: SELECT * FROM testing WHERE MONTH(date) = (5 OR 4); Which becomes: SELECT * FROM testing WHERE MONTH(date) = 1; `(MONTH(NOW()) OR MONTH(NOW())-1)' aka `(5 OR 4)' evaluates to 1, because it's just ORing two integers. Note that it's returning exactly what it's supposed to be returning, the date in January. So while it may look like it makes sense in pseudo-code, on paper it's not how MySQL evaluates things. It builds the right side of the equality and then compares. Stick with your second query and you'll be fine, even if it is more typing. ;) HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urban myth?
From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] My boss says that if you do a select statement against a table the result set always comes back in the same order. I say that this is a myth and that the result is random, except when some ordering is specified in the SQL statement. Who is right? Is this behavior specified by ANSI or ISO? From what I understand (as I've heard this explained a half dozen times by different people), the results are returned in the order they appear on disk. This can change via any number of factors (such as a disk defrag), though, so you should never rely on getting results back in the same order every time if you're not using an ORDER BY clause. For the most part, though, results /will/ appear to come back in the same order. I imagine that's what your boss is talking about. It's definitely not to be relied on, though. If this is documented online, I've yet to see it (though I haven't exactly gone looking for it). Someone else may have a more accurate description of the way it's done, though. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query help
From: Richard Reina [mailto:[EMAIL PROTECTED] I have two tables: EVENT IDname date sponsor_ID 23 Sady Hawkins 2004-11-04 235 89 Founders Day 2004-12-21 NULL 87 Winter Gala 2004-01-23 NULL SPONSOR ID name 235 George Suter 34 William Riggs 896Lidia Bronson I am having trouble writing a query that joins both tables but will pull up an event even if it DOES NOT have a sponsor. SELECT FROM e.name, e.date, s.name FROM event e, sponsor s WHERE e.sponsor_ID = s.ID This query works only for those events that have a sposor. Any help would be greatly appreciated. You need to use a LEF JOIN instead of the default: SELECT e.name, e.date, s.name FROM event e LEFT JOIN sponsor s ON e.sponsor_ID = s.ID HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: re-using a results set
From: Scott Swaim [mailto:[EMAIL PROTECTED] Thank you Chris for that answer. I did not know if it was a mysql problem or php problem. Again I apologize (especially to Mike Johsnon) for the multiple request. I was getting a return error on the list and I thought it was a configuration error on my part. I did not mean to upset anyone. Thank you again for the answer and I will try to have better netiquette in the future. Ahh. I, in turn, apologize for my message. Quite often you see someone on here who, if a reply isn't received within an hour or two, will simply resend their message again and again until someone boils over. Sorry about my hasty reply. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: If() syntax question
From: Don Dachner [mailto:[EMAIL PROTECTED] Is it possible to do something like this? If(select * from xxx, if record found..update it, if record not found ..insert it) Try the REPLACE INTO syntax: http://dev.mysql.com/doc/mysql/en/REPLACE.html -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: re-using a results set
From: Scott Swaim [mailto:[EMAIL PROTECTED]
I have a form that does the following
? while ($speaker =
mysql_fetch_object($qid_speaker)) { ?
option value=?
pv($speaker-first_name) ? ? pv($speaker-last_name) ?
?
pv($speaker-first_name) ? ? pv($speaker-last_name) ?
? } ?
/select
$qid_speaker = db_query(SELECT first_name, last_name FROM
people WHERE speaker = 'Y' ORDER BY last_name);
I am using this in a form select drop down box.
What I need to do is reuse this results set in another drop
down box. but I can not figure out how to do this.
If I use the mysql_free-result($qid_speaker) and then try to
do the query again I get a mysql_fetch_object(): 12 is not a
valid MYSQL result resource.
You've now posted this four times today. Was that on purpose? I can only assume so, as
some people have absolutely no sense of netiquette whatsoever.
Why, you ask, has no one replied with the infinite wisdom to solve your problem? I,
for one, haven't because your original email is at least vaguely unintelligible. Have
I asked for more information so that I might be more helpful? No, I've been busy.
Posting over and over and over again, though, seems to have worked, so readers, take
note -- if at first you don't succeed, try, try again until you annoy someone into
listening.
*sigh*
That said, sir, could you possibly provide more details for your situation? Your code
is a bit jumbled. We have no idea what pv() does, for instance. Nor db_query(). These
are not standard PHP functions, so you'll have to forgive us if we're all a bit
clueless and unwilling to help.
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Security
From: Mulugeta Maru [mailto:[EMAIL PROTECTED] Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. As Paul DuBois said earlier, this is something you want to control in your application itself. You still haven't specified if you're actually using an application in this scenario, so I'm still assuming you're talking about giving the clients access to the native mysql client. Just as your bank gives you a web or executable client with which to access your records and transactions, they don't give you access to the database itself. One example I'm talking about is developing an application for the clients in PHP. It would take basic login information and from there keep track of what client it is. At that point, you have the CustomerID, so only display to them info pertinent to them (SELECT * FROM Customers WHERE CustomerID='$CustomerID'; SELECT * FROM Transaction WHERE CustomerID='$CustomerID'), thus only allowing them to update or view records /through the web app/ relating to them. So long as you never select records for Customer B, Customer A will never have the ability to view or modify Customer B's records. Does that make any more sense? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with IF acting wierd.
From: Amir Hardon [mailto:[EMAIL PROTECTED] I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? The problem is that in either case of the first IF, you're still joining on 3 tables when you really want two. If main.type is a category and the WHERE clause ends up evaluating to `WHERE categories.id=main.id' you're still joining on the items table unnecessarily, and vice-versa. Without another WHERE clause to limit it, you're getting the full cartesian product from the first two tables against the third. I don't think this works, but it's essentially what you want: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Note the extra IF in there to join only on /either/ items or categories. I never knew you could use an IF clause to specify a column in a WHERE clause, though, so maybe this will work. It's worh a shot at least. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with IF acting wierd.
From: Mike Johnson SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Oh, my mistake. I just realized I reversed items and categories in the IF clause. Try this instead: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,categories,items) WHERE IF(main.type,categories.id,items.id)=main.id; Sorry! Actually, now I'm really curious if this works or not. Let me know how it turns out. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Security
From: Maru, Mulugeta [mailto:[EMAIL PROTECTED] When I go online to access my bank account I only see transactions pertain to my account only. I think when ever I make a transaction the database records my account number in the transaction table. When I log-in using my account number and password the system checks whether it is correct or not and run another query to get all transaction that match my account number. Do I make sense? (sent offlist by mistake, please excuse the dupe) The point being made is that you're looking at your bank account information in a client that is set to read records only pertaining to your account. The native mysql client is not such a program and was never intended to be. While you can customize access for users to certain databases or certain tables within those databases, it's simply not built as a multi-user transactional client for limiting access to data in commonly-used tables. It begs the question why you're giving your clients access to the native mysql client itself rather than developing an application to do this, in which you could quite easily limit such access. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: disabling optimizations to identify slow queries
From: Bill Marrs [mailto:[EMAIL PROTECTED] I've found a performance issue with a series of mysql queries that I make to generate a web page. But, when I go to investigate it, reloading the page a few times, I find the performance of the pages within a couple tries becomes very fast. It sounds like query caching is working against you. There are a variety of ways to get around it. While it'll be a PITA, you may want to have you script call `RESET QUERY CACHE` at the begining, and then include `SQL_NO_CACHE` in your SELECT statement(s). More info here: http://www.mysql.com/doc/en/Query_Cache_Status_and_Maintenance.html And here: http://www.mysql.com/doc/en/SELECT.html HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I unsubscribe
From: Tim V [mailto:[EMAIL PROTECTED] How do I unsubscribe? This message should have a To unsubscribe link at the bottom (if you haven't already noticed it on every other message you receive). -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What field lengths to use
From: Adam Staunton [mailto:[EMAIL PROTECTED] Hi all, I just tried to change a field with a type of LongText to Text and with a width of 10 characters in phpmyadmin. It accepted the change of the field type top text but didn't seem to accept the change in field length to 10. To test , I entered 20 characters into a text field in my php/mysql database and it accepted it when I updated. What's wrong ? or is it not possible to assign actual field lengths in phpmyadmin ? The TEXT type doesn't have a user-set limit on it: http://www.mysql.com/doc/en/BLOB.html If all the records going in the column are 10 characters, use a CHAR(10): http://www.mysql.com/doc/en/CHAR.html HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539
RE: Just simple question...
From: Dusan Spisak [mailto:[EMAIL PROTECTED] Here is my question: Is it possible to use MySQL in the similar way to Access? Without any servers and administration and clients and controlcenter...? I don't need any network at all, I just would like to create and run some small database for home use. Is it possible in MySQL? And, it seems, there is no user interface in MySQL at all. Am I right? MySQL is a database server. However, Access is, too. It's just masked by Microsoft and bundled into a nice GUI (graphical user interface, if you're not familiar with the term) package. It's perfectly acceptable to install the MySQL server locally and only use it locally. It doesn't need to be accessible from the outside. There's a companion client for the server, but it's pretty bare-bones command-line stuff. If you're looking for any sort of GUI, you'll need a webserver installed locally as well. Any GUI I've seen for MySQL runs as a web application, usually written in PHP. As someone said before, I can highly recommend taking on the learning curve for MySQL. It's actually much easier than it may seem -- one of those easy to learn, a lifetime to master things. However, if you're turned off by having to either work on the command-line console or run a local webserver to use a GUI, I can't say that MySQL is necessarily what you're looking for. Good luck in your decision, though. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
From: Ed Curtis [mailto:[EMAIL PROTECTED] I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? This is untested, but I imagine you could do the following: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND changelog.orig_id IN ( pages.mls_1, pages.mls_2, pages.mls_3, pages.mls_4, pages.mls_5, pages.mls_6, pages.mls_7, pages.mls_8, pages.mls_9, pages.mls_10, pages.mls_11, pages.mls_12 ); -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What replaces IN?
From: Scott Purcell [mailto:[EMAIL PROTECTED] I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) IN has been supported for a while, but subselects have not. SELECT * FROM table1 WHERE item_id IN (1, 2, 3, 4, 5) should work, but not a subselect. I'm not sure of subselect syntax, actually, or what (recent) version in which it was introduced. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie query question...
From: John Croson [mailto:[EMAIL PROTECTED] I have a simple query: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND cat_id='2' OR cat_id='5' AND approved='1' ORDER BY year,month,day ASC Results: ++--+---+-++ | id | year | month | day | cat_id | ++--+---+-++ | 25 | 2003 |12 | 7 | 5 | | 44 | 2004 | 2 | 15 | 2 | | 53 | 2004 | 3 | 28 | 5 | | 45 | 2004 | 6 | 6 | 2 | ++--+---+-++ Can anyone point out where I've screwed up the query?? I don't think it should be giving me the first entry. It is a trivial problem, and I can filter the results further with php, but I'd like to do it right, and am trying to learn SQL. You problem is in that OR. Add a couple parentheses to get: SELECT id,year,month,day,cat_id FROM events LEFT JOIN calendar_cat ON events.cat=calendar_cat.cat_id WHERE year=YEAR(CURDATE()) AND month=MONTH(CURDATE()) AND day=DAYOFMONTH(CURDATE()) AND (cat_id='2' OR cat_id='5') ^^ AND approved='1' ORDER BY year,month,day ASC Previously, it was going through all the ANDs and then saying oh, /or/ I can grab rows with a catid of 5 (hence the inclusion of that first row). HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql error message
From: vincent.gueu [mailto:[EMAIL PROTECTED] I've installed mysql under Linux and what I do, I always have this number of error message :040204. Please I need your help! Thank u I have no idea what your problem is, but I'd be amused if you had the number 040205 tomorrow (those being today's and tomorrow's dates, respectively). Can you elaborate on the problem? Where do you get this error? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Read Slaves, and load balancing between them...
From: James [mailto:[EMAIL PROTECTED] I am interested in setting up 3 read only slaves and one write master. I am thinking of implementing this because one server is slower than 3 ;-). But anyway, I have read all the docs and I am fairly sure how I want do this and keep the sanity checking in the loop. What I am considering doing is checking (in intervals) the binlog positions between the slave and the master. I am worried about this being out of synch, for just about every 3 reads, there is a write. I am looking to see if this is a good idea, and what others comments/suggestions are. It all depends on how much traffic your db server is getting and how quickly you want to be notified if replication is behind or a slave thread died. Your idea of checking the binlog position between the slave(s) and master at an interval is a good one, though you run the risk of getting it at the wrong time, such as when a rather large update is being made, and being notified with a false positive. I have a very similar setup -- one master, two slaves. What I did was set up a Perl script to run in the cron every 5 minutes. It logs into each slave and performs a SHOW SLAVE STATUS. From this it looks at the Slave_IO_Running and Slave_SQL_Running columns to determine that the slave thread is still operating. If either is 'no' it pages me with the 'Last_error' column. This may not be optimal, as you sound as though you want to see if/when replication is out of sync and not just completely dead. I'd say give it a shot checking the binlog positions. If you find yourself getting a lot of false positives, try having the check script sleep for N seconds and check again to be sure of itself before notifying you, just to see if it needs to catch up. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Read Slaves, and load balancing between them...
From: James [mailto:[EMAIL PROTECTED] Mike Andrew, Excellent ideas... The reason I am considering checking the log location, is because normal lusers will be using this software, so it won't be too good for them to see errors about the slave being out of synch. (Some 20 people including my self are using this software.) This is something that the software will have to use some logic to figure out which slave is the best. I will have a list of read slaves that I will randomly call each program start, or upon error. Mike did bring up a good point, false positives. Has anyone done what I am talking about (checking bin log position)? Also will this method actually remove load from the servers? Thanks for your input so far. A model of a write master and several read slaves is almost guaranteed to reduce load caused by db bottlenecking. We've seen it here in our setup. As for false positives: if possible, try whipping up a lengthy UPDATE query (or LOAD DATA INFILE) that you know will take a while. Start it on the master and, while it's cranking, manually check the binlog position on the master and slave(s). I'm not sure if such a load will cause a temporary discrepancy, but it's the first thing that comes to mind. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Paging!
From: Adel Ardalan [mailto:[EMAIL PROTECTED] When I select a rather large table to be shown, the rows are going very fast and I can't see the rows at the beginning. Also, I can't scroll up. How can I view the results page by page? I can only assume you're talking about the native mysql client. In that case, there are a few suggestions to help you. If possible, first weed out rows you don't need with a WHERE clause. If there's still too much data, add a `LIMIT offset,count` clause to the end of the query. SELECT * FROM table LIMIT 0,30; SELECT * FROM table LIMIT 30,30; SELECT * FROM table LIMIT 60,30; etc Or, if possible, increase the scrollback buffer for your shell client (if you're shelled in, that is, and not on the server directly). As it stands, though, the native mysql client really isn't meant to be used to view such large amounts of data. Are you using any sort of frontend with this database? PHP, Perl, Java, etc? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Paging!
From: Mike Johnson From: Adel Ardalan [mailto:[EMAIL PROTECTED] When I select a rather large table to be shown, the rows are going very fast and I can't see the rows at the beginning. Also, I can't scroll up. How can I view the results page by page? I can only assume you're talking about the native mysql client. In that case, there are a few suggestions to help you. If possible, first weed out rows you don't need with a WHERE clause. If there's still too much data, add a `LIMIT offset,count` clause to the end of the query. SELECT * FROM table LIMIT 0,30; SELECT * FROM table LIMIT 30,30; SELECT * FROM table LIMIT 60,30; etc Or, if possible, increase the scrollback buffer for your shell client (if you're shelled in, that is, and not on the server directly). As it stands, though, the native mysql client really isn't meant to be used to view such large amounts of data. Are you using any sort of frontend with this database? PHP, Perl, Java, etc? Oh, one more... SELECT * FROM table INTO OUTFILE '/path/to/file'; Then exit the mysql client and use (if on a *nix or Windows system) `more /path/to/file` to page through the results of the table dump. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Paging!
From: Dan Greene [mailto:[EMAIL PROTECTED] another option is to set your pager option: mysql pager more then re-run your query Wow. You learn something new every day. Thanks! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error: 1062 Duplicate Entry '252' for key 1
From: Mike Mapsnac [mailto:[EMAIL PROTECTED] Thanks for explanation. But I don't insert anything at all to the SLAVE. It is 100%. Can the problem be problem be relative to Mysql 4.0.13 ? Thanks Out of curiosity, what's the data type of this column? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP! Select queries for tables that has fields with # characters
From: Howell, Scott [mailto:[EMAIL PROTECTED] I am trying to query a table that has field names with # characters in them. For example a table emp_earn has a field called FILE# I need to do a query where FILE# = 1332, but anything I try errors out. I can't seem to even create a table with a column with a # in the name. However, have you tried enclosing the column name in backticks? SELECT * FROM table WHERE `FILE#`=1332; -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Selecting by date
From: Matthew Stuart [mailto:[EMAIL PROTECTED] I have created a sql statement that enables me to select all entries in to MySQL that are dated today, I am trying to do the same for all items that are now one day old and also two days old etc I am doing this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW()-1 I have it working for todays records, it filters out any that don't match todays date or NOW(), but it still returns the same records for NOW()-1, or NOW()-2. I have tried DATE() but this gives me an error. What is the correct syntax for what I am trying to achieve? You probably want something like this: SELECT * FROM table WHERE category = 3 AND show = 1 AND date = NOW() - INTERVAL 1 DAY; Also note that if you're looking specifically at a date, you might want to use CURDATE() instead of NOW(), as NOW() returns the full -mm-dd hh:ii:ss stamp (whereas CURDATE() returns just -mm-dd). MySQL handles it silently just fine, but it's a good habit to get into. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grants for START/STOP SLAVE
This is in reference to 4.0.16, FWIW... I can't find any direct references to the actual grant permission for controlling a slave thread in the online docs. The closest I saw was the 5th paragraph on http://www.mysql.com/doc/en/Replication_HOWTO.html. It would seem logical that it's covered by either REPLICATION CLIENT or REPLICATION SLAVE, but not so. Anyway, my experimentation leads me to believe that it's controlled by the SUPER privilege. The online docs don't mention these in the description, only this: Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL. Paul DuBois' 2nd ed. of MySQL 4 (the purple book) doesn't mention them directly in the description of SUPER, either (pages 682 and 869). The user in question is for an automated script that checks if the two replication threads are present on the slave server and, if not, pages someone to inform them that replication's been broken. Normally it's a very infrequent matter, but we recently converted some of our major tables from MyISAM to InnoDB and the traffic to these tables sometimes encounters a row lock and the subsequent timeout. While it eventually executes on the master, the timeout kills the slave thread. When that happens, a simple START SLAVE restarts replication and catches up to the master, including the original timed-out query. I'd like to have the check script attempt a START SLAVE when it sees that replication's been broken, as 99.9% of the time it's a timeout and that'll keep us from getting paged and having to do it manually at four in the morning. However, SUPER seems to be over-privileging the user, which I'd like to avoid if possible. Can anyone confirm that it's SUPER that grants permissions for START/STOP SLAVE? Is this actually documented anywhere? Thanks in advance! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: grants for START/STOP SLAVE
From: Paul DuBois [mailto:[EMAIL PROTECTED] At 11:31 -0500 1/15/04, Mike Johnson wrote: This is in reference to 4.0.16, FWIW... I can't find any direct references to the actual grant permission for controlling a slave thread in the online docs. The closest I saw was the 5th paragraph on http://www.mysql.com/doc/en/Replication_HOWTO.html. It would seem logical that it's covered by either REPLICATION CLIENT or REPLICATION SLAVE, but not so. Anyway, my experimentation leads me to believe that it's controlled by the SUPER privilege. The online docs don't mention these in the description, only this: Allows one connect (once) even if max_connections is reached and execute commands CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS and SET GLOBAL. Paul DuBois' 2nd ed. of MySQL 4 (the purple book) doesn't mention them directly in the description of SUPER, either (pages 682 and 869). Hmm...you're right. I just looked in the source, and it's the SUPER privilege that's needed for START SLAVE and STOP SLAVE. I've updated the manual to indicate this: http://www.mysql.com/doc/en/START_SLAVE.html http://www.mysql.com/doc/en/STOP_SLAVE.html Hey, thanks. Now that's service. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question about IF statements...
From: Cory Hicks [mailto:[EMAIL PROTECTED] Hello, I must be having a goober moment.I am running the following sql query with no problems: SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' GROUP BY project_id However, having 'NULL' appear where the total is 0.00 doesn't do me much good :-) How do I write this query to only show me the results where the total is 0.00...so where the results that total 0.00 don't appear at all. I am sure I am missing something...thanks for any help! SELECT project_id, IF (SUM( time_worked ) '0.00', SUM( time_worked ),'NULL') AS total FROM time_daily WHERE user_id = 'clh' AND period_id = '27' AND total IS NOT NULL GROUP BY project_id; That should work, though I haven't tested it. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Moving Bookmark Table Data
From: Michael B Allen [mailto:[EMAIL PROTECTED]
I'd like to move some bookmarks in one database to another. The format
of the Active PHP Bookmarks 'apb_bookmarks' table is (minorly
trucated):
+--+--+--+-+-+
---+
| Field| Type | Null | Key | Default
| Extra |
+--+--+--+-+-+
---+
| bookmark_id | int(11) unsigned | | PRI | NULL
| auto_incr |
| group_id | int(10) unsigned | | MUL | 0
| |
| bookmark_title | varchar(255) | | |
| |
| bookmark_url | varchar(255) | | |
| |
| bookmark_descrip | varchar(255) | YES | | NULL
| |
| bookmark_creatio | datetime | | | -
| |
| bookmark_private | char(1) | | | 0
| |
| bookmark_last_hi | datetime | YES | | NULL
| |
| user_id | int(10) unsigned | | MUL | 0
| |
| bookmark_deleted | char(1) | | | 0
| |
+--+--+--+-+-+
---+
I want to move this into the online-bookmarks 'bookmark' table which
has the format:
+-+---+--+-+-+
+
| Field | Type | Null | Key | Default | Extra
|
+-+---+--+-+-+
+
| user| char(20) | | | |
|
| title | char(70) | | MUL | |
|
| url | char(200) | | | |
|
| description | char(200) | YES | | NULL|
|
| private | enum('0','1') | | | 0 |
|
| date| timestamp(14) | YES | | NULL|
|
| childof | int(11) | | | 0 |
|
| id | int(11) | | PRI | NULL|
auto_increment |
| deleted | enum('0','1') | | | 0 |
|
+-+---+--+-+-+
+
The only field mappings I care about are:
bookmark_title - title
bookmark_url - url
bookmark_description - description
Now I know I can generate insert statements and then run that
script on
the target but is there a better way? The databases do not have direct
access as each is running on localhost only.
While it's a little clunky, why not do this on the first server:
CREATE TABLE apb_bookmarks_tmp
SELECT '' AS user,
bookmark_title AS title,
bookmark_url AS url,
bookmark_description AS description,
0 AS private,
NULL AS date,
0 AS childof,
NULL AS id,
0 AS deleted
FROM apb_bookmarks;
I'm assuming that by these are the only field mappings that I care about, you mean
that those are the only fields you want brought over. If not, then replace the NULLs
and 0s with the proper field names.
Once you've done this, all you need to do is mysqldump the table, scp it over the the
new host, and import it. If you had indices on the original table, you'll have to
manually create them on the new table -- CREATE TABLE ... SELECT FROM doesn't carry
those over.
HTH!
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BETWEEN or = =
From: Eve Atley [mailto:[EMAIL PROTECTED]
I am attempting to construct a select statement in which I
can find values between two fields: start, and end. I have
tried using BETWEEN and comparing with = and =, but
neither meet success. Can someone please set me straight?
This is meant to be in a PHP page, but I'm assuming the
syntax is similar if not the same. I understand there is
also a min/max, but I'm not sure it would work in this
instance.
$sql = SELECT * FROM federal-married WHERE start =
.$_POST['salary']. AND end = .$_POST['salary'];
Table: federal-married (finds federal tax rate based on
marital status)
start = min field, ie. 804
end = max field, ie. 2801
$_POST['salary'] = salary of individual posted from a form
Have you tried this?
$sql =
SELECT *
FROM federal-married
WHERE {$_POST['salary']} BETWEEN start AND end
;
--
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Implement one statement w/o subqueries.
From: Ruslan U. Zakirov [mailto:[EMAIL PROTECTED] Hello. Table 1: Items id, Name Table 2: Properties id, Item, Name, Value I want select Items _and_ all thier props only if Item have specified property. Example: Table Item: 1, Mouse 2, Monitor 3, Keyboard Table Properties: 1, 1, Color, Red 2, 2, Color, Gray 3, 1, Interface, Wireless 4, 2, MaxResolution, [EMAIL PROTECTED] 5, 3, Color, Gray 6, 3, NumberOfKeys, 101 I want select all Items and all thier properties if item's Color is Gray: Item, Name, Prop, Value 2, Monitor, Color, Gray 2, Monitor, MaxResolution, [EMAIL PROTECTED] 3, Keyboard, Color, Gray 3, Keyboard, NumberOfKeys, 101 I hope you've understand what I mean. Thanks. Ruslan. PS: MySQL 4.0.x I believe you just need to join the Properties table twice: SELECT I.id, I.Name, P2.Name, P2.Value FROM Properties P1 INNER JOIN Items I ON I.id=P1.Item INNER JOIN Properties P2 ON P2.Item=I.id WHERE P1.Name='Color' AND P1.Value='Gray'; -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL problem
From: Aron Bereket [mailto:[EMAIL PROTECTED] Hi there, I have recently installed mysql and php on my mechine and both work fine. But when i wrote an HTML/Php to access my databse I got the following error message: Fatal error: Call to undefined function: mysql_pconnect() in /var/www/html/employee.php on line 48 I tried also with mysql_real_connect() and mysql_connect() functions bud didn't solve the problem. Does it mean that the mysql_pconnect() doesn't exitst or is some configuration problem? Your help is appriciated. Are you sure the MySQL API was compiled with your install of PHP? Create a test script and just put the following in it: ? phpinfo() ? Then view that in your web browser. Near the top should be a header Configure Command -- does it include an appropriate --with-mysql=xxx argument? If that checks out, scroll further down to view information about PHP's planned interactions with MySQL. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange ORDER BY question
From: Lewis, Jason [mailto:[EMAIL PROTECTED] Okay I have a field in my db called MemberLevel in this field you can be one of 5 levels. Platinum Gold Silver Paying Non-Paying now my question is, how would I ORDER BY MemberLevel and get it to come out in the above order? I have been racking my brains for a week on this one and any help will be appreciated. I just saw in the online manual comments that this can be done like this: SELECT * FROM tablename ORDER BY MemberLevel='Platinum' DESC, MemberLevel='Gold' DESC, MemberLevel='Silver' DESC, MemberLevel='Paying' DESC, MemberLevel='Non-Paying' DESC; (from http://www.mysql.com/doc/en/Sorting_rows.html) However, before I saw that, I'd have suggested a meta-table for these values, upon which you could sort by rank. Something such as... CREATE TABLE MemberLevels ( rank INT NOT NULL PRIMARY KEY, description VARCHAR(20) ); INSERT INTO MemberLevels (rank, description) VALUES (0, 'Platinum'); INSERT INTO MemberLevels (rank, description) VALUES (1, 'Gold'); INSERT INTO MemberLevels (rank, description) VALUES (2, 'Silver'); INSERT INTO MemberLevels (rank, description) VALUES (3, 'Paying'); INSERT INTO MemberLevels (rank, description) VALUES (4, 'Non-Paying'); Then remap the MemberLevel column in your table(s) to use the MemberLevels.rank values instead, possibly renaming the MemberLevel column to MemberLevelRank for clarity, and then join on that table in order to sort in their ranked order: SELECT tablename.*, MemberLevels.description FROM tablename, MemberLevels WHERE tablename.MemberLevelRank=MemberLevels.rank ORDER BY MemberLevels.rank ASC; As said, I imagine the previous solution would be better, but it never hurts to suggest another way. :) -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange ORDER BY question(SOLUTION)
From: Lewis, Jason [mailto:[EMAIL PROTECTED] Didn't know if anyone else might need this but I was given the solution. SELECT * FROM tablename ORDER BY MemberLevel='Platinum' DESC, MemberLevel='Gold' DESC, MemberLevel='Silver' DESC, MemberLevel='Paying' DESC, MemberLevel='Non-Paying' DESC; Thanks again Mike! Thanks for posting it, actually, as I wasn't aware of that solution until your question prompted me to go looking. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get counts of col=value with an GROUP BY clause?
From: Greg Owen [mailto:[EMAIL PROTECTED] I'm trying to build a query to show counts of specific columns by value. There's probably a simple way to do this that I don't know about, and if you could just point me to the function or part of the manual to research before you start laughing, I'd greatly appreciate it. Actually, this was a welcome distraction. :) What I want to do is query and get a result that I can display like this (probably irrelevant, but the application and presentation layer is handled in Perl with DBI and DBIx::XHTML_Table): +---+---+--+--+--+--+--+ | Class | Total | Easy | Med. | Hard | Quiz | Exam | +---+---+--+--+--+--+--+ | algebra | 2 |2 | | |1 |1 | | calculus | 3 | |1 |2 |2 |1 | | discrete math | 2 |1 | |1 |2 | | | geometry | 1 | |1 | |1 | | | hypermath | 1 | | |1 |1 | | | trigonometry | 2 |1 |1 | | |2 | +---+---+--+--+--+--+--+ Here's the shorter version, which puts in 0s instead of the blanks in your table: SELECT class AS Class, COUNT(Class) AS Total, SUM(IF(difficulty=0,1,0)) AS Easy, SUM(IF(difficulty=1,1,0)) AS `Med.`, SUM(IF(difficulty=2,1,0)) AS Hard, SUM(IF(in_use=0,1,0)) AS Quiz, SUM(IF(in_use=1,1,0)) AS Exam FROM Questions GROUP BY Class ORDER BY Class; And the following will do the same, but with blanks instead of 0s (as you had above): SELECT class AS Class, COUNT(Class) AS Total, IF(SUM(IF(difficulty=0,1,0))=0,'',SUM(IF(difficulty=0,1,0))) AS Easy, IF(SUM(IF(difficulty=1,1,0))=0,'',SUM(IF(difficulty=1,1,0))) AS `Med.`, IF(SUM(IF(difficulty=2,1,0))=0,'',SUM(IF(difficulty=2,1,0))) AS Hard, IF(SUM(IF(in_use=0,1,0))=0,'',SUM(IF(in_use=0,1,0))) AS Quiz, IF(SUM(IF(in_use=1,1,0))=0,'',SUM(IF(in_use=1,1,0))) AS Exam FROM Questions GROUP BY Class ORDER BY Class; The key parts being used are the SUM() function and IF() statements. SUM() simply totals what you're giving it. The structure of IF() is IF(condition, output if condition is true, output if condition is false). Using an IF() statement, you can control exactly what gets sent to SUM(). The lines for each of the right 5 columns are just that. For the 'Easy' column, if difficulty=0 send 1 to SUM(), else send 0. And so on. My apologies if my SQL is a bit compacted or tough to read. It makes sense to me because I wrote it piece-by-piece, but I imagine it doesn't look so simple to someone else. :) I'm not sure of any standards for nested functions and control structures such as that. Anyone have any input on that? -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Variable names, colum names
From: Paul Fine [mailto:[EMAIL PROTECTED]
Greetings!
Is it common practice to name script variables/form data in
say PHP to match the appropriate colums in the db tables?
I can see how this makes sense, however in the little work I
have done I preferred to do the opposite as it seems to be
helpful to keep a distinction.
I've found it useful to keep the same names, such that you could do the following. Say
you were taking form input and inserting it as a row into the table...
?
$query = INSERT INTO table SET ;
foreach ($_POST as $key = $val) {
$query .= $key='$val', ;
}
$query = preg_replace(/, $/, , $query);
mysql_query($query);
?
That's oversimplifying it, for sure, as you'd want error checking and data validation
and the like. And you also have to remember that any fields in the HTML form, hidden
or otherwise, had better match up to the db table structure.
On the plus side, if you add a column to the db, all you need to do is add a field of
the same name to the HTML form.
In a nutshell, while it's useful to do things like abstract the construction of the
query, it's often not worth the trouble.
--
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic error
From: Trevor Rhodes [mailto:[EMAIL PROTECTED]
Hello friendly helper types,
What is the problem here. I can't find any errors. I know
it's there but as a
newbie I'm as blind as a bat at the moment. Thanks for your help.
Warning: mysql_num_rows(): supplied argument is not a valid
MySQL result
resource
Warning: mysql_fetch_row(): supplied argument is not a valid
MySQL result
resource
===
$link = mysql_connect( localhost, $user, $pass );
if ( ! $link )
die( Couldn't connect to MySQL );
mysql_select_db( $db, $link )
or die ( Couldn't open $db: .mysql_error() );
$result = mysql_query( select * from domains );
$num_rows = mysql_num_rows( $result );
print There are currently $num_rows rows in the table;
print table border=1\n;
while ( $a_row = mysql_fetch_row( $result ) )
{
print tr\n;
foreach ( $a_row as $field )
print \ttd$field/td\n;
print /tr\n;
}
print /table\n;
mysql_close( $link );
===
Have you tried including $link as a second argument in mysql_query()?
mysql_query( select * from domains, $link );
IIRC, it's not necessary, but it might be worth a shot.
--
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing something obvious w/grant statement length?
From: Ari Davidow [mailto:[EMAIL PROTECTED] I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? The syntax is a bit weird for GRANT statements; either side of [EMAIL PROTECTED] are two separate args to be stored in two separate columns. This should work... GRANT ALL ON *.* TO 'me'@'mysite-dev.foo.com' IDENTIFIED BY 'foo'; -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: missing something obvious w/grant statement length?
From: Paul DuBois [mailto:[EMAIL PROTECTED] At 13:29 -0500 12/16/03, Mike Johnson wrote: From: Ari Davidow [mailto:[EMAIL PROTECTED] I seem to have run into a problem with a host name that incorporates a hyphen: mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1064: You have an error in your SQL syntax near '-dev.foo.com IDENTIFIED BY foo' at line 1 mysql GRANT ALL ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY foo; ERROR 1145: The host or user argument to GRANT is too long I must be missing something very obvious--how to incorporate a hostname with a hyphen, for instance. Can someone help? The syntax is a bit weird for GRANT statements; either side of [EMAIL PROTECTED] are two separate args to be stored in two separate columns. Why is it weird? What if you wanted to include a '@' character in your username? '[EMAIL PROTECTED]'-style quoting wouldn't allow that. :-) Heh, OK, weird has bad connotations. What I meant was that the syntax for a GRANT statement replaces a series of INSERT/UPDATE statements on the mysql db, and as such, while succinct, it's not always entirely intuitive. Another example of that is the IDENTIFIED BY 'password' clause. It took me a few times to remember that it automatically called PASSWORD() on 'password' -- then again, I came into GRANT statements from first doing the INSERT/UPDATE calls manually. :) -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sending array data using php mail
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Hi, can anyone tell me what is the problem of my script:
$sql2 = SELECT * FROM Cash WHERE HP='$HP' and
SignUpDate='$SignUpDate';
$rows1 = mysql_query($sql2,$linkptr1) ;
$OwnnerMail =[EMAIL PROTECTED];
$Subject = Testing;
while ( $row = mysql_fetch_array($rows1) ) {
$newvalue = $row[Password];
echo $newvalue\n;
}
$Body = $newvalue;
$From = $HP;
mail( $OwnnerMail,$Subject, $Body, From: $From);
I can see all the data for $newvalue in browser. But, when i
receive email, i can only see the last data.
can i know what is the problem? thank you.
Your script is doing exactly what you told it to. After the while() loop, $newvalue is
set to the last member of $row[Password].
Try something like this...
$sql2 = SELECT * FROM Cash WHERE HP='$HP' and SignUpDate='$SignUpDate';
$rows1 = mysql_query($sql2,$linkptr1);
$OwnnerMail =[EMAIL PROTECTED];
$Subject = Testing;
$Body = ;
while ( $row = mysql_fetch_array($rows1) ) {
$newvalue = $row[Password];
echo $newvalue\n;
$Body .= $newvalue\n;
}
$From = $HP;
mail( $OwnnerMail,$Subject, $Body, From: $From);
See how $Body is initialized before the while() loop, and then appended to using .=
inside the loop? Once the loop completes, $Body contains all values of
$row[Password], not just the last one.
--
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sending array data using php mail
From: Abs [mailto:[EMAIL PROTECTED]
hi
the error is in this line:
$newvalue = $row[Password];
make it
$newvalue.= $row[Password];
(notice ^ the dot - for concatenation with the
previous value).
and if u want to see each password in the browser too,
in that same loop, put:
echo {$row[Password]\n;
so it would now look like:
while ( $row = mysql_fetch_array($rows1) ) {
$newvalue.=$row[Password];
echo {$row[Password]\n;
}
u may also want to use a br in ur echo line to make
the browser output nicer, the \n affects only the html
code. and while u're at it, put a \n in $newline so
that ur email looks nicer.
For the record, there's a syntax error in there -- the closing curly brace is missing.
echo {$row[Password]}\n;
^
Also, I've never tried this syntax with double-quotes. Do the curly braces keep the
PHP parser from thinking that the opening for Password is a close of the string? I
use single quotes in that kind of situation, FWIW.
--
Mike Johnson
Web Developer/Systems Asst.
Smarter Living, Inc.
phone (617) 497-2500 x226
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! Well, not really, I'm afraid - the plain $id takes the visitor to the next page with the correct POSTed value in the URL. Alas, alas, adding single or double quotes doesn't solve things. I've shuffled/altered names around in a (fruitless) attempt to correct things but still, clicking on the hyperlink for Spain and echoing the $Query and the $Result to screen I get - Query= SELECT properties.caption AS caption, properties.country, properties.area AS area, images.image_filename AS filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.country='Spain' Result= i.e. No Result. Boo-hoo. Anything stand out there? Let's try some debugging on your data, as the query looks fine to me. What about running the following queries right in the MySQL console? SELECT * FROM properties WHERE country='Spain'; That should give you all you need but images.image_filename. If that returns what it should, then take reference_number from the results and lookup in images using it: SELECT image_filename FROM images WHERE reference_number=[reference_number]; (where [reference_number] is from the previous result) If the first query returns nothing, then your problem is that there's no record for properties.country='Spain'. If the second returns nothing, then there's no record for images.reference_number matching Spain's reference_number in properties. Either of those things being off would result in the actual join query returning nothing. Let me know how those turn out... -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE
From: Daniel Kiss [mailto:[EMAIL PROTECTED] Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 I believe it's... LOAD DATA LOCAL INFILE '/path/to/file' INTO TABLE tablename FIELDS TERMINATED BY ',' OPTONALLY ENCLOSED BY ''; -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] . Dear List, My thanks to those who replied. I'm embarrassed as anything - the bloke I'm doing this thing for has now changed his tiny mind and has decided to have a set number of images per property, whereas before he was contemplating accepting a widely variable number. I can thus stick all the fields in the one table and, with that, my need for Joins goes west. While this now needs loads'v table reconstruction, at least I'm well able in that area. Thanx again for your assistance and offers of assistance. Damn, I was really hoping to get to the bottom of the problem. Ah well. FYI, might I suggest going with your original design anyway? If this guy changes his mind like this often, then he may very well decide, a month after launch, that he wants to go back to a variable # of images per property. In that case you'd be right back at the drawing board. If you build it that flexible from day one, though, he can change his mind to his heart's content. Anyway, good luck! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to joins - this simple one doesn't work.
From: fatblokeonbike [mailto:[EMAIL PROTECTED] $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area=$id; but it doesn't work - I get the usual ...not a valid MySQL result resource I've played around with it, but I confess myself beat. I expect the answer's terribly simple - but then, as everyone keeps telling me, so am I. If you can help, thanks in advance. If $id is a string and not an int, then you need to single- or double-quote it. Try this: $Query=SELECT properties.area, images.image_filename FROM properties, images WHERE properties.reference_number=images.reference_number AND properties.area='$id'; HTH! -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mean/median/mode
From: Robert Citek [mailto:[EMAIL PROTECTED] Hello all, How can I calculate the mean/median/mode from a set of data using SQL? Mean seems to exist as the average (avg): select name, avg(value) from table group by name Is there a way to calculate median and mode with a group by clause? Median: the value at which 50% of the samples are above and below that value. Mode: the most common value For mode, this should work: SELECT COUNT(value) AS mode FROM table GROUP BY value ORDER BY mode DESC LIMIT 1; As for median, it's sort of a hack, but this may do the trick: SELECT FLOOR(COUNT(value)/2) FROM table; SELECT name, value FROM table ORDER BY value ASC LIMIT previous result, 1; Caveat: That'll work if you have an odd # of rows in the table (I have 15 in my test table); I don't know, mathematically, what median should return for a set of data of an even number, actually. The middle two? Or should it pick one? Good luck either way. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stopping or aborting a long query
From: Robert Citek [mailto:[EMAIL PROTECTED] How does one stop or abort a query? I was doing some experimenting with MySQL and created a table with 100,000 records. I then did a join like so: create table foo select foo.name, foo.val from foo, foo x, foo y, foo z ; This took a long time. So, I aborted with Ctrl-C (^C) which bumped me out of the mysql client. I then reconnected, issued 'show processlist;', and issued a 'kill ###;' for the process. Is this the way to abort a query? Or is there a more graceful method that aborts the query but keeps me in the mysql client? Have googled and searched the mailing list archives and faq, but nothing so far. Hints for search terms and pointers to a URL are greatly appreciated. When I need to kill a query, I skip the Ctrl-C step and just open another session on the client and kill it from there. The previous window will say something about the MySQL server going away, but can generally reconnect on its own if you give it a line feed. However, I've found that when I kill a query, more often than not it just hangs in the processlist. In fact, we've had queries that hang and prevent other queries from getting to the table for so long that we end up restarting the server to free it up. Ugh. I haven't researched it much, but has anyone else seen this hanging query problem before? It was probably 3.23 at that point (we recently upgraded to 4, but haven't done much to warrant the situation since). The status for the process usually says 'killed' if that helps. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem request can be resolved in the next versions
From: lamjoun [mailto:[EMAIL PROTECTED] Hi, I have a problem with this request. insert into table month_var (year,month,var_cd,val) select '2003','10',var_cd,0 from var and var_cd not in (select var_cd from month_var where year='2003' and month='10'); thanks This isn't a problem request to be resolved in the next versions. You're simply using incorrect MySQL syntax. Since you've now posted this three times with no further explanation of what you're trying to get at, one can only assume you're trying to perform an INSERT INTO...SELECT FROM that'd be helped by using a join of some sort. If you're trying to limit it based on values found or not found in another table, as it appears, try something like... INSERT INTO month_var (year, month, var_cd, val) SELECT '2003', '10', var.var_cd, '0' FROM var LEFT JOIN month_var ON month_var.var_cd=var.var_cd WHERE month_var.year='2003' AND month_var.month='10' AND month_var.var_cd IS NULL I don't have your table structure and, actually, am not entirely sure of what you're aiming for, so I don't know if that above query works. If not, try looking at the manual. INSERT...SELECT syntax http://www.mysql.com/doc/en/INSERT_SELECT.html JOIN syntax http://www.mysql.com/doc/en/JOIN.html -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limitations on data for default...
From: Mikael Fridh [mailto:[EMAIL PROTECTED] or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html It's only too much precision when you're selecting the whole field. You can simply select a timestamp as... SELECT DATE_FORMAT(column, '%Y-%m-%d') AS date ...and get a date just fine. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stupid newbie question
From: Scott Yamahata [mailto:[EMAIL PROTECTED] At the command prompt, I'm supposed to type: mysql -h host -u user -p Does that mean that I type, for example: mysql -h localhost -u admin -p with localhost=host and admin=user? When I'm asked for the password, I hit return and it lets me in. But if I try to create a database, by using: GRANT ALL ON store.* TO [EMAIL PROTECTED]; I get an ERROR 1044: Access denied for user: '@localhost' to database 'store' Any help is appreciated. Unless you've previously granted GRANT permissions to [EMAIL PROTECTED] (as the MySQL root user), then this will, and should, fail. In addition, though I'm not entirely certain, I think you need to delimit [EMAIL PROTECTED] as two different strings -- GRANT ALL ON store.* TO 'admin'@'localhost' If you need help giving GRANT permissions to [EMAIL PROTECTED], let us know or check out: http://www.mysql.com/doc/en/GRANT.html -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
large LOAD DATA INFILE and replication
I performed a LOAD DATA INFILE on two very large (2G and 997M) files to a db server that acts as a master to several slaves. The LOAD DATA was to a non-replicated db, and should not have been replicated, but it appears that, as it was not done as a LOAD DATA LOCAL INFILE, the data files were replicated (albeit uselessly) to the slave servers. Right off the bat, this resulted in a packet size issue which we resolved by adjusting max_allowed_packet, but the disk capacity of the file systems on the slave hosts filled to capacity. At this point, we were unable to set SQL_SLAVE_SKIP_COUNTER anywhere past the point of the data load and ended up stuck with broken replication on the slave machines. Here's a tail of an error log of one of the slaves: 03 7:04:10 Error in Log_event::read_log_event(): 'read error', data_len: 825362754, event_type: 49 03 7:04:10 Error reading relay log event: slave SQL thread aborted because of I/O error 03 7:04:10 Slave: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0 03 7:04:10 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'central-bin.041' position 649082785 As stated above, we cannot set SQL_SLAVE_SKIP_COUNTER to go beyond position 649082785. The master is Redhat Linux 9 running MySQL 4.0.15-standard-log, and the slaves are Redhat Linux 7.1 running MySQL 4.0.15-standard-log as well. Has anyone run into this before? -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dirt Slow Query On Datetime Range
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Change your query to use BETWEEN rather that = and =. --ja snip And here is the query: SELECT COUNT(*) AS CallCount FROM ServiceRADIUSAccounting WHERE (Realm = 'testreal.com') AND (AcctStartTime = '2003-11-11 15:30:00' AND AcctStopTime = '2003-11-11 15:30:00') His WHERE clause is on two different fields (AcctStartTime and AcctStopTime). I don't think a BETWEEN clause is what's needed... -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: exporting data
Do you mean like 'mysqldump'? You can use mysqldump to dump tables +/- data and then upload it to a database later--or 'import' it, e.g., mysqldump -u user_name database all the tables from that database. mysqldump takes a variety of arguments. see 'man mysqldump' for more info. -mj Desmond Lee wrote: Hi guys I know that you can import data into a table by using 'load data infile' or 'mysql' import. however, is there something that allows you to export the create statemtns for the table schema and the data of a database for backup. SO that if we reinstall the whole thing from scratch all we need are the exported files that will recreate the whole database from the time we exported? Thanks Desmond _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
