texts: How to speed up string cleanup function?

texts:
I need to cleanup a string, so that certain ASCII code characters are left out of the string, and others are replaced.

I am new to Postgres. My function ufn_cie_easy() performs way too slow:

DECLARE
  letter char = '';
  str_result TEXT = '';
  x integer;
  y integer;
  asc_code int;
BEGIN
  y:=1;
  x:=char_length(arg);
  LOOP
    letter=substring(arg from y for 1);
    asc_code=ascii(letter);
    IF (asc_code BETWEEN 47 and 58) or (asc_code BETWEEN 65 and 90) or (
        asc_code BETWEEN 97 and 122) THEN
      str_result := str_result || letter;
      ELSIF (asc_code BETWEEN 192 and 197) THEN
      str_result := str_result || 'A';
      ELSIF (asc_code BETWEEN 200 and 203) THEN
      str_result := str_result || 'E';
      ELSIF (asc_code BETWEEN 204 and 207) THEN
      str_result := str_result || 'I';
      ELSIF (asc_code BETWEEN 210 and 214) OR (asc_code=216) THEN
      str_result := str_result || 'O';
      ELSIF (asc_code BETWEEN 217 and 220) THEN
      str_result := str_result || 'U';
      ELSIF (asc_code BETWEEN 224 and 229) THEN
      str_result := str_result || 'a';
      ELSIF (asc_code BETWEEN 232 and 235) THEN
      str_result := str_result || 'e';
      ELSIF (asc_code BETWEEN 236 and 239) THEN
      str_result := str_result || 'i';
      ELSIF (asc_code BETWEEN 242 and 246) OR (asc_code=248) THEN
      str_result := str_result || 'o';
      ELSIF (asc_code BETWEEN 249 and 252) THEN
      str_result := str_result || 'u';
      ELSE
      CASE asc_code
        WHEN 352 THEN str_result := str_result || 'S';
        WHEN 338 THEN str_result := str_result || 'OE';
        WHEN 381 THEN str_result := str_result || 'Z';
        WHEN 353 THEN str_result := str_result || 's';
        WHEN 339 THEN str_result := str_result || 'oe';
        WHEN 382 THEN str_result := str_result || 'z';
        WHEN 162 THEN str_result := str_result || 'c';
        WHEN 198 THEN str_result := str_result || 'AE';
        WHEN 199 THEN str_result := str_result || 'C';
        WHEN 208 THEN str_result := str_result || 'D';
        WHEN 209 THEN str_result := str_result || 'N';
        WHEN 223 THEN str_result := str_result || 'ss';
        WHEN 230 THEN str_result := str_result || 'ae';
        WHEN 231 THEN str_result := str_result || 'c';
        WHEN 241 THEN str_result := str_result || 'n';
        WHEN 376 THEN str_result := str_result || 'Y';
        WHEN 221 THEN str_result := str_result || 'Y';
        WHEN 253 THEN str_result := str_result || 'y';
        WHEN 255 THEN str_result := str_result || 'y';
        ELSE str_result := str_result;
      END CASE;
      END IF;
    y:=y+1;
    exit when  y=x+1;
  END LOOP;
  return str_result;
END;


texts: postgresql: how to define a JSONB column with default value

texts:
I am unable to find in the documentation how to create
a JSONB column in PostgreSQL that has a DEFAULT value of
an empty json document.

How the above can be stated in the CREATE TABLE definition ?


texts: Oracle Full Client / Database Client package locations

texts:
I need to install multiple versions of the Oracle client on an app server for an automation project. The platform I'm looking for in specific is Linux, 64 bit.

Oracle's network of websites are a maze and not helpful at all.

Here is what I've found:

12.1.0.1 : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-1959253.html

11.2.0.1 : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

11.2.0.4 : ??? Who knows, but luckily we have a copy on our repo servers at work.

I need to find the package for 11.2.0.2.

The reason I'm not just using a 12c client for everything (as much as I would like to) is because I can't. This annoying error greets you trying to use expdp a 11.2.0.2 database using the 12c client:

  Export: Release 12.1.0.2.0 - Production on Tue Feb 24 14:14:39 2015

  Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights
  reserved. UDE-00018: Data Pump client is incompatible with database
  version 11.2.0.2.0

So much for backwards compatibility.

P.S., the source database is a production 11.2.0.2 database that will not be upgraded for at least a year.


texts: Fixing a MySQL query that miscounts cases where count is 0

texts:
I have the following SQL code:

SELECT widgets.campaign_name, widgets.enddate, widgets.active,
       widgets.id_widget,COUNT(*) AS entries
  FROM widgets
  LEFT JOIN entries ON widgets.id_widget = entries.id_widget
  WHERE widgets.active IN ( 0 )
    AND id_user = 8
  GROUP BY widgets.campaign_name

It does exactly what I want to do beside one thing. If I have 0 entries on a campaign, it still shows 1 entry. I want to get rid of this problem and make it count exactly how many entries are. If 0, it should display 0.

The code above works fine for 1,2....n entries.


texts:
On a physical Dell server, I am trying to setup a backup job to *.bak file on to failover server.
In the video, DBA Fundamentals - backup 3 - setting up maintenance plans @6:24 backing up to a local drive but need to be on failover server.
I created a file on to another server drive \\mssql\backup. I try to run a backup file on to another physical server and it will not backup file over .bak file to failover. Do you have any recommendations on how to do this?
I need a T-SQL script in my management jobs to create .bak file over to failover server so I do not have to copy and paste .bak file. Used in agent.
I am trying to test the disaster recovery .bak for mcafee epoilcy orchestrator with MSSQL database using the backup file.


texts: Database Schema for Resume/CV and Separate Sections

texts:
I am trying to figure out if I am on the right track here.

resumes
id
user_id

resume_sections
id
name
desc

Sections are those that we normally find in resumes, experience, skills, education and so one.

Thanks in advanced!


texts: Error in attaching mdf file from a crashed server to a new server

texts:
One of our server hosting an important database crashed.

The sysadmin team could only access the files by erasing the password with a tool and then log in to access the .MDF files.

I copied the data to the new server and attempted to attach it, but I get the following error:

  TITLE: Microsoft SQL Server Management Studio

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch.     (Microsoft.SqlServer.ConnectionInfo)

  CREATE FILE encountered operating system error 32 (The process cannot access the file    because it is being used by another process.) while attempting to open or create the       physical file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'. (Microsoft SQL Server, Error: 5123)


texts: Selecting the correct row given multiple input value combinations

texts:
I have a table that stores part numbers for various items like so:

+------+-------------+-------------+-------------+
| Item |    Color    |    Type     | Part Number |
+------+-------------+-------------+-------------+
| Pen  |             |             |         001 |
| Pen  |       red   |    felt     |         002 |
| Pen  |      blue   |    felt     |         003 |
| Pen  |             |    felt     |         004 |
| Pen  |     green   |    felt     |         005 |
| Pen  |             |     gel     |         006 |
| Pen  |     green   |     gel     |         007 |
+------+-------------+-------------+-------------+

I need to get the part numbers for items that were ordered in the past. Is there a way to match the "correct" row with a single query knowing the Item, Color and Type?

Given {Item = Pen, Color = orange, Type = felt} the result should be 004

Given {Item = Pen, Color = blue, Type = felt} the result should be 003

Given {Item = Pen, Color = blue, Type = gel} the result should be 006

I'd like to avoid populating every possible combination into the table if possible.


texts: Postgresql Query getting slow execution time

texts:
I did the explain analyze for this query, it was giving 30ms, but if the data is more I will get an execution expired; Using PostgreSQL 10

For normal execution: https://explain.depesz.com/s/gSPP

For slow execution:  https://explain.depesz.com/s/bQN2

SELECT inventory_histories.*, order_items.order_id as order_id FROM
"inventory_histories" LEFT JOIN order_items ON (order_items.id =
inventory_histories.reference_id AND inventory_histories.reference_type = 4)
WHERE "inventory_histories"."inventory_id" = 1313 AND
(inventory_histories.location_id = 15) ORDER BY inventory_histories.id DESC
LIMIT 10 OFFSET 0;

Indexes:

"inventory_histories_pkey" PRIMARY KEY, btree (id)
"inventory_histories_created_at_index" btree (created_at)
"inventory_histories_inventory_id_index" btree (inventory_id)
"inventory_histories_location_id_index" btree (location_id)


texts:
When I telnet to a MySQL DB, the connection gets dropped quickly. Should I increase the interactive_timeout or the wait_timeout?

(Background: trying to keep SSH port forwarding alive on 3306)


texts:
I intend to use Amazon RDS MySQL with one db.t2.micro instance.

Are there any limitations of max_connections and max_questions ?

I searched on the Internet and I found that there is a default value for max_connections, but I can change it through parameters, is this correct ?

And I didn't find anything about max_questions.

Can I set any value for max_connections and max_questions ? Or I am limited to a certain value ?

Thanks


texts: Optimised query to select document and possible annotations

texts:
I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current user.

Originally I tried this:

SELECT content,select_start,select_end,select_colour
FROM documents
LEFT OUTER JOIN annotations
USING (document_id)
WHERE document_id=<some_document_id_here> AND (user_id=<some_user_id_here> or user_id IS NULL)

But it failed to return a document if another user had added annotations. So I'm now using this:

SELECT content,select_start,select_end,select_colour
FROM documents A
LEFT OUTER JOIN (
    SELECT document_id,select_start,select_end,select_colour
    FROM annotations
    WHERE user_id=<some_user_id_here>
) B
ON A.document_id = B.document_id
WHERE A.document_id=<some_document_id_here>

which works, but the inner select statement could create quite a sizeable temporary table. I'd like to know if there's a better way to achieve this.


texts: Can't understand difference in fulltext results - contains, contains with wildcard, freetext

texts:
I have a table with an fulltext index on the column named Filecontent.
The table has a row where content contains "W 917". For context, the content column on this rows contains much more than just what I'm searching for.
I don't understand why I'm getting different results depending on whether I'm using contains, contains with wildcard or freetext.
Why is CONTAINS without wildcard getting results, but CONTAINS with wildcard doesn't?
-- Searching for "W 917"
-- No match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917*"')

-- Match - CONTAINS
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917"')

-- Match - FREETEXT
SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W 917"')

