texts: Create multiple files for each file group for table partitioning?

texts:
I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as

CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO (
    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4],
    ......

Which of following option is better?

Create one file for each file group [PRIMARY], [fg2], [fg3], [fg4]. And put each file in different drive.
Create four files for each file group. And save each files in different drive.


texts: Calculating interval in days based on average velocity

texts:
I have an orders table and need to calculate when there is a change in the average count across time. This could be either an increase or decrease.

For instance, if there is usually 1 order every two days, I need to detect if that suddenly increased to 3 orders every day or decreased to 1 order every 3 days.

Example table structure and data:

CREATE TABLE "public"."orders" (
    "id" SERIAL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');

In the data set above, supposed I wanted to detect that over the course of the last week there is usually only 1 order every 2 days, but over the last 24 hours that pattern has been violated with 3 orders in one day.

Desired result:

Previous 7 day's average orders per day (not including last 24 hours): 0.43
Previous 7 day's average order velocity (not including last 24 hours): 1 order every 2 days
Last 24 hour's orders: 3

Alternate scenario:

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');

Alternatively, suppose I have the above data set. Normally there are approximately 3 orders every day for the past week, but over the last 24 hours there was only 1 order.

Desired result:

Previous 7 day's average orders (not including last 24 hours): 2.57
Previous 7 day's average order velocity (not including last 24 hours): 3 orders every 1 days
Last 24 hour's orders: 1

How would I do this?

PostgreSQL 9.6


texts: how to reduce time of a query which have 4 joins

texts:
I am new to CakePHP and I am working on CakePHP 3.8 version. There is a join query which takes almost 10 -12 seconds to fetch data. I don't know how to optimize this query. this query is written in CakePHP 3.8 format.

SELECT Coach.email                AS `Coach__email`,
       Coach.name                 AS `Coach__name`,
       Coach.account_id           AS `Coach__account_id`,
       Coach.id                   AS `Coach__id`,
       Coach.last_login           AS `Coach__last_login`,
       Coach.subscription         AS `Coach__subscription`,
       Coach.linked_agency        AS `Coach__linked_agency`,
       A.name                     AS `A__name`,
       Find_in_set(2, U.activate) AS `active`,
       ( Count(C.id) )            AS `count`,
       ( Count(CA.id) )           AS `countactive`
FROM   jsb_coach Coach
       INNER JOIN jsb_accounts U
               ON U.id = Coach.account_id
       LEFT JOIN jsb_client C
              ON C.coach_id = Coach.account_id
       LEFT JOIN jsb_client CA
              ON CA.id = C.id
                 AND CA.status LIKE 1
       LEFT JOIN jsb_agency A
              ON Coach.agency_id = A.account_id
GROUP  BY Coach.id
ORDER  BY Coach.last_login DESC


texts: Creating a table if it doesn't exist (old versions of PostgreSQL)

texts:
In recent versions of postgreSQL one can use the syntax:

create temp table if not exist my_table as
...

to create a temporary table if it doesn't exist yet.

However, the clause if not exist is a relatively recent feature.

I am working against a database that uses PostgreSQL version 8.2.15. How can I create a temporary table if it doesn't exist yet?


texts: Include LOCK_ESCALATION in CREATE Script in SQL Server 2012

texts:
Consider following T-SQL script:

USE [DB1];
GO

CREATE TABLE [T1] (
[C1] INT NOT NULL,
[C2] TEXT NOT NULL
);
GO

ALTER TABLE [T1] SET (LOCK_ESCALATION = AUTO);
GO

When I explicitly set LOCK_ESCALATION to DISABLE or AUTO (like in the script above), this information is shown in the script generated using "Script Table as -> CREATE to"

USE [DB1]
GO

/****** Object:  Table [dbo].[T1]    Script Date: 3/26/2015 9:00:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T1](
    [C1] [int] NOT NULL,
    [C2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[T1] SET (LOCK_ESCALATION = AUTO)
GO

ALTER AUTHORIZATION ON [dbo].[T1] TO  SCHEMA OWNER
GO

If I now ALTER that table and set LOCK_ESCALATION to TABLE, like in the following example:

USE [DB1];
GO
ALTER TABLE [T1] SET (LOCK_ESCALATION = TABLE);
GO

this line will not be included in the CREATE script generated by "Script Table as -> CREATE to".

USE [DB1]
GO

/****** Object:  Table [dbo].[T1]    Script Date: 3/26/2015 9:14:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T1](
    [C1] [int] NOT NULL,
    [C2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER AUTHORIZATION ON [dbo].[T1] TO  SCHEMA OWNER
GO

Is there a way to always include LOCK_ESCALATION in the CREATE scripts?

I am using SQL Server Management Studio 2012.


texts: Is there a way to group often used tables and queries in SSMS?

texts:
I am not a DBA by training, but due to role shifts I am having to modify and maintain tables and stored procedures fairly often. I would like to visually group tables and stored procedures together in a sort of shortcuts folder. So that when I need to open several related views I don't have to scroll all over the database. Is something like this possible?

Sorry if this is a dumb question. I am just trying to make this part of my job a little less tedious.


texts:
Using the example of a 'likes' feature, whereby you can like (for example), a blog, comment, etc, is it better to have a separate table for each section (likes_blog, likes_comment, etc) or simply have a special row in table 'likes' (row 'section')?

By better, I am referring to scaling - millions of records, indexing, select, insert, etc.

I would presume it's better to have separate tables for each section as each will be relatively smaller, which might inherently bring with it less issues. Is this a correct assumption?


texts: Faster way of upgrading postgres to 10 from 9.5

texts:
I am trying to test the migration of postgres to 10 from 9.5

On trying in a test DB of size 490 GB in one postgres node it is taking about 18 mins. The command I used

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.5/bin \
   -B /usr/lib/postgresql/10/bin \
   -d /var/lib/postgresql/data/postgresql0 \
   -D /var/lib/postgresql/10/data/postgresql0 \
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k

Is there any faster way of doing this?


texts:
I am trying to figure out a way to have all of our random SQL scripts that we write under source control. I am already using SSDT for the database project. This is not for these sort of scripts. It is for all the other random queries that we run over time. Before we put them in a stored procedure. Or the other scripts that just don't really fit as a SP.

How can I bring my non SSDT Sql scripts under source control using Visual Studio?


texts: Change the database from restoring state to standby mode

texts:
I have a SQL Server 2012 database that must be always in 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option.

It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (in standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately, which left the database in restoring mode.

I don't want to work all over again from the beginning. How can I change the database to standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file?

My database has no log shipping configuration between two servers, it is just a target database.


texts: Creating SQL Azure login for single database

texts:
I want to create separate logins for each database we create on a SQL Azure database server which only have read / edit permissions for a single database and do not have drop permissions, and to automate this process as much as possible. One of the main reasons for doing this is to make sure that Entity framework drop and recreate scripts can't accidentally run against the database.

I found the following which looks very similar to what I'm trying to do

Allow user to do anything within his own schema but not create or drop the schema itself

Am I right in thinking I need to first create a login, then create a user, then create a role and add the user to this role?

CREATE LOGIN dblogin WITH password='m77hHmSk';

CREATE USER dbuser FROM LOGIN dblogin;

CREATE ROLE dbuserrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'dbuserrole ', 'dbuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO dbuserrole ;

I've come up with the above.. am I close? Where would I specify the database which can be accessed?


texts: Multiple SELECT vs single SELECT over TCIP

texts:
My question is a performance one:

I need to select roughly 400+ rows, i know their ids.

currently i loop in code and fire off multiple queries over tcp/ip - because its easy:

...
select [columns] where id = 1544;
select [columns] where id = 4765;
select [columns] where id = 3544;
select [columns] where id = 4090;
select [columns] where id = 5060;
...

Would it be more efficient on SQL server to fire off a single query for a date range instead (the rows I'm after are date-sequential, but we currently don't have a date field in that table), keeping in mind the overhead of multiple transactions vs a single transaction:

select [columns] where date between x AND y;


texts: High Availability Options for 2008 R2 or 2014 to 2016 "Mirror" for Reporting?

texts:
I'm a BI Developer investigating options for this scenario.

I'm going to preface this question by saying I don't know the proper terms, so I will put "quotes" around terms that are probably not the official SQL terms.

Let's say I have a SQL 2008 R2 SP1 Standard instance that runs a 3rd party vendor's transactional application.
We are unable to create or modify indexes on this database to performance tune for reporting queries, due to it being a vendor-controlled database (a common problem, yes?).

We want to have a "mirror" (for lack of better/accurate SQL term) of just the DATABASE (not the whole application) from the source instance onto a SQL 2016 Enterprise instance.

The 2016 "mirror" would have INSERTS/UPDATES/DELETES that happen on the 2008 R2 source database "pulse copied" to it on a very frequent basis, say every, 1 minute (could be 5 minutes, could be 10, don't know what's feasible yet).

The 2016 "mirror" would be used for reporting purposes, that we could tune and hit without negatively impacting the source database with a bunch (potentially up to 2,000) of queries simultaneously.

The "mirror" would therefore become the data source for reports (no load balancing from mirror versus source is necessary), instead of the live database.

Failing Over from 2008 R2 [source] to 2016 [target] is irrelevant, and is not the goal.

The goal is a "copy" or "mirror" of the source database to a target "mirror" that is 1-10 minutes behind the live database, and that "mirror" is to be used for reporting (SSRS, SSAS Tabular, Power BI, etc.) and that allows us the freedom to performance tune via indexes, et cetera that would otherwise not be allowed on the vendor's database.

Given this info, does the "mirror" must be READ-WRITE for the index creates / alters / rebuilds / reorgs, or can it be READ ONLY?

What are our valid options for this mixed SQL Server version scenario?
Log Shipping?
Replication?
Database Mirroring?
Availability Groups?
Others??

Does the answer change if the source system gets upgraded from SQL 2008 R2 Standard to SQL 2014 Enterprise?

I consider my question to be distinct and different from How to accomplish SQL Server 2012+2014 high availability since the requirements are different, and thus is not a duplicate.


texts: How to convert the column sum-function-total to dollars in Oracle SQL?

texts:
I am unable to format the below query intending to display the column-alias total re-formatted to display as dollars.  Though unsuccessful, I suspect a nested solution combined with TO_CHAR, is appropriate but I haven't succeeded with my attempts.

SELECT SUM(TO_CHAR(invoice_total,'$999,999.99')) AS "Total Invoice Amount"
FROM company.invoices;

Can anyone please assist?  Thank you.


texts: Orderable representation of multi-part values

texts:
Consider a table for construction projects.
Each project requires resources, e.g. steel, wood, copper, etc.
Suppose we want the following types of queries:

All projects requiring less than 3 units of wood.
All projects requiring only wood and steel.
All projects requiring at least four units of copper but no steel.

What schema design supports queries like this?

Here's one idea:

CREATE TABLE projects (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE materials (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE projects_and_materials (
    project_id INTEGER,
    material_id INTEGER,
    value INTEGER,
    FOREIGN KEY(project_id) REFERENCES projects (id),
    FOREIGN KEY(material_id) REFERENCES material (id)
);

The value column tells us how much of the material referenced by material_id is needed for the project referenced by project_id.
This schema can represent the data, but it's not clear to me that it's good for queries.


texts:
I am in the process of moving my servers from stand alone hosting to AWS using RDS (MySQL) for database usage.

One of the first things RDS states is that in order to use their backup, and snapshot features of RDS the tables must be using InnoDB.

My site has always run on MyIsam, probably mainly because 10+ years ago, it seemed like the right choice.

Because of this, my database structure is quite simple, and quite separate, there are no join queries on the site, so every select is only getting data from one table.

some of the tables have quite a few indexed fields, The site requires, frequent inserts, regular updates and many reads,

I don't use full text searching, the majority of queries revolve around either the UserID or the tables autoincrement ID

So, my question is, what to expect if i was to move to Innodb? I am not that worried about the transaction side of it. and for this reason MyISAM is ok. I am more concerned about backup, previously I had a cron job that would automate the backup , but of course this came at a price of lockouts while the backup was in process.

MY understanding of InnoDB is that because it does row level locking, this would not be an issue.

But what about performance? I have read so many reviews and bench tests, but i dont know how they relate to my situation.

Can I move over to InnoDB without worrying about setting up foreign keys etc, can i keep things as simple as they are now, or would that negatively affect the system if using InnoDB.

I realize that InnoDB has become the default option in MySQL these days, which makes me think that all dbs should be running on it anyway.

What I dont want to do is have my database fall over because I didnt set up innodb properly.

Is it even worth moving my tables over to it, given its been running just fine on MyISAM for years.

Or is it a case of, this is good cause to change?


texts:
Structure of table:

user-----headline----day----number
1   -----lotr    -----1 -----  2
1   -----lotr    -----1 -----  2
1   -----cpo     -----2 -----  2
1   -----cpo     -----3 -----  6
2   -----ouch    -----1 -----  5

Results I am trying to get:

user-----headline-----number
1 -----    lotr  ----- **4**
1 -----    cpo   ----- **8**

echo
1 has lotr 4
1 has cpo 8

The query that I am trying to get is based on the user logged in and his id, in this case = 1.

I am trying like this, but no result:

SELECT DISTINCT headline, sum( number ) as result
FROM table
where user = '$login_session'"

echo
result;

What is the best way to get these results and echo them?

I tried Rolando's query but nothing happened:


texts: Select when group by contains more than one row with the same value?

texts:
I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many relationship.) I built a SQL fiddle to demonstrate this in context: http://sqlfiddle.com/#!6/83952/1

In this fiddle, the design column of the perf_ticket_type table should be the same for every ticket_type that has the same perf_id, but I'm trying to select only the instances where it doesn't. So for perf_id 3, there is more than one unique design being returned with the query I'm using currently.

What I want as my result is the two columns of the performance table only for perf_id 3, based on the multiple values of design for that perf_id in the joined table.

I've been frustrated by understanding GROUP BY in the past, so I'm unsure if there's something different I could be doing here to get my desired result. At the moment, I think I can select what I have in the fiddle into a temp table and then do another select on that with a GROUP BY perf_id HAVING COUNT(*) > 1 to get what I want (as per select rows where column contains same data in more than one record), but that seems like it's an extra step.


texts: File database vs application server database for concurrent performance & bulk data

texts:
I asked a question about Ripple's database implementation, and received this response:

The ripple server uses SQLite for structured data and a configurable "back end" for unstructured "bulk" storage.
The structured data consists of things like transactions indexed by which accounts they affected. The unstructured data consists of "chunks" of data indexed by hash that constitute portions of network history.
The preferred back end for bulk storage is currently RocksDB on Linux platforms.

This strikes me as strange since Ripple's structure allows the developers to place almost any demand they wish upon the server operators.  In other words, why not use a database server, specifically PostgreSQL?
I found this interesting breakdown of PostgreSQL vs SQLite, and this explanation:

It breaks down to how they implement snapshot isolation.
SQLite uses file locking as a means to isolate transactions, allowing writes to hit only once all reads are done.
Postgres, in contrast, uses a more sophisticated approach called multiconcurrency version control (mvcc), that allows multiple writes to occur in parallel with multiple reads.

First, is it true that the ideal implementation for bulk storage is to use a file database?
Second, is it true that for concurrent reads & writes, PostgreSQL vastly outperforms a file database?
Lastly, when tables approach billions of rows in length, for concurrent performance, is a file database or PostgreSQL superior?
Please assume both alternatives are ideally tuned.


texts: Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition?

texts:
(I'm asking this because I was reviewing this older question I asked and because I'm currently writing some queries and getting annoyed at how much I need to repeat myself).
In ISO-compliant SQL implementations, expressions and computed-columns in a SELECT clause cannot be referenced by other expressions in the same query (i.e. not in other expressions in the same SELECT, nor the same query's WHERE, GROUP BY and HAVING clauses). The result is that queries that perform simple text-processing operations end-up unnecessarily verbose.
Consider a query that returns the first-substring delimited by angle-brackets '<text goes here>' without causing runtime errors if the text is '> like this <' or 'this>':
SELECT
    CASE
        WHEN
            CHARINDEX( '<', textBlob ) > 0 AND CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) > CHARINDEX( '<', textBlob ) )
            THEN
                SUBSTRING(
                    textBlob,
                    /*start:*/ CHARINDEX( '<', textBlob ),
                    /*length:*/ CHARINDEX( '>', textBlob, CHARINDEX( '<', textBlob ) ) - CHARINDEX( '<', textBlob )
                ) AS angleBracketText
    END
