Re: [Firebird-devel] FB4 reserved word "LOCAL"
On 2-2-2019 16:32, Jorge Gonçalves wrote: Hi, the word LOCAL was promoted to reserved word ? I'm trying, for the first time, to run my company application on FB4 and the calls to the table "LOCAL" fails with "Token unknown ... LOCAL " . Yes it did, see doc/sql.extensions/README.keywords. This was needed as part of the time zone support. LOCAL is also a reserved word in SQL:2016 (earlier version as well). doc/sql.extensions/README.keywords also lists other keywords added as reserved or non-reserved keywords in Firebird 4. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Inconsistency between TimeZoneUtil::MAX_LEN and definition of RDB$TIME_ZONES.RDB$TIME_ZONE_NAME
In the Firebird sources, TimeZoneUtil::MAX_LEN is defined as 32, however the column RDB$TIME_ZONES.RDB$TIME_ZONE_NAME is defined as CHAR(63). There currently is one time zone name with length 32 (the rest is shorter), so right now there is no problem, however this inconsistency between definitions seems like an accident waiting to happen if in the future a zone is added with a length longer than 32. Could this be fixed so they both use the same length (I'd suggest 63)? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Full ICU in Firebird 4?
With the recent changes to the ICU version, does Firebird 4 on Windows now have the full ICU? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone identifier to displacement convertion
On 2019-01-20 15:30, Jorge Gonçalves wrote: according to the time zone documentation the time_zone member in the ISC_TIME_TZ and ISC_TIMESTAMP_TZ structure returns the time zone identifier or displacement. There are any way to convert the time zone identifier to the displacement on the client side ? Be aware that the time communicated in ISC_TIME_TZ and ISC_TIMESTAMP_TZ is UTC/GMT time. The zoneid is technically not necessary to use the time unless you want to be able to reconstruct with the original zone/offset. As far as I'm aware, fbclient doesn't offer anything to facilitate this to avoid additional dependencies like ICU and keeping track of the time zones supported by the server. Zoneids between 0 and 2878 are offsets, and you can derive the offset in minutes by subtracting 1439 for an offset between -23:59 and +23:59. Higher zoneids should be mapped to the zone name listed in the documentation (or table RDB$TIME_ZONES), and then you need to use the timezone support in your programming language, OS or a library like ICU to derive the offset applicable for that time zone at the specified UTC time. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Difference in performance between current_timestamp and localtimestamp
I was doing an artificial performance test on Firebird by inserting into a table that had a column updatedts timestamp default localtimestamp The insert did not touch this column (so the default is applied). To my surprise, that was about 7 - 10 % slower than using CURRENT_TIMESTAMP: updatedts timestamp default current_timestamp Why is that? I'd expect equivalent performance, or otherwise the reverse given the current_timestamp timestamp with time zone value needs to be converted to a timestamp without time zone. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for RETURNING *?
On 17-12-2018 13:26, Adriano dos Santos Fernandes wrote: On 09/12/2018 11:55, Mark Rotteveel wrote: Back in 2012 I created CORE-3808 to request support for RETURNING *, which should - equivalent to SELECT * - return all columns instead of having to manually include all columns in the returning clause. The primary use case for me would be to more easily support the JDBC getGeneratedKeys functionality, and with better performance than the current implementation because it would avoid roundtrips to query the metadata tables. I assume this would also be useful for other drivers or frameworks that require similar functionality. For example PostgreSQL already has this (see https://www.postgresql.org/docs/current/sql-insert.html). For a table with columns A, B, C, but with command INSERT INTO T (A) ..., should RETURNING * returns A, B, C or just A? Adriano, thank you for implementing this. I have now also added it in Jaybird 4. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Database creation slower in Firebird 4
It looks like database creation in Firebird 4 is slower (about 50%)
compared to 3.0.4. On my system, creating a database in Firebird 3.0.4
(through org.firebirdsql.management.FBManager) takes roughly 200ms,
while on Firebird 4.0.0.1352 it takes roughly 300ms.
Is this primarily due to increased size of metadata, or has something
else changed that slows this down?
This may seem trivial ('Who creates a lot of databases?' Well, I do):
running Jaybird's tests creates a lot of databases, so a decrease in
performance there is very noticeable for me.
The Jaybird pure-java testsuite on 3.0.4 takes +/- 6 minutes, against 4
it takes +/- 10 minutes (although that also includes extra tests for
features not in Firebird 3). The testsuite consists of almost 5000
tests, which leads to the creation of - at a guess - 1500 to 2000
databases (some tests don't need a database, and some tests share a
database).
To be clear, I'm only asking if this is an unexpected performance
regression, or if it is something that is expected with no room for
improvement.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5976) gbak multi-database file restore uses wrong minimum number of pages for first database file
gbak multi-database file restore uses wrong minimum number of pages for first database file --- Key: CORE-5976 URL: http://tracker.firebirdsql.org/browse/CORE-5976 Project: Firebird Core Issue Type: Bug Components: GBAK Affects Versions: 4.0 Alpha 1 Environment: Firebird-4.0.0.1352-0_x64 Reporter: Mark Rotteveel I have a Jaybird test that creates a multi-file database using a gbak restore (through the services API). This test creates an artificially small first file. Jaybird instructs Firebird to make the first file 10 pages big, and Firebird then overrides that to 200 pages. This doesn't work correctly, because those 200 pages are not sufficient. As a result the second database file is not created. Firebird 4, the restore logs the following: """ [..] gbak:backup version is 11 gbak:created database C:\Users\Mark\AppData\Local\Temp\junit8203596626416973464\junit4583341152862574241\testrestore1.fdb, page_size 8192 bytes gbak:started transaction gbak:length given for initial file (10) is less than minimum (200) gbak:adding file C:\Users\Mark\AppData\Local\Temp\junit8203596626416973464\junit4583341152862574241\testrestore2.fdb, starting at page 201 gbak:committing secondary files gbak:cannot commit files gbak: ERROR:Starting page number for file C:\USERS\MARK\APPDATA\LOCAL\TEMP\JUNIT8203596626416973464\JUNIT4583341152862574241\TESTRESTORE2.FDB must be 213 or greater gbak:creating indexes [..] """ If the minimum is actually 213 or greater, instead of "gbak:length given for initial file (10) is less than minimum (200)", shouldn't it do "gbak:length given for initial file (10) is less than minimum (**212**)" and create the first database file as 212 pages instead of the 200 it does now? For reference, Firebird 3 does create both database files and logs: """ [..] gbak:backup version is 10 gbak:created database C:\Users\Mark\AppData\Local\Temp\junit10807610804273459707\junit7128503662445743782\testrestore1.fdb, page_size 8192 bytes gbak:started transaction gbak:length given for initial file (10) is less than minimum (200) gbak:adding file C:\Users\Mark\AppData\Local\Temp\junit10807610804273459707\junit7128503662445743782\testrestore2.fdb, starting at page 201 gbak:committing secondary files gbak:creating indexes [..] """ -- 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 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Error "Invalid time zone region" with syntactically questionable timestamp string
Today I ran the Jaybird tests on Firebird 4 for the first time since the
timezone changes where merged. A test that previously worked (despite a
typo), now yields an error
java.sql.SQLException: Invalid time zone region [SQLState:HY000, ISC
error code:335544382]
This can be reproduced with:
CREATE TABLE T2 (C10 TIMESTAMP);
commit;
insert into T2 values ('2001-JAN-6:8:00:03.1223');
This query has a syntax 'error', a `:` between date and time instead of
a space (it works with '2001-JAN-6 8:00:03.1223'). But this syntax error
has worked fine for the past 17 years; it was introduced in a commit in
Jaybird on 2001-06-29.
I'm OK with a stricter syntax, but then I would expect another error (eg
a "conversion error from string "2001-JAN-6:8:00:03.1223"" or similar),
as this error is just confusing. If this is an intentional change, it
should also be explicitly mentioned in the release notes.
This is possibly related to topic "[Firebird-devel] Valid date or not"
from 21st of February 2018.
The firebird.log contains the following error:
RAMONA Thu Dec 27 10:20:45 2018
ICU error retrieving the system time zone: -128. Fallbacking to
displacement.
Interestingly, this error also occurs during the build, as the log also
contains errors from the build server during the build process, for example:
FBCOMPILEWINWed Dec 26 22:23:25 2018
ICU error retrieving the system time zone: -128. Fallbacking to
displacement.
This raises a number of questions:
1. Why does this specific error occur when non-timezone types are used?
2. If indeed we no longer allow this syntactically questionable format,
would it be possible to instead raise a different (less confusing) error?
3. What is the underlying cause of the error logged ("ICU error
retrieving the system time zone: -128.")? Is there a problem with
timezone support on Windows?
4. Errors logged during build should be fixed, right?
And a nitpick: "Fallbacking" should be "Falling back"
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5975) Add boolean aggregate functions EVERY, ANY and SOME
Add boolean aggregate functions EVERY, ANY and SOME --- Key: CORE-5975 URL: http://tracker.firebirdsql.org/browse/CORE-5975 Project: Firebird Core Issue Type: New Feature Components: Engine Reporter: Mark Rotteveel The SQL standard defines the boolean aggregate functions EVERY, ANY and SOME (see SQL:2016-2 10.9 ). In a way, these are the counterpart of the quantified comparison predicates (ALL, ANY and SOME) that Firebird already supports. The value expression inside the aggregate is a boolean expression, eg EVERY(somebooleancolumn) is true if all values for somebooleancolumn are true (also true if the group set is empty, which can be relevant when also using a FILTER-clause). ANY(x = 'y' and someothercolumn is null) is true if at least one row in the group set has a column x with value 'y' and someothercolumn null. From the standard: """ Syntax Rules [..] 7) If is specified, then: [..] b) Let DT be the declared type of the . [..] e) If EVERY, ANY, or SOME is specified, then DT shall be boolean and the declared type of the result is boolean. [..] General Rules [..] 7) If is specified, then: a) Let TX be the single-column table that is the result of applying the to each row of T1 and eliminating null values. If one or more null values are eliminated, then a completion condition is raised: warning — null value eliminated in set function. b) Case: i) If DISTINCT is specified, then let TXA be the result of eliminating redundant duplicate values from TX, using the comparison rules specified in Subclause 8.2, "", to identify the redundant duplicate values. ii) Otherwise, let TXA be TX. [..] d) Case: [..] vi) If EVERY is specified, then Case: 1) If the value of some element of TXA is False, then the result is False. 2) Otherwise, the result is True. vii) If ANY or SOME is specified, then Case: 1) If the value of some element of TXA is True, then the result is True. 2) Otherwise, the result is False. """ -- 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 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Comparison of many standard (and non-standard) SQL features amongst 10 databases
On 17-12-2018 19:40, Carlos H. Cantu wrote: Seems that there are some incorrect information, ie: Duplicate NULL values in unique index - Firebird is marked as NO, but actually you can have several NULL keys in a Unique index contraint (not in PK). Multi-row INSERTs - also marked as NO, but actually you can do insert from select in Firebird. AFAIK, they mean insert with a values list (table value constructor) instead of a single values set. Maybe there are more incorrect information... Yes, the "Global temporary tables" also is not correct. I suggest that you mail to the address listed on that page. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Support for RETURNING *?
On 17-12-2018 13:26, Adriano dos Santos Fernandes wrote: On 09/12/2018 11:55, Mark Rotteveel wrote: Back in 2012 I created CORE-3808 to request support for RETURNING *, which should - equivalent to SELECT * - return all columns instead of having to manually include all columns in the returning clause. The primary use case for me would be to more easily support the JDBC getGeneratedKeys functionality, and with better performance than the current implementation because it would avoid roundtrips to query the metadata tables. I assume this would also be useful for other drivers or frameworks that require similar functionality. For example PostgreSQL already has this (see https://www.postgresql.org/docs/current/sql-insert.html). For a table with columns A, B, C, but with command INSERT INTO T (A) ..., should RETURNING * returns A, B, C or just A? It should return all columns of the table (or view), so A, B and C. In other words, the `*` in a `RETURNING *` would be similar to the `*` in a `SELECT * FROM sometable`, which will also produce all columns from sometable. This would be for all statement types supporting RETURNING, not just INSERT. If it would just return the columns referenced in the insert column-list it would not be useful for my needs. The primary use case for me is to return generated columns (identity, trigger populated, default values, etc). Jaybird currently queries the metadata tables for the table referenced in DML and then adds a RETURNING-clause with all columns of the table. That isn't very efficient. Being able to just add RETURNING * without having to query the metadata tables would be simpler and quicker. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODS hdr_creation_date
On 23-11-2018 17:50, Dmitry Yemanov wrote: 23.11.2018 17:53, Adriano dos Santos Fernandes wrote: Does anyone see any problem in extending (in time zones branch) hdr_creation_date to include the time zone? Already existing (created in Alpha) ODS13 databases will be inaccessible (or FB will crash while accessing them). Probably not an absolute evil, but something worth avoiding if possible. We need to an explicit decision about that -- whether it's acceptable before Beta. I see no reason to disallow breaking changes to the ODS before the first GA (4.0.0) release. Preferably the ODS should be stable after the first beta, but I don't think that should be a hard requirement. Disallowing ODS changes during development because it inconveniences people is IMHO not a good enough reason. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Batch API wire protocol
This doesn't really provide me sufficient information to implement it,
but given I don't have the time and energy to work on this anyway, I'll
park it until I have.
I do get the feeling (although I maybe wrong, as I don't think I fully
understand the protocol), that this will be hard to implement, and it
feels like the message structures need a lot of upfront knowledge of
total message sizes which could be pretty memory intensive if
considering blobs, and just a bit annoying for rows without blobs).
Couldn't this be more like a fetch (that is: streaming rows as
individual messages)?
Mark
On 27-10-2018 19:35, Alex Peshkoff via Firebird-devel wrote:
On 10/27/18 15:02, Mark Rotteveel wrote:
What is the wire protocol of the batch API (that is: what operations,
what message format, etc)?
op_batch_create = 99,
op_batch_msg = 100,
op_batch_exec = 101,
op_batch_rls = 102,
op_batch_cs = 103,
op_batch_regblob = 104,
op_batch_blob_stream = 105,
op_batch_set_bpb = 106,
typedef struct p_batch_create
{
OBJCT p_batch_statement; // statement object
CSTRING_CONST p_batch_blr; // blr describing input messages
ULONG p_batch_msglen; // explicit message length
CSTRING_CONST p_batch_pb; // parameters block
} P_BATCH_CREATE;
typedef struct p_batch_msg
{
OBJCT p_batch_statement; // statement object
ULONG p_batch_messages; // number of messages
CSTRING p_batch_data;
} P_BATCH_MSG;
typedef struct p_batch_exec
{
OBJCT p_batch_statement; // statement object
OBJCT p_batch_transaction; // transaction object
} P_BATCH_EXEC;
typedef struct p_batch_cs // completion state
{
OBJCT p_batch_statement; // statement object
ULONG p_batch_reccount; // total records
ULONG p_batch_updates; // update counters
ULONG p_batch_vectors; // recnum + status vector pairs
ULONG p_batch_errors; // error's recnums
} P_BATCH_CS;
typedef struct p_batch_free
{
OBJCT p_batch_statement; // statement object
} P_BATCH_FREE;
typedef struct p_batch_blob
{
OBJCT p_batch_statement; // statement object
CSTRING p_batch_blob_data; // data
} P_BATCH_BLOB;
typedef struct p_batch_regblob
{
OBJCT p_batch_statement; // statement object
SQUAD p_batch_exist_id; // id of blob to register
SQUAD p_batch_blob_id; // blob id
} P_BATCH_REGBLOB;
typedef struct p_batch_setbpb
{
OBJCT p_batch_statement; // statement object
CSTRING_CONST p_batch_blob_bpb; // BPB
} P_BATCH_SETBPB;
There are 3 complex operations - batch messages (sends N messages, they
should be XDR-encoded during it), batch completion state (you should be
able to receive it - and mention struct is followed on the wire by sets
of data) and batch blob stream (you should learn to XDR-encode it). The
rest are trivial, but this 3 (specially blob stream) have rather complex
format in addition to mentioned structures.
Also, is it possible to use this protocol in a lower protocol version
when talking to Firebird 4 (eg Jaybird currently only implements
protocols v10 - 13), or does it really need to be connected with
protocol v16 (if I have my protocol versions right)?
Mark, as far as I remember I did not add any explicit checks for
protocol version. But I'm sure that batches (speically blob stream) is
at least 90% of changes needed to implement appropriate protocol.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB4 Windows snapshots not updated since Wednesday
On 2018-11-05 14:45, Adriano dos Santos Fernandes wrote: On 03/11/2018 12:55, Mark Rotteveel wrote: The Windows snapshots of Firebird 4 haven't been updated since the 31st of October, while the Linux snapshot was last updated today (the 3rd of November). Because there were no commits since then? Right now the last Windows build is still October 31st and last Linux build is November 9th. Do you mean to say that Windows only builds after commits, and Linux builds always, even if there is nothing new? If so, why that difference? Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] FB4 Windows snapshots not updated since Wednesday
The Windows snapshots of Firebird 4 haven't been updated since the 31st of October, while the Linux snapshot was last updated today (the 3rd of November). Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Batch API wire protocol
What is the wire protocol of the batch API (that is: what operations, what message format, etc)? Also, is it possible to use this protocol in a lower protocol version when talking to Firebird 4 (eg Jaybird currently only implements protocols v10 - 13), or does it really need to be connected with protocol v16 (if I have my protocol versions right)? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: Some OLTP numbers
On 27-10-2018 07:36, liviuslivius wrote: Thank you. Will be god if this repo will be part of Firebird project repo as autor is in the team It is a Firebird project: it is a subversion repository on Firebird's SourceForge location that has not been moved to GitHub. I don't know why this one wasn't moved to GitHub though. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Status of time zone support
What is the status for time zone support? Is it going to be included in Firebird 4 or not? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Expired certificate for firebirdsql.org
It is working fine for me, the certificate validity is from Tuesday, September 25, 2018 to Monday, December 24, 2018 Mark On 26-10-2018 12:17, Hristo Stefanov wrote: Hello, Currently firebirdsql.org is inaccessible because of an expired certificate which cannot be whitelisted due to using HTTP String Transport Security. Regards, Hristo Stefanov Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Case (and accent) insensitive ICU collations in multiple columns
On 24-10-2018 08:20, nonomura wrote: The source code cited below clearly tells the root of the problem that I reported. [..] https://github.com/Alexpux/firebird-git-svn/blob/master/src/common/unicode_util.cpp#L1334 Just a heads up: that is an outdated clone of an old repository that was synced from subversion and hasn't been updated since 2015. The Firebird repository is on https://github.com/FirebirdSQL/firebird Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] LOCALTIMESTAMP name
On 23-10-2018 16:20, liviuslivius wrote: Really strange "standard" here... Standards are nothing but inconsistent, especially as they evolve over time. However, CURRENT_TIME dates back to at least the SQL:92 standard (and probably existed earlier in some form in non-standardized dialects), while LOCALTIME is more recent (probably SQL:1999 or SQL:2003). And there might also be a semantic reason for it. The LOCAL in LOCALTIME and LOCALTIMESTAMP describes an aspect of the datatype (that it is without timezone), while the CURRENT in CURRENT_TIME and CURRENT_TIMESTAMP describes an aspect of the value (the 'when'). They could also have chosen to use CURRENT_LOCALTIME, but maybe they judged that too long. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] LOCALTIMESTAMP name
On 23-10-2018 11:36, liviuslivius wrote: Hi, why "LOCALTIMESTAMP" not "LOCAL_TIMESTAMP" as is for e.g. "CURRENT_TIMESTAMP"? That is because SQL:2016, ISO 9075-2:2016, in section 6.36 value function>, specifies the names as CURRENT_TIME, CURRENT_TIMESTAMP and LOCALTIME and LOCALTIMESTAMP . Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 3.0.4 unicode_ci_ai index problems
On 17-10-2018 20:13, Luis Forra wrote: Mark, was you that advised me to put the question in firebird-suport in the first place. I'm well aware of that. My point is that other people may not get what problem you are talking about, if you just post some code and outoput without making the problem explicit by describing it. That is especially important if you want people to actually reply and address your problem. Questions without an explicit problem statement are far more likely to go unanswered. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Firebird 3.0.4 unicode_ci_ai index problems
Can you please explicitly describe the problem, instead of expecting us
to interpret the output and try to figure out what problem you see here?
Mark
On 17-10-2018 19:47, Luis Forra wrote:
After discussing in firebird-suport I believe that this is relevant to
this list
The databases that I have migrate to utf8 with colation unicode_ci_ai
are much slower in use, the problem is the indexes with various varchar
fields.
example of the problem
CREATE TABLE M_UNICODE (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE
);
CREATE TABLE D_UNICODE (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE
);
CREATE TABLE M_CI_AI (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI
);
CREATE TABLE D_CI_AI (
S1 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI,
S2 VARCHAR(10) NOT NULL COLLATE UNICODE_CI_AI
);
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'A');
INSERT INTO M_UNICODE (S1, S2) VALUES ('A', 'B');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'A');
INSERT INTO M_CI_AI (S1, S2) VALUES ('A', 'B');
set term ^ ;
execute block
as
declare variable i bigint = 1000;
begin
while (i > 0) do
begin
insert into d_unicode (s1,s2) values ('A','A');
insert into d_ci_ai (s1,s2) values ('A','A');
i = i-1;
end
insert into d_unicode (s1,s2) values ('A','B');
insert into d_ci_ai (s1,s2) values ('A','B');
end^
set term ; ^
ALTER TABLE M_UNICODE ADD CONSTRAINT M_U PRIMARY KEY (S1, S2);
ALTER TABLE M_CI_AI ADD CONSTRAINT M_CI_AI PRIMARY KEY (S1, S2);
commit work;
ALTER TABLE D_UNICODE ADD CONSTRAINT D_U FOREIGN KEY (S1, S2) REFERENCES
M_UNICODE (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE D_CI_AI ADD CONSTRAINT D_CI_AI FOREIGN KEY (S1, S2)
REFERENCES M_CI_AI (S1, S2) ON DELETE CASCADE ON UPDATE CASCADE;
commit work;
Query
update m_ci_ai set s2 = 'C' where s1 = 'A' and s2 = 'B';
Operations
Read : 9
Writes : 0
Fetches: 2 070
Marks : 6
Enchanced Info:
+---+---+---+-+-+-+-+--+--+--+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads |
| | | | | |
+---+---+---+-+-+-+-+--+--+--+
|D_CI_AI | 0 | 2002 | 0 |
1 | 0 | 0 | 0 | 0 | 0 |
|M_CI_AI | 0 | 2 | 0 |
1 | 0 | 0 | 0 | 0 | 0 |
+---+---+---+-+-+-+-+--+--+--+
Query
update m_unicode set s2 = 'C' where s1 = 'A' and s2 = 'B';
Operations
Read : 0
Writes : 0
Fetches: 43
Marks : 8
Enchanced Info:
+---+---+---+-+-+-+-+--+--+--+
| Table Name | Records | Indexed | Non-Indexed |
Updates | Deletes | Inserts | Backouts | Purges | Expunges |
| | Total | reads | reads |
| | | | | |
+---+---+---+-+-+-+-+--+--+--+
|D_UNICODE | 0 | 2 | 0 |
1 | 0 | 0 | 0 | 0 | 0 |
|M_UNICODE | 0 | 2 | 0 |
1 | 0 | 0 | 0 | 0 | 0 |
+---+---+---+-+-+-+-+--+--+--+
I appreciate any help to solve this problem, thank you
Best regards
Luis Forra
---
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5937) Inconsistency between ALTER and USAGE privileges for sequences (generators).
Inconsistency between ALTER and USAGE privileges for sequences (generators). Key: CORE-5937 URL: http://tracker.firebirdsql.org/browse/CORE-5937 Project: Firebird Core Issue Type: Bug Components: Security Affects Versions: 3.0.4, 3.0.3, 4.0 Alpha 1, 3.0.2, 3.0.1, 3.0.0, 4.0 Initial Reporter: Mark Rotteveel There appears to be an inconsistency between the ALTER and USAGE privileges for sequences. Only users with ALTER permission on sequences are allowed to use ALTER SEQUENCE RESTART WITH Users with USAGE permission cannot execute that statement, but they can achieve the same effect with: select gen_id(, - gen_id(, 0)) from rdb$database Either this loophole needs to be closed (eg by disallowing values other than 0 or 1 without ALTER permission), which will likely break applications that rely on being able to use gen_id with a different value. Or, better, we should relax the requirements a bit, and allow RESTART WITH (and only RESTART WITH) to users who have USAGE permission. Then at least the loophole is explicit and doesn't create a false sense of safety. See also https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/133140 -- 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 Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] NULL and/or true/false
On 1-10-2018 18:18, Kovalenko Dmitry wrote: Hello, Firebird 3.0.4 Could anybody (Adriano?) confirm, that next result is corrected: select null or true as c4__null_or_true, /* TRUE */ null or false as c5__null_or_false, /* NULL */ null and true as c6__null_and_true, /* NULL */ null and false as c7__null_and_false /* FALSE */ from rdb$database Just – yes or no :) Yes, and verified against SQL:2016. You can reason about if you substitute TRUE and FALSE for NULL (or UNKNOWN as for booleans that is interchangeable) and see how that influences the result. For (UNKNOWN or TRUE), you can reason that whether UNKNOWN is replaced with TRUE or FALSE, the result is TRUE, so UNKNOWN is irrelevant for the result. But for (UNKNOWN or FALSE), you can't because if replaced with TRUE, the result is TRUE, but with FALSE the result is FALSE. That means that UNKNOWN decides the result, so the result is UNKNOWN. Same for AND: (UNKNOWN and FALSE), whether you replace with TRUE or FALSE, the result is FALSE. And for (UNKNOWN and TRUE), UNKNOWN decides the result: if you replace with TRUE, the result is TRUE, with FALSE, the result is FALSE. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] [FB-Tracker] Created: (CORE-5930) internal Firebird consistency check (Incorrect snapshot deallocation - too few slots)
internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) - Key: CORE-5930 URL: http://tracker.firebirdsql.org/browse/CORE-5930 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 4.0 Beta 1 Environment: Firebird-4.0.0.1227-0_x64 Windows 10 + Jaybird master Reporter: Mark Rotteveel I was just running the Jaybird tests against Firebird-4.0.0.1227-0_x64 (Windows 10). It has been a while since I tested with Firebird 4, so I don't know when this problem was introduced. When running the test org.firebirdsql.gds.TestReconnectTransaction.testReconnectTransaction, I get an error when trying to rollback a limbo transaction: java.sql.SQLException: internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) [SQLState:XX000, ISC error code:335544333] In short this test will create a connection, start a transaction, prepare that transaction and then close the connection. This will create a limbo transaction. It then creates a new connection, start a transaction, look for the limbo transaction, reconnects the limbo transaction, and then rolls back the limbo transaction. The rollback of the limbo transaction is what fails. Test: https://github.com/FirebirdSQL/jaybird/blob/master/src/test/org/firebirdsql/gds/TestReconnectTransaction.java#L86 I have tried setting ReadConsistency = 0 in firebird.conf, but that has made no difference. After running this test, the log contains the following entries: """ RAMONASat Sep 29 18:05:26 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) RAMONASat Sep 29 18:05:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61486, user = Mark RAMONASat Sep 29 18:05:27 2018 I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. RAMONASat Sep 29 18:05:27 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. """ After this error has occurred, it also doesn't seem possible to gracefully shutdown Firebird. It seems to hang. After shutting down, new connections are rejected, but the process does not stop and needs to be killed. Attempting to connect after trying to stop Firebird logs: """ RAMONASat Sep 29 18:07:55 2018 Authentication error connection shutdown """ Likely a related issue, another test (org.firebirdsql.gds.ng.wire.version10.TestV10Transaction.testBasicPrepareAndRollback) gets stuck. This test creates a connection, starts a transaction (read committed record version wait), inserts a value into a table, prepares the transaction (to be committed later in the test). Test: https://github.com/FirebirdSQL/jaybird/blob/master/src/test/org/firebirdsql/gds/ng/AbstractTransactionTest.java#L154 (it hangs on assertValueForKey(key, false, null);) Then in a separate connection + transaction (also read committed record version wait), the test attempts to select from the table. This connection is stuck fetching rows (even when setting ReadConsistency = 0 in firebird.conf). This works fine in Firebird 3 and earlier. With setting ReadConsistency = 0 in firebird.conf this is logged (after killing the test): """ RAMONASat Sep 29 18:28:36 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61927, user = Mark RAMONASat Sep 29 18:28:36 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61926, user = Mark """ With setting ReadConsistency = 1 in firebird.conf: """ RAMONASat Sep 29 18:31:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61943, user = Mark RAMONASat Sep 29 18:31:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61944, user = Mark RAMONASat Sep 29 18:31:27 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB internal Firebird consistency check (TPC: Attempt to mark inactive transaction to be in limbo) RAMONASat Sep 29 18:31:27 2018 I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. RAMONASat Sep 29 18:31:27 2018 Database: D:\DE
Re: [Firebird-devel] Error java.sql.SQLException: internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) [SQLState:XX000, ISC error code:335544333]
On 29-9-2018 22:21, Vlad Khorsun wrote: 29.09.2018 19:14, Mark Rotteveel wrote: ... Do I need to create a ticket for this, or is this a known problem? Yes, create a ticket please. Done: CORE-5930 I included both problems into that one ticket as I think they are the same. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Error java.sql.SQLException: internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) [SQLState:XX000, ISC error code:335544333]
On 29-9-2018 18:14, Mark Rotteveel wrote: I was just running the Jaybird tests against Firebird-4.0.0.1227-0_x64 (Windows 10). It has been a while since I tested with Firebird 4, so I don't know when this problem was introduced. When running the test org.firebirdsql.gds.TestReconnectTransaction.testReconnectTransaction, I get an error when trying to rollback a limbo transaction: Likely a related issue, another test (org.firebirdsql.gds.ng.wire.version10.TestV10Transaction.testBasicPrepareAndRollback) gets stuck. This test creates a connection, starts a transaction (read committed record version wait), inserts a value into a table, prepares the transaction (to be committed later in the test). Then in a separate connection + transaction (also read committed record version wait), the test attempts to select from the table. This connection is stuck fetching rows (even when setting ReadConsistency = 0 in firebird.conf). This works fine in Firebird 3 and earlier. With setting ReadConsistency = 0 in firebird.conf this is logged (after killing the test): """ RAMONA Sat Sep 29 18:28:36 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61927, user = Mark RAMONA Sat Sep 29 18:28:36 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61926, user = Mark """ With setting ReadConsistency = 1 in firebird.conf: """ RAMONA Sat Sep 29 18:31:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61943, user = Mark RAMONA Sat Sep 29 18:31:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61944, user = Mark RAMONA Sat Sep 29 18:31:27 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB internal Firebird consistency check (TPC: Attempt to mark inactive transaction to be in limbo) RAMONA Sat Sep 29 18:31:27 2018 I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. RAMONA Sat Sep 29 18:31:27 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. """ Interestingly after this test, Firebird has no problem shutting down with setting ReadConsistency = 0, but hangs with setting ReadConsistency = 1. -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Error java.sql.SQLException: internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) [SQLState:XX000, ISC error code:335544333]
I was just running the Jaybird tests against Firebird-4.0.0.1227-0_x64 (Windows 10). It has been a while since I tested with Firebird 4, so I don't know when this problem was introduced. When running the test org.firebirdsql.gds.TestReconnectTransaction.testReconnectTransaction, I get an error when trying to rollback a limbo transaction: java.sql.SQLException: internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) [SQLState:XX000, ISC error code:335544333] In short this test will create a connection, start a transaction, prepare that transaction and then close the connection. This will create a limbo transaction. It then creates a new connection, start a transaction, look for the limbo transaction, reconnects the limbo transaction, and then rolls back the limbo transaction. The rollback of the limbo transaction is what fails. I have tried setting ReadConsistency = 0 in firebird.conf, but that has made no difference. After running this test, the log contains the following entries: """ RAMONA Sat Sep 29 18:05:26 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB internal Firebird consistency check (Incorrect snapshot deallocation - too few slots) RAMONA Sat Sep 29 18:05:27 2018 INET/inet_error: read errno = 10054, client host = Ramona, address = 127.0.0.1/61486, user = Mark RAMONA Sat Sep 29 18:05:27 2018 I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. RAMONA Sat Sep 29 18:05:27 2018 Database: D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB I/O error during "WriteFile" operation for file "D:\DEVELOPMENT\PROJECT\JAYBIRD\JAYBIRD\FBTEST.FDB" Error while trying to write to file The handle is invalid. """ After this error has occurred, it also doesn't seem possible to gracefully shutdown Firebird. It seems to hang. After shutting down, new connections are rejected, but the process does not stop and needs to be killed. Attempting to connect after trying to stop Firebird logs: """ RAMONA Sat Sep 29 18:07:55 2018 Authentication error connection shutdown """ Do I need to create a ticket for this, or is this a known problem? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ODP: User-defined aggregate functions
On 29-9-2018 16:46, Karol Bieniaszewski wrote: Hi, Maybe i show my concept not so clearly. Look how simple it is with my proposition and also how simple to understand by users. Your syntax looks too much like a normal function, which I think is confusing. How will your proposal work when the aggregate function is used in for example a window function with an order by? In that case intermediate results are needed. Consider for example the difference between `count(*) over()` and `count(*) over(order by something)`, or say something like (Firebird 4) `avg(something) over(order by something rows between 5 preceding and 5 following)`. I also don't see how your syntax discerns between accumulation and finishing, for example how would the value of `accumulated` in `custom_avg` be retained? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] User-defined aggregate functions
On 29-9-2018 14:02, Adriano dos Santos Fernandes wrote: Mark, saying that an object-based interface is better to store state or the way to support external routines is disregard how good Firebird selectable procedures are. Selectable procedures stores intermediary states in a very elegant way. I am worried that it disallows (or at least complicates) more complex implementations of aggregate functions, and may not work so great when used with sliding windows. My first aggregate proposal wouldn't work well for that either, the second proposal could possibly be extended in a similar manner as PostgreSQL does. However, I have to admit, not having written a lot of aggregate functions, I'm having a hard time coming up with good examples to be able to consider both designs. It's also the way modern languages implemented the same concept recently, say JavaScript (ecmascript) and C# with yield. Do you know of an equivalent example in those languages, because I can't easily think of an equivalent aggregate function in a similar style in either JavaScript or C#. Aggregate functions are a combinations of an accumulator and a 'finisher' (and maybe a combiner if you want to be able to parallelize), and trying to combine those two is more complex (and I think it makes it brittle to write, so more susceptible to bugs). My second proposal will make it purely functional, separating the concerns of accumulating and finishing. My propose is to use the same elegant concept for aggregation. Your example seems much more confusing. It makes sense thinking on interfaces, but very different from Firebird way of doing things. It may need to have a different syntax, although I tried to follow the package body syntax in my example for consistency. Also, it does not use pass-by-reference concept. It uses underlying Firebird mechanism of messages, the same used for initial parameters values and return values. PSQL syntax may be changed to.make it more clear that it's not a parameter by reference. From the perspective of the one writing the PSQL function, it does seem to be using a pass by reference: the value of the parameter can be changed from outside while within the loop, which is something entirely new for PSQL. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] User-defined aggregate functions
On 28-9-2018 20:14, Adriano dos Santos Fernandes wrote: On 28/09/2018 15:04, Leyne, Sean wrote: -Original Message- From: Adriano dos Santos Fernandes Sent: Friday, September 28, 2018 1:55 PM To: firebird-devel@lists.sourceforge.net Subject: Re: [Firebird-devel] User-defined aggregate functions On 28/09/2018 14:35, Leyne, Sean wrote: How would this work within a GROUP BY context? I see how it works within a flat list, but when there are intermediate levels, I don't see this working. Engine does all the work for groups. Each group makes the function start - suspend (n times) - finish. So if there are 3 GROUP levels, each function would be called 3 times, one for each level? If by "group level" you mean the "group key", yes. Each time the group key changes, the function will be executed again. Within the group rows (or window partition), SUSPEND is used to feed input and produce result. Maybe Sean is talking about SQL standard grouping sets which Firebird doesn't support yet. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] User-defined aggregate functions
rns double precision as begin record_type custom_avg_record (current_count integer, accumulated double precision) function initial_state returns custom_avg_record as begin return custom_avg_record(0, 0); end function state_transition(custom_avg_record previous_state, i double precision) returns custom_avg_record as begin if (is is null) then return previous_state; return custom_avg_record( previous_state.current_count + 1, previous_state.accumulated + i); end function final_state(custom_avg_record previous_state) returns double precision return previous_state.accumulated / previous_state.current_count; end end Record type syntax etc just made up, would need more fleshing out (and possibly aligned with supporting row values). Below I put some example (tested mentally only) functions: -- -- Works as standard SUM. create aggregate function custom_sum (i integer) returns (o integer) as begin while (not agg_finished) do begin if (i is not null) then begin if (o is null) then o = 0; o = o + i; end suspend; end end -- Works as standard AVG. create aggregate function custom_avg (i double precision) returns (o double precision) as declare count integer = 0; declare accumulated double precision = 0; begin while (not agg_finished) do begin if (i is not null) then begin count = count + 1; accumulated = accumulated + i; o = accumulated / count; end suspend; end end -- Works as standard COUNT. create aggregate function custom_count (i integer) returns (o integer) as begin o = 0; while (not agg_finished) do begin if (i is not null) then o = o + 1; suspend; end end -- This function shows the difference of returning value in SUSPEND when data set is not empty and returning in function termination when data set is empty. -- select custom_count_plus_1000(1) from rdb$database -- returns 1 -- select custom_count_plus_1000(1) from rdb$database where 1 = 0 -- returns 1000 create aggregate function custom_count_plus_1000 (i integer) returns (o integer) as begin o = 0; while (not agg_finished) do begin o = o + 1; suspend; end o = o + 1000; end -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Transliteration problem with table locks?
On 18-9-2018 17:33, Alex Peshkoff via Firebird-devel wrote:
On 09/16/18 07:58, Kjell Rilbe wrote:
Hi,
Got no reply in firebird-support, so trying here. I've been using
Firebird since a long time, successfully using quoted identifiers with
Swedish characters åäö in them, but have now stumbled upon a problem.
I am at version 3.0.3 (on Windows Server 2016, 64 bit).
Tried this code in C# using FirebirClient 6.3.0.0:
Dictionary locks = new
Dictionary() {
{ "KörningInstans", FbTransactionBehavior.Protected |
FbTransactionBehavior.LockWrite },
{ "Körning", FbTransactionBehavior.Protected |
FbTransactionBehavior.LockWrite }
}; // Note "ö" in table names above!
FbTransaction trans = connection.BeginTransaction(new
FbTransactionOptions() {
LockTables = locks,
TransactionBehavior = FbTransactionBehavior.Consistency |
FbTransactionBehavior.Write
}); // Exception
The BeginTransaction call fails with an exception "arithmetic
exception, numeric overflow, or string truncation
Cannot transliterate character between character sets". Error code in
the exception is 335544321. SQLSTATE 22000. It contains 3 errors:
1. type 1, error code 335544321, no message.
2. type 1, error code 335544565, no message.
3. type 0, error code 335544321, message "arithmetic exception,
numeric overflow, or string truncation\r\nCannot transliterate
character between character sets".
The database is created like this in isql:
create database 'KorningarDev' user DEV password '***' page_size 4096
set names 'UTF8' default character set UTF8 collation UNICODE;
And the connection string looks like this:
Server=xxx.xxx.xxx.xxx;Port=3050;Database=KorningarDev;Charset=UTF8;User=DEV;Password=***
I see no reason why there would be any transliteration problems:
Windows unicode -> UTF8, and then UTF8 all the way, right?
Is this a bug, and would that bug be most likely in .NET
FirebirdClient or in the FB engine? (I.e. where should I report it?)
Look like a bug. At least I do not see any place where engine
transliterates TPB in the code.
On the other hand not sure was this particular bug caused by this or
with given connection parameters .net client should provide all string
already in utf8.
Test case using native client is highly welcome.
It looks like the Firebird .net provider uses Encoding.Default for TPB
string properties, which on most platforms - IIRC - is not UTF-8, see
https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/74580961c9ada64aea49dcaabacae0de4fac3540/Provider/src/FirebirdSql.Data.FirebirdClient/Common/TransactionParameterBuffer.cs#L38
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Login with mixed case fails
On 17-9-2018 14:58, Alex Peshkoff via Firebird-devel wrote: Actual job is done by fb_utils::dpbItemUpper. This function has one 'funny' feature - in almost all cases (exception is support of dialect-1 roles) this function should be invoked for each dpb item once & only once. And itr's not invoked by SRP plugin (you call it SRP client?) - that's done for all plugins when preparing client authentication block for plugins, it's ClntAuthBlock::loadClnt function. Except call to this function username is always passed 'as is' - if it was in double quotes it remains such, it's never uppercased except in dpbItemUpper. Although I may have mislocated that, but it seems that the only plugin where this is relevant right now is the Srp plugin, right? That does make you wonder if this shouldn't be moved from ClntAuthBLock to SrpClient. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Login with mixed case fails
On 17-9-2018 15:36, Alex Peshkoff via Firebird-devel wrote: On 09/17/18 16:31, Mark Rotteveel wrote: Lets rephrase my question. Assuming I have created a user using create user "CaseSensitive" password 'password' using plugin Srp; Exactly which value must I pass from server to client (assuming the wire protocol, not any transformations fbclient does on its own) in: Also assuming that in DPB "CaseSensitive" was passed... 1. CNCT_login: CaseSensitive or "CaseSensitive" 2. isc_dpb_user_name: CaseSensitive or "CaseSensitive" Both cases - "CaseSensitive" and exactly what must I use in the SRP client proof: CaseSensitive or "CaseSensitive". CaseSensitive Thanks, that seems to work. It looks like when I tried this earlier I missed an instance of uppercasing in Jaybird's code. I'll also see if I can create a pull request to fix this in Firebird .net provider. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Login with mixed case fails
On 17-9-2018 14:58, Alex Peshkoff via Firebird-devel wrote: On 09/17/18 15:45, Mark Rotteveel wrote: On 16-9-2018 17:28, Mark Rotteveel wrote: I think you should probably post this to the Firebird .net provider mailing list. The problem seems to be that case-sensitive user names need to be correctly supported by the client. And in the case of Firebird.net provider (and Jaybird for that matter), for example the SRP implementation will always upper case the username, which makes the SRP proof for a case sensitive user name fail. The thing that needs to be addressed here that - in my opinion- this is a problem that is caused by lack of proper low-level documentation. Exactly how are case sensitive usernames supposed to be handled? I have done some searching in the Firebird sources, and I'm not sure if I found all relevant parts, but as far as I can tell: - isc_dpb_user_name : pass username in double quotes from client to server (and same for spb) - CNCT_login : pass username in double quotes - SRP client: If enclosed in double quotes: strip quotes and use as is, if enclosed in single quotes, strip quotes and uppercase, if unquoted, uppercase. Is this correct? Or are am I missing something? Actual job is done by fb_utils::dpbItemUpper. This function has one 'funny' feature - in almost all cases (exception is support of dialect-1 roles) this function should be invoked for each dpb item once & only once. And itr's not invoked by SRP plugin (you call it SRP client?) - that's done for all plugins when preparing client authentication block for plugins, it's ClntAuthBlock::loadClnt function. Except call to this function username is always passed 'as is' - if it was in double quotes it remains such, it's never uppercased except in dpbItemUpper. That doesn't really answer my question as to me that authentication related code is a tangle that I always get lost in. Lets rephrase my question. Assuming I have created a user using create user "CaseSensitive" password 'password' using plugin Srp; Exactly which value must I pass from server to client (assuming the wire protocol, not any transformations fbclient does on its own) in: 1. CNCT_login: CaseSensitive or "CaseSensitive" 2. isc_dpb_user_name: CaseSensitive or "CaseSensitive" and exactly what must I use in the SRP client proof: CaseSensitive or "CaseSensitive". Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Login with mixed case fails
On 16-9-2018 17:28, Mark Rotteveel wrote: I think you should probably post this to the Firebird .net provider mailing list. The problem seems to be that case-sensitive user names need to be correctly supported by the client. And in the case of Firebird.net provider (and Jaybird for that matter), for example the SRP implementation will always upper case the username, which makes the SRP proof for a case sensitive user name fail. The thing that needs to be addressed here that - in my opinion- this is a problem that is caused by lack of proper low-level documentation. Exactly how are case sensitive usernames supposed to be handled? I have done some searching in the Firebird sources, and I'm not sure if I found all relevant parts, but as far as I can tell: - isc_dpb_user_name : pass username in double quotes from client to server (and same for spb) - CNCT_login : pass username in double quotes - SRP client: If enclosed in double quotes: strip quotes and use as is, if enclosed in single quotes, strip quotes and uppercase, if unquoted, uppercase. Is this correct? Or are am I missing something? Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Login with mixed case fails
I think you should probably post this to the Firebird .net provider mailing list. The problem seems to be that case-sensitive user names need to be correctly supported by the client. And in the case of Firebird.net provider (and Jaybird for that matter), for example the SRP implementation will always upper case the username, which makes the SRP proof for a case sensitive user name fail. The thing that needs to be addressed here that - in my opinion- this is a problem that is caused by lack of proper low-level documentation. Mark On 16-9-2018 11:54, Kjell Rilbe wrote: Hi, In FB 3.0.3 it's possible to create a user with mixed case like this in isql: create user "MixedCaseName" password '***'; After that, I create a database in isql, referencing the already defined alias 'MyDB': create database 'MyDB' user "MixedCaseName" password '***' page_size 4096 set names 'UTF8' default character set UTF8 collation UNICODE; Then I try to connect using .NET FirebirdClient 6.3.0 with this connections string: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User='"MixedCaseName"';Password=*** This fails with an error saying "Your user name and password are not defined. Ask your database administrator to set up a Firebird login.". I checked that the connection string is (correctly) parsed so that the user name that's actually submitted by the .NET FirebirdClient is "MixedCaseName" with double quotes included. I've also tried these connections strings, but none of them work: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User="MixedCaseName";Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User='MixedCaseName';Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User=MixedCaseName;Password=*** In all these cases, the user name is parsed so that it is sent to the server qithout quotes, and the same error occurs. Finally, switching the single and double quotes: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User="'MixedCaseName'";Password=*** The password is parsed so that it is sent with the single quotes 'MixedCaseName' to the server. Same error. It seem to me that the .NET FirebirClient does a correct job parsing the connection string, but no matter how the user name is sent to the server, the login fails. Trying the exact same thing with a uppercase user name works when the username is specified so that it is sent to the server without quotes, but fails with the same error if it's sent with double or single quotes. I.e. these work: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User="UPPERNAME";Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User='UPPERNAME';Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User=UPPERNAME;Password=*** But these fail: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User="'UPPERNAME'";Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User='"UPPERNAME"';Password=*** I also note that in this case, it doesn't matter what case is used for the user name. I.e. These also work: Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User="Uppername";Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User='uPpErNaMe';Password=*** Server=xxx.xxx.xxx.xxx;port=3050;Database=MyDB;Charset=UTF8;User=upperNAME;Password=*** As far as I understand this: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-login-international.html Firebird 3 is supposed to fully support mixed-case user names. Seems not to work in this case, or am I missing something? Neither user name nor password uses anything except a-z, A-Z, 0-9 and & in any of these tests, so there should be no problems caused by OEM/ANSI/UTF8 code page mixups. Mvh, Kjell Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] GDS Exception. 335544761. too many open handles to database
On 2018-09-13 19:16, Leyne, Sean wrote: Our Java application is receiving the noted error, have done a web search for details on same without luck. Can someone explain what the error means. See https://stackoverflow.com/q/39148222/466862 In short, this error occurs if you allocate too many statements, transactions, blobs and maybe some other object types on a single connection without releasing them (closing, etc). This could indicate that your code is not properly closing resources, or maybe a bug in your Jaybird version that causes objects to not be properly closed, or maybe a bug in Firebird that doesn't properly release handles. This problem could be more pronounced if your connections have a long lifetime, or if you use a connection pool without a max connection lifetime (or with a very high max), because in that case minor resource leaks can accumulate. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Expected dates for 2.5.9 and 3.0.4 releases?
When can we expect the release of Firebird 2.5.9 and 3.0.4? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FB4 - Windows snapshots vs ICU
On 2018-08-03 07:51, Gabor Boros wrote: 2018. 08. 02. 13:53 keltezéssel, Gabor Boros írta: Is some external files needed or wrong files included in the snapshots? Yes, two external files. msvcp100.dll and msvcr100.dll from Microsoft Visual C++ 2010 SP1 Redistributable Package. That sounds wrong. I thought we were using a newer version of Visual C++ for Firebird 4, shouldn't that include the ICU lib? Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Read consistency patch
On 2018-07-24 10:13, liviuslivius wrote: Hi, question about this patch https://github.com/FirebirdSQL/firebird/pull/105 Is this possible that this CN(commit number) of transactions (pair transaction id + CN) can be stored into some table? Is it possible to retrive most recent CN from transaction start point (call this SCN). The term SCN is already used by nbackup, lets not muddle the waters by overloading it for another usecase. It can provide simple way to retrive new records/changes in tables (new feature). E.g. whe can then do SELECT * FROM TABLEX WHERE CN>:SCN; You may want to look at RDB$RECORD_VERSION introduced in Firebird 3, see https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch03s05.html#d0e5251 and https://www.ibphoenix.com/resources/documents/contributed/doc_393 Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Jaybird hang on Firebird 4 with modified TcpRemoteBufferSize
On 23-7-2018 17:25, Adriano dos Santos Fernandes wrote: On 23/07/2018 11:12, Mark Rotteveel wrote: The problem can't be reproduced with the native client, which suggests Jaybird does something wrong, or this is something that is only triggered by what Jaybird does. Mark, are you dealing with this situation re. partial packets with negative lengths? // Send data in manageable hunks. If a packet is partial, indicate // that with a negative length. A positive length marks the end. Those lengths don't surface in the wire protocol itself, but I do wonder, if length can be negative, whether the following is correct: const SSHORT l = (SSHORT) MIN(length, INET_remote_buffer) Sounds to me like this could be problematic if length is negative and smaller than -1 * INET_remote_buffer. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Jaybird hang on Firebird 4 with modified TcpRemoteBufferSize
On 23-7-2018 15:07, Alex Peshkoff via Firebird-devel wrote: On 22.07.2018 18:03, Mark Rotteveel wrote: On 22-7-2018 16:14, Dimitry Sibiryakov wrote: 22.07.2018 14:56, Mark Rotteveel wrote: If I change one of the two test classes that triggers this problem to create a single database for the entire test class, the problem changes from a too large buffer being read (which causes the hang) to an error Unsupported or unexpected operation code 16777216 in processOperation [SQLState:08000, ISC error code:337248276] This error means that Jaybird received a response packet with an operation code of 16777216 instead of a valid operation code (op_response (9), op_fetch_response (66) or op_sql_response (78)). It looks like the previous packet was a little smaller than you expected because 16777216 is "01 00 00 00" which may be a valid piece of status vector. It is entirely possible the problem already occurred with an earlier read, but given that Jaybird itself does nothing with the TcpRemoteBufferSize setting of Firebird, it would suggest the problem is on the side of Firebird, because the only difference is that setting. I don't have this problem if TcpRemoteBufferSize has the default value, nor with some other values, and only if wire encryption is used. When I tested it back in March, it worked fine with TcpRemoteBufferSize value 8192, 32755, 32756, 32760, 32761 and 32764, but broke with 8191, 32759, 32765, 32766 and 32767 (I haven't retested all those values). Mark, I tried to reproduce this with c++ client. I did the following - set in FB4 TcpRemoteBufferSize=8191, connected to it using FB3 client (to avoid having same size preset at both ends of wire link) 'isql -user sysdba -pas masterkey localhost:employee' and in order to produce some network load did: execute block returns(x int) as begin x=0; while (x < 100) do begin x=x+1; suspend; end end but nothing bad happens. Please can yo tell me what exactly should I run to reproduce an error. The problem can't be reproduced with the native client, which suggests Jaybird does something wrong, or this is something that is only triggered by what Jaybird does. Back in March I provided a sample testcase that runs the test classes necessary to reproduce this. The error is currently only triggered when running multiple testcases that should be independent from the perspective of Jaybird. I'll see if I can further reduce that to a single testcase. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md I just took another look, the format for isc_dpb_session_time_zone is undocumented. Looking at the implementation it is a string like passed to SET TIME ZONE, is that correct? I'd also like to know what the plans are for this: will it land in Firebird 4? If so: when? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Jaybird hang on Firebird 4 with modified TcpRemoteBufferSize
This problem still occurs with 4.0.0.1059-0_x64, and I'm no closer to finding a cause, except that it looks like Firebird is somewhere encoding integer values incorrectly or seems to suffer from a buffer overflow somewhere depending on the configured value of TcpRemoteBufferSize. If I change one of the two test classes that triggers this problem to create a single database for the entire test class, the problem changes from a too large buffer being read (which causes the hang) to an error Unsupported or unexpected operation code 16777216 in processOperation [SQLState:08000, ISC error code:337248276] This error means that Jaybird received a response packet with an operation code of 16777216 instead of a valid operation code (op_response (9), op_fetch_response (66) or op_sql_response (78)). Mark On 27-3-2018 19:08, Mark Rotteveel wrote: I increased the TcpRemoteBufferSize to 32767 (the documented maximum value) in my firebird.conf, and when I run the full test suite of Jaybird, the tests hang consistently on the same test. It is blocked on reading from the socket during a fetch. If I run this test in isolation it works, if I run all tests in the same class it works. It only happens when I run all tests. Given this test (like most Jaybird tests) creates a new database, it likely isn't a problem with the test itself. If I disable wire encryption, it works. If I comment out TcpRemoteBufferSize and re-enable wire encryption, it works. If I set TcpRemoteBufferSize to 16384 or 32760 it also works fine. This all suggests that maybe some data isn't flushed, or there might be a buffer overflow or another type of boundary issue somewhere, or that maybe somewhere an assumption of multiples of 8 bytes has crept in. I'm not sure how long this problem has been there (and maybe it already exists in Firebird 3), I do recall having similar problems before, but I'm not sure if I changed TcpRemoteBufferSize when that happened (I sometimes do and sometimes don't change this setting). I'll see if I can find the time and energy later this week to run these tests on earlier snapshots and Firebird 3 as well, and maybe try some different values. I hope in the meantime, that maybe one of you has an idea what the problem might be. -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [moved from support] LOCALTIME and LOCALTIMESTAMP
On 2018-07-18 22:51, livius wrote: Hi, thank you for the link. If i understand correctly then all functions "CURRENT_..." and "LOCAL_..." result are in session TZ? Note that it is LOCALTIME and LOCALTIMESTAMP, so no underscore there. If yes, then now it is much clearer, I thought that "CURRENT_" state for server time zone retrieved from e.g. system and "LOCAL_" was for database timezone or something like this. will be good if you provide some example in provided link. Something like this (if I have not mistaken something): old way FB3: SERVER TZ = "-02:00" CURRENT_TIMESTAMP will be "2018-07-18 23:00:00" CURRENT_TIME will be "23:00:00" CURRENT_DATE will be "2018-07-18" new way FB4: SERVER TZ = "-02:00" SESSION TZ = "+02:00" CURRENT_TIMESTAMP will be "2018-07-19 03:00:00 +02" CURRENT_TIME will be "03:00:00 +02" LOCAL_TIMESTAMP will be "2018-07-19 03:00:00" LOCAL_TIME will be "03:00:00" CURRENT_DATE will be "2018-07-19" One more question. How phisically are TZ info stored in field? Is field alwas written in GMT and do not require any more bytes in the database? And is converted to session timezone? This is answered in https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Authentication plugin mismatch not clearly reported to client
On 25-6-2018 12:26, Dimitry Sibiryakov wrote: 25.06.2018 12:22, Alex Peshkoff via Firebird-devel wrote: This attack does not depend on plugin name knowledge. If one is using legacy plugin no need to try >8 chars passwords. This is prevented by timeout after 3 unsuccessful logins. You may start completely block account after that instead. That is a security anti-pattern, as that would allow you to simply execute a denial-of-service attack blocking a valid user by failing authentication a few times. Back-off/timeout or IP-based rate-limiting are better solutions. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Authentication plugin mismatch not clearly reported to client
On 25-6-2018 10:35, Alex Peshkoff via Firebird-devel wrote: On 25.06.2018 10:47, Mark Rotteveel wrote: On 2018-06-24 20:49, Alex Peshkoff via Firebird-devel wrote: Because it's bad idea to open to client (specially not authenticated) details of problems with authentication. I agree with that in general, but in this specific case I don't see the need for that. Communicating about a mismatch in plugins between server and client is not a risk Afraid you are wrong here. It helps an attacker to detect what plugin is actually used by server (for example - srp or srp256) and use that info to attack particular plugin later. That argument doesn't make much sense to me. If an attacker wants to probe which plugins a server supports, then they can try to connect with a client that passes all known plugins in CNCT_plugin_list, the server is then happy to announce all plugins it supports in p_acpt_keys. That only wouldn't work if the server is using an unknown or obscure third-party plugin (although maybe leaving out CNCT_plugin_list would still lead to the server announcing the list, not sure?). A subsequent 'attack' could then focus on the assumed vulnerable plugin. In other words, communicating that there is no overlap between plugins requested by client and supported server in itself does not leak important information, but it does simplify troubleshooting for the user without having to access the Firebird log file. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Passing plugin list to native client
On 25-6-2018 10:32, Alex Peshkoff via Firebird-devel wrote: On 25.06.2018 10:51, Mark Rotteveel wrote: On 2018-06-24 20:51, Alex Peshkoff via Firebird-devel wrote: Because it's as designed. What problems with it? Having to construct the config string is awkward, especially when you already have a mechanism that is used to communicate other connection properties: that is individual items in the DPB. People use it for many years to create trace configurations. Much more often I've used to hear that building DPB is very hard programming task ;) But what about text pairs Name=Value you are the first. I didn't say it was hard, I said it was awkward, because it deviates from the normal pattern. Jaybird has all the plumbing to populate DPBs items, so that was already a solved problem. Especially given the client itself does use isc_dpb_auth_plugin_list to communicate its plugins to the server, it is surprising that the user application can't communicate its intent to the client in the same way. Item added to commuicate between client and server inside DPB. But I agree that it's presence is an argument to be understood from client app too - please add ticket to the tracker. Done, http://tracker.firebirdsql.org/browse/CORE-5860 -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Passing plugin list to native client
On 2018-06-24 20:51, Alex Peshkoff via Firebird-devel wrote: On 23.06.2018 19:05, Mark Rotteveel wrote: When using the native fbclient, why can't I use isc_dpb_auth_plugin_list/isc_spb_auth_plugin_list to pass the authentication plugins to try, and why do I need to use the isc_dpb_config/isc_spb_config with "AuthClient = " (+ other config items if necessary)? Because it's as designed. What problems with it? Having to construct the config string is awkward, especially when you already have a mechanism that is used to communicate other connection properties: that is individual items in the DPB. Especially given the client itself does use isc_dpb_auth_plugin_list to communicate its plugins to the server, it is surprising that the user application can't communicate its intent to the client in the same way. Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Authentication plugin mismatch not clearly reported to client
On 2018-06-24 20:49, Alex Peshkoff via Firebird-devel wrote: On 23.06.2018 17:06, Mark Rotteveel wrote: Why is an authentication plugin mismatch (as in the list of plugins between client and server have no overlap) not clearly communicated to the client? For example if I have AuthServer = Srp256,Srp,Legacy_Auth and the client only tries Srp224, then the error returned to the client is Error occurred during login, please check server firebird.log for details [SQLState:08006, ISC error code:335545106] With entry in the log: RAMONA Sat Jun 23 16:01:45 2018 Authentication error No matching plugins on server Why is the error "Authentication error" + "No matching plugins on server" not reported back to the client? Because it's bad idea to open to client (specially not authenticated) details of problems with authentication. I agree with that in general, but in this specific case I don't see the need for that. Communicating about a mismatch in plugins between server and client is not a risk and it can simplify troubleshooting a lot. Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Passing plugin list to native client
When using the native fbclient, why can't I use isc_dpb_auth_plugin_list/isc_spb_auth_plugin_list to pass the authentication plugins to try, and why do I need to use the isc_dpb_config/isc_spb_config with "AuthClient = " (+ other config items if necessary)? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Authentication plugin mismatch not clearly reported to client
Why is an authentication plugin mismatch (as in the list of plugins between client and server have no overlap) not clearly communicated to the client? For example if I have AuthServer = Srp256,Srp,Legacy_Auth and the client only tries Srp224, then the error returned to the client is Error occurred during login, please check server firebird.log for details [SQLState:08006, ISC error code:335545106] With entry in the log: RAMONA Sat Jun 23 16:01:45 2018 Authentication error No matching plugins on server Why is the error "Authentication error" + "No matching plugins on server" not reported back to the client? -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Fwd: [FB-Tracker] Commented: (CORE-5788) Proposed Security Patch: Replacement of use of SHA-1 in the SRP Client Proof with SHA-256
On 21-6-2018 14:07, Alex Peshkoff via Firebird-devel wrote: I've forwarded this FYI to devel & admin. [ http://tracker.firebirdsql.org/browse/CORE-5788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=33942#action_33942 ] Status of Srp256 plugin in FB3 & FB4. In master branch Srp256 (with enhanced security) becomes single default authentication plugin. That means that with default configuration clients earlier than FB 3.0.4 will be not able to attach to FB4. This should not be severe problem - hopefully most of clients will be upgraded when FB4 is released. In B3_0_Release default plugin is old Srp - I've decided not to break compatibility with existing clients in point release. Hope that fits requirements of most users. People who need enhanced security should upgrade all clients to at least 3.0.4 and set AuthServer=Srp256 in firebird.conf. In all cases hashes stored in security database are fully compatible between Srp & Srp256, i.e. security.db does not require any upgrade. I have added support for this in Jaybird 3.0.5 and Jaybird 4. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Fwd: [FB-Tracker] Commented: (CORE-5788) Proposed Security Patch: Replacement of use of SHA-1 in the SRP Client Proof with SHA-256
On 21-6-2018 16:20, Tony Whyman wrote: Be careful: if you set the ClientAuth to Srp,Srp256 and the ServerAuth is Srp256,Srp, then you will always use SHA-1 to generate the client proof and you get no benefit from the patch. Ideally the client is Srp256 only, with Srp256,Srp to allow for compatibility with older Firebird 3 servers only. I understand that, but I was thinking about when introducing this now in a Jaybird 3 point-release. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Fwd: [FB-Tracker] Commented: (CORE-5788) Proposed Security Patch: Replacement of use of SHA-1 in the SRP Client Proof with SHA-256
On 21-6-2018 15:57, Alex Peshkoff via Firebird-devel wrote: On 21.06.2018 16:36, Mark Rotteveel wrote: That is everything continues to use SHA-1, except the generating of M itself. As far as I can see - yes. The only detail that you need to support both cases depending upon plugin name. Yes, I think I'll modify the SrpClient class to accept the relevant hash algorithm name. Now I only need to decide how I'm going to order the auth. Srp256, Srp is more secure, but Srp, Srp256 is probably faster when connecting to current Firebird 3 versions. Maybe it is time I introduce a connection property for that. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Fwd: [FB-Tracker] Commented: (CORE-5788) Proposed Security Patch: Replacement of use of SHA-1 in the SRP Client Proof with SHA-256
On 21-6-2018 14:07, Alex Peshkoff via Firebird-devel wrote:
I've forwarded this FYI to devel & admin.
[
http://tracker.firebirdsql.org/browse/CORE-5788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=33942#action_33942
]
Status of Srp256 plugin in FB3 & FB4.
In master branch Srp256 (with enhanced security) becomes single default
authentication plugin. That means that with default configuration
clients earlier than FB 3.0.4 will be not able to attach to FB4. This
should not be severe problem - hopefully most of clients will be
upgraded when FB4 is released.
In B3_0_Release default plugin is old Srp - I've decided not to break
compatibility with existing clients in point release. Hope that fits
requirements of most users. People who need enhanced security should
upgrade all clients to at least 3.0.4 and set
AuthServer=Srp256
in firebird.conf.
In all cases hashes stored in security database are fully compatible
between Srp & Srp256, i.e. security.db does not require any upgrade.
If I understand the patch correctly, then - looking at the Jaybird code
- the only place affected would be
byte[] clientProof(String user, String password, byte[] salt,
BigInteger serverPublicKey) {
final byte[] K = getClientSessionKey(user, password, salt,
serverPublicKey);
final BigInteger n1 = fromBigByteArray(sha1(toBigByteArray(N)));
final BigInteger n2 = fromBigByteArray(sha1(toBigByteArray(g)));
final byte[] M = sha1(toBigByteArray(n1.modPow(n2, N)),
sha1(user.toUpperCase().getBytes(StandardCharsets.UTF_8)),
salt,
toBigByteArray(publicKey), toBigByteArray(serverPublicKey), K);
sessionKey = K;
return M;
}
and then specifically the line
final byte[] M = sha1(toBigByteArray(n1.modPow(n2, N)),
sha1(user.toUpperCase().getBytes(StandardCharsets.UTF_8)), salt,
toBigByteArray(publicKey), toBigByteArray(serverPublicKey), K);
should become
final byte[] M = shaXXX(
toBigByteArray(n1.modPow(n2, N)), // n1 in FB
sha1(user.toUpperCase().getBytes(StandardCharsets.UTF_8)), // n2 in FB
salt,
toBigByteArray(publicKey),
toBigByteArray(serverPublicKey),
K);
Where XXX is the relevant SHA variant applied.
That is everything continues to use SHA-1, except the generating of M
itself.
--
Mark Rotteveel
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Closed: (CORE-5726) Unclear error message when inserting value exceeding max of dec_fixed decimal
On 19-6-2018 18:06, livius wrote: it is better to change all already errors to new one in new point release as FB4 is. There is no need for a new error, but a consistent error scheme should be applied, as already exists. BTW: Firebird 4 is not a point release, it is a major release. A point release is something like 2.5.8, which is a point release for Firebird 2.5. -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Closed: (CORE-5726) Unclear error message when inserting value exceeding max of dec_fixed decimal
On 2018-06-18 14:49, Alex Peshkoff via Firebird-devel wrote: On 17.06.2018 13:17, Mark Rotteveel wrote: I don't agree with this ticket getting closed. I raised an objection to the current solution in the comments that I think needs to be addressed. The current solution causes a divergence in errors between decimals(p,s) with p <=18 and 18 < p <=34, which is confusing and leaks implementation details to the user. In the same situation it should raise the same errors. Error 'arithmetic exception, numeric overflow, or string truncation' is too widely used here & there in our codebase. Therefore it was suggested by Dmitry Emanov to avoid that error when dealing with decimal float digits, use instead exact native error codes. I do not remember where and how was it discussed, we often talk privately but sometimes discuss such thing on forums too. The current error is specific, it is arithmetic exception, numeric overflow, or string truncation numeric value is out of range In other words, group "arithmetic exception, numeric overflow, or string truncation" (which - or similar - IIRC is also mentioned in the SQL standard), with specific error "numeric value is out of range". This combination is also valid for other numeric datatypes, and I don't see a good reason to deviate here, as it will break common error handling or make it more problematic. Must say that if we take some decision changing this particular message I suppose it will be better to review all related errors related with decimal floats. On my mind if we decide to do it better do sooner than later - beta release is hopefully coming soon. I think that would be a good idea. Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Closed: (CORE-5726) Unclear error message when inserting value exceeding max of dec_fixed decimal
On 2018-06-18 15:10, Carlos H. Cantu wrote: I dream about the day that "arithmetic exception, numeric overflow, or string truncation" will be replaced by more specific errors. You may want to look closer at that error: it actually consists of two error codes, which will pretty much narrow it down to the specific problem, while at the same time allow for handling for a group of similar errors. The "arithmetic exception, numeric overflow, or string truncation" is just the wider group, while the second error code narrows it down to the specific problem (in this case "numeric value is out of range"). Mark -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] [FB-Tracker] Closed: (CORE-5726) Unclear error message when inserting value exceeding max of dec_fixed decimal
I don't agree with this ticket getting closed. I raised an objection to the current solution in the comments that I think needs to be addressed. The current solution causes a divergence in errors between decimals(p,s) with p <=18 and 18 < p <=34, which is confusing and leaks implementation details to the user. In the same situation it should raise the same errors. Compare Error: *** IBPP::SQLException *** Context: Statement::Execute( insert into extdecimal(dec_18_18) values (10) ) Message: isc_dsql_execute2 failed SQL Message : -802 Arithmetic overflow or division by zero has occurred. Engine Code: 335544321 Engine Message : arithmetic exception, numeric overflow, or string truncation numeric value is out of range vs Error: *** IBPP::SQLException *** Context: Statement::Execute( insert into extdecimal(dec34_34) values (1) ) Message: isc_dsql_execute2 failed Engine Code: 335545141 Engine Message : Decimal float invalid operation. An indeterminant error occurred during an operation. numeric value is out of range Mark On 17-6-2018 08:58, Pavel Zotov (JIRA) wrote: [ http://tracker.firebirdsql.org/browse/CORE-5726?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Pavel Zotov closed CORE-5726. - Unclear error message when inserting value exceeding max of dec_fixed decimal - Key: CORE-5726 URL: http://tracker.firebirdsql.org/browse/CORE-5726 Project: Firebird Core Issue Type: Bug Components: Engine Affects Versions: 4.0 Alpha 1 Environment: Firebird-4.0.0.864-0_x64 (Windows 10) Reporter: Mark Rotteveel Assignee: Alexander Peshkov Fix For: 4.0 Beta 1 Inserting a value that exceeds the precision of a dec_fixed decimal column results in an unclear exception message. Table: create table extdecimal ( id integer generated always as identity primary key, dec34_34 decimal(34, 34) ); Insert: insert into extdecimal(dec34_34) values (1); This yields error: Error: *** IBPP::SQLException *** Context: Statement::Execute( insert into extdecimal(dec34_34) values (1) ) Message: isc_dsql_execute2 failed SQL Message : -901 Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements Engine Code: 335545141 Engine Message : Decimal float invalid operation. An indeterminant error occurred during an operation. Instead it should raise error 335544321 (arithmetic exception, numeric overflow, or string truncation) + error 335544916 (numeric value is out of range), as 1 doesn't fit in a decimal(34, 34). For comparison, a decimal(18,18) will accept 1-9 (as it is actually precision 19 with some caveats), but raise 335544321 + 335544916 when inserting 10 or higher. -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Listing all SYSTEM context variable names
On 15-6-2018 20:33, Leyne, Sean wrote:
That is not within Firebird itself. I want to be able to do something like
select RDB$GET_CONTEXT('SYSTEM', variable_name) from
or even
select variable_name, variable_value from
Sorry, are you looking for a list of the *names* or the names *and* the values?
Yes, I'm looking for both. However, if I have a selectable source for
the names, then getting the values is trivial (see my first select
above, although it would have been better if I had used
select variable_name, RDB$GET_CONTEXT('SYSTEM', variable_name) as
variable_value from
Why do you feel that you need to "SELECT" that list? (I see it similar to the
list of KEYWORDS -- it is not something that is SELECTable)
Because I want to be able to automatically produce a list of those
values, in a preferably maintenance-free and version-independent way.
I would suggest extending MON$CONTEXT_VARIABLES, including a
MON$NAME_SPACE column, and then including the SYSTEM variables.
BTW: Having a selectable source of keywords would actually be a great idea.
Mark
--
Mark Rotteveel
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Listing all SYSTEM context variable names
On 15-6-2018 17:58, Leyne, Sean wrote:
Mark,
Is there currently an option (table (virtual?) or otherwise) to list all the
SYSTEM context variable names for use with RDB$GET_CONTEXT (and maybe
the values)?
They are in the README.context_variables file in the doc\sql.extensions folder
That is not within Firebird itself. I want to be able to do something like
select RDB$GET_CONTEXT('SYSTEM', variable_name) from
or even
select variable_name, variable_value from
I can do that for USER_SESSION and USER_TRANSACTION.
Mark
--
Mark Rotteveel
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Listing all SYSTEM context variable names
On 15-6-2018 17:25, Dimitry Sibiryakov wrote: 15.06.2018 17:19, Mark Rotteveel wrote: Having to rely on external lists like the Firebird 2.5 language reference is a bit too brittle for my tastes, I'd like the truth according to the Firebird itself. But documentation is the only place where you can see name of variable and its meaning. Knowledge of names without meaning is pointless. What can you do with variable 'FOO' having value 'BAR' (except simple display to an user which also have no idea what they are for)? Have you ever considered I simply want to list all values of all system context variables. The tool doing that won't care about its meaning, that will be the job of the person consulting the output. Having to explicitly list all values within my sources is brittle as it will miss values introduced in newer versions and gives additional maintenance overhead. And alternatively, Firebird could consider to make it self-documenting, by including a description. That would be similar to what - for example - PostgreSQL's pg_settings view does. Although the existing SYSTEM namespace variables are relatively self-descriptive anyway. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Listing all SYSTEM context variable names
Is there currently an option (table (virtual?) or otherwise) to list all the SYSTEM context variable names for use with RDB$GET_CONTEXT (and maybe the values)? There is MON$CONTEXT_VARIABLES, but that only lists the USER_SESSION and USER_TRANSACTION values. Having to rely on external lists like the Firebird 2.5 language reference is a bit too brittle for my tastes, I'd like the truth according to the Firebird itself. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?
On 10-6-2018 12:36, Vlad Khorsun via Firebird-devel wrote: Mark, all I just committed changes as we discussed above. I.e. session reset now ignores prepared transactions, rollback currently active user transaction and start new one, issue warning if user transaction made changes in tables. Thanks! Here is how it looks now: SQL> alter session reset; Statement failed, SQLSTATE = 01002 Cannot reset user session -There are open transactions (2 active) SQL> SQL> commit; SQL> set autoddl off; SQL> SQL> alter session reset; SQL> SQL> insert into x values (current_transaction); SQL> select * from x; ID 311 362 SQL> alter session reset; Session was reset with warning(s) -Transaction is rolled back due to session reset, all changes are lost SQL> SQL> select * from x; ID 311 SQL> select current_transaction from rdb$database; CURRENT_TRANSACTION = 363 SQL> set warning off; SQL> insert into x values (current_transaction); SQL> select * from x; ID 311 363 SQL> alter session reset; SQL> select * from x; ID 311 SQL> select current_transaction from rdb$database; CURRENT_TRANSACTION = 364 Regards, Vlad -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Pseudo columns in Firebird
On 10-6-2018 11:37, Dmitry Yemanov wrote: 09.06.2018 16:09, Mark Rotteveel wrote: Besides RDB$DB_KEY and RDB$RECORD_VERSION, does Firebird have any other pseudo columns? No, it doesn't. Thanks! -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Pseudo columns in Firebird
On 10-6-2018 10:44, fbbt wrote: You look for something like RDB$TRANSACTION_ID? :) I think you'll find that is called RDB$RECORD_VERSION (introduced in Firebird 3). I'm wondering if there are others, so I can improve the implementation of DatabaseMetaData.getPseudoColumns in Jaybird. On 09.06.2018 20:09 Mark wrote: Besides RDB$DB_KEY and RDB$RECORD_VERSION, does Firebird have any other pseudo columns? -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
[Firebird-devel] Pseudo columns in Firebird
Besides RDB$DB_KEY and RDB$RECORD_VERSION, does Firebird have any other pseudo columns? -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 5-6-2018 18:10, Lester Caine wrote: I do not recall seeing any discussion on the ground rules for handling timezone offsets prior to an implementation being proposed? It could then have been pointed out that the SQL rules simply don't work in a lot of cases. The idea of listing the end time assumes that there will only ever be 4 decimal places of second accuracy, but the involves cropping current time systems. Avoiding the problem makes a lot more sense. Firebird timestamps have a precision of 100 microseconds. No more, no less, so 4 decimals precision is correct there. This clearly defines the boundaries as [start, end]. You have a transition that starts at time x, this means that the previous transition end (inclusive) is at the previous tick (100 microseconds before time x). So you get 1. [start_1, end_1] (where end_1 = start_1 - 1 tick (100 microseconds)) 2. [start_2, end_2] ... BTW: earlier you complained about it being fractional, and now you're complaining about the precision of those fractions not being precise enough? I don't think I understand what you're arguing for and what the problem is, unless you are arguing that the range end should be exclusive, so [start, end), which means that "end" is the same value as "start" of the next one, eg 1. [start_1, start_2) 2. [start_2, start_3) ... Which might seem nice from a theory perspective, but does not play nice with for example usage of BETWEEN. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 5-6-2018 11:43, Lester Caine wrote: On 05/06/18 09:39, Mark Rotteveel wrote: On 5-6-2018 10:16, Lester Caine wrote: On 05/06/18 08:50, Mark Rotteveel wrote: That naming doesn't make much sense to me, and I actually found the RULE_START and RULE_END naming pretty clear and self-explanatory. Except that it's not the rule itself, but the transitions within the rule ... I'd still like to know why there is a need for the 'end' anyway as the next transition already contains that. Because that is easier when doing queries... select * from ... where current_timestamp between rule_start and rule_end. But you have to use 'current_timestamp' to find the 'rule_start' and 'rule_end' and for many timezones 'rule_end' will simply be the current generic end date returned by the tz database. It requires a little bit more processing than simply pulling out two numbers for a single transition. You normally need the data from the next transition in any case. Your example would be much better as select * from ... where date between tzoffset(tz, date, current_start) and tzoffset(tz, date, next_start) And we can also have tzoffset(tz, date, current) and tzoffset(tz, date, next ) That is still no argument to leave the end of the range out of the provided information. Including it will have little to no cost, and provides full information in one record, which makes it self-contained and easy to understand. And I've still not had anybody explain why the removal of seconds from the offsets is seen as a good idea? Why is it a bad idea? Because the first transition of every rule set is from LMT to a standard time of some sort. All normalizations to UTC time prior to various times in the last 200 years involve a seconds based correction. SO if one is doing any historic work, one has to ditch many current methods ... because they only work from 1970 ... and do things a different way. At the very least, the documentation has to SAY when the built in procedures can be relied on, and when one has to ditch them. At the same time adding the fact the dates are all Gregorian would be useful. Knowing that historic changes TO Gregorian dates need special treatment would be useful ... I only learnt that Russia was still on the Julian calendar until the early 1900's this week ... Is there any reason why post-1970 time zones need second resolution for zone offsets? Or is there any other strong argument why second precision is needed? To be honest, I don't see why we should cater to an extremely uncommon minor use-case which will likely be of no interests to the majority of Firebird users, and that will be fraught with so many complications that you'll probably need your own solution anyway if you need full precision offsets pre-1970s. The TZ database does not guarantee correctness nor completeness of its information before 1970, so using second precision there will probably only lead to a false sense of precision. Be aware that I'm not asking and arguing this because I hate the idea of using seconds, but changing this has more impact than just the information reported here. Given the choice in the current implementation to encode both time zone identifiers or offset information in 2 bytes, it doesn't have any room for an offset in seconds. So supporting an offset in seconds will have profound impact on not just the information reported here, but on the actual API implementation, storage, etc. And if we don't change it there, then there is also no need to report a precision in seconds anyway, because it wouldn't match with the precision of the API. Maybe it is just me, but it seems we are now having discussions that should have been had and resolved before implementation. And I repeat again, that I think that this feature should not land in Firebird 4, and needs to be delayed to Firebird 5. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 5-6-2018 10:16, Lester Caine wrote: On 05/06/18 08:50, Mark Rotteveel wrote: That naming doesn't make much sense to me, and I actually found the RULE_START and RULE_END naming pretty clear and self-explanatory. Except that it's not the rule itself, but the transitions within the rule ... I'd still like to know why there is a need for the 'end' anyway as the next transition already contains that. Because that is easier when doing queries... select * from ... where current_timestamp between rule_start and rule_end. And I've still not had anybody explain why the removal of seconds from the offsets is seen as a good idea? Why is it a bad idea? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 4-6-2018 17:17, Adriano dos Santos Fernandes wrote: Procedure name TRANSITION_RULES is renamed to TRANSITIONS. Rules are another thing, it's how the transitions are specified in the tzdb. It may be added at another time. Output columns RULE_START and RULE_END is renamed to INITIAL_TIMESTAMP and FINAL_TIMESTAMP. That naming doesn't make much sense to me, and I actually found the RULE_START and RULE_END naming pretty clear and self-explanatory. You seem to want to avoid the use of START and END here. However to me, the meaning INITIAL and FINAL for a range is slightly off and therefor confusing. Why not just use RULE_START and RULE_END, or maybe VALID_FROM and VALID_TO. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?
On 4-6-2018 11:49, Vlad Khorsun via Firebird-devel wrote: 02.06.2018 19:54, Mark Rotteveel wrote: JDBC API requires that statements are executed in a transaction. In theory I can break that rule, but the problem with that is that it requires explicit handling, which is 1) annoying to do and 2) brittle given the other API requirements involving transactions; and given my current health issues I don't see myself implementing that anytime soon. And no, Jaybird has no specific handling for SET TRANSACTION, because the JDBC API specifies that users should use the methods defined in the API for things like transaction configuration, etc. All above is a good explanation why generic\universal API is bad :) Uhm, no. Generic APIs like JDBC allow tools and libraries to work with your database without having been specifically developed for your database, which especially for Firebird is quite helpful. BTW, looks like implicit transaction start (not supported by Firebird) could solve this issues, agree ? It could make things easier, yes, although at the same time I also see issues with its design. _"all new session management statements could run with no transaction context."_ There is a big difference between _could_ and _must_. For flexibility sake, especially in the light of drivers whose API makes it harder to execute transaction-less (Jaybird and Firebird ADO.net, but AFAIK also FDB/pyfirebirdsql, possibly others), Firebird should allow execution of these statements in a transaction. Don't you think that 3 API calls where just one is enough not looks perfect ? I agree it is not perfect, but that is the world we live in. Well, i understand your point and will not argue against it (while nor like it nor agree with it). Consider returning a warning if execution of ALTER SESSION RESET occurs within a transaction. If you must restrict it to only a single active transaction that's fine. Such warning is useless. Users will never read it. Engine not benefit from it too. JDBC and Jaybird has specific support for warnings, and I think a warning should be issued here. I could even live with a rule that this must be the first statement of a transaction (maybe even with requiring it to be the only statement of a transaction). What do you think if engine will internally rollback immediately before reset and start new transaction (with the same properties as old one) after reset ? Transaction handles will not be changed. I.e. for end user it looks like rollback retaining but not retains old context. I think this is OK, if it is coupled with a warning. As an aside, the sqlstate is 01002 is warning(!) "disconnect error" according to the SQL standard, I don't think that state is suitable here. This is exactly the same state that used for isc_open_trans error on detach. Sure, but there it **is** a "disconnect error" (although, not a warning :) the SQL standard is a bit vague if warning sqlstates can also be used for errors or not). Note that on disconnect, I could actually live with Firebird rolling back active (not-prepared) transactions ;) Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RDB$TIME_ZONE_UTIL package
On 30-5-2018 02:45, Adriano dos Santos Fernandes wrote: Hi! Here is first prototype of a system package, which worth discuss. I have put the RDB$ prefix on the package name, as liking very much or not, is the system prefix of Firebird objects. I did not named its sub routines or they parameters with RDB$ prefix. That's totally annoying and unnecessary. I agree. I added the _UTIL suffix as there is already and will not be removed the virtual table RDB$TIME_ZONES. I think RDB$TIME_ZONE name for the package would be hence confusing. Here is the usage of the two current sub routines. Function DATABASE_VERSION --- SQL> select rdb$time_zone_util.database_version() from rdb$database; DATABASE_VERSION 2017c --- Minor nitpick: the current db is 2018e. It worth discuss the name conversion. Should it be prefixed by GET_ (DATABASE_VERSION)? I think it is fine to leave that off. Procedure TRANSITION_RULES --- SQL> set list on; SQL> SQL> select * from rdb$time_zone_util.transition_rules( CON> 'America/Sao_Paulo', CON> date '2016-01-01', CON> date '2019-12-31'); RULE_START 2015-10-18 03:00:00. GMT RULE_END 2016-02-21 01:59:59. GMT ZONE_OFFSET -180 DST_OFFSET 60 I find this a bit confusing. If I'm interpreting it right, this means the actual zone offset is -120. Maybe add an extra column EFFECTIVE_OFFSET (or something like that). Presence of that could make it more self-explanatory. .. --- It list all transition rules from the start to the end date, including the pre-start and post-end in the same rule set of start and end respectively. I'm deliberately returning the timestamps in GMT time zone, but they can be easily converted to the wanted one. The input parameter names are TIMEZONE_NAME, FROM_TIMESTAMP and TO_TIMESTAMP. The name convention also worth discuss, should it be prefixed by GET_ or LIST_ ? Again, I don't think that is necessary. For GMT it will list: --- SQL> select * from rdb$time_zone_util.transition_rules( CON> 'GMT', CON> date '2016-01-01', CON> date '2019-12-31'); RULE_START 0001-01-01 00:00:00. GMT RULE_END -12-31 23:59:59. GMT ZONE_OFFSET 0 DST_OFFSET 0 --- -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?
On 2-6-2018 17:56, Vlad Khorsun via Firebird-devel wrote: 02.06.2018 18:07, Mark Rotteveel wrote: I just saw the following commit: https://github.com/FirebirdSQL/firebird/commit/bbf8348817c4592999fc137b18ba1be7326ad42d This disallows execution of ALTER SESSION RESET if there are transactions active. I think this is too restrictive. Only from client POV and only at first look. Think about at as replacement of detach\attach calls. At detach you have no active transactions or it will fail with error. For example, in Jaybird queries executed through the JDBC API will always be executed in a transaction. With this change, this statement must be executed without transaction, which means I must either include explicit support to detect this statement, or people need to break out of the JDBC API and use the underlying API, both are options I want to avoid (especially the last one). I think the same applies for other drivers. Could you add new method to reset connection ? Will it "break out of the JDBC API" ? That method would not be part of the JDBC API, so it would not be easily accessible. Technically, JDBC 4.3 introduced a feature where I could execute a session reset, but the rules for that specific API limits its usefulness and flexibility compared to being able to execute it like a normal statement. And it would be hard to use for users in earlier Java versions (or people using older Jaybird versions) to use ALTER SESSION RESET without breaking out of the JDBC API to the Firebird-extensions to that API or even to the underlying internal API. And it would make it harder to use this with external libraries like connection pool libraries (which allow init on checkout of the pool by executing a statement). BTW, does JDBC API requires that any statement shoud run within explicit transaction ? Does Jaybird allow to not start implicit transaction with statement ? Does Jaybirs support execution of "SET TRANSACTION" statement ? Also, all new session management statements could run with no transaction context. JDBC API requires that statements are executed in a transaction. In theory I can break that rule, but the problem with that is that it requires explicit handling, which is 1) annoying to do and 2) brittle given the other API requirements involving transactions; and given my current health issues I don't see myself implementing that anytime soon. And no, Jaybird has no specific handling for SET TRANSACTION, because the JDBC API specifies that users should use the methods defined in the API for things like transaction configuration, etc. _"all new session management statements could run with no transaction context."_ There is a big difference between _could_ and _must_. For flexibility sake, especially in the light of drivers whose API makes it harder to execute transaction-less (Jaybird and Firebird ADO.net, but AFAIK also FDB/pyfirebirdsql, possibly others), Firebird should allow execution of these statements in a transaction. Consider returning a warning if execution of ALTER SESSION RESET occurs within a transaction. If you must restrict it to only a single active transaction that's fine. I could even live with a rule that this must be the first statement of a transaction (maybe even with requiring it to be the only statement of a transaction). Instead I propose that execution of ALTER SESSION RESET within a transaction will not fail if the current transaction is the only active transaction of the connection. It must check too much things to not break it by reset. And list of things to check could be changed in the future. And I think that restricting ALTER SESSION RESET to only transaction-less execution will make it hard to use, and restricts its usefulness. It's currently not even possible to execute it from ISQL. In ISQL you get the error """ Statement failed, SQLSTATE = 01002 Cannot reset user session with open transactions (2 active) """ As an aside, the sqlstate is 01002 is warning(!) "disconnect error" according to the SQL standard, I don't think that state is suitable here. I think class 25 (invalid transaction state), and then maybe sqlstate 25001 (active SQL-transaction) or something like that. Alternatively this could be classified as class 08 (connection exception). Better might be to define our own non-standard subcode (eg 25501 or 08501) (sub-codes starting with 5-9 or I-Z are implementation-defined). Also, I'm not 100% sure, but it also looks like the current restriction also doesn't allow for prepared but not yet committed transactions. Prepared transactions are not active, and presence of these should not block execution of ALTER SESSION RESET. If you speak about 2PC tranaction in prepared state - it is really active and must be committed or rolled back (as any other active transaction). If it in limbo
[Firebird-devel] ALTER SESSION RESET not allowed if there are transactions?
I just saw the following commit: https://github.com/FirebirdSQL/firebird/commit/bbf8348817c4592999fc137b18ba1be7326ad42d This disallows execution of ALTER SESSION RESET if there are transactions active. I think this is too restrictive. For example, in Jaybird queries executed through the JDBC API will always be executed in a transaction. With this change, this statement must be executed without transaction, which means I must either include explicit support to detect this statement, or people need to break out of the JDBC API and use the underlying API, both are options I want to avoid (especially the last one). I think the same applies for other drivers. Instead I propose that execution of ALTER SESSION RESET within a transaction will not fail if the current transaction is the only active transaction of the connection. Also, I'm not 100% sure, but it also looks like the current restriction also doesn't allow for prepared but not yet committed transactions. Prepared transactions are not active, and presence of these should not block execution of ALTER SESSION RESET. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 2018-05-24 11:01, Mark Rotteveel wrote: On 2018-05-24 01:08, Vlad Khorsun via Firebird-devel wrote: 3. Never reset external connection when it gets out of use. It also could make system work differently - when local system was upgraded from v3 to v4 and start to use connection pooling *and* if remote statements depends on session-scoped data. But in this case user could run 'ALTER SESSION RESET' when it is required and get correct behaviour. It requires coding discipline and careful planning but possible. It have no overhead on session reset when it is not needed. But that will be awkward, because each EXECUTE STATEMENT ON EXTERNAL will obtain a (possibly) different connection from the pool, so an EXECUTE doing a session reset followed by another EXECUTE could be using different statements. This means one would have to use an EXECUTE BLOCK that does both the session reset (will that even be possible from execute block?) and then the other statement. I think this will make solutions less flexible. Ignore above, I was thinking to much about the autonomous transaction case. -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 2018-05-24 01:08, Vlad Khorsun via Firebird-devel wrote: 24.05.2018 0:39, Dimitry Sibiryakov wrote: So far we have following propositions: 1. Always reset external connection when it gets out of use. Close connection if any kind of error happens. It actually disables connections pool for pre-v4 remote servers. It could be done by disabling pool in config. I think we can be slightly more fine-grained by checking the error code(s). 2. Always reset external connection when it gets out of use. Do not close connection if syntax error happens and let it to be re-used as is. [..] BTW, we could backport ALTER SESSION RESET into v3 and even into v2.5: it should just truncate GTT's and clear user context variables as pre-v4 engines can't change other session properties. I think that is a good idea. 3. Never reset external connection when it gets out of use. It also could make system work differently - when local system was upgraded from v3 to v4 and start to use connection pooling *and* if remote statements depends on session-scoped data. But in this case user could run 'ALTER SESSION RESET' when it is required and get correct behaviour. It requires coding discipline and careful planning but possible. It have no overhead on session reset when it is not needed. But that will be awkward, because each EXECUTE STATEMENT ON EXTERNAL will obtain a (possibly) different connection from the pool, so an EXECUTE doing a session reset followed by another EXECUTE could be using different statements. This means one would have to use an EXECUTE BLOCK that does both the session reset (will that even be possible from execute block?) and then the other statement. I think this will make solutions less flexible. 4. Implement EXTERNAL DATA SOURCE database object and one of its property should be flag to [not] reset external connection on re-use. This is the best solution (as for me) but it might not fit into v4 release schedule. What did i missed ? I disagree, I think using an unconditional session reset will be no problem, because in existing use cases of external connections, there won't any expectation of preserving session state. In its current form, EXECUTE STATEMENT WITH COMMON TRANSACTION within the same transaction will use the same connection anyway (and changing that will break things), and across transactions, or multiple executes WITH AUTONOMOUS TRANSACTION, the connection will be new and fresh anyway So, there is no expectation of session preservation (outside of WITH COMMON TRANSACTION), so we also don't need to worry about keeping it working. Therefor: I stand by my original proposal: unconditional session reset. When this session reset yields a syntax error, ignore (indicates older server version), if another error: invalidate connection. It will behave the same as older versions, and we prevent annoying bugs by inconsistent behaviour caused by retained session state, or wrong expectations. For example: it "worked" locally, as only a single user was active locally, so it repeatedly used the same connection from the pool. When then moved to production (or broader test environment), a solution trying to abuse session state across EXECUTE STATEMENT ON EXTERNAL suddenly stops working because there no longer is a guarantee that you get the same connection. I think we should avoid that, and make it clear: no session state across multiple invocations of EXECUTE STATEMENT. -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 23-5-2018 11:24, Vlad Khorsun via Firebird-devel wrote: 18.05.2018 19:44, Vlad Khorsun via Firebird-devel wrote: All, I going to merge into master implementation of pool of external connections. The feature was initially developed more than a year ago, and works at production with good feedback. According to discussion in this topic i made following changes: - New system privilege MODIFY_EXT_CONN_POOL to manage pool properties (instead of initiallly used 'ALTER DATABASE' user right). - New session management statement ALTER SESSION RESET is implemented (see CORE-5832). Ticket is not marked as closed for a while as i want to make sure implementation is complete. Thus any suggestions, notes, etc are welcome. As I said in another mail, I think the RDB$GET/SET_CONTEXT USER_SESSION should also be cleared. I also noticed that you unconditionally reset the statement idleTimeout and statementTimeout to 0. Does this mean there is no DPB property to set a connection-wide config for these options? Will setting it to 0 ignore the default config, or will it apply the default config? I still have some questions to agree (or not): - Should connections pool always use ALTER SESSION RESET when connection become idle ? At first looks - yes, it should. It allows to make (almost) no difference for user code with non-pooled connections. But from practical POV i have some doubts and want to discuss it here: - pre v4 Firebird versions have no such statement and it will fail (of course connections pool should handle it correctly) - it adds at least one additional network roundtrip which could be unnecessary if user code doesn't alter session and not uses GTT ON COMMIT PRESERVE on remote side. For some applications it could add too much performance penalty for nothing I think it should unconditionally do a session reset on return to the pool if the protocol is v16 or higher (assuming v16 is the Firebird 4 protocol version). You might want to consider if it can be executed asynchronously (eg execute on check-in, read response on check-out, decide if error response invalidates connection or not?). I don't think the pool-side should conditionally reset the session, because 1) it becomes complex fast to check if session reset should apply or not and 2) will disallow independent evolution of what is reset or not, nor would it be possible do additional reset in the ON RESET trigger proposed below. - At tracker there was proposition to add new database trigger ON RESET which should fire when ALTER SESSION RESET is run. Should we implement it ? I suggest to call it ON SESSION RESET (or ON SESSION_RESET) instead of ON RESET. - If i missed something else - please tell me. Please, read and comment: https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool It was updated according to suggestions in this topic. I see a number of typos, but maybe I'll propose a pull request for those if I can find the time. -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 20-5-2018 17:24, Vlad Khorsun via Firebird-devel wrote: 20.05.2018 14:15, Mark Rotteveel wrote: I would expect such a feature to reset all session state to the initial state on connect, including things like * Clear context USER_SESSION * Reset role to the initial role specified on attach * Reset other session configuration (eg DECFLOAT behavior, timeouts, session timezone, etc) Clear GTT's also. Yes, I didn't mention those as that was the starting point of the discussion. I want to emphasize more needs to be reset than just GTTs. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 20-5-2018 14:01, Vlad Khorsun via Firebird-devel wrote: Cause i needed a some way to manage pool at runtime, without server restart, and i saw no better\easy way to do it. If you offer some - it will be considered of course. Pool management statements (even without persistence) allows DBA to play with pool properties to find an optimal values and then put in into config - this was initial idea. Ok, but I think in the longer term, we may want to consider to make DDL to control engine/server configuration to be durable (that is persistent across restarts). Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Time zone feature documentation
On 11-5-2018 18:31, Adriano dos Santos Fernandes wrote: Hi! Here is the first README version for the time zone feature. https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md Will it be possible to set the session timezone on connect through a DPB property? I like the flexibility of statements like `SET TIME ZONE`, but having to execute a number of statements immediately after connect to establish a consistent session configuration is overhead I'd prefer to have to do without. This question can be taken broader than just set time zone, as I guess it should also apply to things like the DECFLOAT bind and error configuration. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 20-5-2018 13:11, liviuslivius wrote: Hi, can i ask why this is only for external connections? 2 databases. One user run execute statement on database 1 from 2 second on database 2 from 1. Third connect simply to database 1 why it can not benefit from pool? Do you mean a user establishing a local connection to database 1 on the same host as the Firebird server itself? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 20-5-2018 13:00, Vlad Khorsun via Firebird-devel wrote: I plan to introduce new database object "EXTERNAL DATA SOURCE" since initial implementation of EXECUTE STATEMENT ON EXTERNAL, but I never have time\priority to implement it, unfortunately. The question at this topic is: should we add some way to clear session state when connection is known to be reused ? If yes - how it should be seen by a user (SQL statement, API call, both ?) I can think of use cases where clearing session state can be useful (eg connections in a application-side connection pool), and I think exposing it as SQL should be OK, as that will give more flexibility in use. I would expect this to work with execute immediate (but also using prepare/execute). I would expect such a feature to reset all session state to the initial state on connect, including things like * Clear context USER_SESSION * Reset role to the initial role specified on attach * Reset other session configuration (eg DECFLOAT behavior, timeouts, session timezone, etc) Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 20-5-2018 12:47, Vlad Khorsun via Firebird-devel wrote: 20.05.2018 12:55, Mark Rotteveel wrote: On 19-5-2018 17:11, Vlad Khorsun via Firebird-devel wrote: 19.05.2018 13:08, Mark Rotteveel wrote: 2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred: ALTER_EXT_CONN_POOL 3. In addition to option 2, maybe allow even finer-grained control, eg support granting people only the privilege to clear the pool, but not change the config: privilege CLEAR_EXT_CONN_POOL (or something like that). It looks as not necessary as pool could be cleared by setting its size to zero. You are missing my point: this privilege is about giving a user/role the right to clear the pool from its current connections, while at the same time not allowing those users to change the configuration like pool size, lifetime, etc. I don't miss it. I just say that privilege to clear the pool looks redundant as setting pool size to zero have same effect. If you insist - i can live with it. But i consider as not good to make new system privilege for every single task. There is a difference between just clearing the pool, and setting the size to zero. Clearing it will just remove the current connections (and allowing it to be repopulated), while setting the size to zero disables the pool. One has a transient effect and is therefor less disruptive, the other will be persistent and can have significant and continuous impact on the performance characteristics of the database server. That is why I proposed an additional (more restricted) privilege. On the other hand, this may be something that we can defer until the pool has seen some use and we have established a clear need for this. We may need to consider doing all three. So far i see only (2) as necessary change. Existing set of system privleges have no ALTER_XXX, so i think we should use MODIFY_EXT_CONN_POOL here. Is fine with me :) Next step could be to implement external pool as database object and allow user to CREATE\ALTER\DROP multiply pools. At the moment, I don't really see the use case of being able to define multiple pools (and how you would then use them). How do you envision this feature works? Pool will be choosed based on external database name. It could be full match or regexp pattern. In latter case we could introduce explicit ordering used to search what pool to use to avoid ambiguity. The main goal is to fine tune number and lifetime of pooled connections for differefent external databases. Ok, I understand, that could be useful. Other questions I have: 1. What happens if the pool configuration is done through DDL? Its runtime values are applied immediately. It is described in README.external_connections_pool. Will it persist in the firebird.conf (or engine13.conf)? No. Will a restart of Firebird clear it again? Yes. Hmm, this could make for odd performance changes between restarts. This will need to be clearly documented. I would expect the config changes done this way to be permanent. Playing devil's advocate, if this configuration change isn't persistent, why expose it as DDL at all? Especially given the difference exposed below. 2. How will the pool work in case of Classic Server? Will pool config changes apply to all processes? Current process only. Since we have pool per process, not per whole system\instance. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 19-5-2018 17:11, Vlad Khorsun via Firebird-devel wrote: 19.05.2018 13:08, Mark Rotteveel wrote: 2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred: ALTER_EXT_CONN_POOL 3. In addition to option 2, maybe allow even finer-grained control, eg support granting people only the privilege to clear the pool, but not change the config: privilege CLEAR_EXT_CONN_POOL (or something like that). It looks as not necessary as pool could be cleared by setting its size to zero. You are missing my point: this privilege is about giving a user/role the right to clear the pool from its current connections, while at the same time not allowing those users to change the configuration like pool size, lifetime, etc. We may need to consider doing all three. So far i see only (2) as necessary change. Also, i want to speak about possible extension of the feature. I think it would be good to have new monitoring table with list of all external connections. Not sure if we should allow to DELETE here but it should be at least considered too. It will replace two of four new context variables (EXT_CONN_POOL_IDLE_COUNT and EXT_CONN_POOL_ACTIVE_COUNT). Having a monitoring table for these would be good. Next step could be to implement external pool as database object and allow user to CREATE\ALTER\DROP multiply pools. At the moment, I don't really see the use case of being able to define multiple pools (and how you would then use them). How do you envision this feature works? Maybe this is something that needs to be deferred until this feature has seen some use? Another little improvement could be new property of pooled connection - recycle time. It means that even if connection is actively used it should be closed after "recycle time" (of course when it become inactive). What do you think ? Limiting the lifetime of a connection in the pool. Yes, that is a good idea. In the Java world, this property is usually called something like maximum connection lifetime. Another thought, I think this may be something that needs to be logged in firebird.log (eg changed external connection pool lifetime from to ; cleared old|all connections, etc) Another setting to enable\disable such logging ? Please, no ;) It would be much better to discuss (separately, yes) how logging could be structured and moved to the public interface\new plugin, IMHO. I said nothing about having settings for enabling or disabling said logging, I think changes to the configuration need to be logged, so they can be audited. Other questions I have: 1. What happens if the pool configuration is done through DDL? Will it persist in the firebird.conf (or engine13.conf)? Will a restart of Firebird clear it again? 2. How will the pool work in case of Classic Server? Will pool config changes apply to all processes? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 19-5-2018 10:43, Vlad Khorsun via Firebird-devel wrote: 19.05.2018 11:04, Mark Rotteveel wrote: On 18-5-2018 18:44, Vlad Khorsun via Firebird-devel wrote: ... Please, read and comment: https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool 1. I'm not sure how I should read the "Key characteristic" that says: "the pool is common for all local connections handled by the given Firebird process" It means that all local (user) connections requests for external connection are handled by the same single pool instance. 2. (maybe related to 1) What is not clear to me is whether this pool is global (for the whole engine), or per database. The whole engine. If it is global, then the permission for ALTER EXTERNAL CONNECTIONS POOL needs to be changed: """ New SQL statement is introduced to manage the pool : ALTER EXTERNAL CONNECTIONS POOL. When prepared it desribed as DDL statement but have immediate effect: i.e. it is executed immediately and completely, not waiting for transaction commit. "ALTER DATABASE" permission is required to run the statement. """ The ALTER DATABASE privilege is per database. I don't think that a per-database privilege should be allowed to control engine-wide configuration options. If the pool (and config) is global, I think this requires a global privilege, preferably a separate one. If the pool **and** config is local, then it is ok. Good point, thanks. Since the pool is global it should be guarded by global privilege. We could use existing MODIFY_ANY_OBJECT_IN_DATABASE or introduce a new one. For example MODIFY_EXTERNAL_CONNECTIONS_POOL, or ALTER_EXTERNAL_CONNECTIONS_POOL, or something not so long. Do you have better idea ? We can consider a number of approaches: 1. Coarse-grained privilege that also allows changing other engine config options exposed in DDL: ALTER_ENGINE_CONFIG. I think this is currently the only statement that allows for changing the engine config through DDL, but this would future-proof it. On the other hand, this could become such a wide privilege that maybe that user should simply have the RDB$ADMIN role instead. 2. Fine-grained privilege that applies only to this single option: ALTER_EXTERNAL_CONNECTIONS_POOL or if shorter is preferred: ALTER_EXT_CONN_POOL 3. In addition to option 2, maybe allow even finer-grained control, eg support granting people only the privilege to clear the pool, but not change the config: privilege CLEAR_EXT_CONN_POOL (or something like that). We may need to consider doing all three. Another thought, I think this may be something that needs to be logged in firebird.log (eg changed external connection pool lifetime from to ; cleared old|all connections, etc) Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] RFC: External Connections Pool
On 18-5-2018 18:44, Vlad Khorsun via Firebird-devel wrote: All, I going to merge into master implementation of pool of external connections. The feature was initially developed more than a year ago, and works at production with good feedback. Some bugs was fixed since then, so it should be stable enough and definitely good for beta release of v4. The branch ExternalConnectionsPool was created at our repository 3 months ago, btw ;) Please, read and comment: https://github.com/FirebirdSQL/firebird/blob/ExternalConnectionsPool/doc/sql.extensions/README.external_connections_pool 1. I'm not sure how I should read the "Key characteristic" that says: "the pool is common for all local connections handled by the given Firebird process" 2. (maybe related to 1) What is not clear to me is whether this pool is global (for the whole engine), or per database. If it is global, then the permission for ALTER EXTERNAL CONNECTIONS POOL needs to be changed: """ New SQL statement is introduced to manage the pool : ALTER EXTERNAL CONNECTIONS POOL. When prepared it desribed as DDL statement but have immediate effect: i.e. it is executed immediately and completely, not waiting for transaction commit. "ALTER DATABASE" permission is required to run the statement. """ The ALTER DATABASE privilege is per database. I don't think that a per-database privilege should be allowed to control engine-wide configuration options. If the pool (and config) is global, I think this requires a global privilege, preferably a separate one. If the pool **and** config is local, then it is ok. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FBJava plugin issues
On 18-5-2018 14:53, Adriano dos Santos Fernandes wrote: On 18/05/2018 09:45, Mark Rotteveel wrote: On 18-5-2018 14:32, Adriano dos Santos Fernandes wrote: On 18/05/2018 08:57, Mark Rotteveel wrote: We also tried to push our own JARs into the DB, but ended up getting tons of "PK violation" errors. This one is because Java has no problem if you have class x.y.Class in many jars, but the plugin AFAIR rejects it, as Oracle does. Well, "no problems" is not entirely correct, it sort of works, you just get potentially different behavior or errors depending on the order of class loading, I mean, playing with classloader is a valid situation to have the same class in multiple places. The jar-in-database is a analogy to a war file. A collection of jars for a database (web app). WARs accept them and have the problem you said. Yes, and every now and then problems with changes in class loading order and/or new dependencies including same classes rears its ugly head. On the other hand, the current unique constraint, probably - I haven't checked - also prevents duplicates in resources like SPI definitions (eg /META-INF/services/ files like /META-INF/services/java.sql.Driver), and that could be problematic. so disallowing duplicates classes is a good thing. Not sure, really. Java 9 and higher disallows sharing the same package across jars when using modules (instead of the old-style class path), that is even a more stricter form than disallowing duplicate classes. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FBJava plugin issues
On 18-5-2018 14:32, Adriano dos Santos Fernandes wrote: On 18/05/2018 08:57, Mark Rotteveel wrote: We also tried to push our own JARs into the DB, but ended up getting tons of "PK violation" errors. This one is because Java has no problem if you have class x.y.Class in many jars, but the plugin AFAIR rejects it, as Oracle does. Well, "no problems" is not entirely correct, it sort of works, you just get potentially different behavior or errors depending on the order of class loading, so disallowing duplicates classes is a good thing. It's probably a restriction that should be removed. -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FBJava plugin issues
On 16-5-2018 16:47, Chmakov, Vladimir wrote: Hello Mark, Thank you for your reply! Yes, the documentation recommends placing JARs inside the DB, but the "external" deployment better suits our software deployment policies. My expectation would be that regardless of the JAR deployment model all JAR components exposed onto the class path would be visible for the plugin. It doesn't seem to be the case, as in the provided example it is obvious, that logback.xml is being ignored or not visible when JAR deployed "externally". Could you please describe the exact layout of your deployment, which files are where? eg: is that logback.xml in a jar file in the /jar/ directory, or did you deploy it separately (which is not going to work)? We also tried to push our own JARs into the DB, but ended up getting tons of "PK violation" errors. Please describe in more detail what you did and what error you get. The fbjava plugin probably hasn't seen a lot of use, so unless you describe exactly what you did, it will be harder to replicate what you did and either point out what you did wrong, or what is wrong with the current plugin so it can be fixed. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Reason for "Missing master config file firebird.conf"
On 18-5-2018 10:00, Jiří Činčura wrote: Hi *, what could be the reason for "Missing master config file firebird.conf" mesage when doing ".\firebird.exe -a"? I'm traying to start Firebird on AAppVeyor for tests for NETProvider. When I execute it manually it starts fine. Only when from script from AppVeyor then I get this messsage. Any hints to check? It's a vanilla Firebird 3, the CWD is the location where firebird.exe is located. Maybe an issue with access rights? Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] CORE-5343 and CORE-2557
On 16-5-2018 17:55, ALİ ÇEKER wrote: I need to create and install the user but I can not do the error I added in the picture Please don't hijack threads on other subjects. If you want to post a question, start a new thread (post a new message instead of replying to an existing one). However your question seems to be off-topic. This mailing list is for discussions about development of Firebird, not for support questions. If you have a question about the installation or use of a third-party tool that utilizes Firebird, then you should ask your question from the vendor of that third-party application. If you have a question about using Firebird, then subscribe to the firebird-support mailing list and ask your question there. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] FBJava plugin issues
On 14-5-2018 17:53, Chmakov, Vladimir wrote: Hello, We are placing them into %FBJAVA_ROOT%\jar folder. The fbjava documentation specifically says not to put more jar files there: """ The internal classes necessary for FB/Java are in /jar/*.jar and is not recommended to put more jar files there. """ It also says: """ It is general recommendation that users store they classes in the database """ The documentation does seem to hint that it might be possible (and the code seems to confirm that), but given it is not recommended, I wouldn't do it. Looking at the code involved, deploying this way will probably require a restart of Firebird for each change (but my knowledge of C++ and Firebird plugins is limited). I'm not sure if that is desirable. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Virtual table for time zones
On 14-5-2018 09:13, Simonov Denis via Firebird-devel wrote: Adriano dos Santos Fernandes <adrian...@gmail.com> wrote Thu, 10 May 2018 18:21:49 +0300: Hi! I want to create a virtual table that lists available time zones. For now there is RDB$ (non-virtual), MON$ (virtual, but all about monitoring), SEC$ (security plugin). What prefix should TIME_ZONES have? Adriano I'm wondering how the time zones will be updated? Will they be distributed as a separate file in Firebird? Can I register a new rule for time zones without updating Firebird and the backup/restore process? If so, is there a DLL syntax for registering a new time zone rule? If you look at PostgreSQL, they release a new point release with updated time zone data. I don't see why we need to make this more complicated than that. However that does mean, that we need to have a quick turn-around time for new releases (especially given the annoying habit of some countries of making changes like this on short notice). If we can't do that, we will need to provide some way to make update the time zone data easily(!). No hacks or requiring users to recompile ICU or anything like that. Mark -- Mark Rotteveel -- Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
