texts: Mongodb - Error after turning fork mode on

texts:
I am working on installing mongodb on remote server I managed  to install everything and tested it.

But when I tried to enable fork mode to make it run in the background via the command

mongod --config  mongod.config

mongod.config

systemLog:
 destination: file
 path: "etc/mongod.log"
 logAppend: true
storage:
 dbPath: "/home/peter/data/db"
 journal:
  enabled: false
processManagement:
 fork: true
net:
 bindIp: 127.0.0.1
 port: 27017
setParameter:
 enableLocalhostAuthBypass: false

this command is run successfully but when trying to run mongo
I get this error

MongoDB shell version v3.4.9
connecting to: mongodb://127.0.0.1:27017
2017-09-30T01:31:07.048-0700 I -        [thread1] Invariant failure !driverName.empty() && !driverVersion.empty() && !osType.empty() && !osName.empty() && !osArchitecture.empty() && !osVersion.empty() src/mongo/rpc/metadata/client_metadata.cpp 350
2017-09-30T01:31:07.048-0700 I -        [thread1]

***aborting after invariant() failure

2017-09-30T01:31:08.207-0700 F -        [thread1] Got signal: 6 (Aborted).

  Note: everything is working fine without fork mode

Any help will be appreciated.

update

log file

2017-10-01T08:07:05.786-0700 I CONTROL  [initandlisten] MongoDB starting : pid=488699 port=27017 dbpath=/home/peterw3/data/db 64-bit h$
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] db version v3.4.9
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] git version: 876ebee8c7dd0e2d992f36a848ff4dc50ee6603e
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] allocator: tcmalloc
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] modules: none
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] build environment:
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten]     distarch: x86_64
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten]     target_arch: x86_64
2017-10-01T08:07:05.787-0700 I CONTROL  [initandlisten] options: { config: "etc/mongod.conf", net: { bindIp: "127.0.0.1", port: 27017 $
2017-10-01T08:07:05.813-0700 I -        [initandlisten] Detected data files in /home/peterw3/data/db created by the 'wiredTiger' stora$
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten]
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTig$
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] Detected WT journal files.  Running recovery from last checkpoint.
2017-10-01T08:07:05.813-0700 I STORAGE  [initandlisten] journal to nojournal transition config: create,cache_size=15451M,session_max=2$
2017-10-01T08:07:05.914-0700 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=15451M,session_max=20000,eviction=(t$
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten]
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2017-10-01T08:07:05.994-0700 I CONTROL  [initandlisten]
2017-10-01T08:07:06.003-0700 W FTDC     [initandlisten] Error checking directory '/sys/block': No such file or directory
2017-10-01T08:07:06.003-0700 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory '/home/peterw3/d$
2017-10-01T08:07:06.005-0700 I NETWORK  [thread1] waiting for connections on port 27017
2017-10-01T08:07:10.193-0700 I NETWORK  [thread1] connection accepted from 127.0.0.1:33231 #1 (1 connection now open)
2017-10-01T08:07:10.209-0700 I -        [conn1] end connection 127.0.0.1:33231 (1 connection now open)


texts: Which user defined data type is memory optimized?

texts: How can i know which one of my uddts is memory optimized? and how can i know it's definition?


texts:
I have two node SQL server standard failover cluster.
I need to setup single node DR using log Shipping method.
Want to ask after switch over to DR how we can setup, plan so that application can connect automatically to DR database server without any changes on application connection string.
Thanks


texts: Customizing nls_sort and nls_comp in Oracle

texts:
There is different letters for 'KAF' (ک , ك) and 'YA' (ی , ي) in Persian with different unicode codes and same spell.

I am using these commands to set Persian sort and comparing in my project

ALTER SESSION SET nls_sort=persian;

ALTER SESSION SET nls_comp =linguistic;

With setting nls_comp and nls_sort parameter I receieve these output

select first_name from customer
where first_name like '%ك'; -- with result

select first_name from customer
where first_name like '%ک';--with no result

How can receive same output for these queries? Can I customize nls_comp ?


texts:
got the following symbol � in my sql
i want to know the source of it
and how could i remove it automatically
manual takes a lot of time

https://postimg.cc/gXK3HfX7

i got the following symbol after convert database to UTF-8
10.2.22-MariaDB


texts:
I have a field that can hold up to 3000 characters.  I need to split this field into 3 individual fields split1, split2, split3, I have successfully been able to figure out how to get the beginning, but grabbing the middle and end I am stuck on.  How would I capture the characters from 1001 to 2000 and from 2001 to 3000?

This is what I use to capture from 0 to 1000 how would I capture the data for split2, and split3

split1 = COALESCE(CASE WHEN DATALENGTH(fieldtosplit) > 1000
         THEN LEFT(CAST(fieldtosplit AS VARCHAR(MAX)), 1000)
         ELSE fieldtosplit END,'')


texts: Will the foreign key constraint throw an error while restoring a database?

texts:
I have never faced any issue like this, but I am just curious. Let's say I have a database dump file with me and I want to import the dump file to another database.
There are 2 tables table_A and table_B. table_B contains a primary key and table_A contains the foreign key. And serially, table_A is first and then table_B.

So, when I update the database table_A will be created with a foreign key and next table_B will be created that contains the primary key.

So, will there be any error in this case?


texts: SQL Server 2016 logins and roles - non english names

texts:
We have English versions of Windows 10 Pro and SQL Server 2016 and third party software (building automation) for reports.
That software requires "NT Authority\SYSTEM" to be in English not in different language i.e "ZARZĄDZANIE NT\SYSTEM" like in my case.
I checked it on VM software using ISO of Win 10 from Microsoft website and it works - I don't receive polish names over there.
Could it possibly be a Windows license origin/location or whatever that cause this issue?
Desired naming:

Not desired:


texts:
I am trying to create automate/daily backup of database in SQL Server 2008 R2. I did bit of research and I get to know that as I am using Express edition; which doesn't come with Maintenance Plan, I can't create database backup automate/daily basis. So, the only possibilities are I have to use either TSQL or create Job. I don't have much understanding of SQL Job, so I am left with T-SQL only.

Can anyone explain me how to do automate backup of database in T-SQL using stored procedure or is there any other options I have to do that.

Regards.


texts: How to find out the service accounts for each of my instances in the current server?

texts:
I have the following query that gives me one line for each sql server instance installed in the current server:

--list the instances in the current server
--marcello miorelli
--16-sep-2016

            DECLARE @GetInstances TABLE
            ( Value nvarchar(100),
             InstanceNames nvarchar(100),
             Data nvarchar(100))

            Insert into @GetInstances
            EXECUTE xp_regread
              @rootkey = 'HKEY_LOCAL_MACHINE',
              @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
              @value_name = 'InstalledInstances'

            --Select InstanceNames from @GetInstances
            Select *
            from @GetInstances

I have a server with 2 instances, when I run the above query there, I get the following result:

Now for EACH of those instances - or for a single instance, be it named or not, I would like to know at hand, information about the sql server services account similar to what is returned from the queries below:

select * from
sys.dm_server_services

SELECT  DSS.servicename,
        DSS.startup_type_desc,
        DSS.status_desc,
        DSS.last_startup_time,
        DSS.service_account,
        DSS.is_clustered,
        DSS.cluster_nodename,
        DSS.filename,
        DSS.startup_type,
        DSS.status,
        DSS.process_id
FROM    sys.dm_server_services AS DSS;

how can I achieve that?


texts: Getting the only updated field using a postgreSQL trigger?

texts: Is there any way to use a trigger which can get the only updated field from a record? I am just testing the audit trail log so it can save the field name alongside its other information when it is updated. I do not want to use IF statement because I may have more than 200 fields in my table.


texts: Oracle nologging impact for REDO Generation

texts:
Oracle database is on noarchivelog mode and tablespace is configured with nologging option keeping there will not be a situation to recover any data in mind.

Following scenario was occured during ETL process:

1st hour AWR showing 15GB per second and 131K per transaction in the
Load Profile.
1st hour AWR showing 54TB Redo generation logged in the "Instance Activity Statistics"

I am wondering why such a big volume of redo is generated in this context.

Considering following context for excessive redo:

It's known that dictionary specific tablespace is forced for
logging.
Considering that NOLOGGING is not applicable by any mean to any
UPDATE operations resulting from the MERGE statement.
NOLOGGING is explicitely specified with CTAS and DMLs like
inserts and used  /*+ APPEND */ hint with the insert statement to
bypass logging even if nologging options is active in
table/tablespace level.

Questions:

Can anyone see any other considerations beyond 1, 2 and 3, for generating such big volume REDOs?
In the context of #2, 54TB(seems that Simply computed by 15G per
second X 60 for min X 60 for hr) redo generation is not possible
within one hour window. Suspecting some bug with the Oracle AWR
report, it should be total redo generated since the Oracle is start
up.
Suspecting bug with the value of redo generated per seconds.

Any constructive answers for above questions and considerations are highly appreciated, so that I could rectify myself if I am wrong.


texts: How to remove identity specification for multiple tables

texts:
I have 109 tables in my database. Now I am migrating to Amazon server with identity insert and I want to remove auto increment column specification for all tables. How set auto increment column i.e. Identity specification to "no" in SQL Server 2008 for all tables?

For Example, I have

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO

And now I would like to remove IDENTITY, not only for this but for all remaining tables.


texts: in postgres, can one optimize a table across partitions?

texts:
in a database with many companies, segregating them using schema and search_path

almost all access comes at the company level, that is, limited to a single schema.  some data-warehouse and accounting functions need to scan all schema.

for this problem, per @horse_with_no_name's most excellent suggestion, using the schema themselves as partitions.  for example

create table global.usr(
    usr_id int primary key,  // these will be unique across all partitions
    name varchar );

create table cmp1.usr(
    usr_id int primary key,
) inherits( global.usr );

create table cmp2.usr(
    ....

if we populate

insert into cmp1 usr values( 11, 'eleven' );
insert into cmp1 usr values( 12, 'twelve' );

insert into cmp2 usr values( 21, 'twenty-one' );
insert into cmp2 usr values( 22, 'twenty-two' );

insert into cmp3 usr values( 31, 'thirty-one' );
insert into cmp3 usr values( 32, 'thirty-two' );

this works beautifully at the schema level:

set search_path = 'global';
select count(1) from t;   => 6

set search_path = 'cmp1, global';
select count(1) from t;   => 2

set search_path = 'cmp1, cmp2, global';
select count(1) from t;   => 4

set search_path = 'global, cmp1';
select count(1) from t;   => 6

the question is whether there is any way to optimize at the global level.  for example, would it be possible to create an index on global.usr_id that would span all partitions?


texts:
Please help, I really need to recover my databases.

service mysql start start: Job failed to start

mysqld --verbose 140401 20:16:11 [Warning] Can't create test file /var/lib/mysql/developer.lower-test
140401 20:16:11 [ERROR] mysqld: File '/var/log/mysql/mariadb-bin.~rec~' not found (Errcode: 122)
140401 20:16:11 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register file.
140401 20:16:11 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file. 140401 20:16:11 [ERROR] Aborting

This is what I've gathered so far.


texts: how to query the plan cache to find how healthy it is?

texts:
This morning looking at my monitoring tool I get a warning about
High Compiles

  Query plan compiles should generally be <15% of batches per second.
  Higher values indicate plan reuse is low, and will often correlate
  with high CPU, since plan compilation can be a CPU-intensive
  operation. High compiles may correlate with low plan cache hit ratios,
  and can be an indicator of memory pressure, since there may not be
  enough room to keep all plans in cache.

  If you see consistently high compiles, run a Quick Trace and sort the
  results by Cache Misses, then expand details to view actual compiling
  statements (SP:CacheMiss events, highlighted) along with the reason
  (SubClass) and procedure (Object).

WHile doing a superficial investigation I notice the
CPU is ok according to the picture below

But although I have set optimize for ad hoc workload to 1,
when I look at the SQL Server Memory Usage I see that lots of it is still used by the plan cache instead of buffer cache.
as can be seen on the picture below.

  The optimize for ad hoc workloads option is used to improve the
  efficiency of the plan cache for workloads that contain many single
  use ad hoc batches. When this option is set to 1, the Database Engine
  stores a small compiled plan stub in the plan cache when a batch is
  compiled for the first time, instead of the full compiled plan. This
  helps to relieve memory pressure by not allowing the plan cache to
  become filled with compiled plans that are not reused.

The question:

How can I find out what I can remove from the plan cache?
or at least how would I start this investigation?


texts: Joining two Tables with matching two values

texts:
I have two tables. One is user. user has two columns.

user_id
user_name

The other table is product. product has two columns.

product_type
seller_id

seller_id is a foreign key of user.user_id.

An example of product_type is "laptop".
I want to list all the names of users who are selling laptops.
(SQL Query)


texts: Error for some tables while inserting when default database is not 'master' in Sybase

texts:
I am not sure whether I should post this here or in Java's related site but because the error is a bit technical and Java developers may not know about it I thought this is the right place.

So the user that we are using to connect to Sybase database has default database set to tempdb, when persisting some of Hibernate entities (for which Hibernate produces simple 'insert' statement) we get this error:

  org.springframework.orm.jpa.JpaSystemException: com.sybase.jdbc4.jdbc.SybSQLException: An attempt was made to startup a multi-database update in database 'MY_DATABASE_NAME' after starting up the master transaction in a temporary database. This could render the database 'MY_DATABASE_NAME' unrecoverable and this update is therefore not allowed.
  ; nested exception is org.hibernate.HibernateException: com.sybase.jdbc4.jdbc.SybSQLException: An attempt was made to startup a multi-database update in database 'MY_DATABASE_NAME' after starting up the master transaction in a temporary database. This could render the database 'MY_DATABASE_NAME' unrecoverable and this update is therefore not allowed

I put the entire error message in above paragraph for others who may  encounter this issue but the actual error you may be interested is:

  An attempt was made to startup a multi-database update in database
  'MY_DATABASE_NAME' after starting up the master transaction in a
  temporary database. This could render the database 'MY_DATABASE_NAME'
  unrecoverable and this update is therefore not allowed.

If we change the default database for that user to 'master' everything is fine but my question is: if the default database is tempdb why do only some tables (Hibernate entities) have this problem and inserting for the rest is working fine?

We compared the definition of the tables that have problem with those that don't and found absolutely no difference.


texts: Relationship with a table without unique/primary key

texts:
I have a database with 3 tables.

Table Work, with a field named workName.

Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName.

And table Activity with field superworkname which should have a relationship with SubWorks.subWorkName.

I can't create this relationship because SubWorks.subWorkName is not an unique key (or primary key, it just don't appear in phpMyAdmin) and it can't be because it's allowed to have multiple SubWorks with the same name until Work.workName is different between them.

How can i resolve this situation? I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I added an ID column to Activity so i can differentiate them

That's what i want to do with relationships:

I have a Work named R1.

3 SubWorks, named: R1-1, R1-2, R1-3 with a relationship to R1.

And 2 Activity (ID: 1, 2), with refer with subWorkName to R1-1.
And another activity with ID 3, which refer with subWorkName to R1-2

I want, if i delete R1: R1-1, R1-2, R1-3 will be deleted too and with them all their activity, so activity 1 and 2 will be deleted to.

Now, suppose i delete R1-1, activity ID 1 and 2 should be deleted too.

If i delete R1-2 activity ID 3 should be deleted.

I already did the first part (if i delete a Work, SubWork will gone with it) but i had problem with the second.


texts: MySQL find_in_set function not work as expect

texts:
Now I am using this command to query user_id not in collections, this is my sql:
select *
from report_user_foundation
where !FIND_IN_SET(user_id ,(
    select GROUP_CONCAT(login_user_ids) as bet_user_ids
    from report_summary rs
    where statistic_time >= 1606752000000)
)

I query user id from table report_user_foundation that not in report_summary table login_user_ids column. but when the using the result user id to query in report_summary that still exists.
select login_user_ids
from report_summary rs
where login_user_ids like '%4685%'

Am I sql is not correct? Am I missing something?


texts:
Let's say that I have this table :

CREATE TABLE test (
  id uuid unique default uuid_generate_v4(),
  version int,
  another_column text,
  PRIMARY KEY (id, version)
);

What I want to achieve is, when I insert new row, I want the version to be based on id column. Ex :

+--------------------------------------+---------+
|                  id                  | version |
+--------------------------------------+---------+
| 25d57091-4d11-40de-9ca7-2c9500eb5484 |       1 |
| 25d57091-4d11-40de-9ca7-2c9500eb5484 |       2 |
| 25d57091-4d11-40de-9ca7-2c9500eb5484 |       3 |
| 9b43eed3-92fd-40e1-9bb4-9220f6bbd7f5 |       1 |
| 9b43eed3-92fd-40e1-9bb4-9220f6bbd7f5 |       2 |
+--------------------------------------+---------+

So I though about creating a sequence but couldn't find anything on this in the manual to base the nextval on a column value.


texts:
I'm attempting to create a linked server from SQL Server to Vertica to pull over some data. I've attempted using the following ODBC found here.

The code I'm currently trying is:

EXEC dbo.sp_addlinkedserver
@server = N'VERTICA',
@srvproduct=N'',
-- SQL Server driver
@provider=N'MSDASQL',
-- Connection string
@provstr=N'Driver=Vertica ODBC Driver 4.1;
    Servername=Vertica;
    Port=5433;
    Database=Vertica;
    UserName=user;
    Password=pass'

The error message I get is:

  OLE DB provider "MSDASQL" for linked server "VERTICA" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".


texts: Confirmation on query optimization and performance understanding (seek and scan)

texts:
I am following along with this video on SQL Server query optimization and I would like to check my understanding of the results I am seeing.

I am using SQL Server 2012 with the AdventureWorks 2012 database.

Testing the first three examples

Example one:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE CAST(CONVERT(VARCHAR(10), Customer.ModifiedDate, 101) AS DATETIME) = '20081013'

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate >= '20081013' AND Customer.ModifiedDate < '20081014'
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Example two:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE DATEADD(YEAR, 6, Customer.ModifiedDate) < '20131231'

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate < DATEADD(YEAR, -6, '20131231')
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Example three:

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE YEAR(Customer.ModifiedDate) = 2008

SELECT Customer.ModifiedDate
FROM Sales.Customer
WHERE Customer.ModifiedDate >= '20081101' AND Customer.ModifiedDate < '20090101'
GO

-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Customer'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Unlike the video, there is no difference between the number of logical reads between any of the queries as is. But, after playing around with the criteria and comparing results my reasoning for not seeing a difference comes down to

'In all the queries, all the records meet the criteria and so all of them have
 the same number of logical reads. When there is a predicate that excludes records
 from the result then you will see an improvement in the number of logical reads.
 This is due to SQL Server being able to utilize an index seek and thus only read
 the records that meat the criteria. If the indexed column is used in an expression
 than this will prevent SQL Server from being able to use an index seek.'

And this idea is also why I always see an improvement with the fourth example

SELECT COUNT(ProductId)
FROM Sales.SalesOrderDetail
WHERE ProductID=710

SELECT COUNT(ProductId)
FROM Sales.SalesOrderDetail
WHERE ProductID*2=1420
GO

-- Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 276, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The first query in this example utilizes a seek but the second query in this has to scan and thus read more (all?) of the index.