FROM
    table
-- Alternatively, move the CASE WHEN expression to the WHERE clause.

The only alternative which avoids any repetition of scalar function call-sites is to use derived-table inner-queries - which introduces repetition of column projections (which isn't as bad as scalar function repetition, but still tedious):
SELECT
    SUBSTRING( textBlob, openIdx, closeIdx - openIdx ) AS angleBracketText
FROM
    (
        SELECT
            textBlob,
            openIdx,
            closeIdx
        FROM
            (
                SELECT
                    textBlob,
                    openIdx,
                    CHARINDEX( '>', textBlob, openIdx ) AS closeIdx
                FROM
                    (
                        SELECT
                            textBlob,
                            CHARINDEX( '<', textBlob ) AS openIdx
                        FROM
                            table
                    ) AS q1
                WHERE
                    openIdx >= 0
            ) AS q2
        WHERE
            closeIdx > openIdx
    ) AS q3

Did Codd, et al. really design SQL to be used like this? I know that they intended for SQL to be a "natural language" (which was all the rage in the 1970s), which surely implies a degree of terseness and eliminating redundant logic (because natural-language has lots of self-references like "it", "this", "that", etc), so I doubt they really intended their language-rules to result in this kind of query being written...
So, at risk of asking an un-answerable question (as the original designers of SQL are away from the keyboard right now): Why did Codd, et al. prohibit intermediate scalars in SELECT - and how would they have recommended avoiding syntactic repetition like in the above queries?

Footnote:
(If I had my way (I wish...) I'd be able to express it like this...)
SELECT
    $openIdx  = CHARINDEX( '<', textBlob ),
    $closeIdx = CASE WHEN $openIdx >= 0 THEN CHARINDEX( '>', textBlob, $openIdx ) END,
    CASE WHEN $openIdx >= 0 AND $closeIdx >= 0 THEN
        SUBSTRING( textBlob, $openIdx, $closeIdx - $openIdx )
    END AS angleBracketText
FROM
    table


texts: What index can I create to help a query using multiple datetime columns and datetime functions in its where clause?

texts:
SQL Server 2016

I have the following query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    (   [ValidFrom] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )
     AND (   [DataEffective] <= SYSDATETIMEOFFSET()
         AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
         )

I'm getting a table scan every time I run this query. I need an index on this table because this query will be run a lot. I've tried several different ones but I can't seem to get the server to use any of them.

I've tried indexing:

ValidFrom
ValidFrom, DataEffective
ValidFrom, ValidUntil, DataEffective, DataExpired

I've also tested against both an empty table and a table seeded with 10202 rows.

I've also tested with a restructured query:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET()
     AND SYSDATETIMEOFFSET() < COALESCE ([ValidUntil], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))
     AND SYSDATETIMEOFFSET() < COALESCE ([DataExpired], DATEADD (DAY, 1, SYSDATETIMEOFFSET()))

and like this:

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= SYSDATETIMEOFFSET()
     AND [DataEffective] <= SYSDATETIMEOFFSET()
     AND (   [ValidUntil] > SYSDATETIMEOFFSET()
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > SYSDATETIMEOFFSET()
         OR  [DataExpired] IS NULL
         )

and like this (to remove the non-deterministic SYSDATETIMEOFFSET() reference):

DECLARE @Now DateTimeOffset(7)

SET @Now = SYSDATETIMEOFFSET()

SELECT   [Id],
         [Descriptor]
FROM     [Networks]
WHERE    [ValidFrom] <= @Now
     AND [DataEffective] <= @Now
     AND (   [ValidUntil] > @Now
         OR  [ValidUntil] IS NULL
         )
     AND (   [DataExpired] > @Now
         OR  [DataExpired] IS NULL
         )

Table Definition

CREATE TABLE [dbo].[Networks](
[ValidFrom] [datetimeoffset](7) NOT NULL,
[ValidUntil] [datetimeoffset](7) NULL,
[DataEffective] [datetimeoffset](7) NOT NULL,
[DataExpired] [datetimeoffset](7) NULL,
[UserId] [nvarchar](128) NOT NULL,
[Id] [uniqueidentifier] NOT NULL,
[Descriptor] [nvarchar](255) NULL )
ON [PRIMARY]
GO


texts: PgAdmin4 unusable when database has lots of schemas

texts:
I have a production database with 60K database schemas. PgAdmin4 is unusable, I cannot even open "Query Tool" window. I suppose that PgAdmin tries to load database metadata, so that's the reason why it's so slow. Is that true and can it be disabled? Can I do anything to speed it up?

I tried to use other DB clients like DBeaver or TablePlus and I have the same problem.


texts: How can Restore MS SQL 2000 database To Ms SQL 2012

texts:
Please suggest:
I have MS SQL 2000 database  backup i want to Restore in Ms SQL 2012 Please suggest.


texts: How to properly implement relationship between a user and multiple other users

texts:
I have a table with the following structure:

Table_1
--- UserId (int)
--- UserName (varchar)

A user can have a relation with other users. Meaning, a UserId from Table_1 can have a relation with other UserIds. So, the question is, how should I structure a second table to implement this?

The second table needs to contain the UserId of a user and other UserIds that the user has relations with. So, should I structure the second table as follows:

Table_2
--- UserId (int - Primary Key)
--- OtherId (varchar)

In the structure above, in OtherId I was planning to store all other UserIds in conjunction, like one column would contain all other UserIds that the principal UserId is related with, but each UserId in the OtherId column would be separated with a comma (,). The way I would retrieve them would be by taking the value from the OtherId column and then, in code-behind, splitting the string by (,) into an array. Then I would use each value from the array to retrieve the UserName from Table_1.

This was the idea I had. I tested it on a database of 300,000 records in Table_1 and one relationship string consisting of almost 3000 UserIds in the second table. This took a significant amount of time (10s) to process all the data, including the splitting of the string.

I am almost certain that my idea is nowhere near to ideal practices. Can someone just give me some ideas on how I should go about this?


texts: Trouble setting up mirroring on SQL Server 2016

texts:
I've setup a test SQL Server 2016 server.  I've installed 2 instances, restored a backup on the primary.  Then restored a backup on the secondary with NO RECOVERY, then restored the transactional log on the secondary, also with NO RECOVERY.

I then followed the prompts in the Mirroring Wizard off the Database Properties page and ended up getting an error:  Database 'Ares' cannot be open.  It is in the middle of a restore.

What am I missing?


texts: Automated SQL backup on a timely fashion, & cleaup the database for the backed up data

texts:
I need to back up SQL database (historian), on a timely fashion, and then clean up the database by removing the backed up data.

I am using MS SQL 2008 (R2), on a Windows XP machine. The biggest issue is the very limited hard disk space. The database is limited to a maximum of 3GB! In terms of overall performance, the PC is really slow, and unfortunately I do not have the choice to change that. So, I could consider backing up overnight when the data flow is expected to be less.

The intention is to back up the data every two weeks, have it stored in a special directory (e.g. c:\ ). Then an operator can move the backup to another machine. Given the limited space, I could consider some 'house clean up', by removing the backed up data. What is more important is the ability to merge the regular backups to an external database. So perhaps a typical SQL backup routine and restore, could be an option.

I would appreciate your kind advice regarding this matter. Thank you.


texts: Highlight Rows with Duplicate Column in SSRS Report Builder

texts:
I have used the below expression to highlight duplicate column
=IIf(Fields!BankAccount.Value=Previous(Fields!BankAccount.Value),"Firebrick","LightSteelBlue")

I have added another column to that shows the next value. However I am not sure how to highlight both the duplicate records (rows)
=Lookup(Fields!RowNum.Value+1,Fields!RowNum.Value,Fields!BankAccount.Value,"DataSet1")


texts: Find points within a distance using MySQL

texts:
I have a MySQL table

Users(State, City, Zip, Lat, Long)

I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my input Lat= 78.3232 and Long = 65.3234 and distance = 5 kilometers. I would like to get the list of users who are inside 5 kilometers distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query?


texts: How to combine `delete` and `insert` operations in one statement?

texts:
for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)?

