Skip to content

Instantly share code, notes, and snippets.

@yoku0825
yoku0825 / 8025.txt
Created Aug 5, 2021
8.0.25 vs 8.0.26 : SET SESSION innodb_strict_mode = OFF
View 8025.txt
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+--------------------------------------+
@yoku0825
yoku0825 / gist:1add18be35013e9c2c105c1f1a518fd1
Created Jul 29, 2021
innodb_parallel_read_threads=4でparallel_readで何本か止めておいてからcontinue
View gist:1add18be35013e9c2c105c1f1a518fd1
Breakpoint 1, Parallel_reader::parallel_read() () at /home/yoku0825/mysql-8.0.26/storage/innobase/row/row0pread.cc:1087
1087 void Parallel_reader::parallel_read() {
(gdb) c
+c
Continuing.
[New Thread 0x7f2972ffd700 (LWP 1916)]
[New Thread 0x7f2971ffb700 (LWP 1917)]
[New Thread 0x7f2998ff9700 (LWP 1914)]
[New Thread 0x7f296b7fe700 (LWP 1920)]
[New Thread 0x7f2970ff9700 (LWP 1918)]
@yoku0825
yoku0825 / gist:996c2d1319506a2959c0c5d4948abd0f
Created Jun 8, 2021
2038年1月19日3時14分8秒 UTCを過ぎると、次のSQLをパースしたところでmysqldが落ちる
View gist:996c2d1319506a2959c0c5d4948abd0f
2038-01-19T03:14:16.025479Z 8 [Warning] [MY-010112] [Server] Current time has got past year 2038. Validating current time with 5 iterations before initiating the normal server shutdown process.
2038-01-19T03:14:16.025550Z 8 [Warning] [MY-010114] [Server] Iteration 1: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025561Z 8 [Warning] [MY-010114] [Server] Iteration 2: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025569Z 8 [Warning] [MY-010114] [Server] Iteration 3: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025575Z 8 [Warning] [MY-010114] [Server] Iteration 4: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025582Z 8 [Warning] [MY-010114] [Server] Iteration 5: Current time obtained from system is greater than 2038
2038-01-19T03:14:16.025590Z 8 [ERROR] [MY-010115] [Server] This MySQL server doesn't support dates later then 2038
2038-01-19T03:14:17.178509Z 0 [System] [MY-010910] [Server] /usr/sbin/
@yoku0825
yoku0825 / please_clause_for_5651.patch
Created Apr 1, 2021
MySQL 5.6.51にPLEASE句を実装するパッチ
View please_clause_for_5651.patch
--- ./sql/sql_yacc.yy.orig 2021-01-05 19:19:59.000000000 +0900
+++ ./sql/sql_yacc.yy 2021-04-01 11:52:06.167953286 +0900
@@ -2,2 +2,3 @@
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
+ Copyright (c) 2021, yoku0825. All rights reserved.
@@ -1570,2 +1571,3 @@
%token SSL_SYM
+%token PLEASE_SYM /* yoku0825 */
%token STARTING
@yoku0825
yoku0825 / sql_authentication.patch
Created Feb 24, 2021
REPLICATION SLAVE権限があったらoffline_modeでも切らないパッチ
View sql_authentication.patch
--- sql/auth/sql_authentication.cc.orig 2020-12-10 12:01:55.000000000 +0900
+++ sql/auth/sql_authentication.cc 2021-02-24 22:46:58.821476338 +0900
@@ -2401,3 +2401,3 @@
- if (!(sctx->check_access(SUPER_ACL)) && !thd->is_error())
+ if (!(sctx->check_access(SUPER_ACL) || sctx->check_access(REPL_SLAVE_ACL)) && !thd->is_error())
{
@yoku0825
yoku0825 / gist:0c1f69e7c7d3fbd73ca588a50994b1c8
Created Aug 24, 2020
イメージ名の後に `mysqld --lower-case-table-names=1` を渡せば良さそうです
View gist:0c1f69e7c7d3fbd73ca588a50994b1c8
### オプションなしで起動(デフォルトの lower_case_table_names = 0)
$ sudo docker run -d -P -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -e MYSQL_ROOT_PASSWORD="""" -e MYSQL_ROOT_HOST=""%"" mysql/mysql-server:5.6
$ mysql -h172.17.0.3
mysql> create database d1;
Query OK, 1 row affected (0.01 sec)
mysql> use d1
Database changed
@yoku0825
yoku0825 / cmake-log
Created Aug 12, 2020
makeでターゲット絞ると全体をビルドするよりは速くなりました
View cmake-log
[yoku0825@150-95-141-50 work]$ time cmake3 -DWITH_BOOST=../mysql-8.0.21/boost ../mysql-8.0.21
-- Running cmake version 3.17.3
-- Found Git: /usr/bin/git (found version "1.8.3.1")
-- This is el6, el7 or el8 as found from 'uname -r' or 'rpm -qf /'
-- We probably need some devtoolset compiler
-- Using /opt/rh/devtoolset-9/root/usr/bin/gcc
-- Using /opt/rh/devtoolset-9/root/usr/bin/g++
-- MySQL 8.0.21
-- The C compiler identification is GNU 9.3.1
-- The CXX compiler identification is GNU 9.3.1
@yoku0825
yoku0825 / gist:913d143d28c19b24d424a79517c1a3fc
Created Jul 25, 2020
--help --verboseよりも先に--plugin-load処理するんですね…
View gist:913d143d28c19b24d424a79517c1a3fc
$ docker run --entrypoint="/usr/sbin/mysqld" mysql/mysql-server:8.0 --plugin-load=group_replication.so --help --verbose | grep group
The following groups are read: mysqld server mysqld-8.0
--defaults-group-suffix=#
Also read groups with concat(group, suffix)
--binlog-group-commit-sync-delay=#
binary log group commit sync queue to fill before
--binlog-group-commit-sync-no-delay-count=#
--binlog-group-commit-sync-delay), the commit procedure
The maximum time that the binary log group commit will
bytes. Rows will be grouped into events smaller than this
@yoku0825
yoku0825 / gist:8b65dd49b63b3658fbf79e058221fe6f
Created Jun 19, 2020
lsofで見たmmaped TempTableストレージエンジン
View gist:8b65dd49b63b3658fbf79e058221fe6f
mysqld 6330 mysql 27u REG 0,38 1048584 2240585 /tmp/mysql_temptable.e5TG4F (deleted)
mysqld 6330 mysql 28u REG 0,38 2097160 2240604 /tmp/mysql_temptable.fz9ETI (deleted)
mysqld 6330 mysql 29u REG 0,38 2097160 2240605 /tmp/mysql_temptable.MRogLL (deleted)
mysqld 6330 mysql 30u REG 0,38 4194312 2240606 /tmp/mysql_temptable.DkB4GO (deleted)
mysqld 6330 mysql 31u REG 0,38 4194312 2240607 /tmp/mysql_temptable.OlwnMR (deleted)
mysqld 6330 mysql 32u REG 0,38 8388616 2240608 /tmp/mysql_temptable.F7hg4U (deleted)
mysqld 6330 mysql 33u REG 0,38 8388616 2240609 /tmp/mysql_temptable.a9KQUZ (deleted)
mysqld 6330 mysql 34u REG 0,38 16777224 2240610 /tmp/mysql_temptable.25rpe5 (deleted)
mysqld 6330 mysql 35u REG 0,38 16777224 2240602 /tmp/mysql_temptable.3djHti (deleted)
mysqld 6330 mysql 36u REG 0,38 33554440 2240603 /tmp/mysql_temptable.PKmzMw (deleted)
View gist:b5e1244fda9a8b8f70cf5dcc9a8f2309
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary> (actual time=0.001..0.016 rows=62 loops=1)
-> Temporary table with deduplication (actual time=99911.386..99911.423 rows=62 loops=1)
-> Filter: (is_metrics_list_analyze_90.`name` in ('lock_deadlocks','lock_row_lock_time_avg')) (actual time=99790.494..99908.201 rows=1178 loops=1)
-> Table scan on is_metrics_list_analyze_90 (actual time=0.002..40.599 rows=174298 loops=1)
-> Materialize (actual time=99788.102..99874.969 rows=174298 loops=1)
-> Window aggregate with buffering: first_value(daily_is_metrics_list.avg_count) OVER w_all, last_value(daily_is_metrics_list.avg_count) OVER w_all (actual time=98284.457..99478.783 rows=174298 loops=1)
-> Table scan on <temporary> (actual time=0.002..51.313 rows=174298 loops=1)
-> Temporary table (actual time=98283.337..98386.183 rows=174298 loops=1)