Order by in clause
Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the in clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id | title | +--+-+ | 10 |foo | +--+-+ | 3 |baz | +--+-+ | 8 |bar | +--+-+ | 5 | wibble | +--+-+ | 1 | flirble | +--+-+ Is this possible? If so, how? Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Order by in clause
On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge m...@good-stuff.co.uk wrote: Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the in clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id | title | +--+-+ | 10 | foo | +--+-+ | 3 | baz | +--+-+ | 8 | bar | +--+-+ | 5 | wibble | +--+-+ | 1 | flirble | +--+-+ Is this possible? If so, how? select * from product where id in (10,3,8,5,1) order by field(id,10,3,8,5,1) should do the trick... Regards, Joeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
在 2010年11月9日 下午3:51，Johan De Meersman vegiv...@tuxera.be 写道： Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Thanks. But this seems the same case happened: mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ And I'm sure the select can fetch records: mysql select * from ip_test where startNum = 3061579775 and endNum = 3061579775; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | Please suggest, thanks again. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). Thanks Aveek On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote: Indexes typically only work on the left-hand-side. Rewrite as select * from ip_test where startNum = 3061579775 and endNum = 3061579775; Magic will happen. 2010/11/9 wroxdb wro...@gmail.com Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a SHOW INDEX to see the cardinality information for these key columns. Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best encription method?
Sir, We have done a encryption by using our own algorithm. it works fine. But during the report analysis we are facing the performance issue. Which is not appreciated by our users. We need only one column to be encrypted and the column size will never exceed 3 chars. We have planed to use the AES_ENCRYPT and AES_DECRYPT by using the key string as a parameter. We can keep this key string as our security point. We found it is giving better performance in all cases. Can i have your advise please? Thank you in advance! Vikram A From: Vikram A vikkiatb...@yahoo.in To: Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 29 September, 2010 11:46:18 AM Subject: Re: Best encription method? Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Best encription method?
Dear Sir, As you advised we will keep the key as well documented. Thank you Vikram A. From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 9 November, 2010 7:05:39 PM Subject: Re: Best encription method? Seems like a better plan than simply rolling your own, yes. Do make sure that the key string is well-documented :-p On Tue, Nov 9, 2010 at 2:30 PM, Vikram A vikkiatb...@yahoo.in wrote: Sir, We have done a encryption by using our own algorithm. it works fine. But during the report analysis we are facing the performance issue. Which is not appreciated by our users. We need only one column to be encrypted and the column size will never exceed 3 chars. We have planed to use the AES_ENCRYPT and AES_DECRYPT by using the key string as a parameter. We can keep this key string as our security point. We found it is giving better performance in all cases. Can i have your advise please? Thank you in advance! Vikram A From: Vikram A vikkiatb...@yahoo.in To: Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 29 September, 2010 11:46:18 AM Subject: Re: Best encription method? Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Best encription method?
Seems like a better plan than simply rolling your own, yes. Do make sure that the key string is well-documented :-p On Tue, Nov 9, 2010 at 2:30 PM, Vikram A vikkiatb...@yahoo.in wrote: Sir, We have done a encryption by using our own algorithm. it works fine. But during the report analysis we are facing the performance issue. Which is not appreciated by our users. We need only one column to be encrypted and the column size will never exceed 3 chars. We have planed to use the AES_ENCRYPT and AES_DECRYPT by using the key string as a parameter. We can keep this key string as our security point. We found it is giving better performance in all cases. Can i have your advise please? Thank you in advance! Vikram A From: Vikram A vikkiatb...@yahoo.in To: Johan De Meersman vegiv...@tuxera.be Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Wed, 29 September, 2010 11:46:18 AM Subject: Re: Best encription method? Sir, I must adopt the encryption. I shall bear the cost of the memory. Thank you for the guidance. Vikram A From: Johan De Meersman vegiv...@tuxera.be To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Tue, 28 September, 2010 2:08:57 PM Subject: Re: Best encription method? On Mon, Sep 27, 2010 at 3:43 PM, Vikram A vikkiatb...@yahoo.in wrote: Normally, If i need to store an integer value i have to define it as int, If I encrypt this, i must define its type as string of different size[it depend upon the encryption output] than its original size. It increases the db size. I am known that if it is secure i have to choose encryption. but when i choose encryption it leads the more memory usage. Well, yes :-) That's part of the price you're going to pay for the security. As the programmer said; I can make it better, faster and cheaper. Pick any two. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: a query not using index
On 11/8/2010 10:47 PM, wroxdb wrote: Hello, I have a query below: mysql select * from ip_test where 3061579775 between startNum and endNum; +++-+--+--++ | startNum | endNum | country | province | city | isp| +++-+--+--++ | 3061514240 | 3061579775 | 中国| 河南 | | 联通 | +++-+--+--++ the desc shows it isn't using the index: mysql desc select * from ip_test where 3061579775 between startNum and endNum; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ 1 row in set (0.01 sec) the table structure is: CREATE TABLE `ip_test` ( `startNum` double(20,0) default NULL, `endNum` double(20,0) default NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 please help, thanks in advance. Have you tried a combined index of (startnum,endnum) instead of two single-column indexes? You may still run into problems, though, because ranged searches are usually performed as WHERE column_A BETWEEN X AND Y and not as WHERE X BETWEEN column_A and column_B and the optimizer has been designed to evaluate the first pattern but not the second. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a query not using index
Would a compound index on both startnum and endnum be a better choice? JW On Tuesday, November 9, 2010, Aveek Misra ave...@yahoo-inc.com wrote: Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a SHOW INDEX to see the cardinality information for these key columns. Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: Thanks for the idea. I have changed the datatype to bigint, the result is not changed. mysql desc select * from ip_test where startNum = 3061579775 and endNum = 3061579775; ++-+-+--+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+-+--+-+--++-+ | 1 | SIMPLE | ip_test | ALL | startNum,endNum | NULL | NULL | NULL | 396528 | Using where | ++-+-+--+-+--+-+--++-+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT NULL, `country` varchar(50) NOT NULL default '', `province` varchar(50) NOT NULL default '', `city` varchar(50) NOT NULL default '', `isp` varchar(100) default NULL, KEY `startNum` (`startNum`), KEY `endNum` (`endNum`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 在 2010年11月9日 下午5:20，Aveek Misra ave...@yahoo-inc.com 写道： I don't see how BETWEEN is not equivalent to (startNum = and endNum =). Of course please try and let us know if that resolves the issue. But if it doesn't, I suspect it is because the indexes are created on columns which are floating point data type. That's because floating point numbers are approximate and not stored as exact values. Attempts to treat double values as exact in comparison may lead to the kind of issues that you are getting. I could be wrong though; but if Johan's trick does not work, you might try and change the data type to DECIMAL to see if it helps (or BIGINT if your numbers are not using any digits after the decimal since BIGINT and DOUBLE both use 8 bytes for storage). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
question about restoring...
So, I got a request this morning to recover some specific records for a client. I just want a handful of records from a couple of tables here. I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? thanks, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: question about restoring...
No, you should import the data into another instance of mysql to extract the records. Regards, Gavin Towey -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Tuesday, November 09, 2010 10:34 AM To: mysql list Subject: question about restoring... So, I got a request this morning to recover some specific records for a client. I just want a handful of records from a couple of tables here. I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? thanks, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: question about restoring...
Not if he has the raw innodb files. -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Tuesday, November 09, 2010 11:05 AM To: Gavin Towey; 'Andy Wallace'; 'mysql list' Subject: RE: question about restoring... That's overkill. You should be able to import the data into another database within the same instance, unless the file is too big to handle. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Tuesday, November 09, 2010 1:50 PM To: Andy Wallace; mysql list Subject: RE: question about restoring... No, you should import the data into another instance of mysql to extract the records. Regards, Gavin Towey -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Tuesday, November 09, 2010 10:34 AM To: mysql list Subject: question about restoring... So, I got a request this morning to recover some specific records for a client. I just want a handful of records from a couple of tables here. I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? thanks, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Duplicate entry '2' for key 1
Dear friends. Anybody would be so nice to explain about meaning of this error message: Duplicate entry '2' for key 1 :. It comes if we visit this: http://www.otekno.biz/kn/code/functions.php?task=sync Thank you very much in advance. -- Enjoy our free facilities: http://www.otekno.biz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Order by in clause
-Original Message- From: Joeri De Backer [mailto:fons...@gmail.com] Sent: Tuesday, November 09, 2010 1:16 AM To: mysql Subject: Re: Order by in clause On Tue, Nov 9, 2010 at 10:09 AM, Mark Goodge m...@good-stuff.co.uk wrote: Hi, I have a query like this: select id, title from product where id in (1,3,5,8,10) What I want it to do is return the rows in the order specified in the in clause, so that this: select * from product where id in (10,3,8,5,1) will give me results in this order: +--+-+ | id | title | +--+-+ | 10 | foo | +--+-+ | 3 | baz | +--+-+ | 8 | bar | +--+-+ | 5 | wibble | +--+-+ | 1 | flirble | +--+-+ Is this possible? If so, how? select * from product where id in (10,3,8,5,1) order by field(id,10,3,8,5,1) should do the trick... Regards, Joeri ...ya learn something new every day... ;-) Here's more on this topic: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Duplicate entry '2' for key 1
Ilham, It means what it says.You are attempting to insert into a table that has a primary or other unique key and that data which you are trying to insert already has that unique value. To diagnose, it would be helpful to know the circumstance under which you got this error message. - michael dykman On Tue, Nov 9, 2010 at 3:36 PM, Ilham Firdaus il...@otekno.biz wrote: Dear friends. Anybody would be so nice to explain about meaning of this error message: Duplicate entry '2' for key 1 :. It comes if we visit this: http://www.otekno.biz/kn/code/functions.php?task=sync Thank you very much in advance. -- Enjoy our free facilities: http://www.otekno.biz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: question about restoring...
Then I guess it's a matter of preference. I'd rather edit a text file than build a new instance of MySQL. On the other hand, if he has a development environment (as we all, of course, do) then he could futz around in there. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Tuesday, November 09, 2010 3:22 PM To: Jerry Schwartz; 'Andy Wallace'; 'mysql list' Subject: RE: question about restoring... Not if he has the raw innodb files. -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Tuesday, November 09, 2010 11:05 AM To: Gavin Towey; 'Andy Wallace'; 'mysql list' Subject: RE: question about restoring... That's overkill. You should be able to import the data into another database within the same instance, unless the file is too big to handle. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Tuesday, November 09, 2010 1:50 PM To: Andy Wallace; mysql list Subject: RE: question about restoring... No, you should import the data into another instance of mysql to extract the records. Regards, Gavin Towey -Original Message- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Tuesday, November 09, 2010 10:34 AM To: mysql list Subject: question about restoring... So, I got a request this morning to recover some specific records for a client. I just want a handful of records from a couple of tables here. I have a copy of the INNODB files for these two tables - is there a way to extract the table contents from these files short of a full import? thanks, ansdy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL clustering and licensing
Hi! Machiel Richards wrote: Good day all Maybe someone can assist me here as I am not sure where to get this information from and I need this for a proposed environment for a client. 1. The client will have 2 new machines, had a look at the specs and it is fairly good considering it will be dedicated to MySQL. The question is, is the solution able to use 2 nodes without shared storage and be clustered? If by clustered you mean what most products call cluster, you cannot apply that concept to MySQL: The MySQL Cluster product is an in-memory database keeping the data mirrored in the RAM (!) of (at least) two machines. It is especially designed for extremely fast response times, typical customers include some telecommunication providers. Even though AFAIK you can run MySQL Cluster on just two nodes, I think it would be a very untypical setup, and very likely your client's application is not what MySQL Cluster was designed for. 2. Is there further licensing implications? MySQL Cluster is available with either GPL or a commercial license. 3. Is this part of the Enterprize edition license ? Currently there is a Mysql enterprize Gold support contract in place, however not sure it requires aditional licences for clustering or not. MySQL Cluster is a separate product, AFAIK it is not covered by any support contract for the general server but needs one of its own. I would really appreciate any suggestions here, I am busy trying to get the info from the net as well but I am pressured for responses. If your client runs a typical database application, I suspect the replication feature is much more appropriate for them. HTH, Joerg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL clustering and licensing
Good day thank you all for the responses thus far. Just to add onto the requirements. The client's business is based around a website that does all business related tasks and are exremely utilized. The idea is to provide failover as well as the best possible response times. Any ideas on this will help us out in making the final decisions. Ps there are only 2 machine available for the databases. Regards -Original Message- From: Joerg Bruehe joerg.bru...@oracle.com To: Machiel Richards machi...@rdc.co.za Cc: mysql mailing list mysql@lists.mysql.com Subject: Re: MySQL clustering and licensing Date: Wed, 10 Nov 2010 00:09:16 +0100 Hi! Machiel Richards wrote: Good day all Maybe someone can assist me here as I am not sure where to get this information from and I need this for a proposed environment for a client. 1. The client will have 2 new machines, had a look at the specs and it is fairly good considering it will be dedicated to MySQL. The question is, is the solution able to use 2 nodes without shared storage and be clustered? If by clustered you mean what most products call cluster, you cannot apply that concept to MySQL: The MySQL Cluster product is an in-memory database keeping the data mirrored in the RAM (!) of (at least) two machines. It is especially designed for extremely fast response times, typical customers include some telecommunication providers. Even though AFAIK you can run MySQL Cluster on just two nodes, I think it would be a very untypical setup, and very likely your client's application is not what MySQL Cluster was designed for. 2. Is there further licensing implications? MySQL Cluster is available with either GPL or a commercial license. 3. Is this part of the Enterprize edition license ? Currently there is a Mysql enterprize Gold support contract in place, however not sure it requires aditional licences for clustering or not. MySQL Cluster is a separate product, AFAIK it is not covered by any support contract for the general server but needs one of its own. I would really appreciate any suggestions here, I am busy trying to get the info from the net as well but I am pressured for responses. If your client runs a typical database application, I suspect the replication feature is much more appropriate for them. HTH, Joerg
Re: question about restoring...
On Tue, Nov 9, 2010 at 11:39 PM, Jerry Schwartz je...@gii.co.jp wrote: Then I guess it's a matter of preference. I'd rather edit a text file than build a new instance of MySQL. The way I parse that, you're saying that there is a way to reattach ibd files to another database ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
