Comments
-
Greg Hurrell
Bah, looks like the root cause is that the
db/schema.rbfiles have diverged.As en example,
forums#showcalls on thelink_to_user_for_topichelper method, which creates a temporary user object so as to avoid hitting the database:User.new(:display_name => topic.last_active_user_display_name)
It then calls the
link_to_userhelper, which ends up doing:link_to user.display_name, user_path(user)
user_pathis choking on the production server, but coping fine on the local development machine. To see why, check out theUser#to_parammethod:def to_param param = (changes['display_name'] && changes['display_name'].first) User.parametrize(param || display_name) end
On the remote server:
User.new(:display_name=>'hey').changes # => {"display_name"=>["", "hey"]} User.new.display_name # => ""On the local machine:
User.new(:display_name=>'hey').changes # => {"display_name"=>[nil, "hey"]} User.new.display_name # => nilThe reason for the discrepant behavior:
The local
db/schema.rb:create_table "users", :force => true do |t| t.string "display_name", :null => false ... end
The remote
db/schema.rb:create_table "users", :force => true do |t| t.string "display_name", :default => "", :null => false ... end
Currently investigating how these ever got out of sync, but I imagine it's because in the very early days when the application was first started (ie. 2007), I must have followed some web tutorial that told me to SVN ignore the
schema.rb, log files etc... -
Greg Hurrell
Alas, I can't blame a web tutorial.
git blamereveals that the mistake was my own:commit d4c78a5b182ed4a45428bcf642fe97839e4ac9bc Author: Greg Hurrell <greg@hurrell.net> Date: Wed Jan 30 00:41:24 2008 +0100 Ignore db/schema.rb This file is easily regenerated and just creates noise in the "git status" output seeing as it changes so often, so ignore it. Signed-off-by: Greg Hurrell <greg@hurrell.net> -
Greg Hurrell
Overview of divergent tables; produced with:
$ diff --ignore-space-change -u db/schema.rb.local db/schema.rb.remote | \ grep -e 'create_table\|^+\|^-' | \ ruby -p -e 'gsub /,\s+/, " "'
Not all of these tables are so problematic, really only those which use a string as a permalink, which look to be like:
articlesconfirmationsemailsforumslinkspagespostsproductsreposresetstagsusers
--- db/schema.rb.local 2010-08-31 16:50:01.000000000 +0200 +++ db/schema.rb.remote 2010-08-31 16:27:35.000000000 +0200 create_table "articles" :force => true do |t| - t.string "title" :null => false + t.string "title" :default => "" :null => false - t.text "body" :limit => 2147483647 :null => false + t.text "body" :limit => 16777215 :null => false create_table "attachments" :force => true do |t| - t.string "digest" :null => false - t.string "path" :null => false - t.string "mime_type" :null => false + t.string "digest" :default => "" :null => false + t.string "path" :default => "" :null => false + t.string "mime_type" :default => "" :null => false - t.string "original_filename" :null => false + t.string "original_filename" :default => "" :null => false create_table "comments" :force => true do |t| - t.text "body" :limit => 2147483647 :null => false + t.text "body" :limit => 16777215 :null => false - t.string "commentable_type" :null => false + t.string "commentable_type" :default => "" :null => false create_table "confirmations" :force => true do |t| - t.string "secret" :null => false + t.string "secret" :default => "" :null => false create_table "emails" :force => true do |t| - t.string "address" :null => false + t.string "address" :default => "" :null => false create_table "forums" :force => true do |t| - t.string "name" :null => false + t.string "name" :default => "" :null => false - t.string "permalink" :null => false + t.string "permalink" :default => "" :null => false create_table "issues" :force => true do |t| - t.string "summary" :null => false + t.string "summary" :default => "" :null => false - t.text "description" :limit => 2147483647 + t.text "description" :limit => 16777215 create_table "links" :force => true do |t| - t.string "uri" :null => false + t.string "uri" :default => "" :null => false create_table "monitorships" :force => true do |t| - t.string "monitorable_type" :null => false + t.string "monitorable_type" :default => "" :null => false create_table "needles" :force => true do |t| - t.string "model_class" :null => false + t.string "model_class" :default => "" :null => false - t.string "attribute_name" :null => false - t.string "content" :null => false + t.string "attribute_name" :default => "" :null => false + t.string "content" :default => "" :null => false create_table "pages" :force => true do |t| - t.string "title" :null => false - t.string "permalink" :null => false + t.string "title" :default => "" :null => false + t.string "permalink" :default => "" :null => false create_table "posts" :force => true do |t| - t.string "title" :null => false - t.string "permalink" :null => false + t.string "title" :default => "" :null => false + t.string "permalink" :default => "" :null => false - t.text "body" :limit => 2147483647 + t.text "body" :limit => 16777215 create_table "products" :force => true do |t| - t.string "name" :null => false - t.string "permalink" :null => false + t.string "name" :default => "" :null => false + t.string "permalink" :default => "" :null => false create_table "repos" :force => true do |t| - t.string "name" :null => false - t.string "permalink" :null => false - t.string "path" :null => false + t.string "name" :default => "" :null => false + t.string "permalink" :default => "" :null => false + t.string "path" :default => "" :null => false create_table "resets" :force => true do |t| - t.string "secret" :null => false + t.string "secret" :default => "" :null => false create_table "sessions" :force => true do |t| - t.string "session_id" :null => false + t.string "session_id" :default => "" :null => false create_table "taggings" :force => true do |t| - t.string "taggable_type" :null => false + t.string "taggable_type" :default => "" :null => false create_table "tags" :force => true do |t| - t.string "name" :null => false + t.string "name" :default => "" :null => false create_table "topics" :force => true do |t| - t.string "title" :null => false - t.text "body" :limit => 2147483647 :null => false + t.string "title" :default => "" :null => false + t.text "body" :limit => 16777215 :null => false create_table "users" :force => true do |t| - t.string "display_name" :null => false - t.string "passphrase_hash" :null => false - t.string "passphrase_salt" :null => false + t.string "display_name" :default => "" :null => false + t.string "passphrase_hash" :default => "" :null => false + t.string "passphrase_salt" :default => "" :null => false
-
Greg Hurrell
Looks like the reason the schemas diverged may be due to having different versions of MySQL locally and on the server.
The server is running 5.0.45, and locally I'm running 5.1.45.
Funnily enough, the server won't let me add a default value of
NULLon aNOT NULLcolumn:mysql> ALTER TABLE articles ALTER COLUMN title SET DEFAULT NULL; ERROR 1067 (42000): Invalid default value for 'title'
And the local development machine won't let me drop the default value (well, it's not prohibited, it's just a no-op):
mysql> ALTER TABLE articles ALTER COLUMN title DROP DEFAULT; Query OK, 0 rows affected (0.04 sec)
So my options here are:
-
set an explicit default on both machines: this isn't
really desirable as my intention here behind using
NOT NULLis to declare that I expect the user to supply a value, not that I am prepared to insert an default value myself if the user doesn't fulfill that expectation -
drop the
NOT NULLconstraint and rely on application-level validations to catch user errors -
live with the existing behavior on the server and rewrite
all my
to_parammethods to special-case the "new record" case
Will need to think about it, although the second option currently seems the least ghastly one; unfortunately the forums are down in the meantime.
-
set an explicit default on both machines: this isn't
really desirable as my intention here behind using
-
Greg Hurrell
Ok, I've fixed the two issues mentioned in this ticket (failure to create new wiki articles, and bombing out on
forums#show).The idea is to make the two database schemas (development and deployment) converge again. Seeing as we are starting from different points I am not going to bother with migrations; this will all just be done in the MySQL console.
Example of fixing the two problems mentioned here
On the local machine
First up, and very importantly, audit the model code to ensure that the model attributes which we'll be changing from
NOT NULLtoNULLhave application-level constraints (validates_presence_ofvalidations) to guard against bad data from getting into the database in the course of normal operation.ALTER TABLE articles MODIFY title VARCHAR(255); -- was NOT NULL ALTER TABLE articles MODIFY body MEDIUMTEXT; -- was LONGTEXT, make it match what's on the server ALTER TABLE articles MODIFY public BOOL DEFAULT 1; -- was NOT NULL ALTER TABLE articles MODIFY accepts_comments BOOL DEFAULT 1; -- was NOT NULL ALTER TABLE users MODIFY display_name VARCHAR(255); -- was NOT NULL
On the remote server
Backup in case things go wrong:
mysqldump -u user -p database > database.sql
Then:
ALTER TABLE articles MODIFY title VARCHAR(255) DEFAULT NULL; ALTER TABLE articles MODIFY body MEDIUMTEXT DEFAULT NULL; ALTER TABLE articles MODIFY public BOOL DEFAULT 1; ALTER TABLE articles MODIFY accepts_comments BOOL DEFAULT 1; ALTER TABLE users MODIFY display_name VARCHAR(255);
It's necessary to restart the application server (
monit restart unicorn) for the app to reflect these changes, and after that the bugs are gone.So now I am going to proceed with checking the remaining tables and making sure that the schema is identical both locally and remotely, and that I'm not using
NOT NULLin places where it can cause problems, and that adequate application-level constraints exist where needed. -
Greg Hurrell
Status changed:
- From: new
- To: closed
Add a comment
Comments are now closed for this issue.