texts: Only one NULL for one part of primary key

texts:
I have two date columns : "start date" and "finish date".

I want to make sure that there can be only one null value in the "finish date" column (which should be current state if it's NULL) for one primary key (composite in my case).
Is there a way to do that?

To illustrate, there should be only one NULL for one part of the primary key.
Here are the columns:

PeriodID | PersonID | StartDate | FinishDate
       1          1      1/1/10      10/1/10
       2          1     10/1/10         NULL
       3          1    somedate   MUST NOT BE NULL

And yes, "PersonID" is a foreign key.

I'm using SQL Server 2008.

EDIT :

I got a working answer,but other ways are wellcome too.


texts: Does MariaDB-Galera Cluster support LAST_INSERT_ID and mysql_info?

texts:
As far as I understand, Galera does not support table locking, but what information can be retrieved about the last query?

  LOCK/UNLOCK TABLES cannot be supported in master-master replication. Lock functions (GET_LOCK(), RELEASE_LOCK(),…) are not supported.

What about LAST_INSERT_ID()?  At some point I read, that the nodes use different auto increment values, but it said nothing whether the last insert id will be returned.

And a related question: In PHP I use the mysql_info function to retrieve the "rows matched" on UPDATE.  Is this information expected to be availible in a Galera cluster?


texts: Attach database failed for Server '{SERVER_NAME}\{INSTANCE_NAME}'. (Microsoft.SqlServer.Smo)

texts:
Strange, I tried to attach the database AdventureWorks from this site - http://sqlserversamples.codeplex.com/

However, I encounter the following problem while attaching AdventureWorks2008R2_Data.mdf

  Attach database failed for Server '{SERVER_NAME}{INSTANCE_NAME}'. (Microsoft.SqlServer.Smo)

  Additional Information:

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

  Directory Lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Log.ldf" failed with the operating system error 3 (The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)


texts: SQL30080N error points to two documents SC31-6160 and (SC31-6156

texts:
If I face this problem: SQL1476N with sqlerror "-30080", then I check this other error:
db2 ? sql30080

SQL30080N  A communication error "<reason-code>" occurred sending or
      receiving data from the remote database.
...
Refer to the document IBM Communications Manager 1.0 APPC Programming
Guide and Reference (SC31-6160) for explanation of the APPC primary and
secondary return codes. For details of APPC sense data, refer to the IBM
Communications Manager 1.0 Problem Determination Guide (SC31-6156).

When can I get information about these documents?

IBM Communications Manager 1.0 APPC Programming Guide and Reference (SC31-6160).
IBM Communications Manager 1.0 Problem Determination Guide (SC31-6156)

Reference:
https://www.ibm.com/docs/en/db2/11.5?topic=SSEPGG_11.5.0/com.ibm.db2.luw.messages.sql.doc/sql28000-sql33999.html#sql30080n


texts: How are these null values stored in a NOT NULL column?

texts:
We are replicating tables from SAP ECC 6.0 on HANA into an Oracle 10g warehouse, using SAP SLT.
Since starting this, we have noticed the NOT NULL column definitions from HANA are retained in the Oracle copies of the tables, but HANA stores many values as empty strings. Oracle stores empty (varchar) strings as NULLs and somehow this does not conflict with the NOT NULL column definition (i.e. we have NULL in a column defined as NOT NULL).

Querying these tables is producing strange results:

SELECT COUNT(*) FROM warehouse.table WHERE col IS NULL;
0
SELECT COUNT(*) FROM warehouse.table WHERE col = '';
0
SELECT COUNT(*) FROM warehouse.table GROUP BY NVL(col,'N');
X 503206
N 2377222

So we can tell that there are NULL values in these columns by using NVL or DECODE functions, but querying them is returning odd results.

We do get proper results once we alter the column:

ALTER TABLE warehouse.table MODIFY (col NULL);
Table altered.
SELECT COUNT(*) FROM warehouse.table WHERE col IS NULL;
390986

But of course we can't alter the column back:

ALTER TABLE warehouse.table MODIFY (col NOT NULL);
ERROR at line 1:
ORA-02296: cannot enable (warehouse.) - null values found

I can't tell if this is a problem with Oracle's implementation of empty string storage, or possibly just a quirk of interacting with SAP's SLT replication. It seems Oracle should not allow these rows with '' or NULL values as replication tries to place them there but we have not seen any errors indicating this.

Edit to add query requested by hypercube:

SELECT LENGTH(col) FROM warehouse.table GROUP BY LENGTH(col);
 2377222
1 503206


texts: How do I calculate working business minutes?

texts:
I'm working on business inteligence reports and one of my task is to create reports for time tracking.

I have to report on business minutes (10 hours per day) within business week (monday to friday).

My initial solution was to use a scalar function. It did not cater for holidays, but it worked. It was not optimized by database engine, therefore, when I have tried using my function in the following way:

Select
   [dbo].[fn_GetWorkMinutesInRange](CreatedOn,ResolvedOn,1, 6, 7)
From
   Incidents

This was not optimised database engine and it was taking over 5 minutes to execute before I canceled it. There are around 20,000 incidents in the Incidents table, so performance should not suffer imo.

I have given up on scalar function solution and I created a table function which created a calendar. This didn't help either as I don't see how I can join this function with my Incidents table.


texts: Steps to restore a SQL Server database to a point in time

texts:
Time given lies in all three backups, full, diff ,log:

full - 12 midnight
diff - 4 am ( every 4 hours)
log  - 15 minutes interval

When:

time restore is 5:17
time restore is 5:14

How should I answer that?


texts: High autovacuum CPU/IO usage from database with more than 10k append-only tables

texts:
In my system I have more than 10k tables that are very similar, I only do insert operations in these tables and delete old data via a cron schedule one time per day.
These insertions come each minute, a mean of 5 rows per table which are inserted in a single insert query, meaning something around 50k rows per minute.
The issue I'm seeing is that stats collector (I disabled all stats except track_counts) uses a lot of CPU and IO all the time.
The same happens to autovacuum which will also use a lot of the PC resources for itself.
Another issue is that they never seem to stop, autovacuum and stats collector are always running as long as I'm always adding new data in the rate shown above.
If I disable track_counts and autovacuum, it fixes my issue entirelly, the database is kept idle most of the time.
The problem with this approach is that I obviously are not running vacuum anymore.
So, my question is, would it be a good solution for my case to disable autovacuum entirelly and run VACUUM FULL after my cron script deleted old data? This would mean that I would trigger a full vacuum query for each table one time per day.
If not, do you have some sugestions for postgres configuration changes to improve the situation without disabling autovacuum?
This is part of my postgresql.conf (feel free to ask for more information about it if needed):
track_activities = off
track_counts = on
track_io_timing = off
track_functions = none

log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

autovacuum = on

log_autovacuum_min_duration = -1

autovacuum_max_workers = 6
autovacuum_naptime = 3600

I already saw some answers for cases of append-only tables or big tables, but I think I'm seeing this issue because in my case I have a LOT of tables.
Also, for completeness, I'm running the database in Postgres 12 and with extension TimescaleDB 1.7.1 inside docker.


texts: Can I create a referential integrity constraint in SQL Server that prevents time overlaps?

texts:
It is trivial to create a Constraint on uniqueness for an integer value, but I am wondering if it is possible to create a constraint on overlap/intersection of a range of values.  For example, my schema requirement from a business level is to only allow one Person to reserve one Resource for one particular Time on a particular day.  They may wish to claim the resource from 9 AM Jan 2, 2016 until 10:15 AM on Jan 3, 2016, so let's assume the resolution of resource-reservation is 1 minute.  The reservation claim could be a tuple (row) in a table where the time (converted to an integer perhaps), plus a resource id (foreign key) must be unique.  The third column in this reservation table would be a foreign key to refer to the Person.

The problem with such a schema is that if someone wants to reserve a resource for a week, at one minute resolution, they have to create 1440 rows in the table per day, so that's 10K rows for ONE reservation.

Does SQL Server 2014 (or SQL Server 2016) have any built in way to express that a particular range of values does not overlap any other range entry, in a way that would let me specify the beginning and ending of the reservations as a BeginDateTime/EndDateTime column in a row, and check that the range plus the resource ID form a unique non-overlapping entity?

Until now I have always modelled these constraints in the application layer (above the database) but I think that the power of relational integrity would be useful for preventing people from inserting and updating the reservations in ways that create conflicts.

It would be possible to do something similar by having triggers check for the database values being in an inconsistent state, but I'd rather have the attempt to write an overlapping value blow up, and not commit.


texts: PostgreSQL full-text search retrival from id gap

texts:
I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part).
There is a postgres function where I

SELECT concat_ws(' ', a."my_column1") INTO c1;
SELECT concat_ws(' ', a."my_column2") INTO c2;
RETURN setweight(to_tsvector('pg_catalog.simple', c1), 'C') ||
       setweight(to_tsvector('pg_catalog.english', c2), 'D');

This is being written into a column called fts_document in the same table. I have INSERT and UPDATE triggers on this table and a trigger function as in the tutorial.

Everything has worked fine at some point.

After a dump from another db with identical structure and a restore to an empty db I got my table with a gap of the field id from 7306 to 21431. The main problem is that now the full-text-search retrieves documents only from this missing id range and the my_column1 and my_column2 values are very valid ones. I have deleted the index on fts_document column and rebuilt it, but no result.

Where does postgres retrieve these weird ("not existing") rows from? How can I fix the search to retrieve also the existing rows and postgres to show the missing docs?


texts:
I create a copy of a database with DB Master Key in the same database server, this server is part of an activity group I run in both servers this script:

OPEN MASTER KEY decryption by password = 'This is the Key';
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'This is the Key';
Alter master key add encryption by service master key;

This fix temporary the decription of the rows, but when the server run on fail over and swap the primary node i must rerun the script to fix again the problem, this issue only affect the restored database, the original one, works normarlly.

Update

I already try restore the key exporting it from the other server but do not work.


texts: Am I Setting Up My Database Correctly? Or Am I Thinking About Things The Wrong Way?

texts:
So I'm having a little self-doubt as to whether or not I am setting up this database I am working on correctly or not.  This is the first large-scale database project I have ever worked on, and I'm doing I've been tasked with doing it alone.  So you know, no pressure at alllllllll.

This question isn't about whether or not I am setting up my tables correctly.  I at least have enough confidence in myself to say I know how to create proper relational database tables, and how to normalize them properly.

What I am concerned with are in facts things like Views, and Stored Procedures.

Quick synopsis of what the project is.  I am retrofitting the company database, updating it to a new relational model.  I need to set up an entirely new database structure from scratch for all the data, and then alongside the database create a VB.Net program that employees can use to interact with the database in various ways.

You see I've basically set up the database so that the Tables act as where all the data is stored, and the Views act as where all the data is retrieved.  I figured that setting it up this way makes it so that it is easier to select data that needs to be shown to people using the .Net app since Views already contain all the proper Joins for all the data.  Sure if you need to select just one piece of data from a table, you can do a simple table query, but for large data sets I figured Views were the way to go.  Was I correct in thinking this?

And then there are the Stored Procedures.  Essentially if the VB.Net project is doing anything variable related for data collecting, I am just setting up a stored procedure within the database to take those variables and work with internal SQL script.

For example, let's say I need to select everything from my PartsData View that has a specific supplier.  Instead of creating a large query string in VB.Net and then passing that String as an SQL Command, I am instead just passing the supplier name as a variable to a 'PartSupplier' Procedure on the database which spits back the information I need.

To me this feels like it just makes more sense to do it this way, as opposed to just constructing long String queries in VB.Net and then throwing them at the database.  Part of what isn't helping my confidence in my approach is that my boss seems to think I'm wasting time and being stupid for doing things my way. :\

I know this isn't exactly something anyone can give me a 100% answer on because I know that you can't give me something like that without knowing all the information.  But am I on the right track here?  Are there some obvious flaws with my logic you can easily see?


texts: What is the best practice for selecting from two tables with multiple relations?

texts:
I have two tables with a structure like below:

and am selecting the data with below query:

SELECT
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = DN) DN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = PN) PN,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Winder) Winder,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = CouplingType) CouplingType,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = Type) Type,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = ILayer) ILayer,
(SELECT Name FROM Management_EnumerableItem ME WHERE ME.ID = OLayer) OLayer
FROM Product_Coupling_Serial

