texts: Better to create a table for each user account, or one huge table for all user data in MYSQL

texts:
I'm creating a web directory that will allow individual users to signup for an account and store essentially text documents in a mysql database entry.

Initially there will likely be only a few hundred users, but we hope to have 10,000 to 100,000 at some point.  And each user would be able to upload 100-200 "documents".

Would it be more efficient to create one massive table, indexed by a user number?  Which could theoretically grow to be 20,000,000 entries.  Or to go ahead and create a table for each user with their individual documents?

I'm assuming it's not healthy to have thousands of tables in a database, but I really can't find any concrete data on this.


texts:
I have a central publisher and several subscribers that are all physically located together and on the same internal network - think of a shop with 7 tills whose local DB replicates to the central DB and then back to the other tills so orders can be looked up on any till.

The replication has been working for several years however the performance has been quite slow and varies vastly between each of the end points - the slowest is showing in the replication monitor as 0.42 rows/second and the fastest is 9.52 rows/second with the others being 1, 3, 4, 6 and 8 rows/second.

I've never quite been able to figure out why there is such a range when all of the end points are physically located next to each other and they all have (from what I can determine) the same configuration.  We've eliminated any kind of hardware performance and configuration issues so I'm convinced it is something to do with the replication itself but I don't really understand how to troubleshoot this any further.

How can I see what is causing the slow down on the terminal in question?

Update:

This is merge replication where orders are stored locally on the tills, replicated up to the central server and then replicated back down to the other tills so all tills have a copy of all orders.

Tills are running SQLExpress and the replicating central server is SQL Server - all running 2014 versions.


texts: Limit number of entries written by Logon-Trigger

texts:
We are auditing connections to our SQL server by using a logon trigger. (SQL Server 2008 Standard Edition.)

However what we are seeing is that there are always a number of entries logged for each user, even with the same spid. We make the calls via a .NET app and there is (I think) connection pooling set to on if that matters.

Are there ways to write the logon trigger differently so that only the initial connection attempts are logged and subsequent calls via the same connection are ignored? I know I could do this by looking at the TB_Logon table we use and see if there is already the same user in there with the same spid for example. But can I change the trigger itself so that it doesn't log multiple entries per user any more?

Thanks all...

The trigger:

CREATE TRIGGER [Tr_Logon]
 ON ALL SERVER
 FOR LOGON
 AS
    BEGIN

    INSERT INTO TB_Logon
        SELECT GETDATE()
                ,ORIGINAL_LOGIN()
                ,EVENTDATA().value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(500)')
                ,@@SPID
                ,program_name()
                ,HOST_NAME()
                ,EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
    END

 END


texts: Oracle sort varchar2 column with special characters last

texts:
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order.  Or are any existing options available that will put letters first, then numbers, then all special characters.

Our first approach was using a function that does some manually mapping of characters to numbers.

select id, sorted_column
from some_table
order FN_SPECIAL_SORT_KEY(sorted_column,'asc')

The special sort function maps each character to a 2 digit number, and the return value is used for sorting.  This seems to be just really expensive concatenation, and it feels wrong.

        for i in 1..length(sorted_text)
        loop
            v_result:=v_result ||  case substr(sorted_text,i,1)
                WHEN ' '   THEN 82 WHEN  '!'   THEN 81 WHEN '"'    THEN 80 WHEN  '#'   THEN 79 WHEN  '$'
                ..............
                WHEN 'u'   THEN 15 WHEN  'U'   THEN 15 WHEN  'v'   THEN 14 WHEN  'V'   THEN 14 WHEN  'w'   THEN 13 WHEN  'W'   THEN 13 WHEN  'x'
                ....
                else 90 end;
        end loop;

I'm having a hard time coming up with an alternative approach.  I want to know what problems exist with this approach.  Perhaps we have no alternatives.

Addendum 1:

Adding example of sorted data.  In general,  all alpha characters case insensitive, then numbers 0-9, then special characters in any order.

Here is a sample sorted ascending list.  Keep in mind special characters are interchangeable, they all should be after letters and numbers.  In binary sort, some special characters are before letters (i.e.  '  )

My desired order,

AB1$
aCC#
ac'
BZ

Oracle binary order

AB1$
BZ
ac'
acc#


texts: Error while running stored Proc sp_BlitzIndex (TM) v4.2 - September 03, 2016

texts:
I am getting below error while running "sp_BlitzIndex(TM) v4.2 - September 03, 2016". My SQL server version is:

  Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

The error message is:

  Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1373 [Batch Start Line 0]
  ABCD database failed to process. The user does not have permission to perform this action.

  Create temp tables.

  ABCD
  Inserting data into #IndexColumns for clustered indexes and heaps
  Inserting data into #IndexColumns for nonclustered indexes
  Inserting data into #IndexSanity
  Failure populating temp tables.
  Last @dsql:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT    12 AS database_id,
                    so.object_id,
                    si.index_id,
                    si.type,
                    'ABCD' AS database_name,
                    COALESCE(sc.NAME, 'Unknown') AS [schema_name],
                    COALESCE(so.name, 'Unknown') AS [object_name],
                    COALESCE(si.name, 'Unknown') AS [index_name],
                    CASE    WHEN so.[type] = CAST('V' AS CHAR(2)) THEN 1 ELSE 0 END,
                    si.is_unique,
                    si.is_primary_key,
                    CASE when si.type = 3 THEN 1 ELSE 0 END AS is_XML,
                    CASE when si.type = 4 THEN 1 ELSE 0 END AS is_spatial,
                    CASE when si.type = 6 THEN 1 ELSE 0 END AS is_NC_columnstore,
                    CASE when si.type = 5 then 1 else 0 end as is_CX_columnstore,
                    si.is_disabled,
                    si.is_hypothetical,
                    si.is_padded,
                    si.fill_factor,
                    CASE WHEN si.filter_definition IS NOT NULL THEN si.filter_definition
                         ELSE ''
                    END AS filter_definition
                    , ISNULL(us.user_seeks, 0), ISNULL(us.user_scans, 0),
                    ISNULL(us.user_lookups, 0), ISNULL(us.user_updates, 0), us.last_user_seek, us.last_user_scan,
                    us.last_user_lookup, us.last_user_update,
                    so.create_date, so.modify_date
            FROM    [ABCD].sys.indexes AS si WITH (NOLOCK)
                    JOIN [ABCD].sys.objects AS so WITH (NOLOCK) ON si.object_id = so.object_id
                                           AND so.is_ms_shipped = 0 /*Exclude objects shipped by Microsoft*/
                                           A...

I have db_owner privileges on the database. Not sure what all permission I need to run the same.


texts: Performance issue using view vs. direct statement

texts:
I have a table with structure similar to this:

table values
(
id1 int,
id2 int,
value real,
value_at timestamp,
constraint primary key (id1,id2,value_at)
)

I need to get the "current value" for a given combination (id1,id2) --- that is, the record with highest value_at among the records with a given combination id1,id2.

The "normal" SQL query would be (example for id1,id2 = 100,200):

select ... from values
where (id1,id2) = (100,200)
      and value_at = (select max(value_at) from values
                      where (id1,id2) = (100,200))

However, I want to have a view that hides this complexity:

select value from vw_current_value where (id1,id2) = (100,200)

My attempt is:

create view vw_current_value as
select id1, id2, value from values as outer_v
where value_at = (select max(value_at) from values as inner_v
                  where outer_v.id1 = inner_v.id1 and outer_v.id2 = inner_v.id2)

It works, but getting a current value for a given (id1,id2) is 12x slower than what I get using the "direct" SQL statement (without using the view)
as reported by EXPLAIN ANALYZE (0.04 ms for the non-view, 0.48 ms for the view, with a table that has 250k records).

My question is: is there a more efficient way to write the view?  Perhaps using some other SQL construct that I'm either overlooking or unfamiliar with?

Notice that I would like to:

Avoid a Pl/PgSQL procedure for this.
Use a view and not the "direct" select statement.
Stick to version 9.2.4  (however, if this is known to have much better performance in later versions, I would certainly like to know about it).

EDIT:  As requested, here's the output of the two explain analyze for each of the queries (first one for the direct query, second for the query using the view):

Index Scan using pk_values on values  (cost=0.09..8.43 rows=1 width=24) (actual time=0.018..0.019 rows=1 loops=1)
  Index Cond: ((id1 = 100) AND (id2 = 200) AND (value_at = $1))
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=1)
          InitPlan 1 (returns $0)
            ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)
                  ->  Index Only Scan Backward using pk_values on values  (cost=0.00..5.98 rows=74 width=8) (actual time=0.011..0.011 rows=1 loops=1)
                        Index Cond: ((id1 = 100) AND (id2 = 200) AND (value_at IS NOT NULL))
                        Heap Fetches: 0
Total runtime: 0.036 ms

Bitmap Heap Scan on values outer_v  (cost=5.05..262.46 rows=1 width=24) (actual time=0.457..0.458 rows=1 loops=1)
  Recheck Cond: ((id1 = 100) AND (id2 = 200))
  Filter: (value_at = (SubPlan 2))
  Rows Removed by Filter: 75
  ->  Bitmap Index Scan on pk_values  (cost=0.00..5.05 rows=74 width=0) (actual time=0.017..0.017 rows=76 loops=1)
        Index Cond: ((id1 = 100) AND (id2 = 200))
  SubPlan 2
    ->  Result  (cost=0.08..0.09 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=76)
          InitPlan 1 (returns $2)
            ->  Limit  (cost=0.00..0.08 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=76)
                  ->  Index Only Scan Backward using pk_values on values inner_v  (cost=0.00..6.02 rows=76 width=8) (actual time=0.003..0.003 rows=1 loops=76)
                        Index Cond: ((id1 = outer_v.id1) AND (id2 = outer_v.id2) AND (value_at IS NOT NULL))
                        Heap Fetches: 0
Total runtime: 0.478 ms


texts: Startup Parameters greyed out in SSCM for new instance

texts:
We do not appear to be able to change the startup parameters for a new SQL Server instance, using SQL Server Configuration Manager (see screenshot below).