Questions:

Is my understanding and explanation of what is happening correct?
Are there any occasions when an indexed column can be involved in expression and still have its index 'seeked'?
Does not having a clustered index on a table have any effect on the ability to use a seek?


texts:
To start, I am a database novice. I thank you for any help in advance.

I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB.

Yesterday, I went through the process of shrinking my log file that had continuously grown to ~120gb.

To prevent this issue in the future I:

Configured the log file for an auto growth of 1gb and max size of 2gb (based on some solarwinds recommendations I had found)
Scheduled regular Transnational log backups every 30sec (again based on some recommendations I had found)

... well, i accidentally configured the log backups to be every 30 minutes in error and i believe that is what then allowed the log file to fill to it's maximum size?

I am now receiving the error "The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'"

I am unable to shrink the log file and I am also unable to update the log file to a bigger size (get the same error message)

I am wondering what the best course of action is and if there is anything else i need to do to prevent this issue in the future once resolved.


texts: MSSQL Server - Mirror Between Different Versions

texts:
I'm planning a SQL server upgrade and trying to get mirroring working between 2 different versions of SQL server, same edition - standard.

Is it possible to mirror from SQL Server 2008 std to SQL Server 2016 std edition? I know it wouldn't be possible the other way round.


texts:
I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:

data_static
----------
id                  | integer                     | not null
....
Indexes:
    "data_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "data_dynamic" CONSTRAINT "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

Table two:

data_dynamic
------------+-----------------------------+-----------
id         | integer                     | not null
...
Indexes:
    "data_dynamic_new_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "idcheck" FOREIGN KEY (id) REFERENCES data_static(id) ON DELETE CASCADE

When I execute count with join in this case (right join) planer works perfectly:

explain select count(d.id) from data_static s right join data_dynamic d on s.id = d.id;
                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=239.10..239.11 rows=1 width=4) (actual time=6.261..6.261 rows=1 loops=1)
   ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.013..2.437 rows=10128 loops=1)
 Total runtime: 6.311 ms
 (3 rows)

But when I execute inner join it scans whole bit table and this take a lot more time:

explain select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=35530.42..35530.43 rows=1 width=4) (actual time=1629.596..1629.596 rows=1 loops=1)
   ->  Hash Join  (cost=365.58..35498.80 rows=12648 width=4) (actual time=26.418..1625.962 rows=10128 loops=1)
         Hash Cond: (s.id = d.id)
         ->  Seq Scan on data_static s  (cost=0.00..32478.87 rows=252787 width=4) (actual time=14.185..1435.169 rows=252787 loops=1)
         ->  Hash  (cost=207.48..207.48 rows=12648 width=4) (actual time=7.939..7.939 rows=10128 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 357kB
               ->  Seq Scan on data_dynamic d  (cost=0.00..207.48 rows=12648 width=4) (actual time=0.012..3.759 rows=10128 loops=1)
Total runtime: 1629.956 ms
(8 rows)

It's not very realistic example but I wondering why planer works in this way and how it can be fixed?


texts: Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4

texts:
I completed a migration from MySQL 5.1 master-slave system to a MariaDB 10.4 Galera Cluster. All the databases where copied over since they were on InnoDB per-file and they are working fine. However some complex queries are VERY slow on the new system since they are completely useless and I'm a little bit loss... I fixed some of them by forcing an index but I don't know how to fix others like this one...
This is the query:
SELECT * FROM (
    SELECT * FROM abvalue WHERE deviceid='XXX'
) AS abvalue LEFT JOIN (
    SELECT * FROM abperson WHERE deviceid='XXX'
) AS abperson ON abvalue.person_id=abperson.person_id

(i did the query that way because it was the most efficient way back in the day)
this query is a little slow on the old system (about 2 seconds) but it completes. However in the new system I gave up after 60 seconds... I tried forcing indexes but I was unable to fix it.
This is the explain on MySQL 5.1
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL           | NULL           | NULL    | NULL | 12428 |             |
|  1 | PRIMARY     | <derived3> | ALL  | NULL           | NULL           | NULL    | NULL |   694 |             |
|  3 | DERIVED     | abperson   | ref  | deviceid_index | deviceid_index | 36      |      |   693 | Using where |
|  2 | DERIVED     | abvalue    | ref  | deviceid_index | deviceid_index | 36      |      | 16484 | Using where |
+----+-------------+------------+------+----------------+----------------+---------+------+-------+-------------+

and this is the explain on MariaDB 10.4
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
| id   | select_type | table    | type       | possible_keys               | key                         | key_len | ref                      | rows    | Extra                           |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+
|    1 | SIMPLE      | abvalue  | ref        | deviceid_index              | deviceid_index              | 36      | const                    | 22780   | Using where                     |
|    1 | SIMPLE      | abperson | ref|filter | deviceid_index,person_index | person_index|deviceid_index | 5|36    | ownspy.abvalue.person_id | 39 (0%) | Using where; Using rowid filter |
+------+-------------+----------+------------+-----------------------------+-----------------------------+---------+--------------------------+---------+---------------------------------+

this is the structures of the tables:
CREATE TABLE `abvalue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `value` char(128) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE,
  KEY `value_index` (`value`(5)) USING BTREE
) ENGINE=InnoDB

CREATE TABLE `abperson` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `deviceid` char(32) NOT NULL,
  `first` text DEFAULT NULL,
  `last` text DEFAULT NULL,
  `person_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceid_index` (`deviceid`(12)) USING BTREE,
  KEY `person_index` (`person_id`) USING BTREE
) ENGINE=InnoDB

Any help is really welcomed!


texts: Changing the mysql.proc table directly after upgrade

texts:
Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5.

This caused a problem because the table definition of mysql.proc is different as discussed here:

http://bugs.mysql.com/bug.php?id=50183

Basically, our stored procedure won't run because one column changed (comment) between 5.1 and 5.5  My question is, can I safely run this command to fix the problem:

alter table mysql.proc modify comment text;

I don't want to run mysql_upgrade as that will lock every table in the database on our production server.


texts: How to count the number of rows by datetime field with the division into time intervals


texts: How to free space after deleting the millions of rows from a table

texts:
I performed a delete operation on my table . It deleted around 10 millions of rows from it

How do I free the space associated with the deleted data.

I used the query:

alter table tablename shrink space;

But it did not work for me. Do I need to concern about the index's associated with the table?

If that is the case, how do I need to do it.


texts: how to count specific product in sql table

texts:
I have a table product and its fields are maker, type and model I want to get the makers who produce only one product type and more than one model. Here is something I am trying

select count(distinct type) as type from product where type = '1'

expe data is: maker = D  &  type = printer


texts:
SELECT TOP 1000 [LEAD_CO_MNE]
      ,CASE
         WHEN CURRENCY <> 'KHR' THEN  AMOUNT_FCY
         ELSE AMOUNT_LCY
         END 'Amount' --I want to split into type of amount debit or crdit
     ,CURRENCY

How could I split the 'Amount' into 2 column debitand credit base on the condition if > 0 credit else debit.


texts: copydb, shutdown commands are not supported in ApsaraDB for MongoDB. Any Alternate Available

texts:
List of below mentioned commands are not supported on ECS - ApsaraDB for MongoDB.

copydb
clone
clean
shutdown
repairDatabase
repairCursor

Is there alternate available for the same ?

I appreciate any assistance with this.


texts: MySQL server feature gap on Windows and Linux

texts:
Is there any gap or difference in features of MySQL Server on Windows and Linux? Feature refers to below aspects:

common DBMS functionalities such as Stored Procedures, Updateable Views, Distributed Transactions
differences in behavior caused by system features.
differences in configuration that have a impact on performance such as Shared Memory on Windows.


texts: linked server connection error in Sql server

texts:
When I tried to execute query I got the below error.

SELECT   *
FROM OPENQUERY([TESTORACLE], 'select *   from TESTTABLE')

  Cannot initialize the data source object of OLE DB provider "MSDASQL"
  for linked server "TESTORACLE".

I have created DSN and used below query to create linked server.

EXEC sp_addlinkedsrvlogin
@rmtsrvname    = 'TESTORACLE'
,@useself       = 'False'
,@locallogin    = NULL
,@rmtuser       = 'USERNAME'
,@rmtpassword   = 'PASSWORD';

EXEC sp_addlinkedserver
@server        = 'TESTORACLE'
,@srvproduct    = 'ORCLELINK'
,@provider      = 'MSDASQL'
,@datasrc       = 'ORCLELINK'

After that I tested the linked server and it worked fine.
When I execute a query it throws access denied exception so I enable Allow In process to the provider.

After that it throws above error and the test connection also not works.

Not sure why the connection fails when I enable Allow in process.

Please help me to resolve the error.

Thanks,


texts: Right outer join seems to not work because of where clause filter

texts:
I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON commission.referenzid = originDocForCurrency.id, but it don't give results, if no record is found with commission.referenzid = 68.

SELECT
    commission.netto AS commissionNetValue,
    commission.r_art AS commissionDocClass,
    commission.waehrung AS commissionCurrency,
    currencies.usd_brief AS foreignCurrencyUSDAsk,
    currencies.usd_geld AS foreignCurrencyUSDBid,
    originDocForCurrency.r_art AS originDocForCurrencyDocClass,
    originDocForCurrency.poswert AS originDocForCurrencyCommission,
 (SELECT originDoc.poswert FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCommission,
 (SELECT originDoc.r_art FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocClass,
 (SELECT originDoc.waehrung FROM [src_boss_entwicklung].[dbo].[tckopf] AS originDoc WHERE originDoc.id = 68) AS originDocCurrency
FROM
    [src_boss_entwicklung].[dbo].[tckopf] AS commission
RIGHT OUTER JOIN
    [src_boss_entwicklung].[dbo].[tckopf] AS originDocForCurrency
    ON commission.referenzid = originDocForCurrency.id
LEFT OUTER JOIN
    [src_boss_entwicklung].[dbo].[devisen] AS currencies
    ON currencies.datum = (
            SELECT TOP 1 fc.datum
            FROM [src_boss_entwicklung].[dbo].[devisen] AS fc
            WHERE fc.datum <= originDocForCurrency.von
                AND fc.usd_brief IS NOT NULL AND fc.usd_brief > 0
                AND fc.usd_geld IS NOT NULL AND fc.usd_geld > 0
            ORDER BY fc.datum DESC
            )
WHERE
    originDocForCurrency.id = 68
    AND
    ( commission.referenzid = 68
        AND commission.btyp = 7
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )
    OR
    ( commission.referenzid = 68
        AND commission.btyp = 8
        AND ( commission.storno <> 1 OR commission.storno IS NULL )
        AND ( commission.proforma <> 1 OR commission.proforma IS NULL ) )

How can I modify the query, to get the desired result? Can I achieve the desired result maybe with a CASE in the WHERE clause?


texts:
I'm attempting to design a database to track IT Fixed Assets and Consumable Parts; what is the best practice for this?

Should I design separate tables for Fixed Assets and Consumables, or should I have just one table containing rows for both types of items, with a Category field?


texts: View history of preferred replica in Availability Group - when

texts:
Is it possible to view a history of when the preferred replica in Availability Group changed?

E.g.

<datetime> Preferred replica changed from InstanceA to InstanceB

Not expecting it to be in any specific format, just trying to be clear as to what I am referring to.


texts: Why is changing data types not effecting the database size?

texts:
I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed their datatype from bigint to int. There is a clustered index on that table which uses neither of those two columns for its key.
The result is puzzeling to me. The data space (in the table properties) stayed exactly the same while the index space dropped 23.5%. Can someone explain to me what happened there? Why did the data space not change?
As an aside, I did something similar to another table. There the data space dropped 30,4% which is exactly what I calculated from the change in row size. The drop in index space was similar.
[edit note: I originally overlooked restoring one nonclustered index. This has been fixed and the question changed accordingly]


texts: Is it much more expensive to use procedures that get lists and deriving a count in the application vs. a separate stored procedure for the count?

texts:
I would like to know if returning a count is much more expensive than returning a dataset in regard to processing performance? I assume there is a bandwidth increase in delivery to the application logic.

I am basically trying to determine if it is more worth my time to create separate procedures to get counts or just utilize the procedures returning datasets and count how many rows are in the dataset in the application logic.


texts: Disable SQL Server 2012 Management Studio remote access

texts:
I am trying to disable SQL Server 2012 Management Studio remote access from other servers. I have unchecked SQL Server Properties -> Connections -> Allow remote connections to this server. but still users can access SQL Server database through SQL Server Management Studio remotely.

I have a client app on all network computers which needs to access the database. I just want to block SQL Server 2012 Management Studio remote access.

Thanks.


texts: How can I get the page size of a Postgres database?

texts:
Is there some way to select the page size of a Postgres database?

I know that the page size is usually 8K. I also read this email thread from 2003. I don't know that pg_controldata will work as I don't have access to the filesystem that the database is hosted on.


texts: Returning results from many functions / statements

texts:
I have a function that I will be running 10-50 times at once:

importsitechanges(33, 209703, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone')

This function that I made returns the number of changes made as a string.

When I run many of them at once in pgadmin, I only get the return string from the last one:

SELECT * FROM importsitechanges(33, 321344, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone');
SELECT * FROM importsitechanges(32, 209703, 'Store Name', 'Addr', 'city', 'state', 'zip', 'phone');
...

How can I get the return strings from all of these functions?


texts:
I have a huge query in SQL Server 2016 – when I run the query for a subset of records, it completes in 15 minutes. When I extrapolate it to total number of records, it is supposed to be complete in 1 hour. But it is not complete even after 3 hours.  When I checked  master.sys.sysprocesses there is a CXPACKET wait with 10293331 waittime

SELECT waittime, waittype, lastwaittype,  open_tran, cpu, physical_io, [sid], loginame, [sql_handle]
FROM master.sys.sysprocesses WHERE SPID=58

Note: I did exec sp_Who2 to get the specific SPID=58

From How to Use sp_WhoIsActive to Find Slow SQL Server Queries

  @get_task_info = 2 – if a query has gone parallel and you’re troubleshooting CXPACKET waits, you can figure out what each task in the query is waiting on.

I executed sp_WhoIsActive with @get_task_info = 2.

The wait_info gives the following:

(5x: 68/168850/635541ms)CXPACKET:15, (3x: 3/16/32ms)CXPACKET:7, (1x: 10207374ms)CXPACKET:1

How to determine what each task is waiting on from this information?

UPDATE

This question is more about finding a "way" to figure out the waiting resources (using sp_WhoIsActive or similar) than actually fixing this current performance issue by adding MAXDOP or analyzing execution plan.


texts: PostgreSQL Slave unable to catch up with the Master

texts:
We have a four node Patroni cluster setup wherein one of the nodes is unable to catchup with the leader because of huge lag. I have tried the below steps but none of them are working.

Restart the node
Remove node from the cluster, Cleanup the data directory and then add the node back again. After starting up it is again lagging.

Postgres Logs :
2021-04-08 00:46:46.165 PDT [38229] LOG:  database system was shut down in recovery at 2021-04-08 00:26:41 PDT
2021-04-08 00:46:46.165 PDT [38229] LOG:  entering standby mode
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  checkpoint record is at 170/70F4F00
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  redo record is at 170/70DD900; shutdown false
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  next transaction ID: 0:24406491; next OID: 5985346
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  next MultiXactId: 711; next MultiXactOffset: 1424
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  oldest unfrozen transaction ID: 562, in database 1
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  oldest MultiXactId: 1, in database 1
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  transaction ID wrap limit is 2147484209, limited by database with OID 1
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 1
2021-04-08 00:46:46.165 PDT [38229] DEBUG:  starting up replication slots
2021-04-08 00:46:46.166 PDT [38230] FATAL:  the database system is starting up
2021-04-08 00:46:46.166 PDT [38231] FATAL:  the database system is starting up
2021-04-08 00:46:46.167 PDT [38229] DEBUG:  resetting unlogged relations: cleanup 1 init 0
2021-04-08 00:46:46.170 PDT [38229] DEBUG:  initializing for hot standby
2021-04-08 00:46:46.170 PDT [38229] LOG:  redo starts at 170/70DD900
2021-04-08 00:46:46.171 PDT [38229] DEBUG:  recovery snapshots are now enabled
2021-04-08 00:46:46.171 PDT [38229] CONTEXT:  WAL redo at 170/70F4E58 for Standby/RUNNING_XACTS: nextXid 24406508 latestCompletedXid 24406507 oldestRunningXid 24406508
2021-04-08 00:46:46.178 PDT [38235] FATAL:  the database system is starting up
2021-04-08 00:46:47.191 PDT [38246] FATAL:  the database system is starting up
2021-04-08 00:46:48.205 PDT [38248] FATAL:  the database system is starting up
2021-04-08 00:46:49.218 PDT [38250] FATAL:  the database system is starting up
2021-04-08 00:47:48.337 PDT [38229] LOG:  consistent recovery state reached at 170/C9C5FFE8
2021-04-08 00:47:48.338 PDT [38224] LOG:  database system is ready to accept read only connections
2021-04-08 00:47:49.782 PDT [38417] LOG:  started streaming WAL from primary at 170/C9000000 on timeline 28
2021-04-08 00:47:50.003 PDT [38421] DEBUG:  received replication command: IDENTIFY_SYSTEM

Can someone suggest on how to resolve the issue.


texts: SQL Server MAXDOP on VMWare Virtual Servers

texts:
When setting MAXDOP on VMWare virtual servers VMWare recommend following the Microsoft guidlines

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi

Keep MAXDOP at or below # of logical processors
Keep MAXDOP at 8
Keep MAXDOP at or below # of logical processors per NUMA node
Keep MAXDOP at 8

A colleague argues that virtual CPUs are different, SQL Server can only access 1 CPU no matter how many you have and MAXDOP should be set to 1. Based on the above information and lots of other websites I think that is wrong, and bad for performance. I have set MAXDOP as above for years. But is there any truth in what they are saying.

Many Thanks


texts:
I am needing a way to run an If/Else statement in SQL Server.  This is what I am needing done, if my Exec sp_executesql @SQL statement returns a value (meaning it exists in the table) I want to set the value returned to a variable.  If it does not return a value, then I want to print a message saying it does not exist.
With the sample DDL Below, I would want to assign the value of 3 to the variable @placeholder since 3 would be the value returned.  How would that syntax be constructed?

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')

Declare @Color varchar(100), @sql nvarchar(max), @placeholder varchar(100)
Set @Color = 'Pink'

Set @Sql = 'Select id from #Test1 WHERE name IN ('''+@Color+N''')'

Exec sp_executesql @SQL

Drop Table #Test1


texts: Slow ORDER BY with large and small tables

texts:
I have one small table (200 rows) and two large tables (200k rows each).

Small table has primary key id and some other fields.

First big table has id field (not a primary key), which are duplicated, but sorted numbers and a matchresult_id field, which are id's from a second big table.

Second big table has a field matchresult_id (primary key) and some other fields.

The query:

SELECT big2.*
FROM small_table s1
       INNER JOIN small_table s2 ON s1.id = s2.parent_id
       INNER JOIN big_table1 big1 ON s2.id = big1.id
       INNER JOIN big_table2 big2 ON big1.matchresult_id = big2.matchresult_id
ORDER BY big2.matchresult_id

The query takes over 1 second to complete. Without ORDER BY it takes 0.003 seconds. How to optimize the query?

Edit: Tables structure (some columns are omitted and names changed for the greater simplicity):

CREATE TABLE `small_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `catalogue_group_parent_id` int(11) NOT NULL,
 #.. plus many more columns
 PRIMARY KEY (`catalogue_group_id`),
 KEY `idx_catalogue_group_parent_id` (`catalogue_group_parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8

CREATE TABLE `big_table1` (
 `id` int(11) NOT NULL,
 `matchresult_id` int(11) NOT NULL,
 `catalogue_group2item_primary` tinyint(1) NOT NULL DEFAULT '0',
 KEY `idx_catalogue_group2item_groupid` (`id`),
 KEY `idx_catalogue_group2item` (`id`,`matchresult_id`),
 KEY `idx_catalogue_group2item_matchresultid` (`matchresult_id`),
 KEY `catalogue_id` (`id`,`matchresult_id`,`catalogue_group2item_primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `big_table2` (
 `matchresult_id` int(11) NOT NULL AUTO_INCREMENT,
 `matchresult_datecreated` datetime NOT NULL,
 `matchresult_datemodified` datetime NOT NULL,
  #.. plus many more columns
 PRIMARY KEY (`matchresult_id`),
 KEY `match_id` (`match_id`)
) ENGINE=InnoDB AUTO_INCREMENT=214477 DEFAULT CHARSET=utf8


texts: When try to do Mysqldump Backup from console then getting such as error

texts:
mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when trying to connect

when i am tring to do MySQLDump backup from console then getting such as above error.
I am properly configure my MySQL Enterprise workbench and SQLyog ultimate tool. Able to do query and backup through that.

Kindly any one suggest me what i had mistake in system profile configuration of workbench.
I have check out my connection parameter that is ok.

Do i need to configure anything anywhere?


texts: Generate Slot Summaries(chunks of continuous slots) from the Slot table

texts:
We have a Slot table to store appointments. The app receives the date and multiple slots which are then divided into smaller slots of 15 mins interval.

 CREATE TABLE `Slot` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `starttime` datetime NOT NULL,
      `endtime` datetime NOT NULL,
      `assigned_To_Id` int(10) unsigned DEFAULT NULL,

      PRIMARY KEY (`id`),
      KEY `appointment_assigned_to_id_foreign` (`assigned_To_Id`),
      CONSTRAINT `appointment_assigned_to_id_foreign` FOREIGN KEY (`assigned_To_Id`) REFERENCES `Employee` (`id`),
    )
 ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

Now, the admins can add slot for a employee(assigned_To_Id) for a particular day by specifying the slots : e.g. :

7 July Slots :
11:45-12:15
13:00-13:30
and slot time = 15 mins (above time range will be divided in slot time intervals of 15 mins and assigned_To_Id = 1

so Slots table will look like :

|id| startime            | endtime             | assigned_To_Id |
|1 | 2018-07-07 11:45:00 | 2018-07-07 12:00:00 |   1            |
|2 | 2018-07-07 12:00:00 | 2018-07-07 12:15:00 |   1            |
|3 | 2018-07-07 13:00:00 | 2018-07-07 13:15:00 |   1            |
|4 | 2018-07-07 13:15:00 | 2018-07-07 13:30:00 |   1            |

Now, I need to summarise this in the front end :
like

    7 July Slots :
    11:45-12:15
    13:00-13:30

Should I store these 2 high level slots in a different table or should I generate them from the Slots table ?
How can I go about writing a query to fetch  2 high level slots from the Slots table ?

Edit :  Slots in the table can be 'x' minutes (any value decided by the admin but it will remain fix for a given period of time).  When bigger slots are broken into smaller slots - we check that these smaller slots do not already exist in db (so no overlaps)  link overlap question here

Requests will come in multiple of 'x' mins.

(so take big slots as inputs, divide them into smaller slots and when later the Vendor revisits the app - show the big slots that he defined). I need to know how to manage the big slots (store in separate table, store in same table with a flag or write a query on smaller slots to generate summarize slots)


texts: Difference between DATE(time_field) AND time_field in a WHERE clause

texts:
I have 3 tables:

users
movements
unique_ids

I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the total of it at the end:

SELECT MONTH(mo.ctime) AS month, COUNT(DISTINCT(unique_id))
FROM users us, movements mo,unique_ids id
WHERE us.time_field > '2014-03-01' AND us.time_field < '2014-03-31 23:59:59'
AND mo.time_field > '2014-03-01'
AND us.room_name LIKE 'name'
AND us.room_name=mo.room_name AND us.user=mo.user AND id.id_log=mo.id_log
GROUP BY month WITH ROLLUP

The thing is that few month ago in other query I realise that adding DATE() to the time_field into WHERE clause makes faster the query, but adding this into the query shown, gives me different results than the initial one.

What's the difference between time_field > 'x' AND DATE(time_field) > 'x' inside WHERE clause?


texts:
I have a streams replication  related user in Oracle 11g. I see the user in dba_users table and was anle to login using the user but when I try to drop the user i get the error.

SQL> drop user <username> cascade;
Error at line 1:
ORA-1403: no data found.

Loos like data got corrupted in internal tables.

Any idea how to fix this mess and remove the user?


texts: mongoexport not exporting all possible data

texts:
I have a collection with documents similar to the following:

{
 _id: 'p_123456',
 id: 123456,
 kind: 'person'
 data: [...]
}

id field can contain either positive or negative integers.
This collection contains close to 100 million documents and I have a python script which I use in order to process the data from the collection.

I'm trying to export all the data, but into 8 different processes using the $mod operator in the following way:
    mongoexport -u user -p password -d db -c collection --query "{\$and:[{kind:'person'},{id:{\$mod:[8,\$i]}}]}" | python process.py - where $i is a number between 0 - 7.

For some reason I've noticed that when I use this method with 8 processes not all the data is being exported just 65 million out of 87 million for this specific kind.

If I run a single mongoexport process with the query {kind:'person'} only, all 87 million documents are being exported.

Is it possible that running 8 different proccesses with $mod:[8,0] to $mod:[8,7] isn't enough in order to export all the data? what am I missing here?

EDIT 1:
Following AdamC suggestion, I ran the following map reduce:

var map = function() {
  if (this.kind == 'person'){
      var key = this.id % 8
      if (key < 0){
          key = key * (-1)
      }
      emit( key, 1 );
  }
};

var reduce = function( key, values ) {
  return Array.sum(values);
}

The results returned as expected ~87 million records:

> db.map_reduce_modulus.find()
{ "_id" : 0, "value" : 10886482 }
{ "_id" : 1, "value" : 10878131 }
{ "_id" : 2, "value" : 10881552 }
{ "_id" : 3, "value" : 10882586 }
{ "_id" : 4, "value" : 10886060 }
{ "_id" : 5, "value" : 10882565 }
{ "_id" : 6, "value" : 10886171 }
{ "_id" : 7, "value" : 10883563 }

Therefor I don't think this is a type issue

Thanks for advance


texts: How to install RODBC driver for SQL Server 2016 R Services

texts:
I'm trying to get started with R services on SQL Server 2016. I was following this example from MS on youtube.

2 min 22sec into the vid John shows a reference to library(RODBC)... which I don't have. So I have downloaded the RODBC driver from CRAN

According to the documentation:

  RODBC is simple to install, and binary distributions are available for
  Windows from CRAN

But it provides no further details about how to install it. It mentions an ODBC Driver Manager but not much more than that.

So.. How do I install RODBC?

Do I unzip it to a specific path?
Is there a utility or tool required to install it?
Do I run a script?


texts: DB2 Indexes are taking very large size on disk space

texts:
I have huge table in DB with estimated size 7 GB  (I saw that using DB2CC and did the action Estimate Size for that Table).

In estimate size dialog I noticed that two of my indexes for that table have very huge size - one is 4 GB and other is 2 GB.

How is possible that index can take so much disk space and is this regular behavior?

Thank you


texts: Tips for Optimizing Oracle Warehouse Builder 11.1 with Oracle DB 10.2

texts:
Are there any tips for improving performance of Control Center and Design Center in Oracle Warehouse Builder? At the client side it is very slow to open and refresh Design center and control center and table/dimension/cube mapping deployments made to a remote database are also slow. Several users (<30) over the network are accessing OWB remote repository and make deployments to remote Oracle 10.2 DB using locally installed Design Center and Control Center.

Please suggest any performance optimization measures at Oracle DB level and at OWB level for the administrator.


texts: Building new version of a table and replacing the old one by rename, is it a good idea?

texts:
We have a slightly heavy table which is semi-frequently (every ~5 minute) being recreated by a procedure. I wondered if there was something that could be done in order to minimize the downtime during the table rebuild, and have thought of two possibilities so far (let's call the table "Device"):

Option A
1) Create the new table in the procedure, name it Device_new
2) Rename old table from Device to Device_old
3) Rename new table from Device_new to Device

