読者です 読者をやめる 読者になる 読者になる

カメニッキ

カメとインコと釣りの人です

perlのDBIでset sessionした値が効いてなくて困ってた

perlからMySQLにGrant発行するときに、条件に応じてset sessionしたかった。

        $dh = Data::ObjectDriver::Driver::DBI->new(
            dsn             => 'dbi:mysql:mysql:127.0.0.1',
            username        => root,
            password        => password,
            reuse_dbh       => 1,
            connect_options => {
                 mysql_auto_reconnect => 1,
                 Callbacks => {
                   connected => sub {
                     shift->do(q{
                         SET SESSION old_passwords=0;
                     });
                     return;
                   },
                },
            },
        );

        $dh = $dh->rw_handle;
        my $sql = "GRANT ALL PRIVILEGES ON `dbname`.* TO `hoge`@`fuga` IDENTIFIED BY \"pass\";";
        $dh->do($sql);

みたいなコード書いてるのに、old_password=0が効いてなくてあれ?ってなってた。 tcpdumpで眺めてたら再接続されているっぽい

15:35:25.472990 IP 192.168.0.1.45833 > 192.168.0.2.mysql: Flags [P.], seq 109:188, ack 98, win 46, options [nop,nop,TS val 2930090828 ecr 853280486], length 79

                        SET SESSION old_passwords=0;

15:35:25.473281 IP 192.168.0.1.45833 > 192.168.0.2.mysql: Flags [P.], seq 188:193, ack 109, win 46, options [nop,nop,TS val 2930090829 ecr 853280486], length 5
15:35:25.473493 IP 192.168.0.1.45833 > 192.168.0.2.mysql: Flags [P.], seq 193:198, ack 120, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 5
15:35:25.473499 IP 192.168.0.1.45833 > 192.168.0.2.mysql: Flags [F.], seq 198, ack 120, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 0
15:35:25.473638 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [S], seq 174310667, win 5840, options [mss 1460,sackOK,TS val 2930090829 ecr 0,nop,wscale 7], length 0
15:35:25.473669 IP 192.168.0.1.45833 > 192.168.0.2.mysql: Flags [.], ack 121, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 0
15:35:25.473808 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [.], ack 160687258, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 0
15:35:25.474039 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [.], ack 71, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 0
15:35:25.474077 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [P.], seq 0:75, ack 71, win 46, options [nop,nop,TS val 2930090829 ecr 853280487], length 75
15:35:25.474361 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [P.], seq 75:88, ack 76, win 46, options [nop,nop,TS val 2930090830 ecr 853280488], length 13
15:35:25.474624 IP 192.168.0.1.45834 > 192.168.0.2.mysql: Flags [P.], seq 88:223, ack 87, win 46, options [nop,nop,TS val 2930090830 ecr 853280488], length 135
E...fK@.@.....iV..h..
..
c.d ..............
...N2........GRANT ALL PRIVILEGES ON `dbname`.* TO `user`@`host` IDENTIFIED BY "aaaaa";

DBI - search.cpan.org この辺みて、connected時にset sessionしてあげるのが良い方法だと思ってたけど、どうやら DBD::mysqlのmysql_auto_reconnectが真だとDB再接続時にDBICのon_connect_doが実行されない件 - Unknown::Programming なのかな?10年前に似たようにハマった人がいて嬉しいきもちになった