We are members of the sysadmin fixed server role, and are running SSCM version 2015.0130.16111.04 for Microsoft SQL Server 2016 (SP2-CU6) (KB4488536) - 13.0.5292.0 (X64) Enterprise Edition.


texts:
I would like to find (using SQL DMVs) whether a database is actually in use and if so then how much of that servers resources it is taking.

I need some ideas on how we can properly collect usage statistics on all of these databases across all of these servers and then create a simple report to understand resources usage.

The purpose of this report is to determine what are the critical counters that we need and what is the simplest and least impact method to collect them.

We don’t need minute to minute, I’m only looking for summary information once per day or so.

Can you please advise on this?


texts: In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

texts:
I'm writing a script to populate some tables with data for testing.

I would like to write something like the following but I don't know how to do it (I'm Oracle 11g)

SET ENABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE
SET DISABLED_USER_ID = SEQ.NEXTVAL; // PSEUDOCODE

INSERT INTO USERS
        (ID,      USR_NAME)
VALUES  (:ENABLED_USER_ID, 'ANDREW');
INSERT INTO CAR
       (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   :ENABLED_USER_ID);

INSERT INTO USERS
        (ID,      USR_NAME)
VALUES  (:DISABLED_USER_ID, 'ANDREW');
INSERT INTO CAR
       (CAR_ID,         CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD',   :DISABLED_USER_ID);

I know I could rearrange the queries and use the sequence.currval reference, but I'd prefer to have the id saved in properly named variables.

Maybe I should just wrap the script in a DECLARE ... BEGIN ... END; but I'm hoping there is a more concise way to do it.

Addition 27 May 2011 15:31

It seems that in any case I have to declare the variables in a DECLARE block. So I'm trying with

DECLARE
  USER_ID NUMBER(10,0) := 1;
BEGIN
  insert into TEST_USER
  values (user_id, 'andrew', sysdate);
END;

but I get the following error

Caused by: java.sql.SQLException: ORA-06550: **line 2, column 27:**
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

  * & = - + ; < / > at in is mod remainder not rem
  <an exponent (**)> <> or != or ~= >= <= <> and or like like2
  like4 likec between || multiset member submultiset

That points to the variable declaration.

I'm using java to load the script from a file and running it using the Oracle JDBC driver (ojdbc14-10.2.0.4.0.jar) on a Oracle 11g server.

The table TEST_USER has been created with

create table TEST_USERS (
    id number(10, 0) not null,
    name varchar2(100),
    date_ins date default sysdate,
    primary key (id)
);


texts: SQL-SERVER: how to get the maximum length of a variable of VARCHAR help

texts: Hello, in this question im trying to get the maximum length of a name but i cnat seem to figure out what im doing wrong, i've search the web for answer but find none. i tried to put the MAX function in the select clause but it didnt seem to fix it, what am i doing wrong?? Thank you


texts:
In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension.

I have the following table definition:

CREATE TABLE readings (
  id          BIGSERIAL      PRIMARY KEY,
  time        TIMESTAMPTZ       NOT NULL,
  value       DOUBLE PRECISION  NOT NULL,
  device      VARCHAR(255)      NOT NULL,
  sensor      VARCHAR(255)      NOT NULL,
  resolution  VARCHAR(255)      NOT NULL,
  UNIQUE(time, device, sensor, resolution)
);

I want last reported time for every (device,sensor) pair. Here is the query I am running:

select MAX(time), device, sensor from readings group by device, sensor

This query takes a significant time to complete. Is there any way to improve the performance? Or any better query to achieve the same result?

I tried doing this and it did not help:

create index if not exists readings_idx on readings (time, device, sensor, resolution);


texts: Result set of SELECT statement in LOOP in MySQL Stored Procedure

texts:
I have the following SP:

CREATE PROCEDURE vessels_near_anchorage_test()
   BEGIN
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT field1, field2 FROM table1;
      OPEN cur1;
      read_loop: LOOP
           FETCH cur1 INTO arg0, arg1, arg2, arg3;
           SELECT * FROM table2 WHERE c1=arg0 AND c2=arg1;
      END LOOP;
      CLOSE cur1;
END;

When I try to result set from this SP in the application server, I get DataError: 1329 (02000): No data - zero rows fetched, selected, or processed. I want to understand how the SP is really being executed and what is causing this error.

In the SP above, the SELECT statement is inside the loop. It returns rows in some iterations while there may be no result in some iterations. When I run this in PhpMyAdmin, I get 850 rows, but it's entirely possible that the last iteration does not return any row.

When a SELECT statement is executed in a loop like that, can I combine results from all iterations and output as a single result set available by the time the loop completes?
For the application, is it only the result set from the last iteration in the loop made available which in this case has no rows?


texts: 2 Different Oracle DB Version on same server

texts:
Is it possible to install and run 2 different oracle version(11g and 12c)software and db on same server?
is it possible then how we can? and if not then why?

Thanks

Rahul Joshi


texts: pg_restore: [archiver] unsupported version (1.11) in file header postgresql

texts:
My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a dump file from my server.

The dump sentence I used was:

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "/home/damian/backups/mybase.backup" mybase

To restore I'm using:

pg_restore -i -h localhost -p 5432 -U postgres -d mybase -v "/home/damian/backups/mybase.backup"

How can I restore a dump file from a newer version of PostgreSQL into an older version of PostgreSQL?

Thanks for any help in advance.

Edit: I tried to use a different parameter in the option -F, this time I tried to make a dump file in plain text but some of the information didn't restore correctly, how can I solve this?

Edit: I thought the problem was the encoding my database was using but I changed it to UTF8 -which is my dump file encoding- but I got the same error as before:

error: syntax error at or near Â«1Â»
LINE 1: 1 TELÃFONO NÃMERO TELEFÃNICO MEDIANTE EL CUAL SE PUEDE ESTAB...

I'm really lost at what is causing these errors.


texts: How can I set the index fill factor back to server default?

texts:
I have some indexes that I was playing with on my dev server.  I set the fill factor of these indexes using the following command:

alter index all on dbo.Table
rebuild with (fillfactor=80)

That sets and rebuilds all of the index on dbo.Table.  Now what I want is to set the fillfactor back to the default of the server.  IE, at the server level all indexes are set to have a fillfactor of 95.  I want the indexes to use the server default again, not just have the fill factor set to 95.

The reason I want to do this is so that, if I script this index and move it to a new server, it will use the default fill factor of that server rather than the specified 95.


texts: Every time I reboot Mariadb places me the max_connections in 151 and in the my.cnf I have it with 300. Why does this happen?


texts: Why does this query take so long to execute?

texts:
I'm currently using Microsoft SQL Azure (RTM) - 12.0.2000.8 as the database. The database currently has 10 DTUs.

The idea here is that I want to make the query based on the view. This view contains of simple SELECT() syntax with 5 table joins. This view give output roughly about 253K rows in about 6 seconds.

CREATE VIEW [dbo].[TopAdsDisplaySumaryView]
AS
SELECT
        client.Id AS ClientId,      -- (PK, int, not null)
        client.PartnerId,           -- (FK, int, not null)
        adsPict.Id AS AdsPictureId, -- (PK, int, not null)
        adsPict.ImageName,          -- (nvarchar(max), null)
        displayAds.DisplayTo,       -- (datetime, not null)
        displayAds.DisplayFrom      -- (datetime, not null)
FROM
        dbo.Machines AS machine
        INNER JOIN dbo.MachineGroups AS machineGroups ON machineGroups.Id = machine.MachineGroupId
        INNER JOIN dbo.Clients AS client ON client.Id = machineGroups.ClientId
        INNER JOIN dbo.DisplyadAds AS displayAds ON displayAds.MachineId = machine.Id
        INNER JOIN dbo.AdsPictures AS adsPict ON adsPict.Id = displayAds.AdsPictureId

Notice that I'm only taking the column from tables clients, displayAds, and adsPictures, but I have to join tables machines and machineGroups in order to get data from clients. displayAds table has the most data.

From here I want to generate the top 5 data with the most data count. This is the query:

SELECT TOP 5
    adsSum.PartnerId as PartnerId,
    adsSum.ClientId as ClientId,
    adsSum.AdsPictureId as AdsPictureId,
    adsSum.ImageName as ImageName,
    count(*) as TotalDisplay
FROM [dbo].[TopAdsDisplaySumaryView] adsSum
GROUP BY adsSum.PartnerId, adsSum.ClientId, adsSum.AdsPictureId, adsSum.ImageName
ORDER BY TotalDisplay DESC

Here's the actual execution plan for this query: https://pastebin.com/CcR9cC5e

When this query is executed, it takes about 1 minute 15 seconds. I'm trying to find out why this query takes a lot of time to execute, while executing the view only takes 6 seconds.

Here's my attempts so far:

Created an index for displayAds table. This gives me ~1 second improvement, no big change.

CREATE NONCLUSTERED INDEX
[MachineIdIndexIncludeAdsPictureIdDisplayFromDisplayTo] ON [dbo].[DisplyadAds]
(
    [MachineId] ASC
)
INCLUDE
(
    [AdsPictureId],
    [DisplayFrom],
    [DisplayTo]
)
GO

Created an index for adsPictures table. Nothing changed.

CREATE NONCLUSTERED INDEX [IX_AdsPictures_IncludeImageName] ON [dbo].[AdsPictures]
(
   [Id] ASC
)
INCLUDE ( [ImageName])
GO

Tried to remove column adsPict.ImageName in the query, the execution time was greatly improved to only 3 seconds (~72 sec. improvement!). I suspect that column is the problem, but I need it to show.

The maximum record length of ImageName is 36. Mostly around 11~20. There are 55 rows in AdsPictures table.

Any idea how to resolve this problem? Any help would be greatly appreciated.


texts: How to split a list of number into ranges with a fixed interval with SQL?

texts:
Let's say I have a table like this

I want to calculate the frequency ( How many times that product exists in that price range ), in intervals of "50"

So eventually it will give me a table like

Interval for range will be lets pretend a fixed 50
We don't know highest and lowest price of these each products.

So I will run the query and it will give a table as shown above.

I got a query like this but it didn't work. It gives this error ( I cannot edit server config )

This is the query

select product, count(*) as frequency,
       floor(price / 50) as range_start, floor(price / 50) + 50 as range_end
from t
group by product, floor(price / 50)
order by product, min(price)

This is the error

 [42000][1055] Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

After that I tried this ( changed floor(price / 50) to floor(min(price) / 50)

select product, count(*) as frequency,
       floor(min(price) / 50) as range_start, floor(min(price) / 50) + 50 as range_end
from t
group by product, floor(price / 50)
order by product, min(price)

This query worked but it gives ranges like


texts:
I am trying to get count of all quiz users who have made purchases. Main table for storing users is user and quiz_client is linked with user table. sales_order table holds data of users who have placed orders/made purchases.

I'm running the below query but it's taking forever, is there something wrong with the query?

SELECT
    COUNT(user.id)
FROM
    cx_user
        JOIN
    quiz_client ON quiz_client.user_id = user.id
        JOIN
    sales_order ON user.email = sales_order.customer_email


texts: MongoDB database schema designing for big data

texts:
I have never designed a database schema in NoSQL, nor in RDBMS before but for my recent big data project I need to store and locate large amount of data in MongoDB(3.6). I have done it myself, but the querying was hard and speed was slow and I need some advice. Please help me out.

The first collection is 'news'.

It has 'oid', 'aid', 'date', 'section', 'rank', 'title'.

'oid', 'aid' is their unique ID and I need to be able to find 'date', 'section', 'rank', 'title' by querying them.

My current schema design is:

{
  "1234567890123": {    // oid + aid
    "date": "20180126",
    "section": "100",
    "rank": "1",
    "title": "Linus: Intel patch is a piece of shit ",
    "oid": "123",
    "aid": "4567890123"
  },
  "1234567890124": {   // oid + aid
    "date": "20180126",
    "section": "101",
    "rank": "2",
    "title": "Intel sues Torvalds for claiming fact",
    "oid": "123",
    "aid": "4567890124"
  }
}

About 180 of these individual news info will be stored in a day.

The second collection is 'comments'. Obviously, it has comments for items in 'news'.

Each comment object consists of comments specific to items('oid', 'aid') in the 'news' section.

The included data will be 'oid', 'aid', 'date', 'commentCount', 'deletedCommentsCount' and it has an array of comments 'uid', 'id', 'time', 'upvotes', 'downvotes', 'contents'

My current schema design is:

{
  "date": "20180126",   //Same with one in 'news'
  "oid": "123",
  "aid": "4567890123",
  "commentCount": "7",
  "deletedCommentsCount": "0",
  "reply": {
    "12345678901230": {         // oid + aid + commentid
      "id": "john_skeet",
      "time": "2018-01-26 07:32:45",
      "upvotes": "2",
      "downvotes": "0",
      "contents": "So true!"
    }
     "12345678901231": {         // oid + aid + commentid
      "id": "sundar_pichai",
      "time": "2018-01-26 07:35:46",
      "upvotes": "5",
      "downvotes": "0",
      "contents": "@john_skeet Get back to work!"
    }
  }
}

In a day for the 180 news * 1160 comments(approx) so about 200,000 comments will be stored. (65MB / day)

I need to:

query all comments in specific 'oid'
query all comments in specific 'oid' and 'aid'
query all comments for specific word in comment 'contents'
query all comments for specific id in comment 'id'

.... so on

Can you tell me the proper way to design a database containing these data? And if you can, the query statements too. ANY comments or answers will be appreciated. Thanks.


texts:
We want to set up a highly available PostgreSQL cluster in our production environment. As of now, We have set up streaming replication. But we have to manually promote the slave as the master in case of failover and also change connection strings of the client applications.

Is there any tool exists for automating this? So that we don't have to promote the slave as master and also no connection string change requires in client applications.

We are using PostgreSQL-9.2.


texts: ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS

texts:
I am using Toad to connect my database and its working fine for me .
But when i try to connect with the same credential , i am getting the error message as shown below:

ORA-12154 TNS: could not resolve the connect identifier specified

Steps:

Opened command promt
Reach to the path of SQLPLUS (C:\programs\Oracle\Ora11g\BIN)
C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name
Enter Password :

ERROR:
 ORA-12154 TNS: could not resolve the connect identifier specified
But when i open this with the owner of the schema ,it works for me
C:\programs\Oracle\Ora11g\BIN\sqlplus schema_name@schema_name
Enter Password:
Connected to :......................

So,my question is why my username is not able to connect that particular schema ,while the owner of that schema can connect .
Note:I can connect to that schema with my username with Toad ,but not with sqlplus

My TNSNAME.ORA is under TNS_ADMIN folder

Please help me out of this .


texts:
I lost all the control files and I don't have backup for it. Control file creation failed

SQL> CREATE CONTROLFILE SET DATABASE "UAT02" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/uat02/redo01.log' SIZE 100M,
GROUP 2 '/opt/oracle/oradata/uat02/redo02.log' SIZE 100M,
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/uat02/system01.dbf' size 400m',
'/opt/oracle/oradata/uat02/sysaux01.dbf' SIZE 300m,
CHARACTER SET US7ASCII
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE CONTROLFILE REUSE DATABASE "UAT02" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/opt/oracle/oradata/uat02/system01.dbf
size 400m'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
System01.dbf is there

[oracle@localhost uat02]$ ls -ltr

total 1034612
-rw-r----- 1 oracle oinstall 104858112 Sep 27 07:41 redo02.log
-rw-r----- 1 oracle oinstall 104865792 Sep 27 11:48 undo01.dbf
-rw-r----- 1 oracle oinstall 419438592 Sep 27 11:48 system01.dbf
-rw-r----- 1 oracle oinstall 314580992 Sep 27 11:48 sysaux01.dbf
-rw-r----- 1 oracle oinstall 104858112 Sep 27 11:48 redo01.log
-rw-r----- 1 oracle oinstall 9748480 Sep 28 23:39 control01.ctl

Any suggestion?


texts: MySQL Error 10048 on nested Delphi Queries

texts:
I'm having a problem with MySQL (Server 5.1.11) interfacing with a Delphi application

The problem occurs when trying to use the application to do a bulk export to Excel.  During the application queries for a list matching projects (Query A), then for each matching project the details of the Project are requested from the database (Query B)

The error that occurs is

Can't Connect to MySQL Server on 'stkitts' (10048)

It generally occurs when trying to reach larger batches.  For example I am able to query the past 3 years (771 projects, 1150 records including details), but expanding to 4 years I error at only 443 records returned.

I've checked that my data is OK, and the error is not associated with specific records.  Repeating a search will break at a different point - although you are unlikely to get results for 4 years back...

The error can occur when reading a field or a new record.  So the returned Excel sheet may or may not have a complete line at the point the error occurred.

My gut feeling is that I am sending too much traffic to the DB server, but I am only using two connections to the database for the above queries (MySQL Administrator confirms that there are only 2 active connections during the export)

Any suggestions?


texts: CMDEXEC The process could not be created for step... (reason: Access is denied). The step failed

texts:
I have created a job to test executing a CmdExec step.  The job step is simply executing a batch file with dir as the command.

The job is executing as the SQL Server service account.  This account is both sysadmin of SQL Server and Administrator in Windows.  The login also has been  granted exec to xp_cmdshell, in master. In Surface Area Configuration, XPCmdShellEnabled = True.  If I run the command from the query window it executes as expected.

Question: why am I still getting the job step error?  This is plain vanilla install of SQL Server 2008 R2 with SP2 installed.  Nothing fancy going on here.

Note: I know how horribly insecure this is, but this is where it has come to in order for me to chip away at this and try and resolve this error.  Best I can tell, this just can’t be made to work and BOL makes it sound so easy.


texts:
I have a corrupt table, 1 dodgy page about 1.6 million rows into a 2.9 million row table. I want to attempt this:

ALTER DATABASE [CorruptDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (CorruptDB, REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK;

..but I want to know how I can be certain the number of rows before and after the data loss. If I do:

UPDATE STATISTICS [corrupt_table] WITH FULLSCAN;

It fails as there is of course a torn page half way down. The same goes for:

SELECT COUNT(*) FROM [corrupt_table];

I suppose my question is, will a non-fullscan UPDATE STATISTICS definitely update the number of rows correctly?

I know it's only one torn page, that should equate to about 100 rows in this table, I just want to know for sure.

Note: This is not on a production system, it's just local on my laptop (2012 Std x64) and the actual issue has been resolved (with a restore) - I've taken a copy of the DB to play with.


texts: CREATE INDEX vs ALTER TABLE ADD INDEX - MySQLism, or SQL Standard?

texts:
Just came across a weird issue, whereby depending on how I create an index, an index name is required.

http://dev.mysql.com/doc/refman/5.5/en/create-index.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

CREATE INDEX `random_name` ON `my_table` (`my_column`); # Requires an index name

ALTER TABLE `my_table` ADD INDEX (`my_column`); # Does not require an index name

It seems to me that the CREATE INDEX call, should not make index name required. I'm wondering if this is a MySQLism, or a SQL standard?


texts: All pull subscriptions cannot connect to Distributor

texts:
recently all my PULL subscriptions have this message in the synchronization status. :-

The process could not connect to Distributor 'DISTRIBUTIONDB'

This is only happening with the PULL subscriptions. I have attempted to reinitialize with a new snapshot, but the message remains.

when attempting to look at the history it suggests that the agent job doesn't exist. So I removed it and recreated the subscription and the job from a T-SQL script. monitor now suggests that the job doesn't exist but when run the T-sql sp_addpullsubscription code on the subscriber i get

Msg 21002, Level 11, State 1, Procedure sp_addpullsubscription_agent, Line 250
The Distribution Agent for this subscription already exists (YAZOO-MLT-Statics-KINKS-MLT-Replicated-94CD221C-4F8E-4732-8738-A6101E9D571B).


texts: How to use SSL/TLS between server and client with Postgres?

texts:
I don't know much with it comes to security. I'm deploying a virtual machine with my database in a cloud service. I would like to encrypt all the communication between this server and any clients. How can I do that ?

More information:
I have read a lot of material from postgres documentation [1,2], but I can't understand much of it. I have followed this tutorial and now I have these files:

valter@eniac:test$ ll
total 28
drwxr-xr-x 2 valter valter 4096 Jan 25 12:54 ./
drwxr-xr-x 3 root   root   4096 Jan 25 12:50 ../
-rw-rw-r-- 1 valter valter 1834 Jan 25 12:53 privkey.pem
-rw-rw-r-- 1 valter valter 4783 Jan 25 12:54 server.crt
-rw------- 1 valter valter 1675 Jan 25 12:53 server.key
-rw-rw-r-- 1 valter valter 3672 Jan 25 12:53 server.req

But I don't know what to do with them. Where are root.crt and root.crt files ? How can I generate them ? Where do I put these generated files ? And what should I do on the client side ?


texts:
I need to run sp_depends on multiple stored procedures on Sybase to get dependencies. Since I have a lot of stored procedures, I wanted to know if there is any way to force sp_depends output into a temp table and then select from there all dependent objects. If this is possible, I can just run sp_depends from a stored procedure for multiple stored procedures and get the result set in one go.

I tried below without any luck (using Toad):

Insert #test_tbl
exec sp_depends sp_proc

Above gives error as:

  "Incorrect syntax near the keyword 'exec'"

Please advise if there is any other efficient way to do this. My goal is to get all dependencies for analysis.

Update:
I do have a way to generate mass 'sp_depends' in excel. Looking for something to run them in one go and get the whole output of supplied objects.
Also, since this is Sybase utility, unfortunately I don't have a way to edit it


texts:
How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal).

I got the following error:

  sg 468, Level 16, State 9, Line 1
  Cannot resolve the collation conflict between "Serbian_Latin_100_CI_AS" and "Croatian_CI_AS" in the UNION operation.

My SQL statement:

select * from #IA_BIH
union
select * from #IA_MNE

Where should I insert collate database_default? I tried different combinations, but it didn't work.


texts:
This is simplified but represents the problem I am trying to solve. We have a table containing 5-10 million rows in a format similar to the following...

Input Table
Date    Item   Moved to Box
Oct-1   1      BoxA
Oct-6   1      BoxB
Oct-8   1      BoxC
Oct-9   1      BoxB
Oct-16  1      BoxC
Oct-17  1      BoxD

And I am trying to convert it into this

Expected Output
Item    Box    Duration
1       BoxA   5
1       BoxB   9
1       BoxC   2
1       BoxD   *unimportant

*It doesn't matter what the query returns for BoxD (the only box move in without a box move out) as it is discarded.

For the example hopefully you can see that the input table represents a log of when an item moves from box to box and the expected output is cumulitively how long each item spends in each box.

My first thought was to do a table join with itself and do some date min/maxing for each record to try to find the exit date from a box, then sum the results but it seems pretty process intensive.

How would someone approach this in an efficient way?


texts:
I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples attribute found in the information schema because it is not guaranteed to be correct.

So far I have this function:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text)
RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$
DECLARE
    schema1_tables CURSOR FOR
        select pg_tables.tablename
        from pg_tables
        where schemaname ~ schema1
        order by tablename;
        schema1_ct int;
        schema2_ct int;
BEGIN
    FOR table_record IN schema1_tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct
        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct
        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)
END LOOP
END; $$ LANGUAGE pgpgsql

But I'm really only interested in the cases where the output's match column is false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where match = false are shown first?


texts: Changing ORDER BY direction causes performance issue

texts:
I use MySQL 5.6, and I have two tables each with 16M rows:

CREATE TABLE IF NOT EXISTS `newsstudios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16855382 ;

CREATE TABLE IF NOT EXISTS `newsstudio_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `newsstudio_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `newsstudio_id` (`newsstudio_id`),
  KEY `category_id` (`category_id`),
  KEY `newsstudio_id_category_id` (`newsstudio_id`,`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16864013 ;

I have one query with order by order by newsstudios.id ASC :

SELECT SQL_NO_CACHE id FROM `newsstudios` WHERE exists
(
  select newsstudio_id from newsstudio_categories
  where newsstudios.id=newsstudio_categories.newsstudio_id
  and newsstudio_categories.category_id in (1303,1313,1323,1333,1343,632)
)
order by newsstudios.id limit 5;

the result of this query is:

+------+
| id   |
+------+
|   27 |
|   47 |
|   87 |
|  110 |
|  181 |
+------+
5 rows in set (0.19 sec)

but when I change the direction of order by to DESC the query execution time decreases 100 times:

+------+
| id   |
+------+
| 98232|
| 98111|
| 95222|
| 88132|
| 78181|
+------+
5 rows in set (21 sec)

First: why does this change in the direction of order by cause this huge difference in performance?

Second: before this query I have tried LEFT JOIN and WHERE IN queries instead of WHERE EXISTS but they have duplicate result that I should use GROUP BY that it cause using filesort and using temporary which decreases performance a lot. Do you have any suggestions for the query to have better performance?


texts:
On mysql community 5.5 (centos 6), VM on hyper-v win 2008 r2, 4 CPU, 48 GB RAM

Which setting(s) that I would need to change to reduce cpu usage and get my web app (joomla) to respond faster without buying new hardware?

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 40G
innodb_read_io_threads  = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

symbolic-links=0

user=mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts: Pass a table name as a parameter to a stored procedure

texts:
I am trying to create a Stored Procedure, and pass the name of a table as a parameter and I get this message:Must declare the table variable "@tblName".
What do I have to change to make it work?
CREATE PROCEDURE [usp_SaveToErrorLog]
    @tblName as nvarchar(50),
    @subject as nvarchar(30)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblName))
        Begin
            Insert Into @tblName
            (ErrorNumber, ErrorMessage, ErrorProcedure, ErrorState, ErrorSeverity, ErrorLine)
            Select
                ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_STATE(), ERROR_SEVERITY(), ERROR_LINE()
        End
