texts:
I'm trying to optimize a MySQL table for a query of the form:

SELECT Col1, Col2 FROM MyTable WHERE Col3 IN('Val1','Val2','Val3',...)

I've added an index to the table:

ALTER MyTable ADD INDEX(`COL3`)

and verified that it was created, but when I analyze the query using EXPLAIN the type is still "ALL" (indicating that all rows are retrieved and searched). What am I doing wrong?

Update:

I've tried EXPLAIN SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val1'. Type is still "ALL". Here are the actual CREATE TABLE, ALTER TABLE and query:

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

ALTER TABLE `RXNCONSO` ADD INDEX(`TTY`);

EXPLAIN SELECT RXCUI, STR FROM RXNCONSO
  WHERE TTY IN ('SCD','SBD','GPCK','BPCK','SCDG','SBDG','SCDF','SBDF');

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | RXNCONSO | ALL  | NULL          | NULL | NULL    | NULL | 1139206 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Update 2:

Here's the result of running

SELECT TTY, count(1) FROM RXNCONSO
 WHERE TTY IN ('SCD','SBD','GPCK','BPCK','SCDG','SBDG','SCDF','SBDF')
 GROUP BY TTY WITH ROLLUP;

+------+----------+
| TTY  | count(1) |
+------+----------+
| BPCK |      526 |
| GPCK |      492 |
| SBD  |    21732 |
| SBDF |    14593 |
| SBDG |    21140 |
| SCD  |    36131 |
| SCDF |    14556 |
| SCDG |    16362 |
| NULL |   125532 |
+------+----------+
9 rows in set (0.38 sec)


texts:
There are several reasons for SQL transactions to rollback. I want to log all of them to the SQL Server Log File so that I can find information about them at a later time in the SQL Server Log File Viewer (see screenshot for example). The information about the transaction should be as detailed as possible. I want to use this information for detecting problems with a client who is losing data. I suspect the problem has somethging to do with a rollback. The problem does almost never occur so I can't use a profiler to see what happens.

The question is: how to log all rollbacks to the log file?
The platform is SQL Server 2005, but if this solution is only possible in a newer version then I would like to hear that too.


texts: SQL Server 2016 Replication Error while Configuring Diistributor

texts:
I am trying to configure Distributor for transactional replication set up on SQL Server 2016 Enterprise Edition. But I get the following error:

SQL Server could not configure 'Server01' as a Distributor.

ADDITIONAL INFORMATION: An exception occurred while executing a
Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

The server principal 'distributor_admin' already exists. Changed
database context to 'master'. (Microsoft SQL Server, Error: 15025)

Any help/direction to resolve this error would be highly appreciated.


texts: MySQL Security - Is there an easy way to encrypt a confidential data and also that it cannot view or access by DBA?

texts: Is there an easy way on how can I encrypt a data and store it MySQL  database and also it also cannot be viewed by dba?


texts: Greater than operator ignoring a nonclustered index?

texts:
I have a pretty simple query in SQL 2008 R2 and I have a nonclustered index on the "sDate" datetime column.

When I look at the execution plan for the following query it seeks the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate = '2012-04-12'

However when i look at the execution plan for this query it does not seek the nonclustered index.

SELECT TOP 1 *
FROM Subs
where sDate > '2012-04-12'

If I force it to use the index the query works and is quick, but if I do not force it, it takes forever. Why isn't SQL using the index on its own?


texts: What is a "Reporting Task for Subplan" task in SSIS, and how can I create one?

texts:
I'm trying to script a daily full backup to a job using an SSIS package. I've inherited several databases from a previous DBA, and I'm trying to emulate the ones he made in order to be consistent. Most of it is pretty straightforward, but there is one task called "Reporting Task for subplan {GUID}" which I am not sure how to create or use:

No task by that name appears to be in the Toolbox pane in BIDS, nor does any task appear to exist which has that icon. Neither Books Online nor Google searches are returning any real documentation or discussion about it. Presumably it's used for logging, since a logfile is specified in its properties.

What is this task and how can I create one, so that I can build packages similar to what my predecessor was building?


texts: MySQL count multiple columns and sum the total occurrence

texts:
I have the following table, say, MyDRAW ...

  DRAWNO    PRIZENO FIRSTD  SECONDD THIRDD  FOURTHD
M123/14     8458    8   4   5   8
M123/14     7214    7   2   1   4
M123/14     1640    1   6   4   0
M123/14     2103    2   1   0   3
M123/14     2557    2   5   5   7
M123/14     2975    2   9   7   5
M123/14     3767    3   7   6   7
M123/14     4409    4   4   0   9
M123/14     4887    4   8   8   7
M123/14     5844    5   8   4   4

I would like to count and sum them UP how many times of occurrence of digit from 0 to 9 (0,1,2,3,4,5,6,7,8,9) for 4 different differenct columns, which is FIRSTD, SECONDD, THIRDD, FORTHD ...

Expected result is ..

DRAWNO   [D0][D1][D2][D3][D4][D5][D6][D7][D8][D9]
M123/14  3   3   4   2   8   5   2   6   5   2

I managed to know how to select one Columns, and Question how to select all columns () and sum them up into TOTAL occurrence of digit 0 to digit 9?

..........

SELECT
DRAWNO,
FIRSTD,
COUNT(FIRSTD) AS COUNT1
FROM t4d
GROUP BY DRAWNO, FIRSTD
ORDER BY DRAWNO, FIRSTD

..........

Thanks.


texts: Registered Server window does not show Database Engine or Local Server Groups

texts:
I am running SQL Server Management Studio 2012 on a Windows 7 machine. After a failed attempt to import my registred servers from SSMS 2008, the icons for Database Engine and Local Server Groups are not displayed in the Registered Servers window: the Registred Servers window is empty.

As such I cannot register a server from the SSMS GUI, by right clicking the Local Server Groups.

How can I restore the Database Engine and Local Server Groups items?


texts: Can filtering be done more efficiently with/without sql joins?

texts:
I have a simple problem that has a simple solution with SQL, but would like to explore alternative ways to solve it if they turn out to be more efficient on large scale.

Let's assume that we have a system where we have users, videos and list of videos users have viewed:

CREATE TABLE `video` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `rank` INT NULL,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_rank` (`rank` ASC));

CREATE TABLE `user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `user_view` (
  `user_id` BIGINT NOT NULL,
  `video_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_id`, `video_id`));

CREATE TABLE `user_friend` (
  `user_id` BIGINT NOT NULL,
  `friend_id` BIGINT NOT NULL,
  PRIMARY KEY (`user_id`, `friend_id`));

The question is: How would we find all the videos that friends of a user have not viewed?

In my solution, I would get the ids of friends (Let's assume user has 3 friends, with ids 1, 2, 3) and build a query:

SELECT
    v.id,
    uv1.video_id
FROM
    video v
    LEFT JOIN user_view uv1 ON (v.id = uv1.video_id AND uv1.user_id = 1)
    LEFT JOIN user_view uv2 ON (v.id = uv2.video_id AND uv2.user_id = 2)
    LEFT JOIN user_view uv3 ON (v.id = uv3.video_id AND uv3.user_id = 3)
WHERE 1
    AND v.id > 100
    AND uv1.video_id IS NULL
    AND uv2.video_id IS NULL
    AND uv3.video_id IS NULL
ORDER BY
    v.rank
LIMIT
    30

Above query will work, but more friends a user has, more joins we'd have to add to the query.

Let's assume we're dealing with 1 billion videos, 100 million users and on averahe user having 50 friends.

Is there a more efficient way to do this in SQL?

Is there a way to do this with non traditional SQL way? Perhaps with noSql with mongodb, cassandra, riak, redis, couchdb or anything else? I'm wondering if there is anything else more efficient purpose built for this.

Any other programming/processing technique that would prove to be more efficient?

I would really appreciate your input.


texts: Validation warning in Data Flow Task in the SSIS

texts:
I am trying to load data into my DimEmployee table using the Data Flow Task. However, I noticed when I run the SSIS, the DimEmployee filled with 4 records out of 9 and also the EmpKey starts counting from -1 then generates random numbers after that! Also I have  a warning associated when running the package:

  Warning   1   Validation warning. Data Flow Task:
  {DC10B0CA-A6DE-4036-98A6-496811C09D4A}: Truncation may occur due to
  inserting data from data flow column "TitleOfCourtesy" with a length
  of 25 to database column "Title" with a length of 8.
    DimEmployee.dtsx    0   0

I created a procedure in my SSMS which supposes to fill the nulls in the columns by 'UNKNOWN'

USE [NorthWindDW]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Insert_New_Employee]

AS
BEGIN

    SET NOCOUNT ON;
    IF NOT EXISTS
    (
        SELECT * FROM [dbo].DimEmployee
        WHERE EmpKey = -1
    )

    BEGIN
    SET IDENTITY_INSERT [dbo].[DimEmployee] ON
    INSERT [dbo].[DimEmployee] (EmpKey, EmpId,Title, EmpFirstName, EmpLastName, HireDate,Country, StoreCode, Telephone,JobTitle) VALUES
    (-1,-1,'UNKNOWN','UNKNOWN','UNKNOWN','1900-01-01', 'UNKNOWN','UNKNOWN', 'UNKNOWN','UNKNOWN');
    SET IDENTITY_INSERT [dbo].[DimEmployee] OFF
    END
END

GO

This is my DimEmployee Table

Create Table [dbo].[DimEmployee]
(EmpKey        INT IDENTITY NOT NULL,
EmpId          INT NOT NULL,
EmpFirstName   NVARCHAR(50) NOT NULL,
EmpLastName    NVARCHAR(50) NOT NULL,
Title          NVARCHAR(8) NOT NULL,
HireDate       DATE        NOT NULL,
Country        NVARCHAR(50) NOT NULL,
StoreCity      NVARCHAR(50) NOT NULL,
Telephone      NVARCHAR(25) NOT NULL,
JobTitle       NVARCHAR(50) NOT NULL);
Go


texts: I'm setting up a master-master replication where in the second master is only read only there by reducing the issues of conflicts. My doubt is that to reduce the downtime when my master goes down I would be pointing my application to the second master (which is in read-only) and continue the work. Is it possible to have a DNS CNAME record pointing to one of your master. In case of failover, just update the DNS CNAME to other working master while you fix the broken one... once you fix and bring up the broken master, it will catch up with the transactions. If yes, how should I do it?


texts: Declare variable for SELECT statement in Postgres function

