Archive

Archive for September, 2012

默认临时目录带来的权限问题

September 29th, 2012 No comments

一客户的本地数据库发现不能对其中的表进行查询,无法进行任何操作,联系我进行排查:
登录数据库:

[root@localhost tmp]# mysql -u root -pxxxxx
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 2
Server version: 5.0.77 SOURCE distribution
 
TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the buffer.
 
mysql> USE customperformance2;
DATABASE changed
mysql> SELECT COUNT(*) FROM erp_user;
ERROR 1033 (HY000): Incorrect information IN file: '.\customperformance2\erp_user.frm'
mysql> DESC erp_user;         
ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_13b3_0.MYI' (Errcode: 13)
$perror 13
OS error code  13:  Permission denied

为什么会出现这样的错误,为什么会报没有权限的[……]

Read more

Categories: database Tags:

浅谈mysql的子查询

September 22nd, 2012 No comments

mysql的子查询的优化一直不是很友好,一直有受业界批评比较多,也是我在sql优化中遇到过最多的问题之一,你可以点击这里 ,这里来获得一些信息,mysql在处理子查询的时候,会将子查询改写,通常情况下,我们希望由内到外,也就是先完成子查询的结果,然后在用子查询来驱动外查询的表,完成查询,但是恰恰相反[……]

Read more

Categories: database, sql优化 Tags:

Waiting Auto-INC LOCK导致死锁

September 2nd, 2012 No comments

今天下午在看死锁相关的文档,到线上查看一生产数据库的时候,正好发现了show engine innodb status有一个死锁的信息:

LATEST DETECTED DEADLOCK
------------------------
120626 20:00:30
*** (1) TRANSACTION:
TRANSACTION 3 3052385643, ACTIVE 0 sec, process no 3898, OS thread id 1356507456 inserting
mysql TABLES IN USE 1, locked 1
LOCK WAIT 16 LOCK struct(s), heap SIZE 3024, undo log entries 56
MySQL thread id 32282264, query id 9170497209 172.24.52.77 product_db UPDATE
INSERT INTO                     occur_dead_lock_table(xx_id1,xx_id2,xx_cloumn1,STATUS,quantity,xxx_id3,price,gmt_create,gmt_modified) VALUES(19273026495,10378,0x313632373230373A36303039323B32303530333A33323637393435,1,1902,723417070,5600,now(),now())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS SPACE id 879 page no 241 n bits 808 INDEX `ind_occur_dead_lock_table` OF TABLE `product_db/occur_dead_lock_table` trx id 3 3052385643 lock_m
ode X locks gap BEFORE rec INSERT intention waiting
Record LOCK, heap no 291 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 8; hex 800000000000288b; ASC       ( ;; 1: len 8; hex 80000000000c400d; ASC       @ ;;
 
*** (2) TRANSACTION:
TRANSACTION 3 3052385652, ACTIVE 0 sec, process no 3898, OS thread id 1663498560 setting auto-inc LOCK
mysql TABLES IN USE 1, locked 1
15 LOCK struct(s), heap SIZE 3024, undo log entries 43
MySQL thread id 32231290, query id 9170497216 172.24.36.165 product_db UPDATE
INSERT INTO         occur_dead_lock_table(xx_id1,xx_id2,xx_cloumn1,STATUS,quantity,xxx_id3,price,gmt_create,gmt_modified) VALUES(19400057961,10379,0x313632373230373A333233323438333B32303530333A3235303333333336,1,19713,723417070,5600,now(),now())
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS SPACE id 879 page no 241 n bits 808 INDEX `ind_occur_dead_lock_table` OF TABLE `product_db/occur_dead_lock_table` trx id 3 3052385652 lock_m
ode X
Record LOCK, heap no 251 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 8; hex 800000000000288b; ASC       ( ;; 1: len 8; hex 80000000000c4028; ASC       @(;;
......省略部分内容。。。。。
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK TABLE `product_db/occur_dead_lock_table` trx id 3 3052385652 LOCK mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)

我们首先来分析一下LATEST DETECTED DEADLOCK的信息中,出现死[……]

Read more

Categories: database Tags: