カテゴリー: SQL

ERROR 5 (HY000) at line xxx: Out of memory

MariaDB10.3をインターネット越しにCommand Promptより

c:\xampp\mysql\bin\mysql.exe -h10.0.0.1 -uroot -pPW db_name < D:^\WEB^\test.sql

ってWindowsよりSQLファイルを流したらOut of memoryとかすら出ずに、途中までしかInsertされない。めちゃデカSQLしたらもちろんOut of Memory。

だいたい１００MBのSQLファイルでSQLDumpより取得したやつででた

とりあえずCentOS7にいれてるMariaDBのmy.cnfを


[mysqld]

innodb_log_file_size = 400M（たぶんこれはあんまり関係ない）

wait_timeout = 28800（これも関係なさそう？）

max_allowed_packet=512M（これは関係してそう）

って感じで編集したら出にくくなった気がするような、でもまだ出たときがあってなんか不安定

SQLdumpで –quick –skip-extended-insert オプションをつかって処理を少し分割させたけど、エラーでて、少しがっかり。

色々調べてるとSQLがインサート中はテーブルがロックされているような感じだった。

innodb_lock_wait_timeout =180にしたらとりあえず治った？？

innodb_lock_wait_timeout が原因？？

SQL入れている間はDBにアクセスできない現象が起きたが、これもテーブルロックが原因なんだろうか。なんか違うような。

innodb_lock_wait_timeout =180にしてからはまだ一度も失敗してない。

というか、エラーメッセージなしでコケるのは本当に勘弁してほしい

 

多量リクエスト（insert）時に10.1.21-MariaDBでmysql-connector-java-5.1.42-bin.jarでJDBCしたときに全然insertが追いつかない（更新されない）問題

なんかMariaDBでmysql-connector-java-5.1.42-bin.jarでJDBCするときにコネクションを貼りまくって、executeUpdateでいっぱい更新しようとしたとき、なんかMariaDBがもういいっぱいだよ～（dirty page だよ～）みたいな感じでぜんぜんSQLのInsertができなくなった

最初はMariaDBの問題かと思って色々チューニングしたりしたけど、解決せず

原因はJDBCのexecuteUpdateでした

これ1秒に100リクエストとかめっちゃ送るときにいちいちコネクション貼り直したりする感じで効率が悪いっぽい

めっちゃいっぱい更新したいときは

executeBatch

を使いましょう

そうしたら恐ろしいほどクエリが改善した。

ついでにいうとBulk insertにするとより早いとおもう！

 

public void doSQL(String sql) {
start();
try {
stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

 

から

 

}
public void doSQLBig(String sql) {
start();
try {
//conn.setAutoCommit(false);
stmt.addBatch(sql);
int result[]=stmt.executeBatch();

for(int i=0;i<result.length;i++){
System.out.println(“result”+i+”: “+result[i]);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

に変更した

出てたエラー↓

at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.coin.java.web.SQL.doSQL(SQL.java:198)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:479)
… 1 more
Caused by: java.net.SocketException: Unrecognized Windows Sockets error: 0: recv failed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at java.net.SocketInputStream.read(Unknown Source)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3008)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3469)
… 12 more
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.GeneratedConstructorAccessor61.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1187)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1182)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2377)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2361)
at com.coin.java.web.SQL.start(SQL.java:81)
at com.coin.java.web.SQL.doSQL(SQL.java:196)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:482)
at com.coin.java.web.RateCallback$4.run(RateCallback.java:166)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 22,566 milliseconds ago. The last packet sent successfully to the server was 22,566 milliseconds ago.
at sun.reflect.GeneratedConstructorAccessor60.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3459)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3900)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.coin.java.web.SQL.doSQL(SQL.java:198)
at com.coin.java.web.CommonApi.insertBalanceSQL(CommonApi.java:3256)
at com.coin.java.web.RateCallback.getBalance(RateCallback.java:479)
… 1 more

 

 

 

MySQL,MariaDBでログファイルが作られない問題

なぜかログファイルが
my.cnfに

log_error = /var/log/mysql/error.log

とかちゃんと指定してるのにファイル自体が作られない。

なんでだろうと思ったらただのフォルダのパーミッションの設定が悪かっただけでした。

単純ミスだけど意外と見落としたから一応記録

SQLで秒おきの最新のカラムのみを取ってくるクエリ

