texts: Optimal data type for storing numbers in SQL Server

texts:
Similar to varchar that stores strings of varying length, is there an alternative for numbers of varying length that allocates space depending on the size of the number?

I have a large table with 50 columns holding about a billion rows including 15 numeric(10) columns. My quick proof of concept with 4 million showed that I can save a bit of disk-space by reducing the size of these 15 columns, hence this question. Please advise.

POC details - I reduced the column size from numeric(22) to numeric(5) for 15 columns with 3.5 mil rows and disk usage dropped from 1398.02 MB to 981.23 MB.
I will have to push this up to numeric(10) as size 5 will not suffice.

Note: numeric(22) was a bad design I inherited. Changing to integer data type reduced disk usage even further - 907.27 MB. Integer should help if our data model standards team allows this [since it contains no precision]. Will keep posted.

The server version and edition is

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17
  2016 19:14:09 Copyright (c) Microsoft Corporation Standard Edition
  (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts: Query to find the second highest row in a subquery

texts:
The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system.
This is the simplified query that I'm using to get the data that I need. There are a few more columns in the original query and this subquery for threads is kind of required so I can also identify if this is a new ticket or if existing one was updated (in case of update, the role for the latest threads will be a customer):
SELECT t.ref, m.role
  FROM tickets t
  LEFT JOIN threads th ON (t.id = th.ticket_id)
  LEFT JOIN members m ON (th.member_id = m.id)
 WHERE th.id IN ( SELECT MAX(id)
                    FROM threads
                   WHERE ticket_id = t.id
                )

It will return a list of tickets so the app can send notifications based on that:
+------------+----------+
| ref        | role     |
+------------+----------+
| 210117-001 | customer |
| 210117-002 | staff    |
+------------+----------+

Now, I want to send only a single notification if there a multiply consecutive updates from the customer.
Question:
How I can pull last and also one before last row to identify if this is consecutive reply from the customer?
I was thinking about GROUP_CONCAT and then parse the output in the app but tickets can have many threads so that's not optimal and there are also a few more fields in the query so it will violate the ONLY_FULL_GROUP_BY SQL mode.
db<>fiddle here


texts: CREATE DATABASE permission denied for sysadmin

texts:
I'm attempting to create a stored procedure that can be used as a proxy for creating databases where users do not have rights for creating databases.  See Restricting Database Access/Functions to User Groups? for more info

When I attempt to execute the stored proc, SQL Server generates a security error:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

This is the code:

CREATE LOGIN DatabaseCreator WITH PASSWORD='Pa$$w0rd'; /* REPLACE WITH A SECURE PASSWORD! */
GO
CREATE USER DatabaseCreator FOR LOGIN DatabaseCreator;
GO
EXEC sys.sp_addsrvrolemember 'DatabaseCreator','sysadmin';
GO
CREATE PROCEDURE dbo.CreateDatabase
(
    @DatabaseName SYSNAME
)
WITH EXECUTE AS 'DatabaseCreator'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(max);
    IF COALESCE(@DatabaseName,'') <> ''
    BEGIN
        SET @cmd = 'CREATE DATABASE ' + QUOTENAME(@DatabaseName) + ';'
        EXEC sys.sp_executesql @cmd;
    END
    ELSE
    BEGIN
        SET @cmd = 'INVALID DATABASE NAME';
        RAISERROR (@cmd, 0, 1);
    END
END;
GO
EXEC master.dbo.CreateDatabase 'MyDatabaseName';

msg 262 appears to be a generic Permission Denied message.  Since DatabaseCreator has sysadmin privileges, this should not be happening.  Unless I'm missing something.


texts: Increase the time to live of a cached plan

texts:
I'm looking at the Plan Cache in SQL Server. I've got a simple stored procedure:

CREATE PROC [dbo].[CountDescriptions](@Description varchar(1))
AS
    SELECT ID
    FROM MyTable
    WHERE Description = @Description
GO

If I execute it and immediately check the cache it's generally there, but it seems to expire very quickly and get removed.

This is making it a bit of a pain to play with.  Is there a way to artificially change a plan's details to stop the garbage collector (not sure if that's the right word for it) from deleting it so quickly?

Maybe increasing it's cost to compile or something?


texts:
I am new to PostgreSQL. The situation I have is someone created a child table inherits from the parent table. And dropped the child table. But the parent table still has the "down arrow" sign on it.

I checked and there's no other link/relation on the parent table. Is this really an issue?

I was told that the parent table is still in the 'been inherited' status and causing the performance issue. How to resolve this? By removing the 'been inherited' status' from the parent table?


texts: why do i have that unnatural process on my server

texts:
i have an abnormal process on my RedHat server i tried to kill it but it appears again and because of that process i can't start my fusion middleware
any ideas please
when I execute the command TOP that line appear
pid     user               cpu   command
31183   oracle            598     .


texts: What is the preferred / common way to separate "access-tokens" in a database design

texts:
I'm currently having a case where a user has to verify their email, is able to reset his password and can log into a web-interface (so far and not limited to). There a now 3 places where some kind of token is used to give a user access to a specific action.

Web session - Access token used to access API endpoints.
Email verification - Access token used to verify a (new) email-address.
Password reset - Access token used to change a password.

So far I've come up with two possible solutions:
a) A separate table for each token:
There will be a web_session, email_verified and password_reset table.
Pros:

Each table can come with meta-unique properties
You can easily grab data from on of these tables based on their name

Cons:

All tables are kinda related but have very different names (and I couldn't come up with a naming-way to "group" them)
Almost all columns are equal across these tables (id, user_id, token, created_at)
Every time something like this gets added I'd have to create a seperate table with (always / almost?!) the same columns.

b) A unified table with a type / role column:
There's just one table with id, user_id, token, created_at and type (enum) - type can in this case be all sorts of access-type, in our case api-session, email-verification and password-reset. (Of course this could be improved in a way that a token will give general access to a specific thing)
Pros:

It's just one table
Can easily be extended without making it hard to use (I'll always be one table)

Cons:

Another conditional where you'll have to check the type before looking for the token itself
Can get confusing I guess as although it's only one table there are differences based on type

Is there a generally preferred way of doing this (I'm using mariadb if that's important) / is there a third, better way of doing it? Or does it really not matter that much and I'm just overthinking it?
I tried to find existing schemes / db-designs but I couldn't find anything useful as there is afaik no name for this whole thing (please tell me I'm wrong and I just cannot search correctly).


texts:
I've noticed that on a server running SQL Server 2016 SP1 CU6 sometimes an Extended Events session shows a SELECT query causing writes.
For example:

The execution plan shows no obvious cause for the writes, such as a hash table, spool, or sort that could spill to TempDB:

Variable assignment to a MAX type or an automatic statistics update could also cause this, but neither was the cause of the writes in this case.

What else could the writes be from?


texts: Error adding Database to TSA Cluster - db2haicu

texts:
I'm testing TSA Clustering using two test servers. It's replicating our production environment.

I'm using two nodes shared disk topology.

When running db2haicu, I am getting the following error in the last step, that is, when adding the database to the cluster.

Adding virtual IP address 'x.x.x.x' to the domain ...

Adding virtual IP address 'x.x.x.x' to the domain was successful.

The following databases can be made highly available:

 Database: TESTDB

Do you want to make all active databases highly available? [1]

1. Yes
2. No

1

Adding database 'TESTDB' to the cluster domain ...

The db2haicu utility hangs on like this until I kill the process.

When I checked the DB2 diagnostic log, it is showing this entry

2015-12-28-13.21.28.810478+330 I187012E411           LEVEL: Warning
PID     : 6446                 TID : 140568303744800 PROC : db2havend (db2ha)
INSTANCE: db2inst1             NODE : 000
HOSTNAME: xxxxxxxxx
FUNCTION: DB2 UDB, high avail services, db2haAddResource, probe:11286
DATA #1 : <preformatted>
Error adding resource db2mnt-mnt_san_db1-rs to group db2_db2inst1_0-rg, resource handle is NOT valid

2015-12-28-13.30.20.027549+330 E189851E371           LEVEL: Error
PID     : 10262                TID : 140256146859808 PROC : db2haicu
INSTANCE: db2inst1             NODE : 000
HOSTNAME: xxxxxxxx
FUNCTION: DB2 UDB, high avail services, SqlhaUIAppContext::getInstance, probe:100
RETCODE : ECF=0x9000052E=-1879046866=ECF_SQLHA_FAILED
          SQLHA API call error

The path I created for the database /mnt/san/db1.

I have cross checked the pre-requisites for db2haicu clustering multiple times and everything seems fine.

Any clue as to why this is happening?


texts: Slow SQL Server 2008 R2 backups after Windows update

texts:
I recently installed a series of Windows Updates on our SQL Server 2008 R2 server.  Note, these were not SQL Server updates, they were just the usual monthly Windows updates.  These updates included a couple of re-boots, but otherwise this was just routine maintenance.  Since then however, SQL Server has been running at least 4 times slower than usual - backups and queries - and I'm at a loss to figure out why.  Backups which used to take 3 hours now take 20.  I appreciate there are a myriad reasons why queries can be slow on a restart, but backups?

I've heard there were issues with the Balanced Power Plan when upgrading from SP2 to SP3 with SQL Server 2008 R2, but this server has had SP3 installed for over a year now.  I tried changing to the High Performance Plan anyway, but that made no difference to the speed of the backups.

I also checked if there were any pending sp_configure changes that would kick-in (or kick-out) after a restart, but the Error Logs did not show any changes.

Is it possible that the Network Adapters would somehow reset after a re-boot?  One of the Windows updates was for the "Intel Ethernet Server Adapter X520-2".  Could that be an issue?  Would the Jumbo Frames size, or anything other configuration value, reset itself after a driver update?

