[icinga-checkins] icinga.org: icinga-core/mfriedrich/mysql: remove mysql binary selects, use case-sensitive collation instead (William Preston)

git at icinga.org git at icinga.org
Tue Apr 20 16:12:38 CEST 2010


Module: icinga-core
Branch: mfriedrich/mysql
Commit: 5d08bca4d928323a576efd90886836b61e6f0f92
URL:    https://git.icinga.org/?p=icinga-core.git;a=commit;h=5d08bca4d928323a576efd90886836b61e6f0f92

Author: Michael Friedrich <michael.friedrich at univie.ac.at>
Date:   Tue Apr 20 16:03:37 2010 +0200

remove mysql binary selects, use case-sensitive collation instead (William Preston)

The binary selects fail to use indexes, causing poor performance during restart/ reload.

added upgrade path for mysql

fixes #267
fixes #361
fixes #365

---

 Changelog                                        |    1 +
 module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql |   20 ++++++++++
 module/idoutils/db/mysql/mysql.sql               |   10 +++--
 module/idoutils/db/oracle/oracle.sql             |    2 +
 module/idoutils/db/pgsql/pgsql.sql               |   22 +++++------
 module/idoutils/src/dbhandlers.c                 |   44 +++++-----------------
 6 files changed, 49 insertions(+), 50 deletions(-)

diff --git a/Changelog b/Changelog
index 4cc8344..7f09f17 100644
--- a/Changelog
+++ b/Changelog
@@ -5,6 +5,7 @@ Icinga 1.0.x Change Log
 1.0.2 - 30/06/2010
 
 ENHANCEMENTS
+* idoutils: remove mysql binary selects, use case-sensitive collation instead (William Preston)
 * idoutils: Spread the list of linked lists thinner (extend objects hash slots) (Opsera Ltd)
 * idoutils: change select * into select [*_]id if unused results
 