create table product(product_id integer primary key);
insert into product(product_id) values(1);
insert into product(product_id) values(2);
insert into product(product_id) values(3);

create table split( parent_id integer not null references product,
                    child_id integer not null references product,
                    primary key(parent_id, child_id) );

insert into split(parent_id, child_id) values(1,2);
insert into split(parent_id, child_id) values(1,3);

create table sale(sale_at date, product_id integer references product);

insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 1);
insert into sale(sale_at, product_id) values(sysdate, 2);
insert into sale(sale_at, product_id) values(sysdate, 2);

select sale_at, product_id from sale where product_id not in (select parent_id from split)
union all
select sale_at, child_id from sale join split on(parent_id=product_id);

/*
|                     SALE_AT | PRODUCT_ID |
--------------------------------------------
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
| June, 10 2013 15:18:22+0000 |          3 |
| June, 10 2013 15:18:22+0000 |          2 |
*/

(SQLFiddle)

----edit:

to clarify, the effect I'm trying to get with a single merge statement is this:

insert into sale(sale_at, product_id)
select sale_at, child_id from sale join split on parent_id=product_id;

delete from sale where product_id in (select parent_id from split);

and the reason I'd like a single statement solution is to prevent a race condition where another transaction inserts/commits data between the insert and delete in the primary transaction.


texts: PostgreSQL ORDER BY clause effect different number of rows to return in pivot table

texts:
I've worked on "Seven Databases in Seven Weeks" recently, and when implementing the the task with building a pivot table using crosstab() - the task was already described here: https://stackoverflow.com/questions/34665186/creating-a-calendar-like-table-using-crosstab-in-postgres?rq=1

I faced kind of PostgreSQL "magic" for ORDER BY clause.

The right query is:

SELECT * FROM crosstab (
  'SELECT extract(week from starts) AS week,
     extract(dow from starts) AS day, count(*)
   FROM events
   GROUP BY week, day
   ORDER BY week',
  'SELECT generate_series(0,6)'
) AS (
week int,
Sunday int, Monday int, Tuesday int,
Wednesday int, Thursday int, Friday int, Saturday int
) ORDER BY week;

The data in the table the following:

It will return this data:

If I were to omit the ORDER BY week in first query in crosstab() function, it's will return 12 rows instead of 10.

SELECT * FROM crosstab (
  'SELECT extract(week from starts) AS week,
     extract(dow from starts) AS day, count(*)
   FROM events
   GROUP BY week, day',
  'SELECT generate_series(0,6)'
) AS (
week int,
Sunday int, Monday int, Tuesday int,
Wednesday int, Thursday int, Friday int, Saturday int
) ORDER BY week;

Output:

There are three strange things here:

first is that when to run EXPLAIN VERBOSE on both of the queries, it show the completely identical QUERY PLAN except ORDER BY clause;
when to run the sub-queries separately it will return 12 rows for both;
ORDER BY clause should impact on the order only.

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Sort  (cost=59.83..62.33 rows=1000 width=32)
   Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
   Sort Key: crosstab.week
   ->  Function Scan on public.crosstab  (cost=0.00..10.00 rows=1000 width=32)
         Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
         Function Call: crosstab('SELECT extract(week from starts) AS week,
      extract(dow from starts) AS day, count(*)
    FROM events
    GROUP BY week, day
    ORDER BY week'::text, 'SELECT generate_series(0,6)'::text)
(10 rows)

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Sort  (cost=59.83..62.33 rows=1000 width=32)
   Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
   Sort Key: crosstab.week
   ->  Function Scan on public.crosstab  (cost=0.00..10.00 rows=1000 width=32)
         Output: week, sunday, monday, tuesday, wednesday, thursday, friday, saturday
         Function Call: crosstab('SELECT extract(week from starts) AS week,
      extract(dow from starts) AS day, count(*)
    FROM events
    GROUP BY week, day'::text, 'SELECT generate_series(0,6)'::text)
(9 rows)

How to explain this behvaior? The closest question was this, but it's not the same I think: Why does changing the sort order return a different number of results? Is it a bug?

Thanks in advance.


texts: Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

texts:
I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?

SELECT *
FROM ( VALUES (1) ) AS t(x);
 x
---
 1
(1 row)

(syntax from postgresql).


texts: Log backup vs differential backup if latter is broken

texts:
Under full recovery mode, will a differential backup "break" the previous log backups?

Let me give an example: Let's say we have the following backups:

FULL backup 1
Log backup 1
LogBackup 2
DIFF backup 1
LogBackup 3
LogBackup 4

Normally to restore in this situation, one would restore as follows:

FULL backup 1
DIFF Backup 1
LogBackup 3
LogBackup 4

My question is as follows: If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups? Like this:

FULL backup 1
LogBackup 1
LogBackup 2
LogBackup 3
LogBackup 4

Any help is appreciated.
If this has been answered in another post, please let me know (I tried searching).


texts:
I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given:

IP: 12.34.56.78
Login: john
Password: pw1
Database: TESTDB
Table: test_table

The remote database is outside my LAN, but firewall is configured on both sides, so I can query it by opening SSMS directly from my database server.
I perform the following:

EXEC sp_addlinkedserver '12.34.56.78', 'SQL Server';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = [12.34.56.78], @locallogin = NULL , @useself = N'False', @rmtuser = 'john', @rmtpassword = 'pw1'

Up to now, no error, and I can see the linked server in SSMS, "Test Connection" is successful but I cannot query it.

select * from [12.34.56.78].TESTDB.test_table

Returns this error:

Msg 208, Level 16, State 1, Line 1
Invalid object name '12.34.56.78.TESTDB.test_table'.

What is the proper way to define a linked server from the Internet, and if possible assigning a nice name to it (rather than referring to it by IP)?
Should I be using SQLNCLI as a provider?


texts: Clarifications on Transparent Data Encryption

texts:
This is the first time we are applying TDE on production databases that are critical and I have the questions below.

While TDE is in progress:

Can we still access the application that is working on this database with a connection string?
If TDE fails while it is encrypting the database, will it break the .mdf file?  How would I recover the database?
Are the .ldf and .mdf still accessible, can we read and write to these files?
What are the database operations that will be affected while the TDE is in progress?
Will TDE have any effect on disk usage?
Will TDE have any effect on database backups? Are both the .bak, .trn files encrypted automatically if TDE is enabled?
What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?
Will there be any effect on SQL Server service packs or cumulative updates?

The platform of relevance is SQL Server 2014 SP2 GDR with availability groups.


texts: Is it okay to have two oracle clients (both 11g and 12c) in the same db server? What are the potential issues I could face? The reason im doing this is to connect the app server where I'm deploying the clients to two databases each running on 11g and another on 12c.


texts: Plpgsql seems to be deleting and inserting instead of updating - Why?

texts:
I am using postgreSQL 7.4.

I have a large table , call it table_a:

key1 INT NOT NULL,
key2 INT NOT NULL,
data INT NOT NULL,
itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::timestamp without time zone))::INTEGER