To give more information about the slow backups, the biggest database I backup is about 3TB, hosted on one Windows drive letter.  That Windows drive is made up of six 820GB spindle drives (strictly speaking, it's twelve drives in a RAID-1 formation).  Previously, this database would backup in about 3.5 hours at a rate of 192 MB/sec.  Now it takes 20 hours at a rate of 32 MB/sec, and I don't understand what's changed.  It seems that in the past, data must have been pulled from all six drives simultaneously during the backup to achieve 192 MB/sec, but now I'm guessing the backup is pulling data from just one drive at a time (checking with PerfMon), hence 32 MB/sec.  Why?  Could this have been caused by a change in the RAID controller, the network adapters, the SQL Server backup command itself, or what?  And is it typical for data to be read/written from an array of drives in a parallel fashion?  If so, what piece of hardware controls this?


texts:
I need to add a column to my table with the count of every occurrence of. So if the value is unique the column value should be 1, but if there is multiple copy i need a differente value for every row (1,2...)
The database is postgres
for example

id
value
count

1
a
1

2
a
2

3
b
1

4
b
2

5
b
3

6
c
1

database example https://dbfiddle.uk/?rdbms=postgres_10&fiddle=0e7132f3f094bd93b390ccae2e811696


texts: What are the current best practices concerning varchar sizing in SQL Server?

texts:
I'm trying to understand the best way to decide how big varchar columns should be, both from storage and performance perspectives.

Performance
From my research, it seems that varchar(max) should only be used if you really need it; that is, if the column must accommodate more than 8000 characters, one reason being the lack of indexing (though I'm a little suspicious of indexing on varchar fields in general.  I'm pretty new to DB principles though, so maybe that's unfounded) and compression (more a storage concern).  In fact, in general people seem to recommend only using what you need, when doing varchar(n)....oversizing is bad, because queries must account for the maximum possible size.  But it's also been stated that the engine will use half the indicated size as an estimate of the average actual size of the data.  This would imply that one should determine, from the data, what the average size is, double it, and use that as n.  For data with very low but non-zero variability though, this implies up to a 2x oversizing over the maximum size, which seems like a lot, but maybe it's not?  Insights would be appreciated.

Storage
After reading about how in-row vs. out-of-row storage works, and keeping in mind that actual storage is limited to actual data, it actually seems to me that the choice of n has little or no bearing on storage (besides making sure it's big enough to hold everything).  Even using varchar(max) shouldn't have any impact on storage.  Instead, a goal might be to limit the actual size of each data row to ~8000 bytes if possible.  Is that an accurate read on things?

Context
Some of our customer data fluctuates a little, so we generally make columns just a little wider than they need to be, say 15-20% bigger, for those columns. I was wondering if there were any other special considerations; for example, someone I work with told me to use 2^n - 1 sizes (I have found no evidence that's a thing though....)

I'm talking about initial table creation. A customer will tell us that they are going to start sending us a new table, and send sample data (or just the first production data set), which we look at and make a table on our end to hold the data. We want to make the table on our end to handle future imports as well as what's in the sample. But, certain rows are bound to get longer, so we pad them.

The question is how much, and are there technical guidelines?


texts:
When I execute this on sql server on another machine (in same sub net) I am getting "Access is denied". But when I did it from command prompt (sc \ start ) it is working.

Please advice why I am getting access denied when I am doing it from sql server using exec xp_cmdshell cmd


texts: How to reference a table from another schema inside a package?

texts:
I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema.

The package has a procedure that uses a cursor. When I create the package I get compilation errors:

  Body  509 30  PL/SQL: ORA-00904: : invalid identifier

The error points to a table that the select of the Cursor is using and that it is located in the other Schema.

If I run the query directly in TOAD it works perfectly, I am new in Oracle and I don't understand why I get this error. Why am I getting this error?


texts: Why would a query work slower when a where clause is added?

texts:
I've got two databases and both have got the same view over the same table which has the same indexes.

The view selects the top location for a given IMEI from a locations table.

CREATE VIEW [dbo].[LatestDeviceLocation]
AS
SELECT DISTINCT t.Imei, t.Accuracy, t.UserId, t.Lat, t.Lng, t.Timestamp
FROM (SELECT Imei, MAX(Timestamp) AS latest
    FROM      dbo.DeviceLocation
    GROUP BY Imei) AS m INNER JOIN
    dbo.DeviceLocation AS t ON t.Imei = m.Imei AND t.Timestamp = m.latest
GO

I'm querying the view with a very simple select with what seems like a very simple where clause.

SELECT TOP 1000 [Imei]
      ,[Accuracy]
      ,[UserId]
      ,[Lat]
      ,[Lng]
      ,[Timestamp]
  FROM [dbo].[LatestDeviceLocation]
  Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z'

On my live server when I query my view I get data back in < 1 second. When I add a where clause Where [Timestamp] > '2015-02-19T00:00:00.000Z' AND [Timestamp] < '2015-02-26T23:59:59.999Z' that jumps up to approximately 1 minute.

On my test server which has 10x more data (350k+ locations shared by approximately same number if Imei numbers as the live site, 25) the query returns data in < 1 second with or without the where clause.

I've looked for locks and can't see any.

I've re-created the index incase it was corrupted and no difference.

I've completely removed the index, performance didn't change.

This is the index that I've used on both servers.

/****** Object:  Index [GangHeatMapIndex]    Script Date: 02/26/2015 22:38:38 ******/
CREATE NONCLUSTERED INDEX [GangHeatMapIndex] ON [dbo].[DeviceLocation]
(
    [UserId] ASC,
    [Timestamp] ASC,
    [Imei] ASC
)
INCLUDE ( [DeviceLocationId],
[Accuracy],
[Lat],
[Lng]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Edit: I've just realised that I wasn't looking in the right place for locks. It is taking out object locks when querying. I'm trying to work out how to write my view with "no lock" built into the view.

Edit 2: I've attached the execution plans, top on is with the index, bottom is without.

Edit 3: More executions plans, this time all on the live server, with the index re-added, with and without where clauses.

Edit 4:

I've changed the view to use a common table expression as follows and the performance is much better.

WITH cte
       AS (SELECT Rank()
                    OVER (
                      partition BY dloc.[Imei]
                      ORDER BY dloc.[Timestamp], devicelocationid DESC) AS arank,
                 dloc.*
           FROM   [dbo].[DeviceLocation] AS dloc)
  SELECT [Imei], [Accuracy], [UserId], [Lat], [Lng], [Timestamp]
  FROM   cte
  WHERE  arank = 1

Including the device DeviceLocationId in the order by prevented any duplicates occurring in the final result.


texts: Error: Invalid object for global temporary table

texts:
I'm new to stored procedure & my application requirement is to collect data in global temporary table & bulk copy using bcp.

I'm calling stored procedure, which copies data into global temporary table. When I execute stored procedure it throws an error

  Invalid object name 'tempdb..qaw_temp'

Code for stored procedure:

CREATE table tempdb..qaw_temp
  (id      char(12),
   i_test    int))

print 'Before Insert'
  insert into tempdb..qaw_temp
  values('0411',
    3077,

print 'After Insert'

This work's if we execute code from Aqua Data Studio. But When I get executed in the stored procedure, it throws above mentioned error.

Stored procedure code

create PROC Xtest
as
Begin
 << Code >>
End

Why does the stored procedure execution fail?

Please help, thank you.


texts: cannot connect my oracle server to developer

texts:
I get the following error: Status : Failure -Test failed: Listener refused the connection with the following error:
ORA-12505, TNS: listener does not currently know of SID given in connect descriptor.  My services are below and my connection window.  I believe the installation of oracle 12c was a success.  Do you guys know what could be wrong, please.

enter image description here


texts: Insert into partitioned tables with select, handle conflicts

texts:
I need to insert into partitioned tables using a select, how do I handle conflicts? Postgres complains this:
[0A000] ERROR: ON CONFLICT clause is not supported with partitioned tables

My query looks like this:

INSERT INTO table_partition SELECT * FROM old_table WHERE start_time >= '2014-12-01 00:00:00' AND start_time < '2015-01-01 00:00:00' ON CONFLICT ON CONSTRAINT table_partition_201412_pkey DO NOTHING ;

Currently, I have created the partition tables with the old table having triggers to copy insert/update/delete onto the partition tables. I've to backfill the older data onto partitions, once done, I can drop the older tables and start using the partitioned tables, but there's a chance some of the data with few date ranges might overlap. It's a problem for me. Could you please help?


texts: Does the order of columns has a significant effect on INSERT?

texts:
Do we experience a noticeable difference in performance for these two queries?

INSERT INTO table (col1, col2, col3, col4, col5) ...

and

INSERT INTO table (col5, col3, col1, col2, col4) ...

Do we need to arrange INSERT columns according to the order of columns in the table?


texts: Accessing ORA_ROWSCN in the triggers body

texts: Is it possible to access ORA_ROWSCN pseudo-column in the body of BEFORE triggers? I'm thinking about storing this value in the table itself to emulate SQLServer rowversion behaviour.


texts: Design for hierarchical embedded database

texts:
The goal is to construct a hierarchical database of Objects having the following format:

TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.]
TypeB -> ObjectB [values, contains ObjectC*, ObjectD*, etc.]
TypeC -> ObjectC [values, ...]
TypeD -> ObjectD [values, ...]
TypeE -> ObjectE [values, ...]

edit: Each type can contain multiple objects of other types. eg. ObjectA can contain Multiple typeB, typeC. etc.

It is fixed and known what kind of values and objects each Type[A,B,C,...] may contain.
 Select or searches can be on on all objects of a certain type, and the system would construct an object containing all the sub-objects referenced by that object.
Example: select all objects of TypeA -> returns :

ObjectA {
  values,
  ObjectB { values, ObjectC, ObjectD, etc. }
  ObjectC { values, ... }
  ...
 }

Doing this would mean a large number of nested select queries on the table. This being an embedded database, it is needed that the number of queries be minimum and there be no large joins, is there any way to partition the table or create indexes or a design principle that would help in optimizing the queries?


texts: mysql trigger to trigger only if record doesn't exist

texts:
The following trigger :

DELIMITER //
CREATE TRIGGER auo_mtltp  AFTER UPDATE ON master_table_linfomasltp2016
FOR EACH ROW
BEGIN
            IF (NEW.IMHQ_solicitado = 'SI')
            THEN
            INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad,n_marcadores_solicitados)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad,OLD.n_marcadores_solicitados);
            END IF;

            IF (NEW.FISH_solicitado = 'SI')
            THEN
            INSERT INTO fish (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad);
            END IF;
    END//
DELIMITER ;

Adds a new row into a table if x condition is met. How ever, ID_CASO is a unique index into the child table, and like this, if i update a record already into the child table, it gives me an error, since there is another trigger running that adds the record to the child table if the x condition is met since insertion.

I need this trigger to run only IF the ID_CASO value is not present in the child table.

Any ideas?

I tried IF EXISTS syntax, but no succes so far.

Also, I thought that if you clarified the "NEW." prefix into the row identifier it would only fire the trigger if the value is NEW, but if the value is OLD and there is no NEW value being updated, it uses the OLD one and tries to add the row into the child table. As in, it just runs the trigger again, even if the value was OLD and not NEW.


texts: Using SQL Snapshot Replication to refresh TEST databases from PRODUCTION. Why not?

texts:
What are the drawbacks and downsides?

We want to schedule automatic nightly refreshes of 20 databases, approximately 1GB each, from the PRODUCTION server to the TEST server.  We're setting up SQL Server snapshot replication to publish snapshot updates from the PRODUCTION databases to TEST.  Are there gotchas we should know about?


texts: Pass Variables to SQLDeveloper Report in anonymous block

texts:
I can pass variables to anonymous block of this form
SQL> set serveroutput on;
SQL> DECLARE
a number := &i_nr;
BEGIN
.....

But it doesn't works into SQLDeveloper PL/SQL DBMS Output (User Defined Reports). In a not PL/SQL report in Developer  alway use ":variable" but in pl/sql type it doesn't works
How can I solve this problem?
Thanks.


texts: Single slave for multiple masters in AWS AuroraDB

texts:
We are planning to use AWS Aurora(MySQL version) as our relational database. There will be three masters in different regions. We need the data from these masters replicated in a single slave. I think in SQLServer, this is possible using log shipping. Aurora MySQL also has bin log shipping but I am not seeing any way to push the logs from different masters in a single slave.

Please advise if anyone has come across and solved this situation.


texts:
We had an AlwaysOn Async replica
we had some problems so we deleted the availability group
After that the database and CPU seem to work abnormally
It's very slow and we have waittype = LATCH_EX , ACCESS_METHODS_DATASET_PARENT
We didn't have such a problem till today
Can anybody help ?????


texts: MongoDB sharding and zone tag that has a range of 1 key value

texts:
Suppose I have a zone tag that has a range of exactly 1 key value, such as the letter A. Further, suppose I assign this zone to two shards. Will the data with a shard key of A be put on both shards?

My understanding leads me to say no because it will put the initial chunk on one of the two shards and since that chunk cannot be split because it encompasses only a single shard value, this means that a migration will never happen, so the other shard will sit empty.


texts: Is my schema for a Discussion forum using Cassandra optimum or has a scope for improvement?

texts:
I am designing a Discussion Forum for my app which has following primary objects:

Image
Video
Text
Audio

The owner can start a discussion in context of any of the above object. Based on user permissions, she can respond to a post up to level 3. The owner of the Post can block a user from responding and a user can unsubscribe to a Post. User can also upvote/downvote a Post.
Consider the following example in context of a Video:

Post

Response 1

Comment 1

Response 2

Comment 2

My potential DB SELECT queries are as follows:

Get all/limited Posts of an Object.
Get all/limited Responses of a Post.
Get all/limited Comments of a Response.

I have come up with the following schema:

Here K means Partition Key and C means Clustering Key.

Is this schema optimum for the current use-case or there is a scope for improvement?


texts: Transaction Log Backup Maintenance Plan and Newly Restored Databases

texts: When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a Full backup each night. We also have a maintenance plan that runs a transaction log backup every 30 minutes. How do I stop the latter failing whilst we are waiting for the first Full backup to complete? I’m thinking I maybe cannot achieve this with maintenance plans but need a bespoke agent job?


texts:
Typically, when one of the developers or data analysts needs to perform a very large update or deletion of data (where a truncate or truncate/insert wouldn't make sense because the data set to keep is too large) I recommend to them to do something like the following:

-- Delete 1 million rows 1 thousand at a time
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
GO 1000

The result of this on databases that are in full recovery mode is that 1) the wait allows other transactions to process if needed and 2) when the log backup runs it is able to mark the operations already completed as dirty in the log file so it can re-use the space and prevent the log from growing too rapidly.

Rather than doing this, I'd like to know if it's possible to accomplish the same thing using checkpoints.  Would this statement effectively result in the same situation from happening?

-- Delete 1 million rows 1 thousand at a time
WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> )
BEGIN
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
CHECKPOINT
END

