texts: Get the date interval from table rows, based on a start stop column

texts:
I have a table returning the following result set:

  mydate    | startstop
------------+----------
 2018-02-07 | start
 2018-02-14 | stop
 2017-02-06 | start
 2017-02-12 | stop
 2016-02-05 | start
 2016-02-12 | stop

I need to know if my current date is in one of the intervals, for example if I query the table from the current date '2017-02-07', I need to get 'TRUE'.

I know it looks like that simple but it is not being simple!

The best that I found is this:

       select true
        where '2017-02-06'>=
         (select mydate from mytable where starstop='start' order by id limit 1)
        and '2017-02-06' <=
         (select mydate from mytable where startstop='stop' order by id limit 1);

It works returning TRUE if the date is in one of each intervals, but only if the table does not have intervals in the future, and as you can see my table has intervals in the future.

Note: The database management system is PostgreSQL 9.1


texts: Computed column cannot be persisted because the column is non-deterministic

texts:
I know this is not the first time this type of question has been asked.

But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always be the same, right?

CREATE TABLE dbo.test (Id INT, EventTime DATETIME NULL, PosixTime INT NOT NULL)
GO

DECLARE @EventTime DATETIME =  '20181001 12:00:00'
DECLARE @GPSTime INT = DATEDIFF(SECOND, '19700101', @EventTime)
INSERT INTO dbo.Test(Id, EventTime, PosixTime)
VALUES (1, @EventTime, @GPSTime)
    , (2, NULL, @GPSTime)
GO

SELECT * FROM dbo.test
GO

ALTER TABLE dbo.test ADD UTCTime AS CONVERT(DATETIME2,ISNULL(EventTime, DATEADD(SECOND, PosixTime, CONVERT(DATE,'19700101'))),112) PERSISTED
GO

  Msg 4936, Level 16, State 1, Line 42 Computed column 'UTCTime' in
  table 'test' cannot be persisted because the column is
  non-deterministic.

I think I'm following the deterministc rules here.

Is it possible to create a persisted computed column here?


texts: What's happening if one of concurent lock on a table rename the locked table?

texts:
I've hundred of insert as following each minutes:

BEGIN ISOLATION LEVEL SERIALIZABLE;

  INSERT INTO table1 VALUES (...);

COMMIT;

And I've the following transaction which will occured once:

BEGIN ISOLATION LEVEL SERIALIZABLE;

  LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE;

  /* Rename the locked table */
  ALTER TABLE table1 RENAME TO table1_renamed;

  /* And I recreate the table */
  CREATE TABLE table1 (
    ...
  );

COMMIT;

The second transaction will blocked all INSERTs until it commits.

When it commits, will inserts be proceed on the new table1 or on the renamed one?

In other world are locks acquired on object's name (so it's resilient on rename operation) or on object's reference?


texts:
I have a set of transactions that can either be payments or purchases. The purchases can be of different types (e.g. 'ApplePurchase', 'OrangePurchase').

I will select some payments by their coupon code. For each payment, I wish to discard all the purchases up to the last time, prior to that payment, that the balance reached zero. I then wish to aggregate the remaining purchases. (Thanks to @Andriy M for that phrasing and apologies for butchering it).

Any advice on how to approach writing (part of) the solution in SQL or would be much appreciated.

Here is some example data:

create table users
     (id integer primary key);

create table transactions
     (user_id integer references users (id),
     type enum('ApplePurchase', 'OrangePurchase', 'Payment'),
     coupon varchar(10),
     date_paid date,
     amount integer);

insert into users
    (id)
    values (1), (2), (3), (4), (5);

insert into transactions
    (user_id, type, coupon, date_paid, amount)
    values (1, 'ApplePurchase', NULL, '2003-01-02', 5),
           (2, 'ApplePurchase', NULL, '2003-01-02', 15),
           (1, 'Payment', 'aCoupon', '2003-01-02', 5), # here user 1's balance reaches 0
           (2, 'OrangePurchase', NULL, '2003-01-03', 10),
           (2, 'Payment', 'bCoupon', '2003-01-03', 23),
           (1, 'ApplePurchase', NULL, '2003-01-05', 15),
           (1, 'Payment', 'bCoupon', '2003-01-05', 15),
           (1, 'ApplePurchase', NULL, '2003-01-07', 15),
           (1, 'Payment', 'bCoupon', '2003-01-07', 15);

Perhaps I am looking to perform this aggregation for payments of coupon 'bCoupon'. Member 2 has one payment of that type and 3 purchases before it. As there are no intervening payments, we only need to aggregate the purchases (as in the first row). Member two has two payments of that type. The first has two purchases before it, but the very first purchase is cancelled by an earlier payment. In that case, I am looking for a result of the form

Date       | Member |  Apple | Orange | Payment | Balance
2003-03-01 | 2      |  15    | 10     | 23      | 2
2003-05-01 | 1      |  15    | 0      | 15      | 0
2003-07-01 | 1      |  15    | 0      | 15      | 0


texts: How to design SQL schema that treats historical data as first-class citizen?

texts:
I'm currently designing a SQL Server database schema for an application that consists of highly structured "tree" data. So, a table might look like this:

id
parentId (references id in same table)
text

This is simple enough, but I also need to be able to update either the parentId of a node or the text of a node (or delete nodes), and access each revision of the tree at the same speed that the current revision can be retrieved. My current solution is to have two tables. First a revision table:

id
revisionDate

And then a modified node table:

id
UID (same for all revisions of a specific node)
revisionId (references id of revision table)
deleted (flag for deleted node)
parentUID (references UID of parent node)
text

So to get the tree for a specific revision, you take the revisionId and all revisionIds before it, then query the node table for all nodes with those revisionIds, then take only the highest revisionId nodes for nodes that share a UID, then delete any node that has the deleted flag set.