diff --git a/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql b/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql
new file mode 100644
index 0000000..c368976
--- /dev/null
+++ b/module/idoutils/db/mysql/mysql-upgrade-1.0.2.sql
@@ -0,0 +1,20 @@
+-- --------------------------------------------------------
+-- mysql-upgrade-1.0.2.sql
+-- DB definition for MySQL
+-- 
+-- Copyright (c) 2010 Icinga Development Team (http://www.icinga.org)
+-- 
+-- Changes:
+-- no more binary casts, instead updating collation to case-sensitive 
+--
+-- -- --------------------------------------------------------
+
+-- -----------------------------------------
+-- change collation to case-sensitive
+-- -----------------------------------------
+
+ALTER TABLE `icinga_hosts` MODIFY COLUMN `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '';
+ALTER TABLE `icinga_objects` MODIFY COLUMN `name1` varchar(128) character set latin1 collate latin1_general_cs NOT NULL default '';
+ALTER TABLE `icinga_objects` MODIFY COLUMN `name2` varchar(128) character set latin1 collate latin1_general_cs default NULL;
+ALTER TABLE `icinga_services` MODIFY COLUMN `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '';
+
diff --git a/module/idoutils/db/mysql/mysql.sql b/module/idoutils/db/mysql/mysql.sql
index 67a6dde..f40befd 100644
--- a/module/idoutils/db/mysql/mysql.sql
+++ b/module/idoutils/db/mysql/mysql.sql
@@ -2,6 +2,8 @@
 -- mysql.sql
 -- DB definition for MySQL
 -- 
+-- 
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
 --
 -- -- --------------------------------------------------------
 
@@ -623,7 +625,7 @@ CREATE TABLE IF NOT EXISTS `icinga_hosts` (
   `config_type` smallint(6) NOT NULL default '0',
   `host_object_id` int(11) NOT NULL default '0',
   `alias` varchar(64) character set latin1 NOT NULL default '',
-  `display_name` varchar(64) character set latin1 NOT NULL default '',
+  `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '',
   `address` varchar(128) character set latin1 NOT NULL default '',
   `check_command_object_id` int(11) NOT NULL default '0',
   `check_command_args` varchar(255) character set latin1 NOT NULL default '',
@@ -851,8 +853,8 @@ CREATE TABLE IF NOT EXISTS `icinga_objects` (
   `object_id` int(11) NOT NULL auto_increment,
   `instance_id` smallint(6) NOT NULL default '0',
   `objecttype_id` smallint(6) NOT NULL default '0',
-  `name1` varchar(128) character set latin1 NOT NULL default '',
-  `name2` varchar(128) character set latin1 default NULL,
+  `name1` varchar(128) character set latin1 collate latin1_general_cs NOT NULL default '',
+  `name2` varchar(128) character set latin1 collate latin1_general_cs default NULL,
   `is_active` smallint(6) NOT NULL default '0',
   PRIMARY KEY  (`object_id`),
   KEY `objecttype_id` (`objecttype_id`,`name1`,`name2`)
@@ -1108,7 +1110,7 @@ CREATE TABLE IF NOT EXISTS `icinga_services` (
   `config_type` smallint(6) NOT NULL default '0',
   `host_object_id` int(11) NOT NULL default '0',
   `service_object_id` int(11) NOT NULL default '0',
-  `display_name` varchar(64) character set latin1 NOT NULL default '',
+  `display_name` varchar(64) character set latin1 collate latin1_general_cs NOT NULL default '',
   `check_command_object_id` int(11) NOT NULL default '0',
   `check_command_args` varchar(255) character set latin1 NOT NULL default '',
   `eventhandler_command_object_id` int(11) NOT NULL default '0',
diff --git a/module/idoutils/db/oracle/oracle.sql b/module/idoutils/db/oracle/oracle.sql
index 2ef60cf..288af52 100644
--- a/module/idoutils/db/oracle/oracle.sql
+++ b/module/idoutils/db/oracle/oracle.sql
@@ -1,6 +1,8 @@
 -- --------------------------------------------------------
 -- oracle.sql
 -- DB definition for Oracle
+-- 
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
 --
 -- requires ocilib, oracle (instantclient) libs+sdk to work
 -- specify oracle (instantclient) libs+sdk in ocilib configure
diff --git a/module/idoutils/db/pgsql/pgsql.sql b/module/idoutils/db/pgsql/pgsql.sql
index 7f04af4..3650d9b 100644
--- a/module/idoutils/db/pgsql/pgsql.sql
+++ b/module/idoutils/db/pgsql/pgsql.sql
@@ -1,15 +1,13 @@
-/*
-# pgsql.sql
-#   	- modfied mysql.sql to work with postgres 
-#	- added from_unixtime and unix_timestamp 
-#	  as sql function
-#
-# initial version: 2009-05-13 Markus Manzke
-# current version: 2010-02-03 Michael Friedrich <michael.friedrich at univie.ac.at>
-#
-#--------------------------------------------------------------------------------
-#
-*/
+-- --------------------------------------------------------
+-- pgsql.sql
+-- DB definition for Postgresql
+-- 
+-- Copyright (c) 2009-2010 Icinga Development Team (http://www.icinga.org)
+--
+-- initial version: 2009-05-13 Markus Manzke
+-- current version: 2010-02-03 Michael Friedrich <michael.friedrich at univie.ac.at>
+--
+-- --------------------------------------------------------
 
 --
 -- Functions
diff --git a/module/idoutils/src/dbhandlers.c b/module/idoutils/src/dbhandlers.c
index b0bd46e..d230901 100644
--- a/module/idoutils/src/dbhandlers.c
+++ b/module/idoutils/src/dbhandlers.c
@@ -79,16 +79,6 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
 	} else {
 		es[0] = ndo2db_db_escape_string(idi, name1);
                 switch (idi->dbinfo.server_type) {
-                        case NDO2DB_DBSERVER_MYSQL:
-                                /* mysql does case INsensitive compare, use BINARY */
-                                if (asprintf(&buf1, "BINARY name1='%s'", es[0]) == -1)
-                                        buf1 = NULL;
-                                break;
-                        case NDO2DB_DBSERVER_PGSQL:
-                                /* Postgres does case sensitive compare  */
-                                if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
-                                        buf1 = NULL;
-                                break;
                         case NDO2DB_DBSERVER_DB2:
                                 break;
                         case NDO2DB_DBSERVER_FIREBIRD:
@@ -100,19 +90,17 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
                         case NDO2DB_DBSERVER_MSQL:
                                 break;
                         case NDO2DB_DBSERVER_ORACLE:
-
-#ifdef USE_ORACLE /* Oracle ocilib specific */
-                                /* Oracle does case sensitive compare  */
-                                if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
-                                        buf1 = NULL;
-#endif /* Oracle ocilib specific */
-
                                 break;
                         case NDO2DB_DBSERVER_SQLITE:
                                 break;
                         case NDO2DB_DBSERVER_SQLITE3:
                                 break;
                         default:
+				/* William Preston: mysql does case sensitive compare
+				 * IF the collation is changed to latin1_general_cs */
+                                /* Postgres does case sensitive compare  */
+                                if (asprintf(&buf1, "name1='%s'", es[0]) == -1)
+                                        buf1 = NULL;
                                 break;
                 }
 	}
@@ -124,16 +112,6 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
 	} else {
 		es[1] = ndo2db_db_escape_string(idi, name2);
                 switch (idi->dbinfo.server_type) {
-                        case NDO2DB_DBSERVER_MYSQL:
-                                /* mysql does case INsensitive compare, use BINARY */
-		                if (asprintf(&buf2, "BINARY name2='%s'", es[1]) == -1)
-                			buf2 = NULL;
-                                break;
-                        case NDO2DB_DBSERVER_PGSQL:
-                                /* Postgres does case sensitive compare  */
-                                if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
-                                        buf2 = NULL;
-                                break;
                         case NDO2DB_DBSERVER_DB2:
                                 break;
                         case NDO2DB_DBSERVER_FIREBIRD:
@@ -145,19 +123,17 @@ int ndo2db_get_object_id(ndo2db_idi *idi, int object_type, char *n1, char *n2, u
                         case NDO2DB_DBSERVER_MSQL:
                                 break;
                         case NDO2DB_DBSERVER_ORACLE:
-
-//#ifdef USE_ORACLE /* Oracle ocilib specific */
-				/* Oracle does case sensitive compare  */
-//		                if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
-//		                        buf2 = NULL;
-//#endif /* Oracle ocilib specific */
-
                                 break;
                         case NDO2DB_DBSERVER_SQLITE:
                                 break;
                         case NDO2DB_DBSERVER_SQLITE3:
                                 break;
                         default:
+				/* William Preston: mysql does case sensitive compare
+				 * IF the collation is changed to latin1_general_cs */
+                                /* Postgres does case sensitive compare  */
+                                if (asprintf(&buf2, "name2='%s'", es[1]) == -1)
+                                        buf2 = NULL;
                                 break;
                 }
 	}





More information about the icinga-checkins mailing list