Again, these are databases in full recovery mode.


texts: Is there a way to optimize this using indexes?

texts:
I'm just now learning indexes, and I was wondering if it's possible to optimize this code using them. Currently this code runs in 1.4 seconds.

SELECT
    C.yearID as year,
    name as teamName,
    C.lgID as league,
    D.cnt as totalBatters,
    C.cnt as aboveAverageBatters
FROM
    (SELECT
        count(masterID) as cnt, A.yearID, A.teamID, A.lgID
    FROM
        (select
        masterID,
            teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    GROUP BY teamID , yearID , lgID, masterid
    HAVING sum(AB) <> 0) B, (select
        teamID,
            yearID,
            lgID,
            sum(AB),
            sum(H),
            sum(H) / sum(AB) as avg
    FROM
        batting
    WHERE ab is not null
    GROUP BY teamID , yearID , lgID
    HAVING sum(AB) <> 0) A
    WHERE
        A.avg >= B.avg AND A.teamID = B.teamID
            AND A.yearID = B.yearID
            AND A.lgID = B.lgID
    GROUP BY A.teamID , A.yearID , A.lgID) C,
    (SELECT
        count(masterID) as cnt, yearID, teamID, lgID
    FROM
        batting
    GROUP BY yearID , teamID , lgID) D,
    teams
WHERE
    C.cnt / D.cnt >= 0.75
        AND C.yearID = D.yearID
        AND C.teamID = D.teamID
        AND C.lgID = D.lgID
        AND teams.yearID = C.yearID
        AND teams.lgID = C.lgID
        AND teams.teamID = C.teamID

I've tried creating indexes for teamid, yearid, lgid, and ab, but performance is the same.

Please help!


texts:
I want to make a copy of database in the same SqlServer .
so, When i using the Copy Database Wizard, it throw error:
( I did this steps with a test DB and it works fine !!!!)

config:

  sa user

  Method:"Use the SQL Management Object method"

  Chose new name for destination database.

error:

   TITLE: Copy Database Wizard

  The job failed.  Check the event log on the destination server for
  details.

  ------------------------------ BUTTONS:

  OK

in event log :

  System

  Provider

  [ Name]  SQLSERVERAGENT

  EventID 208

  [ Qualifiers]  16384
   Level 3
   Task 3
   Keywords 0x80000000000000

  TimeCreated

  [ SystemTime]  2014-05-07T06:23:11.000000000Z
   EventRecordID 123672
   Channel Application
   Computer Server1
   Security
  EventData

  CDW_Server1_Server1_3     0x666DE807F406D7438C65B09171211D7B
  Failed     2014-05-07 10:52:50     The job failed. The Job was invoked
  by User sa. The last step to run was step 1
  (CDW_Server1_Server1_3_Step).

last lines of log file:

  OnProgress,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,Database transfer failed for 1
  database(s). OnProgress,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,100,0x,Transfer objects finished
  execution. OnTaskFailed,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null)
  OnPostExecute,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) OnWarning,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,-2147381246,0x,SSIS Warning Code
  DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but
  the number of errors raised (1) reached the maximum allowed (1);
  resulting in failure. This occurs when the number of errors reaches
  the number specified in MaximumErrorCount. Change the
  MaximumErrorCount or fix the errors.

  OnPostExecute,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) DiagnosticEx,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,104546304
  PackageEnd,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,1,0x,End of package execution.


texts: Query not excluding right amount of records using in clause

texts:
I'm trying to find excluded records from one table from another.

select old_fy.*
from old_fy
where "old_count_or_not" = "Count Current" or "old_count_or_not" = "Count Partial"

Returns 557 rows

select "My PK" from new_fy where "Count or not" = "Count Current" or "Count or not" = "Count Partial"

Returns 415 rows

Now, I want to do 557-415, thus I'm trying to run the following query:

select old_fy.*
from old_fy
where (old_fy."old_count_or_not" = "Count Current" or old_fy."old_count_or_not" = "Count Partial")
and (old_fy."My PK" not in (select "My PK" from new_fy where "Count or not" = "Count Current" or "Count or not" = "Count Partial"));

It returns 234 when it should return 142 (557-415). Assuming something is slightly wrong with my syntax somehow.


texts:
I need to calculated time elapsed for each user to perform his part in a transaction.

The sequence is: 0 -1 - B2 or 0-B3
The table looks like the following:

+--------+--------+-------------------+------------------+-----------+
| id     | Trtype |Internal Status    | Timestamp        | User Name |
+--------+--------+-------------------+------------------+-----------+
|2072499 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072499 | BSE100 | 1                 | 04/06/2014 08:53 | User2     |
|2072499 | BSE100 |B2                 | 03/06/2014 13:33 | User3     |
|2072605 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072605 | BSE100 |B3                 | 03/06/2014 13:33 | User3     |
.....................

I am using the following sql query to generate the table above:

select h.id, h.trtype, T.DESCRIPTION, hist.internal_status,
 ts.description status_Desc, h.val_dte, h.account_dte,
 hist.signature_timestamp, usr.login_name as user_name
  from k$transaction_header h, k$transtypes t, k$transaction_history hist, k$usr usr, K$TRANSACTION_STATUS ts
 where h.trtype = t.trtype
 and hist.trans_id = h.id
 and usr.id=hist.signature_usr
 and ts.id=hist.internal_status
 AND H.ACCOUNT_DTE = TO_DATE(&DATE_YYYYMMDD, 'yyyymmdd')
 order by h.id, hist.internal_status


texts: DBCC CHECKDB WITH DATA_PURITY gives out of range error

texts:
I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error:

Msg 2570, Level 16, State 3, Line 2
Page (1:19558), slot 13 in object ID 181575685, index ID 1, partition ID 293374720802816, alloc unit ID 11899744092160 (type "In-row data"). Column "NumberOfShares" value is out of range for data type "numeric".  Update column to a legal value.

The column NumberOfShares is a numeric (19,6) data type. If I run the following

select max (NumberOfShares) from AUDIT_Table
select min (NumberOfShares) from AUDIT_Table

I get:

22678647.839110
-1845953000.000000

These values are inside the bounds of a numeric (19,6) so I'm not sure why the DBCC check fails. Any ideas to find out why it fails? Do I need to use DBCC PAGE? How would you troubleshoot this?

Thanks,
Mark.


texts:
I would like to ask what are the number presenting in Property column in sys.col$ table? When I count distinct, it returns 39 rows. And base on my own research, I know for sure that the property = 0, it means that column uses Byte semantic and property = 8388608, it uses Char semantic. Below is a query that I found on the internet:

"select DECODE (type#,
                  1, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
                  96, DECODE (BITAND (property, 8388608), 0, 'B', 'C'),
                  Null)
from sys.col$;"

Again, I try to understand what other numbers are.

Thank you so much

Khang


texts: Accessing environment variables at design time in SSDT?

texts:
I'm re-designing some SSIS packages for 2012 and some parameter/variable/configuration issues are not clear to me.

The result that I am trying to achieve is that the user context's temp folder(stored in the %temp% OS environment variable) is available in my package. Specifically, when I open the package in SSDT, it should expand the environment variable to the current path so that it passes validation. I can get this at run time with script task, but it's ugly working around it at design time. In the SSIS catalog, I can apply an environment variable to a parameter, so that aspect is not an issue.

How can I get this to happen when a project is opened?


texts: SQL Server opendatasource reading a password protected Excel file

texts:
I would like to read a password protected Excel workbook and I'm not sure what the syntax is to enter this parameter.

I found only passwords for the connection, which is not needed due to login.

I use the following in my SQL Server 2014 Web Edition

EXECUTE AS LOGIN = 'sa'
select  *
     -- into tr.corelab
        from  OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\test.xlsx;Extended Properties=Excel 12.0')...[test$]


texts:
I'm using Percona Server 5.6.31 on RHEL 6. 64G RAM/16vCPU - VMware. It's Completely InnoDB and the database size is 300G.

I have set Innodb_buffer_pool to 52G, innodb_buffer_pool_instances to default (8) and innodb_flush_method to O_DIRECT. Innodb_read_io_threads and write_io threads are set to 64.

Memory utilization is above 90% most of the time. CPU utilization varies. Sometimes spikes. vm.swappiness is set to default (60).

I have two queries:

What should be my value for innodb_buffer_pool_instances? Is it right or is a change required?
I read many articles suggesting that swappiness needs to be set to 1 or 0 or 10 for MySQL database servers. But I do not observe any swapping on the OS. Do I need to still swappiness to 0 or 1. Is it better to change the value on proactive basis or I should change only when it swaps?

I can provide more info if required.

Update:

Show variables and Show global status output can be seen here Config details

CPU utilization is often above 80%. Does CPU utilization only have to do with query tuning?


texts: jdbc connection string ORACLE , integrated security

texts:
we are trying to configure Spotfire to use JDBC connection to ORACLE. We would like it to use integrated security (I call it Active Directory, TIBCO seems to like integrated security).

Here is the current connection string which required username, pwd

jdbc:oracle:thin:@WARHAWK.conocophillips.net:1521:OWVCORP3

can anyone point me to, or tell me how to modify this to use integrated security.


texts:
I am looking for a solution for a secondary replica change.   I have a vendor database that uses memory optimized tables.   I am trying to find a solution that would allow the In Memory tables be changed to On Disk tables in a secondary replica for external integrations and reporting.

Can anyone think of a way to accomplish this?

SQL 2016 EE


texts: How to return script IDs not in policies

texts:
I need to identify a list of scripts not in use via our mySQL database.
Active policies have a "created_by" value of "JSS" in the "policies" table. All Scripts are listed in the "scripts" table. Scripts used in all policies are listed in the "policy_scripts" table. "scrips.script_id" and "policy_scripts.script_id" are the same.

table: policies
column: policy_id
column: created_by

table: scripts
column:script_id
column: file_name

table: policy_scripts
column: script_id
column: policy_id

My Two attempts were:

    SELECT DISTINCT
    s.file_name
FROM
    policy_scripts AS ps
        LEFT JOIN
    scripts AS s ON ps.script_id = s.script_id
WHERE
    ps.script_id != ANY (SELECT
            script_id
        FROM
            scripts)
ORDER BY s.file_name;

and

SELECT
    COUNT(DISTINCT script_id)
FROM
    policy_scripts
WHERE
    script_id != ANY (SELECT
            script_id
        FROM
            scripts);

however I don't think this is working as intended... It also does not take into account active policies. I'm not sure how to add that in.

any help would be appreciated.


texts:
MySQL docs state that the time required for inserts has these approximate proportions:

  Connecting: (3)
  Sending query to server: (2)
  Parsing query: (2)
  Inserting row: (1 × size of row)
  Inserting indexes: (1 × number of indexes)
  Closing: (1)

In other words, if we use stored procedures to do inserts into a table without indexes, the deciding factor for the time required would simply be:

  Inserting row: (1 × size of row)

So I test with a stored procedure that made 2 million inserts:

create table t(i int)engine innodb;
delimiter $
    create procedure f()begin
        while @a<=2000000 do
            insert t select null;
            set@a=@a+1;
        end while;
    end$
delimiter ;

set@a=1;
begin;call f;commit;drop procedure f;
select count(*)from t;drop table t;

The query call f; takes 29.44 seconds.

Rolando suggests (below) to use a prepared statement, so I updated the stored procedure to use prepare:

create table t(i int)engine innodb;
delimiter $
    create procedure f()begin
        while @a<=2000000 do
            execute e; # # # # # # # # # # # # # # changed insert statement
            set@a=@a+1;
        end while;
    end$
delimiter ;

set@a=1;
prepare e from'insert t select null';  # # # # # # added prepare statement
    begin;call f;commit;drop procedure f;
deallocate prepare e;  # # # # # # # # # # # # # # added optional deallocate prepare statement
select count(*)from t;drop table t;

However, the query call f; now still takes 30.27 seconds.

It seems like there's no way to beat raw "begin + loop insert + commit" (unless we resort to file loading using load data infile).

However, if we insert using select joins, it only takes 6.28 seconds:

create table t(i int)engine innodb;
insert t select null
from(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`10`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`100`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`1000`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`10k`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`100k`
join(
    select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10
)`1m`
join(
    select 1 union select 2
)`2m`;
select count(*)from t;drop table t;

I'm using MySQL (though I suspect this isn't a MySQL-specific issue).

Why is "select join insert" so much faster than "loop insert"?

What's the explanation for this oddity?


texts: Enforcing "one-to-one-or-more" vs. "one-to-zero-or-more"

texts:
Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints:

each A is associated with 0 or more B's, and each B must be associated with exactly one A;
each A is associated with 1 or more C, and each C must be associated with exactly one A.

It is easy to come up with a schema that will automatically enforce constraint 1, namely, include in the definition for the B table a foreign key referring to the A table.

The same idea does not quite work for constraint 2, because it does not ensure that at least 1 C is associated with each A.

Is there a standard/canonical way to enforce constraint 2?  Or does it depend on the particular DB vendor?  If the latter is the case, what would it be for Oracle?

EDIT: Sorry, my original wording of the constraints was incomplete.  I fixed this by specifying how many A's must be associated with each B and each C, respectively.


texts: SSMS 2012 is not showing the tables of the system databases

texts:
I have SQL Server Management Studio 2012 on my laptop connected to a SQL Server 2012. If I go in System Databases and then any of the 4 (master, model, msdb, tempdb) I don't have the Tables folder.

I'm quite sure it was there in SSMS 2008 R2. Why were the tables hidden? How can I make them visible?


texts: SSIS Package - Error Exporting Data to Flat File - Invalid Character

texts:
I have an SSIS package where I pull data from a 3rd party source into a CSV file.

I get this error:

"Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Cannot copy or convert flat file data for column "Description".

SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Flat File Destination" (20300) failed with error code 0xC02020A0 while processing input "Flat File Destination Input" (20304). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure."

There seems to be an invalid character causing this issue...

I tried to remove it using this code but still an issue...


texts: Need to choose ASM performance for production database

texts:
We have a 1.5 TB of data (nonasm) and we plan to move into ASM by choose normal redundancy.

Normal Redundancy ==> Two Way Mirroring , So we need 3TB data storage for 1.5TB data including mirroring(SAN disk).

Please clarify below:

A) If we choose single RAW disk 3TB.

How many disk group we can split?

either each 500gb into 6 parts? (1 disk group)

This will be efficient for asm operation?

If the failure group we need to specify mandatory?

In our case, we have a single 3TB disk for storage, so no need to specify failure group right?

From blogs,

((For small numbers of disks (<20) it is usually best to use the default Failure Group creation that puts every disk in its own Failure Group))

so It can take by default itself. So no need to specify right?

B) If we have separate two disks each 1.5 TB

How many disk group we can split, either each 500gb into 3 parts?
(Total 2 disk group a and b)

If the failure group we need to specify mandatory?

Shall we specify manually failure group of A disk group is B and failure group of B is A?

