abdfahim Posted August 7, 2008 Share Posted August 7, 2008 I have a mysql table with large no of records. There is a DATETIME field in the table. Now, the problem is, when I browse the table with phpmyadmin, the top two records show garbage datetime like '2008-10-15 58:95:78' etc. If I delete the top two rows, then the next two becomes these garbage format. More surprisingly, when I run query searching for that exact value, it shows ZERO which means there is no record actually!!! Anyone has any idea?? Quote Link to comment Share on other sites More sharing options...
Xurion Posted August 7, 2008 Share Posted August 7, 2008 phpmyadmin wont be 'changing' the values so there is no need to delete records. Try using a different mysql browser and see what results it returns. Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 7, 2008 Author Share Posted August 7, 2008 I checked that before and the result is same. They showed it in browser window but not when I wrote query. Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 7, 2008 Author Share Posted August 7, 2008 For the convenience you guys, I put the result here. The browser window shows Name daytime BSE01 2008-24-61 48:36:48 BSE13 2008-24-67 48:36:48 But when I run the query SELECT * FROM `rel_main` WHERE `daytime`='2008-24-61 48:36:48' It says MySQL returned an empty result set (i.e. zero rows). I just copy and paste the daytime value from the browser window to the query so that i dont miss any space or something. But, still it shows zero result. I wish to post a grabbed picture of the whole scenario here but I guess this is not possible in this forum !! Quote Link to comment Share on other sites More sharing options...
Xurion Posted August 7, 2008 Share Posted August 7, 2008 I'm confused. How do you get a date of '2008-24-67' ??? Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 18, 2008 Author Share Posted August 18, 2008 Sorry for being late in reply. I'm confused. How do you get a date of '2008-24-67' ??? I know its confusing, thats why I say it Strange !! But it do display like that, but as I told, there is no values actually when I run query !! Quote Link to comment Share on other sites More sharing options...
Xurion Posted August 18, 2008 Share Posted August 18, 2008 I'd reinstall everything you're using. I've never seen mysql act that way. Quote Link to comment Share on other sites More sharing options...
Hooker Posted August 18, 2008 Share Posted August 18, 2008 Can you post your full table structure? Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 19, 2008 Author Share Posted August 19, 2008 ok .. the table has abt 30 fields .. o them 4 is VARCHAR, 1 is DAYTIME and the rests are FLOAT type. DAYTIME type field 's name is daytime, with settings NOT NULL and default 0000-00-00 00:00:00. The table has has 5 indexes -with first 4 field has nor mal indexing (no primary, no unique) each, and another index consist those 4 fields together to declare them UNIQUE. The table was INNODB, but later I changed it to MYISAM, Collation latin1_swedish_ci, pack_keys DEFAULT, no checksum, no delay_key_write. Format DYNAMIC, rows 5,626,749, Row Length 83, Row Size 138 B, Data Size 459,346 KiB, Index Size 297,929 KiB, Total Size 756,929 KiB. Huh .. I thing that's all !! Quote Link to comment Share on other sites More sharing options...
awpti Posted August 20, 2008 Share Posted August 20, 2008 SHOW CREATE TABLE table_name. Paste that, not a description of the table/field types. Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 20, 2008 Author Share Posted August 20, 2008 CREATE TABLE `cellrel_main` (\n `BSC` varchar(15) NOT NULL default '',\n `daytime` datetime NOT NULL default '0000-00-00 00:00:00',\n `Res` float default NULL,\n `CELL` varchar(15) NOT NULL default '',\n `Mo2` varchar(15) NOT NULL default '',\n `Rel` varchar(5) default NULL,\n `Id` float default NULL,\n `Cfail` float default NULL,\n `Hoasbcl` float default NULL,\n `Hoaswcl` float default NULL,\n `Hoatthr` float default NULL,\n `Hodupft` float default NULL,\n `Hodwnqa` float default NULL,\n `Hoexcta` float default NULL,\n `Hosuchr` float default NULL,\n `Hotohcs` float default NULL,\n `Hotokcl` float default NULL,\n `Hotolcl` float default NULL,\n `Houplqa` float default NULL,\n `Hoatthss` float default NULL,\n `Hoattlss` float default NULL,\n `Horttoch` float default NULL,\n `Hosucbcl` float default NULL,\n `Hosucwcl` float default NULL,\n `Hovercnt` float default NULL,\n `Hoversuc` float default NULL,\n UNIQUE KEY `BSC` (`BSC`,`daytime`,`CELL`,`Mo2`),\n KEY `BSC_2` (`BSC`),\n KEY `daytime` (`daytime`),\n KEY `CELL` (`CELL`),\n KEY `Mo2` (`Mo2`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 Quote Link to comment Share on other sites More sharing options...
fenway Posted August 20, 2008 Share Posted August 20, 2008 So you're saying you've run this query from PHPMyAdmin and it returns records, but not from your script? Also, MySQL can be very lenient in storing date/times. Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 21, 2008 Author Share Posted August 21, 2008 What i am saying that, in PHPMYADMIN, if I browse the table, the top two values always shows that strange datetime, but if I run query, it don't find those strange values. For you guys, I upload two snapshot - you can take a look on those. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 21, 2008 Share Posted August 21, 2008 What i am saying that, in PHPMYADMIN, if I browse the table, the top two values always shows that strange datetime, but if I run query, it don't find those strange values. For you guys, I upload two snapshot - you can take a look on those. Those links were useless... hence "removed". Don't try and limit them by this field... use a differnet one. Quote Link to comment Share on other sites More sharing options...
abdfahim Posted August 23, 2008 Author Share Posted August 23, 2008 Don't try and limit them by this field... use a differnet one. I dont get what you want me to do ....... Quote Link to comment Share on other sites More sharing options...
fenway Posted August 25, 2008 Share Posted August 25, 2008 Don't try and limit them by this field... use a differnet one. I dont get what you want me to do ....... I want you find these records without using the date as the search criteria. 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.