texts: Drop datafile without dropping a tablespace in Oracle 11g

texts:
I was trying to take backup of database in Oracle 11g by using RMAN

      $ rman target /
    Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 11 10:14:37 2015

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

    connected to target database: ORCL (DBID=1415405400)

RMAN> backup incremental level 0 as compressed backupset database;

Starting backup at 11-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/11/2015 10:16:58
RMAN-06056: could not access datafile 5

I have misplaced the datafile then I have added that datafile executing a touch command but still I was getting an error that the database is unable to lock the file. I put the database in mount mode then I took that file offline and after I started backup  there was the same error. So how can I solve this error without getting tablespace deleted


texts: Random characters after table name in FROM clause of SELECT query

texts:
I am rather new to SQL and database administration. I am maintaining/modifying a PHP application that interacts with Postgresql, and have encountered the statement:

SELECT * FROM client_messages c;

I do not understand the meaning of the final 'c' character. In fact, it seems like it is possible to have random characters after the table name in the FROM clause of a SELECT, without affecting the results of the query. Experimenting with the 'psql' utility, I find that these three queries all produce the same result, without any errors:

SELECT * FROM cltk2980dat c LIMIT 5;
SELECT * FROM cltk2980dat LIMIT 5;
SELECT * FROM cltk2980dat kdopwijfdwlhoeufhnekfhyufhf LIMIT 5;

Yet, the author of the program I am maintaining seems to have really wanted that final 'c' character in the SELECT query. I cannot find any explanation for this in manuals of SQL syntax, though I can find explanations of many other ways to modify the FROM clause of a SELECT.

Is there some significance to that final character? Will apparently random characters after the FROM table-name ever have any significance under some circumstances?


texts:
I am using the Desktop Vehicle Maintenance template in access, but i am trying to cater it towards our farms needs.

We will be keeping track of the service's done on Trucks, Tractors, Equipment, etc.
Each Vehicle will have different fields and different data tracked. But i can't figure out how to create a table for each entity and pull it into one centralized table to display service's done on all vechiles/equipment.


texts: How do I prevent my clients viewing my database definition when it is installed on their server?

texts:
I want to give my database to the client to install on their server.

However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run from a single IP address.

How do I stop the user viewing the database tables even though database is present on their server?

We want to give the application to run on their own servers, but we don't want to show the tables and stored procedures. They are also not supposed to make any modifications to the database. Is this possible?

Perhaps can we encrypt database credentials in the web.config file?


texts: How to check if specific number of rows exists without count(*)

texts:
I know we can check if at least one row of a table exists using EXISTS keyword, for example:
select * from A where exists(select from B where B.id = A.id)

What if I want to check if at least N rows exist, without using COUNT?
Performance matters to me. I don't want the whole table to be scanned to count matching rows. I just need my query to stop when N rows are found. I mean, the existence of at least N rows is what matters, and not the total count. That could save time if table is large.
Is there such feature in PostgreSQL?


texts: MongoDB: Can I have a delayed secondary that doesn't vote?

texts:
I have a replica set setup with 1 Primary, 1 Secondary and 1 Arbiter; each on a different data center.

I want to add a delayed secondary to one of the existing data centers, but I don't want it to participate in any election decisions.

I still want to have a total of 3 votes, so if the data center that hosts the delayed secondary (plus another member) goes down, my replica set would still elect a primary. Is this possible?


texts: impdp syntax for schema that is not available on other database

texts:
My intention is to import the data of schema 'SCOTT'. I have exported it from db1 using expdp scott/tiger schemas=scott dumpfile=scottexp.dmp ......
Now When I try to import the same in database db2 what would be the syntax. I don't have the SCOTT user in database db2(intentionally deleted to test). I tried with sysdba & system but unsuccessful.

impdp xxxxx schemas=yyyyy dumpfile=scottexp.dmp....

Please let me know the xxxxx and yyyyy values here.

Thanks in advance.


texts: add RECOMPILE for specific query server wide

texts: I've SQL 2017 and i have a query performance problem. i did try to play with all those trace flag (4199,4136,4137,9481)server wide and play with the indexes and stats and the query is still running slow. the only thing that resolve the issue is if i add the OPTION  (RECOMPILE) to the query. my problem is that i can't add it to query itself that comes from the ERP program.can i use it server wide for specific query?


texts: PostgreSQL bad plan: the frequency of a tag depends on the project!

texts:
I try to simplify a problem that I've been investigating for weeks.

Suppose that you have a large table like this:

SUBSCRIBERS
----------------------------
id | project_id | tags | ...
----------------------------
1  | 1          | ['en', 'something']
2  | 1          | ['en']
3  | 1          | ['de']
4  | 2          | []
5  | 2          [ []
...

Now suppose that project 1 has millions of rows and most of them have the tag en.

Also project 2 has millions of rows, but there isn't any row with the tag en.

The following query generates terrible execution plans:

SELECT * FROM subscriptions WHERE project_id = 2 AND tags @> ARRAY['en']::varchar[];

Indeed the planner sees the following:

project = 2 is very common
tag = 'en' is very common

Thus the planner thinks that there are many rows to return! However no rows match the actual condition.

That creates crazy choices in the indexes or even seq scans! I mean, I verified that it takes 20 minutes instead of a few ms, due to the wrong choices!

I have already tried the following (without any benefit):

SET STATISTICS 10000 for project_id and tags
CREATE STATISTICS on project_id, tags
SET random_page_cost = 1
make sure that the perfect indexes (compound, btree, gin, etc.) exist on project_id and tags
frequent ANALYZE and much more...

I think that the problem is that PostgreSQL should collect separate stats on tags for each project id. However it seems that there is no way to achieve that...  Any idea?

Also I think that CREATE STATISTICS doesn't work properly on array columns (maybe works on the whole column instead of the single values). Any specific information about this?


texts:
I have a few questions that I want to understand in order to take informed decision. I am not a DBA by profession, I have done some administrative tasks in the past and now I would have to create a maintenance plans to some of my new servers and understand the issues with my old servers.

My database log size is 50GB(converting MB to GB) and when I use the following query:

SELECT name,
   size/128.0 FileSizeInMB,
   size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS EmptySpaceInMB
FROM sys.database_files;

it returns 25GB as the Empty space consistently. My database is in FULL recovery mode with hourly log back up. Doesn't this log file get trimmed at some point in time after hourly transactional backup and reduce the size of the log file?
We have a defragmentation plan set up at night. This plan runs for 15 mins and rebuilds all indexes above 40% fragmentation level. The way the DBA from one of our vendors explain is that during the rebuild the tables are locked and hence make the log file grow. I don't understand this clearly.
Doesn't the log files grow in both rebuilding and re-organizing? Cant a long running re-organize fill up logs quickly?
How frequently should we update statistics after a daily index re-organize - immediately or weekly/monthly?


texts: How to install GDS plugin in a Neo4j virtual machine created in Azure?

texts:
I have a project working in my laptop. My data is in Neo4j desktop database. I also use the plugin Graph Data Science (GDS)
I would like to work in a Neo4j VM but I have to install GDS in the VM.
I looked here in the documentation: https://neo4j.com/docs/graph-data-science/current/installation/
But I don't understand how to install GDS in my VM deployed in Azure.
How do I have to do it ?
In my local machine I can install the plugin 'Graph Data Science' just by clicking on a button in Neo4j Desktop. In the Neo4j VM interface there is no place I can do this, so I was asking if there is an alternative to install this plugin in the VM.
It was advise to me this: You can download the specified JAR version of the GDS that matches your Neo4j version. At the moment if you use Neo4j 4.0.x you can download the 1.3.x version and if you use Neo4j 3.5.x you can download the 1.1.x GDS version at the release page: github.com/neo4j/graph-data-science/releases Then you need to add the following line to your neo4j.conf file and it should work: dbms.security.procedures.unrestricted=gds.*
But how can I access the file if it is a VM in Azure ?


texts: SQL Server 2005 upgrade service pack via command line

texts:
I have been wracking my brain over upgrading sql server 2005 to sql server 2005 sp4. I have a script for installing new instances of sql servers for all versions and they all work wonderfully. Problem is, sql server 2012 can't upgrade sql 2005 SP3 and below. So, I have to install up to sql 2005 SP4, AND THEN upgrade through sql 2012, which is highly annoying.

I need to do this silently via the command line, but I cannot get it to work at all. Here are some samples I tried:

 "SQLSP4.exe /qb /ACTION=Upgrade /INSTANCENAME="FOOBAR""
 "SQLSP4.exe /quiet /instancename="FOOBAR""

among a couple other variations. I could not find anything online at all. In fact, the one helpful thread ended in saying that Microsoft didn't have any support for this, but I'm not sure about that.

What can be done here? Does anyone have any experience in updating service packs via command line?


texts:
In a SELECT query of

SELECT b.id, MIN(IFNULL(a.views,0)) AS counted
FROM table1 a JOIN table2 b ON a.id=b.id GROUP BY id
HAVING counted>0

How can I turn this query to UPDATE as

UPDATE b.number = counted


texts: How do I stop psql from displaying executed SQL?

texts:
When I execute SQL, I see the query echoed back at me.

=# \d
LOG:  statement: SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN
  [... LOT OF STUFF CUT]
                   List of relations
 Schema |          Name          |   Type   |  Owner
--------+------------------------+----------+----------

All queries get echoed too,

=# SELECT * FROM ( VALUES (1) ) AS t(x);
LOG:  statement: SELECT * FROM ( VALUES (1) ) AS t(x);
 x
---
 1
(1 row)

How do I disable this feature? I've tried lots of things.


texts: Unable to create linked server from SQL Server 2008R2 to Azure SQL

texts:
I have created a couple of databases in Azure  and added them to an Elastic Pool.
I am able to create a linked server on SQL Server 2017. The issue I am faced with is when creating the linked server to Azure SQL i get the following screenshot error:

Can Anyone guide me on how to go on about this as I am a newbie to Azure SQL. I have read the following article:

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/f90fd20b-e9e1-42a1-a7ad-647e4c55afb1/linked-server-to-connect-to-azure-sql-database?forum=sqlgetstarted

Yet i can't seem to sort that out.

Much appreciated.


texts:
SELECT
    p.id
  , ig.NumReceived
  , og.NumShipped
  , coalesce(
        (p.StartingInventory - og.NumShipped + ig.NumReceived),
        p.StartingInventory
      ) as OnHand
  , p.ProductName
  , p.StartingInventory
  , p.MinimumRequired
FROM
  products p
        left outer join (
            select productid, sum(NumReceived) as NumReceived
            from incoming
            group by productid
        ) as ig on p.id = ig.productid
        left outer join (
            select productid, sum(NumberShipped) as NumShipped
            from outgoing
            group by productid
        ) as og on p.id = og.productid

I managed to get coalesce() to function correctly in this statement for (p.StartingInventory-og.NumShipped+ig.NumReceived) but my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well. By which I mean, retrieving NumShipped or NumReceived will display 0. coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived) works as it should but the line i'm referring to is ig.NumReceived, og.NumShipped