Option B
Same as Option A, except do it using Partition Switching.
Caveat: Partition switching is significantly more complex to setup, and has quite strict requirements for usage.

Are these options poor solutions, if yes, how would you solve this use-case? Thank you.


texts: How do I normalize and transform field text algorithmically

texts:
I've got names and emails exported from one sales system that I am trying to move into my email service provider.  I have to normalize the data.

The email field has data like this:

John Smith <jsmith@gmail.com>

I want to algorithmically turn all email field entries into this:

jsmith@gmail.com

Do you know the best tool, script, process or excel function to do this?  It's racking my brain, because I don't want to do it manually.


texts:
I'm a newbie when it comes to SQL Server, it would be helpful if someone could point me in the right direction.

We have a database file that is called main-db.bak.

This is the database file that we have cloned as it contains all the main things we need in the process of constructing new websites for our clients. Is it possible to do the following:

Have a copy of the database on the same SQL Server Instance
Create a new empty database
Restore the database from main-db.bak file and not overrite anything else that may be using the same data MDF and LDF files?

Or would it be easier to reproduce this database from a live running database copy so that it is not needed to restore if from backup?

Is it mandatory to rename the LDF and MDF files when the database is restored, can I script to automate the majority of the work?


texts: Nested-Loop-Join: How many comparisons and how many pages-accesses?

