Unable to upgrade - Table locks?

[Sympology]

Help, I've upgrading the server to the latest version and have a problem, it won't upgrade and now I have an unusable system.

This is the error I'm getting.

D:\NetXMS\bin>nxdbmgr upgrade
NetXMS Database Manager Version 0.2.30

Configuration file OK
Upgrading database...
Upgrading from version 97 to 98
This database upgrade requires log conversion. This can take significant amount
of time (up to few hours for large databases). If preserving all log records is
not very important, it is recommended to clear logs befor conversion. Clear logs
? (Y/N) Y
SQL query failed (The total number of locks exceeds the lock table size):
DELETE FROM syslog
Rolling back last stage due to upgrade errors...
Database upgrade failed

iI'm not a MySQL guy so have no idea where to even start to look to fix this issue. Hope you can help.

[victor]

Hi!

Try to connect to your database with mysql client and clear log tables manually by executing the following queries:

DELETE FROM syslog;
DELETE FROM event_log;
DELETE FROM snmp_trap_log;
DELETE FROM audit_log;

Then run upgrade process again.

Best regards,
Victor

[victor]

Also, there are a bug registered in MySQL bugtracker - http://bugs.mysql.com/bug.php?id=15667 which seems similar to your problem. One of possible solutions mentioned in comments is to increase value of server variable innodb_buffer_pool_size (may be useful if you use InnoDB database, not MyISAM).

Also, if manual DELETE FROM statements will not work, try to clear tables with

TRUNCATE TABLE syslog;
TRUNCATE TABLE event_log;
TRUNCATE TABLE snmp_trap_log;
TRUNCATE TABLE audit_log;

Best regards,
Victor

[Sympology]

Thanks guys, seems like a common issue with MySQL.

I found this and it's worked:

This occurs when MySQL runs out of locks, which for an InnoDB database occurs when the buffer pool is full. You can fix this by increasing the size of the buffer pool, by editng the innodb_buffer_pool_size option in my.cnf (my.ini), to set innodb_buffer_pool_size to a number higher than the default (which is typically 8M); for instance:

  innodb_buffer_pool_size = 256M

Thanks for your help.

[Sympology]

OK, so all looks ok, but running a db check and I get this:

32.84.0). Link? (Y/N) Y
* Checking node objects...                                            [FIXED ]
* Checking interface objects...                                      [PASSED]
* Checking network service objects...                                [PASSED]
* Checking cluster objects...                                        [PASSED]
* Checking template to node mapping...                                [PASSED]
* Checking object properties...                                      [PASSED]
* Checking event processing policy...                                [PASSED]
* Checking collected data...                                          SQL query
failed (Table 'netxms_db.idata_84' doesn't exist):
SELECT count(*) FROM idata_84 WHERE idata_timestamp>1256215074
SQL query failed (Table 'netxms_db.idata_86' doesn't exist):
SELECT count(*) FROM idata_86 WHERE idata_timestamp>1256215074

[Sympology]

Hit post to quick.
It says the 1 was fixed, but are those failed tables anything to be concered about?

[victor]

That means that for nodes with id 84 and 86 collected data will not be stored. I suggest to recreate these tables by the following queries:

CREATE TABLE idata_84 (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) not null);
CREATE INDEX idx_idata_84_item_id ON idata_84(item_id);
CREATE INDEX idx_idata_84_id_timestamp ON idata_84(item_id,idata_timestamp);

CREATE TABLE idata_86 (item_id integer not null,idata_timestamp integer not null,idata_value varchar(255) not null);
CREATE INDEX idx_idata_86_item_id ON idata_86(item_id);
CREATE INDEX idx_idata_86_id_timestamp ON idata_86(item_id,idata_timestamp);

Best regards,
Victor