MySQL ログの時刻をOSに合わす

MySQLのデフォルトのログの時刻がUTCでわかりにくいので、OSの時刻に合わせます。

[ my.cnf ]

log_timestamps=SYSTEM   <=== これを追加

[ 変更前 ]

時刻の末尾がZ

2021-07-24T00:27:57.759453Z 4 [System] [MY-013381] [Server] Server upgrade from '80023' to '80026' started.
2021-07-24T00:28:03.206784Z 4 [System] [MY-013381] [Server] Server upgrade from '80023' to '80026' completed.

[ 変更後 ]

時刻の末尾に +9.00がつく

2021-07-24T09:36:02.475995+09:00 4 [System] [MY-013381] [Server] Server upgrade from '80025' to '80026' completed.
2021-07-24T09:36:02.609224+09:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-07-24T09:36:02.609856+09:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

MySQL8 初期設定 パスワードポリシー

MySQL8からユーザーのパスワードポリシーのデフォルトが厳しくなり、かんたんなパスワードが設定できなくなったので、my.cnfで永続的に設定しています。

[ デフォルトの状態 ]


mysql> SHOW variables like '%validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

[ my.cnfに設定 ]


validate_password.policy=LOW
validate_password.length=4
validate_password.check_user_name=OFF

mysqld を再起動

[ 変更後の状態 ]

[root@???? ?????]# mysql -uroot -p -e "SHOW variables like '%validate_password%'";
Enter password: 
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | OFF   |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+


MySQL8 JSON TABLE で配列を扱う

MySQL8からJSONの配列をレコードとして扱えるので、使ってみました。

[ Table例 ]
空港の滑走路のマスタで、複数の滑走路をJSONにしてます。

CREATE TABLE `m_runway` (
	`CD_IATA` CHAR(3) NOT NULL DEFAULT '' COMMENT 'IATA空港コード' COLLATE 'utf8_general_ci',
	`CD_ICAO` CHAR(4) NOT NULL DEFAULT '' COMMENT 'ICAO空港コード' COLLATE 'utf8_general_ci',
	`LAT` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '緯度',
	`LON` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '経度',
	`RUNWAYS` JSON NULL DEFAULT NULL COMMENT 'JSON明細',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `m_runway_CDIATA` (`CD_IATA`) USING BTREE,
	INDEX `m_runway_CDICAO` (`CD_ICAO`) USING BTREE,
	INDEX `m_runway_LAT` (`LAT`) USING BTREE,
	INDEX `m_runway_LON` (`LON`) USING BTREE
)
COMMENT='滑走路マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

[ JSON列のJSON ]