Its a better performance?

If you cause a failure group specification it causes a spindle failure?

Either A or B case, which one is better performance approach for I/O operation in ASM?


texts: Database Tuning Advisor user permissions

texts:
I was asked to create an account to use Database Tuning Advisor.
I red on technet but I don't understand which permission do I have to give.

I tried to login with this new user, but trying to list tables, I always get warning about there are no user tables available.
If I give db_owner to the user the tables are listed.

Thanks for help.

Bye


texts: How to model "Courses prerequisites" for a university?

texts:
I am trying to model a courses structure and dependencies of a university.

For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But you also need to finish Introduction to Logic OR Mathematical Logic and Proofs.

I was thinking about using a table for the courses (Id, Course Number, Course Name, Hours, Professor) and another table for the dependencies.

There lies the problem: How to model this dependencies/prerequisites? The only -viable- solution that I came up with was making this as a string, written in plain English; but that clearly doesn't seem to be a decent solution and I also believe that it violates the atomicity principle.

Notice that the prerequisites of Artificial Intelligence are of the form IA <- DS && (IL || MLP), but other courses will have a different structure like Calculus 2, that requires analytical geometry and calculus 1 done before: C2 <- AG && C1.

I don't know if this is relevant or not, but I will use MySQL.


texts: How to convert time without time zone to timestamp without time zone in PostgreSQL?

texts:
I received this error when trying to alter a column of type time to type timestamp:

PG::CannotCoerce: ERROR:  cannot cast type time without time zone to timestamp without time zone

It's not important to me how the existing times are converted, but I do need this column to be changed. How can I force this or cast this?

This is the SQL statement:

ALTER TABLE "students" ALTER COLUMN "time_since_missing_schedule_notification" TYPE timestamp USING CAST(time_since_missing_schedule_notification AS timestamp)

I'm using Rails/ActiveRecord and this was the Ruby code that generated the above SQL statement:

change_column :students, :time_since_missing_schedule_notification, 'timestamp USING CAST(time_since_missing_schedule_notification AS timestamp without time zone)'

When I connect to Heroku's PostgreSQL server, I get these versions:

psql (9.3.1, server 9.2.7)


texts: How to detect DBCC ShrinkDatabase completion percentage?

texts:
Recently we have archived some of data from one of very large production database and need to shrink all data files to reacquire disk space.

Problem is that its taking too much time and we are unable to find out how much work is done by DBCC ShrinkDatabase so we can estimate remaining execution time.

Is there any quick way to get completion percentage of DBCC ShrinkDatabase task ?


texts: GTID ignore error on parallel replication

texts:
Im getting an error in replication where GTID and parallel replications are enabled. Now I want to skip one transaction.

I didn't find a way to do that.

Last_SQL_Error:

Worker 48 failed executing transaction '' at master log mysql-bin.002980, end_log_pos 448844064; Error 'Operation DROP USER failed for 'appi'@'10.0.0.10'' on query. Default database: ''. Query: 'drop user 'appi'@'10.0.0.10''

GTID Info:

  Retrieved_Gtid_Set: c924545b-a3e3-11e8-8a39-42010a280410:1-6798729399

    Executed_Gtid_Set: 26e8b1bf-a4ca-11e8-b64f-42010a280415:1-56,
        c924545b-a3e3-11e8-8a39-42010a280410:1-6789350755:6789350757-6789490840:6789490842-6789490901:6795994381:6796336449-6796336451:6796734964:6796760916:6796785072


texts:
I want to delete data from a table that contains millions of rows.

As a test I tried to delete 1 row but it takes over 2mins.

DELETE TOP (1)
    FROM [geneva.reports.model].PriceDiffs
    WHERE UserID = 384166

There is no index on UserID but there are 3 non-clustered indexes. I tries disabling them but they failed due to lock timeout.

The actual query plan is uploaded to Paste the Plan.


texts:
When upgrading and creating my new SQL server farm on 2016 I researched things to set on the server, one being MAXDOP setting.
From reading up about the setting I have set it at 8 on a server that has 8 logical CPUs.  The server is a VM and I believe it has a single NUMA node.  The worload is OTLP.
However I recently went on a training course where the suggestion was to set MAXDOP to 4 so I have started to second guess myself about this setting.
CTFP is set at 50.
I would appreciate help in determining the correct setting for this.


texts: How to allow remote connections to mysql from specific IPs only?

texts:
I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only?
I am looking for simple and elegant way to do this without doing too much OS related admin and just handle it with mysql.


texts: Checkpoints are occurring too frequently during pg_restore

texts:
Under PostgreSQL 9.2.2 (Windows 32 bits), I have a pg_restore command that systematically results in log warnings about the checkpoint frequency, for example:

LOG:  checkpoints are occurring too frequently (17 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

The database is about 3.3 Gb in size with 112 tables/160 views and restores in about 14 minutes.

Is it normal for that to occur during a pg_restore?


texts: Show that functional dependency pseudotransivity holds using closure test

texts:
I'm currently studying a basic database course with the course literature Database Systems - The Complete Book1.

In one of the book's exercises you are asked to

  Show that the following rules hold, by using the closure test.

This is the closure test described in the book:

And the question I'm having trouble with is as follows:

How can I show that the rules hold? Can I do it by computing the closures of the relevant attributes somehow prove that?

Thank you in advance!

1 Garcia-Molina, H. & Ullman, J. D. & Widom, J. (2009). Database Systems - The Complete Book, Second Edition. Pearson Education Inc., Upper Saddle River, NJ, USA.


texts:
I've been assigned a project where we have an active secondary environment that I'm confident that Availability Groups would address but have some questions that I hope someone can answer for me.

The situation :

I'm trying to resolve isn't the standard business continuity or disaster recovery but to expedite the release of an upgraded (schema changed) environment.

Currently the databases/UI/cubes that support our customer facing systems get an new release every 8 weeks and on that release weekend it will take 24 hours to apply new schema, apply data backfills, and to do system maintenance like rebuilding index and restarting servers.

The hope is to have a duplicate environment that we could update prior to the release date and 'swap' over minimizing the downtime to our customers. I'm primarily concerned with having a form of replicated databases that stay current with regards to data but that the secondary could have schema modifications made to it and I'm not sure if Availability Groups are the best answer for that.

Besides re-architecting the environment is there another technology I should consider to address my need to have 'hot swap' secondary environment that can be upgraded?


texts: MySQL: Is the read committed isolation level safe with mixed binary log format?

texts:
When using the MIXED format with the READ COMMITTED isolation level, am I safe?

The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed)

  If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging

In the list of When running in MIXED logging format, the server automatically switches from statement-based to row-based logging under the following conditions

.. the isolation level is not mentioned: http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html

Furthermore, the following bug from 2006/2007 talks about adding an warning or error message if the isolation level is used with the incompatible log format. See link to bug in the comment section

I have tested the combination in MySQL 5.5 and I don't get any warning. So I assume that the row format is used if the isolation is used. Am I right?

Update

