SQL crashed, Need Help

[teraquad]

Hi,
I have corruption due to power failure and mysql is complaining of a table crash. Following is the error reported by zm.

Code: Select all

SQL-ERR 'Table './zm/Events' is marked as crashed and should be repaired', statement was 'select count(if(1 and ( E.MonitorId = 1 ),1,NULL)) as EventCount0, count(if(1 and ( E.StartTime >= '2012-08-15 03:43:54' and E.MonitorId = 1 ),1,NULL)) as EventCount1, count(if(1 and ( E.StartTime >= '2012-08-14 04:43:54' and E.MonitorId = 1 ),1,NULL)) as EventCount2, count(if(1 and ( E.StartTime >= '2012-08-08 04:43:54' and E.MonitorId = 1 ),1,NULL)) as EventCount3, count(if(1 and ( E.StartTime >= '2012-07-15 04:43:54' and E.MonitorId = 1 ),1,NULL)) as EventCount4, count(if(1 and ( E.Archived = 1 and E.MonitorId = 1 ),1,NULL)) as EventCount5 from Events as E where MonitorId = '1''

I have tried the (mysqlcheck --all-databases --auto-repair -pyour_database_password -u your_databse_user) numerous times with no results.
Also i have tried (myisamchk --silent --force --fast --update-state -O key_buffer=64M -Osort_buffer=64M -O read_buffer=1M -O write_buffer=1M /var/lib/mysql/*/*.MYI ) with no results as well.

Code: Select all

george@zoneminder zm1$ myisamchk --silent --force --fast --update-state -O key_buffer=64M -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M /var/lib/mysql/*/*.MYI 
myisamchk: unknown option '-O'
george@zoneminder zm1$ 

I don't really want to lose my database about .9gb but if there is a simple way to start over i will live with it.

Thanks for any help .

[Christo]

Hello teraquad,

Recently because of a sudden powerdown I had the same problem and did solve this by a repair executed with Phpmyadmin.

I am not familiar with the exact string to execute this from the commandline, so I can't tell you if your commands are correct.

I assume you did stop the ZoneMinder service before your repair?!

Kind regards and good luck, Christo

[teraquad]

Hi Christo,

The command i am using comes from many post on mysql problems. However, I have no idea of its functional use. I have installed phpmyadmin and don't know the commands to bring it up. I did stop the mysql server to preform the command mentioned but it reported an error in reaching the server.

In short I am very confused. As to where i go next. I guess i need to find a step by step procedure.

Thanks for your help!

[teraquad]

Good News Problem Solved.

Hi Christo,
I used the command below being very careful to see that zm was stopped and using root instead of my username. Also i had no password so i just hit enter when prompted.

Code: Select all

mysqlcheck --all-databases --auto-repair -u root -p

Thank you very much for the help.

[Christo]

hello teraquad,

I am pleased to read that this issue is solved!!

About your Phpmyadmin installation, I am sure that it stumbled on the password issue.

In most case, the username is just "root"
The password is than simular to your MSQL password

If your in.. Just select the database (zm) and tables will unfold on the right side.

In case of repair:
- select all tables
- a pulldown menu somewhere at the bottom (midle of your screen) select repair.
- your done:)

In case of a event reset: (reset event-count back to zero (0)
- select table events
- in the topmenu select Empty (= truncate)
- your done:)

Goodluck and regards, Christo

[mastertheknife]

Code: Select all

REPAIR TABLE `Events`;

Should be enough.
mastertheknife