texts: Moving `order by` Significantly Improves Execution Time

texts:
I provide a simply query that, I think, resembles my real query.

I'm using Oracle.

Given:

create table main_table(a NUMBER, b VARCHAR2(10))
create table rates_table(a NUMBER, rate NUMBER)
create table other_table(a NUMBER, c VARCHAR2(10))

and their data:

select * from main_table

1   foo
2   bar

select * from other_table

2  bar

select * from rates_table

2  42.2

The following query finds all a's in main_table where its b matches other_table's c, limiting to the first 1000 results. Then, it left joins rates_table, getting the rate from other_table:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
) o
left join rates_table rt on rt.a = o.a
order by o.a, o.b

Output:

2  bar 42.2

In my real query, which, again, resembles the above query, I noticed a significant (11 seconds to < 1 second) execution time when moving the order by to within the inner SELECT:

select o.a, o.b, rt.rate from (
  select mt.a, mt.b from main_table mt
  left join other_table ot on mt.a = ot.a
  where mt.b = ot.c
  and rownum < 1000
  order by mt.a, mt.b -- <---
) o
left join rates_table rt on rt.a = o.a

It, too, returns:

2  bar 42.2

Here's the first query's EXPLAIN PLAN:

And the second's:

In general, does the placement of order by matter, i.e. will the results differ depending on where I put this clause? I'm not asking about this particular query, since the data only has a few rows.

Why would the placement of order by, i.e. in my real query, result in such an improvement - 11 seconds to < 1 second?


texts: PostgreSQL: set default privileges on schemas themselves?

texts:
According to the PostgreSQL documentation on ALTER DEFAULT PRIVILEGES, default privileges can be set on tables, sequences, functions, types.

Is there any way to set default privileges on schemas themselves, so that the privileges specified in the GRANT clause are by default applied to any new schema in the database upon its creation?


texts: Optimize PostgreSQL for a lot of INSERTS and bytea updates

texts:
What we have (software):

PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf)
Windows 7 64 bit

Hardware:

Intel Core i7-3770 3.9 Ghz
32 Gb RAM
WDC WD10EZRX-00L4HBAta Drive (1000Gb, SATA III)

So, we have to load into DB aprox. 100.000.000 rows with bytea column, and more simple 500.000.000 rows (without LOBs). There are 2 varchar indexes on 1st table (with 13, 19 length) and 2 varchar indexes on 2nd table (18, 10 lengths).
There are also sequences for id generation for each table.

By now these operations are doing with 8 connections in parallel with 50 JDBC batch size. The picture below demonstrates system load: it is zero-load on postgresql processes. After 24 hours of loading we have loaded only 10.000.000 rows which is very slow result.

We are asking for help in tuning PostrgreSQL configuration in purposes of:

1) for ultra fast loading this amount of data, it is once-only operation, so it could be temporary configuration

2) for production mode for doing moderate number of SELECTs into these 2 tables by their indexes without join and without sorting.


texts: What's the best way to list groups of values that are contained in other groups

texts:
For example, starting with a table like this:

create table t as
select 'A' as x, level as y from dual connect by level<=5
union all
select 'B' as x, level+2 as y from dual connect by level<=5
union all
select 'C' as x, level as y from dual connect by level<=3
union all
select 'D' as x, level+2 as y from dual connect by level<=3;

alter table t add primary key (x, y);

select * from t;

X Y
- -
A 1
A 2
A 3
A 4
A 5
B 3
B 4
B 5
B 6
B 7
C 1
C 2
C 3
D 3
D 4
D 5

How do I get this:

SUBSET_X SUPERSET_X
-------- ----------
D        A
C        A
D        B

I'm posting my own effort as an answer but wondering if there is some other fancy way, perhaps with analytics or a set operator I don't know about

--edit

My test data unintentionally implies that the sets always consist of consecutive integers - unfortunately this is not true with my real data.


texts: How can I connect my other table using the mysqli_insert_id?

texts:
I've got a problem like this , I cannot get the id of the parent table .

table_name : transaction_tbl

- transaction_id
-file_name
-file_path
-description

table_name : transaction_details
-details_id
-transaction_id
- details

here is my code to insert :

 $sql = "INSERT INTO transaction_tbl (`file_name`,`file_path`,`description`)           VALUE('$file_name','$file_path','$description') " ;

                    $query = $conn->query($sql);
                    $transaction_id = mysqli_insert_id($conn);
                if ($query === True ){
                    $sql = "INSERT INTO transaction_details (`transaction_id`,`details`) VALUES ($transaction_id,$details) ";

                }else {
                    trigger_error('Wrong SQL: ' . $sql . 'Error: ' . $conn->error, E_USER_ERROR);
                }

Now I can insert in my transaction_tbl but not in transaction_details. what should I do? Can somebody help me?


texts: How to solve log file sync wait in oracle 12c

texts:
I have performance issue with Oracle instance.
There is some java process that makes massive inserting into database. And the top wait event is "log file sync". I tried to change some Oracle database parameters, but result still not satisfied users...
I'm newbie in Oracle, so please be patient :)

Settings that I changed:

MEMORY_TARGET = 34359738368
MEMORY_MAX_TARGET = 34359738368
OPTIMIZER_MODE = FIRST_ROWS
OPTIMIZER_USE_PENDING_STATISTICS = TRUE
COMMIT_WRITE = BATCH, NOWAIT
REDO LOG FILES = 3 x 1000MB
_use_single_log_writer = TRUE
commit_logging=batch
commit_wait=nowait

Things that I can't change (unfortunately):

RAID 5
Java code
count of commits
SQL code
Also I can't disable logging


texts: Oracle DB | Issue with agent and OMS connection