texts:
I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.

CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz)
  RETURNS TABLE (kategorien text, requestcounter int) AS
$func$
DECLARE
_minid bigint;
_maxid bigint;

BEGIN

SELECT id  INTO _minid from tablename
where starttime >= $1 ORDER BY starttime ASC LIMIT 1;
SELECT id  INTO _maxid from tablename
where starttime < $2 ORDER BY starttime DESC LIMIT 1;

SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
            WHEN duration <= 40000000 THEN '01-04 sec'
            WHEN duration <= 100000000 THEN '04-10 sec'
            WHEN duration <= 300000000 THEN '10-30 sec'
            WHEN duration <= 600000000 THEN '30-60 sec'
            ELSE 'more than 60 sec' END
     , count(*)::int
FROM   tablename
WHERE  id >= _minid and id <= _maxid
GROUP  BY 1
ORDER  BY 1;

END;
$func$ LANGUAGE plpgsql;

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

How to fix this?


texts: Configuring CloudSQL (MySQL) with binary logs but without backups

texts:
In the Google CloudSQL (MySQL) console, I can read:

  Automated backups required for binary logging.

This requirement looks strange to me.  I do not understand why automated backups are required for binary logging.  I see use-cases where I might want replication (and failover, both needing binary logging) without automated backups.

Does anyone know why this requirement exists ?


texts: How to write group by and having in a relational algebra interpreter?

texts:
I am practising writing relational algebra (RA) expressions in RA interpreter from Duke, but I can't figure out how to represent group by or having (from SQL). I have this expression, but I need to group it by countries, and get only those that have count>5 (I can work around count with another query, but I don't know how to group):

\project_{country, count(*)}(locationTable);

Also, is there order by in RA?


texts: How to recover from a segmentation fault

texts:
Postgresql 11 running on a Centros RHEL 7.
Suddenly BD crashed and service went down.

service postgresql-11.service status
Redirecting to /bin/systemctl status postgresql-11.service
Segmentation fault

Last log:

LOG:  database system was interrupted; last known up at 2019-09-05 10:19:08 UTC
LOG:  could not stat file "./base/13877/2617_vm": Input/output error
LOG:  could not stat file "./base/13877/5002": Input/output error
LOG:  could not stat file "./base/13877/13745": Input/output error
LOG:  could not stat file "./base/13877/13728_fsm": Input/output error
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 836/A34031F0
LOG:  incorrect resource manager data checksum in record at 836/A35B25F8
LOG:  redo done at 836/A35B2368
LOG:  last completed transaction was at log time 2019-09-05 10:17:18.321885+00
LOG:  checkpoint starting: end-of-recovery immediate
LOG:  request to flush past end of generated WAL; request 836/AE344938, currpos 836/A35B25F8
CONTEXT:  writing block 0 of relation base/16384/2619_vm
FATAL:  xlog flush request 836/AE344938 is not satisfied --- flushed only to 836/A35B25F8
CONTEXT:  writing block 0 of relation base/16384/2619_vm
LOG:  startup process (PID 43658) exited with exit code 1
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

I have found some more messages:

 WARNING:  terminating connection because of crash of another server process
 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
 HINT:  In a moment you should be able to reconnect to the database and repeat your command.
 FATAL:  the database system is in recovery mode x(5)

How to proceed in this situation?


texts: How can I design a query to pause / sleep / wait for a specified amount of time?

texts: I am testing a monitoring application against a SQL Server 2000 instance. How can I write a T-SQL query that takes an amount of time that I specify? For example, most scripting languages have something like the SLEEP command which allows you to pause script execution for a specified amount time. I am looking for something similar that is compatible with SQL Server 2000. This is intended to test the long-running query threshold settings of the monitoring application.


texts: Oracle 11gR2: ORA-01102: cannot mount database in EXCLUSIVE mode

texts:
I am running Oracle 11gR2 on an AIX box.  I did a rename database and change DB ID using NID.  I can restart db in a nomount mode, but I can not mount it by the following error.

ORA-01102: cannot mount database in EXCLUSIVE mode
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01507: database not mounted

I started DB by using a new pfile.  I notice that the NID utility did not change the old DB folder names in ASM.  So this rename database effort will not be perfect.

I have already removed some easy things when I Googled this error, such as removing the lk* files under $ORACLE_HOME/dbs.

Any suggestions for me to start the database after the renaming command of NID?

Thank you so much!


texts: How to capture client host name in postgresql?

texts: pg_stat_activity system view has client_address and client_hostname, but it always empty or null. Do I need to tweak anything to update the view?


texts:
I am working on creating a script to restore database from a backup file to default location of an instance, which is set away from system databases installation folder.

Currently, I'm using to find default DATA Location

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1 AND file_id = 1

and default LOG Location

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'mastlog.ldf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1
        AND file_id = 2
    )

Is there any other way to find database default location with the help of query?

Thank you for time.


texts:
I installed SQL Server with Reporting Services package by mistake and I will never have a requirement to use the Reporting Services part.

Is it possible to uninstall Reporting Services (and anything else that would have been included/installed because of Reporting Services)?


texts: After restarting Mysql replication, Slave commands are executed

texts:
up vote
0
down vote
favorite
I have a Mysql Master Slave Replication which is working fine. Unfortunately I got some serious problems on my slave and the only chance I saw was to restart the whole replication.

Slave was stopped
I created a dump on the master with logfile and logposition informations

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2   > ~/dump.sql
I dropped the database on the Slave ( to enter the fresh data )
I inserted the fresh dump
Reset master log file and master position with information given from the dump file
Start Slave

Now everything worked fine for some minutes and I saw how the correct data was imported from the Master. But after 10 minutes, the slave stopped importing because there were some tables missing. After a second run, I found out, that the DROP DATABASE command, I executed on the Slave, was now executed again on the slave.

Even a RESET Slave and importing data then and proceeding from Step 3 didnt help.

How can I prevent that the commands, which have been executed on the slave since the DUMP, are not being executed again?


texts:
I have a query which was working fine a few days back:
SELECT
    id, title, details, filetype, filepath, size, details, location, datetime,
    IF(hid=1, 'Anonymous',
        (
            SELECT name
            FROM users
            WHERE did = userid
        ))
    AS username
FROM infotable
WHERE ext2!='0'
ORDER BY id DESC
LIMIT 50

but a couple of days back, it started giving an error:

Commands out of sync; you can't run this command now

I am calling it from a PHP page but I have also tried it from MySQL's GUI to run it as SQL query. It gave the the same error:

When I googled, I found that it may be due to MySQL to MySQLi update. I tried a lot to write an alternate query using MySQLi, but every time I got an error (Some times the same error and some times other errors).
My questions are:
1. What this can be called as: Sub Query or Multi Query
2. What is the exact error and what is its solution?


texts: What is the sync point of a Sql Server backup? If I have a full backup that runs from 20:12 to 20:38, when I restore the backup is it restoring to the state at 20:12? 20:38? Some other point?


texts: how to view content of xbase procedures?

texts:
I would like to view the code of my xbase stored procedures.

I recognize that show procedures gives me the list of procedures.

Can anyone instruct me on how to get the actual code for the stored procedures?


texts: Dynamic Count of items +/- 15% per row value

texts:
I have a table with a column of building sizes named "SQFT".  I trying to find the number of buildings that are within 15% +/- of each rows "SQFT" value.
I have tried case, count with filter and window functions but none would give me the results I'm looking for, at least not in the way I was using them.

Below is what I'd like as a final result.

ID   -15%    SQFT    +15%    COUNT
1    85      100     115     2
2    94      110     127     3
3    102     120     138     3
4    111     130     150     4
5    119     140     161     5
6    128     150     173     5
7    136     160     184     5
8    145     170     196     5
9    153     180     207     5
10   162     190     219     5
11   170     200     230     7
12   179     210     242     7
13   187     220     253     7
14   196     230     265     6
15   204     240     276     5
16   213     250     288     4

This is the code so far:

WITH base AS (
   SELECT "Unique ID" as id, "Sqft Total" AS "SQFT", "Sqft Total" * (1-.15) as low, "Sqft Total" * (1+.15) as high
   FROM   "_KEY_TABLE"
   )
SELECT
    id,
    round(low::numeric,0) as "-15%",
    sqft,
    round(high::numeric,0) as "+15%",
    count(*) over(partition by "SQFT" between low and high) as "COUNT"
    -- case when "SQFT" between low and high is true then 1 end,
    -- count(*) filter (where "SQFT" between low and high) as counter_filtered,
FROM   base b
group by b."SQFT", id, low, high


texts: Handling delayed key info in real time data warehouse

texts:
Our data warehouse is currently loaded with a traditional daily ETL batch job, but we're looking to soon implement a few stars in real-time (fed by streaming Kafka messages, FWIW).  So only a couple fact tables and dimensions will be in real-time, while the rest will remain loaded daily.  I was wondering how others have dealt with the issue of a real-time fact table with FKs into daily batch-loaded dimensions.

The scenario we have is our business is pushing for our Sales Workflow star schema to be in real-time so we can run intraday workflow analytics, but there are keys in the fact table, like into customers and sales reps (and several others), that'll still be populated daily.  Without turning the entire DW in real-time, what are some best practices for solving the "delayed" key problem?


texts: Identify records with one identical field, but different timestamp

texts:
I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on.

My current query uses this line in the SELECT statement to identify records that have a field that occurs elsewhere in the database:

CASE SIGN(COUNT(*) - 2) WHEN -1 then 0 else 1 END AS "REPEAT_OFFENDER_BIT"

So basically it says "if there are multiple records with this field (which is determined by my GROUP BY clause), put a one in the REPEAT_OFFENDER_BIT column, else put a zero.

I also need to make sure that the timestamps associated with these records it finds as having a certain common field are not the same.  Only then would the record get a one rather than a zero.

Let's say my table columns are these:

fname | lname | ssn | chargeddate

A row should get a one for REPEAT_OFFENDER_BIT only if there exists another record with the same SSN and a different charged date.

What are my options for achieving this?  My SQL is quite primitive so I came here for help.  Thank you!


texts: Can someone explain what bloating means in database terms? For example what does it mean to say an index is bloated. I tried searching for it but there is no explanation of what bloating is, only what it causes or what it is caused by.


texts: Log Autogrowths bigger than log size, but no shrinks to explain how

texts:
I have a database with a logfile size of 1GB.  I implemented some logging of AutoGrowths to eventually setup some notifications for when a database autogrows.

