Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL8 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11の対処方法について

MySQL5.7からMySQL 8.0にアップグレードしてしばらく経過したところ、じゃあ、MySQL8でのmy.cnfをいじってみようかと思ったところからが地獄の始まり。謎のエラーで起動しなくなってしまいました

2019-03-18T05:15:40.431335Z 1 [ERROR] [MY-012574] [InnoDB] Unable to lock ./ibdata1 error: 11

えっーっと・・なんだろう、ibdata1のロックが取得できない・・もしかしてクラッシュした?(汗) なんでロックが取得できないんだろう。。。と色々と考えたところで・・

続きを読む

AWS ELB&NginxでIPアドレス直指定アクセスを拒否する方法でハマった・・どうやるんだろう?

AWS ELB(ALB)でIPアドレス直指定アクセスを禁止する方法ですが。。 例えば、www.abc.abcというドメインのELBの111.222.333.444だったとして、http://www.abc.abc にはアクセスを許可したいけど、http://111.222.333.444はアクセスを拒否したいなんてことないですか? すこし例が悪いですが、逆に、111.222.333.444のIPは、www.abc.abcと、www2.abc.abcという2つのドメインを持っている場合で、IPアドレスでのアクセスは拒否して、そのほかドメインの指定は、アクセスを許可したいという場合は、ELBでどのように設定すれば良いのでしょうか?

それは・・・

ELBの設定ではわかりませんでした(汗) 誰か教えてください

というわけで、ELBというより、ターゲットホストのnginxでそれを制御する方法をメモしました。

Amazon Web Services パターン別構築・運用ガイド 改訂第2版

Amazon Web Services パターン別構築・運用ガイド 改訂第2版

続きを読む

MySQL AUTO_INCREMENTカラムの変更方法

たとえば、こんなテーブルがあったとします。このテーブルってidがプライマリーキーです。

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `seq_id` int(10) unsigned NOT NULL ,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)
;

以下のように、seq_idをPRIMARY KEYに変更したい場合、どのような手順を踏めば良いでしょうか?すこし力技になります

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL ,
  `seq_id` int(10) unsigned NOT NULL NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`seq_id`)
)
;

筋トレが最強のソリューションである マッチョ社長が教える究極の悩み解決法

筋トレが最強のソリューションである マッチョ社長が教える究極の悩み解決法

超筋トレが最強のソリューションである 筋肉が人生を変える超科学的な理由

超筋トレが最強のソリューションである 筋肉が人生を変える超科学的な理由

続きを読む

MySQL リモートからの接続時にMySQLプロンプトにユーザー名とホスト名とデータベース名に変更する方法

すごく大した話ではないのですが、複数のデータベースを保有している時、コマンド接続した際に、どこのDBサーバに接続しているかわかんなくなっちゃうことありませんか? 自分の場合、管理が良くないので、本番データベースに接続しているのか、テスト用のデータベースに接続しているのかわからなくなることがあります。 基本的に自分はツールは使わないであくまでもコマンドラインです。パスワードを変えているから問題ないでしょ?と言われるかもしれませんが、複数のターミナルで本番環境とテスト環境が同時に立ち上がっている時が多く、テスト環境だと思いきや、実は本番環境を更新してたわ(汗)なんてことが多くあり、冷や汗を出したことがあります。

何書いているかわかんないですよね(笑)私が言いたいのはつまりこういうことです。

クライアントAからホストB(本番)のmydb に接続した場合のMySQLプロンプト

接続

[user@host_a ~] mysql -umyuser -p -h host_b -Dmydb

接続後のMySQLプロンプト

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mydb]>

クライアントAからホストC(テスト)のmydbに接続した場合のMySQLプロンプト

接続

[user@host_a ~] mysql -umyuser -p -h host_c -Dmydb

接続後のMySQLプロンプト

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mydb]>

ね?この状態で本番、テスト環境ともに、同じデータベースだった場合、どっちに接続しているかわからなくないですか?

というわけで、私は、MySQLのプロンプトを以下のようにしたいのです

myuser@host_b [mydb] >

続きを読む

MySQL8でいつの間にか使えなくなったパスワード設定

MySQL8 以前のバージョンの時のユーザアカウント作成周りでは問題なかったのに、MySQL8になってたらやたらシンタックスエラーが出まくる状態の方は、以降を試してみるといいかもしれません(自分のことですが) いつの間にかMySQL8のユーザー周りとか権限周りの設定が厳しくなっている(自分的にはめんどくさくなっている)のでメモしておきました

続きを読む

AWS route53でワイルドカードを利用したDNSレコードの作成そして・・優先順位はどうなるの?

route53しか使っていないので、自分の中ではroute53の機能としてメモを書きます(笑) 結論として、awsのroute53はワイルドカードDNSレコードを作成できる!ことです(笑)

例えば・・、

52.5.71.100 というpublic ipを持つインスタンスがあったとして、 これを、 zone s-quad.com に

a1.s-quad.com
a2.s-quad.com
a3.s-quad.com
a4.s-quad.com
a5.s-quad.com
・
・
a1000.s-quad.com

