TL;DR
単に
mysql_ssl=1で接続しようとすると転けるmysql_ssl=1;mysql_ssl_verify_server_cert=0なら良いかと思うとSSL connection error: Enforcing SSL encryption is not supported without mysql_ssl_verify_server_certで転けるDBD::mysql が
libmysqlclient.soではなくlibmariadb.soとリンクされているとこうなる
$ ldd $(rpm -ql perl-DBD-MySQL | grep mysql.so)
linux-vdso.so.1 (0x00007fff0e77b000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f23f7a07000)
libmariadb.so.3 => /lib64/libmariadb.so.3 (0x00007f23f77b2000) <---- いた
libperl.so.5.26 => /lib64/libperl.so.5.26 (0x00007f23f73a3000)
libc.so.6 => /lib64/libc.so.6 (0x00007f23f6fcc000)
/lib64/ld-linux-x86-64.so.2 (0x00007f23f7e47000)
libz.so.1 => /lib64/libz.so.1 (0x00007f23f6db4000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f23f6bb0000)
libm.so.6 => /lib64/libm.so.6 (0x00007f23f682e000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007f23f6599000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007f23f60ae000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f23f5e96000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f23f5c6d000)
libutil.so.1 => /lib64/libutil.so.1 (0x00007f23f5a69000)
単に mysql --ssl-mode=required と同じことをしたいだけなのに
$ mysql -h 127.0.0.1 -P 64080 -u yoku0825 --ssl-mode=required -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
Ssl_cipher TLS_AES_128_GCM_SHA256
$ perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:mysql:;host=127.0.0.1;port=64080;mysql_ssl=1", "yoku0825")'
DBI connect(';host=127.0.0.1;port=64080;mysql_ssl=1','yoku0825',...) failed: SSL connection error: self signed certificate in certificate chain at -e line 1.
怒られる。
証明書が自己署名なのはそう（単に mysqld --initialize の時の auto_generate_certs=ON の設定で作られるやつだから）だけど、エラーになってほしくはない。でも mysql_ssl=0 にはしたくない（経路の暗号化はしてほしい）
というか、 mysql コマンドラインクライアントは文句を言わないので DBD::mysql の時だけ何かがおかしい。ということは。
$ rpm -ql perl-DBD-MySQL | grep mysql.so
/usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so
$ ldd /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so
linux-vdso.so.1 (0x00007ffc71bcb000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe5bb2d7000)
libmariadb.so.3 => /lib64/libmariadb.so.3 (0x00007fe5bb082000)
libz.so.1 => /lib64/libz.so.1 (0x00007fe5bae6a000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fe5bac66000)
libm.so.6 => /lib64/libm.so.6 (0x00007fe5ba8e4000)
libssl.so.1.1 => /lib64/libssl.so.1.1 (0x00007fe5ba64f000)
libcrypto.so.1.1 => /lib64/libcrypto.so.1.1 (0x00007fe5ba164000)
libperl.so.5.26 => /lib64/libperl.so.5.26 (0x00007fe5b9d55000)
libc.so.6 => /lib64/libc.so.6 (0x00007fe5b997f000)
/lib64/ld-linux-x86-64.so.2 (0x00007fe5bb717000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fe5b9767000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fe5b953e000)
libutil.so.1 => /lib64/libutil.so.1 (0x00007fe5b933a000)
やっぱりlibmariadbにリンクされてる。
libmysqlclientで試してみるために cpanm --look でビルドしてみる。
$ cpanm --look DBD::mysql
$ perl Makefile.PL
PLEASE NOTE:
For 'make test' to run properly, you must ensure that the
database user 'yoku0825' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.
mysql> CREATE USER 'yoku0825'@'localhost' IDENTIFIED BY 's3kr1t';
mysql> GRANT ALL PRIVILEGES ON test.* TO 'yoku0825'@'localhost';
You can also optionally set the user to run 'make test' with:
perl Makefile.PL --testuser=username
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/include/mysql -m64
ldflags (mysql_config) =
libs (mysql_config) = -L/usr/lib64/mysql -lmysqlclient -lpthread -ldl -lssl -lcrypto -lresolv -lm -lrt
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testport (default ) =
testsocket (default ) =
testuser (guessed ) = yoku0825
version (mysql_config) = 8.4.8
To change these settings, see 'perl Makefile.PL --help' and
'perldoc DBD::mysql::INSTALL'.
Checking if libs are available for compiling...
Looks good.
Checking if your kit is complete...
Looks good
Warning: prerequisite Test::Deep 0 not found.
Using DBI 1.641 (for perl 5.026003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Generating a Unix-style Makefile
Writing Makefile for DBD::mysql
Writing MYMETA.yml and MYMETA.json
$ make
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
Running Mkbootstrap for mysql ()
chmod 644 "mysql.bs"
"/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- mysql.bs blib/arch/auto/DBD/mysql/mysql.bs 644
gcc -c -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/usr/include/mysql -m64 -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -DVERSION=\"5.013\" -DXS_VERSION=\"5.013\" -fPIC "-I/usr/lib64/perl5/CORE" dbdimp.c
"/usr/bin/perl" -p -e "s/~DRIVER~/mysql/g" /usr/lib64/perl5/vendor_perl/auto/DBI/Driver.xst > mysql.xsi
"/usr/bin/perl" "/usr/share/perl5/vendor_perl/ExtUtils/xsubpp" -typemap '/usr/share/perl5/ExtUtils/typemap' mysql.xs > mysql.xsc
Warning: duplicate function definition 'do' detected in mysql.xs, line 142
Warning: duplicate function definition 'rows' detected in mysql.xs, line 550
mv mysql.xsc mysql.c
gcc -c -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/usr/include/mysql -m64 -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -DVERSION=\"5.013\" -DXS_VERSION=\"5.013\" -fPIC "-I/usr/lib64/perl5/CORE" mysql.c
gcc -c -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/usr/include/mysql -m64 -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -DVERSION=\"5.013\" -DXS_VERSION=\"5.013\" -fPIC "-I/usr/lib64/perl5/CORE" socket.c
rm -f blib/arch/auto/DBD/mysql/mysql.so
gcc -lpthread -shared -Wl,-z,relro -Wl,-z,now -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -L/usr/local/lib -fstack-protector-strong dbdimp.o mysql.o socket.o -o blib/arch/auto/DBD/mysql/mysql.so \
-L/usr/lib64/mysql -lmysqlclient -lpthread -ldl -lssl -lcrypto -lresolv -lm -lrt -lperl \
chmod 755 blib/arch/auto/DBD/mysql/mysql.so
Manifying 2 pod documents
$ exit
$ find -name mysql.so
./.cpanm/work/1769741347.2091827/DBD-mysql-5.013/blib/arch/auto/DBD/mysql/mysql.so
LD_PRELOAD で .cpanm にできた mysql.so の方を使う形にして試してみる。
$ LD_PRELOAD=./.cpanm/work/1769741347.2091827/DBD-mysql-5.013/blib/arch/auto/DBD/mysql/mysql.so perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:mysql:;host=127.0.0.1;port=64080;mysql_ssl=1", "yoku0825")'
できた…
DBD::MySQLのバージョンのせいだと嫌なので念のため mariadb-connector-c-devel を入れ直して試してみる。
$ perl Makefile.PL
PLEASE NOTE:
For 'make test' to run properly, you must ensure that the
database user 'yoku0825' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.
mysql> CREATE USER 'yoku0825'@'localhost' IDENTIFIED BY 's3kr1t';
mysql> GRANT ALL PRIVILEGES ON test.* TO 'yoku0825'@'localhost';
You can also optionally set the user to run 'make test' with:
perl Makefile.PL --testuser=username
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/include/mysql -I/usr/include/mysql/mysql
ldflags (mysql_config) =
libs (mysql_config) = -L/usr/lib64/ -lmariadb
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testport (default ) =
testsocket (default ) =
testuser (guessed ) = yoku0825
version (mysql_config) = 10.5.5
To change these settings, see 'perl Makefile.PL --help' and
'perldoc DBD::mysql::INSTALL'.
Checking if libs are available for compiling...
The chosen MySQL client library appears to be MariaDB's. Compilation may fail.
Consider DBD::MariaDB or installing Oracle's MySQL client library.
Checking if your kit is complete...
Looks good
Using DBI 1.641 (for perl 5.026003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Generating a Unix-style Makefile
Writing Makefile for DBD::mysql
Writing MYMETA.yml and MYMETA.json
$ make
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
Running Mkbootstrap for mysql ()
chmod 644 "mysql.bs"
"/usr/bin/perl" -MExtUtils::Command::MM -e 'cp_nonempty' -- mysql.bs blib/arch/auto/DBD/mysql/mysql.bs 644
gcc -c -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/usr/include/mysql -I/usr/include/mysql/mysql -g -D_REENTRANT -D_GNU_SOURCE -O2 -g -pipe -Wall -Werror=format-security -Wp,-D_FORTIFY_SOURCE=2 -Wp,-D_GLIBCXX_ASSERTIONS -fexceptions -fstack-protector-strong -grecord-gcc-switches -specs=/usr/lib/rpm/redhat/redhat-hardened-cc1 -specs=/usr/lib/rpm/redhat/redhat-annobin-cc1 -m64 -mtune=generic -fasynchronous-unwind-tables -fstack-clash-protection -fcf-protection -fwrapv -fno-strict-aliasing -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -g -DVERSION=\"5.013\" -DXS_VERSION=\"5.013\" -fPIC "-I/usr/lib64/perl5/CORE" dbdimp.c
dbdimp.c: In function 'parse_params':
dbdimp.c:639:22: warning: implicit declaration of function 'mysql_real_escape_string_quote'; did you mean 'mysql_real_escape_string'? [-Wimplicit-function-declaration]
ptr += mysql_real_escape_string_quote(sock, ptr, valbuf, vallen, '\'');
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql_real_escape_string
dbdimp.c: In function 'mysql_dr_connect':
dbdimp.c:1244:31: error: 'MYSQL_OPT_COMPRESSION_ALGORITHMS' undeclared (first use in this function); did you mean 'MYSQL_OPT_COMPRESS'?
mysql_options(sock, MYSQL_OPT_COMPRESSION_ALGORITHMS, calg);
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MYSQL_OPT_COMPRESS
dbdimp.c:1244:31: note: each undeclared identifier is reported only once for each function it appears in
dbdimp.c:1415:31: error: 'MYSQL_OPT_GET_SERVER_PUBLIC_KEY' undeclared (first use in this function); did you mean 'MYSQL_SERVER_PUBLIC_KEY'?
mysql_options(sock, MYSQL_OPT_GET_SERVER_PUBLIC_KEY, &server_get_pubkey);
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MYSQL_SERVER_PUBLIC_KEY
dbdimp.c:1479:22: error: 'SSL_MODE_PREFERRED' undeclared (first use in this function); did you mean 'SO_PEERCRED'?
ssl_mode = SSL_MODE_PREFERRED;
^~~~~~~~~~~~~~~~~~
SO_PEERCRED
dbdimp.c:1481:19: error: 'SSL_MODE_VERIFY_IDENTITY' undeclared (first use in this function)
ssl_mode = SSL_MODE_VERIFY_IDENTITY;
^~~~~~~~~~~~~~~~~~~~~~~~
dbdimp.c:1483:19: error: 'SSL_MODE_VERIFY_CA' undeclared (first use in this function)
ssl_mode = SSL_MODE_VERIFY_CA;
^~~~~~~~~~~~~~~~~~
dbdimp.c:1485:19: error: 'SSL_MODE_REQUIRED' undeclared (first use in this function); did you mean 'OP_REQUIRE'?
ssl_mode = SSL_MODE_REQUIRED;
^~~~~~~~~~~~~~~~~
OP_REQUIRE
dbdimp.c:1486:30: error: 'MYSQL_OPT_SSL_MODE' undeclared (first use in this function); did you mean 'MYSQL_OPT_SSL_CRL'?
if (mysql_options(sock, MYSQL_OPT_SSL_MODE, &ssl_mode) != 0) {
^~~~~~~~~~~~~~~~~~
MYSQL_OPT_SSL_CRL
dbdimp.c:1495:30: error: 'SSL_MODE_DISABLED' undeclared (first use in this function); did you mean 'SS_DISABLE'?
unsigned int ssl_mode = SSL_MODE_DISABLED;
^~~~~~~~~~~~~~~~~
SS_DISABLE
dbdimp.c: In function 'mysql_st_prepare':
dbdimp.c:2419:24: warning: assignment to 'my_bool *' {aka 'char *'} from incompatible pointer type '_Bool *' [-Wincompatible-pointer-types]
bind->is_null= &(fbind->is_null);
^
dbdimp.c: In function 'mysql_st_internal_execute41':
dbdimp.c:2856:9: warning: implicit declaration of function 'mysql_stmt_bind_named_param'; did you mean 'mysql_stmt_bind_param'? [-Wimplicit-function-declaration]
if (mysql_stmt_bind_named_param(stmt,bind,num_params, NULL))
^~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql_stmt_bind_param
dbdimp.c:2915:63: warning: format '%lu' expects argument of type 'long unsigned int', but argument 3 has type 'my_ulonglong' {aka 'long long unsigned int'} [-Wformat=]
"\t<- mysql_internal_execute_41 returning %lu rows\n",
~~^
%llu
rows);
~~~~
dbdimp.c: In function 'mysql_st_execute':
dbdimp.c:3076:32: warning: format '%lu' expects argument of type 'long unsigned int', but argument 3 has type 'my_ulonglong' {aka 'long long unsigned int'} [-Wformat=]
sprintf(actual_row_num, "%lu", imp_sth->row_num);
~~^ ~~~~~~~~~~~~~~~~
%llu
dbdimp.c: In function 'mysql_describe':
dbdimp.c:3170:22: warning: assignment to 'my_bool *' {aka 'char *'} from incompatible pointer type '_Bool *' [-Wincompatible-pointer-types]
buffer->is_null= &(fbh->is_null);
^
dbdimp.c:3171:20: warning: assignment to 'my_bool *' {aka 'char *'} from incompatible pointer type '_Bool *' [-Wincompatible-pointer-types]
buffer->error= (bool*) &(fbh->error);
^
dbdimp.c: In function 'mysql_st_fetch':
dbdimp.c:3475:63: warning: format '%lu' expects argument of type 'long unsigned int', but argument 3 has type 'my_ulonglong' {aka 'long long unsigned int'} [-Wformat=]
PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\tmysql_num_rows=%lu\n",
~~^
%llu
mysql_num_rows(imp_sth->result));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbdimp.c:3477:68: warning: format '%lu' expects argument of type 'long unsigned int', but argument 3 has type 'my_ulonglong' {aka 'long long unsigned int'} [-Wformat=]
PerlIO_printf(DBIc_LOGPIO(imp_xxh), "\tmysql_affected_rows=%lu\n",
~~^
%llu
mysql_affected_rows(imp_dbh->pmysql));
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbdimp.c: In function 'mysql_st_FETCH_attrib':
dbdimp.c:4081:60: warning: format '%lu' expects argument of type 'long unsigned int', but argument 3 has type 'my_ulonglong' {aka 'long long unsigned int'} [-Wformat=]
PerlIO_printf(DBIc_LOGPIO(imp_xxh), "INSERT ID %lu\n", imp_sth->insertid);
~~^ ~~~~~~~~~~~~~~~~~
%llu
make: *** [Makefile:352: dbdimp.o] Error 1
あっ make転けた…
じゃあDBD::MariaDB（ちゃんと存在するのだ）で試す。
$ sudo cpanm DBD::MariaDB
..
$ perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:MariaDB:;host=127.0.0.1;port=64080;mysql_ssl=1", "yoku0825")'
DBI connect(';host=127.0.0.1;port=64080;mysql_ssl=1','yoku0825',...) failed: Unknown attribute mysql_ssl at -e line 1.
今度は mysql_ssl なんて知らんと言われた… mariadb_ssl らしいんだけど、別にここはコンパチってわけではないのか。
$ perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:MariaDB:;host=127.0.0.1;port=64080;mariadb_ssl=1", "yoku0825")'
DBI connect(';host=127.0.0.1;port=64080;mariadb_ssl=1','yoku0825',...) failed: SSL connection error: self signed certificate in certificate chain at -e line 1.
うむ、やはりlibmariadbに依存しそう。
ただ、これがどこのコードから派生しているのかよくわからん…。
大体にして、そもそも mariadb コマンドラインクライアントは
$ ./client/mariadb -h 127.0.0.1 -P 64080 -u yoku0825 --ssl -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
Ssl_cipher TLS_AES_256_GCM_SHA384
ちゃんとつながるんである！mariadb コマンドラインクライアントが SSL connection error: self signed certificate で転けるのは --ssl-verify-server-cert をつけた時だけ。
$ ./client/mariadb -h 127.0.0.1 -P 64080 -u yoku0825 --ssl --ssl-verify-server-cert -sse "SHOW SESSION STATUS LIKE 'Ssl_cipher'"
ERROR 2026 (HY000): TLS/SSL error: self signed certificate in certificate chain
じゃあDBD::mysqlも mysql_ssl_verify_server_cert=0 してやればいいかと思うと
$ perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:mysql:;host=127.0.0.1;port=64080;mysql_ssl=1;mysql_ssl_verify_server_cert=0", "yoku0825")'
DBI connect(';host=127.0.0.1;port=64080;mysql_ssl=1;mysql_ssl_verify_server_cert=0','yoku0825',...) failed: SSL connection error: Enforcing SSL encryption is not supported without mysql_ssl_verify_server_cert at -e line 1.
今度は Enforcing SSL encryption is not supported without mysql_ssl_verify_server_cert で転けるのである…。
libmysqlclientにリンクしたDBD::mysqlならこっちも文句は言わない。
$ LD_PRELOAD=~/.cpanm/work/1769741347.2091827/DBD-mysql-5.013/blib/arch/auto/DBD/mysql/mysql.so perl -MDBI -MData::Dumper -E 'my $conn= DBI->connect("dbi:mysql:;host=127.0.0.1;port=64080;mysql_ssl=1;mysql_ssl_verify_server_cert=0", "yoku0825")'
わからない…。