I've also tried IFNULL(). I've tried encompassing just the ig.NumReceived as well as the select statement. I'm using MySql 5.7.22


texts: creating and populating table using pl/sql

texts:
I have been looking for a way to create a table in pl/sql format like using DECLARE and BEGIN. Below is what i tried in sql Developer and but continiously getting the error below.

Please advise what am i doing wrong and if any good resource to learn on populating table using pl/sql? cheers!!

Error report : ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:***

begin case declare exit for goto if loop mod null pragma
raise return select update while with an identifier

Code :
set serveroutput on;
DECLARE
    v_customer_ID  VARCHAR(10) not null := 3025;
    v_customer_Name VARCHAR(15) :='Michel Jackson' ;
    v_room_code  VARCHAR(5) := 6536;
BEGIN
CREATE TABLE CUSTOMER
( v_customer_id VARCHAR2(10) NOT NULL,
  v_customer_name varchar2(15),
  v_room_code varchar2(5),
  CONSTRAINT CUSTOMER_PK PRIMARY KEY (v_customer_ID)
);
 END;
 /


texts: is it a good practice to ip block dev/test app servers from production database server?

texts: I would like to do this as somewhat of a last line of defense as we will be replicating production to test regularly.  Is this good practice?


texts: Why can a new user select from any table?

texts:
When I create a user in Postgres,

create user test with password 'test';

The user ends up being able to connect to every database in the instance.  The user can also select from any table:

# psql -h localhost testdb test
Password for user test: *****
psql (8.4.20, server 9.1.13)
WARNING: psql version 8.4, server version 9.1.
         Some psql features might not work.
Type "help" for help.

testdb=> select * from testtable;
 id
------------
      1
(1 row)

The \l database listing shows that testdb has empty Access privileges.  So why can a newly created user connect to that database?

The newly created "test" user can also select data from tables.  Why can he do so without a grant of select privileges?

I'd like to create a user that can only connect to one database and only read from certain views.  How can I prevent a user from reading directly from tables?


texts: SQL Server Demo Environment on Docker Desktop (Win 10): DB storage und upgrade paths

texts:
I would like to run SQL Server 2019 on my work as well as my private machine (both running Windows 10 Pro). Thus I thought Docker Desktop would be a nice option.

However I would like to keep the docker image itself minimal (just SQL Server and system databases) and store my demo databases elsewhere. I have tried to accomplish this using a data volume mounted from the host using the following command line switch.

-v C:\SQL:/sql

CREATE DATABASE does work using FILENAME "C:\sql..."

    CREATE DATABASE [test] CONTAINMENT = NONE ON  PRIMARY
( NAME = N'test', FILENAME = N'C:\sql\test.mdf' ,...

ATTACH does work  using Database File Location "/sql/..."

USE [master]
GO
CREATE DATABASE [CustomerOrders] ON
--( FILENAME = N'/sql/mssql/data/CustomerOrders.mdf' ), --generated by ssms -> wrong
(FILENAME = N'C:\SQL\mssql\data\CustomerOrders.mdf'),
( FILENAME = N'C:\SQL\mssql\data\CustomerOrders_log.ldf' )
 FOR ATTACH
GO

However RESTORE does only work with destination /var/opt/mssql and NOT with
destination /sql/ or C:\sql

Try 1

      USE [master]
    RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'C:\SQL\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQL\CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Try 2

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'/sql/CreditData.mdf',
MOVE N'CreditLog' TO N'/sql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

GO

Error Messages

    Msg 3634, Level 16, State 1, Line 2 The operating system returned the error '2(The system cannot find the file specified.)' while
    attempting 'RestoreContainer::ValidateTargetForCreation' on
    '/sql/CreditData.mdf'. Msg 3156, Level 16, State 5, Line 2 File
    'CreditData' cannot be restored to '/sql/CreditData.mdf'. Use WITH
    MOVE to identify a valid location for the file. Msg 3634, Level 16,
    State 1, Line 2 The operating system returned the error '2(The system
    cannot find the file specified.)' while attempting
    'RestoreContainer::ValidateTargetForCreation' on '/sql/CreditLog.ldf'.
    Msg 3156, Level 16, State 5, Line 2 File 'CreditLog' cannot be
    restored to '/sql/CreditLog.ldf'. Use WITH MOVE to identify a valid
    location for the file. Msg 3119, Level 16, State 1, Line 2 Problems
    were identified while planning for the RESTORE statement. Previous
    messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE
    DATABASE is terminating abnormally.

This one works (but is not what I want, as it is stored inside the docker image then):

USE [master]
RESTORE DATABASE [Credit] FROM  DISK = N'/sql/backup/CreditBackup100.bak'
WITH  FILE = 1,  MOVE N'CreditData' TO N'/var/opt/mssql/CreditData.mdf',
MOVE N'CreditLog' TO N'/var/opt/mssql/CreditLog.ldf',  NOUNLOAD,  STATS = 5, REPLACE

Another big difference is file growth on database files stored within the container and on files on the volume (resp. the host).
I created a test database with a 8 MB mdf file  in /var/opt/mssql/data and in /sql (my data volume). Afterwards I issued the following command

ALTER DATABASE [test2] MODIFY FILE ( NAME = N'test2', SIZE = 1048576KB )

This grows the file to 1 GB. In the location /var/opt/mssql/data it finished instantely while the resize operation took 8 seconds on the data volume /sql.

I tested for Instant File Initialization with traceflags 3004 and 3605 but didn't see any output regarding nulling the mdf file out on the data volum /sql.

Thanks in advance for your help

Martin


texts: How password can be prompted in percona xtrabackup

texts: How password can be prompted while using percona xtrabackup.--ask-pass option is not working with xtrabackup. Thanks in advance.


texts:
I am trying to create the following table in MYSQL and is giving errors,

CREATE TABLE CUSTOMER (
CUS_ID INT NOT NULL AUTO_INCREMENT,
CUS_NAME VARCHAR,
CUS_DOB DATE,
CUS_ADDR VARCHAR,
CUS_EMAIL VARCHAR,
CUS_TEL VARCHAR,
CUS_PW VARCHAR,
CUS_JOINDATE DATETIME,
CUS_LASTACCESS DATE)

#1064 - 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 ' CUS_DOB DATE, CUS_ADDR VARCHAR, CUS_EMAIL VARCHAR, CUS_TEL VARCHAR, CUS_PW' at line 3


texts: Select rows changed in current transaction

texts:
We have table name and CURRENT_TRANSACTION_ID().

How can we SELECT all rows INSERTed/UPDATEd in current transaction in MS SQL Server?

For example,

BEGIN TRANSACTION;
  EXEC some_big_procedure();
  --How to know what has been done inside some_big_procedure()?
ROLLBACK;

Note: original purpose is to test functions by comparing changes made by them with expected changes.

PostgreSQL has such mechanism (see this question):

BEGIN TRANSACTION;
  SELECT some_big_procedure();

  SELECT 'Changed row', t.*
  FROM my_table t
  WHERE t.xmin::text = (txid_current() % (2^32)::bigint)::text;
ROLLBACK;

Here txid_current() is analogue of CURRENT_TRANSACTION_ID(), t.xmin is built-in system column contains id of transaction which handles this row.

Bad solution is to write service function which will go through all the tables and add t.xmin's analogue. This column must be handled by triggers which are created dynamically by that service function too.

Any better idea for MS SQL?


texts: Is there a way to edit the System Database Directory for DB2 to remove a database?

texts:
We have a case where we want to try to get rid of a database. The database still exists on disk and when we catalog it, it re-appears in the Database Manager. But when we try to do a DROP on it, it complains that it cannot find the alias. If we do uncatalog it and try to re-create it, it complains that the database name or alias already exists. So, we are stuck where we can't drop it and we can't recreate it.

I believe the SQLDBDIR file is the System Database used by DB2. Is there a way to edit this safely to get the entry out of there? Or is there another file we could edit, or a command we could run (outside of the standard DROP/CATALOG/UNCATALOG commands) that would get us in the clear?

Any help here would be great as we've been coming across this one more and more with our installed apps. (It arises from disk errors on laptops and we go to try to reinstall the database on a different section of disk.)


texts:
I am designing database for an web application, I have designed the product and specification details database as follows,

create table pr_size
(
    size_id             int not null auto_increment,
    value               varchar(256) not null default 'No Size Specified',
    is_active           bit(1) default 1,
    primary key (size_id)
);

create table pr_color
(
    color_id            int not null auto_increment,
    color_value         varchar(256) not null default 'No color specified',
    is_active           bit(1) default 1,
    primary key(color_id)
);

create table pr_measurement_unit
(
    mes_unit_id         int not null auto_increment,
    unit                varchar(10),
    is_active           bit(1) default 1,
    primary key (mes_unit_id)
);

create table pr_dimension
(
    dimension_id        int not null auto_increment,
    mes_unit_id         int not null default 0,
    dimension_value     char(10) not null default 'No Value',
    is_active           bit(1) default 1,
    primary key (dimension_id),
    foreign key (mes_unit_id) references pr_measurement_unit (mes_unit_id)
);

create table pr_product_features
(
   product_features_id  int not null auto_increment,
   brand_id             int not null default 0,
   color_id             int not null default 0,
   size_id              int not null default 0,
   dimension_id         int not null default 0,
   is_active            bit(1)  default 1,
   primary key (product_features_id),
   foreign key (brand_id)   references pr_brands(brand_id),
   foreign key (color_id)   references pr_color(color_id),
   foreign key (size_id)    references pr_size(size_id),
   foreign key (dimension_id)   references pr_dimension(dimension_id)
);

create table product
(
   product_id           int not null auto_increment,
   product_name         varchar(256) not null default 'No product name specified',
   ........
   ........
   is_active            bit(1) default 1,
   primary key (product_id)
);

