Archive for the ‘plugin’ Category

MariaDB User Feedback

Октябрь 14th, 2011

One thing which we, as developers of MariaDB, run into is that our personal database needs are not the same as many of our users. In fact, our needs are quite light compared to many. We have a MariaDB website, a company website, a knowledgebase, this blog, and that’s about it. None of them are particularly high traffic compared to what our customers have. But apart from talking to our customers, which are just a small percentage of the total MariaDB population, we wanted to have a way of finding out how MariaDB is used “in the real world”, so to speak.

Asking lots and lots of people to fill out surveys isn’t any fun, and we would have to keep repeating the survey ad nauseum to get useful information over time on trends and such, so instead we’ve created a User Feedback plugin, and it’s included it in the just-released MariaDB 5.3.2-beta. The plugin is disabled by default, but we hope many of you will enable it. The statistics gathered will help us determine where to focus our development efforts and we think they will prove beneficial, or at least interesting, to everyone else. (What? You thought we were going to keep all of the information to ourselves?)

More information on the plugin can be found at http://kb.askmonty.org/en/user-feedback-plugin and some nice charts will eventually be available for your viewing pleasure at: http://mariadb.org/feedback_plugin/. I say eventually because if you go there now, there’s not much to look at, what with the plugin being just released and all.

The statistics gathered include things like buffer sizes, CPU architecture, OS, kernel version, what plugins are enabled, how much memory is installed, and so on. The statistics are all anonymous and contain no sensitive or private information. The information is collected into the information_schema.feedback table (and if you have the plugin enabled you can see what has been collected with a simple SELECT * FROM information_schema.feedback; query). Once enabled, the Feedback plugin will automatically send a report a few minutes after startup and then once a week. Submitting is done via an HTML POST (just like a web form).

Even if you choose not to submit anonymous statistics to mariadb.org, you may still find the plugin useful, especially if you run many instances of MariaDB. You see, the URL the plugin POSTs to is configurable. The default is to post to mariadb.org, but you can easily change it to POST to your own server, or to your own server and mariadb.org (you can configure multiple URLs).

Full details on configuring the plugin, and how to enable it, are available at http://kb.askmonty.org/en/user-feedback-plugin.

So, if you’ve ever wanted to help out with MariaDB, but just couldn’t think of anything, here’s something that everyone who runs MariaDB can do without breaking a sweat. Help us help you by enabling the User Feedback plugin!

Thanks.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in 5.3.2-beta, General, MariaDB, mysql, plugin

Комментарии отключены

 

Monitoring MySQL SQL statements the way it SHOULD be done!

Ноябрь 23rd, 2010
You may have read a previous post of mine, back in April this year, where I wrote about using the MySQL 5.5 Audit interface to SQL Statement monitoring. There was a bunch of comments and some ideas, but not much happened. Until now that is.

Hereby I release the first version of SQLStats, which is a much enhanced version of what I described in the previous post. This is a MySQL Plugin for MySQL 5.5 that allows you to monitor the most recently executed, and the most frequently executed statements using 2 INFORMATION_SCHEMA tables. The thing is not very complex, to be honest, but it does do the job. So what was the job then? Well, looking at what this plugin does, it goes something like this:
  • Allows you to monitor ALL SQL statements executed by the server.
  • The SQL statements are "normalized", meaning that literals / constants are removed before comparison.
  • Data is saved in memory. No disk access and very little overhead.
  • Data is retrieved from INFORMATION_SCHEMA tables, just a simple SELECT and you know what is going on in the server.
I have done some basic testing of the overhead of this plugin, and it wasn't even noticable. If you have used the general query log for this stuff before, know what overhead I'm talking about. With SQLStats, the overhead is close to 0.

There is no need for MySQL Proxy or anything like that. There is no need to change something in the Client or in the Connector. To be honest, there are a couple of things I want to add to the plugin eventually, but this is a starting point at least. To use it: download it, install the tomcat / mysql monitoring server, install the monitoring agent and ... No wait, that was MySQL Enterprise Monitor, this is how you do it: download, build, install and use it. That's it.