This works, but gets ugly fast (I'm using a simplified example) I actually have about 10 tables like this that need revision tracking, with each table having cross references to each other. This approach also breaks foreign keys, since UIDs are not unique. I cannot simply copy the full tree data for each revision, since there may be hundreds of revisions per hour.

What would the best practice be for a problem like this? I'd be willing to use a nonrelational database if it would be clearly superior.


texts:
I had two MySQL servers running (127.0.0.1:3306 and 127.0.0.1:3307). Now I get this strange error: mysql_connect(): Lost connection to MySQL server at 'reading initial communication packet' I can connect with localhost:3307, but there aren't any tables/databases. I'm fairly sure /etc/my.cnf is right:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root
password   = ***

[mysqld]
port       = 3306
socket   = /var/run/mysql/mysql.sock

[mysqld2]
port       = 3307
socket   = /var/run/mysql/mysql2.sock

What can I do now? How can I sanely restart the MySQL server?

Perhaps more importantly:

How can I make sure all the tables still exist?


texts: SQL Server: Are there any performance benefits from indexing a table <8kb in size?

texts:
If a table is <8kb in size in SQL Server then presumably it is all stored on a single page. Thus - aside from enforcing a unique constraint - is there any performance benefit that could be obtained by applying an index?

My assumption is that in order to read such a table, SQL Server has to read the single page it is stored on into memory. So any indexing on an <8kb table is pointless from a performance point of view as any index would take up an extra page by itself and then you'd have to read two pages (unless the index included required columns) to get the data.

So unless there's something in the indexing which would either improve the handling of the data in ways I don't understand, or it helps the query optimiser, my assumption is indexing such a small table is pointless.

Would appreciate your expert thoughts!

(This is a purely theoretical question to help me ensure I understand some database concepts properly... thanks!)


texts: How can I first sort by color & then in alphabetical order in ssrs?

texts:
I'm still new to ssrs. In my query the color is determined by location. In my query I did
ORDER BY [Location], Underwriter. Now this works perfectly fine in my query, but when I run it in SSRS it doesn't sort like in the query. I get this

what I would like to do is first group the colors together and & set the underwriters in alphabetical order i.e. Balluka, Bill(light blue)
           Matanane, Brian(green)
           Walsh, Daniel(green)
           Davidson, Christine (pink)
           Hamilton, Marianne (pink)
           Karger, Tierney (pink)
           Marroquin, Melanie (pink)
           Sylvester, Todd (pink)
           Hopp, Robert (white)
           Lohr, Jeff (white)
           Raskovic, Hanna (white)

Now what I tried doing in ssrs was in the design I right clicked underwriter> interactive sorting: tried sorting by underwriter & location but nothing happens when I run it. Please help


texts: Comparing the data in two Postgres databases

texts:
I am looking for a way to compare the data in two Postgres databases.

Basically a 'Before' and 'After' snapshot of a transaction being posted.  What I am looking for is all the tables/records that have been altered by a particular type of transaction.

The solution could use ODBC, or PostgreSQL directly. I have connections to the databases both ways.


texts: Redundant to have index + full text on same column?

texts: If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to create the indexes and am wondering if I need both.


texts:
Out of nowhere we started seeing this error and it seems to occur fairly frequently when making calls to the database or any other database in our Elastic Pool. The DTUs aren't being maxed out and the resource dmvs don't seem bad.

  Failed to enter Common Language Runtime (CLR) with HRESULT
  0x80131022. This may be due to low resource conditions. (db)

Here's what I got from the system resource governor pool

Resource Pool Name           cache_memory (MB)   used_memory (MB)
internal                     104.773437          1577.125000
default                      37.609375           38.796875
SloSecSharedPool             2.914062            8.156250
InMemBackupRestorePool       26.210937           101.437500
InMemDmvCollectorPool        186.195312          203.406250
InMemMetricsDownloaderPool   2.234375            2.250000
InMemDTAPool                 0.000000            0.000000
SloHkPool                    0.000000            0.031250
InMemQueryStorePool          22.453125           35.304687
InMemWIAutoTuningPool        3.312500            4.062500
InMemXdbLoginPool            3.976562            6.250000
PVSCleanerPool               0.000000            0.000000
InMemTdeScanPool             0.000000            0.000000
SloSharedPool1               1108.890625         1234.312500

From the sys.dm_os_performance_counters this is what it looks like for the past 3 hours.

cpu%    data_io%   log_write%   memory_usage%   max_worker%   sessions%
22.37   73.51      16.54        41.56           5.50          0.43

This doesn't seem like a common error for SQL Azure as I cannot find anything that pertains to this happening past SQL Server 2008. Any help would be appreciated.

Update:

We got a response from the Microsoft support rep and it looks like our memory usage was completely capped out.

After moving the elastic pool up a tier the error has gone away. We have ~56GB of memory instead of the ~28GB we had previously and the error ceased. This would have probably also moved us to a different server on Azure which may have fixed the problem for now. The site has been running at around 78% on memory usage with the buffer cache hit ratio @ 8588 and PLE of 87332. Now it's operating at usage under 2% across the board for CPU, Workers, DataIO, etc. during normal load which seems like a huge waste.

We could not definitively identify what was causing the errors but we assume it was the complete memory usage as the site is running fine for now...


texts: Query for existence of objects across two multi-to-one relationships

texts:
I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".

The "Config" relation contains basically just a name. Finally, there is the "ConfigKV" relation, having three attributes: a key, a value, and a foreign key to "Config". Each Config has multiple ConfigKVs.

This is the SQL statement to create the database:

CREATE TABLE "MyObject" (
  "value" INTEGER NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

CREATE TABLE "Config" (
  "name" TEXT NOT NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "ConfigKV" (
  "key" TEXT NOT NULL,
  "value" TEXT NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

I want to query for all MyObjects that:

has value 42
the associated Config of which has exactly two ConfigKVs associated:

"Foo"->"Bar"
"Fuz"->"Baz"

Aside from the "exactly" part above, I can achieve that with this query:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'foo' AND value == 'bar'
) as SQ1 on SQ1.cfg == MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'fuz' AND value == 'baz'
) as SQ2 on SQ2.cfg == MyObject.cfg
WHERE MyObject.value == 42;

The inner joins neatly eliminate all MyObjects that do not have the required Key-Value mappings associated. However, I have no clue how to exclude MyObjects the config of which has a superset of the specified Key-Value mappings. Any idea here?

Thanks a lot!


texts:
I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example:

SELECT *,
  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
FROM "Detection"
WHERE
  "RecognitionTimestamp" >= '2018-10-03 09:01:00' AND
  "RecognitionTimestamp" <= '2018-10-04 09:00:00'

How can I do this?
I've tried avg("delta_previous"), avg(delta_previous),avg("RecognitionTimestamp" - lag("RecognitionTimestamp"))


texts: Updating a set of rows in a table with values from a different set of rows. Both sets are from the same table

texts:
I want to update a set of rows in a table with data from a different set of rows from the same table.
Ex: I want to update Customer Invoice Amounts from year 2018 with Customer Invoice Amounts from Year 2017. In the below scenario I want to update Set2 with Amount and Product from Set1 for the respective Customer and Line Item.

Set 1

Customer, Year, Invoice Line, Amount, Product

Cus1, 2017, Line1, 200, PR1
Cus1, 2017, Line2, 300, PR2
Cus2, 2017, Line1, 500, PR2

Set2

Cus1, 2018, Line1, 0,
Cus1, 2018, Line2, 0,
Cus2, 2018, Line1, 0,

Can I accomplish this using MERGE command. I am not planning to insert data I just want to update the Set2 with any available data in Set1.


texts: Can I rename the View Report button in SSRS

texts: I was wondering if it is possible to rename the button View Report in SSRS or even add some command buttons with it.


texts: how to select rows where for a column there are two rows one with null and another with non-null value then select only row with non-null value

texts:
Column A      Column B

117220177     BOOK9488
117220177     NULL
14061         NULL

Oracle query for selecting rows:

When Column B has two rows corresponding to column A then pick row with non-null value in Column B. When there is only one row then pick irrespective of value in Column B

Expected Output:

Column A      Column B

117220177     BOOK9488
14061         NULL


texts: Halting a multi-step Agent job without resorting to "Quit the job reporting success"?

texts:
I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures.
How can I tell Agent to successfully exit the whole job?  (Using "Quit the job reporting success" throws a dreaded Yellow Triangle for the job, and the Red Circle for the step, even though there are no errors.)
Since there are many steps, any one of which might legitimately fail, I'd rather not lump everything into one giant Step.


texts: Why does an index rebuild requires a Sch-M lock?

texts: I don't see why an index rebuild would require a Sch-M lock on the given index.


texts:
In a series of unfortunate mistakes, I lost 4 months of data and all the rapid database modifications. I’m not a database administrator, I’m a programmer who also designs and happens to maintain a database.

Initially I just wanted to only compare some database objects to another database. I did that using OpenDBDiff tool. I selected the objects that I wanted to update and hit ‘Update selected’. It failed for some reason, but I didn’t pay any attention to it. So I generated a script to execute directly into SSMS. I again failed to notice that the script included all objects, and not just the selected ones. I realized too late, by then, some of the destination's database objects i.e. tables, views, functions, etc. were already dropped.

And then it got worse,  I know we have daily backup and decided to restore from there. It turns out that for some reason, this database, on that server has its last backup 4 months ago and didn't run afterwards successfully. I decided that I’d restore from its transaction log to the current date. I still don’t know if that’s even possible, but I decided to do it on a live database. I did not take a new backup even at this point, I was afraid that this will destroy the transaction log. So I ran a command like this:

USE [master]
BACKUP LOG [mydb] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb_LogBackup_2019-01-17_13-49-00', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [mydb] FROM  DISK = N'D:\Backups\mydb_2018_08_30_19_01_01\mydb.bak' WITH  FILE = 1,  MOVE N'exact_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.mdf',  MOVE N'exact_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [mydb] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\mydb_LogBackup_2019-01-17_13-49-00.bak' WITH  NOUNLOAD,  STATS = 5,  STOPAT = N'2019-01-17T13:00:00'
GO

The log backup failed. The database is now in restoring state. Before all this, mydb.mdf and mydb.ldf files where located in the D:\db folder, now they are not there. Instead they are in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\. I tried to attach them to my local server, it fails because they are in restoring state too. I download and managed to open the with a SQL Server database repair tool, but the only hold the data from 4 months ago.

I’m currently scanning the computer for deleted files to search for the original .mdf and .ldf files, but I don’t have much hope on this because the quick scan didn’t show anything and I’m running a deep scan now.

Questions

How can I take the database out of that (restoring…) state and what will happen if I do so? This is why I decided to ask before I do anything else that might be destructive.
My goal now is to restore the database in the most recent possible state, so how can I achieve that?


texts: How to speed up SSIS data load from Oracle database?

texts: I need to load the data from a Oracle server in another country by using SSIS package. I've configured Oracle drivers, environment variables and connections in SSDT already. I'm loading about 200 MB of data (one table) and it takes about 20 minutes! to load the data. The link in the remote location is 15Mbps with 86ms of Latency. Is there a way to configure the Oracle or SSIS package to speed up the load ?


texts:
I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity.

This are the message columns :

id
sender_id
receiver_id
body
activity_id

I'm looking to get all the activities who have at least one message from both directions of message.

Here is what I mean, message id 1 :

id|sender_id|receiver_id|body   |activity_id
1 |20       |44         |hi     | 55

So for activity 55 there is only one directional messaging, if you take activity messages below :

id|sender_id|receiver_id|body     |activity_id
1 |20       |44         |hi       | 55
3 |44       |20         |hi there | 55
4 |44       |20         |who're u | 55

So above messages for activity 55 are "multi directional", meaning there are messages from both sides. In this case there are more than one message from 44 to 22 but thats ok, as long as there is at least one message from 44 to 22 and at least one message from 22 to 44 I want to get that activity_id.

I'd like to get all the activity_ids which have at least one or more message exchange from both sides.

I was thinking to do it something like this :

select distinct(activity_id) from messages
where sender_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
) or receiver_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
)

It's not really working at the moment, can someone point me to the right direction?


texts: error query did explode the postgres database

texts:
I had some problems last month. I had a bad query in Postgres (PostGIS) of a user launched during the night. This query took all the memory and I had 0% of free disk.

Fortunately, I restored the database with my dumps, it's ok, but I'm afraid for the next time.

Can I predict a bad query in Postgres and stop it immediately?

Which tools? Which scripts? Do you recommend any?


texts: Print the execution time on messages Pane?

texts:
How I can print the execution time of each query on messages Pane? For example the Select * from tbl1 runs for 10 mins then Select * from tbl2 runs for 15mins and so on..

Select * from tbl1
Go
Select * from tbl2
Go
Select * from tbl3
Go


texts: Using MySQL ver. 5.5.55 in a production env

texts:
Is it still OK to use this older version of MySQL on a production web server (LAMP on Ubuntu 14.04)? It's a low traffic volume business website. Loads fast enough. So far, no serious issues (like being hacked or something).
Also we use older version of PhpMyAdmin (ver. 4.0), so my question also regarding this too.


texts: SQL Server chooses Nested Loop join with dimensional table and make seek for each row

texts:
I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.

The Sort operation Estimation is 100 rows instead of 450 K rows and maybe affects the plan selection:

NestedLoop: https://www.brentozar.com/pastetheplan/?id=B110MZ2Pm or
NestedLoop plan

This is within a test DB. We have an additional DB with the same schema and almost the same data.

Running exactly the same query (both from SSMS) generates a different plan using Hash Join and dimensional table scan (32K reads):

HashJoin: https://www.brentozar.com/pastetheplan/?id=r1Jm7b2D7 or
Hash plan

I need a help to understand and solve the issue.

I can work around it by hint Hash Joint, but it does not make any sense that 2 similar DBs on the same instance generate different plans.

Update #1: I found that the estimated cost is different
So when SQL Server executes parallel it will choose a hash join.

With a single thread will be nested loop.

Update #2: The same issue occurred during a SELECT from the same table.
Depend on the number of columns (estimated cost). When I reduce the number of columns, the execution plan falls to a nested loop and seeks the dimension table.


texts:
I have the following table:

CREATE TABLE Train(
    Train_No integer PRIMARY KEY,
    Loco_No integer REFERENCES Locomotive(Loco_No),
    Back_Loco_No float REFERENCES Locomotive(Loco_No),
    Train_Length float CONSTRAINT Train_Len CHECK(Train_Length <= 400),
    Train_Weight float CONSTRAINT Train_Weight CHECK(Train_Weight <= 10000)
);

