Flyspray - The bug killer!

“If debugging is the process of removing bugs, then programming must be the process of putting them in.” – Edsger Dijkstra

This is the Bug Tracking System for the Flyspray project. This is not a demo! Before opening a new task, please read the guidelines!

Do not issue bugs reports against versions earlier than 0.9.9.5

Security problem? Check the security section.

Tasklist

FS#1208 - characters in french task descriptions incorrectly displayed after 0.9.8 to 0.9.9 update

Attached to Project: Flyspray - The bug killer!
Opened by Frédéric Jaume (soulfish) - Wednesday, 07 March 2007, 13:11 GMT+2
Last edited by Florian Schmitz (Floele) - Wednesday, 07 March 2007, 17:15 GMT+2
Task Type Bug Report
Category Rendering
Status Closed
Assigned To No-one
Operating System All
Severity Medium
Priority Normal
Reported Version 0.9.9
Due in Version Undecided
Due Date Undecided
Percent Complete 100%
Votes 0
Private No

Details

After upgrading a 0.9.8 version (which itself had been upgraded from previous versions too, 0.9.6 beeing the first one I think) to a 0.9.9 version, I get incorrectly displaying characters in all data read from the database, including comments, task names, task details, user names...

I use french language, so all data in flyspray contains accentuated characters like é,à,è ... as well as quotes (’) and doubles quotes (”).

All accents are displayed incorrectly, and quotes, and doubles quotes appear preceeded with 2 antislahes “\”.

Everything displayed fine in version 0.9.8, and no MySQL of PHP upgrades have been done to our web server.

I have found the following solution after realizing that the DB had fields in the latin1 charset, but containing utf8 data:

Treat each field containing potentially incorrect characters with this series of queries (this method has been used in Wordpress updates which had the same issues apparently):

ALTER TABLE flyspray_tasks MODIFY detailed_desc BLOB ;
ALTER TABLE flyspray_tasks charset=utf8 ;
ALTER TABLE flyspray_tasks MODIFY detailed_desc longtext CHARACTER SET utf8 ;

For the quotes and double quotes issues, I had to do this:

UPDATE flyspray_comments SET comment_text=REPLACE(comment_text, ‘\‘, ) WHERE comment_text LIKE ‘%\%’ ;
UPDATE flyspray_comments SET comment_text=REPLACE(comment_text, ‘\”‘, ‘“‘) WHERE comment_text LIKE ‘%\”%’ ;

After that, all data in my DB is ok, and displays fine in version 0.9.9.

I posted about this problem in this thread: http://forum.flyspray.org/viewtopic.php?id=54

I’m joining the complete list of queries I used to get correctly displayed entries. I did not update text fields which contain dates, I guess it’s not useful.

   upgrade_flyspray_charset.sql (6.8 KiB)
This task depends upon

Closed by  Florian Schmitz (Floele)
Wednesday, 07 March 2007, 17:15 GMT+2
Reason for closing:  Not a bug
Comment by Florian Schmitz (Floele) - Wednesday, 07 March 2007, 17:14 GMT+2

Thanks for the queries, we’ll keep them for reference (I’ll add it to the FAQ).

Comment by Frédéric Jaume (soulfish) - Wednesday, 07 March 2007, 18:02 GMT+2

I also noticed that my full name, which includes accents is not displayer correctly right here on this Flyspray DB. ;)

Comment by Florian Schmitz (Floele) - Wednesday, 07 March 2007, 18:31 GMT+2

Nope. Feel free to correct it. We got a bad database dump of the Flyspray database when migrating, so we have these problems here.

Comment by Florian Schmitz (Floele) - Wednesday, 07 March 2007, 18:34 GMT+2

And btw, for the slashes issue (from old 0.9.9 sql script): 

UPDATE flyspray_admin_requests SET
	 reason_given = REPLACE(REPLACE(reason_given, "\'", "'"), '\"', '"'),
	 time_submitted = REPLACE(REPLACE(time_submitted, "\'", "'"), '\"', '"'),
	 time_resolved = REPLACE(REPLACE(time_resolved, "\'", "'"), '\"', '"'),
	 deny_reason = REPLACE(REPLACE(deny_reason, "\'", "'"), '\"', '"');
 
UPDATE flyspray_attachments SET
	 orig_name = REPLACE(REPLACE(orig_name, "\'", "'"), '\"', '"'),
	 file_name = REPLACE(REPLACE(file_name, "\'", "'"), '\"', '"'),
	 file_desc = REPLACE(REPLACE(file_desc, "\'", "'"), '\"', '"'),
	 file_type = REPLACE(REPLACE(file_type, "\'", "'"), '\"', '"'),
	 date_added = REPLACE(REPLACE(date_added, "\'", "'"), '\"', '"');
 
UPDATE flyspray_comments SET
	 date_added = REPLACE(REPLACE(date_added, "\'", "'"), '\"', '"'),
	 comment_text = REPLACE(REPLACE(comment_text, "\'", "'"), '\"', '"');
 
UPDATE flyspray_groups SET
	 group_name = REPLACE(REPLACE(group_name, "\'", "'"), '\"', '"'),
	 group_desc = REPLACE(REPLACE(group_desc, "\'", "'"), '\"', '"');
 
UPDATE flyspray_history SET
	 event_date = REPLACE(REPLACE(event_date, "\'", "'"), '\"', '"'),
	 field_changed = REPLACE(REPLACE(field_changed, "\'", "'"), '\"', '"'),
	 old_value = REPLACE(REPLACE(old_value, "\'", "'"), '\"', '"'),
	 new_value = REPLACE(REPLACE(new_value, "\'", "'"), '\"', '"');
 