END


texts:
adcfgclone.pl dbconfig fails when trying to configure the database to use logical host names. Unable to make database connection, error in ApplyDatabase.log
"   Exception occurred: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
    Connection could not be obtained; returning null"

Database version: 12.1.0.2
Oracle E-Business Suit version : 12.1.3

Steps performed:
1. Adpreclone.pl run on apps tier and two database node (2 node rac database)
2. Created context file on the first database node to use logical hostname.
3. adcfgclone.pl dbconfig being run to configure the database to use logical name. Error at this step.


texts:
I`m trying to obtain the total time of a query execution

I have this query but not allways get the output that I hope, For example for the same parallel 4 execution of a query sometimes I obtain 5 rows (query controller and 4 parallels) but other times I only obtain 2 parallel process result (the parallels processes only open in the same instance)

Is this query correct or I need to add something more?

select inst_id, sql_exec_id,session_id, QC_SESSION_ID,program,sql_id,sql_plan_hash_value,round((count(*))/60/60, 2) as Horas
from    gv$active_session_history ash
where   sql_id='7u0wv0g5r3y2n'
and sql_exec_id is not null
group by    inst_id, sql_exec_id,session_id,QC_SESSION_ID,program,sql_id,sql_plan_hash_value
order by    2,1,4,5 desc;


texts:
Dropping multiple KEYS in a table is a preferred option or one by one is preferred?

“DROP KEY A,DROP KEY B,DROP KEY C"

For more 900 million records in the table.

How does it work internally?


texts:
Using oracle 11,

Although a lot was already written about blobs, there is something that is not still clear for me.

I was told that by default blobs stored in row if the are < 3900. Does it means that for a single table, some blobs will be stored in row and some others won't?
Is there a way to check whether they are stored in row or not?
If I set ENABLE STORAGE IN ROW, does it means that whatever the size the blob has, it will be save within the same segment than the other data?

Oracle Doc says:

  "When the size of the LOB stored in the given row is small,
  approximately 4000 bytes or less, and you either explicitly specify
  ENABLE STORAGE IN ROW or the LOB storage clause when you create the
  table, or when you do not specify this parameter (which is the
  default)."


texts: Entity ID field that can refer to multiple tables?

texts:
In designing both audit tables and notification tables, I've run into an issue where I need a row to refer to an entity in another table, but which table that entity is isn't always the same. For example:

Each time a user makes a post or uploads a new photo to their album, a notification is created. If the notification is for a post, the notification entry must refer to the posts table. If it is for a photo, the notification entry must refer to the photos table.
Moderators may edit posts, delete posts, or block users. The audit_actions entry must refer to the entity that was acted upon in the corresponding table.

My original approach looked like this (this is heavily simplified, not the actual table structure):

TABLE notification_types:
     id         INT
     type_id    INT
     entity_id  INT

If type_id was 1, entity_id would refer to an entry in the posts table
If type_id was 2, entity_id would refer to an entry in the photos table
etc

I was starting to use a similar scheme for audit tables. My plan was to make an audit_history table for each auditable table that mirrored the original table (e.g. posts and post_audit_history), and maintain a single audit_actions table that linked an actor and action to each history item

TABLE audit_actions
     admin_id        INT
     action_id       INT
     entity_id       INT
     audit_entry_id  INT

If action_id was 1 ("edited post"), entity_id would refer to an entry in posts, audit_entry_id would refer to an entry in posts_audit_history, etc

Of course, this approach is terrible. entity_id can refer to multiple tables, which is confusing and prevents it from being a foreign key. There is no way to enforce integrity without complicated triggers.

I've been trying researching and trying to find solutions but I am stuck. Some approaches I've considered:

Maintain an entity_ids table. Each entity in every table then has a foreign key to an entry in the entity_ids table; in other words, every single entity in any table has a globally unique ID. This maintains integrity, but when we looked up an item by its entity_id we'd have to search multiple tables to find the entity with that ID
Maintain separate notification tables for each type of notification, and separate audit tables for each type of auditable table. E.g. post_notifications, photo_notifications, post_audit_actions, profile_audit_actions, etc. This would require running messy and expensive joins for what were formerly simple queries like 'find all notifications for user X' or 'get all audit records for user Y' or 'get the 100 most recent audit records'

Which approach is best? Is there a better strategy that I haven't thought of here?


texts: DTUTIL performance slower in SQL Server 2012?

texts:
We've noticed that since deploying Windows 2012/SQL Server 2012, the performance of DTUTIL has decreased. DTUTIL.exe consumes 50% on each of 2 cores on an 8 GB VM. The same syntax against Win 2008 R2/SQL Server 2008 R2 shows almost no impact on a VM with similar specs. Overall, it takes ~90-120 seconds to deploy a package.

We're using 64-bit SQL Server 2012 Enterprise Edition on SP1; the syntax of the command is:

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe" /DestS COMPUTER1 /FILE "e:\folder\SSISPackage.dtsx" /COPY "SQL;FOO\BAR\SSISPackage" /QUIET

We have not yet tried this using the new SSIS approach in 2012. We have validated that the behavior repros across multiple different packages. We've also looked at all of the post-SP1 cumulative updates; they do not appear to address this problem, but we also have not applied them either to rule that out.

Has anyone else encountered/resolved this? Granted, package deployment is not a high frequency action, but we do notice it during our automated deployments.


texts:
How long will it take to rename a table with a size of 219 GB?

This is the specification of our server:

Memory: 65 GB
Available disk space: 266 GB

MySQL Ver 14.12 Distrib 5.0.77

Sorry for the confusion. Our plan is to rename a MySQL table with size of 219 GB.
How long will it take? Do we need to have a large disk space for this migration?

Table's engine: InnoDB.


texts: SQL server agent unable to open step output file the step failed - mapped drives

texts:
I have used Ola Hallengren's maintenance script to create backup scripts and schedules, originally both to a local folder and this worked fine. Then I set it up to send the backup files to a mapped Azure file share as a mapped network drive with no log file output, this worked.

I have now re ran the script so that it backups and outputs to the same drive (one folder called Backup and one called LogBackup), SQL Server agent will now not run if the output file location is declared, I have tried using a local drive but it still does not work. Any suggestions?

This is the error displayed in the job history:

Message
Executed as user: NT Service\SQLSERVERAGENT. Unable to open Step output file.  The step failed.


texts: SQL Server 2019 replication problem after update to CU9

texts:
After update to CU9 for SQL Server 2019 (15.0.4102.2) (Linux Ubuntu 18), replication (snapshot) stopped working with errors:
2021-03-23 11:58:08.70 [0%] The replication agent had encountered an exception.
2021-03-23 11:58:08.70 Source: Replication
2021-03-23 11:58:08.70 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException
2021-03-23 11:58:08.70 Exception Message: memory mapped file write failed
2021-03-23 11:58:08.70 Message Code: 0

Do you have any suggestions? I've tried to recreate replication, restart mssql server. Nothing works.


texts:
I wonder why there is a difference in execution plan cost for the 2 queries :

create table test_insert(id int not null primary key, value varchar2(10));

--1
insert into test_insert(id,value)
values (2,'111');

--2
insert into test_insert(id,value)
select 3,'111' from dual where not exists (select null from test_insert where id =3);

Cost for query 2 is always higher (how much depends on number of rows in the table).
In my understanding not exits ... should not add any overhead - in order to enforce PK constraint  the engine must check the corresponding unique index anyway, so subquery adds extra parsing time, but not execution time or extra step to the plan.

The example is for Oracle, but I checked Postgres as well, results are similar.

For example,

--1
| Id  | Operation                | Name        | Cost  |
--------------------------------------------------------
|   0 | INSERT STATEMENT         |             |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT |       |
--------------------------------------------------------
--2
| Id  | Operation                | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |       |       |     3 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL | TEST_INSERT  |       |       |            |          |
|*  2 |   FILTER                 |              |       |       |            |          |
|   3 |    FAST DUAL             |              |     1 |       |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN     | SYS_C0012345 |     1 |    13 |     1   (0)| 00:00:01 |


texts: Hierarchial, tree-like data model, which DB type?

texts:
I have the need for a database, I know what to store, however the question is what database to use and why. I have used relation SQL databases a lot in the past (up to a few years ago) and I know I could do this in SQL. My question is mostly if there is a better alternative. I will sketch the scenario.

I have objects, and those objects can also contain other objects. There is a hierarchial relationship between those objects, bigger objects can contain smaller objects, not the other way around, but outside of this fact, the smaller and bigger objects are to be treated the same, have similar features and can and will be queried just the same. Important to note is that smaller objects can be part of multiple bigger objects (shared by the bigger objects)

Most of the queries will be on an identifier, to just retrieve the main object information plus the objects that it contain (and the objects those objects contain). Most of the information on these objects will remain the same, with a few exceptions, information that will be updated frequently. The important update is adding new objects (and their children which are also objects)

The two options that I see are:

SQL relational database, with a table that has two keys (parent and child)
MongoDB style document database, faster query times (I think, since we can use object ID as index) but slower and more chances at messing up

Is there something I'm missing? What is likely the best approach?


texts: floating point exception in isql with mariadb

texts:
I have this problem in my Debian server :

$ isql -v asterisk-connector
[1]    13397 floating point exception  isql -v asterisk-connector

I already checked logs / google and got nothing

Distributor ID: Debian
Description:    Debian GNU/Linux 8.3 (jessie)
Release:    8.3
Codename:   jessie

mysql  Ver 15.1 Distrib 10.1.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

unixODBC 2.3.1

This configuration works on other non Debian servers but now I have this problem only on Debian even calling unixODBC 3

$ isql -3 -v asterisk-connector
[1]    13464 floating point exception  isql -3 -v asterisk-connector

If I missed any information require for debugging let me know


texts: How to disable flashback query logging for a specific table (Oracle)?

texts:
We have a specific table that has a lot of activity and it creates a lot of change records. The consequence is that the flashback data only goes back a couple of days. That is OK for many cases but it would be beneficial to have access to more historical data.

We would like to either restrict logging on that table. Or disable it completely. I imagine that we may be able to do this by tablespace, I just have not found much on how to make these changes.


texts:
I'm trying to do my first Function in PostgreSQL, but I can't to do it.
I was write this piece of code

CREATE OR REPLACE FUNCTION getCars ()
   RETURNS TABLE (
      car_name VARCHAR,
      id_car INT
)
AS $$
BEGIN
   RETURN QUERY SELECT
      brand,car_id
   FROM
      cars;
end;
$$

LANGUAGE plpgsql;

But... The error is when I'm trying to call taht function:

SELECT getcars();

Where is the issue?

the error message it's:

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type text does not match expected type character varying in column 1.
  Where: PL/pgSQL function getcars() line 3 at RETURN QUERY

Thanks in advance!


texts: Why does SemiJoin node receive so bad statistic even after runnning ANALYZE

texts:
PosgtreSQL 9.4

I have two tables:

CREATE TABLE home_transactions( id serial NOT NULL, transaction_id integer );
CREATE TABLE transactions( id serial NOT NULL, user_id, amount numeric(13,4) NOT NULL );

The tables are relatively small (~100 rows each). Now, I need to select all (user_id, amount) pairs for users who have home_transactions. That's a classic usage of SemiJoin. But the table has the following statistic:

user_id = 959: 92 home trasactions
user_id = 811, 551, 589, 949: 4 home_trasactions

so, the query:

EXPLAIN ANALYZE SELECT user_id, amount
FROM transactions t
WHERE EXISTS (SELECT 1 FROM home_transactions ht WHERE ht.transaction_id = t.id)

produces the following analyzed plan:

Hash Semi Join  (cost=3.43..98.26 rows=4 width=8) (actual time=0.625..0.673 rows=108 loops=1)
                                     ^------EXPECTED                           ^-----ACTUAL
  Hash Cond: (it.id = bn.internal_transaction_id)
  ->  Seq Scan on transactions t  (cost=0.00..94.40 rows=146 width=12) (actual time=0.554..0.609 rows=146 loops=1)
  ->  Hash  (cost=2.08..2.08 rows=108 width=4) (actual time=0.029..0.029 rows=108 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on home_trasactions ht  (cost=0.00..2.08 rows=108 width=4) (actual time=0.002..0.015 rows=108 loops=1)
Planning time: 0.206 ms
Execution time: 0.710 ms

QUESTION: Why is there so different expected and actual rows? I ran VACUUM ANALYZE right before the execution but it didn't help. How can I deal with such a distinction?


texts: Semantics and significance of a one time query execution, delete with Like vs =

texts:
I was required to delete the values of a certain table XX_choice_value, but this will be a delete run only once in two different stages. Raw query execution and nothing more, and then in my Pull-request one of the reviewers said that I should only use this:
delete from xx_choice_value where code  = 'INIT_ACCORD_PAY';

Instead of this:
delete from xx_choice_value where code like 'INIT_ACCORD_PAY';

meaning changing the equal operator for the like, because I was not using a pattern and that like added a small performance issue.  I explained that this was a one time query execution not used in a loop and that for this matter the like and the '=' where significantly similar. In the end I changed it to satisfy the semantics of the query, which I agreed.
But I do not agree on the performance issue since is really, for me, irrelevant for this case.
Is there something I am missing besides semantics? performance is an issue in this case ?


texts: Linked server in SQL Server 2005 using NT authentication impersonation

texts: I am trying to Link a server using an NT authentication as the impersonated connection, but this connection will be static to a specific user instead of passing the currently logged-in user's credentials.  Is there any way to do this without setting up a new SQL-server account on the server I am linking to and using that for an unlisted user.


texts: Connection to Reporting service fails in SQL Server 2012

texts:
I am trying out Form authentication in Reporting Service to work. But not successful. The problem is I can't connect to report server both from Management Studio and Report Manager.
Actually I am following the steps from http://bharathonsqlserver.blogspot.sg/2012/09/forms-authentication-in-ssrs-2012.html#comment-form.


texts: REST webservice as a Foreign Data Wrapper in postgresql

texts:
I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find any resources on how (or if it's possible at all) to embed the XML output of a REST API in a foreign table.

Anyone know if this is possible and if so has any leads on how to implement it?


texts: Rolling update to all rows in innodb table (no rollback if aborted)

texts:
I have the following table, which stores the Social Security Death Master File database (~86M rows):

CREATE TABLE `death_master_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ssn_an` smallint(5) unsigned DEFAULT NULL,
  `ssn_gn` tinyint(3) unsigned DEFAULT NULL,
  `ssn_sn` smallint(5) unsigned DEFAULT NULL,
  `as_of` date DEFAULT NULL,
  `change_type` varchar(1) DEFAULT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `first_name` varchar(15) DEFAULT NULL,
  `middle_name` varchar(15) DEFAULT NULL,
  `name_suffix` varchar(4) DEFAULT NULL,
  `verify_proof_code` varchar(1) DEFAULT NULL,
  `death_year` smallint(4) unsigned DEFAULT NULL,
  `death_month` tinyint(3) unsigned DEFAULT NULL,
  `death_day` tinyint(3) unsigned DEFAULT NULL,
  `birth_year` smallint(4) unsigned DEFAULT NULL,
  `birth_month` tinyint(3) unsigned DEFAULT NULL,
  `birth_day` tinyint(3) unsigned DEFAULT NULL,
  `state_of_residence` varchar(2) DEFAULT NULL,
  `last_known_zip_residence` varchar(5) DEFAULT NULL,
  `last_known_zip_payment` varchar(5) DEFAULT NULL,
  `extra` varchar(7) DEFAULT NULL,
  `lifespan` mediumint(8) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ssn_as_of` (`ssn_an`,`ssn_gn`,`ssn_sn`,`as_of`),
  KEY `index_death_master_files_on_last_name_and_first_name` (`last_name`,`first_name`),
  KEY `idx_dob_ssn` (`birth_year`,`birth_month`,`ssn_an`)
) ENGINE=InnoDB AUTO_INCREMENT=85952194 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

