Category Archives: MySQL

wordpressでイメージなどを挿入するとそのURLがドメイン付きのフルパスになっていて、サイトを移動すると、古いドメインを参照してしまう。投稿の内容はmysqlの中にあるのでこれを変換する手段。

変換

mysqlのデータ構造はいろいろつくられているが変更する必要があるのはテーブルwp_optionsやwp_postsでいいと思う。よって以下のクエリーを実行して書き換える。

UPDATE wp_options SET option_value = REPLACE(option_value, 'old.site.example.com', 'new.site.example.com');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'old.site.example.com', 'old.site.example.com');

1 2	UPDATE wp_options SET option_value = REPLACE(option_value, 'old.site.example.com', 'new.site.example.com'); UPDATE wp_posts SET post_content = REPLACE(post_content, 'old.site.example.com', 'old.site.example.com');

old.site.example.comが古いドメインでold.site.example.comが新しいドメイン。ディレクトリ階層が異なることは想定していない。

クエリーはやり直しがきかないのでバックアップ必須。

多数の似たようなSQLを実行したい場合pythonでSQLを出力するといい

2019年7月4日

admin

多くの似たようなSQLを発行したい場合、例えばプログラムのfor文で回せば処理できるような場合どうすればいいか。一般にSQLはfor文はないから、pythonなどでスクリプトを書くのもいいかもしれない。がスクリプトでDBを開いてとか書くのが面倒。そこでpythonでSQLだけを出力して、それを実行してしまえばいい。

jupyter

こういうときのちょっとしたpythonスクリプトを書くにはjupyterが便利。
まずは１個のSQLをpythonのprintで出力してみる

print('RENAME TABLE `eitango`.`old_2019_tango_111_111` TO `eitango`.`old__2019_tango_111_111`;')

1	print('RENAME TABLE `eitango`.`old_2019_tango_111_111` TO `eitango`.`old__2019_tango_111_111`;')

次にfor文で回す。SQLはformatで置換する。

for i in range(1, 13):
    for j in range(1,21):
        print('RENAME TABLE `eitango`.`old_2019_tango_{0:03}_{1:03}` TO `eitango`.`old__2019_tango_{0:03}_{1:03}`;'.format(i,j))

for i in range(1, 13):

for j in range(1,21):

print('RENAME TABLE `eitango`.`old_2019_tango_{0:03}_{1:03}` TO `eitango`.`old__2019_tango_{0:03}_{1:03}`;'.format(i,j))

文字列中の{0:03}はformat引数の０番目の値を３桁で０で埋めることを指定している。これを実行すると以下のようなSQLが得られる。

RENAME TABLE `eitango`.`old_2019_tango_001_001` TO `eitango`.`old__2019_tango_001_001`;
RENAME TABLE `eitango`.`old_2019_tango_001_002` TO `eitango`.`old__2019_tango_001_002`;
RENAME TABLE `eitango`.`old_2019_tango_001_003` TO `eitango`.`old__2019_tango_001_003`;
RENAME TABLE `eitango`.`old_2019_tango_001_004` TO `eitango`.`old__2019_tango_001_004`;
RENAME TABLE `eitango`.`old_2019_tango_001_005` TO `eitango`.`old__2019_tango_001_005`;
RENAME TABLE `eitango`.`old_2019_tango_001_006` TO `eitango`.`old__2019_tango_001_006`;
RENAME TABLE `eitango`.`old_2019_tango_001_007` TO `eitango`.`old__2019_tango_001_007`;
RENAME TABLE `eitango`.`old_2019_tango_001_008` TO `eitango`.`old__2019_tango_001_008`;
RENAME TABLE `eitango`.`old_2019_tango_001_009` TO `eitango`.`old__2019_tango_001_009`;

RENAME TABLE `eitango`.`old_2019_tango_001_001` TO `eitango`.`old__2019_tango_001_001`;

RENAME TABLE `eitango`.`old_2019_tango_001_002` TO `eitango`.`old__2019_tango_001_002`;

RENAME TABLE `eitango`.`old_2019_tango_001_003` TO `eitango`.`old__2019_tango_001_003`;

RENAME TABLE `eitango`.`old_2019_tango_001_004` TO `eitango`.`old__2019_tango_001_004`;

RENAME TABLE `eitango`.`old_2019_tango_001_005` TO `eitango`.`old__2019_tango_001_005`;

RENAME TABLE `eitango`.`old_2019_tango_001_006` TO `eitango`.`old__2019_tango_001_006`;

RENAME TABLE `eitango`.`old_2019_tango_001_007` TO `eitango`.`old__2019_tango_001_007`;

RENAME TABLE `eitango`.`old_2019_tango_001_008` TO `eitango`.`old__2019_tango_001_008`;

RENAME TABLE `eitango`.`old_2019_tango_001_009` TO `eitango`.`old__2019_tango_001_009`;

あとはこれはphpMyAdminにコピペしたり、mysqlに直接流し込んでもいいかもしれない。DBの変更は間違うと大変なのでよくチェックしてから行おう。

MySQLのデータベースが派手に壊れた場合の修復覚書

2019年6月14日

admin

MySQL 1 Comment

ここではデータベースが派手に壊れた場合の話。ハードディスクが壊れたり、自分の場合はVMWAREで保存状態にしたまま、VirtualBoxで開いて操作したあとに、保存していたVMWAREで起動してしまったためハードディスクが派手に壊れてしまって普通のrepairでは直らなかった。

