title: MySQL alter table modify column failing at rows with null values
body: <p>I have a table with about 10K rows, which I am trying to alter so that the field <code>fielddelimiter</code> is never null. I am attempting to do an alter statement, expecting any null values to be changed to the default value, but I get an error back from the sql statement.</p>
  
  <pre><code>alter table merchant_ftp_account modify column `fielddelimiter` char(1) NOT NULL DEFAULT 't';
  
  17:08:48  [ALTER - 0 row(s), 0.000 secs]  [Error Code: 1265, SQL State: 01000]  Data truncated for column 'fielddelimiter' at row 3987
  ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]
  </code></pre>
  
  <p>As I understand it this means that the data exceeds the field size at this row, but (a) the data in the field is (null) at that row, and (b) I am able to update that row directly with the value 't', and I don't get a truncation error. If I update that row with a nonnull value and try to re-run the alter statement, it fails at the next row where <code>fielddelimiter</code> is null. [ETA: I get that MySQL <em>could</em> update in any direction, but I can actually track its progress as I change rows.]</p>
  
  <p>There's a warning in the MySQL docs:</p>
  
  <pre><code>Warning This conversion may result in alteration of data. For example, if you shorten a
  string column, values may be truncated. To prevent the operation from succeeding if
  conversions to the new data type would result in loss of data, enable strict SQL mode
  before using ALTER TABLE (see Section 5.1.6, “Server SQL Modes”).
  </code></pre>
  
  <p>But the values that it's supposedly truncating are nulls. Can anybody explain to me what is going on here? And how to resolve it?</p>
  
  <p>[ETA: The existing <code>fielddelimiter</code> field definition is char(1) (allows nulls, no default value), so it should not have values > 1 char, and a select confirms that it does not. The distinct values in the field are NULL, '' (empty string), 'p', 't', and 'y'.]</p>