I tried populating lifespan using update death_master_files set lifespan = datediff(concat(death_year, "-", death_month, "-", ifnull(death_day,1)), concat(birth_year, "-", birth_month, "-", ifnull(birth_day,1)));

Unfortunately, there was an unrelated issue which caused the server to restart, thus killing the transaction midstream. Now I'm looking at ROLLING BACK 1 lock struct(s), heap size 376, 1 row lock(s), undo log entries 32937515 in my SHOW ENGINE INNODB STATUS, which is blocking all further actions on that table until it finishes. :(

One speed improvement would seem to be using update death_master_files set lifespan = datediff(MAKEDATE(death_year, 1) + INTERVAL (death_month-1) MONTH + INTERVAL (ifnull(death_day,1)-1) DAY, MAKEDATE(birth_year, 1) + INTERVAL (birth_month-1) MONTH + INTERVAL (ifnull(birth_day,1)-1) DAY); instead of the string manipulation.

There's no need for any transactions on this. The table is not in use yet, and the lifespan column is either NULL or the value populated by this update. The update is multi-run-invariant. There are currently no slaves on the database either.

I'm going to let it finish the rollback (which'll take hours…). The next time I run it though, I'd like it to run such that it just writes, without rollback/transactions, so that if something dies, it can resume where it left off.