What I'm experiencing though, is that SQL Server is saying there were autogrowths of the log file, but the filesize of the log file is not actually changing.  There are enough autogrowths to have grown my file to 12GB in size since the beginning of the year, but my file is still only 1GB.

Does anyone know how/why this would happen?

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)   Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

There haven't been any autoshrinks, or manual shrinks of the log file.


texts: Help converting a MySQL query to postgres

texts:
Is it possible to do this query in postgresql?

SET @p:= 0;
UPDATE t SET x=CEIL((@p:= @p + 1) / $n) ORDER BY id DESC;

I'm struggling with the variable syntax.  I tried:

\set p 0
update t set x=CEIL((:p := :p+1) / 10);
ERROR:  syntax error at or near ":="
LINE 1: update t set x=CEIL((0 := 0+1) / 10);


texts: Database_Id for our database in sys.databases does not match that of DB_ID ( '<our database' )

texts:
The Database ID in sysdatabases and sys.databases for our database is 4.

The function DB_ID ( '<our database>' ) returns 5 for our database.
Also any missing index data for our database in the table sys.dm_db_missing_index_details is assigned to database_id 5 also.

It seems to me that the sys.databases should have ID 5 and NOT 4. This is driving me bonkers and making it so sp_BlitzIndex does not work.

Does anyone know why my database id doesn't match between sys.databases, sys.dm_db_missing_index_details and DB_ID() on SQL Azure? Is it actually screwed up or does this just work different in SQL Azure?


texts: Most suitable DB Architecture for real-time high-volume processing API

texts:
I've got a service that should support real-time interaction between many users (thousands real-time). I use .NET 4 with MSSQL 2008 on IIS 7.5.

The DB consists of only a few tables without high usage or data - but one table is growing REALLY fast (each use updates it each second with a new record).

On my server load testing it grew to above 400,000 records in an hour or so - my performance declined from 200ms RTT to 800ms RTT in about 2 hours.

How should I build my DB to support this? Mayvbe the problem lies in another place?

For now it works with all the tables in one DB, but I thought maybe I should move the "heavy" table to a new DB to increase performance. Should it work? is it a smart move?

I've though about moving that table to NoSQL - will it help?

Any ideas are welcome!


texts: Schema unusable after deleting another database

texts:
I just get a problem with a customer's SQLServer. I'm reading articles related to schema, owners and permissions, but I can't get it out.

I have two DB : DB1 and DB2.
I have a schema, which has the same name of it's owner (user1, which has "user1" as default schema).
This schema exists on both databases, user1 is mapped as owner on both databases.

So, some of my tables are in this format : user1.table1, user1.table2, dbo.table3...

DB1 is not used anymore : I turned it offline. Just after that, the tables [user1].[tableXX] from DB2 were not usable... Turning back DB1 online solved the problem.

How can this happens? How can I turn DB1 offline without blocking DB2?

Thanks in advance!


texts: Reset Running Total based on another column

texts:
Am trying to calculate running total. But it should reset when the cummulative sum greater than another column value

create table #reset_runn_total
(
id int identity(1,1),
val int,
reset_val int,
grp int
)

insert into #reset_runn_total
values
(1,10,1),
(8,12,1),(6,14,1),(5,10,1),(6,13,1),(3,11,1),(9,8,1),(10,12,1)

SELECT Row_number()OVER(partition BY grp ORDER BY id)AS rn,*
INTO   #test
FROM   #reset_runn_total

Index details:

CREATE UNIQUE CLUSTERED INDEX ix_load_reset_runn_total
  ON #test(rn, grp)

sample data

+----+-----+-----------+-----+
| id | val | reset_val | Grp |
+----+-----+-----------+-----+
|  1 |   1 |        10 | 1   |
|  2 |   8 |        12 | 1   |
|  3 |   6 |        14 | 1   |
|  4 |   5 |        10 | 1   |
|  5 |   6 |        13 | 1   |
|  6 |   3 |        11 | 1   |
|  7 |   9 |         8 | 1   |
|  8 |  10 |        12 | 1   |
+----+-----+-----------+-----+

Expected result

+----+-----+-----------------+-------------+
| id | val |    reset_val    | Running_tot |
+----+-----+-----------------+-------------+
|  1 |   1 | 10              |       1     |
|  2 |   8 | 12              |       9     |  --1+8
|  3 |   6 | 14              |       15    |  --1+8+6 -- greater than reset val
|  4 |   5 | 10              |       5     |  --reset
|  5 |   6 | 13              |       11    |  --5+6
|  6 |   3 | 11              |       14    |  --5+6+3 -- greater than reset val
|  7 |   9 | 8               |       9     |  --reset -- greater than reset val
|  8 |  10 | 12              |      10     |  --reset
+----+-----+-----------------+-------------+

Query:

I got the result using Recursive CTE. Original question is here https://stackoverflow.com/questions/42085404/reset-running-total-based-on-another-column

;WITH cte
     AS (SELECT rn,id,
                val,
                reset_val,
                grp,
                val                   AS running_total,
                Iif (val > reset_val, 1, 0) AS flag
         FROM   #test
         WHERE  rn = 1
         UNION ALL
         SELECT r.*,
                Iif(c.flag = 1, r.val, c.running_total + r.val),
                Iif(Iif(c.flag = 1, r.val, c.running_total + r.val) > r.reset_val, 1, 0)
         FROM   cte c
                JOIN #test r
                  ON r.grp = c.grp
                     AND r.rn = c.rn + 1)
SELECT *
FROM   cte

Is there any better alternative in T-SQL without using CLR.?


texts: How to manually set Code Page and Collation for my SSAS Cube?

texts:
When I try to Process my SSAS2012 Olap Cube I get a lot of erros like this

Errors in the OLAP storage engine:
A duplicate attribute key has been found when processing: Table: 'dbo_ART'
,Column: 'NAZIV'
,Value: 'ARMATURA ZA KERAMIĆKI VODOKOTLIĆ'.
The attribute is 'Art Naziv'.
Errors in the OLAP storage engine: The record was skipped because the attribute key is a duplicate.
Attribute: Art Naziv of Dimension: ART from Database: robot, Record: 2451.

When I investigate this error I found that at my transactional database at table ART exists two totally different rows with whit value of column NAZIV ='ARMATURA ZA KERAMIĆKI VODOKOTLIĆ' only differences from this two records is that one do not have letters with 'Ć' and 'Č' insead on that placed is letter 'C'.

If I do

select naziv
from art
group by naziv
having count(*)>1

I do not get any duplicated rows.
Because some how transactional database know difference between letters 'Č','Ć','C'.
But OLAP cube when Processing Dimension ART reading all this letters as "C" and getting duplicate keys.
My transactional Database is at SQL 2012 R2 standard server with Collation =Croatian_CI_AS server is generally set to Latin1_General_CI_AI.
My SSAS 2012 Enterprise server have Windows Collation set to Latin1_General
and all Sensitive is Unchecked.

How I should set my language settings for SSAS server to avoid this error.
Is that "accent sensitive" setting.
Or I need to change my Windows Collation to something else ?


texts:
I maintain an Oracle Enterprise 11.2.0.3 database where one user has editioning enabled.  I understand that this was an irreversible choice according to the documentation.

If I wish to revert that user to a non-editioned status can I export their objects, drop and recreate the user and then import the old objects?

Are there any consequences?
Which edition will Oracle export?
should I drop all child editions first?

This question is caused by the assessment of the team that editioning is not used and does not offer any benefits when we have generous time periods for scheduled downtimed. It prevents any materialized views being created by the editioned user if they select non editioned object from another user. This is required for one project due soon and a data warehouse in the near future.


texts: Does Extended Event session without target affects server performance?

texts:
Extended event session can be created without targets. In such situation you can watch collected events in SQL Server Management Studio in the Live Data view. For such session a LiveStreamTarget is created whenever Live Data view is opened. If no one is monitoring such session, there is no target created for it.

Does such XEvent session affects server performance when no one is monitoring events with Live Data tab?


texts: If an account has REQUIRE SUBJECT, does it still need a password?

texts:
I'm in the process of setting up SSL-secured replication between two servers.  Each server has its own public/private keypair, and the CA cert is just the concatenation of the two public certs, like this answer.

Now I'm updating the replication account with REQUIRE SUBJECT "exact subject of the client"

Is there any practical value to also having a password on the replication account (IDENTIFIED BY "secret")?


texts: Can I use SSRS 2016 with SQL Server 2008 R2 database engine?

texts:
I don't know that much about the state of products offering reporting/analytics, but I first got into OLAP in the late 90s, with a data warehousing tool, but then didn't touch it again for a while.  My vocabulary on the topic may not be precise.

I have data in a SQL Server 2008 R2 database.  The application putting data into it is not certified for SQL 2016, but SSRS 2016 (SQL Server Reporting Services 2016) has caught my eye.  It seems to have the features I'd like to use with this application.  I am adding reporting capabilities to a 3rd party application.

Can I create a new machine with just SSRS 2016 (which is a feature of SQL Server as a whole) and point it to the SQL 2008 R2 server with the database such that I can then start doing reporting on the application database?

What are the ramifications if the SQL 2008 R2 server is also an Enterprise server (or cluster) with many other databases on it?

This is not related to Sharepoint.  The 3rd party application is not a Microsoft application.


texts: Excessive CPU use in vendor SQL Server DB

texts:
Background: I've got a SQL Server 2012 DB provided by a vendor so modification of queries and tables is limited. We do own the DB though, so we can add and maintain indexes.

Indexes haven't been maintained or rebuilt so there are hundreds sitting at 30%+ fragmentation... this is my initial suspect of massive and constant CPU use, but while we work on fixing this I'm investigating other issues.

I'm not seeing any significant memory or disk IO pressure. This is a relatively lightly used OLTP system and has been well provisioned for resources... it really shouldn't be having any issues, or at least should only have noticeable spikes, no constant CPU use.

Two questions:

Can out of date statistics and highly fragmented indexes throughout the DB cause the excessive CPU use?
Does the combination of wait stats listed below from this system discredit the index fragmentation explanation?

Information:

WaitType                                    Wait_S
---------------------------------           -----------
CXPACKET                                    773345.21
PAGELATCH_UP                                737295.83
SOS_SCHEDULER_YIELD                         140425.24
LATCH_EX                                    69877.95
RESOURCE_SEMAPHORE_QUERY_COMPILE            60985.48
LCK_M_SCH_S                                 39488.17