というFQDNを登録したい場合どうしますか?私は1000レコード作る気まんまんだったのですが、どーもそんなことしなくても良さげな感じでした

それがワイルドカードDNSレコードってやつですね。

続きを読む

AWS S3のCORS設定がグレーアウトして編集できない場合の対処方法

S3のCORS構成エディタの編集ができない・・・なぜそうなのか理由はあるのだろうけど、僕にはさっぱりとわからないです。そして、以降のことをすることにより、特に原因がわからないまま、改善したので備忘録代わりに掲載します。

どこのプログラムなのかは別として、開発者から以下のエラーが出て、画像にアクセスできなーい?っていう問い合わせがきました。

Access to image at 'https://www2.abcxxxxx.com/abc-mages/786959437222742.jpg' from origin 'http://localhost:3000' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.

nginxのリバースプロキシで画像を取得しているので、nginxのヘッダー追加で改善できるのかと思いきや。。。できない。どうも自分は、CORSの基礎がわかっていないようで・・・。

そこで、本題のS3側の、CORSの設定の話に繋がるので、、

CORSの設定は、対象のバケットの「アクセス権限 - CORSの設定」で確認できます。自分の場合は、もれなくグレーアウトしています(汗)何も編集できなーい!!

f:id:hit10231023:20190227221856j:plain

CORSって何?

Cross Origin Resource Sharing の頭文字をとったものです。まあ、あまり詳しくはわからないのですが、異なるドメイン間で画像とか動画等をやりとりする為のセキュリティを守るしくみみたいなものだと私は思ってます。www.aaaaqaz12wsde34.com から www2.abcxxxxx.comの画像をアクセスするのは許可するよーとかそういうのをヘッダで制御するしくみですね。wev-apiとかjavascriptとかが近年進化しているので、セキュリティ的にそういうしくみができたのかなー?って思ってます。

そんなこんなで、いつの日からか、aws s3にもCORSの設定ができたって感じです。(・・というか前からあって自分が気にしていなかったかもですが・・)

編集できるようにするためにはどうするの?

CORSの詳細は、すみませんが自分が無知すぎるので、話はおいておいて。編集できるようになった証跡を以降記載していきます。

続きを読む

SQLで画像URLの拡張子のみ表示するSQL

まあ、これだけなんですが(汗)個人的に頻繁に使うのでメモ。 まず、文字列を、REVERSEで反転させて、最初に登場するドット(.)の位置までRIGHT関数で切り出す感じにしてます。もっとシンプルな方法がありましたら、どなたかご教授を(笑)

文字列関数を利用して、画像URLの拡張子のみ抽出するSQL

SET @ImageURL='https://cdn.pixabay.com/photo/2013/11/28/10/36/road-220058_960_720.jpg' ;

SELECT RIGHT(@ImageURL,INSTR(REVERSE(@ImageURL),'\.')) AS extension ;
------------------------
+-----------+
| extension |
+-----------+
| .jpg      |
+-----------+
続きを読む

2000円台でこの暖かさ! LIFEJOY 洗える 日本製 電気毛布 敷きタイプ買ってみました!

寒い冬、エンジニアな人が風邪、インフルエンザになりやすい季節になってきましたね。基本エンジニアな人たちは、頭は良いけど、体は弱い、いや、体力が低下するまで、お仕事にのめり込んじゃうのがいけないのだと思いますが(笑)せめて、寝る時くらいは、あったかあったかで眠りたいですよね。もともと自分は冷え性なので、寝ながら仕事できるようにせめて、寝る時くらいはあったかくして寝たいなと思いまして。

安価で購入できる。LIFEJOY 洗える 日本製 電気毛布 敷きタイプを購入してみました。

続きを読む

【蘇るiMac】 iMac 2010の内臓ハードディスククラッシュをきっかけに外付けSSDハードディスクに変更し爆速になった記録

タイトルで爆速は大げさかな(汗)

ここ数日、ブログを書こうとしたら、このmacの中から何かカリカリ音がして、なんだろう?と気になってはいたのですが、その翌日にさらにおかしくなり、文字を入力すると、変換の部分でピタっと画面がとまるとか(笑)筐体をぶっ叩くと、日本語変換できるとか、もーあきらかに動きが変なんですね(汗) じゃあ、この際だからmac初期化してディスクを綺麗にしようと試み初期化は終わり再インストールしても、やっぱりピタっと止まる・・なんだこれ? ハードディスク見てもエラーではないし、でも原因は、ハードディスクっぽいって感じ。

色々対策して、今ここで爆速iMacでブログを書けるまでになった対応を(記念に)メモします。

・・というかわかっている人が対応したら、半日で治ったのかのしれませんが汗

f:id:hit10231023:20181125193556j:plain

続きを読む

MySQL SQLの検索結果をshellの変数に格納する方法

色々な書き方がありますが、私は以下のように書いてます。というほど書いてないので、必要な時にすっかり忘れるのが現実ですが。。

例えばこんなケースで書いてみます。

とあるテーブルの検索結果が0(ゼロ)件以外だったら、後続の処理を実行する

続きを読む

MySQL8 多段的なCTE表でアクセスログを集計するSQL

