Launching LSNRCTL produces an error I am trying to launch LSNRCTL on my Windows 10 64-bit laptop; Oracle 18 XE
I get this error message:

C:\app\Owner\product\18.0.0\dbhomeXE\bin>lsnrctl
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR-2021 13:04:13
Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Message 850 not found; No message file for product=NETWORK, facility=NL
LSNRCTL>

My ORACLE_HOME variable is set to: C:\app\Owner\product\18.0.0\dbhomeXE\bin
Andrew Sayer suggested:

Either unset your oracle_home or set it to c:\ app\Owner\product\18.0.0\dbhomeXE (assuming that’s the real directory)

I followed the suggestions, here is the output:
C:\>lsnrctl status
LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR 02114:18:42
Copyright (c) 1991, 2018, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.15)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory

listener.ora file:
DEFAULT_SERVICE_LISTENER = XE

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Owner\product\18.0.0\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Owner\product\18.0.0\dbhomeXE\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

sqlnet.ora file:
SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

AFAIK all of the above directories exist.
EdStevens said:

You have "TNS-12541: TNS:no listener ".  Your listener is not started.

When I try to start the listener I get this message:
C:\>lsnrctl start

LSNRCTL for 64-bit Windows: Version 18.0.0.0.0 - Production on 10-APR-2021 17:00:21

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Message 1070 not found; No message file for product=NETWORK, facility=TNSTNS-12557: Message 12557 not found; No message file for product=NETWORK, facility=TNS
 TNS-12560: Message 12560 not found; No message file for product=NETWORK, facility=TNS
  TNS-00527: Message 527 not found; No message file for product=NETWORK, facility=TNS

EdStevens wrote:

"message not found" invariably means that ORACLE_HOME is not set correctly.  The message file (the file with the text of all of the error messages) is located in relation to ORACLE_HOME. If ORACLE_HOME is not correct, then it can't find the message file, and all errors will cascade to "message not found".

The question author responded:

I think I solved the problem; the Oracle service TNS Listener was not started. I changed the ORACLE_HOME environment variable to: C:\app\Owner\product\18.0.0\dbhomeXE -  I dropped the "\bin" directory.


What am I doing wrong with subquery I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?

Question: Show each lecturer’s lowest coursework weighting, displaying the Staff ID, the Module ID and the weighting selected.

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2)

There are many many ways to do this. Here is one that only slightly changes your attempt:

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2
                    WHERE m2.ModuleConvenor = l.StaffID) ;


table_open_cache increase issue - MariaDB 10.2 I'm trying to increase to 9000 but MariaDB sets it at 4895 automatically.

Here is my simple config
https://pastebin.com/raw/4eFiuFSv

MySQLTuner output:

Control warning line(s) into /var/lib/mysql/cp.goal.ge.err file
Control error line(s) into /var/lib/mysql/cp.goal.ge.err file
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:

Beware that open_files_limit (10000) variable

should be greater than table_open_cache (4895)

Optimize queries and/or use InnoDB to reduce lock wait
Consider installing Sys schema from https://github.com/mysql/mysql-sys

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 125M)
    table_open_cache (> 4895)

I can see 3 different reasons why MariaDB might reset this variable. In order of most to least likely explanation:

You have another .cnf file where the table_open_cache variable is being set to 4895.
The MariaDB Systemd unit file (or SysV init script on older Linux versions) is overwriting the variable value you've set.
You're running a non-standard MariaDB server that's been compiled with a hard-coded value for table_open_cache. (Unlikely!)

Additionally, it's possible that another software (such as cPanel) might change the variable dynamically after MariaDB has started.

To see which options the MariaDB server will get from the option files, run:

mysqld --print-defaults

To find all the places where the MariaDB server is looking for .cnf files, run:

mysqld --verbose --help | grep -A 1 'Default options are read'

You can then grep each of these files to see if you find any instances of table_open_cache or table-open-cache.

For reference, you should be able to see the current value of the variable with:

show global variables like 'table_open_cache';

And you may also want to consult this page in the MariaDB Knowledge Base: Optimizing table_open_cache.


Finding when a currently running Extended Events session started? For old-school traces, I can look in sys.traces or the SQL Server log to find when a trace was started. Is there something similar for Extended Events? I came across this blog entry about using a DDL trigger but I'm wondering if there is something better.

SQL Server 2012-2014

There is but it does not necessarily provide a "status" itself. If you look at the definition of sys.dm_xe_sessions in Books Online here:

  Returns information about an active extended events session. This
  session is a collection of events, actions, and targets.

The key is active extended events sessions, this DMV is only going to return those XE sessions that are running. So you basically just look at the create_time and that is when the session was started. You can verify this by looking at the system_health session as the time returned should match up to when the instance was last started.

An example is my SQL Server 2012 instance on my local machine is kept stopped most of the time until I need it. I just started it so you can see the times are pretty close:


SQL Router Not Applying Round Rubin I configure sql router under three machines and found if I use simple command mysql -u ia -pTest@123 --port=7001 then its not applying round rubin connection request but when i use with protocol parameter then its working fine "mysql -u ia -pTest@123 --port=7001 --protocol=TCP"
I want to connect with normal command like mysql -u ia -pTest@123 --port=7001
and round rubin should work in this case.

=== Mysql Configuration
[logger]
level = INFO
[routing:secondary]
bind_address = localhost
bind_port = 7001
destinations = 192.168.0.187:3306,192.168.0.190:3306
routing_strategy = round-robin
[routing:primary]
bind_address = localhost
bind_port = 7002
destinations = 192.168.0.186:3306
routing_strategy = first-available (edited)

Every connection has a -h 127.0.0.1 or as you have a --protocol=TCP" on local computers to use round-Robin.

The problem is on linux computers mysql tries to open a socket connection, where you can' use Roud-Robin.

The same is true for Windows computers, but there mysql uses named pipes

so you have to force the connection to use tcp/ip, that works with an ipadress in -h or like you used forcing the protocol


mysqlsh rebootClusterFromCompleteOutage force prompt From mysqlsh I can run this instruction:

...
shell.connect(host, pass);
dba.rebootClusterFromCompleteOutage(clusterName);

But then it's asking me (y|N answer) for each instance it finds offline whether or not I want to turn it on again.

In the context of scripting this instruction (automation), how can I ignore these prompts and force y(es) for all of them?

I have used the rejoinInstances option to specify all my nodes, and it seems that doing it that way the shell doesn't ask for confirmation:

dba.rebootClusterFromCompleteOutage(clusterName, {rejoinInstances: ["node-2:3306","node-3:3306"]});

Note: don't include the seed in the array.


How to update an element in json array with where clause? I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings.

Here are some sample json values from jsonb column of the table.

{"pattern": {"values": ["foo", "edfgh"]}}
{"pattern": {"values": ["abc", "abdhi", "foo"]}}
{"pattern": {"values": ["these", "abcd", "para", "avil"]}}

We can select the nth element using

select data #> '{pattern, values, 0}' from table;

How can we loop through array and update foo to bar if it is present in that array?

You can use a query like the one below;

UPDATE table SET
  data = jsonb_set(data,'{pattern, values}', ((data->'pattern'->'values') - 'foo') ||  '["bar"]' )