So where can I get it, you ask (or maybe you don't, but I'm gonna tell you anyway). It's on sourceforge, and you can download it from here:
http://sourceforge.net/projects/sqlstats/

There are two files to download: A simple PDF documents with some basic usage and configuration tips, and a source-code package (which also includes the same PDF).

What would I want from you? Ideas for future development, bug reports and a few beers, that's it, not too much to ask, eh?

/Karlsson
Also, did I mention that the overhead is VERY low...

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in audit, information_schema, monitoring, mysql, plugin

Комментарии отключены

 

MySQL 5.5.7 — Can we trust it being RC, or?

Ноябрь 11th, 2010
I just saw that MySQL 5.5.7 RC had been released, and reading the releasenotes made me more than a fair bit suspicious. In some kind of general agreement on what constitutes a "beta" release, this is when the software has reached a level of maturity when no more major features are to be introduced. MySQL (and many others) has broken that rule at times, and the rule is not enforced or something.

What constitutes an RC release though, in my mind, but I really want to know what you think, is software that is really 100% feature complete. There may be, but hopefully there aren't, even any major bugs to iron out. In short, it is "A Candidate to Release", and as close to GA as you can get. I have not seen this rule broken much, really.

With MySQL 5.5.7, this is an rc, as was the previous release, 5.5.6, and this time there is a really major feature introduced between these two release, pluggable authentication. And before I go on, let me stress that this feature per se is not what I am questioning here, quite the opposite, this is a very useful feature.

