MySQLで「load data LOCAL infile」が利用可能か確認する

MySQLで大きなファイルをインポートする際に便利な「load data LOCAL infile」
利用しているサーバーで使う事ができるのかをチェックするには、
グローバル変数を確認します。
何かとエラーに躓きがちな「load data LOCAL infile」ですので、
事前に実行できてファイルアクセスが可能なことは確認しておいた方が安心です。

とはいえ、レンタルサーバーでもほぼ利用可能なはずです。

グローバル変数「LOCAL_INFILE」の有効を確認

「load data LOCAL infile」利用するにはヘルプ記載の通り、
グローバル変数「LOCAL_INFILE」が有効である必要があります。

LOCAL キーワードは、あとで説明されているように、ファイルの予測される場所やエラー処理に影響を与えます。LOCAL は、サーバーとクライアントの両方がそれを許可するように構成されている場合にのみ機能します。たとえば、mysqld が --local-infile=0 で起動された場合、LOCAL は機能しません。セクション6.1.6「LOAD DATA LOCAL のセキュリティーの問題」を参照してください。

（抜粋引用）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文

MySQLの環境変数を確認するには、以下をSQLウィンドウに入力し実行します。

すべての変数が確認できますので、
その中で「LOCAL_INFILE」を探し「ON」になっていることを確認します。

SHOW VARIABLES;

また、決め打ちで確認したい場合、以下のようにSQLを発行しても確認ができるはずです。

SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘LOCAL_INFILE’;

結果として、以下のように表示されればまずローカルアクセスは可能でしょう。

尚、この変数はヘルプで以下のように書かれています。

local_infile

Property	Value
System Variable	local_infile
Scope	Global
Dynamic	Yes
Type	boolean
Default Value	ON

This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side.

To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime. For more information, see Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

（抜粋引用）MySQL :: MySQL 5.5 Reference Manual :: 5.1.7 Server System Variables

以下Google訳

この変数は、LOAD DATAステートメントのサーバー側のLOCAL機能を制御します。 local_infileの設定によっては、クライアント側でLOCALが有効になっているクライアントによるローカルデータの読み込みを拒否または許可します。

サーバが明示的にLOAD DATA LOCAL文を拒否または許可するようにするには（クライアントプログラムおよびライブラリがビルド時または実行時にどのように設定されているかに関係なく）、local_infileを無効または有効にしてmysqldを起動します。 local_infileは実行時にも設定できます。 詳細は、6.1.6項「LOAD DATA LOCALのセキュリティの問題」を参照してください。

デフォルトがONですので、特別なことがない限りはONで利用可能のはずです。

このローカルファイルのアクセスが拒否されると、
WordPressのバックアップ系プラグインにも影響が出るでしょうし、
相当なことがなければ、利用は認められている事と思います。

PHPを利用して「load data LOCAL infile」でインポートをするのであれば、
恐らくはこれだけで十分なはずです。

後は、正しいSQLの発行ができれば、インポート可能なはずです。

「正しいSQLが発行できれば」が地味に面倒なんですけどね。

それにルーチン処理であればPHPを書くのもいいのですが、
できればphpMyAdminからサクッとSQL実行したいと言うのが本音ですよねぇ。

phpMyAdminからSQL発行して「load data LOCAL infile」を利用可能か?

phpMyAdminから同様に「load data LOCAL infile」で、
インポートができると便利だと思い試みましたがうまく動作させることはできませんでした。

結果、MySQL5.5環境では以下のように表示されました。

XSERVERデフォルト（MySQL5.5向け）phpMyAdminでも、
独自にWebサーバーへ導入したphpMyAdmin共に同じでした。

#1148 – The used command is not allowed with this MariaDB version

またMySQL5.7環境では以下のように表示されました。

XSERVERデフォルト（MySQL5.7向け）で実行した場合、以下のように表示されました。

#1148 – The used command is not allowed with this MySQL version

エラーの内容自体はローカルで実行した時にも、
SQL誤りなどで似たメッセージが表示されたため、紛らわしいのですが、
phpMyAdminからは利用ができないようです。

何度実行してもSQLを見直してもエラーになる理由として、
公式ヘルプには以下のように書かれています。

CSV

Comma separated values format which is often used by spreadsheets or various other programs for export/import.

Note

When importing data into a table from a CSV file where the table has an ‘auto_increment’ field, make the ‘auto_increment’ value for each record in the CSV field to be ‘0’ (zero). This allows the ‘auto_increment’ field to populate correctly.

It is now possible to import a CSV file at the server or database level. Instead of having to create a table to import the CSV file into, a best-fit structure will be determined for you and the data imported into it, instead. All other features, requirements, and limitations are as before.

CSV using LOAD DATA

Similar to CSV, only using the internal MySQL parser and not the phpMyAdmin one.

（抜粋引用）Import and export — phpMyAdmin 5.0.0-dev documentation

Google翻訳

CSV
スプレッドシートやエクスポート/インポートのためのさまざまな他のプログラムでよく使用されるカンマ区切り値形式。

注意

テーブルに ‘auto_increment’フィールドがあるCSVファイルからテーブルにデータをインポートするときは、CSVフィールドの各レコードの ‘auto_increment’値を ‘0’（ゼロ）にします。 これにより、 ‘auto_increment’フィールドが正しく設定されます。

サーバーまたはデータベースレベルでCSVファイルをインポートできるようになりました。 代わりに、CSVファイルをインポートするテーブルを作成する必要がなく、代わりにインポートされたデータと最適な構造が決定されます。 他のすべての機能、要件、および制限は以前と同じです。

LOAD DATAを使用したCSV
CSVと同様に、内部のMySQLパーサーのみを使用し、phpMyAdminは使用しません。

「LOAD DATA」をphpMyAdminは使用しない。

そう読み取れるので、phpMyAdminを利用した「load data LOCAL infile」は、
恐らくは動作しないというのが結論なようです。

少なくとも自分は動作させることができませんでした。

一部のサイトではグローバル変数「LOCAL_INFILE」の
起動オプションによる問題という記述が見られますが、

前述の通り「LOCAL_INFILE」が「ON」であっても結果は同じです。

もしONではない環境であったとしても、
グローバル変数「LOCAL_INFILE」はSESSIONによる変更は利用できません。

SET SESSION local_infile = 1;

#1229 – Variable ‘local_infile’ is a GLOBAL variable and should be set with SET GLOBAL

当然ですが、共有レンタルサーバーのグローバル変数を変更しようとすればエラーが返ります。

SET GLOBAL local_infile = 1;

#1227 – Access denied; you need (at least one of) the SUPER privilege(s) for this operation

phpMyAdminでの「load data LOCAL infile」利用は諦めた方が良さそうです。

本サイトで公開しているツールについては、
十分にテストは行っておりますが個人で作成している為、
潜在的なバグがないとは言い切れません。
その為、ツールを用いた結果については十分検証の上ご利用ください。
当サイトおよび、管理人は如何なる損害もその責を負いません。

当サイト内のコンテンツおよび画像において、
出典・引用の外部著作権者の明記がないものは、
すべて管理人による著作物です。
当サイトでご紹介しておりますコンテンツの著作権の放棄は致しません。

サイト内コンテンツを引用される際にはご連絡は不要です。
ただし、出典元として当サイト(個別記事)へのリンクをお願いいたします。
申し訳ございませんが、無断転載、複製をお断りさせて頂いております。

コンテンツを有益であると感じていただけましたら非常に光栄です。
ありがとうございます。