texts: How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts

texts:
I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD.
I couln'd find any valuable information so far unfortunately. Perhaps because Linux platform is quite fresh for MSSQL Server. Thank you for any advice here.


texts: "Truncate" or "Delete/Vacuum Full" for deleting some of the table rows

texts:
I have a db which has 223 tables and I have to delete some of the records from 10 of them, each has apprx. 1.5million records. Those tables are storing the temperatures every 7seconds. We have decided to remove all records but the first record for every minute. So now there are 8 records for each minutes and after this process it will store 1 record per minute which are older than 3 months.

Should I do it by delete and vacuum full afterwards or by truncate? There are also some views related to some of those tables. If I do it by truncate, would those views work afterwards?


texts: Why the mysql MSI Installer is more huge than wamp installer packages size?

texts:
In http://dev.mysql.com/downloads/windows/installer/5.6.html, you can get Windows (x86, 32-bit), MSI Installer  whose size is 252.6M .
In http://sourceforge.net/projects/wampserver/files/, you can get wampserver installer packages whose size is only  39.8M.
I am confused that wampserver contain apache\mysql\php ,why 1+1+1<1?


texts: How to model a price discount with a purchase of several products as condition?

texts:
Scenario
Let us say that, in my business context, there are three different products, i.e., product one, two and three, and I wish to offer price discounts when a condition like the following one takes place:

If a customer buys 2 units of product one and 1 unit of product two, I will give him a price discount of x% on product three.

Question
How should I model the situation described above in my database structure?


texts: SQL Statement Layer Filter Queries Limit Access

texts:
Goal: Want to create a so called "SQL Statement Layer" which checks/filters all incoming queries. If incoming query is invalid, should be denied.

Use-case: Limit database access and avoid unrevertable queries. No duplicate code in multiple applications about the same "triggers or procedures" (centralized).

Research so far:

GRANTS/Privileges
limit to Stored Procedures

Description: I am standing before a new project and we want to limit and minimize the access to the database. For example: The developer can create a DROP query inside the Application layer but the database will deny this query because DROPqueries aren't allowed.

I worked some years ago with Laravel and did most of the "procedure and trigger" work inside the Application Layer like the Controllers instead of using a Mutator or Accessor. Now we want to cut it down to the lowest possible level to avoid duplicate code like Mutators and Accessors in different Applications.

Question: Are Grants and stored procedure the only way? If not, which ones?


texts: How to get prior data in a missing dates in sql table

texts:
How to get prior data from a sql table?

In a table we have data as following and don't have data on holidays and weekends.

rate column         ratevalue

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)

01/03/17              1.2240 (Tuesday)

My requirement is to get data based on prior day as below

12/29/16              1.2266 (Thursday)
12/30/16              1.2345 (Friday)
12/31/16              1.2345 (Saturday)
01/01/17              1.2345 (Sunday)
01/02/17              1.2345 (Monday, New Year Holiday)
01/03/17              1.2240 (Tuesday)

Similarly the logic applies to any holidays where the rate doesn't get picked up. It should show the previous day's value.


texts: Query timeout expired error on SQL Server via ASP page

texts:
I am getting the error

  [Microsoft][Sql Server Native Client 11.0]
  Query timeout expired

on SQL Server via ASP page. Most of the time it is working fine but sometimes I am getting this error. Can anyone tell me If this a SQL Server issue or ASP.NET issue?

I have run the same query in SSMS, it completed within seconds. ASP page can query anything. I am using sa user for auth, so I think it has full permissions.


texts:
we have an SSIS job that inserts data into a table in server A from data in table which is located in some other server B.

For this,we are making use of dblinks to connect to other database . But, this is causing lot of performance issues and job is failing because it is taking lot of time to execute the query.

So, i would like to know , what are the alternatives for Dblinks. The one thing i got is , using Materialistic views .

BUt i am not sure , how that works ?  Please suggest..


texts: database inaccessible and stuck in startup state

texts:
Situation

I have 7 databases which are inaccessible.
In the sql server error log, I see them starting up but they never
become available.
In sysprocesses, I see them stuck in IO_RETRY wait type.

select spid, lastwaittype,cmd, status from master..sysprocesses order by spid;

Effects

The above situation is causes CPU consumption which increases over time.

What do I want now?

Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods.

How did they end of in such a state

We used Idera Virtual restore tool to create these dbs from a backup
file.
We renamed those DBs using SSMS directly.
DBs were still
accessible and readable.
We altered some data on those dbs.
Now DBs
became inaccessible.
We kills spids and restarted sql server without
patience.
Now Idera virtual restore service cannot remove them,
neither can we drop them.
Sql server cannot bring them up.


texts: PostgreSQL join subquery can't restrict query

texts:
I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.

I'm trying to run a query that will return an individual users 'amount owed' across all groups (to which they belong).

At the moment the query looks like this:

SELECT
    SUM(owe)
FROM (
    SELECT
        (expenses.amount/count(*))
    AS owe
    FROM expenses
    LEFT JOIN user_expenses
    ON (
        expenses.id = user_expenses.expense_id
        )
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    )
AS total;

The problem is this query retrieves ALL expenses across the board. What i'd like to do is restrict by using:

WHERE user_expenses.user_id = 1

How can I add that to the aforementioned query (in a subquery) to restrict the result to expenses that only that user is associated with, but still get all users involved in an expense so i can do the math for amount each member owes?

TABLE SCHEMA (if that might help):

USERS
id
name
username
email

EXPENSES
id
created_at
updated_at
title
amount
group_id
paid_by
img_url
note

USER_EXPENSES (join table)
id
expense_id
user_id

GROUPS
id
created_at
name
desc

USER_GROUPS
id
user_id
group_id

Some more sample info for clarification:
if i run this query -

SELECT
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id
      WHERE e.paid_by != 1
      GROUP BY e.id;

i get something like this (with dummy data):

If I run the following:

SELECT
    e.title,
    e.amount,
    (e.amount/count(*)) AS owe, count(*) AS members
      FROM expenses e LEFT JOIN
           user_expenses ue
           ON e.id = ue.expense_id AND ue.user_id = 1
      WHERE e.paid_by != 1
      GROUP BY e.id;

I get this (note the members count):


texts: MySQL Enterprise Backup error: Log scan was only able to reach xxxx

texts:
I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.

I get this error:

mysqlbackup: ERROR: Log scan was only able to reach 1667072,
           but a checkpoint was at 1667505.
           This means that the database server has overwritten
           a part of the circular InnoDB log files before ibbackup
           was able to read it.
           To solve this problem either:
           1) re-run the backup when the database has less load, or
           2) re-configure the database with bigger InnoDB log files.

Config:

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
datadir = /common/mysql/xxx/data/
innodb_data_home_dir = /common/mysql/xxx/data/
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/xxx/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
datadir = /common/mysql/testb/full/latest/datadir
innodb_data_home_dir = /common/mysql/testb/full/latest/datadir
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/testb/full/latest/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400


texts: Why NOSql should be more performant than RDBMS under structured data loads?

texts:
The main reason NoSQL dbms have become so common is the need of storing unstructured data where a Relational database instead would require carefully designing a database schema and fitting data into it.

What I don't understand is whether they are generally more performant than traditional relational databases and why.
This of course under loads of data which both RDBMS and NoSQL can manage (like say a simple row with id,value,date).

To be more specific several threads on StackOverflow answering to questions like "What database can handle Millions rows" like SO question and SO question 2 always end up suggesting NoSQL databases.
But apart from the specific structure of data I can't see why a NoSQL should be more performant than a traditional RDBMS.

Suggesting a RDBMS which can deal with millions of rows of structured data would also help.


texts: Make MySQL server terminate when shell exits or on ctrl-c

texts:
If I run mysqld, ie invoke it directly on the command-line, it survives the shell being closed or hitting ctrl-c.

Is there any way to run it "interactively", ie so that it will shut down if either of those things happen?