Source query for the wait results:

WITH [Waits] AS
(
 SELECT
     [wait_type],
     [wait_time_ms] / 1000.0 AS [WaitS],
     ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
     [signal_wait_time_ms] / 1000.0 AS [SignalS],
     [waiting_tasks_count] AS [WaitCount],
     100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
     ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
 FROM
     sys.dm_os_wait_stats
 WHERE
     [wait_type] NOT IN (... common waits )
     AND [waiting_tasks_count] > 0)
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95;


texts: advice on modeling schema to have better access to foreign entity

texts:
I am fairly new to modeling on MongoDB--please consider this simple example schema

const userSchema = Schema({
  name: String,
  username: String
});

const addressSchema = Schema({
    user: { type: Schema.Types.ObjectId, ref: 'User' },
    street: Schema.Types.String,
    city: Schema.Types.String,
    state: Schema.Types.String,
    zip: Schema.Types.String,
});

Since querying addresses by user will be a constant job, what is the right choice to make?

a) place an "addresses" field in User schema as

addresses: [{ type: Schema.Types.ObjectId, ref: 'User' }]

b) having a virtual property on User

userSchema.virtual('addresses', {
  ref: 'Address',
  localField: '_id',
  foreignField: 'user'
});

Is this a good use of virtual type, according to mongoose docs?

  are document properties that you can get and set but that do not get
  persisted to MongoDB. The getters are useful for formatting or
  combining fields, while setters are useful for de-composing a single
  value into multiple values for storage

It doesn't quite fall in the situation they describe, so I'm not sure if it applies.


texts: How to restore a database with innodb tables from files?

texts:
I am trying to restore a MariaDB database containing innodb tables from files. Blue-eyed as I am, I considered this an easy task: I just stopped the server, copied the files and restarted it as follows:
# Stop MariaDB server
systemctl stop mariadb.service
# Copied database
sudo rsync --numeric-ids -aHAEXv --rsh="ssh" user@mybackupserver:/xyz/mysql/owncloud /var/lib/
# Start MariaDB server
systemctl start mariadb.service

Unfortunately this does not work as expected. Although the database and all it's tables are there, I can't access any table. Trying to do so I get:

1932 - Table 'owncloud.oc_activity_mq' doesn't exist in engine.

I than tried to create an empty database of the same name before I repeated all steps above, but I got the same error.
Oddly in PHPMyAdmin all tables have the Collation  in use.
I checked permissions and ownership of the directory a dozen times, everything within my MariaDB data directory is owned by the user mysql and every file has the permission 0660 every directory 0700…
My question is, is it possible to restore a single database with innodb tables directly from an old data directory to a new one? And if it is, how?
I know this question came up before, for example here: How to restore MySQL database from Files
But I am asking here specifically for a database with innodb engine, as off what I read, I got the impression the problems I ran in, might have to do with the engine


texts:
I am facing issue when trying to handle null.

Here is example

I have a table

Create table Test_Table
(
id int null,
pName varchar(10)
)
GO

Id column has NULL value.

INSERT INTO Test_Table
SELECT NULL, 'A' UNION
SELECT NULL,'B' UNION
SELECT NULL, 'C' UNION
SELECT NULL,'D'
GO

Now when I execute different select statements, last two conditions are failing. I can use ISNULL to handle it but there are approx 500 SPs which I would need to change.

So, I need solution apart from that, if possible.

SELECT * FROM Test_Table WHERE id is NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=NULL --True

SET ANSI_NULLS OFF
SELECT * FROM Test_Table WHERE id=id --False. Why???

Declare @ID INT = 0

SET ANSI_NULLS OFF
Select case when @ID > 0 then @ID else id end as Statements, *
From   Test_Table
Where  ID = case when @ID > 0 then @ID else ID END -- Fails. Why??

When Id=NULL is true then why Id=Id is false??

Thanks!


texts: Migrating from DEV SQL Server to PROD - Any Point to Script the Statistics from DEV to prod?

texts:
I have a new database (powers a new website) in a dev environment.

I have everything scripted out to create the DB, objects, and data in my production environment. In that DB, auto create and auto update statistics is turned on by default.

Is there any point in scripting the statistics from all of the DEV tables, or will they auto generate on their own in production?

I should have specified that I am not migrating an entire db file, but rather, I will be re-generating tables, procs, views, data, etc, in production via scripts...


texts: Finding the Min value for a subgroup, filtering out preceding Min values within the same parent group

texts:
How do I get the minimum value for a group (Acc, TranType), but filtering out any minimum values used in preceding rows for the Acc group. Preceding rows would be defined as Acc asc, TranType asc.

The PosCancelID should only appear once per Acc group. But the same PosCancelID could appear within another Acc Group within the data set.

So with the given data set:

Acc  | TranType | PosCancelID
100     1               2
808     1               5
808     1               4
808     2               5
808     2               4<--To be filtered from min calc as it min for (808,1)
813     2               3
813     4               3<--To be filtered from min calc as it min for (813,2)
809     1               3
809     1               4
809     2               3<--To be filtered from min calc as (809,1) uses it
809     2               4
809     3               4<--To be filtered from min calc as (809,2) uses it

I should get:

Acc  | TranType | PosCancelID
100     1               2
808     1               4
808     2               5
813     2               3
809     1               3
809     2               4

SELECT ACC, TranType, min(maxPreceeding) as ActualCancelID
FROM
(
   SELECT ACC, TranType,
          MAX(m.posCancelID) OVER (PARTITION BY m.ACC
                                  ORDER BY m.TranType, m.posCancelID
                                  ROWS UNBOUNDED PRECEDING) as maxPreceeding
    FROM MCancel as m
) AS x
GROUP BY ACC, TranType

The above query gives me almost what i want but isn't filtering for acc = 813. So I know there must be a better (actually applying a filter to remove previous minimum values) way.


texts: How to transfer table from one database to another in postgresql

texts:
pg_dump -h host_ip -U postgres -d source_db -t aa_nqrgs.act_26022017  |
    psql -h destination_host_ip -U postgres -d destination_db

I have tried this command to transfer a table from one server to another, but it shows an error like 'invalid command \N'. How can I resolve this?


texts: How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

texts:
Off the heels of this question,

How does MySQL handle joins on ENUMs?

How does MySQL work if the referencing table has more and less values in the ENUM than the table being referenced? Is it validating integrity based solely on the global ENUM textual key (symbol)? Or the underlying integer value?


texts: SQL Join slower than subquery on View with a Function

texts:
My database has a view that calls a function. Joining on that view is surprisingly slow. Is there a way to speed up joining to a view like that?

I've included 2 queries which illustrate the issue. They give identical results. The first query takes 6 seconds to run. According to the estimated execution plan, the first query spends 82% of its time in "Nested Loops (Inner Join)". The second query runs much faster, but for more complicated queries I would have to use a join instead of a sub query.

I'm happy to provide more information about my particular system, but I suspect this is a generally applicable issue.

--ptynbr is int
--ptyid is varchar
--vwTest has 8307 rows
--map has 126 rows
--Instrument and Party are plain old tables.

Create function [dbo].[fnTest](@ptynbr int)
returns varchar(40)
as
begin
  declare @result varchar(40);
  select @result = ptyid from dbo.[Party] where ptynbr = @ptynbr;
  return (@result);
end;
GO

CREATE view [dbo].[vwTest] as
select dbo.[fnTest](i.ptynbr) PartyName, i.insaddr
from Instrument i
GO

select distinct i.PartyName
into #map
from [vwTest] i

--Takes 6 seconds
select i.insaddr
from [vwTest] i
join #map m on m.PartyName = i.PartyName

--Takes < 1 second.
 select i.insaddr
from [vwTest] i
where i.PartyName in (select PartyName from #map)


texts:
This question is derived from the following post UNION query not working

Fails:

INSERT INTO AssetControl
SELECT *
FROM CSVImport
UNION SELECT *
FROM AssetTemp;

Works:

INSERT INTO AssetControl
SELECT U.* FROM
(SELECT * FROM CSVImport
UNION
SELECT * FROM AssetTemp) AS U

I don't understand why an alias U needs to be created in order for the records to be successfully input into the AssetControl table. Can anyone shed some light on this?


texts:
I have 2 instances of SQL Server on two Windows Servers, namely:

D-MSSQL01-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v.2019)

And seperate server for AGs:

D-MSSQL02-DE (v.2014)
D-MSSQL02-DE\SQL2019 (v.2019)

Listeners:

D-AGSQL01-PP (v.2014 listener)
D-AGSQL04-PP (v.2019 listener)

I'm after setting up new AG and it's listener on SQL server 2019 with new IP:port assigned by networkteam. Now, when I connect to this newely created listener, somehow under "Always On High Availability" I see 2014 AG instance!
Why is this connecting to SQL2014 instead of 2019?


texts: pg_restore fails with drop default on identity column

texts:
Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase.   See below:

pg_restore --verbose --format=custom --dbname=bosh --clean --if-exists --single-transaction --exit-on-error

pg_restore: dropping TABLE DATA cpi_configs
pg_restore: dropping TABLE DATA configs
pg_restore: dropping TABLE DATA compiled_packages
pg_restore: dropping TABLE DATA cloud_configs
pg_restore: dropping TABLE DATA blobs
pg_restore: dropping TABLE DATA agent_dns_versions
pg_restore: dropping DEFAULT vms id
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2493; 2604 16496 DEFAULT vms id postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column "id" of relation "vms" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
    Command was: ALTER TABLE IF EXISTS public.vms ALTER COLUMN id DROP DEFAULT;

I'm running this as part of an automation utility where it's not trivial to change the command.  Best I can think of to workaround this is to use -C to drop/create the database with pg_restore, but is this a bug to file or something we can do differently with pg_dump?


texts: Which is better for long term database scalability: adding columns or having key/value store

texts:
For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach:

to add new columns to settings table every time new setting is introduced
to have settings table as key/value storage

Consider that application might have millions of instances (which all run on the same db and use the same table for settings), DB might be sharded. DB is relational, i.e. either MySQL or T-SQL.

As a developer I would prefer the 2nd variant, so I could scale the application without changing the DB schema and add/remove settings at will. As far as I understand having several million records in a single table shouldn't be an issue if the index is clustered on application instance. Are there any drawbacks that I'm not aware of?

And what about 1st variant? Are there any big benefits? And what about the number of columns: is there any theoretical limit of columns that table can have? What happened if I had a table with 1000 (10 000, 1000 000) columns? Would that be slow?