So: any suggestions for how to run UPDATE on the entire table, with rolling updates per row (no locking / transactions)?

(Bonus: it'd be nice if I don't have to do an OPTIMIZE TABLE after this either, since that also takes a while with an 8 GB table. This is the only change since the db was populated [by 1krow bulk inserts from the raw file parser].)


texts: Is it possible to CREATE CERTIFICATE using certificate from the Windows Certification Store?

texts:
Additional Information

With SQL Server 2016 SP1 we have the availability to use Always Encrypted functionality with the Standard Edition of the server. One is able to create a certificate in the Windows Certification Store and mapped this certificate to COLUMN MASTER KEY - then, use the CMK to protect COLUMN ENCRYPTION KEY used for encrypting data in particular column.

Using Windows Certification Store is handy:

because other Extensible Key Management providers are for the Enterprise Edition (like Hardware Security Modules - HSM)
the access to the certificates is manage in central prlace
we skip to levels of the encryption hierarchy (using Service Master Key and Database Master Key)

Case scenario

The Always Encrypted functionality is used to encrypted all data in a particular column. I need to encrypted only some of the data, so I need to use a symmetric key or asymmetric key. I want to protect them with a certificate which is stored in the Windows Certification Store in order to control the access to the keys from there. Is this possible to create such certificate?