What I have done is written a trigger to test the Train_Length is less than 400 as followed:

create or replace
trigger TRAIN_LENGTH
AFTER INSERT ON TRAIN
REFERENCING NEW AS New
FOR EACH ROW
BEGIN
   IF :new.Train_Length> 400 THEN
       RAISE_APPLICATION_ERROR(-20000,'Train weight has exceeded maximum train length');
  END IF;
END;

I believe the only way to test that this trigger is working is by inserting some data into my train table. However I did notice that looking at my CREATE_TABLE statement I have a CHECK constraint on my Train_Length <= 400. I have temporarily tried inserting some data into my table by doing:

INSERT INTO Train VALUES (9,1,NULL,500,100);

This throws up:

SQL Error: ORA-02290: check constraint (K0954342.TRAIN_LEN) violated
02290. 00000 -  "check constraint (%s.%s) violated"

I believe that the cause of this would be because the value 500 violates the check constraint as the Train_Length <= 400. Also I noticed that my trigger is called AFTER INSERT ON TRAIN which means that really and truly the trigger is only ever called after insert on the train table. BUT! Because I have my check constraint on my Train table being Train_Len CHECK(Train_Length <= 400) this means that this is being called before the trigger is even called.

SO I believe the only way to check that my trigger does work is to drop my Train table and remove the check constraint Train_Len CHECK(Train_Length <= 400) and then try to re-run the following INSERT:

INSERT INTO Train VALUES (9,1,NULL,500,100);

If someone can tell me if this is correct?


texts: How can a session be blocked by an session that is idle?

texts:
I'm trying to identify blocking sessions using the following query:

SELECT
  v1.sid esid,  v1.blocking_session bsid,  v1.process,  v1.wait_class,  v1.event,  v1.sql_id,  v1.seconds_in_wait waitss,  v2.blocking_session bsid2,  v2.process,
  v2.sql_id,  v2.wait_class,  v2.event
from
  v$session v1, v$session v2
where
  v1.blocking_session is not null and v1.blocking_session = v2.sid
order by
  waitss desc

Here is sample output:

ESID    BSID    PROCESS WAIT_CLASS  EVENT                           SQL_ID          WAITSS  BSID2   PROCESS_1 SQL_ID_1 WAIT_CLASS_1 EVENT_1
2707    2313    8526    Application enq: TX - row lock contention   05v90pbavkptk   11      (null)  28152     (null)   Idle         SQL*Net message from client

Which indicates that the blocking session is idle. Maybe I'm missing something but I don't understand how an idle session that is not executing any statement can be blocking anything.


texts: Once in a while synchronization of the independently updated mysql databases problem

texts:
I have a production database and a test database running on MySQL with MyISAM and InnoDB tables, which are updated independently.

The Production database is updated by system and sometimes by a user.

The Test database is mostly updated by a user, and most work is done on this database.

Then at the end of every week or a month, after all verifications that data looks good on test, these two databases need to be merged.

So test will need to have all changes/updates of its own + changes/updates of a production database.  Or, the production database could be just a dump of this new test database (or vice-versa).

INSERT IGNORE and/or REPLACE INTO will not be useful as I don't want to lose any of the data, if for instance both production and test databases happened to have a record (different values) with the exact same primary key.

What I came up so far is:

Set up one way MySQL replication from a production (master) to test (slave).

If new records are added to a test database, write a MySQL trigger to update AUTO_INCREMENT value of the table in production database to be a next primary key of affected table in test database.

So the production database tables will have gaps in the primary key fields to make database merge possible later.

The only problem I see here is when MySQL server is restarted, I lose those set AUTO_INCREMENT values in a production database. (It's set to the next of the current biggest primary key)

I also can't insert just empty records in order to solve this last problem with AUTO_INCREMENT after MySQL restart. (at least it's not preferred) Because I will have to go through all queries in a web application and make sure they are not using pseudo "empty" records.

I'm wondering what the best solution would be for this kind of problem?


texts: Select distinct multiple columns with one result column

texts:
I have a table like the following:

CREATE TABLE aschema.atable
 (
  id       BIGSERIAL,
  col_a    aschema.anenum,
  col_b    aschema.anenum,
  col_c    aschema.anenum
)

and I would like to do something like

SELECT DISTINCT unnest(array_agg(col_a, col_b, col_c)) as anenum_value
  FROM aschema.atable
  WHERE anenum_value IS NOT NULL

but I do not know if it makes sense.
Maybe with an index over each of the three column I can simply select independently the three columns like that:

SELECT DISTINCT value FROM (
SELECT col_a as value
  FROM aschema.atable  WHERE col_a IS NOT NULL
UNION
SELECT col_b as value
  FROM aschema.atable  WHERE col_b IS NOT NULL
UNION
SELECT col_c as value
  FROM aschema.atable  WHERE col_c IS NOT NULL
)

But I am not sure about the performance.

Actually I have rows like

id  col_a  col_b  col_c
1   ABD    CDE    XYZ
2   CDE    null   null
3   ABD    null   null
3   FGH    LMN   null

And I expect as a result

ABC
ABD
CDE
FGH
LMN
XYZ

Any recommendation and good example?


texts: Vulnerability in Server from SQL Server 2008 R2

texts:
We scanned our systems and got a high vulnerability reported in a server.

One of the thing that got flagged was MS12-060, which is related to  MS Office 2003 Web Components that gets installed along with Analysis Services.

Can we remove that component without breaking the SQL Server engine or do we need to uninstall SSAS first and then remove the component.


texts: Why index on low selectivity column hurts query performance

