Skip to content

Instantly share code, notes, and snippets.

View yoku0825's full-sized avatar

yoku0825 yoku0825

49 followers · 29 following
View GitHub Profile
@yoku0825
yoku0825 / gist:8a1e2b43e80f8ce1b82f41507b28918e
Created January 17, 2022 09:25
general_logをコネクションIDごとに別ファイルに分けるマン(2100年になると使えなくなる
#!/usr/bin/perl
use strict;
use warnings;
use utf8;
my $file= "./general_query.log";
open(my $fh, "<", $file) or die;
@yoku0825
yoku0825 / E8919BF3A08481
Created December 15, 2021 01:15
mysql80 26> SELECT x'E8919BF3A08481', LENGTH(x'E8919BF3A08481'), CHARACTER_LENGTH(x'E8919BF3A08481');
+-------------------+---------------------------+-------------------------------------+
| x'E8919BF3A08481' | LENGTH(x'E8919BF3A08481') | CHARACTER_LENGTH(x'E8919BF3A08481') |
+-------------------+---------------------------+-------------------------------------+
| 葛󠄁 | 7 | 7 |
+-------------------+---------------------------+-------------------------------------+
1 row in set (0.00 sec)
@yoku0825
yoku0825 / gist:af3aef5572a9240dfc725d182c515ac7
Created November 15, 2021 09:04
innodb_file_per_tableを途中で跨がせる
$ ll /usr/mysql/5.7.36/data/d1/
total 220
-rw-r----- 1 yoku0825 yoku0825 67 Nov 15 17:50 db.opt
-rw-r----- 1 yoku0825 yoku0825 8586 Nov 15 17:57 t1.frm
-rw-r----- 1 yoku0825 yoku0825 98304 Nov 15 17:57 t1.ibd <--- innodb_file_per_table= ON
-rw-r----- 1 yoku0825 yoku0825 8586 Nov 15 17:56 t3.frm
-rw-r----- 1 yoku0825 yoku0825 98304 Nov 15 17:56 t3.ibd
mysql57 11> SET GLOBAL innodb_file_per_table= 0;
@yoku0825
yoku0825 / left_join_on_where.php
Created October 4, 2021 12:15
https://twitter.com/mamy1326/status/1444988083426889728
<?php
function t2_in_1_3($buff)
{
if ($buff["t2"] == 1 || $buff["t2"] == 3)
return true;
return false;
}
$t1= array(1, 2, 3);
@yoku0825
yoku0825 / 8025.txt
Created August 5, 2021 06:42
8.0.25 vs 8.0.26 : SET SESSION innodb_strict_mode = OFF
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS;
+--------------------------------------+
@yoku0825
yoku0825 / gist:1add18be35013e9c2c105c1f1a518fd1
Created July 29, 2021 09:38
innodb_parallel_read_threads=4でparallel_readで何本か止めておいてからcontinue
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 June 8, 2021 23:24
2038年1月19日3時14分8秒 UTCを過ぎると、次のSQLをパースしたところでmysqldが落ちる
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 April 1, 2021 05:39
MySQL 5.6.51にPLEASE句を実装するパッチ
--- ./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 February 24, 2021 13:51
REPLICATION SLAVE権限があったらoffline_modeでも切らないパッチ
--- 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 August 24, 2020 14:33
イメージ名の後に `mysqld --lower-case-table-names=1` を渡せば良さそうです
### オプションなしで起動(デフォルトの 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