texts: SQL over partition -avoid repeated statement in for every column

texts:
I have the follwoing SQL statement. Is there a way to avoid writing the OVER(PARTIION... statement multiple times for each line as it is identical in every line?

            SELECT
            DISTINCT
            LAST_VALUE (DSET_COB_DT)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_DSET_COB_DT,
            LAST_VALUE (SRC_HUB)              OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB,
            LAST_VALUE (TRD_SRC_SYS)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_SRC_SYS,
            LAST_VALUE (TRD_ID)               OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_ID,
            LAST_VALUE (NVL (TRD_VER,0))      OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_VER,
            LAST_VALUE (SRC_HUB_INGESTION_DT) OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB_INGESTION_DT,

            FROM DF_RISK_SIT2_OWNER.recon_ts_rs

Any suggestions would be appreciated to make the statement easier to read.


texts: Trying to write a function that doesn't require dbo. prefix

texts:
I am trying to write a simple system function so that the dbo. prefix is NOT required to access the function. I have found an example of this for SQL Server 2000 but when I run

EXEC SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
go

create function system_function_schema.trim
    (@input varchar(max))
    returns  varchar(max)
as
begin
    declare @result varchar(max)

    select  @result = rtrim(ltrim(@input))

    return  @result
end
go

EXEC SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE

This is the error thrown.

  The specified schema name "system_function_schema" either does not exist or you
  do not have permission to use it. Severity 16

The expected usage would be

select trim('   padded value   ')

Does anyone know the approach I should be taking or if I HAVE to create it as a regular user defined function that must be called with the dbo. like :

select dbo.trim('   padded value   ')


texts:
I'm new to MySQL.

I've found myself in the position of having two different versions running on one host.

I'm a little unsure of how these are working together.  Do I issue >service mysqlservice 3307 start?

Where does mysql3307 come into play?


texts:
I have old db with myisam tables, using mariadb and want try move to xtradb for row-level locking. Each table have 27 columns, primary index and other 11 indexes. Columns with indexes have many selects with where, group and order. I make some test and I'm little confused.

Table with about 6M rows - disk space (data + keys):
myisam - 915MiB
innodb (xtradb) - 2011MiB

On innodb(xtradb) I try large select with many where, group and order on indexed columns and it takes 63s, but if I delete all other indexes - only primary stay, then it takes 21s.

It looks like if I don't use transaction, foreign keys, joins, etc...on this tables, that primary key is fine and all others is useless. But I think this is very strange :-/

I don't have hardware for better test, are you guys think it's good idea to convert all tables to inno(xtra) and drop all indexes except primary in production environment?


texts:
Is there a blog / webpage / information (I did not find anything) out there, if it has performance benefits, if I use a newer Windows Server version for my SQL Server?

Example: I run SQL 2016 on a Windows Server 2012. Does it make sense to update this server to Windows Server 2016 or the upcoming Windows Server 2019 beside those out-of-support lifecycle topics? Does it make a difference, if I install a new SQL Server 2019 (if it is out) on Windows Server 2016 or Windows Server 2019?

PS: The only point I found to this topic was that - if I want to use NVDIMMs I should use Windows Server 2016 for better support.


texts: MySQL: "FROM_UNIXTIME(0)" gives "1970-01-01 01:00:00", which is 1 hour off

texts:
Context: MySQL version

On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";)

Context: MySQL timezone settings

and with SELECT @@global.time_zone, @@session.time_zone; yielding

+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

...which means the server tracks the system timezone.

Context: System timezone

This being a Linux, we check the system timezone like this (output redacted):

$ timedatectl
      Local time: Wed 2018-02-14 13:21:30 GMT
  Universal time: Wed 2018-02-14 13:21:30 UTC
        RTC time: Wed 2018-02-14 13:21:30
       Time zone: Europe/London (GMT, +0000)

Context: What we know

Time and date values are not stored with timezone information in MySQL (there is no clear description of this in The DATE, DATETIME, and TIMESTAMP Types, except "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)"; note that "converts TIMESTAMP values to UTC for storage" actually makes sense only as an implementor's note, it's not useful to the user - what is important is that TiMEZONE always returns the correct value in whatever timezone one is in, while DATETIME will always return the same value in whatever timezone one is in)

Context: Current time

It's now around :

$ date +"The current time is %s alias %c"
The current time is 1518617572 alias Wed 14 Feb 2018 14:12:52 GMT

Problem

Let's run:

SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());

The output (suitably pivoted by hand; there should be an option to do that):

NOW()                            2018-02-14 14:15:13  -- Display "now" in SYSTEM timetone: OK
FROM_UNIXTIME(0)                 1970-01-01 01:00:00  -- Display 1970-01-01 00:00:00 in SYSTEM timetone: NOT OK
UNIX_TIMESTAMP()                 1518617713           -- Correct unixtime
FROM_UNIXTIME(UNIX_TIMESTAMP())  2018-02-14 14:15:13  -- Same as NOW(): OK

In the above everything is fine, except the FROM_UNIXTIME(0) giving 1970-01-01 01:00:00, which is one hour off. If Unix Time 0 were displayed in UTC, it would give 1970-01-01 00:00:00 (or would it?)

This happens if I connect locally or from a remote machine.

To make doubly sure, I checked by coercing to DATETIME. The same happens:

CREATE TEMPORARY TABLE test (N DATETIME, FUX DATETIME, UXT LONG, NN DATETIME);
INSERT INTO test SELECT NOW(),FROM_UNIXTIME(0),UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT * FROM test;
+---------------------+---------------------+------------+---------------------+
| N                   | FUX                 | UXT        | NN                  |
+---------------------+---------------------+------------+---------------------+
| 2018-02-14 14:19:51 | 1970-01-01 01:00:00 | 1518617991 | 2018-02-14 14:19:51 |
+---------------------+---------------------+------------+---------------------+


texts: Visual Studio 2013 and up - Database Project Schema Compare File

texts: Is there any reason to save the schema compare file once you are done comparing, creating scripts or just simply updating?


texts: How to get the value of column that the same for few rows by selecting values from other columns?

texts:
Since I am not a MySQL expert, I am having trouble determining how to ask a search engine for advice on this topic.

I have the following table:

+------------+--------------+-------------------+
| product_id | attribute_id | text              |
+------------+--------------+-------------------+
|       1770 |           57 | wi-fi             |
|       1770 |           58 | bluetooth         |
+------------+--------------+-------------------+

There are many other products with many attributes.

I need to select product_id that has text = wi-fi and text = bluetooth (in different rows). The table name is product_attribute.

I have tried the following:

SELECT *
FROM product_attribute
WHERE product_attribute.text = 'wi-fi'
    AND product_attribute.text = 'bluetooth';

However no results are returned since in one row we have text = wi-fi and another row has text = bluetooth, whereas my query is looking for both wi-fi and bluetooth in the same row.

How can I get product_id = 1770 by making the query select from the text column in different rows with the same product_id?

Here is SQL Fiddle.


texts: sp_Blitz error: "File '' either does not exist.."

texts:
SQL Server 2008 v10.0.5538.0
sp_Blitz v5.5

I'm trying to run sp_Blitz for the first time and getting this error message:

sp_Blitz @CheckUserDatabaseObjects = 0

  Warning: Null value is eliminated by an aggregate or other SET operation.
  Msg 567, Level 16, State 5, Procedure sp_Blitz, Line 3863 [Batch Start Line 0]
  File '' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

What may be causing the error?


texts:
I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4.

When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH MATERIALIZED VIEW CONCURRENTLY view the trigger fires after each statement (insert,update,delete)

What happens is, to do a insert it will take around 57 seconds.

How can I fix this and still use a materialized view?

I had this implemented for a long time, and never had this problem, recently I've added pgbouncer, can it be the cause of this?


texts:
I am a newbie to Db's on a whole, and I came across a tutorial, wherein we needed to store ~700 Tb of data over a few years. It was mentioned that scaling SQL for such limits of data is not the best approach, and hence in the tutorial they went ahead with NoSQL. I have a few doubts regarding this:

Is it possible to store such Tb's in a single machine, as per my understanding, horizontal scaling is not possible in SQL. Please correct this if I am wrong.
What will be the latency comparison of SQL vs NoSQL. Because of horizontal scaling, will a noSQL db like DynamoDB give lower latency than a SQL Db.


texts:
I'm fairly new with database desings (only worked on a couple of projects) and i kinda need some info on how to contruct the following.

I need to keep stats for an archery league and the players.
Players info i had no trouble figuring it out. But the match and the stats confuses me.

League consists of matches.
Each match consists of 2 rounds
Each round consists of 10 shots.
Each shot consists of 3 arrows (where we get a score).

How can i sort the stats for each player?
I was hoping you could help me. Number of tables i'll need at least.

Thank you in advance


texts: What is the best way to create Primary Key on a large table in Postgresql 9.5?

texts: I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock the table because this is highly transactional database.


texts: Patching SSRS on Always On Availability Groups

texts:
I have SSRS 2016 installed on Always On Availability Groups.  I have patched my secondary servers with SQL Server patches.  When I failover SSRS will not connect to the databases, it has an error

  The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '175'. The expected version is '173'.

How can I get this new server to connect?  What is the proper way to upgrade SSRS with 2016 AG's?


texts: SQL Server changes execution plan - Part 2

texts:
Previous question: SQL Server changes execution plan
We are using SQL Server 2014 Developer Edition.
SQL Server changes execution plan on identical query and the same database and SQL Server (I checked several times).
If I connect with Management Studio from my dev computer with AD account, it takes 18 seconds to finish the query (most of the time). If I go with remote connection to server and execute query in Management Studio it takes 2 seconds to finish. On my colleague's machine it takes 2 second with Management Studio and 18 if he connects with MVC application (Ado.Net).
Things we did

Restart SQL Server
DBCC FREEPROCCACHE
sp_updatestats
try with different users AD and SQL users

Slow execution plan

Fast execution plan

Query
SET ANSI_NULLS ON
GO

SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @ccode varchar(500)
DECLARE @PageSize int
DECLARE @PageNumber int
SET @ccode = '%skd%'
SET @PageSize = 20
SET @PageNumber = 1

