Can not delete row from MySQL
- by Drew
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:
SQLyog to erase it.
DELETE FROM TABLE WHERE
GEO_SHAPE_ID = 150000042 (0 rows
affected).
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;
