Website site Database (Project)
I wanted to know if this is a good place to post for a project I needed done, If not can someone direct me to a better place to post it. Thanks ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Website site Database (Project)
What do you mean? On Sun, 10 Oct 2004 23:07:59 -0700 (PDT), John [EMAIL PROTECTED] wrote: I wanted to know if this is a good place to post for a project I needed done, If not can someone direct me to a better place to post it. Thanks ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
Good idea. liang le's answer almost got it, but I couldn't make it work with string values quite right. Here is my situation: I am issuing a series of queries all-in-one like SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 3; expecting a return of aName otherItem aName otherItem aName otherItem but if one of those rowIDs does not exist, then I get aName otherItem aName otherItem and my app has no value for the non-existing row. I would like for the query to return an indication that the row did not exist, like: aName otherItem 0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL) aName otherItem Does that make more sense, and if so, is there a solution? Thanks, John Liang Le -- Your query: (SELECT IFNULL(a.nameColumn,'0') nameColumn, IFNULL(a.otherColumn,'0') otherColumn FROM theTable a WHERE a.rowID = 5) UNION (SELECT IFNULL(b.nameColumn,'0') nameColumn, IFNULL(b.otherColumn,'0') otherColumn FROM theTable b WHERE b.rowID = 5) ; Worked when the row DID NOT exist (like I asked for). However, when the row DID exist, it was returning: aName otherItem 0 0 The zeros are troublesome. It should look like: aName otherItem Thanks! on 10/10/04 10:46 PM, Michael Stassen at [EMAIL PROTECTED] wrote: Then how will you know the difference between a row with nameColumn = 0 (or '') and one that doesn't exist? What you are asking for seems very strange. You want the db to pretend there's a value for nonexistent rows. If rowID 5 should have the value 0, then I wonder why there isn't a row with rowID=5 and value=0. If it's just a matter of treating non-existent rows as having 0 value in your app, why don't you simply code that into your app? In other words, I find it hard to provide a solution, because I don't understand what you want. Perhaps if you explained it, someone could suggest how best to accomplish it. Michael John Mistler wrote: Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Index
Hi, just wanted to know if I can set other indexes on one of my replication servers that are not on the master server? This would be a great performance benefit if I use this server for administrative purpose.. Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange join results
In article [EMAIL PROTECTED], Ville Mattila [EMAIL PROTECTED] writes: When I try to find out the current amount of products in our stock, and ordered quantities I use this query: SELECT p.id, SUM(out.quantity) ordered_out, SUM(in.quantity) ordered_in FROM products p LEFT JOIN outorders out ON (out.productid = p.id) LEFT JOIN inorders (in.productid = p.id); Even I have only one OutOrder for certain product, I get ordered_out value as 2. ordered_in value is anyway correct. If I remove all things related to InOrders I will get correct result to ordered_out. Have I missed something? Isn't LEFT JOIN the correct query to join tables to the main table (in this case products)? Drop the sum() aggregate and you'll see what went wrong: you get the same out row for every matching in row and vice versa; thus your sums are in general multiples of the correct values. To remedy that, you need a division: SELECT p.id, SUM(out.quantity) / COUNT(DISTINCT in.id) ordered_out, SUM(in.quantity) / COUNT(DISTINCT out.id) ordered_in FROM products p LEFT JOIN outorders out ON out.productid = p.id LEFT JOIN inorders ON in.productid = p.id GROUP BY p.id; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
In article [EMAIL PROTECTED], John Mistler [EMAIL PROTECTED] writes: Good idea. liang le's answer almost got it, but I couldn't make it work with string values quite right. Here is my situation: I am issuing a series of queries all-in-one like SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 3; expecting a return of aName otherItem aName otherItem aName otherItem but if one of those rowIDs does not exist, then I get aName otherItem aName otherItem and my app has no value for the non-existing row. I would like for the query to return an indication that the row did not exist, like: aName otherItem 0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL) aName otherItem Does that make more sense, and if so, is there a solution? This sounds like a broken DB design. You should have another table listing all possible rowIDs; something like that: CREATE TABLE myRows ( rowID INT NOT NULL, nameColumn VARCHAR(255) NOT NULL, PRIMARY KEY (rowID) ); INSERT INTO myRows (1, 'row #1'); INSERT INTO myRows (2, 'row #2'); INSERT INTO myRows (3, 'row #3'); Then you can do the following: SELECT t1.nameColumn, coalesce(t2.otherColumn, '') FROM myRows t1 LEFT JOIN yourTable t2 ON t2.rowID = t1.rowID -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
This is a pretty weird thing. If you have no rows returned, and want to taka an action on that, then the application should check for the case of no rows returned, not for a specific value. But if you insist and use MySQL 4.1 (as subqueries are assumed): SELECT IFNULL((SELECT nameColumn FROM theDatabase WHERE rowId = 5), 0); Which is not to say that I think this is a good idea :-) /Karlsson John Mistler wrote: Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table _ Do You Yahoo!? 150??MP3 http://music.yisou.com/ ??? http://image.yisou.com 1G??1000??? http://cn.rd.yahoo.com/mail_cn/tag/1g/*http://cn.mail.yahoo.com/event/mail_1g/ -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
SELECT nameColumn, otherColumn, count(*) as flag FROM theDatabase WHERE rowID = 1 group by rowID; or make a temp table with the IDs and then left join it with theDatabase and drop temp. Santino At 23:27 -0700 10-10-2004, John Mistler wrote: Good idea. liang le's answer almost got it, but I couldn't make it work with string values quite right. Here is my situation: I am issuing a series of queries all-in-one like SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM theDatabase WHERE rowID = 3; expecting a return of aName otherItem aName otherItem aName otherItem but if one of those rowIDs does not exist, then I get aName otherItem aName otherItem and my app has no value for the non-existing row. I would like for the query to return an indication that the row did not exist, like: aName otherItem 0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL) aName otherItem Does that make more sense, and if so, is there a solution? Thanks, John Liang Le -- Your query: (SELECT IFNULL(a.nameColumn,'0') nameColumn, IFNULL(a.otherColumn,'0') otherColumn FROM theTable a WHERE a.rowID = 5) UNION (SELECT IFNULL(b.nameColumn,'0') nameColumn, IFNULL(b.otherColumn,'0') otherColumn FROM theTable b WHERE b.rowID = 5) ; Worked when the row DID NOT exist (like I asked for). However, when the row DID exist, it was returning: aName otherItem 0 0 The zeros are troublesome. It should look like: aName otherItem Thanks! on 10/10/04 10:46 PM, Michael Stassen at [EMAIL PROTECTED] wrote: Then how will you know the difference between a row with nameColumn = 0 (or '') and one that doesn't exist? What you are asking for seems very strange. You want the db to pretend there's a value for nonexistent rows. If rowID 5 should have the value 0, then I wonder why there isn't a row with rowID=5 and value=0. If it's just a matter of treating non-existent rows as having 0 value in your app, why don't you simply code that into your app? In other words, I find it hard to provide a solution, because I don't understand what you want. Perhaps if you explained it, someone could suggest how best to accomplish it. Michael John Mistler wrote: Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A database design question
Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
I think you need to explain what kind of SELECTs you want to do, and what results you expect. How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock tables
Hi, I'm a bit confused by the lock mechanism under mysql. When user A does an update on table 1, the table is automatically locked by mysql?that means at the same time user B won't be able to modify the same row? Or do I have to specify the lock for each query? And what about temporary tables? If anybody has a simple explanation or a link on a doc thanks, Melanie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Website site Database (Project)
I've been subscribed to this mailing list for at least a year now and I don't remember ever seeing a specific project description on it where someone was looking for a consultant. However, I don't remember ever hearing anyone expressing any objection to that either ;-) I'm not aware of any better place to post a project description either. Therefore, I suggest you describe what you want. If anyone flames you for doing so, tell them it was my fault for encouraging you. ;-) Rhino - Original Message - From: John [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 11, 2004 2:07 AM Subject: Website site Database (Project) I wanted to know if this is a good place to post for a project I needed done, If not can someone direct me to a better place to post it. Thanks ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Website site Database (Project)
Super-hot flame is on its way already ;-D By the way, Good morning (in the United States) and Good 'otherwise' to otherwise ;^) -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 8:18 AM To: John; mysql Subject: Re: Website site Database (Project) I've been subscribed to this mailing list for at least a year now and I don't remember ever seeing a specific project description on it where someone was looking for a consultant. However, I don't remember ever hearing anyone expressing any objection to that either ;-) I'm not aware of any better place to post a project description either. Therefore, I suggest you describe what you want. If anyone flames you for doing so, tell them it was my fault for encouraging you. ;-) Rhino - Original Message - From: John [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 11, 2004 2:07 AM Subject: Website site Database (Project) I wanted to know if this is a good place to post for a project I needed done, If not can someone direct me to a better place to post it. Thanks ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on RedHat ES 3.0
G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg.
RE: MySQL on RedHat ES 3.0
You can use rpm --help to find out how to list all rpm packages that are installed, then when you find out the flag to list them, then do -- rpm -(the flag you found) | grep mysql OR if you want to find out if mysql process is running or not then do -- ps -efaux | grep mysql -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 9:13 AM To: [EMAIL PROTECTED] Subject: MySQL on RedHat ES 3.0 G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
Il giorno 11/ott/04, alle 11:53, [EMAIL PROTECTED] ha scritto: I think you need to explain what kind of SELECTs you want to do, and what results you expect. you're right, I'll try to explain it better I'm working on a system that must keep track of all the music broadcasted by a tv, so, let's call record A TVprogram, TVprogram table will contain all the programs broadcasted in cronological order, they have a broadcasting date, start time and end time, type of program ( news, entertainment, cartoons, and so on... ) every TVprogram record can have one or more associated records of two types: a record B ( let's call it MusicTrack ), containg info ( title, composer, etc. ) about a Music track used on some way in the TV program a record C ( let's call it Movie ), containing info ( title, director, etc. ) about a movie or serial or cartoon and so on broadcasted during the TV program. ( the list of music tracks used on the movie will be extracted from another database at later time). MusicTrack and Movie are associated to a given TVprogram on a progressive ( and cronological ) order. So I.E., for a TVprogram record I could have: 1 a MusicTrack record with info about a song used as intro for the program 2 a MusicTrack record with info about a song used as background music while talking about the movie that will be broadcasted 3 a Movie record containing info about the movie itself 4 a MusicTrack record with info about a song used at the end of the program the select I would like to perform is, given a TVprogram element, have a list of all its MusicTrack or Movie records in crological order, or have a list of TVprogram elements on a given interval, and for everyone of them a list of their referred records. but you're right, I now think the possible solutions are to merge the two table type on one table type, or keep them separated, perform two different separate joins and then merge them by code... thank you, Giulio How do you expect to get results from a SELECT which returns hits in both the B and C tables? If you expect to do this, then the D table is probably your correct answer. Do you really need a rec_type field? Can you not leave the columns which exist only in B type records null in c-type records and vice versa? How much commonality is there between B and C type fields? I presume there is some, or you would not be wanting to merge them. Incidentally, I think you only need a simple join, not a left join - unless I misunderstand. Alec Giulio [EMAIL PROTECTED] wrote on 11/10/2004 10:44:43: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Cantoberon Multimedia srl http://www.cantoberon.it Tel. 06 39737052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: MySQL server doesn't start.
Hi, It is necessary to post in English (enough with Janglish) so that a lot of people may understand because it is an international mailing list. If you hope for the discussion in Japanese, you can apply for participation in Japanese user group's mailing list (http://www.mysql.gr.jp/ml.html) /usr/local/libexec/mysqld: unrecognized option `--key_buffer=16M' /usr/local/libexec/mysqld Ver 3.23.57 for pc-linux on i686 Perhaps, the cause is the same as the content of the following thread: http://lists.mysql.com/mysql/172283 -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 and concat
Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Table Error 1071
There might be limitation on length of index in mysql try this CREATE TABLE `adminpages` ( `adminpageid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT , `file_name` VARCHAR( 250 ) NOT NULL , `page_title` VARCHAR( 250 ) NOT NULL , PRIMARY KEY ( `adminpageid` ) , INDEX ( `file_name` , `page_title` ) ) COMMENT = 'Listing of all pages the administration module' just change the data length of columns from 255 to 250 for both the columns. I tried it on my local server. is is ok Anil DBA -Original Message- From: GH [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 12:16 AM To: [EMAIL PROTECTED] Subject: Create Table Error 1071 How can I fix the following error? I got this via phpMyAdmin Error SQL-query : CREATE TABLE `adminpages` ( `adminpageid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT , `file_name` VARCHAR( 255 ) NOT NULL , `page_title` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `adminpageid` ) , INDEX ( `file_name` , `page_title` ) ) COMMENT = 'Listing of all pages the administration module' MySQL said: #1071 - Specified key was too long. Max key length is 500 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and validation rule
Jonathan Jesse wrote: As mentioned previously I am a MySQL newbie. I have read most of the Paul DuBois book and portions I have found relevant of the manual to help me out, however I have not found an answer to this question, maybe it is not even needed. I have used MS Access a lot and one of the ways I use it is to track hostname to username to ip address at work. In the forms we have a validation rule on the field ip address which allows only numbers in XXX.XXX.XXX.XXX format. Is there such a way to do this in MySQL or would that be on the application that I would use to open/insert/etc ? Thanks in advance, Jonathan MySQL can't do that check on the fly. take a look here : http://dev.mysql.com/doc/mysql/en/String_functions.html and use inet_hton as proposed in the comments. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RedHat ES 3.0
Thanks. rpm -a | grep mysql rpm -l | grep mysql rpm -f | grep mysql rpm -g | grep mysql rpm -p | grep mysql All returned nothing, so I am concluding that MySQL is not installed. Can you please help me out with steps on how to download and install MySQL. Is this by rpm's or gz? Thanks much -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 9:16 AM To: [EMAIL PROTECTED] Subject: RE: MySQL on RedHat ES 3.0 You can use rpm --help to find out how to list all rpm packages that are installed, then when you find out the flag to list them, then do -- rpm -(the flag you found) | grep mysql OR if you want to find out if mysql process is running or not then do -- ps -efaux | grep mysql -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 9:13 AM To: [EMAIL PROTECTED] Subject: MySQL on RedHat ES 3.0 G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Read-Only DB User
First connect to mysql as root user and issue the following command grant select on databasename.* to username@ipaddress identified by 'passwd'; flush privileges; it will grant only select privilege to the newly created user on database and he can only connect from the ipaddress specified in command Anil DBA -Original Message- From: Lee Zelyck [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 7:30 AM To: [EMAIL PROTECTED] Subject: Read-Only DB User Hi All, I'm sorry to access such a basic question, but I couldn't find a specific answer to it in the mysql manual pages. The question is, how would someone create a basic read-only user for a single db? I just intend for it to be used by a script to validate data in the db itself. Anyway, if anyone can provide a lean and concise statement that will provide this, it would be very much appreciated. Thanks! Lee __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and validation rule
You are correct. That is an application-side rule that you will need to enforce using your application code. MySQL could possibly do this check but the overhead involved would be severe and your application's performance would suffer. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jonathan Jesse [EMAIL PROTECTED] wrote on 10/10/2004 04:35:30 PM: As mentioned previously I am a MySQL newbie. I have read most of the Paul DuBois book and portions I have found relevant of the manual to help me out, however I have not found an answer to this question, maybe it is not even needed. I have used MS Access a lot and one of the ways I use it is to track hostname to username to ip address at work. In the forms we have a validation rule on the field ip address which allows only numbers in XXX.XXX.XXX.XXX format. Is there such a way to do this in MySQL or would that be on the application that I would use to open/insert/etc ? Thanks in advance, Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RedHat ES 3.0
On Mon, 2004-10-11 at 14:52, Ferguson, Michael wrote: Thanks. rpm -a | grep mysql rpm -l | grep mysql rpm -f | grep mysql rpm -g | grep mysql rpm -p | grep mysql All returned nothing, so I am concluding that MySQL is not installed. There's a q missing, and better do a case-insensitive grep: rpm -qa | grep -i mysql Can you please help me out with steps on how to download and install MySQL. Is this by rpm's or gz? You can probably install it with redhat's update tools, which is likely to be the easiest thing (I'm not using RedHat, so I can't tell you exactly how this would work). If you'd rather use the rpms provided by MySQL AB, take a look at the docs here: http://dev.mysql.com/doc/mysql/en/Installing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Determine the Server's Version on Old Server?
You should keep threads on the list. That way, more people can help, and more can benefit from the answers. I've not looked at the code behind mysql_get_server_info(), but every version of mysql I've seen has 3 parts to the version number. It seems clear that the mysql version numbering scheme is release.version, where release is 3.23, 4.0, 4.1, or 5.0, and version is sequential. Assuming you'll always get a 3 part version seems safe to me. Michael Matthew Boehm wrote: Will mysql_get_server_info() give you what you need? http://dev.mysql.com/doc/mysql/en/mysql_get_server_info.html I guess I could use that and parse out on the . separator. Will I always get a 3 . separated string? ie: X.XX.XX ? Or could I sometimes get X.XX? Thanks, Matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 and concat
Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. SELECT * FROM sampletable WHERE datefield = '1999-01-12' and datefield '1999-02-01' This example query will get all of the records from sampletable that were entered after January 11th and before February 1st. It will also be **very** fast if the column datefield is indexed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM: Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to access field name in C
What is the best way to access a specific field in C? Its really easy in
PHP...
PHP
---
$res = mysql_real_query($mysql,SELECT col1, col2 FROM table);
while($row = mysql_fetch_row($res)) {
print $row['col1'];
print $row['col2'];
}
Is the only way/best way to do the above in C by using a nested for-loop?
Ex:
fields = mysql_fetch_fields(res);
while((row=mysql_fetch_row(res)) {
for(x=0;xnumFields;x++) {
sprintf(output, Column name: %s Column Value: %s\n,
fields[x].name, row[x]);
}
}
Seems painful and extra-loopy to do it in C. Is there a better way?
Thanks,
Matthew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A database design question
My answers interspersed below (and yes, I have read his follow up reply that had additional information) Giulio [EMAIL PROTECTED] wrote on 10/11/2004 05:44:43 AM: Hi all, I have some doubts about how to implement this kind of scenario: I have a table of elements in cronological order, let's call it table A: for every A element, I have a number of elements ordered on a progressive number. This could be a simply one-to-many relation, where I can handle a list of all A records with related B records using a left join. the issue is complicated ( for me, at least ) by the fact that the records related to table A can be of two different types, that have in common some fields but not others. I mean for every record A I have an ordered list of mixed records B and C. So I'm thinking about pro and cons of three different ways to handle this problem. 1) create tables A,B, and C, with tables B and C having a field id_A containing the ID of records A they belong, and figure out how to handle a left join having oh its right side elements from two different tables 2) create tables A,B, and C, and create an intermediate table D to link table A elements with their related B and C elements, and again figure out how to handle the list of A elements with linked B and C elements. I would think that this structure (#2) would fit your model the best. Each Table A element could contain various elements of tables B and C depending on the schedule/format of that show. Table D would be your logging table that would relate elements of Table A to elements of Tables B or C along with a time stamp (based on what time in the program the element appeared.) I have seen many databases designed with a table like D that needed to contain references to objects of different types. I would create tableD to look something like this: CREATE TABLE content_log ( ID int auto_increment primary key, tableA_id int not null, Object_ID int not null, Object_type (here you have options, you could use a SET, ENUM, INT, or CHAR datatype), TimeOffset time not null ) 3) create only tables A and D, where table D is a mix of the fields from tables B and C with added a fileld rec_type to handle different fields depending on the record type ( this seems to me to be the simplest solution, although not the best in term of normalization rules ) Hope it was all clear, thanx in advance, Giulio So.. if you wanted to find all of the movies shown during Show # 14 SELECT * FROM content_log WHERE tableA_ID = 14 and object_type='movie' /*or however you set up that column*/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: MySQL on RedHat ES 3.0
Thanks -Original Message- From: Thomas Plümpe [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 10:26 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: RE: MySQL on RedHat ES 3.0 On Mon, 2004-10-11 at 14:52, Ferguson, Michael wrote: Thanks. rpm -a | grep mysql rpm -l | grep mysql rpm -f | grep mysql rpm -g | grep mysql rpm -p | grep mysql All returned nothing, so I am concluding that MySQL is not installed. There's a q missing, and better do a case-insensitive grep: rpm -qa | grep -i mysql Can you please help me out with steps on how to download and install MySQL. Is this by rpm's or gz? You can probably install it with redhat's update tools, which is likely to be the easiest thing (I'm not using RedHat, so I can't tell you exactly how this would work). If you'd rather use the rpms provided by MySQL AB, take a look at the docs here: http://dev.mysql.com/doc/mysql/en/Installing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I need to add all states to an IN?
I have a resume form that picks consultants by state (such as an html guru
in California, or an SAP expert in Alabama). But what if I want to pick from
ANY state?
When I put in criteria, the SQL comes back like so...
SELECT SQL_CALC_FOUND_ROWS * FROM resume INNER JOIN candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('+html' IN
BOOLEAN MODE) AND candidate.Candidate_ID = resume.Candidate_ID AND
candidate.Location IN ('CA', 'California') LIMIT 0 , 10
(note the candidate.Location IN('CA','California') ).
Now, when I want to pull back ANY state, I need to pull it back from
anywhere. If I leave the candidate.Location part off in my code, will it
automatically search anywhere regardless of state location? Or do I need to
go through my states database and put it into an array so that the IN
portion is filled in with all states?
If so... what's the proper structure to list all states in this statement?
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do I need to add all states to an IN?
At 11:25 -0400 10/11/04, Eve Atley wrote:
I have a resume form that picks consultants by state (such as an html guru
in California, or an SAP expert in Alabama). But what if I want to pick from
ANY state?
When I put in criteria, the SQL comes back like so...
SELECT SQL_CALC_FOUND_ROWS * FROM resume INNER JOIN candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('+html' IN
BOOLEAN MODE) AND candidate.Candidate_ID = resume.Candidate_ID AND
candidate.Location IN ('CA', 'California') LIMIT 0 , 10
(note the candidate.Location IN('CA','California') ).
Now, when I want to pull back ANY state, I need to pull it back from
anywhere. If I leave the candidate.Location part off in my code, will it
automatically search anywhere regardless of state location? Or do I need to
go through my states database and put it into an array so that the IN
portion is filled in with all states?
Either way would work, but clearly the first is simpler. :-)
If so... what's the proper structure to list all states in this statement?
It would just be a longer IN() list.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: lock tables
You only need to lock whene you are going to run a query that contains a series of actions and they all have to happen at the same time. As for single queries, they are already atomic, so you don't need to put and locks around them. On Mon, 11 Oct 2004 11:14:36 +0100, Melanie Courtot [EMAIL PROTECTED] wrote: Hi, I'm a bit confused by the lock mechanism under mysql. When user A does an update on table 1, the table is automatically locked by mysql?that means at the same time user B won't be able to modify the same row? Or do I have to specify the lock for each query? And what about temporary tables? If anybody has a simple explanation or a link on a doc thanks, Melanie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How Do I Determine the Server's Version on Old Server?
mysqladmin version On Mon, 11 Oct 2004 10:36:54 -0400, Michael Stassen [EMAIL PROTECTED] wrote: You should keep threads on the list. That way, more people can help, and more can benefit from the answers. I've not looked at the code behind mysql_get_server_info(), but every version of mysql I've seen has 3 parts to the version number. It seems clear that the mysql version numbering scheme is release.version, where release is 3.23, 4.0, 4.1, or 5.0, and version is sequential. Assuming you'll always get a 3 part version seems safe to me. Michael Matthew Boehm wrote: Will mysql_get_server_info() give you what you need? http://dev.mysql.com/doc/mysql/en/mysql_get_server_info.html I guess I could use that and parse out on the . separator. Will I always get a 3 . separated string? ie: X.XX.XX ? Or could I sometimes get X.XX? Thanks, Matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to unpack a table?
Hi, I've just accidentally run myisampack (myisampack Ver 1.22) on a table which I still need to update. Is there anyway I can unpack it? Thanks, JS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on RedHat ES 3.0
rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RedHat ES 3.0
Thanks. Here is what it got after I ran your command. It seems that mysql and php is already installed. Right??? [EMAIL PROTECTED] root]# rpm -qa | grep -i mysql libdbi-dbd-mysql-0.6.5-5 mysql-3.23.58-1 perl-DBD-MySQL-2.1021-3 mysql-devel-3.23.58-1 mod_auth_mysql-20030510-1.ent php-mysql-4.3.2-8.ent MySQL-python-0.9.1-6 -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:29 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: Re: MySQL on RedHat ES 3.0 rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Read-Only DB User
Run SELECT * FROM user; in the mysql database. All of the options are obvious. On Mon, 11 Oct 2004 19:28:49 +0530, Anil Doppalapudi [EMAIL PROTECTED] wrote: First connect to mysql as root user and issue the following command grant select on databasename.* to username@ipaddress identified by 'passwd'; flush privileges; it will grant only select privilege to the newly created user on database and he can only connect from the ipaddress specified in command Anil DBA -Original Message- From: Lee Zelyck [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 7:30 AM To: [EMAIL PROTECTED] Subject: Read-Only DB User Hi All, I'm sorry to access such a basic question, but I couldn't find a specific answer to it in the mysql manual pages. The question is, how would someone create a basic read-only user for a single db? I just intend for it to be used by a script to validate data in the db itself. Anyway, if anyone can provide a lean and concise statement that will provide this, it would be very much appreciated. Thanks! Lee __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mac OS X 3-4x slower reading rows than x86?!
I have some queries that return around 75,000 rows, and I've been trying to figure out how to speed them up a little. In the course of looking for the bottleneck, I discovered that simply getting a large result was considerably slower on OS X. I tested on a number of machines, with MySQL versions from 4.0.16 to 4.1.5, running either Mac OS X 10.3.x, Linux, or FreeBSD. In each case, the x86 machine reported a time of 0.12 sec - 0.20 sec for the last SELECT below, and the Mac reported in the range of 0.75 sec - 1.0 sec. Configurations (RAM/CPU MHz/system load) were similar (tested on a dual 2GHz G5), and I checked all MySQL variables that seemed relevant. Speeds were consistent and repeatable. I understand that MySQL may be more optimized for x86, or just run better on that architecture, but a difference of this magnitude seems wrong. I'm interested in the reported time for the last query below. mysql running on same machine as mysqld. Setup: CREATE TEMPORARY TABLE tmp (tid MEDIUMINT UNSIGNED NOT NULL); INSERT INTO tmp values (42); CREATE TEMPORARY TABLE tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; INSERT INTO tmp2 SELECT * FROM tmp; INSERT INTO tmp SELECT * FROM tmp2; Benchmark (75,025 rows): SELECT * FROM tmp; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on RedHat ES 3.0
Yup. On Mon, 11 Oct 2004 11:33:50 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: Thanks. Here is what it got after I ran your command. It seems that mysql and php is already installed. Right??? [EMAIL PROTECTED] root]# rpm -qa | grep -i mysql libdbi-dbd-mysql-0.6.5-5 mysql-3.23.58-1 perl-DBD-MySQL-2.1021-3 mysql-devel-3.23.58-1 mod_auth_mysql-20030510-1.ent php-mysql-4.3.2-8.ent MySQL-python-0.9.1-6 -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:29 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: Re: MySQL on RedHat ES 3.0 rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RedHat ES 3.0
Yes, they seems to be installed already. -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:34 AM To: Benjamin Arai Cc: [EMAIL PROTECTED] Subject: RE: MySQL on RedHat ES 3.0 Thanks. Here is what it got after I ran your command. It seems that mysql and php is already installed. Right??? [EMAIL PROTECTED] root]# rpm -qa | grep -i mysql libdbi-dbd-mysql-0.6.5-5 mysql-3.23.58-1 perl-DBD-MySQL-2.1021-3 mysql-devel-3.23.58-1 mod_auth_mysql-20030510-1.ent php-mysql-4.3.2-8.ent MySQL-python-0.9.1-6 -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:29 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: Re: MySQL on RedHat ES 3.0 rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to unpack a table?
J S [EMAIL PROTECTED] wrote on 11/10/2004 16:28:41: I've just accidentally run myisampack (myisampack Ver 1.22) on a table which I still need to update. Is there anyway I can unpack it? myisamchk --unpack table.MYI http://dev.mysql.com/doc/mysql/en/myisamchk_repair_options.html Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to access field name in C
Hello,
In C you dont have named access to the columns as they
are returned as an array of char*. The only solution
would be to access it using its index value.
Like PHP, you have to do mysql_fetch_row() anyway.
After that access the row by its index.
If you prefer named access and dont mind a minute
perdformance hit, then you can write your own function
that takes FIELD_RES* structure and the name of the
column. The function then loops thru each colum name
and if found returns the char*.
But do keep in mind that the pointer for the currenly
FIELD structure changes in this way.
HTH
Karam
--- Matthew Boehm [EMAIL PROTECTED] wrote:
What is the best way to access a specific field in
C? Its really easy in
PHP...
PHP
---
$res = mysql_real_query($mysql,SELECT col1, col2
FROM table);
while($row = mysql_fetch_row($res)) {
print $row['col1'];
print $row['col2'];
}
Is the only way/best way to do the above in C by
using a nested for-loop?
Ex:
fields = mysql_fetch_fields(res);
while((row=mysql_fetch_row(res)) {
for(x=0;xnumFields;x++) {
sprintf(output, Column name: %s Column
Value: %s\n,
fields[x].name, row[x]);
}
}
Seems painful and extra-loopy to do it in C. Is
there a better way?
Thanks,
Matthew
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to unpack a table?
I've just accidentally run myisampack (myisampack Ver 1.22) on a table which I still need to update. Is there anyway I can unpack it? myisamchk --unpack table.MYI http://dev.mysql.com/doc/mysql/en/myisamchk_repair_options.html Alec Thanks Alec, I haven't actually run myisamchk -rq --sort-index --analyze yet, only myisampack on the MYD tables. Does that mean I don't need to worry? JS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL on RedHat ES 3.0
Before you go further, I would like to point out a caution: When you do a rpm -qa | grep -i mysql It outputs various packages, not necessarily indicate that mysql itself is installed, it might mean different things i.e. libraries, perl, php that supports MySQL. Specifically the output line mysql-3.23.58-1 indicates that the MySQL 3.23.58-1 is installed, therefore, yes the MySQL package is installed. Scott -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:38 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: Re: MySQL on RedHat ES 3.0 Yup. On Mon, 11 Oct 2004 11:33:50 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: Thanks. Here is what it got after I ran your command. It seems that mysql and php is already installed. Right??? [EMAIL PROTECTED] root]# rpm -qa | grep -i mysql libdbi-dbd-mysql-0.6.5-5 mysql-3.23.58-1 perl-DBD-MySQL-2.1021-3 mysql-devel-3.23.58-1 mod_auth_mysql-20030510-1.ent php-mysql-4.3.2-8.ent MySQL-python-0.9.1-6 -Original Message- From: Benjamin Arai [mailto:[EMAIL PROTECTED] Sent: Monday, October 11, 2004 11:29 AM To: Ferguson, Michael Cc: [EMAIL PROTECTED] Subject: Re: MySQL on RedHat ES 3.0 rpm -qa | grep -i mysql On Mon, 11 Oct 2004 09:12:54 -0400, Ferguson, Michael [EMAIL PROTECTED] wrote: G'Day All, I successfully installed RedHat ES 3.0 and would like to get MySQL installed on it. After reading the online manual at dev.mysql.com/doc/mysql/en/Installation_layouts.html I am still a bit unsure of my next move. Can some please help me out with directions on how to check the system to verify whether or not MySQL is installed, and how to download and install MySQL on this server. Many thanks and best wishes. Ferg. -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to unpack a table?
J S [EMAIL PROTECTED] wrote on 11/10/2004 16:56:28: I've just accidentally run myisampack (myisampack Ver 1.22) on a table which I still need to update. Is there anyway I can unpack it? myisamchk --unpack table.MYI http://dev.mysql.com/doc/mysql/en/myisamchk_repair_options.html Alec Thanks Alec, I haven't actually run myisamchk -rq --sort-index --analyze yet, only myisampack on the MYD tables. Does that mean I don't need to worry? No - I think your data is now packed, so you need to unpack it. I haven't done it myself, but one of my customers did exactly what you did last Friday, and recovered by using the myisamchk feature. I wans't there, so check the exact syntax from the manual. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best way to access field name in C
Dear Matthew,
PHP
---
$res = mysql_real_query($mysql,SELECT col1, col2 FROM
table); while($row = mysql_fetch_row($res)) {
print $row['col1'];
print $row['col2'];
}
Is the only way/best way to do the above in C by using a
nested for-loop?
Ex:
fields = mysql_fetch_fields(res);
while((row=mysql_fetch_row(res)) {
for(x=0;xnumFields;x++) {
sprintf(output, Column name: %s Column Value:
%s\n, fields[x].name, row[x]);
}
}
Seems painful and extra-loopy to do it in C. Is there a better way?
the php-code does not exactly the same than the lines in c.
The only difference was to add a call to mysql_store_result ().
But you may put the first two calls into a function to have it
in one row.
mysql_real_query (pMySQL, ...);
res = mysql_store_result (pMySQL);
while((row=mysql_fetch_row(res)) {
printf(%s%s, row[1], row[2]);
}
Regards,
TomH
--
PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121
Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0
Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED]
AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to access field name in C
Matthew Boehm wrote:
What is the best way to access a specific field in C? Its really easy in
PHP...
http://dev.mysql.com/doc/mysql/en/mysql_fetch_row.html
you won't have the hash-table feature offered by PHP, but nothing stop
you to do the same.
PHP
---
$res = mysql_real_query($mysql,SELECT col1, col2 FROM table);
while($row = mysql_fetch_row($res)) {
print $row['col1'];
print $row['col2'];
}
Is the only way/best way to do the above in C by using a nested for-loop?
Ex:
fields = mysql_fetch_fields(res);
while((row=mysql_fetch_row(res)) {
for(x=0;xnumFields;x++) {
sprintf(output, Column name: %s Column Value: %s\n,
fields[x].name, row[x]);
}
}
Seems painful and extra-loopy to do it in C. Is there a better way?
PHP hide that loop somewhere, but it use the C api anyway...
If you know your query you can access row[0] or row[2] directly, isn't it ?
If you are familiar with C++, you could program something like
row[col1]. (does anyone know if the c++ api offer that sort of feature ? )
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Benjamin Arai http://www.cs.ucr.edu/~barai [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CORRECT WAY TO SQUEEZE INNODB 4.0.17
I my network I have 1 master and 4 slaves. I need to squeeze the innodb on my master; which is the correct way to execute the squeeze action (now the innodb files is around 1gb, while in a new db is around 300 mB) thanks Massimo - Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charset problem
Hi, sometime ago my boss imported a dump into a base using Cocoa MySQL on Mac. Unfortunatly he switch the charset from ISO-8859-1 to something wrong, probably UTF-8. From this time we have such weird characters in our fields : FerrandiËre instead of Ferrandière, CitÈ instead of Citée and so on. The other problem is that he noticed mistake a few days after the import and he had trashed the correctly encoded dump in the meantime. So now we have only a base with wrongly encoded fields values. What is the way to fix that please ? Thanks in advance. Jean-Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Diffrences in table types
Yes, if you use both table types within a single database then you will have to split up the memory usage. However, in many databases there are just one or two tables that use 90% of the disk/memory space. If this is your situation then you just allocate most of the memory for the table type these tables use (assumign they use the same type), and you won't have any performance problem because the others don't need much memory. However, if your data is evenly split and evenly accessed between the two table types then splitting the memory may present some performance degredation. Of course the solution is buy more memory. John On Mon, 2004-10-11 at 09:49 -0700, Benjamin Arai wrote: Here is another question. Can you achieve the same performance having to different kinds of databases as though you were only using one? I am assuming that you are going to run into problems because you cannot set both types of databases to have a lot of memory allocated to them. Right? On Mon, 11 Oct 2004 09:23:18 -0700, John McCaskey [EMAIL PROTECTED] wrote: As far as I know memory usage between the two table types is roughly the same. The way memory is setup/used is somewhat different however. For myisam the primary memoy buffer to accelerate queries is the key_buffer which caches data for keys. In innodb you have more options to set with the main one being the innodb_buffer_pool_size which is used for caching keys and data, you want to set this as large as possible. You also have several other adjustable buffers inlcuing an 'additonal_mem_pool' which I'm not quite sure what it is used for, and the log_buffer which is used for transaction related memory I believe. So, if you are going to be using both MyISAM and InnoDB you will need seperate buffers, which will of course increase total memory usage, or leave a smaller size for both. But if you switch completely to InnoDB you can drop the MyISAM buffers down to almost nothing (still need them as the mysql table with user data etc uses them, but say 8megs would be plenty). John On Sun, 2004-10-10 at 10:51 +0200, Jacques Jocelyn wrote: Hello John, Interesting post, quite useful, Question about performance with InnoDB ? say you have a hosting server with 256 Mb of ram, would you know if that will make a difference if the major database is converted from MyIsam to InnoDb ? Although, InnoDB is not a requirement, just luxury, but I would love to enjoy foreign keys and transactions Please advise, Thanks Sunday, October 10, 2004, 8:39:15 AM, you wrote: JM I meant 'No transaction support', which is you can't use JM begin work; ... ; commit; etc to perform transactions, each query JM takes effect immeiately and is visible to all other JM threads/clients immediately. ... JM Concurrency refers to multiple seperate connections (threads) JM trying to read/write to/from the same table at the same time. JM Imagine you have 100 different connections to the database all JM trying to write to the same table. With MyISAM each one will lock JM the entire table, and only one will execute at a time, making it JM very slow. In InnoDB each one will only lock the rows it is JM modifying and they can all execute at once (if they are not JM modifying the same rows), and it will be very fast. Best regards, Jacques Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password() function
At 11:41 -0400 10/11/04, Jerry Swanson wrote:
I create table and used password
// CHAR(15)
select password('123456');
++
| password('123456') |
++
| 565491d704013245 |
++
//INT(10)
+-+
| password|
+-+
| 565491d70401324 |
When I used char(15) the data was not complete.
What data type I should use for password function?
Actually, you should use a different function than PASSWORD(), which
should be used only in connection with account information in the
grant tables in the mysql database. SHA() or MD5() some possibilities.
See this section in the manual:
http://dev.mysql.com/doc/mysql/en/Encryption_functions.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Re: MySQL 4.0 and concat
Sorry. This should have gone back to the list. -- Mensaje reenviado -- Subject: Re: MySQL 4.0 and concat Date: Lun 11 Oct 2004 11:37 From: Alfredo Cole [EMAIL PROTECTED] To: [EMAIL PROTECTED] El Lun 11 Oct 2004 08:35, escribió: Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. I will try this. But there will always be times when using concat might be required. It would be nice to know if there is a solution to the concat problem. Thank you, and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom --- -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 and concat
A bit of a warning, if the fields are DATETIME rather than DATE, add the appropriate hours:minutes:seconds to the test WHERE datetimefield '1999-02-01 23:59:59'. When comparing DATETIME fields with date strings '00:00:00' is assumed and that can cause problems if one forgets that. Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. SELECT * FROM sampletable WHERE datefield = '1999-01-12' and datefield '1999-02-01' This example query will get all of the records from sampletable that were entered after January 11th and before February 1st. It will also be **very** fast if the column datefield is indexed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM: Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: MySQL server doesn't start.
Hi, It is necessary to post in English (enough with Janglish) so that a lot of people may understand because it is an international mailing list. If you hope for the discussion in Japanese, you can apply for participation in Japanese user group's mailing list (http://www.mysql.gr.jp/ml.html) /usr/local/libexec/mysqld: unrecognized option `--key_buffer=16M' /usr/local/libexec/mysqld Ver 3.23.57 for pc-linux on i686 Perhaps, the cause is the same as the content of the following thread: http://lists.mysql.com/mysql/172283 -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What am i up against
This maybe somewhat of a silly question. Scenario - I am pretty much a noob at both relational databases and web programming. I've built most of my pages using a RAD tool that, for the most part, does a decent job , if you keep it fairly simple. One of the limitations is inserts updates are done on a one form on one page into one table basis. There is a MtM feature. Anyway, now I'm flying solo and have created a form that spans 5 pages and will insert into (I lost count) I believe 3-5 tables. I want to make sure I make provisions for rollback. All but one table is innodb. While Im reading and digging around, wondering is this a massive insert statement ? Would joins need to be involved ? I'm imagining it's more of a step by step (1 table at a time) process. With rollback, if an insertion is already done into 1 table , and the insert into table 2 fails, does that mean table 1's insertion would be deleted ? I think that is probably enough and I apologize for asking what are basic questions and a bit scattered at that. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alias not allowed in WHERE clause?
SELECT city, state, SQRT( POWER( ( latitude - 39.039200 ), 2 ) + POWER( ( longitude + 95.689508 ), 2 ) ) AS distance FROM geographics WHERE distance 1 ORDER BY distance LIMIT 10; Returns: #1054 - Unknown column 'distance' in 'where clause' Are alias not allowed in WHERE clauses? I am able to replace the alias with the entire math function, and it works as desired. However, I do not like the heaviness/repetiveness of the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alias not allowed in WHERE clause?
At 11:32 -0700 10/11/04, Nathan Clark wrote: SELECT city, state, SQRT( POWER( ( latitude - 39.039200 ), 2 ) + POWER( ( longitude + 95.689508 ), 2 ) ) AS distance FROM geographics WHERE distance 1 ORDER BY distance LIMIT 10; Returns: #1054 - Unknown column 'distance' in 'where clause' Are alias not allowed in WHERE clauses? How could they be? The WHERE clause determines which rows to select. Aliases are defined for columns from the rows that have been selected. I am able to replace the alias with the entire math function, and it works as desired. However, I do not like the heaviness/repetiveness of the query. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: MySQL server doesn't start.
Hi, It is necessary to post in English (enough with Janglish) so that a lot of people may understand because it is an international mailing list. If you hope for the discussion in Japanese, you can apply for participation in Japanese user group's mailing list (http://www.mysql.gr.jp/ml.html) /usr/local/libexec/mysqld: unrecognized option `--key_buffer=16M' /usr/local/libexec/mysqld Ver 3.23.57 for pc-linux on i686 Perhaps, the cause is the same as the content of the following thread: http://lists.mysql.com/mysql/172283 -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CORRECT WAY TO SQUEEZE INNODB 4.0.17
On Monday 11 October 2004 11:57 am, Massimo Petrini wrote: I my network I have 1 master and 4 slaves. I need to squeeze the innodb on my master; which is the correct way to execute the squeeze action (now the innodb files is around 1gb, while in a new db is around 300 mB) thanks There isnt one, other then to recreate the db.. That I know of. Jeff pgpeOFdWpcR3a.pgp Description: PGP signature
Re: What am i up against
If you are saying the user would navigate through multiple pages updating a table on each page and you want to treat all the updates collectively as one transaction, that is a bad idea. You want to pass all the data required for a single transaction in one request so it can be committed or rolled back as part of the same request. Web pages are generally stateless unless you are using session state variables which is not a good idea in terms of scalability. You don't want to keep transactions open from page to page. If you have a transaction that updates multiple tables and you roll it back, it will negate all changes (inserts, updates, deletes) that occurred within the transaction. Hope that helps, not sure I'm understanding your question. Regards, Joe Audette Stuart Felenstein [EMAIL PROTECTED] wrote: This maybe somewhat of a silly question. Scenario - I am pretty much a noob at both relational databases and web programming. I've built most of my pages using a RAD tool that, for the most part, does a decent job , if you keep it fairly simple. One of the limitations is inserts updates are done on a one form on one page into one table basis. There is a MtM feature. Anyway, now I'm flying solo and have created a form that spans 5 pages and will insert into (I lost count) I believe 3-5 tables. I want to make sure I make provisions for rollback. All but one table is innodb. While Im reading and digging around, wondering is this a massive insert statement ? Would joins need to be involved ? I'm imagining it's more of a step by step (1 table at a time) process. With rollback, if an insertion is already done into 1 table , and the insert into table 2 fails, does that mean table 1's insertion would be deleted ? I think that is probably enough and I apologize for asking what are basic questions and a bit scattered at that. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] [EMAIL PROTECTED] http://www.joeaudette.com http://www.mojoportal.com
Re: What am i up against
See interspersed: --- Joe Audette [EMAIL PROTECTED] wrote: If you are saying the user would navigate through multiple pages updating a table on each page and you want to treat all the updates collectively as one transaction, that is a bad idea. I agree, that is what I am trying to not do. You want to pass all the data required for a single transaction in one request so it can be committed or rolled back as part of the same request. Exactly what I want to accomplish. Web pages are generally stateless unless you are using session state variables which is not a good idea in terms of scalability. You don't want to keep transactions open from page to page. I don't understand this. I assume you are referring to the application session variables (and I'm using PHP). Yes I'm using session variables to collect the data. How am I keeping transactions open ? Since I don't want to do a transaction till the very end. All I'm doing is bringing the data to last stage. After it's all been collected. If you have a transaction that updates multiple tables and you roll it back, it will negate all changes (inserts, updates, deletes) that occurred within the transaction. Hope that helps, not sure I'm understanding your question. That's because my question was somewhat convoluted due to me not completely understanding all of it myself. Stuart Regards, Joe Audette -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where clause question
I'm getting an unknown column error for discount with the following query. Any idea why? -Ed SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` where discount '10' limit 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to unpack a table?
I've just accidentally run myisampack (myisampack Ver 1.22) on a table which I still need to update. Is there anyway I can unpack it? myisamchk --unpack table.MYI http://dev.mysql.com/doc/mysql/en/myisamchk_repair_options.html Alec Thanks Alec, I haven't actually run myisamchk -rq --sort-index --analyze yet, only myisampack on the MYD tables. Does that mean I don't need to worry? No - I think your data is now packed, so you need to unpack it. I haven't done it myself, but one of my customers did exactly what you did last Friday, and recovered by using the myisamchk feature. I wans't there, so check the exact syntax from the manual. Alec OK, managed to unpack all the tables, although ran into a problem with one table where the myisamchk failed with an error 28 (filesystem full). I ran myisamchk on the table again (setting the tmp directory to a bigger filesystem) , and this time although it finished, it displayed lots of warnings about duplicate keys... I ran myisamchk with -d: # myisamchk -d url_visit MyISAM file: url_visit Record format: Fixed length Character set: latin1 (8) Data records: 6826673 Deleted blocks: 1275276 Recordlength: 20 table description: Key Start Len Index Type 1 2 4 unique unsigned long 2 7 4 unique unsigned long 114 unsigned long 154 unsigned long 6 1 binary and it seems to have deleted some blocks. I think I've lost some data? Is there anything I can do apart from a restore to fix this? Thanks, JS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where clause question
Because discount isn't one of: 1) a field on one of the tables your query is based on 2) a formula based on one or more of the fields from one or more of the tables your query is based on. What it is: an alias to the results of a function applied to 2 fields on one of your tables. Since the name discount is a reference to some of the *results* of this particular query, it will be impossible for the WHERE clause to use the RESULTS of a query to determine what rows should PARTICIPATE in the query (WHERE clauses are evaluated BEFORE aliases are determined). It's kind of like trying to drink from a glass before you fill it up. Understand? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Lazor [EMAIL PROTECTED] wrote on 10/11/2004 04:33:27 PM: I'm getting an unknown column error for discount with the following query. Any idea why? -Ed SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` where discount '10' limit 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where clause question
Great explanation. By the way, Ed, what you might be looking for is the HAVING clause, which culls records right before the LIMIT is applied. SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` HAVING discount 10 LIMIT 10 Note that this is, by nature, /very/ slow: you're basically selecting ALL records, then winnowing out records according to the HAVING clause (with no optimization), then throwing everything but the first 10 records away. You can read more about it here: http://dev.mysql.com/doc/mysql/en/SELECT.html Search for HAVING. Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: Ed Lazor [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, October 11, 2004 3:51 PM Subject: Re: Where clause question Because discount isn't one of: 1) a field on one of the tables your query is based on 2) a formula based on one or more of the fields from one or more of the tables your query is based on. What it is: an alias to the results of a function applied to 2 fields on one of your tables. Since the name discount is a reference to some of the *results* of this particular query, it will be impossible for the WHERE clause to use the RESULTS of a query to determine what rows should PARTICIPATE in the query (WHERE clauses are evaluated BEFORE aliases are determined). It's kind of like trying to drink from a glass before you fill it up. Understand? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Lazor [EMAIL PROTECTED] wrote on 10/11/2004 04:33:27 PM: I'm getting an unknown column error for discount with the following query. Any idea why? -Ed SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` where discount '10' limit 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alias not allowed in WHERE clause?
Well, it might not be SQL standard, but most databases out there allow you to use the alias in your where clauses. It helps make the sql more readable, and it shouldn't be that hard to add this feature to the parser, so it can translate that alias back to the original row-source selection, during parse time. /morten Paul DuBois wrote: At 11:32 -0700 10/11/04, Nathan Clark wrote: SELECT city, state, SQRT( POWER( ( latitude - 39.039200 ), 2 ) + POWER( ( longitude + 95.689508 ), 2 ) ) AS distance FROM geographics WHERE distance 1 ORDER BY distance LIMIT 10; Returns: #1054 - Unknown column 'distance' in 'where clause' Are alias not allowed in WHERE clauses? How could they be? The WHERE clause determines which rows to select. Aliases are defined for columns from the rows that have been selected. I am able to replace the alias with the entire math function, and it works as desired. However, I do not like the heaviness/repetiveness of the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where clause question
Interesting. I thought you could sort by aliases. Thanks Shawn. The easy answer was to just add the calculation to the where section as well. But which approach is faster - having or the calculation? Ie. select ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount from products where round( ( (MSRP - Price) / MSRP) * 100) 10 - OR - select ... HAVING discount 10 ? -Original Message- Great explanation. By the way, Ed, what you might be looking for is the HAVING clause, which culls records right before the LIMIT is applied. SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` HAVING discount 10 LIMIT 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where clause question
Ah. Well that's a different question. You can, in fact, use aliases in ORDER BY (and GROUP BY): SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) AS discount FROM products ORDER BY discount ASC LIMIT 10 Now, regarding HAVING, I would imagine the HAVING clause would be faster, assuming you actually want the value of discount in the result of the SELECT. Otherwise, you're doing the calculation twice. No idea if that's true, though, so maybe someone else can give a definitive answer. Eamon Daly - Original Message - From: Ed Lazor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 11, 2004 4:51 PM Subject: RE: Where clause question Interesting. I thought you could sort by aliases. Thanks Shawn. The easy answer was to just add the calculation to the where section as well. But which approach is faster - having or the calculation? Ie. select ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount from products where round( ( (MSRP - Price) / MSRP) * 100) 10 - OR - select ... HAVING discount 10 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alias not allowed in WHERE clause?
You may use Alias's if you use HAVING instead of WHERE this is one of the defined difrerences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren't expecting great speed. John On Mon, 2004-10-11 at 23:51 +0200, Morten Egan wrote: Well, it might not be SQL standard, but most databases out there allow you to use the alias in your where clauses. It helps make the sql more readable, and it shouldn't be that hard to add this feature to the parser, so it can translate that alias back to the original row-source selection, during parse time. /morten Paul DuBois wrote: At 11:32 -0700 10/11/04, Nathan Clark wrote: SELECT city, state, SQRT( POWER( ( latitude - 39.039200 ), 2 ) + POWER( ( longitude + 95.689508 ), 2 ) ) AS distance FROM geographics WHERE distance 1 ORDER BY distance LIMIT 10; Returns: #1054 - Unknown column 'distance' in 'where clause' Are alias not allowed in WHERE clauses? How could they be? The WHERE clause determines which rows to select. Aliases are defined for columns from the rows that have been selected. I am able to replace the alias with the entire math function, and it works as desired. However, I do not like the heaviness/repetiveness of the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Where clause question]
Sorry, mailed it in html format. Read answer below Original Message Subject:Re: Where clause question Date: Tue, 12 Oct 2004 00:00:12 +0200 From: Morten Egan [EMAIL PROTECTED] To: Ed Lazor [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Not knowing how the inards of mysql works, I would say the having clause is usually slow, and if done correctly you actually dont execute the calculation twice, because the parser should recognize this as beeing the same as what you've specified in your select part. /morten Ed Lazor wrote: Interesting. I thought you could sort by aliases. Thanks Shawn. The easy answer was to just add the calculation to the where section as well. But which approach is faster - having or the calculation? Ie. select ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount from products where round( ( (MSRP - Price) / MSRP) * 100) 10 - OR - select ... HAVING discount 10 ? -Original Message- Great explanation. By the way, Ed, what you might be looking for is the HAVING clause, which culls records right before the LIMIT is applied. SELECT ID, Title, MSRP, Price, round( ( (MSRP - Price) / MSRP) * 100) as discount FROM `products` HAVING discount 10 LIMIT 10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with table structure
hello, i'm just looking for some examples of a customer table that some of you are using for your ecomm sites (or any site that would need a customer table). here is mine so far: (horrible wrapping to follow...) mysql describe customers; ++-+--+-+--- --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+--- --++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | fname | varchar(20) | | PRI | || | lname | varchar(20) | | PRI | || | address1 | varchar(40) | | | || | address2 | varchar(40) | YES | | || | city | varchar(20) | | | || | state | char(2) | | | || | zip| varchar(10) | | | || | phone | varchar(20) | YES | | || | fax| varchar(20) | YES | | || | email | varchar(64) | | PRI | || | newsletter | tinyint(1) | | | 0 || | password | varchar(32) | | | || | signupdate | datetime| | | -00-00 00:00:00 || | lastvisit | datetime| | | -00-00 00:00:00 || | type | tinyint(3) unsigned | | | 0 || | company| varchar(64) | YES | | || | is_active | tinyint(4) | | | 0 || | activationdate | datetime| | | -00-00 00:00:00 || | activationtype | tinyint(3) unsigned | | | 0 || ++-+--+-+--- --++ i would appreciate not only table descriptions (like mine above)(if you're willing) but comments on what i have so far as well. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Master/Master failover setup question
I have been reading and researching ways to create a failover system for
our MySQL databases that require as little intervention as possible.
However I am having trouble coming up with a way to get the system back
into a stable state after a failover has occurred and the main master has
been fixed.
The idea is a system along the lines of...
Master (A) - Standby-Master (B) - { Slave 1 , Slave 2 , ... , Slave N }
I have defined 2 possible failures, SOFT and HARD. If the master (A)
becomes unresponsive or slow and gets failed out, it would constitue as a
SOFT failure and it would ideally automatically reset the system to its
initial failover capable state when the master (A) has recouperated. A
HARD failure would be anytime the database (A) has crashed and the data
needs to be recreated.
In both cases/failures, the problem I run into is what to do when bringing
the system back into the optimal state without interruption or data
corruption. In degraded mode, writes go to (B) and needs to be switched
back to (A) while keeping replication alive. It can be done with circular
replication but data corruption will happen because of auto increment
fields.
I have found quite a few discussions on the topic of failover setups and
circular replication but haven't found anything that satisfies my needs
yet. Any help/pointers would be greatly appreciated.
Thanks!
Atle
-
Flying Crocodile Inc, Unix Systems Administrator
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)
I'm trying to create a single UPDATE query to deal with the following problem:
==
-- I've got two tables:
CREATE TABLE `banannas` (
`owner` varchar(15) NOT NULL default ''
);
CREATE TABLE `monkeys` (
`name` varchar(15) default NULL,
`banannacount` int(4) default NULL
);
-- I've got three monkeys:
INSERT INTO `monkeys` VALUES ('bonzo',NULL),('dunston',NULL),('ham',NULL);
-- Some of those monkeys have banannas.
-- Some of those monkeys have more than one bananna.
-- Some of those monkeys don't have any banannas.
INSERT INTO `banannas` VALUES ('bonzo'),('bonzo'),('bonzo'),('ham');
==
I'm trying to write an UPDATE query so that monkeys.banannacount is set to the
number of banannas each monkey owns.
mysql SELECT name,COUNT(banannas.owner) as bc FROM monkeys LEFT JOIN banannas
ON monkeys.name=banannas.owner GROUP BY owner;
+-++
| name| bc |
+-++
| dunston | 0 |
| bonzo | 3 |
| ham | 1 |
+-++
I know it's possible to assign an UPDATE with the product of a join, but the
GROUP BY clause is throwing me off.
mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner GROUP BY
banannas.owner SET monkeys.banannas=COUNT(banannas.owner);
ERROR 1064 (42000): 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
'GROUP BY banannas.owner SET monkeys.banannas=COUNT(banannas.owner)' at line 1
mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner SET
monkeys.banannas=COUNT(banannas.owner) GROUP BY banannas.owner;
ERROR 1064 (42000): 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
'GROUP BY banannas.owner' at line 1
Is this possible without subqueries? Is this possible at all?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delete duplicate entry
Hi Gerald, try ALTER IGNORE TABLE. Thank you very much. I should have checked the manual first. http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table. If IGNORE isn't specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, then for rows with duplicates on a unique key, only the first row is used. The others are deleted. --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)
According to the manual http://dev.mysql.com/doc/mysql/en/UPDATE.html, the
multi-table UPDATE syntax is
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
so, you can join tables, but you can't GROUP BY.
You could do this with a temporary table to hold the counts:
CREATE TEMPORARY TABLE banana_count SELECT name, COUNT(banannas.owner) bc
FROM monkeys LEFT JOIN banannas
ON monkeys.name=banannas.owner
GROUP BY owner;
UPDATE monkeys JOIN banana_count USING (name)
SET banannacount=bc;
DROP TABLE banana_count;
Michael
Laszlo Thoth wrote:
I'm trying to create a single UPDATE query to deal with the following problem:
==
-- I've got two tables:
CREATE TABLE `banannas` (
`owner` varchar(15) NOT NULL default ''
);
CREATE TABLE `monkeys` (
`name` varchar(15) default NULL,
`banannacount` int(4) default NULL
);
-- I've got three monkeys:
INSERT INTO `monkeys` VALUES ('bonzo',NULL),('dunston',NULL),('ham',NULL);
-- Some of those monkeys have banannas.
-- Some of those monkeys have more than one bananna.
-- Some of those monkeys don't have any banannas.
INSERT INTO `banannas` VALUES ('bonzo'),('bonzo'),('bonzo'),('ham');
==
I'm trying to write an UPDATE query so that monkeys.banannacount is set to the
number of banannas each monkey owns.
mysql SELECT name,COUNT(banannas.owner) as bc FROM monkeys LEFT JOIN banannas
ON monkeys.name=banannas.owner GROUP BY owner;
+-++
| name| bc |
+-++
| dunston | 0 |
| bonzo | 3 |
| ham | 1 |
+-++
I know it's possible to assign an UPDATE with the product of a join, but the
GROUP BY clause is throwing me off.
mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner GROUP BY
banannas.owner SET monkeys.banannas=COUNT(banannas.owner);
ERROR 1064 (42000): 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
'GROUP BY banannas.owner SET monkeys.banannas=COUNT(banannas.owner)' at line 1
mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner SET
monkeys.banannas=COUNT(banannas.owner) GROUP BY banannas.owner;
ERROR 1064 (42000): 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
'GROUP BY banannas.owner' at line 1
Is this possible without subqueries? Is this possible at all?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monkeys and Banannas (UPDATE .. JOIN .. SET .. GROUP)
Quoting Michael Stassen [EMAIL PROTECTED]: According to the manual http://dev.mysql.com/doc/mysql/en/UPDATE.html, the multi-table UPDATE syntax is UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] so, you can join tables, but you can't GROUP BY. You could do this with a temporary table to hold the counts: CREATE TEMPORARY TABLE banana_count SELECT name, COUNT(banannas.owner) bc FROM monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner GROUP BY owner; UPDATE monkeys JOIN banana_count USING (name) SET banannacount=bc; DROP TABLE banana_count; What if I want to just update one monkey's bananna count without temporary tables? I think I can run this query without a GROUP, but mysql doesn't like the query. mysql SELECT COUNT(banannas.owner) as bc FROM banannas WHERE owner=dunston; ++ | bc | ++ | 0 | ++ 1 row in set (0.07 sec) mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner SET monkeys.banannas=COUNT(banannas.owner) WHERE monkeys.name=ham; ERROR (HY000): Invalid use of group function mysql UPDATE monkeys LEFT JOIN banannas ON monkeys.name=banannas.owner AND monkeys.name=ham SET monkeys.banannas=COUNT(banannas.owner); ERROR (HY000): Invalid use of group function -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