(I'd find this useful during development as I have a tmux script to quickly bring up and down various servers.)


texts: Order by numbers followed by alphabet and then null values

texts:
I have below column in my database:

abc
1
2
null
100
6

I want to sort and show these values as per below sequence:

1
2
6
100
abc
null

Anyone please help me to solve it.
Consider below query:

select * from test_table where status=1 order by test_column DESC

OR

 select * from test_table where status=1 order by if((test_column = '' OR test_column IS NULL),'999999',test_column) DESC


texts: MySQL update row cell with 0 value with value from closest row that does not have 0 value

texts:
I have a list of entries that has a time field where unix timestamp is stored.
I recently discovered that some of the entries have that field value 0.
I want to run an update that will update those records with the value of the closest row that does not have 0 value.

This is what I have built so far but I am unsure it will use the value of the closest record.

UPDATE `records` AS `m`
  JOIN `records` AS `s` ON `s`.`time` > 0
   SET `m`.`time` = `s`.`time`
 WHERE `m`.`time` = 0

My schema.

CREATE TABLE `records` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `string` VARCHAR(254) NOT NULL DEFAULT '',
    `time` INT(11) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `time` (`time`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM


texts: MySQL Replication - Used to work, now get error 2026

texts:
I've had replication running now for about 2 years, with 1 master and 2 slaves. Both are through an SSL connection. I'm using MySQL 5.5.15 on the master (CentOS), and MySQL 5.14.46 on the slaves (on a Windows machine - don't ask).

Last week both slaves stopped replicating off the master, giving me an error code of 2026 and saying that they can't connect to the master. Looking in the error logs, both stopped being able to connect to the server at the same time - 16:46 in the afternoon.

I was here, I'm the only system admin and I was fiddling with anything on the server. In fact, it has run smoothly now for a long time. The SSL certificates appear to still be set up correctly in the master.

Has anybody else had a problem like this?


texts: Can I access the file stream shared folder using Listeners in case of AlwaysOn cluster environment?

texts:
One of the most effective ways to extend your SQL Server Integration Services (SSIS) control flow is to use a Script task to write custom code that perform tasks you cannot perform with the built-in components.But it's not straight forward in case of AlwaysOn configured nodes

Below is my current environment setup which helps you to understand the problem.

My environment

I am having NODE 1 and NODE 2 as cluster configured
SQL Server always on and availability group configured
SQL Server file stream is enabled on both node 1 and node 2 entitled as MYSHAREDNAME
Internal load balancing Listener configured and entitled as MYACTIVENODE

Problem Statement
One of the node among cluster (node 1 or node2) can be down any time. We will not sure which one will become PRIMARY. I am trying to access the shared folder as follow

\NODE1\MYSHAREDNAME - Works if NODE 1 is primary node
\NODE2\MYSHAREDNAME - Works if NODE 2 is primary node

But it's difficult to hard code above path as any node can go down. So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

So I used LISTENER name so that it can automatically detect the PRIMARY node to do the desired job as shown below

  \\MYLISTENERNAME\MYSHAREDNAME

But I am not able to access it

How can I fix it? Ports 1433, 5022, 59999 are enabled.

Extract from https://msdn.microsoft.com/en-in/library/dn385720.aspx

  AlwaysOn Availability Groups are supported as long as you do not add new database files to the primary database. If a database operation requires a new file to be created in the primary database, first disable AlwaysOn Availability Groups in the secondary node. Then, perform the database operation on the primary database and backup the database in the primary node. Next, restore the database to the secondary node, and enable AlwaysOn Availability Groups in the secondary node. Note that AlwaysOn Failover Cluster Instances is not supported when using the SQL Server Data Files in Windows Azure feature

But I did not understand what does above statement means?


texts:
We have a database (SQL Server 2008r2) that is being transactionally replicated, namely tables, sprocs, views, indexed views and UDFs only, to another server (SQL Server 2014) as part of a cautious (read: slow) upgrade process. However, we still need to deploy new code or changes to it in the meanwhile.

Whenever we try to deploy new/changed code to it, using DACPAC under Visual Studio, we get a slew of the following errors:

Error SQL72035: [dbo].[SomeTable] is replicated and cannot be modified.

Is there anyway to avoid this? I was thinking if we could script the drop and create of the replication, we could put it in pre and post-deploy script, respectively, but I'm not sure if a) that is a good approach, and b) common practice.


texts: Postgresql-Server won't start after moving data directory

texts:
I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start.
I.e. with
sudo service postgresql restart && sudo service postgresql status

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2017-11-06 10:31:32 CET; 1min 42s ago
  Process: 8962 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 8962 (code=exited, status=0/SUCCESS)

Nov 06 10:31:32 HERCULES systemd[1]: Starting PostgreSQL RDBMS...
Nov 06 10:31:32 HERCULES systemd[1]: Started PostgreSQL RDBMS.

The logs for both versions then contain the following:
pg_ctl: could not access directory "/media/daveknave/BIGFS/PGSQL/9.5/main": Keine Berechtigung

"Keine Berechtigung" translates to "Permission denied".
ls -l /media/daveknave/BIGFS/PGSQL/9.5/main

gives
drwxrwxrwx 5 postgres postgres 4096 Sep 25 21:42 base
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 global
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_clog
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_commit_ts
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_dynshmem
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_logical
drwxrwxrwx 4 postgres postgres 4096 Sep 25 21:42 pg_multixact
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_notify
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_replslot
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_serial
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_snapshots
drwxrwxrwx 2 postgres postgres 4096 Nov  3 21:07 pg_stat
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_stat_tmp
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_subtrans
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_tblspc
drwxrwxrwx 2 postgres postgres 4096 Sep 25 21:42 pg_twophase
-rwxrwxrwx 1 postgres postgres    4 Sep 25 21:42 PG_VERSION
drwxrwxrwx 3 postgres postgres 4096 Nov  2 10:19 pg_xlog
-rwxrwxrwx 1 postgres postgres   88 Sep 25 21:42 postgresql.auto.conf
-rwxrwxrwx 1 postgres postgres  133 Nov  3 21:07 postmaster.opts
-rwxrwxrwx 1 postgres postgres  100 Nov  3 21:07 postmaster.pid

I've set the file permissions in an attempt to solve this, however no result.
Trying to access the server using
sudo -u postgres psql -W

prompts
Password:
psql: could not connect to server: Datei oder Verzeichnis nicht gefunden
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Can anybody please help? Have any of you encountered that same issue? I've searched using google, but couldn't find any hints to solve my problem.


texts: Galera cluster replication stalls when using wsrep_provider_options repl.commit_order=2

texts:
we are currently testing if moving from mariaDB master/slave replication to a 3-node-galera-cluster is an option for us.

General replication in the cluster works fine like expected. Load test shows that write-performance does not meet our requirements though.
To migrate data from the master/slave-system to the cluster one cluster node is set up as slave to that m/s-system (backup from master is imported, slave replication on the cluster node starts from the point where the backup is taken). The cluster node never catches up to the master (seconds-behind-master is increasing).
So we looked for tuning-options for our cluster. Setting repl.commit_order=2 improved performance, replication from the master to the cluster node went fine. But we then experienced that the other cluster nodes weren't replicating any more (though their state was synced and the wsrep%-variables did not show anything unusual, no throttling), replication within the cluster seemed to have stalled entirely.
When resetting repl.commit_order replication worked again.
Our cluster was not replicating neither when we had just the cluster operational (without replicating from the master node) with repl.commit_order=2.

Here excerpts from the server.cnf:

[galera]
# Mandatory settings
wsrep_provider         = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address  = "gcomm://10.0.0.10,10.0.0.8,10.0.0.11"
wsrep_cluster_name       = foobar
wsrep_node_name          = foobar-db1
wsrep_node_address       = 10.0.0.10
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2

wsrep_slave_threads      = 16
wsrep_provider_options   = 'gcs.fc_limit=80;gcs.fc_factor=0.8;repl.commit_order=2'

# SST settings
wsrep_sst_method         = xtrabackup-v2
wsrep_sst_receive_address= 10.0.0.10
wsrep_sst_auth           = galera:somepw

wsrep_retry_autocommit   = 10

# required since 10.1
wsrep_on=ON

Any ideas?


texts: How to update information of the data of column in the table matching the certain text?

texts:
I have a condition where i need to update the only matched case of the data in the table.
For an example
I have a #test table

Sample data are

Firstname Lastname
ABCDEF    GHIJKL
BFGABCF   FGYUTY
CDFSERABC VVVVVV

I need an update query which match the 'ABC' in the #test table and update that 'ABC' with 'XYZ'.

Resulting data must be

Firstname Lastname
XYZDEF    GHIJKL
BFGXYZF   FGYUTY
CDFSERXYZ VVVVVV


texts: Process to map a transaction table to a status table

texts:
I have a table, TRANSACTIONS, in SQL Server 2012 with ~100000 rows that has the following columns:

TRANSACTION_ID
TRANSACTION_DATE
PERSON_ID
FROM_STATUS_LK
TO_STATUS_LK
FROM_GROUP_ID
TO_GROUP_ID
ORGANIZATION_LK
TRANSACTION_TYPE_LK

I want to map each record to a table, PERSONS, with the following columns:

PERSON_ID
SEQNO
STATUS_LK
GROUP_ID
FROM_DATE
TO_DATE
ORGANIZATION_LK

The new table will have SEQNO's that reset after each TRANSACTION_DATE. For example, if N transactions occurred on October, 20th 2014, then there would be N records in the child table with SEQNO's from 1-N for that date.

What SQL constructs could I use to achieve this?


texts: What are the design alternatives when having multiple "type" tables

texts:
I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically:

user_type (
    id int pk
    label char
)

I could simplify this by doing something like:

labels (
    id int pk
    label char
    context blah
)

but is this an appropriate way to do this?


texts: MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values

texts:
I am creating a stored procedure to search user's Towns.
From the User Interface user will select multiple towns that will be sent to the stored procedure.
Sample input of the towns parameter for the stored procedure would be like orangi,faisal,nazimabad.

Towns that are stored in the table are

Orangi
Korangi
Shah Faisal
Shahrah-e-Faisal
Nazimabad
North Nazimabad

Now I want to select all those above records against that input. That is I want to use LIKE operator with IN from a stored procedure's parameter.

I don't know how many towns would be there in the input.
it could be just one, or two or three or all these mentioned above.

I want to search all the towns against that query

Sample Stored Procedure is

CREATE PROCEDURE `GetUsersByTown`(
IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
      SELECT
      name,
      Town,
      FROM userLocation where Town in (SearchTowns)
END

Expected Outcome

Name ---  Town
Hamza --- Orangi
Jamal --- Korangi
Hasham --- Shah Faisal
Aliyan --- Shahrah-e-Faisal
Danish --- Nazimabad
Farrukh --- North Nazimabad

I have tried to use find_in_set() but FIND_IN_SET does not accept wildcards like % and it doesn't use index?

I have also looked at using REGEXP  ValueA|ValueB but I don't know how to put the input parameter that is comma separated values in that REGEXP


texts: I don't want to have the error when I drop a table although the table doesn't exist

texts:
I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist.

I did this

set echo on

set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off
--
WHENEVER SQLERROR CONTINUE

drop table  CONTINENT;

COMMIT;

EXIT;

but it coudn't skipe the error when the table doesn't exist, how can I fix this problem please


texts: How to merge multiple rows in one column with hierarchy Postgresql 12?

texts:
I have a table like this to save the catalog of 90 services:
-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

I need to change the table structure in order to have the three levels (service, subservice and description) in the same column with its own id and pointing to a new column with the level's number. Namely something like this (note that the id's are something that I made up):
-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

I'm working on Postgresql 12. I have created the new column parent_id and I was trying to do this sequence:
CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

Can anyone please give a rough idea how to transform the structure of the table?


texts: Reducing contiguous values in geographical data to a set of polygons?

texts:
I've got a MySQL table with about 200,000 rows in it. The table has four columns, lat, lng, and value. value is just a percentage, and represents your chances of seeing an aurora in the night sky for a given lat and lng.

I'm wanting to reduce this data into a set of polygons that I can pass to some kind of mapping service (e.g. Google Maps) or pass to a script to draw it to a static image, and I'm wondering if MySQL can do this. All the MySQL spatial tutorials I've looked up deal mostly with point-to-point distance finding (e.g. where's the nearest pub to you?), so I'm a little confused.

Specifically, I'd like to group similar regions together, kind of like this:

So if I requested all the locations that have a 1% chance, I'd get back two records, one for the yellow region, and one for the light blue region.

The data that I'm inserting comes from a text file, 1024 values per line, and 512 lines (each value represents about 0.3515625 degrees) and the value is just a whole number between 1 and 100 (the data is mostly zeroes, so I ignore those to save time / memory / rows needed).

So is there a way for MySQL to do this? Or would I need to do this kind of reduction before inserting the data?


texts: Calling two stored procedures from one, is the order guaranteed?

texts:
I have some code that looks like this:

    EXEC StoredProcedure1 @Arguments

    EXEC StoredProcedure2 @Arguments

Are the two datasets guaranteed to return in the called order? I am expecting two result sets to be passed back to my server code and am running into some issues and I feel the ordering may not be guaranteed. If it is not guaranteed, is there a way to enforce ordering?

Not the ordering of the data, but ordering of StoredProcedure1 always being returned as result set 1, and StoredProcedure2 always being returned as result set 2.


texts:
I ran this command:

cluster verbose address using address_locality_lookup;

And got this result:

INFO:  clustering "dba.address" using sequential scan and sort
INFO:  "address": found 0 removable, 29323623 nonremovable row versions in 1139047 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 26.00s/8286.70u sec elapsed 8342.01 sec.
CLUSTER
Time: 9366585.421 ms

Does that indicate that clustering succeeded (ie all the rows are now in the order indicated by the address_locality_lookup index)?

What do 'removable', and 'nonremovable' indicate?


texts:
We enabled slow_log to debug slow queries. But In slow_log table  sql_text column is BLOB.. It used to work fine before on 5.6 but now shows blob on 5.7

(Clarification)  When saving the slowlog to a TABLE, what happens to BLOBs and TEXT strings that cannot be safely stored in some standard column?  Apparently, TEXT values are stored into a BLOB.  In that case, how do I get back the TEXT in the desired CHARACTER SET?


texts: Why does sql server have a hard time rounding floats?

texts:
if exists (select 1 from sys.tables where name = 'd_ratio') begin drop table d_ratio end
    if exists (select 1 from sys.tables where name = 'elrd') begin drop table elrd end

    CREATE TABLE [dbo].[d_ratio](
        [index] [bigint] NULL,
        [Class] [bigint] NULL,
        [Dratio] [float] NULL,
        [ELR-Act1] [float] NULL,
        [ELR-Act2] [float] NULL
    ) ON [PRIMARY]
    GO

    Insert into D_ratio
    select 0,2503,0.285,0.75,1.33

    CREATE TABLE [dbo].[ELRD](
        [RATE_EFFDT] [date] NOT NULL,
        [CLASS] [varchar](4) NOT NULL,
        [ELR] [decimal](5, 2) NULL,
        [D_RATIO] [decimal](5, 2) NULL,
        [EXPO_ACT] [char](2) NOT NULL
    ) ON [PRIMARY]
    GO

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2),Dratio, 1
    from d_ratio

    select Dratio, * from d_ratio
    select D_ratio, * from elrd

It goes in as 0.285 and comes out as 0.28.

However if I cast it as a decimal with 3 points of precision like so:

    truncate table elrd
    insert into elrd(Rate_effdt, class, elr, d_ratio, EXPO_ACT)
    select getdate(), right('0000' + cast(class as varchar(10)),4), round(cast([ELR-Act1] as decimal(10,3)),2), cast(dratio as decimal(10,3)), 1
    from d_ratio

It goes in as 0.285 and comes out as 0.29 - it gets rounded properly.

Question What is SQL Server doing? Is it truncating floats and rounding decimals?


texts: Why is a .bak so much smaller than the database it's a backup of?

texts:
I just took a backup of a SQL Server database.  The MDF and LDF files together total around 29 GB, but the .bak file was only 23 GB, about 20% smaller.

My first guess when one version of a set of data is smaller than another version containing the same data would be data compression, but compression usually yields a much better compression ratio than 20%, especially for highly-ordered data (such as database tables.) Also, compressed data can't easily be compressed further, but I know that .bak files can be compressed.

So if the data isn't being compressed, and nothing's being discarded, (because the whole point of making a backup is to be able to restore it to an identical state afterwards,) then what's that 20% that's unaccounted for?


texts:
I have a user experiencing problems with mounting/contacted a database. I work in infrastructure so very very little knowledge on db's -- guess I was first/only port of call, so any advice would be  greatly appreciated. I have pasted what he has tried and my extremely basic suggestions :)

Initial error reported:

ORA-00283: recovery session canceled due to errors[oracle@*********** ~]$ sqlplus spsystem/reganam
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 4 07:08:41 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

And when entering as sydba

SQL> select * from all_users;
select * from all_users
 *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Quick search on these errors suggested a recovery of the database was needed and for the user to run the following from the SQL> prompt:

SQL> startup mount
ORACLE Instance started
SQL> recover database
Media recovery complete
SQL> alter database open;
Database altered
SQL> startup mount
ORACLE instance started.

**This was attempted and failed**

Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 771755408 bytes
Database Buffers 2415919104 bytes
Redo Buffers 16904192 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01114: IO error writing block to file 2 (block # 1)
ORA-01110: data file 2: '/oracle/oradata/ora112/sysaux01.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 1
Additional information: -1

There are no disk issues and filesystems are healthy (size & condition) and nothing that I can garner from OS logs or that I can fully understand from trace logs?


texts: The EXECUTE permission was denied on the object xp_availablemedia

texts:
One of my colleagues has Public role on SQL Management Studio but db_owner on a couple of DB. While trying to CREATE back-up, the following message pops up

I have now added db_backupoperator on the DB, still the following error


texts: How to query with maximum number of rows with same value?

texts:
In Mariadb 10.5.8
I have a table with some repeating value e.g.
Item , qty
shoes 3
pants 2
pants 2
pants 4
shoes 3
pants 2
shoes 6
pants 2
shoes 3
pants 2

I want to query up to 3 occurrences of each Item. so it will return
Item , qty
shoes 3
pants 2
pants 2
pants 4
shoes 3
shoes 6

Note it stopped picking each item (shoes, pants) after picking the first 3 occurances.


texts:
For the last few days I've been trying to figure out why my MySQL read slave servers are capped at 1012+1 (+1 for the superuser) connections when I have the max_connections set to 5000. It seems that no matter what I change, the "Historic max_used_connections" never increases beyond 1013.

The mysql read slaves support a high traffic web site. I have a cluster of 3 read only slaves. Each read slave seems to be capped at 1013 max connections. I can take two out of the cluster so all connections go to one and still only 1013. No error messages show up in the mysql log file.

A mysqladmin status on one of the read slaves shows this:
Uptime: 1661  Threads: 128  Questions: 17634224  Slow queries: 0  Opens: 2441  Flush tables: 1  Open tables: 938  Queries per second avg: 10616.630

Same server, mtop shows this:
load average: 0.05, 0.15, 0.19 mysqld 5.1.66-log up 0 day(s),  0:28 hrs
106 threads: 4 running, 906 cached. Queries/slow: 1.6K/0 Cache Hit: 99.96%
Opened tables: 0  RRN: 278  TLW: 1.0K  SFJ: 0  SMP: 0  QPS: 1

Each server has 12 dual-core Intel CPU's (24 cores) with 140 GB of RAM running RHEL 6.3, Kernel 2.6. (x86_64)

Any idea what would be limiting/capping the connections at 1012+1 ?

EDIT
Also wanted to add two things:

1) I am running the "free/community" edition of MySQL that you get from a Red Hat 6.3 YUM install. Could there be some limitation in that version?

2) I've changed the /etc/security/limits.conf file on the MySQL read slaves, increasing values for users mysql and root (did not help my situation):

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1161534
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 10240
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 32768
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


