Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan
Ahoj,
vies poslat my.cnf?
Dik
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Dolezita vec, pred tym ako to budes skusat, tak vypni mysql a sprav si zalohu /var/lib/mysql (idealne aby si zachoval prava). Daj vediet, ci ti to pomohlo.
On Aug 5, 2012, at 3:50 PM, Dusan Simek pixelfields@gmail.com wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
<my.cnf>_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam zalohovat pomocou xtrabackup utilitky - https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup .
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek pixelfields@gmail.com wrote:
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote:
http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php -> take nieco si skusal? Mozes mat poskodenu integritu niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a REPAIR na vsetky tabulky vo vsetkych DB.
On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com wrote:
> Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil > mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim > mate nekdo nejakou zkusenost. Situace je nasledujici: > > Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 > zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate > spojeni. V logu vidim nasledujici: > > bud: > > OperationalError: (2013, "Lost connection to MySQL server at > 'reading initial communication packet', system error: 104") > > nebo: > > OperationalError: (2003, "Can't connect to MySQL server on > '83.167.228.14' (111)") > > nebo: > > OperationalError: (2002, "Can't connect to local MySQL server > through socket '/var/run/mysqld/mysqld.sock' (2)") > > mysql vypada, ze bezi normalne: > > netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 > 0.0.0.0:* LISTEN 26693/mysqld > > ps ax | grep mysql 12488 ? S 0:04 /bin/sh > /usr/bin/mysqld_safe 26869 ? Sl 0:02 > /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql > --user=mysql --log-error=/var/lib/mysql/msa.err > --pid-file=/var/lib/mysql/msa.pid > --socket=/var/run/mysqld/mysqld.sock --port=3306 > > socket vypada ok: > > ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 > Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. > srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock > > > Kdyz zkusim mysql restartovat, dostanu nasledujici: > > /etc/init.d/mysql restart Stopping MySQL database server: > mysqld. Starting MySQL database server: mysqld. Checking for > corrupt, not cleanly closed and upgrade needing tables.. > msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost > connection to MySQL server during query ERROR 2002 (HY000): > Can't connect to local MySQL server through socket > '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't > connect to local MySQL server through socket > '/var/run/mysqld/mysqld.sock' (2) .... > > v logu pri startu vidim toto: > > Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading > MySQL tables if necessary. Aug 5 10:26:33 msa > /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the > '--basedir' option is always ignored Aug 5 10:26:33 msa > /etc/mysql/debian-start[30664]: Looking for 'mysql' as: > /usr/bin/mysql Aug 5 10:26:33 msa > /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: > /usr/bin/mysqlcheck Aug 5 10:26:33 msa > /etc/mysql/debian-start[30664]: This installation of MySQL is > already upgraded to 5.1.63, use --force if you still need to > run mysql_upgrade Aug 5 10:26:33 msa > /etc/mysql/debian-start[30672]: Checking for insecure root > accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: > Triggering myisam-recover for all MyISAM tables > > Diky za jakykoliv tip. > > Dusan _______________________________________________ > Community-list mailing list Community-list@lists.vpsfree.cz > http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Ahoj, kazdy ma asi nejakou tu utilitku na zalohy mysql, ja uz leta pouzivam mysql-zrm:
https://zmanda.com/backup-mysql.html
Jinak diky za dalsi tip.
Dusan
On 08/06/2012 12:46 PM, Tomas Srnka wrote:
Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam zalohovat pomocou xtrabackup utilitky - https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup .
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek pixelfields@gmail.com wrote:
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
Nezkousel, zkusim, o ty poskozeny intergite jsem taky premyslel.
V priloze by mel byt my.cnf, jestli to projde.
Dusan
On 08/05/2012 03:45 PM, Tomas Srnka wrote: > > http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php > -> take nieco si skusal? Mozes mat poskodenu integritu > niektorych tabuliek. Idealne si napis skript, co zbehne CHECK a > REPAIR na vsetky tabulky vo vsetkych DB. > > On Aug 5, 2012, at 3:38 PM, Dusan Simek pixelfields@gmail.com > wrote: > >> Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil >> mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim >> mate nekdo nejakou zkusenost. Situace je nasledujici: >> >> Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 >> zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate >> spojeni. V logu vidim nasledujici: >> >> bud: >> >> OperationalError: (2013, "Lost connection to MySQL server at >> 'reading initial communication packet', system error: 104") >> >> nebo: >> >> OperationalError: (2003, "Can't connect to MySQL server on >> '83.167.228.14' (111)") >> >> nebo: >> >> OperationalError: (2002, "Can't connect to local MySQL server >> through socket '/var/run/mysqld/mysqld.sock' (2)") >> >> mysql vypada, ze bezi normalne: >> >> netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 >> 0.0.0.0:* LISTEN 26693/mysqld >> >> ps ax | grep mysql 12488 ? S 0:04 /bin/sh >> /usr/bin/mysqld_safe 26869 ? Sl 0:02 >> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql >> --user=mysql --log-error=/var/lib/mysql/msa.err >> --pid-file=/var/lib/mysql/msa.pid >> --socket=/var/run/mysqld/mysqld.sock --port=3306 >> >> socket vypada ok: >> >> ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 >> Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. >> srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock >> >> >> Kdyz zkusim mysql restartovat, dostanu nasledujici: >> >> /etc/init.d/mysql restart Stopping MySQL database server: >> mysqld. Starting MySQL database server: mysqld. Checking for >> corrupt, not cleanly closed and upgrade needing tables.. >> msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost >> connection to MySQL server during query ERROR 2002 (HY000): >> Can't connect to local MySQL server through socket >> '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't >> connect to local MySQL server through socket >> '/var/run/mysqld/mysqld.sock' (2) .... >> >> v logu pri startu vidim toto: >> >> Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading >> MySQL tables if necessary. Aug 5 10:26:33 msa >> /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the >> '--basedir' option is always ignored Aug 5 10:26:33 msa >> /etc/mysql/debian-start[30664]: Looking for 'mysql' as: >> /usr/bin/mysql Aug 5 10:26:33 msa >> /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: >> /usr/bin/mysqlcheck Aug 5 10:26:33 msa >> /etc/mysql/debian-start[30664]: This installation of MySQL is >> already upgraded to 5.1.63, use --force if you still need to >> run mysql_upgrade Aug 5 10:26:33 msa >> /etc/mysql/debian-start[30672]: Checking for insecure root >> accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: >> Triggering myisam-recover for all MyISAM tables >> >> Diky za jakykoliv tip. >> >> Dusan _______________________________________________ >> Community-list mailing list Community-list@lists.vpsfree.cz >> http://lists.vpsfree.cz/listinfo/community-list > > > _______________________________________________ Community-list > mailing list Community-list@lists.vpsfree.cz > http://lists.vpsfree.cz/listinfo/community-list >
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Ja pouzivam replikaci primo zabudovanou v MySQL :)
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
On Tue, 07 Aug 2012 07:52:59 +0200 Dusan Simek pixelfields@gmail.com wrote:
Ahoj, kazdy ma asi nejakou tu utilitku na zalohy mysql, ja uz leta pouzivam mysql-zrm:
https://zmanda.com/backup-mysql.html
Jinak diky za dalsi tip.
Dusan
On 08/06/2012 12:46 PM, Tomas Srnka wrote:
Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam zalohovat pomocou xtrabackup utilitky - https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup .
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek pixelfields@gmail.com wrote:
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote: > > Nezkousel, zkusim, o ty poskozeny intergite jsem taky > premyslel. > > V priloze by mel byt my.cnf, jestli to projde. > > Dusan > > On 08/05/2012 03:45 PM, Tomas Srnka wrote: >> >> http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php >> -> take nieco si skusal? Mozes mat poskodenu integritu >> niektorych tabuliek. Idealne si napis skript, co zbehne CHECK >> a REPAIR na vsetky tabulky vo vsetkych DB. >> >> On Aug 5, 2012, at 3:38 PM, Dusan Simek >> pixelfields@gmail.com wrote: >> >>> Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil >>> mysql. Chtel bych poprosit o info, jak to opravit, pokud s >>> tim mate nekdo nejakou zkusenost. Situace je nasledujici: >>> >>> Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 >>> zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate >>> spojeni. V logu vidim nasledujici: >>> >>> bud: >>> >>> OperationalError: (2013, "Lost connection to MySQL server at >>> 'reading initial communication packet', system error: 104") >>> >>> nebo: >>> >>> OperationalError: (2003, "Can't connect to MySQL server on >>> '83.167.228.14' (111)") >>> >>> nebo: >>> >>> OperationalError: (2002, "Can't connect to local MySQL server >>> through socket '/var/run/mysqld/mysqld.sock' (2)") >>> >>> mysql vypada, ze bezi normalne: >>> >>> netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 >>> 0.0.0.0:* LISTEN 26693/mysqld >>> >>> ps ax | grep mysql 12488 ? S 0:04 /bin/sh >>> /usr/bin/mysqld_safe 26869 ? Sl 0:02 >>> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql >>> --user=mysql --log-error=/var/lib/mysql/msa.err >>> --pid-file=/var/lib/mysql/msa.pid >>> --socket=/var/run/mysqld/mysqld.sock --port=3306 >>> >>> socket vypada ok: >>> >>> ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 >>> Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. >>> srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock >>> >>> >>> Kdyz zkusim mysql restartovat, dostanu nasledujici: >>> >>> /etc/init.d/mysql restart Stopping MySQL database server: >>> mysqld. Starting MySQL database server: mysqld. Checking for >>> corrupt, not cleanly closed and upgrade needing tables.. >>> msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost >>> connection to MySQL server during query ERROR 2002 (HY000): >>> Can't connect to local MySQL server through socket >>> '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't >>> connect to local MySQL server through socket >>> '/var/run/mysqld/mysqld.sock' (2) .... >>> >>> v logu pri startu vidim toto: >>> >>> Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading >>> MySQL tables if necessary. Aug 5 10:26:33 msa >>> /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the >>> '--basedir' option is always ignored Aug 5 10:26:33 msa >>> /etc/mysql/debian-start[30664]: Looking for 'mysql' as: >>> /usr/bin/mysql Aug 5 10:26:33 msa >>> /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: >>> /usr/bin/mysqlcheck Aug 5 10:26:33 msa >>> /etc/mysql/debian-start[30664]: This installation of MySQL is >>> already upgraded to 5.1.63, use --force if you still need to >>> run mysql_upgrade Aug 5 10:26:33 msa >>> /etc/mysql/debian-start[30672]: Checking for insecure root >>> accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: >>> Triggering myisam-recover for all MyISAM tables >>> >>> Diky za jakykoliv tip. >>> >>> Dusan _______________________________________________ >>> Community-list mailing list Community-list@lists.vpsfree.cz >>> http://lists.vpsfree.cz/listinfo/community-list >> >> >> _______________________________________________ Community-list >> mailing list Community-list@lists.vpsfree.cz >> http://lists.vpsfree.cz/listinfo/community-list >> > > > _______________________________________________ Community-list > mailing list Community-list@lists.vpsfree.cz > http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG =Fpgl -----END PGP SIGNATURE----- _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Replikace ale neřeší úplně moc zálohu.. Když se ti rozpadnou data v master databázi tak slave ti je všechny úplně na pohodu zkopíruje k sobě..
S pozdravem / Best regards Jan Harsa
17.Listopadu 118 28401 Kutná Hora Czech republic
IČ: 75494272 Mob: +420 728 036 935
Dne 7.8.2012 12:13, Tomas Volf napsal(a):
Ja pouzivam replikaci primo zabudovanou v MySQL :)
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
On Tue, 07 Aug 2012 07:52:59 +0200 Dusan Simek pixelfields@gmail.com wrote:
Ahoj, kazdy ma asi nejakou tu utilitku na zalohy mysql, ja uz leta pouzivam mysql-zrm:
https://zmanda.com/backup-mysql.html
Jinak diky za dalsi tip.
Dusan
On 08/06/2012 12:46 PM, Tomas Srnka wrote:
Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam zalohovat pomocou xtrabackup utilitky - https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup .
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek pixelfields@gmail.com wrote:
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote:
Diky ... zatim jsem tedy pokrocil takto:
1/ myisam tabulky vypadaji ok, tam repair, check, optimize proslo ok 2/ inndodb tabulky konci errorem:
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'OPTIMIZE TABLE ... '
zatim to tedy vypada ze budu muset udelat neco takoveho:
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru...
pokud tedy nemate nikdo nejaky jiny napad.
Diky.
Dusan
On 08/05/2012 04:25 PM, Pavel Snajdr wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA256 > > Prinejhorsim ti experimentalne zpristupnim zalohu a budes si > moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon > jeden z nich bude konzistentni. > > S pozdravem > > Pavel Snajdr > > +421 948 816 186 | +420 720 107 791 | 110-010-956 > CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE > http://relbit.com | http://vpsfree.cz | https://www.redhat.com > > On 08/05/2012 03:50 PM, Dusan Simek wrote: >> Nezkousel, zkusim, o ty poskozeny intergite jsem taky >> premyslel. >> >> V priloze by mel byt my.cnf, jestli to projde. >> >> Dusan >> >> On 08/05/2012 03:45 PM, Tomas Srnka wrote: >>> http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php >>> -> take nieco si skusal? Mozes mat poskodenu integritu >>> niektorych tabuliek. Idealne si napis skript, co zbehne CHECK >>> a REPAIR na vsetky tabulky vo vsetkych DB. >>> >>> On Aug 5, 2012, at 3:38 PM, Dusan Simek >>> pixelfields@gmail.com wrote: >>> >>>> Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil >>>> mysql. Chtel bych poprosit o info, jak to opravit, pokud s >>>> tim mate nekdo nejakou zkusenost. Situace je nasledujici: >>>> >>>> Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 >>>> zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate >>>> spojeni. V logu vidim nasledujici: >>>> >>>> bud: >>>> >>>> OperationalError: (2013, "Lost connection to MySQL server at >>>> 'reading initial communication packet', system error: 104") >>>> >>>> nebo: >>>> >>>> OperationalError: (2003, "Can't connect to MySQL server on >>>> '83.167.228.14' (111)") >>>> >>>> nebo: >>>> >>>> OperationalError: (2002, "Can't connect to local MySQL server >>>> through socket '/var/run/mysqld/mysqld.sock' (2)") >>>> >>>> mysql vypada, ze bezi normalne: >>>> >>>> netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 >>>> 0.0.0.0:* LISTEN 26693/mysqld >>>> >>>> ps ax | grep mysql 12488 ? S 0:04 /bin/sh >>>> /usr/bin/mysqld_safe 26869 ? Sl 0:02 >>>> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql >>>> --user=mysql --log-error=/var/lib/mysql/msa.err >>>> --pid-file=/var/lib/mysql/msa.pid >>>> --socket=/var/run/mysqld/mysqld.sock --port=3306 >>>> >>>> socket vypada ok: >>>> >>>> ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 >>>> Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. >>>> srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock >>>> >>>> >>>> Kdyz zkusim mysql restartovat, dostanu nasledujici: >>>> >>>> /etc/init.d/mysql restart Stopping MySQL database server: >>>> mysqld. Starting MySQL database server: mysqld. Checking for >>>> corrupt, not cleanly closed and upgrade needing tables.. >>>> msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost >>>> connection to MySQL server during query ERROR 2002 (HY000): >>>> Can't connect to local MySQL server through socket >>>> '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't >>>> connect to local MySQL server through socket >>>> '/var/run/mysqld/mysqld.sock' (2) .... >>>> >>>> v logu pri startu vidim toto: >>>> >>>> Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading >>>> MySQL tables if necessary. Aug 5 10:26:33 msa >>>> /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the >>>> '--basedir' option is always ignored Aug 5 10:26:33 msa >>>> /etc/mysql/debian-start[30664]: Looking for 'mysql' as: >>>> /usr/bin/mysql Aug 5 10:26:33 msa >>>> /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: >>>> /usr/bin/mysqlcheck Aug 5 10:26:33 msa >>>> /etc/mysql/debian-start[30664]: This installation of MySQL is >>>> already upgraded to 5.1.63, use --force if you still need to >>>> run mysql_upgrade Aug 5 10:26:33 msa >>>> /etc/mysql/debian-start[30672]: Checking for insecure root >>>> accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: >>>> Triggering myisam-recover for all MyISAM tables >>>> >>>> Diky za jakykoliv tip. >>>> >>>> Dusan _______________________________________________ >>>> Community-list mailing list Community-list@lists.vpsfree.cz >>>> http://lists.vpsfree.cz/listinfo/community-list >>> >>> _______________________________________________ Community-list >>> mailing list Community-list@lists.vpsfree.cz >>> http://lists.vpsfree.cz/listinfo/community-list >>> >> >> _______________________________________________ Community-list >> mailing list Community-list@lists.vpsfree.cz >> http://lists.vpsfree.cz/listinfo/community-list > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.12 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iF4EAREIAAYFAlAeglAACgkQdh+64ds5DaacSwD/eG8sDfT9ycREamDJW5D6Iny3 > 1Vy3XCg3RahKLAqrfjAA/R7jp3yPam63I5dtv/dbFTXw27WfyfUtAaXQT3UdxljG > =Fpgl > -----END PGP SIGNATURE----- > _______________________________________________ > Community-list mailing list > Community-list@lists.vpsfree.cz > http://lists.vpsfree.cz/listinfo/community-list > _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
Kluci, nechcete se na ty 1 radkovy maily presunout radsi na nas Jabber MUC? :)
Diky :]
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/07/2012 12:14 PM, Samir Djellil wrote:
Replikace není záloha :) Já používám replikaci k tomu, abych mohl dělat dumpy na slave a ne na masteru, kvůli zátěži.
Dne 7.8.2012 12:13, Tomas Volf napsal(a):
Ja pouzivam replikaci primo zabudovanou v MySQL :)
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
On Tue, 07 Aug 2012 07:52:59 +0200 Dusan Simek pixelfields@gmail.com wrote:
Ahoj, kazdy ma asi nejakou tu utilitku na zalohy mysql, ja uz leta pouzivam mysql-zrm:
https://zmanda.com/backup-mysql.html
Jinak diky za dalsi tip.
Dusan
On 08/06/2012 12:46 PM, Tomas Srnka wrote:
Ahoj,
aby si predosiel do buducna problemom s obnovou zalohy, odporucam zalohovat pomocou xtrabackup utilitky - https://prasiatko.vpsfree.cz/wiki/doku.php?id=mysql_zalohovanie#xtrabackup
.
Tomas
On Mon, Aug 6, 2012 at 10:08 AM, Dusan Simek pixelfields@gmail.com wrote:
no chybu to samozrejme vyhodi a zapise do logu neco takoveho:
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f4490cf1850): insert into events (eventid,source,object,objectid,clock,value) values (71594,0,0,13699,1343957163,2) Connection ID (thread ID): 14 Status: NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 120805 21:38:23 mysqld_safe Number of processes running now: 0 120805 21:38:23 mysqld_safe mysqld restarted 120805 21:38:23 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. 120805 21:38:23 [Note] Plugin 'FEDERATED' is disabled. 120805 21:38:23 InnoDB: Initializing buffer pool, size = 192.0M 120805 21:38:23 InnoDB: Completed initialization of buffer pool InnoDB: Log scan progressed past the checkpoint lsn 46 3585383713 120805 21:38:23 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 46 3585383743 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 397 row operations to undo InnoDB: Trx id counter is 0 302035200 InnoDB: Last MySQL binlog file position 0 532, file name ./mysql-bin.013426 InnoDB: Starting in background the rollback of uncommitted transactions InnoDB: Cleaning up trx with id 0 295634833 120805 21:38:23 InnoDB: Rollback of non-prepared transactions completed 120805 21:38:23 InnoDB: Started; log sequence number 46 3585383743 120805 21:38:23 [Note] Recovering after a crash using mysql-bin 120805 21:38:23 [Note] Starting crash recovery... 120805 21:38:23 [Note] Crash recovery finished. 120805 21:38:23 [Note] Event Scheduler: Loaded 0 events 120805 21:38:23 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.1.63-0+squeeze1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Debian) 120805 21:38:33 InnoDB: Assertion failure in thread 140313926240000 in file ../../../storage/innobase/btr/btr0cur.c line 178 InnoDB: Failing assertion: btr_page_get_next(get_page, mtr) == buf_frame_get_page_no(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
takze info o te taulce tam je, ovsem jaky koliv popus o repair skonci crashem a restartem enginu.
Takze nakonec jsem to vyresil nasledovne: dle dokumentace tady: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
jsem zmenil promennou innodb_force_recovery na 3, kdy se povedlo
vydumpovat databaze bez crache enginu. Pak odinstalovat mysql-server, smazat vse ve /var/lib/mysql, nainstalovat znova mysql-server, natahnout dumpy.
Slo by to asi opravit i z nejake starsi zalohy, ale takhle jsem mel alespon nejake relativne nejnovejsi data.
Diky.
Dusan
On 08/06/2012 09:08 AM, Jirka Bourek wrote:
Když ten mysqlcheck doběhne takhle, tak v logu DB nic není? Třeba popis chyby nebo info o tom, že se DB restartovala. Pokud si dobře vzpomínám, ve výpisu show table status by mohlo být nějaké info ke konkrétní tabulce, proč ji nejde obnovit. (Pokud nemáš nějak divně přenastavené lgoování, info o poškozených tabulkách by tam mělo být.)
Btw. nevím, proč se při opravě tabulky pouští optimize table, možná by bylo lepší zkontrolovat a opravit poškozené tabulky ručně (na verzi 5 se oprava normálně dělá sama, když se přistoupí k poškozené tabulce, tzn. ruční oprava bude potřeba jenom tam, kde je nějaký problém.)
Dusan Simek wrote: > Diky ... zatim jsem tedy pokrocil takto: > > 1/ myisam tabulky vypadaji ok, tam repair, check, > optimize proslo ok 2/ inndodb tabulky konci errorem: > > mysqlcheck: Got error: 2013: Lost connection to MySQL > server during query when executing 'OPTIMIZE TABLE ... > ' > > zatim to tedy vypada ze budu muset udelat neco > takoveho: > > > http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corru... > > > > > >
pokud tedy nemate nikdo nejaky jiny napad.
> > Diky. > > Dusan > > On 08/05/2012 04:25 PM, Pavel Snajdr wrote:
Prinejhorsim ti experimentalne zpristupnim zalohu a budes si moct vytahnout drivejsi snapshoty mysql dat, doufam ze aspon jeden z nich bude konzistentni.
S pozdravem
Pavel Snajdr
+421 948 816 186 | +420 720 107 791 | 110-010-956 CTO of Relbit | Predseda vpsFree.cz, o.s. | RHCE http://relbit.com | http://vpsfree.cz | https://www.redhat.com
On 08/05/2012 03:50 PM, Dusan Simek wrote:
>>>> Nezkousel, zkusim, o ty poskozeny intergite jsem >>>> taky premyslel. >>>> >>>> V priloze by mel byt my.cnf, jestli to projde. >>>> >>>> Dusan >>>> >>>> On 08/05/2012 03:45 PM, Tomas Srnka wrote: >>>>> http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php >>>>> >>>>> >>>>>
- -> take nieco si skusal? Mozes mat poskodenu integritu
>>>>> niektorych tabuliek. Idealne si napis skript, >>>>> co zbehne CHECK a REPAIR na vsetky tabulky vo >>>>> vsetkych DB. >>>>> >>>>> On Aug 5, 2012, at 3:38 PM, Dusan Simek >>>>> pixelfields@gmail.com wrote: >>>>> >>>>>> Ahoj chlapci, ten patecni vypadek/presun vps >>>>>> mi nejak rozbil mysql. Chtel bych poprosit o >>>>>> info, jak to opravit, pokud s tim mate nekdo >>>>>> nejakou zkusenost. Situace je nasledujici: >>>>>> >>>>>> Pri nekterych requestech (je to nahodne, 10 >>>>>> requestu ok, 11 zfailuje) dojde ke bude k >>>>>> chybe pri pripojeni, nebo ke ztrate spojeni. >>>>>> V logu vidim nasledujici: >>>>>> >>>>>> bud: >>>>>> >>>>>> OperationalError: (2013, "Lost connection to >>>>>> MySQL server at 'reading initial >>>>>> communication packet', system error: 104") >>>>>> >>>>>> nebo: >>>>>> >>>>>> OperationalError: (2003, "Can't connect to >>>>>> MySQL server on '83.167.228.14' (111)") >>>>>> >>>>>> nebo: >>>>>> >>>>>> OperationalError: (2002, "Can't connect to >>>>>> local MySQL server through socket >>>>>> '/var/run/mysqld/mysqld.sock' (2)") >>>>>> >>>>>> mysql vypada, ze bezi normalne: >>>>>> >>>>>> netstat -tupan | grep mysql tcp 0 0 >>>>>> 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld >>>>>> >>>>>> ps ax | grep mysql 12488 ? S 0:04 /bin/sh >>>>>> /usr/bin/mysqld_safe 26869 ? Sl 0:02 >>>>>> /usr/sbin/mysqld --basedir=/usr >>>>>> --datadir=/var/lib/mysql --user=mysql >>>>>> --log-error=/var/lib/mysql/msa.err >>>>>> --pid-file=/var/lib/mysql/msa.pid >>>>>> --socket=/var/run/mysqld/mysqld.sock >>>>>> --port=3306 >>>>>> >>>>>> socket vypada ok: >>>>>> >>>>>> ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 >>>>>> mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 >>>>>> root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 >>>>>> mysql mysql 0 Aug 5 15:32 mysqld.sock >>>>>> >>>>>> >>>>>> Kdyz zkusim mysql restartovat, dostanu >>>>>> nasledujici: >>>>>> >>>>>> /etc/init.d/mysql restart Stopping MySQL >>>>>> database server: mysqld. Starting MySQL >>>>>> database server: mysqld. Checking for >>>>>> corrupt, not cleanly closed and upgrade >>>>>> needing tables.. msa:.../home/dsimek > ERROR >>>>>> 2013 (HY000) at line 1: Lost connection to >>>>>> MySQL server during query ERROR 2002 >>>>>> (HY000): Can't connect to local MySQL server >>>>>> through socket '/var/run/mysqld/mysqld.sock' >>>>>> (111) ERROR 2002 (HY000): Can't connect to >>>>>> local MySQL server through socket >>>>>> '/var/run/mysqld/mysqld.sock' (2) .... >>>>>> >>>>>> v logu pri startu vidim toto: >>>>>> >>>>>> Aug 5 10:26:33 msa >>>>>> /etc/mysql/debian-start[30658]: Upgrading >>>>>> MySQL tables if necessary. Aug 5 10:26:33 >>>>>> msa /etc/mysql/debian-start[30664]: >>>>>> /usr/bin/mysql_upgrade: the '--basedir' >>>>>> option is always ignored Aug 5 10:26:33 msa >>>>>> /etc/mysql/debian-start[30664]: Looking for >>>>>> 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 >>>>>> msa /etc/mysql/debian-start[30664]: Looking >>>>>> for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug >>>>>> 5 10:26:33 msa >>>>>> /etc/mysql/debian-start[30664]: This >>>>>> installation of MySQL is already upgraded to >>>>>> 5.1.63, use --force if you still need to run >>>>>> mysql_upgrade Aug 5 10:26:33 msa >>>>>> /etc/mysql/debian-start[30672]: Checking for >>>>>> insecure root accounts. Aug 5 10:26:33 msa >>>>>> /etc/mysql/debian-start[30677]: Triggering >>>>>> myisam-recover for all MyISAM tables >>>>>> >>>>>> Diky za jakykoliv tip. >>>>>> >>>>>> Dusan >>>>>> _______________________________________________ >>>>>> >>>>>>
Community-list mailing list Community-list@lists.vpsfree.cz
>>>>>> http://lists.vpsfree.cz/listinfo/community-list >>>>> >>>>> >>>>>>
_______________________________________________ Community-list
>>>>> mailing list Community-list@lists.vpsfree.cz >>>>> http://lists.vpsfree.cz/listinfo/community-list >>>>> >>>> >>>> >>>>>
_______________________________________________ Community-list
>>>> mailing list Community-list@lists.vpsfree.cz >>>> http://lists.vpsfree.cz/listinfo/community-list >> _______________________________________________ >> Community-list mailing list >> Community-list@lists.vpsfree.cz >> http://lists.vpsfree.cz/listinfo/community-list >> > _______________________________________________ > Community-list mailing list > Community-list@lists.vpsfree.cz > http://lists.vpsfree.cz/listinfo/community-list _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
_______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
Mne niečo podobné zvyknú tiež len tak zriedka vracať databázy, ale obvykle na komerčných hostingoch, kde majú samostatný stroj pre databázu (napríklad bývalý kongo hosting). Paradox je, že tie chyby sa vyskytujú skôr v noci, než cez deň (v náhodných časoch), ale niekedy nastanú pri niektorých kľúčových cronoch, čo nie je moc dobré. Mám odchytávač mysql chýb a keď sa nejakému mysql serveru takto "nechce", tak mám potom ráno zaspamovanú schránku. :)
Preto ak na niečo prídeš (alebo niekto iný) a bude to možné ovplyvniť z pozície klienta (teda bez prístupu ku configom mysql), budem len rád.
Vďaka.
On 05.08.2012 15:38, Dusan Simek wrote:
Ahoj chlapci, ten patecni vypadek/presun vps mi nejak rozbil mysql. Chtel bych poprosit o info, jak to opravit, pokud s tim mate nekdo nejakou zkusenost. Situace je nasledujici:
Pri nekterych requestech (je to nahodne, 10 requestu ok, 11 zfailuje) dojde ke bude k chybe pri pripojeni, nebo ke ztrate spojeni. V logu vidim nasledujici:
bud:
OperationalError: (2013, "Lost connection to MySQL server at 'reading initial communication packet', system error: 104")
nebo:
OperationalError: (2003, "Can't connect to MySQL server on '83.167.228.14' (111)")
nebo:
OperationalError: (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
mysql vypada, ze bezi normalne:
netstat -tupan | grep mysql tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 26693/mysqld
ps ax | grep mysql 12488 ? S 0:04 /bin/sh /usr/bin/mysqld_safe 26869 ? Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/msa.err --pid-file=/var/lib/mysql/msa.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
socket vypada ok:
ls -la /var/run/mysqld/ total 8 drwxr-xr-x 2 mysql mysql 4096 Aug 5 15:32 . drwxr-xr-x 20 root root 4096 Aug 5 10:46 .. srwxrwxrwx 1 mysql mysql 0 Aug 5 15:32 mysqld.sock
Kdyz zkusim mysql restartovat, dostanu nasledujici:
/etc/init.d/mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. msa:.../home/dsimek > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ....
v logu pri startu vidim toto:
Aug 5 10:26:33 msa /etc/mysql/debian-start[30658]: Upgrading MySQL tables if necessary. Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysql' as: /usr/bin/mysql Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Aug 5 10:26:33 msa /etc/mysql/debian-start[30664]: This installation of MySQL is already upgraded to 5.1.63, use --force if you still need to run mysql_upgrade Aug 5 10:26:33 msa /etc/mysql/debian-start[30672]: Checking for insecure root accounts. Aug 5 10:26:33 msa /etc/mysql/debian-start[30677]: Triggering myisam-recover for all MyISAM tables
Diky za jakykoliv tip.
Dusan _______________________________________________ Community-list mailing list Community-list@lists.vpsfree.cz http://lists.vpsfree.cz/listinfo/community-list
community-list@lists.vpsfree.cz
-
Dusan Simek -
Jan Harsa -
Jirka Bourek -
Ján Šmatlík -
Pavel Snajdr -
Tomas Srnka -
Tomas Volf