MySQL HA on cloud
Hi, I have cloud setup where MySQL servers are installed. Replication is applied in between mysql servers. Now I want to implemet HA for MySQL. Can any one help me how can I achieve MySQL failover? I looked DRBD, Linux Heartbeat, but I am not able to confirm which should I go for or is there anything else by which I can achieve my goal? Thank You. Regards, Yogesh
Query on wait_timeout
Hi, Small doubt for wait_timeout. If my wait_timeout is set for 180 seconds and if any deadlock occures and both query are waiting to execute. What wil happen in that case? 1. Do the connection will wait till deadlock is removed or 2. Connection will close after 180 seconds as both queries are ideal and waiting for each other. Thanks, Yogesh
Re: Can't read dir of '.' (errno: 13)
Check owner of the MySQL data directory. 2011/1/19 Pintér Tibor tib...@tibyke.hu mysql show databases ; ERROR 1018 (HY000): Can't read dir of '.' (errno: 13) mysql show databases; ERROR 1018 (HY000): Can't read dir of '.' (errno: 13) mysql $ perror 13 OS error code 13: Permission denied t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Query is not using Index
Hi,
I am firing following query
SELECT
'Sales' as transaction_type,
CONCAT('$', SUM(CASE DATE(px_orders.sales_orders.order_completed_date)
WHEN CURDATE() THEN px_orders.sales_order_products.paid_amount ELSE 0
END)) AS today,
CONCAT('$', SUM(CASE WEEK(px_orders.sales_orders.order_completed_date,1)
WHEN WEEK( CURRENT_TIMESTAMP(),1) THEN
px_orders.sales_order_products.paid_amount ELSE 0 END)) AS this_week,
CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 7 DAY) ) '7'
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 7 DAY) ) = '0'
,px_orders.sales_order_products.paid_amount,0)))
as week_1,
CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 14 DAY) ) '7'
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 14 DAY) ) = '0'
,px_orders.sales_order_products.paid_amount,0)))
as week_2,
CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 21 DAY) ) '7'
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 21 DAY) ) = '0'
,px_orders.sales_order_products.paid_amount,0)))
as week_3,
CONCAT('$', SUM(if (DATEDIFF(
DATE(px_orders.sales_orders.order_completed_date), DATE_SUB(CURDATE() ,
INTERVAL 28 DAY) ) '7'
DATEDIFF(DATE(px_orders.sales_orders.order_completed_date),
DATE_SUB(CURDATE() , INTERVAL 28 DAY) ) = '0'
,px_orders.sales_order_products.paid_amount,0)))
as week_4,
CONCAT('$', SUM(if (px_orders.sales_orders.order_completed_date =
DATE_SUB(CURDATE( ),INTERVAL DAYOFMONTH(CURDATE( ))-1
DAY),px_orders.sales_order_products.paid_amount,0)))
as mtd,
CONCAT('$', SUM(if (px_orders.sales_orders.order_completed_date =
DATE_SUB(CURDATE( ),INTERVAL DAYOFYEAR(CURDATE( ))-1
DAY),px_orders.sales_order_products.paid_amount,0)))
as ytd
FROM
px_orders.sales_order_products LEFT JOIN px_orders.sales_orders
ON px_orders.sales_order_products.order_id = px_orders.sales_orders.id
WHERE
px_orders.sales_order_products.status IN ( 'COMPLETED', 'CANCELED' )
AND px_orders.sales_orders.affiliate_organisation_id = 265;
By explaining this query I am finding that query is not using index in table
`px_orders.sales_order_products`. It is
Explain Output:-
id select_type table type
possible_keys key key_len ref rows
Extra 1 SIMPLE sales_order_products
ALL fk_op_order_id
159809 Using where 1 SIMPLE sales_orders eq_refPRIMARY PRIMARY
8 px_orders.sales_order_products.order_id 1 Using where
Explain is showing that table `sales_order_products` have possible key
fk_op_order_id but not using the key and examining all the rows from a
table.
order_id from sales_order_products is foreign key to id of sales_orders.
Anyone can tell why this is happening.? Is there a way to optimize this
query?
Thank You.
Re: ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13)
May be db files are not as MySQL user. Check owner ship for the files of the table. It should be mysql user ownership. On Tue, Jan 18, 2011 at 6:36 AM, Peng Yu pengyu...@gmail.com wrote: Hi, I run the following command. But I got the following error. I'm not sure what causes the problem. I have seen the same issue before, but it disappeared even I didn't take any action. Could anybody let me know how to fix the problem? mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A mysql select geneName as symbol, name as refSeq, chrom, strand, txStart, txEnd from refFlat group by refSeq having count(*)=1; ERROR 1 (HY000): Can't create/write to file '/var/lib/mysql/#sql_9e1_0.MYI' (Errcode: 13) -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Re: Rewrite SQL to stop table scan
can you send DDL of the table? On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell bferr...@baywinds.orgwrote: How would you rewrite the following SQL so that is doesn't do a full table scan. It does in fact do a full scan in spite of the time clause. It's been making me nuts for months. select count(*) as count from alerts where (unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) ) and devid = '244'; Thanks in advance, Bruce P.S. I've tried it this way: select count(*) as count from alerts where ((unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) )) and devid = '244'; and explain always says this: +--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++--+---+--+-+--+-+-+ | 1 | SIMPLE | alerts | ALL | NULL | NULL | NULL |NULL | 2041284 | Using where | ++-++--+---+--+-+--+-+-+ And it's structured this way: | Field| Type| Null | Key | Default | Extra | id| varchar(60)| NO | MUL | | | stamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Re: Rewrite SQL to stop table scan
Hey, Try making `id` as primary key. That will keep data in sorted manner and scan will look only the data required in-spite of full table scan. On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell bferr...@baywinds.orgwrote: How would you rewrite the following SQL so that is doesn't do a full table scan. It does in fact do a full scan in spite of the time clause. It's been making me nuts for months. select count(*) as count from alerts where (unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) ) and devid = '244'; Thanks in advance, Bruce P.S. I've tried it this way: select count(*) as count from alerts where ((unix_timestamp(stamp) (unix_timestamp(now()) - '300' ) )) and devid = '244'; and explain always says this: +--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++--+---+--+-+--+-+-+ | 1 | SIMPLE | alerts | ALL | NULL | NULL | NULL |NULL | 2041284 | Using where | ++-++--+---+--+-+--+-+-+ And it's structured this way: | Field| Type| Null | Key | Default | Extra | id| varchar(60)| NO | MUL | | | stamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
Re: Large table
What is the table type for Table? Firstly check with queries and index if required. Check if queries using this table can be fine tuned. Check if table getting locked. If size of table is problem and if the table type is innodb check for innodb_file_per_table options. Also have a look for portioning. On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy kmsram...@gmail.comwrote: All, I have a very large table. It has about 1 billion rows. Initially everything was fine. But now the table is a bit slow. Loaded takes a lot of time. I usually load in chunks of 22 million rows. Is the size of the table any way related to the performance? I am not sure about this. Will splitting the table and having a view from multiple table increase the performance? Thanks in advance. -- Thanks, Sairam Krishnamurthy +1 612 859 8161
Re: export result from select statement
#mysql -username -p -e select * from table_name where id=123 '/home/me/test/test.txt' On Mon, Jan 10, 2011 at 8:15 AM, LAMP l...@afan.net wrote: maybe it's clear to other but it's pretty unclear. #mysql -username -p select * from table_name where id=123 '/home/me/test/test.txt' actually doesn't work?!? Jerry Schwartz wrote: The technique I've settled on is this: mysql blah blah blah the_select_query.sql the_output_i_want.txt That gives you a tab-delimited text file with column headings. 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: LAMP [mailto:l...@afan.net] Sent: Saturday, January 08, 2011 6:05 PM To: mysql@lists.mysql.com Subject: export result from select statement Hi guys, I wonder how to store to csv or txt file result from SELECT query? not a whole table nor database. Just results from SELECT query. Usually I use MySQL Query Browser and Export feature, but in this case I don't have access with MySQL Query Browser. Though, I have a command line access. I found on one place something like #SELECT 2+2 into outfile '/path/to/specific/directory/test.csv'; Though, it doesn't work?!? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
Re: problem open a innodb table
Dont you have mysql dump file for those table? It is best way to restore InnoDB tables. 2011/1/7 Elim PDT e...@pdtnetworks.net I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of openning innodb tables even after I copied the back-up files of few months ago. I don't have many innodb tables and so this not causing too much trouble. I don't know what to do for restoring those tables, and not even know if that is possible or not. Please help. Attached is a table of primes that contains 2+ million primes yet it is so small that I can attached here. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
unauthenticated user | while load testing
Hi, What is unauthenticated user seen in mysql processlist ? I am doing load testing and at a single time more than 1000 users are coming to mysql server. Do any one have idea about it?