and a table that summaries the last update time for key1, call it table_b:

key1        INT NOT NULL,
max_itstamp INT NOT NULL

I created a trigger function in plpgsql to update or insert rows in table_b as necessary:

CREATE OR REPLACE FUNCTION table_b_update() RETURNS TRIGGER AS '
 DECLARE
  l_key1 INT;
  l_itstamp INT;
 BEGIN
  l_key1 := new.key1;
  l_itstamp := new.itstamp;
  PERFORM TRUE FROM table_b WHERE key1=l_key1;
  IF NOT FOUND THEN
   INSERT INTO table_b(key1, max_itstamp) values (l_key1, l_itstamp);
  ELSE
   UPDATE table_b SET max_itstamp=l_itstamp WHERE key1=l_key1;
  END IF;
  RETURN NULL;
 END'
LANGUAGE plpgsql IMMUTABLE;

and then I attached a trigger to table_a:

CREATE TRIGGER table_a_trigger1 AFTER INSERT OR UPDATE ON table_a FOR EACH ROW
EXECUTE PROCEDURE table_b_upate();

Now, the time to insert new data into table_a grows incrementally.  The file footprint of table_b grows steadily.

I have used RAISE NOTICE commands in the function to confirm that the If statement causes an UPDATE and not an INSERT after the first call per key.

Since the table_a insert time grows for each INSERT, I tried a VACUUM FULL on table_b.  The table_a insert time was reduced considerably.  The file size for table_b was reduced considerably.  After the VACUUM FULL the table_a insert time started to grow again.  I don't want to do a VACUUM FULL after every INSERT into table_a though.

Is it possible that the UPDATE is actually doing a DELETE and INSERT in table_b?


texts: "The attribute cannot be found when processing" error

texts:
I'm facing following error while processing SSAS MDX cube:

Errors in the OLAP storage engine: The attribute key cannot be found when
processing: Table: 'FACT_TABLE_NAME', Column: 'COLUMN_NAME',
Value: 'VALUE'. The attribute is 'Attribute Name'.

Upon checking, I can see that this VALUE is indeed missing in FACT_TABLE_NAME but it exists in associated DIM_TABLE_NAME. However, this situation for me is perfectly fine and there's a chance that certain values won't exists between DIM and FACT (assuming that DIM can have more data).

Question is - is there anything I can do except processing data with using custom error configuration and ignoring "Key not found" errors?

Additional information

Some screens

I had to blur out value due to data security. Table that is being returned is the view behind WEEKLY MEASURES table.

Join between WEEKLY MEASURES and CUSTOMER happens on CustOrgHier_id both in DSV and in the Cube.


texts: Get first and last record of a selection without using min() max()

texts:
My energy-meter is writing the (accumulated) consumtion every minute to a db. In order to get my todays consumption I use:
SELECT
  (MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today
FROM logging.main_meter
WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE();

Problem is: max() and min() appear to slow the query down a lot. I added a key to column energy_kwh but it didn't help. I bet there is a better way to get the first and last record for the calculation.
The table (its actually much bigger, ~130 columns, so I reduced it to the relevant part):
CREATE TABLE 'main_meter`
(
  id int(11) NOT NULL AUTO_INCREMENT,
  timestamp int(11) DEFAULT NULL,
  strtime datetime DEFAULT NULL,
  energy_kwh double unsigned DEFAULT '0',
) ENGINE=InnoDB AUTO_INCREMENT=1717655 DEFAULT CHARSET=latin1;


texts:
My company is currently going through a data center migration.  We are now down to the SQL Servers and this includes SSRS.  The bad thing that is going to hit us is the current SSRS server is being migrated to a new domain.  So all users/groups that have access to SSRS are in DomainA and after the migration the SSRS server will be in DomainB.

All user and groups were migrated from DomainA to DomainB, supposidly using the preserve SIDs.  During a test we could not get this to work.

So DomainA\User1 does not work when server is changed to DomainB.

We have over 70,000 combinations of user permissions and group permissions across all of our reports.

Does anyone have any suggestions or ideas on how we can properly migrate this server from DomainA to DomainB without having to manually duplicate the security settings.

Note:  This is a virtual machine and is being replicated from one VMWare cluster to one on the new network and then will be dis-joined from DomainA and joined to DomainB.  Service accounts will be changed to DomainB and backup of encryption key will be restored after service account is changed.


texts: How can I make the audit system create new audit files if they already exist?

texts:
I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the same file.

I understand that there is a way to create a new file with suffixes if the audit file already exists. How can I do that? I use Oracle 10.0.4.0


texts: Insert but ignore if duplicate AND set a value different if exist in another table

texts:
I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array containing userIDs from friends that the user invited. I wish to loop through them and insert the invitations into a database. The looping and all is already under control but the database query isn't.

This is what my current query looks like:

SELECT `iID` FROM `invitations` WHERE `iSender` = 'SENDER' AND `iReceiver` = 'RECEIVER';

If that returns zero rows I process this query:

INSERT INTO `invitations` (`iSender`, `iReceiver`) VALUES ('SENDER', 'RECEIVER');

And then I check if they're already signed up to my website:

SELECT `uID` FROM `users` WHERE `uOAuthID` = 'RECEIVER';

If it returns more then 1 row I run the following and final query:

UPDATE `invitations` SET `iProcessed` = 1 WHERE `iReceiver` = 'RECEIVER';

So basically this is how the process is currently shaped:

If the user hasn't already has been invited by the inviter we insert the invitation into the database.
Then we check if the invited user already is signed up.
If he is signed up we update the invitation and say that it already has been processed.

I guess there's a better and faster method to do this with just maybe 1 or 2 queries. I've tried using INSERT IGNORE and ON DUPLICATE but that just gave me errors and gave up.

I hope that you understand what I'm looking for. Thank you all for your time!


texts: Getting around Primary Key requiring non-nullable columns

texts:
Word Problem

I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least with SQL Server 2014) I cannot have a nullable column in a primary key.

Example

CREATE TABLE Test
(
    Id1 INT NOT NULL,
    Id2 INT NOT NULL,
    PRIMARY KEY (Id1, Id2)
)

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

The error is on the Primary Key of Test2:

  A primary key constraint cannot be defined on the nullable column 'TestId2' in the table or user-defined table type ...

My workaround:

CREATE TABLE Test3
(
    TestId1 INT NOT NULL,
    TestId2Calc AS COALESCE(TestId2, -1) PERSISTED NOT NULL,
    TestId2 INT NULL,
    PRIMARY KEY (TestId1, TestId2Calc),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)

But this feels dirty. Is this my only option?


texts: the value of undo_sql in flashback_transaction_query view is always null

texts:
so I have a table defined as follow:

`create table test.test as (select * from dual);

and ran the following update statements:

update test.test set dummy = '1' where dummy = 'X';
commit;

I want to find the undo_sql for the transaction above, so I ran the following statement to find the transaction id

SQL> select dummy, versions_xid, versions_startscn, versions_endscn from test.test
versions between scn minvalue and maxvalue ;

D VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN
- ---------------- ----------------- ---------------
1 0A000500B00A0000           2802291
X                                            2802291

and ran the following statement to find the undo_sql statement

SQL> select undo_sql from flashback_transaction_query
where xid = (select  versions_xid from test.test versions
between scn minvalue and maxvalue where versions_xid is not null);

UNDO_SQL
--------------------------------------------------------------------------------

as you can see, I got nothing, if I run the following statement, I also get nothing.

SQL> select undo_sql from flashback_transaction_query where undo_sql is not null;

no row selected

looks like undo_sql is always null;

Is there anything I didn't configure ?  Here are the values from v$system_parameter

SQL> select name, value from v$system_parameter where name like '%undo%';

NAME                 VALUE
----------------     ----------------
undo_management      AUTO
undo_tablespace      UNDOTBS1
undo_retention       18000


texts: Timezone offset displayed though it shouldn't be stored

texts:
I new to Postgres and v9.6.3 on a Linux system.

From the docs and the PostgreSQL: Up and Running book, I learned that the timestamptz type just subtracts the offset of a timestamp on insertion and then just stores UTC on disk. The timezone information is 'lost' so to say.

Now I'm wondering how the psql client can display the timezone correctly despite that:

The following row was created by something like:

insert into table values ('1999-12-31T00:00:00+0100'::timestamptz, ...);

and is displayed as

select time from table;

          time          | field1 | field2 |
------------------------+--------+---------
 1999-12-31 00:00:00+01 |      1 |       1 |
...

and

select time AT TIMEZONE 'Europe/Berlin' from table;

          time
---------------------
 1999-12-31 00:00:00
...

You can see in the first snippet that postgres seems to know the correct timezone from the insertion ('Europe/Berlin' which has +01h offset to GMT in winter) But how does this come?
I would have expected it do display UTC time.


texts: Getting difference of a column between first and last row with count number

texts:
I have table mytable as shown below

CREATE TABLE mytable( id, created_at, total_number )
AS VALUES
    ( 17330  , '2018-05-24 19:25:29'::timestamp, 26909 ),
    ( 46331  , '2018-05-25 00:57:34',            26914 ),
    ( 72131  , '2018-05-26 00:48:12',            26944 ),
    ( 102583 , '2018-05-27 00:53:50',            26972 );

I would like to get total_number difference between first and the last

I got my first and last row with this query

(SELECT * FROM mytable
    ORDER BY created_at ASC LIMIT 1)
    UNION
    (SELECT * FROM mytable
    ORDER BY created_at DESC LIMIT 1)

My query should return this results. How can I achieve it?

difference        number_of_rows   avg
(26972-26909=63)  4                63/4


texts: MYSQL query working fine in AWS RDS instances with 1 vCPU but hangs if running in instances with 2 vCPU or more

texts:
I have this following SQL query below executed in a Lambda function. The Lambda function connected to AWS RDS running MySQL 5.7.22.

When this query is executed against an instance type db.t2.small or db.t2.micro, the query works fine and results are returned in a few seconds. These instance type have 1 vCPU

However, when we raise the instance type to db.t3.small or db.t3.medium, which has 2 vCPU, the query hangs until the lambda timeout (about 10 minutes). Even after the lambda function timeout, the DB CPU remains at 50% consumption for the longest time until I decided to reboot the RDS.

This is counter intuitive as I would think that having more vCPU should improve performance. I'm guessing it might be linked to multi threading somehow but unable to find any info. Hope to get some help/hints here.

Here's the SQL query in question

INSERT INTO
   CP58 (
   SELECT
      s.BRN, IFNULL(IFNULL((
      SELECT
         rtmp.DLR_CODE
      FROM
         STABLE stmp, CP58_REPORT rtmp
      WHERE
         stmp.DLR_CODE = rtmp.DLR_CODE
         AND s.BRN = rtmp.BRN
         AND stmp.HQ_FLAG = 'HQ'
         AND stmp.STATUS = 'Active'
      GROUP BY
         stmp.COMP_REGNO, rtmp.DLR_CODE
      ORDER BY
         stmp.COMP_REGNO, SUM(rtmp.TOTAL) DESC LIMIT 1),
         (
            SELECT
               rtmp.DLR_CODE
            FROM
               STABLE stmp,
               CP58_REPORT rtmp
            WHERE
               stmp.DLR_CODE = rtmp.DLR_CODE
               AND s.BRN = rtmp.BRN
               AND stmp.HQ_FLAG = 'HQ'
            GROUP BY
               stmp.COMP_REGNO,
               rtmp.DLR_CODE
            ORDER BY
               stmp.COMP_REGNO,
               SUM(rtmp.TOTAL) DESC LIMIT 1
         )
), 'NOHQDEALERCODE') as 'HQDEALERCODE', s.DEALERNAME, s.ADDRESS, IFNULL(MAX(x.CPTABLE_YEAR), MAX(y.EDTABLE_YEAR)) as 'YEAR', IFNULL(SUM(x.CPTABLE), 0) as 'CPTABLE_TOTAL', IFNULL(SUM(y.EDTABLE), 0) as 'EDTABLE_TOTAL',
         (
            IFNULL(SUM(x.CPTABLE), 0) + IFNULL(SUM(y.EDTABLE), 0)
         )
         as 'TOTAL', '0' as 'PDF'
      FROM
         (
            SELECT
               stmp.COMP_REGNO as 'BRN',
               stmp.DLR_CODE as 'DLR_CODE',
               stmp.DLR_NAME as 'DEALERNAME',
               CONCAT(stmp.ADDRESS, ' ', stmp.TOWN, ' ', stmp.POSTCODE, ' ', stmp.STATE) as 'ADDRESS'
            from
               STABLE stmp
            GROUP BY
               DLR_CODE
         )
         s
         LEFT OUTER JOIN
            (
               SELECT
                  subqc.DESTDEALERCODE as 'DEALERCODE',
                  SUM(subqc.RM) as 'CPTABLE',
                  YEAR(subqc.DATETIME) as 'CPTABLE_YEAR'
               FROM
                  CPTABLE subqc
               GROUP BY
                  subqc.DESTDEALERCODE
            )
            x
            on x.DEALERCODE = s.DLR_CODE
         LEFT OUTER JOIN
            (
               SELECT
                  subsubqe.DEALERCODE as 'DEALERCODE',
                  SUM(subsubqe.AMOUNT) as 'EDTABLE',
                  YEAR(subsubqe.BANKACK_DATE) as 'EDTABLE_YEAR'
               FROM
                  EDTABLE subsubqe
               GROUP BY
                  subsubqe.DEALERCODE
            )
            y
            on s.DLR_CODE = y.DEALERCODE
      GROUP BY
         BRN
      HAVING
         TOTAL > 0
         AND HQDEALERCODE <> 'NOHQDEALERCODE')

When the query hangs, I ran the EXPLAIN command and below is the result. It remains in this state unchanged, while consuming 50% cpu, until I restart the DB server.


texts: MySQL Space efficient way of storing indexed key-value pairs associated with table

texts:
We're storing transactions in an accounting system using two tables, one contains the general description of the particular transaction (the other contains the entries, one for each account - which isn't relevant for the question).

Slightly edited, the first table looks something like this:

CREATE TABLE `transaction` (
`transaction_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`transaction_type` varchar(48) NOT NULL,
`comment` text NOT NULL,
`created` timestamp NOT NULL,
PRIMARY KEY (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The 'comment' field is only text, but occasionally contains interesting data, for example it could look like this: "remote_transaction_id=EN123121021 player_id=128912"

Since we're talking of 10s of millions of entries each month, searching using text-matching in the text isn't very efficient, and the text index would probably be pretty hideous as well.

A neat solution would be to put generic tags in a separate table with relevant indices:

CREATE TABLE `transaction_string_tag` (
`transaction_id` int(11) unsigned NOT NULL,
`tag_id` int(11) unsigned NOT NULL,
`tag_value` varchar(127) NOT NULL DEFAULT '',
 PRIMARY KEY (`transaction_id`,`tag_id`),
 KEY `transaction_id` (`transaction_id`),
 KEY `tag_id` (`tag_id`),
 KEY `tag_value` (`tag_value`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This tag table would grow pretty quickly.

Given that we're using MySQL 5.1.xx, what's our best option? Imagine that we're adding 3-4 tags per transaction.

EDIT: The tag tables will be only be written to once.

EDIT 2: Mistakenly wrote 100s instead of 10s. Last month had 12 million entries in the transaction table above (and the number of rows in the corresponding entry table is arount 36 million)


texts: What permissions do you need to take a mysql database backup via SSH?

texts: are 'SELECT' and 'LOCK' privileges enough for a 'mysql database user' to take a full database backup via SSH? I am able to take a backup using these privileges, but unsure if I am backing up all the files properly. So my question is, are these two privileges enough or does the user need more privileges in order to take a proper backup?


texts: how to find maximum of a column and other corresponding columns values in that table


texts: Is this atomic set and get in MariaDB / MySQL: `UPDATE t SET col = @my_var := col + 1 WHERE id = 123`?

texts:
I want to atomically update a single row and get its value.
I discovered today that MariaDB / MySQL has this additional way of setting user variables using the assignment operator :=, described e.g. here.
I wonder therefore if the following statement, followed by checking the value of @my_var, accomplishes what I want:
UPDATE t SET col = @my_var := col + 1 WHERE id = 123;
It seems it should, but I'd like to confirm with the pundits.

(I know there's also SELECT .. FOR UPDATE.)


texts: Recovering credential data from a master DB backup

texts:
On a SQL Server 2008 R2 instance, a misconfigured credential used as a SSIS proxy for an SSA job was dropped before I could look at the system views to figure out when it was originally misconfigured.

I restored a backup of the master DB under an alternate user DB name to see if I can extract the data but it appears the sys.credentials view is looking at the live master DB instead of the restored DB even with the DB context set to the restored DB.

Is there an alternate method to retrieve the data from this and other system views from a backup without the usual DR methodology?


texts: Dynamic Number of Columns in Pivot Table

texts:
I have a table to store StudentID, SubjectID, ObtainedMarks. Now I have to create a pivot table to show studentID and obtainedMarks in different subject. Number of subject may differ. I could generate pivot table for fixed subjectID like

SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  ([1],[2],[3],[4])
)as finalResult

This works but problem is 1,2,3 is not static it should come from same table

So I did something like

declare @minvalue int, @maxvalue int
declare @myArray varchar(max)
set @MyArray=''

select RowID=IDENTITY(int, 1,1), SubjectID into #tempSubject from [Exam].[ObtainedMarkEntry]
select * from #tempSubject
set @minvalue = (select min(RowID) from #tempSubject)
set @maxvalue = (select max(RowID) from #tempSubject)
while (@minvalue<=@maxvalue)
begin
    declare @sid nvarchar(10)
    set @sid=(select SubjectID from #tempSubject where RowID=@minvalue)
    set @myArray= @myArray + '[' + @sid + '], '
    set @minvalue= @minvalue+1
end
set @myArray= SUBSTRING(@myArray,0,len(@myArray))
drop table #tempSubject

declare @q as nvarchar(max)
SELECT * FROM (SELECT StudentID, SubjectID, ObtainedMarks
from [Exam].[ObtainedMarkEntry]) as InvoiceResult
PIVOT
(
    sum(ObtainedMarks) for [SubjectID] in  (@myArray)
)as finalResult

Now I am getting error at in(@myArray) can't I assign variable in this section.


texts: Where is the pg_hba.conf file amendment on pg_admin 4?

texts:
On pg_admin3, you have an option to change / amend the pga_hba.conf file as you can see here

I tried to find a similar option in pg_admin 4 but to no avail.

Has the option disappeared?


texts: MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database

texts:
I have the following two tables that I ingest with data from Maxmind's GeoLite2 Country database:
CREATE TABLE `country_location` (
  `geoname_id` int(11) UNSIGNED NOT NULL,
  `locale_code` varchar(255) DEFAULT NULL,
  `continent_code` char(2) DEFAULT NULL,
  `continent_name` varchar(255) DEFAULT NULL,
  `country_iso_code` char(2) DEFAULT NULL,
  `country_name` varchar(255) DEFAULT NULL,
  `is_in_european_union` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`geoname_id`),
  KEY `idx_country_iso_code` (`country_iso_code`),
  KEY `idx_continent_code` (`continent_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `country_blocks` (
  `network` varchar(255) NOT NULL DEFAULT '',
  `network_start_integer` NUMERIC(39, 0) unsigned DEFAULT NULL,
  `network_last_integer` NUMERIC(39, 0) unsigned DEFAULT NULL,
  `geoname_id` int(11) UNSIGNED DEFAULT NULL,
  `registered_country_geoname_id` int(11) DEFAULT NULL,
  `represented_country_geoname_id` int(11) DEFAULT '0',
  `is_anonymous_proxy` tinyint(1) DEFAULT '0',
  `is_satellite_provider` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`network_last_integer`),
  KEY `idx_network_start_integer` (`network_start_integer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have been previously using the following query:
SELECT l.country_iso_code, l.continent_code
    FROM geo.country_location l
        JOIN geo.country_blocks b
    ON (l.geoname_id=b.geoname_id)
    WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
    ORDER BY network_last_integer LIMIT 1;

This query executes in less than ~1ms which is expected to be fast given the usage of only 1 key.
Unfortunately since MaxMind now adds some additional columns for geoname_id (registered_country_geoname_id, represented_country_geoname_id) there are certain cases (when geoname_id = 0) when the query would produce false positives returning the results of the next network from country_blocks for which the JOIN is triggered. In such cases I need to use registered_country_geoname_id instead of geoname_id (I don't care of represented_country_geoname_id so far).
I am trying to figure out a way of elegantly resolving that issue without any degradation of speed.
I tried the following but unfortunately it takes ~65ms which is quite slow (and explainable since now I am using two keys):
SELECT l.country_iso_code, l.continent_code
    FROM geo.country_location l
        JOIN geo.country_blocks b
        ON (l.geoname_id=b.geoname_id)
        OR (l.geoname_id=b.registered_country_geoname_id)
    WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
    AND network_start_integer <= CONV(HEX(INET6_ATON('1.1.1.1')),16,10);

To optimize this and since selecting with IN with LIMIT from within other queries is not supported with the MariaDB 10.5 I ended up selecting the proper geoname_id first:
SELECT IF(geoname_id = 0, registered_country_geoname_id, geoname_id) as geoname_id
FROM geoip.country_blocks
WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
ORDER BY network_last_integer LIMIT 1;

and then using the result to select within country_location.
This is quite fast since I am still using one key in country_blocks, but is there any more elegant way of achieving this?
~Cheers~


texts: SQL Server Import Export Wizard Error - Server throw Exception

texts:
I am trying to move all database tables from one db to another as a nightly job.

The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client 10.0 to that one. I should mention I only have read access to this server.

The second (import) machine where I want to save the job is running 2014 - Windows Authentication - I connect with SQL Server Native Client 11.0 to this one.

Copy all tables. I have tried both saving the SSIS package as SQL Server and File System. It throws an error when I actually try to save it. The error says, "The Server threw an exception." Additional information: The Server threw an exception. Super helpful.

the SQL Server instance is new, so I thought it might be a rights issue. I checked the event log and have these two errors 1000 Application Error and 10016 Microsoft-Windows-DistributedCOM.

For 10016 I tried this run -> dcomcnfg -> Component Services -> DCOM config -> MSDTServer100 -> Right click Properties -> Security -> Launch and Activation Permissions. Also, I am already an admin on this box.

In SQL 2014 I don't have MSDTServer100 So I changed permissions for these DTS Package Host (several of them) and Microsoft SQL Server Integration Services 12. Is there a different config I am missing?

I should add, I ran this wizard locally and the export worked just fine. I couldn't save it because I only have SQL express locally.

I should mention I only have read access to the first server. I ran a Profiler trace. I see two errors User Error Message "Changed database context to 'msdb'" and "Changed language setting to us_english" the event on the trace is Audit Logout. I don't do this often enough to know, but I wouldn't say I'm seeing anything out of the ordinary.


texts: Deleting table entries based on criteria in another table

texts:
We want to be able to delete entries from a MySQL table, based on deletion criteria set in another table.  Let me explain with an example.
I have two tables defined as follows:
CREATE TABLE base_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f1 VARCHAR(8),
  f2 VARCHAR(8),

  PRIMARY KEY (id)
);

CREATE TABLE del_criteria_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f3 VARCHAR(8),
  f4 VARCHAR(8),

  PRIMARY KEY (id)
);

base_tbl has the data and del_criteria_tbl has the criteria for deleting entries from base_tbl.
I populate the tables as follows:
INSERT INTO base_tbl(f1, f2) VALUES ('ABC', '123C57'),
('ABC', '532B49'), ('DEF', '397F45'),
('DEF', '684G65'), ('GHI', '793A86'),
('GHI', '541H32');

and
INSERT INTO del_criteria_tbl(f3, f4) VALUES ('ABC', '532B49'),
('ABC', '813E89'), ('DEF', '397F45'),
('GHI', '541H32');

Obviously:
mysql>SELECT * FROM base_tbl;
+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  2 | ABC  | 532B49 |
|  3 | DEF  | 397F45 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
|  6 | GHI  | 541H32 |
+----+------+--------+

mysql>SELECT * FROM del_criteria_tbl;
+----+------+--------+
| id | f3   | f4     |
+----+------+--------+
|  1 | ABC  | 532B49 |
|  2 | ABC  | 813E89 |
|  3 | DEF  | 397F45 |
|  4 | GHI  | 541H32 |
+----+------+--------+

I would like to define a succinct and efficient SQL operation that executes the following pseudo-SQL logic:
DELETE FROM base_tbl WHERE base_tbl.f1 = del_criteria_tbl.f3 AND base_tbl.f2 = del_criteria_tbl.f4

After the operation is executed, SELECT * FROM base_tbl should yield:
+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
+----+------+--------+


texts: Deconstruct json and insert/update it into a different table in an updatable view

texts:
I have a view that essentially presents a denormalized view onto my data.

It's roughly like having a blog table and a comments table, and the view then shows a blog post per row, with all the comments belonging to that post in an array in a json field.

The view uses the following code to achieve that:

  (SELECT array_to_json(array_agg(row_to_json(x.*))) AS array_to_json  FROM
     (SELECT foo, bar
      FROM table_b
      WHERE a.id = table_b.abc_id) x) AS baz

I've made the view updatable, which works for everything except this kind of column. I also have some typical many-to-many relationships in that data, and those are automatically updated when you edit them via the view. But I couldn't figure out how to do the same for this pseudo-json column.

Essentially, I need to pass this json to an update/insert statement in my INSTEAD OF trigger. I experimented with json_to_recordset, but I couldn't get that to do what I need.

Any idea how I could achieve this?


texts: Index physical stats DMV - Object ID/table not found

texts:
I have installed SQL Server 2012 RC0 on my laptop recently and I was trying a demo which illustrates how shrink database will cause fragmentation. I created a table, a clustered index on the identity column and inserted a few records and then issued the below to check fragmentation:

select avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID('databasename'),
  OBJECT_ID('tablename'), 1, NULL, 'limited')

This is the error message I get:

Could not find table or object ID 2139154666. Check system catalog.

I did a select * from sysobjects and the Object exists with that objectID. Why does SQL Server think that it doesn't exist? What am I doing wrong?


texts: Not able to install MSSQL 2014 on an additional disk(some disk other than the OSdisk) silently on Windows 2012 R2

texts:
I am providing the following paths during installation(and yes, there's no overriding anywhere)-

InstallSQLDataDir = "R:\Microsoft SQL Server\Instance"
SQLBackupDir = "R:\BACKUP\Microsoft SQL Server\Instance\MSSQL12\MSSQL\Backup"
SQLUserDbDir = "F:\DATA\Microsoft SQL Server\Instance\MSSQL12\MSSQL\SQLData"
SQLUserDbLogDir = "L:\LOGS\Microsoft SQL Server\Instance\MSSQL12\MSSQL\SQLLog"
SQLTempDbDir = "T:\\"
SQLTempDbLogDir = "T:\\"
InstallShareDir = "E:\Program Files\Microsoft SQL Server"
InstallShareWowDir = "E:\Program Files (x86)\Microsoft SQL Server"
InstanceDir = "E:\Program Files\Microsoft SQL Server"

Still, everytime, the MSSQL installation happens onto the OSdisk/System Drive/C drive. Why ?


texts:
Recently I installed SQL Server 2016 on my PC.

Here is the detailed information of my server :

  Microsoft SQL Server 2016 (RC1) - 13.0.1200.242 (X64)
  Mar 10 2016 16:49:45
  Copyright (c) Microsoft Corporation
  Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 6.3  (Build 10586: )

After installing server on my PC then tried to install the below plugin's

SQL Pretty Printer Add-In for SSMS V3.6.1
SQLSentry Plan explorer

Problems:

SQL Pretty Printer does not even install it was throwing error like

But the SQL Pretty Printer site says it works on SQL Server 2016 you can check the link.

Next SQLSentry Plan explorer was installed successfully but the plugin was not showing up in SSMS.

Version: v2.8 (Build 9.0.9252.0)

Can anyone help me out with this thing. Note both worked perfectly in SQL Server 2014.


texts: SSIS package to connect to HTTP source and download file

texts:
I'm new to SSIS.  I can see "FTP Task" in the SSIS toolbox in VS2015 Community edition.  Is there an HTTP equivalent?

I have a HTTP url and some user credentials.  Shared via the HTTP url is a .sql file.  I want to login and download the file every Sunday @ 1am.

Can anyone point me in the right direction please?


texts: Database under heavier load -- all SQL queries start timing out after a period of hours/days

texts:
I make a web application which has experienced heavy growth in the past week and it appears my database is seizing up because of it.

You can see that everything seemed to be OK until about 3AM this morning and then all of my requests started timing out. This isn't the first time this has happened, but after I push new changes to the server it starts up OK again. It doesn't seem to happen under low load, but has also ran at high load for hours perfectly fine. I don't think 3AM is an especially high load time.

Things I have done / to note:

Dedicated server, MSSQL and I'm using NHibernate as an ORM to generate my queries.
The server is pretty much just a data store. 99% of the queries are very basic insert/selects. I have one bulk insert for creating a large amount of playlist items.
I have batching enabled. It is set to 50 by default and 200 if >1000 playlist items are about to be  written to the database. This reduced the spikes in my DB's response times.
I have my command timeout set to 5 seconds. It was set at the default, 30 seconds, and I was still experiencing the issue. The problem seems to have stayed away for a longer period of time after setting it to 5 seconds.
I have tried running exec sp_updatestats as described here: https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation# which did show some indexes being updated, but did not result in any real differences.
I have set my databases connection isolation level to ReadUncommitted: configuration.SetProperty("connection.isolation", "ReadUncommitted");
I specify the default DB for NHibernate performance as described here: configuration.SetProperty("default_schema", "[db896d0fe754cd4f46b3d0a2c301552bd6].[dbo]");

What on earth should I be looking at to try and get a grip on what is causing my database to lockup? It's showing only 1% CPU usage so I don't think it is overly taxed. It shouldn't be a long running query if I have 5 second caps on everything. It's not the indexes. I'm running out of ideas.

In terms of queries -- it's all SELECT user statements. It's just a bunch of people trying to login and access their account. I'm getting about 1000 requests / 30 minutes for SELECT user.

Here's some more info:

@@VERSION:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (X64)   Nov 30 2012
  17:11:43   Copyright (c) Microsoft Corporation  Web Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)


texts: Make sure MySQL is not accepting remote connections

texts:
I am currently doing development for a LAMP-based website. I do not know what configuration changes have been made to MySQL since it was installed on our server. The way that we use MySQL, there is no reason for our database to accept any remote connections; it only needs to be accessed locally either via PHP or through the command-line shell over an ssh session.

For security reasons, I want to make sure that there is no way to connect to our database remotely. What settings do I need to check to make sure that this is the case? Is there a single option somewhere that I can set to prevent all remote connections?


texts: Representing a many-to-one relationship in a database diagram

texts:
I have two tables loadingDetail, and stowageDetail:

loadingDetail           | stowageDetail
[PK] loadingID (int)    | [PK] StowageID (int)
[PK] transactionID (in) | [PK] room (int)
                        | [UNIQUE] transactionID (int)

transactionID in loadingDetail can be duplicated when the loadingID value is different.
transactionID in stowageDetail is UNIQUE.
many transactionID values in loadingDetail can have only one transactionID in stowageDetail.
transactionID in loadingDetail can be deleted when there is no transactionID in stowageDetail (No Action)

How I can represent that in a database diagram?

Additional information

I'm really looking for a database-diagram possible answer. But I tried to find something about many-to-one relationships in database diagrams and it seems can't find it. The relationship is like many Containers put inside Big Box. The Containers just can removed if the Big Box is already removed. The supported database diagram "I know" is one-to-many which in my condition does not fit because the Box is removed the first, then Containers the second.


texts: How to define User/Permission relationship as M:M?

texts:
The Resource relates to Action as M:M via Permission join table/separate entity. User can have many permissions. The same permission may be assigned to multiple users.

How to define User-Permission relation as M:M? How would it look like in EF?


texts: Is it possible to recover deleted tables?

texts:
One user deleted some tables from the database. Fortunately after that the base was not compressed and did not recover.

Now I have a question. Is it possible to restore these tables, with all the contained data? Sorry if the question is "childish" but although I thought I had seen a similar topic, I searched but can't find anything.


texts:
This is mostly likely not a SQL server issue but the setup seems to only be affecting BULK INSERTS to SQL Servers.

We have recently moved VM Hardware and all the guests that were moved had their virtual switches changed from standard to distributed.

I then started receiving
A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0)
on a two SQL servers during BULK INSERT operations. One of the SQL servers was a VM with the new configuration and the other was a physical server. Both BULK INSERT operation originated from a VM with the new configuration. The BULK INSERTs would not fail every time, it was very random when it would.

When we changed the virtual switch to be a standard switch instead of a distributed switch the issue goes away.

I am looking for more of an explanation to why it doesn't work with a distributed switch instead of a resolution. My guess would be that the BULK INSERT operation is serial and with a distributed switch the packets are being routed through different hosts, some of which may be busier than others, and are arriving at the destination server beyond some latency threshold. (note: there is nothing in the windows event log at the times of the errors on either the source or destination server)

https://www.sqlservercentral.com/Forums/Topic1394090-391-1.aspx


texts:
I am using the below to return the hh:mm difference between two date ranges. How would I update this code to minus the hours by 5?
CURRENT CODE = to_char(resolved_datetime-opened_datetime, 'hh24:mi')
CURRENT RESULTS = 06:35
NEW RESULTS = 01:35
resolved_datetime timestamp
opened_datetime timestamp


texts: What are your use-cases for HandlerSocket?

texts:
There's a bit of hype around: HandlerSocket

It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in.

My question is, what production use-cases make sense to bypasses the libmysqld layer of MySQL to utilize the HandlerSocket plug-in? Are you deploying it into production?


texts: ODBC Driver "Oracle in OraClient11g_home1" required but can't seem to get it on Windows 10

texts:
Not sure if this was the right StackExchange for this. My job recently upgraded their machines to Windows 10 and I thought setting them up would be a breeze, but I can't for the love of me figure out how to get the correct ODBC drivers on it. I've tried every download I could find and it isn't giving me the right driver.

Not sure where to get the drivers I need. I thought it'd be simple but I just can't find it anywhere after hours of searching. I don't know enough about database connections to know if I can just utilize one of these drivers and somehow translate the configuration from before onto them (old driver just had main entries for Data Source Name, Description, TNS Service Name, and User ID - that's all I enter to make the connection work.)


texts: PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves since fewer disk blocks need to be read in a given query. Does Oracle have anything similar? Would it even help performance on a large table that is almost never updated if there is such an option?


texts:
I have some automated backups that i want to disable. but i do not know how to do it. Apparently these are done via Volume Shadow copy Service  .

Can anyone tell how to get rid of these backups ?


texts: Limiting the occurence of a value in PostgreSQL

texts:
Using PostgreSQL 11.7 and I have to model some real-world constraints. Effectively, I'm modeling a graph and an edge, of course, has two nodes. Each node can have zero, one, or two edges. Thus, I need a constraint similar to:

node UUID NOT NULL CHECK (SELECT true FROM this_table WHERE node = NEW.node HAVING COUNT(*) <= 2)

Except that this syntax is illegal. I also have the following problem since this applies to four of our tables:

I don't know the table name up front
I don't know the column name up front
I don't know the allowed number of occurrences up front

I almost have it working, but I can't figure out how to extract the value of the NEW.node. I have written a trigger that's close, but no cigar:

CREATE DOMAIN uint2 AS int4 CHECK(VALUE >= 0 AND VALUE < 65536);

CREATE OR REPLACE FUNCTION limit_count() RETURNS TRIGGER AS $$
DECLARE
    total       integer;
    this_table  text := TG_TABLE_NAME::regclass::text;
    this_column text := TG_ARGV[0]; -- first argument (triggers cannot define arguments directly)
    this_limit uint2 := TG_ARGV[1]; -- second argument
BEGIN
    IF cardinality(TG_ARGV) <> 2 THEN
        RAISE EXCEPTION 'limit_count() trigger expects a column name and a limit';
    END IF;

    EXECUTE FORMAT('SELECT COUNT(%I) FROM %I WHERE %1$I = $1', this_column, this_table)
       USING NEW.quote_literal(this_column)
       INTO total;

    IF total + 1 > this_limit THEN
        RAISE EXCEPTION '%.% has % occurences, but must not have more than %', this_table, this_column, total, this_limit;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fibre_route_edge_start_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('start_node', 2);
CREATE TRIGGER fibre_route_edge_end_node_limit_trigger BEFORE INSERT OR UPDATE ON fibre_route_edge
    FOR EACH ROW EXECUTE PROCEDURE limit_count('end_node', 2);

The above fails with:

ERROR:  schema "new" does not exist
LINE 1: SELECT NEW.quote_literal(this_column)
               ^
QUERY:  SELECT NEW.quote_literal(this_column)

Happy to solve this problem without a trigger, but I'm kinda stumped right now. And examples I've seen using hstore and other tools have been incomprehensible to me.


texts:
I am running a VPS with 1024 mb ram that is hosting a single WordPress site. Lately I have been having a lot of database problems where WordPress is giving me "Error establishing a database connection". I can make the server crash just by crawling it with Screaming Frog SEO tool.

Sometimes the issue resolves itself, sometimes I need to manually restart MySQL, and other times I have to completely reboot the server.

I thought the error was related to MySQL itself, but after examining the various logs, it seems like my server is running out of memory and therefore simply killing MySQL.

Using MySqltuner, I am getting the following results:

[--] Physical Memory     : 992.6M
[--] Max MySQL memory    : 804.0M
[--] Other process memory: 306.2M
[--] Total buffers: 225.0M global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 409M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 643.1M (64.79% of installed RAM)
[OK] Maximum possible memory usage: 804.0M (80.99% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

Settings in my.cnf:

[mysqld]
max_allowed_packet=268435456
open_files_limit=10000
innodb_buffer_pool_size = 128M
innodb_log_file_size = 128M
key_buffer_size = 64M
innodb_file_per_table
slow_query_log=1
slow_query_log_file="/var/log/slowqueries"
long_query_time = 3
general_log=1
general_log_file="/var/lib/mysql/generalqueries"

Would really appreciate some suggestions as how to proceed from here to figure out what is consuming my memory and causing the issues.


texts: How mongoDB projection affects performance?

texts:
From MongoDB documentation it is mentioned that:

  When you need only a subset of fields from documents, you can achieve
  better performance by returning only the fields you need

How filtering fields affect performance? Is the performance related to the size of data that is transmitted over network? or the size of data that will be hold in memory? How exactly this performance is improved? What is this performance that is been mentioned in documentation?

I have slow MongoDB queries. Is returning a subset affect my slow query (I have compound index on the field)?


texts: Minimum value within a set (or window) within a three column table

texts:
I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012.

I have a table that looks like this:

I would like to retrieve a result set like this, where each category has the ID associated with the lowest order number, and also the ID associated with the highest order number:

I am looking for the TSQL select statement that would do this.


texts: SQL Login for SSRS Reports require EXECUTE permissions?

texts:
Recently my team did a SSRS report deployment.The reporting server was installed in application server and it was pointed to database server.I don't know why we do like that?Probably to avoid load on the database server.

Usually the team just deploy .rdl files to the reporting server and it usually works.But recently when the team did SSRS reports deployment,the reports was not running when checked.
So on checking at the report manager url,i can see the  deployed report.
Later it was identified that the SQL Report login requires EXECUTE permission.
Till now no other reports required that permission,this new report asked for this permission.Why did it happen?
I know that EXECUTE permission is required to run the sp's.
Is it necessary to give EXECUTE for SSRS SQL logins

Additional Detail : The fix given was to give EXECUTE permission for the user.But what confuses me is that ,all of a sudden the SSRS sql login requires EXECUTE permission.For none of the other reports which runs of sp's never asked for EXECUTE permission.


texts: I want to select all records for those persons who have one or more entries that equals zero dues

texts:
DUES_TABLE
ID       LNAME   FNAME   DUES    DATE
3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01

9450     Jones   Jane    12.50   2014-10-01
9450     Jones   Jane    12.50   2014-11-01
9450     Jones   Jane    12.50   2014-12-01

6074     Suggs   Tom     12.50   2014-10-01
6074     Suggs   Tom      0.00   2014-11-01
6074     Suggs   Tom     12.50   2014-12-01

I want to output all records, whether dues were paid or not, for those persons who have one or more zero dues entries, like so:

3456     Smith   Bob     12.50   2014-10-01
3456     Smith   Bob      0.00   2014-11-01
3456     Smith   Bob     12.50   2014-12-01
9450     Suggs   Tom     12.50   2014-10-01
9450     Suggs   Tom      0.00   2014-11-01
9450     Suggs   Tom     12.50   2014-12-01

Notice that Jane Jones is excluded because she paid all her dues. And Both Bob Smith and Tom Suggs have their zero and positive dues entries listed.

Anyone have an idea how I can achieve this result?


texts: Making ordinal synonyms for Postgres full text search

texts:
I have a Postgres 9.3 database where users are mostly searching for locations by a combination of name and/or address (or fragments of).

An example of an address would be "123 8th st".

I've been able to setup a synonym dictionary that allows users to find such an address with "123 8th street", but I can't seem to get it to do the same for ordinals. That is, I want a search for "123 8 street" (etc) to be able to find this address.

I'm using the following code:

CREATE TEXT SEARCH CONFIGURATION my_app_english (
  COPY = english
);

CREATE TEXT SEARCH DICTIONARY my_app_synonyms (
  TEMPLATE = synonym,
  SYNONYMS = my_app_synonyms
);

ALTER TEXT SEARCH CONFIGURATION my_app_english
  ALTER MAPPING FOR asciiword
  WITH my_app_synonyms, english_stem;

And the synonym file had lines like:

1st 1
2nd 2
3rd 3
4th 4
5th 5
...

How can I make this full text search match "8th" when the user searches for "8"?

I suspect the token type is the issue, but am not sure how I can consistently treat the token with the ordinal like that without.


texts: What are the disadvantages if indexes are added on all columns?

texts:
I know, that it is not a good design to set too many indexes, but just for understanding the theory:
What exactly are the disadvantages if you add an index on all columns in a table in MySQL?

Are the queries getting slower if I have indexes on columns, that i
rarely use?
Are the inserts somewhat slower?
Is MySQL able to pick out the best index that speeds up the query?


texts:
Let's say I have 2 nodes in my replica set with an arbiter running. Now when it comes to adding a new member to an existing replica set while it is still running for production, what steps do I need to do to make sure the data in new node will be in sync with other nodes? Especially when the new node is a fresh mongo instance without any data.

Do I just rs.add() new node to replica set and the data will be populated automatically? Or do I need mongodump latest data from current node then restore it to new node and do rs.add() to let it sync anything left behind?

I am thinking to use the same strategy when I need to upgrade the mongo machine ie. prepare 2 new machines with mongo instance, add them to replica set, let the data sync, remove mongo connection to the 2 old machines, remove 2 old machines from replica, shutdown old machines leaving me with 2 new upgraded machines. Would that be the best practice while running in production to avoid 0 downtime?


texts: Database Design Review - How to model a business domain about posting groups?

texts:
I am building a website as a personal project just for some experience in a new web framework and want to make sure I model my SQL database in a good way with the appropriate relationships before building it out and getting myself in too deep. I'm coming over from using MongoDB so this will be my first application in which I have to model the SQL database.

This application will allow users to join and create groups for discussion, add categories to the groups and then post topics to a specific category in a group.

My application will have four main things: Users, Groups, Categories and Topics (assuming no replies to topics, just a simple posting for users to read).

My current advance

This is what I have so far (FK = Foreign Key):

User

id
username
password

Group

id
title
description
owner (FK user-id)

Category

id
title
group (FK group-id)

Topic

id
title
author (FK user-id)
category (FK category-id)

Membership

id
group (FK group-id)
user (FK user-id)

Responses to comments

The rules are:

A user can own/create multiple groups and can join multiple groups
A user can create multiple categories within the group that they own/created
Members of a group can create multiple topics within a category
There can't be duplicate categories within the same group

I am in no capacity a database expert and took one course on database a year ago so any feedback is appreciated


texts: OLA Output File Cleanup - Better Options

texts: Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell script may work but I really don't know where to start. Any help, even just a simple point in the right direction, would be greatly appreciated.


texts:
SELECT j.job_id,j.last_updated,j.job_title,j.status,
(SELECT COUNT(*) FROM candidates WHERE job_id=j.job_id) resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=1) auto_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=0) self_resumes_count,
(SELECT COUNT(*) FROM candidates c WHERE job_id=j.job_id AND c.ref_type=-1) paging_resumes_count,
(SELECT SUM(views) FROM job_views WHERE job_id=j.job_id) job_views,
(SELECT posts FROM autopost_jobs WHERE job_id=j.job_id) autopost_count
FROM jobs j WHERE j.user_id={$userId} ORDER BY j.last_updated DESC,STATUS

Table structure:

| jobs  | CREATE TABLE `jobs` (
  `job_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL,
  `job_title` varchar(450) NOT NULL,
  `job_desc` varchar(5000) NOT NULL,
  `sjob_data` text NOT NULL,
  `job_requirements` varchar(5000) DEFAULT NULL,
  `years_of_experience` varchar(100) NOT NULL,
  `location_id` int(11) unsigned NOT NULL,
  `department_id` int(11) NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  `job_company_name_alt` varchar(100) DEFAULT NULL,
  `is_show_company` tinyint(1) NOT NULL DEFAULT '1',
  `status` tinyint(2) NOT NULL DEFAULT '0' COMMENT '0=inactive,1=active,2=deleted',
  `job_reward` varchar(128) DEFAULT NULL,
  `job_lang` int(11) NOT NULL DEFAULT '0' COMMENT '0 - Ru 1 - En',
  `job_photo` varchar(100) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `is_hot_job` tinyint(1) NOT NULL DEFAULT '0',
  `job_internal_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1794 DEFAULT CHARSET=utf8 |

| candidates | CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) DEFAULT NULL,
  `job_id` int(10) unsigned NOT NULL DEFAULT '0',
  `through_user_id` int(11) NOT NULL,
  `ref_type` tinyint(4) NOT NULL COMMENT '0 = personal , 1 = automatic , 2 = file uploaded , 3 = lead sent',
  `media_id` tinyint(2) NOT NULL DEFAULT '-1',
  `status_id` int(11) DEFAULT NULL,
  `cv_filename` varchar(250) DEFAULT NULL,
  `lead_url` varchar(256) DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `is_viewed` tinyint(1) NOT NULL DEFAULT '0',
  `is_hired` tinyint(1) NOT NULL DEFAULT '0',
  `is_employee_note` tinyint(4) NOT NULL DEFAULT '0',
  `note` varchar(1000) NOT NULL,
  `last_updated` datetime NOT NULL,
  `device_type` int(1) NOT NULL DEFAULT '0' COMMENT '0 - web, 1 - mobile',
  `third_party_approval` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `setIndex` (`user_id`,`job_id`,`through_user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=20012 DEFAULT CHARSET=utf8 |
| autopost_jobs | CREATE TABLE `autopost_jobs` (
  `job_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `posts` bigint(20) DEFAULT '0',
  `media_id` int(11) NOT NULL,
  `last_user_id` int(11) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `inserted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
| job_views | CREATE TABLE `job_views` (
  `user_id` int(11) NOT NULL,
  `hr_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `media_id` int(11) NOT NULL,
  `post_type_id` int(11) NOT NULL COMMENT '0 - self, 1 - auto, -1 - undefined ',
  `date` date NOT NULL,
  `views` bigint(20) NOT NULL DEFAULT '0',
  `last_time` time NOT NULL,
  PRIMARY KEY (`user_id`,`hr_id`,`job_id`,`media_id`,`date`,`post_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

This query just hangs, what could be the reason?, how can I optimize it


texts: Query that displays backup information (successful and failed) SQL Server

texts:
I have two jobs that backs up two different databases.
Job 1 backs up DB1
Job 2 backs up DB2
DB1 fails to back up due to low space on Drive 1 resulting to a Job 1 failure. To fix that issue, I just had to add space. No biggie. I was told about this today when the issue has been occuring for a month . Yeah I know it's crazy but it's dev

I want to get a complete backup history for DB1. I know that I can retrieve successful backup information from the msdb.dbo.backupset table but I want to know if there is a query that displays failed backups for a database.

My query below displays backup history for a particular database from 12/31/13-1/27/14. Info includes server, database name, Backup start and end times, Total time it took for the dbs to be backed up, db size and backup set name.

SELECT
   distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
 CAST((DATEDIFF(second,  msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)) AS varchar)+ ' secs  ' AS [Total Time] ,

   Cast(msdb.dbo.backupset.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)',
   msdb.dbo.backupset.name AS backupset_name
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--Enter your database below
--and database_name = 'db_name_here'
and msdb.dbo.backupset.backup_start_date>'2013-12-31' and msdb.dbo.backupset.backup_start_date<'2014-01-27 23:59:59'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date

Is there any way to get that information by modifying my code? I'm able to retrieve JOB1's history by executing a sql statement that runs against the sysjobhistory and sysjob table. This may be a long shot Is there a way I can use the sysjobhistory, sysjob, backupset and backupsetmediafamily tables in msdb to produce the results I want?


texts: Is it possible to restore a database in a mirroring configuration?

texts:
Or will I have to break the mirror, restore the database, then rebuild the mirror?

We're in the process of upgrading our SharePoint servers.  We already have two new  servers in place and they are currently mirrored.  If possible, I'd like to leave the servers in a mirrored state to save time during our maintenance window.