What I am questioning though is:
  • Why is MySQL introducing new major features in an RC release, even in between 2 RC releases? This means, if I am not mistaken, that this very important feature (authentication deal with security, mind you), that it might go live (the C is for Candiate) without having been beta tested?
  • What made MySQL 5.5.6 a Release Candidate? What I mean here is that if we assume that this major new features was conceived, written and performed in just a few weeks between 5.5.6 and 5.5.7, MySQL knew that 5.5.6 wasn't feature complete, and hence in no way a release candidate (C is for Candidate, if you had forgotten that little fact).
  • Why does MySQL insist on having major important changes to the security setup be tested the least in the server before GA? Fact is, what MySQL is telling us here is that there may not be any testing at all (as 5.7.7 is RC (where C is for Candidate) which means is could possibly be GA.
My conclusion is that MySQL 5.5 is not to be treated as GA (is MySQL even considering a GA release as feature complete, or are they about to introduce more features again in that line of releases) just not yet. Which is a problem for me personally I I just recommended us to go with 5.5.6, hey, it's RC (you know what the C in RC is for now, right?), and that it would be as close to solid for production use as you can get.

To be clear, I will stick with 5.5.6 for now. Not 5.7.7 or even 5.5 GA for a while, until I have tested that pluggable authentication is secure and solid for production use. I really want 5.5, and I am not alone, so I do not understand why MySQL had to screw around with this. I do understand why pluggable authentication should go into MySQL, for sure, but not in 5.5 or at least not in the midst of a RC cycle.

/Karlsson
Who is not saying he will now change to Postgres. Nope, I will not not act stupid. And watch me run with 5.5.6 for a while yet, I will not be alpha testing MySQL security in a live production site, no way, José.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in authentication, mysql, plugin

Комментарии отключены

 

MySQL 5.5.7 — Can we trust it being RC, or?

Ноябрь 11th, 2010
I just saw that MySQL 5.5.7 RC had been released, and reading the releasenotes made me more than a fair bit suspicious. In some kind of general agreement on what constitutes a "beta" release, this is when the software has reached a level of maturity when no more major features are to be introduced. MySQL (and many others) has broken that rule at times, and the rule is not enforced or something.

What constitutes an RC release though, in my mind, but I really want to know what you think, is software that is really 100% feature complete. There may be, but hopefully there aren't, even any major bugs to iron out. In short, it is "A Candidate to Release", and as close to GA as you can get. I have not seen this rule broken much, really.

With MySQL 5.5.7, this is an rc, as was the previous release, 5.5.6, and this time there is a really major feature introduced between these two release, pluggable authentication. And before I go on, let me stress that this feature per se is not what I am questioning here, quite the opposite, this is a very useful feature.

What I am questioning though is:
  • Why is MySQL introducing new major features in an RC release, even in between 2 RC releases? This means, if I am not mistaken, that this very important feature (authentication deal with security, mind you), that it might go live (the C is for Candiate) without having been beta tested?
  • What made MySQL 5.5.6 a Release Candidate? What I mean here is that if we assume that this major new features was conceived, written and performed in just a few weeks between 5.5.6 and 5.5.7, MySQL knew that 5.5.6 wasn't feature complete, and hence in no way a release candidate (C is for Candidate, if you had forgotten that little fact).
  • Why does MySQL insist on having major important changes to the security setup be tested the least in the server before GA? Fact is, what MySQL is telling us here is that there may not be any testing at all (as 5.7.7 is RC (where C is for Candidate) which means is could possibly be GA.
My conclusion is that MySQL 5.5 is not to be treated as GA (is MySQL even considering a GA release as feature complete, or are they about to introduce more features again in that line of releases) just not yet. Which is a problem for me personally I I just recommended us to go with 5.5.6, hey, it's RC (you know what the C in RC is for now, right?), and that it would be as close to solid for production use as you can get.

To be clear, I will stick with 5.5.6 for now. Not 5.7.7 or even 5.5 GA for a while, until I have tested that pluggable authentication is secure and solid for production use. I really want 5.5, and I am not alone, so I do not understand why MySQL had to screw around with this. I do understand why pluggable authentication should go into MySQL, for sure, but not in 5.5 or at least not in the midst of a RC cycle.

/Karlsson
Who is not saying he will now change to Postgres. Nope, I will not not act stupid. And watch me run with 5.5.6 for a while yet, I will not be alpha testing MySQL security in a live production site, no way, José.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in authentication, mysql, plugin

Комментарии отключены

 

Book review: MySQL 5.1 plugin development

Ноябрь 1st, 2010
MySQL 5.1 Plugin Development MySQL 5.1 Plugin Development,
by Sergei Golubchik and Andrew Hutchings.
Packt Publishing, 2010.
Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick.

This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the writers were less verbose and stick more to the topic, when it comes to the plugin architecture, there are still more some unanswered questions. I guess that the ones who have implemented MySQL extensions so far have read the code, more than the documentation.
But, back to the point. The void is filled now, and in such a wonderful way! I have to disclose that I was the reviewer of this book, and while this fact puts me in a conflicting position when it comes to a review, it has also given me inner reason for praise, beyond the experience of a regular reader. The people who have worked with me will know that I am hard to please, and so my review was peppered with suggestions for improvements that, I admit it, made the authors' life quite difficult. I mostly complained that some of the examples proposed for each chapter were not enough useful and interesting. In short, there was not enough incentive for the reader to start coding immediately. I am glad to report that the authors were very responsive, and, rather than being annoyed by my demands, did work enthusiastically at the examples in the book until they became a true gem in this category. This book will surprise you for its practical approach, when it guides you through the examples, until you end up with a working extension.
The first chapter is probably the part that will save many hours of attempts to everyone who wants to build a plugin. In that chapter the authors explain how to build the different types of plugins in several operating systems. If you have ever tried to build a plugin, you will appreciate this chapter.
The book's body covers every type of plugin that you can create in MySQL, from the classic UDF to the more recent storage engine. For each chapter, you will have code ready to compile and try on your own, and the line-by-line explanation of what that code does, and why it was written the way it is.
Whenever the code is less than intuitive, the authors take care of it with additional theoretical and practical explanation, making sure that the reader is fully informed on the topic, and can complete the task effectively.
Summing up, this is not a book for the idle reader. It's a a practical help for the doers, the ones who want to get the job done, and need guidance through the maze of the MySQL plugin protocol.

Update. There is also a review at Slashdot.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in book, Development, extending, mysql, plugin, Review, storage engine, udf

Комментарии отключены

 

Second Drizzle Beta (and InnoDB update)

Октябрь 14th, 2010

We just released the latest Drizzle tarball (2010-10-11 milestone). There are a whole bunch of bug fixes, but there are two things that are interesting from a storage engine point of view:

  • The Innobase plugin is now based on innodb_plugin 1.0.6
  • The embedded_innodb engine is now named HailDB and requires HailDB, it can no longer be built with embedded_innodb.

Those of you following Drizzle fairly closely have probably noticed that we’ve lagged behind in InnoDB versions. I’m actively working on fixing that – both for the innobase plugin and for the HailDB library.

If building the HailDB plugin (which is planned to replace the innobase plugin), you’ll need the latest HailDB release (which as of writing is 2.3.1). We’re making good additions to the HailDB API to enable the storage engine to have the same features as the Innobase plugin.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in beta, code, Drizzle, embedded_innodb, haildb, Innobase, InnoDB, plugin, tarball

Комментарии отключены

 

TaskFreak! v0.6.2 – Alter Search Plugin

Июль 15th, 2010

Background Knowledge

The Search Plugin for TaskFreak! created by DaDaemon and xdu v0.0.1 (March 26, 2007) was designed to create a simple, quick search capability of the tasks title and description. As well it only searched through he current task view (tasks visible at the time) and tasks that are not completed. For some this was not what was desired and would rather have the Search Plugin search through all tasks weather completed or not and as well search through the comments of tasks along with the title and description. I’ll show you how this is done using Searcher, bchristie and davidlmansfield instructions posted on the TaskFreak! Forums.

Solution – Add the Ability to Search All Tasks

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in the “Load Tasks” section to just above the “Task Order” section. This solution was posted by Searcher at Re: Quick ‘n’ Dirty Search Plugin.

Code Before
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198

$arrFilters = array();
 
// --- filter: project ---
if ($pProject) {
    // load tasks for specific project
	$sqlFilter = 'ii.projectId = \''.$pProject.'\'';
    $pLink=Tzn::concatUrl($pLink,'sProject='.$pProject);
} else if (!$objUser->checkLevel(6)) {
    // user can only access his own projects
    if ($objUserProjectList->rMore()) {
        $arrProject = array();
        while($objTmp = $objUserProjectList->rNext()) {
            $arrProject[] = $objTmp->id;
        }
        if ($objUser->checkLevel(13)) {
        	$arrProject[] = '0';
        }
        $sqlFilter = 'ii.projectId IN ('.implode(',',$arrProject).')';
 
        unset($arrProject);
        $objUserProjectList->rReset();
    }
}
 
$objItemList->addWhere($sqlFilter);
 
// --- filter: user ---
$pUser = intval($_REQUEST['sUser']);
if (!isset($_REQUEST['sUser']) && @constant('FRK_DEFAULT_VIEW_OWN_TASKS')) {
	// default view is own tasks only
	$pUser = $objUser->id;
}
 
if($_REQUEST['sUser'] == 'myprojects' && $_SESSION['selUser']){
   $objItemList->addWhere('ii.authorId='.$_SESSION['selUser']);
}
elseif ($pUser) {
	$objItemList->addWhere('ii.memberId = \''.$pUser.'\'');
    $_SESSION['selUser'] = $pUser;
    $pDefaultUserId = $pUser;
} else {
    unset($_SESSION['selUser']);
    session_unregister('selUser');
    $pDefaultUserId = $objUser->id;
    // by default, show own tasks
    /*
    if (!$objUser->checkLevel(1)) { // admin can see all
	    $objItemList->addWhere('(ii.memberId='.$objUser->id
    		.' OR ii.authorId='.$objUser->id.')');
    }
    */
}
 
$pLink=Tzn::concatUrl($pLink,'sUser='.$pUser);
 
// --- private tasks --------------------------------------------------------
 
$arrFilter[] = 'showPrivate=0';
 
if ($objUser->checkLevel(12)) {
	// show internal tasks
	$arrFilter[] = 'showPrivate=1';
}
 
$arrFilter[] = '(showPrivate=2 AND (ii.memberId='.$objUser->id
	.' OR ii.authorId='.$objUser->id.'))';
 
$objItemList->addWhere('('.implode(' OR ',$arrFilter).')');
 
// --- filter: context ---
 
if ($_REQUEST['sContext']) {
	$pContext = Tzn::getHttp($_REQUEST['sContext'],true);
	$objItemList->addWhere('context = \''.$pContext.'\'');
    $pLink=Tzn::concatUrl($pLink,'sContext='.$pContext);
}
 
$sqlFilter = '';
$pShow = ($_REQUEST['show'])?$_REQUEST['show']:'today';
$pLink=Tzn::concatUrl($pLink,'show='.$pShow);
 
$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
 
// --- Filter per date -----------------------------------------------------
 
switch ($pShow) {
	case 'all':
		break;
	case 'future':
		// show coming tasks and late tasks (undone only)
		$sqlFilter = '((deadlineDate >= \''
			.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' AND statusKey < '
			.FRK_STATUS_LEVELS.')'.' OR statusKey < '.FRK_STATUS_LEVELS.')';
        // show uncompleted tasks with no deadline
		$sqlFilter .= ' OR (deadlineDate = \'9999-00-00\' AND statusKey < '
			.FRK_STATUS_LEVELS.')';
		break;
	case 'past':
		// show past tasks and already done
		$sqlFilter = '(deadlineDate < \''
			.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' OR statusKey = '
			.FRK_STATUS_LEVELS.')';
		break;
	case 'today':
		// show all future tasks (done + undone) and late tasks
		$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
		$sqlFilter = '(statusKey = '.FRK_STATUS_LEVELS.' AND statusDate > \''
			.gmdate('Y-m-d 00:00:00',time()-$pKeepNoDead).'\') ';
 
		// hide far future tasks ?
		$tmpFilter = '';
		if (@constant('FRK_DEFAULT_FAR_FUTURE_HIDE')) {
			$tmp = intval(FRK_DEFAULT_FAR_FUTURE_HIDE) * 86400;
			$tmpFilter .= 'deadlineDate < \''
				.gmdate('Y-m-d 00:00:00',time()+$tmp).'\'';
		}
 
		// show tasks with no deadline ?
		if (@constant('FRK_NO_DEADLINE_TOO')) {
			// yes
			if ($tmpFilter) {
				$sqlFilter .= 'OR ('.$tmpFilter
					.' OR deadlineDate = \'9999-00-00\')'
					. ' AND statusKey < '.FRK_STATUS_LEVELS;
			} else {
				$sqlFilter .= ' OR statusKey < '.FRK_STATUS_LEVELS;
			}
		} else {
			// don't show uncompleted non planned tasks
			if ($tmpFilter) {
				$sqlFilter .= ' OR ('.$tmpFilter.' AND statusKey < '
    	        	.FRK_STATUS_LEVELS.')';
			} else {
	            $sqlFilter .= ' OR (deadlineDate <> \'9999-00-00\' AND statusKey < '
    	        	.FRK_STATUS_LEVELS.')';
			}
		}
 
        if (@constant('FRK_DEFAULT_CURRENT_TASKS')) {
            $objItemList->setPagination(FRK_DEFAULT_CURRENT_TASKS);
        }
		break;
	default:
		break;
}
 
// -TODO- Add filter current project only (no completed, no cancelled)
 
// echo '<p>&</p><p>-</p><p>-</p>'.$sqlFilter;
 
if ($sqlFilter) {
	$objItemList->addDateFilter($sqlFilter);
}
 
// search filter
	$pSearch = ($_REQUEST['search']);
	if ($pSearch) {
		$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
		$objItemList->addWhere($sqlFilter);
	}
Code After
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
	$objItemList->addWhere($sqlFilter);
}
else
{
	$arrFilters = array();
 
	// --- filter: project ---
	if ($pProject) {
	    // load tasks for specific project
		$sqlFilter = 'ii.projectId = \''.$pProject.'\'';
	    $pLink=Tzn::concatUrl($pLink,'sProject='.$pProject);
	} else if (!$objUser->checkLevel(6)) {
	    // user can only access his own projects
	    if ($objUserProjectList->rMore()) {
	        $arrProject = array();
	        while($objTmp = $objUserProjectList->rNext()) {
	            $arrProject[] = $objTmp->id;
	        }
	        if ($objUser->checkLevel(13)) {
	        	$arrProject[] = '0';
	        }
	        $sqlFilter = 'ii.projectId IN ('.implode(',',$arrProject).')';
 
	        unset($arrProject);
	        $objUserProjectList->rReset();
	    }
	}
 
	$objItemList->addWhere($sqlFilter);
 
	// --- filter: user ---
	$pUser = intval($_REQUEST['sUser']);
	if (!isset($_REQUEST['sUser']) && @constant('FRK_DEFAULT_VIEW_OWN_TASKS')) {
		// default view is own tasks only
		$pUser = $objUser->id;
	}
 
	if($_REQUEST['sUser'] == 'myprojects' && $_SESSION['selUser']){
	   $objItemList->addWhere('ii.authorId='.$_SESSION['selUser']);
	}
	elseif ($pUser) {
		$objItemList->addWhere('ii.memberId = \''.$pUser.'\'');
	    $_SESSION['selUser'] = $pUser;
	    $pDefaultUserId = $pUser;
	} else {
	    unset($_SESSION['selUser']);
	    session_unregister('selUser');
	    $pDefaultUserId = $objUser->id;
	    // by default, show own tasks
	    /*
	    if (!$objUser->checkLevel(1)) { // admin can see all
		    $objItemList->addWhere('(ii.memberId='.$objUser->id
	    		.' OR ii.authorId='.$objUser->id.')');
	    }
	    */
	}
 
	$pLink=Tzn::concatUrl($pLink,'sUser='.$pUser);
 
	// --- private tasks --------------------------------------------------------
 
	$arrFilter[] = 'showPrivate=0';
 
	if ($objUser->checkLevel(12)) {
		// show internal tasks
		$arrFilter[] = 'showPrivate=1';
	}
 
	$arrFilter[] = '(showPrivate=2 AND (ii.memberId='.$objUser->id
		.' OR ii.authorId='.$objUser->id.'))';
 
	$objItemList->addWhere('('.implode(' OR ',$arrFilter).')');
 
	// --- filter: context ---
 
	if ($_REQUEST['sContext']) {
		$pContext = Tzn::getHttp($_REQUEST['sContext'],true);
		$objItemList->addWhere('context = \''.$pContext.'\'');
	    $pLink=Tzn::concatUrl($pLink,'sContext='.$pContext);
	}
 
	$sqlFilter = '';
	$pShow = ($_REQUEST['show'])?$_REQUEST['show']:'today';
	$pLink=Tzn::concatUrl($pLink,'show='.$pShow);
 
	$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
 
	// --- Filter per date -----------------------------------------------------
 
	switch ($pShow) {
		case 'all':
			break;
		case 'future':
			// show coming tasks and late tasks (undone only)
			$sqlFilter = '((deadlineDate >= \''
				.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' AND statusKey < '
				.FRK_STATUS_LEVELS.')'.' OR statusKey < '.FRK_STATUS_LEVELS.')';
	        // show uncompleted tasks with no deadline
			$sqlFilter .= ' OR (deadlineDate = \'9999-00-00\' AND statusKey < '
				.FRK_STATUS_LEVELS.')';
			break;
		case 'past':
			// show past tasks and already done
			$sqlFilter = '(deadlineDate < \''
				.strftime(TZN_DATE_SQL,PRJ_DTE_NOW).'\' OR statusKey = '
				.FRK_STATUS_LEVELS.')';
			break;
		case 'today':
			// show all future tasks (done + undone) and late tasks
			$pKeepNoDead = intval(@constant('FRK_NO_DEADLINE_KEEP') -1) * 86400;
			$sqlFilter = '(statusKey = '.FRK_STATUS_LEVELS.' AND statusDate > \''
				.gmdate('Y-m-d 00:00:00',time()-$pKeepNoDead).'\') ';
 
			// hide far future tasks ?
			$tmpFilter = '';
			if (@constant('FRK_DEFAULT_FAR_FUTURE_HIDE')) {
				$tmp = intval(FRK_DEFAULT_FAR_FUTURE_HIDE) * 86400;
				$tmpFilter .= 'deadlineDate < \''
					.gmdate('Y-m-d 00:00:00',time()+$tmp).'\'';
			}
 
			// show tasks with no deadline ?
			if (@constant('FRK_NO_DEADLINE_TOO')) {
				// yes
				if ($tmpFilter) {
					$sqlFilter .= 'OR ('.$tmpFilter
						.' OR deadlineDate = \'9999-00-00\')'
						. ' AND statusKey < '.FRK_STATUS_LEVELS;
				} else {
					$sqlFilter .= ' OR statusKey < '.FRK_STATUS_LEVELS;
				}
			} else {
				// don't show uncompleted non planned tasks
				if ($tmpFilter) {
					$sqlFilter .= ' OR ('.$tmpFilter.' AND statusKey < '
	    	        	.FRK_STATUS_LEVELS.')';
				} else {
		            $sqlFilter .= ' OR (deadlineDate <> \'9999-00-00\' AND statusKey < '
	    	        	.FRK_STATUS_LEVELS.')';
				}
			}
 
	        if (@constant('FRK_DEFAULT_CURRENT_TASKS')) {
	            $objItemList->setPagination(FRK_DEFAULT_CURRENT_TASKS);
	        }
			break;
		default:
			break;
	}
 
	// -TODO- Add filter current project only (no completed, no cancelled)
 
	// echo '<p>&</p><p>-</p><p>-</p>'.$sqlFilter;
 
	if ($sqlFilter) {
		$objItemList->addDateFilter($sqlFilter);
	}
}

Solution – Add Ability to Also Search within Task Comments

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in just below the heading section of the “Load Tasks”. This solution was posted by davidlmansfield at [PATCH] [Search Plugin] include tasks matching in comment fields.

Code Before
39
40
41
42
43
44

// search filter
$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\')';
	$objItemList->addWhere($sqlFilter);
}
Code After
39
40
41
42
43
44