MySQLのデータにはMyISAM方式とINNODB方式があってここではMYISAMの場合、こっちのほうが修復が効きやすいらしい。

当然だがバックアップはとっておく。

どのように壊れたのかの記録

$ sudo service mysql start
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.


$ systemctl status mysql.service
œ mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: activating (start-post) (Result: exit-code) since Thu 2019-06-13 18:33:15 JST; 9s ago
  Process: 29320 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 29310 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 29320 (code=exited, status=1/FAILURE);         : 29321 (mysql-systemd-s)
    Tasks: 4
   Memory: 232.0K
      CPU: 404ms
   CGroup: /system.slice/mysql.service
           mqcontrol
             tq29321 /bin/bash /usr/share/mysql/mysql-systemd-start post
             mq29371 sleep 1

Jun 13 18:33:15 inpsrv systemd[1]: Starting MySQL Community Server...
Jun 13 18:33:18 inpsrv systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE


$ journalctl -xe
- Unit mysql.service has begun starting up.
Jun 13 18:31:39 inpsrv kernel: audit: type=1400 audit(1560418299.204:473): apparmor="DENIED" operation
Jun 13 18:31:39 inpsrv audit[29003]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld"
Jun 13 18:31:39 inpsrv audit[29003]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld"
Jun 13 18:31:39 inpsrv kernel: audit: type=1400 audit(1560418299.212:474): apparmor="DENIED" operation
Jun 13 18:31:41 inpsrv systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
~
~
$ cat /var/log/mysql/error.log
2019-06-13T09:43:51.297306Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `interscript/page_restrictions`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

$ sudo service mysql start

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

$ systemctl status mysql.service

œ mysql.service - MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

Active: activating (start-post) (Result: exit-code) since Thu 2019-06-13 18:33:15 JST; 9s ago

Process: 29320 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)

Process: 29310 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)

Main PID: 29320 (code=exited, status=1/FAILURE); : 29321 (mysql-systemd-s)

Tasks: 4

Memory: 232.0K

CPU: 404ms

CGroup: /system.slice/mysql.service

mqcontrol

tq29321 /bin/bash /usr/share/mysql/mysql-systemd-start post

mq29371 sleep 1

Jun 13 18:33:15 inpsrv systemd[1]: Starting MySQL Community Server...

Jun 13 18:33:18 inpsrv systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

$ journalctl -xe

- Unit mysql.service has begun starting up.

Jun 13 18:31:39 inpsrv kernel: audit: type=1400 audit(1560418299.204:473): apparmor="DENIED" operation

Jun 13 18:31:39 inpsrv audit[29003]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld"

Jun 13 18:31:39 inpsrv kernel: audit: type=1400 audit(1560418299.212:474): apparmor="DENIED" operation

Jun 13 18:31:41 inpsrv systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE

$ cat /var/log/mysql/error.log

2019-06-13T09:43:51.297306Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `interscript/page_restrictions`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

ユーザデータベースや特権データが壊れていると起動もできないので、以下で起動できるか確かめる。

# mysqld --skip-grant-tables

1	# mysqld --skip-grant-tables

このオプションはログインをスキップするので、特権データが壊れていても動くと思われる。

起動もしない場合

起動もしない場合は直接ファイルを触って直す。今回はMYISAM形式のファイルなので、myisamchkをつかう。MYISAMかどうかは、ファイルの拡張子がMYDやMYIならこの形式で、ibdの場合はINNODB。

以下の順番で試す。

# myisamchk -r -q broken.MYI
# myisamchk -r broken.MYI
# myisamchk --save-recover broken

# myisamchk -r -q broken.MYI

# myisamchk -r broken.MYI

# myisamchk --save-recover broken

これで直ればいいのだが、直らない場合はMYIを作り直す。MYDが実際のデータが入っているのでこのファイルがサイズが０だったり存在しない場合は復旧は無理だと思われる。

上記のmysqld –skip-grant-tablesでも起動できない場合は、すべてを初期化してから、MYDを復活するしかないが、ここではmysqldは動くものとする。

ここからmysqlでMYIを空にしてから、保存してあるMYDをもとに戻してから、復旧をすることになる。

テーブルを空にする

# mysql brokendb
> SET autocommit=1;
> TRUNCATE TABLE broken;
> quit

# mysql brokendb

> SET autocommit=1;

> TRUNCATE TABLE broken;

> quit

これで空の正常のMYIができてサイズ０のMYDができるので、保存してあるMYDを上書きコピーしてから上記のmyisanchkで復旧する。

MySQLのdatetimeに0000-00-00 00:00:00を設定できない

2019年6月14日

admin

MySQL No Comments

wordpressの昔のデータベースのデータスキームでdatetimeのデフォルト値がユーザ設定の’0000-00-00 00:00:00’になっていたのだが、MySQLの新しいバージョンではこの値は無効になったらしく、データの操作がうまくできなくなったので、この値を’1970-01-01 08:00:00’に変える。

sqlで以下を実行、post_dateは対象のコラム

UPDATE wpn_posts SET post_date = '1970-01-01 08:00:00' WHERE post_date < '0000-01-01 00:00:00';

1	UPDATE wpn_posts SET post_date = '1970-01-01 08:00:00' WHERE post_date < '0000-01-01 00:00:00';

この方法以外にも、sql_modeを設定したり、他の方法もあるようだがスルー