[Urgent Help!]Redmine 500 error(Mysql2::Error)

Added by li an over 4 years ago

[Env.]Redmine 2.3.2 on windows
[Background]From yesterday, I found when click to "Repository" in redmine, speed is very slow, so I try to boot mongrel, I installed mongrel gem, and run "ruby script/rails server -e production", there is such message.

=> Booting Mongrel
=> Rails 3.2.13 application starting in production on http://0.0.0.0:3000
=> Call with -d to detach
=> Ctrl-C to shutdown server

Then from today, there is interl 500 error, when developers doing "update" issue, I checked the log, there are 2 mysql erros shown below,I guess there maybe 2 reasons.

  • mongrel

I remove mongrel info. from gemfile.lock and gemfile, run bundle install again, then restart redmine service, for I am not sure if 500 error is booting mongrel leads, so I did like that.

  • accessing "repository" and fetching changesets speed is too slow influences normal redmine usage

I am not sure if the 2 are the right reasons, so hope to get some guide and solution to this issue.

By the way, , our redmine is bitnami redmine, it has apache integrated, mongrel booting is unnecessary or not?

Hope to get help....

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Started GET "/redmine/" for xxx at 2015-04-28 09:55:59 +0800
Processing by WelcomeController#index as HTML
Completed 500 Internal Server Error in 0ms