SELECT DISTINCT CLASSIFICATIONS.ABBREV_TEXT,
            CLASSIFICATIONS.TITLE_TEXT,
            CLASSIFICATIONS.CFN_UID
    FROM klasje.dbo.CLASSIFICATIONS
    inner join Klasje.dbo.CFN_VERSIONS on CLASSIFICATIONS.CFN_UID = CFN_VERSIONS.CFN_UID
    inner join Klasje.dbo.VERSION_CATEGORY_XREFS on CFN_VERSIONS.CVN_UID = VERSION_CATEGORY_XREFS.CVN_UID
    left outer join Klasje.dbo.VELJAVNOST on CFN_VERSIONS.LIFE_CYCLE_CODE = VELJAVNOST.RV_LOW_VALUE
    WHERE CATEGORY_CODE like @ccode OR DESCRIPTOR_TEXT like @ccode OR DEFINITION_TEXT like @ccode
    ORDER BY ABBREV_TEXT
    OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY

Both execution plans on gist.
SELECT @@OPTIONS returns 5496 in in both cases (slow and fast).


texts:
I have a weekly SQL database backup job that is failing. If I look at the end of the history of the job, I see the following messages:

  Executing query "USE [msdb]  ".: 2% complete  End Progress  Progress:
  2015-06-14 21:27:49.47     Source: Rebuild Index Task      ;

  Executing query "ALTER INDEX [DTA_progress_index] ON
  [dbo].[DTA_pro...".: 4% complete  End Progress  Progress: 2015-06-14
  21:27:49.47     Source: Rebuild Index Task      ;

  Executing query "USE [msdb]  ".: 6% complete  End Progress  Progress:
  2015-06-14 21:27:49.82     Source: Rebuild Index Task      ;

  Executing query "ALTER INDEX [DTA_reports_column_index] ON
  [dbo].[D...".: 8% complete  End Progress  Progress: 2015-06-14
  21:27:49.82     Source: Rebuild Index Task      ;

  Executing query "USE [msdb]  ".: 10% complete  End Progress
  Progress: 2015-06-14 21:27:50.38     Source: Rebuild Index Task      ;

  Executing query "ALTER INDEX [PK__DTA_repo__1AA1422F61A66D40] ON
  [d...".: 12% complete  End Progress  Progress: 2015-06-14 21:27:50.38
  Source: Rebuild Index Task      ;

  Executing query "USE [msdb]  ".: 14% complete  End Progress
  Progress: 2015-06-14 21:27:50.46     Source: Rebuild Index Task      ;

  Executing query "ALTER INDEX [DTA_reports_database_index] ON [db...
  The package execution fa...  The step failed.

I understand that the DTA indexes are created by the database tuning advisor, so I believe that the job is attempting to alter an index that no longer exists. The question is, how can I change the job so that it now longer attempts to do this?


texts: Accessing a network share via a SQL Server 2012 Availability Group

texts:
If you have a SQL Server Cluster called CLS, with machines CLS1 and CLS2, accessing:

\\CLS\network\path

is equivalent to accessing either

\\CLS1\network\path

or

\\CLS2\network\path

depending on whether CLS1 or CLS2 is the currently active instance.

The same behavior isn't occurring on a SQL Server 2012 Availability Group I've set up to try out AGs. I have three machines called PRI, SEC1 and SEC2 in an availability group called AG. I can access the databases via AG, and can remote desktop AG, but cannot access network shared via AG.

Are you supposed to be able to, by default, access networks shares via the AG?
If not, can this behavior be enabled?

Edit: I am primarily interested in the administrative shares (c$, d$ etc.)


texts:
I have configured PgBouncer with PostgreSQL 9.6 on windows 7 machine. Here are the pgbouncer configuration settings;

[databases]

test = host=127.0.0.1 port=5432 dbname=master_db user=postgres password=test123

[pgbouncer]

logfile = C:\Program Files (x86)\PgBouncer\log\pgbouncer.log
pidfile = C:\Program Files (x86)\PgBouncer\log\pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = C:\Program Files (x86)\PgBouncer\etc\userlist.txt
admin_users = postgres
stats_users = postgres
max_client_conn = 100
default_pool_size = 20

user list file

"test_user" "bacha"

When i try to connect to a database using pgbouncer, through this command;

psql -h 127.0.0.1 -p 6432 -U test_user master_db

i get this error

psql: ERROR:  client_login_timeout (server down)
psql: ERROR:  pgbouncer cannot connect to server

How can i solve this Problem ?


texts:
I have a table with the following structure:
ItemID           int
ItemType         char(1)
Language         char(2)
Localization     char(2)
Literal          nvarchar(4000)

which is used to translate the text of specific ItemID to Literal one. The Literal column may contain HTML tags. For a particular set of queries, these HTML tags needs to be removed and because the sanitization is done with SQL CLR over thousands of rows, I prefer not to perform such on read.
So, I have added a persisted column like this:
ALTER TABLE [dbo].[table]
ADD [LiteralSanitized] AS NULLIF(CAST(LTRIM(RTRIM([dbo].[fn_Utils_RemoveAllHtmlTags] ([Literal]))) AS NVARCHAR(4000)), '') PERSISTED;

The table has only one index (the primary key) with the following definition:
ItemID, ItemType, Language, Localization

So the query was faster, but I am seeing some additional reads for this table:
Scan count    - 2     vs 12,230
Logical reads - 3,234 vs 43,472

which might be normal as now I reading more data because of the column. So, I have added the following index:
(ItemID ASC, ItemType ASC, Language ASC, Localization ASC) INCLUDE ([LiteralSanitized])

but it is not used by the engine. So, I try to force the engine to use it:
UPDATE #temp
SET [QuestionText] = PSGQ.[LiteralSanitized]
FROM #temp PQD
INNER JOIN [dbo].[table_with_translations] PSGQ WITH(INDEX = [the_new_index])
    ON PQD.[ProtoQuestionID] = PSGQ.[ItemID]
WHERE PSGQ.[ItemType] = 'Q'
    AND PSGQ.[Language] = @language
    AND RTRIM(PSGQ.[Localization]) = ''
    AND PSGQ.[LiteralSanitized] IS NOT NULL;

but the engine is doing the following:

performs index scan (on my new index)
then performs nested loop and key lookup with the clustered index
extract the literal column

If my column is Persisted, why the engine continues to try to return the Literal column as it is even not needed?


texts:
I got the results as below after querying the multiple tables in our database (oracle database 12c enterprise edition).

but we would like to see the table as:

there are more than 100 rows in the table and also when I write the PIVOT command in the query window, it doesn't highlighted with any colour as other commands do. so please help me out of this scenario.


texts: Disable/enable PostgreSQL trigger inside function for the same table

texts:
I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update column on some other table. I just disable that protecting trigger before update and enable it again after the update inside function that do all the work. In this case, it doesn't work, since I need to disable the trigger for the same table.

  ERROR:  cannot ALTER TABLE "Table1" because it is being used by active queries in this session

Is there another way to do it? Is there way to disable all updates to that column except from within this function?

It's PostgreSQL 9.4.


texts: Script to automate enabling TDE on any unencypted user databases

texts:
I'm looking for a way to automate the encryption of user databases.  Ideally this would be a job that can be scheduled to run every night.  The job would: find any user database that is not encrypted, create the DEK (if it doesn't exist), and enable TDE.  We use the same Certificate/PK for each database on every server (for better or worse).  This certificate has been backed up.

I attempted this by creating the following stored procedure.  However, it occasionally fails with the following error:

  Executed as user: TAC_NT********. Msg 33107, Level 16, State 1, Server *******, Line 2
  Cannot enable database encryption because it is already enabled.  Msg 5069, Level 16, State 1, Server *********, Line 2
  ALTER DATABASE statement failed.  Encypting Database: *********  Warning: The certificate used for encrypting the database encryption key has not been backed up.
  You should immediately back up the certificate and the private key associated with the certificate.
  If the certificate ever becomes unavailable or if you must restore or attach the database on another server,
  you must have backups of both the certificate and the private key or you will not be able to open the database.
  Encypting Database: *********.  Process Exit Code 1.  The step failed

The databases do get encrypted, but the job reports failure.

Here's the SP:

CREATE PROC [dbo].[sp_EncryptDBs] AS
BEGIN
    SET ANSI_WARNINGS OFF;
    SET XACT_ABORT, NOCOUNT ON;
/*  Get Databases that are online and have no DEK   */
    SELECT
        db_name(D.database_id) [Database]
    INTO #Databases
    FROM
        sys.databases D
    WHERE
        NOT EXISTS
            (
                SELECT *
                FROM
                    sys.dm_database_encryption_keys DEK
                WHERE
                    DEK.database_id = D.database_id
            )
        AND db_name(D.database_id) NOT IN
            (
                'Master',
                'model',
                'msdb'
            )
        AND D.state_desc = 'Online'
    WHILE (SELECT COUNT(*) FROM #Databases) > 0
    BEGIN
        DECLARE @Database NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases)
        DECLARE @Command1 NVARCHAR(MAX) = N'
            USE [' + @Database + ']
            CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
            ENCRYPTION BY SERVER CERTIFICATE ********
            '
        DECLARE @Command2 NVARCHAR(MAX) = N'
            ALTER DATABASE [' + @Database + ']
            SET ENCRYPTION ON
        '
        PRINT 'Encypting Database: ' + @Database;
        EXEC sp_executesql @Command1 ;
        EXEC sp_executesql @Command2 ;
        --PRINT @Command1
        --PRINT @Command2
        DELETE FROM #Databases WHERE [Database] = @Database;
    END
    DROP TABLE #Databases;

/*  Databases that have a DEK but are in an unencypted state    */
    SELECT db_name(database_id) [Database]
    INTO #Databases2
    FROM
        sys.dm_database_encryption_keys K
    WHERE
        K.encryption_state <> 3
    WHILE (SELECT COUNT(*) FROM #Databases2) > 0
        BEGIN
            DECLARE @Database2 NVARCHAR(50) = (SELECT TOP 1 [Database] FROM #Databases2)
            DECLARE @Command3 NVARCHAR(MAX) = N'
                ALTER DATABASE [' + @Database2 + ']
                SET ENCRYPTION ON
            '
            PRINT 'Encypting Database: ' + @Database2;
            --PRINT @Command3
            EXEC sp_executesql @Command3;
            DELETE FROM #Databases2 WHERE [Database] = @Database2
        END
        DROP TABLE #Databases2
END
GO


texts:
Is it possible in SQL Server to set collation on individual rows in a table (as opposed to by column, database or server)?

If not, perhaps does anyone know if this is a planned feature for SQL Server 2016?

The only problem this solves is curiosity between two colleagues.


texts:
My question is on the answer given at Difference between Sharding And Replication on MongoDB

I need to split thedata of 75GB into 3 shards of 25GB each with replication factor of 3. Answer depicts below picture

                            Sharded Cluster
             /                    |                    \
      Shard A                  Shard B                  Shard C
        / \                      / \                      / \
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+
|Primary| |Secondary|    |Primary| |Secondary|    |Primary| |Secondary|
|  25GB |=| 25GB    |    | 25 GB |=| 25 GB   |    | 25GB  |=| 25GB    |
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+

and says we need at least 6 database servers organized in three replica-sets. Each replica-set consists of two servers who have the same 25GB of data.

My understanding is that each shard is a physical machine holding below two sets of data(primary and secondary) and host two separate mongod server/instance . One will hold primary data and second will hold secondary data which is back up of primary data on another shard

Primary data :- This is 1/3 of the 75GB data distributed over some partition key
Secondary :- This will hold secondary data which is back up of primary data on another shard

Application query will go only to primary data on a given shard based on partition key. It is possible that on a shard a secondary gets converted to primary if another shard primary goes down .  Is my understanding correct here ?

No of instances of Mongos and Arbiter

Also i believe i need to start only 1 mongos instance(which will be separate from mongod instance) ? Similarly I need to have single mongo arbiter for all 3 shards instead of 3 on each shard ? Both mongos and aribiter can be on separate machine or share the hardware of one of the shard depending on requirement ?


texts:
I am struggling with my statement in PostgreSQL. I have 3 tables:

tbl - flags (labeled as a)
a.email

tbl - accounts (labeled as b)
b.id
b.email

tbl - foo (labeled as c)
c.account_id
c.total

My sql statement looks like:

select a.email, sum(c.total)
from flags a, accounts b, foo c
where a.email = b.email and b.id = c.account_id

This statement works but there could be some accounts who does not have an account or total amount either. Well, this statement does not make sense, because there are several a.emails in flags a, which don’t have any c.total record. And also there are several b.emails in accounts b, who doesn’t have any record in c.total.

My desired output:
email - sum(i.total)

foo@foo.com      5666
kjkj@kjaksd.com  882
hello@world.com  23122
mac@toast.com    0 -- mac@toast.com doesn’t have account or any total

So is it possible to check if a record exist in accounts compare to flags? And if so, let’s prompt total amount and if not, let’s prompt 0.


texts: Clarification on mysql deadlock situation

texts:
Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that

1) Transaction (2) holds exclusive lock for PERMISSION_ID index.

2) Transaction (1) comes and try to acquire insert intention exclusive lock for PERMISSION_ID. But put on to wait since exclusive lock is already with (2)

3) Then transaction (2) also wants to acquire insert intention exclusive lock for PERMISSION_ID which place it behind (1) in waiting queue. Thus deadlock.

What I am not sure about my explanation is assumption in step (2) where request to insert intention exclusive lock for PERMISSION_ID waits for exclusive lock to be released. Is it correct or am I missing something here. Please explain.

*** (1) TRANSACTION:
TRANSACTION 22466201, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 10 lock struct(s), heap size 1184, 16 row lock(s), undo log entries 2
MySQL thread id 12287, OS thread handle 0x2b4ea1f61700, query id 130397444 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'))

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466201 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 22466203, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1184, 14 row lock(s), undo log entries 1
MySQL thread id 12279, OS thread handle 0x2b4ea1700700, query id 130397446 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4187, 'anonymous', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='SYSTEM'))

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)


