Redmine 0.9.4.stable (JDBC) Can't create new user.

Added by Roel vdwal over 10 years ago

Incorrect integer value: 'f' for column 'mail_notification' at row 1: INSERT INTO users (login, hashed_password, firstname, lastname, mail, mail_notification, admin, status, last_login_on, language, auth_source_id, created_on, updated_on, type, identity_url) VALUES):

i come from version 0.8.4 and updated 2 weeks ago to version 0.9.2 and today to 0.9.4.

It seems that the query is false, the column "mail_notification" needs an integer instead of the string "f". But where does this error come from? What column and/or value is related to this "f"?

Replies (9)

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Felix Schäfer over 10 years ago

mail_notification is a column in the users table which is described in the migration to be boolean (which is mapped by rails to other stuff depending on what DB is used, e.g. to tinyint(1) on MySQL). I think the problem here is that the DB driver doesn't map stuff correctly and tries to set the column to the boolean value false 'f' instead of mapping it to to an int, 0 in this case.

Could you please tell us more about your setup? Which DB, what rails server, what rails and ruby versions? Or even better, go to the redmine folder and give us the output of ruby script/server RAILS_ENV=production.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

I don't think your idea of the problem is correct.
Processing UsersController#add (for 10.0.0.113 at 2010-05-03 11:37:42) [POST]
Parameters: {"authenticity_token"=>"xxxxx", "user"=>{"login"=>"MyNewUserMu", "firstname"=>"MyNewUser", "lastname"=>"xxx, "mail"=>"", "language"=>"en", "admin"=>"0"}, "password"=>"[FILTERED]", "password_confirmation"=>"[FILTERED]", "continue"=>"Maak en ga verder", "controller"=>"users", "action"=>"add"}

ActiveRecord::StatementInvalid (ActiveRecord::ActiveRecordError: Incorrect integer value: 'f' for column 'mail_notification' at row 1: INSERT INTO users (login, hashed_password, firstname, lastname, mail, mail_notification, admin, status, last_login_on, language, auth_source_id, created_on, updated_on, type, identity_url) VALUES('MyNewUserMu', 'xxxxx', 'MyNewUser', 'MyNewUser', '', 'f', '0', 1, NULL, 'en', NULL, '2010-05-03 11:37:42', '2010-05-03 11:37:42', 'User', NULL)):
gems/gems/activerecord-jdbc-adapter-0.9.5-java/lib/active_record/connection_adapters/jdbc_adapter.rb:565:in `execute'
gems/gems/activerecord-jdbc-adapter-0.9.5-java/lib/active_record/connection_adapters/jdbc_adapter.rb:589:in

As you can see in this part "_'', 'f', '0', 1,_" the "f" is on a false position, if you remove the "f" from the insert query the query is OK. since the '0', 1, are for the emailnotification and admin status.

My setup is as follows:
OS: Sun solaris10
Server: GlassfishV3 stable
Database: MySQL 5 running at separate zone
Database connection over glassfish JNDI

Ruby server output:
Summary

There are 26 gems installed:

actionmailer, actionpack, activerecord, activerecord-jdbc-adapter, activerecord-jdbc-adapter, activerecord-jdbcmysql-adapter, activerecord-jdbcmysql-adapter, activeresource, activesupport, hoe, jdbc-mysql, jruby-jars, jruby-openssl, jruby-rack, json_pure, rack, rails, rake, rspec, rubyforge, rubygems, rubytree, rubyzip, sources, warbler, warbler.
Gems

actionmailer 2.3.5 [rdoc] [www] - depends on actionpack.
Service layer for easy email delivery and testing.

actionpack 2.3.5 [rdoc] [www] - depends on activesupport, rack.
Web-flow and rendering framework putting the VC in MVC.

activerecord 2.3.5 [rdoc] [www] - depends on activesupport.
Implements the ActiveRecord pattern for ORM.

activerecord-jdbc-adapter 0.9.2 [rdoc] [www]
JDBC adapter for ActiveRecord, for use within JRuby on Rails.

activerecord-jdbc-adapter 0.9.5 [rdoc] [www]
JDBC adapter for ActiveRecord, for use within JRuby on Rails.

activerecord-jdbcmysql-adapter 0.9.2 [rdoc] [www] - depends on activerecord-jdbc-adapter, jdbc-mysql.
MySQL JDBC adapter for JRuby on Rails.

activerecord-jdbcmysql-adapter 0.9.5 [rdoc] [www] - depends on activerecord-jdbc-adapter, jdbc-mysql.
MySQL JDBC adapter for JRuby on Rails.

activeresource 2.3.5 [rdoc] [www] - depends on activesupport.
Think Active Record for web resources.

activesupport 2.3.5 [rdoc] [www]
Support and utility classes used by the Rails framework.

hoe 2.6.0 [rdoc] [www] - depends on minitest, rake, rubyforge.
Hoe is a rake/rubygems helper for project Rakefiles
Executable is sow.

jdbc-mysql 5.0.4 [rdoc] [www]
MySQL JDBC driver for Java and MySQL/ActiveRecord-JDBC.

jruby-jars 1.4.0 [rdoc] [www] - depends on hoe.
This gem includes the core JRuby code and the JRuby 1.8 stdlib as jar files

jruby-openssl 0.7 [rdoc] [www]
OpenSSL add-on for JRuby

jruby-rack 0.9.7 [rdoc] [www]
Rack adapter for JRuby and Servlet Containers

json_pure 1.4.2 [rdoc] [www]
JSON Implementation for Ruby
Executables are edit_json.rb, prettify_json.rb.

rack 1.0.1 [rdoc] [www] - depends on camping, fcgi, memcache-client, mongrel, ruby-openid, test-spec, thin.
a modular Ruby webserver interface
Executable is rackup.

rails 2.3.5 [rdoc] [www] - depends on actionmailer, actionpack, activerecord, activeresource, activesupport, rake.
Web-application framework with template engine, control-flow layer, and ORM.
Executable is rails.

rake 0.8.7 [rdoc] [www]
Ruby based make-like utility.
Executable is rake.

rspec 1.2.9 [rdoc] [www] - depends on bmabey-fakefs, cucumber, diff-lcs, heckle, hoe, syntax.
rspec 1.2.9
Executables are autospec, spec.

rubyforge 2.0.4 [rdoc] [www] - depends on json_pure.
A script which automates a limited set of rubyforge operations
Executable is rubyforge.

rubygems 1.3.5 [rdoc] [www]
RubyGems itself
Executable is gem.

rubytree 0.6.2 [rdoc] [www] - depends on gemcutter, hoe, rubyforge.
RubyTree is a Ruby implementation of the generic tree data structure

rubyzip 0.9.4 [rdoc] [www]
rubyzip is a ruby module for reading and writing zip files

sources 0.0.1 [rdoc] [www]
This package provides download sources for remote gem installation

warbler 0.9.14 [rdoc] [www] - depends on jruby-jars, rake.
Warbler chirpily constructs .war files of your Rails applications.
Executable is warble.

warbler 1.0.1 [rdoc] [www] - depends on jruby-jars, jruby-rack, rake, rubyforge, rubyzip.
Warbler chirpily constructs .war files of your Rails applications.
Executable is warble.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Felix Schäfer over 10 years ago

As far as I can see, the mapping in the query is thus:

  • login => 'MyNewUserMu'
  • hashed_password => 'xxxxx'
  • firstname => 'MyNewUser'
  • lastname => 'MyNewUser'
  • mail => ''
  • mail_notification => 'f'
  • admin => '0'
  • status => 1
  • last_login_on => NULL
  • language => 'en'
  • auth_source_id => NULL
  • created_on => '2010-05-03 11:37:42'
  • updated_on => '2010-05-03 11:37:42'
  • type => 'User'
  • identity_url => NULL

Which puts the 'f' on mail_notification. Please have a look at the schema of your production DB, you should see that mail_notification is tinyint(1) and not bool. Just to be sure: What version of MySQL 5 is it? The mysql gem does have issues with MySQL 5.1, not sure how it is with jruby though.

Again, the problem comes from some part between ActiveRecord and the DB not converting the "ruby" boolean false to a "mysql" tinyint(1) '0' but to a "mysql" bool 'f' instead, which causes mayhem in MySQL. Please file a bug, I'm not sure if anyone will be able to reproduce this though, as I'm not aware of any of the devs running solaris or jruby, but they will give you some pointers to help you find where exactly that happens. Another place you could seek for help is the IRC channel (freenode #redmine) as IIRC there are 1 or 2 other jruby users over there.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

I tested this with MySQL server 5.1.39 on Windows XP and MySQL 5.1.45 on Solaris 10. On both Redmine dies with the same error.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

I still can't solve this problem.

I don't know where the defect is created, is it in Redmine's code, within jruby, activerecord-jdbcmysql-adapter-0.9.7-java, mysql-connector.jar or in my webserver?

The resulting error is:

ActiveRecord::StatementInvalid (ActiveRecord::ActiveRecordError: Incorrect integer value: 'f' for column 'mail_notification' at row 1: INSERT INTO users (login, hashed_password, firstname, lastname, mail, mail_notification, admin, status, last_login_on, language, auth_source_id, created_on, updated_on, type, identity_url) VALUES('test', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'test', 'test', '', 'f', 'f', 1, NULL, 'nl', NULL, '2010-06-04 10:28:38', '2010-06-04 10:28:38', 'User', NULL)):

if i try to execute the above insert directly on MySQL it also errors.
So who is creating this stupid "f" value anyway?

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

please help...

I tried downgrading jruby and all kind of mysql adapters, but i still get this error on every table in de redmine-database that has boolean fields. I can go nowhere, can't create users, projects. Nothing.

I came from version 0.9.2 and have a lot of customers waiting for this service indise my redmine project. .

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Felix Schäfer over 10 years ago

Again, I don't know of any of the devs working on jruby nor does the install page mention jruby. I can only enjoin you again to seek the help of other jruby users on #redmine.

I can also again only tell you that the mysql gem doesn't support MySQL 5.1, I don't know about the jruby stuff though. Also note that on the last example you posted, it has both times 'f' for mail_notification and admin instead of 0, so I really think this is some sort of incompatibility in the activerecord-jdbc, jdbc and mysql stack. Anyway, have a look at the schema of the users table in your DB and see what column type admin and mail_notification are. Do you know if there is something akin to script/console in rails/jruby.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

thanks for your reply.

some further details:

1. i had redmine running but that was version 0.9.2 and i wanted the new updates from 0.9.4
2. i had redmine running on glassfish V3 prelude
3. i had redmine running on MySQL 5.1
(here is an excellent tutorial that works http://musingsofaprogrammingaddict.blogspot.com/2008/11/redmine-on-jruby-and-glassfish.html)

Because i had some troubles with Glassfish V3 prelude (not supporting multiple domainnames) i upgraded to the glassfish V3 final release. But some applications didn't work anymore so i downgraded to Glassfish V2.

I needed a later jruby version for redmine 0.9.4 so i installed that one, and there was also a new update of the activerecord-jdbc gem so i automaticly installed that one.

Everything seemed to work untill i tried to create a new user and /or a new project.

All columns in MySQL that give the error are tinyint(1) which means they may be interpreted as boolean.
http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

i also filled a Jira for jruby and hopefully someone will see where the problem is.

RE: Redmine 0.9.4.stable (JDBC) Can't create new user. - Added by Roel vdwal over 10 years ago

there is only one guru we can't miss on this world, it's Arun Gupta.

i installed the 0.9.2 release based on this "manual" http://musingsofaprogrammingaddict.blogspot.com/2008/11/redmine-on-jruby-and-glassfish.html and it worked flawless.

i followed the same directions for 0.9.4. and it seems i missed a spot (and the manual)

the database.yml should use the jndi connector from glassfish, this is done by:
production:
adapter: jdbc
jndi: jdbc/Redmine

BUT THIS LINE IS NOT ENOUGH!!

I found the following on Arun Gupta's blog: http://blogs.sun.com/arungupta/entry/totd_9_using_jdbc_connection

the right way for using the jndi is:
production:
adapter: jdbc
jndi: jdbc/Redmine
driver: com.mysql.jdbc.Driver

so specify which driver to use and everythings works again.

i wasted 20 hours on 1 missing line.

(1-9/9)