So,what is the best practice for the best performance of selects like that (fastest way)?


texts: How does pausing a SQL Server mirror affect transaction logs on the principal?

texts:
One of our two datacenter's is undergoing maintenance for a minimum of 72 hours, but it could be up to 5 days, and I have a very old legacy SQL Server running a production service as a mirror across both data centers.

I'm thinking that for the duration of the outage, I can pause the mirror, but I'm wondering what the effect will be on the transaction logs of the principal? We have scheduled t-log backups running hourly, so this should keep them from growing too large right?

How then does that affect replication when restarting the mirror? Would it be easier to break the mirror completely, put the database into simple recovery mode, and then rebuild the mirror afterwards?


texts: SQL Server 2008 R2 SP1 - Can't install "Full-text Search"

texts:
I have a "Microsoft SQL Server Express 2008 R2 SP1" installation on a machine without the "Advanced Services". Now I want to install the "Full-text Search", so I downloaded the setup "SQLEXPRADV_x64_DEU.exe".

The problem is, if I start the setup, select my existing sql-instance and go further to the feature-selection menu, I don't see the "Advanced Services". I just see the already installed features. Here's a screenshot (in german):

I did the installation of the "Full-text Search" on about 20 different machines with existing sql-instances without problems. But on this machine, I don't know what's the problem.

Hope somebody can help me! Thanks in advance!


texts: Default value format of a stored procedure parameter(of datatype : money)

texts:
With my query:

SELECT (CAST(0.00 AS MONEY))

I am expecting a result : 0.00

But it shows me the result: 0,00(with comma)

I checked this query ,when i found that,the CREATE PROCEDURE script displays the default value as
[money] = 0,0000, for one of the input parameters of a procedure.

How can i make the CREATE PROCEDURE display it with '.' instead of ','?


texts:
I have a query that I am struggling with. The problem I have is that I would like it to just display a total of column variancevalue as a result.

SELECT
  DEPARTMENT.DESCRIPTION AS DepartmentName,
  SUBDEPARTMENT.DESCRIPTION AS SubDeptName,
  SUBSUBDEPARTMENT.DESCRIPTION AS SubSubDeptName,
  sum(STOCKTAKELIST.QTYONHAND) Onhand,
  sum(STOCKTAKELIST.STOCKCOUNT) StockCount,
  sum(STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) Variance,
  sum((STOCKTAKELIST.STOCKCOUNT - STOCKTAKELIST.QTYONHAND) * STOCKTAKELIST.CostPrice) VarianceValue
FROM
  STOCKTAKELIST STOCKTAKELIST
  INNER JOIN Stock STOCK ON STOCKTAKELIST.INTRECNO_STOCK = STOCK.INTRECNO
  INNER JOIN Location LOCATION ON STOCK.INTRECNO_LOCATION = LOCATION.INTRECNO
  LEFT JOIN DEPARTMENT ON STOCK.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBDEPARTMENT ON STOCK.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
  LEFT JOIN SUBSUBDEPARTMENT ON STOCK.SUBSUBDEPTCODE = SUBSUBDEPARTMENT.SUBSUBDEPTCODE AND SUBSUBDEPARTMENT.SUBDEPTCODE = SUBDEPARTMENT.SUBDEPTCODE AND SUBSUBDEPARTMENT.DEPTCODE = DEPARTMENT.DEPARTMENTCODE
WHERE
  STOCKTAKELIST.ListCreate >= '01 Jun 2018' AND
  STOCKTAKELIST.ListCreate <= '10 Jun 2018 23:59:59' AND
  STOCKTAKELIST.IncludeInCount = 'Yes' AND
  STOCKTAKELIST.STOCKCOUNT <> STOCKTAKELIST.QTYONHAND AND
  STOCKTAKELIST.Status = 'Complete'
GROUP BY
  1,2,3
ORDER BY DEPARTMENT.DESCRIPTION, SUBDEPARTMENT.DESCRIPTION, SUBSUBDEPARTMENT.DESCRIPTION

Can this be done and if so how would I do that?


texts: Equivalent to Azure SQL Threat Detection for on-premise database

texts:
Due to GDPR, we are seeking a way to help identify malicious activity or data breaches from our production SQL Server.
It seems if we were using Azure, this is a flick of a switch.
What is available for on-premise servers?


texts:
I am using BIDS to export data from Oracle to SQL server.

I am using OLEDB source and destination.

I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields give invalid date format issues.

These four fields either have nulls or have only date part in the datetime fields of ORACLE.

Any solution for this?

I have Tried all sorts of data conversions and insert nulls option.


texts:
How can My Oracle Support (Metalink) be used effectively?

What are your secret hints to this system? What saved you the most time?


texts:
Need your help. My sql job is not able to take backup to the shared folder although the domain account, under which the sql agent service account is running, has full permission on that shared folder.
In addition to that, my sql service account uses default virtual login. So when I change that account to the domain account that I also used for sql agent service account, then the problem is fixed.
Why can't I take backup to shared folder using sql agent service account?


texts: ORA-12154: TNS: Could not resolve the connect identifier specified

texts:
I am running a script that "creates/replaces" a procedure in a database and then runs the procedure. The code is as below:

WHENEVER SQLERROR EXIT

UNDEFINE g_cust_pwd
UNDEFINE g_db_name

SPOOL 'output'

PROMPT setup_and_run.sql begin

ACCEPT g_db_name CHAR PROMPT 'Please enter the DB instance: '
ACCEPT g_cust_pwd PROMPT 'Please enter the TEST_SCHEMA schema password: ' HIDE

PROMPT Testing connection

CONNECT TEST_SCHEMA/&&g_cust_pwd@&&g_db_name

WHENEVER SQLERROR CONTINUE

SET SERVEROUTPUT ON

SET DEFINE OFF

PROMPT
PROMPT @procedure_test.prc
PROMPT Creating or Replacing Procedure
@procedure_test.prc

PROMPT
PROMPT procedure_test
PROMPT Running Procedure...
EXEC procedure_test

PROMPT setup_and_run.sql complete

SPOOL OFF

UNDEFINE g_cust_pwd
UNDEFINE g_db_name
/