texts: how to using group concat distinct count in MySQL 5.7

texts:
Now I am using MySQL 5.7, I have some data like this:
user_id

63763
63763
NULL
16459,16535,16613,16615,42896,42911,62463,62464,63594,63763,63764,63765,63766,63769,63930,63934,63935
NULL
NULL

I want to concat the user_id and distinct , finnally count the result of distinct result(finally I find the result still have duplicate value), is it possible to complete it in one sql? I known distinct like this:
select group_concat(DISTINCT user_id SEPARATOR ',')
from report_summary
where statistic_time >= 1615824000000

how to count the result?

and now I found the result still have duplicate value using this sql:
select group_concat(DISTINCT bet_user_ids SEPARATOR ',')
from report_summary
where statistic_time >= 1616083200000
and statistic_time <= 1616169599999

the result is:
16459,16535,16613,16615,18627,42890,42896,46521,62463,62464,63591,63595,63763,63764,63765,63769,63934,63939,63940,42890,42896,42890,42896,62463,63763

the user 42890 have three times in the result.


texts:
I have a query that fetches a number of aggregations over a table. It's very similar to the following adapted example from the Postgres documentation, just with more entries in the grouping sets clause:

SELECT
    brand,
    size,
    count(*)
FROM items_sold
GROUP BY GROUPING SETS (
    brand,
    size,
    ()
);

If there is a WHERE clause that restricts this query to a small subset of the table, it is pretty obvious how to speed that up with an index. But what options do I have for cases where I run this kind of query against the whole table, or a large enough fraction that an index on the WHERE clause condition isn't useful?

Now, I personally can't really think of a way of speeding this kind of query up, in the end it has to scan the entire table to perform these aggregations. But just because I can't think of any way doesn't mean there isn't one. From what I read, dedicated search engines like Elasticsearch or Solr can perform similar aggregations over very large amounts of data, and I assume they're reasonably fast at this.

What options do I have to speed up this kind of query that uses a GROUP BY GROUPING SET over an entire table?


texts: Management Studio won't recognize other file extensions

texts:
I am using SSMS 2012. I am working with .PRC, .TAB, and .UDF files. I added these file extensions to SSMS so that it would treat them like .SQL files already via Tools > Options > Text Editor > File Extension, but recently SSMS has stopped recognizing these files (e.g. won't show them with color coding or allow me to run them against a database). Funnily enough when I remove a file extension from a file it then recognizes them because it has "Map extensionless files to SQL Query Editor" checked.

Does anyone know what the problem might be?


texts: select count(*) in mysql 5.5 innodb-- rewrite advice?

texts:
I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...

select count(*)
from mails3
  join questions using (question_id)
where mails3.from_user_id = '86696'
  and mails3.is_community_star = 1
  and mails3.rating = 3
  and questions.is_rated_community = 1;

the query execution plan looks simple enough but very slow

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mails3
         type: ref
possible_keys: question_id_index,emails_doublestars,favorite_emails,user_responses,owner_responses,sentmail,watchlist,read_responses,rating_date,read_and_unrated
          key: emails_doublestars
      key_len: 8
          ref: const,const,const
         rows: 2784
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: questions
         type: eq_ref
possible_keys: PRIMARY,is_rated_community_senddate_idx
          key: PRIMARY
      key_len: 5
          ref: answerology.mails3.QUESTION_ID
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)


texts: For a MySQL DBA, what operations require root access. I know that we need to be root for installation, starting and stopping services. With DBA’s not having root access, is there a way to perform majority of the operations?


texts: SQL Server: How to track progress of CREATE INDEX command?

texts:
SQL Server 2014, Std Ed

I have read that percent_complete in dm_exec_requests does not work for CREATE INDEX, and in practice, percent_complete sticks at 0. So that doesn't help.

I currently use the method below, which at least shows me movement (that the index creation is not blocked). But I have zero idea if I am %10 through the process or %99.

I tried the method described here:
https://dba.stackexchange.com/a/102545/6229
but it shows a clearly wrong est completion time (it basically shows 'now' for a 60+ minute process that I am 10 min into)

How can I get a clue?

SELECT percent_complete, estimated_completion_time, reads, writes, logical_reads, text_size, *
FROM
sys.dm_exec_requests AS r
WHERE
r.session_id <> @@SPID
AND r.session_id = 58


texts: Get all database, table and column names and column definitions

texts:
I need to check the data is identical between SQL Server.

For that I need following details. Let me know if this is possible.

All Database Names
table Names
column Names
Column Max Length in a table

╔══════════╦══════════╦══════════════╦══════╗
║ Database ║  Table   ║    column    ║ Size ║
╠══════════╬══════════╬══════════════╬══════╣
║ IBM      ║ Employee ║ EmployeeId   ║    5 ║
║ IBM      ║ Employee ║ EmployeeName ║   49 ║
║ IBM      ║ Project  ║ ProjectID    ║    4 ║
║ IBM      ║ Project  ║ ProjectName  ║  110 ║
║ TCS      ║ Employee ║ EmployeeId   ║    6 ║
║ TCS      ║ Employee ║ EmployeeName ║   51 ║
║ TCS      ║ Project  ║ ProjectID    ║    5 ║
║ TCS      ║ Project  ║ ProjectName  ║  226 ║
╚══════════╩══════════╩══════════════╩══════╝


texts:
I have a postgres table that will be used to store data pulled from several remote sources / APIs. I will be syncing the data several times a day.

Since the data will vary wildly depending on the source, I'm thinking about using columns to store the common information (eg. source name, modification timestamp, etc.) and then put the raw record in a json or jsonb column to be examined later.

I'm considering adding a column that will store a hash of the raw record (MD5 perhaps?) that I can use to quickly compare with the "new" record pulled from the source, so if the hash values don't match I will update the record, and if they match I will skip it.

There will be millions of rows, so storage / access efficiency is definitely a factor.

My questions are:

Does this approach make sense?
Is there a more efficient / useful way to store the raw record?
What's the best way to store the hash value? varchar?


texts:
I am developing an app that has two types of user: Customers and employees.

