Always corrupted after restart server
Hi List, I have a very big size MyISAM table. For some reason I need to restart the server periodically. But After restarting the server, the table always get corrupt, and always need to run myisamchk. Don't know what cause the problem. But it will be very helpful if somebody can give me some tips to avoid this problem. Thanks alot. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WTA Query For Winner Drawing
Dear List, I'm developing a winner drawing application using PHP+MySQL. As we know that MySQL has a built in function RAND() to randomize the result. But using that function will only randomize, my goal is the player with higher point gets higher posibility when randomizing. Anybody can help? Many thanks. sangprabv sangpr...@gmail.com http://www.petitiononline.com/froyo/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQLTuner
Hi, I run mysqltuner this morning and I got these warning: [!!] Key buffer size / total MyISAM indexes: 12.0G/23.2G [!!] Key buffer hit rate: 76.9% [!!] Query cache efficiency: 0.0% [!!] Temporary tables created on disk: 27% [!!] Table cache hit rate: 5% And mysqltuner recommends to adjust these setting: key_buffer_size ( 23.2G) query_cache_limit ( 1M, or use smaller result sets) tmp_table_size ( 64M) max_heap_table_size ( 128M) table_cache ( ) My physical RAM is only 16Gb. I am afraid it will freezed the server if I set key_buffer_size ( 23.2G). Any suggestion to optimize it? Thanks for any response. sangprabv sangpr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Open Tables
I saw so many open tables in my server, is it dangerous? And if I execute flush tables what does it impacts to the open tables? Thx. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
is_string or is_numeric
Hi, I found no built in function in mysql to check whether a record is numeric or string. Is there any trick to do so? Many thanks. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Optimizing my.cnf
I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: [mysqld] key_buffer = 512M max_allowed_packet = 512M thread_stack= 4096K thread_cache_size = 256 myisam-recover = BACKUP max_connections= 999 table_cache= 2048 thread_concurrency = 100 query_cache_limit = 32M query_cache_size= 512M expire_logs_days= 10 max_binlog_size = 100M skip-innodb skip-federated [mysqldump] quick quote-names max_allowed_packet = 512M [isamchk] key_buffer = 512M Is it optimized enough for a high load MySQL server machine? Is there any suggestion to get more speed and response? TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Optimizing my.cnf
As you see on my my.cnf I skip innodb and federated. So I just use myisam in this case. TIA. Willy On Mon, 2009-10-05 at 20:47 -0700, Rob Wultsch wrote: On Mon, Oct 5, 2009 at 6:12 PM, sangprabv sangpr...@gmail.com wrote: I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: blah blah blah... This heavily depends on workload. Are you using innodb? etc... -- Rob Wultsch wult...@gmail.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: sub query or something else
Many thanks for the query. It works ;)
Willy
On Fri, 2009-09-04 at 08:09 +0200, Wolfgang Schaefer wrote:
sangprabv wrote:
I have these query:
SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A';
SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B';
Is it possible to make the queries into 1 single query? How to make it
happen? Many thanks for helps.
Willy
You can group by partners and then calculate the price for the certain
partner, if that is what you want.
SELECT partner, IF(partner = 'A', sum(price)*0.5, '-') as price1,
IF(partner = 'B', sum(price)*0.65, '-')
as price2
FROM table
WHERE partner IN ('A', 'B')
GROUP BY partner;
cheers,
wolfgang
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
Many thanks for your query, seems we need to group it like Wolfgang's
does.
Willy
On Thu, 2009-09-03 at 22:33 -0700, Manasi Save wrote:
may be you can use IN clause:
SELECT SUM(price)*0.5 AS price1, SUM(price)*0.65 AS price2 FROM table
WHERE partner IN ('A', 'B');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sub query or something else
I have these query: SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; Is it possible to make the queries into 1 single query? How to make it happen? Many thanks for helps. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
What I'm looking for is to SUM the price from partner A and B with each result. So the result I expect is like Partner A total's price 123, Partner B total's price 456. Can you give me the query example? TIA. Willy On Thu, 2009-09-03 at 23:11 -0400, Robert Citek wrote: It's not clear what exactly you are looking for. Two possible solutions: 1) use a union 2) use a join with another table containing partner and factor fields. Can you give a short example of what the input looks like and what you would like the output to look like? Regards, - Robert On Thu, Sep 3, 2009 at 10:46 PM, sangprabvsangpr...@gmail.com wrote: I have these query: SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; Is it possible to make the queries into 1 single query? How to make it happen? Many thanks for helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Possible Faster Query
I have this query on my current application: SELECT count(sql_id) AS total, DATE_FORMAT(insertdate, '%H:%i' ) AS mydate FROM momt WHERE 1=1 AND insertdate BETWEEN DATE_ADD(NOW(), INTERVAL - 1 HOUR) AND NOW() AND (sms_type = 1 OR sms_type =2) GROUP BY mydate ORDER BY insertdate Is there any possibilities to speed up the query? Because my data become very huge and this query seems getting slower. TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple Rows DELETE Fails on Replication
DELETE FROM table WHERE key LIKE '%100%' Also I tried to continously insert big numbers of new records (around 50 millions new records) and tried to delete 1 record on master, I checked the slave not synchronized. Is it just a lag? Willy On Fri, 2009-06-26 at 16:14 +0800, Moon's Father wrote: Could you tell me your detail statements? On Wed, Jun 24, 2009 at 9:46 AM, sangprabv sangpr...@gmail.com wrote: Hi, I found every time I try to do multiple rows delete on replicated MySQL is always failed. Is there any explanation regarding this issue and how to solve it? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Delete replication on replication
I have configured mysql replication and test to insert some records. Both nodes synchronized but when I delete 1 record from the main node, why the second node not synchronized? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Delete replication on replication
Hi Isart, Thx for the reply. Currently it has been solved. I rolled back and re configure it. What I wonder is what cause the problem, since I did all the same procedures. FYI I didn't make any structure change on my DB and table. TIA. WM On Tue, 2009-06-23 at 21:15 +0100, Isart Montane wrote: what do yo get when you run show slave status on the slave? are you replicating the database you are modifying? Isart Montane On Tue, Jun 23, 2009 at 7:29 AM, sangprabv sangpr...@gmail.com wrote: I have configured mysql replication and test to insert some records. Both nodes synchronized but when I delete 1 record from the main node, why the second node not synchronized? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Multiple Rows DELETE Fails on Replication
Hi, I found every time I try to do multiple rows delete on replicated MySQL is always failed. Is there any explanation regarding this issue and how to solve it? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Master-Master Replication Problem
I have configured 2 MySQL server to do master-master replication. Below is my config: Node A server-id = 1 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 1 master-host = 10.1.20.103 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic Node B server-id = 2 log_bin = mysql-bin expire_logs_days= 10 max_binlog_size = 100M binlog_do_db= clustertest binlog_do_db= gateway binlog_do_db= reporting binlog_do_db= traffic binlog_ignore_db= mysql binlog_ignore_db= test replicate-same-server-id = 0 log-slave-updates auto-increment-increment = 10 auto-increment-offset= 2 master-host = 10.1.20.102 master-user = replicator master-password = username master-port = 3306 replicate-do-db = clustertest replicate-do-db = gateway replicate-do-db = reporting replicate-do-db = traffic I restart MySQL daemon, and go to mysql shell and execute this on both server: Node A flush tables with read lock; show master status \G Node b stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.102', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node A and execute unlock tables; Node B flush tables with read lock; show master status \G Node A stop slave CHANGE MASTER TO MASTER_HOST='10.1.20.103', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.01', MASTER_LOG_POS=98; start slave I went back to Node B and execute unlock tables; After that I try to insert new record from Node A and I see Node B is synchronized. But when I try to insert new record from Node B. I see nothing change at Node A. What I missed here? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ndbcluster problem
Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ndbcluster problem
That 108 is the total number of records. This caused by the DataMemory directive in the config and I must increase the value. Willy On Thu, 2009-06-18 at 14:10 -0400, Mike OK wrote: Have you checked the type of column you are using. Depending on what the 108 number means, it could be altering the table to say int or bigint column. If it means total number of records, it does not seem to correspond to a medint value, either signed or not. If it means the record number, your column might have a large start number. Some new companies don't like invoicing starting out at record 1. I have no experience in ndbcluster but I would assume that it has some kind of column limit for performance gains in indexing. Mike O'Krongli Acorg Inc http://www.acorg.com - Original Message - From: sangprabv sangpr...@gmail.com To: mysql@lists.mysql.com Sent: Thursday, June 18, 2009 1:10 PM Subject: ndbcluster problem Is there any record limitation in ndbcluster? Because I can't insert more records after it reached 108 records. How to solve this? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mike_...@acorg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Clusterring, Fail over (High Availability) and Load Balancing With Ubuntu 8.04
Is it possible to do load balancing with 3 servers which also cluster and fail over (High Availibity) with MySQL? Can somebody give me the how to or URL to setup? Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Lookup record with same id and match it's status
Hi, I have a table which stores log traffic. The table contains these fields: transaction_id, from, to, message, status, insertdate For example there is a message from A send to B, when the message sent to B it will insert new record. And when the message is read by B, it will also insert new record. So the records should be something like this: transaction_id, from, to, message, status, insertdate 20081224001, A, B, stest, SENT, 2008-12-24 01:01:01 20081224001, A, B, NULL, READ, 2008-12-24 01:01:03 My question is if I want to lookup 20081224001 and expect the result to be like this: transaction id, from, to, message, sent, received 20081224001, A, B, stest, 2008-12-24 01:01:01, 2008-12-24 01:01:03 How to build the query then? Please help and TIA. Willy Soap and education are not as sudden as a massacre, but they are more deadly in the long run. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Table Lock
Hi, Is that true that MySQL lock table on each query? Does it means will cause slow speed? Then how to avoid table lock and speed up MySQL? TIA. Willy The Public is merely a multiplied me. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query to Select records in the last 4 weeks
Hi, I have tried to use this query: SELECT count(smsc_id) as total, insertdate FROM momtbak WHERE insertdate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE() group by week(date_format(insertdate,'%Y-%m-%d'),3) to group records in the last 4 weeks by week. But the result returns this list: 144 2008-11-06 07:00:24 1883 2008-11-10 07:00:06 1645 2008-11-17 11:59:46 2476 2008-11-24 21:54:11 1015 2008-12-01 20:45:43 The expected result is the date shown above is the weeknumber. What do I miss here? TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED]Re: Query to Select records in the last 4 weeks
Hi, I finally found the solution SELECT count( smsc_id ) AS total, week( insertdate ) AS tanggal FROM momtbak WHERE insertdate BETWEEN DATE_SUB( CURRENT_DATE( ) , INTERVAL 4 WEEK ) AND CURRENT_DATE( ) GROUP BY week( insertdate ) Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query to Select records in the last 4 weeks
Hi, I get stuck to build a query to select records between curdate() and the last 4 weeks and groupped by week. I tested with: SELECT * FROM momtbak WHERE insertdate BETWEEN curdate( ) AND curdate( ) - INTERVAL 4 week It doesn't work. Please help, TIA. Willy Every why hath a wherefore. -- William Shakespeare, A Comedy of Errors -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query to Select records in the last 4 weeks
Hi, Thanks for the prompt reply. I have tested yours and it seems to be working. What about to group the result by week? TIA. Willy Your life would be very empty if you had nothing to regret. -Original Message- From: Daevid Vincent [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: sangprabv [EMAIL PROTECTED] Subject: Re: Query to Select records in the last 4 weeks Date: Wed, 03 Dec 2008 17:52:32 -0800 SELECT * FROM momtbak WHERE insertdate BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK) AND CURRENT_DATE(); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Randomize by Score and DESC
Hi, I have a query like this SELECT * FROM table ORDER BY score DESC, RAND() The aim is to randomize the result by score with descending order. But it doesn't work. What missed here? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Randomize by Score and DESC
Hi, Thans for the reply, I have tried it but I don't see the RAND() to be work. This list is always the same. TIA Willy -Original Message- From: mos [EMAIL PROTECTED] To: sangprabv [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Randomize by Score and DESC Date: Sat, 29 Nov 2008 13:05:09 -0600 Mailer: QUALCOMM Windows Eudora Version 6.0.0.22 At 09:15 AM 11/29/2008, sangprabv wrote: Hi, I have a query like this SELECT * FROM table ORDER BY score DESC, RAND() The aim is to randomize the result by score with descending order. But it doesn't work. What missed here? TIA. Willy Willy, That is because you are ordering by Score then Rand. If Score is an integer, try SELECT * FROM table ORDER BY score+RAND() DESC This will randomize the higher scores first, followed by the lower scores etc.. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Overhead Issue
Hi, I just want to know what things that cause table/db overhead? Because I have my tables always get overhead problem. And must run OPTIMIZE query every morning. Is there any other solution? TIA. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Overhead Issue
Thanks for the reply, does this overhead reduce performance? And is there any tips to avoid this overhead? TIA. WM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Speed Getting Slower
Hi, My MySQL server's data is increasing for about 1 million new records per day. And it's now become slower when processing data. Is there any way to speed up? TIA. Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestion Setting For Highload Server
Hi Mike, I don't understand with the delete the 'deleted' rows. Can you explain me more? And about the RAM, yes we are going to upgrade it. The application opens 1000 simultan connections to service the requests For the tables, we have 2 identical tables, the flow will be like this. First when there is an incoming request application A will store it into the first table, and application A will parse it to redirect it application B to get the response. After responsed, application B will store it into the second table. And application A will always check the second table by searching new rows, if found it will copy it to the first table and push it to the external request as the result. In simple word there will always 3 records for each transaction and these 3 records have the same transaction ID. I heard the lock and unlocking method is only optimized for InnoDB type? Is it right? Regards, Willy -- --- SMS SERVER, SMS BULK, SMS PREMIUM, WEB DESIGN, NETWORKING Contact [EMAIL PROTECTED], 62 811 923 464, 62 21 944 8282 4 http://www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggestion Setting For Highload Server
Hi, I have situation where a MySQL server processes about 10-20 thousands requests per minute. I need suggestions from you for tuning up this server to get optimized setting. TIA Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestion Setting For Highload Server
Hi Mike, Thanks for the reply. 1. Currently the hardware is P4 2.8 on Slackware 12 with 1GB of DDR Memory (we plan to upgrade it) 2. The table type is MyISAM 3. There is no slow query, because all of the queries are a simple type 4. The table's size is increasing dynamically with at least 10 thousands new records / minute 5. Here is the current my.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections = 1000 query_cache_limit = 1M query_cache_size= 16M tmp_table_size = 64M max_heap_table_size = 32M log_slow_queries= /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes log_bin = /var/log/mysql/mysql-bin.log expire_logs_days= 10 max_binlog_size = 100M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M TIA Regards, Willy On Sat, 2008-07-19 at 15:45 -0500, mos wrote: At 12:11 PM 7/19/2008, sangprabv wrote: Hi, I have situation where a MySQL server processes about 10-20 thousands requests per minute. I need suggestions from you for tuning up this server to get optimized setting. TIA Willy Willy, You will need post more information: 1) What type of hardware are you running this on? (OS, Drives, Memory etc.) 2) What table engine are you using (MyISAM or InnoDb?) 3) What do the slow queries look like? Updates? Table joins? Inserts? Simple selects? 4) How large are the tables? 5) What does your My.Cnf file look like? Mike -- --- SMS SERVER, SMS BULK, SMS PREMIUM, WEB DESIGN, NETWORKING Contact [EMAIL PROTECTED], 62 811 923 464, 62 21 944 8282 4 http://www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery error with no result
Hi, Thanks for the reply. The query seems doing something but after minutes of waiting it still return no result. Regards, Willy On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: Would this work for you? SELECT msgdata FROM sent_sms WHERE momt = 'MT' AND binfo IN (SELECT binfo FROM sent_sms WHERE momt = 'DLR') David On 5/21/08 10:30 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, I tried to look for records from a table with this query: SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT binfo FROM sent_sms WHERE momt = 'DLR' ) But MySQL returns this error: #1242 - Subquery returns more than 1 row I tried also with ANY, IN, EXISTS. And modified the query into: SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 WHERE momt = 'MT'. But none works. What I want to view is, all records which has momt = 'MT' and binfo from the same table where has momt = 'DLR' and has the same binfo. TIA Regards, Willy -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: subquery error with no result
Hi, I found the solution for this issue. I separate into 2 queries and put the result onto an array, and do PHP array_intersect_assoc to match. Much more simpler, fast, about 200 thousands record matched in 10 seconds. Many thanks for your helps. Regards, Willy On Thu, 2008-05-22 at 07:40 -0700, Rob Wultsch wrote: Sub queries suck in mysql. It is probably worth while to post the results of: EXPLAIN SELECT msgdata FROM sent_sms WHERE momt = 'MT' AND binfo IN (SELECT binfo FROM sent_sms WHERE momt = 'DLR') Are binfo and momt indexed? If not, they probably should be. A replacement for the sub query should be something like: SELECT s1.msgdata FROM sent_sms AS s1 INNER JOIN sent_sms AS s1 ON s1.binfo =s2.binfo WHERE s1.momt = 'MT' On Thu, May 22, 2008 at 2:05 AM, sangprabv [EMAIL PROTECTED] wrote: Hi, Thanks for the reply. The query seems doing something but after minutes of waiting it still return no result. Regards, Willy On Wed, 2008-05-21 at 23:06 -0400, David Lazo wrote: Would this work for you? SELECT msgdata FROM sent_sms WHERE momt = 'MT' AND binfo IN (SELECT binfo FROM sent_sms WHERE momt = 'DLR') David On 5/21/08 10:30 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, I tried to look for records from a table with this query: SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT binfo FROM sent_sms WHERE momt = 'DLR' ) But MySQL returns this error: #1242 - Subquery returns more than 1 row I tried also with ANY, IN, EXISTS. And modified the query into: SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 WHERE momt = 'MT'. But none works. What I want to view is, all records which has momt = 'MT' and binfo from the same table where has momt = 'DLR' and has the same binfo. TIA Regards, Willy -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subquery error with no result
Hi, I tried to look for records from a table with this query: SELECT msgdata FROM sent_sms WHERE momt = 'MT'AND binfo = ( SELECT binfo FROM sent_sms WHERE momt = 'DLR' ) But MySQL returns this error: #1242 - Subquery returns more than 1 row I tried also with ANY, IN, EXISTS. And modified the query into: SELECT t1.msgdata FROM (SELECT binfo FROM sent_sms WHERE momt = 'DLR') AS t1 WHERE momt = 'MT'. But none works. What I want to view is, all records which has momt = 'MT' and binfo from the same table where has momt = 'DLR' and has the same binfo. TIA Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum insert records per query?
Hi, Thanks for the reply. Now I also manipulate it using PHP script to loop every 1000 of records. Regards, Willy On Tue, 2008-05-20 at 11:48 +0800, Moon's Father wrote: To make the parameter max_allowed_packet larger. On Mon, May 19, 2008 at 12:17 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, Currently in my production environment, mysql only allow max 50.000 records per insert. Is it possible to increase the number of records inserted during insert into query? How to do it? TIA Regards, Willy -- 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 -- [sangprabv] http://www.sangprabv.web.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maximum insert records per query?
Hi, Currently in my production environment, mysql only allow max 50.000 records per insert. Is it possible to increase the number of records inserted during insert into query? How to do it? TIA Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
count from 3 tables
Hi, I have 3 tables which I want to count records from. The tables are users, phonebooks, groups. I want to count how many phonebooks and groups does a user have. Currently I try to use this query: SELECT users.username, count( phonebooks.key_user ) AS pb, count( groups.key_user ) AS gp FROM users LEFT JOIN ( phonebooks, groups ) ON ( phonebooks.key_user = users.key_user AND groups.key_user = users.key_user ) GROUP BY users.username But the result was wrong. Does anybody know why it's wrong and how to solve it? TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL in multi-threaded environment
Hi, What is your table engine? AFAIK it is recommended to use InnoDB than MyISAM. Regards Willy - Original Message - From: Ace To: sangprabv Cc: mysql@lists.mysql.com Sent: Thursday, May 31, 2007 11:48 AM Subject: Re: MySQL in multi-threaded environment Thanks! but I am already using table locks...any other thoughts? Cheers, Rajan On 5/29/07, sangprabv [EMAIL PROTECTED] wrote: Maybe need to lock the table Regards Willy - Original Message - From: Ace [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 30, 2007 12:22 PM Subject: MySQL in multi-threaded environment Hi Friends, I am facing problem while using MySQL in multi-threaded environment. I am using C lang for developement. I maintain MySQL connection pool between threads but with increase in number of requests, it started reporting following errors - == Server Errors: Error: 1064 :Parse error Error: 1062 :Duplicate entry == Then I used mutexes around MySQL API calls and it worked. But seems use of mutex impacts the performance, is this true? Any other solution to this than mutex or any out-of-box solution that might have worked? Thanks for your help!!! -- Cheers, Rajan
Dual Hardisk
Hi, I have a database named X on a hardisk, and the hardisk can not store new record because it has no space anymore. Can I just add a new hardisk and ask MySQL to store new record for database X to the new hardisk? Please share me the knowledge to do it, many thanks for your helps. Regards Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