texts:
The table is simple:
CREATE TABLE `t1` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `name` int DEFAULT '1',
  `LastName` int DEFAULT '0',
  UNIQUE KEY `idx_ID` (`ID`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB;

DELIMITER $$
DROP PROCEDURE IF EXISTS populate_t1;
CREATE PROCEDURE populate_t1(IN num INT)
BEGIN
    DECLARE COUNT INT DEFAULT 0;
    WHILE COUNT < num DO
        INSERT INTO t1(`name`,`LastName`) VALUES(round(rand()+5, 0), round(rand()*10000, 0));
        SET COUNT = COUNT + 1;
    END WHILE;
END $$
DELIMITER ;

CALL populate_t1(1000000);
CREATE INDEX idx_name ON t1 (`name`);
OPTIMIZE TABLE t1;

The column name has very low selectivity (i.e., only 2 distinct values). Query
> SELECT * FROM t1 WHERE Name=5;
> OK, 499845 records, Time: 0.840s

would be slower than the query which use a full table scan
> DROP INDEX idx_name ON t1;
> OK
> SELECT * FROM t1 WHERE Name=5;
> OK, 499845 records, Time: 0.258s

This performance drop is expected, but I want to figure out why.
I know that the index is like an English dictionary index in the first several pages of an English dictionary. And the index is stored in a B+tree in my example I suppose. So with an index on column name, MySQL would know which rows satisfy name=5. Then MySQL fetches these rows into the result set. I think the process above would be quicker than scanning the whole table (but I am wrong). So what I want to ask is which process makes (e.g., loading index from disk to memory, searching in the B+tree, etc.) the index scan slower than the full table scan.
Further, I know that if the table is larger, the performance difference would be larger (I still want to know why).
I would be very grateful for your help!
Thanks!


texts: INSERT INTO SELECT causing data to double

texts:
I have strange issue. I am working in a Teradata database.
In a procedure I am populating a main table by processing data via multiple intermediate tables.
But in the end only one table is populating the main table.
The syntax is something like this

INSERT INTO sch_1.main_table
(
col1,
col2...
coln
)
SELECT
col1,
col2...
coln
FROM sch_1.work_table;

Now the count(*) from sch_1.work_table results in 112rows whereas its going straight to double in sch_1.main_table i.e. 224 rows.

I am unable to share the logic of the procedure as its for a client.
But this thing has me stumped.
There are no joins involved in this statement, just a plain and simple insert from a work table to a main table.

Has anyone faced this?


texts: How to use variables in procedure (not function)?

texts:
As I understand, in Postgres, procedures are for manipulating data and functions are for reading data. I would like to:

declare a variable
select a value into a variable
insert the variable into another table
do this in a transaction
do this in a stored procedure
roll back if necessary

CREATE PROCEDURE test_variable()
LANGUAGE SQL
AS $$
    BEGIN;
        DECLARE some_id INTEGER;
        SELECT nextval('some_sequence') INTO some_id;
        INSERT INTO some_table (some_column) VALUES (some_id);
    END;
$$;

The above is not working out for me. When I search for solutions, there's so many different variables involving functions, $$, declaration, thing not returning anything; can't seem to find a simple example; I just need a clear example of the syntax.


texts: How do I uniquely update values of a text column before applying UNIQUE constraint?

texts:
I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I already had ABC-1 as an entry? Seems like a bit of an recursive problem. I probably need to do this through a cursor, selecting all duplicate rows and updating them one by one.

Does anyone have a snippet to share? I'm using PostgreSQL 9.4 and plpgsql.


texts: SQL Server 2008 R2 Standard with SSIS Enterprise

texts: I currently have SQL server 2008 R2 Standard installed with SSIS. I need a SSIS feature that is only available with the enterprise edition. Is it possible to upgrade SSIS only to enterprise while keeping the database install as is (standard)?


texts:
we have a collection in below format:

{
    "_id" : {
        "a" : 8,
        "b" : ObjectId("5b23aa9e24826b82a2553ce7")
    },
    "field1" : "value1",
    "field2" : "value2",
    "field3" : "value3",
    "field4" : "value4",
}

as per my knowledge, MongoDB creates a default index on _id field. But here in this case I want to know how MongoDB will create index on _id; is it on both _id.a and _id.b field or just on _id.b field?
also if I create an additional compound index on (_id.a,field1,field2), will it create an additional overhead? or I need to create and index on only (filed1,filed2) as _id is already indexed field.

Please suggest.
Thanks!


texts: Should I align my SQL Server file and growth sizes with the block sizes on my SAN?

texts:
It was brought to my attention that our (Hitachi) SAN handles data in 42MB blocks. It is tiered storage so each block is evaluated when the director makes decisions as to when it should promote/demote storage to faster/slower storage. In the absence of having the political weight to "pin" particular LUNs to particular tiers I am trying to do all I can to set up my SQL Server (2008 R2 Enterprise) instance for success.

As such, I was wondering if there would be any benefit/detriment to changing my file sizes and file growth sizes to multiples of 42? An example database would be SIZE=57886MB with a FILEGROWTH=5124MB.

I see the potential for extra reads (since (42*1024)/8 = 8601.6, but (42*1024)/64 = 672) but I'm not sure I'm fully grasping all of the concepts here.


texts: Not able to execute four part query within function

texts:
I am facing an strange problem

When I execute

select * from [remoteserver].[DB].schema.table_name;

it executes fine.

but executing the same within table valued function give error
Login fail with use 'login name'

Why?

Thanks!


texts: Create a trigger to update table data on another Server's database

texts:
I am creating a trigger in MySQL and I need a little help.

I have 2 websites, 2 databases (same name) on 2 different web servers, S1 & S2.

These databases have the same tables names.

I want both the user data on both the websites to be the same.

So if one user registers on S1, then that user registration information should be passed to S2.

If a user registration information is updated on S1, the same information should be updated on S2.

And the same applies for S2.

How can I create a trigger so that every time there is an insert / update / delete in database on S1, then the user table on S2 also gets automatically updated.

And every time there is an insert / update / delete in database on S2, then the user table on S1 also get automatically updated.

Is this possible? Could you provide some examples?


texts: How to test if a column definition allows nulls?

texts: I have a table with roughly 3000 rows in it and I need to modify a column definition to allow nulls. I have a change script that will perform the change but I would like to be able to re-run the script so that the change will only occur if the column definition hasn't already been changed. How do I test a column definition to identify if the column is NULL or NOT NULL?


texts:
I inherited an Oracle 11.2 server.  I wanted to see if whomever configured it before me had any alerts configured, perhaps to notify on audit events.

Logging on as sysdba, I issued this command per the examples in https://docs.oracle.com/database/121/SUTIL/GUID-8D62D6A0-99F4-465C-B088-5CCF259B7D80.htm#SUTIL1519

show alert

I got this error:

SQL> show alert
SP2-0158: unknown SHOW option "alert"

Is there any way from the command line to show what alerts on configured on the server?  Thanks!


texts:
Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this solution:

select id
from T1
where price in(
   select max(price)
   from(
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)

union

select id
from T2
where price in(
   select max(price)
   from(
      select max(price) as price
      from T1

      union

      select max(price) as price
      from T2

      union

      select max(price) as price
      from T3
   ) temp
)

union

....    same for T3 table.

Is it possiple to optimize this query?


texts: DML queries with different performance on similar servers

texts:
I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as HTTP, Redis and AMQP (it has more workload).

The idea is separate all DB2 services on dedicated servers. The problem comes with the performance of DML commands between servers. Executing the same query, the DB2 (shared server) performs better than the dedicated server (between 400ms and 3500ms faster on average in all queries). They two servers mount the same type of hardware, operating system (CentOS 6) and the database is identical (including indexes).

DB1 has CPU with 8 threads and 32GB of RAM. The my.cnf configuration is as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Fine Tuning
thread_stack = 512K
key_buffer = 1G
max_allowed_packet = 32M
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 64M
query_cache_size = 512M
query_cache_type = 1
join_buffer_size = 16M
tmp_table_size = 512M
max_connections = 2048
max_heap_table_size = 64M
wait_timeout = 3000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_buffer_pool_size = 24G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table = ON
innodb_log_buffer_size = 64M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 0

DB2 has CPU with 16 threads and 64GB of RAM. The my.cnf configuration is as follows:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

symbolic-links=0

# Fine Tuning
thread_stack = 512K
key_buffer = 384M
max_allowed_packet = 32M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 128M
query_cache_type = 1
join_buffer_size = 8M
tmp_table_size = 256M
max_connections = 800
max_heap_table_size = 32M
wait_timeout = 5000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_lock_wait_timeout = 5000
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 40M
innodb_file_per_table=1

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0

Anyone who knows what may be happening?

Thank you!


texts: MongoDB Fragmentation affect the Performance in WiredTiger

texts:
Im using MongoDB 3.6 I have a huge number of fragmentation in my collections. 300+ GB on one collection. WiredTiger is the storage engine.

I know its bad for the OS and much wasted space. This affects the memory as well if we use MMAP engine. But I don't know how the fragmentation affects the WiredTiger?


texts: Does the pgpool-II extension allow for parallel execution of any query?

texts:
I read about pgpool-II here and it states that:

  Using the parallel query function, data can be divided among the multiple servers, so that a query can be executed on all the servers concurrently to reduce the overall execution time. Parallel query works the best when searching large-scale data.

I am wondering whether anyone has experience with using this function? Does it have similar functionality as Greenplum, Aster Data or Stado/GridSQL? These all offer the ability to utilize many computers to process any SQL query in parallel, which is a huge benefit if one wants to undertake data-mining on a large dataset. I was unable to find any documentation on this on the Pgpool-II pages.


texts: MySQL - Group By - How does it determine which row to return from joined table

texts:
I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up.

I have 3 tables:

foo

bar

bar_image

And paring back all distractors, the fundamental query looks like this:

SELECT
    f.id AS foo_id,
    b.id AS bar_id,
    COALESCE(bi.image_path, b.legacy_image_path) AS image_path
FROM
    foo AS f
INNER JOIN bar AS b ON b.foo_id = f.id
LEFT OUTER JOIN bar_image AS bi ON bi.bar_id = b.id AND bi.deleted_at > f.created_at
GROUP BY
    f.id

And the sample sets:

; The following query will result in image_path = a.png due to no matching rows in bar_image

foo
    id: 1
    created_at: 1000

bar
    id: 1
    legacy_image_path: a.png

bar_image
    {empty}

; New foo row created, after image replacement
; Above query will return
; foo: 1, image_path: a.png
; foo: 2, image_path: b.png

foo
    id: 2
    created_at: 2000

bar
    id: 1
    legacy_image_path: b.png

bar_image
    id: 1
    image_path: a.png
    deleted_at: 1500

; And another iteration for sake of thoroughness
; Another foo row created, after another image replacement
; Above query will return
; foo: 1, image_path: a.png
; foo: 2, image_path: b.png
; foo: 3, image_path: c.png

foo
    id: 3
    created_at: 3000

bar
    id: 1
    legacy_image_path: c.png

bar_image
    id: 2
    image_path: b.png
    deleted_at 2500

As you can see, with the group by on the foo table, and the given join to the bar_image table, each foo is correctly displaying the proper image_path, which is desired, but I cannot explain why this is occurring.

When running the query after all the new rows were inserted, foo:1 would return two image rows from bar_image table, but the group by is truncating them to a single row, how does MySQL know to select the closest value, vs some other (seemingly random) row?


texts:
As I understand, when selecting from a table in Teradata the table is locked by default.
I would like to do a simple select * from table without locking the table. Meaning, i want to do a select and allow other users to select from the same table without having to wait for my select to be over.

I know that i can downgrade the select lock using LOCKING modifier as follows:

locking table my_table for access select * from my_table

But this will allow my operation not to wait for locks. To my understanding it will still lock the table and so other operations will have to wait for the lock.


texts: DB2, How to save all stored procedures as files

texts: How does one extract all stored procedures from a DB2 schema as individual files (without using Data Studio so it can be scripted).  I need them all so I can uploaded to source control.  I have found a way to do it via command line if one has access to the server, but do not have server access.


texts: Which should I use on my DB? - Clustered Index or Non Clustered Index or Both

texts:
I just need advice on how I am going to optimize my database.

Date ---- Time ---- Area ---- Block ---- Data1 ---- Data2 ---- Data3 ---- DataN

I have that format of tables on my database. Each tables has 30-days of records with hundred thousands of records each data. Uploading of data to database is every morning. The data of yesterday will be uploaded.

The most common queries to run is grouped by Date or Date and Area or Date and Area and Block. Now, in order to make queries faster, I found out using indexes. I used indexes before. But that was when I am using a primary key. In this table, I didn't use primary key because it is totally unnecessary.

Now, I am really confused on how I am going to optimize this. Based on this reference on Column Considerations part, clustered index is not a good choice on Columns that undergo frequent changes. I am really confused.

Please help me if I need to use clustered index here or just non clustered or both.


texts: How to check AUTO_CREATE_STATISTICS/AUTO_UPDATE_STATISTICS is enabled for database

texts:
I have a project database that maintain different databases from different environnement (dev, test, prod, predprod...), I need to check if AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICSis set to OFF/ON against each DB and if its OFF- alter it to ON.

ALTER DATABASE  DB
SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE  DB
SET AUTO_UPDATE_STATISTICS ON

How can i achieve this?


texts: SQL Server restarts, unable to find the reason

texts:
One of our SQL Server instances restarts itself in a clustered environment, but it is not fail-overed. When I checked the error logs, event viewer and cluster log I'm unable to find any particular reason for what it causing these restarts, but in failover cluster manager --> Roles --> Critical events I found this error:

Cluster resource 'SQL Server' of type 'SQL Server' in clustered role
'SQL Server (MSSQLSERVER)' failed. Based on the failure policies for
the resource and role, the cluster service may try to bring the
resource online on this node or move the group to another node of the
cluster and then restart it. Check the resource and group state using
Failover Cluster Manager or the Get-ClusterResource Windows PowerShell
cmdlet.

Can I know if there is any specific reason for SQL Server restarts with the above error, or is there anything we can configure for the SQL Server error logs to capture the errors of these restarts or failovers or any audits?
Both Windows servers are not rebooted
Server is physical server
It is an Active/Passive clustered environment
Both servers are running on Windows Server 2012 R2
Microsoft SQL Server 2014 Enterprise Edition (64-bit) on Windows
I verified all 6 errorlogs, and events in event viewer, and cluster.log file also.


texts: Transaction Identifier - possible with MySQL?

texts:
I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if everything goes OK.

What I need is to detect if the connection at the end is still within the same transaction, as the user code can query commit/rollback and start a new transaction.

What is the industry practice for this? Can MySQL database return some transaction ticket/ID? Or MySQL store transaction only variable, which will be detectable after commit/rollback?


texts: How to execute a query from psql without waiting for the result?

texts:
My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there.

I can connect to my database from home as usual with psql.

How can I tell the remote server to execute my query from the terminal without having to wait for a response?

(postgresql-9.2 , linux enviroment)

Edit:
I'm open to other solutions, it's not necessary to use psql


texts: Performance on same table vs 1:1 relationship in MySQL

texts:
I have a two tables with 1:1 relationship, one table is called questions with 14 fields and the other is called links with 3 fields, the foreign key is in the question table. The tables are both MyISAM.

In the links table the fields are:
linkId, questionUrl and count

The reason for having the links table is that I can track clicks each time a user clicks on a link it increments the corresponding row.

Am I correct to leave this, or move the 3 fields from the links table to the questions table? My reasons for having a separate table is because the links table will be accessed and updated a lot more (each time any link is clicked).

Are there any performance issues or caveats for either?


texts: Filtering extended events with configuration table

texts:
I've created E.E. session to audit logins, but there are many logins that I need to filter out.
I'm wondering, if there is a better option(like creating conf. table with unwanted logins and filter sqlserver.username using those values) than just adding them one by one via gui\script.


texts: Improve query speed for large, wide table

texts:
I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and will not be updated. (Most columns are omitted here to save space.)

    CREATE TABLE claims
   (
     spending double precision,
     releaseid bigint NOT NULL,
     priceinclude bytea,
     costsharing double precision,
     hcciprofode bytea,
     memberlinkeid bigint,
     age integer,
     orgid integer,
     gender character(1),
     membercity character varying(22),
     memberstate character(2),
     zip character varying(5),
     principaldiagnosis character varying(6),
     CONSTRAINT claims_pkid PRIMARY KEY (releaseid)
   )
   WITH (
     OIDS=FALSE
   );

Indexes:
"claims_pkid" PRIMARY KEY, btree (releaseid)
"claims_age_idx" btree (age)
"claims_memberlinkeid_idx" btree (memberlinkeid)
"claims_principaldiagnosis_idx" btree (principaldiagnosis)
"claims_serviceprovider_idx" btree (serviceprovider)
"claims_zip_idx" btree (zip)

The indexes shown above are the only ones I've added so far. I am running the database using Windows 7 with the following hardware:

64-bit Windows 7
32.0 GB RAM
Intel Core i7-4770 CPU @ 3.40 GHz

My main question involves what I can do to improve query speed without buying a dedicated server. I am trying to demonstrate a proof-of-concept to my supervisors to show them that SQL would be faster than the statistical package the currently use, STATA. I have optimized the default settings of PostgreSQL as follows:

shared_buffers=8GB
effective_cache_size=24GB
work_mem=1GB
maintenance_work_mem=1GB
checkpoint_segments=128
checkpoint_completion=0.9
wal_buffers=-1
default_stat_target=500

However, the queries are still slow. For example, the following query

EXPLAIN ANALYZE VERBOSE
SELECT * FROM claims
WHERE gender='F'
AND age BETWEEN 16 AND 20
LIMIT 100000;

returns this:

Limit  (cost=0.00..226622.43 rows=100000 width=427) (actual time=0.029..9509.028 rows=100000 loops=1)
Output: spending, releaseid, priceinclude, costsharing, hcciprofcode, htype,
hcciotpcode, drgmpr, medsurg, typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate, zip, admissiondate, serviceprovider,
servicenpi, ser (...)"
->  Seq Scan on public.claims  (cost=0.00..4778859.76 rows=2108732 width=427)
(actual time=0.028..9497.420 rows=100000 loops=1)"
Output: spending, releaseid, priceinclude, costsharing,
hcciprofcode, htype, hcciotpcode, drgmpr, medsurg,
typecode, claimcat, memberlinkeid, age, orgid,
insurancetype, gender, membercity, memberstate,
zip, admissiondate, serviceprovider, servicenp (...)"
Filter: ((claims.age >= 16) AND (claims.age <= 20) AND (claims.gender = 'F'::bpchar))"
"Rows Removed by Filter: 3194024"
"Planning time: 0.190 ms"
"Execution time: 9519.455 ms"

Should I be creating a new view or a smaller table to access frequently used columns? I have never created a database this large so I am quite perplexed as to what to do!

UPDATE: My goal is to be able to query this database in a reasonable amount of time for exploratory data analysis. 15 seconds for queries is reasonable; 5 minutes for queries on the whole table is not. I am not sure how best to deal with this large table in order to achieve that goal, which is what I would like help with. (Apologies if this is a little vague.)


texts: Transactional Replication times-out with Database Snapshot on subscription

texts:
I have a VLDB that is being replicated using transnational replication. The performance has been terrible, and the process frequently times out.

After much investigation, including Service Packs and hotfixes, page file increase, TempDB tweaks, RAM increases, SAN tests, even a new node in the cluster(!) we have finally found that the issue is being caused by a Database Snapshot on the subscriber.

I have seen a similar issue with index rebuilds, where the Ghost clean-up task was blocking the Copy-On-Write thread, and the resolution was to disable the Ghost clean-up task.

Unfortunately, in this case, disabling the ghost clean-up task has not resolved the issue, and replication continues to get Query Time-outs, usually during delete operations. Increasing the query timeout period simply increases the amount of time before it times-out!

Has anybody come across this, or have any ideas as to how to resolve the issue? Removing the Database Snapshot, even during the period of replication is not possible, as it is supporting user reports.


texts: How to properly configure pgBouncer transaction level pooling?

texts:
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections?  Also, how should I determine the number of max_connections and max_transaction_locks on postgresql.conf?

Our application has over 200 concurrent processes, each of which regularly reads and writes to the database.  Using session-level, I would expect the number of pooled connections to be around the number of concurrent processes working on the DB at a given time, but I don't really know how to translate that to transaction-level pooling.

Thanks very much!  Let me know if more information is necessary and I will gladly oblige.


texts: Migrating a database from SQL Server 2012 to the cloud

texts:
I have a sample SQL Server database which is running on my computer. I have a cloud database account on AWS (free account). I want to migrate the database, with all tables, to the cloud.

How can I do that? Are there any open-source or free tools?


texts:
I began receiving these errors consistently after running a script that collects and stores data in psql:

  Could not connect because of FATAL:  remaining connection slots are
  reserved for non-replication superuser connections

I am running psql on Ubuntu.  So I decided to restart psql with:

sudo service postgresql restart

Now however, psql has shut down completely

Could not connect because of could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

I am completely new to maintaining psql on a server and would be grateful for any advice.

Where do I even begin to solve this problem? How can I get psql to start again without losing any data that is in this massive database?


texts: Synchronization report for uncontained objects across each of the replicas in HAG

texts: I have seen How to transfer logins and passwords between instances of SQL Server , Synchronize Availability Group Logins and Jobs and SQL Server 2012 Availability Group Add-in to synchronize unconstrained objects between replica. There are a several more articles about "how to do it". What I could not find is a script to just report (not sync) the objects that are not in sync.


texts: Decoding timestamp number in "change in schema version between compile time and run time"

texts:
I am trying to track down a "who/why" for the error

<...> reported a change in schema version between compile time ("185302088475260")
and run time ("185448117384521") for table <...>

I am aware of the resolution(s) (recompile the SPROC, or for one of our others that takes ~2 hours, don't touch schema in the linked server until it is complete), however the schema for the specific tables shouldn't be changing. To assist in tracking down who/where/why I would like to decode those compile/run time numbers into some form of datetime or something such that I can narrow down the time frame I need to investigate.

I am having difficulty in finding exactly what those numbers represent, they appear not to be unix timestamps (too many digits), nor .net Ticks, nor a win32 epoch (giving me some year in 1601? Although the "time" part might be right?).


texts:
Hey guys I am trying to study for an exam and I cant seem to get my head around normal forms.

here is one question:
R1 [a b c]
fd1: {a,b} -> {c}
fd2: c -> b

I know the candidate keys are
{a,b}
{a,c}

But how do i determine what normal form this is in?


texts: Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key

texts:
Scenario:

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available. Password used to protect the DBMK 1 is unknown, encryption is enabled by SMK A.

Server2 running SQL Server 2012 with SMK B, db2 with DBMK 2, symmetric key and certificate available. Password used to protect DBMK 2 is unknown, SMK B used for encryption.

A restore of db2 to Server1 is required. There's one encrypted column in a table that stores passwords for an internal ASP.NET application.
There are other applications accessing encrypted data in db1 on Server1 .

Question:

Best option to restore db2 to Server1 and access the encrypted data without causing issues to db1 ?

Tested:

A simple restore will bring the db2 data, symmetric key and certificate to Server1, but SMK A can't be used to decrypt the data: "Please create a master key in the database or open the master key in the session before performing this operation."
Restored SMK B on a test server and all was good. Can't use this solution on Server1, as I believe that restoring SMK B to Server1 (overwriting SMK A) will disable my access to the encrypted data in db1. Don't want that to happen.

Any advice ? Thank you.


texts: Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

texts:
I have an intervals table which stores a start date and an end date for variables variable_id (four columns : id, start, end, variable_id).

Clients compute new intervals for one variable and store them in that table. To preserve consistency (for a variable_id I don't want intervals to overlap), clients first check (select) what intervals we have and then insert, delete and/or update accordingly (or do nothing).

If two clients do that simultaneously there is of course a risk that I insert overlapping intervals, a thing I don't want.

Well actually my question is simple: what should I do?

It seems that a SELECT .. FOR UPDATE doesn't work, because this locks existing rows, and that's not enough for me, as rows may be inserted. I'd rather need the result of "SELECT * FROM intervals WHERE variable_id = 1234" to be "safe" in some way.

I have implemented a solution that uses SERIALIZABLE transactions. The result is that if two clients enter this select/update-delete-insert transaction, one will fail (could not serialize access due to read/write dependencies among transactions or could not serialize access due to concurrent update), in which case I just have to run again my select/update-delete-insert logic to try to insert more appropriate intervals.

The issue here is that the serializable transaction seems to lock the entire table. But if a client wants to update variable 1234's intervals, while another one wants to update variables 4321's, there should be no problem, both transactions should be able to run happily.

I'm no SQL pro and can't even decide whether this is a simple classic sql question or a difficult issue.

Some remarks :

PostgreSQL 9.1.1
There's actually a lot more going in the transaction. Not only I update the intervals but I also insert corresponding variables' values in another table. Intervals in the intervals table are the intervals on which we have the variable's values. However I don't think this interferes with the issue described above.


texts:
Is there GUI for Couchbase to explore data (especially views), similar to PGAdmin 3 or MySQL Workbench or RoboMongo

EDIT: ah, I found the button mentioned by this link


texts: Extract insertions that failed because of size limit from SQL Server Express Transaction Log

texts:
I have a SQL Server Express database with Full Recovery Model.  Several months ago, the database hit it's size limit (10GB) and some data did not successfully get added to the database.  Several weeks after that point, the database began receiving monthly full backups, but never any transaction log backups.  The transaction log is still an overly sized file (>200GB). Both files are free to grow without limit.
Is it possible that this transaction log will contain the data that failed to be inserted several months ago, or is the file just that big because of no truncation/shrinking?
I appreciate that monthly backups don't make any sense with a Full Recovery Model. That was just the as-found condition.


texts: Is it a bad practice to keep Master and Slave (Replica) Database on Same Server?

texts:
I have a database A (master) on server 1. I need to read from database A perform aggregations and write on database B. Since database A has a very high traffic, I cannot read directly from database A without messing up performance.

Is it a bad practice to keep database A (master) on server 2 and create a server 2 with a read-replica of database A and database B? In other words, I would have a master and a slave database on the same server. Is it OK ?


texts:
I trying to get info about why MySQL does not using my index when I create a inner join and trying to ORDER BY on the end.

I have my SQL query here:

SELECT
    *
FROM
    product p INNER JOIN productStore ps ON p.productUUID = ps.productUUID
ORDER BY
    ps.storeTitle
LIMIT 50;

When I'm using order by this select take over 3,5 sec, when i remove order by its taking like 1,6ms to run the same SQL, my explain SQL is follow

With ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ps  ALL PRIMARY NULL    NULL    NULL    942187  Using filesort
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 16  foeniks_core.ps.productUUID 1   NULL

Without ORDER BY:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ps  ALL PRIMARY NULL    NULL    NULL    942187  NULL
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 16  foeniks_core.ps.productUUID 1   NULL

the field there not are indexing right is a varchar on 282 length.

My table design is here:

CREATE TABLE `productStore` (
  `productUUID` binary(16) NOT NULL,
  `storeUUID` binary(16) NOT NULL,
  `distributorLastUsed` binary(16) DEFAULT NULL,
  `storeTitle` varchar(282) DEFAULT NULL,
  `storeUrl` varchar(282) DEFAULT NULL,
  `storeDescription` text,
  `storeDescriptionDemo` text,
  `storePrice` int(11) NOT NULL DEFAULT '0',
  `storePriceNext` int(11) NOT NULL DEFAULT '0',
  `storePriceCost` int(11) NOT NULL DEFAULT '0',
  `overwrites` int(11) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `added` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `allowDisplay` tinyint(1) NOT NULL DEFAULT '0',
  `activated` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productUUID`,`storeUUID`),
  KEY `productStoreLanguageToStore_idx` (`storeUUID`),
  KEY `productStoreToDistributor_idx` (`distributorLastUsed`),
  KEY `storeUrl` (`storeUrl`(180)) USING BTREE,
  KEY `testStoreTitle` (`storeTitle`(182)),
  CONSTRAINT `productStoreToDistributor` FOREIGN KEY (`distributorLastUsed`) REFERENCES `distributor` (`distributorUUID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `productStoreToProduct` FOREIGN KEY (`productUUID`) REFERENCES `product` (`productUUID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `productStoreToStore` FOREIGN KEY (`storeUUID`) REFERENCES `store` (`storeUUID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Product table:

CREATE TABLE `product` (
  `productUUID` binary(16) NOT NULL,
  `productManufacturerUUID` binary(16) NOT NULL,
  `productManufacturerSKU` varchar(40) DEFAULT NULL,
  `productEan` varchar(40) DEFAULT NULL,
  `cnetID` varchar(10) DEFAULT NULL,
  `edbID` int(10) DEFAULT NULL,
  `overwrites` int(10) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `added` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `activated` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`productUUID`),
  KEY `manufacturerSKU` (`productManufacturerSKU`(16)),
  KEY `productToManufacturer_idx` (`productManufacturerUUID`),
  KEY `cnetID` (`cnetID`),
  KEY `productEAN` (`productEan`),
  CONSTRAINT `productToManufacturer` FOREIGN KEY (`productManufacturerUUID`) REFERENCES `manufacturer` (`manufacturerUUID`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts:
I am testing my application I need some code that stable simulates the deadlock on database site (sql script if possible).

Thank you.

ADDED:

Reproducing deadlocks involving only one table


texts: How to fix mysql uppercase query script in php

texts:
I am currently working on the website that uses ADODB library. In entire website all the queries are written in UPPERCASE.
The problem is when I run the query it doesn't work because of table name which is UPPERCASE. But when I change the table name to lowercase it works.
$sql = "SELECT * FROM MEMBERS where USERNAME = '$username'";

$db = ADONewConnection('mysql');
$db->debug = true;
$db->Connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_NAME);

$resultFriends = $db->Execute($sql);

while ($row = $resultFriends->FetchRow()) {
        var_dump($row);
        die;
}

Here is the error I get:

ADOConnection._Execute(SELECT * FROM MEMBERS where USERNAME = 'fury', false) % line 1012, file: adodb.inc.php
ADOConnection.Execute(SELECT * FROM MEMBERS where USERNAME = 'fury') % line   15, file: index.php

I have read some articles about it but that doesn't help. e.g.
How to make MySQL table name case insensitive in Ubuntu?
Bear in mind I don't want to change the scripts. There are 1000 files and 10000 places.
Is there any library or are there any way that I can run this queries without error?


texts:
Migrating a database from SqlServer to Mysql using "MySQL WorkBench 6.3 CE" using ODBC data source as default methode to use to connect to the RDBMS.

-> Source Selection Test connection is Successful
-> Target Selection Test Connection gives Error Calling Python module function DbMySQLFE.connect
I have researched alot and few folks blaming SSH connection over TCP. If its the case Can I disable SSH over TCP??
I have attached the Screenshot. Please respond its really urgent. thanks


texts: Is there a setting in MySQL that will allow you to see the columns of a table on a query, even if the table is empty?

texts:
When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample.  Sometimes, the table I would like to look at has no data, so I get a completely empty query.

Now, I know I can use something like "show create table [table_name]" to see the query; similarly, I am aware that it's possible to show the tables with a join statement, but the syntax is just complex enough that I don't always remember it, and I don't want to add complexity to every simply query I want to make, just to handle the occasional empty table.

Thus, I was wondering:  is there a setting in MySQL's client, that would allow me to see the column names every time, even when the table is empty?


texts: Append and add information from rows above

texts:
the title is not really helpful. Sorry. I better explain with code. I need a query in MySQL for the following problem:

Build schema:

CREATE TABLE AsIs
(
`PN` VARCHAR(10),
`Origin`VARCHAR(10),
`Sub`VARCHAR(10)
);

INSERT INTO AsIs
VALUES ('11111', 'List1', '22222'), ('98765', 'List2', '123456');

I create a table with the column subs:

CREATE TEMPORARY TABLE `Subs` SELECT  `sub` AS `PN` FROM AsIs;

I create a table to union the values from table AsIs and table  subs :

CREATE TEMPORARY TABLE `UnionAsISwithSubs`
SELECT `PN` FROM AsIs UNION SELECT `PN` FROM `subs`;

And finally I create the last table to add the information from the AsIs table:

`CREATE TABLE `FinalList`
SELECT `UnionAsISwithSubs`.`PN`,`AsIs`.`Origin`,`AsIs`.`sub`
FROM `UnionAsISwithSubs`
LEFT JOIN AsIs ON `UnionAsISwithSubs`.`PN` = `AsIs`.`PN;

There are for sure better ways to do this, but my main question is, how can i update the column 'Origin` with the values from the row above. So it should like this:

CREATE TABLE ShouldBe Like AsIs;
INSERT INTO ShouldBe
VALUES('11111', 'List1', '22222'),('98765', 'List2', '123456'),
('22222', 'List1', ''),('123456', 'List2', '');

Many thanks in advance for your help.

I have simulate it with SQL fiddle. Please follow the link:

http://sqlfiddle.com/#!9/dd792/4


texts: Database Design: Can only sell to X customers per given timeframe (ex: 11am to noon)

texts:
I'm attempting to implement a system that limits the amount of total orders the system can accept for for a given timeframe.

Scenario: a business can only fulfill 5 orders per hours. Once that timeframe is filled, no more orders can be accepted. This feature would reset daily.

High Level Design:
Business Table - holds business information, etc. (structure: id(INT), name(VARCHAR), address(VARCHAR))

Timeframe Table - holds available timeframes for the day. (structure: id(INT), start_time(TIME), end_time(TIME))

Business Timeframes Table - reference business with available timeframes. (structure: id(INT), business_id(INT), timeframe_id(INT)).

Having set this up, I can assign a business with available timeframes. To create a counter, I'm thinking that I should create another table, which references a business_id, timeframe_id, available,  filled, and possibly datetime/timestamp.

Since the counter would reset daily, and there is a possibility of implementing future orders (deliver for today vs tomorrow vs day after), what would be the best way to go about this design?


texts: Deny users permission to create tables in master

texts:
I have noticed that I can create tables, schemas, stored procedures etc. the master database.

I don't know what is granting this permission. It's annoying, because when we open a *.sql file in SSMS, it's related by default to master. If a CREATE operation is executed without noticing that and setting the correct database context, the table is created in master.

If we didn't have the permission, we'd get an error, notice it and change the database context. With the permission, the table is created and we only see the mistake later, and need to drop it and recreate. I noticed that there are already some tables created by mistake in master.

I want to revoke that permission from every human user, but I fear breaking something. I don't know if some SQL Server routine needs permission to create there.

What's the best way to revoke create permission from master without breaking SQL Server?


texts: Deadlock from mysqldump --single-transaction, is it possible?

texts:
Is it possible to get a deadlock if you ran mysqldump with the argument --single-transaction ? or any other complication ?

If it is used to backup a live site with many other querys running at the same time.

Extra info:

The mysqldump process could take over few minutes to complete.

The tables are innodb.

EDIT

I am concerned about the deadlocks errors that could happen on the live applications relying on the database during the time where mysqldump is running.


texts: dm_exec_describe_first_result_set_for_object for multiple result sets

texts:
Does a procedure exist to return metadata for all result sets in a stored procedure?

Something like sys.dm_exec_describe_first_result_set_for_object, but for all result sets?

Eventually, I will want to discover the metadata for every result set in every stored procedure in the database. For now, I would settle for just the result sets that sys.dm_exec_describe_first_result_set_for_object cannot describe. That is, the 2nd, 3rd, and Nth result sets.

Looking at using SQLCLR to do this now:

How to save results of a procedure with more than one result set
tSQLt - DB Unit Testing for SQL Server - ResultSetFilter.cs


texts: How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!

texts:
while running the following query:

-- DATEADD (datepart , number , date )

DECLARE @dt DATETIME
SELECT @dt = DATEADD(month,-6,getdate())
select @dt
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @dt

I get lots of locks and blocks, possibly because this command has not been run for a while, if ever.

Is there a way to find out how much is there to delete in each of the involved tables, before I actually run or plan to run this command?

I use dateadd to calculate 6 months.

Within sp_delete_backuphistory the following tables are trimmed:

  sp_delete_backuphistory must be run from the msdb database and affects
  the following tables:

  backupfile

  backupfilegroup

  backupmediafamily

  backupmediaset

  backupset

  restorefile

  restorefilegroup

  restorehistory


texts: Declaring and using variables in triggers

texts:
I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem.

DELIMITER $$

USE `pucko`$$

CREATE TRIGGER `after_delete_Alien`
AFTER DELETE ON `Alien` FOR EACH ROW BEGIN
    DECLARE vapenid INT;
    DECLARE alien_count INT;
    DECLARE rymdskepp_count INT;

    SET vapenid = (SELECT vapen FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien);

    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = vapenid; // Debug, this shows that vapenid is null?

    DELETE FROM `Alien_Använder_Vapen` WHERE idAlien = OLD.idAlien;

    SET alien_count = (SELECT count(*) FROM `Alien_Använder_Vapen` WHERE vapen = vapenid);
    SET rymdskepp_count = (SELECT count(*) FROM `Rymdskepp_Använder_Vapen` WHERE vapen = vapenid);

    if(alien_count < 1 && rymdskepp_count < 1) THEN
        DELETE FROM `vapen` WHERE idVapen = vapenid;
    END IF;
END$$

The problem I am having is when vapenid is/should be set. I know that there is an alien with id "abc". I know that there is a vapen with id 1. In Alien_Använder_Vapen there is a row with alienId abc and vapen as 1. I use the SIGNAL to debug a bit what the value of vapenid is, and mySQL returns

  delete from Alien where idAlien = "abc"    Error Code: 1231. Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'

This makes me think that NULL is returned from the query in vapenid, but that doesn't make sense, as SELECT vapen FROMAlien_Använder_VapenWHERE idAlien = 'abc' in a normal query returns 1 correctly.

I've been trying to figure this out for quite some time now but I just can't see what I'm doing wrong. Is my syntax or logic wrong/weird?


texts: A table with many records get slow to write and read

texts:
I have a payment service which has the transactions table on MySQL along with other tables:

transactions
-------------
- user_id
- time
- amount
- card_id

Every minute hundreds of transactions occur and many users check their app for transaction history. Sometimes, it gets slow to read and write because of high high load.

What would be best approach to deal with this situation?
I am thinking to move transactions table from MySQL to NoSQL technology (MongoDB or Cassandra). Or can I solve this problem on MySql itself?

UPDATED

Transactions schema:

CREATE TABLE `transactions` (
  `id` int(10) NOT NULL AUTO_INCREMENT
  `userid` int(10) DEFAULT NULL
  `store_id` int(10) DEFAULT NULL
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `amount` decimal(20,2) DEFAULT NULL
  `note` varchar(255) DEFAULT NULL
  `status` int(3) DEFAULT NULL
  `error_mes` varchar(250) DEFAULT NULL
  `card_mask` varchar(100) DEFAULT NULL,
  `card_id` int(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tr_store_id_id_fk` (`store_id`)
) ENGINE=MyISAM AUTO_INCREMENT DEFAULT CHARSET=utf8;

Queries:

- select * from transactions where userid='xxxx'
- select * from transactions where userid='xxxx' and created_at between 'xxx' and 'xxx'
- select * from transactions where userid='xxxx' and card_id='xxx'
- select * from transactions where userid='xxxx' and store_id='xxx'


texts: Bidirectional realtime synchronization between Oracle and SQL Server tables

texts:
I have to sync data between Oracle and SQL Server database tables. And I'm actually looking for some advice on which technologies to use or how to address this.

At the moment I'm doing the Oracle → SQL Server direction.

Oracle table inserts, updates and deletes are written to temporary tables and a console application picks it up. The downside of this solution is that it's based on scheduling of the console application and because of that there is some delay of syncing (Task Scheduler on console application). A perfect solution would be to sync the Oracle data changes in real-time to SQL Server.

I was thinking about making a Data Flow using SQL Server Integration Services Package. But how can I call this SSIS package when a data change happens on Oracle?

My question is: can we write data to a temporary table using a trigger, but can we also execute a SSIS package or a console application at that time (I mean real-time) and not on a scheduled basis?

Thanks for any advice and sorry if I've written something wrong. I'm not a DBA.


texts: What locks are taken during statistic operations in SQL Server?

texts:
What locks are taken during the various statistic operations below in SQL Server assuming the default options of auto create stats and auto update stats are set to true?

Automatic creation of a statistic object
Automatic update of a statistic object
Manual creation of a statistic object
Manual update of a statistic object

Is there any difference in the locks taken in scenarios 1 & 2 above when statistics are set to update asynchronously?


texts: Allow user to GRANT certain permissions to others

texts:
I have tried googling this but may well be using the wrong terms... permissions and the like aren't my strong point.
What I want to do is first create a role - eg 'Snr_Analyst' - with certain permissions to read everything, and alter particular schemas in a database. That bit I can do.
I also want that role to be able to grant read permissions on everything in that database to any other user, but not to grant any higher permissions.
Is this possible?
I'm currently using SQL Server 2014.


texts: Reset all sequences so that they continue after max(id) + 1?

texts:
It looks like I messed up a database migration and while all sequences are there, they would start at 1, which triggers errors like:

  django.db.utils.IntegrityError: duplicate key value violates unique
  constraint "django_admin_log_pkey" DETAIL:  Key (id)=(2) already
  exists.

Is there a query/script I could run that would run across all tables in the database, look at any columns tied to a sequence and reset those sequences to max(column) + 1?

Using PostgreSQL v11.


texts: MySQL .frm file format: how to extract column info?

texts:
Is there a more up-to-date specification for MySQL's .frm files than the one on dev.mysql.com? I'm trying to parse it manually (don't ask... just experimenting; not trying to recover anything).

The first part on "Table 11.1 .frm File Header Section" mostly matches up, but after that I'm not sure what to do.

"Table 11.2 .frm File Key Information Section" says the "Name of engine" is at 0x101a, but I'm finding "InnoDB" at 0xAA39 in my .frm file. I'm guessing those offsets aren't actually exact, and they depend the number of indexes and such.

How can I figure out the actual offset for where the "Table 11.4 .frm File Column Information Section" begins?


texts: Confused about what to use to create the data

texts:
I want to create a database for myself. The data has complex grouping. I don't know which system to use to create my database. The data is in groups and sub-group forms, it almost has 5-6 layers of subgroups. I tried SQL but such complex sub-groups are not possible in it.

I really need to create the database. Hope for some good advice.

This is the type of grouping I want to do and I am searching for a kind of system that can save such data in a way that when I want to filter the data to find something I can get the results.

EG: if I search for high toughness properties of a material then MS and high carbon steel should appear.

Hope this helps.


texts:
I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three columns when I use the following code. What am I doing wrong?

declare
    @TempTable table (idx smallint Primary Key IDENTITY(1,1), OneId int, OtherId int)

declare
    @OneId int,
    @OtherId int,
    @date datetime = dbo.GetFloorDate(getdate()),
    @i int = 1

insert @TempTable select Id, OtherId from One where @date = (select dbo.GetSomeDate (Id))

select MessageId = 9999, OneId, OtherId into MessageQueue from @TempTable

select * from MessageQueue


texts: Insert rows in a table, via a row-level trigger, activated after insertion in that same table

texts:
I have to design a schema and trigger system in SQL that will save words and their synonyms.

I thought of this basic schema:

create table words (
    id int primary key auto_increment,
    word varchar(50)
);

create table synonyms(
    w1 int references words(id),
    w2 int references words(id),
    primary key (w1, w2)
);

Another requirement is to make the synonyms relation symmetric (if a is a synonym of b, then b is a synonym of a and the table synonyms should be updated accordingly).

I thought of using a trigger that looks like this:

create trigger MakeSynonymsSymmetric
after insert on synonyms
for each row
insert into synonyms
values (new.w2, new.w1);

Which of course won't work because the trigger activates itself generating an infinite loop (or at least that's my understanding).

Trying to insert into synonyms gives me this error:

  Can't update table 'synonyms' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Any way around this problem by defining the schema in a different way, but always using triggers?


texts: Comments aren't supported in my HiveQL version, trying workaround using a FALSE IF statement that doesn't select a column

texts:
I can't currently use inline comments with -- or multiline comments with /* */ in my HiveQL scripts and I'm trying to find a workaround.
So far I've thought of using a redundant IF statement as follows:
IF(FALSE, 'my comment here', NULL)
i.e. include my comment in the IF statement, but have the IF statement do nothing (not even select a column of NULLs which the above statement does). I strictly cannot have the above IF statement create a column, because the next step in my workflow requires very specific columns.
Basically, is there anyway to have an IF statement not select a column? I have scoured SO and the net, but have yet to some up with a definitive answer to this.
*Note that I am using HiveQL which is very limited (so limited that it doesn't support inline comments).


texts: Strategy-design of a database for medication

texts:
I am working on a database where it contains 3 main tables.

The first table is a list of medications with auto increment IDs.

id   name

1    paracetamol 500mg

...

The second table will detect how many paracetamol box we have with a foreign key of id from the first table:

q_id   id   quantity

12     1    5

The third table will indicate each box how much pills does it contains and the barcode of each box:

p_id q_id nbr_pills_per_box barcode

100  12   30                32445332223

101  12   35                ...

102  12   30                ...

Does these 3 tables make a relational database or should I change the design and even the concept ?


texts: MySQL Workbench fails to store password in vault

texts:
I am trying to access my SQL server through MySQL Workbench. So I click the + sign to create a new connection.

At this point I choose a connection name and enter the hostname, username, and port. Then I click "Store in Vault" to store my password.

So I then enter my password to store.

At this point I click "Test Connection", but despite having just stored my password in the vault, I am yet again prompted again to enter my password.

I comply and enter my password once more. This time I get a connection failure message:

I know that my username, password, hostname, and port are all correct.

I just wanted to know if the fact that MySQL seemingly 'forgets' that I stored my password in the vault is indicative of any particular errors on my part.


texts: Attempting to identify minimal functional dependencies in a Compact Disc details scenario

texts:
I believe this relation/table is in first normal form (1NF):
Collection (cd_id, title, label, artist, type, country, song_id, song_title, length)
Note: Bold means keys.
What I have managed to identify:
{cd_id, song_id} → {song_title, length}
{cd_id} → {title, label, artist, type, country}

Is this correct, is it correct to say that it meets 1NF?
Are there more functional dependencies I am missing?

Additional details
Contextual information and/or sample values of the attributes/columns involved:

CD_id: 009329
Title: Title of the CD
Artist: Nickelback
Type: Group | Solo
Country: References the artist’s country or origin
Song_title: "Burn It to the Ground"
Length: 3:32 mins

Extras
Per song under a CD, meaning that there will be duplicates. Every time a song belonging to a CD is added.


texts: Count 3 hours overtime with in a given date time window

texts:
I have to implement an access control system for a company, that will be able to report the access of each employee in a specific area of the building in order to provide a report.
One of the requirements is to provide a list of the employees with more than 3 hours overtime with in a given date time window.

The table schema of the table that holds the required information is the following.
ACTION states if the employee entered or left a specific level of the building. The rest are the time, the employee and the level.

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

a sample result set

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

I believe I know how I can estimate the overtime considering the sum up of the time each employee spent inside the premises of the building which will give the total hours. But I only can imagine how I could do it in the application layer.

Is there a way I can estimate the overtimes purely with SQL?

EDIT

According to the comments there must be a strict sequence among the enter - leave actions therefore a created the following data set.


texts: Small Squares or Blocks after string in table field

texts:
My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query OPENQUERY command (displayed below).

I ran view A as a SELECT script in a new query window. Assuming the field to be varchar data type but not knowing the length, the LEN(VendorNo) returns 6 to 8 chars on Dev and 21 chars on Prod (with the remaining 13, or so, invisible).
When using SQL BIDS or exporting to Excel (on Prod), I see a series of small squares or blocks after the string. The ASCII equivalent is CHAR(32).

The service pack versions on both servers were different. The Dev server: Product Version - 9.00.4229.00, Product Level - SP3 and Edition – Standard.
The Prod server: Product Version - 9.00.1547.00, Product Level – RTM and Edition is Standard.

On a side note, I included a GROUP BY clause for “view A” in a project and noticed the characters appeared, but by removing GROUP BY also removed the square blocks. Any help or direction would be appreciated. Maybe updates to the SP on Prod? We also have a second test server that shows the same results as the Prod server.

-- My code --

CREATE view nav.vw_Vendor
AS
SELECT * FROM  OPENQUERY(NAVISION,
'SELECT Vendor.No_ AS VendorNo
FROM Vendor Vendor')

-- View A

SELECT VendorNo FROM nav.vw_Vendor


texts: SQL Server Availability Group - Listener configuration problem

texts:
Platforms:

Windows Server 2016
SQL Server 2016
SSMS 2018

Problem:

  Create failed for Availability Group listener 'lsn'.

  None of the IP addresses configured for the availability group
  listener can be hosted by the server 'MACHINE1'. Either configure a
  public cluster network on which one of the specified IP addresses can
  be hosted, or add another listener IP address which can be hosted on a
  public cluster network for this server.

I have three machines:

the first machine runs Active Directory
the second machine is named MACHINE1
the third machine is named MACHINE2.

The machines are nodes for SQL Server.

My machines, user and cluster are added to Active directory in a OU and they have necessary permissions. IPv4 configuration is complete as well.

My Active Directory looks like this:

cluster1 - Failover cluster virtual network name account
MACHINE1
MACHINE2

I couldn't find any clear solution for this problem.


texts:
I have a mysql table having following structure :

payment_id(int auto_increment),
emp_id(int),
chargeTime(datetime),
payment(double)

I need to have three latest (with respect to chargeTime) records of all emp_id. What should be the query?

Can anyone please help me? Without using cursor would be helpful.


texts:
I have been reading through past posts but I am still getting issues with 2 SQL servers connecting using a linked server when querying from a 3rd SQL server.  I constantly get the error "Login failed for user NT AUTHORITY\ANONYMOUS"

I have set the SPNs correctly using the Kerberos Configuration Manager provided by Microsoft for the service account SQL is running as on each machine.  I have also been reading that if the computer contains a linked server then the computer object in AD needs trusting for delegation is that correct?  Also do the service accounts need trusting for delegation?

I can't just make the changes to test so I need to get this right beforehand.


texts:
I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1).

Not withstanding any code changes or environment files that need to be changed as a result, are there any dangers to renaming the schema of a PostgreSQL database? If so, what?  Or will a simple ALTER SCHEMA be ok?


texts: Question concerning minimal/cannonical cover

texts:
For the question above, I am confused with how to approach simplifying the left hand side of BDCE->A. Do I get rid of E, as D depends on it, or do I get rid of CD as well, as CD->A?

Here's what I thought I did: Computed the closure of B,C,D,E,BC,BD,DE,CD,CE,BCD,BCE,BDE,CDE, then saw which closures actually use the dependency BCDE->A, and which dependencies actually get A. So far, CD is the smallest amount of attributes that get A.

The above leaves me with CD->A. Is this correct?


texts: Migration advice from SQL server 2000 bak

texts:
A small and old Windows 2003 server, just crashed; from the backup only the .bak files are available.
A new server will be built, however, as the database is very small (20MB) and the Windows server was also used for printing and file sharing (5 users), I was wondering what platform would be worth it to start from (server platform and SQL server version).
Maybe to make things easier it could be wise to use a simple Windows 7/8 machine with SQL server installed.
BTW, clients are running Access 2003 (ADODB/adp).

Your thoughts?


texts: Oracle XE Fedora 15 Installation problem

texts:
I got a problem installing Oracle XE on Fedora 15. Hope you can help me.
I used this tutorial(that worked great for F14) but didn't got lucky with F15.
The problem is that during installation goes ok,but then I try to connect to the XE web without success.
So I tried this
su - oracle
./app/oracle/product/10.2.0/server/bin/sqlplus /nolog

and got this error
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found

So I want to know what am I missing.
I thank your help in advance


texts: AWS DMS connect to on premise SQL Server DB

texts:
I am working through the process of migrating a SQL Server 2014 DB to AWS Redshift. I have converted the schema and provisioned a PostgreSQL Redshift instance. Now in AWS-DMS->Create Migration->Database endpoints->Connect source and target database endpoints I am not clear on the following:

Server name: ???

What do I need to specify for AWS to see this? My SQL Servername is 'SERVERNAME'. But I presume AWS would need an IP address of my machine as well? Also - I have not opened the Server up for internet access - it is a local install behind a firewall. How should I go about allowing AWS to access it?

Then for the target database connection details:

Server name: ???

Is this the full endpoint or just the server name without the ...amazon.com extension?


texts: mysql - query which provides additional column with iterative number by condition

texts:
I do have a table which looks like this.
Already grouped by user_id AND activity date:

[user_id] [activity_date]
123455     '2019-07-01'
123455     '2019-07-02'
123455     '2019-07-03'
222222     '2019-07-01'
222222     '2019-07-02'

Is it possible to write a query which gives me an additional column like this:

 [user_id] [activity_date]   [additional_counter]
123455     '2019-07-01'        1
123455     '2019-07-02'        2
123455     '2019-07-03'        3
222222     '2019-07-01'        1
222222     '2019-07-02'        2

It should just count up if the user_id has the same predecessor (same user_id).


texts: Azure sql database backup and exporting data-tier application

texts:
Currently i have an azure subscription in it has a server and a database.

How could i setup azure SQL database backups that will run automatically for example every 15 mins? is there a way? if yes how do i recover this kind of backups? and how much it will cost?

as my interim when i am backing up i just export data-tier application and now that i implement an encrypted column (i used always encrypted method) i cant export it, i am encountering an error saying that

  Could not export schema and data from database. (Microsoft.SqlServer.Dac)

  ------------------------------ ADDITIONAL INFORMATION:

  One or more errors occurred. (mscorlib)

  One or more errors occurred.

  One or more errors occurred.

  One or more errors occurred.

  One or more errors occurred.

  Explicit conversion from data type varchar(max) encrypted with
  (encryption_type = 'RANDOMIZED', encryption_algorithm_name =
  'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '*****',
  column_encryption_key_database_name = '******') collation_name =
  'Latin1_General_BIN2' to nvarchar(max) encrypted with (encryption_type
  = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '****',
  column_encryption_key_database_name = '*******') is not allowed.
  (Microsoft SQL Server, Error: 529)

I am a newbie when it comes to azure and as a DBA i hope you could help me guys.