pulsorock Posted April 16, 2009 Share Posted April 16, 2009 I have this table and every time I insert a record, the table crashes and I need to do a repair. I have no idea what it has that is causing this. Here is the table details: /*Column Information For - videoads_impressions*/ -------------------------------------------------------- Field Type Collation Null Key Default Extra Privileges Comment --------------- ------------------- --------- ------ ------ ------- -------------- ------------------------------- ------- id bigint(20) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references creative_id int(9) unsigned (NULL) NO (NULL) select,insert,update,references zone_id int(9) unsigned (NULL) NO (NULL) select,insert,update,references impression_time timestamp (NULL) YES (NULL) select,insert,update,references /*Index Information For - videoads_impressions*/ ------------------------------------------------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- videoads_impressions 0 PRIMARY 1 id A 0 (NULL) (NULL) BTREE /*DDL Information For - videoads_impressions*/ ----------------------------------------------------- Table Create Table -------------------- -------------------------------------------------------------------------------------------------------- videoads_impressions CREATE TABLE `videoads_impressions` ( `id` bigint(20) unsigned NOT NULL auto_increment, `creative_id` int(9) unsigned NOT NULL, `zone_id` int(9) unsigned NOT NULL, `impression_time` timestamp NULL default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC When ever I run a query (like these below), I get table corrupted when I try to see the data. INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '8', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '1', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '9', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '8', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '7', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '1', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '2', '4', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '3', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); I get this when I try to view the data. Table 'videoads_impressions' is marked as crashed and should be repaired Any ideas? Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi Not seen syntax like that, with brackets around the table name and the field and value clauses, but don't think that should be the issue. Do you get the error on the first insert, or just when doing a large number? All the best Keith Quote Link to comment Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 Don't know if this is the cause but: INSERT INTO (`videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); should be INSERT INTO `videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )); Also, instead of running several insert queries, you should run one multirow insert INSERT INTO `videoads_impressions` (`id` , `creative_id` , `zone_id` , `impression_time`) VALUES (NULL , '1', '6', NOW( )), (NULL , '2', '8', NOW( )), (NULL , '3', '1', NOW( )), ... (NULL , '1', '6', NOW( )); Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 The strange thing is, that I don't get any erros when I insert new record... I get it when I try to view the data. Example, I run this query: INSERT INTO videoads_impressions (id, creative_id, zone_id, impression_time) VALUES (NULL, '1', '6', NOW( )); This is returned: (1 row(s) affected) Execution Time : 00:00:00:015 Transfer Time : 00:00:00:000 Total Time : 00:00:00:015 But when I "select * from table" then I get the table corrupted error. Quote Link to comment Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 This strange... Did you try dropping the table all together and recreating it later? (backup data first!) Oh... do you really need bigint primary_key? Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 Correction.... in the copy/paste I pasted wrong the query.... it should be pasted like these INSERT INTO videoads_impressions (id, creative_id, zone_id, impression_time) VALUES (NULL, '1', '6', NOW( )); INSERT INTO videoads_impressions (id, creative_id, zone_id, impression_time) VALUES (NULL, '2', '8', NOW( )); INSERT INTO videoads_impressions (id, creative_id, zone_id, impression_time) VALUES (NULL, '3', '1', NOW( )); [....] Either way, when I run these querys, I got no error... but for some reason, the table get corrupted.... I'm not sure if it has to be something with this row: id bigint(20) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 This strange... Did you try dropping the table all together and recreating it later? (backup data first!) Oh... do you really need bigint primary_key? The reason I have it bigint is because it will have lots of data (will be an impression tracker table)... so it could get huge.... so I guessed it would be the right option.... could that be the problem? Why is that? Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi If you repair the table and then try a "select *" before trying to insert any records does it error then? Big int is a VERY big field. It would cover a unique key for every single person in the world triggering a new record 3 times a second for 100 years. All the best Keith Quote Link to comment Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 Not sure. Probably not. Reaon I'm asking is that unsigned INT can store 2^32 values. If you were doing 100 inserts each second 24/7 you would reach that number in more than a year. Chances are, that by that time you're too rich to do database stuff by yourself. Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 Hi If you repair the table and then try a "select *" before trying to insert any records does it error then? Big int is a VERY big field. It would cover a unique key for every single person in the world triggering a new record 3 times a second for 100 years. All the best Keith I run the inserts, then I "select * from table", it tells me the table is crashed, I run repair, this is shown: videoads_impressions repair info Wrong bytesec: 3- 0- 23 at 0; Skipped videoads_impressions repair warning Number of rows changed from 9 to 0 videoads_impressions repair status OK Then, the data is deleted from the table. Then I "select * from table", and no errors are shown, but table is empty. Not sure why is happening... Quote Link to comment Share on other sites More sharing options...
kickstart Posted April 16, 2009 Share Posted April 16, 2009 Hi Only thing I can think of is that DELAY_KEY_WRITE is causing an issue (it shouldn't). All the best Keith Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 Tried changing the bigint(20) to int(20) and it happens the same. Then tried dropping the id (auto_increment) field completely, and ran these querys, and it also crashes the table INSERT INTO videoads_impressions (creative_id, zone_id, impression_time) VALUES ('3', '1', NOW( )); INSERT INTO videoads_impressions (creative_id, zone_id, impression_time) VALUES ('1', '6', NOW( )); INSERT INTO videoads_impressions (creative_id, zone_id, impression_time) VALUES ('2', '9', NOW( )); INSERT INTO videoads_impressions (creative_id, zone_id, impression_time) VALUES ('3', '6', NOW( )); INSERT INTO videoads_impressions (creative_id, zone_id, impression_time) VALUES ('1', '6', NOW( )); Is so weird.... /*Column Information For - videoads_impressions*/ -------------------------------------------------------- Field Type Collation Null Key Default Extra Privileges Comment --------------- --------- --------- ------ ------ ------- ------ ------------------------------- ------- creative_id int(9) (NULL) NO (NULL) select,insert,update,references zone_id int(9) (NULL) NO (NULL) select,insert,update,references impression_time timestamp (NULL) YES (NULL) select,insert,update,references /*Index Information For - videoads_impressions*/ ------------------------------------------------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- /*DDL Information For - videoads_impressions*/ ----------------------------------------------------- Table Create Table -------------------- -------------------------------------------------------------------- videoads_impressions CREATE TABLE `videoads_impressions` ( `creative_id` int(9) NOT NULL, `zone_id` int(9) NOT NULL, `impression_time` timestamp NULL default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CHECKSUM=1 ROW_FORMAT=DYNAMIC Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 Fixed it... I dropped the table, then recreated it using phpMyAdmin (before that, I created it using SQLyog) and this is now what I have: /*Column Information For - videoads_impressions*/ -------------------------------------------------------- Field Type Collation Null Key Default Extra Privileges Comment --------------- ------------------- --------- ------ ------ ----------------- -------------- ------------------------------- ------- id bigint(20) unsigned (NULL) NO PRI (NULL) auto_increment select,insert,update,references creative_id int(9) unsigned (NULL) NO (NULL) select,insert,update,references zone_id int(9) unsigned (NULL) NO (NULL) select,insert,update,references impression_time timestamp (NULL) NO CURRENT_TIMESTAMP select,insert,update,references /*Index Information For - videoads_impressions*/ ------------------------------------------------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment -------------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- ------- videoads_impressions 0 PRIMARY 1 id A 20 (NULL) (NULL) BTREE /*DDL Information For - videoads_impressions*/ ----------------------------------------------------- Table Create Table -------------------- ----------------------------------------------------------------------------------------------- videoads_impressions CREATE TABLE `videoads_impressions` ( `id` bigint(20) unsigned NOT NULL auto_increment, `creative_id` int(9) unsigned NOT NULL, `zone_id` int(9) unsigned NOT NULL, `impression_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 So I guess is something that SQLyog is adding to the table properties.... don't know why... Now it worries me... I created most of these tables using SQLyog... dont know if should recreate them. Quote Link to comment Share on other sites More sharing options...
Mchl Posted April 16, 2009 Share Posted April 16, 2009 You can probably use ALTER TABLE to switch it off (I guess it's CHECKSUM=1 that triggers it - the question is why) Quote Link to comment Share on other sites More sharing options...
pulsorock Posted April 16, 2009 Author Share Posted April 16, 2009 You can probably use ALTER TABLE to switch it off (I guess it's CHECKSUM=1 that triggers it - the question is why) Yes, it seems to be CHECKSUM=1 the problem.... I also wonder why is causing the problem. Anyway, I edited the tables and disabled it.... However, don't know how to edit SQLyog to have these options disabled by default when creating or editing tables. That would have saved me few hours of work today. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.