-- Searching for "W"
-- Match - CONTAINS with wildcard
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W*"')

-- No match - CONTAINS
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W"')

-- No match - FREETEXT
SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W"')
´´´


texts: How to identify all queries in a pending transaction in Oracle?

texts: Let's say I have a pending transaction that shows in select * from v$transaction. Is there a way to identify all queries executed so far in the pending transaction?


texts: Is it possible to setup MySQL replication through ProxySQL?

texts:
We currently have replication working through haproxy so that if a node goes down replication continues with one of the other nodes. We're trying to completely replace haproxy with proxysql. Due to this I'm trying to replicate this functionality in proxysql.

Test:
3 on-prem nodes setup as a Percona Galera cluster.
1 on-prem ProxySQL node pointing to the Percona cluster
1 aws EC2 node with Percona installed.

I setup replication between the EC2 node, and one of the 3 on-prem nodes without issue. I can also connect to the DB through proxysql from the aws node.

When ever I stop the slave, and run the following:

CHANGE MASTER TO
         MASTER_HOST="XX.XXX.XXX.193",
         MASTER_USER="[username]",
         MASTER_PASSWORD="[password]",
         MASTER_AUTO_POSITION = 1,
         MASTER_PORT = 6033;

I get the following error when I run show slave status:

Slave_IO_State: Waiting to reconnect after a failed registration on master
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 1597
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Lost connection to MySQL server during query (Errno: 2013)

I can then swap back to directly connecting between DB nodes and replication works fine. Is the mysql backend of proxysql preventing this from working in the same way it does with haproxy?


texts:
It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware?

The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't mentions anything about virtualization and I have read somewhere that is mostly for dedicated machines.

Let's assume that the VM is more than 64GB in RAM and it's presented a topology of 2 v-sockets with 8 v-cores per socket for a 16 v-core VM.

Is the trace flag of help inside the hypervisor?


texts: Select Column data, from sys.column info

texts:
Either this is impossible, or I can't write a good internet search.

I have a query to get the table and column information for our database:

Select t.name [table], c.name [column]
From sys.tables t
Left join sys.columns c on t.object_id = c.object_id

What I'd like to do is add in something like

(Select AVG(LEN(c.name)) from t.name) [AVG LEN]

I'd obviously build in some checks to only run on varchar for Len, and regular AVG for numerics, but that part would be easy. It is getting SQL to actually treat the names correctly that I'm struggling with.


texts: Is it better to add a data file or better to increase the existing files (MAXSIZE)

texts:
I'm quite familiar with SQL-Server as a developer but not so much in the DBA role.

Currently I'm working on a rather large DB (~7TB, living in 7 files, all equal in size and all within one single file group). The DB is running out of disc space.

The customer is going to increase the space available by 2 TB.

To state some things first:

The system is a fail over cluster
It is - why ever - no option to add a second file group.
I know that using a second file group might offer great benefits... (not wanted by the customer. It's an old system and nobody dares to touch it more than necessary...)

The options I'm thinking of:

I could use ALTER DATABASE with MODIFY FILE to increase the size of the existing files (~1TB each at the moment).
I could use ADD FILE to add one or two additional files similiar to the existing ones.

The questions

Is there anything special to keep in mind with fail over clusters'?
Is there any reason why to prefere one of the options?

remark: The goal is as little impact as possible.


texts: How to Create Table Name That Contains Last Month in Suffix

texts:
I want to create a table with the last month's name as part of the table name.
If the current month is February then I would want the table name to look like this: table_name_jan18

The statement I have so far looks like this:

create table abc_'||'trunc(SYSDATE,'mm')-1';


texts: restoring Postgres dump with tablespaces

texts:
I'm trying to restore a dump done with pg_dumpall > mybak.sql from a server that has 1 table in a different tablespace that the default one.

On the target machine I have recreated the directory structure of the tablespace and it is owned by the postgres user with read/write permissions but still the restore is failing. Any suggestions?

command used to restore is:

psql mydb -f mybak.sql

Thanks


texts:
I have a problem connecting with the database with PHP or with MySQL in Linux . I can only connect to the DB with SQLYog. The error I'm getting is this:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'XX.XX.XX.XX' (110)

What can be the problem? The IP from the computer of the SQLYog and the computer with PHP or MySQL in Linux is the same.

Edit: The MySQL Server is not in the same network where I'm developing. This MySQL server is from another company.


texts: What is the best way to monitor long running queries in SQL Server?

texts: I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database.  Is there an alternative to running the query in SQL Server Management Studio and checking on it every hour or so?  I would like to be emailed or sent a message when it's done, but don't know the best tool for this.


texts: Ola Hallengren DatabaseBackup error on locating Windows directory

texts:
Need help in resolving the issue with backing up a DB using Ola Hallengren's stored procedure to a shared folder.

I have a procedure that is executed at SQL Server startup. It runs Ola Hallengren's DatabaseBackup procedure. The SQL Server service runs under NT Service.

The share has permissions for the machine where SQL runs.

Now, if execute DatabaseBackup manually it goes fine. When it executes automatically at startup it fails with the following error:

  Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 786
  [Batch Start Line 0] The directory \\*** does not exist

If I alter the procedure with an Execute As DBO clause, it fails with the same error.

I also tried to Alter the SP with Execute As Self clause - the same error again.

So the question is:

What should I do to make it work?

The file path is provided as a UNC path (e.g. \\machine2\backup\)
This is SQL Express, so I don't have access to SQL Agent jobs.
Therefore, this is a stored procedure configured to run when the SQL Server instance starts up, not a SQL job set to run when SQL Agent starts.


texts:
This is my database structure:

Users {user_id, username}

Profiles {profile_id, user_id, user_views}

My question is: If I do it the way mentioned above, should I to set user_id as primary key or unique key? and what about profile_id?


texts: MySQL: Improve performance for one row insert into table with unique constraint

texts:
I have a table with ~ 40 mil records, with a unique index on the url column, of type varchar(255).

Now the insert speed is about 30/s, is this expected? how could I improve it?

I can't use bulk insert (insert delayed or more values at a time).

Specs:

Hardware

16GB RAM
Intel i7 3.4 Ghz (8core)
2 standard 7200rpm HDD (1x 1TB + 1x 2TB)

Software

MySQL 5.5 (innodb_version 1.1.8, protocol_version 10, version 5.5.29-0ubuntu0.12.10.1-log, version_compile_machine x86_64, version_compile_os debian-linux-gnu)

Ubuntu 12.10 (debian/linux OS)
my.cnf
I'm using the c++ conector as client

Query usage:

CREATE TABLE IF NOT EXISTS `url_unique`
(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `valid` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY unique_index (`url`)
) ENGINE=MyISAM
  DEFAULT CHARSET=ascii
  DEFAULT COLLATE=ascii_bin
  ;

INSERT IGNORE INTO url_unique (url)
VALUES ('http://domain.com')

IF(affected_rows>0) {
    id = SELECT LAST_INSERT_ID();
    .... do some ohter insert using id
}else {
    id = SELECT id FROM url_unique WHERE url = 'http://domain.com';
    .... do some other insert using id
}

Query statistics:

insert ignore into - 0.035s (duplicate value, no insert, affected_rows=0)

update: 0.0328340 (99.588%)
...
Com_insert 1
Handler_write 1
Key_read_requests 34
Key_reads 8
Key_write_requests 2
Key_writes 1
Questions 1
Table_locks_immediate 1

SELECT - 0.002s

(faster because of the insert, some sort of query cache hit probably)


texts: how to add data compression to a primary key of a table?

texts:
We work with tables with over 300GB of size, a small percentage of them tables use sparse columns
although that doesn't always agree with CPU utilization as this question shows.

For the tables that don't use sparse columns I want to implement data compression - either page or row
depending on the findings of procedure sp_estimate_data_compression_savings
or this script:

T-SQL Script for Estimating Compression Savings

I have a routine that generate the create index scripts that works fine.

when I create this table as an example:

IF OBJECT_ID('[DBO].[t1]') IS NOT NULL
DROP TABLE [DBO].[t1]
GO
CREATE TABLE [DBO].[t1] (
[a]  INT                              NOT NULL,
CONSTRAINT   [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED    ([a] asc))

My create index scripts gives me:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1]
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED (  [a] ASC  )
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Question:

Is there a way to apply a data compression to this table without dropping the primary key?

Basically I want to get this done (note the data_compression=PAGE) without having to drop any constraint first:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1]
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4]
PRIMARY KEY CLUSTERED (  [a] ASC  )
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  ,
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]


texts: What's the difference between stored procedure and function in PostgreSQL 11?

texts:
In PostgreSQL 11 beta1 announcement, it is stated that a major feature of the new version is

SQL Stored Procedures
PostgreSQL 11 introduces SQL stored procedures that allow users to use
embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a
procedure. Procedures can be created using the CREATE PROCEDURE
command and executed using the CALL command.

My questions are,

What's the difference between a procedure created with CREATE PROCEDURE and a traditional function created with CREATE FUNCTION?
From browsing the two documentation page, they seem to be quite similar and can both ,e.g., take parameters and return OUT values. Is transaction behavior the only difference?

I have written many functions in PL/PGSQL. Should I change my functions to the new procedures? (If so, what are the advantages?)


texts: SQL Server not giving back memory - staying at Max Server Memory setting

texts:
I'm the only one working on this server and followed documentation about optimizing SQL memory usage when I saw that SQL was using all of the memory available of the server.

One of the steps I did is to fix the max memory size to 20 Gb (server for test purposes with 32 Gb memory).

After tuning, yesterday, SQL Server was only using 3-4 Gb but today, it has again reached the max memory usage I allocated.

Why is SQL Server keeping all 20 Gb? What will happen when users will begin to use the server if the max memory size is already used?


texts: Setting root password in fresh mysql 5.7 installation


texts: Can I change the profile of oracle sys user?

texts:
Are there side effects of changing the profile of sys to any other than default?
Our monitoring keeps telling us the password of sys has expired. The "Default“ profile has a standard limit of 180 days. Even this does not apply – the password of sys is marked as expired.


texts: Error during table creation : is not valid at this position expecting an identifier