The following suggests that it is safe (that ROW will be used when having MIXED). Link to manual in the comments (I can't post more than 2 links)

  If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used. It is possible to change the logging format to STATEMENT, but doing so at runtime leads very rapidly to errors because InnoDB can no longer perform inserts.


texts: Master and slave tables with different character sets - will replication break?

texts:
We have some huge tables in our MySQL 5.6 database that have latin-1 as their character set, and want to convert them all to UTF-8 (along with converting the database's global character set). The necessary ALTER TABLE statements take, between them, about an hour to run. We'd like to minimise our downtime.

Our plan is to set up replication, make the encoding changes on the slave database, let the databases sync up, and then briefly take down our webserver and point it at the slave database.

Will this work, or is there a risk of it causing data corruption in the slave?

My concern about this stems from there being two obvious possible approaches for how string values (that are being INSERTed or UPDATEed) might be stored in MySQL's binlog and sent across to the slave. Either...

The values are stored as unicode character sequences (i.e. the binlog knows what encoding they are encoded in sends that information to the slave so that it can convert the values to match the character set of the target column on the slave.)

or...
The encoded values are just stored as byte sequences, with no record of what the character set was.

The documentation doesn't shed any light on which of the above models MySQL uses.

If #1 is true, then our plan will work fine.

However, if #2 is true, then trying to replicate queries from the master to the slave is going to go wrong in some way if one of the columns involved has a different character set. For instance, once we've converted our tables from latin-1 to UTF-8 on the slave, the slave might try to store latin-1-encoded strings passed across from the master in a UTF-8-encoded column, resulting in either the stored text not being validly encoded in UTF-8 or representing the wrong characters.

The MySQL docs on Replication and Character Sets shed little light on the problem; they discuss potential issues resulting from differences in the global character set of each server, but don't touch on potential problems caused by differences in table or column character sets at all.

Is our plan sound, or are we at risk of ending up with corrupt data on the slave?


texts: How to find out "InnoDB Write buffer efficiency"?

texts:
Is there any way to find out that "InnoDB Write buffer efficiency" without tools like mysqltuner?

I can't find it in INNODB STATUS or information_schema.


texts: Oracle sql developer tree shaped represantation of Explain_Plan

texts:
I want to ask what exactly happens to the following Explain_Plan table :

It firsts Joins the two tables Products & Order_Items then the output is joined with Orders and then with the Customers.Am I right?if no please explain me, thank you very much.


texts: How can I use an array variable inside the IN operator for Oracle SQL?

texts:
I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();.

I insert some varchars into it, and then attempt to use it with an IN operator.

set serveroutput on;
DECLARE
  TYPE arr_type is TABLE of VARCHAR2(11 BYTE);
  my_array arr_type := arr_type();
  my_array_two arr_type := arr_type();
BEGIN

  SELECT MY_ID BULK COLLECT INTO my_array FROM XYZ_REQUEST;
  SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;

   FOR i IN 1..cm_array.COUNT LOOP
     DBMS_OUTPUT.PUT_LINE(my_array(i));
   END LOOP;
END;
/

I get the error: local collection types not allowed in SQL statements on the line containing: SELECT ANOTHER_ID BULK COLLECT INTO my_array_TWO FROM ABC_REQUEST WHERE PARENT_ID IN my_array;, but it doesn't make sense because if I comment out that line, my_array prints fine, which means TYPE arr_type is TABLE of VARCHAR2(11 BYTE);.

I suspect the issue is because I'm trying to use an unbounded array with the IN operator i.e. PARENT_ID IN my_array.

I tried wrapping the variable inside parenthesis, but it doesn't help. How can I use the IN operator with an array?

I understand that in this case I can use subqueries or joins, but I am wondering is it possible to use an array with an IN operator.


texts: mongoDB: querying for a document that has an object reference

texts:
I have a widgets collection that has records like this:

{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget1"}
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }

I also have a locations collection that looks like this:

{ "_id" : ObjectId("55a69475da314d9984fc6201"), "abbrev" : "CAN", "description" : "Canada" }
{ "_id" : ObjectId("55a6948eda314d9984fc6202"), "abbrev" : "USA", "description" : "United States" }
{ "_id" : ObjectId("55a69496da314d9984fc6203"), "abbrev" : "MEX", "description" : "Mexico" }

If I want to find all widgets that are located in Canada, using the Objectid, I know I can do this:

> db.widgets.find( { loc: ObjectId("55a69475da314d9984fc6201")})
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }

But what if I want to search for locations not using the objectid but the abbreviation or the description?  How would I do that?


texts: oracle sql joins returns multiple row than expected


texts: Find out where length semantics gets changed

texts:
I've code to create tables that doesn't specify length semantics:

CREATE TABLE CHAR_TEST (
    CHAR_TEST_ID NUMBER(*,0) NOT NULL,
    FOO VARCHAR2(6) NOT NULL,
    BAR VARCHAR2(6) NOT NULL,

    CONSTRAINT CHAR_TEST_PK PRIMARY KEY (CHAR_TEST_ID) ENABLE
);

Actual code is fairly large and contains hundreds of objects. I have two versions of the code that correspond to different source control revisions separated by a couple of years.

I've created 2 users/schemas on the same server with identical settings. I then run each revision in each respective schema. To my surprise, the latest revision gets an unexpected CHAR semantic:

Name         Null     Type
------------ -------- -----------
CHAR_TEST_ID NOT NULL NUMBER(38)
FOO          NOT NULL VARCHAR2(6)
BAR          NOT NULL VARCHAR2(6)

Name         Null     Type
------------ -------- ----------------
CHAR_TEST_ID NOT NULL NUMBER(38)
FOO          NOT NULL VARCHAR2(6 CHAR)
BAR          NOT NULL VARCHAR2(6 CHAR)

I can't figure out where those CHAR come from!

I can't reproduce it if I run CREATE TABLE code isolately
I've been unable to find ALTER TABLE CHAR_TEST statements in my code base that change anything but keys or indexes
SELECT value FROM V$PARAMETER WHERE NAME LIKE '%nls_length_semantics%'; prints BYTE in both schemas

Could you please suggest where to look further?


texts:
If I create the following table..

CREATE TABLE foo ( x int );

I can EXCLUDE the following,

WHERE x = <a duplicate value> (generic unique constraint)
WHERE x = <specific value>    (type of unique constraint reduce values that aren't specific to null)
WHERE x <> ANY OTHER value    (EXCLUDE WITH <>)

But, is there a clever way to permit

INSERT INTO foo (x) VALUES (0);
INSERT INTO foo (x) VALUES (1);

but, not

INSERT INTO foo (x) VALUES (1);
INSERT INTO foo (x) VALUES (0);

Or is there a module would otherwise let me do it as a function of the index and not with a trigger.

In summary, you can insert multiple values of 1 (or 0), but upon the first insertion of 1 the file is closed and no further insertion of 0 can be made, but 1 can continue to be inserted.


texts: Shortcut for Applying stored procedures in MySQL Workbench

texts:
Using MySQL Workbench, after editing a stored procedure in the built-in editor, the only way I know how to commit the changes are to take my hands off the keyboard, use the mouse, and click "Apply", which does nothing but show me a dialog box, on which I must click "Apply" yet again, which commits the changes buy yields YET ANOTHER dialog box saying that things went OK, on which I must click "Finish".

That's a lot of nonsense to mess with.  Is there a shortcut key that will allow me to commit a stored procedure with a single keystroke, or any other mechanism that will shorten the process?


texts: Optimizing MySQL for write throughput ceiling

texts:
We have a large MySQL database on AWS RDS, with a huge table around ~1.8TB with massive write workload. We recently hit a write throughput bottleneck, which caps our provisioned IOPS way below the nominal value. We can't upgrade our instance further, so now we have to shard or optimize our write operations. Sharding is not an option now.

Most of the workload is on a single large table.
The table has a few dozen columns with small data types. No huge text or blob fields.
Lots of indexes, several of them composite indexes with 2-3 columns.
Around a dozen million new rows are updated everyday, then another dozen million new rows inserted.
Updates are not sequential.
It's trivial to change our application to perform only updates or only inserts for while, and avoid and inserting and updating simultaneously, if that makes any difference.

We already optimized the aplication and the table schema and indexes as much as we could to reduce writes, but it's not enough. Are there any other practices I can adopt to further optimize this database write throughput?


texts: How extract cldr timezones from xml file to store in a DB table?

texts:
In our DB, I need each location to be associated with a "timezone".

These timezones are in https://github.com/unicode-org/cldr/blob/release-36-1/common/supplemental/metaZones.xml

I want to make a table of the timezone name strings in that xml file, so that we will have a "timezone table ID" that I can send as part of location data to client software. The client contains the code that uses timezone to determine time offset (e.g. UTC+1) for a given datetime at that location.

That is, instead of storing/sending/parsing timezone strings, for some (internal) purposes we will use this table ID.

How can I extract the list of timezone name strings from that xml file, and store it in a DB table? (Obviously I could write a little program to do this, but someone must have done this already.)

Or perhaps somewhere else in each cldr release, there is a (plaintext) list of those timezones?

Or CSV formatted info, that would be easier to massage manually.


texts: Ms Access queries equivalent in Sql Server

texts:
Queries in MS Access can act like views do in oracle in that you can query against them. Example: if you have a query in access called allProducts, you can also do

SELECT * from allProducts;

My questions is, what is the equivalent in Sql Server? I'm currently working on a project where we are converting all our ms access databases over to ms sql server. Tables get converted over to tables, but what about queries knowing very well our programmers used access queries as exampled above.


texts:
This is the table that I have. Effective date is a user input, then I need to use a trigger that produces Print depending on the language the user inputs (English or French only).

For English, I used DATE_FORMAT to achieve the output as shown, but I am completely lost on what I should do if the user chooses French. The desired output would be 23 Mai 2016.

I was thinking of storing all the months in a separate table and then calling that table depending on the month component of the date provided but I'm still not sure if that will work. Any help is appreciated.

╔══════════╤════════════════╤═════════════╗
║ Language │ Effective Date │ Print       ║
╠══════════╪════════════════╪═════════════╣
║ English  │ 23/05/2016     │ 23 May 2016 ║
╟──────────┼────────────────┼─────────────╢
║ French   │ 23/05/2016     │             ║
╚══════════╧════════════════╧═════════════╝

Edit:

Trigger Code:

DELIMITER |

CREATE TRIGGER printdate
BEFORE INSERT ON `h&a` FOR EACH ROW
BEGIN
  IF NEW.language = "English"
  THEN
  SET NEW.print = DATE_FORMAT(NEW.`effective date`, '%d %M %Y')
  END IF;
  END|

 DELIMITER ;


texts: What does "ISO standard equivalent" actually mean?

texts:
I'm reading about comparison operators. In the documentation, Comparison Operators Modified by ANY, SOME, or ALL it says (emphasis added):

  Comparison operators that introduce a subquery can be modified by the
  keywords ALL or ANY. SOME is an ISO standard equivalent for ANY.

What does this mean?


texts: I have a user that has select only access but was granted execute on a single function in our mysql db. The issues I'm running into is other more privileged users will drop and create this function periodically which causes my less privileged user to lose the execute privilege they were granted. Is there a way to grant a default privilege just to the one function the user needs so that when it's dropped and created I don't have to re-grant their permissions?


texts: How can we upgrade SQL Server 2008 R2 to 2012 with replication

texts: We want to upgrade from SQL Server 2008 R2 to 2012, current production environment having multiple publishers and subscribers.


texts: Are there any performance differences with a GUID vs INT clustered indexed column, especially with joins on those columns?

texts:
Basically what the title says: What are the differences in performance (if any) between using a clustered index on a GUID column vs a clustered index on an INT column, especially when those columns are part of your join predicates?

Edit: To clarify, I was always under the assumption GUIDs typically perform worse than INTs when used in join predicates.
I understand a GUID is 4x the data size of an INT, but I was asking my question to really understand why and what's going on "under the hood" that causes GUIDs to perform worse, even after the indexes are rebuilt and there's minimal fragmentation differences between the two types.


texts: Single service account running all SQL Servers

texts: Is there any negative performance impact for using the same service account and agent account to run SQL Server and SQL Agent respectively for all SQL server instances running at a small company of say 35 servers? The biggest database is 0.5 GB. any suggestions are welcome. Thank you.


texts: Inheritance - What's better: three "equal" tables or one with a type column?

texts:
I'm having an existencial doubt: I have three conceptual entity types on my logic:

- distributors
- business_groups
- clients

All of three are "companies", and will have the same columns. The relationships are the following:

- 1 distributor => N business_group
- 1 business_group => N clients

In reverse:

- 1 business_group => 1 distributor
- 1 client => 1 business_group

My question

Is it better to create three "equal" tables, one for each entity type, or a single one for the three with distributor_id and business_group_id foreign keys (referencing the same table) that sometimes will be NULL?

Considerations

First approach (three tables): Separation of different concepts, tables with less entries, foreign keys without NULLs, three "equal" tables (with the same columns all)
Seconds approach (one table): Only one table but less intuitive, some NULLs on foreign key columns, one column more for type


texts:
Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

I have some sql server database for which recovery model is set to full.Now for some databases the ldf size has become large.I tried taking log backup regularly,but i didn’t find decrease in physical size of ldf. I followed what stan20 has told in this link http://social.msdn.microsoft.com/Forums/en-US/databasedesign/thread/704bcfbc-9251-4f4e-9a80-04bc7fe9ae78
But it only reduced logical file size. So how i can mange size of ldf file?


texts: oracle expdp similar method to export datas for SQL Server

texts:
On our oracle databases, we regularly run database export through expdp/impdp utility (wrapped for readability):

$ expdp user/[password] SCHEMAS=<nom_schema> DIRECTORY=expDatapump
     DUMPFILE=exp.<schema>.dmp LOGFILE=exp.<schema>.log

Thanks to it we are able to refresh data from a specific schema on test database, for example.

I am wondering if there is such a way to do this in Microsoft SQL Server. I have tested the  -> tasks -> "export data" wizard within SSMS, but that was not very helpful (useable only for very small db, PK & FK deleted etc).

Can you perform dumps from your SQL Server databases? On an other hand, is it preferable (if possible) to directly restore a specific SQL Server schema from a .bak file?


texts:
I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly.

I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', RESEED, 0) in the insert procedure.

But I don't know how to have a normal identity besides this one. Solution may be using two tables but I prefer to have both of them in a single table.


texts: Due to precedence set within my organization, I need to replace NULL values in certain columns (roughly 15-20) with the empty string. While the solution comes quite easily in more traditional languages, I'm struggling with how to do this in T-SQL. Can anybody point me in the right direction? Much appreciated.


texts: MySQL corruption even after full dump and re-import

texts:
We have a production issue where MySQL has started spitting InnoDB corrupt page errors and falling over.

The error message seem to relate to indexes so I assume the data itself is ok and when I restarted the server with innodb_force_recovery=1 I was able to dump the lot using mysqldump.

However, re-importing hasn't fixed the error, dropping/recreating the database didn't help either and I've even gone as far as moving the innodb files out the way and letting MySQL recreate them before re-importing.

No joy, corruptions keep re-occuring within a few minutes and the server dies again.

The weird thing is the database is a mix of ISAM and InnoDB tables and I had a couple of errors in the ISAM tables as well on some attempts.

Is this likely to be something data-related, something corrupt deeper in MySQL that isn't cleared by the above or signs of a hardware fault underneath it all?

EDIT: This is Mysql 5.0.51 running on Ubuntu 8.04


texts:
I have problem in DBeaver with russian (azbuka) chars. Everything was fine, but now I see this.

It is on OS level or it is app related? How can I fix it? OS Ubuntu 20.04.
Thank you.


texts: How to start LaunchPad service of SQL Server on Ubuntu?

texts:
I'm running SQL Server 2017 on Ubuntu 16.04.

When trying to run an external Python script, I got the following error:

Msg 39011, Level 16, State 1, Line 0
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

I searched and found out that the solution on Windows is to start the service of LaunchPad with Windows' service manager.

But I need the equivalent of Ubuntu. I don't even know what the LaunchPad service is called.


texts: Joining tables with all rows for each unique column value

texts:
I have 2 tables as follows:

    | A        |
    +----------+
    | ty_date  |
    | ly_date  |
    +----------+

and

    | B           |
    +-------------+
    | locationid  |
    | ty_date     |
    | value       |
    +-------------+

Let's say Table A has ty_date from 1st January till 31st December while Table B has ty_date from 1st January till, say, 1st August for each locationid.

I am trying to join the table in such a way that in Table B each locationid has all the dates from Table A with the field value having null values for all future dates.

I tried FULL OUTER JOIN ON A.ty_date = B.ty_date but I am still missing the grouping logic for each locationid

Any help on what approach I can try?


texts:
I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace).

Our application has very high uptime requirements. It runs 24x7x365 and impacts literally thousands of sites, and I want to ensure the minimum possible downtime during the transition. (Don't ask how we're pulling this off with a single box right now.)

My plan for the web server is a little bit of DNS work to point a new subdomain to the new server setup, and forward requests from the existing server. That'll take care of flipping everyone to the new web box fast enough. The problem is the database - how do I keep it in sync until I'm ready to flip the switch on the website (or is it worth the effort vs. just accepting the downtime to stop the app, backup, compress, transfer, and restore?).

A few details. We're running Sql2k5 on the old system, 2k8r2 on the new. The database itself is ~30 gigs for the primary, ~60 gigs for the warehouse. I can live with downtime on the warehouse, but really want to minimize the impact on the main database.

Any suggestions for the best way to migrate the database across from the old setup to the new?


texts: Count orders 12 months back from a certain month group by that month

texts:
I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count all order from selected month and sum up all orders that where made until 12 months back.

The outcome should look like:

I started with this query but it only counts orders for the selected month and not includes orders for former months.

SELECT TO_CHAR(create_date,'yyyymm') AS yyyymm,
       COUNT(order_id ) AS Number_of_orders_12_month_period
FROM Orders
WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create_date) AND TO_CHAR(create_date,'yyyymm')
group by 1


