Re: [Firebird-devel] Some aspects of the optimizer hints
On 01/07/2014 01:35 PM, Dmitry Yemanov wrote: > 07.01.2014 12:46, Dmitry Yemanov wrote: >> It *is* related as soon as you need to alter from the default FIRST ROWS >> to custom FIRST ROWS in some particular query. > Read: "custom ALL ROWS", sorry. > > True. I.e. historical form of giving 'FIRST' - only hint is not enough. -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Odp: Some aspects of the optimizer hints
Den 2014-01-08 08:22 skrev Dmitry Yemanov såhär: > 08.01.2014 10:30, liviusliv...@poczta.onet.pl wrote: > >> Why plan can not by extended for hint purposes? > It can, the question is whether it should. > >> This is natural placefor FB users to tell optimizer >> the better way of query execution. > Personally, I see an important difference between planning (in how it > historically looked like) and hinting (in how it's being discussed), so > I'd prefer separate clauses for these actions. > > That said, I'm not going to insist if the majority here would favor the > PLAN clause being used also for hinting. Perhaps my vote shouldn't count for too much, but I think plan for ... or plan hint ... makes about as much sense (in terms of natural language/common sense) as optimize for ... That said, I'd vote against "plan for/hint" if the plan clause in its current use is or is soon going to be deprecated. So, what are the plans for the plan clause? Regards, Kjell -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Odp: Some aspects of the optimizer hints
08.01.2014 12:15, Kjell Rilbe wrote: > That said, I'd vote against "plan for/hint" if the plan clause in its > current use is or is soon going to be deprecated. So, what are the plans > for the plan clause? Explicit planning is still used by some customers, so it cannot be deprecated. But it's not going to be extended/improved to catch up the optimizer evolution. Dmitry -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Odp: Some aspects of the optimizer hints
Den 2014-01-08 09:32 skrev Dmitry Yemanov såhär: > 08.01.2014 12:15, Kjell Rilbe wrote: > >> That said, I'd vote against "plan for/hint" if the plan clause in its >> current use is or is soon going to be deprecated. So, what are the plans >> for the plan clause? > Explicit planning is still used by some customers, so it cannot be > deprecated. But it's not going to be extended/improved to catch up the > optimizer evolution. Isn't that just about what deprecated means? Still available, but outdated, will not be maintained/developed further, and should be avoided in new code? Anyway, in that case I would consider the plan claus "dead" and vote for some other keyword, e.g. optimize for... Kjell -- -- Kjell Rilbe DataDIA AB E-post: kj...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64 -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-4314) COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed
COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed --- Key: CORE-4314 URL: http://tracker.firebirdsql.org/browse/CORE-4314 Project: Firebird Core Issue Type: Bug Components: Charsets/Collation Affects Versions: 2.5.2 Update 1 Environment: Ubuntu 32 bits beta version of 14.04 Reporter: Kaf It appears the same case discussed in http://tracker.firebirdsql.org/browse/CORE-3447 but now for different OS and different Firebird version: ldd /usr/sbin/fbserver | grep icu libicuuc.so.52 => /usr/lib/i386-linux-gnu/libicuuc.so.52 (0xb75a3000) libicudata.so.52 => /usr/lib/i386-linux-gnu/libicudata.so.52 (0xb5c1a000) I get COLLATION UNICODE_CI_AI for CHARACTER SET UTF8 is not installed when i try to make any query to a database that was created in the same firebird version but after the computer upgrade ubuntu from 13.10 to 14.04 this error started to happen. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
On 01/07/2014 01:11 PM, marius adrian popa wrote:
> Notes on database security assesment
> http://www.slideshare.net/qqlan/firebird-interbase-database-engine-hacks-or-rtfm
>
I see 2 kinds of samples - mixed DDL/DML statements and use of UDF.
It's well known and documented (can't provide a link but as far as I
remember it's documented) fact that DDL/DML mix is not what makes
firebird behave well. Anyway:
$ ./isql employee -u sysdba ## it's fb3 and use of embedded access
SQL> set autoddl off;
SQL> ALTER DATABASE ADD DIFFERENCE FILE 'filename';
SQL> ALTER DATABASE BEGIN BACKUP;
SQL> INSERT INTO country values ('aa', 'bb');
SQL> commit;
SQL> select * from country;
COUNTRY CURRENCY
=== ==
USA Dollar
England Pound
Canada CdnDlr
Switzerland SFranc
Japan Yen
Italy Euro
France Euro
Germany Euro
Australia ADollar
Hong Kong HKDollar
Netherlands Euro
Belgium Euro
Austria Euro
FijiFDollar
Russia Ruble
Romania RLeu
aa bb
SQL>
I see no lockout. And without "set autoddl off" also (certainly) no lockout.
What about second sample (calling arbitrary UDF) - yes, it works. But it
requires non-default, not-recommended configuration (which is explicitly
documented directly in firebird.conf). To change configuration one must
be root. And it requires firebird server to run as root, which is also
non-default, not-recommended and also requires root access to change
default safe way to run. I suppose having root access one can find
simpler ways to kill OS at the target box or add new user :) The sample
is beautiful and impressive, but non realistic.
I.e. on my mind the presentation is about nothing.
--
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
On Wed, 08 Jan 2014 19:13:02 +0400, Alex wrote:
> On 01/07/2014 01:11 PM, marius adrian popa wrote:
>> Notes on database security assesment
>>
http://www.slideshare.net/qqlan/firebird-interbase-database-engine-hacks-or-rtfm
>>
>
> I see 2 kinds of samples - mixed DDL/DML statements and use of UDF.
>
> It's well known and documented (can't provide a link but as far as I
> remember it's documented) fact that DDL/DML mix is not what makes
> firebird behave well. Anyway:
>
> $ ./isql employee -u sysdba ## it's fb3 and use of embedded access
> SQL> set autoddl off;
> SQL> ALTER DATABASE ADD DIFFERENCE FILE 'filename';
> SQL> ALTER DATABASE BEGIN BACKUP;
> SQL> INSERT INTO country values ('aa', 'bb');
> SQL> commit;
...
> aa bb
>
> SQL>
>
> I see no lockout. And without "set autoddl off" also (certainly) no
> lockout.
I think he means you can create a file anywhere on the file system (ie in
a webserver directory) with a relatively high level of control of what gets
into it.
Mark
--
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
On 01/08/2014 07:22 PM, Sergey Mereutsa wrote: > Hello Alex, > > ... skipped ... > > A> I.e. on my mind the presentation is about nothing. > > The presentation is about "If you have armed gun - you can shut your > leg" :) > > I think, the main message idea from Marius is about default security - > i.e. by default FB after installation should not allow > _unprivileged_ user to make shot in his/her legs :) > But all samples provided _do_ require privileged user (sysdba or root) - may be except CREATE DATABASE ':'; Currently everyone can create new DB and become it's DBO. That's what will be changed in fb3 before beta1. -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
On 01/08/2014 07:34 PM, Mark Rotteveel wrote:
> On Wed, 08 Jan 2014 19:13:02 +0400, Alex wrote:
>> On 01/07/2014 01:11 PM, marius adrian popa wrote:
>>> Notes on database security assesment
>>>
> http://www.slideshare.net/qqlan/firebird-interbase-database-engine-hacks-or-rtfm
>> I see 2 kinds of samples - mixed DDL/DML statements and use of UDF.
>>
>> It's well known and documented (can't provide a link but as far as I
>> remember it's documented) fact that DDL/DML mix is not what makes
>> firebird behave well. Anyway:
>>
>> $ ./isql employee -u sysdba ## it's fb3 and use of embedded access
>> SQL> set autoddl off;
>> SQL> ALTER DATABASE ADD DIFFERENCE FILE 'filename';
>> SQL> ALTER DATABASE BEGIN BACKUP;
>> SQL> INSERT INTO country values ('aa', 'bb');
>> SQL> commit;
> ...
>> aa bb
>>
>> SQL>
>>
>> I see no lockout. And without "set autoddl off" also (certainly) no
>> lockout.
> I think he means you can create a file anywhere on the file system (ie in
> a webserver directory) with a relatively high level of control of what gets
> into it.
>
May be I did not understand what means 'your file is locked'.
But anyway - this works only if a webserver directory has write access
for user firebird of firebird server runs as root.
--
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
On Wed, 08 Jan 2014 19:41:16 +0400, Alex wrote: > On 01/08/2014 07:22 PM, Sergey Mereutsa wrote: >> Hello Alex, >> >> ... skipped ... >> >> A> I.e. on my mind the presentation is about nothing. >> >> The presentation is about "If you have armed gun - you can shut your >> leg" :) >> >> I think, the main message idea from Marius is about default security - >> i.e. by default FB after installation should not allow >> _unprivileged_ user to make shot in his/her legs :) >> > > But all samples provided _do_ require privileged user (sysdba or root) - > may be except > CREATE DATABASE ':'; > Currently everyone can create new DB and become it's DBO. > That's what will be changed in fb3 before beta1. He simply demonstrates that once you 'own' a Firebird server, you can use that to further exploit/hack into a server. Mark -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Some aspects of the optimizer hints
On 1/7/2014 3:45 PM, Mark Rotteveel wrote: > The fact that the optimizer will (or should) optimize it in a specific > way does not mean you should abuse also it to get the optimizer to use > a specific optimization. It can lead to hard to find bugs in the > future, for example if you used SELECT FIRST 100 to trick the > optimizer (aka "I am never going to have more than a million"), fun > ensues when against all expectations you do pass the 1 million mark. > And yes, you can take the position that hints make for lazy > optimizers, on the other hand sometimes optimizers simply make bad > plans that I'd like to address 'now', and not 10 years down the road > when Oracle, Microsoft or the Firebird team finally finds time to do > something with my bug report. In my mind, a specific syntax for > optimization hints is better than abusing features that will trigger > specific optimizer behaviour: a separate syntax makes explicit that it > is an optimization (attempt), and future maintainers of your code > won't think you were an idiot for abusing 'feature X' in a way that > doesn't make sense, and they (or you) will be better aware of > consequences of changing it. And yes, you could document the abuse, > but I think most people forget to comment or will think it obvious. > With the SQL Server syntax optimizing for retrieval of the first 150 > rows is done with: SELECT ... FROM someTable ... OPTION (FAST 150) It > also contains an interesting option (OPTIMIZE FOR) for 'helping' the > optimizer decide on a plan by providing exemplar values for parameters. Let's get a little serious. The LIMIT/OFFSET (FIRST in Firebird) is used when displaying rows by page for display. In these cases, there is an obvious number of rows to ask for. There are cases where it is clear that going to effort to retrieve unwanted rows in an otherwise optimal manner is a losing strategy. I hope we're on the same page so far. A preposterous use case was presented where somebody was going to burn hundreds of thousands of DVDs, but wanted to optimize the time for the first to start burning. Despite the fact that it is a ludicrous case that should not be used as the basis for system design, we pointed out that FIRST would accomplish the task, which it will. To argue that somebody might not pick a number "large enough" and result in an application malfunction is beyond lame. No database application should be dependent on an optimizer to function correctly, just quickly and efficiently. An optimizer can be reasonably expected to pick the best execution strategy given the data available to it. As database architects, it is our job to define interfaces that provide the information that the optimizer needs to perform its work. The LIMIT/OFFSET clauses and syntactic variants fill the need precisely. Personally, I don't like SQL. I've never liked SQL. I much prefer to use a language that I'm free to extend to implement semantics that I feel are important, hence the bipolar GDML/SQL nature of Interbase / Firebird. The mantra of Interbase was "SQL: We don't fix, improve it, or extend it. We just implement it. We innovate in GDML." My next database system, an implementation of the amorphous data model, is designed for highly parallel execution across an arbitrary number of machines. The interface language (not SQL!) is designed to express semantics in such a way that the compiler and runtime can send pieces hither, thither, and yon to where ever the data might reside. There will be no provision for hints, plans, or anything of the like. "Tell me what you want, and I'll figure out how to do it.". Humans should not be in the business of telling computers how best to do things. Grumph. -- The Wolf -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
08.01.2014 16:44, Mark Rotteveel wrote: > He simply demonstrates that once you 'own' a Firebird server, you can use > that to further exploit/hack into a server. Perhaps, it is time to change default for DatabaseAccess parameter... -- WBR, SD. -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
Hello Alex, A> But all samples provided _do_ require privileged user (sysdba or root) - A> may be except A> CREATE DATABASE ':'; A> Currently everyone can create new DB and become it's DBO. A> That's what will be changed in fb3 before beta1. It is true only if server itself has access to the this path. For example, you can not create files in /etc/ by default :) Also, usually FB server runs as firebird user and this user can not write outside explicitly defined directories, at least in *nix. So, this example does not impressing too :) -- Best regards, Sergeymailto:s...@dqteam.com -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird Interbase Database engine hacks or rtfm
Hello Alex, ... skipped ... A> I.e. on my mind the presentation is about nothing. The presentation is about "If you have armed gun - you can shut your leg" :) I think, the main message idea from Marius is about default security - i.e. by default FB after installation should not allow _unprivileged_ user to make shot in his/her legs :) -- Best regards, Sergeymailto:s...@dqteam.com -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Some aspects of the optimizer hints
On Tue, Jan 7, 2014 at 3:45 PM, Mark Rotteveel wrote: > > With the SQL Server syntax optimizing for retrieval of the first 150 > rows is done with: > > SELECT ... > FROM someTable > ... > OPTION (FAST 150) > > It also contains an interesting option (OPTIMIZE FOR) for 'helping' the > optimizer decide on a plan by providing exemplar values for parameters. > A problem I'm having with general solutions is that (as far as I know) we're not dealing with a general problem. The problem of slow access to the first record is specific to queries with an ORDER BY and no FIRST/ROWS clause. Adding a general mechanism opens the door to a lot more opportunities to paper over optimizer problems and blame the user for bad performance. Cheers, Ann -- Rapidly troubleshoot problems before they affect your business. Most IT organizations don't have a clear picture of how application performance affects their revenue. With AppDynamics, you get 100% visibility into your Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrkFirebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
