Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

query fails with message 'operating system directive write failed. Invalid argument. [CORE2321] #2745

Closed
firebird-automations opened this issue Feb 12, 2009 · 21 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: David Keith (dkeith2_yahoo.com)

Is related to CORE2422
Is related to CORE2514

Votes: 1

Running a select query when I received the message:
operating system directive write failed. Invalid argument.

Executed query from within IBExpert 2009.01.11, running on Windows XP/Dell Precision M6300 laptop, 3.5 GB ram. Intel Core 2 Duo 2.5 GHz processor.

Can anyone provide direction as to what is causing this error? Thanks.

David Keith

Query:
SELECT DISTINCT C.SAB,S.ATV,C.TTY,S.ATN
FROM RXNSAT S, RXNCONSO C
WHERE S.RXCUI = C.RXCUI
and s.sab = c.sab
--AND C.RXCUI = :RXCUI
AND (
((C.SAB = 'RXNORM') AND (C.TTY IN ('SCD','SBD'))) OR
((C.SAB = 'VANDF') AND (S.ATN IN ('DDF','VA_CLASS_NAME','VA_GENERIC_NAME','VA_DISPENSE_UNIT','DCSA','NF_NAME'))) OR
((C.SAB = 'MTHFDA') AND (S.ATN IN ('TRN','DDF','DRT','DCSA','RXO')))
)
AND C.TTY <> 'IN'
rows 1

- - - - - Database/ Extra Details - - - - -
Database:
ls -la /data/Drugs/DrugData/DRUGDATA.FDB
-rw-rw---- 1 firebird firebird 3033006080 2009-02-12 12:52 /data/Drugs/DrugData/DRUGDATA.FDB

Tables in query:
ROWCOUNT TABLE_NAME
793272 RXNCONSO
4227730 RXNSAT

