pdoで取得したSQLをファイルに出力したいのですが、エラーにより出力できません。

Question

～～～ 経緯ここから ～～～
最初の質問は
https://teratail.com/questions/93539
です。
Doctrineを使用していたのですが、あまりにもメモリを消費するのでpdoを使用し、$obj->getXX()を使用しないように修正しました。
pdo検索でメモリを消費しなくなりましたが、取得件数が数百万件になり、ループする回数を減らしたいため
https://teratail.com/questions/99914
の質問をしました。
一度、数百万件で処理を実行したところ、問題なく出力できました。
しかし、ループでファイルに出力する処理で時間がかかりすぎるため、今回の質問をすることになりました。
～～～ 経緯ここまで ～～～

// ファイルの作成処理
$outputFile = '/test/test.txt';
// 出力ファイルが既に存在する
if (file_exists($outputFile)) {
} else {
  if (touch($outputFile)) {
    // ファイル作成成功
    if (chmod($outputFile, 0777)) {
      // パーミッション変更成功
    } else {
      // パーミッション変更失敗
    }
  } else {
    // ファイル作成失敗
  }
}

// 以下の処理はパーミッション変更まで成功した前提で実行しています

// $connはPDO接続情報
// SELECTのSQL
$sqlStr = "SELECT test_table.id id FROM test_table tt WHERE tt.note LIKE :test";
// OUTFILEのSQL
$outFileStr = " INTO OUTFILE ". $outputFile;
// SQL作成
$sqlStr .= $outFileStr;

// prepare
$stmt = $conn->prepare($sqlStr);

// ※バインド
$stmt->bindValue(':test', '%test%', PDO::PARAM_STR);

// SQL実行
$stmt->execute($sqlStr);

上記のSQLの結果をファイルに出力したいのですが、エラーが発生して出力できません。
エラーメッセージは以下が出力されます。

【エラーメッセージ】
SQLSTATE[28000]: Invalid authorization specification: 1045 Access denied for user 'user'@'123.456.789.111' (using password: YES)

APサーバー：123.456.789.111
DBサーバー：123.456.789.000
※数値は適当

上記のPHPを実行しているAPサーバーとDBサーバーが異なるため、
DBサーバーの結果をAPサーバーに出力できないと思います。
このような状態でSQLの結果をファイルに出力することは可能でしょうか？

補足：
「INTO OUTFILE」が付与されていないときはエラーは発生しませんでした。

Comments

[ttyp03]

出力先はAPサーバー？DBサーバー？

[red13]

出力先はAPサーバーです。

Answer 1

「INTO OUTFILE」が付与されていないときはエラーは発生しませんでした。

'user'@'123.456.789.111がファイル出力の権限をもっていないか
mysqlデーモンの実行ユーザーがディレクトリに対して書き込み権限をもっていないのでは？
mysqlデーモンにどうしてもファイル出力させなくていはいけないのでなければ
pdo経由のwebベースで出力してもらうほうが楽だとおもいますよ

Comments

[red13]

ファイル作成処理を追加しました。
ファイルは作成され、パーミッションも777が設定されています。
この状態でもエラーメッセージは変わりません。

winSCPから、mysqlの実行ユーザーでファイルを修正した場合、
「アップロードは成功しましたが、パーミッション/タイムスタンプの設定中にエラー」が表示されます。

Answer 2

検討、実験した結果、以下のような処理になりました。

１．サブテーブルの最大件数を取得する。
SELECT COUNT(id) id_count FROM sub_table GROUP BY id ORDER BY id_count DESC LIMIT 1

