title: Invalid Default Value for MYSQL Column
body: <p>I am using the Geospatial extension for MySQL. In the <code>MyISAM</code> table <code>listings</code> there is a <code>GEOMETRY</code> column <code>p</code>, and 2 <code>float</code> columns <code>lat</code> and <code>lng</code>. Values in <code>p</code> are set using <code>UPDATE listings SET p = POINT(lat, lng);</code>.</p>
  
  <p><strong>Problem:</strong> Now I want to set a spatial index on column <code>p</code>. When I tried:</p>
  
  <pre><code>ALTER TABLE listings ADD SPATIAL INDEX(p);
  </code></pre>
  
  <p>and also tried: </p>
  
  <pre><code>CREATE SPATIAL INDEX sp_index ON listings (p);
  </code></pre>
  
  <p>Both times, I get the error <code>error : All parts of a SPATIAL index must be NOT NULL</code>.</p>
  
  <p>So (using Navicat) I tried making col <code>p</code> <code>NOT NULL</code>, but it requires a default value. What default value should I use for this? I tried using <code>''</code> as the default value but it gave an error <code>invalid default value for 'p'</code>. </p>
  
  <p>I tried setting the default value for <code>p</code> as <code>POINT(0,0)</code> but I get the error <code>BLOB/TEXT column 'p' can't have a default value</code></p>
  
  <p>How should I approach this problem to create the spatial index? Thanks!</p>
  
  <h2>Edit</h2>
  
  <p>After running query <code>ALTER TABLE listings CHANGE p p POINT NOT NULL</code>, running <code>ADD SPATIAL INDEX(p)</code> causes a strange error <code>Lost connection to MySQL server during query</code>. Other queries that does not involve creating an index works fine ie. <code>SELECT * FROM listings</code></p>
  
  <p><strong>Results from <code>SHOW CREATE TABLE listings</code></strong></p>
  
  <pre><code>CREATE TABLE `listings` (
   `listing_id` int(8) NOT NULL AUTO_INCREMENT,
   `url` varchar(255) DEFAULT NULL,
   `website_city` varchar(32) DEFAULT NULL,
   `website` varchar(32) DEFAULT NULL,
   `price` int(7) DEFAULT NULL,
   `price_per_br` int(7) DEFAULT NULL,
   `bedroom` int(2) DEFAULT NULL,
   `bathroom` int(2) DEFAULT NULL,
   `fee` varchar(10) DEFAULT NULL,
   `address_1` varchar(255) DEFAULT NULL,
   `address_2` varchar(255) DEFAULT NULL,
   `city` varchar(64) DEFAULT NULL,
   `state` varchar(32) DEFAULT NULL,
   `postal` int(6) DEFAULT NULL,
   `retrival_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `post_timestamp` timestamp NULL DEFAULT NULL,
   `lat` float(10,6) DEFAULT NULL,
   `lng` float(10,6) DEFAULT NULL,
   `p` point NOT NULL,
   `description` text,
   `img_subpath` varchar(15) DEFAULT NULL,
   `photos` text,
   `reply_email` varchar(255) DEFAULT NULL,
   `phone` varchar(16) DEFAULT NULL,
   `has_dishwasher` tinyint(1) DEFAULT NULL,
   `has_laundry` tinyint(1) DEFAULT NULL,
   `has_fireplace` tinyint(1) DEFAULT NULL,
   `has_elevator` tinyint(1) DEFAULT NULL,
   `has_balcony` tinyint(1) DEFAULT NULL,
   `has_deck` tinyint(1) DEFAULT NULL,
   `has_backyard` tinyint(1) DEFAULT NULL,
   `has_parking` tinyint(1) DEFAULT NULL,
   `has_gym` tinyint(1) DEFAULT NULL,
   `has_pool` tinyint(1) DEFAULT NULL,
   `cats_ok` tinyint(1) DEFAULT NULL,
   `dogs_ok` tinyint(1) DEFAULT NULL,
   PRIMARY KEY (`listing_id`),
   KEY `lat lng` (`lat`,`lng`,`price`,`price_per_br`,`bedroom`,`bathroom`,`post_timestamp`) USING BTREE,
   KEY `spatial` (`p`(25))
  ) ENGINE=MyISAM AUTO_INCREMENT=589605 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>I converted <code>p</code> to back to <code>GEOMETRY</code>, ran the query to add the index to <code>p</code> and got a new error <code>Cannot get geometry object from data you send to the GEOMETRY field</code></p>