UPDATE flyspray_list_category SET
	 category_name = REPLACE(REPLACE(category_name, "\'", "'"), '\"', '"');
 
UPDATE flyspray_list_os SET
	 os_name = REPLACE(REPLACE(os_name, "\'", "'"), '\"', '"');
 
UPDATE flyspray_list_resolution SET
	 resolution_name = REPLACE(REPLACE(resolution_name, "\'", "'"), '\"', '"');
 
UPDATE flyspray_list_tasktype SET
	 tasktype_name = REPLACE(REPLACE(tasktype_name, "\'", "'"), '\"', '"');
 
UPDATE flyspray_list_version SET
	 version_name = REPLACE(REPLACE(version_name, "\'", "'"), '\"', '"');
 
UPDATE flyspray_notification_messages SET
	 message_subject = REPLACE(REPLACE(message_subject, "\'", "'"), '\"', '"'),
	 message_body = REPLACE(REPLACE(message_body, "\'", "'"), '\"', '"'),
	 time_created = REPLACE(REPLACE(time_created, "\'", "'"), '\"', '"');
 
UPDATE flyspray_notification_recipients SET
	 notify_method = REPLACE(REPLACE(notify_method, "\'", "'"), '\"', '"'),
	 notify_address = REPLACE(REPLACE(notify_address, "\'", "'"), '\"', '"');
 
UPDATE flyspray_prefs SET
	 pref_name = REPLACE(REPLACE(pref_name, "\'", "'"), '\"', '"'),
	 pref_value = REPLACE(REPLACE(pref_value, "\'", "'"), '\"', '"'),
	 pref_desc = REPLACE(REPLACE(pref_desc, "\'", "'"), '\"', '"');
 
UPDATE flyspray_projects SET
	 project_title = REPLACE(REPLACE(project_title, "\'", "'"), '\"', '"'),
	 theme_style = REPLACE(REPLACE(theme_style, "\'", "'"), '\"', '"'),
	 intro_message = REPLACE(REPLACE(intro_message, "\'", "'"), '\"', '"'),
	 visible_columns = REPLACE(REPLACE(visible_columns, "\'", "'"), '\"', '"'),
	 notify_email = REPLACE(REPLACE(notify_email, "\'", "'"), '\"', '"'),
	 notify_jabber = REPLACE(REPLACE(notify_jabber, "\'", "'"), '\"', '"');
 
UPDATE flyspray_registrations SET
	 reg_time = REPLACE(REPLACE(reg_time, "\'", "'"), '\"', '"'),
	 confirm_code = REPLACE(REPLACE(confirm_code, "\'", "'"), '\"', '"'),
	 user_name = REPLACE(REPLACE(user_name, "\'", "'"), '\"', '"'),
	 real_name = REPLACE(REPLACE(real_name, "\'", "'"), '\"', '"'),
	 email_address = REPLACE(REPLACE(email_address, "\'", "'"), '\"', '"'),
	 jabber_id = REPLACE(REPLACE(jabber_id, "\'", "'"), '\"', '"'),
	 magic_url = REPLACE(REPLACE(magic_url, "\'", "'"), '\"', '"');
 
UPDATE flyspray_reminders SET
	 start_time = REPLACE(REPLACE(start_time, "\'", "'"), '\"', '"'),
	 last_sent = REPLACE(REPLACE(last_sent, "\'", "'"), '\"', '"'),
	 reminder_message = REPLACE(REPLACE(reminder_message, "\'", "'"), '\"', '"');
 
UPDATE flyspray_tasks SET
	 date_opened = REPLACE(REPLACE(date_opened, "\'", "'"), '\"', '"'),
	 date_closed = REPLACE(REPLACE(date_closed, "\'", "'"), '\"', '"'),
	 closure_comment = REPLACE(REPLACE(closure_comment, "\'", "'"), '\"', '"'),
	 item_summary = REPLACE(REPLACE(item_summary, "\'", "'"), '\"', '"'),
	 detailed_desc = REPLACE(REPLACE(detailed_desc, "\'", "'"), '\"', '"'),
	 last_edited_time = REPLACE(REPLACE(last_edited_time, "\'", "'"), '\"', '"'),
	 due_date = REPLACE(REPLACE(due_date, "\'", "'"), '\"', '"');
 
UPDATE flyspray_users SET
	 user_name = REPLACE(REPLACE(user_name, "\'", "'"), '\"', '"'),
	 user_pass = REPLACE(REPLACE(user_pass, "\'", "'"), '\"', '"'),
	 real_name = REPLACE(REPLACE(real_name, "\'", "'"), '\"', '"'),
	 jabber_id = REPLACE(REPLACE(jabber_id, "\'", "'"), '\"', '"'),
	 email_address = REPLACE(REPLACE(email_address, "\'", "'"), '\"', '"'),
	 dateformat = REPLACE(REPLACE(dateformat, "\'", "'"), '\"', '"'),
	 dateformat_extended = REPLACE(REPLACE(dateformat_extended, "\'", "'"), '\"', '"'),
	 magic_url = REPLACE(REPLACE(magic_url, "\'", "'"), '\"', '"');
Comment by Florian Schmitz (Floele) - Friday, 06 April 2007, 11:42 GMT+2

Extended script, includes cache.

   upgrade_flyspray_charset.sql (7.2 KiB)
Comment by Cristian Rodríguez R. (judas_iscariote) - Wednesday, 30 May 2007, 14:56 GMT+2

the above sql script has some bugs.

you can also try the following PHP script, place it in you flsyrpay root directory but dont forget to take a database backup first

it was only tested with mysql 5, older versions may or may not work

   convert.php (1.4 KiB)

Loading...