// search filter
$pSearch = ($_REQUEST['search']);
if ($pSearch) {
	$sqlFilter = '(ii.title LIKE \'%'.$pSearch.'%\' OR ii.description LIKE \'%'.$pSearch.'%\' OR ii.itemId in (select itemId from '.$objItemList->gTable('itemComment').' where body LIKE \'%'.$pSearch.'%\'))';
	$objItemList->addWhere($sqlFilter);
}

Solution – Stop SQL Injections

Edit the “index.php” located in the root of TaskFreak! as follows. We will be working in just below the heading section of the “Load Tasks”. This solution was posted by bchristie at Re: Quick ‘n’ Dirty Search Plugin.

Code Before
39
40

// search filter
$pSearch = ($_REQUEST['search']);
Code After
39
40

// search filter
$pSearch = isset($_REQUEST['search'])?mysql_real_escape_string($_REQUEST['search']):false;

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in alter, change, filter, General, howto, mod, plugin, Search, searching, taskfreak!

Комментарии отключены

 

Trace SQL From the Database to the Source Code with MySQL Enterprise Monitor

Июнь 11th, 2010

OK, you found the problem SQL statement that was affecting your server’s performance, now where did it originate?

The new MySQL Enterprise Plugins for Connector/J and Connector/NET send query statistics, including the source location for each query, directly to the MySQL Enterprise Monitor.