texts:
I have databases on Postgres 10 on some CentOS 7 servers.
It's necessary to maintain different database backup copies and diverse retention requirements for critical PostgreSQL instances.

Rman provides retention for Oracle backups, administrators are able to set retention for days or months for example. And after the retention period the expired backup would be deleted.

What PostgreSQL's native package/software are able to do this?


texts: Incrementally Updated Image File Backup Multiple Locations

texts:
With an Incrementally Updated Image File Backup the same script handles both the initial image backups, and the incremental backups that can be applied to the images to bring them up to date.  Is there a way to change a parameter or modify this script so that the image files get created in one location and the incrementals get created in another?

A partial solution is to do the inital image file backups, move them to the alternate location and then re-catalog them there.  The next run will then create the incremental backup in the original location, but update the image files in the new location.  The problem comes when a new datafile is created.  The backup will recognize that it doesn't have an image for it and create one, but in the same location as the incrementals.  Aside from repeating the move and re-catalog is there a way to have things happen as desired from the start?

If you're wondering why this would be useful, consider that the image backup volume could be snapped and cloned to a new volume on another server for fast refreshes of lower environments.  Having the incrementals and images in separate locations reduces the space requirements for the snap/clone.

Backup Command:

BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'MyImageBackup' DATABASE;


texts: Not enough server storage is available to process this command - RESTORE DATABASE is terminating abnormally

texts:
First a little description of the system that I'm seeing the problem on:

My firm has a regularly scheduled restoration of a database from backups of another, I won't go into the business reasons for this.

The database being restored is on an instance of SQL Server 2014 with SP2-CU1 updates which is running on a Windows Server 2012 R2 VM.

The drive on which the mdf/ndf/ldf files are, has 1.19TB free space, and the database occupies some 863GB. The .bak files themselves are held There are also some other database files on this drive leaving 244GB free, which I assume is enough for it to do a restore without any issue related to insufficient working space.

Over the weekend we do a restoration from a full backup, and every other day is from a differential. We sometimes see the restoration fail multiple times in a row, at present I've had some 8 or 9 consecutive failed attempts since Monday. This usually occurs when restoring from the full backup which leads me to believe that it's a performance issue with SQL Server when handling large databases, or it might be something funny with the VMs?

The error I am seeing when it fails is below. This can occur at seemingly any point during the restoration, for instance the last attempt it got up to 76% complete on restoring the full backup before keeling over.

Msg 3203, Level 16, State 1, Line 2
Read on "\\VM\Backups\TheDatabase.bak" failed: 1130(Not enough server storage is available to process this command.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 4319, Level 16, State 5, Line 3
A previous restore operation was interrupted and did not complete processing on file 'TheDatabase'. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

There is a project to do partitioning so we need only backup and restore the hot portion of the DB, which should hopefully resolve this, but I am looking for anything in the interim which could potentially reduce the occurences of this or stop this from happening entirely. Let me know if more info is needed to diagnose this.

Any help would be greatly appreciated!


texts: move a postgres cluster to another machine

texts:
I have a postgreSQL 8.3 cluster I should move onto a new machine which runs postgres 9.3 (Ubuntu 14.04) .

First of all I copied my whole cluster and I pasted into /etc/postgresql/9.3/<new_clustername>.

Then, I set my default cluster not to be automatically loaded when the service starts by editing /etc/postgresql/9.3/main/start.conf and setting it to manual.

Then, I created /etc/postgresql/9.3/<new_clustername>/start.conf setting it to auto.

If I try to restart postgres I get a "No PostgreSQL clusters exist; see "man pg_createcluster" error. I guess something wasn't OK. Any suggestion?


texts: How to import table's data in MySql from Sql Server?

texts:
I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error.

Here this error may be occurring because table in Sql Server has a column with data type image and table in MySql this column's data type is LONGBLOB.

Please provide your expert answers. If not possible through SQL Server Import and Export Wizard than please suggest any other method for data transfer.


texts:
Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the same server address and a device to use multiple server addresses, so there is a many to many mapping between them.
I am a little confused how I can represent the relationship between them in SQL, so far I am using a third table with the primary keys from the address table and device table and perform two joins in the query, but I'm not sure if this is the best way.


texts: What is the use of ibdata1 file in mysql?

texts:
I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions.

Solution 1: innodb_file_per_table
Solution 2: innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Solution 3: innodb_data_file_path=ibdata1:10M:autoextend:max:50M

And I really dont have knowledge about the ibadata1 file, can anybody help me on this and I want to know whether is it safe to fix the ibdata size(solution 3) and what does the ibdata exactly for?


texts: MS SQL full datetime to only month and date

texts:
i am using ms sql server, i have date filed with value '01-Jan-2016 12:12:03PM'
from this format i need something like 'January-2016'
how can i do this?
with this i need to count something by month.

MY code following

select
DATENAME(MONTH,p._DATE) AS AC_MONTH_,
COUNT(CASE WHEN p.PC=266 THEN 1 END) AS PRE_DATE,
COUNT(CASE WHEN p.PC=1325 THEN 1 END) PP_DATE
from true_all p
where  p._DATE>'01-Jun-2016'
group by DATENAME(MONTH,p._DATE)
order by DATENAME(MONTH,p._DATE) desc


texts:
i have created a linked server from sql server and i am now trying to access the table in oracle using the following trigger to insert data into oracle. i cannot refer the table name in oracle. getting the intellisense as invalid tablename.
CREATE TRIGGER ifs_limss  ON  Culligan_Dev.dbo.C_LIMS_IFST   AFTER INSERT AS
BEGIN

SET NOCOUNT ON;

INSERT INTO IFS.IFSAPP.C_LIMS_IFS
    (id, [name])

SELECT I.id, I.[name]
FROM INSERTED AS I

-- Insert statements for trigger here

END
GO


texts:
Suppose I have an equities pricing table with a 1000 million rows. I need to be able to maintain consistency, read and write to it at high speed with a large number of clients.

What I'd like is a partition that splits the table into two. An 'archive partition' that has a clustered index and has the older (by time) 90% of the data. I'd also like a 'recent partition' that contains the most recent 10% with a non-clustered index.

I'm not sure if this is possible.

My question is: Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions?


texts: Find a row by primary key, when you don't know in which table it is?

texts:
I'm the (inofficial) DBA responsible for a proprietary OODBMS. Management wants us to move to Postgres, to reduce license costs. The move should be progressive, therefore we should also keep the same data-structure as in the OODBMS. Luckily, with the support for ARRAYs, and table inheritance, we can literally create an identical schema in Postgres. All tables will use bigint as primary key, and all (indirectly) inherit from the same base table.

The biggest issue is this: Our bigint key are (and must be) unique across all tables, and we must be able to quickly load a set of rows, based on their primary key, without knowing in which table they are. The rows will be spread over any and all tables. The primary concern here is speed, rather than disk or memory usage.

Expressed differently, what we need is a unique index across all tables. AFAIK, this is not possible in Postgres. What is the "next best" option? I'm open to any solution, including using, or maybe even coding, some "Postgres extension".

To give some hints about the actual DB, we are talking about 300 tables, 130M rows, and about 300GB in size (OODBMS size).


texts: Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift

texts:
I'm quite new to using POSIX regex in Redshift. I honestly think that this pattern must be compatible.
[^A-Za-z0-9\\.\\,-]+

However, as I've tried a query like this
SELECT * FROM my_table WHERE string_column ~ '[^A-Za-z0-9\\.\\,-]+';

it gives me a result that doesn't match with what I like, and it seems to be considering the caret as a character literal. I've also tried using [^[:alnum:]] to no avail. What could be the reason why this happens? Did I input something wrong, or is this a limitation within Redshift or the JDBC drivers?
I'm currently running queries through JetBrains DataGrip (latest version) and running it with the latest Amazon Redshift JDBC driver.


texts: Postgresql Upgrade from 9.1.9 to 9.4.5 pg_stat_activity.procpid causes failure

texts:
Attempting to upgrade from 9.1.9 to 9.4.5 Postgresql.

pg_upgrade process fails while trying to pg_restore pg_stat_activity. This fails because the column name differs from the old system to the new system.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 498; 1259 26457 VIEW blocking_procs postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column a.procpid does not exist
LINE 14: ...bl" JOIN "pg_stat_activity" "a" ON (("bl"."pid" = "a"."procp...

How does one solve this issue and use pg_upgrade to move from 9.1.9 to 9.4.5?


texts: SQL Server replication model snapshot, transactional and merge - which method can be used

texts:
I am trying to implement sql server database replication between 2 branch servers to a Head Office Server.

My application is a distributed one, the main application is hosted on head office which is controlling the masters and final approvals. The branch servers are located on two other countries, which are used to enter daily transactions.

Since internet bandwidth is too slow, I am planning to run the replication only on off hours (ie.. night 12 AM to Morning 8 AM). During business hours it is difficult to synchronize. All tables are designed such a way to validate and avoid duplication or other errors.

Also there are chances of internet outage for couple of days.. may be up to a week.

There are three type of tables,

BI Directional - Needs to sync between both sides(HO to branch and branch to HO, Approvals)
Sync from Branch to HO (Transactions)
Sync from HO to Branches (Masters)

When I configure replication, I am confused between different types of replication such as snapshot, transactional and merge replication.

Can anybody suggest which one is the best method for my model

I am also facing some issues with Primary key and foreign keys lost after configuring replication.. Any idea why this is happened..?


texts: Default value for UUID column in Postgres

texts: In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?


texts: Posgres 10 database occupied more extra disk space

texts:
I executed select datname, pg_size_pretty(pg_database_size(oid)) from pg_database in my Postgres 10 database. It gives that one of my database occupied 165 GB memory as result.

And When I executed select pg_size_pretty(sum(pg_relation_size(oid))) from pg_class; by selecting this database. It gives 125 GB occupied disk space.

So here I can not find out where is the other disk space (40 GB) used?

Is it used for caching? If so then it must not grater than 4 GB because I checked show effective_cache_size; it gives me 4 GB.

So can someone tell me where exactly that other disk space is used?


texts: What is the default column name given to a TABLE() type with a single column

texts:
CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT [¿what goes here?] as missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))

What is the default column name that should be in [¿what goes here?] in the above example?


texts: Escaping a single quote in SQL Server 2005

texts:
I am using SQL server 2005 and using the northwind database. I need the supplier id of  Grandma Kelly's Homestead and I used this query:

select supplierid from suppliers
where companyname ='Grandma Kelly''s'' Homestead'

I checked the link Escaping a single quote in SQL Server, but I can't find an answer.

I used the two single quotes but it didn't work since the query is executing but it doesn't return a value.

How can I escape the single quote and retrieve the value? I would also like to know how to insert the values into the table having quotes


texts:
I was inserting two data sets, using minimal logging, into an empty heap table using via two Execute SQL Tasks running in parallel and with SQL of the following form.

INSERT INTO Table (TABLOCK) SELECT FROM ...

After the job hangs a bit, one of the SQL tasks became a deadlock victim. Below is XML output of the deadlock graph.

Can someone explain what was happening under the hood?

  <resource-list>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process9609dc8" mode="Sch-S"/>
     <owner id="process9609dc8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process5e13048" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
   <objectlock lockPartition="0" objid="1586156746" subresource="FULL" dbid="7" objectname="dbo.TargetTable" id="lock7374a00" mode="IX" associatedObjectId="1586156746">
    <owner-list>
     <owner id="process5e13048" mode="Sch-S"/>
     <owner id="process5e13048" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9609dc8" mode="X" requestType="convert"/>
    </waiter-list>
   </objectlock>
  </resource-list>

Things get a lot trickier because I found that for most cases the two Execute SQL Tasks can run in parallel successfully. Try below:

Create table dbo.TablockInsert (c1 int, c2 int, c3 int)

--then issue the script in two Execute Sql Task in parallel you won't fail:
insert into dbo.TablockInsert(TABLOCK) SELECT 1, 1, 1

Since the only difference is the SELECT... FROM... statement, looks like the SELECT... FROM... statement can have an impact on the lock mode here?


texts: Why does pg_dumpall use the wrong encoding in Ubuntu 16.04 / Postgres 9.5?

texts:
I cannot run a "pg_upgradecluster" because there are problems with pg_dumpall. I have set the encoding to UTF8 everywhere on the server but pg_dumpall always uses

"SET client_encoding = 'LATIN1';"

. Where does this "LATIN1" come from?
postgres=# show server_encoding; show client_encoding;

server_encoding
-----------------
 UTF8
(1 row)

 client_encoding
-----------------
 UTF8
(1 row)

postgres@ubunt:~$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

postgres@ubuntu:~$ env
SHELL=/bin/bash
TERM=xterm
USER=postgres
MAIL=/var/mail/postgres
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin
PWD=/var/lib/postgresql
LANG=en_US.UTF-8
SHLVL=1
HOME=/var/lib/postgresql
LOGNAME=postgres
XDG_DATA_DIRS=/usr/local/share:/usr/share:/var/lib/snapd/desktop
_=/usr/bin/env

postgres@ubuntu:~$ cat /etc/default/locale
#  File generated by update-locale
LANG=en_US.UTF-8
#LANGUAGE="en_US:en"

Because of the wrong encoding I get errors:

pg_dump: Dumping the contents of table "TBLDATEN" failed:
PQgetResult() failed. pg_dump: Error message from server: ERROR:
character with byte sequence 0xe2 0x80 0xa6 in encoding "UTF8" has no
equivalent in encoding "LATIN1"

I have the same problem with pg_dump but then I can use

pg_dump -E UTF8

to set the correct encoding.

postgres@recworus157:~$ psql -l | grep LATIN
postgres@recworus157:~$ psql -l
                                         List of databases
         Name         |    Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
----------------------+-------------+----------+-------------+-------------+------------------------
 Liste1               | cpl_user    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste2               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste3               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste4               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste5               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 Liste6               | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
...


texts:
I have a sql script file which is executed from a batch file and is supposed to execute some sql scripts and write errors and output to files. Here is an example of how the script file looks like:

:error ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.err"
:out ".\DataAndTableScripts\9.0.0\_RunOrderedScripts.out"
:on error ignore
go
set nocount on;
print 'Running .\DataAndTableScripts\9.0.0\OrderedFileList.txt'
print 'Start time: ' + convert( varchar, getdate(), 121 );
GO
:!!echo   Table_auth.IdentityApplication.sql
:error ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.err"
:out ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.out"
:r ".\DataAndTableScripts\9.0.0\Table_auth.IdentityApplication.sql"
go

The :!!echo  Table_auth.IdentityApplication.sql piece is suppose to print this text in the command prompt window. I initially had this code on SQL 2014, but when I upgraded to SQL 2016 and to command line utilities 13 this echo piece stopped working. It seems like, even when I have :on error ignore, when it gets to :!!echo, if fails and the rest of the script is not executed.

Anyone has an idea of what the issue may be?


texts:
I'm experiencing a very long response time for a query returning a relatively small number of rows. It takes over three minutes to return ~1.3 million rows. I would assume it was an indexing problem except counting the rows takes almost no time at all.

Amazon AWS: It's running on an RDS t2.medium with a SSD (IOPS disabled) according to AWS console.

The table:

CREATE TABLE IF NOT EXISTS ip_range_domains (
  ip_range_domain_id     BIGSERIAL PRIMARY KEY,
  domain_id              BIGINT REFERENCES domains       NOT NULL,
  source_type_id         INTEGER REFERENCES source_types NOT NULL,
  low                    INET                            NOT NULL,
  high                   INET                            NOT NULL,
  auto_high_conf         BOOLEAN                         NOT NULL DEFAULT FALSE,
  invalidation_reason_id INTEGER REFERENCES invalidation_reasons  DEFAULT NULL,
  invalidated_at         TIMESTAMP WITHOUT TIME ZONE              DEFAULT NULL,
  created_at             TIMESTAMP WITHOUT TIME ZONE     NOT NULL DEFAULT current_timestamp
);
CREATE INDEX domain_id_btree ON ip_range_domains (domain_id);

I also created a hash index on it afterwards but it doesn't seem to have any effect.

The slow query:

SELECT * FROM ip_range_domains WHERE domain_id = 400266;

The query above took 224.9 Seconds:

=> SELECT COUNT(*) FROM ip_range_domains WHERE domain_id = 400266;
  count
---------
 1383530

The query above took 164 milliseconds to return:

=> SELECT COUNT(*) FROM ip_range_domains;
  count
----------
 23156869

The query above took 1.9 seconds to complete:

=> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ip_range_domains WHERE domain_id = 400266;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ip_range_domains  (cost=26398.17..282905.77 rows=1410288 width=55) (actual time=94.046..476.018 rows=1383530 loops=1)
   Recheck Cond: (domain_id = 400266)
   Heap Blocks: exact=44000
   Buffers: shared hit=47783
   ->  Bitmap Index Scan on test_index_9  (cost=0.00..26045.60 rows=1410288 width=0) (actual time=85.699..85.699 rows=1383530 loops=1)
         Index Cond: (domain_id = 400266)
         Buffers: shared hit=3783
 Planning time: 0.122 ms
 Execution time: 697.753 ms
(9 rows)

Postgres Version:

=> select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
(1 row)

RDS Info:

db.t2.medium
IOPS: disabled


texts:
To educate myself in PHP and mysql, I am making a browser based rpg game. This game will allow any number of users to register an account and, once registered, they can add, edit or delete champions into my database. The problem is that they should ofcourse only be able to edit and delete champions that they made themself.

Now since this is just a practise project, I don't forsee large quantities of data or any serious attempts to hack it, but all the same I'd like to do it right so I can use this experience if I ever do get to work on large serious projects.

Should I
a) Let a new database be created for each user?
b) Let a new set of tables be created for each user?
c) Put the data of all users together in the same 3-5 tables and track for each row which user created it?

I think that C is the cleanest solution with the least data consumption, but perhaps the one with the least security. Is this correct?

And how would you suggest I implement solution C? Is there a privalege that restricts a user from only editting / deleting rows that he/she created him/herself? Or should I include this restriction in the PHP level? Would having this restriction in the PHP level not create a security risk since someone might be able to connect to my database without my php scripts?

And should a new mysql user be made for each player? Or can they all share a user account and make the distinction between players with an ID field or something like that?


texts: MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records exist

texts:
I've been trying to find a solution for this problem using just 1 join. Is it possible?

table:

pkey | uid | A | B | C
100  | 1   | 5 | 2 |
101  | 1   | 5 | 3 | 'should find this'
102  | 1   | 4 | 6 |
103  | 2   | 5 | 2 | 'should find this'
104  | 3   | 2 | 7 |
105  | 3   | 1 | 1 |
106  | 3   | 2 | 1 | 'should find this'

Now i need to select records with pkey 101 and 103. I use this query to select max(B).

SELECT table.*
FROM (
     SELECT uid, max(A) as maxA
     FROM table
     GROUP BY uid
) as maxlog
JOIN table
ON table.uid = maxlog.uid
AND table.A = maxlog.maxA

It returns two records for uid 1 (pkey 100 and 101). How can i filter on max(B) in the same query with adding another JOIN?


texts: Allow a user to create folders and linked reports, but not "real" reports

texts:
I have a need to allow certain users the ability to create folders and linked reports within specified folders, but I don't want them to be able to put actual reports in those folders. Is there a magic combination of permissions I can put on a role that will allow this? I'm assuming the "Create linked reports" permission applies to the source report that's being linked, and I'm not sure if linked reports fall under "Manage reports" or "Manage resources".

(For the curious, I want to be able to differentiate between the people developing and deploying reports to a designated location, and people linking those reports to directories that certain end users will have access to.)

Edit:

Just to make it clearer what I'm trying to do... Say I've got two directories on the report server: Base Reports, and Sales Department. I want the developers to be able to create reports in Base Reports (no problem there). I also want to allow another group to be able to create linked reports in Sales Department which are based on reports within Base Reports. I do not want this group to be able to create actual reports within Sales Department.

In order to allow the second group to create linked reports based on what's in Base Reports, I set Create Linked Reports permission on the Base Reports directory. What do I have to set on Sales Department to allow them to put the linked reports in there, without allowing them to create actual reports?


texts: Why is there an aggregate in this UPDATE plan?

texts:
Given this

declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));

insert @Data(id, fact)
values
    (1, 'a'),
    (1, 'b'),
    (2, 'c'),
    (2, 'd'),
    (2, 'e');

-- Form a list of unique id values
insert @Summary(id, collected) select distinct id, '' from @Data;

-- Accumulate the fact values into collected
update s
set collected = collected + d.fact
from @Summary as s
inner join @Data as d
    on d.id = s.id;

