texts: SQL Developer suggesting I add aggregate to group by clause

texts:
This isn't actually a bug, just an annoyance that I would like some explanation for. I have tried my Google-Fu, but I am lacking in skill today apparently.

I am using Oracle SQL Developer 4.1.5.21 Build MAIN-21.78
I used to use a really old version from 2010 (can't remember the version number), and it never made this suggestion, which is what has me stumped.

Essentially, I have the following query:

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg

It runs beautifully, no errors, and returns the expected data. HOWEVER, oracle developer tells me I'm stupid and wrong and should go die...okay maybe not that bad. But it does give me a query hint telling me I am wrong:

SELECT list inconsistent with GROUP BY;
amend GROUP BY clause to:
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
        , MIN(STDT."Application_Date")

Notice how it's telling me to add the aggregated MIN function to my GROUP BY clause, what gives? I have been writing SQL for years now and this is the first time I've ever been told to do such a thing...frankly, I'm ignoring Oracle for now, because it's being a jerk and can't tell me politely why. That being said, I would like an explanation from the community if anyone has one. :)

Thanks!

Edit 09/12/16

I just noticed that there is a pattern to this query suggestion. What I didn't mention above was that the above query was inside a CTE! Because...why the hell would that matter, but apparently it does.

Outside a CTE, Oracle SQL Developer doesn't care that the aggregate isn't in the group by clause (which it shouldn't regardless). However, as soon as the query is inside a CTE, BAM, it gets all hormonal and tells me it wants chocolate...I mean it tells me to add the aggregate to the group by clause.

Example (and it doesn't matter if I reference the CTE in the query):

WITH APPDT AS (
  SELECT /*+ materialize */
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer tells me to add the aggregate to the
        --GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg
)

  SELECT
          STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg AS "Semester"
        , MIN(STDT."Application_Date") AS APP_DATE--<=this aggregate
        --SQL Developer doesn't tell me to add the aggregate
        --to the GROUP BY clause below
  FROM INT_COMMENCING_APPS STDT
  WHERE 1=1
  AND STDT."Availability_Year" >= 2017
  GROUP BY STDT."Student_ID"
        , STDT."Study_Package_Code"
        , STDT."Availability_Year"
        , STDT.semester_agg


texts: SQL Server Replication causing locks on table

texts:
we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on.  Two which are located in the US, and 1 located in Asia.  Two of the 3 servers are busy servers and 1 is not.  The one which is not is the Distributor and Publisher.

What we are seeing is that the table which is part of the Replication finds itself being locked by the sp_MSupd_dbo stored procedure.  We notice this because other very simple select statements (just a lookup of the PK) are blocked during the execution time.  This stored procedure is doing a simple UPDATE statement.  This table has approx. 120,000 records in it.  When running the sp_MSupd_dbo stored procedure manually, it seems to run very fast without issue.  Additionally, the select statements which seem to get blocked are also very quick.  We are seeing some locks as much as 20-30 seconds long.

Also, the majority of the traffic are these quick select statements which do a lookup straight from the primary key.

Has anyone else seen anything like this before?


texts: Obtain column names of referenced objects

texts:
I used a script from here: https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I get the column name? so it finds table name from stored proc but can it also find the column name that is being used? thank you in advance

The query Referenced above is.

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
    database_id int,
    database_name sysname
);

-- ignore systems databases
INSERT INTO #databases(database_id, database_name)
SELECT database_id, name FROM sys.databases
WHERE database_id > 4;

DECLARE
    @database_id int,
    @database_name sysname,
    @sql varchar(max);

CREATE TABLE #dependencies(
    referencing_database varchar(max),
    referencing_schema varchar(max),
    referencing_object_name varchar(max),
    referenced_server varchar(max),
    referenced_database varchar(max),
    referenced_schema varchar(max),
    referenced_object_name varchar(max)
);

WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
    SELECT TOP 1 @database_id = database_id,
                 @database_name = database_name
    FROM #databases;

    SET @sql = 'INSERT INTO #dependencies select
        DB_NAME(' + convert(varchar,@database_id) + '),
        OBJECT_SCHEMA_NAME(referencing_id,'
            + convert(varchar,@database_id) +'),
        OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
        referenced_server_name,
        ISNULL(referenced_database_name, db_name('
             + convert(varchar,@database_id) + ')),
        referenced_schema_name,
        referenced_entity_name
    FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

    EXEC(@sql);

    DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;


texts: How to select/count rows where the same value exists in a column but not in other column all at the same table

texts:
I have this table structure and data:

CREATE TABLE IF NOT EXISTS `default_relations_users` (
  `id_user_rq` int(11) NOT NULL,
  `id_user_ap` int(11) NOT NULL,
  UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 2),
(2, 3);

I need to get only the rows where the value is on the left but not on the right, meaning for example: 1,2 and 2,1 will not be selected but 1,3 and 1,4 yes. I try this query:

SELECT * FROM default_relations_users
WHERE default_relations_users.id_user_rq = 1
 AND NOT EXISTS (SELECT id_user_ap FROM default_relations_users WHERE id_user_ap = 1)

Maybe I don't understand what Subqueries mean or I'm using in the wrong way. So any help?


texts: SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck?

texts:
I am looking for an indication (if any) that would either disregard the memory bottleneck option, or accept it, or make me investigate further.

for instance:

page life expectancy:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

ON this occasion, on my current environment,
I have a server that has 8 GB of RAM,
I would ask management for more memory. I believe this is a memory bottleneck.

what is using the RAM would be another investigation.

is this email below valid to identify possible memory bottlenecks?

  we need to look at SQLServer:Buffer Cache Hit ratio

  If that ratio is less than 95% than the server is under memory
  pressure

  Hope this helps,


texts:
I know a lot of Database Administrators and they are all over 28-29 years old.

Is all database administration like that? I mean, is this about getting experience more than at least 7-8 years?

Or is being a database administrator so hard?


texts: Database is not accessible as transaction log file growing to full

texts:
On one of our current prod servers, we have one of the data drives going out of space.[Somehow we have created 2 GB space for now]

Now we are planning to move one of the database in different drive which is almost 20 GB. But this database is not accessible state. I was thinking of backup/restore way to move, but it wont as it shows in unavailable state.

When I checked the SQL logs, it says transactional log of this database got full. So I checked its state using log_reuse_wait_desc which says "ACTIVE TRANSACTION".

How can I get the database available and ready for move so that I can move via backup/restore?

Additional Info: Database status shows "SUSPECT"
Please suggest.


texts: Why a SQL Server Table takes 1GB more after adding 55MB of data?

texts:
I've found that one customer database size exploded between yesterday and today.
After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data have been added to the table but the table size is now 1GB while the original size of the table was 70MB.

For additional information, 55MB of data was added by adding more than 2000 rows with a varchar(max) field filled with the data.

Here is the before/after analysis :

Yesterday

Today

Can you explain to me why a table took 70MB now takes 1GB after adding 55MB of data ?

There is not any index on the varchar(max)

The free space is very low, it is not related to the autogrowth. And the autogrowth is not configured with a percentage but with a fixed growth size.

Here is the creation script of the table :

USE [DuoClient20140003]
GO

/****** Object:  Table [dbo].[tContactHistory]    Script Date: 23/05/2016 10:53:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tContactHistory](
    [KeyCH] [int] IDENTITY(1,1) NOT NULL,
    [DateCH] [datetime] NULL,
    [KeyType] [int] NULL,
    [KeyF] [int] NULL,
    [KeyI] [int] NULL,
    [KeyCA] [int] NULL,
    [Content] [varchar](max) NULL,
    [KeyCom] [int] NULL,
    [KeyMH] [bigint] NULL,
    [KeyH] [bigint] NULL,
 CONSTRAINT [tContactHistory_PK] PRIMARY KEY CLUSTERED
(
    [KeyCH] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tAddrCarnet_tContactHistory_FK1] FOREIGN KEY([KeyCA])
REFERENCES [dbo].[tAddrCarnet] ([KeyCA])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tAddrCarnet_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tComments_tContactHistory_FK1] FOREIGN KEY([KeyCom])
REFERENCES [dbo].[tComments] ([KeyCom])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tComments_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tFoyers_tContactHistory_FK1] FOREIGN KEY([KeyF])
REFERENCES [dbo].[tFoyers] ([KeyF])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tFoyers_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tIndivs_tContactHistory_FK1] FOREIGN KEY([KeyI])
REFERENCES [dbo].[tIndivs] ([KeyI])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tIndivs_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tMailHistory_tContactHistory] FOREIGN KEY([KeyH])
REFERENCES [dbo].[tMailHistory] ([KeyH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tMailHistory_tContactHistory]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tModifsHistory_tContactHistory_FK1] FOREIGN KEY([KeyMH])
REFERENCES [dbo].[tModifsHistory] ([KeyMH])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tModifsHistory_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tTabShort_tContactHistory_FK1] FOREIGN KEY([KeyType])
REFERENCES [dbo].[tTabShort] ([KeyT])
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tTabShort_tContactHistory_FK1]
GO

ALTER TABLE [dbo].[tContactHistory]  WITH NOCHECK ADD  CONSTRAINT [tContactHistoryKeyI_Chk] CHECK  (([KeyI]>=(1)))
GO

ALTER TABLE [dbo].[tContactHistory] CHECK CONSTRAINT [tContactHistoryKeyI_Chk]
GO


texts:
In my recent studies about Auto Growth, I took a look at one of our DB's at work and see the Transaction log has many Auto Growth events. My understanding is that the Auto Growth event is a blocking event and should be avoided. So my goal is to stop this recurring Auto Growth. Is this happening because the reserved Log space is too small and the transactions fill it up immediately triggering the Auto Growth? Also, In this DB, they are taking TLog backups every hour on the hour.
Here is a pic from the Disk Usage Report.
Thanks!


texts:
I am in the progress of designing a database (or databases) for somebody and since I am a beginner I would love some advice.

The data is described as:

Customer1 (c1.type1, c1.type2, c1.type3)
Customer2
Customer3
Customer4 (c4.type1, c4.type2)
Customer5
Customer6
...
Customer10

There will be around 10 types of customers, perhaps less.

Not all the customers have different sub-types.  The type for customer1 for example, would be different than for other customers.

All of them would have general info included, for instance address, email, and phone number, along with other info.

Every customer generates an invoice. They pay different amounts since they buy different things.

We are predicting the tables may contain many millions of records.

Should I have multiple databases for each customer type?

Should I have one database with multiple tables for each customer type?

Should I have one database with one table for all customer types?

What about invoices?  Separate database?  Separate table?  One invoices table for all invoices?

As of this moment I am inclined to go with one database with multiple tables for each customer type.  The invoices would be in a separate table.

What do you think?


texts: GROUP BY gives wrong result with MIN() aggregate function


texts: How to transfer data from Database with latin1 character set, to database UTF-8. And on transferring convert data from latin1 to UTF-8 e.g., û->ū, ë->ė, þ->ž, á->į and etc.


texts:
we’ve made a downgrade from Enterprise Evaluation Edition to Standard and we saw the over night jobs all running much longer these are ETL packages and SQL Stored Procedures to load data from application server to DW server(which was downgraded from Enterprise to Standard). All other haven’t changed including configurations, network etc. It seems the performance took hard hit but we don’t know where to start with? If you could help with some thoughts and advice that would be much appreciated. Please note, the downgrade was performed by a third party vendor and steps are as suggested on MSSQLTIP website - https://www.mssqltips.com/sqlservertip/3079/downgrade-from-sql-server-enterprise-edition-to-standard-edition/

Thanks In Advance
Yang


texts: Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database?

texts:
Does frequent creation and deletion of physical table in database causes fragmentation? I am frequently creating and deleting database for loading data warehouse load. I am using these table to hold records temporarily  for batch updates.

This question is asked primarily from SQL Server 2016 and Azure Data Warehouse (MPP) point of view. But i would like to extend this question for any databases (if true)


texts: Cleaning up orphaned metadata in information_schema

texts:
OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so:

alter table mytable
add constraint fk_mytable_myothertable
foreign key (myothertable_id)
references myothertable(id)
on delete restrict
on update restrict;

Now, the constraint does not show in

show create table mytable;

nor in

show keys from mytable;

yet when I try to re-run the creation of the constraint, I get

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-115b_290'

--so, I searched through information_schema and found this:

mysql> select * from INNODB_SYS_FOREIGN where id like '%fk_mytable_myothertable%';
+------------------------------+------------------+------------------+--------+------+
| ID                           | FOR_NAME         | REF_NAME         | N_COLS | TYPE |
+------------------------------+------------------+------------------+--------+------+
| mydb/fk_mytable_myothertable | mydb/#sql-8a0_e6 | mydb/myothertable|      1 |    0 |
+------------------------------+------------------+------------------+--------+------+
1 row in set (0.00 sec)

mysql> select * from INNODB_SYS_FOREIGN_COLS where id like '%fk_mytable_myothertable%';
+------------------------------+-----------------+--------------+-----+
| ID                           | FOR_COL_NAME    | REF_COL_NAME | POS |
+------------------------------+-----------------+--------------+-----+
| mydb/fk_mytable_myothertable | myothertable_id | id           |   0 |
+------------------------------+-----------------+--------------+-----+
1 row in set (0.00 sec)

question is: can I just delete these orphaned references to the non-existent constraint, or is there more to cleaning up?


texts: How does the table scan in an UPDATE WHERE predicated on an unindexed column work?

texts:
I was looking into a deadlock caused by two concurrent UPDATE statements:

UPDATE [table]
SET [column] = 0
WHERE [unindexed_column] = @id

My understanding is that because the column the WHERE is predicated on is unindexed, a full table scan is performed. For each row, an update lock is acquired. If it matches the WHERE clause, it then upgrades to an exclusive lock and holds it until the statement completes.

The deadlock occurs when session A has an exclusive lock on row 2 and is trying to acquire an update lock on row 1, while session B has an exclusive lock on row 1 and is trying to acquire an update lock on row 2.

The reason for the deadlock makes sense, but I don't understand exactly how the table scan is performed that makes this scenario possible. If the two queries perform the scan in the same order, it seems like the worst case scenario is that one of the queries is blocked on acquiring an update lock until the other query completes and releases it's locks.

How is a table scan performed? Is the order the table rows are scanned in inconsistent? If the update statement fails to acquire an update lock, does it go to the next row and try the previous row again later? What, exactly, makes this deadlock possible?


texts:
I have varchar data in database table which stores date in DD-MM-YYYY format.

I want to select that varchar in MM-DD-YYYY format.

I'm using

select convert (varchar(30), (select CONVERT(datetime, '16-11-2011', 103)), 101)

It gives 11/16/2011

Any simple query is possible for this?


texts: How to obtain all distinct items but limit each distinct to max n and ordered by date?

texts:
I have a table where I have uuid as PK, command_name string, count_successful integer and command_start date.

I can grab all records by:

select
    uuid,
    command_name,
    count_successful,
    command_start
from
    sync_logs
order by
    command_start desc;

But above query will give me for example 78 records where command_name equals to foo and 45 records with command_name equals to bar.

How can I get max of 10 recent records per command_name?


texts: "ORA-01950: no privileges on tablespace 'SYSTEM' " : What error is it?

texts:
Earlier I created a user :

SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.

but when I login as suhail and try to create a table I get an error saying ORA-01950: no privileges on tablespace 'SYSTEM' .  What error is it ? Why does it occur ?

SQL> connect suhail
Enter password:
Connected.
SQL> create table suhail_table(name char(10));
create table suhail_table(name char(10))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0


texts: Do the action blocks of an ALTER TABLE statement run atomically?

texts:
In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma.
 For example:

ALTER TABLE mytable
  DROP CONSTRAINT pkey,
  ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;

Will the actions be executed atomically somehow?  Or does the statement need to be wrapped in a transaction to ensure all actions take effect in unison?


texts:
Is it possible to do this in a SQL database

 SELECT * FROM Employee
    WHERE BirthDate BETWEEN DATE 1970-01-01 AND DATE 1970-01-01

The result that was said to from this query is all the employees that are born on
1970-01-01.
This query has been taken from the following book
Sorry if this question is lacking.


texts: Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

texts:
I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output.  Some sample output of SHOW ENGINE INNODB STATUS from our DB:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-09-29 17:00:30 7f9eb7410700
*** (1) TRANSACTION:
TRANSACTION 2842247584, ACTIVE 0 sec, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 26 row lock(s)
MySQL thread id 69517881, OS thread handle 0x7f9ee07bd700, query id 11929163563 cfsender01.cf.intela.local 192.168.20.116 cfsender01 updating
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
...

Notice that there is no SQL statement listed in transaction (1).

Does anyone know why this might happen, or what the lack of a sql statement in the show engine innodb status would indicate about the transaction?

FWIW, transaction (1) was chosen as the victim by MySQL and rolled back.

Also, we're running MySQL Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618.


texts: How to represent bike components and their relationship to each other?

texts:
So I hope my question here isn't too vague.

I'm currently thinking about how to model a bike with all its parts and their relationships to each other within a database.

I haven't chosen a specific database as of yet. So it could SQL, document or graph database, or whatever else I'm not aware of.

So let's say I want to represent a bicycle. A bike is composed of various parts (or components) which all relate to each other and all have various properties.

For instance a frame can have a 135mm rear dropout, which means it will relate to a rear hub that is 135mm wide. The hub itself has 36 holes which means it can be used with a 36 holes rim, and so on and so forth. However a frame could be disc specific (it has disc mount but no rim brake mount) which means it can only take a disc hub. However a disc hub can be mounted on a frame that does not have disc mount but uses rim brakes.

I have already explore the idea of using a realtional database (postgres) with a json type and single table inheritence. As in a frame and a hub are both component sharing common properties (model, make, material, year...) stored in a component table as well as specific value (size, number of spoke holes...) store in a json field within the component table. But the problem is to represent the relationship, I could either match unique properties or use foreign keys for each rows.

So my question here is, is there a better way to do that?


texts: documents don't go into newly added shard

texts:
I have added a shard to an existing cluster, and when I do db.printShardingStatus() I see a distribution:

mongos> db.printShardingStatus()
--- Sharding Status ---
  sharding version: {
    "_id" : 1,
    "minCompatibleVersion" : 5,
    "currentVersion" : 6,
    "clusterId" : ObjectId("5c84149e75f51a74e9796add")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "127.0.0.1:27023",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "127.0.0.1:27024",  "state" : 1 }
        {  "_id" : "shard0002",  "host" : "localhost:27025",  "state" : 1 }
  active mongoses:
        "4.0.6" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  3
        Last reported error:  Error connecting to localhost:27025 (127.0.0.1:27025) :: caused by :: Connection refused
        Time of Reported error:  Sat Mar 09 2019 20:55:55 GMT+0000 (UTC)
        Migration Results for the last 24 hours:
                8 : Success
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
                config.system.sessions
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000   1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0000 Timestamp(1, 0)
        {  "_id" : "testdb",  "primary" : "shard0001",  "partitioned" : true,  "version" : {  "uuid" : UUID("68b7f8fe-17f6-4049-87ce-6c616c7746b7"),  "lastMod" : 1 } }
                testdb.testcollection
                        shard key: { "testkey" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000   3
                                shard0001   3
                                shard0002   1
                        { "testkey" : { "$minKey" : 1 } } -->> { "testkey" : "key0" } on : shard0002 Timestamp(9, 0)
                        { "testkey" : "key0" } -->> { "testkey" : "key20968" } on : shard0000 Timestamp(7, 1)
                        { "testkey" : "key20968" } -->> { "testkey" : "key4265" } on : shard0000 Timestamp(4, 0)
                        { "testkey" : "key4265" } -->> { "testkey" : "key53621" } on : shard0000 Timestamp(6, 0)
                        { "testkey" : "key53621" } -->> { "testkey" : "key64595" } on : shard0001 Timestamp(5, 1)
                        { "testkey" : "key64595" } -->> { "testkey" : "key8552" } on : shard0001 Timestamp(9, 1)
                        { "testkey" : "key8552" } -->> { "testkey" : { "$maxKey" : 1 } } on : shard0001 Timestamp(4, 1)

But when I access the newly added shard (shard0002), the collection is empty? Does anyone know why?


texts: Unable to drop database user without login

texts:
One of our developers with blanket privilege on a database wanted to create a regular user mapped to a login when what he mistakenly created was a user without login and now I'm unable to remove the user. DROP USER XX command runs forever with no result nor an error. The user doesn't own any schemas as well. How can I remove this user?
Thank you


texts:
In order for an application user (APP_USER) to use another schema (SCHEMA_OWNER) without the need for prefixing queries with the schema name, I have implemented a logon trigger that changes the session parameter CURRENT_USER.

This works fine except for sequences. When I do either of

select my_seq.nextval from dual;
insert into foo(bar) values(my_seq.nextval);

... I get the error

SQL Error [1031] [42000]: ORA-01031: insufficient privileges

Any ideas on why this may be and how to solve it?

Two possibilties comes to mind, maybe I need to

grant select privileges to SYS.DUAL of SCHEMA_OWNER? or
use the SESSION_USER instead of the deprecated CURRENT_USER parameter/variable?

Unfortunately I do not have admin rights on the database, else I would test it myself. I must work through a DBA, which means I have some difficulty in trying out different solutions and ask the question here in the hope to speed up the process.

Please note that workarounds like creating Oracle synonyms or views is not an option and likewise I cannot prefix all queries with the schema name since the application is a packaged product. Running as the schema owner is not an option either because of enterprise policy.


texts:
Having already read several questions on SO, external blog posts and manual

SO: Foreign Key constraint to partitioned table in Pg
dba.SE: Different ways of handling FK to partitioned table in Pg
Manual: Inheritance
Manual: Partitioning
Manual: Constraint triggers
Blog: Postgres modelling with inheritance

I still find myself wondering if I should go with partitioning considering my case or not.

The case - simplified

Storing customer data. All names of tables mentioned below are made up for clarity.

Having objects that are identifiable by customer and are non-physical beings, also their physical objects in which they are actually stored in case of needing to send some objects back to the customer on demand, or process it in some other ways. They are mapped in a many-to-many relationship. objects_nonphysical, objects_physical, objects_mapping_table.
Second many-to-many relationship is between those non-physical objects and their metrics. There are objects which are bound with some metrics. metrics, metrics_objects_nonphysical
Both non-physical and physical objects have their hierarchy tables which are child-parent relations. objects_nonphysical_hierarchy, objects_physical_hierarchy

Depending on each customers' needs and requirements the data about physical objects can be supplied or might need to be created from scratch. Basically, what I need to do is:

Maintain internal system for fast INSERT and SELECT statements, because here is where the mapping is going to take place.
Maintain the system for external customer to view and operate on their non-physical objects - fast retrieval of data. Strong need of efficiency for SELECT statements - this data is available for many customers to search whenever they want.

My consideration

There can be a customer, who may access the data, view and operate on it, but that doesn't need to be a contractor that we got the data from / are processing the data for.

This has lead me to introduce table partitioning into my system, considering that I always know which partition data should fall into (partitioning for contractors), and then to mainain system for external customer where I need partitioning for customers (this would be done with some delay using automation tools and set of rules to rewrite the data in customers manner, so that for each customer we'd only scan one partition for each table.

Data volume

My data is going to grow constantly, especially when importing new customers' objects and metrics. The pace of new data arriving into the system is unpredictable at the moment for the long run. There is really no way to measure it not knowing who is going to be the next customer. Right now there are just 2 customers with more or less 1M rows for every customer in every table. But in future I predict new customers to come with a volume of 10M rows or so as well.

Questions

These questions are all related to each other.

Should partitioning really be considered here, or is that an overkill? I consider it to be of use since I'm always scanning exactly one partition.
If partitioning is the way to go, how do I enforce FK constraint the most effectively considering my needs? Should I go for constraint triggers, or just keep it in the application layer for internal system, or maybe some other method?
If partitioning isn't the way to go, what should I dive into?

If there is not enough data supplied, please let me know in the comments below.


texts:
I am attempting a single column merge between two tables. The first table (VisitorSession) has 40,000,000 rows. The second (ShoppingCart) has 9,000,000 rows.

In my development environment, the query takes just under 8 minutes. But in the production environment, it should take significantly less (much more powerful machine). However, I anticipate the query taking at least 2 minutes to run in production. I know that this query was causing timeouts for other developers in the development environment, which means it could easily cause timeouts for customers. Is there a safer and/or faster way to perform this query?

declare @dt datetime = cast(dateadd(month, -6, getdate()) as date);

merge ShoppingCart as TargetTable  -- 07:55 to complete in Dev
using
(
  select * from -- 04:55 to run select, resulting in 12,727,927 rows in Dev
  (
    select
      visitorid  -- int, not null, foreign key
      ,useripaddress  -- varchar(55), null
      ,row_number() over
      (partition by visitorid order by createdate desc) as [row]
    from VisitorSession (nolock)
    where UserIPAddress is not null
    and CreateDate > @dt   -- createdate is a datetime, not null
  ) as subTbl
  where subTbl.[row] = 1
) as SourceTable
on (TargetTable.VisitorID = SourceTable.VisitorID)  -- visitorid is not a primary key
when matched
  then update set
  TargetTable.UserIpAddress = SourceTable.UserIpAddress;


texts: SQL Server backup fails on disk space error

texts:
SQL Server 2008 R2 RTM running on Windows 2008 64bit.

Having problems backing up a single database with the following command

backup database [somedatabase]
to disk = '\\somehost\d$\BACKUP\somedirectory\some_database.bak'
with compression, init, stats=1

The database backs up just fine for a while

93 percent processed.
94 percent processed.

Then I get an error about diskspace

  Msg 3202, Level 16, State 1, Line 1
  Write on "\somehost\d$\BACKUP\somedirectory\some_database.bak" failed: 1130 (Not enough server storage is available to process this command.)
  Msg 3013, Level 16, State 1, Line 1
  BACKUP DATABASE is terminating abnormally.

This doesn't make any sense because there is over 800GB of available disk space and the backup is using 121GB.

In SQL Server's logs I get this message

Date        9/25/2013 1:04:56 PM
Log     SQL Server (Current - 9/25/2013 12:15:00 PM)

Source      Backup

Message
BackupIoRequest::ReportIoError: write failure on backup device
'\\somehost\d$\BACKUP\somedirectory\some_database.bak'. Operating
system error 1130(Not enough server storage is available to process
this command.).

Some information:

Backups are being executed from the server locally and the backup files are being saved to \\somehost which is another server close by.
Both the local and remote hosts have sufficient disk space.
Compression is on

Any assistance on getting to real cause of these backup failures would be appreciated.

Thank you

Update 16:48

As of R2, Compression is a backup option on standard. Thank you RLF. Still having the same problem with or without. The only difference is that the backup fails at 18% without compression. Decided to monitor disk space to make sure nothing else was eating up GB during the backup.

As expected, available disk space went down to 600GB while using compression and 400GB without. Disk space never dipped below 400 though.


texts: Return proper column based on other column's values

texts:
Consider an EAV table with separate columns for each datatype:

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;

Now I'd like to store the username column for entity_id=5, which is a VARCHAR:

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');

How would one construct the SELECT statement to select from the proper column with a single query?

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Might I be best off simply COALESCEing the values and carefully managing the data in application code? Is there no real performance penalty to such a SELECT?

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Or perhaps duplicate the data into a redundant read VARCHAR column for reading?

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Note that I would like to use different datatypes for each value as in some cases I might need to index or perform datype-specific logic on certain columns, in those cases I'll know which datatype or I'll use a separate SELECT to get the column type.


texts: Can we create a table in specific datafile - Oracle 11g R2?

texts:
I am buying a new server than has two HDs:
300 GB with 15000 rpm.
600 GB with 10000 rpm.

I am thinking to allocate the datafiles in both hard disks. and put the less used tables in the data files that reside  in the slower HD that is 600 GB with 10000 rpm.

So, Can we create a table in specific data file - ?

I am using Oracle 11g R2 SOE .

Regards,


texts:
I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients.

One of the challenges I'm facing is that there appears to be some disperencies between order item pricing and order pricing such that the summation of all of the order items does not equal the pricing of the order.  This occurs because admins are aloud to go in after the fact and change an order price inorder to discount the customer.  However no record of the discount exists.

In this case would I want to create a fact table for the order and then a fact table for the order item?  If so I'm assuming I would need to create a key for the fact tables to maintain the relationships.

Is this an acceptable practice or will I run into problems with this type os solution when I go to build the cube.  Any thoughts on a better way to model this would be appreciated.

Thanks in advancce


texts: How to use OPENROWSET with MSOLEDBSQL driver?

texts:
MSOLEDBSQL driver is installed in my system. I am using the below syntax to connect to a database in a different SQL Server using OPENROWSET:

(in example I am trying to read from the same system)

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=True;Authentication=ActiveDirectoryIntegrated;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;

I am getting the below error:

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned
  message "Client unable to establish connection".

  OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned
  message "SSL Provider: The certificate chain was issued by an
  authority that is not trusted. ".

  Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source
  object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Any kind of help is appreciated.


texts: Passing passwords more securely to mysql utilities on the command line

texts:
This question is related to another question I subsequently asked here: Using the output from mysqldiff.exe to transform database2 to match database1.

I have been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. The idea is to identify schema differences between two databases, or other contained objects (tables, routines, triggers, etc).

Basically this command:

mysqldiff.exe --server1=root:password@localhost:3306
--server2=root:password@localhost:3306 db1:db2

seems to work, though I'm yet to fully grasp how to use this program's output for the purpose of transforming the one db to match the other! I guess I might have to ask that question differently further down the line.

My immediate issue is:

This utility screams: # WARNING: Using a password on the command line interface can be insecure., yet I am unable to find a means to avoid spilling out my passwords out there! There is no such option from the available help.

With regular MySQL binaries (mysqld.exe, mysql, mysqladmin, etc), I am able to use --login-path=xxxx, having configured the credentials using mysql_config_editor.exe. With Mysql Utilities, how can I solve this issue?

PS: I realize that my question's tags are a bit imprecise, but I stuff like "mysql-utilities" and "mysqldiff" are not available!


texts: Why pgAdmin4 invoke "'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte" error?

texts:
I'm newbie in postgreSQL ,when I'm trying to create a new local server using pgAdmin4 ,it shows me this error :

'utf8' codec can't decode byte 0xe9 in position 42: invalid continuation byte

I'm using windows 7 as my OS.

My steps to create a new server :


texts: Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL

texts:
By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work.

So I have a table called tableName, and I use it to make a new table called tableNameBuffer using the following command:

CREATE TABLE IF NOT EXISTS tableNameBuffer (LIKE tableName INCLUDING ALL)

TRUNCATE TABLE tableNameBuffer

In tableName, there is a column with a unique constraint, short_form, which also shows up in tableNameBuffer (this is good). However, when I try to mass insert data from tableName into tableNameBuffer`, I get the following error for a few select rows (that do have the same name):

  'ERROR: duplicate key value violates unique constraint "tableNameBuffer_name_idx"
  DETAIL: Key (name)=(data) already exists.'

The name column does not have a unique constraint in either table, and I checked using:

SELECT pg_get_indexdef(oid) FROM pg_class WHERE relname = 'tableNameBuffer_key'

which gives the following:

CREATE UNIQUE INDEX tableNameBuffer_key ON data.tableNameBuffer USING btree (short_form)

Just to clarify, there is no sequence or serial id (I don't think), and the problem only seems to be for the name column. All the other columns have duplicates (with the exception of short_form of course) but are inserting fine. I strongly believe the problem is not with the code, and since I am relatively new to SQL, maybe I'm overlooking something? If, however, I can't seem to find a solution, then maybe I'll look over the code extensively again.

Thanks in advance!

EDIT: I just found an index made by the following command if that affects anything?:

CREATE UNIQUE INDEX tableName_index on tableName USING btree (name COLLATE pg_catalog."default");

The insert query is built using the code so it's a little hard to understand without posting all my code (and it's a lot) but basically:

INSERT ="INSERT INTO %s (%s) VALUES("%tableNameBuffer,",".join(headers))

INSERT += "%(" + ")s,%(".join(headers) + ")s );"

cur.execute( INSERT, data )

Where headers are the keys, data is the data to be inserted, and cur is the cursor to the database.

There is only the above index and the following constraint in tableName:

CONSTRAINT tableName_key PRIMARY KEY (short_form)

And OIDS is false if that makes a difference, and I don't think there is table inheritance.


texts: Postgres returning timing of query within actual query?

texts:
Is it possible to return the time taken for a query in postgres?

Normally you would use \timing in psql, but is it possible to get this value from a query alone, i.e. that I would use in a prepared statement?


texts: SQL | How to sum in same table but referring to different cell values

texts:
As an example, I've a table like this:

CREATE TABLE Sample
    (`id` int, `productName` varchar(7), `description` varchar(55), `quantity`int(10))
;

INSERT INTO Sample
    (`id`, `productName`, `description`, `quantity`)
VALUES
    (1, 'Cheese', 'GT', 10),
    (1, 'Cheese', 'GE', 10),
    (1, 'Cheese', 'GE', 10),
    (2, 'Ham', 'VD', 10),
    (3, 'Wine', 'EX', 5)
;

and I'm trying to get a result so that the calculation is different if the description is of value GT and GE or VD. I tried with union all, but it does not seem to work:

SELECT x.productName, SUM(x.quantity)
  FROM ( SELECT productName, SUM(quantity*10)
        FROM Sample
        WHERE description IN ('GT', 'GE')
        UNION ALL
        SELECT productName, SUM(quantity*20)
        FROM Sample
        WHERE description IN ('VD')
       ) AS x

Is this possible?


texts: Variables for Merging MySQL InnoDB Tables

texts:
I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is

INSERT INTO `merged_table` (table_id, field1, field2)
SELECT 1 AS table_id, field1, field2 FROM `tableToMerge`

The merged table has a combined primary key of an auto incrementing id, and the table_id, whereby the table_id is being used for KEY() based partitioning. For this migration, there are no other indexes in the merged table.

Unfortunately, this takes very a long time and results in a table with over 7 million rows. I would like to know which MySql variables I should check/tweak to get the fastest performance possible. The variables I have been playing around with so far are:

read_buffer_size=1000M
tmp_table_size=1000M
max_heap_table_size=1000M
innodb_buffer_pool_size=4000M
innodb_flush_method=O_DIRECT

For testing, I am using a local "consumer grade" SSD that can perform over 1k transactions per second and 500MB/s write throughput, but during the merge it is only working at roughly 300-500 tps and a maximum write throughput of just 50MB/s. Also, the CPU in htop looks like it's barely putting any effort in.

The database is running in Virtualbox so I can restore from a snapshot and run a different scenario. If I need to allocate more memory, I can do so.


texts: Execution plan using B+ tree index, but also sorts

texts:
I'm using Oracle sqlplus. And I have the following query:

SELECT fooID from foo MINUS
SELECT fooID from bar;

I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo and one in the field fooID of the table bar. After that I analyze the statistics of both my tables: foo and bar and check the execution plan of my query using EXPLAIN PLAN .... But I get this:

  SELECT STATEMENT
      MINUS
        SORT UNIQUE
          INDEX FAST FULL SCAN FOO_INDEX
        SORT UNIQUE
          INDEX FAST FULL SCAN BAR_INDEX

How is that even possible? When doing INDEX FAST FULL SCAN, because the index is a B+ tree, doesn't the system gets back it's tuples sorted? Why does it need to do SORT UNIQUE (data already sorted)?


texts: Which table was updated by the last query?

texts:
When I perform an update query, MySQL server gives me information about how many rows were updated. But I wish to know in which table they were updated.
Is that information somewhere available?

The query is something like

UPDATE t1
JOIN t2 ON t1.a=t2.b
SET t1.x=1, t2.y=t1.z
WHERE ....

8 rows affected...

But in which table? t1 or t2?


texts: INDEX on a table where I'm only filtering on a row_number()

texts:
I've got the following table (scaled down to a MWE):
Fiddle here.

CREATE TABLE weekly
( yearnum int,
  weeknum int,
  location varchar2(5),
  weekly_count int,
  weekly_sum int);

INSERT INTO weekly values (2012,1,'X',5,10);
INSERT INTO weekly values (2012,2,'X',6,8);
INSERT INTO weekly values (2012,3,'X',7,14);
INSERT INTO weekly values (2012,4,'X',5,12);
INSERT INTO weekly values (2012,3,'Y',1,22);
INSERT INTO weekly values (2012,4,'Y',5,100);
INSERT INTO weekly values (2012,1,'Z',100,1022);
INSERT INTO weekly values (2012,2,'Z',120,1205);
INSERT INTO weekly values (2012,3,'Z',60,700);

I need the average of the last 26 entries by location, so I've got this:

SELECT
  t.location,
  sum(t.weekly_sum)/sum(t.weekly_count) AS avg_26
FROM
(
  SELECT
    w.location,
    w.weekly_sum,
    w.weekly_count,
    row_number() over (partition BY w.location
                        ORDER BY w.yearnum DESC,w.weeknum DESC) rn
  FROM weekly w
) t
WHERE t.rn <= 26
GROUP BY t.location;

My problem is that it appears this is always going to be a full table scan of weekly.  Is there any index or strategy I could do to improve this, since I can only filter it by the row_number() result?


texts: SQL Server 2016: Table can't grow while 313 MB is still available in the database

texts:
Yesterday, the below error was reported:
Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full
After deletion of some records from the table by an engineer, the error was cleared. I couldn't check the details yesterday as I actually didn't have admin access to it. My access was sorted later on. As I checked the DB size today, I observed the below:

There is 312.63 MB available free space, which means that there is 312.63 MB of space allocated to the database but not yet allocated to any page or objects (Please correct me if I'm wrong). I don't expect the delete operation of yesterday to have released any page/space. So why wasn't the database able to use this space which was readily available and allocated to the database?
I leave out the possibility that the file has grown any further since yesterday because there was ample disk space already available when the the incident occured. This is a SQL Server 2016 SP1 Express Edition which has the Autogrowth setting having been enabled with file growth size of 64 MB and the maximum size being set to Unlimited. Considering:
  10184 MB + 64 MB = 10248 MB > 10240 MB (= 10 GB = maximum allowed DB size in Express Edition)

It's obvious the file couldn't (and can't) grow any further.
While the database couldn't resize the file, it still could have used whatever space that was available to it. So why it didn't happen?
Could it be that, some objects have been dropped after delete was performed yesterday?


texts: I didn't get listener file get installed

texts: I have downloaded oracle sql developer to access Remote database. After downloading i couldn't connect to the database. I don't have tns.ora file installed in my system neither do i have oracle_home. Do I need to install oracle database too in order to have tns.ora file in my system.


texts: Does PostgreSQL Explain Analyze Execution Time consider Fetching time

texts:
I have some tests on GitHub which record how much time it takes to execute a query and fetch all results.

If I check the execution plan, I get:

Index Scan using post_comment_pkey on post_comment postcommen0_  (cost=0.29..2716.48 rows=34752 width=43) (actual time=6.767..14.352 rows=34880 loops=1)
  Filter: (post_id = 1)
  Rows Removed by Filter: 34880
Planning time: 0.659 ms
Execution time: 14.840 ms

So, the query is using an index and executing in 15 ms.

However, on the Java side, after the ResultSet is fetched and processed, the time measurement indicates that it took 90 ms.

The only explanation would be that the Explain Analyze doe snot consider the fetch time and fetching 34880 records takes 50-60 ms.

Is this correct?


texts: Deployed application (Oracle 11g, VB.NET 2015 and Crystal Reports) cannot connect to database

texts: I'm developing an application using Oracle 11g, VB.NET 2015 and Crystal Reports on Windows 10. I created a package and installed it on another PC on same network. The package is running fine but unable to connect to the database... Can you give advise/tips how I can deploy the application on client/server, my current network environment is Windows 10.


texts: SQL Server 2012 - Know when a SEQUENCE has recycled or is nearing cycle phase

texts:
In SQL Server 2012, is there a way to know if a Sequence has recycled?

Or some sort of trigger to know if a Sequence is nearing its cycling phase.

I need to update a table whenever a Sequencerecycles.


texts: Is there anyway to make this stored procedure thread safe?

texts:
I have a stored procedure that deletes X rows. This procedure is called by a Job Scheduler (Not SQL Server Agent) from one of three Azure Instances. The deletion task is run every 30 minutes from each instance and almost always results in a deadlock. Basically, multiple/simultaneous calls of this same stored procedure keep blocking eachother out.

Any suggestions on how to make this stored procedure threadsafe ?

CREATE PROCEDURE [dbo].[RawData_d_DeleteObsoleteRawData]
         @RemoteReadingMeasureIdList [IntListType] READONLY,
         @MaxSavingDurationInDays INT
AS
BEGIN

         DECLARE @CurrentDate SMALLDATETIME = CAST(GETUTCDATE() as SMALLDATETIME)
         DECLARE @MaxTime SMALLDATETIME
         DECLARE @TotalDeleted INT = 0

         -- Max time for raw data
         SET @MaxTime = DATEADD(DAY, -@MaxSavingDurationInDays, @CurrentDate)

         -- Delete non remote reading raw data
         DELETE TOP (10000) FROM [dbo].[RawData]
         WHERE MeasureId NOT IN (SELECT Item FROM @RemoteReadingMeasureIdList)
         AND DateEnergy < @MaxTime

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         -- Delete non remote reading raw data (keep 1 raw data per day for remote reading)
         ;WITH cte AS
         (
                 SELECT AreaId, MeasureId, DateEnergy,
                          ROW_NUMBER() OVER (PARTITION BY MeasureId, YEAR(DateEnergy), DAY(DateEnergy) ORDER BY DateEnergy DESC) AS rn
                 FROM [RawData] rd WITH (NOLOCK)
                 INNER JOIN @RemoteReadingMeasureIdList m ON rd.MeasureId = m.Item
                 WHERE DateEnergy < @MaxTime
         )
         DELETE TOP (10000) rd
         FROM [RawData] rd
         INNER JOIN cte ON rd.AreaId = cte.AreaId AND rd.DateEnergy = cte.DateEnergy AND rd.MeasureId = cte.MeasureId
         WHERE rn <> 1

         SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT

         SELECT @TotalDeleted as DeletedRow

Update 26/03/2014

Using the READPAST solution recommended by Jon was the best overall option.

To test I created two new queries, each with their own spid

Spid 51

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin transaction
Exec [RawData_d_DeleteObsoleteRawData]

Spid 52

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Exec [RawData_d_DeleteObsoleteRawData]

The first SPID does not commit and the second one is run immediately after.

Without the READPAST hint, all other executions of RawData_d_DeleteObsoleteRawData never complete.

With the READPAST hint added to the two DELETEs and the SELECT, all other executions of RawData_d_DeleteObsoleteRawData are able to continue.

Thank you again to wBob and Jon Seigel for their assistance


texts:
I am a developer reading the book "Querying Microsoft SQL Server 2012." I have come across the following diagram:

Why are the pages in the first two rows of the diagram (row 1 starting: 1:174 and row 2 starting: 1:41) not part of an extent? The documentation says: "all pages are stored in extents":
How does the number scheme work e.g. what does 1:174 mean?
Directly above the diagram, the book says: "IAM pages for an object are organized as a doubly linked list;"  The diagram does not show any links between the pages.


texts: How to grant Unmask permission to a user at table level and column level in sql server?

texts:
How to grant unmask permission to a user for one particular table and to one particular column within a table?

For example, if in table1 if column1 and column2 is masked and in table2 if column3 and column4 is masked, for user usr1 with select Access if i want to provide unmask only to table1 and only column1 how to do so?


texts: Storing DB credentials in an application source code?

texts:
I am making an app which connects to a DB, and the app will have plenty different userslogins.

Is it a good design choice to have a specific DB login/password for the application, using which the application will connect to the DB, and then verify userlogin/hashed+saltedpassword which are stored in a TABLE? (and for PostgreSQL to then SET ROLE to a user with necessary privileges for different TABLES/SCHEMAS)

My problem with this is I don't fancy the idea of having to store any credentials in the application, as if it's "reverse engineered" and the source code is found the hacker would know the credentials to access the database, and knowing the logic of the application he could then use "SET ROLE" himself and get access to any user he knows.

I just don't know how to access the DB without storing credentials in the application itself. I can make the user credentials = DB credentials, then no credentials would be stored but then the user could access the DB directly. So do we have to store the DB credentials in our source code?

Thanks!

Edit:

So it's like this:

User -> inputs his credentials into the app

App -> has login/password stored in the source code to access the DB, it connects to the DB and does

SELECT hash, salt FROM login_table WHERE userlogin = *given user login*

The app uses the given password and hashes it with the salt it found in the login_table, then it tries to match the just hashed password with the hash from login_table. If those match - the application does

SET ROLE *given user login*

(this is specific to PostgreSQL)  and then does all the other stuff using the established connection.

But while this seems great, the app needs to connect to the DB in the first place, so do I store it's DB login&password in the source code?


texts: SQL Server Numa config - Why is VAS reservered memory different on each numa mode?

texts:
I have a virtual machine running on hyper-V. The VM has 26 processors assigned to it and 300 GB of RAM.
The underlying physical server has 2 CPU sockets, both with 8 cores each.
Looking at the NUMA config in SQL Server I can see the following:

Node: 0 State: ONLINE Online schedulers: 13 Offline schedulers: 0
Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 688
Node: 1 State: ONLINE Online schedulers: 13 Offline schedulers: 0
Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0

Why is there a difference of Reserved VAS memory between the two numa modes?  Does it mean all the RAM is in the first processors DIMMs on the physical server, and so the second processor is actually having to access the RAM on the first?
Thanks.


texts: How to check the status of an SSAS cube while it is processing

texts:
When one processes an analysis services cube in Sql Server Management Studio, by right clicking on the cube name in the GUI and selecting the "process" option, a GUI pops up and displays the status of the cube partitions and amount of time that the cube processing has been running for each partition.

I am working on a system which remotely processes analysis services cubes. It does this by sending an XMLA script telling the server to process the cube to the ASCMD executable.

As far as I can tell the only output from ASCMD is just trace/warning/error gibberish, and does not output information that would be easily useful without some complex parsing.

Is there any way to check the processing status of a cube, while it is processing, to gather information such as which partitions are currently being processed and the time they started, etc...?

Using ascmd isn't a strict requirement, if there are other microsoft executable which are used behind the scenes to process cubes I can switch, it is just important I gather a semi-real-time status of the current cube that is processing.


texts: Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

texts:
Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following

CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TEMP TABLE bar ( a int REFERENCES foo );

I get,

  ERROR:  constraints on temporary tables may reference only temporary tables

I thought this used to work, so I went back to PostgreSQL 8.4 and I see even then it used to error,

  ERROR:  cannot reference permanent table from temporary table constraint

Why is this though? Why can't a temporary table reference a non-temporary table.


texts: SQL Server configuring both UCP and MDW on a single server

texts:
I'm trying to configure both UCP and MDW on a SQL Server.

I have already setup UCP and it created [sysutility_mdw] database.

Now when i try to setup MDW it is trying to use sysutility_mdw database created in #1

Should I use same database for both UCP and MDW or should I create separate ones?


texts: Alternative to FK for sys.objects reference

texts: I'm updating an audit/log table that is populated by trigger when a table is updated. My intention is to use the sys.objects.object_id for table name and column name columns in the log table but it's not possible to use a FK reference for the system views. Is there any useful alternative to maintain referential integrity on the table?


texts: Is non-dynamic scalar subquery evaluted for each row?

texts:
Is a scalar subquery that's independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless?

SELECT /* ... */
FROM
    t1
WHERE
    parent = (
        -- is this sq evaluated once?
        SELECT
            t2.id
        FROM t2
        WHERE t2.id = 10
    )

or using a CTE

WITH parent_id AS (
    SELECT
        t2.id
    FROM t2
    WHERE t2.id = 10
)
SELECT /* ... */
WHERE
    parent = (
        SELECT p.id FROM parent_id p -- is this sq evaluated once?
    )

If the answer is that the subquery/CTE is evaluated for each row, would joining the CTE on each row improve the performance?

WITH parent_id AS (
    SELECT
        t2.id
    FROM t2
    WHERE t2.id = 10
)
SELECT /* ... */
FROM
    t1, parent_id
WHERE
    parent = parent_id.id

Also, is there a way to figure out from EXPLAIN ANALYZE how many times an expression was evaluated?

Note: the examples are arbitrary and do not reflect real scenario, thus ignore the fact it doesn't make sense to do a subquery to find out an id which is already known (10).


texts: How do I identify all super users in SQL Server?

texts:
For a security audit,I need to identify all super users and log every activity performed. Best approach for this?

By "super user" I mean sysadmin, serveradmin, securityadmin, processadmin, and dbcreator.


texts: Backup & recovery strategies for Partitioned Database in Sybase ASE

texts:
I need to know about backup & recovery strategies for partitioned Database in Sybase ASE 15.7 . I am not worried about transaction failure & recovery scenarios.

We have already faced this 2 scenarios previously in un-partitioned database & it took more than 6 hrs for us to recover back [Reason : Low level sybase error]

In case of catastrophic failure like:

Data corruption (Some low level defect causes a table corruption that went unnoticed for days or weeks)
Failure of a device [A storage system gets corrupted]

Is my idea good to partition my database to achieve better resiliency?
In case I am doing so, is it possible to recover only failed partition? Sybase ASE maintains logical partitions, so is it possible to do piecemeal recovery?
In case my primary partition gets corrupted because of sybase low level error / disk corruption, will my replica also gets corrupted? [After all it's a replication :)]

In response to the initial answer by Catarrunas:

I didn't get point 2. Could you help me to explain that?

So in Sybase ASE, storage structure looks like (Partition --> Segment --> Device --> Extent --> Allocation Unit --> Page). Idea is to store 1 partition per segment. Of course again segment can span multiple devices.

Is it possible now to recover only failed partition held by 1 segment? Can we perform segment level recovery or still we would require to recover whole db?
So currently we don't have any replication. But it was more like due to some low level error, data got corrupted which went unnoticed for few days. In fact the last 2 dumps were also corrupted & we had to go -3th level dump to recover back.


texts: Capturing table changes with CDC and In Memory tables

texts:
We had a plan to capture table changes with CDC until we realized that it doesn't support In Memory tables.

Is there another way (preferably as straightforward as CDC) to capture data changes (from in memory tables) in SQL Server 2016?


texts:
I have a table (PostgreSQL 9.6) containing 2.6 million+ timestamped rows associated with account identifiers, and for any given identifier, I want to count the total number of occurrences as well as just the number of occurrences today in a single query.

For reference, this is the same table described in this question, but I have simplified it here to focus on this specific issue:

CREATE TABLE account_test
(
  id integer NOT NULL PRIMARY KEY
);

CREATE TABLE log_test
(
  account integer NOT NULL REFERENCES account_test(id),
  event_time timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX account_test_idx ON log_test USING btree (account,event_time);

INSERT INTO account_test VALUES (1);
INSERT INTO account_test VALUES (2);

INSERT INTO log_test VALUES (1,'2018-01-01');
INSERT INTO log_test VALUES (1,'2018-01-02');
INSERT INTO log_test VALUES (1,'2018-01-03');
INSERT INTO log_test VALUES (1,now());
INSERT INTO log_test VALUES (1,now());

INSERT INTO log_test VALUES (2,'2018-01-01');
INSERT INTO log_test VALUES (2,'2018-01-02');
INSERT INTO log_test VALUES (2,now());

This is my initial attempt, which is producing the same number for both the daily and total counts due to the GROUP BY:

    SELECT a.id,COUNT(d) AS daily,COUNT(t) AS total FROM account_test a
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id;

 id | daily | total
----+-------+-------
  1 |    10 |    10
(1 row)

The results I am looking for are:

 id | daily | total
----+-------+-------
  1 |     2 |     5
(1 row)

Specifically, the result of this ugly query:

SELECT qd.id,qd.daily,qt.total FROM
(
    SELECT a.id,COUNT(d) AS daily FROM account_test a
      JOIN log_test d ON a.id=d.account AND d.event_time > now() - interval '1 day'
     WHERE a.id=1 GROUP BY a.id
) qd,
(
    SELECT a.id,COUNT(t) AS total FROM account_test a
      JOIN log_test t ON a.id=t.account
     WHERE a.id=1 GROUP BY a.id
) qt;

I realize this may be a softball question, but in this case my SQL instincts are failing me, and I suspect there might be some clever trick that would eliminate the extra JOIN.


texts: MySQL permissions or privileges (User does not have access to metadata required)

texts:
I am using MySQL and I need to give one user to web service for access  database.

The web service need all permission related to stored procedure, like procedure execution.

I executed the following statements:

CREATE USER testservice@'%' IDENTIFIED BY 'test$123';
GRANT EXECUTE ON TestDB.* TO 'testservice'@'%';

but when run the web service , web service produces the following error:

User does not have access to metadata required to determine stored procedure
parameter types. If rights can not be granted, configure connection with
"noAccessToProcedureBodies=true" to have driver generate parameters that represent
INOUT strings irregardless of actual parameter types.

So, what do I need to do? Can any one help me?


texts:
MySQL 5.6.23, InnoDB

I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to place a NULL in the table. Some numeric types have this behavior, whereas others place a 0. I am using FIELDS TERMINATED BY and some columns do properly get NULL values, so it it not a fixed-row format issue.

These are the types I've observed while testing:

INT inserts NULLs
DECIMAL(x,0) inserts NULLs
DECIMAL(x,y) inserts 0.0s
FLOAT inserts 0s
DOUBLE(x,y) inserts 0.0s
DOUBLE inserts 0s

All of the columns in question are defined with DEFAULT NULL. I know that various functions could convert these 0s into NULLs. The question is whether there is a datatype that can handle decimal precision and will also insert NULLs on load.

Additionally, I see a whole bunch of questions relating to misunderstanding of the difference between a string, an empty string, and a null value. (example example example) This is not the issue, as the NULLs are there and are loaded properly into the same column when I redefine it as DECIMAL(x,0), then improperly when defined as DECIMAL(x,3).


texts:
Is there any way to find out the fragmentation of indexes in MySQL ?

If such an output can be obtained, it would be helpful to identify which all indexes need to be rebuilt.

I'm using MySQL 5.1 on Windows Server 2008

Can somebody please help me.


texts: sp_send_dbmail is populating 00 bits between all characters in a result set

texts:
I'm using sp_send_dbmail in a SQL agent job triggered nightly. The job queries our database and checks for product price updates, if there are some, it will send them in an email as an attachment to our e-commerce vendor. They have some automatic processes they will update our e-commerce platform, however their automated process cannot deal with the file provided by sp_send_dbmail.
sp_send_dbmail seems to be putting null characters between all characters in the result set.

See result of opening the csv in a hex editor:

Viewed through a text editor I see as expected:

sp_send_dbmail query here:

    SET @FileName = 'Update_' + CONVERT(VARCHAR(12),GETDATE),105) '.csv'
    SET @Query = 'Some Query'

    EXEC msdb.dbo.sp_send_dbmail
        @recipients = 'me@domain.com'
        @query = @Query
        @attach_query_result_as_file = 1
        @query_attachment_filename = @FileName
        @query_result_separator = ','
        @query_result_no_padding =  1
    END

What's going on here?

****EDIT FOR ADDITIONAL CLARIFICATION****

Using Microsoft SQL Server 2008 R2
Collation is Latin1_General_CI_AS

Leaving no_padding out leaves several trailing spaces in each returned field. each space (20) separated by a null (00)


texts: SQL Server Replication - FTP Snapshot: Policy requires SSL

texts:
SQL Server 2014 on both Publisher & Subscriber.
Publisher & Subscriber are across the internet.
Replication is Transactional Replication

Error messages:

  The process could not connect to FTP site '' using port 21. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL20032)

  Get help:
  http://help/MSSQL_REPL20032

  220 Microsoft FTP Service

  534 Policy requires SSL.

  (Source: MSSQL_REPL, Error number: MSSQL_REPL12003) Get
  help: http://help/MSSQL_REPL12003

Is there a way around this without relaxing SSL policy for the FTP Server ?


texts: Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications

texts:
We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts and where clauses.

So what we need is a way to translate a query like this:

SELECT Whatever FROM Person WHERE Name = 'John';

to this:

SELECT Whatever FROM Person WHERE Name = N'John';

Is there a way to implicitly force this on SQL server (2005 or higher, I guess if an upgrade is needed it would be acceptable).

Otherwise, does anyone know of a 3rd party proxy that would be able to translate the queries? Building one would be costly, but I think it is feasible. Or could we use some other trick like a trigger or something?

I have already checked if the 3rd party application can handle multi-byte characters in the strings that are sent and returned, that is not a problem at all. The only problem is that it's formatting the queries without using N's. (It should have been using parameters but we can't change it anyway).


texts:
Recently someone told me I should take a full backup before dbcc checkdb and one after checkdb, even in full recovery.
Can someone explain me why I should do this?

I thought a full backup before checkdb was unnecessary (in full recovery) because I can always restore last full backup + diff backup + t-log backups.
Is this a wrong assumption?


texts:
In our Datawarehouse context, we have to update some record in fact tables inside our ETL.

One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them afterwards. This lead us to much less time spent scanning tables in queries, and the time spent in building indexes has very low impact (1-2 minutes).

Is this a bad practice?

Note: We cannot partition tables right now.


texts: Is there a performance benefit to placing transaction log files on a separate drive?

texts:
There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the database file will be experiencing random reads and writes while the transaction log will only have sequential writes.

But what if you have hundreds of databases? Is there a true performance benefit to placing hundreds of transaction log files on a separate disk? If multiple transaction logs are being written to, then I would think the transaction log writes would be just as random as the database writes.


texts: Linked server error when querying one table

texts:
I am running 2 queries using the same linked server.

SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust1]
SELECT * FROM [SERVER].[Customer].[dbo].[ccr_AxCust2]

For Query 1 this returns results fine, however query 2 I get the following error:

  Msg 7399, Level 16, State 1, Line 2
  The OLE DB provider "SQLNCLI" for linked server "SERVER" reported an error. The provider did not give any information about the error.
  Msg 7330, Level 16, State 2, Line 2
  Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "SERVER".

The table is accessible on the actual server; has anyone seen this behavior before?


texts: Select column values linked to exact set of values in another column

texts:
Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example:

user_id IN (1, 3)    => chat_id 2
user_id IN (1, 2, 3) => chat_id 1
user_id IN (1, 2)    => no result

Is there a simple join to return the correct chat_id?

|-----------------------|
| chats_users           |
|-----------|-----------|
| chat_id 1 | user_id 1 |
| chat_id 1 | user_id 2 |
| chat_id 1 | user_id 3 |
| chat_id 2 | user_id 1 |
| chat_id 2 | user_id 3 |
| chat_id 3 | user_id 2 |
| chat_id 3 | user_id 3 |
|-----------------------|


texts: DB2 - Archive mode - large number of logs in logarchmeth1 folder

texts:
maybe for someone this is begginner's questions. I read lot of documentation and set:

`LOGPRIMARY 6
 LOGSECOND 4
LOGFILSIZ 4096
logarchmeth1 Disk - C:\archive
 logpath C:\active`

Number of logs in C:\active is 6 which is ok. But current number of logs in C:\archive is 40. How is that possible? I thought that maximum number of primary logs in C:\archive will be 6 and then 4 LOGSECOND (if needed, they are not preallocated).

Allowed log space is breached but new logs are created constantly in C:\archive. How is that possible? I will be thankful if someone can explain me this. On this way I do not see how can I get Transaction log full (except if I run out of total disk space) because new logs in C:\archive are created.

I am beginner so please sorry for simple question I did not find answer on Internetenter code here.

When those files will be deleted after the new online backup or when?
logarchmeth1 is the destination for archive logs I set them on 6 for PRIMARY how then I have 40 logs? Thank you


texts: Easier way to handle so many isnull() situation

texts:
Is there any good way to avoid writing isnull() function so many times inside a stored procedure?

I have a stored procedure that uses the isnull() function almost 30 times, I think that I am missing a concept, but until I find better way to get my data I'll love to clean my code from so many isnull() functions.

Can I for while set SQL Server 2008 R2 to using null values as float 0? Most of my isnull() is just adding zero 0 value if there is no data so I can do math operations.

EDIT: I am not lazy, I just trying to clean my code and avoid select parts looks like this

select
   vrsta.NAZIV
   ,isnull(sum(prod.prod_mpci),0) as prod_MPCI
   ,isnull(sum(isnull(mal_MPCI,0) + (vel_kol * isnull(mal_MPC,vel_VPC))),0) as lager_mpci
   ,isnull(sum(vel_NCI),0)+isnulL(sum(mal_NCI),0) as lager_nci
   ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as visak_MPC
   ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) > isnull(prod.prod_kol,0) and isnull(dzn.dzn,'2010-01-01') < @dzu
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as visak_MPC
    ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0)
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_MPC,vel_VPC)
                    else 0 end
            ) as manjak_MPC

    ,sum(   case
            when isnull(vel_KOL,0)+isnull(mal_KOL,0) <= isnull(prod.prod_kol,0)
                    then ((isnull(vel_KOL,0)+isnull(mal_KOL,0))-isnull(prod.prod_kol,0)) * isnull(mal_NC,vel_NC)
                    else 0 end
            ) as manjak_NC


