BIG mysql db problem

[rik]

hello,

i have a problem with my cacti database.

two tables are causing me trouble:

poller_time:
it tells me "can't find poller_time.MYI" and when i go into the mysql folder there is no file there.


user_log:
it tells me "can't open user_log.MYI" the file is there but must be always in use or something like this i guess ??

i don't have realized a problem with cacti, but i am damed scared

the problem is i can't make a mysqldump (only with -f).

does anyone know how to solve the problem?

thx

[rik]

ok..

some more informations what i did now:

i created a new database with all my datas inside and created the 2 missing tables new from the original script (.sql).

i did a update from 0.8.6b to 0.8.6c....

as ever everything seems normal now! i will let it running like this now.

does anybody know why it came to this two corrupted tables?

i recently had a full hardisk partitions, where mysql resides. can this be the cause?

i just don't want this error comes again.

thank you for any informational or helping answer.

rik

[TheWitness]

If it's windows 2000/xp, messed up file systems can cause files to disappear.

TheWitness

[rik]

no it is not microsoft.

i have a

freebsd 5.2.1
apache 2
mysql 5.0.0_2 server
cacti 0.8.6b (now c)

do you think the same whould happen with this system?

thx

[TheWitness]

File system problems will affect all OS'.

TheWitness

[rik]

yes.. you are totally right.

i can imagine the cause for user_log which table was always in use (locked) whould be the full disk.

but it seems strange to me the file of the poller_time table wasn't anymore on the disk...

i am happy if i know why this problem appeard because we have about 500 devices and it whould not be funny to not know why such problem came, maybe it is more a mysql topic.

thank you for your answers
rik

[evilninja]

We have cacti version 0.8.6f
We have the same problem mentioned above :

CACTID: Poller[0] ERROR: Problem with MySQL: Can't find file: 'poller_time.MYI' (errno: 2)

Can we apply the same solution (add the table) or has anybode else have another solution ?

Thx !

[TheWitness]

Poller time is a fairly transient table. So, between polling cycles you could do the following:

mysql -u root -p cacti
> truncate table poller_time;
> quit

If this does not fix the issue, then you can take the create syntax from cacti.sql file and paste it into mysql's command line interface, or use MySQL query browser, phpMySQL, or MySQL control center. You would disable the poller first, delete the table and then execute the create script.

TheWitness

[jef]

I know that MyISAM tables tend to corrupt when the database shut's down abruptly.

When i'm in this situation, i generally:
- verify tables with >show table status
- if any table looks like crap, run a > mysqlcheck on them
- then a > mysqlrepair if needed.
Depending on urgency to fix the system...

The disapeared file seems like something else though ?


Jef

[rony]

Those 2 tables can be dropped and recreated.

Use the following Query to recreate them in the mysql client while having the cacti database selected:

Code: Select all

drop table poller_output;
drop table user_log;

CREATE TABLE user_log (
  username varchar(50) NOT NULL default '0',
  user_id mediumint(8) NOT NULL default '0',
  time datetime NOT NULL default '0000-00-00 00:00:00',
  result tinyint(1) NOT NULL default '0',
  ip varchar(15) NOT NULL default '',
  PRIMARY KEY  (username,user_id,time)
) TYPE=MyISAM;

CREATE TABLE poller_output (
  local_data_id mediumint(8) unsigned NOT NULL default '0',
  rrd_name varchar(19) NOT NULL default '',
  time datetime NOT NULL default '0000-00-00 00:00:00',
  output text NOT NULL,
  PRIMARY KEY  (local_data_id,rrd_name,time)
) TYPE=MyISAM;

This will recreate the tables in question. I would also suggest that you run a repair on the database before and after the above actions.

Questions, let me know.