[HOWTO] mysql 5.0 and ansi

FAQ

Memberlist

Profile

[HOWTO] mysql 5.0 and ansi_quotes

Cacti Forum Index -> Informational/HOWTO's

Author

Message

colejv

Joined: 19 Mar 2008
Posts: 12

Posted: Wed Mar 26, 2008 9:05 am Post subject: [HOWTO] mysql 5.0 and ansi_quotes

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

Quote:

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 @@global.sql_mode;

On our server it returns

Quote:

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:

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:

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

below this line

Code:

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

Quote:

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

Display posts from previous:

	Cacti Forum Index -> Informational/HOWTO's	All times are GMT - 5 Hours
Page 1 of 1