texts: when a table trigger fires, what user does the trigger execute as

texts:
I have a table that I am using Insert/Update/Delete triggers to audit. I want to restrict access to the audit table so that only the triggers can write to it. What I am trying to find out is, what user would those triggers count as for inserting records into the audit table.

EDIT: Since phil showed me the documentation in his comment for how it is determined, this changes my question a little.

What is a good method to restrict access to the audit table so that only the triggers can insert records?


texts: Update table using values from another table in SQL Server


texts: SQL Data type for "Reference" fields in table - SQL Server

texts:
I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit welcome).

I will draw a brief overview of the fields functions to try and illustrate requirement clearer. The fields with the asterisks I am calling "reference" fields, there are many more event types but just keeping example brief and hopefully clear. Table-Events should be like a junction table. I am trying to change my ERD (of which this is a small section) so that my events aren't hardcoded and I reduce redundancy and if in future new EventTypes are needed I can just update EventTypes to add them.

Edit: Each Column here represents a table with the first Table- representing the table name the second field the GUID field etc.

Table-Player     Table-Events      Table-EventTypes
PlayerID         EventsID          EventTypeID
firstName        Minute            Try*
secondName       Fk_EventType      Conversion*
Fk_team          Fk_Player         Penalty*

As such though Try/Conversion/Penalty will never have any data it will just be selected in the Events Table with Fk_Player and minute. So what data type should I use.


texts: Debug why Postgres wants to use an uninstalled extension for a function

texts:
I recently moved a Postgres function app_user_id from an old, custom extension called intpair into a new, custom extension called heap_exts. This worked well except for machines I restored from a Postgres backup.
Here's the error:
db=> select app_user_id(1, 2);
ERROR:  could not access file "$libdir/intpair": No such file or directory

intpair is not installed and heap_exts is installed as shown by the output of \dx:
-[ RECORD 3 ]-------------------------------------------------------------
Name        | heap_exts
Version     | 1.0
Schema      | public
Description |

-- No mention of intpair

The function app_user_id is listed in the system catalogs as being owned by heap_exts:
SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
  AND e.extname IN ('heap_exts')
  AND p.proname = 'app_user_id'
ORDER BY 1, 3;

-[ RECORD 1 ]----------
extname   | heap_exts
extschema | public
proname   | app_user_id
proschema | public

I initially migrated the function from the old extension intpair into the new extension heap_exts with the following code:
-- version 0.1 in heap_exts--0.1.sql
ALTER EXTENSION intpair  DROP FUNCTION app_user_id(bigint, bigint);
ALTER EXTENSION heap_exts ADD FUNCTION app_user_id(bigint, bigint);

However, for the new machine restored via backup from the old machine, I created the app_user_id function directly because the old extension intpair no longer exists:
CREATE FUNCTION app_user_id(BIGINT, BIGINT) RETURNS app_user_id
AS 'MODULE_PATHNAME', 'app_user_id_make' LANGUAGE C STRICT IMMUTABLE PARALLEL SAFE;

Why is Postgres trying to look in the intpair extension for the app_user_id function instead of looking in heap_exts?


texts: 8000 character limit on OPENQUERY against a linked server

texts:
I have a query that I'm trying to run through OPENQUERY on SSRS/SQL Server 2014, but I keep getting an error of:

  The character string that starts with [...] is too long. Maximum length is 8000.

Is there any way to work around this limitation?

For reference, I'm trying to run a query from SSRS through a linked MySQL Server.


texts: Promote a slave to master; fall back to former master if new master has issues?

texts:
I've been managing mysql db's for some years now, and have gone through several iterations of promoting a slave to take over the role of its former master. My understanding has always been that after you promote a slave to become the new master, you need to basically rebuild the former master from scratch (i.e. from backup) to turn it into a slave of the new master. That 'understanding' has likely been due to all those iterations being due to corruption or some other problem with the original master. It may merely be the misguided assumption of a sysadmin.

I'm in the process of migrating an existing hardware infrastructure into IaaS. I have a slave in IaaS of the existing master, and when the time comes, I'll promote that slave to become the new master for the new infrastructure.

But I've wondered  - is there a way to have the promotion be 'non-destructive' of the original master? Say it becomes clear after deployment that there are performance issues, or what have you - and I realize that i need to fall back to the original master, if only for some hours or days until issues can be worked out. Is it possible to re-promote the former master, in a data-consistent fashion with the new master?

I guess a related question in this case would be, can the former master be immediately turned around to become a slave of the newly promoted master? Thus being able to switch between them as needed?

This would all be under CentOS 5.x, Perconal mysql 5.5.


texts: MYSQL storage increasing unexpectedly (RDS)

texts:
I am total newbie to database game.
& I am using mysql 8.0 RDS 20GB free tier.