Whenever I run the code above with the correct credentials (double, triple checked these to make sure that this isn't the issue). The code works fine, as expected, the problem occurs whenever I run the code for a second time in the same SQLPlus session it gives a TNS error.

Normally, I would assume that this is merely a TNS names error and the database needs to be added to the list, but it works only once per session (even if I add a disconnect at the end)


texts: What are the risks of upgrading DB major version in replica and then promoting it as master?

texts:
Recently AWS decided to force update all MariaDB RDS under 10.3, my RDS is in MariaDB 10.1. We are trying to minimize the downtime and any risk of data out of sync.
Our plan is to create a replica and upgrade it from 10.1 to 10.5. After that we will let it to consume binary log from old non-upgraded RDS then promoting it as Master and point our website to the newly created and upgraded RDS.
Assuming we will have continuous stream of data coming into the RDS, what are the risk of steps above? Is the new RDS able to get all binary log genereated during the upgrade time? We estimated there will be ~ 1.5 hours to finish upgrading from 10.1 to 10.5
Thank you for helping


texts:
Im running SQL server 2014 on a VPS with 150Gb ssd. Today i found that my hard disk was full 100% and there's a very large file from SQL Server Error log that was 45.7Gb.
I tried manually delete the file, but Windows says the disk space doesnt change at all.
How can I delete this file ? I dont need error log data, it's fine for me to delete it and regain the disk space.
Thank you very much for your support.


texts: PostgreSQL CREATE TABLE creates with incorrect owner

texts:
I'm using PostgreSQL 9.2.4.  When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly grant myself permission.

I created the non-superuser like this:

admin_user=# create role "test1" NOINHERIT LOGIN ENCRYPTED PASSWORD 'wibble' CONNECTION LIMIT 10;

Then I created a database owned by test1 like this:

admin_user=# create database "test1$db1" with owner "test1";

Then I started a new psql as user test1, and created a table:

test1$db1=> create table test_table (column_name varchar(50));

But I couldn't insert into it:

test1$db1=> insert into test_table values ('some data');
ERROR:  permission denied for relation test_table

Checking the permissions shows that the table is owned by the postgres user:

test1$db1=> \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | test_table     | table | postgres

However, I can grant myself permissions and do stuff:

test1$db1=> grant insert, select on test_table to test1;
GRANT
test1$db1=> insert into test_table values ('some data');
INSERT 0 1
test1$db1=> select * from test_table;
 column_name
-------------
 some data
(1 row)

What's going on?  I'm pretty sure this used to work.  And the PostgreSQL docs for CREATE TABLE say

  CREATE TABLE will create a new, initially empty table in the current
  database. The table will be owned by the user issuing the command.

Having to grant permissions to myself on my own tables doesn't sound like it's what I should have to do.

Any help much appreciated!

[UPDATE]

It looks like this might be caused by some kind of change to the PostgreSQL package at http://apt.postgresql.org/pub/repos/apt/

We have three machine images, all of which installed PostgreSQL from that source:

Install at 23 July 2013, 18:47 UTC -- correct table ownership
Install at 23 July 2013, 19:39 UTC -- correct table ownership
Install at 24 July 2013, 13:16 UTC -- error

So perhaps something has been changed in the packaging.  I'll drop a line to the mailing list for that.


texts: Database function SELECT with optional argument

texts:
I need to perform a SELECT in postgres that filters records only when a certain argument value is not null. If the argument is null then the argument is ignored. I am experimenting with a function...
create or replace function my_func(argument INT)
returns table
        (
            field1   VARCHAR(50),
            field2 VARCHAR(2),
            field3 BIGINT
        )
language plpgsql
AS
$$
begin
select d.field1, d.field2, COUNT(*) as record_count
             from my_table d
             group by d.field1, d.field2;

if argument IS NOT NULL
    -- TODO: How to filter the first recordset here?
end;
$$

How do I perform a second, optional SELECT in a function please? Or perhaps there's a slick way to do an option where clause?


texts: Node and Instance Relationship on Oracle RAC

texts:
I'd like to ask about the process of Node and Instance on Oracle RAC functionality.
I know that RAC provides Failover, a RAC feature that keeps an application running even if the node that was servicing its request failed to operate.
Well so if RAC enters the Failover mode, is that mean the instance that was on the failure node will be shutted down or it was transferred to another node?

Thank You!


texts:
Using VB.NET, I want to restore a SQL Server database from a .BAK file without the "log file".

I tried using:

Restore database dbName
from disk = '" & path & "'"

but it didn't work, exception that refers to

"not finding _log file"

more specifically:

Cannot open backup device 'D:\AlomohasebSQL.bak'.
Operating system error 2(Invalid path).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


texts:
Is there a way to have a PostgreSQL user that can drop all but specified tables?

We need the user to be able to drop some tables but not all. It would be preferred to be able to use something like revoke where we could specify which tables the user should not be able to drop.


texts: Space used by databases file through linked server

texts:
I need to find out space used by database file for multiple databases through linked server(from one central server to multiple client SQL servers).
I created view on client size, for all databases:

CREATE VIEW [dbo].[view] AS SELECT name AS 'DataFileName',
   physical_name AS 'DataPhysicalName',
   ROUND(size / 128, 1) AS 'DataTotalSizeInMB',
   ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS
   'DataSpaceUsedInMB',
   ROUND(size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0, 1) AS
   'DataAvailableSpaceInMB' FROM  [sys].[database_files] WHERE type = 0

and on server side I used loop to query this view. But column "DataSpaceUsedInMB" works only for default database for my login.


texts: Not all members in a Windows group have permissions

texts:
I ran into a bit of an odd situation.  Let's say I have a domain group [DMN\Group] which has been created as a login on my server and a user in the database.  One of the members in that group is not getting the permissions that have been assigned to the group.  What's odd to me about this situation is that when I do

exec xp_logininfo 'DMN\Group', 'members'

the user shows up.  However, when I do

exec xp_logininfo 'DMN\user', 'all'

the group doesn't show up as a permission path.  How can this happen?  And, perhaps more importantly, how can I fix it?


texts: Is there a way to check connection properties of an active SQL Server connection?

texts:
The context:

I have a Java application that's using HikariCP for its connection pooling solution. While lightning-fast, HikariCP doesn't have a lot of configuration options, by design, and that's all fine and dandy except I need to set a connection property, and while a DataSource like Tomcat's gives me the ability to do something like:

tomcatDataSource.setConnectionProperties("someProperty=true");

Hikari's DataSource object doesn't have such a method.

My options, as far as I've been able to tell are to either create a DataSource object manually and pass it to Hikari to wrap it in it's own class, or to include the connection property as part of the connection URL.

(Just to make sure we're on the same page, I'm talking about the "sendStringParametersAsUnicode" flag as described in this document: Setting the connection properties - SQL Server)

The question (also known as "so far this question seems to belong in StackOverflow but hopefully you kept reading"):

Is there a way, on the database side, to see the connection properties of an active connection? In SSMS or some other tool? I tried SSMS's activity monitor on the target database, and right clicking on anything that seemed right-clickable, but nothing mentioned the properties of the open connections. Is this even possible?

I'd like to see the properties so I can double-check if my two options described above for setting this flag actually work.


texts:
I tried phpsqliteadmin

But it doesn't seem to work anymore on PHP 5.4.

There are several alternatives, but none of them seems to be "The One" that is maintained and widely used.

Is there a standard SQLite admin webinterface, (preferably installable via apt)


texts: Exporting an HBase table which is larger than the available HDFS space?

texts:
I have a 1TB hbase table which I need to export. Unfortunately I only have 600GB free in HDFS, so I was hoping to export it to a mounted file share.

I've learned that the hbase export can only export to HDFS. Due to the above constraints I lack the space to do so.

Is there any way I can export the data of this table without increasing the available HDFS space?


texts:
I have a performance bottleneck with a SELECT GROUP BY operation.
Schema
I have a table like this:
CREATE TABLE [InverterData](
    [InverterID] [bigint] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [ValueA] [decimal](18, 2) NULL,
    [ValueB] [decimal](18, 2) NULL
    CONSTRAINT [PrimaryKey_e149e28f-5754-4229-be01-65fafeebce16] PRIMARY KEY CLUSTERED
    (
        [TimeStamp] DESC,
        [InverterID] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON)
)

and a Index like this:
CREATE NONCLUSTERED INDEX [TimeStamp_Power-NonClusteredIndex] ON [dbo].[InverterData]
(
    [InverterID] ASC,
    [TimeStamp] ASC
)
INCLUDE
(
    [ValueA],
    [ValueB]
)

The [InverterData] table as has following storage stats:

Data space: 26,901.86 MB
Row count: 131,827,749
Partitioned: true
Partition count: 62

Usage
With my descripted schema (one plus some extra table that are not important for my question) I can run super fast queries like this:
SELECT [TimeStamp], [ValueA], [ValueB]

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160108'
 AND [InverterData].[TimeStamp] < '20160109'

Excecution timespan: 178ms
Excecution plan:

Problem
I now want to make a SELECT GROUP BY by [InverterID] and 15 minute interval of [TimeStamp].
Somethink like this:
SELECT [InverterID]
 , DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [TimeStamp] ) / 15 * 15, 0) AS [TimeStamp]
 , SUM([ValueA]), SUM([ValueB])

FROM [InverterData]

JOIN [Inverter] ON [Inverter].[ID] = [InverterData].[InverterID]
JOIN [DataLogger] ON [DataLogger].[ID] = [Inverter].[DataLoggerID]

WHERE [DataLogger].[ProjectID] = 20686
 AND [InverterData].[TimeStamp] >= '20160107'
 AND [InverterData].[TimeStamp] < '20160108'

GROUP BY
 [InverterID], DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [InverterData].[TimeStamp] ) / 15 * 15, 0)

Excecution timespan: 4637ms
Excecution plan:

Attempts
I think it can be related to the necessary Sort operation here:

As far as I know it is possible to avoid this SORT by creating a matching indexer. But I don't know how to do this with my 15 minute interval grouping.
Question
As you can see the excecution timespan of the SELECT GROUP BY is massivly longer. But I don't know where and how to avoid the bottleneck!?
Update 1 (related to @Max Vernon answer)
If it is possible ide like to have a faster solution where I can flexible change the interval (for example 10, 15 or 6o minutes). So without calculated columns.


texts: Why does constructing this jsonb_array from two jsonb objects fail?

texts:
Just trying to answer someone's question, and I'm wanting to run this by someone else. Constructing a jsonb array with two key value pairs.

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'::jsonb
  ))
) AS t(x);

Getting this error

ERROR:  invalid input syntax for type json
LINE 5:         '{"k1":"v1","k2":"v2"}'::jsonb
                ^
DETAIL:  Token "" is invalid.
CONTEXT:  JSON data, line 1: {"k1":"v1","k2"...

But this works (dropping the second k/v pair of the second element in the array)

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1"}'::jsonb
  ))
) AS t(x);

Constructing the jsonb array from a jsonb and a text also works..

SELECT *
FROM ( VALUES
  (jsonb_build_array(
    '{"k1":"v1","k2":"v2"}'::jsonb,
    '{"k1":"v1","k2":"v2"}'
  ))
) AS t(x);

I'm thinking this is a bug.


texts: PostgreSQL Common Table Expressions vs a temporary table?

texts:
The PostgreSQL documentation on WITH shows the following example:

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

It also notes:

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries.

I see that WITH can be used for other things, like recursive evaluation. But in the example above, is there any important difference between using WITH and creating temporary tables?


texts: Get command line of connection, even if "pg_stat_activity.application_name" is empty

texts:
I would like to know who is blocking the development DB from being dropped.
But "pg_stat_activity.application_name" is empty.
Is there a way to get to the command-line nevertheless?
PostgreSQL: 11.11


texts: "login failed for user " on SQL Azure from azure hosted web application

texts:
I have an Azure ASP.net MVC web application which uses a SQL Azure db. It was working fine for some months (someday for some reason Azure changed the name of the server and I had to set that again but everything works fine), but now it does not work.

First I thought, they may have changed the server name again, but I opened my SSMS and after resetting the password it connected again. So I change that password in my application connection strings and test everything worked, and yes, debug and tests work fine. So I published, but once published the application throws error

Login failed for user 'my_username'

I check on this post Azure SQL Database "Login failed for user" in application, but works fine in SSMS which seemed similar but I can not do that without getting errors.