create table pr_product_feature_applicability
(
   product_feature_applicability_id int(11) NOT NULL AUTO_INCREMENT,
   product_id int(11) NOT NULL DEFAULT '0',
   product_feature_id int(11) NOT NULL DEFAULT '0',
   dated_from datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   dated_thru datetime NOT NULL DEFAULT '9999-12-30 00:00:00',
   is_active bit(1) NOT NULL DEFAULT b'1',
   primary key (product_feature_applicability_id),
   key product_id (product_id),
   key pr_product_feature_idx (product_feature_id),
   constraint pr_product_feature FOREIGN KEY (product_feature_id) REFERENCES           pr_product_features (product_features_id) ON DELETE CASCADE ON UPDATE CASCADE,
   constraint pr_product_feature_applicability_ibfk_1 FOREIGN KEY (product_id)  REFERENCES product (Product_id)
);

This design seems good to me (I understood this design from some internet materials), but I am facing some issues.

Consider the following table values,

In front end we are confused because,

For a size there are option for multiple colors and brands.
For a color there are option for multiple sizes and brands.

Developers do understand the logic but it is confusing to write sql query to insert values to the pr_product_feature_applicability table. Since while we choosing multiple colors there are different possible ids for a single size, how to insert multiple color id's in a pr_product_feature_applicability table with a single insert statement.


texts: Does rowlocks on index cause keylocks? : Transaction (Process ID xy) was deadlocked, example included

texts:
I am currently experiencing the error (logged via traceID 1222 ):

keylock hobtid= 8205698989 objectname=mydb.dbo.Orders indexname = _dta_Index_Orders_5_120345_K1_K2_7_1 id=lock43fe181 mode=S AssociatedObjectID = 8205698989
waiterID = process 85b2566 mode=S requestType=wait
waiter-list
keylock hobtid= 820562208 objectname=mydb.dbo.Orders indexname = pk_Orders_OrderID id=lock4012341 mode=X AssociatedObjectID = 820562208
resource-list
Update Orders set OrderType = 1 where OrderID = 52000 and DeliveryID = 1236
inputbuf
Update [Orders] set [OrderType] = @1 where [OrderID] = @2 and [DeliveryID] = @3

Table:

Orders

Columns:

OrderID         int (pk)
OrderNumber     Varchar(50)
OrderDate       datetime
OrderType       int
DeliveryID  int
DateDelivered   datetime
CustomerID  int

Index:

There is a Clustered Index on the PK:

OrderID         int

name pk_Orders_OrderID

The one causing the issue: (I got it by getting the object from AssociatedObjectID  )

_dta_Index_Orders_5_120345_K1_K2_7_12: (nonclustered)

Index Key Columns:

OrderNumber     Varchar(50)
OrderID         int (pk)

Included Columns:

OrderDate       datetime
OrderType       int

Now option "use page locks when accessing the index" caused a lot of pagelocks, so it was unchecked.
The "Use row locks when accessing the index" is still on.

But I am getting the above error as mentioned, and the funny thing is that when customers sometimes run a lengthy report, the keylock occurs
even though the specific report is for Customer A, and the update happening to Orders is for Customers B - thus the CustomerIDs don't match and therefore the should not be no lock, or am I mistaken?

The keylock word in question - is this directly related to the "use row locks when accessing the index"?

Do row locks cause keylocks?

Would it be that the deadlock message:
Transaction (Process ID xy) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
was caused when the _dta_Index_Orders_5_120345_K1_K2_7_12 index was scanning, and at that stage it happend to scan on orderID 52000 (even though OrderID 52000 is not in the resultset)?

I am stunned and have no clue now.
Thank you


texts: Unique Constraint exception after dropping constraint

texts:
We had dropped a unique constraint using the ALTER TABLE DROP CONSTRAINT script, but it is still throwing the Unique Constraint exception while trying to insert. We drilled this down to two possible reasons.

Having the Unique Constraint with the same name under other schemas. We had deleted from USER_CONSTRAINTS so that it is deleted from the current scheme. Do we need to delete it from ALL_CONSTRAINTS? This is the only difference from the Test and Development Environment, where it worked fine.
Not dropping the index. This was not carried out in the development environment, but still the Unique Constraint Exception was not thrown.

We are using Oracle 11g. Suggestions welcome.


texts: Are there any tools to go from ERD to DB design and vice versa?

texts:
I'm learning DB design and looking for some tools, preferably open source software, that could help go from an ERD to a DATABASE and vice versa.

For the record, the database i'm using is MySQL.


texts:
Help in sizing oracle 10g database sga
We've an Oracle 10g r2 database, size = 4.5TBASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system caching).Server Configuration is Intel Xeon 4CPU (8core per cpu) i.e total 32 cores; Total Internal Memory is 128GB. Operating System is Solaris 10 update 9.

The SGA size is 80GB, if we increase the SGA to 84GB~85GB the cpu utilization shoots upto 99%~100%. With 80GB SGA the cpu utilization is 20%~50% depending upon the load.

What is the relation between cpu cores and sga size? I used 4GB memory per core, is this a correct estimate for the Intel Xeon platform
Why does the cpu util shoot to 100% with an increase in SGA?Is there a rule of thumb for sizing the oracle sga for oltp environment?

vmstat output:
r   b   w   swap          free         sr   s0 s1 s2 s3 us
0   26  27  128146676   6448872 115 0   3   0   94  19
1   74  31  123050360   4031544 0   0   2   0   56  16
0   83  31  122930884   3921080 0   0   0   0   31  10
1   75  31  122934416   3921480 0   0   0   0   61  13
0   78  31  122787016   3802200 0   0   0   0   104 25
0   84  31  122595368   3626344 0   0   0   0   110 22
0   91  31  122717320   3759480 0   0   0   0   108 25
0   88  31  122831348   3889744 0   0   6   0   111 27
0   79  31  122581408   3647472 0   0   0   0   101 44
1   84  31  122306816   3388964 0   0   0   0   71  27

The "sr" column reports zero for most of the entries (but the first where it shows a high value 115)Moreover the vmstat shows an unusually high blocked and swapped out processes.


texts: Am I using the correct data types on SQL Server side to call an Oracle 10g function?

texts:
When calling an Oracle function with the following in parameter types

NUMBER(7,0)
VARCHAR2(8 BYTE)
VARCHAR2(4 BYTE)
DATE

and return type NUMBER(14,5) from SQL Server 2008R2 via a linked Oracle server, I declared the parameter variables on SQL Server side as follows:

numeric(7,0) -- also tried INT, BIGINT
varchar(8)
varchar(4)
datetime -- also tried DATE

and return variable type numeric(14,5)

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price, @id1, @id2, @curr, @ed ) AT ORASRV

The call fails with the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1".

What am I doing wrong?

To test that function I used the following PL/SQL script and it worked fine (printed 0 which is a correct result for the parameters):

declare
  c varchar2(4);
  ed date;
  res number(14,5);
begin
  c := 'USD';
  ed := to_date('22-MAY-14', 'DD-Mon-YYYY');
  res := pkg.getPrice(214360, '129746', c, ed);
  DBMS_OUTPUT.PUT_LINE(res);
end;

Also created a function just for testing:

create or replace FUNCTION getTest (
    a IN VARCHAR2 -- also tried INTEGER
)
RETURN  VARCHAR2 -- also tried INTEGER
IS
  res VARCHAR2(1); -- also tried INTEGER
BEGIN
  res := a;
  RETURN res;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END;

When I call it from SQL Server as follows

declare @a varchar(1),@b varchar(1) -- also tried INT
select @b = 'a'
exec ( 'BEGIN ? := db.getTest(?); END;', @a, @b) AT ORASRV

I get the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1".
Msg 7215, Level 17, State 1, Line 172
Could not execute statement on remote server 'ORASRV'.

In case of INTEGER on Oracle side and INT on SQL Server side the message is just "ORA-06502: PL/SQL: numeric or value error


texts: Is it possible to use Native Clients from previous releases to connect SQL Server 2012?

texts: Is it possible to use SQL Server Native Clients from previous SQL Server releases (e.g. SQL Server 2008 R2 Natice Client) to connect SQL Server 2012 database?


texts:
I have a table which has around 50 columns. But there are only 6 columns on which we perform regular search. When I am trying to create a non clustered index on it I am getting the warning of 900 bytes. This would be a problem as we also do bulk update/inserts. So can onyone help me in finding a solution?

If I put few columns out of those 6 in INCLUDE, will that cause any performance issue?

Lets say the datatypes for the columns are -

     ColumnA (nvarchar(255),null)
     ColumnB (Int, null)
     ColumnC (nvarchar(255),null)
     ColumnD (Datetime,Null)
     ColumnE (nvarchar(255),null)
     ColumnF (nvarchar(Max),null)

Primarily these columns are used in WHERE to pull out data.

Thanks,
Unmesh


texts: Redis master and slave use different memory

texts:
I deployed a redis replica set which consist of one master and one slave.

From top command output, I figure out each memory usage.

master: 8176352Byte * 8.5% = 678MB

slave: 2050116Byte * 75.6% = 1513MB

Why does slave use much more memory than master?

master top output:

...
KiB Mem:   8176352 total,  7746300 used,   430052 free,    55048 buffers
KiB Swap:  8385892 total,  2360512 used,  6025380 free.  1279644 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
32609 redis     20   0 1717424 696708    928 S   0.3  8.5 304:22.75 redis-server
...

slave top output:

...
KiB Mem:   2050116 total,  1977052 used,    73064 free,     3620 buffers
KiB Swap:  2096444 total,   259100 used,  1837344 free.   165552 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
 1675 redis     20   0 1725616 1.477g    672 S  0.0 75.6   0:23.03 redis-server
 ...


texts: Where can I get a proper dataset for performing Normalization techniques?

texts:
I'm new to databases and I just started with learning different techniques of Data Warehousing. I was given an assignment to perform Normalization techniques on a dataset of at least 1000 rows with minimum of three tables.

I'm not able to find any such data for the same.

I tried data.world, kaggle and uci but all in vain. Also, can I perform the normalization techniques using MySQL Workbench? Please help me out.


texts: MS SQL Row-Level-Security out of a Citirx Store Front

texts:
I’m looking for either a solution, or a definitive answer that what I want to do can’t be done.
My situation:
I have a MS Access Front End that is located in a Citrix Store Front.
The Front End connects to a 2017 MS SQL Server Backend.
The server backend is configured to use Row-Level-Security as described in this article.
Out of the Citrix Store Front, my Access Front End connects to the server, but the row-level-security only recognizes the user and password that I send via the access connection string, not the user that opens the Access Front End. This makes sense to me, since I’m passing a User ID and Password via the connection string.
The idea of having the MS Access Front End in the Citrix Store Front, is that our IT does not want to open up the firewall for each individual user, and that thus we can use one connection string for everyone.
My Question:
Given that this is the case, can I still implement Row-Level-Security, or is it not possible under the one connection string scenario?


texts: How To Add Days And Time For Instructor Schedule

texts:
Problem

