Windows 2008R2 Cacti server crashed, SQL Assoc error

[hammondo]

Sorry for the double post - I replied to a 2009 thread with a similar topic earlier today but now things are more urgent as Cacti has been offline for 18 hours at this point. Server crashed yesterday 12:04 a.m., after that all graphs showed *#J" and in the cacti.log file I saw SQL Assoc.... error - last I checked that file is over 5 GB so it was no longer visible in the Cacti UI as of mid-morning today. I tried mysqlcheck --repair cacti but my root password must have gotten corrupted. I next tried a mysql password recovery using the mysql-init file to launch mysqld service after stopping that service and now the service will not start. As a result I cannot access the web UI. Below is the text of the mysqld --init-file= output and text from a couple of windows application errors:

Here is the console output from the password reset attempt:

536 [Note] Plugin 'FEDERATED' is disabled.
536 [Note] InnoDB: The InnoDB memory heap is disabled
536 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
536 [Note] InnoDB: Compressed tables use Windows interlocked functions
536 [Note] InnoDB: Not using CPU crc32 instructions
536 [Note] InnoDB: Initializing buffer pool, size = 128.0M
536 [Note] InnoDB: Completed initialization of buffer pool
536 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
536 [ERROR] InnoDB: The system tablespace must be writable!
536 [ERROR] Plugin 'InnoDB' init functions returned error.
536 [ERROR] Plugin 'InnoDB' restration as a STORAGE ENGINE failed.
mysqld: Too many arguments (first extra is 'server').
Use --verbose --help to get a list of available options
536 [ERROR] Aborting

536 [Note] Binlog end
536 [Note] Shutting down plugin 'partition'
536 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
536 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
536 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
536 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
536 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
536 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
536 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
536 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
536 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
536 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
536 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
536 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
536 [Note] Shutting down plugin 'INNODB_FT_DELETED'
536 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
536 [Note] Shutting down plugin 'INNOBD_METRICS;
536 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
536 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LAW'
536 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
536 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
536 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
536 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
536 [Note] Shutting down plugin 'INNODB CMPMEM'
536 [Note] Shutting down plugin 'INNODB_CMP'
536 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
536 [Note] Shutting down plugin 'INNODB_LOCKS'
536 [Note] Shutting down plugin 'INNODB_TRX'
536 [Note] Shutting down plugin 'BLACKHOLE'
536 [Note] Shutting down plugin 'ARCHIVE'
536 [Note] Shutting down plugin 'MRG_MYISAN'
536 [Note] Shutting down plugin 'MySIAM'
536 [Note] Shutting down plugin 'MEMORY'
536 [Note] Shutting down plugin 'CSU'
536 [Note] Shutting down plugin 'sha256_password'
536 [Note] Shutting down plugin 'mysql_old_password'
536 [Note] Shutting down plugin 'mysql_native_password'
536 [Note] Shutting down plugin "binlog'
536 [Note] mysqld: Shutdown complete

mysql-init.txt content:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('******');

where ***** - my password for mysql root. As of right now I have no web UI access, here is the error when I attempt to access the UI: FATAL: Cannot connect to MySQL server on 'localhost'. Please make sure you have specified a valid MySQL database name in 'include/config.php'

In Windows Event Logs>Application Logs I see various errors such as "mysqld: Incorrect key file for table '.\cacti\poller_output.MYI\; try to repair it" and "mysql thread id 827, OS thread handle 0xf84, query id 12101056 localhost 127.0.0.1 cacti update insert into poller_output (local_data_id, rrd_name, time, output) values (2813, 'traffic_out', '2016-06-22 10:14:36', '0')". Of course any assistance is certainly MOST APPRECIATED! You truly wise cacti guardians are thus (MOST APPRECIATED!).

[hammondo]

Couldn't wait any longer so a new 2012 VM was spun up and the latest installer run on it. Cacti is now running on the new server at the latest version. I'm wondering where on the old 2008 server, the database file would live? I was running 8.8.b on the old machine. I see a cacti.sql file in the C:\inetpub\wwwroot\cacti\ folder but it has a very old time stamp (2013) and is all of 177 KB. I would like to try installing MySql on a test machine and attempt to recover the cacti database password, on that test machine with the old database file if possible?

[BSOD2600]

Follow the basic guide for upgrading to get the hints on where the data resides to migrate to the new server.
http://docs.cacti.net/manual:088:1_inst ... ding_cacti

the existing collected data is in the *.rrd files... however cacti doesnt know about them any longer since that config is stored in the DB. two options. 1) restore your cacti DB from the 2008 server to 2012 (hopefully not corrupted), have cacti perform the db upgrade, update paths (if installed in diff folders), and good to go. 2) manually recreate each host and data source that was previously graphed -- after each new data source created, you'd then need to go into it and change the rrd over to the old one. yes, very time consuming and error prone

[hammondo]

Thanks for the response BSOD2006! Cacti 0.0.8h has been running fine on the 2012 server with my manually creating all the hosts. Thus far I have had no calls for any of the old data so I'm leaning towards option 3: leave things as they are;-)