[firebird-support] Re: distinct on list() function
This is not a bug, but a documented feature. list() returns BLOB. And blobs are distict because BLOB_ID-s are compared. SELECT DISTINCT, ORDER BY and GROUP BY work on the BLOB ID, not the contents. https://firebirdsql.org/refdocs/langrefupd21-blob.html https://firebirdsql.org/refdocs/langrefupd21-blob.html You ust use CAST in this case. with cte as (select 1 cnt from rdb$database union all select 2 from rdb$database ) select cast(list(cnt) as varchar(10)) from cte union select cast(list(cnt) as varchar(10)) from cte
[firebird-support] going blob (0:25677) is not owned by relation (id = 548), ignored
Hi! One of our customer's firebird.log contains a bunch of this message. What is this means? FABIANLIBRASRVThu Oct 17 12:42:18 2019 going blob (0:25677) is not owned by relation (id = 548), ignored FABIANLIBRASRVThu Oct 17 12:42:18 2019 going blob (0:25678) is not owned by relation (id = 548), ignored FABIANLIBRASRVThu Oct 17 12:42:18 2019 going blob (0:25679) is not owned by relation (id = 548), ignored
[firebird-support] Re: Why this SQL is correct?
OK, I understand now. Thank you.
[firebird-support] Re: Why this SQL is correct?
So HAVING condition is evaluated for EACH ROW instead of just on the group? I think then the current approach is wrong in the engine. http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt "2) The is applied to each group of T."
[firebird-support] Why this SQL is correct?
Hi! SELECT e.emp_no FROM employee e JOIN employee_project p ON p.emp_no = e.emp_no GROUP BY e.emp_no HAVING MAX((SELECT SUM(e2.salary) FROM employee e2 JOIN employee_project p2 ON p2.emp_no = e2.emp_no WHERE e2.emp_no = e.emp_no AND p2.proj_id IS NOT DISTINCT FROM p.proj_id)) <> 0 This is accepted by Firebird (also by Oracle). In the subselect p.proj_id should not be accepted - group does not contains it, and has no aggregate on it. What value the engine choose when it runs, and why? Is this equivalent with it? SELECT e.emp_no FROM employee e JOIN employee_project p ON p.emp_no = e.emp_no GROUP BY e.emp_no HAVING (SELECT SUM(e2.salary) FROM employee e2 JOIN employee_project p2 ON p2.emp_no = e2.emp_no WHERE e2.emp_no = e.emp_no AND p2.proj_id IS NOT DISTINCT FROM MAX(p.proj_id)) <> 0
RE: [firebird-support] Scaling Firebird - Azure
Software raid and hardware raid without BBU has a huge performace hit : https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/ https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/ EXT3, EXT4 : file system barrier property has huge performance hit https://www.firebirdnews.org/understanding-barrier-on-linux/ https://www.firebirdnews.org/understanding-barrier-on-linux/ https://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ https://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ Hyperthreading could harm performance : https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/ https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/
[firebird-support] Firebird 2.5.9 mon$attachments.mon$remote_address
Hi! Before 2.5.9 mon$attachments.mon$remote_address returned just IP, e.g.: "192.168.0.1". With 2.5.9 it returns IP + port number, e.g.: "192.168.0.1/55970". It made broke some of our code. (We are comparing mon$remote_address to outer source IP without port) It this intentional, a side effect or a bug? Changelog doesn't mentions this change. Thank you!
[firebird-support] Database corrptions
Hi! We use 2.5.8 CS on Oracle Linux 7.6. It has 200+ database and 4000+ connections. Yesterday we had corruptions in many databases around the same moment. These are the different messages in the firebird.log after the corruptions and trying to valdate/fix/backup/restore/read the databasese. What could be the cause/problem? "Operating system call pthread_mutex_lock failed. Error code 22" "Operating system call pthread_mutex_destroy failed. Error code 16" "Error while trying to read from file Invalid argument" "Error while trying to read from file Success" "Error while trying to read from file Connection reset by peer" "Error while trying to access file Success" "Error while trying to access file Bad file descriptor" "Error while trying to access file Resource temporarily unavailable" "Error while trying to read from file No such file or directory" "Error while trying to read from file No such process" "I/O error during "fstat" operation for file..." "I/O error during "read" operation for file..." "wrong page type page is of wrong type (expected 7, found 5)" "Cannot dump the monitoring data" "internal Firebird consistency check (cannot find record back version (291), file: vio.cpp line: 4022)" "internal Firebird consistency check (bad difference record (176), file: sqz.cpp line: 68)" "internal Firebird consistency check (cannot find record fragment (248), file: dpm.cpp line: 1246)" "internal Firebird consistency check (record disappeared (186), file: vio.cpp line: 3249)" "internal Firebird consistency check (next transaction older than oldest active transaction (266), file: cch.cpp line: 6523)" "internal Firebird consistency check (decompression overran buffer (179), file: sqz.cpp line: 228)" "internal Firebird consistency check (next transaction older than oldest active transaction (266), file: cch.cpp line: 6523)" "internal Firebird consistency check (record disappeared (186), file: vio.cpp line: 3249)" "internal Firebird consistency check (wrong record version (185), file: vio.cpp line: 3258)" "internal Firebird consistency check (cannot find record back version (291), file: dpm.cpp line: 1189)" "internal Firebird consistency check (wrong record length (183), file: vio.cpp line: 1248)" "internal Firebird consistency check (can't continue after bugcheck)" "Firebird shutdown is still in progress after the specified timeout" "Chain for record is broken in table ()" "Record is wrong length in table ()" "Record is marked as damaged in table ()" "Relation has 2 orphan backversions (0 in use) in table ()" "Page is an orphan"
[firebird-support] Re: NVME drive optimize for firebird engine
Hi! Do not use software RAID, it has no disc cache and IO becomes terribly slow. You have to use harware RAID with BBU ho have disc cache and good performance.
[firebird-support] Re: Firebird stopped after 190 connections
Hi!
You have to make some changes in xinetd.
service gds_db
{
disable = no
flags = reuse
socket_type = stream
wait = no
tcps = 50 10
user = firebird
per_source = UNLIMITED
instances = UNLIMITED
server = /opt/firebird/bin/fb_inet_server
}
[firebird-support] Re: Unique constraint instead unique index
Hi! You cannt have expression in unique key, but you can have expression index with unique property. ;)
[firebird-support] Re: Best way to delete millions of rows
This is not a bug, this is expected behaviour : sweep/garbage collection occures.
[firebird-support] Re: Best way to delete millions of rows
Hi! Firebird does not like ext4. http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ http://www.firebirdnews.org/understanding-barrier-on-linux/ http://www.firebirdnews.org/understanding-barrier-on-linux/
[firebird-support] Re: problem with amount user that connect to firebird classic-server
Hi!
TCP connections are limited in xinetd.
Edit /etc/xinet.d/firebird file
service gds_db
{
disable = no
flags = reuse
socket_type = stream
wait = no
tcps = 50 10
user = firebird
per_source = UNLIMITED
instances = UNLIMITED
server = /opt/firebird/bin/fb_inet_server
}
[firebird-support] Re: performance issue with firebird 3.0 embedded on linux
Hi! #1 : FB does not like ext4 : Firebird News » Forced Writes Performance impact on #Ubuntu with ext4 no barrier http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ Firebird News » Forced Writes Performance impact on #Ub... http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ Tweet of the day comes from Carlos H. Cantu about forced writes effects for running firebird scripts Script executing time – Linux Ubuntu 10.04.3 Ext4 – ... View on www.firebirdnews.org http://www.firebirdnews.org/forced-writes-performance-impact-on-ubuntu/ Preview by Yahoo #2 : HT is no necessary a good idea in case of data intensive applications (eg. databases) : Be aware: To Hyper or not to Hyper https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/ https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/ Be aware: To Hyper or not to Hyper https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/ Our customers observed very interesting behavior on high end Hyperthreading (HT) enabled hardware. They noticed that in some cases when high load is applied... View on blogs.msdn.microsof... https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/ Preview by Yahoo #3 : Use classic server, or superclassic (superserver does not like concurrency is a single database). #4 : I think the bottleneck here is the disk, not the engine itself. We made a test for bulk insert and best method was to run execute blocks with as many inserts as possible in it in a single thread. We achieved 8-9K records / sec for small tables and 3-4K records / sec for bigger tables. (tables were not indexed, and it was a simple HDD)