texts:
I have a table we just transferred from MS Access to SQL Server 2016.

It's a simple table for creating load numbers for shipping products. Using access we link to the SQL Server and we are able to add new records and create a load number but we can not edit the record after that. What setting am I missing?

It must be in permissions. Any ideas?  I have noticed that I can edit some older records and records that I create so it must be a permissions setting somewhere.


texts: how to pass the "file path" as a parameter in powershell?

texts:
I have recently added a SQL Server JOB on SERVER-A that goes to SERVER-B and backups all the SSAS databases.

This seems to be fine.

However when I want to delete backups older than 3 days, using the function below, I get the error message on the picture below.

It says that the path is wrong, but that is exactly the path where my .abf files are.

How should I pass the path to powershell?

this is my function in powershell:

Function DeleteOldFiles
{
    try
    {
    $limit = (Get-Date).AddDays(-$DaysKeepBackup)
    Get-ChildItem -Path $checkFolder -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | foreach ($_) {Remove-Item $_.fullname -Force}
    #Write-Host "Backup files created before $limit have been deleted"
    }
    catch
    {
        Write-Error $_
    }
}

Now I have changed my function in Powershell.

Function DeleteOldFiles
{
    try
    {
    $limit = (Get-Date).AddDays(-$DaysKeepBackup)
    Get-ChildItem -Path  Microsoft.PowerShell.Core\FileSystem::$checkFolder -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | foreach ($_) {Remove-Item $_.fullname -Force}
    #Write-Host "Backup files created before $limit have been deleted"
    }
    catch
    {
        Write-Error $_
    }
}

Now I get a different error:

  The job script encountered the following errors. These errors did not
  stop the script: A job step received an error at line 124 in a
  PowerShell script. The corresponding line is '
  DeleteOldFiles '. Correct the script and reschedule the job. The error
  information returned by PowerShell is: 'Access is denied '


texts: Remove the deeper jsonb element in Postgres

texts:
[
   {
      "type":"test.added",
      "values":[
         {
            "field":"field1",
            "value":"fieldValue1"
         },
         {
            "field":"field2",
            "value":"fieldValue2"
         }
      ]
   }
]

I want to remove the 'field1' object pair from the JSON in postgres

Here is what I have tried so far,

updatedJson -> 0 ->> 'values' - '"field1"'
i tried using the #- Delete the field or element with specified path
(for JSON arrays, negative integers count from the end)

Both steps didn't help me. I am new to Postgres. Is there any way like getting the path and deleting it. I am using jsonb. PostgreSQL version: 9.6.
Kindly help


texts: MySQL on dedicated 2048 Linode starting to swap

texts:
Versions in this post: MySQL 5.6.21 Community Server, Linode 2048, Debian 7.6 plain vanilla install from Linode

I used to run a 1024 Linode i.e. 1GB ram for my MySQL instance that serves a fairly busy website.
When I recently, this weekend, made an upgrade to the website I took the opportunity to install MySQL on a 2048 Linode instead.

The major architectural change made is that I now use only InnoDb tables instead of MyISAM that we used before.

Also, the 1024 linode I used before had 8 CPUs and now there's only 2, as I'm sure you are all aware.

So, before - in the busy hours - I could see cpu spikes but there was never a memory problem. Ever.

Now, I just had a auto-notification mail saying how my Linode instance had started using the filesystem inordinately much and I logged on and saw that MySQL was using 86% of the memory (which I guess could be OK since InnoDb is configured to use 1400MB for the buffer pool). However, it's using max swap and the instance is really struggling to handle the load, which is not really near what peak hour will look like.

Any thoughts? Any help at all would be much appreciated.

my.cnf looks like so

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

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir        = /usr
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = my_machine_name

log-error       = /var/log/mysql/error.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1456M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 2
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Yes, it's a percona tool standard file. I really have very little knowledge of what does what in MySQL. The change to InnoDb was made to avaoid table locks and to actually be able to NOT have to upgrade the machine too soon.

Thanks for any help at all.


texts: What are the minimum requirements for running a dtsx package on a PC?

texts:
One of my clients has some very specific needs to get a spreadsheet run through some ETL processes I have created.

I have developed an SSIS .dtsx package which imports an Excel spreadsheet into SQL Server and then performs some transformation processes and then exports the results to another Excel spreadsheet. I am wanting to hand off the tool to the client so they can kick off the ETL process ad-hoc. In order to allow them to run the DTSX package, what are the minimum requirements that are needed to get this running on their system? Can this be done with SQL Express or do they need to have Integration Services running to execute the package?


texts:
I have a classic 3-environments setting (dev, staging and production), each with its own RDS PostgreSQL 9.3. The client/server versions are psql (9.3.5, server 9.3.3).

In order to keep the schemas aligned, I have a periodic schema diff job, which notifies me when the schemas differ.

A few days ago, I started getting many of these diffs:

CREATE TABLE ...
...
<     title pg_catalog.text,
<     subtitle pg_catalog.text,
---
>     title text,
>     subtitle text,

The text fields in staging was somehow converted to pg_catalog.text in prod.

What is the difference between text and pg_catalog.text? How can I equate the two schemas?


texts:
We have a huge enterprise system, with geographically seperated SQL servers.

We have some requirements related with data replication between these servers.

We have considered the Sync Framework option, but there is a requirement regarding multiple parallel data replication. Thus, Sync framework does not seem to be an option.

Also, we do not have a central database; requirements state that user shall configure the topology and related conflict resolution steps. There are three conflict resolution options; "source wins", "destionation wins" and "timestamp based".

Also, note that, database structure is a bit complicated and we have some deep hierarchies.

Lastly, the only way of communication between these servers is using port 80; using other ports is not an option.

I need to decide how to implement the replication structure, but I do not seem to have any options other than creating a replication structure manually and implementing all features.

