Defect #1951
closed
Problem with MySQL
0%
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
Related issues
Updated by Jean-Philippe Lang about 16 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?
Updated by Jean-Philippe Lang about 16 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?
Updated by Karl Heinz Marbaise about 16 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.
Updated by Karl Heinz Marbaise about 16 years ago
- Status changed from New to Resolved
Updated by Jean-Philippe Lang about 16 years ago
- Status changed from Resolved to Closed
Install doc was updated in the wiki. Thanks.
Updated by Mark McCoy over 15 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
Updated by Mark McCoy over 15 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>
Updated by Jean-Philippe Lang almost 15 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)