Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts
0
Posted on Tuesday, August 21, 2018 by 醉·醉·鱼 and labeled under , ,
DatabaseCleaner上面的example过时了。按照示例去做的话,会报错 unknown method 'before'。在github上找到了答案,还是比较简单的。


class ActiveSupport::TestCase
  include FactoryGirl::Syntax::Methods

  ActiveRecord::Migration.check_pending!
  DatabaseCleaner.strategy = :truncation
  DatabaseCleaner.logger = Rails.logger
  setup { DatabaseCleaner.start }
  teardown { DatabaseCleaner.clean }
end

配置完成以后,就可以在MYSQL去monitor query,看具体是如何操作数据库的。

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

观察log

tail -f -n300 /var/run/mysqld/mysqld.log

最后,重置改动。

mysql> SET GLOBAL general_log = 'OFF';

  1. https://stackoverflow.com/questions/568564/how-can-i-view-live-mysql-queries
  2. https://github.com/metaskills/minitest-spec-rails/issues/44#issuecomment-244155657
0
Posted on Monday, January 22, 2018 by 醉·醉·鱼 and labeled under
之前分析过SQL SERVER的死锁,但基本都是基于READ COMMITTED下的死锁。玩得高级点的,就是key lookup lock。最近不幸玩了MySQL,拿原来的理解去尝试分析,结果不对,然后才发现,MySQL的默认隔离级别是REPEATABLE READ。呵呵~

在RR级别下,除了常规的RECORD LOCK,还有一个GAP LOCK。即两条记录之前的间隙。这样的话,就不会允许在范围内插入数据了。http://blog.csdn.net/wanghai__/article/details/7067118 这里有个很好的例子去模拟死锁。

至于分析锁,首先执行
set global innodb_status_output_locks=on;

然后,再执行
SHOW ENGINE INNODB STATUS \G
就可以拿到所有session的锁了。

-- session 1
mysql> start transaction;
mysql> delete from game_summaries where game_id = 2;

-- session 2
mysql> start transaction;
mysql> delete from game_summaries where game_id = 3;

-- session 1
mysql> insert into game_summaries(game_id, score) values (2, 0);
-- waiting

-- session 2
mysql> insert into game_summaries(game_id, score) values(3, 0);
-- deadlock occurs


Deadlock info

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-11 02:19:51 0x7ff5b7b83700
*** (1) TRANSACTION:
TRANSACTION 365986, ACTIVE 59 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140693325752064, query id 1184 172.18.0.1 root update
insert into game_summaries(game_id, score) values (2, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365986 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 365987, ACTIVE 45 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17, OS thread handle 140693326018304, query id 1186 172.18.0.1 root update
insert into game_summaries(game_id, score) values(3, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (2)


然后就按照下面两篇文章去分析锁就行了。


  1. http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/
  2. http://keithlan.github.io/2017/06/05/innodb_locks_1/
如果你有SQL SERVER的背景知识,简单来说,就是基本的record lock(以及相关的index),加上gap lock。一旦有gap lock,这个范围内是不允许插入数据的。这就增加了死锁发生的几率。这种情况更多是发生在DELETE & INSERT 组合情况下。
在上面的例子里面,两个delete statement所加的gap lock是不会相互冲突的。但是会阻止后续的插入。

0
Posted on Tuesday, October 25, 2011 by 醉·醉·鱼 and labeled under ,

安装mysql的命令太多,记下以备后用,免得再去寻找。

      1. 安装
        sudo apt-get install mysql-server
        安装完成以后,默认会自动启动mysql。如果没有启动mysql,继续下面的方法。
      2. 启动,停止,重启
        sudo start mysql
        sudo stop mysql
        sudo restart mysql
      3. 登陆
        mysql –h hostname –u username –p
        hostname为远程机器IP,如果是本机,直接mysql –u username -p即可。
      4. 更改权限/创建新用户
        grant all privileges on *.* to ‘username’@'%’ indentified by ‘password’ with grant option;
        更新username为所有的权限,%是指该用户可以在所有的ipaddress上面通过password访问mysql。
        如果该用户名不存在,则会创建一个新的用户。
        如果希望用户在指定机器上访问,则将%替换为改机器的address。
      5. 提交更新
        flush privileges;
      6. 开放远程登陆
        默认情况下,mysql只允许本机访问,需要修改/etc/mysql/my.cnf文件,将bind-address = 127.0.0.1 注释掉。同时,还需要确认该用户有远程登陆的权限,即#4里面,@后面需要使用%。
        重启mysql。
      7. 操作数据库 > 显示数据库列表
        show databases;
        image
      8. 操作数据库 > 显示数据表
        use mysql;
        show tables;
        image
      9. 操作数据库 > 显示数据库结构
        describe {table name};
      10. 操作数据库 > 建库与删库
        create database {database name};
        drop database {database name};
      11. 操作数据库 > 建表
        use database;
        create table {table name};
        drop table {table name};
      12. 操作数据库 > 删除,更新,插入,查询