質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.65%

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

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,547

red13

score 77

~~~ 経緯ここから ~~~
最初の質問は
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」が付与されていないときはエラーは発生しませんでした。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ttyp03

    2017/11/17 15:32

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

    キャンセル

  • red13

    2017/11/17 15:44

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

    キャンセル

回答 3

checkベストアンサー

+2

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/17 15:51

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

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

    キャンセル

0

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);

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/17 16:51

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

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

    キャンセル

  • 2017/11/17 16:55

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

    キャンセル

  • 2017/11/17 16:59

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

    キャンセル

0

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

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

2.「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

3.「2.」を登録するテーブルを作成する。
CREATE TEMP_TABLE (カラム);

4.「2.」をINSERTする。
INSERT INTO TEMP_TABLE 「2.」のSELECT;

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

6.「3.」のテーブルを削除する。
DROP TABLE TEMP_TABLE;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/11/28 14:27

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

    キャンセル

  • 2017/11/30 09:33

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.65%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る