texts:
We performed a "P2V" migration to a local VMware host last night to get an old physical Oracle server over to a new virtual home (we covered all the licensing aspects, we're OK there).  Prior to starting the virtualization process we brought all services and instances offline.  Coming up on the virtual side went very smooth, with everything being operational expect an error we're seeing in Enterprise Manager.  The error we're seeing is:

  Agent is unable to communicate with the OMS. (REASON = Agent is
  Unreachable (REASON : Agent to OMS Communication is broken OMS
  platform services are not available)

We've done the normal troubleshooting, and running EMCTL STATUS AGENT returns:

  emctl status agent
  Oracle Enterprise Manager Cloud Control 13c Release 1 Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
  --------------------------------------------------------------- Agent Version          : 13.1.0.0.0 OMS Version            : 13.1.0.0.0
  Protocol Version       : 12.1.0.1.0 Agent Home             :
  /oracle/product/agent/agent_inst Agent Log Directory    :
  /oracle/product/agent/agent_inst/sysman/log Agent Binaries         :
  /oracle/product/agent/agent_13.1.0.0.0 Core JAR Location      :
  /oracle/product/agent/agent_13.1.0.0.0/jlib Agent Process ID       :
  32622 Parent Process ID      : 32476 Agent URL              :
  https://xxxx:3872/emd/main/ Local Agent URL in NAT :
  https://xxxx:3872/emd/main/ Repository URL         :
  https://xxxx:4903/empbs/upload Started at             : 2020-01-25
  09:52:01 Started by user        : oracle Operating System       :
  Linux version 3.0.101-0.46-default (amd64) Number of Targets      : 75
  Last Reload            : 2020-01-25 10:29:21 Last successful upload
  : 2020-01-25 10:59:32 Last attempted upload                        :
  2020-01-25 10:59:32 Total Megabytes of XML files uploaded so far :
  10.25 Number of XML files pending upload           : 0 Size of XML files pending upload(MB)         : 0 Available disk space on upload
  filesystem    : 46.30% Collection Status                            :
  Collections enabled Heartbeat Status                             : Ok
  Last attempted heartbeat to OMS              : 2020-01-25 10:59:25
  Last successful heartbeat to OMS             : 2020-01-25 10:59:25
  Next scheduled heartbeat to OMS              : 2020-01-25 11:00:25

We're at a loss here.  Any advice on how to fix this?  Thanks in advance.


texts:
Say I have a query like this:

select * from openquery ([linkedservername],'select * from [databasename].dbo.person')

Is there a way of omitting [databasename].dbo.? I have an enormous query I want to put inside OPENQUERY and appending [databasename].dbo.
to every table name will be a pain.


texts: How to take a remote SQL server database backup to local machine?

texts:
I've to take a SQL database backup from remote machine to local Linux machine and through command line. Hence executed following command -
sqlcmd -S xxx.xxx.xxx.xxx -U <username> -P <password> -Q "BACKUP DATABASE [TestUser] TO DISK='TestUser.bak';"

But this takes backup on machine where database server is installed. Is there any way I can get backup on machine where it is executed?
**NOTE: ** I can see many questions which asks same but are old ones and answer - either this is not supported or use of third party tools. Hence asking question again, in case has solution with latest stuff.


texts:
So I got a query with a whole bunch of joins and that returns ONE row, like this (just an example):

select t1.chickenId, t2.beefId, t3.fishId from Food f
join t1 on t1.id = f.id
join t2 on t2.id = f.id
join t3 on t3.id = f.id
where f = 'something'

A result would be like this for example:

chickenId | beefId | fishId
5          58         14

Now the part I don't know how to do... The reason for using the above query is because I want to use the result to find something in another table.

I have another table named "Extras" and I want the rows from that table that have the ids from any of the columns of the previous query. So if the id 5, 58 or 14 is in Extras table, I want those rows.

This following query is incorrect but I believe it can be used to demonstrate what I'm trying to achieve:

select * from Extras where extra = 'Potatoes' and foodId in (select t1.chickenId, t2.beefId, t3.fishId from Food f
    join t1 on t1.id = f.id
    join t2 on t2.id = f.id
    join t3 on t3.id = f.id
    where f = 'something')

Any idea how I can achieve this? I'm using Microsofts SQL Server 2008


texts: Having trouble with a query. DBMS Assignment

texts:
Working on an assignment for school, was able to figure out all but this one.

I need to display the employee name and employee identification number along with their HOD's name and identification number.
The table is structured like this:

(TABLE NAME): EMPLOYEES

EMPLOYEE_ID
EMPLOYEE_NAME
HOD (which is an EMPLOYEE_ID)


texts:
I want to cancel calling a source file on a mysql server which includes an enormous amount of data. It appears to be a sqldump that is broken into many small updates for many tables.

First I tried killing the process,

mysql> show processlist\G
mysql> kill ###;

This appears to only cancel a single query inside the source file; a new process pops up and the server continues to execute the source file.

Then I tried stopping the mysql service,

bash> sudo service mysql stop

But this only paused the problem; the server threw errors constantly until I started the service again, where it picked up where it left off again.

What are my options to fully cancel this query?


texts: Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

texts:
I am Working around MySQL error "Deadlock found when trying to get lock; try restarting transaction".

I am going to have to update a program to allow deadlocks. Is it possible that SELECT statement will produce deadlock errors? I know that it is only a read lock so multiple selects won't be a problem, but what if there is an INSERT, UPDATE or DELETE statement (with subqueries possible with joins) and a SELECT statement (possible with joins or subqueries)?

Is it possible that the error will be thrown on the SELECT instead of the INSERT, UPDATE or DELETE.

The story is here if you are curious.


texts:
For example let's say I have to create a table called Employee with columns EmployeeID, Name, DOB, Address, DepartmentID[FK].

So I create the table as follows with a primary key and a nonclustered index:

Table Name: Employee

Columns:

ID [int]                (Auto increment column) - This is the primary key of the table.
EmployeeID [char(10)]   This is the non-clustered index.
DOB [Date]              This column is added as a INCLUDED column to the non clustered index.
Name [varchar(100)]
Address [Varchar(max)]
DepartmentID [Char(10)]

I have created a clustered index with EmployeeID column and DOB has been included to the index considering possible query patterns like...

Show Name and DOB of all employees who has born after 1986
Show all details of employee holding the EmployeeID ='M123456789'
Show all details of all employees whose EmployeeID starts with 'M' and have born after year '1986'

Assuming this table may have large no of rows, could you let me know whether the above design would be optimum or should I do any changes?

Is it better to make the column EmployeeID the primary key without adding an ID column? (Performance wise and from application perspective)

NOTE: Please note that this is a hypothetical scenario only. I'm not going to create a database in this way. I just expecting your expert advices for my questions.


texts: xp_cmdshell only sends first character of string

texts:
I am trying to use xp_cmdshell to launch a python script with a list as its input but for some reason its only passing the first character of the string to command line. The result I get from the code below is "'D' is not recognized as an internal or external command, operable program or batch file.". If I add the START command before the path it just replaces 'D' with 'S'. This particular row I'm trying to test with concatenates to 241 characters so I'm not bumping up against the varchar limits for xp_cmdshell. Using MSSQL 2008R2 on Windows Server 2008R2.

DECLARE
    @inspection varchar

SELECT
    @inspection = 'D:/Python27/ArcGISx6410.1/python.exe D:/GIS-DATA/Scripts/whydrant_update_01.py [''' + COALESCE(FACILITYID, '') + ''',''' + COALESCE(MAKER, '') + ''',''' + COALESCE(HYDRCOND, '') + ''',''' + COALESCE(BASECOL, '') + ''',''' + COALESCE(CAPCOL, '') + ''',''' + COALESCE(STEAMSIZE, '') + ''',''' + COALESCE(DISSIZE, '') + ''',''' + COALESCE(CAST(FLOW AS varchar), '') + ''',''' + COALESCE(CAST(PRESSURE AS varchar), '') + ''',''' + COALESCE(VALVELOC, '') + ''',''' + COALESCE(ACTIVE, '') + ''',''' + COALESCE(OPERABLE, '') + ''',''' + COALESCE(OPERHARD, '') + ''',''' + COALESCE(CORRODED, '') + ''',''' + COALESCE(CLEARING, '') + ''',''' + COALESCE(PAINTING, '') + ''',''' + COALESCE(MARKER, '') + ''',''' + COALESCE(NOTE, '') + ''',''' + COALESCE(CAST(created_date AS varchar), '') + ''',''' + COALESCE(USER_, '') + ''']'
FROM sde.sde.a1288
WHERE FACILITYID = '2294'

EXEC xp_cmdshell @inspection


texts: How do I replicate SQL Server index INCLUDE and STATISTICS functionality on PostgreSQL?

texts:
I'm working on a project that must support two database engines; SQL Server and PostgreSQL.

We are using NHibernate as the ORM.

We are running into performance issues with certain queries. Using SQL Server tools we've come up with several new indexes and statistics that greatly improve performance on SQL Server.  However, I'm not certain how to implement the same indexes and statistics on PostgreSQL.

Two examples are:

    CREATE STATISTICS [perfStat_Answer_02] ON [dbo].[Answer]
    ([InclusionExpressionGroupId], [QuestionId], [AnswerId])

    CREATE NONCLUSTERED INDEX [perf_Answer_01] ON [dbo].[Answer]
    (
        [QuestionId] ASC
    )
    INCLUDE (
            [AnswerId],
            [InclusionExpressionGroupId],
            [AnswerConceptId],
            [Revision],
            [AnswerText],
            [AnswerOrder]
    )
    WITH (
            SORT_IN_TEMPDB = OFF
            , IGNORE_DUP_KEY = OFF
            , DROP_EXISTING = OFF
            , ONLINE = OFF)
    ON [PRIMARY]

What is the syntax for the INCLUDEd fields in PostgreSQL, if such a feature exists?

How do we add statistics?

Reading the PostgreSQL docs, I'm not convinced that either are supported.  However, I would like to know if there is any way to accomplish something similar.


texts: How to force oracle accept insufficient swap size?

texts:
I want to install Oracle 12c on Oracle Linux 6.10 with 5.7803GB swap size.

But one of prerequisites checked failed:

INFO: *********************************************
INFO: Swap Size: This is a prerequisite condition to test whether sufficient total swap space is available on the system.
INFO: Severity:IGNORABLE
INFO: OverallStatus:VERIFICATION_FAILED
INFO: -----------------------------------------------

INFO: ERROR: [Result.addErrorDescription:703]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: ERROR: [Result.addErrorDescription:714]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: ERROR: [Result.addErrorDescription:714]  PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]
INFO: INFO: [Task.perform:819]
TaskSwapSize:Swap Size[CHECK_SWAP_SIZE]:TASK_SUMMARY:FAILED:IGNORABLE:VERIFICATION_FAILED:Total time taken [58 Milliseconds]
          ERRORMSG(olhost): PRVF-7573 : Sufficient swap size is not available on node "olhost" [Required = 7.8167GB (8196368.0KB) ; Found = 5.7803GB (6061052.0KB)]

How to force oracle to use smaller swap size instead of required?


texts: SQL query to list all student users, add column to show who were logged in at least once

texts:
I have a query, which lists those users, which are students:

SELECT  *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE
ra.roleid = 5  /* is student */

I have a table with user ids, who were logged in at least once:

SELECT * FROM mdl_user_lastaccess

I need to list all student users, and have a new column, which shows if the user was logged in or not. Logged in: value 1, not logged in: value 0.

I came up with this query, but the loggedin column is always 1. The 0 values are missing:

SELECT  *, (SELECT IF ((SELECT count(*) as c FROM mdl_user as u, mdl_user_lastaccess as ula WHERE u.id = ula.userid), 1, 0)) as loggedin
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE
ra.roleid = 5

Any idea how to fix it?

WRONG SOLUTION:

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid
WHERE
ra.roleid = 5

ORDER BY loggedin DESC

Results 455 rows, which contains duplicates.

GOOD SOLUTION:

SELECT DISTINCT u.*, IF(ul.userid IS NULL, 0, 1) AS loggedin, ul.courseid
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
LEFT JOIN (
    SELECT ula.userid, ula.courseid
    FROM mdl_user_lastaccess ula
    GROUP BY ula.userid
) ul ON u.id = ul.userid
WHERE ra.roleid = 5
ORDER BY loggedin DESC

I had to use DISTINCT to ignore the duplicates.

FINAL GOOD SOLUTION:

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS loggedin, la.courseid
  FROM mdl_user                 AS u
  JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
  LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
 WHERE ra.roleid = 5           -- is student
 GROUP BY u.id                 -- to collapse all sessions for certain user
 ORDER BY loggedin DESC


texts:
I am looking for a solution to copy the entire database by using MYSQL query.

I am developing a system where admin can create b2c and b2b stores from backend, and for both types of websites, I have a single type of database structure and would like to copy that directly whenever a store will created. So I am looking for a direct query to achieve that instead of a PHP script (already done).


texts: How to gracefully handle MySQL deadlocks involving SAVEPOINT?

texts:
I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause

Here is a reproducible sample:

CREATE TABLE U ( -- user
    id int not null primary key,
    name varchar(222)
);

CREATE TABLE O ( -- object
    id int not null primary key,
    name varchar(222)
);

CREATE TABLE OU ( -- user <-> object
    id int not null auto_increment primary key,
    object_id int,
    user_id int,

    -- (object_id, user_id) should be UNIQUE (not enforced by a constraint)
    CONSTRAINT fk_object_id FOREIGN KEY (object_id) REFERENCES O(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES U(id) ON DELETE CASCADE
);

INSERT INTO U VALUES (1, 'foo'), (2, 'bar');

-- Start 2 concurrent transactions (A, B)
BEGIN; -- A
BEGIN; -- B
INSERT INTO O VALUES (1, 'a'); -- A
INSERT INTO O VALUES (2, 'b'); -- B
SAVEPOINT s_a; -- A
SAVEPOINT s_b; -- B
SELECT * FROM OU WHERE object_id = 1 FOR UPDATE; -- A
SELECT * FROM OU WHERE object_id = 2 FOR UPDATE; -- B
INSERT INTO OU(object_id, user_id) VALUES (1, 1); -- A
INSERT INTO OU(object_id, user_id) VALUES (2, 1); -- B

one of the transactions will fail with a Deadlock found when trying to get lock; try restarting transaction error.

The problem is that it rolls back the whole transaction without any way for me to gracefully handle the error.

In that example, the object 2 is lost. I would really like for it to still persist and handle the second insert a few seconds / minutes after another transaction.

The way the code is currently designed does not allow fine tuning of the transaction block (I cannot commit after the first insert then start another transaction)

Is there any way to tell MySQL to not rollback on that deadlock error, or to rollback specifically to the savepoint?

edit here is the innodb engine status

*** (1) TRANSACTION:
TRANSACTION 3727668996, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 20145497, OS thread handle 0x7f4fdba2b700, query id 15334836096 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.33 user1 update
INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655988, 1, 26399, NULL, '2017-04-20 19:04:21')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668996 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 3727668998, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 20145647, OS thread handle 0x7f5d6d253700, query id 15334836100 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.35 user1 update
INSERT INTO `Tablename` (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655991, 1, 26399, NULL, '2017-04-20 19:04:21')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 201 page no 1012 n bits 840 index `product_id` of table `dbname`.`Tablename` trx id 3727668998 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)


texts: SQL Server Full-Text Search: combining proximity term and thesaurus

texts:
Scenario

I am developing a full-text search over a products table.

The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code.

I succeed in querying the table using the CONTAINS predicate with NEAR terms:

/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */
SELECT myFields
FROM SEARCHABLE_PRODUCTS
WHERE CONTAINS(myIndexedField, @mySearchTerm)

Then I try to enhance the search, leveraging on the thesaurus feature in order to enable synonyms search. E.G. 'hard disk', 'hard disk drive', 'hdd'. So I have also configured
the thesaurus xml file, and I succeed in querying the table using the FORMSOF term.

    <expansion>
        hdd
        hard disk
        hard disc
        harddisk
    </expansion>

Issue

I do not succeed in combining the NEAR and the FORMSOF terms in the CONTAINS predicate:

/* @mySearchTerm is a generic proximity term such as '"hdd*"~"sata*""' */
SELECT myFields
FROM SEARCHABLE_PRODUCTS
WHERE CONTAINS(myIndexedField, ' FORMSOF (THESAURUS, @mySearchTerm) ')

A syntax error is returned. As to the MSDN reference, it seems that it is not possible. Is there any solution or workaround?


texts:
Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other

I am new in DBA and we are using PostgreSQL 9.6 and thinking to use PostgreSQL 10.0. We have regular data in tables along with that we have large spatial data (GIS related data having 'postgis' extension) on Windows Platform

We checked few replication tools in which 'Slony' tool is comes with default installation hence we want to use for replication.

we are checking this link for reference as well as documentation.

If anyone knows proper technical article including required steps then it would great!!


texts:
I have a pre-defined csv template.
The header row begins on row 5.

Prior to this I have a date value in cell B2.

How do I update this cell value in SSIS?


texts: What is the best way to split a string into integers and units?

texts:
I've got a table of medication prices, each with their own units.
I am trying to split the strength into four columns: Numerator_integer, Numerator_uom, denominator_integer and denominator_uom.
I'm able to split the fraction into a numerator and denominator with a little game of 'find the slash' like so:
2GM/50ML becomes 2GM and 50ML
            if object_id('tempdb..#example') is not null begin drop table #example end

    create table #example (cost decimal(10,4), size decimal(10,4), strength varchar(255))

    insert into #example (cost, size, strength)
    values (918.060,    210.000,    '0.05MG/ML'),
            (808.480,       50.000, '1GM/50ML'),
            (1619.190,   50.000,    '2GM/50ML'),
            (1941.960,    1.000,    '300MG'),
            (30005.560,    40.000,  '5MG/ML')

    ;with costs2 as (select cost, size, strength,
    case when strength like '%/%' then replace(left(strength, charindex('/', strength)),'/','')  else strength end as numerator,
    case when strength like '%/%' then right(strength, len(strength) - charindex('/', strength)) else '1' end as denominator
    from #example),

    costs as (select *, null as numerator_uom, null as denominator_uom from costs2)

    select * from costs

However I can't think of an elegant way to loop through the numerator and split out the numeric half from the varchar half.
Question: how would you split out the numeric and varchar halves of a string?


texts: Mysterious alwaysOn breakage on SQL Server 2012

texts:
I've just bumped into a new situation regarding alwaysOn availability. We have a setup with a large database (terabyte range) with one primary and one secondary replica. Geographically the servers are located a small distance apart. Log and full backups are taken from the secondary replica, and index maintenance is obviously run on primary. Sometimes these schedules overlap, if the full backup takes particularly long to complete.

For several years now, this setup has worked perfectly, until recently. Now we have had several nights where at a certain, specific time the log backups on secondary fail, giving an error on primary having a more recent backup than secondary (when there are no backups taken from primary at all). At the same point, the synchronization state is "synchronized and healthy", but the dm_hadr_database_replica_states system view shows that last_received_time is stuck at the same time as the failure of log backup on secondary, and the redo queue is ever growing. As a result, the log backup jobs fail and the log keeps increasing indefinitely. Once suspending and resuming the replication helped, but otherwise we've had to boot the entire secondary replica server (booting the service just got it stuck).

At first we thought it was a combination of some network error at a specific time, and some hitherto unknown bug in alwaysOn not resuming or reporting its state correctly. But then we reproduced this during the day: We were taking a full backup and reading a high volume of data for an ETL package, from the secondary replica, during which the log backups started failing. As we aborted the backup, after a while the replication caught up and started working again.

So the question: What can cause alwaysOn to behave this way? It seems extremely dangerous to me that a high availability feature can get into a state where the alarms don't work, because the synchronization state is on and supposedly healthy, while in reality it's not. And that it can't restore itself even during no additional load, but just gets stuck until rebooted.


texts: How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012

texts:
How I should escape the % character in params so my RAISERROR retun my message

declare @msg varchar(max)  = (SELECT ' Message with %  ' AS MSG)

if @msg is not null
begin
    RAISERROR (@msg ,16,1);
end

This will rise error with message

  Msg 2787, Level 16, State 1, Line 4
  Invalid format specification: '%  '.

For end users this message is unreadable.

My messages is generated from database and setting this message.

Way I do avoid getting wrong message is replacing

set @msg = REPLACE(@msg,'%','P')

But I still do not know how to add % sign


texts: How Do I Gain SysAdmin Access Without Having a SysAdmin Account?

texts:
Situation:
We recently had a consultant company build new Lync 2013 Servers in a mirror setup.  There are two security principles set, SA (disabled), and a AD group that is only associated with the Public server role.

  Setup:
  - 6 x Windows Server 2012 R2 Standard 64-bit
  - 4 x SQL Server 2012 Standard w/SP1 64-bit (11.0.3128) -
  - 2 x SQL Server Express 2012 64-bit(Witness)
  - All servers are VMware VMs

Questions:
 - Is there a Jedi trick to gain SysAdmin access or will I have to contact our consultant?
 - How does this happen?  I thought there was some sort of failsafe to
   keep this from happening.


texts:
One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached its maximum allowable range for a value i.e. 2147483647 (the column by default is signed). Now the auto_increment detects the maximum value, but it doesnt have a mechanism to report any error or warnings related to that, instead as noticed later, it inserted new records with the same id value i.e. 2147483647, for almost 1.77Million records.

Following this, updates queries ran updating any row with id 2147483647, unfortunately there were 1.7M of them, this caused all those statement to be logged in binary logs in "Row" format (current binlog format is mixed, however mysql will choose "row" based for Update statements). This instantly filled up the disks containing binary logs (every update changed 1.7M rows). And the slaves were unable to cope up with this much data, and that filled up the relay logs volume on them too. This caused us to reset the slaves with a plan to re-build them again in future (Once master is fixed).

We have changed the id column type from INT to BIGINT to allow a larger range for the integer values. However, the auto_increment behavior made us to execute the Alter again to initialise it to a proper start point i.e. 2147483648. As per the mysql documentation the auto_increment will get the maximum value currently in the column and then start with that value + 1. However, when we tested the same behavior with a test table, we found that auto_increment will start with value as follows :

start value = (current max value for the column) + (number of rows in the table) + 1

This unusual behavior could have resulted in large gaps in auto_increment, which would not be beneficial to client (especially with any queries where they use a range condition based on id column).


texts:
I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user.

But no matter what I do it won't change. I have added a value of 1G both in /etc/my.cnf
and also in /etc/mysql/my.cnf and restarted the mysql service. It doesn't seem to update.

My mysql keeps on stopping and when i run mysqltuner & tuning-primer.sh all point to the fact I have to increase the innodb_buffer_pool_size but it simply won't increase.

Is there any other way to enforce this?


texts:
I'm going to be moving a database from one server to another. Right now the name makes no sense and I'd like to change it. I found a answer here that makes me think it might be relatively simple. My question is three-fold.

1: Is there a reason I shouldn't rename the database?
2: Would the renaming be as simple as

RESTORE DATABASE [NewDatabaseName]
FROM  DISK = N'C:\OldDatabaseName.bak'
WITH  FILE = 1,
   MOVE N'OldDatabaseName' TO N'C:\NewDatabaseName.mdf',
   MOVE N'OldDatabaseName' TO N'C:\NewDatabaseName_1.ldf',
   NOUNLOAD,
   STATS = 10
GO

3: What's this "NOUNLOAD" parameter? I can't find any reference to it in the searching I've done.


texts: Is updating many columns in a MERGE statement bad for performance?

texts:
I have a merge statement that either needs to insert about 65 rows worth of data, or update rows. At the moment I'm updating all columns when a match is found. But I don't need to.

Would there be a significant performance increase if i only updated a few columns on WHEN MATCHED THEN UPDATE vs updating all columns?


texts: I understand that as of the 2016 versions it is now possible to run SSRS and Power BI on the same server. I would like to know if anyone is currently running this combination and what are the pros and cons of doing so.


texts:
My database design skills are a bit rusty so I was hoping to get some help from y'all =)

I have a 'user' table that contains all the typical fields (id,name,email,etc.). Users can have many 'invitations'. (many to one) Each 'invitation' has a single owner (user). However, a single 'invitation' can have many 'invitees'.

My confusion is this: If users can have many invitations and invitations can only have one owner (user) but that single invitation can contain many invitees (these are users) how should this be modeled? Do I need a new table?? ('invite')

I hope my question makes sense.


texts: how to correctly return values from a function

texts:
I would like to create a function that queries a user table by passing a username and a password, the function is supposed to return 'Y' if the username / password are correct and 'N' if they're not.

the return values is initialized to 'N' and i check if the function found a match using the SQL%FOUND to assign a 'Y' to teh return variable.

i get the following error: 06503. 00000 -  "PL/SQL: Function returned without value"

CREATE OR REPLACE FUNCTION VERIFY
                     ( userName IN VARCHAR2,
                       userPassword VARCHAR2
                     )
                  RETURN VARCHAR2
AS
returnVar VARCHAR2(1):='N';
userID number;
zip number;
BEGIN
   SELECT userId,SUBSTR(ZIPCODE, 1,3)
    INTO userID,zip
FROM users
WHERE username = userName AND PASSWORD = userPassword;
 if SQL%FOUND then
   returnVar:='Y';
 end if;
return returnVar;
EXCEPTION
     WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END VERIFY;

NOTE: also in case you are wondering why i select the userID and the zip: when the function works fine and tested, i will include it into a package inorder to make the userID and Zip Code available globally, this is a requirement of the exercise i am working on.


texts:
I have setup my replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.

When I start my slave and check for logs, it says

2016-03-17 04:03:02 UTC LOG:  shutting down
2016-03-17 04:03:02 UTC LOG:  database system is shut down
2016-03-17 04:03:03 UTC LOG:  database system was shut down in recovery at 2016-03-17 04:03:02 UTC
2016-03-17 04:03:03 UTC LOG:  entering standby mode
2016-03-17 04:03:03 UTC LOG:  redo starts at 2D/57000210
2016-03-17 04:03:03 UTC LOG:  consistent recovery state reached at 2D/57014BE8
2016-03-17 04:03:03 UTC LOG:  invalid record length at 2D/57014BE8
2016-03-17 04:03:03 UTC LOG:  database system is ready to accept read only connections
2016-03-17 04:03:03 UTC LOG:  started streaming WAL from primary at 2D/57000000 on timeline 1
2016-03-17 04:03:03 UTC LOG:  incomplete startup packet

My pg_hba.conf entry is as follows:

 host    replication     replication     10.50.80.12/32          trust

My recovery.conf is as follows:

standby_mode = 'on'
primary_conninfo = 'host=10.50.80.11 port=5432 user=replication password=replication'

But when I do,

SELECT *
FROM pg_stat_replication;

nothing is shown.

Please help if I am missing something.

Also, do I have to create the databases in slave as in master?


texts: deadlock when transaction is waiting for itself

texts:
Sypnosis: percona-server 5.7.12, sometimes deadlocking

Am I right interpreting this output in a way that transaction 2 is waiting on itself ? And, if yes - why ?

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-04-18 05:34:00 0x7f2ae0b05700
*** (1) TRANSACTION:
TRANSACTION 444786051, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5486539, OS thread handle 139822142461696, query id 307909819 192.168.10.49 enaza update
INSERT INTO `enz_external_user` SET `partner` = 180,`external_id` = 'XXXXXXXXXXXXXXXXX',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'XXXXXXXXXXXXXXXXX',`parent` = 13953989,`ts` = '2019-04-18 05:34:00' ON DUPLICATE KEY UPDATE `partner` = 180,`external_id` = 'XXXXXXXXXXXXXXXXX',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'XXXXXXXXXXXXXXXXX',`parent` = 13953989,`ts` = '2019-04-18 05:34:00'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786051 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 444786049, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5486536, OS thread handle 139822135006976, query id 307909810 192.168.10.49 enaza update
INSERT INTO `enz_external_user` SET `partner` = 180,`external_id` = 'ZZZZZZZZZZZZ',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'ZZZZZZZZZZZZ',`parent` = 13953988,`ts` = '2019-04-18 05:34:00' ON DUPLICATE KEY UPDATE `partner` = 180,`external_id` = 'ZZZZZZZZZZZZZZ',`pay_system` = 7,`move_type` = 0,`token` = NULL,`service` = NULL,`oauth_id` = NULL,`geo_id` = NULL,`state` = 4281,`agreement_number` = 'ZZZZZZZZZZZZZZ',`parent` = 13953988,`ts` = '2019-04-18 05:34:00'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786049 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11226 page no 80703 n bits 344 index external_id of table `enz`.`enz_external_user` trx id 444786049 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)


texts: SP2-0584: EXIT variable was non-numeric error

texts:
I'm using following PLSQL loop to delete a large number of records from my table. However it is giving me an error that loop exit condition is not correct.

FOR i IN 1..100
LOOP
  DELETE
  FROM mytable
  WHERE date <= (SYSDATE - 30)
  AND ROWNUM < 5000;
EXIT WHEN (SELECT COUNT(*)
  FROM mytable
  WHERE date <= (SYSDATE - 30)) = 0;
  COMMIT;
END LOOP;

What is the problem here?


texts:
I setup a two node postgresql 10 cluster to test replication. Following this guide

I was able to get replication running correctly. However, I want to test the process of the slave to master. When I stop the master node, and run pg_ctl promote -D on the slave, I get:

  pg_ctl: server did not promote in time

I've looked online and can't find anything related to being unable to promote in time. After running the command and receiving the error, I have to manually start postgresql and it is still running in a read only state.

Postgresql is running on Ubuntu 18.04.


texts: Oracle how do I find out the max number of concurrent sessions?

texts:
I am creating an application with ASP.Net and I wanted to figure out how many users concurrently connect to our database. (the existing app is a Windows app and there is no way to get this information).

I am not looking for live data but what the max # of users had been at any point. If there is a way to specify a date range than that would be preferred.

Thank you

Right now we are using 11.x but will migrate to 12 (or higher)


texts: Second normal form does not produce the desired result

texts:
I have a problem with 2NF and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.

Let's have a table with 5 fields.

Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}

Dependencies are this way:

StudentName, SubjectCode, #Exam -> Grade

SubjectCode -> SubjectName

SubjectName -> SubjectCode

Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.

Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade

According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

Gradings = {StudentName, Subject Code, #Exam, Grade}

Subjects = {Subject Code, SubjectName}

But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.

I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".

What am I doing wrong? I feel like I am beating my head against a wall.

Thank you in advance.


texts:
I have a table with a column that stores ID values (integers) that require a padding with 0s (zeroes) in the middle. I can pad left or right but, how do I pad in the middle?

For example:

4001 = 40001
4123 = 40123
4000 = 40000

So, any number 4XXX becomes 40XXX.

Comment interactions

Question:

  To clarify, once the values of the relevant column get the padding, are they meant to:

  be stored permanently or
  to be temporarily computed only (e.g., the padding serves display purposes only)?

Response:

They should be stored permanently.


texts: A way to reference the ID in a multi insert transaction? (postgres)

texts:
Assuming table "entity.eid" is auto incrementing, I want to be able to reference the autoincrement value assigned later in the same transaction. The way I have been doing this is by doing multiple transactions which I think is not optimal.

START TRANSACTION;
INSERT INTO entity ...;
INSERT INTO t2 (eid, ...) VALUES (?NEW EID REF HERE?, ...), (...), (...);
COMMIT;


texts:
I have a table with a field that has a very large variance on a particular field that is often searched. The number of records associated with any value in the field highly differs.

My estimated number of rows are always off, before refreshing statistics it's highly overestimated after refreshing it's highly underestimated. What can I do to improve it?

I update the statistics with full scan. The code is usually called from within a procedure but now I tested it outside.


texts: What is the Always On Sync sync frequency?

texts: On an Always On Availability group, I understand an async secondary replica asks the primary for the blocks to be shipped. However, I would like to know how frequent is this task (to know the possible time frame of data loss) and to know if this frequency can be altered (to minimize the data the time frame of data loss).


texts: High Latch Wait time with Logon trigger enabled while full backups are running

texts:
I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives.

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64)   Oct 31 2020 02:43:57   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0  (Build 17763: )

All servers have the same hardware but two of my servers are installed on Windows Server 2019 as the others are on Windows Server 2012.
I have the same logon trigger enabled on all my databases. This logon trigger runs fine most of the time. But, on one of my servers that is on Windows Server 2019, the logon trigger fails while full backups are executing. (Note that the other server that is on Windows Server 2019 is new and still only has small databases on it so I can't really be sure it will run fine when we put bigger databases on it.)
When the full backups starts, latch wait time goes high like crazy, as it is normally around 0 (on SSD drives). This is the only server that has this problem with latch wait time during full backups. (all other stats are fine, CPU is fine, ect.)

And while this happens, my logs are full of the errer

Logon failed for login 'login' due to trigger execution

After investigation, I've found out that if I disable the logon trigger on this server, the full backups are able to execute fine, with a small occasional peak to latch time, but stays fine most of the time.

My questions are:

Could it be possible that the OS (Windows Server 2019 vs Windows Server 2012) makes a difference as my only server that has a problem is on Windows Server 2019?
Why would the logon trigger affect the full backups that much?

Right now my only solution is to disable the logon trigger on that server, as clients cannot connect on the server while full backups are taken, since logon trigger execution fails. But we use the logon trigger for security, to log people who login on the servers outside of the application.
Here is the definition of my logon trigger
CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
BEGIN
  INSERT INTO [master].[dbo].[TRACETABLE2]
   --the auditing snippet below works fine in a
  --login trigger,
  --database trigger
  --or any stored procedure.
  SELECT
    getdate()                                    AS EventDate,
    HOST_NAME()                                  AS HostName,
    SUSER_SNAME()                                AS sUserName
    WHERE convert(nvarchar(max),ConnectionProperty('local_net_address')) not like '192.168.0%'

  END
GO

The TRACETABLE2 table on master permission has been given to PUBLIC.
CREATE TABLE [dbo].[TRACETABLE2](
    [EVENTDATE] [datetime] NOT NULL,
    [HOSTNAME] [nvarchar](128) NULL,
    [USERNAME] [nvarchar](128) NULL
) ON [PRIMARY]
GO

GO
GRANT INSERT ON [master].[dbo].[TRACETABLE2] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections] TO PUBLIC

Collation is Latin1_General_CI_AS on all servers.
Here are the latches from the server that is having troubles.

Here is the Cost threshold for parallelism.

I forgot to mention all my backups are encrypted with a server certificate in AES_256

I did some tests based on the comment of rois and simplifying the trigger to this produces the same problem.
CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
   BEGIN
     DECLARE @i INT
  END
GO

Update 2021-03-04 : Microsoft was able to reproduce the problem! Waiting for news from them. Will keep you updated.

Update 2021-05-07 : Okay so after more than two months of troubleshooting with Microsoft, they've figured that I run out of worker threads. But as to why it happens with the logon trigger on servers on Windows Server 2019 and why its not happening on Windows Server 2012, no clue. (The same waits happen but to a lesser extent on Windows Server 2012.)
The final conclusion was that the logon trigger has a high cost on the server, and that I can achieve what I need to do with SQL Audit, which has a less higher cost on the server. So I just stopped using logon triggers and switched to SQL Audit instead.


texts: How do I properly copy BIN_logs to a another mysql server for testing an "undo"?

texts:
About a week ago a user, meaning to delete one category from an app database, deleted all/most of them thru the web app that runs the database. I thought we did not have backups so we revived an old server that had been decommissioned about a year ago and got the data from that table and injected it into the table on the current system. This worked ... ok. A few errors from not having some categories that apparently created after migrating from old server to new server. Today I find the binary logs. I did not think there were any but

"mysql> SHOW BINARY LOGS;"

and

"mysql> SHOW MASTER STATUS;"

show that we have active bin logs. I have never done anything with bin logs but I'm hoping to use them to undo this mess. I am not sure how do this. I would like to copy the bin logs to a test server where I already have a copy of the database running. Do I just scp bin_log username@testserver:/ or is there some util I need to use? Any and all help is appreciated. Also, if you know of any awesome "How-to's" on doing this, please link them in. Both new and old server are running MySQL 5.0.51b. The new test server is running MariaDB 5.5.60. New server is running RHEL 6.10 and test server is running RHEL 7.6. Thank you for your time and attention.


texts:
I am facing an issue with the mongodb 4.0. I have setup mongodb on remote server and want to connect it with robo3t. But getting the below error.

And here is my mongodb.conf file

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# Where and how to store data.
storage:
  dbPath: /var/lib/mongodb
  journal:
    enabled: true

#  engine:
#  mmapv1:
#  wiredTiger:

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# network interfaces
net:
  port: 27017
  bindIp: 0.0.0.0

# how the process runs
processManagement:
  timeZoneInfo: /usr/share/zoneinfo

#security:
 #authorization: "enabled"

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options:

#auditLog:

#snmp:

Mongodb version --> 4.0
Os --> ubuntu 16.04

Update-->


texts: mysql 8 ndbcluster distributed privileges

texts:
I've setup a mysql 8 ndbcluster but cannot get the distributed privileges working.

following how to setup distributed privileges using NDB_STORED_USER (https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-privilege-distribution.html), i've verified the following:

SHOW ENGINES lists the value of "DEFAULT" for the Support column on the ndbcluster (Engine) row.
Both SQL nodes have "default-storage-engine=ndbcluster" in their my.cnf
I created a user on SQL node 1 and then added them to NDB_STORED_USER.

mysql> GRANT NDB_STORED_USER ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.26 sec)

I verified I could see the respective GRANTS in ndb_sql_metadata from BOTH SQL Nodes

type    name    seq note    sql_ddl_text
11  "'admin'@'%'"   0   2   "CREATE USER 'admin'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$MGBmokOj
                                m!<=P9q7BhmH0ANKg1Fs3JGEv.ITwLdXa7KC0n2GOogn6Xc/' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT"
12  "'admin'@'%'"   1   [NULL]  "GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,NDB_STORED_USER,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`%`"
12  "'admin'@'%'"   0   [NULL]  "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%`"
3 rows returned```

I've restarted SQL Node 2, but I still do not see the new user (admin)

    mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)```

Any other suggestions? this is my first time setting up a mysql cluster.


texts:
I have installed SQL Server using Failover Clustering on 2 nodes, running Windows Server 2012 R2 Enterprise Edition and SQL Server 2008R2 Standard SP1.  Shared storage is provided by an iSCSI NAS device.

If I move the services to node 1, then shutdown node 2, SQL Server continues to run, as expected, on node 1.  However, if I then shutdown node 1, and restart it, SQL Server fails to startup with the following error:

  The resource has failed to come online due to the failure of one or more provider resources.

Why can't I start SQL Server?


texts: What is the performance cost of enabling "log_lock_waits" on Postgres?

texts:
I am trying to understand what is the performance cost of enabling log_lock_waits on Postgres.

As per this documentation, while log_lock_waits = on:

  Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off. Only superusers can change this setting.

But it doesn't say how much it hinders performance and I can't find it anywhere else.


texts: Get 10th and 90th percentile by customer

texts:
I have a table which contains customers and scores (based on different factors, irrelevant in this case; a customer can have multiple scores), which looks like this:

customer_id | score | score_giver_id
====================================
          1 | 100   | 1
          1 | 102   | 1
          1 | 101   | 1
          1 | 140   | 1
          2 | 131   | 3
          1 | 44    | 1
          3 | 223   | 1
          3 | 1     | 2
          3 | 201   | 1
          3 | 211   | 1
          3 | 231   | 1
          3 | 243   | 1

The score_giver_id is irrelevant, but I'd still like to fetch it.

In the example above, when getting the 50th percentile, grouped by customer_id, the result should be (I picked the 50th percentile in this example, because it illustrates what I want to do better):

customer_id | score | score_giver_id
====================================
          1 | 101   | 1
          2 | 131   | 3
          3 | 223   | 1

I used the method described here.

I need to get the value that is at the 10th percentile, respectively at the 90th percentile in PostgreSQL. I've seen that since 9.4 there is an ntile function, but I don't really understand how it works, what it does, and if it helps me.

I've found a nice snippet for MySQL, which works (even though there are some caveats), but I'd like to use built-in functions if available (for MySQL there are none, hence the snippet).


texts:
I have Native SSRS configured on a 2012 instance (11.0.2100). I have two reports with images that act as buttons to toggle between the chart report and the detail report. I want to configure the action URLs to dynamically pass in parameters when switching between the reports, so that the defaults (derived from a query) are not used when switching views, the manually inputted params would persist.

I have tried the below versions of my URL and, in all cases, the default (query driven) parameters are used to run the report, not the passed in parameters.

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&rs:StartDate=2015-10-01&rs:EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&rs:Command=Render&StartDate=2015-10-01&EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=2015-10-01&EndDate=2015-10-31

http://internalreporting/ReportsManager/Pages/Report.aspx?ItemPath=%2fFinance%2fDischarge+Totals+By+Day+Less+New+Borns+-+Chart&StartDate=10/1/2015&EndDate=10/31/2015

Any ideas on where I'm formatting my URLs incorrectly?


texts: What is the corresponding MySQL TimeStamp in MS SQL Server?

texts: I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but  MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?


texts: Remote to a remote oracle server and get running instances?

texts:
I need to make a shell script that takes a list of servers and and check the running instances then run sql script in each instance!

now I do not want it to ask me for a password!

it's redhat and centos servers, I can not install any additional packages like sshpass, ...

any ideas?


texts: Why can a non-clustered index not be dropped using online=on option

texts:
When attempting to drop a non-clustered index using the online option such as DROP INDEX [IX_MYINDEX] ON [dbo].[myTable] WITH ( ONLINE = ON ), I receive the following error message.

Msg 3745, Level 16, State 1, Line 16
Only a clustered index can be dropped online.

The SQL Server documentation clearly states:

  The ONLINE option can only be specified when you drop clustered
  indexes.

https://msdn.microsoft.com/en-us/library/ms176118.aspx

But can someone please explain to me why this is the case?  In my experience, you would be much more likely to drop a non-clustered index than a clustered index, since the clustered index in most cases is also your primary key.


texts:
I have quite large and rather important DB to backup.

First off, whats best way to do it?

Secondly, easiest way I know is to export it through phpmyadmin, but, i'm worried about options, although I'm quite sure this "Add DROP DATABASE" will not drop actual databases, I'm interested what are these options for? (see pic)

Thanks.


texts: How can I restructure a table of points with repeating lengthy text ids?

texts:
I have a dataset that looks like this:

4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,82,49.226215,9.23175,244,1531840519
4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,86,49.22584,9.229898,260,1531840525
4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,82,49.225726,9.22834,263,1531840530
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,84,49.456019,11.026769,123,1531840479
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,82,49.455454,11.02813,122,1531840484
ff9d6b955f214ff0@@@599e1d04-7fc4-4ce4-8c92-22e9d7ac6596,75,49.454935,11.029419,121,1531840489

Multiple tracks of cars of a car-sharing service. The first entry is a random ID that marks one rental. (I guess something like hash(user, car, RNG) ). Every row consists of

ID, speed, lat, lng, orientation (deg), epoch

...but the ID column takes up at least half of the storage. (Don't ask me why this format was chosen).

I want to keep the IDs, but I'm not sure how to do it. (In PostgreSQL with Django)

My idea is to use one table that only contains the ID strings and the second table that contains all the other information and a foreign key on the first table.

I just wonder if there is already a feature for this problem directly within PostgreSQL as I can imagine that multiple repetitions of a string in a column is a common problem.

Update:

I worked my way through the full data set, and found only 439252 unique ID Strings.
BigInt + @@@ + uuid: Thanks for pointing that out, however, I will not be able to exploit it as the string-format is not fixed throughout the whole data dump. (I had a manual look at the first thousand or so entries and they use it, but when I tried to implement the approach, I found other formats also.)


texts: LOCK error after creating a clustered index

texts:
I just created a clustered index on a 1MM row table (page and row locking turned on). I now try to run queries and I get this error:

  "The instance of the SQL Server Database Engine cannot obtain a LOCK
  resource at this time. Rerun your statement when there are fewer
  active users. Ask the database administrator to check the lock and
  memory configuration for this instance, or to check for long-running
  transactions."

I will need to adjust the page/ row locking settings later, but in the meantime I need to drop the index because I have users querying the table. I tried to drop it, but I received the same error. Any recommendations to drop the index for good? Thank you.

EDIT 1:
Based on Remus answer below, I ran the following script to enable dynamic allocation of memory for locks.

EXEC sp_CONFIGURE 'show advanced options', 1 RECONFIGURE
EXEC sp_configure 'locks', 0 RECONFIGURE

Then, I restarted the server.


texts:
I'm new to Oracle and I've noticed that pagination queries take the format.

select *
from ( select /*+ FIRST_ROWS(n) */
         a.*, ROWNUM rnum
       from
         ( your_query_goes_here with filter and order by ) a
       where
         ROWNUM <= :MAX_ROW_TO_FETCH )
where
  rnum  >= :MIN_ROW_TO_FETCH;

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Why is there an extra outer select to split the min and max rownum where clause? Would this be just as performant and correct?

select /*+ FIRST_ROWS(n) */
  *
from
  ( your_query_goes_here with filter and order by )
where
  ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

Shouldn't ROWNUM be already be correct since the inner query filters and orders the values?


texts: How to stabilize performance on frequently updated table in PostgreSQL

texts:
I'm trying to find-out a way how to prevent performance degradation in my PostgreSQL database.

The table is updated every minute with 100,000 - 200,000 INSERTs. Every 5 minutes the table is updated with the same scale of aggregated UPDATE and a number of DELETEs.  Aggregations and DELETEs are done in a way to keep the size of the table around 10 million records.

Apparently updates are too frequent so AUTO VACUUM cannot be completed. I started to run VACUUM after each DELETE series. That improved behavior a lot.

I've a suspicion that periodic reindexing would help but haven't verified it yet. Actually it's very suspicious that PostgreSQL has a dedicated command for reindexing.

I have played with different fillfactor for the indexes, with no visible difference.

What else should I consider to make keep performance stable? Let's say that slow but stable over time is better than sometimes fast and sometimes very slow.

UPD: vacuum analyzed after each delete + reindex every 6 hours solved the issue.


texts: How to fold an event stream into a table?

texts:
I'm trying to experiment a pure-postgres event sourced projection, and I'd like to represent a stream of events as a table:
imagine this flow of events:
┌───────┬───────────────────────┬───────┬──────────────────────────────┐
│ order |        type           │ topic │           payload            │
├───────┼───────────────────────┼───────┼──────────────────────────────┤
│ 1     | user-registered       │ users │ {"name": "john"}             │
│ 2     | user-changed-password │ users │ {"name": "john", "activation_token": "1234"} │
│ 3     | user-registered       │ users │ {"name": "bob"}              │
│ 4     | user-banned           │ users │ {"name": "bob"}              │
└───────┴───────────────────────┴───────┴──────────────────────────────┘

I'd like to create a view of the current state of active users containing:
┌──────┬──────────────────┐
│ name │ activation_token │
├──────┼──────────────────┤
│ john │ 1234             │
└──────┴──────────────────┘

In this example, there is only 1 active user because the second user has been banned.
So my question is: how could I "fold" the events table to end up with the active_users table ?
I tried to use case expressions but it's so hard to merge records:
create view users as
    select case type
        when 'user-registered' then (payload->>'name', null, true)
        when 'user-changed-password' then (null, payload->>'activation_token', true)
        when 'user-banned' then (null, null, false)
    end from es.events where topic = 'users'
    order by "order" asc
;


texts:
This is my first post, so please be gentle :)

I have a couple of queries:

SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'Work Web'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'

SELECT COUNT(DISTINCT(h.USERID))
FROM MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where h.CUSTOM2 = 'OUTLOOK'
and h.ACTIONDATETIME >= GETUTCDATE()-30
and u.LOGIN = 'y'

Now I've been asked to pull the data for the last year, so I thought I would use a case statement, but cannot figure out how to incorporate the distinct user part into the sum case statement....so I get huge numbers, obviously

Select 'Date' = format(h.ACTIONDATETIME, 'MMM yyyy')
, sum(CASE WHEN h.CUSTOM2 = 'Work Web' THEN 1 ELSE 0 END) WorkWeb
, sum(CASE WHEN h.CUSTOM2 = 'OUTLOOK' THEN 1 ELSE 0 END) Outlook
, 'UserCount' = COUNT(DISTINCT(h.USERID))
From MHGROUP.USERHISTORY h with (NOLOCK)
JOIN MHGROUP.DOCUSERS u with (NOLOCK) on h.USERID = u.USERID
where u.LOGIN = 'y'
and Year(h.ACTIONDATETIME)>2017
Group By Format(h.ACTIONDATETIME,'MMM yyyy')

What is returning is the following, which is a sum. I am looking for a distinct count for each way.

Thanks for any assistance


texts: How do I pivot to summarize my entries by a specific area?

texts:
I have a map where every row represents a bird and a row with the attributes shown in the excerpt of the attribute table:

round     | area | date         | species     | behaviour
---------------------------------------------------------
1         | 20   | 2018-04-13   | woodpecker  | NULL
1         | 55   | 2018-04-14   | woodpecker  | NULL
1         | 21   | 2018-06-01   | robin       | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | robin       | NULL
2         | 55   | 2018-05-28   | chaffinch   | NULL
2         | 20   | 2018-05-29   | chaffinch   | NULL
3         | 21   | 2018-06-01   | robin       | breedig
3         | 55   | 2018-06-01   | blackbird   | NULL

Every study area was visited n-times (see column "round", where "round" is global. That means, that when we did not
find a single bird on a specific round, we have no entry for this area on this date but the round will increase next time the area is visited)

What I need is a table like that one below. I have no idea, how to get this done with SQL. What I need is something like:

*Start with lowest round
    get area and count the species for this area
    next area
next round*

The wanted table:

area  | round | date        | woodpecker | robin | blackbird | chaffinch | sum
--------------------------------------------------------------------------------
20    | 1     | 2018-04-13  | 1          | 0     | 0         | 0         | 1
55    | 1     | 2018-04-14  | 1          | 1     | 2         | 0         | 4
21    | 1     | 2018-04-01  | 0          | 1     | 0         | 0         | 1
20    | 2     | 2018-05-29  | 0          | 0     | 0         | 1         | 1
55    | 2     | 2018-05-28  | 0          | 0     | 0         | 1         | 1
21    | 3     | 2018-05-28  | 0          | 1     | 0         | 0         | 1
55    | 3     | 2018-06-01  | 0          | 0     | 1         | 0         | 1


texts:
What is the optimal number of worker tasks to specify in MySQL Workbench for a speedy migration?

The default is 2.

The MySQL server is running on the same Windows machine, installed with defaults.  It has 8 vCPUs and 61GB memory.


texts: TokuDB fails to load in MariaDB, even when transparent hugepages are disabled

texts:
Earlier, I upgraded my website server to Ubuntu 14.04 LTS. I had a very nice setup with MariaDB and the TokuDB engine. However, the setup was kind of messed up when TokuDB refused to load properly, causing WordPress to display the install page because it could not read the database tables! Agh! (Hopefully they are still there...)

I disabled transparent hugepages (because they were enabled after the upgrade), but TokuDB still refuses to load, even when I followed the directions on the MariaDB website:

MariaDB [(none)]> install soname 'ha_tokudb';
ERROR 1123 (HY000): Can't initialize function 'TokuDB'; Plugin initialization function failed.

What else could be causing TokuDB to not load?

UPDATE: I have fixed the issue. You can read about it here: https://mariadb.atlassian.net/browse/MDEV-6165


texts: Converting varbinary data to varchar in SQLServer leads to unexpected results

texts:
I need to fix some data conversion issue in SQL server 2008. I got some requirement change on storing the data. Without much care I converted existing varbinary data to varchar using CONVERT(NVARCHAR(max), @bytearraydata, 1).

The same conversion in C# is done using Encoding.Default.GetString and Encoding.Default.GetBytes methods. Encoding.Default.GetBytes(string) gets back the bytearray as it was earlier. But when I try to get back the byte array of string which I converted using CONVERT()  gives me a wrong result.

My work is to fetch byte array stored as string the database and convert it to byte array and finally render the content as PDF. Data going through encoding mechanism (while saving and while fetching) works fine for me. But when I try to fetch the data which was converted using CONVERT it is failing to generate PDF.

How can I resolve this problem?

Summary:

Byte array column has been changed to string.

Existing data conversion done using this function:

Convert(NVARCHAR(MAX), @bytearraydata, 1)

In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

Are Encoding and CONVERT not compatible?


texts: Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

texts:
I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue.

issue is: when I wanted to integrate OWASP WebGoat application with oracle XE 11g , I receive the above mentioned error.

connection URL in web.xml is:

jdbc:oracle:thin:@//localhost:1521/XE/${USER}
================================================================= listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)
============================================================= tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
=================================================================== java version:

java version "1.7.0_55"
Java(TM) SE Runtime Environment (build 1.7.0_55-b13)
Java HotSpot(TM) Client VM (build 24.55-b03, mixed mode, sharing)
=================================================================== output of the lsnrctl services:

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2014 16:34
:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=Amir-PC)(PORT=2403))
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
===================================================================== output of the tnsping xe :

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 09-MAY-2
014 16:35:19

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

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Amir-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)
could some one please help me resolve this issue? I have been struggling more than 3 days and not found any useful answer yet.


texts: Can I at least get MySQL errors to give line numbers in the correct query?

texts:
We all know MySQL errors are pretty unhelpful. I understand that due to query optimisation it would be in-principle very difficult to unwind errors to their exact location in the source text and that the designers didn't bother trying.

However I'm baffled by the fact that, when I do mysql -u foo < many_queries.sql, the error line numbers coughed out are not even within the correct query! There are often dozens of distinct, correct queries between the reported line and the actual problem.

I assume the parser logic is roughly like this

for each line in source
    line_number++
    if part of last query
        append current line to last_query
    else
        if execute last_query
            yay
        else
            report error
        clear last_query
        append current line to last_query
        last query_starts_at = line_number

It would seem trivial for the "report error" line to at least know where in the source the query begins and I simply cannot believe that this was too difficult an engineering problem since the complexity of query execution is completely black boxed.

At the moment I'm manually commenting-out thousands of lines at a time to binary-search the problem.

EDIT I've noticed that the error is often reported as being in the first of the INSERT statements (although the error is caused by a trigger or a stored procedure or function being called via one of the later INSERT or UPDATE statements).

There must be a better way to at least approximately locate errors. What is it?


texts: MySQL replication stopped for over 10 days, will replication work correctly when expire_logs_days=10?

texts:
I'm foggy on exactly how replication works.  Here's my scenario.

Today (April 6th) I realized that our replication slave had an error caused by a failed "CREATE USER" command on March 20.  Since then, no replication has happened.

This leaves a six day or so gap where expire_logs_days would have expired logs on the master, and the slave was not replicating.

I was able to skip the error and get replication going again. It all caught up according to SHOW SLAVE STATUS\G.

How can I check that things are replicated correctly, beyond "spot checks?"  For example, the number of posts on master/slave are identical, that's encouraging.

But when I run, for example, this query on each database, I do not get the same answers:

select count(*) from table where datestamp >= '2016-03-24 00:00:00' AND datestamp <= '2016-03-24 23:59:59';

Should I be concerned that data is missing/incorrect?  Is there a reason why the timestamps would be off between databases?

I'd prefer not to have to re-set up the replication, but am prepared to do so.

Thank you.


texts: By Mistake Deleted AlwaysOn Availability Database from live Production Server

texts:
Today by mistake deleted The AlwaysOn Availability Database from live server.
Let  I am explaining the Details.
In AlwaysON Availability Groups, there is 2 Availability Replica. That is

Primary Replica Server (That is SQLServer1)
Secondary Replica Server (That is SQLServer2)

In Primary Replica Server, there is 4 databases.

AlwaysOnTest
Database2
Database3
Database4

In Secondary Replica Server (SQLServer2) , I can't say exactly because that is another SQLserver .But frankly i would like to say that i don't have access of that server.
When i got the accessibility of primary server it's already configured by Third party Vendor.
As a DBA my task was to take backup of all databases in SQLServer1 . Also take backup of AlwaysONTest Database.
Where AlwaysOnTest  Database of Primary Replica Server(SQLServer1) is synchronize with Secondary Replica server (SQLServer2). This is only one database , which synchronize with secondary replica.
Today at 12:10 P.M at Qatar time by mistake AlwaysOn database deleted.
I have Full backup of Yesterday of Primary Server.
Note: The Backup Preference of Backup is Secondary Only.
After instance I have also taken full backup of primary replica.
Now i want to again configure AlwaysONTest Database with Secondary Replica.
Note: Only by mistake deleted AlwaysOn Availability database. The environment is as it is.
What i shall do . Any suggestion or help will be appreciated.


texts:
I have a MySQL 5.5 instance running on a RedHat distro.  I have edited my.cnf with the following setting:

wait_timeout=30

However, client connections are sleeping and never getting killed.  The server has been restarted with the new setting in place.

What am I not understanding?

my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
performance_schema=off
innodb_buffer_pool_size=64M

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

wait_timeout = 30

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 500
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M

expire_logs_days    = 10
max_binlog_size         = 100M

bind-address=192.168.0.11
interactive_timeout = 60
max_connections = 500

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[isamchk]
key_buffer      = 16M

EDIT

Interestingly per @RolandMySQLDBA comments:

It appears none of the cnf file settings are being considered.

and the proof:

[root@localhost ~]# locate my.cnf
/etc/my.cnf
/opt/rh/mysql55/root/etc/my.cnf


texts: Oracle remove and place (re-install) database on 10g

texts:
I need to put together a plan to execute some change scripts to a Oracle 10g database, which will add some values, change some values a drop a table and create a new table.

I have a step for creating a DMP of the original database before the changes and for running a script that will execute all the changes.
But I need a back-out plan if things go wrong.
I was wondering what is the bets step(s) to drop the database and import the old DMP file?

I know about the sqlplus command:

imp <touser>/<password>@<host_address>/<service> file=<filename>.dmp fromuser=<fromuser> touser=<touser> log=backup_import.log

But what is the best way to drop the existing database? Is it just

drop user <schema_user> cascade;

Thanks in advance.

K


texts: SQL Server bitfield/flags in int indexing

texts:
In a schema, an int is used as a bitfield, one of those bits is used in a frequently called SQL query (a bit marking entries to be new for processing).

The query (entity framework generated, got it from listening in/querying execution plan cache) is

SELECT TOP (1000)
[Extent1].[Id] AS [Id],   [Extent1].[Created] AS [Created],   [Extent1].[DataTypeId] AS [DataTypeId],
[Extent1].[CodePage] AS [CodePage],   [Extent1].[InMsgStateFlags] AS [InMsgStateFlags],   [Extent1].[ContentData] AS [ContentData],
[Extent1].[ContentHash] AS [ContentHash]
FROM [dbo].[tInput] AS [Extent1]
WHERE (([Extent1].[InMsgStateFlags]) & (@p__linq__0)) = @p__linq__1

Only relevant values in reality are 1 for both parameters (though others would be possible).

I tried

 -- add computed column
 alter table dbo.tInput add InMsgStateFlagNew as
 (InMsgStateFlags & 1)
 persisted

 -- add index, filtered index cannot be created on computed column..
 CREATE INDEX IX_InMsgStateFlag_New ON tInput (InMsgStateFlagNew);
 -- where InMsgStateFlagNew = 1;

Sadly, SQL Server (2008 R2) does not automatically recognize this and still scans the index (there is a "normal" index on InMsgStateFlags). Is there any way to make the C#-generated code (well, the statement given above) recognize the index without having to rewrite the C# code?

(Note: I would for the rewrite-case directly query the computed column, probably making it a bit type, and leaving the generic functionality in place and handling the fact that both values are 1 in the special case of going to the computed column. If possible, I would like to avoid that, in order not to introduce breaking changes between code and DB schema.)


texts: SQL 2008: agent job error in opening excel files using DTS package

texts:
I have a SQL 2008 server (SQL2008S1) with Excel installed on it. I had to migrate databases and DTS packages from a SQL 2000 server (SQL2000S2) to this server. 1 DTS package is trying to access an Excel file on a remote server like \\SQL2000S2\C$\directory\someExcelFile.xls. The SQL server and agent on SQL2008S1 are both running under same service account (Domain\SQL2008S1_service) and has full permissions on \\SQL2000S2\C$\directory. I even granted Domain\SQL2008S1$ (machine name) full access to \\SQL2000S2\C$\directory\

When I open the DTS package on SQL2008S1 and execute it manually, it runs perfectly fine. But when I schedule it as job, the job reports:

  Executed as user:
  Domain\SQL2008S1_service.
  ...p_DTSExecutePackageTask_5   DTSRun
  OnError:
  DTSStep_DTSExecutePackageTask_5, Error
  = -2147467259 (80004005)      Error string:  DTSStep_DTSExecuteSQLTask_2:
  The Microsoft Jet database engine
  cannot open the file
  '\\SQL2000S2\C$\directory\someExcelFile.xls'.
  It is already opened exclusively by
  another user, or you need permission
  to view its data.

I am 100% sure that the files are not open and I thought I have granted enough rights to the SQL agent/SQL service account.

Any help please?


texts:
I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the database files and file groups for these years.

The current scheme in place covers 200901 - 201512 and I now need to add 200001 - 200812. What is the best way to bring this online for the partitioned table?

Create a new partition scheme and partition function and change the clustered index to this new partition scheme; or
Can I somehow alter the existing partition scheme and partition function?

I'm asking because the current table has 18 billion rows and I want to do this the efficient way :)


texts: When Master is being auto vacuumed, Is slave gettting exclusive lock on its table to apply WAL?

texts:
I've met the very curious pattern that triggers a lot of wait queue on slaves.
And I think I finally find it's because of auto vacuum. However, I've never got such description on official document nor any of blogs of PostgreSQL.
So I would really appreciate if someone knows this question.

I have a big table(I'll say, A table) on Master, and I run periodically a batch script on slaves and this batch calls a lot of SELECT queries on the A table.

One day, I found that on slave there are a lot of wait queues, and there were waiting on the SharedAccessLock on the A table. However, these queries are also SELECT query and so these queries should never wait any SELECT queries.

So I've searched around and I found that when slaves are being got lots of SELECT queries on the A table, and at the same moment Master is executing autovacuum on the A table. I stopped autovacuum on Master and kill the autovacuum process, then restart all the queries to slaves. After that wait queues disappeared.

Error logs say, these SELECT queries on slaves wait SharedAccessLock, however I am guessing that these SELECT queries waits the WAL apply. I know it's very odd to say like this thing. My PostgreSQL is 9.6ver.


texts: What does IDLE state denotes in a row of pg_stat_activity?

texts:
Can someone resolve my below doubts about pg_stat_activity in terms of postgres 9.5?

Can some let me know what does the state IDLE denotes in the row of pg_stat_activity? Is it like it checks all the open connections to postgres and if the connection is not executing any query then IDLE denotes the last query executed by the connection?
In the output of select * from pg_stat_activity I am seeing rows with state IDLE and the query_start_date more than 2 days old? What does this signify ? Should I be worried about this?
Can someone let me know how pg_stat_activity is populated? If I am executing a query like Select * from pg_stat_activity does it produce the details from some snapshot maintained by postgres or it checks the current connections open in postgres to provide the details?


texts: Why does SQL try to convert rows that are excluded by my where clause?

texts:
I've come across this multiple times, and I'm sure there is a good reason for it - but how do I avoid it?

I'm sure it has to do with the quirks around isnumeric. In English, I have a view that filters on isnumeric(somefield) = 1. I then try to query it using an int in my where clause, and because some other field in the table has character values, the whole thing fails.

I created a SQL fiddle that shows the error.

I tried doing a cast/convert in the select on the view, but the query engine seems to ignore it.

So - why does this happen, and is there a clean way to deal with it?

The original fiddle had a very similar error due to me missing quotes on the insert. That is NOT the problem I was trying to highlight. I have fixed the view by adding quotes to all values being inserted.


texts: How should I organize my physical storage on 5 ssd single server for 2.2 TB database

texts:
I need to migrate a 2.2 TB database to a better organized single server with 12 physical cores. New server has 5 SATA SSDs 1 TB each, will upgrade to 6 SSDs 1 TB each.

My current design is (this is new server):

Disk 1: Windows and SQL server binaries, transaction log for main database (this is temporary, going to move log to Disk 6).
Disk 2: TempDB split into 8 data files and 1 log file that take all the space evenly per this article https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/
Disk 3-4-5: RAID 0 with main db of 2.2 TB. Raid is created via Windows Server 2016's "Storage Pools" functionality.
Disk 6: In the future will contain main db log.

There will be no redundancy, this is all I can have right now. Current recovery plan is daily backup to external storage, and full db restore if something fails.

What will be the best approach to place DB of this size over 3 available SSD disks?

1 filegroup, 1 file on a raid?
1 filegroup, several files on same raid (there is some info that this can improve IO when server has many physical cores). If I go in that direction, do I need to rebuild clustered indexes so that all my data would be evenly distributed across all files? is there any benifits?
1 filegroup, 3 (or more?) files on 3 individual disks? Again, rebuilt to evenly distribute all data?
Multiple filegroups on separate disks?

Of course I expect "it depends" answer, but I need a place to start to finally migrate the DB, and then look into analyzing and tuning it furthier.

Software & hardware: Windows Server 2016, Sql Server 2017.
Machine is Dell Precision Tower 7810 with 12 physical cores (2 processors).
Currently 32 GB ram, hoping to upgrade to 64 soon.


texts: How do you structure a database that organizes post by location like craigslist?

texts:
I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to explain that. I'm just wondering how someone would handle posts that are categorized by location the same way craigslist does it, eg. Country > State > City > County.

Let's say you have a PostTable that holds all posts site-wide. Do you have 4 fields in this table dedicated to CountryID, StateID, CityID, CountyID? Or do you have 1 field that lists CountyID and from that ID determine what Country, State, and City it's a part of?


texts: How can I set a field when original lookup is NULL or isn't in the list?

texts:
I am trying to merge our HR data.  I had to create my own tables to give a "regions" table

 DROP TABLE
IF EXISTS testgiver.ldap_karen;

CREATE TABLE testgiver.ldap_karen (uid VARCHAR (10) NOT NULL PRIMARY KEY) SELECT
  ldap_full.thomsnamedisplay,
    ldap_full.uid as 'uid',
    ldap_full.businesscategory,
    ldap_full.mail,
    ldap_full.givenname,
    ldap_full.sn,
    ldap_full.departmentnumber,
    ldap_full.title,
    ldap_full.thomsmgdescr,
    ldap_full.thomsmgdescroverride,
    ldap_full.thomscompanydescr,
    ldap_full.thomscompanydescroverride,
    ldap_full.thomslocationstreet1,
    ldap_full.thomslocationcity,
    ldap_full.thomslocationstatedescr,
    ldap_full.thomslocationpostal,
    ldap_full.thomslocationcountrydescr,
    ldap_full.thomssupervisorid,
    ldap_full.thomssupervisoriddescr,
    ldap_full.thomscompanyshortdescroverride,
    ldap_full.thomsbulevel1descr,
    ldap_full.thomsbulevel1shortdescr,
    ldap_full.thomsbulevel2descr,
    ldap_full.thomsbulevel2shortdescr,
    ldap_full.thomsroleindicatordescr,
    ldap_full.thomsjobcodedescr,
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail,
  reports_regionnames.regionname
FROM
    ldap_full
LEFT JOIN ldap_full AS sup_email ON ldap_full.thomssupervisorid = sup_email.uid
LEFT JOIN reports_regions ON ldap_full.thomslocationcountrydescr = reports_regions.thomslocationcountrydescr
LEFT JOIN reports_regionnames ON reports_regions.thomsregion1 = reports_regionnames.regiondid

This works fine and adds a region to each user.  However I would like to take it a step further.  I would like it to handle instances where and employee does not have ldap_full.thomslocationcountrydescr (so null) or if the employees ldap_full.thomslocationcountrydescr is not in the lookup table.  This seems simple but I have tried a few things and I don't think my syntax is even close.

Would simply like the region to be set to "bad" for each employee with a "new country" or no country.


texts: differences between hot standby vs warm standby postgresql？

texts:
I am confused about the Differences BETWEEN the database replication methods mentioned in a wiki page of postgres, which is best for normal situation?

warm-standby/Continuous archiving/log shipping

offers high availability

http://www.postgresql.org/docs/current/static/warm-standby.html
hot-standby/binary-replication/Streaming Replication

used for read-only querie

https://wiki.postgresql.org/wiki/Hot_Standby

https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
PITR


texts: Oracle "Import from Export Files" screen refusing both SYSADMIN and Normal users

texts:
I have just installed Oracle 11g on Win64, and I want to import some .dmp files.

I am trying with the tool Oracle Enterprise Manager 11g:

If I log in with SYS as SYSADMIN, and go to Import from Export Files, I am told Role Error. Export and Import are not supported on 10g databases for users logged in with the SYSDBA role. Logout and login using a different role before trying again.
If I log in with SYSMAN as Normal, the Import from Export Files screen shows no error, but when I click Create Directory Object then OK, I get: Errors. Failed to commit: ORA-01031: insufficient privileges. You are not logged in with SYSDBA privilege. You cannot assign privileges to directory object.

I tried creating a "Directory Object" as Normal, but unfortunately it is not visible under SYSADMIN.

How can I use this Import from Export Files screen ?


texts: Storing data in multiple rows vs serialized data

texts:
I am currently designing a database table where I store list of courses chosen by students for a session. My current design has to do with storing each course in a row and a student can have up to 30 courses per session. This means that 300 students in that single department will consume 30*300 = 9000 rows for just a session. There over 50 departments in the School and each department has at least 300 students per level (5 levels) and the minimal courses taken per session is up to 30. The system is designed in such a way that these data stored will not be deleted.

Meaning:

50 (departments) * 300 (possible students) * 5 (levels) * 30 (rows for courses) = 2, 250,000 (possible rows consumed in a session).

Serialized Format

Is it better to store a student courses in a serialized format that use just a row to save it?

50 (departments) * 300 (possible students) * 5 (levels) * 1 (rows for courses) = 75,000 (possible rows consumed in a session).

I calculated this and I think it makes sense but I don't know if it is a faulty design.

Low Point
I recognised that update and deletion query will overwrite/delete everything in the courses column.

Please an insightful help will be appreciated.

Note: I use MySql for my database.


texts: For how long postgres will keep alive connection after changing credentials?

texts: I have an app running Ruby on Rails, standard nothing fancy at all. Today when I was changing password in Postgresql I spot that app is still running with old credentials. This gives me time to rolling restart app with new db credentials. Open connections still work, because was authenticated with old credentials. How long Postgres will keep those connection active? I assume that there must be some time after client must re-authenticate. Where I can find more about it? Some setting? Tried to find something on https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html but still don't know how it works in details.


texts: I have 100 records. I want to display only 10 rows of that record default or by specifying the number by using procedure


texts: I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

texts:
I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine.
There is this option in InnoDB capacity that you can define the IOPS in it. Let's say
that I have a SATA 72K-RPM HDD that allows 100 IOPS. Does it mean that my DB can run 100 concurrent SQL query per second only or it means 100 single Read or Write in 1 second ?
How a DB can handle 3000 query per second being launched on a Hardware like that ? I'm a bit confused so please explain it for me. I want to get more than 200 query execution on my dedicated server.