texts:
I keeping getting this error during table creation: Order is not valid at this position expecting an identifier. I look up on the Web but I can't figure out how to fix this. What that error means, and how to fix it?

Create Table Customer
(
CustomerID Int Primary Key,
CustomerName varchar(20) Not Null,
CustomerContactNo varchar (10)Not Null,
CustomerAddress varchar(50)Not Null,
CustomerEmail varchar(25)Not Null
)
ENGINE=INNODB;

Create Table Employee
(
EmployeeID TinyInt Primary Key,
EmployeeName varchar(20) Not Null,
EmployeeContactNo varchar(10)Not Null,
EmployeeEmail varchar(25)Not Null
)
ENGINE=INNODB;

Create Table Order
(
OrderNo Int Primary Key,
Foreign Key(CustomerID) REFERENCES Customer(CustomerID),
Foreign Key(EmployeeID) REFERENCES Employee(EmployeeID)
)ENGINE=INNODB;


texts: How could duplicate entry on unique key happen if only one master is active?

texts:
Say, I we have master-master replication with only one master active at a time.

We have users table:

create table users (
 id int auto_increment,
 email varchar(255),

 primary key id(id),
 unique key email(email)
);

and web application cluster which inserts new record for new users.

Now we got error which stopped replication:

Error 'Duplicate entry 'something@example.com-' for key 'email'' on query. Default database: 'xxx'. Query: 'INSERT INTO users  ...

Question is: is that true that this error in this setup is impossible, and probable reason of error is that we had two masters active at same time?


texts: ts_headline() not highlighting properly for  operator

texts:
ts_headline() does not seem to highlight the input text in a way that respects the <-> operator.

The following query:

SELECT ts_headline('English','This Commercial Bank does not have any Equity in Europe but European Commercial Bank does',
                    phraseto_tsquery('English','European Commercial Bank')::tsquery);

returns the following as output:

This Commercial Bank does not have any Equity in Europe but European Commercial Bank does

This highlights the words Commercial and Bank separately in addition to the phrase European Commercial Bank.

The expected output would be:

This Commercial Bank does not have any Equity in Europe but European Commercial Bank does

Which only highlights European Commercial Bank due to the <-> operators inserted by phraseto_tsquery().

I am using Postgres 10.3 on Linux x64. Any suggestions or solutions to this problem?

I reported a bug.


texts:
I have a LAN in which clients connect to the SQL Server installed on a server with the IP address of 192.168.1.10 (SERVER_A).

I have another SQL Server instance installed on another server with the IP address of 192.168.3.11 (SERVER_B).

The servers are routed to each other so I can connect to one instance by SSMS from the other server.

The problem is I need to connect from the clients in the LAN (192.168.1.0) to the instance installed on SERVER_B.

Is there a way to create an alias instance on SERVER_A which points to the instance installed on SERVER_B? So I can connect to SERVER_A and it redirects me to SERVER_B?

I have an application and I can't change how it talks to the database. So I have to create all the Tables, Views and SPs again on SERVER_A and redirect them to tables, views, and SPs on SERVER_B. I want to use something like aliasing. However the problem is I can connect to the instance locally but not remotely when I use alias instance name.


texts: MariaDB 10.1.18 - Resetting Auto Increment Column After Reboot

texts:
I have 2 live MySQL servers, one running on Linux and another running on Windows.

I have recently setup another MySQL environment on Windows however I am getting this error when inserting data into any of the databases's tables...

Failed to read auto-increment value from storage engine

From what I am reading this is because the MySQL auto increment field is reset after rebooting the server, which it does on this machine. However I am not getting this on my production boxes either Linux or Windows, which are the same version, with many reboots.

Any ideas how to fix this?