I changed my connection string from this

Server=tcp:SERVER_NAME.database.windows.net,1433;Database=my_db;User ID=my_username@qtdhcrrxmi;Password={my_password}; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

to this

Server=tcp:SERVER_NAME.database.windows.net,1433;Initial Catalog=my_db;Persist Security Info=False;User ID={my_username};Password={my_password};Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

as it is shown in the Azure portal, in the database connection strings. I only removed the attribute

 Data Source=SERVER_NAME.database.windows.net;

I also checked that publish procedure does not change connection strings.

But it still works on debug and on SSMS but not on production.


texts: Unable to create Functions/Store Procedure Azure mySQL

texts:
I have just set up a MySQL database with Azure and I am using Navicat to connect to it and everything is working fine. I am now trying to create some Functions and Store Procs but I am getting a Super User Error:
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) > Time: 0.027s

Here is My Code for one of them:
CREATE function `triggertradersazure`.`sf_entity_active_sub`(`p_entity_id` INT)
returns INT(11)
DETERMINISTIC
begin
  IF EXISTS (SELECT 1
             FROM   tbl_stripe_customer_link_sub sc
                    JOIN tbl_stripe_sub_payments ss
                      ON sc.stripe_customer_id = ss.stripe_customer_id
                         AND ss.stripe_subscription_id =
                             sc.stripe_subscription_id
             WHERE  active = 'Y'
                    AND entity_id = p_entity_id) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  end IF;
end;

User Info from mql.user:
INSERT INTO `mysql`.`user`
            (`host`, `user`, `password`, `select_priv`, `insert_priv`, `update_priv`, `delete_priv`, `create_priv`, `drop_priv`, `reload_priv`, `shutdown_priv`, `process_priv`, `file_priv`, `grant_priv`, `references_priv`, `index_priv`, `alter_priv`, `show_db_priv`, `super_priv`, `create_tmp_table_priv`, `lock_tables_priv`, `execute_priv`, `repl_slave_priv`, `repl_client_priv`, `create_view_priv`, `show_view_priv`, `create_routine_priv`, `alter_routine_priv`, `create_user_priv`, `event_priv`, `trigger_priv`, `create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`)
VALUES      ('%', 'triggertraders', '*', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '', 'N');

