Project

General

Profile

Actions

Defect #20831

closed

journal_details table reverted value and old_value back to String on update from 2.6.0 to 3.1.1

Added by John Janus over 8 years ago. Updated about 8 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Invalid
Affected version:

Description

I recently upgraded our redmine installation from 2.6.0 to 3.1.1.
Since then it has not been possible to edit descriptions.
The log shows the following error:

Mysql2::Error: Data too long for column 'value' at row 1: INSERT INTO `journal_details` (`property`, `prop_key`, `old_value`, `value`, `journal_id`) VALUES ('attr', 'description', '<long description here>',2068)
The same error message appears for old_value.

I found Defect #15483, which describes a similar problem.
A quick check in the database shows, that the type for old_value and value is indeed varchar(255).
BUT in the database for the 2.6.0 version, those columns had varchar(255) as type as well, though editing a description was possible there.

Version 2.6.0 was installed without prior data on a windows server with the bitnami stack (mysql as database).
3.1.1 was installed on a linux server with mariadb 10.0.20. the database was dumped from the windows mysql server and read into the mariadb without problems.


Files

sqlite3-to-mysql.py (1.08 KB) sqlite3-to-mysql.py translate a sqlite3 dump into MySQL format. Anonymous, 2016-01-04 10:23
dump_error.log (13.1 KB) dump_error.log Full error stack Anonymous, 2016-01-04 10:55

Related issues

Related to Redmine - Defect #15483: Error 500 on Issue Update (postgres)Closed

Actions
Has duplicate Redmine - Defect #23741: unable to modify the issue descriptionClosed

Actions
Actions #1

Updated by Go MAEDA over 8 years ago

  • Status changed from New to Needs feedback

No problem with my installation of 3.1.1. journal_details table have not been changed since Redmine 1.1 (r4954).

$ grep -l journal_details db/migrate/*
db/migrate/007_create_journals.rb
db/migrate/20110227125750_change_journal_details_values_to_text.rb

Could you describe detailed upgrade procedure you have done?

Actions #2

Updated by Go MAEDA over 8 years ago

  • Related to Defect #15483: Error 500 on Issue Update (postgres) added
Actions #3

Updated by John Janus over 8 years ago

I installed Redmine on the Linux machine, created a dump from the database on the Windows box, put that into the new database and started
"bundle exec rake db:migrate RAILS_ENV=production"
as described at http://www.redmine.org/projects/redmine/wiki/RedmineUpgrade

It might be possible, that the initial bitnami Windows install was already defective, as the type of the fields are set to varchar(255) on the Windows box as well.
It was however never a problem to change the description on that install.
the commands i issued were:

mysqldump -h<winmachine> -u<user> -p<pass> bitnami_redmine > redminedump.sql
mysql -u<user> -p<pass> -D redmine < redminedump.sql
bundle exec rake db:migrate RAILS_ENV=production

After changing the types to text, the issue is solved.

Actions #4

Updated by Toshi MARUYAMA over 8 years ago

  • Status changed from Needs feedback to Closed
  • Resolution set to Invalid

Thank you for your feedback.

Actions #5

Updated by Anonymous about 8 years ago

Hi,

I get the same issue (full stack attached) :

Mysql2::Error: Data too long for column 'old_value' at row 880: ALTER TABLE `journal_details` CHANGE `old_value` `old_value` varchar(255) DEFAULT NULL/home/qes/.rvm/gems/ruby-2.2.1/gems/activerecord-4.2.5/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:305:in `query'

I'm upgrading from Redmine 1.x

About your application's environment
Ruby version 1.8.7 (x86_64-linux)
RubyGems version 1.8.15
Rack version 1.3
Rails version 2.3.14
Active Record version 2.3.14
Active Resource version 2.3.14
Action Mailer version 2.3.14
Active Support version 2.3.14
Edge Rails revision unknown
Application root /usr/share/redmine
Environment production
Database adapter sqlite3
Database schema version 20110902000000

To Redmine 3.x

Environment:
Redmine version 3.2.0.stable
Ruby version 2.2.1-p85 (2015-02-26) [x86_64-linux]
Rails version 4.2.5
Environment production
Database adapter Mysql2
SCM:
Subversion 1.6.17
Git 1.7.10.4
Filesystem 
Redmine plugins:
no plugin installed

I dump my sqlite3 and use a script to translate the dump to MySQL format (cf. attachment)

I can see in sqlite3 and MySQL dump this kind of line that is clearly larger than 255 chars :

INSERT INTO "journal_details" VALUES(1171,1055,'attr','description','En profil documentaliste, les bordereau de recherche ne tient pas dans l''écran^M
==> nécessité d''utiliser la barre de scroll pour valider.^M
^M
Demande : faire tenir la fenêtre dans l''écran.^M
','En profil documentaliste, les bordereaux de recherche ne tiennent pas dans l''écran^M
==> nécessité d''utiliser la barre de scroll pour valider.^M
^M
Demande : faire tenir la fenêtre dans l''écran.^M
');

What I don't understand, is that the column old_value is already varchar(255), but data bigger than 255 was inserted in it without problem.

Actions #6

Updated by Anonymous about 8 years ago

Well, for now, I shrinked too large values with this queries :

use redmine;
update journal_details set old_value = left(old_value, 255) where length(old_value) > 255;
update journal_details set value = left(value, 255) where length(value) > 255;
Actions #7

Updated by Toshi MARUYAMA about 8 years ago

Patrick Ferreira wrote:

What I don't understand, is that the column old_value is already varchar(255), but data bigger than 255 was inserted in it without problem.

old_value should be text, not varchar(255).

Actions #8

Updated by Toshi MARUYAMA over 7 years ago

  • Related to Defect #23741: unable to modify the issue description added
Actions #9

Updated by Toshi MARUYAMA over 7 years ago

  • Related to deleted (Defect #23741: unable to modify the issue description)
Actions #10

Updated by Toshi MARUYAMA over 7 years ago

  • Has duplicate Defect #23741: unable to modify the issue description added
Actions

Also available in: Atom PDF