texts: Can a Wallet be used with SQL Developer? How?

texts: I have a Wallet configured and working fine for SQLPlus. Is there a way to make it work with SQL Developer?


texts:
SELECT
ratings.programme_id,
programme.programme_name,
ratings.ratings
FROM
programme
INNER JOIN ratings ON ratings.programme_id = programme.programme_id

I have two tables a and b

a has id and name and b has id and ratings. There can be multiple ratings for each id in a. I want to calculate the sum of ratings for each id.

for example entry john has received ratings of 1,3 and 5 and I would like to get the sum which would be 9


texts: SSIS vs T-SQL Join Performance, Difference Databases on Same server

texts:
We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases.

I suspect for small table joins, the difference is minimal or negligible. In this case, our team prefers T-SQL, easier to code/script than writing diagrams. Additionally in the DevOps perspective, we can compile/build scripts in a DB project;  unfortunately, SSIS will not compile T-SQL correctly, I can write 'testabcd' in an SSIS Execute SQL statement, and the project will still build/compile.

However, for a large number of rows which will take longer processing, what is quicker? T-SQL which has indexes and statistics,  or SSIS which is conducted all in memory?

I read these articles with different viewpoints, the team is trying to gain consensus.

https://derekdb.wordpress.com/2012/03/13/ssis-lookup-or-t-sql-join/
http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/

Let's assume, T-SQL Engine and SSIS are provided same hardware: CPU and Memory. Given the same specs, I would like to know performance speed on an internal algorithm perspective.


texts: Why does a create database statement return values when called from C#?

texts:
I have the following database command:

CREATE DATABASE [test1] ON
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1.mdf'),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\test1_log.ldf')
FOR ATTACH

When I do it via C# it returns me more than one value. Here is the C# code:

public void RunQuery(string query, bool longRunning = false)
{
    using (var cmd = new SqlCommand(query, Connection))
    {
        cmd.CommandTimeout = longRunning ? _longRunningCommandTimeout : _commandTimeout;
        cmd.ExecuteNonQuery();
    }
}

This results in the following error from SQL Server:

  Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated

And here is the stack trace from the exception:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

The database attach succeeds, even though an error is raised.

When I execute the same query (under the same user) via Management Studio it gets me just the following:

  Commands completed successfully.

Is it possible that the CREATE DATABASE statement returns more than one result?


texts: Relationship between file shrink and used memory in SQL Server

texts:
Some background info: this is a test SQL Server 2012, with restored databases from production servers, all of them with over 20% space available in the data file, up to even 50%. I'd rather restore as many databases on this test server than waste the limited storage space. So decided to shrink the data files.

The way I see it with sys.dm_exec_requests, it seems to be a connection between the reported percentage_complete for the shrink operation and the memory taken up by SQL Server. It looks like when SQL Server grabs all the available memory (configured maximum memory), the shrink slows down to a crawl. I assume the server tries to load as much data in memory to save I/O ?

Killing the shrink process seems to clear the memory (doesn't release it to the OS, but as I restart the data file shrink - it's fast again). Adding more memory to the OS helps as well. But for databases in the hundreds of GBs, it's a painful process.

Is there any proper way to do this ? Maybe an option to release the free space when restoring the backups ? Drop the indexes before the shrink and rebuild once done ?

Any good resources to read and understand how the shrink works, or on how the data files are organized ?


texts: Index not being used for logical operation


texts: how to mongorestore mongoDB 4.2 to documentDB 4.0?

texts:
I need to do mongorestore from my mongodb 4.2 to DocumenDb 4.0 compatible i got the command from aws
which is this
mongorestore --ssl --host="abc.aws.com:27017" --username=user --password=pass --sslCAFile rds-combined-ca-bundle.pem dump

but it is showing me this error
[1] 1120
-bash: dGFp: command not found
[root@mongo-tf-doc01 dump]# 2021-04-06T12:10:48.434+0000        error connecting to host: could not connect to server: server selection error: server selection timeout
current topology: Type: Single
Servers:
Addr: abc.aws.com:27017:27017, Type: Unknown, State: Connected, Average RTT: 0, Last error: connection(abc.aws.com:27017[-121]) connection is closed

I am able to connect documentdb successfully so i know this is not a connection problem but error occurs when i restore


texts:
I have a fun problem,

I have data stored in SQL Server 2008 VARDATA column as Hex.

I need to see what this translated to string is.

I have tried CONVERT(VarChar(MAX), VARDATA, 2) and it returns a Varchar value of the hex exactly the same as it is in the VARDATA.

If I put the value that is in my VARDATA into an online converter I get the exact value I expect back.

Any help would be appreciated.

Details.

Example of the Hex is

0x003200300031003400300037003200340030003000300035003600380038003300300039

It should work out to.

201407240005688309

EDIT: some additional information. This data starts out in ORACLE it is in a RAW type column.

We bring the data to SQL Server 2008 via Linked Servers using the below open query.

Select TABNAME, VARKEY, DATALN, VARDATA
from sapsr3.KAPOL

When I use the suggested function on this SQL Server 2008 table below.

Select CONVERT(NVARCHAR(MAX),VARDATA)
from KAPOL

I get these symbols:

㈀　　㄀　㄀　㄀　　　　　　㜀㜀㠀㠀

If I do the conversion of HEX in Oracle first and then try and bring it over I get an empty column.

Select TABNAME, VARKEY, DATALN, utl_raw.cast_to_nvarchar2(hextoraw(VARDATA)) VARDATA
from sapsr3.KAPOL

Lastly, in the first instance with no conversion in the oracle table the data type for the column VARDATA is VARBINARY

When I try the conversion in Oracle first is NVARCHAR


texts: Can you create a table param WITHOUT a pre-defined TVP in a SP?

texts:
In the body of an SP I have the following line of code:

DECLARE @assetCode NVARCHAR(50) = ( SELECT DISTINCT [Asset Code] FROM <some table> WHERE hProp = xxx )

Is it possible to do the same with a TABLE variable type? i.e. something along the lines of:

DECLARE @tbl TABLE = (SELECT x,y,z FROM ....)

I have seen something similar to this in a Table-valued function:

RETURNS TABLE
AS
RETURN
(
    SELECT
    *
    FROM
    (   SELECT
        ... etc


texts: Creating/restoring mdf/ldf to non-default file location giving access denied

texts:
Default folder is typical C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA. But C: drive on server is almost full.

So, in restoring an 11gb database from another server, I decided to use the L: drive on the same server.

Tried to restore to L:\SQL\Data (a folder I created). Access Denied.

Some quick Googling later, I see that the SQL Server service is started by NT SERVICE\MSSQLSERVER, so I make MSSQLSERVER the owner of the L:\SQL\Data folder and give it full permission to the folder.

Try to restore again. Access Denied.

Hm. Try to create NEW database in same location. Access Denied.

Create new database in default folder, detach, move mdf/ldf to L:\SQL\Data, making sure all folder and file permissions and ownership are set to MSSQLSERVER, then attach using CREATE DATABASE ... FOR ATTACH. Database created in read-only mode.

Try to change to read/write mode. Access Denied.

Create a folder datax in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL and give it appropriate permissions/ownership. Create a new database there. It worked!

Move back one folder and created a folder data in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER and give it appropriate permissions/ownership. Create a new database there. Access Denied.

Create a folder C:\temp\data and give it appropriate permissions/ownership. Access Denied.

SQL Server service has been restarted. Server has been restarted. Same results.

This last bit with xp_cmsshell might be a bit of a diversion...

So... tried EXEC xp_cmdshell 'md l:\data'; Message back is: "An error occurred during the execution of xp_cmdshell. A call to 'CreatePipe' failed with error code: '5'."

Changed permissions on root of L:, tried again, same result.

EXEC xp_cmdshell 'dir c:*.*'; Same result.

EXEC xp_cmdshell 'whoami /all'; Same result.

Anyway, bottom line is, everything I've read out here about getting an "access denied" when restoring/creating db is because of folder permissions. But best I can tell, those are correct. I even tried some differences with permissions being inherited versus explicit.

A couple links I've come across would indicate that there is no inherent restriction on putting data/log files anywhere I want, i.e., outside the default location.

I haven't tried changing the service account for the SQL Server service because I don't have that level of freedom on this server.

Also, this is from SSMS on both local machine and directly on server, and always connected under the context of an administrator on the domain.


texts:
I have already set the environment in windows

SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';

Even I tried by adding following in environment path-

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

But still I am getting error:

  ERROR: rt_raster_to_gdal: Could not load the output GDAL driver SQL

The query I am running is

SELECT ST_AsGDALRaster(rast, 'GTiff') As rastjpg
FROM myRaster WHERE rid=1;

How to remove this issue?


texts: v$logmnr_contents with username as unknown

texts:
I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".

Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?


texts: Can you see other users' session variables in MySQL?

texts:
In MySQL, Using SHOW GLOBAL VARIABLES gives the ability to see global variables.
SHOW SESSION VARIABLES are the variable settings for that session.

Is there a way to see the SESSION variables of other active, connected users, from a superuser connection? For example, is there a query or view that would show me which active sessions have variables set differently from the global variables?


texts: Adding Partitions to cube In SSAS via VS 2013

texts:
I am trying to add a new partition to my cube via SSDT 2013.  I am using the Wizard and doing all stages that needed. After I click "Finish" nothing happens and I cannot see that a new partition has been added.

The answer I found from Google is to update SSDT to the latest version.  I did that and still nothing happens.

Anyone encountered this problem? Any ideas?

My SSDT version is 12.0.60525.0


texts: EM Express page of Oracle 19c is not opening

texts:
I have setup the https port for EM express in the CDB database:

exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500)

However while opening the EM express page https://hostname:5500/em/ the browser is throwing "Secure Connection Failed" error. I am using Oracle 19c EE and firefox browser. all services including xdb> is up and running.

Can you someone help me resolving this issue?

Adding the lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-MAR-2020 03:14:51

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-MAR-2020 00:07:11
Uptime                    0 days 3 hr. 7 min. 41 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/product/19.0.0/grid_home/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/ol7-orcl-19c-db-01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-orcl-19c-db-01.db.net)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ol7-orcl-19c-db-01.db.net)(PORT=5502))(Security=(my_wallet_directory=/u01/app/oracle/product/19.0.0/db_home/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ol7-orcl-19c-db-01.db.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/19.0.0/db_home/admin/cdb1/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_FRA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca.db.net" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "a2067c18400e18e5e055c4a9f9e8cd11.db.net" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1.db.net" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB.db.net" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "ora19cr3.db.net" has 1 instance(s).
  Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: Preparing query in postgreSQL with variable table name?

texts:
I'm trying to do an INSERT INTO a table whose name is in the variable myTable.

In MySQL, I'd do it like this:

SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I checked into postgreSQL documentation for prepared statements, and I couldn't find any similar examples.

I can assemble query into a string:

sql_query := 'INSERT INTO ' || mySchema || '.' || myTable || ' SELECT action_name, user, ';

But how do I execute it?


texts: T-SQL tool that requires a semicolon to end statements

texts:
I have what is probably a rather unusual question. Is there a T-SQL tool that requires using a semicolon to terminate statements? I know that it's not required to use semicolons with SQL Server 2008 R1, which is what I'm connecting to, but I actually would like to be forced to use it for running update queries. I'm sure we're all aware of the dangers of writing a query like the one below in the middle of a sql pad, and then highlighting it and running it (which is unfortunately something I tend to do a lot):

UPDATE dbo.Customers
SET ZipCode = '40223'
WHERE Name = 'Bob Loblaw'

... because you only have to miss-select your text once to nuke all your customer's zipcodes!

So, ideally, I'd like to continue doing most of my sql work in SQL Server Management Studio, but have a separate pad open (in a tool that requires semicolons) and only allow myself to write data manipulation queries over there. This would give me great peace of mind, because I'd know I could never miss-select my commonly used update queries. If I did, it wouldn't run, because it wouldn't have a semicolon.


texts: script to find all bigint that can be converted to int fields in postgresql performance

texts:
I want to compact a postgresql 11 db and my idea is to convert lots of bigint to int data types if and only if there is a reasonable lag to the limit of int data type considering the data inside.

So, can you help me in finding a script to find all bigint that can be converted to int fields in postgresql ?


texts: On Amazon RDS, what consumes storage space beyond the database files?

texts:
I recently right-sized the data and log files for an RDS database, leaving just a few GB of unallocated space.  Shortly thereafter, something allocated, then deallocated, 1 GB of the margin of error.  What might have caused this?

All of the databases - including tempdb - are either tiny (MB) or at exactly the size they've been set; they add up to 197 GB, leaving 3 GB free on a 200 GB instance.  And yet:

Clearly, if this happens again in a slightly greater magnitude, there will be a problem.  For now, I can simply shrink one of the database files to give a larger margin, but I'd like to understand what actually happened.


texts: In MySQL why is the first batch executed through client-side prepared statement slower?

texts:
I have around a million lines to be inserted into a MySQL (InnoDB) database, so to speed things up I turned to batch/multi-line insert. To be specific, I connect to the database (I'm using Java, so it's done through Connector/J) with rewriteBatchedStatements=true appended at the end of URL, then I initialize all the required prepared statements with prepareStatement(). I use addBatch() to add lines into individual prepared statements and call executeBatch() when a certain number of lines is reached. After several calls of executeBatch(), I will do a commit.

In terms of final result everything is good, but I'm very curious about a behavior. Suppose I set the threshold of when to call executeBatch() to 10,000 lines, then during processing, the first call of executeBatch() will be significantly slower than the subsequent calls (in my scenario about 5 sec vs. < 1 sec). It feels as if the MySQL server is still "preparing" something.

From what I know, PostgreSQL has an option like setPrepareThreshold to set the number of queries issued before actually compiling the SQL statement. Is MySQL doing something similar? How can this delay be mitigated or is this behavior native to MySQL implementation?

EDIT (Some more background)

Below is one of the tables I will insert records into (others have similar schema).

'CREATE TABLE `flow_hourly` (
  `datetime` datetime NOT NULL,
  `customer_id` varchar(7) CHARACTER SET utf8mb4 NOT NULL,
  `pages` bigint(14) NOT NULL,
  `hits` bigint(14) NOT NULL,
  `bandwidth` bigint(14) NOT NULL,
  `nvpages` bigint(14) NOT NULL,
  `nvhit` bigint(14) NOT NULL,
  `nvbandwidth` bigint(14) NOT NULL,
  PRIMARY KEY (`datetime`,`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Since this database is for development purposes, the current maximum size is also around a million records, but in production the rate of growth should be relatively fast (n*100K+ inserts per day).

One may guess from the schema above that I'm creating a database to record server traffic information. For each time interval (e.g. hourly, daily, monthly...) there is a dedicated table so that queries on different time intervals can be sped up accordingly. I receive traffic information on an hourly basis, so except for the hourly tables, I do INSERT ... ON DUPLICATE UPDATE to accumulate values.


texts: PostgreSQL DB error could not create shared memory segment: Cannot allocate memory

texts:
We have PostgreSQL 9.2 on AWS with 16G memory (no swap) we have following kernel setting

kernel.shmall = 6802050
kernel.shmmax = 13930597974
vm.overcommit_memory = 0
vm.overcommit_ratio = 50

our DB is very small because all it does just store some key value for apache/php to run application but we have very high volume traffic on web servers, recently we notice kernel start killing postgresql with OOM error so we have tried following option

vm.overcommit_memory = 2

Notes: This is what i was try to do https://serverfault.com/questions/141988/avoid-linux-out-of-memory-application-teardown

but when i try to start postgresql i got following error

Sep 22 13:18:25 db.world.com postgres[874]: [1-1] FATAL:  could not create shared memory segment: Cannot allocate memory
Sep 22 13:18:25 db.world.com postgres[874]: [1-2] DETAIL:  Failed system call was shmget(key=5432001, size=9210265600, 03600).
Sep 22 13:18:25 db.world.com postgres[874]: [1-3] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMALL.  To reduce the request size (currently 9210265600 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
Sep 22 13:18:25 db.world.com postgres[874]: [1-4]    The PostgreSQL documentation contains more information about shared memory configuration.

so i tried to reduce shared_buffer to 6GB and start postgresql and it came up successful but as soon as load start it start throwing out of memory error again. any suggestion


texts: Using SQL Server Profiler to track performance for nested stored procedure

texts:
I have a slow unit test (defined as a SQL script) executing stored procedures nested in other stored procdedures.
I'd like to know if SQL Server Profiler provides tracks for execution along the 'call stack' at different nesting levels.


texts: Any program / command to see a preview of .sql dump file?

texts:
If I have [multiple] .sql dump files, is there a command or tool that would allow me to see a preview of the file ?

e.g: db-preview backup.sql

    tables (40)
    ----------
    - users (1200)
    - articles (450)
...


texts: Querying system tables from Linked Server

texts:
I am trying to query the syspublications table on Linked Server A from Server B. Both servers are running SQL Server 2012. Using this simple query on Server B:

SELECT a.dest_table
FROM [ServerA].<DBNAME>.dbo.sysarticles a , [ServerA].<DBName>.dbo.syspublications p
WHERE a.pubid = p.pubid AND p.NAME = 'my publication name'

I receive the following error:

Msg 7314, Level 16, State 1, Line 152The OLE DB provider "SQLNCLI11" for linked server "Server-A" does not contain the table ""<DBNAME>"."dbo"."sysarticles"". The table either does not exist or the current user does not have permissions on that table.

To rule out permissions issues, I have set up the Linked Server A to authenticate with a user who has system admin permissions on Linked Server A. I can query all user-defined tables without issue.

What obvious thing am I overlooking?


texts:
I have MySQL community version 5.5.50 running in linux having 4-5 databases on it.

Requirement is to upgrade to Percona Server 5.6.31-77.0.

What are the pre-requisites that I need to check before upgrading? Are there any common errors that i'm likely to face?

I'm simply planning to backup the databases. Remove the MySQL binaries. Install the new Percona Server and restore the databases.


texts: Hyper V replication - vs - sql replication solutions

texts:
I need to establish an offsite copy of a number of production servers, including SQL server.

Hyper V replication has been chosen as a simple solution (all servers are virtual). This is to supplement offsite backup for disaster scenarios. We do not need instant failover, mirroring or any advanced setup, just a reasonably recent copy of the most critical data and systems.

Hyper V replication looks simple, set-and-forget (just make sure to put tempdbs on a non-replicated VHD, set flags to maintaing write order across VHDs). But I am still concerned of efficiency and robustness, and thinking that a simple log shipping setup might be better, easier on the bandwith (would not need to replicate anything but the logs)

Any opinions or insight on what (when) to choose one over the other would be welcome


texts: Why tkprof utility is not adviced to be used in production setups? What concerns does it have?


texts: Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

texts:
I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns.

At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant amount of time. For example running the below command takes well over 15 mins:

SELECT * FROM tbl WHERE column_a = 'value1' AND column_b = 'value2';

The table is mostly used for reads. The only time the table is written to is during weekly updates which insert about 15 million rows.

What are the best ways to manage tables so large? Would you recommend splitting it by year?

The table size is 542 GB and the external size is 109 GB.

EXPLAIN (BUFFERS, ANALYZE) output:

"Seq Scan on table  (cost=0.00..116820941.44 rows=758 width=92) (actual time=0.011..1100643.844 rows=667 loops=1)"
"  Filter: (("COLUMN_A" = 'Value1'::text) AND ("COLUMN_B" = 'Value2'::text))"
"  Rows Removed by Filter: 4121893840"
"  Buffers: shared hit=2 read=56640470 dirtied=476248 written=476216"
"Total runtime: 1100643.967 ms"

The table was created using the following code:

CREATE TABLE tbl (
  DATE     timestamp with time zone,
  COLUMN_A text,
  COLUMN_B text,
  VALUE_1  double precision,
  VALUE_2  double precision,
  VALUE_3  double precision,
  VALUE_4  double precision,
  VALUE_5  double precision,
  VALUE_6  double precision,
  VALUE_7  double precision,
);

CREATE INDEX ix_table_name_date ON table_name (DATE);


texts: Force PostgreSQL clients to use SSL on Amazon RDS

texts:
I'm running a PostgreSQL 9.4.x instance on Amazon RDS.  I can successfully connect to that server using SSL using a command line similar to this one:

psql "host=x.y.z.rds.amazonaws.com dbname=aaa user=bbb sslmode=verify-full"

How can I force clients to use SSL (to make eavesdropping harder)?  The following should fail to connect:

psql "host=x.y.z.rds.amazonaws.com dbname=aaa user=bbb sslmode=disable"

As far as I can tell, on Amazon RDS you can't edit pg_hba.conf, so I can't put hostssl entries in there.

This AWS blog post suggests how it could be done with MySQL, but I can't find links to similar information for PostgreSQL.

My motivation is to prevent myself (or other users) from accidentally transmitting sensitive information unencrypted across the network.


texts: Refactoring 80 GB table that stores massive amount of calculated results

texts:
Trying to evaluate architectural changes to improve the performance of an app that calculates a lot of individual values and stores each of these results as a row. The storage size for one of the usages is 80 GB. I'm used to a artificial key driven warehouse, and this utilizes a natural key, so I'm considering various approaches.

The table structure is like this.

   create table dbo.ItemTable
    (
    Column14 [BIGINT]        not null
   ,Column15 [BIGINT]        not null
   ,Column1  [INT]           not null
   ,Column16 [NVARCHAR](max) not null
   ,Column17 [INT]           not null references AnotherTable1 ([Id])
   ,Column18 [INT]           not null
   ,Column19 [INT]           not null references AnotherTable2 (Id) default 0
   ,Column20 [NVARCHAR](50)  null
   ,constraint [PK_AccountItems] primary key clustered
    (
    Column14 asc,
    Column15 asc,
    Column1 asc
    )
    )
    on Column21 textimage_on Column21

go

create table dbo.ItemValues
    (
    Column14 [BIGINT]   not null -- THIS DOES NOT PROVIDE UNIQUENESS. IT IS SOLEY FOR CLUSTERING THE RELATED GROUPS OF COLUMN15 TOGETHER.
   ,Column15 [BIGINT]   not null
   ,Column16 [INT]      not null
   ,Column17 [TINYINT]  not null
   ,Column18 [DATE]     not null
   ,Column19 [SMALLINT] not null
   ,Column20 [SMALLINT] not null
   ,Column21 [FLOAT]    not null
   ,Column22 [FLOAT]    not null
   ,constraint [PK_AccountItemValues] primary key clustered
    (
    Column14 asc,
    Column15 asc,
    Column16 asc,
    Column17 asc,
    Column18 asc
    )
    )
    on Column23

go

The key issues I see:

Bigint datatype. These columns are approx 10GB in storage cost alone. They could be reduced to INT and save a lot of space
Float takes up additional storage when we might only need to use a 5-6 decimal precision level. I'm thinking to identify our max requirement here and then switch to decimal if possible to reduce size of the float columns.
Column14 on ItemTableValues is purely for clustering the related Column15 together in close promoximity. This was done before I got here, since all Column15 rows are "grouped" when queried. Not sure that the cost of having this column purely for clustering records together offsets the cost of maintaining and storing.
Refactoring the key and table to provide a narrower table with artificial key and a separate mapping to these keys.

Any other thoughts ideas? This has a huge performance impact when dealing with millions of rows being inserted, queried, and then deleted. I'd like to see if I could optimize to reduce the IO thrashing this causes.

Some of the indications of possible issues: (dmv's 25 days since last reboot)

ItemValues: In Top 12 of indexes with most latch contention. Page latch wait in ms = 45,635, with wait count 247
ItemValues: 2nd place of indexes with most lock promotion count - 953 instances
ItemTable 2nd place for index under most row locking pressure, with 405,759 lock wait ms  in last 3 weeks
ItemValues table #1 for most page io latch contention, with 3188168 count and 810432461 ms in last 3 weeks.
ItemValues #3 for most page splits with 68528 leaf allocation count and 42,594 nonleaf allocation count.

Usage patterns on this table

Insert & Delete upon the core AccountCalculations being updated. At this time, it completely deletes and then reinserts the data with no difference comparison between the two. I have a goal to get this difference working, but it requires some big app changes.
At one time the insert/delete could range from 8000 rows to 36,000 rows. This is a full insert/delete. (I will be examining a first step of using merge to try and reduce the activity on this)
A significant number of reports query these details. All the accounts pulled are typically from the same Column14 grouping, and a child to Column15 (Account). All 8000-3600 rows may end up needing to be loaded by the application for calculations/display.


texts: Oracle 11g: performance improvements of inserts

texts:
I have a table of 500 millions of rows (and growing)

I did the following to improve performance of inserts:

On database side:

dropped all indexes and constraints
disabled logging

On application side:

switched from JPA managed entities to native insert queries, added APPEND Oracle hint to the query
tried to commit in batches per 1k/2k/3k of rows
tried to write in parallel (multiple threads, thread count = to core count on server) to one table

This gave me about 300 rows per second

Additionally tried:

write in parallel in batches to multiple tables (to group then back results using UNION)

This gave me about 1k rows per second, but on empty tables. But when I filled tables with dummy data (200 of millions each), speed of inserts dropped to 250 - 300 per second.

Could anyone suggest what else can I do to speed-up inserts? Basically I want to understand what is (what could be) the bottleneck first.

UPD:
Table is partitioned by insert date, table has about 60 columns - most of columns are VARCHAR2(2000 BYTE)


texts: Index optimization for IN, BETWEEN, ORDER BY and LIMIT query

texts:
Brief Background of issue

We have a aggregate table to store activities and have currently 6.6 Million rows and we need create listing from this table.

This activity listing have features of filtering as well as sorting on different different columns.

Data in this table Meaning Activities count is increasing at 1 million/month rate.

We are using MySQL with version 5.6

Table structure is as follows

CREATE TABLE `filter` (
  `source_id` bigint(20) unsigned NOT NULL,
  `entity_id` varchar(40) NOT NULL,
  `type` int(11) NOT NULL,
  `metrics_1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `metrics_2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `metrics_3` int(11) unsigned NOT NULL DEFAULT '1',
  `posted_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  PRIMARY KEY (`source_id`,`type`,`entity_id`),
  KEY `indx_posted_on` (`posted_on`,`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Primary Key for this table is

PRIMARY KEY (`source_id`,`type`,`entity_id`)

Query we are using to retrieve list of activities is

SELECT
    filter.`entity_id`
FROM filter AS filter
LEFT JOIN act_deleted AS act_deleted ON act_deleted.entity_id = filter.entity_id
WHERE
    filter.source IN(211,493)
    AND filter.`type` = 1
    AND filter.posted_on BETWEEN '2015-01-29 00:00:00' AND '2016-08-12 23:59:59'
    AND filter.metrics_1 BETWEEN 0 AND 9999999999
    AND act_deleted.entity_id IS NULL
ORDER BY filter.`posted_on` DESC
LIMIT 5000, 50;

NOTE
act_deleted contains deleted rows. we do not want to show deleted rows to perticular user.

type, metrics_1, metrics_2 condition is dynamic, if user select this filter, it will be there in WHERE part.

INDEX created for this query is

I read about indexes and created this index for the above query as we have sorting on posted_on for this query.

`indx_posted_on` (`posted_on`)

Execution Plan

Issues we facing are as follows

1) First time query execution time is about 28 seconds even with index.

Time Taken

NOTE consecutive query execution time is 1 second with same indexes. I read mysql buffers the table data and so consecutive query execution is faster.

2) There can be high (30 sources max) in IN part of the query and when i do that, applied indexes gets change in query execution plan and execution time increases to about 15 seconds. should be under 2 seconds.

Output of Explain for this scenario as follows

Time Taken

Solution can be for this issue is to use FORCE INDEX. is it okay if i use force index ?

3) Most important issue is i need to add sorting on metrics_1, metrics_2 field also.
do i need to create seperate indexes for that ?
Concern is if i make seperate indexes for each scenario it increases size of table and it results in deadlock while data insertion. (Currently table is about 10 GB in size)

UPDATE
Most simple query would be (without any filter by user)

SELECT
    filter.*
FROM `filters` AS filter
LEFT JOIN act_deleted AS act_deleted ON (act_deleted.entity_id = filter.entity_id AND act_deleted.user_id = 1)
WHERE
    filter.source_id IN (211,493,527,505,554,465,561,565,529,537,504,485,542,590,488,533,468,545,477,547,569,521,513,461,663) AND
    (filter.posted_on BETWEEN '2015-07-29 00:00:00' AND '2016-08-12 23:59:59') AND
    act_deleted.entity_id IS NULL
ORDER BY filter.`posted_on` DESC / ORDER BY filter.`metrics_1` DESC / ORDER BY filter.`metrics_2` DESC
LIMIT 0, 20;

UPDATE

When i add more indexes, it increases table size (currently about 10 GB TOTAL size where 2 GB of data and 8 GB index size)) and i'm getting dead lock in update part in one same query.

When i tried to update metrics_1 in batch, it results in dead lock few times.
Is it because of MORE INDEXES or can be some other reason ?

Please Let me know if any extra information required.
Any help will be appreciated.

Thanks


texts: How do I SELECT from a table with a JOIN with multiple matching values?

texts:
I have the following simple query that works just fine when there is one keyword to match:

SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
GROUP BY gc.id
ORDER BY id DESC

What I want to do is find the id's that match at least two of the keywords I provide. I thought just adding a simple AND would work but I get blank results.

SELECT gc.id, gc.name
FROM gift_card AS gc
JOIN keyword ON gc.id = keyword.gc_id
WHERE keyword = 'mini'
AND keyword = '2012'
GROUP BY gc.id
ORDER BY id DESC

Obviously SQL is not my strong suit so I am looking for some help one what I am doing wrong here.

Here are my table structures:

CREATE TABLE `gift_card` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8;

CREATE TABLE `keyword` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gc_id` int(11) NOT NULL,
  `keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `dupes_UNIQUE` (`gc_id`,`keyword`)
) ENGINE=InnoDB AUTO_INCREMENT=477 DEFAULT CHARSET=utf8;


texts: Detecting resource scheduling conflicts with MySQL

texts:
How to query the 3 tables below to detect resource scheduling conflicts? Conflicts occur when two events with overlapping date/time ranges require common participants.

CREATE TABLE `events` (
  id int(11) AUTO_INCREMENT,
  `date` date,               # date on which the event occurs
  start_minute smallint(6),  # minute of day on which event starts
  end_minute smallint(6),    # minute of day on which event ends
);

CREATE TABLE participants (
  id int(11) AUTO_INCREMENT,
  `name` varchar(255),
);

CREATE TABLE event_participations (
  event int(11),             # refers to events.id
  participant int(11),       # refers to participants.id
  PRIMARY KEY (event,participant)
);

I think the following query would detect overlapping events:

SELECT *
FROM events e CROSS JOIN events e2
WHERE e.date=e2.date AND e.start_minute<e2.end_minute AND e2.start_minute<e.end_minute

However, overlaps do not imply conflicts as the two events may involve mutually exclusive sets of participants. How to query for event-pairs that require the same participants at the same time?


texts: MongoDB - multi wirte data to same document

texts:
I have a question, and its about how mongo will handle if 2 user at same time make a update request to update the data with the $set function like this

Update reuqest 1:

{
   $set: {
      'field1' : 'new data'
   }
}

Update request 2:

{
   $set: {
      'field2' : 'secound data'
   }
}

if i using mysql, its will update each field without eny issue, will mongodb do the same?

the next case can be i have 3 users and there will be a update like this

Update request 1:

{
   $set: {
      'field1' : 'data'
   }
}

Update request 2:

{
   $set: {
      'field1' : 'data',
      'field2' : 'data'
   }
}

Update request 3:

{
   $set: {
      'field2' : 'data'
   }
}

in this case if i do the same in MySQL its will request 2 overwirte the field1 from request 1 and the request 3 will overwirte the field2 data in request 2.

and i expect mongo work at the same way? or can i make a version control over a document so i can se changes over time?

i need to prevent this case, becures i'm building a huges API layer where evey thing use Mongo and i want to update product catalog on cross from platform to platform.

hope eny how can help me to understand this part in MongoDB


texts: SQL developer - procedure just for a session

texts: Is it possible to create a bunch of procedures just for the SQL developer session, so when the session ends the procedures are deleted?


texts: Tempdb Splitting File/Filegroups among different Databases

texts:
A common problem placing databases on one server Instance is they all share same tempdb. Is it possible to allocate tempdb file/and filegroups for separate databases. Does Resource Governor allow this?

Database 1, only gets access to one file/filegroup 1 in tempdb.

Database 2, only gets access to one file/filegroup 2 in tempdb.


texts: String search is very slow with OR statement

texts:
I have the following tables:
product, product_stock, and product_offer
I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, when I add one subplan, things look ok
explain select * from product p
where
   p.product_number like '%T%'
and
  p.id in
      (select c.product_id from product_stock c where c.quantity > 0)

This gives me the following feedback:
Gather  (cost=1000.42..36876.20 rows=22531 width=53)

Which is very good for a table over 1m records
However, when I add an OR statement as following:
explain select * from product p
where
   (p.product_number like '%T%')
and
  (p.id in
      (select c.product_id from product_stock c where c.quantity > 0)
   or p.id in
      (select product_id from product_offer where now() between offer_start and offer_end)
  )

This results in a very slow query as following:
Gather  (cost=313039.34..166666168.02 rows=31023 width=53)
  Workers Planned: 2
        ->  Parallel Bitmap Heap Scan on product b  (cost=312039.34..166662065.72 rows=12926 width=53)
            Recheck Cond: ((product_number)::text ~~ '%T%'::text)
            Filter: ((SubPlan 1) OR (hashed SubPlan 2))
            ->  Bitmap Index Scan on product_trgm_gin  (cost=0.00..310574.23 rows=41364 width=0)
                Index Cond: ((product_number)::text ~~ '%T%'::text)
            SubPlan 1
              ->  Materialize  (cost=0.00..17907.26 rows=558188 width=8)
                  ->  Seq Scan on product_stock c  (cost=0.00..12935.32 rows=558188 width=8)
                           Filter: (quantity > 0)
            SubPlan 2
              ->  Seq Scan on product_offer (cost=0.00..1308.68 rows=59468 width=4)


texts: How do I get PostgreSQL to keep inserting after unique index violation?

texts:
I have ETL (Pentaho DI 8.2) to fill a Data Vault Hub table. Basically the ETL depends on the UNIQUE index to refuse duplicates of the business key column. In SQL Server this works great. With PostgreSQL I notice that it stops inserting after the first violation of the unique index. The ETL write is set to do individual inserts and ignore database errors. I add a rule to ignore the duplicates, but this slows down performance. Also I want to prevent this complication with an extra construct.

How do I get PostgreSQL to continue inserting valid rows into my hub tables?


texts: 'LDF' is not a valid database file header. The PageAudit property is incorrect

texts:
I 'am trying to import database into sqlserver 2008 using this query

EXEC sp_attach_db @dbname = 'LIQ_DEV_V9',
@filename1='/home/elsayedawd/DATA/databasedumbs/database9/LIQ_DEV_V9.mdf',
@filename2='/home/elsayedawd/DATA/databasedumbs/database9/LIQ_DEV_V9_log.LDF';

but it failed with

  Error: The header for file
  '/home/elsayedawd/DATA/data-base-dumbs/database-9/LIQ_DEV_V9_log.LDF'
  is not a valid database file header. The PageAudit property is
  incorrect. SQLState:  S00015 ErrorCode: 5172


texts:
If I leave sqlplus open and unused for too long (probably 15 minutes or more), when I EXIT the following message will appear.

ORA-03113: end-of-file on communication channel

Does this indicate a real problem? If so, how might one go about correcting it?

There are a number of other posts about this error at database startup time. This is not at startup time. This is on the client. I can immediately start sqlplus again and have a working connection. Environment is Oracle 11.2.0.3.0 on Windows 64-bit, client is Windows 7 64-bit.


texts: "Sniff" SQL queries being sent out from my computer

texts:
I'm a student working on pulling certain information from an SQL server here at work. I don't have sysadmin privileges and my attempts at receiving them have not been met with success. It's a big company and the bureaucracy surrounding these things is nothing short of ridiculous, so that isn't an option.

I want to run a query that is already being executed by one of the programs on this computer, but with slightly different parameters (joining a few more tables, etc). The database is vast and incredibly complicated, and I've therefore been unable to understand how this program gets the information it does.

So my question is this: is there any way to monitor the queries being sent from my computer, specifically the query text? It would be tremendously helpful and save me time, energy, and a hell of a lot of red tape.

(I recognize this may seem like a stupid question or that I'm asking the impossible - if it can't be done it's no big deal - I can get by without it. It'd be nice, though.)


texts: Is there a way to set bind_address from command line in mysql 5.5 without restarting?

texts:
I made a modification to bind_address in my.cnf that knocked down a monitor. It has been repaired since, firewall allowing the monitor IP in. However, mysql is still running in its previous state since restart.

In MySQL 5.5, there does not seem to be a bind_address variable available from CLI (unless I'm missing something). Is there a way I can set bind_address from CLI so I don't have to restart mysql?


texts: Delete query uses 100% CPU with in Azure SQL Database

texts:
I have a relatively small database, maybe a few hundred thousand rows, and we have a stored procedure that is meant to clear out a 'Dealer' (the application aggregate root) and all of it's related data.  The database lives on a v12 Azure SQL instance, and when it hits one particular table, it consumes 100% of the instance's DTU/CPU and takes a really long time to finish.

I believe the issue is that the table has a ton of foreign keys, probably about 30.  Looking at the execution plan you can see that it's doing a ton Nested Loop with index scans to find dependent rows.  All of these rows were already delete prior to the attempt to delete the rows from this particular table, so these loops are actually pointless.

My question is, would it be more efficient to disable the foreign key constraints on these dependent tables, do the delete, and then re-enable to constraints?  Would there be some negative effect to doing that, aside from the fact the it's technically possible that some bad data gets inserted while the constraints are disabled.  Or is there an even better way to accomplish what I want to accomplish?

UPDATES
This is a v12 Azure SQL database, it is not full blown SQL Server 2012 in a VM.  I am not batching any deletes, and the offending statement is part of a larger SPROC.  I identified this particular table delete by tearing the sproc apart and running bits and pieces of it separately.  When it got to this table, it took literally 15 minutes to complete.  It will affect on the order of hundreds of rows, varying depending on the state of the particular 'Dealer' we're clearing out.

We cannot do soft deletes in this case because usually we're clearing this dealer out so it can be replaced (soft deletes would cause duplication and would require a lot of code to work around).  I have investigated other types of blocking and wait states and found nothing else that seemed to be negatively affecting this.  As I mentioned this database is relatively small, and it's mostly used for very simple CRUD operations.

Here's is the execution plan, it's huge.

The XML version of the execution plan can be found here: https://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2


texts: Retrieving month from date field in my database

texts: I have dates stored in database with the function : CURDATE() , when a user makes an order . With a form where the admin chooses the month and the year, I want a query to display all the orders made in that month and year . How can I do that ?


texts: Variable substitution in psql when using \copy

texts:
I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that:

  The syntax of the command is similar to that of the SQL COPY command. Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply.

This seems quite definitive, however I'm wondering if anyone knows of a workaround?


texts:
In SSMS if you are connecting to SQL Server instance set to specific port other than 1433 you simply put ",port number" after the instance name. In the same regard if I am using System.Data.SqlClient.SqlConnection and want to populate a data set with System.Data.DataSet, I would do the same when specifying the Data Source portion of the connection string to pass into that object.

Is there not a way to do this using Microsoft.SqlServer.Management.Smo.Server class?

I have tried variations with no luck like:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version

Or just trying:

$s = New-Object Microsoft.sqlserver.management.smo.server "Server\Instance,port"
$s | Select Name, Version


texts: Window function with order by returns strange results

texts:
Here is example:

drop table if exists tst;

create table tst (
num integer not null
);

insert into tst values (1), (2), (3);

-- window functions WITH order by clause
select *, max(num) over (partition by true order by num asc), array_agg(num) over (partition by true order by num asc) as test
from tst;

-- window functions WITHOUT order by clause
select *, max(num) over (partition by true), array_agg(num) over (partition by true) as test
from tst;

The result is as follows:

Why order by clause has impact on aggregation function?


texts: SQL Server Optimizer Time Threshold To Evaluate Candidate Plans

texts:
In SQL Server, is there any time threshold to evaluate candidate plans? It is true that optimizer can't evaluate all possible plans so actually when it decides to quit and go for optimum plan execution?

If threshold does exists, is there any change in threshold for SQL Server 2017 ?


texts: Restoring plain-text pg_dump with psql and --disable-triggers

texts:
I had to perform some tests with a short script to update some "legacy" data in one of my tables.

Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. Simplest way to do that was:

pg_dump -a --file table.sql -t table database

Now I did what I had to do, checked the results and found them rather unsatisfactory. I thought to myself: how lucky I am to have a backup of that table.

I had already been warned when I backed up the table that:

pg_dump: NOTICE: there are circular foreign-key constraints among these table(s):
pg_dump:   table
pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.

I didn't think much of it, but now we have a problem. Indeed the table in question has multiple triggers attached to it, but I cannot restore the table.sql with option --disable-triggers  of the pg_restore command.

If I try following command I get an error message:

pg_restore -a -d database -t table -h localhost --disable-triggers table.sql

namely:

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Is there a flag for the psql-command that exhibits the same behavior as --disable-triggers?

I have already checked the psql "manpage", searching for trigger and similar keywords but didn't find anything.

Or is the only option I have to drop the triggers on the table before restoring the data?

Sidenote: I am using postgres v. 9.3 on a Ubuntu 14.10 System

It was suggested to edit the generated sql-file, to include the statement:

ALTER TABLE table DISABLE TRIGGER ALL

When I now executed: psql -d database -f table.sql I got an error message about violating the "Unique" constraint of the primary key.

To fix this I tried to wrap the copy into:

BEGIN TRANSACTION READ WRITE;
TRUNCATE TABLE table;

-- copy here

COMMIT;

Now the error message is:

psql:project_backup.sql:18: ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "another" references "table".
HINT:  Truncate table "another" at the same time, or use TRUNCATE ... CASCADE.
psql:project_backup.sql:20: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:project_backup.sql:21: invalid command \N
psql:project_backup.sql:22: invalid command \N

The latter warning repeats for each \N (symbolizing the null value) in the dump.


texts: How to do PostgreSQL replication on CentOS 7?

texts:
Followed this guide to do the PostgreSQL replication:

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps

I didn't do this:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/
psql -c "select pg_stop_backup();"

After I configed both master and slave nodes, test to create a table on master, didn't find new data been synced to slave.

From this guide I saw need to add a archive_command config in the recovery.conf file:

https://www.server-world.info/en/note?os=CentOS_7&p=postgresql&f=3

archive_command = 'cp %p /var/lib/pgsql/archive/%f'

But PostgreSQL 9.6 maybe not support that feature. So how can it been synced automatically?

Addition

It's helpful:

https://dalibo.github.io/PAF/Quick_Start-CentOS-7.html


texts: Can I delete an Oracle "Whole database backup" while a level 0 backup is being created?

texts:
I have created a Whole database backup using:

-- In SQL*Plus
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 5000G SCOPE=BOTH;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = 'O:\ORA_FRA';
alter system set db_create_file_dest = 'C:\ORADATA\MAINDB';
alter database enable block change tracking;

-- In RMAN
configure default device type to disk;
configure device type disk backup type to backupset parallelism 1;
configure backup optimization on;
configure retention policy to recovery window of 31 days;
configure archivelog deletion policy to none;
configure controlfile autobackup on;

Backup database plus archivelog;

This backup finished. Then I realised I actually needed a Level 0 incremental backup. I started this using:

run
{
    allocate channel disk_iub device type disk;
    recover copy of database with tag daily_iub;
    backup incremental level 1 for recover of copy with tag daily_iub database;
}

This backup is now in progress, but I am afraid the Fast recovery area will run out of diskspace with the former whole database backup still stored there.

Can I safely delete the first backup? If so, how?
Or, will I somehow run into problems with the archivelog and block change tracking?
I'm aware there will temporary not be a usable backup. That is acceptable, as I have a data-dump somewhere else.


texts:
Where can I learn more advanced SQL programming to improve my ability in SQL?

I have reviewed the database AdventureWorks 2008 but I need more.


texts: Multiple person roles in database(how to define if same person)

texts:
I have the following data structure:

For internal purposes I can register my outside partner. In this case I don't know his/her identify card number. So I add my partner to database but without id card number,and has no user access.

*I cannot add more pictures, sorry.

Once my partner comes and he/she wants to use the system. He decides to register as a member. He may would not know that he/she has already registered as an outside partner. Filling all Person datafields and the user fields, including the identify card number.

And here comes trouble. I don't want to split the same person into 2 in the case when the partner is already in the system.  I want to use the same person details insted of duplication. It is extremely important that we do not allow person duplication.

What would you suggest?


texts:
I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement?

Example

insert into DestinationTbl select * from SourceTbl where [col1]='xyz'

Above statement may insert more than one rows into a table. So i want identity value of all the rows which are inserted. How it can be done?


texts: Having issues with SQL with a join issues

texts:
I have the following code:

SELECT a.oop_action_id,
  a.requestor,
  a.discussion_topic,
  a.action_item_desc,
  a.owner,
  a.partner,
  a.CP_Director,
  a.Chief_of_Staff,
  a.Date_Due,
  c.empl_email secondary,
  b.empl_email prime,
  d.empl_email cpdirector,
  e.empl_email cos,
  a.mail_sent,
  a.status
FROM OOP_ACTION_ITEMS A, EMPLOYEE B,  EMPLOYEE C,  EMPLOYEE D,  EMPLOYEE E
WHERE a.owner = b.LAST_NAME||','||b.FIRST_NAME||' '||SUBSTR(b.MIDDLE_NAME,1,1)(+)
AND a.partner = c.LAST_NAME||','||c.FIRST_NAME||' '||SUBSTR(c.MIDDLE_NAME,1,1)(+)
AND a.CP_Director = d.first_name||' '||SUBSTR (d.MIDDLE_NAME, 1, 1)||' '||d.last_name(+)
AND a.Chief_of_Staff = e.first_name||' '||SUBSTR (e.MIDDLE_NAME, 1, 1)||' '||e.last_name(+);

I'm trying to run this and I keep getting a 'missing keyword' error on line 17

can anyone explain?


texts: Add data from rows to different columns if more than 1 row have a partial match

texts:
I tried to find a solution but do not find anything to add multiple columns from partially matching rows.

Data:(Maximum of 3 programs per person)

name     surname    program
Jan      Peters     program1
Jan      Peters     program2
Pete     Peters     program1
Jane     Smith      program3
Jane     Smith      program1
Jane     Smith      program2

Result needed:

name     surname    prog1       prog2      prog3
Jan      Peters     program1    program2
Pete     Peters     program1
Jane     Smith      program3    program1   program2

Even just pointing me into a direction would help.


texts: Correct format to combine text and columns to create a new column?

texts:
Long story short, my company's system creates a "keyword search" column when we enter in new stores manually. When they need to add 100's of stores, they have me insert everything via SQL. I can do everything except create a very long and odd column that the system uses as a search field for end users. I've been using excel to combine all of the columns, but it's extremely time consuming. I'm hoping someone can share some insight in to how to create this column using SQL so I can eventually automate this entire process. I'm a newer DBA and newer to this site so bare with me please lol.

Table is like:

CustNo | Name | City | Zip
______________________________
100 | New Store | Miami | 33101
101 | Store 2 | Miami | 33102

I need to add a column that uses those fields and combines my own text input. End result is like;

SScustno100 100 SSnameNew_store New store SScityMiami Miami SSzip33101 33101

So query would be similar to:

"SScustno"[custno] [custno] "SSname"[name] [name] "SScity"[city] [city]
"SSzip"[zip] [Zip]

The later is a better reference. How would I write a query that combines my text input and the columns in the table to combine everything together to create the "keyword search" field for each record? I feel like this should be easy, but I've tried a few times and I've concluded I just don't know the correct syntax to work off of.

Any help is greatly appreciated. If you need more info I'm happy to provide it. I tried to dumb it down as best as I could. The actual column I create in excel combines 120+ columns, but I'm not putting that mess on you guys. I just need a way to combine my text and column data together in order to insert it into a new column.


texts: Suppressing the SSMS prompt to save changes

texts: Is it possible to configure SQL Server Management Studio (SSMS). so that the dialog box shown above does not appear when I close an unsaved query window?


texts:
This query doesn't return the "latest" entry, rather it returns the "older" rows:

 SELECT MAX(ID), ID, NEW_BALANCE, DATE, NAME, ACC_NO
 FROM
 (
  SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO
  FROM account_logs
  WHERE customer_id = 1061
  ORDER BY DATE DESC, ID DESC
) AS sub
GROUP BY YEAR(date), MONTH(date) ORDER BY DATE ASC ;

The following inner query:

SELECT ID, NEW_BALANCE, DATE, NAME, ACC_NO
FROM account_logs
WHERE customer_id = 1061
ORDER BY DATE DESC, ID DESC

...perfectly arranges the rows by the latest entries:

I would like to GROUP BY the latest entries, rather I get this:

The rows I need:


texts: Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

texts:
I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here.

I always thought that I could trust that identity values increment by constant increments. On the other side, I know that using sequences I can never be sure that there are not some gaps in them.

Is it time to adapt to gaps in sequences or will it be better to stay with identity columns in temporary tables?


texts: Facing warning message while creating tar.gz file

texts:
I am creating a tar.gz of database hot backup files.

During execution of tar command, I am getting this warning for only this file every time I run tar:

  /u01/app/oracle/oradata/db5/sysaux01.dbf: file changed as we read it

I intend to perform clones and recoveries of Oracle database using this tar file. Will it cause problems?


texts:
I need to move about 150 databases from one server to another server.

SQL Server 2008 Web Edition
SQL Server 2012 Preview (in a different datacenter - East Coast Azure)

I was planning on moving them one at a time using RedGate Packager, however this will take a while.

Is there a faster and easier way?