This user does not have super user but I should not need it and Azure doesn't allow it?
Not sure what to do with this at the moment. My function had
`CREATE DEFINER = `tiggertraders`@`ip`

I removed this as this is meant to fix the problem.
Any help would be great,


texts: mysql (mariadb) won't start after power outage (MacOS)

texts:
/var/log/system.log says (over and over)
Oct 13 19:34:01 Data-Server com.apple.xpc.launchd[1] (com.mariadb.server[2128]): Service exited with abnormal code: 1
Oct 13 19:34:01 Data-Server com.apple.xpc.launchd[1] (com.mariadb.server): Service only ran for 0 seconds. Pushing respawn out by 10 seconds.

/usr/local/var/mysql/Data-Server.local.err says (once recently, repeated a number of times well before the crash)
2020-10-13  2:44:25 20019181 [Warning] Aborted connection 20019181 to db: 'EcoReality' user: 'root' host: '10.1.2.2' (Got timeout reading communication packets)

First thing I did was to shutdown the launchctl entry, to keep it from constantly restarting.
# launchctl unload /Library/LaunchDaemons/com.mariadb.server.plist
Then I tried invoking mysqld manually:
# sudo  /usr/local/bin/mysqld -u mysql
2020-10-13 20:46:09 0 [Note] /usr/local/bin/mysqld (mysqld 10.4.6-MariaDB) starting as process 2364 ...
2020-10-13 20:46:09 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-13 20:46:09 0 [Note] InnoDB: Uses event mutexes
2020-10-13 20:46:09 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-13 20:46:09 0 [Note] InnoDB: Number of pools: 1
2020-10-13 20:46:09 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-13 20:46:09 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-13 20:46:09 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-13 20:46:09 0 [ERROR] InnoDB: Invalid log block checksum. block: 81635496 checkpoint no: 2609153 expected: 296846624 found: 3735928559
2020-10-13 20:46:09 0 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 41797373564 between the checkpoint 41797373564 and the end 41797373440.
2020-10-13 20:46:09 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-10-13 20:46:09 0 [Note] InnoDB: Starting shutdown...
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-13 20:46:09 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-13 20:46:09 0 [Note] CONNECT: Version 1.06.0009 January 27, 2019
2020-10-13 20:46:09 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2020-10-13 20:46:09 0 [ERROR] Aborting

So now I'm a bit stumped at the lack of diagnostic messages. Is there any way to coax more info out of mysqld when it goes down? Or should I just start incrementing innodb_force_recovery until something interesting happens?


texts:
I am attempting to run a dynamic SQL statement and it works for any storename that does not have an apostrophe in it.  However, when the storename has an apostrophe in it the syntax does not properly, however I am stuck using dynamic SQL in this instance.

How should I ammend this syntax in order for storename to include an apastrophe?

Here is DDL

Declare @TestHyphens Table(StoreName varchar(500),topItemSale varchar(500))

Insert Into @TestHyphens (StoreName, topItemSale) Values
('Bob''s Burger''s','Pen'), ('Jacks Coffee Shop','Pencil')
,('Larry''s Hair Shack','Rainbow'), ('Manny''s Sandwich Shop','Sunglasses')

Declare @sql nvarchar(4000), @storename varchar(500)

Declare db_cursor CURSOR FOR
Select StoreName
FROM @TestHyphens

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @storename

While @@FETCH_STATUS = 0
BEGIN

        Set @storename = REPLACE(@storename, '''', '''''''''');

        SET @storename = CHAR(39)+CHAR(39)+@storename+CHAR(39)+CHAR(39);

        SET @SQL = N'Select * from @TestHyphens where storename IN ('+@storename+ N')'

        Print @storename
        Print @sql

    FETCH NEXT FROM db_cursor INTO @storename

END

Close db_cursor
DEALLOCATE db_cursor

And print statements produce:

''Bob''''s Burger''''s''
Select * from @TestHyphens where storename IN (''Bob''''s Burger''''s'')
''Jacks Coffee Shop''
Select * from @TestHyphens where storename IN (''Jacks Coffee Shop'')
''Larry''''s Hair Shack''
Select * from @TestHyphens where storename IN (''Larry''''s Hair Shack'')
''Manny''''s Sandwich Shop''
Select * from @TestHyphens where storename IN (''Manny''''s Sandwich Shop'')

And as you can see these are incorrectly hyphenated -

''Manny''''s Sandwich Shop''
''Larry''''s Hair Shack''
''Bob''''s Burger''''s''


texts: How to use a variable for object's owner (schema)?

texts:
In Oracle we can use a variable instead of the object's owner. The value of this variable will be prompted by Oracle when the scripts starts.

Something like CREATE TABLE **&owner.**MY_TABLE.

I'm trying to accomplish the same thing with SQL Server but I failed to find how to do it.


texts:
What is the maximum row count for a table in MYSQL?

I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 bytes. Upon reaching how many rows should there be an alarm? They are intermittently used so archiving them will involve some additional work.

Which of the is advisable?
1. Archiving
2. Moving to big data
3. Leaving them as it is.


texts: Migrate small sets of data from production to staging/sandbox

texts:
I have a development environment that consists of a production, staging and development servers that host SharePoint 2010 or 2013 as well as SQL Server 2008 R2. These each need to have relevant data, though there is limited bandwidth and hardware price restrictions that we must abide by.

Is there any way we can take a small portion of production data, and transfer it to our staging and development environments? And by small portion, I mean, say, the last month of data. So the storage impacts will be minimal as well as time consumption to do additional backups and/or transfer times. Our production database is a couple terabytes.

Possible restrictions:

Limited bandwidth for transfers between domains (prod -> dev)
Huge database sizes
SharePoint: simply keeping the schema and having the last month of updated data will cause a lot of orphaned / corrupted data, so there has to be some kind of seeded data to keep integrity

The overall goal is to reduce the database size of development and staging servers, while being able to capture the most recent data.

Preferred solutions; scripts or software.


texts: Getting current date as part of an Stored Procedure

texts:
I have the following SP:

SELECT
   moncallAdd.FirstListing,
   DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
   DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
                 DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
 mdr.dbo.mOnCallAdd
 where DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
         DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE())) and
 mOnCallAdd.SchedName = 'arc im'

UNION
SELECT
 moncallDelete.FirstListing,
 DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
         DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
 DATEADD(MINUTE, mOnCallDelete.duration,
         DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
                 DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
  mdr.dbo.mOnCallDelete
where DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
         DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE())) and
  mOnCallDelete.SchedName = 'arc im'

and what I'm trying to do is as follows:

If I have a schedule that runs from 9 a.m. today to 5 p.m. today, and I run my SP anytime during that time frame, I'd like to see that schedule as part of my dataset. I can make this work for date alone, but can't figure out the time portion. Can anyone offer any assistance on this?

  Addendum (was an answer, merging in -- jcolebrand)

Richard,

First let me thank you for your insight about the design of this table, and I will agree with you that it was poorly designed, but I didn't design it (thank god.) Secondly, I tried your query and I am getting better results, but it's still not eliminating all of the data that shouldn't be returned yet. Here's two results from my dataset that should have not shown up:

HEART HOSPITAL - xxxxx  2011-08-15 19:00:00.000 2011-08-16 07:00:00.000
HEART HOSPITAL - xxxxx  2011-08-15 13:00:00.000 2011-08-15 19:00:00.000

(the query was run at 9:18 CST so these wouldn't have occurred yet.)

When I run this query:

SELECT DATEADD(DAY,38490,'12/31/1899'), CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME)

(38490 is the int in my first value of the StartOncallDate field)

it returns a value of:

2005-05-19 00:00:00.000 2011-08-15 00:00:00.000

Also here are a couple of lines of data in my table:

     Rec Id  Timestamp        SchedName     StartOnCallDate   StartOncallTime    FirstListing     Duration       AddDate  AddTime
    70550    55426893      BITTAR&ESKEW        38490               1020             YIUM            840           38490    1293
    70551    55427287        ZZOB02            38494               1020         CARTER, KIMBERLY    900           38491     247


texts: How to reset statistics after UPDATE STATISTICS ... WITH ROWCOUNT

texts:
For query tuning and testing purposes, you can manually assign a rowcount and pagecount to a table's index statistics by running UPDATE STATISTICS. But how do you recompute/reset the statistics to the table's actual contents?

--- Create a table..
CREATE TABLE dbo.StatTest (
    i      int NOT NULL,
    CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i)
);
GO

--- .. and give it a thousand-or-so rows:
DECLARE @i int=1;
INSERT INTO dbo.StatTest (i) VALUES (@i);

WHILE (@i<1000) BEGIN;
    INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest;
    SET @i=@i*2;
END;

A dummy query:

SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;

... will return the following query plan (the row estimate in the Index Scan is 1024 rows).

Run the UPDATE STATISTICS command..

UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

... and the plan looks like this, now with an estimate of 10 million rows:

How do I reset the rowcount to the actual contents of the table without using WITH ROWCOUNT?

I've tried WITH FULLSCAN, WITH RESAMPLE and WITH SAMPLE n ROWS, but the statistics rowcount remains 10 million rows. Inserting a row or even deleting all of the rows doesn't update the statistics, because the change is too small.


texts:
I need your advice.

Our company is about to cancel the SQL Server support.

I have this instinct feeling this is a very bad idea.

No more SQL Server updates, I know, but even when that is reason enough for me to keep the support, my company thinks my Data Warehouse is running perfectly. So no reason to update "non existing" bugs, right?

My question for you is: Why would you keep the SQL Server support contract running?


texts:
I had to remove a cassandra dead node on a hurry (because an important script with a truncate query wouldn't work due to consistency level reasons -- any thoughts on that would be nice too).

Anyway, the data is still there and I don't want to risk losing any of it. If my understanding is correct, I'd need to delete the data (move it away) and then reattach the node to the cluster as if it was a new one.

Could I just run the node with all its data there or could it cause any sort of corruption?


texts: Standard behaviour in executing multiple alter statements in Postgres

texts:
I was just experimenting with ALTER TABLE commands in psql. I came across a fact that we can give multiple alter clause within same ALTER TABLE command. Like

 ALTER TABLE EmployeeLoans add col1, add col2, drop col0,...

That it optimise row re-writes especially when we are adding multiple columns with default values.

I tried a query on the following table:

               Table "db145004db.employeeloans"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 empid      | integer |           | not null |
 empname    | citext  |           |          |
 empsurname | citext  |           |          |
 salary1    | bigint  |           |          |

Query1:

alter table employeeloans drop column salary1, add column salary1 bigint;

Query2:

alter table employeeloans add column salary1 bigint, drop column salary1;

I was expecting that the second query should throw an error, that the column 'salary' already exists. But, postgres doesn't.

Similiarly, while adding unique-constraint on a column that is going to be dropped

Query3:

alter table employeeloans add constraint salary1_unique unique(salary1), drop column salary1;
ERROR:  column "salary1" named in key does not exist

By seeing this error, I interpret that column salary1 is dropped before creating the unique key;

So,I was wondering what we will be the execution plan/order while giving multiple alters in same SQL command. Is this behaviour compatible with ANSI standard (if any). Any detailed (referring postgres source code) or "explain me like i'm 5" answers are welcomed.


texts: Master/Slave and Master/Master replication - MySQL

texts:
What is the difference between having a master-slave replication and having a master-master( where in the second master acting as hot-standby - active-passive) replication?
Also, what are the issues that I could face having a master master replication where both the masters are writable. I have read here that experts have said it is very dangerous to have both masters writable. Without Percona/NDB/DRBD, what are my options for setting up HA for my databases? What is the best replication that I can have?


texts: Restore job run successfully without error but actually not applied during log ship from older version to newer one

texts:
Our requirement is for SQL Server 2008 R2 databases to be upgraded/migrated to SQL Server 2016. We want to configure log shipping between SQL Server 2008 R2 (source) and SQL Server 2016 (target). We have also need to open these standby database after a long time (For example 1 month), while transaction logs will be deleted after two days. Is this possible?

Currently we have configured 2 test instances using NORECOVERY with Log Shopping jobs (Backup, Copy and Restore) running every hour, while Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?


texts:
I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided).

It seems, that different DBMS use different quoting for table names.

For example, I have the following in MySQL:

SELECT * FROM `tablename`;

while it is

SELECT * FROM [tablename];

in MS SQL Server.

Is there an "uniform" quoting that all databases support? Or is there a jdbc method that gives me tells me the correct quoting?


texts: How to filter values from different table inner join and group by

texts:
There Are 5 Tables BASE_Inventory, BASE_GoodsInward, BASE_GoodsInwardLine, BASE_GoodsOutward, BASE_GoodsOutwardLine

BASE_Inventory Table Inner Join to BASE_GoodsInwardLine and BASE_GoodsOutwardLine
BASE_GoodsInward Inner Join to BASE_GoodsInwardLine Table
BASE_GoodsOutward Inner Join to BASE_GoodsOutwardLine Table

Tables are below,

Inventory Table

CREATE TABLE BASE_Inventory
(
InventoryId BIGINT NOT NULL UNIQUE,
ItemNameCode VARCHAR(45) NOT NULL,
ItemDescription1 VARCHAR(255) NOT NULL,
CurrentQuantity DECIMAL(18, 4) DEFAULT 0.00 NOT NULL
PRIMARY KEY(InventoryId)
)

Goods Inward Table

CREATE TABLE BASE_GoodsInward
(
    GoodsInwardId BIGINT NOT NULL UNIQUE,
    GoodsInwardDate DATE NOT NULL
    PRIMARY KEY(GoodsInwardId)
)

CREATE TABLE BASE_GoodsInwardLine
(
    GoodsInwardLineId BIGINT NOT NULL UNIQUE,
    GoodsInwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    InwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsInwardLineId)
)

Goods Outward Table

CREATE TABLE BASE_GoodsOutward
(
    GoodsOutwardId BIGINT NOT NULL UNIQUE,
    GoodsOutwardDate DATE NOT NULL
    PRIMARY KEY(GoodsOutwardId)
)

CREATE TABLE BASE_GoodsOutwardLine
(
    GoodsOutwardLineId BIGINT NOT NULL UNIQUE,
    GoodsOutwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    OutwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsOutwardLineId)
)

Data with table

Inventory

Inward

Outward

I want to retrieve 2 type of result query from above result Inward & Outward tables (Inward and Outward Quantity should be SUM of Inventory ID)

1: Date wise Inventory Inward & Outward

2: Inventory wis Inward & Outward


texts: Temporal database design, with a twist (live vs draft rows)

texts:
Not sure if/how I should be cross-posting this... But in case some follow DBA but not SO...

Is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?

(Full details on the SO thread).

I'm looking into implementing object-versioning with the added twist of needing to have both live and draft objects, and could use the insights from someone experience in this, as I'm beginning to wonder if it's even possible without potentially horrific hacks.

I'll break it down to posts with tags for the sake of the example, but my use-case is a bit more general (involving slowly changing dimensions - http://en.wikipedia.org/wiki/Slowly_changing_dimension).

Suppose you've a posts table, a tags table, and a post2tag table:

posts (
 id
)

tags (
 id
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id)
)

I'm in need of a couple of things:

Being able to show exactly how a post looked like at an arbitrary datetime, including for deleted rows.
Keep track of who is editing what, for a complete audit trail.
Needs a set of materialized views ("live" tables) for the sake of keeping referential integrity (i.e. logging should be transparent to the developers).
Needs to be appropriately fast for live and the latest draft rows.
Being able to have a draft post coexist with a live post.

I've been investigating various options. So far, the best I've come up with (without points #4/#5) looks a bit like the SCD type6-hybrid setup, but instead of having a current boolean there's a materialized view for the current row. For all intents and purposes, it looks like this:

posts (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

post_revs (
 id,
 rev pkey,
 public,
 created_at,
 created_by,
 deleted_at
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by
)

tag_revs (
 id,
 public,
 rev pkey,
 created_at,
 created_by,
 deleted_at
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by
)

post2tag_revs (
 post_id,
 tag_id,
 post_rev fkey post_revs(rev), -- the rev when the relation started
 tag_rev fkey tag_revs(rev), -- the rev when the relation started
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_rev, tag_rev)
)

I'm using pg_temporal to maintain indexes on period(created_at, deleted_at). And I keep the various tables in sync using triggers. Yada yada yada... I created the triggers that allow to cancel an edit to posts/tags in such a way that the draft gets stored into the revs without being published. It works great.

Except when I need to worry about draft-row related relations on post2tag. In that case, all hell breaks loose, and this hints to me that I've some kind of design problem in there. But I'm running out of ideas...

I've considered introducing data duplication (i.e. n post2tag rows introduced for each draft revision). This kind of works, but tends to be a lot slower than I'd like it to be.

I've considered introducing drafts tables for the "last draft", but this quickly tends to become very very ugly.

I've considered all sorts of flags...

So question: is there a generally accepted means of managing live vs non-live rows in a row-version controlled environment? And if not, what have you tried and been reasonably successful with?


texts:
I'm dealing with a database whose plan cache seems to be periodically clearing out by itself. As an example, in the span of about 2 minutes, the SQL plan cache went from 12.5k MB to 3.1k MB. At this time and shortly after, the number of compilations shot up dramatically as well. The rest of the cache shrinks as well, with everything seeming to get smaller by a large amount.

I've looked through the default trace and don't see anything obvious. At first, I thought it was related to a linked server due to a correlation of some SHOW_STATISTIC calls coming from it during the same minute as when the cache cleared out, but it seems that that's what happens when a linked server executes a query across the way as it needs statistics to use. I don't see any DBCC FREEPROCCACHE calls in the default trace logs for any of these events either.

I have the transaction log that covers the timeframe when one of these events happened, but I'm still unfamiliar with navigating it.

I haven't seen any evidence to suggest it's one of the ALTER DATABASE or sp_configure commands that will clear the cache either. There are no events in the SQL Server Log that happen at the time of this clearing out.

I've noticed in SQL Sentry that a number of times that this happened, another process on the SQL box was taking up ~10% of CPU, but it's not during all of the events so I'm not certain if that's worthwhile to look into for this.

At this point, I suspect that a lot of data was changed during each of these events, and the resulting statistic changes made many plans in the cache obsolete, so they were kicked out. I'm not certain how to best check this though, without bogging the system down with an expensive trace. Is there an easy way to determine this through the default trace and transaction log?

Another factor in this is that some of the queries have over a minute (!) compile time, and are requesting multiple GB of memory per execution, while using a small fraction of that. I suspect these are all related problems, but am trying to determine the cause of the cache clearing to minimize the minute compilation issues.

In summary:
* Plan cache is dropping considerably in the span of a minute.
* The log doesn't contain any mention of commands that will clear the cache.
* I have the transaction log and default trace contents for the timeframe of a few of these events.

This is on SQL Server 2016 SP1 CU2, running in 2012 compatibility mode. Version: 13.0.4001.0. The server has 300 GB of Memory available. I haven't seen the cache get much larger than 14 GB, but most of the time it is about 12.5 GB.

Any ideas on where to go from here on how to determine what's causing this to drop?


texts: different login credentials based on user

texts:
I run a PHP symfony-framework web app for my company and it uses a single database login for all connections, whether the user is an admin or a client.

I know that higher security systems often use different logins for different levels of users.

Whether or not a use a framework, is this a security feature I should make use of?

Does it matter if the application is web-based or if it's intranet based?

To clarify:
Should I concern myself with creating a plugin for symfony to implement multiple db logins and should I do this for any other apps I create that are not based on a framework?

In case you're wondering, I'm using PHP5 and MySQL.


texts: Do I gain read performance improvement by using zlib rather than snappy compression in MongoDB?

texts:
My current storage engine is WiredTiger and its compression level is as default, snappy. I've come across MongoDB documentation and it's been mentioned that using zlib compress better but needs more CPU.

I want to know will zlib store more data in memory compared to snappy as it compress the data? I have a server with 16 CPU cores. As RAM is more expensive I'd rather to save on memory in case it keeps more data.

Is this correct? Can I blindly switch to zlib to cache more data and improve read performance?

NOTE: Our server is read intensive.


texts:
I have oracle 11g with extproc separately configured in listener.ora.

Users report some environmental variables that should be exported are not set.

From where does extproc gets it environment besides ENV in its definition in listener.ora? They come from shell that started listener? Why variables included in ENV do not appear?

How could I efficiently check what env variabls extproc has set?


texts: SQL server 2014 Transaction Log Backup fails

texts:
I have setup a Maintenance Plan for my SQL server 2014 Database. This Plan Generates Full Backup every Sunday, and Differential backup every Day and Transaction Log every hour.

This Maintenance plan worked fine initially, but now after few successful backups the Transaction Log backups are failing (specially during Production Hours).

When i restart the SQL server instance, then the Backups are generated fine again until the production hours (work hours). My Database is around 40Gb.

So what is causing this Backups to fail during Production hours? Any suggestions


texts:
Coming to SQL from other programming languages, the structure of a recursive query looks rather odd.  Walk through it step by step, and it seems to fall apart.

Consider the following simple example:

CREATE TABLE #NUMS
(N BIGINT);

INSERT INTO #NUMS
VALUES (3), (5), (7);

WITH R AS
(
    SELECT N FROM #NUMS
    UNION ALL
    SELECT N*N AS N FROM R WHERE N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Let's walk through it.

First, the anchor member executes and the result set is put into R.  So R is initialized to {3, 5, 7}.

Then, execution drops below the UNION ALL and the recursive member is executed for the first time.  It executes on R (that is, on the R that we currently have in hand:  {3, 5, 7}).  This results in {9, 25, 49}.

What does it do with this new result?  Does it append {9, 25, 49} to the existing {3, 5, 7}, label the resulting union R, and then carry on with the recursion from there?  Or does it redefine R to be only this new result {9, 25, 49} and do all the union-ing later?

Neither choice makes sense.

If R is now {3, 5, 7, 9, 25, 49} and we execute the next iteration of the recursion, then we will end up with {9, 25, 49, 81, 625, 2401} and we've lost {3, 5, 7}.

If R is now only {9, 25, 49}, then we have a mislabeling problem.  R is understood to be the union of the anchor member result set and all the subsequent recursive member result sets.  Whereas {9, 25, 49} is only a component of R.  It is not the full R that we have accrued so far.  Therefore, to write the recursive member as selecting from R makes no sense.

I certainly appreciate what @Max Vernon and @Michael S. have detailed below.  Namely, that (1) all the components are created up to the recursion limit or null set, and then (2) all the components are unioned together.  This is how I understand SQL recursion to actually work.

If we were redesigning SQL, maybe we would enforce a more clear and explicit syntax, something like this:

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS
    UNION ALL
    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Sort of like an inductive proof in mathematics.

The problem with SQL recursion as it currently stands is that it is written in a confusing way.  The way it is written says that each component is formed by selecting from R, but it does not mean the full R that has been (or, appears to have been) constructed so far.  It just means the previous component.


texts: Troubleshooting high CPU usage from postgres and postmaster services?

texts: I'm using an open source (RHEL 6.2) based machine running SIEM software. When I run the top command, I see postgres and postmaster both with 96% CPU usage. Is there a way to pin-point or see what causing these service to stack up?


texts: With Postgresql v10 and WAL2JSON... Is it possible to include the database name / datname in the json?

texts: We have a set of databases that were not set up in a multi-tenant fashion. Looking to build a hub that will hold the data in a multi-tenant fashion using wal2json to pipe data from source to the hub. With each JSON object, I need to know which database it's coming from. Is this possible?


texts: Why mongodb logs commands in the log file and how to avoid that?

texts:
OS: Windows 10/ Server 2012

Mongodb verison : 3.4.9

I have set the mongod --logPath and --quiet command line parameter

The logs are just growing beyond 100mb and not rotating. Seeing the logs I see lot of entries example:

  I COMMAND  [conn9] command DBNAME.VERSIONS command: find {

How can I restrict mongodb to log these as I really do not care about the command


texts: How do I allow remote mysql access to all users?

texts:
I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure mysql for the same?

I know about the GRANT ALL PRIVILEGES command, but that would require me to individually grant remote access to all the users.


texts:
I am attempting to achieve the following within a single data pump export:

SCHEMA1 = Structures only
SCHEMA2 = Structures only
SCHEMA3 = Structures and data

The answer in this question only works on the table level:

Export Data Pump all data from few schemas except one table structure only?

It suggests removing data from a specific table using a filter:

SCHEMA2.table1: WHERE 1=2

I need the same but applied to all data in the schemas other than SCHEMA3.

How can this be done?


texts:
I was going through upgrade process of mysql server. It is mentioned that to upgrade from 4.1 to 5.5 we need to upgrade step by step like first to 5.0,5.1 ...5.4 and then 5.5.

I have some questions :

What if we directly upgrade from 4.1 to 5.5. What might get effected? functionality or problem related to upgrading
Chances to loose data ? Even though I'll take backup using 'mysqldump --all-data-base' option.
Lets say, I have two database. I'll take only two database backup. I'll install latest version of mysql 5.7 and load two database back. What are chances to loose data or get corrupted ?

I'm curious to know this. If anybody tried? Because I need to do upgrade in live server.

Is there any step by step document which tells exact steps to upgrade from 4.1 to 5.5 in linux server


texts: Postgres Repeatable Read vs Serializable

texts:
I'm trying to understand whether the repeatable read isolation level is good enough for my scenario in an application that uses Postgres, but the docs are making it difficult to understand which is best suited.
I have an application that starts a transaction that first reads the current value of a single row based on the primary key of that row, calculates what the new state of that row should be after receiving an event from a message queue, and then updates the state of that row in the database.
Given that there are multiple instances of the application deployed, and that multiple events on the message queue can be received for the same primary key at the same time, it's possible that 2 transactions could attempt to enter the above described transaction at the same time. Is repeatable read isolation good enough for this case, or do I need to consider using serializable? My assumption is that if the first transaction attempts to commit its result to the database whilst the second transaction is in progress, the second transaction will fail with a conflict because it sees that the row it was attempting to update was modified by the first transaction - is this correct?
And a follow-up question: I'd like to understand how much more 'expensive' it is to use serializable transaction isolation vs repeatable read - I want to understand the underlying mechanism of what's going on within postgres - i.e. is there locking going on, and how does that affect performance of other queries running at the same time?


texts: How can I get this code to work? It's in mysql, I keep getting an error 1822' failed to add foreign key constraint. Missing index for constraint'

texts:
CREATE TABLE EMPLOYEE (
 employeeNumber VARCHAR (15) NOT NULL,
employeeName VARCHAR (20) NOT NULL,
department VARCHAR (10) NOT NULL,
 phone INT NOT NULL,
PRIMARY KEY (employeeName, department)

CREATE TABLE DEPARTMENTS(
department VARCHAR (10) NOT NULL,
 department Code VARCHAR(10) NOT NULL,
department Name VARCHAR(30) NOT NULL,
FOREIGN KEY (department)
REFERENCES EMPLOYEE (department)

CREATE TABLE EMAIL_INFO(
 employee Name VARCHAR (20) NOT NULL,
 email VARCHAR (30) NOT NULL,
FOREIGN KEY (employeeName)
 REFERENCES EMPLOYEE (employeeName)
);


texts:
Currently I am trying to create a database design that is long lasting. But I hit a point where I not know how to continue.

I have a object table in my database. That tables stores one unique object (With a incremental ID). However for the application I am creating attributes about that object are required (extra information so a sort of metadata for that object).

These are now five different attributes per object. But I already know in the upcoming future they want more attributes added to a object so they can get more information about it.

If it would have stayed with those five attributes a would create extra columns in the object table.

But now I am thinking to create seperate attributes table where I store that data. And I was thinking in the sorts of:

Table: attributes

Columns: object_id, object_type, object_data. Is this the best practice or what are your thoughts on the matter?


texts: SSAS Calculated Field - rolling sum, for a year of days

texts:
I have a fact table storing employee occurrences incurred day over day.
I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "Acitve Occurrences" Measure's AggregateFunction set to LastChild.
The problem happens as there can be more then one "entry" in a day. "Entry" is also a measure with its AggregateFunction set to LastChild. The result is that the occurrences still sum in instances with multiple "entry".

All of this feels terribly wrong. It screams to me that the "Active Occurrences" (the 365 day rolling sum) should be a calculation instead of done in the ETL. The problem is my lack of knowledge with MDX, at this point.

What would a calculation for this look like?

The reason why I would need the individual entries in the day, is that they have a reference to a type/reason dimension. Flattening the figures to the single day would lose those individual references.

Thank you in advance.


texts: In SQL Server would misc pages be pushed out when an object grows within a mixed extent?

texts:
In SQL Server 2008-2012, objects (including tables) are first put in a mixed extent. An extent can accommodate up to 8 pages and accommodates more than one type of page (meaning: pages from more than one object). Say ExtentA has pages from objects A(2), B(2) and C(4) - numbers in parentheses indicate how many pages they own within that extent. Object B grows, and my question is: Would SQL Server start kicking out A and C's pages before assigning B a new, uniform extent (assuming it eventually outgrows extentA) OR it just jumps to a new extent given extentA is already full.

This question was moved from Stack Overflow to DBA. Comments on the original question:

  Valid question, but why do you ask? Seems to be totally unimportant knowledge. –  usr

  Hi, Thanks for the reply. I'm actually in a very difficult database optimization situation in which I have to crack down on unnecessary spaces being allocated for numerous tables (1000+ tables). So I'm starting from the basics... I couldn't figure out this question because I had no means of tracking it.. I can see that a new extent is being assigned, but I cannot find out whether the previous extent was fully used... –  LearnByReading


texts:
When I use interval 1 I got an error in my SQL syntax. I want to get month now -1 to have a month before.

SELECT
      catalog.code,
      catalog.supplier,
      catalog.created_at
FROM  catalog
WHERE MONTH(catalog.created_at) = month(NOW()) - INTERVAL 1
AND   YEAR(catalog.created_at) = year(NOW())


texts:
I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and restore the same .bak file (on the same server) into a new database, this way, i always have the most up-to-date database to test on.

here are the steps:

back up database ABC (call it abc.bak)
drop (or clean/clear) database abc_test
restore abc.bak into abc_test

Please let me know if anyone know or have a script that does so.

Thanks!


texts: Importing dump file contents to another DB with different schema using Oracle Data Pump

texts:
I asked this question in stackoverflow but no one was able to answer it so am trying my luck here if anyone knew what am talking about. Here is the link original question.

What i wanted to do is to import all contents from dump file to another database which has a different schema. I tried the command below but got error since oracle doesn't allow SYSTEM to SYSTEM remap.

C:\>impdp SYSTEM/password@testing directory=imp_dir dumpfile=dumpFile.dmp logfile=dump_log.log full=yes remap_schema=SYSTEM:SYSTEM

My question is there a way to import all contents of dump file without having to repeat the command below over and over again until all schemas are copied? Thanks

C:\>impdp SYSTEM/password@testing directory=imp_dir dumpfile=dumpFile.dmp logfile=dump_log.log full=yes remap_schema=schema1:schema1


texts: PgTap, unit test a foreign key is deferrable

texts:
I am using PgTap to unit test a Postgres (v9.4) database structure in a continuous migration process. It works great.

Here is my question: « Is there a way to control that a foreign key constraint is deferrable (initially immediate) ? »

I do already check foreign keys but I want to ensure some of them are deferrable.

Thank you for your help.


texts:
Which SQL command is recommended to convert a date to yyyymmdd format?

convert(varchar(8), getdate(), 112); or
convert(varchar, getdate(), 112)

I notice that if I use the second one, it will append two spaces after the date. (e.g. [20130705] - notice the two space after the value 20130705)

Is it recommended to use the first SQL statement?


texts: Insert Value based on One Column based on Value on another Column

texts:
I just tried to set _timeStamp column to store value on the basis of HOUR of _date column and tried to create table as like below but encounter 1064 error. I'm new to mysql so help me in figuring out mine mistake.

create table `help` (`_ID` integer not null,
`_date` datetime not null,
`_timeStamp` integer not null
        SET  (1 if HOUR(`_date`)<10 else 2 if HOUR(`_date`)<16 else 3 if HOUR(`_date`)<24
            else -1),
PRIMARY KEY (`_ID`)
);


texts: Why is my stored procedure very much slower than the same MYSQL query?

texts:
My stored procedure is very slow compared to a normal query or a temporary table. How can I optimize my stored procedure?

  CREATE DEFINER=`datasll`@`...` PROCEDURE `product`()
  BEGIN
  EXPLAIN SELECT
         cs.id,
         cs.fk_c,
         cs.so,
         cs.pr,
         cs.date,
         cs.o_p,
         cs.sh_c,
         cs.x,
         LEFT(cs.x,2),
         cs.doc,
         cs.fk_op,
         cc.id_c_c,
         cc.name,
         cc.descrip,
         left(cc.n_o,3) as bon,
         cc.fk_c_br,
         cc.de,
         cc.m_mat,
         cc.color,
         cc.fk_c_s
  FROM
        data_pro.catas cs

  LEFT OUTER JOIN
        data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
  WHERE
        data_pro.cc.pap = 1
  AND   data_pro.cc.stati = 'b';
  END

When I use EXPLAIN this is the result:


texts: Check if DB2 backup image contains any tablespace not using automatic storage?

texts:
I want to check a backup DB2 image if it contain any tablespace using non-automatic storage.

I had tried to use db2ckbkp commands. But it seems it cannot tell me if any tablespace using non-automatic storage.

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

       NONAUTOTS
                  tbspInImage: T

                           ID: 7
                        flags: 0x101
                       flags2: 0x0
                  extent_size: 32
                prefetch_size: 16
                      version: 4
                      flavour: 9
                        state: 0x0
          statechangeobjectid: 0
            statechangepoolid: 0
                      LifeLSN: 0000000000062B2C
               LoadPendingLSN: 0000000000000000
              LoadRecoveryLSN: 0000000000000000
                     BeginLSN: 0000000000000000
                       EndLFS: 0
                   StordefLSN: 0000000000000000
              Full Backup LSN: 0000000000000000
              Last Backup LSN: 0000000000000000
             Full Backup Time:  00000000 = "19700101080000"
             Last Backup Time:  00000000 = "19700101080000"
                   TotalPages: 0
                 UseablePages: 0
                  reorgPoolID: 0
                   reorgObjID: 0
               poolReorgCount: 0
              # of containers: 1
                current_group: 0
                    cont_csum: 3582768349
          current_map_entries: 0
                    page_size: 4096
                     map_csum: 0
         tsp rfwd encountered: 16
             storage group ID: -1
      source storage group ID: -1
                     data tag: 0
    recovery storage group ID: -1
   recovery storage group LSN: 0000000000000000

                Container CB
                                 Type: 0x0
                           TotalPages: 0
                          UsablePages: 0
                   # of OS rsvd bytes: 0
                        Page 0 offset: 0
                           Tag offset: 0
                        Extent offset: 0
                                 Name: C:\Users\anson.wong\Desktop\ABC\nonAutoTS

I found a command from DB2 RESTORE which could generate a script file containing information about tablespace using non-automatic storage. But I prefer handling it in memory without the need to write a file.

db2 RESTORE DATABASE NEWDB FROM "C:/Users/Desktop/ABC" redirect GENERATE SCRIPT generatedScript.txt

Tablespace name                          = NONAUTOTS
Tablespace ID                            = 7
Tablespace Type                          = System managed space
...
Using automatic storage                  = No

Thank you.


texts: MySQL set column value if another column's new value is different than current one

texts:
I have a table that has a column mycolumn and a column that has a boolean flag mycolumn_is_new. I want to set the second one to true when an UPDATE query changes the value of the first one. The user can type the same data or new data.

I have come up with this query, but mycolumn_is_new is always set to false :

UPDATE mytable SET
    mycolumn = '1234',
    mycolumn_is_new = IF(mycolumn <> '1234', TRUE, FALSE)
WHERE id = 321

Is it possible check the new and old value in a single query?


texts:
We are working with some legacy code that is using a sub-optimally normalized DB.  The problem that we are trying to solve is best described with an illustrative example.
hi_priority and lo_priority are two tables that have the same schema, as defined below:
CREATE TABLE hi_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);

CREATE TABLE lo_priority
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(16),
  col1 VARCHAR(16),
  col2 INT,

  PRIMARY KEY(id)
);

In each table there is a 1:1 mapping between the name and col2.
The idea is to potentially SELECT name, col1, col2 from both tables but limit the total number of distinct names (let's say 7 for our illustrative example), subject to the following constraints:

Preference is to be given to entries from hi_priority table over entries from lo_priority tables.
For example, if there are 15 distinct names in hi_priority table,
we will select all rows with 7 names from hi_priority table,
ordered by col2.
If there are 5 distinct names in hi_priority
table, we select all rows with those 5 names and on top of that we
select all rows with 2 distinct names from lo_priority table,
ordered by col2
If there are some entries in both the
hi_priority table and the lo_priority table, we select entries
only from the hi_priority table

Let us assume that the tables are populated as follows:
INSERT INTO hi_priority(name, col1, col2) VALUES('john', 'kl7y5tis4yh4', 1), ('john', 'tiid6iywq02k', 1),
('john', 'detawgsxz615', 1), ('amy', 'i6u4hhc1trjk', 2), ('amy', 'wdpt0t5vtmbt', 2),
('amy', '87z5wgdfztwl', 2), ('amy', 'oj98jjdpb5yv', 2), ('steve', 'hllaazonflf0', 3),
('steve', '0h22y66kq3ow', 3), ('steve', 'o04ksti7di60', 3), ('steve', '3qlylbzqfr51', 3),
('steve', 'wmisshywtb12', 3), ('bob', 'ku8wpf7d6ta9', 3), ('bob', '6t7mn60g0g18', 3),
('bob', 'qv0s7ho3jku6', 3), ('bob', '5qgz7dznzwvn', 3), ('bob', 'mm76nhimm6fu', 3),
('bob', 'k7nlpksc55t2', 3), ('oliver', 'gvf9kjewpj7h', 4), ('oliver', '24w5s30w5te6', 4);

INSERT INTO lo_priority(name, col1, col2) VALUES('doug', 'j205tzrsfmax', 11), ('doug', '0w7rcazbh6es', 11),
('doug', '6xswf8frsjaw', 11), ('john', 'iw7d14vtysz2', 2), ('john', '6lg667dygaz1', 2),
('john', '83uk5dcobpu5', 2), ('john', 'tl8cpzatv0n9', 2), ('mike', '2dsarwozpmci', 3),
('mike', 'hinn6w03wdib', 3), ('mike', '4sxbgyacmjob', 3), ('mike', 'm4q13ln9gctj', 3),
('mike', 'pnip9c8cejo9', 3), ('steve', 'faff9p9v96x4', 4), ('steve', 'd5mxqpd3k8zi', 4),
('martha', 'bxggn5t6d2xn', 8), ('martha', 't05mi47i4n6l', 8), ('martha', 'p30wmw2o6nty', 8),
('martha', 'wip6efajt9yv', 8);

The expected output is:
+--------+------------+------+
| name   | col1       | col2 |
+--------+------------+------+
| john   | kl7y5tis4y |    1 |
| john   | tiid6iywq0 |    1 |
| john   | detawgsxz6 |    1 |
| amy    | oj98jjdpb5 |    2 |
| amy    | 87z5wgdfzt |    2 |
| amy    | wdpt0t5vtm |    2 |
| amy    | i6u4hhc1tr |    2 |
| bob    | k7nlpksc55 |    3 |
| bob    | mm76nhimm6 |    3 |
| bob    | 5qgz7dznzw |    3 |
| bob    | qv0s7ho3jk |    3 |
| bob    | 6t7mn60g0g |    3 |
| bob    | ku8wpf7d6t |    3 |
| steve  | wmisshywtb |    3 |
| steve  | 3qlylbzqfr |    3 |
| steve  | o04ksti7di |    3 |
| steve  | 0h22y66kq3 |    3 |
| steve  | hllaazonfl |    3 |
| mike   | 4sxbgyacmj |    3 |
| mike   | pnip9c8cej |    3 |
| mike   | m4q13ln9gc |    3 |
| mike   | hinn6w03wd |    3 |
| mike   | 2dsarwozpm |    3 |
| oliver | gvf9kjewpj |    4 |
| oliver | 24w5s30w5t |    4 |
| martha | bxggn5t6d2 |    8 |
| martha | t05mi47i4n |    8 |
| martha | p30wmw2o6n |    8 |
| martha | wip6efajt9 |    8 |
+--------+------------+------+

Amy, Bob and Oliver are only present in hi_priority table and will be selected.
Mike and Martha are only present in lo_priority table and will be selected.
John and Steve are present in both hi_priority table and lo_priority table but will be selected only from hi_priority table


texts: Is commit necessary after DML operation in Function/Procedure?

texts:
I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?

Example:

create or replace function test_fun
return number is
begin
   delete from a;
   return 0;
end;

or procedure

create or replace procedure aud_clear_pro
as
begin
   delete from a;
end;

does it need commit after delete?

Cannot understand the following situation:

If I call the function/procedure from SQL window then it requires commit

but
If I schedule  function/procedure using dbms_scheduler and run the job, delete statement is automatically committed.

WHY?


texts: Database behind a Multilingual User Interface

texts:
This question is about an issue somewhat more complicated than the one which has already been addressed in these old questions, all of which are duplicates of one another:

Suggestion for database structure for multilanguage (2011 Jun)

What's the best database structure to keep multilingual data? (2010 Feb)

What are best practices for multi-language database design? (2009 May)

Schema for a multilanguage database (2008 Nov)

The most popular database scheme for backing multilingual user interfaces seems to be having all the translated texts of all languages in one table with 3 columns: the text id, the language code, and the text itself. The text id and the language code together make up the primary key.

That's all very fine, but now consider a complication: suppose that the texts need to be searchable. Suppose, for example, that this is a multi-language e-shop. This means that for every product category entered into the database, the shop owner will enter the name of the product category in each and every one of the N supported languages, and then the shopper will be able to search for the product category by name, in their own language.

There is a problem: Collation.

Different languages have different collation sequences, and the collation sequence which works for one language does not work for another. So if all texts of all languages are on a single column, what collation sequence are they going to have? How are we going to query the database to find the text id of a specific text? While in a web product search accuracy and performance might not be awfully important, for the purposes of this discussion let us assume that they really matter.

Most database administrators are familiar with the concept of collation in the sense of "the collation of the database". Luckily, that's just the default collation, which is used if no other collation information is present, but there exist other places too, where collation can be specified:

The SQL CREATE INDEX command supports a collation specification. (Though rumors have it that Microsoft SQL Server does not support it; does anyone know about that?)
The SQL SELECT statement also supports collation, but in this case the collation specification works as a function, causing an index scan instead of an index lookup, something which might be impermissible if we want performance. (Then again, if that's the best we can have, it might be better than nothing.)
I also hear that on Microsoft SQL Server you can have non-persisted, computed columns on which you can specify collation and create a filtered index, though I have never heard of this before, and if it is a Microsoft-SQL-Server-only feature, then I'd rather refrain from using it, no matter how cool and well thought out it is.

So, in light of all that, how do we structure our database, and how do we perform our queries, if the goal is an updatable and searchable multilingual database?

This question was inspired by a discussion that took place here: how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?


texts:
I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL employees for that month?
Create Table #empSales
(
    employeename varchar(100)
    ,saleamt decimal(10,2)
    ,saleMonth varchar(100)
)

Insert Into #empSales VALUES
('James', '1.00', 'January')
,('Richard', '3.28', 'January')
,('Barb', '4.13', 'January')

Select
employeeName
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT (
    SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName
Order By employeeName


texts: How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite

texts:
Unlike some other RDBMS, SQLite does allow for bare columns in SELECT querys with GROUP BY, i.e. not all selected columns need to be part of the group by or aggregate functions otherwise. In this case, the first value in the result set will be selected for the grouped output row, regardless of whether this value is constant across all entries in the result set.

Something similar seems to happen in an UPDATE query with SELECT where the SELECT returns multiple rows, where only one row can be updated. In this case the first result from the SELECT is used for updating the target row.

I'm wondering if it's possible to force SQLite to be more strict in such cases and throw an error.

For illustration, consider this fiddle. Which will update both B properties with the first result from the SELECT. What I would like instead is that an error is generated.


texts: dynamically create trigger to audit row changes

texts:
I'm using this approach to create a trigger to catch data changes. However, the system allows schema changes, and I would like to create this trigger dynamically after a new table has been created. I was trying to separate the logic into a stored procedure and call it in the trigger body, but in the stored procedure context, there's no access to inserted / updated. Is there any better approach to achieve the same result?

CREATE PROC X
    @TableNameValue varchar(50)
AS

    declare @bit int ,
        @field int ,
        @maxfield int ,
        @char int ,
        @fieldname varchar(128) ,
        @TableName varchar(128) ,
        @PKCols varchar(1000) ,
        @sql varchar(2000),
        @UpdateDate varchar(21) ,
        @UserName varchar(128) ,
        @Type char(1) ,
        @PKFieldSelect varchar(1000),
        @PKValueSelect varchar(1000)

        select @TableName = @TableNameValue
        -- date and user
        select @UserName = system_user ,
        @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)
        -- Action
        if exists (select * from inserted)
        if exists (select * from deleted)
        select @Type = 'U'
            else
        select @Type = 'I'
            else
        select @Type = 'D'

        -- get list of columns
        select * into #ins from inserted
        select * into #del from deleted
        -- Get primary key columns for full outer join
        select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        -- Get primary key fields select for insert
        select @PKFieldSelect = coalesce(@PKFieldSelect+'+','') + '''' + COLUMN_NAME + ''''
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
        select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
        where  pk.TABLE_NAME = @TableName
        and CONSTRAINT_TYPE = 'PRIMARY KEY'
        and c.TABLE_NAME = pk.TABLE_NAME
        and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

        if @PKCols is null
        begin
            raiserror('no PK on table %s', 16, -1, @TableName)
            return
        end

AND FOR EVERY SCHEMA CHANGE I WOULD RUN THIS STATEMENT

PS: trigtest is the new table, I've hardcoded just to test it

create trigger trigtest_ChangeTracking on trigtest for insert, update, delete
as
    declare @TableNameValue varchar(50)
    set @TableNameValue = 'trigtest'

    exec X @TableNameValue


texts:
I keep getting an error of

  Conversion failed when converting date and/or time from character string.

when I run my query.  How should this be altered in order for the query to execute succesfully?

Declare @Table1 Table (name varchar(100), d1 date, amt float)
Insert Into @Table1 (name, d1, amt) Values
('Red', '01/01/2015', '14.00'), ('Red', '01/01/2016', '23.00'),
('Red', '01/03/2015', '22.00'), ('Red', '02/01/2015', '33.00'),
('Red', '03/04/2015', '10,00'), ('Red', '04/04/2016', '10.00'),
('Pink', '01/01/2015', '4.00'), ('Pink', '04/05/2016', '5.00')

DECLARE @ItemList nvarchar(500) = N'''Red''';
DECLARE @startdate date = N'''01/01/2015''';
DECLARE @enddate date = N'''12/31/2015''';

DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver,
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN ('+ REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN ('+ REPLACE(@startdate,'''', '''''') + N')
AND ('+ REPLACE(@enddate,'''', '''''') + N')
''
);
';

EXEC sp_executesql @sql;

EDIT
It is a postgresql database that I am querying, but created a table variable for simplicity and to show DDL


texts: How do I determine if a database is the principal in a mirroring setup?

texts:
I have two database servers Server1 and Server2, configured with mirroring.  A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on Server1 and is not mirrored. OtherDB has a stored procedure named SP_Z which refers to a table in MirrorDB to compute some value.

When Server1 is the principal for MirrorDB the SP_Z stored procedure in OtherDB works perfectly, however when MirrorDB fails over to Server2 the SP_Z in OtherDB fails as it can not open MirrorDB.

How do I solve this problem?


texts: MySQL: not quoting WHERE conditions slows query execution

texts:
I just ran into a curious issue with MySQL. I had a very slow query, even if properly indexed, which was like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = 10

As said both parm1 and parm2 had indexes. The query was taking from 0.2s up to 120s, depending on the searched values.

Then I quoted the search terms like this:

SELECT * FROM `tbl1` WHERE `parm1` = 'taleggio' AND `parm2` = '10'

and now the execution takes 0.02s! Why that?