Do you know any tools, or have any ideas how to handle replication easily without any central SQL server and with user configurable topologies?


texts: PK Violation after transactional replication

texts:
I have an application set up with transactional replication being pushed to a standby machine that will be used for emergency failovers. The replication appears to be working, any inserts made to Server 1 will automatically appear at Server 2.

However, I can't quite get the failover working. In the scenario that Server 1 becomes unavailable (which is the only scenario where Server 2 will ever be used, so the replication is one-way), the idea is that work should continue at Server 2, and that the transition should be somewhat seamless since all data has already been replicated.

But when moving to Server 2, after making sure that all updates on Server 1 have been transferred, I keep getting primary key violation exceptions, for some tables.

  Violation of PRIMARY KEY constraint 'PK_TableA'. Cannot insert duplicate key in object 'dbo.TableA'.

A simple query such as

INSERT INTO TableA (Field1, Field2, TableB_ID) VALUES ('a','b', 6)

will yield the above error. It seems that when I instruct the table to assign an identity of its own, by omitting it from the query (TableA has an ID int identity(1,1) field), SQL Server will auto-assign an ID that violates a PK constraint. Why would this be?

TableA has a trigger for INSERT and DELETE that does a simple denormalization job

UPDATE TableB
SET Count = (SELECT COUNT(1) FROM TableA WHERE TableB.ID = TableA.TableB_ID)
WHERE ID IN(
    -- Fetch affected ID's from deleted or inserted rows
    SELECT DISTINCT TableB_ID FROM deleted
    UNION
    SELECT DISTINCT TableB_ID FROM inserted
)

This was accidentally not a part of the Server 2 database at the time of the replication, and I inserted it afterwards. Consistency in the TableB.Count field is not critical for the task at hand. The PK Violation occurred before the trigger existed in Server 2, as well as after creating it.

At both Publisher and Subscriber, the ID field that is yielding the violations has the following definition:

[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL

I suppose the NOT FOR REPLICATION part is redundant on the Publisher, as no replication job will ever write to it, but I can't see that it should be the cause of the problem, either.


texts: I am getting error while restoring FileGroup on SQl server 2012

texts:
I have create two filegroup FG1 and FG2.
and i have create one table student and associated with FG1. and i have created backup for FG1 with Test.bak file .

now i wanted to restore the Test.bak

i am getting error like attached screen ..please advise

regard's
Ravi


texts: How should I configure these disks on a SQL Server for a BI configuration?

texts:
Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks

2 x 150 (10k)
6 x 150 (15k)

They are all local disks.

My requirements

My DB is 350gb and set to default 10% growth
My OS & SQL Server are Server 2k8R2 (C: drive OS + page + applications = 55Gb)
Log requirements are about 70gb and set to default 10% growth and is routinely truncated
My TempDb is about 12gb currently and set to default 10% growth

My problem is that I’m trying to understand where to best put the TempDB and OS and the Log. My experience is limited in optimal configuration of these two

This is not an online transactional system. It has heavy data write (new data + indexes rebuild/reorg) then heavy data read (I'm estimating at about 50/50) processing for about 13 hours, and then just quiet.

My understanding is that the TEMPDB is heavily used during normal processing compared to the log.

My idea is the following

2 x 150g (15k) Raid 1 = 150g for OS + TempDB
2 x 150g (10k) Raid 1 = 150g for LOG (note slower disks here)
4 x 150g (15k) Raid 5 = 150g for data

Does this sound like a good idea? I could then swap the Log + TempDB if needed.

Am I breaking a cardinal rules like never put TempDB on OS disk due to paging concerns, or perhaps never put log on slower disk than data?

Edit:

We also have a SSAS on the system and the end users access only the Cube. The 50% read above is based on the time it takes to process SSAS database.


texts: Getting error SP2-0158: unknown SET option

texts:
I am getting error while running my script

SP2-0158: unknown SET option "errorlogging"
SP2-0158: unknown SET option "echo"
SP2-0158: unknown COLUMN option "ENTMAP"
SP2-0158: unknown COLUMN option "ENTMAP"
SP2-0158: unknown COLUMN option "ENTMAP"
SP2-0158: unknown COLUMN option "ENTMAP"
SP2-0158: unknown COLUMN option "ENTMAP"
Connected

SP2-0158: unknown SET option "markup"
Loading count
SP2-0158: unknown SET option "markup"

no rows selected

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

My Script is:-

SET VERIFY OFF
    SET ECHO OFF MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>File Loading Monitoring Report</TITLE> -

File Loading Monitoring Report: &_Date
 -
" -
BODY "TEXT='black'" -
TABLE "WIDTH='70%' BORDER='5'"

column "StartTime" heading "StartTime" format 99 ENTMAP OFF
column "NumberOfFiles" heading "NumberOfFiles" format 999,999,999 ENTMAP OFF
column "Volume_GB" heading "Volume_GB" format 999,999,999 ENTMAP OFF
column "ClusterName" heading "ClusterName" format 999,999,999 ENTMAP OFF
column "IPAddress" heading "IPAddress" format 999,999,999 ENTMAP OFF

define connStr = 'OWNER/_OWNER@abc.com:2521/abc.com';

SPOOL d:/deployments/One/content/FileVolume.html

----------------------------------
-- GetQueue
----------------------------------
connect &connStr
SET PAGESIZE 200
SET MARKUP HTML ENTMAP OFF

PROMPT Loading count

SET MARKUP HTML ENTMAP ON

select to_char(df.starttime, 'YYYY-MM-DD') StartTime, dc.clustername, round(sum(df.filelength)/1024/1024/1024,0) Volume_GB, count(*) NumberOfFiles
from files df join fileloadstatus dfs on df.fileid = dfs.fileid
    where df.starttime > sysdate - 14 and df.starttime < sysdate +1 and dc.clusterid = 81 and p.name like '%Stage1%'
group by to_char(df.starttime, 'YYYY-MM-DD'), dc.clustername
order by 1 desc, 2 asc;

SET MARKUP HTML ENTMAP OFF

disconnect;

SPOOL OFF
exit


texts: how to get a sum total of each column and row - postgresql [Crosstab or Case]

texts:
I have to count the records according to the criterion, they are much more filters but I have summarized.

I could use either of two ways.

1#

SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, dim_tiempo.mes, count(*)::int
FROM hec_his INNER JOIN dim_espacio ON hec_his.id_espacio = dim_espacio.id_espacio
INNER JOIN dim_tiempo ON hec_his.id_tiempo = dim_tiempo.id_tiempo
WHERE (dim_tiempo.ano=''2012'' and hec_his.codigo1=''Z001'')
GROUP  BY 1,2 ORDER  BY 1,2',
$$VALUES ('01'), ('02')$$) AS ct (desc_estab character varying, "Enero" int, "Febrero" int)

2#

SELECT NULLIF(dim_espacio.desc_estab, 'TOTAL') AS Establecimiento,
COUNT(CASE mes WHEN '01' THEN 1 ELSE null END) AS Enero,
COUNT(CASE mes WHEN '02' THEN 1 ELSE null END) AS Febrero,
COUNT(CASE mes WHEN '03' THEN 1 ELSE null END) AS Marzo,
COUNT(CASE mes WHEN '04' THEN 1 ELSE null END) AS Abril,
COUNT(CASE mes WHEN '05' THEN 1 ELSE null END) AS Mayo,
COUNT(CASE mes WHEN '06' THEN 1 ELSE null END) AS Junio,
COUNT(CASE mes WHEN '07' THEN 1 ELSE null END) AS Julio,
COUNT(CASE mes WHEN '08' THEN 1 ELSE null END) AS Agosto,
COUNT(CASE mes WHEN '09' THEN 1 ELSE null END) AS Septiembre,
COUNT(CASE mes WHEN '10' THEN 1 ELSE null END) AS Octubre,
COUNT(CASE mes WHEN '11' THEN 1 ELSE null END) AS Noviembre,
COUNT(CASE mes WHEN '12' THEN 1 ELSE null END) AS Diciembre,
COUNT(mes) AS Total
FROM hec_his
INNER JOIN dim_espacio ON hec_his.id_espacio = dim_espacio.id_espacio
INNER JOIN dim_tiempo ON hec_his.id_tiempo = dim_tiempo.id_tiempo
WHERE (dim_tiempo.ano='2012' and hec_his.codigo1='Z001')
GROUP BY desc_estab;

the result is the same but have trouble getting the totals rows and columns :

the result I want is something like this:


texts: how to convert from raiserror to throw in the current context?

texts:
I have a function that checks is the file is present on disk in an specific location.

Using that function I am checking for the latest backup, in the code below.
If we don't have a backup anymore, for a specific date, I want to raise an error.

Currently I am using raiserror but
because of Deprecated raiserror detection I would like to use throw instead.

How can I do it?

this is part of my `stored procedure`:

IF @ServerNameToRestoreOn IS NULL
               THROW 50001, '@ServerNameToRestoreOn cannot be NULL - it must be the name of server\instance you want to restore the database to', 1

               IF @Database IS NULL
               THROW 50001, '@Database cannot be NULL - it must be the name of the database you want to restore', 1

               IF NOT EXISTS
                (
                    SELECT MAX(backup_set_id)
                    FROM msdb.dbo.backupset a
              INNER JOIN msdb.dbo.backupmediafamily b
                     ON a.media_set_id = b.media_set_id

                    WHERE a.database_name = @Database
                      AND a.type='D'
                      AND a.is_copy_only = 0
                      AND a.backup_finish_date < @StopAt
                      AND master.dbo.fn_FileExists(b.physical_device_name) = 1
                    GROUP BY database_name
                )
                BEGIN

                         SELECT @sql = 'The database called "%s" does not have a backup before' + CAST (@StopAt AS VARCHAR(20)) + @vCrlf + @vCrlf
                         RAISERROR(@sql ,16,1,@Database)

                END

How can I replace the raiserror by throw?
I want to show the @Database and the @stopat in the error message

or, in better wording:

how do you pass THROW() a message where you can still use printf-style substitution like you can with RAISERROR?


texts: Daily SQL Server Alert: BackupVirtualDeviceFile::SendFileInfoBegin: failure on backup device

texts:
I'm running a ~100Gb database on Microsoft SQL Server 2016 (SP2-GDR)  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: ) (Hypervisor)

Every weekday night just after 7pm I get a SQL Server Alert email like this:-

SQL Server Alert System: 'Severity 016: Miscellaneous User Error' occurred
BackupVirtualDeviceFile::SendFileInfoBegin:  failure on backup device

My regular daily and weekly database backups all happen at 2am (so it's not those), so I can only assume it's my hourly transaction log backups. But the only SQL agent job that fails is a C# program doing a database read (it times-out), and I can find no other errors in my SQL Agent Logs at that time. And my daily CheckDB Maintenance "Check Database Integrity Task" says the databases are fine. Any thoughts or suggestions? Thanks.


texts: Checking for ApplicationName in a transaction through a Before Trigger

texts:
I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the user is not allowed to make this transaction.

Is there a way to create this script?


texts: Import binary database file into postgres

texts:
I have a quite newbie question that I do not know if is possible or not:

I had to format my computer, and the only way to keep my postgres database was accessing to data with my folder browser (nautilus)

So now I have the data folder with a lot of binary data inside, from /var/lib/postgresql/9.3/main/base

My question is:

Is there any form to import theese data to my (now) new postgres database?

I think copy paste is not going to work.

Maybe with some import? Or have I permanentely lost all my data?

I run postgres 9.3 in Ubuntu 14.04

Thanks


texts:
I am new to DB.
We have galera cluster on Test environment where mariaDB is replicated in 3 servers.
Now customer want us to work with only 1 server. they cant provide 3.

Can we do replication on single physical server?
Is it good idea? How can we achieve that?

Thanks in advance.


texts: select from Dynamic Management View causes blocked processes

texts:
we have troubles regarding querying from a dynamic view in SQL Server. We basically check for an existing object lock on a table (CHANGELOG) from sys.dm_tran_locks with the @@spid in a stored procedure which does a few other things too.
In some cases, this leads to blocking of random pages, tables rids etc.
In the "SentryOne" Tool, we have blocking situations looking like this
As there is no actual way to find out all locks from @@spid, how can it be possible that a simple select from a dynamic view actually blocks other tables? We execute the select in READ UNCOMMITED
The query that is displayed in  line 170:
if exists (select NULL from sys.dm_tran_locks l with (nolock)
        where   l.resource_associated_entity_id = object_id('[dbo].[CHANGELOG]')    and
            l.request_session_id = @@spid and
            l.resource_type = 'OBJECT')

Does anyone have an idea how to avoid these blocks? Or are we missing some point and the select is not even the real reason for the block?


texts:
I am using PostgreSQL 9.3 server on my production server. Everything had been working fine for the past couple of months. But since the past week or so, PostgreSQL seems to have becomes very slow with increased CPU and Disk usage.

Here is the graph of CPU, Disk and Bandwidth usage over the past month

I'm using memcached to cache most of the queries. Web pages with few basic queries are faster while pages with complex queries take a lot of time. The server response time for such pages has increased from 0.2 seconds to 4 seconds (measured using Google PageSpeed Insights).

Following is my postgresql.conf tuned using PgTune.

default_statistics_target = 50
maintenance_work_mem = 60MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 704MB
work_mem = 6MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 240MB
max_connections = 80

Any ideas on what could have gone wrong? My website is a Django application hosted on DigitalOcean (1GB Ram, 30GB SSD Disk, Ubuntu 14.04 x64).


texts:
I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message.

  "UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being
      replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or
      tracked by Change Data Capture."

I have followed the instructions on a post that disable CDC for the table for which sp has command UPDATETEXT and then create SP and after sp creation re- enable CDC for that table

Question:

Whats the reason behind this Error ?
Will UPDATETEXT command effect logging in future? because we are thinking to use CDC for auditing change.


texts: Optimizing postgres row overlap constraints involving UUIDs and GIST

texts:
For various reasons all my tables have UUIDs as primary keys and I need to exclude duplicate rows where the combination of area_id, user_id, and finished_at <-> started_at window.

The only way I can see to do this involves casting UUIDs to text inside the overlapping_allocations constraint below.
However this seems stupidly wasteful, am I missing a trick?

This is all running on Postgres 9.3.5 and there is freedom to enable other common extensions.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "btree_gist";

CREATE TABLE areas(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    name text NOT NULL UNIQUE,
    -- blah blah, lots of other fields
)

CREATE TABLE allocations(
    id uuid PRIMARY KEY default uuid_generate_v1(),
    area_id uuid references areas,
    user_id text NOT NULL,
    started_at timestamp with time zone NOT NULL,
    finished_at timestamp with time zone DEFAULT NULL,
    created_at timestamp with time zone default NOW(),
    CONSTRAINT valid_times CHECK (started_at < finished_at),
    CONSTRAINT overlapping_allocations EXCLUDE USING GIST (
        -- These two casts are horrid but GIST doesn't understand uuids
        -- see here https://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type
        cast("area_id" AS text) WITH =,
        user_id WITH =,
        tstzrange(started_at, COALESCE(finished_at, 'infinity'::timestamptz) ) WITH &&
    )
);


texts: Always On AG cluster with 2 servers that fail over to each other

texts: I have 2 servers. One is dedicated as an OLTP server and the other will be a DW server. The OLTP server will have our application DB on it and our DW will have a couple of databases on it used for reporting. I would like to cluster them and build an AG for our OLTP DB that will failover to the DW server and provide the DW with a read-only replica for reporting. By the same token I would like to build the 2 DW databases into an AG and have them failover to the OLTP instance strictly for fault tolerance even though they technically could be read only replicas there as well. Is this possible? I need fault tolerance, I need the read-only replica for reporting and I need some fault tolerance for the DW too and I only have 2 servers and Enterprise licensing for those 2 servers.  Thanks!


texts: version controll application/system for mysql databases

texts:
Possible Duplicate:
  Keeping track of schema changes

Hello,

How do you keep your mysql database schema under version control.


texts: trying to log into correct database without password

texts:
My SQL 5.6.31 is setup on RHEL 7.2 as follows:

I can log in using the root user account as follows:

mysql -u root -p
Enter Password:

I created two normal users (one via my command line and one via my PHP based application)

Normal User Created via Command Line:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

Normal User Created via my PHP based Web App:

My PHP Web app is just inserting the username into the database (database name: practiceDB) using INSERT INTO command in the code. Say for
example normal user that got created and stored into the database has name peter

Scenario #1:

When I log in into the mysql from the command line using username jeffery and password mypass like the following:

mysql -u jeffery -pmypass

And run the command, show databases, I can see my practiceDB and other default MySQL databases like information_schema.

Scenario #2:

Since one of the normal user (peter) got created by the webapp and is residing in the database (practiceDB), I tried to access from the
commandline the user without using any password as follows:

mysql -u peter

I can get into the mysql command prompt. Infact, forget about specifying the existing username peter in the above commandline.
Even if I specify any thing , I can get into the command prompt. Also, when I checked show databases, I only saw only information_schema database
which is not where my user got stored.

Could anyone tell me why this is happening?


texts:
I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem:

2 tables:

 - Drivers (id_driver, name, etc...)
 - Trucks (id_truck, model, id_driver,
   etc...)

The user will fill a form with the truck's info, but a truck may have more than one driver, and thats my problem. I don't know what to do there.

Thanks in advance.


texts: Multi-instance 2-node SQL Cluster - do instances need their own disks?

texts:
I currently have a single default instance of SQL2017 Standard on a 2-node cluster.
Against my preference, I need to setup a second instance on the cluster (the new database requires some users to have sysadmin so they can't be hosted alongside other databases on the existing instance :-( )

On the existing cluster, only the Active node is licenced.

When I install a second instance, do I HAVE to use a separate set of disks, or can it be installed on the same disks as the default instance?
(I'm not even sure if it is possible, let alone whether it is preferable)
Am planning to host BOTH instances on a single node - resource requirements are not high, so can be managed.

My concern is that one of the instances 'could' fail over to the second node.
But the second node is not licenced, so am not allowed to run an instance on each node (though I believe I do have a short period to fail it back).

Presumably I need to implement some monitoring to alert me if EITHER instance fails over, and get it failed back ASAP.


texts: Cannot access Properties page of a database

texts:
I'm in the process of decommissioning a database server and migrating the databases from one server to another.  I was attempting to get the the properties of the database and received a SQL Error popup.

Property Owner is not available for Database '[database name]'.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

Facts:

Server 2003 Standard with SP2
SQL Server 2005 Standard - 9.0.1399
I'm a sysadmin, so it shouldn't be perms issue
Had another sysadmin perform the operation with the same error
DBCC CheckDB listed no errors
Able to backup w/o error
Took DB offline/online w/o error

Any suggestions or tests that I can perform and report on?


texts: Poor performance by LINQ to SQL generated query execution plan

texts:
Can you please check the attached execution plan from my SQL Server 2016 and suggest me where to improve.
The query is generated from the .Net application using LINQ to SQL.
I can see large sort operation in the plan and i have indexes for SORT columns.Also i see OR in where conditions.
Can anyone please direct me where the issue is?I am still learning on how to debug from the execution plan.
Execution Plan
Even after hard-coding the linq parameters it takes 23 seconds to run.
Since it is generating from .net application,i have less control over the query and i tried adding different indexes(some covering indexes to avoid key lookups) etc.,but of no help till now.
Appreciate any kind of help in providing more insight into the plan on problem areas.


texts:
Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM'
How do I load all rows, with 'allotedto' column containing any one of given input values?
(eg: all rows alloted to RAM or KLM) ?

Here, we don’t want the solution to repeat column name multiple time like below:

select * from table where [allotedTO] like '%RAM%'  OR [allotedTO] like '%KLM%'

We want to implement solution similar to below queries, but it is not working   :

select * from table where [allotedTO] like '%RAM|KLM%'

SELECT PATINDEX('%[RAM|KLM]%', [allotedTO])