CREATE TABLE RXNSAT (
RXCUI VARCHAR(8) NOT NULL,
LUI VARCHAR(8),
SUI VARCHAR(8),
RXAUI VARCHAR(8),
STYPE VARCHAR(3),
CODE VARCHAR(50),
ATUI VARCHAR(10),
SATUI VARCHAR(50),
ATN VARCHAR(50) NOT NULL,
SAB VARCHAR(20) NOT NULL,
ATV VARCHAR(255) NOT NULL,
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX IDX_RXNSAT_ATN_SAB ON RXNSAT (SAB, ATN);
CREATE INDEX IDX_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX IDX_RXNSAT_ATV_FUNC ON RXNSAT COMPUTED BY (SUBSTRING(REPLACE(REPLACE(ATV,'*',''),'-','') FROM 1 FOR 9));
CREATE INDEX IDX_RXNSAT_CODE ON RXNSAT (CODE);
CREATE INDEX IDX_RXNSAT_RXCUI ON RXNSAT (RXCUI);
CREATE INDEX IDX_RXNSAT_SAB ON RXNSAT (SAB);
CREATE INDEX X_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX X_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX X_RXNSAT_RXCUI ON RXNSAT (RXCUI);

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE RXNCONSO (
RXCUI VARCHAR(8) NOT NULL,
LAT VARCHAR(3) DEFAULT 'ENG' NOT NULL,
TS VARCHAR(1),
LUI VARCHAR(8),
STT VARCHAR(3),
SUI VARCHAR(8),
ISPREF VARCHAR(1),
RXAUI VARCHAR(8) NOT NULL,
SAUI VARCHAR(50),
SCUI VARCHAR(50),
SDUI VARCHAR(50),
SAB VARCHAR(20) NOT NULL,
TTY VARCHAR(20) NOT NULL,
CODE VARCHAR(50) NOT NULL,
STR VARCHAR(2000) NOT NULL,
SRL VARCHAR(10),
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNCONSO_RXCUI_TTY_SAB ON RXNCONSO (RXCUI, TTY, SAB);
CREATE INDEX IDX_RXNCONSO_SAB ON RXNCONSO (SAB);
CREATE INDEX X_RXNCONSO_CODE ON RXNCONSO (CODE);
CREATE INDEX X_RXNCONSO_RXCUI ON RXNCONSO (RXCUI);
CREATE INDEX X_RXNCONSO_STR ON RXNCONSO (STR);
CREATE INDEX X_RXNCONSO_TTY ON RXNCONSO (TTY);

- - - - - - - - - - - - - firebird.conf - - - - - - -
#⁠
#⁠ Firebird configuration file
#⁠
#⁠RootDirectory =

#⁠DatabaseAccess = Full

#⁠ExternalFileAccess = None
ExternalFileAccess = Full

#⁠UdfAccess = Restrict UDF
UdfAccess = Full

TempDirectories = /mnt/ramdisk;/tmp

#⁠LegacyHash = 1

#⁠Authentication = mixed

#⁠DefaultDbCachePages = 2048
DefaultDbCachePages = 512

#⁠DatabaseGrowthIncrement = 134217728

#⁠MaxFileSystemCache = 65536

TempBlockSize = 2097152

#⁠TempCacheLimit = 67108864
#⁠TempCacheLimit = 134217728
#⁠OldParameterOrdering = 0
#⁠CompleteBooleanEvaluation = 0
#⁠DeadlockTimeout = 10
#⁠MaxUnflushedWrites = 100
#⁠MaxUnflushedWriteTime = 5
#⁠BugcheckAbort = 0
#⁠OldColumnNaming = 0
#⁠RelaxedAliasChecking = 0
#⁠ConnectionTimeout = 180
#⁠DummyPacketInterval = 0
#⁠RemoteServiceName = gds_db
#⁠RemoteServicePort = 3050
#⁠RemoteAuxPort = 0
#⁠TcpRemoteBufferSize = 8192
#⁠TcpRemoteBufferSize = 16384
#⁠TcpNoNagle = 1
#⁠RemoteBindAddress =
#⁠LockMemSize = 1048576
#⁠LockSemCount = 32
#⁠LockGrantOrder = 1
#⁠LockAcquireSpins = 0
#⁠LockHashSlots = 1009
#⁠EventMemSize = 65536
#⁠CpuAffinityMask = 1
#⁠UsePriorityScheduler = 1
#⁠PrioritySwitchDelay = 100
#⁠PriorityBoost = 5
#⁠GCPolicy = combined
#⁠GuardianOption = 1
#⁠ProcessPriorityLevel = 0
#⁠IpcName = FIREBIRD
#⁠RemotePipeName = interbas
#⁠CreateInternalWindow = 1
#⁠LockSignal = 16
#⁠RemoteFileOpenAbility = 0
#⁠Redirection = 0

- - - - - - - - - - Machine Details - - - - - - -

Dell dual core Xeon:
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.30
clflush size : 64
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 1
initial apicid : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.23
clflush size : 64
power management:

cat /proc/meminfo
MemTotal: 3635128 kB
MemFree: 938632 kB
Buffers: 125044 kB
Cached: 1951672 kB
SwapCached: 0 kB
Active: 1719064 kB
Inactive: 850080 kB
HighTotal: 2752256 kB
HighFree: 371096 kB
LowTotal: 882872 kB
LowFree: 567536 kB
SwapTotal: 4088500 kB
SwapFree: 4088440 kB
Dirty: 456 kB
Writeback: 0 kB
AnonPages: 492312 kB
Mapped: 68284 kB
Slab: 96688 kB
SReclaimable: 88252 kB
SUnreclaim: 8436 kB
PageTables: 3340 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 5906064 kB
Committed_AS: 1549940 kB
VmallocTotal: 114680 kB
VmallocUsed: 21496 kB
VmallocChunk: 92532 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
DirectMap4k: 4096 kB
DirectMap4M: 913408 kB

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 22G 2.1G 20G 10% /
/dev/sda6 200G 4.5G 196G 3% /data
/dev/sda7 20G 38M 20G 1% /home
/dev/ram0 1.8G 0 1.8G 0% /mnt/ramdisk

Commits: 4c459eb

====== Test Details ======

Test not needed here: "temporary folder just gets out of free space when performing the external sorting (a part of the index creation process)."

@firebird-automations
Copy link
Collaborator Author

Commented by: David Keith (dkeith2_yahoo.com)

The OS is Mandriva 2009. Here are some entries from the firebird.log file:

LXDB-MANDRIVA2009 (Client) Wed Feb 11 16:51:15 2009
INET/inet_error: send errno = 32

LXDB-MANDRIVA2009 (Client) Wed Feb 11 16:51:24 2009
INET/inet_error: send errno = 32

LXDB-MANDRIVA2009 (Client) Wed Feb 11 16:51:28 2009
INET/inet_error: send errno = 32

LXDB-MANDRIVA2009 (Client) Wed Feb 11 16:51:28 2009
REMOTE INTERFACE/gds__detach: Unsuccesful detach from database.
Uncommitted work may have been lost

LXDB-MANDRIVA2009 (Client) Wed Feb 11 16:51:28 2009
INET/inet_error: send errno = 32

LXDB-MANDRIVA2009 Wed Feb 11 17:22:13 2009
INET/inet_error: read errno = 104

LXDB-MANDRIVA2009 Wed Feb 11 19:59:24 2009
Database: /data/Drugs/DrugData/DRUGDATA.FDB
database file appears corrupt (/data/Drugs/DrugData/DRUGDATA.FDB)
wrong page type
page 184183 is of wrong type (expected 6, found 5)
internal gds software consistency check (error during savepoint backout (290), file: exe.cpp line: 4034)

LXDB-MANDRIVA2009 Wed Feb 11 19:59:24 2009
INET/inet_error: send errno = 104

LXDB-MANDRIVA2009 Wed Feb 11 19:59:24 2009
SERVER/process_packet: broken port, server exiting

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

(I moved the database schema & extra details, from the environment details into the problem description)

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: Running a select query when I received the message:
operating system directive write failed. Invalid argument.

Executed query from within IBExpert 2009.01.11, running on Windows XP/Dell Precision M6300 laptop, 3.5 GB ram. Intel Core 2 Duo 2.5 GHz processor.

Can anyone provide direction as to what is causing this error? Thanks.

David Keith

Query:
SELECT DISTINCT C.SAB,S.ATV,C.TTY,S.ATN
FROM RXNSAT S, RXNCONSO C
WHERE S.RXCUI = C.RXCUI
and s.sab = c.sab
--AND C.RXCUI = :RXCUI
AND (
((C.SAB = 'RXNORM') AND (C.TTY IN ('SCD','SBD'))) OR
((C.SAB = 'VANDF') AND (S.ATN IN ('DDF','VA_CLASS_NAME','VA_GENERIC_NAME','VA_DISPENSE_UNIT','DCSA','NF_NAME'))) OR
((C.SAB = 'MTHFDA') AND (S.ATN IN ('TRN','DDF','DRT','DCSA','RXO')))
)
AND C.TTY <> 'IN'
rows 1

=>

Running a select query when I received the message:
operating system directive write failed. Invalid argument.

Executed query from within IBExpert 2009.01.11, running on Windows XP/Dell Precision M6300 laptop, 3.5 GB ram. Intel Core 2 Duo 2.5 GHz processor.

Can anyone provide direction as to what is causing this error? Thanks.

David Keith

Query:
SELECT DISTINCT C.SAB,S.ATV,C.TTY,S.ATN
FROM RXNSAT S, RXNCONSO C
WHERE S.RXCUI = C.RXCUI
and s.sab = c.sab
--AND C.RXCUI = :RXCUI
AND (
((C.SAB = 'RXNORM') AND (C.TTY IN ('SCD','SBD'))) OR
((C.SAB = 'VANDF') AND (S.ATN IN ('DDF','VA_CLASS_NAME','VA_GENERIC_NAME','VA_DISPENSE_UNIT','DCSA','NF_NAME'))) OR
((C.SAB = 'MTHFDA') AND (S.ATN IN ('TRN','DDF','DRT','DCSA','RXO')))
)
AND C.TTY <> 'IN'
rows 1

- - - - - Database/ Extra Details - - - - -
Database:
ls -la /data/Drugs/DrugData/DRUGDATA.FDB
-rw-rw---- 1 firebird firebird 3033006080 2009-02-12 12:52 /data/Drugs/DrugData/DRUGDATA.FDB

Tables in query:
ROWCOUNT TABLE_NAME
793272 RXNCONSO
4227730 RXNSAT

CREATE TABLE RXNSAT (
RXCUI VARCHAR(8) NOT NULL,
LUI VARCHAR(8),
SUI VARCHAR(8),
RXAUI VARCHAR(8),
STYPE VARCHAR(3),
CODE VARCHAR(50),
ATUI VARCHAR(10),
SATUI VARCHAR(50),
ATN VARCHAR(50) NOT NULL,
SAB VARCHAR(20) NOT NULL,
ATV VARCHAR(255) NOT NULL,
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX IDX_RXNSAT_ATN_SAB ON RXNSAT (SAB, ATN);
CREATE INDEX IDX_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX IDX_RXNSAT_ATV_FUNC ON RXNSAT COMPUTED BY (SUBSTRING(REPLACE(REPLACE(ATV,'*',''),'-','') FROM 1 FOR 9));
CREATE INDEX IDX_RXNSAT_CODE ON RXNSAT (CODE);
CREATE INDEX IDX_RXNSAT_RXCUI ON RXNSAT (RXCUI);
CREATE INDEX IDX_RXNSAT_SAB ON RXNSAT (SAB);
CREATE INDEX X_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX X_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX X_RXNSAT_RXCUI ON RXNSAT (RXCUI);

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE RXNCONSO (
RXCUI VARCHAR(8) NOT NULL,
LAT VARCHAR(3) DEFAULT 'ENG' NOT NULL,
TS VARCHAR(1),
LUI VARCHAR(8),
STT VARCHAR(3),
SUI VARCHAR(8),
ISPREF VARCHAR(1),
RXAUI VARCHAR(8) NOT NULL,
SAUI VARCHAR(50),
SCUI VARCHAR(50),
SDUI VARCHAR(50),
SAB VARCHAR(20) NOT NULL,
TTY VARCHAR(20) NOT NULL,
CODE VARCHAR(50) NOT NULL,
STR VARCHAR(2000) NOT NULL,
SRL VARCHAR(10),
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNCONSO_RXCUI_TTY_SAB ON RXNCONSO (RXCUI, TTY, SAB);
CREATE INDEX IDX_RXNCONSO_SAB ON RXNCONSO (SAB);
CREATE INDEX X_RXNCONSO_CODE ON RXNCONSO (CODE);
CREATE INDEX X_RXNCONSO_RXCUI ON RXNCONSO (RXCUI);
CREATE INDEX X_RXNCONSO_STR ON RXNCONSO (STR);
CREATE INDEX X_RXNCONSO_TTY ON RXNCONSO (TTY);

- - - - - - - - - - - - - firebird.conf - - - - - - -
#⁠
#⁠ Firebird configuration file
#⁠
#⁠RootDirectory =

#⁠DatabaseAccess = Full

#⁠ExternalFileAccess = None
ExternalFileAccess = Full

#⁠UdfAccess = Restrict UDF
UdfAccess = Full

TempDirectories = /mnt/ramdisk;/tmp

#⁠LegacyHash = 1

#⁠Authentication = mixed

#⁠DefaultDbCachePages = 2048
DefaultDbCachePages = 512

#⁠DatabaseGrowthIncrement = 134217728

#⁠MaxFileSystemCache = 65536

TempBlockSize = 2097152

#⁠TempCacheLimit = 67108864
#⁠TempCacheLimit = 134217728
#⁠OldParameterOrdering = 0
#⁠CompleteBooleanEvaluation = 0
#⁠DeadlockTimeout = 10
#⁠MaxUnflushedWrites = 100
#⁠MaxUnflushedWriteTime = 5
#⁠BugcheckAbort = 0
#⁠OldColumnNaming = 0
#⁠RelaxedAliasChecking = 0
#⁠ConnectionTimeout = 180
#⁠DummyPacketInterval = 0
#⁠RemoteServiceName = gds_db
#⁠RemoteServicePort = 3050
#⁠RemoteAuxPort = 0
#⁠TcpRemoteBufferSize = 8192
#⁠TcpRemoteBufferSize = 16384
#⁠TcpNoNagle = 1
#⁠RemoteBindAddress =
#⁠LockMemSize = 1048576
#⁠LockSemCount = 32
#⁠LockGrantOrder = 1
#⁠LockAcquireSpins = 0
#⁠LockHashSlots = 1009
#⁠EventMemSize = 65536
#⁠CpuAffinityMask = 1
#⁠UsePriorityScheduler = 1
#⁠PrioritySwitchDelay = 100
#⁠PriorityBoost = 5
#⁠GCPolicy = combined
#⁠GuardianOption = 1
#⁠ProcessPriorityLevel = 0
#⁠IpcName = FIREBIRD
#⁠RemotePipeName = interbas
#⁠CreateInternalWindow = 1
#⁠LockSignal = 16
#⁠RemoteFileOpenAbility = 0
#⁠Redirection = 0

- - - - - - - - - - Machine Details - - - - - - -

Dell dual core Xeon:
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.30
clflush size : 64
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 1
initial apicid : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.23
clflush size : 64
power management:

cat /proc/meminfo
MemTotal: 3635128 kB
MemFree: 938632 kB
Buffers: 125044 kB
Cached: 1951672 kB
SwapCached: 0 kB
Active: 1719064 kB
Inactive: 850080 kB
HighTotal: 2752256 kB
HighFree: 371096 kB
LowTotal: 882872 kB
LowFree: 567536 kB
SwapTotal: 4088500 kB
SwapFree: 4088440 kB
Dirty: 456 kB
Writeback: 0 kB
AnonPages: 492312 kB
Mapped: 68284 kB
Slab: 96688 kB
SReclaimable: 88252 kB
SUnreclaim: 8436 kB
PageTables: 3340 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 5906064 kB
Committed_AS: 1549940 kB
VmallocTotal: 114680 kB
VmallocUsed: 21496 kB
VmallocChunk: 92532 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
DirectMap4k: 4096 kB
DirectMap4M: 913408 kB

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 22G 2.1G 20G 10% /
/dev/sda6 200G 4.5G 196G 3% /data
/dev/sda7 20G 38M 20G 1% /home
/dev/ram0 1.8G 0 1.8G 0% /mnt/ramdisk

environment: Database:
ls -la /data/Drugs/DrugData/DRUGDATA.FDB
-rw-rw---- 1 firebird firebird 3033006080 2009-02-12 12:52 /data/Drugs/DrugData/DRUGDATA.FDB

Tables in query:
ROWCOUNT TABLE_NAME
793272 RXNCONSO
4227730 RXNSAT

CREATE TABLE RXNSAT (
RXCUI VARCHAR(8) NOT NULL,
LUI VARCHAR(8),
SUI VARCHAR(8),
RXAUI VARCHAR(8),
STYPE VARCHAR(3),
CODE VARCHAR(50),
ATUI VARCHAR(10),
SATUI VARCHAR(50),
ATN VARCHAR(50) NOT NULL,
SAB VARCHAR(20) NOT NULL,
ATV VARCHAR(255) NOT NULL,
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX IDX_RXNSAT_ATN_SAB ON RXNSAT (SAB, ATN);
CREATE INDEX IDX_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX IDX_RXNSAT_ATV_FUNC ON RXNSAT COMPUTED BY (SUBSTRING(REPLACE(REPLACE(ATV,'*',''),'-','') FROM 1 FOR 9));
CREATE INDEX IDX_RXNSAT_CODE ON RXNSAT (CODE);
CREATE INDEX IDX_RXNSAT_RXCUI ON RXNSAT (RXCUI);
CREATE INDEX IDX_RXNSAT_SAB ON RXNSAT (SAB);
CREATE INDEX X_RXNSAT_ATN ON RXNSAT (ATN);
CREATE INDEX X_RXNSAT_ATV ON RXNSAT (ATV);
CREATE INDEX X_RXNSAT_RXCUI ON RXNSAT (RXCUI);

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE RXNCONSO (
RXCUI VARCHAR(8) NOT NULL,
LAT VARCHAR(3) DEFAULT 'ENG' NOT NULL,
TS VARCHAR(1),
LUI VARCHAR(8),
STT VARCHAR(3),
SUI VARCHAR(8),
ISPREF VARCHAR(1),
RXAUI VARCHAR(8) NOT NULL,
SAUI VARCHAR(50),
SCUI VARCHAR(50),
SDUI VARCHAR(50),
SAB VARCHAR(20) NOT NULL,
TTY VARCHAR(20) NOT NULL,
CODE VARCHAR(50) NOT NULL,
STR VARCHAR(2000) NOT NULL,
SRL VARCHAR(10),
SUPPRESS VARCHAR(1),
CVF VARCHAR(50)
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE INDEX IDX_RXNCONSO_RXCUI_TTY_SAB ON RXNCONSO (RXCUI, TTY, SAB);
CREATE INDEX IDX_RXNCONSO_SAB ON RXNCONSO (SAB);
CREATE INDEX X_RXNCONSO_CODE ON RXNCONSO (CODE);
CREATE INDEX X_RXNCONSO_RXCUI ON RXNCONSO (RXCUI);
CREATE INDEX X_RXNCONSO_STR ON RXNCONSO (STR);
CREATE INDEX X_RXNCONSO_TTY ON RXNCONSO (TTY);

Firebird config:

Firebird Classic 2.1
firebird.conf:
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
#⁠
#⁠ Firebird configuration file
#⁠
#⁠RootDirectory =

#⁠DatabaseAccess = Full

#⁠ExternalFileAccess = None
ExternalFileAccess = Full

#⁠UdfAccess = Restrict UDF
UdfAccess = Full

TempDirectories = /mnt/ramdisk;/tmp

#⁠LegacyHash = 1

#⁠Authentication = mixed

#⁠DefaultDbCachePages = 2048
DefaultDbCachePages = 512

#⁠DatabaseGrowthIncrement = 134217728

#⁠MaxFileSystemCache = 65536

TempBlockSize = 2097152

#⁠TempCacheLimit = 67108864
#⁠TempCacheLimit = 134217728

#⁠OldParameterOrdering = 0

#⁠CompleteBooleanEvaluation = 0

#⁠DeadlockTimeout = 10

#⁠MaxUnflushedWrites = 100

#⁠MaxUnflushedWriteTime = 5

#⁠BugcheckAbort = 0

#⁠OldColumnNaming = 0

#⁠RelaxedAliasChecking = 0

#⁠ConnectionTimeout = 180

#⁠DummyPacketInterval = 0

#⁠RemoteServiceName = gds_db

#⁠RemoteServicePort = 3050

#⁠RemoteAuxPort = 0

#⁠TcpRemoteBufferSize = 8192
#⁠TcpRemoteBufferSize = 16384

#⁠TcpNoNagle = 1

#⁠RemoteBindAddress =

#⁠LockMemSize = 1048576

#⁠LockSemCount = 32

#⁠LockGrantOrder = 1

#⁠LockAcquireSpins = 0

#⁠LockHashSlots = 1009

#⁠EventMemSize = 65536

#⁠CpuAffinityMask = 1

#⁠UsePriorityScheduler = 1

#⁠PrioritySwitchDelay = 100

#⁠PriorityBoost = 5

#⁠GCPolicy = combined

#⁠GuardianOption = 1

#⁠ProcessPriorityLevel = 0

#⁠IpcName = FIREBIRD

#⁠RemotePipeName = interbas

#⁠CreateInternalWindow = 1

#⁠LockSignal = 16

#⁠RemoteFileOpenAbility = 0

#⁠ ----------------------------
#⁠Redirection = 0
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

Machine:

Dell dual core Xeon:
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.30
clflush size : 64
power management:

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.20GHz
stepping : 3
cpu MHz : 3200.000
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
apicid : 1
initial apicid : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pebs bts pni monitor ds_cpl cid cx16 xtpr
bogomips : 6384.23
clflush size : 64
power management:

cat /proc/meminfo
MemTotal: 3635128 kB
MemFree: 938632 kB
Buffers: 125044 kB
Cached: 1951672 kB
SwapCached: 0 kB
Active: 1719064 kB
Inactive: 850080 kB
HighTotal: 2752256 kB
HighFree: 371096 kB
LowTotal: 882872 kB
LowFree: 567536 kB
SwapTotal: 4088500 kB
SwapFree: 4088440 kB
Dirty: 456 kB
Writeback: 0 kB
AnonPages: 492312 kB
Mapped: 68284 kB
Slab: 96688 kB
SReclaimable: 88252 kB
SUnreclaim: 8436 kB
PageTables: 3340 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 5906064 kB
Committed_AS: 1549940 kB
VmallocTotal: 114680 kB
VmallocUsed: 21496 kB
VmallocChunk: 92532 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 4096 kB
DirectMap4k: 4096 kB
DirectMap4M: 913408 kB

df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 22G 2.1G 20G 10% /
/dev/sda6 200G 4.5G 196G 3% /data
/dev/sda7 20G 38M 20G 1% /home
/dev/ram0 1.8G 0 1.8G 0% /mnt/ramdisk

=>

Firebird Classic 2.1

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

David,

It looks like a simple case of the database being corrupted, nothing to do with the query.

I should investigate/test the HDD/disk controller hardware and then run gfix to resolve the corruption and retry the query.

You should find the problem resolved.

@firebird-automations
Copy link
Collaborator Author

Commented by: David Keith (dkeith2_yahoo.com)

Found it!!!

Have been getting these from time to time. It looks like database corruption. However what I found was that this is caused by the use of a ramdisk as the first of two Temporary Directories.

firebird.conf:
...
TempDirectories = /mnt/ramdisk;/tmp
...

/mnt/ramdisk was created by the following mount command:

mount -t tmpfs /proc/ram0 /mnt/ramdisk

....which creates the following mount:

/proc/ram0 on /mnt/ramdisk type tmpfs (rw)

Still not sure why, but certain queries would cause the error to occur every time. Following the logic that I had a corrupt database I executed

gfix -v -validate /database.fdb -user sysdba -password <password>

This would always return with no errors. I then did a full backup and restore. Certain indexes would fail to recreate with the same error. Could this be due to the size of the index being created and the amount of RAM available to /proc/ram0? In other words, when the ramdisk runs out of available ram, could firebird be failing to use /tmp?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

> Could this be due to the size of the index being created and the amount of RAM available to /proc/ram0? In other words, when the ramdisk runs out of available ram, could firebird be failing to use /tmp?

Firebird must allocate space for temporary file at next available configured folder but it seems this feature is broken now...

BTW, there is *no sence* to use RAM drive for temporary files. Much better is to enlarge TempCacheLimit setting.

@firebird-automations
Copy link
Collaborator Author

Commented by: Robert Gilland (robert.gilland_basx.com.au)

I am getting this one as well:

running this SQL

CREATE INDEX IDX_TEMP_LOGS
ON LOGS ( STOREID, LOGID );

ISC ERROR CODE:335544373

ISC ERROR MESSAGE:
operating system directive write failed

Using Firebird 2.1.2

@firebird-automations
Copy link
Collaborator Author

Commented by: Robert Gilland (robert.gilland_basx.com.au)

LOGS table has 231842669 records

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Robert, your temporary folder just gets out of free space when performing the external sorting (a part of the index creation process).

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE2422 [ CORE2422 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE2514 [ CORE2514 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => In Progress [ 3 ]

Fix Version: 2.5 RC1 [ 10362 ]

Fix Version: 2.1.4 [ 10361 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.4 [ 10361 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Cannot be tested

Test Details: " temporary folder just gets out of free space when performing the external sorting (a part of the index creation process). "

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

Test Details: " temporary folder just gets out of free space when performing the external sorting (a part of the index creation process). " => Test not needed here: "temporary folder just gets out of free space when performing the external sorting (a part of the index creation process)."

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants