So, in the Drupal 6 version of a lot of my modules, I made prodigious use of the datetime column type. Now, Drupal 7 says those pesky data-specific column types are off-limits. I created a hook_update_N function to convert datetime columns into char columns that looks like this:
function hook_update_N() { db_change_field( "foo", "column_1", "column_1", array( "type" => "char", "length" => 20, "not null" => true, "default" => "0000-00-00 00:00:00", ) );
db_change_field( "foo", "column_2", "column_2", array( "type" => "char", "length" => 20, "not null" => true, "default" => "0000-00-00 00:00:00", ) ); }
And when I run update.php, I now get:
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'column_2': ALTER TABLE {foo} CHANGE `created_on` `column_1` CHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00'; Array ( ) in db_change_field() (line 2984 of includes/database/database.inc).
Please not that it's complaining about the default value on column_2 when it's trying to update column_1.
How does one convert a table with two or more datetime columns to char(20) columns in Drupal 7?
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Tim Gustafson tjg@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Doh! Sorry about that; I incorrectly anonymized the error message. It should have said:
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'column_2': ALTER TABLE {foo} CHANGE `column_1` `column_1` CHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00'; Array ( ) in db_change_field() (line 2984 of includes/database/database.inc).
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Tim Gustafson tjg@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
On Wed, Jan 18, 2012 at 2:38 PM, Tim Gustafson wrote:
Doh! Sorry about that; I incorrectly anonymized the error message. It should have said:
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'column_2': ALTER TABLE {foo} CHANGE `column_1` `column_1` CHAR(20) NOT NULL DEFAULT '0000-00-00 00:00:00'; Array ( ) in db_change_field() (line 2984 of includes/database/database.inc).
You've declared a CHAR with length of 20 and only have 19 characters in the default. The string length of CHAR is static and not variable as with VARCHAR.
You've declared a CHAR with length of 20 and only have 19 characters in the default. The string length of CHAR is static and not variable as with VARCHAR.
Nope, that's not it.
For one thing, CHAR columns are not required to fill out all the character space they have allocated to them; I have countless other CHAR columns with max lengths of 255 characters and a default of an empty string, and that's no problem.
And anyhow, changing the column lengths to 19 does not make the error go away.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Tim Gustafson tjg@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-
Earnie Boyd -
Tim Gustafson