Webシステムに関することを中心に書いてます
備忘録的にアップします。
かなり昔に作られたシステムのメンテで学んだこと
pg_queryを使用してSQLを処理していましたが、自前のエスケープが効いてなくてエラーが発生していました。
addslashesで処理をしていましたが・・・
Googleティーチャーに聞いてみると記事を発見
standard_conforming_strings がOnの場合はエスケープが文字として認識される。
\nと改行コードがバックスラッシュとnの2文字と認識されるようです。
PostgreSQL9.1以降はこの値のデフォルトがOnということ
それで自前でエスケープしてもエラーになったと。
standard_conforming_stringsの説明
pg_queryを使っていたのをpg_query_paramsに変更することで関数でエスケープしてくれるので、これで回避！
エスケープがバックスラッシュではなくなったってことですね。
参考：徳丸浩の日記
EC-CUBEの規格登録をしようとしたら下記のエラーが出た件
*—————————————————————*
SQL: [INSERT INTO dtb_classcategory(name,class_id,creator_id,rank,create_date,update_date,classcategory_id)
SELECT $1,$2,$3,$4,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,$5]
PlaceHolder: [array (
0 => ‘テストデータ’,
1 => ’92’,
2 => ‘2’,
3 => 3,
4 => 96,
)]
MDB2 Error: unknown error
_doQuery: [Error message: Could not execute statement]
[Last executed query: EXECUTE mdb2_statement_pgsql_2977d07f3499f15f5f700e51693c3dfbbe8b622f1d (‘テストデータ’, ’92’, ‘2’, 3, 96)]
[Native message: ERROR: 重複キーが一意性制約”dtb_classcategory_pkey”に違反しています
DETAIL: キー (classcategory_id)=(96) はすでに存在します]
*—————————————————————*
EC-CUBE 2.x
PostgreSQL
classcategory_idがかぶっているよというエラーです。
PostgreSQLで状況を確認します。(phpPgAdmin)
まずはシーケンスの状況を確認します。
|
1 |
select * from dtb_classcategory_classcategory_id_seq; |
last_valueの値がclasscategory_idの最後の値です。
これが96になっています。
classcategory_idのMAXの値を調べます。
|
1 |
SELECT MAX(classcategory_id) FROM dtb_classcategory; |
240と値が返ってきました。
last_valueの値を変える必要があります。
|
1 |
select setval('dtb_classcategory_classcategory_id_seq',241); |
下記のエラーが出ました。
「リードオンリーのトランザクションでは setval() を実行できません」
phpPgAdminでは実行ができないようなので、SSHでサーバーに繋げて直接PostgreSQLから実行します。
|
1 |
# select setval('dtb_classcategory_classcategory_id_seq',241); |
最後に規格登録の動作確認をして登録できればOKです。
phpPgAdminでインポートするときに困ったこと。
テーブルのデータ入れ替えでクライアントよりデータを頂きました。
それをCOPYコマンドでデータを作ってインポート
「SQL実行完了」と表示されてもデータがインポートされていない。
色々調べてみたらどうやら\マークがNGだったようです。
「お米\1300」といった感じのデータがあると×
エスケープ文字なので当然といえば当然ですね。
これをエスケープしてしまえばOKです。
「お米\\1300」
これでインポートできた！
PostgreSQLのoid使用時の注意
サーバー移転時の話
7.4→9.2にpsqlもバージョンアップ
SELECT時にoidを使っていたものがカラムがないというエラーが出ました。
マニュアルを調べてみるとoidを使う場合はテーブル作成時に指定しないと駄目らしい。
バージョン8.1以降からデフォルトでのoidは無効のようです。
下記のようにテーブル作成します。
|
1 |
create table 'テーブル名' (id int) with oids; |
今回はoid使わなくてもOKなところだったのでプログラムを修正しました。
PostgreSQLのバックアップを取ってみる
pg_dumpで実行する場合パスワードなしだと下記のエラーが出る
たぶんpg_hba.confでローカルならパスワードなしOKって設定ができると思いますが…
pg_dump: [archiver(db)] connection to database “hogehoge” failed: fe_sendauth: no password supplied
オプション -w でもスルーできない場合に困る。
cronで実行したいときとかにね。
そのときは「.pgpass」ファイルで回避可能ですよ。
上記ファイル名でファイルを作成し、パーミッションを600にします。
そうしないとエラーで怒られます。
.pgpassの中身は下記
|
1 |
ホスト名:ポート:DB名:ユーザ名:パスワード |
パスワード以外はワイルドカード(*)のしてもありです。
ファイル作成後に下記のように実行するとOK
|
1 |
$ pg_dump -w -c DB名 > バックアップ.sql |
CakePHPでDBはPostgreSQLのサイトがある。
そちらを移転作業したときに起きた問題
動作チェック中に下記のエラーが発生
|
1 2 |
Query failed: ERROR: operator does not exist: date ~~ unknown HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. |
HINTから情報を検索するとPostgreSQLのバージョンで検索(Where)のキャスト(型)のチェックが厳密化されたようです。
今回はPostgreSQL7.4.23 → 9.2.10
厳密化は8.3からのようです。
キャストの厳密化とは検索するときにフィールドの型を厳密にチェックするようです。
今までのバージョンは型を自動で変換してくれていたようですが、8.3からはそれを行わないとのこと
|
1 2 3 4 |
WHERE field_int = '1' // int型に文字列を投げるとか WHERE field_text = 1// text型に数値をなげるとか |
それらがNGとのこと
私のプログラムのエラーはdate型にLIKE検索をしているのがNGでした。
対策としてはFunctionとCastを作成してそれで型の変換をさせる方法があるようですが、
次の移転時にまた困るような気がしたので、今回はプログラム側の修正を行いました。
(プログラムの修正箇所が少なかったため)
CakePHPを使っているのですが下記の修正ではエラーでした。
array(“date::text”=>”2017-11%”)
ということで、ちゃんと関数で変換します。
|
1 |
array("to_char(date, 'yyyy-mm')"=>"2017-11") |
エラーなく検索結果が返ってきたらOK
PostgreSQLは使い慣れていません。
DB管理ツールからSQLを投げたらずっとウェイト状態になってしまいました・・・
SQLでプロセスの停止ができるようなので試してみました。
プロセスIDの確認
|
1 |
SELECT * from pg_stat_activity; |
プロセスを停止させる。
|
1 |
SELECT pg_cancel_backend(プロセスID); |
上で駄目なら
|
1 |
SELECT pg_terminate_backend(プロセスID); |
上で駄目なら直接Kill
|
1 |
# kill -9 プロセスID |
この場合はroot権限が必要だったりするので使えないことがあります。