I don't have much data on it.(just 300 Mb)

But I always get a mail that you have exceeded 85% of storage limit.(approx 17Gb)

Also Under monitoring tab in rds panel it shows:- Free Storage Space (MB) = 19.330Gb (Constantly without any drop)

I have turned off : general log, slow logs, binlog,

Can someone please help me whats consuming these storage. Is this something to do with INNODB?


texts:
I have been using SQL Server linked servers for years, and they have been working fine. But now when I try to run a query (on a machine with Windows 10) using linked server I get this error:

  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

If I then run the same query on my older machine without Windows 10, I have no problems and never ever had any problems.

Now the funny part begins: If I run the query from my old PC (logged on with the same credentials) and then within 5 minutes run the same query from my Windows 10 machine - there is no problem!

It's like if my old machine establishes a connection that is open for about 5 minutes and then times out, and my Windows 10 machine cannot find out how to establish that connection.

Any ideas?

I am just running a query in SSMS logged onto server A querying data from a database on server B, and doing the same thing on both machines.


texts:
SELECT * would result in:

course_id + group
-----------------
   1      + SE123
   1      + BA134
   2      + SE213
   3      + SE223
   3      + BA344
   4      + SE222

I would like it be like this:

course_id + group
---------------------
   1      + SE123/BA134
   2      + SE213
   3      + SE223/BA344
   4      + SE222

How can I do that?


texts: Does setting a database to single_user prevent delivery of Service Broker messages?

texts: Is it safe to assume that while a database is in single_user mode, the queues are not available for message delivery? My understanding is that the special tables that the queues represent reside in the database context, but it's not clear if message delivery counts as a 'connection' in the usual sense.


texts: How to run Oracle Client 10g from non Administrator account?

texts: Hi I have 2 users configured on my Windows 10 Home machine. One is Administrator and another is my own user. Although my user is configured as an Administrator, I can use Oracle Client 10g from my Administrator account only. Is there any way in which I do not have to switch to the Administrator account and still be able to use Oracle Client 10g from my own account ?


texts: Query Editor Connection Error With Unattended Service Account

texts:
ERROR:
When editing a report in report builder, opening the query editor gives the following error

You have specified integrated security or
credentials in the connection string for the
data source, but the data source is configured
to use a different credential type. To use the values
in the connection string, you must configure
the unattended report processing account for the report server.

SETUP:
1. SQL server 14.0.2027.2 (2017)
2. SSRS 14.0.600 .1274 (2017) configured with an unattended service account
3. Reporting portal is accessed through active directory login
4. The report uses a shared datasource with this connection string

Server=tcp:xxxxx;Database=yyyy;Integrated Security=true;
Encrypt=SSPI;Connection Timeout=30;MultipleActiveResultSets=True;
Persist Security Info=False;App=SSRS;

5.The Credentials are set to Without Any Credentials
6. Setup as documented by MSFT https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/configure-the-unattended-execution-account-ssrs-configuration-manager?view=sql-server-ver15

OBSERVATIONS:
 1. Viewing reports and subscriptions work as expected
 2. If i set credentials to As the user viewing the report, this fixes the issue but breaks subscriptions as they need either stored credentials or no credentials
 3. In report builder, doing a Test Connection on the data source, returns Connection created successfully

WORKAROUND:
 1. In report builder, changing the datasource from Use a shared connection or report model to  Use a connection embedded in my report with the exact same connection string as above allows the query editor to open. However i have to change it back to the using a shared connection after I make the required edits.

The workaround is not ideal for our user base and I would like to fix the underlying issue. Has any one experienced this issue before ?


texts:
Newbie here..

Hello
Searching online and I could not find any articles/blogs that pointed, related or had any opinions regarding SQL Server and SQL Server Alerts installed/running on virtual machines. Question is, how important, or relevant are SQL Server Alerts (19-25,823,824, or 825) if the environment SQL is installed on is running as a guest on a a VM? Are there other solutions.

Regards,


texts: PostgreSQL find groups with same members

texts:
Given the following schema:

User
  id

Group
  id

Membership
  group_id
  user_id

(With a unique index on group_id, user_id)

How would I select the group(s) of which the same two users are a member?

What I have now:

SELECT   "Membership"."group_id"
FROM     "Membership"
WHERE    "Membership"."user_id" IN (1, 2)
GROUP BY "Membership"."group_id"
HAVING   COUNT("Membership"."group_id") >= 2

Which seems to work, (it only retuns the group ids of which both users are a member), but looks rather confusing.

Is there a better way to express this query?

EDIT

So given the following data:

Users
-----
id
1
2
3

Group
-----
id
1
2
3

Membership
----------
group_id user_id
1        1
1        2
2        1
2        3
3        2
3        3

I'd like to find groups of which both user 1 and 2 are a member.

So the result of the query with user_id 1 and 2 should be group 1.
The same query for user_id 1 and 3 should return group 2.
And the query for user_id 2 and 3 should return group 3.


texts: MongoDB - Replication Oplog Window has gone below 1 hours

texts:
I'm working with MongoDB Atlas and I have a cluster of 3 with M30 and 100gb of storage.

My current usecase is the following:
- An user dispatches one search in my platform
- The platform dispatches this search to other providers (12)
- I get like 2k documents per provider for this search

For the configuration, I'm working with Mongodb Atlas in a 3 replica-set cluster (M30 = 8 GB RAM • 100 GB storage). I also have an TTL index to expire the documents in the only database/collection I'm using to delete the documents after 10 minutes based on a date field in each document (searchStartedAt).

What I was trying to understand is this alert I'm getting ("Replication Oplog Window has gone below 1 hours"), if I got this right is how long the master node can continue receiving data before the slave nodes get out of sync.

I would like to check if my understanding is right and if there is any tunning possible to avoid this.

Can someone give me a hint? If any other information can help regarding this, please let me know and I'll update the question.

Thanks a lot for any tips regarding it.


texts:
I am looking to create an index for a database to acheive better performance.
I am using this query:

SELECT DISTINCT t1.* FROM current_order
AS t1 LEFT JOIN
receipt AS t2 USING (paper_id,subscriber_id)
WHERE t2.id NOT IN (SELECT id
FROM receipt WHERE paid_till_date > Now())
UNION
SELECT current_order.* FROM receipt RIGHT JOIN
current_order USING (paper_id, subscriber_id)
Where receipt.id IS NULL ORDER BY
subscriber_id, paper_id

Tables are:

 PAPER
    id
    name

 SUBSCRIBER
    id
    name
    address
    suburb
    state
    postcode
    round_id

 CURRENT ORDER
    paper_id
    subscriber_id

 ROUND
    id
    name
    paperboy

 RECEIPT
    id
    receipt_date
    paid_till_date
    paper_id
    subscriber_id

I understand that primary keys are already indexed, and that i should use an index where WHERE clauses and ORDER BY clauses are used frequently. So i'm assuming that the subscribers Name would be a good one to index?
And also the receipt date?

Any tips appreciated


texts: Constraint violation in a trusted constraint

texts:
I have a table with a constraint on it that has an existing record that is in violation of that constraint.  While this is possible if someone applied the constraint without a check and after violation data was inserted, how would it be possible that the is_not_trusted flag is zero in the sys.foreign_keys table?

This is the situation I currently have, a data violation on a foreign key where the is_not_trusted flag is zero.  I thought that was essentially the flag that confirms whether the constraint has been enforced 'religiously'.

Thanks


texts:
This question got me thinking:
ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)
In it the questioner states two functions give the same result set, however they don't in that they have identical rows but they are always in different (and consistent) orders.

Is the order of a result set part of the result set? Wikipedia implies no (but doesn't strongly determine an answer, nor does it speak from a position of authority):

https://en.wikipedia.org/wiki/Result_set

Is

Sector  Name
A   Alpha
B   Beta

the same result set as

Sector  Name
B   Beta
A   Alpha

I personally only work with SQL Server, does the answer change by platform? Is the answer a matter of opinion only? (If so please give answers of fact only, or of consensus by professional bodies at any rate- not your personal opinion please)


texts:
I have a database in mongoDB 2.6.4 in which I want to restrict the users as per their level. I want to give read privileges to some users and insert/update privileges to some other users on a particular database. Please let me know how I can create user defined roles and assign them to the users. I tried the below but it is not working; the users still have full privileges:

db.runCommand({createRole: "read",

   privileges: [

         { resource: { db: "sample", collection: "" },
                     actions: [ "find" ]
           }],
   roles: [],

   writeConcern: { w: "majority" , wtimeout: 5000 }

             })

==================================================================================

db.addUser( { user : "read",
              pwd  : "read",
              roles:["read"]
        } )


texts: How can client retrieve SQL Server public SSL certificate?

texts:
Can a user retrieve the connection certificate for an SQL Server instance (similar to how someone can retrieve an HTTP certificate)? If so, how?
Context
If encryption is requested by the SQL Server client, the server certificate must be validated before allowing an encrypted connection. Development servers are often configured with self-signed certificates, which won't validate under default client setup.
To use a self-signed certificate to encrypt a connection, either:

the certificate must be added to the local store
the client must set "TrustServerCertificate=true"
the client must not request encryption, and the server must set "ForceEncryption" to "yes".

The latter two options are open to MitM attacks every time a connection is made. For the 2nd, some clients don't save the connection setting, so it must be re-set for each new connection. Thus the most desirable solution is to add the certificate to the local store (it takes a little more work initially, but after that is easiest & safest option). For users in a domain, the certificate is easy enough to distribute, but otherwise the certificate will need to be installed manually on each computer. An admin could provide the certificate to each user, either by sending directly or making it available as a network resource, but sometimes the admin may be too busy or unresponsive for other reasons to provide the certificate.
In such a case, it would be useful if the user could get the certificate from the SQL server directly, just as a certificate can be retrieved from an HTTP server using a browser or openssl s_client. This would still be vulnerable to a MitM attack when the certificate was retrieved, but provides a much narrower window than trusting the certificate every time there's a new connection.