Mantis Bugtracker
Main | My View | View Issues | Change Log | Roadmap | Docs
  

Viewing Issue Advanced Details [ Jump to Notes ] [ View Simple ] [ Issue History ] [ Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0000580 [Cacti] Database major always 2005-09-26 08:56 2007-11-13 12:49
Reporter numa View Status public  
Assigned To TheWitness
Priority normal Resolution fixed Platform
Status closed   OS
Projection none   OS Version
ETA none Fixed in Version 0.8.6h Product Version 0.8.6f
  Target Version Product Build
Summary 0000580: cacti don't work with mysql 5.0.12
Description Hi,

Cacti don't work with mysql5.0.12 because there was a change in mysql as we can see in the in mysql5.0.12 changelog (http://dev.mysql.com/doc/mysql/en/news-5-0-12.html) [^] :

***************
Note: Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. The changes include elimination of redundant output columns specified in USING clauses and proper ordering of output columns. (Bug #4789, Bug #6136, Bug #6276, Bug #6489, Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)

This change may necessitate rewriting of certain queries. For example, the following query will work as written before 5.0.12, but now will fail with an Unknown column 't1.id' in 'on clause' error:

SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

To rewrite the query, use parentheses to group the tables in the inner join:

SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

For that particular query, it is also possible to rewrite it as a natural join:

SELECT t1.id,t2.id,t3.id FROM t1,t2 NATURAL LEFT JOIN t3
WHERE t1.id=t2.id;
**********************

Cacti use this kind off queries for example in graphs.php :

select graph_templates_graph.id,
                graph_templates_graph.local_graph_id,
                graph_templates_graph.height,
                graph_templates_graph.width,
                graph_templates_graph.title_cache,
                graph_templates.name,
                graph_local.host_id
                from graph_local,graph_templates_graph
                left join graph_templates on graph_local.graph_template_id=graph_templates.id
                where graph_local.id=graph_templates_graph.local_graph_id
Steps To Reproduce
Additional Information
Tags No tags attached.
Attached Files

- Relationships
has duplicate 0000587closed Mysql5.0.13 
related to 0000611closed cacti breaks with mysql5 because of bugs in queries 
related to 0000622closedTheWitness sql_save prevents saving new graphs 

-  Notes
(0001297)
rony (administrator)
2005-09-27 20:04
edited on: 2005-09-29 11:09

 Thank you for the additional information.
(0001301)
TheWitness (developer)
2005-10-08 18:27

 I believe that I have resolved this in SVN for the 0.8.6 BRANCH. Will proceed to the 0.9 BRANCH/TRUNK
(0001302)
TheWitness (developer)
2005-10-09 17:49

 Still working on this. I believe that I have all the selects working. However, graph creation, and I am sure certain other functions still do not work.
(0001305)
mace (reporter)
2005-10-18 18:32

 Confirming this issue exists on debian unstable with cacti 0.8.6g and mysql 5.0.13rc-1.

Going through the source sticking () around tables gets cacti going again.
No loss of service WRT data collection appears to have occurred.

Let me know if there's anything I can do to help.
(0001306)
TheWitness (developer)
2005-10-18 18:44

 You still will not be able to create things. I am sllooooowwwwllyyyy working with the ADODB author to work through the issue creating devices/graphs, etc.

TheWitness
(0001311)
TheWitness (developer)
2005-10-22 12:36

 larryjadams@comcast.net wrote:
> All,
>
> Please reference the link for MySQL 5.x. There is a quick solution to the problem I think here:
>
> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html [^]
>
> --sql-mode=MYSQL40
>
> Larry
(0001319)
chrismc (reporter)
2005-10-26 17:31

 mysql-5.0.15 is now out and released as stable. Any chance on getting fixes for MySQL5 yet?
(0001320)
TheWitness (developer)
2005-10-26 19:09

 Please read my prior post. This issue is resolved with the -sql-mode directive. Until we open up the system to multiple databases, that will be the workaround.

TheWitness
(0001321)
numa (reporter)
2005-10-27 05:33

 Hi,
I have just worked on this and i modified queries on 0.8.6g to add () in this scripts :

graphs.php,
graph_view.php,
data_sources.php,
/lib/auth.php,
/lib/html_tree.php

and now cacti works fine whith mysql 5 without --sqlmode=4.0

TheWitness , you can send me a mail at scoot7481@yahoo.com, and i will send you the modified scripts.

numa
(0001326)
chrismc (reporter)
2005-10-27 13:31
edited on: 2005-10-27 13:34

 /lib/tree.php needs an update as well to fix an SQL error (Error (Code 1292): Truncated incorrect DECIMAL value: '') that gives a warning and no result. This would surface when editing "Graph Trees" and moving an item up or down by clicking the up and down arrows. On line 153, $order_key needs single quotes around it:

151: $order = $dir == 'up' ? 'DESC' : 'ASC';
152:
153: $sql = "SELECT * FROM $table WHERE $field $arrow '$order_key' AND $field LIKE '%" . substr($order_key, ($tier * CHARS_PER_TIER))."'
154: AND $field NOT LIKE '%" . str_repeat('0', CHARS_PER_TIER) . substr($order_key, ($tier * CHARS_PER_TIER)) . "' $where ORDER BY $field $order";
155:
156: $displaced_row = db_fetch_row($sql);
(0001327)
TheWitness (developer)
2005-10-27 19:59

 Glad you brought that up, in testing, the statement db_execute("UNLOCK TABLES $table"); would result in not unlocking the table and subsequent database down condition. In addition to your patch, I changed it to db_execute("UNLOCK TABLES"); and it was resolved.
(0001373)
seanius (reporter)
2005-11-08 02:00

 fyi, this is bug #337886 in the debian BTS:

http://bugs.debian.org/337886. [^]

i've told the reporter (and others who have contacted me privately) that
i'll wait for either a new release or a patch with blessing from the cacti developers.
(0001378)
TheWitness (developer)
2005-11-08 21:14

 I am now running on MySQL 5.x and have also enhanced logging facilities to catch SQL errors. I believe you can stick a fork in MySQL, but I do want to improve the database logging some more.
(0001417)
TheWitness (developer)
2005-11-26 22:32

 Resolved in SVN

- Issue History
Date Modified Username Field Change
2005-09-26 08:56 numa New Issue
2005-09-27 20:04 rony Note Added: 0001297
2005-09-29 11:09 rony Note Edited: 0001297
2005-10-08 18:27 TheWitness Note Added: 0001301
2005-10-09 17:49 TheWitness Note Added: 0001302
2005-10-18 18:28 mace Issue Monitored: mace
2005-10-18 18:32 mace Note Added: 0001305
2005-10-18 18:44 TheWitness Note Added: 0001306
2005-10-20 21:23 rony Relationship added has duplicate 0000587
2005-10-22 12:36 TheWitness Note Added: 0001311
2005-10-26 17:31 chrismc Note Added: 0001319
2005-10-26 19:07 TheWitness Status new => assigned
2005-10-26 19:07 TheWitness Assigned To => TheWitness
2005-10-26 19:09 TheWitness Status assigned => resolved
2005-10-26 19:09 TheWitness Resolution open => won't fix
2005-10-26 19:09 TheWitness Note Added: 0001320
2005-10-27 03:54 numa Issue Monitored: numa
2005-10-27 05:33 numa Status resolved => feedback
2005-10-27 05:33 numa Resolution won't fix => reopened
2005-10-27 05:33 numa Note Added: 0001321
2005-10-27 13:31 chrismc Note Added: 0001326
2005-10-27 13:31 chrismc Note Edited: 0001326
2005-10-27 13:34 chrismc Note Edited: 0001326
2005-10-27 19:59 TheWitness Note Added: 0001327
2005-10-31 01:51 seanius Issue Monitored: seanius
2005-11-03 23:14 rony Relationship added related to 0000611
2005-11-08 02:00 seanius Note Added: 0001373
2005-11-08 21:14 TheWitness Note Added: 0001378
2005-11-11 08:19 rony Relationship added related to 0000622
2005-11-26 22:32 TheWitness Status feedback => resolved
2005-11-26 22:32 TheWitness Resolution reopened => fixed
2005-11-26 22:32 TheWitness Note Added: 0001417
2005-11-26 22:33 TheWitness Status resolved => assigned
2005-11-26 22:33 TheWitness Status assigned => resolved
2005-11-26 22:33 TheWitness Fixed in Version => 0.8.6h
2007-11-13 12:49 TheWitness Status resolved => closed

Mantis 1.1.6[^]
Copyright © 2000 - 2008 Mantis Group
Powered by Mantis Bugtracker