I have planned to make the supertype as user that contains only:

id
username
password

While there would be two subtypes (customer and employee). Each of them has different specific data, namely,

Customer:

id
purchases
name
age

Employee:

id
badge
name
age.

Could someone help me by suggesting a structure with what foreign key to put and implement?

I'm clearly new to making databases.


texts:
I am currently working on a project to mask and de-identify data and wanted to pass my findings through a sanity check (you guys).

Is it better to create a new view which houses masked data and limits source table access to admins, or is it better to create a trigger / stored procedure to apply the masking realtime after query results have been fetched?

With the view approach I would have to make some code changes, as the code base is accessing tables through a traditional ORM. But this would mask data regardless of access (app, cli, after a snapshot).

Is it possible through triggers / stored procedures to intercept a request against a sensitive table, and apply the mask before returning the results? This would avoid having to make any code changes.

Or would it be better to implement a hybrid solution, to add a trigger on a sensitive table, save the data as given, but then apply a stored procedure to store the same (but masked and de-identified) data in the view.

Thanks in advance.


texts:
Suppose I have many data objects that include very many share very many identical strings (e.g. tags, URLs, "DEBUG"/"TRACE"/"INFO", etc...).

Which databases (if any) pool identical strings internally to prevent them from being copied many times?


texts: Tracing 'Invalid Number Error' from millions of rows using a large, complicated query

texts:
Background
I am working on fixing a large query that I inherited. The query consists of over 200 lines of DML statements.

The search queries the database restricting the data to a single month. Most months work just fine, but there are a few months that generate an ORA-01722: Invalid Number error.

I suspect the problem is one of the following:

a column defined as VARCHAR contains alpha characters but is
being used where a number is required
a column is null and is being used where a number is required

If I can find the row that is causing the problem, I can change the query to handle said data appropriately. However, I don't know how I can isolate that row. I have attempted to break the query into parts and see where null values are being generated, but the entire query is dependent on a number of outer joins. I have been able to isolate the range to few days, but that doesn't allow me to isolate the row in question.

Also, the query has been defined as a stored procedure. At the end of the procedure I have defined an error handler to catch all errors and display a message:

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, error_message || parameter_string_values, TRUE);

However, the procedure raises the Invalid Number error and does not get trapped by the error handler at the bottom.

Questions

Are there any methods or database variables that I can set to help
isolate this error?
Why isn't the ORA-01722: Invalid Number error being trapped by the
error handler?


texts: Is it possible for a properly set up MySQL to return invalid UTF-8?

texts:
I am wondering: assuming everything is set up properly, meaning a column with charset utf8mb4 and a client connecting to the database setting the connection encoding to utf8mb4, is there any way in which said client could SELECT data which contains invalid UTF-8 byte sequences? Is there any way to insert data into a utf8mb4 column which is not in fact valid UTF-8?

Put another way: is it reasonable to assume that SELECTing any non-binary column over a client connection set to utf8mb4 should always return valid UTF-8 byte sequences?

(Let's assume plain SELECT * statements without any CAST shenanigans or such.)


texts: using $Partition function to improve query performance

texts:
I have tables which are partitioned based on a INT column.

I see some queries that are using $Partition function to compare partition number instead of comparing the actual field data.

For example instead of saying:

select *
from T1
    inner join T2 on T2.SnapshotKey = T1.SnapshotKey

they have been written like below:

select *
from T1
    inner join T2 on $Partition.PF_Name(T2.SnapshotKey) = $Partition.PF_Name(T1.SnapshotKey)

where PF_Name is the name of partition function.

I see comments on those queries that this has been done to improve performance, and when I run both queries I see different in execution time and different execution plan. I'm not sure how these two queries are different.

This is a real query:

-- this takes about 9 seconds
select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

-- this takes about 5 seconds
select sum(PrinBal)
from fpc
    inner join gl on $Partition.SnapshotKeyPF(gl.SnapshotKey) = $Partition.SnapshotKeyPF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

And below is the execution plan of the real query:

Sorry I can't upload even a sanitized execution plan as uploads are monitored by our network and it might be a policy violation.

Question is: Why execution plans are different and why the second query is faster.

I appreciate if someone can share any idea on this. Just interested to know why they are different. Faster is better though.

This is happening on SQL Server 2014. If I run the same on SQL Server 2012, the result will be different and the first query will perform faster!


texts:
Have anyone implemented Mongo replication using replset arbiter. At the time of fail-over from Primary to Secondary, Arbiter should bring down VIP on primary and enable on secondary when Arbiter appoints secondary as Master.

Is this is possible?

I do understand that it is for voting purpose. But when it can decide who should be available for writes and who shouldn't be, I would like to embed a logic that should also route application requests to the winner. Also I don't want to keep the host decision on app end. Kinda doesn't look clean for me.

Note: It will open write on secondary, doesn't mean that it will route the requests from application to secondary (NewMaster).

So the drivers here are application drivers should be written in such a way.

@mongo_client = MongoReplicaSetClient.new(
['n1.mydb.net:27017', 'n2.mydb.net:27017', 'n3.mydb.net:27017']
).db("test").collection("sample")

To choose its member. I'm I right?


texts: MySQL replication: slave is not getting data from master

texts:
My problem is that all setup is done for MySQL replication but the slave is unable to sync the master's data. To understand I am sharing link below; please visit.

mysql>  SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.110
                  Master_User: slaveuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000033
          Read_Master_Log_Pos: 402
               Relay_Log_File: VoltyLinux-relay-bin.000046
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000033
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replica
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 402
              Relay_Log_Space: 692
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f1739fcc-0d2d-11e6-a8cc-c03fd56585b5
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 60
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

https://stackoverflow.com/q/36929641/2644613


texts: EXPLAIN ANALYZE shows no details for queries inside a plpgsql function

texts:
I am using a PL/pgSQL function in PostgreSQL 9.3 with several complex queries inside:

create function f1()
  returns integer as
$$
declare

event tablename%ROWTYPE;
....
....

begin

FOR event IN
   SELECT * FROM tablename WHERE condition
LOOP
   EXECUTE 'SELECT f2(event.columnname)' INTO dummy_return;
END LOOP;

...

INSERT INTO ... FROM a LEFT JOIN b ... LEFT JOIN c WHERE ...

UPDATE T SET cl1 = M.cl1 FROM M WHERE M.pkcols = T.pkcols;

...

end
$$ language plpgsql;

If I ran EXPLAIN ANALYZE f1(), I only get the total time, but no details. Is there a way I can get detailed results for all queries in the function?

If queries in the function should not be optimized by Postgres, I would also ask for an explanation.


texts: Return available rooms depending 2 others tables


texts:
What is the cost of block nested loop join?
Given B1 blocks of R1 (outer relation) and B2 blocks of R2 (inner relation), why does it cost B1+B1*B2?


texts: How to get dates from a table while the date range is from the date belonging to another table?

texts:
I have a table datedim which has all the dates of a month in it. I have another table attendance that has dates on which attendance were taken. I need to write a query where i can get all the dates from the start till current.
For eg. if attendance table has records starting from 3-Apr-2019 to 3-June-2019, i should get all the dates in between them.


texts:
I have a server in which there is no SSMS installation, and only query analyzer is available. And all I need to do is find the trigger information (basically trying to find out what that trigger does).

I tried using object explorer in query analyzer, to use "Alter To->New window" command. But that command failed saying "Use SSMS to alter the query".

Is there a way, I can write a simple sql query, that gives me the trigger information?


texts: How to add slots for Slot table such that there are no clash

texts:
I have a Slot Table which has following fields :

   CREATE TABLE `Slot` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `starttime` datetime NOT NULL,
      `endtime` datetime NOT NULL,
      `assigned_To_Id` int(10) unsigned DEFAULT NULL,

      PRIMARY KEY (`id`),
      KEY `appointment_assigned_to_id_foreign` (`assigned_To_Id`),
      CONSTRAINT `appointment_assigned_to_id_foreign` FOREIGN KEY (`assigned_To_Id`) REFERENCES `Employee` (`id`),
    )
 ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

Now, the admins can add slot for a employee(assigned_To_Id) for a particular day by specifying the slots :
e.g. :