統計処理で何分とか、何時間とか何秒とかで区切りをつけてSQLを取ってきたい時がある
その時は以下の方法でできそう

SELECT DATE_FORMAT(FROM_UNIXTIME(TRUNCATE(UNIX_TIMESTAMP(`date`) / 180, 0) * 180), ‘%Y-%m-%d %H:%i’) AS custom_date,`id`
FROM `child` Where `date`>\”2017-07-04 00:00:00\” GROUP BY TRUNCATE(UNIX_TIMESTAMP(`date`) / 180, 0) ORDER BY `date`DESC LIMIT 360″;

データインポート時にERROR 2006 (HY000): MySQL server has gone away

c:\xampp\mysql\bin\mysqldump.exe -hHOST -uUSER -pPW –max_allowed_packet=512M 転送元DB | c:\xampp\mysql\bin\mysql.exe -hHOST -uUSER -pPW –max_allowed_packet=512M 転送先DB

で接続先のDBにそのままDBをコピー出来ると思ったんだけど
ERROR 2006 (HY000): MySQL server has gone away
ってでる

接続先のmysqlが落ちてそう

max_allowed_packet指定してても出るから仕方ないから接続先のDBのmax_allowed_packetを上げる

vi /etc/my.cnf

[mysqld]
max_allowed_packet = 1000M

一番下に追加する

動くといいね！

Daisy Chained Replication on MariaDB(MySQL)

A(master)　—インターネット→　B(slave)　—-イントラネット→　C(slave)

ってデータを更新したい。ややこしいと言うか出来るのかって話だけど出来るらしい

Master with Relay Slaveと言うらしい

んでせっかくならGTIDっていうのを使ってやろうって話

mysql_secure_installation

とりあえずやってみよう

SQLでAがBの条件の時を優先してもしなかったら違うAを出力するYJコード一覧のクエリ

タイトル長くなった。

すげー時間かかったけどこれでできた。

オーダーでBを優先して表示させているのがいい感じ

最初はSelectのMAXとかでやってた。

でもMAXとかの方が処理は早い

まぁUNIONするよりこれでやる方が100倍ぐらい早いのでこれでいこう

 

SELECT *
FROM `iyaku_code` AS m
WHERE `ＪＡＮコード` = (
SELECT `ＪＡＮコード`
FROM `iyaku_code`
AS s
WHERE m.`個別医薬品コード` = s.`個別医薬品コード`
order by
case when s.`A` =”B” then 1 else 2 end,
s.`A`
limit 1
) 

MySQLで重複コードを除外するクエリ

医薬品データベースでYJコードは同じでもJANコードが違うのはほとんどである。

だからYJコードを主レコードというか重複しないレコードで他のデータベースとLEFT JOINさせたい場合にYJコードが重複してると困っちゃう。

いろいろ考えた結果↓の感じで動いた。

SELECT *
FROM `iyaku_code` AS m
WHERE `ＪＡＮコード` = (
  SELECT MAX(`ＪＡＮコード`)
  FROM `iyaku_code` AS s
  WHERE m.`個別医薬品コード` = s.`個別医薬品コード`
);

Showing rows 0 – 24 (20379 total, Query took 0.0542 seconds.)

パフォーマンスもまぁまぁ良かったのでこれを使うことにしよう

MySQLの効率化

いま在庫量のデータベースをいじってる。

固定の医薬品コードとは違いこっちはひたすらデータが積み上がっていくデータベースになる。

だからこれは最初の段階でしっかり設計をしないといけない。

とりあえず現在の在庫のSQLクエリを作ってみた。

 

2つのデータベースを結合して、合計をさせてやった。

 Showing rows 0 – 24 (32 total, Query took 0.0697 seconds.)

SELECT `YJコード`,SUM( `合計` ) FROM (
(SELECT `YJコード`,-SUM( `使用数` ) as 合計 FROM `picking_finished` GROUP BY `YJコード`)
UNION
(SELECT `YJコード`,SUM( `入出数` ) as 合計 FROM `inventory_control` GROUP BY `YJコード`)
)as tmp
GROUP BY YJコード

うんまぁまぁ早い。

しかしここからYJコードからデータをとってきて引っ付けてあーだーこーだしなとイケない。

Accessさんならクエリをクエリ出来て簡単だったけどMySQLはなんかスパゲッティコードになりそうで怖い。

まぁ一つづつ作っていこう。