Help with an SQL query
Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
Marc Pidoux wrote: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... It's not an easy question to answer, Marc. Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. What you have to include in your decision is what the company you work for is used to have. It's not easy and probably expensive too if they have to support another OS/webserver/scripting languag/database than they're used to. Another point of consideration is what you are used to. You mention years of experience in using ASP. If that means you have to learn PHP/MySQL from scratch then that will be very expensive also (counting the extra hours you have to spend to learn all the ins and outs of both PHP and MySQL). Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick split() and data parse
Scott Haneda wrote: I have a field `fedex`, in it is text date, in tab sep \n delimeted form, for example: 90FedEx Home Delivery (Residential) 14.06 20FedEx Express Saver 22.63 (...) Is there some way I can do some string parsing on the text field and get to the 3rd item where the passed in shippign_method equals? You can do something with a combination of the functions SUBSTRING(), LOCATE() and SUBSTRING_INDEX(), but it would of course be a lot wiser extract the information from this field and put it in a table of some sort. That way it would be a lot faster to find the data (no need to parse every single record) and you wouldn't need to write complex expressions to find the data you want... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
[EMAIL PROTECTED] wrote: Jigal van Hemert [EMAIL PROTECTED] wrote on 11/02/2005 03:29:14 AM: If I understand http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html correctly, the index of the primary key is stored as the clustered index together with the data. To me this means that there is no difference between counting the primary key entries and counting the data entries. That would be true if everyone could always see every record. However there could be several active copies (versions) of any record. Each copy would be part of a different transaction. How many records YOU can see can be very different than how many records I can see depending on what's going on in our respective transactions. Sorry for the late reply, I had a day off. I was talking about the difference between COUNT(*) and COUNT(primary key field); I don't think that there is a difference in speed if you take snapshots -- as InnoDB calls them -- into account. If the primary key and the record data are stored together both counts should be equally fast, no matter how many versions of a record exist. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Gobi wrote: Gobi wrote: Arno Coetzee wrote: Gobi wrote: Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. select VBS_ID , max(Date) from weight Actually, I tried that. For one thing, using max() also requires a GROUP BY clause. Otherwise, mysql complains. So I am forced to run the following statement: Select VBS_ID, max(Date) from Weight Group By VBS_ID And it returns: VBS_IDmax(Date)weight 1110/8/200511.5 1210/8/200510.5 At first, I thought I had it right but when I look more closely, the weights are incorrect. The weight for 11 on 10/8/2005 should be 11.52. It looks look it is just returning the weight for 10/3/2005. As a further note on my above post, I added idx to my query: Select idx, VBS_ID, max(Date) from Weight Group By VBS_ID and it returned: idxVBS_IDmax(Date)weight 1 1110/8/200511.5 5 1210/8/200510.5 So actually, it is returning the wrong rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Actually, taking a closer look at your link, I think my query is slightly different from Kemin's. Kemin was trying to get the top numbers from col2 AND col3 together. Therefore, in his original statement, he was trying to order both col2 and col3 in descending order. Me, I just want to get the most recent date for each unique VBS_ID. I don't need to get the max weight so I am not sure if it is necessary to concat my date and weight together. Comments? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Hi Gobi, the problem with your original query is that there is no guarantee that your max(date) and it's associated VBS_ID is picked, so what you have to ensure is that they get picked together, so I think your statement should be like this: SELECT VBS_ID, SUBSTRING( MAX( CONCAT( LPAD( Date,10,'0'),weight, 11 ) AS D LEFT( MAX( CONCAT( LPAD(Date,10,'0'),weight)), 10 ) AS W GROUP BY VBS_ID; What happens here is that you're not doing max on date, you're doing it on the combination of date and weight. What might be a problem though is your dateformat, you might run into problems with that I guess. Also you might have to play with lengths a bit. /Johan Gobi wrote: Johan Höök wrote: Hi Gobi, there was a similar posting in august. See: http://lists.mysql.com/mysql/187436 which I think describes what you want. I'll include a bit of it here as well -- This is out of the MySQL class and is called the Max-Concat trick. What is the biggest country in each continent? The Rows Holding the Group-wise Maximum of a Certain Field MySQL Select Continent, - SUBSTRING(MAX(CONCAT(LPAD(Population,10,'0'),Name)),11) AS Country, - 0+LEFT(MAX(CONCAT(LPAD(Population,10,'0'),Name)),10) AS Population - From Country - Group By Continent - Order By Population DESC; - /Johan Sorry for being slow but this statement is quite confusing and I am having difficulty trying to understand it in order to adapt it to my table. Can you explain it? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access control for mysql database
Hello. Reading these parts of the manual could answer a lot of questions: http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html http://dev.mysql.com/doc/refman/5.0/en/GRANT.html Xiaobo Chen wrote: Hi, all I am trying to find out how to check which user is connecting to the server from which host. I have a database, data_A; I wrote a simple java code to connect this database as user 'root', then it just waits there for input from the keyboard. Before I type anything, it should keep connecting to the database. But when I type 'select current_user()' in mysql prompt, it always says: [EMAIL PROTECTED] Can any one help me figure out this? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
Hi Jigal, all! Jigal van Hemert wrote: [[...]] Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. ^^ No flame war intended, but: Is this a typo, or on which assumptions / figures do you base that? AFAIK, the whole LAMP setup is free-of-charge GPL software, unless the environment requires a commercial MySQL license. (Support agreements would be a different issue.) Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbug
Dear Tracy, Tracy wrote: Dear sir, I am trying to uninstall my old Mysql version 4.0, however, I don't know how to. Please give me some directions on how to do that. You have a very high trust in the capabilities of our crystal balls ... For a question like this, you should definitely _at least_ tell your platform and the package format you used for installation, also the exact MySQL version might be interesting. Jörg PS: This is quite a common omission on this list, but I really cannot understand how (these) posters expect others can help them. -- Joerg Bruehe, Senior Production Engineer 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: PHP/MySQL vs ASP/MSSQL?
I would venture to guess that support was the issue. That would make a fair comparison. - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Joerg Bruehe [EMAIL PROTECTED] Sent: 11/04/2005 06:28 AM To: Jigal van Hemert [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: PHP/MySQL vs ASP/MSSQL? Hi Jigal, all! Jigal van Hemert wrote: [[...]] Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. ^^ No flame war intended, but: Is this a typo, or on which assumptions / figures do you base that? AFAIK, the whole LAMP setup is free-of-charge GPL software, unless the environment requires a commercial MySQL license. (Support agreements would be a different issue.) Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 VoIP: [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: PHP/MySQL vs ASP/MSSQL?
Hi Joerg (and other list readers), Joerg Bruehe wrote: Jigal van Hemert wrote: Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. ^^ No flame war intended, but: Is this a typo, or on which assumptions / figures do you base that? Oooops, I meant to wright lower. Thanks for correcting this. AFAIK, the whole LAMP setup is free-of-charge GPL software, unless the environment requires a commercial MySQL license. (Support agreements would be a different issue.) I agree, and even with a license and a support agreement it will probably be a lot cheaper than a comparable MS based setup (unless I have to believe http://www.microsoft.com/windowsserversystem/facts/topics/tco.mspx which likes me to believe that an MS-based setup is actually cheaper in the end). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
2005/11/4, Marc Pidoux [EMAIL PROTECTED]: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... A setup of ASP/MySQL could be a right option for you if you already know ASP. I'm not sure if it's possible though (using ODBC ?). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP/MySQL vs ASP/MSSQL?
My turn to chime in on this one... I have been using ASP/MySQL on a Windows AND Linux box without any issues. Yes, that's right, ASP, with the right 3rd party software, can even run on Linux. (Don't ask Why??, just note that it can be done!) As long as you create a system DSN (IMOH, the easiest way to go) to connect to your MySQL databases (you have to use the MyODBC 3.51 driver), you won't have any problems coding ASP with MySQL as your backend. You can use the MS Jet to connect, however it puts another layer of complexity into the mix. Just another option... J.R. -Original Message- From: Pooly [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 7:37 AM To: MySQL General Subject: Re: PHP/MySQL vs ASP/MSSQL? 2005/11/4, Marc Pidoux [EMAIL PROTECTED]: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... A setup of ASP/MySQL could be a right option for you if you already know ASP. I'm not sure if it's possible though (using ODBC ?). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
I: Da inoltrare a mysql@lists.mysql.com
Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Bye, Stefano.
MySQL 5.0 : error using max(idrow) on a null value
Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Bye, Stefano.
Re: SQL help for qty Sold YTD...
I spent several minutes looking at your question and your data model and nothing jumped out at me that precluded you from determining the quantity of the this vendor's items sold via this data model. I might have missed something though. I'm a little concerned that your LineItem table appeared to have no primary key. In my opinion, the primary key of a line item table should be an order number (saleTranID?) and then a sequence number (1 for the first item on the order, 2 for the second, etc.) but you (apparently) have no primary key defined at all and don't have a sequence number either. However, that shouldn't keep this particular query from running or returning appropriate rows. I am also assuming that invID is an inventory ID - my brain kept reading it as invoice ID but I learned to ignore it ;-) - where an inventory ID uniquely identifies one product that you sell, e.g. invID 1 might be power supplies for Sony camcorders while invID 2 might be Palm Tungsten E PDAs. This is something I would normally call a product ID if I were doing the naming :-) If, in fact, invID *is* an invoice ID, i.e. something that uniquely identifies a particular sales transaction then there is something wrong which might explain why you're not getting any data. So, assuming I haven't misunderstood anything or simply missed something, I would be inclined to break the query down into chunks. Execute each chunk on its own andmake sure that each chunk delivers what you think it should. If it doesn't, either the query is wrong or the data isn't what you think it is. Verify that the data you expect is there by doing SELECTs against the relevant tables; if the data is there, it's got to be your query that is wrong. Inspect each chunk until you find the culprit(s) in either the SQL or the data. Also, for what it's worth, I would strongly suggest that you set up a test environment with a SMALL quantity of data in each table - 50 rows or less should be plenty for most situations - and try your queries against that test environment. That makes the testing process a lot less painful - why wait for many seconds or even minutes for the query to give you the wrong answer? - and let's you solve the problem faster. It might sound like a lot of work but it shouldn't be; just clone the real tables and then copy a small but representative sample of data from the real tables into the clones. You also asked about performance but there is no way anyone can comment on that without knowing a lot more about what indexes you have and, perhaps, which engine you are using. But, in my opinion, your first effort should be directed toward getting the query running correctly, THEN worry about making it go faster. Rhino - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 04, 2005 12:28 AM Subject: SQL help for qty Sold YTD... I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this: select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category: ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | vendcode | char(3) | YES | MUL | NULL| | | categoryID | int(10) unsigned | | PRI | NULL| auto_increment | ++--+--+-+-+ + Inventory: +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | categoryID | int(11) | YES | MUL | NULL| | | invID| int(10) | | PRI | 0 | | | itemnum | int(11) | YES | MUL | NULL| | +--+---+--+-+-+---+ Sales: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | saletranID | int(10) unsigned | | PRI | NULL| auto_increment | |
Mysqli
I was working on Php web page and attemtped to use stored procedure. Last night on Php line, they said that I should consider MySQLi and to ask MySQL list about it. Do anyone know anything about executing a stored procedure from php page? Something to do with interface or? Do I need something extra for it or? Scott -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql hidden processes
Hello folks, When I arrived at work this morning I noticed an oh-so-fun email from cron reporting that chkrootkit had found a hidden process. After a good hour of research (and some replaced binaries, of course) I came to the conclusion that it was a false positive. Although, while searching I did notice that I have nine processes hidden from both ps and readdir, all mysql. Example output follows: /usr/lib/chkrootkit/chkproc -v -v PID 1230(/proc/1230): not in readdir output PID 1230: not in ps output CWD 1230: /var/lib/mysql EXE 1230: /usr/sbin/mysqld ... (report the same for 1231 - 1238) You have 9 process hidden for readdir command You have 9 process hidden for ps command The command `cat /proc/1230/cmdline` outputs: /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock If I shut down mysqld chkproc reports nothing, and interestingly whether or not mysqld running the main chkrootkit doesn't report the nine processes hidden. I did a bit of googling and looking at mysql.com, but I didn't see anything indicating why these processes are hidden from ps and readdir. Does anyone have any insight? mysqld Ver 4.1.10 for pc-linux-gnu on i386 (Source distribution) on Linux 2.6.9 SMP Thanks, -- Chris Wells Web Developer Lumberjack Mordam Music Group, Inc. 5920 American Rd E Toledo, OH 43613 Fry: I must be a robot. Why else would human women refuse to date me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help for qty Sold YTD...
I would first try refactoring your SQL to use INNER JOIN statements instead of the comma separated lists you are currently using. I would also not use any subqueries. Test this and see if it works for you: SELECT SUM(li.quantity) as qtysoldytd FROM LineItem li INNER JOIN Sales sa on li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005 INNER JOIN Inventory inv on inv.invID = li.invID INNER JOIN Category cat on cat.categoryid = inv.categoryid AND cat.vendcode='AA'; The linkages work like this: 1) LineItem links into Sales through saletranID and YEAR(solddate) 2) Sales links into Inventory through the invID 3) Inventory links to Category through categoryid and vendcode Because I used INNER JOINs, each link in the chain must exist across all tables or the row cannot be added to the final results. Because this query contains several joins and your table sizes are not insignificant it becomes a candidate for what I call piecewize evaluation. Piecewize evaluation is where you take the full query and build your desired results in stages. One stage that jumps out at me is the conversion of vendcode to a list of invID values. Another stage could be isolating just those line items for 2005. I suggest this because JOINing two tables (either by explicit declaration as I do or by comma-separated lists as you did) is a geometrically expensive operation (it's cost to compute grows by multiplying how many rows are participating from each table). If we start with two tables M and N and they each have m and n rows in them, a JOIN operation takes on the order of m*n cycles to compute. If we can somehow shrink each table participating in the JOIN (by pre-selecting certain rows) so that we now have m/4 and n/2 rows to JOIN that reduces your overall cost to (m * n)/8. When we are discussing products of m*n on the order of 100 million rows or so, reducing production time by a factor of 8 is noticable. The situation is even more apparent if you add more tables. Consider if you had tables A, B, and C and they had a,b, and c rows in them. If you had to JOIN those three tables to build a query it would take a*b*c units of time to complete. If we were only able to reduce each table by 10%, that reduces the overall computation to (.9*a)*(.9*b)*(.9*c) = .729(abc) If: a = 50,000 b = 500,000 c = 800,000 records The original execution cost is proportional to: (5 * 50 * 80) = 2 (2.0e16) after 10% reductions through precomputations: 2.0e16 * .729 = 1.458e16 --- # of rows combinations NOT fed through the CPU to be evaluated as being in the result or not: 2.0e16 - 1.458e16 = 5.42e+15 = 5420 How long do you think it takes even a modern computer to do 5420 tests? It can make a serious difference. Piecewize evaluation works VERY WELL in stored procedures (if you are on v5.0 or higher) because you can parameterize your queries quite easily and you are assured of executing the same query pattern every time you need it. ## stage 1 - identifying Line items from 2005 CREATE TEMPORARY TABLE tmpLI ( KEY(invID) ) SELECT li.invID, li.quantity FROM LineItem li INNER JOIN Sales sa on li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005 ## stage 2 - identifying Inventory Items for a certain category CREATE TEMPORARY TABLE tmpInv ( KEY(invID) ) SELECT DISTINCT invID FROM Inventory inv on inv.invID = li.invID INNER JOIN Category cat on cat.categoryid = inv.categoryid AND cat.vendcode='AA'; ## stage 3 - compute your desired results SELECT SUM(li.quantity) FROM tmpLI li INNER JOIN tmpInf inv ON inv.invID = li.invID; ## stage 4 - the database is not your momma. Clean up after yourself... DROP TEMPORARY TABLE tmpLi; DROP TEMPORARY TABLE tmpInv; ## end query I hope that helps (HTH), Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 11/04/2005 12:28:50 AM: I cannot figure this one out. I have a Category table with 50,000 records, an Inventory table with over 2 million records. A Sales table with 500,000 records. And a LineItem table with 800,000 records pairing the Inventory ID with the Sales Transaction ID and Quantity. I need to generate a Quantity sold year to date for a certain vendor. The vendor code can be found in the Category table which has a relationship with Inventory. I am trying a SQL statement like this: select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID IN (select invID from Inventory where categoryid IN (select categoryid from Category where vendcode='AA')) this yields null when I know there are sales for that vendor in 2005. Simplified schemas for the tables are as follows: Category:
Re: PHP/MySQL vs ASP/MSSQL?
Jigal van Hemert wrote: Hi Joerg (and other list readers), Joerg Bruehe wrote: Jigal van Hemert wrote: Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). You could always run MySQL on Windows Server along with your IIS and ASP. You don't need to throw the baby out with the bathwater. If you decide to keep your ASP code, then I would keep your IIS setup, as opposed to running something like Chilisoft and Apache. -- Erich Beyrent -- http://www.beyrent.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Edit MySQL through MS Access?
In MS Access, linked tables (not imported tables) are live with the backend. If you change something in a linked table, that change automatically propogates to the origin of the link and changes the source of the data. Try setting up your tables as linked tables and see if it helps. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Marc Pidoux [EMAIL PROTECTED] wrote on 11/04/2005 01:14:25 AM: Yes! That's so cool, took me a little while to have it working but it works. I can retrieve a MySQL table in MS Access and even upload a new table from MS Access to MySQL but I cannot update it from MS Access, when I try to upload an updated table, I get an error Table already exists which makes sense but I want to update/overwrite it. Something like downloading the table from MySQL, edit some of the fields and data in MS Access and reupload the whole thing. Is this possible too? Thanks again! Marc Dan Nelson wrote: In the last episode (Nov 03), Marc Pidoux said: Eh, eh, sorry, stupid question for some of you, I'm sure... I'm wondering if there is a way to edit a MySQL DB through MS Access like you can for an MSSQL DB? I want to edit a lot of data, tables etc... and doing it through phpMyAdmin just isn't very efficient. Sure. Just install the MySQL ODBC connector and link to the tables same as you would for MS SQL. There is a whole section in the manual detailing this: http://dev.mysql.com/doc/refman/5.0/en/msaccess.html
Re: PHP/MySQL vs ASP/MSSQL?
Jigal van Hemert [EMAIL PROTECTED] wrote on 11/04/2005 03:09:58 AM: Marc Pidoux wrote: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... It's not an easy question to answer, Marc. Win/IIS/ASP/MSSQL might need a bit more hardware to run on compared to Linux/Apache/MySQL/PHP (aka LAMP). The license costs for a LAMP setup will probably be higher. Both will be able to do the job. What you have to include in your decision is what the company you work for is used to have. It's not easy and probably expensive too if they have to support another OS/webserver/scripting languag/database than they're used to. Another point of consideration is what you are used to. You mention years of experience in using ASP. If that means you have to learn PHP/MySQL from scratch then that will be very expensive also (counting the extra hours you have to spend to learn all the ins and outs of both PHP and MySQL). Kind regards, Jigal. I agree, unless you have the time and resources to learn and support a different OS and development environment stick within your comfort zone. If you have been using ASP for any length of time then you are probably used to using ODBC (or OLEDB) for data access. MySQL has an excellent ODBC driver. If you are moving to ASP.NET there is also a .NET connector for MySQL that also works very well. I am not suggesting that one platform is in general better than the other, I am suggesting that with your background, your best bet on a good project outcome will be to stay with what you know and what your client can support (even if you are the client). I would heed Jigal's advice. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: INSERT DELAYED crash in 5.0.15
I also have the same problem upgrading from 4.1.15 to 5.0.15; this query hang mysql: INSERT DELAYED INTO phpads_adstats SET clicks = 0, views = 1, day = NOW(), hour = HOUR(NOW()), bannerid = '97', zoneid = '15', source = '' This is happening with every linux mysql 5.0.15 binary distribuition (dynamic, static debug). here is the stack trace 0x809d632 handle_segfault + 430 0x82d74c8 pthread_sighandler + 184 0x808d519 store__15Field_varstringPCcUiP15charset_info_st + 245 0x80530ea save_in_field__11Item_stringP5Fieldb + 86 0x80d55a8 fill_record__FP3THDRt4List1Z4ItemT1b + 112 0x80d5620 fill_record_n_invoke_before_triggers__FP3THDRt4List1Z4ItemT1bP19Table_triggers_list14trg_event_type + 40 0x80f5266 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4ItemT2T215enum_duplicatesb + 1366 0x80b04a1 mysql_execute_command__FP3THD + 7405 0x80b53ba mysql_parse__FP3THDPcUi + 286 0x80ad283 dispatch_command__F19enum_server_commandP3THDPcUi + 1747 0x80acba3 do_command__FP3THD + 195 0x80ac104 handle_one_connection + 764 0x82d4c7c pthread_start_thread + 220 0x82fe57a thread_start + 4 Kind Regards, Nat [EMAIL PROTECTED] wrote: Description: I have a 1M hit a day web server that logs ad hits to mysql. Always been solid as a rock. Most recently on mysql 4.1.14. We upgraded our backup servers to 5.0.15, no problem. We upgraded the live server and the mysqld crashes about once a second. Here is the SQL that crashes it: INSERT DELAYED INTO views SET ad_id='4', host='pool-68-239-6-162.bos.east.verizon.net', ip='1156515490' Remove the DELAYED and no problems. Here is a stack trace: 0x81876bc handle_segfault + 668 0xb7f6edfd _end + -1348763991 0xb7d6b307 _end + -1350876237 0x816994e _ZN15Field_varstring5storeEPKcjP15charset_info_st + 206 0x81043d7 _ZN11Item_string13save_in_fieldEP5Fieldb + 87 0x81c9f79 _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table _triggers_list14trg_event_type + 137 0x81f39a6 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu m_duplicatesb + 3158 0x81a4b9f _Z21mysql_execute_commandP3THD + 25135 0x81a9113 _Z11mysql_parseP3THDPcj + 483 0x81a9d3d _Z16dispatch_command19enum_server_commandP3THDPcj + 2781 0x81ab594 handle_one_connection + 2340 0xb7f67463 _end + -1348795121 0xb7dccd64 _end + -1350476272 How-To-Repeat: Dunno, needs a high load. Fix: Dunno. Never had mysql problems before. Submitter-Id: submitter ID Originator: [EMAIL PROTECTED] Organization: Rellim MySQL support: none Synopsis: INSERT DELAYED crash Severity: serious Priority: medium Category: mysql Class: sw-bug Release:mysql-5.0.15 (Source distribution) C compiler:gcc (GCC) 3.4.4 C++ compiler: g++ (GCC) 3.4.4 Environment: System: Linux cms 2.6.13.2 #3 SMP Wed Sep 28 17:25:36 PDT 2005 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i686-pc-linux-gnu/3.4.4/specs Configured with: /mnt/www3/usr/local/src/gcc-3.4.4/configure --prefix=/usr --exec-prefix=/usr --enable-shared --disable-libgcj --disable-libf2c Thread model: posix gcc version 3.4.4 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 20 2003 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1435624 Mar 4 2003 /lib/libc-2.3.1.so -rw-r--r--1 root root 2425490 Mar 4 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 4 2003 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-openssl' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' -- -- Natalino Picone - [EMAIL PROTECTED] -- It's a horrible thing to be on top of the world and then to lose it and try to get it back. It's a whole lot harder the second time. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql hidden processes
On Friday 04 November 2005 08:06 am, Chris Wells wrote: /usr/lib/chkrootkit/chkproc -v -v PID 1230(/proc/1230): not in readdir output PID 1230: not in ps output CWD 1230: /var/lib/mysql EXE 1230: /usr/sbin/mysqld ... (report the same for 1231 - 1238) You have 9 process hidden for readdir command You have 9 process hidden for ps command The command `cat /proc/1230/cmdline` outputs: /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock Isnt this just nptl showing 1 process instead of 9 because it shared? Just add H to the ps command and you will see them. Jeff pgpOWtrPWgKxg.pgp Description: PGP signature
Re: MySQL 5.0 : error using max(idrow) on a null value
On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff pgpApFheY1YGC.pgp Description: PGP signature
Re: Mysql hidden processes
Jeff Smelser wrote: On Friday 04 November 2005 08:06 am, Chris Wells wrote: /usr/lib/chkrootkit/chkproc -v -v PID 1230(/proc/1230): not in readdir output PID 1230: not in ps output CWD 1230: /var/lib/mysql EXE 1230: /usr/sbin/mysqld ... (report the same for 1231 - 1238) You have 9 process hidden for readdir command You have 9 process hidden for ps command The command `cat /proc/1230/cmdline` outputs: /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock Isnt this just nptl showing 1 process instead of 9 because it shared? Just add H to the ps command and you will see them. Jeff That's pretty much what I figured, but I couldn't find anything specifically noting that. I just wanted to hear it confirmed from someone else before I completely wrote this off as a (sort of) false-positive from chkrootkit. And yes, as expected, `ps Haux` shows all of them. And `ps aux -L` shows everything with the parent thread listed. Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: MySQL 5.0 : error using max(idrow) on a null value
For a while my application should support both mysql 4 and 5 ( teh same copy on different sites of course ) so I should keep using a soluting wich should works well on both revision. I also have a couple of server with their database configured as master slave, so I don't trust to use autoincrement. When I can leave ther revision 4 at all I think to use the autoincrement using a trigger and mybe something like the oracle sequence . regards, Enzo Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -Messaggio originale- Da: Jeff Smelser [mailto:[EMAIL PROTECTED] Inviato: venerdì 4 novembre 2005 16.42 A: mysql@lists.mysql.com Oggetto: Re: MySQL 5.0 : error using max(idrow) on a null value On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fails to generate error message
I am just learning PHP/MySQL, so please forgive if this is a
ridiculously lame question.
I have developed a small web app to simply display information from the
database. The page will refresh itself every 60 seconds (plus the time
it takes to perform the refresh itself). The data in the db is updated
every 10 minutes. This arrangement runs fine until the page refresh
happens during the database update. When that happens, it will display
the 1st line for the html header !DOCTYPE HTML PUBLIC and that it! I
am dead in the water because the refresh meta-command in the html is not
loaded. Being new, I wrote this very close to the book I was following
and thought I had everything I need so PHP would generate an error
message. But all I get is !DOCTYPE HTML PUBLIC. Note the die()
function in the code below. Also the showerror() function is basically
a wrapper around a die() function
I would appreciate any help as to why I am not getting an error message
as well as any ideas as to the root problem. I suspect some type of db
locking issue.
Below is what I believe is the pertinent code. I am using EasyPHP 1-8
(for Windows) which is made up of :
Apache 1.3.33
PHP 4.3.10
MySQL 4.1.9
!DOCTYPE HTML PUBLIC
?php
require_once HTML/Template/IT.php;
require dbInb.inc;
// Connect to the MySql server
if (!($connection = @ mysql_connect($hostname, $username, $password)))
die(Cannot connect);
if (!(mysql_select_db($databaseName, $connection)))
showerror();
// Create a new template, and specify that the template files are
// in the subdirectory templates
$template = new HTML_Template_IT(./templates);
// Load the template file
$template-loadTemplatefile(Inbounds.tpl, true, true);
// Run the OVERDUE query on the connection
if (!($result = @ mysql_query(SELECT * FROM tblinbounds WHERE
batch_in_id = (SELECT batch_in_id FROM tblbatch_in ORDER BY batch_in_id
DESC LIMIT 1) AND ((del_date DATE_FORMAT(NOW(), '%m/%d/%y')) OR
(IsEmpty(del_date))) ORDER BY del_date, del_time, $connection)))
showerror();
while ($row = mysql_fetch_array($result))
{
// work with the record block
$template-setCurrentBlock(OVERDUE);
// Assign the row data to the template placeholders
$template-setVariable(VENDOR, $row[vendor]);
$template-setVariable(DESCRIPTION, $row[desc]);
$template-setVariable(CARRIER, $row[carrier]);
$template-setVariable(TRAILER, $row[trailer]);
$template-setVariable(DOCK, $row[dock]);
$template-setVariable(DELDATE, $row[del_date]);
$template-setVariable(DELTIME, $row[del_time]);
// Parse the current block
$template-parseCurrentBlock();
}
.
.
.
.
. (More blocks like the OVERDUE block above and that's it.)
**
** LEGAL DISCLAIMER **
**
This E-mail message and any attachments may contain
legally privileged, confidential or proprietary
information. If you are not the intended recipient(s),
or the employee or agent responsible for delivery of
this message to the intended recipient(s), you are
hereby notified that any dissemination, distribution
or copying of this E-mail message is strictly
prohibited. If you have received this message in
error, please immediately notify the sender and
delete this E-mail message from your computer.
mysql.user table is not updated to new password format ???
Dears sirs, please, how to correct this problems : *mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 051104 15:40:25 [Warning] Can't open and lock time zone table: La table ' mysql.time_zone_leap_second' n'existe pas trying to live without them C:\PROGRA~1\EASYPH~1\MySql\bin\mysqld.exe: Prêt pour des connections Source distribution* Thank you -- Sylvie Binet
Problems finding the MAX value
My organization runs an online auction on our intranet server for the United Way. I'm having a hard time printing out a list of the winning bidders (I'm under a lot of pressure; the baked goods are getting stale). In these examples, I've just printed out my bidder's first names, to protect their privacy. This output seems correct. I could just manually scan this for the maximum value for each item: mysql SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle, SUBSTRING_INDEX(u.name, , 1) AS fname, b.bid FROM PHPAUCTION_bids AS b, PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND b.auction=a.id ORDER BY FullTitle LIMIT 35; +--+---+-+ | FullTitle| fname | bid | +--+---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Lisa | 2. | | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| Anne | 5. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (30c1)| Anne | 10. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 3. | | 2 dozen chocolate chip cookies (a3aa)| Linda | 4. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 5. | | 2 dozen chocolate chip cookies (a3aa)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl | 2. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 3. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl | 5. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | Mandy | 2. | | 2 Gold Rings (d9c1) | Isabelle | 3. | | 2 Gold Rings (d9c1) | Hugh | 4. | | 2 Gold Rings (d9c1) | Isabelle | 5. | | 2 Gold Rings (d9c1) | Hugh | 6. | | 2 Gold Rings (d9c1) | Roslyn | 7. | | 2 Gold Rings (d9c1) | Hugh | 8. | | 2 Gold Rings (d9c1) | Roslyn | 10. | | 2 Gold Rings (d9c1) | Hugh | 12. | | 2 Gold Rings (d9c1) | Roslyn | 15. | | 2 Gold Rings (d9c1) | Hugh | 16. | | 2 Gold Rings (d9c1) | Linda | 20. | | 2 Gold Rings (d9c1) | Hugh | 21. | | 2 Gold Rings (d9c1) | t | 22. | | 2 Gold Rings (d9c1) | Hugh | 23. | | 2 Gold Rings (d9c1) | Linda | 25. | | 2 Gold Rings (d9c1) | t | 26. | | 2 piece outfit (purple and white) (d60d) | Mandy | 1. | | 2 piece outfit (purple and white) (d60d) | Susan | 3. | +--+---+-+ 35 rows in set (0.06 sec) mysql Note that there were actually three different batches of 2 dozen chocolate chip cookies. I printed the internal ID numbers to distinguish them. However, when I try to get fancy and print out just the winners, the winning amount comes out, but with the name of the FIRST, not the winning, bidder: mysql SELECT CONCAT(a.title, (, LEFT(b.auction,6), )) as FullTitle, SUBSTRING_INDEX(u.name, , 1) AS fname, MAX(b.bid) FROM PHPAUCTION_bids AS b, PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND b.auction=a.id GROUP BY FullTitle ORDER BY FullTitle LIMIT 5; ++++ | FullTitle
Are functions evaluated before or during insertion?
Hi everybody!
It seems like different instances of MySQL do things in different order, with
rather unfortunate effects on the last_insert_ID function.
Consider the following tables and insertions:
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
) engine=InnoDB;
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID),
key (parent_ID),
foreign key (parent_ID) references parent (ID)
) engine=InnoDB;
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'), (last_insert_ID(), 'b3');
The intention is of course that all three child rows should reference the
parent row that was inserted. This will work if the server first evaluates
all the calls to last_insert_ID and then starts inserting the rows. I have
one MySQL 4.0 server where this appears to work consistently.
On some MySQL 4.1 servers I sometimes get the error a foreign key constraint
fails. On one server it sometimes works and sometimes not. What I think
happens is that the server evaluates the first call to last_insert_ID and
inserts the first row (b1), and then starts processing the b2 row, evaluates
last_insert_ID again, and gets the ID of the b1 row.
I thought I had read somewhere in the documentation that functions are
evaluated before data is inserted, and as this never failed on my development
server I relied on it. Then it failed on the production server. Ouch!
So my questions are:
1: Is this expected behaviour? That is, should I expect some function calls to
be evaluated after some rows have already been inserted and auto_increment
counters have been incremented, or are functions supposed to be evaluated
before insertion starts?
2: Whichever order a query is processed in, wouldn't it be better to always do
things in the same order?
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with an SQL query
Figured out the query: select idx, vbs_id, date, weight from Weight, (select vbs_id as maxid, max(date) as maxdate from Weight group by vbs_id) as t where vbs_id = maxid and date = maxdate; It returns the proper weight and idx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select on passwords?
I was using MySQL 4.1 and have a Users table where I store the UserID
and the corresponding password using the following:
Insert into Users (UserID, Password) values ('someid', password(somepw));
and when people login, I would query using:
Select * from Users where UserID = 'someid' and Password = password(somepw);
and it would work nicely. However, I have just recently upgraded to
5.0.15 to take advantage of the Views and now I found that the above
Select statement does not return a user record for me anymore. Am I
doing something wrong here?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems finding the MAX value
KEVIN ZEMBOWER [EMAIL PROTECTED] wrote on 11/04/2005 11:05:05 AM: My organization runs an online auction on our intranet server for the United Way. I'm having a hard time printing out a list of the winning bidders (I'm under a lot of pressure; the baked goods are getting stale). In these examples, I've just printed out my bidder's first names, to protect their privacy. This output seems correct. I could just manually scan this for the maximum value for each item: mysql SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle, SUBSTRING_INDEX(u.name, , 1) AS fname, b.bid FROM PHPAUCTION_bids AS b, PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND b.auction=a.id ORDER BY FullTitle LIMIT 35; +-- +---+-+ | FullTitle| fname | bid | +-- +---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Lisa | 2. | | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| Anne | 5. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (30c1)| Anne | 10. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 3. | | 2 dozen chocolate chip cookies (a3aa)| Linda | 4. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 5. | | 2 dozen chocolate chip cookies (a3aa)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl| 2. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 3. | | 2 dozen chocolate chip cookies (d8e5)| Cheryl| 5. | | 2 dozen chocolate chip cookies (d8e5)| ucantoutbidme | 6. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | Mandy | 2. | | 2 Gold Rings (d9c1) | Isabelle | 3. | | 2 Gold Rings (d9c1) | Hugh | 4. | | 2 Gold Rings (d9c1) | Isabelle | 5. | | 2 Gold Rings (d9c1) | Hugh | 6. | | 2 Gold Rings (d9c1) | Roslyn| 7. | | 2 Gold Rings (d9c1) | Hugh | 8. | | 2 Gold Rings (d9c1) | Roslyn| 10. | | 2 Gold Rings (d9c1) | Hugh | 12. | | 2 Gold Rings (d9c1) | Roslyn| 15. | | 2 Gold Rings (d9c1) | Hugh | 16. | | 2 Gold Rings (d9c1) | Linda | 20. | | 2 Gold Rings (d9c1) | Hugh | 21. | | 2 Gold Rings (d9c1) | t | 22. | | 2 Gold Rings (d9c1) | Hugh | 23. | | 2 Gold Rings (d9c1) | Linda | 25. | | 2 Gold Rings (d9c1) | t | 26. | | 2 piece outfit (purple and white) (d60d) | Mandy | 1. | | 2 piece outfit (purple and white) (d60d) | Susan | 3. | +-- +---+-+ 35 rows in set (0.06 sec) mysql Note that there were actually three different batches of 2 dozen chocolate chip cookies. I printed the internal ID numbers to distinguish them. However, when I try to get fancy and print out just the winners, the winning amount comes out, but with the name of the FIRST, not the winning, bidder: mysql SELECT CONCAT(a.title, (, LEFT(b.auction,6), )) as FullTitle, SUBSTRING_INDEX(u.name, , 1) AS fname, MAX(b.bid) FROM PHPAUCTION_bids AS b, PHPAUCTION_users AS u, PHPAUCTION_auctions AS a WHERE b.bidder=u.id AND b.auction=a.id GROUP BY FullTitle
Re: Problems finding the MAX value
[EMAIL PROTECTED] wrote on 11/04/2005 11:22:35 AM: This is such a FAQ that they put the answer in the manual: http://dev.mysql.com/doc/refman/5.0/en/index.html OOPS! I copied the wrong link. It should have been: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html Sorry all! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to select on passwords?
Gobi wrote:
I was using MySQL 4.1 and have a Users table where I store the UserID
and the corresponding password using the following:
Insert into Users (UserID, Password) values ('someid', password(somepw));
and when people login, I would query using:
Select * from Users where UserID = 'someid' and Password =
password(somepw);
and it would work nicely. However, I have just recently upgraded to
5.0.15 to take advantage of the Views and now I found that the above
Select statement does not return a user record for me anymore. Am I
doing something wrong here?
I realized what my error is. Password() in 5.0 has been upgraded to
increase security so I needed to lengthen my password field to include
the extra bits. But I am using MD5 now as recommended by MySQL
documentation.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.user table is not updated to new password format ???
Sylvie, *mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 051104 15:40:25 [Warning] Can't open and lock time zone table: La table ' mysql.time_zone_leap_second' n'existe pas trying to live without them C:\PROGRA~1\EASYPH~1\MySql\bin\mysqld.exe: Prt pour des connections Source distribution* Take the message literally. Run mysql_fix_privilege_tables.sql. See http://dev.mysql.com/doc/refman/5.0/en/mysql-fix-privilege-tables.html PB - Sylvie Binet wrote: Dears sirs, please, how to correct this problems : *mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run 051104 15:40:25 [Warning] Can't open and lock time zone table: La table ' mysql.time_zone_leap_second' n'existe pas trying to live without them C:\PROGRA~1\EASYPH~1\MySql\bin\mysqld.exe: Prt pour des connections Source distribution* Thank you -- Sylvie Binet No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
Marc, I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... .NET/MySQL will be a _lot_ cheaper than .NET/MSSQL. Visual Studio will save you a lot of time, but even that is a cost consideration, there is a free .NET GUI too. If you go Windows and .NET, I'd strongly encourage you to use Connector/NET rather than Connector/ODBC. Assuming you are using such a direct connector rather than ODBC, there are small MySQL-MSSQL differences in how you work with the database in the .NET GUI, but these differences will not slow you down appreciably. If OS is a major issue for you, it's even possible now to develop in .NET/MySQL on Linux, but a main point of .NET is to insulate you from the OS, and IAC Windows is not hard to get comfortable with, even if you hate it as many do. PB - Marc Pidoux wrote: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... Thanks, Marc -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems finding the MAX value
Man, you're awesome. Only two typos in the whole procedure. However, for the life of me, I'm puzzled over how it works. If you have more patience can you explain? Correct query (table is singular, not PHPAUCTIONS_...): CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTION_bids GROUP BY auction; The temporary table tmpWinners doesn't even contain a field for the ID of the winning bidder. This is the first puzzling point and significant diference between our two solutions. Correct query (changed line 8 from AND b.bit...): SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bid = w.winningbid INNER JOIN PHPAUCTION_users AS u ON b.bidder=u.id INNER JOIN PHPAUCTION_auctions AS a ON b.auction=a.id ORDER BY FullTitle LIMIT 5; - \g +--+---+-+ | FullTitle| fname | bid | +--+---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | t | 26. | +--+---+-+ 5 rows in set (0.15 sec) mysql So, the SELECT query goes through the tmpWinniing table and, for each record, finds a record in the bids table that has the same item ID and same bid price. It then uses the bidder's ID it found to look up the bidder's name, and the auction item's ID to look up the item's title. It seems to me that the significant difference between our solutions is your use of two conditional clauses in the INNER JOIN between tmpWinner and PHPAUCTION_bids. I don't think I've ever seen a join done on more than one field between two tables before. Would this query still work if more than one person bid the same amount on the same item? The business rules built into phpAuction prevent this, but in a more generic situation, would this query still work correctly? I guess one anwer to this is 'yes,' because the MAX() function returns the first of two equal maximum values it finds, doesn't it? Despite my puzzlement at how you were able to come up with such a great solution, I'm very grateful for your help and explanations. If you're ever in Baltimore, MD, I owe you a beer. Thanks. -Kevin [EMAIL PROTECTED] 11/04/05 11:22AM This is such a FAQ that they put the answer in the manual: http://dev.mysql.com/doc/refman/5.0/en/index.html What you are looking for is the row that contains the maximum bid for each itemname. The easiest first step it to actually determine what the highest bid for each item actually is then use that information to build the rest of what you wanted. The most portable and easiest to write solution to this is the two-table method ( I think I divined your column names correctly, maybe not...): # begin example # CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTIONS_bids GROUP BY auction; SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bit = w.winningbid INNER JOIN PHPAUCTION_users AS u ON b.bidder=u.id INNER JOIN PHPAUCTION_auctions AS a ON b.auction=a.id ORDER BY FullTitle LIMIT 35; DROP TEMPORARY TABLE tmpWinners; # end example # Does that make sense? You should be able to expand on that pattern to build whatever list you want. I showed the full-chain of how each table relates to another but you could have simplified the query above, can you see where? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Another INSERT DELAYED crash
Description: INSERT DELAYED crashes my new mysqld. Here is the error log: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 max_used_connections=2 max_connections=200 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 144382 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x89a6ad0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbdffe178, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81876bc 0xb7e92dfd 0x81043b9 0x81c9f79 0x81f39a6 0x81a4b9f 0x81a9113 0x81a9d3d 0x81ab594 0xb7e8b463 0xb7cf0d64 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x896c8a8 = INSERT DELAYED INTO search SET summary_id='1124898819.8519_9251', zip='', zipdist='0', city='', county='', state='', total='0', last_name='', cost='0', insurance='', duration='0', age_spec='0', religion='0', ethnicity='0', modality='0', therapist_gender='0',thrpst_type_id='0', language='0', sexual_orient='0', pttotal='0', issue='0', condn='0', partner_id='0' thd-thread_id=4 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Here is the decoded stack dump: 0x81876bc handle_segfault + 668 0xb7e92dfd _end + -1349665111 0x81043b9 _ZN11Item_string13save_in_fieldEP5Fieldb + 57 0x81c9f79 _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table _triggers_list14trg_event_type + 137 0x81f39a6 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu m_duplicatesb + 3158 0x81a4b9f _Z21mysql_execute_commandP3THD + 25135 0x81a9113 _Z11mysql_parseP3THDPcj + 483 0x81a9d3d _Z16dispatch_command19enum_server_commandP3THDPcj + 2781 0x81ab594 handle_one_connection + 2340 0xb7e8b463 _end + -1349696241 0xb7cf0d64 _end + -1351377392 I have aplied this patch: /home/mydev/mysql-5.0-bug13707, it fixes my last reported INSERT DELAYED, but not this one. How-To-Repeat: Not exactly sure, but removeing the DELAYED in the above makes it work fine. Fix: Dunno. Submitter-Id: submitter ID Originator:[EMAIL PROTECTED] Organization: Rellim MySQL support: none Synopsis: ANother INSERT DELAYED bug Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-5.0.15 (Source distribution) C compiler:gcc (GCC) 3.4.4 C++ compiler: g++ (GCC) 3.4.4 Environment: System: Linux cms 2.6.13.2 #3 SMP Wed Sep 28 17:25:36 PDT 2005 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i686-pc-linux-gnu/3.4.4/specs Configured with: /mnt/www3/usr/local/src/gcc-3.4.4/configure --prefix=/usr --exec-prefix=/usr --enable-shared --disable-libgcj --disable-libf2c Thread model: posix gcc version 3.4.4 Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Sep 20 2003 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1435624 Mar 4 2003 /lib/libc-2.3.1.so -rw-r--r--1 root root 2425490 Mar 4 2003 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 4 2003 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-openssl' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP/MySQL vs ASP/MSSQL?
I have used both and I prefer php/mysql :-) On 11/4/05, Peter Brawley [EMAIL PROTECTED] wrote: Marc, I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... .NET/MySQL will be a _lot_ cheaper than .NET/MSSQL. Visual Studio will save you a lot of time, but even that is a cost consideration, there is a free .NET GUI too. If you go Windows and .NET, I'd strongly encourage you to use Connector/NET rather than Connector/ODBC. Assuming you are using such a direct connector rather than ODBC, there are small MySQL-MSSQL differences in how you work with the database in the .NET GUI, but these differences will not slow you down appreciably. If OS is a major issue for you, it's even possible now to develop in .NET/MySQL on Linux, but a main point of .NET is to insulate you from the OS, and IAC Windows is not hard to get comfortable with, even if you hate it as many do. PB - Marc Pidoux wrote: I've been using ASP for years but I have to work on a bigger project involving many users and data so of course, Access is not an option anymore. Since it's a project requiring thousand of files and several applications all linked together, I can't create it once and change it later. Basically, which option is the best between PHP/MySQL and ASP/MSSQL? It's not a giant project but it might include around 20'000 members interacting daily through forums, blogs, messages etc... Thanks, Marc -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- 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: PHP/MySQL vs ASP/MSSQL?
Me, too. One thing that's a plus for my clients is to tell them that is will cost them less if I develop in php/mysql as opposed to ASP/MSSQL. Hosting is cheaper and I can do things in PHP that takes 5 lines as opposed to ASP/MSSQL which takes 20. heh Jenifer - Original Message - I have used both and I prefer php/mysql :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: R: MySQL 5.0 : error using max(idrow) on a null value
select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; Does changing max(c.idrow)+1 to coalesce(max(c.idrow),0)+1 solve your problem? --Pete On Fri, Nov 04, 2005 at 04:56:26PM +0100, AESYS S.p.A. [Enzo Arlati] wrote: For a while my application should support both mysql 4 and 5 ( teh same copy on different sites of course ) so I should keep using a soluting wich should works well on both revision. I also have a couple of server with their database configured as master slave, so I don't trust to use autoincrement. When I can leave ther revision 4 at all I think to use the autoincrement using a trigger and mybe something like the oracle sequence . regards, Enzo Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -Messaggio originale- Da: Jeff Smelser [mailto:[EMAIL PROTECTED] Inviato: venerd? 4 novembre 2005 16.42 A: mysql@lists.mysql.com Oggetto: Re: MySQL 5.0 : error using max(idrow) on a null value On Friday 04 November 2005 07:30 am, AESYS S.p.A. [Enzo Arlati] wrote: Hi, I'm trying to migrate from MySQL 4.1.11 to MySQL 5.0 and I get a problem with the new release. I have this table... provasql CREATE TABLE `provasql` `idrow` bigint(20) unsigned NOT NULL default '0', `descr` varchar(50) default NULL, PRIMARY KEY (`idrow`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ...this is the SQL command... insert into provasql ( idrow, descr ) select ( select max(c.idrow)+1 from provasql c ) , 'This is only a test'; ...and this is the error: ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL colum n 'idrow' at row 1 With MySQL 4.1.11 I'd never get this error message, but it happens with the 5.0.15 version. Can anyone help me? Mysql 5 is much more picky on things you shouldnt have been able to do in the first place.. Just change idrow to auto_increment and stop doing max.. Its not needed. innodb properly handles auto_increment now. Jeff -- 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: Problems finding the MAX value
How it works... KEVIN ZEMBOWER [EMAIL PROTECTED] wrote on 11/04/2005 12:40:50 PM: Man, you're awesome. Only two typos in the whole procedure. However, for the life of me, I'm puzzled over how it works. If you have more patience can you explain? Correct query (table is singular, not PHPAUCTIONS_...): CREATE TEMPORARY TABLE tmpWinners SELECT auction, max(bid) as winningbid FROM PHPAUCTION_bids GROUP BY auction; The temporary table tmpWinners doesn't even contain a field for the ID of the winning bidder. This is the first puzzling point and significant diference between our two solutions. The CREATE TABLE command can make tables out of query results. No need to define the columns as they will be auto-typed to fit the results. In this case I chose to create a TEMPORARY table instead of a static table. http://dev.mysql.com/doc/refman/4.1/en/create-table.html Correct query (changed line 8 from AND b.bit...): SELECT CONCAT(a.title, (, LEFT(b.auction,4), )) AS FullTitle , SUBSTRING_INDEX(u.name, , 1) AS fname , b.bid FROMtmpWinners AS w INNER JOIN PHPAUCTION_bids AS b ON b.auction = w.auction AND b.bid = w.winningbid INNER JOIN PHPAUCTION_users AS u ON b.bidder=u.id INNER JOIN PHPAUCTION_auctions AS a ON b.auction=a.id ORDER BY FullTitle LIMIT 5; - \g +-- +---+-+ | FullTitle| fname | bid | +-- +---+-+ | 101 Dalmations, Oliver Company, A Goofy Movie - VHS (d7b4) | Saori | 3. | | 2 dozen chocolate chip cookies (30c1)| ucantoutbidme | 11. | | 2 dozen chocolate chip cookies (a3aa)| Donna | 7. | | 2 dozen chocolate chip cookies (d8e5)| Donna | 8. | | 2 Gold Rings (d9c1) | t | 26. | +-- +---+-+ 5 rows in set (0.15 sec) mysql So, the SELECT query goes through the tmpWinniing table and, for each record, finds a record in the bids table that has the same item ID and same bid price. It then uses the bidder's ID it found to look up the bidder's name, and the auction item's ID to look up the item's title. exactly. It seems to me that the significant difference between our solutions is your use of two conditional clauses in the INNER JOIN between tmpWinner and PHPAUCTION_bids. I don't think I've ever seen a join done on more than one field between two tables before. Would this query still work if more than one person bid the same amount on the same item? The business rules built into phpAuction prevent this, but in a more generic situation, would this query still work correctly? I guess one anwer to this is 'yes,' because the MAX() function returns the first of two equal maximum values it finds, doesn't it? You have surmised correctly. If more than one row matches your maximum condition (as determined by the rows in tmpWinners) then all matching rows will be returned in the final query. Despite my puzzlement at how you were able to come up with such a great solution, I'm very grateful for your help and explanations. If you're ever in Baltimore, MD, I owe you a beer. Thanks. -Kevin SQL is a language that tends towards patterns. Learn the patterns and you get better at SQL. This happens to be a very well-known pattern so I can't take any of the credit as it was around long before I started programming. I am just glad I could share. Do I still get that beer? ;-) About multiple terms in the ON phrases of the JOIN clauses... It is sometimes to your advantage to move terms out of the WHERE clause and into the ON clause (especially when it comes to the LEFT and RIGHT joins). Not only can it make the logic of your query correct but sometimes it opens the optimizer up to using indexes it may not have considered before. Almost any logical statement is permissible in an ON clause. See http://dev.mysql.com/doc/refman/5.0/en/join.html for more details on what will and won't work based on the order your tables are JOINed into the query. Sorry about the typos. Glad they didn't mess you up :-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Fw: Help with an SQL query
Oops, I meant to post this on the list AND copy the person asking the question Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Gobi [EMAIL PROTECTED] Sent: Friday, November 04, 2005 1:46 PM Subject: Re: Help with an SQL query I can't test this in MySQL- I'm using an older version of MySQL that doesn't support subqueries - but it works in DB2 and it should do the trick for getting the current weight of each VBS_id value: select VBS_id, date, weight from VBS_table x where date = (select max(date) from VBS_table where VBS_id = x.VBS_id); I'm assuming you are using a more version of MySQL which DOES support subqueries! If not, you may be able to get the same result with temp tables but I haven't tried that. Make sure to say something if you don't know how to use temp tables to simulate subqueries. I'm dubious that this query can be simulated with temp tables though (Rant: I REALLY wish people (all people, not just you) posting questions to this mailing list would get in the habit of specifying which version of MySQL they are using!! The answers to questions FREQUENTLY depend on the MySQL version so it would really help reduce the size of answers if people volunteered this information in the original question.) Also, I'm assuming that that MySQL supports correlated subqueries; I'm really not sure so I'll let you try the actual query and see if it works for you. Here's an explanation of how this query works, in case you've never seen one like this before. This is called a correlated subquery; the key thing that makes it obvious that this is a correlated subquery (in case you have to recognize one) is that a correlation name, in this case 'x', appears in the FROM clause of the outer query and again in the WHERE clause of the subquery. The subquery appears in brackets in case you are not familiar with subqueries. A correlated subquery works backwards to a normal subquery. In a normal subquery, the subquery is executed first and is only executed once: its result is plugged into the outer query which then executes just once as well. In a correlated subquery, both the outer query and the subquery are executed _repeatedly_, possibly MANY times each. The outer query is executed in order to obtain one row, THEN the subquery is executed to see if the row found by the outer query can be kept. In this case, let's say that the outer query returned the first row of the table, which has a VBS_id of 11 and a date of '10/3/2005': the subquery determines the maximum (most recent) date for any row that has same VBS_id as the one just found by the outer query; if the maximum date differs from the date found by the outer query, the outer query row is discarded and does not appear in the final result set. In this case, the maximum date for VBS_ID is 10/8/2005 which is not equal to the value found by the outer query, so that row is discarded. Having dealt with the first row of the outer query, the outer query executes again and gets another row. Again, the subquery is executed to see if the date is the same as maximum date for that VBS_id and again, the outer row is only kept if its date matches the maximum date found by the subquery. And so it goes, one row at a time, until the outer query has read every row of the table; a single row of the outer query is obtained, then the subquery determines if that row contains the maximum date for the VBS_id that was just found by the outer query. The final result will contain only rows that have the maximum dates for each VBS_id and will show the VBS_id, the maximum date, and the weight at that date. That is the result you requested. Bear in mind that a correlated query can perform much more poorly than a regular subquery, although the optimizer sometimes has tricks that make the performance quite tolerable. There may be a solution that doesn't involve a correlated subquery but it's not coming to me. However, others who are better at SQL may think of something that does the job. Rhino - Original Message - From: Gobi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 04, 2005 3:05 AM Subject: Help with an SQL query Not sure if this is the right place to ask. I have a table, Weight, with the following test data: idx VBS_ID DateWeight 11110/3/200511.5 2119/5/2004 10 31110/7/200511.51 41110/8/200511.52 51210/8/200510.5 61210/1/200510.3 7129/28/200510 What I would like to do is to get the most recent weight for each unique VBS_ID. So, in this case, I want a result of: 11, 10/8/2005, 11.52 12, 10/8/2005, 10.5 Can anyone tell me how to do this? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Problem with load data and NULL
All, I need help with using load data to load a text file that is created by another application. I have a text file that contains 2 columns, date and smallint and 2 rows: d,e 2005-12-31, ,2 In the above example the first row, column e should be NULL and the second row column d should be NULL. The application that outputs this text files does not output a \N for null it leave the data empty. So when I load the data into MySQL, even though I have created the table as: CREATE TABLE `a` (`d` date default NULL,`e` smallint(6) default NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 The Load Data commands inserts a 0 for my values so the table looks like: ++--+ | d | e| ++--+ | 2005-12-31 |0 | | -00-00 |2 | ++--+ What can I say on the LOAD DATA command so the data is loaded as NULL and not 0? I'm on windows and I've used the 4.1 and 5.0 clients to get this to work. Thanks for your help. Barbara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table
name or alias?
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After doing
some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on a
table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something else
going on?
mysql EXPLAIN
- SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
++-+++--+-+-+-+---+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+++--+-+-+-+---+-+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL |
NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY |
6 | t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY |
6 | NULL | 23508 | Using index |
++-+++--+-+-+-+---+-+
3 rows in set (0.01 sec)
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9
MySQL 4.0.26 wich package
Hi Will be installing MySQL 4.0.26 in order to be compatible with my Internet Service Provider (my internet connection)who has MySQL 4.0.24. On MySQL site there is two version of 4.0.26, they are: Linux (x86, glibc-2.3, dynamic, gcc) Linux (x86, glibc-2.2, static (Standard only), gcc) Available in Standard, Max and Debug. Which package should I install ? Thank you for answering my question Excuse my english Serge Goyette -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Selects, Alias Names and stored procedures
I don't have any idea about your second question but I have a thought on the
first one.
In DB2, which I use most of the time, you don't need to qualify a column
name like list_ID or acct_ID unless it is ambiguous. In this case, neither
one is ambiguous because both get used in single-table SELECT statements. If
I did those same queries in DB2, I would only expect an error message if I
did a join of two tables in which both tables had an acct_ID or list_ID;
then the SQL processor would get confused if the duplicated column names
weren't qualified in a SELECT list, GROUP BY, WHERE, or whatever.
Since DB2 and MySQL are presumably written to the same SQL standard, they
probably have the same rules; you only have to qualify a column name when it
is ambiguous.
That's my guess and I'm sticking to it until I hear otherwise from someone
more familiar with MySQL's implementation of SQL :-)
Rhino
- Original Message -
From: Gordon Bruce [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, November 04, 2005 2:51 PM
Subject: Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table name or
alias?
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After
doing some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on
a table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something
else going on?
mysql EXPLAIN
- SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
++-+++--+-+-
+-+---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+++--+-+-
+-+---+-+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY | 6 |
t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY | 6 | NULL | 23508 | Using
index |
++-+++--+-+-
+-+---+-+
3 rows in set (0.01 sec)
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
How to rename a database
I need to rename a database. I read somewhere that I could simply stop the MySQL database server and rename the folder that contains the data, re-start MySQL, and I'm good-to-go. However, I tried this, and while it appears to have worked initially, when I try to access one of the tables in the database, I get the error, fccamp.activities doesn't exist. When I rename the folder back to fccamp_dbo, it works just fine. So, how do I rename a database correctly? I'm using MySQL 5.0 in a Windows XP environment. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure Conversion
I am converting a MS SQL Server ASP application over to use MySQL. I have two simple stored procedures that I need to convert. I have very little experience with MS SQL stored procedures, and none-what-so-ever with stored procedures in MySQL, so I really don't know what this should look like. I'll post the first one, and if I'm able to figure it out, I'll attempt my second one by myself. Here's the stored procedure converted as much as I can get it. CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30) ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Statement Conversion
I'm converting an application from SQL Server to MySQL. I have the following query that I need to convert: SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM Activities a ORDER BY Activity What is the proper syntax for this in MySQL? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Statement Conversion
If your MySQL server is a *nix system than table names are case sensitive. SELECT A.*, CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS CO FROM Activities A ORDER BY Activity I also just noticed, remove the CO = and add AS CO following the END of the case statement. -Original Message- From: Jesse Castleberry [mailto:[EMAIL PROTECTED] Sent: Friday, November 04, 2005 3:54 PM To: MySQL List Subject: SQL Statement Conversion I'm converting an application from SQL Server to MySQL. I have the following query that I need to convert: SELECT A.*, CO = CASE CounselorOnly WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END FROM Activities a ORDER BY Activity What is the proper syntax for this in MySQL? Thanks, Jesse -- 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 rename a database
Jesse, I need to rename a database. I read somewhere that I could simply stop the MySQL database server and rename the folder that contains the data, re-start MySQL, and I'm good-to-go. However, I tried this, and while it appears to have worked initially, when I try to access one of the tables in the database, I get the error, fccamp.activities doesn't exist. When I rename the folder back to fccamp_dbo, it works just fine. It's more complicated than just renaming the directory. Here is what we wrote for it: CREATE PROCEDURE RenameDatabase( IN oldname CHAR (64), IN newname CHAR(64) ) BEGIN DECLARE sname CHAR(64) DEFAULT NULL; DECLARE rows INT DEFAULT 1; DECLARE total INT DEFAULT 0; CREATE DATABASE IF NOT EXISTS newname; REPEAT SELECT table_name INTO sname FROM information_schema.tables AS t WHERE t.table_schema = oldname LIMIT 1; SET rows = FOUND_ROWS(); IF rows = 1 THEN SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname, ' TO ', newname, '.', sname ); PREPARE cmd FROM @scmd; EXECUTE cmd; DEALLOCATE PREPARE cmd; SET total = total + 1; END IF; UNTIL rows = 0 END REPEAT; IF total 0 THEN SET @scmd = CONCAT( UPDATE mysql.db SET Db = ', newname, ' WHERE Db = ', oldname, ' ); PREPARE cmd FROM @scmd; EXECUTE cmd; SET @scmd = CONCAT( UPDATE mysql.columns_priv SET Db = ', newname, ' WHERE Db = ', oldname, ' ); PREPARE cmd FROM @scmd; EXECUTE cmd; DEALLOCATE PREPARE cmd; FLUSH PRIVILEGES; END IF; END; PB http://www.artfulsoftware.com - Jesse Castleberry wrote: I need to rename a database. I read somewhere that I could simply stop the MySQL database server and rename the folder that contains the data, re-start MySQL, and I'm good-to-go. However, I tried this, and while it appears to have worked initially, when I try to access one of the tables in the database, I get the error, fccamp.activities doesn't exist. When I rename the folder back to fccamp_dbo, it works just fine. So, how do I rename a database correctly? I'm using MySQL 5.0 in a Windows XP environment. Thanks, Jesse -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Heikki: What will become of InnoDb once MySQL license runs out in 2006???
Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. If yes, will you still be supporting it or will it be up to MySQL AB? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Conversion
Jesse, VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. A stored procedure cannot return a value. PB - Jesse Castleberry wrote: I am converting a MS SQL Server ASP application over to use MySQL. I have two simple stored procedures that I need to convert. I have very little experience with MS SQL stored procedures, and none-what-so-ever with stored procedures in MySQL, so I really don't know what this should look like. I'll post the first one, and if I'm able to figure it out, I'll attempt my second one by myself. Here's the stored procedure converted as much as I can get it. CREATE Procedure sp_InsertNewCamper ( in cFirstName NVarChar(30), in cLastName NVarChar(30), in cUserName NVarChar(30), in cPassword NVarChar(30) ) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword) // error on this line. return LAST_INSERT_ID() /*@@Identity*/ END When I execute this, It bombs on the indicated line stating that there is a syntax error. Thanks, Jesse -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RES: RES: Delivery by Demand
OK, Shawn, nice tip. I really didn't know if it was actually performed by server or by client. But I'll study the MySQL client protocols. But it is still strange and needs investigation, because as I remember, I've submit a prove fire to the oracle server. The prove was: I've sent a really really really heavy query, containing several tables cartesian joins, resulting in too many data (billions of billions of records), and we've imaginate it could spend at least many hours. For my surprising, the server reponse was imediate, with a only first page. Do you think that the server continues the actual processing until the end, despite no more pages being requested anymore? My Regards FM -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 4 de novembro de 2005 02:06 Para: Fabricio Mota Cc: mysql@lists.mysql.com Assunto: Re: RES: Delivery by Demand Yes, it is a client-side behavior to the extent that the MySQL server does not page through data. It gets the complete results unless you ask for a LIMIT, then it stops building results after it meets the criteria of your LIMIT. I really do not know much about Oracle administration and communication protocols so I am just guessing.I believe that even your Oracle clients had to ask for data in pages instead of the full set. Are you sure your Oracle server was really holding those results for you and only delivering batches of 100 records? That seems very much like a client-side behavior that was just hard for you to notice. It could have been designed as part of the client library As I said, I just don't know but I know others on the list have had some extensive Oracle experience. Maybe one of them can weigh in on this With MySQL, the behavior you want to emulate is definitely something you control from the client-end either by using the LIMIT clause or by pulling down single rows in batches. You have to remember, though, that while the client is processing it's latest batch of rows that the server still holds onto a complete result set and has to maintain an open connection to your client. It's really in the best interest of performance for your client to spend as little time getting the data from the server. That means that you should only write queries that ask for the data you actually need and you should get the data out of the server as soon as possible. That way the server has more resources available to deal with other queries. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Fabricio Mota [EMAIL PROTECTED] wrote on 11/03/2005 10:52:34 PM: Shawn, So are you telling me that it's a configuration in Client, but not in Server? FM -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 2 de novembro de 2005 17:37 Para: Fabricio Mota Cc: mysql@lists.mysql.com Assunto: Re: Delivery by Demand Fabricio Mota [EMAIL PROTECTED] wrote on 11/02/2005 10:23:46 AM: Hi all, In the past, I worked as a Oracle user. I've noted that in oracle (or maybe in that configuration), when we request a great amount of data, such like: select * from million_records_table It does not delivers the entire table at the first moment. It delivers a little amount of data - such like a single page containing about 100 records - and awaits the cursor request the Record No 101 for fetch the next set of data. In MySQL - at least, with the default configuration I have used - it does not happens. It delivers all the million of records existent in the table, inconditionally. I know that there is the clause LIMIT N, to limit the first N records existing in the query, but is there a way to warrant a delivery by demand, such Oracle does, without to have to alter the application's SQL code? Thank you I know that in the C-API (and others) there are two commands to retrieve records from the server. One is mysql_store_result() which will bring all of your results into your machine in a single pull. The second is mysql_use_result(). That command sets up a transfer process of pulling the rows from the server one at a time. If you need 100 rows of data, you issue 100 mysql_fetch_row() commands. You are responsible for storing the records for later re-use. Is that the flexibility you are looking for? http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: RES: RES: Delivery by Demand
At 23:06 -0300 11/4/05, Fabricio Mota wrote: OK, Shawn, nice tip. I really didn't know if it was actually performed by server or by client. But I'll study the MySQL client protocols. But it is still strange and needs investigation, because as I remember, I've submit a prove fire to the oracle server. The prove was: I've sent a really really really heavy query, containing several tables cartesian joins, resulting in too many data (billions of billions of records), and we've imaginate it could spend at least many hours. For my surprising, the server reponse was imediate, with a only first page. Do you think that the server continues the actual processing until the end, despite no more pages being requested anymore? With respect to the original question, you might want to consider using a prepared statement and a server-side cursor. You can set an attribute of the cursor that indicates how many rows at a time a fetch operation should grab from the server. http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-attr-set.html My Regards FM -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 4 de novembro de 2005 02:06 Para: Fabricio Mota Cc: mysql@lists.mysql.com Assunto: Re: RES: Delivery by Demand Yes, it is a client-side behavior to the extent that the MySQL server does not page through data. It gets the complete results unless you ask for a LIMIT, then it stops building results after it meets the criteria of your LIMIT. I really do not know much about Oracle administration and communication protocols so I am just guessing.I believe that even your Oracle clients had to ask for data in pages instead of the full set. Are you sure your Oracle server was really holding those results for you and only delivering batches of 100 records? That seems very much like a client-side behavior that was just hard for you to notice. It could have been designed as part of the client library As I said, I just don't know but I know others on the list have had some extensive Oracle experience. Maybe one of them can weigh in on this With MySQL, the behavior you want to emulate is definitely something you control from the client-end either by using the LIMIT clause or by pulling down single rows in batches. You have to remember, though, that while the client is processing it's latest batch of rows that the server still holds onto a complete result set and has to maintain an open connection to your client. It's really in the best interest of performance for your client to spend as little time getting the data from the server. That means that you should only write queries that ask for the data you actually need and you should get the data out of the server as soon as possible. That way the server has more resources available to deal with other queries. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Fabricio Mota [EMAIL PROTECTED] wrote on 11/03/2005 10:52:34 PM: Shawn, So are you telling me that it's a configuration in Client, but not in Server? FM -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 2 de novembro de 2005 17:37 Para: Fabricio Mota Cc: mysql@lists.mysql.com Assunto: Re: Delivery by Demand Fabricio Mota [EMAIL PROTECTED] wrote on 11/02/2005 10:23:46 AM: Hi all, In the past, I worked as a Oracle user. I've noted that in oracle (or maybe in that configuration), when we request a great amount of data, such like: select * from million_records_table It does not delivers the entire table at the first moment. It delivers a little amount of data - such like a single page containing about 100 records - and awaits the cursor request the Record No 101 for fetch the next set of data. In MySQL - at least, with the default configuration I have used - it does not happens. It delivers all the million of records existent in the table, inconditionally. I know that there is the clause LIMIT N, to limit the first N records existing in the query, but is there a way to warrant a delivery by demand, such Oracle does, without to have to alter the application's SQL code? Thank you I know that in the C-API (and others) there are two commands to retrieve records from the server. One is mysql_store_result() which will bring all of your results into your machine in a single pull. The second is mysql_use_result(). That command sets up a transfer process of pulling the rows from the server one at a time. If you need 100 rows of data, you issue 100 mysql_fetch_row() commands. You are responsible for storing the records for later re-use. Is that the flexibility you are looking for?
Re: Heikki: What will become of InnoDb once MySQL license runs out
Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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: Heikki: What will become of InnoDb once MySQL license runs out
Last one people: I just realized that Heikki is monitoring our post pertaining to innodb. This guy/gal is an oracle employee. The enemy is amongst us. Beware. Haha Haha Ezra On 11/4/05, Ezra Taylor [EMAIL PROTECTED] wrote: To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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: Heikki: What will become of InnoDb once MySQL license runs out
There is always Postgres if you're that paranoid ;-) --Harry Ezra Taylor wrote: Last one people: I just realized that Heikki is monitoring our post pertaining to innodb. This guy/gal is an oracle employee. The enemy is amongst us. Beware. Haha Haha Ezra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RES: Heikki: What will become of InnoDb once MySQL license runs out
There are several opinions... I would like to forecast the real intentions of Oracle... FM -Mensagem original- De: Ezra Taylor [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 4 de novembro de 2005 23:46 Cc: mysql@lists.mysql.com Assunto: Re: Heikki: What will become of InnoDb once MySQL license runs out To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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]
Add New User
When I log in as root using: mysql -u root -p mysql I get the mysql prompt: mysql I then issue this command or statement: mysqlGRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY 'some_password' WITH GRANT OPTION; I get the following returned: Query OK, 0 rows affected (0.00 sec) Why is this not working? To test it further I try to log in as testUser but it tells me access denied for user [EMAIL PROTECTED] Even if I grant the testUser @ localhost. I can look in the user table and sure enough user [EMAIL PROTECTED] is there. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
At 21:49 -0500 11/4/05, Ezra Taylor wrote: Last one people: I just realized that Heikki is monitoring our post pertaining to innodb. This guy/gal is an oracle employee. The enemy is amongst us. Beware. Haha Haha Ezra Ezra, Your basis for claiming that Heikki is the enemy is ... what? On 11/4/05, Ezra Taylor [EMAIL PROTECTED] wrote: To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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] -- 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]
Help optimize this simple find
This simple find is taking 4 to 7 seconds. Way too long!! (This is a geotargeting query using the database from IP2location.) select lat,lon from geocodes where ipFROM=1173020467 and ipTO=1173020467 The database looks like this (how IP2location recommends): CREATE TABLE `geocodes` ( `ipFROM` int(10) unsigned zerofill NOT NULL default '00', `ipTO` int(10) unsigned zerofill NOT NULL default '00', `lat` double default NULL, `lon` double default NULL, PRIMARY KEY (`ipFROM`,`ipTO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; And there are 1.7 million records. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Heikki: What will become of InnoDb once MySQL license runs out
Relax Paul: If you noticed it put the words Haha Haha at the end of my statement. Anyway, as I said, Oracle is out to make money. They will crush anyone that gets in there way. Ezra On 11/4/05, Paul DuBois [EMAIL PROTECTED] wrote: At 21:49 -0500 11/4/05, Ezra Taylor wrote: Last one people: I just realized that Heikki is monitoring our post pertaining to innodb. This guy/gal is an oracle employee. The enemy is amongst us. Beware. Haha Haha Ezra Ezra, Your basis for claiming that Heikki is the enemy is ... what? On 11/4/05, Ezra Taylor [EMAIL PROTECTED] wrote: To Mysql users: Just to remind you all, Oracle is a business that expects to make money. As you all know, Mysql is a threat to the fat cats such as Oracle,DB2,MSSql and others. If you think Oracle is going to play fair with us then you will believe that crack will one day be a multi vitamin. For those you that don't know what crack is, it's a drug that will fuck your life up. Ezra Taylor On 11/4/05, Heikki Tuuri [EMAIL PROTECTED] wrote: Mike, - Original Message - From: mos [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Saturday, November 05, 2005 12:11 AM Subject: Heikki: What will become of InnoDb once MySQL license runs out Heikki, I am about to start a large MySQL project that requires transactions and I need to know if InnoDb will be around for MySQL after MySQL's license for InnoDb runs out in 2006. the current GPL version of MySQL/InnoDB will of course be available then by the very nature of the GPL license. The MySQL AB - Innobase Oy OEM agreement is about commercial non-GPL MySQL/InnoDB licenses. About that agreement I want to refer to the official press release of Oracle Corporation: http://www.oracle.com/innodb/index.html If yes, will you still be supporting it or will it be up to MySQL AB? I want to refer to the official press release where Charles Rozwat, Oracle's Executive Vice President in charge of Database and Middleware Technology says: Oracle intends to continue developing the InnoDB technology and expand our commitment to open source software. TIA Mike Regards, Heikki Tuuri Vice President, server technology Oracle Corporation -- 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] -- 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: Help optimize this simple find
Brian Dunning [EMAIL PROTECTED] wrote on 11/04/2005 10:36:00 PM: This simple find is taking 4 to 7 seconds. Way too long!! (This is a geotargeting query using the database from IP2location.) select lat,lon from geocodes where ipFROM=1173020467 and ipTO=1173020467 The database looks like this (how IP2location recommends): CREATE TABLE `geocodes` ( `ipFROM` int(10) unsigned zerofill NOT NULL default '00', `ipTO` int(10) unsigned zerofill NOT NULL default '00', `lat` double default NULL, `lon` double default NULL, PRIMARY KEY (`ipFROM`,`ipTO`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; And there are 1.7 million records. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I would bet that if you do an EXPLAIN on your query that you will see that you wound up with a full table scan. It did this because it takes fewer read operations to just scan the table than if you do an indexed lookup for any more than about 30% of the rows in any table. Can you not change the query to not use = or = ?? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
match by relevancy
I'm using PHP Version 4.3.9 and MySQL 4.1.12. I recently upgraded from
PHP 4.1.x and MySQL 4.0.x.
Basically my SQL worked until my upgrade. ORDER BY relevancy DESC no longer
works?
SELECT *,MATCH (AU,ST,SD)
AGAINST ('johnston' IN BOOLEAN MODE)
AS relevancy FROM ccl.ccl_main
WHERE MATCH (AU,ST,SD)
AGAINST ('johnston' IN BOOLEAN MODE)
ORDER BY relevancy DESC;
I see no inpsiration in the relevant part of the
Docs: http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html nor
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Desperately looking for an answer. Any thoughts?
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