WHERE
  (data #> '{pattern, values}') ? 'foo';

Details;

First you have to find records which have "foo" value in "values" array;

(data #> '{pattern, values}') ? 'foo'

After that remove the value "foo" from the array;

((data->'pattern'->'values') - 'foo')

Create a new array by concatenating your new value(s).

((data->'pattern'->'values') - 'foo') ||  '["bar"]'

Replace new array with the old one.

jsonb_set(data,'{pattern, values}', new_array)


How to query the names of encrypted stored procedures? What query will identify the names of stored procedures which are encrypted?  Here's what I got so far -

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need another condition here to identify just the encrypted SPROC's
order by ROUTINE_NAME asc

Thanks in advance.

SELECT name FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsEncrypted') = 1;


how to write code for logon/logoff triggers in oracle? I have written the code for a logon/logoff triggers. Logon is working but the logoff trigger is created with an error. Please give some suggestions.

Logging table:

create table logon_off_audit(
user_id varchar2(30),
session_id number(8),
host varchar2(30),
last_program varchar2(48),
last_action varchar(32),
last_module varchar(32),
logon_date date,
logon_time varchar2(30),
logoff_date date,
logoff_time varchar2(10),
elapsed_minutes number(8));

Login trigger:

create or replace trigger
   logon_audit_trigger
after logon on database
begin
insert into logon_off_audit values(
   user,
   sys_context('userenv','sessionid'),
   sys_context('userenv','host'),
   null,
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null,
   null
);
end;
/

Logoff trigger:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','action') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where
sys_context('userenv','program') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where
sys_context('userenv','module') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/

Basically, all of your sys_context() calls in the subqueries were wrong. It always needs to be sys_context('userenv','session') because you need the sid to join with v$session. Note that you could condense it down to a single update statement.

I've tested this on 12c and it works:

create or replace trigger
   logoff_trigger
before logoff on database
begin
update logon_off_audit set last_action = (select action from v$session where sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_program = (select program from v$session where
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
last_module = (select module from v$session where
sys_context('userenv','sessionid') = audsid)
where
sys_context('userenv','sessionid') = session_id;
update
  logon_off_audit
set
   logoff_date = sysdate
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
   logoff_time = to_char(sysdate, 'hh24:mi:ss')
where
   sys_context('userenv','sessionid') = session_id;
update
logon_off_audit
set
elapsed_minutes =
round((logoff_date - logon_date)*1440)
where
sys_context('userenv','sessionid') = session_id;
end;
/


find all numbers that appear at least three times consecutively This is a question from leetcode online judge

I tried this

SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1

but got runtime error!!

Anyhelp would be appreciated

First of all, your runtime error is based on COUNT (Num). It should be COUNT(Num)

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS guru;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE guru;
Query OK, 1 row affected (0.00 sec)

mysql> USE guru
Database changed
mysql> CREATE TABLE logs
    -> (id int not null auto_increment,
    -> num int not null,
    -> primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

YOUR QUERY EXECUTED

mysql> SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) > 1;
+-----+
| Num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql>

LOOK AT THE QUESTION AGAIN

Write a SQL query to find all numbers that appear at least three times consecutively

From the output in your question, 2 does not occur three times consecutively.

I have an iterative solution using user-defined variables

MY PROPOSED QUERY

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT DISTINCT num FROM
(
    SELECT grp,num,MAX(samecount) same FROM
    (
        SELECT
            (@group := @group + IF(@curnum=num,0,1)) grp,
            (@same  := IF(@curnum=num,@same+1,1)) samecount,
            (@curnum:=num),num
        FROM logs ORDER BY id
    ) AA GROUP BY grp,num HAVING same >= 3
) A;

Looks messy, right ? Here is is executed

MY PROPOSED QUERY EXECUTED

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

mysql>

WHY IT WORKS

Let's strip all the aggregation and subquery stuff and start with the innermost query

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT
    (@group := @group + IF(@curnum=num,0,1)) grp,
    (@same  := IF(@curnum=num,@same+1,1)) samecount,
    (@curnum:=num),num
FROM logs ORDER BY id;

When you run this, here is the output

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@group := @group + IF(@curnum=num,0,1)) grp,
    ->     (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->     (@curnum:=num),num
    -> FROM logs ORDER BY id
    -> ;
+------+-----------+----------------+-----+
| grp  | samecount | (@curnum:=num) | num |
+------+-----------+----------------+-----+
|    1 |         1 |              1 |   1 |
|    1 |         2 |              1 |   1 |
|    1 |         3 |              1 |   1 |
|    2 |         1 |              2 |   2 |
|    3 |         1 |              1 |   1 |
|    4 |         1 |              2 |   2 |
|    4 |         2 |              2 |   2 |
+------+-----------+----------------+-----+
7 rows in set (0.00 sec)

mysql>

Note the column samecount. When samecount reaches 3 or more, the num column has the value that has been repeated 3 or more times.

Adding the aggregation layer, you get this:

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT grp,num,MAX(samecount) same FROM
    -> (
    ->     SELECT
    ->         (@group := @group + IF(@curnum=num,0,1)) grp,
    ->         (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->         (@curnum:=num),num
    ->     FROM logs ORDER BY id
    -> ) AA GROUP BY grp,num HAVING same >= 3;
+------+-----+------+
| grp  | num | same |
+------+-----+------+
|    1 |   1 |    3 |
+------+-----+------+
1 row in set (0.00 sec)

mysql>

After the aggregation, it is possible that a number can be repeated multiple times later on.

Adding the DISTINCT layer on just NUM, you get the end result I said before.

QUERY AGAINST MORE DATA

Let's add more data

INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
INSERT INTO logs (num) VALUES (7),(7),(7),(8),(8),(9),(5);
INSERT INTO logs (num) VALUES (5),(6),(11),(11),(11),(2),(2);
INSERT INTO logs (num) VALUES (4),(4),(4),(4),(4),(3),(3);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);

MY PROPOSED QUERY EXECUTED WITH MORE DATA

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
|   7 |
|  11 |
|   4 |
+-----+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Remove "almost" duplicate rows (may or may not start with plus sign) To remove duplicated rows from Oracle, I tend to use the following:
DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

(taken from SO)
However, I'd like to remove the same rows in a slightly different scenario. Image there is a table with rows: name, surname. Duplicate row is when

name is the same, and
surname is the same, or starts with + (plus sign) and then is the same.

For example, the following is to be considered the same:

name: John, surname Doe
name: John, surname +Doe

How do I remove those duplicated rows?

WITH cte AS(
SELECT rowid, name,
CASE WHEN surname NOT LIKE '+%'
THEN '+' + surname
ELSE surname
END AS surname
FROM your_table)

DELETE FROM your_table
WHERE rowid NOT IN
(SELECT MIN(rowid)
 FROM cte
 GROUP BY name, surname
)


Can't SQL Server store NULLs in fixed-length columns? I came across this statement in the official Oracle documentation:

  In Microsoft SQL Server, only columns with variable-length data types
  can store NULL values. When you create a column that allows NULLs with
  a fixed-length data type, the column is automatically converted to a
  system variable-length data type...

I have never read about this in the SQL Server docs nor have I experienced such a thing. On the contrary: in SQL Server, fixed-length data types (such as int and float, but also char) are heavily used and stored very efficiently even when NULLable.

Is there any rationale behind this Oracle statement?!

Well, this was the case for Sybase, which is the ancestor for SQL Server. Unfortunately, I did not find any information as to when this exact change was made, but according to this article the engine was fully rewritten in SQL Server 2005. As this is not the case for any modern SQL Server edition, you can just ignore this statement.


Find duplicate records across one-to-many relationship tables Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly.

CREATE TABLE People (Id int,
                 first_name varchar(100),
                 last_name varchar(100));

CREATE TABLE Attributes (Id int,
                     person_id int,
                     field varchar(100),
                     field_value varchar(100));

INSERT INTO People VALUES (1, 'John', 'Smith');
INSERT INTO People VALUES (2, 'John', 'Smith');
INSERT INTO People VALUES (3, 'John', 'Smith');

INSERT INTO Attributes VALUES (1, 1, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (2, 1, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (3, 2, 'HairColor', 'Brown');
INSERT INTO Attributes VALUES (4, 2, 'EyeColor', 'Blue');
INSERT INTO Attributes VALUES (5, 3, 'HairColor', 'Blonde');

Which gives us:

 id | first_name | last_name
----+------------+-----------
  1 | John       | Smith
  2 | John       | Smith
  3 | John       | Smith

 id | person_id |   field   | field_value
----+-----------+-----------+-------------
  1 |         1 | HairColor | Brown
  2 |         1 | EyeColor  | Blue
  3 |         2 | HairColor | Brown
  4 |         2 | EyeColor  | Blue
  5 |         3 | HairColor | Blonde

I'd like a query that will return IDs 1 and 2 from the People table. I can find duplicates within a single table:

select first_name,last_name,count(*) from People
    group by first_name,last_name having ( count(*) > 1 );

But I'm having trouble joining the one-to-many table and detecting duplicates across both tables. How can I detect duplicates across tables that have a one-to-many relationship?

One way to do this (check the SQLfiddle):

select
    p1.id as id1,
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( select 1
      from
      ( select *
        from attributes a1
        where a1.person_id = p1.id
      union all
        select *
        from attributes a2
        where a2.person_id = p2.id
      ) g
      group by field, field_value
      having count(*) <> 2
   ) ;

and another:

select
    p1.id as id1,
    p2.id as id2
from people p1
  join people p2
    on  p1.first_name = p2.first_name
    and p1.last_name = p2.last_name
    and p1.id < p2.id
where not exists
    ( ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      union
        select field, field_value
        from attributes a2
       where a2.person_id = p2.id
      )
    except
      ( select field, field_value
        from attributes a1
        where a1.person_id = p1.id
      intersect
        select field, field_value
        from attributes a2
        where a2.person_id = p2.id
      )
    ) ;

Intersect has higher precedence than except/minus, at least in Postgres and SQL Server. Just to be safe, you can use parentheses, to ensure the precedence.


Building where clause based upon parameters In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN

For example:

select fld1, count(fld1)
from xyz
where fld1 in (
    case $1
        when 1 then 'Value1'
        when 2 then 'Value2'
        when 3 then '''Value1'',''Value2'''
        when 4 then '''Value4'',''Value5'',''Value6'''
    else NULL
)
group by fld1

Value 1 works fine as does Value2, Values 3 and 4 fail not syntactically but no rows are returned.

The output of a CASE expression has to be a single value not a list of values.

The statement did not give a syntax error for cases 3 and 4 because  '''Value1'',''Value2''' is evaluated as a single string (which includes 4 quote characters and a comma): 'Value1','Value2'

You could rewrite your condition using a CASE expression that evaluates to boolean, like this:

select fld1, count(*)
from xyz
where case $1
          when 1 then fld1 in ('Value1')
          when 2 then fld1 in ('Value2')
          when 3 then fld1 in ('Value1', 'Value2')
          when 4 then fld1 in ('Value4', 'Value5', 'Value6')
      end
group by fld1 ;

but I'd prefer to write it more more simply using OR:

select fld1, count(*)
from xyz
where ( $1 = 1 and fld1 in ('Value1')
     or $1 = 2 and fld1 in ('Value2')
     or $1 = 3 and fld1 in ('Value1', 'Value2')
     or $1 = 4 and fld1 in ('Value4', 'Value5', 'Value6')
      )
group by fld1 ;


Execution of sequential delete and uncommitted read We are running the following queries using dynamic SQL, which is erroring out for few cases when we run concurrent instances. The error is:

  Could not continue scan with NOLOCK due to data movement...

This question says this error is thrown when a process reads data which is being deleted by another process. Our process is deleting and reading the same rows, however one after the other as per following query (the SELECT is after the DELETE):

DELETE FROM Table1
WHERE colum1 = somevalue1
AND column2 = somevalue2

SELECT COUNT(*)
FROM Table1 WITH (NOLOCK)
WHERE colum1 = somevalue1
AND column2 = somevalue2

I am trying to understand the execution of above query. As the SELECT is uncommitted, does it start executing before the DELETE is committed? Would this mean that removing the NOLOCK hint would stop the error?

As the SELECT is uncommitted, does it start executing before the DELETE is committed?

No, T-SQL statements always execute sequentially in SQL Server. The point is the SELECT may read uncommitted changes made by other concurrently-executing transactions.

Would this mean that removing NOLOCK should stop the error?

Yes, but only because error 601 is only possible when the transaction isolation level is READ UNCOMMITTED. Moving to a different isolation level prevents that particular error from occurring.
Additional information
Error 601 can occur for a variety of reasons, but all share a common theme: the SQL Server engine was following some pointer chain or other, when it encountered a situation where a structure it was expecting to be present had been moved or deleted by another concurrently-executing process.
The number of cases where error 601 can occur has been progressively reduced over SQL Server releases, with SQL Server 2012 the least likely to return this error, though it is still possible.
My own view is that all error 601 incidents are bugs - for a suitable value of 'bug'. The behaviour is certainly undesirable, and goes well beyond the SQL standard's description of phenomena that may be encountered under the READ UNCOMMITTED isolation level.
While it is certainly true that the SQL standard does not define the detailed behaviours of the different isolation levels very well, what it does say leads to the common belief that the only consequence of READ UNCOMMITTED is that a transaction may see data that has been changed by another transaction before that other transaction commits. As a result of that, using READ UNCOMMITTED isolation level is often justified on the basis that almost all transactions commit very quickly (and very few roll back) so reading 'dirty' data is just a timing difference.
Unfortunately, the implementation of READ UNCOMMITTED in SQL Server goes much further than simple dirty reads. A READ UNCOMMITTED transaction in SQL Server can return duplicated data, a partial read of a large data type, skip data records entirely, or simply fail with an 601 error.
It is possible to experience some of these behaviours under READ COMMITTED and even REPEATABLE READ. This leads some to conclude that the only acceptable isolation levels are those that provide at least statement-level consistency, namely row-versioning READ COMMITTED, SNAPSHOT isolation, or SERIALIZABLE.
Of these, row-versioning READ COMMITTED is usually the easiest to transition to. See this Books Online Topic and its subtree for details of row-versioning isolation levels.


How to get the sum of filtered fields in SQL How can I filtered values in SQL?

When I execute this query, I'm getting the result below.

SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
       InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
FROM Stock_TBL
INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
         InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity

I want that filtered like the below screen shot. How can i do it (for this three tables are involving and only 5 stores).

You want to PIVOT the StoreName.  Try something like this:

SELECT * FROM (
  SELECT
    Stock_TBL.StockID,
    Stock_TBL.ItemCode,
    Stock_TBL.ItemDescription,
    InvoiceItems_TBL.StoreName,
    InvoiceItems_TBL.Quantity
  FROM Stock_TBL
  INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
  INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
  GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
) t
PIVOT ( SUM(Quantity)
  FOR StoreName IN (
    [E M B],
    [LB],
    [SHOP],
    [WAT],
    [NIH],
    [AJITH]
  )
) p

Note that you will have to specify the list of stores manually.  If you don't want to do this, then research dynamic pivot.


Help with Normalisation I'm having difficulty understanding how to go from 1NF to 3NF. All the tutorials I've looked at are very technical and I just need someone to tell me in plain English how to do it.

I have this table in 1NF (I think) and need to do functional dependency diagram for it. I don't understand how to join the group fields/attributes with arrows i.e. I don't understand functional, partial and transitive dependencies. Help please.

Here is my table:

And here is my (unfinished) dependency diagram:

As the old saying goes: Technically correct is the best kind of correct.  So following the technical tutorials is something you should do.  However, I remember when normalization was first taught to me it didn't click in right away.  Let's consider the basic steps in plain, but less accurate, English:

First Normal Form (1NF) says to remove columns containing repeating groups to its own table.  Your relation doesn't have any repeating groups in any of the columns, so your relation is already in 1NF.

Second Normal Form (2NF) says that there shouldn't be any non-key attributes which depend on a subset of a multi-part candidate key.  You have one multi-part key.  That appears to be the details of the appointment, which seem to depend on the patient, the doctor and the date.  That means that you need to move the appointment details out to a new relation (along with foreign keys that make a candidate key for the Appointment Details relation.  When talking about 2NF, sometimes you'll hear the term "partial functional dependency".  You want to avoid those.

Third Normal Form (3NF) says that every attribute in a relation depends only on every candidate key (and nothing else).  This is also know as a "transitive functional dependency" - also to be avoided.

If you look at your two relations that you have after achieving 2NF, you have these candidate keys and the following fields which rely on those keys:

Patient ID determines: Patient Name, Sex and DOB.
Doctor determines itself (there are no non-key attributes of doctor)
Patient ID + Appointment Date + Doctor + Details determine the details of an appointment.

NOTE: One other obvious thing that stands out from your sample data is that appointment details is probably actually a compound attribute.  It looks like the details are a number and a textual description, jammed together with " - " between them.  You could make a case, therefore, that the textual description is determined by the leading number.  If that is true of the actual data, then you'd actually want to split out a two-column relation: (e.g.: Treatment ID, Treatment Description) as a separate relation.  This would be part of getting to 3NF as well.

If you did split out a treatment relation, then the appointment relation would only have the Treatment ID, not the description in it.

I hope this helps to understand the steps and how to apply them to your example.  Stick with the technical tutorials.  You'll figure them out soon enough and then you'll start to get an instinctual feel for defining relations in 3NF right from the start.  Once you get comfortable with it, you won't usually need to go through a formal normalization exercise.  It will just come naturally.


java.sql.SQLException: ORA-01410: invalid ROWID I am getting java.sql.SQLException: ORA-01410: invalid ROWID for some of the rows.

Here is my query.

"select OP.PLU_LONG PROD_ID," +
                          "OP.NEW_PRICE PRICE," +
                          "OP.REG_PRICE REGPRICE," +
                          "OP.QUANTITY_LIMIT QUANTITY_LIMIT," +
                          "OP.ON_AD_DATE AD_START_DATE,"+
                          "OP.OF_AD_DATE AD_END_DATE " +
                          "from ABC OP";

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(dbUrl,dbUser,dbPw);
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sqlSTGOP);
            // System.out.println("Entering while block");
            while (rset.next()) {
                String price = rset.getString("PRICE");
                String reg_price = rset.getString("REGPRICE");
                String prod_id = rset.getString("PROD_ID");
                int quantity_limit = rset.getInt("QUANTITY_LIMIT");
                Date ad_start_date = rset.getDate("AD_START_DATE");
                Date ad_end_date = rset.getDate("AD_END_DATE");
            }
        } catch (SQLException sqe) {
            logger.error("**** SQLException occured while getting Special Price and Quantity Limit:"+sqe);
            error2 = sqe.toString();
            System.out.println("Problem in creating result set" + error2);
        } catch (Exception e) {
            logger.error("**** Exception occured while getting Special Price and Quantity Limit:"+e);
            error2 = e.toString();
            System.out.println("Problem in creating result set" + error2);
        }

The error happened here only. When we restart the process, it goes away. It happens once in a while. When we get the values from resultset, then it throws an invalid rowid sqlexception.

sqlSTGOP is a string constant refering to above select query.
driver: oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@a.b.c.d:1521:db
java version: jdk1.6
version of jdbc: ojdbc14_g.jar
dbUrl is the url of database which has this ABC table
dbPw is the password of that database
oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

There is no PK or index on this table.

  Is someone deleting rows?

There is one process which runs before this process which removes and loads the data under table ABC and when it finishes, then this process runs after that. That is why we are unable to find out, why this error is coming even the earlier process finishes its task before starting of this process.

No other process, procedure, trigger, package is using this table ABC.

go to

http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html

and download

http://download.oracle.com/otn/utilities_drivers/jdbc/11204/ojdbc6.jar
http://download.oracle.com/otn/utilities_drivers/jdbc/11204/orai18n.jar

Remove the class.forname and put the jars above on your classpath.  Loose the ojdbc14.


Performance difference between searching table with 350 columns and searching table with 25 columns I am currently doing some hibernate performance test. My requirement is to search for data in a particular column of a table containing 8 million rows and 350 columns. Currently, I don't have the access to that database, however, the test database that I'm now using also contains 8millions+.

My question is, is there a significant difference in terms of scan speed when searching for a data in a table that containts 350 columns than in table containing 25 columns?

By the way, the way I check for data is :

sessionFactory.getCurrentSession().createSQLQuery("SELECT COL_5 from MY_TABLE where COL_5='" + theColValuePassedFromParameter+ "'").list();

Where in if the returned list is empty, the boolean method will return true, else false; I would also appreciate If someone can point me to a better, performant way of searching for a non-primary key data in hibernate.

Thanks in advance...

If someone can point me to a better, performant way of searching for a non-primary key data [...]

Add an index in your database on the column(s) you are filtering on - in this case COL_5. The resulting execution plan should be a single index seek and the performanze difference negligible (or even zero).

Also rather than getting the entire result set, just check if there is at least one resulting row. You can either add a TOP 1 to your query (or whatever SQL equivalent), or add a .setMaxResults(1).


What could be the cause for my high disk IO latency (from sys.dm_io_virtual_file_stats DMV)? These are the results from querying the sys.dm_io_virtual_file_stats DMV. (I add some calculated fields for clarity).

I'm finding certain basic operations in Database 7 are bottlenecking core operations I have running in Database 6.

For example, I have a table in Database 7 that isn't used anywhere, and has a lot of rows (about 300 million). In Database 6, a bulk insert of hundreds of thousands of records is happening every hour throughout the entire day.
If I run a simple SELECT COUNT(1) FROM Database7.dbo.UnusedTableInDatabase it takes about 10 minutes to return, and it appears to eat up a lot of the server resources, IO in particular. My continuously running bulk insert job on Database 6 crawls to almost a halt and a backlog starts to build up.

One thing I know is Database 7 has it's data and log files on the same logical drive.
Database 6 is appropriately setup so that it's data and log files are on separate drives.

Where should I start digging next to try to resolve these issues?

UPDATE: My server is hosted on AWS with the Provisioned IOPS SSD (io1) drive. It sounds like I'm supposed to be seeing up to 64,000 IOPS per volume, but I'm seeing about 2,000 IOPS right now, whereas another server I have on AWS with the General Purpose SSD (gp2) drive is giving me about 6,000 IOPS.

My problem with this DMV is that it misses the critical context of time, so it's difficult to pinpoint when the spikes in latency occur.   If you're running a VM rather than an RDS instance, I'd recommend setting up a Perfmon Trace to collect disk latency metrics so you can narrow down the times.  If that's not an option, push the results of your query to a user table every 5 minutes or so and then trend out the deltas so you can see what periods of time experience spikes.

Once you figure out when spikes are occurring, you'll have an easier time identifying the cause of the latency via Extended Events (e.g. capturing long-running queries), Job History, etc.  Who knows, maybe you've run into a situation where you've got nothing locally directly affecting the I/O during the spikes at which point you need to escalate to Amazon with this information in hand.  Noisy neighbor scenarios happen, so don't rule anything out.

On a side note, if you want to further break down the DMV stats to their database files, check out Paul Randal's post on this DMV explaining how to do that.


SQL Server EXECUTE AS impersonation as RBAC method I wrote a web application that is using AD authentication (Windows) and has it's own authorization module (like RBAC). Backend is SQL Server. A DBA in my team is not happy with us using a service account to talk to the database, he would rather have the actual user impersonated using EXECUTE AS at the database level. He would then use actual SQL roles mapped to lock down the access per user.
To me it sounds like a bad idea as I have to manage 100s of users at the SQL Server level and it just makes the attack surface bigger. What other arguments for or against this setup exist?

As a DBA by night, software developer by day, my preference is managing user security in the database (regardless if that's in addition to managing it elsewhere). SQL Server hooks directly into Windows Authentication and therefore supports both Active Directory User and Active Directory Group objects for security.
A lot of times its logical to map a specific AD Group to a specific database, schema, or subset of entities. E.g. you might use schemas to define related entities by application, and then grant read access to a certain AD Group and write access to another AD Group (with the respective SQL permissions) for one schema and visa versa for another schema as a way to separate users' data access across applications.
There's a lot of ways to splice how you can use security in SQL Server and again, coupled with AD Groups (or if AD groups aren't being used, then SQL roles can take their place) it becomes a lot more manageable than keeping track of individual permissions on each user.
Also from a security perspective, by having only one account that can access anything on the SQL Server you put yourself at higher risk because one account has more access than is needed and if became compromised would potentially be more damaging. One account being used in just as many instances as if you were using native Windows Authentication passed through to SQL Server is the same size of attack surface, and in fact even more risky assuming you're storing the dedicated account's password somewhere in the application. Whereas with Windows Authentication passed through to SQL Server, you don't need to store any credentials in the application.
Additional Info (from extended discussion):
Impersonation
Regarding Impersonation in ASP.NET Core, it is possible to still use legacy impersonation per Microsoft's docs (especially for connecting to a database): Configure Windows Authentication in ASP.NET Core (see the Impersonation section at the end)
Doing so is not really recommended for a few reasons, one being legacy security concerns, and another being it may limit your options in how you write your apps and the implementation is cumbersome in itself.
Alternative solutions for Impersonation are:

Disabling "Windows Authentication" and re-enabling "Anonymous Authentication" in IIS, and then scaling out your own login page for authentication so you can get a handle on the user (using modern encryption standards on the password.)
Implementing Token based authentication.
Adding the Identity role for Entity Framework Core as seen in this Microsoft example: Create an ASP.NET Core web app with user data protected by authorization

One Dedicated User vs Multiple Users / Groups Database Permissions Security Analogy
Premise: A house that has 200 doors and 1 skeleton key under the mat that opens them all is less secure than 200 different keys which each open only their own subset of door(s) that are kept on the key ring of each user's pocket.
One Dedicated User: The 200 door house has one main door you have to go through first. 200 people each have their own key to get through that door. Once you get through that door, the skeleton key for all 200 other doors in the house is under the mat on the inside of that door.
Individual User / Group Permissions: If you get rid of that skeleton key (individual user) and give all 200 users their own unique key that only opens that front door and specific doors in the house, now you've limited the scope of access each user has to the house and theoretically reduced your attack vector. Basically, all you've done is map each user's key to a limited set of doors.
(You're no longer hiding a skeleton key under the mat.)
Think of Active Directory in the same way. Just because Active Directory stores a mapping of users to groups, doesn't mean there's an additional backdoor in your applications. The point of security threat is where your users actually authenticate which is at the application level. (Mapping permissions in SQL is almost exactly like mapping them in AD, except SQL can leverage the existing Groups in AD.)
Takeaway Point
While permission management can be a pain, it isn't any more complex to do it in the database if you're already doing it at the application level. This assumes the users have some sort of AD (or equivalent) permissions already implemented. If their permissions are already configured in AD then the heavy lifting is already done, it's just in the DBA's hands to map each relevant AD Group or set of Users to correlating SQL permissions, but it's really quite simple to do on the database side.


Deriving a computed column based on several nullable columns I'm a bit of an SQL novice.

I've been trying to create an auto generated column called TAG from 5 other columns, some of which can retain NULL marks.

The syntax used to create the auto generated column is:

[Tag]  AS ([Tag Type]+[Parent Tag Type]+[Tag Area No]+[Tag seq No]+[Tag Suffix])

This autogenerates OK if none of the five columns retains a NULL, but if I try to create a row with any one of the columns containing a NULL, the auto generate does not work, and leaves the TAG column blank, even though it creates the row OK with the all the individual column data.

Is there a way I can get the auto generate to work with NULLs?

You could use the CONCAT function in SQL Server 2012 or later, which automatically ignores NULL, and implicitly converts the inputs to string types if necessary.

Example:

CREATE TABLE #Demo
(
    [Tag Type] char(1) NOT NULL,
    [Parent Tag Type] char(1) NULL,
    [Tag Area No] integer NOT NULL,
    [Tag seq No] integer NOT NULL,
    [Tag Suffix] char(1) NULL,

    [Tag] AS
        CONCAT([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
);

INSERT #Demo
    ([Tag Type], [Parent Tag Type], [Tag Area No], [Tag seq No], [Tag Suffix])
VALUES
    ('T', NULL, 1234, 1, NULL),
    ('T', 'P', 5678, 1, 'S');

SELECT * FROM #Demo AS D;

Output:

Tag Type | Parent Tag Type | Tag Area No | Tag seq No | Tag Suffix | Tag
:------- | :-------------- | ----------: | ---------: | :--------- | :-------
T        | null            |        1234 |          1 | null       | T12341
T        | P               |        5678 |          1 | S          | TP56781S

... and just in case the example was simplified and a separator is to go between the elements, check out CONCAT_WS() introduced in SQL Server 2017.


Suppress SQLCMD messages Would it be possible to suppress all sqlcmd messages output to the cmd window?

I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages.

The script is over 4Gb so there is a large amount of data.

At the top of the script put the following

SET NOCOUNT ON

That will hide the n rows effected messages.


MongoDB Atlas M0 (Free Tier) BI capabilities I'm using MongoDB in my server and developed BI tool using cube-js & mongosqld, which worked when mongo was running locally, however after I moved to Atlas using M0 tier I could not connect to it anymore using mongosqld, this is the error I get:

  unable to load MongoDB information: failed to create admin session for loading server cluster information: unable to execute command: server selection error: server selection timeout

Checking the documentation again I realized BI Connector is not supported in M0:

https://docs.atlas.mongodb.com/bi-connection/#bi-connection

So I guess this error is expected ?

In that case, what other options I have to develop a visualisation tool while keep using M0 tier ? Of course the preferred way for me is to keep using cube-js app which I already developed.

This is the documentation I used originally and it does mention limitation for free tier but doesn't say it's not supported at all so I'm confused what's the root cause for this error message and if it should work or not:

https://docs.mongodb.com/bi-connector/v2.1/tutorial/connecting-to-atlas/

I also verified I CAN connect using mongo:

mongo mongodb+srv://<my-user>:<my-password>@<my-domain>/<my-db>

however when I try the same uri in mongosqld:

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb+srv://<my-user>:<my-password>@<my-domain>/<my-db>

I get the following error:

  when authentication is enabled, admin credentials must be provided with --mongo-username and --mongo-password or in a config file at 'mongodb.net.auth'

I then tried to connect using the following command:

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb+srv://<my-domain>/<my-db> --mongo-username <my-yser> --mongo-password <my-password>

and got this error:

  error parsing uri: authsource without username is invalid

then I tried without "srv":

mongosqld --mongo-ssl --sslMode allowSSL --auth --sslPEMKeyFile test.pem --schema <my-schema>.drdl --mongo-uri mongodb://<my-domain>/<my-db> --mongo-username <my-yser> --mongo-password <my-password>

and got the error mentioned originally - "failed to create admin session"

Your BI Connector links are from different documentation contexts (Atlas vs On-Prem). For an overview of deployment variations, see About the MongoDB Connector for BI.

  Checking the documentation again I realized BI Connector is not supported in M0: https://docs.atlas.mongodb.com/bi-connection/#bi-connection

The MongoDB Atlas documentation is referring to "BI Connector for Atlas", which is an integrated feature allowing you to add a managed instance of BI Connector to an M10+ Atlas deployment. Since BI Connector operations may be CPU and memory intensive, adding a managed BI Connector is only supported for dedicated Atlas clusters.

  This is the documentation I used originally and it does mention limitation for free tier but doesn't say it's not supported at all so I'm confused what's the root cause for this error message and if it should work or not:
  https://docs.mongodb.com/bi-connector/v2.1/tutorial/connecting-to-atlas/

This is the product documentation for the On-Prem MongoDB Connector for BI. If you install this in your own environment, you can connect to a supported MongoDB deployment (including the Atlas free tier) subject to any caveats for your deployment environment.

For example, the Atlas free tier cannot run aggregation pipelines using the allowDiskUse option. This means aggregation stages will be limited to using 100MB of RAM per stage, so queries might fail if one of the intermediate result sets is too large.

See Atlas Free Tier Limitations for other considerations.

  unable to load MongoDB information: failed to create admin session for loading server cluster information: unable to execute command: server selection error: server selection timeout

This error message indicates inability to connect to your Atlas deployment. Make sure the source IP for your BI Connector instance is whitelisted in Atlas and check the connection string details.


SQL Native Client 11 - SQL Server 2017 We currently connect to SQL Server 2014 with the Native Client without any issues.

However, the SQL Server is being upgraded to SQL 2017. I have been told that SQL Native Client will no longer work for connecting, and we need to install the OLE DB driver instead.

Is this correct? I have not found much online other than the Native Client is deprecated and also this article on the lifecycle.

What impact would this have on existing .Net applications and SSIS packages that connect to the database.

Here is a sample connection from an application...

connectionString="metadata=res://*/LoggingModel.csdl|res://*/LoggingModel.ssdl|res://*/LoggingModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer;initial catalog=DBLogging;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"

Microsoft has deprecated the SQL Server Native Client (NCLI) driver, however SQL Server itself uses a wire-protocol, TDS that is independent of the driver used by the client.

NCLI will continue to work for the forseable future, however no new work will be conducted by Microsoft on the NCLI driver.

Pedro Lopez talks about the announcement, and subtle un-deprecation here.  Also included are some sample connection string changes that will help migrate over to the MSOLEDBSQL driver.


SQL Server How to Set up Maintenance plan to shrink log So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL".

When it is to FULL and I perfrom shrink -> log -> it doesnt reduce much
but If I set recovery model to simple it reduces it to 1 MB.

The problem is as I see it I need to leave it at full so my back ups have full version of the database.

Now I created a maintenance plan to shrink database, but im noticing it also almost doing no shrinking again because I guess recovery model is at full. How can I get a maintenance plan working which will actually reduce the log file ?

When you switch the recovery mode to shrink the log file you are invalidating prior backups because it breaks the log chain.  What you need to do it switch it to simple, shrink it down to what you want, then switch it back and set up regular log backups (I would recommend hourly) in addition to full backups.  When a log backup is performed the log is truncated (note, not shrunk.  it will remain the same size on disk) and then the database will be free to re-use the space at the beginning of the file instead of appending to the end of the log file and causing file growth.


MongoDB Server has authorization schema version 3, but found a schema version 1 user Trying to restore a production MongoDB onto a dev MongoDB and getting this error

assertion: 17408 Server has authorization schema version 3,
but found a schema version 1 user: { _id: ObjectId('5256c667c8bf4cc093fa9794'),
user: "adminuser", userSource: "mongoUsers", roles: [ "userAdminAnyDatabase",
 "dbAdminAnyDatabase", "clusterAdmin", "readWriteAnyDatabase" ] }

Production Server is sharded and using version 2.6.3

Dev Server is a 2.6.3 and using version 2.6.3

Client being used to restore is version 2.6.1 (maybe this is the problem?)

Only the user databases are copied, the config, admin and users databases are not restored.

Problem solved

Upgrading to mongo shell 2.6.3 resolved the issue.


MariaDB Incremental Backup always creates full backup (mariabackup/xtrabackup) I am trying to set up an incremental backup solution for my MariaDB database (stored in MyISAM storage, which is listed as supported) using the included mariabackup tool (a version of xtrabackup).

The issue I'm having is that the 'incremental' backups are all identical in size to the primary full backup and to one another. The steps I am going through are as follows.

First, I make a full backup of the database:

mariabackup --backup --databases="database" --target-dir /some/place/backup/full/ \
    --user backupuser

This creates a full backup which can be successfully prepared and restored.

Next, I aattempt to make an incremental backup using the command:

mariabackup --backup --databases="database" --target-dir /some/place/backup/inc/1/ \
        --incremental-basedir /some/place/backup/full/ --user backupuser

This command runs without error but it creates a full backup, even though the from_lsn and to_lsn are both identical (the largest/latest lsn).

Regardless of whether the lsn has incremented since the last backup, the log always shows xtrabackup: Transaction log of lsn (x) to (x) was copied. where x is the database's current lsn number.

If no data has been entered in the database since the last backup, then looking at the xtrabackup_checkpoints files I see:

Full backup:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

Incremental backups:

backup_type = incremental
from_lsn = 1706249
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

In this case the incremental backups seem like they should be copying no data over.

If the lsn has increased since the last backup, I see:

Full backup:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

Incremental backups:

backup_type = incremental
from_lsn = 1706249
to_lsn = 1706269
last_lsn = 1706269
recover_binlog_info = 0

In this case it looks like it should be copying only 1706249 to 1706269, but it copies the entire db. What am I doing wrong?

MyISAM tables are not transactional so they are copied in full with each incremental backup.


Oracle is not using a unique index for a long key I have a table with 250K rows in my test database. (There are a few hundred millions in production, we can observe the same issue there.) The table has an nvarchar2(50) string identifier, not null, with a unique index on it (it's not the PK).

The identifiers are made up of a first part that has 8 different values in my test database (and about a thousand in production), then an @ sign, and finally a number, 1 to 6 digits long. For example there could be 50 thousand rows that start with 'ABCD_BGX1741F_2006_13_20110808.xml@', and it is followed by 50 thousand different numbers.

When I query for a single row based on its identifier, the cardinality is estimated as 1, the cost is very low, it works fine. When I query for more than one row with several identifiers in an IN expression or an OR expression, the estimations for the index are completely wrong, so a full table scan is used. If I force the index with a hint, it is very fast, the full table scan is actually executed an order of magnitude slower (and a lot more slower in production). So it is an optimizer problem.

As a test, I duplicated the table (in the same schema+tablespace) with the exact same DDL and exact same content. I recreated the unique index on the first table for good measure, and created the exact same index on the clone table. I did a DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true);. You can even see that the index names are consecutive. So now the only difference between the two tables is that the first one was loaded in random order over a long time period, with blocks scattered on the disk (in a tablespace together with several other big tables), the second was loaded as one batched INSERT-SELECT. Other than that, I can't imagine any difference. (The original table has been shrinked since the last big deletion, and there hasn't been a single delete after that.)

Here are query plans for the sick and the clone table (The strings under the black brush are the same all over the picture, and also under they gray brush.):

(In this example, there are 1867 rows that start with the identifier that is black brushed. A 2-row query produces a cardinality of 1867*2, a 3-row query produces a cardinality of 1867*3, etc. Can't be a coincidence, Oracle seems to not care about the end of the identifiers.)

What could cause this behavior? Obviously it would be pretty expensive to recreate the table in production.

USER_TABLES: http://i.stack.imgur.com/nDWze.jpg USER_INDEXES: http://i.stack.imgur.com/DG9um.jpg I only changed the schema and tablespace name. You can see that the table and index names are the same as on the query plan screenshot.

I found the solution! It is so beautiful and I actually learned a LOT about Oracle.

In one word: histograms.

I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the cloned table. For the sick table, there was one row for each of the 8 different identifier-starting-parts. And this is the key: they were cut off at 32 characters, before the @ sign. As I said, the first part of keys is highly repetitive, they become different after the @ sign.

It seems that histograms can be more powerful than the simple fact that a unique index always has a cardinality of 0 or 1 for a given value. When I was querying for 2+ rows, Oracle looked at the histogram, it thought that there could be tens of thousands of values for that identifier-starting-part, and it threw the CBO off course.

I deleted the histograms for that column in the old table and the problem went away!

More reading: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating


SSIS - How to parse extensions from path-filename strings I'm importing a large set (126M rows) of file data from CSVs. Import works fine, except I'm trying to derive a column as I import rather than trying to script it out afterward (due to the number of rows).
SSIS is not terribly helpful with string manipulation functions, and the possibilities I have are pretty complicated as well.

The derived column needs to reflect the file extensions. File extensions range from 1-10 characters (anything longer can be truncated at 10), and are (of course) separated from the file name and path by a period. They don't correspond to a concise list of file types (something like "docx, xlsx, accdb, msg" are Office types). The file path may also have periods in it. And in some cases it will not have files at the end of the path, just a folder name itself.
Some examples:

\\Server\Share\named resource
\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory
\\Server\Share\Group\Year.Month.Day.Subfolder\File.ext
\\Server\Share\Team Folder 1.404\another.file.here.extension

So I need to be able to extract the extensions up to 10 characters.
I tried TOKEN() but with folder names possibly having periods, that didn't work well - adding \ as a token delimiter helped some, but I still got odd extensions from folder names.
I can't find a combination of SUBSTRING(), RIGHT(), FINDSTRING(), and/or TOKEN() that will meet the rules and the derived column definition doesn't allow logic like IF or IIF.

Some false positives are expected and I plan to sort them out after importing. If it helps here, I have a second column in the CSVs which is extracted by SSIS, it's the size of the file (for folders it populates a 0). I haven't gotten this to matter either, because of the lack of IF or IIF in derived column definition.

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator

? Conditional

One way to implement the Derived Column would be to parse the string like this

 TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1
 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) )

In this case, "name" is the column that holds the string to be parsed.

What we are doing here is:

Remove any trailing white-space with RTRIM( name )
Reverse the string so we are dealing with the end as the beginning with REVERSE()
Grab the first "token" delimited by a backslash using TOKEN()
Count how many "." delimiters are in the result using TOKENCOUNT()
Test the count of "." delimited tokens is equal to 1 using ?
If the count is 1, return an empty string - no extension.
Otherwise return the first token before the "." using TOKEN()
Reverse the extension to get back to "normal" using REVERSE()


innodb_buffer_pool_size, optimize and mysqltunner I'm trying to understand how to properly size innodb_buffer_pool_size and when to optimize my tables. Questions are:

When sizing the innodb_buffer_pool_size, should I take into account the size of the tables on disk (du -sh /folder_with_db_data) or the size reported by a tool like mysqltunner.pl
When running mysqltunner.pl, it says that my tables are 0% fragmented, yet, the space in disk is much higher than the space reported by mysqltunner.pl. After running an optimize for all-databases I can see that the space on disk of the data shrinks until it matches the space previously reported by mysqltunner.pl. Question is, why if there is "no fragmentation" does the data on disk shrinks?
Could you help me understand what the "optimize" really does?
Setting the innodb_buffer_pool_instances parameter so I end up with 1 instance per each GB of innodb_buffer_pool_size, is a mariadb good practice. Does this setting could involve any risk? I ask this because of the application I'm using (Zimbra), does not even mention this setting and I'm curious about it.

Thank you very much!!!

innodb_buffer_pool_size

No; wrong approach.

Simply set innodb_buffer_pool_size to about 70% of available RAM.

If you make it too much larger than that, you risk swapping, which hurts performance terribly.

If you make it too much smaller than that, your queries run slower.

If you have a lot more data than that, you will simply hope that the "working set" is smaller, and the performance will still be good.

If you have a lot less data than that, you are wasting some RAM that you won't be using anyway.

Fragmentation

Bogus.

There are at least 3 ways in which InnoDB fragments.  The available metrics measure only one of them.

Fragmentation has very little to do with performance.  So, as long as you are not threatened with running out of disk space, don't defragment.  Furthermore, promptly after defragmenting, the freed up space will be quickly gobbled up.  The encouraging thing is that the used space will stop growing as fast after it gets to some steady state.

Catch 22:  If you are about to run out of disk space, you probably can't run OPTIMIZE because it needs space to run!

OPTIMIZE TABLE

Ignore their advice; never use it for InnoDB tables.

It copies the table over, thereby removing any "free" space because the copying compacts things better.  But, I repeat, this only squeezes out some of the "free" space.

Well, OK, there are rare cases where OPTIMIZE is useful.  However, MysqlTuner "cry's wolf" a lot on this item.

innodb_buffer_pool_instances

Simple; don't worry.

Just set it to an integer that is innodb_buffer_pool_size/1G, but no less than 1 and no bigger than 16.  No risk.  But, also, virtually no gain.  In a very busy system, it may speed things up by only a few percent.  If you have a very busy system, you need to be looking at other ways to avoid falling off the cliff.

The application probably does not mention it because either (1) they did notice it when it was added (MySQL 5.5), or (2) they realized it did not matter much.


Way to ensure just one connection to a DB2 database Let's suppose a database with a bad security design, say everyone access to the database with a user with DBADM or SYSADM authority, and also, there could be only username for everyone (i.e. db2inst1).

I would like to perform an administrative task, for example a schema migration, and I need to be the only one connected to the database.

How can I be sure that I will be the only one that access the database?

Quiesce database is not enough because other users access with a high privilege user.
Quiesce instance does not allow to activate the database if restrict access is selected.
DB2COMM could be removed from db2set, however a local cron could be eventually fired.
MAXAPPLS to 1. However, I should be that one, if not force apps again should be issued.
Connect in exclusive mode could not work if the user is used by others.
Changing the workload configuration to allow only me? (from my IP)

However, those are too many changes just for a simple task.

I saw a similar discussion, however I do not see something that convince me: http://database.ittoolbox.com/groups/technical-functional/db2-l/luw-how-to-prevent-connections-to-a-single-database-within-an-instance-3189860

What are your propositions? What would you do in that case?

The easiest way to do this would be to simply uncatalog and then re-catalog the database with a different name (perform this as the instance owner on the database server):

db2 uncatalog database SAMPLE
db2 catalog database SAMPLE as XXX on <path>

... perform maintenance activities ...
db2 uncatalog database XXX
db2 catalog database SAMPLE on <path>

Any user trying to connect locally on the database server, using connect to sample, will get the error:

SQL1013N  The database alias name or database name "SAMPLE" could not be found.  SQLSTATE=42705

However, for remote connections to also be unable to connect, you'll have to restart the DB2 instance.  This is required because the DB2 TCP Connection Manager (db2tcpcm) will cache the the database catalog entries, so until the EDU is restarted it will still "know" about the database.  After restarting the DB2 instance, remote connections will get the error:

SQL30061N  The database alias or database name "SAMPLE            " was not
found at the remote node.  SQLSTATE=08004

Having to restart the instance isn't the best solution, but it works.


How to apply filter at runtime in SSRS 2008 R2 In my report in SSRS 2008 I have created a expression for the CasesShipped column like this:

=IIF(Fields!Current_Product.Value= "Match"," ",Sum(Fields!Cases_Shipped.Value))

It sums the Cases_Shipped value having same distributor item code. Now I want to apply  a filter for the Cases_Shipped drop down list value to report records after previously mentioned activity.

How can I do this?

Probably the easiest way would be to use a drill through report. Add an action to the row group that links to a new report. Duplicate the layout of the new report, but implement a filter on the dataset of interest.

Pass the row group value ([SQLFieldName]) to a parameter of the report. If you use a multivalue variable with an IN statement in the SQL, several filters can be used at once.


Which database should be using for my specific goal Introduction:
I am trying to figure out what kind of database I should use for my project to get the best speed possible.
Background:
I am not sure if MySQL is the best solution, but I will use MySQL as an example to explain the data that I currently have.  The data currently (or will) be arranged in 2 tables.
Table 1 has have 3 columns and Table 2 has have 1 column.  Both tables will have around 1 billion rows each.
The project will get the first value from Table 2 and check if it exists in the first column of Table 1.
This project is currently running on a computer which has a mechanical drive.
Rough estimates suggest at the data will use around 500GB of drive space.
Goals:

To be able to insert the data into the tables as fast as possible,
To be able to compare each column in Table 2 to see if the same value exists in column 1 of Table 1 as fast as possible.

Progress:
At the moment, I am inserting the data into a MySQL database and batching the inserts into groups of 1000, which is averaging to about 200 inserts per second.  At this rate, it will take about 2 months to generate and insert the 1 billion records..
Benchmark:
The process of generating the data and inserting the data is using around 5% CPU, around 1GB RAM of the 16GB available, and when checking the disk IO, it is averaging about 2MB/s.  So my guess is that the mechanical drive is the bottleneck.  I can constantly hear it seeking away.
Question:
With the possible mechanical drive as the bottleneck in mind (correct me if I am wrong about this being the bottleneck), is MySQL the best option to achieve the 2 goals above as fast as possible, or is there a different type of database I should be considering and using instead?

XY Problem
I see too many Red Flags in your post to leave a comment.  In general, you seem to have an XY Problem.
Problem 1

I can constantly hear it grinding away.

STOP! RUN! DO NOT WALK! GET THIS HD REPLACED ASAP!!
If it is really making a grinding sound, then your HD is already toast.
Problem 2

it is averaging about 2MB/s

Modern Hard drives can achieve an IO transfer speed in the 600 MB/s range.  Your observation means you have a non-DB related issue.
Make sure you run your IO benchmark outside of the DB. If your Data Process is not coming close to that measured transfer speed, then the bottleneck is not with the disk IO.
Usually, it is with the way you are processing your data.
Problem 3

averaging to about 200 inserts per second.

Ahh!! Now we are getting to the heart of the problem.
This is usually an indication that you are using a slow-by-slow process method to load data.
You need to load data in Bulk.  When you do that, you should see INSERT rates closer to 1M rows in mere seconds. Your IO transfer rates should then be closer to what you measured outside of the DB.
Problem 4

With the possible mechanical drive as the bottleneck

Use a RAID setup to improve IO/IOPS performance.  This applies to both HDDs and SSDs.
Problem 5

To be able to compare each column in Table 2 to see if the same value exists in column 1 of Table 1 as fast as possible.

Relational Databases are built based on Relational Algebra. If you can change your Business Requirement to be a Relational Algebra question, then the solution becomes a no-brainer
I suggest you Change your Requirements so that

The values in Table_1.Column_1 need to be UNIQUE and NOT NULL
The database is not allowed to accept data for Table_2 that does not comply with the stated requirements.

From there, the answer becomes "Use a FOREIGN KEY on each of the columns in Table_2 that points to the PRIMARY KEY (TABLE_1.COLUMN_1)".
The overhead for the FK check during INSERT should be minimal.
I am unaware of any of the major relational databases that cannot handle a 1 Billion row child table and/or 1 Billion row parent table. So, MySQL should be just fine. (BTW - "List of Software" questions are off topic for this forum.)


SQL Server db user mapped to windows login not in SQL Server logins I am on a SQL Server 2008 where I login with DOMAIN\USER_A

On my database I can see this login is mapped to the user dbo of that database.
What I don't understand is that DOMAIN\USER_A is nowhere to be found in the SQL Servers Security Logins.

CURRENT_USER reports dbo
SYSTEM_USER reports DOMAIN\USER_A as do SUSER_NAME

I can't find DOMAIN\USER_A in the sys.syslogins? What is going on? Can a database user have its own login circumventing the SQL Server?

The only way a login has rights to a server is if those rights have been granted.  The question is likely:  How did these rights get granted?

If DOMAIN\USER_A is a member of some Windows group, then through the rights granted to the group the DOMAIN\USER_A login exists in the server.  This is true even if there is no individual entry in sys.database_principals or sys.server_principals for that login.

You can look for the permission paths used by a login as follows:

EXEC xp_logininfo 'DOMAIN\USER_A', 'all'

You might find several permission paths for a login.


When should I NOT set the Maximum Server Memory option in SQL Server? In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher.

http://www.brentozar.com/archive/2012/11/how-to-set-sql-server-max-memory-for-vmware/

I have a new server with 64GB RAM. Due to licensing constraints, this server must run the SQL Server database engine, SSIS, and SSAS. SSIS ETLs happen overnight, and users hit the actual database mostly during the daytime. In this scenario, would it be best to leave the default setting so that SSIS can take the memory if needed, and then the database engine can take memory when it needs it?

You should always set your max memory away from default and leave some room for OS (see Jonathan's post of how much Memory to leave based on the amount of RAM installed).

Jonathan Kehayias has blogged about : How much memory does my SQL Server actually need?

  reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can also refer to my answer here for more details.

  would it be best to leave the default setting

NO, dont leave it as default as problems like OS unresponsiveness, Working Set trimming as well as other applications running on the server will be affected adversely. It will affect your backups as well.

Note that Memory Manager for SQL Server 2012 and up has changed.


Unable to connect to MongoDB replica set using public IPs I have configured a three-node replica set in MongoDB. I am able to connect to each server and replication is in sync. But when I'm trying to connect to them as a replica set from a public IP, I am getting an error:

  No primary detected for set rs0

But I am able to connect to the replica set from private IPs.

mongo --host rs0/52.X.X.X:27017,52.X.X.X:27017,52.X.X.X:27017 -uroot -pabc123 --authenticationDatabase admin this public IP not able to connect

mongo --host rs0/10.X.X.X:27017,10.X.X.X:27017,10.X.X.X:27017 -uroot -pabc123 --authenticationDatabase admin

With these private IPs we are able to connect to the replica set.

Check your firewall settings!
If FW is open to outside, you can connect single node even with telnet. Of course check that you haven't configure mongod to listening only private IP network adapter.

If you are behind NATed gateway, there should be redirection from outside network to servers.


Prevent drop column with index on it in PostgreSQL In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in production.

Here is a demonstration:

CREATE TABLE test (
    id INT     NOT NULL PRIMARY KEY,
    a  VARCHAR NOT NULL,
    b  VARCHAR NOT NULL
);
CREATE INDEX test_index ON test(a, b);

SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
test      | test_index
*/

ALTER TABLE test DROP COLUMN b;
SELECT * FROM pg_indexes WHERE tablename='test';
/*
tablename | indexname
---------------------
test      | test_pkey
*/

Is it possible to make PostgreSQL return error when you drop a column with an index on? Or maybe some other ways to prevent described situation?

There is no way to do that except writing an event trigger that throws an exception whenever certain users try to run schema modifications.

You should make sure that any database user that connects and doesn't know better than randomly dropping columns without understanding the consequences does not own any database object and doesn't have the CREATE privilege on any schema.


SQL Server and SharePoint time problem I have created a SharePoint application which has a specific time zone.
The Windows server where SQL Server and SharePoint are installed are using same time zone.

When I create a new item in list in SharePoint it shows some different time zone time.

I don't know why this is happening but I suspect this is because of SQL Server. If I query current date and time in SQL Server then it shows correct time zone but in SharePoint it is showing different times.

What could be the reason?

SQL Server gets its time Zone information from the operating system on which it is installed. So if SELECT GETDATE() reveals the right information, then SQL Server is most likely not the problem.

You can query the registry on your SQL Server, if you have the appropriate permissions, and see what the server's time zone is set to:

    DECLARE @TimeZone NVARCHAR(255)

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

N'StandardName',

@TimeZone OUTPUT

SELECT @TimeZone

That should tell you what time zone your SQL Server thinks it is in.

I would suggest that the issue is either the client on which you are running the SharePoint application session - or more likely a setting in SharePoint itself or the server time zone on the SharePoint server itself.

You can check the regional settings for a site and see what it reports. This link should help get you started there.


Oracle 11gR2 Enterprise manager problem I joined a company as a fresher DBA and in testing server there is weird problem.

My listener is running very well and developers can connect remotely to database but whenever I tried to start enterprise manager it shows me error like:

  Listener status down. The listener "(Listener name)" is not running
  using the Listener parameter file:
  "D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin/listener.ora" on
  Host: "(Hostname)" at Port: "1521".

When I write emctl status dbconsole it shows me:

  C:\Windows\system32>emctl status dbconsole Oracle Enterprise Manager
  11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2010
  Oracle Corporation.  All rights reserved.
  https://(HOSTNAME):5500/em/console/aboutApplication Oracle Enterprise
  Manager 11g is running.

  Logs are generated in directory
  D:\app\dbadmin\product\11.2.0\dbhome_1/(Hostname)_(Listenername)/sysman/log

Here is my listener.ora file:

(Listener Name) =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = (Hostname))(PORT = 1521))
    )
  )

lsnrctl status:

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JUL-2018 14:33:08

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                02-JUN-2018 19:18:22
Uptime                    37 days 19 hr. 14 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         d:\app\dbadmin\diag\tnslsnr\ESTDEV03\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ESTDEV03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "********" has 1 instance(s).
  Instance "*******", status READY, has 1 handler(s) for this service...
Service "******" has 1 instance(s).
  Instance "******", status READY, has 1 handler(s) for this service...
The command completed successfully.

TNSPING Result:

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 13-JUL-2018 10:16:01

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (Service Name))))
OK (20 msec)