２．「１．」で取得した件数でループしてサブテーブル部分のSQLを作成する。
SELECT
main.*,
// ここをPHPのループで作成
(SELECT id sub_id FROM sub_table sub WHERE sub.main_id = main.id LIMIT 0, 1) sub_id1,
(SELECT sub_note sub_note FROM sub_table sub WHERE sub.main_id = main.id LIMIT 0, 1) sub_note1,
(SELECT id sub_id FROM sub_table sub WHERE sub.main_id = main.id LIMIT 1, 1) sub_id2,
(SELECT sub_note sub_note FROM sub_table sub WHERE sub.main_id = main.id LIMIT 1, 1) sub_note2,
・・・
FROM
main_table main

３．「２．」を登録するテーブルを作成する。
CREATE TEMP_TABLE (カラム);

４．「２．」をINSERTする。
INSERT INTO TEMP_TABLE 「２．」のSELECT;

５．PHPのexec()でリダイレクト出力する。
mysql -u root -ptest 'SELECT * FROM TEMP_TABLE' > test/test.csv

６．「３．」のテーブルを削除する。
DROP TABLE TEMP_TABLE;

Comments

[red13]

約5万件を出力して試しました。
https://teratail.com/questions/99914
上記の質問で実装した、結合のみで数百万件ループしてCSV出力した場合は約50分、
今回の実装でリダイレクト出力した場合は約25分、という結果になりました。

[red13]

処理としては複雑ですが、最速のため、このような実装をせざるを得ない状況になりました。
私としては、このような処理はメンテナンスのことを考えると実装すべきではないと思います。
ただ、もうどうしようもないため、この質問は解決済みにさせていただきます。

Answer 3

INTO OUTFILEを使っても、出力先はDBサーバーになります。
APサーバーに出力するのであれば、泥臭いやり方ですが、フェッチしながらファイルに書きだしてあげればどうでしょうか。

$outputFile = '/test/test.txt';
$sqlStr = "SELECT test_table.id id FROM test_table tt WHERE tt.note LIKE :test";
$stmt = $conn->prepare($sqlStr);
$stmt->bindValue(':test', '%test%', PDO::PARAM_STR);
$stmt->execute($sqlStr);
$fp = fopen($outputFile, 'w');
while($res = $stmt->fetch(PDO::FETCH_ASSOC)){
    fputcsv($fp, $res);
}
fclose($fp);

Comments

[red13]

調べたところ「INTO OUTFILE」はリモートの情報をローカルに出力できないようですね。

この質問をした理由は過去に自分が質問した
https://teratail.com/questions/99914
に理由があります。

while($res = $stmt->fetch(PDO::FETCH_ASSOC)){
}
のようなループを使用して、最後まで出力はできました。
しかし、ループ処理が完了するのに30分以上かかります。
あまりにも時間がかかりすぎているため、高速化のためにループせずに出力する方法等を模索しているところです。

[ttyp03]

該当の質問はこっちですかね？
https://teratail.com/questions/93539
遅いという理由だと、提示した方法だと厳しいですね。
一旦DBサーバー側にINTO OUTFILEで出力して、それをFTPなどでAPサーバーに持ってくる感じでしょうかね。

[red13]

最初の質問は
https://teratail.com/questions/93539
です。
Doctrineを使用していたのですが、あまりにもメモリを消費するのでpdoを使用し、$obj->getXX()を使用しないように修正しました。
pdo検索でメモリを消費しなくなりましたが、取得件数が数百万件になり、ループする回数を減らしたいため
https://teratail.com/questions/99914
の質問をしました。
一度、数百万件で処理を実行したところ、問題なく出力できました。
しかし、ループでファイルに出力する処理で時間がかかりすぎるため、今回の質問をすることになりました。

DBサーバーに出力ファイルを作成して
exec()処理でダウンロードさせるとかですかね？
そうなるとDBサーバーに出力できるかどうか調べないといけません。

[ttyp03]

過去質問の前提があるなら、その旨書いておいた方がいいと思いますよ。
私のようにこの質問だけ見た人にはわからないので。
-------------------------
DBサーバーは出力先ディレクトリの権限次第だと思うので、その設定さえできていれば問題ないと思います。

[red13]

コメントの内容を本文に追記しました。