Figure 1 is a screenshot of new source location feature.

Figure 1. Source Location

Figure 2 shows the standard query statistics, which are collected in the query analyzer.  In both cases, the statistics are gathered by the MySQL Connector and the Plugin, not MySQL proxy.

Figure 2. Query Analyzer

If you’re a MySQL Enterprise customer, you can download the new monitor and plugins from customer.mysql.com.  If you’d like to try it out, a 30 day trial is available at http://www.mysql.com/trials/

Please review the help file and readme for full instructions, but here are a few of the highlights for setting up monitoring for Java applications.  (I’ll provide further instructions for .NET in a future post.)

1.  Set up the MySQL Enterprise Monitor and Agent.  The agent resides on the MySQL production server and the monitor should reside on a non-production server within your environment.  Although the Plugin gathers the query statistics, the agent needs to run and connect to the monitor at least once so the Monitor can associate the data with the appropriate server.

2.  Download the following files from the customer portal (customer.mysql.com) and add them to your classpath:

  • Connector/J version 5.1.12 or newer
  • Plugin for Connector/J c-java-mysql-enterprise-plugin-1.0.0.42.jar

3. Change your JDBC url.  In my case, I changed it from the standard JDBC url, “jdbc:mysql:127.0.0.1:3306/sakila , to