select * from @Summary;

I had expected

id   collected
---  ---------
1    ab
2    cde

However what I get is

id   collected
---  ---------
1    a
2    c

The first fact per id is written and the others skipped. The reason is obvious from the plan

where the Stream Aggregate passes through the first row per id; 5 rows are passed to it from the Nested Loop and it passes 2 rows to the Compute Scalar.

The result and general plan shape is the same with and without primary keys. Changing to temp tables, or real tables makes no difference. I can reproduce on SQL Server 2017 and 2019.

My question is what is the theoretical basis for inserting the aggregate into the plan? My best guess is it is a form of Halloween protection. I understood its purpose was to prevent a row leapfrogging ahead of the current scan position and hence being updated a second time. I can sort-of see that applying here as a row in @Summary would be touched many times in the absence of the aggregate. It seems a very expansive application of Halloween protection, though.

I know there are ways to achieve this - STRING_AGG being the most obvious. My real use-case is around constructing JSON. This is just a minimal reproducible example. My specific question here is about understanding the semantics and behaviour of the optimizer.


texts: PostgreSQL query and shape in json array?

texts:
I have a json array stored in my postgres database in jsonb column. The json data is facebook ad data and look like this:

{
"cpc": "1.1",
"cpm": "11.1",
"cpp": "11.1",
"ctr": "0.11",
"ad_id": "11",
"reach": "1",
"spend": "1.1",
"clicks": "11",
"actions": [
    {
        "value": "11",
        "action_type": "video_view"
    },
    {
        "value": "1",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    }
],
"ad_name": "ad name here",
"adset_id": "11111",
"date_stop": "2015-05-25",
"frequency": "1.0511",
"adset_name": "adset name here",
"date_start": "2015-05-20",
"campaign_id": "11111111",
"impressions": "1111",
"account_name": "Account name here",
"campaign_name": "campaign_name here",
"unique_clicks": "11",
"unique_actions": [
    {
        "value": "111",
        "action_type": "video_view"
    },
    {
        "value": "11",
        "action_type": "post_reaction"
    },
    {
        "value": "11",
        "action_type": "mobile_app_install"
    },
    {
        "value": "11",
        "action_type": "link_click"
    }
],
"publisher_platform": "instagram",
"inline_link_click_ctr": "0.1111"

}

Now, I can select the firt elements like:

select jdata->>'cpc' as CPC,
   jdata->>'ad_name' as ad_name,
   jdata->>'cpm' as CPM,
   jdata->>'cpp' as CPP,
   jdata->>'actions' as actions
from my_table_name

I want to have row data by selecting and it returns similiar kind that I need. But for actions element not. Because "actions" element is a json array. I need the "actions" element like json to row but like this format;

---------------------------------------------------------------------
| ad_name        |actions->video_view  |actions->mobile_app_install |
---------------------------------------------------------------------
| ad name here   |         11          |            1000            |
---------------------------------------------------------------------
| 2 ad name here |         12          |            10001           |
---------------------------------------------------------------------

I have queried with jsonb_array_elements function, but it did not return that i need?

Any help and suggestion appreciated


texts: Creating mongo 3 node replica set on ec2 problems

texts:
I'm trying to create a 3 node cluster of mongo replicas on ec2 instances, with internal ips 10.168.xxx.xxx, 10.166.xxx.xxx and 10.188.xxx.xxx, my config file is:

dbpath = /home/ubuntu/mongo-data/
logpath = /home/ubuntu/mongo-data/mongod.log
logappend = true

journal = true
fork = true
smallfiles = true
pidfilepath = /home/ubuntu/mongo-data/mongod.pid
replSet = appNameReplicaSet

I launched mongod -f mongod.config on each instance, and on one of those I tried:

$ mongo
> rs.initiate();
{
"info2" : "no configuration explicitly specified -- making one",
"me" : "ip-10-168-66-132:27017",
"info" : "Config now saved locally.  Should come online in about a minute.",
"ok" : 1
}
> rs.add('10.166.xxx.xxx:27017');
//after LONG pause
{
"errmsg" : "exception: need most members up to reconfigure, not ok : 10.188.22.254:27017",
"code" : 13144,
"ok" : 0
}

I also tried:

config = {
    _id: 'appNameReplicaSet',
        members: [
        {_id: 0, host: '10.168.xxx.xxx:27017'},
        {_id: 1, host: '10.166.xxx.xxx:27017'},
        {_id: 2, host: '10.188.xxx.xxx:27017'}
    ]
}
rs.reconfig(config, {'force':true})
{ "ok" : 0, "errmsg" : "a replSetReconfig is already in progress" }

.log file:

Sat Jul 27 01:33:59.149 [initandlisten] connection accepted from 127.0.0.1:36931 #4 (1 connection now open)
Sat Jul 27 01:34:02.468 [conn4] replSet replSetInitiate admin command received from client
Sat Jul 27 01:34:02.470 [conn4] replSet info initiate : no configuration specified.  Using a default configuration for the set
Sat Jul 27 01:34:02.470 [conn4] replSet created this configuration for initiation : { _id: "appNameReplicaSet", members: [ { _id: 0, host: "ip-10-168-xxx-xxx:27017" } ] }
Sat Jul 27 01:34:02.470 [conn4] replSet replSetInitiate config object parses ok, 1 members specified
Sat Jul 27 01:34:02.473 [conn4] replSet replSetInitiate all members seem up
Sat Jul 27 01:34:02.473 [conn4] ******
Sat Jul 27 01:34:02.473 [conn4] creating replication oplog of size: 990MB...
Sat Jul 27 01:34:02.474 [FileAllocator] allocating new datafile /home/ubuntu/mongo-data/local.1, filling with zeroes...
Sat Jul 27 01:34:02.485 [FileAllocator] done allocating datafile /home/ubuntu/mongo-data/local.1, size: 511MB,  took 0.01 secs
Sat Jul 27 01:34:02.485 [FileAllocator] allocating new datafile /home/ubuntu/mongo-data/local.2, filling with zeroes...
Sat Jul 27 01:34:02.491 [FileAllocator] done allocating datafile /home/ubuntu/mongo-data/local.2, size: 511MB,  took 0.005 secs
Sat Jul 27 01:34:02.491 [conn4] ******
Sat Jul 27 01:34:02.491 [conn4] replSet info saving a newer config version to local.system.replset
Sat Jul 27 01:34:02.492 [conn4] replSet saveConfigLocally done
Sat Jul 27 01:34:02.492 [conn4] replSet replSetInitiate config now saved locally.  Should come online in about a minute.
Sat Jul 27 01:34:08.435 [rsStart] replSet I am ip-10-168-xxx-xxx:27017
Sat Jul 27 01:34:08.435 [rsStart] replSet STARTUP2
Sat Jul 27 01:34:09.441 [rsSync] replSet SECONDARY
Sat Jul 27 01:34:09.441 [rsMgr] replSet info electSelf 0
Sat Jul 27 01:34:10.440 [rsMgr] replSet PRIMARY
Sat Jul 27 01:34:26.513 [conn4] replSet replSetReconfig config object parses ok, 2 members specified
Sat Jul 27 01:37:45.835 [conn4] couldn't connect to 10.188.xxx.xxx:27017: couldn't connect to server 10.188.xxx.xxx:27017

UPDATE: added output of rs.initiate() & logs;


texts: Send Multiple Attachments With Send DBMail

texts:
I thought that you just needed to separate two file names with a ; but my syntax fails with an error of

Msg 102, Level 15, State 1, Line 251
Incorrect syntax near '@filename2'.

This is my full syntax, what do I need to do in order to attach both files to my email?

Declare @filename1 varchar(max), @filename2 varchar(max)
Set @filename1 = 'C:\Testfiles\Test1.csv'
Set @filename2 = 'C:\Testfiles\Test2.csv'

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email',
  @body = @body1,
  @file_attachments = @filename1;@filename2;


texts: SQL Agent Job Logs - Can't get the Error Log

texts:
I've some logs that I want to extract to a report in order to get the SQL_Agent_Jobs status that runs during the night. If the Job fails then I want to receive the Error Log in a table. I am trying to get that information with this query:

DECLARE @job_id UNIQUEIDENTIFIER

SELECT
    @job_id = job_id FROM msdb.dbo.sysjobs
WHERE
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'

;WITH Error_Output (job_id, error_log) AS
(
    SELECT
        JS.job_id,
        CASE
        WHEN JSL.[log] IS NULL THEN JH.[Message]
        ELSE JSL.[log]
        END AS LogOutput
    FROM
        msdb.dbo.sysjobsteps JS
        INNER JOIN msdb.dbo.sysjobhistory JH
            ON JS.job_id = JH.job_id AND JS.step_id = JH.step_id
        LEFT OUTER JOIN msdb.dbo.sysjobstepslogs JSL
            ON JS.step_uid = JSL.step_uid
    WHERE
        INSTANCE_ID >
        (SELECT
            MIN(INSTANCE_ID)
        FROM
            (SELECT top (2)
                INSTANCE_ID, job_id
            FROM
                msdb.dbo.sysjobhistory
            WHERE
                job_id = @job_id
                AND STEP_ID = 0
            ORDER BY
                INSTANCE_ID desc
            ) A
        )
        AND JS.step_id <> 0
        AND JH.job_id = @job_id
        AND JH.run_status = 0
)
SELECT
    sj.name AS [Job Name]
    ,MAX(sja.run_requested_date) AS [Start Date]
    ,MAX(sja.start_execution_date) AS [Effective Start Date]
    ,MAX(sja.stop_execution_date) AS [Effective End Date]
    ,DATEDIFF(MINUTE, MAX(sja.start_execution_date), MAX(sja.stop_execution_date)) as [Job Execution Time -  M]
    ,CASE WHEN error_log IS NULL THEN 'Job runs successfully' else error_log end as [Job Log]
FROM
    msdb.dbo.sysjobactivity AS sja
    INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
    LEFT OUTER JOIN Error_Output AS error ON error.job_id = sja.job_id AND error.job_id = sj.job_id
WHERE
       [name] like '%JOB_1%'
    OR [name] like '%JOB_2%'
    OR [name] like '%JOB_3%'
    OR [name] like '%JOB_4%'
    OR [name] like '%JOB_5%'
GROUP BY
    sj.name, error_log

However, I'm always getting that the Job runs 'Job runs successfully' and I already test and see that the job failed in execution. I even configured SQL_Agent_Job to receive email whenever the job failed, and I have received the emails and the status in the query does not change to the error log (failed).