CREATE TABLE `images` (
  `imageId` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `imageUrl` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `imageFileName` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `imageComments` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `imageAddedOn` datetime DEFAULT NULL,
  `imageStatus` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`imageId`),
  UNIQUE KEY `image_UNIQUE` (`imageId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: Is there any difference with specifying the primary key as an include column in a nonclustered index?

texts:
Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as an included column when creating a nonclustered index?

Side question, why does the nonclustered index default to storing the primary key and not the clustered index fields to do a key lookup on the table?...In the cases where the primary key is not the clustered index, isn't it slower for the key lookup to occur whereas if it stored the clustered index it could do the lookup that way?


texts: SQL Insert multiple values with UNIQUE constraint

texts:
I have a MS SQL Server 2012 and the following table:

CREATE TABLE dzp.contractid (
id bigint PRIMARY KEY IDENTITY(1,1),
VNR char(18) NOT NULL,
CONSTRAINT UC_VNR UNIQUE (VNR))

For context, VNR represents a unique contract number in string form.

I want to import a csv file with contract information, with possibly multiple rows per contract. To avoid querying over a char(18), I am trying to normalize the data upon loading using the INSERT-Trigger of the staging table.

Flow is:

csv-file --> staging table --> insert trigger --> normalized tables

In my trigger, I am attempting the following:

BEGIN TRY
    INSERT INTO dzp.contractid(VNR)
    SELECT VNR
    FROM dzp.accounts_stage
END TRY

BEGIN CATCH
-- ignore unique constraint violation error, raise otherwise
IF ERROR_NUMBER() <> 2627
    RAISERROR ('blah', 16, 1)
END CATCH

So what I hope to achieve is basically:

Get all the contract numbers from the staging table
Add all these numbers to the normalized table
Ignore the error that gets thrown when the unique constraint is violated
When working correctly, I now have a table with all the unique VNRs

Problem:

The Uniqueness-Constraint seems to be blocking the entire INSERT, not only the values that are actually duplicates.

Is there some way of inserting data row-wise, or tell the constraint to treat it on a row-by-row-basis?

Is this even the right approach? I'm happy to completely change the approach, since I cannot imagine I'm the first to have this issue... For context, this is essentially a reporting database that gets batch-fed once per day, with about 1m rows in total (from the csv-table), and has ~5 users running queries from time to time.

Thanks for your input!


texts: Oracle, how to manually re-instantiate a PL/SQL package?

texts:
I have a web application which calls a procedure from an Oracle PL/SQL package. Each time the package is replaced in turn of a new release the application runs into an exception ORA-06508

java.sql.SQLException: ORA-04068: Der bestehende Status der Packages  wurde  aufgehoben
ORA-04061: Bestehender Status von package body "OPS$XY.PACK_XY" wurde annulliert
ORA-04065: Ausführung nicht erfolgreich, package body "OPS$XY.PACK_XY" wurde geändert oder gelöscht
ORA-06508: PL/SQL: aufgerufene Programmeinheit : "OPS$XY.PACK_XY" konnte nicht gefunden werden
ORA-06512: in Zeile 1

From the Oracle-Docs I got the info that the package needs to be re-instantiated, which is done by the next PL/SQL call after the ORA-06508- error.

Is there a way to re-instantiate this package manually right after CREATE OR REPLACE PACKAGE ?


texts: Grouping in relational algebra with more than one grouping attribute

texts:
So I am having a bit of trouble understanding the Grouping operator, Ɣ.
If had a the relation Sales(employeeId, date, salePrice) and I wanted to list the largest salePrice for each employee per day, how would I group both by employeeId and date?

For example would it be
ƔemployeeId,date,MAX(salePrice)->largestSale(Sales)
or
ƔemployeeId,date; MAX(salePrice)->largestSale(Sales)
or something along those lines?


texts:
I've got an optional column (residential) in one of my tables that is giving me trouble. If a Licensee intends to work w/ residential customers, they need to take an additional course (course_topic_code C):

CREATE TABLE Licensees (
  license_num INTEGER PRIMARY KEY,
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN
);

INSERT INTO Licensees VALUES
  (123, 'Salesperson', NULL),
  (234, 'Salesperson', TRUE),
  (567, 'Salesperson', FALSE);

CREATE TABLE Course_Requirements (
  PRIMARY KEY (license_level, residential, course_topic_code),
  license_level VARCHAR(50) NOT NULL,
  residential BOOLEAN,
  course_topic_code CHARACTER NOT NULL,
  required_minutes INTEGER NOT NULL
);

INSERT INTO Course_Requirements VALUES
  ('Salesperson', true,  'C', 90),
  ('Salesperson', true,  'A', 180),
  ('Salesperson', false, 'A', 180),
  ('Broker',      true,  'C', 90),
  ('Broker',      true,  'A', 180),
  ('Broker',      false, 'A', 180);

The trouble is, it's valid for me to not know whether a Licensee intends to service residential customers. If the column is true they're required to take that course, but if it is false or null they're not required. This is the query I've come up with to see required courses for each Licensee:

select l.license_num, cr.course_topic_code, cr.required_minutes
from Course_Requirements cr
join Licensees l on cr.license_level = l.license_level
                and cr.residential = l.residential;

As you can see, the licensee w/ NULL for residential has no required courses:

567;"A";180
234;"C";90
234;"A";180

I might be able to devise a query that will give me what I want, but I feel that I might be modeling things incorrectly. I generally try to avoid nullable fields, but in this situation it seems appropriate. Is there a better way to model this?


texts: How to improve the speed of a large data migration between SQL Server and MySQL server?

texts:
I have an application that is connected to a SQL Server Express database. The database grew and the database is almost at 10GB which is the max limit that SQL Server Express allows for.

I am trying to switch to MySQL server instead of with only InnoDB engine. I installed MySQL 8.0.16 community edition on a Windows Server 2016 VPS. I am using the migration wizard in MySQL Workbench to migrate the database over. But the data import is painfully slow. I understand that it's going to take time to transfer the 10GB databases, but I am hoping there are things I can do to make the data transfer a bit faster.

Now, both SQL and MySQL servers are running on the same VPS. Which makes the connection speed neglectable. But, since I have two database servers running on the same machine, the machine is willing to slap me across the face if it could. The server is dying for more resources that I can't offer. My plan is to remove SQL Server from the machine once the migration is complete.

What temporary changes I can make to either MySQL or SQL Server to make this process runs a bit faster.

Since my database server is not yet in production, I ran the following two command on the MySQL Server "hoping this will speed it up even for a little"

SET GLOBAL FOREIGN_KEY_CHECKS=0;
SET GLOBAL UNIQUE_CHECKS = 0;

I have also disabled the keys on all of my large tables using the following commands

ALTER TABLE `Table1` DISABLE KEYS;
ALTER TABLE `Table2` DISABLE KEYS;

All the commands I used were executed after the import was on in the process. I am not sure if the changes I made will affect the current connection or not (i.e, "the import wizard connection")

When I check the health of my server, I see about 1.5GB of free memory that I could probably give to the MySQL server, but not sure what is the most beneficial setting for me to increase to help with this import. Running SHOW PROCESSLIST shows that the migration wizard sends bulk large INSERT INTO .... statements to the server wait until it is committed and then sends another insert.

What other steps can I do now to help the process move faster? Are there online configuration variables I can increase to help speed up the INSERT statements without restarting the insert process as that will only put me behind even more.


texts: Grant all the privileges to a user over all other user's objects in Oracle

texts: I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks


texts: How to convert mysql database charset to utf-8

texts:
I have a vb forum with a huge amount of data

I need a way to convert this forum database tables charset with thier data from

latin1_swedish_ci to utf8-general-ci

How to do this ?


texts: Can a function be immutable while calling a volatile function?

texts: I have a function that does some mathematical calculations. It calls another function, which is volatile. I would like to know if the calling function can be declared as either immutable or stable, or it must necessarily be volatile as well.


texts: How does one scale SQL Server 2008 or 2012?

texts:
How does one scale SQL Server 2008 (or 2012)? At its basic, I understand there are two options:

Scale up:

If CPU bound, I can clearly see going from 1 CPU core to 2 to 4. Or if RAM usage rockets, just adding more RAM. Does SQL Server 2008/2012 actually pick up the slack and scale up that way assuming NO application level changes? To minimize speculation, lets assume I'm not doing something dumb like burning CPU cycles, doing cross joins etc.

Scale out:

It's not very clear how scaling out would work. I mean if I added another SQL server right next to my first one, how does the query know which server to run on? Is there some load balancer at the front (and does it come with the SQL Server software?) ? Does it entail application level changes for scaling out to work? Or do I have to shard the data and have custom code that calls up the correct database server depending on the data sharding key?

Would appreciate input from more experienced folks.


texts: MySQL: Change compression on an existing InnoDB table

texts:
Is there any way to turn on compression for an existing table? I don't care if there's downtime for this.

The docs only mention setting compression when the table is created.


texts:
In my MYSQL configuration I activated logging of request not using indexes.

slow_query_log=1
slow_query_log_file = /home/mysql/sqlprive.log
long_query_time=7
log_queries_not_using_indexes =1

But in my log file I find some requests that are not slow and are using indexes:

# Query_time: 0.001160  Lock_time: 0.000188 Rows_sent: 10  Rows_examined: 20
SELECT `t`.`id` AS `t0_c0`, `t`.`nom` AS `t0_c2`, some others fields whitout indexes
FROM `my_table` `t` GROUP BY t.id ORDER BY t.name ASC
LIMIT 10 OFFSET 10;

Here is the structure of my table

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  #some other fields whitout any indexes
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1060 ;

Someone has an idea why I find this kind of request in the slow log file?


texts:
just starting to learn to design. I don't know the term in english to describe this type of design, so I'm here for some help.

Can this "specialitzations" have a relation like this one? Thanks!


texts: Unable to find server name of SQL Server

texts:
After installing SQL Server 2008 R2, it wants a server name.

I tried SQLCMD -L on cmd, but it shows the error below:

  Data source name not found and no default driver specified

What am I doing wrong?

Any kind of help will be appreciated.


texts: Typing with two carets in pgAdmin editor

texts: While I'm working with pgAdmin Query Tool I sometimes accidentally enter the mode when two carets appear in the editor and same characters are inserted in two places of text simultaneously while I'm typing. What is this mode called and what's the keyboard shortcut?


texts: Does freeing up disk space improve the performance of MySQL?

texts:
We have a MySQL master server here which was not set up with any kind of management for the binary logs. Over time, the disk space (1 TB) was slowly filling up and it was noticed that the performance of the MySQL server was becoming worse. At the time, I didn't make any connection between disk space issues and performance. I took it upon myself to clean this up, backing up old binary logs and freeing up space on the server.

Now that the transition is (mostly) complete and the server has much more free disk space, I've noticed that the performance has increased. I don't have any numbers to back this up, but one thing that I have noticed is that certain transactions in some of our apps which used to time out on a semi-regular basis are now proceeding smoothly.

I can imagine a couple of explanations for the improved performance:

The disk controller is not having to search as hard for free space
MySQL is better able to optimize tables, indices, etc.

but I really don't know why. So I really have two questions:

Can a lack of free disk space impact MySQL performance?
If so, why?


texts: I want to upgrade SQL server from 2008 to 2014 versiyon. I checked all of the deprecated features including raiserror syntax. But I have many stored procedures to check. Some of them use new syntax, some of them use old syntax and I need to alter the old syntax from them. How can I check all of the procedures to detect old raiserror syntax?


texts: Error trying a manual's example in DB2 11.1.4.4

texts:
When trying this example from the DB2 11.1 manual:

VALUES (JSON_VALUE('{"id":"987"}', 'strict $.id' RETURNING INTEGER));

I get this error:

  SQL0171N  The statement was not processed because the data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.JSON_VALUE" is incorrect. Parameter name: ""

I get the same error for various json functions. Using the Command Line Processor Plus from Windows 10.


texts: Array column vs another table when the relationship is quite small

texts:
The Data

I am creating a table of cities and their zip codes for the United States. Some zip codes have a list of acceptable cities, as designated by the USPS. For example, the zip code 36613 is associated with Eight Mile, AL, but the University of Mobile is in 36613 and it's address is in Mobile, AL. A look at USPS data and I see that Mobile is on the acceptable cities list for zip code 36613.

The Question

Now, I have a few routes that I can take:

I can create a new table called acceptable_city with a FK/PK relationship on city.
Create a column in city called acceptable_city that is defined as an array (acceptable_city text[]), and create an index on this column.

So, in a case like this which option is more advantageous? What are the pros and cons of each approach?


texts: Database design: Which will perform better, auto incremented column or a composite key for appointments

texts:
The schema of table is :

appointment(date,time,patientID,...)
pateint(patientID,PName,age,...)

PS: two or more patient can came at the same time .
    The number of appointments per day up to 500 .

Which is better for performance, using auto-incremented primary key or a composite primary key (date,time,pateintID)?


texts: Can't resume synchronization after patch failed on secondary node of Availability Group

texts:
I applied Service Pack 4 on secondary node of an Availability Group. It failed so I restored the whole VM for the secondary node to an earlier version, and now the data is not synchronizing.

I have active and passive two nodes. I tried to resume the data movement manually. But it failed. Can you suggest anything here?


texts:
(select price from product_price where product_id=1)/(select price from comp_product_price where comp_product_id =1)

I'm holding the current price in the product price and the competitor product price at the comp_product_price. I would like to get the ratio of these. Above query gives the syntax error.
How can i handle the issue? Thanks.


texts: Set a default value for a jsonb property in PostgreSQL

texts:
Let's say that I have a jsonb column with documents like:

{
    "lead": "The lead",
    "video": "An URL to the video"
}

In some cases I also need to store the name of a picture that's uploaded to the server by the user in the JSON. Obviously I don't want clashes on the filenames so I wonder if it's possible to use uuid_generate_v4() as default value:

insert into document (title, content) values (
    "The title of the document",
    '{
        "lead": "The lead",
        "video": "An URL to the video",
        "picture": ":?" -- Here I need the value returned by uuid_generate_v4()
    }'
);

Is it possible or should I get the uuid in the application before inserting the data in the database?

Thanks beforehand.


texts: Postgresql, write archive file over mount, secure against simultaneous access?

texts:
I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share.

I just use the basic command, given as an example in the postgresql.conf

test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f

Does the cp command somehow secure that my backup server, which reads the WAL archives, doesn't read half copied WAL files? Do I have to manually define a cp command that lets the file end with .tmp and then call rename afterwards to give it its proper name?

Has someone got a example of an save archive_command?


texts: PGAdmin 4 - server status / view log file

texts:
I recently upgraded to Postgresql 9.6 and PgAdmin 4.

Now, one handy feature of PgAdmin 3 was the "server status" window, which I used to look at the log file.

I can't find that in PgAdmin 4. How can I view the server log in PgAdmin 4? Or is there a nice alternative?


texts: MySQL GTID replication, slave read executed binlogs, failed to get the latest binlog

texts:
MySQL VERSION: Server version:        5.7.19-17-log Source distribution
Replication mode: mysql multi source replication: MySQL-C works as slave for MySQL-A(source1) and MySQL-B(source2);  real  topology as follows:

current state:

  channel 1( A-C),  both IO and SQL_THREAD works well, relay-log from A (parsed by mysqlbinlog), all gtids in relay-log are executed in Executed_Gtid_Set.
  channel 2( B-C), works fine.


texts: How to get paged resultset from a table?

texts:
I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is:

Sort the table on create date with the most recent dates at the bottom (ASC), so that the changes to the table do not interfere with the overall result.
Get the row number of the row that contains the last row id from the previous page, i.e. @LastOffset.
Get the next set of rows as per the @Count parameter.

I have the following example:

-- Notes table sorted by Created date ASC
NoteId BookId Username Created     NoteText
1      2      abc      01/01/2015  Note 1
2      2      abc      01/01/2015  Note 2
3      2      abc      01/01/2015  Note 3
4      2      abc      01/02/2015  Note 4

I need to create a Stored Procedure that will return the following result given parameters: @LastOffset = 2, @Count = 2

-- Result set
NoteId BookId Username Created     NoteText
3      2      abc      01/01/2015  Note 3
4      2      abc      01/02/2015  Note 4

This is what I have so far:

    DECLARE @StartOffsetRow int
    SET @StartOffsetRow =   (SELECT RNUM
                            FROM (
                                    SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, NoteId
                                    FROM Notes
                                    WHERE BookId = @BookId AND Username = @Username
                            ) WROW
                            WHERE WROW.NoteId = @StartOffset)

    SELECT *
    FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY Created) RNUM, *
            FROM Notes
            WHERE BookId = @BookId AND Username = @Username
    ) WROW
    WHERE WROW.RNUM > @StartOffsetRow AND WROW.RNUM <= @StartOffsetRow + @Count

This setup seems to work, but contains repeat queries and a lot of select statements. Looking at the execution plan, there are 7 queries. I was wondering if there is a different approach to this problem with fewer calls. I am using SQL 2008 R2 server.


texts: Most common way to express cardinality in ascii

texts:
I know that there are several symbols to express the cardinality between entities.

But is there a pragmatic common way to express cardinality in ascii?

Example: User -- Group. One user can have several groups and One group can have several users.

I looked at this page: https://en.wikipedia.org/wiki/Cardinality_(data_modeling)

For example: one-to-many    "order" ←→ "line item"

Here are some graphical ways to express cardinality:

https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model#Cardinalities

What is the most common way to express cardinalties?

Is there a standard/rfc for this?


texts: Pick up the name of the column by two diff column in single table

texts:
Thank you guys in advance! i am desperately need to display my data in this way,

My database structure is,

Column idcat, parent_id and child_id is sharing single column name, catname.

I tried to write my own query,

SELECT idcat, parent_id`, child_id, catname, catDesc, catstat, del_cat
(SELECT catname WHERE parent_id=idcat FROM categories ) as parent,
(SELECT catname WHERE child_id=`parent_id FROM categories) as child
FROM categories

I hope you can help me guys! thank you!


texts: How important is drive speed for a MySql server

texts:
I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually the searches will each query multiple match against statements as I will need to search multiple columns in the table index. The only write queries will be to update a single field of a single record approximately 10 to 20 times per minute.
My question is this... How beneficial would it be to setup an 8 drive NVMe raid 0? I know indexes are usually queried from RAM as this is usually significantly faster than storage drives. But with the newest PCIe 4.0 NVMe SSDs and the crazy speeds of these drives, I could probably setup raid0 that has about twice throughout as the fastest DDR4 ram.
Or does none if this even matter? Is throughout the factor that even matters? Can you even force indexes to be queried from the storage drive?
Thanks in advance for the advice.


texts: Oracle - how to find what caused CPU to spike 3 weeks ago

texts:
On August 22, 2017 at 11:15 on our Oracle 11.2.0.4 (32 cpus, 200GB) we had a cpu spike from 10 to 85% (as seen on AWs cloudwatch).  At 11:25 it went to 100%

Now 3 weeks later we need to understand what sql was running at 11:15 that caused the issue i.e. how much cpu they consumed.  not the ones at 11:25 but ones at 11:15

is that possible


texts:
Setup
I designed a database scheme which purpose it is to act as a foundation for testing a System under Test (SUT)  which weren't testable by automated test on default (have no Oracle and are therefore non-testable).
The scheme has a Source entity which is tied to a scenario entity via an n:m relation.
The those two describe the kind of input which is generated and provided to the SUT.
Then there are entities which describes the output, so further automated tests can ask which output it should be.
The first is a Dataset entity with a 1:n relation between scenario and Dataset.
Furthermore, Dataset is related to Result via a 1:N as well.
Problem
My problem is that source and result have two attributes (resolution, site).
To avoid an unnecessary redundant relation between source and result I wanted to create a View.
And because I have no way of interfering with the two attributes they have different Values which mean the same thing e.g. source.site = 20 result.site = London.
That's why I wanted to use a look up table (LUT) which stores both values and is only accused by the view.
Question
Is this that right way, or should I, instead of using the view and LUT, use an n:m relation which will be represented by an associative entity?
Image
To aid my description I added the latest image of my DB Scheme.

I hope this is clear, if not I'll happily provide more information.
SQL
The SQL which I used to create the view:
SELECT emi.id AS emission_id,
src.id AS source_id,
sce.id AS scenario_id,
emi.*
FROM scenario sce,
    dataset d,
    source_df src,
    map_scenario_source_df maps,
    emission_dda emi,
    df_site_discription df_site,
    df_resolution df_res
  WHERE sce.id = d.scenario_id AND d.id = emi.dataset_id AND maps.source_df_id = src.id
  AND sce.id = maps.scenario_id AND emi.site::text = df_site.name::text
  AND df_site.number_string::text = src.site::text
  AND emi.agility::text = df_res.agility::text
  AND src.resolution::text = df_res.resolution::text
  AND emi.normalized_center_frequency_mhz >= (src.mean_freq - 384000)::double precision
  AND emi.normalized_center_frequency_mhz <= (src.mean_freq + 384000)::double precision;


texts: Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

texts:
I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria?

What I am essentially asking is, is there an algorithm or any literature(s) that explains how to simplify/reduce range intervals from complex predicates? Assuming B-TREE index or any index with sequential arrangement of key values.

For example:-

Single Column Examples

From

(duration > 5 and duration < 10) or (duration < 100)

To

NULL < duration < 100

From

(duration > 5 and duration < 10) or (duration < 100)

To

5 < duration < 10
100 < duration

Two Column Examples

From

(duration in (9,10) and service_id > 500)

To

9 <= duration <= 9 AND 500 < service_id
10 <= duration <= 10 AND 500 < service_id

From

(duration in (9,10) and service_id = 500) or (duration = 19 and service_id=570)

To

9 <= duration <= 9 AND 500 <= service_id <= 500
10 <= duration <= 10 AND 500 <= service_id <= 500
19 <= duration <= 19 AND 570 <= service_id <= 570


texts: Safe delete Transaction Log in SQL Mirroring scenario

texts:
We are using SQL Server Mirroring with a witness (High Safety mode) and we see the transaction log on the Principal Server has grown around 20 GB in few weeks. I looked around on SO and google for specific articles related to "Safe delete Transaction Log" in SQL Server but found none for my case.

My Questions:

Would it be safe taking full backup of Principal Server and then delete the transaction log completely?
Does it have to do something with Full Recovery/ Simple Recovery Mode?
What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Note:

We are using SQL Server 2014 Enterprise Edition for Mirroring and Express version of it as a Witness.

Thanks in Advance!


texts: Azure Managed Instance MSOLAP OLE DB connector

texts:
We have a managed Azure SQL instance, and trying to leverage it for some Azure AS documentation. Managed Azure supports OPENROWSET which was going to be used to access DMVs in the SSAS tabular model, but instead we get the following error:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" has not been registered.

Question is then, can MSOLAP OLE DB provider be installed on an Azure Managed SQL database, and if so how? Thanks!

Example query run on the managed instance:

SELECT *
    FROM OPENROWSET('MSOLAP','DATASOURCE=azure.windows.net/server;
     Initial Catalog=DataCatalog;',
    'SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS')


texts: use cases for db.collection.validate() - checks the structures within a namespace for correctness by scanning the collection’s data and indexes

texts:
I found on MongoDB mailing list one use case for db.collection.validate():

  The count() command is generally an estimate of the current document
  count, based upon statistics provided by the storage engine.

  After an unclean shutdown of a mongod using the WiredTiger storage
  engine, count statistics reported by count may be inaccurate. If this
  does occur you can run validate() on each collection to restore the
  correct statistics.

are there other use cases? Maybe regular DBA job task? We have regularly unclean shutdowns.

  The validate command checks the structures within a namespace for
  correctness by scanning the collection’s data and indexes. The command
  returns information regarding the on-disk representation of the
  collection.

  The validate command can be slow, particularly on larger data sets.


texts: What is the date mentioned in @@version SQL Server

texts:
I wanted to know what the date that is result of select @@vesion in SQL server refers to.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: )


texts:
I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time
So, I used approach that helped me before many times - updating top (N) rows at a time with 1-N second interval between chunks
This time started with update top (1000) rows at a time, monitoring the Extended Events session for lock_escalation events in the process
lock_escalation showed up during each update operation, so I started lowering row count per chunk 1000 -> 500 -> 200 -> 100 -> 50 rows and so on down to 1
Before (not with this table, and for delete operations - not update), lowering row count to 200 or 100, helped to get rid of lock_escalation events
But this time, even with 1 row per 1 update operation, table lock_escalation still shows up. Duration of each update operation is about the same, regardless if its 1 row or 1000 rows at a time
How to get rid of table lock escalations in my case ?
@@TRANCOUNT is zero
Extended event:

Code:
set nocount on

declare
    @ChunkSize              int = 1000,                         -- count rows to remove in 1 chunk
    @TimeBetweenChunks      char(8) = '00:00:01',               -- interval between chunks

    @Start                  datetime,
    @End                    datetime,
    @Diff                   int,

    @MessageText            varchar(500),

    @counter                int = 1,
    @RowCount               int = 1,
    @TotalRowsToUpdate      bigint,
    @TotalRowsLeft          bigint

-- total row count to update
set @TotalRowsToUpdate = (select count(*)
                            from [Table1]
                                join [Table2] on
                                    btid = tBtID
                            where   btStatusID = 81)

set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''

-- begin cycle
while @RowCount > 0 begin

    set @Start = getdate()

    -- update packages
    update top (@ChunkSize) bti
        set btstatusid = 154,
            btType = 1
    from [Table1] bti
        join [Table2] on
            btid = tBtID
    where   btStatusID = 81

    set @RowCount = @@ROWCOUNT

    -- measure time
    set @End = getdate()
    set @Diff = datediff(ms,@Start,@End)

    set @TotalRowsLeft = @TotalRowsLeft - @RowCount
    set @MessageText = cast(@counter as varchar) + ' - Updated ' + cast(@RowCount as varchar) + ' rows in ' + cast(@Diff as varchar) + ' milliseconds - total ' + cast(@TotalRowsLeft as varchar) + ' rows left...'

    -- print progress message
    raiserror (@MessageText,0,1) with nowait

    set @counter += 1

    WAITFOR DELAY @TimeBetweenChunks

end

Plan:
https://www.brentozar.com/pastetheplan/?id=SyozGWMLw


texts: pg_repack not organizing table by column

texts:
I'm using pg_repack to order one table by a timestamp column, however I'm finding that after running pg_repack command it's not ordered at all.

I'm using this command

pg_repack --jobs 4 -t table_with_dates -e -o "start_time asc"

After about 10 minutes it finishes but when I query the first page of the table there is no order over "start_time" column.

I'm using postgresql 9.6.3 and pg_repack 1.4.0

Is there something i'm missing here so I can get the correct data ordering?

Edit 1:
Table definition

CREATE TABLE customer_service (
    customer_service_id character varying(32) NOT NULL,
    store_id character varying(32) NOT NULL,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone NOT NULL,
    customer_id character varying(32) NOT NULL,
    service_id character varying(32) NOT NULL,
    CONSTRAINT customer_service_key PRIMARY KEY (customer_service_id),
);
CREATE INDEX customer_service_start_idx
  ON public.customer_service
  USING btree
  (start_time);

 CREATE INDEX customer_service_customer_idx
  ON public.customer_service
  USING btree(customer_id);

CREATE INDEX customer_service_service_idx
  ON public.customer_service
  USING btree
  (service_id);

SELECT performed by pg_repack:

LOG: (query) CREATE TABLE repack.table_6527203 WITH (oids = false) TABLESPACE pg_default AS SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service
ORDER BY start_time WITH NO DATA

LOG: (query) INSERT INTO repack.table_6527203 SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service

Seems like the insert into is not ordering the data?, as the order by is only present on the create table command

Edit 2:
If I run

WITH dates_per_page AS (
  SELECT (ctid::text::point)[0]::bigint AS page_number, COUNT(DISTINCT date(start_time))
  FROM customer_service
  GROUP BY 1
) SELECT avg(count) FROM dates_per_page;

I get 15.340 as the result, I would expect a number closer to 1 here...


texts: select data which is bigger than buffer pool size

texts:
Imagine we have a table which has 100 Million rows and 80 GB size.

Every rows has Text column and multiply integer columns.

we set Innodb_buffer_pool_size to 40G

I executing this Query:

select text,id,like_count from example where time > 'xxx-xx-xx' and time < 'xxx-xx-xx'

So if this query need to read 50G of data file (because of query) and move to Buffer Pool. I want to know how buffer pool handle this data.

because We set buffer pool to 40G but query need 50G to handle it.


texts:
Not sure this is even possible but I was wondering if there is any way of using PostgreSQL to return the top 100 most occurring words from a database column containing strings of varying lengths?

The table contains ~ 250k rows, with each row containing a message among much other metadata. So, is there a way of sifting through every word in each row, taking the total number of times it appears and comparing it to a count of all the other words in the same column to return the top 100?

If it is easier, the metadata can be dropped to leave only the column containing the Tweets.

Ideally, I would like to be able to perform what this website does to give the same output, only with many more records.


texts: How to create a train schedule in SQLite3?

texts:
I am interested in what a SQLite3 database schema would look like for a train timetable.

Generally this is presented in table format as the final result.

Sample Schedule for M to F

STATION A   STATION B    STATION C ... STATION K
6:00        6:15         7:30          9:00
6:30        6:45         7:45          9:30
7:00        7:15         ----          10:00
8:00        8:15         9:30          11:00

Sample Schedule for Weekends

STATION A   STATION B    STATION C ... STATION K
7:00        7:15         8:30          10:00
8:30        8:45         8:45          10:30
9:00        ----         9:30          11:00
10:00       10:15        10:30         12:00

The trains don't have any number (like the A-Train, 12 Train, etc). So we can make an internal number. Should the internal number be unique or duplicated for a Monday-Friday to a Saturday and Sunday.  Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.

Stations can be ordered by their distance.

So far I am thinking I need tables for:

Stations (ID, name, distance, line)
Train (ID, train no, Day Operation, details)
Train run (Train.ID, Station.ID, arr/dep, time, notes)

Is this normalised enough?

Is my Schema correct or so I need to make any changes?


texts: How to get the nearest date to a record date in sql?

texts:
I have the following data structure:

Table A:

ID  |  RequestNumber  |  Date
----+-----------------+-----------
 1  |      1          |2017/09/27
 2  |      1          |2018/06/02

Table B:

RequestNumber  |  Serial  |  Date
---------------+----------+----------
     1         |    1     |2017/09/27
     1         |    2     |2017/09/27
     1         |    6     |2018/06/03
     1         |    7     |2018/06/03
     1         |    8     |2018/06/03

As we can see the nearest date to the first row of Table A is 2017/09/27  in the Table B and the nearest date to the second row is 2018/06/03 in the Table B

So...

I need a query to have each row from Table A with all rows from Table B that is the nearest to the record from Table A (it means 2 records should be returned the first record and 3 records should be returned for the second record)

The expected result would be:

ID  |  RequestNumber  |  Serial  |  Date
----+-----------------+----------+------------
 1  |      1          |    1     | 2017/09/27
 1  |      1          |    2     | 2017/09/27
 2  |      1          |    6     | 2018/06/03
 2  |      1          |    7     | 2018/06/03
 2  |      1          |    8     | 2018/06/03

Thanks in advance


texts: dropping and adding subscription only picking up new article on some subscribers

texts:
I have used the following script to drop aand re-add a table from a subscription so I could move replace the ntext fields with nvarchar(max) columns. the script worked fine, but when I ran the snapshot agent, it generated the new snapshot with 1 article, but only delivered to 2 of the 4 subscribing servers. They all have a subscription in the script. Can anyone suggest why this would be ?

The only thing that is different with those servers is that they also have a AV group installed on them. Could a bug in SQL 2012 be preventing the replication process working properly ??

sys.sp_dropsubscription @publication = 'statics BLOB tables', -- sysname
  @article = 'pgeDataLocation_tbl', -- sysname
  @subscriber = 'CHICAGO', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
GO
sys.sp_dropsubscription @publication = 'statics BLOB tables', -- sysname
  @article = 'pgeDataLocation_tbl', -- sysname
  @subscriber = 'SURVIVOR', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
GO
sys.sp_dropsubscription @publication = 'statics BLOB tables', -- sysname
  @article = 'pgeDataLocation_tbl', -- sysname
  @subscriber = 'KINKS', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
GO
sys.sp_dropsubscription @publication = 'statics BLOB tables', -- sysname
  @article = 'pgeDataLocation_tbl', -- sysname
  @subscriber = 'BLONDIE', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
GO

sys.sp_droparticle @publication = 'statics BLOB tables',
  @article = 'pgeDataLocation_tbl'
GO

---  table changes

sys.sp_addarticle @publication ='statics BLOB tables', -- sysname
  @article = 'pgeDataLocation_tbl', -- sysname
  @source_object = N'pgeDataLocation_tbl', -- nvarchar(386)
  @destination_table = 'pgeDataLocation_tbl' -- sysname
  GO
  sys.sp_addsubscription @publication = 'statics BLOB tables', -- sysname
  @subscriber = 'CHICAGO', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
  GO
  sys.sp_addsubscription @publication = 'statics BLOB tables', -- sysname
  @subscriber = 'SURVIVOR', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
  GO
    sys.sp_addsubscription @publication = 'statics BLOB tables', -- sysname
  @subscriber = 'KINKS', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
  GO
    sys.sp_addsubscription @publication = 'statics BLOB tables', -- sysname
  @subscriber = 'PINKFLOYD', -- sysname
  @destination_db = 'MLT-Replicated' -- sysname
  GO


texts: How to swap array values in PostgreSQL.?

texts:
How to swap array values in PostgreSQL. For example i have a table that has a column whose data type is bigint[]. It has following value in it.

value: {111, 222, 333}
My desired output is {333, 111, 222}

i can not find any built in function to achieve other than array_replace.


texts: How to switch between Oracle 11g and 12c on same linux

texts:
I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run test paralally.
Do you know any document that describe this scenario of multiple oracle on same machine.

Note: I am not from DB background and just using it for something so please suggest accordingly


texts:
How can I handle a table given to me from an outside DB that does not have a PK? Or even an ID field that marks the record specifically. I am using SQL Server.
I have a table that has come to me that has information on an event. I need to be able to pull the company information that I can. There is an event_id field that I can use to keep track of the rows, but there is no company_id.
Here are the rows I think are useful for this discussion:

event_id
company_name

I can do a grouping by company_name and get multiple counts, so I know the name is consistent. The end goal of this is to connect this to my larger DB that DOES have company_id columns.
The external DB is not willing to change their export at all.
Should I create a separate key table for the companies in this table and simply keep my own set of keys? Is that good practice? If so, how can I do that? If not, what should I consider doing?
Forgive me if this question has been answered before. I looked but could not find it. I'm not even sure how to word this question. I would love to be pointed in the right direction.


texts: Error while exporting data to flat file from database using sqlplus in UNIX

texts:
Trying to export data from a table to .dat file in a unix script using the SPOOL command.

However on execution only ~17k data is exported to file out of 91K data with an error in end of the file : ORA-08103 Object do not exist, though the objects exists in the database.

When I tried to export the data in csv, it exported only ~11k data with same error in the file.
Exporting almost ~35 columns from the table.

spool ${DataDirectory}/output.dat;

select * from < Table_name >;

What could be the reason for not exporting complete data set and throwing a error even if object exist?

Attached is the screenshot of the script.


texts:
The postgres database in question receives monthly updates, in the form of new tables. I am trying to work out which concept is most efficient to perform the task which I will describe below.

Table1 has a StoreNumber column; its contents refer to a geographical location. This number will always match the same location. To help our analysts use the data, I have been running a CASE function right after the new tables are added each month, which will add a new column defining the location name.

Now, to me this feels efficient, however I am being challenged and it is suggested that a reference table is a much better method of providing the location information.

It is hard to find information out there on using reference tables - Which is my struggle for finding an answer to this myself!


texts: What is technical meaning of point in time recovery?

texts:
Please can I have a technical meaning of point in time recovery? Does it mean that given a backup file, we must be able to recover upto any point in time during the span of the time covered by the backup?

If so, then is it right to say only a transaction log backup will allow for this? Or is only full or differential backup sufficient? How about recovery models - is full recovery model a must? Or can it be bulk recovery model also?


texts: What does it mean when Postgres query state is null


texts: Trigger with a Variable Select and Order by

texts:
Is there a way to a set trigger that will activate once a new table is created and will get the new tables name?
I was able to come up with the query below but it only gets the first query by alphabetical order, not by date created

CREATE TRIGGER CreateTable
ON DATABASE
    FOR CREATE_TABLE
AS
     DECLARE
       @table varchar(max);
     SELECT  top 1  @table =   [name] FROM sys.tables
     insert into NewTable values(@table)
     ;
     end


texts: Oracle 11gR2 can I delete datafiles to reduce tablespace?

texts: I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles have between 3% and 4% usage ... my question is: can I delete some of these datafiles to reduce the size of the tablespace? The information that these datafiles contain would be automatically relocated in the remaining ones? Thank you very much in advance.


texts: Moving tables to another SQL2008 database (including indexes, triggers, etc.)

texts:
I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another.

I originally just used the Import/Export Wizard, but all the destination tables were missing primary and foreign keys, indexes, constraints, triggers, etc. (Identity columns were also converted to plain INTs, but I think I just missed a checkbox in the wizard.)

What's the right way to do this?

If this were just a couple of tables, I would go back to the source, script out the table definition (with all indexes, etc), then run the index creation portions of the script on the destination. But with so many tables, this seems impractical.

If there wasn't quite so much data, I could use the "Create Scripts..." wizard to script out the source, including data, but a 72m row script just doesn't seem like a good idea!


texts: Is there any practical difference between using the Lookup Wizard to create a FK relationship, or using the Relationships window?

texts:
I'm very new to Access, so I'd appreciate patience if this is a basic topic.

I'm creating a lot of tables which I need to link. I have two tables, Keyword and KeywordOrthography. KeywordOrthography has a KeywordFK field that connects to the Keyword.ID field.

As far as I know there are two ways to connect KeywordFK to KeywordID:

Go into the relationships window, drag Keyword "ID" to "KeywordFK", create a One-to-Many relationship (enforcing referential integrity.)
Clear any existing relationships between the two tables, go into Table Design view, and on the KeywordFK field, select Lookup Wizard. Select get values from another table, and import the ID field from Keyword. Then when I go to input a new record into KeywordOrthography, I can select from already existing Keywords to input.

Both methods create a 1-to-n relationship with the table, and the second way makes it easier to input a new record manually, though I'm not sure if it causes any complications when using SQL or other methods when adding new records. On the other hand, the first method is much faster when it comes to linking tables.

Question is: Are these methods equivalent as I've pre-supposed? Is there an advantage to doing it one way or the other?


texts: I ran Brent Ozar's sp_blitz on one of my production server.  IT tagged a few tables in the MSDB database as being user created.  They all follow the pattern dbo.DTA_*.  Do these table come with MSDB, or were they added by something/someone?


texts: Transfer SQL Server 2017 Standard license to a new server

texts:
We recently purchased a license for SQL Server 2017 Standard (16 cores) in behalf of a client from an online reseller (not directly from Microsoft), and installed it without problem: we downloaded and installed the evaluation version of SQL Server from the MS web site and then used the edition upgrade of the installation center to enter the serial number (since we didn't have the license at the installation time).

But now the client want to reinstall SQL Server in another (more powerful) server and decommission the current one. So the question is: can we just use the serial number in the new server, or previously we have to "remove" the license of the current installation in some way?


texts: I'm wondering when a table acquires an oid. Is it only when the transaction commits? Or does it have a hidden oid prior to commit? Especially in the context of a CREATE TABLE AS SELECT.


texts: Physical only checkdb is failing, but full one is completed successfully

texts:
I am executing checkdb with physical_only option and it fails with multiple errors like below:

  Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1557580587,
  index ID 1, partition ID 72057594088456192, alloc unit ID
  72057594177454080 (type In-row data). The off-row data node at page
  (1:13282192), slot 3, text ID 6370769698816 is referenced by page
  (0:0), slot 0, but was not seen in the scan. Msg 8965, Level 16, State
  1, Line 1 Table error: Object ID 1557580587, index ID 1, partition ID
  72057594088456192, alloc unit ID 72057594177454080 (type In-row data).
  The off-row data node at page (1:13282192), slot 5, text ID
  6370769764352 is referenced by page (0:0), slot 0, but was not seen in
  the scan. CHECKDB found 0 allocation errors and 5255 consistency
  errors in table 'TableX' (object ID
  1557580587). CHECKDB found 0 allocation errors and 5255 consistency
  errors in database 'DatabaseX'. repair_allow_data_loss is the minimum
  repair level for the errors found by DBCC CHECKDB (DWH_LAND).

However full checkdb is successfull:

  CHECKDB found 0 allocation errors and 0 consistency errors in database
  'DatabaseX'. DBCC execution completed. If DBCC printed error messages,
  contact your system administrator.

TableX has around 200 000 rows and has clustered columnstore index on it.
 We are using following version of SQL Server:
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4

Should I be worried?


texts: MySQL - stay in cache priority for queries

texts:
Is there a way to tell MySQL that some queries has highter priority in cache and mustn't be popped out until it's data become invalid?

The problem is that innodb cache size is rather small and data of "Major" heavy weight queries are popped out quickly because of other frequently used queries.

So we want to say: "these Major query data MUST stay in cache. If cache is full, no Major data will be popped out until it become invalid. If cache is full of Major data, no other queries will use cache".

Some info: engine=InnoDB. Cache is turned on and directives SQL_(NO)_CACHE are not used in application queries. Cache is big enought to store all Major data and much "hot" data, but too small to store even 5% of all queries data. It's desirable to use cache on all queries but they shouldn't replace Major data


texts: Predict how much hard drive space a query might cost?

texts:
Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My question is: Given a query, is there a way to predict how much hard drive space it might cost? Is adding the size of involved tables a good enough estimation?

EDIT: Currently I am using Postgres 10. The query in question is about creating a materialized view and do some arithmetic, lag and group by on a single table with a size of around 750 GB.

Query looks like

CREATE MATERIALIZED VIEW foo AS
   WITH change_data AS (
             SELECT (
                      CAST(lagged_data.todays_count AS double precision) /                                      CAST(lagged_data.previous_count AS double precision)
                      - CAST(1 AS double precision)
                    ) / GREATEST(EXTRACT(
                        days from (date_belongs_to - previous_date_belongs_to)
                    ), 1) AS change,
                    lagged_data.date_belongs_to,
                    lagged_data.user_id
               FROM lagged_data
              WHERE lagged_data.previous_count IS NOT NULL
        ),
        lagged_change_data AS (
             SELECT change_data.change,
                    LAG(change_data.change) OVER (
                        PARTITION BY change_data.user_id
                            ORDER BY change_data.date_belongs_to
                    ) AS previous_change,
                    change_data.user_id
               FROM change_data
        ),
        acceleration AS (
              SELECT AVG(ABS(lagged_change_data.change - lagged_change_data.previous_change)) AS fgs,
                     lagged_change_data.user_id
                FROM lagged_change_data
               WHERE lagged_change_data.previous_change IS NOT NULL
            GROUP BY lagged_change_data.user_id
        )


texts: PostgreSQL - Best way to incremental export 1000+ tables every 5 mins

texts:
I have 6 PostgreSQL database servers(v11) hosted on some third-party vendor. I don't have access to setup pg_logical. Each server has 1 database but 1000+ tables.
So I want to get the data from these 6 servers to my central PostgreSQL database. The sources tables are having PK and last_updated_timestamp columns.
Im just trying to find the best approach to get the data at every 5 or 15mins interval from these tables to my central database server.
My goal is to sync the whole database to Report database server with every5mins interval.


texts: How to have folders in the SSMS 2017 project?

texts:
I am using SSMS 2017 and SQL server 2017, I have many companies and each company has many databases.

For example. I have five companies:

Company1, Company2, Company3, Company4, Company5

Every company has many databases:

Employees_DB, Products_DB, Customers_DB (we are talking about 15 database)

Each of the previous databases has many tables.

How can I create a folder for each company, so it has all its own databases?

To be the result :

Company1 folder : Employees_DB, Products_DB, Customers_DB

Company2 folder : Employees_DB, Products_DB, Customers_DB

Company3 folder : Employees_DB, Products_DB, Customers_DB

Company4 folder : Employees_DB, Products_DB, Customers_DB

Company5 folder : Employees_DB, Products_DB, Customers_DB


texts: Permissions required for running extended events

texts: I've found this link: Changes to Extended Events Permission Requirements in SQL Server 2012 that mentions the need of CONTROL SERVER permission for using extended events on SQL Server 2008. I want to grant access to extended events to developers, is not any other way to do it? We are running on SQL Server 2008R2.


texts: MySQL Insert/Update across 3 tables with 1m+ rows

texts:
To start with, I know nothing of database design, so I apologise if this seems obvious to others. I have been researching up to 3NF over the last few weeks, and I think I have a layout that works.
I have a database with 1m+ rows, currently organised as follows:
Table: MasterTable
Rows: ID, FirstName, LastName, PetName, PetAge
I would like to split it as follows:
Table: People
PersonID (PK), FirstName, LastName
Table: Pets
PetID (PK), PetName, PetAge
Table: Records
RecordID (PK), MasterTable.ID, People.PersonID, Pets.PetID
PKs in all cases auto-increment so that more records can be added later.
The people and pets tables have been populated using:
INSERT INTO Pets(PetName, PetAge)
SELECT PetName, PetAge
From MasterTable
WHERE 1

INSERT INTO People(FirstName, LastName)
SELECT PetName, PetAge
From MasterTable
WHERE 1

INSERT INTO Records(ID)
SELECT ID
From MasterTable
WHERE 1

So I have three tables. When I try to create the Records table, I can't get anything to work.
I have tried:
INSERT INTO Records(PersonID, PetID, ID)
SELECT People.PersonID, Pets.PetID, MasterTable.ID
FROM MasterTable
LEFT JOIN People ON MasterTable.FirstName = People.FirstName AND People.LastName = MasterTable.LastName
LEFT JOIN Pets ON Pets.PetName = MasterTable.PetName AND Pets.PetAge = MasterTable.PetAge
WHERE 1

I think the WHERE clause might be the problem. I have tried
WHERE Pets.PetName = MasterTable.PetName
and almost every kind of WHERE I can think of.
I have a few questions I'd really appreciate some help with as I'm going out of my mind here.

Does it matter the order of the LEFT JOIN clauses? Does it matter which table is specified first and which is specified last?

I initially tried INNER JOIN but I figure it's just going to join more columns than is necessary, is that right?

If I am inserting firstname and lastname, I can't match on firstname and lastname, right? As in, create the firstname lastname entries and then use that ID to match the next join?

It seems simple enough to split this into three, assign a PK to each, and then create a finale table where PKs relate to PKs, but apparently it's not.
When I add '''LIMIT 5''' the select returns the correct info. Without the limit clause, all my attempts have run for over 24h and not finished. Either they have been stuck copying everything to temp tables, or they have just said "selecting data" as the status.
Can someone please help?
Sorry if something doesn't make sense, I'll clarify as I go.


texts: eCommerce orders table. Save prices, or use an audit/history table?

texts:
Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product

A product can been purchased. It has various details, but the most important is unit_price.

An order_line_item has a foreign key to the product_id purchased, the quantity purchased and the unit_price at the point in time the customer purchased the product.

Most of what I have read says that the unit_price on the order_line_item should be explicitly added (i.e. not referenced through the product_id). Makes sense, as the store could change the price in the future which would mess up order reports, tracking, integrity etc.

The thing I don't understand, is why directly save the unit_price value to the order_line_item?

Would it not be better to create an audit/history table that documents the unit_price change of a product?

When an order_line_item is created, the foreign key of the product_audit table is added and the price can be retrieved (by reference) from there.

There seem to me to be a lot of positives to using this approach (less duplication of data, price change history etc), so why isn't it more frequently used? I haven't come across an example of an eCommerce schema that uses this approach, am i missing something?

UDPATE: It seems like my question relates to Slowly Changing Dimension. I'm still confused though as Slowly Changing Dimension relates to data warehouse and OLAPs. So can Slowy Changing Dimension types be applied to my main business transaction process database (OLTP)? I wondering if I'm mixing a lot of concepts up, Would greatly appreciate some guidance.


texts: How to remove duplicates from any array and preserve ordering in PostgreSQL?

texts:
I'm looking for a way to eliminate duplicates from a PostgreSQL array
while preserving the ordering of its elements. What I currently
have are the following functions:

create function array_unique( anyarray )
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) ); $$;

create function array_unique_sorted( anyarray )
  returns anyarray immutable strict language sql as $$
  select array( select distinct unnest( $1 ) order by 1 ); $$;

/* ### TAINT there ought to be a simpler, declarative solution */
create function array_unique_stable( text[] )
  returns text[] immutable strict parallel safe language plpgsql as $$
  declare
    R         text[] = '{}';
    ¶element  text;
  begin
    foreach ¶element in array $1 loop
      if not array[ ¶element ] && R then
        R :=  R || array[ ¶element ];
        end if;
      end loop;
    return R; end; $$;

In the above, array_unique takes an array of any type and returns a copy
with all duplicates removed; their relative ordering is arbitrary.
array_unique_sorted is like array_unique, but the elements are sorted
relative to each other; this is sometimes useful as all arrays with the
same set of of distinct elements will compare equal after being
normalized by this function.

array_unique_stable already does what I'm looking for: given an array
(which in this exampe must be a text[] array), it scans elements from
left to right; whenever it encounters a previously unseen element, it adds
that one to the result. Thus, only the first occurrence of each value
is kept.

However, the implementation has some drawbacks: First, there seems to be no
way to write it so it accepts the pseudo-type anyarray.

Second, while
the first two functions are written in SQL, they may presumably be inlined,
array_unique_stable is written in PL/pgSQL, and so it cannot be inlined.

Third, it bugs me that I couldn't come up with a solution in pure SQL...


texts:
I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this:

CREATE TABLE testtable (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL, -- This is just an example: this could be the PK
    value BYTEA -- This could be TEXT
);

If the application using this table tries to insert two rows using the same name, I get this error in the logs (this is an expected error, of course):

ERROR:  duplicate key value violates unique constraint "testtable_name_key"
STATEMENT:  INSERT INTO testtable(name, value) VALUES ('a', ...something rather long...);

While logging the error is useful, as well as logging the statement (and possibly the value for "name" in this particular context), logging the long BYTEA or TEXT value isn't. In fact, binary data in the logs are escaped in text form (e.g. E'\\377\\327...'), which effectively makes it take even more space than it would in binary form in the database. This can cause more problems than help when the value's size is tens of kB or more.

Is there a way to prevent these values from appearing in the logs when there is an error? I'm still interested in knowing there was an error (and preferably have some information about the statement). Of course, this is not just about this particular error and use case, but any error log that could potentially dump multiple kilobytes of uninteresting data in the logs.

Otherwise, is there any way to truncate the "STATEMENT:  INSERT INTO ..." line? Failing that, would there be any way to keep the ERROR: line but to remove the STATEMENT: line altogether?

I've tried to set log_error_verbosity = terse in postgresql.conf, but this doesn't seem to change anything for this.

(I'm also using log_destination = 'syslog' to log to rsyslog, so a solution that limits each log "bundle" to a few lines there would be a reasonable compromise.)

I'm using PostgreSQL 8.4, but if solutions only present in newer versions would also be interesting.


texts: Is "--one-database" safe to use for importing from a dump?

texts:
If I make a full dump using mysqldump --all-databases > dump.sql and then I want to import just a single database, I can use mysql --one-database db1 < dump.sql.

According to the documentation, This option is rudimentary and should be used with care because statement filtering is based only on USE statements.  Here is an example of how this could be dangerous:

mysql --one-database db1

DELETE FROM db2.t2;
USE db2;
DROP TABLE db1.t1;
CREATE TABLE db1.t1 (i INT);
USE db1;
INSERT INTO t1 (i) VALUES(1);
CREATE TABLE db2.t1 (j INT);

But this scenario seems very unlikely because I have never found a dump to contain statements that specify a database.table syntax.  So the question is, if I make a full dump as written above, is there any risk of using the --one-database option to restore?


texts: What is the maximum number of columns allowed on a table in MySQL MEMORY storage engine?

texts:
The official MySQL documentation the the MEMORY storage engine covers maximum table size (in MB) but the maximum number of columns that can be defined on a table is not specified.

This question is specifically about the MEMORY storage engine - not InnoDB or MySQL in general.


texts:
Typically when doing a restore of a DB from a production server to a non-production server I will use the WITH REPLACE option as when I forget I get an error about the tail log not being backed up.

According to MSDN I should indeed be backing up my tail log before restoring:

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the … WITH NORECOVERY option of the BACKUP Transact-SQL statement.

What are some dangers or disadvantages of the way I'm doing it? Why is backing up the tail log first of advantage to me?

I'm using SQL Server 2008R2 but I'm presuming this query will be relevant to most newer versions of SQL Server also so have not tagged it as such initially.


texts: Copying a restoring database in SQL Server 2012

texts:
I've got two SQL Servers, one of these servers (Server A) is backing up transaction logs on some database and uploading them to the other (Server B). Unfortunately I have no access to Server A, I simply have to trust that it is doing its job of periodically uploading its transaction logs to Server B (via something like FTP).

Now, suppose Server B needs to recover the database to do some reading and writing. Doing this will break its ability to receive further transaction log backups.

Is there any way to copy/branch/backup the restoring database, so I can have one version of it that will continue to apply the transaction logs, and one version that will be recovered for reading/writing?


texts: Difference between encrypted backup of an encrypted DB and an unencrypted backup of an encrypted DB

texts:
Let's say we have a fully encrypted database (the type of encryption is not considered in this scenario), and we produce two backups for that database.

If I encrypt only one of the backups, would there be a difference between it and the other one? Is it redundant to encrypt the backup if the database was already encrypted?

I ask from the perspective of the security of the data, not performance or anything else.

Thanks!


texts: Overcome LIKE character length limitation

texts:
By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause.

I'm trying to fetch the query plan from query plan cache for a particular query.

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where st.text like '%MY_QUERY_LONGER_THAN_4000_CHARS%' ESCAPE '?'

if the query inside the the LIKE is longer than 4000 chars then I get 0 results even if my query is in the cache plan. (I was expecting at least an erorr).

Is there a way to workaround this issue or to do it differently?
I have queries which can be > 10000 chars long and it look like I can't find them with the LIKE.


texts: SSMS 2018 How to create query that display tables with DBA defined columns and tables that are not empty

texts:
I am a power bi user, my current task was to do custom "views" on Microsoft SQL database so that those "views" can be used in doing power bi report later.

Now there's one problem that i have encountered, I have a long list of tables that can't be disclosed and each of the table has a list of columns bound to them.

I did like to request for help, I need to search tables that are not empty but with custom defined columns.

Let's say I have tableA, tableB, tableC and tableD.

I want to first search let's say a custom column named "UserID" but I don't want it to displayed yet, I want to do 2nd filter that filters out the table that are empty.

So the end result was only the tables that are not empty and tables that have the column named UserID

Is it possible to do this, and if yes, can I request for the query to get the result I want?


texts: managing a growing database in the long run (10 years from now)

texts:
I'm close to finishing-up a WEB app (PHP + mysql website portal), which will start slowly, as in uploading few pictures and inserting few rows in main database table, but soon, and over few years, I expect it to grow steadily and even logaritmically at some point.

By my Excel calculations, at some point, the database table data + indexes (innoDB) will overgrow the size of hosted pictures by a factor of 10, but in the beginning it will be the other way round.

So basically my portal hosts pictures (medium size jpegs let's say) and uses MySql InnoDB database tables.

My question to you all admins and professionals is about my MySql Database.

I'm thinking in advance so I don't get caught unprepared :

When my database will grow more and even get 1.2TB or 2TB or more....how can I deal with that ? Can I say split it on 2 or more dedicated machines so searches will still be fast and getting data from this big database ? Or shall I do 1 server and pump it up with a lot of cheap 7200 rpm sata ? or what can be the solution to a growing database.

My first configuration for this web-app is a VPS - SSD hosting with a 4ghz processor, 3GB of ram and 2x60 GB SSD raid 1.

thanks for your answers,


texts:
Is it possible to generate variables solely through code?  If you have a base variable name, and add an int to the end of the name, could you generate variables?  Something similar to the below?

In the end I would want this syntax to generate 4 variables

@newlygeneratedvariable1
@newlygeneratedvariable2
@newlygeneratedvariable3
@newlygeneratedvariable4

Declare @i int, @totalvariables int

Set @totalvariables = '4'

for @i <= 4
Set @i = 1

    Declare @newlygeneratedvariable + @i
    Print

    @i = @i + 1

Next @i