多段的にする必要は実はそんなにないのですが、apacheおよびnginx等のアクセスログ(テーブル)をカテゴリに分けて集計するSQLを作ってみました。

nginxのログをテーブルに格納する方法については、以前、ここにまとめました。(ちょっと古いか。。)

hit.hateblo.jp

以下、今回のログの形式ですが、上記の内容とは別のものです。

CREATE TABLE `accesslogs` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `remote_address` varchar(128) DEFAULT NULL,
  `User_Agent` varchar(512) DEFAULT NULL,
  `Url` varchar(512) DEFAULT NULL,
  `CreatedAt` datetime NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=159193 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

やりたいことは、

  1. googlebotからのクロールが?
  2. どのディレクトリをクロールしているか?
  3. 最終のクロール日時を出しつつ
  4. botがPC用クロールなのかモバイル用のクロールなのかを判別しながら

のアクセス回数をカウントするSQLです。

結果からいうとこんな感じになります

+------------------+-----------------------------------------------------+-------------+---------------------+
| BotType          | UriType                                             | AccessCount | LastAccess          |
+------------------+-----------------------------------------------------+-------------+---------------------+
| Googlebot_Mobile | zzzz                                                |        1934 | 2018-10-30 08:25:37 |
| Googlebot_PC     | 12345678                                            |        1226 | 2018-10-30 08:21:02 |
| Googlebot_Mobile | 12345678                                            |         462 | 2018-10-30 08:25:12 |
| Googlebot_PC     | abcdef                                              |       10106 | 2018-10-30 08:25:58 |
| Googlebot_PC     | zzzz                                                |        5306 | 2018-10-30 08:23:46 |
| Googlebot_Mobile | abcdef                                              |         633 | 2018-10-30 08:15:30 |
続きを読む

トイレの水が出ない!!修理の方法など SH381BA編

トイレの修理はお早めに!

TOTOのトイレ(SH381BA)の修理します。はい!全然、技術系の話でもデータベースの話でもありません!

結果からいいますと、トイレの水が出なくなりました厳密にいうと、タンク内部で水漏れしている気が・・する・・

幸いにも、自宅は、トイレが2つあるので、全く使えない状態というのは避けることはできたのですが、 それでも、今まで2台トイレがあったのに、1台しか使えないということが、どれだけ困るかを実感できました。 このようなケースの場合、どのパーツ名のどの型番のものを購入すればいいのかを、備忘録代わりに残して、同じ症状で同じお悩みがある方に、お役に立てればと思っています

そんでもって結局、今回は止水ダイヤフラム

の交換とあいなりました! そして、意外に安くてびっくり

TOTO ボールタップ用ダイヤフラム HH11113

TOTO ボールタップ用ダイヤフラム HH11113

オーエ 洋式用 トイレのつまりとり ショート

オーエ 洋式用 トイレのつまりとり ショート

続きを読む

MySQL8 SQL Window関数とCTE表でデータクレンジングと名寄せを実現する

とあるデータのクレンジング、名寄せ作業をSQLだけで完結する上で、よくわからなくなってしまったので、メモします。

例えば、こんな定義とデータがあったとして。

定義

CREATE TABLE `artist_master` (
  `Id` bigint(20) unsigned NOT NULL,
  `CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
  `Valid` tinyint(4) NOT NULL DEFAULT '1',
  `Name` varchar(128) NOT NULL,
  `VideoCount` bigint(20) unsigned NOT NULL DEFAULT '0',
  `Ext` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 
;

データ

mysql> SELECT * FROM artist_master ;
-----------------
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+
| Id              | CreatedAt           | Valid | Name           | VideoCount | Ext                                  |
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+
|  38095832004676 | 2018-10-26 06:50:23 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 333655795382791 | 2018-10-26 06:50:21 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 354380886937504 | 2018-10-26 06:50:25 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 391755868626561 | 2018-10-26 06:50:24 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 504480813693733 | 2018-10-26 06:50:22 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 577030850315619 | 2018-10-26 06:50:24 |     1 | Chris Alan Lee |          0 | https://www.imdb.com/name/nm1327414/ |
| 629430777071848 | 2018-10-26 06:50:20 |     1 | Chris Alan Lee |          0 | NULL                                 |
| 643255722139392 | 2018-10-26 06:50:20 |     1 | Chris Alan Lee |          0 | NULL                                 |
+-----------------+---------------------+-------+----------------+------------+--------------------------------------+

やりたいこと

上記のデータを踏まえ、

577030850315619のValid値を1のまま保守して、その他のValid値を0にしたい場合、どうすればいいんだっけ?というものを考えてまして・・・いわゆる、データークレンジングとか名寄せの話になるのですが(汗)それをSQLのみで実現するにはどうしたらいいってお話です

続きを読む

MySQL LIKE JOIN というアウトローなクエリーを試してみる

こんなの不可能だろと思いつつやってみたら以外と出来たというクエリーについて紹介します。

JOIN のON句にLIKEを使うというだけなのですが、これが以外と使える場面があります。今回、音楽のアーティスト名周りでこの難解なSQLをどのように活用するかを考えてみました。

続きを読む