A Linux system froze on me and I was forced to reboot. Afterwards, when accessing tables I received the error "Invalid Sequence for DML and DDL statments (-3007)" when selecting from some tables.
We are running RedHat 7.2 and using SAP-DB version 7.4.03.27 (and not in Oracle compatible mode). Since the system froze, there were no details in the Linux logs, and unfortunately I am guilty of recreating the databases, so I don't have any knldiag[.err] information handy. Nonetheless, I'll try and provide as much details as I can. I saw a post from a few months earlier from someone else who was having the same problem. There were actually a few databases on this system, two of which had the exact same schema. Interestingly enough, I did have problems reading from the same table in both database instances. It could be a coincidence, but perhaps not. One difference between the two is that one database was new and had just been loaded up, while the other was old and had been through at least a couple of clean shutdowns and restarts of the Linux box. I also believe that this database had not had any of its data modified since the last reboot. I am not 100% sure, and don't want to lead you down the wrong path, but I feel pretty sure about this. If so, that may eliminate the possibility that DML (as well as DDL) statements are the root cause. The problematic table's schema (names changed here): create table table1 ( col1 serial (1) not null, col2 varchar(255) not null, col3 int not null, col4 smallint not null, col5 char(1), col6 timestamp not null default timestamp ) // alter table table1 add primary key (col1) // alter table table1 add foreign key (col3) references table2 (col7) // create index index1 on table1 (col2) The referenced table's schema (I could select from it without any problems): create table table2 ( col7 serial (1) not null, col8 integer not null, col9 char(255) not null, col10 int not null, col11 smallint not null, col12 char(255), col13 int, col14 timestamp not null default timestamp ) // alter table table2 add primary key (col7) // alter table table2 add foreign key (col8) references table3 (col15) In terms of reproducing the problem, I am afraid to just power off the system and see if this occurs again, since I just got things working again... I can however state that every time the Linux box has crashed (4 times in the last year(?), including due to power outage), the databases were always corrupt. The other times I was running an older version of SAP-DB, and it was normally indexes that got had problems. I like the SAP-DB database for its robustness and functionality, but stability is pretty important, too. Having to recover every time Linux crashes is very, very painful, and will only get more so as databases grow. I have not had that problem with other databases. Again, if I do a clean shutdown and reboot, everything works fine, it is just Linux crashing/freezing that causes the problem. I can understand if I lost a little bit of data because the cache was not flushed or something, but it is puzzling when catalog information apparently gets corrupted. I would think that the meta data would just have a copy read into memory from disk, so it seems strange that it could get corrupted like this. Any ideas/tips on how I can avoid this problem? Should I eschew foreign keys (I think I read an old post about problems with foreign keys)? Can I force flushing of the cache after updating the data if it is somehow related to changes in the database after all? I assume that a major focus of MaxDB will be on MySQL integration. Will you also try and improve the stability of the system, too? I am not trying to whine and complain, I am just trying to sincerely understand if having to always recover everything after a crash is going to be a problem for a while, and we need to consider moving to another database, which I do not want to do. Thanks in advance, Mark __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
