texts: function to check whether the current server is involved in an availability group and if so, whether it is the primary

texts:
I used to have 2 servers on an availability group, but there was a problem with one  of them servers involved,while patching, and to make a long story short I removed all the databases from the availability group.

The way I managed execution of jobs only in the primary server was mainly through this script which tests whether the database is in the primary replica:

  If sys.fn_hadr_is_primary_replica ('apcore') =1
BEGIN

      EXEC [APCore].[app].[usp_upd_applicationStatusTimeExpired]

END

But now that the current server is the only server in the Availability group,
this script

  select sys.fn_hadr_is_primary_replica ('apcore')

retuns null, and therefore is not accurate.

I have been developing a function that should return a bit 1 in one of the following cases:

1 - we are not part of an availability group

2 - we are part of an availability group and we are the primary server

The question is:

Would this function work for a distributed availability group?
Is there any other situation that I haven't thought of that might prevent this function to return the expected value?

and here is the function:
the function is to be used inside jobs and check whether we should run the job (either because we are in an availability group and we are the primary, or we are not in an  availability group at all).

--=========================================
-- scalar-valued function dbo.check_HADR_status
-- returns 1 when either primary or we are standalone
-- (not part of an availability group)

-- USAGE:
-- SELECT MASTER.dbo.check_HADR_status()
--=========================================

USE MASTER
GO

IF OBJECT_ID (N'dbo.check_HADR_status') IS NOT NULL
   DROP FUNCTION dbo.check_HADR_status
GO

CREATE FUNCTION dbo.check_HADR_status()
RETURNS BIT
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN (SELECT CASE WHEN EXISTS (select * from sys.availability_replicas) THEN
                            CASE WHEN  (   SELECT COALESCE(a.role_desc,'RADHE')
                                             FROM sys.dm_hadr_availability_replica_states AS a
                                             JOIN sys.availability_replicas AS b
                                             ON b.replica_id = a.replica_id
                                             WHERE b.replica_server_name = @@ServerName) LIKE 'PRIMARY' THEN 1
                                 ELSE 0
                            END
                  ELSE 1
                  END)
END
GO

IF (SELECT MASTER.dbo.check_HADR_status()) = 1
   BEGIN
      PRINT 'RUN THE JOB STEP'
   END
ELSE
   BEGIN
      PRINT 'DON''T RUN THE JOB AS WE ARE IN A SECONDARY REPLICA'
   END


texts: How do I go from a PostgreSQL native point type to a PostGIS point?

texts:
If I have a point

SELECT point(long,lat);

How do I go from that to a PostGIS point?


texts: SQL design for hierarchical structure where parent unit should not exist without subunits

texts:
I'm new to SQL so I wasn't sure what to even search for this besides looking at hierarchical structures which don't seem to be exactly my use case.

I'm building an app where a Post contains multiple Pages, each containing multiple Videos and Images. I currently have a table for a Post, a Page, a Video, and an Image where a Page contains a reference to its post id and a Video or Image each contain a reference to their page id. I thought this would work, but I need to insert the Post object in order to get its id to store in each Page object, but a Post object should not exist without pages. And yet a Page should not exist without belonging to a Post object. So I'm not sure how to structure these tables.


texts: How to import blanks as nulls instead of zeros while importing txt using wizard

texts:
I'm using the Import Wizard to load a text file and need blanks in integer fields to be nulls, but only zeros are inserted.

How to import it properly?


texts: Understanding aggregate window functions

texts:
Consider the following table:

CREATE TABLE T1
(
  keycol INT         NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1   VARCHAR(10) NOT NULL
);

INSERT INTO T1 VALUES
  (2, 'A'),(3, 'A'),
  (5, 'B'),(7, 'B'),(11, 'B'),
  (13, 'C'),(17, 'C'),(19, 'C'),(23, 'C');

Currently, I am looking into window functions and am trying out aggregate window functions. Although I feel I understand how the windows are defined with the OVER and PARITION clauses, I am unsure how the aggregate window functions are being calculated, such as AVG() OVER ().

I am looking to understand the following three queries.

-- Query 1
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      7
     7 | B    |      7
    11 | B    |      7
    13 | C    |     18
    17 | C    |     18
    19 | C    |     18
    23 | C    |     18

-- Query 2
SELECT keycol, col1, AVG(keycol) OVER (ORDER BY keycol) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      3
     7 | B    |      4
    11 | B    |      5
    13 | C    |      6
    17 | C    |      8
    19 | C    |      9
    23 | C    |     11

-- Query 3
SELECT keycol, col1, AVG(keycol) OVER (PARTITION BY col1 ORDER BY keycol) AS RowAvg
FROM T1

keycol | col1 | RowAvg
-----: | :--- | -----:
     2 | A    |      2
     3 | A    |      2
     5 | B    |      5
     7 | B    |      6
    11 | B    |      7
    13 | C    |     13
    17 | C    |     15
    19 | C    |     16
    23 | C    |     18

Query 1: I believe RowAvg should be the average of the rows for each col1 level. Are the numbers 2 and 7 the FLOOR of the average or is my understanding incorrect?

Query 2: I am not too sure what is being done to produce RowAvg here. As no PARTITION or framing is used here, I believe the window should be the entire table, is this correct? Also, how is the RowAvg being found?

Query 3: Is this finding the (FLOOR) average for each partition however doing this incrementally? That is, for row 1 of the first partition ('A'), we find the average of that row. Then, for row 2 of the first partition, we find the average of the first 2 rows.

General question: Does introducing ORDER BY into the aggregate window function perform the aggregate function 'consecutively' such as in queries 1 and 2? It is interesting to see that in query 1, AVG is performed to each partition as a whole, whereas in queries 1 and 2 the RowAvg's are almost different for each row.


texts: What ciphers need to be enabled to install and run SQL Server 2014 SP1 on Windows 10?

texts:
Our organization is trying out Windows 10 and our distribution had some modifications to the ciphers in use due to recent security concerns.

As it stands SQL Server 2014 Developer SP1 fails to install properly. The services fail to start. I have tried enabling TLS 1.0 trough TLS 1.2, this led to me being able to start the SQL Engine using Local Service Account. But not SQL Agent or Browser. Nor could i connect to the server locally with SMSS all logins fail (sa or the current windows user).

Disabled SSL/TLS:
SSL 3.0 / TLS 1.0 / TLS 1.1
Enabled TLS:
TLS 1.2

Disabled Ciphers:
DES 56/56
NULL
RC2 128/128
RC2 40/128
RC2 56/128
RC4 40/128
RC4 56/128
RC4 64/128
RC4 128/128
Enabled Ciphers:
AES 128/128
AES 256/256
Triple DES 168/168

Hashes:
MD5 (Disabled)
SHA (Enabled)
Diffie-Hellman (Enabled)
PKCS (Enabled)

Ciphers are then sorted from most secure to least secure.

Setup Bootstrap Log:

  Feature:              Database Engine Services
  Status:               Failed: see logs for details
  Reason for failure:   An error occurred during the setup process of the feature.
  Next Step:            Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
  Component name:       SQL Server Database Engine Services Instance Features
  Component error code: 0x851A0019
  Error description:    Could not find the Database Engine startup handle.

ERRORLOG when trying to start SQL Server Engine during setup:

Error: 17190, Severity: 16, State: 1.
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

My next course of action is to try to enable the most likely culprits like SSL 3.0 and then move on to enabling ciphers.

But if anyone can shed some light as to the requirements of SQL Server 2014 SP1 or if i am looking in the wrong place, please let me know.


texts: Where is the documentation for SQL Server's blocked process report?

texts:
I'm trying to work with the blocked process reports captured using Extended Events and, while I can find numerous blog articles and Q&A about specific details (e.g.1 2 3), I can't find any documentation from MS about the contents of the report & how to interpret each field.
For example, I'd like to know:

What are clientoption1and clientoption2
Why do I sometimes get an object_id that doesn't exist in my database (like this question)
What does index_id indicate (probably answered at link above)
How to decode waitresource (here's a start)
... and the list goes on.

I don't just want answers to these questions, I want something more complete.
Is this an undocumented feature or am I just crap at finding it?


texts: SQL Server index rebuild cause database growth

texts:
I have a 80GB database with about 20GB of indices size. Whenever I build the indices online the database file will grow to gain 20GB of unallocated space, and if the unallocated space is less than 20GB then it will grow to reach the 20GB of unallocated space. It's clear for me that the index is being created then the old one is being deleted.

How can I avoid this growth while we rebuild the index online? I tried to turn the SORT_IN_TEMPDB = ON but this didn't help.


texts: When I go to databases in phpPgAdmin it says that there are 0 databases. Yet, if I run SELECT datname FROM pg_database it displays all databases OK. I would like to be able to 'explore' tables and databases.


texts: sqlplus SP2-0341 (line overflow during variable substitution)

texts:
I want to export an Oracle database to a file and i'm using SQLDeveloper on Windows for that (the resulting SQL text file is about 35MB). This results in very long lines.

I hit a snag when importing the file using sqlplus on linux:

SP2-0341: line overflow during variable substitution (>3000 characters at line 1)

This occurs on multiple lines.

I see no options on how to limit the length of the lines when exporting. I've tried to set line size and then export, to no avail. I've also tried to edit the file but the vim tools i used focus on explicit line length and not the semantics of SQL (they'll break strings). Also my regex-fo can only go as far as surrounding the VALUES keyword with newlines, but even then the lines are still much longer than 3000. Also, 35MB is a lot to edit by hand.

I'm limited to those tools and RDBMS but i'm open to suggestions.


texts: SQL Server: grant select access to a user in a view and not in its tables

texts:
I have a SQL Server 2012 instance with a few databases. In one of them I created a view, that selects tables in more than a database.

I want a user to be able to select that view, but it must not select its tables. The view was created exactly because the user can't select the tables.

I've read https://stackoverflow.com/questions/368414/grant-select-on-a-view-not-base-table and http://msdn.microsoft.com/en-us/library/ms188676.aspx and still it is not working.

If I do a GRANT SELECT TABLE TO USER to all tables, the user is able to select the view. But if I revoke to any table, it fails.

This should be a easy procedure to do, but I'm having trouble to make it work. I've seen it happen before (the owner of a instance gave me access to a view and didn't do it to its tables) but I'm unable to do it or find somebody who knows how.

Could somebody provide me a tutorial on how to do it, or a code example?

When the user SELECTs the view I get the message:

  The SELECT permission was denied on the object <TABLE>, database <DB>, schema dbo.

If I grant select to that table, the error message changes the table name to another table the view reads.


texts: How to restore a database using mdf and ldf files in SQL Server 2008?

texts:
Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent the disk to a data recovery place and looks like they can recover some of the .mdf and .ldf files.

We are particularly interested in 1 particular database.

My questions are:

Is this possible to restore a particular  database using corresponding mdf and ldf files?
Do we need to have any mdfs from the  system databases? Any other files we need to ask data recovery company to look for?
Steps and/or or best practices to restore that database into a new machine.

Thanks for your help in advance.


texts: Execute a stored procedure in a SELECT or FROM statement or a SQL function

texts:
Are there any possibilities to execute a stored procedure in a SELECT or FROM statement or a SQL function?

Like

select sp_name_and_parameters
from table_name_or_sp_name_and_parameters

or

select function_name(sp_name_and_parameters)

I have no ideas


texts:
I'm running the below query and wanted to ask about how i could optimise this query. This query has been in use where i work for a while and i have the feeling that it could be optimised as its run regularly with different parameters for reporting purposes

First run takes around 2 minutes. Subsequent runs take around 30s as the query cache has kicked in i believe.

The tags table has over 15million rows and contains tags for users as well as other tables like companies.

CREATE TABLE `tags` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` enum('company','review','job','sector','user') NOT NULL DEFAULT 'company',
`fk_id` int(11) DEFAULT '0',
`tag_key` varchar(24) DEFAULT NULL,
`tag_value` varchar(128) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `tag_key_value` (`tag_key`,`tag_value`) USING BTREE,
KEY `fk_id_key` (`fk_id`,`tag_key`) USING BTREE,
FULLTEXT KEY `tag_value` (`tag_value`)
) ENGINE=InnoDB AUTO_INCREMENT=15725767 DEFAULT CHARSET=utf8

The users table has around 450k rows.

    CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) DEFAULT NULL,
`title` int(1) NOT NULL,
`firstname` varchar(50) NOT NULL,
`surname` varchar(50) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) DEFAULT NULL,
`university` int(11) NOT NULL DEFAULT '0',
`university_start_year` varchar(20) NOT NULL,
`university_end_year` varchar(20) NOT NULL,
`degree_discipline` varchar(3) DEFAULT NULL,
`signup_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`newsletter_subscription` int(1) NOT NULL DEFAULT '1',
...
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `university` (`university`) USING BTREE,
KEY `university_start_year` (`university_start_year`) USING BTREE,
KEY `university_end_year` (`university_end_year`) USING BTREE,
KEY `degree_discipline` (`degree_discipline`) USING BTREE,
..
KEY `email` (`email`),
FULLTEXT KEY `firstname` (`firstname`,`surname`,`comments`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1188075 DEFAULT CHARSET=utf8

I don't have the ability to change the table structure or indexes so any input on the optimising the query would be greatly appreciated. I thought that maybe using sub queries or optimising how the joins work would be the first point to address but i'm a bit lost of how i would tackle this.

SELECT u.firstname, u.email
FROM users AS u
LEFT JOIN tags as tags ON tags.fk_id = u.id AND tags.type = "user" AND tags.tag_key = "option"
LEFT JOIN tags as tags6 ON tags6.fk_id = u.id AND tags6.type = "user" AND tags6.tag_key = "role"
LEFT JOIN tags as tags7 ON tags7.fk_id = u.id AND tags7.type = "user" AND tags7.tag_key = "sector"
WHERE (user_type IN ("1")) AND
(tags.tag_key = "option" AND tags.tag_value IN ("1", "2", "8")) AND
((degree_discipline IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
OR (tags6.tag_key = "role" AND tags6.tag_value IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))
OR (tags7.tag_key = "sector" AND tags7.tag_value IN ("ACC", "ACT", "ACF", "INS", "CBN", "INV", "RBN", "BNK", "ECO", "FIN", "MAT"))) AND
(u.signup_date >= "2012-09-01" ) AND
newsletter_subscription = "1"
GROUP BY u.email

Explain

If there's any other information that i can provide that would be helpful let me know.

Server Environment

These queries are being run on a replication slave for reporting purposes. This replication server has hourly backups run on it via mysqldump which could be affecting the caches but thats only a guess at this point.


texts:
I have a database of specimens for a natural history collection. The scientific names for the specimens have changed since the last name update ~10 years ago. The actual specimen entries are recored in a catalog that references unique IDs in Names to show the identification. I updated the names and IDs (which are formulated from the names) in Microsoft Access and I now have three spreadsheets: Names, OldNames, and Catalog.

[Names] structure
    AllID NewID AllID Name

[OldNames] structure
    ID Name

[Catalog] structure
    ID (could be an AllID or NewID) Specimen#

Now I need to find all the names that changed between AllID and NewID, locate the corresponding specimen by number, and physically change the label. My current code is to run a query with [Names].[AllID] linked to [OldNames].[ID] and [Catalog].[ID].

Field: AllID
Table: Names
Criteria: Not[Names].[NewID]

I also attached [Catalog].[Specimen#] so that I can locate the actual specimen.

The problem is that the query does not retrieve all of the changed fields. I noticed this because a physical specimen with an old ID was not on the list created by the query. Should line of code

Criteria: Not [names].[NewID]

return all IDs that were updated? If not, what other code should I use?

All help is appreciated, including a link to the same question asked by another user. This seems quite simple and basic but I'm unable to find an answer so far.


texts: Not able to select the disk during Oracle 11g Installation

texts: Hi All, During the 11g installation on Win 2012, I am not able to select the available disks which are already stamped using ASM. Any idea what could be the reason? I have tried to select using keyboard space as well but doesn't work out. Appreciate your help. Thanks.


texts: How to fix a bad copy of Postgres dbs to a different machine?

texts:
I had to copy over one set of DBs (around 2TB of data) from one machine to another one with 4TB of file system space. All databases are for internal usage only, no replica, no external writes.

The version of Postgres in both machines is the same (10.6).

What I did was physically "cp" files from one file system to another one, following the paths in postgresql.conf for the variable data_directory :

cp -R /var/lib/postgresql/10/main/ /new_partition_path/postgresql/10

then changed data_directory to /new_partition_path/postgresql/10/main and finally restarted postgres.

based on the default Ubuntu path.

Apparently all looked good, but now everytime I run some script (in parallel with 5/10 psql commands) I get strange errors like:

ERROR:  table "infix_final_pt_2" does not exist
STATEMENT:  drop table infix_final_pt_2

or

ERROR:  syntax error at or near "2" at character 61
STATEMENT:  drop table infix_final_pt_2

Like the table has not been built (even if the script for it run successfully).
The scripts have been used many times and they always worked.

I suspect that I did a bad job just copying over file system files. Can I recover somehow this situation? Maybe Postgres admin commands need to be run? I am not able to figure it out from the documentation.


texts: Informix: is it necessary to update statistics on views?

texts:
In Informix I have a partitioned table for performance reasons.
One contains the latest data and another the historical data. Also, there is a view which contains data from both.

When I update statistics on the partitioned tables, do I also have to update statistics on the view?


texts: Is there an underling difference between TIMESTAMP and ROWVERSION when creating columns with each?

texts:
I'm having problems with SSDT detecting a change to a "TIMESTAMP" column where both the target column and model.xml column definition are of type "TIMESTAMP".  When this happens, the following alter statement is generated which fails trying to alter a TIMESTAMP column:

ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyColumn] TIMESTAMP NOT NULL;

My guess is that target column was created as ROWVERSION rather than TIMESTAMP and this is tripping up SSDT as when I update the model.xml column def to "ROWVERSION" a difference is NOT detected and the ALTER statement is not generated.

I know that these two statements are functionally equivalent:

alter table [MyTable] add [NewColumn] TIMESTAMP not null
alter table [MyTable] add [NewColumn] ROWVERSION not null

and that ROWVERSION is a alias for TIMESTAMP..

However, when enquiring on the column type, both alter statements result in the DATA_TYPE being reported as [timestamp]:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = 'MyTable'

Is there an underlying "hidden" difference between columns created as ROWVERSION vs TIMESTAMP? - one that is being picked up by the Diff engine in SSDT?

Thanks in advance for any help!


texts: Load balancer versus connection pooling -- is there a difference?

texts:
I am working on a project that is expected to serve millions of users shortly after launch. The database is postgres, and for now I am assuming at least two servers will be needed. A sys admin (who is clearly well versed in scalable systems) has suggested putting a load balancer in between the web servers and the database servers.

My question is with regard to the difference of load balancing versus connection pooling. To maintain performance, should I be looking at one or the other, or both?


texts: MySql in master A ceate user after using replicate-do-db filter on Master A, that user is not showing on Master B by this query-------select User from mysql.user; how to completely access both machines .... (linux redhat6)


texts: Restore previous version of database in sql server

texts:
I need your help on restoring previous version of database on SQL Server.

In last week I restored database 'A' in DEV server with it's production copy and didn't realize that this database 'A' in DEV was not backed up and there was some already development work going on which is lost now!

Is there anyway of restoring previous version of 'A' database in DEV server?

Any help or suggestion would be really great!

Thank you!


texts: Insert command not showing schema details

texts:
I need to back up a table from a database schema.The table is located under kdb28_test_h and a schema named kref. I tried with the following code

pg_dump --host=110.11.12.16 --port=5432 --username=db_root --password --column-inserts \n
    --table=kref.abc kdb28_test_h > test_h_popl_back_up.sql

but insert command is generating like

insert into abc(----);

but I need

insert into kref.abc(------)


texts:
Is there a decent way to sync MySQL database source code (ie: functions and stored procedures) to git?

I'd like to be able to develop stored procedures within my IDE and version control them in git so I can easily move them between servers, and identify changes to the code.  It would be great if I could use git-hooks  to auto update my stored procedures on push.  It seems like a reasonable thing to want to do, but I don't see anyone doing things this way.

I'd rather not attempt to store a long SP in a migration, or something like Liquibase.  Ideally, I'd like to store the pure SQL in my git repo.

Disclaimer: I'm a new DBA, so I may be missing something obvious.


texts: How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

texts:
I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this stored procedure, there is a query that selects data from ServerB.DatabaseB.SchemaB.TableB. There is a linked server set up from ServerA to ServerB with the security context set to: "Be made using the login's current security context". When the job executes the stored procedure and the stored procedure tries to select from the remote table the following error is thrown:

Executed as user: domain\user.
Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)
OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)
OLE DB provider "SQLNCLI" for linked server "ServerB" returned message "An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does
not allow remote connections.". [SQLSTATE 01000] (Error 7412).  The step failed.

I have made sure that the remote server allows remote connections and that the [domain\user] login has appropriate access to the remote server and database.

Does anybody have a resolution to this? It started out as a problem with trying to perform SQL authentication across the wire so we created the domain user and now I'm having this timeout issue.

edit

We now have named pipes enabled on the production servers and I am still getting an error.

Executed as user: &lt;domain&gt;\&lt;user&gt;. Named Pipes Provider: Could not open a connection to SQL Server [1450]. [SQLSTATE 42000] (Error 1450)  OLE DB provider
"SQLNCLI" for linked server "&lt;remote server&gt;" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI" for linked server "&lt;remote server&gt;" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". [SQLSTATE 01000] (Error 7412).  The step failed.

any ideas?


texts: Database Design for NoSQL Fantasy Baseball League

texts:
I am trying to create a proper schema to place scraped data into, using a MongoDB database.

Currently it looks like

Team {
    id : id,
    name : String,
    teamLink : String,
    username : String,
    userLink : String,
    league : Number,
    Rank : {
        global : Number,
        division : Number
    },
    Points : {
        global : Number,
        division : Number
    }
    Stats : [{
        desc : String,
        value : String,
        globalRank : Number,
        globalPts : Number,
        divRank : Number,
        divPts : Number
    }]
}

League {
    id : Number,
    name : String,
    link : String,
    division : Number,
    acrive : Boolean
    rankings : [{
        week : Number,
        ranks : [{
            team : String,
            position : Number,
            points : Number
        }]
    }]
}

The problem is that a lot of this makes it hard to quickly rank over something as simple as a single stat (e.g. HR). So I am trying to better understand on how to model the data to say, quickly sort over HRs or easily obtain the rankings. I come from a relational db background so I am having a hard time adjusting without joins.

Thank you


texts: CLUSTER requires more space than expected

texts:
I have a very large table with a GIST index on a Postgis geometry column, 540GB and 300GB in size respectively. There are no other contraints or indecies on the table.

CREATE TABLE nodes (
    id bigint NOT NULL,
    version int NOT NULL,
    user_id int NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    geom geometry(Point,4326)
);
CREATE INDEX idx_nodes_geom ON nodes USING gist (geom);

Need to cluster the table using the geom index.

The docs say

  When an index scan is used, a temporary copy of the table is created
  that contains the table data in the index order. Temporary copies of
  each index on the table are created as well. Therefore, you need free
  space on disk at least equal to the sum of the table size and the
  index sizes.

I have 900GB of free space, so expect it to be enough, but it fails with no space left on device error.

What am I missing here please?


texts: Kill all sessions for ONE user in MS SQL

texts:
So we have a bad external application that connects to the database, executes a stored procedure and gets an answer.

It never releases the database connection.

I need a script that either kills all of the sessions for one user
or
I need a script that kills all of the sessions that have been inactive for x hours for a particular database

Anyone have something like this?


texts: Report Builder does not connect for a remote reporting Server

texts:
Current Environment

I am in a bit of trouble here, I have a Reporting Services installed on one server and SQL Server database engine installed on another server.

The users are connecting to Reports Manager which obviously points to the server where Reporting Services are installed.

User use a generic login (a local admin account on the Report Server) to connect to the reports manager.

But when the users click on the Report Builder button on the Reports Manager, It initially failed to launch complaining `"Application cannot retrieve the files. Authentication failed."

After a lot of research I found out that, even though users entered the credentials of a Local account when connecting to the Reports Manager, but when they clicked on the Report Builder button, the credentials from their windows account were picked up hence the ClickOnce application failed to Authenticate the user and failed to Launch.

I changed this behavior by allowing Basic Authentication on Reporting Server and Allowing Anonymous Authentication on Reports Builder.

Now when the users click on the Report Builder instead of report builder just picking the credentials from the current user windows account it prompts for the credentials , the  user passes the credentials of the Local Admin account on the reporting server and the ClickOnce Application is downloaded as expected.

Problem

Now the report builder is launched fine and I can right click the The datasources folder in the left pane of the designer, browse for the available data sources , test connection (shows tested successfully) . But as soon as I click on the DataSet and try to add a new dataset it throws an error saying "Authentication failed, cannot connect to the datasources."

Even though in the last step the connection test was successful but at this stage it is failing.

Important Note

The SQL Server has no external IP, it can only be seen from the reporting server. Could this be the reason that the connection is failing?

I mean does the report builder try to obtain a direct connection to the SQL Server when launched?

Or is there anything else that I have missed out?

Any pointer any suggestions are much appreciated , Thank you.


texts:
if I run this simple query:

SELECT * from myapp.latests WHERE organization_id = 1 and user_id = 1;

I get error:

NoHostAvailable:

Running nodetool status results into:

Datacenter: eu-central
======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--  Address    Load       Tokens       Owns (effective)  Host ID                               Rack
UN  10.0.0.53  190.96 KiB  256          0.0%              80f8bf0a-c46d-41ce-bb2e-498def5b3792  1a
UN  10.0.20.5  189.35 KiB  256          0.0%              f25fe4bf-29b2-4403-8e3e-4e973a5f26ab  1b
UN  10.0.0.54  183.13 KiB  256          0.0%              c46ac05a-6cb2-48f1-a776-2c57c33e7719  1a

And here is the description:

DESCRIBE myapp;

CREATE KEYSPACE myapp WITH replication = {'class': 'NetworkTopologyStrategy', 'eu-central-1': '2'}  AND durable_writes = true;

CREATE TABLE myapp.locations (
    organization_id int,
    user_id int,
    date text,
    unix_time bigint,
    lat double,
    long double,
    PRIMARY KEY ((organization_id, user_id, date), unix_time)
) WITH CLUSTERING ORDER BY (unix_time ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE myapp.latests (
    organization_id int,
    user_id int,
    lat double,
    long double,
    unix_time bigint,
    PRIMARY KEY (organization_id, user_id)
) WITH CLUSTERING ORDER BY (user_id ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

Any idea?


texts:
I need to move a mysql installation to another computer, can I just copy files?
It is vers 4.1, I installed it and copied database but couldn't access it by the program I need. And if I connect to old comp it works fine.

So I need it to be just like on the old one


texts:
I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed frequently, so I am considering merging the rows from 2014 into a single partition. My assumption is that this will improve the query optimizer and help prevent too many partitions from being created over time. The steps I was planning to do that are:

Create a new partition called xyz_2014
Copy the data from 2014 into xyz_2014
Alter table xyz_2014 to inherit from xyz
Alter old tables xyz_2014_01, xyz_2014_02, etc. to not inherit from xyz
Update my INSERT trigger for xyz to reference xyz_2014
Confirm that everything is working correctly
Delete old tables

Is this approach typically how merging tables in Postgres is done?


texts: MySQL "CREATE TABLE LIKE" include triggers

texts:
I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster)

When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?


texts: What would be the Candidate key (s) of this relation?

texts:
R(ABCDEPG)

F={AB->CD, DE->P, C->E, P->C, B->G}

To my consideration, the candidate keys are: AB, AG, CD, DE.

Am I correct? Why or why not?


texts: What SSMS and/or Visual Studio tools sync SQL Server environments?

texts:
I need to sync table structures, stored procedures, triggers, data, etc. between environments. There are dev, QA, and production environments. I am in a Microsoft-centric environment.

What tools within SSMS and/or Visual Studio can sync SQL Server machines/databases? Thanks.


texts: Is it wrong to consider SCAN DENSITY as a performance metric in higher versions of SQL Server apart from SQL Server 2000?

texts:
DBCC SHOWCONTIG - introduced in SQL Server 2000
sys.dm_db_index_physical_stats - introduced in SQL Server 2005

Question: Is it wrong to consider scan density as a performance metric in higher versions of SQL Server apart from SQL Server 2000? I am looking for an answer
whether SCAN density was or was not something we should be concerned about in higher versions of SQL Server (> SQL Server 2000), as this metric was rolled out in DMVs and functions.
Background: I have a requirement to maintain scan density of more than 90% in on of my SQL Server database (SQL Server 2016) as part of vendor's requirement.
However, I can see in the Microsoft site, DBCC SHOWCONTIG as a deprecated feature which will be removed in future version of SQL Server and I don't see the SCAN DENSITY column in function sys.dm_db_index_physical_stats (which is the replacement of DBCC SHOWCONTIG).
Example:
For one of my table with 12954223 rows, below are the stats for clustered index (page count - 830943) with different queries:
With DBCC SHOWCONTIG:
Scan density - 76.11 %[103868:106468]
Logical scan fragmentation - 4.68%

With sys.dm_db_index_physical_stats
avg_page_space_used_in_precent - 92.84 %
agg_fragmentation_in_precent - 4.68%


texts: How real-time apply works in logical standby oracle?

texts:
I am a little bit confused about real-time apply services.
I've read a documentation about the services and I still have a question.
Does archived redo logs required for logical standby or not?
I've configure logical standby and there are 3 destination for db_recovery_file dest.
1st is local storage, 2nd transmits logs on standby and the 3d is for switchover.
But still I do not purely understand how it works.
Please, clarify or give some useful links.


texts: Swap utilization is high on Oracle server

texts:
How do I reduce swap utilisation on a Linux server which is running as an Oracle 11gR2  Server?

Currently it shows above 80% in Enterprise Manager.

How do I identify which processes use swap memory in Oracle and how do I reduce it?


texts: How to differentiate between "no child rows exist" and "no parent row exists" in one SELECT query?

texts:
Say I have a table C that references rows from tables A and B:

id, a_id, b_id, ...

and a simple query:

SELECT * FROM C WHERE a_id=X AND b_id=Y

I would like to differentiate between the following cases:

No row exists in A where id = X
No row exists in B where id = Y
Both such rows in A and B exist, but no rows in C exist where a_id = X and b_id = Y

The above query will return empty result in all those cases.

In case of one parent table I could do a LEFT JOIN like:

SELECT * FROM A LEFT JOIN C ON a.id = c.a_id WHERE c.a_id = X

and then check if the result is empty (no row in A exists), has one row with NULL c.id (row in A exists, but no rows in C exist) or 1+ rows with non-NULL c.id (row in A exists and at least one row in C exists). A bit messy but it works, but I was wondering if there is a better way of doing this, especially if there is more than one parent table?


texts:
Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all.

(This command executed by me)

systemctl -l status mariadb.service

mariadb.service - MariaDB 10.1.36 database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf

Active: failed (Result: exit-code) since Mon 2020-01-20 10:58:08 IST; 3s ago

Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/

Process: 8176 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)

Process: 8165 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)

Process: 8158 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 8176 (code=exited, status=1/FAILURE)

Status: "MariaDB server is down"

2020-01-20 10:58:03 139801831975168 [ERROR] Plugin 'InnoDB' init function returned error.

2020-01-20 10:58:03 139801831975168 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2020-01-20 10:58:03 139801831975168 [Note] Plugin 'FEEDBACK' is disabled.

2020-01-20 10:58:03 139801831975168 [ERROR] Unknown/unsupported storage engine: InnoDB

2020-01-20 10:58:03 139801831975168 [ERROR] Aborting
Error in my_thread_global_end(): 1 threads didn't exit
mariadb.service: main process exited, code=exited, status=1/FAILURE

Failed to start MariaDB 10.1.36 database server.

Unit mariadb.service entered failed state.

mariadb.service failed.


texts:
I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and disable the mirror, then re-enable it after I'm done poking at the offending server.

The plan I have right now is to:

Disable the mirror
Do my potentially dangerous troubleshooting
Restore all missed transaction logs
Re-establish the mirror

Is this the safest way to go about breaking/re-establishing a mirror? Are there any gotcha's I should look out for?


texts: SQL Server Import/Export: cannot copy geo columns

texts:
I'm trying to copy data from a SQL Server 2014 Express database to a SQL Server 2008R2 Express database.

Compatibility level for the 2008R2 Express database is set to 100.

As soon as I hit Next in the Select Source Tables and Views window, I get the following error:

  [dbo].[Client] -> [dbo].[Client]: The data type could not be assigned
  to the column "Address_Coords" in "SQL Server Native Client 11.0"

The column is a geometry column.

How can I copy data from one database to another when the database contains geo columns?


texts: postgresql : **ERROR: path element at position 1 is not an integer:**

texts:
I am trying to update a field value in jsonb column with text value present in another column but getting some syntax errors; not getting any solution.
i am trying to swap values of OutgoingVertexid & IncomingVertexId in below JSONB
'[
      {
         "Edgeid":10,
         "Weight":100,
         "Active":1,
         "OutgoingVertexid":"",
         "IncomingVertexid":"G",
         "EdgeColor":"Black"
      }
   ]
'

so used below code by putting all values OutgoingVertexid & IncomingVertexid value in temp table.
UPDATE temp_table
        SET
        owner_property = CASE
                            WHEN owner_outgoing_edge IS NOT NULL
                                THEN jsonb_set(owner_property, '{OutgoingVertexid}', '""')
                            ELSE
                                jsonb_set(owner_property, '{OutgoingVertexid}', ''||owner_incoming_edge::jsonb||'')
                                END;

but getting below error:
ERROR: path element at position 1 is not an integer:
Thanks in Advance


texts: Is it possible to assign sql logins into a group?

texts: This may be a bit too elementary question for this site but is it possible to control SQL Server authenticated logins using groups? So is it possible to assign sql logins under groups and give rigths for these groups? If so, are these groups only seen for an instance in question?


texts: SQL Developer: how to script output to query results grid

texts:
I am used to MS SQL Server Studio where I can DECLARE and SET variables and use them in my query, and then be presented with the results in a datagrid that I can copy and paste into Excel.

In Oracle SQL Developer, I can get simple query results returned in the 'Query Results' grid, but if I need to script something more complex (with variables), I need to use the 'Run Script' option and my results are ouput to the 'Script Output' window, where it's much harder to select the results for copying into Excel.

Here is example code, where the variable :v_yr is used three times:

var v_yr number;
exec :v_yr := 2014;

select job.employee_no,
mas.last_name,
mas.first_name,
pay.contract_salary,
pay.contract_days_actual,
CASE job.contract_year WHEN :v_yr THEN cal.contract_days_actual END,
pay.full_year_salary,
pay.full_year_contract_days,
CASE job.contract_year WHEN :v_yr THEN sal.annual_rate END
from hrs.employee_jobs job
LEFT OUTER Join hrs.employee_master mas on job.employee_no = mas.employee_no
LEFT OUTER JOIN hrs.employee_pays pay on job.employee_no = pay.employee_no AND
    job.job_seq_no = pay.job_seq_no
LEFT OUTER JOIN hrs.schedule_amounts sal on pay.schedule_id = sal.schedule_id AND
    pay.schedule_no = sal.schedule_no AND
    pay.schedule_level = sal.schedule_level AND
    pay.schedule_step = sal.schedule_step
LEFT OUTER JOIN hrs.calendar_master cal on pay.calendar = cal.calendar
where job.contract_year in (2013,:v_yr);

In my Googling so far, it seems that in order to display these results in a grid, I will need to set up this query as some kind of stored procedure, assign the results to a cursor.  I don't know enough about Oracle programming to understand this answer yet.


texts: Select different combinations for same table msSQL

texts:
I have a table which has 2 fields OS_ID and OZ_ID. I want to select combinations of OS_ID for different OZ_ID. What would be the query for that?

Input

OS_ID   OZ_ID
----    ----
1       1
2       1
3       2
4       2
5       2
6       2
7       3
8       3
9       3

Output

combination row

1,3,7
1,3,8
1,3,9
1,4,7
1,4,8
1,4,9
1,5,7
1,5,8
1,5,9
1,6,7
1,6,8
1,6,9
2,4,7
2,4,8
...


texts: MySQL replica is lagging without resource saturation

texts:
I am running MySQL 5.6.41 on AWS RDS. I have one multi-AZ master and one multi-AZ read replica. It is a vanilla configuration MySQL configuration, so the binlog is in MIXED format and the replication process on the replica is single threaded.

When I attach a new replica, it is booted with ~1 hour of lag, and it takes 10 hours to catch up. When I turn the binlog format to ROW on the replica, the lag starts increasing indefinitely if sync_binlog=1.

I have tracked this down to the SQL_THREAD in particular on the replica that is struggling to keep up.

I'm trying to work out why replication is so slow, given that no system resources appear to be saturated.

The write IOPS on the master does not exceed 2,500/s during short-lived spikes. The average is more like 470/s. Read IOPS are very low: no more than 100/s. We have a baseline capacity of 2,700/s IOPS because we have a 900GiB General Purpose SSD. See AWS docs on this. I also don't think the disk is the bottleneck because the DiskQueueDepth doesn't exceed 1.

I don't believe the issue is saturating the network because the IO_THREAD on the replica is keeping up with the master.

The CPU and memory usage on the replica seem ok. CPU doesn't go above 8% utilization on an 8 vCPU instance, and it has 8 GiB freeable memory.

Does anyone have any ideas for how I can track this down further?


texts: Postgres won't shut down due to wal archiving

texts:
I commanded Postgres to shut down using the init.d scripts (Linux) over 18h ago.

I can still see the processes running:

-bash-3.2$ ps -fe | grep postg
postgres  2299  3265  0 16:06 pts/5    00:00:00 ps -fe
postgres  2300  3265  0 16:06 pts/5    00:00:00 grep postg
root      3263 10185  0 May23 pts/5    00:00:00 su - postgres
postgres  3265  3263  0 May23 pts/5    00:00:01 -bash
root      5985 13676  0 May20 pts/3    00:00:00 su - postgres
postgres  5987  5985  0 May20 pts/3    00:00:01 -bash
postgres 14266     1  0 May23 ?        00:06:34 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres 14268 14266  0 May23 ?        00:01:51 postgres: logger process
postgres 14270 14266  0 May23 ?        00:01:30 postgres: writer process
postgres 14281 14266  0 May23 ?        00:00:09 postgres: archiver process   last was 000000010000028F000000A3
postgres 14282 14266  0 May23 ?        00:03:07 postgres: stats collector process
postgres 14283 14266  0 May23 ?        00:56:49 postgres: wal sender process postgres 10.40.227.238(12032) streaming 28F/A4000650
postgres 14306 14266  9 May28 ?        04:01:55 postgres: opera_man om 10.40.227.146(44745) SELECT

On the standby server (running normally) I see that:

$ ps -fe | grep postg
cluser   20724  7090  0 09:54 pts/0    00:00:00 psql -U postgres report
postgres 20726 21475  0 09:54 ?        00:01:12 postgres: postgres report [local] idle
postgres 21475     1  0 Apr24 ?        00:00:03 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
postgres 21477 21475  0 Apr24 ?        00:00:01 postgres: logger process
postgres 21478 21475  0 Apr24 ?        05:34:10 postgres: startup process   recovering 000000010000028F000000A4
postgres 21485 21475  0 Apr24 ?        00:07:16 postgres: writer process
postgres 21486 21475  0 Apr24 ?        00:00:18 postgres: stats collector process
postgres 24091 21475  0 May23 ?        00:46:49 postgres: wal receiver process   streaming 28F/A40006E0
cluser   32136 30224  0 16:09 pts/16   00:00:00 grep postg

The log shows 'FATAL:  the database system is shutting down'. What could be the reason of this and how do I get it to run again?


texts: Does canceling update operation perform automatic rollback

texts:
I ran into a bit of confusion. I'm under the belief that database operations are automatic - that is either the query succeeds or the transaction is automatically rolledback. For example - if you have a long running UPDATE statement and you "cancel" it before it finishes - IE clicking red circle X button in SQL Developer (which you should receive a ORA-01013: user requested cancel of current operation error). Upon canceling the query the query is rolled back and no rows were affected. I believe the same should hold true for DELETE statements.

I looked around for some documentation on it and I found this article that really isn't authoritative.


texts:
So I have 'students' with the following attributes:
student_id, name, mentor, district_id. Where district_id is a foreign key. Mentors are also students, so mentor refers to a student_id.
And I have 'districts' with the following attributes:
district_id, country.
I want to get students who are from a specific country, whos mentors are from a specific country.
Here's how I get the students from specific country:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "Japan";

I do the exact same thing but with the intention of using it to filter out by mentors of students.
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "USA";

So now I have two tables one for students from Japan, and one for students from the USA. To get Japanese students with American mentors I would 'combine' these two tables like this:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = "Japan"
AND students.mentor in (
    SELECT student_id,
    FROM districts, students
    Where students.district_id = districts.district_id
    AND district.country = "USA"
);

The idea is that I check if the studentID given by students.mentor is in the table of studentID of people from the US.
However this doesn't work. Only some are filtered off. I checked by adding students.mentor to the SELECT in the first line. And comparing it to the IDs in the second table.
Here is an example of the what the tables would look like with the added check.
Table 1) Students in Japan:
100
101
102

Table 2) Students in USA:
201
202
203

Table 3) Students in Japan with USA mentors (wrong output)
101 201
102 301
103 789

Table 3) Desired output
101 201


texts: How can this unique index allow duplicate rows?

texts:
Is there a way that this unique index has allowed duplicate rows? I thought maybe there are some extra space characters, but I can't find them.

=> select *, length(keyword), length(country), length(language) from keyword where id in (4588076, 4951423);
   id    |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | violation | revenue | length | length | length
---------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+-----------+---------+--------+--------+--------
 4588076 | power wallet review |         | SPYFU  |            0 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
 4951423 | power wallet review |         | SPYFU  |            2 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
(2 rows)

The index is

"keyword_keyword_country_language" UNIQUE, btree (keyword, country, language)

PostgreSQL 9.5.3

OK I was planning to remove the other two columns, but I thought I would test the keyword column and found this:

=> select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column?
---------+---------------------+---------+---------------------+----------
 4588076 | power wallet review | 4951423 | power wallet review | f


texts:
Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of their decleration in the stored proc. I.E if i did exec test @rich output,@bob output the call would blow up. How can i have the order be arbitary? Thank you

create procedure test
  @ID as INT output
 ,@mark as char(20) output
as

  select @ID = 5,@mark='test'
go
declare @bob as int
declare @rich as char(20)
exec test @bob output, @rich output
select @bob,@rich


texts: How to insert default values to a table with not null columns?

texts:
I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required).

I would like to write a query that inserts a row with all zeros.

INSERT [TablesName] DEFAULT VALUES works good for nullable columns.

I can't use INSERT INTO table1 (field1, field2) VALUES (0, 0.0); as I don't know the number of columns.

Is there a query that would insert a new row with default values for not null tables?


texts:
In some scenarios like this one, SQL Server will silently truncate (N)VARCHAR values leading to serious data loss when incorrectly declaring variables.

Question: Can SQL Server be configured to not silently truncate VARCHAR values? (and issue an error/raise an exception instead)


texts: mysql launch fails after upgrade to 5.7.11

texts:
After upgrading a Mac to OS X 10.11.3, mysql was gone, so I downloaded and installed 5.7.11. It installed as usual but fails to launch with;

(com.oracle.oss.mysql.mysqld[1560]): Service exited with abnormal code: 1

in the system log. It respawns and fails repeatedly!

Any suggestions appreciated!


texts: Insufficient Privileges When Connect to SYS and SYSTEM

texts:
I have installed EBS VISION instance on my virtual machine.

After installation from my windows machine I am able to connect to schemas such as APPS, HR, however, unable to connect to SYS and SYSTEM schemas. I am getting insufficient privileges error.

What could be the reason and how able to resolve this?

Update 1

Files screenshot

SQLPLUS screenshot


texts: Differential Backup from SQL Server 2012 to Azure Blob

texts:
I am attempting to backup a database to my Azure Blob storage. The databases are relatively large... between 1 and 30 Gig.

I have successfully backed up the first day, but on subsequent days, I get an error stating that the file already exists and that I need to use the WITH FORMAT.

But I need to do a differential backup, as the load of data on a full backup would be very slow and pricey.

I get the error "MyBackup.bak exists on the remote endpoint, and WITH FORMAT was not specified.".

What I am trying to do is:

BACKUP DATABASE [MyDatabase]'
TO URL = 'https://myaccount.blob.core.windows.net/dbbackups/Container/', MyDatabase.bak',
WITH CREDENTIAL = 'AzureDBBackupsContainer'
, DIFFERENTIAL
, COMPRESSION;

I have seen that a differential backup is supported when using URL backups.

Is this actually not possible, or am I doing something wrong? Do I need to somehow change the file name each day?


texts: Select postgresql within 1 hour range of current date

texts:
GOAL

I have a table which is fed by a collection of an agent ZABBIX, with a   TIMEOUT of 3 seconds.

I would like to do aSELECT within a range of 1 hour in 1 hour always from the current date of the execution of SELECT, the query below does not report any errors, but does not bring any information.

SQL

 SELECT
     *
 FROM
     history
 WHERE
     "clock"> = now () - interval '60 minutes';

DATE

 2019-10-28 08:10:00.424430
 2019-10-28 08:11:03.424523
 2019-10-28 08:12:06.424596
 ...
 2019-10-28 09:10:00.424430
 2019-10-28 09:11:03.424523
 2019-10-28 09:12:06.424596

DESIRED OUTPUT

 2019-10-28 08:00:03.424439
 2019-10-28 09:00:03.424522
 2019-10-28 10:00:03.424591

NOTE: The clock column of thehistory table is TIMESTAMP


texts:
We have a Percona PXC with three nodes. last week, we had a third node, db-node-3, go down with an error:

2017-03-12T10:41:22.070396+08:00 30 [ERROR] Slave SQL: Could not execute Write_rows event on table p13n.mih_card_ranking; Duplicate entry '53648815-3-1' for key 'msno_version_algorithm_id', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 6117, Error_code: 1062

And today the second node db-name-2, also experienced the same error:

2017-10-12T08:48:32.070396+08:00 30 [ERROR] Slave SQL: Could not execute Write_rows event on table p13n.mih_card_ranking; Duplicate entry '83628815-3-1' for key 'msno_version_algorithm_id', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 6117, Error_code: 1062
2017-10-12T08:48:32.070417+08:00 30 [Warning] WSREP: RBR event 3 Write_rows apply warning: 121, 575158440
2017-10-12T08:48:32.086370+08:00 30 [Warning] WSREP: Failed to apply app buffer: seqno: 575158440, status: 1
         at galera/src/trx_handle.cpp:apply():351

Right now, i know that our Developers are performing a lot of data INSERT on the reported table, and suspect that might be corrupting the data somehow and breaking the PXC.

This kind of error to me like something you would expect from a slave. On a slave, Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. And on a slave, you can normally solve this kind of issue with the SQL_SLAVE_SKIP_COUNTER = 1.

But since this is a PXC environment, should this sort of error be happening? And what would be the best possible solution to solve this and avoid it in the future?

mysql> SHOW CREATE TABLE mih_card_ranking\G
*************************** 1. row ***************************
       Table: mih_card_ranking
Create Table: CREATE TABLE `mih_card_ranking` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `version` smallint(5) unsigned NOT NULL,
  `algorithm_id` tinyint(4) unsigned NOT NULL,
  `msno` bigint(20) unsigned NOT NULL,
  `data` json NOT NULL,
  `updated_ts` int(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `msno_version_algorithm_id` (`msno`,`version`,`algorithm_id`),
  KEY `btree__updated_ts` (`updated_ts`)
) ENGINE=InnoDB AUTO_INCREMENT=169056776 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)


texts: Is it possible to index serial8 type column for BTS in Informix

texts: Trying to Index an Informix Database for Basic Text Search. One of the columns is of type Serial8. Is there a way or is it possible to index serial8 column for BTS, without changing the type of the original data table column?


texts:
In the interest of keeping the database as secure as possible, I'd like to lock the SYS and SYSTEM accounts so that no one can login with them.

Assuming that:

There are no OS scripts/cron jobs logging in as SYS or SYSTEM
There aren't any applications or outside utilities using either of these accounts
I can always login "/ as sysdba" with the proper OS account

Will locking these two accounts have any adverse effects? Has anyone done this before who can comment on whether or not it's a good idea?


texts: i cannot install mysql plugin keyring_file.dll

texts:
I have been trying to install the keyring_file.dll of MySQL 5.7.26 plugin from WAMP 3.1.9 without success. It keeps me telling in the CMD Prompt:

  ERROR 1123 (HY000): Can't initialize function 'keyring_file'; Plugin initialization function failed.

...and the MySQL log states:

  [ERROR] Plugin keyring_file reported: 'File 'C:/Program Files/MySQL/MySQL Server 5.7/keyring\keyring' not found (Errcode: 2 - No such file or directory)'

Could somebody help me figure out what the root cause is?


texts: How to select data in the same table on different rows that are empty?

texts:
I use this select on my SQLite database:

SELECT attributeX
 FROM table
 GROUP BY attributeX
 HAVING COUNT(attributeY) = 0;

My table:

  attributeX (type integer) |  attributeY (type text)
----------------+-------
  1452 | aaa
  1452 | bbb
  1452 |
  1452 | bbb
  1453 |
  1453 |
  1453 |
  1454 | eee
  1454 | fff
  1455 | iii
      ....

I get result 1453 only when all items in column attributeY are empty for 1453. So in SQLite I get result 1453.

I have the same database only with myschema in PostgreSQL. I use this SELECT:

SELECT attributeX
 FROM myschema.table
 GROUP BY attributeX
 HAVING COUNT(attributeY) = 0;

and I get:

 attributeX
------------
(0 rows)

How to select in PostgreSQL?

SQLite schema of this table:

CREATE TABLE table (
 attribute1 INTEGER PRIMARY KEY,
 attributeX INTEGER NOT NULL,
 attribute2 TEXT NOT NULL,
 attributeY TEXT
);

CREATE INDEX table_attributeY_idx ON table (attributeY);
CREATE INDEX table_attribute2_idx ON table (attribute2);
CREATE INDEX table_attributeX_idx ON table (attributeX);

PostgreSQL schema of this table:

Table "myschema.table"
        Column  |          Type         |                                    Modifier                             | Storage  | Desc
----------------+-----------------------+-------------------------------------------------------------------------+----------+-------
 attribute1     | integer               | not null implicitly nextval('myschema.table_attribute1_seq'::regclass)  | plain    |
 attributeX     | integer               | not null                                                                | plain    |
 attribute2     | character varying(32) | not null                                                                | extended |
 attributeY     | text                  |                                                                         | extended |
Indexy:
    "table_attribute1_pkey" PRIMARY KEY, btree (attribute1)
    "fki_table_attributeX_fkey" btree (attributeX)
Conditions for foreign key:
    "table_attributeX_fkey" FOREIGN KEY (attributeX) REFERENCES myschema.table2(attributeX2) ON UPDATE RESTRICT ON DELETE CASCADE
Has OID: no

Insert:

INSERT INTO myschema.table (attributeX, attribute2, attributeY)
VALUES (%s, %s,%s)... (python script)


texts: How can I get the hierarchical values from the below query?

texts:
I have a table named Category which has a column named CategoryID. There is a referencing column in the same table called fParentCategoryID.

I need to get all the category IDs and their subcategory IDs comma separated. For Example - if parent category ID of 10 is 1 and if parent category Id of 20 is 10 then when I print category ID 20 I need to print both 1 and 10 as its parents in comma separated values.

I tried the below query but I get NULL for ParChild column. Please help.

;WITH
  cteReports
  AS
(
SELECT c.CategoryID,
       c.fParentCategoryID,
       [level] = 1,
       ParChild=cast(CAST(c.fParentCategoryID AS VARCHAR(200)) + ',' + CAST(c.CategoryID AS VARCHAR(200)) AS VARCHAR(MAX))
FROM   retail.Category c
WHERE c.fParentCategoryID is NULL
UNION ALL
SELECT c.CategoryID,
       c.fParentCategoryID,
       [level] + 1,
       ParChild = ParChild + ',' + CAST(c.CategoryID AS VARCHAR(200))
FROM   retail.Category c
        JOIN cteReports r
            ON  c.fParentCategoryID = r.CategoryID

)

SELECT *
FROM   cteReports cr

use this script to create and populate the table.
(note: there is a 30K limit to the question body..So I had to use pastebin to copy your code and reference it)


texts: How does mysql optimiser chooses a composite Index

texts:
I have a table with the following structure.
 -id (bigint|pk)
 -funnel_id (bigint|fk with funnels table)
 -affiliate_id (bigint|fk with affiliates table)
 -created_timestamp (bigint)

Observations

funnel_id has 10,000 unique records
affiliate_id has 15,000 unique records

I have various combinations of SELECT query on the table like:
1) SELECT * FROM tbl WHERE affiliate_id = 300;
2) SELECT * FROM tbl WHERE funnel_id = 300;
3) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300;
4) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300 AND created_timestamp BETWEEN 600 AND 700;

So as per the rule, I should create a composite index with the below statement. Also, it gives me a (100 score) when I checked with the EXPLAIN keyword on the query.
CREATE INDEX ON tlb index_name(affiliate_id, funnel_id, created_timestamp)

But as soon as I create a foreign_key on funnel_id and on affiliate_id the optimizer starts picking up the foreign_key index and the (100 score) also reduces (to 10).
But then I created a composite index on (affiliate_id, funnel_id). Guess what happend next. The query optimizer again stated to pick the (affiliate_id, funnel_id, created_timestamp) index.
So my question here is, why the optimizer didn't pick the (affiliate_id, funnel_id) index. Guys, please let me know your views on this as it is getting a bit confusing.


texts: is it possible to do a reverse pattern search in a database?

texts:
I have 2 database tables with rows containing strings. If I combine two strings, one from each table, as input I need to be able to find where the parts of the strings are located in each table.

For example:

table 1 has "AB" at row 3
table 2 has "ED" at row 4

If I give the input as "ABED" I need the two rows to be returned.

Can this be done using an SQL command?

Edit: We do not know the sub-strings. We only know the string ABED.So we don't know if the first table has "ABE" in it or "AB" in it and which part of the string is in the second table.


texts:
I have 2 packages and 2 public synonyms for those packages. There are dependencies between those packages. If I make changes to any of those packages and compile them then packages are compiled (specs and bodies) and valid but both synonyms are invalid. I know I can recompile synonyms but how to avoid this please? Is there a way to keep all objects compiled?
Thank you.


texts: Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

texts:
My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in average. (That's what AWR tells me, anyway.)

SELECT
    MAX(fldA)
FROM
    humongoustable
WHERE
    fldB = :B2 AND fldC = :B1

There's only one index involving the above fields, and it's on (fldC, fldA, fldB).

I suspect that isn't helping any, and there should be two separate nonunique indices; one for fldA and another for (fldB, fldC). Am I correct?


texts:
I have two tables table1, table2 where table2 is a subset of table1 (basically created with the query create table table2 as select * from table1 where .....)

Now, I am trying to run the below query. But its taking almost 30 minutes to just delete 10 records.

delete from table1
where column1 in
    (select column2 from table2)
order by column1
limit 10

If I remove the order by clause in the above query it's showing performance improvement but I get the warning;

  Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

Please suggest a way improve the performance of the delete query.


texts:
I'm currently trying to log into a remote AS/400 server. I was given credentials to obtain some data and do a couple tests. After 2 failed attempts, the iAccess Client showed me an alert saying my user would be blocked if the third attempt failed, so I stopped trying.

I'm reporting the event, but I need to know if the "attempt counter" resets after some time.


texts: Does anyone know to how to install Oracle 11g on Ubuntu 11.10?


texts:
Let me state the obvious fist: I completely understand that floating point types cannot accurately represent decimal values. This is not about that! Nevertheless, floating point calculations are supposed to be deterministic.

Now that this is out of the way, let me show you the curious case I observed today. I have a list of floating-point values, and I want to sum them up:

CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT STR(SUM(#someFloats.val), 30, 15) FROM #someFloats;

DROP TABLE #someFloats;

-- yields:
--   13.600000000000001

So far, so good - no surprises here. We all know that 1.2 can't be represented exactly in binary representation, so the "imprecise" result is expected.

Now the following strange thing happens when I left-join another table:

CREATE TABLE #A (a int);
INSERT INTO #A (a) VALUES (1), (2);

CREATE TABLE #someFloats (val float);
INSERT INTO #someFloats (val) VALUES (1), (1), (1.2), (1.2), (1.2), (3), (5);

SELECT #A.a, STR(SUM(#someFloats.val), 30, 15)
  FROM #someFloats LEFT JOIN #A ON 1 = 1
 GROUP BY #A.a;

DROP TABLE #someFloats;
DROP TABLE #A;

-- yields
--   1   13.600000000000001
--   2   13.599999999999998

(sql fiddle, you can also see the execution plan there)

I have the same sum over the same values, but a different floating-point error. If I add more rows to table #A, we can see that the value alternates between those two values. I was only able to reproduce this issue with a LEFT JOIN; INNER JOIN works as expected here.

This is inconvenient, because it means that a DISTINCT, GROUP BY or PIVOT sees them as different values (which is actually how we discovered this issue).

The obvious solution is to round the value, but I'm curious: Is there a logical explanation for this behavior?


texts: Is there an easy way to delete all records in multiple tables (but not every table)?

texts:
So I "fixed" an issue the other day, which has caused a snowball of new issues (imagine that). I have a large group of tables that serve as Temp tables for many import/export processes that flow to other systems. Each table is labeled "TO_xxxx". I want to clear all records from each "TO_" table. Is there a way to structure a delete statement to clear all records from only "TO_" tables?

Is it as easy as

Delete from TO_%

or is there more to it? There's about 80 "TO_" tables total, so I figured I'd post here before manually going through everything. If you can help, I would appreciate it. Thanks.

System is SQL Server 2016.


texts: Do NDF files move with Alter Database automatically

texts:
I know you don't want to hear this but i am not a DBA, but I am responsible for a 2016 SQL server.  From time to time I need to do some DBA work.

This time I need to move the tempdb (mdf, ndf, and ldf files) to another volume.

When i run the alter database query do i need to specify the ndf files in the query?  Or do those move automatically because they are part of the file group?

Being each of the ndfs have logical names, I am thinking I need to, but just want to be certain.

I know I need to specify the LDF files.

These are all the files we have associated with the TEMPDB:

  tempdev   C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf

  templog   C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\templog.ldf

  temp2 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf

  temp3 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf

  temp4 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf

  temp5 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_5.ndf

  temp6 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_6.ndf

  temp7 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_7.ndf

  temp8 C:\Program Files\Microsoft SQL
  Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_8.ndf

Thanks for the help!

-Evan


texts: error while converting sql to eloquent query

texts:
I have converted an sql query to eloquent like below:

$roomtypes = DB::select(`rt.id`,`rt.type`,`rt.total_no_of_rooms`)
    ->addSelect(DB::raw(`SUM(r.no_of_rooms) as reserved rooms`))
    ->selectSub(`rt.total_no_of_rooms`, `rooms available`)
    ->selectSub(`-`, `rooms available`)
    ->selectSub(`SUM`, `rooms available`)
    ->from(`Roomtypes as rt`)
    ->join(`Reservations as r`, function($join) {
        $join->on(`r.roomtype_id`, `=`, `rt.id`)
            ->on(`$check_in`, `>=`, `r.check_in`)
            ->on(`$check_out`, `<=`, `r.check_out`);
        })
    ->groupBy(`rt.id`)
    ->groupBy(`rt.type`)
    ->groupBy(`rt.total_no_of_rooms`)
    ->get();

but I am getting an error

  "Argument 1 passed to Illuminate\Database\Connection::prepareBindings() must be of the type array, null given, called in "

Please, could anyone help ?


texts: ASM diskgroup drop from another ASM instance

texts:
I have created long time back one ASM RAC setup and I created one diskgroup with some name say xyz. I think that instance is gone but the diskgroup was not dropped exclusively using command:

drop diskgroup xyz including contents;

Now that I have created a new setup and want to re utilize these disks, I am getting below error:

CREATE DISKGROUP XYZ EXTERNAL REDUNDANCY DISK ...

ORA-15003: diskgroup "xyz" already mounted in another lock name space

How can this diskgroup be removed? Any help is appreciated.


texts: What could cause a slow DB connection between AWS and Azure?

texts:
Currently we are running with a piece of middleware and our SQL server in AWS. We are looking to move to Azure SQL managed instance. But I am experiencing severely increased insert times when using ODBC from our AWS middleware to the Azure Managed Instance.
During testing when the middleware and SQL server an both on AWS or both on Azure it takes about 5 seconds to insert. Where as if one is on Azure and the other on AWS it is taking at least 2 minutes and 30 seconds. And I have tried with the SQL on Azure and middleware in AWS and the SQL in AWS and the middleware on Azure both taking over 2 minutes.
I have tried using the Azure public endpoint and still get the insert time of over 2 minutes. But if I make the call from SSMS it takes only a couple seconds. This is true if I make the call from my local machine or from AWS or Azure, to either AWS or Azure. So I am guessing that the issue has to do with ODBC connection traversing our Azure instance. Unfortunately I do not have access to the network but if there are any suggestions I can reach out to our network team.


texts: Access denied when disabling agent job, despite SqlAgentOperator membership

texts:
I am attempting to disable a SQL agent job from an account which a member of the SqlAgentOperator role (but not sysadmin)

The doco says this should be possible

  3 SQLAgentOperatorRole members can enable or disable local jobs they
  do not own by using the stored procedure sp_update_job and specifying
  values for the @enabled and the @job_id (or @job_name) parameters. If
  a member of this role specifies any other parameters for this stored
  procedure, execution of the procedure will fail.

...and indeed the code for msdb.dbo.sp_update_job appears to support this - it has explicit handling for exactly this scenario. Yet if I run:

use [MSDB]
go
select suser_sname()
if(is_member('SqlAgentOperatorRole')<>1)
    raiserror('Not in required role',16,1)
go
exec dbo.sp_update_job @job_name='a job', @enabled=0

... I get the following error:

  Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1 The
  EXECUTE permission was denied on the object 'sp_update_job', database
  'msdb', schema 'dbo'.

The error message appears to indicate the proc's not even being run, but even granting that user explicit EXECUTE on that stored proc doesn't seem to fix it.

Does anyone know how to grant a user the ability to disable/enable SQL agent jobs that they don't own, or has successfully used this functionality. Maybe it's just broken in SQL 2012 SP1 CU4 (which is what I am using)


texts:
Our database has been giving this error occassionally...
"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlInternalConnection.OnError"

I have run perfmon with below results. How can I check what is causing such high disk I/O?
The Avg. Disk Queue Length counter (blue) is very high.

Thanks!


texts: SSIS Derived column giving error on CAST function

texts:
I am trying to use (DT_DATE) DATEADD(dd, -1, GETDATE()) in a derived column. However, by the time I can start writing my expression with (DT_DATE) it turns red and shows an error. Whether it's just (DT_DATE) sitting there or the full expression it throws an error.

Here is a screen shot of the error

Also:

If I remove this one derived column expression the rest of my package works fine, including a different derived column
All I'm doing is taking an Excel file, adding two derived columns to it, then putting into an SQL database

Basically it seems to not want to accept (DT_DATE). I'm sure I'm making some small error here but I've tried everything I can think of and any help would be greatly appreciated.


texts: How to structure Master/Detail tables for performance without denormalizing?

texts:
Some Background Info

We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails.  There are roughly 80k Transactions a day which translates to 80k rows in TransactionHeaders and 900k rows in TransactionDetails being burned daily.  At current, the TransactionHeaders has about 10 million rows and TransactionDetails has about 110 million rows.  We use the data for general reporting.

dbo.TransactionHeaders (
TransactionHeaderId            int                            identity,
TransactionHeaderTypeId        int                            not null,
StoreId                        int                            not null,
TransactionDate                date                           not null,
TransactionTime                time                           not null,
...)

dbo.TransactionDetails (
TransactionDetailId            int                            identity,
TransactionHeaderId            int                            not null,
ItemUPC                        NCHAR(14)                      not null,
Price                          NUMERIC(16,2)                  not null,
ReplicationDate                datetime                       not null
...)

The Issue

Querying has become cumbersome.  It takes a very long time to access the sales of a given store or a given item for any period.

What I've Tried

I have tried to bring TransactionDate down to the TransactionDetails table in order to partition it on the date with one partition per day.  This worked great for finding the sales of an item.  The problem is that many of the reports require the StoreID in addition to being over a specific date range.

Given that adding more information from the TransactionHeader table to the TransactionDetail table breaks the pattern, I'm hesitant to denormalize the tables into one table due to storage concerns.

I've had the idea to partition TransactionHeader on TransactionDate and partition TransactionDetail on TransactionHeaderID.  In theory this makes the queried data significantly smaller and reinforces the pattern by making the details only reasonably accessed via the header information.

The Question(TL;DR)

Is there a preferred, correct, standard, etc. method for dealing with tables in the Master/Detail pattern in order to increase performance?  Partitioning one or both tables? I'd like to avoid denormalizing if at all possible.


texts: MS SQL Server - Working Mechanism of Top clause

texts:
Intro
This question is basically asked to understand how the TOP clause works in SQL.
Example
There is a table Table1 in which there are 10 Million records.
If I fire the following query SELECT Top 10 Id from Table1 where Id>0 order by Id it returns the first Top 10 Id in the table.
Question
How the data is actually retrieved?
Assumption 1: Does it get all the 10 Million records from the database and searches the Top 10 records from it? [More CPU, RAM intensive]
OR
Assumption 2: Does it directly searches for Top 10 records based on the where clause on the table and returns only those 10 records? [Less CPU, RAM intensive]


texts: How to set a auto-increment value in SQLAlchemy?

texts:
This is my code :

    class csvimportt(Base):
     __tablename__ = 'csvimportt'

      #id = Column(INTEGER, primary_key=True, autoincrement=True)
      aid = Column(INTEGER(unsigned=True, zerofill=True),
          Sequence('article_aid_seq', start=1001, increment=1),
          primary_key=True)

I want to set a autoincrement value from 1000. How do I do it?


texts: Help with good RDBMS transactional schema design that mimicks a sports league

texts:
I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league.  Architecturally my requirements are:

Well normalized
Transactional
Application developers can model it reasonably well with popular ORM tools

Attributes of the desired schema:

There can be many Leagues.
There may exist 1 or more Divisions in a single League.
There may exist 1 or more Conferences in a single Division.
There may exist 1 or more Teams in a single Conference.
There may exist 1 or more Teams in a Division if and only if the Division has not been partitioned into Conferences (In other words, a Division does not need to be split into Conferences and instead a Team may have a reference directly to a single Division instead of a Conference)
There may exist 1 or more Players in a single Team.
Players exist in 1 and only 1 Team.

This would be simple enough to do and maintain referential integrity if the League structure was to be rigid.  Here is what I was thinking, but I wondered if this would be a good design or if there is a better approach for this situation.

League Table: ...
LeagueGroup Table: FK - LeagueId NOT NULL, FK - LeagueGroupParentId NULLABLE, LeagueGroupType
Team: FK - LeagueGroupId NOT NULL
Player: FK - TeamId NOT NULL

So basically I was thinking that Divisions and Conferences would be the same table with a self-referential foreign key looking upwards for its grouping parent.  The advantage of this is that league grouping can actually go N deep.

Disadvantages are that this might not be very easy or useful to map in an ORM framework.  Further the application developers might need to write a bit of logic to build this into tree like object structure that is useful for their purposes.  Further still, without a trigger of some kind, I wouldn't know how to enforce that teams can't exist at both the Division and the Conference level at the same time from the Database level.

What are your thoughts on this approach, and do you see any viable alternatives to this?


texts: Can't refresh Materialized View Concurrently

texts:
I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error:
SQL Error [XX000]: ERROR: unexpected attlen: -2
  Where: SQL statement "SELECT newdata FROM pg_temp_25.pg_temp_2489701 newdata WHERE newdata IS NOT NULL AND EXISTS (SELECT 1 FROM pg_temp_25.pg_temp_2489701 newdata2 WHERE newdata2 IS NOT NULL AND newdata2 OPERATOR(pg_catalog.*=) newdata AND newdata2.ctid OPERATOR(pg_catalog.<>) newdata.ctid)"

I've made sure a unique index exists, and I've tried running the refresh without concurrency first, which works fine.
I'm running PostgreSQL 9.6 under CloudSQL from Google Cloud.
Can anyone provide any insight?


texts:
Say I have the following tnsnames.ora in my two node RAC database,

DIVTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DIVTEST-scan.DIVCORP.com)(PORT = 1721))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DIVTEST)
)
)

And I create a service as following:

Service_name – DIVTEST
Failover_type - Basic
Failover_method - session
Failover_retries – 180
Failover_delay - 5

and the service in running in both the nodes.

For server side TAF, Should I edit my tnsnames.ora (above mentioned) on my database server as given below?

DIVTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DIVTEST-scan.DIVCORP.com)(PORT = 1721))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DIVTEST)
(FAILOVER_MODE=
(TYPE=session)
(METHOD=basic))))

OR just have the service running in both the nodes will ensure runtime TAF?

Should the service-name mentioned in TNS and the service which I create for TAF using srvctl be the same so that client need not make any changes in tns at their end?


texts: How to Resolve a Case of "The tablix 'xyz' contains an invalid DataSetName in SSRS?

texts:
I am getting the following stubborn error in an SSRS 2012 report:

Deploying report 'abc'.
Warning : The tablix 'xyz' contains an invalid DataSetName (Expected: ‘DataSet1’; Actual: ‘DataSet2’). The DataSetName of the inner data region or group must be the same as the DataSetName of the outer scope, unless a relationship is specified on the inner scope or on the dataset.

When looking through the visual studio GUI, the dataset name is correct, so the GUI shows nothing that would merit this warning. What is the best way to resolve this?


texts:
Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ million records across 400+ or so tables.

The problem comes with software like PHPMyAdmin runs "SHOW TABLE STATUS FROM dbase;" where it can take up to 50 seconds to execute that command. Is there a way to optimise this? (MySQL or MariaDB)

Thanks!


texts:
The below error starts happening once a week at the same time every week.

The OLE DB provider "SQLNCLI" for linked server X reported a change in schema version between compile time X and run time X for table [tablename].
[SqlException (0x80131904): The OLE DB provider "SQLNCLI" for linked server "ABCDEF" reported a change in schema version between compile time ("175385004624084") and run time ("175393583420554") for table ""ABCDEF"."dbo"."TheViewOnLinkedServer"".]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteReader() +89
   XXXX.XXXX.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) in E:\WorkingFolderVSSNET\Lesa\Databases\Lesa.Net.Data\Lesa.Net.Data\SQLHelper.cs:671
   XXXX.XXXX.Data.SqlHelper.ExecuteReader(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in E:\WorkingFolderVSSNET\Lesa\Databases\Lesa.Net.Data\Lesa.Net.Data\SQLHelper.cs:803
   XXXX.XXXX.XXXXX.XXXX.GetAttTypes(String ID, String App) +279

[ApplicationException: Error in GetAttTypes]
   XXXX.XXXX.XXXX.XXXX.XXXXXXX(String ID, String App) +507
   XXXX.XXXX.XXXX.XXXXX.XXXXX.Load(Int32 XXXX) +14717

[Exception: Error in XXXXXX.Load( XXXXX=XXXXXX) at XXXXX- for.]
   XXXX.XXX.XXX.XXXXXX.XXXXXX..ctor(String XXXXX, String XXXXX) +312
   XXXXXXX.XXXXXXXX.Page_Load(Object sender, EventArgs e) +2481
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +50
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627

To fix the problem I just run the alter statement for the view without any changes.

The view selects from another view on a linked server and does list the column names (doesn't select by *).

I am not able to duplicate the problem after it has been fixed yet because I can't figure out what could be causing it. I have tried rebuilding indexes to see if that is causing it but it doesn't seem to.

Any ideas on how to troubleshoot this or what the cause could be?


texts:
When trying to perform certain user security actions, I get the following error:

ORA-46953: The password file is not in the 12.2 format.

How do I migrate a 12.1 password file to 12.2? Or, is this even necessary?


texts: Are logs in the LDF file being removed after a checkpoint?

texts:
Using the fn_dblog I can get the active portion of the online transaction log of my database.

But after every checkpoint I can only see 2 entries showing the beginning of the checkpoint LOP_BEGIN_CKPT and the end of the checkpoint LOP_END_CKPT.

I'm wondering whether if the previous logs removed after every checkpoint or they are only ignored because the dbi_checkptLSN value that located in the bootpage indicate to the latest LSN?

If it is result of the dbi_checkptLSN am I able to move that indicator, and if yes how?

If not, and if the logs is not removed how can I access the previous logs?


texts:
We have a LANDEsk migration going on where several servers are being consolidated into one SQL 2014 Cluster. What would be the best way to calculate IOPS for such a scenario? The data is still being migrated.

I am also following Brent's url here which is very good in cleaning up perfmon data.


texts: How Do I Grant Permissions to Replace a Function?

texts:
I am getting the following error message (when trying to enable NOSQL in DB2 10.5, although I don't think that's really the issue):

  Error code: -551 DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501,
  SQLERRMC=DB2ADMIN;REPLACE FUNCTION; SYSTOOLS.JSON_VAL2, DRIVER=4.17.29

  An error occurred during database object creation, rolling back

The DB2ADMIN user already has the following permissions:

grant DBADM, SECADM, ACCESSCTRL, DATAACCESS ON DATABASE TO USER db2admin

I cannot find the GRANT command that I need to use to enable DB2ADMIN to replace the function. Sorry, I'm not a DBA, so I just need the exact command I'd need to enter.


texts: Is there a more efficient way to apply a new schema to an existing collection when there's existing data?

texts:
I'm needing to add a new schema to the schema collection on an existing table. Right now, I simply unbind the old schema collection from the table column, drop it, create the new schema, and rebind it to the table column.

The problem is, I need to preserve all of the data already in the table. What looks like is happening, is that when I apply the new schema, it goes through all of the data and revalidates everything which takes a really long time. Is there a better way to do this?

What I've tried so far is to rename the existing the table (e.g. Mytable_bak), then recreate the same table but with the new schema collection attached, select all of the data out of the _bak table into the newly created table, and then drop the _bak table. This is how I've done in the past and it seems to be a bit faster (not sure why). The problem with this is that it's really just a big pain. The table is actually published for replication which causes it to throw errors. I guess maybe if there was a way to temporarily disable replication for the table, make the migration, then re-replication, then that might work. But this is kind of out of my current knowledge.

So I'm wondering if there's a better way to achieve all of this, or maybe just a solution to my current approach?


texts:
I am using Talend to backup MS SQL to MySQL Server. Everything goes well, I have a connection, data flows from one db to the other... however I cannot correctly copy diacritic signs.

On MS SQL I have Polish_CI_AS and on MySQL I have Latin2 general_ci.

When I tried to copy data from MS SQL to an Excel file, everything is in place.
I concluded that problem lies in MySQL.

Have you encountered this issue before?


texts:
I am a fresh admin in SQL DB and I need to learn how to establish the security settings in SQL DB. I have encountered an issue that is somehow unclear or even weird for me.
I know that if one user has Fixed Database-Level Roles db_datareader, he or she can perform "SELECT" on all user tables in DB.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
But in my SQL DB, even the user which has already been added to that role can not perform "SELECT" on any table/view unless the "SELECT" permission is explicitly granted in "Schemas settings" which implies db_datareader role does not work at all.
Could anyone please give me advice on this? Many thanks


texts:
My table, t, has the following format:

I would like to transform this table into:

The COUNT_XXXXX columns are determined by counting the number of rows where CLICKSKEY is not NULL organised by SUBJECT_TYPE.  The COUNT_CLICKED column counts the number of rows where CLICKSKEY is not NULL independent of SUBJECT_TYPE.

Could anybody help?


texts: SQL Server not using all CPU cores/threads

texts:
After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it:

.

The server has the following hardware and software:

Windows 2008 R2 Enterprise 64bit SP1
Intel Xeon E7-4870 - 4 processors (40 cores, 80 threads)
Microsoft SQL Server 2012 Enterprise Edition (64-bit)

Running select cpu_count from sys.dm_os_sys_info returns 40.

The OS sees all 80 threads.

Why is only half the server's processing power being used?

We have the same hardware and software on two servers and they both exhibit the same behavior.


texts: Can low memory cause Table Spool operators in plan

texts:
I was trying to optimize a query and found that the plan had table spool(eager) in it.  But when I ran it on another server which had much more memory(256GB compared to 64GB) than the other one the operator was gone and the query was much faster.

So I am wondering if that memory is even the cause of the plan change which I see ie is it even a plausible theory?

volume of Data is the same.


texts: Scheduling a task that happens very frequently in Sql Server

texts:
I've just added some caching to my Sql server to speed up a particularly important SELECT query. This cache will be refreshed every ~10 minutes. The time between activations does not have to be predictable, so long as there is not a long delay between activations.

Is using the Sql server agent the right solution here? This seems to be more aimed at large database maintenance tasks. Should I use a different SQL Server method, or perhaps trigger the update from my client application?

Also, my dev machine does not have the server agent available, so using a different method is attractive for that reason.


texts: Moving Databases between Instances of different Versions of SQL Server

texts:
I found out SQL Server Developer Edition is now free.

I would like to use my older databases from SQL Server Express 2016 to a new instance of SQL Server Developer 2017.

I want all the data objects: indexes, triggers, etc. to be moved together with the data files.

I am trying to figure out the ways in which I can do this. I am pretty sure these would work between instances of the same version, but I am not sure if it would work if moving between different versions of SQL Server:

Backup Old and Restore to New
Script Old and run the script on New
Detach Old and reattach it on New

Are these 3 workable? Are there any other ways?

The instances are on the same machine, and I have not yet installed SQL Server developer edition.

Migration does not need to be immediate, but at most within a week or so.