“jdbc:mysql://127.0.0.1:3306/sakila?statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters& amp;serviceManagerUrl=http://[mymonitorURL.com]&serviceManagerUser=[myagentusername]& serviceManagerPassword=[myagentpassword]l”;

Full instructions are in the readme.  Substitute appropriate URL and credentials for the bracketed [ ] items above.


4. Finally, the mysql user account that is used by the application needs to have SELECT privilege to the mysql.inventory table.  This table includes a unique identifier which the monitor uses to match the data with the MySQL instance.  I executed the following:

mysql> GRANT SELECT ON mysql.inventory TO ‘elgato’@'%’;

I found it helpful to monitor the application log file for error messages.   The readme includes additional logging information.  In my tomcat test application, the log information was sent to /var/logs/tomcat6/daemon.log file.

I hope this is helpful information.  If you have any issues, feel free to post a comment on this blog post or post on the forums:  http://forums.mysql.com/list.php?166



PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Connector Plugin, Connector/J, Enterprise Monitor, monitor, plugin

Комментарии отключены

 

MySQL University: Securich — Security Plugin for MySQL (rerun)

Июнь 9th, 2010

This Thursday (June 10th, 14:00 UTC), Darren Cassar will rerun his February 25 presentation of Securich - Security Plugin for MySQL. (Recording of the session failed in February; hopefully it will succeed this time.) According to Darren, the author of the plugin, Securich is an incredibly handy and versatile tool for managing user privileges on MySQL through the use of roles. It basically makes granting and revoking rights a piece of cake, not to mention added security it provides through password expiry and password history, the customization level it permits, the fact that it runs on any MySQL 5.0 or later and it's easily deployable on any official MySQL binary, platform independent.
More information here: http://www.securich.com/about.html.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone. All sessions (slides & audio) are recorded; the links will be on the respective MySQL University session pages which are listed on the MySQL University home page.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, plug-in, plugin, securich, security, sun, university

