Can not delete row from MySQL

Posted by Drew on Stack Overflow See other posts from Stack Overflow or by Drew
Published on 2010-03-17T00:03:17Z Indexed on 2010/03/17 0:11 UTC
Read the original article Hit count: 388

Filed under:
|
|

Howdy all,

I've got a table, which won't delete a row. Specifically, when I try to delete any row with a GEO_SHAPE_ID over 150000000 it simply does not disappear from the DB.

I have tried:

  1. SQLyog to erase it.
  2. DELETE FROM TABLE WHERE GEO_SHAPE_ID = 150000042 (0 rows affected).
  3. UNLOCK TABLES then 2.

As far as I am aware, bigint is a valid candidate for auto_increment. Anyone know what could be up?

You gotta help us, Doc. We’ve tried nothin’ and we’re all out of ideas!

DJS.

PS. Here is the table construct and some sample data just for giggles.

CREATE TABLE `GEO_SHAPE` (
  `GEO_SHAPE_ID` bigint(11) NOT NULL auto_increment,
  `RADIUS` float default '0',
  `LATITUDE` float default '0',
  `LONGITUDE` float default '0',
  `SHAPE_TYPE` enum('Custom','Region') default NULL,
  `PARENT_ID` int(11) default NULL,
  `SHAPE_POLYGON` polygon default NULL,
  `SHAPE_TITLE` varchar(45) default NULL,
  `SHAPE_ABBREVIATION` varchar(45) default NULL,
  PRIMARY KEY  (`GEO_SHAPE_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=150000056 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

SET FOREIGN_KEY_CHECKS = 0;

LOCK TABLES `GEO_SHAPE` WRITE;
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (57, NULL, NULL, NULL, 'Region', 10, NULL, 'Washington', 'WA');
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (58, NULL, NULL, NULL, 'Region', 10, NULL, 'West Virginia', 'WV');
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (59, NULL, NULL, NULL, 'Region', 10, NULL, 'Wisconsin', 'WI');
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (150000042, 10, -33.8833, 151.217, 'Custom', NULL, NULL, 'Sydney%2C%20New%20South%20Wales%20%2810km%20r', NULL);
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (150000043, 10, -33.8833, 151.167, 'Custom', NULL, NULL, 'Annandale%2C%20New%20South%20Wales%20%2810km%', NULL);
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (150000048, 10, -27.5, 153.017, 'Custom', NULL, NULL, 'Brisbane%2C%20Queensland%20%2810km%20radius%2', NULL);
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (150000045, 10, 43.1002, -75.2956, 'Custom', NULL, NULL, 'New%20York%20Mills%2C%20New%20York%20%2810km%', NULL);
INSERT INTO `GEO_SHAPE` (`GEO_SHAPE_ID`, `RADIUS`, `LATITUDE`, `LONGITUDE`, `SHAPE_TYPE`, `PARENT_ID`, `SHAPE_POLYGON`, `SHAPE_TITLE`, `SHAPE_ABBREVIATION`) VALUES (150000046, 10, 40.1117, -78.9258, 'Custom', NULL, NULL, 'Region1', NULL);
UNLOCK TABLES;

SET FOREIGN_KEY_CHECKS = 1;

© Stack Overflow or respective owner

Related posts about mysql

Related posts about delete