Re: Aborted connects
This is because either somebody is attacking your mysqld or your variable named connect_timeout is very lower. On Wed, Oct 22, 2008 at 9:55 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi list, Aborted clients and connects are increasing very fast. Aborted_clients 4934 Aborted_connects 5034 connect_timeout 10 wait_timeout 28800 Please tell me how to fix this problem. -- Krishna Chandra Prajapati MySQL DBA, Mob: 9912924044 Email-id: [EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: super-smack on mysql 5.0 solaris 10 x86_64
Maybe you should set your old_passwords = on. On Fri, Oct 17, 2008 at 4:36 PM, Sudhir Menon [EMAIL PROTECTED] wrote: Hi Ujang I could reproduce the same error mentioned by you in the actual post even after having all of the packages for MySQL. Figuring out the what could be the problem . Thanks Regards Sudhir Menon -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
archive mysql data
dear all, I need your direction about archiving mysql data. the scenario is: mysql on host A with dbname DB1 has the INSERT 90% and select 10% activities. mysql on host B with dbname DB2 as an archiver database of DB1 database. on DB1 the data will be purged daily on midnight. I need all the data is transferred to DB2 (my be with replication) but don't want transfer DELETE DROP statement into it. I heard about mk-archiver, but it seem not good from the performance perspective. Is there a way to archive the data by utilizing replication with DML (DELETE) and DDL (DROP) filtered? -- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyQuery 2.3 Beta available for download
It's very nice! But it's too simple. On Mon, Oct 6, 2008 at 12:26 AM, Anders Karlsson [EMAIL PROTECTED] wrote: Sorry for crossposting, but I think this is relevant both th general MySQL and specifically to Win32 users. MyQuery 2.3 has a lot of new features, a few bugfixes and some other niceties: * Output and sparse_output commands - The commands will output selected data to a file. The latter is a way to output ONLY what you select, no column headers, no summaries, ni fillers etc. only the data. This is useful when using the next new feature. * Source commands - This is a means of running another script from inside a script. * A better, more structured settings dialog using tabs. * Better handling of locked results, and a means of auto-locking results, so that where there are more than on result, these will show up in multiple tabs. * Lock / unlock of result tabs using right-click on the tabs. * Fixed a bug in the login dialog that caused the database list to work if there was an initial, unsuccessful attempt to connect. Dowload from https://sourceforge.net/projects/myquery/ Using the SPARSE_OUTPUT_FILE and SOURCE commands to write SQL that in turn generates SQL and then ruun this is a powerful feature. This particular feature is though rather complex to implement, more so than one might think, so input in this area is highly valued. /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Joining subqueries
mrc_titles is a temp table? On Wed, Oct 15, 2008 at 11:59 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: I tried to make a query that joins to subqueries: SELECT discontinued.b FROM (SELECT mrc_titles.title AS a FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title JOIN pub ON prod.pub_id = pub.pub_id WHERE pub.pub_code = MRC AND prod.prod_discont = 1) AS `discontinued` LEFT JOIN (SELECT mrc_titles.title AS b FROM mrc_titles JOIN prod ON mrc_titles.title = prod.prod_title JOIN pub ON prod.pub_id = pub.pub_id WHERE pub.pub_code = MRC AND prod.prod_discont = 0) AS `available` ON discontinued.a = available.b WHERE available.b IS NULL ; Basically I'm trying to find the `mrc_titles.title` records that only match where `prod`.`prod_discont` = 1, excluding those that match `prod`.`prod_discont` = 0. I think the query makes sense to a human, but I get ERROR 1137 (HY000): Can't reopen table: 'mrc_titles' from MySQL 4.1.22-standard. I didn't see anything about this limitation in the 4.x documentation (although somehow it seems to ring a bell). What am I missing? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: simple design choice
If you want to choose the first one, then the flag' data type must be enum.For int fill the disk with 4 byte and enum just 1 byte. On Sat, Oct 4, 2008 at 2:15 AM, Alex K [EMAIL PROTECTED] wrote: That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. 2008/10/3 Mr. Shawn H. Corey [EMAIL PROTECTED]: On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: On Fri, Oct 3, 2008 at 9:49 AM, Alex K [EMAIL PROTECTED] wrote: Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex #1 Define uses a lot of unnecessary space. I would imagine it would add not much more than 1 MB to the size of db, depending on column choice. A decent choice I think. #2 Yuck. #3 A compact and clean solution. If you're going to do #1, make the new column status, with two states: active and deleted. In the future you can add more states without re-doing your tables again. -- Just my 0.0002 million dollars worth, Shawn Linux is obsolete. -- Andrew Tanenbaum -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Master-master setup
There're an article about master to master replication at my blog. On Thu, Oct 9, 2008 at 3:45 PM, Simon J Mudd [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Carl) writes: I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the edits and deletes would work (primary key is autoincrement): (Serial) (Serial) TransactionServer A Server B Add to server A1 Replicated 1 Add to server A2 Add to server B (before record 2 2 is replicated) Replicate to server B ? Replicate to server A? Does replication control the order in which transactions are applied so that somehow the replication from server A to server B is applied before the insert to server B? You need to set 2 variables to ensure you don't have problems. # when you have 2 master servers auto_increment_increment = 2 # each server has a different offset (values in this case 1,2) auto_increment_offset= 1 This way each master will generate unique ids Note: doing this means that you will get gaps in your ids as each server uses its own value to generate new ids and these increment by auto_increment_increment every time. Be aware that if the updates to the tables are very frequent it's quite possible that replication delay may mean that the data on both servers is not the same. The only way to ensure that this is avoided is to use explicit WHERE id IN (1,3,43,5,...,nn) clauses so that you are absolutely certain that the changes applied on one master will be produced on the other one. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Most efficient way of handling a large dataset
I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). The selection is done via a web-based interface. Unfortunately, it's too slow. So I want to be able to optimise it for faster access. Speed of updating is less crucial, as it isn't updated in real-time - the table gets updated by a nightly batch job that runs outside normal working hours (and, apart from the rare occasion when a location is added or removed, the only thing that changes is the value in qty). Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Most efficient way of handling a large dataset
You might consider adding qty to the index and have so your queries would be satisfied with the index lookup alone, saving an extra step since the database won't then go access the data (just the one field, qty). You might also consider making all field non-null and, if you keep the fields as character data, make them fixed length, not variable, even if it wastes space. Integer lookups are faster than character lookups and you can probably keep conversions tables for most if not all of those fields in memory. You might think about the query cache.. If you have it on but your lookups aren't repeated often enough, the query cache won't be used. If you don't have it on, but a lot of your queries are repeated, then having it on will help. On Fri, Oct 24, 2008 at 5:59 AM, Mark Goodge [EMAIL PROTECTED] wrote: I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). The selection is done via a web-based interface. Unfortunately, it's too slow. So I want to be able to optimise it for faster access. Speed of updating is less crucial, as it isn't updated in real-time - the table gets updated by a nightly batch job that runs outside normal working hours (and, apart from the rare occasion when a location is added or removed, the only thing that changes is the value in qty). Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: ORDER BY Help
SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
ORDER BY Help
Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil
Re: Most efficient way of handling a large dataset
On Fri, Oct 24, 2008 at 6:59 AM, Mark Goodge [EMAIL PROTECTED] wrote: I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). The selection is done via a web-based interface. Unfortunately, it's too slow. So I want to be able to optimise it for faster access. Speed of updating is less crucial, as it isn't updated in real-time - the table gets updated by a nightly batch job that runs outside normal working hours (and, apart from the rare occasion when a location is added or removed, the only thing that changes is the value in qty). Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Mark -- http://mark.goodge.co.uk - my pointless blog http://www.good-stuff.co.uk - my less pointless stuff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Your first query uses an index, so that should be fairly quick. Your second query does not because your only index starts with partcode, but you are not searching on partcode. Add an index on region+location. That should speed the second query up considerably. If you want to normalize your data, you should replace region and location with ids that link to another table. While that will speed up searches, the speed improvement likely won't be noticeable for the searches you listed. Make sure query cache is enabled. That will help a lot since the result of the search will be cached until the table changes. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY Help
Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Hi Thanks for your quick reply. The sample value for a would be like a average of integer. e.g 6.01, or 10.19. Neil On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Still doesn't make much sense to me. Could you show us how to calculate it for some of the rows above? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi Thanks for your quick reply. The sample value for a would be like a average of integer. e.g 6.01, or 10.19. Neil On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: ORDER BY Help
Hi Basically from the query below, it would only return one product like RedLight. But I need to return a list of all products, ordered by a SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC On Fri, Oct 24, 2008 at 2:53 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Still doesn't make much sense to me. Could you show us how to calculate it for some of the rows above? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi Thanks for your quick reply. The sample value for a would be like a average of integer. e.g 6.01, or 10.19. Neil On Fri, Oct 24, 2008 at 2:49 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: Could give us sample values for a field? Should it contain the same thing as in the query I've sent? 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Hi This works, however I still want to be able to list the whole list like because I need to display it on the screen, but in the ordered together i.e all RedLights, all BlueLights etc a Date ProductID ProductName ProductScore Quantity %2008-11-10100 Red Light 0.05 10 %2008-11-11100 Red Light 0.05 2 %2008-11-12100 Red Light 0.05 0 %2008-11-10150 Blue Light 0.01 5 %2008-11-11150 Blue Light 0.01 5 %2008-11-12150 Blue Light 0.01 5 %2008-11-10160 Green Light 0.055 %2008-11-11160 Green Light 0.065 %2008-11-12160 Green Light 0.115 Is this possible ? On Fri, Oct 24, 2008 at 2:28 PM, Olexandr Melnyk [EMAIL PROTECTED]wrote: SELECT ProductID, ProductName, AVG(ProductScore * Quantity) AS a FROM Products GROUP BY ProductID ORDER BY a DESC 2008/10/24, Tompkins Neil [EMAIL PROTECTED]: Following on from my email below I now need help with the following problem. Here is a list of my sample data Date ProductID ProductNameProductScore Quantity 2008-11-10100 Red Light 0.05 10 2008-11-11100 Red Light 0.05 2 2008-11-12100 Red Light 0.05 0 2008-11-10150 Blue Light 0.01 5 2008-11-11150 Blue Light 0.01 5 2008-11-12150 Blue Light 0.01 5 2008-11-10160 Green Light 0.055 2008-11-11160 Green Light 0.065 2008-11-12160 Green Light 0.115 I need to list this data in the order of the the product with the highest quantity, followed by ProductScore. Am I able to calculate a quantity percentage, based on the number of records for say Red Light. Thanks, Neil On Fri, Oct 24, 2008 at 1:32 PM, Tompkins Neil [EMAIL PROTECTED] wrote: Thanks for the reply, this is exactly what I wanted. Cheers Olexandr ! On Fri, Oct 24, 2008 at 1:26 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY CASE WHEN ProductScore = 125 THEN 0 ELSE 1 END, ProductScore But this query won't use an index, so it would be a good idea to do this in two queries 2008/10/24 Tompkins Neil [EMAIL PROTECTED] Hi I've the following basic query like SELECT ProductName FROM Products WHERE ProductScore 100 ORDER BY ProductScore However, how can I order by ProductScore, but ensure the product with ID 125 is at the top ? Is this possible. Thanks Neil -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: Stopping DNS Lookups
Yes, that's exactly what the link from Hassan said to do. Interestingly, what is not stated in that link is that you must add entries in mysql tables 'db' and 'user' for '127.0.0.1' (a.k.a. 'localhost') if you need to access the database from the local server. Remote access via static IP (assuming those entries were already in 'db' and 'user') work fine, but 'localhost' no longer works after the reboot. Cheers, -Richard Moon's Father wrote: Add skip-name-reslove in my.cnf and restart mysql immediately. On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Awesome! Thanks very much - exactly what I was looking for. I'm in the field and was under the gun, otherwise would have checked the manuals first. Again, thanks. -Richard Hassan Schroeder wrote: On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Recently I had to start monitoring the firewall traffic on this intranet, and discovered the MySQL server is routinely sending queries to the main DNS server (outside the firewall). I suspect the server is performing reverse DNS lookups for some reason. Is there a quick way of disabling these calls to the DNS server? See http://dev.mysql.com/doc/refman/5.0/en/dns.html HTH, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which solution is better for $count and @cols
On Thu, Oct 23, 2008 at 10:31 PM, Fayland Lam [EMAIL PROTECTED] wrote: B one SQLs with some operation SELECT col FROM table WHERE $where while $count is scalar @cols and real cols is splice(@cols, $start, $rows) If you're talking about Perl/DBI, doing that normally loads the entire result set into your program's memory. Using a LIMIT avoids that. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stopping DNS Lookups
Not on my testing system (Win-XP). I test on Win-XP but deploy on Unix. Not only that, but the production application servers are separate from the database server, so I never use localhost in production anyway. But on the XP test server, I had to add the 127.0.0.1 entries to 'db' and 'user' - 'localhost' alone did not work. I did not test whether or not 'localhost' would work on a Unix server as this was not something I required. Would be a good test, though. Cheers, -Richard Martin Gainty wrote: Richard- That should work..curious if localhost is mapped elsewhere? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Fri, 24 Oct 2008 07:51:44 -0700 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Stopping DNS Lookups Yes, that's exactly what the link from Hassan said to do. Interestingly, what is not stated in that link is that you must add entries in mysql tables 'db' and 'user' for '127.0.0.1' (a.k.a. 'localhost') if you need to access the database from the local server. Remote access via static IP (assuming those entries were already in 'db' and 'user') work fine, but 'localhost' no longer works after the reboot. Cheers, -Richard Moon's Father wrote: Add skip-name-reslove in my.cnf and restart mysql immediately. On Thu, Oct 23, 2008 at 12:37 AM, Richard S. Huntrods [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Awesome! Thanks very much - exactly what I was looking for. I'm in the field and was under the gun, otherwise would have checked the manuals first. Again, thanks. -Richard Hassan Schroeder wrote: On Wed, Oct 22, 2008 at 7:40 AM, Richard S. Huntrods [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Recently I had to start monitoring the firewall traffic on this intranet, and discovered the MySQL server is routinely sending queries to the main DNS server (outside the firewall). I suspect the server is performing reverse DNS lookups for some reason. Is there a quick way of disabling these calls to the DNS server? See http://dev.mysql.com/doc/refman/5.0/en/dns.html HTH, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] You live life beyond your PC. So now Windows goes beyond your PC. See how http://clk.atdmt.com/MRT/go/115298556/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Most efficient way of handling a large dataset
Hi Mark, all! Mark Goodge wrote: I'd appreciate some advice on how best to handle a biggish dataset consisting of around 5 million lines. At the moment, I have a single table consisting of four fields and one primary key: partcode varchar(20) region varchar(10) location varchar(50) qty int(11) PRIMARY KEY (partcode, region, location) The biggest variable is partcode, with around 80,000 distinct values. For statistical purposes, I need to be able to select a sum(qty) based on the other three fields (eg, select sum(qty) from mytable where partcode ='x' and region = 'y' and location = 'z') as well as generating a list of partcodes and total quantities in each region and location (eg, select sum(qty), partcode from mytable where region = 'y' and location = 'z' group by partcode). Sorry, I don't get it - I think there is a contradiction in your mail: Your table has four fields, three of which form the primary key. This means that for any combination of values for those three fields (partcode, region, location) there will be at most one row, and so only one qty value. Why do you want to sum over one value? And in your second query there is also no need for sum(qty), a plain qty will do because for each group there will be only one row (region and location are set to fixed values in your statement). IMO, the main question is whether all your statements use fixed values for region and location (like your second statement), but only some do for partcode (your first statement). If that holds true for your statements, then your primary key is defined in the wrong order: it should have partcode as the last field (= the least significant one). Then, all your statement could use the primary key, and you need no separate index. [[...]] Does anyone have any suggestions? My initial thought is to replace the region and location varchar fields with int fields keyed to a separate list of region and location names. Would that help, or is there a better way? Well, if your data are integer values, then using integer as column type should speed up your operations considerably: Operations (including comparisons) on integers are faster than on character strings, and reduced data size means shorter (= faster) transfers and more elements in caches (assuming same cache size). HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment problem
Anybody know if there's a way to change a primary key field that is not auto-incremented, turning on auto-increment but preserving the values that are currently in it? TIA, Paul W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to count # of character occurrences in a string?
I have a Char column with text data like ab:cdef:h and I'd like to count the number of : in the column. I can't find an easy way to do it. I thought there should be a MySQL function to do this. Any suggestions? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to count # of character occurrences in a string?
I have a Char column with text data like ab:cdef:h and I'd like to
count the number of : in the column. I can't find an easy way to do
it. I thought there should be a MySQL function to do this. Any suggestions?
select length('aa:bb:cc:dd')-length(replace('aa:bb:cc:dd',':',''));
btw ever heard of google?
t
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to count # of character occurrences in a string?
A quick and dirty way would be to use an expression like length(x) - length(replace(x, :, )) Here's some code that I actually ran: set @x = aa:bb:cc:dd; set @colons = length(@x) - length(replace(@x, :, )); select @x , @colons; On Fri, Oct 24, 2008 at 5:16 PM, mos [EMAIL PROTECTED] wrote: I have a Char column with text data like ab:cdef:h and I'd like to count the number of : in the column. I can't find an easy way to do it. I thought there should be a MySQL function to do this. Any suggestions? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: mysqld, mysqld-nt, mysqld-debug
Hi. I think you had a mistake for the server types.Mysql-nt.exe is the only exexutable program on windows.So you just pay close attention about it. On Mon, Oct 6, 2008 at 8:35 PM, Steven [EMAIL PROTECTED] wrote: Steve (n) Martin! if you want apache and mysql (and possibly PHP for scripting) AND you want all 3 to run at once then d/l and implement with XAMP there are alot of XAMP specific bells and whistles that are installed that get in the way of tuning your MySQL and tuning your Apache installations so my advice is if you just want to use Mysql standalone dont use XAMP but start mysql with mysqld as suggested Well, that's your opinion. But I think for beginners is XAMPP the best basis. Everything else you can later still wish to change. Once is a fact, I wanted no discussion on the pros and reignite XAMPP. I just wanted to help. ;-) Viel Gluck! Martin Good Luck? What for? Greetings, Steven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: mysqldump: Error 5: Out of memory
Show the details of your hardware us. On Thu, Oct 2, 2008 at 3:02 PM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, Just try the below command on console. It will give that the error is exactly related to what. $perror 5 What is total ram in your box. On Thu, Oct 2, 2008 at 12:26 PM, Uma Bhat [EMAIL PROTECTED] wrote: Hey Guys! I have been googling a lot on this error and read various suggestions. But havnt found an appropriate solution yet. I get this error while taking mysqldump of an InnoDB table (say mytable) mysqldump: Error 5: *Out of memory (Needed 632894352 bytes) when dumping table `mytable` at row: 484911* *current my.cnf settings:* innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 32M max_allowed_packet = 1024M -q with mysqldump option did not help. it resulted in same error. Appreciate your quick response ! Much Thanks, Uma -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Speed up slow SQL statement.
You may see the section named group by optimization on the document. On Tue, Sep 30, 2008 at 4:44 AM, Rob Wultsch [EMAIL PROTECTED] wrote: Glancing over things I suggest: ALTER TABLE browse_nodes_to_products ADD INDEX(browse_node_id,product_id); (if product_id has greater cardinality put that before browse_node_id) The syntax: inner join (browse_nodes, browse_nodes_to_products) on (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id and products.id = browse_nodes_to_products.product_id) is pretty ugly in my opinion. On Mon, Sep 29, 2008 at 7:10 AM, Eric Stewart [EMAIL PROTECTED] wrote: Good morning everyone, products.id is defined as a PRIMARY KEY so it's index. browse_nodes_to_products.product_id is defined as a INDEX so it's indexed. browse_nodes_to_products.browse_node_id is defined as an INDEX so it's indexed. browse_nodes.amazon_id is defined as an INDEX so it's indexed. See http://pastebin.com/m46cced58 It has complete table structures, row counts and EXPLAIN output of the SQL statement I'm trying to optimize. I don't think I understand your question regarding carrying the product_id through the relationship. This is a many to many relationship. A browse_node can contain many products and a product can be in many browse_nodes. This is achieved through a many to many join table browse_nodes_to_products. Further research into the SQL statement is revealing that a temp table is being created and may be one of the reason it's slowing down. Any ideas how I can optimize this? Eric On Sep 26, 2008, at 11:47 AM, Martin Gainty wrote: Hi Eric- the immediate challenge is to fic the join statement so make sure products.id is indexed make sure browse_nodes_to_products.product_id is indexed make sure browse_nodes_to_products.browse_node_id is indexed make sure browse_nodes.amazon_id is indexed there seems to be mapping/relationship challenge for your product to browse_node_id which finally maps to amazon_id would be simpler if is there any way you can carry the product_id thru from products table to browser_nodes_to_products table to browse_nodes table anyone? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Speed up slow SQL statement. Date: Fri, 26 Sep 2008 10:42:07 -0400 Good morning everyone, I've got a sql statement that is running quite slow. I've indexed everything I can that could possibly be applicable but I can't seem to speed it up. I've put up the table structures, row counts, the sql statement and the explain dump of the sql statement all in paste online here http://pastebin.com/m46cced58 I'm including the sql statement itself here as well: select distinct products.id as id, products.created_at as created_at, products.asin as asin, products.sales_rank as sales_rank, products.points as points from products inner join (browse_nodes, browse_nodes_to_products) on (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id and products.id = browse_nodes_to_products.product_id) where browse_nodes.lft = 5 and browse_nodes.rgt = 10 order by products.sales_rank desc limit 10 offset 0; What I'm trying to accomplish with this is to get an ordered list of unique products found under a category. Any ideas on how I could speed this up? Thanks in advance, Eric Stewart [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL 5.1 Function Creation
Make sure your log_bin_trust_function_creator is on. On Sun, Sep 28, 2008 at 3:04 AM, Jesse [EMAIL PROTECTED] wrote: I'm trying to use existing functions from a restored database from 5.0xx to 5.1, and get an error about the mysql.proc table is missing or corrupt. The mysql.proc table appears to be there, and does not appear to be corrupt. I did a grant select on mysql.proc to user, and that did not make any difference, as it has in the past. So, I decided that I'd delete the function from the database, and try to add it back in, and when I do, I get an error, Failed to CREATE FUNCTION. The code that I'm trying to execute is as follows: CREATE DEFINER = 'root'@'localhost' FUNCTION `Age`(dob DATE) RETURNS int(11) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE today DATE; SELECT CampStartDate INTO today FROM config; RETURN DATE_FORMAT(FROM_DAYS(TO_DAYS(today) - TO_DAYS(dob)), '%Y') + 0; END; Any ideas what's going on? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: How could i check the following values in MySQL Server 5.0
Any additional tools will satisfy your demand. On Mon, Oct 13, 2008 at 7:34 PM, Sudhir Menon [EMAIL PROTECTED] wrote: My reply was with regards to the answer in this post. http://lists.mysql.com/mysql/214827 Anyways thanks for the concern from your end.. Mike :) My query was answered. Thanks Regards Sudhir Menon -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: order of items in a WHERE...IN clause
See the usage of the function named field. On Mon, Jul 28, 2008 at 8:15 PM, Mr. Shawn H. Corey [EMAIL PROTECTED]wrote: On Mon, 2008-07-28 at 07:32 -0400, Gary Josack wrote: Andrew Martin wrote: Hello, Is it permissible to order a clause such that the search term is the first item (in the clause)? standard: field1 IN (123, 654, 789) in question: 123 IN (field1, field2, field3) I am interested to know if the optimizer treats this any differently if anybody can shed any light on it (except for the obvious difference in the above queries!) Thanks, Andy Both are valid syntax where 1 is returned if the expression is equal to any of the values in the list. I can't see the optimizer treating these any differently. Thanks, Gary M. Josack Any difference will come up in an EXPLAIN. To run one, put the word EXPLAIN in front of the SQL statement: EXPLAIN sql_statement; -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
