texts: Installing Oracle 11gR2 on Oracle Linux 6 - error possibly related to database (unique) name

texts:
Installing 11gR2 on Oracle Linux 6.4. System is a desktop used by one developer. It's on DHCP. Hostname is similar to ip-t3400-1547.mydomain.local

Please note I have no issues installing this software on systems with fixed IP address, where the hostname is a solid alphanumeric string (no dashes).

I am loosely following:

Oracle® Database Quick Installation Guide 11g Release 2 (11.2) for Linux x86-64 E10860-01 August 2009
Oracle Database 11g Release 2 on Red Hat Enterprise Linux 6 Deployment Recommendations Version 1.1 March 2012

I've installed the package oracle-rdbms-server-11gR2-preinstall, which takes care of many pre-install tasks. I've set SELinux to permissive. Quote from /home/oracle/.bash_profile:

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=joe
export ORACLE_UNQNAME=joe
export ORACLE_HOSTNAME=ip-t3400-1547
umask 022

"joe" is the owner of the machine, so I'm using his name as the SID (real name edited for privacy). Please note that everything described below happens with or without ORACLE_UNQNAME being set here.

I select Server Class, Single Instance, Advanced Install, Enterprise Edition, General Purpose configuration type, then:

Global database name: joe
Oracle Service Identifier: joe

The above parameters, and the variables in .bash_profile, are different when installing on a system with a fixed IP address. In that case, I set ORACLE_HOSTNAME to the actual hostname of the machine (e.g.: dbserver13.mydomain.local), ORACLE_UNQNAME to the hostname (dbserver13), and SID same as UNQNAME (dbserver13). Works great.

I cannot do that on the DHCP-based workstation, because our naming convention includes the dash character in the hostname, which is not accepted by the installer for all those parameters.

I believe this difference is what causes the installer to fail, but I can't figure out an alternative.

I have tried to add "joe" and "joe.mydomain.local" to /etc/hosts, on the loopback entry (beginning with 127.0.0.1) but it makes no difference.

Character Sets: Unicode. Select Use Database Control, notifications disabled.

Some pre-requisite packages fail the check, but this is fine.

The installer keeps chugging along, then towards the end it throws this warning:

Enterprise manager configuration succeeded with the following warning -

Error starting Database Control. Please execute the following command(s).

1) Set the enrvironment variable ORACLE_UNQNAME to Database unique name

2) /u01/app/oracle/product/11.2.0/dbhome_1/bin/emctl start dbconsole

The problem is, I have that variable set already. It makes no difference.

As a result, the installer only asks me to run one post-install script, instead of the usual two scripts. The Web UI doesn't seem to work.

I am obviously doing something wrong.

How should I choose all those database names and SIDs and whatnot, in a way that allows me to install 11gR2 on a DHCP-based desktop?


texts: Difficulty Optimizing Postgres Write Throughput

texts:
I'm trying to index a collection of roughly 127K files using a
PostgreSQL 9.6 server on RDS. I had expected the process of writing these
documents to the DB to take about 8 hours, but over time I observe
that the write rate decays to 0 so that the process doesn't
complete (at some point, inserts begin timing out). Unfortunately, I don't have much DBA/PostgreSQL background,
so I'm struggling to debug this.

On average, indexing one file means inserting 125 rows and 0.5 MB
of data into a table (some files are significant
outliers, yielding ~40K rows). I have some uniqueness constraints on the table and would prefer to avoid introducing them later, if possible.

Initially, the max WAL size on the database was set to 2 GB. My
automation could process roughly 25K files before insert performance
became unusable, with acceptable write throughput during the first
hour. Increasing the max WAL size to 30 GB helped, but didn't completely solve the problem; with this configuration the system was able to index 85K files before the insert rate degraded. Looking at the
database logs, I saw primarily checkpoints that started due to the
configured timeout (15 minutes). On the RDS console, I see a fairly
consistent average write throughput of 5-15 MB / sec.

Eventually I may need to index a larger corpus of ~635K files, so I'd
like to find settings where I get consistent write throughput.

Database Specifications:

PostgreSQL 9.6.15 (on RDS)
6 TB disk
4 CPU
16 GB RAM
Max WAL size: 30 GB
Checkpoint Timeout: 15 min.
Checkpoint Completion Target: 0.9

Questions:

Do I need to increase the configured max WAL size again? Is there a rule of thumb for how large this should be?
Why did a 15x increase in max WAL size only increase the amount of files I could index by 3-4x?
Are there other places I should look for diagnostic information?
If I stop the write process temporarily and restart it hours later, write performance improves temporarily. Why is this?

Clarifications:

The database instance is a db.m4.xlarge.
The 6 TB of disk on this database is general purpose SSD.
The current timeout for the transaction that stores a single file's data is 15 minutes. My tests suggest this should be ample to process any individual file.
What I wrote originally about indices was not completely precise. I need to enforce some uniqueness requirements on a handful of columns, and would prefer to avoid introducing this constraint after the table is populated, if possible.


texts: Is there anything a table-level constraint declaration can do that a column-level one can not do?

texts:
Had an exam today. One question disturbed me:

  What can a table-level constraint do that a column-level constraint can't do?

My answer was that only a table-level constraint declaration permits setting up a composite primary key. I guess this is not the only difference. How could I have added to that?


texts:
Suppose we have R=(A,B,C,D) and FDs are {B->C, C->D}. The only candidate key is AB, then A and B are prime; C and D are not prime.
B->C breaks the 2NF requirements because is a partial dependency. So we break the relation into R1(A,B,D) and R2(B,C). But now C->D is lost! Am I wrong? So this breaking is not "good"?


texts: Why would CREATE INDEX with "online" mode suddenly slow my database down?

texts:
Earlier I used CREATE INDEX ... WITH(ONLINE=ON) without any problems - the statement would run in parallel with user queries and not slow them down at any time. Then suddenly I see that once I start the statement the database slows down greatly - user queries pile up, usually with PAGEIOLATCH_SH wait mode and the database effectively becomes unusable until I cancel the statement. The slowdown begins immediately after I initiate the statement execution. It happens both when I try to create an index that differs from existing indices and when I try to create an index identical to one of existing indices (with a different name of course).

What could possibly cause such change?


texts: Missing data when trying to import large columns using powershell

texts:
Powershell command (whitespace added for readability):

Invoke-Sqlcmd -ServerInstance "$db" -Database "$dbname" -Username "xxxx" -Password "xxx"
              -Query "select logmessages from log
                      where TIME_STAMP BETWEEN '2019-01-11 09:00:00' and '2019-01-11 09:02:00'
                      AND logmessages like '%string%'
                      order by TIME_STAMP"
              -QueryTimeout 0 -ConnectionTimeout 0
| Export-Csv "C:\Users\xxxxx\Desktop\PS\data.csv" -Append

Each row result is expected to be around 01 mb. The problem is I'm seeing partial data.

The table has 17 columns and I'm trying to fetch data from one of the column (type:varchar; length: -1) which typically stores xml data close to the size of 1mb per record. When I run a select statement against that column, only partial data is returned.


texts: Postgres functional indexes that returns an array and used in joins

texts:
I have a table that looks like this:

Value Table
(active), (version_no), (thing_id), (data)
------
true,       3,            7,          "active-value"
true,       2,            6,          "other-thing"
false,      2,            7,          "old-value"
false,      1,            7,          "first-value"

That is to say, each "thing" has one value. Every time the value is updated (only active values get updated), a trigger puts the old version into the table and marks it as inactive. I only want one version of each piece of data.

I would like to have another table reference the values. But it can reference it in one of two ways. It can either reference the "active" value for a thing or a fixed version.

Reference Table:
(version_no), (thing_id)
------
2,               7 -- Will always return "old-value"
3,               7 -- Will always return "active-value"
null,            7 -- Will return "active-value" until a new value is inserted...

I would like to have a single way to query the references based on the value they point to.

One way I thought of is to have a functional gist index f(active, thing_id, version) on the values table that would return a list of tuples that a reference could match on. For instance f(false, 2, 1) -> [(2, 1)] and f(true, 2, 3) -> [(2, 3), (2, null)]

Then check if

SELECT references.*
FROM references
INNER JOIN values
    ON (
        (reference.thing_id, reference.version) =
        ANY(f(values.active, values.thing_id, values.version)
       )
WHERE values.data = "abc"

Am I on the right track? Is there a better way of having "dual references" to another table? Is there a more efficient way than an array?


texts:
I'tm trying to show in SQLcmd window the results of these queries:

SELECT SYSDATETIME() AS "date";

PRINT 'EXTRAÍDO POR:'
SELECT SUSER_NAME() AS "Owner"

PRINT 'PASSWORD SETTINGS';
exec xp_loginconfig;

PRINT 'PASSWORD SETTINGS';
select * from sys.sql_logins;

And using this command in SQLcmd:

sqlcmd -S myServer -i inputpath -s, -o outputPath(like C:\log.xls) -e

So the sqlcmd output the file and does not show nothing in sqlcmd screen. How can i print these "Print" commands in the sqlcmd screen? (command promt).

When I run it on SSMS works well, like:

(1 row affected)
OWNER:

(1 row affected)
PASSWORD SETTINGS

(8 rows affected)
PASSWORD SETTINGS

I would like to show in the prompt command this, like in the SSMS.

Thanks!


texts: Is matriculation number a good primary key?

texts:
I am trying to design a grade assignment database and i am facing the following dilemma.

I have a student table which has the following characteristics:

matriculation number which i know for a fact is unique.
first name
last name
course of studies(Bsc Mechanical engineering, Msc      mechanical
engineering, Bsc computer science etc...)
email
remark Maybe some special notes about a particular student.

However there is also the following condition. Every student can at the same time belong to two different course of studies, that is a student can be Bsc and Msc mechanical engineering at the same same time. So after some thought i decided to create a separate table called course of studies, which has two columns an auto increment id(PK) and a course of study which will include all possible course of studies. Then connect the student and course of studies tables using a many to many relationship.

At the same time i thought that another implementation would be to use a surrogate key (auto increment id) on the student table and instead of creating a separate course of studies table i could simply incorporate it in the student table as a column and not have any problem with my primary key. Since if i had a student that belongs on two different course of studies i would have two different rows with a unique id

So my question to you is what do you think is the best implementation given the situation(Please explain why). I know that natural primary keys vs surrogate primary keys is a highly debated subject which doesn't seem to have a universal answer however given the situation what do you think is the best implementation?

Any additional details can be provided .

EDIT
There are two good answers that are saying more or less the same thing. I chose to accept the one with the more votes.


texts:
I'm reviewing some execution logs for long running queries in SSRS.
A common thread I'm seeing is reports that seem to take a few hours to process, with a rowcount of 0.

I'm using the following query to get my results:

SELECT e.instancename,
   e.username,
   e.timestart,
   e.timeend,
   Datediff(mi, e.timestart, e.timeend) AS 'Minutes',
   e.timedataretrieval,
   e.timeprocessing,
   e.timerendering,
   e.[RowCount],
   catalog.PATH,
   catalog.name
FROM   executionlogstorage e
   INNER JOIN catalog
     ON e.reportid = catalog.itemid
   INNER JOIN users
     ON catalog.modifiedbyid = users.userid
   INNER JOIN users AS users_1
     ON catalog.createdbyid = users_1.userid
WHERE  Datediff(mi, e.timestart, e.timeend) > 15
   AND catalog.name <> ''
   and TimeStart > 01-01-2014
   -- Filtering out Oracle reports--
   and (TimeDataRetrieval + TimeProcessing + TimeRendering + [RowCount]) <> 0
ORDER  BY 'Minutes' DESC

And one of the results I'm not sure how to translate is:

|Minutes | timedataretrieval | timeprocessing | timerendering |  RowCount   |
  |243     | 3                 | 14453530       | 0             |  0          |

How is it possible to have a report processing for 4 hours, and return 0 rows, with a dataretrieval time of 3 miliseconds?
If this were a lone case I'd ignore it, but I've got a hundred similar ones. (and a few thousand ones that do make sense)


texts: How to efficiently eliminate data from select when rows are different by one column value

texts:
I have such TABLE with data:

A  | B  | C  |  D | E
-----------------------
a1 | b1 | c1 | d1 | PA
a1 | b1 | c1 | d1 | PR
a2 | b2 | c2 | d2 | PA
a3 | b3 | c3 | d3 | PA
a3 | b3 | c3 | d3 | PR

row1 has its counterpart row2 (they are only different by col E)
row3 has no counterpart - unique
row4 has its counterpart row5  ( same as first statement - col E )

the query should gave me a result like this:

A  | B  | C  |  D | E
-----------------------
a2 | b2 | c2 | d2 | PA

only unique row which has no counterpart -
no row with same set of data only which different only by value in column E

we can apply here a few approaches which one will be most effective if we have more rows with its counterparts as unique rows (without counterpart)
which join use ?
which one created select statement subtract or add ?

ps. an extended case

A  | B    | C  |  D                  | E
----------------------------------------
a1 | b1   | c1 | 2017-05-16 11:46:36 | PA
a1 | b1   | c1 | 2017-05-16 11:46:37 | PR
a2 | b2   | c2 | 2017-05-17 01:34:28 | PA
a3 | null | c3 | 2017-05-12 19:14:15 | PA
a3 | b3   | c3 | 2017-05-12 19:14:15 | PR
a4 | b4   | c4 | 2017-05-12 19:16:15 | PA
a4 | b4   | c4 | 2017-05-12 23:16:15 | PR

column A -D may contain null values  (row4)
column D is a TIMESTAMP  and it could vary from its counterpart by 1-2minutes   (row1, row2)
we add the max difference in timestamp  = example 2 minutes (row6 has no counterpart as row7 vs row6 timestamp difference is greeter than 2 minuts (4 hours)
we don't want any row with PR in our result

expected result:

A  | B    | C  |  D                  | E
----------------------------------------
a2 | b2   | c2 | 2017-05-17 01:34:28 | PA
a4 | b4   | c4 | 2017-05-12 19:16:15 | PA


texts: SELECT query to read recursive relationship table

texts:
I have below MySQL database table which has a recursive relationship. A hospital maybe managed by another main hospital in this scenario. I have a difficult time trying to retrieve all data with name of the hospital which manages another hospital.

Some expert help would be much helpful for me.
Thanks.


texts: SQL server not using the memory allocated

texts:
We have noticed that for one of our SQL server with below configuration ,memory is not getting availed as allocated.

  SQL server 2005 SP4, 32 bit

  Windows server 2003 R2 SP2, 32 bit with 16 GB of RAM and 12 Cores

Also, we have enabled AWE and PAE as well.

Min server memory = 1024

Max server memory = 13500

I know Task manager is not a best way, but RAMMAP is also not working, but we believe SQL is not using the allocated memory.

Please advise what can be done here or is my AWE not properly configured?


texts: User, group, or role ... already exists in the current database. Changed database context to 'DWConfiguration'. Error code: 0x84BB0001

texts:
Most of the question is in the title.
My case concerns MS SQL Server 2017 - Developer Edition.
The error happens during SQL Server 2017 installation process.
While searching for answer online I noticed, the majority of the cases when this error emerges, is while migrating existing db into newer version of MS SQL Server but I get it during MS SQL Server 2017 installation process.
Before posting this question I've read this and this and many sites like these,
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/05d4bdb7-9585-4d8c-9f35-2fb9d4355ccc/error-0x84bb0001-installing-sql-server-2017-express-on-windows-10-pro?forum=sqlgetstarted ,
that always say about "migrating some existing database" or "associated Databases" and setting privileges in SQL Management Studio.
Well I get it during SQL Server 2017 installation.
It's the first time I install any version of SQL Server in my life.
I don't even have SQL Management Studio installed yet.
I've read this as well but I'm not mapping no users to any existing DBases. I'm just trying to install this s*** for the first time in my life.
As tutorial I'm using this youtube video.
Well, during installation on the "feature selection" screen I ticked everything - not only "Database Engine Selection".

The whole message of the error I receive is as follows:
Error installing SQL Server Database Engine Services Instance Features
User, group, or role 'ZARZĄDZANIE NT\USŁUGA SIECIOWA' already exists in the current database.
Changed database context to 'DWConfiguration'.
Error code: 0x84BB0001
Visithttps://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.1000.169&EvtType=0x7590AFD6%400x9A41B222&EvtType=0x7590AFD6%400x9A41B222 to get help on troubleshooting.

I'm installing this MS SQL Server on Windows 10 Home Edition x64.
Another thing I've done different than on the youtube tutorial is here, https://youtu.be/sgMCfX2zmOc?t=589  on this very "Database Engine Configuration" screen I left tick at "Windows Authentication Mode" where as in the Tutorial it is changed to the Mixed Mode - but can this setting be the reason of User, group, or role 'ZARZĄDZANIE NT\USŁUGA SIECIOWA' already exists in the current database. Changed database context to 'DWConfiguration'.
In case it's necessary I'm uploading the complete error log to OneDrive and I'm sharing the link with you.
I don't know what else could I write here.
Give me a hint in the comments, if the link to the log files works.
UPDATE IS FROM HERE
After few hours of rest I've made another attempt to instal MS SQL SERVER 2017 with Polybase Query Service which in my opinion is a feature responsible for the previous crashes of earlier installation attempts.
This time I made sure to add myself as the sys admin in every stage of the installation.
Moreover, I changed the Authentication Mode to "Mixed Mode" just as it's shown in all tutorials I've been following. The password chosen was a strong one, so no mistake here.
The result is similar as before (failure of all SQL features installation) but the error is different now. It's even less descriptive this time.
TITLE: Microsoft SQL Server 2017 Setup
The following error has occurred:
The handle is invalid
For help, click: https://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.2027.2&EvtType=0x69CEA745%25400xDC80C325

One thing got cleared out 4 sure. The authentication method is irrelevant, because the SQL Server 2017 instance won't install no matter if the authentication Method chosen is "Windows Authentication Mode" or "Mixed Mode".
Here is a screen shot from the very first installation attempt. Just have a look underneath the green progress bar. It's always PolyBase Query Service feature that crashes whole SQL Server 2017 installation and its main component: Database Engine Services.

No Hadoop queries for me :-)


texts:
I'm trying to aggregate about 500-1000 MS Excel files that deal with various small business issues (inventory, purchase orders, etc) and I'm currently trying to decide what kind of RDMS I should use. To give you more detail, the database will be mainly used by 1 maybe 2 people hosted on network server. Currently, I've been fooling around with MariaDB and Microsoft Access. Excel data is easily imported into MS Access and I have some road bumps when it comes to importing that same data using MariaDB. The DB must be easily query-able and must have the capability to easily import data from a spreadsheet.

I'm not sure what direction to go in, do I continue fenagling with MariaDb and MySQL or move onto MS Access. Or, should I be using something else?


texts: 'go to symbol' in Allround Automation PL/SQL Developer?

texts: I really like using my PC just by keyboard and any time I have to use a mouse is a real pain for me. I really like 'go to symbol' feature in VS Code or Sublime Text, so I can navigate through any source code with ease. Is there anything that would bring this feature also into PL/SQL Developer, so I would be able to easily navigate through my PL/SQL packages?


texts:
We're analysing a deadlock situation on a SQL Server 2005 database and have set the trace flags 1204 and 1222.

In the resulting logs we're seeing the following line repeatedly:

"Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values."

Can anyone tell me what's likely to be causing this? There's nothing particularly wrong with the queries that I can see (they're generated by Hibernate so are a bit messy and full of aliases but are fundamentally sound).

Is it something I need to worry about or something that can be ignored?


texts: How to find the rows from a table which has exactly two 'n' s in the employee name?

texts: I want to find all the employees whose names have exactly two n's in their name. How can this be done?


texts: Group By - Incorrect syntax near 'mycolumn'

texts:
I'm getting a syntax error in SQL Server Management Studio (SQL Server 2008) for the following query:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, '')))  Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);

Error message:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'Address4'

I can't see what is wrong - this works:

SELECT
    MIN(EffectiveOn) AS EffectiveOn,
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
FROM
    NameAddressCoverHoldingTable
GROUP BY
    Address1,
    Address2,
    Address3,
    --RTRIM(LTRIM(COALESCE(Address4, '') + ' ' + COALESCE(Address5, ''))) Address4,
    PostCode,
    PolicyId
ORDER BY
    PolicyId,
    MIN(EffectiveOn);

What is the syntax error?


texts: PostgreSQL partition hierarchy design for centralized logging

texts:
I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with partitioning, but a multi-layer partition:host-service inherits service, host-service-yyyymm inherits host-service.

I've worked out an example scheme for a specific service (php errors in this case) and am looking for some PostgreSQL experts to critique it for obvious performance bottlenecks.

// SET UP MASTER PHP LOG TABLE //
CREATE TABLE php (
 log_id     int not null,
 host       char(5),
 logdate     date not null,
 message        text
);

// SET UP HOST-SPECIFIC 'PARTITIONS' //
CREATE TABLE host1_php (
 CHECK ( host = 'host1' )
) INHERITS (php);

CREATE TABLE host2_php (
 CHECK ( host = 'host2' )
) INHERITS (php);

// SET UP HOST-SPECIFIC TIME 'PARTITIONS' //
CREATE TABLE host1_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host1_php);

CREATE TABLE host1_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host1_php);

CREATE TABLE host2_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host2_php);

CREATE TABLE host2_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host2_php);

CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate);
CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate);
CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate);
CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);

I'll also be adding services such as apache access/errors.

I think I have to trigger to insert into the host_service (to utilize the host check constraint), and then each host_service must trigger to insert into the host_service_yyyymm table.

What can I expect performance-wise of such a trigger/partition scheme?

Some extra info discussed in chat:

PostgreSQL version 9.1.2
Queries on data won't be very often, and mostly on the current month of data for multiple hosts.
PHP is only about 1 per minute insert, but total apache is probably going to be around 300-500 per second across all hosts.


texts: How can I add multiple partitions to a Greenplum table using dynamic SQL

texts:
I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for the specified date.

I have another function that generates a date range based on the data present in the overflow partition, and calls the first function to split the overflow partition into a new partition for each date.

However, I don't think that it is possible to do it this way, as it fails with this error:

ERROR: relation 6712928 is still open (relcache.c:2417)

I suspect that this is because it's trying to do all the alter statements in parallel. Can anyone think of a way to execute the statements one at a time?

I tried executing the query in a FOR loop and calling the function once per iteration of the loop, but it does the same thing.

Here are my two functions:

 CREATE OR REPLACE FUNCTION dv_util.create_partition(p_schema character varying, p_table character varying, p_date date) RETURNS boolean AS
 $BODY$
 DECLARE
   splitter varchar(10000);
   counter integer;
 BEGIN

   splitter :=  'ALTER TABLE '||p_schema||'.'||p_table||' SPLIT DEFAULT PARTITION START ('''||p_date::text||'''::date) INCLUSIVE END ('''||(p_date + interval '1 days')::date::text||'''::date) EXCLUSIVE INTO (PARTITION "'||p_date::text||'",      default partition)';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table;
   IF counter > 0 THEN -- table exists
     SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_'||p_date::text;
     IF counter = 0 THEN -- partition does not exist
       EXECUTE splitter;
       RETURN true;
     ELSE
       RETURN false;
     END IF;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE;

 CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     FOR q_add IN EXECUTE adder LOOP
       SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;


texts:
I have an issue where whatever I do I cannot get MySQL to use a spatial index.

A query like the following then takes 5 seconds!

Any way to force this to use the index at all?

WITH ranked_reports AS (
    SELECT
        station_id,
        raw_text,
        observation_time,
        RANK() OVER ( PARTITION BY station_id ORDER BY observation_time DESC ) order_rank
    FROM
        METAR
    WHERE
        ST_Distance_Sphere (
            geo_point,
        ST_GeomFromText ( 'POINT(51.85 -0.79)', 4326 )) <= 100 * 1609.34
    ) SELECT
    *
FROM
    ranked_reports
WHERE order_rank =1

Explain of query

+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra                       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 8       | const |     10 |   100.00 | NULL                        |
|  2 | DERIVED     | METAR      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 585814 |   100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-----------------------------+

Table Structure

CREATE TABLE `METAR` (
  `station_id` varchar(5) NOT NULL,
  `station_iata` varchar(5) DEFAULT NULL,
  `observation_time` datetime NOT NULL,
  `latitude` float(255,2) DEFAULT NULL,
  `longitude` float(255,2) DEFAULT NULL,
  `raw_text` varchar(255) DEFAULT NULL,
  `temp_c` float(255,1) DEFAULT NULL,
  `dewpoint_c` float(255,1) DEFAULT NULL,
  `wind_dir_degrees` int(255) DEFAULT NULL,
  `wind_speed_kt` int(11) DEFAULT NULL,
  `wind_gust_kt` int(11) DEFAULT NULL,
  `visibility_statute_mi` float(255,2) DEFAULT NULL,
  `altim_in_hg` float(255,2) DEFAULT NULL,
  `altim_in_hPa` float(255,0) DEFAULT NULL,
  `sea_level_pressure_mb` float(255,0) DEFAULT NULL,
  `wx_string` varchar(255) DEFAULT NULL,
  `sky_condition` mediumtext,
  `flight_category` varchar(255) DEFAULT NULL,
  `three_hr_pressure_tendency_mb` float DEFAULT NULL,
  `maxT_c` float(255,0) DEFAULT NULL,
  `minT_c` float(255,0) DEFAULT NULL,
  `maxT24hr_c` float DEFAULT NULL,
  `minT24hr_c` float(255,0) DEFAULT NULL,
  `precip_in` float(255,0) DEFAULT NULL,
  `pcp3hr_in` float(255,0) DEFAULT NULL,
  `pcp6hr_in` float(255,0) DEFAULT NULL,
  `pcp24hr_in` float(255,0) DEFAULT NULL,
  `snow_in` float(255,0) DEFAULT NULL,
  `vert_vis_ft` int(255) DEFAULT NULL,
  `time_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `geo_point` point NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`station_id`,`observation_time`),
  KEY `IDX_Station` (`station_id`) USING BTREE,
  KEY `IDX_Deletion` (`observation_time`) USING BTREE,
  KEY `IDX_IATA` (`station_iata`,`observation_time`) USING BTREE,
  SPATIAL KEY `IDX_Spatial` (`geo_point`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


texts: why are people advised not to update through views

texts:
During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views.

Where there/Are there significant performance implications to doing this?
Or is this more a case of senior developers/DBAs' telling junior personnel not to do this because they can unwittingly wreak havoc with an incorrect join.

EDIT
I'm using MSSQL 2000-2008 (Depending on the particulars of the client)


texts: Handling 'Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements'

texts:
Our systems suddenly started failing on:

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements.

There was no service pack or other installation changes and the data has not changed in nature.

It is a legacy system with no documentation with nested stored procedures and triggers, some of which have XACT_ABORT ON and IF @@TRANCOUNT > 0 ROLLBACK.

In order to make a temporary fix, I want to ignore the error and carry on through the procedure but it is not picked up by the CATCH.

Is there any way I can catch and handle this error? Is there any reason that badly written nested transactions could start behaving differently? We have checked connections defaults have not been changed.

  Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Standard Edition
  (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts: Best approach for Legal/Natural can have many Orders

texts:
I'm working on this model:

where as you may see one NaturalPerson can have many Order and the same for LegalPerson the only difference between NaturalPerson and LegalPerson are a few so in that case will be better to repeat all the Order fields in NaturalPerson and in LegalPerson or will be better to leave as I have right now? My only concern around my model is if is not this a serious inconsistency to leave an empty column at Order table (legal_person or natural_person depends on which the Order belongs to) as the Orders belongs to a Natural or a Legal but not both of them?

What will yours do in this case?

1st approach

For all the pros mentioned at this post I tough the best here is go with Class Table Inheritance so if I understood the explanation my model now is this one:

It's right?

2nd approach

See my own answer below ...


texts: SQL Server Restore w/o "Closing Existing Connections"

texts:
By default in SSMS (2012/2014) when you choose to restore a database, the "close existing connections" option located in the
is by default unchecked, is it possible to restore a database while maintaining a connection to it (i.e. in an Enterprise Edition)? If not, why have this option (besides as a sanity check), if so? How?


texts: Getting the data from a foreign key table

texts:
I have a table that relates a user to client using both their table id's

UsersClients Table

   ---------------------
   | userid | clientid |
   ---------------------
   | 2      | 32       |
   | 3      | 43       |
   | 2      | 24       |
   | 2      | 12       |
   ---------------------

The userid relates to the id field with in my Users Table
The clientid relates to the id field with in my Clients Table

I have linked them all up OK.

My question is how can I use this key table to get the details.  For instance, I am the user with an ID of 2. By simply doing a query on the above UsersClients Table with the userid of 2, I will simply get the following results 32,24,12.

Now, in order to get the then client details from clients table for each of the returned results, I would query the clients db with something like

   SELECT * FROM Clients WHERE id IN(32,24,12);

But as I have the above UsersClients Table linking up to the relevant tables that I can approach this better, so I don't have to first scrape the UsersClients Table for each client ID then scrape the clients table for the relevant details.


texts: Where should one put indexes in a time dimension table?

texts:
After reading the Questions and Answers from this website about indexes, a question came to my mind.

What if, one is using a time dimension table with the lower level of granularity being the day. Where should one put the indexes ?

Randy Melder in the question : What does “index” means on RDBMS ? said :

  Think of an index as "table of
  contents"... that is an ordered list
  of pointers to positions in a file,
  aka offsets

In the case of the time dimension, most data research might be done either for a specific day, a specific week, a specific month or a specific quarter if the time table stores all the day for a unique year.

My question is : Should one put indexes for all those fields ?

Day is suppose to be unique so for this one I understand perfectly the use of indexes.
But a week id will have 7 occurences, a month id will have 30/31 occurences, a quarter id will have more or less 120 occurences.

Should one still put indexes for those fields ?
Will it still be useful?

I am asking you that because in the same question, David Spillett said :

  Adding too many indexes can be a bad
  optimisation of course, as the extra
  space used to store the indexes (and
  the IO-load to maintain them if your
  DB sees many write operations) may be
  a worse problem than the slightly less
  optimal read queries, so don't over-do
  it.

So what would be the best considerations  for the time dimension case ?


texts: Memory Pressure in SQL Server - Enable LPIM or not?

texts:
I have slow query execution in my SQL Server 2016.

Server configurations:

Total Physical Memory : 128 GB
SQL server Max Memory : 102 GB
Transaction replication is enabled.
Database Size:1.6 TB
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0  (Build 14393: )

I ran the ring buffer query to check if Physical memory is low. The reason I checked for ring buffer for memory pressure is because my plan cache is also getting cleared frequently even though no one is running any script to clear cache.I get the below result where I see HIGH and LOW with same values for Avail Phys Mem.KB.

One thing I learned is for all RESOURCE_MEMPHYSICAL_LOW the indicator process is 2, which means memory issue is in the memory allocated to SQL Server not the rest of memory available for OS and other application.
FYI. This is a SQL Server dedicated server.

I also checked in Performance Monitor for Avaialable MBytes which shows the value 16,662.00 (16 GB) as below.

I think 16 GB is enough for OS+other applications, this being a SQL server dedicated server.

Checked the server diagnostics as well. It says that 25 GB is available.

So now I am confused whether:

Is there a memory pressure in this server?
The RESOURCE_MEMPHYSICAL_LOW flag is being set by the OS or external process or SQL Server?
Should I enable LPIM to fix this issue?

Additional details as per @Shanky's answer.

I had Query store enabled, but I disabled it a week ago.
rounds_count for SQL Server Plans with clock hand HAND_EXTERNAL is increasing.

More Details.

I am facing huge memory grant by some queries and this is causing RESOURCE_SEMAPHORE waits for other queries.

Does this huge memory grant(one query i checked ,memory grant is ~7GB)  result in a memory pressure from SQL server and can this be the cause of plan cache clearing and RESOURCE_MEMPHYSICAL_LOW flag.


texts: Is there any MongoDB handler for find event?

texts:
I want to know is there any handler or trigger kind of functionality exist in MongoDB which can help me to run my mongo command on find query?

Basically, I want to run a specific set of Mongo queries on find query. You can say a listener on find query to run other mongo commands.

Other than that is there anything like create a view from a remote database, I know MongoDB provides creating a view from the same database but I want from the remote database?

I do not want any application level solution like any third party module or watcher script, I want something which is within the MongoDB itself like inbuilt function or any event handling.

I have tried auditing but, it only records the operation and all, does not provide any handler through which I can run my mongo commands like Oracle providing in Fine-grained auditing.

Any help related to this is appreciable, Thanks in advance.


texts: Copy SQL database with encryption from one Azure subscription to another

texts:
I need to copy an entire Azure SQL Database from one subscription to another.

The challenge I have is that it has a certificate with symmetrical key and if I try to export it to a .bacpac file I get this error:

  SQL71626 (certificate/symmetric key is not supported in Microsoft Azure SQL Database v12)

Like all things IT there are probably 101 ways of doing this, and a number of sites explain some options (but none that I have found mention encryption and how best to deal with it).

Some additions info:
The destination subscription already exists
It has a SQL Server, I can add more if required.


texts: Where is pt-table-checksum finding this hostname?

texts:
The Setup: An EC2 instance running MySQL 5.6 locally replicating to RDS MySQL. The VPC IP address range is 172.0.0.0/8.

Somehow the slave got out of sync and I'm getting this error when I do SHOW SLAVE STATUS:

Could not execute Update_rows event on table db37708.general; Can't find record in 'general', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log newprod.000106, end_log_pos 285712203

I compared the master and slave tables and yes, there is some data out of sync. Not schema, just text data. Okay, fine, I'll just run my script that wraps pt-table-checksum and I get this:

[ubuntu@fabersubuntu utils ]$ ./checksumSpecificTable.sh db37708 general
Replica ip-10-3-2-119 is stopped.  Waiting.
Replica ip-10-3-2-119 is stopped.  Waiting.
^C# Caught SIGINT.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-19T12:09:50      0      0        1       1       0  32.282 db37708.general

So I run the script again after adding PTDEBUG=1 to it and I see things like this:

# DSNParser:1638 30974 DBI::db=HASH(0x21aae58) SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
# DSNParser:1652 30974 DBH info:  DBI::db=HASH(0x21aae58) $VAR1 = {
#   '@@hostname' => 'ip-10-3-2-119',
#   'CONNECTION_ID()' => '351452',
#   'DATABASE()' => undef,
#   'VERSION()' => '5.6.40-log'

My questions are:

Where is this "hostname" coming from? Assuming it's an IP address format, it makes sense that it is waiting because I don't have any hosts in a 10.0.0.0 range.
If I CALL mysql.skip_repl_error, I get another error; apparently I have lots of errors all of a similar type (so far). I assume pt-table-checksum can't do anything for me if replication isn't working, so what would be next step be other than rebuild my RDS slave?


texts: flatten JSON array into comma delimited list

texts:
I am trying to construct a SELECT statement which will take a JSONB column in the following format:

{
  "Cities": [
    {
      "Name": "Atlanta"
    },
    {
      "Name": "London"
    },
    {
      "Name": "New York"
    }
  ]
}

The output of the column result set needs to be in the following format:

Atlanta, London, New York

UPDATE

@a_horse_with_no_name's answer below is correct, but my requirements are actually a little more complicated than originally posted. I actually need to fit this select in to a larger (join) query as follows:

select eo.citydata.cities.names <-- Flattened Comma delimited JSON Array
from orderline o
join eventorders eo on eo.orderlineid = o.id
join events e on e.id = eo.eventid
where e.id = '123'

Clearly the answer provided will need to be modified in order for this to work and I'm struggling to figure out how to do it.


texts: Impacts of a change to the Page Verify option

texts:
There's no doubt about importance of doing regular integrity checks on production databases.
I'm testing the impact of changing the maintenance plans and changing how databases are backed up.
First of all, it is necessary to activate the "checksum" pages verification mode, this changing I think gradually impact the performance because it activates when a certain page is read in memory, changed, and written back to disk.

I have started to test this change with backups. I prepared a backup TSQL script that save the duration of each backup command and I ran 100 times each of the following command:

BACKUP DATABASE [MyDB] TO DISK = N'nul' (ran 100 times)
BACKUP DATABASE [MyDB] TO DISK = N'nul' WITH CHECKSUM (ran 100 times)

MyDB it's a 50gb database with page verify set to "checksum".
The results is:

Backup with checksum is 20% slowler

I started some test with a larger database (400GB) and I noticed that first backups (with empty cache) are generally slower. After the first backups have completed the duration tends to stabilize.

My questions are:

Does the backup process buffers data in cache ? If yes, could this cause a variation of PLE trend or memory pressure ?
If backups with checksum are 20% slower what happens with application queries? I don't think my tests are the absolute truth but..
Is there any kind of waiting time linked to the checksum process?
Do you know if there are people online who have tested the impacts of this change ? Any additional material can be useful.


texts: How to multi-match two tables efficiently?

texts:
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both tables have multiple columns. The problem is, that every object may belong to an indefinite amount of categories and I need to query both efficiently: All objects that belong to a specific category and for any object to which categories it belongs, which is why I can't just use joins.

My idea was to create a third table with just two columns, matching objects to categories. But it looks like an ugly solution to me and I also don't know what indices I should set upon it, if any at all. I might end up with the key column's data being saved four times: In the original table, in its PRIMARY KEY, in the matching table, and in the index on that table.

Is there a better approach or maybe even a standard method to handle such situations?


texts: MySQL master to slave dump fails when done over SSH pipe

texts:
When we need to [re]build a MySQL-5.5 slave, our usual approach is this (CHANGE MASTER TO / START SLAVE omitted for clarity):

master$ mysqldump -uroot -p --all-databases --master-data > dump.sql
master$ scp dump.sql slave:
slave$ mysql -uroot -p < dump.sql

This has always worked flawlessly, and we are used to doing it on busy masters without any problem. Now we're trying to automate it a little more and I came out trying to run this from the slave system:

ssh masterhost.mydomain "mysqldump -uroot -p --all-databases --master-data" \|
  mysql -uroot -p

We're basically doing the same thing we do with a dump file, but we're doing it "on the fly" over SSH. At first it seems to work, after asking for passwords it runs mysqldump on the master system, its output goes through ssh and gets piped in mysql on the slave system. But then when we START SLAVE we almost always get duplicate primary key conflicts on the slave system (note that IO thread is running without problems).

As far as I know --master-data should acquire enough locking (--lock-all-tables implied, and we also tried adding it explicitly) to avoid this kind of problems, and it takes care of sending out correct log positions from the master so we cannot get them wrong.

I should mention that we have a mix of InnoDB and MyISAM tables.

Why doesn't this dump-and-restore-over-SSH work just like the dump-to-file-restore-from-file, as I would expect?


texts: Can a foreign key have a default value equal to 0?

texts: I have a column whose default value is 0 .Can I make that column as foreign_key


texts: How to force subscriber to have foreign key WITH CHECK?

texts:
I know by default SQL Server replication adds WITH NOCHECK and NOT FOR REPLICATION to the foreign keys after transactional replication. But is there are way, where I can avoid these code changes made by replication. We use a compare tool and these small changes get highlighted. I have an understanding, why they are placed, but some requirements have come up in which I want to have WITH CHECKs constraint at subscriber side as well. I just want to know if somehow we can avoid these few additions and changes to articles scripts both on publisher and subscriber.

The orignal script looks like this:

ALTER TABLE [dbo].[Tablename] WITH CHECK ADD CONSTRAINT [FK__E__06] FOREIGN KEY ([eId]) REFERENCES [dbo].[table] ([Id])
GO
ALTER TABLE [dbo].[Tablename] WITH CHECK ADD CONSTRAINT [FK__E_S__05] FOREIGN KEY ([nId]) REFERENCES [dbo].[table] ([Id])
GO

Altered script at subscriber appeared as this:

ALTER TABLE [dbo].[Tablename] WITH NOCHECK ADD CONSTRAINT [FK__E__06] FOREIGN KEY ([eId]) REFERENCES [dbo].[table] ([Id]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Tablename] WITH NOCHECK ADD CONSTRAINT [FK__E_S__05] FOREIGN KEY ([nId]) REFERENCES [dbo].[table] ([Id]) NOT FOR REPLICATION
GO

I want to avoid these changes and in article properties pane, I have seleced True for contraints to be copied at subscriber


texts:
I am trying to setup Managed Backup (in Azure SQL) using SQLServer  2014 (Ent 64bit) and  it is failing.

I have the credential created with Identity as the Storage account name and Password set with current Access key, I've tried both keys as well.

When I run through the GUI to Enable managed backup, set the retention period, add the SQL credential (which updates the Storage URL) and I press OK, I get the follow error message back:
Alter failed for

  SmartAdmin 'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.  (Microsoft.SqlServer.Smo)

  Failed to access the storage URL 'https://[storageaccountname].blob.core.windows.net/' using credential 'MyCredName'.
  Provide a valid SQL Credential for the storage URL. (Microsoft SQL Server, Error: 45207)

I downloaded Azure Storage Explorer from codeplex to test connection and it works, I can browse, upload, delete from my local machine.

I tried this from one of our on prem servers as well and same error.  What am I missing?


texts: Alter on huge table consumes all disk space

texts:
I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:

alter table hugeTable add column newCol tinyint(1) after existingColumn;

After a few hours of running, I received an error, roughly:

... table 'hugeTable' is full

I was looking around and I didn't see anything that was bad - I still had roughly 294G of disk space left. Looking at the error log, it became apparent what happened - it's generating a #sql file that keeps growing. I kicked it off again to get a feel for how quickly it grows and in just an hour it's over 35G:

-rw-r----- 1 mysql mysql  35G Nov 19 21:53 '#sql-ib1124-819861495.ibd'

and growing quickly.

So the real question is, is there a way around this? I have a backup of this table, so it's OK to risk turning this off and running the update with no undo, #sql, etc files. OR, will I have to use sed to modify the backup file and use that?


texts: Why does an UPDATE to a full-text-indexed column lock the full text index?

texts: I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was an UPDATE <indexed table> SET <indexed column>=<some value>, the other query looks like automatic population of the full-text index. What I don't understand is why the UPDATE needed the lock; as I understand Microsoft's documentation, the index should be populated asynchronously, not as part of the UPDATE query. Can anyone clear up why this lock was necessary?


texts: Read CSV and perform SQL in SQL Server Management Studio

texts:
Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line?

Example

data.csv

"column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"

I want to run a SQL update based on the data in each line such as

UPDATE [data].[orders] set order_number = $column1, something_else = $column2 where order = $column3

Is this possible to do in SQL Server Management Studio?


texts: Column with timestamp datatype not having proper value

texts:
I am using PostgreSQL v9.5
A column utime with datatype timestamp without timezone is not having proper values for some rows as given below:
select utime from mytable where utime is not null order by utime desc limit 10;

    202020-02-12 00:00:00
    25019-10-10 00:00:00
    22019-09-20 00:00:00
    22019-09-20 00:00:00
    22019-09-20 00:00:00
    22019-09-20 00:00:00
    22019-09-10 00:00:00
    20188-07-09 00:00:00
    20120-04-01 00:00:00

I am not able to figure out where is the problem because 99% of the rows are having proper timestamp for this column.


texts: Concern on Active Directory Users on Azure SQL Server

texts:
I was able to add Azure Active Directory Users to Azure Sql server. My question is not related in including or removing an Azure AD user, but its existence after the user ceases himself/herself.

My concern here is, will Azure SQL automatically remove the user from the DB and its grants or will the user be disabled?

Thank You,


texts: slow wal replay in backup postgres12 server

texts:
We have the following PG12 setup with Master, replica and readonly replica with archive.

master and replica both has 2cpu, 4gb ram
readonly replica with archive has 2 cpu, 2gb ram

below you can see that the db-app-2 and db-app-backup are both writing and flushing the WAL at the same time. But when it comes to WAL replay db-app-backup is far behind and the replay lag increases as the time goes.

CPU 69.2% RAM 34.0% HDD 61%

 application_name | client_addr  |   state   |   sent_lsn   |  write_lsn   |    write_lag    |  flush_lsn   |    flush_lag    |  replay_lsn  |   replay_lag    | sent_lag | write_lag | flush_lag | replay_lag
------------------+--------------+-----------+--------------+--------------+-----------------+--------------+-----------------+--------------+-----------------+----------+-----------+-----------+------------
 db-app-2         |   x.y.z.a    | streaming | 277/B8400000 | 277/B8400000 | 00:00:00.011132 | 277/B8400000 | 00:00:00.013937 | 277/B82EB168 | 00:00:00.213115 | 0 bytes  | 0 bytes   | 0 bytes   | 1108 kB
 db-app-backup    |   x.y.z.b    | streaming | 277/B8400000 | 277/B8400000 | 00:00:00.01765  | 277/B8400000 | 00:00:00.025976 | 274/C8DBC6E0 | 00:15:42.462623 | 0 bytes  | 0 bytes   | 0 bytes   | 12 GB

Any help in identifying the bottleneck for wal replay is appreciated.

Note:
The below query is used in master to fetch the data above

SELECT application_name, client_addr, state, sent_lsn, write_lsn, write_lag, flush_lsn, flush_lag, replay_lsn, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           sent_lsn
       )) AS sent_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           write_lsn
       )) AS write_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           flush_lsn
       )) AS flush_lag,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(),
           replay_lsn
       )) AS replay_lag
  FROM pg_stat_replication;


texts: Difference between "Table-Incremental" and "Table-Update" for "Population Type" when scheduling Full Text Index Population

texts:
I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time
as the users are complaining about the data they enter would not show up until too long a time has passed.
For this, I right click on the table on which Full Text Index is defined and go to Schedules. It asks to choose the "Population Type". "Table-Incremental" and "Table-Update" are two of the options. I don't want to go with the "Table-Full" option as it might consume a lot of resources.
What is the difference between these two ("Table-Incremental" and "Table-Update") options?
Thank you.


texts:
We're running a huge MongoDB with a large index (made of of four fields) which was created ad-hoc and should now be superseded by slimmer one (of the first two fields). But the problem is we can't just delete the old one as the warmup of the new one may take hours (or days!). We have two secondary instances, is it possible to only delete the large index on one of those and have that instance take over when the new index is warmed? Or is there some way to guide Mongo to the new indices?
Edit: on a small test database Mongo rejects the new index after I delete the old one and instead uses half of third index, even though the new one is a perfect fit for the query. How to guide/update the query plan?


texts: Login failed for user 'domain\Administrator'. (Microsoft SQL Server, Error: 18456)

texts:
On my newly installed SQL Server 2014, I get the following error when trying to connect to the SQL Server:

2015-05-13 12:09:38.40 Logon   Error: 18456, Severity: 14, State: 5.
2015-05-13 12:09:38.40 Logon   Login failed for user 'INTRA\Administrator'.
Reason: could not find a login matching the name provided. [CLIENT: <local machine>]

I reinstalled the sql server three times.

The user for which the login fails is the AD user with which I am logged into the server itself. When logging in via SSMS, I don't have to give any credentials (it is auto-filled), so this can't be a typo error.

How can I resolve this issue?

PS: I also had problems with the SSL cert which are already resolved, so the underlying problem may be bigger.


texts: MySQL: how to implement a text search without delimiter

texts:
I have a table table1 that contains several fields, some textual and other non-textual, on which I do search queries. Let's focus on some text fields, which I simply call field1, field2, field3.
The table contains about 1.5 million records and the queries are not so fast, because when I have to search on these fields, I'm doing in fullscan mode.

I give you a concrete example:

SELECT *
FROM table1
WHERE [some conditions]
AND (field1 LIKE "%str1%" OR field2 LIKE "%str1%" or field3 LIKE "%str1%")
AND (field1 LIKE "%str2%" OR field2 LIKE "%str2%" or field3 LIKE "%str2%")
AND (field3 LIKE "%str3%")

Point out that str1, str2, etc ... can be a word or a part of a word (for example "Table" or "Tabl").

Considering how the query is build, I don't see any sense to define any index on field1, field2, field3: they would not bring any advantage, just an useless waste of resources in my opionion.

So, I have evaluated the FULLTEXT indexes of MySQL and the query MATCH() AGAINST(): this is not good, because in my case it works only when exactly field1=word1 or field2=word2, while often the fields contain concatenated words without delimiters (for example field1 contains the value "HOUSETABLERAIN" and not "HOUSE TABLE RAIN").

What can I do to improve the execution time? My question includes changes to the query and the edit of data structure as well.

I thought two solutions, but I'm not convinced of either.

Solution A)
Introduce another table table2 (table1_id INT, field VARCHAR, string VARCHAR); for each record in table1, insert all possible substrings (confining itself to combinations with at least 4 characters).
Practical example:

If in table1 there is the record

id | field1
45 | HOUSETABLERAIN

then in table2 there will be

table1_id | field | string
       45 | field1 | HOUS
       45 | field1 | HOUSE
       45 | field1 | HOUSET
       45 | field1 | HOUSETA
       45 | field1 | HOUSETAB
       45 | field1 | HOUSETABL
       45 | field1 | HOUSETABLE
       45 | field1 | HOUSETABLER
       45 | field1 | HOUSETABLERA
       45 | field1 | HOUSETABLERAI
       45 | field1 | HOUSETABLERAIN
       45 | field1 | OUSE
       45 | field1 | OUSET
       45 | field1 | OUSETA
...

Afterwards I could define an index on table2.string and I should have some advantages; furthermore, I could also eliminate a part of the fields in table1, because I would no longer search on them.

But I fear it's madness ... even before testing it.

Solution B)
Add a TEXT field for the search and enter all possible combinations separated by the blank space " ", then define a FULLTEXT index. The problem are the stopwords that are not considered by the MATCH AGAINST query.

I'd like to have some opinions and maybe some better ideas.
Thank you.


texts:
What is the best practice for Data Obfuscation in SQL Server?

We'd like to use masked Production data in our UAT system.

If we want to do it quickly, and with a higher level of obsfucation, what approach should be taken?  I'm thinking about Character scrambling for people's given name and family name, but how?  Should I create a function myself or there is any predefined functions available to use?  I don't want to spend time re-inventing the wheel :)

How about for date fields?  For example should date of birth be randomly picked from the whole table and assigned to a record, or there is a better way of doing that?


texts: Master-Master Replication: Missing PK Values

texts:
I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence values

1
2
3
5
6
8

its missing 4 & 7 because its replicate latest auto increment value from one server to another server so its skip the numbers. Is there any workaround through which we can maintain this sequence

Server A:
auto-increment-increment=2
auto-increment-offset = 1

Server B:
auto-increment-increment=2
auto-increment-offset = 2


texts: SSIS 14 - Buffer Memory - Good practices

texts:
I've a some packages in SSIS 14 in order to extract the data from a lot of operational databases and load into my Data Warehouse.

I've the following virtual machine:

Windows Server R2 Standard
RAM: 24 GB Processor: 4 processors & 2.52
GHZ OS: 64-bit Operating System

I've one package for each operation database and each packages extract in average 3 million records by day...

I am getting very worried and desperate because I can not load without getting the following error (or similar):

  Error: The system reports 55 percent memory load. There are 25769267200 bytes of physical memory with 11377299456 bytes free. There are 4294836224 bytes of virtual memory with 1408303104 bytes free. The paging file has 71665074176 bytes with 57322270720 bytes free.

In order to avoid this error I already do:

Add more drives to the machine (one for TempDB and another for Logs)
Changes the SSIS paths to the new drives
Try to insert only one day
by execution

But I'm always getting the same error... This is one of my data flow:

I'm getting desperate because my customers are asking about the data and I can't create a good solution using SSIS...

What can I do in order to solve this problem?


texts: Best way to create dynamic table for search

texts:
There is a table of documents:

CREATE TABLE `docs` (
  `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_uuid` binary(16) NOT NULL,
  `sender_uuid` varchar(40) NOT NULL,
  `receiver_uuid` varchar(40) DEFAULT NULL,
  `chain_id` int(10) unsigned NOT NULL,
  `status` tinyint(4) NOT NULL COMMENT 'open,sent,inbox,read,etc',
  `date_created` int(10) unsigned NOT NULL DEFAULT '0',
  `date_changed` int(10) unsigned NOT NULL DEFAULT '0',
  `date_read` int(10) unsigned NOT NULL,
  `doc_date` int(10) unsigned NOT NULL,
  `doc_number` varchar(50) NOT NULL,
  `is_archive` tinyint(1) NOT NULL DEFAULT '0',
  `doc_hash` binary(16) NOT NULL COMMENT 'md5 of document data,
  PRIMARY KEY (`doc_id`),
  ... keys ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

which contains the mandatory fields in the document.

And I have a tables with document "indexes"

CREATE TABLE `docs_idexes` (
  `doc_id` int(10) unsigned NOT NULL,
  `created_from` binary(16) DEFAULT NULL,
  `is_signed` tinyint(1) NOT NULL DEFAULT '0',
  `encrypted` tinyint(1) NOT NULL DEFAULT '0',
  `file_name` varchar(255) DEFAULT NULL,
  `file_date` int(10) unsigned DEFAULT NULL,
  `inv_date` int(10) unsigned DEFAULT NULL,
  `inv_num` varchar(20) DEFAULT NULL,
  `error_code` int(10) unsigned DEFAULT NULL,
  `sub_doc_type_id` smallint(5) unsigned DEFAULT NULL,
  `sub_status_id` smallint(5) unsigned DEFAULT NULL,
  `action` tinyint(3) unsigned DEFAULT NULL,
  `buyer_uuid` varchar(40) DEFAULT NULL,
  `consignee_uuid` varchar(100) DEFAULT NULL,
  `consignor_uuid` varchar(40) DEFAULT NULL,
  `customer_uuid` varchar(40) DEFAULT NULL,
  `performer_uuid` varchar(40) DEFAULT NULL,
  `supplier_uuid` varchar(40) DEFAULT NULL,
  `delivery_place_uuid` varchar(40) DEFAULT NULL,
  `contract_date` date DEFAULT NULL,
  `contract_number` varchar(35) DEFAULT NULL,
  `delivery_date` int(10) unsigned DEFAULT NULL,
  `order_number` varchar(35) DEFAULT NULL,
  `order_date` int(10) unsigned DEFAULT NULL,
  `return_date` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`doc_id`),
  ... keys ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The problem is that the indexes will be added every month. And run ALTER TABLE is bad idea.

What is best way to create a database architecture without ALTER TABLE when I add a new index?


texts: How can I change the location of the log file in PostgreSQL? Can it be done without stopping the cluster?


texts: How to MySQLDump with For Channel to set up multi-source replication?

texts:
I'm trying to set up multi-source replication.

But, when I try and import the second database I get the error:

ERROR 3079 (HY000) at line 28: Multiple channels exist on the slave. Please provide channel name as an argument.

How can I supply the channel name to mysqldump

I run the command:

mysqldump -h1.2.3.4 -P3306 -umysqldumpuser -ppassword --dump-slave --apply-slave-statements --include-master-host-port --single-transaction --databases db_name -E -R -q -c --max_allowed_packet=2G | mysql -S/var/run/mysqld/mysqld.sock -umysqldumpuser -ppassword

The first dump is fine, as I can then run:

RESET SLAVE ALL FOR CHANNEL "";

CHANGE MASTER TO
. . .
FOR CHANNEL '<channel_name>;

So it is now replicating over a channel. But I can't dump the second one as it gives me the above error.

The only way I can see this working, would be to dump the first one, stop replication, make a note of the logfile and pos, and then wipe the slave connection, leaving replication to the first database off while I dump the second one.

Only then can I start replication to both databases at the same time.

This is highly impractical, especially if you are looking to combine connections from numerous locations.

I have searched the documents, but can't find any reference to using mysqldump and channels.


texts: Backup detects corruption, but CHECKDB does not

texts:
I have a database where when I run the backup command

BACKUP DATABASE [MyDatabase] TO
DISK =  'G:\Backup\MyDatabase_01_01_2018.bak'
WITH    NOFORMAT, NOSKIP, COMPRESSION, INIT, BUFFERCOUNT = 100

I get the error message

  Msg 3043, Level 16, State 1, Line 8
  BACKUP 'MyDatabase' detected an error on page (1:745345) in file 'F:\Data\MyDatabase_1.ndf'.
  Msg 3013, Level 16, State 1, Line 8
  BACKUP DATABASE is terminating abnormally.

I ran a full CHECKDB but it comes back clean.  I did notice that the Page Verify option had been set to NONE (not my doing) so I changed it to CHECKSUM and rebuilt all the indexes in the DB to get it to write to all pages and generate checksums. After this the backup still fails and the checkdb still shows clean (so no change).

DBCC CHECKDB('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS,
DATA_PURITY, EXTENDED_LOGICAL_CHECKS;

from the SQL log:

  DBCC CHECKDB (MyDatabase) WITH all_errormsgs, no_infomsgs, data_purity executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 21 minutes 46 seconds.  Internal database snapshot has split point LSN = 000ab776:0000112f:0001 and first LSN = 000ab776:0000112d:0001.

I ran DBCC PAGE but it errors (doesn't even seem to be returning the right page in the first place).  I CAN run it with print option 2 and it returns but honestly I don't know what I'm looking for there.

DBCC PAGE ('MyDatabase',1,745345,3)

PAGE: (3:513793)

BUFFER:

BUF @0x00000003811F8280

bpage = 0x00000000F2D70000          bhash = 0x0000000000000000          bpageno = (1:745345)
bdbid = 5                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 44283                       bstat = 0x809
blog = 0x5adb215a                   bnext = 0x0000000000000000

PAGE HEADER:

Page @0x00000000F2D70000

m_pageId = (3:513793)               m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x4                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 1075937538                                 m_indexId (AllocUnitId.idInd) = 2
Metadata: AllocUnitId = 633462595911680                                  Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (3:513795)
m_nextPage = (3:513820)             pminlen = 17                        m_slotCnt = 426
m_freeCnt = 2                       m_freeData = 7338                   m_reservedCnt = 0
m_lsn = (608841:643611:411)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:511232) = ALLOCATED          SGAM (1:511233) = NOT ALLOCATED
PFS (1:744096) = 0x40 ALLOCATED   0_PCT_FULL                             DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED

  Msg 2514, Level 16, State 8, Line 20
  A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

Any ideas what I could try next?  Server version is

select @@version

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)
    Feb 21 2018 12:19:47
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Compatibility level of the DB is 100 (SQL 2008).


texts: Change default collation when provisioning a SQL Server VM in the Azure portal

texts:
Is there a way to change the default SQL collation directly from the "Create virtual machine" wizard in Azure ? The SQL VM images from the gallery are set for SQL_Latin1_General_CP1_CI_AS.

Browsing through the wizard's SQL Server settings, there's nothing on collation. I've also searched through the parameters (listed below) in the generated templates, but again nothing on collation. Am I missing some hidden way of doing this ? Using SQL 2016 right now, but seems to be the same for other versions.

    "sqlConnectivityType": {
        "type": "string"
    },
    "sqlPortNumber": {
        "type": "int"
    },
    "sqlStorageDisksCount": {
        "type": "int"
    },
    "sqlStorageWorkloadType": {
        "type": "string"
    },
    "sqlStorageDisksConfigurationType": {
        "type": "string"
    },
    "sqlStorageStartingDeviceId": {
        "type": "int"
    },
    "sqlStorageDeploymentToken": {
        "type": "int"
    },
    "sqlAutopatchingDayOfWeek": {
        "type": "string"
    },
    "sqlAutopatchingStartHour": {
        "type": "string"
    },
    "sqlAutopatchingWindowDuration": {
        "type": "string"
    },

Later edit October 2018: Checked again and still no collation options in the wizard or template.


texts: Connection to database causes SSPI context error

texts:
I have migrated a SQL server from another server box that was decommissioned. They have the same name and also same IP address. However, when I connect to the server from an application using trusted connection, I get the "SSPI context not generated" error.
What I have done:

I have used the setspn -X to confirm there is no duplicate SPN.

I have changed the order of protocol to follow the order:
Shared Memory,
Named Pipes,
TCP/IP.

I have verified that when I restart SQL server, the service registers and deregisters. This was found in the SQL server log.

I have checked that on the SQL configuration the TCP/IP network protocol has the right IP and is active and enabled on both 32bit and 64bit.

I am running out of ideas and I am still getting the same error. I can't find any log that point to Kerberos.


texts: How do I select arrays that are not empty?

texts:
Why is this so tricky, what is token set to that it isn't equal to null nor an empty string?

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword';

 lexemes
---------
 {}
 {}
 {}
 {titl}
(4 rows)

Ok.. So I want to get rid of {},

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword'
  AND lexemes <> '{}'
  AND lexemes <> ARRAY[]::text[]
  AND lexemes IS NOT NULL
  AND lexemes <> ARRAY[' ']
  AND lexemes <> ARRAY[null]::text[];

I knew most of these wouldn't work., but I'm totally confused why <> '{}' wouldn't work not <> ARRAY[]::text;. How do I filter this out?


texts: Does separating VARCHAR values based on size have a performance gain?

texts:
I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ranges of varchar.

EX:

table_1 - up to varchar(10)
table_2 - varchar(11) TO varchar(500)
table_3 - varchar(501) TO varchar(MAX)

I have always been under the impression that varchar was only going to use the size that it needed.

Do you know if this is going to have any gains in performance and would it be worth the extra coding / logic that would be needed?


texts: Question on best practice - keep empty partitions at both ends of your table?

texts:
I was reading ALTER PARTITION FUNCTION best practices which says -

  Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

Questions :

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?
I think certain optimizations are done if the partition is empty, but
is it correct to say that the major cost is in the data movement
itself?
Is there anything new in SQL 2014 that could help out (we are moving from 2008 to 2014 .. yes I am aware that SQL Server 2019 is out ..but we will eventually get there) ?

I saw a lot on Managed Lock Priority (e.g. https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/); however, that seems to apply only to Table Partition Switch and Online Index Rebuild, but not to an ALTER PARTITITON FUNCTION.


texts: Inconsistency between maxbytes & bytes. Oracle

texts:
Query:
Select ROUND (SUM (d.bytes) / (1024 * 1024 * 1024)) AS size1,
ROUND (SUM (d.maxbytes) / (1024 * 1024 * 1024)) AS size2
From dba_data_files d where tablespace_name = 'TABLESPACE_NAME'

Output:

size1: 70, size2: 64

As you can see the max bytes allowed for the data files for a particular table-space is only 64GB however it has somehow extended to 70GB!!
Scenario: At a given point the table-space got completely filled (64GB) and I saw 100% used in one of my routine checks. So I went ahead and temporarily truncated some unwanted tables to free up some space. I didn't encounter the cannot extend error anywhere in any of my processes (although it might have occurred) but what bugs me is that the total size of the table-space just increased from 64GB(max) where it should have stopped to 70(gb).
Please advise how it could have happened or any option I have missed !! and where is the extra 6 GB being stored.
Thanks in advance.
Edit:
Output of :
select bytes,maxbytes,autoextensible
  from dba_data_files
 where tablespace_name = 'TABLESPACE_NAME';

15728640000 4294967296  YES

30303846400 32212254720 YES

28626124800 32212254720 YES


texts: Adding the domain account to a security group on the SQL Server computer that has sufficient privileges to log on as a service

texts:
After reading this article,

http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000173

I have some problems configuring point 2) and 3):

2) Create a SQL Server login that has the ability to backup (and restore) databases (y) by adding it to the SYSADMIN server role.

3) Add the domain account (x) to a security group on the SQL Server computer that has sufficient privileges to log on as a service, etc.

Where can I find detailed instructions/videos on how to accomplish them?

Thanks.


texts:
I'm in the process of moving a large (7GB-9GB) database to a hosted server. the old server uses SQL 2008 R2 and the new one is using SQL 2016. I'm trying to find the best way to reduce the downtime for this migration. I've looked into mirroring and replications and don't think either really fits the situation. I'm hoping I'm wrong and there's something I can use that I'm just not looking at properly.

Is it possible to set up a mirror or something in this manner and then once I drop the primary just disable it?

I have full access to both servers but may need to have firewall ports opened if needed.


texts: what server role that needs to be configured for the user to enable sql server agent

texts:
I'm on SQL SERVER 2008,

I want to enable the SQL Agent for a particular user, who has only read access to the database. I don't want to give sysadmin role?

Is there any way, I can do this?


texts: how to display running count of records in query

texts:
I am working on SQL SERVER 2012, and I have been trying to figure out how to include a running count of records in a group by statement. For example, the query would look something like:
SELECT CUSTOMER_ID, TRANSACTION_ID, COUNT
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID
And the output would look something like:
10   100   1
10   108   2
10   125   3
10   140   4
12   102   1
12   103   2
12   138   3
15   135   1
I appreciate any advice on how to query this information.


texts:
I have an availability group on SQL Server 2016 SP1 CU5 on Windows 2012 R2.
It works fine, but I need to add an IP address on another network.

So I added the IP:

ALTER AVAILABILITY GROUP [AG_MOON]
MODIFY LISTENER N'AGLISTMOON'
(ADD IP (N'10.20.2.102', N'255.255.255.0')
);

The command runs successfully. Then I query the view: sys.availability_group_listener_ip_addresses:

SELECT * FROM sys.availability_group_listener_ip_addresses;

I can see both addresses, but the old address is in state 1 (ONLINE). The new address is OFFLINE.

There are no errors or other indications of a problem in the error log.

How can I set it ONLINE?


texts: SQL Server Snapshot Replication - Is there an option to clean up the snapshot folder after X amount of time?

texts:
We're running snapshots from a 2016 to a 2019 SQL Server.
I've looked through Microsoft's documentation and I'm not seeing a clear cut way to have this, and I'm not seeing any jobs on the Publisher/Subscriber that would run this sort of cleanup.
We recently had a drive fill up and I want to make sure to get this sorted out if it's a native feature of replication, otherwise I'm going to run a PowerShell script to take care of it.


texts: Multitenant application: several tables or several IDs?

texts:
We are designing a common product to serve several companies. The most convenient way to do this, technically speaking, is to deliver a database to each customer.
But we predict that, depending on the customer size, deliver a database to each customer can be prohibitive (because of the cost of a cloud-based database, as we do not work with local databases), but we would like to serve the smaller companies too. So we are discussing the use of just one database to several customers.
If we decides to do so, what would be the best approach?

Apply a "master identifier" to all tables.
All tables will have a column that we call, lets say "customer_id". So, each register recorded in the database will carry the customer ID, and we can differ between what belongs to which customer.

Prefix tables.
All tables will be replicated to each customer (in the same database), in which each one will receive a prefix, pointing to what customer the table belongs. In the backend, easily we add the "prefix" feature to differ between customers.

Of course, each approach can be applied to reach the same objective, but we would like to know "the best approach". What are the implications to each approach, covering questions like performance, scalability, development complexity, easiness of administration, etc.?


texts:
We had a user change a password on an account that was on an automation equipment that pinged the sql server many times a second (for no apparent reason - the pinging).

In the end my errorlog ballooned out to >6gb, and my hard drive isn't large enough to accomodate that.

I've cycled it, but it's still at the 6gb.

Is there a way to reduce that?

Thanks


texts: Cannot install DB2 Express-C 10.1 on Mac OS X 10.7.5

texts:
I'm having troubles seting up DB2 Express-C 10.1 on my Mac OS X (v10.7.5). My initial install attempt failed, but then I found

https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014927797

That seemed to work up to the point where I tried to start the db:

$ ./db2start
dyld: Library not loaded: /db2unix/db2galileo/db2_galileo_darwinport/darwin64/s120905/engn/lib/libdb2e.dylib
Referenced from: /Users/home/sqllib/adm/./db2start
    Reason: image not foundTrace/BPT trap: 5
$

OTOH, if I try to run db2setup from the expc directory I get and error message that others have seen. Something like

/Applications/expc/db2/macos/install/db2setup: line 606:
/tmp/db2.tmp.22412/db2/macos/install/../java/jre/bin/java: No such file or  directory

Where the 5-digit number in the tmp directory name changes on each run.

That is followed by a nice DB2 10.1 intro screen which is suddenly replaced by a blank DB2JDKTester window that says "DB2 SETUP LAUNCHPAD" at the top.

Has anyone else tried to install DB2 on Mac OS X 10.7?

(And, yes, I've seen the other thread with the same title, but that was never actually answered.)


texts: How to identify consonant/vowel pattern matches in MySQL?

texts:
Evening all,
I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my thinking is something structured along these lines with about 300k rows:

CREATE TABLE words(
 `value` VARCHAR(100),
 `pattern` VARCHAR(100)
);

To populate a small fraction of the contents:

INSERT INTO words(`value`) VALUES
  ('aardvark'),
  ('aardvarks'),
  ('aardwolf'),
  ('aardwolves'),
  #...
  ('mortal'),
  ('mortals'),
  #...
  ('posted'),
  ('posteen'),
  #...
  ('zymotoxic'),
  ('zymurgies');

The following query should return "mortal" and "posted":

SELECT `value` FROM words WHERE `pattern` = '101101';

because '1' represents a consonant and '0' a vowel. I'm aware that MySQL doesn't have a regexp_replace equivalent, so I'm wondering what's the best way to populate the pattern field? I'm assuming it's not the deplorably slow:

UPDATE words SET `pattern` = REPLACE(`value`, 'a', '0');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'b', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'c', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'd', '1');
UPDATE words SET `pattern` = REPLACE(`pattern`, 'e', '0');
#...
UPDATE words SET `pattern` = REPLACE(`pattern`, 'z', '1');

Currently trying a stored procedure with a cursor looping through the words table and updating each pattern one by one but, perhaps unsurprisingly, that's no quicker. Am I missing something really obvious?

Thanks in advance for any help you're able to give me.

Edit: added the stored procedure approach. This is quicker but not by much (0.7 seconds per thousand rows, which adds up) but it gets the job done.

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `set_word_pattern`()
BEGIN
  DECLARE l_last_row INT DEFAULT 0;
  DECLARE temp_word VARCHAR(100);
  #DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE c_traverse_words CURSOR FOR SELECT `value` FROM words WHERE pattern = "" LIMIT 20000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row = 1;

  OPEN c_traverse_words;
  cursor_loop: LOOP
    FETCH c_traverse_words INTO temp_word;
    IF l_last_row = 1 THEN
      LEAVE cursor_loop;
    END IF;
    SET temp_pattern_word = get_pattern(temp_word);
    UPDATE words SET `pattern` = temp_pattern_word WHERE `value` = temp_word;
  END LOOP cursor_loop;
  CLOSE c_traverse_words;

  SELECT "Done"; #temp_pattern_word;
END

and the function it calls:

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `get_pattern`(new_word VARCHAR(100)) RETURNS varchar(100) CHARSET utf8
BEGIN
  DECLARE temp_char CHAR(1);
  DECLARE temp_pattern_word VARCHAR(100);

  DECLARE i INT;

  SET temp_pattern_word = "";
  SET i = 1;

  WHILE(i <= CHAR_LENGTH(new_word)) DO
    SET temp_char = MID(new_word, i, 1);

    IF temp_char IN ("a", "e", "i", "o", "u") THEN
      SET temp_pattern_word = CONCAT(temp_pattern_word, "0");
    ELSE
      SET temp_pattern_word = CONCAT(temp_pattern_word, "1");
    END IF;
    SET i = i + 1;
  END WHILE;
RETURN temp_pattern_word;
END


texts: Pulling a username from table one to update credits on table 2 under the same username

texts: I currently have 2 tables, one being a table that stores votes, and the other being the members table.  I want SQL to pull the username from the "votes" table (as this keeps track of who voted), then add +1 "credits" in the table "members".  Along with this, it will have to change the spaces to underscore and remove capital letters if they are used. Does anyone have an idea on how to do this?


texts: What are the compelling resons to update SQL Server name to a real one after its changing?

texts:
Took the job of monitoring and tuning a few dozens SQL Server 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. running inside virtual machines.

All of them on running

select @@servername
select * from sys.servers

return  the same machine autogenerated during Windows setup name WIN-MIQD9ME78FK.

These production servers works over 2 (probably much more) years.

As far as I can guess the hosts' name were changed after installing SQL Server.

What are the compelling reasons for updating SQL Servers name for its real host names?

And which surprises should I be ready for after such changes?


texts: In SQL Server Configuration Manager on the Advanced tab in TCP/IP Protocol properties box there is an option Accepted NTLM SPNs. What is the use of that option and how it can be used?


texts: Continuously Adding SSDs to an existing PostgreSQL Server

texts:
An Ubuntu 18.04 server dedicated to running a PostgreSQL 11.2 server (with TimescaleDB extension) will be running out of disk space soon so new SSD disks will need to be added to the machine to support the growing database size.

The data is expected to continue increasing at the same/higher rate, so the storage hardware will need to be increased continuously until the machine runs out of 2.5" drive bays. Only then will distributing the database over multiple machines be considered, due to the increased complexity involved.

Thoughts

Union file systems like mergerfs can pool the drives together, solving the storage expansion problem easily. But this will increase the latency of database operations and are thus not recommended. Redundancy can be added by having an underlying RAID-1/5/6/10 or using SnapRAID.
RAID-0 and RAID-10 allows expansion of the RAID array into the newly added drive, with the bonus of increased performance from striping. However every drive being added is an additional point of failure. Furthermore, numerous people claims that mirroring SSDs is of limited use as both SSDs in RAID-0 will likely fail at the same time. So maybe this means that RAID-10 is no better than RAID-0. Furthermore, failure rates increase linearly with each additional SSD added.
RAID-5/6 reduce performance due to parity calculations and having write to 2 drives, reducing the effective IOPS by 75%. Seems to be a poor choice for databases.
PostgreSQL TABLESPACES can be used to assign every table to a specific drive. However, using tablespaces will make recovery very complicated. Furthermore, is it possible to create new tablespaces on the new drives and let Postgres automatically decide where to write new record to?
ZFS, BTRFS? Not familiar with them, willing to explore if they are suitable.

Question: What is the year-2020 recommended method of expanding the storage of a PostgreSQL machine, if expansion has to be done frequently (1-2X a year), performance should not be affected much, and recovery should not be so complicated that it may lead to data loss?

RAID-10 seems like a good idea to me, except that RAID-1 appears to have limited usage while causing the 'loss' of half the disk space, made worse by the increasing points of failure with increasing number of drives.

Due to budget constraints, we cannot fill up all 16 drive bays in a 2U chassis with SSDs at once, so it has to be done incrementally.

Any advice is greatly appreciated!

EDIT: After looking into ZFS, it seems like this might be one of the solutions for my case.

A ZFS pool with only mirrored ZFS vdevs (2 drives per vdev) will allow storage pool expansion by adding 2 drives at a time
Keeping 1-2 hot spares in the ZFS pool can allow automatic failover by ZFS when 1 of the drives fail
With mirrored vdevs, the rebuild time after a drive has failed will be way faster than with RAIDZ vdevs. This also reduces the chance that the surviving drive used for the rebuild can fail during the process. The degraded mirrored vdev will have much greater performance than a degraded RAIDZ vdev during rebuild
ZFS supports inline data compression which helps significantly (4X compression) with reducing the storage requirements of TimescaleDB data without having to use native TimescaleDB compression that prevents compressed data from being updated unless it is uncompressed. This is pretty important too as TimescaleDB is known to have poorer data compression compared to other databases like InfluexDB
Monitor and replace the SSDs when they are about to fail, as suggested by jjanes

If I understand this correctly, using ZFS mirrored vdevs will check all my boxes: Continuously add drives, allow drive pooling which provides a single mountpoint, data redundancy and a bonus ~4X data compression.


texts: `ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend

texts:
I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave replica of the database and I wanted to take the advantages of innodb_file_per_table during the process. So I set innodb_file_per_table in my slave configuration and imported the dump of the database. To my surprise, it failed with

  ERROR 1114 (HY000) at line 5602: The table 'Sample' is full

The file Sample.ibd is currently about 93GB, with more than 600GB free space available on the partition, so it's not a disk free-space issue. Neither it seems to be hitting any kind of file-system limit (I'm using ext4).

I'd be grateful for any ideas what could be the cause, or what to investigate.

Update: I'm using mysql  Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64).

SELECT @@datadir; -- returns `/home/var/lib/mysql/`
SHOW VARIABLES LIKE '%innodb_data_file_path%'; -- ibdata1:10M:autoextend

df -h /home/var/lib/mysql/
768G   31G  699G   5% /home


texts: Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

texts:
Maybe I am missing something here:

CREATE TABLE public.example_table (
    id integer UNIQUE
);

CREATE TABLE public.foreign_table (
    id integer,
    example_table_id integer,
    CONSTRAINT fk_example_table_id
    FOREIGN KEY (example_table_id)
    REFERENCES public.example_table (id)
    ON DELETE SET NULL
);

INSERT INTO public.example_table (id) VALUES
    (1);

INSERT INTO public.foreign_table (id, example_table_id) VALUES
    (1, 1),
    (2, null);

If I run TRUNCATE CASCADE, both tables are wiped which is not what I expected would happen.

TRUNCATE example_table CASCADE;

SELECT COUNT(*) FROM public.foreign_table;

0

What I would expect to happen would be that foreign_table would alter to:

(1, null)
(2, null)

Am I not understanding what SET NULL is supposed to accomplish?

Is there a way to use TRUNCATE CASCADE without having it delete from the other table? I use Laravel where I can call Model::truncate(); and it will automatically truncate the table and reset my indexes, I was hoping I could call this on the example_table and have it reset all the rows in foreign_table to null instead of just deleting the whole table.

Thanks for your help.


texts: How to restore mysql table from .frm files

texts: so I'm trying to restore my tables with the *.frm files in my mysql folder. I have tried the method in this answer: Restoring MySQL Tables from .ibd, .frm and mysqllogbin files but I got an error saying [Errno 13] Permission denied: version check after running the mysql utilities. I am stuck here, I have tried other options which have failed as well. Kindly help


texts:
I use SQL Express and my database has the following backup schedule:

Full backup - 24 hours
Differential backups - 6 hours
Transaction log backups - 30 minutes

My question is:

I have checked LSNs of transaction log backups that were made last night and discovered that one of them has the same First LSN and the Last LSN.

Why?
Was it an error?


texts: Create SQL job to send email in Excel format

texts:
I am not skilled in SQL and need some help.  I have a SQL2005 server and I created a job to send an email with a CSV attachment. My job runs fine but I need the end result to be an Excel file in either .xls or .xlsx format.

Here is the code for the job:

USE My_DB
GO

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail'
    ,@recipients =  'me@my_org.com'
    ,@copy_recipients = ''
    ,@execute_query_database = 'My_DB'
    ,@query = 'exec MY_RPT_MonthlyWIPSp'
    --@body =@bodyContent
    ,@subject = 'Monthly WIP Report'
    ,@attach_query_result_as_file = 1
    ,@query_attachment_filename = 'result.csv'
    ,@query_result_separator=',',@query_result_width =32767
    ,@query_result_no_padding=1;

What do I need to change and where to get the data to come out in the format I need?  Do I have to modify my SP?