ActiveRecord::StatementInvalid (Mysql2::Error: Query execution was interrupted: SELECT `settings`.* FROM `settings` WHERE `settings`.`name` = 'session_lifetime' LIMIT 1):
app/models/setting.rb:171:in `find_or_default'
app/models/setting.rb:108:in `[]'
app/models/setting.rb:141:in `session_lifetime?'
app/controllers/application_controller.rb:64:in `session_expired?'
app/controllers/application_controller.rb:53:in `session_expiration'

Started GET "/redmine/issues/7101" for xxx at 2015-04-28 09:56:01 +0800
Processing by IssuesController#show as HTML
Parameters: {"id"=>"7101"}
Completed 500 Internal Server Error in 1045ms

ActiveRecord::StatementInvalid (Mysql2::Error: Can't connect to MySQL server on '127.0.0.1' (10061): SELECT MAX AS max_id FROM `settings` ):
app/models/setting.rb:152:in `check_cache'
app/controllers/application_controller.rb:85:in `user_setup'

Mongrel boot.gif (5.63 KB)

Replies (12)

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

There is another 500 error log when I doing fetch changeset of repository.

Completed 500 Internal Server Error in 52993ms

ActiveRecord::StatementInvalid (Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: DELETE FROM changes WHERE changes.changeset_id IN (SELECT changesets.id FROM changesets WHERE changesets.repository_id = 118)):
app/models/repository.rb:429:in `clear_changesets'
app/controllers/repositories_controller.rb:109:in `destroy'

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by Martin Denizet (redmine.org team member) over 4 years ago

Hello Li,

I scanned through your post, I've 2 comments:
  • You can disable "Auto fetching" of commits (Administration/Settings/Repositories). If it's enable you may want to disable it and replace it by a crontab or hooks.
  • Mongrel is not supported anymore for several years already. It has been re-written in the Puma project. However, from what I heard, people switching to Puma because of performance issues were disappointed. It seems it's not a magic bullet.

Cheers,

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

Hello Martin,

I disable auto fetching already, and set a crontab task each per 10 mins to fetch svn repository changesets, last year I did like that and the repository speed is solved, I donot know why it occurs again...and we got so many 500 error in the log.

I remove mongrel gem now, I think it doen't help any more, for now, I monitored, everything is fast, only when we browsing repository from redmine, the repository view shows slow and also redmine speed is slow too for other uses.

Is there any solution?

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by Martin Denizet (redmine.org team member) over 4 years ago

li an wrote:

Completed 500 Internal Server Error in 52993ms

ActiveRecord::StatementInvalid (Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: DELETE FROM changes WHERE changes.changeset_id IN (SELECT changesets.id FROM changesets WHERE changesets.repository_id = 118)):
app/models/repository.rb:429:in `clear_changesets'
app/controllers/repositories_controller.rb:109:in `destroy'

From this log, it seems like you are trying to destroy a repository.
I already experiences problems destroying a 10000+ commits repository in Redmine. I had to try several time before I was able to delete it completely as the number of database records was huge.

Some time ago, someone reported about experiments to improve Redmine's performances what worked for him was to change from Ruby 1.9.3 to Ruby 2.1.2. Maybe it worth considering.

Cheers,

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

I clear all project repository settings today,and sets to each project repository one bye one again, after setting one repository, run fetch changesets, then sets another project repository. That is what I did.

You said destroy repository, that means I clear repository settings from project or delete practical repository contents?

my Environment now:

Redmine version                2.3.2.stable
Ruby version 1.9.3-p231 (2012-05-25) [i386-mingw32]
Rails version 3.2.13
Environment production
Database adapter Mysql2

For I am using bitnami redmine, ruby is setup automatically, I am not sure how to upgrade only ruby version...

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

Our repository view speed becomes so slow from this week, I found there are so many delete files operation this week in some project svn repository, that means many projects doing delete files in SVN this week, then redmine is trying to fetch these changesets,too, does it influence the speed?

If so, after all repository fetches changesets, will the speed rever to normal...?

~~~(>_<)~~~

Martin Denizet (redmine.org team member) wrote:

li an wrote:

Completed 500 Internal Server Error in 52993ms

ActiveRecord::StatementInvalid (Mysql2::Error: Lock wait timeout exceeded; try restarting transaction: DELETE FROM changes WHERE changes.changeset_id IN (SELECT changesets.id FROM changesets WHERE changesets.repository_id = 118)):
app/models/repository.rb:429:in `clear_changesets'
app/controllers/repositories_controller.rb:109:in `destroy'

From this log, it seems like you are trying to destroy a repository.
I already experiences problems destroying a 10000+ commits repository in Redmine. I had to try several time before I was able to delete it completely as the number of database records was huge.

Some time ago, someone reported about experiments to improve Redmine's performances what worked for him was to change from Ruby 1.9.3 to Ruby 2.1.2. Maybe it worth considering.

Cheers,

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by Martin Denizet (redmine.org team member) over 4 years ago

li an wrote:

You said destroy repository, that means I clear repository settings from project or delete practical repository contents?

For fast browsing of revisions, Redmine saves all the changes of a each commit in its database (Models called ChangeSet / Change).
If you have a big repository with many commits/changes theses DB tables will be huge.

When you delete a Redmine "Repository" object from the database, all associated records (changesets and changes) will be deleted from the database.

Regarding your MySQL error, I think I'd try to take the Redmine instance offline (Stop the web server), comment out you commit fetching crontab and destroy the repository object from the Rails console. Doing so should make sure you are the only one issuing transactions to MySQL.
Better safe than sorry, I strongly advise you perform a DB backup first!

rails console production
Repository.find(<repository_id>).destroy

Alternatively, if you wish to check which transaction is preventing the lock on the record, you can open the MySQL console:

SHOW ENGINE INNODB STATUS\G 

I hope it helps!

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by Martin Denizet (redmine.org team member) over 4 years ago

I forgot to mention: after deleting a large repo would be the right time to defragment your database.

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

Do you know how to fetch changesets for one project repository? Now the changesets fetching is for all repositories.
I tested in another redmine env. and I delete all repository settings in each project from "project-settings-repository", then I set 1 project repo.again and performs fetching changesets, after the fetching is done,the repo. view speed is normal.

So I have an idea, that is to fetch changesets for each project one by one, do you know the command how to do it? I checke with redmine faq about this, but it doesn't work.

And about your suggestion to execute destroy repository in database, I am a bit confused, for I only delete subversion files in subversion, that means 1 commint contails so many deleting, but the repository is still using, destroying repo. means delete this repo., right?

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

My crontab is using windows bat script which I attached, it is for fetching changesets for all project repo.
Can I modify it to fetching some project repo.? My idea is to only fecth some projects repo. really need fetching. I think it will helps to lessen the fetching burden, what do you think so?

I run these command under redmine console, it will fetch project(id:132 in database) repo. But I donot know how to add it into the script,for if in the script
the fisrt sentence is "rails console production", and then it will going into rail production env, then stopped to wait for the command input, it cannot go on running "Repository.find(132).fetch_changesets".

rails console production
Repository.find(132).fetch_changesets

Do you have any method?

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by li an over 4 years ago

Can you explain about defragment database, what shall I do when deleting a large repo?
And deleting a large repo. means deleting practical repo. or just deleting the repo. from "project-setting-repository"?

Martin Denizet (redmine.org team member) wrote:

I forgot to mention: after deleting a large repo would be the right time to defragment your database.

RE: [Urgent Help!]Redmine 500 error(Mysql2::Error) - Added by Toshi MARUYAMA over 4 years ago

li an wrote:

[...]

Do you have any method?

You can use "ruby script/rails runner".

(1-12/12)