I need to make a database design (ERD) to display a schedule for every instructor, including the following:

day of course (e.g. Saturday or Monday)
time it will give course (period from 12 PM TO 03 PM)
what course it will give (C# or SQL)

Example

Instructor Martin gives course C#, Monday and Saturday, for period 12 pm - 03 pm, and course SQL on Sunday and Wednesday for period 12 pm - 03 pm.

Details

I designed the following tables:

Instructors table (InstructorID, InstructorName)
Courses table (CourseID, CourseName)
Instructors_courses table (instcourseID, InstructorID, CourseID)

Relationship between Instructors table and Courses table is many to many so I do another table Instructors_Courses.

Questions

How to represent days and times for every course added

Can I add table for time and table for days and make relation with Instructors_courses table by adding day id and time id (one to many)

OR do it programming from user interface.
course start date and course end date these two fields how to represent in table Instructors_courses.

I can added but it will repeated with every course are this correct or what.


texts: How to recover the data from Redo Log Group which has not been archived yet

texts:
I have a normal situation where we have 3 Redo Groups (A,B & C) with 1 member (100 MB) each and database is also in Archive log Mode.

In normal scenarios whenever there is redo log switch, there will be a archive file generated and if we have all backups and archive files we can have our database recover till the point we have archive logs.

My question is - when writing to redo log group C (A & B has been archived already), let say 50 MB is written and database crashes, so definitely C group has not been archived yet.

So is it like, while recovering my database again on a different server, i will be losing 50 MB of data? or my understanding is wrong.

Is there any way we can also get that 50 MB of data somehow?


texts: Is it possible for heroku postgres follower database to follow non-heroku postgres master?

texts:
I am considering to move our site's database from our dedicated db server to heroku postgres. as we have a few gigs of data in the db and don't want to take it down for a long migration downtime it appeared to me that it might be possible to set up replication(following in heroku terms) from our current database server to a heroku 'follower' database. We could then make our current db read-only for a short time while the heroku comes up to date and then make the switch very quickly.
the heroku documentation(https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#database-upgrades-and-migrations) mentions replication between databases of different heroku apps, but it does not touch on the scenario i want to set up where the master db is not on heroku.
Anyone thinks or knows this is doable?


texts: Why am I getting ORA-00937 error using only aggregate functions?

texts:
Here is a sample of what I'm trying to do:

SELECT
  COUNT(T1.FIELD) +
  (
    SELECT
      COUNT(T2.FIELD)
    FROM
      TABLE2 T2
  )
FROM
  TABLE1 T1

I don't see a need for group by clause in here.


texts:
We have an in-house application, developed in WinDev, that uses a Microsoft SQL Server 2012 SP2 (build 11.0.5058) database running on Windows Server 2008R2.

We are experiencing performance issues. At the same time Activity Monitor shows a huge number of waits of type "network I/O", where "huge" in this case is a cumulative wait time of 7 million seconds for a server that is up for less than 6 days:

The network connectivity itself is fine, the server is connected with 10G while the client are either physical desktop connected with 1Gb or Remote Desktop servers with 10G. Network monitoring shows no link saturation, nor any other network issue. CPU, RAM and disk I/O are also fine.

From what I read about Network I/O waits, it relates to records returned by a query that are not consumed by the clients. So I tend to think the issue is in the application but I have a hard time having the developers investigate this (not that they are unwilling to do it, but they are quite busy and it seems they don't have any clue about the root cause and how to solve it)

So the questions:

Am I right thinking the performance issue is related to those Network I/O waits?
what clue could I provide to the dev team to help them identify the cause?
apart from fixing the application, is there some fine tuning I can do on the SQL Server itself to alleviate the issue?


texts: How to maintain customer addresses for Orders?

texts:
The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules":

Customers must either be Individuals or Businesses (a business belongs to a certain type).
A Customer must place at least one Order.
Orders must contain their historical shipping address (i.e. if a customer updates their address, any Orders associated with the Customer must reference the old address).

I have come up with the following design based on the above requirements:

However, there is a problem with normalization.
In order for the 3rd requirement to be met, I am saving the shipping address into the Order at the time of creation. Additionally, I am referencing an address in the Customer table (which is potentially the same).

In the bottom right of the image, I extracted the Address-related attributes into its own table as the first step in my attempt to normalise the design.

Over the past week, I have searched for hints without much luck.
I know about this post and this one, yet the implementation is rather ambiguous for my scenario/case.

The thought of making a single table with all addresses, then referencing each as required has crossed my mind but doesn't seem like the right (let alone best) way of achieving this goal.

My question is simple:

How do I meet the 3rd requirement of associating an address with a Customer and an Order historically, with normalisation in mind?

Any help is very much appreciated. Cheers.


texts: PL/SQL : query numeric data type by scale?

texts:
Wish to select rows with number fields having a certain amount of data after the decimal

eg

select number from table where number.scale > 4;

Is this possible to do directly, or must I cast to string and parse character wise


texts: is automated data movement via Excel considered ETL?

texts: I am wondering if informal methods of automatically extracting, transforming, and loaded data constitute "ETL." For example, I code Excel VBA to extract from databases onto a spreadsheet, transform the data into the desired format, and load by moving the Excel file to the correct folder. Would it be incorrect to showcase this as ETL for resume? Is there a term or lingo for this?


texts: Read Scale Availability Groups and Listener

texts:
Is it possible to use a listener with a read scale availability group?

I have read on some sites you cannot use one but in others that you can.

If so, what IP address do you use for it?  I have tried to assign a new one in the subnet and also using the IP of the VM with the primary replica.  I can create the listener but cannot connect to it.

I am interested in using the listener to only connect to the primary replica, I am not planning to use read-only routing. My idea to use a read scale AG is to provide a limited degree of DR. I understand the listener would have to be redeployed if the AG was failed over.


texts: Unexpected SLAVE STATUS after CHANGE MASTER

texts:
I'm trying to setup MySQL replication but I am getting duplicate keys when I start the slave.

I've got master and relay.  I've also got backup, which is intended to be slaved to relay, but my issue is on relay.  I create a dump from master using the following:

mysqldump \
    --all-databases \
    --flush-logs \
    --single-transaction \
    --master-data \
    | gzip > dump.sql.gz

This dump includes something like the following:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120;

I then import the dump into relay, and execute the following:

FLUSH PRIVILEGES;
CHANGE MASTER TO
    master_host='master',
    master_user='username',
    master_password='secret';
START SLAVE;
SHOW SLAVE STATUS\G

I'm getting what I find to be unexpected results from the slave status:

Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 800917736
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 635
Relay_Master_Log_File: mysql-bin.000001
Last_Error: Could not execute Write_rows event on table myschema.mytable; Duplicate entry '5191176' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 778

A few things I don't understand:

how does it have a duplicate key, when I just pulled in a fresh dump?
why is it loading from mysql-bin.000001 when the CHANGE MASTER and Master_Log_File are mysql-bin.000012?
how has it progressed to Read_Master_Log_Pos: 800917736 if it encountered an error on mysql-bin.000001?


texts: SQL Server 2012 - How to view the locks' log?

texts:
I'm using SQL Server 2012 Express Edition, may I know how can I view all the lock/block processes which were recorded in my database? The reason I want to view the previous locked/blocked processes is SQL Server only allows us to view the current locked process in the database.

I found that I have few timeout errors in my SQLException log file, so I would like to know is there a way to view or query the past records on the locks/blocks that caused the time out.

And I did not turn on my blocked process report for my database T.T


texts: Oracle HIPAA/FIPS-Compliant Encrypted Connections

texts:
I'm currently evaluating encrypting data in-transit for an Oracle SE2 12.1 Amazon RDS instance in accordance with HIPAA requirements.
Specifically, I'm considering the use of Oracle Native Network Encryption [1,2] (the alternative option being Oracle SSL [3,4]). I'm trying to piece together the documentation from Oracle, Amazon, HIPAA, NIST, etc. to determine the appropriate HIPAA-compliant approach.
From Amazon's HIPAA Whitepaper [5]:

Connections to Amazon RDS for Oracle containing PHI must use transport encryption and evaluate the configuration for consistency with the Guidance. This is accomplished using Oracle Native Network Encryption and enabled in Amazon RDS for Oracle option groups.

From the HIPAA Guidance [6]:

Valid encryption processes for data in motion are those which comply, as appropriate, with NIST Special Publications 800-52, Guidelines for the Selection and Use of Transport Layer Security (TLS) Implementations; 800-77, Guide to IPsec VPNs; or 800-113, Guide to SSL VPNs, or others which are Federal Information Processing Standards (FIPS) 140-2 validated.

Based on this doc, it seems that the Oracle network encryption must be FIPS 140-2 validated. However, I'm not certain whether the Oracle network encryption meets this criteria; I haven't quite been able to confirm this from the Oracle/FIPS/other docs.
In particular, I've found the following Oracle 12.1 documentation related to FIPS 140-2:

The FIPS 140-2 cryptographic libraries are designed to protect data at rest and in transit over the network.
Oracle Database uses these cryptographic libraries for Secure Sockets Layer (SSL), Transparent Data Encryption (TDE), and DBMS_CRYPTO PL/SQL package.
To configure Transparent Data Encryption and the DBMS_CRYPTO PL/SQL package program units to run in FIPS mode, set the DBFIPS_140 initialization parameter to TRUE.
You can configure the SSLFIPS_140 parameter for Secure Sockets Layer.

However, given the documentation, I'm not sure that these FIPS parameters apply to the Oracle native network encryption, nor whether they are necessary.

Is Oracle native network encryption [1,2] HIPAA/FIPS compliant?
If so, is any configuration required or is it compliant by default?
Are there certain encryption algorithms that are/are not compliant?

[1] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html
[2] http://docs.oracle.com/cd/E11882_01/network.112/e40393/asoconfg.htm#ASOAG9581
[3] http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.SSL.html
[4] http://docs.oracle.com/cd/E11882_01/network.112/e40393/asossl.htm#ASOAG9689
[5] https://d0.awsstatic.com/whitepapers/compliance/AWS_HIPAA_Compliance_Whitepaper.pdf
[6] https://www.hhs.gov/hipaa/for-professionals/breach-notification/guidance/index.html


texts: how mongo db does locking for updates/inserts?

texts:
I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them:
1. keep all user's data in one collection that is much easier, and
2. keep every user's data in a separate collection;
Now i need to know is a locking battle-neck for parallel writes in first option or not, while my updates/inserts are many but on one by one (there is no need to update/insert many (multi: true).
does mongo lock the whole collection for an updateOne/insertOne (multe: false) or only does it for updateMany/insertMany (multi: true) cases?


texts:
I want to compute the number of columns with not null constraint in my database. So I thought about querying the table sys.all_columns:
select * from sys.all_columns

The problem is that it provides me with extra information I dont need (columns in system defined tables).

So I have to add some SQL code to determine whether some column belongs to User Defined Table or not. What is the best way to proceed ?


texts: Calculating percentage of a row over total sum

texts:
Apologies for the bad title, I wasn't sure what would be a good title for this.

This is currently (simplified view of the) data I'm working with

Agent    |  Commission
---------|------------
Smith    |    100
Neo      |    200
Morpheus |    300

I need to calculate the percentage of the total commission, each agent is responsible for.

So, for Agent Smith, the Percentage would be calculated as (Agent Smith's commission / Sum(commission)*100

So, my expected data would be

Agent    |  Commission   |  % Commission
---------|---------------|---------------
Smith    |    100        |     17
Neo      |    200        |     33
Morpheus |    300        |     50

I have a function returning the commission for each agent. I have another function returning the percentage as (Commission/Sum(Commission))*100. The problem is that Sum(commission) gets calculated for each and every row, and given that this query would be run on a Data Warehouse, the data set would be rather large ( currently, it's just under 2000 records) and quite honestly, a bad approach (IMO).

Is there a way of having the Sum(Commission) not calculate for every row being fetched ?

I was thinking something on the lines of a 2 part query, the first part would fetch the sum(commission) into a package variable/type and the second part would refer to this pre-calculated value, but I'm not sure how I can accomplish this.

I am limited to using SQL, and I'm running on Oracle 10g R2.


texts:
I am writing a query to run against the SCOM 2012 OperationsManager database. SQL instance is Microsoft SQL Server 2012 - 11.0.5058.0 (X64).

Within the database is a table called Monitors. The schema of the DB can be found here: Link to Schema

I can run the following query:

select monitorid, configurationxml
from dbo.monitor
where monitorid = '4960E39A-59C8-A2C2-99B1-59B73D73156F'

and it returns this:

monitorid                               configurationxml
4960E39A-59C8-A2C2-99B1-59B73D73156F    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName><DiskLabel>$Target/Property[Type="Windows!Microsoft.Windows.LogicalDevice"]/DeviceID$</DiskLabel><CounterName>PercentFree</CounterName><IntervalSeconds>900</IntervalSeconds><NumSamples>4</NumSamples><SystemDriveWarningThreshold>10</SystemDriveWarningThreshold><SystemDriveErrorThreshold>5</SystemDriveErrorThreshold><NonSystemDriveWarningThreshold>10</NonSystemDriveWarningThreshold><NonSystemDriveErrorThreshold>5</NonSystemDriveErrorThreshold>

(so the Monitor GUID and XML in the column configurationxml

I can then run this query:

Select
    D.C.value('IntervalSeconds[1]','varchar(4000)') CheckInterval,
    D.C.value('NumSamples[1]','varchar(4000)') SamplesBeforeDown,
    D.C.value('SystemDriveWarningThreshold[1]','varchar(4000)') SystemDriveWarningThreshold,
    D.C.value('CounterName[1]','varchar(4000)') Countertype
    FROM (Select n.c.query('.') as xmlquery
    from
     (Select cast(ConfigurationXML as xml) Recxml
     FROM [dbo].[monitor] mt)
    a Cross Apply Recxml.nodes('/') N(C)) r
    Cross Apply xmlquery.nodes('/') D(C)

which shreds (not sure if that is the right term) the XML and allows me to retrieve specific element values against that Column:

CheckInterval   SamplesBeforeDown   SystemDriveWarningThreshold Countertype

          900                   4                            10 PercentFree

The problem I have is that I would like to be able to pull specific element values linked to the GUID in that specific row so that I can then have the GUID and the xpecific XML element values that I want, something that would look like this:

monitorid                            CheckInterval  SamplesBeforeDown   SystemDriveWarningThreshold Countertype

4960E39A-59C8-A2C2-99B1-59B73D73156F           900                  4                            10 PercentFree

The main problem I am encountering is that when I shred the XML it loses its reference to the row it came from.

How can I link the XML content to the row it is coming from?


texts:
When I execute this command: npm install db-mysql, I get the following result.  What's the solution to this problem?

C:\Users\Mouad>npm install db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm http GET https: //registry.npmjs.org/db-mysql
npm ERR! Error: SELF_SIGNED_CERT_IN_CHAIN
npm ERR!     at SecurePair.<anonymous> (tls.js:1283:32)
npm ERR!     at SecurePair.EventEmitter.emit (events.js:92:17)
npm ERR!     at SecurePair.maybeInitFinished (tls.js:896:10)
npm ERR!     at CleartextStream.read [as _read] (tls.js:430:15)
npm ERR!     at CleartextStream.Readable.read (_stream_readable.js:294:10)
npm ERR!     at EncryptedStream.write [as _write] (tls.js:344:25)
npm ERR!     at doWrite (_stream_writable.js:211:10)
npm ERR!     at writeOrBuffer (_stream_writable.js:201:5)
npm ERR!     at EncryptedStream.Writable.write (_stream_writable.js:172:11)
npm ERR!     at write (_stream_readable.js:547:24)
npm ERR! If you need help, you may report this log at:
npm ERR!     <http: //github.com/isaacs/npm/issues>
npm ERR! or email it to:
npm ERR!     "npm-@googlegroups.com"

npm ERR! System Windows_NT 6.1.7601
npm ERR! command "C:\\Program Files\\nodejs\\\\node.exe" "C:\\Program Files\\nodejs\\node_modules\\npm\\bin\\npm-> cli.js" "install" "db-mysql"
npm ERR! cwd C:\Users\Mouad
npm ERR! node -v v0.10.4
npm ERR! npm -v 1.2.18
npm ERR!
npm ERR! Additional logging details can be found in:
npm ERR!     C:\Users\Mouad\npm-debug.log
npm ERR! not ok code 0


texts:
I have a users table with various fields which are getting updated frequently.
i.e table have:

userid -  PRIMARY int
profile_views - int
ratings - int
last_active - int ( to store UNIX timestamps for sorting purpose)

I also have an index on last_active to get the last active users. When user logins or performs a certain action last_active gets updated and same with profile_views and ratings.

My question is does mysql reindex all table ro reindex index last_active when I update last_active index value or update profile_views or ratings?

table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(80) NOT NULL,
  `salt` varchar(40) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `detail` varchar(500) NOT NULL,
  `genre_id` int(2) NOT NULL,
  `category` tinyint(1) NOT NULL,
  `profile_views` int(11) NOT NULL,
  `ratings ` int(11) NOT NULL,
  `last_active` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `genre_id` (`genre_id`),
  KEY `last_active` (`last_active`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4059 ;

and second table to join to

CREATE TABLE IF NOT EXISTS `users_genre` (
  `userid` int(11) NOT NULL,
  `genre_id` int(11) NOT NULL,
  KEY `userid` (`userid`),
  KEY `genre_id` (`genre_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Thanks


texts: Two substantially different but similar cases: Are they the same in terms of table cadinality?

texts:
Imagine these different cases when designing a database:
Case 1:
One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during a time period.
Case 2:
Multiple instances of the same book physically (same title) can be borrowed by many people at the same time (obviously) in the one and same library only as before.

Would these two cases produce the same result when designing a relational schema?
If someone comes up with a better title for this question I will gratefully accept a change.


texts: Prevent THREADPOOL waits due to idle worker thread trimming

texts:
After reading Unusual THREADPOOL Waits by Josh Darnell, a Twitter user mentioned there is an undocumented trace flag to prevent trimming idle workers:

The idea is that once SQL Server has created enough threads to service the peak workload, it should not then trim worker threads (release them to the OS) after 15 minutes or so of them not being needed.
The idle worker threads will continue to use resources (e.g. memory) but there will not be the burst of THREADPOOL waits when more workers are suddenly required. Apparently this can be of assistance when using Always On Availability Groups.
What is this undocumented trace flag, and how does it work?


texts: 11g dataguard broken after primary ran out of space

texts:
I have Data Guard setup with Primary (A) and read-only standby (B).

primary server (A) ran out of space on the data mount point as auto extend was turned on. I managed to revive it by adding additional files to tablespace on another mount point (there were existing data files on here already, looks like it was added at some point as a stop-gap to give db more space). There are matching mount points on (B) with some other data files there already. The StandbyFileManagement is set to auto.

The standby was erroring with ORA-16766: Redo Apply is stopped.
I ran the following on the standby:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

Got the following error:

ORA-10458: standby database requires recovery;
ORA-01157: cannot identify/lock data file 71 - see DBWR trace file
ORA-01111: name for data file 71 is unknown - rename to correct file
ORA-01110: data file 71: '/u00/oracle/product/11.2.0/db_2/dbs/UNNAMED00071'

Could someone suggest what the best way to recover from this would be? is it easier just to recover by re-initalizing Data Guard? how would I do that?


texts:
I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main dish could be a pizza, or some kind of pasta, or other. So a pizza could have many pieces and a pasta dish could be requested with a different kind of pasta as a customer wishes. On top of that you can add extra ingredients or remove some in every dish, which ingredients come to a cost and a quantity in the database which should be updated per order completion.

So should make an ISA relationship and separate my dishes to a pizza, pasta category, from there I could have the main pasta used in the dish and custom size for the pizza. In this scenario when placing an order the kind of pasta should be specified if different as well as the pieces in the pizza. Afterwards the ingredients and price would be multiplied by a constant factor proportional to the number of pieces, in that way avoiding storing all the possible combinations in the database.

Would this be a good design, what else could it be done to avoid data replication and have a readable order table? Any help would be much appreciated.


texts: How to create a versioning DDL/DML change script for SQL Server?

texts:
I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the current version of the schema, and I want to create a single script that upgrades the schema from any earlier version to the current version.

Something like this (pseudocode):

if (select v from version) = 1
    create table newtable ...
    update version set v = 2
end
if (select v from version) = 2
    alter table newtable add column newcolumn ...
    update newtable set newcolumn = ...
    update version set v = 3
end
if (select v from version) = 3
    ...
end
...

There are many problems here.

Mixing DDL and DML can lead to problems, like adding a new column and trying to update it in the same batch will cause an error that the column doesn't exist. So I thought that I should always separate them into different steps, and each step should be a different batch. Of course, every change step will end in a DML to change the version number, but that's okay. There should be transactions inside the DML batches, and let's not worry about the DDL batches.

I also want to make sure that during one execution, only one step is executed, because error handling could be a nightmare. Stopping the script after one step is also not trivial, neither in SSMS nor in SQLCMD. In my above sample, if we start at version 1, it will be updated to 2, then 3, then 4, etc. So I thought I would reverse the order of changes. First comes the change from 3 to 4, then from 2 to 3, then from 1 to 2, this way, only one step is executed at a time.

Does this look okay? Are there other things to consider?


texts: How to distribute/release/publish a database schema

texts:
I want to distribute/release/publish a database schema to multiple 3rd parties (e.g. I'll produce a zip for them to download and they'll set up their database using it). They aren't all using the same RDBMS.

Is there a standard way to do this? Can I use an accepted subset of SQL?

Note that I'm not distributing an application (just the data model) so can't use any database agnostic libraries to set the db up.


texts: Calculation of two dependent variables in Access Query

texts:
I'm having problem constructing below two variables in Access Query or Access SQL which are dependent on each other, please kindly help!

a(n)=b(n)+c(n);
b(n)=if(a(n-1)<>0,a(n-1),d(n)).

n refers to the current record while n-1 refers to the previous record.

Here's the sample data:


texts:
Our monitoring tools are showing SQLVDI errors, I think they're coming from the Event Viewer only. But the backups are showing successful in the SQL Server log files and we've successfully restored the backups.

The events look like this:

Event Log Module Status: 0
The Last Record Number of the eventlog type that current event entry belongs to: 0
# of duplicate events: 20.00
Events Source: SQLVDI
Category: None
Event ID: 1
User (If Applicable): N/A
Event Description: SQLVDI:
Loc=TriggerAbort.
Desc=invoked.
ErrorCode=(0).
Process=3056.
Thread=7460.
Server. Instance=MSSQLSERVER.
VD=Global\{08771828-3181-438B-A554-E02DA96EB3F2}7_SQLVDIMemoryName_0.
Event Log Name: Application
Event Log Type: error
Event Log Date Time: 2018-05-11 09:04:06
Notification Generated By: *0 Minute Delay - Priority 4 The Windows Event Log - SQL 2008 service transitioned from a Normal state to a Failed state at 2018-05-11 09:16:21 .

Can I safely stop monitoring SQLVDI errors?

Thank you for your time,

Thomas


texts: Why is AWS RDS' maximum auto_explain.log_min_duration parameter 214783?

texts:
That seems like a really arbitrary number of milliseconds to use as a maximum.  What can I do if I want to log even longer-running queries on a data warehouse?

C.f. https://collabshot.com/show/83dd1e


texts: Does the Binary Data column shown in SQL Profiler contain useful information?

texts:
Many of the events that SQL Profiler captures include a Binary Data column. The documentation for these event classes merely repeats the fact that this column contains "Binary value dependent on the event class captured in the trace."

I'm particularly interested in the RPC:Starting and RPC:Completed events; I notice that the binary data for each event appears the same whenever the same query is executed, although the Starting and Completed events have different binary data. But other events also display information here that I would be interested in decoding. This assumes, of course, that the data would be even relevant or meaningful.

Is the content of these fields documented anywhere, and is it ever meaningful to a developer/administrator?


texts: Is there a way to speed up a DISTINCT query?

texts:
I have a table t in a database (PostgreSQL 10.4):

\d t;
                Table "public.t"
  Column  |          Type          | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
 sn       | character varying(11)  |           |          |
 site     | character varying(50)  |           |          |
Indexes:
    "site_2018_idx" btree (site), tablespace "indexspace"
    "sn_2018_idx" btree (sn), tablespace "indexspace"

I need to find distinct 'sn's for specific site, and I do it like this:

SELECT DISTINCT sn FROM t WHERE site='a_b301_1' ORDER BY sn ;

It works, but is very slow, to return 75 distinct 'sn' values it takes about 8 minutes!
Is there a way to speed it up?
The explain analyze gives this output:

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=42873094.21..42873103.25 rows=3615 width=12) (actual time=190431.413..190431.417 rows=75 loops=1)
   Output: sn
   Sort Key: t.sn
   Sort Method: quicksort  Memory: 28kB
   ->  HashAggregate  (cost=42872844.42..42872880.57 rows=3615 width=12) (actual time=190431.233..190431.263 rows=75 loops=1)
         Output: sn
         Group Key: t.sn
         ->  Bitmap Heap Scan on public.t  (cost=874850.36..42695793.24 rows=70820471 width=12) (actual time=8755.163..168773.143 rows=43096912 loops=1)
               Output: sn, site
               Recheck Cond: ((t.site)::text = 'a_b301_1'::text)
               Heap Blocks: exact=783666
               ->  Bitmap Index Scan on site_2018_idx  (cost=0.00..857145.24 rows=70820471 width=0) (actual time=8540.835..8540.835 rows=43096912 loops=1)
                     Index Cond: ((t.site)::text = 'a_b301_1'::text)
 Planning time: 0.466 ms
 Execution time: 190433.289 ms
(15 rows)

Additional information

After I created another index, as was recommended (site, sn), the time decreased significantly, from 8 min to 30 sec. It is great, I just don't understand why is this the case? How is one multi-column index better than two separate indexes, in this case?


texts:
When I right-click a table in Object Explorer and click "Script Table" I can get SSMS to script my table.

Can I do that with T-SQL code?

I'm after the "CREATE TABLE xx" code that SSMS generates, so that I can take the script and use on other projects. I need all the bits and pieces, including PRIMARY KEY, CONSTRAINTs, "WITH (PAD_INDEX = " and schema declaration.

I'm on SQL Server 2014


texts:
G'Day,

I am looking for a bash script (using Perl - As that is already installed on the machine and I don't want to have to install Ruby, Python etc. just for this requirement) that will perform some simple mySQL replication monitoring of a remote Master & Slave. The script will be able to:

Connect to a remote mySQL master and a remote mySQL slave (cron job is running on a different machine)
Using the output of SHOW SLAVE STATUS; and SHOW MASTER STATUS be able to email notify:

1) A general problem with replication ie. Not running, slave threads stopped etc.
2) Whether the slave is behind in transactions (Ideally this would be configurable)

Email should be configurable for delivery details as well as a cooling off period. So if the cron is run every minute emails for the same error kind are only sent after the cooling off period. So instead of 10 emails for the replication not running you get 1

I have looked online for a solution, but have been unable to find anything suitable.

So if anyone can point me in the right direction I would appreciate it.


texts:
I am wanting to compare the Distinct sn from HMI_Temp to sn in my table HMI -> if the sn exists, I want to update the values - got this syntax CHECK! - if the sn does NOT exist, I want to insert the data from HMI_Temp into HMI I have tried this syntax, but I get errors of:

  Invalid column name 'cb'
  Invalid column name 'lp'
  Invalid column name cn
  Invalid column name stn

And this is my syntax - how should I re-write this to accomplish my desired result?

MERGE INTO HMI AS Target
USING (SELECT DISTINCT sn FROM HMI_Temp) AS Source ON Target.sn = Source.sn
WHEN NOT MATCHED THEN
INSERT (lp, cb, cn, sn, stn) VALUES (Source.lp, Source.cb, Source.cn, Source.sn, Source.stn);

DDL:

CREATE TABLE [dbo].[HMI_Temp]
(
    [sID] [int] IDENTITY(1,1) NOT NULL,
    stn [float] NULL,
    [sn] [nvarchar](255) NULL,
    [cn] [nvarchar](max) NULL,
    [lp] [nvarchar](max) NULL,
    [cb] [nvarchar](max) NULL,
    PRIMARY KEY CLUSTERED
    (
        [sID] ASC
    ) WITH
      (
           PAD_INDEX = OFF
         , STATISTICS_NORECOMPUTE = OFF
         , IGNORE_DUP_KEY = OFF
         , ALLOW_ROW_LOCKS = ON
         , ALLOW_PAGE_LOCKS = ON
      ) ON [PRIMARY]
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[HMI_Temp] ON
GO

INSERT [dbo].[HMI_Temp] ([sID], stn, [sn], [cn], [lp], [cb])
VALUES (1, 8888, N'Test', N'Test', N'Test123', N'Test456')
VALUES (1, 8888, N'SecondTest', N'SecondTest', N'SecondTest123', N'SecondTest456')
GO

This is the update statement I was using that worked, but now throwing a wrench in it for the insert if not exists :(

UPDATE y
SET y.lp = x.lp
,y.cb = x.cb
,y.cn = x.cn
FROM HMI y
INNER JOIN HMI_Temp x
ON y.stn = x.stn
AND y.sn = x.sn
AND y.cn = x.cn


texts: RECURSIVE CTE does not use an INDEX. (Disabling seqscan forces it using an index however and it is faster)

texts:
Suppose the following relations:

match(match_id)
event(match_id, seq, gt, ...)

There are the following indexes:

match(match_id)
event(match_id, seq)

Further notes:

gt is monotonically increasing
For a given match I have a collection of events which happens at a specific 'gt' time
both match and event are mat views.
List item

I am using postgresql 13.1
My goal is to come up with a RECURSIVE CTE query which calculates the delta between one event and the next one, however I find that very slow.
While this can be solved practically with a self-join, I am not interested in that, I want to find out why my CTE is slow. I believe it should not be that slow.
Further numbers:

number of matches is 400
each match has an average of 541 events

My RECURSIVE CTE query is the following:

WITH RECURSIVE
    delta_gts AS (
        SELECT m.match_id, 1 AS seq, 0 AS gt, 0 AS delta
        FROM matches m

        UNION

        SELECT dgt.match_id, ev.seq AS seq, ev.gt AS gt, (ev.gt - dgt.gt) AS delta
        FROM delta_gts dgt
        JOIN events ev ON ev.match_id = dgt.match_id AND ev.seq = (dgt.seq + 1)

    )

SELECT * FROM delta_gts g

Futher notes I also tried by adding the following (for one match only):
WHERE g.match_id = 'ita_1672780'

and I find out in the plan that there is no predicate pushdown. I think this was implemented in pgsql 13.1
This is the plan:
QUERY PLAN
CTE Scan on delta_gts g  (cost=160601.44..161032.40 rows=21548 width=76) (actual time=173.940..354185.831 rows=220268 loops=1)
"  Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1581611"
  CTE delta_gts
    ->  Recursive Union  (cost=0.00..160601.44 rows=21548 width=76) (actual time=173.931..353944.926 rows=220268 loops=1)
"          Buffers: shared hit=5453034 read=596370, temp read=1340253 written=1580590"
          ->  Seq Scan on netcastingdocument_matches m  (cost=0.00..10.08 rows=408 width=28) (actual time=173.917..174.265 rows=408 loops=1)
                Buffers: shared hit=6
          ->  Hash Join  (cost=14121.22..16016.04 rows=2114 width=76) (actual time=259.550..305.356 rows=190 loops=1158)
                Hash Cond: ((dgt.match_id = ev.match_id) AND ((dgt.seq + 1) = ev.seq))
"                Buffers: shared hit=5453028 read=596370, temp read=1340253 written=1580590"
                ->  WorkTable Scan on delta_gts dgt  (cost=0.00..81.60 rows=4080 width=72) (actual time=0.005..0.067 rows=190 loops=1158)
                ->  Hash  (cost=8106.89..8106.89 rows=288289 width=24) (actual time=257.949..257.949 rows=288323 loops=1158)
                      Buckets: 65536  Batches: 8  Memory Usage: 2484kB
"                      Buffers: shared hit=5453022 read=596370, temp written=1565616"
                      ->  Seq Scan on netcastingdocument_events ev  (cost=0.00..8106.89 rows=288289 width=24) (actual time=0.016..92.171 rows=288323 loops=1158)
                            Buffers: shared hit=5453022 read=596370
Planning:
  Buffers: shared hit=107
Planning Time: 50.290 ms
JIT:
  Functions: 13
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 4.108 ms, Inlining 0.000 ms, Optimization 19.158 ms, Emission 154.531 ms, Total 177.796 ms"
Execution Time: 355489.930 ms

Considerations:

It is not using the index (match_id, seq) on the events table at all when the recursive part of the CTE is executed.
Disabling seqscan does the trick as it will use the index for events.

After some investigation it looks like that the issue is that a SeqScan is being performed for looking up the next event which is not right in my situation.


texts: Grouping/sorting performance choice between bigint and nvarchar

texts:
I want to store a hash-code for a variable-length text field (max 1000 chars) in a database table. The hash-code will be computed and assigned once on insert, and new rows will be inserted very often.
The hash-code will be used mainly for filtering (WHERE), grouping (GROUP BY), and sorting (ORDER BY) in a couple of queries. The database table will hold a few million rows over time, with the probability of identical hash-codes (for identical text) being around 30% (rest being unique).
I have the choice of making the hash-code data type NVARCHAR (SHA1 of text) or BIGINT (converted bytes of SHA1 of text). I think BIGINT will be better in terms of storage space (less pages).
Generally speaking, which of these two data types will be better in terms of performance, considering the operations mentioned above?


texts: What can be best database schema for multiple project and one database?

texts:
I am working on multiple projects but the back-end will remain the same. I have a different server for auth, data, etc. and one database to store all the details.

If one projects are creating any data then only that project can access that data and others project shouldn't access that.
For example, if users saving some data in the application from one app that only that app can access that.

I am trying to find the Schema for this database. I am using MySQL 5.7 database.

I am not looking for the multitenant approach of storing the data.

I am looking to store the data in JSON datatype.

Thanks for helping me.


texts: Why am I getting table does not exist error?

texts:
so I'm trying to creating a database table using pg admin. I created a table in pg admin and then I was supposed to run the table using putty. But when I run it it says error table does not exist. The table does exist though so I don't know why I'm getting that error!

--Dropping tables clear out any existing data
DROP TABLE IF EXISTS automobiles;

CREATE TABLE automobiles(
    id INTEGER PRIMARY KEY,
    make VARCHAR(15)NOT NULL,
    model VARCHAR(20)NOT NULL,
    year INTEGER NOT NULL,
    owner CHAR(25) NOT NULL,
    msrp DECIMAL NOT NULL,
    purchase_date DATE NOT NULL
);

INSERT INTO automobiles(id, make, model, year, owner, msrp, purchase_date) VALUES(
    1,
    'Ferarri',
    'F40',
    '1987',
    '',
    '1690000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    2,
    'Tundra',
    '4X2',
    '2019',
    '',
    '39625',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    3,
    'Mercedes-Benz',
    'AMG GT',
    '2014',
    '',
    '145000',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date)VALUES(
    4,
    'Chrysler',
    '300',
    '2004',
    '',
    '800',
    '3/12/2019');
INSERT INTO automobiles(id, make, model, year,owner, msrp, purchase_date) VALUES(
    5,
    'Jeep',
    'Wrangler',
    '2019',
    '',
    '33695',
    '3/12/2019');

SELECT make, model, year, msrp FROM automobiles ORDER BY year ASC;

UPDATE automobiles
SET owner = 'Deanna Slotegraaf'
WHERE make = 'ferarri';

DELETE FROM automobiles WHERE make = 'Chrysler';

This is the full set of code that I have.

Last login: Mon Mar 11 18:02:19 2019 from 108.170.191.53
slotegraafd@opentech:~$ cd /var/www/html/webd2201/slotegraafd/sql
slotegraafd@opentech:/var/www/html/webd2201/slotegraafd/sql$ psql -d slotegraafd_db -f lab7_auto_records.sql
Password:
psql:lab7_auto_records.sql:9: NOTICE:  table "automobiles" does not exist, skipping
DROP TABLE
CREATE TABLE
psql:lab7_auto_records.sql:30: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:38: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:46: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:54: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
psql:lab7_auto_records.sql:62: ERROR:  date/time field value out of range: "3/12/2019"
LINE 8:  '3/12/2019');
         ^
HINT:  Perhaps you need a different "datestyle" setting.
 make | model | year | msrp
------+-------+------+------
(0 rows)

UPDATE 0
DELETE 0

This is the putty session

Thanks...


texts: How does one query a SQL Server Linked Server when SQL Server Authentication is specified?

texts:
I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query and get the following error:

Msg 18452, Level 14, State 1, Line 1
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I know from http://msdn.microsoft.com/en-us/library/ms175537(v=sql.105).aspx that windows authentication is preferred, but it is a vendor database and not up to me.

Also from that link: "you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server."
I am not an Windows admin on the local machine and not any kind of admin on the linked machine.  Is there no other way?


texts:
I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen.

I have a column that is Varchar(20).  It is used to store just year data (2010, 2011, etc.)  it is being converted to an Int data type, but when the command below is ran, an error code happens.

ALTER TABLE mytable ALTER COLUMN1 int NULL

The error displayed is: Conversion failed when converting the varchar value '2010.          ' to data type int.

There are 3 rows in the table with the value of 2010 in Column1, but NO rows with '2010.          ' (period and 10 spaces) as a value.  I ran

select len(Column1) from MyTable where Column1 like '%2010%'

I got 2 rows with a length of 4, and 1 with a length of 5.  After updating the row with 5 length to have 2010 as the value, the table could be altered.

I am curious where the period and spaces came from here.  Also curious how 1 character length increase results in a period and 10 spaces.  I am thinking something along the lines of a carriage return or line feed, but I can't find any data to back that up, its just a hunch.


texts: SQL Server: How to get the responsiveness of a trigger, without blocking the transaction that fires the trigger?

texts:
On a thread today I posed a desire to get code to execute immediately when a record is inserted rather than resorting to polling.

Triggers are powerful tools, yet dangerous places to put real code, because if the code is slow or blocks, the transaction will block, and it can become a real problem.

For my purpose, polling is not feasible. I really need my code to execute immediately when the record shows up. A windows app is inserting data into the table. I do not have sources to the app and cannot change it. I have full access to the SQL Server.

But it is truly a bad practice to put serious code in the trigger.

So the question beccomes: What is the alternative to a trigger that is as responsive as a trigger? (Or almost as responsive... 1-5 seconds of delay would be OK, but a 30 second polling cycle is really unpleasant. The code to run takes from 0-3 seconds to complete.)

Constraints:

I only have access to the db, not the app
SQL Server 2005 and 2008
Feasible. We aren't looking to invent something crazy, add a lot of infrastructure, or big new systems. We have an app. It talks to SQL Server. Adding more layers prob is not poss.
Implementing yet another server app (like msg queue server or such) is not feasible for this project. This thing is only going to fire one to fifteen times a day.

Tools at hand (not comprehensive):
- .NET
- Python
- C#

Would like to avoid xp_cmdshell if at all possible.


texts: How to determine Oracle LOB storage footprint?

texts:
With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space.

Timing is fairly easy to profile but what's the easiest way to get a reasonably accurate measurement of how much space a specific LOB column takes up?


texts: I have two servers. One database server and another is an app server. I have client installed in my app server and I have the respective the tns in my app server to connect to the db server. Now can I take export backups of my database from my app server and keep the export dumps in the app server instead of running it in db server and storing it there? If yes, how is it possible?


texts: Does remove document automatically remove the index that reference this document?

texts: I am wondering if removing the document will automatically update the index if this document is referenced?


texts: Any suggestions on having master-master replications around the world by nosql solutions?

texts:
I would like to set up databases that are put at different data centres around the world. For this, I think some nosql solutions may suit the case. For example, couchdb, maybe. Eventual consistency is ok for this.

The case does not limit to document based solution. But I would like to know the possibility, potential problems for setting up master-master replications around the world as I see the cases the servers are in same data centre most likely.

Thanks.


texts: How to pass schema prefix to queries that don't contain it

texts:
I have a bunch of SQL queries that don't have a schema prefix. Without the prefix, the queries won't run.
I wonder if there is a way to apply a schema to a query, without manually adding -- e.g., copying and pasting -- the schema prefix to each query. The prefix is the same for all the queries.
Also just to add, I use MS SQL Server. Thanks!
EDIT
I am adding a sample query as requested.
The queries are basically like
   USE   DBID1000

SELECT   T1.USERID
  FROM   TABLE1 AS T1 WITH (NOLOCK)
  JOIN   TABLE2 AS T2 ON T1.NAME = T2.NAME

However, this won't run unless I add a schema prefix as shown below (assume the prefix is dbdbo
   USE   DBID1000

SELECT   T1.USERID
  FROM   dbdbo.TABLE1 AS T1 WITH (NOLOCK)
  JOIN   dbdbo.TABLE2 AS T2 ON T1.NAME = T2.NAME


texts:
I have two SQL Server user accounts on a database that behave differently on INSERT / UPDATE / DELETE. SQL Server version is 2008 R2 (10.50.4042, 64 bits).

UserA can do these commands (on any table). It is a SQL Server 'local' account (a SQL Server login, NOT a Windows Account on the local server).

DOMAIN\UserB, cannot. It comes from a Windows AD account. It is a member of Administrators group on the database server.

It get the error :

  The INSERT/UPDATE/DELETE permission was denied on the object
  'TheTable', database 'TheDb', schema 'dbo'

As far as I know, the two accounts have the same roles.
One of these roles has the permission to INSERT/UPDATE/DELETE on the tables.
The accounts are not member of the role db_denydatawriter.

I tried to grant the permission explicitly for UserB :

GRANT SELECT, UPDATE, DELETE, INSERT on TheTable TO [DOMAIN\UserB]

But I still have the error.

If I add sysadmin role on UserB, of course it has the permission, but I do not want to let it have this role.

So, to me, the only difference left is local account / Windows AD account.

Do you know a way to have more information about a potential Windows AD account specific permission?

After much more research, I suspect that UserB has a role with no permission. This Stack Overflow answer put me on the track. Here is a query showing that:

-- Users that are members of roles without any permission :
select *
from sys.database_role_members members
left outer join sys.database_principals memberprinc on memberprinc.[principal_id] = members.[member_principal_id]
left outer join sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
where members.role_principal_id in
(
    -- Roles without any permission :
    select principal_id
    from sys.database_principals roleprinc
    where not exists
    (
        select *
        from sys.database_permissions perm
        where perm.[grantee_principal_id] = roleprinc.[principal_id])
)
and memberprinc.name = 'DOMAIN\UserB'

But I am not able to edit these tables (even with sysadmin role), which is understandable.

  Msg 259 Ad hoc updates to system catalogs are not allowed.

I noticed another difference between UserA and UserB: On TheDb properties, tab Permissions, sub-tab Effective:

I found (from SQL Server Profiler) that this list comes from the following query:

EXECUTE AS LOGIN = N'DOMAIN\UserB';
SELECT
    permission_name AS [Permission]
FROM fn_my_permissions(N'[TheDb]', N'DATABASE')
ORDER BY permission_name;
REVERT;

Do you know how to track the origin of theses permissions ?

As suggested in comments, I listed the DENY permissions for UserA and UserB:

SELECT [dp].[name] AS [user_name], [dpr].[name] AS [role_name], [per].[permission_name], [per].[state_desc], OBJECT_NAME([major_id]) AS [object_name]
FROM [sys].[database_principals] AS [dp]
INNER JOIN [sys].[database_role_members] AS [drm] ON [dp].[principal_id] = [drm].[member_principal_id]
INNER JOIN [sys].[database_principals] AS [dpr] ON [drm].[role_principal_id] = [dpr].[principal_id]
INNER JOIN [sys].[database_permissions] AS [per] ON [dpr].[principal_id] = [per].[grantee_principal_id]
WHERE [dp].[name] in ('DOMAIN\UserB', 'UserA') AND [per].[state_desc] = 'DENY'
ORDER BY [dp].[name];

The two users have the very same DENY permissions on two objects:

user_name   role_name   permission_name state_desc  object_name
---------------------------------------------------------------
UserA        RoleC      REFERENCES      DENY        ObjectD
UserA        RoleC      REFERENCES      DENY        ObjectE
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectD
DOMAIN\UserB RoleC      REFERENCES      DENY        ObjectE

Does anyone have some more clues?

SELECT SUSER_NAME() does return DOMAIN\UserB when I log in as DOMAIN\UserB.


texts: The leading zeroes before decimal point is missing in string formatting of decimal data in XML in db2 11.1

texts:
When generating XML that includes decimal values in  Db2 11.1, the leading zero right before the decimal point isremoved.

In the following query, the decimal value   .00000000000000 is published even it should come as 0.00000000000000 from the XMLELEMENT function.

SELECT XMLElement(
  NAME "keytable",
  XMLAGG(
    XMLELEMENT(
      NAME "tab",
      XMLAttributes( interp as "interp")
    )
  ) OPTION null ON NULL
)
FROM abc where wkstn_oid_sh = 100 AND wkstn_oid_lng = 4211"

The correct result should be :

<KEYTABLE><TAB INTERP="0.00000000000000"/><TAB INTERP="0.00000000000000"/> </KEYTABLE>.

But currently db2 returns :

<KEYTABLE><TAB INTERP=".00000000000000"/><TAB INTERP=".00000000000000"/></KEYTABLE>.

How can I get it to format decimals as 0.00000000000000?


texts: How to reinitialise /var/lib/mysql files?

texts: Due to a mishap I deleted the entire /var/lib/mysql directory. Since the database did not contain anything important, I do not want to go through the hassle of restoring it from an old backup, but instead create the directory structure from scratch. How do I do it without reinstalling MySQL?


texts: How to use GROUP BY after the Having clause Mysql

texts:
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions

All the accounts which is not Disable and also shows their permissions
I got all accounts according to scenario but the problem is if one account has more than one products then it obviously shows account id more than one time

*What i am looking for to GROUP BY the a.id after the HAVING clause which checks the permissions * but no luck getting syntax error

Error Code: 1064 right syntax to use near 'GROUP BY a.`id` LIMIT 0, 1000' at line 14

Here is my query

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
 FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
 INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
 INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
 INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
 WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
 HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' )
 AND `Disable` !='1'   ORDER BY a.`id`  GROUP BY a.`id`

Any help would be appreciated


texts:
I have a query that makes something like this:

+---------+---------+------+
| Patient | Pattern | Rank |
+---------+---------+------+
| a       |      0  |    1 |
| a       |      1  |    2 |
| a       |      0  |    3 |
| a       |      0  |    4 |
| b       |       1 |    1 |
| b       |       1 |    2 |
| c       |       0 |    1 |
+---------+---------+------+

I would like to turn it into something like this:

+---------+----------+
| Patient |  Patternstring |
+---------+----------+
| a       |     0100 |
| b       |       11 |
| c       |        0 |
+---------+----------+

However, I'm getting stuff like this:

+---------+----------+
| Patient |  Patternstring |
+---------+----------+
| a       |     1000 |
| b       |       11 |
| c       |        0 |
+---------+----------+

The original query is ordered by patient, then rank. The way I'm producing things like the current output table is with this:

SELECT PATIENT_ID, WM_CONCAT(PATTERN) as PATTERNSTRING

FROM
(That query)

GROUP BY PATIENT ID;

I'm guessing this has something to do with the way WM_CONCAT functions. Even though the query that WM_CONCAT is reading would have it concatenate everything in the right order (assuming it was reading top-bottom), it seems to be reading things in more-or-less a random order. Is there any trick I can use to get WM_CONCAT to work properly?

I know that LISTAGG would let me solve this easily, but I am using Oracle 11.1 and upgrading isn't an option.


texts:
I'm trying to find the cause of a higher amount of VolumeReadIOPs in our Aurora Postgres instance on AWS. This is what the past 6 weeks look like:

I found the cause of the largest period (02/27 to 03/12), which was a really bad query running almost constantly.

However, since 03/12, I'm seing a larger amount of reads than I did in the period before 02/27. But I can't find the source of this just by looking at what has changed in the code.

What kind of logging kan I enable to find the source of this?
Are there any other CloudWatch metrics that can help me figure this out?


texts: Creating 7 digit codes in database for every new insertion

texts:
I am aware that using identity column we can auto-increment the keys from 0, one-by-one. What I want to do is:

Generate codes in SQL server 2014 which will be of 9 digits (operational codes).

First 2 digits are going be pre-defined and remaining 7 will increment by 1 for every new insertion of record.  For example:

51 (predefined ) and remaining 7 are 0000001  and so insertions will happen as:

510000001
510000002
510000003
510000004
510000005
510000006

and so on.


texts: Convert SQL Server query to MS Access syntax

texts:
I have this query in SQL Server:

SELECT Dated, AccountID, AccountName, Rate,
  case (when (Credit-Debit) > 0 then Credit-Debit END) as Credit,
  case (when (Credit-Debit) < 0 then Credit-Debit END) as Debit,
  Balance
FROM TempTrallBalance

How can I convert it to Access syntax?


texts: Ternary relationship: what's the difference between having single table and having multiple tables?

texts:
Consider the following ternary relationship:

Suppose that all entities have only two attributes (PK and Name).

Here are the tables I derived (5 tables):

Sector
-------------------------
ID_Sector    SectorName
-------------------------

Product
-------------------------
ID_Product    ProductName
-------------------------

Company
--------------------------------------
ID_Company    ID_Sector    CompanyName
--------------------------------------

Relationship 1 (R1)
-------------------------
ID_Sector    ID_Product
-------------------------

Relationship 2 (R2)
-------------------------
ID_Company    ID_Product
-------------------------

Question:

Is it a good solution for that ternary relationship? What's the difference between having 2 tables (R1 and R2) instead of the following single table:

Ternary table
-------------------------------------
ID_Sector    ID_Company    ID_Product
-------------------------------------

To me, it looks like that having 2 separate tables for each relationship (R1 and R2) is a better solution when compared to having a single table, but I don't know if that's actually true or if it's a good practice.


texts: MySql-Workbench: Can't connect to MySQL server on '127.0.0.1' (111)

texts:
The hard data first:
Local machine: Xubuntu 17.10, with MySql-Workbench 6.3
Remote Machine: Ubuntu 16.04, with MySql-Client 5.7 and MySql-Server 5.7

The connection method is: Standard TCP/IP over SSH

The error:

  Your connection attempt failed for user 'root' from your host to
  server at 127.0.0.1:3306:   Can't connect to MySQL server on
  '127.0.0.1' (111)

Then it gives me a list with things to check.

  1 Check that mysql is running on server 127.0.0.1

check

  2 Check that mysql is running on port 3306

check

  3 Check the root has rights to connect to 127.0.0.1 from your address

I checked the mysql.user table and it says that root has the host: localhost and since MySql-Workbench does an ssh connect to the remote machine and then connects to the MySql-Server, localhost should be fine.
Nonetheless I tried setting the host to % as a wildcard but that didn't change anything either so I switched back to default localhost.
I also checked the my.cnf. That is the entire content of it:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

There is no bind-address or skip-networking directive set.

  4 Make sure you are both providing a password if needed and using the
  correct password for 127.0.0.1 connecting from the host address you're
  connecting from

Password and stuff is all fine. Passwords are set, they are typed correctly when logging in.

When I do, what Workbench is supposed to do

ssh to the remote machine (ssh user@remotemachine)
login to mysql server (mysql -u root -p)

It works flawlessly
I just can't do via MySql-Workbench

Help is much appreciated because I already tried everything I was able to research.


texts: Is there a DBMS that allows the "= NULL" syntax?

texts:
Inspired by a StackOverflow question (Why doesn't SQL support "= null" instead of "is null"?).

Is there a DBMS that actually supports the = NULL syntax?


texts:
I was reading the book MySQL high performance, and it mentioned that:

  CHAR is also better than VARCHAR for data that’s changed
  frequently, because a fixed-length row is not prone to fragmentation.

But the thing I could not understand is how VARCHAR is subjected to fragmentation in the first place?