texts: How to get space used by In-Memory table in SqlServer

texts:
I'm trying to understand how much storage In-Memory table consumes in SqlServer:

SELECT is_memory_optimized,durability_desc FROM sys.tables WHERE name='TBL_HSD_AD_OR'
select count(*) [#Rows] from TBL_HSD_AD_OR with(nolock)

Now when I'm using EXEC sp_spaceused I get:

EXEC sp_spaceused 'TBL_HSD_AD_OR'

Is there a different way to get the space used for Memory-Optimized tables?


texts: Will SQL charset wildcard query work on Oracle database?

texts:
I am trying to run the following query

SELECT * FROM GEEKYBRAINS.admin_emp where ename like '[sm]%';

It results in an empty set.

The admin_emp table has the following data though.

I am running this on Oracle database. Will the charset wildcard search work with Oracle database ?


texts: How can I prevent so many aborted clients in MySQL (increasing aborted_clients count)?

texts:
I am running MySQL 5.6.14 on an Ubuntu server and noticed the error log constantly filling with:

  Aborted connection 17201 to db: 'somedb' user: 'sdb' host: 'x.x.x.x' (Got timeout reading communication packets)

(These happen about 10 times per minute. The counter is up to 13,000 after a server uptime of only 24 hours.)

We have perhaps twenty or thirty instances of a Windows client program that uses the .NET framework via Connector/NET (6.2.2.0) to connect to the database and I've made sure that every time it creates a connection (MySqlConnection.Open()) it is also properly closed (MySqlConnection.Close()).

I've adjusted various timeouts, max_allowed_packet, and other settings based on research on the web, but these still seem to persist.

Here are (what I believe are) the relevant settings:

connect_timeout = 20
wait_timeout = 60
net_read_timeout = 60
net_write_timeout = 60
max_allowed_packet = 33554432 // 32M

What can I do to stop these events from constantly happening?


texts: Is there an Euler diagram of SQL Server 'sys' tables?

texts:
I am trying to wrap my head around which tables to include in queries on the sys tables because it sounds like some objects might appear in multiple places according to the summary descriptions that can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-ver15
e.g. tables should exist (in some form) in sys.columns, sys.objects, sys.tables


texts: How can I replace multiple correlated subqueries in the SELECT clause by LEFT JOINs?

texts:
A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=e387589d446d9c9a952294f8c7a98494.

I have simple table layout:

class
person: belongs to a class
room:   belongs to a class

The following query selects all classes with its persons embedded:

select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons
from      class
order by  class.identifier;

While experimenting and learning more about correlated subqueries, I noticed that the query above could be rewritten by replacing the correlated subquery with a LEFT JOIN combined with a GROUP BY:

select     class.identifier, array_agg(person.identifier) as persons
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
group by   class.identifier
order by   class.identifier;

Please note that I made the assumption that each class has at least one person. If not, I could add coalesce() around json_agg.

In my second case, I am going to select all classes with its persons and its rooms embedded. Let's first write in the same manner as the first query above:

select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons,
                            array(select room.identifier from room where room.class_identifier = class.identifier) as rooms
from      class
order by  class.identifier;

This gives the expected results.

Now I want to repeat what I did before: introduce LEFT JOINs. My first attempt was as following:

select     class.identifier, array_agg(person.identifier) as persons
                           , array_agg(room.identifier) as rooms
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
left join  (
             select  room.identifier, room.class_identifier
             from    room
           ) as room
on         class.identifier = room.class_identifier
group by   class.identifier
order by   class.identifier;

Now I am getting wrong results. Persons or rooms are being repeated in the output arrays. I understand why this happens (we are grouping on the cartesian product between class, person and room, hence every person is repeated for every room and vice versa), but I don't know how to proceed.

How can I continue here? Is replacing multiple correlated subqueries in a SELECT clause to LEFT JOIN + GROUP BY possible to start with, or need I other tricks?


texts: Cannot restore data using Postgres WAL files

texts:
I'm a newbie at PostgreSQL.
I'm trying to setup a streaming replication (postgresql 9.1).
And I want to test backup & restore on local.
I also made backup files successfully (I think so) by using pg_basebackup
But cannot restore data.
Here is how I set my database up.

Postgres on server1 (master)
Configure file: /var/lib/pgsql/9.1/data/postgresql.conf
 # WRITE AHEAD LOG
 wal_level = hot_standby

 # - Archiving -
 archive_mode = on
 archive_command = 'cp %p /var/lib/pgsql/9.1/wal-archive/%f'

 # - Master Server -
 max_wal_senders = 5
 wal_keep_segments = 32

 # - Standby Servers -
 hot_standby = on

Start master and insert some data

Postgres on server2 (slave)
Configure file: /var/lib/pgsql/9.1/data/postgresql.conf (content is same as master)

Create recovery.conf file on slave
 // vi /var/lib/pgsql/9.1/data/recovery.conf
 standby_mode = 'on'
 primary_conninfo = 'host={master_hostname} port=5432 user=repl_user password={my_password}'

Start slave => and data is automatically synced with master

Create WAL backup
 // slave
 service postgresql-9.1 stop
 rm -rf /var/lib/pgsql/9.1/data/*
 su - postgres
 pg_basebackup -h {master_hostname} -p 5432 -U repl_user -D /var/lib/pgsql/9.1/data --xlog --checkpoint=spread --progress

WAL files is generated in /var/lib/pgsql/9.1/wal-archive on master after pg_basebackup is executed in slave
 // something like this
 000000070000000000000013
 000000070000000000000014
 000000070000000000000015
 000000070000000000000015.00000020.backup

Drop one table (master)
 DROP TABLE {table_name}

Restore that dropped table using WAL files (master)
 service postgresql-9.1 stop
 vi /var/lib/pgsql/9.1/data/recovery.conf

 // add this line to recovery.conf
 restore_command = 'cp /var/lib/pgsql/9.1/wal-archive/%f %p'

Start master again
 service postgresql-9.1 start

But that dropped table is not restored on master. I don't know why.
I also tried to add some other option, but everything is not worked.
recovery_target_time = '2020-10-29 08:20:00 UTC'
recovery_target_inclusive = false

Please help on how to restore data using WAL files.
If I accidentally drop a table on master, how can I restore it using WAL files? Which configuration should I setup on recovery.conf file? Is just write restore_command in recovery.conf file and start postgres again make server restore the lastest backup point data?


texts: Designing a data model for a survey; building the relationships

texts:
Let's say I have a survey that will be handed out to many people. I have a hypothetical diagram with User, Question, and Response entities. Should my relationship be one-to-one from question to answer? My thinking is that each question can only have one response. I could argue, however, that since this is a survey with many users, each question can have multiple Responses.

How do I approach this situation, and where is my confusion stemming from?


texts: Shrinking the log file does not reduce size

texts:
I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.

When I try to shrink the log file, it's not shrinking.

I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file.

When I ran

DBCC SQLPerf(logspace)

I found that the log size is 4932 MB and Log space used is 98.76%!

Then I tried this command

USE <databasename>;
DBCC loginfo;

Now almost all VLFs are "status 2" which means all are in use.

I tried to take a log backup and then shrink the log file. Shrinking didn't reduce the size.

I changed the recovery model to SIMPLE and tried shrinking again, but this also didn't help.

I checked for open transactions

DBCC opentran (database);

and found that no transaction is open now.

What is stopping me from shrinking the log file? How can I solve this?


texts:
I ran across a partition that was initially setup (sql 2012) beginning of 2014 to break by month but wasn't updated for 2015. The main issue is that the last partition was setup as Jan 2015 so Feb data is loading into it and I'd like to add future months to the partition and break out the Feb data out of the Jan 2015 partition.   My first thought was to load the data to another table and truncate the existing table then drop and recreate the partition as necessary but the table has over a billion records so storage and time are a consideration.  What other options would have I have separate the Feb data from the Jan 2015 partition as well as add future months with the least impact to the system?

Here's the existing partitioning function being used.

CREATE PARTITION FUNCTION [YearMonthPartFunction](date)
    AS RANGE RIGHT
    FOR VALUES (N'2014-01-01T00:00:00.000',
                N'2014-02-01T00:00:00.000',
                N'2014-03-01T00:00:00.000',
                N'2014-04-01T00:00:00.000',
                N'2014-05-01T00:00:00.000',
                N'2014-06-01T00:00:00.000',
                N'2014-07-01T00:00:00.000',
                N'2014-08-01T00:00:00.000',
                N'2014-09-01T00:00:00.000',
                N'2014-10-01T00:00:00.000',
                N'2014-11-01T00:00:00.000',
                N'2014-12-01T00:00:00.000',
                N'2015-01-01T00:00:00.000')


texts:
I’m making a little site which may, if I’m lucky, hold a little of data with regards banking transactions. This is version 2 of a previous learning project.

I will have a table with transactions. Based on the type (payment, deposit or transfer) the value will be negative or positive.

Best cases scenario, I have 10,000 users and they each have 1500 transactions per year and 8 years of data. 120,000,000. One primary bit of info stored and shown a lot is Current Balance. But also, the running total shown on multiple different types of statement and transaction screens.

So, should I break the rules and store the running total or transaction on every row, and recalculate all transactions that an insert/update may effect? I mean, that’s usually just the last weeks transactions.

Or store a separate DailyBalance table, with a date and account number as the pk with that days transactions added?

Or simply calculate on the fly?

Any advice on structure for performance as data integrity would be handy.

I’m using SQL Server 2016.
I’d have a pk on the transactionid.
And an index on accountId
As well as one on transactionDate probably.

Maybe clustered on Id, accountId?


texts: Is there a way to have mysql automatically create partitions as needed?

texts: We have a large table with columns, date, tag, country. We would like to partition the table by YEAR(date), tag, country, but would like to have mysql create the partitions on the fly. i.e. We don't want to set the number of partitions up front when the partitions are setup and then have to remember to add new partitions in the future.


texts: Does PostgreSQL take time to start streaming replication after restart master?

texts:
I set streaming replication in postgresql 9.3. I have one master and 3 slaves and replication works fine.

But when i restart the master server and login using postgres database, pg_stat_replication does not show replication details immediately. It takes several seconds ( around 5 seconds ) show replication details .

I want to know why this happens ? whether it's problem with pg_stat_replication ?

I want to make sure replication start as soon as server starts .


texts: Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE'

texts:
I have an application which is getting data from Oracle(11.2.0.4) Materialized view, there is expected behaviour about materialized view will go INVALID when
any DML action happens on the associated tables or assocaited objects are invalid.

My materialized view opotions are as listed below and confirming that associated objects are valid all the time.

CREATE materialized VIEW mv_emp_test1
NOLOGGING COMPRESS build IMMEDIATE
REFRESH FORCE
START WITH (SYSDATE) NEXT (SYSDATE+5/1024)
as
......

Materialized view go into INVALID state when it freshes after every 5 minutes and become valid if any of the following method applied:

alter materialized view mv_emp_test1 recompile;
or
exec dbms_mview.refresh('mv_emp_test1');

Also, checked related documents and bugs, Doc ID 264036.1 in metalink has included the exact scenario but didn't find proper workaround so that auto refresh in every 5 minutes.


texts:
I have installed postgresql 10 on centos 7.4 and installed postgis2.5 few months back. The installation was successful and Iam able to use postgis. All of a sudden Iam getting the following error.

test=# create extension postgis ;
ERROR:  could not load library "/usr/pgsql-10/lib/postgis-2.5.so": /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

And ldd on postgis-2.5.so is as follows

lib]# ldd postgis-2.5.so
    linux-vdso.so.1 =>  (0x00007ffcc03ef000)
    libgeos_c.so.1 => /usr/geos36/lib64/libgeos_c.so.1 (0x00007f7a7d766000)
    libproj.so.12 => /usr/proj49/lib/libproj.so.12 (0x00007f7a7d4fd000)
    libjson-c.so.2 => /lib64/libjson-c.so.2 (0x00007f7a7d2f2000)
    libxml2.so.2 => /lib64/libxml2.so.2 (0x00007f7a7cf88000)
    libm.so.6 => /lib64/libm.so.6 (0x00007f7a7cc86000)
    libSFCGAL.so.1 => /lib64/libSFCGAL.so.1 (0x00007f7a7c1b7000)
    libc.so.6 => /lib64/libc.so.6 (0x00007f7a7bdea000)
    libgeos-3.6.3.so => /usr/geos36/lib64/libgeos-3.6.3.so (0x00007f7a7ba3c000)
    libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f7a7b735000)
    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7a7b51f000)
    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7a7b303000)
    libdl.so.2 => /lib64/libdl.so.2 (0x00007f7a7b0ff000)
    libz.so.1 => /lib64/libz.so.1 (0x00007f7a7aee9000)
    liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f7a7acc3000)
    /lib64/ld-linux-x86-64.so.2 (0x00007f7a7dc56000)
    libCGAL.so.11 => /usr/lib64/libCGAL.so.11 (0x00007f7a7aa9b000)
    libCGAL_Core.so.11 => /usr/lib64/libCGAL_Core.so.11 (0x00007f7a7a862000)
    libmpfr.so.4 => /usr/lib64/libmpfr.so.4 (0x00007f7a7a607000)
    libgmp.so.10 => /usr/lib64/libgmp.so.10 (0x00007f7a7a38f000)
    libboost_date_time-mt.so.1.53.0 => /usr/lib64/libboost_date_time-mt.so.1.53.0 (0x00007f7a7a17e000)
    libboost_thread-mt.so.1.53.0 => /usr/lib64/libboost_thread-mt.so.1.53.0 (0x00007f7a79f67000)
    libboost_system-mt.so.1.53.0 => /usr/lib64/libboost_system-mt.so.1.53.0 (0x00007f7a79d63000)
    libboost_serialization-mt.so.1.53.0 => /usr/lib64/libboost_serialization-mt.so.1.53.0 (0x00007f7a79af7000)
    librt.so.1 => /usr/lib64/librt.so.1 (0x00007f7a798ef000)

The function GEOSFrechetDistanceDensify seems to be added in version 3.7 of Geos as in link below & I have geos 3.6. Is this the reason for this error?.
https://trac.osgeo.org/geos/browser/git/NEWS?rev=3.7.0

Please help me to get the issue resolved.


texts:
I've got the following query:

SELECT
            j.id,
             concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
            c.email AS client_email,
            concat(bc.company,' ', bc.name_first, ' ', bc.name_last) AS bclient,
            j.title,
            j.refnum,
            j.job_number,
            j.time_job,
            j.time_arrival,
            j.address,
            j.suburb,
            j.city,
            j.stpr,
            j.postcode,
            j.priority,
            j.status_label_id,
            j.description,
            u.name_first AS staff_first,
            u.name_last AS staff_last,
            fl.serialised_data,
            fl.gtime,
            j.created_date
        FROM
            public.ja_status AS s
        JOIN
            public.ja_jobs AS j
        ON
            j.status_label_id = s.id
            AND NOT j.deleted
            AND j.templated = false
             AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))))
        JOIN
            public.ja_customers AS c
        ON
            c.id = j.customerid
        LEFT JOIN
            public.ja_customers AS bc
        ON
            bc.id = j.bill_customer
        JOIN
            public.ja_feedlog AS fl
        ON
            fl.jobid = j.id
            AND fl.clientid = 19233
            AND fl.log_type IN (5,101,6,102)
            AND (
                fl.log_type IN (5,101)
                OR (
                    fl.description LIKE '%status_change%'
                    AND fl.log_type IN (6,102)
                )
            )

        LEFT JOIN
            public.ja_mobiusers AS u
        ON
            u.id = fl.requestorid
        ORDER BY
            j.id, gtime
        LIMIT
            50000

Explain analyze: http://explain.depesz.com/s/25Wj

How can I improve it? The total time is absolutely huge!

It seems the major problem is on ja_feedlog table:

                                  ->  Bitmap Heap Scan on "ja_feedlog" "fl"  (cost=230474.49..397722.58 rows=33256 width=952)

  (actual time=307.690..290532.037 rows=323605 loops=1)

Table ja_feedlog:

CREATE TABLE public.ja_feedlog
(
  id integer NOT NULL DEFAULT "nextval"('"ja_feedlog_id_seq"'::"regclass"),
  clientid bigint,
  mobiuserid bigint,
  customerid bigint,
  invoiceid bigint,
  description character varying(1024),
  gtime bigint,
  jobid bigint,
  log_type smallint,
  serialised_data "text",
  push_status smallint DEFAULT 0,
  requestorid bigint,
  the_geom "geometry",
  admin_read smallint NOT NULL DEFAULT 0,
  visitid bigint,
  CONSTRAINT pk_feedlog PRIMARY KEY ("id")
)


texts: Oracle Workspace Manager: How is DDL applied to versioned tables?

texts:
We're considering the use of Oracle Workspaces for development/test environments. While its benefits are pretty clear concerning DML and data in general, my Google-Fu obviously wasn't sufficient finding answers to some DDL related questions. I know about the beginDDL() and commitDDL() wrappers to be used with DDL on version-enabled tables, and that a *_LTS table is used in background for this. Still, several questions remained1:

Is DDL applied on the "workspace version" only, or "escalated" up to BASE/LIVE automatically, or is that depending on X?

We need to know this for development processes, e.g. whether we could try new implementations requiring table alterations on "isolated level" that way (using a workspace without affecting other processes on the same table using a different workspace).

While one might argue we could simply "try and find out", my concern is there might be different behavior as well. For example, I have a clear idea how it could work altering a column from VARCHAR2(20) to VARCHAR2(50) only for the current workspace (so this might work, simply by "view definition" in the workspaces using substr()) -- but have my doubts on how add/drop column might be reflected (yes, by view definition -- but what if adding a NOT NULL column without DEFAULT, which theoretically were possible if the table is empty in all workspaces?) A simple try-and-err might miss things, so I'd prefer to know in advance (feel encouraged to attest your arguments with links to authoritative material).

1: yes, I know -- one question per post, so this is but the top of my ice-berg for now