TNSNAMES.ORA File:

# tnsnames.ora Network Configuration File: D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

(Listener Name)=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = (service name))
    )
  )

(Listener Name) =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

I asked old DBA but he was not able to find a solution.
So can you please tell me where is a problem?

Try resetting the local agent:

> emctl stop agent

> emctl clearstate agent

> emctl start agent


Is this good/bad database design and why? I am building a database for a small project. I don't have much experience so I am unsure, but I get the feeling I am not approaching this correctly.

I have a system which allows users to create courses, which contain sections of questions which they answer. I have separated out courses, sections and questions as separate entities, but the questions table requires foreign keys from:

The Users table to know who created it.
The Courses table to know what course it is part of.
The Sections table to know what section of the course it is in.

Question:
In terms of the Questions table requiring foreign keys from everywhere, is this layout acceptable? Is there any improvements that could be made?

Thanks for any help.

Bad. Here are some random notes which come from experience dealing with this sort of system. Take as you will:

A big offender is xyz_N columns (i.e. answer_2 and a5_comment). When you see this, think incorrectly normalized data. What happens if there are 6 questions? 100 questions? (Oh, yes, I develop this sort of software and you wouldn't believe how many questions people are expected to answer!)
Another bad design is trying to "share" a table: Question also serves as "Answers"? No; again, de-normalized data! How will multiple people answer without duplicating data? It's not possible because each "Answer" is a Question! Think in terms of actions/queries and ask if they can be fulfilled.
Keeping the above in mind, there are very crucial missing relationships for Surveys/Exams (which "contain Questions") and Responses (which "contain Answers"). These relationships are very important for normalization (no xyz_N columns) and being able to ask questions like: "Which questions should I show now?"
There are no "Users" relating to Courses. There are Teachers (Faculty) and Students. Students can not "own" courses. This is easily and cleanly separable and allows better DRI. Also, different information needs to be captured for different "user" types - trust me. Also, sometimes a Teacher is a Student as well!
Redundant relationship from "Answer"/Question to Course; this relationship exists Question to Section to Course and does not need to be duplicated.


Help on improving the query performance of MySQL table My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed.
The table is poorly designed by someone long before I joined the team. It was all working well as there was very less data in Db till now. Recently we launched the tool to a wider audience and the Db is now having huge data. The 'select' query speed is very slow and takes days to get result.
I am not an expert in Databases. Please help on suggestions to improve the performance. I am thinking of options like creating indexes. We cannot have downtime for the system.Th Db is write heavy (around 200 insertion per second).  I am afraid of creating table locks if i try out index creation.
Table schema:
no primary Key, no indexes
CREATE TABLE `ImageDetails` (
  `BOOKNAME` varchar(11) DEFAULT NULL,
  `PREV_ID` varchar(11) DEFAULT NULL,
  `TIMESTAMP` varchar(100) DEFAULT NULL,
  `IMAGE_FILENAME` varchar(1000) DEFAULT NULL,
  `POSITION_ID` varchar(1000) DEFAULT NULL,
  `PRECEDING_TEXT` varchar(3000) DEFAULT NULL,
  `WIDTH` float DEFAULT NULL,
  `HEIGHT` float DEFAULT NULL,
  `IMAGE_ORIENTATION` varchar(100) DEFAULT NULL,
  `COMPUTED_WIDTH` float DEFAULT NULL,
  `COMPUTED_HEIGHT` float DEFAULT NULL,
  `SRC_STYLE_WIDTH` varchar(100) DEFAULT NULL,
  `SRC_STYLE_HEIGHT` varchar(100) DEFAULT NULL,
  `LAYOUT_TYPE` varchar(30) DEFAULT NULL,
  `COVER` varchar(10) DEFAULT NULL,
  `TYPE` varchar(250) DEFAULT NULL,
  `TYPE_SCORE` float DEFAULT NULL,
  `CATEGORY` varchar(500) DEFAULT NULL,
  `CATEGORY_SCORE` float DEFAULT NULL,
  `HISTORIC_SCORE` float DEFAULT NULL,
  `QUALITY_SCORE` float DEFAULT NULL,
  `NEW_WIDTH_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_WIDTH_IN_POINTS` float DEFAULT NULL,
  `NEW_HEIGHT_IN_PERCENT` float DEFAULT NULL,
  `NEW_MAX_HEIGHT_IN_POINTS` float DEFAULT NULL,
  `ORIGINAL_WIDTH` float DEFAULT NULL,
  `ORIGINAL_WIDTH_UNIT` varchar(100) DEFAULT NULL,
  `ORIGINAL_HEIGHT` float DEFAULT NULL,
  `ORIGINAL_HEIGHT_UNIT` varchar(100) DEFAULT NULL,
  `CONTAINER_HIERARCHY_TYPE` varchar(1000) DEFAULT NULL,
  `ERROR_CODE1` varchar(1000) DEFAULT NULL,
  `ERROR_CODE2` varchar(1000) DEFAULT NULL,
  `STATUS` varchar(1000) DEFAULT NULL,
  `CLIENT` varchar(100) DEFAULT NULL,
  `SAMPLE` tinyint(1) DEFAULT NULL,
  `T_ID` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

EDIT:
Mysql Engine version- 8.0.17
200 insert statements per second
The select query are of the form:
-- 1.
select count(BOOKNAME)
from table
where timestamp between time1 and time2.

-- 2.
select count(BOOKNAME)
from table
where (timestamp between time1 and time2)
    and status="success";

-- 3.
select count(BOOKNAME)
from table
where (timestamp between time1 and time2)
    and (type like "%landscape%" )
    and status="success";

You can try creating one single fully covering index, based on the queries you provided, on the fields timestamp, type, status, bookname. This should be applicable to each of the three queries above, but knowing if it's ideal or the best index would be dependent on your table's data and testing it. As Andrew points out, depending on how large the timestamp ranges are, and more so meaning depending on how many rows the typical range returns, will affect the selectivity of indexing on that column. But after you create the index, you should be able to test and run an EXPLAIN and ANALYZE and see if it's using the index appropriately.
Note for a 200 million row table, it might take a little bit to create the index, and you might want to do it during a maintenance window, off-hours. Also you should look to use proper data types and clean up your table when possible as well, for example timestamp should be stored in a timestamp type column, and does the status column really need up to 1,000 characters when there's only 6 distinct values for status?...these minor things could also affect performance in minor ways, and definitely affect maintainability, but this is more of a sidenote.


Annotations for SSIS Forgive me if this is an inappropriate question for this forum (being DBA and all, I thought it may fit).
I have an SSIS package that I've recently upgraded and moved from BIDS to VS 2019.
It's a very straightforward process to create and write an annotation, however, there are a number of annotations on the flow diagram which I need to edit, but I simply cannot find an option to do this.  Does anyone know of a way, bar actually editing the XML (dtsx) file and manually using a text editor?
To Clarify...
This was all my fault for being a trendy developer and using a dark scheme on my VS install!  It turns out that you can't see the cursor as it's black on black.
Kudos to billinkc, below, for pointing out my stupidity!

You've answered with your options. Either click and edit each individual annotation or open with your favorite text editor and be prepared to write XML safe comments i.e. runs when sales_amount &gt; 30
Now that I have cracked open a 2017 version SSIS Package, the annotation XML is far friendlier for text editing than it was in the 2005/2008 era.
Given a package that looks like

That Inner Annotation > 30 is out in the "clear" down in the DTS:DesignTimeProperties in a CDATA field. Update all the Text attributes, save and reopen and you should be good.
    <AnnotationLayout
      Text="Inner Annotation &gt; 30"
      ParentId="Package\SEQC Placeholder"
      Size="138,60"
      Id="4b28de53-9db6-47c2-957c-21118893f6dc"
      TopLeft="34,25">
      <AnnotationLayout.FontInfo>
        <mssge:FontInfo>
          <mssge:FontInfo.TextDecorations>
            <av:TextDecorationCollection />
          </mssge:FontInfo.TextDecorations>
        </mssge:FontInfo>
      </AnnotationLayout.FontInfo>
    </AnnotationLayout>

Normally, I'd put a plug in for Biml as you can reverse engineer a package to a simplified XML markup, edit there and re-generate your SSIS package.
This is what the above package would look like (assuming I imported the SSIS Annotations which is not a default)
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="dba_288939" Language="None" ConstraintMode="LinearOnCompletion">
            <Annotations>
                <Annotation Tag="Varigence.Biml.SsisImporter.ImportedFromPackage">dba_288939</Annotation>
                <Annotation Tag="Varigence.Biml.SsisImporter.OriginalName">dba_288939</Annotation>
            </Annotations>
            <Tasks>
                <Container Name="SEQC Placeholder" ConstraintMode="LinearOnCompletion">
                    <Annotations>
                        <Annotation AnnotationType="Description">Sequence Container</Annotation>
                    </Annotations>
                    <Tasks>
                        <Dataflow Name="Data Flow Task">
                            <Annotations>
                                <Annotation AnnotationType="Description">Data Flow Task</Annotation>
                            </Annotations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

However, the emitted SSIS package doesn't have annotations in it. I'll reach out to the fine folks as Varigence and see what I'm doing wrong in the package generation phase.
Visual Studio 2019 annotation edit experience


How can I pass a string as where clause to a function? How can I convert this:

CREATE OR REPLACE FUNCTION getFooData()
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where x = 3;
END
$BODY$
LANGUAGE plpgsql VOLATILE

into this:

CREATE OR REPLACE FUNCTION getFooData( IN criteria text )
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where $1; -- > I CAN'T DO THIS
END
$BODY$
LANGUAGE plpgsql VOLATILE

I need to create a flexible query criteria in my function or else I'll be in a nightmare to create several functions to cover all foo fields combinations and pass only the values thru parameter. The problem is in some situations I may use foo.x, foo.y and foo.z in other I may use foo.h, foo.x. I may use any fields in search criteria in any combinations.

EDIT: Following the Horse's answer/comment, this is my final version of my function:

CREATE OR REPLACE FUNCTION public.pointscanner(IN routegeometry text, IN criteria text)
  RETURNS TABLE(way geometry, name text, distance double precision, tags hstore, operator text, admin_level text, z_order integer) AS
$BODY$
DECLARE
    geomRoute geometry;
    routeBB box2d;
    sql text;
BEGIN
    geomRoute = ST_GeomFromText( $1 ,4326);

    routeBB := ST_Extent(geomRoute);

    sql := 'SELECT pt.way, pt.name, ST_Distance( $1, ST_Transform(pt.way,4326) ) * 111195 as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order '
     || ' FROM planet_osm_point pt where $2 && ST_Transform(pt.way,4326) and ' || criteria;

    RETURN QUERY EXECUTE sql USING geomRoute, routeBB;

END; $BODY$
  LANGUAGE plpgsql VOLATILE

Called as: select * from pointscanner(<first_arg>, 'x = 3') where second selection

Performance test: must I put the selecion criteria inside or leave it outside a function?

In this test I create a functio that select some records in a OpenStreetMap table. This is the heart of the function:

Function pointscanner():

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326)

The fact is: I must do a second selection (fine tunning) in the function result. Must I do this inside the function and pass the selecion parameters or I can do it outside the function?

Selection exemple: name is not null

Option 1: a where clause inside the function:

Change function to:

RETURN QUERY SELECT pt.way, pt.name, ST_Distance( geomRoute, ST_Transform(pt.way,4326) ) * 111195
as distance, pt.tags, pt.operator, pt.admin_level, pt.z_order
FROM planet_osm_point pt
where routeBB && ST_Transform(pt.way,4326) and pt.name is not null;

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=333 width=172)
    (actual time=1251.040..1251.792 rows=358 loops=1)"
"  Filter: (round((distance)::numeric, 5) < '500'::numeric)"
"Planning time: 0.074 ms"
"Execution time: 1251.867 ms"

Option 1: a where clause outside the function:

Leave the function untouched.

Call it as: select * from pointscanner() where round(distance::numeric, 5) < 500 and name is not null

Result:

"Function Scan on pointscanner  (cost=0.25..17.75 rows=332 width=172)
    (actual time=4654.345..4655.075 rows=358 loops=1)"
"  Filter: ((name IS NOT NULL) AND (round((distance)::numeric, 5) < '500'::numeric))"
"  Rows Removed by Filter: 238"
"Planning time: 0.114 ms"
"Execution time: 4655.161 ms"

So I conclude I MUST do the second selection criteria INSIDE the function.