texts: Moving a large MySql database(Over 15 GB) from server to server mysqldump

texts:
I need to move a mysql database that is about 17 GB that is on a Cpanel/Centos based server that I do not have root access, to a Centos server that I have full root access.  I do have limited SSH on the Cpanel with mysqldump available(I think).

When doing a test database download from the Cpanel Mysql backup utility, the server ran out of ram and shut down.  I only did about 250 MB as a test for the download.

My choices seem to be:

Master/Slave replication(I don't have root so that seems out)
Rsync the database files(I don't have root so that seems out)
Mysqldump
PhpMyadmin(Crashes the server when the tables have too many rows)

At this point I am planning on doing a mysqldump from the Cpanel server.  I don't want to have it locked down for a super long time, since our website is live.  I saw that I can use the mysqldump with the -r and it will cause the buffer to go row by row instead of loading the whole table into the buffer.  Some of the tables have 2 million rows.  Most are under 50 thousand rows.

What is the best way to move the database without locking down the system or causing the server to crash because of the ram being overloaded?

Both servers have powerful CPU's and 16 GB of ram with fairly current MySql versions.

Would you recommend a table by table dump or is that even necessary?

I might be able to get the current Cpanel host to setup my.cnf for me to set up the master slave connection(Not sure about that one though)


texts: Remote Desktop Session's Machine ID for SQL Server Query

texts:
How to get machine name of a user, who is connected on an instance through Remote Desktop and used windows authentication login. I need it for each query she has executed.
sys.dm_exec_connections just provide me connectino type i.e. NTLM. And windows event id 4624, gives me all the information i need BUT how to relate this information with my session id in SQL Server ???


texts: Oracle database 18c – ORA-12541: TNS:no listener

texts:
I have installed Oracle 18c in my local machine and i have created a user names as

USER_NAME := c##scott
Passkey : oracle

When i used to connect to that user using cmd. The following error is populating out.

Command: sqlplus c##scott/oracle@agndev

ERROR:
ORA-12541: TNS:no listener

After checking the listener error I have rechecked my listener file

Listener File:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\ora_base)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\ora_base\bin\oraclr18.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = AGNDEV))
    )
  )

Tnsnames.ora file:

# tnsnames.ora Network Configuration File: D:\ora_base\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )

    AGNDEV =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = AGNDEV)
        )

  )

I am new to 18c can anyone say the solution to this error.

Thank you.


texts: Best practice: Unions or a derived table?

texts:
I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:

CREATE TABLE `pending` (
  ...
  `invoice` int(11) DEFAULT NULL,
  `lid` int(11) DEFAULT NULL,
  `custacct` varchar(21) DEFAULT NULL,
  UNIQUE KEY `pend_inv` (`invoice`),
  KEY `pend_acct` (`custacct`),
  KEY `pend_pid` (`pid`)
  ...
) ENGINE=InnoDB

CREATE TABLE `done` (
  ...
  `invoice` int(11) DEFAULT NULL,
  `lid` int(11) DEFAULT NULL,
  `custacct` varchar(21) DEFAULT NULL,
  UNIQUE KEY `done_inv` (`invoice`),
  KEY `done_acct` (`custacct`),
  KEY `done_pid` (`pid`)
  ...
) ENGINE=InnoDB

And two similar tables, customers and locations:

CREATE TABLE `customers` (
  `acct` varchar(14) NOT NULL,
  ...
  PRIMARY KEY (`acct`),
  ...
) ENGINE=InnoDB

So, yes, when an invoice is not yet fulfilled, it's in the "pending" table, then when the company's finished with it, it gets moved to the "done" table. So any given invoice will only be on one of the two tables.

Unfortunately, we're still actively using the proprietary software that runs on this schema, so I am powerless to fix it. The "pending" table will usually have around 9000 rows, whereas the "done" table is ~800,000 and counting.

For two years (the full extent of my LAMP/MySQL experience), I have been writing UNIONs to capture information from the customers or locations tables, with the first query joining customers/location/both against "pending" and the second joining customers/locations/both against "done."

I've recently had a nagging feeling that writing two nearly-identical queries UNIONed together is probably newbish and should be done differently. So I finally applied my more recent learning to a problem I had "solved" in my first few weeks of working with MySQL and realized that I can just use a derived table with a UNION inside that subquery to achieve the same results with less typing.

The execution time is near-similar, usually 0.01s for the derived query and slightly less than that for the UNIONed query, but the dervied table query uses far fewer characters and requires less time to type out.

So my question for those with more experience, is using a derived table subquery more sane and maintainable in the long run? It's easier for me, the programmer, so that makes me suspicious that maybe it's not the best practice!

Sample queries below:

select c.whatever,l.whatever from customers c join
  (select d.custacct as acct,d.lid from done d where d.invoice=123456
     union
   select p.custacct,p.lid from pending p where p.invoice=123456
  ) as combi
    on c.acct=combi.acct join locations l on combi.lid=l.lid;

select c.whatever,l.whatever from done d join customers c on d.custacct=c.acct join locations l on d.lid=l.lid where d.invoice=123456
union select c2.whatever,l2.whatever from pending p join customers c2 on p.custacct=c2.acct join locations l2 on p.lid=l2.lid where p.invoice=123456;


texts: how to pass outer query param into inner in MySQL 5.7

texts:
Now I am using this query in MySQL 5.7 to count login count:
select
(
    select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count
) as login_count
from report_user a
group by user_id

when I calculate the inner login count, I just want to calculate by per user, then the user_id is specified, the result is correct. But when have many users in this table, the result is not correct. How to pass the outer user id into inner query in execute the query? I want this inner sql only calculate each user:
select sum(login_count)
    from (
        select avg(login_count) as login_count
        from report_user b
        -- where b.user_id = a.user_id
        group by user_id,date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
    ) as login_count


texts:
This is my query:

insert into [Consolidated_LoB].[dbo].[consold]
    ([shp_to_pstl_cd]
      ,[LoB]
      ,[catlg_nbr]
      ,[shpd_qty])
   select
      [POSTAL_CODE]
      ,[LoB]
      ,[PART]
      ,[SHIPPED_QUANTITY]
   from
      [ITB_Pank].[dbo].[ITB_consoldtd]

and the message I receive is:

  Arithmetic overflow error for type varchar, value = 436112652.000000.

Please advise

CREATE TABLE [dbo].[consold]
(
    [srce_loc] [char](3) NULL,
    [q2c_nbr] [char](8) NULL,
    [ln_itm] [char](3) NULL,
    [mfg_loc] [char](3) NULL,
    [shpd_qty] [int] NULL,
    [shpg_loc] [char](3) NULL,
    [inv_prof_loc] [char](3) NULL,
    [shp_to_pstl_cd] [char](10) NULL,
    [shpmnt_dt] [datetime] NULL,
    [shpmnt_typ_ind] [char](2) NULL,
    [tot_typ] [char](2) NULL,
    [catlg_nbr] [char](34) NULL,
    [LoB] [char](10) NULL
    [del_id] [varchar](12) NULL
)

CREATE TABLE [dbo].[ITB_consoldtd]
(
    [POSTAL_CODE] [float] NULL,
    [LoB] [char](10) NULL,
    [DELIVERY_ID] [float] NULL,
    [PART] [nvarchar](255) NULL,
    [SHIPPED_QUANTITY] [float] NULL
)

To simplify the mapping exercise, this is source to target

- [POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
- [LoB]              [char](10)      -> [LoB]            [char](10)
- [PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
- [SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]


texts: UnShrunk SQL Server database restore which is larger than available disk space

texts:
I cannot restore a Microsoft SQL Server database due to lack of hard drive space. The original database was 1.6TB and approximately 1.4TB was deleted from it and a backup was taken without shrinking the database.

The backup file is only 168GB and was sent to me on a hard drive through a courier. When I try to restore the 168GB backup file it tries to create the original 1.6TB .mdf data file and errors because all my available servers have less than 1TB of available disk space.

First I tried to restore the data files to a NTFS Compressed folder, but I got an error stating that the target file may not have the compression bit set.

Then, per this link: Shrink SQL database during restore

I have tried putting the restored .mdf file into a symbolic link folder which points to a compressed folder, however it errors:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'C:\' to create the database. The database requires
  1541063704576 additional free bytes, while only 748261179392 bytes are
  available. (Microsoft.SqlServer.SmoExtended)

Then, since this is running within a HYPER-V VM, I've tried adding a new virtual disk into a compressed folder, but the VM would not start complaining about one of the drives residing in a compressed folder.

Then, I tried adding a new HYPER-V virtual disk into a symbolic link folder which points to a compressed folder.  This works fine, except the file is not compressed while it's restoring and was getting close to running out of space before I killed it (I'm guessing Explorer may compress the file AFTER it is closed by the writing process "SQL")

The restored database should be only 168GB of actual data within it.

Does anyone have any solution?


texts: Prevent logins loss when restoring a database

texts:
I'm backuping and restoring databases accross two sql server (v12.0.2000).
But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent in the one that was restored. I expected it to be backuped to the bak file !

Is there any special manipulation for a login to be backuped in order to be easily restored on another server ?

I could not go on maintaining various script to restore the database on one hand and to recreate the logins on the other hand.

How could I create a consistent backup file that could easily be restored ?


texts:
I have a table with this structure:
create table licence_report
(
    id                          bigint         default nextval('licence_report_id_seq'::regclass) not null,
    created                     timestamp                                                         not null,
    last_modified               timestamp,
    licence_number              varchar(255)                                                      not null,
    state                       varchar(10)                                                           licence_type                varchar(255),
    constraint licence_report_pkey
        primary key (id, created)
)
    partition by RANGE (created);

I need to ensure that there is only one licence number in the state NEW, how do I add a constraint like this?
I tried the below:
CREATE OR REPLACE FUNCTION offroad.check_new_reports_by_licence(licence_number_check varchar)
    RETURNS BOOLEAN AS
$BODY$
    SELECT EXISTS(SELECT id
  FROM   licence_report
  WHERE  licence_number = licence_number_check
  AND    state = 'NEW');
$BODY$
    LANGUAGE sql VOLATILE
    COST 100;

ALTER TABLE licence_report ADD CONSTRAINT const_check_existing_new_reports_by_licence CHECK (check_new_reports_by_licence(licence_number) = FALSE);

I get the following error:
[23514] ERROR: check constraint "const_check_existing_new_reports_by_licence" is violated by some row

But the output of this query is 0 rows:
SELECT licence_number
FROM licence_report
WHERE state = 'NEW'
GROUP BY licence_number
HAVING COUNT(*) > 1;


texts:
does postgres deadlock if query update and insert in a table inside a function begin? plpgsql
function
Declare
select same table
begin
update multiple rows same table
insert row same table


texts: SSIS:How retrieve and insert an XML Column

texts:
I'm trying to Insert two columns from one table onto another. The first column is an int but the second column is an XML. I'm having issues inserting the xml column

In first SQL Task I'm doing a select of the the two columns and storing this information into an object.

In the for loop I'm passing on the information :

And in the Result set I'm declaring the variables :

I know that the Form_XML_Text is set to Varchar here but there is not XML option.

And running this insert statement:
INSERT INTO TEST
VALUES (?,?)

This is the error message I'm getting back:


texts: Force using an Index in Inner Join SELECT

texts:
I have the following query:

select  field1, field2 from table1 t1 with (index(IndSearch))
inner join table2 t2 ON t2.Id = t1.Id
where t1.date between '04/01/2019' AND '05/01/2019'
AND t2.field3 IN(....) AND t2.field4 IN(...)

Table1 has clustered index in t1.Id
IndSearch is a nonclustered index with (t1.Id, t1.date and other fields)
Table2 has a nonclustered index with (t2.id, t2.field3, field4)
Table1 contains more than 5M rows
Table2 contains more than 11M rows

The problem is the query analyzer always chooses the index in Table2. That option is like 7 seconds slower because there are more rows in that table.

I would like to hint the query analyzer to use the index IndSearch, searching with the date field. I have done some tests and that search is faster.

I did try using with (index(IndSearch)), however that did not work.

I did try using left join instead inner join. That changed the execution plan.  In fact, that modification improved the response time, now the query analyzer first do an index scan over IndSearch.

I think that did solve my problem, however I am still curious if someone has any comments or suggestions. I will appreciate if someone can explain how to achieve a similar result using query or table hints.

You can take a look in the execution plans:

Inner join (7-8 seconds duration):
https://www.brentozar.com/pastetheplan/?id=BJl-Lg5jE

Left Join (almost instantaneous):
https://www.brentozar.com/pastetheplan/?id=ryya8gqoN


texts: Refactoring a very large database table and having join over millions of records

texts:
I have a fairly big postgresql jobs table of (more than 60 attributes) and one critical query contains joins with this table of million records. and I cannot reduce its size(paritioning by range which is introduced recently in postgresql 10). Only 10 fields are required for this critical query(All other 50 fields when joining are a burden). is there any way that I can only join with smaller table(with 10 fields only, Because I think pg loads all the fields when applying join) when running this critical query but in all of the remaining app, I would be able to fully use this table.
E.g

jobs table fields critical for query are: cost, id, user_id, params, location
and the fields which are not required in this query are quite a lot.

Note: If I create two different tables then I had to update a lot of code everywhere


texts:
As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error.

CREATE TABLE Job
(
    JobNumber INT NOT NULL,
    JobName CHAR(30) NOT NULL,
    JobDescription CHAR(60) NOT NULL,
    Title Char(30) NOT NULL,
    PersonID INT NOT NULL,
   CONSTRAINT PK_JobNum PRIMARY KEY (JobNumber)
);

CREATE TABLE Employee
(
    PersonID INT NOT NULL,
    FirstName CHAR(30) NOT NULL,
    LastName CHAR(30) NOT NULL,
    CONSTRAINT Pk_PersonID PRIMARY KEY(PersonID),
    CONSTRAINT FK_Job_2_Employee FOREIGN KEY(PersonID) REFERENCES Job(PersonID)
);

CREATE TABLE Game
(
    GameNumber INT NOT NULL,
    Title CHAR(30) NOT NULL,
    System CHAR(30) NOT NULL,
    CONSTRAINT PK_GameNumber PRIMARY KEY(GameNumber),
    CONSTRAINT FK_Job_2_Game FOREIGN KEY(Title) REFERENCES Job(Title)
);

Error comes up when I try to initialize the second table. (And it'll probably come up on the third.)

Multiple people can have the same job, and a person can have multiple jobs.
The JobNumber and the JobName aren't the same thing. Each JobNumber only has one employee, And A single JobNumber has only one JobName.
The JobNumber and what job a person has is not the same thing.
The job number and the kind of job isn't the same thing. For example Both Job Number 1235 and 1435 could both have the job "level designer."


texts:
I have a database structure like this

ID | title | startTime | endTime

And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1 the second record has to be like DAY(NOW()) or the first upcoming record and the third has to be like DAY(NOW())+1
What I was able to do is this:

SELECT DISTINCT title, FROM_UNIXTIME(startTime) as start FROM tl_calendar_events
WHERE MONTH(FROM_UNIXTIME(startTime)) = MONTH(NOW())
AND DAY(FROM_UNIXTIME(startTime)) >= DAY(NOW())
ORDER BY DAY(FROM_UNIXTIME(startTime)) ASC LIMIT 3;

But this will return the first 3 upcoming records. The startTime and endTime are all unix timestamp fields. So the final output of this query would look something like this if I take that the date is: 06-12-2015:

Record #1 (startTime - 10-11-2015)
Record #2 (startTime - 06-12-2015)
Record #3 (startTime - 12-01-2016)

Also I need some sort of a DISTINCT select so that if it happens I have like 3 records with the on the same day I only select one. I figured I could do this with 3 joins but just ended up creating a lot of mess. I hope you understood my question and can provide some help.

SAMPLE DATA:

Event #1 (04-11-2015)
Event #2 (06-12-2015)
Event #3 (10-12-2015)
Event #4 (12-03-2016)
Event #5 (17-04-2016)

SAMPLE OUTPUT

EVENT #2 (as it is the first one before the current date)
EVENT #3 (as it is the current date or the first closest match)
EVENT #4 (as it is the first one to come after event #3)

I hope this will help explain my situation much better.


texts: Longest matching suffix while using aggregate functions

texts:
Background

Looking to find the longest matching string suffix.

Setup

Consider the following fiddle:

CREATE TABLE noun
    ("label" varchar(10))
;

INSERT INTO noun
    ("label")
VALUES
    ('bar'),
    ('blue bar'),
    ('red bar'),
    ('green bar'),
    ('purple bar'),
    ('handlebar')
;

CREATE TABLE noun_inflection
    ("label_singular" varchar(9), "label_plural" varchar(9))
;

INSERT INTO noun_inflection
    ("label_singular", "label_plural")
VALUES
    ('bar', 'bars'),
    ('handlebar', 'handlebar')
;

And the following query:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

This returns two rows:

LABEL       | LABEL_SINGULAR | LABEL_PLURAL
------------+----------------+-------------
handlebar   | bar            | bars
handlebar   | handlebar      | handlebar

The first row is correct, but not desired. For this particular purpose, the Levenshtein distance can be used to eliminate the duplicate:

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  levenshtein( n.label, ni.label_singular )
limit 1;

This re-orders the rows based on the label similarity. In this example, "handlebar" exactly matches "handlebar", and has a distance of 0. Adding the limit 1 restricts the query to a single row.

Problem

The setup works, except PostgreSQL 9.1 does not respect LIMIT modifiers in aggregate functions. That is, the following doesn't work:

SELECT
  xmlagg( xmlement( ... ) ORDER BY levenshtein( ... ) LIMIT 1 )
FROM
  noun n, noun_inflection ni

The problem remains. The word 'handlebar' matches '%bar' and '%handlebar', and so this returns two rows, which, in turn, injects two xmlelements into the resulting XML document when only one is expected.

Update #1

To clarify:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular;

That should return a single 'handlebar' XML element. Currently, it returns 'handlebars' and 'handlebar':

{ "Value": "<noun>handlebars</noun><noun>handlebar</noun>", "Type": "xml" }

The desired output is:

{ "Value": "<noun>handlebar</noun>", "Type": "xml" }

Update #2

Even though the following code solves the handlebar/handlebars problem, it prevents multiple different nouns from being returned:

select
  xmlagg(
    xmlelement(
      name "noun",
      trim( TRAILING label_singular FROM n.label ) || ni.label_plural
    )
  )
from
  noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
group by n.label, ni.label_singular
order by levenshtein( n.label, ni.label_singular )
limit 1

Update #3

This looks like it will require a stored function. Something along the lines of:

  SELECT
    trim( TRAILING label_singular FROM p_noun ) || ni.label_plural
  FROM
    noun_inflection ni
  WHERE
    p_noun ILIKE '%'||ni.label_singular
  ORDER BY
    levenshtein( p_noun, ni.label_singular )
  LIMIT 1;

Question

How would you match and return only the longest substring?


texts: DBCC checkDB stuck in killed/rollback state

texts:
I killed DBCC CheckDB Job of 3TB Database which has been rolling back since 17 hours with 89 %.as i have checked that the SPID running with waittype EXECSYNC.
Could we have any other way to clear this mess without restarting SQL Server


texts:
Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like:

2012-05-09 16:31:54.13 spid74      Error: 823, Severity: 24, State: 2.
2012-05-09 16:31:54.13 spid74      The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000e11b0000 in file 'T:\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2012-05-09 16:31:54.13 spid74      Error while allocating extent for a worktable.  Extent (1:461024) in TEMPDB may have been lost.

SQL Server re-start, which re-creates tempdb, has so far always fixed the issue.

Per a Microsoft tech recommendation, I've enabled trace flag 818 ( http://support.microsoft.com/kb/826433 ), but this flag has not yet yielded any fruit [addition ERRORLOG details]; I'm supposing what's happening must not be meeting the criteria for this flag's feature to log extra data.

Have a Microsoft case open, and sysadmins and storage vendor looking at things.  Also working through http://support.microsoft.com/kb/2015755 , including running sqliosim at one site for ~2.5 hrs.  Though there must be something going wrong in the I/O path, nothing has bubbled to the top to pinpoint the cause.

What have you experienced?
What are your thoughts about where else to look?


texts: pg_dump: Write failed: Connection reset by peer

texts:
we are trying to move all database data to other location.
we chose to perform pg_dump and we encountered few problems:

first was out of memory problem:
during pg_dump initialization we get an error:

pg_dump: saving search_path =
pg_dump: saving database definition
out of memory

then we gave to VM +16GB of RAM, so 24GB in total, the pg_dump process passed it and now we have another problem:

pg_dump: dumping contents of table lob_messages
Write failed: Connection reset by peer
gumul@IT-dep:~$

Error occurs so far only on this "lob_messages" table, this table is huge - pgAdmin shows 256GB size.
I have tried pg_dump all the database, after that error i have tried only pg_dump on that exact table. Error still occurs.

We are using Postgesql 9,4. Our sys admins dunno the problem.
Here is htop view:

Here is df -h view:

i have tried to make pg_dump file on /mnt mounted NAS storage and also locally in /var/lib/postgresql/9.4/main

here is postgresql.conf changed settings:

error remains. Can you share some wisdom what i'm doing wrong?
During that error (connection reset by peer) i got disconect from the server, but only on that session where pg_dump is run.
On other connection (monitoring htop) remains connected during that error.


texts: RAISERROR on a trigger. What will happen? Will we get informed?

texts:
What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below?

RAISERROR('Attempt to modify supposedly immutable number.',16,1)

How will we know this code was executed?


texts: Simplify a SQL table result with less rows

texts:
I have a table where there are some names with 3 sports. If the person practices the sport it shows a '1' else '0'.

How can I change the table with SQL so that it only shows a line per person, like this:

Regrads,
Elio Fernandes


texts: When changing startup parameters on a cluster, does each node have to be updated manually?

texts:
When moving a SQL 2005 instance to new disks on a two-node Windows 2003 cluster, I changed startup params on the active node to reflect the new paths for the master DB files and error logs.

I discovered after attempting a move to the alternate node that the startup params were not synchronized with the passive node and I had to reapply them there. Is that by design or should all nodes in a cluster have SQL Server Configuration Manager settings synchronized automatically?


texts: Database design for a multiple choice question website

texts:
So I want to design a database schema for MCQ website in which I have several sections ,Lets say 3. and these section will have many sub-sections each. I want to store the questions, choices and correct ans in such a way that every questions of sub-topics get stored in same table but questions of different section`s sub-section get stored in different one.

but,I don't know how to design a database schema like this. or what else could be better Approach ?


texts: Postgresql BDR - What causes replication_slots to go inactive?

texts:
I'm trying to hunt down what causes replication slots to go inactive. Might seem like a remedial question but I'm noticing that on my master database server, I have many inactive replication slots that are taking up some space on the disk.  I know I can just drop inactive slots using the pg_drop_replication_slot(slot_name name) command.

But I'd also like to be able review how we are doing our work / code to see if we are doing something wrong.

If a child server goes offline... from what I've read, it's supposed to just reconnect to the same slot when it comes back up.
So not too sure what causes a slot to go inactive.

Here's the query I'm running to see what's what:

widgets=# SELECT slot_name, database, active, pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes
widgets-# FROM pg_replication_slots
widgets-# WHERE plugin = 'bdr';
        slot_name         | database | active | retained_bytes
--------------------------+--=-------+--------+------------------------
 bdr_16385_12345_1_1111__ | widgets  | f      | 2431628304
 bdr_16385_11111_1_1638__ | widgets  | f      | 2432065824
 bdr_16385_22222_1_1638__ | widgets  | t      | 56
 bdr_16385_33333_1_2222__ | widgets  | t      | 56
 bdr_16385_44441_1_1111__ | widgets  | f      | 2432185336
 bdr_16385_55551_1_1111__ | widgets  | f      | 1133538200
 bdr_16385_66661_1_1111__ | widgets  | f      | 2431950952
 bdr_16385_77771_1_1111__ | widgets  | f      | 2432057320
 bdr_16385_88881_1_1111__ | widgets  | f      | 2331233432
(9 rows)

widgets=#

Any comments would be appreciated.


texts: Fastest way to split/store a long string for charindex function

texts:
I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function).
I have tested this with a 1GB string and a 9-digit-substring using SQL Server, storing the string as a varchar(max). Charindex takes 10 secs. Breaking up the 1GB string in 900 byte overlapping chunks and creating a table (StartPositionOfChunk, Chunkofstring) with chunkofstring in binary collation, indexed takes under 1 sec. Latter method for 10GB,10 digit-substring rises charindex to 1,5 min. I would like to find a faster storage method.
Example
string of digits: 0123456789 - substring to search 345
charindex('345','0123456789') gives 4
Method 1: I can now store this in a SQL Server table strtable consisting of one column colstr and perform:
select charindex('345',colstr) from strtable

Method 2: or I can make up a table strtable2 (pos,colstr1) by splitting up the original string: 1;012 | 2;123 | 3;234 a.s.o and then we can have the query
select pos from strtable2 where colstr1='345'

Method 3: I can make up a table strtable2 (pos2,colstr2) by splitting up the original string into larger chunks 1;01234 | 4;34567 | 7;6789 and then
select pos2+charindex('345',colstr2) from strtable2 where colstr2 like '%345%'

First method is the slowest.
Second method blows up the database storage size!
Method 3: Setting colstr2 length to 900 bytes in binary collation, creating an index on this column takes 1 sec for 1GB string and 9 digit substring search. For 10GB string and 10 digit substring ist takes 90 secs.
Any other idea how to make this faster (maybe by utilizing the string consists of Digits, with Long integers,....)?
Search is always for a 12 digit substring in a 1TB string of digits  SQL Server 2017 Developer Edition, 16 cores, 16GB RAM. Primary goal is search speed! 10 digits in a 10GB string (for performance testing).


texts:
I have captured a trace in profiler and stored the trace to both a table and an xml for replay. I open the xml and replay the trace on the same server/database with modified stored procedures (I want to check the performance of some changes). In the replay options I choose "save to table" to store the replay results for later analysis. The replay went well with a few errors about 'bulk inserts' which I accept since the profiler documentation states that it does not replay bulk events.

My problem is that the replay trace table does not have the same columns as my original trace! specifically the duration and cpu columns. I really need these in order to compare the two sets of results. Is there anything I am missing in the replay setup or anywhere?

Thanks
dat_girl


texts: Why is an entire index that is infrequently used stored in the buffer pool?

texts:
I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire index. A few more notes on this specific index:

It is not a compound index. Only one field is included
That field has relatively low cardinality (55 distinct values)
The index exist on a table with half a billion records
The sys.dm_db_index_usage_stats DMV shows 51 combined user_scans and user_seeks
The sys.dm_db_index_usage_stats DMV also shows 1,159,987 user_updates

Leading to a few questions:

Why would this infrequently used index be taking up so much space in the buffer pool?
Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?
Should a field with such low cardinality be indexed in the first place?
Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box

I am a developer trying to be a DBA, so bear with me ;)


texts:
I want to find all the queries executed by an particular user in a database over a period of time in MYSQL. Even they executed query by using MYSQL workbench i want to see log in my Database cloud server. Is there any possible there in MYSQL.

Thanks in advance.


texts:
Does SQL Server have any simple way to fetch metrics on how many (and possibly what type) of locks a given query has taken out during the course of it's execution?

Rationale...
I'm in the process of rewriting of a rather nasty stored procedure which makes heavy use of cursors. A set-based solution is possible, and as a result I've got three versions on it: the original, a set-based version with table vars and a set-based version that's purely CTE based. I'm getting good metrics out for the CTE based solution, but because of the size and complexity of the query created by the CTE version I'm wary of releasing it until I've done as much due diligence as I can on it.

Now I realise that the locks SQL take out are those that it thinks it needs to take out at a given point in time, and on a loaded production server it may behave differently, but I'm hoping it would give me a feeling for how it's going to behave.

Currently I'm using profiler and holding transactions open so I can run sp_lock, but was wondering whether I'm missing a trick in SQL Server 2008.

Alternatively, I'm trying to persuade myself that profiler returning a high number of reads when inserting into a table variable is okay (for a given value of okay), but I've not convinced myself. The CTE solution has half the reads of the table-var version, and the high read-count of the table-var version seems purely to be down to the inserts. In terms of duration, both the CTE and table var one are about the same, giving execution times roughly 150-200% faster than the cursor based approach.


texts: When a transaction truncate a global temporary table, is that logged in transaction log or error log?

texts:
When a transaction truncate a global temporary table, is that logged in transaction log or error log ? I have inherited bunch of old SQL 2012 servers from my senior dbas who left the organisation. I'm stuck with queries from report users which processes are truncating certain business critical tables.
If it is logged in transaction log, then could the SID,login details be retrieved? Suspect, there are multiple agent job using those ##table.


texts: Is it worth it to run VACUUM on a table that only receives INSERTs?

texts:
In a 2015 re:Invent talk, AWS mentioned that vacuum should be run not only after updates or deletes but also after inserts. Here is the relevant part of the talk:

http://www.youtube.com/watch?v=tZXp19q8RFo&t=16m2s

Supposedly there is some cleanup that must be done on blocks even if they only received inserts and this cleanup can be done either the first time a block is selected (slowing down reads) or during vacuum. Is this true and if so, exactly what cleanup must be done?


texts: Can a SQL Server Subscription Database be unsubscribed and used as standalone?

texts:
We've got a new server we want to migrate to and I have setup a Publication on the current production server as a Transactional publication. Since the subscribed database is up to date, I want to discontinue the subscription, rename the database, then point our database connections to it.
The new server is SQL Server 2012 where the old server is 2008.

Thanks for any pointers.


texts: How do You concatenate a value to an array that was retrieved through a query in sql?

texts:
I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:

  word   |     mkvword
---------+-----------------
 hello   | {world,friend}
 goodbye | {home,forever}
 hi      | {fellas,ladies}

According to the documentation provided for PostgreSQL, appending a value to an array of values should be a simple matter of using the concatenation operator: "||".

However, if, I attempt to add the string there, to the array of strings {fellas,ladies} which correspond to the word hi, nothing appears to happen:

SELECT mkvword FROM mkvtable WHERE word = 'hi' || 'there';

PostgresSQL appears to be saying as much:

 mkvword
---------
(0 rows)

Since there are no errors thrown, I assume things are syntactically okay.

What am I doing wrong? My best guess is that psql is getting upset that I'm trying to access the array by using it's relationship to a value from the adjacent column, but that's just an uneducated guess. Perhaps this sort of query doesn't jell well with the concatenation operator?

TL;DR: How do you properly concatenate values to arrays stored in table rows?


texts: Migrate a SQL Server instance from one server to another

texts: I have a database for my website running on AWS.  My web admin has given me access to the database, and I'd like to host and manage it on my own.  How do I migrate the SQL database from AWS to my own SQL server?


texts: Why can members of db_datareader SELECT sys views

texts:
In SQL Azure my understanding of db_datareader is that it is allowed to SELECT all user tables.

I couldn't find a definition of exactly what a user table is, but I assumed it was all of the non-builtin tables and views, and excluded all the views and tables that are in the sys schema.

Why then can members run the following statements?

SELECT TOP (5) * FROM [sys].tables

SELECT TOP (5) * FROM [sys].[all_views];

SELECT * FROM sys.dm_exec_sessions;

SELECT TOP (5) * FROM [sys].objects

... and more


texts:
So just trying to understand what the industry standard is around DBA's being able to see card number.

As per PCI document

  7.1 Limit access to system components and cardholder data to only those individuals whose job requires such access.
  7.2 Establish an access control system(s) for systems components that restricts access based on a user’s need to know, and is set to “deny
  all” unless specifically allowed.

So can DBA's with syadmin access ok in seeing card numbers.  If not how is it done in real world?


texts: Memory Optimized Tables - can they really be so difficult to maintain?

texts:
I'm investigating the benefits of upgrading from MS SQL 2012 to 2014.  One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast.

I've found that there are a few limitations on memory optimized tables, such as:

No (max) sized fields
Maximum ~1KB per row
No timestamp fields
No computed columns
No UNIQUE constraints

These all qualify as nuisances, but if I really want to work around them in order to gain the performance benefits, I can make a plan.

The real kicker is the fact that you can't run an ALTER TABLE statement, and you have to go through this rigmarole every time you so much as add a field to the INCLUDE list of an index.  Moreover, it appears that you have to shut users out of the system in order to make any schema changes to MO tables on the live DB.

I find this totally outrageous, to the extent that I actually cannot believe that Microsoft could have invested so much development capital into this feature, and left it so impractical to maintain.  This leads me to the conclusion that I must have gotten the wrong end of the stick; I must have misunderstood something about memory-optimized tables that has led me to believe that it is far more difficult to maintain them than it actually is.

So, what have I misunderstood?  Have you used MO tables?  Is there some kind of secret switch or process that makes them practical to use and maintain?


texts: Can we restrict Maximum Job history rows for particular job/jobs in SQL Server 2012?

texts:
I am aware that there are 2 universal settings for Job history retention rows in SQL Agent. First one is default 1000 rows and per job max history row count is 100.  Second one is deleting history older than given weeks.

Apart from these 2 options, do we have any possibility left to set up our desired max row count for a single job/few jobs? Like individual max row count for each job/specific jobs.

For example, I have a job which runs for every hour whose run time changes according to other concurrent jobs resource blocking.I want to see at which hour schedule, the Job took long time. As I have multiple jobs running on server, only 10 job history entries are available for this job. Also, I don't require more history entries for few other jobs. So, Could anybody please let me know if there is any way to setup individual max history row count for specific jobs?


texts: How Do I Troubleshoot Login Failed Errors In SQL Server?

texts:
What are some tips for troubleshooting SQL Server login errors? Questions like:

How Do I see the full Error Message? My client just has a generic error message that says logon failed with no real information/state information.
What if there is no error in the error log, but I still can't connect?
How Do I read the logon failure (Error 18456) error messages?
How Do I troubleshoot the various conditions found in the error messages?
What is Kerberos, why do I care and how do I fix errors related to it?


texts: How do I stop/disable PITR and safely clean WAL segments?

texts:
Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final).

We've setup PITR in our production server.  For some reasons, after setting up PITR, we're not able to manage and maintain it.  Eventually, our WAL archive drive (secondary drive) has become full (100% use) and additional WAL archive segments that are to be archived got accumulated in pg_xlog/ directory itself (which is available in primary drive)

PITR SETUP DETAILS

There are 2 drives:

Primary drive (pgsql/data/ directory resides) is 400 GB.
Secondary drive (WAL archives) is 300 GB.  All WAL archives are written to this secondary drive.

Now, we've decided to stop/disable PITR.

My question is, in this situation, how do I stop/disable PITR and safely clean up all WAL segments from both the drives?

What are the recommended/suggested ways? Experts advice/idea/suggestion on this are highly appreciated.


texts: PostgreSQL transaction locked database table: "idle in transaction"

texts:
I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not new, has been running several time and has been tested beforehand.

I am trying to see what transaction might have cause it, or if a combination of it with an autovacuum process could have caused it. But in the meantime I would like to unlock the database tables. I tried restarting the postgreSQL service, terminating thus the processes in "idle in transaction" state which were locking the tables but it didn't work: next time my application performed the same call the tables went locked again.

Any ideas on what can I do to unlock the database gracefully?


texts: MySQL query optimization for legacy application

texts:
I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone.

The fact that they have actual data now years later is why we're noticing the performance hit at this time. Just not sure what to do with it yet.

I've tried to make sure indexes are correct and what not in the schema, but here's a sample query being generated by the application. Any ideas to help with performance?

SELECT id,
       name,
       phone
FROM   customers
WHERE  ( last_updated >= '2013-03-01'
         AND last_updated <= '2013-03-27 24:00:00'
         AND status = '0'
         AND deadlead = ''
         AND purchased = ''
         AND id != 0 )
        OR ( status = '0'
             AND deadlead = ''
             AND purchased = ''
             AND id IN (SELECT DISTINCT cid
                        FROM   notes
                        WHERE  timestamp >= '2013-03-01'
                               AND timestamp <= '2013-03-27 24:00:00')
             AND id != 0 )

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `alt_phone` varchar(255) NOT NULL,
  `tradein` text NOT NULL,
  `purchased` text NOT NULL,
  `deadlead` text NOT NULL,
  `interest` varchar(255) NOT NULL,
  `trailer` varchar(255) NOT NULL,
  `status` binary(1) NOT NULL,
  `options` text NOT NULL,
  `created_on` date NOT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id_2` (`id`),
  KEY `id` (`id`),
  KEY `id_3` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8913 ;

CREATE TABLE IF NOT EXISTS `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `note` text NOT NULL,
  `file` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reminder` date NOT NULL,
  `locked` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19308 ;

UPDATED:

This is the output of using EXPLAIN on the query:


texts: Which kind of table design best suitable for normalization in SQL server?

texts:
I have three different kinds of products i.e., vehicle, gold and property in our application. Each product has almost 30 different kind of its own inventory details. I have designed my table structure as below:-

Header Table:-

 PkId ------ + ProductFkId------ + AssetId ------ + RegNo
  1          | 1                 | A00001         | R0001
  2          | 2                 | A00002         | R0001
  3          | 3                 | A00003         | R0001

Vehicle Inventory Table:-

 PkId ---------------+ HeaderFkId -------- + Make ------- + Model
  1                  | 1                   | Toyota       | Etios Cross Dieselvdsp
  2                  | 2                   | Toyoto       | Etios Dieselgd
  3                  | 3                   | Toyota       | Etios Dieseld-4d Gd

Gold Inventory Table:-

  PkId ------- + HeaderFkId ----- + Carat18 ----- + Carat19 ----- + Carat20 ----- + Carat21 ----- + Carat22 ------ + Carat23 ----- + Carat24
   1           | 1                | 10            | 0             | 100           | 45            | 35             | 4             | 7
   2           | 2                | 1             | 100           | 0             | 50            | 40             | 6             | 0
   3           | 3                | 30            | 40            | 10            | 10            | 56             | 0             | 10

Property Inventory Table:-

  PkId ----- + HeaderFkId ----- + MarketPrice ----- + ReservePrice ----- + PropertyDescription
   1         | 1                | 300000            | 200000             | Flat No. 202 & 202-A
   2         | 2                | 500000            | 400000             | Flat No. 201 & 201-A
   3         | 3                | 200000            | 100000             | Flat No. 200 & 200-A

All together in Single Table:-

PkId --- + ProductFkId--- + AssetId --- + RegNo --- + Make --- + Model --- + Carat18 --- + Carat19 --- + Carat20 --- + MarketPrice --- + ReservePrice --- + PropertyDescription
 1       | 1              | A00001      | R0001     | Toyota   | Etios     | Null        |  Null       | Null        | Null            | Null             | Null
 2       | 2              | A00002      | R0002     | Null     | Null      | 5           |  84         | 56          | Null            | Null             | Null
 3       | 3              | A00003      | R0003     | Null     | Null      | Null        |  Null       | Null        | 46000           | 500000           | Flat

Instead of storing product data in separate tables, I can also store in one table. Tomorrow any new product is coming at that time I need to create new table or adding additional 10 or 20 columns based on product in one table. If I save all product details in one table at that time except particular product other product columns will store null values. Which table design structure is best suitable for normalization?


texts: Grant permissions on a stored procedure to tables in another schema

texts:
I created a user in SQL Server 2012 database and revoked all permissions given by the public role.

Then I granted EXECUTE permission on a stored procedure.
The user can execute the procedure but cannot get the data it returns.

The procedure is in schema1, and the tables from which it selects are in schema2.

If I add the user to the db_datareader role it can read all data from all the tables in the database. I tried using WITH EXECUTE AS OWNER but it didn't work.

How can I grant only the access to the given procedure and nothing else?


texts: Is it possible to add a DB to an Always On AG and setup replicas at different time?

texts:
I have a 3-node SQL Server Always On availability group. 2 nodes are on the same LAN, and the third one is on a remote site with high latency.

I will add a huge DB (10TB) to the AG, so I would like to be able to just get it added to 2 replicas on the LAN in a first stage, and some days later, to get it added on the 3rd (remote) replica after manually copying it on-site.

Is this a supported scenario?
If so...What would be the recommended approach?

Adding the database to all nodes at the same time is not an option unfortunately.


texts: UPDATE only if COUNT from another table is not zero

texts:
I update a table by counting the FK from another table as

UPDATE table1
SET number = number +
(
SELECT COUNT(*) FROM table2
WHERE  table1.id=table2.id
)

table1 is a big table, and I want to make the UPDATE only if COUNT has found something. In other words, I do not want to make the UPDATE for number=0.


texts: How to Export MS SQL DB from Docker-Linux to Access format

texts:
I have a MS SQL database which is currently up and running on my Mac within a Docker container that is hosting the Ubuntu Linux version of MS SQL Server.  I need to export the database to a format that can be imported into an Access (yes, Access) database.

Alternatively, I would need to export all 300+ tables to CSV, and hopefully would not need to do them one by one.  Is there any way to export all tables en masse to CSV?  But this is an alternative option.  I still prefer the Access-compatible export since our new SIS vendor apparently uses it. Thanks!


texts:
We had a SQL server die due to some RAID problems.  We sent the machine out to a data recovery company and they are able to recover the .MDF and .LDF files from the drives.  Will I be able to use those files to restore the databases to a new machine?
What do I need to watch out for? Thanks!


texts: Having trouble with a T-SQL scalar variable during a create table

texts:
I am still a beginner, but I am having trouble with a T-SQL scalar variable during a create table.   Can anyone tell me what is wrong with this script?

-- T-SQL MSSQL
SET NOCOUNT ON
DECLARE @tt = 'TempNWA'
--Create Temporary Table
CREATE TABLE [@tt]
 (  [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50)
 )
DROP TABLE [@tt]

The error I get, which I can't decipher is:

Incorrect syntax near '='


texts: Database staging environment for non-IT personnel

texts:
I'm in the process of proposing a database staging environment to my IT department. The idea is that a non-IT person like me (public works data analyst) would have a place to test solutions, and then either implement them in the live environment myself, or ask IT to implement them if needed. There are a few reasons/scenarios where this environment would be beneficial:

I have some basic database privileges in our live database environment (create table, create view, etc.). I make schema changes about once a week, but it seems insane for me to test and implement these changes in a live environment. There are countless dependencies on the database, so if something goes wrong, it could be disastrous. I'd much rather test things out ahead of time in a separate environment.
I don't have some of the more advanced privileges like create trigger or create function in the live database. This is fine, but I do have a few problems that could be solved by triggers and/or functions. I plan on proposing that I be granted these permissions in the staging environment so that I can develop and test some ideas, and if they work, propose that IT implement them in the live environment.
In general, my IT department doesn't have the time or resources to develop solutions for me. It's really that simple. So if I can do the legwork myself, then my problems are much more likely to be solved.

The 'staging environment for non-IT personnel' seems like a sound enough approach to me, but to be honest, I just made the idea up. I have no idea how this is typically done in the IT/database world.

Is there any sort of established IT/Database practice that would fit this scenario? (Am I on the right track when proposing a database staging environment for non-IT personnel?)


texts: How to execute multiple concatenated queries?

texts:
I wanted to grant privileges on some selected tables only. So far I could only gather the necessary queries but unable to execute those.

SELECT
   CONCAT('GRANT SELECT ON drupal.', TABLE_NAME, ' to ''test'';') As queryStrings
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'drupal' AND TABLE_NAME LIKE 'cache%';

OUTPUT:

+----------------------------------------------------------------+
| queryStrings                                                   |
+----------------------------------------------------------------+
| GRANT SELECT ON drupal.cache_page to 'test';                   |
| GRANT SELECT ON drupal.cachetags to 'test';                    |
| GRANT SELECT ON drupal.cache_config to 'test';                 |
| GRANT SELECT ON drupal.cache_data to 'test';                   |
| GRANT SELECT ON drupal.cache_entity to 'test';                 |
| GRANT SELECT ON drupal.cache_discovery to 'test';              |
| GRANT SELECT ON drupal.cache_default to 'test';                |
| GRANT SELECT ON drupal.cache_render to 'test';                 |
| GRANT SELECT ON drupal.cache_container to 'test';              |
| GRANT SELECT ON drupal.cache_toolbar to 'test';                |
| GRANT SELECT ON drupal.cache_bootstrap to 'test';              |
| GRANT SELECT ON drupal.cache_menu to 'test';                   |
| GRANT SELECT ON drupal.cache_dynamic_page_cache to 'test';     |
+----------------------------------------------------------------+

Is there something like EXECUTE queryStrings; ?

I tried to store it in a variable like

SET @query = CONCAT('GRANT SELECT ON drupal.', TABLE_NAME, ' to ''test'';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'drupal' AND TABLE_NAME LIKE 'cache%');

But I am getting error:

  You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'drupal' AND TABLE_NAME LIKE' at line 1


texts:
I have a client appconsole. I want to execute a SQL command "copy from stdin" and send data from client STDIN to Postgres server to fill a table. Is it possible without psqlclient and just via sqlcommand copy?

Every time, in other posts when someone asks about it, folks always tell us to use psqlclient, but I don't want to use it.

I've searched already in psqlclient source code at git. There, they are using methods like "PQputCopyData(conn, buf, linelen)" from libpq.dll to process stream data.

So that's why I'm asking if it is possible to pass data using just SQL commands and client data pipe. If it's not possible, what is the reason for this command "FROM STDIN"?


texts: SQL Comments disappear with psql and `\e`

texts:
Is there anyway at all, ever to change the behavior of comments in psql. Take the query below. Execute it.

CREATE TABLE foo
AS
  SELECT x AS id,
    -- x AS id2,
    x AS id3
  FROM generate_series(1,50) AS x;

Run that in psql. Then run \e. Now at least, for me what I see in my editor is the line absent. This is driving me crazy. Is there a way around this.. The comment is just absent from the buffer that gets passed to the editor. Often, it's commented and not deleted because I want to uncomment it at a later point.


texts:
I am trying to change the following properties for SQL users to disable password complexity and expiration.

Using:

ALTER LOGIN xx WITH
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

Where 'xx' is an exact username works perfectly.

However, I would like to update an entire group of users that match a string such as:

SELECT FROM sys.sql_logins where is_policy_checked = '1' and name like '%cpsuser%'

So I attempted:

ALTER LOGIN FROM sys.sql_logins where is_policy_checked = '1' and name like '%cpsuser%' WITH
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO

But get an error that SQL doesn't recognize 'FROM'.

Is my Logic incorrect?
This is MySQL 2008.


texts: Best way to import and/or upgrade Oracle database

texts:
I have an Oracle database, version 11.2.0.3.6. I need to export that database and import it in a 11.2.0.3.15 home on another server. What's preferred?

1) Just export/import the old database on a newly created instance running 11.2.0.3.15?

2) Upgrade the old database to 11.2.0.3.15, and then do the export/import to the new server?

3) Any other suggestion?

Thanks in advance.


texts: Record scan speed, column orientated databases and SSDs

texts:
I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated databases. About half way through he talks about how long it takes to do a table scan for a query, and discusses the actual physical disk head reading from a spinning magnetic disk. He says that even though we may only want to query, say, 3 columns from a table with 100 columns, the disk head still needs to physically pass over the entire records in a row-orientated database, hence one reason why a column orientated storage system may be faster in some scenarios for querying.

I have a couple of questions about this:

How true is this, given that data in records may not actually be physically next to each other on disk (because of B+Tree index structures)? Is this an oversimplification, or is it true in some situations and not in others (e.g. in tables with no indexes)?
How does this relate to SSDs - as there is no spinning disk and head scanning over it, does the same principle hold true when data is stored in an SSD?

Thanks


texts: How to create a new column in SELECT CASE when string contains Arabic words?

texts:
I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with ????? because I have set the column to Arabic words, I have tried to convert the content of the new column to nvarchar(55) unfortunately, I got the same result.
How can I get the correct result?
SELECT case when ( CAST(o.startTime as time(7)) > cast(Start as time(7)))
          then  cast('قدوم' as nvarchar(55))
          else cast('رجوع' as nvarchar(55)) end as stat,
       userId, FirstName
from   Users, TimeTable


texts: Why don't relational databases have read-only indexes on columns?

texts: In many programming language implementations, the compiler will turn on certain optimizations if it knows that a variable will not change after its initial assignment. Wouldn't this stretch to persistent data too? If the database knows that a certain column will not be updated after a record is created, couldn't it change the way the data is stored, or cached, to improve performance?


texts: Single Virtual Drive Vs Multiple Virtual Drives for SQL Server files

texts:
Hi guys I have question about how two different Hard disk setups can possibly affect performance of my database.

I have two Options.

Option 1:

One Physical Harddisk and then Multiple Virtual hard drives for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

Option 2:

One Physical Harddisk and then One huge Virtual hard drive for SQL Server's Database files (.mdf , .ndf),Log files, TempDB, Backups etc

To my knowledge, If these were all physical hard drives, having multiple drives for all these files (tempDB, Log file, backups, database files), would definitely benefit from  parallel processing.

But is there any drawbacks of having one massive Virtual Hard drive vs multiple Virtual Hard drives as in reality, when Sql Server is reading or writing , it will be all done from one Physical Hard disk?

Is one method preferred over other? Any suggestions or pointers in the right direction is much appreciated. Thanks


texts: Does this set of locks imply table lock and if so is it avoidable?

texts:
I have a query on SQL Server 2008 that when using sp_whoisactive with get_locks = 1 has the following locks open....

<Object name="my_table" schema_name="dbo">
  <Locks>
    <Lock resource_type="KEY" index_name="pk_mypk" request_mode="X" request_status="GRANT" request_count="1" />
    <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
    <Lock resource_type="PAGE" page_type="*" index_name="pk_mypk" request_mode="IX" request_status="GRANT" request_count="1" />
  </Locks>
</Object>

I understand the Key and Page lock but am unsure what the Object one is. Is that a table lock? If so why did it bother taking key and page locks as well? Are there ways to work out why a particular query that is only touching a small set of rows would use a table lock, I'm guessing it's lock escalation.


texts:
I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings.

Will postgreSQL always take this amount of memory or only when needed?

I'm running my database on an Amazon EC2 instance and I will monitor the memory with a CloudWatch custom metric and I'm now thinking about it if the memory I set in the configuration will always be reserved i.e. appears to be used in the cloudWatch metric.

If this is the case, how can I monitor the actual used memory of postgreSQL?


texts: How to get newly inserted row ids when inserting with execute?

texts:
I have a function which returns newly inserted row count:

   BEGIN
   EXECUTE (

      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column

      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
  END;

Instead, I have to return a list which contains newly inserted row ids. How can I do that?

I tried with result_oid,@@identity but I got no result.
This function is returning only one value. How can I get all the values?

CREATE OR REPLACE FUNCTION public.test2(_tbl regclass,_id integer)
RETURNS integer[]
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL UNSAFE
COST 100
AS $BODY$DECLARE
ab integer;

BEGIN
  EXECUTE (

  SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE budget_id = $1 RETURNING id',
                _tbl, string_agg(quote_ident(attname), ', '))
  FROM   pg_attribute
  WHERE  attrelid = _tbl
  AND    NOT attisdropped  -- no dropped (dead) columns
  AND    attnum > 0        -- no system columns
  AND    attname <> 'id'   -- exclude id column
  )
 USING _id
 into ab;

 RETURN ARRAY[ab];
END;$BODY$;


texts: SQL SERVER FileTable/FILESTREAM share suddenly becomes unavailable

texts:
We had difficulty setting up a FileTable share on a SQL 2014 instance and after giving up on trying to name it after our instance name, we left the name of the share as mssqlserver, but it finally came online, online meaning if you right click in SSMS on the FileTable table you can "Explore FileTable Directory", however it seems to randomly go offline again after some time and you receive the usual canned error message:

So, after magically disabling/enabling FILESTREAM at the server level, database level via t-sql/ssms/configuration manager it some how comes back, but I have to mess with the setting at all of those spots for it to come back! and there seems to be no magical sequence of steps that makes it come back.

Has anyone out there experienced this? Will a restart of the service/server help this? If not, will a repair of sql server help?


texts: Execute xp_cmdshell as a non-windows user

texts:
So I have a bunch of database users who should be able to execute a procedure with a call to xp_cmdshell. They are not windows domain accounts, they are just sql-server logins. I want to do it using a proxy ##xp_cmdshell_proxy_account##. I am aware of security threats that it may bring. I cannot use 'WITH EXECUTE AS admin' due to our company inner version control and I obviously do not want to grant all of the users rights to execute xp_cmdshell, that is why my only choice (am I right here?) is using the proxy account.

I have a special windows-domain account for it, to whom I granted xp_cmdshell execution rights. I can successfully execute cmdshell using this account. I created the proxy account using the following command:

EXEC sp_xp_cmdshell_proxy_account  [my_domain\special_account],'SuperSecretPassword'

When I try to execute any xp_cmdshell command as a database user with no xp_cmdshell rights an error appears:

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

My question is - can I execute a xp_cmdshell command using an sql server login and I am simply missing something here or is it only possible using a domain account? Is there any other procedure to follow here to make it happen?

If I fail to make the above work I will have to switch some logic to a CLR procedure, but I am determined to make it work.

I am using sql-server 2012. Any help will be appreciated, thanks!


texts: When is it safe to disable InnoDB doublewrite buffering?

texts:
MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked.

How could InnoDB still be able to maintain data integrity and ACID if we disable doublewrite buffering?

In what situations will it be safe to turn off InnoDB doublewrite buffer?


texts: Blocked sessions waiting with PAGELATCH_* wait types?

texts:
Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types?

I previously assumed that SQL server would only report blocking sessions in the blocking_session_Id column. If the blocked sessions were waiting for a logical lock and not anything else such as a PAGELATCH_*.


texts: Where to put login.sql in Windows environment to configure SQL Plus?

texts:
I installed Oracle 10g XE on my Windows Server 2008 R2. Now I would like to configure SQL Plus to use login.sql for login settings. I created a login.sql file and put it into a

  C:\users\myname

folder, but it does not seem to work. Where should I put this configuration file in Windows environment? My login.sql file is as follows:

SET pagesize 2000
SET LONG 10000
SET linesize 1000
COLUMN last_name format a20
COLUMN total format 999,999,999
SET feedback ON
alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ssPM'
COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name||CHR(10)||'SQL> ' AS global_name
FROM   global_name;
SET SQLPROMPT '&gname'
SET TERMOUT ON;


texts: oracle 12c rac switch from mounted to open

texts:
How to open an RAC database correctly(version 12.1) from "mounted" state?

Earlier, nodes #01 and #02 were opened manually by the command alter database open.

Now, the database in this state:

SQL> select instance_name,status from gv$instance;
INSTANCE_NAME    STATUS
node01          OPEN
node02          OPEN
node04          MOUNTED
node03          MOUNTED

~$ srvctl status database -d cldb -v
Instance node01 is running on node srv-n01. Instance status: Open,Readonly.
Instance node02 is running on node srv-n02. Instance status: Open,Readonly.
Instance node03 is running on node srv-n03. Instance status: Mounted (Closed).
Instance node04 is running on node srv-n04. Instance status: Mounted (Closed).


texts: can I convert an integer to base-2 without a function

texts:
Here's a bit of a challenge.  I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek.  I may not be using the proper terminology here, but this is an integer from which one can derive the days of the week an SSRS subscription is set up to run.  Each day is assigned a number as follows:

Sunday: 1
Monday: 2
Tuesday: 4
Wednesday: 8
Thursday: 16
Friday: 32
Saturday: 64

The sum of the days the subscription is set to run is the number in this column.  Thus subscriptions that run Monday through Friday have a value in this column of 62.

The end result I want is a view that derives T/F flags for each day based on this number so that I have a column for each day.  The method I am currently exploring to get me there is converting this integer to base-2 so then I can convert that to a varchar and parse out the days.  In this example, the result would be 111110.

The final twist - I do not have the ability to create functions or stored procedures in this database, so my strong preference is to solve this within a SELECT statement...

(If push comes to shove, I will move the raw data and use a function in a separate database - and have found a number of those online.)


texts: sp_Blitzcache doesn't return as many results as sys.dm_exec_query_stats for a given query

texts:
I am trying to find plans for a particular set of queries (those that update MyTable) using sp_blitzcache:

EXEC sp_Blitzcache @SlowlySearchPlansFor = 'UPDATE [My Table]'

this returns 3 results, however, when I run the following query

SELECT  d.name,
        t.text AS TSQL_Text,
        s.creation_time,
        s.execution_count,
        s.total_worker_time AS total_cpu_time,
        s.total_elapsed_time,
        s.total_logical_reads,
        s.total_physical_reads,
        p.query_plan
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        JOIN sys.databases d
            ON t.dbid = d.database_id
WHERE   t.text LIKE '%UPDATE \[My Table\]%' ESCAPE '\'

it returns far more.

Is there some sort of limit in sp_blitzcache around what queries are found? For example, are low cost queries ignored?


texts: SQL 2016 SQL Server Assertion: File: , line=951 Failed Assertion

texts:
I'm currently upgrading our Data warehouse from SQL 2012 to SQL 2016. I have both my old and new DW's running side by side in parallel.

My ETL process (A framework developed in SSIS by a 3rd party) has run successfully for more than 2 years on 2012 but is failing on 2016. So far the databases and ETL process are identical.

Both Servers are Virtual machines running on VMWare.
Old Server is Win 2008 with 24Gb of RAM. SQL 2012 Std. Max mem set to 16Gb.
New Server is Win 2012 with 64Gb of RAM. SQL 2016 dev. Max mem set to 50Gb.
New DW is running v13.0.1601.5 RTM Developer Edition (64-bit).

While running my ETL process the load steps that use a SQL Merge into either a dimension or fact table fail with the following error.

Full text:

  DESCRIPTION:  SQL Server Assertion: File: , line=951
  Failed Assertion = 'IS_OFF (BUF_MINLOGGED, m_buf->bstat) ||
  pageModifyType != PageModifyType_Contents || GetPagePtr ()->IsTextPage
  ()'.  This error may be timing-related. If the error persists after
  rerunning the statement, use DBCC CHECKDB to check the database for
  structural integrity, or restart the server to ensure in-memory data
  structures are not corrupted.

As recommended I have run DBCC and no errors were found. I have also restarted SQL. I then restarted the ETL process and got the same error.

My searches for this error show that is was known error in SQL 2008, 2012 & 2014 and fixed in subsequent hotfixes & cumulative updates. so I'm a bit surprised to see it reappear in 2016.

The links I've found say it affects SSIS when trying to do inserts if database is in Simple or Bulk Logged recovery model. (I'm running in Simple recovery model)

A suggested workaround is to change Db recovery model to FULL. I have tried this and it works, but it's not much of a solution for a Data Warehouse.

Has anybody else encountered this with 2016?

Can anyone suggest alternative workarounds?

Updates:

26/7/2016: I applied the Critical Update KB3164398 (v13.0.1708.0) and the problem still exists.

27/7/2016: I have applied Cumulative Update CU1 KB3164674 (v13.0.2149.0).

3/8/2016: Error occurred overnight on our smallest cube. CU1 did not fix the issue. Today I reported the bug on MS Connect And I have also logged a support call with Microsoft.

12/8/2016: MS-Support responded initially, but the responses was "We don't have a fix for that". The Support guy was going to discuss it with his colleagues and get back to me. 8 days later I haven't heard from him.

Although I don't have a 'fix' we did find a workaround that suited us.  See my posted answer.

29/9/2016. I applied CU2 last week. On Thursay we accidentally ran an old version of the merge which failed again with the same error. So..  CU2 hasnt fixed it either.

23/1/2017: I Applied 2016 SP1 CU1 and I believe this has resolved the issue.  Specifically KB3205964


texts:
I have a table that stores REST requests and responses:

CREATE TABLE ws_call_logs
(
   id                 BIGINT         NOT NULL,
   ws_method_id       BIGINT         NOT NULL,
   device_id          BIGINT,
   session_id         BIGINT,
   corr_id            BIGINT,
   request            MEDIUMBLOB,
   response           MEDIUMBLOB,
   http_status_code   INT UNSIGNED   NOT NULL,
   processing_status  CHAR(1)        NOT NULL,
   created_timestamp  DATETIME       NOT NULL
)

I created 4 indexes representing the 4 queries I usually execute:

CREATE INDEX ix_corr_id_timstamp ON ws_call_logs (corr_id, created_timestamp);
CREATE INDEX ix_corr_method_timstamp ON ws_call_logs(corr_id, ws_method_id, created_timestamp);
CREATE INDEX ix_corr_id_status_timstamp ON ws_call_logs (corr_id, http_status_code, created_timestamp);
CREATE INDEX ix_corr_method_status_timstamp ON ws_call_logs (corr_id, ws_method_id, http_status_code, created_timestamp);

Here is the query:

SELECT *
FROM
   ws_call_logs
WHERE
   (corr_id is not null
      AND http_status_code > 200
      AND created_timestamp >= '2015-11-01 23:00:00'
      AND created_timestamp <= '2015-11-30 23:00:00'
   )
ORDER BY
   created_timestamp
DESC LIMIT 25;

I am expecting that it should be fast because it should use the 3rd index that I have created. But it seems it is not and it takes around 1 minute before it returns.

Can anybody spot what is the problem? Or any tips how to debug?

And any other tips to optimise the queries will be appreciated.


texts:
I'm using a Function that I can't read, when I try to open it, I can't see the definition.
I get following error

  Error : Insufficient privilege.

Nevertheless, it is being executed by my app without any issue, but I would like to export my app to another server and I can't export this function.

How should I do to be able to read it ?


texts:
How do I script a Create Table DDL for a View?

Example View:

create view dbo.CustomerTransaction
as
select
   cust.CustomerId,    -- this is int
   cust.CustomerName,  -- varchar(25)
   tr.PurchaseAmount   -- decimal(10,2)
from dbo.Customer cust
inner join dbo.Transaction tr
   on cust.CustomerId = tr.TransactionId

Expected Result:

create table dbo.CustomerTransaction
(
     CustomerId int,
     CustomerName varchar(25),
     PurchaseAmount decimal(10,2)
)


texts: How to achieve Data synchronization in Active/Active clustering using SQL Server 2012 Standard Edition

texts:
I have a requirement of Active/Active clustering to be implemented using SQL Server 2012 Standard Edition in shared storage SAN. How the data share or synchronization between the two instances on the the disks of nodes in shared storage is going to achieve on the same.

My concern here is how the data synchronization happens between the two nodes in SQL Server 2012 Standard Edition and how to configure the same?


texts:
I have successfully installed and run an INNODB Cluster on mysql 8.0.19

However I cannot set options for the cluster

when we run

cluster=dba.getCluster();
cluster.options();

We get the following error

Invalid object member options (AttributeError)

Please advise?


texts:
I am using DB2. So, I have a view which is working fine. It is :

Select
  Atr.Ssn_Tx
 ,Names.First_Na
 ,Names.Middle_Na
 ,Names.Last_Na
 ,Reff.Ref_Cd_Desc_Tx Suffix_Na
 ,MBR.PRSN_MBRSH_ID

 From Hix.T_Enrt En
 Inner Join Hix.T_Prsn_Enrt Pe   On En.Enrt_Id = Pe.Enrt_Id
                                And En.Enrt_Status_Cd In (428,439, 430 )
                                And Pe.Active_In = 'Y'
                                And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
 Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
 Left Outer Join Hix.T_Prsn_Addl_Attr Atr  On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
 Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd
                     From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                     where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names      On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID

Also, I have a select query which is also working just fine (a new column is created in this query). The select query is:

SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063)
               OR  (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y')
               THEN 'PRIMARY'
             WHEN FILLING_STATUS_CD=2063
               AND PRIM_TX_FILER_IN ='N'
               THEN 'SPOUSE'
             WHEN FILLING_STATUS_CD IN (2066, 2068)
               THEN 'DEPENDENT'
             ELSE   'Not sent to FDSH'
             END) AS FILTER_TYPE_CD
    FROM HIX.T_PRSN_MBRSH_TAX
    WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2

Now I have to make this select query a part of the view. I want to make a view which does all the work that my view and select query are doing individually.


texts: How to "hack-drop" a database snapshot after SQL Server upgrade has botched it

texts:
I'm dealing with an issue where database snapshots cannot be dropped after SQL Server has been upgraded to 2014. The snapshots are defunct but cannot be dropped.

Now although the Connect item has a disappointing comment in it I assume that this will be fixed eventually. However, I need to deal with this soon.

Is there a way to drop a database snapshot that cannot be deleted due to this bug? I fear that I have exhausted all documented means to do this. It might take undocumented modifications to system tables. Is there a way to force a database (snapshot) to be dropped?


texts: Insert into table with identity insert taking almost a second

texts:
We have an oltp application and I am trying to troubleshoot why insert into one of our table is taking close to a second.  Not all inserts are taking this much time but there are some which does take close to a second and since this insert is part of business transaction which can only take around 200ms, a one second insert is a  problem.

This table has a primary key which is identity and is nonclustered.  Clustered index is another column.

The insert stored procedure is very simple.  Something like

Create proc usptable_insert
( a bunch of parameters)
as
insert into table (col1, col2 ........)
values (parameters1, parameter2...... etc)
DECLARE @Id INT = (SELECT SCOPE_IDENTITY());
SET IDENTITY_INSERT [table2] ON
insert into table2 (col1, col2 ........)
values (@Id, parameter2...... etc
SET IDENTITY_INSERT [table2] OFF

We are in the process of migrating from table1 to table2 hence inserting into two tables and using identity insert to make sure table2 has same keys.

First two rows are the the insert statements

Initially I thought it was because of latch contention because of hotspot issue on the last page.  But I tried to reproduce this in pre-prod by running this stored procedure through sqlstress with 100 threads and couldn't.

If I am not wrong this behaviors cannot be blocking as its an insert and unless something is blocking the whole table which is not the case.

What can be other reasons for this behavious?


texts:
I'm trying to create a trigram index on a text filed containing names. I've added the pg_trgm extenion to the schema. Running \dx shows it is enabled:

List of installed extensions
   Name    | Version |   Schema   |                            Description
-----------+---------+------------+-------------------------------------------------------------------
 btree_gin | 1.3     | pg_catalog | support for indexing common datatypes in GIN
 dblink    | 1.2     | wos_core   | connect to other PostgreSQL databases from within a database
 pg_trgm   | 1.4     | wos_core   | text similarity measurement and index searching based on trigrams
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

However when I run the following:

CREATE INDEX authors_full_name_idx ON wos_core.interface_table USING GIN (authors_full_name gin_trgm_ops);

I get the following:

ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

Can anyone tell me what I'm doing wrong?

                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit


texts:
I'm trying to connect Tableau to our database Postgresql via PgPool, for load Balance.

I have Postgresql configured with Streaming Replication between one master and two slaves. And have another server running PgPool for load balance. All of this running correctly for the tests that we've done, I can connect via PgPool from another computer, and even connected some app we have and works fine, PgPool connects with servers with no problem. The thing is, when I try to connect Tableau to my PgPool server i got an Error:

"The connection to the data source might have been lost"

"Communication with the Tableau Protocol Server process was lost"

And checking the logs in my PgPool server I see that it receives the connection, and Tableau does some queries... I guess some to get the tables in the DB, but it says something like:

statement:  -- statement does not return rows

 SELECT *

 INTO TEMPORARY TABLE "#Tableau_0_1_Connect"

 FROM (SELECT 1 AS COL) AS CHECKTEMP

 LIMIT 1

but I'm guessing it can't get anything... and later Tableau tries to do some cursor:

BEGIN;

declare "SQL_CUR00000033237CDCD0" cursor for

select relname, nspname, relkind...."

but gets an error:

FATAL: unable to Execute

DETAIL: unable to get bind message

I don't know if I should configure something else, I'm guessing is some PgPool configuration, but don't know what it can be.

I tried to execute the same exact statements (both of them) in a terminal in another computer and i get rows, with the same user and password, so it's not a privilege thing...

Does anybody know how to solve this or how to use PgPool and Tableau correctly?


texts: Using DNS per application in ConnectionStrings

texts:
I have come across an idea recently when I had to migrate a ton of in-house applications to a new SQL server. The more I look at it, the more it sounds perfect, but I wanted to ask the community for feedback about it. It is using SQL0216 with AlwaysOn.

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Example: DNS:App1_SQL points to Servr1,  DNS:App2_SQL points to Servr1.
Server=App1_SQL;Database=App1;Trusted_Connection=True;
Server=App2_SQL;Database=App2;Trusted_Connection=True;

This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

If a particular application is using external database references, I trained my developers to always use Synonyms. This way, I create a Linked Server to the old Database, and update the Synonyms accordingly upon migration.

Other than having to manage a ton of DNS entry one-by-one, I still consider it a way to relieve some stress related to database migration, being able to do it by small chunks instead of a whole server in one shot.

Now, what are the caveats that I don't see? Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever. Would Kerberos be an issue?

Thanks in advance


texts: Best practice for record snapshot? (versioning?)

texts:
What's the best practice for storing a record snapshot?

Use case example: Assume a user bought a product in an online store. It is a pleasant experience that the user can view a copy of what he saw when he bought the product (description, the price, etc). Surely after the product been bought it may changed or may deleted. So a direct link / view to the product page is not in the cards.

I think of some some solution:

Create a product snapshot table with denormalized field included. I think this solution is the most simple. The drawback is, maybe it has the most storage requirements? But the storage price is considered affordable right now, isn't it? Kindly comment if you might have different thoughts. This might be similar to this https://stackoverflow.com/questions/720856/database-design-for-point-in-time-snapshot-of-data
Add a created date and valid until (or maybe just simple active) field in the table. Set valid until value to null if it is still valid. When creating a new record update valid until from the previous valid record is updated to current date. But managing the relation of previous related object or such can be involving many operations and prone to errors? This might be similar to this thread Versioning data in a database
Use temporal database implementation: https://en.wikipedia.org/wiki/Temporal_database, There are products that implement this, is it too sophisticated for this use case? or maybe something like Change Data Capture in Microsoft SQLServer? https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017. The drawback maybe it takes time configuring this implementation and maybe it is just too much for this use case?

Thanks for checking out this question


texts:
So I have the following scenario on my hands:

I have a product database that runs for a specific app, all inserts into the database come in through three separate connections.

Recently I have developed a new extension to the database in order to create predictions using SSAS.

My boss wants me to run a field test by creating a copy of the product database to see how well the predictions perform.

My problem is twofold:

According to Microsoft documentation of SQLServer 2012 the database mirroring feature will be removed in future versions: https://msdn.microsoft.com/en-us/library/ms189852.aspx The server is currently running 2012 but it might be upgraded in the future so I feel like this method is a dead end.

Should I be trying to use Database Mirroring for this scenario?

What is a good alternative for this way of Database Mirroring?


texts:
I have three MS SQL Servers, 2016. 1 source and two destinations.

There is a linked server setup from each destination server to the source.

We execute a simple select query from each destination server to the source server using OPENQUERY.

The query from destination 1 returns in 1 minute while the query from destination 2 returns in 7 minutes.

All the servers are in different data centers and we have checked the network, and we have not found any issues. What else can we check?


texts: MDX - Getting the last 6 month Dynamically

texts:
I have a Dim table that called DimAccounts.
It is describing a User account and the dates that the account has been created.
for Example:

I would like to get the list of accounts that have been created on the last 6 month Dynamically. For example today is the 09\01\2016.
So my list of accounts will be the accounts created from the 01-08-2015 until 09-01-2016. Notice that this CreatedOn field does not have a hierarchy and it is an attribute of the accounts dimension .


texts: Mongodb - save image as bytes or as files

texts:
I'm developing a website which allows the user to upload an image per item. I guess there will be hundreds per user.

What is more preferred?

Save the images:

as bytes in the DB (mongodb)
as files in the server

I don`t really know where to place this question in the "Stack Exchange" forums.


texts:
Some blogs tell that reload is equal stop and start, but in my test it does not stop the listener (uptime does not change).

What situation can I use only reload and when I must stop/start the listener? Is stop/start for just validate the applied configuration for all current connections? (force the all connections to reconnect in listener)?


texts: SQL Server DMV to show if database is used

texts:
What is the best indicator in finding out whether a database is utilized or accessed?

I tried index statistics and  based on I/O utilization but none seem to be a good indicator.

Problem: I have a lot of databases on a development environment and due to space issues we are trying to decommission those that are not used without actually messing with the ones that are really needed.


texts: MySQL - Investigate deadlock on atomic update

texts:
On a high traffic queue managed by Redis there is the following code:

update `ads_publishers` set `impressions` = `impressions` + 1 where `ad_id` = ? and `pub_id` = ?

Here's is the table schema:

CREATE TABLE `ads_publishers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ad_id` int(10) unsigned NOT NULL,
  `pub_id` int(10) unsigned NOT NULL,
  `impressions` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ads_publishers_ad_id_index` (`ad_id`),
  KEY `ads_publishers_pub_id_index` (`pub_id`),
  CONSTRAINT `ads_publishers_ad_id_foreign` FOREIGN KEY (`ad_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ads_publishers_pub_id_foreign` FOREIGN KEY (`pub_id`) REFERENCES `sites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Lately with an additional traffic increase I start receiving deadlock exceptions on the above query:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

The amount is not significant given the traffic, but I would like to know if there is anything I could do about it, instead of just increasing the execution attempts.

The session isolation level is set to REPEATABLE READ as far as I know. Should I even think about changing it to READ_COMMITTED just for the above transaction or this is to the wrong direction?


texts: Monitoring Query which executes thousand of run/minute and is generally fast

texts:
I am looking for some advise here on one of our SQL database server with below behavior-
Queries running for this databases are generally considered good with avg run time of 20 ms.
Suddenly on some weird days it will go from 20 ms to 80 ms and its very hard for our monitoring process to capture which time exactly it shifted its run time and why-
Our current monitoring method include below 2:-

DMV's from cached TOP SQL queries which does not help much as metrics are cumulative so its hard to find the point in time where issue happened and plan or something changed.

Extended events for RPC COMPLETED, SP statement completed, SQL batch completed and SQL statement completed, but only for queries running over 5 secs.

We have not looked into QUER STORE yet due to some issues i am reading online because this DB is SQL2017 with AG setup in 2 DC configuration. Though there are trace flags for some but our Eng team is hesitant still to use QS on this server which is high OLTP with batch req/sec on avg 30-40K/ secs/
Please suggest or advise if i am missing anything except third party monitoring tool?


texts:
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement.

Students

SELECT studentID, first_name, last_name, SchoolID, ... FROM students

CREATE VIEW vw_eligible_student
AS
SELECT * FROM students
WHERE enroll_this_year = 1

Teachers

SELECT TeacherID, first_name, last_name, SchoolID, ... FROM teachers

CREATE VIEW vw_eligible_teacher
AS
SELECT * FROM teachers
WHERE HasCert = 1 AND enroll_this_year = 1

Schools

CREATE VIEW vw_eligible_school
AS
SELECT TOP 100 PERCENT SchoolID, school_name

FROM schools sh
JOIN
     vw_eligible_student s
     ON s.SchoolID = sh.SchoolID
JOIN
     vw_eligible_teacher t
     ON s.SchoolID = t.SchoolID

At my workplace, I have investigated one of our in-house database application. I checked through the objects found out that there are two or three layers of the view stack each other. So that was remind me about what I read in the past. Can any one help explaining it?

If it is not OK to do so, I want to know that it is limited to just SQL Server or it is for database design in general.

Additional Info:
I updated an example from my company. I change a bit to be more general without too many technical (too many columns in this example). Mostly the nested view we used is based on abstract or aggregated view. For example, we have a large student table with hundred of columns. Say, Eligible Student View is based on students who enrolls this year. And student eligible view could be use other places such as in stored-procedure.


texts:
If I have a cte such as the below code. How many times does the table People get queried against? I was under the impression that it was only called 1 time and stored in memory but some of my queries I have been running seem to be running a lot longer than they should be. Which leads me to believe that it may be hitting the People Table 3 times.

with ctegeneric as (select person from people where person = 'dumb')
Select * from ctegeneric
UNION ALL
Select * from ctegeneric
UNION ALL
Select * from ctegeneric