Problem with db_schema_import and MySQL timestamp!


#1

Hi,
This post concerns a SQL error while I try to import a schema into MySQL
with timestamp dates.

To reproduce try these commands:

mickey@bunny:~/dev/rails/timeonrails$ rake db_schema_dump
(in /home/mickey/dev/rails/timeonrails)

mickey@bunny:~/dev/rails/timeonrails$ cat db/schema.rb

This file is autogenerated. Instead of editing this file, please use

the

migrations feature of ActiveRecord to incrementally modify your

database, and

then regenerate this schema definition.

ActiveRecord::Schema.define() do

create_table “categories”, :force => true do |t|
t.column “name”, :string, :limit => 20
t.column “description”, :text
t.column “created_on”, :timestamp, :limit => 14
t.column “updated_on”, :timestamp, :limit => 14
end

end

Then, when I try to re-import the same schema, I get a SQL error:

mickey@bunny:~/dev/rails/timeonrails$ rake db_schema_import
(in /home/mickey/dev/rails/timeonrails)
rake aborted!
Mysql::Error: You have an error in your SQL syntax. Check the manual
that
corresponds to your MySQL server version for the right syntax to use
near ‘(14),
updated_on datetime(14)) ENGINE=InnoDB’ at line 1: CREATE TABLE
categories
(id int(11) DEFAULT NULL auto_increment PRIMARY KEY, name
varchar(20),
description text, created_on datetime(14), updated_on
datetime(14))
ENGINE=InnoDB

After executing the import with --trace option it looks like the problem
is
coming from mysql_adapter.rb file, where any reference to :timestamp is
replaced by :datetime, but the ‘datetime’ SQL command does not accept an
argument (14)!

PS: Another consequence of this error is that the table is dropped at
the same
time, that is not a good news!

I propose the following patch:

mickey@bunny:~/…/vendor/…/active_record/connection_adapters$ diff
-bru
./mysql_adapter.rb ./mysql_adapter-patched.rb


./mysql_adapter.rb
2006-02-23 12:30:19.000000000 +0100
+++
./mysql_adapter-patched.rb
2006-02-23 12:29:50.000000000 +0100
@@ -103,7 +103,7 @@
:integer => { :name => “int”, :limit => 11 },
:float => { :name => “float” },
:datetime => { :name => “datetime” },

  •      :timestamp   => { :name => "datetime" },
    
  •      :timestamp   => { :name => "timestamp" },
         :time        => { :name => "time" },
         :date        => { :name => "date" },
         :binary      => { :name => "blob" },
    

I don’t know if it’s a good idea, I am not an expert on MySQL.
So, I’m waiting for yours advises…

Bgrds.