texts:
Say you have two relations R and S , where R has 1000 tuples and 100 page-accesses, and S has 50 tuples and 25 page-accesses.

Assuming R is the outer relation, then how many tuple-comparisons and page-accesses are done?

And how many page-accesses if R is the inner relation?

for each tuple r in R do
   for each tuple s in S do
      if r and s satisfy the join condition
         then output the tuple (r,s)

So in order to find out how many tuple-comparisons are done, I need to do 1000 * 50 = 50000 because the algorithm is doing this "for each" tuple and we have in total 1000 tuples for R and 50 tuples for S, thus 50000 comparisons in total.

But how to know the page-accesses now? If R is outside, we have (1000 tuples) * (25 page-accesses for S) + (100 page-accesses for R) = 25100 page accesses?

And if R is inside, then: 50 * 100 + 25 = 5025 page accesses

I'm not sure if it is correct like that.. or how is this done correctly pleaseee? :/


texts: How to filter column output based on specific format value

texts:
Is there any way to filter by the output format in sql server.

Below is my output :

  SQLOutput

  AB-123456-99 Checker

  123456-10000-00 2016

  111119-20000-00 2016

How can I get the expected output as below,

the query should exclude all the column values which are in the format

  123456-10000-00  -- xxxxxx-xxxxx-xx

If the column output has this format that value should be excluded from results.

and my final output should be as below :

  SQLOutput

  AB-123456-99 Checker

Please help if there is any way to get this.


texts:
I'm really new on oracle db. I'm trying to update specific column on this table.

CREATE TYPE ABSTRACT_SALARY AS OBJECT (
startDate DATE,
endDate DATE,
salary NUMBER );

CREATE TYPE SALARY_TYPE AS TABLE OF ABSTRACT_SALARY;

CREATE TABLE Person(
---
---
---
---
salaryType SALARY_TYPE
)
NESTED TABLE salaryType STORE AS person_salaryTypes;

How can I update salary column %10 higher. Thanks in advance.


texts: Database not showing in sys.databases over linked server

texts:
I have a query that I can run on my server (Server1) as follows:

SELECT * FROM master.sys.databases WHERE name = 'MyDB'

This returns a row.

I have a linked server setup on Server2 which points to Server1. The linked server's permissions are set as "be made using the login's current security context" and there are no local to remote server mappings.

When I run the following query on Server2, I get no rows:

SELECT * FROM SERVER1.master.sys.databases WHERE name = 'MyDB'

For some reason, this is the one of two databases where this is happening, all the others (10+) show fine in the following remote query:

SELECT * FROM SERVER1.master.sys.databases

I don't understand why sys.databases does not list the database "MyDB" when queried via a linked server.

Also, I am not sure if it makes a difference, but MyDB is mirrored (as are all databases on the server).


texts:
I have a job that executes every 15mins on SQL Server 2008 and it comes up with the following error at least once in two days. But, it runs successfully the next time it runs.  The account that is running the stored procedure has db_owner permission on the database. Any idea why?

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000] (Error 229).  The step failed.


texts: pgAdmin on a fresh OS X Server installation

texts:
I know for sure postgresql is running (checked with ps ax), but when I install pgAdmin and try to make a server connection to localhost I get this error:

Server doesn't listen
The server doesn't accept connections: the connection library reports
could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (fe80::1) and accepting TCP/IP connections on port 5432?

Any help?


texts: Sqlcmd command strips $ out of query variable

texts:
What I am trying to do

Restore a database from backup using sqlcmd. Sqlcmd is being called by PowerShell.

What is the issue

The server folder which contains the database backups has a $ inside of the name. ie Hostname$InstanceName. When I try to restore any databases I get an error and notice it is trying to restore from \\networkshare\Hostname\DatabaseName\Database.bak

You'll notice that everything after the $ got stripped out. I believe this is due to sqlcmd's support of variables (-v parameter). I have tried to use the -x parameter (disable variable substitution) with no success.

This process works fine with folders that do not have $ inside of their names.

Does anyone know what I can try from here? Or see if I'm doing something wrong.

Thanks

Sample code

$Source_Server = "Hostname`$InstanceName"
$FQFile_DatabaseBackup = $Network_Share + "\" + $Source_Server + "\" + $Source_Database + "\Database.bak"
$Query_RestoreDatabase = "RESTORE DATABASE [$Source_Database] FROM DISK = '$FQFile_DatabaseBackup' WITH RECOVERY, REPLACE"
Invoke-Expression -Command "sqlcmd -x -S `"$Destination_Server`" -d `"master`" -E -Q `"$Query_RestoreDatabase`""


texts: Just how much is a query supposed to slow down the whole system?

texts:
This is similar to my earlier question but more generic. I have a system with 16GB of memory, 16 cores, supposedly quality hard drives, RHEL 5, MySQL 5.0.x. Yes, I know, we're working on upgrading that. But this is what we have.

I just ran a few queries with large inserts and complicated joins that caused load to spike to 6, from where it usually is at 1.10. People started reporting that the website was becoming sluggish. These are all MyISAM table, but this is not about row vs table locking - the performance loss was on an entirely separate set of tables from what was being operated on. This is not about 'can't execute other queries', this is about 'everything else on the system slows down'.

I've been trying to google this and can find no other resource saying a query is supposed to spike server load like that. So I'm starting to wonder: Is it just me? If so, do you have any suggestions? And if not, do you have any mitigation suggestions?


texts:
My dev MySQL-8 server crashed last night and would not restart. The error is:
[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

I tried the classic "start with innodb_force_recovery=1" but that has 0 effect (also removed tried =2 without much success. I also tried removing the ibdata and ib logfiles: it won't start either.
I'm really surprised that these "standard" methods do not work for something that is a mere crash ... but evils is often in the details as we know.
So the last option I tried is re-creating the tables in the database and then replacing the .idb files that are automatically generated with the ones from the crashed server.
The positive is that the MySQL database starts ... but when I want to see the content of the tables I have an error message saying:
Error Code: 1812. Tablespace is missing for table <table>.

I feel I'm nearly there ... any help or suggestion would be welcomed.
Many thanks - Chris


texts: Why won't some DBMS's allow rollback for certain DDL statements?

texts:
Recently I found out that MySQL doesn't support rollback of DDL such as "alter table"... Being used to PostgreSQL, that struck me as odd, but a friend of mine told me that even Oracle doesn't allow it.. Are there technical reasons for not supporting it? Is it simply an "uninteresting" feature for them?

Edit: just found this comparison. It looks like there are many DBMSes that do support transactional DDL.


texts:
I have a table in postgresql, let's say users:
CREATE TABLE users(
  user_id bigint NOT NULL,
  username character varying(24) NOT NULL,
  password character varying(64) NOT NULL,
  timestamp_created timestamp with time zone DEFAULT NOW() NOT NULL,
  new boolean DEFAULT FALSE NOT NULL
);

With the following query, I select all users
SELECT * FROM users;

What I want to achieve is the following: when a row is selected, the value of 'new' should be updated from false to true.
There is no BEFORE/AFTER SELECT in postgresql trigger functions, is there something similar?
Edit: I'm using NodeJS. Currently, I store all the user ID's of the returned rows in an array and do this query:
UPDATE users SET new = true WHERE user_id = ANY($1);

$1 is placeholder for the user ID's array.
Is there a more convenient way to do this?


texts:
DROP PROCEDURE IF EXISTS proc_x;
DELIMITER #
CREATE DEFINER=`root`@`localhost`  PROCEDURE proc_x(sequel CHAR)
proc_main:BEGIN
SET @SQL = CONCAT(@sequel);
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END proc_main #

Above is a syntax for creating stored procedure in my mysql database.
When I execute following statement I get error.

CALL proc_x("INSERT  INTO tbl_rec(id,name,brnachID) values(10,'Krunal',07)");

  Error : ERROR 1064 (42000) at line 93: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

Can anyone help me to solve it?

Currently I'm using Mysql on UNIX.


texts:
I have the following aggregation query:

db.getCollection('usersongs').aggregate([
        {$match: { room: 'someObjId', status: { $in: [ "ended", "skipped" ] } } },
        {$group: {
            _id: "$song",
            timesPlayed: { $sum: 1 },
            scores: { $sum: { $subtract: [ "$likes", "$dislikes" ] } }
        }},
        {$sort: { scores: -1 } },
        {$limit: 10 }
]);

The schema of "usersongs" has the following fields (and some more):

room (ObjectID)
song (ObjectID)
status (string)
likes (int)
dislikes (int)

I already have an index for room and status fields but the query is still very slow (5 seconds plus), should i add more fields to this index combination for the query structure above?

I tried to added {explain: true} as a second parameter to the aggregate method but can't find anything helpful in the output (also it looks ran too fast at this mode).

db.usersongs.find(
    { room: 'someObjId', status: { $in: [ "ended", "skipped" ] } }).explain(true)

returns this output.

I am using v3.2.4

This started after migration from single node to ReplicaSet, indexes were rebuilt from the mongodump files (BSON's) but maybe something was missed out? Maybe I need to rebuild the index?

The document has more than 7M records. This is why I created so many indexes to support different operations. I'm doing in the code (each one have his own conditions so I want to cache that to speed things up).

Why does mongo choose "room_1_song_1_status_1_duration_1" index as the winning plan? The proper index to my case is "room_1_status_1".

I tried:

db.usersongs.find(
    { room: ObjectId('someId'), status: { $in: [ "ended", "skipped" ] } }
    ).hint( "room_1_status_1" )

...and now it's running perfectly! The problem is the that hint isn't supported with the aggregation framework. So it hasn't solved my problem yet.


texts:
I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB:

 sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com"

So I try to set this up in Squirrel SQL as follows with the Oracle Thin Driver:

jdbc:oracle:thin:@pashratgdb01.lex.bb.com:1876:atgpen1.lex.bb.com"

When I do this, I get the following error:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:202)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
... 5 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)
at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
... 7 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:275)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 12 more


