[HOWTO] mysql 5.0 and ansi_quotes

[colejv]

I was having a hard time getting cacti to install I kept getting this error.

Error

You have created a new database, but have not yet imported the 'cacti.sql' file. At the command line, execute the following to continue:

mysql -u cactiuser -p cacti < cacti.sql

This error may also be generated if the cacti database user does not have correct permissions on the cacti database. Please ensure that the cacti database user has the ability to SELECT, INSERT, DELETE, UPDATE, CREATE, ALTER, DROP, INDEX on the cacti database.

It turns out our production Mysql server has ansi_quotes on globally, this can be determined by running the following query.

Code: Select all

 SELECT @@global.sql_mode;  

On our server it returns

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

Since it is our production mysql server, changing the Global parameters (which would require a restart) was out of the question. My solution was to alter catcti/lib/database.php file to set the session sql_mode every time cacti connects to the database.

catcti/lib/database.php

Code: Select all

      
        while ($i <= $retries) {
                if ($cnn_id->PConnect($hostport,$user,$pass,$db_name)) {
                               $sql = "set SESSION sql_mode=''";
                               $query = $cnn_id->Execute($sql);
                                return(1);
                }

So basically I added these two lines

Code: Select all

$sql = "set SESSION sql_mode=''";
$query = $cnn_id->Execute($sql);

below this line

Code: Select all

 if ($cnn_id->PConnect($hostport,$user,$pass,$db_name)) {

So that if the connection is created the Session SQL_mode is set.

Obviously this is going to have problems with upgrades to cacti and may not work with some plugins if they build there own sql connections and use double quotes (like cacti does) instead of single quotes in their queries
Hope this helps someone

My System Info

Cacti Version - 0.8.7b
Plugin Architecture - 2.1
Poller Type - Cactid v
Server Info - FreeBSD 6.1-RELEASE-p11
Web Server - Apache/2.2.4 (FreeBSD) mod_ssl/2.2.4 OpenSSL/0.9.7e-p1 DAV/2 PHP/5.2.3 with Suhosin-Patch
PHP - 5.2.3
PHP Extensions - session, Reflection, date, libxml, apache2handler, ctype, pcre, SimpleXML, SPL, dom, filter, hash, iconv, json, mysql, PDO, pdo_sqlite, posix, standard, SQLite, tokenizer, xml, xmlreader, xmlwriter, sockets, snmp
MySQL - 5.0.51a-log
RRDTool - 1.2.23
SNMP - 5.3.1
Plugins

• Thresholds (thold - v0.3.9)
  Global Plugin Settings (settings - v0.3)
  Network Tools (tools - v0.2)
  Host Info (hostinfo - v0.2)