Комментарии отключены

 

Performance gain of MySQL 5.1 InnoDB plugin

Июнь 6th, 2010
plugin performanceYou know already that InnoDB in MySQL 5.5 has great improvements in performance and scalability. You will have to wait a few months for that, though, because MySQL 5.5 is not GA yet.
But if you need some extra performance in MySQL 5.1, you may want to use the Innodb Plugin instead of the built-in one. As of version 5.1.47, the Innodb plugin is of GA quality, and it comes with a good out-of-the-box improvement compared to the built-in engine.

To test my assumptions, I used one of my test Linux servers to perform a sysbench on 5.0.91, 5.1.47 built-in and plugin, and 5.5.4. The MySQL servers were all configured with 
innodb_buffer_pool_size=5G

MySQL 4.1.47 was tested both as out-of-the-box, and with the plugin enabled.

ignore_builtin_innodb
# note: the following statements must go all in one line
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

default-storage-engine=InnoDBinnodb_file_per_table=1
innodb_file_format=barracudainnodb_strict_mode=1

The test was the same for all the servers. A simple sysbench both read-only and read/write on a 1M records table.

sysbench \
    --test=oltp \
    --oltp-table-size=1000000 \
    --mysql-db=test \
    --mysql-user=$PASSWD \
    --mysql-password=$USER \
    --mysql-host=$HOST \
    --mysql-port=$PORT \
    --max-time=60 \
    --oltp-read-only=$ON_OFF \
    --max-requests=0 \
    --num-threads=8 run

What came out is that, by using the innodb plugin instead of the built-in engine, you get roughly 15% more in read-only, and close to 8% in read/write.


Note that 5.5. enhancements are more impressive in scalability tests with more than 8 cores. In this server, I have just tested a simple scenario.

I did some more testing using "ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=X" in the InnoDB table, where X changed from 4 to 16. But sysbench didn't seem to play well with compression. For low values of KEY_BLOCK_SIZE, you actually get a much worse result than the built-in engine. I have yet to figure out how I would use this compressed InnoDB in practice.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in InnoDB, mysql, performance, plugin, testing

Комментарии отключены

 
« Older Entries