texts:
I wanted to create a second log file, but by mistake I created a data file.

Now I'm trying to remove it, but I get the error message "the file cannot be removed because it is not empty". The following query lists a lot of tables:

SELECT o.name AS TableOrIndex
FROM sysfiles f
JOIN dbo.sysfilegroups s
ON f.groupid = s.groupid
JOIN dbo.sysindexes i
ON i.groupid = s.groupid
JOIN dbo.sysobjects o
ON i.id = object_id(o.name)
AND i.indid in (0, 1)
WHERE f.name = 'DB_NDF_FILE'

UNION

SELECT i.name
FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
JOIN sysfiles l ON f.groupid = l.groupid
WHERE l.name = 'DB_NDF_FILE'

What's the correct procedure to move these data to other data files so I can safely delete this newly created file?


texts: How can I model direct ownership alongside group ownership?

texts:
I want to model the following:

Companies sell their own Products.
Sometimes, Companies will package multiple Products into a Bundle, and sell those as well.
A Company may only sell their own Products and Bundles.

I feel like it should be possible to model this just using foreign keys but I can't work it out. The possibilities I have considered, do not properly ensure data integrity.

I don't want to rely on something that will only work in a single kind of database, but could use something Oracle specific as a last resort.

Failed Attempt #1

products
  id

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)
  company_id REFERENCES companies(id)

companies
  id

This doesn't allow individual products to be related to a company. They are only related when they are part of a bundle, but they may not necessarily be part of any bundle.

Failed Attempt #2

products
  id

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

This is the exact same problem as #1.

Failed Attempt #3

products
  id
  company_id REFERENCES companies(id)

bundles
  id
  company_id REFERENCES companies(id)

products_bundles
  product_id
  bundle_id

companies
  id

In this model, it is possible to put a product for one company, into a bundle owned by a different company.

Failed Attempt #4

products
  id
  company_id REFERENCES companies(id)

bundles
  id

products_bundles
  product_id REFERENCES products(id)
  bundle_id REFERENCES bundles(id)

companies
  id

In this model, the ownership of a bundle could be derived from the products in the bundle. However, there is nothing to prevent products owned by different companies being put into the same bundle which breaks this possibility.

Failed Attempt #5

products
  id
  company_id REFERENCES companies(id)

  UNIQUE (id, company_id)

bundles
  id

products_bundles
  bundle_id REFERENCES bundles(id)
  product_id
  company_id

  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)

companies
  id

This is better but I'm slightly uncomfortable about the need for the the composite foreign key. Much of what I have read suggests that a composite foreign key is a sign of the data not being fully normalised but I'm not sure if that is true here and, if it is, how to improve it further.

This also doesn't allow me to enforce a company-wide unique naming scheme for bundles (although I can do that for products).

Possible Attempt #6

products
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

bundles
  id
  company_id REFERENCES companies(id)
  UNIQUE (id, company_id)

products_bundles
  bundle_id
  product_id
  company_id
  FOREIGN KEY (product_id, company_id) REFERENCES products(id, company_id)
  FOREIGN KEY (bundle_id, company_id) REFERENCES bundles(id, company_id)

companies
  id

I guess this might be the right solution. Are these composite foreign keys the right way to go?


texts: How can I run SQL Server Vulnerability Assessment from a SQL Job?

texts:
I want to run SQL Server Vulnerability Assessment from a SQL Server Agent Job. Currently, I am attempting a job with a PowerShell script and am running a command like the one below.

Invoke-SqlVulnerabilityAssessmentScan -ServerInstance $(ESCAPE_DQUOTE(SRVR)) -Database AdventureWorks

I have confirmed that Invoke-SqlVulnerabilityAssessmentScan is available on the SQL Server (I can run it from the PowerShell command prompt there), but when I run my job, I receive an error stating that

  The term 'Invoke-SqlVulnerabilityAssessmentScan' is not recognized as the name of a cmdlet

After looking at this Microsoft article, I am wondering if SQL Agent only has a subset of PowerShell cmdlets that it can access.

How can I run the vulnerability assessment scan from a SQL Job?


texts: How can I prevent Pentaho DI/Spoon from deadlocking?

texts:
I have a transformation with four steps:

Read a pipe-delimited text (a text file input step)
Write to a staging table, with truncation (import table)
Run a stored procedure against the table populated in #2
Run a stored procedure against the table modified in #3

These have hops between them: 1 to 2 to 3 to 4.

It consistently hangs (tested several times, waiting up to 1.6 hours); that is, Pentaho reports starting the job in the Logging window, then nothing more.  The GUI itself does not hang, in fact the Stop button disables after a moment, but the transformation does not proceed.

It appears to be deadlocking: checking for blocked sessions on the target database (PostgreSQL), I see two sessions, one for the stored procedure in step #3 (which shouldn't have started yet), which is blocked by a session which is trying to truncate the table in step #2.  The TRUNCATE never completes (though Postgres does not report it as blocked), so no records get written and step #3 cannot run.  If I kill either session, the transformation fails, with no records written; the sprocs in steps #3 or #4 sometimes run, but with no new data on which to operate.

If I disable the hop from 2 to 3, the job runs fine, and it takes just a couple of minutes.

Why is the sproc in step #3 trying to run when it has a dependency on step #2?  Does a Pentaho "hop" not mean "run A, wait for it to end, and then run B"?  What can I do to prevent the deadlock situation, or have I misread what's going on?


texts: How to query row containing the given element inside a JSON column in Laravel PostgreSQL

texts:
All I have is a PostgreSQL table that has a JSON column called agents.
See below:
SELECT agents FROM cnms_rosters;

Result:
      agents
------------------
 [1,13,3,16,15]
 [12,13,14,15,15]
 [11,73,55,16,44]
(3 rows)

This column contains the id of an agent. What I want to do is to query the row or rows that has the specific agent id.
How can I achieve that?
I am currently using Laravel framework. Is there any Eloquent approach in querying the data? If not, how to query it using DB::raw or other query builder?


texts:
I understand that looking at DBCC PAGE is supposed to show me the last known good DBCC CHECKDB but I am not seeing this work correctly.

I have a database that I know is corrupted. If I run DBCC CHECKDB the errors are displayed as expected (see below).

CHECKDB found 0 allocation errors and 1 consistency errors in table 'Blah'
   (object ID 251147940).
Msg 2508, Level 16, State 3, Line 1

When I run the below to evaluate dbi_dbccLastKnownGood, I get the time that the CHECKDB was just run down to the second.

DBCC PAGE ('Blah', 1, 9, 3) WITH TABLERESULTS;

I've read Paul Randal's information, and from that, it looks like I'm doing/reading this correctly. Has anyone seen this before and if so, any thoughts on working around it?

Here is a complete sample of the error. I am getting 12 of these for different tables.

    Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "Mailings", index ID 0, partition ID 16459231395840, alloc unit ID 16459231395840 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table Mailings' (object ID 251147940).


texts:
I have a table with all employee licenses and two dates in relation to a period of 2 years. I have to verify if have someone period sum of 30 days of license in one range 12 months

For example: data of range refer the 2 years between January of 2017 and January of 2019, we have of into January of 2017 and December of 2018 have 29 days of license not interest me, but the range between February of 2017 and January of 2018 has 33 days of the license and this interest me.

How do I create a query in Postgres to solve my problem?


texts: Dynamic Ports are being set to different ports

texts:
On one of our instances of SQL Server (12.0.5571.0 if you really need to know) we use multiple IP addresses to connect.

127.0.0.1 - Dynamic Port set to 0. No static port set
172.16.0.5 - IP Address for the server that the SQL Server is on. Dynamic Port set to 0 (autgenerating). No static port assigned
172.16.0.6 - IP address for the SQL Server instance (that way teams just have to hit the IP address and not know which server they really are on). No Dynamic port assigned. Static port 1433

I've been playing with puppet and have been messing with the registry settings

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp

to see if I could use puppet to automate the configuration of IP addresses and port assignments. I created a new registry path path called 'IP172016000006'

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP172016000006

which I templated from

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP1

but now whenever I start the instance of SQL Server my dynamic ports for 127.0.0.1 and 172.16.0.5 are different.

For example, I will stop the instance of SQL Server, go into

'SQL Server Configuration Manager'

navigate to the network configuration for the instance

tcp/ip

IP addresses

and set the 'TCP Dynamic Ports = 0 for the IP addresses 127.0.0.1 and 172.16.0.5.

After I restart SQL Server, the instance will be listening on 127.0.0.1:43340 and 172.16.0.5:43341 (it seems to always be 1 more then the other port)

I've read the documentation on SQL Server Browser and found that SQL Server Browser will return the first port that it finds.

I've also read the BOL for setting a static port for SQL Server, which funny enough covers how to set dynamic ports. The BOL says that SQL Server will try to connect to the port that is specified, but if it fails it will get a new port. That makes sense for once in a long while, but SQL Server is consistently having inconsistent dynamic port assignments.