7 July Slots :
11:45-12:00
12:00-13:00
and slot time = 15 mins (above time range will be divided in slot time intervals of 15 mins and assigned_To_Id = 1

Now before adding the slots we need to check if there are any clashes for the slots with existing data in db which are assigned to 1.

select count(*) from Slots
where
(
    (starttime >= '2018-07-07 11:45:00' and  ('2018-07-07 11:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 11:45:00' and  '2018-07-07 11:59:59' and endtime <= '2018-07-07 11:59:59')
    or
    ('2018-07-07 11:45:00' Between starttime and endtime  and '2018-07-07 11:59:59' Between starttime and endtime)
)

or
(
    (starttime >= '2018-07-07 12:00:00' and  ('2018-07-07 12:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 12:00:00' and  '2018-07-07 12:59:59' and endtime <= '2018-07-07 12:59:59')
    or
    ('2018-07-07 12:00:00' Between starttime and endtime  and '2018-07-07 12:59:59' Between starttime and endtime)
)
 and assigned_To_Id = 1
 so if there are 'n' slots --> check for each slot

Like for every time slot give by admin we have 3 conditions which we can use to check if there is any time clash or any existing slot

if the query returns a count of 0, that means we are good to add slots
like divide 11:45-12:00 into slots of 15 mins and 12:00-13:00 also in slots of 15 mins.

My question is : How can I run this insert along with the count(*) query such that if two admins try to do similar thing only one of them succeeds.

Can we combine insert statement with condition ?
Is stored procedure a good idea for this one ?


texts:
I work in a parking company. We need a report of monthly vehicle exits by time.

select hour(datetimeout) as 'dateout',count(*)
from to_leaveveh
where datetimeout between '2014-09-01' and '2014-09-02'
group by dateout

This only shows data for one day. I want to create a monthly report with:

Time in 24 hour format
Count of vehicle exits


texts: Splitting up tables with similar structures since they contain unrelated data? (huge numbers)

texts:
Is my reasoning correct in splitting up tables with similar structures since they contain unrelated data?

I'll explain the situation with an example :

Suppose there is a server which hosts an online game's database. The game has multiple gameworlds which operate in parallel. The schema for each gameworld of the game is same, let's say there are 15 relational tables in the database design of the game. Also assume that the server decides not to delete the data ofold servers and instead, archives it.

Approach 1 :

Now since the gameworlds are hosted on one server, It might seem logical to put all the game data into one database containing 15 tables. The table structure of an example table might look like this :

CREATE TABLE `table` (
    playerid ...,
    score ....,
    gameworldid ...,
    PRIMARY KEY(gameworldid,playerid)
) ENGINE = InnoDB

So, everytime I need to show top 10 players with most score, I will have to do something like SELECT * FROM table WHERE gameworldid = {id} ORDER By score DESC LIMIT 10

Approach 2 :

Since the data for one gameworld doesn't relate to data of the other gameworld in any way (except for the structure), we may make different databases for different gameworlds, or different set of tables for each gameworld. The table structure will look something like this :

CREATE TABLE `gameworldid_table` (
    playerid ...,
    score ....,
    PRIMARY KEY(playerid)
) ENGINE = InnoDB

In this case, top 10 players with most scorequery will be SELECT * FROM table ORDER BY score DESC LIMIT 10

The question is, which approach is better in this situation? The storage engine being used is InnoDB, and in all , 6 billion and up rows can be expected to be there in the biggest merged table, and 500 and up gameworlds. (Up since the old data isn't deleted, so when a gameworld restarts, a new gameworld is created.

In my opinion, the advantages of Approach 2 over Approach 1 are :

Less space consumed by primary key and indices, making them more efficient
Faster sorting, joining and other stuff as compared to merged table in approach one which contains lots of extra data that might come in way of these operations.
Faster inserts as compared to ones in larger tables
Data won't keep just piling up forever in the same table

And the advantages of Approach 1 over Approach 2 ?

Better manageability : 15 tables sound better as compared to 500 * 15 tables, if you ever have to do some manual fixing (rare).
Sounds less insane
Too many tables may just make database slower ( don't know for a fact, can't find it on the internet)

I guess my opinions on advantages and disadvantages may be biased, but I really want to know what all problems can come my way if I choose one Approach over the other. Also, is there any other possible solution for this solution?


texts: Oracle DB Security Issue: Anyone can login using SQL*PLUS, with any username, and any wrong passphrase

texts:
I'm using Oracle DB 11g-R2 on CentOS 6.4, and unfortunately there is a terrible issue freaking me out.

Anyone can login using SQL*PLUS, with any username, and any wrong passphrase.

Consider somebody logging in using SYS AS SYSDBA with a wrong passphrase, and get all database and tables he wants. Isn't it bad?

I know you may say that when somebody gets in physically, they can circumvent the rest, yes indeed. But anyone who knows this problem, doesn't need to get in physically, they will be only in need of the IP address and the name of your DB to login remotely or use a script to do what they need by SQL*PLUS.

Fortunately, anyone logging in with wrong passphrase for three times, will be locked for the next time. But what is the benefit? Personally as an attacker, I only need one time to be in someone's database to get the all things I need.

Does anybody know how can I fix this and why is this happening?


texts: Large `mongodump` followed by `mongorestore`

texts:
I have a fairly large (~240GB) Mongo database that I need to transfer across a sluggish network and onto a new server.

Traditionally for these situations, I've found that a mongodump followed by a mongorestore has been much faster than a db.cloneCollection() method. However, I realized today that doing a full mongodump followed by a mongorestore is a bit wasteful (I think) since I do all of the data transfer, then do all of the insertions.

I would prefer to transfer data from the old mongo (the mongodump step) while simultaneously inserting available data into the new database (the mongorestore step).

Does anyone know how to parallelize the dumping and inserting process in MongoDB? (And would this actually be faster?)


texts: Extremely slow query on indexed column in Postgres

texts:
I'm getting a extremely slow query on an indexed column. Given the query

SELECT *
FROM orders
WHERE shop_id = 3828
ORDER BY updated_at desc
LIMIT 1

explain analyze returned:

    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..594.45 rows=1 width=175) (actual time=202106.830..202106.831 rows=1 loops=1)
   ->  Index Scan Backward using index_orders_on_updated_at on orders  (cost=0.43..267901.54 rows=451 width=175) (actual time=202106.827..202106.827 rows=1 loops=1)
         Filter: (shop_id = 3828)
         Rows Removed by Filter: 1604818
 Planning time: 98.579 ms
 Execution time: 202127.514 ms
(6 rows)

The table description is:

                                         Table "public.orders"
       Column       |            Type             |                           Modifiers
--------------------+-----------------------------+---------------------------------------------------------------
 id                 | integer                     | not null default nextval('orders_id_seq'::regclass)
 sent               | boolean                     | default false
 created_at         | timestamp without time zone |
 updated_at         | timestamp without time zone |
 name               | character varying(255)      |
 shop_id            | integer                     |
 recovered_at       | timestamp without time zone |
 total_price        | double precision            |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "index_orders_on_recovered_at" btree (recovered_at)
    "index_orders_on_shop_id" btree (shop_id)
    "index_orders_on__updated_at" btree (updated_at)

It's a Postgres server, running on an AWS RDS t2 micro instance.
The table has around 2.6 million rows.


texts: Incorrect hierarchy relationship while joining with parent-child tables

texts:
I have two tables with 1:M relation in my DB:

GROUP (GroupId, GroupName, AccountId)

USER (UserId, UserName, GroupId, AccountId)

and ACCOUNT (AccountId, AccountName).

I added all needed links from one to another table in DSV and created 3 dimensions for each of the tables. Then I added one hierarchy for GROUP and USER dimensions like 'Account - Group' and 'Account - User'.

The strange behavior that I see is that everytime I query USER dimension with filter by AccountId it just returns me results for GROUP dimension, like there is no relation between USER and ACCOUNT at all! It seems like SSAS engine sees GroupId in my USER table and resolves relation with ACCOUNT using GroupId instead of UserId.

  SELECT { } ON COLUMNS,

  [User].[Account - User].[Account].&[11].Children ON ROWS

  FROM Cube

This query returns users that are in the groups linked to account with id 11, not users linked to account 11 directly.

In other words, what I am trying to get is to get results like if tables GROUP and USER were independently related to ACCOUNT and my hierarchies works properly.


texts: MySQL database problems all of a sudden-- users' programs can't connect, I saw error 2013, error 32

texts:
I am editing my problem, based on what I've researched and done:

Hi

This is mysql running on an osx server.

We suddenly have an issue where only one of anything can seem to connect to mysql at anytime. The problem started with the accounting program we use; only one person could access and everyone else got an "error connecting to mysql". Even if I'm on the server, I can't run mysql workbench or sequel pro to connect. workbench gives me the error

"Error connecting to MySQL: Lost connection to MySQL server at 'reading authorization packet', system error: 0 (code 2013)
MySQL stat graphs may be disabled."

Sequel Pro reports error 32 and 2013, and if I try to run mysql dump i get error 32 and 2013. Once any person logs off from mysql, i or another user can get back on again, but only one at a time.

This started happening about 2 weeks ago and I am unaware of any changes to the system.

This is hurting a cashstrapped group so I hope I can get them some relief ASAP. I seem to be reading about a ton of possible fixes but no certainties. Any help would be appreciated.

Update: Using mysql workbench, pressing "stop server" and "start server" has alleviated the problem. Still, I would like to understand the cause and fix it, rather than this very clumsy method. Thanks for reading.


texts:
I'm having an issue with a stored procedure in DB2 LUW 10.5 using Data Studio 4.1.3 when trying to call more then one statement at a time.  I've narrowed the problem down to the way semi-colon's (;) are being treated.  For easier trouble-shooting I created a really simple example of what I am trying to do.

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";
    insert into "DB2ADMIN"."TestTable"
    values('a','a','a');
    insert into "DB2ADMIN"."TestTable"
    values('b','b','b');
    select * from "DB2ADMIN"."TestTable";

If I run the code above it completes fine but when I look at the sp I only see:

--<ScriptOptions statementTerminator=";"/>

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";

It only reads to the first semi-colon and then stops.  If I remove all semi-colons but the last one the procedure will not get created.  My code in real life is much longer but is behaving the same.  Any help or ideas would be greatly appreciated.


texts: How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

texts:
I have the following three tables:

create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id), gid int references groups(id));

Following are the records in the respective tables:

insert into users values (1, 'John'), (2, 'Bob'), (3, 'Maria'), (4, 'Jin');

insert into groups values (1, 'Persons - A'), (2, 'Persons - B'),
(3, 'Persons - C'), (4, 'Persons - D'), (5, 'Pay - A'), (6, 'Pay - B'), (7, 'Pay - C');

insert into user_groups values (1, 1), (1, 2), (2, 1), (2, 4), (2, 6), (2, 7),
(3, 1), (3, 7), (4, 1), (4, 3);

Note: There are two types of groups, Persons and Pay

Now I want to find those users who have access to both types of groups (Persons and Pay), below is the desired output:

id        name       groups_user_has_access_to
2         Bob        Persons - A, Persons - D, Pay - B, Pay - C
3         Maria      Persons - A, Pay - C

The result should have only those users with their groups that have access to both types of groups.

Sorry for the bad English and appreciate your help in advance.


texts: How bad is it to delete from a Greenplum AO table?

texts:
We have a few AO tables (which nowadays means "Append Optimized", not "Append Only"). Normally, we just insert into these tables.

As part of a significant system change, I need to do a fairly large delete from these tables. It's only about 2% of the data volume, and only over a small range of partitions. Eventually those partitions will roll off the end of our retention policy.

I can't find any information on the consequences of executing a delete on an AO table.


texts: I Need Help Setting Up Roles and Passwords in PostgreSQL

texts:
I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used to store passwords where you would not have to enter them when accessing a database instance. I really need more detailed understanding on the Unix terminology being used in pg-hba.conf.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

For my Ruby on Rails projects having roles with passwords with different types of access will be sufficient. From what I can tell everything I want would be considered local.

I submitted the two questions below in Stack Overflow last year and tried to implement them but to no avail. I ended up either locking myself out of PostgesSQL or with no passwords working. At that time I was using the built-in PostgreSQL instance on my Mac Mini Server.

https://stackoverflow.com/questions/22882280/postgresql-9-2-4-desire-to-change-the-localhost-server-password-in-mavericks

https://stackoverflow.com/questions/25167284/rails-4-desire-to-set-postgresql-password-access-database-in-rails

I want to add an encrypted password to my current role. I also want to add two more roles with encrypted passwords to use in my Ruby on Rails applications hosted on my Mac Mini server, one with read only access and the other with read/write access.

I assume I would execute the following commands in the PostgreSQL terminal or in pgAdmin.

CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonlypasswordencryptedtext';
CREATE USER readwrite WITH ENCRYPTED PASSWORD 'readwritepasswordencryptedtext';
GRANT SELECT PRIVILEGES ON ALL TABLES IN public TO readonly, readwrite;
GRANT INSERT, UPDATE, DELETE PRIVILEGES ON TABLE table1, table2, table3, etc. TO readwrite;
ALTER ROLE myuserid WITH ENCRYPTED PASSWORD 'myuseridpasswordencryptedtext';

I would like help on how to change pg-hba.conf and in what order so I don't lock out myuserid AND if there are additional things I need to consider.

I have multiple databases with tables. I don't think my GRANT statements take that into consideration. Would I need to create a schema to include all my databases for the readonly user and the databases to update for the readwrite user?


texts: Pass multiples values to script into sqlplus in Oracle

texts:
I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus.

With this select I have the values but I don't know how to pass this values.

 select column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec')))

 LOOP
   start script.sql column_name;
 END LOOP;

I don't understand your example. I have try to use but it doesn't works. I want to pass both values to "prueba.sql" (one in each execution)

cat prueba.sql
 prompt &1;

SQL> COL column_value NEW_VALUE v_column_value
SQL> select column_value column_value from table(sys.dbms_debug_vc2coll('Logons Per Sec', 'Executions Per Sec'));

COLUMN_VALUE
--------------------------------------------
Logons Per Sec
Executions Per Sec

SQL> @prueba.sql &&v_column_value
Executions

I want to execute a report inside the prueba.sql each time with one of this values as parameter.

Thanks


texts: Read only postgres table, except by trigger

texts:
Suppose the following situation:

I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock table increasing or decreasing the quantity and also insert an entry in the transaction log table, for auditing. This is done in the application code.

I am considering changing the update of the product_stock table to be done by a trigger on the transaction log table (instead of doing it directly in my application code), but I would like to add a reinforcement that the product_stock table would never be updated directly, and only the trigger would be authorized to update it.

Is there anyway to do this in postgresql (currently using 9.1)


texts:
I am new to database design and despite my research I can really only find basic examples on design which does not help for my situation.

Currently using MySQL

The problem I have is as follows:

I am building a site to manage aircraft information, then display that information and how it changes in the form of graphs (things like air speed, plane speed, wing tilt, ect ect).

At the moment I get all this data in CSV form, but different planes give different data (for example one plane might not have wing tilt).

At the moment all I have set up is a table with userID, username, and password.

The concept I do not understand is how I can store something like planeSpeed, especially since it would be a bunch of numbers because it is over the course of the flight (it gets a value every minute).

Also some users would have multiple planes.

So I am stuck visualizing this as a column would be airspeed and then have a ton of numbers in it?

Or would I somehow build a separate table for each plane and instead of userID I would have time?

Very unsure how to design this, any help or tips would be a huge help, sorry for sounding so inexperienced have never built more than a simple login before.


texts: Create a default value for session valid_until MySQL

texts:
Server version:        5.6.22 Homebrew

The table:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

By default, I want the valid_until column to be the current time + 1 day (or whatever). I've tried settings the 'default' to DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY) but this failed. I also tried changing the type to date, timestamp, time and year but those give the same error:

ALTER TABLE `sessions` CHANGE `valid_until` `valid_until` DATETIME
 NOT NULL
 DEFAULT 'DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY)'

MySQL said: Invalid default value for 'valid_until'


texts: Is a join faster with 5 tables joined together Or 5 separate queries?

texts:
I have confusion that I already have in my application 5 tables say a,b,c,d,e and have relationship with each other like a.b_id is primary key of b and chain goes on.
Now my question is that which approach will give me better performance/faster result?
5 queries
Or
A query join with 5 tables?
Thank you.


texts: Should I look into alternatives to a relational database?

texts:
I am currently in the process of going through some PHP training with procedural code and will be doing a course in OOP with PHP right after that. The course will provide a solid starting point for me to build a social network project that I originally designed in a relational database format. The design of the site should be fairly simple as there are less than 10 templates to build the site, and then a few static pages to tie it all together.

But as I researched other social networks and sites with larges amount of constantly changing data, I began finding things about social graphs and such that make the sites much faster in handling millions of people all at once.

Since my site will be based mostly on user generated content (article submissions or possible wiki type submissions) and connections with others through groups/ common interest/ and individual friend relationships, I think a standard relational database may not be the most efficient means to operate as the site grows.

So I am trying to decide my growth plan. Should I continue to design my site relationally for now and worry about upgrading to something else as my site grows? Or should I start planning now to use a different database style or programming language?

If the later, What should I look into. NOSQL seems to be the direction I need to go, but there are several types of that as well so trying to figure out what I may need is difficult. Will it work with the PHP code I already have set up, or would it require a complete redesign?

Also, Does OOP design prompt for different database designs than a procedural design?


texts: Why not use a table instead of a materialized view?

texts: I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a  physical table, why not store the data into a table in the first place? So what is the benefit of using materialized view instead of a table?


texts: Unable to start the Integration services for SQL server 2012

texts:
Suddenly we are facing some errors in starting up the integration services.

From event viewer and ssms when we tried connecting we get below error message

The SQL Server Integration Services 11.0 service failed to start due to the following error:
This version of SQL Server Integration Services 11.0 is not compatible with the version of Windows you're running. Check your computer's system information and then contact the software publisher.

Some info:- This is the mirror server where databases are in mirror state. We had some windows patching last night and after reboot started seeing these errors. Interestingly we had other servers also patched along with its principal, but did not witness the similar IS error out there and other servers.

Tried researching on this but unable to much details on the error posted.

Thanks, please suggest


texts: Is there a quantitative method to follow to determine FILLFACTOR for NONCLUSTERED INDEXes?

texts:
I've read different ideas to set FILLFACTOR and then monitor fragmentation over time (#1: 5 Things About Fillfactor, #2: Database Maintenance Best Practices Part II – Setting FILLFACTOR).

Instead of making an 'educated' guess upfront and then adjusting, I was wondering:

If anyone knew of a (quantitative) way to determine what level of fillfactor would be needed upfront?

(I'm primarly concerned with respect to NONCLUSTERED INDEXes, as I typically create my clustered indexes on ever-increasing integers.)


texts: SQL Server how to detect unused views and/or get usage stats

texts:
Is there a way to determine if a view is no longer being used (without removing them)?

Ideally I would like to know the views' usage for SQL Server 2000 and 2012.

I am upgrading some databases and suspect that many of the views are no longer being used. Also, some of the views will be difficult to compile on the new server as they access multiple databases, some of which are not being moved to the new server.


texts: querying a JSOn object in separate columns from Postgres table

texts:
I have a table with column type as json.
select request_id,cast(job_info as varchar)  from requests_table where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%';

Here job_info and job_id are the json type column.
It yield me data like this:
          request_id   |                 job_info
--------------------------------------+-------------------------------------------------------------------------------------------------------------------
 f373085d-0124a161b2d7 | ["test-cluster", "test-cluster-id-11111"]
(1 row)

I am expecting job_info data should get retrieved as two separate column.
f373085d-0124a161b2d7,test-cluster,test-cluster-id-11111


texts: On modeling pairs with a possibly empty second element

texts:
EDIT: I've made the post significantly shorter.  I hope it's more digestible now.

Consider the following schema:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE VIEW mydata AS

  SELECT X.x, Y.y

    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y);

This schema enforces the following constraints on the rows of the mydata view:

no two rows may have the same y-value but different x-values;
the x-value is never empty;
the y-value is never emtpy;

The following output listing would be consistent with this schema:

Listing 1

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b | j
 c | k
 d | l
 a | h

Now, suppose that instead of the three constraints above we had the following:

  1'. no two rows may have the same non-empty y-value but different x-values;

  2'. the x-value is never empty;

(Constraint 2' is the same as constraint 2; constraint 1' entails adding the qualifier "non-empty" to constraint 1.)

With these new constraints the following listing, where the missing y-values are NULL, would become valid:

Listing 2

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h

But such a listing would not be possible with the schema shown above.  Even if the NOT NULL constraint were removed from the definition of the _DATA.y column, the closest we would get is

Listing 3

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h

...because there's no way to determine the appropriate value of x for those rows where y is NULL.

I'm looking for a normalized schema that would enforce the new set of constraints (and thus allow data like that shown in Listing 2).

My "non-solution"

My "non-solution" to this problem is a hard-to-maintain hack.  I post it here for three reasons:

to clarify the situation described in the question above;
to provide table-initialization code that responders can use to test their proposals if they so wish (it's in fact the code I used to generate Listing 2);
to give an example of the sort of hard-to-maintain hack that I'm trying to avoid.

This "non-solution" consists of defining some distinct values of y that can somehow be recognized as NULL, and redefining mydata to make use of this information.  For example:

INSERT INTO X VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('NULL_b', 'b')
  , ('k', 'c')
  , ('NULL_d', 'd')
;

INSERT INTO _DATA VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'NULL_b')
  , (4, 'k')
  , (5, 'NULL_d')
  , (6, 'h')
;

CREATE TEMP VIEW mydata AS
  SELECT x,
         CASE WHEN y LIKE 'NULL_%'
              THEN NULL
              ELSE y
         END AS y
    FROM X JOIN     Y USING (x)
           JOIN _DATA USING (y);

Then

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h
(6 rows)

Ugly as sin, but aesthetic aside, it is also very hard to maintain.


texts: Oracle 11.2: Backup and recovery strategy based on full cold backup plus expdp

texts:
Are there any issues with the following strategy for backup/recovery in Oracle 11.2.0.4?

Perform a full database cold backup once a month, moving the file off the server for safekeeping.
Write a cron job to perform expdp of all schemes except SYS daily, retaining only the last two weeks of files, and moving them off the server for safekeeping.
In the event of a failure, perform full database recovery using the cold backup, then do an impdp from the latest file (to restore all schemas except SYS). I can accept losing all database data since the last expdp file was taken (which is 24 hours, worst case).

The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?


texts: Return Bit Value based on previous records by Category

texts:
How would I return TRUE or FALSE if four of the last six records failed for a given MAKEID?  So there has to be 4 failures (or FALSE records) in the last six

MAKEID       FAILURES    CREATEDATE
101          TRUE        12/1/2015
101          FALSE       12/5/2015
101          TRUE        12/6/2015
101          FALSE       12/8/2015
101          TRUE        12/10/2015
101          FALSE       12/15/2015
101          FALSE       1/1/2016
101          FALSE       1/2/2016
102          TRUE        12/2/2015
102          TRUE        12/4/2015
102          FALSE       12/5/2015
102          TRUE        12/6/2015
102          FALSE       12/7/2015
102          TRUE        1/1/2016

Query would return the following:

MAKEID    FOUROFLASTSIXFAILED
101       TRUE
102       FALSE

Requirement is there needs to be 4 or more failures in last six records.  So if there were 4, 5, or 6 failures in the last six records FOUROFLASTSIXFAILED would return TRUE.  If there were only 1, 2, 3 records for a given MAKEID that would return FALSE because there at least needs to be 4 failures in previous six records.


texts: How to pull a list of applied service packs and cumulative updates from SQL Server 2008 R2?

texts:
I want to list all the SPs and CUs applied to a SQL Server 2008R2 instance.

Example:

10.50.4260 2720425 http://support.microsoft.com/kb/2720425 CU#1 for SQL Server 2008 R2 SP2
10.50.4000 2630458 http://support.microsoft.com/kb/2630458 Service Pack 2
10.50.2881 2868244 http://support.microsoft.com/kb/2868244 CU#14 for SQL Server 2008 R2 SP1

How can I get the above list from SQL Server 2008R2 instance?


texts: Have two versions of one object in ssdt sql server database project or have objects visible/not visible depending on configuration option selected

texts:
I have a database project with 3 tables. 2 of the tables are the same table, but one is configured as a temporal table and the other is not. The 3rd table in the project is the history table for the temporal table version of Table_1.
When I edit the .sqlproj file manually and change the custom variable I created to IsTemporal=true then Table_1 shows up as a temporal table and Table_1__TemporalHistory shows up too in solution explorer. When I change IsTemporal=false then only Table_1 shows up in object explorer without the extra stuff for temporal.
Here is what the section of the .sqlproj file looks like defaulted to IsTemporal=false and the objects that appear in solution explorer.
  <PropertyGroup>
      <IsTemporal Condition="'$(IsTemporal)'==''">false</IsTemporal>
  </PropertyGroup>
  <ItemGroup>
    <Build Include="dbo\Tables\Table_1.table.sql" Condition="'$(IsTemporal)' == 'false'" />
    <Build Include="dbo\Tables\Table_1__TemporalHistory.table.temporal.history.sql" Condition="'$(IsTemporal)' == 'true'" />
    <Build Include="dbo\Tables\Table_1.table.temporal.sql" Condition="'$(IsTemporal)' == 'true'" />
  </ItemGroup>

When I edit the section of the .sqlproj file setting the default IsTemporal=true the version of the objects with temporal stuff will appear in solution explorer.
  <PropertyGroup>
      <IsTemporal Condition="'$(IsTemporal)'==''">true</IsTemporal>
  </PropertyGroup>
  <ItemGroup>
    <Build Include="dbo\Tables\Table_1.table.sql" Condition="'$(IsTemporal)' == 'false'" />
    <Build Include="dbo\Tables\Table_1__TemporalHistory.table.temporal.history.sql" Condition="'$(IsTemporal)' == 'true'" />
    <Build Include="dbo\Tables\Table_1.table.temporal.sql" Condition="'$(IsTemporal)' == 'true'" />
  </ItemGroup>

What I'm trying to figure out how to do now is be able to toggle back and forth between IsTemporal=true and IsTemporal=false without having to manually edit the .sqlproj file. I was thinking I should be able to add 2 new configurations to be able to toggle between the two, but I cannot figure out how to tie the new configurations I created to the IsTemporal property I added to the .sqlproj file.


texts: Divide by Zero Causing Error transaction to fail with Arthimetic Abort and ANSI WARNINGS FALSE after upgrade

texts:
This is a strange issue..

We are receiving Divide by zero errors that are causing SQL Server to terminate transaction and crashing the test website.

Arithmetic Abort is set to FALSE along with ANSI warnings and all the other defaults... No code change has happened so it is not explicitly set in the code either.

This is only happening on most of the databases on the server that contain very similar data and the same metadata, but not all.

This has started happening after I performed a in place upgrade of our 2008 test server to 2012. (the issue occurs in both compatibility modes)

If I explicitly turn ARITHMETICABORT to false inside the query window it runs fine... something seems to be ignoring the database wide settings. this happens from within management studio or from code

here is the first piece of code we detected it in, for reference, but I think this is not related to the code

    SELECT  sm.MarkupId, sm.WebSiteCode, sm.CurrencyCode, sm.OfferTitle, sm.Priority, sm.MarkupPercent, sm.MarkupDownAmount, sm.Cumulative,
            sm.AllowAdditionalDiscounts, sm.StartDate, sm.EndDate, sm.DayOfWeek, sm.StartTime, sm.EndTime, sm.DepartureStartDate, sm.DepartureEndDate,
            ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId, ISNULL(sb.CommissionPercent, 0) AS CommissionPercent, eg.Title AS EstabGroup,
            d.DomainName, sm.DurationFrom, sm.DurationTo, sm.BookingTotalFrom, sm.BookingTotalTo,
            ActualCommission = CASE WHEN ISNULL(sb.CommissionPercent, 0) = 0 THEN CAST(( ( sm.MarkupPercent - 100 ) / sm.MarkupPercent ) * 100 AS DECIMAL(18, 2))
                                    ELSE ROUND(ISNULL(sb.CommissionPercent, 0) - ( 100.0 - sm.MarkupPercent ), 2)
                               END, sm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    SupplierMarkup_tbl sm
            INNER JOIN Domain_tbl d ON d.DomainId = sm.DomainId
            LEFT JOIN Supplier_tbl s ON sm.SupplierId = s.SupplierId
            LEFT JOIN acomEstabGroup_tbl eg ON sm.EstabGroupId = eg.EstabGroupId
            LEFT JOIN SupplierBookedItemType_tbl sb ON sb.SupplierId = s.SupplierId
                                                       AND sb.WebsiteCode = sm.WebSiteCode
                                                       AND sb.BookedItemTypeId = 17
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = sm.BoardTypeId
    WHERE   1 = 1
    UNION
    SELECT  CASE WHEN cm.IsExtranet = 1
                      AND cm.GrossAdjustment = 1 THEN -2
                 WHEN cm.IsExtranet = 1
                      AND cm.NetAdjustment = 1 THEN -1
                 ELSE 0
            END AS MarkupId, '' AS WebSiteCode, '' AS CurrencyCode, mo.Title AS OfferTitle, -1 AS Priority, cm.MarkupPercent * 100 AS MarkupPercent,
            0 AS MarkupDownAmount, cm.Cumulative, cm.AllowAdditionalDiscounts, cm.StartDate, cm.EndDate, cm.DayOfWeek, cm.StartTime, cm.EndTime,
            cm.CheckInStartDate AS DepartureStartDate, cm.CheckInEndDate AS DepartureEndDate, ISNULL(s.name, '') AS Supplier, ISNULL(s.Supplierid, '') AS SupplierId,
            0 AS CommissionPercent, '' AS EstabGroup, d.DomainName, cm.DurationFrom, cm.DurationTo, cm.BookingTotalFrom, cm.BookingTotalTo, ActualCommission = 0,
            cm.BoardTypeId, BoardTitle = ISNULL(b.Title, '')
    FROM    conContractEstabMarkup_tbl cm
            INNER JOIN Domain_tbl d ON d.DomainId = cm.DomainId
            LEFT JOIN Supplier_tbl s ON s.SupplierId = 97
            LEFT JOIN acomBoardType_tbl b ON b.BoardTypeId = cm.BoardTypeId
            LEFT JOIN dbo.MarkupOffer_tbl mo ON mo.MarkupOfferId = cm.MarkupOfferId
    WHERE   cm.EstabId IS NULL
            AND cm.Deleted = 0
    ORDER BY Priority, Supplier

This is not strictly an answer as I cant tell why this would happen, but I have found the root cause. Hopefully someone can tell me why this behavior happens or if it is a bug ?

The error happens when the 2nd statement after the UNION doesn't return any rows.

not seen one like this in a while !


texts: Getting rid of While Loops in function(s)

texts:
I am running into a problem where WHILE loops are crushing performance under higher data volumes just simply because of the number of iterations through the loop it is taking to get to the final result.

I am looking at a Recursive CTE, but I am running into not really knowing how to pass a variable that gets tallied at the end of an iteration that subsequently gets passed back into a function. Here is the code

CREATE FUNCTION [dbo].[Erlang_C_Required]
(
  -- Add the parameters for the function here
  @NCO FLOAT -- Average Arrival Rate  (second)
  ,
  @AHT FLOAT -- Average Call Duration (seconds)
  ,
  @TASA FLOAT -- Time Goal
  ,
  @TSVL FLOAT -- Service Level Goal
)
 RETURNS @Return TABLE ( Agents FLOAT NOT NULL )
AS
BEGIN
    IF @NCO = 0.0
        BEGIN
            INSERT  INTO @Return
                    SELECT  0;
            RETURN;
        END;
    IF @AHT = 0.0
        BEGIN
            INSERT  INTO @Return
                    SELECT  0;
            RETURN;
        END;
    IF @TASA = 0.0
        BEGIN
            SET @TASA = 30.0;
        END;
    IF @TSVL = 0.0
        BEGIN
            SET @TSVL = .7;
        END;
    DECLARE @Traffic FLOAT; -- Traffic Intensity
    SET @Traffic = ( @NCO / 1800 ) * @AHT;

    DECLARE @Agents FLOAT; --number of agents

    SET @Agents = 2;

    WHILE ( SELECT  Serv
            FROM    dbo.Erlang_C_Serv(@Agents, @Traffic, @TASA, @AHT)
          ) <= @TSVL
        BEGIN
            SET @Agents = @Agents + 1;
        END;

-- Return the result of the function

    INSERT  INTO @Return
            SELECT  @Agents;
    RETURN;

END;

So the question is how would I go about eliminating the WHILE loop (there are more nested in this "tree" of functions that get called for this Erlang C calculation)


texts: Why does SQL Browser service get disabled?

texts:
I have an intermittent problem with a software installation package that installs our product (written using InstallShield/InstallScript).  During the process of the installation, we restart the SQL Browser Service.  Most of the time this works fine.  But occasionally - and I have not worked out how to reproduce this predictably - the service fails to restart, and I find in my "Services" manager that the service status is set to "Disabled".

Any ideas what would be causing the service to be disabled, and how to prevent it happening?


texts: Microsoft SQL Server 2012 Audit Problems

texts:
I have a current audit in MS sql server 2012 that is set up with the following audit properties:

queue delay : 1000 milliseconds
on audit level failure: continue
audit destination:  file
file path C:\STIGAudit
audit file maximum length : maximum rollover file selected with maximum number of files = 5000
maximum file size: 2MB
reserve disk space: checked

The audit when enabled writes 2MB files until the folder reaches 100MB size and then it writes only 0KB files to the folder. Why is it writing empty files after 100MB? Shouldn't it be writing files until it reaches approx. 10GB? The audit was disabled while these values were input and then renabled afterwards.


texts:
I am running an Insert Into statement in my stored procedure that is receiving data from a Table Value Parameter  I just realized I need to 1st verify that the data does not already exist in the table before doing an insert so I am attempting to alter my procedure to this

ALTER PROC [dbo].[CMT]
(@TempTable AS dbo.TableValue_ET READONLY)
AS
BEGIN

if b.[empID] NOT IN (Select [empID] FROM [Habib].[dbo].[SE])
BEGIN
  INSERT INTO [Habib].[dbo].[SE]([empID],[POD],[empNotes],[hiredate],[terminationdate],[terminationtime],[address],[city],[state])
  SELECT
  [empID]
  ,[POD]
  ,[empNotes]
  ,convert(varchar(10), [b].[hiredate], 101)
  ,convert(varchar(10), [b].[terminationdate], 101)
  ,[terminationtime] = LTRIM(RIGHT(CONVERT(VARCHAR(20), [b].[terminationdate], 100), 7))
  ,[address]
  ,[city]
  ,[state]
  FROM @TempTable b
END

END

However, when I attempt to save this updated syntax I get an error of

  Msg 4104, Level 16, State 1, Procedure UpdateShippingExceptions, Line 7
  The multi-part identifier "b.empID" could not be bound.

What must I change so that I can 1st check if the empID already exists in the table before inserting it?


texts: Why is the CTE much worse than inline subqueries

texts:
Im trying to better understand how the query planner work in postgresql.

I have this query:

select id from users
    where id <> 2
    and gender = (select gender from users where id = 2)
    order by latest_location::geometry <-> (select latest_location from users where id = 2) ASC
    limit 50

It runs in less than 10ms on my database with around 500k entries in the users table.

Then I thought that to avoid the duplicate subselects I could rewrite the query as a CTE, like this:

with me as (
    select * from users where id = 2
)
select u.id, u.popularity from users u, me
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this rewritten query runs in around 1 sec! Why does this happen? I see in the explains that it doesnt use the geometry index, but can anything be done for that? Thanks!

Another way to write the query is:

select u.id, u.popularity from users u, (select gender, latest_location from users where id = 2) as me
    where u.gender = me.gender
    order by  u.latest_location::geometry <-> me.latest_location::geometry ASC
    limit 50;

However, this will also be as slow as the CTE.

If on the other hand I extract out the me parameters and statically insert them the query is quick again:

select u.id, u.popularity from users u
    where u.gender = 'male'
    order by  u.latest_location::geometry <-> '0101000000A49DE61DA71C5A403D0AD7A370F54340'::geometry ASC
    limit 50;

Explain of the first (fast) query

 Limit  (cost=5.69..20.11 rows=50 width=36) (actual time=0.512..8.114 rows=50 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_pkey on users users_1  (cost=0.42..2.64 rows=1 width=32) (actual time=0.032..0.033 rows=1 loops=1)
           Index Cond: (id = 2)
   InitPlan 2 (returns $1)
     ->  Index Scan using users_pkey on users users_2  (cost=0.42..2.64 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Index Scan using users_latest_location_gix on users  (cost=0.41..70796.51 rows=245470 width=36) (actual time=0.509..8.100 rows=50 loops=1)
         Order By: (latest_location <-> $0)
         Filter: (gender = $1)
         Rows Removed by Filter: 20
 Total runtime: 8.211 ms
(12 rows)

Explain of the second (slow) query

Limit  (cost=62419.82..62419.95 rows=50 width=76) (actual time=1024.963..1024.970 rows=50 loops=1)
   CTE me
     ->  Index Scan using users_pkey on users  (cost=0.42..2.64 rows=1 width=221) (actual time=0.037..0.038 rows=1 loops=1)
           Index Cond: (id = 2)
   ->  Sort  (cost=62417.18..63030.86 rows=245470 width=76) (actual time=1024.959..1024.963 rows=50 loops=1)
         Sort Key: ((u.latest_location <-> me.latest_location))
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Hash Join  (cost=0.03..54262.85 rows=245470 width=76) (actual time=0.122..938.131 rows=288646 loops=1)
               Hash Cond: (u.gender = me.gender)
               ->  Seq Scan on users u  (cost=0.00..49353.41 rows=490941 width=48) (actual time=0.021..465.025 rows=490994 loops=1)
               ->  Hash  (cost=0.02..0.02 rows=1 width=36) (actual time=0.054..0.054 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                     ->  CTE Scan on me  (cost=0.00..0.02 rows=1 width=36) (actual time=0.047..0.049 rows=1 loops=1)
 Total runtime: 1025.096 ms


texts: Joining 4 tables and selecting distinct values based on 3 timestamps

texts:
I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple times, there are duplicates on the part_id and i would only like to have the last tested results from all the 3 tables.

I am running this query now

select lb.request_ky,
lb.build_dm,
lb.part_id,
dv.date_test,
dv.result,
dw.date_test,
dw.result,
tt.date_test,
tt.result

From tableA lb,
tableB dv,
tableC dw,
tableD tt

Where lb.part_id = dv.part_id
And lb.part_id = dw.part_id
And lb.part_id = tt.part_id

This query would give me duplicates on the part id. How can i just get the latest result from each table and join them together?


texts: Single sql query to get the transpose of a matrix

texts:
I am facing a problem in writing a query for this question.Consider two random 3x3 (n=3) matrices A and B,having the following schema:

A(i,j,val) and B(i,j,val) where

i int:Row Index

j int:Coloumn Index

val int:Cell Value

Write a single query to get the matrix transpose(A) in the same format as A ie.,output tuples should be of format (i,j,val) where i is row, j is coloumn and val is cell value.


texts: MySQL8 Restore from Physical Files - Innodb

texts:
I recently had one of my VM's go down (MS Server 2016) and the only version I am able to get up and running is two weeks old. However, I do have a backup of my C:/ drive from last night that has my C:/ProgramData/MySQL and C:/Program Files/MySQL in order to restore my databases can I simply shut down MySQL service, copy/replace the folders in my VM, and start MySQL again?

[SOLVED] As stated in the comment below:

Copying the entire C:/ProgramData/MySQL folder did not work for me, the MySQL service would not start. Instead, copying just the contents of C:/ProgramData/MySQL/MySQL Server 8.0/Data solved the problem. The service started with no errors and all my data was recovered.


texts: SQL Server trace based on client TCP port

texts:
I have a Windows terminal server with many different users logging in via RDP to run an application. The application makes one or more connections per user to a SQL Server 2008 R2 instance. All users access the same database using the same SQL login. I would like to be able to trace a particular user's SQL session, but I have not found a way to determine which SQL session(s) belongs to which user. However, I am able to determine the source TCP port each instance of the application is using.

Is there a way to trace a SQL session based on the client's TCP port?


texts: What is Sql Server Clustered Index in Oracle terminology

texts:
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.

I would like a simple explaination for this concept for oracle savy users.

More information about Clustered Indexes in Sql Server.
What is a Clustered Index?


texts: postgres: are subqueries pushed down to a postgres_fdw?

texts:
given this query:

select *
from fdw_schema.customer c
where exists
(  select *
   from fdw_schema.purchase p
   where c.id = p.customer_id
   and p.purchase_date > now() - interval '30 days');

Will the full query get pushed down to the postgres_fdw?

My alternative would be:

select distinct c.*
from fdw_schema.customer c
join fdw_schema.purchase p on c.id = p.customer_id
where p.purchase_date > now() - interval '30 days';

I'm looking at pg_stat_activity on the foreign server, but all I can see is

  FETCH 100 FROM c2


texts: Remove question marks from database(latin1_general_ci table)?

texts:
How can I remove question marks(not diamond shaped) from a latin1_general_ci MyISAM table?

I tried going back to utf8 and tried to apply things in https://stackoverflow.com/questions/1476356/detecting-utf8-broken-characters-in-mysql but it did not work, and some cannot work due to fulltext index of the fields.

Example field, viewing from directly database using phpmyadmin or heidisql:

"OSU ??? Oklahoma City"