What I'm doing wrong?


texts:
I've setup streaming replication between a master and a backup server. Now I want to stop the replication but I've noticed that every time I stop postgres in the backup server some old WALs files stay in the master server.

My question, is there a safe way to stop replication and avoid master accumulating WALs file in the master ?


texts: Trying to understand how to use postgresql generate_series() for this operation

texts:
First of all, im coming from this question, where I was recommended to use generate_series() , but after some adjustments in my db, I dont really understand how to create such sentence
This is the old question: Populate a table that should have an X amount of rows with default values with a big insert query? Or can it done somehow by default?

But I will rephrase the question again:

Those are my initial tables

Users: id(int) PK, weeks_to_live(int)
Calendar: id(int) PK, user_id (int) (FK -> Users(id))*
CalendarField: id(int) PK, text(text), rating(int), calendar_id(int) (FK ->Calendar(id))*, week_number(int)

Point is, when I generate my calendar in my app, actually my Users, and Calendar tables get filled properly.
Upon a generation they will end up looking like this

users

id   weeks_to_live
1    3
2    2

calendar

id   user_id
1    1
2    2

That part is fine, but I need my CalendarField table to be autogenerated too, and thats where supposedly generate_series() should come into action, so they will end up looking like this

CalendarField

id    text     rating    calendar_id   week_number
1     ''       0         1             0
2     ''       0         1             1
3     ''       0         1             2
4     ''       0         2             0
5     ''       0         2             1

As you can observe:

id: is autoincremental
text: always defaults to "",
rating: always defaults to 0,
calendar_id (fk) : is the relation with each calendar, who has an amount of calendarFields row equaling to the number up to weeks_to_live in users
week_number: this number, should begin at 0, and keep incrementing while its < than weeks_to_live in the users table

Now my question is, how do I generate that last insert so my CalendarField table ends up filling with that info?. I suppose that with generate series, I wont need to create an enormous insert with == weeks_to_live values in case it gets huge. But I don't understand how to do it.

Or should I do a giant Insert then?


texts: XQquery for specific attribute of all specific elements in XML

texts:
I am, looking for a query to get all attribute values "NetAmount" of Element "PRODUCT" from the following samle XML:

<ROOT>
 <CATEGORY name="A">
  <PRODUCT NetAmount="10.50"/>
  <PRODUCT NetAmount="20.50"/>
 </CATEGORY>
 <CATEGORY name="B">
  <PRODUCT NetAmount="30.50"/>
  <PRODUCT NetAmount="40.50"/>
  <PRODUCT NetAmount="75.50"/>
 </CATEGORY>
</ROOT>

For my scenario it does not matter wether the XML is stored in a table or variable.

I tried several different approaches but all failed.

Sample:

SELECT
  NetAmount.value('(@NetAmount)[1])','varchar(250)') AS Products
FROM
  @x.nodes('//ROOT/Category/Product')
    AS P(NetAmount)

I usually get the following error message that makes no sense to me:

  Meldung 2370, Ebene 16, Status 1, Zeile 5 XQuery [value()]: Am Ende
  des XQuery-Ausdrucks wurden keine Token mehr erwartet. Gefunden: ')'.


texts: connect to Oracle Cloud from SQLPlus and Instant Client

texts:
I want to try to connect with SQLPlus to Oracle Cloud.

for regular oracle I can connect from remote machine as follows:

IP = 1.2.3.4
PORT = 1521
ServiceName = "ORCL1"
Username = "username"
Password = "password";

sqlplus username/password@1.2.3.4:1521/ORCL1

Is it possible to connect also to oracle cloud in a similar way?

Next step is to connect Oracle Instant Client to Oracle Cloud, but I want to do so without any installation (except copy of DLLs, but without need of editing 'tnsnames.ora' etc.).

I use occi interface like this:

IP = 1.2.3.4
PORT = 1521
ServiceName = "ORCL1"
Username = "username"
Password = "password";

env->createConnection("username", "password", "1.2.3.4:1521/ORCL1");

Can I connect in the same way (or how to connect) to Oracle Cloud?


texts:
I need to store the exact value of a decimal:

For example 0.00500 and 0.0050.

If I use decimal (10, 5), I receive 0.00500 every time. If I enter 0.0050, I still get 0.00500


texts: How to guard against backup corruption due to spurious bit errors?

texts:
There's always a very small chance that a backup that was just written to disk is corrupted. Disks are not 100% reliable. Bits can be flipped with very low probability. I have seen that happen on multiple machines on otherwise well-functioning disks.

It is possible to detect such errors using backup checksums. But what do you do when an error is found? If a log backup is corrupted the log backup chain is broken. If it is a full backup depending differential backups are now unusable. A new full backup is needed in order to restart the usual backup schedule.

What can be done to avoid losing backups randomly due to spurious data corruption?


texts:
I have to truncate a table which gave me the below error.

  ORA-02266: unique/primary keys in table referenced by enabled foreign keys.

I fixed that by checking the below,

SELECT p.table_name "Parent Table", c.table_name "Child Table",
    p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
    FROM dba_constraints p
    JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
    WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
    AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
    AND p.owner='SCHEMA_NAME'
/

alter table schema.table_name disable constraint SYS_constraint_name;

truncated the table.

Now I am trying to enable the constraint but getting the below,

enable constraint ora-02298 cannot validate - parent keys not found

So I use the below query to identify the records in child table,

SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)

I am unable to delete the above as they have child tables which are acting as foreign keys.

Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints


texts: TABLOCK for both truncate and subsequent bulk insert

texts:
I'd like to truncate a table and then run a bulk insert on it with some new data.
I tried a query something like this:
BEGIN TRANSACTION;

TRUNCATE TABLE db.dbo.table;

BULK INSERT db.dbo.table
FROM 'file.txt'
WITH (TABLOCK, FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a');

COMMIT TRANSACTION;

However it seems like I can query a time between the truncate and the insert. Granted this is a small time window but I would like to eliminate it entirely.
How can I edit the query so the TABLOCK (or better TABLOCKX?) locks the table from when the truncate begins to when the bulk insert ends?


texts: How are these two SQL Server rollbacks different?

texts:
In SQL Server 2008 R2, how are these two rollbacks different:

Run an ALTER statement, for a few minutes, and then hit 'Cancel Executing'. It takes a few minutes to rollback completely.

Run the same ALTER statement, but this make sure that the LDF file is not big enough for it to complete successfully. Once the LDF limit is met and no 'autogrowth' is allowed, query execution stops immediately (or a rollback happens) with this error message:

The statement has been terminated.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'SampleDB' is full.
To find out why space in the log cannot be reused, see the
log_reuse_wait_desc column in sys.databases

How are these two different on the following points?

Why is the second 'rollback' instantaneous? I'm not entirely sure if it could be called a rollback. My guess is, the transaction log is written as the execution progresses and once it realizes that there is not enough space to fully complete the task, it just stops with some 'end' message, without commit.

What happens when the first rollback takes so much time (is a rollback single threaded)?
2.1. Does SQL Server go back and undo the entries made in the LDF file?
2.2. The LDF file size gets smaller at the end of the rollback (from DBCC SQLPERF(LOGSPACE))

One additional question: During the second scenario, SQL Server starts consuming LDF file pretty quickly. In my case, it increased from 18% usage to 90% usage in the first few minutes (< 4 mins). But once it reached 99%, it stayed there for another 8 mins, while fluctuating usage between 99.1% to 99.8%. It goes up (99.8%) and down (99.2%) and up again (99.7%) and down (99.5%) a few times before the error is thrown. What is happening behind the scenes?

Any MSDN links that could help explain this more are appreciated.
At Ali Razeghi suggestion, I'm adding perfmon : Disk Bytes/sec
Scenario 1:

Scenario 2:


texts: Triggers or views on frequently updating table

texts: We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database server consumes almost 100%. So now we are thinking to apply triggers on all tables that are used in this scheduler (which runs a SP to update that table) or should we make a simple view? Any help is appreciated. Thanks.


texts: What are the potential causes for a sudden MySQL slowdown?

texts:
A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What could cause such a problem?

A few more information: The mysql version is 5.0.51a The mysql database is on a dedicated box with 1.5 GB ram. There was no swap being used when the mysql server was slow.

Here's the mysqlreport from the database http://pastebin.com/au6yMWqQ None of the caches are full..

I've noticed that the DNS server used by the server for resolving hostname was a bit slow sometimes lately. Could this have an impact?

Thanks,
I've crossposted this on serverfault, hope it's ok...

EDIT: Could I try to have a master master replication on ec2 to protect against the problem happening again? Half of the instances connecting to the mysql database are on ec2 (the rest directly from layeredtech)


texts: Can I manage NULL = 'SomeDefault' in the database so applications and SQL don't have to remember this optimization?

texts:
I have a table with lots of default values, and want to store very common values as NULL to save space, without forcing my SQL statements or application logic to become overburdened with conditionals.

Ideally, I would like this equivalent of an INSERT and UPDATE trigger:

if (NEW.field_a = 'SomeDefault')
    NEW.field_a = NULL;

With the ability for this query to return results:

SELECT * FROM table WHERE field_a = 'SomeDefault'

And the ability for this result to be > 1:

SELECT field_a, count(1) FROM table GROUP BY field_a HAVING field_a = 'SomeDefault'

And for this join query...

non_optimized
|| w          || x   ||
| SomeDefault | foo  |
| OtherValue  | bar  |

optimized
|| y          || z   ||
| NULL        | 1.1  |
| OtherValue  | 2.2  |

SELECT * FROM non_optimized JOIN optimized ON w = y;

... to produce this output:

| SomeDefault | foo | SomeDefault | 1.1 |
| OtherValue  | bar | OtherValue  | 2.2 |

Can some combination of triggers, or table config, accomplish these requirements?


texts: Better way to select products that are related to ALL of the categories?

texts:
I am trying to select products that are related to ALL of the categories, that has been "handed" to me.

The products are in the Product table and the relation to the categories are in the ProductCategoryRelation table.

The below code works, but I am not sure it is the fastest way to do it.

The 1,2,3 numbers are the category IDs.

SELECT *
FROM Product
WHERE (
    SELECT Count(*)
    FROM ProductCategoryRelation
    WHERE ProductCategoryRelation.CategoryId IN (1,2,3)
        AND ProductCategoryRelation.ProductId = Product.ProductId
    ) = 3