I've removed the offending registry values, but SQL Server is still having troubles setting the dynamic ports to be the same. I've restarted the whole server. I've set the TCP dynamic ports to be '0' multiple times.

How do I get consistent dynamic ports again?

Windows Server 2012 R2
SQL Server 12.0.5571.0
8 Instances of SQL Server installed - 3 of which are clustered (it's a test config box for us DBAs; don't judge too harshly)


texts: Insert as many rows as count on other table returns

texts:
Hello I have three tables in my MySQL-DB

Project:
id | name
1  | project1
2  | project2

KPI:
id | name
1  | build_status
2  | satisfaction

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1

Now I am trying to add a trigger to the Project-table. If a new project gets added it should insert as many rows into ProjectKPIData as there are KPI's and also insert the id of them. So after I add a new project the table ProjectKPIData should look like this (the project id 3 is for the newly created project):

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1
5  | 1      | 3          | 0
6  | 2      | 3          | 0

Is there a specific MySQL query I can acomplish this? Im pretty new to MySQL-Databases and I couldn't find a way of doing this while searching online.

Thanks for the help.


texts:
LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise.

I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work?

I have a lot of XML documents that are 4-10K in size that could easily be compressed to fit in under 4K. Would COMPRESS option result LOBs getting stored in-row therefore improving performance? Or does Oracle make in-row/out-of-row storage decision based on uncompressed size?


texts:
Is there a way to skip a specific table in the binary logs?

I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're going to update nightly from a batch process (70GB table). This table is only read, and it's updated nightly using mysqlimport.

During the update process for this reference-only table the binary logs are causing a pretty serious delay (especially when I do a full 70GB update). It's got to do 70GB twice basically. yuck.

The reference table is currently using MyISAM (I chocked InnoDB trying to load it, so trying out MyISAM).


texts: What is the use of restore headeronly when performing a backup?

texts:
When I monitor my backups with the following query:

SELECT      command, percent_complete,
            'elapsed' = total_elapsed_time / 60000.0,
            'remaining' = estimated_completion_time / 60000.0
FROM        sys.dm_exec_requests
WHERE       command like 'BACKUP%'
or command like 'RESTORE%'

I notice that before the backup, SQL Server perform a restore headeronly then the backup.

I was wondering what was the use for it and if its execution time could be reduced somehow. It appears to take longer than the actual backup.


texts:
I am new to Mongodb and I would like to confirm my configuration of write concern with majority and journal are correct.

I have three mongodb nodes:

1) In the mongo.conf of all mongodb servers, i added

storage:
    journal: enabled: true

2) in the mongodb shell, i entered

cfg = rs.conf()
cfg.settings.getLastErrorDefaults = {w: 1, j: true}
rs.reconfig(cfg)

Am I correct?

if yes, my second question is, what is the purpose of step 1 for?

Thanks


texts: Monetary value in integer over decimal datatype

texts:
I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead.
The reasoning that it does not store actual value and will cause some rounding difference/errors.
But i have yet ever participate in any project that use an integer datatype for monetary value in database storage.

My question is:
1) Is there any solid business use case/scenario/example of using decimal datatype (19, 4) on monetary value that will result in rounding issue?
As so far i had yet to experience the above mentioned issue.

2) As we all know that fiat currency have range of decimal place of 0 to 3 depending on countries.

Imagine now that we have a system that accepts fiat currencies transfer based on configuration configured in currencies config table.

Is it common to implement a decimal constraint for each currency during insert or we leave it to application level to deal with that?

TransactionLog
--------------
CurrencyID
UserIDFrom
UserIDTo
TransactionDateTime
CurrencyAmount (decimal(19, 4)


texts: Hiding all databases in SSMS that a user does not have access to without making them db_owner

texts:
I am trying to make it so a test user can only see the dbs that he has access to. Currently, the user can only see the one db that he has access to, but he is now the db_owner of that database. I don't feel that a test user should be the db_owner as this gives him access to everything in the database. From the articles that I found online, this seems to be the only way to make it so the user can only see the single db. When I use DENY VIEW ANY DATABASE TO [user] he does not see any db within the server, including the db I have given explicit view privileges to. Is there a way to allow him to only view the single db without giving him db_owner privileges?

The article I am referencing is here.


texts: How to fix "TNS:listener does not currently know of SID given in connect descriptor" with DBVisualizer on Linux?

texts:
I am trying to connect with Oracle database from a server having Oracle client installed (RHEL Linux Environment). I installed DB Visualizer on client machine. But getting listner error on hitting connect button.

I have tried TNSPING and connecting through SQL Plus and both are fine. What's the possible cause of the error in DBViz? How can I fix it?

DB Connectivity Detail:

TNS Ping

[root@bvqa-emmcas01 bin]# ./tnsping CMSQA
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=CMSQA)))
    OK (10 msec)

SQL Plus Connectivity

[root@bvqa-emmcas01 bin]# ./sqlplus temcsys/<password>@CMSQA
    SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Release 11.2.0.3.0 Production on Tue Mar 24 00:08:35 2015
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

tnsnames.ora

CMSQA=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.160.189.78)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME=CMSQA)
    )
  )

Error in DB Visualzier

Long Message:
Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


texts: How to create a cumulative sum on update in mysql-python

texts:
[Migrated from Stackoverflow]

I have the following statement in Python for MySQL Driver, that increments counter by one, on update with the same language.

add_lang = ("INSERT INTO Languages (lang, length, counter)\
            VALUES (%s, %s, 1)\
            ON DUPLICATE KEY UPDATE\
            counter = counter + 1;")

Now, what I want to achieve is a cumulative sum. My analogy suggests:

add_lang = ("INSERT INTO Languages (lang, length, counter)\
            VALUES (%s, 0, 1)\
            ON DUPLICATE KEY UPDATE\
            counter = counter + 1;\
            length = length + new_length")

How to differentiate between inserted new values, and current value before update ? Notice new_length and length. this is just to show my aim.


texts:
I have columns like role1, role2, role3, etc. They are all booleans.

I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the view would contain ["role1", "role3"].

Is there any good way to do this that doesn't explode into a ton of CASE WHEN´s? To clarify, I'd be fine with O(n) with CASE WHEN, but not O(2^n) which is what it currently seems to need. :)


texts:
I am facing a big problem here so hopefully someone could help.

We had a PHP web app on IIS 7 and Windows Server 2012 R2 with SQL Server Express.

We decided to move on a better option. So we did upgrade our Server.

We had the following specs:

4 core Intel i5-4690
32GB RAM
2 x 2TB SATA HDD (RAID 1)
2 x 120GB SSD (RAID 1)
Unlimited data transfer
2 IP addresses included

So move to Windows Server 2016 Standard with latest IIS and MSSQL Web with these specs:

Intel E5-2603 V3
6 cores (6 threads)
48GB RAM DDR4
6 x 500GB SATA storage drive (RAID 5)
2 x 120GB SSD system drive (RAID 1)
Unlimited data transfer
2 IP addresses included
Add up to 16 IP addresses
1Gbit/s connectivity
Microsoft SQL Web

The problem is that SQL Server Web is really slow. Transferring the data was successful and no problem on that. But SQL is really slow. The SSMS is really slow.

SQL Server Express was taking 821.9 MB of ram on the previous server while on the new one is taking up to 15 Gb. Also the logs are really high.

All the settings seems ok and similar to previous one.

I made a testing to check the speed of queries and to insert 1000 rows in  a table in hte new 2016 Web is taking around 19 seconds while on the previous one (SQL Server Express) was around 0.5 seconds.

I tried to look online for testings and solutions but nothing helped so far.

Any feedback would be helpful.

Thanks in advance


texts: Improving performance in a query with median

texts:
I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019)
Actual Execution Plan
https://www.brentozar.com/pastetheplan/?id=HJzjpF5Tv
Query
declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010C030000408ED64540020000800DDC53C0)
exec sp_executesql N'WITH #myselect
                         AS (SELECT
                                 BedroomsAvailable
                                 ,ListingId
                                 ,UnitTypeId
                                 ,Price,
                                 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price)
                                 OVER (PARTITION BY UnitTypeId, BedroomsAvailable) AS MedianCont
                            FROM Listings
                            WHERE ExpiryDate >= GetDate() AND  Price >= @MinFilterPrice AND Price <= @MaxFilterPrice AND Location.STDistance(@CurrentLocation) <= @RadiusInKms )
                        SELECT BedroomsAvailable
                            ,COUNT(listingid) AS Count
                            ,MIN(price) AS MinPrice
                            ,MAX(price) AS MaxPrice
                            ,AVG(price) AS AveragePrice
                            ,STDEV(price) as StandardDeviation
                            ,MIN(MedianCont) MedianPrice
                            ,UnitTypeId
                        FROM #myselect
                        GROUP BY BedroomsAvailable, UnitTypeId
                        ORDER BY UnitTypeId',N'@CurrentLocation [geography],@RadiusInKms int,@MinFilterPrice decimal(3,0),@MaxFilterPrice decimal(5,0)',@CurrentLocation=@p3,@RadiusInKms=20000,@MinFilterPrice=100,@MaxFilterPrice=10000

Indexes
CREATE SPATIAL INDEX [16_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE SPATIAL INDEX [16_MMMM] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE SPATIAL INDEX [256_HHHH] ON [dbo].[Listings]
(
    [Location]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Listings_CityId] ON [dbo].[Listings]
(
    [CityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Listings] ADD  CONSTRAINT [PK_Listings] PRIMARY KEY CLUSTERED
(
    [ListingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


texts:
Just wondering if there's a way of doing something like the below in Oracle, where if a returned result is one value you output one thing, else you output something else?

For example:

select if(COL1,1:'TRUE',0:'FALSE') from TABLE1;

Thanks in advance C