Project

General

Profile

Actions

Defect #1951

closed

Problem with MySQL

Added by Karl Heinz Marbaise over 15 years ago. Updated over 14 years ago.

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

0%

Estimated time:
Resolution:
Affected version:

Description

Installed Redmine (Revision:1900) everything working fine. Updated to 1907 i got SQL Errors (see attached file). May be it might my problem, cause i I'm using MySQL 4.0.18 ?


Files

production.log (7.44 KB) production.log Karl Heinz Marbaise, 2008-09-25 21:50

Related issues

Has duplicate Redmine - Defect #2012: revision 1907 breaks mysql 4.0.27Closed2008-10-09

Actions
Actions #1

Updated by Jean-Philippe Lang over 15 years ago

I don't think this problem is related to your database version.
Did you made any change to Redmine?

Is there anybody else who having problem on the issue view since r1907?

Actions #2

Updated by Jean-Philippe Lang over 15 years ago

Actually, it seems to be related to your database version since the query that can be found in your log runs fine with Mysql 5.

SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues`  LEFT OUTER JOIN `issue_statuses`
ON `issue_statuses`.id = `issues`.status_id  LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id 
WHERE ((issue_statuses.is_closed=0) AND projects.id = 1 AND projects.status=1 AND 
EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id))

Can you try to execute this query with Mysql 4 manually?

Actions #3

Updated by Jean-Philippe Lang over 15 years ago

Works fine with mysql 4.1.22

Actions #4

Updated by Karl Heinz Marbaise over 15 years ago

Jean-Philippe Lang wrote:

Works fine with mysql 4.1.22

Ok. Tested on Comand line

sles9base:~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use redmine;
Database changed
mysql> SELECT count(DISTINCT `issues`.id) AS count_all FROM `issues`  LEFT OUTER JOIN `issue_statuses`
    -> ON `issue_statuses`.id = `issues`.status_id  LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id
    -> WHERE ((issue_statuses.is_closed=0) AND projects.id = 1 AND projects.status=1 AND
    -> EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking' AND em.project_id=projects.id))
    -> ;
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT em.id FROM enabled_modules em WHERE em.name='iss
mysql>

I think this means the installation requiremens have to be changed to MySQL 4.1.

Actions #5

Updated by Karl Heinz Marbaise over 15 years ago

  • Status changed from New to Resolved
Actions #6

Updated by Jean-Philippe Lang over 15 years ago

  • Status changed from Resolved to Closed

Install doc was updated in the wiki. Thanks.

Actions #7

Updated by Mark McCoy over 14 years ago

  • Status changed from Closed to Reopened

I am seeing a "MySQL has gone away" error whenever Redmine searches for issues. This is a fresh install of 0.8.4 on Opensolaris 2009.06, so there are no issues in the database yet.

Mysql is 5.1.30

script/about output:
About your application's environment
Ruby version 1.8.7 (i386-solaris2.11)
RubyGems version 1.3.1
Rails version 2.1.2
Active Record version 2.1.2
Action Pack version 2.1.2
Active Resource version 2.1.2
Action Mailer version 2.1.2
Active Support version 2.1.2
Edge Rails revision unknown
Application root /export/home/mmccoy/Apps/redmine-0.8.4
Environment development
Database adapter mysql

Actions #8

Updated by Mark McCoy over 14 years ago

to clarify, using the same query as in the original bug report causes a mysql error.

mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.30, for pc-solaris2.11 (i386) using readline 5.1

Connection id: 4
Current database: redmine
Current user: root@localhost
SSL: Not in use
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.1.30 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 32 min 46 sec

Threads: 1 Questions: 7 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 4 Queries per second avg: 0.3
--------------

mysql> SELECT count(DISTINCT `issues`.id) AS count_all, tracker_id AS tracker_id FROM `issues` LEFT OUTER JOIN `projects` ON `projects`.id = `issues`.project_id LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id LEFT OUTER JOIN `trackers` ON `trackers`.id = `issues`.tracker_id WHERE (((projects.id = 3 OR projects.parent_id = 3)) AND issue_statuses.is_closed=0) AND (projects.status=1) GROUP BY tracker_id;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Actions #9

Updated by Jean-Philippe Lang over 14 years ago

  • Status changed from Reopened to Closed

The "Lost connection" error is not a SQL error. The above query runs fine with mysql 5:

mysql> SELECT count(DISTINCT `issues`.id) AS count_all, tracker_id AS tracker_id FROM `issues` LEFT OUTER JOIN `projects` ON
 `projects`.id = `issues`.project_id LEFT OUTER JOIN `issue_statuses` ON `issue_statuses`.id = `issues`.status_id LEFT OUTER
 JOIN `trackers` ON `trackers`.id = `issues`.tracker_id WHERE (((projects.id = 3 OR projects.parent_id = 3)) AND 
issue_statuses.is_closed=0) AND (projects.status=1) GROUP BY tracker_id;
+-----------+------------+
| count_all | tracker_id |
+-----------+------------+
|         3 |          1 |
+-----------+------------+
1 row in set (0.05 sec)

mysql> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 5.0.51a-3ubuntu5.4 |
+--------------------+
1 row in set (0.00 sec)
Actions

Also available in: Atom PDF