カテゴリーアーカイブ SQL

著者:takahashi

MySQL 8.0でユーザーを作成する方法

MySQL8.0絡みでまたしても躓いたので記事にします。

新しくWebアプリ用のユーザーを追加するために、いつも通り下のようなSQL文を実行しました。

GRANT ALL ON test.* TO testuser@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;

ところが、

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password123' WITH GRANT OPTION' at line 1

シンタックス…エラー…？？？

どこか文字が全角になってたりするのだろうな～と最初はおもって何度も確認したのですが、特におかしな点は見つからず…

ちなみに後で同じSQL文を他のMySQL 5系のMySQLサーバーで試してみたところ

mysql> GRANT ALL ON test.* TO testuser@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)

こんな感じで普通に通りました。

いやな予感がして調べたところ、ある事実が発覚。

MySQL8.0ではGRANT構文でユーザを作成できない – guro_chanの手帳

上記記事によると、いままではそれとなくGRANT構文でユーザーも一緒に作成できてしまう挙動だったものを、 MySQL 8.0では明確にできないように変更したのだとか…

ではどうすればいいのか、という話ですが、下記のような2行のSQL文を実行することで、同様の操作を行うことができます。

create user 'ユーザー名'@'アクセスを許可するアクセス元IPもしくはホスト名' identified by 'パスワード';
grant all on アクセスを許可するDB名.アクセスを許可するテーブル名 to '1行目で指定したユーザー名'@'一行目で指定したアクセス元IPもしくはホスト名' with grant option;

最初に書いたSQL文をこの形式に変換すると下記のようになります。

create user 'testuser'@'localhost' identified by 'password123';
grant all on test.* to 'testuser'@'localhost' with grant option;

実行してみます。

MySQL [(none)]> create user 'testuser'@'localhost' identified by 'password123';
Query OK, 0 rows affected (0.05 sec)

MySQL [(none)]> grant all on test.* to 'testuser'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

こんどはちゃんと通りましたね。

リモート接続や指定されたDB以外はuseできないかなども確認しましたが、ちゃんと意図したとおりの動作になることを確認できました。

こんごはこちらのSQL文を使っていけば問題なさそうですね。

しかし、今まで使えていたSQL文を突然使用不能にした上に、警告メッセージもなくただシンタックスエラーにしてしまうというのは…なかなか乱暴な気がします…(汗

著者:asaba

【sql】INTO句で一発で新規テーブルを作る方法

ここ最近はsqlのリファレンスも積極的に読むようになりました。

zendframeworkも最初見た時はなんじゃこりゃjavaみたいと困惑することもありましたが

今になってようやくその戸惑いも消えつつあります。

普通のINSERT句で新規にテーブルを作って与えられた値をレコードに突っ込むのは

簡単ですが、そのレコードの値を新たに作った新規テーブルにまた入れるとなると骨が折れますよね。

今回良いなと思ったINTO句は、一つの命令文で新規テーブル追加と旧テーブル両方にレコード

を追加することができます。一文でできるって素晴らしいです。

SELECT 名前、価格、産地、消費期限
　　INTO 消費期限切れテーブル FROM 野菜テーブル

WHERE 消費期限 20190809

ここでは野菜テーブルから、消費期限切れレコードの値以下の行を抽出しています。例えば

ここに消費期限レコードの値が20190801や20180122に該当すれば、新規の消費期限切れテーブルに

移送されるということです。

一時的に目的のレコードを抜き出せるので、在庫処理などに向いてそうで便利そう。

著者:takahashi

FuelPHP + pdo_sqlsrv + SQL Server で「’NAMES’ は SET オプション として認識されません。」となったときの対処法

FuelPHPにSQL Serverを連携させたPHPのWebアプリを作ったときに出た問題。

SQL文自体には問題がなかったのですが、このようなエラーが発生しました。

原因はFuelPHP側の設定ファイルに文字コード設定を入れてしまっていたこと。

// fuel/app/config/db.php

return array(
    'default' => array(
        'type' => 'pdo', 
        'connection' => array(
            'dsn' => 'sqlsrv:Server=localhost;Database=hoge_db;',
            'username' => 'user',
            'password' => 'password123',
            'persistent' => false,
        ),
        'identifier' => '', 
        'table_prefix' => NULL,
        'charset' => 'utf8', //←これが原因
    )
);

charsetを入れると、DB呼び出し時にfuelが自動的に

SET NAMES 文字コード;

のようなSQL文を実行しようとします。

ところが、SQL Serverではこの”SET NAMES”オプションが廃止されており、これが原因でSQL実行時に毎回エラーになってしまいます。

対策としては

'charset' => 'utf8', 

を

'charset' => '', 

と書き換えれば、SET NAMESが行われなくなり、エラーが解決します。

いろいろ調べまくってやっと判明したぐらい細かい項目で、原因がわかるまで苦労しました…

お困りの方の参考になれば幸いです。