[
    {
        "name": "06",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 51.2,
        "isClosed": false,
        "location": {
            "lat": 40.6469841,
            "lon": -75.45063
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "13",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 123.1,
        "isClosed": false,
        "location": {
            "lat": 40.6551933,
            "lon": -75.44975
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "24",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 231.2,
        "isClosed": false,
        "location": {
            "lat": 40.6600342,
            "lon": -75.42926
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0.152,
            "nm": 0.082,
            "feet": 499,
            "mile": 0.095,
            "meter": 152.1
        }
    },
    {
        "name": "31",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 303.1,
        "isClosed": false,
        "location": {
            "lat": 40.6465263,
            "lon": -75.4322357
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    }
]

[ Select SQL例 ]

NESTED PATH を指定


    SELECT
      r.CD_IATA,
      r.CD_ICAO,
      -500 AS LAT,
      -500 AS LON,
      detail.NM_RUNWAY,
      detail.HDG,
      ROUND(detail.FEET, 0) AS FEET, 
      ROUND(detail.METER, 0) AS METER
    FROM 
      m_runway r, 

      JSON_TABLE(RUNWAYS, '$[*]' 
      COLUMNS (
        `NM_RUNWAY` VARCHAR(100) PATH '$.name',
        HDG INT PATH '$.trueHdg',
        
        NESTED PATH '$.length' 
        COLUMNS (
          FEET FLOAT PATH '$.feet',
          METER FLOAT PATH '$.meter'
        )               
        )
      ) AS detail
    WHERE
      CD_ICAO = 'RJTT'

[ 結果 ]

+---------+---------+------+------+-----------+------+-------+-------+
| CD_IATA | CD_ICAO | LAT  | LON  | NM_RUNWAY | HDG  | FEET  | METER |
+---------+---------+------+------+-----------+------+-------+-------+
| HND     | RJTT    | -500 | -500 | 04        |   35 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 05        |   42 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 16L       |  150 | 11053 |  3369 |
| HND     | RJTT    | -500 | -500 | 16R       |  150 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 22        |  215 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 23        |  222 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 34L       |  330 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 34R       |  330 | 11053 |  3369 |
+---------+---------+------+------+-----------+------+-------+-------+

MySQL8 タイムゾーンの設定

MySQL8を新規にインストールした状態で、タイムゾーンがUTCのままなので設定しました。
公式ドキュメントによると、タイムゾーンテーブルのロードが必要のようで、下記コマンドでロードして反映。

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

my.cnf に下の設定を入れます。

#default-time-zone = 'Asia/Tokyo'
default-time-zone='+9:00'

タイムゾーン名、オフセットどちらでも反映できるようです。

ここで教えていただきました。

https://qiita.com/kakudaisuke/items/f4b76fbfd7f89992d971

MySQL8 デフォルト my.cnf からbinlogを無効化

新規にインストールしたMySQL8のサーバーで、データサイズが大きく、10GBを超えていて更新が多いデータベースを運用していましたところ、

1) MySQLのメモリ消費が急増
2) サーバーのロードアベレージが常に高い

状態になったので、調べていたところ、MySQL8からデフォルトでbinlogが有効化された状態であることがわかりました。

my.cnfの設定に binlogの無効化を追加したところ大人しくなりました。

disable-log-bin   <== これを追加

MySQL 8系 でのJDBC設定

そろそろ、本番環境のMySQLを8系に上げないといけないと思い、開発環境を8系にして、MyBatisで試してたところ、いきなり最初の connect でエラーが出て使えないので、調べてましたところ、

JDBCの設定記述を変えないといけないことがわかり、無事解決しました。

ここで教えて頂く : https://qiita.com/syoki/items/3d82ef00300868353572

< MyBatis Config UNPOOLED 設定例 >


  <properties>     
    
    <!-- 5.7 -->
    <!--<property name="msdriver" value="com.mysql.jdbc.Driver" />--> 
    <!-- 8 -->
    <property name="msdriver" value="com.mysql.cj.jdbc.Driver" />   
    <!-- 5.7 -->  
    <!--<property name="msurl" value="jdbc:mysql://localhost:3306/dbname?characterEncoding=utf8&amp;autoReconnect=true&amp;useSSL=false" />-->
    <!-- 8 -->
    <property name="msurl" value="jdbc:mysql://localhost/dnksg?characterEncoding=utf8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B9&amp;rewriteBatchedStatements=true" />     
    
    <property name="msusername" value="???????" />     
    <property name="mspassword" value="??????????" />   
    

    
  </properties>

MySQL 漢字時刻文字列整数秒変換関数

Jsonでリクエストされた漢字の時刻を秒数に変換して MySQL に登録する必要あり作ってみましたが、
Javascriptであらかじめ変換してからリクエストすることにしたので、使ってません。
動作確認出来てないですが、必要な方はご利用下さい。

CREATE DEFINER=`root`@`localhost` FUNCTION `fc_intsecond_fromkanji`(
	`kanji` varchar(20)
)
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '漢字時間文字列整数秒数取得'
BEGIN

DECLARE CNT_SEP TINYINT DEFAULT 0;
DECLARE HMSTXT VARCHAR(20) DEFAULT '';
DECLARE FMTTXT VARCHAR(20) DEFAULT '';
DECLARE CHKNUM TINYINT DEFAULT 0;
DECLARE TP     VARCHAR(3) DEFAULT '';
DECLARE H SMALLINT DEFAULT 0;
DECLARE M TINYINT DEFAULT 0;
DECLARE S TINYINT DEFAULT 0;


SET HMSTXT = REPLACE(REPLACE(REPLACE(kanji, '時間', 'H'), '分', 'M'), '秒', 'S');
SET FMTTXT = REPLACE(REPLACE(REPLACE(kanji, '時間', ':'), '分', ':'), '秒', '');

SET TP = 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(HMSTXT, '0', ''),
'1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
;


insert into tst (fld0, fld1) values (HMSTXT, TP);

IF (TP = 'HMS') THEN
  SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 3, ':') AS UNSIGNED);
END IF;

/**/ 
IF (TP = 'HS') THEN
  SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;
IF (TP = 'HM') THEN
	SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;
IF (TP = 'MS') THEN
  SET M = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
	SET S = CAST(fc_delim_idxtxt(FMTTXT, 2, ':') AS UNSIGNED);
END IF;

/**/ 
IF (TP = 'H') THEN
	SET H = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
END IF;
IF (TP = 'M') THEN
	SET M = CAST(fc_delim_idxtxt(FMTTXT, 1, ':') AS UNSIGNED);
END IF;
IF (TP = 'S') THEN
  SET S = CAST(FMTTXT AS UNSIGNED);
END IF;

	 
RETURN H * 3600 + M * 60 + S;


END

MySQL ストアドファンクションから文字コード指定を消してパフォーマンス改善

MySQLの5.7から8に移行中での出来事で、ストアドが2秒程度で結果が返ってたのが、20秒程度かかってしまうので、

先日行った OSC2019 Tokyo Fall でMySQLのブースにいた人に聞いてみて、直してみると、改善できました。

全角文字を扱わない場合、文字コードは指定しない方がいいです。

< Old >

| fc_chgdvessel | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fc_chgdvessel`(
`vslnm` varchar(50)
) RETURNS varchar(36) CHARSET utf8
COMMENT '船名記号スペース変換関数'
begin

return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(vslnm, ' ', ''), '-', ''), '.', ''), '_', ''), '(', ''), ')', ''), '.', '');

end | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |

< New >

| fc_chgdvessel | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fc_chgdvessel`(
`vslnm` varchar(50)
) RETURNS varchar(36)
COMMENT '船名記号スペース変換関数'
begin

return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(vslnm, ' ', ''), '-', ''), '.', ''), '_', ''), '(', ''), ')', ''), '.', '');

end | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |